Så här gör dataresor genom Excel

Här är de viktigaste sakerna du kan göra när data finns i Excel-tågstationen:

• Importera    Du kan importera data från många olika externa datakällor. Dessa datakällor kan finnas på din dator, i molnet eller halvvägs runt om i världen. Mer information finns i Importera data från externa datakällor.

• Power Query    Du kan använda Power Query (kallades tidigare Hämta & omvandla) för att skapa frågor för att forma, omvandla och kombinera data på en mängd olika sätt. Du kan exportera arbetet som en Power Query-mall för att definiera en dataflödesåtgärd i Power-appar. Du kan även skapa en datatyp som komplement till länkade datatyper. Mer information finns i Hjälp om Power Query för Excel.

• Säkerhet    Datasekretess, autentiseringsuppgifter och autentisering är alltid ett kontinuerligt problem. Mer information finns i Hantera inställningar och behörigheter för datakälla och Ange sekretessnivåer.

• Uppdatera    Importerade data kräver vanligtvis en uppdateringsåtgärd för att göra ändringar, till exempel tillägg, uppdateringar och borttagningar, i Excel. Mer information finns i Uppdatera en extern dataanslutning i Excel.

• Anslutningar/Egenskaper    Varje extern datakälla har blandade anslutnings- och egenskapsinformation som ibland kräver ändringar beroende på omständigheterna. Mer information finns i Hantera externa dataområden ochderas egenskaper , Skapa, redigeraoch hantera anslutningar till externa data och Anslutningsegenskaper.

• Äldre    Traditionella metoder, till exempel guider från äldre import och MSQuery, är fortfarande tillgängliga för användning. Mer information finns i Dataimport- och analysalternativ ochAnvända Microsoft Query för att hämta externa data.

Följande avsnitt innehåller mer information om vad som händer bakom kulisserna på den här upptagna Excel-tågstationen.

Det finns anslutnings-, fråge- och externa dataområdesegenskaper. Anslutnings- och frågeegenskaper innehåller båda traditionell anslutningsinformation. I en dialogrutas namn betyder Anslutningsegenskaper att det inte finns någon fråga kopplad till den, men Frågeegenskaper betyder att det finns det. Egenskaperna för det externa dataområdet styr layout och format för data. Alla datakällor har dialogrutan Externa dataegenskaper, men datakällor som har associerade autentiseringsuppgifter och uppdatera information använder den större dialogrutan Dataegenskaper för externt område.

Följande information sammanfattar de viktigaste dialogrutorna, fönstren, kommandosökvägarna och motsvarande hjälpavsnitt.

Dialogruta eller fönster Kommandosökvägar	Tabbar och tunnlar	Huvudhjälpavsnitt
Senast använda källor Data > Senast använda källor	(Inga flikar) Tunnel till dialogrutan> Navigatör	Hantera inställningar och behörigheter för datakälla
Anslutningsegenskaper ELLER Guiden Dataanslutning Data > Frågor & anslutningar > fliken Anslutningar > (högerklickar på en anslutning) > Egenskaper	Fliken Användningsdefinition på fliken Används i	Anslutningsegenskaper
Frågeegenskaper Data> På > (högerklicka på en anslutning) > Redigera anslutningsegenskaper OR Data> Queries & Connections | Fliken Frågor > (högerklicka på en anslutning) > Egenskaper Eller >Egenskaper ELLER Data> Uppdatera alla>(när de är placerade på ett inläst frågekalkylblad)	Fliken Användningsdefinition på fliken Används i	Anslutningsegenskaper
Frågor & Anslutningar Data > Frågor & Anslutningar	Fliken Frågor, fliken Anslutningar	Anslutningsegenskaper
Befintliga anslutningar Data > befintliga anslutningar	Fliken Anslutningar, fliken Tabeller	Ansluta till externa data
Externa dataegenskaper ELLER Egenskaper för externa dataområde ELLER data> (inaktiverat om det inte är placerad på ett frågekalkylblad)	Används på fliken (i dialogrutan Anslutningsegenskaper)   Knappen Uppdatera till höger tunnel till Frågeegenskaper	Hantera externa dataområden och deras egenskaper
Anslutningsegenskaper > Definition-> Exportera anslutningsfil OR Query> Export Connection File	(Inga flikar) Dialogrutan Tunnlar till fil, mapp med datakällor	Skapa, redigera och hantera anslutningar till externa data

Data i en Excel-arbetsbok kan komma från två olika platser. Data kan lagras direkt i arbetsboken eller lagras i en extern datakälla, till exempel en textfil, en databas eller en OLAP-kub. Den här externa datakällan är ansluten till arbetsboken genom en dataanslutning, som är en uppsättning information som beskriver hur du hittar, loggar in på och får åtkomst till den externa datakällan.

Den största fördelen med att ansluta till externa data är att du kan analysera dessa data med jämna mellanrum utan att kopiera dem till arbetsboken flera gånger, vilket är en åtgärd som kan ta lång tid och lätt bli fel. När du har anslutit till externa data kan du även automatiskt uppdatera (eller uppdatera) dina Excel-arbetsböcker från den ursprungliga datakällan när datakällan uppdateras med ny information.

Anslutningsinformationen lagras i arbetsboken och kan också lagras i en anslutningsfil, till exempel en ODC-fil (Office Data Connection) (.odc) eller en namnfil för datakälla (.dsn).

För att hämta externa data till Excel behöver du åtkomst till dessa data. Om den externa datakälla som du vill komma åt inte finns på den lokala datorn kan du behöva kontakta administratören för databasen för att få lösenord, användarbehörigheter eller annan anslutningsinformation. Om datakällan är en databas kontrollerar du att databasen inte öppnas i exklusivt läge. Om datakällan är en textfil eller ett kalkylblad kontrollerar du att en annan användare inte har den öppen för exklusiv åtkomst.

Många datakällor kräver också en ODBC-drivrutin eller OLE DB-provider för att samordna dataflödet mellan Excel, anslutningsfilen och datakällan.

I följande diagram sammanfattas de viktigaste punkterna om dataanslutningar.

1. Det finns flera olika datakällor som du kan ansluta till: Analysis Services, SQL Server, Microsoft Access, andra OLAP- och relationsdatabaser, kalkylblad och textfiler.

2. Många datakällor har associerade ODBC-drivrutiner eller OLE DB-provider.

3. En anslutningsfil definierar all information som behövs för att komma åt och hämta data från en datakälla.

4. Anslutningsinformation kopieras från en anslutningsfil till en arbetsbok och anslutningsinformationen kan enkelt redigeras.

5. Data kopieras till en arbetsbok så att du kan använda dem på samma sätt som data som lagras direkt i arbetsboken.

Om du vill hitta anslutningsfiler använder du dialogrutan Befintliga anslutningar. (Välj data > befintliga anslutningar.) Med den här dialogrutan kan du se följande typer av anslutningar:

• Anslutningar i arbetsboken    

  I den här listan visas alla aktuella anslutningar i arbetsboken. Listan skapas utifrån anslutningar som du redan har definierat, som du har skapat med hjälp av dialogrutan Välj datakälla i guiden Dataanslutning, eller från anslutningar som du tidigare valde som en anslutning från den här dialogrutan.

• Anslutningsfiler på datorn    

  Den här listan skapas från mappen Mina datakällor som normalt lagras i mappen Dokument.

• Anslutningsfiler i nätverket    

  Den här listan kan skapas från en uppsättning mappar i ditt lokala nätverk, där platsen kan distribueras i nätverket som en del av distributionen av Microsoft Office-gruppprinciper eller ett SharePoint-bibliotek. 

Du kan också använda Excel som anslutningsfilredigerare när du vill skapa och redigera anslutningar till externa datakällor som lagras i en arbetsbok eller i en anslutningsfil. Om du inte hittar den anslutning du vill använda kan du skapa en anslutning genom att klicka på Bläddra efter fler så att dialogrutan Välj datakälla visas och sedan klicka på Ny källa för att starta guiden Dataanslutning.

När du har skapat anslutningen kan du använda dialogrutan Anslutningsegenskaper (Välj data > Frågor & Anslutningar > fliken Anslutningar > (högerklicka på en anslutning) > Egenskaper) för att styra olika inställningar för anslutningar till externa datakällor och använda, återanvända eller växla anslutningsfiler.

Obs!    Ibland heter dialogrutan Anslutningsegenskaper dialogrutan Frågeegenskaper när det finns en fråga som skapats i Power Query (kallades tidigare Hämta & omvandla) kopplad till den.

Om du använder en anslutningsfil för att ansluta till en datakälla kopieras anslutningsinformationen från anslutningsfilen till Excel-arbetsboken. När du gör ändringar i dialogrutan Anslutningsegenskaper redigerar du den dataanslutningsinformation som lagras i den aktuella Excel-arbetsboken och inte den ursprungliga dataanslutningsfilen som kan ha använts för att skapa anslutningen (anges med filnamnet som visas i egenskapen Anslutningsfil på fliken Definition). När du har redigerat anslutningsinformationen (förutom egenskaperna Anslutningsnamn och Anslutningsbeskrivning) tas länken till anslutningsfilen bort och egenskapen Anslutningsfil tas bort.

Om du vill säkerställa att anslutningsfilen alltid används när en datakälla uppdateras klickar du på Försök alltid använda den här filen för att uppdatera data på fliken Definition. Om du markerar den här kryssrutan ser du till att uppdateringar av anslutningsfilen alltid används av alla arbetsböcker som använder anslutningsfilen, som också måste ha den här egenskapen a inställd.

Med hjälp av dialogrutan Anslutningar kan du enkelt hantera de här anslutningarna, till exempel skapa, redigera och ta bort dem (Välj data > Frågor & Anslutningar > fliken Anslutningar > (högerklicka på en anslutning) > Egenskaper.) Du kan använda den här dialogrutan för att göra följande: 

• Skapa, redigera, uppdatera och ta bort anslutningar som används i arbetsboken.

• Verifiera källan för externa data. Det kan vara bra att göra det om anslutningen har definierats av en annan användare.

• Visa var varje anslutning används i den aktuella arbetsboken.

• Diagnostisera ett felmeddelande om anslutningar till externa data.

• Omdirigera en anslutning till en annan server eller datakälla, eller ersätt anslutningsfilen för en befintlig anslutning.

• Gör det enkelt att skapa och dela anslutningsfiler med användare.

Anslutningsfiler är särskilt användbara för att dela anslutningar regelbundet, göra anslutningar mer anpassningsbara, hjälpa till att förbättra säkerheten för anslutningar och underlätta administration av datakälla. Det bästa sättet att dela anslutningsfiler är att placera dem på en säker och betrodd plats, till exempel en nätverksmapp eller ett SharePoint-bibliotek, där användarna kan läsa filen men endast angivna användare kan ändra filen. Mer information finns i Dela data med ODC.

Använda ODC-filer

Du kan skapa ODC-filer (Office Data Connection– .odc) genom att ansluta till externa data via dialogrutan Välj datakälla eller med hjälp av guiden Dataanslutning för att ansluta till nya datakällor. En ODC-fil använder anpassade HTML- och XML-taggar för att lagra anslutningsinformationen. Du kan enkelt visa eller redigera innehållet i filen i Excel.

Du kan dela anslutningsfiler med andra för att ge dem samma åtkomst som du har till en extern datakälla. Andra användare behöver inte konfigurera en datakälla för att öppna anslutningsfilen, men de kan behöva installera den ODBC-drivrutin eller OLE DB-provider som krävs för att komma åt externa data på sin dator.

ODC-filer är den rekommenderade metoden för att ansluta till data och dela data. Du kan enkelt konvertera andra traditionella anslutningsfiler (DSN-, UDL- och frågefiler) till en ODC-fil genom att öppna anslutningsfilen och sedan klicka på knappen Exportera anslutningsfil på fliken Definition i dialogrutan Anslutningsegenskaper.

Använda frågefiler

Frågefiler är textfiler som innehåller information om datakällan, inklusive namnet på den server där data finns och den anslutningsinformation som du anger när du skapar en datakälla. Frågefiler är ett traditionellt sätt att dela frågor med andra Excel-användare.

Använda DQY-frågefiler    Du kan använda Microsoft Query för att spara DQY-filer som innehåller frågor om data från relationsdatabaser eller textfiler. När du öppnar filerna i Microsoft Query kan du visa data som returneras av frågan och ändra frågan för att hämta olika resultat. Du kan spara en DQY-fil för alla frågor som du skapar, antingen med Frågeguiden eller direkt i Microsoft Query.

Använda OQY-frågefiler    Du kan spara OQY-filer för att ansluta till data i en OLAP-databas, antingen på en server eller i en offlinekubfil (.cub). När du använder guiden Flerdimensionell anslutning i Microsoft Query för att skapa en datakälla för en OLAP-databas eller kub skapas en .oqy-fil automatiskt. Eftersom OLAP-databaser inte är ordnade i poster eller tabeller kan du inte skapa frågor eller DQY-filer för att komma åt dessa databaser.

Använda .rqy-frågefiler    Excel kan öppna frågefiler i .rqy-format för att stödja drivrutiner för OLE DB-datakällor som använder det här formatet. Mer information finns i dokumentationen för din drivrutin.

Använda QRY-frågefiler    Microsoft Query kan öppna och spara frågefiler i .qry-format för användning med tidigare versioner av Microsoft Query som inte kan öppna DQY-filer. Om du har en frågefil i .qry-format som du vill använda i Excel öppnar du filen i Microsoft Query och sparar den sedan som en DQY-fil. Information om hur du sparar .dqy-filer finns i Hjälpen för Microsoft Query.

Använda .iqy-webbfrågefiler    Excel kan öppna .iqy-webbfrågefiler för att hämta data från webben. Mer information finns i Exportera till Excel från SharePoint.

Ett externt dataområde (kallas även frågetabell) är ett definierat namn eller tabellnamn som definierar platsen för de data som förs till ett kalkylblad. När du ansluter till externa data skapas ett externt dataområde automatiskt i Excel. Det enda undantaget är en pivottabellrapport som är ansluten till en datakälla, som inte skapar ett externt dataområde. I Excel kan du formatera och skapa ett externt dataområde eller använda det i beräkningar, som med andra data.

Ett externt dataområde får automatiskt ett namn enligt följande:

• Externa dataområden från ODC-filer (Office Data Connection) får samma namn som filnamnet.

• Externa dataområden från databaser namnges med namnet på frågan. Som Query_from_ härkällan är namnet på den datakälla som du använde för att skapa frågan.

• Externa dataområden från textfiler namnges med textens filnamn.

• Externa dataområden från webbfrågor namnges med namnet på den webbsida som data hämtades från.

Om kalkylbladet innehåller fler än ett externt dataområde från samma källa numreras områdena. Till exempel MinText, MyText_1, MyText_2 och så vidare.

Ett externt dataområde har ytterligare egenskaper (inte att förväxla med anslutningsegenskaper) som du kan använda för att styra data, till exempel bevarande av cellformatering och kolumnbredd. Du kan ändra de här externa dataområdesegenskaperna genom att klicka på Egenskaper i gruppen Anslutningar på fliken Data och sedan göra ändringarna i dialogrutorna Egenskaper för externt dataområde eller Externa dataegenskaper.

Det finns flera dataobjekt (till exempel ett externt dataområde och en pivottabellrapport) som du kan använda för att ansluta till olika datakällor. Vilken typ av datakälla du kan ansluta till skiljer sig dock åt mellan de olika dataobjekten.

Du kan använda och uppdatera anslutna data i Excel Services. Precis som för alla externa datakällor kan du behöva autentisera din åtkomst. Mer information finns i Uppdatera en extern dataanslutning i Excel. Feller mer information om autentiseringsuppgifter, se Autentiseringsinställningar för Excel Services.

I följande tabell sammanfattas vilka datakällor som stöds för varje dataobjekt i Excel.

Excel data objekt	Skapar Extern data område?		OLE DB	ODBC	Text fil	HTML fil	XML fil	SharePoint lista
Guiden Importera text	Ja		Nej	Nej	Ja	Nej	Nej	Nej
Pivottabellrapport (ej OLAP)	Nej		Ja	Ja	Ja	Nej	Nej	Ja
Pivottabellrapport (OLAP)	Nej		Ja	Nej	Nej	Nej	Nej	Nej
Excel-tabell	Ja		Ja	Ja	Nej	Nej	Ja	Ja
XML-mappning	Ja		Nej	Nej	Nej	Nej	Ja	Nej
Webbfråga	Ja		Nej	Nej	Nej	Ja	Ja	Nej
Guiden Dataanslutning	Ja		Ja	Ja	Ja	Ja	Ja	Ja
Microsoft Query	Ja		Nej	Ja	Ja	Nej	Nej	Nej

Lösning för Excel Services för Excel-tabeller och namngivna områden

Om du vill visa en Excel-arbetsbok i Excel Services kan du ansluta till och uppdatera data, men du måste använda en pivottabellrapport. Excel Services stöder inte externa dataområden, vilket innebär att Excel Services inte stöder en Excel-tabell som är ansluten till en datakälla, en webbfråga, en XML-mappning eller Microsoft Query.

Du kan dock komma runt den här begränsningen genom att använda en pivottabell för att ansluta till datakällan och sedan utforma och layouta pivottabellen som en tvådimensionell tabell utan nivåer, grupper eller delsummor så att alla önskade rad- och kolumnvärden visas. 

Nu kör vi ner databasminnet.

Om MDAC, OLE DB och OBC

Först och främst ber vi om ursäkt för alla förkortningar. Microsoft Data Access Components (MDAC) 2.8 ingår i Microsoft Windows. Med MDAC kan du ansluta till och använda data från en mängd olika relations- och ickerelationsdatakällor. Du kan ansluta till många olika datakällor med ODBC-drivrutiner (Open Database Connectivity) eller OLE-DB-leverantörer som antingen byggs och levereras av Microsoft eller utvecklas av olika tredje parter. När du installerar Microsoft Office läggs ytterligare ODBC-drivrutiner och OLE DB-providers till på datorn.

Om du vill se en fullständig lista över OLE DB-providers som är installerade på datorn visar du dialogrutan Datalänkegenskaper från en Datalänk-fil och klickar sedan på fliken Provider.

Om du vill se en fullständig lista över ODBC-providers som är installerade på datorn visar du dialogrutan ODBC-databasadministratör och klickar sedan på fliken Drivrutiner.

Du kan också använda ODBC-drivrutiner och OLE DB-providers från andra tillverkare för att få information från andra källor än Microsoft-datakällor, inklusive andra typer av ODBC- och OLE DB-databaser. Om du vill ha information om hur du installerar dessa ODBC-drivrutiner eller OLE DB-providers kontrollerar du dokumentationen för databasen, eller kontaktar din databasleverantör.

Använda ODBC för att ansluta till datakällor

I ODBC-arkitekturen ansluter ett program (till exempel Excel) till ODBC-drivrutinshanteraren, som i sin tur använder en specifik ODBC-drivrutin (till exempel Microsoft SQL ODBC-drivrutinen) för att ansluta till en datakälla (till exempel en Microsoft SQL Server-databas).

Så här ansluter du till ODBC-datakällor:

1. Kontrollera att rätt ODBC-drivrutin är installerad på den dator som innehåller datakällan.

2. Definiera ett namn på datakällan (DSN) genom att använda administratören för ODBC-datakällor för att lagra anslutningsinformationen i registret eller en DSN-fil, eller en anslutningssträng i Microsoft Visual Basic-koden för att överföra anslutningsinformationen direkt till ODBC-drivrutinshanteraren.

   Definiera en datakälla genom att i Windows klicka på Start-knappen och sedan på Kontrollpanelen. Klicka på System ochunderhåll och sedan på Administrationsverktyg. Klicka på Prestanda och underhålloch sedan på Administrationsverktyg. och klickar sedan på Datakällor (ODBC). Om du vill ha mer information om de olika alternativen klickar du på knappen Hjälp i varje dialogruta.

Maskindatakällor

I maskindatakällor lagras anslutningsinformation i registret, på en viss dator, med ett användardefinierat namn. Du kan bara använda maskindatakällor på den dator där de är definierade. Det finns två typer av maskindatakällor – användare och system. Användardatakällor kan endast användas av den aktuella användaren och är bara synliga för den användaren. Systemdatakällor kan användas av alla användare på en dator och är synliga för alla användare på datorn.

En maskindatakälla är särskilt användbar när du vill ge ytterligare säkerhet, eftersom den gör att endast användare som är inloggade kan visa en maskindatakälla och en maskindatakälla inte kan kopieras av en fjärranvändare till en annan dator.

Fildatakällor

I fildatakällor (även kallade DSN-filer) lagras anslutningsinformation i en textfil, inte registret, och de är oftast mer flexibla att använda än maskindatakällor. Du kan till exempel kopiera en fildatakälla till valfri dator med rätt ODBC-drivrutin, så att programmet kan förlita sig på konsekvent och korrekt anslutningsinformation till alla datorer som den använder. Du kan också placera fildatakällan på en enda server, dela den mellan många datorer i nätverket och enkelt underhålla anslutningsinformationen på en plats.

En fildatakälla kan också vara odelbar. En odelbar fildatakälla lagras på en enda dator och pekar på en maskindatakälla. Du kan använda odelbara fildatakällor för att få åtkomst till befintliga maskindatakällor från fildatakällor.

Använda OLE DB för att ansluta till datakällor

I OLE DB-arkitekturen kallas programmet som har åtkomst till data för datakonsumenter (till exempel Excel), och programmet som tillåter intern åtkomst till data kallas en databasleverantör (till exempel Microsoft OLE DB-provider för SQL Server).

En Universal Data Link-fil (.udl) innehåller den anslutningsinformation som en datakonsument använder för att komma åt en datakälla via OLE DB-providern för den datakällan. Du kan skapa anslutningsinformationen genom att göra något av följande:

• I guiden Dataanslutning använder du dialogrutan Datalänkegenskaper för att definiera en datalänk för en OLE DB-provider. 

• Skapa en tom textfil med filnamnstillägget .udl och redigera sedan filen så att dialogrutan Datalänkegenskaper visas.