Geavanceerde Excel-formules

Lijst met top 10 geavanceerde Excel-formules en -functies

U kunt deze sjabloon voor geavanceerde Excel-formules hier downloaden - sjabloon voor geavanceerde Excel-formules

# 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 de werknemers-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:

  1. U kunt geen primaire kolom hebben rechts van de kolom waarvoor u de waarde uit een andere tabel wilt vullen. In dit geval mag de kolom Salaris werknemer niet vóór Werknemer-ID staan.
  2. In het geval van dubbele waarden in de primaire kolom in Tabel B, wordt de eerste waarde in de cel ingevuld.
  3. Als u een nieuwe kolom in de database invoegt (bijv. Een nieuwe kolom invoegen vóór salaris werknemer in tabel B), kan de output van de formule verschillen op basis van een functie die u in de formule hebt genoemd (in het bovenstaande geval is de output 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 bij de 5e waarneming te krijgen, staan ​​hieronder de gegevens.

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 invoegt in de gegevens op de 5e rij, retourneert de formule "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 overeenkomst is met een bepaalde tekenreeks of getal in het opgegeven bereik. In het onderstaande voorbeeld proberen we de positie van "Rajesh Ved" in de kolom Werknemersnaam te vinden.

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 reeds 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 50K 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 slechts aan een van de vele voorwaarden moeten voldoen.

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 waarnemingen 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 de werknemers-ID en de naam van de werknemer in één kolom willen weergeven.

We kunnen deze CONCATENATE-formule alleen 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 als we een bepaalde subtekenreeks uit een bepaalde string willen extraheren. De genoemde formules kunnen 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:

Toepassing van de RECHTER-formule in Excel is ook hetzelfde, het is alleen dat we naar het teken rechts van de tekenreeks zouden kijken. In het geval van een MID-functie in Excel moeten we echter de startpositie van de vereiste tekenreeks en de lengte van de tekenreeks opgeven.

# 9 - OFFSET-formule in Excel

Deze geavanceerde Excel-functie met een combinatie van andere functies zoals SOM of GEMIDDELDE kan handig zijn om een ​​dynamisch tintje aan de berekeningen te geven. Het wordt het best gebruikt in gevallen waarin we doorlopende rijen in een bestaande database invoegen. OFFSET Excel geeft 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.