Как выделить непустые ячейки в Excel?
Рассмотрим таблицу, содержащую данные о продажах в разных странах и городах:
В этой таблице есть пустые строки. Наша задача — удалить их. Мы можем делать это по очереди, выделяя каждую пустую строку и щелкая правой кнопкой мыши, чтобы удалить строки. Более простой метод — отсортировать пустые строки, а затем удалить их все сразу.
Чтобы отсортировать данные в нашей таблице, выполните следующее
- Выделим левой клавишей мыши диапазон данных таблицы, затем на вкладке «Данные» нажмем на кнопку «Сортировка»:
- В появившемся диалоговом окне сортировки выберите столбец, по которому будут отсортированы данные, и задайте порядок:
- Затем система автоматически сортирует строки с пустыми ячейками и размещает их в нижней части таблицы:
- Затем, выделите пустые строки левой клавишей мыши и удалите их, нажав правой кнопкой мыши и выбрав пункт «Удалить»:
Метод 1: простое выделение и удаление
Этот метод, вероятно, можно считать самым простым. Вот что мы делаем:
- Любым удобным способом производим выделение области, в которой нам нужно найти и удалить все незаполненные ячейки. После того, как выделение выполнено, нажимаем клавишу F5.
- На экране отобразится окно перехода, в котором щелкаем “Выделить”.
- Мы окажемся в окне выделения групп ячеек. Здесь среди все вариантов выбираем “пустые ячейки”, поставив напротив отметку, после чего жмем OK.
- В нашем диапазоне будут отмечены все пустые ячейки. Щелчком правой кнопки мыши по любому их них открываем меню, в котором выбираем команду “Удалить”.
Примечание: Вы можете выбрать удаление со сдвигом вверх только тогда, когда в нижней части выделения нет строк с заполненными данными (по крайней мере, свободных строк должно быть столько же, сколько в выбранном диапазоне).
Действия по выделению и выбору ошибок или пропусков с условным форматированием в Google Таблицах
С помощью Google Spreadsheets легко обнаружить ячейки с ошибками и пробелами. Просто настройте автоматическое выделение этих ячеек по мере их появления, чтобы они не оставались некорректными. Это можно сделать, перейдя к настройкам условного форматирования и изменив значения по своему усмотрению.
С помощью инструмента условного форматирования в Google Spreadsheets можно установить, что каждая ячейка будет заполнена определенным цветом или стилем шрифта, если в ней есть пробелы или ошибки. Благодаря этому они выделяются на листе и их легко заметить.
Выберите или выделите пустые ячейки в Google Таблицах
Снимок экрана: Windows Dispatch
- Сначала запустите электронную таблицу Google Spreadsheet.
- Найдите ячейки, которые нужно выбрать. Выберите их все.
- В строке меню нажмите Формат.
- Прокрутите вниз и выберите Условное форматирование.
- Перейдите на вкладку «Один цвет».
- В разделе Применить к области убедитесь, что у вас есть все ячейки, которые вы хотите выбрать.
- Прокрутите вниз до правила Формат и нажмите стрелку в раскрывающемся списке.
- Выберите Пустой.
- В области Custom выберите форматирование, например, цвет шрифта, стиль и отступы.
- Нажмите Готово.
- Затем будут выбраны все пустые ячейки в выделенной области.
Выберите или выделите ячейки с ошибками в Google Таблицах
Снимок экрана: Windows Dispatch
- В рабочей книге Google Spreadsheet перейдите в раздел «Формат» и выберите «Условное форматирование».
- Убедитесь, что вы находитесь на вкладке «Один цвет». В разделе «Правила форматирования» нажмите на стрелку и выберите «Пользовательская формула».
- Теперь введите следующее: = ISERROR (B1). Вы можете заменить B1 на любую начальную ячейку в вашем диапазоне.
- Выберите стиль форматирования в разделе «Пользовательский».
- Нажмите Готово.
- Если в ячейках есть ошибки, будет применено выбранное вами форматирование.
Вот и все! Вы можете сразу обнаружить в своей книге ячейки с ошибками или незаполненные ячейки. Может ли техника условного форматирования быть полезной для повышения производительности и организации работы в Google Spreadsheets? Не стесняйтесь поделиться с нами своей историей в комментариях ниже.
Случаи, в которых возможно удаление незаполненных ячеек
Эта операция может привести к смещению данных, что нежелательно. Существуют некоторые ситуации, когда удаление необходимо, например:
Классическим является удаление пустот по одному элементу за раз. Этот метод возможен, если вы работаете с участками, которые нуждаются в незначительной коррекции. Наличие большого количества пустых элементов, приводит к необходимости использования пакетного метода удаления.
Решение 1: удаляем выделением группы ячеек
Выделить группы ячеек можно с помощью специального инструмента. Процесс выполнения:
- Выберите проблемную область, где скопились пустые ячейки, и нажмите клавишу F5.
- На экране должно открыться следующее командное окно. Нажмите на интерактивную кнопку «Выбрать».
- Программа откроет другое окно. Выберите «Пустые ячейки». Установите флажок и нажмите «OK».
- Пустые ячейки будут выбраны автоматически. При нажатии на пустое место откроется окно, в котором необходимо нажать кнопку «Удалить».
- После этого откроется окно для удаления ячеек. Установите флажок «Ячейки с прокруткой вверх». Примите его, нажав на кнопку «OK».
- В результате программа автоматически удалит места, подлежащие исправлению.
- Чтобы отменить выделение, нажмите левую кнопку мыши в любом месте таблицы.
Внимание! Метод удаления с прокруткой выбирается только в том случае, если после выделения нет строк, содержащих информацию.
Решение 2: применяем фильтрацию и условное форматирование
Прежде чем приступить к использованию этого метода, ознакомьтесь с полным планом каждого шага.
Обратите внимание, что этот метод может быть невыгодным, так как он используется при работе только с одним столбцом, в котором нет формул.
Рассмотрите последовательное описание фильтрации данных:
- Выберите область столбца. Найдите пункт «Правка» на панели инструментов. При нажатии на него появляется окно со списком параметров. Перейдите на вкладку «Сортировка и фильтр».
- Выберите фильтр и активируйте LKM.
- Это активирует верхний блок. На странице появится квадратный значок со стрелкой, направленной вниз. Это означает, что можно открыть окно с дополнительными функциями.
- Нажмите кнопку , снимите флажок «(Пустой)» на открывшейся вкладке и нажмите «OK».
- После этих манипуляций в столбце останутся только заполненные ячейки.
Совет от эксперта! Фильтрация подходит для удаления пустот только в том случае, если внутри отфильтрованных ячеек нет данных; в противном случае все данные будут потеряны.
Теперь давайте рассмотрим, как условное форматирование работает вместе с фильтрацией:
- Для этого выделите проблемную область и активируйте кнопку «Условное форматирование» на панели инструментов «Стили».
- В появившемся окне найдите строку «Еще» и нажмите на эту ссылку. В появившемся окне введите значение «0» в левом поле. В правом поле выберите нужную цветовую заливку или оставьте значения по умолчанию. Нажмите OK. В результате все ячейки, содержащие информацию, будут окрашены в выбранный вами цвет.
- Если приложение удаляет ранее сделанное выделение, примените его снова и активируйте инструмент фильтрации. Установите курсор на «Фильтр по цвету ячеек» или «Фильтр по шрифту» и активируйте одну из опций.
- В результате останутся только те ячейки, которые окрашены и, следовательно, заполнены данными.
- Снова выделите цветную область, найдите кнопку «Копировать» в верхней части панели инструментов и нажмите ее. Это показано в виде двух перекрывающихся листов.
- Если вы выбрали другую область на этом листе, сделайте другой выбор.
- Нажатие на кнопку мыши открывает меню, в котором есть значок «Значения». Иконка появится в виде таблицы с числовым расчетом 123, нажмите на нее.
Примечание: При выборе области убедитесь, что верхняя линия находится ниже нижней линии цвета выбранного списка.
- Это обеспечивает передачу копируемых данных без использования цветового фильтра.
Дополнительная работа с данными может быть выполнена на месте или путем перемещения их в другую область листа.
Решение 3: применяем формулу
Этот способ удаления пустых ячеек таблицы связан с определенными трудностями и поэтому менее популярен. Сложность заключается в использовании формулы, которая должна храниться в отдельном файле. Давайте проследим этот процесс по порядку:
- Выберите диапазон ячеек для корректировки. Затем нажмите на кнопку мыши и найдите команду «Присвоить имя». Назовите выбранный столбец и нажмите OK.
- В любой точке листа выберите свободную область, соответствующую размеру корректируемой области. Нажмите на ПКМ и введите другое имя.
- После необходимо активировать самую верхнюю ячейку свободной области и вписать в нее формулу: =ЕСЛИ(СТРОКА()-СТРОКА(Корректировка)+1>ЧСТРОК(Фамилии)-СЧИТАТЬПУСТОТЫ(Фамилии);””;ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(Фамилии <>””;СТРОКА(Фамилии);СТРОКА()+ЧСТРОК(Фамилии)));СТРОКА()-СТРОКА(Корректировка)+1);СТОЛБЕЦ(Фамилии);4))).
Примечание: Названия полей можно выбирать произвольно. В нашем примере это «Имена» и «Коррекция».
- Как только эти формулы будут введены, нажмите комбинацию клавиш «Ctrl+Shift+Enter». Это необходимо, поскольку в формуле присутствуют матрицы.
Разверните верхнюю ячейку до границ ранее определенной области. Должен появиться столбец с перенесенными данными, но без пустых ячеек.
Изменяем цвет заливки для особых ячеек (пустые, с ошибкой в формуле)
Как и в предыдущем примере, вы можете изменить цвет заливки определенных ячеек двумя способами: динамически и статически.
Используем формулу для изменения цвета заливки особых ячеек в Excel
Цвет ячейки будет автоматически изменяться в зависимости от значения ячейки.
Для иллюстрации давайте еще раз рассмотрим таблицу цен на бензин, но на этот раз не оставим ни одной пустой ячейки и добавим еще пару государств. Вот как можно найти эти пустые ячейки и изменить их цвет.
- На вкладке Home (Главная) в разделе Styles (Стили) нажмите Conditional Formatting (Условное форматирование) > New Rule (Создать правило). Точно также, как на 2-м шаге примера Как динамически изменять цвет ячейки, основываясь на её значении.
- В диалоговом окне New Formatting Rule (Создание правила форматирования) выберите вариант Use a formula to determine which cellsto format (Использовать формулу для определения форматируемых ячеек). Далее в поле Format values where this formula is true (Форматировать значения, для которых следующая формула является истинной) введите одну из формул:
- чтобы изменить заливку пустых ячеек
Поскольку мы хотим изменить цвет пустых ячеек, нам нужна первая функция. Введите его, затем поместите курсор между скобками и нажмите на значок выбора интервала справа от строки (или введите нужный интервал вручную):
Изменяем цвет заливки особых ячеек статически
После установки заливка остается постоянной, независимо от значения ячейки.
Если вы хотите настроить цвет заливки пустых ячеек или ячеек с формулами, содержащими ошибки, используйте этот метод:
- Выделите таблицу или диапазон и нажмите F5, чтобы открыть диалоговое окно Go To (Переход), затем нажмите кнопку Special (Выделить).
Если вы хотите выбрать ячейки, содержащие формулы с ошибками, установите флажок Формулы > Ошибки. Как видно из рисунка выше, существует множество других доступных настроек.
Обратите внимание, что сделанные таким образом исправления форматирования сохранятся даже после заполнения пустых ячеек значениями или исправления ошибок в формулах. Трудно представить, что этот маршрут может кому-то понадобиться, разве что в экспериментальных целях.