Jak Excelem putují data

Tady jsou hlavní věci, které můžete dělat, když jsou data ve stanici Excelu:

• Dovoz    Data můžete importovat z mnoha různých externích zdrojů dat. Tyto zdroje dat můžou být na vašem počítači, v cloudu nebo na půli cesty po celém světě. Další informace najdete v tématu Import dat z externích zdrojů dat.

• Power Query    Pomocí Power Query (dříve označovaného jako Get & Transform) můžete vytvářet dotazy pro tvarování, transformaci a kombinování dat různými způsoby. Svou práci můžete exportovat jako šablonu Power Query a definovat tak operaci toku dat v Power Apps. Můžete dokonce vytvořit datový typ, který doplní propojené datové typy. Další informace najdete v Power Query nápovědy aplikace Excel.

• Bezpečnost    Ochrana osobních údajů, přihlašovací údaje a ověřování jsou vždy trvalým problémem. Další informace najdete v tématech Správa nastavení a oprávnění zdroje dat aNastavení úrovní ochrany osobních údajů.

• Osvěžit    Importovaná data obvykle vyžadují operaci aktualizace, která do Excelu přenese změny, například přidání, aktualizace a odstranění. Další informace najdete v článku Aktualizace externího datového připojení v Excelu.

• Connections/vlastnosti    Každý externí zdroj dat má přidružené informace o připojení a vlastnosti, které někdy vyžadují změny v závislosti na vašich okolnostech. Další informace najdete v tématech Správa oblastí externích dat a jejich vlastností, Vytváření, úpravy a správa připojení k externím datům a Vlastnosti připojení.

• Dědictví    Tradiční metody, jako jsou starší průvodci importem a MSQuery, jsou stále k dispozici pro použití. Další informace najdete v tématech Možnosti importu a analýzy dat a Načtení externích dat pomocí Microsoft Query.

V následujících částech najdete další podrobnosti o tom, co se děje na pozadí tohoto rušného vlakového nádraží v Excelu.

K dispozici jsou vlastnosti rozsahu připojení, dotazu a externích dat. Vlastnosti připojení i dotazu obsahují tradiční informace o připojení. Vlastnosti připojení v názvu dialogového okna znamenají, že k němu není přidružený žádný dotaz, ale vlastnosti dotazu znamenají, že existuje. Vlastnosti oblasti externích dat řídí rozložení a formát dat. Všechny zdroje dat mají dialogové okno Vlastnosti externích dat , ale zdroje dat, které mají přidružené přihlašovací údaje a informace o aktualizaci, používají větší dialogové okno Vlastnosti dat externí oblasti .

Následující informace shrnují nejdůležitější dialogová okna, podokna, cesty k příkazům a odpovídající témata nápovědy.

Dialogové okno nebo podokno Cesty k příkazům	Karty a tunely	Hlavní téma nápovědy
Poslední zdroje Data > poslední zdroje	(Žádné karty) Tunely pro připojení > Navigátor	Správa nastavení a oprávnění zdroje dat
Vlastnosti připojení Nebo Průvodce datovým připojením Data > Dotazy & Connections > Connections kartě > (klikněte pravým tlačítkem na připojení) > Vlastnosti	Karta Využití Karta Definice Použitá na kartě	Vlastnosti připojení
Vlastnosti dotazu Data > Existující Connections > (klikněte pravým tlačítkem na připojení) > Upravit vlastnosti připojení Dotazy na> dat OR & připojení| Karta Dotazy > (klikněte pravým tlačítkem na připojení) > Vlastnosti VLASTNOSTI> dotazů OR NEBO Data > Aktualizovat všechny > Connections (při umístění na načteném listu dotazu)	Karta Využití Karta Definice Použitá na kartě	Vlastnosti připojení
& Connections dotazů & Connections dotazy> dat	Karta Dotazy Connections  tab	Vlastnosti připojení
Existující Connections Data > Existující Connections	Connections karta Tabulky	Připojení k externím datům
Vlastnosti externích dat OR Vlastnosti oblasti externích dat NEBO Vlastnosti> dat (zakázáno, pokud nejsou umístěné na listu dotazu)	Používá se na kartě (z dialogového okna Vlastnosti připojení )   Tlačítko Aktualizovat na pravém tunelu na vlastnosti dotazu	Správa oblastí externích dat a jejich vlastností
Vlastnosti připojení > kartě Definice > Export souboru připojení NEBO dotaz > export souboru připojení	(Žádné karty) Dialogové okno Tunely do souboru Složkazdrojů dat	Vytvoření, úprava a správa připojení k externím datům

Data v excelovém sešitu můžou pocházet ze dvou různých umístění. Data mohou být uložena přímo v sešitu nebo v externím zdroji dat, jako je textový soubor, databáze nebo datová krychle OLAP (Online Analytical Processing). Tento externí zdroj dat je připojen k sešitu prostřednictvím datového připojení, což je sada informací, které popisují, jak externí zdroj dat vyhledat, přihlásit se k němu a jak k němu přistupovat.

Hlavní výhodou připojení k externím datům je, že tato data můžete pravidelně analyzovat bez opakovaného kopírování dat do sešitu, což je operace, která může být časově náročná a náchylná k chybám. Po připojení k externím datům můžete také automaticky aktualizovat (nebo aktualizovat) excelové sešity z původního zdroje dat pokaždé, když se zdroj dat aktualizuje novými informacemi.

Informace o připojení jsou uloženy v sešitu a můžou být také uloženy v souboru připojení, například v souboru odc (.odc) nebo v souboru s názvem zdroje dat (DSN).

Pokud chcete do Excelu přenést externí data, potřebujete k datům přístup. Pokud externí zdroj dat, ke kterému chcete získat přístup, není ve vašem místním počítači, možná budete muset požádat správce databáze o heslo, uživatelská oprávnění nebo jiné informace o připojení. Pokud je zdrojem dat databáze, ujistěte se, že databáze není otevřena ve výhradním režimu. Pokud je zdrojem dat textový soubor nebo tabulka, ujistěte se, že je jiný uživatel nemá otevřený pro výhradní přístup.

Mnoho zdrojů dat také vyžaduje ovladač ODBC nebo zprostředkovatel OLE DB ke koordinaci toku dat mezi Excelem, souborem připojení a zdrojem dat.

Následující diagram shrnuje klíčové body datových připojení.

1. Existuje celá řada zdrojů dat, ke kterým se můžete připojit: Analysis Services, SQL Server, Microsoft Access, další databáze OLAP a relační databáze, tabulky a textové soubory.

2. Mnoho zdrojů dat má přidružený ovladač ODBC nebo zprostředkovatel OLE DB.

3. Soubor připojení definuje všechny informace potřebné pro přístup k datům a jejich načtení ze zdroje dat.

4. Informace o připojení se zkopírují ze souboru připojení do sešitu a informace o připojení lze snadno upravit.

5. Data se zkopírují do sešitu, takže je můžete použít stejně jako data uložená přímo v sešitu.

Pokud chcete najít soubory připojení, použijte dialogové okno Existující Connections. (Vyberte Data > Existující Connections.) V tomto dialogovém okně uvidíte následující typy připojení:

• Connections v sešitu    

  Tento seznam zobrazuje všechna aktuální připojení v sešitu. Seznam se vytvoří z již definovaných připojení, která jste vytvořili pomocí dialogového okna Vybrat zdroj dat v Průvodci datovým připojením, nebo z připojení, která jste dříve vybrali jako připojení v tomto dialogovém okně.

• Soubory připojení na počítači    

  Tento seznam je vytvořen ze složky Moje zdroje dat , která je obvykle uložena ve složce Dokumenty.

• Soubory připojení v síti    

  Tento seznam je možné vytvořit ze sady složek v místní síti, jejichž umístění je možné nasadit v síti v rámci nasazení zásad skupiny Microsoft Office, nebo ze sharepointové knihovny. 

Excel můžete použít také jako editor souborů připojení k vytváření a úpravám připojení k externím zdrojům dat uloženým v sešitu nebo v souboru připojení. Pokud požadované připojení nenajdete, můžete ho vytvořit tak, že kliknete na Vyhledat další , zobrazí se dialogové okno Vybrat zdroj dat a potom kliknutím na Nový zdroj spustíte Průvodce datovým připojením.

Po vytvoření připojení můžete pomocí dialogového okna Vlastnosti připojení (Vybrat data > Dotazy & Connections > Connections kartě > (klikněte pravým tlačítkem na připojení) > Vlastnosti) k řízení různých nastavení připojení k externím zdrojům dat a k používání, opakovanému použití nebo přepínání souborů připojení.

Poznámka    Někdy se dialogové okno Vlastnosti připojení nazývá dialogové okno Vlastnosti dotazu, pokud je k němu přidružený dotaz vytvořený v Power Query (dříve označovaný jako Získat & transformaci).

Pokud pro připojení ke zdroji dat použijete soubor připojení, Excel zkopíruje informace o připojení ze souboru připojení do excelového sešitu. Když provedete změny pomocí dialogového okna Vlastnosti připojení , upravujete informace o datovém připojení, které jsou uložené v aktuálním excelovém sešitu, a ne původní soubor datového připojení, který mohl být použit k vytvoření připojení (označený názvem souboru, který je zobrazen ve vlastnosti Soubor připojení na kartě Definice ). Po úpravě informací o připojení (s výjimkou vlastností Název připojení a Popis připojení ) se odebere odkaz na soubor připojení a vlastnost Soubor připojení se vymaže.

Pokud chcete zajistit, aby se soubor připojení vždy používal při aktualizaci zdroje dat, klikněte na Vždy se pokusit použít tento soubor k aktualizaci těchto dat na kartě Definice . Zaškrtnutím tohoto políčka zajistíte, že aktualizace souboru připojení budou vždy používány všemi sešity, které používají tento soubor připojení, které musí mít také nastavenou tuto vlastnost.

Pomocí dialogového okna Connections můžete tato připojení snadno spravovat, včetně jejich vytváření, úprav a odstraňování (Výběr možnosti Data > Dotazy & Connections > Connections kartě > (klikněte pravým tlačítkem na připojení) > Vlastnosti.) Pomocí tohoto dialogového okna můžete dělat tohle: 

• Vytvářet, upravovat, aktualizovat a odstraňovat připojení, která se v sešitu používají.

• Ověřte zdroj externích dat. Můžete to udělat v případě, že připojení definoval jiný uživatel.

• Zobrazit, kde jsou jednotlivá připojení v aktuálním sešitu použitá.

• Diagnostikovat chybovou zprávu o připojeních k externím datům.

• Přesměrovat připojení na jiný server nebo zdroj dat, nebo nahradit soubor připojení pro stávající připojení.

• Snadno vytvářet a sdílet soubory připojení s uživateli.

Soubory připojení jsou obzvláště užitečné pro konzistentní sdílení připojení, což zvyšuje zjistitelnost připojení, pomáhá zlepšit zabezpečení připojení a usnadňuje správu zdrojů dat. Nejlepší způsob, jak sdílet soubory připojení, je umístit je do zabezpečeného a důvěryhodného umístění, jako je síťová složka nebo knihovna SharePointu, kde uživatelé můžou soubor číst, ale soubor můžou upravovat jenom určení uživatelé. Další informace najdete v tématu Sdílení dat s ODC.

Použití souborů ODC

Soubory ODC (.odc) office (.odc) můžete vytvořit připojením k externím datům prostřednictvím dialogového okna Vybrat zdroj dat nebo pomocí Průvodce datovým připojením pro připojení k novým zdrojům dat. Soubor ODC používá k ukládání informací o připojení vlastní značky HTML a XML. Obsah souboru můžete snadno zobrazit nebo upravit v Excelu.

Soubory připojení můžete sdílet s jinými uživateli, abyste jim poskytli stejný přístup jako vy k externímu zdroji dat. Jiní uživatelé nemusí pro otevření souboru připojení nastavovat zdroj dat, ale možná budou muset nainstalovat ovladač ODBC nebo zprostředkovatele OLE DB potřebného pro přístup k externím datům na svém počítači.

Soubory ODC jsou doporučenou metodou pro připojení k datům a sdílení dat. Další tradiční soubory připojení (DSN, UDL a soubory dotazů) můžete snadno převést na soubor ODC tak, že soubor připojení otevřete a potom kliknete na tlačítko Exportovat soubor připojení na kartě Definice v dialogovém okně Vlastnosti připojení .

Použití souborů dotazů

Soubory dotazů jsou textové soubory, které obsahují informace o zdroji dat, včetně názvu serveru, na kterém se data nacházejí, a informací o připojení, které zadáte při vytváření zdroje dat. Soubory dotazů představují tradiční způsob sdílení dotazů s ostatními uživateli Excelu.

Použití souborů dotazů .dqy    Pomocí aplikace Microsoft Query můžete uložit soubory .dqy, které obsahují dotazy na data z relačních databází nebo textových souborů. Když tyto soubory otevřete v aplikaci Microsoft Query, můžete zobrazit data vrácená dotazem a upravit dotaz tak, aby načítal různé výsledky. Soubor .dqy můžete uložit pro libovolný dotaz, který vytvoříte, a to buď pomocí Průvodce dotazem, nebo přímo v aplikaci Microsoft Query.

Použití souborů dotazů .oqy    Soubory .oqy můžete uložit pro připojení k datům v databázi OLAP, a to buď na serveru, nebo v soubor offline datové krychle (.cub). Pokud použijete Průvodce vícerozměrným připojením v aplikaci Microsoft Query k vytvoření zdroje dat pro databázi OLAP nebo datovou krychli, automaticky se vytvoří soubor .oqy. Vzhledem k tomu, že databáze OLAP nejsou uspořádané do záznamů nebo tabulek, nemůžete vytvářet dotazy ani soubory .dqy pro přístup k těmto databázím.

Použití souborů dotazů .rqy    Aplikace Excel může otevírat soubory dotazů ve formátu .rqy, aby podporovala ovladače zdroje dat OLE DB, které používají tento formát. Další informace najdete v dokumentaci k vašemu ovladači.

Použití souborů dotazů .qry    Aplikace Microsoft Query může otevírat a ukládat soubory dotazů ve formátu .qry pro použití se staršími verzemi aplikace Microsoft Query, které nemůžou otevřít soubory .dqy. Pokud máte soubor dotazu ve formátu .qry, který chcete použít v Excelu, otevřete ho v aplikaci Microsoft Query a pak ho uložte jako soubor .dqy. Informace o ukládání souborů .dqy najdete v nápovědě k aplikaci Microsoft Query.

Použití souborů webových dotazů .iqy    Excel může otevřít soubory webových dotazů .iqy a načíst data z webu. Další informace najdete v tématu Export do Excelu ze SharePointu.

Oblast externích dat (označovaná také jako tabulka dotazů) je definovaný název nebo název tabulky, který definuje umístění dat přenesených do listu. Když se připojíte k externím datům, Excel automaticky vytvoří oblast externích dat. Jedinou výjimkou je sestava kontingenční tabulky připojená ke zdroji dat, která nevytvoří oblast externích dat. V Excelu můžete naformátovat a rozvrhnout oblast externích dat nebo ji použít ve výpočtech, stejně jako u jiných dat.

Excel automaticky pojmenuje oblast externích dat následujícím způsobem:

• Rozsahy externích dat ze souborů ODC (Office Data Connection) mají stejný název jako název souboru.

• Rozsahy externích dat z databází jsou pojmenované s názvem dotazu. Ve výchozím nastavení je Query_from_source název zdroje dat, který jste použili k vytvoření dotazu.

• Oblasti externích dat z textových souborů mají název textového souboru.

• Rozsahy externích dat z webových dotazů jsou pojmenovány názvem webové stránky, ze které byla data načtena.

Pokud list obsahuje více než jednu oblast externích dat ze stejného zdroje, jsou oblasti očíslované. Například MyText, MyText_1, MyText_2 atd.

Oblast externích dat má další vlastnosti (nezaměňovat s vlastnostmi připojení), které můžete použít k řízení dat, například zachování formátování buněk a šířky sloupce. Tyto vlastnosti oblasti externích dat můžete změnit kliknutím na vlastnosti ve skupině Connections na kartě Data a provedením změn v dialogových oknech Vlastnosti oblasti externích dat nebo Vlastnosti externích dat.

Existuje několik datových objektů (například oblast externích dat a sestava kontingenční tabulky), které můžete použít pro připojení k různým zdrojům dat. Typ zdroje dat, ke kterému se můžete připojit, se ale mezi jednotlivými datovými objekty liší.

Připojená data můžete používat a aktualizovat v Excel Services. Stejně jako u jakéhokoli externího zdroje dat možná budete muset ověřit svůj přístup. Další informace najdete v článku Aktualizace externího datového připojení v Excelu. Fdalší informace o přihlašovacích údaji najdete v tématu Excel Services Nastavení ověřování.

Následující tabulka shrnuje, které zdroje dat jsou podporovány pro jednotlivé datové objekty v Excelu.

Vynikat data objekt	Vytváří Externí data rozmezí?		TECHNOLOGIE OLE DB	ODBC	Text soubor	HTML soubor	XML soubor	SharePoint seznam
Průvodce importem textu	Ano		Ne	Ne	Ano	Ne	Ne	Ne
Sestava kontingenční tabulky (bez OLAP)	Ne		Ano	Ano	Ano	Ne	Ne	Ano
Sestava kontingenční tabulky (OLAP)	Ne		Ano	Ne	Ne	Ne	Ne	Ne
Excel Table	Ano		Ano	Ano	Ne	Ne	Ano	Ano
Mapování XML	Ano		Ne	Ne	Ne	Ne	Ano	Ne
Webový dotaz	Ano		Ne	Ne	Ne	Ano	Ano	Ne
Průvodce datovým připojením	Ano		Ano	Ano	Ano	Ano	Ano	Ano
Microsoft Query	Ano		Ne	Ano	Ano	Ne	Ne	Ne

Excel Services alternativní řešení pro excelové tabulky a pojmenované oblasti

Pokud chcete zobrazit excelový sešit v Excel Services, můžete se připojit k datům a aktualizovat je, ale musíte použít sestavu kontingenční tabulky. Excel Services nepodporuje rozsahy externích dat, což znamená, že Excel Services nepodporuje excelovou tabulku připojenou ke zdroji dat, webový dotaz, mapování XML nebo microsoft query.

Toto omezení ale můžete obejít tak, že se pomocí kontingenční tabulky připojíte ke zdroji dat a pak navrhnete a rozložíte kontingenční tabulku jako dvourozměrnou tabulku bez úrovní, skupin nebo mezisoučtů tak, aby se zobrazily všechny požadované hodnoty řádků a sloupců. 

Pojďme se podívat na pás paměti databáze.

Informace o MDAC, OLE DB a OBC

Nejdřív se omlouváme za všechny zkratky. Microsoft Data Access Components (MDAC) 2.8 je součástí systému Microsoft Windows . Pomocí MDAC se můžete připojit k datům z široké škály relačních a nerelačních zdrojů dat a používat je. K mnoha různým zdrojům dat se můžete připojit pomocí ovladačů ODBC (Open Database Connectivity) nebo zprostředkovatelů OLE DB, které sestavuje a dodává Microsoft nebo vyvíjí různé třetí strany. Při instalaci sady Microsoft Office se do počítače přidají další ovladače ODBC a zprostředkovatelé OLE DB.

Pokud chcete zobrazit úplný seznam zprostředkovatelů OLE DB nainstalovaných v počítači, zobrazte dialogové okno Vlastnosti datového propojení ze souboru datového propojení a potom klikněte na kartu Zprostředkovatel .

Pokud chcete zobrazit úplný seznam zprostředkovatelů ODBC nainstalovaných v počítači, zobrazte dialogové okno Správce databáze ODBC a potom klikněte na kartu Ovladače .

Můžete také použít ovladače ODBC a zprostředkovatele OLE DB od jiných výrobců k získání informací z jiných zdrojů dat, než jsou zdroje dat Microsoftu, včetně jiných typů databází ODBC a OLE DB. Informace o instalaci těchto ovladačů ODBC nebo zprostředkovatelů OLE DB najdete v dokumentaci k databázi nebo se obraťte na dodavatele databáze.

Připojení ke zdrojům dat pomocí rozhraní ODBC

V architektuře ODBC se aplikace (například Excel) připojuje ke Správci ovladačů ODBC, který zase používá konkrétní ovladač ODBC (například ovladač Microsoft SQL ODBC) pro připojení ke zdroji dat (například k databázi Microsoft SQL Server).

Pokud se chcete připojit ke zdrojům dat ODBC, postupujte takto:

1. Ujistěte se, že je v počítači, který obsahuje zdroj dat, nainstalovaný příslušný ovladač ODBC.

2. Definujte název zdroje dat (DSN) pomocí správce zdrojů dat ODBC pro uložení informací o připojení do registru nebo souboru DSN, nebo připojovací řetězec v kódu jazyka Microsoft Visual Basic pro předání informací o připojení přímo do Správce ovladačů ODBC.

   Pokud chcete definovat zdroj dat, klikněte ve Windows na tlačítko Start a potom klikněte na Ovládací panely. Klikněte na Systém a údržba a potom klikněte na Nástroje pro správu. Klikněte na Výkon a údržba a nástroje pro správu. a potom klikněte na Zdroje dat (ODBC). Další informace o různých možnostech získáte kliknutím na tlačítko Nápověda v každém dialogovém okně.

Zdroje dat počítače

Zdroje dat počítače ukládají informace o připojení v registru na konkrétním počítači s uživatelem definovaným názvem. Tento typ zdrojů dat můžete použít jenom na počítači, kde jsou definovány. Existují dva typy zdrojů dat počítače – uživatelské a systémové. Uživatelské zdroje dat může používat jenom aktuální uživatel a jsou viditelné jenom pro něj. Systémové zdroje dat můžou používat všichni uživatelé na počítači a jsou viditelné všem uživatelům v počítači.

Zdroj dat počítače je zvlášť užitečný, když chcete zajistit zvýšené zabezpečení, protože pomáhá zajistit, že zdroj dat počítače můžou zobrazit jenom přihlášení uživatelé a že zdroj dat počítače nemůže vzdálený uživatel zkopírovat do jiného počítače.

Souborové zdroje dat

Souborové zdroje dat (označované také jako soubory DSN) ukládají informace o připojení do textového souboru, nikoli do registru, a jsou obecně flexibilnější než zdroje dat počítače. Můžete například zkopírovat souborový zdroj dat do libovolného počítače se správným ovladačem ODBC, aby se vaše aplikace mohly spolehnout na konzistentní a přesné informace o připojení ke všem počítačům, které používá. Nebo můžete souborový zdroj dat umístit na server a sdílet pro velký počet počítačů v síti, takže budete informace o připojení jednoduše spravovat na jednom místě.

Souborový zdroj dat se dá nastavit také tak, že nepůjde sdílet. Zdroj dat souboru, který nelze sdílet, se nachází v jednom počítači a odkazuje na zdroj dat počítače. Tento typ zdroje dat můžete použít k přístupu k existujícím zdrojům dat počítače ze souborových zdrojů dat.

Připojení ke zdrojům dat pomocí OLE DB

V architektuře OLE DB se aplikace, která přistupuje k datům, nazývá příjemce dat (například Excel) a program, který umožňuje nativní přístup k datům, se nazývá zprostředkovatel databáze (například zprostředkovatel Microsoft OLE DB pro SQL Server).

Soubor Universal Data Link (.udl) obsahuje informace o připojení, které příjemce dat používá pro přístup ke zdroji dat prostřednictvím zprostředkovatele OLE DB tohoto zdroje dat. Informace o připojení můžete vytvořit jedním z následujících způsobů:

• V Průvodci datovým připojením použijte dialogové okno Vlastnosti datového propojení k definování datového propojení pro zprostředkovatele OLE DB. 

• Vytvořte prázdný textový soubor s příponou .udl a upravte soubor, který zobrazí dialogové okno Vlastnosti datového propojení .