Genel formül
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Özet
Birden çok değeri bulup bir formülle değiştirmek için, birden çok SUBSTITUTE işlevini iç içe yerleştirebilir ve INDEX işlevini kullanarak başka bir tablodaki bul / değiştir çiftlerini besleyebilirsiniz. Gösterilen örnekte 4 ayrı bul ve değiştir işlemi yapıyoruz. G5'teki formül:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
burada "bul", adlandırılmış aralık E5: E8 ve "değiştir", adlandırılmış aralık F5: F8'dir. Bu formülün nasıl daha kolay okunacağını öğrenmek için aşağıya bakın.
Önsöz
Excel'de bir dizi bul ve değiştir işlemini çalıştırmak için yerleşik bir formül yoktur, bu nedenle bu, tek bir yaklaşımı göstermek için bir "kavram" formülüdür. Aranacak ve değiştirilecek metin doğrudan bir tablodaki çalışma sayfasında depolanır ve INDEX işleviyle alınır. Bu, çözümü "dinamik" yapar - bu değerlerden herhangi biri değiştirilir, sonuçlar hemen güncellenir. Elbette, INDEX kullanma zorunluluğu yoktur; İsterseniz değerleri formüle sabit kodlayabilirsiniz.
Açıklama
Temelde formül, her ikameyi gerçekleştirmek için SUBSTITUTE işlevini şu temel modelle kullanır:
=SUBSTITUTE(text,find,replace)
"Metin" gelen değerdir, "bul" aranacak metindir ve "değiştir" ile değiştirilecek metindir. Aranacak ve değiştirilecek metin, her satırda bir çift olmak üzere sağdaki tabloda E5: F8 aralığında saklanır. Soldaki değerler "bul" adlı aralıkta ve sağdaki değerler "değiştir" adlı aralıktadır. INDEX işlevi, hem "bul" metnini hem de "değiştir" metnini şu şekilde almak için kullanılır:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Bu nedenle, ilk değiştirmeyi çalıştırmak için ("kırmızı" yı arayın, "pembe" ile değiştirin):
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Toplamda, dört ayrı ikame çalıştırıyoruz ve sonraki her bir SUBSTITUTE, önceki SUBSTITUTE'ın sonucuyla başlıyor:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
Okunabilirlik için satır sonları
Bu tür iç içe geçmiş formüllerin okunmasının oldukça zor olduğunu fark edeceksiniz. Satır sonları ekleyerek formülün okunmasını ve sürdürülmesini çok daha kolay hale getirebiliriz:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( B5, INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)), INDEX(find,4),INDEX(replace,4))
Excel'deki formül çubuğu fazladan beyaz boşlukları ve satır sonlarını yok sayar, böylece yukarıdaki formül doğrudan yapıştırılabilir:
Bu arada, formül çubuğunu genişletmek ve daraltmak için bir klavye kısayolu var.
Daha fazla ikame
Daha fazla bul / değiştir çiftini işlemek için tabloya daha fazla satır eklenebilir. Her çift eklendiğinde, formülün yeni çifti içerecek şekilde güncellenmesi gerekir. Adlandırılmış aralıkların (kullanıyorsanız) gerektiğinde yeni değerler içerecek şekilde güncellendiğinden emin olmak da önemlidir. Alternatif olarak, adlandırılmış aralıklar yerine dinamik aralıklar için uygun bir Excel Tablosu kullanabilirsiniz.
Diğer kullanımlar
Aynı yaklaşım, metinden noktalama işaretlerini ve diğer sembolleri bir dizi ikame ile "ayırarak" metni temizlemek için kullanılabilir. Örneğin, bu sayfadaki formül telefon numaralarının nasıl temizleneceğini ve yeniden biçimlendirileceğini gösterir.
