Bijgewerkt door Ryan Dube in oktober 2017.
Ik heb altijd geloofd dat Microsoft Excel een van de krachtigste softwaretools is die er zijn. Het is niet alleen het feit dat het een krachtige spreadsheet-applicatie is. Microsoft Excel zelf heeft zo'n indrukwekkende verzameling ingebouwde tools en functies. In dit artikel wil ik je laten zien hoe krachtig formules en conditionele opmaak kunnen zijn, met drie handige voorbeelden.
We hebben een aantal verschillende manieren behandeld om beter gebruik te maken van Excel, zoals het gebruiken van uw eigen agendasjabloon. Een agendasjabloon maken in Excel Een agendasjabloon maken in Excel U hebt altijd een kalender nodig. We laten u zien hoe u uw eigen aangepaste sjabloon gratis kunt maken met Microsoft Excel. Lees meer, gebruik het als een hulpmiddel voor doelenbeheer 10+ Nuttige Excel-sjablonen voor projectbeheer en -tracering 10+ Nuttige Excel-sjablonen voor projectbeheer en -tracering Sjablonen zijn voor projectmanagers wat messen zijn voor professionele koks: onmisbaar. We laten u zien hoe u succesvolle projecten kunt repliceren door kant-en-klare sjablonen te gebruiken in Microsoft Excel en verder. Meer lezen en andere unieke manieren waarop u het kunt gebruiken om uw leven te beheren. Lees die artikelen maar eens en u zult zien hoe krachtig Microsoft Excel kan zijn.
Veel van de kracht ligt echt achter de formules en regels die u kunt schrijven om automatisch gegevens en informatie te manipuleren, ongeacht welke gegevens u in de spreadsheet invoegt.
Graven in Microsoft Excel
Met de juiste informatie kunt u een systeem maken dat automatisch resultaten en rapporten van die onbewerkte gegevens berekent en opnieuw berekent. Vandaag wil ik nog wat verder graven onder de oppervlakte en laten zien hoe je sommige van de onderliggende formules en andere hulpmiddelen kunt gebruiken om beter gebruik te maken van Microsoft Excel.
Coole voorwaardelijke opmaak met formules
Een van de tools waarvan ik denk dat mensen ze niet vaak genoeg gebruiken, is Conditional Formatting. Als u op zoek bent naar geavanceerdere informatie over voorwaardelijke opmaak in Microsoft Excel, moet u het artikel van Sandy over het formatteren van gegevens in Microsoft Excel met voorwaardelijke opmaak bekijken. Automatisch gegevens in Excel-spreadsheets formatteren met voorwaardelijke opmaak Gegevens automatisch opmaken in Excel-spreadsheets met voorwaardelijke opmaak Met de voorwaardelijke opmaakfunctie van Excel kunt u afzonderlijke cellen in een Excel-spreadsheet opmaken op basis van hun waarde. We laten u zien hoe u dit kunt gebruiken voor verschillende dagelijkse taken. Lees verder .
Met behulp van formules, regels of slechts enkele heel eenvoudige instellingen, kunt u een spreadsheet transformeren in een automatisch dashboard dat u in een oogopslag veel over de informatie in de spreadsheet laat zien.
Om naar Voorwaardelijke opmaak te gaan, klikt u op het tabblad Start en vervolgens op het werkbalkpictogram "Voorwaardelijke opmaak".
Onder voorwaardelijke opmaak zijn er een heleboel opties. De meeste hiervan vallen buiten het bestek van dit specifieke artikel, maar het merendeel gaat over het markeren, inkleuren of schaduwen van cellen op basis van de gegevens in die cel. Dit is waarschijnlijk het meest gebruikte gebruik van voorwaardelijke opmaak - dingen doen zoals een cel rood maken met logisch minder dan of groter dan formules. Brad Jones beschrijft hoe veel van deze artikelen in zijn artikel over het maken van een Excel-dashboard te gebruiken zijn Visualiseer uw gegevens en maak uw spreadsheets Gebruiksvriendelijk met een Excel-dashboard Visualiseer uw gegevens & maak uw spreadsheets Gebruiksvriendelijk met een Excel-dashboard Soms, een eenvoudig spreadsheetformaat is niet boeiend genoeg om uw gegevens toegankelijk te maken. Met een dashboard kunt u uw belangrijkste gegevens in een gemakkelijk te verwerken vorm presenteren. Lees meer - zeker de moeite waard om eens te kijken of dat je doel is.
Een van de minder gebruikte voorwaardelijke opmaakhulpmiddelen is de optie pictogramsets, die een geweldige reeks pictogrammen biedt die u kunt gebruiken om van een Excel-gegevenscel een pictogram voor een dashboardweergave te maken.
Ik ontdekte dit nadat ik een upgrade had uitgevoerd naar Microsoft Office 13+ Redenen die je moet upgraden naar Microsoft Office 2016 13+ redenen waarom je moet upgraden naar Microsoft Office 2016 Microsoft Office 2016 is hier en het is tijd voor jou om een beslissing te nemen. De productiviteitsvraag is - moet u upgraden? We geven u de nieuwe functies en de verserende redenen om u te helpen ... Lees meer en gebruikte voorwaardelijke opmaak in Microsoft Excel voor de eerste keer.
Ik controleerde de iconensets en zag deze coole LED-indicatielampjes die ik alleen echt had gezien in sommige van de fabrieksautomatiseringsdisplays die ik in het verleden heb geprogrammeerd. Wanneer u op "Regels beheren" klikt, gaat u naar Regelsbeheer voorwaardelijke opmaak. Afhankelijk van de gegevens die u hebt geselecteerd voordat u de pictogramset hebt gekozen, ziet u de cel die wordt aangegeven in het venster Beheer met de pictogramset die u zojuist hebt gekozen.
Wanneer u op "Regel bewerken ..." klikt, ziet u het dialoogvenster waarin de magie plaatsvindt. Hier kunt u de logische formule en vergelijkingen maken die het gewenste dashboardpictogram weergeven. In mijn voorbeeld bewaak ik de tijd besteed aan verschillende taken versus mijn gebudgetteerde tijd. Als ik meer dan de helft van mijn budget heb besteed, wil ik een geel lampje laten weergeven en als ik het budget overschrijd, wil ik dat het rood wordt.
Zoals je kunt zien, ben ik niet echt bezig met het in de begroting opnemen van mijn tijd. Bijna de helft van mijn tijd gaat veel verder dan wat ik heb gebudgetteerd.
Tijd om opnieuw te focussen en mijn tijd beter te beheren Gebruik de 80/20 Time Management Rule om uw taken te prioritiseren Gebruik de 80/20 Time Management Rule om uw taken te prioritiseren Hoe maximaliseert u uw tijd? Als je al prioriteiten stelt, delegeert en nog steeds moeite hebt om alles gedaan te krijgen, moet je de 80/20-regel proberen, ook bekend als Pareto-principe. Lees verder !
Items opzoeken met de VLookup-functie
Oké, misschien is dat niet gek genoeg voor jou. Misschien ben je niet zo enthousiast over simpele logische formules die licht aan en uit doen. Als je meer geavanceerde Microsoft Excel-functies wilt gebruiken, dan ben ik weer een andere voor je gegaan.
U bent waarschijnlijk bekend met de functie VLookup, waarmee u in een kolom door een lijst naar een bepaald item 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 dat de gegevens die u zoekt aan de rechterkant zijn, maar wat als ze worden overgeschakeld?
In het onderstaande voorbeeld, wat als ik de taak die ik heb uitgevoerd op 25-06-2013 wilt vinden aan de hand van de volgende gegevens?
In dit geval zoekt u door waarden aan de rechterkant en u wilt de overeenkomstige waarde aan de linkerkant retourneren - tegenover de manier waarop VLookup normaal werkt Zoek Excel-spreadsheets sneller: vervang VLOOKUP met INDEX en MATCH Zoek Excel-spreadsheets sneller: vervang VLOOKUP Met INDEX en MATCH Nog steeds met VERT.ZOEKEN om informatie in uw spreadsheet te zoeken? Dit is hoe INDEX en MATCH een betere oplossing kunnen bieden. Lees verder . Als je Microsoft Excel pro-user forums leest, zul je veel mensen zien zeggen dat dit niet mogelijk is met VLookup en dat je hiervoor een combinatie van Index en Match-functies moet gebruiken. Dat is niet helemaal waar.
Je kunt VLookup zo laten werken door er een functie KIEZEN in te nestelen. In dit geval zou de formule er als volgt uitzien:
"=VLOOKUP(DATE(2013, 6, 25), CHOOSE({1, 2}, E2:E8, A2:A8), 2, 0)" Wat deze functie betekent, is dat u de datum 6/25/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 klopt, maar wat je doet met de "CHOOSE" -functie is het manipuleren van de twee velden. U wijst verwijzingsindexcijfers toe aan gegevensreeksen: de datums worden toegewezen aan indexnummer 1 en de taken aan index nummer twee. Dus, als u "2" in de functie VLookup typt, verwijst u eigenlijk naar Indexnummer 2 in de functie KIEZEN. Gek, eh?
Dus de VLookup gebruikt de kolom Datum en retourneert de gegevens uit de kolom Taak, ook al is Taak aan de linkerkant. Nu je dit kleine beetje weet, stel je eens voor wat je kunt doen!
Als u probeert andere opzoektaken voor geavanceerde gegevens uit te voeren, moet u het volledige artikel van Dann over het vinden van gegevens in Excel raadplegen met behulp van opzoekfuncties Alles vinden in uw Excel-spreadsheet met opzoekfuncties Alles vinden in uw Excel-spreadsheet met opzoekfuncties In een gigant In Excel-spreadsheet, CTRL + F kom je alleen tot nu toe. Wees slim en laat formules het harde werk doen. Opzoekformules besparen tijd en zijn gemakkelijk toe te passen. Lees verder .
Insane geneste formules om snaren te ontleden
Zoals je kunt zien, probeer ik een beetje gekker te worden als we gaan, want ik weet dat er een paar van jullie zijn die denken: "... nou dat is helemaal niet gek !!" Ik weet het, je normen zijn hoog. Ik probeer ze waar te maken. Hier is nog een gekke formule voor jou.
Er kunnen zich gevallen voordoen waarin u gegevens in Microsoft Excel importeert van een externe bron die bestaat uit een reeks van gescheiden gegevens. Zodra u de gegevens invoert, wilt u die gegevens in de afzonderlijke componenten analyseren. Hier is een voorbeeld van naam, adres en telefoonnummerinformatie begrensd door het teken ";".
Zo kun je deze informatie ontleden met een Excel-formule (kijk of je mentaal deze waanzin kunt volgen):
Voor het eerste veld, om het meest linkse item (de naam van de persoon) te extraheren, zou u eenvoudig een LEFT-functie in de formule gebruiken.
"=LEFT(A2, FIND(";", A2, 1)-1)" Hiermee doorzoekt u de tekenreeks uit A2, zoekt u het scheidingsteken ";", trekt u er één af voor de juiste locatie van het einde van dat tekenreeksgedeelte en pakt u vervolgens de meest linkse tekst naar dat punt. In dit geval is dat "Ryan". Missie volbracht.
Excel-formules nesten
Maar hoe zit het met de andere secties? Welnu, om de gedeelten aan de rechterkant te extraheren, moet je meerdere RECHTER functies nesten om het tekstgedeelte tot dat eerste ";" symbool omhoog te halen en de LINKER-functie opnieuw uit te voeren. Zo ziet dit 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 bij elkaar te passen. Het enige dat ik deed, is deze functie:
"RIGHT(A2, LEN(A2)-FIND(";", A2))" En plaatste het in elke plaats in de LEFT-functie hierboven waar een "A2" staat. Dit extraheert het tweede deel van de string correct.
Voor elk volgend deel van de reeks moet nog een nest worden gemaakt. Dus nu neem je gewoon de gekke "RIGHT" -vergelijking die je voor de laatste sectie hebt gemaakt, en geef je die vervolgens door aan een nieuwe RECHTSE formule met de vorige formule RIGHT in zichzelf geplakt, waar je ook "A2" ziet. Dit 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 neem je DIE formule, en plaats het in de originele LINKER-formule waar er een "A2" is. De laatste mind-bending formule ziet er zo 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 extraheert "Portland, ME 04076" correct uit de originele reeks.
Om het volgende gedeelte uit te pakken, herhaalt u het bovenstaande proces helemaal opnieuw. Je formules kunnen heel gestoord zijn, maar het enige wat je doet, is lange formules knippen en plakken, lange nesten maken die heel goed 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 de kolom met de gescheiden gegevens en selecteer vervolgens Tekst in kolommen onder het menu-item Gegevens . Dit zal een venster openen waarin je de string kunt splitsen op elk gewenst delimiter.
Met een paar klikken kun je hetzelfde doen als die formule hierboven ... maar wat is daar het leuks aan?
Gek worden met Microsoft Excel
Dus daar heb je het. De bovenstaande formules bewijzen hoe overdreven een persoon kan zijn bij het maken van Microsoft Excel-formules om bepaalde taken te volbrengen. Soms zijn die formules niet echt de gemakkelijkste (of beste) manier om dingen te bereiken. De meeste programmeurs zullen u vertellen om het eenvoudig te houden, en dat geldt net zo goed voor Excel-formules 16 Excel-formules die u zullen helpen om problemen met het echte leven op te lossen 16 Excel-formules die u kunnen helpen om problemen met het echte leven op te lossen De juiste tool is het halve werk. Excel kan berekeningen verwerken en gegevens sneller verwerken dan u uw rekenmachine kunt vinden. We laten u de belangrijkste Excel-formules zien en laten zien hoe u ze kunt gebruiken. Lees meer als iets anders.
Waren deze formules en technieken wild genoeg? Heeft u geweldige formuletrucs van uw eigen Microsoft Excel super-user toolbox? Deel uw input en feedback in de comments hieronder!
Afbeelding Krediet: kues / Depositphotos