Jak správně zabalit padák?
Výhoda. Vydání 2, přepracované.
Řekněme, že máme následující tabulku objednávek:
Potřebujeme například vědět, jaká byla výše Ivanovovy třetí objednávky nebo kdy Petrov provedl svůj druhý obchod. Vestavěná funkce VLOOKUP umí vyhledat pouze první výskyt příjmení v tabulce a nepomůže nám. Otázky jako „Kdo byl manažerem objednávky číslo 10256?“ také zůstane nezodpovězeno, tk. vestavěný VLOOKUP není schopen vrátit hodnoty ze sloupců nalevo od vyhledávacího.
Oba tyto problémy jsou vyřešeny jedním šmahem – napišme si vlastní funkci, která bude hledat nejen první, ale v obecném případě i N-tý výskyt. Navíc bude schopen vyhledávat a vytvářet výsledky v libovolných sloupcích. Nazvěme to, řekněme, VLOOKUP2.
Otevřete Editor jazyka Visual Basic stisknutím ALT+F11 nebo výběrem z nabídky Služba – Makro – Editor jazyka Visual Basic (Nástroje — Makro — Editor jazyka Visual Basic), vložte nový modul (menu Vložit – Modul) a zkopírujte tam text této funkce:
Funkce VLOOKUP2(Tabulka jako varianta, SearchColumnNum jako dlouhá, SearchValue jako varianta, _ N jako dlouhá, ResultColumnNum jak dlouhá) Dim i As long, iCount as Long Select Case TypeName(Table) Case "Range" For i = 1 To Table.Rows .Count If Table.Cells(i, SearchColumnNum) = SearchValue Then iCount = iCount + 1 End If If iCount = N Then VLOOKUP2 = Table.Cells(i, ResultColumnNum) Exit For End If Next i Case "Variant()" For i = 1 To UBound(Table) If Table(i, SearchColumnNum) = SearchValue Then iCount = iCount + 1 If iCount = N Then VLOOKUP2 = Table(i, ResultColumnNum) Exit For End If Next i End Select End Function
Zavřete editor jazyka Visual Basic a vraťte se do Excelu.
Nyní přes Vložit – Funkce (Vložit — Funkce) v kategorii Definováno uživatelem (definováno uživatelem) můžete najít naši funkci VLOOKUP2 a použít ji. Syntaxe funkce je následující:
=VLOOKUP2(tabulka; číslo_sloupce_kde_hledáme; hledaná_hodnota; N; počet_sloupců_od_k_získané_hodnotě)
Nyní nám omezení standardní funkce nejsou překážkou:
PS Zvláštní poděkování patří The_Prist za vylepšení funkce, aby mohl vyhledávat v zavřených knihách.
- Vyhledání a nahrazení dat z jedné tabulky do druhé pomocí funkce VLOOKUP
- „Left VLOOKUP“ pomocí funkcí INDEX a MATCH