Sisu
Kõik klassikalised otsingu- ja tüübiasendusfunktsioonid VPR (OTSING), GPR (HLOOKUP), ROHKEM AVALDATUD (MATCH) ja nendesarnastel on üks oluline omadus – nad otsivad algusest lõpuni ehk lähteandmetes vasakult paremale või ülalt alla. Niipea, kui leitakse esimene sobiv vaste, otsing peatub ja leitakse ainult esimene vajalik element.
Mida teha, kui peame leidma mitte esimese, vaid viimase juhtumi? Näiteks viimane tehing kliendi jaoks, viimane makse, viimane tellimus jne?
1. meetod: viimase rea leidmine massiivi valemiga
Kui algses tabelis ei ole veergu kuupäeva või rea seerianumbriga (tellimus, makse ...), siis on meie ülesanne tegelikult leida viimane rida, mis vastab antud tingimusele. Seda saab teha järgmise massiivi valemiga:
siin:
- funktsioon IF (KUI) kontrollib ükshaaval kõiki veeru lahtreid Klient ja kuvab rea numbri, kui see sisaldab meile vajalikku nime. Lehel oleva rea numbri annab meile funktsioon LINE (RIDA), aga kuna meil on tabelis vaja reanumbrit, siis lisaks peame lahutama 1, kuna meil on tabelis päis.
- Siis funktsioon MAX (MAX) valib moodustatud reanumbrite hulgast maksimaalse väärtuse ehk kliendi kõige värskema rea numbri.
- funktsioon INDEX (INDEX) tagastab lahtri sisu leitud viimase numbriga mis tahes muust nõutavast tabeli veerust (Tellimiskood).
Kõik see tuleb sisestada kui massiivi valemst:
- Office 365-s, kus on installitud uusimad värskendused ja dünaamiliste massiivide tugi, võite lihtsalt vajutada sisene.
- Kõigis muudes versioonides peate pärast valemi sisestamist vajutama kiirklahvi Ctrl+nihe+sisene, mis lisab sellele valemiribale automaatselt lokkis sulgud.
2. meetod: pöördotsing uue LOOKUP funktsiooniga
Kirjutasin juba pika artikli koos videoga uuest funktsioonist Vaata (XLOOKUP), mis ilmus Office'i uusimates versioonides, et asendada vana VLOOKUP (OTSING). BROWSE abil lahendatakse meie ülesanne üsna elementaarselt, sest. Selle funktsiooni jaoks (erinevalt VLOOKUP-ist) saate otse määrata otsingu suuna: ülalt alla või alt üles – selle viimane argument (-1) vastutab selle eest:
3. meetod. Otsige uusima kuupäevaga stringi
Kui lähteandmetes on veerg seerianumbri või kuupäevaga, mis mängib sarnast rolli, siis ülesannet muudetakse – peame leidma mitte viimase (madalaima) vastega rea, vaid rea, millel on viimane ( maksimaalne) kuupäev.
Olen juba üksikasjalikult arutanud, kuidas seda klassikaliste funktsioonide abil teha, ja proovime nüüd kasutada uute dünaamiliste massiivi funktsioonide võimsust. Suurema ilu ja mugavuse huvides teisendame ka algse tabeli "nutikaks" tabeliks, kasutades kiirklahvi Ctrl+T või käske Avaleht – vorminda tabelina (Avaleht — vorminda tabelina).
Nende abiga lahendab see "tapjapaar" meie probleemi väga elegantselt:
siin:
- Funktsioon kõigepealt FILTER (FILTER) valib meie tabelist ainult need read, kus on veerus Klient – nimi, mida me vajame.
- Siis funktsioon GRADE (SORT) sorteerib valitud read kuupäeva järgi kahanevas järjekorras, kusjuures viimane pakkumine on ülaosas.
- funktsioon INDEX (INDEX) ekstraheerib esimese rea, st tagastab viimase tehingu, mida vajame.
- Ja lõpuks, väline FILTER funktsioon eemaldab tulemustest täiendavad 1. ja 3. veeru (Tellimiskood и Klient) ja jätab ainult kuupäeva ja summa. Selleks kasutatakse konstantide massiivi. 0;1;0;1}, määrates, milliseid veerge me tahame (1) või ei soovi (0) kuvada.
4. meetod: viimase vaste leidmine Power Querys
Vaatame täielikkuse huvides lahendust meie pöördotsingu probleemile, kasutades Power Query lisandmoodulit. Tema abiga laheneb kõik väga kiiresti ja kaunilt.
1. Teisendame oma algse tabeli klaviatuuri otsetee abil nutikaks Ctrl+T või käske Avaleht – vorminda tabelina (Avaleht — vorminda tabelina).
2. Laadige see Power Querysse nupuga Tabelist/vahemikust tab kuupäev (Andmed – tabelist/vahemikust).
3. Sorteerime (päises oleva filtri rippmenüü kaudu) oma tabeli kuupäeva kahanevas järjekorras, nii et kõige värskemad tehingud oleksid üleval.
4… Vahekaardil Transformation vali meeskond Grupi poolt (Teisendus – rühmitamine) ja määrake rühmitus klientide järgi ning valige liitmisfunktsioonina suvand Kõik read (Kõik read). Saate uuele veerule anda ükskõik millise nime – näiteks Detailid.
Pärast rühmitamist saame oma klientide ainulaadsete nimede loendi ja veergu Detailid - tabelid kõigi nende kõigi tehingutega, kus esimene rida on viimane tehing, mida me vajame:
5. Lisage nupuga uus arvutatud veerg Kohandatud veerg tab Lisage veerg (Lisa veerg – lisa kohandatud veerg)ja sisestage järgmine valem:
Siin Detailid – see on veerg, millest võtame klientide kaupa tabeleid ja 0 {} on rea number, mille tahame eraldada (Power Query ridade nummerdamine algab nullist). Saame veeru kirjetega (Rekord), kus iga kirje on iga tabeli esimene rida:
Jääb üle kõigi kirjete sisu laiendada topeltnooltega nupuga veeru päises Viimane tehing soovitud veergude valimine:
… ja seejärel kustutage veerg, mida enam ei vajata Detailid paremklõpsates selle pealkirjal - Eemalda veerud (Eemalda veerud).
Pärast tulemuste üleslaadimist lehele läbi Avaleht — Sulgege ja laadige — Sulgege ja laadige sisse (Avaleht – sulge ja laadi – sulge ja laadi…) saame sellise ilusa tabeli viimaste tehingute nimekirjaga, nagu soovisime:
Lähteandmete muutmisel ei tohi unustada tulemusi uuendada, tehes neil paremklõpsu – käsk Värskenda ja salvesta (Värskenda) või klaviatuuri otsetee Ctrl+muu+F5.
- Funktsioon LOOKUP on funktsiooni VLOOKUP järglane
- Kuidas kasutada uusi dünaamilise massiivi funktsioone SORT, FILTER ja UNIC
- Viimase mittetühja lahtri leidmine reast või veerust funktsiooni LOOKUP abil