Opsomming: in hierdie tutoriaal sal jy leer hoe om 'n Excel-formule te gebruik om die laaste voorkoms van 'n item in 'n lys te vind.
Ek het onlangs aan 'n vergaderingsagenda gewerk.
Ek het 'n lys in Excel wat 'n lys van mense het en die datums waarop hulle "vergaderingvoorsitters" was.
Ek moet ook weet wanneer 'n persoon laas "vergaderingvoorsitter" was as gevolg van duplisering in die lys (wat beteken 'n persoon was al verskeie kere voorsitter van die vergadering).
Dit is omdat ek moet seker maak dat die mees onlangse voorsitter nie heraangestel word nie.
Ek het dus besluit om 'n bietjie Excel-funksie-magie te gebruik om die werk te doen.
Hieronder is die eindresultaat, ek kan 'n naam uit die aftreklys kies en dit gee my die datum van die laaste voorkoms van daardie naam in die lys.
As jy 'n goeie begrip van Excel-funksies het, weet jy dat daar geen Excel-funksie is wat dit kan doen nie.Hier laat ons wonderwerke gebeur.
In hierdie tutoriaal sal ek jou drie maniere wys om dit te doen.
Contents [show]
Vind die laaste voorkoms - deur die MAX-funksie te gebruik
Hier is die Excel-formule wat die laaste waarde in die lys sal terugstuur:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Hier is hoe hierdie formule werk:
- Die MAX-funksie word gebruik om die reëlnommer van die laaste ooreenstemmende naam te vind.Byvoorbeeld, as die naam Glen is, sal dit 11 gee omdat dit op reël 11 is.Aangesien ons lys op die tweede ry begin, word 1 afgetrek.Daarom is die laaste voorkoms van Glen 10 in ons lys.
- SUMPRODUCT word gebruik om seker te maak jy hoef nie Control + Shift + Enter te gebruik nie, want SUMPRODUCT kan skikkingsformules hanteer.
- Die INDEX-funksie word nou gebruik om die datum van die laaste ooreenstemmende naam te vind.
Vind die laaste voorkoms - deur die OPSOEK-funksie te gebruik
Hier is nog 'n formule wat dieselfde werk doen:
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Hier is hoe hierdie formule werk:
- Opsoekwaarde is 2 (jy sal sien hoekom .. hou aan lees)
- Die soekreeks is 1/($A$2:$A$14=$D$3) - dit gee 1 terug wanneer dit 'n ooreenstemmende naam vind, en andersins 'n fout.So jy eindig met 'n skikking.例如，查找值為 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!}。
- Die derde parameter ([resultaat_vektor]) is die reeks waarin dit die resultaat gee, in hierdie geval die datum.
Die rede waarom hierdie formule werk, is dat die OPSOEK-funksie 'n benaderde pastegniek gebruik.Dit beteken dat as dit 'n presiese passing kan vind, dit sal terugkeer, maar as dit nie kan nie, sal dit die hele skikking skandeer tot die einde en die volgende grootste waarde terugstuur wat minder is as die opsoekwaarde.
In hierdie geval is die opsoekwaarde 2, en in ons skikking kry ons net 1 of 'n fout.Dit skandeer dus die hele skikking en gee die posisie van die laaste 1 terug - wat die laaste ooreenstemmende waarde vir die naam is.
Soek Laaste Voorkoms - Gebruik Gepasmaakte Funksie (VBA)
Kom ek wys jou ook 'n ander manier.
Ons kan pasgemaakte funksies skep (ook bekend as gebruiker-gedefinieerde funksies) met behulp van VBA.
Die voordeel van die skep van persoonlike funksies is die gemak van gebruik.Jy hoef nie elke keer bekommerd te wees oor die skep van komplekse formules nie, want die meeste van die werk gebeur in die VBA-agterkant.
Ek het 'n eenvoudige formule geskep (soos die VLOOKUP formule).
Om 'n pasgemaakte funksie te skep, benodig jy VBA-kode in die VB-redigeerder.Ek sal vir jou die kode en die stappe gee om dit later in die VB-redigeerder te kry, maar laat ek jou eers wys hoe dit werk:
Hier is die formule wat jou die resultaat sal gee:
=LastItem Lookup($D$3,$A$2:$B$14,2)
Die formule neem drie parameters:
- Vind die waarde (dit sal die naam in sel D3 wees)
- Vind die reeks (dit sal die reeks met naam en datum wees – A2:B14)
- kolomnommer (dit is die kolom waarvoor ons die resultaat wil hê)
Nadat u die formule geskep het en die kode in die VB-redigeerder geplaas het, kan u dit soos enige ander gewone Excel-werkbladfunksie gebruik.
Hier is die kode vir die formule:
Funksie LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Heelgetal) Dim i So Long For i = LookupRange.Columns(1).Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells(i, 1) Then LastItemLookup = LookupRange.Cells(i, ColumnNumber) Verlaat Funksie Einde Indien Volgende i Eindig Funksie
Hier is die stappe om hierdie kode in die VB-redigeerder te plaas:
- Gaan na die ontwikkelaaroortjie.
- Klik op Visual Basic Options.Dit sal die VB-redigeerder in die agterkant oopmaak.
- Regskliek in die Project Explorer-venster van die VB-redigeerder op enige voorwerp in die werkboek waar jy kode wil invoeg.As jy nie Project Explorer sien nie, gaan na die View-oortjie en klik Project Explorer.
- Gaan na Insert en klik op Modules.Dit sal 'n module-voorwerp vir jou werkboek invoeg.
- Kopieer en plak die kode in die modulevenster.
Nou sal die formule in alle velle van die werkboek beskikbaar wees.
Let daarop dat jy die werkboek in .XLSM-formaat sal moet stoor aangesien dit makro's bevat.Ook, as jy wil hê dat hierdie formule beskikbaar moet wees in alle werkboeke wat jy gebruik, kan jy dit in jou persoonlike makro-werkboek stoor of 'n byvoeging daaruit skep.
