Hoe u waarden kunt indexeren en sorteren met behulp van de helperkolom in Excel.

Anonim

In dit artikel leren we hoe u waarden kunt indexeren en sorteren met behulp van de helperkolom in Excel.

Scenario:

Soms werken met ongemanierde data. We moeten de kortere versie halen als vereiste gegevens uit de tabelgegevens op basis van de helperkolom. Hier zullen we eerst begrijpen hoe we de helperkolom kunnen krijgen en vervolgens de gesorteerde kleine vereiste gegevens krijgen op basis van de tabelgegevens.

Hoe waarden binnen bereik te indexeren?

Voor dit artikel moeten we de AANTAL.ALS-functie gebruiken. Nu gaan we een formule maken van de functie. Hier gegeven tekst en getal gemengde waarden in een bereik. We moeten ze eerst numeriek en daarna alfabetisch in oplopende volgorde indexeren.

Generieke formule:-

= AANTAL.ALS ( lijst , "<=" & f_waarde ) + ( AANTAL (lijst) * ISTEXT (f_waarde) )

lijst : lijst met cijfers en tekst

f_value: eerste waarde van het bereik

Uitleg:

  1. AANTAL.ALS-functie telt het aantal waarden in de geretourneerde lijst
  2. COUNT-functie haalt het aantal getallen binnen bereik op.
  3. De ISTEXT-functie controleert of de waarde in de geselecteerde cel tekst is of niet. Retourneert WAAR voor tekst en ONWAAR voor getallen.

Voorbeeld:

Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we eens kijken hoe we de helperkolom kunnen krijgen met behulp van de uitgelegde formule. Hier moet de helperkolom worden gebaseerd op de sorteerwaarden in volgorde (eerst cijfers en vervolgens alfabetisch). Hiervoor gebruiken we de formule om de kolom Index of rangorde te krijgen als hulpkolom voor de onderstaande onkostengegevens.

Hier vindt u de waarden binnen bereik. Voor berekeningsdoeleinden gebruiken we het genoemde bereik voor de vaste array D5:D12 als kosten.

Gebruik de formule:

= AANTAL.ALS (kosten, "<=" & D5) + ( AANTAL (kosten) * ISTEXT (D5))


De formule ziet eruit zoals weergegeven in de bovenstaande momentopname. De waarde en matrix worden gegeven als genoemd bereik en celverwijzing in de formule.


Zoals u kunt zien, komt de index van de eerste cel in het bereik uit op 5, wat betekent dat als we de uitgaven in sorteervolgorde plaatsen, Elektriciteit zal op de 5e plaats worden geplaatst. Kopieer nu de formule in andere cellen met behulp van de optie naar beneden slepen of met de sneltoets Ctrl + D zoals hieronder weergegeven om de index of rangorde voor de rest van de waarden te krijgen.

Zoals u kunt zien, hebben we nu een lijst met indexen of rangorden die de kosten sorteren. Nu zullen we deze Rangkolom als hulpkolom gebruiken om de kortere versie van de tabel met dezelfde volgorde in de nieuwe lijst of tabel te krijgen.

Hoe waarden sorteren met behulp van de helperkolom in Excel?

Sorteer het nummer in Excel met behulp van de helperkolom moet indexnummers en het vereiste bereik hebben om te sorteren. Helperkolom kan elke gewenste volgorde zijn. Als u bijvoorbeeld de lijst in de vereiste willekeurige volgorde wilt krijgen, plaatst u gewoon de rang die overeenkomt met de waarde in de index- of rangkolom die als hulpkolom zal werken.

  1. INDEX-functie
  2. MATCH-functie
  3. RIJEN-functie

Nu gaan we een formule maken met behulp van de bovenstaande functies. MATCH-functie retourneert de index van de laagste overeenkomst uit het bereik. De functie INDEX neemt de rij-index als argument en retourneert de bijbehorende vereiste resultaten. Deze functie vindt de

Generieke formule:

= INDEX (return_array, MATCH (ROWS (relatieve_referentie), Index, 0))

return_array : array om waarde te retourneren van

Relatieve referentie : genereert een oplopende volgorde in de formule. Kan met elke array worden gebruikt

Index : Indexarray van de tabel

0 : exacte overeenkomst

Uitleg:

  1. RIJEN (relatieve_verwijzing) retourneert een waarde volgens de lengte van de uitgebreide formule. Als het in de eerste cel wordt toegepast, wordt het één, dan de tweede en gaat door totdat deze zich uitstrekt.
  2. MATCH-functie komt overeen met de index met rijwaarde om ze in oplopende volgorde te krijgen met behulp van de RIJEN-functie.
  3. De functie INDEX retourneert de overeenkomende Index met de bijbehorende waarde.

Voorbeeld:

Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we deze formule dus begrijpen door deze in het onderstaande voorbeeld uit te voeren. Hier hebben we de gegevens in willekeurige volgorde gerangschikt om de eerste drie waarden te krijgen op basis van de indexkolom. Gebruik de formule om de eerste waarde van de vereiste korte tabel te krijgen.

Gebruik de formule:

= INDEX ( B5:B13 , MATCH ( RIJEN (D5:D5),D5:D13,0))

De formule ziet eruit zoals weergegeven in de bovenstaande momentopname. De waardematrix wordt gegeven als genoemd bereik en celverwijzing.

Zoals u kunt zien, is de eerste waarde het nummer uit het bereik dat "Melk" blijkt te zijn, de bijbehorende index is 1. Kopieer nu de formule in andere cellen met behulp van de optie slepen of met de sneltoets Ctrl + D zoals hieronder getoond om te krijgen de rest van de waarden.

Zoals u kunt zien, worden de waarden in oplopende volgorde geïndexeerd. We hebben alle waarden verkregen omdat het ook bewijst dat de formule goed werkt. We kunnen de getalwaarden extraheren met dezelfde formule.

Zoals u kunt zien, hebben we de kortere en gesorteerde waarden uit de tabel met behulp van de helperkolom die dienovereenkomstig is geïndexeerd.

Hier zijn enkele observatienotities die hieronder worden weergegeven.

Opmerkingen:

  1. De formule werkt alleen met zowel cijfers als tekst.
  2. De formule negeert de tekstwaarde bij het vergelijken van getallen en negeert getallen wanneer deze overeenkomen met tekstwaarden.
  3. Helperkolom kan elk type vereiste kolomvolgorde zijn.

Ik hoop dat dit artikel over het indexeren en sorteren van waarden met helperkolom in Excel verklarend is. Bekijk hier meer artikelen over opzoekformules in Excel. Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite

Hoe de SOMPRODUCT-functie in Excel te gebruiken?: Retourneert de SUM na vermenigvuldiging van waarden in meerdere arrays in Excel.

SOM als datum tussen ligt : Retourneert de SOM van waarden tussen bepaalde datums of periode in Excel.

Som als datum groter is dan opgegeven datum: Retourneert de SOM van waarden na de opgegeven datum of periode in Excel.

2 manieren om per maand op te tellen in Excel: Retourneert de SOM van waarden binnen een bepaalde specifieke maand in Excel.

Meerdere kolommen optellen met voorwaarde: Retourneert de SOM van waarden over meerdere kolommen met voorwaarde in Excel

Jokertekens gebruiken in Excel : Tel cellen die overeenkomen met zinnen met behulp van de jokertekens in Excel

populaire artikels

50 Excel-snelkoppeling om uw productiviteit te verhogen : Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.

Hoe te gebruikende VERT.ZOEKEN-functie in Excel : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.

Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.

Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.