Příspěvky uživatele
< návrat zpět
Otázkou je, čo sa myslí prepojením súborov. Pokiaľ sa má jednať o linkovanie súborov (Data ==>> Edit Links), tak ja osobne toto pokladám za vec, ktorá je pomerne náchylná na chyby a vyžaduje ošetrenie možnosti, že v zdrojovom súbore dojde ku zmene jeho štruktúry (posun buniek a pod.) v dobe, kedy nebude cieľový súbor otvorený a prepojenie nebude aktívne.
Prepojeniu súborov týmto spôsobom sa snažím, pokiaľ je to možné, vyvarovať. Určite bezpečnejšou alternatívou je vytvorenie datového prepojenia, buď pomocou MS Query, alebo ako prostredníka použiť DBMS (napr. Access alebo SQL server) a vec poriešiť so zapojením SQL, možnosťou zrejme môže byť i Power Query. Otázka, či je to elegantnejšie, je vec názoru, rozhodne je to ale bezpečnejšie. Samozrejme, nejaké dodatočné úsilie s tým spojené bude.
Ber to ako námet na zamyslenie, dopredu upozorňujem, aby som náhodou nevzbudil zbytočné očakávania, že to tvoriť nebudem.
v čom nevyhovuje kontingenčná tabuľka?
Jakube, ty vidíš v stĺpci A nejaké čísla? Ja ti to teda vysvetlím: nejedná sa o čísla, ale o textové reťazce!
Range(Cells(1, 1), Cells(Evaluate("=MAX(IF(A1:A1000<>"""", ROW(A1:A1000)))"), 11)).Select
Než som to sem dal, tak som to samozrejme otestoval a mne to vyberie iba po riadok, v ktorom je v stĺpci A hodnota väčšia, než nula - presne podľa zadania. Ale mám tu k dispozícii akurát Excel 2007, čo je verzia dávno za horizontom a okrem toho, tvoj súbor k dispozícii nemám, takže som testoval na tom, ktorý som si podľa popisu bol schopný vytvoriť sám.
Range(Cells(1, 1), Cells(Evaluate("=MAX(IF(A1:A1000>0, ROW(A1:A1000)))"), 11)).Select
Zmyslom môjho príspevku bolo poskytnúť riešenie. Bol som si vedomý toho, že s jeho aplikáciou môže byť problém, mimo iné z dôvodu, že zo zadania nie je úplne zrejmé, čo presne potrebuješ riešiť a preto som upozorňoval, podobne ako marjankaj, na to, že na základe prílohy by to bolo jednoduchšie, rýchlejšie a celkovo efektívnejšie. Miesto toho, aby si prílohu poskytol, tak si opakovane spochybňoval dôvody, prečo by si ju sem mal dať, keďže podľa teba je predsa jasné, čo chceš. Jasné to nie je a skončilo to podľa predpokladov: riešenie aplikovať nedokážeš (poradil si si nakoniec inak, v poriadku), úplne zbytočne špekuluješ nad mojimi mindrákami a celkovo mi prídeš, že frajera zo seba robíš ty.
Jinak opravdu netuším, k čemu bych ty matice měl nahrávat sem.
Trebárs preto, že žiadaš radu zadarmo. Máš asi dojem, že povinnosťou ľudí tuná je radiť bez toho, aby sa autor dotazu aspoň trochu snažil sám, trebárs tým, že vloží prílohu, aby si ju riešiteľ nemusel tvoriť miesto pohodlného žiadateľa o radu. Ono by sa tieto diskusie prestali veľmi rýchlo diať, keby všetci riešitelia začali žiadať za radu nejakú platbu, to by sa žiadatelia viac začali snažiť a prestali trochu hlúpo kecať..
No, a druhý dôvod "prečo" si uviedol vo svojej odpovedi vlastne sám. Makro, ktoré ti niekto napíše, nakoniec ani nie si schopný do svojho riešenia zakomponovať, píšeš sám, že VBA sa učiť nechceš. To je potom naozaj trochu zbytočné unúvať sa s akoukoľvek snahou o pomoc.
Lubo, Tvoje riešenie na 2007 funguje. Moje pokusy dostať to celé do jedného vzorca zlyhali, Excel to proste v tom maticovom tvare nezvládne prepočítať. Využijem riešenie, ktoré si mi poslal. Vyhnem sa aspoň triedeniu, pridať do tabuľky dva výpočtové stĺpce zas nie je až taká tragédia, hlavne, že to funguje. Ďakujem ešte raz.
Lubo, ďakujem za osvetlenie, z tej prílohy som to pochopil. Pekný vzorec. Skúsil by som ešte eliminovať pomocný stĺpec cv, v tom prípade ale zase bude treba maticový vzorec, a to sa uvidí, či bude použiteľné na veľkom rozsahu dát: =IF([@country]<>tblCountry[country];ROW();
IF(ISNA(MATCH([@vendor];tblVendor[vendor];0));ROW();
IF(MATCH([@country]&[@vendor]; [country]&[vendor]; 0)=(ROW()-ROW(tblSourceData[[#Headers];[country]]));ROW();0))). Ten rozšírený filter vyskúšam až zajtra, či pobeží na 2007, keby to šlo, tak to by bola špica, na 2016 funguje. Ráno dám vedieť, zatiaľ veľmi pekne ďakujem.
edit: Teraz som si uvedomil, že nedokážem v kritériu rozšíreného filtra eliminovať stĺpec cv, nakoľko to kritérium, pokiaľ zapíšem pomocou maticového vzorca, tak to nefunguje ani v 2016. Ako kritérium by som teda musel použiť zápis:
=IF(G11<>tblCountry[country]; FALSE;
IF(ISNA(MATCH(H11; tblVendor[vendor];0 ));FALSE;
MATCH(I11;tblSourceData[cv];0 ) <> ROW(G11)-ROW(tblSourceData[[#Headers];[country]])))
Takže budem filtrovať priamo v tabuľke buď pomocou vzorca Tvojho, alebo mojej maticovej modifikácie hore uvedenej, prípadne ešte zjednodušenej:=IF([@country]<>tblCountry[country];FALSE;
IF(ISNA(MATCH([@vendor];tblVendor[vendor];0));FALSE;
MATCH([@country]&[@vendor]; [country]&[vendor]; 0)<>(ROW()-ROW(tblSourceData[[#Headers];[country]]))))
Rozšírený filter je proste passé, zdá sa. Ráno sa každopádne ozvem.
Problém je, v tom, že rozbaľovanie a zbaľovanie je pri inštrukcii
.Protect Contents:=True, UserInterfaceOnly:=True
povolené len z VBA. Takže na Group a Ungroup si musíš pridať 2 malé kódy.Sub GroupSelection()
Selection.Group
End Sub
Sub UngroupSelection()
Selection.Ungroup
End Sub
Daj si ich pod nejaké tlačítko a máš vyriešené.
lubo, ďakujem za points, ale vôbec nie som v stave pochopiť túto časť Tvojho príspevku:
1. Pomocný sloupec s [country]&[vendor]
2. Samostatně (více sloupců nebo vnořené KDYŽ)
když(country; nechat;
když(dodavatel;nechat;
když(unikátní;nechat;smazat)))
Tak se eliminují zbytečné operace.
K hledání unikátních položek je vhodná funkce POZVYHLEDAT (MATCH):
=POZVYHLEDAT([@country]&[@vendor];[country]&[vendor];0)
Funkce vždy najde. Stačí výsledek porovnat s akt. řádkem.
Funkce je v nejhorším případě (nejsou duplicity) stejně rychlá jako countif. Doba se zkracuje při vyšším počtu duplicit (Pokud funkce najde shodu končí, countif počítá do konce). A samozřejmě záleží na pozici v seznamu.
Lze to trochu modifikovat:
když(country; řádek;
když(dodavatel;řádek;
když(unikátní;řádek;0)))
Pak lze odstranit duplicity (zatím jsem netestoval, jestli je rychlejší odstranění duplicit nebo odstranění vybraných.)
Buď nechápem, ako to myslíš, alebo je možné, že nechápeš presne Ty, čo potrebujem dosiahnuť ja. Ja potrebujem identifikovať tie bunky, ktoré som v riešení označil žltou farbou a ku ktorým som dal do listu advanced filter printscreen želaného výsledku.
Na to, že skalárny súčin nebude schodnou cestou pri použití pomocného vzorca, som prišiel. Samozrejme, pokiaľ dáta zotriedim podľa krajiny a podľa dodávateľov, tak to bude realizovateľné i pomocou vzorca, ktorý som zostavil. Ja som sa ale triedeniu dát chcel vyhnúť a napadlo ma riešiť to cez rozšírený filter. Vtip je v tom, že ten filter v 2016 funguje, ale v 2007 nie. Zaujímalo ma, prečo tomu tak je.
Inak ale, pokiaľ by si bol tak láskavý a do mojej prílohy vložil svoje riešenie, ktoré, ako som zmienil, z Tvojho popisu nechápem, tak by som Ti bol vskutku vďačný.
pepe, fajn, bohužiaľ, PQ je pre mňa v práci v súčasnosti mimo možnosti použiteľnosti, nakoľko 2007 PQ nemá. Čo sa týka nástroja samotného, tak okrem výhod, ktoré uvádzaš, má i svoje nevýhody. Neumožňuje totiž, na rozdiel od Accessu, spojiť v jednom kroku viac, než dve tabuľky, to je tá vlastnosť, na ktorú poukazujem, pokiaľ som zmienil jeho ťažkopádnosť. Teda, aby som bol presný, za tú chvíľu, ktorú som to študoval, som neprišiel na spôsob, ako spojiť viac než dve tabuľky v jednom kroku a je preto potrebné použiť postupnosť niekoľkých krokov. Je v tomto ohľade na tom podobne ako MS Query. Dostatočne dobré pre užívateľov bez skúseností s SQL alebo prístupu ku skutočne plnohodnotnému DBMS (Access, SQL server apod.). Pokiaľ ale niekto čuchne k DBMS, tak sa domnievam, že z PQ zas až tak nadšený nebude. Vďaka za snahu, v túto chvíľu by ma ale viac zaujímalo, ako prípadne v 2007 rozchodiť ten rozšírený filter.
Na odstránenie externých linkov si v takom prípade asi budeš musieť napísať makro, ktoré v cykle prejde všetky bunky a vyhodnotí, či sa v nich nachádza externý link. Napr. takto, ale moc som to netestoval (odstráni externé linky z nezamknutých buniek, ale pozor, pokiaľ sa link nachádza vo vzorci, celý vzorec nemilosrdne zmaže a nahradí ho hodnotou). Na to, čo potrebuješ, je to asi funkčné, ale prílohu si sem zatiaľ nedal, takže je otázka, či to bude vyhovovať a nenapácha to viac škôd, než úžitku. Sub RemoveExternalLinks()
Dim sh As Worksheet, myRng As Range, cell As Range, mySh As Worksheet
For Each sh In ActiveWorkbook.Sheets
Set myRng = ActiveSheet.UsedRange
For Each cell In myRng
If InStr(cell.Formula, "!") > 0 And cell.Locked = False Then cell.Value = cell
Next cell
Set myRng = Nothing
Next sh
End SubPokiaľ ale vkladáš tie linky podľa návodu od eLCHy, tak po tom, čo dáš ten ctrl+enter, spusti nasledújúci kód, ktorý odstráni linky len vo vybranej oblasti:Sub RemoveExternalLinksInSelection()
Dim cell As Range
For Each cell In Selection
If InStr(cell.Formula, "!") > 0 Then cell.Value = cell
Next cell
End Sub Ďalej sa snaž sám.
Sľúbil som, že sa podelím o svoje riešenie. Mal som síce v úmysle počkať, s čím prípadne príde niekto iný, okrem pepeho sa ale nikto neozval, tak dávam svoje riešenie už teraz a to z nasledujúceho dôvodu: Na 2016 mi to funguje, na 2007 k môjmu prekvapeniu nie a bol by som rád, pokiaľ by niekto bol schopný prísť na to, v čom je u 2007 zakopaný pes. Na liste advanced filter je riešenie cez rozšírený filter, na liste formula riešenie cez pomocný stĺpec. Riešenie cez pomocný stĺpec v reále použiť nemôžem, na stotisícovom počte záznamov to proste excel v práci nie je schopný prepočítať. Dúfal som, že problém obídem prostredníctvom rozšíreného filtra, to sa mi ale na 2007 vôbec rozchodiť nepodarilo - výsledkom po aplikácii rozšíreného filtra je prázdny zoznam, napriek tomu, že v 2016 sú to tie žlté riadky podľa môjho predpokladu. Pokiaľ by to niekoho zaujímalo, tak popis problému je v prílohe. Vzorec použitý ako kritérium pre filtrovanie je pomerne jednoduchý, v bunke F7:
=SUMPRODUCT((($F$11:F11&$G$11:G11)=(F11&G11))*(F11=tblCountryAF[country])*COUNTIF(tblVendorAF[vendor]; G11))<2=FALSE
Vôbec netuším, prečo to v 2007 vyhodnocuje špatne a inak než v 2016. Príde na to niekto?
edit: samozrejme, že pokiaľ by som dáta zosortoval podľa dodávateľa, tak vzorec môžem použiť iba na vybraného dodávateľa a toto už 2007 zvládne, triedeniu dát sa ale snažím vyhnúť napriek tomu, že pôvodné poradie ošetriť dokážem pomocným stĺpcom. V túto chvíľu ma zaujíma, prečo ten rozšírený filter v 2007 nezafunguje korektne/podľa predstáv.
Ja písanému textu rozumiem a info o tom, že je súbor zamknutý, som zachytil. Preto nejde zrušiť prepojenia. Nenapísal si ale, že súbor odomknúť nedokážeš.
Uživatelské menu
\nMenu
On-line nástroje
Formulář Faktura
Formulář Faktura IV
Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.
RSS nejnovější články