Formula umum
=MAX(INDEX(data,0,MATCH(column,header,0)))
Ringkasan
Untuk mengambil nilai maksimal dalam sekumpulan data, di mana kolomnya adalah variabel, Anda dapat menggunakan INDEX dan MATCH bersama dengan fungsi MAX. Pada contoh yang diperlihatkan rumus di J5 adalah:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
di mana data (B5: F15) dan header (B4: F4) diberi nama rentang.
Penjelasan
Catatan: Jika Anda baru mengenal INDEX dan MATCH, lihat: Cara menggunakan INDEX dan MATCH
Dalam konfigurasi standar, fungsi INDEX mengambil nilai pada baris dan kolom tertentu. Misalnya, untuk mendapatkan nilai di baris 2 dan kolom 3 dalam rentang tertentu:
=INDEX(range,2,3) // get value at row 2, column 3
Namun, INDEX memiliki trik khusus - kemampuan untuk mengambil kembali seluruh kolom dan baris. Sintaksnya melibatkan pemberian nol untuk argumen "lainnya". Jika Anda menginginkan seluruh kolom, Anda menyediakan baris sebagai nol. Jika Anda menginginkan seluruh baris, Anda menyediakan kolom sebagai nol:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
Dalam contoh yang ditunjukkan, kami ingin mencari nilai maksimum dalam kolom tertentu. Masalahnya adalah bahwa kolom tersebut harus bervariasi sehingga dapat dengan mudah diubah. Di F5, rumusnya adalah:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Bekerja dari dalam ke luar, pertama-tama kami menggunakan fungsi MATCH untuk mendapatkan "indeks" dari kolom yang diminta di sel J4:
MATCH(J4,header,0) // get column index
Dengan "Hijau" di J4, fungsi MATCH mengembalikan 3, karena Hijau adalah nilai ketiga di header rentang bernama . Setelah MATCH mengembalikan hasil, rumusnya dapat disederhanakan menjadi ini:
=MAX(INDEX(data,0,3))
Dengan nol disediakan sebagai nomor baris, INDEX mengembalikan semua nilai di kolom 3 dari data rentang bernama . Hasilnya dikembalikan ke fungsi MAX dalam array seperti ini:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Dan MAX mengembalikan hasil akhirnya, 93.
Nilai minimum
Untuk mendapatkan nilai minimum dengan kolom variabel, Anda cukup mengganti fungsi MAX dengan fungsi MIN. Rumus di J6 adalah:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Dengan FILTER
Fungsi FILTER baru juga dapat digunakan untuk mengatasi masalah ini, karena FILTER dapat memfilter data menurut baris atau kolom. Triknya adalah dengan membuat filter logis yang akan mengecualikan kolom lain. COUNTIF berfungsi dengan baik dalam kasus ini, tetapi harus dikonfigurasi "mundur", dengan J4 sebagai rentang, dan header untuk kriteria:
=MAX(FILTER(data,COUNTIF(J4,header)))
Setelah COUNTIF berjalan, kami memiliki:
=MAX(FILTER(data,(0,0,1,0,0)))
Dan FILTER mengirimkan kolom ke-3 ke MAX, sama seperti fungsi INDEX di atas.
Sebagai alternatif untuk COUNTIF, Anda dapat menggunakan ISNUMBER + MATCH sebagai gantinya:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Fungsi MATCH sekali lagi diatur "mundur", sehingga kita mendapatkan array dengan 5 nilai yang akan berfungsi sebagai filter logis. Setelah ISNUMBER dan MATCH dijalankan, kami memiliki:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Dan FILTER kembali mengirimkan kolom ke-3 ke MAX.
