Dont display Pivot Table values less than 0
-
2012년 6월 10일 일요일 오후 2:24
I have multiple records in my source data that add correctly when in the Pivot Table. But - I want the pivot table to only show a value if it is greater than 0 not less than 0.
Source would show rows of:
Col A
1
2
-4
Like i said the data in Col A calculates correct but the sum is -2. I don't want to display the - numbers in the table.
모든 응답
-
2012년 6월 11일 월요일 오후 3:00중재자
Hi,
I have an idea for this issue:
- Add a column into the table, suppose it is named as “sum_null”
- Each cell of this column has a formula, such as: =IF(SUM(B2:B4)<0,"null",B2)
This formula means that if the sum of the data is less than 0, the cell will show “null”.
3. Use a macro to clear the cells which the content is null.
Here’s the code:
Sub clear_null()
Dim row_count As Integer
Dim loop_num As Integer
row_count = ActiveSheet.UsedRange.Count
For loop_num = 2 To row_count
If Cells(loop_num, 3) = "null" Then
Cells(loop_num, 3).Clear
End If
Next
End Sub
4. Run the clear_null macro
5. Insert the Pivot Table
6. Choose the “Sum of sum_null” field, right click-> PivotTable Options…->Layout&Format ->check “For Empty cells show:” then set the empty cells show “”.
Jaynet Zhang
TechNet Community Support
- 답변으로 표시됨 Jaynet ZhangMicrosoft Contingent Staff, Moderator 2012년 6월 15일 금요일 오전 1:17
-
2012년 6월 11일 월요일 오후 3:14For that you should tell us how many field is in Value area and how many in Row/Column Area.
.png)
