Formula umum
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Ringkasan
Untuk menemukan posisi nilai dalam larik 2D, Anda dapat menggunakan fungsi SUMPRODUCT. Dalam contoh yang diperlihatkan, rumus yang digunakan untuk menemukan nomor baris dan kolom dari nilai maks 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 rentang bernama C5: G14.
Catatan: untuk contoh ini, kami secara sewenang-wenang mencari lokasi nilai maksimum dalam data, tetapi Anda dapat mengganti data = MAX (data) dengan pengujian logis lainnya yang akan mengisolasi nilai tertentu. Perhatikan juga rumus ini akan gagal jika ada nilai duplikat dalam larik.
Penjelasan
Untuk mendapatkan nomor baris, data dibandingkan dengan nilai maks, yang menghasilkan larik hasil TRUE FALSE. Ini dikalikan dengan hasil ROW (data) yang dihasilkan dan deretan nomor baris yang terkait dengan rentang 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 perkalian menyebabkan Excel memaksa nilai TRUE FALSE dalam larik pertama menjadi 1 dan 0, jadi kita dapat memvisualisasikan langkah perantara 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 di lembar kerja. Untuk mendapatkan indeks relatif terhadap rentang bernama "data", kami menggunakan:
-ROW(data)+1
Hasil akhirnya adalah larik (5; 4; 3; 2; 1; 0; -1), yang hanya menampilkan nilai pertama (5).
Rumus untuk menentukan posisi kolom bekerja dengan cara yang sama.
Catatan: Saya menemukan pendekatan ini dalam komentar oleh Mike Erickson di MrExcel.com. Ada beberapa ide bagus lainnya di utas itu juga, termasuk opsi rumus array.
