Merge Tables In Excel Using Power Query (Easy Step

     
Excel for sofaxuong.vn 365 Excel for the web Excel 2021 Excel 2019 Excel năm 2016 Excel 2013 Excel 2010 Excel 2007 More...Less

A data table is a range of cells in which you can change values in some of the cells & come up with different answers khổng lồ a problem. A good example of a data table employs the PMT function with different loan amounts and interest rates khổng lồ calculate the affordable amount on a trang chủ mortgage loan. Experimenting with different values khổng lồ observe the corresponding variation in results is a common task in data analysis.

Bạn đang xem: Merge tables in excel using power query (easy step


In sofaxuong.vn Excel, data tables are part of a suite of commands known as What-If analysis tools. When you construct and analyze data tables, you are doing what-if analysis.

What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. For example, you can use a data table lớn vary the interest rate and term length for a loan—to evaluate potential monthly payment amounts.


Note: You can perform faster calculations with data tables and Visual Basic for Applications (VBA). For more information, see Excel What-If Data Tables: Faster calculation with VBA.


Types of what-if analysis

There are three types of what-if analysis tools in Excel: scenarios, data tables, and goal-seek. Scenarios và data tables use sets of đầu vào values to calculates possible results. Goal-seek is distinctly different, it uses a single result and calculates possible input values that would produce that result.

Like scenarios, data tables help you explore a mix of possible outcomes. Unlike scenarios, data tables show you all the outcomes in one table on one worksheet. Using data tables makes it easy to lớn examine a range of possibilities at a glance. Because you focus on only one or two variables, results are easy lớn read & share in tabular form.

A data table cannot accommodate more than two variables. If you want khổng lồ analyze more than two variables, you should instead use scenarios. Although it is limited to only one or two variables (one for the row đầu vào cell & one for the column input đầu vào cell), a data table can include as many different variable values as you want. A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want.

Learn more in the article, Introduction lớn What-If Analysis.


Create either one-variable or two-variable data tables, depending on the number of variables & formulas that you need khổng lồ test.

One-variable data tables

Use a one-variable data table if you want to see how different values of one variable in one or more formulas will change the results of those formulas. For example, you can use a one-variable data table khổng lồ see how different interest rates affect a monthly mortgage payment by using the PMT function. You enter the variable values in one column or row, and the outcomes are displayed in an adjacent column or row.

In the following illustration, cell D2 contains the payment formula, =PMT(B3/12,B4,-B5), which refers khổng lồ the đầu vào cell B3.

Two-variable data tables

Use a two-variable data table to see how different values of two variables in one formula will change the results of that formula. For example, you can use a two-variable data table to lớn see how different combinations of interest rates and loan terms will affect a monthly mortgage payment.

In the following illustration, cell C2 contains the payment formula, =PMT(B3/12,B4,-B5), which uses two input cells, B3 and B4.

 

Data table calculations

Whenever a worksheet recalculates, any data tables will also recalculate—even if there has been no change to the data. Lớn speed up calculation of a worksheet that contains a data table, you can change the Calculation options lớn automatically recalculate the worksheet but not the data tables. To lớn learn more, see the section tốc độ up calculation in a worksheet that contains data tables.


A one-variable data table contain its input values either in a single column (column-oriented), or across a row (row-oriented). Any formula in a one-variable data table must refer to lớn only one input cell.

Follow these steps:

Type the list of values that you want lớn substitute in the đầu vào cell—either down one column or across one row. Leave a few empty rows and columns on either side of the values.

Do one of the following:

If the data table is column-oriented (your variable values are in a column), type the formula in the cell one row above & one cell khổng lồ the right of the column of values. This one-variable data table is column-oriented, and the formula is contained in cell D2.If you want lớn examine the effects of various values on other formulas, enter the additional formulas in cells to lớn the right of the first formula.

If the data table is row-oriented (your variable values are in a row), type the formula in the cell one column khổng lồ the left of the first value and one cell below the row of values.If you want to examine the effects of various values on other formulas, enter the additional formulas in cells below the first formula.

Select the range of cells that contains the formulas và values that you want to lớn substitute. In the figure above, this range is C2:D5.

On the Data tab, click What-If Analysis > Data Table (in the Data Tools group or Forecast group of Excel 2016).

Do one of the following:

If the data table is column-oriented, enter the cell reference for the input cell in the Column đầu vào cell field. In the figure above, the đầu vào cell is B3.

If the data table is row-oriented, enter the cell reference for the input cell in the Row đầu vào cell field.


Note: After you create your data table, you might want to lớn change the format of the result cells. In the figure, the result cells are formatted as currency.


Formulas that are used in a one-variable data table must refer to the same input cell.

Follow these steps

Do either of these:

If the data table is column-oriented, enter the new formula in a blank cell lớn the right of an existing formula in the đứng top row of the data table.

If the data table is row-oriented, enter the new formula in a empty cell below an existing formula in the first column of the data table.

Select the range of cells that contains the data table & the new formula.

On the Data tab, click What-If Analysis Data Table (in the Data Tools group or Forecast group of Excel 2016).

Xem thêm: Giải Bài Tập Trang 89, 92 Sgk Toán 8 Tập 2 Trang 92 Sgk Toán 8 Tập 2

Do either of the following:

If the data table is column-oriented, enter the cell reference for the input cell in the Column input cell box.

If the data table is row-oriented, enter the cell reference for the input đầu vào cell in the Row input đầu vào cell box.


A two-variable data table uses a formula that contains two lists of input đầu vào values. The formula must refer khổng lồ two different đầu vào cells.

Follow these steps:

In a cell on the worksheet, enter the formula that refers khổng lồ the two đầu vào cells.

In the following example—in which the formula starting values are entered in cells B3, B4, và B5, you type the formula =PMT(B3/12,B4,-B5) in cell C2.

Type one menu of input đầu vào values in the same column, below the formula.

In this case, type the different interest rates in cells C3, C4, & C5.

Enter the second danh sách in the same row as the formula—to its right.

Type the loan terms (in months) in cells D2 và E2.

Select the range of cells that contains the formula (C2), both the row và column of values (C3:C5 and D2:E2), và the cells in which you want the calculated values (D3:E5).

In this case, select the range C2:E5.

On the Data tab, in the Data Tools group or Forecast group (in Excel 2016), click What-If Analysis > Data Table (in the Data Tools group or Forecast group of Excel 2016).

In the Row input cell field, enter the reference to the đầu vào cell for the đầu vào values in the row.Type cell B4 in the Row input đầu vào cell box.

In the Column input cell field, enter the reference lớn the input đầu vào cell for the đầu vào values in the column.Type B3 in the Column input đầu vào cell box.

Click OK.

Example of a two-variable data table

A two-variable data table can show how different combinations of interest rates và loan terms will affect a monthly mortgage payment. In the figure here, cell C2 contains the payment formula, =PMT(B3/12,B4,-B5), which uses two input cells, B3 và B4.


You can use a few other Excel tools khổng lồ perform what-if analysis if you have specific goals or larger sets of variable data.

Goal Seek

If you know the result to lớn expect from a formula, but don"t know precisely what đầu vào value the formula needs to get that result, use the Goal-Seek feature. See the article Use Goal Seek to lớn find the result you want by adjusting an input đầu vào value.

Excel Solver

You can use the Excel Solver add-in to find the optimal value for a set of input variables. Solver works with a group of cells (called decision variables, or simply variable cells) that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells lớn satisfy the limits on constraint cells and produce the result you want for the objective cell. Learn more in this article: Define & solve a problem by using Solver.


By plugging different numbers into a cell, you can quickly come up with different answers lớn a problem. A great example is using the PMT function with different interest rates & loan periods (in months) lớn figure out how much of a loan you can afford for a trang chủ or a car. You enter your numbers into a range of cells called a data table.

Here, the data table is the range of cells B2:D8. You can change the value in B4, the loan amount, và the monthly payments in column D automatically update. Using a 3.75% interest rate, D2 returns a monthly payment of $1,042.01 using this formula: =PMT(C2/12,$B$3,$B$4).

*

You can use one or two variables, depending on the number of variables & formulas you want to test.

Use a one-variable thử nghiệm to see how different values of one variable in a formula will change the results. For example, you can change the interest rate for a monthly mortgage payment by using the PMT function. You enter the variable values (the interest rates) in one column or row, & the outcomes are displayed in a nearby column or row.

In this live workbook, cell D2 contains the payment formula =PMT(C2/12,$B$3,$B$4). Cell B3 is the variable cell, where you can plug in a different term length (number of monthly payment periods). In cell D2, the PMT function plugs in the interest rate 3.75%/12, 360 months, và a $225,000 loan, và calculates a $1,042.01 monthly payment.

Use a two-variable demo to see how different values of two variables in a formula will change the results. For example, you can demo different combinations of interest rates and number of monthly payment periods lớn calculate a mortgage payment.

Xem thêm: Liên Minh Châu Âu Tiết 2 : Eu, Địa Lý 11 Bài 7: Liên Minh Châu Âu: Eu

In this live workbook, cell C3 contains the payment formula, =PMT($B$3/12,$B$2,B4), which uses two variable cells, B2 and B3. In cell C2, the PMT function plugs in the interest rate 3.875%/12, 360 months, và a $225,000 loan, & calculates a $1,058.03 monthly payment.