Как Фильтровать Пустые Ячейки в Excel • Алгоритмы удаления
Как Фильтровать Пустые Ячейки в Excel
На этом шаге мы рассмотрим автоматическую фильтрацию списка.
Фильтрация списка — это процесс сокрытия всех строк, кроме тех, которые удовлетворяют определенным критериям. В Excel списки можно фильтровать двумя способами:
Чтобы автоматически отфильтровать список, сначала установите табличный курсор на одну из его ячеек. Затем выполните команду Данные | Фильтр | Автофильтр . Excel проанализирует список и добавит в строку заголовков полей кнопки раскрывающихся списков (кнопки автофильтра) (рис.1).
Если щелкнуть на кнопке одного из раскрывающихся списков, то он раскроется и покажет все значения, содержащиеся в этом столбце (рис.1). Выберите какое-то значение, и Excel спрячет все строки, кроме тех, которые включают отмеченное значение.
Когда Вы oтфильтруете список, в строке состояния появится сообщение о том, сколько строк отобрано. Кроме того, изменится цвет кнопки раскрывающегося списка, чтобы напомнить Вам, что список отфильтрован по значениям, содержащимся в этом столбце.
Автоматическая фильтрация имеет ограничения. В раскрывающемся списке появляются только первые 999 различных значений. Если число элементов в Вашем списке превышает указанный предел, то можно использовать средства расширенной фильтрации.
- Все. Отображает все элементы столбца. Используется для отмены фильтрации столбца.
- Первые 10. Выбирает десять элементов списка.
- Условие. Позволяет фильтровать список по нескольким условиям.
- Пустые. Фильтрует список, отображая только строки с пустыми ячейками в данном столбце.
- Непустые. Фильтрует список, показывая только строки с непустыми ячейками в данном столбце.
Чтобы весь список был показан снова, щелкните на кнопке раскрытия списка и выберите опцию Все , которая расположена первой в раскрывающемся списке, или выполните команду Данные | Фильтр | Отобразить все .
Для отмены режима Автофильтр и удаления кнопок раскрывающихся списков в именах полей выберите команду Данные | Фильтр | Автофильтр повторно. В результате будет удален флажок у пункта меню Автофильтр , и список вернется в обычное состояние.
Не забывайте, что если у Вас есть формулы, в которых используются ссылки на ячейки отфильтрованного списка, то после выполнения фильтрации значение, вычисляемое по таким формулам, не изменяется.
Автоматическая фильтрация по значениям в нескольких столбцах
Иногда необходимо отфильтровать список по значениям в нескольких столбцах. На рисунке 2 показан список, который будет отфильтрован:
Рис.3. Список отфильтрован по значениям в двух столбцах
Обычно автоматическая фильтрация заключается в выборе одного значения в одном или нескольких столбцах. Если выбрать опцию Условие в раскрывающемся списке, то можно осуществить фильтрацию более гибким способом. При выборе опции Условие появляется диалоговое окно Пользовательский автофильтр (рис. 4).
- Значение больше или меньше установленного. Например, можно выбрать записи, указывающие на объем продаж, превышающие 10 000 .
- Значения в интервале. Например, отобрать все записи, указывающие на объем продаж, превышающие 10 000 И не превышающие 50 000 .
- Два отдельных значения. Например, отобрать записи, в которых находится информация об объеме продаж в городах Москва ИЛИ Курган .
- Выборка по шаблону. Можно использовать символы подстановки «*» и «?» , чтобы отфильтровать список более гибким способом. Например, чтобы вывести на экран записи только о тех клиентах, фамилии которых начинаются с буквы B , используйте шаблон B* .
Иногда необходимо отфильтровать числовые поля так, чтобы показать на экране наибольшие и наименьшие значения. Для этого нужно использовать опцию Первые 10 .
Название Первые 10 — это просто общепринятый термин, действие же опции не ограничивается нахождением только 10 «наибольших» элементов. На самом деле ее действие даже не ограничивается нахождением только наибольших элементов. При выборе опции Первые 10 появится диалоговое окно Наложение условия по списку (рис. 5).
С его помощью Вы можете выбрать наибольшие или наименьшие элементы из списка, а также узнать их количество. В этом диалоговом окне можно также выбрать опцию Элементов из списка или % от количества элементов .
Построение диаграммы по данным отфильтрованного списка
Для создания некоторых многоцелевых диаграмм используются данные отфильтрованного списка. Этот способ очень полезен, так как только диаграмма позволяет наглядно увидеть картину, представленную теми или иными данными. При изменении критерия автофильтра автоматически изменяется и показывает значения, взятые только из «видимых» ячеек.
Для того чтобы воспользоваться этим методом, убедитесь, что активизирована опция Отображать только видимые ячейки на вкладке Диаграмма в диалоговом окне Параметры (рис. 6).
Как удалить пустые строки в Microsoft Excel
- Все. Отображает все элементы столбца. Используется для отмены фильтрации столбца.
- Первые 10. Выбирает десять элементов списка.
- Условие. Позволяет фильтровать список по нескольким условиям.
- Пустые. Фильтрует список, отображая только строки с пустыми ячейками в данном столбце.
- Непустые. Фильтрует список, показывая только строки с непустыми ячейками в данном столбце.
При выполнении задач в приложении Excel может наступить необходимость удалить пустые ячейки. Они зачастую являются ненужным элементом и только увеличивают общий массив данных, чем запутывают пользователя. Определим способы, какими можно быстро удалить пустые элементы.
Как удалить пустые строки Excel несколькими способами
При работе с большими объемами данных иногда возникают проблемы, причины которых, на первый взгляд, не очевидны. Например, при попытке выделить всю таблицу выделяется только ее часть, некорректно строятся промежуточные итоги или сводные таблицы. При внимательном рассмотрении оказывается, что виной этому пустые строки. В статье мы учимся удалять пустые строки в Excel.
Удаление с помощью сортировки
Откуда могут появляться записи с отсутствием в них данных? Такое может наблюдаться при выгрузке информации из базы данных или экспорте из другого файла или приложения. Значения могут быть уничтожены вручную без удаления самой строки. Так или иначе, с этим явлением нужно и можно бороться.
Один из способов, как удалить пустые строки в Excel — использование сортировки. Если необходимо удалить те записи, у которых ни в одной ячейке нет данных, сортировать можно по любому столбцу. Если же только те, что не заполнены в определенных колонках — сортируйте только эту колонку. Покажем на примере, как это сделать для целиком пустых записей.
Поставщик косметики прислал прайс-лист, в котором часть информации была удалена. Это затрудняет работу с данными, поэтому мы хотим убрать эти строки. Выберем для сортировки колонку «Цена». Для упорядочивания значений можно воспользоваться меню «Сортировка и фильтр» панели «Редактирование» вкладки «Главная», отсортировав числа по возрастанию.
Или воспользоваться выпадающим меню заголовка форматированной таблицы. Так или иначе, после построения списка строки с отсутствующими ценами окажутся внизу таблицы. Теперь удалим пустые строки Excel в конце командой контекстного меню.
Использование фильтра
Теперь раскройте меню этого списка. Внизу перечня элементов колонки находится пункт «Пустые». Выделите его, предварительно сняв флажки со всех остальных элементов. В результате применения вы оставите только пустые строки Excel. Удалите их через список операций правого клика мыши или из блока операций «Ячейки» вкладки «Главная».
Выделение диапазона полей
Отмечаем таблицу и вызываем пункт меню «Выделить группу ячеек». Он находится в блоке команд «Редактирование» и вызывается операцией «Найти и выделить».
В окне настроек команды выбираем вариант «пустые ячейки» и нажимаем «ОК».
Программа отмечает нужные записи. Чтобы удалить пустые строки Excel, используйте команду «Удалить строки с листа», из меню операции «Удалить» блока «Ячейки».
Есть и другой способ выделения пустых полей. Чтобы им воспользоваться, откройте вкладку «Главная» и найдите блок «Редактирование». В меню «Найти и выделить» выполните команду «Перейти». В окне настроек перехода отметьте таблицу, с которой работаете, и нажмите кнопку «Выделить» внизу формы.
Недостаток метода выделения ячеек
Однако с последним методом нужно быть крайне осторожным. Если вы выделите таблицу перед тем, как в таблице Excel удалите пустые строки, программа отметит не только полностью незаполненные записи, но и те, в которых присутствуют ячейки без данных. Удаление всего выделенного диапазона может привести не только к потере нужной информации, но и к искажению оставшейся.
Использование первых двух способов более надежно и безопасно, поэтому лучше использовать именно их, чтобы удалить пустые строки в Excel.
Как Фильтровать Пустые Ячейки в Excel
-
Выделяем диапазон, который собираемся обрабатывать. Находясь во вкладке «Главная», жмем на пиктограмму «Условное форматирование», которая, в свою очередь, располагается в блоке инструментов «Стили». Переходим в пункт открывшегося списка «Правила выделения ячеек». В появившемся списке действий выбираем позицию «Больше…».
Удаляем пустые строки в Excel
Как удалить пустые строки в таблице Excel
Функционал Excel предполагает использование нескольких инструментов и вариантов избавления от пустых строк в таблице. Некоторые варианты, например, не могут быть применены при определённых условиях или их применение становится нежелательным.
Дальше рассмотрим все доступные варианты удаления пустых строк в таблице MS Excel.
Вариант 1: Стандартное удаление
Самый простой способ удаления пустых строк в таблице Excel. Несмотря на свою простоту и распространённость, он не всегда является самым удобным и безопасным. Лучше всего его применять, когда, например, в конце таблицы остались незаполненные ячейки.
Очистка пустующих элементов этим способом происходит по следующей инструкции:
- Выделите в таблице те пустые строки, которые вам требуется удалить.
- Нажмите правой кнопкой мыши по выделению. Из контекстного меню выберите пункт «Удалить».
Выделенные строки будут удалены. Как вариант можно вместо вызова контекстного меню просто нажать на кнопку «Удалить», которая расположена в блоке «Ячейки». Там появится небольшое контекстное меню, где нужно выбрать пункт «Удалить строки с листа».
Вариант 2: Настроить сортировку данных
Этот вариант будет отличным решением в том случае, если в таблице местами присутствуют пустые строки, разбросанные по всему документу. Суть заключается в том, что вы настраиваете сортировку документа таким образом, чтобы все пустые строки ушли вниз и их можно было легко удалить из таблицы.
Однако бывает так, что в таблице нет цифровых значений или их не получается отсортировать описанным выше образом. В таком случае можно пойти на некоторую хитрость:
- Способ заключается в том, чтобы пойти на небольшую хитрость – добавить дополнительный столбец, установить в нём цифры и отсортировать содержимое таблицы по этому столбцу. Потом он будет удалён. Обратите внимание на блок «Ячейки». Здесь нужно воспользоваться кнопкой «Вставить».
- Из контекстного меню выберите «Вставить столбцы на лист».
- Теперь пронумеруйте все ячейки в этом столбце по возрастанию. Можно просто написать в первых двух ячейках числа, выделить их и потянуть за нижний край. Таблица заполнится автоматически.
- Затем выделите всю таблицу и кликните правой кнопкой мыши по ней. Из контекстного меню выберите пункт «Сортировка». Далее «Сортировка от минимального к максимальному».
- Когда таблица отсортирована можно выделить столбце, по которому проводилась сортировка. После нажмите на кнопку «Удалить», которая расположена в блоке «Ячейки». Из контекстного меню выберите пункт «Удалить столбцы с листа».
- Теперь удалить все пустые ячейки, которые спустились в нижнюю часть таблицы.
Вариант 3: Применить фильтр
Более продвинутая версия предыдущего способа. Может использоваться в сложных таблицах, которые не получается корректно сортировать предыдущим способом, а удалять каждую пустующую ячейку по отдельности сильно долго.
- Выделите всю таблицу и кликните по кнопке «Сортировка и фильтр», которая расположена во вкладке «Главная». Эта кнопка будет находится в блоке «Редактирование». Иногда её может не быть. В таком случае ищите инструмент «Фильтр».
- После нажатия на кнопку должно появится контекстное меню, где потребуется выбрать пункт «Фильтр».
- В шапке таблицы должен появится соответствующий значок в виде стрелки. Нажмите на один из них.
- Откроется окошко настроек фильтрации. Здесь нужно снять галочку у пункта «Пустые» и нажать «Ок».
- Пустые ячейки в выделенном столбце будут отфильтрованы и исчезнут. Их можно вернуть, если отключить фильтр.
- По аналогии избавьтесь от пустующих ячеек в других столбцах.
Вариант 4: Выделение группы ячеек
Данный способ чем-то похож на предыдущий, но при этом позволяет быстро выделить все пустующие ячейки в таблице и удалить их с помощью соответствующего инструмента. Реализуется следующим образом:
- На листе с таблицей выделите саму таблицу. В верхней части обратите внимание на блок «Редактирование». Там воспользуйтесь инструментом «Найти и выделить».
- Должно появится контекстное меню, в котором выбирайте пункт «Выделение группы ячеек».
- Откроется настройка выделения групп ячеек. Здесь установите маркер на пункте «Пустые ячейки». Нажмите «Ок».
- В таблице выделятся все пустующие ячейки. В блоке «Ячейки», что расположен в верхней части нажмите на кнопку «Удалить».
Как видите, все пустые ячейки были удалены. Несмотря на то, что данный способ очень удобен, его не рекомендуется использовать в таблицах, где есть перекрывающийся диапазон и/или пустые ячейки в строках с имеющимися данными. Дело в том, что при использовании данного способа может нарушиться внешний вид и структура таблицы.
Все имеющиеся способы удобны в тех или иных ситуациях, поэтому выбирайте подходящий в зависимости от вашей ситуации. Если у вас есть какие-либо предложения, касательно темы статьи или вопросы, то пишите их в комментариях.
Работа с фильтрами в MS Office Excel на примерах.
- Выделите всю таблицу и кликните по кнопке «Сортировка и фильтр», которая расположена во вкладке «Главная». Эта кнопка будет находится в блоке «Редактирование». Иногда её может не быть. В таком случае ищите инструмент «Фильтр».
- После нажатия на кнопку должно появится контекстное меню, где потребуется выбрать пункт «Фильтр».
- В шапке таблицы должен появится соответствующий значок в виде стрелки. Нажмите на один из них.
- Откроется окошко настроек фильтрации. Здесь нужно снять галочку у пункта «Пустые» и нажать «Ок».
- Пустые ячейки в выделенном столбце будут отфильтрованы и исчезнут. Их можно вернуть, если отключить фильтр.
- По аналогии избавьтесь от пустующих ячеек в других столбцах.
Открывается окно присвоения наименования. В поле «Имя» даем любое удобное название. Главное условие – в нем не должно быть пробелов. Для примера мы присвоили диапазону наименование «С_пустыми». Больше никаких изменений в том окне вносить не нужно. Жмем на кнопку «OK».
Функция Автофильтр в Excel, применение и настройка
Когда необходимо найти одну или несколько строк в большой таблице, приходится тратить много времени, прокручивая лист и выискивая глазами нужные ячейки. Встроенный фильтр Microsoft Excel упрощает поиск данных среди множества ячеек. Выясним, как включить и выключить автоматический фильтр, и разберем возможности, которые он дает пользователям.
Как включить автофильтр в Excel
Существует несколько способов начать работу с использованием этой опции. Разберем каждый из них наглядно. Результатом включения фильтра будет появление квадратной кнопки со стрелкой рядом с каждой ячейкой в шапке таблицы.
Важно! Использовать фильтр можно только в том случае, если у таблицы есть шапка. Установка фильтра на таблицу без заголовков приведет к потере данных в верхней строке – они скроются из виду.
Настройка фильтра по данным таблицы
Фильтр часто используют в больших таблицах. Он нужен для того, чтобы быстро просматривать строки одной категории, временно отделить их от другой информации.
Сортировка проводится также по текстовым или числовым фильтрам. Программа оставит на листе строки, соответствующие установленным требованиям. К примеру, текстовый фильтр «равно» отделяет строки таблицы с указанным словом, «не равно» действует наоборот – если указать в настройках слово, строк с ним не будет. Существуют текстовые фильтры по начальной или конечной букве.
Числа можно сортировать по фильтрам «больше или равно», «меньше или равно», «между». Программа способна выделить первые 10 чисел, отобрать данные выше или ниже среднего значения. Полный перечень фильтров для текстовой и числовой информации:
Если ячейки закрашены, и установлен цветовой код, открывается возможность сортировки по цвету. Ячейки выбранного цвета переходят наверх. Фильтр по цвету позволяет оставить на экране строки, ячейки которых окрашены в выбранный из списка оттенок.
Важно! Отдельно стоит отметить функцию «Дополнительно…» в разделе «Сортировка и фильтр». Она предназначена для расширения возможностей фильтрации. С помощью расширенного фильтра можно задать условия вручную в виде функции.
Действие фильтра сбрасывается двумя методами. Проще всего использовать функцию «Отменить» или нажать комбинацию клавиш «Ctrl+Z». Другой способ – открыть вкладку данные, найти раздел «Сортировка и фильтр» и нажать кнопку «Очистить».
Пользовательский фильтр: настройка по критериям
Фильтрацию данных в таблице можно настроить так, как удобно конкретному пользователю. Для этого в меню автофильтра включена опция «Настраиваемый фильтр». Разберемся, чем она полезна и чем отличается от заданных системой режимов фильтрации.
Понаблюдаем за работой текстовых фильтров:
- Для этого откроем меню фильтров в столбце с текстовыми данными и выберем любой тип фильтра – например, «начинается с…».
- В примере использована одна строка автофильтра, но можно задействовать две.
- В результате на экране остаются две строки, начинающиеся с выбранной буквы.
Отключение автофильтра через меню Excel
Чтобы выключить фильтр на таблице, нужно снова обратиться к меню с инструментами. Для этого подходят два способа.
- Откроем вкладку «Данные», в центре меню находится большая кнопка «Фильтр», входящая в раздел «Сортировка и фильтр».
- Если нажать эту кнопку, значки со стрелками исчезнут из шапки, и рассортировать строки будет невозможно. При необходимости можно включить фильтры снова.
Другой способ не требует перемещения по вкладкам – нужный инструмент размещен на «Главной». Откроем раздел «Сортировка и фильтр» справа и снова нажмем на пункт «Фильтр».
Совет! Чтобы определить включена сортировка или выключена, можно взглянуть не только на шапку таблицы, но и на меню. Пункт «Фильтр» подсвечивается оранжевым, когда его включают.
Если автофильтр настроен правильно, он поможет найти информацию в таблице с оформленной шапкой. Фильтры работают с числовыми и текстовыми данными, что помогает пользователю значительно упростить работу с табличным процессором Excel.
Удаление пустых строк в таблице Microsoft Excel
- Для этого откроем меню фильтров в столбце с текстовыми данными и выберем любой тип фильтра – например, «начинается с…».
- В примере использована одна строка автофильтра, но можно задействовать две.
Пример 1 . Сортировка данных в таблице. Выделяем всю таблицу. Открываем вкладку «Данные» – инструмент «Сортировка и фильтр» – нажимаем кнопку «Сортировка». Или щелкаем правой кнопкой мыши по выделенному диапазону и делаем сортировку «от минимального к максимальному».
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.