Luku 17 · 17.17
Kaikki asiakkaan tuotteet yhteen soluun pilkuilla eroteltuina
Tilausluettelossa on yksi rivi tuotetta kohden. Lähetteeseen, postitukseen tai yhteenvetoon tarvitset kuitenkin yhden rivin asiakasta kohden: "Hotel De Haven: Krentenbollen, Croissants, Tompoucen". Ennen tämä onnistui vain makrolla. TEKSTI.YHDISTÄ- ja SUODATA-funktioilla siihen riittää yksi kaava.
Esimerkkinä käytämme taas Bakkerij De Korenaarin laskentataulukkoa Bestellingen: sarakkeessa B on asiakas ja sarakkeessa C tuote, riveillä 2-13.
Huomaa: Excel-sivustoilla näet myös version, jossa SUODATA-funktion sijaan käytetään JOS-funktiota: =TEKSTI.YHDISTÄ(", ";1;JOS(Bestellingen!B2:B13=C2;Bestellingen!C2:C13;"")). Euro-Officessa se antaa pelkällä Enter-näppäimellä tyhjän solun. Näppäinyhdistelmällä Ctrl+Vaihto+Enter se toimii, mutta silloin samat tuotteet jäävät näkyviin useaan kertaan. SUODATA yhdessä AINUTKERTAISET.ARVOT-funktion kanssa on siistimpi ratkaisu.
Vaihe 1: jokainen asiakas kerran
Tee yhteenveto toiseen laskentataulukkoon, esimerkiksi niin, että asiakkaat ovat sarakkeessa C. Kirjoita soluun C2 =LAJITTELE(AINUTKERTAISET.ARVOT(Bestellingen!B2:B13)). Saat asiakkaat aakkosjärjestyksessä allekkain (katso Itsestään päivittyvät luettelot: SUODATA, AINUTKERTAISET.ARVOT ja LAJITTELE).
Vaihe 2: tuotteet asiakkaittain
Valitse solut E2:sta viimeisen asiakkaan riviin asti (tässä E2:E5) ja kirjoita:
=TEKSTI.YHDISTÄ(", ";1;AINUTKERTAISET.ARVOT(SUODATA(Bestellingen!$C$2:$C$13;Bestellingen!$B$2:$B$13=C2)))
Paina sitten Ctrl+Enter, niin kaava tulee jokaisen asiakkaan viereen.
- SUODATA hakee kaikki solussa C2 olevan asiakkaan tuotteet.
- AINUTKERTAISET.ARVOT huolehtii siitä, että tuote, jonka asiakas tilasi useammin, mainitaan vain kerran. Jos haluat nähdä toistot, jätä AINUTKERTAISET.ARVOT pois.
- TEKSTI.YHDISTÄ liittää tuotteet yhteen. Ensimmäinen argumentti on erotin (pilkku ja välilyönti), toinen kertoo, ohitetaanko tyhjät solut: 1 tarkoittaa kyllä.
Hotel De Havenin kohdalla lukee silloin "Krentenbollen, Croissants, Tompoucen".

Huomaa: kirjoita toiseksi argumentiksi 1 tai TRUE, älä TOSI. Euro-Office ei tunnista TOSI-arvoa ilman sulkeita ja antaa silloin virheen #NAME? (katso Excelin kaavojen siirtäminen: suomalaiset funktioiden nimet ja puolipiste). TOSI() sulkeineen toimii.
Muunnelmat
- Jokainen tuote omalle rivilleen solun sisällä: käytä erottimena MERKKI(10)-funktiota, =TEKSTI.YHDISTÄ(MERKKI(10);TRUE;...), ja ota Tekstin rivittäminen käyttöön Etusivu-välilehdellä. Kätevä lähetteessä.
- Määrien kanssa, esimerkiksi "2x Appeltaart": lisää tilausluetteloon apusarake kaavalla =F2&"x "&C2 (määrä sarakkeessa F) ja yhdistä tämä sarake.
Sudenkuopat
- Jos asiakkaalla on hyvin paljon tuotteita, yhdestä solusta tulee sekava. Listaa ne silloin SUODATA-funktiolla allekkain (katso Itsestään päivittyvät luettelot: SUODATA, AINUTKERTAISET.ARVOT ja LAJITTELE).
- Asiakas, jolla ei ole tuotteita, antaa SUODATA-funktiolla virhearvon. Ota se kiinni funktiolla JOSVIRHE(...;"").
- Kollega, jolla on Excel 2016 tai vanhempi, näkee virheen #NAME?. Muunna tulos ensin arvoiksi, jos lähetät tiedoston eteenpäin.
