In dit artikel wordt besproken hoe u alle waarden uit een tabel kunt matchen en ze in verschillende cellen kunt ophalen. Dit is vergelijkbaar met het opzoeken van meerdere waarden.
Generieke formule
{=INDEX(names,SMALL(IF(groups=group_name,ROW(names)-MIN(ROW(names))+1),COLUMNS(expanding ranges))), "--List Ends--")}
Te veel functies en variabelen!!!. Laten we eens kijken wat deze variabelen zijn.
namen: Dit is de lijst met namen.
Groepen: De lijst met groepen waartoe deze namen ook behoren.
Groepsnaam: de referentie van de groepsnaam.
Bereik uitbreiden: dit is het uitdijende bereik dat wordt gebruikt om een toenemend aantal te krijgen wanneer het naar rechts wordt gekopieerd.
Voorbeeld: Extraheer de namen van werknemers in verschillende kolommen op basis van hun bedrijf.
Stel dat u een tabel met werknemers hebt die is gegroepeerd op basis van hun bedrijf. De eerste kolom bevat de namen van werknemers en de tweede kolom bevat de naam van het bedrijf.
Nu moeten we de naam van elke werknemer in verschillende kolommen krijgen, afhankelijk van hun bedrijf. Met andere woorden, we moeten ze degroeperen.
Hier heb ik A2:A10 als werknemer en B2:B10 als bedrijf genoemd, zodat die formule gemakkelijk te lezen is.
Schrijf deze matrixformule in F2. Gebruik CTRL+SHIFT+ENTER om deze formule in te voeren.
{=INDEX(Werknemer,SMALL(IF(Bedrijf=$E2,ROW(Werknemer)-MIN(ROW(Werknemer))+1),KOLOMMEN($E$1:E1))), "--Lijst eindigt--" )}
Kopieer deze formule in alle cellen. Het zal elke individuele naam in de verschillende kolommen extraheren op basis van hun groep.
Zoals je kunt zien in de afbeelding hierboven, is elke medewerker gescheiden in verschillende cellen.
Dus, hoe werkt deze formule?
Laten we, om de formule te begrijpen, eens kijken naar de formule in G2
Dat is =IFERROR(INDEX(Medewerker,KLEIN(ALS(Bedrijf=$E3,RIJ(Medewerker)-MIN(RIJ(Medewerker))+1),KOLOMMEN ($E$1:F2))),"--Lijst eindigt--")
De mechanica is eenvoudig en bijna hetzelfde als de formule met meerdere VERT.ZOEKEN. De kunst is om het indexnummer van elke medewerker uit verschillende groepen te halen en door te geven aan de INDEX-formule. Dit wordt gedaan door dit deel van de formule.
INDIEN(Bedrijf=$E3,RIJ(Medewerker)-MIN(RIJ(Medewerker))+1):
Dit deel retourneert een reeks indexen en false voor bedrijfsnaam in $E3, die "Rankwatch" bevat.
{FALSE;2;FALSE;4;FALSE;FALSE;7;FALSE;9}.
Hoe? Laten we het van binnenuit afbreken.
Hier matchen we de bedrijfsnaam in $E3 met elke waarde in Bedrijfsbereik (Bedrijf=$E3).
Dit retourneert een array van true en false.{FALSE;WAAR;FALSE;WAAR;FALSE;FALSE;WAAR;FALSE;WAAR}.
Nu voert de IF-functie de TRUE-instructies uit voor TRUE, namelijk ROW(Employee)-MIN(ROW(Medewerker))+1. Dit deel retourneert dit deel retourneert een array van indexen beginnend van 1 tot aantal medewerkers {1;2;3;4;5;6;7;8;9}. De if-functie geeft alleen waarden weer voor TRUE, wat ons op zijn beurt {FALSE;2;FALSE;4;FALSE;FALSE;7;FALSE;9} geeft.
De huidige formule is vereenvoudigd tot
=IFERROR(INDEX(Werknemer,KLEIN( {FALSE;2;FALSE;4;FALSE;FALSE;7;FALSE;9},KOLOMMEN($E$1:F2))),"--Lijst eindigt--"). Zoals we weten, retourneert een kleine functie de n-de kleinste waarde uit een array. KOLOMMEN($E$1:F2) dit retourneert 2. De functie SMALL retourneert de op een na kleinste waarde uit de bovenstaande array, namelijk 4.
Nu is de formule vereenvoudigd =IFERROR(INDEX(Werknemer,4),"--Lijst eindigt--"). Nu retourneert de INDEX-functie eenvoudig de vierde naam van medewerker array die ons "Sam”.
Dus ja, dit is hoe namen uit groepen in verschillende kolommen worden geëxtraheerd met behulp van de INDEX-, SMALL-, ROW-, COLUMNS- en IF-functie. Als u enige twijfel heeft over deze functie of als deze niet voor u werkt, laat het me dan weten in de opmerkingen hieronder.
Download bestand:
Gerelateerde artikelen:
VERT.ZOEKEN Meerdere waarden
Gebruik INDEX en MATCH om waarde op te zoeken
Opzoekwaarde met meerdere criteria
Populaire artikels:
De VERT.ZOEKEN-functie in Excel
AANTAL.ALS in Excel 2016
Hoe de SUMIF-functie in Excel te gebruiken?