17. peatükk · 17.17
Kõik kliendi tooted ühte lahtrisse, komadega eraldatult
Tellimuste loendis on iga toote kohta üks rida. Saatelehe, meilisaadetise või ülevaate jaoks on aga vaja üht rida kliendi kohta: „Hotel De Haven: Krentenbollen, Croissants, Tompoucen“. Varem sai seda teha ainult makroga. Funktsioonidega TEXTJOIN ja FILTER piisab ühest valemist.
Näitena kasutame jälle pagariäri Bakkerij De Korenaar töölehte Bestellingen: veerus B on klient ja veerus C toode, ridadel 2 kuni 13.
Tähelepanu: Exceli veebisaitidel näed ka varianti, kus FILTER asemel on IF: =TEXTJOIN(", ";1;IF(Bestellingen!B2:B13=C2;Bestellingen!C2:C13;"")). Euro-Office'is annab see tavalise Enter vajutamisel tühja lahtri. Kombinatsiooniga Ctrl+Shift+Enter see töötab, kuid siis jäävad korduvad tooted alles. FILTER koos funktsiooniga UNIQUE on korrektsem.
1. samm: iga klient üks kord
Koosta ülevaade teisele töölehele, näiteks nii, et kliendid on veerus C. Sisesta lahtrisse C2 =SORT(UNIQUE(Bestellingen!B2:B13)). Saad kliendid tähestikulises järjekorras üksteise alla (vt Iseennast uuendavad loendid funktsioonidega FILTER, UNIQUE ja SORT).
2. samm: tooted kliendi kaupa
Vali lahtrid alates E2 kuni viimase kliendi reani (siin E2:E5) ja sisesta:
=TEXTJOIN(", ";1;UNIQUE(FILTER(Bestellingen!$C$2:$C$13;Bestellingen!$B$2:$B$13=C2)))
ning vajuta Ctrl+Enter, et valem tuleks iga kliendi kõrvale.
- FILTER toob kõik lahtris C2 oleva kliendi tooted.
- UNIQUE tagab, et toodet, mida klient tellis mitu korda, nimetatakse ainult üks kord. Kui tahad kordusi näha, jäta UNIQUE ära.
- TEXTJOIN liidab need kokku. Esimene argument on eraldaja (koma ja tühik), teine määrab, kas tühjad lahtrid jäetakse vahele: 1 tähendab jah.
Hotel De Haven kõrval on siis „Krentenbollen, Croissants, Tompoucen“.

Tähelepanu: sisesta teiseks argumendiks 1 või TRUE, mitte eestikeelse Exceli TÕENE. Euro-Office seda ei tunne ja annab tulemuseks #NAME? (vt Exceli valemite ülevõtmine: ingliskeelsed funktsioonide nimed ja semikoolon).
Variandid
- Iga toode eraldi real lahtri sees: kasuta eraldajana CHAR(10), =TEXTJOIN(CHAR(10);TRUE;...), ja lülita vahekaardil Home (Avaleht) sisse Wrap text (murra tekst). Mugav saatelehe jaoks.
- Koos kogustega, näiteks „2x Appeltaart“: loo tellimuste loendisse abiveerg valemiga =F2&"x "&C2 (kogus on veerus F) ja ühenda see veerg.
Lõksud
- Kui kliendil on väga palju tooteid, muutub üks lahter ülevaatamatuks. Sel juhul pane need funktsiooniga FILTER üksteise alla (vt Iseennast uuendavad loendid funktsioonidega FILTER, UNIQUE ja SORT).
- Ilma toodeteta klient annab funktsiooniga FILTER veaväärtuse. Püüa see kinni valemiga IFERROR(...;"").
- Kolleeg, kellel on Excel 2016 või vanem, näeb #NAME?. Kui saadad faili edasi, teisenda tulemus enne väärtusteks.
