不使用PowerPivot的Excel数据模型 [英] Excel Data Model without using PowerPivot

问题描述

我对Excel中的数据模型"有疑问.每当我了解此功能时，它就会与PowerPivot一起使用.

I have a question about the "Data Model" in excel. Whenever I read about this function, it is used with PowerPivot.

我问是因为我想做这样的事情:

I ask because I would like to do something like this:

我有表A :

• ID
• info1

和表B :

• ID
• info2

现在，如果我将这些表与数据模型功能(通过ID列)连接在一起，我以为可以将tableB.info2连接到tableA，并有一个显示ID,info1,info2

Now if I connect these tables with with the data model function (through the ID-Column), I thought that I could then join tableB.info2 to tableA and have a table that shows ID,info1,info2

但是这似乎是不可能的，还是有可能，我做错了什么?

But that doesn't seem to be possible, or is it possible and I'm doing something wrong?

在不使用PowerPivot的情况下，您可以对数据模型做任何事情吗?我觉得我错过了此功能的重点.

Is there ANYTHING you can do with datamodels without using PowerPivot? I feel like I'm missing the point of this feature.

推荐答案

PowerPivot是一种高级工具，可以消除表格中的麻烦.它为您节省了大量时间.

PowerPivot is a highly advanced tool that takes the hassle out of tables. It saves you a whole lot of time.

数据模型"是PowerPivot，PivotTables和PowerView的核心"，不能与这三个工具分开使用.

The "Data Model" is at the "core" of PowerPivot, PivotTables, and PowerView, and cannot be used apart from either of these three tools.

您所谈论的内容可以实现，但最好在VLOOKUP或INDEX(MATCH())中处理，

What you're talking about can be achieved, but is best handled in a VLOOKUP or INDEX(MATCH()),

但是我们只能说我们想走那条路，因为老实说我有时候会这么做.

But let's just say we want to go down that route, because honestly I do sometimes.

首先

在表1和表2之间的数据模型中查找ID值，箭头流向仅具有info1的表1.

Hook up your ID values in your data model between table 1 and table 2, the arrow flowing towards table 1 that only has info1.

在表1中创建一个计算列，并为其指定公式

Create a calculated column in table1, give it the formula

=RELATED(table2[info2])

命名您刚刚添加的新列相关信息2"

Name that new column you just added "related info2"

您的价值神奇地增长.

但是如何将其重新输入到excel工作簿中?这根本不是一个干净"的解决方案，这就是为什么我们建议使用VLOOKUP或MATCH(INDEX())

But how do you get it back into your excel workbook? It's not at all a "clean" solution, which is why we recommend VLOOKUP or MATCH(INDEX())

要将"info2"带入excel工作表，您需要做的是使用平移数据透视表".

What you'll want to do to bring your info2 into your excel sheet is use a "Flattened PivotTable".

您可以通过按数据透视表"按钮在PowerPivot编辑器中创建这种类型的数据透视表.

You can create this type of PivotTable in your PowerPivot Editor, by pressing the "PivotTable" button.

将Flattened PivotTable放在Excel工作表中您想要info2放置的另一个表的旁边，就像您将要神奇地在其旁边填充info2列一样.

Put your Flattened PivotTable right beside the other table in your Excel worksheet that you want info2 at, like you're magically going to populate the info2 column right beside it, because you're about to.

将所有包含数据列的表中的所有数据透视表字段拖放到行"框中，包括我们刚刚在表1中创建的相关info2"列.

Drag and drop all PivotTable fields from the table that contains our calculated column to the "rows" box, including that "related info2" column we just created in table1.

要在正确的行上获取正确的值，请进入设计"选项卡，然后单击小计"按钮，禁用所有小计，并使用总计"按钮执行相同操作，禁用所有总计.

To get the right values on the right rows, go into your Design tab, and click on the "Subtotals" button, disable all subtotal, and do the same with the "Grand Totals" button, disable all grand totals.

确保报告布局"为紧凑"，也可以在设计"标签中找到.

Make sure your "Report Layout" is "Compact", also found in the Design tab.

Optional: You can add a field to "values" to allow row formatting to recognize the rows, otherwise the PivotTable thinks that there are no rows and you won't get anything but a mostly solid color, no matter how many designs you try to switch to (we're doing something tricky, remember?)

现在隐藏您刚刚使用数据透视表创建的excel工作表中的所有列，但不包括相关info2"列.

Now hide all the columns in your excel sheet you just created with the PivotTable except your "related info2" column.

是的，可以这样做，但是最好使用VLOOKUP或INDEX(MATCH())

So yes, it is possible to do, but you're better off with a VLOOKUP or INDEX(MATCH())

这篇关于不使用PowerPivot的Excel数据模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！