如何在 Excel 2007 中添加代表不同工作表名称的字符串下拉列表？
我最近被安排参加一个工作报告项目。我的总体目标是制作一份包含 9 个不同图表的报告，能够按日期和组筛选数据。有 22 个不同的组，我需要向下钻取以按单独的月份、星期和日期显示报告。我必须在 excel 2007 中完成这一切。目前，我正在努力按月显示所有不同应用程序组的结果以及所有组的摘要。
我设置的方式是，拥有一本包含 2 个工作表的工作簿，其中 1 个包含所有 9 个图表，我们将其称为 Dashboard_All。这些图表链接到第二个工作表，其中包含每个图表的所有信息，我们称之为 Chart_Data。
您可能想知道，这些图表数据来自哪里？ Chart_Data 引用另一个工作簿中包含原始数据、数据透视表和矩阵表的一些表。我已将其设置为此处的一张工作表包含一张表中的所有原始数据。一个工作表包含所有数据透视表（每个图表 1 个数据透视表），以便筛选原始数据并给出结果。我制作了 23 个不同的工作表，其中包含这些结果，每个组 1 个（我在每个数据透视表上使用组过滤器来执行此操作），所有组 1 个作为摘要。我还制作了 23 个不同的工作表，其中为每个数据透视表结果制作了矩阵表。使用这些表是因为每当我更改不同组的数据透视表时，结果都会发生变化，有时如果没有数据可供读取，则列或行会被省略。因此，我制作了引用数据透视表的表格，以便让单元格基本上充当间隔符，以防数据透视表中存在空白值。这些图表将不同月份的所有可能组合作为行和不同列。上一个工作簿中的 Chart_data 引用了这些矩阵表。
我在 Chart_Data 中使用了链接到下拉列表的 VLOOKUP 函数。例如， 请选择一个月：下拉 一月 二月 三月 ... 所有月份。 Vlookup 知道，当我选择一个月时，仅显示该月份图表的各种数据。这样我的每月目标就完成了。现在对于应用程序组，我需要另一个下拉框来引用我的工作簿中的不同矩阵表工作表。如果我从下拉列表中选择一个组，VLOOKUP 将知道使用适当的信息转到相应的工作表。
问题是，如何让它识别不同的纸张？例如，
=VLOOKUP(C3, '[BlahBlah_WaWa_Charts.xlsx]Group 1 Chart Data'!$A$20:$B$32, 2)
如何才能使“组 1”随着下拉选择而变化？
我知道这很冗长且令人困惑，但我不知道还能如何解释。 多谢。
I have recently been placed on a reporting project for my job. My overall objective is to make a report of 9 different charts that is able to filter the data by date and also by group. There are 22 different groups and I need to drill down to show the report by individual month, week, and day. I have to do this all in excel 2007. For now, I'm working on showing results by month and for all the different applications groups plus a summary for all groups.
The way that I have set this up is, to have one workbook that contains 2 worksheets, 1 that has all 9 graphs, let's call this Dashboard_All. These graphs are linked to the second sheet that contains all of the information for each graph, let's call this Chart_Data.
You may be wondering, where this Chart Data is coming from? Chart_Data references some tables in another workbook that contains raw data, pivot tables, and matrix tables. I have it set up to that one worksheet here contains all the raw data in one table. One worksheet has all the pivot tables (1 pivot table for each chart) in order to sift through the raw data and give me the results. I have made 23 different worksheet that have these results, 1 for each group (I used the group filter on each pivot table to do this) and 1 for all groups as a summary. I also made 23 different worksheets where I have made matrix tables for each of the pivot tables results. These tables are used because when ever I change a pivot table for a different group, the results change and sometimes columns or rows are omitted if there is no data there to be read. So I made tables that reference the pivot tables in order to have cells that basically act as spacers in case there are blank values in the pivot tables. These charts have all the possible combinations for the different months as rows and the different columns. Chart_data from the previous workbook references these matrix tables.
I used a VLOOKUP function in Chart_Data that is linked to a drop down list. For example,
Please, choose a month: drop down January February March ... All Months. The Vlookup knows that when I select a month to only display the various data for the charts only for that respective month. So my monthly objective has been accomplished. Now for the application group, I need another drop down box that will reference the different matrix table worksheets in my workbook. If I choose a group from the drop down, the VLOOKUP will know to go to that respective sheet with the appropriate information.
The problem is, how do I get it to recognize different sheets? For example,
=VLOOKUP(C3, '[BlahBlah_WaWa_Charts.xlsx]Group 1 Chart Data'!$A$20:$B$32, 2)
How do I have it so that "Group 1" changes with the drop down selection?
I know this is long winded and confusing, but I'm not sure how else to explain this.
Thanks alot.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以使用 INDIRECT() 例如；
You can use INDIRECT() Eg;
根本不清楚您想要做什么或内容的结构如何。
您是为 22 个组中的每组制作一组图表，还是为所有 22 个组制作一组图表？
这些图表是在一本独立工作簿中还是在 22 个小组工作簿中？
您是否尝试从独立工作簿或组工作簿中检索工作簿名称？
...等等...
请尝试重写您的问题/解释，以阐明您的数据的结构以及您的目标是什么。
It isn't clear at all what you're trying to do or how your content is structured.
Are you making one set of charts for each of the 22 groups, or one set of charts for ALL 22 groups?
Are the charts in one stand-alone workbook or in each of the 22 group workbooks?
Are you attempting to retrieve the workbook names from the stand-alone workbook or within the group workbooks?
...etc...
Please try re-writing your question/explanation to clarify how your data is structured and what your objectives are.