Om data alltid är på resande fot är Excel som Grand Central Station. Tänk dig att data är ett tåg fyllt med passagerare som regelbundet går in i Excel, gör ändringar och sedan lämnar det. Det finns dussintals sätt att komma in i Excel, som importerar data av alla typer och listan fortsätter att växa. När data är i Excel är de redo att ändra form precis som du vill med Power Query. Data, precis som vi alla, kräver också "skötsel och utfodring" för att saker och ting ska fungera smidigt. Det är där anslutnings-, fråge- och dataegenskaper kommer in. Slutligen lämnar data Excel-tågstationen på många sätt: de importeras av andra datakällor, delas som rapporter, diagram och pivottabeller och exporteras till Power BI och Power Apps.
Vanliga saker du kan göra med data i Excel tågstation
Här är de viktigaste sakerna du kan göra medan data finns på tågstationen i Excel:
- Importera Du kan importera data från många olika externa datakällor. De här datakällorna kan finnas på din dator, i molnet eller på andra sidan jordklotet. Mer information finns i Importera data från externa datakällor.
- Power Query Du kan använda Power Query (kallades tidigare Hämta & transformera) till att skapa frågor för att forma, omvandla och kombinera data på en mängd olika sätt. Du kan exportera ditt arbete som en Power Query mall för att definiera en dataflödesåtgärd i Power Apps. Du kan även skapa en datatyp som komplement till länkade datatyper. Mer information finns i Power Query för Excel-hjälp..
- Säkerhet Datasekretess, autentiseringsuppgifter och autentisering är alltid ett ständigt problem. Mer information finns i Hantera inställningar och behörigheter för datakällor och Ange sekretessnivåer.
- Uppdatera Importerade data kräver vanligtvis en uppdateringsåtgärd för att få in ändringar, till exempel tillägg, uppdateringar och borttagningar, i Excel. Mer information finns i Uppdatera en extern dataanslutning i Excel.
- Anslutningar/egenskaper Alla externa datakällor har en viss anslutnings- och egenskapsinformation som ibland måste ändras beroende på omständigheterna. Mer information finns i Hantera externa dataområden och deras egenskaper, Skapa, redigera och hantera anslutningar till externa data och Anslutningsegenskaper.
- Äldre Traditionella metoder, till exempel äldre importguider och MSQuery, är fortfarande tillgängliga för användning. Mer information finns i Alternativ för import och analys av data och Använda Microsoft Query för att hämta externa data.
I följande avsnitt får du mer information om vad som händer bakom kulisserna på den här livliga Excel-tågstationen.
Sammanfattning av anslutningar och egenskaper
Det finns egenskaper för anslutning, fråga och externa dataområden. Både anslutnings- och frågeegenskaperna innehåller traditionell anslutningsinformation. I en dialogrutas rubrik innebär Anslutningsegenskaper att det inte finns någon associerad fråga, men Frågeegenskaper betyder att det finns. Externa dataområdesegenskaper styr layout och format för data. Alla datakällor har dialogrutan Egenskaper för externa data , men datakällor med associerad autentiserings- och uppdateringsinformation använder den större dialogrutan Dataegenskaper för externt område .
Följande information sammanfattar de viktigaste dialogrutorna, kommandosökvägarna och motsvarande hjälpavsnitt.
| Dialogruta eller fönster Kommandosökvägar |
Flikar och tunnlar | Huvudhjälpavsnitt |
|---|---|---|
|
Senaste källor Uppgifter>Senaste källor |
(Inga flikar) Dialogrutan Tunnlar att ansluta>navigatören |
Hantera inställningar och behörigheter för datakällor |
|
Anslutningsegenskaper ELLER Guiden Dataanslutning Uppgifter>Frågor & anslutningar>Fliken> Anslutningar (högerklicka på en anslutning) >Egenskaper |
Fliken Användning Fliken Definition Fliken Används i |
Anslutningsegenskaper |
|
Frågeegenskaper Uppgifter>Befintliga anslutningar> (högerklicka på en anslutning) >Redigera anslutningsegenskaper ELLER Uppgifter>Frågor & anslutningar| Fliken> Frågor (högerklicka på en anslutning) >Egenskaper ELLER Fråga>Egenskaper ELLER Uppgifter>Uppdatera alla>Anslutningar (när de är placerade på ett kalkylblad för inlästa frågor) |
Fliken Användning Fliken Definition Fliken Används i |
Anslutningsegenskaper |
|
Frågor & anslutningar Uppgifter>Frågor & anslutningar |
Fliken Frågor Fliken Anslutningar |
Anslutningsegenskaper |
|
Befintliga anslutningar Uppgifter>Befintliga anslutningar |
Fliken Anslutningar Fliken Tableller |
Ansluta till externa data |
|
Externa dataegenskaper ELLER Egenskaper för externt dataområde ELLER Uppgifter>Egenskaper (inaktiveras om de inte placeras i ett frågekalkylblad) |
Används på fliken (från dialogrutan Anslutningsegenskaper ) Uppdateringsknappen till höger tunnlar till Frågeegenskaper |
Hantera externa dataområden och deras egenskaper |
|
Anslutningsegenskaper>Fliken> Definition Exportera anslutningsfil ELLER Fråga>Exportera anslutningsfil |
(Inga flikar) Tunnlar till Dialogrutan Arkiv Mappen Datakällor |
Skapa, redigera och hantera anslutningar till externa data |
Grunderna i dataanslutningar
Data i en Excel-arbetsbok kan komma från två olika platser. Data kan lagras direkt i arbetsboken eller 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 samling information som anger hur du hittar, loggar in i och får åtkomst till den externa datakällan.
Den främsta fördelen med att ansluta till externa data är att du regelbundet kan analysera informationen utan att behöva kopiera den till arbetsboken flera gånger, vilket ofta kan vara tidskrävande och resultera i fel. När du har anslutit till externa data kan du också automatiskt uppdatera Excel-arbetsböckerna från den ursprungliga datakällan så fort den uppdateras med ny information.
Information om anslutningen lagras i arbetsboken och kan även lagras i en anslutningsfil, till exempel en ODC-fil (Office Data Connection – .odc) eller en fil med namn på datakälla (.dsn).
Om du vill föra in externa data i Excel måste du ha åtkomst till dessa data. Om den externa datakällan 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å ett 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 måste du se till att en annan användare inte har den öppen för exklusiv åtkomst.
För många datakällor krävs också en ODBC-drivrutin eller OLE DB-provider för att koordinera dataflödet mellan Excel, anslutningsfilen och datakällan.
I följande diagram sammanfattas de viktigaste punkterna om dataanslutningar.
1. veckor Det finns en mängd olika datakällor som du kan ansluta till: Analysis Services, SQL Server, Microsoft Access, andra OLAP- och relationsdatabaser, kalkylblad och textfiler.
2. veckor Många datakällor har en tillhörande ODBC-drivrutin eller OLE DB-provider.
3. veckor En anslutningsfil definierar all information som behövs för att komma åt och hämta data från en datakälla.
4. veckor Anslutningsinformationen kopieras från en anslutningsfil till en arbetsbok och anslutningsinformationen kan enkelt redigeras.
5. veckor Informationen kopieras till en arbetsbok så att du kan använda den precis som du använder data som lagras direkt i arbetsboken.
Hitta anslutningar
Hitta anslutningsfiler med hjälp av dialogrutan Befintliga anslutningar . (Markera data>Befintliga anslutningar.) I den här dialogrutan kan du se följande typer av anslutningar:
-
Anslutningar i arbetsboken
Den här listan visar alla aktuella anslutningar i arbetsboken. Listan skapas frå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 valt som en anslutning i den här dialogrutan. -
Anslutningsfiler på datorn
Den här listan skapas från mappen Mina datakällor , som vanligtvis finns i mappen Dokument . -
Anslutningsfiler i nätverket
Listan kan skapas från en uppsättning mappar i det lokala nätverket, vars plats kan distribueras över nätverket som en del av distributionen av Microsoft Office-grupprinciper eller ett SharePoint-bibliotek.
Redigera anslutningsegenskaper
Du kan också använda Excel som anslutningsfilredigerare för att 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 för att visa dialogrutan Välj datakälla . Klicka sedan på Ny källa för att starta guiden Dataanslutning.
När du har skapat anslutningen kan du använda dialogrutan Anslutningsegenskaper (Välj datafrågor>& fliken Anslutningsanslutningar>> (högerklicka på en anslutning) >Egenskaper) för att styra olika inställningar för anslutningar till externa datakällor och för att använda, återanvända eller byta anslutningsfiler.
Anteckning Ibland kallas dialogrutan Anslutningsegenskaper för Frågeegenskaper när det finns en fråga som skapats i Power Query (kallades tidigare Hämta & transformera) som är 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 med hjälp av dialogrutan Anslutningsegenskaper redigerar du den dataanslutningsinformation som lagras i den aktuella Excel-arbetsboken och inte den ursprungliga dataanslutningsfilen som eventuellt har använts för att skapa anslutningen (anges av filnamnet som visas i egenskapen Anslutningsfil på fliken Definition ). När du har redigerat anslutningsinformationen (med undantag för egenskaperna Anslutningsnamn och Anslutningsbeskrivning ) tas länken till anslutningsfilen bort och egenskapen Anslutningsfil rensas.
För att 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 dessa 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.
Hantera anslutningar
Med hjälp av dialogrutan Anslutningar kan du enkelt hantera dessa anslutningar, inklusive att skapa, redigera och ta bort dem (Välj datafrågor>& fliken Anslutningar>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.
- Kontrollera källan för externa data. Det kan vara praktiskt att göra det här 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 filer med användare.
Dela ODC och frågeanslutningar i filer
Anslutningsfiler är särskilt användbara för att dela anslutningar regelbundet, göra anslutningar enklare att hitta, hjälper till att förbättra säkerheten för anslutningar och underlättar administration av datakälla. Det bästa sättet att dela anslutningsfiler är att förvara dem på en säker och tillförlitlig plats, till exempel en nätverksmapp eller ett SharePoint-bibliotek, där användare kan läsa filen men endast behöriga 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 genom att använda guiden Dataanslutning för att ansluta till nya datakällor. En ODC-fil använder anpassade HTML- och XML-taggar för att lagra information om anslutningen. 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å datorn.
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 anslutningsinformationen 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 de här filerna i Microsoft Query kan du visa de data som returneras av frågan och ändra frågan så att andra resultat returneras. Du kan spara en DQY-fil för alla frågor som du skapar, antingen med hjälp av 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 få åtkomst till dessa databaser.
Använda RQY-frågefiler Excel kan öppna frågefiler i .rqy-format för att stödja OLE DB-drivrutiner för datakällor som använder det här formatet. Mer information finns i dokumentationen till drivrutinen.
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 kan du öppna filen i Microsoft Query och spara den som en DQY-fil. Information om hur du sparar DQY-filer finns i Microsoft Query-hjälpen.
Använda .iqy-webbfrågefiler Excel kan öppna .iqy Web Query-filer för att hämta data från webben. Mer information finns i Exportera till Excel från SharePoint.
Använda externa dataegenskaper
Ett externt dataområde (kallas även frågetabell) är ett definierat namn eller tabellnamn som definierar platsen för informationen i kalkylbladet. 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, vilket inte skapar ett externt dataområde. I Excel kan du formatera och strukturera ett externt dataområde eller använda det i beräkningar, precis som med andra data.
I Excel namnges ett externt dataområde automatiskt på följande sätt:
- 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 standard är Query_from_källa namnet på den datakälla som du använde för att skapa frågan.
- Externa dataområden från textfiler namnges med textfilsnamnet.
- Externa dataområden från webbfrågor namnges med namnet på webbsidan som informationen hämtades från.
Om kalkylbladet innehåller mer ä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 kontrollera data, till exempel bevarande av cellformatering och kolumnbredd. Du kan ändra egenskaperna för det externa dataområdet 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 Egenskaper för extern data .
|
|
|---|
Stöd för datakällor i Excel Services
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. Men vilken typ av datakälla som du kan ansluta till skiljer sig åt mellan varje dataobjekt.
Du kan använda och uppdatera anslutna data i Excel Services. Precis som med 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 i Excel Services.
I följande tabell sammanfattas vilka datakällor som stöds för varje dataobjekt i Excel.
|
Excel (på engelska) data objekt |
Skapar Externt data räckvidd? |
OLE DB |
ODBC |
Text fil |
HTML-format fil |
XML fil |
SharePoint (SharePoint) lista |
|
|---|---|---|---|---|---|---|---|---|
| Guiden Textimport | Ja | Nej | Nej | Ja | Nej | Nej | Nej | |
| Pivottabellrapport (icke-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 |
Obs
De här filerna, dvs. en textfil som importerats med hjälp av guiden Textimport, en XML-fil som importerats med hjälp av en XML-mappning och en HTML- eller XML-fil som importerats med hjälp av en webbfråga, använder inte en ODBC-drivrutin eller OLE DB-provider för att upprätta anslutningen till datakällan.
Excel Services lösning 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 Excel-tabeller som är anslutna till en datakälla, en webbfråga, en XML-mappning eller Microsoft Query.
Du kan emellertid kringgå den här begränsningen genom att använda en pivottabell för att ansluta till datakällan och sedan utforma och utforma pivottabellen som en tvådimensionell tabell utan nivåer, grupper eller delsummor så att alla önskade rad- och kolumnvärden visas.
ODBC- och OLE DB-dataåtkomstkomponenter
Låt oss ta en titt på databasminnenas allé.
Om MDAC, OLE DB och OBC
Först och främst, ber om ursäkt för alla akronymer. 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 relationella och icke-relationella datakällor. Du kan ansluta till många olika datakällor med hjälp av ODBC-drivrutiner (Open Database Connectivity) eller OLE DB-providers, 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 i datorn.
Om du vill se en fullständig lista över installerade OLE DB-providrar på datorn öppnar du dialogrutan Egenskaper för datalänk från en datalänkfil och klickar sedan på fliken Provider .
Om du vill se en fullständig lista över installerade ODBC-providrar på datorn öppnar du dialogrutan ODBC-databasadministratör och klickar 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. Information om att installera dessa ODBC-drivrutiner eller OLE DB-providers kontrollerar du dokumentationen för databasen, eller kontakta din databassäljaren.
Använda ODBC för att ansluta till datakällor
I ODBC-arkitekturen ansluter ett program (t.ex. 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:
- Kontrollera att rätt ODBC-drivrutin är installerad på datorn där datakällan finns.
- Ange 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 skicka anslutningsinformationen direkt till ODBC-drivrutinshanteraren.
Om du vill definiera en datakälla klickar du på Start-knappen i Windows och sedan på Kontrollpanelen. Klicka på System och underhåll och sedan på Administrationsverktyg. Klicka på Prestanda och underhåll och sedan på Administrationsverktyg. och 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ällorna lagras anslutningsinformation i registret på en specifik dator med ett användardefinierat namn. Du kan använda maskindatakällor endast 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 endast synliga för den användaren. Systemdatakällor kan användas av alla användare på en dator och visas för alla användare på datorn.
En maskindatakälla är särskilt användbar när du vill uppnå ökad säkerhet, eftersom den hjälper till att säkerställa att endast användare som är inloggade kan se maskindatakällor och att 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 i stället för i registret, och ä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 det använder. Alternativt kan du placera fildatakällan på en enda server, dela den mellan många datorer i nätverket och enkelt samla anslutningsinformationen på en plats.
En fildatakälla kan också vara odelbar. En odelbar fildatakälla finns 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 en datakonsument (till exempel Excel), och programmet som tillåter intern åtkomst till data kallas en databasprovider (till exempel Microsoft OLE DB Provider för SQL Server).
En UDL-fil (Universal Data Link) innehåller anslutningsinformation som en datakonsument använder för åtkomst till en datakälla via OLE DB-providern för datakällan. Du kan skapa information om anslutningen på något av följande sätt:
- 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, vilket visar dialogrutan Datalänkegenskaper .