Complexe formules in Excel (inhoudsopgave)
- Inleiding tot complexe formules in Excel
- Kies Functie Dienen als opzoekfunctie
- VERT.ZOEKEN MET KIEZEN
- ALS Met EN & OF-functie
Inleiding tot complexe formules in Excel
Excel wordt geleverd met veel nuttige formules, we hebben een categorie Financieel, Logisch, Tekst, Datum & tijd, Opzoeken en referenties en Wiskunde & Trig ”. Elke afzonderlijke formules zijn goed genoeg voor intermediaire gebruikers waar ze geconfronteerd worden met ongecompliceerde situaties, maar naarmate je verder gaat naar het volgende niveau, moet je vaardigheid de hoogte in gaan, dus in dat opzicht speelt het toepassen van complexere formules in excel een cruciale rol bij het oplossen van complexe situaties. Dit artikel in gedachten houdend, nemen we u mee door enkele belangrijke en broodnodige complexe formules. Lees verder.
Kies Functie Dienen als opzoekfunctie
Niet veel van jullie hebben de CHOOSE-functie gebruikt, maar dit is ook erg handig als opzoekfunctie.
U kunt deze complexe formules Excel-sjabloon hier downloaden - Complexe formules Excel-sjabloon Kijk bijvoorbeeld naar de onderstaande gegevens.
We hebben gegevens van klanten op maandelijkse basis onder deze tabel, we hebben elke klantentabel. In deze tabel heb ik in cel B13 de vervolgkeuzelijst van de maand gemaakt, zodat wanneer deze vervolgkeuzelijst verandert, we het totaal van de geselecteerde maand moeten krijgen.
Maak een tabel zoals hieronder.
Pas VERT.ZOEKEN toe op de cel D13.
Open nu de KIES-functie in cel B14.
Selecteer het INDEX-nummer als VLOOKUP-cel en maak er een absolute referentie van.
Selecteer nu voor elk argument elke maand verkoopnummers.
Sluit de beugel en druk op Enter.
Kopieer en plak de formule nu ook in onderstaande cellen.
Nu kiest de formule de nummers van de geselecteerde maand.
VERT.ZOEKEN Met KIES-functie
We hebben de traditie VERT.ZOEKEN gezien, maar we kunnen het ook combineren met de KIES-functie. Kijk bijvoorbeeld naar de onderstaande gegevens.
In cellen A1 tot B11 hebben we een verkooptabel en in cel D2 heb ik de vervolgkeuzelijst met maanden gemaakt en in cel E2 moeten we VERT.ZOEKEN toepassen en de geselecteerde verkoopwaarde voor de maand ophalen. Maar het probleem om VLOOKUP toe te passen is een gegevensstructuur, waarbij de opzoekwaarde rechts van de resultaatkolom is, dus VLOOKUP kan de gegevens niet van rechts naar links ophalen. In dit geval kunnen we de functie KIEZEN gebruiken.
Om de tabelarray te selecteren, moeten we hier de functie KIEZEN openen.
In het INDEX-argument Nummer in bloem voeren haakjes 1 & 2 in. Selecteer nu het eerste bereik als Maandkolom.
Selecteer de kolom Verkoop als het tweede bereik.
Vermeld kolomindexnummer als 2 en bereik opzoeken als 0.
Dus we haalden het resultaat voor de maand april uit de tabel. Dus wanneer u geen nauwkeurige gegevensstructuur heeft, kunnen we de functie KIEZEN selecteren.
ALS Met EN & OF-functie
Logische functies zitten vol met logica om de dingen te berekenen. Bekijk de onderstaande voorbeeldgegevens.
Uit de bovenstaande tabel moeten we de bonus berekenen op basis van de onderstaande voorwaarden.
- Als de Service meer dan 1, 5 jaar is en als de afdeling Verkoop of Ondersteuning is, is de Bonus 1000.
- Als er iets anders is, is de bonus 3000.
Om de bonus te berekenen moeten we IF, AND, & OR samen toepassen. Open de voorwaarde ALS in cel D2.
We moeten meerdere voorwaarden testen, de EN-functie openen.
Het volgende logische is nu dus Verkoop of Ondersteuning voor deze open OF-voorwaarde.
Test of de titel Verkoop of Ondersteuning is.
Dus als alle opgegeven voorwaarden WAAR zijn, moet de bonus 10000 zijn.
Als de geleverde logische tests ONWAAR zijn, is de bonus 3000.
Sluit de haak en druk op Enter om het resultaat te krijgen.
Kopieer en plak de formule in de onderstaande cellen.
Dus volgens de voorwaarde hebben we het resultaat gekregen. Als de service> 1, 5 jaar is en de titel Verkoop of Ondersteuning is, krijgen we 10000 of anders krijgen we 3000.
Complexe formule met jokertekens
VLOOKUP vereist dat een exacte opzoekwaarde wordt gekoppeld om de gegevens op te halen. Dit is de traditionele slogan, maar we kunnen de gegevens nog steeds ophalen met behulp van de opzoekwaarde voor deeltjes. Als de opzoekwaarde bijvoorbeeld "VIVO" is en in de hoofdtabel "VIVO Mobile" is, kunnen we nog steeds matchen met jokertekens. We zullen nu een van de voorbeelden zien, hieronder staan de voorbeeldgegevens.
We hebben opzoektabel in kolom A. In kolom C hebben we opzoekwaarden, deze opzoekwaarden zijn niet precies hetzelfde als de opzoektabelwaarden. Dus we zullen zien hoe VLOOKUP toe te passen met jokertekens.
Open eerst de functie VERT.ZOEKEN in cel D1.
Het eerste argument is de opzoekwaarde . Een van de problemen met de opzoekwaarde hier is dat we geen exacte overeenkomst hebben, dus moeten we deze opzoekwaarde vóór en na de opzoekwaarde met een asterisk omsluiten.
Hier hebben we twee asterisk-tekens "*" & C2 & "*" toegepast. Hier geeft een sterretje aan dat er iets tussen het jokerteken moet worden gevonden en retourneert het bijbehorende resultaat.
Hoewel we zojuist het sterretje 'Infosys' hadden, kwam dit overeen met de waarde in de opzoektabel en leverde het exacte resultaat 'Infosys Ltd' op.
Op dezelfde manier hebben we in cel D6 de foutwaarde #WAARDE! omdat er geen woord 'Mintra' in de opzoektabel staat.
Aanbevolen artikelen
Dit is een handleiding voor complexe formules in Excel. Hier bespreken we enkele van de belangrijke en broodnodige complexe formules met voorbeelden en Excel-sjabloon. U kunt ook onze andere voorgestelde artikelen doornemen voor meer informatie -
- ZOEK Formule in Excel (Tips & Tricks)
- Zelfstudies over Excel NORMSINV-functie
- Naamvak en het gebruik ervan in Excel
- Tekenreeksen samenvoegen in Excel