数据透视表和仪表板 - 最终用户可能需要以空数据集开头.... [英] Pivot Tables and Dashboards - End user needs to start with a empty Dataset possibly....

问题描述

我想为最终用户开发一个电子表格，他们必须将数据输入到我将使用预定义字段创建的工作表的数据源中。

I want to develop a spreadsheet for an end user(s) where they will have to enter their data into the data source on a sheet that I will create with predefined fields.

我还想为他们设置一个跟踪他们业务某些方面的仪表板。

I also want to have a dashboard set up for them that tracks certain aspects of their business.

但是我对数据透视表/仪表板的理解是为了使数据透视表能够在数据透视表可以显示数据之前，工作数据必须始终存在于表中。

However my understanding of pivot tables/dashboards is that in order for the pivot table to work data must always exist in the table before the pivot table can display the data.

由于最终用户不熟悉Excel并且知道如何创建数据透视表和仪表板，是否存在无论如何，我可以设置所有这些并部署一个空的数据源，这样当用户打开工作簿时，他们所要做的就是将数据输入
数据源表，然后为他们创建数据透视表，仪表板是否创建并显示在具有相应图形/切片器等的单独工作表上？

Since the end user will not be proficient with Excel and know how to create pivot tables and dashboards, is there anyway I could set all of this up and deploy an empty datasource so when the user opens the workbook all they have to do is enter data into the datasource sheet and then the pivot table(s) are created for them and the dashboard is created and displayed on a separate sheet with the appropriate graphs/slicers, etc?

如果这只能通过VBA完成，是否有任何示例可以po因此我可以修改它以满足我的需求吗？

If this can only be done through VBA, are there any examples you can point me to so I can then modify it to meet my needs?

或者，它是否只是简单地使用示例数据和表中的一行数据部署工作簿然后只是指示最终用户从该行开始并用他们的数据修改它？

Or, would it just make it easier to deploy the workbook with just one row of data in the table with example data and then just instruct the end user to start with that row and modify it with their data?

Keith

Keith Aul

Keith Aul

推荐答案

使用VBA，您可以使用您建议的两种方式之一。让用户输入数据，然后单击按钮，"显示结果"。等等。单击按钮时，它会启动构建数据透视表的VBA代码，并且（可能）创建一个显示结果的
新工作表。

With VBA you can do it either of the two ways you suggested. Let the user enter the data and then click on a button, "Display result" or the like. When clicking the button it starts VBA code that builds the pivot tables and (possibly) create a new sheet where the result is displayed.

如何组合此VBA代码是从你的描述中无法分辨，但这里有一些用于创建数据透视表的裸机VBA代码：

How this VBA code should be composed is impossible to tell from your description but here is some bare-bone VBA code to create a pivot table:

  Dim PivotSource As Range

  Dim PivotTablePos作为范围

  Dim pc As PivotCache

  Dim pt As PivotTable

  Dim pi As PivotItem

 Dim PivotSource As Range
 Dim PivotTablePos As Range
 Dim pc As PivotCache
 Dim pt As PivotTable
 Dim pi As PivotItem

 设置PivotSource =范围（"< your range>"）

  设置PivotTablePos =范围（"<您的数据透视表的位置>"）

 设置pc = ActiveWorkbook.PivotCaches.Create（xlDatabase，PivotSource）

 设置pt = pc.CreatePivotTable（PivotTablePos，"< your header>"）））
 使用pt

   .PivotCache.MissingItemsLimit = xlMissingItemsMax'允许字段丢失

    '*

    '*添加您想要的栏目
    '*

    .AddDataField pt.PivotFields（"< your column>"）），"< your column>"，xlCount  '与数据字段相同的名称

    .AddDataField pt.PivotFields（"< your column>"）），"Minimum"，xlMin

    .AddDataField pt.PivotFields（"< your column>"），" Maximum"，xlMax

    .CompactLayoutRowHeader ="< your label>" b
    .RefreshTable 

结束用于
 

  '...等......

  Set PivotSource = Range("<your range>")
  Set PivotTablePos = Range("<your position for the Pivot table>")
  Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, PivotSource)
  Set pt = pc.CreatePivotTable(PivotTablePos, "<your header>"))
  With pt
   .PivotCache.MissingItemsLimit = xlMissingItemsMax 'Allow fields to be missing
    '*
    '* Add the columns you want
    '*
    .AddDataField pt.PivotFields("<your column>")), "<your column>", xlCount  'Same name as the data field
    .AddDataField pt.PivotFields("<your column>")), "Minimum", xlMin
    .AddDataField pt.PivotFields("<your column>"), "Maximum", xlMax
    .CompactLayoutRowHeader = "<your label>"
    .RefreshTable 
End With
 
  '... etc ...

详细信息：

http://www.thespreadsheetguru.com/blog/2014/9/27/vba-guide-excel-pivot-tables

这篇关于数据透视表和仪表板 - 最终用户可能需要以空数据集开头....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！