Formula generik
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Ringkasan
Untuk mencari kedudukan nilai dalam array 2D, anda boleh menggunakan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, formula yang digunakan untuk mencari nombor baris dan lajur dari nilai maksimum dalam array adalah:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
di mana "data" adalah julat bernama C5: G14.
Catatan: untuk contoh ini, kami sewenang-wenangnya menemukan lokasi nilai maksimum dalam data, tetapi anda dapat mengganti data = MAX (data) dengan ujian logik lain yang akan mengasingkan nilai yang diberikan. Perhatikan juga formula ini akan gagal sekiranya terdapat nilai pendua dalam larik.
Penjelasan
Untuk mendapatkan nombor baris, data dibandingkan dengan nilai maksimum, yang menghasilkan pelbagai hasil BENAR BENAR. Ini didarabkan dengan hasil ROW (data) yang menghasilkan dan susunan nombor baris yang berkaitan dengan julat bernama "data":
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE)*(5;6;7;8;9;10;11))
Operasi pendaraban menyebabkan Excel memaksakan nilai-nilai TRUE FALSE dalam array pertama hingga 1s dan 0s, jadi kita dapat menggambarkan langkah perantaraan seperti ini:
=SUMPRODUCT((0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0)*(5;6;7;8;9;10;11))
SUMPRODUCT kemudian mengembalikan hasil 9, yang sesuai dengan baris ke-9 pada lembaran kerja. Untuk mendapatkan indeks relatif dengan julat bernama "data", kami menggunakan:
-ROW(data)+1
Hasil akhir adalah susunan (5; 4; 3; 2; 1; 0; -1), dari mana hanya nilai pertama (5) yang dipaparkan.
Rumus untuk menentukan kedudukan lajur berfungsi dengan cara yang sama.
Catatan: Saya menggunakan pendekatan ini dalam komen oleh Mike Erickson di MrExcel.com. Terdapat beberapa idea baik lain dalam utas itu, termasuk pilihan formula susunan.
