Formula generik
=MAX(INDEX(data,0,MATCH(column,header,0)))
Ringkasan
Untuk mendapatkan nilai maksimum dalam sekumpulan data, di mana lajur berubah-ubah, anda boleh menggunakan INDEX dan MATCH bersama dengan fungsi MAX. Dalam contoh yang ditunjukkan formula di J5 adalah:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
di mana data (B5: F15) dan tajuk (B4: F4) dinamakan julat.
Penjelasan
Catatan: Sekiranya anda baru menggunakan INDEX dan MATCH, lihat: Cara menggunakan INDEX dan MATCH
Dalam konfigurasi standard, fungsi INDEX mengambil nilai pada baris dan lajur tertentu. Sebagai contoh, untuk mendapatkan nilai pada baris 2 dan lajur 3 dalam julat tertentu:
=INDEX(range,2,3) // get value at row 2, column 3
Walau bagaimanapun, INDEX mempunyai helah khas - keupayaan untuk mengambil keseluruhan lajur dan baris. Sintaks melibatkan pembekalan sifar untuk argumen "lain". Sekiranya anda mahukan keseluruhan lajur, anda memberikan baris sebagai sifar. Sekiranya anda mahukan keseluruhan baris, anda memberikan lajur sebagai sifar:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
Dalam contoh yang ditunjukkan, kami ingin mencari nilai maksimum dalam lajur tertentu. Kelainannya ialah lajur perlu berubah-ubah sehingga dapat diubah dengan mudah. Dalam F5, formula adalah:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Bekerja dari dalam ke luar, pertama kami menggunakan fungsi MATCH untuk mendapatkan "indeks" lajur yang diminta di sel J4:
MATCH(J4,header,0) // get column index
Dengan "Green" di J4, fungsi MATCH mengembalikan 3, kerana Green adalah nilai ketiga dalam header range yang dinamakan . Setelah MATCH mengembalikan hasilnya, rumus dapat dipermudah kepada ini:
=MAX(INDEX(data,0,3))
Dengan sifar yang diberikan sebagai nombor baris, INDEX mengembalikan semua nilai pada lajur 3 data julat yang dinamakan . Hasilnya dikembalikan ke fungsi MAX dalam array seperti ini:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Dan MAX mengembalikan keputusan akhir, 93.
Nilai minimum
Untuk mendapatkan nilai minimum dengan lajur pemboleh ubah, anda boleh mengganti fungsi MAX dengan fungsi MIN. Formula dalam J6 adalah:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Dengan PENAPIS
Fungsi FILTER baru juga dapat digunakan untuk menyelesaikan masalah ini, kerana FILTER dapat menyaring data berdasarkan baris atau lajur. Caranya adalah dengan membina penapis logik yang tidak termasuk lajur lain. COUNTIF berfungsi dengan baik dalam kes ini, tetapi mesti dikonfigurasi "mundur", dengan J4 sebagai julat, dan tajuk untuk kriteria:
=MAX(FILTER(data,COUNTIF(J4,header)))
Selepas COUNTIF berjalan, kami mempunyai:
=MAX(FILTER(data,(0,0,1,0,0)))
Dan FILTER memberikan lajur ke-3 ke MAX, sama seperti fungsi INDEX di atas.
Sebagai alternatif kepada COUNTIF, anda boleh menggunakan ISNUMBER + MATCH sebagai gantinya:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Fungsi MATCH sekali lagi disiapkan "ke belakang", sehingga kita mendapat array dengan 5 nilai yang akan berfungsi sebagai penapis logik. Selepas ISNUMBER dan MATCH dijalankan, kami mempunyai:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Dan FILTER sekali lagi memberikan ruang ke-3 ke MAX.
