V tej vadnici se boste naučili, kako poiskati zadnji pojav elementa na seznamu z uporabo Excelovih formul.
Pred kratkim sem delal na določanju dnevnega reda sestanka.
V Excelu sem imel seznam, na katerem sem imel seznam ljudi in datume, ko so delovali kot "predsednik sestanka".
Ker se je na seznamu ponavljalo (kar pomeni, da se je oseba večkrat sestala s predsednikom), sem morala vedeti tudi, kdaj je bila oseba nazadnje v vlogi "predsednika sestanka".
To je bilo zato, ker sem moral zagotoviti, da nekdo, ki je pred kratkim predsedoval, ne bo ponovno dodeljen.
Zato sem se za to odločil uporabiti nekaj čarovniških funkcij Excel.
Spodaj je končni rezultat, kjer lahko iz spustnega menija izberem ime in mi prikaže datum zadnjega pojavljanja tega imena na seznamu.
Če dobro razumete funkcije Excel, bi vedeli, da za to ne obstaja nobena funkcija Excel.
Ampak vi ste v razdelku Formula Hack in tukaj naredimo čarobnost.
V tej vadnici vam bom pokazal tri načine za to.
Poiščite zadnji pojav - uporaba funkcije MAX
Za to tehniko je zaslužen članek Excelovega MVP Charleyja Kyda.
Tukaj je Excelova formula, ki bo vrnila zadnjo vrednost s seznama:
= INDEX ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ROW ($ A $ 2: $ A $ 14)**($ D $ 3 = $ A $ 2: $ A $ 14))-1))
Takole deluje ta formula:
- Funkcija MAX se uporablja za iskanje številke vrstice zadnjega ujemajočega se imena. Na primer, če je ime Glen, bi vrnilo 11, saj je v 11. vrstici. Ker se naš seznam začne od druge vrstice naprej, smo odšteli 1. Tako je položaj zadnjega pojavljanja Glena 10 na našem seznamu.
- SUMPRODUCT se uporablja za zagotovitev, da vam ni treba uporabljati Control + Shift + Enter, saj lahko SUMPRODUCT obravnava formule matrike.
- Funkcija INDEX se zdaj uporablja za iskanje datuma zadnjega ujemajočega se imena.
Poiščite zadnji pojav - uporaba funkcije LOOKUP
Tu je še ena formula za isto delo:
= POGLED (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Takole deluje ta formula:
- Iskalna vrednost je 2 (videli boste zakaj … nadaljujte z branjem)
- Obseg iskanja je 1/($ A $ 2: $ A $ 14 = $ D $ 3) - Ta vrne 1, ko najde ujemajoče se ime, in napako, če ne. Tako boste na koncu dobili matriko. Na primer, iskalna vrednost je Glen, matrika pa bi bila {#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 argument ([result_vector]) je obseg, iz katerega daje rezultat, v tem primeru datumi.
Ta formula deluje, ker funkcija LOOKUP uporablja tehniko približnega ujemanja. To pomeni, da če bo našel natančno ujemajočo se vrednost, bi to vrnil, če pa ne, pa bo skeniral celotno matriko do konca in vrnil naslednjo največjo vrednost, ki je nižja od iskalne vrednosti.
V tem primeru je iskalna vrednost 2, v našem nizu pa bomo dobili le eno ali napake. Tako skenira celotno matriko in vrne položaj zadnje 1 - to je zadnja ujemajoča se vrednost imena.
Poiščite zadnji pojav - uporaba funkcije po meri (VBA)
Naj vam pokažem tudi drug način tega.
S pomočjo VBA lahko ustvarimo funkcijo po meri (imenovano tudi uporabniško določena funkcija).
Prednost ustvarjanja funkcije po meri je, da je enostavna za uporabo. Ni vam treba skrbeti, da boste vsakič ustvarili kompleksno formulo, saj večina dela poteka v ozadju VBA.
Ustvaril sem preprosto formulo (ki je zelo podobna formuli VLOOKUP).
Če želite ustvariti funkcijo po meri, morate imeti kodo VBA v urejevalniku VB. Kodo in korake za njeno umestitev v urejevalnik VB vam bom dal čez nekaj časa, vendar vam najprej pokažem, kako deluje:
To je formula, ki vam bo dala rezultat:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Formula ima tri argumente:
- Iskalna vrednost (to bi bilo ime v celici D3)
- Obseg iskanja (to bi bil obseg z imeni in datumi - A2: B14)
- Številka stolpca (to je stolpec, iz katerega želimo rezultat)
Ko ustvarite formulo in kodo vnesete v urejevalnik VB, jo lahko uporabite tako kot vse druge običajne funkcije delovnega lista Excel.
Tu je koda za formulo:
"To je koda za funkcijo, ki najde zadnji pojav iskane vrednosti in vrne ustrezno vrednost iz podanega stolpca" Koda, ki jo je ustvaril Sumit Bansal (https://trumpexcel.com) Funkcija LastItemLookup (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns (1) .Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells (i, 1) Potem LastItemLookup = LookupRange.Cells (i, ColumnNumber) Zapri funkcijo End, če je naslednja i Konča funkcijo
Tukaj je nekaj korakov za umestitev te kode v urejevalnik VB:
- Pojdite na zavihek Razvijalec.
- Kliknite možnost Visual Basic. To bo odprlo urejevalnik VB v zaledju.
- V podoknu Raziskovalec projektov v urejevalniku VB z desno tipko miške kliknite kateri koli predmet delovnega zvezka, v katerega želite vstaviti kodo. Če ne vidite Project Explorerja, pojdite na zavihek Pogled in kliknite Project Explorer.
- Pojdite na Vstavi in kliknite Modul. S tem boste v svoj delovni zvezek vstavili objekt modula.
- Kodo kopirajte in prilepite v okno modula.
Zdaj bi bila formula na voljo na vseh delovnih listih delovnega zvezka.
Upoštevajte, da morate delovni zvezek shraniti v obliki .XLSM, saj ima v njem makro. Če želite, da je ta formula na voljo v vseh delovnih zvezkih, ki jih uporabljate, jo lahko shranite v delovni zvezek osebnega makra ali pa iz nje ustvarite dodatek.
