Özet
Sayısal bir değere göre sıralanmış bir adlar listesini görüntülemek için METİN işlevinin yardımıyla BÜYÜK, İNDİS, KAÇINCI'ya dayalı bir formül seti kullanabilirsiniz. Gösterilen örnekte, G5'teki formül şöyledir:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Ve G10'daki formül:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
burada müşteri (B5: B17) tarih (C5: C17) ve miktar (C5: C17) adlandırılmış aralıklardır.
Not: Bunlar dizi formülleridir ve Excel 365 dışında control + shift + enter ile girilmelidir.
Açıklama
Bu örnek, anlaşılır olması için iki bölüm halinde oluşturulmuştur: (1) her ay için en yüksek 3 tutarı belirlemeye yönelik bir formül ve (2) en yüksek 3 aylık tutarın her biri için müşteri adını almaya yönelik bir formül.
Kaynak verilerde gerçek bir sıralama olmadığını unutmayın. Bunun yerine, doğrudan tutarlarla çalışmak için BÜYÜK işlevini kullanıyoruz. Diğer bir yaklaşım, RANK işlevi ile kaynak verilere derece eklemek ve müşteri adlarını almak için sıra değerini kullanmaktır.
Bölüm 1: Her ay ilk 3 tutarı alın
Her hafta en yüksek 3 tutarı almak için G5'teki formül şu şekildedir:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Not: Bu bir dizi formülüdür ve Excel 365 dışında control + shift + enter ile girilmelidir.
Baştan sona çalışarak, ilk olarak, adlandırılmış aralık tarihindeki her tarih için ay adlarını almak için TEXT işlevini kullanırız :
TEXT(date,"mmmm") // get month names
Özel sayı biçimi "mmmm", adlandırılmış aralık tarihindeki her ad için "Nisan", "Mayıs", "Haziran" gibi bir dize döndürür . Sonuç, aşağıdaki gibi bir ay isimleri dizisidir:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
TEXT işlevi, bu diziyi, belirli bir aydaki tarihleri G4'teki değere göre test ederek (karışık bir referans, böylece formül aşağı ve çapraz kopyalanabilir) belirli bir aydaki tarihleri filtrelemek için yapılandırılan IF işlevine sunar:
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Yalnızca Nisan ayındaki miktarlar hayatta kalır ve IF üzerinden geçebilir; diğer tüm değerler YANLIŞ:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Son olarak, BÜYÜK işlevi kalan "n'inci" en büyük değeri döndürmek için F5'teki değeri (ayrıca karışık bir referans) kullanır. G5 hücresinde, BÜYÜK "1." en büyük değer olan 18.500 değerini döndürür. Formül aşağıya ve tablo boyunca kopyalandıkça, BÜYÜK işlevi üç ayın her birinde ilk 3 tutarı döndürür.
Artık her ayın ilk 3 değerini bildiğimize göre, bu bilgiyi her biri için müşteri adını almak için bir "anahtar" gibi kullanabiliriz.
Bölüm 2: İstemci adlarını alın
Not: Bu, INDEX ve MATCH kullanımının birden çok kriterle kullanımına bir örnektir. Bu kavram sizin için yeniyse, işte temel bir örnek.
G5: I7'deki ilk üç değerle ilişkili adı almak için INDEX ve MATCH kullanıyoruz:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Not: Bu bir dizi formülüdür ve Excel 365 dışında control + shift + enter ile girilmelidir.
İçten dışa doğru çalışarak, MATCH işlevi aşağıdaki gibi Boole mantığını kullanacak şekilde yapılandırılmıştır:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Arama değeri 1'dir ve arama dizisi şu ifadeyle oluşturulur:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Arama dizisini oluşturan ifade, (1) Nisan ayında olmayan ve (2) G5'teki (18.500) değer olmayan miktarları "filtrelemek" için Boole mantığını kullanır. Sonuç şöyle bir 1'ler ve 0'lar dizisidir:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Eşleme türü için arama değeri 1 ve sıfır olan (tam eşlemeyi zorlamak için) MATCH, doğrudan INDEX işlevine 3 döndürür:
=INDEX(client,3) // returns "Janus"
INDEX, adlandırılmış aralık istemcisindeki üçüncü değeri "Janus" döndürür.
Formül, tablo boyunca kopyalandığında, üç ayın her birinde ilk 3 müşteriyi döndürür.
