How to link graphs in Excel. Excel workbook

Have you used the VLOOKUP function to move column data from one table to another? Since Excel now has a built-in data model, the VLOOKUP function has been deprecated. You can create a relationship between two tables based on matching data in them. You can then create Power View sheets or PivotTables and other reports with fields from each table, even if they come from different sources. For example, if you have customer sales data, you may want to import and link to analyze sales trends by year and month.

All tables in the workbook are listed in the PivotTable and Power View field lists.

When importing related tables from relational base Excel data can often create these relationships in a data model generated in the background. In other cases, you must create the links manually.

If the team Relationship is inaccessible, which means the workbook contains only one table.

In the window Relationship Management click the button Create.

In the window Creating a connection click the arrow next to the field Table and select the table from the dropdown list. In a one-to-many relationship, this table must be a part with multiple elements. In the customers and time logic example, you must first select the customer sales table because there are likely to be many sales occurring each day.

For element Column (alien) select the column that contains data related to the element. For example, if you have a date column in both tables, you need to select that column now.

In field Related table select a table that contains at least one column of data that is related to the table selected in the field Table.

In field Related Column ( primary key) select the column containing unique values, which correspond to the values ​​in the column selected in the field Column.

Click the button OK.

Learn more about relationships between tables in Excel

Notes about connections

Example. Linking time logic data with airline flight data

You can learn how both tables and logic relate to time using free data on the Microsoft Azure Marketplace. Some of these data sets are very large, and downloading them in a reasonable amount of time requires fast connection to the Internet.

You can now separate arrival delays by year, month, and other calendar values.

Adviсe: By default, months are listed in alphabetical order. With the Power Pivot add-in, you can change the sort order so that they appear in chronological order.

Table BasicCalendarUS should be open in the Power Pivot window.

In the main table, click Sort by column.

In the Sort field, select MonthInCalendar.

In the By field, select MonthOfYear.

The pivot table now sorts each month and year combination (October 2011, November 2011) by the month number in the year (10, 11). Changing the sort order is easy because the channel DateStream provides all the necessary columns for this script to work. If you use a different time logic table, your actions will be different.

"Relationships between tables may be required"

As fields are added to pivot table You will be notified that a relationship between tables is required to understand the fields selected in the PivotTable.

Although Excel can tell you when a relationship is needed, it cannot tell you which tables and columns to use or whether a relationship between tables is possible. To get answers to your questions, try the following.

Step 1: Decide which tables to link to

If your model contains only a few tables, it is clear which ones to use. But for larger models you may need some help. One way is to use the chart view in the Power Pivot add-in. The chart view provides a visualization of all the tables in the data model. With it, you can quickly determine which tables are separated from the rest of the model.

Note: You can create ambiguous relationships that are not valid when used in a PivotTable or Power View report. All of your tables may be related in some way to other tables in the model, but when you try to join fields from different tables, you will receive the message "Table relationships may be required." Most probable cause is that you are faced with a many-to-many relationship. If you follow the chain of table relationships that connect to the tables you need, you will likely find that there are two or more one-to-many relationships between the tables. There is no easy workaround that will work in every situation, but you can try creating calculated columns to consolidate the columns you want to use in one table.

Step 2: Find columns that can be used to create a path from one table to another

Once you have determined which table is not related to the rest of the model, review the columns in it to determine whether another column elsewhere in the model contains corresponding values.

Let's say you have a model that contains product sales by territory, and you subsequently import demographic data to see if there is a correlation between sales and demographic trends in each territory. Because demographic data comes from different sources, their tables are initially isolated from the rest of the model. To integrate demographics data with the rest of your model, you will need to find a column in one of the demographics tables that matches the one you are already using. For example, if demographic data is organized by region and your sales data defines a sales area, then you could link the two data sets by finding common columns such as state, zip code, or region to provide a lookup.

In addition to matching values, there are several additional requirements for creating relationships.

If we need to data from one Excel table was automatically transferred to another, you need to link these tables. There are several ways to link data from two tables in Excel. One way - using a formula, see the article “How to link tables in Excel”.
Here we will consider, how to link two tables in different Excel workbooks, by establishing a relationship between tables. Once everything is set up, it is enough to open one table so that the data is transferred to this table from another table. You don't need to open all the books for the connection to work. It will work with one open workbook.
So, we have two Excel workbooks - Book1 and Book2. In Book 1 there is such a table, already filled out. Table 1.Attention!
If the table has merged cells, then the connection in them will not work or will be lost.
In Book 2 we make the same table, but do not fill it out.

You can also call the “Insert Link” function like this. We also copy, but in the context menu click on the function “ Special insert" In the dialog box that appears, click on the “Insert Link” button.

If one worksheet uses data from another worksheet, the sheets are considered related. Using linking, you can combine cell values ​​from several different tables into one worksheet.

Changing the contents of a cell in the same worksheet or table ( source) of a workbook causes its associated cells in sheets or tables to change ( receivers). This principle distinguishes linking sheets from simply copying the contents of cells from one sheet to another.

Depending on the technique of execution, binding can be “straight” and through the team SPECIAL INSERT.

Method 1 – “Direct linking of cells”

Direct linking sheets is used directly when entering a formula into a cell, when a reference to a cell of another sheet is used as one of the elements of the formula. For example, if table cell B4 on Worksheet2 contains a formula that uses a link to cell A4 of another worksheet (for example, Sheet1) and both sheets are loaded with data, then this linking of sheets is called “direct”.

Term “direct” binding means that the user himself directly when entering the formula indicatessheet name And absolute cell address, separated by an exclamation point "!".

Examples of formulas: = C5*Sheet1! A4

Sheet3! В2*100%

Sheet1! A1- Sheet2! A1

Note.

To indicate references to cells and sheets located in unloaded (unopened) workbooks, you must specify in the formula without spaces full path file location. The path is specified in single quotes, which indicates the name of the disk, directory (folder), the name of the workbook (file name) in square brackets and the name of the sheet to which the link is going.

Method 2 – Linking cells using the “Paste Special” command

Linking via command SPECIAL INSERT is performed if any table cell on one worksheet should contain the value of a cell from another worksheet.

To reflect in cell C4 on the worksheet Price value of cell H4 on the original sheet Purchase, you need to place the cursor on cell H4 of the source sheet and execute the command Edit–Copy. On a sheet Price place the cursor on cell C4, which needs to be linked to the original one, and execute the command Edit–Paste Special–Insert Link(see Fig. 8). Then on the sheet Price an indication of the source sheet cell will appear Purchase, For example: = Purchase!$H$4

With this linking, EXCEL automatically uses the absolute address per cell, because the relative access address can lead to errors, especially if accessing unloaded files (workbooks).

Exercise. Link cells C4, C5, C6, C7, C8 in the table Procurement costs on a sheet Price with corresponding cells on the sheet Purchase using various methods of linking cells (Fig. 8).

Rice. 8 Linking cells from different worksheets

! When linking cells, determine which cells are the original cells.

! For one linked table, the source cells can be from different tables on different worksheets or on the current worksheet.

Assignments for independent work.

Before completing an independent task, let’s look at the example of sheet tables Price linking cells.

For example:

on a sheet Price in the table Procurement costs cells A4:A8 are linked to table cells Quantity of purchased products on a sheet Purchase;

cells B4:B8 are the original ones, because

contain initial information about the prices of purchased goods; Purchase;

cells C4:C8 are linked to cells H4:H8 on the worksheet

cells D4:D8 contain formulas for calculating the funds spent on purchased goods and refer to the cells of their own table (for example, the formula in cell D4 looks like =B4*C4, which means multiplying the price of the product by its quantity);

cell D9 is the sum of cells D4:D8; in the second table Price calculation

on the same sheet, cells A14:A18 are connected similarly to step 1;

cells B14:B18 are related to the source cells of the current sheet B4:B8;

cells C4:C8 are the original ones, because

contain initial information about the salon’s markup on purchased goods;

cells D14:D18 contain formulas for calculating the selling price of goods and refer to the cells of their own table (for example, the formula in cell D14 looks like =B14*C14+B14, which means multiplying the purchase price by the established percentage of the markup, which gives the amount of the markup that must be added to purchase price); After performing all operations with these tables, check their functionality. Change Name of product - Purchase Sofa in cell A4 on the sheet

cells D14:D18 contain formulas for calculating the selling price of goods and refer to the cells of their own table (for example, the formula in cell D14 looks like =B14*C14+B14, which means multiplying the purchase price by the established percentage of the markup, which gives the amount of the markup that must be added to purchase price); to something else - for example Sofa. quantity of purchased goods Purchase Sofa

cells D14:D18 contain formulas for calculating the selling price of goods and refer to the cells of their own table (for example, the formula in cell D14 looks like =B14*C14+B14, which means multiplying the purchase price by the established percentage of the markup, which gives the amount of the markup that must be added to purchase price); in June (in cell G4 on the sheet Price enter the number 11).

cells D14:D18 contain formulas for calculating the selling price of goods and refer to the cells of their own table (for example, the formula in cell D14 looks like =B14*C14+B14, which means multiplying the purchase price by the established percentage of the markup, which gives the amount of the markup that must be added to purchase price); purchase price of the Sofa in cell B4 on the sheet Price for another – 2500.00 rub.

Sofa markup percentage in cell C14 on the sheet from 50% to 32%.

Check , have there been changes in the related tables or not?

Attention! Note,

which cells of different tables were affected by the changes made. When linking cells via PASTE SPECIAL... copying to adjacent cells becomes problematic due to the absolute addressing of the cells.

Exercise 1. Link the cells of the remaining tables in the workbook using various methods.

Note. The tables for calculating revenue and income for the 1st quarter use a formula for summing the source cells of only the 1st quarter. Task 2. Create on sheets Revenue And

Exercise 1.

Income tables for calculations for the 2nd quarter. Link these tables to the corresponding source data. Revenue Task 3.

Plot a pie chart on a worksheet and analyze the distribution of income by product type. Task 4. Create a summary table on it, reflecting by product name the quantity of purchases and sales, markup, purchase and sale prices, sales income for the 1st quarter and for the 2nd quarter. Link this table to the corresponding source data on the other worksheets.

Exercise 1. In the tables for calculating revenue and income for the 2nd quarter, use the source cells of the 2nd quarter only.

Information in a spreadsheet Microsoft Excel can be combined with data extracted from the drawing.

By creating a connection with electronic Excel table You can include information from the entire sheet or just part of it, i.e. named range. In the External Data Link dialog box, you can do the following:

• Set a data link by selecting the XLS file to which you want to link the data extracted from the drawing.
• Set the mapping between the extracted drawing data and the spreadsheet data.
• Select the columns in the XLS file that you want to merge with the extracted drawing data.

The Data Correction page provides access to dialog box"Link to External Data", where you can link the extracted data to the entire Excel spreadsheet or to a range of rows and columns.

To create a link to a spreadsheet, you must have Microsoft application Excel. If not installed Microsoft program Excel or XLS file is not saved locally on the computer on which the data is being retrieved, the data cannot be linked to.

After you change a linked spreadsheet, such as adding a row or column, you can update the table in the drawing to reflect that change by using the LINK UPDATE command. Likewise, if you change a table in a drawing, the associated spreadsheet can be updated using the same command.

Note: To remove a data link, use the LINK command. Links to data from the palette External links"Cannot be deleted.

About data linking and matching

Data linking involves selecting a Microsoft Excel spreadsheet to combine all or part of the table with the extracted drawing data. For more information about data binding, see Create a table link to external data.

The data mapping process creates a relationship between two sets of data: the data in the extracted drawing data table column and the data in the Excel spreadsheet column.

The following example examines a simple specification. Three columns of data are retrieved from the drawing. These columns contain the following data: Part no., Editorial And Quantity. You can include two additional columns of data from a spreadsheet in your specification. These columns contain the following data Price And Provider.

IN in this example the spreadsheet contains a column of part numbers that has a header Catalog number and corresponds to the column Detail number in the extracted data table. In the External Data Link dialog box, select "Detail number" as a column for drawing data and "Catalogue number" as a column for external data. Because there is at least one corresponding data cell in both the drawing data column and the external data column, the data extraction function knows how the two sources are related.

You can check the consistency of the data by clicking the Check Compliance button in the External Data Link dialog box. If the match is incorrect, a warning message is displayed indicating the reason. In some cases correct creation There is no connection between drawing data and spreadsheet data.

Validating matched data

A data match failure means that a valid match between the drawing data and the external data (in the spreadsheet) cannot be found. The following error messages are displayed.

No matches were found between the values ​​in the drawing data and the external data.

• If the problem cannot be resolved, external data merging will not occur. Compare the spreadsheet column data with the drawing data and try to find the corresponding values. For example, drawing data and external data can be set to different meanings accuracy.

The data column in external data does not contain unique values.

• An external data column contains one or more cells with the same data. In this situation, it is impossible to unambiguously define the correspondence between the drawing data and external data.

First and most importantly: linking tables based on common characteristics is not a typical feature for MS Excel. For such tasks there is special programs- so-called databases. Although it is more correct to call them “database management systems”, or DBMS. These include, for example, MS Access, MS SQL Server, Oracle and many others. In these systems, joining tables based on common key fields is perhaps the most common operation. And to solve it there are powerful and convenient tools. They allow you to organize a wide variety of types of relationships between tables, monitor data integrity, perform cascade deletion of records, etc. However, to fully use a DBMS, you need certain training, knowledge of the query language, etc. Therefore, in the practice of accountants, DBMSs in their pure form are rarely used , and all data processing is usually done in Excel program. As for MS Excel, the possibilities for linking tables are limited. Basically this communication involves writing formulas that reference different sheets or workbooks. You can create a printed report or pivot table that operates on multiple databases. But there are no special tools for fully organizing connections between data sets in Excel, and there shouldn’t be.

However, not everything is so bad... The fact is that in real work All An accountant usually does not need options for linking tables. And it will somehow manage without cascading deletion of records. But linking one large table with a directory is a completely relevant and practically applicable task. And, most importantly(!), such a function is completely implementable inMS Excel. Moreover, the only thing that is required for this is a couple of simple tricks and a system of hyperlinks.

As an example, I will use two tables, the form of which is shown in Fig. 1 and 2. The first table (it is located on the sheet " Orders ") consists of four fields: " Order", "Date", "Customer", "Amount " and the field is still empty " Comment. " This is a simplified version of the order database. In field " Comment. "We will ultimately put a link to the directory element. It is clear that in the database " Orders » one counterparty can appear many times - this depends on the number of orders placed by this client.

The second table is called " Clients " I left only five columns in it: “ Name "(name of the enterprise), followed by the name of the director, legal address, phone number and e-mail.

Important! In the directory "Clients" » one line contains data about one enterprise. Repetitions are not allowed here; each client is present in the database only once.

So, we are going to create a relationship between the table " Orders" and "Customers " To do this, in the field " Comment. » tables « Orders "we will put a hyperlink that will point to a specific row of the table " Clients " The algorithm for the hyperlink will be as follows. First, it must parse the contents of the field " NaimPredpr " V current line tables « Orders " In other words, remember company name who placed a specific order. The link should then navigate to the worksheet " Clients " On this sheet, find the line that describes the parameters of the company that made the order. And finally - set the pointer current cell to the corresponding line in the directory " Clients " This is how it looks in general terms.

By the way, in database terminology such a relationship is called “ one to many "(when viewed from the table side " Clients "). That is, one element of the customer directory references several records in the order database. There are no special tools in MS Excel to create such a connection. We must build them ourselves. To do this, based on the algorithm, we will need: a built-in function for creating a hyperlink (this function is called “ HYPERLINK() ") and a function for searching data in a table. I suggest using the " MATCH() " But that's not all. The hyperlink must be specified the exact address for transition: file name, worksheet name and cell that needs to be made active. To get the name of the file, sheet and cell, we will use the function " CELL() " In addition, to generate a string with the transition address, we will have to turn to the built-in functions for working with text: “ PSTR()" and "SEARCH() " That's all the main tools. You can move on to practical implementation.

First let's do a few preliminary actions. Let's start with the function " HYPERLINK() ", which will be key when organizing connections between tables. It has two parameters. The first is the address, i.e. the name of the file and worksheet to which the link points. The second parameter is the address of a specific cell on this sheet where the hyperlink will point. We will have a lot of hyperlinks. And each time to create them you will have to specify the file name and sheet name in the function parameters. It's long and inconvenient. Therefore, it is better to create a variable once with the name of the sheet and file, and then specify its variable in the function " HYPERLINK() " as needed. In relation to MS Excel, it is convenient to use as such a variable named cell range. This is where we will start.

1. Call Excel, load the document and go to the menu “ Formulas " The tape will look like in Fig. 3.

2. In the group " Specific names"click on the icon" Name Manager " The window shown in Fig. will open. 4.

3. In this window, click the button “ Create " The window “ Creating a name "shown in Fig. 5.

4. In this window in the field “ Name: "enter the text "My_List". In the Range field: » print the formula «=PSTR(CELL("filename" ;Customers!$A$1);SEARCH(" [ " ;CELL(" filename " ;Clients!$A$1));256)& " ! " " (Fig. 3).

5. In the “Create a name” window, click “OK”.

6. In the “Name Manager” window, click “OK”.

Now let's figure out what we actually did? A new named range has appeared in the workbook " My_List ".

Important! In our case, the name of the named range must be WITHOUT spaces. Otherwise, addressing formulas will not work correctly.

Like any named range, it points to a cell or group of cells in an Excel workbook. But in our case, the address of this group is not permanent, but dynamic. It is formed by the formula that we entered in the field “ Range: " Briefly about how this formula works. Let's start from the inside - with the function " CELL()."

The expression "CELL("filename" ;Customers! $A$1) " accesses the cell " A1" on the sheet "Customers " and returns for this cell its full address , i.e. the path to the file, the file name and the name of the sheet where this cell is located. For example, my document with tables is called “ DeanLinksExcel_.xls " It is stored on disk " D: " in the folder " !Factor " Then the result of the formula will be like this: “D:!Factor[DinLinksExcel_.xls]Clients».

From this line we only need to take file names And sheet A workbook - the drive letter and folder name are not needed when creating a hyperlink. To solve this problem we will use standard functionsExcel for working with text.

You can cut part of a line using the " PSTR() " But she needs to indicate: original text(for us this is the full path to the cell), starting position And number of signs, which needs to be cut from the original string.

The starting position is easy to determine. To do this, use the function " SEARCH() " we find the first occurrence of the square opening bracket ("[ ") into the text where the path to the cell is stored. The formula fragment that performs this operation looks like this: " SEARCH(" [ " ;CELL(" filename " ; Clients!$A$1)) ". For the line " D:!Factor [DinLinksExcel_.xls]Clients" this formula will return " 12 " I chose the maximum number of characters that need to be cut from the source text - “ 256".

It remains to substitute the obtained values ​​into the function " PSTR() " Its first parameter is the text with the address to the cell. The second parameter is the starting position number for cutting part of the line. The last parameter is the maximum allowed number of characters per line. The formula for this operation will be: “=PSTR(CELL("filename" ;Customers!$A$1);SEARCH

(" [ " ;CELL(" filename " ;Customers!$A$1));256) " In our example, such a formula will return the result as “[DinLinksExcel_.xls]Clients" Essentially, this is a link to the sheet " Clients » workbook « DeanLinksExcel_.xls " Next may be the cell address. But so far one element is missing - between the sheet name and the cell address there should be Exclamation point (« ! "). We can attach this symbol to the formula using the operation “& ", and in the final version the expression will look like this: "=PSTR(CELL("filename" ;Customers!$A$1);SEARCH(" [ " ;CELL(" filename " ; Clients!$A$1)); 256)&" ! " " And the result of her work will be like this: “».

Advice Carefully copy fragments of the formula into separate cells of the worksheet, and then the work of each part will be in the palm of your hand.

By the way, it’s easy to check how a formula works inside a named range. Enter the expression “=My_Sheet " and press the " key Enter " The result should appear in the cell: “[DinLinksExcel_.xls]Clients!».

Important! Please note that for proper operation Formula sheet names must not contain spaces.

1. Go to the sheet “ Orders ", stand on the cell " E2".

2. Enter the formula “=HYPERLINK(My_Sheet&ADDRESS(MATCH(C2,Customers!$A:$A,0); 1);" > " ) ».

3. Copy this formula to the entire height of the table. The result of our work is shown in Fig. 6.

Let's check what we got. In the table " Orders "Left-click, for example, on a cell " E6 " This line contains information about the order with the number " 5" from the company "PE "Kolo". After click Excel will switch to the worksheet " Clients ", and the active cell pointer will be at the address " A6 " It is in this position of the directory that information about “ Private enterprise "Kolo" (Fig. 7).

Let us briefly consider the algorithm of the formula. Expression "=MATCH(C2,Customers! $A:$A,0)"finds a cell in the column " A" on the sheet "Clients" ", in which the name of the counterparty from the cell " C2" sheet "Orders" " The last parameter of the function " MATCH()" is "0" " This means that it will look for the value according to the principle accurate coincidences. Let's return to our example. Suppose we are working with the string " 6 » tables « Orders "(Fig. 6). This line contains the order number " 5" from the company "PE "Kolo" " After copying the hyperlink from the cell " E2 "down the column" E" in cell "E6 "The formula with the search function will look like this: "MATCH(C6;Clients!$A:$A;0)" And the result of this expression will be equal to “ 6 " This means that in the table " Clients » company description « PE "Kolo" » located in sixth line of the worksheet.

Go ahead. We will substitute the result of this expression as the first parameter of the function, the address. The second parameter will indicate “ 1 " Then the expression " ADDRESS(MATCH (C2;Clients!$A:$A;0);1)"(second row of the table " Orders ") essentially means " ADDRESS(2;1) " This function will return as text the address of the cell that is located in the second row and first column of the worksheet. For order " 1 " (second line) this will be the address "$A$2 " To order in the sixth line of the table (company " Private enterprise "Kolo" ") expression will return the string "$A$6 "etc.

The cell addressing is clear. Now you need to use the obtained result in the function " HYPERLINK()."

This is the situation here. Let me remind you that the function “ HYPERLINK() "two parameters. The first is the address where the hyperlink points. It includes the name of the file, sheet and cell address to go to. We already have the names of the file and sheet, this information is stored in the variable " My_List " We have received the address of the cell for reference to the counterparty for a specific order. It remains to combine these two parts with the operation “& " And then the final expression for the transition address will be like this: “My_List&ADDRESS(MATCH(C2; Clients!$A:$A;0);1)».

Let's see what we got. For cell " C2 "such a formula will return the result "[DinLinksExcel_.xls]Clients!$A$2" - i.e. a link to the cell " A2 " in the directory " Clients " That's right: for order number " 1 » link points to counterparty « TOV "Smith" " If you refer to the cell " C6 "(order number " 5" from "PE "Kolo" "), then in this table row " Orders "The expression for the transition address will look like this: "My_List&ADDRESS(MATCH(C6,Clients!$A:$A,0),1)" And the result of the formula will be like this: “[DinLinksExcel_.xls]Clients!$A$6».

Let's move on to the second parameter of the function " HYPERLINK() " There should be text here thatExcel will show hyperlinks in place. In other words, this is the name of the hyperlink itself. I chose the symbol “> " (the sign "greater than "). And then the final formula for creating a hyperlink will be: “=HYPERLINK(My_List&ADDRESS(MATCH(C2, Clients!$A:$A, 0),1);" > " ) ».

That's all about creating relationships between tablesExcel. We completed the task, all links work correctly. The only thing I would like to add is a few words on the design of the table. Now in the column "