Jak automaticky vyplnit další buňky po výběru hodnoty z rozevíracího seznamu v Excelu: Komplexní průvodce
Při práci s Excelem může automatizace zadávání dat výrazně zvýšit produktivitu. Jedním z běžných úkolů je automatické vyplňování souvisejících dat po výběru hodnoty z rozbalovacího seznamu. Tato příručka zkoumá čtyři komplexní metody, od nativních funkcí Excelu až po VBA a nástroje třetích stran, jako je Kutools.
Nejprve: Vytvořte rozevírací seznam
Metoda 1: Automatické vyplňování pomocí funkce VLOOKUP
Metoda 2: Automatické vyplňování pomocí funkcí INDEX a MATCH
Metoda 3: Automatické vyplňování pomocí Kutools for Excel
Metoda 4: Automatické vyplňování pomocí definované funkce
Metoda 4: Automatické vyplňování pomocí definované funkce
Nejprve: Vytvořte rozevírací seznam
Než implementujete jakoukoli metodu automatického vyplňování, potřebujete mít nastavený rozevírací seznam. Tento rozevírací seznam slouží jako spouštěč pro vyplňování souvisejících buněk.
Kroky:
Krok 1. Připravte zdroj dat.
Krok 2. Vytvořte rozevírací seznam.
Přejděte do buňky, kde chcete rozbalovací nabídku (např. List1!D2)
přejděte na Data > Ověření dat > Ověření dat.
V dialogovém okně Ověření dat vyberte Seznam v části Povolit a vyberte zdroj dat. Klikněte na OK.
Jakmile je rozbalovací nabídka nastavená, můžete pokračovat v implementaci kterékoli z následujících metod automatického vyplňování.
Metoda 1: Automatické vyplňování pomocí funkce VLOOKUP
Funkce VLOOKUP je jednou z nejčastěji používaných funkcí pro vyhledávání dat v Excelu. V kombinaci s rozevírací nabídkou dokáže rychle načíst související data z referenční tabulky.
Kroky:
Do sousední buňky rozbalovacího seznamu (např. E2) zadejte:
🔓 Vysvětlení vzorce:
- Hledá hodnotu v buňkách D2 v prvním sloupci buněk A2:B5. Pokud je nalezena, vrátí odpovídající hodnotu ze sloupce 2 (sloupec B). Pokud není nalezena, zobrazí se chyba (#N/A).
- FALSE znamená, že se musí jednat o přesnou shodu.
Krok 2. Stiskněte vstoupit klíč.
✨ Poznámky
- Použijte funkci IFERROR() pro skrytí chyb, pokud není vybrána žádná hodnota:
=VYHLEDAT(D2;$A$2:$B$5,2;XNUMX;NEPRAVDA) - Nelze vyhledávat nalevo od sloupce s klíčem.
Metoda 2: Automatické vyplňování pomocí funkcí INDEX a MATCH
INDEX a MATCH je silné duo, které svou flexibilitou předčí VLOOKUP. Podporuje vyhledávání na levé straně a zůstává stabilní i při změně uspořádání sloupců.
Kroky:
Do sousední buňky rozbalovacího seznamu (např. E2) zadejte:
🔓 Vysvětlení vzorce:
- MATCH(D2; $A$2:$A$5; 0)
Hledá D2 v rozsahu A2:A5. 0 znamená přesnou shodu (jako NEPRAVDA ve funkci VLOOKUP).
Vrátí pozici (číslo řádku), kde se nachází buňka D2. - INDEX($B$2:$B$5; ...)
Převezme číslo řádku z funkce MATCH.
Vrátí odpovídající hodnotu z buňek B2:B5.
Krok 2. Stiskněte vstoupit klíč.
✨ Poznámky
- Jak návratový rozsah (INDEX), tak i vyhledávací rozsah (MATCH) musí být zarovnány po řádcích.
- Lze hledat vlevo nebo vpravo.
- Odolnější než funkce VLOOKUP.
Metoda 3: Automatické vyplňování pomocí Kutools for Excel
Kutools nabízí přístup založený na grafickém uživatelském rozhraní, který eliminuje potřebu vzorců. Je obzvláště užitečný pro uživatele, kteří chtějí rychlé výsledky, aniž by se museli ponořovat do funkcí Excelu.
Kroky:
Krok 1. V sousední buňce rozbalovacího seznamu (např. E2) přejděte na Kutools > Pomocník vzorců > Vyhledávání a reference > Hledejte seznam hodnot.
Krok 2. Vyberte pole tabulky, vyhledávací hodnotu a číslo sloupce. Klepněte na tlačítko OK.
✨ Poznámky
- Kutools umožňuje aplikovat toto na celý rozsah najednou.
- Nástroj je velmi vhodný pro začátečníky a snižuje počet manuálních chyb.
- Snadné použití.
- Nejsou potřeba žádné vzorce.
Už vás nebaví opakující se úkoly a složité vzorce v Excelu? Kutools for Excel je váš univerzální posilovač produktivity! S více než 300 výkonnými funkcemi— dávkové úpravy, inteligentní vyplňování, automatické filtrování – budete pracovat 10krát rychleji. Stáhněte si nyní a posuňte své znalosti Excelu na další úroveň!
Metoda 4: Automatické vyplňování pomocí definované funkce
Pro uživatele, kteří potřebují dynamické a automatizované řešení nad rámec limitů vzorců, nabízí VBA maximální kontrolu a přizpůsobení logiky.
Kroky:
Krok 1. Stisknutím kláves Alt + F11 otevřete editor VBA.
Krok 2. Klikněte na Vložit > Modul.
Krok 3. Vložte níže uvedený kód do modulu.
'Update by Extendoffice
Function GetProductInfo(productName As String, colIndex As Integer) As Variant
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") 'the sheet that the data source in
Dim rng As Range
Set rng = ws.Range("A2:B5") 'the range of data source
Dim r As Range
For Each r In rng.Rows
If r.Cells(1, 1).Value = productName Then
GetProductInfo = r.Cells(1, colIndex).Value
Exit Function
End If
Next
GetProductInfo = "Not found"
End Function
Krok 4. Vraťte se na list a do sousední buňky rozbalovacího seznamu (např. E2) zadejte:
Krok 5. Stiskněte klávesu Enter.
✨ Poznámky
- Vyžaduje sešit s podporou maker (.xlsm)
Často kladení otázek
Q1: Co když se můj datový rozsah často mění?
Pro uchování odkazů použijte pojmenované rozsahy nebo dynamické tabulky.
Q2: Mohu použít funkci VLOOKUP pro vyhledávání vlevo?
Ne, zvažte použití INDEX+MATCH nebo Kutools pro ten případ.
Q3: Je Kutools bezpečné používání?
Ano, je široce používaný a důvěryhodný, ale vždy si ho stahujte z oficiálních webových stránek.
Q4: Bude VBA fungovat ve všech verzích Excelu?
Většina desktopových verzí ji podporuje, ale ve výchozím nastavení je zakázaná a v Excelu Online není podporována.
Q5: Je Kutools zdarma k použití?
Kutools for Excel Není to zcela bezplatný nástroj, ale nabízí bezplatnou zkušební verzi s následnou možností jednorázového nákupu:
- 30denní bezplatná zkušební verze s plnou funkcionalitou – bez nutnosti platební karty.
- Trvalá licence pro jednoho uživatele: přibližně 49 USD, včetně 2 let bezplatných aktualizací a podpory.
- Po uplynutí dvouleté doby podpory můžete nadále používat verzi, kterou máte, neomezeně dlouho – pouze bez dalších aktualizací.
Nejlepší nástroje pro produktivitu v kanceláři
Vylepšete si své excelové dovednosti s Kutools for Excela zažijte efektivitu jako nikdy předtím. Kutools for Excel Nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab Přináší do Office rozhraní s kartami a výrazně vám usnadňuje práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!