Как убрать blank в конце таблицы excel pivot


Как убрать blank в конце таблицы excel pivot

Потом попробовала такой вариант, показал "-"
pole: IIf(IsNull([pole]),’-‘,[pole])

А на "пробел" опять (Blank) появился.
pole: IIf(IsNull([pole]),’ ‘,[pole])

На крайний случай могу использовать тире, но было бы интересно узнать, можно ли получить визуально пустую ячейку?

В общих чертах, просто запросы.

Создала "Запрос 1", где использовала поля из нескольких таблиц.

В "Запросе 2" использовала поля "Запроса 1" и пару остальных таблиц.

Получилось, что в сводной таблице для "Запросе 2" в полях появилось значение (Blank).

вот собственно как отображается

Серьезно? . )
Тогда пришлите мне свой монитор .

В виде экспорта в Excel получилось спрятать (Blank) . это также мне понадобится.

Для простого отображения в форме и редактирования — это уже есть.

Серьезно? . )
Тогда пришлите мне свой монитор .

хм, ну вот сводная таблица, а монитор уже другой:

Сейчас вспомнила, в другой базе у меня тоже была использована сводная таблица, сейчас посмотрела и там всё нормально, пустые поля видны как пустые.


Это не подействовало в Excel. Для Excel подошел вариант Akina.

Removing blank Excel Pivot Table entries

I have a pivot table based on PowerPivot with multiple levels in the Rows section. Quite a few of the elements are blank. Is there a way to suppress the blank rows?

My Pivot Table looks like this at the moment.

This is what I want it to look like

That’s just an example. I’ve tried to set filters for each level not to display blanks but if I filter each level to hide blank fields the pivot table doesn’t have any items in it. It seems that Excel ends up filtering each level out that has ANY blank values.

Not sure if I made sense or provided enough information for troubleshooting. I’m pretty much brain dead at the moment so I apologize.

Please let me know if it doesn’t make sense or if I can provide any additional information to clarify what I’m trying to do and encountering.

EDIT: Changed the code block to be more clear and added an "after" code block to show what I want to get to. I guess the issue is that the "depth" of the rows for the entire pivot table has to be equal. For example if I have 3 indents for the first item, the others must also show 3 levels of indent worth of data. If I hide blanks and that results in 1 indent worth of data for the first item, it will hide non-blanks for other items as well if they appear after 1 indent. Still not sure if that makes any sense 🙂 I need some sleep.

5 Answers 5

I think you should be able to check Show items with no data on rows and/or Show items with no data on columns in Display under PivotTable Options.

I ended up using a Flattened PivotTable instead from the PowerPivot "Manage" screen instead. That seemed to do what I needed.

I needed to solve exactly the same problem, but wanted the the rows to be hidden automatically when the pivottable filtering was changed or the data was refreshed, so I wrote a function called from a ‘Worksheet_PivotTableUpdate’ event on the sheet holding the pivottable, although you could drive it off a button if you prefer I suppose. This initially did the job by scanning down each row in the pivottable, hiding it if it was visible and didn’t need to be (as the row’s first cell was ‘(blank)’), or showing it if it was hidden and shouldn’t be, otherwise just leaving it hidden or visible as it was.

However, I found the function ran very slowly, so here’s my next attempt, which initially un-hides all the rows in the pivottable, then goes and finds all the blank cells in the first column, adding them to a range object, then it hides the entire row for every cell in that range. This version works about ten times as fast 🙂

Pass in the name of the sheet where the pivottable reside, the name of the pivottable and an optional string to look for in the rows that need to be hidden; this defaults to ‘(blank)’ if not supplied by the calling function.

Apologies in advance if I fall foul of anybody’s variable or function naming conventions (I’m only a hacker ;-))

I looked for a way to select all of the ‘blanks’ cells in the first column using one of the Go To Special type functions, but couldn’t find anything that would fit the bill, so please let me know if you know how to do such a thing as it will speed this up even more.


Ссылка на основную публикацию