Efnisyfirlit
Allar klassískar leitar- og tegundaskiptiaðgerðir VPR (SKRÁNING), GPR (ÚTLIT), FYRIR MEIRA (MATCH) og þeir eins og þeir hafa einn mikilvægan eiginleika - þeir leita frá upphafi til enda, þ.e. frá vinstri til hægri eða efst til botns í upprunagögnunum. Um leið og fyrsta samsvörunin er fundin hættir leitin og aðeins fyrsta tilvikið af þættinum sem við þurfum finnst.
Hvað á að gera ef við þurfum að finna ekki fyrsta, heldur síðasta atvikið? Til dæmis, síðasta færslan fyrir viðskiptavininn, síðasta greiðslan, nýjasta pöntun o.s.frv.?
Aðferð 1: Að finna síðustu línuna með fylkisformúlu
Ef upprunalega taflan er ekki með dálk með dagsetningu eða raðnúmeri línu (pöntun, greiðsla ...), þá er verkefni okkar í raun að finna síðustu línuna sem uppfyllir tiltekið skilyrði. Þetta er hægt að gera með eftirfarandi fylkisformúlu:
hér:
- virka IF (EF) athugar allar frumur í dálki einn í einu Viðskiptavinur og sýnir línunúmerið ef það inniheldur nafnið sem við þurfum. Línunúmerið á blaðinu er gefið okkur af fallinu LINE (ROW), en þar sem við þurfum línunúmerið í töflunni verðum við að auki að draga 1 frá, því við höfum haus í töflunni.
- Síðan aðgerðin MAX (Max) velur hámarksgildi úr mynduðu setti línunúmera, þ.e. númer nýjustu línu viðskiptavinarins.
- virka INDEX (VÍSITALA) skilar innihaldi reitsins með síðustu tölu sem fannst úr öðrum nauðsynlegum töfludálki (Pöntunarnúmer).
Allt þetta verður að færa inn sem fylkisformúla, þ.e.:
- Í Office 365 með nýjustu uppfærslunum uppsettum og stuðningi fyrir kraftmikla fylki geturðu einfaldlega ýtt á Sláðu inn.
- Í öllum öðrum útgáfum, eftir að hafa slegið inn formúluna, verður þú að ýta á flýtilykla Ctrl+Shift+Sláðu inn, sem mun sjálfkrafa bæta krulluðum axlaböndum við það á formúlustikunni.
Aðferð 2: Snúið uppflettingu með nýju LOOKUP aðgerðinni
Ég skrifaði þegar langa grein með myndbandi um nýjan eiginleika SKOÐA (XLOOKUP), sem birtist í nýjustu útgáfum af Office til að koma í stað gamla VLOOKUP (SKRÁNING). Með hjálp BROWSE er verkefnið okkar leyst á einfaldan hátt, því. fyrir þessa aðgerð (ólíkt VLOOKUP), geturðu beinlínis stillt leitarstefnuna: ofan frá eða niður - síðasta rök hennar (-1) er ábyrg fyrir þessu:
Aðferð 3. Leitaðu að streng með nýjustu dagsetningu
Ef í upprunagögnunum höfum við dálk með raðnúmeri eða dagsetningu sem gegnir svipuðu hlutverki, þá er verkefninu breytt – við þurfum ekki að finna síðustu (neðstu) línuna með samsvörun, heldur línuna með nýjustu ( hámarks) dagsetningu.
Ég hef þegar fjallað ítarlega um hvernig á að gera þetta með því að nota klassískar aðgerðir, og nú skulum við reyna að nota kraftinn í nýju kraftmiklu fylkisaðgerðunum. Fyrir meiri fegurð og þægindi breytum við upprunalegu töflunni líka í „snjallt“ borð með því að nota flýtilykla Ctrl+T eða skipanir Heim - Snið sem töflu (Heima - Snið sem töflu).
Með hjálp þeirra leysir þetta „morðingjapar“ vandamál okkar á mjög þokkafullan hátt:
hér:
- Virka fyrst FILTER (SÍA) velur aðeins þær línur úr töflunni okkar þar sem í dálknum Viðskiptavinur - nafnið sem við þurfum.
- Síðan aðgerðin GRADE (RAÐAÐA) flokkar valdar línur eftir dagsetningu í lækkandi röð, með nýjasta tilboðið efst.
- virka INDEX (VÍSITALA) dregur út fyrstu röðina, þ.e. skilar síðustu viðskiptum sem við þurfum.
- Og að lokum, ytri FILTER aðgerðin fjarlægir auka 1. og 3. dálk úr niðurstöðunum (Pöntunarnúmer и Viðskiptavinur) og skilur aðeins eftir dagsetningu og upphæð. Til þess er fylking fasta notaðar. {0;1;0;1}, sem skilgreinir hvaða dálka við viljum (1) eða viljum ekki að (0) birtist.
Aðferð 4: Að finna síðustu samsvörun í Power Query
Jæja, til að vera fullkomin, skulum við skoða lausn á öfugri leitarvandamálinu okkar með því að nota Power Query viðbótina. Með hjálp hennar er allt leyst mjög fljótt og fallega.
1. Við skulum breyta upprunalegu töflunni okkar í „snjöll“ með því að nota flýtilykla Ctrl+T eða skipanir Heim - Snið sem töflu (Heima - Snið sem töflu).
2. Hladdu því inn í Power Query með hnappinum Frá borði/sviði flipi Gögn (Gögn — úr töflu/sviði).
3. Við flokkum (í gegnum fellilistann fyrir síuna í hausnum) töfluna okkar í lækkandi röð eftir dagsetningu, þannig að nýjustu viðskiptin eru efst.
4… Í flipanum Umbreyting velja lið Group by (Umbreyta - hópur eftir) og stilltu flokkunina eftir viðskiptavinum, og veldu valmöguleikann sem uppsöfnunaraðgerð Allar línur (Allar línur). Þú getur nefnt nýja dálkinn hvað sem þú vilt - til dæmis Nánar.
Eftir flokkun munum við fá lista yfir einstök nöfn viðskiptavina okkar og í dálknum Nánar - töflur með öllum færslum hvers þeirra, þar sem fyrsta línan verður nýjasta færslan, sem er það sem við þurfum:
5. Bættu við nýjum reiknuðum dálki með hnappinum Sérsniðinn dálkur flipi Bæta við dálki (Bæta við dálki - Bæta við sérsniðnum dálki)og sláðu inn eftirfarandi formúlu:
Hér Nánar – þetta er dálkurinn sem við tökum töflur eftir viðskiptavini, og 0 {} er númer línunnar sem við viljum draga út (línunúmerun í Power Query byrjar á núlli). Við fáum dálk með færslum (Met), þar sem hver færsla er fyrsta línan úr hverri töflu:
Það er eftir að stækka innihald allra skráa með hnappinum með tvöföldum örvum í dálkhausnum Síðasti samningur velja viðeigandi dálka:
… og eyða síðan dálknum sem ekki er lengur þörf á Nánar með því að hægrismella á titil þess - Fjarlægðu dálka (Fjarlægja dálka).
Eftir að hafa hlaðið niðurstöðunum inn á blaðið í gegnum Heim — Loka og hlaða — Loka og hlaða inn (Heima — Loka og hlaða — Loka og hlaða til...) við munum fá svo fallega töflu með lista yfir nýleg viðskipti, eins og við vildum:
Þegar þú breytir upprunagögnunum máttu ekki gleyma að uppfæra niðurstöðurnar með því að hægrismella á þær - skipunina Uppfærðu og vistaðu (Endurnýja) eða flýtilykla Ctrl+Alt+F5.
- LOOKUP aðgerðin er afsprengi VLOOKUP
- Hvernig á að nota nýju dynamic fylkisaðgerðirnar SORT, FILTER og UNIC
- Að finna síðasta reitinn sem ekki er auður í röð eða dálki með LOOKUP aðgerðinni