17 skyrius · 17.5
Sąrašai, kurie atsinaujina patys, su FILTER, UNIQUE ir SORT
Klientų sąrašas be pasikartojimų, visų neapmokėtų užsakymų apžvalga, produktų sąrašas pagal apyvartą: anksčiau tokį sąrašą sudarydavote kopijuodami, filtruodami ir rūšiuodami, o viską pasikeitus tekdavo daryti iš naujo. Su funkcijomis FILTER, UNIQUE ir SORT parašote vieną formulę, kuri sukuria visą sąrašą ir atsinaujina pati. Euro-Office palaiko vadinamuosius dinaminius masyvus: rezultatas „išsilieja“ per tiek langelių, kiek reikia.
Pavyzdžiui imame kepyklos Bakkerij De Korenaar darbalapį Bestellingen: A stulpelyje yra data, B klientas, C produktas, D suma, o E nurodyta, ar užsakymas apmokėtas (ja arba nee).
Unikalus, surūšiuotas klientų sąrašas
G2 langelyje įveskite =SORT(UNIQUE(FILTER(B2:B500;B2:B500<>""))) ir paspauskite Enter. Daugiau nieko nereikia: kiekvieną klientą gausite po vieną kartą, abėcėlės tvarka, vieną po kito nuo G2.
- FILTER(B2:B500;B2:B500<>"") praleidžia tuščias eilutes. Taip galite pasirinkti platesnį diapazoną užsakymams, kurie bus pridėti vėliau.
- UNIQUE pašalina pasikartojimus.
- SORT juos surikiuoja.

Jei pridedamas naujo kliento užsakymas, pavyzdžiui, Tandarts Bos 14 eilutėje, jis iš karto atsiranda tinkamoje sąrašo vietoje. Jei norite pakeisti sąrašą, keiskite formulę pirmajame langelyje, G2.
Visi šio mėnesio neapmokėti užsakymai
FILTER gali grąžinti ir ištisas eilutes, taikydama kelias sąlygas:
=FILTER(A2:D500;(E2:E500="nee")*(A2:A500>=EOMONTH(TODAY();-1)+1);"nėra neapmokėtų")
- Pirmoji dalis yra tai, ką norite matyti: stulpeliai nuo A iki D.
- Antroji dalis yra sąlygos. Kiekviena sąlyga rašoma skliaustuose. * tarp jų reiškia ir, + reiškia arba.
- EOMONTH(TODAY();-1)+1 yra pirmoji šio mėnesio diena.
- Trečioji dalis yra tekstas, rodomas, kai nieko nerasta.

Dėmesio: Euro-Office visam rezultatui pritaiko pirmojo stulpelio formatą. Jei rezultatas prasideda data, sumos paskutiniame stulpelyje taip pat rodomos kaip data: 36,00 tampa 05-02-1900. Pažymėkite tą rezultato stulpelį ir patys pasirinkite formatą Currency (valiuta) arba Number (skaičius) (žr. Skaičių, datų ir valiutos formatavimas).
Rūšiavimas ir stulpelių pasirinkimas
- Pagal sumą, didžiausia pirmiau: =SORT(FILTER(A2:D500;E2:E500="nee");4;-1). 4 yra ketvirtasis rezultato stulpelis, -1 reiškia mažėjančia tvarka.
- Rūšiavimas pagal stulpelį, kurio nerodote: =SORTBY(FILTER(B2:B500;E2:E500="nee");FILTER(D2:D500;E2:E500="nee");-1) pateikia tik klientų pavadinimus, surūšiuotus pagal sumą.
- Tik klientas ir suma: =CHOOSECOLS(FILTER(A2:D500;E2:E500="nee");2;4).
Kažkas trukdo
Jei langelyje, į kurį turi patekti rezultatas, jau kažkas yra, sąrašas negali išsilieti. Excel tuomet rodo klaidos reikšmę #SPILL!. Euro-Office tokiu atveju palieka formulės langelį tuščią; jį spustelėję pamatysite punktyrinę liniją aplink sritį, į kurią turi patekti rezultatas. Išvalykite tos srities langelius ir sąrašas iš karto atsiras. Todėl po tokia formule ir šalia jos palikite laisvos vietos.

Skaičiavimai su ištisais stulpeliais vienoje formulėje
Kai kurios formulės skaičiuoja su visu stulpeliu iš karto, pavyzdžiui, =SUM(D2:D13*(E2:E13="nee")): visų dar neapmokėtų užsakymų sumos, šiuo atveju 258,50. Euro-Office tokia formulė veikia paprasčiausiai paspaudus Enter. Formulių su IF per visą stulpelį kitos funkcijos viduje, pavyzdžiui, =LARGE(IF(B2:B37="Pie";C2:C37);1), Euro-Office paspaudus tik Enter neapskaičiuoja teisingai: gausite #NUM! arba neteisingą skaičių. Tokią formulę užbaikite klavišais Ctrl+Shift+Enter. Formulė tada gauna riestinius skliaustus {…} ir pateikia teisingą rezultatą. Dažnai galima apsieiti ir be IF, naudojant FILTER: =LARGE(FILTER(C2:C37;B2:B37="Pie");1).
Ko padaryti negalima
- LET ir LAMBDA, taip pat jomis paremtų funkcijų, tokių kaip BYROW, MAP ir GROUPBY, Euro-Office nepalaiko. Excel failas su šiomis funkcijomis rodo #NAME?. Naudokite pagalbinį stulpelį arba žr. Excel-formules met LET of LAMBDA: wat je in Euro-Office in plaats daarvan doet, kaip galima apsieiti kitaip.
- Kolega, naudojantis Excel 2019 ar senesnę versiją, prie šių funkcijų matys #NAME?.
Spąstai
- Ištisi stulpeliai, pavyzdžiui, B:B, sulėtina formulę. Pasirinkite platų, fiksuotą diapazoną, pavyzdžiui, B2:B500.
- Jokių TRUE ar FALSE be skliaustų šiose formulėse; naudokite 1 ir 0 arba TRUE ir FALSE (žr. Formulių perkėlimas iš Excel: Jūsų kalbos funkcijų pavadinimai ir kabliataškis).
