17. nodaļa · 17.5
Saraksti, kas atjaunojas paši, ar FILTER, UNIQUE un SORT
Klientu saraksts bez dublikātiem, visu neapmaksāto pasūtījumu pārskats, produktu saraksts pēc apgrozījuma: agrāk šādu sarakstu veidoja, kopējot, filtrējot un kārtojot, un to visu darīja no jauna, tiklīdz kaut kas mainījās. Ar funkcijām FILTER, UNIQUE un SORT Jūs uzrakstāt vienu formulu, kas izveido visu sarakstu un pati to atjaunina. Euro-Office atbalsta šos tā sauktos dinamiskos masīvus: rezultāts "izplešas" pār tik daudz šūnām, cik nepieciešams.
Kā piemēru izmantosim maizes ceptuves Bakkerij De Korenaar darblapu Bestellingen: A kolonnā ir datums, B kolonnā klients, C kolonnā produkts, D kolonnā summa un E kolonnā norāde, vai pasūtījums ir apmaksāts ("ja" vai "nee").
Unikāls, sakārtots klientu saraksts
Šūnā G2 ierakstiet =SORT(UNIQUE(FILTER(B2:B500;B2:B500<>""))) un nospiediet Enter. Vairāk nekas nav jādara: katrs klients parādās vienu reizi, alfabētiskā secībā, vienu zem otra, sākot no G2.
- FILTER(B2:B500;B2:B500<>"") izlaiž tukšās rindas. Tādējādi diapazonu varat ņemt ar rezervi pasūtījumiem, kas tiks pievienoti vēlāk.
- UNIQUE izņem dublikātus.
- SORT tos sakārto.

Ja tiek pievienots jauna klienta pasūtījums, piemēram, Tandarts Bos 14. rindā, tas uzreiz parādās pareizajā vietā sarakstā. Ja vēlaties sarakstu mainīt, labojiet formulu pirmajā šūnā, G2.
Visi šī mēneša neapmaksātie pasūtījumi
FILTER var atlasīt arī veselas rindas, izmantojot vairākus nosacījumus:
=FILTER(A2:D500;(E2:E500="nee")*(A2:A500>=EOMONTH(TODAY();-1)+1);"nav neapmaksātu")
- Pirmā daļa ir tas, ko vēlaties redzēt: kolonnas no A līdz D.
- Otrā daļa ir nosacījumi. Katrs nosacījums ir iekavās. Zīme * starp tiem nozīmē un, zīme + nozīmē vai.
- EOMONTH(TODAY();-1)+1 ir šī mēneša pirmā diena.
- Trešā daļa ir teksts, kas tiek parādīts, ja nekā nav.

Ņemiet vērā: Euro-Office visam rezultātam piešķir pirmās kolonnas formātu. Ja rezultāts sākas ar datumu, arī summas pēdējā kolonnā tiek rādītas kā datums: 36,00 kļūst par 05-02-1900. Atlasiet šo rezultāta kolonnu un paši izvēlieties formātu Valūta vai Skaits (skatiet Skaitļu, datumu un valūtas formatēšana).
Kārtošana un kolonnu izvēle
- Pēc summas, lielākā vispirms: =SORT(FILTER(A2:D500;E2:E500="nee");4;-1). Skaitlis 4 ir rezultāta ceturtā kolonna, -1 nozīmē dilstošā secībā.
- Kārtošana pēc kolonnas, kuru neparādāt: =SORTBY(FILTER(B2:B500;E2:E500="nee");FILTER(D2:D500;E2:E500="nee");-1) parāda tikai klientu vārdus, sakārtotus pēc summas.
- Tikai klients un summa: =CHOOSECOLS(FILTER(A2:D500;E2:E500="nee");2;4).
Kaut kas traucē
Ja šūnā, kurā jānonāk rezultātam, jau kaut kas ir ierakstīts, saraksts nevar izplesties. Programmā Excel tad redzama kļūdas vērtība #SPILL!. Euro-Office šādā gadījumā atstāj šūnu ar formulu tukšu; ja uz tās noklikšķināt, ap apgabalu, kurā jānonāk rezultātam, redzama punktēta līnija. Iztīriet šī apgabala šūnas, un saraksts parādās uzreiz. Tāpēc atstājiet brīvu vietu zem šādas formulas un blakus tai.

Aprēķini ar veselām kolonnām vienā formulā
Dažas formulas veic aprēķinus ar veselu kolonnu uzreiz, piemēram, =SUM(D2:D13*(E2:E13="nee")): visu vēl neapmaksāto pasūtījumu summas, šeit 258,50. Programmā Euro-Office šāda formula darbojas, vienkārši nospiežot Enter. Formulas ar IF pār veselu kolonnu citas funkcijas iekšienē, piemēram, =LARGE(IF(B2:B37="Torte";C2:C37);1), Euro-Office, nospiežot tikai Enter, neaprēķina pareizi: Jūs saņemat #NUM! vai nepareizu skaitli. Pabeidziet šādu formulu ar Ctrl+Shift+Enter. Formula tad tiek ietverta figūriekavās {…} un sniedz pareizu rezultātu. Bieži to var paveikt arī bez IF, izmantojot FILTER: =LARGE(FILTER(C2:C37;B2:B37="Torte");1).
Kas nav iespējams
- LET un LAMBDA, kā arī uz tām balstītās funkcijas, piemēram, BYROW, MAP un GROUPBY, Euro-Office neatbalsta. Excel fails ar šīm funkcijām rāda #NAME?. Izmantojiet palīgkolonnu vai skatiet Excel formulas ar LET vai LAMBDA: ko darīt Euro-Office to vietā, lai uzzinātu, ko darīt to vietā.
- Kolēģis ar Excel 2019 vai vecāku versiju šo funkciju vietā redz #NAME?.
Biežākās kļūdas
- Veselas kolonnas, piemēram, B:B, padara formulu lēnu. Izvēlieties plašu, fiksētu diapazonu, piemēram, B2:B500.
- Loģiskās vērtības šajās formulās rakstiet kā 1 un 0 vai kā TRUE un FALSE (skatiet Formulas pārņemšana no Excel: angļu funkciju nosaukumi un semikols).
