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í.