Bij het werken met gegevens in Excel kunnen sommige taken onnodig saai aanvoelen. Misschien moet u een kolom met volledige namen opsplitsen in aparte kolommen voor voor- en achternamen, of tekst uit meerdere cellen combineren met specifieke komma's. Dit zijn geen complexe analytische uitdagingen, maar eenvoudige gegevensverwerkingstaken die regelmatig voorkomen.
Het goede nieuws is dat Excel ingebouwde functies heeft die speciaal voor deze situaties zijn ontworpen. Deze worden echter vaak over het hoofd gezien omdat ze geen deel uitmaken van De standaard Excel-toolset die de meeste mensen leren, inclusief ikzelf. De functies die ik hier zal bespreken, gaan niet over geavanceerde berekeningen, maar als je regelmatig gegevens moet verwerken, kunnen deze functies je tijd besparen.
Snelle links
5. TEKSTENPLIT
Scheidt aan elkaar geplakte teksten
Als je ooit een spreadsheet hebt ontvangen waarin iemand zijn voor- en achternaam, en misschien zelfs zijn middelste initialen, in één cel heeft gepropt, dan weet je hoe lastig het is om die gegevens te scheiden. TextSplit lost dit probleem op: het neemt tekst uit één cel en verdeelt deze over meerdere kolommen op basis van een door jou opgegeven scheidingsteken.
Laten we eens werken met een voorbeeld van een verkoopspreadsheet. Je ziet de namen van de salesmedewerkers, zoals "Sarah Chen", "Mike Johnson" en "Lisa Park", allemaal in één kolom. In plaats van elke naam handmatig in aparte kolommen te typen, kan TextSplit het werk automatisch doen.
De formule is als volgt:
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
Dit doet elke leraar:
- tekst: De cel met de tekst die u wilt splitsen.
- kolomscheidingsteken: Het teken dat uw gegevens scheidt (zoals een spatie, komma of puntkomma).
- rij_scheidingsteken (optioneel): Wordt gebruikt bij het splitsen in zowel rijen als kolommen.
- ignore_empty (optioneel): TRUE negeert lege waarden, FALSE behoudt deze (standaard is FALSE).
- match_mode (optioneel): Bepaalt de hoofdlettergevoeligheid (0 is hoofdlettergevoelig, 1 is hoofdletterongevoelig).
- pad_with (optioneel): Wat doe je in lege cellen als de resultaten een ongelijke lengte hebben?
Voor de namen van salesvertegenwoordigers zou ik bijvoorbeeld de volgende formule gebruiken om de namen in afzonderlijke kolommen te splitsen:
=TEKST SPLITSEN(A2, " ")
De functie maakt automatisch het benodigde aantal kolommen aan op basis van uw gegevens. Hoewel deze basisaanpak in de meeste gevallen werkt, zijn er aanvullende parameters die u nauwkeurige controle geven over TEXTSPLIT-functie in Excel.
4. TEKSTJOIN
Meerdere cellen samenvoegen tot één cel
TEXTJOIN doet het tegenovergestelde van TEXTSPLIT. Het haalt tekst uit meerdere cellen en combineert deze tot één cel met behulp van het door u gekozen scheidingsteken. Dit is handig wanneer u opeenvolgende waarden wilt maken, zoals volledige adressen, productbeschrijvingen of e-maillijsten.
De formule ziet er als volgt uit:
=TEXTJOIN(scheidingsteken, negeer_lege, tekst1, [tekst2], ...)
Dit is wat elke parameter regelt:
- scheidingsteken: Het teken of de tekst die de ingesloten waarden scheidt (komma, spatie, streepje, enz.).
- negeer_leeg: TRUE om lege cellen te negeren, FALSE om ze in het resultaat op te nemen.
- tekst1, tekst2, enz.: De cellen of bereiken die u wilt samenvoegen (u kunt afzonderlijke cellen of hele bereiken opgeven).
Als ik naar het verkoopspreadsheet kijk en aparte kolommen heb voor voornaam en regio, maar ze in één gecombineerde kolom wil hebben, dan zou ik TEXTJOIN gebruiken. negeer_leeg Als u deze optie op TRUE instelt, worden lege cellen automatisch overgeslagen.
=TEXTJOIN(" - ", TRUE, B2, D2)Bij het kiezen tussen verschillende tekstintegratiemethoden is het belangrijk om te begrijpen Verschillen tussen CONCAT- en TEXTJOIN-functies Het kan u helpen bij het kiezen van de juiste tool voor uw specifieke behoeften op het gebied van gegevensintegratie.
3. KIESCOLLEN
Geef specifieke kolommen met uw gegevens op.
Met CHOOSECOLS kunt u specifieke kolommen uit een bereik extraheren zonder te kopiëren en plakken of referenties te maken. Als u een grote dataset hebt, maar alleen kolommen 2, 5 en 8 nodig hebt voor uw analyse, gebruikt deze functie alleen de kolommen die u nodig hebt en verwijdert de rest.
Op basis van verkoopgegevens wil ik mogelijk alleen de verkoper en de namen van de verkopers extraheren, waarbij ik orderdata, productcategorieën en andere details negeer. In plaats van handmatig kolommen te selecteren en te kopiëren, creëert de functie CHOOSECOLS een dynamische referentie die automatisch wordt bijgewerkt wanneer de brongegevens veranderen.
De functie volgt de volgende formule:
=KIESKOLOMMEN(array, col_num1, [col_num2], ...)
Dit is hoe elke parameter werkt:
- matrix: Het bereik of de tabel met uw brongegevens (dit kan een celbereik zijn zoals A1:F100 of een tabelverwijzing).
- kolom_num1: Het nummer van de eerste kolom die u wilt extraheren (1 voor de eerste kolom, 2 voor de tweede kolom, enz.).
- kolom_getal2, enz.: Extra kolomnummers die u wilt opnemen (optioneel – u kunt er zoveel opgeven als u wilt).
Als ik bijvoorbeeld de namen van de salesvertegenwoordigers uit kolom 2 en hun status uit kolom 9 wil ophalen, zou ik het volgende gebruiken:
=KIESKOLOMMEN(A1:I23, 2, 9)
De functie retourneert beide kolommen als een gestreamde array, automatisch aangepast aan de grootte van de data. Daarom is CHOOSECOLS een van de Excel-functies waarmee u veel tijd kunt besparenHierdoor is het niet langer nodig om meerdere VLOOKUP-formules te gebruiken of handmatig kolommen te kopiëren bij het werken met grote datasets.
Excel heeft ook een functie KIEZEN. Deze functie werkt op een vergelijkbare manier, maar selecteert specifieke rijen in plaats van kolommen, met dezelfde formulestructuur met rijnummers.
2. NEMEN EN LATEN VALLEN
Delen van uw gegevens extraheren
TAKE en DROP werken samen om specifieke delen van uw gegevensbereik vast te leggen. TAKE extraheert een specifiek aantal rijen of kolommen aan het begin of einde van uw dataset, terwijl DROP rijen of kolommen aan het begin of einde verwijdert, zodat u overhoudt wat er overblijft.
Deze functies fungeren als nauwkeurige hulpmiddelen voor het nemen van steekproeven. Of u nu alleen de eerste tien rijen gegevens nodig hebt voor een snelle analyse, of koprijen wilt verwijderen die uw berekeningen hinderen, deze functies voeren de taak netjes uit.
TAKE gebruikt deze formule:
=NEEM(array, rijen, [kolommen])
DROP volgt een soortgelijk patroon:
=DROP(array, rijen, [kolommen])
Dit is hoe de parameters voor beide functies werken:
- matrix: Het brongegevensbereik dat u wilt extraheren of wijzigen.
- rijen: Aantal rijen dat moet worden genomen/verwijderd (positieve getallen beginnen van boven, negatieve getallen beginnen van onderen).
- kolommen (optioneel):
Het aantal kolommen dat u wilt verwijderen of verwijderen (positief vanaf links, negatief vanaf rechts).
Gebruik de volgende formule om de eerste vijf rijen met verkoopgegevens te verkrijgen:
=NEEM(A1:C100, 5)
Om de eerste 20 rijen te verwijderen en met schone gegevens te werken, kunt u het volgende proberen:
=DROP(A1:C23, 20)
U kunt rij- en kolombewerkingen combineren. De volgende formule geeft u bijvoorbeeld de eerste tien rijen en de eerste drie kolommen:
=NEEM(A1:F23, 10, 3)
Deze functies zijn erg handig, vooral als u dynamische subsets van gegevens nodig hebt die zich automatisch aanpassen. De functies TAKE en DROP in Excel gebruiken Het biedt de mogelijkheid om flexibele rapporten te maken die zich aanpassen aan veranderende datasetgroottes.
1. AGGREGAAT
Krachtige berekeningen die rommelige gegevens verwerken
AGGREGATE combineert de functionaliteit van 19 verschillende statistische functies in één flexibele formule. Wat het onderscheidt, is de mogelijkheid om fouten, verborgen rijen of gefilterde gegevens te negeren – iets wat standaardfuncties zoals SUM of AVERAGE niet betrouwbaar kunnen.
Als uw gegevens fouten met #N/A bevatten, of als u filtert om alleen bepaalde regio's weer te geven, kan AGGREGATE sommen, gemiddelden of andere statistieken berekenen zonder dat deze problemen uw resultaten verstoren. Ik vind het handig bij het werken met dynamische datasets waarbij de zichtbaarheid en datakwaliteit regelmatig veranderen.
De zinsstructuur bestaat uit verschillende onderdelen:
=AGGREGATE(functie_num, opties, array, [k])
Elk criterium bepaalt verschillende aspecten van de berekening:
- functie_getal: Een getal van 1 tot en met 19 dat aangeeft welke functie moet worden gebruikt (1=GEMIDDELDE, 4=MAX, 9=SOM, 12=MEDIAAN, enz.).
- opties: Bepaalt wat er tijdens de berekening moet worden genegeerd (0=geen, 1=verborgen rijen, 2=foutwaarden, 3=verborgen rijen en fouten, 5=alleen foutwaarden, 6=verborgen rijen en foutwaarden).
- matrix: Het bereik van cellen dat berekend moet worden.
- k (optioneel):
- Wordt alleen gebruikt bij bepaalde functies, zoals GROOT, KLEIN of PERCENTIEL.
Om de getoonde verkoopbedragen samen te vatten en eventuele fouten te negeren, kan ik het volgende gebruiken:
=AGGREGATE(9, 6, D2:D23)
Het getal 9 geeft SOM aan en het getal 6 vertelt de functie dat zowel verborgen rijen als foutwaarden moeten worden genegeerd.
AGGREGATE is speciaal ontwikkeld vanwege de krachtige mogelijkheid om berekeningen uit te voeren. Lijst met Excel-functies die elke kantoormedewerker zou moeten kennen—Het verwerkt de chaos van echte gegevens die niet effectief beheerd kan worden door eenvoudigere functies.
Ingebouwde tools die de moeite waard zijn om te gebruiken
De belangrijkste Excel-functies zijn vaak niet de functies die mensen als eerste leren. Ze pakken echter de subtiele problemen aan die zich voordoen bij het werken met spreadsheets, zoals het verwerken van rommelige tekstgegevens, het extraheren van specifieke delen uit grote datasets en het uitvoeren van berekeningen met onvolledige gegevens. Geen van de functies die we hebben besproken, vereist geavanceerde Excel-vaardigheden. TEXTSPLIT, CHOOSECOLS, TAKE en DROP zijn echter alleen beschikbaar in Microsoft 365 en Excel voor het web.
De volgende keer dat u herhaaldelijk gegevens opschoont of handmatig kolommen kopieert, bedenk dan dat deze functies bestaan. Ze zijn al ingebouwd in Excel om de vervelende taken af te handelen, zodat u zich kunt concentreren op wat de gegevens u daadwerkelijk vertellen.
