Turinys
Visos klasikinės paieškos ir tipo pakeitimo funkcijos VPR (ŽIŪRĖTI), GPR (HLOOKUP), DAUGIAU ATSKIRTA (MATCH) o tokie kaip jie turi vieną svarbią savybę – jie ieško nuo pradžios iki pabaigos, ty iš kairės į dešinę arba iš viršaus į apačią šaltinio duomenyse. Kai tik randamas pirmasis atitikimas, paieška sustabdoma ir randamas tik pirmasis elemento, kurio mums reikia, atvejis.
Ką daryti, jei reikia rasti ne pirmą, o paskutinį įvykį? Pavyzdžiui, paskutinė kliento operacija, paskutinis mokėjimas, paskutinis užsakymas ir pan.?
1 būdas: paskutinės eilutės radimas naudojant masyvo formulę
Jei pradinėje lentelėje nėra stulpelio su data arba eilutės eilės numeriu (užsakymas, apmokėjimas...), tada mūsų užduotis iš tikrųjų yra rasti paskutinę eilutę, kuri tenkina nurodytą sąlygą. Tai galima padaryti naudojant šią masyvo formulę:
Čia:
- Funkcija IF (JEI) po vieną patikrina visus stulpelio langelius Klientas ir rodo eilutės numerį, jei jame yra mums reikalingas pavadinimas. Eilutės numerį lape mums suteikia funkcija LINIJA (ROW), bet kadangi mums reikia lentelės eilutės numerio, tai papildomai turime atimti 1, nes lentelėje turime antraštę.
- Tada funkcija MAX biurą ar brokerį (MAX) parenka didžiausią reikšmę iš suformuoto eilučių numerių rinkinio, ty paskutinės kliento eilutės numerį.
- Funkcija RODYKLĖ (INDEKSAS) grąžina langelio turinį su rastu paskutiniu numeriu iš bet kurio kito reikiamo lentelės stulpelio (Užsakymo kodas).
Visa tai turi būti įvesta kaip masyvo formulė, t.y:
- „Office 365“ su įdiegtais naujausiais naujinimais ir dinaminių masyvų palaikymu galite tiesiog paspausti įeiti.
- Visose kitose versijose, įvedę formulę, turėsite paspausti spartųjį klavišą "Ctrl"+perėjimas+įeiti, kuri formulės juostoje automatiškai pridės garbanotas petnešas.
2 būdas: atvirkštinė paieška naudojant naują LOOKUP funkciją
Jau parašiau ilgą straipsnį su vaizdo įrašu apie naują funkciją VAIZDAS (XLOOKUP), kuris pasirodė naujausiose „Office“ versijose, kad pakeistų seną VLOOKUP (ŽIŪRĖTI). BROWSE pagalba mūsų užduotis išsprendžiama gana elementariai, nes. šiai funkcijai (skirtingai nei VLOOKUP) galite aiškiai nustatyti paieškos kryptį: iš viršaus į apačią arba iš apačios į viršų – už tai atsakingas paskutinis jos argumentas (-1):
3 būdas. Ieškokite eilutės su naujausia data
Jei šaltinio duomenyse turime stulpelį su serijos numeriu arba data, kuri atlieka panašų vaidmenį, tada užduotis modifikuojama – reikia rasti ne paskutinę (žemiausią) eilutę su atitikmeniu, o eilutę su naujausia ( maksimali) data.
Jau išsamiai aptariau, kaip tai padaryti naudojant klasikines funkcijas, o dabar pabandykime panaudoti naujų dinaminio masyvo funkcijų galią. Siekdami didesnio grožio ir patogumo, mes taip pat paverčiame originalią lentelę į "protingą" lentelę naudodami sparčiuosius klavišus "Ctrl"+T arba komandas Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę).
Su jų pagalba ši „žudikų pora“ labai grakščiai išsprendžia mūsų problemą:
Čia:
- Funkcija pirmiausia FILTRAS (FILTRAS) pasirenka tik tas mūsų lentelės eilutes, kuriose yra stulpelyje Klientas – mums reikalingas vardas.
- Tada funkcija GRADE (RŪšiuoti) surūšiuoja pasirinktas eilutes pagal datą mažėjančia tvarka, o naujausias pasiūlymas yra viršuje.
- Funkcija RODYKLĖ (INDEKSAS) ištraukia pirmą eilutę, ty grąžina paskutinę mums reikalingą sandorį.
- Ir galiausiai, išorinė FILTER funkcija pašalina papildomus 1 ir 3 stulpelius iš rezultatų (Užsakymo kodas и Klientas) ir paliekama tik data ir suma. Tam naudojamas konstantų masyvas. 0;1;0;1}, nustatydami, kuriuos stulpelius norime (1) arba nenorime (0) rodyti.
4 būdas: suraskite paskutinį atitiktį Power Query
Na, o išsamumo sumetimais pažvelkime į atvirkštinės paieškos problemos sprendimą naudodami Power Query priedą. Su jos pagalba viskas išsprendžiama labai greitai ir gražiai.
1. Paverskime originalią lentelę į „išmaniąją“ naudodami sparčiuosius klavišus "Ctrl"+T arba komandas Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę).
2. Įkelkite jį į „Power Query“ naudodami mygtuką Iš lentelės/diapazono kortelė Duomenys (Duomenys – iš lentelės/diapazono).
3. Rūšiuojame (per antraštėje esantį filtro išskleidžiamąjį sąrašą) lentelę mažėjančia datos tvarka, kad naujausios operacijos būtų viršuje.
4… Skirtuke Transformacija pasirinkti komandą Grupuoti pagal (Transformuoti – Grupuoti pagal) ir nustatykite grupavimą pagal klientus ir kaip agregavimo funkciją pasirinkite parinktį Visos eilutės (Visos eilutės). Galite pavadinti naują stulpelį bet kuo, pavyzdžiui, kaip norite Detalės.
Po sugrupavimo gausime unikalių mūsų klientų vardų sąrašą ir stulpelyje Detalės – lentelės su visomis kiekvienos iš jų operacijomis, kur pirmoji eilutė bus paskutinė operacija, kurios mums reikia:
5. Pridėkite naują apskaičiuotą stulpelį mygtuku Pasirinktinis stulpelis kortelė Pridėti stulpelį (Pridėti stulpelį – pridėti pasirinktinį stulpelį)ir įveskite šią formulę:
Čia Detalės – tai yra stulpelis, iš kurio paimame lenteles pagal klientus ir 0 {} yra eilutės, kurią norime išgauti, numeris (eilių numeracija Power Query prasideda nuo nulio). Gauname stulpelį su įrašais (Įrašas), kur kiekvienas įrašas yra pirmoji kiekvienos lentelės eilutė:
Belieka išplėsti visų įrašų turinį mygtuku su dvigubomis rodyklėmis stulpelio antraštėje Paskutinis sandoris pasirenkant norimus stulpelius:
… ir ištrinkite stulpelį, kurio nebereikia Detalės dešiniuoju pelės mygtuku spustelėdami jo pavadinimą - Pašalinti stulpelius (Pašalinti stulpelius).
Įkėlę rezultatus į lapą per Pagrindinis puslapis — Uždaryti ir įkelti — Uždaryti ir įkelti (Pagrindinis – Uždaryti ir įkelti – Uždaryti ir įkelti į…) gausime tokią gražią lentelę su naujausių operacijų sąrašu, kaip ir norėjome:
Keičiant šaltinio duomenis, reikia nepamiršti atnaujinti rezultatų paspaudus juos dešiniuoju pelės klavišu – komanda Atnaujinkite ir išsaugokite (Atnaujinti) arba spartusis klavišas "Ctrl"+Kitas+F5.
- Funkcija LOOKUP yra VLOOKUP palikuonis
- Kaip naudoti naujas dinaminio masyvo funkcijas SORT, FILTER ir UNIC
- Paskutinio netuščio langelio radimas eilutėje arba stulpelyje naudojant LOOKUP funkciją