Excel'de Yaygın Veri Temizleme Formülleri

Excel formülleri

Yıllarca, yayını sadece nasıl yapılacağını tanımlamak için değil, aynı zamanda daha sonra bakabileceğim bir kayıt tutmak için de bir kaynak olarak kullandım! Bugün, bize felaket olan bir müşteri veri dosyasını veren bir müşterimiz vardı. Hemen hemen her alan yanlış biçimlendirildi ve; sonuç olarak, verileri içe aktaramadık. Excel'in temizlemeyi Visual Basic kullanarak yapması için bazı harika eklentiler olsa da, makroları desteklemeyen Mac için Office'i çalıştırıyoruz. Bunun yerine, yardımcı olacak düz formüller arıyoruz. Bunlardan bazılarını burada başkaları da kullansın diye paylaşacağımı düşündüm.

Sayısal Olmayan Karakterleri Kaldır

Sistemler genellikle telefon numaralarının 11 haneli belirli bir formüle ülke kodu ile birlikte ve noktalama işareti olmadan girilmesini gerektirir. Bununla birlikte, insanlar genellikle bu verileri bunun yerine çizgiler ve noktalar ile girer. İşte harika bir formül sayısal olmayan tüm karakterleri kaldırmak Excel'de. Formül, A2 hücresindeki verileri inceler:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Artık elde edilen sütunu kopyalayabilir ve Düzen> Değerleri Yapıştır doğru biçimlendirilmiş sonuçla verilerin üzerine yazmak için.

Birden Çok Alanı Bir OR ile Değerlendirin

Eksik kayıtları genellikle bir içe aktarımdan temizleriz. Kullanıcılar, her zaman karmaşık hiyerarşik formüller yazmak zorunda olmadığınızın ve bunun yerine bir VEYA ifadesi yazabileceğinizin farkında değiller. Aşağıdaki bu örnekte, eksik veriler için A2, B2, C2, D2 veya E2'yi kontrol etmek istiyorum. Herhangi bir veri eksikse, 0 döndürürüm, aksi takdirde 1 döndürürüm. Bu, verileri sıralamama ve eksik olan kayıtları silmeme olanak tanır.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Alanları Kırp ve Birleştir

Verilerinizde Ad ve Soyad alanları varsa ancak içe aktarmanızın tam ad alanı varsa, yerleşik Excel İşlev Birleştirme özelliğini kullanarak alanları düzgün bir şekilde birleştirebilirsiniz, ancak TRIM'i kullanarak önceki veya sonraki boş boşlukları kaldırdığınızdan emin olun. Metin. Alanlardan birinde veri olmaması durumunda tüm alanı TRIM ile sarıyoruz:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Geçerli E-posta Adresini Kontrol Edin

Hem @ hem de. bir e-posta adresinde:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

İlk ve Son İsimleri Çıkarın

Bazen sorun tam tersidir. Verilerinizin tam bir ad alanı vardır, ancak ad ve soyadları ayrıştırmanız gerekir. Bu formüller ad ve soyad arasındaki boşluğu arar ve gerektiğinde metni alır. BT ayrıca, soyadının olup olmadığını veya A2'de boş bir giriş olup olmadığını da idare eder.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

Ve soyadı:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Karakter Sayısını Sınırla ve Ekle…

Hiç meta açıklamalarınızı temizlemek istediniz mi? İçeriği Excel'e çekmek ve ardından bir Meta Açıklama alanında (150 ila 160 karakter) kullanmak üzere içeriği kırpmak istiyorsanız, bunu şu formülü kullanarak yapabilirsiniz: Benim yerim. Açıklamayı bir boşlukta net bir şekilde bozar ve ardından…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Elbette, bunların kapsamlı olması amaçlanmamıştır… sadece hızlı bir başlangıç ​​yapmanıza yardımcı olacak bazı hızlı formüller! Kendinizi başka hangi formülleri kullanırken buluyorsunuz? Bunları yorumlara ekleyin ve bu makaleyi güncellerken size kredi vereceğim.

Ne düşünüyorsunuz?

Bu site spam'i azaltmak için Akismet'i kullanıyor. Yorum verilerinizin nasıl işlendiğini öğrenin.