[Excel] Het grote Excelvragentopic #43

quote:

Op donderdag 30 april 2015 16:25 schreef qu63 het volgende:

[..]

Hiermee? http://blog.contextures.c(...)cel-index-and-match/

In mijn ogen gaat dat niet helpen. Het is zo traag vanwege de array formule op elke regel wordt toegepast. Daar moet je vanaf. .

quote:

Op donderdag 30 april 2015 14:23 schreef nils7 het volgende:
Nu heb ik een sumproduct formule gevonden die voor mij per unieke ID een sorteringsnummer zet qua datum.
Dit is de formule die ik heb
=IF(G2="","",SUMPRODUCT(--(G:G=G2)*(M2>M:M))+1)
Met in kolom G de unieke ID's en in kolom M de datums.

Je tekst klopt volgens mij niet helemaal of ik moet het helemaal verkeerd begrijpen. In kolom G staat niet je unique ID, want er staat wel een ID maar die komt op meerdere keren voor volgens mij. En in M staat dan je datum waarin die ID gebruikt is.

Dus gegeven het feit dat in G je IDs staan en in M je datums, moet je een lijstje maken waarin elke ID inderdaad maar 1 keer voorkomt. Alleen voor dit lijstje moet je array formules toepassen, niet als hulpkolom in je hele dataset.

De formule moet zijn per unique ID:
=MIN(G:G="je unique product ID",M:M)
Dit is je eindresultaat. Wel als array formule invullen natuurlijk.

(uitleg volgt als dit is wat je zoekt en mooi maken doe je via
=IFERROR(MIN(G:G="je unique product ID",M:M),"")
)

[ Bericht 1% gewijzigd door snabbi op 01-05-2015 00:21:22 ]

Weet iemand hoe ik de 2015 uit de formulebalk verwijder?
Wil uiteindelijk een grafiek maken op datums over verschillende jaren, dus het jaartal moet uit de formulebalk

Alvast bedankt!

quote:

Op vrijdag 1 mei 2015 11:51 schreef fetss het volgende:
[ afbeelding ]

Weet iemand hoe ik de 2015 uit de formulebalk verwijder?
Wil uiteindelijk een grafiek maken op datums over verschillende jaren, dus het jaartal moet uit de formulebalk

Alvast bedankt!

Dan moet je alles op 1 en hetzelfde jaar zetten (2000 bv, want 29 feb moet ook een plaats krijgen). Stel je hebt een datumveld op K24, dan zet je op L24 de volgende formule: =DATUM(2000;MAAND(K24);DAG(K24)). Dan zullen de dag en maand van ongeacht welk jaar op K24 worden overgezet op dag-maand-2000.

[ Bericht 3% gewijzigd door Twentsche_Ros op 01-05-2015 13:37:58 ]

quote:

Op donderdag 30 april 2015 23:45 schreef snabbi het volgende:
If Target.Address = "$XFC$1" Or Target.Address = "$C$5" Or _
Target.Column = 3 Or Target.Column = 8 Then

Hij doet het nu . Heel erg bedankt voor de hulp!

quote:

Op vrijdag 1 mei 2015 00:13 schreef snabbi het volgende:

[..]

In mijn ogen gaat dat niet helpen. Het is zo traag vanwege de array formule op elke regel wordt toegepast. Daar moet je vanaf. .

[..]

Je tekst klopt volgens mij niet helemaal of ik moet het helemaal verkeerd begrijpen. In kolom G staat niet je unique ID, want er staat wel een ID maar die komt op meerdere keren voor volgens mij. En in M staat dan je datum waarin die ID gebruikt is.

Dus gegeven het feit dat in G je IDs staan en in M je datums, moet je een lijstje maken waarin elke ID inderdaad maar 1 keer voorkomt. Alleen voor dit lijstje moet je array formules toepassen, niet als hulpkolom in je hele dataset.

De formule moet zijn per unique ID:
=MIN(G:G="je unique product ID",M:M)
Dit is je eindresultaat. Wel als array formule invullen natuurlijk.

(uitleg volgt als dit is wat je zoekt en mooi maken doe je via
=IFERROR(MIN(G:G="je unique product ID",M:M),"")
)

Ja klopt een ID die wel meer keer in de lijst voorkomt, ik ga het eens proberen.

quote:

Op vrijdag 1 mei 2015 11:51 schreef fetss het volgende:
[ afbeelding ]

Weet iemand hoe ik de 2015 uit de formulebalk verwijder?
Wil uiteindelijk een grafiek maken op datums over verschillende jaren, dus het jaartal moet uit de formulebalk

Alvast bedankt!

Wanneer je wilt kunnen rekenen met een datum, dan heeft Excel een jaartal nodig. Dus wanneer je in je grafiek de slimheid tussen 1-jan 2-jan (lege ruimte) 4-jan wil gebruiken, dan ontkom je niet aan een jaartal.

Is het slechts een tekstuele weergave, gebruik dan '1-mei

quote:

Op vrijdag 1 mei 2015 17:24 schreef nils7 het volgende:

[..]

Ja klopt een ID die wel meer keer in de lijst voorkomt, ik ga het eens proberen.

Ik was wat snel in mijn post en heb nu ook een voorbeeld online gezet:
http://s000.tinyupload.com/?file_id=53690208357538775507

=IFERROR(MIN(IF(G:G=G2,M:M)),"")

Het bestand heeft twee tabbladen. De slechte manier waarbij je voor elke regel opnieuw gaat bepalen wat de eerste datum is. De slimme manier waarbij je dit 1 keer doet.

De enige manier om echt significant tijdwinst te halen is om het aantal array formules te beperken. Vandaar deze oplossing.

Is het mogelijk om in Excel in blad 1 een enorme lijst met namen te gooien die meerdere keren voorkomen, in blad 2 een lijst met een lijst met namen, en dan in blad 3 een kopie van blad 1 min alle regels die een naam hebben die in blad 2 voorkomt?

Dus bijvoorbeeld
blad 1
aa______zeist________oranje
bb______Rotterdam___geel
aa______zeist________geel
cc______Gouda_______roze

blad 2
bb

blad 3
aa______zeist________oranje
aa______zeist________geel
cc______Gouda_______roze

[ Bericht 18% gewijzigd door Arnold_fan op 12-05-2015 13:05:04 ]

quote:

Op dinsdag 12 mei 2015 12:52 schreef Arnold_fan het volgende:
Is het mogelijk om in Excel in blad 1 een enorme lijst met namen te gooien die meerdere keren voorkomen, in blad 2 een lijst met een lijst met namen, en dan in blad 3 een kopie van blad 1 min alle regels die een naam hebben die in blad 2 voorkomt?

Dus bijvoorbeeld
blad 1
aa______zeist________oranje
bb______Rotterdam___geel
aa______zeist________geel
cc______Gouda_______roze

blad 2
bb

blad 3
aa______zeist________oranje
aa______zeist________geel
cc______Gouda_______roze

Ongetwijfeld. Moet blad 2 op basis van blad 1 gegenereerd worden, of is dat handmatige invoer?

quote:

Op dinsdag 12 mei 2015 14:11 schreef Janneke141 het volgende:

[..]

Ongetwijfeld. Moet blad 2 op basis van blad 1 gegenereerd worden, of is dat handmatige invoer?

Blad 2 bouwt langzaam op, handmatig.

Blad 1 komt elke week binnen vanaf een externe bron, ook in Excel, maar omdat steeds dezelfde zaken hierin voorkomen wil ik na een check dit op blad 2 toevoegen zodat ik dit later in blad 3 niet meer ga tegenkomen in de weken erna. Op die manier blijft er een klein blad 3 over waar alleen zaken opstaan die ik wel wil zien.

quote:

Op dinsdag 12 mei 2015 12:52 schreef Arnold_fan het volgende:
Is het mogelijk om in Excel in blad 1 een enorme lijst met namen te gooien die meerdere keren voorkomen, in blad 2 een lijst met een lijst met namen, en dan in blad 3 een kopie van blad 1 min alle regels die een naam hebben die in blad 2 voorkomt?

Dus bijvoorbeeld
blad 1
aa______zeist________oranje
bb______Rotterdam___geel
aa______zeist________geel
cc______Gouda_______roze

blad 2
bb

blad 3
aa______zeist________oranje
aa______zeist________geel
cc______Gouda_______roze

In blad3 zet je bijvoorbeeld in cel A2 de volgende formule neer:
=ALS(ISFOUT(VERT.ZOEKEN(Blad1!A2;Blad2!A:A;1;ONWAAR));Blad1!A2;"")
In B2 wordt het iets als: =ALS(ISLEEG($A2);"";Blad1!B2) <-- dit kan je naar rechts kopieren.
Vervolgens kan je alles naar beneden kopieren.

Wat doet het:
Je kijkt in A2 of de waarde van Blad1!A2 voorkomt in de kolom A in blad2. Wanneer dit WEL zo is, dan geeft deze VERT.ZOEKEN functie een goed resultaat. Wanneer het NIET zo is, dan geeft de VERT.ZOEKEN formule een ERROR. De ISFOUT functie vangt zo'n ERROR op. Is er een fout (dus komt niet voor in blad2), dan moet hij de waarde van blad1 weergeven. Staat hij er wel op dan een lege cel.

Het enige "lelijke" effect is dat je krijgt:
- Je krijgt tussendoor lege regels te zien. Dus op de plek van je "bb" zie je nu dus een lege regel.
- Je krijgt een 0 te zien wanneer je in Blad3 wel een formule hebt staan, maar in Blad1 staat op die regel geen waarde.

Uiteraard kunnen we beide cosmetisch oplossen, maar eerst checken of dit is wat je zoekt.

Ik gebruik altijd Als.fout(). Zelfde resultaat?

Meh. Iemand hier toevallig ervaring met het maken van Dashboards? Ik zit vast, en niet zo'n beetje ook. (Al moet ik wel toegeven dat ik nooit gebruik heb gemaakt van formules in excel en ik mezelf in het diepe heb gegooid). Ik doe mijn best om even uit te leggen wat ik nu heb staan.

Ik heb een enorme lijst met namen, een datasheet met meerdere tabbladen en de data die daarin staat is gefilterd op 4 landen.
De bedoeling is uiteindelijk dat ik kan selecteren per naam en vervolgens per land de data kan inzien. Binnen het dashboard verspringt dan de data mooi in een diagram enzovoort.

Die lijst met namen staan weergegeven in een controle tabblad, samen met een getal. In een dummy tabel geeft dit getal aan hoeveel regels hij naar beneden moet voor de juiste data bij de juiste naam.

Maar dan moet het nog worden weergegeven per land en ieder land moet aan te klikken zijn via een button. Het idee was om per land een aparte verschuiving te maken en zo weer te geven, maar dit schijnt niet te werken. Een ander idee was om dan twee aparte invul velden te gebruiken en dan die te verwijzen - 1 voor de verwijzing naar de hoeveelheid rijen, en 1 voor de hoeveelheid kolommen. Ik heb alleen geen idee hoe dit verder in elkaar zit.

Kortom, hoe link ik alles netjes aan elkaar zodat ik kan inzien wat iedere naam per land aan data heeft liggen?

quote:

Op woensdag 13 mei 2015 11:03 schreef Crientj het volgende:
Meh. Iemand hier toevallig ervaring met het maken van Dashboards? Ik zit vast, en niet zo'n beetje ook. (Al moet ik wel toegeven dat ik nooit gebruik heb gemaakt van formules in excel en ik mezelf in het diepe heb gegooid). Ik doe mijn best om even uit te leggen wat ik nu heb staan.

Ik heb een enorme lijst met namen, een datasheet met meerdere tabbladen en de data die daarin staat is gefilterd op 4 landen.
De bedoeling is uiteindelijk dat ik kan selecteren per naam en vervolgens per land de data kan inzien. Binnen het dashboard verspringt dan de data mooi in een diagram enzovoort.

Die lijst met namen staan weergegeven in een controle tabblad, samen met een getal. In een dummy tabel geeft dit getal aan hoeveel regels hij naar beneden moet voor de juiste data bij de juiste naam.

Maar dan moet het nog worden weergegeven per land en ieder land moet aan te klikken zijn via een button. Het idee was om per land een aparte verschuiving te maken en zo weer te geven, maar dit schijnt niet te werken. Een ander idee was om dan twee aparte invul velden te gebruiken en dan die te verwijzen - 1 voor de verwijzing naar de hoeveelheid rijen, en 1 voor de hoeveelheid kolommen. Ik heb alleen geen idee hoe dit verder in elkaar zit.

Kortom, hoe link ik alles netjes aan elkaar zodat ik kan inzien wat iedere naam per land aan data heeft liggen?

Wat tips:
Gebruik altijd 1 tabblad en maak een degelijke database, die alle gegevens bevat.
En verdiep je in de wereld van draaitabellen.
De omschrijving die jij nu geeft, vind ik te verwarrend.

quote:

Op woensdag 13 mei 2015 11:41 schreef Twentsche_Ros het volgende:

[..]

Wat tips:
Gebruik altijd 1 tabblad en maak een degelijke database, die alle gegevens bevat.
En verdiep je in de wereld van draaitabellen.
De omschrijving die jij nu geeft, vind ik te verwarrend.

Alles staat per grafiek op een ander tabblad. Vervolgens per naam en per land. Het is teveel data om in 1 tabblad te doen.

quote:

Op woensdag 13 mei 2015 11:44 schreef Crientj het volgende:

[..]

Alles staat per grafiek op een ander tabblad. Vervolgens per naam en per land. Het is teveel data om in 1 tabblad te doen.

Dat lijkt me sterk..

2^20 rijen (= 1.048.576 rijen)
2^14 kolommen (=16.384 kolommen)

te weinig???

Ik neem toch wel aan dat je niet dezelfde gegevens meerdere keren moet invoeren.

quote:

Op woensdag 13 mei 2015 11:51 schreef Twentsche_Ros het volgende:

[..]

Dat lijkt me sterk..

2^20 rijen (= 1.048.576 rijen)
2^14 kolommen (=16.384 kolommen)

te weinig???

Ik neem toch wel aan dat je niet dezelfde gegevens meerdere keren moet invoeren.

Nee, dit is hoe het aangeleverd is. Kan/mag daar verder niets aan veranderen. Het zou best wel kunnen werken op de een of andere manier, ik heb alleen geen idee hoe.
Zou het iets uithalen als ik de knop per land iedere keer opnieuw moet verwijzen naar welke tabel ie nodig heeft? Dat je eh, iets krijgt als;

Land 1 - Grafiek1 - Grafiek2
Land 2 - Grafiek1 - Grafiek2

Grootste probleem is dat ik niet zie hoe je meerdere gegevens in 1 veld gaat proppen.

quote:

Op woensdag 13 mei 2015 12:50 schreef Basp1 het volgende:

[..]

Waarom wil je meerdere gegevens in 1 veld proppen?

Ja weet ik niet. Ik weet niet zo goed hoe ik hiermee verder moet denk ik .

quote:

Op woensdag 13 mei 2015 00:41 schreef Piger het volgende:
Ik gebruik altijd Als.fout(). Zelfde resultaat?

In dit geval niet.
Als.fout toont het resultaat wanneer hij wel gevonden is, terwijl je juist wil dat hij niets toont wanneer hij in Blad2 staat. Dus als je een soort inverse van een als.fout zou kunnen maken, dan zou het wel werken. (als.nietfout?)

quote:

Op woensdag 13 mei 2015 11:03 schreef Crientj het volgende:
Meh. Iemand hier toevallig ervaring met het maken van Dashboards? Ik zit vast, en niet zo'n beetje ook. (Al moet ik wel toegeven dat ik nooit gebruik heb gemaakt van formules in excel en ik mezelf in het diepe heb gegooid). Ik doe mijn best om even uit te leggen wat ik nu heb staan.

Ik heb een enorme lijst met namen, een datasheet met meerdere tabbladen en de data die daarin staat is gefilterd op 4 landen.
De bedoeling is uiteindelijk dat ik kan selecteren per naam en vervolgens per land de data kan inzien. Binnen het dashboard verspringt dan de data mooi in een diagram enzovoort.

Die lijst met namen staan weergegeven in een controle tabblad, samen met een getal. In een dummy tabel geeft dit getal aan hoeveel regels hij naar beneden moet voor de juiste data bij de juiste naam.

Maar dan moet het nog worden weergegeven per land en ieder land moet aan te klikken zijn via een button. Het idee was om per land een aparte verschuiving te maken en zo weer te geven, maar dit schijnt niet te werken. Een ander idee was om dan twee aparte invul velden te gebruiken en dan die te verwijzen - 1 voor de verwijzing naar de hoeveelheid rijen, en 1 voor de hoeveelheid kolommen. Ik heb alleen geen idee hoe dit verder in elkaar zit.

Kortom, hoe link ik alles netjes aan elkaar zodat ik kan inzien wat iedere naam per land aan data heeft liggen?

Welke versie van Excel gebruik je.
Eigenlijk is een pivot table met een Slicer heel geschikt voorzoiets

quote:

Op woensdag 13 mei 2015 23:25 schreef snabbi het volgende:

[..]

In dit geval niet.
Als.fout toont het resultaat wanneer hij wel gevonden is, terwijl je juist wil dat hij niets toont wanneer hij in Blad2 staat. Dus als je een soort inverse van een als.fout zou kunnen maken, dan zou het wel werken. (als.nietfout?)

eh nee. als fout geeft een resultaat wanneer verticaal zoeken niets vindt. Dus dan zou dit hetzelfde zijn.

quote:

Op donderdag 14 mei 2015 00:56 schreef Piger het volgende:

[..]

eh nee. als fout geeft een resultaat wanneer verticaal zoeken niets vindt. Dus dan zou dit hetzelfde zijn.

=ALS.FOUT(VERT.ZOEKEN(Blad1!A2;Blad2!A:A;1;ONWAAR);")
In de geschetste casus moet in Blad3 bb NIET getoond worden wanneer deze WEL in Blad2 staat.

Wanneer bb in Blad2 staat (en ook op Blad1!A2) dan levert de vertikaal zoeken een positief resultaat op, namelijk bb. Hij gaat hem dan WEL tonen. Dit wil ik niet. Wanneer hij op Blad2 staat moet hij juist niets tonen.

Op het moment dat bb niet in Blad2 staat en wel op Blad1!A2 dan gaat vertikaal zoeken hem niet vinden en dus komt die ERROR en zal hij de "" tonen. Dit wil ik niet. Wanneer hij niet op Blad2 staat moet hij hem juist wel tonen.

Ik ga de uitgebreide formule proberen. Bij de andere manier krijg ik dus een true or false achter de regel? Daar zou ik ook op kunnen filteren natuurlijk .... Niet exact wat ik bedoelde maar met hetzelfde resultaat. Dank beiden!

quote:

Op dinsdag 12 mei 2015 23:40 schreef snabbi het volgende:

[..]

In blad3 zet je bijvoorbeeld in cel A2 de volgende formule neer:
=ALS(ISFOUT(VERT.ZOEKEN(Blad1!A2;Blad2!A:A;1;ONWAAR));Blad1!A2;"")
In B2 wordt het iets als: =ALS(ISLEEG($A2);"";Blad1!B2) <-- dit kan je naar rechts kopieren.
Vervolgens kan je alles naar beneden kopieren.

Wat doet het:
Je kijkt in A2 of de waarde van Blad1!A2 voorkomt in de kolom A in blad2. Wanneer dit WEL zo is, dan geeft deze VERT.ZOEKEN functie een goed resultaat. Wanneer het NIET zo is, dan geeft de VERT.ZOEKEN formule een ERROR. De ISFOUT functie vangt zo'n ERROR op. Is er een fout (dus komt niet voor in blad2), dan moet hij de waarde van blad1 weergeven. Staat hij er wel op dan een lege cel.

Het enige "lelijke" effect is dat je krijgt:
- Je krijgt tussendoor lege regels te zien. Dus op de plek van je "bb" zie je nu dus een lege regel.
- Je krijgt een 0 te zien wanneer je in Blad3 wel een formule hebt staan, maar in Blad1 staat op die regel geen waarde.

Uiteraard kunnen we beide cosmetisch oplossen, maar eerst checken of dit is wat je zoekt.

Ik heb het ingevoerd, het eerste werkt inderdaad prima, dus ik krijg witte velden in de A kolom. De B en verder blijven gevuld, klaarblijkelijk ziet Excel deze velden niet als leeg waardoor hij overal de rest van de regels blijft weergeven.

Als de witregels automatisch zouden kunnen verdwijnen zou ik dat echt heel mooi vinden, ik heb nu deze AAAAAAA genoemd en een filter toegevoegd dat hierop werkt, alleen moet ik nu af en toe het filter opnieuw toepassen omdat hij de nieuwe AAAAAAA niet automatisch gaat filteren.

Maar nogmaals, als het automatisch kan zou dat echt top zijn, dan kan dat ook nooit vergeten worden.