数据透视表中包含值字段更改的变量表？ [英] Pivot Tables that have variables that change for the Values field?

问题描述

（我在这个问题前面加上一个警告，我知道这些并发症似乎更多地基于正在做什么而不是做错了什么，但我对什么有严格的限制我可以用来获取所需数据的工具）

(I preface this issue with a warning that I'm aware of the complications this seems to be more based on what is being done where rather than what is being done wrong, but I have strict limitations as to what tools I can use to get the data needed)

我遇到了很多问题，有点像这样：

I have quite a few issues that I'm running into, and it sort of goes like this:

1）我在SharePoint（2007）中有一个Excel工作表库，它使用元数据向我的老板显示信息。我正在做的是使用这个库的导出功能在excel中创建一个表（它自动为我做这个），通过宏更新
数据并使用数据透视表显示这些数据然后可以过滤因此，一切都很好。如下所述，这个问题是这个数据是基于每个Excel工作表中找到的数据的总和（所有工作表中的
类似，但是包含每行数据的总和） ，所以数据只是数据的总和）

1) I have a library of excel sheets in SharePoint (2007) that are utilizing Metadata to display information to my boss. What I am doing is using the export feature of this Library to create a table in excel (it does this for me automatically), updating the data via a macro and using pivot tables to display this data which can then be filtered accordingly and all is well. The problem with this, as will be mentioned below, is that this data is based off a summation of the data found within each excel sheet (which is similar across all sheets, but contains data per-row which is the summed, so the data is simply a summation of data)

2）我的老板告诉我，他正在寻找的信息需要进一步过滤，我有2列（对于这个例子） ，我只会使用有数据的A列和B列。这两列都是数据验证，可能是
列A中的10个项目之一，B列为4列。这意味着他希望能够在所有excel电子表格中查看所有实例。 A列的选项6由B列的选项2过滤。 

2) My boss has informed me that the information he is looking for needs to be filtered further, I have 2 columns (for this example, I'll simply use Column A and Column B) that have data. Both columns are Data Validated to be one of a possible 10 items for Column A and 4 for Column B. What this means is that he wants to be able to see, across all excel spreadsheets, all instances of say.. Option 6 of Column A filtered by Option 2 of Column B. 

鉴于此，最初我只是总结说，A列的所有结果都进入了sharepoint和所有结果B列，然后给出这些结果的总和，但是现在我们正在考虑计算A到B的实例，然后
对此进行求和，然后在所有计划中再次对此进行求和。

Given this, originally I was simply summing say, all of the outcomes of column A into sharepoint and all of the outcomes of Column B, and then giving a summation of these results, however now we're looking into calculating the instances of A to B, and then summing this, and then summing this again across all plans.

所以我正在做的是一个嵌套的Select Case语句来完成这个，看起来像这样：

So what I'm doing is a nested Select Case statement to accomplish this, which looks something like:

Select Case (ColumnA.Value)	
   Case(1)
      variableA
   Case(2)
      variableB
   Case(3)
      VariableC
   Case(4)
      variableD
   Case(9)
      variableI
   Case Else
      variableJ
End Select

等。

然后成为

Select Case (ColumnA.Value)

案例（1）

VariableA

VariableA

选择案例（ColumnB.Value）

Select Case (ColumnB.Value)

案例（1）

VarabileA1

VarabileA1

案例（2）

VariableA2

VariableA2

结束选择

案例（2）

<温泉n class ="x_Apple-tab-span"style ="white-space：pre"> 选择Case（ColumnB.Value）

Select Case (ColumnB.Value)

案例（1）

VariableB1

VariableB1

案例（2）

VariableB2

VariableB2

你好意思明白这点。这将在技术上给出每个A实例的所有实例的总和与B的每个实例的关系

You hoepfully get the point. This will technically give me the sum of all instance for each instance of A in relation to each instance of B

问题1：这是40个变量，有更有效的方法吗？我确定可能有一些数组（或数组数组？）但是...逻辑不是来找我

Problem 1: That's 40 Variables, is there a more efficient way to do this? I'm sure there probably is via some array (or array of arrays?) but.. the logic just isn't coming to me

问题2：计算完所有这些并找到一种方法将其轻松上传到sharepoint（存储在一个字符串中，然后在导出后，分离回数字变量），我遇到了一个可以使用不同的数据透视表的问题根据某些过滤器计算
。例如：

Problem 2: After calculating all this out and finding a way to upload it to sharepoint easily (storing in a string and then after exporting, separating back out into number variables), I run into the problem of a pivot table that can use different values based on certain filters. For example:

如果我进行设置以便为A列设置报告过滤器，那么我应该在该下拉列表中有10个项目。但是什么会被视为价值？我想A的实例总和。如果我然后为B列添加另一个过滤器，并从那里选择一个项目，
我不知道该值是什么。逻辑会告诉我，根据上面的代码，如果我选择过滤ColumnA（1）并过滤ColumnB（2），那么我希望Values字段是变量VariableA2的总和，但如果我然后更改ColumnB的过滤器
为1，我需要将value字段更改为variableA1。有没有办法在没有手动编辑实际数据透视表的情况下即时执行此操作？

If I set it up so that the Report Filter will be set for Column A, I should have 10 items in that dropdown. But what would be put as the Value? I suppose the sum of the instances of A. If I then add another filter for Column B, and select an item from there, I don't know what the value would be. Logic would tell me that, given the code above, If i select filter ColumnA(1) and filter ColumnB(2), then I would want the Values field to be the summation of variable VariableA2, but if i then change the filter for ColumnB to 1, i would need to change the value field to be variableA1. Is there a way to do this on the fly without manually editing the actual pivot table?

我想我完全在考虑这个逻辑错误:(，更糟糕的是，技术上有3列我需要过滤结果（但我知道如果我能弄清楚2我可以很容易地把它带到3）

I think i'm thinking of this logic completely wrong :(, and worse yet, there's technically 3 columns that i need to filter results by (but I know that if i can figure out 2 i can take it to 3 rather easily)

推荐答案

退一步之后，我意识到我所寻找的数据在限制条件下根本无法实现。无法将逐行数据放入元数据中，除非它是所述值的总和或计数，并且通过求和过滤将无效。仍然
感兴趣是否可以根据过滤器项更改数据透视表值选择，我的猜测是可以通过VBA，但至于总体结论，我将不得不尝试提出一些获取行数据的方法。

After taking a step back, I have realized that the data I'm looking for is simply not possible under the constraints. You cannot get row-by-row data into metadata unless it's a summation or count of said values, and filtering by a sum will do no good. Still interested as to if you can change pivot table values based on the filter item selected, my guess is it's possible via VBA, but as for an overall conclusion, I'm just going to have to try and come up with some way of getting row data around.

编辑： 

是否可以创建数据透视表中的虚拟过滤器，例如固定的项目列表，然后根据选择的项目，将值字段更改为适当的变量总和？

Would it be possible to create a dummy filter in a pivot table, with say, a fixed list of items, and then depending on what item is selected change the Values Field to an appropriate summation of varaibles?

例如：

如果我创建了一个下拉列表：

If I create a dropdown that would have:

A栏（1） - B栏（1）

Column A (1) - Column B (1)

A栏（1） - B栏（2）

Column A (1) - Column B (2)

然后，如果选择其中一个，它会交换VariableA1和VariableA2总和之间的值？

Then if one of these were selected, it would swap the Values Filed between the summation of VariableA1 and VariableA2?

这篇关于数据透视表中包含值字段更改的变量表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！