Saturs
Visas klasiskās meklēšanas un tipa aizstāšanas funkcijas VPR (MEKLĒT), GPR (HLOOKUP), VAIRĀK ATKLĀTA (MATCH) un tiem līdzīgiem ir viena svarīga iezīme – viņi meklē no sākuma līdz beigām, ti, no kreisās uz labo vai no augšas uz leju avota datos. Tiklīdz tiek atrasta pirmā atbilstošā atbilstība, meklēšana tiek pārtraukta un tiek atrasts tikai pirmais vajadzīgā elementa gadījums.
Ko darīt, ja jāatrod nevis pirmais, bet pēdējais gadījums? Piemēram, pēdējais darījums klientam, pēdējais maksājums, pēdējais pasūtījums utt.?
1. metode: pēdējās rindas atrašana ar masīva formulu
Ja sākotnējā tabulā nav kolonnas ar datumu vai rindas kārtas numuru (pasūtījums, maksājums …), tad mūsu uzdevums faktiski ir atrast pēdējo rindu, kas atbilst dotajam nosacījumam. To var izdarīt ar šādu masīva formulu:
Šeit:
- funkcija IF (JA) pārbauda visas kolonnas šūnas pa vienai Klients un parāda rindas numuru, ja tajā ir mums vajadzīgais nosaukums. Rindas numuru uz lapas mums piešķir funkcija LINE (ROW), bet, tā kā mums ir nepieciešams tabulas rindas numurs, mums papildus ir jāatņem 1, jo mums tabulā ir galvene.
- Pēc tam funkcija MAX (MAX) izvēlas maksimālo vērtību no izveidotās rindu numuru kopas, ti, klienta pēdējās rindas numuru.
- funkcija INDEX (INDEX) atgriež šūnas saturu ar atrasto pēdējo numuru no jebkuras citas nepieciešamās tabulas kolonnas (Pasūtījuma kods).
Tas viss jāievada kā masīva formula, ti:
- Programmā Office 365, kurā ir instalēti jaunākie atjauninājumi un atbalsts dinamiskajiem masīviem, varat vienkārši nospiest ienākt.
- Visās pārējās versijās pēc formulas ievadīšanas būs jānospiež īsinājumtaustiņš Ctrl+maiņa+ienākt, kas formulas joslā tai automātiski pievienos cirtainas breketes.
2. metode: apgrieztā meklēšana, izmantojot jauno funkciju LOOKUP
Es jau uzrakstīju garu rakstu ar video par jaunu funkciju SKATĪT (XLOOKUP), kas parādījās jaunākajās Office versijās, lai aizstātu veco VLOOKUP (MEKLĒT). Ar BROWSE palīdzību mūsu uzdevums tiek atrisināts diezgan elementāri, jo. šai funkcijai (atšķirībā no VLOOKUP) varat skaidri iestatīt meklēšanas virzienu: no augšas uz leju vai uz augšu - tās pēdējais arguments (-1) ir atbildīgs par to:
3. metode. Meklējiet virkni ar jaunāko datumu
Ja avota datos mums ir kolonna ar sērijas numuru vai datumu, kas spēlē līdzīgu lomu, tad uzdevums tiek modificēts – jāatrod nevis pēdējā (zemākā) rinda ar atbilstību, bet gan rinda ar jaunāko ( maksimālais) datums.
Es jau detalizēti apspriedu, kā to izdarīt, izmantojot klasiskās funkcijas, un tagad mēģināsim izmantot jauno dinamiskā masīva funkciju jaudu. Lielākam skaistumam un ērtībai, izmantojot īsinājumtaustiņus, mēs arī pārvēršam sākotnējo tabulu par “viedo” tabulu. Ctrl+T vai komandas Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu).
Ar viņu palīdzību šis “slepkavas pāris” ļoti graciozi atrisina mūsu problēmu:
Šeit:
- Funkcija vispirms FILTER (FILTER) atlasa tikai tās rindas no mūsu tabulas, kas atrodas kolonnā Klients – mums vajadzīgais nosaukums.
- Pēc tam funkcija GRADE (KĀRTOT) sakārto atlasītās rindas pēc datuma dilstošā secībā, un augšpusē ir jaunākais piedāvājums.
- funkcija INDEX (INDEX) izvelk pirmo rindu, ti, atgriež pēdējo mums vajadzīgo darījumu.
- Un, visbeidzot, ārējā FILTER funkcija noņem no rezultātiem papildu 1. un 3. kolonnu (Pasūtījuma kods и Klients) un atstāj tikai datumu un summu. Šim nolūkam tiek izmantots konstantu masīvs. 0;1;0;1}, nosakot, kuras kolonnas mēs vēlamies (1) vai nevēlamies (0) rādīt.
4. metode: pēdējās atbilstības atrašana programmā Power Query
Pilnības labad apskatīsim mūsu apgrieztās meklēšanas problēmas risinājumu, izmantojot Power Query pievienojumprogrammu. Ar viņas palīdzību viss tiek atrisināts ļoti ātri un skaisti.
1. Pārveidosim savu sākotnējo tabulu par “gudru”, izmantojot īsinājumtaustiņus Ctrl+T vai komandas Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu).
2. Ielādējiet to programmā Power Query, izmantojot pogu No tabulas/diapazona tab Datums (Dati — no tabulas/diapazona).
3. Mēs kārtojam tabulu (izmantojot galvenē esošā filtra nolaižamo sarakstu) dilstošā datuma secībā, lai jaunākie darījumi būtu augšpusē.
4… Cilnē transformācija izvēlēties komandu Grupēt pēc (Pārveidot — grupēt pēc) un iestatiet grupēšanu pēc klientiem un kā apkopošanas funkciju atlasiet opciju Visas līnijas (Visas rindas). Varat nosaukt jauno kolonnu jebko, kas jums patīk, piemēram Sīkāka informācija.
Pēc grupēšanas mēs iegūsim mūsu klientu unikālo vārdu sarakstu un kolonnā Sīkāka informācija - tabulas ar visām katra no tām transakcijām, kur pirmajā rindā būs pēdējais darījums, kas mums ir nepieciešams:
5. Pievienojiet jaunu aprēķināto kolonnu ar pogu Pielāgota kolonna tab Pievienot kolonnu (Pievienot kolonnu — pievienot pielāgotu kolonnu)un ievadiet šādu formulu:
Šeit Sīkāka informācija – šī ir kolonna, no kuras mēs ņemam tabulas pēc klientiem, un 0 {} ir rindas numurs, kuru vēlamies izvilkt (rindu numerācija programmā Power Query sākas no nulles). Mēs iegūstam kolonnu ar ierakstiem (Ieraksts), kur katrs ieraksts ir katras tabulas pirmā rinda:
Atliek izvērst visu ierakstu saturu ar pogu ar dubultbultiņām kolonnas galvenē Pēdējais darījums izvēloties vajadzīgās kolonnas:
… un pēc tam izdzēsiet kolonnu, kas vairs nav vajadzīga Sīkāka informācija ar peles labo pogu noklikšķinot uz tā nosaukuma - Noņemiet kolonnas (Noņemt kolonnas).
Pēc rezultātu augšupielādes lapā caur Sākums — Aizveriet un ielādējiet — Aizveriet un ielādējiet (Sākums — aizvērt un ielādēt — aizvērt un ielādēt...) mēs iegūsim tik jauku tabulu ar pēdējo darījumu sarakstu, kā mēs vēlējāmies:
Mainot avota datus, nedrīkst aizmirst atjaunināt rezultātus, ar peles labo pogu noklikšķinot uz tiem – komanda Atjaunināt un saglabāt (Atjaunot) vai īsinājumtaustiņš Ctrl+cits+F5.
- Funkcija LOOKUP ir VLOOKUP pēctecis
- Kā izmantot jaunās dinamiskā masīva funkcijas SORT, FILTER un UNIC
- Pēdējās netukšās šūnas atrašana rindā vai kolonnā, izmantojot funkciju LOOKUP