3 gekke Excel-formules die geweldige dingen doen

• produktiviteit

Advertentie

Excel-formules kunnen bijna alles. In dit artikel leert u hoe krachtig Microsoft Excel-formules en voorwaardelijke opmaak kunnen zijn, met drie nuttige voorbeelden.

Ontgrendel nu het cheatsheet "Essentiële Excel-formules"!

Hiermee meld je je aan voor onze nieuwsbrief

Opgraven in Microsoft Excel

We hebben een aantal verschillende manieren besproken om Excel beter te gebruiken, zoals het gebruiken om uw eigen kalendersjabloon te maken of het gebruiken als een hulpmiddel voor projectbeheer.

Veel van de kracht zit in de Excel-formules en -regels die u kunt schrijven om gegevens en informatie automatisch te manipuleren, ongeacht welke gegevens u in de spreadsheet invoegt.

Laten we eens kijken hoe u formules en andere hulpmiddelen kunt gebruiken om Microsoft Excel beter te gebruiken.

Voorwaardelijke opmaak met Excel-formules

Een van de hulpmiddelen die mensen niet vaak genoeg gebruiken, is voorwaardelijke opmaak. Als u op zoek bent naar meer geavanceerde informatie over voorwaardelijke opmaak in Microsoft Excel, bekijk dan Sandy's artikel over het opmaken van gegevens in Microsoft Excel met voorwaardelijke opmaak.

Met het gebruik van Excel-formules, regels of een paar echt eenvoudige instellingen kunt u een spreadsheet omzetten in een geautomatiseerd dashboard.

Om naar voorwaardelijke opmaak te gaan, klikt u op het tabblad Start en vervolgens op het werkbalkpictogram Voorwaardelijke opmaak .

Onder Voorwaardelijke opmaak zijn er veel opties. De meeste hiervan vallen buiten het bestek van dit specifieke artikel, maar de meerderheid gaat over het markeren, kleuren of in de schaduw stellen van cellen op basis van de gegevens in die cel.

Dit is waarschijnlijk het meest voorkomende gebruik van voorwaardelijke opmaak - dingen zoals een cel rood kleuren met formules die kleiner dan of groter zijn dan. Meer informatie over het gebruik van IF-instructies in Excel.

Een van de minder gebruikte voorwaardelijke opmaakhulpmiddelen is de optie Pictogrammensets, die een grote reeks pictogrammen biedt die u kunt gebruiken om van een Excel-gegevenscel een dashboardweergavepictogram te maken.

Wanneer u op Regels beheren klikt, gaat u naar Regels voor voorwaardelijke opmaakregels .

Afhankelijk van de gegevens die u hebt geselecteerd voordat u de icon set koos, ziet u de cel in het venster Manager, met de icon set die u zojuist hebt gekozen.

Wanneer u op Regel bewerken klikt, ziet u het dialoogvenster waarin de magie gebeurt.

Hier kunt u de logische formule en vergelijkingen maken die het gewenste dashboardpictogram weergeven.

Dit voorbeelddashboard toont de tijd besteed aan verschillende taken versus gebudgetteerde tijd. Als u meer dan de helft van het budget overschrijdt, wordt een geel lampje weergegeven. Als je budget te hoog is, wordt het rood.

Zoals u kunt zien, toont dit dashboard dat tijdbudgettering niet succesvol is.

Bijna de helft van de tijd wordt veel besteed boven de begrote bedragen.

Tijd om u opnieuw te concentreren en uw tijd beter te beheren!

1. Gebruik van de VLookup-functie

Als u meer geavanceerde Microsoft Excel-functies wilt gebruiken, is hier een andere voor u.

U bent waarschijnlijk bekend met de functie VLookup, waarmee u in een lijst naar een bepaald item in een kolom kunt zoeken en de gegevens uit een andere kolom in dezelfde rij als dat item kunt retourneren.

Helaas vereist de functie dat het item waarnaar u in de lijst zoekt in de linkerkolom staat en de gegevens die u zoekt aan de rechterkant staan, maar wat als ze zijn omgeschakeld?

Wat als ik in het onderstaande voorbeeld de taak wil vinden die ik op 25-6-2018 heb uitgevoerd uit de volgende gegevens?

In dit geval zoekt u door waarden aan de rechterkant en wilt u de overeenkomstige waarde aan de linkerkant retourneren - in tegenstelling tot de manier waarop VLookup normaal werkt.

Als je Microsoft Excel pro-user forums leest, zul je veel mensen vinden die zeggen dat dit niet mogelijk is met VLookup, en dat je een combinatie van Index- en Match-functies moet gebruiken om dit te doen. Dat is niet helemaal waar.

Je kunt VLookup op deze manier laten werken door er een CHOOSE-functie in te nestelen. In dit geval ziet de Excel-formule er als volgt uit:

 "=VLOOKUP(DATE(2018, 6, 25), CHOOSE({1, 2}, E2:E8, A2:A8), 2, 0)" 

Wat deze functie betekent, is dat u de datum 25-6-2013 in de opzoeklijst wilt zoeken en vervolgens de bijbehorende waarde uit de kolomindex wilt retourneren.

In dit geval zult u merken dat de kolomindex "2" is, maar zoals u kunt zien, is de kolom in de bovenstaande tabel eigenlijk 1, toch?

Dat is waar, maar wat u doet met de functie "KIEZEN" is het manipuleren van de twee velden.

U wijst referentie "indexnummers" toe aan gegevensbereiken - de datums toewijzen aan indexnummer 1 en de taken aan indexnummer 2.

Dus als u '2' typt in de functie VLookup, verwijst u eigenlijk naar indexnummer 2 in de functie KIEZEN. Cool toch?

Dus nu gebruikt de VLookup de kolom Datum en retourneert de gegevens uit de kolom Taak, ook al bevindt Taak zich links.

Nu je dit kleine stukje weet, stel je eens voor wat je nog meer kunt doen!

Als u andere geavanceerde opzoektaken probeert uit te voeren, bekijk dan het volledige artikel van Dann over het vinden van gegevens in Excel met behulp van opzoekfuncties.

2. Geneste formule om tekenreeksen te parseren

Hier is nog een gekke Excel-formule voor jou.

Er kunnen gevallen zijn waarin u gegevens in Microsoft Excel importeert vanuit een externe bron die bestaat uit een reeks gescheiden gegevens.

Nadat u de gegevens hebt ingevoerd, wilt u die gegevens in de afzonderlijke componenten onderverdelen. Hier is een voorbeeld van naam-, adres- en telefoonnummerinformatie, gescheiden door het teken ';'.

Hier is hoe je deze informatie kunt ontleden met behulp van een Excel-formule (kijk of je mentaal kunt volgen met deze waanzin):

Voor het eerste veld, om het meest linkse item (de naam van de persoon) te extraheren, gebruikt u gewoon de functie LINKS in de formule.

 "=LEFT(A2, FIND(";", A2, 1)-1)" 

Hier is hoe deze logica werkt:

• Zoekt de tekstreeks van A2
• Zoekt het scheidingsteken ";"
• Trekt er een af ​​voor de juiste locatie van het einde van die stringsectie
• Grijpt de meest linkse tekst tot dat punt

In dit geval is de meest linkse tekst "Ryan". Missie volbracht.

3. Geneste formule in Excel

Maar hoe zit het met de andere secties?

Er zijn misschien eenvoudiger manieren om dit te doen, maar omdat we willen proberen de gekste Nested Excel-formule mogelijk te maken (die echt werkt), gaan we een unieke aanpak gebruiken.

Om de delen aan de rechterkant te extraheren, moet u meerdere RECHTS-functies nesten om het gedeelte van de tekst tot dat eerste ";" -symbool te pakken en de LEFT-functie erop opnieuw uit te voeren. Zo ziet het eruit voor het extraheren van het straatnummergedeelte van het adres.

 "=LEFT((RIGHT(A2, LEN(A2)-FIND(";", A2))), FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))), 1)-1)" 

Het ziet er gek uit, maar het is niet moeilijk om het samen te stellen. Alles wat ik deed was deze functie aannemen:

 RIGHT(A2, LEN(A2)-FIND(";", A2)) 

En plaatste het op elke plaats in de LINKER functie hierboven waar een "A2" is.

Dit extraheert correct het tweede gedeelte van de string.

Voor elke volgende sectie van de string moet een nieuw nest worden gemaakt. Dus nu neemt u gewoon de gekke “JUISTE” vergelijking die u voor de laatste sectie had gemaakt, en geeft u die vervolgens door in een nieuwe JUISTE formule met de vorige JUISTE formule in zichzelf geplakt waar u “A2” ziet. Zo ziet dat eruit.

 (RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))) 

Vervolgens neemt u DIE formule en plaatst u deze in de originele LINKER formule waar een "A2" staat.

De uiteindelijke verbijsterende formule ziet er als volgt uit:

 "=LEFT((RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))), FIND(";", (RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))), 1)-1)" 

Die formule haalt 'Portland, ME 04076' correct uit de oorspronkelijke string.

Herhaal het bovenstaande proces opnieuw om het volgende gedeelte te extraheren.

Uw Excel-formules kunnen erg rond worden, maar het enige dat u doet, is lange formules in zichzelf knippen en plakken, lange nesten maken die echt werken.

Ja, dit voldoet aan de eis van "gek". Maar laten we eerlijk zijn, er is een veel eenvoudigere manier om hetzelfde te bereiken met één functie.

Selecteer gewoon de kolom met de gescheiden gegevens en selecteer vervolgens onder het menu-item Gegevens naar kolommen .

Dit opent een venster waar u de tekenreeks kunt splitsen door een willekeurig scheidingsteken dat u wilt.

Met een paar klikken kun je hetzelfde doen als die gekke formule hierboven ... maar waar is het plezier daarin?

Gek worden met Microsoft Excel-formules

Dus daar heb je het. De bovenstaande formules bewijzen hoe overdreven een persoon kan worden bij het maken van Microsoft Excel-formules om bepaalde taken uit te voeren.

Soms zijn die Excel-formules niet de gemakkelijkste (of beste) manier om dingen te bereiken. De meeste programmeurs zullen u vertellen om het simpel te houden, en dat is net zo waar met Excel-formules als met al het andere. U kunt zelfs ingebouwde functies zoals Power Query gebruiken.

Als je echt serieus wilt worden met het gebruik van Excel, lees dan onze beginnershandleiding voor het gebruik van Microsoft Excel. De beginnershandleiding voor Microsoft Excel. De beginnershandleiding voor Microsoft Excel Gebruik deze beginnershandleiding om je ervaring met Microsoft Excel te beginnen. De basistips voor spreadsheets hier helpen u om zelf Excel te leren. Lees verder . Het heeft alles wat u nodig hebt om uw productiviteit te verhogen met Excel. Raadpleeg daarna onze essentiële cheatsheet voor Excel-functies De essentiële cheatsheet voor Microsoft Excel-formules en -functies De essentiële cheatsheet voor Microsoft Excel-formules en -functies Download deze cheatsheet voor Excel-formules om een ​​snelkoppeling te maken naar 's werelds favoriete spreadsheetprogramma. Lees verder .

Ontdek meer over: Microsoft Excel, Microsoft Office 2016, Spreadsheet.