Vyhledání řetězce znaků v textu buňky spolu s vyhledáním hodnoty ve vedl.listu

[Transmit]

Dobrý den,

v listu "zápis o dodávce" je ve sloupci A zápis o původu zboží, ve kterém se vždy nachází země původu. Bohužel je země pokaždé zmíněna v textu v buňce v jiném pořadí, ani rozdělovače nejsou vždy stejné. Pouze označení země je vždy stejně zapsané (CHINA, KOREA, atp.)
V druhém listu, "seznam států", jsou tyto názvy států zapsané spolu s úplným názvem dané země ve vedlejším sloupci (např. u KOREA je Republik Korea).
To, o co bych vás chtěl požádat, je pomoc s nastavením vzorce, aby, pokud je to možné, bylo do sloupce B na listu "zápis o dodávce" vyvzorcovaný úplný název země z listu "seznam států".
Př: pokud je v buňce A2 na listu "zápis o dodávce" obsažen řetězec znaků "KOREA", ve sloupci B na stejném listu se objeví "Republik Korea".
Seznam států bude velmi obsáhlý. Jako výsledek bych chtěl dosáhnout toho, že se budou k zápisům o dodávce doplňovat kompletní názvy států, které vytvořím v listu "seznam států".
Vzorový soubor v příloze.

Sešit1.xlsx

(8.94 KiB) Staženo 102 x

Díky moc za pomoc,
Štěpán

[guest]

Makrem, ne vzorcem.

[Azuzula]

Tady jsem spíchla funkci která udělá co potřebuješ.

Kód: Vybrat vše

Function NajdiShodu(BunkaKdeHledatRetezec As Range, ZdrojovaTabulka As Range, SloupecZdroje_Hledej As Integer, SloupecZdroje_Ukaz As Integer) As Variant
Dim a As Long
'*** Funkce na hledání určité sady znaků v buňce podle více hodnot z tabulky ***
'BunkaKdeHledatRetezec  = je buňka ve které se bude vyhledávat řetězec ze zdrojové tabulky
'ZdrojovaTabulka        = zdrojová tabulka ve které se bude vyhledávat
'SloupecZdroje_Hledej   = sloupec zdrojové tabulky ve kterém se má vyhledávat
'SloupecZdroje_Ukaz     = sloupec zdrojové tabulky ze kterého se vezme výsledek podle řádku shody hledání
'*** vzorec: =NajdiShodu(BunkaKdeHledatRetezec;ZdrojovaTabulka;SloupecZdroje_Hledej;SloupecZdroje_Ukaz) ***
'*** pžíklad: =NajdiShodu(A2;'List2'!A:B;1;2)***

With ZdrojovaTabulka
For a = 1 To .Rows.Count
    If .Cells(a, SloupecZdroje_Hledej) = "" Then GoTo konec 'pokud je prázdná buňka na konci zdrojové tabulky
    If BunkaKdeHledatRetezec = "" Then NajdiShodu = 0: Exit Function 'když je prohledávaná buňka prázdná, funkce vrátí hodnotu nula
    If InStr(1, BunkaKdeHledatRetezec, .Cells(a, SloupecZdroje_Hledej), vbTextCompare) > 0 Then
        NajdiShodu = .Cells(a, SloupecZdroje_Ukaz) 'úspěšné hledání
        Exit Function
    End If
Next
End With
konec:
NajdiShodu = CVErr(xlErrNA) 'vrátí chybu N/A když nenajde shodu
'*** Tuto funkci napsala Azuzula - Zuzana Nyiri ***
End Function

[Transmit]

Perfektní, děkuju moc!

Tady jsem spíchla funkci která udělá co potřebuješ.

Kód: Vybrat vše

Function NajdiShodu(BunkaKdeHledatRetezec As Range, ZdrojovaTabulka As Range, SloupecZdroje_Hledej As Integer, SloupecZdroje_Ukaz As Integer) As Variant
Dim a As Long
'*** Funkce na hledání určité sady znaků v buňce podle více hodnot z tabulky ***
'BunkaKdeHledatRetezec  = je buňka ve které se bude vyhledávat řetězec ze zdrojové tabulky
'ZdrojovaTabulka        = zdrojová tabulka ve které se bude vyhledávat
'SloupecZdroje_Hledej   = sloupec zdrojové tabulky ve kterém se má vyhledávat
'SloupecZdroje_Ukaz     = sloupec zdrojové tabulky ze kterého se vezme výsledek podle řádku shody hledání
'*** vzorec: =NajdiShodu(BunkaKdeHledatRetezec;ZdrojovaTabulka;SloupecZdroje_Hledej;SloupecZdroje_Ukaz) ***
'*** pžíklad: =NajdiShodu(A2;'List2'!A:B;1;2)***

With ZdrojovaTabulka
For a = 1 To .Rows.Count
    If .Cells(a, SloupecZdroje_Hledej) = "" Then GoTo konec 'pokud je prázdná buňka na konci zdrojové tabulky
    If BunkaKdeHledatRetezec = "" Then NajdiShodu = 0: Exit Function 'když je prohledávaná buňka prázdná, funkce vrátí hodnotu nula
    If InStr(1, BunkaKdeHledatRetezec, .Cells(a, SloupecZdroje_Hledej), vbTextCompare) > 0 Then
        NajdiShodu = .Cells(a, SloupecZdroje_Ukaz) 'úspěšné hledání
        Exit Function
    End If
Next
End With
konec:
NajdiShodu = CVErr(xlErrNA) 'vrátí chybu N/A když nenajde shodu
'*** Tuto funkci napsala Azuzula - Zuzana Nyiri ***
End Function

[MePExG]

Ponúkam riešenie pomocou maticového vzorca. =MATCH(1;IFERROR(MATCH("*"&stat[Skratka]&"*";A2;0);0);0) vnoreným do index.
Zápis vzorca pomocou Ctrl+Shift+Enter.
Riešenie v prílohe

Stat.xlsx

(11.36 KiB) Staženo 138 x

.

[Azuzula]

Ponúkam riešenie pomocou maticového vzorca...

Teď teprve vidím jakou sílu mají matice (minimálně ty v excelu) a já si naivně myslela, že jsou jen na počítání čehosi co v životě nebudu potřebovat.
Hold jsme o tohle na střední ani nezavadili. Díky Tobě mám další námět na studium.

[guest]

Já bych se v tomto případě maticovému počtu vyhnul. Podle mě bude rychlost výpočtu rapidně klesat s počtem záznamů.

[MePExG]

Žiaľ musím súhlasiť s xln., ale pojal som to ako výzvu, ktorú som myslím zdolal. Do cca. 1000 riadkov by som to používal takto (záleží na procesore), ale keby bolo riadkov viac, aj ja by som si radšej vyrobil funkciu.

[guest]

Jako výzva v pohodě, ostatně v takovém vzorci je leckdy větší know-how než v kódu ze Záznamníku maker (chraň bůh, to není poznámka ke kódu Azuzuly!).

Já bych nešel ani do vlastní funkce. Cílem je projít data, která se sice mohou měnit, ale není to nejspíš otázka změny "každou minutu". Projít, doplnit čisté hodnoty, ende. Proč si kazit přepočet listu tunou zdržujících výpočtů.