poraďte mi prosím vzorec na tuto situaci (rád bych se vyhnul makrům).
Mám soubor Vyhodnocení.xls, kde se vyhodnocují data z externích xls souborů, a soubory LV-06-10.xls a CA-06-10.xls, kde je vždy obsazena buňka A1 v listu List1.
Soubory LV-06-10.xls a CA-06-10.xls jsou umístěny na C:\Excel Soubory\2010\06.
Potřebuji, abych v souboru Vyhodnocení.xls zadával měsíc, rok, a název závodu. Na základě těchto 3 údajů se mi vygeneruje cesta k souboru, ze kterého chci číst (např pro měsíc 06, rok 2010 a název závodu LV je to soubor LV-06-10.xls, který je umístěn na C:\Excel Soubory\2010\06). Jsem schopen si na základě zadaných údajů za pomocí funkce CONCATENATE vygenerovat celou cestu ke správnému souboru i s názvem souboru.
Potřebuji ale, abych na základě této vygenerované cesty, dokázal přečíst buňku A1 v listu List1 v cílovém souboru.
Doufám, že jsem to vysvětlil srozumitelně, snad ještě pomohou obrázky:
Soubor Vyhodnocení.xls:
Soubor LV-06-10.xls umístěný v C:\Excel Soubory\2010\06:
Poraďte mi prosím vzorec do buňky E12 v souboru Vyhodnocení.xls. Děkuji vám za pomoc!
Ano, NEPŘÍMÝ.ODKAZ - to je ta funkce, kterou jsem hledal. Ale je pravda, že to funguje pouze v případě, když mám zdrojový soubor, ze kterého chci číst data, otevřený.
Máte někdo tip na vzorec, který by fungoval i v případě, když bude zdrojový soubor zavřený?
Děkuji za odkaz na soubory s příklady. Příklady ale ukazují pouze, jak se odkázat na data z jiného xls souboru. S tímto problém nemám.
Mám problém s tím, že mám vzorec (napsán TEXTOVĚ v buňce) a potřebuji, abych ho rozpohyboval do regulérního vzorce. Toto zprostředkuje funkce NEPŘÍMÝ.ODKAZ. Jenomže ten textový vzorec odkazuje na buňky v jiném xls souboru. A pokud textový vzorec rozpohybuji pomocí funkce NEPŘÍMÝ.ODKAZ, pak vzorec funguje jen tehdy, když mám cílový xls soubor, na který odkazuji, otevřený na liště. Potřebuji, aby to fungovalo, i když bude cílový xls soubor zavřený.
Nicméně velmi děkuji za snahu a zároveň prosím dále o pomoc
Pěkný příklad, díky, přesto mám pochybnost.
Chápu správně, že to jde, ale pouze je-li cesta
ve vzorci napsána napevno, takže generování
cesty k souboru a dosazení do vzorce nelze
dosáhnout a tento požadavek vůbec neřeší ?
Možností pro řešení první ukázky je seznam
s již předem vytvořenými vzorci s cestami,
pak v zadání zobrazení nevytvářet cestu, ale
vlastně odkaz na buňku se vzorcem s cestou,
ale nezdá se mi to moc elegantní, původní
záměr byl lepší a víc se mi líbil.
Jinak bude nutné se smířit s otevíráním sešitů
pro použití funkce nepřímý odkaz....
Opravdu neexistuje jiná možnost?
Přes importovaná data? SQL.REQUEST?
Autor
RE: Odkaz na buňku v jiném souboru (soubor se mění)
Bath napsal:
Chápu správně, že to jde, ale pouze je-li cesta
ve vzorci napsána napevno, takže generování
cesty k souboru a dosazení do vzorce nelze
dosáhnout a tento požadavek vůbec neřeší ?
Ano, přesně tak. Pokud mám cestu k souboru ve vzorci napevno, lze z něj číst, i když je soubor zavřený. Pokud ale mám cestu k souboru napsanou textově v nějaké buňce a tuto cestu vložím do vzorce pomocí funkce NEPŘÍMÝ.ODKAZ, pak lze ze souboru číst data pouze tehdy, je-li otevřený.
NEBOLI:
PRVNÍ TYP ZÁPISU:
pokud do některé buňky napíšu ='C:\Název složky\[Data.xls]List1'!A1
Pak jsem schopen přečíst buňku A1 v listu List1 v souboru Data.xls i v případě, že je tento soubor zavřený
DRUHÝ TYP ZÁPISU:
pokud některé do buňky napíšu =NEPŘÍMÝ.ODKAZ(B2)
kde v buňce B2 je textově zapsaná cesta: 'C:\Název složky\[Data.xls]List1'!A1
Pak jsem schopen přečíst buňku A1 v listu List1 v souboru Data.xls pouze v případě, když je soubor otevřený.
A protože cesta k souboru a název souboru, ze kterého chci číst data je generován na zákaldě zadaných parametrů (je proměnlivý), nemohu použít první typ zápisu, protože je neproměnlivý. Musím nejprve textově vygenerovat celou cestu k souboru (pomocí funkce CONCATENATE) a jeho název a pak musím použít NEPŘÍMÝ.ODKAZ, abych z vygenerované textové cesty udělal vzorec.
Potřebuji pracovat se stovkou externích souborů, proto tedy nemohu každý otevírat. Potřebuji z nich číst, i když jsou zavřené.
Bath napsal:
Možností pro řešení první ukázky je seznam
s již předem vytvořenými vzorci s cestami,
pak v zadání zobrazení nevytvářet cestu, ale
vlastně odkaz na buňku se vzorcem s cestou,
ale nezdá se mi to moc elegantní, původní
záměr byl lepší a víc se mi líbil.
Vašemu návrhu moc nerozimím, popište jej prosím podrobněji.
Bath napsal:
Opravdu neexistuje jiná možnost?
Přes importovaná data? SQL.REQUEST?
Napište mi prosím, jak jste to myslel s tou SQL databází - to už ale asi nebude přes vzorce, že? Nebo SQL.REQUEST je vzorec? Velmi děkuji za odpověď.
Prosím i o další návrhy, jak situaci řešit. Je i jiná možnost, jak udělat z textu vzorec kromě použití funkce NEPŘÍMÝ.ODKAZ?
Nedovedu to srozumitelně popsat,
ale zkusim příkládek, bude-li lepší,
omlouvám se.
Podrobněji k předchozímu návrhu:
Seznam se vzorci G1:G12
Vzorce v jednotlivých buňkách, pevně zadané cesty:
G1: ='C:\Excel Soubory\2010\[LV10-01.xls]List1'!$A$1
G2: ='C:\Excel Soubory\2010\[LV10-02.xls]List1'!$A$1
G3: ='C:\Excel Soubory\2010\[LV10-03.xls]List1'!$A$1
... atd. až po
G12: ='C:\Excel Soubory\2010\[LV10-12.xls]List1'!$A$1
Vzorec pro zobrazení dle zadání:
E12: =POSUN($G$1;$C$5-1;$C$6-2010;1;1)
Převezme hodnotu příslušné buňky dle zadání měsíce,
bude-li ve sloupci H obdobně zadáno pro rok 2011
nebo ve sloupci F pro rok 2009 bude posun i dle roku,
ale nutno si ještě ošetřit vstupní hodnoty a chyby...
Asi to moc nenadchne, nic světoborného... :)
Doplněno 21.7.2010 (10 řádků včetně):
A vlastně ještě sloupečky dle názvu závodu, záleží
kolik jich asi bude, jinak je to pěkný vopruz... :(
V určitých mezích to asi poskládat vzorci půjde. :)
Na můj vkus jsou to kvanta sešitů složitě v adresáři,
raději složka jen pro rok a co list v sešitu to měsíc
s názvy 2010(1) až 2010(12), pak název sešitu jen
LV10.xls nebo CA10.xls, dost možná jen Agenda10
a firmy pořešit jinak. Samozřejmě netuším záměr
a rozsah celého projektu... :) Není to žádná kritika!
Snad to bude k pochopení... ?
Skládání cesty elegantnější, ale zase ta protivenství
s předáním cesty jako vzorec...
...
Ke spekulacím závěrem:
SQL.REQUEST by mělo jít použít i ve vzorci, nemám
s tím zkušenost proto bylo s otazníkem, ani nevím,
zda-li lze použít doplněk, který funkci obsahuje, pro
verzi 2003 či 2007, snad má někdo více zkušeností.
Nápad spočíval ve vytvoření connection pro daný sešit
a pomocí sestaveného dotazu získat data dle měsíce,
roku i závodu přímo do buňky.
Existuje nějaká podobná funkce s použitím ve vzorci?
Vytvořit si vlastní funkci, která by to uměla?
...
Co nějak využít ADO ? Tipy pro inspiraci:
http://technet.microsoft.com/en-us/library/ee692882.aspx
http://support.microsoft.com/kb/257819
Upravil Bath, 21.07.2010 08:02
Autor
RE: Odkaz na buňku v jiném souboru (soubor se mění)
Tak sem ted hledal jeste na netu a nenasel sem nic rozumneho. Funkce Neprimy.odkaz opravdu nepracuje s zavrenymy sesity jak jste psali, je to napsane i v napovede.
Zkusil sem obratit pozornost na XLM funkce (Excel4) kde jsou nejake moznosti, ale nedokazl sem rozchodit vzorce
GET.WORKBOOK a GET.CELL, ktere by mozna, ale opravdu jen mozna pomohly.
Takze ja nevim, co si udelat doplnek, ktery by obsahoval trosku VBA kodu a ten by zajistoval pozadovanou funkcnost?
Nikdy neni tak zle, aby nemohlo byt jeste hur :-)
Office 2007 EN & Office 2003 CZ & Office 2000 EN & Office 2010 EN
Autor
RE: Odkaz na buňku v jiném souboru (soubor se mění)
K PRVNÍ ČÁSTI TVÉHO PŘÍSPĚVKU:
Ano, to by teoreticky pomohlo, ale pouze v případě, že budu ze zdrojového souboru číst pouze buňku A1. Ve skutečnosti poutřebuji vyčíst celou tabulku ze zdrojového souboru (nejen buňku A1). Ale to bych sem nechtěl plést, aby to neudělalo můj příklad zbytečně složitý.
KE DRUHÉ ČÁSTI TVÉHO PŘÍSPĚVKU:
Funkci SQL.REQUEST vůbec neznám, ani ji nemám mezi vzorci ve verzi Office 2003 k dispozici. Pokusím se o této funkci zjistit nějaké informace
PRO PREMKA:
Díky za tipy a za tvůj čas strávený při hledání. Zkusím zjistit nějaké informace o tebou zmiňovaných vzorcích. Do VBA bych se nerad pouštěl, protože s tím nemám žádnou zkušenst. Bohužel časové možnosti ve firmě mi nedovolí učit se základy VBA v Excelu, abych pak byl schopen něco naprogramovat. Ani netuším, jaký level by bylo naprogramovat doplňek, který by zajišťoval čtení buněk z externích zavřených xls souborů. Proto bych se rád VBA vyhnul.
"Proto bych se rád VBA vyhnul."
V tomto případě příspěvek již bez přínosu. :(
...
"trosku VBA kodu"
Zkoušeno předat cestu Application.Evaluate
jako pokus o vlastní vba funkci, ale výsledkem
opět stejná funkce (nepřímý odkaz), převede
na objekt jen je-li otevřený. :(
Vypadalo to tak nadějně, ale nedaří se rozchodit
při zavřeném sešitu. :)
...
"Ve skutečnosti potřebuji vyčíst celou tabulku ze zdrojového souboru"
Teoretický příklad nevyhověl celkovému objemu... :( Marná snaha. :(
Viz tip na: ADO
Byla by to předem určená nebo pojmenovaná oblast? Celé listy?
Pojmenované záhlaví slopuců? (později jako Fields)
Stejná struktura tabulek v jednotlivých sešitech?
Závislé od struktury tabulek v sešitech, ale realizovatelné, příp.
využít i sestavení dotazu dle závodu aj. nebo vytahat data sešitu
do jednoho sešitu a dotáhnout vzorci. Co myslíte? Zkusit příklad?
...
Tento příklad lze nalézt ve vba nápovědě:
The GET.CELL(42) macro function returns the horizontal distance from the left edge of the active window to the left edge of the active cell. This macro function has no direct Visual Basic equivalent.
Kód VBA
Worksheets("Sheet1").Activate
Range("C3").Select
MsgBox ExecuteExcel4Macro("GET.CELL(42)")
Dotaz ke zmíněným GET.WORKBOOK a GET.CELL...
Nezdá se mi, že to bude ve vzorcích podporováno,
ale nešlo by to protlačit přes ExecuteExcel4Macro
a schovat do vlastní vba funkce?
Kde nalézt nějaké detaily a pěkné info pro využití
pro verzi 2003 či 2007?
(Reference Excel 4 (XLM) maker, čísla funkcí, získat
vlastnosti buňky, font, barva apod.)
...
Případné další info vítáno.
Autor
RE: Odkaz na buňku v jiném souboru (soubor se mění)
...
INDIRECT.EXT nezkoušeno, nutnost
instalace doplňku autora funkce
...
SQL.REQUEST prý nebude více podporováno
(asi bude v nějakém starším MS doplňku)
...
PULL splní požadavek, funguje
...
GETVALUE ve VBA funguje bez problémů, ale
pokud změním na public funkci, tak se nedaří
zatím rozchodit pro použití pro vzorce v listu.
Dejte případně vědět, kdyby to někdo přemohl!
Omezení pro XLM funkce?
Lze použít pouze v listech excel4macro,
v definovaných názvech nebo volat z vba?
