excel间接函数，用于读取日期并返回动态值 [英] excel indirect function to read dates and return dynamic values

问题描述

浏览论坛，找不到我需要的东西。我在B列中有100多个具有唯一工作表名称和数据的工作表。B列将包含各种结束日期。某些工作表的结束日期为1或2，其他工作表的结束日期为30以上。 。我想创建一个包含该表的摘要页面，该表将进行自我更新以显示在B列中具有END日期的所有工作表名称在接下来的30天内到期。这需要编码吗？还是使用excel间接公式，也许在其周围包裹了一个vlookup？

looking through the forum and can't find what I need. I have 100+ sheets with unique sheet names and data in column B. Column B will contain various END DATES. some sheets will have 1 or 2 end dates, others have upwards of 30 end dates. . I would like to create a summary page containing a table that will update itself to show all sheet names that have END dates in column B expiring within the next 30 days. is this something that requires coding? or use of the excel indirect formula with maybe a vlookup wrapped around it?

推荐答案

为完整起见，这是使用以下方法的方法PowerQuery /获取和获取如果您拥有Excel 2013或更高版本，则进行转换。

For completeness, here's how you would do this using PowerQuery/Get & Transform if you had Excel 2013 or later.

首先，您可以通过选择工作表中的每个数据区域并将其选择为Excel表（aka ListObjects），或者使用功能区中的插入>表格命令，或仅使用键盘快捷键[Ctrl] + [T]。理想情况下，您应该给他们一个全名，以表示他们的名字。 （我使用了前缀 Input_，然后使用了运行计数，因为稍后，此句柄将帮助我忽略最终结果中不需要的任何表，只需查看其名称是否以 Input_）。

First, you would turn each of the data areas in your workbook into Excel Tables (aka ListObjects), by selecting them and either using the Insert>Table command from the ribbon, or simply by using the keyboard shortcut [Ctrl] + [T]. Ideally you would give them all a name that denotes what they are. (I've used the prefix "Input_" and then a running count, because later this 'handle' will help me to ignore any Tables that I don't want in my end result, simply by seeing if it's name is prefixed with "Input_").

然后从数据选项卡中，选择新建查询>从其他来源>空白查询：

Then from the Data tab, select New Query>From Other Sources>Blank Query:

一个看起来很神秘的用户界面，称为PowerQuery窗口，打开时不会引起太多兴趣。如果在公式栏中键入= Excel.CurrentWorkbook并按Enter，则将在该窗口中填充工作簿中各个表的列表，如下所示。而且，如果单击内容列右侧的双箭头图标，将出现一个菜单，可让您展开这些表以在每个表中包括实际的列，并将其带入PowerQuery窗口：

A mysterious looking UI called the PowerQuery window will open with nothing much of interest in it. If you type =Excel.CurrentWorkbook into the formula bar and press Enter, then a list of the various Tables in your workbook will populate in that window, as shown below. And if you click that twin arrow icon to the right of the Content column, a menu will appear that lets you expand those tables to include the actual columns in each of them, and bring them into the PowerQuery window:

这是按OK时数据转储的样子：

And here's how that data dump looks, when you push OK:

仅当您有您不想在最终混搭中显示工作簿中的其他表。在下面的屏幕快照中，我正在过滤结果集，使其仅包含具有Input_前缀的表：

This next bit is only required if you have other Tables in your workbook already that you don't want to show in your end mashup. In the screenshot below, I'm filtering the result set to only include tables with the Input_ prefix:

然后我要更改数据通过选择相关的列，然后从 Transform 选项卡中选择数据类型>日期，将该DateTime列的类型更改为简单的Date。

And then I'm going to change the data type of that DateTime column to a simple Date, by selecting the column concerned, and then choosing Data Type>Date from the Transform tab.

然后我要选择关闭并加载到从右上角的菜单中：

Then I'm going to select Close and Load To from the menu at top right:

..，然后从出现的对话框中选择仅创建连接和添加到数据模型选项：

..and then select the "Only Create Connection" and "Add to Data Model" options from the resulting dialog:

< a href = https://i.stack.imgur.com/4VkQH.png rel = nofollow noreferrer>

现在回到Excel中，我创建一个数据透视表，并选中使用此工作簿的数据模型选项：

Now back in Excel, I create a PivotTable, and leave the "Use this workbook's Data Model" option checked:

...这会带来一点不同看起来比通常看到的数据透视表字段列表要

...which brings up a slightly different looking PivotTable Fields List than you usually see:

我现在将名称（表名称）和结束日期字段添加到数据透视表中，并设置日期过滤器以仅显示接下来的30天：

I now add the Name (Table Name) and End Date fields to my Pivot, and set a date filter to just show dates within the next 30 days:

这是最终结果，数据透视表仅显示在接下来的30天内具有结束日期的那些表：

And here's the end result, a PivotTable that shows just those tables with end dates within the next 30 days:

此方法的优点是，如果以后添加带有更多输入表的更多选项卡，然后为它们提供前缀 Input_，则下次刷新数据透视表时它们会自动出现。而且，数据透视表并没有告诉您仅哪个标签具有适用的结束日期的报告，而是还告诉您涉及这些标签中的各个记录。

The beauty of this approach is that if you later add more tabs with more input tables, then provided you prefix them with "Input_" then they will automatically appear next time you refresh the PivotTable. And furthermore, instead of having a report that merely tells you which tabs have applicable End Dates, the PivotTable also tells you which individual records in those tabs are involved.

我会按照未显示的方式进行操作，例如通过删除内容为列提供更友好的名称。后缀，我可能会写一个简单的宏来根据当前日期起30天自动重新过滤数据透视表。但这至少向您显示了升级到Excel 2013并使用PowerPivot / Get和Transform根本上自动化诸如此类的任务的好处。

There's more stuff I would do along the way that I haven't shown, such as give the columns friendlier names by ditching the "Content." suffix, and I'd probably write a simple macro to automatically refilter the PivotTable based on 30 days from the current date. But this at least shows you the benefits of upgrading to Excel 2013 and using PowerPivot/Get and Transform to radically automate tasks such as this.

这篇关于excel间接函数，用于读取日期并返回动态值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！