Újabb bizonyíték: VLOOKUP-nál is jobb barátunk lehet az INDEX-MATCH párosa

2016. január 30. - Office Guru

A mai poszt témája egy olvasói kérdés, amely egy igencsak mindennapos Excel-problémát jár körbe, egy olyat, amelynek eléréséhez valószínűleg mindenkinek megvan a saját kis megoldási módszere, most egy ilyen megoldást fogok bemutatni - nem biztos, hogy a legegyszerűbb, de a lényegi célkitűzést teljesíti, azaz egy lépésben megadja a szükséges választ.

Szóval adott a következő kis táblázat, alatta olvasható is a kérdés, amelynek a megoldását keressük: azaz melyik város eladásai produkálták a legnagyobb változást 2014 és 2015 között?

980.jpgMost tekintsünk el azoktól az egyszerű, többlépcsős megoldásoktól, mint hogy végzünk egy kivonást, maximalizáljuk a különbséget és bekeressük a hozzá tartozó városnevet, hiszen értelemszerűen a leggyorsabb utat keressük (hangsúlyozom, a most taglalt megoldási javaslatnál is lehet egyszerűbb út).

Az biztos, hogy a függvényünk egy tömbfüggvény kell, hogy legyen, tekintve, hogy egy tömbből szeretnénk egyetlen választ megkeresni, másrészt az is valószínű, hogy szükségünk lesz MAX, ABS, INDEX és MATCH függvényekre is, hiszen

MAX megadja a paramétereiként megadott számok közül a legnagyobbat
ABS megadja a paramétereként megadott szám abszolút értékét
INDEX egy megadott tömbben megadja a megadott sor és oszlop találkozásánál lévő értéket
MATCH az első paramétereként megadott érték helyzetét fogja a második paramétereként megadott tartományból megadni nekünk (a harmadik paraméter a pontos egyezés, kisebb-nagyobb feltétel megadására ad lehetőséget), így értelemszerűen a

=MATCH(D20,F19:F23,0)

meg fogja mondani, hogy a D20-as cella értéke az F19:F23 tartományban hol található pontos egyezés esetén.

Most mindenkinek azt javasolnám, hogy a fentebb taglalt függvények segítségével először próbálja saját maga kitalálni a megoldást, persze közben én itt folytatom azért a megoldáshoz vezető út bemutatását.

Először próbáljuk meghatározni szimplán a különbségeket abszolút értékben, végig figyelve arra, hogy itt CTRL+SHIFT+ENTER segítségével alkalmazható tömbfüggvényről van szó:

{=ABS(E4:E11-D4:D11)}

981.jpgKövetkező lépésben kell megkeresnünk, hogy ebben az új tömbben, melyik a legnagyobb érték, itt kell majd a MAX függvényt használnunk:

{=MAX(ABS(E4:E11-D4:D11))}

982.jpgEzután ha visszagondolunk a fentebb leírt MATCH definícióra, akkor láthatjuk, hogy a következő lépésünk az lesz, hogy a MATCH segítségével meghatározzuk a maximum értékünk helyzetét az abszolútérték-különbségeink tömbjében:

{=MATCH(MAX(ABS(E4:E11-D4:D11)),ABS(D4:D11-E4:E11),0)}

Ez vissza fogja adni nekünk annak a sornak a számát, amelyben az abszolútérték-különbségeink tömbjében a legnagyobb érték található.

983.jpgInnen pedig már csak egy INDEX formulára lesz szükségünk, hiszen ezzel az eredeti tömbünk városnév oszlopából kell visszakapnunk az előbbi tömbfüggvénnyel meghatározott sorszám szerinti értéket, azaz:

{=INDEX(C4:C11,MATCH(MAX(ABS(E4:E11-D4:D11)),ABS(D4:D11-E4:E11),0))}

984.jpgÉs láthatjuk is, mi a megoldásunk. Bárkinek van más ötlete, javaslata, VBA-kódja a témában, szívesen látom!

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr388331930

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

toportyánféreg 2016.01.31. 19:09:45

Function valami(tart As Range)
mdiff = 0

For n = 1 To tart.Rows.Count
diff = tart.Cells(n, 3) - tart.Cells(n, 2)
If diff > mdiff Then
mdiff = diff
varos = tart.Cells(n, 1)
End If
Next n

valami = varos
End Function

toportyánféreg 2016.01.31. 20:44:53

magamnak máskorra:
Function valami(tart As Range)
mdiff = 0

For Each sor In tart.Rows

diff = sor.Cells(1, 3) - sor.Cells(1, 2)
If diff > mdiff Then
mdiff = diff
varos = sor.Cells(1, 1)
End If
Next sor

valami = varos
End Function

Péter Bogdán 2016.02.03. 11:55:53

Sziasztok!
Egy hibát vettem észre, amire nem tudok megoldást :)
Mi van, ha egyenlőség van?

Atzs · https://adatrendezo.hu 2016.02.05. 21:09:54

@Péter Bogdán: Ha az összefűz függvény tömbfüggvény lenne, akkor meg lehetne oldani natív függvényekkel. Sajnos nem az. :-(

Ellenben toportyánféreg kódjának minimális átalakításával megoldható a probléma. Ha van kedved próbálkozni vele, akkor ne nézd meg a következő hozzászólásomat.

Atzs · https://adatrendezo.hu 2016.02.05. 21:10:46

Function valami(tart As Range)
mdiff = 0

For Each sor In tart.Rows

diff = sor.Cells(1, 3) - sor.Cells(1, 2)
If diff > mdiff Then
mdiff = diff
varos = sor.Cells(1, 1)
ElseIf diff = mdiff Then
varos = varos & "," & sor.Cells(1, 1)
End If
Next sor

valami = varos
End Function

A kulcslyuk szelleme 2016.02.09. 13:49:38

Javaslom az ABS függvény kihagyását, mert így csak a bármilyen előjelű _változást_ mutatja és nem a növekedést.