Genel formül
=MAX(INDEX(data,0,MATCH(column,header,0)))
Özet
Sütunun değişken olduğu bir veri kümesindeki maksimum değeri almak için, MAX işleviyle birlikte INDEX ve MATCH kullanabilirsiniz. Gösterilen örnekte J5'teki formül şöyledir:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
burada veriler (B5: F15) ve başlık (B4: F4) adlandırılmış aralıklardır.
Açıklama
Not: INDEX ve MATCH konusunda yeniyseniz bkz .: INDEX ve MATCH nasıl kullanılır
Standart bir konfigürasyonda, INDEX işlevi, belirli bir satır ve sütunda bir değer alır. Örneğin, belirli bir aralıkta satır 2 ve sütun 3'teki değeri almak için:
=INDEX(range,2,3) // get value at row 2, column 3
Bununla birlikte, INDEX'in özel bir numarası vardır - tüm sütunları ve satırları alma yeteneği. Sözdizimi, "diğer" argümanı için sıfır sağlamayı içerir. Bir sütunun tamamını istiyorsanız, satırı sıfır olarak sağlarsınız. Bir satırın tamamını istiyorsanız, sütunu sıfır olarak sağlarsınız:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
Gösterilen örnekte, belirli bir sütundaki maksimum değeri bulmak istiyoruz. Buradaki bükülme, kolonun değişken olması gerektiğidir, böylece kolayca değiştirilebilir. F5'te formül şu şekildedir:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
İçten dışa doğru çalışarak, önce J4 hücresinde istenen sütunun "dizinini" almak için MATCH işlevini kullanırız:
MATCH(J4,header,0) // get column index
J4'te "Yeşil" ile MATCH işlevi 3 değerini döndürür, çünkü Yeşil adlandırılmış aralık başlığındaki üçüncü değerdir . MATCH bir sonuç döndürdükten sonra, formül şu şekilde basitleştirilebilir:
=MAX(INDEX(data,0,3))
Satır numarası olarak sıfır sağlandığında, INDEX, adlandırılmış aralık verilerinin 3. sütunundaki tüm değerleri döndürür . Sonuç, aşağıdaki gibi bir dizide MAX işlevine döndürülür:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Ve MAX, nihai sonuç olan 93'ü döndürür.
En az değer
Değişken bir sütunla minimum değeri elde etmek için, MAX işlevini MIN işleviyle değiştirmeniz yeterlidir. J6'daki formül:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
FİLTRE ile
FILTER verileri satıra veya sütuna göre filtreleyebildiğinden, yeni FİLTRE işlevi de bu sorunu çözmek için kullanılabilir. İşin püf noktası, diğer sütunları hariç tutacak mantıksal bir filtre oluşturmaktır. COUNTIF bu durumda iyi çalışır, ancak "geriye doğru", aralık olarak J4 ve ölçütler için başlık olacak şekilde yapılandırılmalıdır :
=MAX(FILTER(data,COUNTIF(J4,header)))
COUNTIF çalıştırıldıktan sonra, elimizde:
=MAX(FILTER(data,(0,0,1,0,0)))
Ve FILTER, yukarıdaki INDEX işleviyle aynı şekilde 3. sütunu MAX'a iletir.
EĞERSAY'a alternatif olarak, bunun yerine ISNUMBER + KAÇINCI kullanabilirsiniz:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
MATCH işlevi yine "geriye doğru" ayarlanır, böylece mantıksal filtre görevi görecek 5 değerli bir dizi elde ederiz. ISNUMBER ve MATCH çalıştırıldıktan sonra, elimizde:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Ve FILTER tekrar 3. sütunu MAX'a iletir.
