Excel Pivot depending on header values

Question

I am trying to create a pivot table in excel, and below is my script. I have an excel sheet with 5 columns, where columns 1,2,3 have the names of 3 different persons (person1, person2, person3) as the heading, and the corresponding values ​​will be either “yes” or “No”. column 4 has sums, and columns 5 will have a share for each person. The share is calculated depending on the value (yes / no) that was present in the first three columns, and the formula that I use 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 must have the amount of the share that each person has

  Name          Sum
  Person1       200
  Person2       400
  Person3       200
  

Answer 1

SQL, - :

"" > " " > All Files (*.*) Excel > > > , , ... > "" > " : SQL" :

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

> > "" " ", "" "" .

"1" , $ , $like [named Range] , [Sheet1$B2:F6]

Excel 2010 , Power Query PowerPivot ( Excel 2007, ).

Answer 2

, , . . -, , , .

• 3

• A8 :

• A7: C11

•