Šioje pamokoje sužinosite, kaip rasti paskutinį elemento atsiradimą sąraše naudojant „Excel“ formules.
Neseniai rengiau susitikimo darbotvarkę.
Turėjau „Excel“ sąrašą, kuriame turėjau žmonių sąrašą ir datas, kada jie veikė kaip „susitikimo pirmininkas“.
Kadangi sąraše buvo pasikartojimų (tai reiškia, kad asmuo kelis kartus buvo susitikęs su pirmininku), taip pat turėjau žinoti, kada paskutinį kartą asmuo veikė kaip „susirinkimo pirmininkas“.
Taip buvo todėl, kad turėjau užtikrinti, kad neseniai pirmininkavęs asmuo nebūtų vėl paskirtas.
Taigi nusprendžiau panaudoti tam tikrą „Excel“ funkcijų magiją, kad tai padaryčiau.
Žemiau yra galutinis rezultatas, kuriame galiu pasirinkti pavadinimą iš išskleidžiamojo meniu ir jis nurodo paskutinio to vardo atsiradimo sąraše datą.
Jei gerai suprantate „Excel“ funkcijas, žinotumėte, kad nėra vienos „Excel“ funkcijos, galinčios tai padaryti.
Bet jūs esate „Formulės nulaužimo“ skiltyje ir čia mes padarome stebuklą.
Šioje pamokoje parodysiu tris būdus, kaip tai padaryti.
Raskite paskutinį kartą - naudokite funkciją MAX
Šios technikos nuopelnas priklauso Excel MVP Charley Kyd straipsniui.
Štai „Excel“ formulė, kuri grąžins paskutinę sąrašo vertę:
= INDEKSAS ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ROW ($ A $ 2: $ A $ 14)*($ D $ 3 = $ A $ 2: $ A $ 14))-1))
Štai kaip veikia ši formulė:
- Funkcija MAX naudojama norint rasti paskutinio atitinkančio pavadinimo eilutės numerį. Pvz., Jei vardas yra Glen, jis grąžins 11, kaip ir 11 eilutėje. Kadangi mūsų sąrašas prasideda nuo antros eilės, 1 buvo atimtas. Taigi paskutinio Gleno įvykio vieta mūsų sąraše yra 10.
- SUMPRODUCT naudojamas siekiant užtikrinti, kad jums nereikėtų naudoti „Control“ + „Shift“ + „Enter“, nes „SUMPRODUCT“ gali tvarkyti masyvo formules.
- Funkcija INDEX dabar naudojama norint rasti paskutinio atitinkančio vardo datą.
Raskite paskutinį kartą - naudodami funkciją LOOKUP
Čia yra dar viena formulė, kaip atlikti tą patį darbą:
= IEŠKOTIS (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Štai kaip veikia ši formulė:
- Paieškos vertė yra 2 (pamatysite, kodėl … toliau skaitykite)
- Peržiūros diapazonas yra 1/($ A $ 2: $ A $ 14 = $ D $ 3) - grąžinama 1, kai randamas atitinkantis pavadinimas, ir klaida, kai ne. Taigi jūs gausite masyvą. Pavyzdžiui, jei paieškos vertė yra „Glen“, masyvas būtų {#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!}.
- Trečias argumentas ([rezultato_vektorius]) yra intervalas, nuo kurio jis duoda rezultatą, o tai šiuo atveju yra datos.
Ši formulė veikia todėl, kad LOOKUP funkcija naudoja apytikslę atitikties techniką. Tai reiškia, kad jei ji gali rasti tikslią atitinkančią vertę, ji ją grąžins, bet jei neras, nuskaitys visą masyvą iki galo ir grąžins kitą didžiausią reikšmę, kuri yra mažesnė už paieškos vertę.
Šiuo atveju paieškos vertė yra 2, o mūsų masyve gausime tik 1 arba klaidas. Taigi jis nuskaito visą masyvą ir grąžina paskutinio 1 poziciją - tai yra paskutinė atitinkanti vardo vertė.
Raskite paskutinį kartą - naudodami pasirinktinę funkciją (VBA)
Leiskite parodyti ir kitą būdą, kaip tai padaryti.
Naudodami VBA galime sukurti pasirinktinę funkciją (dar vadinamą vartotojo apibrėžta funkcija).
Pasirinktinės funkcijos sukūrimo pranašumas yra tas, kad ja lengva naudotis. Jums nereikia kas kartą sukti galvos dėl sudėtingos formulės sukūrimo, nes didžioji darbo dalis atliekama VBA programinėje įrangoje.
Aš sukūriau paprastą formulę (kuri labai panaši į VLOOKUP formulę).
Norėdami sukurti pasirinktinę funkciją, VB redaktoriuje turite turėti VBA kodą. Aš jums duosiu kodą ir veiksmus, kaip kurį laiką jį įdėti į VB redaktorių, bet pirmiausia leiskite parodyti, kaip tai veikia:
Ši formulė duos jums rezultatą:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Formulė apima tris argumentus:
- Paieškos vertė (tai būtų pavadinimas langelyje D3)
- Paieškos diapazonas (tai būtų diapazonas su pavadinimais ir datomis - A2: B14)
- Stulpelio numeris (tai stulpelis, iš kurio norime rezultato)
Sukūrę formulę ir įdėję kodą į VB redaktorių, galite ją naudoti kaip ir bet kurias kitas įprastas „Excel“ darbalapio funkcijas.
Čia yra formulės kodas:
„Tai yra funkcijos kodas, kuris nustato paskutinį paieškos vertės įvykį ir grąžina atitinkamą vertę iš nurodyto stulpelio“ Kodas, sukurtas „Sumit Bansal“ (https://trumpexcel.com) Diapazonas, stulpelio numeris kaip sveikasis) Dim i kaip ilgas, kai i = „LookupRange“. Stulpeliai (1). Ląstelės. Skaičiuokite iki 1 veiksmo -1 veiksmas „If Lookupvalue = LookupRange.Cells“ (i, 1) Tada „LastItemLookup = LookupRange.Cells“ (i, ColumnNumber) Išeiti iš funkcijos pabaigos, jei „Kitas“ - „Baigti funkciją“
Štai šie kodo įdėjimo į VB redaktorių veiksmai:
- Eikite į skirtuką Kūrėjas.
- Spustelėkite „Visual Basic“ parinktį. Tai atidarys VB redaktorių užpakalinėje sistemoje.
- VB redaktoriaus „Project Explorer“ srityje dešiniuoju pelės mygtuku spustelėkite bet kurį darbaknygės objektą, į kurį norite įterpti kodą. Jei nematote „Project Explorer“, eikite į skirtuką „View“ ir spustelėkite „Project Explorer“.
- Eikite į „Įterpti“ ir spustelėkite „Modulis“. Tai įterps jūsų darbo knygos modulio objektą.
- Nukopijuokite ir įklijuokite kodą modulio lange.
Dabar formulė būtų prieinama visuose darbaknygės darbalapiuose.
Atminkite, kad turite išsaugoti darbaknygę .XLSM formatu, nes joje yra makrokomanda. Be to, jei norite, kad ši formulė būtų prieinama visose naudojamose darbaknygėse, galite ją išsaugoti asmeninėje makrokomandos darbaknygėje arba sukurti iš jos priedą.
