De Excel Troubleshooting Thread

[ralleke]

Hopelijk kan iemand mij helpen.
Kort uitgelegd. Ik werk in de logistieke sector en moet maandelijks een klant een overzicht bezorgen van de wachttijden bij de verschillende laad en loslocaties. (hieronder screenshot van de laadlocaties)

Kolom E = laadslot van
Kolom F = Laadslot tot
Kolom G = werkelijk laaduur van
Kolom F = Werkelijk laaduur tot
Kolom I = wachttijd laden
Kolom J = wachttijd laden -1u vrij
CEL A285: 01:00

Zie screenshot hieronder:

Nu als het laadslot van 10:30 is en het laadslot tot ook 10:30 --> Dit betekent afspraak om 10:30
Nu als het laadslot van 11:00 is en het laadslot tot 20:00 is --> Dit betekent dat het een range is en chauffeur tussen deze uren mag toekomen.
Als het laadslot van "00:00" is en het laadslot tot ook 00:00" is --> Dit betekent dat er geen slot is en wachturen steeds mogen aangerekend worden. (indien meer dan 1u)
Nu als de chauffeur te vroeg arriveert (Laadslot van bv 10:30) en werk laaduur van = 10:00 --> Dan mag er uiteraard pas geteld worden vanaf 10:30.

Bijkomend is er met de klant afgesproken dat chauffeur max 15 min te laat mag aankomen voor de wachturen te vergoeden.
Stel Laadslot van 10:30 en laadslot tot ook 10:30 --> Werkelijk laaduur van is 10:31 tot 10:45 --> wachttijd mag nog aangerekend worden.
In dit voorbeeld vanaf 10:46 dus niet meer.

Bijkomend is er ook een afspraak dat op de locaties "GENT" en "WILRIJK" nooit wachturen mogen aangerekend worden.

Nu in kolom I heb ik de volgende formule gezet:
=ALS([@[Werk. laaduur van]]<[@[LAADSLOT VAN]];[@[Werk. laaduur tot]]-[@[LAADSLOT VAN]];[@[Werk. laaduur tot]]-[@[Werk. laaduur van]])

Maar met deze formule wordt dus alleen rekening gehouden als de chauffeur te vroeg aankomt. Met al de rest wordt geen rekening gehouden.
Current work-a-round dat mij veel extra tijd bezorgt, filters zetten op WILRIJK en GENT en dan in kolom I "manueel 0:00" te typen.

In kolom J komt dan de effectieve tijd dat mag aangerekend worden (kolom I - 1u)
Formule: =ALS([@[Wachttijd laden]]-$A$285<0;"Geen wachturen";[@[Wachttijd laden]]-$A$285)

Iemand dat mij een formule kan bezorgen, dat rekening houdt met alle bovenstaande criteria?

 

[Arkaine]

@ralleke

Vlug even je verhaal gelezen, niet super aandachtig

Het makkelijkste lijkt mij gewoon voor elke voorwaarde een helper-column aan te maken en dan vanuit de helper columns een geneste if-functie te maken die elke voorwaarde één voor één aftoetst.

Zonder helper columns zal het er al moeilijk leesbaar uitzien, maar alles gooien in één formule maakt er gewoon een drama van.

 

[shellshock]

Hopelijk kan iemand mij helpen.
Kort uitgelegd. Ik werk in de logistieke sector en moet maandelijks een klant een overzicht bezorgen van de wachttijden bij de verschillende laad en loslocaties. (hieronder screenshot van de laadlocaties)

Kolom E = laadslot van
Kolom F = Laadslot tot
Kolom G = werkelijk laaduur van
Kolom F = Werkelijk laaduur tot
Kolom I = wachttijd laden
Kolom J = wachttijd laden -1u vrij
CEL A285: 01:00

Zie screenshot hieronder:

Nu als het laadslot van 10:30 is en het laadslot tot ook 10:30 --> Dit betekent afspraak om 10:30
Nu als het laadslot van 11:00 is en het laadslot tot 20:00 is --> Dit betekent dat het een range is en chauffeur tussen deze uren mag toekomen.
Als het laadslot van "00:00" is en het laadslot tot ook 00:00" is --> Dit betekent dat er geen slot is en wachturen steeds mogen aangerekend worden. (indien meer dan 1u)
Nu als de chauffeur te vroeg arriveert (Laadslot van bv 10:30) en werk laaduur van = 10:00 --> Dan mag er uiteraard pas geteld worden vanaf 10:30.

Bijkomend is er met de klant afgesproken dat chauffeur max 15 min te laat mag aankomen voor de wachturen te vergoeden.
Stel Laadslot van 10:30 en laadslot tot ook 10:30 --> Werkelijk laaduur van is 10:31 tot 10:45 --> wachttijd mag nog aangerekend worden.
In dit voorbeeld vanaf 10:46 dus niet meer.

Bijkomend is er ook een afspraak dat op de locaties "GENT" en "WILRIJK" nooit wachturen mogen aangerekend worden.

Nu in kolom I heb ik de volgende formule gezet:
=ALS([@[Werk. laaduur van]]<[@[LAADSLOT VAN]];[@[Werk. laaduur tot]]-[@[LAADSLOT VAN]];[@[Werk. laaduur tot]]-[@[Werk. laaduur van]])

Maar met deze formule wordt dus alleen rekening gehouden als de chauffeur te vroeg aankomt. Met al de rest wordt geen rekening gehouden.
Current work-a-round dat mij veel extra tijd bezorgt, filters zetten op WILRIJK en GENT en dan in kolom I "manueel 0:00" te typen.

In kolom J komt dan de effectieve tijd dat mag aangerekend worden (kolom I - 1u)
Formule: =ALS([@[Wachttijd laden]]-$A$285<0;"Geen wachturen";[@[Wachttijd laden]]-$A$285)

Iemand dat mij een formule kan bezorgen, dat rekening houdt met alle bovenstaande criteria?

Voor het bepalen van de locatie kan je volgende formule gebruiken

=ALS(OF(D2="Wilrijk";D2="Gent");"Geen Wachturen";"nok")

In plaats van "nok" kan je dan verder werken met de formule om de daadwerkelijke laadtijd te berekenen.

 

[JDB]

Misschien redelijke noob vraag maar ik ben nog niet zo onderlegd in excel.

Ik heb in mijn eerste werkblad een prijslijst staan.
Referenties in kolom C , prijzen in kolom K , % korting toe te passen op die prijs in kolom L

Voor een selectie van die referenties is er een speciale prijs onderhandeld.
Die staat in werkblad 2
De referenties staan bij dat werkblad in kolom C , de prijzen in kolom Q Op deze prijzen wordt geen % korting toegepast

Nu wil ik die speciale prijzen in het eerste werkblad krijgen. Dus de prijzen voor die selectie referenties verhuizen naar kolom K van werkblad 1 en voor die lijnen % korting dan op 0% zetten.
Iemand een idee of dit op een vlotte manier kan zonder een dag ctrl+f en manueel prijzen aanpassen te doen?

 

[videast]

Misschien redelijke noob vraag maar ik ben nog niet zo onderlegd in excel.

Ik heb in mijn eerste werkblad een prijslijst staan.
Referenties in kolom C , prijzen in kolom K , % korting toe te passen op die prijs in kolom L

Voor een selectie van die referenties is er een speciale prijs onderhandeld.
Die staat in werkblad 2
De referenties staan bij dat werkblad in kolom C , de prijzen in kolom Q Op deze prijzen wordt geen % korting toegepast

Nu wil ik die speciale prijzen in het eerste werkblad krijgen. Dus de prijzen voor die selectie referenties verhuizen naar kolom K van werkblad 1 en voor die lijnen % korting dan op 0% zetten.
Iemand een idee of dit op een vlotte manier kan zonder een dag ctrl+f en manueel prijzen aanpassen te doen?

Echt heel makkelijk in XL via een VLOOKUP of VERT.ZOEKEN functie. Maak een vrije kolom in werkblad 1 (bv M) Er vanuit gaande dat je lijst op lijn 2 begint: zet in cel M2 deze formule: =VERT.ZOEKEN(c2; 'werkblad 2'!C:Q;15;onwaar). Laatste element is False in Engelse excel, niet zeker of dat onwaar is). Dat geeft je de eventuele lagere prijs uit je werkblad 2. Kopieer deze formule voor alle lijnen. Er zal #N/A staan als die referentie niet voorkomt. Met een IF of ALS formule zou je bv 0 of blanco kunnen zetten als die ref niet voorkomt in blad2

In een volgende kolom N kun je dan de prijs berekenen, met een ALS formule: =ALS(isna(M2); K2*(1-L2);M2), er van uitgaande dat de korting in % staat in L. Ik weet niet of ISNA juist is voor de Nederlandse Excel. Werk namelijk uitsluitend met de Engelse versie.

Het heeft 10x langer geduurd om dit te schrijven dan de 1 min dat het duurt om dat te doen voor iemand met ervaring. Ik hoop dat het je lukt.

 

[JDB]

Echt heel makkelijk in XL via een VLOOKUP of VERT.ZOEKEN functie. Maak een vrije kolom in werkblad 1 (bv M) Er vanuit gaande dat je lijst op lijn 2 begint: zet in cel M2 deze formule: =VERT.ZOEKEN(c2; 'werkblad 2'!C:Q;15;onwaar). Laatste element is False in Engelse excel, niet zeker of dat onwaar is). Dat geeft je de eventuele lagere prijs uit je werkblad 2. Kopieer deze formule voor alle lijnen. Er zal #N/A staan als die referentie niet voorkomt. Met een IF of ALS formule zou je bv 0 of blanco kunnen zetten als die ref niet voorkomt in blad2

In een volgende kolom N kun je dan de prijs berekenen, met een ALS formule: =ALS(isna(M2); K2*(1-L2);M2), er van uitgaande dat de korting in % staat in L. Ik weet niet of ISNA juist is voor de Nederlandse Excel. Werk namelijk uitsluitend met de Engelse versie.

Het heeft 10x langer geduurd om dit te schrijven dan de 1 min dat het duurt om dat te doen voor iemand met ervaring. Ik hoop dat het je lukt.

Bedankt! Ik ga een poging wagen.
Geloof me, ik kon dit zelf niet verzinnen. Ik ben dan ook een totale leek, net zoals iedereen op onze bureau


edit: 't is gelukt
Bedankt!

 

[Static]

Kan iemand me helpen bij volgende Excel vraag:

Ik wil graag uit een .csv dump van een bankrekening een klein dashboard opstellen.
Hierbij heb ik per verrichting een cel met een hele lange tekst in:
vb: "BETALING VIA BANCONTACT 19-01 19-01-2023 OM 11.51 UUR 4011 OKAY"
Ik heb een formule nodig die hierin zoekt op het woord "OKAY" en dan een categorie en soort weergeeft die in aparte tabel staat.
vb

WAT ZOEKEN	CATEGORIE	SOORT
OKAY	BOODSCHAPPEN	OKAY

Zo wil ik dan per verrichting de categorie en soort kost hebben om een dashboard te bouwen.
Een simpele Vlookup werkt hier dus niet... iemand een idee om een precies woord te zoeken in een lange tekstcel en een return te geven van een andere tabel?

Alvast bedankt!

 

[videast]

Opnieuw sorry dat ik enkel de Engelse formule ken:
= search (te zoeken tekst; cel waar te zoeken; start zoeken vanaf positie x).
Search is niet case sensitieve, maw hoofd- en kleine letters spelen geen rol.

De gelijkaardige FIND functie is wel case sensitive. Je gaat wel even de gelijkaardige Nederlandse functie moeten zoeken.

De functie geeft een getal terug waar de te zoeken string staat. Als de string niet gevonden krijg je een fout terug.

Dat is het antwoord op je vraag, maar ik heb het gevoel dat je iets anders nodig hebt. Namelijk in de tabel wil je het laatste stuk eruit halen (okay) en dat opzoeken via vlookup in een andere tabel om de categorie op te halen. Dan kun je een pivot (draaitabel) erop zetten.

Wss is die "okay" niet voor alle verrichtingen even lang (afh van de winkel). Dat maakt het een beetje tricky. Ik zou enkele lijnen met verschillende zaken moeten zien om je de beste formule te geven. Zou bv "de laatste x characters" kunnen zijn. Of "zoek UUR, en tel daar x karakters bij, tot aan het einde van de string. Als je mij een sample file stuurt met een paar verschillende lijnen (mag fake data zijn), wil ik je wel de formule maken. Gebruik mijn forumnaam bij gmail...

 

[Tomski]

ik zit hier al een halve namiddag op een vlookup te sukkelen

2 kolommen die ik effe moet checken op ontbrekende waardes, beide text, de waardes komen overeen (op sommige na) maar toch geven ze allemaal N/A. Echt geen zin om manueel gans die lijst te gaan overlopen wie er ontbreekt (tzijn er 3, want kolom C is korter)

heb de velden al es vergeleken.. =ISTEXT(A4) & =ISTEXT(C5) resulteert voor beide waardes in True, dus het zijn textvalues. LEN functie geeft dezelfde waarde dus er zijn geen leading of trailing spaties in te vinden.

alle N/A krijgen een Value not available error, maar ik zie ze bv wel staan, de value staat er in.
Heb de celformat al aangepast maar ook geen resultaat. Ik moet écht overtypen en dan werkt het. Kan niet de bedoeling van excel zijn
Zelfs een copy paste als value werkt niet.

Als ik een waarde in kolom A exact overtyp in dezelfde cel dan werkt de functie.. wtf am I doing wrong?

 

[videast]

Kun je eens je vlookup formule hier pasten? Anders is het moeilijk raden. Maar kans is groot dat het aan je cellen ligt.

Als je eens checkt op twee gelijke cellen met =A4=C5 krijg je dan ook True?
Of maak een tijdelijke kolom met =valuetotext(A4), ja dat werkt ook voor tekst, en doe dat copy paste as values in de originele kolom. [Edit: neen, beter niet terugpasten in originele kolom... je zou je probleem ermee kunnen bestendigen, gebruik liever die nieuwe kolom voor de vlookup]
als data van een download komt gebeurt er soms iets raars met tekstherkenning door excel.

 

[Tomski]

Kun je eens je vlookup formule hier pasten? Anders is het moeilijk raden. Maar kans is groot dat het aan je cellen ligt.

Als je eens checkt op twee gelijke cellen met =A4=C5 krijg je dan ook True?
Of maak een tijdelijke kolom met =valuetotext(A4), ja dat werkt ook voor tekst, en doe dat copy paste as values in de originele kolom. [Edit: neen, beter niet terugpasten in originele kolom... je zou je probleem ermee kunnen bestendigen, gebruik liever die nieuwe kolom voor de vlookup]
als data van een download komt gebeurt er soms iets raars met tekstherkenning door excel.

Heb net het issue gevonden. Heb excel gesaved als csv in utf8 en na elke value van de defecte kolom verscheen er een y met 2 puntjes op. Nog nooit meegemaakt. Data kwam idd van een download (ansi)

 

[videast]

Heb net het issue gevonden. Heb excel gesaved als csv in utf8 en na elke value van de defecte kolom verscheen er een y met 2 puntjes op. Nog nooit meegemaakt. Data kwam idd van een download (ansi)

Yep, al vaak meegemaakt met downloads. Lukte die valuetotext dan niet? Gaf LEN(..) dan de effectieve lengte of de lengte met de 2 extra karakters erbij? Indien dat laatste, dan kun je met =LEFT(A1;LEN(...)-2) dat wegknippen.

 

[Tomski]

Yep, al vaak meegemaakt met downloads. Lukte die valuetotext dan niet? Gaf LEN(..) dan de effectieve lengte of de lengte met de 2 extra karakters erbij? Indien dat laatste, dan kun je met =LEFT(LEN(...)-2) dat wegknippen.

Das het rare, LEN gaf bij beide 14 char.

 

[dunno]

=XLOOKUP en =SWITCH(TRUE; ) zijn een stuk makkelijker werken dan =VLOOKUP en (nested) =IF.

 

[toru]

Ideetjes hoe ik dit beter kan aanpakken?

Ik heb een Excel om een kleine 'boekhouding' voorstelt:


Datum - Volgnummer - Beschrijving - Type uitgave - Type kost - Bedrag

Ik log hierin elk volgnummer (bv een factuur) onder het type uitgave (Bijvoorbeeld Drank) en Type kost (bv: Werkingskost, Evenement, ...)

Dit werkt goed, alleen is niet elke factuur even rechtlijnig in te dienen. Zo krijg ik een factuur binnen waarbij ik als Type uitgave meerdere zaken aan moet kunnen toekennen (bv 100 euro Administratie website en 100 euro Transactiekosten website). Vroeger waren alle categorieën gewoon kolommen en kon ik dat zo uitsplitsen. Maar dat gaf dus een heel warrige en grote Excel die praktisch onleesbaar was.

Sowieso kan ik gewoon twee lijnen gebruiken om één uitgave te boeken en zo de opsplitsing te maken. Maar dat lijkt me ook niet heel netjes en mooi. Iemand een idee hoe ik zoiets in Excel zou kunnen doen?

 

[videast]

Ik zou zeggen: op twee of meerdere lijnen zetten, en dan een pivotje (draaitabel) erop als je wil aggregeren per factuur. Kun je kiezen om eventueel het "Type" uitgave al dan niet in kolommen te zetten.

 

[shellshock]

Volgende probleem. Ik zit met een reeks van documenten, onderverdeeld in secties waarvan sommige een volgnummer (startend bij 1) moeten krijgen, gebaseerd op een ja/nee/wachten-check. Zolang het documentnummer hetzelfde is, moet het volgnummer ook hetzelfde zijn.

Zo zou het er dus moeten uitzien. Formulegewijs kom ik er niet direct uit. Heb al wat liggen prutsen met de =reeks()-functie, maar kan niks vinden. VBA-gewijs zal dit waarschijnlijk eenvoudig opgelost kunnen worden, maar daar heb ik nog geen kaas van gegeten. Met filters gaan werken is geen optie. Nummering moet 'on the fly' worden aangepast zodra in de kolom 'volgnummer krijgen' een verandering plaatsvindt.

Volgnummer krijgen	Document	Sectie	Volgnummer
ja	54564​	A	1​
ja	54564​	B	1​
ja	69888​	A	2​
nee	3633​	A
ja	9876​	A	3​
nee	59676​	A
nee	59676​	B
nee	24968​	A
ja	213​	A	4​
nee	213​	B
ja	213​	C	4​
ja	268​	A	5​
nee	87899​	A
ja	7899​	A	6​

 

[cG`]

Volgende probleem. Ik zit met een reeks van documenten, onderverdeeld in secties waarvan sommige een volgnummer (startend bij 1) moeten krijgen, gebaseerd op een ja/nee/wachten-check. Zolang het documentnummer hetzelfde is, moet het volgnummer ook hetzelfde zijn.

=ALS(A2="ja";ALS(ISFOUT(VERGELIJKEN(B2;$B$1:B1;0));MAX($D$1:D1)+1;ALS(D1="";MAX($D$1:D1)+1;D1));ALS(A2="nee";"";""))

De formule in D2 plakken, zorg ervoor dat er geen gegevens in rij 1 staan.

Is gemaakt met hulp van ChatGPT, dus mijn "ondersteuning" is beperkt Maar hiermee kan je toch al iets doen.

 

[Lint]

Volgende probleem. Ik zit met een reeks van documenten, onderverdeeld in secties waarvan sommige een volgnummer (startend bij 1) moeten krijgen, gebaseerd op een ja/nee/wachten-check. Zolang het documentnummer hetzelfde is, moet het volgnummer ook hetzelfde zijn.

Zo zou het er dus moeten uitzien. Formulegewijs kom ik er niet direct uit. Heb al wat liggen prutsen met de =reeks()-functie, maar kan niks vinden. VBA-gewijs zal dit waarschijnlijk eenvoudig opgelost kunnen worden, maar daar heb ik nog geen kaas van gegeten. Met filters gaan werken is geen optie. Nummering moet 'on the fly' worden aangepast zodra in de kolom 'volgnummer krijgen' een verandering plaatsvindt.

Volgnummer krijgen	Document	Sectie	Volgnummer
ja	54564​	A	1​
ja	54564​	B	1​
ja	69888​	A	2​
nee	3633​	A
ja	9876​	A	3​
nee	59676​	A
nee	59676​	B
nee	24968​	A
ja	213​	A	4​
nee	213​	B
ja	213​	C	4​
ja	268​	A	5​
nee	87899​	A
ja	7899​	A	6​

Sorteer op kolom dossiernummer, en dan in een extra kolom (E) "if(B2=B1;E1;E1+1)", en dan in uw volgnummer "if(A2="ja";E2

 

[shellshock]

=ALS(A2="ja";ALS(ISFOUT(VERGELIJKEN(B2;$B$1:B1;0));MAX($D$1:D1)+1;ALS(D1="";MAX($D$1:D1)+1;D1));ALS(A2="nee";"";""))

De formule in D2 plakken, zorg ervoor dat er geen gegevens in rij 1 staan.

Is gemaakt met hulp van ChatGPT, dus mijn "ondersteuning" is beperkt Maar hiermee kan je toch al iets doen.

Sorteer op kolom dossiernummer, en dan in een extra kolom (E) "if(B2=B1;E1;E1+1)", en dan in uw volgnummer "if(A2="ja";E2

Even hierop terugkomen. Heb wat gesukkeld, maar onlangs een heel eenvoudige oplossing gevonden middels de functie 'verschuiving' of 'offset' in het Engels. je maakt een apart tabblad waarin je de dubbele documentnummers verwijdert middels 'Dubbele waarden verwijderen' Je kan in de kolom met volgnummer de volgende aanpassing doen ja = 1, nee = 0. Of eventueel een hulpkolom toevoegen.
Vervolgens in C2 het volgende:

=ALS(A2=1;SOM(VERSCHUIVING($A$2;0;0;RIJ(A2)-1;1));"")

Als A2 de waarde 1 (dus moet een volgnummer krijgen) heeft, moet er in C2 de som genomen worden van het bereik vanaf A2. Volgende twee parameters zijn nul, want ik wil niet dat er rijen of kolommen wordt verschoven. Hoogte is het aantal rijnummers dat ik wil opellen. In A2 is dat één rijnummer, in A3 twee rijnummers (want A2:A3) enz enz, dus aantal rijen dat ik wil optellen is het rijnummer van AX -1. Laatste is 1, want ik wil de resultaten van één kolom optellen. Zeker A2 absoluut maken.
Dan nog in de functie vermelden dat als A2 niet gelijk is aan 1 er geen optelling -en dus geen volgnummer- moet komen. Kan je uiteraard door iets anders naar keuze vervangen.

Volgnummer krijgen	Document	Volgnummer
1	54564​	1​
1	69888​	2​
0	3633​
1	9876​	3​
0	59676​
0	24968​
1	213​	4​
1	268​	5​
0	87899​
1	7899​	6​