Bu rakamı inceleyin:
Sanki Bölgeye göre filtre uyguluyormuşsunuz gibi bundan bir rapor üretmek istediğinizi varsayalım. Yani, Kuzeyde filtre uygularsanız şunu görürsünüz:
Ama ya aynı şeyin formül tabanlı bir versiyonunu isteseydiniz?
I: K sütunlarında aradığınız sonuç şu şekildedir:
Açıkçası, aynı rapor, ancak burada filtrelenmiş öğe yok. Doğu ile ilgili yeni bir rapor istiyorsanız, G1'deki değeri Doğu olarak değiştirmeniz iyi olur:
İşte böyle yapılır. Her şeyden önce, DÜŞEYARA kullanılarak yapılmaz. Ben de bu tekniğin başlığı hakkında yalan söyledim!
F sütunu daha önce gösterilmemiştir ve gizlenebilir (veya raporu engellememesi için başka bir yere taşınabilir).
F sütununda gösterilenler, G1'in A sütununda bulunduğu satır numaralarıdır; yani, hangi satırlar "Kuzey" değerini içerir? Bu teknik, en azından üst üste 2. sütununda karşı değer “Kuzey” ile eşleşir, ancak bunun yerine tüm sütun, bir OFFSET işlevi kullanmak içinde başlamalıdır yüzden, yukarıdaki hücre kullanarak içerir: OFFSET($A$1,F1,0,1000,1).
F1 0 olduğundan, bu OFFSET(A1,0,0,1000,1)A1: A1000'dir. (1000 keyfi, ancak işi yapacak kadar büyük - başka bir sayı yapabilirsiniz).
F2'deki 2 değeri, ilk "Kuzey" nin olduğu yerdir. Sonunda F1 değerini de geri eklemek istiyorsunuz, ancak bu şimdiye kadar sıfır.
"Sihir" F3 hücresinde hayat buluyor. İlk Kuzeyin 2. Sırada bulunduğunu zaten biliyorsunuz. Dolayısıyla, A1'in altında iki satır aramaya başlamak istiyorsunuz. OFFSET işlevinin ikinci argümanı olarak 2'yi belirterek bunu yapabilirsiniz.
F3'teki formül otomatik olarak F2 hücresinde hesaplanan 2'yi gösterecektir: Formülü aşağıya kopyaladığınızda =OFFSET($A$1,F2,0,1000,1), OFFSET($A$1,2,0,1000,1)hangisinin A3: A1000 olduğunu göreceksiniz . Yani, Kuzeyi bu yeni aralık ile eşleştiriyorsunuz ve bu yeni aralığın üçüncü hücresinde Kuzeyi buluyor, bu nedenle MATCH 3 verir.
Yukarıdaki hücreden F2 değerini geri eklediğinizde, ikinci Kuzeyi içeren satır olan 3 artı 2 veya 5'i göreceksiniz.
Bu formül, tüm değerleri alacak kadar doldurulmuştur.
Bu size tüm Kuzey kayıtlarının bulunduğu satır numaralarını verecektir.
Bu satır numaralarını I ile K arasındaki sütunlardaki sonuçlara nasıl çevirirsiniz? Hepsi tek bir formülle yapılır. I2 Bu formülü girin: =IFERROR(INDEX(A:A,$F2),””). Sağa kopyalayın ve ardından aşağıya kopyalayın.
Neden EĞERHATA kullanmalısınız? Hata nerede? F6 hücresine dikkat edin - # N / A içeriyor (bu nedenle F sütununu gizlemek istersiniz) çünkü 15. satırdan sonra Kuzey sayısı yoktur. Dolayısıyla F sütunu bir hata ise, bir boşluk döndürün. Aksi takdirde, A sütunundaki değeri alın (ve sağa doldurulduğunda, B & C).
$ F2, F sütununa mutlak bir referanstır, bu nedenle sağ dolgu hala F sütununa başvurur.
Bu konuk makale Excel MVP Bob Umlas'tan. Excel Outside the Box kitabındaki en sevdiği tekniklerden biridir.
Kutunun Dışında Excel »
