[Excel] Het grote Excel vragen topic #38

quote:

Op maandag 30 september 2013 13:15 schreef PAAC het volgende:
Leuk probleem, alles in één kolom krijgen lukt makkelijk, nu nog dubbelen eruit halen... (eerst maar weer eens aan het werk trouwens)

die dubbelen eruithalen kost gewoon veel cpu door de aantal.als of andere zoek opties

Solved! Gaat nu weer sneller. (alleen lijkt het nu niet zo door de aselect procedure)
edit: mmmmm blijft nog steeds redelijk intensief als is het een stuk minder. Veel betere opties kan ik 123 niet bedenken nu



er even vanuit gaande dat je de data makkelijk in 1 kolom krijgt zoals eerder aangegeven

zie file
http://www.uploadmb.com/dw.php?id=1380546801


whatskeburt?
Edit: oeps! doorvoerfout in laatste kolom (H) voor de aantal.als. Bereik klopt niet in file en plaatje. moet zijn zoals bij KOL H staat hierboven

note: kolom E en F kunnen ook samengevoegd worden, uiteraard. Dan is dat de enige hulpkolom
note2: kan alleen gebruikt worden met getallen. met tekst niet helaas want daar werkt min() niet op

[ Bericht 5% gewijzigd door Holy_Goat op 30-09-2013 15:33:50 ]

Met hulpkolommen erbij heb ik iets dat het lijkt te filtreren(op een hele kleine test matrix)...
Straks testen hoe snel het werkt

quote:

Op maandag 30 september 2013 16:47 schreef qu63 het volgende:

[..]

Mja, in mijn geval gaat het dus om de zoekwoorden die gebruikt worden om op de site te komen..

We puzzelen vrolijk verder

Het volgende lijkt redelijk snel te werken(sommatie van aantallen komt op 5000 neer).

Ik zet alleen de eerste cel neer en de rest kan gekopieerd worden.
Array A1: J500 = array met 5000 unieke waarden
Kolom N = tussenkolom met alle waarden in één kolom
Kolom M = tussenkolom met indices
Kolom O = kolom met Unieke waarden
Cell S = cell met aantal rijen (ofwel 500)

1. Zet alle kolommen in één grote kolom(N) met een lengte van 5000 rijen
=INDEX($A$1:$J$500;ALS(REST(AANTALARG($N$1:N1);$S$1)=0;$S$1;REST(AANTALARG($N$1:N1);$S$1));AFRONDEN.BOVEN(AANTALARG($N$1:N1)/$S$1;1))

2. Als de unieke string voor komt, dan voorgaande waarde, anders voorgaande waarde + 1
=ALS(AANTAL.ALS($N$2:N2;N2)>=2;M1;M1+1)

(Plaats deze links van de kolom met resultaten, door deze stap krijgt iedere unieke waarde zijn eigen unieke index(en dubbele waarden de index van de voorgaande waarde) welke opgezocht kan worden met vert.zoeken)

3. Zoek voor iedere index de eerste waarde
=VERT.ZOEKEN(AANTALARG($N$2:N2);$M$2:$N$5001;2;0)

(Aangezien de eerste index uniek is, wordt hierdoor automatisch de juiste waarde genomen en alleen unieke waarden in de kolom geplaatst)

[ Bericht 2% gewijzigd door PAAC op 30-09-2013 21:40:03 ]

quote:

Op dinsdag 1 oktober 2013 02:43 schreef qu63 het volgende:

[..]

Waar zouden 1, 2 en 3 geplaatst moeten worden? Als je 1 in N1 plaatst krijg je een kringverwijzing, en als je em in N2 zet dan mist ie een keyword in mijn sample..

Kan je jouw sheet uploaden? Dan kan ik em zelf bewerken en dan weer uploaden om mijn aanpassingen te laten zien..

Je hebt gelijk, vanochtend bedacht ik me al dat ik headers had gebruikt.
Ofwel N1 = header en N2 is begin van de kolom.

Als alternatief kun je ook $A$1:A1 gebruiken bijvoorbeeld.
Dit alternatief werkt toch niet...

[ Bericht 2% gewijzigd door PAAC op 01-10-2013 14:23:57 ]

quote:

Op dinsdag 1 oktober 2013 08:57 schreef PAAC het volgende:

[..]

Je hebt gelijk, vanochtend bedacht ik me al dat ik headers had gebruikt.
Ofwel N1 = header en N2 is begin van de kolom.

Als alsternatief kun je ook $A$1:A1 gebruiken bijvoorbeeld.

doe eens uploa-deeeen

quote:

Op dinsdag 1 oktober 2013 10:59 schreef Holy_Goat het volgende:

[..]

doe eens uploa-deeeen

Moest toevallig nog even naar huis, anders was het vanavond pas geweest
http://filedump.org/files/KHEGGclr1380629920.html

quote:

Op dinsdag 1 oktober 2013 14:28 schreef qu63 het volgende:

[..]

Dit lijkt te werken idd! Nog even verder puzzelen

Welke puzzelstukjes moeten er dan nog opgelost worden?

quote:

Op dinsdag 1 oktober 2013 14:59 schreef qu63 het volgende:

[..]

Nou..

Het werkt, dat vooropgesteld Het werkt goed zelfs
Maar als er lege cellen in de matrix zitten dan is '0' ook een uniek resultaat en dan wordt de rij unieke resultaten onderbroken door een 0. Gelukkig gaat ie daarna wel weer verder

Daarnaast wordt de matrix verticaal uitgelezen terwijl ik em horizontaal invul. Dat zorgt er voor dat de lijst unieke resultaten anders opgebouwd wordt, iets waar ik met mijn verwerking rekening moet houden

Maar haske bedankt iig!

Ahh, ik ging uit van een vooraf ingevuld blok met data
Maar mooi dat het helpt.

Had ook nog een kleine aanpassing gemaakt aan mijn algoritme, toen ik wist dat het niet met getallen maar met tekst moest Benieuwd of dat ook ongeveer voldoet

http://www.uploadmb.com/dw.php?id=1380633186

quote:

Op maandag 30 september 2013 20:30 schreef PAAC het volgende:
Het volgende lijkt redelijk snel te werken(sommatie van aantallen komt op 5000 neer).

Ik zet alleen de eerste cel neer en de rest kan gekopieerd worden.
Array A1: J500 = array met 5000 unieke waarden
Kolom N = tussenkolom met alle waarden in één kolom
Kolom M = tussenkolom met indices
Kolom O = kolom met Unieke waarden
Cell S = cell met aantal rijen (ofwel 500)

1. Zet alle kolommen in één grote kolom(N) met een lengte van 5000 rijen
=INDEX($A$1:$J$500;ALS(REST(AANTALARG($N$1:N1);$S$1)=0;$S$1;REST(AANTALARG($N$1:N1);$S$1));AFRONDEN.BOVEN(AANTALARG($N$1:N1)/$S$1;1))

2. Als de unieke string voor komt, dan voorgaande waarde, anders voorgaande waarde + 1
=ALS(AANTAL.ALS($N$2:N2;N2)>=2;M1;M1+1)

(Plaats deze links van de kolom met resultaten, door deze stap krijgt iedere unieke waarde zijn eigen unieke index(en dubbele waarden de index van de voorgaande waarde) welke opgezocht kan worden met vert.zoeken)

3. Zoek voor iedere index de eerste waarde
=VERT.ZOEKEN(AANTALARG($N$2:N2);$M$2:$N$5001;2;0)

(Aangezien de eerste index uniek is, wordt hierdoor automatisch de juiste waarde genomen en alleen unieke waarden in de kolom geplaatst)

Kun je m trouwens eens uitleggen? Vind em moeilijk te snappen. Snap je mijn 'methode' ook een beetje? Waarom zou mijn methode nog CPU vriendelijker zijn eigenlijk? (zo lijkt het nu)

Kan je ook gezamelijke filters of slicers toevoegen aan een draaigrafiek?

Ik heb nu een aantal draaigrafieken, waarbij ik ze allemaal tegelijk gegevens voor een bepaalde categorie wil laten zien. Voor draaitabellen lukt dit wel met behulp van slicers, maar bij draaigrafieken lijkt het niet te kunnen?

quote:

Op dinsdag 1 oktober 2013 15:25 schreef Holy_Goat het volgende:

[..]

Kun je m trouwens eens uitleggen? Vind em moeilijk te snappen. Snap je mijn 'methode' ook een beetje? Waarom zou mijn methode nog CPU vriendelijker zijn eigenlijk? (zo lijkt het nu)

Jou methode bekijk ik vanavond waarschijnlijk (ben nu ook aan het werk namelijk).

Kort uitgelegd
1. Zet alles in één kolom (deze noem ik voor de makkelijkheid "DataKolom").

2. Zorg ervoor dat waarden in de DataKolom een uniek ID-nr krijgen(kolom moet links staan van DataKolom).
Als een waarde in de DataKolom meerdere keren voor komt(= niet meer uniek), dan krijgt deze de voorgaande ID-nr en wanneer deze voor de eerste keer voor komt(= nieuwe unieke waarde) wordt de ID-nr met één opgehoogd.

3. Zoek de unieke waarde op mbv de ID-nr's.
Wanneer een ID-nr in stap 2 verandert, betekend dit automatisch dat op dezelfde rij in de kolom ernaast een unieke waarde staat. Wanneer je dit opzoekt met vert.zoeken en "[benaderen] = 0", dan geeft ie de eerste waarde behorend bij de teller "AANTALARG($N$2:N2)".

Dus in de eerste rij van de unieke kolom zoekt ie naar ID-nr = 1, op de tweede rij zoekt ie ID-nr = 2 etc. Als er meerdere dezelfde ID-nr's zijn(zie stap 2), dan neemt hij door "[benaderen] = 0" de eerste rij van de meerdere waarden en geeft hierdoor dan automatisch een unieke waarde terug die ernaast staat.

[ Bericht 0% gewijzigd door PAAC op 01-10-2013 15:59:27 ]

quote:

Op dinsdag 1 oktober 2013 16:05 schreef qu63 het volgende:

[..]

Hij is inderdaad schneller! Alleen je uitleg in kolom K klopt niet geloof ik, de functies die je daar noemt staan niet meer in E, F, G en H..

Grappig ook dat jij =ALS(H2<>"";andere fomule;"") doet, ik draai t altijd om.. =ALS(H2="";"";andere formule)

Nu nog even kijken of ik jouw sheet kan combineren met die van PAAC

_{PS: Mooie lijst met 5letter-woorden!}

uitleg klopt idd niet meer, had ik niet meer aangepast na aanpassing

quote:

Op dinsdag 1 oktober 2013 15:52 schreef PAAC het volgende:

[..]

Jou methode bekijk ik vanavond waarschijnlijk (ben nu ook aan het werk namelijk).

Kort uitgelegd
1. Zet alles in één kolom (deze noem ik voor de makkelijkheid "DataKolom").

2. Zorg ervoor dat waarden in de DataKolom een uniek ID-nr krijgen(kolom moet links staan van DataKolom).
Als een waarde in de DataKolom meerdere keren voor komt(= niet meer uniek), dan krijgt deze de voorgaande ID-nr en wanneer deze voor de eerste keer voor komt(= nieuwe unieke waarde) wordt de ID-nr met één opgehoogd.

3. Zoek de unieke waarde op mbv de ID-nr's.
Wanneer een ID-nr in stap 2 verandert, betekend dit automatisch dat op dezelfde rij in de kolom ernaast een unieke waarde staat. Wanneer je dit opzoekt met vert.zoeken en "[benaderen] = 0", dan geeft ie de eerste waarde behorend bij de teller "AANTALARG($N$2:N2)".

Dus in de eerste rij van de unieke kolom zoekt ie naar ID-nr = 1, op de tweede rij zoekt ie ID-nr = 2 etc. Als er meerdere dezelfde ID-nr's zijn(zie stap 2), dan neemt hij door "[benaderen] = 0" de eerste rij van de meerdere waarden en geeft hierdoor dan automatisch een unieke waarde terug die ernaast staat.

Ah, het echte verschil zit em dus in 2. Ik begon eerst ook met 'unieke waarden' maar kwam er toen achter dat het me eigenlijk geen reet interesseerde of die uniek was, alleen maar of hij 'niet eerder' geweest was. Vervolgens de index als uitkomst noteren, en die weergeven in unieke lijst met verschuiving(kleinste(...;...)) , dus waar jij zoekt naar een ID zoek ik naar de kleinste waarde welke overeenkomt met een bepaalde plaats. En dan schijnt het dus voordelig te zijn om kleinste() te gebruiken boven vert.zoeken (die is langzamer). Sowieso, overal waar je kunt met 'index' e 'vergelijken' werken ipv vert.zoeken of horiz.zoeken. Is mss iets lastiger maar sowieso sneller omdat vert.zoeken achter de schermen meer doet dan de combi van index en vergelijken.

Wat ik nog wel jammer vind an sich is dat we een hulp kolom nodig hebben om de N x M Matrix om te zetten naar een lange kolom met MxN items. Zou ook leuk zijn daar eens over te brainstormen

quote:

Op dinsdag 1 oktober 2013 16:17 schreef Holy_Goat het volgende:
Wat ik nog wel jammer vind an sich is dat we een hulp kolom nodig hebben om de N x M Matrix om te zetten naar een lange kolom met MxN items. Zou ook leuk zijn daar eens over te brainstormen

Zeg, ik wil ook nog wat vrije tijd overhouden

quote:

Op dinsdag 1 oktober 2013 16:19 schreef PAAC het volgende:

[..]

Zeg, ik wil ook nog wat vrije tijd overhouden

Sja. Is study evasive behavior hier. Maar wel interessant