Excel Advanced Formulas (inhoudsopgave)
- Inleiding tot geavanceerde formules in Excel
- Voorbeelden van geavanceerde formule in Excel
Inleiding tot geavanceerde formules in Excel
Als je eenmaal het middelste niveau in Excel hebt bereikt, moet je hard werken om door te gaan naar het geavanceerde niveau. Om naar het geavanceerde niveau te gaan, moet u op de hoogte zijn van enkele van de vaak gebruikte geavanceerde formules. In dit artikel zal ik de top 10 geavanceerde formules behandelen die alle excellerende studenten moeten kennen. Volg dit artikel om te leren en te verkennen.
Voorbeelden van geavanceerde formule in Excel
Laten we begrijpen hoe we de geavanceerde formules in Excel kunnen gebruiken met enkele voorbeelden.
U kunt deze geavanceerde formules Excel-sjabloon hier downloaden - geavanceerde formules Excel-sjabloonVoorbeeld # 1 - Gedeeltelijke VLOOKUP-functie
U moet een situatie zijn tegengekomen waarin VERT.ZOEKEN een fout oplevert, zelfs als de zoekwaarde iets was. Als u bijvoorbeeld zoekt naar het salaris van de naam Abhishek Sinha en als u alleen Abhishek hebt, kan VLOOKUP u de gegevens niet ophalen.
Met een sterretje aan beide uiteinden van de opzoekwaarde kunnen we de gegevens echter ophalen voor een gedeeltelijke opzoekwaarde.
In tabel 1 heb ik de volledige naam en het salaris, maar in tabel 2 heb ik slechts gedeeltelijke namen en moet ik het salaris van elke werknemer vinden.
Stap 1: Open de VLOOKUP-formule in cel E3. Voordat u de opzoekwaarde selecteert, plaatst u een asterisk (*) aan weerszijden van de opzoekwaarde.
Stap 2: Zoals gewoonlijk kunt u de VERT.ZOEKEN-formule nu voltooien en we zullen de resultaten hebben.
Na het gebruik van de bovenstaande formule wordt de uitvoer hieronder weergegeven.
Dezelfde formule wordt in andere cellen gebruikt.
Voorbeeld # 2 - AANTAL AANTALEN met operatorsymbolen
U moet de functie AANTAL.ALS & IFS hebben gebruikt om de dingen in de lijst te tellen. We kunnen ook tellen op basis van operatorsymbolen zoals groter dan (>) kleiner dan (<) en gelijkteken (=).
Bekijk nu bijvoorbeeld de onderstaande gegevens. Als u het totale aantal facturen voor de regio ZUID na de datum 10 januari 2018 wilt tellen, hoe telt u dan?
Voorbeeld # 3 - ALS toestand met EN & OF voorwaarden
Logische functies maken deel uit van onze dagelijkse activiteiten. Je moet ze beheersen om door te gaan naar het volgende niveau. Als u de bonus berekent op basis van meerdere voorwaarden, moet u EN- of OF-voorwaarde met ALS-voorwaarde nesten om de klus te klaren.
Stel dat u het bonusbedrag voor elke afdeling moet berekenen op basis van de afdeling en het aantal dienstjaren waarvoor u deze functies nodig hebt. Op basis van de onderstaande criteria moeten we de bonus berekenen.
Als de service langer is dan 4 jaar en de afdeling ofwel de verkoop- of ondersteuningsbonus is 50000 of anders is de bonus 25000.
Pas de onderstaande formule toe om het bonusbedrag te krijgen.
Na het gebruik van bovenstaande formule wordt de onderstaande output getoond.
Dezelfde formule van toepassing in cel E3 tot E9.
Voorbeeld # 4 - TEKST Functie om u koers dynamisch te maken
Stel dat u een dagelijkse verkooptabel bijhoudt en dat u de tabel elke dag moet bijwerken. Aan het begin van de tabel staat één kop met de tekst "Geconsolideerde verkoopgegevens van DD-MM-JJJJ tot DD-MM-JJJJ". Wanneer en wanneer de tabel wordt bijgewerkt, moet u de kopdatum wijzigen. Is het niet een frustrerende taak om steeds opnieuw hetzelfde te doen? We kunnen deze kop dynamisch maken door de TETX-, MIN- en Max-functie te gebruiken in combinatie met het operator-symbool ampersand (&).
Voorbeeld # 5 - INDEX + MATCH + MAX om de hoogste verkoper te vinden
U hebt een lijst met verkopers en de verkopen die zij hebben gedaan op basis van hun naam. Hoe weet u wie de beste of hoogste verkoper in de lijst is? Natuurlijk hebben we verschillende andere technieken om het resultaat te vertellen, maar deze functie kan de hoogste verkoper uit de partij teruggeven.
Solliciteer onderstaande functie om de hoogste verkoper naam te krijgen.
Na het gebruik van bovenstaande formule wordt de onderstaande output getoond.
Voorbeeld # 6 - Haal een aantal unieke waarden uit de lijst
Als u veel dubbele waarden heeft en u moet vertellen hoeveel unieke waarden er zijn in hoe vertelt u dat? Je kunt het zien door de dubbele waarde te verwijderen, maar dit is niet de dynamische manier om het unieke aantal waarden te vertellen.
Met behulp van deze matrixfunctie kunnen we de unieke waarden van de partij onderscheiden.
Pas de onderstaande formule toe om een lijst met unieke waarden te krijgen.
Voorbeeld # 7 - Gebruik benoemd bereik om de vervolgkeuzelijst dynamisch te maken
Als u vaak met een vervolgkeuzelijst werkt en de vervolgkeuzelijst up-to-date maakt, moet u teruggaan naar het bronlijstbereik om waarden te verwijderen of toe te voegen. Daarna moet u teruggaan naar de vervolgkeuzelijsten en het bereik van de vervolgkeuzelijst opnieuw bijwerken.
Als u echter een benoemd bereik voor uw vervolgkeuzelijst kunt maken, kunt u de vervolgkeuzelijst dynamisch en actueel maken.
Maak een naambereik zoals weergegeven in de onderstaande afbeelding.
Ga nu naar de vervolgkeuzelijst en open het vervolgkeuzemenu.
Druk in bron op F3-toets om alle gedefinieerde namen weer te geven, selecteer de naam van uw vervolgkeuzelijst.
Ok, er is een vervolgkeuzelijst gereed en deze zal de waarden automatisch bijwerken wanneer er een wijziging in het vervolgkeuzelijstbereik is.
Voorbeeld # 8 - Verwijder foutwaarden met de IFERROR-functie
Ik weet zeker dat je foutwaarden hebt ondervonden tijdens het werken met VLOOKUP, Divisieberekeningen. Het omgaan met deze foutwaarden is een moeizame taak. Maar we kunnen van deze foutwaarden afkomen door de IFERROR-functie in de formule te gebruiken.
Na het gebruik van bovenstaande formule wordt de onderstaande output getoond.
Dezelfde formule die in andere cellen wordt gebruikt.
Voorbeeld # 9 - Gebruik de PMT-functie om uw eigen EMI-diagram te maken
Tegenwoordig is de EMI-optie niet vreemd voor ons allemaal. In Excel kunnen we onze eigen EMI-grafiek schatten met behulp van de PMT-functie. Volg de onderstaande stappen om uw eigen grafiek te maken.
Pas de onderstaande formule toe in cel B4 om het EMI-bedrag te krijgen.
Voorbeeld # 10 - INDEX + MATCH als alternatief voor de functie VERT.ZOEKEN
Ik hoop dat u zich bewust bent van de beperking van de functie VERT.ZOEKEN. Een belangrijke beperking is dat VERT.ZOEKEN de gegevens van links naar rechts kan halen, niet van rechts naar links. Gegevens zijn niet altijd goed georganiseerd en klaar voor gebruik. Vaak bevindt de waardekolom die we zoeken zich aan de linkerkant van de opzoekwaarde, zodat VLOOKUP in deze gevallen niet helpt.
Een combinatie van INDEX + MATCH-functie dient als alternatief voor de VLOOKUP-functie.
Na het gebruik van de bovenstaande formule wordt de uitvoer hieronder weergegeven.
Dezelfde formule wordt in andere cellen gebruikt.
Op basis van de product-ID moeten we verkoopwaarden extraheren. In de hoofdtabel staat Product-ID aan de rechterkant van de verkoopkolom. Dus VLOOKUP kan de gegevens niet ophalen. Gebruik de onderstaande formule om de gegevens op te halen.
Aanbevolen artikelen
Dit is een handleiding voor geavanceerde formules in Excel. Hier bespreken we hoe u geavanceerde formules in Excel kunt gebruiken, samen met praktische voorbeelden en een downloadbare Excel-sjabloon. U kunt ook onze andere voorgestelde artikelen doornemen -
- Hoe de Excel INDEX-functie te gebruiken?
- Geavanceerd Excel | Database functie
- Voorbeelden over TRIM-formule in Excel
- Handleiding voor Excel OFFSET-formule