Как убрать пустые строки в сводной таблице excel

Как удалить пустые строки в Excel быстрыми способами

При импорте и копировании таблиц в Excel могут формироваться пустые строки и ячейки. Они мешают работе, отвлекают.

Некоторые формулы могут работать некорректно. Использовать ряд инструментов в отношении не полностью заполненного диапазона невозможно. Научимся быстро удалять пустые ячейки в конце или середине таблицы. Будем использовать простые средства, доступные пользователю любого уровня.

Как в таблице Excel удалить пустые строки?

Чтобы показать на примере, как удалить лишние строки, для демонстрации порядка действий возьмем таблицу с условными данными:

Пример 1 . Сортировка данных в таблице. Выделяем всю таблицу. Открываем вкладку «Данные» — инструмент «Сортировка и фильтр» — нажимаем кнопку «Сортировка». Или щелкаем правой кнопкой мыши по выделенному диапазону и делаем сортировку «от минимального к максимальному».

Пустые строки после сортировки по возрастанию оказываются внизу диапазона.

Если порядок значений важен, то до сортировки необходимо вставить пустой столбец, сделать сквозную нумерацию. После сортировки и удаления пустых строк вновь отсортировать данные по вставленному столбцу с нумерацией.

Пример 2 . Фильтр. Диапазон должен быть отформатирован как таблица с заголовками. Выделяем «шапку». На вкладке «Данные» нажимаем кнопку «Фильтр» («Сортировка и фильтр»). Справа от названия каждого столбца появится стрелочка вниз. Нажимаем – открывается окно фильтрации. Снимаем выделение напротив имени «Пустые».

Таким же способом можно удалить пустые ячейки в строке Excel. Выбираем нужный столбец и фильтруем его данные.

Пример 3 . Выделение группы ячеек. Выделяем всю таблицу. В главном меню на вкладке «Редактирование» нажимаем кнопку «Найти и выделить». Выбираем инструмент «Выделение группы ячеек».

В открывшемся окне выбираем пункт «Пустые ячейки».

Программа отмечает пустые ячейки. На главной странице находим вкладку «Ячейки», нажимаем «Удалить».

Результат – заполненный диапазон «без пустот».

Внимание! После удаления часть ячеек перескакивает вверх – данные могут перепутаться. Поэтому для перекрывающихся диапазонов инструмент не подходит.

Полезный совет! Сочетание клавиш для удаления выделенной строки в Excel CTRL+«-». А для ее выделения можно нажать комбинацию горячих клавиш SHIFT+ПРОБЕЛ.

Как удалить повторяющиеся строки в Excel?

Чтобы удалить одинаковые строки в Excel, выделяем всю таблицу. Переходим на вкладку «Данные» — «Работа с данными» — «Удалить дубликаты».

В открывшемся окне выделяем те столбцы, в которых находятся повторяющиеся значения. Так как нужно удалять повторяющиеся строки, то должны быть выделены все столбцы.

После нажатия ОК Excel формирует мини-отчет вида:

Как удалить каждую вторую строку в Excel?

Проредить таблицу можно с помощью макроса. Например, такого:

А можно ручками. Предлагаем простенький способ, доступный каждому пользователю.

1. В конце таблицы делаем вспомогательный столбец. Заполняем чередующимися данными. Например, «о у о у о у» и т.д. Вносим значения в первые четыре ячейки. Потом выделяем их. «Цепляем» за черный крестик в правом нижнем углу и копируем буквы до конца диапазона.
2. Устанавливаем «Фильтр». Отфильтровываем последний столбец по значению «у».
3. Выделяем все что осталось после фильтрации и удаляем.
4. Убираем фильтр – останутся только ячейки с «о».

Вспомогательный столбец можно устранить и работать с «прореженной таблицей».

Как удалить скрытые строки в Excel?

Однажды пользователь скрыл некую информацию в строках, чтобы она не отвлекала от работы. Думал, что впоследствии данные еще понадобятся. Не понадобились – скрытые строки можно удалить: они влияют на формулы, мешают.

В тренировочной таблице скрыты ряды 5, 6, 7:

Будем их удалять.

1. Переходим на «Файл»-«Сведения»-«Поиск проблем» — инструмент «Инспектор документов».
2. В отрывшемся окне ставим галочку напротив «Скрытые строки и столбцы». Нажимаем «Проверить».
3. Через несколько секунд программа отображает результат проверки.
4. Нажимаем «Удалить все». На экране появится соответствующее уведомление.

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

Таким образом, убрать пустые, повторяющиеся или скрытые ячейки таблицы можно с помощью встроенного функционала программы Excel.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Excel: как удалить пробелы в сводной таблице

Часто вам может понадобиться удалить пустые значения из сводной таблицы в Excel.

К счастью, это легко сделать с помощью кнопки « Параметры » на вкладке « Анализ сводной таблицы ».

В следующем примере показано, как именно это сделать.

Пример: удаление пробелов в сводной таблице Excel

Предположим, у нас есть следующий набор данных в Excel, который показывает количество продаж, совершенных двумя людьми в разные месяцы:

Теперь предположим, что мы создали следующую сводную таблицу, чтобы суммировать продажи, сделанные каждым человеком за каждый месяц:

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

Чтобы заменить эти пробелы нулями, щелкните любую ячейку в сводной таблице.

Затем щелкните вкладку Анализ сводной таблицы на верхней ленте.

Затем нажмите кнопку « Параметры »:

В появившемся новом окне убедитесь, что установлен флажок « Для пустых ячеек показывать: », а затем введите ноль:

Как только вы нажмете OK , пустые ячейки в сводной таблице будут автоматически заменены нулями:

Обратите внимание, что вы можете заменить пустые значения любым значением, которое вы хотите.

Например, вместо этого вы можете ввести строку, такую как «Нет»:

Затем пустые значения в сводной таблице будут заменены этим значением:

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

Как убрать пустые строки в сводной таблице excel

Доброго дня Ексельчани! Проблема следующая: Заполняю таблицу, другая таблица берет из заполняемой данные и делает свои вычисления, но она безразмерная, чтобы не тянуть все время формулу и делаю по этой таблице сводную. Т.к. в вычисляемой есть пустые ячейки и равные 0 в сводной соответственно они и появляются .
Вопрос как от них избавиться? Пример прикладываю.
ЗЫ: с помощью фильтра нельзя сделать так как постоянно меняются вводные данные и они не включаются.
ЗЫ2: хочу сделать так, чтобы вводить только вводные данные а все остальное защитить от изменений — буду пользоваться не я, а 100 лет бабушки. «Кто знает-тот поймет;)»

Не смогу добавить файл .xlsx ((( «Превышен максимальный размер загружаемого файла (100 КБ).» Это новые правила на сайте или я чтото не так делаю??

Изменено: Александр Белов — 03.03.2016 09:32:34
Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
03.03.2016 09:45:13

1. Нули. Файл-Параметры-Дополнительно-Показать параметры для следующего листа. -Снять галку с пункта «Показывать нули для. «
2. Пустые ячейки. ПКМ в любом месте Сводной — Параметры Сводной таблицы. -Разметка и формат — Для пустых ячеек отображать.

Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
Пользователь
Сообщений: 55 Регистрация: 01.01.1970
03.03.2016 09:57:46

Спасибо, Sanja ! 1) — прошел на ура
2) не совсем прошел. Так как в вычисляемой таблице есть формулы которые как бы равны 0 — в сводной они появляются в фильтре как значение «пустое» и он ее как бы считать пытается и показывает по ней итоги. Если ее отключить в фильтре то она исчезает, но при этом и фильтр не меняется если добавлять новые данные — как бы замораживается выбор.

Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
03.03.2016 10:17:05
Убрать пусто
Не пренебрегайте поиском

Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
Пользователь
Сообщений: 55 Регистрация: 01.01.1970
03.03.2016 10:33:52

Sanja , дело в том, что я не хочу заменить «пусто» на пусто. А вообще убрать эту строчку из сводной.
За ответ мерси!

Пользователь
Сообщений: 55 Регистрация: 01.01.1970
03.03.2016 10:48:28

Sanja , дошло самому))
В фильтрах — фильтр по значени-больше-0
И все работает.
Всем спасибо Всем пока.
Sanja , спасибо и удачи;)

Изменено: Александр Белов — 03.03.2016 19:07:28
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
03.03.2016 14:23:52

Цитата
Александр Белов написал: Не смогу добавить файл .xlsx ((( «Превышен максимальный размер загружаемого файла (100 КБ).» Это новые правила на сайте или я чтото не так делаю??

Новые? А Вы раньше там про ограничения ничего не видели? ))
Пользователь
Сообщений: 65 Регистрация: 01.01.1970
02.07.2019 12:28:30

Форумчане, приветствую.
поиск не дал результата сразу скажу, поэтому продолжу эту тему.
Есть сводная таблица, которая считает, делает вычисления, по многим показателям = 0 и это бывает вся строка целиком по нулям.
необходимо чтобы в сводной таблице сделать » подавить строки с нулевыми значениями «, именно подавить , т.е. исчезли они из отчета.
Пробовал:
1. Анализ > Параметры сводной таблицы > убрал галку в разделе Формат с «Для пустых ячеек отображать» , — не помогло
2. Файл > Параметры > Дополнительно > Параметры отображения листа > убрал галку с «Показывать нули в ячейках. которые содержат нулевые значения» — не помогло.
нули то исчезли, а пустые строки остались, которые не несут информации, а ты их пролистываешь бесполезно.

как побороть эту проблему ?

* своданя табл сделана в MS Excel 2016, POWER PIVOT

Изменено: Yastreb — 02.07.2019 12:30:57
Пользователь
Сообщений: 6602 Регистрация: 22.02.2017
Excel x64 О365 / 2016 / Online / Power BI
02.07.2019 13:08:55

Yastreb, в формуле меры делайте проверку получившегося значения на равенство нулю, и если получается ноль, то в сводную выводите BLANK(), тогда полностью пустые строки сами из отчета пропадут.

Вот горшок пустой, он предмет простой.
Пользователь
Сообщений: 2 Регистрация: 25.07.2019
25.07.2019 12:25:19

Приветствую!
У меня стоит обратная задача:
создается сводная таблица с использованием 2-х таблиц:
— Таблица 1 содержит полный список товаров в поле Наименование, которое используется как первичный ключ с полю второй таблицы Материал
— Таблица 2 содержит поля Материал, Расход, Дата, Заказ. при этом в поле Материал находятся и другие товары, не входящие в столбец Наименование таблицы 1, а входящие в данный столбец могут и не использоваться в рассматриваемый период дат.
— сводная таблица: строки — Наименование (Т1), столбцы Дата (2), значения Сумма Расход(Т2). в результате в столбцы выводятся Наименования только упомянутые в Т2 в поле Материал
— Необходимо чтобы сводная таблица в Строках содержала полный список поля Наименование(Т1), не используемым материалам должны соответствовать пустые строки. КАК ЭТО СДЕЛАТЬ? т.е. принудительно вывести строки для всех значений первичного ключа?

Изменено: anvo — 25.07.2019 12:28:33
Пользователь
Сообщений: 2 Регистрация: 25.07.2019
25.07.2019 12:36:12

Ответ нашел самостоятельно в настройках. удалить вопрос не смог

Вопрос задавался в рамках решения следующей задачи:
имеется таблица с технологическими данными Т1 Товар, Количество товара, Материал (сырьё для товара), Расход (материала), Дата, Станок

И для группы товаров — полуфабрикатов, которые в таблице Т1 могут присутствовать как в поле Товар (т.е. как готовое изделие), так и в поле Материал (т.е. как заготовка для другого Товара) необходимо получить таблицу текущего остатка данных TKU как ( СуммаКоличество — СуммаРасход) по дням. Кто-то решал подобное?

Как убрать пустые ячейки из сводной таблицы 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