Zijn er Excel specialisten in de zaal?

Gewoon even offtopic praten over van alles en nog wat.

Moderator: Moderators

Plaats reactie
Gebruikersavatar
WillyH
Donateur
Berichten: 658
Lid geworden op: 22 dec 2018 09:35
Locatie: Zuiderkempen (B)

Zijn er Excel specialisten in de zaal?

Bericht door WillyH »

Ik breek me het hoofd over een Excel puzzel waar ik al de ganse dag zit op de staren. Helaas is mijn Excel kennis ook eerder basic te noemen. Als ik even mag omschrijven:
Er zijn twee werkbladen, BASIS en EXTRA genoemd. In BASIS een overzicht van automerken en een aantal kenmerken waaronder type aandrijving: 4-wiel, 2-wiel en elektrisch (Kolom F). Voor bepaalde acties wordt ook in een aparte kolom aangegeven in welke maand deze voorgesteld worden. In kolom A zijn de de gegevens MERKNAAM en MAAND al samengevoegd voor verdere verwerking (idem als kolom K in werkblad EXTRA). Er is ook een kolom die de actie omschrijft (kolom H).
Nu is er in het werkblad EXTRA een overzicht van de acties per maand nodig, opgelijst per merk.
Tot daar geen probleem, de lijst wordt netjes weergegeven per merk/maand. Echter, er zijn ook acties die enkel betrekking hebben op enkel de 4-wielaandrijving, of enkel de elektrische aandrijving.
Ik zou dus willen vergelijken op 2 gegevens/kolommen tussen 2 werkbladen. Dat krijg ik niet werkende.
Deze formule in het werkblad EXTRA zou het moeten doen maar geeft telkens een foutmelding:
=INDEX(BASIS!$H$2:$H$250;VERGELIJKEN(B4&K4,BASIS!$F$2:$F$250&BASIS!$A$2:$A$250,0))
Kolom B staat voor de maandnaam waarin een actie mogelijk is. Iemand een idee wat er mogelijk fout is, of eventueel een andere aanpak?
Alvast bedankt!
hfjbuis
Donateur
Berichten: 3057
Lid geworden op: 13 feb 2017 00:26
Locatie: Hoensbroek

Re: Zijn er Excel specialisten in de zaal?

Bericht door hfjbuis »

Ik gebruik deze functie zelf niet!

Uiteraard zou de foutmelding helpen :mrgreen:

Een mogelijke oplossing is:
- het gebruik van de wizzard, die kan je op weg helpen
- Je hebt een functie (Index) met een parameter (Vergelijken). Probeer de functie vergelijken apart uit en zet het resultaat in een kolom.
- Als het resultaat klopt, gebruik dan deze kolom als parameter voor de functie index
- Als dit werkt, zou je e.e.a. kunnen samenvoegen, maar dat zou ik zelf achterwegen laten mocht er in de toekomst weer een probleem optreden
- beginnen met de eenvoudigste vergelijking en als je die werkend hebt, uitbreiden naar meer kolommen/bladen/velden
There are only 2 limits, the sky and your imagination
Gebruikersavatar
WillyH
Donateur
Berichten: 658
Lid geworden op: 22 dec 2018 09:35
Locatie: Zuiderkempen (B)

Re: Zijn er Excel specialisten in de zaal?

Bericht door WillyH »

Bedankt voor de tips! De foutmelding zegt dat de formule niet correct is, zeg maar een of meerdere schrijffouten. Het Internet staat vol interessante voorbeelden maar helaas vaak ook met verkeerd geschreven formules. Een punt, dubbelpunt, puntkomma of een komma, het maakt een wereld van verschil in Excel.
Ondertussen (vermoedelijk) wel de juiste schrijfwijze van de formule te pakken:
=INDEX(RASTER!$O$2:$O$250;VERGELIJKEN(B4&K4; RASTER!$F$2:$F$250&RASTER!$A$2:$A$250;0))
Maar helaas nog steeds niet het verhoopte resultaat. Ik ga verder de zaak ontmantelen en zoals je voorstelt stap voor stap de formule trachten uit te breiden.
Gebruikersavatar
hainjedaf
Donateur
Berichten: 2424
Lid geworden op: 01 aug 2011 10:01
Locatie: Amsterdam
Contacteer:

Re: Zijn er Excel specialisten in de zaal?

Bericht door hainjedaf »

Waarom maak je je formule zo complex?

Ik gebruik soms 20-30 cellen die ik later verberg.
In die cellen laat ik elke stap van de formule individueel uitvoeren.
Zo kan ik voor elke stap controleren of ik het gewenste resultaat krijg.
Maakt fouten opsporen zoveel makkelijker.
Dit kan ook op een apart excel formulier in hetzelfde excel bestand.


Dat resultaat roep ik op in het mooie leesbare deel van mijn excelsheet. (Pretty printing)

(Oh ja en om je intellectuele rechten te beschermen, kan je de
formule cellen na verbergen, ook nog beschermen met een wachtwoord.)
Met vriendelijke groet,
Marout Sluijter-Borms,
Amsterdam

Prusa Mini met OctoPrint
Sparren en tinkeren met iedereen en over alles wat techniek is.
Gebruikersavatar
WillyH
Donateur
Berichten: 658
Lid geworden op: 22 dec 2018 09:35
Locatie: Zuiderkempen (B)

Re: Zijn er Excel specialisten in de zaal?

Bericht door WillyH »

Ja, ofwel maak je de formule complex, ofwel maak je het hele rekenblad complex :P

Nu, de formule werk wel (gedeeltelijk) met onderstaande formule:
Om de NB foutmelding te vermijden
=ALS.FOUT(INDEX(RASTER!$O$2:$O$250;VERGELIJKEN(B4&K4; RASTER!$F$2:$F$250&RASTER!$A$2:$A$250;0));"")

MAAR helaas geeft die oplossing maar de eerste correcte verwijzing weer, terwijl er meerdere correcte verwijzingen zijn. Terug naar het tekenbord :oops:
Gebruikersavatar
hainjedaf
Donateur
Berichten: 2424
Lid geworden op: 01 aug 2011 10:01
Locatie: Amsterdam
Contacteer:

Re: Zijn er Excel specialisten in de zaal?

Bericht door hainjedaf »

WillyH schreef: 08 okt 2021 11:04 Ja, ofwel maak je de formule complex, ofwel maak je het hele rekenblad complex :P

Nu, de formule werk wel (gedeeltelijk) met onderstaande formule:
Om de NB foutmelding te vermijden
=ALS.FOUT(INDEX(RASTER!$O$2:$O$250;VERGELIJKEN(B4&K4; RASTER!$F$2:$F$250&RASTER!$A$2:$A$250;0));"")

MAAR helaas geeft die oplossing maar de eerste correcte verwijzing weer, terwijl er meerdere correcte verwijzingen zijn. Terug naar het tekenbord :oops:
Daaarom maak ik het rekenblad complex. Dan kan ik eerst zien of elk deel van de formule een resultaat geeft.
Die filteren en uiteindelijk terug transporteren....

Mijn jaarlijkse dienstrooster is meen ik 75 kolommen breed waarvan 8 zichtbaar :)
Maar na het invoeren van mijn omloopje, roosterplek en de begindatum, rekent hij
feilloos uit wat ik de komende 450 dagen aan het doen ben :)
Met vriendelijke groet,
Marout Sluijter-Borms,
Amsterdam

Prusa Mini met OctoPrint
Sparren en tinkeren met iedereen en over alles wat techniek is.
Gebruikersavatar
WillyH
Donateur
Berichten: 658
Lid geworden op: 22 dec 2018 09:35
Locatie: Zuiderkempen (B)

Re: Zijn er Excel specialisten in de zaal?

Bericht door WillyH »

Hola, Marout :shock: 75 kolommen breed waarvan 8 zichtbaar! Zolang jij nog kan volgen, prima!
Eén van de problemen die ik ondervind is dat het om twee rekenbladen gaat waarvan het eerste puur data is waar je enkel kan in opzoeken, geen aanpassingen mag/kan doen.
Nu, in principe zou het vrij eenvoudig moeten zijn: zoek in het BASIS rekenblad een bepaalde inhoud van een cel uit rekenblad EXTRA, controleer of de tweede criteria (in dezelfde rij) overeenkomt met deze in het EXTRA rekenblad en geef dan de waarde van een bepaalde cel (in dezelfde rij) weer.

Enfin, dankzij jouw en Hfjbuis's tips ben ik ook een andere weg ingeslagen en zo kunnen vaststellen (via formule controleren) dat er bepaalde gegevens in het EXTRA werkblad onvolledig zijn. Stap voor stap verder!
Gebruikersavatar
hainjedaf
Donateur
Berichten: 2424
Lid geworden op: 01 aug 2011 10:01
Locatie: Amsterdam
Contacteer:

Re: Zijn er Excel specialisten in de zaal?

Bericht door hainjedaf »

WillyH schreef: 10 okt 2021 12:07 Hola, Marout :shock: 75 kolommen breed waarvan 8 zichtbaar! Zolang jij nog kan volgen, prima!

Enfin, dankzij jouw en Hfjbuis's tips ben ik ook een andere weg ingeslagen en zo kunnen vaststellen (via formule controleren) dat er bepaalde gegevens in het EXTRA werkblad onvolledig zijn. Stap voor stap verder!
Die onzichtbare cellen kan je weer zichtbaar maken als nodig is.

als ik mijn eigen dienstrooster XLSX erbij pak:
Als mijn rooster wijzigt, pas ik de formules aan die per weekdag bepalen wat ik doe.
Dat kan zijn een 2 weekse omloop dus 14 weekdagen diensten/vrij maar kan ook een 6 weekse omloop zijn van 42 weekdagen.
(elke kolom kijkt of er in de kop een vrije dag of een roosterdienst is bepaald)
Tijdelijk maak ik de verborgen cellen zichtbaar zodat ik in de formules kan roeren

De formule om het rooster over een week uit te smeren, is even groot als het aantal weekdagen in een omloop.
De formules om uit te zoeken wat moet worden weergegeven veranderen alleen in dat ze niet in 14 maar in 42 cellen moeten zoeken.
De formules om deze zaken te doen, veranderen niet;
-weekeinddagen een kleurtje te geven
-weekeinddagen te bepalen
-dagnaam te bepalen
-dagnaam (3), dagnummer, maandnaam (3), jaartal (4) in 1 teksteveld

Als ik het hele verhaal draaiend heb, kan ik formuleregel 2 tot en met oneindig, gewoon kopiëren van regel 2.
Regel 2 en volgende gebruiken datum vorige regel+1 als input en de rest is daarvan afhankelijk.
Regel 1 wijkt af omdat die de inputgegevens verwerkt uit de pagina kop (aanvangsdatum, roosterplek) verwerkt.

Maar zodra ik dit heb aangepast, wil ik niet meer zien wat excel doet in mijn formules (die in het zelfde werkblad staan).
Dus die kolommen verberg ik.
Tussenvoegen van extra kolommen, vb om geplande vakanties of activiteiten in te voeren, hebben geen effect omdat
de formules allemaal met vaste verwijzingen werken ($ teken voor de kolomletter of hetrijcijfer).

En ik definieer alleen het mooi opgemaakte deel als 'afdrukbereik' mocht ik een keer een papieren versie nodig hebben.

En ja, ongetwijfeld bevatten mijn 75+ kolommen berekeningen die ook met een excelformule hadden gekund.
Ik ben geen excel god maar dit werkt voor mij. En ik begrijp het wanneer het aangepast moet worden.
Qua verwerkingssnelheid zel het met de moderne PC geen fluit uitmaken. Onder windows staan 8 of 9 van de 12 cores toch in
een 'do nothing' loop. :lol:
Met vriendelijke groet,
Marout Sluijter-Borms,
Amsterdam

Prusa Mini met OctoPrint
Sparren en tinkeren met iedereen en over alles wat techniek is.
eigen_gebruik
Berichten: 46
Lid geworden op: 02 nov 2014 07:24

Re: Zijn er Excel specialisten in de zaal?

Bericht door eigen_gebruik »

Een 1e tip die ik je kan meegeven is om eens te kijken of je kunt werken met tabellen. (Engelse versie Excel : "Format as table")
Dit maakt het lezen van complexe formules een stuk makkelijker, omdat je dan per tabel met unieke (kolom) namen gaat werken en Excel het e.e.a. automatisch voor je uitdoktert.

Uitgaande van jouw 1e voorbeeld :

=INDEX(BASIS!$H$2:$H$250;VERGELIJKEN(B4&K4,BASIS!$F$2:$F$250&BASIS!$A$2:$A$250,0))

Geformateerd als tabel hebben we als kolomnaam dan bijv.

Werkblad - BASIS
A : Merknaam en Maand
B : Actie en Maand
F : Kenmerken
H : Actie Omschrijving

Werkblad - EXTRA
K : Merknaam en Maand

Dit word dan

=INDEX(BASIS!Table1[Actie Omschrijving];VERGELIJKEN(B4&K4,BASIS!Table1[Kenmerken]&BASIS!Table1[Merknaam en Maand],0))

M.i. een stuk makkelijker leesbaar.
Groot voordeel van het werken met tabellen is dat je "onbeperkt" rijen kunt toevoegen of verwijderen.
Uitgaande van jouw voorbeeld : Als je van 250 naar 500 (of van 250 naar 100) rijen zou gaan dien je de formule handmatig aan te passen ($A250 moet $A500 worden of $A250 moet $A100 worden).
Indien je met tabellen werkt is dit niet meer nodig (Excel doktert het allemaal voor je uit). En kan fouten in de toekomst dus voorkomen.

Nu terugkomende op je echte vraag.
In het verleden ook flink gespeeld met INDEX + MATCH (INDEX + VERGELIJKEN :oops: ) en een site die het mij goed uitlegde was de volgende : https://exceljet.net/index-and-match
Een andere site die de functies apart goed uitlegde was : https://www.techonthenet.com/excel/formulas/index.php
(vandaar ook mijn voorkeur om software engelstalig te gebruiken omdat je veel betere en meer "hits" krijgt op bijv. Google :mrgreen: )

INDEX(kolom met waardes;X) waarbij X een nummer van een rij is binnen die kolom met waardes
MATCH(waarde die je op wilt zoeken;kolom/rij met waardes waarin je die opzoekt;accuratesse ) waarbij accuratesse -1, 0 of 1 kan zijn (1 : minder dan, 0 : gelijk, -1 groter als)

Gecombineerd waarbij we dus het vinden van X automatiseren d.m.v. Match.

INDEX(kolom met waardes;MATCH(waarde die je op wilt zoeken;kolom/rij met waardes waarin je die opzoekt;precisie))

Waar het dus volgens mij fout gaat :
- in eerste instantie : "waarde die je op wilt zoeken". B4&K4. Dit zou B4 of K4 moeten zijn.
- in tweede instantie : "kolom met waardes waarin je die opzoekt". BASIS!$F$2:$F$250&BASIS!$A$2:$A$250. Dit kan volgens mij maar een kolom zijn. Dus BASIS!$F$2:$F$250 of BASIS!$A$2:$A$250
Maar waar Excel waarschijnlijk van over de emmer gaat is "precisie". Alle "parameters" dienen dus gescheiden te worden door een ; (maar dit verschilt dus per landinstelling van de machine) maar uitgaande van jouw voorbeeld is die waarschijnlijk ; op jouw systeem. (Maar daar was je dus zelf al achtergekomen :lol: )

Hoop dat je in ieder geval iets aan de links en mijn summiere uitleg hebt.
Zonder een duidelijk beeld te hebben hoe de data werkelijk georganiseerd is op je werkblad is suggesties geven waarom je een foute uitkomst krijgt niet echt makkelijk.

Maar nogmaals de tip om je gegevens zoveel mogelijk gestructureerd in "tabellen" om te zetten. Dit maakt het werken met complexe formules zo veel leesbaarder, makkelijker en leuker. (Want je kunt echt leuke dingen doen met Excel).
Niets meer frustrerender wanneer je formule niet meer goed werkt omdat je een rij boven je bestaande data hebt toegevoegd.
Gebruikersavatar
WillyH
Donateur
Berichten: 658
Lid geworden op: 22 dec 2018 09:35
Locatie: Zuiderkempen (B)

Re: Zijn er Excel specialisten in de zaal?

Bericht door WillyH »

eigen_gebruik schreef: 12 okt 2021 08:48 **SNIPPED**

Waar het dus volgens mij fout gaat :
- in eerste instantie : "waarde die je op wilt zoeken". B4&K4. Dit zou B4 of K4 moeten zijn.
- in tweede instantie : "kolom met waardes waarin je die opzoekt". BASIS!$F$2:$F$250&BASIS!$A$2:$A$250. Dit kan volgens mij maar een kolom zijn. Dus BASIS!$F$2:$F$250 of BASIS!$A$2:$A$250
Je kan met de functie VERGELIJKEN (MATCH in het Engels) op twee criteria zoeken. Een mogelijke manier die op het Internet te vinden is, is met de formule in kwestie. De opgezochte waarde in B4 wordt gezocht in de kolom BASIS!$F$2:$F$250 en er moet bijkomend een overeenkomst zijn met K4 in de kolom BASIS!$A$2:$A$250.
Heel erg bedankt voor de andere tips en opmerkingen, daar ga ik zeker gebruik van maken!
Ik lees net trouwens ook over een nieuwe functie, XZOEKEN (XLOOKUP) met enkele interessante eigenschappen die in dit geval wel handig kunnen zijn. Enig nadeel: je hebt er de nieuwste Excel versie voor nodig.
Gelukkig heb ik een Micro$oft 365 abonnement, alleen vervelend als anderen ook gebruik moeten kunnen maken van het rekenblad.
eigen_gebruik
Berichten: 46
Lid geworden op: 02 nov 2014 07:24

Re: Zijn er Excel specialisten in de zaal?

Bericht door eigen_gebruik »

WillyH schreef: 12 okt 2021 11:33 Je kan met de functie VERGELIJKEN (MATCH in het Engels) op twee criteria zoeken. Een mogelijke manier die op het Internet te vinden is, is met de formule in kwestie. De opgezochte waarde in B4 wordt gezocht in de kolom BASIS!$F$2:$F$250 en er moet bijkomend een overeenkomst zijn met K4 in de kolom BASIS!$A$2:$A$250.
Ik vermoedde al wel dat je 2 criteria wilt vergelijken / zoeken. Maar vond het gebruik van de & (met mijn summiere Excel kennis) in formules zo "raar".
Stel B4 = merk en K4 = aandrijving
B4&K4 leest voor mij dan als merkaandrijving. (Aangezien beide waardes d.m.v. & aanelkaar "geplakt" worden) voordat je naar de 2e stap van de MATCH gaat.

Exceljet heeft een hele mooi elegante oplossing als je op meerdere criteria wilt zoeken : https://exceljet.net/formula/index-and- ... e-criteria

Wat ik in ieder geval altijd doe als een formule (weer :P) niet werkte zoals ik verwachtte, is om in de cel van de formule te gaan staan en de "Evaluate Formula" in de "Formulas tab" te gebruiken (Excel 2016 / 2019. Zal in eerdere versies ook wel bestaan, maar durf niet te zeggen waar die dan staat).
Er zal dan een nieuw schermpje verschijnen waarmee je dan stap voor stap door je formule gaat en dan gelijk ziet met welke waardes Excel werkt om de berekening / vergelijking uit te voeren.
Heel erg bedankt voor de andere tips en opmerkingen, daar ga ik zeker gebruik van maken!
Graag gedaan hopelijk dat je er iets aan hebt :roll: . Het is gewoon altijd fijn als hetgeen wat je graag wilt doen in Excel ook werkt in Excel. En beide sites hebben mij in ieder geval een heel stuk geholpen.

En "Format as table" was voor mij echt een eye opener :shock: . Naast dat je elke tabel dan automatisch mooie kleurtjes kan geven :mrgreen: en kolomnamen overgenomen werden (niet vergeten om de "My table has headers" aan te vinken.)
Kon ik daarna mijn data zoveel makkelijker sorteren, filteren. Rijen toevoegen, verwijderen zonder dat mijn formule stuk ging. De tabel binnen het werkblad zonder problemen verplaatsen.
Als ik nu een tabel binnen Excel opzet door de kolomnamen te definieren, is de 2e stap automatisch "Format as table". Voordat ik uberhaupt begin met het vullen met data.

Ook de functie "Define Name" (als je rechtermuis klikt in een cell) is een functionaliteit die ik graag misbruik.
Is er een vermoeden dat een waarde in een losse cel vaak gebruikt zal worden in een formules, dan benoem ik hem d.m.v. die functionaliteit.
Als die waarde dan ergens nodig is hoef ik alleen de (voor mij logische) naam te onthouden en te gebruiken. En niet waar die waarde zich bevind.
Die cel toch op een andere plek hebben om dat werkblad "mooier" / overzichtelijker te maken.
Cel knippen en op een andere plek plakken. En de formule blijft gewoon werken.
Gelukkig heb ik een Micro$oft 365 abonnement, alleen vervelend als anderen ook gebruik moeten kunnen maken van het rekenblad.
Ik was voor mezelf al huiverig om van Office 2013 naar 2016 en dit jaar van Office 2016 naar 2019 te gaan.
Zo "bang" om een werkende Excel sheet met "complexe" formules en een stuk VBA scripting aan de achterkant om zeep te helpen. Maar gelukkig werkte alles naar behoren.
Kan me wel voorstellen dat je (ondanks dat het je leven een stuk makkelijker maakt) geen gebruik zult maken van nieuwe functies als andere gebruikers nog op een oudere versie draaien.
Gebruikersavatar
WillyH
Donateur
Berichten: 658
Lid geworden op: 22 dec 2018 09:35
Locatie: Zuiderkempen (B)

Re: Zijn er Excel specialisten in de zaal?

Bericht door WillyH »

eigen_gebruik schreef: 12 okt 2021 15:46
Graag gedaan hopelijk dat je er iets aan hebt :roll: .
Absoluut! Vooral het "Format as Table" ga ik zeker gebruiken!

Bedankt!
Willy
Plaats reactie