I våra tidigare artiklar har vi lärt oss hur man extraherar unika värden från ett intervall med hjälp av en kombination av olika excel -funktioner. Även om de fungerar fantastiskt men de är också komplexa, kan man inte förneka detta. Om du extraherar unika värden ofta kan dessa formler tröttna på dig. De gör också filen tung och långsam.
Så i den här artikeln kommer vi att lära oss hur man skapar en användardefinierad funktion som tar ett intervall som ett argument och endast returnerar unika värden från det intervallet. Du kan kopiera koden direkt till din fil och börja använda den direkt.
VBA -kod för unik funktion:
Funktion UNIQUES (rng As Range) As Variant () Dim list As New Collection Dim Ulist () As Variant 'Lägger till varje värde av rng till samlingen. Vid fel Återuppta nästa för varje värde i rng 'här är värde och nyckel desamma. Samlingen tillåter inte dubbletter av nycklar, därför återstår bara unika värden. list.Add CStr (Value), CStr (Value) Next On Error Gå till 0 'Definierar matrisens längd till antalet unika värden. Eftersom arrayen börjar från 0, subtraherar vi 1. ReDim Ulist (list.Count - 1, 0) 'Lägga till unikt värde i arrayen. För i = 0 To list.Count - 1 Ulist (i, 0) = list (i + 1) Nästa 'Skriva ut arrayen UNIQUES = Ulist End -funktion
Kopiera koden till VB -redigeraren för excel.
Med funktionen UNIK
Ovanstående funktion är en användardefinierad flercellad matrisfunktion. Det betyder att du måste välja det område där du vill att den unika listan ska skrivas ut, sedan skriva formeln och trycka på tangenterna CTRL+SKIFT+ENTER.
I gifvan ovan har vi en lista över länder. Nu finns det många dubblettländer på listan. Vi vill bara få listan över unika länder.
För att göra det, välj ett område där du vill ha den unika listan. Skriv nu denna formel:
| = UNIK (A2: B7) |
Tryck på tangenterna CTRL+SKIFT+ENTER. Och det är gjort. Alla unika värden anges i det valda intervallet.
Notera:Om intervallet du har valt är mer än det unika värdet visar de #N/A -fel. Du kan använda den som en indikation på att sluta unika värden. Om du inte ser #N/A i slutet av listan betyder det att det kan finnas fler unika värden.
Förklaring av kod
Jag har använt två huvudbegrepp för VBA i denna UD -funktion. Samlingar och användardefinierad matrisfunktion. Först har vi använt en samling för att få de unika värdena från det angivna intervallet.
för varje värde i listan. Lägg till CStr (värde), CStr (värde) Nästa nästa
Eftersom vi inte kan skriva ut en samling på arket överförde vi den till en annan array UList.
för i = 0 To list.Count - 1 Ulist (i, 0) = list (i + 1) Nästa nästa
Notera:VBA -arrayindexering börjar från 0 och Collection indexering startar från 1. Det är därför vi har subtraherat 1 för array in för loop och lagt till 1 i indexering av listsamling.
I slutändan har vi skrivit ut den matrisen på arket.
Det finns en UNIK funktion som inte är tillgänglig för Excel 2016 som gör samma sak. Den funktionen är tillgänglig i Excel 2019. Endast medlemmarna i insiderprogrammet har tillgång till den funktionen. Genom att använda denna teknik kan du visa upp dig på kontoret för att ligga före MS.
Så ja killar, så här kan du skapa en funktion som extraherar unika värden helt enkelt. Jag hoppas att jag var tillräckligt förklarande för att få dig att förstå detta. Om du har några specifika frågor angående denna eller någon annan Excel -VBA -relaterad fråga, ställ den i kommentarfältet nedan.
Klicka på länken nedan för att ladda ner arbetsfilen:
UNIQUES -funktionHur man använder VBA -samlingar i Excel | Lär dig att använda samlingen som kan hjälpa dig att få unika värden.
Skapa VBA -funktion för att returnera matris | Lär dig hur du skapar en användardefinierad arrayfunktion som returnerar en array.
Matriser i Excel Formul | Lär dig vilka matriser som finns i excel.
Hur man skapar användardefinierad funktion via VBA | Lär dig hur du skapar användardefinierade funktioner i Excel
Använda en användardefinierad funktion (UDF) från en annan arbetsbok med VBA i Microsoft Excel | Använd den användardefinierade funktionen i en annan arbetsbok i Excel
Returnera felvärden från användardefinierade funktioner med VBA i Microsoft Excel | Lär dig hur du kan returnera felvärden från en användardefinierad funktion