Lijst met Top 10 geavanceerde Excel-formules en -functies
# 1 - VERT.ZOEKEN Formule in Excel
Deze geavanceerde Excel-functie is een van de meest gebruikte formules in Excel. Het is voornamelijk te danken aan de eenvoud van deze formule en de toepassing ervan bij het opzoeken van een bepaalde waarde uit andere tabellen, die één gemeenschappelijke variabele in deze tabellen heeft. Stel dat u twee tabellen heeft met details over het werknemerssalaris en de naam van een bedrijf, waarbij werknemer-ID een primaire kolom is. U wilt het salaris krijgen van tabel B in tabel A.
U kunt VERT.ZOEKEN gebruiken zoals hieronder.
Het zal resulteren in de onderstaande tabel wanneer we deze geavanceerde Excel-formule toepassen in andere cellen van de kolom Salaris werknemer.
Sleep de formule naar de rest van de cellen.
Er zijn drie belangrijke begrenzingen van VERT.ZOEKEN:
- U kunt geen primaire kolom rechts van de kolom hebben waarvoor u de waarde uit een andere tabel wilt vullen. In dit geval mag de kolom Salaris werknemer niet vóór Werknemer-ID staan.
- In de dubbele waarden in de primaire kolom in Tabel B, wordt de eerste waarde in de cel ingevuld.
- Als u een nieuwe kolom in de database invoegt (bijv. Een nieuwe kolom invoegen vóór salaris werknemer in tabel B), kan de uitvoer van de formule verschillen op basis van een functie die u in de formule hebt genoemd (in het bovenstaande geval is het resultaat zou blanco zijn)
# 2 - INDEX-formule in Excel
Deze geavanceerde Excel-formule wordt gebruikt om de waarde van een cel in een bepaalde tabel te krijgen door het aantal rijen, kolommen of beide op te geven. Ex. Om de naam van een medewerker van de 5 krijgt th observatie, Hieronder vindt u de data.
We kunnen de geavanceerde Excel-formule gebruiken zoals hieronder:
Dezelfde INDEX-formule kan worden gebruikt om waarde langs de rij te krijgen. Wanneer u zowel rij- als kolomnummer gebruikt, ziet de syntaxis er ongeveer zo uit:
De bovenstaande formule zou "Rajesh Ved" retourneren.
Opmerking: Als u een andere rij in te voegen in de 5 e rij, zal de formule return “Chandan Kale.” Daarom is de uitvoer afhankelijk van eventuele wijzigingen in de gegevenstabel in de loop van de tijd.
# 3 - MATCH-formule in Excel
Deze geavanceerde Excel-formule retourneert het rij- of kolomnummer wanneer er een specifieke tekenreeks of getal in het opgegeven bereik is. In het onderstaande voorbeeld proberen we "Rajesh Ved" te vinden in de kolom Werknemersnaam.
De formule zou zijn zoals hieronder weergegeven:
De MATCH-functie retourneert 5 als de waarde.
Het derde argument wordt gebruikt voor de exacte overeenkomst. U kunt ook +1 en -1 gebruiken op basis van uw vereisten.
Opmerking: men kan INDEX en MATCH combineren om de beperking van VERT.ZOEKEN te omzeilen.
# 4 - ALS EN Formule in Excel
Er zijn veel gevallen waarin men vlaggen moet maken op basis van enkele beperkingen. We zijn allemaal bekend met de basissyntaxis van IF. We gebruiken deze geavanceerde Excel IF-functie om een nieuw veld te maken op basis van een beperking van een bestaand veld. Maar wat als we meerdere kolommen in overweging moeten nemen bij het maken van een vlag? Ex. In het onderstaande geval willen we alle werknemers markeren met een salaris van meer dan 50.000, maar een werknemers-ID van meer dan 3.
In dergelijke gevallen zouden we IF AND gebruiken. Hieronder vindt u de schermafbeelding voor hetzelfde.
Het zou het resultaat als 0 retourneren.
We kunnen veel voorwaarden of beperkingen hebben om een vlag te maken op basis van meerdere kolommen met AND.
# 5 - ALS OF Formule in Excel
Evenzo kunnen we ook de OR-functie in Excel gebruiken in plaats van AND als we moeten voldoen aan een van de vele voorwaarden.
Als in de bovenstaande gevallen aan een van de voorwaarden wordt voldaan, wordt de cel gevuld als 1 anders 0. We kunnen ook 1 of 0 vervangen door enkele subtekenreeksen met dubbele aanhalingstekens ("").
# 6 - SUMIF-formule in Excel
Bij sommige analyses moet u wellicht enkele waarnemingen filteren bij het toepassen van de som- of telfunctie. In dergelijke gevallen is deze geavanceerde Excel SUMIF-functie in Excel ons te hulp. Het filtert alle observaties op basis van bepaalde voorwaarden in deze geavanceerde Excel-formule en somt ze op. Ex. Wat als we de som van de salarissen willen weten van alleen die werknemers met een werknemers-ID groter dan 3?
Door de SUMIFS-formule toe te passen:
De formule retourneert de resultaten als 322000.
We kunnen ook het aantal werknemers in de organisatie tellen met een werknemers-ID groter dan 3 wanneer we AANTAL.ALS gebruiken in plaats van SOM.ALS.
# 7 - CONCATENATE Formule in Excel
Deze geavanceerde Excel-functie is een van de formules die met meerdere varianten kunnen worden gebruikt. Deze geavanceerde Excel-formule helpt ons om verschillende tekstreeksen samen te voegen tot één tekstreeks. Voorbeeld: als we werknemers-ID en werknemersnaam in één kolom willen weergeven.
We kunnen deze CONCATENATE-formule gebruiken om dat te doen.
De bovenstaande formule resulteert in "1Aman Gupta".
We kunnen nog een variant hebben door een enkel koppelteken tussen ID en NAAM te plaatsen. Ex. CONCATENATE (B3, "-", C3) zal resulteren in "1-Aman Gupta". We kunnen dit ook gebruiken in VERT.ZOEKEN wanneer ZOEKEN in Excel-waarde een combinatie is van meer dan één variabele.
# 8 - LINKS, MIDDEN en RECHTS Formule in Excel
We kunnen deze geavanceerde Excel-formule gebruiken om een specifieke substring uit een bepaalde string te extraheren. Het kan worden gebruikt op basis van onze vereisten. Ex. Als we de eerste 5 tekens uit Werknemersnaam willen extraheren, kunnen we de LINKS-formule in Excel gebruiken met de kolomnaam en tweede parameter als 5.
De output wordt hieronder gegeven:
De toepassing van de RECHTS-formule in Excel is ook hetzelfde. Het is alleen dat we het personage rechts van de string zouden bekijken. In het geval van een MID-functie in Excel moeten we echter de startpositie van de vereiste tekstreeks en lengte van de tekenreeks opgeven.
# 9 - OFFSET-formule in Excel
Deze geavanceerde Excel-functie met een combinatie van andere functies zoals SOM of GEMIDDELDE, zou een dynamisch tintje kunnen geven aan de berekeningen. Het wordt het best gebruikt in gevallen waarin we doorlopende rijen in een bestaande database invoegen. OFFSET Excel biedt ons een bereik waarin we de referentiecel, het aantal rijen en kolommen moeten vermelden. Ex. Als we het gemiddelde willen berekenen van de eerste 5 werknemers in het bedrijf waar we een salaris van werknemers hebben, gesorteerd op werknemer-ID, kunnen we het volgende doen. De onderstaande berekening geeft ons altijd een salaris.
- Het geeft ons de som van de salarissen van de eerste 5 werknemers.
# 10 - TRIM-formule in Excel
Deze geavanceerde Excel-formule wordt gebruikt om de onbelangrijke spaties uit de tekst op te ruimen. Ex. Als we spaties aan het begin van een naam willen verwijderen, kunnen we deze gebruiken door de TRIM-functie in Excel te gebruiken, zoals hieronder:
De resulterende output zou "Chandan Kale" zijn zonder enige spatie voor Chandan.
