Как выделить пустые ячейки в excel
Перейти к содержимому

Как выделить пустые ячейки в excel

  • автор:

MS Excel

Первый вариант.
Если нужно выделить ячейки по условию, то — условным форматированием.
Второй вариант.
Найти и выделить в Excel .
На закладке «Главная» в разделе «Редактирование» нажимаем на кнопку «Найти и выделить». В появившемся списке нажимаем на функцию «Выделение группы ячеек». В диалоговом окне ставим галочку у слов «Пустые ячейки».
Ещё окно функции «Выделение группы ячеек» можно вызвать клавишей F5 или сочетание клавиш « Ctrl » + «G». Выйдет окно «Переход» Внизу этого окна нажимаем на кнопку «Выделить…». Когда выделятся ячейки, их можно закрасить цветом для большей визуализации.
Таким способом можно выделить ячейки по другим параметрам – отличия по столбцам, строкам, только видимые ячейки, т.д.
Выделить ячейки с формулами в Excel .
Но , можно выделить все ячейки, которые входят в конкретную формулу. Еще один вариант, как выделить ячейки с формулами, описан выше – это выделить с помощью функции «Найти и выделить».
После того, как нашли и выделили ячейки, их можно окрасит цветом, изменить цвет шрифта, т.д.
Выделить только видимые ячейки в Excel .
Первый вариант – функцией «Найти и выделить» описан выше.
Второй вариант .
Выделить только видимые ячейки после фильтра можно обыкновенным способом, как выделяем диапазон ячеек.
Например, выделим ячейки с цифрой 1. Для наглядности окрасим их в желтый цвет.

Выделить повторяющиеся значения в Excel . Как выделить дубликаты в Excel .
В Excel есть несколько способов выделить дубликаты в таблице .

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

Можно скопировать уникальные значения в Excel .
Защита выделенных ячеек в Excel .
Можно защитить ячейку, чтобы в ней не могли ничего написать, изменить. Для этого нужно поставить пароль.

Можно защитить ячейку от неверно вводимых данных, чтобы писали правильно, в том формате, который нужен для дальнейшей обработки документа.

Как закрепить выделенную область в Excel .
В Excel можно закрепить верхние строки, столбцы слева таблицы – шапку таблицы, чтобы при прокрутке большой таблицы, эти строки и столбцы были всегда видны.
Можно закрепить область печати выделенных фрагментов таблицы.
Можно закрепить картинки, чтобы они не сдвигались при фильтрации данных таблицы.
Можно закрепить ссылки в ячейках, размер ячеек, т.д .

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

Второй способ.
Нам нужно, чтобы подсвечивалась другим цветом ячейка или шрифт с датой, например, выходные дни или дата оплаты счета, день рождения, т.д. Чтобы дата окрашивалась в другой цвет в день наступления этой даты или за несколько дней до наступления этой даты. Здесь нам поможет условное форматирование .

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

Количество выделенных строк в Excel .
Хоть и говорят, что нет специальной функции, без макросов, чтобы посчитать выделенные строки в Excel . Но, есть много других приемов, чтобы посчитать выделенные строки, ячейки.

Excel: Удаление пустых ячеек из диапазона

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

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

пустые ячейки

Задача — удалить пустые ячейки.

1.Выделяем исходный диапазон

Выделяем исходный диапазон

2. Для Excel 2003

Выбираем в меню команду Правка – Перейти — далее кнопку Выделить

Для Excel 2007 и выше

Выбираем на вкладке Главная команду Найти и выделить и далее Выделение группы ячеек

3. В открывшемся окне Выделение группы ячеек выбираем Пустые ячейки и нажимаем ОК.

Нажать Ок

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

пустые ячейки в диапазоне

4. Удаляем выделенные ячейки (Правка — Удалить ячейки) со сдвигом вверх

Удаляем выделенные ячейки

Удаляем выделенные ячейки

В итоге, в диапазоне остались только ячейки, содержащие информацию.

Удаление пустых ячеек из диапазона

Имеем диапазон ячеек с данными, в котором есть пустые ячейки: Задача — удалить пустые ячейки, оставив только ячейки с информацией.

Способ 1. Грубо и быстро

  1. Выделяем исходный диапазон
  2. Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.

delete_blanks3.png

Способ 2. Формула массива

Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или — в Excel 2003 и старше — меню Вставка — Имя — Присвоить (Insert — Name — Define)

Диапазону B3:B10 даем имя ЕстьПустые, диапазону D3:D10 — НетПустых. Диапазоны должны быть строго одного размера, а расположены могут быть где угодно относительно друг друга.

Теперь выделим первую ячейку второго диапазона (D3) и введем в нее такую страшноватую формулу:

В английской версии это будет:
=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),»»,INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>«»,ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))

Причем ввести ее надо как формулу массива, т.е. после вставки нажать не Enter (как обычно), а Ctrl+Shift+Enter. Теперь формулу можно скопировать вниз, используя автозаполнение (потянуть за черный крестик в правом нижнем углу ячейки) — и мы получим исходный диапазон, но без пустых ячеек:

Способ 3. Пользовательская функция на VBA

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

Для этого откройте редактор Visual Basic (ALT+F11), вставьте новый пустой модуль (меню Insert — Module) и скопируйте туда текст этой функции:

Function NoBlanks(DataRange As Range) As Variant() Dim N As Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Result() As Variant Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 To MaxCells, 1 To 1) For Each Rng In DataRange.Cells If Rng.Value <> vbNullString Then N = N + 1 Result(N, 1) = Rng.Value End If Next Rng For N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 If Application.Caller.Rows.Count = 1 Then NoBlanks = Application.Transpose(Result) Else NoBlanks = Result End If End Function

Не забудьте сохранить файл и вернитесь из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:

  1. Выделите достаточный диапазон пустых ячеек, например F3:F10.
  2. Идем в меню Вставка — Функция (Insert — Function) или жмем на кнопку Вставить функцию(Insert Function) на вкладке Формулы (Formulas) в новых версиях Excel. В категории Определенные пользователем (User Defined) выберите нашу функцию NoBlanks.
  3. В качестве аргумента функции укажите исходный диапазон с пустотами (B3:B10) и нажмите Ctrl+Shift+Enter, чтобы ввести функцию как формулу массива.

Ссылки по теме:

  • Удаление сразу всех пустых строк в таблице простым макросом
  • Удаление сразу всех пустых строк на листе с помощью надстройки PLEX
  • Быстрое заполнение всех пустых ячеек
  • Что такое макросы, куда вставлять код макросов на VBA

Заполнение пустых ячеек значениями из соседних ячеек

Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек — по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками — например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.

из fill-blanks1.png сделать fill-blanks2.png

В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.

Способ 1. Без макросов

Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12). Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) : fill-blanks3.pngНе снимая выделения, вводим в первую ячейку знак «равно» и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами): fill-blanks4.pngИ, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво. В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.

Способ 2. Заполнение пустых ячеек макросом

Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert — Module и копируем или вводим туда вот такой короткий код:

Sub Fill_Blanks() For Each cell In Selection If IsEmpty(cell) Then cell.Value = cell.Offset(-1, 0).Value Next cell End Sub

Как легко можно сообразить, этот макрос проходит в цикле по всем выделенным ячейкам и, если они не пустые, заполняет их значениями из предыдущей ячейки. Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.

Способ 3. Power Query

  • Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
  • При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов — всё делать заново.

Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы — Диспетчер имен), либо превратить в «умную» таблицу командой Главная — Форматировать как таблицу (Home — Format as Table ) или сочетанием клавиш Ctrl + T :

Превращаем таблицу в умную

После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.

В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить — Заполнить вниз (Transform — Fill — Fill Down) :

Заполнение пустых ячеек в Power Query

Вот и всё 🙂 Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close&Load — Close&Load to. )

В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные — Обновить всё (Data — Refresh All) .

Ссылки по теме

  • Объединение двух столбцов с данными
  • Быстрое выделение всех ячеек с формулами или константами
  • Быстрое заполнение пустых ячеек макросом из надстройки PLEX

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *