[EXCEL] lijst genereren op basis van meerdere criteria

[Raymond]

* Raymond heeft weer eens een Excel vraagje.

Ik heb een sheet waarin -schematisch- het volgende staat:



Een hele lijst met nummers, die meerdere keren voor kunnen komen. Bij code1 staat een x of een y. Bij code2 kàn iets ingevuld staan, achter een x code (achter een y code zal er nooit iets ingevuld staan).

Nu moet ik automatisch een overzichtje (of eigenlijk 2) genereren waarin het volgende komt te staan:

-Alle nummers waar alleen y-codes achter staan (één of meerdere)
-Alle nummers waar één of meerdere x-codes achterstaan èn waar achter elke x-code een code2 staat ingevuld.

Andersom gerekend: Uit de lijst moet dus gefilterd worden: alle nummers waar een x-code achter staat, waar nog geen code2 is ingevuld.

Om het in een context te plaatsen: Het is een overzicht met items waar een reparatie aan moet gebeuren. Sommige reparaties vergen geen onderdelen (y code) en daar kan altijd aan begonnen worden. Voor andere reparaties zijn onderdelen nodig en de code2 geeft aan dat het onderdeel er is. Wanneer alle onderdelen binnen zijn, kan er aan de reparatie begonnen worden.
Dus uit de lijst moeten alle nummers gefilterd worden, waar onderdelen voor nodig zijn, maar die nog niet allemaal binnen zijn.

Wat er dus uiteindelijk tot stand moet komen is een lijst met alle reparatie-nummers waar aan begonnen kan worden.

In bovenstaand schema mag er dus begonnnen worden aan 1 en 2 en voor 3 moet er nog een onderdeel binnen komen.

Iemand hier een suggestie hoe dit valt aan te pakken (liefst zonder het gebruik van vbasic)?

[BtM909]

Ik heb hier wat inhoudelijke vraagjes over

-Alle nummers waar alleen y-codes achter staan (één of meerdere)

Wil je dus een gefilterde lijst hebben (oftewel is nummer 1 met een x hetzelfde als 1 met een y) of mag het resultaat dubbele nummers bevatten?

[kosz]

Gebruik daarvoor gewoon de functie draaitabellen die in excel zit.
http://www.microsoft.com/...wto/excel_draaitabel.aspx
Het is ff proberen hoe en wat welk veld reageert, maar wat jij wilt is daarmee gemakkelijk te doen.

[Raymond]

Een nummer kan zowel x als y codes hebben, dus "1 met een x" is idd hetzelfde nummer als "1 met een y". Denk bijvoorbeeld aan een item dat wacht op reparatie waarbij iets afgesteld moet worden (y codering) als waar ook een onderdeel voor nodig is (x codering).

Het mooiste is als er 1 lijst komt met unieke nummers, waarin alle nummers staan die gerepareerd kunnen worden (dus alleen y coderingen, of x-coderingen waar alle onderdelen voor binnen zijn gekomen, te herkennen aan de a codering).

Ik ben zelf ook al druk aan het piekeren geweest en volgens mij het allemaal samen laten komen in 1 lijst erg lastig. Vandaar dat ik in mijn vraag al uitging van 2 lijsten: eentje met nummers die alleen y-coderingen hebben en een lijst waar nummers ook een x-codering hebben (waarvan de onderdelen al binnen zijn).
Maar misschien is 3 lijsten wel makkelijker (alleen y, alleen x, x en y).

Met een draaitabel kom je al een heel eind, heb ik al gezien. Gewoon alle nrs weergeven en daar de a-waarden achter laten zetten) en filteren op x Als het nou mogelijk zou zijn om alle waarden uit een draaitabel te laten filteren waar een blank bij staat (nr3 in het bovenstaande voorbeeld), dan zou het overzicht er al zijn

---

Verder lopen piekeren :

Volgens mij zou deze taktiek kunnen werken:
(1)-genereer een lijst van nrs die een x-codering hebben zonder a-codering (onderdeel nodig, nog niet binnen)
(2)-genereer een lijst van alle nrs in de lijst

Filter alle nummers uit lijst (1) uit lijst (2) en dat is het overzicht van nrs die gerepareerd kunnen worden.

[ Voor 12% gewijzigd door Raymond op 22-11-2010 14:05 ]

[kosz]

Geef dan de lege velden een waarde als bv -
Heb hetzelfde probleem ook zo eens opgelost, lege velden zijn makkelijk te vullen met een waarde als volgt :

Markeer je gehele gebied.
Druk op F5.
Kies Speciaal
Kies lege waarden en druk op OK.
Tik nu bijv. - en druk op CTRL+ENTER.
Alle gemarkeerde cellen (dat zijn de lege) worden nu gevuld met -

[Raymond]

kosz schreef op maandag 22 november 2010 @ 14:04:
Geef dan de lege velden een waarde als bv -
Heb hetzelfde probleem ook zo eens opgelost, lege velden zijn makkelijk te vullen met een waarde als volgt :

Markeer je gehele gebied.
Druk op F5.
Kies Speciaal
Kies lege waarden en druk op OK.
Tik nu bijv. - en druk op CTRL+ENTER.
Alle gemarkeerde cellen (dat zijn de lege) worden nu gevuld met -

Daarmee ben ik nog niet geholpen: Als ik dat doe, dan kan ik idd wel alle velden met een "-" eruit vissen, maar dan lijkt het net alsof voor alle reparaties waar meerdere onderdelen voor nodig zijn, de onderdelen binnen zijn, aangezien de velden met een a codering wèl blijven staan.

[kosz]

Deel je draaitabel zo in dat je je variabelen bovenin met een filter optie kan kiezen welke je wilt laten zien, en welke opgenomen moeten worden in de resultaten van je draaitabel.
Zo kan je alles in 1 sheet houden.

[ Voor 13% gewijzigd door kosz op 22-11-2010 14:16 ]

[Raymond]

Ik ben idd ook op die tour bezig: Ik heb nu in een draaitabel een lijst van alle nrs die niet gerepareerd kunnen worden. Da's ook al winst

[kosz]

Het is een beetje pielen om het juiste veld op de plek te krijgen die je in gedachte had, en dat ie ook nog doet wat je graag zou willen zien
Beter een middag rommelen in excel dan met de hand tellen

[Raymond]

't is een dagelijks terugkomend ritueel, dat de gebruiker elke dag een uur kost.
Maar aangezien het onterecht starten van een reparatie meer dan een uur per stuk kost, loont dat uur de moeite wel.
Maar als dat uur puzzelen terug gebracht kan worden naar 2 minuten, dan is een middag excel puzzelen ook al snel weer terugverdiend

---

Goed: ik heb nu een lijst met alle nummers en een lijst met nummers die eruit gekegeld moeten worden. Hoe maak ik nu een 3de lijst waarin ik de niet-reparaties uit de totale lijst heb gehaald?
Een soort van inverse-VLOOKUP idee, zeg maar

edit:
ben nu dit even aan het bestuderen:
http://www.get-digital-he...tween-two-lists-in-excel/
edit2: een array formule loslaten op 1000 rijen is geen succes, ben ik bang

[ Voor 16% gewijzigd door Raymond op 22-11-2010 14:55 ]

[pedorus]

Ik zou hiervoor een hulpkolometje aanmaken, en daarop filteren. In die kolom kan true/false staan aan de hand van de criteria. Voor jouw voorwaarden, als doortrekbare Engelse formule:

Raymond schreef op maandag 22 november 2010 @ 12:29:
-Alle nummers waar alleen y-codes achter staan (één of meerdere)

=AND(B2="y",COUNTIFS(A:A,A2,B:B,"<>y")=0,COUNTIF(A$2:A2,A2)=1)

-Alle nummers waar één of meerdere x-codes achterstaan èn waar achter elke x-code een code2 staat ingevuld.

=AND(B2="x",COUNTIFS(A:A,A2,B:B,"x",C:C,"")=0,COUNTIFS(A$2:A2,A2,B$2:B2,"x")=1)

De laatste voorwaarde uit het rijtje zorgt er alleen voor dat je alleen de eerste hit terugkrijgt.

Iets zegt me trouwens dat een echte database hier meer geschikt voor is dan Excel.

[kosz]

Misschien dat ik de draad een beetje kwijt ben geraakt hoever je tot je beoogde doel bent genaderd, maar waar om geen 3 draaitabellen laten genereren ?
1 met de x waarden
1 met de y waarden
1 met lege velden

[CaptJackSparrow]

Ik begon me ook af te vragen of je niet tegen de limitaties van Excel aan begint te lopen. Voor een rekenprogramma heeft het nog heel aardige database functies maar je zou je sheet eens in Access kunnen importeren en daar eens mee aan de gang gaan. Zo moeilijk is dat nu ook niet en het is veel krachtiger op database gebied omdat het er nou eenmaal speciaal voor bedoeld is.

Als je Access hebt natuurlijk.

[Raymond]

kosz schreef op maandag 22 november 2010 @ 15:02:
Misschien dat ik de draad een beetje kwijt ben geraakt hoever je tot je beoogde doel bent genaderd, maar waar om geen 3 draaitabellen laten genereren ?
1 met de x waarden
1 met de y waarden
1 met lege velden

Omdat het einddoel 1 lijst is met nummers die gerepareerd mogen worden. En de mensen die er mee gaan werken, hebben geen ervaring met excel. Dus de lijst moet zonder handwerk door de gebruikers tot stand komen.

Ik ben nu druk bezig om de 2 pivottable kolommen samen te voegen tot een lijst met unieke nummers, dmv dit soort technieken:
http://www.get-digital-he...ue-list-from-two-columns/

Maar dat wil nog niet bepaald lukken (geen resultaten, of een berekeningstijd van enkele minuten op een dualcore processor).

edit: het werkt nu, maar het kost ongeveer een seconde rekentijd per nummer. Dus als ik een lijst van 300 nummers wil krijgen, dan kost het doodleuk 5 minuten

* Raymond zoekt dus maar ff verder naar een iets efficiënter algorithme

[ Voor 12% gewijzigd door Raymond op 22-11-2010 15:21 ]

[pedorus]

Raymond schreef op maandag 22 november 2010 @ 15:16:
edit: het werkt nu, maar het kost ongeveer een seconde rekentijd per nummer. Dus als ik een lijst van 300 nummers wil krijgen, dan kost het doodleuk 5 minuten

* Raymond zoekt dus maar ff verder naar een iets efficiënter algorithme

Tsja, en daarnaast moet je de pivottables steeds updaten. Een autofiltertje lijkt me toch makkeljiker

Overigens kun je de twee formules die ik geef vrij makkelijk combineren, zodat je true krijgt als aan een van de twee voorwaarden wordt voldaan:

code:

=AND(COUNTIFS(A:A,A2,B:B,"x",C:C,"")=0,COUNTIF(A$2:A2,A2)=1)

Hoewel dit nog steeds O(N^2) is, denk ik dat de rekentijd bij 1000 items nog wel te doen is.

Je kan echter ook gebruik maken van 2 hulpkolommetjes en de sortering in kolom A. In kolom D krijg je dan (heeft te repareren onderdelen?, alleen zeker kloppend in eerst rij voor een nummer):

code:

=OR(AND(A3=A2,D3=TRUE),AND(B2="x",C2=""))

En in kolom E (kan alles nu gebeuren voor dit nummer?):

code:

=AND(NOT(D2),A1<>A2)

[Raymond]

Uiteindelijk opgelost door een pivottable van een pivottable+hulpkolommetje te maken.
Ik moet nu nog wel gaan proefdraaien hoe robuust het is.

[pedorus]

Een pivottable van een pivottable? Wow.

Mocht je toch nog een alternatieve oplossing willen zien:

=IFERROR(INDEX(Sheet1!A:A,B1+1),"")	=MATCH(TRUE,D:D,0)	=OR(AND(Sheet1!A3=Sheet1!A2,C2=TRUE),AND(Sheet1!B2="x",Sheet1!C2=""))	=AND(NOT(C1),Sheet1!A1<>Sheet1!A2,Sheet1!A2<>"")	=IFERROR("WAARSCHUWING: ongesorteerdheid op regel "&MATCH(TRUE,Sheet1!A2:INDEX(Sheet1!A:A,MATCH(1E+300,Sheet1!A:A)-1)>Sheet1!A3:INDEX(Sheet1!A:A,MATCH(1E+300,Sheet1!A:A)),0)+2,"")
=IFERROR(INDEX(Sheet1!A:A,B2+1),"")	=MATCH(TRUE,INDEX(D:D,B1+1):D2000,0)+B1	=OR(AND(Sheet1!A4=Sheet1!A3,C3=TRUE),AND(Sheet1!B3="x",Sheet1!C3=""))	=AND(NOT(C2),Sheet1!A2<>Sheet1!A3,Sheet1!A3<>"")	=IF(MATCH(1E+300,Sheet1!A:A)>2000,"WAARSCHUWING: deze sheet ondersteund slecht 2000 regels en er zijn er meer","")

Het idee is dat dit op sheet2 staat, en dat de kolommen b-d gehide worden, de kolommen a-d worden doorgetrokken tot regel 2000, en de formule rechtsboven met ctrl-shift-enter wordt ingevoerd. De sheet zelf kan dan gelocked worden van editing. Het mooie is dat er 0 handmatige acties nodig zijn, en het overzicht altijd up-to-date is.

[Raymond]

Zo'n array-formule heb ik eerst mee gewerkt, idd. Dat werkt idd eigenlijk beter, maar kent 1 groot nadeel: het kost (echt waar!) een seconde rekentijd per regel. Dus reken maar uit hoe lang het duurt om jouw 2000 regels te berekenen

[pedorus]

Raymond schreef op dinsdag 23 november 2010 @ 15:15:
Zo'n array-formule heb ik eerst mee gewerkt, idd. Dat werkt idd eigenlijk beter, maar kent 1 groot nadeel: het kost (echt waar!) een seconde rekentijd per regel. Dus reken maar uit hoe lang het duurt om jouw 2000 regels te berekenen

Minder dan 1 seconden/niet echt merkbaar op mijn pc. Die ene array-formule is er alleen maar om te testen of de ids wel goed gesorteerd zijn. Als dezelfde ids niet bij elkaar staan, dan gaat het mogelijk mis.

[Anoniem: 130540]

Ik heb een oplossing bedacht door 3 kolommen toe te voegen met een formule.

De eerste telt het aantal bonnen (kolom A) met hetzelfde nummer. Ze hoeven hiervoor niet gesorteerd te zijn:
Kolom D : =AANTAL.ALS($A$2:$A$1000;A2).

De tweede test per regel of een combinatie compleet is; een Y onder code1 of een X onder code1 met iets in code2:
Kolom E : =ALS(OF(B2="y";EN(B2="x";C2<>""));A2;0)
Wanneer een test WAAR is wordt het nummer uit kolom A overgenomen, anders een 0.

Tenslotte wordt in de derde test vergeleken of het aantal vermelde bonnummer in kolom E gelijk is aan het totaal aantal regels met hetzelfde bonnummer in kolom A:
Kolom F : =ALS(EN(AANTAL.ALS($E$2:$E$7000;A2)=D2;A2<>"");"volledig!";"-")
Zo ja, dan krijg je de tekst 'volledig' en hoef je enkel nog te filteren op regels met deze tekst.

Als je deze formules doortrekt tot voorbij het totaal aantal regels dat je redelijkerwijs verwachten mag (vandaar de $A$1000 in mijn voorbeeld) en kolom D en E verbergt, dan krijg je bij elke
nieuwe toevoeging in je register prompt in kolom F te zien of iets 'volledig' is.
De berekening vind enkel plaats bij een wijziging in een cel, je lijst is dus continue 'up-to-date'.

Ik heb het getest met 5000 regels. Dat geeft een lichte vertraging bij het invoeren. Natuurlijk kan je de automatische berekening ook uitschakelen en met F9 doorvoeren voordat je een lijst wilt printen.

Dit lijkt me een veel simpeler en lichter oplossing dan werken met 2 of 3 draaitabellen.

[Raymond]

Bedankt voor het meedenken! Ik zal eens gaan stoeien of ik dat in een los bestand kan plaatsen en dan een enkele kolom met de reparatienummers kan genereren. Dan kan namelijk het bronbestand elke keer vervangen worden, zonder dat steeds opnieuw de formules in het werkblad gekopieerd moeten worden ed.

[Anoniem: 130540]

De formules in een ander bestand, of 2e blad bewaren, is een safe werkwijze. Bovendien worden de berekeningen alleen dan uitgevoerd wanneer je het nodig hebt.
Ik heb mijn formules nog een beetje door-ontwikkeld (omdat ik het niet laten kan en het bovendien beter en sneller kon) en kom nu op het volgende:

code:

 =ALS(OF($B2="y";EN($B2="x";$C2<>""));$A2;0)

Controleert als vanouds of een regel compleet is en plaatst, indien WAAR, het bonnummer uit kolom A in kolom D, en anders een 0.

code:

 =ALS(D2<>0;AANTAL.ALS($A$2:$A2;$A2);0)
 =ALS(D3<>0;AANTAL.ALS($A$2:$A3;$A3);0)

Telt het aantal keren dat het bonnummer uit kolom A voorkomt in die kolom t/m de huidige regel. Dit vertelt mij hoeveelste vermelding het is van dit bonnummer. Sorteren is niet nodig, ik wil eigenlijk alleen maar weten of het bonnummer al eerder voorgekomen is of niet.
De opdracht wordt alleen uitgevoerd als die relevant is, d.w.z. wanneer de controle uit kolom D WAAR is, en wordt slechts op het bovenliggende deel van het bestand uitgevoerd. Dat levert een beetje winst op wat betreft rekentijd.

code:

  =ALS($D2>0;ALS(AANTAL.ALS($A$2:$A$1000;$A2)=AANTAL.ALS($D$2:$D$1000;$D2); ALS($E2=1;"Alles compleet";"Compleet regel  " & $E2);"wacht op andere regel");"wacht op onderdeel")

Oogt wat duizelingwekkend, maar valt reuze mee. Het hart van deze formule vergelijkt weer het aantal zelfde bonnummers in kolom A met die in kolom D. Om rekentijd te besparen gebeurt dat opnieuw alleen als kolom D WAAR is. Afhankelijk van de uitkomst onderscheidt de formule 4 situaties: (wanneer compleet) 1. 'Alles compleet' (bij eerste vermelding bonnummer), 2. 'Compleet regel 2,3,4' bij volgende zelfde bonnummers, (indien incompleet) 3. 'wacht op andere regel' wanneer het aantal bonnummers ongelijk is, 4. 'wacht op onderdeel' als de regel incompleet is.

Dit werkt redelijk snel en geeft je de gezochte functionaliteit. Je kan filteren op = 'wacht op onderdeel', <> 'wacht*' voor alle complete regels/sets, of op = 'Alles compleet' voor een lijst met unieke bonnummers.

Waarmee ik hoop beweringen als 'limitaties van Excel' en 'Access voor nodig' naar de prullenbak te hebben verwezen.