Als functie in Excel met meerdere voorwaarden | Geneste formule
In deel 1 van onze zelfstudie over de Excel ALS-functie zijn we begonnen de basis van de Excel ALS-functie te leren.
Zoals je je misschien nog herinnert, hebben we enkele ALS-formules besproken voor getallen, datums en tekstwaarden, evenals hoe je een ALS-functie kunt schrijven voor lege en niet-lege cellen.
Voor een krachtige gegevensanalyse moet je echter vaak meerdere voorwaarden tegelijk evalueren, wat betekent dat je meer geavanceerde logische tests moet maken met meerdere ALS-functies in één formule.
De onderstaande voorbeelden van formules laten je zien hoe je dit correct kunt doen.
Geneste ALS formules achter elkaar gebruiken
Een van de mogelijkheden die ik zelf ook nog vaak gebruik, is om meerdere ALS formules achter elkaar te gebruiken. Een voorbeeld daarvan zou kunnen zijn:
=ALS(A1>2;”Boven Twee”;ALS(A1<0;”Onder Nul”;”Nul tot Twee”))
Je moet deze formule zo lezen:
- Als cel A1 groter is dan 2, geef dan de tekst “Boven Twee” weer
- Anders als cel A1 kleiner is dan nul, geef dan de tekst “Onder Nul” weer
- Anders (als aan beide voorwaarden niet is voldaan) geef dan de tekst “Nul tot Twee” weer
Je kunt hiermee formules maken met zoveel =ALS functies erin als je wilt. Hoe meer je er toevoegt, hoe moeilijker het te lezen wordt dus op een gegeven moment is het bijna niet meer bij te houden.
Let ook op dat er aan het einde van deze formule twee “)” staan weergegeven, eentje voor elke ALS formule die we erin hebben opgenomen.
Excel ALS-functie met meerdere EN / OF-voorwaarden
Samenvattend kunnen er 2 basistypen van meerdere voorwaarden zijn, met logica EN en OF. Daarom moet je ALS-functie respectievelijk een EN- of OF-functie in de logische test insluiten.
EN functie
Als je logische test de EN-functie bevat, retourneert Microsoft Excel WAAR als aan alle voorwaarden is voldaan; anders wordt FALSE geretourneerd.
OF functie
Als je de OF-functie in de logische test gebruikt, retourneert Excel WAAR als aan één van de voorwaarden is voldaan; FALSE als aan geen is voldaan.
Om het punt beter te illustreren, laten we een paar ALS-voorbeelden bekijken met meerdere voorwaarden.
Voorbeeld 1: De functie ALS & EN gebruiken in Excel
Stel dat je een tabel hebt met de resultaten van twee examenuitslagen.
- De eerste score, opgeslagen in kolom C, moet gelijk zijn aan of groter zijn dan 20.
- De tweede score, vermeld in kolom D, moet gelijk zijn aan of hoger zijn dan 30.
- Alleen als aan beide bovenstaande voorwaarden is voldaan, slaagt een student voor het examen.
De eenvoudigste manier om een juiste formule te maken, is door eerst de voorwaarde op te schrijven en deze vervolgens op te nemen in het argument logic_test van je ALS-functie:
Dus: EN (B2> = 20; C2> = 30)
ALS / EN-formule: =ALS(EN(C2>=20; D2>=30); “Geslaagd”; “Gezakt”)
Makkelijk toch?
De formule vertelt Excel om “Geslaagd” terug te geven als de waarde in kolom C> = 20 EN de waarde in kolom D> = 30 en dus aan beide voorwaarden is voldaan. Anders retourneert de formule “Gezakt”.
De onderstaande schermafbeelding bewijst dat onze Excel Als/ EN functie correct is:
Notitie. Microsoft Excel controleert alle voorwaarden in de EN-functie, zelfs als een van de reeds geteste voorwaarden evalueert als ONWAAR.
Dergelijk gedrag is een beetje ongewoon, omdat in de meeste programmeertalen opeenvolgende voorwaarden niet worden getest als een van de vorige tests FALSE heeft geretourneerd.
In de praktijk kan een ogenschijnlijk correcte ALS / EN-formule een fout veroorzaken vanwege deze specificiteit.
Neem de formule =ALS(EN (A2<>0;(1/A2)>0,5);”Goed”, “Slecht”)
Deze zal bijvoorbeeld “Delen door nulfout” (#DIV/0!) geven als cel A2 gelijk aan 0. Om dit te voorkomen, moet je een geneste ALS-functie gebruiken:
=ALS(A2<>0;ALS((1/A2)>0,5;”Goed”;”Slecht”);”Slecht”)
Voorbeeld 2: ALS gebruiken met OF-functie in Excel
Je gebruikt de combinatie van ALS & OF-functies op een vergelijkbare manier. Het verschil met de hierboven besproken ALS / EN-formule is dat Excel WAAR retourneert als aan ten minste een van de opgegeven voorwaarden is voldaan.
Dus als we de bovenstaande formule op de volgende manier wijzigen:
=ALS(OF(C2>=20;D2>=30);”Geslaagd”;”Gezakt”)
Kolom E krijgt het “Geslaagd” -teken als de eerste score gelijk is aan of groter is dan 20 OF de tweede score gelijk is aan of groter is dan 30.
Zoals je in de onderstaande screenshot kunt zien, hebben onze studenten een betere kans om het eindexamen te behalen met dergelijke voorwaarden:
Voorbeeld 3: ALS gebruiken met EN & OF-functies
Als je jouw gegevens moet evalueren op basis van meerdere sets van meerdere voorwaarden, moet je beide EN & OF-functies tegelijkertijd gebruiken.
Stel dat je in de bovenstaande tabel de volgende criteria hebt om het succes van de studenten te evalueren:
- Voorwaarde 1: kolom C> = 20 en kolom D> = 25
- Voorwaarde 2: kolom C> = 15 en kolom D> = 20
Als aan één van de bovenstaande voorwaarden is voldaan, wordt het eindexamen geacht te zijn geslaagd, anders – niet geslaagd.
De formule lijkt misschien lastig, maar je zult meteen zien dat het niet zo is! Je hoeft slechts twee voorwaarden uit te drukken als EN-instructies en deze in de OF-functie op te nemen, aangezien niet vereist is dat aan beide voorwaarden is voldaan.
OF (EN (C2> = 20; D2> = 25); EN (C2> = 15; D2> = 20)
Gebruik ten slotte de bovenstaande OF-functie als de logische test in de ALS-functie en voer argumenten waarde_als_waar en waarde_als_onwaar in. Als resultaat krijg je de volgende ALS-formule met meerdere EN / OF-voorwaarden:
=ALS(OF(EN(C2>=20;D2>=25);EN(C2>=15;D2>=20));”Geslaagd”;”Gezakt”)
De onderstaande schermafbeelding geeft aan dat we de formule goed hebben:
Natuurlijk ben je niet beperkt tot het gebruik van slechts twee EN / OF-functies in je Excel ALS formules. Je kunt zoveel logische functies gebruiken als jouw bedrijfslogica vereist, op voorwaarde dat:
- In Excel 2016, 2013, 2010 en 2007 bevat je formule niet meer dan 255 argumenten en de totale lengte van de formule is niet groter dan 8.192 tekens.
- In Excel 2003 en lager kun je maximaal 30 argumenten gebruiken en de totale lengte van je formule mag niet meer dan 1.024 tekens bevatten.
En dat is alles wat ik te zeggen heb over het gebruik van de ALS-functie in Excel. Bedankt voor het lezen en ik hoop je volgende week op onze blog te zien!
Ben helemaal niet handig in Excel en heb enorm lopen puzzelen, maar kom er niet uit. Ik probeer het volgende:
een bedrag 200 is veel.
Het is me gelukt met 2 waardes, maar niet met drie. Hoe kan ik dit oplossen?
Hoi Blue, ik snap je vraag niet helemaal “een bedrag 200 is veel” zou je iets verder kunnen toelichten?
Hoi Joost,
Ik heb ook lopen puzzelen aan de hand van bovenstaande en ik heb nu één formulie die ik wil combineren met 3 andere opties, zou jij misschien weten hoe ik dit moet formuleren? Het is wel ingewikkelde formule met veel opties:
– Cel B6 is een tekstveld waarin we aangeven of een materiaal gelamineerd wordt, hier typen we JA of NEE
– Cel B3 is een keuzeveld met 4 materialen (Vinyl, PVC 3mm, Alu 2mm, Magn. 0,85)
– In kolom F staat per materiaal een productietijd inclusief lamineren (Vinyl = F3, PVC 3mm = F5, Alu 2mm = F6, Magn. 0,85 = F7)
– In kolom G staat per materiaal een productietijd zonder lamineren (Vinyl = G3, PVC 3mm = G5, Alu 2mm = G6, Magn. 0,85 = G7)
Als lamineren (B6) op JA staat, en de materiaalkeuze (B3) is Vinyl, dan wordt gekozen voor F3. Als lamineren op NEE staat en de materiaalkeuze op Vinyl, dan wordt gekozen voor G3.
Ik heb nu de formule voor het Vinyl en deze werkt:
ALS(EN(B6=”JA”;B3=”Vinyl”);F3;G3)
Vertaald: Als EN (lamineren=JA) EN (Materiaal=Vinyl), dan (productietijd met lamineren) en anders (productietijd zonder lamineren).
Voor PVC 3mm wordt dit dus: ALS(EN(B6=”JA”;B3=”PVC 3mm”);F5;G5)
Voor Alu 2mm wordt dit dus: ALS(EN(B6=”JA”;B3=”Alu 2mm”);F6;G6)
Voor Magn. 0,85 wordt dit dus: ALS(EN(B6=”JA”;B3=”Magn. 0,85″);F7;G7)
Ik wil graag één formule waarin deze formules voor alle 4 de materialen staat gecombineerd. Weet jij hoe ik deze vier formules in één formule kan verwerken?
Hopelijk kun je me verder helpen!!
Hier een suggestie om deze vier formules te combineren tot één formule in Excel. Je kunt de functie `ALS` (of in het Engels `IF`) meerdere keren nesten om de verschillende mogelijkheden af te handelen. Hier is de gecombineerde formule die aan je vereisten voldoet:
=ALS(EN(B6=”JA”, B3=”Vinyl”), F3, ALS(EN(B6=”JA”, B3=”PVC 3mm”), F5, ALS(EN(B6=”JA”, B3=”Alu 2mm”), F6, ALS(EN(B6=”JA”, B3=”Magn. 0,85″), F7, ALS(B3=”Vinyl”, G3, ALS(B3=”PVC 3mm”, G5, ALS(B3=”Alu 2mm”, G6, ALS(B3=”Magn. 0,85″, G7, “”)))))))
Hoe werkt deze formule:
1. Het eerste deel van de `ALS` functie: `ALS(EN(B6=”JA”, B3=”Vinyl”), F3, …)` Hier controleert de functie of B6 gelijk is aan “JA” en B3 gelijk is aan “Vinyl”. Als dit waar is, retourneert de formule F3 (de productietijd met lamineren voor Vinyl). Als dit niet waar is, gaan we naar het volgende deel.
2. Het volgende deel is de volgende `ALS` functie: `ALS(EN(B6=”JA”, B3=”PVC 3mm”), F5, …)` Hier controleren we of B6 gelijk is aan “JA” en B3 gelijk is aan “PVC 3mm”. Als dit waar is, retourneert de formule F5 (de productietijd met lamineren voor PVC 3mm). Als dit niet waar is, gaan we naar het volgende deel.
3. We gaan op dezelfde manier door met de derde en de vierde materiaalopties (Alu 2mm en Magn. 0,85) met hun respectievelijke `ALS` functies.
4. Als geen van de bovenstaande voorwaarden waar is, betekent dit dat lamineren (B6) op “NEE” staat of dat de materiaalkeuze (B3) geen van de bovengenoemde opties is. In dat geval worden de productietijden zonder lamineren (G3, G5, G6, G7) geretourneerd, afhankelijk van de materiaalkeuze (B3).
Merk op dat ik voor het geval van de productietijden zonder lamineren geen extra EN-functie gebruik omdat de waarde van B6 er niet toe doet in dat geval.
Plaats deze formule in de cel waarin je het resultaat wilt zien, en het zal de juiste productietijd teruggeven op basis van de ingevoerde waarden in B6 en B3.
Kun je mij wellicht ook helpen?
ik wil het resultaat van 3 cellen in 1 cel.
Stel: in A1 staat Gering en in B1 staat incidenteel en in C1 staat gevorderd dan wil ik in D1 als resultaat 1.
Hoe krijg ik dit voor elkaar?
Hallo Danny, om het resultaat van drie cellen in één cel te krijgen op basis van bepaalde voorwaarden, kun je ook de geneste ALS-formule gebruiken. In jouw geval, waarbij je wilt dat D1 gelijk is aan 1 als A1 is “Gering”, B1 is “Incidenteel” en C1 is “Gevorderd”, kun je de volgende formule gebruiken:
=ALS(EN(A1=”Gering”, B1=”Incidenteel”, C1=”Gevorderd”), 1, 0)
Deze formule maakt gebruik van de EN-functie om ervoor te zorgen dat alle drie de voorwaarden waar zijn.
Als dat het geval is, wordt 1 weergegeven, anders wordt 0 weergegeven.
Succes!
Ik wil graag een regel maken en dan daarin 2 formules samen voegen.
Formule 1: =ALS(OF(F26=”Ja”);(E26-E26))
Formule 2: =ALS(OF(F26=”Nee”);(E26))
Ik krijg het niet voor elkaar wat moet ik doen?
Je wilt formules combineren in één cel, afhankelijk van de waarde in cel F26.
Als ik het goed begrijp, wil je Formule 1 gebruiken als de waarde in F26 gelijk is aan “Ja”, en Formule 2 als de waarde in F26 gelijk is aan “Nee”.
Als dat het geval is, kun je de functie “ALS” gebruiken in combinatie met de functie “OF” om dit te bereiken.
Hier is de gecombineerde formule:
=ALS(OF(F26=”Ja”, F26=”Nee”), E26-E26, ALS(F26=”Nee”, E26, “”))
Formule uitleg:
– `ALS(OF(F26=”Ja”, F26=”Nee”)`: Hier controleren we of F26 gelijk is aan “Ja” of “Nee”.
– `E26-E26`: Dit gedeelte wordt uitgevoerd als F26 gelijk is aan “Ja”. Het resultaat is altijd nul, omdat we E26 van E26 aftrekken (wat dus gelijk is aan nul).
– `ALS(F26=”Nee”, E26, “”)`: Dit gedeelte wordt uitgevoerd als F26 gelijk is aan “Nee”. In dat geval geven we de waarde van cel E26 terug. Als F26 iets anders bevat dan “Nee” of “Ja”, dan geven we een lege tekenreeks (“”) terug.
Met deze formule krijg je het gewenste resultaat waarbij de formules worden gecombineerd op basis van de waarde in cel F26.
Als F26 gelijk is aan “Ja”, zal het resultaat nul zijn; als F26 gelijk is aan “Nee”, zal het resultaat gelijk zijn aan de waarde in cel E26.
In alle andere gevallen zal de cel leeg zijn.
graag had ik het volgende gehad maar ik geraak er niet
als cel B2 = tekst 1 dan komt er in cel B3 9u maar als B2 = tekst 2 dan komt erin in B3 10u
Momenteel heb ik volgende formule:
=ALS(ALS(B2=”tekst 1″;B2=”tekst 2″);”09:00″;”10:00″)
De functie ALS moet twee voorwaarden hebben, in jouw formule worden beide voorwaarden binnen één ALS-functie gecombineerd.
Het kan als volgt:
=ALS(B2=”tekst 1″; “09:00″; ALS(B2=”tekst 2”; “10:00”; “Onbekende tekst”))
In deze formule wordt eerst gecontroleerd of B2 gelijk is aan “tekst 1”. Als dat waar is, wordt “09:00” weergegeven.
Als B2 niet gelijk is aan “tekst 1”, wordt de tweede ALS-functie uitgevoerd om te controleren of B2 gelijk is aan “tekst 2”. Als dat waar is, wordt “10:00” weergegeven.
Als B2 geen van beide teksten bevat, kun je ervoor kiezen om een standaardwaarde in te stellen, bijvoorbeeld “Onbekende tekst” of een lege string (“”).
Zorg ervoor dat je de puntkomma (;) gebruikt als scheidingsteken als dat de standaard is voor jouw Excel-instellingen. Als je komma’s gebruikt, pas de formule dan aan door komma’s te gebruiken in plaats van puntkomma’s.
Succes!
Het werkt. Bedankt!
Stel, ik heb een tabel waarin één van de opgenomen data gemeentes betreft. Ik wil een kolom bijmaken waarin in nu automatisch wil laten verschijnen in welke provincie de gemeente ligt. Dus, als in de gemeentekolom het gaat over “gemeente a” tot “gemeente g” dan moet in de kolom “provincie 1” verschijnen, als het gemeente h tot m is dan provincie 2, gemeente n tot s provincie 3, gemeente t tot v provincie 4 en gemeente w tot z provincie 5.
Concreet: ik wil ongeveer 60 gemeenten in de kolom ernaast laan de 5 provincies waarin ze vallen. meerdere categorie dus… iemand een idee hoe ik dat doe?
Als je wilt controleren met welke letter de naam van de gemeente begint, kun je onderstaande formule gebruiken zodat deze de eerste letter van de gemeentenaam gebruikt.
Met de gemeente namen in kolom A en je de provincies in kolom B, voer dan per regel in cel B2 de volgende formule in.
=ALS(EN(UPPER(LINKS(A2,1))>=”A”, UPPER(LINKS(A2,1))<="G"), "Provincie 1", ALS(EN(UPPER(LINKS(A2,1))>=”H”, UPPER(LINKS(A2,1))<="M"), "Provincie 2", ALS(EN(UPPER(LINKS(A2,1))>=”N”, UPPER(LINKS(A2,1))<="S"), "Provincie 3", ALS(EN(UPPER(LINKS(A2,1))>=”T”, UPPER(LINKS(A2,1))<="V"), "Provincie 4", ALS(EN(UPPER(LINKS(A2,1))>=”W”, UPPER(LINKS(A2,1))<="Z"), "Provincie 5", "Onbekend"))))) ``` De `UPPER`-functie zorgt ervoor dat de vergelijking niet afhankelijk is van de hoofdlettergevoeligheid. Succes!
Ik zou graag een artikelen lijst maken met keuzemogelijkheden. Indien jas in een kolom A in de dropdown wordt gekozen, dan zou in kolom B alleen de verschillende jasmaten moeten verschijnen. Indien in kolom A in de dropdown schoen wordt gekozen dan zou in kolom B alleen de schoenmaten moeten verschijnen waaruit gekozen kan worden. Ik kom hier niet goed uit. Ik heb al deze formule gebruikt =ALS(A2=”Categorie 1″;Subcategorieën_Categorie1;ALS(A2=”Categorie 2″;Subcategorieën_Categorie2;ALS(A2=”Categorie 3″;Subcategorieën_Categorie3;””))), maar dan met er een haakje voor?
Om een dropdown-menu te maken dat afhankelijk is van een dopdown selectie in een andere cel, kun je gebruikmaken van de functie “Gegevensvalidatie” in combinatie met de functie “INDIRECT” in Excel. Hier is hoe je dit kunt doen:
Maak een lijst met alle categorieën en hun bijbehorende subcategorieën in aparte kolommen. Laten we zeggen dat je een lijst hebt met categorieën in kolom C en hun bijbehorende subcategorieën in kolom D.
Voorbeeld met een lijst van productcategorieën en hun bijbehorende subcategorieën.
Categorie | Subcategorie
Kleding | T-shirts
Kleding | Broeken
Kleding | Jassen
Schoenen | Sneakers
Schoenen | Laarzen
Schoenen | Sandalen
Zorg ervoor dat deze lijst in een werkblad staat voordat je begint met het instellen van de gegevensvalidatie en de INDIRECT-formule.
Selecteer vervolgens de cel waarin je het dropdown-menu wilt hebben (bijvoorbeeld cel A2).
Ga naar het tabblad “Gegevens” in de Excel-werkbalk en klik op “Gegevensvalidatie”.
Kies als type “Lijst” en bij de bron typ je “=C:C”.
Hiermee maak je een dropdown-menu met alle categorieën in kolom C.
Klik OK.
Nu moeten we de dropdown-lijst in kolom B laten verschijnen, afhankelijk van de selectie in kolom A. Ga naar cel B2 (of de cel waarin je de subcategorieën wilt weergeven).
Ga vervolgens naar het tabblad “Gegevens” en klik op “Gegevensvalidatie”.
Kies ook hier als type “Lijst” en bij de bron typ je “=INDIRECT(“D”&VERGELIJKEN(A2,C:C,0))”.
Dit zal de juiste lijst met subcategorieën tonen, afhankelijk van de selectie in cel A2.
Klik op OK.
Nu zou je een dropdown-menu moeten hebben in cel B2 met de subcategorieën die overeenkomen met de categorie die is geselecteerd in cel A2.
Herhaal deze stappen voor andere rijen en kolommen indien nodig.
Zo zou het moeten lukken. Succes!
Ik heb een planning met 14 medewerkers (rijen) werkzaam op 8 locatie’s die we invullen onder werkdagen die in kolommen staan. Ik wil eigenlijk één veld laten vullen met een controle of de locaties allemaal op iedere werkdag ingevuld staan. Dus in de regel met de werkdag moeten 8 variabelen staan anders wil ik een foutmelding krijgen. De locaties staan als tekst. Weet iemand wat ik als formule zou moeten gebruiken?
Je kunt de functie COUNTIF gebruiken om te controleren of er precies 8 locaties zijn ingevuld voor elke werkdag.
Of in NL “AANTAL.ALS“.
Stel dat je werkdagen in de kolommen B t/m H staan (bijvoorbeeld B1 tot H1 voor maandag tot en met zondag), en de locaties voor elke medewerker in de rijen 2 tot en met 15 (bijvoorbeeld A2 tot A15), dan kun je deze formule gebruiken in cel I2 (voor maandag).
=ALS(AANTAL.ALS(B$2:H$15;”<>“)<>8;”Foutmelding”;””)
Deze formule telt het aantal gevulde cellen in de reeks van B2 tot H15 voor elke werkdag en controleert of dit precies 8 is.
Als het aantal gevulde cellen niet gelijk is aan 8, geeft het “Foutmelding” terug, anders geeft het een lege tekst terug.
Je kunt deze formule vervolgens naar beneden kopiëren voor elke werkdag in kolommen I tot P.
Op die manier krijg je een foutmelding als er niet precies 8 locaties zijn ingevuld voor een bepaalde dag.
Succes!
Bedankt Joost! Dit is alleen niet helemaal volledig. Soms werken er 2 medewerkers op 1 locatie en dan telt hij er ook 8. Ik was eigenlijk op zoek naar een formule die controleerde of de specifieke locaties allemaal genoemd worden, is dat uberhaupt mogelijk?
Oke, begrijp ik het goed dat ja dan het volgende hebt:
Een planning met 14 medewerkers (rijen).
In de kolommen staan de werkdagen maandag t/m zondag.
In de cellen de naam van de 8 verschillende locaties.
Daarmee kun je dus zien welke medewerker op welke werkdag op welke locatie heeft gewerkt.
Eén controle veld laten met een controle of de 8 verschillende locaties op iedere werkdag (kolom) ingevuld staan bij de verschillende medewerkers.
Dus in de 14 regels met de werkdagen kolommen moeten minimaal 8 locaties staan per kolom, anders moet er een foutmelding komen.
Dan heb je waarschijnlijk genoeg aan de formule:
=ALS(ALS(AANTAL.ALS(B2:H15;B2:H15)>=8;1;0)=1;”Alle locaties ingevuld”;”Fout: Niet alle locaties ingevuld”)
De formule controleert of elke kolom (werkdag) in het opgegeven bereik minstens 8 unieke locaties heeft.
Als dat het geval is, geeft de formule de boodschap “Alle locaties ingevuld” terug.
Als een kolom minder dan 8 unieke locaties heeft, geeft de formule de foutmelding “Fout: Niet alle locaties ingevuld” terug.
Dit zorgt ervoor dat elke kolom (werkdag) correct wordt gecontroleerd op het vereiste aantal unieke locaties.
Succes met de planning!
Hi Joost, bedankt voor je snelle reacties! Velden en kolommen kloppen zoals je het aangeeft maar als ik je formule noteer krijg ik #naam als uitkomst. Enig idee waarom het niet werkt?
Oke, dat nog een andere methode waar je wellicht iets aan hebt, zie ook afbeelding hieronder.
Om te controleren of een specifieke locatie voorkomt in een werkdagkolom, kunnen we de functie AANTAL.ALS gebruiken.
Hier is hoe je dat kunt doen voor locatie 1 (cel J2):
=ALS(ALS(AANTAL.ALS($B$2:$B$15;J2)>=1;1;0)*ALS(AANTAL.ALS($C$2:$C$15;J2)>=1;1;0)*ALS(AANTAL.ALS($D$2:$D$15;J2)>=1;1;0)*ALS(AANTAL.ALS($E$2:$E$15;J2)>=1;1;0)*ALS(AANTAL.ALS($F$2:$F$15;J2)>=1;1;0)*ALS(AANTAL.ALS($G$2:$G$15;J2)>=1;1;0)*ALS(AANTAL.ALS($H$2:$H$15;J2)>=1;1;0)=1;”Alle locaties ingevuld”;”Fout: Niet alle locaties ingevuld”)
Uitleg:
AANTAL.ALS($B$2:$H$15;J2)` telt hoe vaak de waarde in cel J2 (locatie 1) voorkomt in het bereik van werkdagkolommen ($B$2:$H$15).
>=1 controleert of de locatie minstens één keer voorkomt in een van de werkdagkolommen.
ALS functie geeft “Alle locaties ingevuld” terug als de locatie minstens één keer voorkomt, anders geeft het “Fout: Niet alle locaties ingevuld” terug.
Je kunt deze formule kopiëren en plakken voor elke locatie die je wilt controleren, waarbij je de celverwijzingen aanpast aan de locatiecellen (J2, J3, J4, enzovoort).
Hallo Joost, wederom bedankt voor je energie in mijn vraagstuk! Vind ik echt geweldig! Ik blijf alleen bij jouw formule #naam! als resultaat krijgen. Er staan geen namen in de formule, de check is op veld J2, enig idee waar dit aan kan liggen?
Het gegeven voorbeeld in het vorige bericht, kun je nu downloaden.
Wellicht kun je met behulp van dit Excel voorbeeld “Planning” verder.
Succes!
Hallo Joost, ik ben op zoek naar een ALS functie met 7 voorwaarden. Als cel A1 ”1” bevat dan wordt cel B1 ”Beste keuze” worden.
Zo heb ik 7 ALS ;regels.
A1 bevat ”1” dan B1 ”Beste keuze”;
A1 bevat ”2” dan B1 ”Goede keuze”;
A1 bevat ”3” dan B1 ”Aanvaardbare keuze”;
A1 bevat ”4” dan B1 ”Minder goede keuze”;
A1 bevat ”5” dan B1 ”Af te raden keuze”;
A1 bevat ”6” dan B1 ”Slechte keuze”;
A1 bevat ”7” dan B1 ”Onaanvaardbare keuze”;
Alvast bedankt!
Joep Verharen
Je kunt onderstaande ALS-functie gebruiken om dit te bereiken.
=ALS(A1=1, “Beste keuze”, ALS(A1=2, “Goede keuze”, ALS(A1=3, “Aanvaardbare keuze”, ALS(A1=4, “Minder goede keuze”, ALS(A1=5, “Af te raden keuze”, ALS(A1=6, “Slechte keuze”, ALS(A1=7, “Onaanvaardbare keuze”, “Geen geldige keuze”)))))))
Deze Excel formule controleert de waarde van cel A1 en geeft de bijbehorende tekstwaarde terug in cel B1, afhankelijk van de zeven voorwaarden die je hebt opgegeven. Als A1 geen van de opgegeven waarden bevat, wordt “Geen geldige keuze” weergegeven.
Succes!
Dit kan veel eenvoudiger dan die geneste Als formule, namelijk met SCHAKELEN. =SCHAKELEN(A1;1;”Beste keuze”;2;”Goede keuze”;3;”Aanvaardbare keuze”;4;”Minder goede keuze”;5;”Af te raden keuze”;6;”Slechte keuze”;7;”Onaanvaardbare keuze”;”Geen geldige keuze”)
In dit geval zou ik geen geneste als formules gebruiken, maar gebruik maken van de functie SCHAKELEN. De formule ziet er dan zo uit: =SCHAKELEN(A1;1;”Beste keuze”;2;”Goede keuze”;3;”Aanvaardbare keuze”;4;”Minder goede keuze”;5;”Af te raden keuze”;6;”Slechte keuze”;7;”Onaanvaardbare keuze”;”Keuze valt niet binnen het bereik”)
3 verschillende waardes met elk een andere aktie. als cel tekst eerste 3 leters Gra bevat dan 1 als cel eerste 3 leters bevat Lem dan 2 als cel eerste 3 leters Ora bevat dan 3
Je kunt dit bereiken met onderstaande ALS-functie.
=ALS(LINKS(A1,3)=”Gra”, 1, ALS(LINKS(A1,3)=”Lem”, 2, ALS(LINKS(A1,3)=”Ora”, 3, “Geen geldige waarde”)))
De functie LINKS wordt gebruikt om de eerste drie letters van de cel A1 te extraheren.
Vervolgens vergelijkt de ALS-functie deze waarden met “Gra”, “Lem” en “Ora” en geeft respectievelijk 1, 2 of 3 terug als ze overeenkomen.
Als geen van deze voorwaarden wordt voldaan, wordt “Geen geldige waarde” weergegeven.
succes!
Hoi, als ik in cel A2 een datum heb en in cel B2 een getal. Dan wil ik graag dat in cel C3 bijv. 18 maanden worden opgeteld bij de datum als het getal 1 is. Bij een ander getal niets in kolom C2 of een 0. Kan ik hiervoor ook de als functie gebruiken? Ik kom er niet uit. Alvast bedankt voor de reactie
Hier een voorbeeld hoe de ALS-functie te gebruiken om dit te bereiken.
=ALS(B2 = 1; A2 + 18*30; ALS(B2 = 0; “”; “”))
Als cel B2 gelijk is aan 1, dan voegt de formule 18 maanden toe aan de datum in cel A2.
Hier wordt aangenomen dat een maand 30 dagen heeft, dus we voegen 18 keer 30 dagen toe.
Als cel B2 gelijk is aan 0, dan geeft de formule een lege string (“”) terug.
Als B2 geen van beide is (dus niet 1 en niet 0), dan geeft de formule ook een lege string terug.
Je kunt deze formule in cel C2 plaatsen en aanpassen aan jouw specifieke situatie.
Beste Joost,
ik hoop dat je mij ook kunt helpen want heb van alles geprobeerd maar het lukt mij niet. Ik wil in een cel (A1) zoeken naar bepaald woord en als dat woord gevonden wordt dan een bepaald naam geven in de cel ernaast. Echter ik wil 1 formule waarin ik diverse woorden definieer om naar te zoeken met verschillende namen dan ook.
celinhoud A11:
“hallo hoe gaat het zoektekst andere gegevens in dezelfde cel”
celinhoud A12:
“hallo hoe gaat het andere gegevens opdracht andere in dezelfde cel”
celinhoud A13:
“hallo even andere woorden in deze cel Excel andere in dezelfde cel”
Met deze formule lukt het mij maar dat heeft maar 1 woord om naar te zoeken, ik wil dit dus uitbreiden naar velen.
cel B11 met formule: =ALS(AANTAL.ALS(A11;”*zoektekst*”);”Huis”;”niet gevonden”)
geeft mij dus “huis” in B11.
cel B12 met formule:
=ALS(AANTAL.ALS(A12;”*opdracht*”);”Hond”;”niet gevonden”)
geeft mij dus “hond” in B12.
cel B13 met formule:
=ALS(AANTAL.ALS(A13;”*Excel*”);”Kat”;”niet gevonden”)
geeft mij dus “kat”in B13.
Nu wil ik die 3 formules dus combineren in 1 en die dan naar alle rijen (B11, B12, B13) kopieeren, waarbij het woord waar ik naar zoek telkens veranderd.
Je kunt de functie ALS.VERT.ZOEKEN combineren met de functie AANTAL.ALS om te controleren of een bepaald woord voorkomt in de cel.
Hier een voorbeeld:
In cel B11 kun je deze formule gebruiken:
=ALS(AANTAL.ALS(A11;”*zoektekst*”); ALS.VERT.ZOEKEN(“*zoektekst*”; {
{“zoektekst”,”Huis”};
{“opdracht”,”Hond”};
{“Excel”,”Kat”}
}; 2; ONWAAR); “niet gevonden”)
Deze formule zal zoeken naar “zoektekst” in cel A11 en als het wordt gevonden, zal het de bijbehorende waarde (“Huis”) weergeven.
Zo niet, dan zal het “niet gevonden” weergeven.
Je kunt deze formule naar beneden kopiëren naar de andere cellen (B12, B13, enzovoort) en de formule zal automatisch de juiste waarde toewijzen op basis van de zoekwoorden.
Beste Joost, dankjewel voor suggestie.
Echter, de formule werkt niet.
Ik gebruik Excel 2013 en als ik zoek naar functies, krijg ik alleen maar VERT.ZOEKEN maar ALS.VERT.ZOEKEN lijkt niet beschikbaar te zijn.
Ten tweede die accolades, zijn die wel te gebruiken of kan/moet ik die vervangen met een ander teken?
Ik heb de aanhalingstekens ook vervangen met ”
Ik hoop maar dat het aan te passen is voor mijn excel versie.
mvg
Seb
Beste Seb,
Als je Excel 2013 gebruikt en ALS.VERT.ZOEKEN niet beschikbaar is, kun je een andere methode gebruiken om de gewenste functionaliteit te bereiken.
Je kunt bijvoorbeeld de ALS-functie gebruiken in combinatie met de functie VIND.ALLES om te controleren of een bepaald woord voorkomt in de cel.
Hier hoe je dit bijvoorbeeld kunt doen:
=ALS(ISNUMBER(VIND.ALLES(“zoektekst”; A11)); “Huis”; ALS(ISNUMBER(VIND.ALLES(“opdracht”; A11)); “Hond”; ALS(ISNUMBER(VIND.ALLES(“Excel”; A11)); “Kat”; “niet gevonden”)))
Dit is een geneste `ALS`-functie die controleert of de zoektekst voorkomt in cel A11.
Als dat het geval is, geeft het “Huis” terug, anders controleert het of “opdracht” voorkomt, enzovoort.
Wat betreft de accolades (`{}`), je hoeft ze niet te gebruiken in Excel-formules.
Ze worden meestal gebruikt in arrayformules of matrixformules in nieuwere versies van Excel.
Pas deze formule toe in cel B11 en kopieer deze naar de andere cellen (B12, B13, enzovoort) zoals je eerder hebt gedaan.
Succes!
Dankjewel Joost!
Ik heb het nu werken gekregen. Ik moest wel even de “ISNUMBER” vertalen naar de NL formule “ISGETAL” en mn aanhalingstekens aanpassen maar nu lijkt het te werken!
Ga verder ermee aan de slag, want ik moet nog meer woorden zoeken dus de formule moet uitgebreid worden. Ben blij dat dit nu werkt.
Dag allemaal,
Ik ben op zoek naar een ALS functie met een berekening waarbij de datum tussen verschillende datums valt, verschillende dingen geprobeerd maar het lukt niet …
cel B18 is een datum
Als cel B18 valt tussen 1/1/2024 en 31/03/2024 dan wil ik het resultaat van cel E18*cel C31 te zien krijgen
En indien cel B18 valt tussen 1/4/2024 en 30/06/2024 dan wil ik het resultaat van cel E18*cel C30 te zien krijgen.
Heeft er iemand een oplossing ?
Je kunt de ALS-functie gebruiken in combinatie met de EN-functie om te controleren of een datum tussen twee datums valt.
Hier is een voorbeeld:
=ALS(EN(B18 >= DATUM(2024;1;1); B18 <= DATUM(2024;3;31)); E18 * C31; ALS(EN(B18 >= DATUM(2024;4;1); B18 <= DATUM(2024;6;30)); E18 * C30; "Geen geldige datumbereik")) De eerste ALS-functie controleert of de datum in cel B18 tussen 1 januari 2024 en 31 maart 2024 valt. Korte uitleg: Als dat het geval is, wordt het resultaat van E18 * C31 weergegeven. Als de datum niet tussen die datums valt, gaat het naar de tweede ALS-functie. De tweede ALS-functie controleert of de datum in cel B18 tussen 1 april 2024 en 30 juni 2024 valt. Als dat het geval is, wordt het resultaat van E18 * C30 weergegeven. Als de datum niet in een van de bereiken valt, wordt "Geen geldige datumbereik" weergegeven.
Hoi Joost ik heb ook een vraag.
Ik wil niet 2 opties (waar en onwaar) maar 4 opties.
Ik heb een formule nodig die het volgende kan weergeven:
Als waarde van de cel A1A2 maarA3 maar A4 dan “label goud”.
Kun jij mij hierbij helpen?
Helaas kan ik je voorwaarde niet omzetten in iets concreets.
Kun je “Als waarde van de cel A1A2 maarA3 maar A4 dan “label goud”” meer vormgeven?
Hallo allemaal,
Ik ben zoek naar een ALS formule die waardes in 2 verschillende kolommen met elkaar vergelijkt gecombineerd met een andere cel waar een cijferreeks staat als unieke identificatie voor een persoon. Cel A2 is de nummerindentificatie. In kolom D staat het weeknummer. In kolom G de datum en in kolom N de waarde enkel of retour. Ik wil op basis van indentificatienummer en datum de uiktomst krijgen als op dezelfde datum 2x enkel in kolom N voorkomt of 1x retour dat er dan ‘OK’ wordt gegeven. Als retour 2x opdezelfde datum voorkomt of in combinatie met enkel dan zou ik de uitkomst ‘DUBBEL’ willen zien. Weten jullie hoe ik deze vergelijking van data in de ALS formule kan maken?
Je kunt de functie ALS gebruiken in combinatie met de functies AANTAL.ALS en EN.
Hier is een voorbeeld:
=ALS(
OF(
EN(
AANTAL.ALS($G$2:$G$100; G2)=2;
AANTAL.ALS(
ALS($G$2:$G$100=G2; $N$2:$N$100=”enkel”);
G2
)=2
);
“DUBBEL”;
OF(
EN(
AANTAL.ALS($G$2:$G$100; G2)=2;
AANTAL.ALS(
ALS($G$2:$G$100=G2; $N$2:$N$100=”enkel”);
G2
)=1
);
“OK”;
“Anders”
)
)
)
We gebruiken de functie AANTAL.ALS om te controleren of er twee keer “enkel” voorkomt op dezelfde datum en of er één keer “retour” voorkomt op dezelfde datum.
Als een van deze voorwaarden waar is, geven we “OK” terug.
Als geen van de bovenstaande voorwaarden waar is, gebruiken we opnieuw de functie AANTAL.ALS om te controleren of er twee keer “retour” voorkomt op dezelfde datum of dat “retour” in combinatie met “enkel” voorkomt op dezelfde datum.
Als een van deze voorwaarden waar is, geven we “DUBBEL” terug.
Als geen van de bovenstaande voorwaarden waar is, geven we “Anders” terug, maar je kunt dit vervangen door een andere uitkomst.
Beste Joost, wil automatisch een prijs laten berekenen adhv een aantal velden.
Heb 4 velden met kortingspercentage (10 tot 20 10%/ 21-30 15% 30 tot 50 20% en 50> 25% korting.
Een veld met plaats opdruk, hiervan heb ik elders in het formulier een pulldown menu van gemaakt en in een kolom daarachter het bedrag en een veld met aantallen.
Als ik de plaats van opdruk selecteer en de aantallen bv :
voorzijde (geselecteerd uit pulldown = 5.25, aantallen 15 dan wil ik 15×5.25-10%. Heb gestoeid met de Als functie maar breek mijn hoofd hierover.
Ja, dit kan wel met behulp van de ALS-functie in combinatie met andere functies zoals VERT.ZOEKEN of INDEX en OVEREENKOMEN om de bijbehorende prijs op te halen op basis van de geselecteerde “plaats opdruk”.
Hier is een voorbeeld hoe het zou kunnen:
Stel dat je een lijst hebt met plaatsen voor opdruk en de bijbehorende prijzen in kolommen A en B, en je hebt de “plaats opdruk” in cel E2 en het aantal in cel F2. Dan kun je de volgende formule gebruiken om het bedrag te berekenen:
=ALS(E2=””; “”; INDEX(B:B; OVEREENKOMST(E2; A:A; 0)) * F2 * (1 – ALS(F2>=10; ALS(F2<=20; 0,1; ALS(F2<=30; 0,15; ALS(F2<=50; 0,2; 0,25))); 0)) Deze formule werkt zo: ALS(E2=""; ""; ...)` controleert of cel E2 leeg is. Als dat het geval is, blijft de uitvoer van de formule leeg, anders gaat de formule verder. OVEREENKOMST(E2; A:A; 0)` zoekt de geselecteerde plaats opdruk (in cel E2) in de lijst met plaatsen voor opdruk (in kolom A) en geeft het overeenkomstige rijnummer terug. INDEX(B:B; ...)` haalt het bijbehorende bedrag op uit kolom B op basis van het rijnummer dat is verkregen met OVEREENKOMST. F2 * ...` vermenigvuldigt het bedrag met het ingevoerde aantal in cel F2. De rest van de formule berekent de korting op basis van het ingevoerde aantal. Succes!
Wat ik zelf het gemakkelijkst vind is een Excel tabel maken waarin je 2 kolommen hebt. De eerste kolom noem je Aantal. De tweede kolom noem je Korting. In de kolom Aantal zet je deze waarden onder elkaar: 0, 10, 21, 30, 50. In de kolom Korting zet je deze waarden onder elkaar: 0% 10% 15% 20% 25%.
In een ander deel van je Excelbestand zet jet het bestelde product, de prijs per product, de aantallen van het bestelde product, etc. Stel dat in cel F2 een aantal staat van een besteld product. En stel dat je in cel G2 de korting wil tonen bij dit bestelde aantal. Dan zet je in G2 deze formule neer: =X.ZOEKEN(F2;Tabel1[Aantal];Tabel1[Korting];;-1)
De -1 op het eind in deze formule zorgt ervoor dat Excel naar het kleinste naastgelegen getal zoekt en daar het resultaat van neemt. Stel het bestelde aantal van een product is 12 dan is 10 het naastgelegen kleinste getal en de korting is dan 10%. Stel het bestelde aantal is 25 dan krijg je met deze formule 15% als resultaat.
Als je niet weet hoe het maken van een Excel tabel gaat, zet dan in cellen A2 tot en met A6 deze waarden onder elkaar: 0, 10, 21, 30, 50. En zet in cellen B2 tot en met B6 deze percentages onder elkaar: 0% 10% 15% 20% 25%.
Aangenomen dat ook nu het bestelde aantal van een product in cel F2 staat en je de korting in G2 wil tonen wordt in dit geval de formule voor het tonen van de korting op deze manier ingevuld: =X.ZOEKEN(F2;$A$2:$A$6;$B$2:$B$6;;-1)
0
0
0
0
Dag Joost,
Ik heb een database met klant met een welbepaalde join date. Nu hebben deze klanten elke jaar een verplicht onderhoud nodig. Ik ben wat aan het proberen met voorwaardelijke opmaak maar het lukt me niet.
Ik heb 2 kolommen met ‘install date’ en ‘Maintenance’. Ik had graag gehad dat de cell in kollom ‘Maintenance’ oranje kleurt 2 maand voor er 1jaar verlopen is (vb install 20/04/2023, zou cell op 20/02/2024 oranje moeten kleuren. op 21/04/2024 zou deze rood moeten kleuren.
Is dit makkelijk in te stellen?
Alvast bedankt voor uw reactie!
Ja, dat is wel mogelijk om dit in te stellen met voorwaardelijke opmaak in Excel.
Je kunt onderstaande eens proberen.
– Een regel die de cel in kolom “Maintenance” oranje kleurt wanneer de huidige datum tussen 10 en 12 maanden na de installatiedatum ligt.
– Een regel die de cel in kolom “Maintenance” rood kleurt wanneer de huidige datum 12 maanden of meer na de installatiedatum ligt.
– Open je Excel-bestand en selecteer de kolom “Maintenance” waarin je de voorwaardelijke opmaak wilt toepassen.
– Ga naar de tab “Home” en klik op “Conditional Formatting” in het lint.
– Kies “New Rule…” uit het vervolgkeuzemenu.
– In het venster “New Formatting Rule” selecteer je “Use a formula to determine which cells to format”.
Voor oranje -> 2 maanden voor 1 jaar is verstreken:
– Voer de volgende formule in: =AND(TODAY() >= EDATE(A1, 10), TODAY() < EDATE(A1, 12)) - Hier is `A1` de cel met de installatiedatum. Pas dit aan op basis van je specifieke kolom. - Klik op "Format..." en stel de opmaak in die je wilt gebruiken (bijv. een oranje vulkleur). - Klik op "OK" om de voorwaardelijke opmaakregel toe te voegen. - Voor rood -> na 1 jaar:
– Voeg nog een nieuwe regel toe door opnieuw te klikken op “Conditional Formatting” > “New Rule…” > “Use a formula to determine which cells to format”.
– Voer de volgende formule in: =TODAY() >= EDATE(A1, 12)
– Weer, `A1` is de cel met de installatiedatum. Pas dit aan op basis van je specifieke kolom.
– Klik op “Format…” en stel de opmaak in die je wilt gebruiken (bijv. een rode vulkleur).
– Klik op “OK” om de voorwaardelijke opmaakregel toe te voegen.
– Klik op “OK” in het “New Formatting Rule” venster om de regels op de geselecteerde cellen toe te passen.
Dag allemaal,
Ook ik heb een vraag/probleem.
Ik heb een sheet met daarin 50 regels met diverse facturen van diverse leveranciers
(1 regel per factuur)
Deze facturen zijn gespreid over 12 maanden.
Ik moet nu per maand een overzicht krijgen maken met de maandelijkse kosten.
Ik heb een drop down menu met daarin alle maanden van dit jaar en komend jaar.
En in de sheet (regel 9) heb ik (horizontaal) 12 kolommen met daarin de maanden mei t/m april.
In ieder kolom staan diversen bedragen
Nu heb ik dus een formule nodig dat ik (via het drop down menu) bijvoorbeeld Mei selecteer en dat er dan gezocht wordt in de kolom van mei, en dat bedrag per factuur regel wordt vermeld.
Dit is te bereiken m.b.v. de functies `SOMMEN.ALS` en `VERT.ZOEKEN`.
Hier hoe je dit bijvoorbeeld kun doen:
1. Een tabel met je facturen en bedragen per maand:
| Factuur ID | Leverancier | Mei | Juni | Juli | … | April |
|————|————-|—–|——|——|—–|——-|
| 1 | Leverancier A | 100 | 150 | 200 | … | 300 |
| 2 | Leverancier B | 200 | 250 | 300 | … | 400 |
| … | … | … | … | … | … | … |
| 50 | Leverancier N | 300 | 350 | 400 | … | 500 |
2. Maak een dropdown menu voor de maanden in bijvoorbeeld cel A1.
– De beschikbare maanden zijn in een reeks (bijvoorbeeld L1:L12).
3. Gebruik de `SOMPRODUCT` en `ALS` functies om de totale maandelijkse kosten te berekenen:
Stel dat de kolomkoppen (maanden) zich bevinden in rij 9 en je gegevens beginnen vanaf rij 10.
Je kunt de volgende formule gebruiken om de totale maandelijkse kosten voor de geselecteerde maand te berekenen:
=SOMPRODUCT((B10:B59 = A1) * (C9:N9 = A1) * C10:N59)
Uitgangspunten:
– B10:B59 bevat de namen van de maanden die overeenkomen met je dropdown menu in A1.
– C9:N9 bevat de kolomkoppen (maanden mei t/m april).
– C10:N59 bevat de bedragen per factuur per maand.
4. Gebruik de `INDEX` en `MATCH` functies om het bedrag per factuurregel op te halen:
Als je bijvoorbeeld het bedrag per factuurregel wilt vermelden op basis van de geselecteerde maand, kun je de volgende formule gebruiken in de kolom waar je het bedrag wilt weergeven (stel dat je deze in kolom O wilt hebben):
=INDEX(C10:N59, ROW()-9, MATCH(A1, C9:N9, 0))
Uitgangspunten:
– `C10:N59` is het bereik met de bedragen per factuur per maand.
– `ROW()-9` geeft de rijindex voor de huidige rij. Pas deze aan afhankelijk van de rij waar je begint.
– `MATCH(A1, C9:N9, 0)` geeft de kolomindex voor de geselecteerde maand.
Met deze opzet krijg je een dynamische oplossing die de maandelijkse kosten berekent op basis van de geselecteerde maand in het dropdown menu en de bedragen per factuurregel weergeeft.
Hopelijk kun je hier mee verder!
Ik kan hier zeker mee verder! Dank je wel Joost.
Hi Joost,
Als vervolg hierop even een vervolg vraag.
Ik heb nu een berekening wat er per maand wordt vastgesteld.
Maar nu wil ik nog een berekening dat ik laat zien wat er nog aan saldi resteert.
Maar omdat de maand natuurlijk maandelijks wijzigt krijg ik het niet voor elkaar om het totaal van de resterende maanden te krijgen.
Als voorbeeld:
I7 zit een drop down menu met de maanden
Laten we zeggen Mei-24
Op regel 12 heb ik staan in:
Kolom D het volledige bedrag 1830
In kolom I de formule =SOMPRODUCT((Blad2!D:D=$I$7)*($K$10:$Z$10=$I$7)*(K15:Z15))
Kolom L t/m W heb ik in iedere maand staan: 152.50
L t/m O is Jan t/ m Apr
P = Mei
Q t/m W is Jun t/m Dec
In kolom J12 heb ik dan dus een formule nodig van het totaal van Q t/m W.
Maar omdat I7 iedere maand wijzigt, schuift dit natuurlijk iedere maand op.
Hier kom ik dus niet uit.
Om het resterende saldo dynamisch te berekenen op basis van de geselecteerde maand in je dropdown menu, kunnen je gebruik maken van een combinatie van Excel-functies zoals `SOM`, `INDEX`, `MATCH`, en `INDIRECT`.
De formule moet dus bepalen vanaf welke kolom de berekening moet beginnen en vervolgens de som van alle bedragen in die kolommen berekenen.
Dit kan bijvoorbeeld zo:
Identificeer de maand en bepaal de kolom
Laten we aannemen dat je de maand selecteert in cel `I7` op Blad1 en dat de kolommen met de maanden zich uitstrekken van L t/m W (Januari t/m December).
Creëer de formule om de som van resterende maanden te berekenen
Gebruik de volgende formule in cel `J12` om het resterende bedrag vanaf de geselecteerde maand te berekenen:
=SOM(INDIRECT(“Blad1!” & ADRES(12, MATCH($I$7, $L$10:$W$10, 0) + KOLommen(L:L) – 1) & “:” & ADRES(12, KOLommen(W:W))))
Hier is een uitleg van de formule:
MATCH($I$7, $L$10:$W$10, 0)
Zoekt de geselecteerde maand (in cel I7) in de rij met de maandnamen (L10:W10) en retourneert de relatieve positie.
KOLommen(L:L) – 1
Retourneert het kolomnummer van de kolom “L” minus 1, zodat de som begint vanaf de juiste kolom.
ADRES(12, MATCH($I$7, $L$10:$W$10, 0) + KOLommen(L:L) – 1)
Geeft het celadres in rij 12 en de kolom die overeenkomt met de geselecteerde maand.
ADRES(12, KOLommen(W:W))
Geeft het celadres van rij 12 en de kolom “W”.
INDIRECT(“Blad1!” & … & “:” & …)
Construeert een bereik van de cellen van de kolom die overeenkomt met de geselecteerde maand tot kolom “W”.
SOM(INDIRECT(…))
Bereken de som van de waarden in het geconstrueerde bereik.
Voeg de formule toe in kolom J voor alle rijen
Hoprlijk kom je hier verder mee.
Hoi,
Ik zit met een vraagje/probleem.
Ik wil in een lijst de inhoud van een kolom vergelijken met een andere kolom om dan in een derde kolom de inhoud van een vierde kolom te zetten als de inhoud van van E=C.
Dus als E1=C1 dan wil ik dat inhoud van K1 in M1 komt. Terwijl E en M op blad1 staan en C en K op blad2. En dit voor de volledige kolom E.
Het komt erop neer dat uiteindelijk de code in E moet vervangen worden door een nieuwe code, namelijk die in K.
E M C K
U1 A-001-001 U1 A-001-001
U1 A-001-001 U2 A-001-002
U1 A-001-001 U3 A-001-003
U2 A-001-002 U4 A-001-004
U2 A-001-002 U5 A-001-005
U3 A-001-003 U6 A-001-006
Ik hoop dat het een beetje duidelijk is 😉
Kan dit? Alvast bedankt.
Dit kan wel met bijvoorbeeld de functies `ALS`, `VERT.ZOEKEN` en `ISFOUT` te gebruiken om de waarden in de kolommen te vergelijken en de gewenste waarde uit de vierde kolom K te plaatsen in de tweede kolom M.
Stel dat je de volgende structuur hebt:
– Blad1:
– Kolom E: bevat de codes die je wilt vergelijken
– Kolom M: hier wil je de nieuwe code plaatsen
– Blad2:
– Kolom C: bevat de codes waarmee je wilt vergelijken
– Kolom K: bevat de nieuwe codes die je wilt overnemen
Je hebt de volgende gegevensindeling:
Blad1:
– Kolom E: vanaf E1
– Kolom M: vanaf M1
Blad2:
– Kolom C: vanaf C1
– Kolom K: vanaf K1
Gebruik VERT.ZOEKEN in Blad1 om de nieuwe code te vinden
Voeg de volgende formule toe in cel M1 op Blad1:
=ALS(ISFOUT(VERT.ZOEKEN(E1, Blad2!C:K, 2, ONWAAR)), “”, VERT.ZOEKEN(E1, Blad2!C:K, 2, ONWAAR))
Deze formule werkt als volgt:
1. `VERT.ZOEKEN(E1, Blad2!C:K, 2, ONWAAR)` zoekt de waarde van E1 in kolom C op Blad2 en retourneert de overeenkomstige waarde uit kolom K.
2. `ISFOUT` controleert of de `VERT.ZOEKEN`-functie een fout retourneert (bijvoorbeeld als de waarde niet gevonden wordt).
3. `ALS(ISFOUT(…), “”, …)` geeft een lege string terug als de waarde niet wordt gevonden, anders geeft het de gevonden waarde terug.
Kopieer de formule in cel M1 naar de rest van de kolom M om alle rijen te vullen die je hebt in kolom E. Hierdoor wordt elke waarde in kolom E vergeleken met de overeenkomstige waarde in kolom C op Blad2 en wordt de bijbehorende nieuwe code in kolom K in kolom M geplaatst.
Wauw, bedankt voor de vlugge respons, ik ga dit eens direct proberen :-). Nogmaals bedankt!!
Hallo, Ik heb een excel gemaakt met ons klantenbestand. Nu is het zo dat indien er in kolom X “GAS” vermeld staat, dan moet in kolom Y de ingevulde datum + 2 jaar gedaan worden. Indien in kolom X “STOOKOLIE” staat, dan moet de datum in kolom Y + 1 jaar gedaan worden. Iemand die me even op weg kan helpen hiermee aub? Ik weet niet hoe ik de formule moet formuleren om op de datum 12 of 24 maanden bij te laten tellen…. ALvast bedankt!
Je kunt gebruik maken van de functie `DATUM.VERPLAATSEN` (in het Engels `EDATE`) om maanden bij een datum op te tellen.
Bijboorbeeld als volgt:
=DATUM.VERPLAATSEN(startdatum, aantal_maanden)
Om dit toe te passen in jouw geval, waar de toevoeging van maanden afhangt van de waarde in kolom X, kun je gebruik maken van de `ALS` (in het Engels `IF`) functie in combinatie met `DATUM.VERPLAATSEN`.
Hier is de formule die je in kolom Y kunt plaatsen:
=ALS(X2=”GAS”; DATUM.VERPLAATSEN(Y2; 24); ALS(X2=”STOOKOLIE”; DATUM.VERPLAATSEN(Y2; 12); “”))
Uitleg:
`X2` verwijst naar de cel in kolom X die aangeeft of het “GAS” of “STOOKOLIE” is.
`Y2` verwijst naar de datum in kolom Y.
`24` staat voor het toevoegen van 24 maanden (2 jaar) voor “GAS”.
`12` staat voor het toevoegen van 12 maanden (1 jaar) voor “STOOKOLIE”.
Sleep de formule naar beneden om deze toe te passen op alle relevante cellen in kolom Y.
Denk dat je hier wel mee verder kan, succes!
Beste strohalm (vriendelijk bedoeld).
Ik heb in kolom A variabele getallen staan.
Die zouden in kolom B terecht moeten komen met indien groter dan 10 met het getal 10.
Echter indien kleiner dan 10 het met eigenlijke getal
Heb me knettergek gezocht, doch het lukt me niet…
Met groet, Dirk
Dat kan, je wilt de waarden in kolom A evalueren en vervolgens de waarden in kolom B plaatsen, afhankelijk van of ze groter of kleiner dan 10 zijn.
Je kunt dit doen met behulp van de `IF`-functie in Excel.
Hieronder hoe dit te doen:
1. Selecteer de cel in kolom B waar je de bewerkte waarde wilt weergeven. Dit zou normaal gesproken cel B1 zijn als je begint op rij 1.
2. Voer de volgende formule in:
=IF(A1 > 10, 10, A1)
3. Kopieer deze formule naar beneden in kolom B voor alle rijen die je hebt in kolom A.
Deze formule werkt als volgt:
– `IF(A1 > 10, 10, A1)` controleert of de waarde in cel A1 groter is dan 10.
– Als dat waar is (`TRUE`), dan zet het de waarde 10 in de cel.
– Als dat niet waar is (`FALSE`), dan zet het de originele waarde van A1 in de cel.
Succes!
Hoi Joost, wellicht dat je mij ook kan helpen? In de cellen T voeren wij het aantal dagen in dat de student zijn stage komt lopen. Het aantal snipperdagen waar de student recht op heeft staan in de cellen X. Ik zou excel graag zelf het aantal willen laten invullen in de cellen X. Als de student minder dan 15 dagen stage loopt, dan krijgt hij 0 snipperdagen. Bij 30 of meer snipperdagen heeft hij recht op 1 snipperdag, bij 45 of meer dagen heeft hij recht op 3 dagen. Hoe kan ik dit het beste invoeren? Alvast dank!
oeps, ik zie dat ik met typen iets niet goed heb gedaan. Excuus. Bij 30 of meer snipperdagen heeft hij recht op 1 snipperdag, bij 45 of meer dagen heeft hij recht op 2 dagen en bij 60 of meer dagen heeft hij recht op 3 snipperdagen.
Hallo Joost, wellicht dat je mij ook kan helpen? In de tabellen T staat bij ons het aantal dagen dat de student bij ons stage komt lopen. In de cellen X staat het aantal snipperdagen waar de student recht op heeft. Graag laat ik excel zelf het aantal bij de cellen X invullen. Hoe doe ik dat? Bij minder dan 30 dagen heeft de student recht op 0 snipperdagen. Bij 30 of meer snipperdagen heeft hij recht op 1 snipperdag, bij 45 of meer dagen heeft hij recht op 2 dagen en bij 60 of meer dagen heeft hij recht op 3 snipperdagen. Ik hoor het graag, alvast dank!
Hallo Dominique,
Stel dat het aantal stage-dagen van een student in cel T2 staat, en je wilt het resultaat in cel X2 plaatsen.
Dan kun je de volgende formule in cel X2 invoeren:
=ALS(T2<30; 0; ALS(T2>=60; 3; ALS(T2>=45; 2; 1)))
ALS(T2<30; 0; – Als het aantal dagen in T2 minder dan 30 is, vul dan 0 snipperdagen in.
ALS(T2>=60; 3; – Als het aantal dagen 60 of meer is, vul dan 3 snipperdagen in.
ALS(T2>=45; 2; – Als het aantal dagen 45 of meer is (maar minder dan 60), vul dan 2 snipperdagen in.
1 – Als het aantal dagen 30 of meer is (maar minder dan 45), vul dan 1 snipperdag in.
In nieuwere versies van Excel kun je gebruik maken van de IFS-functie:
=IFS(T2<30; 0; T2>=60; 3; T2>=45; 2; T2>=30; 1)
Hopelijk ben je hiermee geholpen
Wauw! Wat geweldig! Zo ontzettend gaaf! Dank! Het maakt mijn werk een stuk leuker zo, echt tof!
Goedendag,
ik zit te stoeien met bepaalde functies maar ik krijg het niet voor elkaar. Ik heb een opdrachten overzicht (in tablat Opdrachten) met in kolom Opdrachten:D de artikel nummers en in kolom Opdrachten:E de bestelde aantallen.
Dan wil ik in mijn voorraadbeheer dat de aantallen automatisch gaan aanpassen bij het juiste artikelnummer.
Bijvoorbeeld ik heb (in tablat Overzichten) aantal 20 staan in kolom Overzichten:E, in rij 2 staat het artikelnummer 1 (kolom A).
Dan wil ik zodra ik een order heb het aantal in mijn Opdrachten tablat invullen met het bijbehorende artikelnummer en dat het automatisch het aantal aanpast aan mijn voorraad.
Dus stel iemand besteld 1stuk dan wordt het in tablat Overzichten mijn artikelnummer 1 19stuks.
Is er iemand die hier de juiste formule voor heeft?
Mijn dank is groot
Beste Lieke,
Om je voorraad automatisch aan te passen op basis van de bestellingen in je “Opdrachten” tabblad, kun je de SOM.ALS functie gebruiken in combinatie met een formule om de voorraad te laten afnemen op basis van de bestelde aantallen.
1. Tabblad “Opdrachten” (Orders):
– In kolom D staan de artikelnummers.
– In kolom E staan de bestelde aantallen.
2. Tabblad “Overzichten” (Voorraad):
– In kolom A staan de artikelnummers.
– In kolom E staan de huidige aantallen (de voorraad).
Formule voor het voorraadbeheer in het “Overzichten” tabblad:
In kolom E van het “Overzichten” tabblad wil je de voorraad laten verminderen op basis van de bestelde aantallen in het “Opdrachten” tabblad.
Gebruik hiervoor de volgende formule in de kolom waar je de voorraad wilt bijhouden (bijvoorbeeld in cel `E2`, als je voorraad bij artikelnummer 1 in `A2` staat):
=A2 – SOM.ALS(Opdrachten!D:D;A2;Opdrachten!E:E)
Uitleg:
– A2: Dit verwijst naar het artikelnummer in het “Overzichten” tabblad. Dit getal wordt vergeleken met de artikelnummers in het “Opdrachten” tabblad.
– SOM.ALS(Opdrachten!D:D; A2; Opdrachten!E:E): Dit deel telt de bestelde aantallen in het “Opdrachten” tabblad.
De functie SOM.ALS zoekt alle bestellingen van het artikelnummer in kolom `D` van het “Opdrachten” tabblad (dat overeenkomt met het artikelnummer in `A2`), en telt de hoeveelheden uit kolom `E` van “Opdrachten” bij elkaar op.
Dus, wat deze formule doet, is het huidige voorraadniveau verminderen met de totale bestellingen van het betreffende artikelnummer.
Voorbeeld:
– Stel, in “Overzichten” staat in `A2` artikelnummer 1, en de huidige voorraad is 20 (staat in `E2`).
– In “Opdrachten” staan de volgende gegevens:
– `D2`: Artikelnummer 1, `E2`: 1 besteld.
– `D3`: Artikelnummer 2, `E3`: 3 besteld.
– `D4`: Artikelnummer 1, `E4`: 2 besteld.
Als je de formule in `E2` van het “Overzichten” tabblad invoert, zal de formule de som van de bestellingen van artikelnummer 1 (1 stuk in `E2` en 2 stuks in `E4`) aftrekken van de oorspronkelijke voorraad:
=20 – (1 + 2) = 17
De voorraad van artikelnummer 1 wordt dan bijgewerkt naar 17 stuks.
Als je dit voor meerdere artikelen wilt doen, kun je de formule naar beneden slepen, zodat elk artikelnummer automatisch wordt vergeleken met de bestellingen.
Hopelijk ben je hiermee geholpen.
Goedemiddag Joost,
Ik hoop dat je mij ook kunt helpen. Ik heb de vorige post’s als nagelopen en een aantal formules proberen te verbouwen naar wat ik wil, maar ik krijg óf foutmeldingen, óf het doet niet wat ik graag wil…
Het gaat om het volgende:
Ik heb op blad1 in kolom C adressen staan en in kolom G startdata start werkzaamheden.
op blad 2 (en volgend) staat een cel voor het adres (met gegevensvalidatie van blad 1 adressen) en daaronder een cel voor de startdatum. Nu zou ik graag de bij het adres passende startdatum willen automatiseren wanneer ik een adres selecteer.
Kan dat?
Ik wil dit niet middel geneste tabellen toepassen, omdat dat best bewerkelijk is en ook voor meer dan 64 adressen moet werken. (ik heb die wel in het formulier zitten op ander posities)
Ja, je kunt de VERT.ZOEKEN (VLOOKUP) of X.ZOEKEN (XLOOKUP) functie gebruiken om de bijbehorende startdatum automatisch op te halen zodra je een adres selecteert.
Hier zijn twee manieren om dit op te lossen:
Oplossing met VERT.ZOEKEN (VLOOKUP)
Als je Excel gebruikt zonder X.ZOEKEN, kun je de volgende formule in de cel zetten waar de startdatum automatisch moet komen:
=VERT.ZOEKEN(A1, Blad1!C:G, 5, ONWAAR
– A1 is de cel waar het adres wordt geselecteerd op Blad2.
– Blad1!C:G is het bereik waarin de adressen en startdata staan.
– 5 betekent dat we de waarde uit de 5e kolom van het bereik willen ophalen (kolom G in Blad1).
– ONWAAR zorgt ervoor dat alleen exacte overeenkomsten worden gevonden.
Oplossing met X.ZOEKEN (XLOOKUP) (alleen in nieuwe Excel-versies)
Als je Excel365 of een recente versie gebruikt, is X.ZOEKEN beter omdat het flexibeler en sneller werkt:
=X.ZOEKEN(A1, Blad1!C:C, Blad1!G:G, “Niet gevonden”)
– A1 is weer de cel met het adres.
– Blad1!C:C zoekt naar het adres in kolom C op Blad1.
– Blad1!G:G geeft de bijbehorende startdatum uit kolom G.
– “Niet gevonden” voorkomt een foutmelding als het adres niet bestaat.
Zorg ervoor dat de gegevensvalidatie op de adrescel correct werkt,
zodat alleen bestaande adressen gekozen kunnen worden.
Dan voorkom je fouten bij het zoeken.