Üks lahtipivootimise näide

Üks lahtipivootimise näide

Mõni päev tagasi tuli mul ette ülesanne – üks suht keeruline risttabel “lahti pivootida” ehk tagasi lametabeliks muuta

Väljakutseks muutis selle vajadus teha kõik valemitega ning vältida igasuguseid makrosid ja mis kõige olulisem

Ma tegin seda!

Ma tegin ka ühe lihtsustatud näite aj jagan seda heal meelel

http://1drv.ms/1JFMtGA (siin on toodud näide Exceli kujul)

Väike samm-sammuline selgitus

Minu näites on algne tabel klassikaline “risttabel”, kus erinevates veergudes atribuudid ja ridades kirjed ning atribuutide väärtused.

Lahtipivootimise tulemuseks peaks olema umbes selline tabel:

Atribuudid eraldi ridadel ja ainult need, mis tabelis ka olemas

Hakatuseks teen vahetabeli. Võib ka lihtsalt laiendada lähtetabelit, kuid ohutuse mõttes tegin vahetabeli. Tegelik algne risttabel ei olnud tabel ja koosnes veel eri laiusega sektsioonidest.

  • Esimene veerg:     Nr =ROW()-ROW(Tabel2)+1

See trikk võimaldab genereerida tabelisse numbreid, kui tabelit allapoole laiendada. Ja see ei sõltu tabeli asukohast

  • Teine veerg:     Nimi =VLOOKUP([@Nr];Tabel1;MATCH(Tabel2[#Päised];Tabel1[#Päised];0);FALSE)

See on üks teine (vana) trikk, mis võimaldab VLOOKUPiga logeda teisest tabelist andmeid veeru nime järgi

  • Kolmas veerg annab atribuutide arvu (> 0)

=COUNTIF(OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”));”>0″)

Antud näite tegin, et ta ei sõltuks atribuudiveergude arvust – reaalses elus ei pruugi atribuudid olla sarnaste niumedega, sel juhul võiks valem olla umbes selline

=COUNTIF(OFFSET(Tabel1[Esimene atribuut];[@Nr]-1;0;1; — siia atribuutide arv tabelis –);”>0″)

Minu algses tabelis oli 17 atribuudiveergu

  • Neljandaks arvutan reanumbri, millest alates tulemuses on SELLE kirje atribuudid

=SUM(Tabel2[[#Päised];[NumOfAttr]]:[@NumOfAttr])-[@NumOfAttr]+1

See on klassikaline “kasvav summa” näide tabeliformaadis

  • Viies veerg on bitmask, mis näitab, millised atribuudid antud kirjes on aj millised mitte

Mask =SUMPRODUCT(

(OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”))>0)

*

(2^(8-COLUMN(OFFSET($A$1;0;0;1;COUNTIF(Tabel1[#Päised];”Attr*”)))))

)

See SUMPRODUCT arvutab bitmaski kasutades kahte vektorit:

Üks vektor on {true,false,…} jada, mis võrdleb atribuudi olemasolu (antud juhul >0)

Teine vektor on 2-astemete (pööratd) jada kujul {256, 128, 64, …}

Nende kahe vektori korrutis kokkuliidetuna annabki bitmaski

  • Kuues veerg on seesama aga kahendkujul (0-1 jada)

Bitmask =DEC2BIN([@Mask];8)&”0″

Juhul kui atribuute on üle 9, tuleb see arvutus tükeldada, kuna dec2bin ei võimalda üle 9 biti

Sellega on vahetabel otsas

Nüüd teeme tulemustabeli

  • Esimene:     NR =ROW()-ROW(Tabel3)+1 — sama trikk, mis eelmises

Siin on maitse küsimus, kas teha algusest peale maksimaalsuurusega tabel ja peita ülearused read IF-funktsiooniga või siis teha tabel ja “venitada” see vajaliku pikkuseni

  • Teine:         LineNr =MATCH([@Nr];Tabel2[ResLine];1)

Annab mulle rea numbri lähte- (ja vahe)tabelis

  • Kolmas:     ResNR =COUNTIF(Tabel3[[#Päised];[LineNr]]:[@LineNr];[@LineNr])

Annab mulle atribuudi järjekorranumbri

Kõik eelnev tagab, et mul on vaid need read, mis lähtetabelis ja vaid need atribuudid, mis lähtetabelis olemas, atribuudiridade arv vastab atribuutude arvule ja atribuutide numbrid algavad 1-st

  • Neljas:         AtrCol =IF([@ResNr]=1;

FIND(“1”;INDEX(Tabel2[BitMask];[@LineNr]));

FIND(“1”;INDEX(Tabel2[BitMask];[@LineNr]);OFFSET([@AttrCol];-1;0)+1)

)

See leiab iga järgmise atribuudi positsiooni tabelis. Tegelikult “1” positsiooni(d) bitmaskis

Kui atribuudi number on 1, siis leiame lihtsa FIND-funktsiooniga. Järgmiste atribuutidega kasutame FIND funktsiooni kolmandat atribuuti (millisest kohast alates), mille me loeme eelmisest reast.

Edasine on lihtne:

Nimi =VLOOKUP([@LineNr];Tabel1;MATCH(Tabel3[#Päised];Tabel1[#Päised];0);FALSE) — taas see vana trikk

AttrName =OFFSET(Tabel1[[#Päised];[Attr1]];0;[@AttrCol]-1) — siin loeme atribuudi nime päisereast

AttrValue =OFFSET(Tabel1[[#Päised];[Attr1]];[@LineNr];[@AttrCol]-1) — siin samamoodi andmereast

Igasugune tagasiside on teretulnud

Henn

This entry was posted in Excel and tagged . Bookmark the permalink.

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s