10 essentiële Microsoft Excel-functies voor gegevensanalyse
Diversen / / November 29, 2021
1. SAMENVOEGEN
=CONCATENATE is een van de meest cruciale functies voor gegevensanalyse, omdat u hiermee tekst, getallen, datums, enz. kunt combineren. van meerdere cellen naar één. De functie is met name handig voor het combineren van gegevens uit verschillende cellen in een enkele cel. Het is bijvoorbeeld handig voor het maken van de trackingparameters voor marketingcampagnes, het bouwen van API-query's, het toevoegen van tekst aan een getalnotatie en verschillende andere dingen.
In het bovenstaande voorbeeld wilde ik de maand en de verkoop samen in één kolom. Daarvoor heb ik de formule =CONCATENATE(A2, B2) in cel C2 gebruikt om Jan $ 700 als resultaat te krijgen.
Formule: = SAMENVOEGEN(cellen die u wilt combineren)
2. LEN
=LEN is een andere handige functie voor gegevensanalyse die in wezen het aantal tekens in een bepaalde cel uitvoert. De functie is voornamelijk bruikbaar bij het maken van titeltags of beschrijvingen met een tekenlimiet. Het kan ook handig zijn als u de verschillen probeert te achterhalen tussen verschillende unieke identificatiecodes, die vaak vrij lang en niet in de juiste volgorde zijn.
In het bovenstaande voorbeeld wilde ik de cijfers tellen voor het aantal weergaven dat ik elke maand kreeg. Hiervoor heb ik de formule =LEN(C2) in de cel D2 gebruikt om 5 als resultaat te krijgen.
Formule: =LENGTE(cel)
3. VERT.ZOEKEN
=VERT.ZOEKEN is waarschijnlijk een van de meest herkenbare functies voor iedereen die bekend is met gegevensanalyse. U kunt het gebruiken om gegevens uit een tabel te matchen met een invoerwaarde. De functie biedt twee manieren om te matchen - exact en bij benadering, die wordt bepaald door het bereik van de zoekopdracht. Als u het bereik instelt op ONWAAR, zoekt het naar een exacte overeenkomst, maar als u het instelt op TRUE, zoekt het naar een geschatte overeenkomst.
In het bovenstaande voorbeeld wilde ik het aantal weergaven in een bepaalde maand opzoeken. Daarvoor gebruikte ik de formule =VLOOKUP("Jun", A2:C13, 3) in de cel G4 en ik kreeg 74992 als resultaat. Hier is "Jun" de opzoekwaarde, A2:C13 is de tabelmatrix waarin ik zoek naar "Jun" en 3 is het nummer van de kolom waarin de formule de overeenkomstige weergaven voor juni zal vinden.
Het enige nadeel van het gebruik van deze functie is dat het alleen werkt met gegevens die in kolommen zijn gerangschikt, vandaar de naam - verticaal opzoeken. Dus als u uw gegevens in rijen hebt gerangschikt, moet u eerst: zet de rijen om in kolommen.
Formule: =VERT.ZOEKEN(lookup_value, table_array, col_index_num, [range_lookup])
4. INDEX/MATCH
Net als de VLOOKUP-functie, zijn de INDEX- en MATCH-functie handig voor het zoeken naar specifieke gegevens op basis van een invoerwaarde. De INDEX en MATCH kunnen, wanneer ze samen worden gebruikt, de beperkingen van VERT.ZOEKEN overwinnen om de verkeerde resultaten te leveren (als u niet voorzichtig bent). Dus wanneer u deze twee functies combineert, kunnen ze de gegevensreferentie lokaliseren en naar een waarde zoeken in een array met één dimensie. Dat retourneert de coördinaten van de gegevens als een getal.
In bovenstaand voorbeeld wilde ik het aantal views in januari opzoeken. Daarvoor gebruikte ik de formule =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3). Hier is A2:C13 de kolom met gegevens waarvan ik wil dat de formule retourneert, "Jan" is de waarde die ik wil matchen, A2:A13 is de kolom waarin de formule "Jan" zal vinden en de 0 betekent dat ik wil dat de formule een exacte overeenkomst vindt voor de waarde.
Als u bij benadering een overeenkomst wilt vinden, moet u de 0 vervangen door 1 of -1. Zodat de 1 de grootste waarde vindt die kleiner is dan of gelijk is aan de opzoekwaarde en -1 de kleinste waarde vindt die kleiner is dan of gelijk is aan de opzoekwaarde. Houd er rekening mee dat als u geen 0, 1 of -1 gebruikt, de formule 1 gebruikt door.
Als u de naam van de maand niet hard wilt coderen, kunt u deze vervangen door het celnummer. Dus we kunnen "Jan" in de bovenstaande formule vervangen door F3 of A2 om hetzelfde resultaat te krijgen.
Formule: =INDEX(kolom van de gegevens die u wilt retourneren, MATCH(gemeenschappelijk gegevenspunt dat u probeert te matchen, kolom van de andere gegevensbron met het gemeenschappelijke gegevenspunt, 0))
5. MINIFS/MAXIFS
=MINIFS en =MAXIFS lijken erg op de functies =MIN en =MAX, behalve dat u hiermee de minimum/maximum reeks waarden kunt nemen en deze ook kunt afstemmen op bepaalde criteria. Dus in wezen zoekt de functie naar de minimum/maximum waarden en matcht deze met invoercriteria.
In het bovenstaande voorbeeld wilde ik de minimumscores vinden op basis van het geslacht van de student. Daarvoor gebruikte ik de formule =MINIFS (C2:C10, B2:B10, "M") en ik kreeg het resultaat 27. Hier is C2:C10 de kolom waarin de formule zoekt naar de scores, B2:B10 is een kolom waarin de formule zoekt naar de criteria (het geslacht), en "M" is de criteria.
Evenzo gebruikte ik voor maximale scores de formule =MAXIFS(C2:C10, B2:B10, "M") en kreeg het resultaat 100.
Formule voor MINIFS: =MINIFS(min_bereik, criteria_bereik1, criteria1,...)
Formule voor MAXIFS: =MAXIFS(max_bereik, criteria_bereik1, criteria1,...)
6. GEMIDDELDE IFS
Met de functie =AVERAGEIFS kunt u een gemiddelde vinden voor een bepaalde dataset op basis van een of meer criteria. Houd er bij het gebruik van deze functie rekening mee dat elk criterium en elk gemiddeld bereik anders kan zijn. In de functie =GEMIDDELDE.ALS moeten zowel het criteriumbereik als het sombereik echter hetzelfde groottebereik hebben. Let op het verschil tussen enkelvoud en meervoud tussen deze functies? Nou, daar moet je voorzichtig mee zijn.
In dit voorbeeld wilde ik de gemiddelde score vinden op basis van het geslacht van de studenten. Daarvoor gebruikte ik de formule =AVERAGEIFS(C2:C10, B2:B10, "M") en kreeg 56,8 als resultaat. Hier is C2:C10 het bereik waarin de formule zoekt naar het gemiddelde, B2:B10 is het criteriabereik en "M" is het criterium.
Formule: =GEMIDDELDE.ALS(gemiddelde_bereik, criteria_bereik1, criteria1,...)
7. AANTAL.ALS
Als u nu het aantal exemplaren wilt tellen dat een gegevensset aan specifieke criteria voldoet, moet u de functie =AANTALLEN.ALS gebruiken. Met deze functie kunt u onbeperkte criteria aan uw zoekopdracht toevoegen, waardoor het de gemakkelijkste manier is om de telling te vinden op basis van de invoercriteria.
In dit voorbeeld wilde ik het aantal mannelijke of vrouwelijke studenten vinden met voldoende punten (d.w.z. >=40). Daarvoor gebruikte ik de formule =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Hier is B2:B10 het bereik waarin de formule zoekt naar de eerste criteria (geslacht), "M" is de eerste criteria, C2:C10 is het bereik waarin de formule zoekt naar de tweede criteria (cijfers), en ">=40" is de tweede criteria.
Formule: =AANTALLEN.ALS(criteria_bereik1, criteria1,...)
8. SOMPRODUCT
De functie =SOMPRODUCT helpt u bereiken of arrays met elkaar te vermenigvuldigen en retourneert vervolgens de som van de producten. Het is een vrij veelzijdige functie en kan worden gebruikt om arrays zoals AANTALLEN.ALS of SOMMEN te tellen en optellen, maar met extra flexibiliteit. U kunt ook andere functies binnen SUMPRODUCT gebruiken om de functionaliteit nog verder uit te breiden.
In dit voorbeeld wilde ik het totaal van alle verkochte producten vinden. Daarvoor gebruikte ik de formule =SOMPRODUCT(B2:B8, C2:C8). Hier is B2:B8 de eerste array (de hoeveelheid verkochte producten) en C2:C8 is de tweede array (de prijs van elk product). De formule vermenigvuldigt vervolgens de hoeveelheid van elk verkocht product met zijn prijs en telt dit vervolgens allemaal op om de totale verkoop te leveren.
Formule: =SOMPRODUCT(array1, [array2], [array3],...)
9. TRIM
De functie =TRIM is vooral handig wanneer u werkt met een gegevensset die meerdere spaties of ongewenste tekens heeft. Met deze functie kunt u deze spaties of tekens gemakkelijk uit uw gegevens verwijderen, zodat u nauwkeurige resultaten krijgt terwijl u andere functies gebruikt.
In dit voorbeeld wilde ik alle extra spaties tussen de woorden muis en pad in A7 verwijderen. Daarvoor gebruikte ik de formule =TRIM(A7).
De formule verwijderde eenvoudig de extra spaties en leverde het resultaat Muismat met een enkele spatie.
Formule: =TRIM(tekst)
10. VINDEN/ZOEKEN
Afronding zijn de FIND/SEARCH-functies waarmee u specifieke tekst binnen een dataset kunt isoleren. Beide functies lijken veel op wat ze doen, met uitzondering van één groot verschil: de functie = FIND retourneert alleen hoofdlettergevoelige overeenkomsten. Ondertussen heeft de =SEARCH-functie dergelijke beperkingen niet. Deze functies zijn vooral handig bij het zoeken naar anomalieën of unieke identifiers.
In dit voorbeeld wilde ik het aantal keren dat 'Gui' verscheen binnen Guiding Tech vinden waarvoor ik de formule =FIND(A2, B2) gebruikte, wat het resultaat 1 opleverde. Als ik nu het aantal keren wilde weten dat 'gui' in Guiding Tech verscheen, zou ik de =SEARCH-formule moeten gebruiken omdat deze niet hoofdlettergevoelig is.
Formule voor zoeken: =VIND(vind_tekst, binnen_tekst, [start_num])
Formule voor zoeken: =ZOEKEN(vind_tekst, binnen_tekst, [start_num])