Kapitola 17 · 17.17
Všetky produkty zákazníka v jednej bunke, oddelené čiarkami
Zoznam objednávok má jeden riadok na každý produkt. Pre dodací list, hromadný e-mail alebo prehľad však potrebujete jeden riadok na zákazníka: "Hotel De Haven: Krentenbollen, Croissants, Tompoucen". Kedysi sa to dalo urobiť len pomocou makra. S funkciami TEXTJOIN a FILTER na to stačí jeden vzorec.
Ako príklad opäť použijeme hárok Bestellingen pekárne Bakkerij De Korenaar: v stĺpci B je zákazník a v stĺpci C produkt, od riadka 2 po riadok 13.
Upozornenie: na stránkach o Exceli nájdete aj variant s funkciou IF namiesto FILTER: =TEXTJOIN(", ";1;IF(Bestellingen!B2:B13=C2;Bestellingen!C2:C13;"")). V Euro-Office po stlačení obyčajného Enter vráti prázdnu bunku. S klávesmi Ctrl+Shift+Enter síce funguje, ale duplicitné produkty v ňom zostanú. Funkcia FILTER s UNIQUE je elegantnejšia.
Krok 1: každý zákazník len raz
Vytvorte prehľad na inom hárku, napríklad so zákazníkmi v stĺpci C. Do bunky C2 zadajte =SORT(UNIQUE(Bestellingen!B2:B13)). Zákazníci sa zobrazia pod sebou v abecednom poradí (pozrite si Zoznamy, ktoré sa samy aktualizujú, pomocou FILTER, UNIQUE a SORT).
Krok 2: produkty pre každého zákazníka
Vyberte bunky od E2 po riadok posledného zákazníka (tu E2:E5) a zadajte:
=TEXTJOIN(", ";1;UNIQUE(FILTER(Bestellingen!$C$2:$C$13;Bestellingen!$B$2:$B$13=C2)))
Potom stlačte Ctrl+Enter, aby sa vzorec vložil vedľa každého zákazníka.
- FILTER vyberie všetky produkty zákazníka v bunke C2.
- UNIQUE zabezpečí, že produkt, ktorý zákazník objednal viackrát, sa uvedie len raz. Ak chcete vidieť aj opakovania, funkciu UNIQUE vynechajte.
- TEXTJOIN ich spojí dohromady. Prvý argument je oddeľovač (čiarka a medzera), druhý určuje, či sa majú prázdne bunky preskočiť: 1 znamená áno.
Pre Hotel De Haven sa potom zobrazí "Krentenbollen, Croissants, Tompoucen".

Upozornenie: ako druhý argument zadajte 1 alebo TRUE, nie preložený názov, napríklad holandské WAAR. Ten Euro-Office bez zátvoriek nepozná a zobrazí #NAME? (pozrite si Preberanie vzorcov z Excelu: jazyk funkcií a bodkočiarka). WAAR() so zátvorkami funguje.
Varianty
- Každý produkt na samostatnom riadku v bunke: ako oddeľovač použite CHAR(10), =TEXTJOIN(CHAR(10);TRUE;...), a na karte Hlavná stránka zapnite Obtekanie textu. Hodí sa to pre dodací list.
- S množstvami, napríklad "2x Appeltaart": v zozname objednávok vytvorte pomocný stĺpec so vzorcom =F2&"x "&C2 (s množstvom v stĺpci F) a spojte tento stĺpec.
Úskalia
- Ak má zákazník veľmi veľa produktov, jedna bunka bude neprehľadná. V takom prípade ich pomocou funkcie FILTER zobrazte pod sebou (pozrite si Zoznamy, ktoré sa samy aktualizujú, pomocou FILTER, UNIQUE a SORT).
- Zákazník bez produktov vráti pri funkcii FILTER chybovú hodnotu. Zachyťte ju pomocou IFERROR(...;"").
- Kolega s Excelom 2016 alebo starším uvidí #NAME?. Ak súbor posielate ďalej, najprv výsledok prekonvertujte na hodnoty.
