3 Crazy Excel-formules die geweldige dingen doen

Microsoft Excel is een van de krachtigste spreadsheethulpmiddelen, met een indrukwekkende verzameling ingebouwde hulpmiddelen en functies. In dit artikel leert u hoe krachtige Excel-formules en voorwaardelijke opmaak kunnen zijn, met drie handige voorbeelden.

Graven in Microsoft Excel

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

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

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

Voorwaardelijke opmaak met formules

Een van de hulpmiddelen die mensen niet vaak genoeg gebruiken, is Conditionele opmaak. Als u op zoek bent naar meer geavanceerde informatie over voorwaardelijke opmaak in Microsoft Excel, moet u het artikel van Sandy over het formatteren van gegevens in Microsoft Excel met voorwaardelijke opmaak raadplegen.

Met het gebruik van Excel-formules, regels of slechts enkele heel eenvoudige instellingen, kunt u een spreadsheet transformeren in een geautomatiseerd dashboard.

Om naar Voorwaardelijke opmaak te gaan, klikt u gewoon op Huis tab en klik op de Conditionele opmaak werkbalkpictogram.

Onder Voorwaardelijke opmaak zijn er veel 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, zoals het een rode cel maken met minder dan of groter dan formules. Meer informatie over het gebruik van IF-instructies in Excel.

Een van de minder gebruikte voorwaardelijke opmaakhulpmiddelen is de Icon-sets optie, die een geweldige reeks pictogrammen biedt die u kunt gebruiken om van een Excel-datacel een pictogram voor een dashboardweergave te maken.

Wanneer u klikt op Regels beheren, het brengt je naar de Regeling voorwaardelijke opmaakregels.

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 pictogrammenset die u zojuist hebt gekozen.

Wanneer u klikt op Regel bewerken, je zult het dialoogvenster zien waar de magie gebeurt.

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

Dit voorbeelddashboard geeft de tijd weer die is besteed aan verschillende taken versus de gebudgetteerde tijd. Als u meer dan de helft van het budget overschrijdt, wordt een geel lampje weergegeven. Als je helemaal over budget heen bent, wordt het rood.

Zoals je ziet, laat dit dashboard zien dat tijdbudgettering niet succesvol is.

Bijna de helft van de tijd wordt veel meer besteed dan de gebudgetteerde bedragen.

Tijd om te herfocussen en uw tijd beter te beheren!

1. Gebruik van de VLookup-functie

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

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 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-2018 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.

Als u Microsoft Excel pro-user forums leest, zult u merken dat dit niet mogelijk is met VLookup, en dat u 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 ziet de Excel-formule er als volgt uit:

"= VERT.ZOEKEN (DATE (2018,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 is “2”, maar zoals je kunt zien is de kolom in de bovenstaande tabel eigenlijk 1, juist?

Dat is waar, maar wat je doet met de “KIEZEN” functie manipuleert de twee velden.

U wijst referentie toe “inhoudsopgave” getallen voor gegevensreeksen: de datums worden toegewezen aan indexnummer 1 en de taken aan indexnummer 2.

Dus, als je typt “2” in de functie VLookup, verwijst u eigenlijk naar Index nummer 2 in de functie KIEZEN. Cool toch?

Dus nu gebruikt de VLookup de Datum kolom en geeft de gegevens van de Taak kolom, hoewel Taak aan de linkerkant staat.

Nu je dit kleine beetje weet, kun je je voorstellen wat je nog meer 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.

2. Geneste formule om snaren te parseren

Hier is nog een gekke Excel-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 de “;” karakter.

Hier leest u hoe u deze informatie kunt ontleden met een Excel-formule (zie of u 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)"

Dit werkt als volgt:

• Doorzoekt de tekstreeks vanaf A2
• Vindt de “;” afbakeningssymbool
• Trekt er een af ​​voor de juiste locatie van het einde van dat stringgedeelte
• Grijpt de meest linkse tekst naar 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 kunnen eenvoudiger manieren zijn om dit te doen, maar omdat we proberen de gekste Nested Excel-formule mogelijk te maken (die echt werkt), gaan we een unieke aanpak gebruiken.

Om de delen aan de rechterkant uit te pakken, moet je meerdere RECHTER functies nesten om het gedeelte van de tekst tot die eerste op te pakken “;” symbool en voer de LINKER-functie opnieuw uit. 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 wat ik deed, is deze functie:

RIGHT (A2, LEN (A2) -Find ( "", A2))

En plaatste het in elke plaats in de LINKERFUNCTIE hierboven waar er een is “A2”.

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 gek “RECHTS” vergelijking die u voor de laatste sectie had gemaakt en geef die vervolgens door aan een nieuwe RECHTSE formule met de vorige formule RIGHT in zichzelf geplakt, waar u ook bent “A2”. Dit is hoe dat eruit ziet.

(RIGHT ((RIGHT (A2, LEN (A2) -Find ( "", A2))) LENGTE ((RIGHT (A2, LEN (A2) -Find ( "", A2)))) - FIND ( "", (RIGHT (A2, LEN (A2) -Find ( "", A2))))))

Dan neem je DIE formule, en plaats hem in de originele LEFT-formule, waar er ook een is “A2”.

De laatste mind-bending formule ziet er zo uit:

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

Die formule haalt correct uit “Portland, ME 04076” uit de originele string.

Om het volgende gedeelte uit te pakken, herhaalt u het bovenstaande proces helemaal opnieuw.

Je Excel-formules kunnen heel gestoord zijn, maar het enige wat je doet, is lange formules knippen en plakken, lange nesten maken die echt werken.

Ja, dit voldoet aan de vereiste 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 gescheiden gegevens en vervolgens onder de Gegevens menu-item, selecteer Tekst naar kolommen.

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 gekke formule hierboven ... maar waar is het plezier in?

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 uit te voeren.

Soms zijn die Excel-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 als al het andere.

Als je echt serieus wilt worden met het gebruik van Excel, lees dan eerst 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 starten. De eenvoudige spreadsheet-tips helpen u om in uw eentje Excel te leren. Lees verder . Het heeft alles wat je nodig hebt om je productiviteit te verhogen met Excel.

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