Na jarenlang met complexe en rommelige spreadsheets te hebben gewerkt, ontdekte ik vier Excel-functies die me wekelijks uren werk besparen door routinetaken te automatiseren die de meeste mensen handmatig uitvoeren. Deze functies zijn onmisbaar voor iedereen die regelmatig met data werkt, of je nu een professionele data-analist bent of gewoon een gewone gebruiker die zijn werk wil vereenvoudigen.
Snelle links
4. XLOOKUP: Geavanceerd opzoeken in spreadsheets
XZOEKEN Het is een geavanceerde zoekfunctie in spreadsheetprogramma's zoals Microsoft Excel en Google Sheets, die verder gaat dan de mogelijkheden van traditionele zoekfuncties zoals VLOOKUP و HLOOKUPBeschikbaarheid. XZOEKEN Grotere flexibiliteit, efficiëntere gegevensverwerking en minder veelvoorkomende fouten die samenhangen met oudere functies. XZOEKEN Een essentiële tool voor financiële analisten, datawetenschappers en iedereen die met grote hoeveelheden data werkt en snel en nauwkeurig specifieke informatie moet extraheren. XZOEKENU kunt zoeken naar een waarde in een specifiek bereik en een overeenkomstige waarde uit een ander bereik retourneren, ongeacht de locatie van de kolommen of rijen. Het ondersteunt ook XZOEKEN Zoekt van rechts naar links en van onder naar boven, waardoor deze functie veelzijdiger is dan andere functies.
Vaarwel VLOOKUP: XLOOKUP is de perfecte oplossing
Ik ben jaren geleden gestopt met VLOOKUP toen ik XLOOKUP ontdekte. VLOOKUP zoekt alleen naar rechts en crasht wanneer je kolommen verplaatst, maar XLOOKUP werkt in elke richting en blijft flexibel. XLOOKUP is een van de Excel-functies waarmee u tijd kunt besparen Vind specifieke gegevens in uw spreadsheets.
In mijn prijsgegevens voor computercomponenten moet ik specifieke GPU-prijzen vinden op basis van productmodellen. Met VLOOKUP zou ik de hele tabel moeten herstructureren. Maar met XLOOKUP hoef ik alleen maar het volgende te typen:
=XLOOKUP("GIGABYTE GeForce RTX 3060 12GB Gaming OC", C:C, D:D)
XLOOKUP doorzoekt de volledige productkolom, vindt mijn GPU en retourneert de bijbehorende prijs. Het maakt niet uit waar de prijskolom zich bevindt en het crasht niet als ik later meer kolommen toevoeg. Ik gebruik dit constant om productinformatie in verschillende werkbladen te raadplegen zonder iets opnieuw te hoeven opmaken.
De basisformule voor XLOOKUP is:
=XLZOEKEN(opzoekwaarde; opzoekmatrix; retourmatrix)
- opzoekwaarde: De waarde waarnaar u wilt zoeken.
- lookup_array: De plek waar u op zoek bent naar waarde.
- retour_array: De kolom of rij die de waarde bevat die u wilt retourneren.
Dus in mijn geval was de waarde die ik wilde vinden "GIGABYTE GeForce RTX 3060 12GB Gaming OC". Ik wilde deze waarde zoeken in de kolom C:C en de corresponderende waarde uit D:D retourneren in dezelfde rij waar de match was gevonden.
Wat ik ook prettig vind aan XLOOKUP, is dat als ik ",-1" aan het einde van de formule toevoeg, de functie van onder naar boven zoekt, waardoor ik automatisch de meest recente prijsinvoer vind. Dit bespaart me de moeite om de gegevens handmatig te sorteren telkens wanneer ik mijn spreadsheets vernieuw.
3. Mijn functies gebruiken SOMMEN.ALS و COUNTIFS In spreadsheets
Professioneel omgaan met meerdere normen
De basisfuncties SOM en AANTAL zijn voldoende voor eenvoudige taken, maar schieten tekort bij analyses in de praktijk. Wanneer ik mijn prijsgegevens onder meerdere omstandigheden moet analyseren, gebruik ik meestal de functies SOMMEN.ALS en AANTALLEN.ALS. Hiermee kan ik eenvoudig honderden rijen segmenteren.
Stel dat ik het aantal AMD-processors wil tellen dat beschikbaar is op Amazon US. In plaats van handmatig te filteren, typ ik:
=COUNTIFS(F:F, "Amazon US", K:K, "AMD")
Dit laat me meteen zien dat er 14 AMD-processors op Amazon in mijn dataset staan. Het mooie hiervan is dat ik zoveel benchmarks kan samenstellen als ik nodig heb.
Voor prijsanalyse werkt de functie SOM.ALS op dezelfde manier. Om de totale waarde van alle Intel-processors die momenteel op voorraad zijn te berekenen, gebruik ik:
=SUMIFS(D:D, K:K, "Intel", G:G, "Op voorraad")
Hiermee worden alle prijzen in kolom D opgeteld, waarbij het merk 'Intel' is en de voorraadstatus 'Op voorraad'.
De syntaxis voor de functie SOMMEN.ALS is:
=SUMIFS(sombereik, criteriabereik1, criteria1, criteriabereik2, criteria2...)
- som_bereik: De kolom waarvan u de som wilt berekenen.
- criteria_bereik1: De eerste kolom om de voorwaarden te controleren.
- criterium1: Eerste bereikconditie.
- criteria_bereik2, criteria2: Aanvullende voorwaarden (optioneel).
De functie AANTALLEN.ALS werkt op een vergelijkbare manier, maar telt nu de overeenkomende rijen in plaats van de waarden op te tellen:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2...)
Ik gebruik bij voorkeur SOMMEN.ALS en AANTALLEN.ALS voor snelle rapporten, omdat ze direct nieuwe gegevens bijwerken, perfect in mijn bestaande formules passen en me in staat stellen alles overzichtelijk te houden zonder een aparte draaitabel te maken. Deze tools maken nauwkeurige en efficiënte data-analyse mogelijk, wat tijd en moeite bespaart bij het ontwikkelen van complexe rapporten. Het gebruik van functies zoals SOMMEN.ALS en AANTALLEN.ALS is een essentiële vaardigheid voor elke data-analist die snel en eenvoudig waardevolle inzichten uit data wil halen.
2. Trimmen en schoonmaken: essentiële stappen om het uiterlijk te behouden
Vaarwel data-rommel
Niets verpest een spreadsheet sneller dan ongestructureerde data vol extra spaties en verborgen tekens. Ik heb dit op de harde manier geleerd toen mijn zoekopdrachten steeds mislukten vanwege extra spaties aan het einde van formuliernamen.
De TRIM-functie verwijdert extra spaties aan het begin en einde van tekst, evenals extra spaties tussen woorden. Wanneer ik gegevens uit verschillende bronnen importeer, bevatten productnamen vaak inconsistente spaties. In plaats van elke cel handmatig op te schonen, maak ik een hulpkolom en gebruik ik:
=TRIM(C2)
Vervolgens verplaats ik de muisaanwijzer naar de rand van de cel totdat deze in een plusteken (+) verandert. Vervolgens sleep ik de muisaanwijzer naar alle rijen waarop ik de TRIM-functie wil toepassen.
1. TEXTEFORE en TEXTAFTER: Een gedetailleerde uitleg en hun belang
Haal de benodigde gegevens nauwkeurig op
De functies TEXTBEFORE en TEXTAFTER behoren tot mijn favoriete Excel-functies om rommelige spreadsheets op te schonen. De moderne tekstfuncties van Excel blinken uit in het extraheren van specifieke informatie uit ongestructureerde tekstreeksen. Zo stonden in mijn kolom 'Prijzen' bijvoorbeeld items als "$ 177.52", "178.33 USD", "₱ 9055" en "9645.50 PHP" door elkaar.
De functie TEXTBEFORE extraheert alles dat voorafgaat aan een opgegeven scheidingsteken:
=TEXTBEFORE(D2, "USD")
Op deze manier extraheert de functie onmiddellijk “178.33” uit “178.33 USD”.
De TEXTAFTER-functie werkt omgekeerd en extraheert alles na het scheidingsteken:
=TEXTAFTER(C2, "AMD ")
Op deze manier heb ik de functie “Ryzen 5 5700X 8-Core AM4 Processor” uit “AMD Ryzen 5 5700X 8-Core AM4 Processor” gehaald.
Voor complexe extracties combineer ik beide functies. Om de numerieke prijs van $ 177.52 USD te krijgen:
=TEXTBEFORE(TEXTAFTER(D8, "$"), "USD")
De algemene syntaxis van de functies TEXTBEFORE en TEXTAFTER is:
=TEXTBEFORE(text, delimiter) en =TEXTAFTER(text, delimiter)
De enorme verbetering die deze twee functies met zich meebrengen, schuilt in hun precisie. In plaats van complexe combinaties van MID-, FIND- en LEN-functies te gebruiken, kan ik nu zuivere extracties bereiken met behulp van eenvoudige, gemakkelijk te lezen formules. Ik gebruik deze functies regelmatig om modelnummers te scheiden, productspecificaties te extraheren en zuivere gegevens uit geïmporteerde tekst te halen, waarvoor ik voorheen urenlang handmatig moest bewerken.
Deze vier functies pakken enkele van de grootste tijdverspillers in Excel aan, zoals het vinden van gegevens met behulp van flexibele zoekfuncties, het analyseren op basis van meerdere criteria, het opschonen van rommelige geïmporteerde tekst en het extraheren van specifieke informatie uit complexe tekstreeksen. De meeste mensen voeren deze taken handmatig uit en besteden uren aan wat normaal gesproken slechts enkele minuten zou kosten om de juiste formules te implementeren.
U hebt deze functies voor alles gebruikt, van analyse van componentprijzen tot rapporten over voorraadbeheer. Ze werken ongeacht uw branche, want rommelige gegevens en complexe zoekvereisten zijn universele problemen. Zodra u deze functies onder de knie hebt, vraagt u zich af hoe u ooit zonder spreadsheets hebt gewerkt.
