Povzetek: v tej vadnici se boste naučili, kako uporabiti Excelovo formulo za iskanje zadnjega pojava elementa na seznamu.
Pred kratkim sem delal na dnevnem redu sestanka.
V Excelu imam seznam s seznamom ljudi in datumi, ko so bili "sestanki predsednikov".
Prav tako moram vedeti, kdaj je bila oseba nazadnje "predsedujoča sestanku" zaradi podvajanja na seznamu (kar pomeni, da je oseba večkrat predsedovala sestanku).
To je zato, ker moram poskrbeti, da zadnji predsednik ne bo prerazporejen.
Zato sem se odločil, da za delo uporabim nekaj čarovnije funkcije Excel.
Spodaj je končni rezultat, iz spustnega menija lahko izberem ime in prikaže mi datum zadnjega pojavljanja tega imena na seznamu.
Če dobro razumete Excelove funkcije, veste, da ni nobene Excelove funkcije, ki bi to zmogla.Tukaj delamo čudeže.
V tej vadnici vam bom pokazal tri načine za to.
vsebina
Poiščite zadnji pojav - s funkcijo MAX
Tu je Excelova formula, ki bo vrnila zadnjo vrednost na seznamu:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Evo, kako ta formula deluje:
- Funkcija MAX se uporablja za iskanje številke vrstice zadnjega ujemajočega se imena.Na primer, če je ime Glen, bo vrnilo 11, ker je v vrstici 11.Ker se naš seznam začne v drugi vrstici, se 1 odšteje.Zato je zadnji pojav Glen 10 na našem seznamu.
- SUMPRODUCT se uporablja za zagotovitev, da vam ni treba uporabljati Control + Shift + Enter, ker SUMPRODUCT lahko obravnava formule matrik.
- Funkcija INDEX se zdaj uporablja za iskanje datuma zadnjega ujemajočega se imena.
Poiščite zadnji pojav - s funkcijo LOOKUP
Tu je še ena formula, ki opravlja enako delo:
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Evo, kako ta formula deluje:
- Iskalna vrednost je 2 (boš videl, zakaj.. nadaljuj z branjem)
- Obseg iskanja je 1/($A$2:$A$14=$D$3) - vrne 1, ko najde ujemajoče se ime, v nasprotnem primeru pa napako.Tako na koncu dobite matriko.例如，查找值為 Glen，數組將為 {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}。
- Tretji parameter ([result_vector]) je obseg, v katerem daje rezultat, v tem primeru datum.
Razlog, zakaj ta formula deluje, je, da funkcija LOOKUP uporablja tehniko približnega ujemanja.To pomeni, da če lahko najde natančno ujemanje, se bo vrnil, če pa ne, bo pregledal celotno matriko do konca in vrnil naslednjo največjo vrednost, ki je manjša od vrednosti iskanja.
V tem primeru je iskalna vrednost 2, v našem nizu pa dobimo samo 1 ali napako.Tako pregleda celotno matriko in vrne položaj zadnje 1 - zadnje ujemajoče se vrednosti za ime.
Najdi zadnji pojav - uporaba funkcije po meri (VBA)
Naj vam pokažem tudi drug način.
Z uporabo VBA lahko ustvarimo funkcije po meri (znane tudi kot uporabniško definirane funkcije).
Prednost ustvarjanja funkcij po meri je enostavna uporaba.Ni vam treba skrbeti za ustvarjanje zapletenih formul vsakič, ker se večina dela zgodi v zaledju VBA.
Ustvaril sem preprosto formulo (podobno kot formula VLOOKUP).
Za ustvarjanje funkcij po meri potrebujete kodo VBA v urejevalniku VB.Kasneje vam bom dal kodo in korake, da jo prenesete v urejevalnik VB, vendar naj vam najprej pokažem, kako deluje:
Tukaj je formula, ki vam bo dala rezultat:
=LastItemLookup($D$3,$A$2:$B$14,2)
Formula ima tri parametre:
- Poiščite vrednost (to bo ime v celici D3)
- Poiščite obseg (to bo obseg z imenom in datumom – A2:B14)
- številka stolpca (to je stolpec, za katerega želimo rezultat)
Ko ustvarite formulo in vstavite kodo v urejevalnik VB, jo lahko uporabite kot katero koli drugo običajno funkcijo Excelovega delovnega lista.
Tukaj je koda za formulo:
Funkcija LastItemLookup(Lookupvalue kot niz, LookupRange kot obseg, ColumnNumber kot celo število) Dim i As Long For i = LookupRange.Columns(1).Cells.Count do 1 Korak -1 Če Lookupvalue = LookupRange.Cells(i, 1) Nato LastItemLoo = LookupRange.Cells(i, ColumnNumber) Izhod iz funkcije Konec, če Next i Konec funkcije
Tukaj so koraki za vstavljanje te kode v urejevalnik VB:
- Pojdite na zavihek za razvijalce.
- Kliknite Možnosti Visual Basic.To bo odprlo urejevalnik VB v ozadju.
- V podoknu Project Explorer urejevalnika VB z desno tipko miške kliknite kateri koli predmet v delovnem zvezku, kamor želite vstaviti kodo.Če ne vidite Project Explorer, pojdite na zavihek Pogled in kliknite Project Explorer.
- Pojdite na Vstavi in kliknite na Moduli.To bo vstavilo objekt modula za vaš delovni zvezek.
- Kopirajte in prilepite kodo v okno modula.
Zdaj bo formula na voljo na vseh listih delovnega zvezka.
Upoštevajte, da boste morali delovni zvezek shraniti v formatu .XLSM, saj vsebuje makre.Če želite, da je ta formula na voljo v vseh delovnih zvezkih, ki jih uporabljate, jo lahko shranite v svoj osebni delovni zvezek z makrami ali iz nje ustvarite dodatek.
