Hur man länkar grafer i excel. Excel arbetsbok

Har du använt VLOOKUP-funktionen för att flytta kolumndata från en tabell till en annan? Eftersom Excel nu har en inbyggd datamodell har VLOOKUP-funktionen fasats ut. Du kan skapa en relation mellan två tabeller baserat på matchande data i dem. Du kan sedan skapa Power View-ark eller pivottabeller och andra rapporter med fält från varje tabell, även om de kommer från olika källor. Om du till exempel har kundförsäljningsdata kanske du vill importera och länka för att analysera försäljningstrender efter år och månad.

Alla tabeller i arbetsboken listas i fältlistorna PivotTable och Power View.

När du importerar relaterade tabeller från relationsdatabas Excel-data kan ofta skapa dessa relationer i en datamodell som genereras i bakgrunden. I andra fall måste du skapa associationer manuellt.

Om laget Relationär inte tillgänglig, innehåller arbetsboken bara en tabell.

I fönstret Äktenskaprådgivning tryck på knappen Skapa.

I fönstret Skapa en anslutning klicka på pilen bredvid fältet Tabell och välj en tabell från rullgardinsmenyn. I en en-till-många-relation måste denna tabell vara en del med flera medlemmar. I exemplet med kund- och tidsinformation måste du först välja kundförsäljningstabellen eftersom det sannolikt kommer att bli många försäljningar varje dag.

För element Kolumn (utländsk) välj kolumnen som innehåller data relaterade till elementet. Till exempel, om det finns en datumkolumn i båda tabellerna måste du välja den kolumnen nu.

I fält Relaterad tabell välj en tabell som innehåller minst en kolumn med data som är relaterad till den tabell som valts i fältet Tabell.

I fält Relaterad kolumn ( primärnyckel) välj kolumnen som innehåller unika värden, som motsvarar värdena i kolumnen som valts i fältet Kolumn.

Klicka på knappen OK.

Läs mer om relationer mellan tabeller i Excel

Relationsanteckningar

Exempel. Associerar tidsintelligensdata med flygbolagsflygdata

Du kan lära dig om relationerna mellan båda tabellerna och tidsintelligensen med gratis data på Microsoft Azure Marketplace. Vissa av dessa datauppsättningar är mycket stora och kräver snabb anslutning till Internet.

Du kan nu dela upp ankomstförseningar efter år och månad, samt andra värden i kalendern.

Råd: Som standard listas månader i alfabetisk ordning. Med Power Pivot-tillägget kan du ändra sorteringsordningen så att de visas i kronologisk ordning.

Tabell BasicCalendarUS ska vara öppet i Power Pivot-fönstret.

Klicka på i huvudtabellen Sortera efter kolumn.

Välj i fältet "Sortera". MonthInCalendar.

I fältet "Till" väljer du Månad För År.

Pivottabellen sorterar nu varje kombination av "månad och år" (oktober 2011, november 2011) efter månadens nummer under året (10, 11). Att ändra sorteringsordningen är lätt eftersom kanalen datumström tillhandahåller alla nödvändiga kolumner för att det här skriptet ska fungera. Om du använder en annan tidsintelligenstabell kommer dina steg att vara annorlunda.

"Relationer mellan tabeller kan krävas"

Som fält läggs till pivottabell du kommer att meddelas att en relation mellan tabeller krävs för att hantera de fält som valts i pivottabellen.

Även om Excel kan tala om för dig när en länk behövs, kan den inte berätta vilka tabeller och kolumner du ska använda, eller om en länk är möjlig mellan tabeller. För att få svar på dina frågor, prova följande.

Steg 1. Bestäm vilka tabeller som ska inkluderas i relationen

Om din modell bara innehåller ett fåtal tabeller är det tydligt vilka som ska användas. Men för större modeller kan du behöva hjälp. Ett sätt är att använda sjökortsvyn i Power Pivot-tillägget. Diagramvy ger en visualisering av alla tabeller i datamodellen. Med den kan du snabbt avgöra vilka tabeller som är separerade från resten av modellen.

Notera: Du kan skapa tvetydiga relationer som är ogiltiga när de används i en pivottabell eller Power View-rapport. Alla dina tabeller kan vara relaterade på något sätt till andra tabeller i modellen, men när du försöker slå samman fält från olika tabeller får du meddelandet "Relationer mellan tabeller kan krävas." Mest trolig orsakär att du har stött på ett många-till-många-förhållande. Om du följer en kedja av relationer mellan tabeller som ansluter till de tabeller du behöver, kommer du sannolikt att upptäcka att det finns två eller flera en-till-många-relationer mellan tabeller. Det finns ingen enkel lösning som fungerar i alla situationer, men du kan prova att skapa beräknade kolumner för att konsolidera de kolumner du vill använda i en tabell.

Steg 2: Hitta kolumner som kan användas för att skapa en sökväg från en tabell till en annan

När du har bestämt vilken tabell som inte är relaterad till resten av modellen, granska kolumnerna i den tabellen för att avgöra om en annan kolumn någon annanstans i modellen innehåller matchande värden.

Anta att du har en modell som innehåller produktförsäljning per territorium och att du därefter importerar demografisk data för att se om det finns en korrelation mellan försäljning och demografiska trender i varje område. Eftersom demografiska data kommer från en mängd olika källor, är deras tabeller initialt isolerade från resten av modellen. För att integrera demografisk data med resten av din modell måste du hitta en kolumn i en av de demografiska tabellerna som matchar den du redan använder. Till exempel, om demografiska data är organiserade efter region och dina försäljningsdata definierar ett försäljningsområde, kan du relatera de två datamängderna genom att leta efter vanliga kolumner som stat, postnummer eller region för att ge en uppslag.

Förutom att matcha värden finns det ytterligare några krav för att skapa associationer.

Om vi ​​behöver Överför automatiskt data från ett Excel-kalkylblad till ett annat, måste du länka dessa tabeller. Det finns flera sätt att länka data från två tabeller i Excel. Enkel - med hjälp av en formel, se artikeln "Hur man länkar tabeller i Excel".
Här, låt oss överväga hur man länkar två tabeller i olika excel-arbetsböcker genom att upprätta relationer mellan tabeller. När allt är inställt räcker det att öppna en tabell så att data överförs till denna tabell från en annan tabell. Du behöver inte öppna alla böcker för att anslutningen ska fungera. Det kommer att fungera med en öppen arbetsbok.
Så vi har två Excel-arbetsböcker - Bok1 och Bok2. Det finns en sådan tabell i bok 1, redan ifylld. Bord 1.Uppmärksamhet!
Om tabellen har sammanslagna celler, kommer länken inte att fungera i dem eller kommer att gå vilse.
I bok 2 gör vi samma tabell, men fyller inte i den.

En annan funktion "Infoga länk" kan kallas så här. Vi kopierar också, men i snabbmenyn klickar vi på funktionen " Specialinsats". Klicka på knappen "Infoga länk" i dialogrutan som visas.

Om ett kalkylblad använder data från ett annat blad anses dessa blad vara länkade. Med länkning kan du samla cellvärden från flera olika tabeller på samma kalkylblad.

Ändra innehållet i en cell på samma ark eller tabell ( källa) i en arbetsbok ändrar dess associerade celler i ark eller tabeller ( mottagare). Denna princip skiljer länkande ark från att bara kopiera innehållet i celler från ett ark till ett annat.

Beroende på exekveringstekniken är bindning "hetero" och genom kommando SPECIALBILAGA.

Metod 1 - "Direkt celllänkning"

Direktlänkning ark används direkt när en formel skrivs in i en cell, när en referens till en cell i ett annat ark används som ett av elementen i formeln. Till exempel, om tabellcell B4 på kalkylblad2 innehåller en formel som använder en länk till cell A4 i ett annat kalkylblad (till exempel Blad1) och båda arken är laddade med data, kallas sådan länkning av blad "direkt".

Termin "direkt" bindning betyder att användaren själv direkt när du anger formeln pekar påarknamn Och absolut celladress, åtskilda av ett utropstecken "!".

Formelexempel: = C5*Sheet1! A4

Blad 3! B2*100 %

Blad 1! A1- Blad2! A1

Notera.

För att ange en länk till celler och ark som finns i oladdade (oöppnade) arbetsböcker, i formeln, måste du ange utan mellanslag fullständig sökväg filplats. Sökvägen anges i enkla citattecken, där namnet på disken, katalogen (mappen), arbetsbokens namn (filnamnet) inom hakparenteser och namnet på det ark som länken går till anges.

Metod 2 - Länka celler genom kommandot "Klistra in special".

Länka via kommando SPECIALBILAGA utförs om någon tabellcell på ett kalkylblad ska innehålla värdet av en cell från ett annat kalkylblad.

Att reflektera i cell C4 på arket Pris värdet av cell H4 på originalarket Inköp, måste du placera markören på cell H4 i källarket och utföra kommandot Redigera – Kopiera. På arket Pris placera markören på cell C4, som måste vara länkad till den ursprungliga, och kör kommandot Redigera - Klistra in special - Klistra in länk(se fig. 8). Sedan på arket Pris en indikation på cellen i originalarket visas Inköp, Till exempel: = Köp!$N$4

Med denna bindning använder EXCEL automatiskt den absoluta adressen per cell, eftersom en relativ referensadress kan leda till fel, särskilt vid åtkomst till oladdade filer (arbetsböcker).

Träning. Länka cellerna C4, C5, C6, C7, C8 i tabellen Inköpskostnader på ett ark Pris med motsvarande celler på arket Inköp med användning av olika metoder för att länka celler (fig. 8).

Ris. 8 Länka celler i olika kalkylblad

! När du länkar celler, bestäm vilka celler som är källcellerna.

! För en länkad tabell kan celler från olika tabeller på olika kalkylblad eller på det aktuella bladet vara källceller.

Uppdrag för självständigt arbete.

Innan du utför en självständig uppgift, överväg exemplet med arktabeller Pris länkar celler.

Till exempel:

på ett ark Pris i bordet Inköpskostnader celler A4:A8 är länkade till tabellceller Antal köpta produkter på ett ark Inköp;

celler B4:B8 är initiala, eftersom. innehålla initial information om priserna på de köpta varorna;

cellerna C4:C8 är länkade till cellerna H4:H8 på arbetsbladet Inköp;

celler D4:D8 innehåller formler för att beräkna det belopp som spenderas på de köpta varorna och hänvisar till cellerna i deras egen tabell (till exempel ser formeln i cell D4 ut som = B4 * C4, vilket innebär att multiplicera priset på varorna med dess kvantitet);

cell D9 är summan av celler D4:D8;

i den andra tabellen Prisberäkning på samma ark är celler A14:A18 anslutna på samma sätt som klausul 1;

cellerna B14:B18 är länkade till de ursprungliga cellerna i det aktuella arket B4:B8;

celler C4:C8 är initiala, eftersom innehålla initial information om salongens påslag för de köpta varorna;

celler D14:D18 innehåller formler för att beräkna försäljningspriset för en produkt och hänvisar till cellerna i deras egen tabell (till exempel ser formeln i cell D14 ut som = B14 * C14 + B14, vilket innebär att multiplicera inköpspriset med mängden påläggsprocent, som anger mängden pålägg som måste läggas till inköpspriset);

Efter att ha utfört alla operationer med dessa tabeller, kontrollera deras "operabilitet".

Förändra Produktnamn - Soffa i cell A4 på ett ark Inköp till en annan - till exempel Soffa.

Förändra kvantitet inköpta varor Soffa i juni (i cell G4 på arbetsbladet Inköp ange siffran 11).

Förändra inköpspriset för Soffa i cell B4 på arket Pris för en annan - 2500,00 rubel.

Förändra procent av Soffans marginal i cell C14 på arket Pris från 50 % till 32 %.

Kolla upp , har det skett ändringar i relaterade tabeller eller inte?

Notera, vilka celler i olika tabeller som påverkades av ändringarna.

Uppmärksamhet! När man länkar celler via PASTE SPECIAL... blir kopiering till intilliggande celler problematisk på grund av den absoluta adresseringen av cellerna.

Övning 1. Länka cellerna i resten av arbetsbokstabellerna med olika metoder.

Instruktion. I tabellerna för beräkning av intäkter och inkomster för 1 kvartal används formeln för att summera källcellerna för endast 1 kvartal.

Uppgift 2. Skapa på ark Inkomst Och Inkomst kalkylblad för andra kvartalet. Länka dessa tabeller till motsvarande källdata.

Instruktion.

Uppgift 3. Rita ett cirkeldiagram på ett ark Inkomst och analysera inkomstfördelningen efter produkttyp.

Uppgift 4. Lägg till ett kalkylblad i slutet av arbetsboken Konsoliderat uttalande. Skapa en pivottabell på den som återspeglar antalet köp och försäljningar, marginal, köp- och försäljningspriser, försäljningsintäkter för det första kvartalet och för det andra kvartalet efter produktnamn. Länka den här tabellen till motsvarande källdata i andra kalkylblad.

Instruktion. I tabellerna för beräkning av intäkter och intäkter för 2:a kvartalet, använd källcellerna för endast 2 kvartal.

Information i ett kalkylblad Microsoft excel kan kombineras med data som hämtats från ritningen.

Genom att skapa en e-postanslutning kalkylblad du kan ta med information från hela arket eller bara en del av det, d.v.s. namngiven intervall. I dialogrutan Länk till externa data kan du göra följande:

• Ställ in datalänken genom att välja XLS-filen till vilken du vill länka data som hämtas från ritningen.
• Mappa de extraherade ritningsdata till kalkylbladsdata.
• Välj kolumnerna i XLS-filen som du vill slå samman med den extraherade ritningsdatan.

Sidan Redigera data ger åtkomst till dialog ruta Länka till extern data, där du kan länka den hämtade datan till hela Excel-kalkylarket eller till en rad rader och kolumner.

För att skapa en länk till ett kalkylblad måste datorn som utför datahämtningen ha Microsoft-applikation Excel. Om inte installerat Microsoft program Excel eller XLS-fil inte lagras lokalt på maskinen där data hämtas, kan datalänken inte upprättas.

Efter att ha ändrat det länkade kalkylarket, till exempel att lägga till en rad eller kolumn, kan du uppdatera tabellen i ritningen för att återspegla ändringen genom att använda REFRESH-kommandot. På liknande sätt, om du ändrar en tabell i en ritning, kan det associerade kalkylbladet uppdateras med samma kommando.

Obs: Använd REFER-kommandot för att ta bort en datalänk. Palett datalänkar externa länkar" kan inte raderas.

Om datalänkning och kartläggning

Datalänkning består av att välja ett Microsoft Excel-kalkylblad för att kombinera hela eller delar av kalkylarket med den extraherade ritningsdatan. För mer information om databindning, se "Skapa en tabellassociation med extern data".

Datamappningsprocessen skapar en länk mellan två uppsättningar data: data i den extraherade ritningsdatatabellkolumnen och data i Excel-kalkylbladskolumnen.

Följande exempel betraktar en enkel BOM. Tre kolumner med data extraheras från ritningen. Dessa kolumner innehåller följande data: Delnr., Redaktionell Och Kvantitet. Ytterligare två kolumner med data från kalkylarket kan inkluderas i specifikationen. Dessa kolumner innehåller följande data Pris Och Leverantör.

I detta exempel kalkylarket innehåller en artikelnummerkolumn som har en rubrik Katalognummer och matchar kolumnen Detaljnummer i den extraherade datatabellen. Välj i dialogrutan Länk till externa data "Detaljnummer" som en kolumn för att rita data, och "Katalognummer" som kolumn för extern data. Eftersom det finns åtminstone en motsvarande datacell i både ritningsdatakolumnen och den externa datakolumnen, "vet" dataextraktionsfunktionen hur de två källorna är relaterade.

Datakonsistens kan kontrolleras genom att klicka på knappen Kontrollera efterlevnad i dialogrutan Länk till externa data. Om det inte överensstämmer, visas ett varningsmeddelande som anger orsaken. I vissa fall rätt skapande det finns ingen koppling mellan ritningsdata och kalkylbladsdata.

Kontrollerar mappad data

Ett fel på datakonsistenskontrollen innebär att en giltig matchning inte kan hittas mellan ritningsdata och extern data (i kalkylbladet). Följande felmeddelanden visas.

Ingen överensstämmelse hittades mellan värdena i ritningsdata och externa data.

• Om problemet kvarstår slås inte externa data samman. Jämför kalkylarkets kolumndata med ritningsdata och försök hitta matchande värden. Till exempel kan ritningsdata och extern data ställas in på olika betydelser noggrannhet.

Datakolumnen i extern data innehåller inga unika värden.

• En extern datakolumn innehåller en eller flera celler med samma data. I denna situation är det inte möjligt att unikt mappa ritningsdata till extern data.

Först och främst är det inte en typisk funktion för MS Excel att länka tabeller efter vanliga funktioner. För sådana uppgifter, där specialprogram så kallade databaser. Även om det är mer korrekt att kalla dem "databashanteringssystem", eller DBMS. Dessa inkluderar till exempel MS Access, MS SQL Server, Oracle och många andra. I dessa system är sammanfogning av tabeller med vanliga nyckelfält kanske den vanligaste operationen. Och för sin lösning finns kraftfulla och behändiga verktyg. De låter dig organisera en mängd olika typer av relationer mellan tabeller, kontrollera dataintegritet, utföra kaskadradering av poster, etc. Men för att kunna använda DBMS fullt ut behöver du viss utbildning, kunskap om frågespråket, etc. Därför, i praktiken av en revisor, används ett DBMS i dess rena form sällan, och all databehandling sker vanligtvis i Excel-program. Vad gäller MS Excel, här är möjligheterna att länka tabeller begränsade. I grund och botten är denna koppling att skriva formler som refererar till olika ark eller arbetsböcker. Du kan skapa en utskriven rapport eller pivottabell som fungerar på flera databaser. Men det finns inga speciella verktyg för en fullfjädrad organisation av relationer mellan datamängder i Excel, och det borde inte vara det.

Men allt är inte så illa ... Faktum är att i verkliga arbete Allt alternativ för att länka tabeller till en revisor behövs vanligtvis inte. Ja, och utan en kaskadradering av poster kommer den också att klara sig på något sätt. Men att koppla samman ett stort bord med en uppslagsbok är en ganska relevant och praktiskt användbar uppgift. Och, viktigast av allt (!), en sådan funktion är ganska realiserbar iMS Excel. Och det enda som krävs för detta är ett par enkla knep och ett system med hyperlänkar.

Som ett exempel kommer jag att använda två tabeller, vars form visas i fig. 1 och 2. Det första bordet (det finns på arket " Order ”) består av fyra fält: “ Order”, “Datum”, “Kund”, “Belopp " och medan det tomma fältet " Kommentar. ". Detta är en förenklad version av orderdatabasen. I fältet " Kommentar. » vi kommer så småningom att lägga en länk till katalogelementet. Det är tydligt i databasen Order » En motpart kan dyka upp många gånger - det beror på antalet beställningar som denna kund gör.

Den andra tabellen heter " Kunder ". Jag lämnade bara fem kolumner i den: " namn ” (företagets namn), följt av direktörens namn, juridisk adress, telefonnummer och e-post.

Viktig! I katalogen "Clients » en rad innehåller data om ett företag. Upprepningar är inte tillåtna här, varje klient finns endast en gång i databasen.

Så vi kommer att organisera en relation mellan bordet " Beställningar" och "Kunder ". För att göra detta, i fältet Kommentar. » tabeller « Beställningar » vi kommer att lägga en hyperlänk som pekar på en specifik tabellrad « Kunder ". Algoritmen för hyperlänken kommer att vara följande. Först måste den analysera innehållet i fältet " NaimPredpr "V nuvarande linje tabeller" Order ". Med andra ord, kom ihåg Företagsnamn vem som lagt en viss beställning. Länken ska då hoppa till arket " Kunder ". På det här bladet hittar du en rad som beskriver parametrarna för företaget som gjorde beställningen. Och slutligen - ställ in pekaren nuvarande cell till motsvarande rad i katalogen " Kunder ". Så här ser det ut i allmänna termer.

Förresten, i databasterminologi kallas ett sådant förhållande " en till många " (när den ses från sidan av bordet" Kunder "). Det vill säga, ett element i kundkatalogen hänvisar till flera poster i orderdatabasen. I MS Excel finns det inga speciella verktyg för att skapa en sådan länk. Vi måste bygga dem själva. För att göra detta, baserat på algoritmen, behöver vi: en inbyggd funktion för att skapa en hyperlänk (en sådan funktion kallas " HYPERLÄNK() ”) och en funktion för att söka efter data i en tabell. Jag föreslår att du använder funktionen " MATCH() ". Men det är inte allt. Hyperlänken måste vara den exakta adressen för övergången: filnamn, kalkylbladsnamn och cellen som du vill aktivera. För att få namnet på filen, arket och cellen använder vi funktionen " CELL() ". Dessutom, för att bilda en rad med hoppadressen, måste vi vända oss till de inbyggda funktionerna för att arbeta med text: " PSTR() "och" SEARCH() ". Det är faktiskt alla huvudverktygen. Du kan gå vidare till praktisk implementering.

Låt oss göra några först preliminär åtgärd. Låt oss börja med funktionen " HYPERLÄNK() ", vilket kommer bli nyckel när du organiserar relationer mellan tabeller. Hon har två alternativ. Den första är adressen, det vill säga namnet på filen och arbetsbladet som länken pekar på. Den andra parametern är adressen till en specifik cell på detta ark där hyperlänken kommer att peka. Vi kommer att ha många hyperlänkar. Och varje gång för att skapa dem måste du ange filnamnet och arknamnet i funktionsparametrarna. Det är långt och obekvämt. Därför är det bättre att skapa en variabel en gång med namnet på arket och filen och sedan ange dess variabel i funktionen " HYPERLÄNK() " efter behov. När det gäller MS Excel är det bekvämt att använda som en sådan variabel namngivna cellintervall. Det är här vi börjar.

1. Vi ringer upp Excel, laddar dokumentet och går till menyn " Formler ". Tejpen kommer att se ut som i fig. 3.

2. I gruppen " Vissa namn» klicka på ikonen « Namnchef ". Fönstret som visas i fig. 4.

3. I det här fönstret trycker du på knappen " Skapa ". Fönstret " Att skapa ett namn ", visat i fig. 5.

4. I det här fönstret, i fältet " Namn:" skriv in texten "Min_lista". I fältet Område: » skriv ut formeln «=MIDDEN(CELL("filnamn" ;Kunder!$A$1);SÖK(" [ " ;CELL(" filnamn " ;Kunder!$A$1));256)& " ! " » (Fig. 3).

5. Klicka på "OK" i fönstret "Skapa ett namn".

6. Klicka på OK i fönstret Namnhanteraren.

Nu ska vi se vad vi faktiskt gjorde? Arbetsboken har ett nytt namngivet intervall " Min lista".

Viktig! I vårt fall måste namnet på det namngivna området vara UTAN mellanslag. Annars kommer adresseringsformler inte att fungera korrekt.

Som alla namngivna intervall pekar det på en cell eller grupp av celler i en Excel-arbetsbok. Men i vårt fall är adressen till denna grupp inte permanent, men dynamisk. Den bildas av formeln som vi skrev in i fältet " Räckvidd: ". Kort om arbetet med denna formel. Låt oss börja inifrån - med funktionen " CELL()".

Uttrycket "CELL(" filnamn " ;Kunder! $A$1) "hänvisar till cellen" A1" på arket "Kunder ” och returnerar för denna cell dess hela adressen , dvs sökvägen till filen, namnet på filen och namnet på arket där denna cell finns. Mitt dokument med tabeller heter till exempel " DeanReferencesExcel_.xls ". Det lagras på disk D:" i mappen "!Factor ". Då blir resultatet av formeln:D:!Factor[DeanReferencesExcel_.xls]klienter».

Från denna linje behöver vi bara ta filnamn Och ark A arbetsbok - enhetsbeteckningen och mappnamnet kommer inte att behövas när du skapar en hyperlänk. För att lösa detta problem använder vi standardfunktionernaExcel för att arbeta med text.

Du kan klippa en del av en sträng med funktionen " MITTEN() ". Men hon måste specificera: original text(vi har hela vägen till cellen), start position Och antal tecken Den som ska tas bort från originalsträngen.

Startpositionen är lätt att bestämma. För att göra detta, använd funktionen SÖK() ” vi hittar den första förekomsten av den fyrkantiga öppningsparentesen (“[ ”) i texten där sökvägen till cellen är lagrad. Formelfragmentet som utför denna operation ser ut så här: SÖK(" [ " ;CELL(" filnamn " ; Kunder!$A$1)) ". För raden" D:!Factor [DeanReferencesExcel_.xls]Kunder» denna formel kommer att returnera « 12 ". Antalet tecken som måste klippas från källtexten, jag valde maximalt - " 256".

Det återstår att ersätta de erhållna värdena i funktionen " MITTEN() ". Dess första parameter är texten med adressen till cellen. Den andra parametern är numret på startpositionen för att klippa en del av strängen. Den sista parametern är det högsta tillåtna antalet tecken per rad. Formeln för denna operation skulle vara:=MIDDEN(CELL("filnamn" ;Kunder!$A$1);SÖK

(" [ " ;CELL(" filnamn " ;Kunder!$A$1));256) ". I vårt exempel kommer en sådan formel att returnera resultatet i formen "[DeanReferenceExcel_.xls]Kunder". Det här är faktiskt en länk till bladet " Kunder » arbetsbok « DeanReferencesExcel_.xls ". Nästa kan vara adressen till cellen. Men än så länge saknas ett element - mellan arknamnet och celladressen ska det finnas Utropstecken (« ! "). Vi kan lägga till denna symbol till formeln med hjälp av operationen "& ", och i den slutliga versionen kommer uttrycket att se ut så här:"=MIDDEN(CELL("filnamn" ;Kunder!$A$1);SÖK(" [ " ;CELL(" filnamn " ; Kunder!$A$1)); 256)&" ! " ". Och resultatet av hennes arbete blir:».

Råd Kopiera försiktigt formelfragmenten till separata celler i kalkylbladet, och sedan kommer arbetet med varje del av det att vara till hands.

Förresten, funktionen av en formel inom ett namngivet intervall är lätt att kontrollera. Ange uttrycket "=Mitt_ark " och tryck på " Stiga på ". Resultatet bör visas i cellen:[DeanLinkExcel_.xls]Kunder!».

Viktig! Observera att för korrekt funktion arknamnsformler får inte innehålla mellanslag.

1. Gå till arket " Order ", gå in i cellen" E2".

2. Ange formeln "=HYPERLÄNK(Mitt_ark&ADRESS(SÖK(C2;Kunder!$A:$A;0); 1);" > " ) ».

3. Kopiera denna formel till hela tabellens höjd. Resultatet av vårt arbete visas i fig. 6.

Låt oss kolla vad vi har. I bordet " Order »vänsterklicka till exempel på cellen « E6 ". Denna rad innehåller information om beställningen med numret " 5 "från företaget" PE "Kolo"". Efter klick Excel kommer att växla till " Kunder ", och den aktiva cellpekaren kommer att vara på adressen " A6 ". Det är i denna position i katalogen som information om " PE "Kolo"" (fig. 7).

Låt oss kort överväga formelns algoritm. Uttryck"=MATCH(C2;Kunder! $A:$A;0)» hittar en cell i kolumnen « A" på arket "Kunder ", som innehåller namnet på motparten från cellen " C2" ark "Beställningar ". Den sista parametern för funktionen " MATCH() "är lika med" 0 ". Det betyder att den kommer att söka efter ett värde enligt principen exakt tändstickor. Låt oss gå tillbaka till vårt exempel. Anta att vi arbetar med strängen " 6 » bord « Beställningar » (Fig. 6). Denna rad innehåller ordernumret " 5 "från firman" PE "Kolo" ". Efter att ha kopierat hyperlänken från cellen " E2 » ner i kolumnen « E" i cell "E6 "Formeln med sökfunktionen blir så här:"MATCH(C6;Kunder!$A:$A;0)". Och resultatet av detta uttryck kommer att vara lika med " 6 ". Det betyder att i tabellen Kunder " Företagsbeskrivning " PE "Kolo" " belägen i sjätte kalkylbladsrad.

Varsågod. Vi kommer att ersätta resultatet av detta uttryck som den första parametern i funktionsadressen. Den andra parametern är " 1 ". Sedan uttrycket " ADRESS(SÖK (C2;Kunder!$A:$A;0);1)" (den andra raden i tabellen" Order "") betyder i huvudsak " ADRESS(2;1) ". Denna funktion returnerar som text adressen till cellen som finns i den andra raden och första kolumnen i kalkylbladet. För beställning" 1 ' (andra raden) detta kommer att vara adressen till '$A$2 ". För en beställning i den sjätte raden i tabellen (företag " PE "Kolo" ") uttryck kommer att returnera strängen "$A$6", etc.

Med adressering av en cell är det tydligt. Nu måste du använda resultatet i funktionen " HYPERLÄNK() ".

Här är situationen. Låt mig påminna dig om att funktionen " HYPERLÄNK() » två parametrar. Den första är adressen dit hyperlänken pekar på. Den innehåller namnet på filen, arket och adressen till cellen som ska hoppa till. Vi har redan fil- och arknamnen, denna information lagras i variabeln " Min lista ". Vi fick adressen till cellen för att länka till motparten för en specifik beställning. Det återstår att kombinera dessa två delar med operationen "& ". Och då blir det slutliga uttrycket för hoppadressen:Min_lista&ADRESS(SÖK(C2; Kunder!$A:$A;0);1)».

Låt oss se vad vi har. För cell" C2 » denna formel kommer att returnera resultatet «[DeanReferenceExcel_.xls]Kunder!$A$2" - dvs en referens till cellen " A2 » i katalogen « Kunder ". Det stämmer: för ordernummer " 1 » länken pekar till motparten « TOV "Smit" ". Om du hänvisar till cellen C6 " (ordernummer " 5" från "PE "Kolo" ”), sedan i den här raden i tabellen ” Order "Uttrycket för hoppadressen kommer att se ut så här:"Mitt_ark&ADRESS(SÖK(C6;Kunder!$A:$A;0);1)". Och resultatet av formeln blir:[DeanReferenceExcel_.xls]Kunder!$6$».

Vi går vidare till den andra parametern för funktionen " HYPERLÄNK() ". Det här borde vara texten somExcel kommer att visa hyperlänken på plats. Med andra ord, detta är namnet på själva hyperlänken. Jag valde symbolen ">" (tecken "mer "). Och då blir den slutliga formeln för att skapa en hyperlänk:=HYPERLÄNK(Min_List&ADRESS(SÖK(C2; Kunder!$A:$A; 0);1);" > " ) ».

Det handlar om att skapa relationer mellan tabellerExcel. Vi klarade uppgiften, alla länkar fungerar korrekt. Det enda jag skulle vilja lägga till några ord om utformningen av bordet. Nu i kolumnen