Excel képlet: Az érték helyének lekérése 2D tömbben

Általános képlet

=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1

Összegzés

A 2D tömbben található érték helyének megkereséséhez használja a SUMPRODUCT funkciót. A bemutatott példában a képletek a tömb max értékének sor- és oszlopszámának megkeresésére szolgálnak:

=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1

ahol az "adatok" a C5: G14 megnevezett tartomány.

Megjegyzés: ennél a példánál önkényesen megtaláljuk a maximális érték helyét az adatokban, de az data = MAX (data) bármely más logikai teszttel helyettesítheti az adott értéket. Vegye figyelembe azt is, hogy ezek a képletek kudarcot vallanak, ha a tömbben vannak duplikált értékek.

Magyarázat

A sorszám megszerzéséhez az adatokat összehasonlítjuk a max értékkel, amely IGAZ HAMIS eredmények tömbjét generálja. Ezeket megszorozzuk a ROW (data) eredményével, amely az elnevezett "adatok" tartományhoz tartozó sorszámokat állítja elő és tömböt:

=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))

A szorzási művelet azt eredményezi, hogy az Excel az első tömbben szereplő IGAZ HAMIS értékeket 1s és 0s értékekre kényszeríti, így egy ilyen köztes lépést vizualizálhatunk:

=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))

A SUMPRODUCT ezután egy 9-es eredményt ad vissza, amely megfelel a munkalap 9. sorának. A megadott adatokhoz tartozó indexhez a következőket használjuk:

-ROW(data)+1

A végeredmény az (5; 4; 3; 2; 1; 0; -1) tömb, amelyből csak az első érték (5) jelenik meg.

Az oszlop helyzetének meghatározására szolgáló képlet ugyanúgy működik.

Megjegyzés: Mike Erickson egy megjegyzésében futottam össze ezzel a megközelítéssel a MrExcel.com oldalon. Van még néhány jó ötlet ebben a szálban, beleértve a tömbképlet opciót.

érdekes cikkek...