Tässä opetusohjelmassa opit löytämään kohteen viimeisen esiintymän luettelosta Excel -kaavojen avulla.
Äskettäin työskentelin kokouksen esityslistan asettamisen parissa.
Minulla oli Excel -luettelo, jossa minulla oli luettelo ihmisistä ja päivämäärät, jolloin he toimivat ”kokouksen puheenjohtajana”.
Koska luettelossa oli toistoa (mikä tarkoittaa, että henkilö on ollut kokouksen puheenjohtajana useita kertoja), minun oli myös tiedettävä, milloin viimeksi henkilö toimi kokouksen puheenjohtajana.
Tämä johtui siitä, että minun piti varmistaa, että äskettäin puheenjohtajana ollutta henkilöä ei nimitetä uudelleen.
Joten päätin käyttää Excel -funktion taikuutta tämän tekemiseen.
Alla on lopputulos, jossa voin valita nimen avattavasta valikosta ja se antaa minulle päivämäärän, jolloin nimi on viimeksi esiintynyt luettelossa.
Jos ymmärrät Excel -toiminnot hyvin, tiedät, ettei ole olemassa yhtä Excel -toimintoa, joka voi tehdä tämän.
Mutta olet Formula Hack -osiossa, ja tässä me teemme taikuuden.
Tässä opetusohjelmassa näytän sinulle kolme tapaa tehdä tämä.
Etsi viimeinen esiintyminen - käyttämällä MAX -toimintoa
Kiitos tästä tekniikasta kuuluu Excel MVP Charley Kydin artikkeliin.
Tässä on Excel -kaava, joka palauttaa luettelon viimeisen arvon:
= INDEKSI ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (RIVI ($ A $ 2: $ A $ 14)*($ D $ 3 = $ A $ 2: $ A $ 14))-1))
Näin tämä kaava toimii:
- MAX -toimintoa käytetään etsimään viimeisen vastaavan nimen rivinumero. Jos nimi on esimerkiksi Glen, se palauttaa 11, kuten se on 11 -rivillä. Koska luettelomme alkaa toiselta riviltä, 1 on vähennetty. Joten Glenin viimeisen esiintymisen sijainti on 10 luettelossamme.
- SUMPRODUCTia käytetään varmistamaan, että sinun ei tarvitse käyttää Control + Vaihto + Enter, koska SUMPRODUCT pystyy käsittelemään matriisikaavoja.
- INDEX -toimintoa käytetään nyt etsimään viimeisen vastaavan nimen päivämäärä.
Etsi viimeinen esiintyminen - LOOKUP -toiminnon avulla
Tässä on toinen kaava saman työn tekemiseen:
= HAKU (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Näin tämä kaava toimii:
- Hakuarvo on 2 (näet miksi… jatka lukemista)
- Hakualue on 1/($ A $ 2: $ A $ 14 = $ D $ 3) - Tämä palauttaa 1, kun se löytää vastaavan nimen ja virheen, kun ei. Joten saat lopulta taulukon. Esimerkiksi jos hakuarvo on Glen, taulukko olisi {#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!}.
- Kolmas argumentti ([result_vector]) on alue, josta se antaa tuloksen, jotka ovat tässä tapauksessa päivämääriä.
Tämä kaava toimii siksi, että LOOKUP -toiminto käyttää likimääräistä ottelutekniikkaa. Tämä tarkoittaa, että jos se löytää tarkan vastaavan arvon, se palauttaa sen, mutta jos ei löydä, se skannaa koko taulukon loppuun asti ja palauttaa seuraavan suurimman arvon, joka on pienempi kuin hakuarvo.
Tässä tapauksessa hakuarvo on 2, ja taulukossamme saamme vain yhden tai virheitä. Joten se skannaa koko taulukon ja palauttaa viimeisen 1 sijainnin - mikä on nimen viimeinen vastaava arvo.
Etsi viimeinen esiintyminen - mukautetun toiminnon (VBA) käyttäminen
Näytän myös toisen tavan tehdä tämä.
Voimme luoda mukautetun toiminnon (jota kutsutaan myös käyttäjän määrittämäksi funktioksi) VBA: n avulla.
Mukautetun toiminnon luomisen etuna on, että sitä on helppo käyttää. Sinun ei tarvitse huolehtia monimutkaisen kaavan luomisesta joka kerta, koska suurin osa työstä tapahtuu VBA -taustaohjelmassa.
Olen luonut yksinkertaisen kaavan (joka on paljon kuin VLOOKUP -kaava).
Muokatun toiminnon luomiseksi sinulla on oltava VBA -koodi VB -editorissa. Annan sinulle koodin ja vaiheet sen sijoittamiseksi VB -editoriin jonkin aikaa, mutta näytän ensin, miten se toimii:
Tämä on kaava, joka antaa sinulle tuloksen:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Kaava sisältää kolme argumenttia:
- Hakuarvo (tämä olisi nimi solussa D3)
- Hakualue (tämä olisi alue, jolla on nimet ja päivämäärät - A2: B14)
- Sarakkeen numero (tämä on sarake, josta haluamme tuloksen)
Kun olet luonut kaavan ja laittanut koodin VB Editoriin, voit käyttää sitä aivan kuten muutkin tavalliset Excel -laskentataulukon toiminnot.
Tässä on kaavan koodi:
'Tämä on funktion koodi, joka löytää hakuarvon viimeisen esiintymän ja palauttaa vastaavan arvon määritetystä sarakkeesta.' Sumit Bansalin luoma koodi (https://trumpexcel.com) Funktio LastItemLookup (Lookupvalue As String, LookupRange As Alue, sarakkeen numero kokonaislukuna) Dim i niin kauan kuin i = LookupRange.Columns (1). Solut. Laske 1 vaiheeseen -1 If Lookupvalue = LookupRange.Cells (i, 1) Sitten LastItemLookup = LookupRange.Cells (i, ColumnNumber) Exit Function End Jos seuraava i Lopeta toiminto
Voit asettaa tämän koodin VB Editoriin seuraavasti:
- Siirry Kehittäjä -välilehdelle.
- Napsauta Visual Basic -vaihtoehtoa. Tämä avaa VB -editorin taustaohjelmaan.
- Napsauta hiiren kakkospainikkeella mitä tahansa työkirjan objektia VB Editorin Project Explorer -ruudussa, johon haluat lisätä koodin. Jos et näe Project Exploreria, siirry Näytä -välilehteen ja napsauta Project Explorer.
- Siirry Lisää -kohtaan ja napsauta Moduuli. Tämä lisää moduuliobjektin työkirjaasi.
- Kopioi ja liitä koodi moduuli -ikkunaan.
Nyt kaava olisi käytettävissä kaikissa työkirjan laskentataulukoissa.
Huomaa, että työkirja on tallennettava .XLSM -muodossa, koska siinä on makro. Jos haluat, että tämä kaava on käytettävissä kaikissa käyttämissäsi työkirjoissa, voit joko tallentaa sen henkilökohtaiseen makro-työkirjaan tai luoda siitä lisäosan.
