Keresse meg a lista utolsó értékét

Sok Excel-felhasználó ismeri, hogyan kell a VLOOKUP függvényt használni egy adott érték megtalálásához a listában.

Ha a keresett elem többször szerepel a listában, akkor a VLOOKUP függvény általában az első értéket adja vissza.

De mit csinálsz, amikor valójában utolsó értékre van szüksége? Tehát amikor z.B. egy terméket utoljára adtak el? Sajnos a VLOOKUP itt nem segít.

Egy kissé trükkös probléma, amelyet ebben a cikkben nagyon elegánsan fogunk megoldani. Ez így működik:

Amikor a VLOOKUP sikertelen

..

Ehhez készítettem egy mintatáblázatot, amelyet innen tölthet le. Ez egy értékesítési áttekintés a különböző értékesítők számára:

A példa táblázat

Amint könnyen látható, minden eladó többször is megjelenik a listában. Az egyértelműség kedvéért feltételes formázást adtam a listához, hogy az E2-ben felsorolt ​​eladó kiemelve legyen:

Feltételes formázás

Most az F2 cellában szeretném kiszámítani az ügyintéző utolsó eladásait az E2 cellából. Ha most megpróbálja a VLOOKUP segítségével megkeresni Müller eladó utolsó forgalmát, akkor nyilvánvalóan szembeszáll ennek a funkciónak a korlátaival:

Amikor a VLOOKUP sikertelen

Szóval mit kéne tenni?

..

segít SUMPRODUCT

Valahogy el kell tehát jutni az utolsó sorig, amelyben Müller úrnál volt forgalom. Ezt a sokoldalú SUMPRODUCT funkcióval érjük el.

Tehát az F2 cellába írjuk be a következő képletet:
=ÖSSZEG(MAX((A2:A31=E2)*SORA(A2:A31)))

És csodával határos módon a helyes sorszámot meghatározták:

A SUMPRODUCT megkeresi a sort

Mi történik ebben a képletben?

Bontsuk csak fel a SUMPRODUCT képletet alkotóelemeire. Ehhez készítettem egy súgótáblát a H2:H31 területen, amely tisztázza a függvény működését:

A SUMPRODUCT alkatrészeire bontva

A H2-ben ellenőrizzük, hogy az értékesítési táblázatunk A oszlopában szereplő értékesítő megegyezik-e az E2 cellában kiválasztott értékesítővel. Ily módon az igazságérték IGAZ lesz, ill. Rosszul számolva.

Az I. oszlopban egyszerűen a ROW függvényt használjuk a megfelelő sorszám kiadására. A J oszlopban pedig kiszámítjuk a H és I oszlop szorzatát. Csak annyit kell tennie, hogy az igazság értéke IGAZ, az 1-es szám HAMIS, a szám pedig 0. Végül a J32 cellában található MAX függvényt használjuk a maximális érték kiszámításához, és így ugyanazt az eredményt kapjuk, mint a SUMPRODUCT függvény.

Keresse meg az utolsó tranzakciót

Most, hogy megvan az utolsó eladások sora az F2 cellában, csak ezt az értéket kell átadnunk az INDEX függvénynek. Mielőtt azonban ezt megtennénk, ki kell vonnunk egy sort, mivel nem érdekel minket a fejléc az oszlopfejlécekkel. Így megkapjuk a megfelelő forgalmat:

=INDEX(C2:C31;F2-1)

Az INDEX az eredményt adja vissza

És mivel a sorszámításhoz nem akarunk segédoszlopot használni, mindent egyetlen képletbe csomagolunk:

=INDEX(C2:C31;ÖSSZEG(MAX((A2:A31=E2)*ROW(A2:A31)))-1)

A kész képlet

Elkészült a nagyszerű számításunk. És ismét megmutatja, milyen erős és sokoldalú a SUMPRODUCT funkció.