Bir listedeki benzersiz öğeleri saymak istediğinizi söyleyin, ancak bir bükülme ile. Ve bu çalışma sayfasıyla çalıştığınızı söyleyin:
D sütunu, B sütunundaki bölümlerin her birindeki satır sayısını sayar ve C sütunu, o bölüm için A sütununun ilk beş karakterine göre benzersiz bölümlerin sayısını sayar. B2: B11 hücreleri ARG içerir ve A2: A11'in ilk beş karakterinde sekiz benzersiz öğe sayabilirsiniz çünkü A7: A9'un her biri 11158 içerir, bu nedenle iki kopya sayılmaz. Benzer şekilde, D12'deki 5, BRD için beş satır olduğunu söyler, ancak 12:16 satırları içinde, 11145 tekrarlandığından ve 11173 tekrarlandığından, ilk beş karakterden üç benzersiz öğe vardır.
Peki Excel'e bunu yapmasını nasıl söylersiniz? Ve C2'de C12 ve C17'ye kopyalanabilen hangi formülü kullanabilirsiniz?
D2'deki basit sayma formülü, =COUNTIF(B:B,B2)B sütununda B2 (ARG) kaç kez bulunduğunu sayar.
Aşağıdaki şekilde olduğu gibi, A sütununun ilk beş karakterini izole etmek için bir yardımcı sütun kullanırsınız:
Ardından, bir şekilde ARG için, benzersiz öğelerin sayısını bulmak için yalnızca F2: F11 hücreleriyle ilgilendiğinizi belirtmeniz gerekir. Genel olarak, bu değeri aşağıdaki şekilde gösterilen dizi formülünü kullanarak bulabilirsiniz:
Formülü göstermek için geçici olarak C3 hücresini kullanırsınız; önceki şekillerde C3'te olmadığını görebilirsiniz. (Bu formülün nasıl çalıştığını kısaca öğreneceksiniz.)
Peki C2, C12 ve C17'deki formül nedir? Şaşırtıcı (ve havalı) cevap bu şekilde gösterilmektedir:
Whoa! Bu nasıl çalışıyor?
Aşağıdaki şekilde tanımlanan isimlerdeki Cevap'a bir göz atın:
Daha önceki bir şeklin formülüyle aynıdır, ancak F2: F11 aralığını kullanmak yerine Rg adlı bir aralık kullanır. Ayrıca formül bir dizi formülüydü, ancak adlandırılmış formüller dizi formülleri gibi ele alınır! Yani =AnswerCtrl + Shift + Enter ile girilmez, ancak her zamanki gibi girilir.
Peki Rg nasıl tanımlanır? C1 hücresi seçilirse (bu, bu numarayı anlamak için önemli bir adımdır), o zaman aşağıdaki şekilde tanımlanmıştır:
Bu =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details, sayfanın adıdır, ancak bu formüle uzun sayfa adı olmadan bakabilirsiniz. Bunu yapmanın kolay bir yolu, sayfaya geçici olarak x gibi basit bir ad vermek ve ardından tanımlanan ada tekrar bakmaktır:
Bu formülün okunması daha kolay!
Bu formülün, tüm B sütununa göre $ B1 ile eşleştiğini (mevcut satıra olan göreceli referansa dikkat edin) ve 1'i çıkardığını görebilirsiniz. F1'den KAYDIR'ı kullandığınız için 1 çıkarırsınız. Artık C'nin formülünü bildiğinize göre, C2'nin formülüne bir bakın:
MATCH($B2,$B:$B,0)(Sayfa adı referans olmadan) Formül yani formülün parçası, 2:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
veya:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
veya:
=OFFSET($F$1,1,0,10,1)
Çünkü COUNTIF($B:$B,$B2)10, 10 ARG var. Bu F2: F11 aralığıdır. Nitekim, C2 hücresi seçiliyse ve Rg'ye gitmek için F5'e basarsanız, şunu görürsünüz:
Başlangıç hücresi C12 ise, Rg'ye gitmek için F5'e basmak şunu üretir:
Yani şimdi, Cevap olarak tanımlandığında =SUM(1/COUNTIF(rg,rg)), işiniz bitti!
Çok daha basit bir örnek kullanarak bu formülün nasıl çalıştığına daha yakından bakalım. Normalde, COUNTIF için sözdizimi aşağıdaki şekildeki =COUNTIF(range,criteria)gibi şöyledir =COUNTIF(C1:C10, "b"):
Bu, aralıktaki b sayısı olarak 2 verir. Ancak aralığın kendisini ölçüt olarak geçirmek, aralıktaki her öğeyi ölçüt olarak kullanır. Formülün bu bölümünü vurgularsanız:
ve F9 tuşuna basın, şunu görürsünüz:
Aralıktaki her bir öğe değerlendirilir ve bu sayı dizisi, bir a olduğu ve iki b, üç c ve dört d olduğu anlamına gelir. Bu sayılar, burada görebileceğiniz gibi 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼ şeklinde 1'e bölünmüştür:
Yani 2 yarım, 3 3, 4 4 ve 1 bütün var ve bunları toplamak 4 verimi sağlıyor. Bir öğe 7 kez tekrarlandıysa, o zaman 7 yedinci olur ve bu böyle devam eder. Oldukça havalı! (Bu formülü keşfettiği / icat ettiği için David Hager'a şapka çıkartın.)
Ama bir dakika bekleyin. Olduğu gibi, bu formülü yalnızca C2, C12 ve C17'ye girmeniz gerekir. C2'ye girip doldurup sadece doğru hücrelerde gösterseniz daha iyi olmaz mıydı? Aslında bunu yapabilirsiniz. Formülü C2'de olacak şekilde değiştirebilirsiniz =IF(B1B2,Answer,"")ve bunu doldurduğunuzda, şu işi yapar:
Ama neden burada duralım? Neden formülü burada gösterildiği gibi adlandırılmış bir formül haline getirmiyorsunuz:
Bunun çalışması için C2 hücresinin etkin hücre olması gerekir (veya formülün farklı olması gerekir). Artık C sütununun formüllerini şu şekilde değiştirebilirsiniz =Answer2:
=Answer2C sütunundaki tüm hücreler gibi C3'ün de sahip olduğunu görebilirsiniz . Buna neden D sütununda devam etmiyorsunuz? Karşılaştırmayı B1 ve B2'ye de uyguladıktan sonra D2'deki formül burada gösterilir:
Dolayısıyla, D2 hücresini seçili tutarsanız ve başka bir formül tanımlarsanız, Cevap3 deyin:
daha sonra =Answer3D2 hücresine girebilir ve doldurabilirsiniz:
Çalışma sayfasının formüllerin gösterildiği üst kısmı ve ardından değerleri gösteren aynı ekran görüntüsü:
Başkaları bunu anlamaya çalıştıklarında, ilk başta kafalarını kaşıyabilirler!
Bu konuk makale Excel MVP Bob Umlas'tan. Kutunun Dışında Daha Fazla Excel kitabından. Kitaptaki diğer konuları görmek için burayı tıklayın.
