A B C D E F G 2 3 MTH 4 ITEM SOLD Data 1 2 3 4 Grand Total 5 Model A Sum of QTY 249,048 203,897 282,726 49,324 784,995 6 Sum of RM CREDIT (1,323,990) (1,099,449) (1,521,920) (260,878) (4,206,237) 7 Model B Sum of QTY 162,610 235,906 252,330 159,215 810,061 8 Sum of RM-CREDIT (865,699) (1,249,328) (1,376,298) (849,610) (4,340,934) 9 Model C Sum of QTY 136,341 198,838 247,709 344,895 927,783 10 Sum of RM CREDIT (722,783) (1,077,941) (1,364,610) (1,779,477) (4,944,811) 11 Total Sum of QTY 547,999 638,641 782,765 553,434 2,522,839 12 Total Sum of RM-CREDIT (2,912,472) (3,426,718) (4.262.827) (2,889,964) (13,491,982) 13In the above sample, ITEM SOLD is a title in the underlying data area. What we see below it (Model A, Model B and Model C) are the unique items available under the title ITEM SOLD in the underlying data area. It appears on the left side of the report because we dragged the ITEM button into the ROW box (left side) in the Layout dialog in step 3 of the PivotTable Wizard.
If we click on the little arrowhead button on the right of ITEM SOLD, a list will appear together with the OK and CANCEL buttons. The list contains all the unique items available in the data area under column ITEM SOLD.
Automatically, all the unique items will appear in the report. We can select which unique item can appear or not appear in the report by marking or unmarking the box at the left of the unique item in the above list.
Click OK to get the computer accept our selection and dismiss the list. The report will be updated with the selection. Click Cancel to dismiss the list without effecting any changes to the report.
We can change the actual spelling of the unique items in the report without affecting the actual spelling in the underlying data.
In the above sample, MTH is also a title in the underlying data area. What we see in a row immediately below it (1, 2, 3, 4) are the unique items available in data under the title MTH in the underlying data area. It appears on the top side of the report because we dragged the MTH button into the COLUMN box (top side) in the Layout dialog in step 3 of the PivotTable Wizard.
240
Automatically, all the unique items will appear in the report. We can select which unique item can appear or not appear in the report by marking or unmarking the box at the left of the unique item in the above list.
Click OK to get the computer accept our selection and dismiss the list. The report will be updated with the selection. Click Cancel to dismiss the list without effecting any changes to the report.
We can change the actual spelling of the unique items in the report without affecting the actual spelling in the underlying data.
In the above sample, the word Data on the right of ITEM SOLD refers to the total or sum of numbers (quantity or values). Actually it is a bit misleading. The better word may be is Total, Sum or Count.
The repeated items below it are the items that are added up in the report. Sum of QTY means that values under the title QTY in the underlying data are summed. Sum of RM-CREDIT means that values under the title RM-CREDIT in the underlying data are summed.
Sum of QTY appears on the right side of ITEM SOLD but below MTH because we dragged the QTY button to the DATA box in the Layout dialog in step 3 of PivotTable Wizard.
Sum of RM-CREDIT appears on the right side of ITEM SOLD because we dragged the RM-CREDIT button to the DATA box but below ITEM SOLD button in the Layout dialog in step 3 of PivotTable Wizard. It appears below Sum of QTY in the report because we dragged the RM-CREDIT button to below the QTY button in the DATA box in the Layout dialog.
We can change the spelling of 'Sum of QTY' and 'Sum of 'RM-CREDIT' (in this case) without affecting the underlying data.
If we click on the little arrowhead button on the right of Data, a list will appear together with the OK and CANCEL buttons. The list contains all the unique items we dragged into the DATA box. We can select which unique item can appear or not appear in the report by marking or unmarking the box at the left of the unique item in the above list.
241
Click OK to get the computer accept our selection and dismiss the list. The report will be updated with the selection. Click Cancel to dismiss the list without effecting any changes to the report.
If we select only one item from the list, the Data portion of the report will disappear (only values will remain).
Sometimes, when we drag a button into DATA box, the default in the result is a count of items instead of a sum of the items.
If we want to find the sum (which is usually the case): Right-click on the report. A floating menu will appear. Select Wizard. We will get the step 3 of the PivotTable wizard. Click Layout to open the Layout dialog box.
Look at the buttons inside the DATA box. If you find the words Count of on a button and you want to change to Sum of. To do this, double-click on the relevant button. A list will appear. Under Summarize by, select Sum from the list. Click OK. You will return to the Layout dialog box.
Now notice that the word Count is replaced with Sum. Click OK. You will return to the step 3 of the PivotTable wizard. Click Finish. The report will be updated and the figure in the report is now a sum instead of a count.
No comments:
Post a Comment