17. peatükk · 17.5
Iseennast uuendavad loendid funktsioonidega FILTER, UNIQUE ja SORT
Vaata lühivideot
Kliendiloend ilma topeltkirjeteta, ülevaade kõigist tasumata tellimustest, tooteloend käibe järjekorras: varem tegid sellise loendi kopeerides, filtreerides ja sorteerides ning pidid seda kõike kordama, kui midagi muutus. Funktsioonidega FILTER, UNIQUE ja SORT kirjutad ühe valemi, mis koostab terve loendi ja uueneb ise. Euro-Office toetab neid nn dünaamilisi massiive: tulemus „valgub“ üle nii mitme lahtri, kui vaja.
Näitena kasutame Bakkerij De Korenaar töölehte Bestellingen: veerus A on kuupäev, veerus B klient, veerus C toode, veerus D summa ja veerus E, kas tellimus on tasutud (ja või nee).
Unikaalne ja sorteeritud kliendiloend
Sisesta lahtrisse G2 =SORT(UNIQUE(FILTER(B2:B500;B2:B500<>""))) ja vajuta Enter. Rohkem pole vaja: iga klient ilmub üks kord, tähestikulises järjekorras, üksteise all alates lahtrist G2.
- FILTER(B2:B500;B2:B500<>"") jätab tühjad read välja. Nii saad võtta vahemiku piisavalt suure ka hiljem lisanduvate tellimuste jaoks.
- UNIQUE eemaldab topeltkirjed.
- SORT paneb need järjekorda.

Kui lisandub uue kliendi tellimus, näiteks Tandarts Bos real 14, ilmub see kohe õigesse kohta loendis. Kui soovid loendit muuta, muuda valemit esimeses lahtris G2.
Kõik selle kuu tasumata tellimused
FILTER oskab tuua ka terveid ridu, mitme tingimusega:
=FILTER(A2:D500;(E2:E500="nee")*(A2:A500>=EOMONTH(TODAY();-1)+1);"midagi pole avatud")
- Esimene osa on see, mida soovid näha: veerud A kuni D.
- Teine osa on tingimused. Iga tingimus on sulgudes. * nende vahel tähendab ja, + tähendab või.
- EOMONTH(TODAY();-1)+1 on selle kuu esimene päev.
- Kolmas osa on tekst, mis kuvatakse, kui midagi ei leita.

Tähelepanu: tulemus ei võta üle sinu loendi vormingut. Summad saavad tavaliselt vormingu General: 64,50 € kuvatakse kui 64,5. Mõnikord saab kogu tulemus esimese veeru vormingu ja summad kuvatakse kuupäevana, nagu pildil: 36,00 muutub siis kujule 05-02-1900. Mõlemal juhul vali tulemuses summadega veerg ja määra ise vorming Currency või Number (vt Numbrite, kuupäevade ja valuuta vormindamine).
Sorteerimine ja veergude valimine
- Summa järgi, suurim enne: =SORT(FILTER(A2:D500;E2:E500="nee");4;-1). 4 on tulemuse neljas veerg, -1 tähendab kahanevat järjekorda.
- Sorteerimine veeru järgi, mida ei kuvata: =SORTBY(FILTER(B2:B500;E2:E500="nee");FILTER(D2:D500;E2:E500="nee");-1) annab ainult klientide nimed, sorteeritud summa järgi.
- Ainult klient ja summa: =CHOOSECOLS(FILTER(A2:D500;E2:E500="nee");2;4).
Nõuanne: testis läks olemasoleva valemi =SORT(FILTER(...);4;-1) järjekord sassi, kui loendi alla lisandus uus tellimus. Valem, mille sisestad pärast lisamist, sorteerib õigesti. Seepärast kontrolli järjekorda, kui ridu on juurde tulnud. Kui see pole õige, sisesta valem uuesti: klõpsa valemiga lahtril, vajuta F2 ja seejärel Enter.
Midagi on ees
Kui lahtris, kuhu tulemus peaks valguma, on juba midagi, ei saa loend laieneda. Excelis näed siis veaväärtust #SPILL!. Euro-Office jätab valemiga lahtri sel juhul tühjaks; kui sellel klõpsad, näed punktiirjoont ala ümber, kuhu tulemus tahab valguda. Tühjenda selle ala lahtrid ja loend ilmub kohe. Jäta sellise valemi alla ja kõrvale seega vaba ruumi.

Arvutamine tervete veergudega ühes valemis
Mõni valem arvutab korraga terve veeruga, näiteks =SUM(D2:D13*(E2:E13="nee")): kõigi veel tasumata tellimuste summa, siin 258,50. Euro-Office'is töötab selline valem tavalise Enter abil. Valemeid, kus IF töötab terve veeruga teise funktsiooni sees, näiteks =LARGE(IF(B2:B37="Pie";C2:C37);1), ei arvuta Euro-Office tavalise Enter abil õigesti: saad #NUM! või vale arvu. Lõpeta selline valem klahvidega Ctrl+Shift+Enter. Valem saab siis looksulud {…} ja annab õige tulemuse. Sageli saab ka ilma IF-ita, funktsiooniga FILTER: =LARGE(FILTER(C2:C37;B2:B37="Pie");1).
Mis ei toimi
- LET ja LAMBDA ning neile tuginevad funktsioonid, nagu BYROW, MAP ja GROUPBY, Euro-Office'is puuduvad. Neid funktsioone sisaldav Exceli fail annab #NAME?. Kasuta abiveergu või vaata Exceli valemid LET või LAMBDA abil: mida Euro-Office'is selle asemel teha, mida teha selle asemel.
- Kolleeg, kellel on Excel 2019 või vanem, näeb nende funktsioonide puhul #NAME?.
Lõksud
- Terved veerud, nagu B:B, teevad valemi aeglaseks. Vali piisavalt suur fikseeritud vahemik, näiteks B2:B500.
- TRUE ja FALSE ainult sulgudega neis valemites: kasuta TRUE() ja FALSE() või 1 ja 0 (vt Exceli valemite ülevõtmine: ingliskeelsed funktsioonide nimed ja semikoolon).
