Excel数据透视取决于标题值 [英] Excel Pivot depending upon Header values

问题描述

我正在尝试在excel中创建一个数据透视表，下面是我的场景。
我有一个excel表与5列，其中列1,2,3具有3个不同的人名（person1，person2，person3）作为标题，它的相应值将是是或否。第4列有数量，列5将为每个人分享。该份额根据前三栏中存在的值（是/否）计算，而我正在使用的公式是

 （D1 / COUNTIFS（A1：C1，是））
即除（金额/计数（有有价值的人））
 
 Person1 Person2 Person3金额PerHeadShare 
是是否200 100 
是是是300 100 
否是否200 200 
否否是100 100 
  

现在我的要求是创建一个包含两列的数据透视表

• column1应该有3行与Person1，Person2和Person3




• column2应该有每个人拥有的份额的总和

   名称Sum 
   Person1 200 
   Person2 400 
   Person3 200 
    

解决方案例

如果您有任何SQL经验，可以尝试以下方式：

数据标签>从Access>将文件过滤器更改为所有文件（*。*）并选择您的Excel文件>打开>确定>选择数据透视表报表，新建工作表，然后选择属性...>定义选项卡>命令类型：SQL和在命令文本中输入：

  SELECT名称，Sum（PerHeadShare）As [Sum] FROM（
 SELECT'Person1作为名称，Person1作为y，* FROM [Sheet1 $] UNION ALL 
 SELECT'Person2'As Name，Person2 AS y，* FROM [Sheet1 $] UNION ALL 
 SELECT'Person3'As Name， Person3 AS y，* FROM [Sheet1 $]）
 WHERE y ='是'
 GROUP BY名称
  

确定>确定>将名称字段拖动到行标签，将Sum字段拖动到值，然后重新命名数据透视表中的标题。

在命令文本中，Sheet1必须更改为数据所在的工作表的名称，后面带有$，或者命名为r ange $ code> [命名范围] ，或如 [Sheet1 $ B2：F6]

如果您有Excel 2010或以上版本，则可以使用Power Query或PowerPivot（我有Excel 2007，因此我无法尝试）更容易。 >

I am trying to create a Pivot table in excel and below is my scenario. I have an excel sheet with 5 columns where columns 1,2,3 is having 3 different persons names( person1, person2,person3) as header and it's corresponding values will be either 'yes' or 'No'. column 4 is having amounts and columns 5 will have the share for each person. The share is calculated depending on the value(yes/No) that was present in the 1st three columns and the formula I am using for this is

      (D1/COUNTIFS(A1:C1,"Yes")) 
      i.e., dividing the (amount/ count( of persons having Yes value))

 Person1    Person2 Person3 Amount  PerHeadShare
 Yes        Yes     No       200         100
 Yes        Yes     Yes      300         100
 No         Yes     No       200         200
 No         No      Yes      100         100

Now My requirement is to create a pivot table with two columns

• column1 should have 3 rows with Person1,Person2 and Person3

• column2 should have sum of the share of amount each person is having

  Name          Sum
  Person1       200
  Person2       400
  Person3       200
  

解决方案

If you have any experience with SQL, you can try something like this:

Data tab > From Access > change file filter to All Files (*.*) and select your Excel file > Open > OK > select PivotTable Report, New Worksheet, and then Properties... > Definition Tab > Command Type: SQL and enter this in Command Text:

SELECT Name, Sum( PerHeadShare ) As [Sum] FROM (
    SELECT 'Person1' As Name, Person1 AS y, * FROM [Sheet1$] UNION ALL 
    SELECT 'Person2' As Name, Person2 AS y, * FROM [Sheet1$] UNION ALL 
    SELECT 'Person3' As Name, Person3 AS y, * FROM [Sheet1$] ) 
WHERE y = 'Yes'
GROUP BY Name

OK > OK > Drag the Name field to Row Labels, drag the Sum field to Values, and then rename the headers in the PivotTable.

In the Command Text "Sheet1" has to be changed to the name of the sheet where the data is with a $ after it, or named range without $ like [named Range], or specific range in the sheet like [Sheet1$B2:F6]

If you have Excel 2010 or above, this can be a bit easier with Power Query or PowerPivot (I have Excel 2007 so I can't try it).

这篇关于Excel数据透视取决于标题值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！