• Úvod
• Excel návod
• Excel šablony
• Nástěnka
• Mobility
• Tipy na výlet
• Galerie
• Fórum

Příspěvky uživatele

takže si to pochopil a funguje?

Chcete soubor, máte jej mít.
Tak pozor, mne je jedno, či sem dáš prílohu, ale riešiť to potrebuješ ty a ty si ten, kto nie je schopný aplikovať riešenie, ktoré som ti dal...

zkusil jsem to ale podle tvého popisu, když ten vzorec roztáhnu, tak píše nesmysly.
Páč nechápeš, ako to funguje
Pokiaľ chceš vzorec na inom liste, tak musíš do vzorca doplniť názov listu, z ktorého sa dáta majú ťahať.

Ten vzorec je nutno roztáhnout, protože těch hodnot mám stovky - Roztiahnuť, tak, ako si to predstavuješ, to nejde. Ja som nehovoril o roztiahnutí, ale o skopírovaní do vybraných buniek.
V prílohe som vytvoril pomocný stĺpec na liste 2. V ňom pomocou vzorca určím, do ktorých buniek (riadkov) sa má zapisovať. Zapnem filter a vzorec z A2 skopírujem iba do viditeľných buniek v rozsahu A2:A1000. Pre 1000 záznamov z listu1 by mal rozsah na liste 2 byť teda správne A2:A2000 (ja som tam nechal iba A2:A1000). Filter som nechal zapnutý. Dal som ti tam i printcreen, ako vybrať iba viditeľné bunky (na liste 2 označiť A2, v ktorej už je vzorec, ctrl+C, označiť A2:A1000, potom F5->Special->Visible cells only, OK, ctrl+V). Filter si nakoniec vypneš a pomocný stĺpec zmažeš. To je celé.

edit: Ono by to vlastne šlo i roztiahnutím vzorca, ale ten postup by musel byť iný. Skúsim popísať v krokoch.
1. Všetko sa bude odohrávať na liste 2
2. do jednotlivých buniek zapíš:
A1: Jméno
B1: XX
C1: YY
B2: 1
B3: =B2+$B$2 - roztiahni dolu po riadok 15 vrátane
C2: =MOD(ROW();2) - roztiahni dolu po riadok 15 vrátane,
3. označ stĺpce B:C, skopíruj a vlož ako hodnoty
4. označ stĺpce A:C, daj zotriediť podľa yy vzostupne a podľa xx vzostupne - v jednom kroku
5. do bunky A2 vlož: =List1!A2, roztiahni dolu po riadok, kde začínajú v stĺpci C jednotky
6. označ stĺpce A:C, daj zotriediť podľa xx vzostupne
7. stĺpce B:C zmaž

v prílohe je prvý spôsob

Pozerám, že nás navštívil ďalší talent na vysvetľovanie, s tajnou prílohou
Hodnoty do stĺpca B zo stĺpca A (viď. tvoj posledný post) prenesieš jednoducho:
Do B1 napíš vzorec: =INDEX($A$1:$A$5;INT(ROW()/2+1))
Zmačkni CTRL+C
Vyber bunky B3,B5,B9
Zmačkni CTRL+V
Aby boli sformátované rovnako, ako korešpondujúce hodnoty zo stĺpca A, u ktorých je nastavený podmienený formát, tak to žiadnym vzorcom nepôjde. Makro nechceš, tak si to budeš musieť nastaviť ručne.
Predpokladám, že napíšeš, že ani ten hore uvedený vzorec nebudeš môcť použiť. No, to máš, chlapče, tak: Pokiaľ sa nevieš zrozumiteľne vyjadriť a príloha je tajná, páč je asi veľký problém vytvoriť inú s dummy hodnotami, je ťažké uveriť, že o riešenie skutočne stojíš. Kosti z netopiera mi zožrala mačka, do doby, než získam nové, sa čarovanie a veštenie odkladá. Sím, hlavne mi neposielaj žiadny tajný súbor na mail

@cmuch: podľa mňa to dobre nechápeš
pokiaľ by mal byť výsledok zhodný s tým, čo vidím v stĺpci B (najmenšie číslo je prvé v poradí vždy bez ohľadu na počet jeho výskytov, čo práve tvoj vzorec nespĺňa), tak asi takto:=SUMPRODUCT(--(COUNTIF(OFFSET($A$1;;;ROW($A$1:$A$4)-ROW($A$1)+1);$A$1:$A$4)=1);--($A$1:$A$4<=A1)), prípadne bez použitia volatilnej OFFSET ale v maticovom tvare (niečo za niečo) takto:=SUM(($A$1:$A$4<=$A1)/COUNTIF($A$1:$A$4;$A$1:$A$4))Vlastne, ten druhý sa dá tiež zapísať v nematicovej forme, za použitia skalárneho súčinu :=SUMPRODUCT(($A$1:$A$4<=$A1)/COUNTIF($A$1:$A$4;$A$1:$A$4))Pokiaľ by sa v uvedenom rozsahu buniek nachádzali prázdne bunky a/alebo nenumerické hodnoty, tak vzorec by sa musel rozšíriť napr. takto:=IF(ISNUMBER(A1);SUMPRODUCT(($A$1:$A$4<=A1)/COUNTIF($A$1:$A$4;""&$A$1:$A$4))-COUNTBLANK($A$1:$A$4);"")

Keby som to mal riešiť ja, tak by som volil ukladanie dát z jednotlivých spreadsheetov do databázy a agregované dáta ťahal do sumárneho spreadsheetu z nej. Databázou mám na mysli Access alebo SQL server. SQL server express je zadarmo.

@eLCHa
v tom prípade to nie je nič strašné; mne sa to však na NTB chová, pokiaľ súbor presuniem do iného adresára, trochu inak - cestu k súboru musím v Command Texte v definícii Query prepísať ručne..

@eLCHa
Výhodu v použití rozšíreného filtra by som videl vo vylistovaní hodnôt, prenášaných do grafu, v samostatnej tabuľke, bol to len taký nápad. Samozrejme, dá sa to i pomocou pomocného stĺpca v originál dátach (t.j. ako ste to urobil Vy) a filtrovať potom cez tento stĺpec.
Použitie RANBETWEEN nemalo za cieľ niekoho pliesť, prišlo mi to jednoducho dobré na generovanie čísel a overenie, či sa to správne zobrazuje v grafe.
Som rád, že ste mi potvrdil funkčnosť na inej mašine - u MS Query je nešikovné to, že to datové spojenie treba vždy, pokiaľ sa zmení umiestnenie súboru, predefinovať. Dá sa to ošetriť makrom, ale to sa mi už písať nechcelo, páč predefinovanie spojenia je jednorázová vec a makro (niekde ho mám zapatrošené) by som musel hľadať.

@marjankaj
ja občas poruším zásadu vyžadovať od OP vzor. Problém riešim, pokiaľ ma jeho riešenie baví - negarantujem samozrejme, že moje riešenie potom bude OP k úžitku a je možné, že o riešenie upresneného zadania už záujem mať nebudem. Riešim pre svoje potešenie a zadarmo, mám potom právo riešiť si vec, ktorá sa možno odchyľuje od pôvodného zadania. Pokiaľ sa niekedy nejaký OP rozhodne, že mi za riešenie zaplatí, tak sa budem zadania striktne držať a požadovať informácie potrebné k riešeniu.
Na tomto fóre mi zaplatil za celú dobu akurát jedenkrát jeden jediný človek, jeden sa aspoň spýtal, čo je dlžný, a jeden akurát sľuboval, otravoval a vo finále, keď som mu vec dodal, sa už neozval.

Zdá sa, že to funguje i v 2010. Pekná práca..

@eLCHa
prečítal som si Váš post na mobilnom telefóne a povedal som si: on tam beztak Karel použil zrejme rozšírený filter. Nepoužil, takže ďalšou možnosťou, ktorá by to celé zjednodušila a odpadlo by Query, by mohol byť rozšírený filter a krátky kód buď v udalostnom makre, alebo tlačítko s makrom na generovanie záznamov odpovedajúcich zadaným kritériám. Makro na zlícovanie osí by som asi zachoval.
Možností riešenia je ako vždy viac.

Inak, skúšku zo štatistiky som ja mal ešte pred plyšákom, odvtedy som to tiež viacmenej nemal možnosť použiť, sme na tom asi skoro všetci podobne - zabúdame na veci, ktoré sme sa na škole krvopotne naučili, holt, čas je milosrdný

Len pre zaujímavosť, dokázal ste ten môj súbor rozchodiť? Mám na mysli: prekresľujú sa dáta v tabuľke a graf pri zmene zelených buniek?

V novej verzii som dorobil to zlícovanie osí X, to už je urobené makrom, čiže súbor je .xlsm, znovu je potrebné opraviť definíciu pripojenia k datovému zdroju..
Popisky osí som nechal viditeľné pre názornosť, ale je možné, samozrejme, zobrazenie popiskov sekundárnych osí potlačiť.

Aby som iba neplácal, tak som skúsil vytvoriť vzorový súbor. Snáď to bude fungovať. Určite bude potrebné zmeniť cestu k súboru, pokiaľ to hodí nejakú chybovú hlášku, tak sa nedeste, i napriek tomu by sa to malo dať rozchodiť.
Takže: bude potrebné, aby ste si zmenili cestu v nastavení Data->Connections->Properties->Definition v textboxe Connection String a Command Text (Na Sheet2 som umiestnil printscreen, je treba prepísať hodnoty DBQ, DefaultDir a v Command Texte cestu v klauzuli FROM, printscreen môžete odstrániť, je tam iba pre názornosť).

Na Sheete2 sa potom odohráva celá vec:
Zvolíte v zelených bunkách Diel a Obdobie.
Graf sa prekreslí vždy, keď dojde ku zmene v niektorej zo zelených buniek.
Nasimuloval som i chybu pri názve dielu.
Uvažujem s 3 dielmi: aaa,bbb a ccc. Schválne som nasimuloval "preklepy", takže pokiaľ zvolíte aaa, obdobie 1-Jan až 31-Jan tak vidíte, že v tabuľke sú všetky hodnoty obsahujúce v reťazci aaa, čiže i xaaa atd.
Snáď to bude ako inšpirácia dostatočné.
Testovacie dáta (databáza) sú na liste Sheet1. Tam som vytvoril nejaké náhodné hodnoty, hovoríte, že tie namerané budú mať log-normálne rozdelenie, v tom prípade by sa mal tvar histogramu približovať tomu tvaru log-norm rozdelenia z grafu.

Ešte by to chcelo pohrať si s x-ovými osami (primárnou a sekundárnou) tak, aby mali rovnaké rozsahy, ale to už sa mi riešiť nechcelo, skúste sám, alebo to vyskúšam zas niekedy, keď budem mať čas a chuť.

Dajte vedieť svoje postrehy..

Ku kontingenčnému grafu, ktorý zmieňoval kolega: Tam by som istý problém videl v tom, že požadovaný histogram musí pracovať s buckets, takže kontingenčkou sa síce dajú zobraziť dáta, odpovedajúce kritériám, ale kontingenčný graf by už asi neodpovedal tomu, čo sa požaduje v zadaní - musel by dáta odpovedajúcim spôsobom agregovať a to už by bola istá komplikácia (hodnoty jednotlivých buckets nie sú statické, takže opäť by na rad museli prísť nejaké pomocné výpočty). Krom toho, kontingenčka by sama osebe ťažko spracovala uvedený prípad, kedy xaaa má byť považované za aaa - to by sa asi muselo ošetriť nejakými pomocnými stĺpcami v zdrojových datách (hodne zbytočnej práce, i keď ani Query v tomto prípade 100%-ne negarantuje správnosť výberu dielu, ideálne by bolo preklepy v názvoch dielov odstrániť). No a pokiaľ by sa tvoril graf nad dátami z kontingenčky, kde by už boli dáta nejako agregované (hodne zbytočnej práce) a nejednalo by sa o kontingenčný graf, tak by sa museli datové rady definovať dynamicky (hodne zbytočnej práce). Môj názor: KT a graf je fajn, pre riešenie uvedeného zadania ale preferujem spôsob, ktorý som popísal a vytvoril k nemu i vzor.

No, to bol len môj pokus, páč som hodinu nevedel prísť na nič iné. Ale, nebudeš veriť, pred malou chvíľou som prišiel na úplne jednoduchý postup:

Príslušný stĺpec stačí sformátovať ako text, žiadny pomocný stĺpec nie je treba!!

v tomto má MS Query muchy, dá sa to obísť tým, že si vytvoríš pomocný stĺpec v exceli so vzorcom:
=""&A2 (predpokladám, že pôvodný stĺpec bol A)
Do dotazu potom načítať dáta z pomocného, nie pôvodného stĺpca

Pokiaľ by som to riešil ja, tak by som:
Zo vstupnej tabuľky (predpokladám, že tá "databáza" predstavuje nejakú excelovskú tabuľku) vytvoril pomocou parametrického dotazu prostredníctvom MS Query výstup do QueryTable. Obsahom výstupu by potom boli akurát dáta za konkrétny diel za vybrané časové obdobie. Nad takto vytvorenou QueryTable by som vytvoril dynamickú pomenovanú oblasť (oblasti), ktorá by slúžila ako zdroj dát pre histogram a zároveň by slúžila pre výpočet štatistických veličín potrebných pre výpočet gaussovej krivky, t.j. priemeru, smerodatnej odchýlky, dolnej a hornej medze. Zostrojenie gaussovej krivky na základe uvedených hodnôt by nemal byť problém, na internete sú k tomu návody. Graf takto vytvorený (histogram kombinovaný s gaussovou krivkou, prípadne 2 samostatné grafy) by sa potom automaticky aktualizovali na základe zmeny parametrov (diel, dátumy) v parametrickom dotaze.
Čiže - jeden výstup, do ktorého by sa doťahovali dáta na základe zmeny vybraných parametrov, grafy by sa prekresľovali automaticky. Trocha SQL, pár vzorcov a pravdepodobne hotovo. VBA pmn v tomto prípade nie je nutné, snáď maximálne niekoľko riadkov kódu. Je možné, že ani vytvorenie pomenovaných oblastí by nutné nebolo, ale to by sa muselo najskôr vyskúšať.
K hore uvedenému je potrebná znalosť nasledujúceho:
1. MS Query - tvorba jednoduchých parametrických dotazov
2. Dynamické vymedzenie oblasti
3. Vzorce na vytvorenie datovej sady pre vytvorenie gaussovej krivky - ako som zmienil, postup je celý na internete

Ten index způsobilosti procesu je nejaká štatistická metrika, pokiaľ sú známe zdrojové dáta (QueryTable), tak nie je problém z toho nejakú štatistickú hodnotu spočítať..

edit: na generovanie náhodných čísel pre vykreslenie gaussovej krivky na základe známeho priemeru a štandartnej odchýlky by som nakoniec teda asi makro zvolil, ale ako som uviedol hore, bol by to skutočne len 1-2 riadky kódu, slúžiacich k predaniu parametrov pre spustenie rutiny Random z doplnku ATPVBAEN.XLAM (generovanie náhodných čísel pri normálnom rozdelení - doplnok Analytické nástroje). Bod 3 tým pádom v podstate odpadá, ten porieši makro. Bod 2 veľmi pravdepodobne nutný nebude, bod 1 je trivialita. Celá úloha sa mi nezdá nijako zložitá. Akurát som teda, priznám sa, nepochopil, k čomu formulár, keď pmn stačia dve-tri bunky s overením dát - jedna pre výber dielu a druhá (prípadne druhá a tretia) pre výber obdobia...
Vzhľadom k tomu, že by som vo finále postupoval trochu odlišne, než som predpokladal úvodom, ten popis je "trochu" chaotický. Ale nechcelo sa mi to už celé prepisovať, tak som popreškrtal časti, ktoré by som nakoniec asi nepoužil. Snáď sa v tom popise dá aspoň trochu orientovať, keby boli nejaké dotazy, tak sem s nimi.

Uživatelské menu

Nejste přihlášen(a)

• přihlásit
• registrace

\n

Menu

On-line nástroje

Formulář Faktura

Aktivní diskuse