Meil on tellimuste nimekiri koos kaupade numbrite ja nimetustega. Tahaks näiteks järjekorranumbri järgi laualt välja tõmmata kõik kaubad, mis sinna kuuluvad. Enam-vähem selline:
Imeline omadus VLOOKUP (VLOOKUP) sellises olukorras aitab see ainult osaliselt, sest suudab andmeid välja võtta ainult esimese leitud vaste järgi, st annab meile ainult õunad. Tabeli kõigi üksuste leidmiseks ja ekstraheerimiseks on parem kasutada massiivi valemit. Nagu see:
=INDEX($B$2:$B$16;VÄHEMALT(IF(2 E$=A2: A16;LINE(B2:B16)-1;»»);LINE()-5))
See tuleb sisestada järgmiselt:
- valige lahtrid, kus tulemusi kuvada (meie näites on see vahemik D6:D20)
- sisestage (kopeerige valem esimesse lahtrisse) vahemik
- vajutage Ctrl + nihe + sisene
Ühiku lahutamine fragmendis STRING(B2:B16)-1 tehakse tabeli päise tõttu. Samal põhjusel, et kompenseerida saadud vahemiku nihet algse suhtes, lahutatakse fragmendi number viis. STRING()-5
#NUM! viga, mis ilmub tühjades lahtrites tulemuseks olevas vahemikus D6:D20, saate kasutada veakontrolli funktsioone IF ja EOSH, asendades meie valemi veidi keerukamaga:
=IF(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))
Excel 2007-s ilmus mugavam funktsioon IFERROR - see võimaldab teil probleemi kompaktsemalt lahendada:
=IFERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)
PS
Exceli ingliskeelses versioonis näevad need funktsioonid välja järgmised:
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))
=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)
- Funktsiooni VLOOKUP kasutamine andmete otsimiseks tabelist
- Funktsiooni VLOOKUP2 täiustatud versioon, mis suudab otsida mis tahes veerust, mitte ainult esimesest väärtusest
- VLOOKUP2 ja VLOOKUP3 funktsioonid PLEXi lisandmoodulist
- Mis on massiivivalemid ja milleks neid kasutatakse?