15 essentiële Excel-formules en -functies, met Copilot-prompts

Nauwkeurige en betrouwbare gegevens beginnen met de juiste basis. Verminder handmatige fouten, onderschept fouten vroeg en bouw logica die elke keer dat gegevens worden gewijzigd met behulp van Microsoft Excel-formules, blijft behouden. Van eenvoudige totalen tot samenvattingen met meerdere voorwaarden en zoekacties voor meerdere tabellen, elk is ingebouwd in Excel en zonder installatie beschikbaar in de browser.

Verken 15 essentiële Excel-formules en -functies, van het opschonen van geïmporteerde gegevens tot het analyseren en samenvatten van gegevenssets. Elke sectie bevat praktijkvoorbeelden en Copilot in Microsoft Excel-acties om aan de slag te gaan zonder de syntaxis te onthouden.

Gegevensopruiming voor nauwkeurige formules

Het is de eerste stap om gegevens in een consistente, bruikbare status te krijgen voordat u een analyse uitvoert. Gebruik deze functies om duplicaten te verwijderen, de afstand te corrigeren en tekst te standaardiseren, zodat formules, opzoekacties en draaitabelsamenvattingen betrouwbare resultaten opleveren.

• TRIM: verwijdert extra spaties aan het begin en einde van een tekenreeks en vermindert meerdere spaties tussen woorden tot één spatie. Plak namen of id's van een ander systeem en TRIM schoont de inconsistenties in de afstand op die vert.ZOEKEN-formules en AANTALLEN.ALS verbreken.

• CONCAT en TEXTJOIN: voeg tekst uit afzonderlijke kolommen samen in één veld. TEXTJOIN voegt een gekozen scheidingsteken toe tussen waarden, waardoor het sneller is om volledige adressen, productlabels of weergavenamen te maken op basis van gesplitste brongegevens.

• LINKS en RECHTS: trek een vast aantal tekens uit een van beide uiteinden van een tekenreeks. Gebruik LINKS om een regiocode te extraheren vanaf het begin van een product-id of RECHTS om een bestandsextensie of datumonderdeel te isoleren van het einde van een veld.

• SORTEREN: retourneert een opnieuw gerangschikte versie van een bereik zonder de oorspronkelijke gegevens aan te raken. Een sorteren projectplanningslijst op einddatum of volgorde van een inventaristracker op voorraadniveau voordat u bekijkt wat moet worden aangevuld.

• FILTER: retourneert alleen de rijen die voldoen aan een ingestelde voorwaarde. Actieve accounts ophalen uit een volledige clientlijst, alleen achterstallige taken van een projecttracker of isoleer één maand aan vermeldingen uit een gegevensset voor het hele jaar.

• Dubbele waarden verwijderen: hiermee worden herhaalde rijen uit een geselecteerd bereik in één stap verwijderd. Voer deze uit op een lijst met contactpersonen voordat een campagne wordt verzonden, op een samengevoegde klantexport om unieke records op te halen of op een trefwoordenlijst die is opgehaald uit meerdere SEO-hulpprogramma's.

Probeer deze Copilot-voorbeeldprompts

Stel een handige manier voor om duplicaten te verwijderen en de tekstopmaak in deze tabel te standaardiseren. Klik om deze Copilot-prompt te kopiëren

Totalen, gemiddelden en aantallen in spreadsheetgegevens

Totalen, gemiddelden, aantallen en de hoogste en laagste waarden in een bereik worden vaak gebruikt in gegevensbladen. De volgende zeven functies kunnen worden toegepast op numerieke bereiken in een zakelijke spreadsheet.

• SOM: telt elke waarde in een geselecteerd bereik op, van een persoonlijke budgetplanner, het bijhouden van maandelijkse uitgaande uitgaven naar een balans afstemmen van activa en passiva.

• GEMIDDELDE: berekent het gemiddelde over een bereik. Gemiddelde orderwaarden van meer dan een kwartaal bijhouden, winst- en verliesoverzichten, gemiddelde reactietijden in een ondersteuningsteam of de gemiddelde score van een antwoordkolom voor enquêtes.

• MIN en MAX: retourneren de laagste en hoogste waarden in een bereik. Koppel ze om uitschieters in een onkostengegevensset te herkennen, te controleren of de gegevensinvoer binnen de verwachte grenzen blijft of om de beste en slechtste prestaties in een verkoopkolom weer te geven zonder het hele blad te sorteren.

• COUNT en COUNTA: COUNT tallies cellen met getallen; COUNTA telt alle niet-lege cellen. Gebruik COUNTA in een registratiekolom om te zien hoeveel reacties er zijn binnengekomen, of REKEN op een kolom omzet om te controleren hoeveel transacties er tot nu toe zijn ingevoerd.

• AANTAL.ALS: telt cellen die voldoen aan één voorwaarde. Hoeveel facturen hebben de status Betaald? Hoeveel enquêteantwoorden geselecteerd optie B? AANTAL.ALS retourneert het getal zonder dat u eerst hoeft te filteren of sorteren.

• SOM.ALS: totalen van waarden in één kolom op basis van een voorwaarde in een andere. Tel uren op die door één teamlid zijn geregistreerd in een rooster, totale kosten van één leverancier in een spreadsheet voor zakelijke budgettering , som van ondersteuningstickets die door één agent in een week zijn gesloten of totale onkosten per categorie van een reisclaim.

• RANG. EQ: retourneert de positie van een getal in een lijst, van hoog naar laag of omgekeerd. Rangschik verkopers op omzet, bestel campagneresultaten op conversiepercentage of bouw een leaderboard op basis van een resultatenkolom zonder de onderliggende gegevens te wijzigen.

Probeer deze Copilot-voorbeeldprompts

Welke formule moet ik gebruiken om te tellen hoeveel taken in dit werkblad als voltooid zijn gemarkeerd? Klik om deze Copilot-prompt te kopiëren

Voorwaardelijke logica en foutafhandeling

Logische formules veranderen statische cellen in voorwaardelijke cellen en houden een spreadsheet leesbaar wanneer er iets misgaat. Stel een formule in om verschillende resultaten te retourneren op basis van wat de gegevens bevatten, combineer meerdere voorwaarden in één test of vervang formulefouten door een schone waarde in plaats van een gebroken cel.

• ALS: retourneert een resultaat wanneer een voorwaarde waar is en een andere als deze onwaar is. Achterstallige facturen markeren in een projectmanagementtracker , label enquêtescores als 'Boven doel' of 'Onder doel', of markeer taken in een takenlijst als voltooid op basis van de waarde in een statuskolom.

• FOUT: vangt elke fout op die een formule retourneert en vervangt deze door een opgegeven waarde, zoals een streepje, een nul of een gewone notitie. Plaats de formule rond een VERT.ZOEKEN-formule die mogelijk niet altijd een overeenkomst vindt om de resultaten leesbaar te houden.

• AND en OR: combineer meerdere voorwaarden in één logische test. AND retourneert alleen waar wanneer aan elke voorwaarde wordt voldaan; OF retourneert waar wanneer er ten minste één is. Nest in een ALS-formule om nauwkeurigere regels te maken, zoals het alleen markeren van een record wanneer zowel de regio als de status overeenkomen met een doel.

Probeer deze Copilot-voorbeeldprompts

Ik wil dat kolom C 'Op schema' weergeeft wanneer de waarde in kolom B hoger is dan 80%, en 'Moet worden beoordeeld' als dat niet het juiste is. Welke formule moet ik gebruiken? Klik om deze Copilot-prompt te kopiëren

Gegevens opzoeken en vergelijken

Zoekformules zoeken een waarde in de ene tabel en retourneren gerelateerde informatie uit een andere tabel. Gebruik deze om namen van klanten op te halen uit order-id's, productcodes te vergelijken met prijscategorieën, specifieke waarden in grote waarden te vinden budgetplanners, trefwoorden toewijzen aan intentiecategorieën uit een taxonomieblad of een verrijkende factuurgenerator met accountgegevens uit een afzonderlijke lijst.

• VERT.ZOEKEN: doorzoekt een tabel van links naar rechts en retourneert een waarde uit een opgegeven kolom wanneer een overeenkomst wordt gevonden. Wordt nog steeds veel gebruikt in gedeelde werkmappen en verouderde bestanden.

• XLOOKUP: zoekt in elke richting en kan een opgegeven waarde retourneren wanneer er geen overeenkomst wordt gevonden, waardoor deze flexibeler is dan VERT.ZOEKEN. Beschikbaar in webversie van Excel.

• VERGELIJKEN: hiermee wordt de positie van een waarde binnen een bereik gevonden. Koppel deze met INDEX om gegevens op te halen uit elke kolom in een tabel, inclusief kolommen links van de zoekkolom waar VERT.ZOEKEN niet kan worden bereikt.

• INDEX: haalt een waarde op van een specifieke positie in een tabel. In combinatie met VERGELIJKEN worden zoekacties verwerkt die noch XLOOKUP noch VERT.ZOEKEN rechtstreeks kunnen oplossen, zoals het retourneren van een waarde op basis van twee overeenkomende voorwaarden in afzonderlijke kolommen.

Probeer deze Copilot-voorbeeldprompts

Schrijf een VERT.ZOEKEN-formule om de productcode in kolom A te vergelijken met de prijslijst in kolommen D en E. Klik om deze Copilot-prompt te kopiëren

Gegevensanalyse en samenvatting

SOMMEN.ALS en draaitabellen verwerken analyse op schaal in Excel. SOMMEN.ALS berekent totalen voor meerdere voorwaarden en draaitabellen groepeert en vat gegevens samen in een weergave die gemakkelijk te lezen en te delen is.

• Draaitabel: hiermee worden grote gegevenssets samengevat en verkend door rijen te groeperen, totalen te berekenen en de weergave opnieuw te rangschikken zonder de brongegevens aan te raken. Een draaitabel gebruiken om de maandelijkse verkoop in verschillende regio's in een te vergelijken business budgeting sheet, traffic analyseren op bron en apparaat, of uitgaven opsplitsen per categorie in een Op Gantt-diagram gebaseerd projectbestand. Chat met Copilot om een draaitabel in te stellen.

• SOMMEN.ALS: totalen die voldoen aan twee of meer voorwaarden tegelijk. De omzet voor een specifiek product in een specifieke regio optellen, het totale aantal uren van een rooster voor één teamlid in een bepaalde week of de wekelijkse uitgaven voor een bepaald item in een budgetplanner. Gebruik deze als één voorwaarde niet voldoende is om de rechterrijen te isoleren.

Probeer deze Copilot-voorbeeldprompts

Maak een draaitabel om de verkoop per regio en maand samen te vatten op basis van deze gegevens. Klik om deze Copilot-prompt te kopiëren

Opmerking: Voor Copilot in Excel is een Microsoft 365-abonnement met Copilot vereist. Files moet worden opgeslagen in OneDrive of SharePoint met Automatisch opslaan ingeschakeld. Copilot werkt met .xlsx-, .xlsb- en .xlsm-bestanden.

Beginnen met een kant-en-klare Excel-spreadsheetsjabloon om deze formules vandaag toe te passen op echte gegevens.

Als u wilt zien hoe Copilot in Excel ondersteuning biedt voor analyse, grafieken en het opschonen van gegevens in één werkmap, verkent u vijf manieren om een Excel-spreadsheet te maken en te analyseren met AI. Meer informatie over het systematisch plannen van uitgaven voor verschillende verticals met vijf budgetplanners voor het beheren van financiën met Excel.