[Excel] Co-occurence

[ATS]

Hoi,

Ik heb in Excel een lange lijst met daarin steeds twee velden (beide ID's van iets, maar dat is verder niet zo relevant). Die lijst ziet er ongeveer alsvolgt uit:

c_id	q_id
1	1
1	2
1	3
2	1
2	4
3	2
4	1
4	3

Wat ik nu als output wil is een matrix waarin ik kan vinden welke c_id's hoe vaak met dezelfde q_id voorkomen. Dus c_id 1 komt één keer samen voor met c_id 2, één keer met c_id 3 en twee keer met c_id 4. Een output als deze zou mooi zijn:

	1	2	3	4
1	-	1	1	2
2	1	-	0	1
3	1	0	-	0
4	2	1	0	-

In deze matrix staat dus zowel horizontaal als verticaal c_id en kan je op de intersectie lezen hoevaak de twee c_id's samen voorkomen. Uiteraard zitten er in mijn lijst veel meer verschillende c_id's en q_id's, dat spreekt voor zich.

Beschikt Excel over een functie om zo'n transformatie te doen? Tips & tricks?

[Lustucru]

ATS in "Van Excel naar Access, dan een left join..." en dan met name:

Wat heb je zelf al geprobeerd? Wat werkte daar niet aan?

En ontopic: loop eens door de menu's totdat je draaitabel tegenkomt...

[ Voor 22% gewijzigd door Lustucru op 22-11-2007 20:31 . Reden: blijkbaar komt de boodschap niet helemaal over ]

[ATS]

Ik vraag toch niet om een compleet script?
Ik heb het zojuist met een draaitabel geprobeerd, maar ik liep er tegenaan dat ik niet c_id als zowel kolom als rij kon gebruiken. Niet helemaal wat ik zoek dus...

[Lustucru]

Ach so. Ik had je TS verkeerd begrepen. Het eerste wat me te binnen schiet -maar misschien is er een makkelijker manier- is een aantal.als met twee criteria en dan vervolgens sommeren per rijen van dezelfde cid.

[Bolukan]

Is dit dan wiskunde-technisch met een matrix formule te maken? Als dat zo is, zal je met Excel ook een eind kunnen komen. Kun je de wiskundige oplossing laten zien?

Je telt dus niet per c_id, maar per combinatie van 2 c_id´s. Dat lijkt me nou niet iets met een matrix-achtige bewerking te bereiken, maar ik ben daar geen expert in.

Je zou je tabel op q_id kunnen sorteren en dan per q_id, de c_id combinaties met een scriptje kunnen uitspugen (c_id_1, c_id_2, 1). In de derde kolom zet je een 1 en met een draaitabel maak je je optelsom.

[onkl]

Leuk. Lastige vragen.
Je kan een tussentabel gebruiken, die aangeeft welke c (rij) -q (kolom) combi's bestaan. Vervolgens kan je een matrixformule gebruiken om de relevante rijen met elkaar te vermenigvuldigen.
werkend voorbeeldje

[ATS]

Cool, dank je wel onkl! Ik ga het straks eens proberen of ik je resultaat kan reproduceren en of het ook werkt voor de 7.000 rijen die ik heb (c_id heeft ongeveer 100 unieke entries, q_id een kleine 2.000).

[onkl]

Let erop dat de tussentabel alleen 1 of 0 kan aangeven. Die gaat er dus -impliciet- van uit dat iedere c-q combi uniek is.

[ATS]

Dank voor de waarschuwing. Elke c is inderdaad maximaal één maal met elke q geassocieerd, maar elke c kan met een oneindig aantal q's geassocieerd zijn en elke q kan met een oneindig aantal c's geassioceerd zijn (n-n relation dus). Ik zal overigens wel de tabel andersom moeten opbouwen: Excel trekt geen 2000 kolommen

[ Voor 16% gewijzigd door ATS op 23-11-2007 12:23 ]

[onkl]

ATS schreef op vrijdag 23 november 2007 @ 12:22:
Dank voor de waarschuwing. Elke c is inderdaad maximaal één maal met elke q geassocieerd, maar elke c kan met een oneindig aantal q's geassocieerd zijn en elke q kan met een oneindig aantal c's geassioceerd zijn (n-n relation dus).

Dat is op zich geen probleem, je voorbeelddata gaf dat afdoende aan. Zoals je ongetwijfeld met me eens zal zijn: 2 zit dichter bij oneindig dan bij 1 (als je het over relaties hebt)

Ik zal overigens wel de tabel andersom moeten opbouwen: Excel trekt geen 2000 kolommen