Excel: как удалить пробелы в сводной таблице
Часто вам может понадобиться удалить пустые значения из сводной таблицы в Excel.
К счастью, это легко сделать с помощью кнопки « Параметры » на вкладке « Анализ сводной таблицы ».
В следующем примере показано, как именно это сделать.
Пример: удаление пробелов в сводной таблице Excel
Предположим, у нас есть следующий набор данных в Excel, который показывает количество продаж, совершенных двумя людьми в разные месяцы:
Теперь предположим, что мы создали следующую сводную таблицу, чтобы суммировать продажи, сделанные каждым человеком за каждый месяц:
Обратите внимание, что в сводной таблице есть несколько пустых значений, где у Энди или Берта не было продаж в определенные месяцы.
Чтобы заменить эти пробелы нулями, щелкните любую ячейку в сводной таблице.
Затем щелкните вкладку Анализ сводной таблицы на верхней ленте.
Затем нажмите кнопку « Параметры »:
В появившемся новом окне убедитесь, что установлен флажок « Для пустых ячеек показывать: », а затем введите ноль:
Как только вы нажмете OK , пустые ячейки в сводной таблице будут автоматически заменены нулями:
Обратите внимание, что вы можете заменить пустые значения любым значением, которое вы хотите.
Например, вместо этого вы можете ввести строку, такую как «Нет»:
Затем пустые значения в сводной таблице будут заменены этим значением:
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Управление пустыми строками, промежуточными итогами и другими настройками сводных таблиц Excel 2010
Дополнительные настройки сводных таблиц применяются для управления самыми разными ее элементами. Например, промежуточные итоги могут располагаться под или над группой исходных данных или вообще могут быть исключены из исходной таблицы.
Как уже упоминалось ранее, перемещение промежуточных сумм в верхнюю часть группы данных позволяет уменьшить общий размер сводной таблицы на несколько строк. Тем не менее сами промежуточные итоги выводятся только в случае предстаатения сводной таблицы в форме структуры или в сжатой форме. Настраиваются промежуточные суммы с помощью меню Промежуточные итоги (Subtotals) контекстной вкладки Конструктор (Design). На рис. 3.16 показано, как выглядят промежуточные итоги, добавленные над группами исходных данных.
Рис. 3.16. Промежуточные итоги над группами данных и пустые строки между ними добавляются с помощью раскрывающихся меню группы Макет контекстной вкладки Конструктор
Обшие итоги выводятся в нижней части каждого столбца и/или каждой строки; их также можно легко скрыть в сводной таблице. Настраиваются общие итоги с помощью раскрывающегося меню Общие итоги контекстной вкладки Конструктор. Некоторые команды этого раскрывающегося меню выглядят несколько странно.
Чтобы добавить общие итоги в правой части сводной таблицы, выберите команду Включить только для строк (On for Rows). Данная команда может также применяться к строке общих итогов, вычисляемых для всех столбцов. Подобным образом команда Включить только для столбцов (On for Columns) требует добавлять обшие итоги только под сводной таблицей. Все построчные общие итоги также подлежат суммированию в последнем столбце сводной таблицы.
Раскрывающееся меню Пустые строки (Blank Rows) позволяет добавлять пустые строки между группами данных. На рис. 3.17 пустая строка 13 добавлена с помощью команды Вставить пустую строку после каждого элемента (Insert Blank Line After Each Item) данного меню.
Рис. 3.17. В Excel 2010 заголовки полей часто мешают правильно проанализировать имеющиеся данные
После изучения сводной таблицы, показанной на рис. 3.16. вам может показаться странным, что столбец В шире столбцов С, D и Е. Причина заключается в том, что в ячейке ВЗ находится текст Названия столбцов. Надпись Названия строк, находящаяся в ячейке А4, приводит к чрезмерному расширению столбца А. Эти надписи появились в версии Excel 2007, и несмотря на то что разрабатывались они в надежде повысить удобство управления сводной таблицей, на практике они только мешают быстро и, главное, правильно анализировать данные.
Чтобы удалить мешающие записи, перейдите на контекстную вкладку ленты Параметры (Options) и в группе Показать или скрыть (Show/Hide) щелкните на кнопке Заголовки полей (Field Headers). В этой группе находятся кнопки для сокрытия или отображения кнопок свертывания (минус) и развертывания (плюс) полей, а также кнопка, с помощью которой можно скрыть список полей сводной таблицы. На рис. 3.17 показана описанная выше группа ленты и часть сводной таблицы со всеми тремя скрытыми элементами.
Как убрать пустые ячейки из сводной таблицы Excel 2010
Наличие пустых ячеек в сводной таблице раздражает многих пользователей. Причем существуют две категории пустых ячеек, от которых хотят избавиться многие пользователи. Во-первых, пустые ячейки могут отображаться в области значений в случае отсутствия определенных записей. Поскольку наличие пробелов раздражает многих пользователей, в подобных случаях лучше отображать нули.
Во-вторых, пустые ячейки могут также отображаться в области строк при наличии нескольких полей строк. Например, как показано на рис. 12.11, в левой колонке отображается название Мини-пекарни, ниже которого выводятся пустые ячейки. Для заполнения пустых ячеек можно воспользоваться новым свойством повторения подписей данных, которое появилось в Excel 2010 и о котором можно прочитать на filetypes.ru. Для замены пробелов нулями в области значений можно воспользоваться следующим кодом:
PT.NullString = "0"
Хотя в коде свойство принимает текстовый нуль, программа Excel помещает в пустые ячейки реальный числовой нуль.
Для заполнения пустых ячеек нулями в Excel 2010 используется следующий код:
РТ.RepeatAllLabels xlRepeatLabels
Код, основанный на использовании метода .RepeatAllLabels, не будет работать в Excel 2007 и в устаревших версиях Excel. Единственный выход заключается в преобразовании сводной таблицы в значения с последующим заполнением пустых ячеек с помощью формулы, которая берет значение из расположенной выше строки.
1 2 3 4 5 6 7 8 9 10 11 12
Dim FillRange As Range Set PT - ActiveSheet.PivotTables("PivotTable1") //Поиск внешнего столбца строки Set FillRange = PT.TableRange1.Resize(, 1) //Преобразование всей таблицы в значения PT.TableRange2.Сору PT.TableRange2.PasteSpecial slPasteValues //Заполнение пустых ячеек величинами из находящейся выше строки FillRange.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _ "=R[-1]C" //Преобразование формул в значения FillRange.Value = FillRange.Value
Dim FillRange As Range Set PT — ActiveSheet.PivotTables(«PivotTable1») //Поиск внешнего столбца строки Set FillRange = PT.TableRange1.Resize(, 1) //Преобразование всей таблицы в значения PT.TableRange2.Сору PT.TableRange2.PasteSpecial slPasteValues //Заполнение пустых ячеек величинами из находящейся выше строки FillRange.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _ «=R[-1]C» //Преобразование формул в значения FillRange.Value = FillRange.Value
Как удалить пустые строки в Excel быстрыми способами
При импорте и копировании таблиц в Excel могут формироваться пустые строки и ячейки. Они мешают работе, отвлекают.
Некоторые формулы могут работать некорректно. Использовать ряд инструментов в отношении не полностью заполненного диапазона невозможно. Научимся быстро удалять пустые ячейки в конце или середине таблицы. Будем использовать простые средства, доступные пользователю любого уровня.
Как в таблице Excel удалить пустые строки?
Чтобы показать на примере, как удалить лишние строки, для демонстрации порядка действий возьмем таблицу с условными данными:
Пример 1 . Сортировка данных в таблице. Выделяем всю таблицу. Открываем вкладку «Данные» — инструмент «Сортировка и фильтр» — нажимаем кнопку «Сортировка». Или щелкаем правой кнопкой мыши по выделенному диапазону и делаем сортировку «от минимального к максимальному».
Пустые строки после сортировки по возрастанию оказываются внизу диапазона.
Если порядок значений важен, то до сортировки необходимо вставить пустой столбец, сделать сквозную нумерацию. После сортировки и удаления пустых строк вновь отсортировать данные по вставленному столбцу с нумерацией.
Пример 2 . Фильтр. Диапазон должен быть отформатирован как таблица с заголовками. Выделяем «шапку». На вкладке «Данные» нажимаем кнопку «Фильтр» («Сортировка и фильтр»). Справа от названия каждого столбца появится стрелочка вниз. Нажимаем – открывается окно фильтрации. Снимаем выделение напротив имени «Пустые».
Таким же способом можно удалить пустые ячейки в строке Excel. Выбираем нужный столбец и фильтруем его данные.
Пример 3 . Выделение группы ячеек. Выделяем всю таблицу. В главном меню на вкладке «Редактирование» нажимаем кнопку «Найти и выделить». Выбираем инструмент «Выделение группы ячеек».
В открывшемся окне выбираем пункт «Пустые ячейки».
Программа отмечает пустые ячейки. На главной странице находим вкладку «Ячейки», нажимаем «Удалить».
Результат – заполненный диапазон «без пустот».
Внимание! После удаления часть ячеек перескакивает вверх – данные могут перепутаться. Поэтому для перекрывающихся диапазонов инструмент не подходит.
Полезный совет! Сочетание клавиш для удаления выделенной строки в Excel CTRL+«-». А для ее выделения можно нажать комбинацию горячих клавиш SHIFT+ПРОБЕЛ.
Как удалить повторяющиеся строки в Excel?
Чтобы удалить одинаковые строки в Excel, выделяем всю таблицу. Переходим на вкладку «Данные» — «Работа с данными» — «Удалить дубликаты».
В открывшемся окне выделяем те столбцы, в которых находятся повторяющиеся значения. Так как нужно удалять повторяющиеся строки, то должны быть выделены все столбцы.
После нажатия ОК Excel формирует мини-отчет вида:
Как удалить каждую вторую строку в Excel?
Проредить таблицу можно с помощью макроса. Например, такого:
А можно ручками. Предлагаем простенький способ, доступный каждому пользователю.
- В конце таблицы делаем вспомогательный столбец. Заполняем чередующимися данными. Например, «о у о у о у» и т.д. Вносим значения в первые четыре ячейки. Потом выделяем их. «Цепляем» за черный крестик в правом нижнем углу и копируем буквы до конца диапазона.
- Устанавливаем «Фильтр». Отфильтровываем последний столбец по значению «у».
- Выделяем все что осталось после фильтрации и удаляем.
- Убираем фильтр – останутся только ячейки с «о».
Вспомогательный столбец можно устранить и работать с «прореженной таблицей».
Как удалить скрытые строки в Excel?
Однажды пользователь скрыл некую информацию в строках, чтобы она не отвлекала от работы. Думал, что впоследствии данные еще понадобятся. Не понадобились – скрытые строки можно удалить: они влияют на формулы, мешают.
В тренировочной таблице скрыты ряды 5, 6, 7:
Будем их удалять.
- Переходим на «Файл»-«Сведения»-«Поиск проблем» — инструмент «Инспектор документов».
- В отрывшемся окне ставим галочку напротив «Скрытые строки и столбцы». Нажимаем «Проверить».
- Через несколько секунд программа отображает результат проверки.
- Нажимаем «Удалить все». На экране появится соответствующее уведомление.
В результате проделанной работы скрытые ячейки удалены, нумерация восстановлена.
Таким образом, убрать пустые, повторяющиеся или скрытые ячейки таблицы можно с помощью встроенного функционала программы Excel.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры