[Excel] Tabel omvormen naar andersoortige tabel

[Harm]

Ik heb in Excel een tabel met data in de volgende vorm:

Bezoeker	Gekozen workshop
Bezoeker A	Workshop 1A
Bezoeker A	Workshop 2
Bezoeker A	Workshop 3
Bezoeker B	Workshop 1B
Bezoeker B	Workshop 2
Bezoeker C	Workshop 3
Bezoeker D	Workshop 2
Bezoeker D	Workshop 3

Die data zou ik graag willen vormgeven als volgt:

Bezoeker	Dinsdag	Woensdag	Donderdag
Bezoeker A	Workshop 1A	Workshop 2	Workshop 3
Bezoeker B	Workshop 1B
Bezoeker C			Workshop 3
Bezoeker D		Workshop 2	Workshop 3

Via een draaitabel kan ik wel de rijen en kolommen op de juiste plek krijgen, maar krijg ik niet de waarden te zien. Wel aantallen of andere getalsbewerkingen, maar geen waarden. Het internet leerde me ook niet hoe ik dat wel voor elkaar kon krijgen. Zoekacties als [google=excel draaitabel waarden weergeven] leverden geen handleiding op.

Handmatig alle mogelijke opties langs te lopen met complexe formules klinkt me te ingewikkeld en te bewerkelijk in de oren. Dus, zijn er ook andere oplossingen mogelijk?

[ Voor 6% gewijzigd door Harm op 30-03-2016 18:19 ]

[Eparox]

Het ligt er een beetje aan hoe ingewikkeld de data is. Heeft een bezoeker maximaal 1 workshop per dag? Zoja, dan zou ik een pivottable maken zoals in het voorbeeld hieronder en vervolgens de kolommen per dag samenvoegen en renamen naar de juiste dag.

[The_Vice]

ik mis de kolom "dag" in je eerste tabel.
maar goed,
makkelijkst, als je beschikking hebt tot Acces, dan middels een crosstab query en "first" in value nemen zonder totals.

in excel, maak een extra kolom(links van je overige data) voor bezoeker die een combinatie is van bezoeker & dag (B1&C1, of [@bezoeker]&[@dag])
dan in kruistabel in cell B2
vlookup ($A2&B$1, tabel,kolomnummer,false)

alleen zal je dan wel handmatig de lijst met unieke deelnemers moeten bijhouden, maar dat kan redelijk makkelijk met een kopie en dan remove duplicates.

[Harm]

Eparox schreef op woensdag 30 maart 2016 @ 18:34:
Het ligt er een beetje aan hoe ingewikkeld de data is. Heeft een bezoeker maximaal 1 workshop per dag? Zoja, dan zou ik een pivottable maken zoals in het voorbeeld hieronder en vervolgens de kolommen per dag samenvoegen en renamen naar de juiste dag.

[afbeelding]

Is dat kolommen samenvoegen en renamen ook te automatiseren? Dat is met deze hoeveelheid data nog wel te doen, maar met 375 rijen (deelnemers) en 25 kolommen (workshopopties) kom je op ruim 9000 cellen en is het meer werk.

The_Vice schreef op woensdag 30 maart 2016 @ 18:51:
ik mis de kolom "dag" in je eerste tabel.

Goeie, die is er inderdaad wel.

The_Vice schreef op woensdag 30 maart 2016 @ 18:51:
maar goed,
makkelijkst, als je beschikking hebt tot Acces, dan middels een crosstab query en "first" in value nemen zonder totals.

in excel, maak een extra kolom(links van je overige data) voor bezoeker die een combinatie is van bezoeker & dag (B1&C1, of [@bezoeker]&[@dag])
dan in kruistabel in cell B2
vlookup ($A2&B$1, tabel,kolomnummer,false)

alleen zal je dan wel handmatig de lijst met unieke deelnemers moeten bijhouden, maar dat kan redelijk makkelijk met een kopie en dan remove duplicates.

Deze optie kan ik morgen wel eens proberen. Ik snap dat nog wel, of mijn collega’s dat ook doen als er weer nieuwe data bijkomt is de vraag. Dus of het een duurzame oplossing, dat weet ik nog niet.

[Belindo]

Een iets andere aanpak, scheelt je een hele hoop kolommen (klikbaar):


Dag kun je natuurlijk ook vervangen door een datum. Of datum+tijd, of een dagdeel.

[ Voor 43% gewijzigd door Belindo op 30-03-2016 19:46 ]

[Eparox]

Harm schreef op woensdag 30 maart 2016 @ 19:04:
Is dat kolommen samenvoegen en renamen ook te automatiseren? Dat is met deze hoeveelheid data nog wel te doen, maar met 375 rijen (deelnemers) en 25 kolommen (workshopopties) kom je op ruim 9000 cellen en is het meer werk.

Het is wel mogelijk met kunst en vliegwerk maar het is niet echt een schone oplossing. Ik zou de oplossing van The_Vice hanteren als het schaalbaar en repliceerbaar moet zijn.

[Verwijderd]

The_Vice schreef op woensdag 30 maart 2016 @ 18:51:
ik mis de kolom "dag" in je eerste tabel.
maar goed,
makkelijkst, als je beschikking hebt tot Acces, dan middels een crosstab query en "first" in value nemen zonder totals.

in excel, maak een extra kolom(links van je overige data) voor bezoeker die een combinatie is van bezoeker & dag (B1&C1, of [@bezoeker]&[@dag])
dan in kruistabel in cell B2
vlookup ($A2&B$1, tabel,kolomnummer,false)

alleen zal je dan wel handmatig de lijst met unieke deelnemers moeten bijhouden, maar dat kan redelijk makkelijk met een kopie en dan remove duplicates.

Dit is naar mijn idee ook de beste en snelste oplossing:

Stap 1: Kopieer de kolom met bezoekers en gebruik de optie duplicaten verwijderen (Gegegvens/Data tabblad).
Stap 2: Maak een hulpkolom Kolom1&kolom2 voor Bezoekers + Dag.
Stap 3: Maak een tabel (zie afbeelding hieronder met formule).
Stap 4: Voer formule door naar rechts en daarna naar beneden.
Stap 5: Kopieer en plak het overzicht als tekst.




Vertalingen:

isfout = ISNB
vert.zoeken = vlookup
onwaar=false

Bij het doorvoeren van de cellen met formule goed letten op de absolute en relatieve celverwijzingen.

[ Voor 7% gewijzigd door Verwijderd op 31-03-2016 11:13 ]

[Eppo ©]

Wat je ook kan doen is met een matrix formule, maar ik weet niet of dat makkelijk uit te leggen is aan je collega's.



Heb daarnaast ook gebruik gemaakt van tabellen, dat maakt je formule wat leesbaarder (vind ik) en het is gemakkelijker om gegevens toe te voegen.

Dit is de formule die ik heb gebruikt:
=ALS.FOUT(INDEX(Tabel1[Workshop];VERGELIJKEN([@Bezoekers];ALS(Tabel1[Dag]="Dinsdag";Tabel1[Bezoeker]);0));"")

Deze moet je invoeren in excel en dan bevestigen met ctrl+shift+enter' ipv alleen een enter.

Uitleg over de formule:
ALS.FOUT([formule];"") = Alleen nodig voor de netheid. Dit zorgt ervoor als er een fout is (#N/B) dat er niks wordt weergegeven.
INDEX([Zoek resultaat];VERGELIJKEN([Zoekwaarde];[Zoeken in kolom];0)) = Het alternatief voor verticaal zoeken. Is een stuk flexibeler dan vert.zoeken.
ALS(Tabel1[Dag]="Dinsdag";Tabel1[Bezoeker]) = Hier bepaal je eerst alle rijen die "Dinsdag" bevatten. In die rijen gaat hij opzoek naar de juiste bezoeker.

[ajakkes]

Je zou kunnen proberen een pivot table als eerste kolom te gebruiken om automatisch te ontdubbelen. Al weet ik niet hoe betrouwbaar dat werkt.

[Verwijderd]

ajakkes schreef op donderdag 31 maart 2016 @ 20:08:
Je zou kunnen proberen een pivot table als eerste kolom te gebruiken om automatisch te ontdubbelen. Al weet ik niet hoe betrouwbaar dat werkt.

En dat is handiger dan 2x klikken?

[ajakkes]

Verwijderd schreef op donderdag 31 maart 2016 @ 23:53:
[...]


En dat is handiger dan 2x klikken?

Kopieren
Plakken
Ontdubbelen
Accepteren

ipv

Gegevens vernieuwen

Het kan de moeite waard zijn. Maar ik heb het niet getest.

[Harm]

Eppo © schreef op donderdag 31 maart 2016 @ 12:00:

Dit is by far the makkelijkste oplossing! Ik kan hiermee namelijk alles in Excel doen en dat is verdraaid fijn, omdat de tussenstap in Access het lastiger onderhoudbaar en overdraagbaar maakt. Deze oplossing is weliswaar wat complexer door de matrixfunctie, maar per saldo het beste. Dank!

[edit]
Dat Excel ook kan ontdubbelen wist ik nog niet, dank voor die tip!

[ Voor 8% gewijzigd door Harm op 04-04-2016 00:03 ]