Home-theater-designers
Microsoft Excel-formules kunnen bijna alles. In dit artikel leert u hoe krachtig Microsoft Excel-formules en voorwaardelijke opmaak kunnen zijn, aan de hand van drie handige voorbeelden.
Kennismaken met Microsoft Excel
We hebben een aantal verschillende manieren besproken om Excel beter te gebruiken, zoals: waar vind je geweldige downloadbare Excel-sjablonen , en hoe Excel te gebruiken als hulpmiddel voor projectbeheer .
Een groot deel van de Excel-kracht ligt achter de Excel-formules en -regels waarmee u gegevens en informatie automatisch kunt 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 tools die mensen niet vaak genoeg gebruiken, is voorwaardelijke opmaak. Met behulp van Excel-formules, regels of slechts een paar heel eenvoudige instellingen, kunt u een spreadsheet transformeren in een geautomatiseerd dashboard.
Om naar Voorwaardelijke opmaak te gaan, klikt u gewoon op de Huis tabblad en klik op de Conditionele opmaak werkbalk icoon.
Onder Voorwaardelijke opmaak zijn er veel opties. De meeste hiervan vallen buiten het bestek van dit specifieke artikel, maar de meeste gaan over het markeren, kleuren of arceren van cellen op basis van de gegevens in die cel.
Dit is waarschijnlijk het meest voorkomende gebruik van voorwaardelijke opmaak, zoals het rood maken van een cel met behulp van minder-dan- of groter-dan-formules. Meer informatie over het gebruik van IF-instructies in Excel.
Een van de minder gebruikte hulpmiddelen voor voorwaardelijke opmaak is de Pictogrammensets optie, die een geweldige set pictogrammen biedt die u kunt gebruiken om een Excel-gegevenscel om te zetten in een dashboardweergavepictogram.
Wanneer u klikt op Regels beheren , het brengt je naar de Beheer van voorwaardelijke opmaakregels .
stop code systeem service uitzondering windows 10
Afhankelijk van de gegevens die u hebt geselecteerd voordat u de pictogrammenset koos, ziet u de cel die wordt aangegeven in het Manager-venster met de pictogrammenset die u zojuist hebt gekozen.
Wanneer u klikt op Regel bewerken , ziet u het dialoogvenster waarin de magie plaatsvindt.
Hier kunt u de logische formule en vergelijkingen maken die het gewenste dashboardpictogram weergeven.
Dit voorbeelddashboard toont de tijd besteed aan verschillende taken versus de gebudgetteerde tijd. Als u de helft van het budget overschrijdt, wordt er een geel lampje weergegeven. Als je je budget volledig overschrijdt, wordt het rood.
Zoals u kunt zien, laat dit dashboard zien dat tijdbudgettering niet succesvol is.
Bijna de helft van de tijd wordt ruim boven de begrote bedragen besteed.
Tijd om je te heroriënteren en je tijd beter te beheren!
1. De VLookup-functie gebruiken
Als u meer geavanceerde Microsoft Excel-functies wilt gebruiken, zijn hier een paar die u kunt proberen.
U bent waarschijnlijk bekend met de functie VLookup, waarmee u door een lijst naar een bepaald item in één 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 dat u zoekt in de lijst zich in de linkerkolom bevindt en de gegevens die u zoekt aan de rechterkant, maar wat als ze worden verwisseld?
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 corresponderende waarde aan de linkerkant retourneren.
Als u Microsoft Excel-pro-gebruikersforums leest, zult u merken dat veel mensen zeggen dat dit niet mogelijk is met VLookup. U moet hiervoor een combinatie van Index- en Match-functies gebruiken. Dat is niet helemaal waar.
U kunt VLookup op deze manier laten werken door er een CHOOSE-functie in te nesten. 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)'Deze functie houdt in dat u de datum 25-6-2013 in de opzoeklijst wilt vinden 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 je doet met de ' KIEZEN ' functie manipuleert de twee velden.
U wijst referentie-indexnummers toe aan gegevensbereiken - wijst de datums toe aan indexnummer 1 en de taken aan indexnummer 2.
Dus als je '2' typt in de VLookup-functie, verwijs je eigenlijk naar Index nummer 2 in de CHOOSE-functie. Cool toch?
De VLookup gebruikt nu de Datum kolom en retourneert gegevens van de Taak kolom, ook al staat Taak aan de linkerkant.
hoe Apple TV uit te schakelen
Nu je dit kleine lekkernij kent, stel je eens voor wat je nog meer kunt doen!
Als je andere taken voor het opzoeken van geavanceerde gegevens probeert uit te voeren, lees dan dit artikel over gegevens zoeken in Excel met behulp van opzoekfuncties .
2. Geneste formule om strings te ontleden
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 ontleden in de afzonderlijke componenten. Hier is een voorbeeld van naam, adres en telefoonnummer, gescheiden door de ';' karakter.
Hier leest u hoe u deze informatie kunt ontleden met behulp van een Excel-formule (kijk of u deze waanzin mentaal kunt volgen):
Voor het eerste veld, om het meest linkse item (de naam van de persoon) te extraheren, gebruikt u eenvoudig een LEFT-functie in de formule.
'=LEFT(A2,FIND(';',A2,1)-1)'Zo werkt deze logica:
- Zoekt de tekstreeks van A2
- Vindt de ';' scheidingsteken symbool
- Trekt er één af voor de juiste locatie van het einde van die stringsectie
- Pakt de meest linkse tekst op 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 eenvoudigere manieren om dit te doen, maar omdat we willen proberen de gekste geneste Excel-formule te maken die mogelijk is (die echt werkt), gaan we een unieke aanpak gebruiken.
Om de delen aan de rechterkant te extraheren, moet je meerdere RECHTS-functies nesten om het tekstgedeelte te pakken tot die eerste ';' symbool en voer de functie LINKS opnieuw uit. Hier is hoe dat eruit ziet voor het extraheren van het huisnummergedeelte 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 in elkaar te zetten. Het enige wat ik deed is deze functie aannemen:
RIGHT(A2,LEN(A2)-FIND(';',A2))En plaatste het op elke plaats in de LINKS functie hierboven waar een 'A2' staat.
Hiermee wordt het tweede deel van de tekenreeks correct geëxtraheerd.
hoe de xbox one-controller uit elkaar te halen
Voor elke volgende sectie van de tekenreeks moet een ander nest worden gemaakt. Nu hoef je alleen nog maar de ' RECHTSAF ' vergelijking die u in de laatste sectie hebt gemaakt, en plak deze in een nieuwe RECHTS-formule met de vorige RECHTS-formule erin geplakt waar u 'A2' ziet. Hier is hoe dat eruit ziet.
(RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2))))))Dan moet je DIE formule nemen en deze in de originele LINKER formule plaatsen waar er een 'A2' is.
De uiteindelijke geestverruimende 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 originele string.
Om de volgende sectie te extraheren, herhaalt u het bovenstaande proces helemaal opnieuw.
Je Excel-formules kunnen erg wispelturig worden, maar het enige wat je doet is lange formules in zichzelf knippen en plakken, waardoor je lange nesten maakt die nog steeds werken.
Ja, dit voldoet aan de eis voor '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 gegevens met scheidingstekens, en dan onder de Gegevens menu-item, selecteer Tekst naar kolommen .
Er verschijnt een venster waarin u de tekenreeks kunt splitsen met elk gewenst scheidingsteken. Gewoon invoeren ' ; ' en u zult zien dat het voorbeeld van uw geselecteerde gegevens dienovereenkomstig verandert.
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 iemand kan zijn bij het maken van Microsoft Excel-formules om bepaalde taken uit te voeren.
Soms zijn die Excel-formules niet echt de gemakkelijkste (of beste) manier om dingen te bereiken. De meeste programmeurs zullen je vertellen om het simpel te houden, en dat geldt net zo goed voor Excel-formules als voor al het andere.
Als je echt serieus aan de slag wilt met het gebruik van Excel, lees dan onze beginnershandleiding voor het gebruik van Microsoft Excel. Het heeft alles wat je nodig hebt om je productiviteit te verhogen met Excel. Raadpleeg daarna onze spiekbrief met essentiële Excel-functies voor meer informatie.
Afbeelding tegoed: kues/stortingsfoto's
Deel Deel Tweeten E-mail De top 7 financiële functies in ExcelOf u nu een accountant of een financiële professional bent, u moet deze Excel-formules kennen.
Lees volgende Gerelateerde onderwerpen- productiviteit
- Spreadsheettips
- Microsoft Excel
- Microsoft Office 2016
- Microsoft Office-tips
- Microsoft Office 2019
Ryan heeft een BSc-graad in Elektrotechniek. Hij heeft 13 jaar in de automatiseringstechniek gewerkt, 5 jaar in de IT en is nu een Apps Engineer. Als voormalig hoofdredacteur van MakeUseOf heeft hij gesproken op nationale conferenties over datavisualisatie en is hij te zien geweest op nationale tv en radio.
Meer van Ryan DubeAbonneer op onze nieuwsbrief
Word lid van onze nieuwsbrief voor technische tips, recensies, gratis e-boeken en exclusieve deals!
Klik hier om je te abonneren