Mums ir pasūtījumu saraksts ar preču numuriem un nosaukumiem. Es gribētu, piemēram, izvilkt no galda pēc pasūtījuma numura visas preces, kas tajā iekļautas. Vairāk vai mazāk šādi:
Brīnišķīga iezīme VLOOKUP (VLOOKUP) šādā situācijā tas palīdzēs tikai daļēji, jo spēj iegūt datus tikai pēc pirmās atrastās atbilstības, proti, dos mums tikai āboli. Lai atrastu un izvilktu no tabulas visus vienumus, labāk ir izmantot masīva formulu. Kā šis:
=INDEX($B$2:$B$16;Vismazāk(IF(2 E$=A2: A16;LINE(B2:B16)-1;»»);LINE()-5))
Tas jāievada šādi:
- atlasiet šūnas, kurās jāparāda rezultāti (mūsu piemērā tas ir diapazons D6:D20)
- ievadiet (iekopējiet formulu pirmajā šūnā) diapazonā
- nospiest Ctrl + maiņa + ienākt
Vienību atņemšana fragmentā STRING(B2:B16)-1 tiek darīts tabulas galvenes dēļ. Tā paša iemesla dēļ, lai kompensētu iegūtā diapazona nobīdi attiecībā pret sākotnējo, fragmentā tiek atņemts skaitlis pieci. STRING()-5
Lai paslēptu #NUM! kļūda, kas parādīsies tukšās šūnās iegūtajā diapazonā D6:D20, varat izmantot kļūdu pārbaudes funkcijas IF un EOSH, aizstājot mūsu formulu ar nedaudz sarežģītāku:
=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)))
Programmā Excel 2007 parādījās ērtāka IFERROR funkcija, kas ļauj kompaktāk atrisināt problēmu:
=IFERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)
PS
Excel angļu valodas versijā šīs funkcijas izskatīsies šādi:
=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)),»»)
- Funkcijas VLOOKUP izmantošana datu atrašanai tabulā
- Uzlabota funkcijas VLOOKUP2 versija, kas var meklēt jebkurā kolonnā, nevis tikai pirmajā vērtībā
- VLOOKUP2 un VLOOKUP3 funkcijas no PLEX papildinājuma
- Kas ir masīvu formulas un kam tās tiek izmantotas?