Ztracen v Excelu aneb POMOC!!!
Po několika dnech marného snažení jsem se odhodlal k nejhoršímu: svěřit se světu s vlastní neschopností. Něco takového nikdo z nás nečiní rád, takže vám musí být jasné, že jsem opravdu zoufalý. Vlastně mi zbývá poslední naděje: že mi pomůže nekdo z vás.
Můj problém začal před několika měsíci, když mne napadlo, že bych v rámci svého vědeckého snažení mohl prozkoumat politickou aktivitu cizinců v České republice. Když to zjednoduším: od našehp vstupu do Evropské unie mohou podle zákona v komunálních volbách volit i občané jiných členských států, kteří u nás (v dané obci) mají trvalý pobyt. Nikdo ale neví, jestli to doopravdy dělají: potřebná data jsou totiž roztroušená na jednotlivých obecních úřadech, kterých je asi šest a půl tisíce. Pokud chce totiž cizinec volit, musí ještě před volbami zajít právě na obecní (městský) úřad a zapsat se do takzvaného dodatku k voličskému seznamu. Nikam dál se to ale nehlásí.
Nevadí, řekl jsem si, kdyby to bylo snadné, nebyla by to věda. Vybral jsem reprezentativní vzorek, rozeslal datovou schránkou asi 450 žádostí o informace, týdny urgoval mlčící úřady, došlá data zapisoval do tabulky, dohadoval se o úhradě, stěžoval si na nečinnost atd. S detaily vás nebudu unavovat, protože v tomhle problém není: bylo to pracné, ale je to za mnou.
Shromáždil jsem tedy hezká data o tom, kolik cizinců v oslovených obcích chodí volit, jenže nejsou mi k ničemu, když nevím, kolik cizinců tam volit nechodí. Jinými slovy, kolik jich tam žije celkem. Tohle kupodivu obce nevědí, takže mi to ani nemohly říci; evidenci cizinců totiž podle zákona vede cizinecká policie.
Zeptal jsem se tedy na policii a celkem rychle dostal požadovaná data. A tady začínají potíže: dostal jsem toho totiž mnohem (mnohem!) víc, než bych potřeboval, a v úplně jiné struktuře, než se kterou dokážu pracovat. Tabulka, kterou bych chtěl, vypadá zhruba takhle:
Obec | Okres | Počet cizinců z EU s trvalým pobytem | Počet cizinců z EU s přechodným pobytem |
Ouholičky | Benešov | 41 | 2 |
Prčice | Sedlčany | 12 | 15 |
atd. |
Ve skutečnosti však mám (po řadě úprav a čištění původního souboru):
Obec | Okres | Země původu | Trvalý pobyt | Přechodný pobyt |
Ouholičky | Benešov | Argentina | 12 | 4 |
Ouholičky | Benešov | Slovensko | 40 | 1 |
Ouholičky | Benešov | Kypr | 1 | 0 |
Ouholičky | Benešov | Malta | 0 | 1 |
Prčice | Sedlčany | Vatikán | 10 | 0 |
atd. atd. atd. |
Vtip je v tom, že celá tahle tabulka má asi 45 tisíc řádků, což poněkud komplikuje zpracování. Zkusil jsem z ní data vytáhnout prostřednictvím funkce DSUM, rozkopírované k názvu každé obce… což bylo to poslední, co jsem před dvěma dny na svém počítači udělal. Od té doby Excel počítá a počítá, 98 % procesoru zabírá, ale nikam se nedostal. Zatím ho stále nechávám běžet, ale všem nám je jasné, že tenhle běžec do cíle nedoběhne.
Mezitím jsem na jiném stroji zkusil různé triky, které by tu úlohu zjednodušily: například data setřídit a rozdělit na deset menších hromádek, takže by nebylo nutné při výpočtu neustále pracovat se všemi daty, ale prohledávat jen 10× menší množinu. Výsledek se dostavil: výpočet je sice stále neuvěřitelně pomalý, zato je ale mnohem pracnější na lidskou obsluhu. Učinil jsem totiž zajímavé pozorování: když ty vzorce kopíruju jednotlivě, jednu obec po druhé, trvá výpočet řekněme 2 sekundy na každou. Když to ale zkusím kopírovat v blocích, výrazně se zpomaluje: deset buněk netrvá 20 sekund, nýbrž minutu, dvacet buněk netrvá čtyřicet sekund, ba ani dvě minuty, nýbrž spíš tak čtyři. Kolik by trvalo těch 6 tisíc obcí ani nechci počítat.
Zkrátka: po třech dnech snahy jsem dospěl do bodu, kdy je mi jasné, že s tím už nehnu. Chyba je zjevně mezi židlí a klávesnicí, ale bohužel i za ní: Excel se s touhle úlohou nedovede poprat a já ho neumím líp zaúkolovat ani použít vhodnější nástroj.
A tak tedy: pomoc!!!!! Když mi někdo dokážete poradit, jak mám dosáhnout výsledku, královsky se vám odměním.:-)
HOTOVO!
Přátelé, děkuju moc. Zase jednou můžu říci, že Internet je báječná věc. Zhruba v půl druhé jsem dopsal tenhle spot a poslal o něm echo na Twitter a na Facebook. Hned poté jsem – trochu otráveně – vypnul počítač na fakultě a vyrazil domů, abych se najedl a přišel na jiné myšlenky. Než jsem sešel to jedno patro a vyšel před budovu, už jsem měl první odpověď. A za chvílí už se sypaly další a další, ze všech kanálů, do kterých jsem dal vědět. Než vám řeknu, jak jsem to nakonec provedl, zkusím ty nápady shrnout; třeba se to ještě někdy bude hodit:
- Relační databáze – Tohle byla asi nejčastější a nejlogičtější rada: vyexportuj to z Excelu do CSV souboru, načti to do vcelku libovolné databáze, a pak už je to hračka. Přestože je to asi nejlepší řešení, nakonec jsem se k němu nepřiklonil, a to hlavně kvůli tomu, že nemám žádnou databázi snadno k dispozici (mám jen verzi Office bez Accessu) a musel bych ji kvůli tomu shánět, instalovat (a učit se).
- Prožeň to skriptem v perlu – Tahle rada mi připomněla, jak jsem kdysi pravidelně v němém úžasu pozoroval, co dokáže s perlem jeden můj nejmenovaný kolega: v podstatě všechno. Pevně věřím, že by si poradil i s tímhle mým problémem, ale sám jsem se do toho pustit bál (i když se mi tu ještě někde válí učebnice perlu). @ptica byl sice tak hodný, že mi rovnou napsal skript a poslal odkaz, který by stačilo spustit, jenže můj problém řešil jen částečně. V textu jsem to nenapsal úplně jasně, ale nešlo mi jen o „sečtení“ čísel na různých řádcích, byla tam navíc podmínka, že daná země patří do EU. To bych sice mohl nějak vyřešit i na straně zdrojových dat, ale zdálo se mi to trochu složité.
- Použij Gooddata.com – O tomhle řešení jsem trochu přemýšlel už předtím, než jsem napsal svůj zoufalý spot: donedávna tam totiž pracoval můj dobrý kamarád, od kterého zhruba tuším, že něco takového ta služba dělá. Ale až když jste mi to tu doporučili jsem se odhodlal se tam podívat a zkusit data naimportovat. Opravdu to bylo poměrně snadné a s trochu snahy bych tam problém záhy vyřešil, narazil jsem ale na potíž s kódováním češtiny: názvy obcí a okresů se rozsypaly. I tohle bych asi dokázal nějak vyřešit ve zdrojových datech, např. nahradit názvy číselným identifikátorem, prohnat Good Data, a pak to zase vrátit zpátky, nicméně pro daný okamžik mne to odradilo.
- Použij Google Fusion Table nebo Google Refine – Tušil jsem, že někde v aplikacích Googlu bude něco, co by se pro můj problém dalo použít, ale z iracionálních důvodů k nim mám trochu ostych. Vtipné je, že mi to doporučil @codeas, který Google Apps školí; zrovna včera jsem přemýšlel o tom, že bych na to školení měl zajít, a teď se mi to potvrdilo.:-)
- Pošli mi ten export a já Ti to zpracuju – Přátelé, během dvou hodin v sobotu odpoledne mi šest lidí, z nichž dobrou polovinu jsem nikdy neviděl, nabídlo, ať jim to prostě pošlu a že to udělají za mne. Dobří lidé nevymřeli; děkuju vám! Nakonec jsem ale žádné z těch nabídek nevyužil: sice jsem si vás schovával jako poslední možnost, to přiznávám, ale chtěl jsem se napřed pokusit sám. Protože kdyby to někdo udělal za mě, nic se nenaučím a příště budu zase v loji.
- „Kontingenčka to nezvládne?“ – Abych byl upřímný, tahle rada se mi původně moc nosná nezdála. Jak jsem psal ve spotu, zkoušel jsem to vyřešit funkcí DSUM, což je v podstatě totéž, co dělá kontingenční tabulka. Ale když mi pak něco podobného na Facebooku napsal druhý člověk, řekl jsem si, že bych to asi zkusit měl – a hle, ono to funguje! Vyžadovalo to sice jisté úpravy dat (sloučit obec a okres do jednoho řetězce s oddělovačem uprostřed, prohnat kontingenční tabulkou a pak zase rozdělit zpátky), ale to jsou detaily. Podstatné je, že co při mém neumném pokusu Excelu trvalo hodiny (a dny :-), to dokázal v kontingenční tabulce spočítat za pár vteřin. Zřejmě to dělá jinak než přes DSUM, resp. to má nějak optimalizované. Takže: heuréka, data konečně vypadají tak, jak mají, a vlastně to ani moc nebolelo.
Teď mne ještě čeká spojit data z dvou tabulek do jedné, což nebude úplně jednoduché, protože mohu použít maximálně textovou shodu názvu obce a okresu, a ta není zcela spolehlivá. Jenže: teď už jde jen o nějakých 370 řádek, a s tím se člověk nakonec přinejhorším popere i ručně.
A poučení? Přeci jen to byl nakonec problém mezi židlí a klávesnicí; jako obvykle.:-)
- Podle hodnocení
- Podle vláken
- Nejnovější
-
-
Problém je v tom, že existuje řada obcí v různých okresech, které se jmenují stejně. Proto jsem z toho vytvářel unikátní řetězce, abych je odlišil. Jiné způsoby (třeba kombinace obou proměnných v pozici "řádek") vedla k tomu, že se mi pak pod společný název obce seřadily okresy), což by mi komplikovalo další zpracování.
-
J pallas (neregistrovaný) ---.mobileonline.telia.com
Excel to chape :-) staci v pozici radku nejprve zvolit okre a potom obec. Data se potom scitaji za obec a chcete li tak i mezisoucty za okres
-
Tomáš Kapler (neregistrovaný) ---.242.broadband3.iol.cz
přiznám se, že databázové funkce nemám rád, zejména kvůli jejich nutnosti používat tabulku s parametry. Nicméně tohle mi přijde naprosto primitivní záležitost pro funkci Data / Souhrny, přesně k tomu je určena.
-
. (neregistrovaný) 82.142.88.---
A jak byste to dělal vy, prosím? Učil se a instaloval nějakou velkou DB?
-
Jiri Tucek (neregistrovaný) ---.jesva.cz
Hlásím se jako další dobrovolník na pomoc zdarma - naliju to do MSSQL":)
Jiří Tuček
www.jiritucek.cz -
A nemyslíš tím právě tu funkci DSUM, o které píšu? Protože jestli ano, pak je to právě ta funkce, kterou jsem při daném množství dat Excel odvařil na dva dny.:-)
-
Michal Hanko (neregistrovaný) 193.86.121.---
Já bych to udělal jednoduchoučkým perlovským skriptem:
while (<>)
{
chop;
@p=split(/\t/,$_);
# případně if (nějaké_dodatečné_podmínky) ...
$h{$p[0]."-".$p[1]}+=$p[3];
}
foreach $x (keys %h) {print $x,"\t",$h{$x},"\n";} -
Michal Hanko (neregistrovaný) 193.86.121.---
PS. doba běhu pro 50000 testovacích řádek: méně než 0,1 sekundy :)
-
Martin Kalenda (neregistrovaný) ---.jisvi.klfree.cz
Pokud jde o to, udělat to za každou cenu excelem - tak neporadím. Od toho by měli být "google/bing answers" aby někdo v indii poslal za dolar 1-5 do hodiny řešení.
-
noname (neregistrovaný) ---.242.broadband3.iol.cz
Nikoliv - Data / Souhrn, vybrat sloupec, který to má sčítat (či cokoliv jiného) při změně jakého sloupce (nejdříve je třeba seřadit podle okres, obec). Interně to pak používá funkci subtotal. Pro sto tisíc řádků otázka maximálně vteřin.
-
Jiri pallas (neregistrovaný) ---.tbcn.telia.com
Obec s okresem jste vubec spojovat nemusel. V pivotu mate moznost do "pozice" radek zadat vice polozek, tedy napr kkres a obec. To vam dava moznost presunout treba obec do filtru a sledovat pouze statistiku okresu. V excelu muzete bez problemu zpracovat par set tisic radku, taze 40 000 jneni problem. Good Data od R. Stanka jsou vhodne na ulohy s objemem dat o rady vetsi. SQL se hodi na zpracovani dat, ale na analyzu je excel, SAS, Good Data a pod mnohem lepsi.
-
rezna (neregistrovaný) ---.net.upcbroadband.cz
zakladatel Internet Info, šéfredaktor Lupa.cz, vedoucí projektu Dobrý web, aktivista ve SPIRu a CZ.NICu
s timto "CV" a pocitate s 45k radky pres excel? - prominte pane ale tohle mi hlava nebere ...
-
franta (neregistrovaný) ---.248.broadband6.iol.cz
Ještě že jsi Marku připsal závěr HOTOVO! Jinak už bych Ti navrhoval řešení ve smyslu bodů 1. nebo 5. Každý umíme něco jiného. A s tou pomocí to ber tak, že kdysi jsi pomohl Ty někomu a dnes on pomůže Tobě. Mám tím na mysli dobu, když jsi ještě býval sysopem na Bajtu. Asi je nás těch dlužníků víc :-)
-
PavelS (neregistrovaný) 80.250.29.---
Na podobne ulohy je jako delany gooddata.com staci to tam dostat v slusnem CSV a pak jsou podobne sumarni operace uz hodne snadne, navic to od zacatku pocita s masivnimi daty.
Format CSV:
http://support.gooddata.com/entries/50910-tips-for-uploading-your-dataP.
-
ptica (neregistrovaný) ---.eurotel.cz
@mantos ulozit excel jako csv, nainstalovat perl (activeperl pro win), pustit https://gist.github.com/1223924
-
Jan panoch (neregistrovaný) 85.162.97.---
Tak nam marku ty data nekde vystav ke stazeni (tedy pokud nejsou tajna) a muzeme se predhanet kdo to lip zpracuje nejakym nastrojem :-)
-
. (neregistrovaný) ---.net.upcbroadband.cz
Vida, první, co napadlo mě, bylo zkusit OpenOffice.org. MS Office jsem přestal používat před hodně lety a OOo mě málokdy zklamal. Vloni jsem byl nucen používat MS Office 2007 (manželka v tom musí psát disertačku a já jsem ten, kdo to formátuje a dělá z textu dokument) a neuvěřitelně jsem tekl, jak je to stupidní balík. Neumí předpočítat všechna pole v dokumentu (Ctrl+A a F9 nezahrne popisy obrázků a tabulek), nejdou vložit vlastní křížové odkazy, např. jen číslo obrázku a nikoliv celý popisek, občas to při práci spadlo, aniž by to umožnilo obnovu ze zálohy (což OOo dělá už řadu let), není možné obrázek ukotvit k určitému znaku, prostě desítky různých omezení či z hlediska tvorby dokumentu zcela zásadních problémů. Pominu příšerné uživatelské rozhraní, to je otázka vkusu a zvyku, ale absolutně nechápu, jak tohle někdo může používat dobrovolně a ještě za to platit. No nic, trochu jsem se rozjel :-), ale vysvětluje to, proč mě jako první napadlo "zkusit to z OOo", protože ten mě snad nikdy ve štychu nenechal. Docela by mě zajímalo, jak by se s tím Calc popral.

Marek Antoš
Základní charakteristika: podnikatel v internetových médiích. Čím vším už jsem byl a občas ještě jsem: zakladatel Internet Info, šéfredaktor Lupa.cz, vedoucí projektu Dobrý web, aktivista ve SPIRu a CZ.NICu. A trochu taky ústavní právník, ale na to v tomhle blogu nedojde.
Nejčtenější články autora
-
Příliš žluťoučký kůň úpěl ďábelské ódy (.CZ?!)
Přečteno 53 087×
-
Český účetní rada
Přečteno 43 748×
-
Internetové diskuse a Prolux Consulting: tady končí prdel
Přečteno 39 602×
-
Vrchní soud: kritické diskusní příspevky na Internetu jsou v pořádku
Přečteno 31 848×
-
Kauza Prolux - rozsudek odvolacího soudu
Přečteno 30 666×
Poslední názory
-
\
Re: Kauza Prolux - rozsudek odvolacího soudu
ke článku Kauza Prolux - rozsudek odvolacího soudu -
\
Re: Kauza Prolux - rozsudek odvolacího soudu
ke článku Kauza Prolux - rozsudek odvolacího soudu -
\
Re: Kauza Prolux - rozsudek odvolacího soudu
ke článku Kauza Prolux - rozsudek odvolacího soudu -
\
Re: Kauza Prolux - rozsudek odvolacího soudu
ke článku Kauza Prolux - rozsudek odvolacího soudu -
\
Re: Kauza Prolux - rozsudek odvolacího soudu
ke článku Kauza Prolux - rozsudek odvolacího soudu
-
- Network Security Analyst
- JAVA VÝVOJÁŘ | IČO/HPP
- C++ Developer | NEkorporát
- Development Team Lead pro ERP systém
- Databázist(k)a
- Delivery Manager neboli Agile Master
-
- Jak úspěšně připravit vaši první online kampaň
- Komunikace s lidmi v souladu s fungováním mozku
- Psaní e-mailů snadněji a rychleji
- Trénink vyjednávání prakticky I.
- Google Disk - mějte svá data pod kontrolou
- Řešení konfliktů - taktiky a strategie
-
- C++ Developer | NEkorporát
- Android Developer |Nekorporát v centru Prahy
- Programátor JAVA
- Databázist(k)a
- Delivery Manager neboli Agile Master
- Embedded Software Engineer / Vývojář Programátor v C
-
- Jak úspěšně připravit vaši první online kampaň
- Plánování projektu II: rizika a komunikace
- Komunikace s lidmi v souladu s fungováním mozku
- Kritické myšlení 5 - Teorie her a jak okolnosti ovlivňují rozhodování
- Trénink vyjednávání prakticky I.
- Řešení konfliktů - taktiky a strategie
Dále u nás najdete
Internet Info Lupa.cz (www.lupa.cz)
Server o českém Internetu. ISSN 1213-0702
Copyright © 1998 – 2019 Internet Info, s.r.o. Všechna práva vyhrazena.