In deze zelfstudie leert u hoe u het laatste exemplaar van een item in een lijst kunt vinden met behulp van Excel-formules.
Onlangs was ik bezig met het opstellen van de agenda voor een vergadering.
Ik had een lijst in Excel waar ik een lijst had met mensen en de data waarop zij als ‘Vergadervoorzitter’ fungeerden.
Omdat er herhaling in de lijst was (wat betekent dat een persoon meerdere keren vergadervoorzitter is geweest), moest ik ook weten wanneer iemand voor het laatst als 'vergadervoorzitter' optrad.
Dit kwam omdat ik ervoor moest zorgen dat iemand die onlangs voorzitter was niet opnieuw werd aangesteld.
Dus besloot ik om wat Excel-functiemagie te gebruiken om dit voor elkaar te krijgen.
Hieronder staat het eindresultaat waarbij ik een naam kan selecteren in de vervolgkeuzelijst en het geeft me de datum waarop die naam voor het laatst in de lijst voorkomt.
Als je een goed begrip hebt van Excel-functies, zou je weten dat er geen Excel-functie is die dit kan.
Maar u bevindt zich in de sectie Formula Hack en hier laten we de magie gebeuren.
In deze tutorial laat ik je drie manieren zien om dit te doen.
Vind het laatste voorval - Gebruik de MAX-functie
Met dank aan deze techniek gaat naar een artikel van Excel MVP Charley Kyd.
Hier is de Excel-formule die de laatste waarde uit de lijst retourneert:
=INDEX($B$2:$B$14,SOMPRODUCT(MAX(RIJ($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Zo werkt deze formule:
- De MAX-functie wordt gebruikt om het rijnummer van de laatst overeenkomende naam te vinden. Als de naam bijvoorbeeld Glen is, zou het 11 retourneren, omdat het in de 11 rij staat. Aangezien onze lijst vanaf de tweede rij begint, is er 1 afgetrokken. Dus de positie van het laatste exemplaar van Glen is 10 op onze lijst.
- SUMPRODUCT wordt gebruikt om ervoor te zorgen dat u Control + Shift + Enter niet hoeft te gebruiken, omdat SUMPRODUCT matrixformules aankan.
- De INDEX-functie wordt nu gebruikt om de datum voor de laatste overeenkomende naam te vinden.
Vind het laatste voorval - De LOOKUP-functie gebruiken
Hier is een andere formule om hetzelfde werk te doen:
=ZOEKEN(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Zo werkt deze formule:
- De opzoekwaarde is 2 (u zult zien waarom … blijf lezen)
- Het opzoekbereik is 1/($A$2:$A$14=$D$3) - Dit retourneert 1 wanneer de overeenkomende naam wordt gevonden en een fout wanneer dit niet het geval is. Dus je krijgt uiteindelijk een array. Als de opzoekwaarde bijvoorbeeld Glen is, is de array {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}.
- Het derde argument ([result_vector]) is het bereik van waaruit het het resultaat geeft, in dit geval datums.
De reden dat deze formule werkt, is dat de functie ZOEKEN de techniek bij benadering gebruikt. Dit betekent dat als het de exacte overeenkomende waarde kan vinden, het dat zou retourneren, maar als het dat niet kan, zal het de hele array tot het einde scannen en de volgende grootste waarde retourneren die lager is dan de opzoekwaarde.
In dit geval is de opzoekwaarde 2 en in onze array krijgen we alleen enen of fouten. Dus het scant de hele array en retourneert de positie van de laatste 1 - wat de laatste overeenkomende waarde van de naam is.
Vind het laatste voorval - Aangepaste functie (VBA) gebruiken
Laat me je ook een andere manier tonen om dit te doen.
We kunnen een aangepaste functie maken (ook wel door de gebruiker gedefinieerde functie genoemd) met behulp van VBA.
Het voordeel van het maken van een aangepaste functie is dat het gemakkelijk te gebruiken is. U hoeft zich niet elke keer zorgen te maken over het maken van een complexe formule, aangezien het meeste werk in de VBA-backend gebeurt.
Ik heb een eenvoudige formule gemaakt (die veel lijkt op de formule VERT.ZOEKEN).
Om een aangepaste functie te maken, moet u de VBA-code in de VB-editor hebben. Ik zal je de code en de stappen om het in de VB Editor te plaatsen over een tijdje geven, maar laat ik je eerst laten zien hoe het werkt:
Dit is de formule die u het resultaat geeft:
=LastItemLookup($D$3,$A$2:$B$14,2)
De formule heeft drie argumenten:
- Opzoekwaarde (dit is de naam in cel D3)
- Opzoekbereik (dit is het bereik met de namen en datums - A2:B14)
- Kolomnummer (dit is de kolom waarvan we het resultaat willen)
Nadat u de formule hebt gemaakt en de code in VB Editor hebt geplaatst, kunt u deze net als alle andere reguliere Excel-werkbladfuncties gebruiken.
Hier is de code voor de formule:
'Dit is een code voor een functie die de laatste instantie van een opzoekwaarde vindt en de overeenkomstige waarde uit de opgegeven kolom retourneert' Code gemaakt door Sumit Bansal (https://trumpexcel.com) Functie LastItemLookup(Lookupvalue As String, LookupRange As Bereik, Kolomnummer als geheel getal) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells(i, 1) Then LastItemLookup = LookupRange.Cells(i, ColumnNumber) Functie verlaten End If Next i Functie beëindigen
Hier zijn de stappen om deze code in de VB Editor te plaatsen:
- Ga naar het tabblad Ontwikkelaars.
- Klik op de Visual Basic-optie. Dit opent de VB-editor in de backend.
- Klik in het deelvenster Projectverkenner in de VB-editor met de rechtermuisknop op een object voor de werkmap waarin u de code wilt invoegen. Als u Project Explorer niet ziet, gaat u naar het tabblad Weergave en klikt u op Project Explorer.
- Ga naar Invoegen en klik op Module. Hiermee wordt een moduleobject voor uw werkmap ingevoegd.
- Kopieer en plak de code in het modulevenster.
Nu zou de formule beschikbaar zijn in het hele werkblad van de werkmap.
Houd er rekening mee dat u de werkmap moet opslaan in de .XLSM-indeling, omdat deze een macro bevat. Als u wilt dat deze formule beschikbaar is in alle werkmappen die u gebruikt, kunt u deze ook opslaan in de persoonlijke macrowerkmap of er een invoegtoepassing van maken.
