Jak se data prochová v Excelu

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

• Import    Data můžete importovat z mnoha různých externích zdrojů dat. Tyto zdroje dat mohou 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    Power Query (dřív nazývaný & Transformovat) můžete použít k vytváření dotazů na tvarování, transformaci a kombinování dat různými způsoby. 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 nápovědě k Power Query pro Excel.

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

• Aktualizovat    Importovaná data obvykle vyžadují operaci aktualizace, aby se do Excelu vnesly změny, jako jsou přidání, aktualizace a odstranění. Další informace najdete v tématu Aktualizace externího datového připojení v Excelu.

• Připojení/vlastnosti    Ke každému externímu zdroji dat jsou přidruženy různé informace o připojení a vlastnostech, které někdy vyžadují změny v závislosti na vašich okolnostech. Další informace najdete v tématu Správa rozsahů externích data jejich vlastností , Vytváření, úpravya správa připojení k externím datům a Vlastnosti připojení.

• Starší verze    Tradiční metody, jako jsou starší průvodci importem a MSQuery, jsou pořád dostupné pro použití. Další informace najdete v tématu Možnosti importu a analýzy dat a Použití Microsoft Query k načtení externích dat.

V následujících částech najdete další podrobnosti o tom, co se děje na tomto rušném excelovém nádraží na pozadí.

Existují vlastnosti oblasti připojení, dotazu a oblasti externích dat. Vlastnosti připojení i dotazu obsahují informace o tradičním připojení. Vlastnosti připojení v názvu dialogového okna znamenají, že k ní není přidružený žádný dotaz, ale vlastnosti dotazu znamenají, že je. 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 příkazů a odpovídající témata nápovědy.

Dialogové okno nebo podokno Cesty příkazů	Tabulátory a tunelové propojení	Hlavní téma nápovědy
Poslední zdroje Data > zdroje	(Bez karet) Dialog Tunely pro> navigátoru	Správa nastavení a oprávnění zdroje dat
Vlastnosti připojení Průvodce datovým připojením OR Data > Dotazy & připojení > připojení > (klikněte pravým tlačítkem myši na připojení) > vlastnosti	Karta Použití : Karta Definice použitá na kartě	Vlastnosti připojení
Vlastnosti dotazu Data> existující připojení > (klikněte pravým tlačítkem myši na připojení) > Upravit vlastnosti připojení OR Data> Dotazy & připojenís | Karta Dotazy > (klikněte pravým tlačítkem myši na připojení) > vlastnosti VLASTNOSTI >DOTAZU OR Data> Aktualizovat všechna> připojení (při umístění na listu načteného dotazu)	Karta Použití : Karta Definice použitá na kartě	Vlastnosti připojení
Dotazy & připojení Dotazy > dat & připojení	Karta Dotazy – karta Připojení	Vlastnosti připojení
Existující připojení Data > existující připojení	Karta Připojení– karta Tabulky	Připojení k externím datům
Vlastnosti externích dat OR – vlastnosti oblasti externích dat OR Data> vlastnosti (Zakázáno, pokud není umístěno na listu dotazu)	Používá se na kartě (v dialogovém okně Vlastnosti připojení)   Tlačítko Aktualizovat v pravém tunelovém propojení na vlastnosti dotazu	Správa rozsahů externích dat a jejich vlastností
Vlastnosti připojení > kartě Definice > Export souboru připojení Nebo dotaz> exportu souboru připojení	(Bez karet) Dialogové okno Tunely do souboru Složka zdroje dat	Vytváření, úpravy a správa připojení k externím datům

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

Hlavní výhodou připojení k externím datům je to, ž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 při každé aktualizaci zdroje dat novými informacemi.

Informace o připojení jsou uložené v sešitu a jde je také uložit do souboru připojení, jako je například soubor ODC (Office Data Connection) nebo soubor 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ého chcete získat přístup, není na místním počítači, budete možná muset kontaktovat správce databáze a získat 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řená ve výhradním režimu. Pokud je zdrojem dat textový soubor nebo tabulka, ujistěte se, že ho jiný uživatel nemá otevřený pro výhradní přístup.

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

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

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

2. Mnoho zdrojů dat má přidružený ovladač ODBC nebo poskytovatele 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í je možné snadno upravit.

5. Data se zkopírují do sešitu, abyste je mohli používat stejně jako data uložená přímo v sešitu.

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

• Připojení v sešitu    

  V tomto seznamu se zobrazí 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í z tohoto dialogového okna.

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

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

• Soubory připojení v síti    

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

Excel můžete také použít jako editor souborů připojení k vytváření a úpravám připojení k externím zdrojům dat, které jsou uložené v sešitu nebo v souboru připojení. Pokud nenajdete připojení, které chcete, můžete vytvořit připojení tak, že kliknete na Procházet další a zobrazíte 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 & Připojení > Karta Připojení > (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žití, opětovnému použití nebo přepnutí souborů připojení.

Poznámka    Někdy se v dialogovém okně Vlastnosti připojení zobrazí dialogové okno Vlastnosti dotazu, když je k tomuto dotazu přidružený dotaz vytvořený v Power Query (dřív se mu říkalo Získat & Transformovat).

Pokud k 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. Při změnách 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ý se mohl použít k vytvoření připojení (označený názvem souboru zobrazeným 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 odkaz na soubor připojení odebere a vlastnost Soubor připojení se odstraní.

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

Pomocí dialogového okna Připojení můžete tato připojení snadno spravovat, včetně vytváření, úprav a odstraňování těchto připojení (Vyberte data> Dotazy & Připojení > Připojení > (klikněte pravým tlačítkem myši na připojení) > Vlastnosti.) V tomto dialogovém okně můžete udělat toto: 

• Vytvořte, upravte, aktualizujte a odstraňte 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í bylo definované jiným uživatelem.

• Zobrazí, kde se každé připojení používá v aktuálním sešitu.

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

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

• Umožňuje snadno vytvářet a sdílet soubory připojení s uživateli.

Soubory připojení jsou zvlášť užitečné pro konzistentní sdílení připojení, zjišťování připojení, zlepšení zabezpečení připojení a usnadnění správy 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é mohou soubor číst, ale soubor mohou upravovat jenom určený uživatelé. Další informace najdete v tématu Sdílení dat pomocí odc.

Použití souborů ODC

Soubory ODC (Office Data Connection) můžete vytvořit tak, že se připojíte k externím datům pomocí 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 uložení 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 lidmi, aby měli stejný přístup jako k externímu zdroji dat. Jiní uživatelé nemusí nastavovat zdroj dat, aby soubor připojení otevřeli, ale možná budou muset nainstalovat ovladač ODBC nebo poskytovatele OLE DB, který je nutný pro přístup k externím datům na svém počítači.

Doporučené metody připojení k datům a sdílení dat jsou soubory ODC. Další tradiční soubory připojení (DSN, UDL a soubory dotazů) můžete snadno převést na soubor ODC tak, že otevřete soubor připojení 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 jsou data umístěna, a informací o připojení, které poskytnete při vytváření zdroje dat. Soubory dotazů jsou tradiční způsob sdílení dotazů s jinými uživateli Excelu.

Použití souborů dotazů .dqy    Microsoft Query můžete použít k uložení souborů .dqy, které obsahují dotazy na data z relačních databází nebo textových souborů. Když tyto soubory otevřete v Microsoft Query, můžete zobrazit data vrácená dotazem a upravit dotaz tak, aby načítá různé výsledky. Soubor .dqy můžete uložit pro libovolný dotaz, který vytvoříte, buď pomocí Průvodce dotazem, nebo přímo v 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 v Microsoft Query použijete Průvodce vícerozměrným připojením k vytvoření zdroje dat pro databázi nebo krychli OLAP, vytvoří se soubor .oqy automaticky. Vzhledem k tomu, že databáze OLAP nejsou uspořádané v záznamech nebo tabulkách, nemůžete pro přístup k těmto databázím vytvářet dotazy ani soubory .dqy.

Použití souborů dotazů .rqy    Excel může otevírat soubory dotazů ve formátu .rqy, aby podporoval ovladače zdrojů dat OLE DB, které tento formát používají. Další informace najdete v dokumentaci k 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í s dřívějšími verzemi aplikace Microsoft Query, které neschová soubory .dqy. Pokud máte soubor dotazu ve formátu .qry, který chcete použít v Excelu, otevřete ho v Microsoft Query a uložte ho jako soubor .dqy. Informace o ukládání souborů .dqy najdete v nápovědě k 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 článku Export do Excelu ze SharePointu.

Oblast externích dat (nazývaná taky tabulka dotazu) je definovaný název nebo název tabulky, který definuje umístění dat předávaný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á nevytváří oblast externích dat. V Excelu můžete oblast externích dat formátovat a rozložit nebo ji použít ve výpočtech, stejně jako u jiných dat.

Excel automaticky názvy oblasti externích dat takto:

• Rozsahy externích dat ze souborů datového připojení Office (ODC) mají stejný název jako název souboru.

• Oblasti externích dat z databází mají název dotazu. Ve Query_from_zdroj je název zdroje dat, který jste použili k vytvoření dotazu.

• Rozsahy 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 má list více než jednu oblast externích dat ze stejného zdroje, budou 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, jako je 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ě Připojení 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 k připojení k různým zdrojům dat. Typ zdroje dat, ke který se můžete připojit, se ale mezi jednotlivými datovými objekty liší.

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

Následující tabulka shrnuje, které zdroje dat jsou podporované pro každý datový objekt v Excelu.

Excel data objekt	Vytvoří Externí data rozsah?		OLE DB	ROZHRANÍ ODBC	Text soubor	HTML soubor	XML soubor	SharePoint seznam
Průvodce importem textu	Ano		Ne	Ne	Ano	Ne	Ne	Ne
Sestava kontingenční tabulky (jiné než OLAP)	Ne		Ano	Ano	Ano	Ne	Ne	Ano
Sestava kontingenční tabulky (OLAP)	Ne		Ano	Ne	Ne	Ne	Ne	Ne
Excelová tabulka	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

Řešení excelových služeb pro excelové tabulky a pojmenované oblasti

Pokud chcete zobrazit excelový sešit ve službě Excel Services, můžete se připojit k datům a aktualizovat je, ale musíte použít sestavu kontingenční tabulky. Služba Excel Services nepodporuje oblasti externích dat, což znamená, že služba Excel Services nepodporuje tabulku Excelu připojenou ke zdroji dat, webovému dotazu, mapování XML nebo Microsoft Query.

Toto omezení ale můžete obejít pomocí kontingenční tabulky pro připojení ke zdroji dat a potom kontingenční tabulku navrhnout a rozložením jako dvojrozměrnou tabulku bez úrovní, skupin nebo mezisoučtů tak, aby se zobrazí všechny požadované hodnoty řádků a sloupců. 

Pojďme se pustit do cesty v paměti databáze.

Součásti MDAC, OLE DB a OBC

Nejdřív se omlouváme za všechny zkratky. Součástí Microsoft Data Access Components (MDAC) 2.8 je Microsoft Windows . Pomocí součástí MDAC se můžete připojit k datům a používat je z různých relačních a nesourelačních zdrojů dat. 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é jsou buď vytvořené a odeslané společností Microsoft, nebo vyvinuté různými třetími stranami. Při instalaci Microsoft Office se do počítače přidávají další ovladače ODBC a zprostředkovatelé OLE DB.

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

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

K získání informací z jiných zdrojů než zdrojů dat Microsoftu, včetně jiných typů databází ODBC a OLE DB, můžete použít také ovladače ODBC a poskytovatele OLE DB od jiných výrobců. 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ý k připojení ke zdroji dat (například k databázi Microsoft SQL Server) používá konkrétní ovladač ODBC (například ovladač Microsoft SQL ODBC).

Pokud se chcete připojit ke zdrojům dat ODBC, proveďte toto:

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

2. Definujte název zdroje dat (DSN) buď pomocí správce zdroje dat ODBC k uložení informací o připojení v registru nebo souboru DSN, nebo připojovacího řetězce v kódu Jazyka Microsoft Visual Basic, který předá informace o připojení přímo Správci 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 apotom klikněte na Nástroje pro správu. Klikněte na Výkon a údržba, klikněte na 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. Zdroje dat počítače můžete používat jenom v počítači, na který jsou definované. Existují dva typy zdrojů dat počítače – uživatele a systém. Zdroje dat uživatelů může používat jenom aktuální uživatel a jsou viditelné jenom pro tohoto uživatele. Systémové zdroje dat mohou používat všichni uživatelé na počítači a jsou viditelné všem uživatelům na počítači.

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

Zdroje dat souborů

Zdroje dat souborů (nazývané taky soubory DSN) ukládají informace o připojení do textového souboru, ne do registru, a obecně jsou flexibilnější než zdroje dat počítače. Můžete třeba zkopírovat zdroj dat souboru do libovolného počítače se správným ovladačem ODBC, aby se vaše aplikace spoléhá na konzistentní a přesné informace o připojení ke všem počítačům, které používá. Nebo můžete zdroj dat souboru umístit na jeden server, sdílet ho mezi mnoha počítači v síti a snadno udržovat informace o připojení na jednom místě.

Zdroj dat souboru může být také nesdílený. Nesdílený zdroj dat souboru se nachází na jednom počítači a odkazuje na zdroj dat počítače. Zdroje dat nesdílených souborů můžete použít k přístupu ke stávajícím zdrojům dat počítače ze zdrojů dat souboru.

Připojení ke zdrojům dat pomocí technologie 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 provider for SQL Server).

Soubor UNIVERSAL Data Link (.udl) obsahuje informace o připojení, které spotřebitel 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 definujte datové propojení pro zprostředkovatele OLE DB pomocí dialogového okna Vlastnosti datového propojení. 

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