Sisällys
Kaikki perinteiset haku- ja tyyppikorvaustoiminnot VPR (HAKU), GPR (HAKU), LISÄTIETOJA (OTTELU) ja heidän kaltaisillaan on yksi tärkeä ominaisuus – ne etsivät alusta loppuun eli lähdetiedoissa vasemmalta oikealle tai ylhäältä alas. Heti kun ensimmäinen vastaava osuma löytyy, haku pysähtyy ja vain ensimmäinen tarvittava elementti löytyy.
Mitä tehdä, jos meidän ei tarvitse löytää ensimmäistä, vaan viimeistä tapahtumaa? Esimerkiksi asiakkaan viimeinen tapahtuma, viimeinen maksu, viimeisin tilaus jne.?
Tapa 1: Etsi viimeinen rivi taulukkokaavalla
Jos alkuperäisessä taulukossa ei ole saraketta, jossa on päivämäärä tai rivin (tilaus, maksu…) sarjanumero, niin meidän tehtävämme on itse asiassa löytää viimeinen rivi, joka täyttää annetun ehdon. Tämä voidaan tehdä seuraavalla taulukkokaavalla:
Tässä:
- Toiminto IF (JOS) tarkistaa sarakkeen kaikki solut yksitellen Asiakas ja näyttää rivinumeron, jos se sisältää tarvitsemamme nimen. Arkin rivinumeron antaa meille funktio LINE (RIVI), mutta koska tarvitsemme taulukon rivinumeron, meidän on lisäksi vähennettävä 1, koska taulukossa on otsikko.
- Sitten funktio MAX (MAX) valitsee muodostuneesta rivinumerojoukosta suurimman arvon eli asiakkaan viimeisimmän rivin numeron.
- Toiminto INDEX (INDEKSI) palauttaa solun sisällön löydetyllä viimeisellä numerolla mistä tahansa muusta vaaditusta taulukon sarakkeesta (Tilauskoodi).
Kaikki tämä on syötettävä muodossa taulukon kaava, eli:
- Office 365:ssä, jossa on uusimmat päivitykset asennettuna ja dynaamisten taulukoiden tuki, voit yksinkertaisesti painaa enter.
- Kaikissa muissa versioissa kaavan syöttämisen jälkeen sinun on painettava pikanäppäintä Ctrl+siirtyminen+enter, joka lisää siihen automaattisesti kiharat aaltosulkeet kaavapalkissa.
Tapa 2: Käänteinen haku uudella HAKU-toiminnolla
Kirjoitin jo pitkän artikkelin videolla uudesta ominaisuudesta KATSO (XHAKU), joka ilmestyi Officen uusimmissa versioissa korvaamaan vanhan VLOOKUPin (HAKU). BROWSE:n avulla tehtävämme ratkeaa melko alkeellisesti, koska. tälle funktiolle (toisin kuin VLOOKUP) voit nimenomaisesti asettaa hakusuunnan: ylhäältä alas tai alhaalta ylös – sen viimeinen argumentti (-1) on vastuussa tästä:
Tapa 3. Etsi merkkijono, jossa on viimeisin päivämäärä
Jos lähdetiedoissa on sarake, jossa on sarjanumero tai päivämäärä, jolla on samanlainen rooli, niin tehtävää muutetaan - meidän ei tarvitse löytää viimeistä (alinta) riviä, jolla on vastaavuus, vaan rivi, jolla on viimeisin ( enimmäispäivämäärä.
Olen jo käsitellyt yksityiskohtaisesti, kuinka tämä tehdään klassisten funktioiden avulla, ja nyt yritetään käyttää uusien dynaamisten taulukkofunktioiden tehoa. Suuremman kauneuden ja mukavuuden vuoksi muunnamme myös alkuperäisen pöydän "älykkääksi" pöydäksi pikanäppäimen avulla Ctrl+T tai käskyt Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona).
Heidän avullaan tämä "tappajapariskunta" ratkaisee ongelmamme erittäin sulavasti:
Tässä:
- Toiminto ensin SUODATIN (SUODATTAA) valitsee vain ne rivit taulukosta, jotka ovat sarakkeessa Asiakas – tarvitsemamme nimi.
- Sitten funktio GRADE (JÄRJESTELLÄ) lajittelee valitut rivit päivämäärän mukaan laskevaan järjestykseen siten, että viimeisin sopimus on ylhäällä.
- Toiminto INDEX (INDEKSI) poimii ensimmäisen rivin eli palauttaa viimeisen tarvitsemamme kaupan.
- Ja lopuksi ulkoinen FILTER-toiminto poistaa ylimääräiset 1. ja 3. sarakkeet tuloksista (Tilauskoodi и Asiakas) ja jättää vain päivämäärän ja summan. Tätä varten käytetään vakiojoukkoa. 0;1;0;1}, määrittää, mitkä sarakkeet haluamme (1) tai emme halua (0) näkyvän.
Tapa 4: Etsi viimeinen vastaavuus Power Querysta
Täydellisyyden vuoksi tarkastellaan ratkaisua käänteisen haun ongelmaan Power Query -apuohjelman avulla. Hänen avullaan kaikki ratkaistaan erittäin nopeasti ja kauniisti.
1. Muunnetaan alkuperäinen taulukko "älykkääksi" pikanäppäimen avulla Ctrl+T tai käskyt Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona).
2. Lataa se Power Queryyn -painikkeella Taulukosta/alueesta kieleke Päiväys (Tiedot – taulukosta/alueesta).
3. Lajittelemme (otsikon suodattimen pudotusvalikon kautta) taulukkomme päivämäärän mukaan laskevaan järjestykseen, jotta viimeisimmät tapahtumat ovat ylhäällä.
4… Välilehdellä Muutos valitse joukkue Ryhmän mukaan (Muunna — Ryhmäperuste) ja aseta ryhmittely asiakkaiden mukaan ja valitse yhdistelmätoimintona vaihtoehto Kaikki linjat (Kaikki rivit). Voit nimetä uuden sarakkeen haluamallasi tavalla – esimerkiksi Lisätiedot.
Ryhmittelyn jälkeen saamme luettelon asiakkaidemme yksilöllisistä nimistä ja sarakkeesta Lisätiedot – taulukot, joissa on kunkin tapahtuman kaikki tapahtumat, joissa ensimmäinen rivi on viimeisin tapahtuma, jota tarvitsemme:
5. Lisää uusi laskettu sarake -painikkeella Muokattu sarake kieleke Lisää sarake (Lisää sarake – Lisää mukautettu sarake)ja syötä seuraava kaava:
Tässä Lisätiedot – Tämä on sarake, josta otamme asiakkaiden taulukot ja 0 {} on sen rivin numero, jonka haluamme poimia (rivien numerointi Power Queryssä alkaa nollasta). Saamme sarakkeen tietueilla (ennätys), jossa jokainen merkintä on kunkin taulukon ensimmäinen rivi:
Jäljelle jää kaikkien tietueiden sisällön laajentaminen painikkeella, jossa on kaksoisnuolet sarakeotsikossa Viimeinen sopimus valitsemalla haluamasi sarakkeet:
… ja poista sitten sarake, jota ei enää tarvita Lisätiedot napsauttamalla hiiren oikealla painikkeella sen otsikkoa - Poista sarakkeet (Poista sarakkeet).
Kun tulokset on ladattu arkille läpi Etusivu — Sulje ja lataa — Sulje ja lataa (Etusivu — Sulje & Lataa — Sulje & Lataa…) saamme niin mukavan taulukon, jossa on luettelo viimeaikaisista tapahtumista, kuten halusimme:
Kun muutat lähdetietoja, älä unohda päivittää tuloksia napsauttamalla niitä hiiren oikealla painikkeella - komento Päivitä & Tallenna (Virkistää) tai pikanäppäimellä Ctrl+muut+F5.
- HAKU-funktio on VLOOKUP-funktion jälkeläinen
- Kuinka käyttää uusia dynaamisia taulukkofunktioita SORT, FILTER ja UNIC
- Viimeisen ei-tyhjän solun etsiminen riviltä tai sarakkeesta HAKU-funktiolla