[Excel] Het grote Excelvragentopic #50

quote:

Op woensdag 8 maart 2023 21:46 schreef Tegan het volgende:
Ik wilde ook AANTALLEN.ALS voorstellen maar dacht halverwege het typen dat je aantal standplaatsen keer aantal jaartallen teveel formules vond.

Dat is ook zo, maar ik ga heel even een poging wagen. Het is nog vrij snel te genereren (in elk geval sneller dan alle hulpkolommen die ik zelf in gedachten had) en als het een klein beetje werkbaar blijft dan is het nog wel een uurtje prutsen waard.

Een draaitabel kan wel met voor ieder mogelijk jaartal een berekend veld lijkt me.

Nee, in mijn ogen is het probleem met een draaitabel dat je daarvoor eigenlijk voor die eerste regel die van 1986 gaat tot 2005, dat je geen regels hebt waar ook 1987 in staat. Je moet dus eigenlijk een soort lijst bouwen in de regel die je hebt en hij moet naar die lijst kijken.

De echte oplossing ligt dan ook in PowerQuery waarin je stiekem die data-set op de achtergrond kan bouwen, maar dat leek mij te ingewikkeld om te plaatsen. Zie dit youtube filmpje hoe je dat kan oplossen.. maar ik weet zeker dat je deze oplossing niet echt gaat gebruiken


[ Bericht 4% gewijzigd door snabbi op 09-03-2023 00:29:22 ]

quote:

Op donderdag 9 maart 2023 00:21 schreef snabbi het volgende:
Nee, in mijn ogen is het probleem met een draaitabel dat je daarvoor eigenlijk voor die eerste regel die van 1986 gaat tot 2005, dat je geen regels hebt waar ook 1987 in staat. Je moet dus eigenlijk een soort lijst bouwen in de regel die je hebt en hij moet naar die lijst kijken.

De echte oplossing ligt dan ook in PowerQuery waarin je stiekem die data-set op de achtergrond kan bouwen, maar dat leek mij te ingewikkeld om te plaatsen. Zie dit youtube filmpje hoe je dat kan oplossen.. maar ik weet zeker dat je deze oplossing niet echt gaat gebruiken

Generate Dates between Start and End Date in Power Query

Je kan per jaartal een berekend veld (in de draaitabel dus) maken met in de formule daarvan dus direct 1987 i.p.v. een verwijzing, en die vergelijken met de twee datums wat wel verwijzingen zijn. Zo is een draaitabel mogelijk.

quote:

Op donderdag 9 maart 2023 07:31 schreef Tegan het volgende:

[..]
Je kan per jaartal een berekend veld (in de draaitabel dus) maken met in de formule daarvan dus direct 1987 i.p.v. een verwijzing, en die vergelijken met de twee datums wat wel verwijzingen zijn. Zo is een draaitabel mogelijk.

Het tonen van alle jaartallen op de horizontale of verticale as - dat is iets wat ik makkelijk kan aandragen, maar om daarbij voor die standplaatsen een true/false uitkomst te krijgen op basis van die start en einddatum.. dat zou ik niet kunnen. Dus mocht jij die formule hebben van dat berekende veld, dan zouden we de oplossing hebben.

quote:

Op donderdag 9 maart 2023 13:13 schreef snabbi het volgende:

[..]
Het tonen van alle jaartallen op de horizontale of verticale as - dat is iets wat ik makkelijk kan aandragen, maar om daarbij voor die standplaatsen een true/false uitkomst te krijgen op basis van die start en einddatum.. dat zou ik niet kunnen. Dus mocht jij die formule hebben van dat berekende veld, dan zouden we de oplossing hebben.

Ik kreeg dit werkend met ALS(EN(JAAR(VeldDatum1)<=1987;JAAR(VeldDatum2)>=1987);1;0) en dan herhalen voor ieder jaartal.

Ik heb de AANTALLEN.ALS-optie van @snabbi gebruikt en die kreeg ik vrij makkelijk aan het werk. In overleg met de eigenaar van het bestand heb ik alles t/m 2021 via kopiëren/waarden plakken veranderd in getallen om het ding wat minder traag te maken, aangezien er toch geen data in het verleden meer bij komen. Dank voor het meedenken.

En het geposte filmpje ga ik nog wel een keer kijken als ik tijd heb. Altijd interessant.

quote:

Op donderdag 9 maart 2023 19:54 schreef Tegan het volgende:

[..]
Ik kreeg dit werkend met ALS(EN(JAAR(VeldDatum1)<=1987;JAAR(VeldDatum2)>=1987);1;0) en dan herhalen voor ieder jaartal.

Stappenplan:
1) Maak een Pivot Table via de Ribbon Insert -> PivotTable. In het popup scherm vink je de onderste optie aan "Add this data to the Data Model".
2) Ga via de Ribbon Data naar de optie: Manage Data Model (is een groen icoontje).
3) Klik op de Ribbon Design en vervolgens op Date Table -> New.
4) Je ziet een aantal definities met dagen in het jaar. Zorg dat je ergens in de half zichtbare kolom met een formule toevoegt: =Day(date)
Zodra je op enter drukt dan zie je ook dat ie netjes de dagen van de maand toont 1,2,3 etc.
5) Rechtermuistoets op de kolom header -> Rename Column, bijvoorbeeld naar Eigen_dag
6) Onder de Ribbon Design klik je op Create Relationships. Zorg ervoor dat je bij de bovenste Calendar de kolom selecteert met Date, daaronder kies je voor Range en dan Startdatum.
7) klik de power pivot weg... eventueel moet je nu in het PivotTable Fields menu klikken op More tables om hem te updaten.
8) Wat je nu ziet is dat je zowel een tabel Calendar hebt, en ook een tabel Range (waar je je normale velden in hebt staan).
9) Onder Calendar is er een optie "More Fields" waardoor je Year op de horizontale of verticale as kunt plaatsen. Eigenlijk moet je denk ik Date hebben om de exacte match te kunnen maken op 31-12, maar ik wilde in ieder geval die dag in de maand en de maand zelf als eventuele Filter opties in de Pivot table hebben zodat je lekker kunt klooien ermee.

Nu zou je dus die berekende velden moeten toevoegen zoals jij dat zegt (eigenlijk kan dit ook in de Power Pivot, maar daar krijg ik het niet werkend).
in plaats van 1987 zou je nu gewoon Calendar[Year] moeten kunnen gebruiken

quote:

Op donderdag 9 maart 2023 20:41 schreef snabbi het volgende:

[..]
Stappenplan:
1) Maak een Pivot Table via de Ribbon Insert -> PivotTable. In het popup scherm vink je de onderste optie aan "Add this data to the Data Model".
2) Ga via de Ribbon Data naar de optie: Manage Data Model (is een groen icoontje).
3) Klik op de Ribbon Design en vervolgens op Date Table -> New.
4) Je ziet een aantal definities met dagen in het jaar. Zorg dat je ergens in de half zichtbare kolom met een formule toevoegt: =Day(date)
Zodra je op enter drukt dan zie je ook dat ie netjes de dagen van de maand toont 1,2,3 etc.
5) Rechtermuistoets op de kolom header -> Rename Column, bijvoorbeeld naar Eigen_dag
6) Onder de Ribbon Design klik je op Create Relationships. Zorg ervoor dat je bij de bovenste Calendar de kolom selecteert met Date, daaronder kies je voor Range en dan Startdatum.
7) klik de power pivot weg... eventueel moet je nu in het PivotTable Fields menu klikken op More tables om hem te updaten.
8) Wat je nu ziet is dat je zowel een tabel Calendar hebt, en ook een tabel Range (waar je je normale velden in hebt staan).
9) Onder Calendar is er een optie "More Fields" waardoor je Year op de horizontale of verticale as kunt plaatsen. Eigenlijk moet je denk ik Date hebben om de exacte match te kunnen maken op 31-12, maar ik wilde in ieder geval die dag in de maand en de maand zelf als eventuele Filter opties in de Pivot table hebben zodat je lekker kunt klooien ermee.

Nu zou je dus die berekende velden moeten toevoegen zoals jij dat zegt (eigenlijk kan dit ook in de Power Pivot, maar daar krijg ik het niet werkend).
in plaats van 1987 zou je nu gewoon Calendar[Year] moeten kunnen gebruiken

Klinkt goed, wellicht binnenkort keertje mee spelen.

Datum 31-12-jaartal kun je zelf genereren met (uit mijn hoofd) DATUM(jaartal;12;31) waarbij 12 en 31 dus geen verwijzing zijn .

In excel (365) zit tegenwoordig een 'feature' dat je een menu krijgt bij de ctrl toets oid.
Hoe krijg ik dat teringding uit? Ik wil dat niet
Het soort me mateloos in mijn werk.

Dus weet iemand toevallig hoe dat uit te zetten is?

quote:

Op maandag 13 maart 2023 14:36 schreef marcb1974 het volgende:
In excel (365) zit tegenwoordig een 'feature' dat je een menu krijgt bij de ctrl toets oid.
Hoe krijg ik dat teringding uit? Ik wil dat niet
Het soort me mateloos in mijn werk.

Dus weet iemand toevallig hoe dat uit te zetten is?

Daar heb ik me ook mateloos aan gestoord, ja. Heeft mij ook even gekost voordat ik gevonden had waar ik die flauwekul uit kon zetten. Dansende paperclip, iemand?

In elk geval,


Het is één van deze twee dacht ik.

Dank, maar helaas lossen die het niet op.
Zit ook al hard te zoeken

Niet? Excel even afsluiten en opnieuw opstarten misschien?

Ik weet vrijwel zeker dat het een van die twee moet zijn. Je moet het echt uit kunnen zetten, want dat is mij namelijk ook gelukt

Zal eens helemaal herstarten.

quote:

Op maandag 13 maart 2023 15:04 schreef marcb1974 het volgende:
Zal eens helemaal herstarten.

Opties, Geavanceerd, Knippen kopiëren en plakken en dan het bovenste vinkje.

Dat moet hem denk ik zijn dan.

quote:

Op maandag 13 maart 2023 15:10 schreef Janneke141 het volgende:

[..]
Opties, Geavanceerd, Knippen kopiëren en plakken en dan het bovenste vinkje.

Dat moet hem denk ik zijn dan.

Yes, die is het,
dank je wel

Ik kom er zelf even niet meer uit helaas. Ik had een bestand gemaakt die per dag een tab bevat waarin informatie wordt ingevuld. Het gaat om bedragen en een id-nummer. Ik had voor iedere dag een viertal bedragen welke allemaal een eigen id-nummer moesten krijgen. Vervolgens heb ik een tabel gemaakt die de waarde uit die velden kopieert.
Nu had ik ook een methode bedacht om een soort van administratie hierop te doen en te berekenen hoeveel unieke nummers er in gebruik waren en met welk bedrag. Nu werkt dat niet meer omdat er is besloten dat een id-nummer meerdere keren op een dag maar ook meerdere dagen gebruikt kan worden.

Nu ben ik dus op zoek naar een manier uit een tabel (zie screen print) het aantal unieke id-nummers te berekenen, maar alleen als ze een waarde hebben hoger dan 0. Idealiter ook met de waarde per uniek id-nummer.
In mijn huidige formule berekent hij alleen de unieke argumenten, ongeacht de waarde (=AANTALARG(UNIEK(B2:B19))-ALS(AANTAL.LEGE.CELLEN(B2:B19)>0;1;0)).



Iemand hier die de gouden tip heeft? De tabel die ik er eigenlijk een soort van tussen geplakt heb is geen must, dus een functie direct over de onderliggende tabjes is ook zeker een optie! Hiernaast heb ik nog een tabel waarbij men als ze het unieke id-nummer invoeren het bedrag moet krijgen wat er aan gekoppeld hoort te zijn.

Ik zou dit denk ik even met een hulpkolommetje oplossen.

Waarom zoek je het antwoord '6'? Omdat er 6 sealbags zijn met een gestort bedrag? Want dan zal er toch ergens een verwijzing naar kolom E in je formule moeten zitten.

[ Bericht 61% gewijzigd door Janneke141 op 20-03-2023 15:30:44 ]

quote:

Op maandag 20 maart 2023 15:25 schreef Janneke141 het volgende:
Ik zou dit denk ik even met een hulpkolommetje oplossen.

Waarom zoek je het antwoord '6'? Omdat er 6 sealbags zijn met een gestort bedrag?

Nee andersom juist, ik wil alleen de sealbags tonen die nog niet afgestort zijn.

quote:

Want dan zal er toch ergens een verwijzing naar kolom E in je formule moeten zitten.

Die is er nu ook, maar das een simpele vlookup die ook niet meer werkt (dat moet iets met sommen.als gaan worden).

AANTAL.ALS(E:E;">0") ?

quote:

Op maandag 20 maart 2023 17:40 schreef Tegan het volgende:
AANTAL.ALS(E:E;">0") ?

Daar zitten dubbelen in zo te zien.

Hulpkolommetje maken met =ALS(Bedrag=0;Sealbagnummer;"") en dan in die kolom de unieke waarden tellen. Dat zou het moeten doen volgens mij.

Edit: Dat werkt niet. Ik denk even verder.

Zoiets dan?

AANTALARG(UNIEK(X.ZOEKEN(1;(B2:B99999>0)*(F2:F99999>0);B2:B99999)))

quote:

Op maandag 20 maart 2023 22:16 schreef Tegan het volgende:
Zoiets dan?

AANTALARG(UNIEK(X.ZOEKEN(1;(B2:B99999>0)*(F2:F99999>0);B2:B99999)))

Die kwam gek genoeg op 1 uit.

quote:

Op maandag 20 maart 2023 17:44 schreef Janneke141 het volgende:

[..]
Daar zitten dubbelen in zo te zien.

Hulpkolommetje maken met =ALS(Bedrag=0;Sealbagnummer;"") en dan in die kolom de unieke waarden tellen. Dat zou het moeten doen volgens mij.

Deze lijkt het hem te doen inderdaad. Ik dacht veel te moeilijk.. Thanks!

quote:

Op dinsdag 21 maart 2023 06:45 schreef sanger het volgende:

[..]
Die kwam gek genoeg op 1 uit.

Dat was een beetje dom van mij. Maar ik vond het toch een leuk probleem dus heb ik in een verloren kwartiertje nog even zitten spelen en hierbij een oplossing die werkt.

AANTAL(UNIEK(ALS.VOORWAARDEN(F1:F99999>0;B1:B99999)))