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

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

Постановка задачи

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

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

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

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

   

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

3. Даем в меню команду на удаление выделенных ячеек: правой кнопкой мыши — Удалить ячейки (Delete Cells) со сдвигом вверх.

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

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

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

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

=ЕСЛИ(СТРОКА()-СТРОКА(НетПустых)+1>ЧСТРОК(ЕстьПустые)-СЧИТАТЬПУСТОТЫ(ЕстьПустые);»»;ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(ЕстьПустые<>»»;СТРОКА(ЕстьПустые);СТРОКА()+ЧСТРОК(ЕстьПустые)));СТРОКА()-СТРОКА(НетПустых)+1);СТОЛБЕЦ(ЕстьПустые);4)))

В английской версии это будет:

=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

history 24 апреля 2013 г.

Группы статей
• Вывод отобранных значений в отдельный диапазон
• Отбор строк в таблице
• Пустые ячейки

Если список значений содержит пропуски (пустые ячейки), то это может существенно затруднить его дальнейший анализ. С помощью формул уберем пустые ячейки из колонки с данными. Также напишем формулу, чтобы удалить нули из списка значений. В конце статьи научимся удалять вообще любое заданное значение из списка: символ, число, текстовую строку.

Пусть имеется список с

пустыми

ячейками (столбец

А

).

Задача

Убрать пустые ячейки из списка, сформировав формулами список в соседнем столбце. То есть под словом «убрать» будем понимать не удаление значения из исходного списка, а формирование еще одного списка, но уже без лишних символов. Чтобы действительно убрать значения из списка нужно использовать макросы — программу на VBA.

Решение

Для избавления от пустых ячеек запишем в ячейке

B2

формулу массива

и скопируем ее вниз (см. файл примера): =ЕСЛИОШИБКА(ДВССЫЛ(«A»&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$14);»»;СТРОКА($A$2:$A$14));СТРОКА(A1)));»»)

Получим в соседнем столбце

B

список со значениями из исходого, но уже без пропусков. Формула работает одинакового и для текстовых значений и для чисел. Алгоритм работы формулы следующий:

• ЕСЛИ(ЕПУСТО($A$2:$A$14);»»;СТРОКА($A$2:$A$14)) – если ячейка не пуста, то эта часть формулы возвращает номер строки. То есть формируется массив номеров строк, НЕ содержащих пустоты {2:»»:4:5:6:»»:»»:9:10:»»:»»:13:14} На месте пустых ячеек в массиве будет символ «» (пустой текст), но можно его заменить в формуле на любую текстовую строку, например «ккк». Проверить результат можно выделив эту часть формулы и нажав

  клавишу
  
  F9
  
  ;

• Функция НАИМЕНЬШИЙ() сортирует массив строк по возрастанию. В сортированном списке сначала будут идти номера строк затем значения «», т.к. в EXCEL считается, что любое текстовое значение больше любого числа (значение пустой текст — текстовое значение);
• Далее для функции ДВССЫЛ() формируются адреса ячеек с непустыми значениями. Например, ДВССЫЛ(«A»&2) возвращает значение из ячейки
  
  А2
  
  . Для пустых ячеек будет формироваться ошибочные адреса ячеек, состоящие только из символа А. Это вызовет ошибку после применения функции ДВССЫЛ();
• Функция ЕСЛИОШИБКА() вместо ошибки будет возвращать «». Этот символ не отображается в ячейке и ячейка выглядит пустой.

На самом деле в соседнем столбце

B

список будет отличаться не только тем, что в нем значения будут идти подряд без пропусков. Если в исходном списке пустые ячейки действительно не содержали ничего, то в новом списке в пустых ячейках будут значения Пустой текст «». Хотя внешний вид пустых ячеек из обоих списков будет неотличим, но формулы увидят разницу. То есть, если Вы планируете делать дальнейшие манипуляции с новым списком, то имейте ввиду, что теперь пустые ячейки в конце списка теперь не совсем пусты — они содержат текстовое значение «». Подробнее про это специфическое значение читайте

здесь

.

Изменим немного формулу: =ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ( ЕПУСТО($A$2:$A$14);»»;$A$2:$A$14);СТРОКА(A1));»»)

Получим тот же список, но еще и

сортированный

по возрастанию (работает только для чисел).

Список также можно сформировать в столбце С другой

формулой массива

:

=ЕСЛИОШИБКА(ДВССЫЛ(«A»&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО(СписокСпропусками);»»;СТРОКА(СписокСпропусками));СТРОКА()-СТРОКА($C$1)));»»)

СписокСпропусками

в формуле — это

динамический диапазон

, который образован формулой:

=СМЕЩ($A$2;;;ДлинаСпискаСпропусками)

Длину списка с пропусками

можно вычислить с помощью формулы:

=ПОИСКПОЗ(ПОВТОР(«я»;10);$A$2:$A$14;1)

СОВЕТ:

Для удаления и выделения пустых строк и ячеек традиционными средствами EXCEL, пользуйтесь идеями из статей

Удаляем пустые строки в таблице

и

Выделение группы ячеек

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

Подсчет пустых ячеек

.

Удаляем заданные символы из списка

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

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

Пусть исходный список находится в диапазоне A12:A24, а в ячейке B6 содержится значение которое нужно удалить из ячеек списка.

=ЕСЛИОШИБКА(ДВССЫЛ(«A»&НАИМЕНЬШИЙ(ЕСЛИ($A$12:$A$24=$B$6;»»;СТРОКА($A$12:$A$24));СТРОКА()-СТРОКА($A$11)));»»)

Единственным отличием является выражение $A$12:$A$24=$B$6, которое заменило формулу с ЕПУСТО(…)

Теперь если значение в исходном списке не равно искомому значению, то вместо него будет выведено значение «» (в конце списка).

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

Возможность и методы удаления

Для начала нужно выяснить, существует ли в принципе такая возможность, как удаление пустых ячеек в выбранном диапазоне данных (таблице), и подобрать оптимальный способ для реализации этой операции. Например, при простом выделении и удалении будет происходить смещение ячеек, что в некоторых случаях нежелательно или, и вовсе, недопустимо. В целом, удалить пустые ячейки проще всего при следующих условиях:

• Весь столбец (строка) не заполнен;
• Элементы в столбце (строке) не взаимосвязаны.

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

Метод 1: простое выделение и удаление

Данный метод, пожалуй, можно считать самым простым. Вот, что мы делаем:

1. Любым удобным способом производим выделение области, в которой нам нужно найти и удалить все незаполненные ячейки. После того, как выделение выполнено, нажимаем клавишу F5.
2. На экране отобразится окно перехода, в котором щелкаем “Выделить”.
3. Мы окажемся в окне выделения групп ячеек. Здесь среди все вариантов выбираем “пустые ячейки”, поставив напротив отметку, после чего жмем OK.
4. В нашем диапазоне будут отмечены все пустые ячейки. Щелчком правой кнопки мыши по любому их них открываем меню, в котором выбираем команду “Удалить”.
5. В появившемся окне останавливаемся на варианте “ячейки, со сдвигом вверх” и нажимаем OK.
6. В результате выполненных действий мы удалили все пустые ячейки, находящиеся в выделенной области.
7. Кликаем по любому элементу на листе, чтобы снять выделение.

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

Метод 2: фильтр и условное форматирование

Этот метод несколько сложнее рассмотренного выше и предполагает применение фильтра или условного форматирования с дальнейшей фильтрацией (сортировкой) данных. Однако тут есть один нюанс – его можно применять только для работы с одним столбцом, в котором, к тому же, нет формул.

Фильтрация данных:

1. Для начала нужно любым удобным способом выделить нужную область ячеек. Затем в главной вкладке в блоке инструментов “Редактирование” щелкаем по значку “Сортировка и фильтр”. В раскрывшемся перечне выбираем пункт “Фильтр”.
2. Внутри самой верхней ячейки выделенного диапазона появится характерный значок фильтра в виде небольшого квадрата с треугольником, направленным вниз. Щелкаем по нему. Откроется меню, в котором убираем галочку напротив пункта “Пустые” и жмем OK.
3. В таблице останутся только ячейки, содержащие какие-либо данные.

Условное форматирование и фильтрация:

1. Выполняем выделение диапазона ячеек. В главной вкладке в группе инструментов “Стили” нажимаем кнопку “Условное форматирование”. В раскрывшемся перечне останавливаемся на варианте “Правила выделения ячеек”, затем – выбираем “Больше”.
2. На экране отобразится окно форматирования:
3. Ко всем заполненным ячейкам будет применена цветовая схема, которую мы выбрали, в то время, как заливка пустых ячеек не изменилась. Повторно выделяем тот же самый диапазон (если выделение было снято) и включаем “Фильтр”.
4. Кликаем по значку фильтра, в открывшемся меню наводим указатель мыши на строку “Фильтр по цвету”, после чего откроется еще одно подменю, где останавливаемся на варианте “Фильтр по цвету ячейки” или “Фильтр по цвету шрифта” (в данном случае, это неважно).
5. В результате, из всего выделенного диапазона мы оставили только заполненные ячейки.

Независимо от того, каким из способов выше мы воспользовались (фильтр или условное форматирование с последующей фильтрацией), дальнейшие шаги следующие:

1. Нажимаем кнопку “Копировать” в группе инструментов “Буфер обмена” (вкладка “Главная”).
2. На этом же (или другом) листе отмечаем соразмерный диапазон, затем щелчком правой кнопки мыши по нему раскрываем меню, в котором кликаем по варианту вставки – “Значения” (значок в виде папки с цифрами 123). Тут есть один нюанс – выделять нужно диапазон, который располагается ниже самой нижней строки области с первоначальными данными.
3. Нам удалось вставить данные (без первоначального форматирования ячеек). Мы можем продолжить работу с ними здесь или перенести (скопировать) в любое другое удобное место.

Сортировка вместо фильтрации:

В данном случае, мы выполняем те же самые шаги, что и при условном форматировании. Но вместо фильтра теперь выбираем “Сортировку по цвету”.

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

Метод 3: использование формулы

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

1. Для начала нужно задать имя диапазону ячеек, с которым планируем работать. Для этого выделяем его, затем кликаем по нему правой кнопкой мыши и в контекстном меню выбираем команду “Присвоить имя”.
2. В окне создания имени в поле “Имя” указываем любое название (без пробелов). Остальные поля оставляем без изменений и нажимаем OK.
3. В любом свободном месте производим выделение соразмерного диапазона ячеек (для удобства, если возможно, лучше это сделать в тех же строках другого столбца). Затем также в контекстном меню, вызываемом щелчком правой кнопки мыши по нему, выбираем пункт “Присвоить имя”.
4. Как и для первого диапазона, задаем имя (в нашем случае – “Без_пустых_ячеек”).
5. Встаем в самую верхнюю ячейку диапазона “Без_пустых_ячеек” (в нашем случае – это D2) и пишем в нее следующую формулу:

   =ЕСЛИ(СТРОКА()-СТРОКА(Без_пустых_ячеек)+1>ЧСТРОК(Диапазон_с_пустыми_ячейками)
   СЧИТАТЬПУСТОТЫ(Диапазон_с_пустыми_ячейками);"";ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(Диапазон_с_пустыми_ячейками
   <>"";СТРОКА(Диапазон_с_пустыми_ячейками);СТРОКА()+ЧСТРОК(Диапазон_с_пустыми_ячейками)));СТРОКА()-
   СТРОКА(Без_пустых_ячеек)+1);СТОЛБЕЦ(Диапазон_с_пустыми_ячейками);4)))

   .
   Примечание: в данном случае указаны имена диапазонов, заданные нами. Вам нужно заменить их на свои названия.

6. Когда все готово, жмем сочетание клавиш Ctrl+Shift+Enter, так как это формула, содержащая массивы. Результат отобразится в выбранной ячейке. В строке формул мы увидим перед знаком “равно” символ “{“. Это означает, что мы все сделали правильно.
7. Теперь нужно растянуть формулу на другие ячейки. Для этого наводим указатель мыши на правый нижний угол ячейки с результатом. Когда появится Маркер заполнения (черный плюсик), зажав левую кнопку мыши тянем его до последней строки диапазона “Без_пустых_ячеек”.
8. Мы получим столбец, в котором подряд расположились значения из ячеек исходного “Диапазона_с_пустыми_ячейками”.
9. Чтобы в дальнейшем иметь возможность работать с этими данными, не снимая выделение (или повторно выделив этот диапазон), щелкаем по нему правой кнопкой мыши и выбираем команду “Копировать” в открывшемся контекстном меню (или можно просто нажать комбинацию Ctrl+C).
10. В любом другом месте листа отмечаем соразмерный диапазон ячеек (например, в первоначальном месте), кликом правой кнопки мыши по выделенной области вызываем меню, в котором выбираем вариант вставки – “Значения”.
11. Столбец с формулами нам больше не нужен. Щелкаем по его обозначению на горизонтальной панели координат правой кнопкой мыши и в открывшемся контекстном меню выбираем команду “Удалить”.
12. В результате проделанных действий мы получили тот же самый столбец, но уже без пустых ячеек.

Заключение

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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Чтобы подсчитать количество ячеек в диапазоне, содержащих значения, используйте функцию СЧЁТЗ.

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

Функция COUNT пропускает пустые значения в D3, D4, D8 и D11 и подсчитывает количество ячеек, содержащих значения в столбце г. Функция находит шесть ячеек в столбце D, содержащий значения, и отображает 6 в качестве выходных данных.

Функция ЕПУСТО в Excel используется для наличия текстовых, числовых, логических и прочих типов данных в указанной ячейке и возвращает логическое значение ИСТИНА, если ячейка является пустой. Если в указанной ячейке содержатся какие-либо данные, результатом выполнения функции ЕПУСТО будет логическое значение ЛОЖЬ.

Примеры использования функции ЕПУСТО в Excel

Пример 1. В таблице Excel находятся результаты (баллы) по экзамену, который проводился в учебном заведении. В данной электронной ведомости напротив некоторых студентов оценки не указаны, поскольку их отправили на пересдачу. В столбце рядом вывести текстовую строку «Сдал» напротив тех, кому выставили оценки, и «На пересдачу» — напротив не сдавших с первого раза.

Выделим ячейки C3:C18 и запишем следующую формулу:

Формула ЕСЛИ выполняет проверку возвращаемого результата функции ЕПУСТО для диапазона ячеек B3:B18 и возвращает один из вариантов («На пересдачу» или «Сдал»). Результат выполнения функции:

Теперь часть данной формулы можно использовать для условного форматирования:

1. Выделите диапазон ячеек C3:C18 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. В появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулы для определения форматируемых ячеек» и введите следующую формулу:
3. Нажмите на кнопку «Формат» (как на образцу), потом укажите в окне «Формат ячеек» красный цвет заливки и нажмите ОК на всех открытых окнах:

На против незаполненных (пустых) ячеек или двоек мы получаем соответственное сообщение «На пересдачу» и красную заливку.

Почему нужно использовать функцию ЕПУСТО при проверке пустых ячеек

У выше указанном примере можно изменить формулу используя двойные кавычки («») в место функции проверки ячеек на пустоту, и она также будет работать:

=ЕСЛИ(ИЛИ( B3=»» ;B3=2);»На пересдачу»;»Сдал»)

Но не всегда! Все зависит от значений, которые могут содержать ячейки. Обратите внимание на то как по-разному себя ведут двойные кавычки, и функция ЕПУСТО если у нас в ячейках находятся одни и те же специфические значения:

Как видно на рисунке в ячейке находится символ одинарной кавычки. Первая формула (с двойными кавычками вместо функции) ее не видит. Более того в самой ячейке A1 одинарная кавычке не отображается так как данный спецсимвол в Excel предназначенный для отображения значений в текстовом формате. Это удобно, например, когда нам нужно отобразить саму формулу, а не результат ее вычисления как сделано в ячейках D1 и D2. Достаточно лишь перед формулой ввести одинарную кавычку и теперь отображается сама формула, а не возвращаемый ее результат. Но функция ЕПУСТО видит, что в действительности ячейка А1 не является пустой!

Проверка на пустую ячейку в таблице Excel

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

Исходная таблица данных:

Чтобы определить наличие пустых ячеек используем следующую формулу массива (CTRL+SHIFT+Enter):

Функция СУММ используется для определения суммы величин, возвращаемых функцией —ЕПУСТО для каждой ячейки из диапазона B3:B17 (числовых значений, поскольку используется двойное отрицание). Если запись СУММ(—ЕПУСТО(B3:B17) возвращает любое значение >0, функция ЕСЛИ вернет значение ИСТИНА.

То есть, в диапазоне B3:B17 есть одна или несколько пустых ячеек.

Примечание: в указанной выше формуле были использованы символы «—». Данный вид записи называется двойным отрицанием. В данном случае двойное отрицание необходимо для явного преобразования данных логического типа к числовому. Некоторые функции Excel не выполняют автоматического преобразования данных, поэтому механизм преобразования типов приходится запускать вручную. Наиболее распространенными вариантами преобразования текстовых или логических значений к числовому типу является умножение на 1 или добавление 0 (например, =ИСТИНА+0 вернет число 1, или =«23»*1 вернет число 23. Однако использование записи типа =—ИСТИНА ускоряет работу функций (по некоторым оценкам прирост производительности составляет до 15%, что имеет значение при обработке больших объемов данных).

Как посчитать количество пустых ячеек в Excel

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

Формула для расчета (формула массива):

Функция ЕСЛИ выполняет проверку диапазона на наличие пустых ячеек (выражение СУММ(—ЕПУСТО(B3:B12))). Если СУММ вернула значение >0, будет выведено сообщение, содержащее количество незаполненных данными ячеек (СЧИТАТЬПУСТОТЫ) и строку «поля не заполнены», которые склеены знаком «&» (операция конкатенации).

Особенности использования функции ЕПУСТО в Excel

Функция ЕПУСТО в Excel относится к числу логических функций (выполняющих проверку какого-либо условия, например, ЕСЛИ, ЕССЫЛКА, ЕЧИСЛО и др., и возвращающих результаты в виде данных логического типа: ИСТИНА, ЛОЖЬ). Синтаксическая запись функции:

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

1. Если в качестве аргумента функции было явно передано какое-либо значение (например, =ЕПУСТО(ИСТИНА), =ЕПУСТО(«текст»), =ЕПУСТО(12)), результат ее выполнения – значение ЛОЖЬ.
2. Если требуется, чтобы функция возвращала значение ИСТИНА, если ячейка не является пустой, ее можно использовать совместно с функцией НЕ. Например, =НЕ(ЕПУСТО(A1)) вернет ИСТИНА, если A1 не является пустой.
3. Запись типа =ЕПУСТО(АДРЕС(x;y)) всегда будет возвращать значение ложь, поскольку функция АДРЕС(x;y) возвращает ссылку на ячейку, то есть непустое значение.
4. Функция возвращает значение ЛОЖЬ даже в тех случаях, когда в переданной в качестве аргумента ячейке содержится ошибка или ссылка на ячейку. Это суждение справедливо и для случаев, когда в результате выполнения какой-либо функции в ячейку была выведена пустая строка. Например, в ячейку A1 была введена формула =ЕСЛИ(2>1;””;ЛОЖЬ), которая вернет пустую строку «». В этом случае функция =ЕПУСТО(A1) вернет значение ЛОЖЬ.
5. Если требуется проверить сразу несколько ячеек, можно использовать функцию в качестве формулы массива (выделить требуемое количество пустых ячеек, ввести формулу «=ЕПУСТО(» и в качестве аргумента передать диапазон исследуемых ячеек, для выполнения использовать комбинацию клавиш Ctrl+Shift+Enter)

Есть именной диапазон L7:L68. В нем строки, пустые или с текстом (постоянно меняются) .
Как в другой диапазон (столбец) выбрать из диапазона L7:L68 все непустые строки, и выстроить их подряд без пробелов?

Пример:
ааа
ббб
(пустая строка)
ллл
(пустая строка)
жжж

надо чтобы в другой столбец собрался текст подряд:
ааа
ббб
ллл
жжж

Помогла ли Вам статья?

Удаление пустых ячеек в Microsoft Excel

​Смотрите также​ не работает?​If arr1(i, 2)​ maxCln)​ равно остается медленной?​Nic70y​’ переносим ячейки,​на вкладке​ нее такую страшноватую​Нажимаем «Ок». У​ в формулах. Если​

​ самого простого случая​редактор запросов​

Алгоритмы удаления

​ с выделением групп​ и в предыдущий​«OK»​«OK»​При выполнении задач в​Вариант, предложенный вам​ > arr1(i +​dC = 0​ Тогда единственный вариант​, у меня 3000​ анализируя содержимое​Формулы (Formulas)​ формулу:​

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

​ приложении Excel может​AndreTM​ 1, 2) Then​ ‘ delta Colunm​ — считать все​ строк и под​’ работаем с​в новых версиях​=ЕСЛИ(СТРОКА()-СТРОКА(НетПустых)+1>ЧСТРОК(ЕстьПустые)-СЧИТАТЬПУСТОТЫ(ЕстьПустые);»»;ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(ЕстьПустые<>»»;СТРОКА(ЕстьПустые);СТРОКА()+ЧСТРОК(ЕстьПустые)));СТРОКА()-СТРОКА(НетПустых)+1);СТОЛБЕЦ(ЕстьПустые);4)))​ будут видны только​

Способ 1: выделение групп ячеек

​ формула потом не​ ее.​ не изменяя существующий​ и быстрый. Но​

1. ​ наименование данной области.​В любом из указанных​После этих манипуляций все​ наступить необходимость удалить​, прекрасно работает.​k = i​ если выводить не​​ данные в массив​​ 200 столбцов. Медленно​

   

2. ​ выделенной областью​ Excel. В категории​​В английской версии это​​ пустые строки. Их​ сработает, а сама​​Есть еще несколько методов​​ запрос в книге,​

   
3. ​ ситуации бывают разные.​​ Мы решили дать​​ в предыдущем пункте​ пустые элементы внутри​​ пустые ячейки. Они​​Вы это пробовали?​fOut = 0​​ в А1​​ и провести преобразования​

   

4. ​ удаляет. Эксель виснет.​If Selection.Count =​Определенные пользователем (User Defined)​ будет:​ видно по столбцу​ программа выдаст ошибку​ для проведения таких​ в разделе​​ Поэтому, как дополнительные​​ ей название​

   

5. ​ вариантов пустые элементы​ указанного диапазона будут​ зачастую являются ненужным​ Должно работать достаточно​buff1 = arr1(i,​​dR = 0​​ уже с ним…​​ Попробую на ночь​​ 1 Then Exit​

​выберите нашу функцию​=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),»»,INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>»»,ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))​ адресов строк. Можно​ о присутствии в​

Способ 2: условное форматирование и фильтрация

​ операций. Одним из​Получение внешних данных​ способы, можно использовать​«Без_пустых»​ будут скрыты. Выделяем​ удалены.​ элементом и только​ быстро (приемлимо быстро).​ 1)​ ‘ delta Row​KSV​ оставить.​ Sub​NoBlanks​Причем ввести ее надо​ подкрасить последнюю пустую​

1. ​ вычислениях ссылки на​ решений вопроса того,​на вкладке ленты​​ варианты с фильтрованием​​.​​ диапазон оставшихся ячеек.​​Удалить пустые ячейки можно​ увеличивают общий массив​Можно еще на​​arr1(i, 1) =​​If SheetCheck(lName) =​:​​_Boroda_​​For Each col​.​​ как формулу массива,​​ ячеку, чтобы виднее​

   

2. ​ несуществующие или некорректные​ как удалить пустые​Power Query​​ и применением комплексной​​Выделяем двойным щелчком левой​ На вкладке​ также путем применения​ данных, чем запутывают​ время сортировки временно​​ arr1(i + 1,​​ 0 Then Sheets.Add.Name​

   

3. ​regnus​: Не уверен, но​ In Selection.Columns​В качестве аргумента функции​ т.е. после вставки​ было, сколько пустых​ данные. Поэтому в​ строки в «Экселе»,​выберите​​ формулы.​​ кнопки мышки первую​​«Главная»​​ условного форматирования и​​ пользователя. Определим способы,​​ отключать обновление экрана​ 1)​​ = lName​​, я не стал​

   

4. ​ в качестве предположения​Set cellTo =​ укажите исходный диапазон​ нажать не​ строк. Выделяем пустые​ такой ситуации следует​ может стать применение​​Из других источников >​​Автор: Максим Тютюшев​​ ячейку условного диапазона​​в блоке настроек​ последующей фильтрации данных.​ какими можно быстро​ и авторасчет формул​

   

   ​arr1(i + 1,​Application.StatusBar = «Идёт​ делать «Нужно2», т.к.​ — возможно, код​ Nothing​​ с пустотами (B3:B10)​​Enter​ строки и удаляем​​ быть особо внимательным,​​ инструмента сортировки.​

   

5. ​ Пустой запрос​Примечание:​«Без_пустых»​«Буфер обмена»​ Этот метод сложнее​ удалить пустые элементы.​​ (если их у​​ 1) = buff1​​ считывание и сортировка​​ судя по коду​ будет работать быстрее,​​For Each cell​​ и нажмите​

   
6. ​(как обычно), а​ функцией «Удалить строку».​ чтобы не нарушить​Для этого нужно после​. В видео показан​Надстройка​(у вас он​выполняем щелчок по​ предыдущего, но, тем​​Скачать последнюю версию​​ вас много)​

   

7. ​buff1 = arr1(i,​ данных…»​ (не проверял), процедура​ если удалять пустые​ In col.Cells​Ctrl+Shift+Enter​Ctrl+Shift+Enter​ Теперь фильтром возвращаем​ целостность математических или​ выделения всей таблицы​ один из способов​Power Query​ может назваться и​ кнопке​ не менее, некоторые​

​ Excel​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub testColumns00()​

​ 2)​​Application.ScreenUpdating = False​testColumns00()​

Способ 3: применение сложной формулы

​ не сверху вниз,​If Not IsEmpty(cell)​, чтобы ввести функцию​. Теперь формулу можно​ все заполненные строки​

1. ​ других операций. В​ (Ctrl + A)​ отображения​предлагается в Excel 2016​ по-другому). Вставляем в​«Копировать»​ пользователи предпочитают именно​Прежде всего, нужно разобраться,​​Dim c As​​arr1(i, 2) =​

   

2. ​With Sheets(lNmBasa)​от​​ как по умолчанию​​ Then​ как формулу массива.​ скопировать вниз, используя​ в таблице.​ остальном же все​ в разделе данных​редактора запросов​​ в виде группы​​ неё формулу следующего​.​ его. Кроме того,​ а действительно ли​​ Range​​ arr1(i + 1,​

   

3. ​For j =​AndreTM​ Excel делает, а​If Not cellTo​regnus​ автозаполнение (потянуть за​Третий вариант.​ просто. В зависимости​ выбрать меню сортировки​​.​​ команд​

   

4. ​ типа:​Затем выделяем любую пустую​ нужно сразу оговориться,​ можно в конкретном​Application.ScreenUpdating = False’​ 2)​​ 1 To maxCln​​должна сортировать быстро.​

   
5. ​ насильно прописать удаление​ Is Nothing Then​: Как удалить пустые​​ черный крестик в​​Как удалить пустые​ от того, какой​ и фильтра. Тут​Всем пользователям, которые работают​Скачать и преобразовать​

   ​=ЕСЛИ(СТРОКА()-СТРОКА(Без_пустых)+1>ЧСТРОК(С_пустыми)-СЧИТАТЬПУСТОТЫ(С_пустыми);"";ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(С_пустыми "";СТРОКА(С_пустыми);СТРОКА()+ЧСТРОК(С_пустыми)));СТРОКА()-СТРОКА(Без_пустых)+1);СТОЛБЕЦ(С_пустыми);4)))​

   ​ область на том​ что этот способ​ массиве или таблице​ откл. обновление экрана​arr1(i + 1,​​k = 0​​Единственное, ее можно​ по циклу снизу​​cell.Copy cellTo​​ ячейки в столбцах​

   

6. ​ правом нижнем углу​ ячейки формулой, смотрите​ результат должен быть​ применяется команда упорядочивания​ с электронными таблицами​. Информация в этой​Так как это формула​ же или на​ подходит только в​ удалять пустые ячейки?​ActiveSheet.EnableCalculation = False’​ 2) = buff1​For i =​ чуть дописать, если​ вверх.​cell.ClearContents​ сразу в 100​ ячейки) — и​​ в статье «Как​​ получен, можно применять​

   

7. ​ от минимального к​ в офисном редакторе​ статье относится и​ массива, то для​ другом листе. Выполняем​ том случае, если​ Указанная процедура приводит​ откл. авторасчет формул​End If​ 1 To maxRow​ у вас выделенный​​Richman​​Set cellTo =​​ столбцах?​​ мы получим исходный​ удалить пустые ячейки​​ ту или иную​​ максимальному (пустые строки​​ Excel, очень часто​​ к Power Query,​

   

8. ​ выведения расчета на​ щелчок правой кнопкой​ значения находятся в​ к смещению данных,​’ работаем с​​Next i​​If .Cells(i, j)​​ диапазон начинается с​​: Формулами и доп​

   

9. ​ cellTo.Offset(1)​1. Нужно все​ диапазон, но без​ в Excel».​ методику.​ окажутся снизу). После​ приходится убирать пустые​ и к новой​

​ экран нужно нажать​​ мыши. В появившемся​ одном столбце и​

​ а это далеко​ выделенной областью​If fOut Then​ <> «» Then​ первой строки на​ строками нужно?​End If​ данные (не пустые​ пустых ячеек:​Если появилась необходимость,​Автор: Панькова Оксана Владимировна​ этого их можно​

​ ячейки, находящиеся в​

lumpics.ru

Удаление столбцов (Power Query)

​ группе. Дополнительные сведения​​ комбинацию клавиш​​ контекстном списке действий​​ не содержат формулы.​ не всегда является​’ сначала выполняем​​ Exit Do​​k = k + 1​ листе, или чтобы​нужно1​Else​ ячейки) вывести к​Если есть подозрение, что​ вставить в заполненную​Как убрать пустые​

​ удалить. Если порядок​ строках, чтобы они​ см. в статье​

Удаление столбцов

​Ctrl+Shift+Enter​​ в параметрах вставки​

1. ​Выделяем диапазон, который собираемся​ допустимым. По сути,​ «сжатие» по строкам​RBdr = k​arr2(k, j) =​​ не затереть строку​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(Лист2!B$1:B$9;НАИМЕНЬШИЙ(ЕСЛИ(Лист2!B$2:B$9<>»»;СТРОКА(Лист2!B$2:B$9);100000);СТРОКА(1:1)));»»)​​If cellTo Is​​ верху листа, чтобы​

2. ​ вам часто придется​​ таблицу пустые строки​​ строки в Excel​​ построения данных важен,​​ не загромождали рабочее​​ Функция «Скачать и​​, вместо обычного нажатия​ выбираем пункт​​ обрабатывать. Находясь во​​ элементы можно удалять​If Selection.Count =​​fOut = 1​​ .Cells(i, j)​

   

​ перед выделенным диапазоном.​​потом копировать——Правая кнопка​

1. ​ Nothing Then​ понимать длину не​ повторять процедуру удаления​ через определенный промежуток,​, как удалить отфильтрованные​​ сначала необходимо использовать​​ пространство. Сейчас будет​​ преобразовать» в Excel 2016.​​ кнопки​

2. ​«Значения»​ вкладке​

3. ​ только в двух​ 1 Then Exit​​For i =​​End If​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub testColumns00()​​ ——специальная вставка—-значения​​Set cellTo =​ пустых ячеек каждого​​ пустых ячеек из​​ то смотрите статью​

   

​ строки в Excel​​ вставку пустого столбца​​ рассмотрено, как в​​Если запрос содержит ненужные​Enter​.​«Главная»​​ случаях:​​ Sub​ RBdr To LBdr​​Next i​​Dim c As​KSV​ cell​ столбца.​​ диапазонов, то лучше​​ «Вставить пустые строки​, поможет функция «Сортировка​ со сквозной нумерацией.​ таблице «Эксель» удалить​​ столбцы, их можно​​.​​Как видим, произошла вставка​​, жмем на пиктограмму​​Если строка (столбец) полностью​PackByRows​​ + 1 Step​DoEvents​ Range​​: Странно…​​End If​

support.office.com

Как удалить пустые строки в «Экселе»: несколько простых методов

​2. Нужно подровнять​ один раз добавить​ в Excel через​ и фильтр в​ После удаления строк​ пустые строки. Некоторые​ удалить.​Но, как видим, заполнилась​ данных без сохранения​«Условное форматирование»​ является пустой (в​With Selection​ -1​arrK(j, 1) =​’ работаем с​600 тыс. ячеек​End If​

Как удалить пустые строки в «Экселе» двумя простейшими методами?

​ столбцы по длине​ в стандартный набор​ одну».​ Excel» и немного​ снова производите упорядочивание​ решения пригодятся и​С помощью ленты​ только одна ячейка.​ форматирования. Теперь можно​, которая, в свою​ таблицах);​.Rows(1).Insert​If arr1(i, 2)​ j​ выделенной областью​ для Excel это​Next​

​ (слева-направо)​ свою функцию для​В Excel можно​ хитрости помогут быстро​ по пронумерованному столбцу.​ в других ситуациях.​редактора запросов​ Для того, чтобы​ удалить первичный диапазон,​ очередь, располагается в​Если ячейки в строке​For Each c​ < arr1(i -​arrK(j, 2) =​’ сначала выполняем​ не тот объем,​Next​Есть и пустые​ удаления пустых ячеек,​ настроить так, что​

​ удалить лишние пустые​Решить вопрос того, как​ Например, при аналогичных​Выберите столбец, который вы​ заполнились и остальные,​ а на его​ блоке инструментов​ и столбце логически​ In .Columns​ 1, 2) Then​ k​ «сжатие» по строкам​ чтобы виснуть. (если,​End Sub​ столбцы.​ и пользоваться ей​ вместо цифр, например,​ строки в огромной​ удалить пустые строки​ действиях со столбцами​ хотите удалить. Чтобы​ нужно скопировать формулу​ место вставить тот,​

Как в «Экселе» удалить пустые строки с помощью сортировки?

​«Стили»​ не связаны друг​c.Cells(0) = WorksheetFunction.CountA(c)​k = i​Next j​If Selection.Count =​ конечно, у вас​Sub testColumns00()​

​Есть:​ во всех последующих​ отрицательных, будут писаться​ таблице.​ в «Экселе», можно​ или отдельными ячейками.​ выделить несколько столбцов,​ на оставшуюся часть​ который мы получили​. Переходим в пункт​ с другом (в​Next​fOut = 0​Call sortArr(arrK)​ 1 Then Exit​ не пентиум III​’ работаем с​111 222 4​ случаях.​

Удаление строк с использованием фильтра

​ нули. Читайте статью​Часто, в большой​ и при помощи​Для начала стоит сказать,​ щелкните их, удерживая​ диапазона. Это можно​ в ходе вышеописанной​ открывшегося списка​ массивах).​Set c =​buff1 = arr1(i,​

​For i =​ Sub​)​ выделенной областью​333 44 55​Для этого откройте редактор​ «Как заменить в​ таблице после копирования​ простого фильтра. Устанавливают​ что при проведении​ нажатой клавишу​

Выделение групп ячеек

​ сделать с помощью​ процедуры, а можно​«Правила выделения ячеек»​Если пустых ячеек мало,​ .Offset(-1).Resize(.Rows.Count + 1)​ 1)​ 1 To maxCln​PackByRows​Попробуйте так (должно​

​’ сначала выполняем​11 555​ Visual Basic (​ Excel отрицательное число​ или корректировки, появляется​ его путем выделения​ операций такого рода​CTRL​ маркера заполнения. Устанавливаем​ продолжать работу с​. В появившемся списке​ то их вполне​With .Parent.Sort​arr1(i, 1) =​For j =​

Вместо послесловия

​With Selection​ работать оч. быстро​ «сжатие» по строкам​111 22 444​ALT+F11​ на ноль».​ много не нужных​ столбца, группы столбцов​ нужно быть крайне​или​ курсор в нижний​ данными на новом​ действий выбираем позицию​ можно удалить с​With .SortFields​ arr1(i — 1,​ 1 To maxRow​.Rows(1).Insert​ на любом кол-ве)​If Selection.Count =​55​), вставьте новый пустой​Имеем диапазон ячеек с​ пустых строк. Удалять​ или всей таблицы.​ внимательным. Дело в​SHIFT​ правый угол ячейки,​ месте. Тут все​«Больше…»​ помощью обычного ручного​.Clear​ 1)​arrOut(j, maxCln +​For Each c​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub PackByRows() ‘ реализация​

fb.ru

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

​ 1 Then Exit​11 44​​ модуль (меню​ данными, в котором​​ по одной строке​ Кроме того, применяют​ том, что ячейки​.​ содержащей комплексную функцию.​ уже зависит от​.​
​ способа удаления. Но,​.Add c.Rows(1), xlSortOnValues,​arr1(i — 1,​ 1 — i)​ In .Columns​ вашего «Нужно1″​ Sub​1​Insert — Module​ есть пустые ячейки:​
​ – это долго.​
​ команду «Фильтр» в​ могут содержать скрытые​На ленте​ Курсор должен преобразоваться​ конкретных задач и​Открывается окошко условного форматирования.​

​ если таких незаполненных​ xlDescending​
​ 1) = buff1​ = arr2(j, arrK(i,​c.Cells(0) = WorksheetFunction.CountA(c)​Dim r&, c&,​testRows00​Нужно1​) и скопируйте туда​Задача — удалить пустые​
​ Но, есть способ,​ разделе сортировки.​ формулы (бесцветный текст).​редактора запросов​
​ в крестик. Зажимаем​ личных приоритетов пользователя.​ В левое поле​ элементов большое количество,​End With​
​buff1 = arr1(i,​ 1))​Next​ i&, n&, v()​
​’ выделенная область​111 222 333​ текст этой функции:​ ячейки, оставив только​ удалить все пустые​Теперь остается нажать на​ А сами вычисления​нажмите кнопку​ левую кнопку мыши​Урок:​
​ вписываем цифру​
​ то в этом​
​.SetRange c​ 2)​Next j​Set c =​v = Selection.Value​

​ та же​ 4 55​Function NoBlanks(DataRange As​ ячейки с информацией.​ строки одним махом.​ сам фильтр, отобразить​ могут ссылаться на​Удалить столбцы​ и тянем его​Условное форматирование в Excel​«0»​ случае, данную процедуру​.Header = xlNo​arr1(i, 2) =​Next i​
​ .Offset(-1).Resize(.Rows.Count + 1)​
​n = UBound(v)​’ ПРЕДУПРЕЖДЕНИЕ: одна​11 22 44​ Range) As Variant()​Выделяем исходный диапазон​
​Первый вариант.​ только пустые ячейки​ строки, в которых​и выберите команду​ вниз до самого​Урок:​. В правом поле​ нужно автоматизировать.​
​.Orientation = xlLeftToRight​ arr1(i — 1,​End With​With .Parent.Sort​ ‘ кол-во строк​ строка выше выделенного​ 555​ Dim N As​

excel-office.ru

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

Постановка задачи

​Жмем клавишу​У нас такая​ и удалить их.​

​ данные отсутствуют, но​Удалить столбцы​ конца диапазона​

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

1. ​Сортировка и фильтрация данных​
2. ​ выбираем любой цвет,​​Наиболее простой способ удалить​​.Apply​​ 2)​ ​Sheets(lName).Cells(1 + dR,​​With .SortFields​ в выделенном диапазоне​​ диапазона будут очищена​ ​111 444 55​​ Long Dim N2​​F5​​ таблица. В нашем​
   
   ​ Но тут есть​ их предполагается ввести​
3. ​. Можно также выбрать​«Без_пустых»​ в Excel​ но можно оставить​​ пустые элементы –​ ​End With​​arr1(i — 1,​

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

​ 1 + dC).Resize(maxRow,​.Clear​For c =​​For Each col​​11 44​​ As Long Dim​​, далее кнопка​ примере — несколько​ одна загвоздка. Нельзя​​ в дальнейшем. Такие​ команду​ ​.​Кроме того, убрать пустые​

​ настройки по умолчанию.​​ это воспользоваться инструментом​​.Rows(0).Delete​​ 2) = buff1​​ maxCln).ClearContents​.Add c.Rows(1), xlSortOnValues,​ 1 To UBound(v,​ In Selection.Columns​1​

​ Rng As Range​Выделить​ строк, но может​ применять такое удаление​ ячейки с точки​

​Удалить другие столбцы​

​Как видим, после этого​ ячейки из массива​
​ Щелкаем по кнопке​

​ выделения групп ячеек.​End With​End If​Sheets(lName).Cells(1 + dR,​​ xlDescending​​ 2)​​col.Offset(-1).Cells(1, 1).Value =​​Нужно2​ Dim MaxCells As​(Special)​ быть строк очень​ для нескольких столбцов,​ зрения программы пустыми​, чтобы удалить все​ действия мы имеем​ можно, применив сложную​

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

​«OK»​Выделяем диапазон на листе,​’ вкл. все,​Next i​ 1 + dC).Resize(maxRow,​End With​’ находим первую​ WorksheetFunction.CountA(col)​111 4 55​ Long Dim Result()​. В открывшмся окне​ много.​

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

​If fOut Then​ maxCln) = arrOut​.SetRange c​ пустую ячейку в​Next​ 222 333​ As Variant Dim​ выбираем​Строки 2, 4, 6,​ строке хоть одна​Но посмотрим, как в​кроме​ подряд расположены заполненные​ нескольких функций.​Как видим, все ячейки​ проводить операцию поиска​ActiveSheet.EnableCalculation = True​ Exit Do​Application.ScreenUpdating = True​.Header = xlNo​ столбце​Set rngS =​11 44 555​ R As Long​Пустые ячейки​ 8 нужно удалить.​ ячейка содержит данные.​ «Экселе» удалить пустые​выделенных.​ ячейки. Но выполнять​Прежде всего, нам нужно​ указанного диапазона, в​ и удаления пустых​Application.ScreenUpdating = True​

​LBdr = k​Application.StatusBar = «Вывод​.Orientation = xlLeftToRight​For i =​ Selection.Offset(-1).Resize(Selection.Rows.Count + 1)​ 22​

1. ​ Dim C As​(Blanks)​
2. ​Выделяем таблицу, нажимаем​​Наконец, рассмотрим еще один​ строки, если они​​С помощью контекстного меню​​ различные действия с​ ​ будет дать имя​​ которых находятся значения,​​ элементов. Жмем на​​End Sub​Loop​​ данных закончен»​​.Apply​​ 1 To n​​With rngS.Parent.Sort​
3. ​111 444 55​ Long MaxCells =​и жмем​ кнопку «Сортировка и​​ универсальный метод. Он​​ действительно являются таковыми.​редактора запросов​

planetaexcel.ru

Как удалить пустые ячейки в столбцах сразу в 100 столбцах? (Формулы/Formulas)

​ этими данными мы​​ диапазону, который подвергается​ были выделены в​ функциональную клавишу на​gling​
​End Sub​End Sub​End With​If Len(v(i, c))​.SortFields.Add Key:=rngS.Rows(1), _​11 44​ Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count,​
​ОК​ фильтр» на закладке​ позволяет решить проблему​
​ Самым простым методом​Выберите столбец, который вы​
​ не сможем, так​
​ трансформации. Выделяем область,​
​ выбранный цвет, а​
​ клавиатуре​
​: Ошибка, должно быть​
​AndreTM​
​Function SheetCheck(ByVal ShName​
​.Rows(0).Delete​
​ = 0 Then​
​SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal​1​
​ DataRange.Cells.Count) ReDim Result(1​.​
​ «Главная» и выбираем​
​ того, как в​
​ считается обычное выделение​
​ хотите удалить. Чтобы​
​ как они связаны​ делаем щелчок правой​
​ пустые остались белыми.​F5​
​ так​
​: А если в​
​ As String) As​
​End With​
​ Exit For​.SetRange rngS​Спасибо)​

​ To MaxCells, 1​​Выделяются все пустые​
​ «Сортировать от минимального​
​ «Эксель» удалить пустые​
​ при помощи мыши​
​ выделить несколько столбцов,​ формулой массива. Выделяем​
​ кнопкой мышки. В​

​ Опять выделяем наш​​.​regnus​

​ щелкните их, удерживая​​ весь диапазон​
​ активировавшемся меню выбираем​ диапазон. В этой​Запускается небольшое окошко, которое​: Пробую ваш файл,​ отключение/включение​
​Dim x As​
​Udik​' проверяем остальные​
​.Orientation = xlLeftToRight​ экселе, тут форматирование​
​ Each Rng In​Даем в меню команду​ от максимального к​
​ от и до​ кнопкой. Оно производится​
​ нажатой клавишу​«Без_пустых»​
​ пункт​ же вкладке​
​ называется​ т.к. вроде он​
​Application.ScreenUpdating​ Byte​
​: Вот попробовал сварганить​
​ ячейки в столбце​
​.SortMethod = xlPinYin​ не то.​
​ DataRange.Cells If Rng.Value​
​ на удаление выделенных​
​ минимальному).​ определенного номера или​
​ движением вверх или​CTRL​
​. Жмем на кнопку​
​«Присвоить имя…»​
​«Главная»​
​«Переход»​
​ делает 2 варианта.​
​- то тоже​
​x = 0​ с сортировкой столбцов,​
​For r =​.Apply​
​Nic70y​ <> vbNullString Then​ ячеек: правой кнопкой​
​Подробнее о сортировке​
​ значения.​ вниз по полю​
​или​«Копировать»​.​
​щелкаем по кнопке​. Жмем в нем​
​500 строк и​ быстродействие поднимется.​
​For Each ws​
​ вышло многа букфф​ i + 1​
​End With​
​: F5​
​ N = N​
​ мыши​
​ в таблице смотрите​
​В этом случае следует​
​ нумерации слева. Затем,​
​SHIFT​
​, которая размещена во​
​Открывается окно присвоения наименования.​
​«Сортировка и фильтр»​
​ кнопку​​ 100 столбцов обрабатывает​Вотбы вам сразу​
​ In ActiveWorkbook.Sheets​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>​​ To n​
​rngS.Rows(1).ClearContents​
​Выделить​ + 1 Result(N,​-​ в статье «Сортировка​
​ использовать раздел редактирования​ используя правый клик,​.​ вкладке​
​ В поле​, расположенной в группе​
​«Выделить…»​ за 1-3 минуты.​ спросить — «как​
​If ws.Name =​
​Public Sub test()​If Len(v(r, c))​End Sub​

​пустые ячейки​​ 1) = Rng.Value​​Удалить ячейки (Delete Cells)​​ в Excel».​ на главной панели​ вы вызываете контекстное​Щелкните правой кнопкой мыши​«Главная»​«Имя»​

​«Редактирование»​​.​ Точно не скажу,​ БЫСТРО проделать данную​ ShName Then​Dim i As​ Then​Смеяться не надо​ПКМ — удалить​ End If Next​со сдвигом вверх.​Все заполненные строки​

​ с выбором кнопки​​ меню, где выбираете​ выделенные столбцы.​
​в блоке инструментов​
​даем любое удобное​
​. В открывшемся меню​Открывается следующее окно –​

​ т.к. при обработке​​ операцию на таком-то​
​x = 1​ Integer, j As​v(i, c) =​Мне было просто​ячейки, со сдвигом​ Rng For N2​​Для упрощения дадим нашим​
​ будут стоять вверху​ «Найти и выделить».​ строку удаления. Но​​В контекстном меню выберите​«Буфер обмена»​
​ название. Главное условие​ жмем на кнопку​
​«Выделение групп ячеек»​
​ виснет.​ объёме"...​Exit For​
​ Integer, k As​ v(r, c)​ нечего делать​
​ вверх​ = N +​ рабочим диапазонам имена,​
​ таблицы, а пустые​ Из выпадающего меню​
​ такая методика применима​ команду​.​
​ – в нем​
​«Фильтр»​. Устанавливаем в нем​
​А 3000 строк​gling​End If​
​ Integer​v(r, c) =​
​Например, предложенный​то?​
​ 1 To MaxCells​ используя​
​ строки соберутся внизу​
​ выбирается строка выделения​
​ только в том​
​Удалить​
​После этого выделяем первоначальный​

​ не должно быть​​.​
​ переключатель в позицию​
​ и 100 столбцов​: Собрал конструкцию из​

​Next ws​​Dim dC As​​ Empty​​Nic70y​Serge_007​ Result(N2, 1) =​Диспетчер Имен (Name Manager)​​ таблицы.​​ группы ячеек. А​​ случае, если ячейки​​, чтобы удалить выделенные​ массив данных. Щелкаем​ пробелов. Для примера​После этих действий, как​«Пустые ячейки»​ появляется жесть.​ разных файлов, быстро​

​SheetCheck = x​​ Integer, dR As​​i = i + 1​​код​: А Вы его​ vbNullString Next N2​на вкладке​​Осталось удалить эти​​ в окошке условия​​ располагаются подряд.​​ столбцы, или​
​ правой кнопкой мыши.​ мы присвоили диапазону​ видим, в верхнем​. Выполняем щелчок по​Эксель зависает минут​ ли работает не​End Function​ Integer​​End If​
​Код200?'200px':''+(this.scrollHeight+5)+'px');">Sub testRows()​ и не применяли:​
​ If Application.Caller.Rows.Count =​Формулы (Formulas)​
​ строки. Чтобы быстро​ активируется та, которая​
​Как удалить пустые строки​Удалить другие столбцы​ В открывшемся списке​
​ наименование​
​ элементе столбца появилась​
​ кнопке​
​ на 20 и​ знаю. Диапазон сортировки​
​Private Sub sortArr(arr1()​
​Const maxRow As​
​Next r, c​' используя встроенные​
​111​
​ 1 Then NoBlanks​
​или - в​
​ это сделать, есть​ указывает на пустые​
​ в «Экселе», если​
​, чтобы удалить все​
​ в группе​
​«С_пустыми»​
​ пиктограмма символизирующая фильтр.​
​«OK»​
​ более​
​ нужно указывать в​
​ As Integer)​

​ же, нужно помнить​​ меню правого клика​Power Query​ который содержит формулу,​​ ячеек. Аналогично кликаем​​Можно также сделать немного​.​
​:​ в один.​RBdr = UBound(arr1,​ maxCln, 1 To​ в моём варианте​

​ Sub​​: Типа вот так?​ ячеек, например F3:F10.​ строго одного размера,​Получилось так.​ и том, что​ выбираете соответствующую операцию.​. В видео показано​ теперь можно удалить.​ правой кнопкой мыши​ по-другому. Кликаем по​Открывается маленькое окошко, в​

​regnus​​Не сортирует столбцы.​
​ 1)​
​ 2) As Integer​ (конечно, заменив в​
​Selection.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp​
​(Код надо поместить​

​Идем в меню​​ а расположены могут​​Второй вариант.​​ в некоторых ячейках​ Заметьте, если использовать​
​ окно​​Урок:​​ и, вызвав контекстное​
​ значку фильтрации. В​ котором нужно выбрать,​, Вы можете выложить​
​удалено администрацией​Do While 1​Dim arr2(1 To​ его вызове процедуру​End Subне подходит​ в общий модуль,​​Вставка - Функция (Insert​
​ быть где угодно​Так же устанавливаем​
​ может находиться скрытый​ клавишу Del, будет​
​редактора запросов​Как присвоить имя ячейке​
​ меню, переходим по​ появившемся меню снимаем​
​ что именно следует​ видео с экрана?​
​KSV​fOut = 1​ maxRow, 1 To​
​testRows00​
​ по причине, изложенной​
​ и вызывать, предварительно​
​ - Function)​ относительно друг друга.​
​ фильтр. Но в​
​ текст. А сами​
​ произведена только очистка​, которое отображается после​
​ в Excel​
​ пункту​
​ галочку с позиции​
​ удалить. Оставляем настройки​Udik​
​:​
​For i =​
​ maxCln)​
​на​
​ в комментариях...​
​ выделив область для​
​или жмем на кнопку​
​Теперь выделим первую ячейку​
​ диалоговом окне фильтра​ пустые ячейки могут​
​ содержимого, а не​
​ изменения запроса в​
​Существует несколько способов удаления​

​«Присвоить имя…»​​«Пустые»​ по умолчанию –​

​: Я на своём​​regnus​ 1 To RBdr​Dim arrOut(1 To​
​PackByRows​regnus​ обработки)​Вставить функцию​ второго диапазона (D3)​ ставим галочку только​
​ быть зависимыми при​ удаление целых строк.​ книге Excel. Чтобы​
​ пустых элементов в​.​. После этого щелкаем​
​«Ячейки, со сдвигом вверх»​ попробовал 3000х200 -​, что у вас​
​ — 1​ maxRow, 1 To​от KSV всё​
​:​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub testRows00()​(Insert Function)​

​ и введем в​​ у слова «Пустые».​​ ссылке на них​​ В прочем, для​ просмотреть​

​ Microsoft Excel. Вариант​​В открывшемся окне, как​ по кнопке​. Жмем на кнопку​

excelworld.ru

​ ок. 35с.​

Удаление пустых ячеек в Microsoft Excel

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

Алгоритмы удаления

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

• Если строка (столбец) полностью является пустой (в таблицах);
• Если ячейки в строке и столбце логически не связаны друг с другом (в массивах).

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

Способ 1: выделение групп ячеек

Наиболее простой способ удалить пустые элементы – это воспользоваться инструментом выделения групп ячеек.

Выделяем диапазон на листе, над которым будем проводить операцию поиска и удаления пустых элементов. Жмем на функциональную клавишу на клавиатуре F5.

Запускается небольшое окошко, которое называется «Переход». Жмем в нем кнопку «Выделить…».

Как видим, все пустые элементы указанного диапазона были выделены. Кликаем по любому из них правой кнопкой мыши. В запустившемся контекстном меню щелкаем по пункту «Удалить…».

После этих манипуляций все пустые элементы внутри указанного диапазона будут удалены.

Способ 2: условное форматирование и фильтрация

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

Выделяем диапазон, который собираемся обрабатывать. Находясь во вкладке «Главная», жмем на пиктограмму «Условное форматирование», которая, в свою очередь, располагается в блоке инструментов «Стили». Переходим в пункт открывшегося списка «Правила выделения ячеек». В появившемся списке действий выбираем позицию «Больше…».

Открывается окошко условного форматирования. В левое поле вписываем цифру «0». В правом поле выбираем любой цвет, но можно оставить настройки по умолчанию. Щелкаем по кнопке «OK».

Как видим, все ячейки указанного диапазона, в которых находятся значения, были выделены в выбранный цвет, а пустые остались белыми. Опять выделяем наш диапазон. В этой же вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр», расположенной в группе «Редактирование». В открывшемся меню жмем на кнопку «Фильтр».

После этих действий, как видим, в верхнем элементе столбца появилась пиктограмма символизирующая фильтр. Жмем на неё. В открывшемся списке переходим в пункт «Сортировка по цвету». Далее в группе «Сортировка по цвету ячейки» выбираем тот цвет, которым произошло выделение в результате условного форматирования.

Можно также сделать немного по-другому. Кликаем по значку фильтрации. В появившемся меню снимаем галочку с позиции «Пустые». После этого щелкаем по кнопке «OK».

В любом из указанных в предыдущем пункте вариантов пустые элементы будут скрыты. Выделяем диапазон оставшихся ячеек. На вкладке «Главная» в блоке настроек «Буфер обмена» выполняем щелчок по кнопке «Копировать».

Затем выделяем любую пустую область на том же или на другом листе. Выполняем щелчок правой кнопкой мыши. В появившемся контекстном списке действий в параметрах вставки выбираем пункт «Значения».

Способ 3: применение сложной формулы

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

Прежде всего, нам нужно будет дать имя диапазону, который подвергается трансформации. Выделяем область, делаем щелчок правой кнопкой мышки. В активировавшемся меню выбираем пункт «Присвоить имя…».

Открывается окно присвоения наименования. В поле «Имя» даем любое удобное название. Главное условие – в нем не должно быть пробелов. Для примера мы присвоили диапазону наименование «С_пустыми». Больше никаких изменений в том окне вносить не нужно. Жмем на кнопку «OK».

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

В открывшемся окне, как и в предыдущий раз, присваиваем любое наименование данной области. Мы решили дать ей название «Без_пустых».

Выделяем двойным щелчком левой кнопки мышки первую ячейку условного диапазона «Без_пустых» (у вас он может назваться и по-другому). Вставляем в неё формулу следующего типа:

Так как это формула массива, то для выведения расчета на экран нужно нажать комбинацию клавиш Ctrl+Shift+Enter, вместо обычного нажатия кнопки Enter.

Но, как видим, заполнилась только одна ячейка. Для того, чтобы заполнились и остальные, нужно скопировать формулу на оставшуюся часть диапазона. Это можно сделать с помощью маркера заполнения. Устанавливаем курсор в нижний правый угол ячейки, содержащей комплексную функцию. Курсор должен преобразоваться в крестик. Зажимаем левую кнопку мыши и тянем его вниз до самого конца диапазона «Без_пустых».

Как видим, после этого действия мы имеем диапазон, в котором подряд расположены заполненные ячейки. Но выполнять различные действия с этими данными мы не сможем, так как они связаны формулой массива. Выделяем весь диапазон «Без_пустых». Жмем на кнопку «Копировать», которая размещена во вкладке «Главная» в блоке инструментов «Буфер обмена».

После этого выделяем первоначальный массив данных. Щелкаем правой кнопкой мыши. В открывшемся списке в группе «Параметры вставки» жмем на пиктограмму «Значения».

Существует несколько способов удаления пустых элементов в Microsoft Excel. Вариант с выделением групп ячеек наиболее простой и быстрый. Но ситуации бывают разные. Поэтому, как дополнительные способы, можно использовать варианты с фильтрованием и применением комплексной формулы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Постановка задачи

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

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

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

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

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

Способ 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) и скопируйте туда текст этой функции:

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

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

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

Работая с большими объемами данных в таблицах Excel, мы часто сталкиваемся с наличием пустых строк в Excel. Для того чтобы убрать пустые строки есть несколько способов:

• с помощью сортировки данных;
• с помощью фильтрации данных;
• с помощью выделения группы ячеек;
• с помощью вспомогательного столбца;

Давайте рассмотрим каждый из них.

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

Представим, что у нас есть таблица с данными продаж по разным странам и городам:

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

Для того чтобы отсортировать данные нашей таблицы проделаем следующие шаги:

• Выделим левой клавишей мыши диапазон данных таблицы, затем на вкладке “Данные” нажмем на кнопку “Сортировка”:

• В появившемся диалоговом окне сортировки выберем столбец, по которому будут отсортированы данные и укажем их порядок:

• После этого система автоматически отсортирует строки с пустыми ячейками, и разместит их внизу таблицы:

• Затем, выделите пустые строки левой клавишей мыши и удалите их, нажав правой кнопкой мыши и выбрав пункт “Удалить”:

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

Как удалить пустые строки в Excel с помощью фильтрации

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

• Выделим левой клавишей мыши диапазон данных таблицы, затем на вкладке “Данные” нажмем на кнопку “Фильтр”:

Важно! Для корректной фильтрации данных обязательно, перед нажатием кнопки “Фильтр”, выделите область со значениями таблицы, включая пустые строки. Если этого не сделать – фильтрация данных таблицы будет осуществляться до первой пустой строки!

• Нажмите на выпадающий список фильтра любого из заголовков таблицы и выберите пустые строки:

• После того как пустые строки выбраны, выделите их и удалите, как это показано на примерах выше.

У этого способа удаления пустых строк есть минус, он заключается в том, что для их удаления вам необходимо отфильтровать каждую колонку. Делать это следует внимательно, дабы не удалить строки с данными.

Как убрать пустые строки в Excel с помощью выделения группы ячеек

Для удаления пустых строк можно воспользоваться инструментом выделения группы ячеек. Для этого:

• Выделите ячейки с данными вашей таблицы;
• Перейдите в меню “Найти и выделить” и выберите пункт “Выделить группу ячеек”:

• Во всплывающем окне отметьте пункт “Пустые ячейки”:

• Система автоматически выделит все пустые ячейки:

• Для удаления выделенных строк, нажмите на любой из правой кнопкой мыши и выберите пункт “Удалить”:

• Затем, во всплывающем окне выберите пункт “Строку”:

Внимание! Будьте аккуратны с использованием данного способа! Удаляя выделенные ячейки, есть высокая вероятность того, что вы удалите строки с данными, например, в которых есть только одна пустая ячейка, а в соседних ячейках строки есть данные!

Как удалить пустые строки в Экселе с помощью вспомогательного столбца

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

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

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

Поэтому, чтобы избежать потери данных при удалении пустых строк, создадим справа от таблицы столбец, в который вставим функцию СЧИТАТЬПУСТОТЫ, в ней укажем диапазон всех ячеек каждой строки таблицы и протянем формулу до конца таблицы:

• Функция СЧИТАТЬПУСТОТЫ ведет подсчет пустых ячеек в указанном диапазоне. Так как столбцов в нашей таблице с данными 4 штуки, то если во вспомогательном столбце мы найдем значение “4” это будет означать, что эта строка в таблице пустая и ее можно удалить. Для этого отфильтруем значения таблицы по вспомогательному столбцу по числу “4” и определим пустые строки:

• Пустые строки найдены. Теперь их можно выделить и удалить.

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

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

Возможность и методы удаления

Для начала нужно выяснить, существует ли в принципе такая возможность, как удаление пустых ячеек в выбранном диапазоне данных (таблице), и подобрать оптимальный способ для реализации этой операции. Например, при простом выделении и удалении будет происходить смещение ячеек, что в некоторых случаях нежелательно или, и вовсе, недопустимо. В целом, удалить пустые ячейки проще всего при следующих условиях:

• Весь столбец (строка) не заполнен;
• Элементы в столбце (строке) не взаимосвязаны.

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

Метод 1: простое выделение и удаление

Данный метод, пожалуй, можно считать самым простым. Вот, что мы делаем:

1. Любым удобным способом производим выделение области, в которой нам нужно найти и удалить все незаполненные ячейки. После того, как выделение выполнено, нажимаем клавишу F5.
2. На экране отобразится окно перехода, в котором щелкаем “Выделить”.
3. Мы окажемся в окне выделения групп ячеек. Здесь среди все вариантов выбираем “пустые ячейки”, поставив напротив отметку, после чего жмем OK.
4. В нашем диапазоне будут отмечены все пустые ячейки. Щелчком правой кнопки мыши по любому их них открываем меню, в котором выбираем команду “Удалить”.
5. В появившемся окне останавливаемся на варианте “ячейки, со сдвигом вверх” и нажимаем OK.
6. В результате выполненных действий мы удалили все пустые ячейки, находящиеся в выделенной области.
7. Кликаем по любому элементу на листе, чтобы снять выделение.

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

Метод 2: фильтр и условное форматирование

Этот метод несколько сложнее рассмотренного выше и предполагает применение фильтра или условного форматирования с дальнейшей фильтрацией (сортировкой) данных. Однако тут есть один нюанс – его можно применять только для работы с одним столбцом, в котором, к тому же, нет формул.

Фильтрация данных:

1. Для начала нужно любым удобным способом выделить нужную область ячеек. Затем в главной вкладке в блоке инструментов “Редактирование” щелкаем по значку “Сортировка и фильтр”. В раскрывшемся перечне выбираем пункт “Фильтр”.
2. Внутри самой верхней ячейки выделенного диапазона появится характерный значок фильтра в виде небольшого квадрата с треугольником, направленным вниз. Щелкаем по нему. Откроется меню, в котором убираем галочку напротив пункта “Пустые” и жмем OK.
3. В таблице останутся только ячейки, содержащие какие-либо данные.

Условное форматирование и фильтрация:

1. Выполняем выделение диапазона ячеек. В главной вкладке в группе инструментов “Стили” нажимаем кнопку “Условное форматирование”. В раскрывшемся перечне останавливаемся на варианте “Правила выделения ячеек”, затем – выбираем “Больше”.
2. На экране отобразится окно форматирования:
3. Ко всем заполненным ячейкам будет применена цветовая схема, которую мы выбрали, в то время, как заливка пустых ячеек не изменилась. Повторно выделяем тот же самый диапазон (если выделение было снято) и включаем “Фильтр”.
4. Кликаем по значку фильтра, в открывшемся меню наводим указатель мыши на строку “Фильтр по цвету”, после чего откроется еще одно подменю, где останавливаемся на варианте “Фильтр по цвету ячейки” или “Фильтр по цвету шрифта” (в данном случае, это неважно).
5. В результате, из всего выделенного диапазона мы оставили только заполненные ячейки.

Независимо от того, каким из способов выше мы воспользовались (фильтр или условное форматирование с последующей фильтрацией), дальнейшие шаги следующие:

1. Нажимаем кнопку “Копировать” в группе инструментов “Буфер обмена” (вкладка “Главная”).
2. На этом же (или другом) листе отмечаем соразмерный диапазон, затем щелчком правой кнопки мыши по нему раскрываем меню, в котором кликаем по варианту вставки – “Значения” (значок в виде папки с цифрами 123). Тут есть один нюанс – выделять нужно диапазон, который располагается ниже самой нижней строки области с первоначальными данными.
3. Нам удалось вставить данные (без первоначального форматирования ячеек). Мы можем продолжить работу с ними здесь или перенести (скопировать) в любое другое удобное место.

Сортировка вместо фильтрации:

В данном случае, мы выполняем те же самые шаги, что и при условном форматировании. Но вместо фильтра теперь выбираем “Сортировку по цвету”.

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

Метод 3: использование формулы

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

1. Для начала нужно задать имя диапазону ячеек, с которым планируем работать. Для этого выделяем его, затем кликаем по нему правой кнопкой мыши и в контекстном меню выбираем команду “Присвоить имя”.
2. В окне создания имени в поле “Имя” указываем любое название (без пробелов). Остальные поля оставляем без изменений и нажимаем OK.
3. В любом свободном месте производим выделение соразмерного диапазона ячеек (для удобства, если возможно, лучше это сделать в тех же строках другого столбца). Затем также в контекстном меню, вызываемом щелчком правой кнопки мыши по нему, выбираем пункт “Присвоить имя”.
4. Как и для первого диапазона, задаем имя (в нашем случае – “Без_пустых_ячеек”).
5. Встаем в самую верхнюю ячейку диапазона “Без_пустых_ячеек” (в нашем случае – это D2) и пишем в нее следующую формулу:
   =ЕСЛИ(СТРОКА()-СТРОКА(Без_пустых_ячеек)+1>ЧСТРОК(Диапазон_с_пустыми_ячейками)-СЧИТАТЬПУСТОТЫ(Диапазон_с_пустыми_ячейками);»»;ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(Диапазон_с_пустыми_ячейками <>«»;СТРОКА(Диапазон_с_пустыми_ячейками);СТРОКА()+ЧСТРОК(Диапазон_с_пустыми_ячейками)));СТРОКА()-СТРОКА(Без_пустых_ячеек)+1);СТОЛБЕЦ(Диапазон_с_пустыми_ячейками);4))) .
   Примечание: в данном случае указаны имена диапазонов, заданные нами. Вам нужно заменить их на свои названия.
6. Когда все готово, жмем сочетание клавиш Ctrl+Shift+Enter, так как это формула, содержащая массивы. Результат отобразится в выбранной ячейке. В строке формул мы увидим перед знаком “равно” символ “<“. Это означает, что мы все сделали правильно.
7. Теперь нужно растянуть формулу на другие ячейки. Для этого наводим указатель мыши на правый нижний угол ячейки с результатом. Когда появится Маркер заполнения (черный плюсик), зажав левую кнопку мыши тянем его до последней строки диапазона “Без_пустых_ячеек”.
8. Мы получим столбец, в котором подряд расположились значения из ячеек исходного “Диапазона_с_пустыми_ячейками”.
9. Чтобы в дальнейшем иметь возможность работать с этими данными, не снимая выделение (или повторно выделив этот диапазон), щелкаем по нему правой кнопкой мыши и выбираем команду “Копировать” в открывшемся контекстном меню (или можно просто нажать комбинацию Ctrl+C).
10. В любом другом месте листа отмечаем соразмерный диапазон ячеек (например, в первоначальном месте), кликом правой кнопки мыши по выделенной области вызываем меню, в котором выбираем вариант вставки – “Значения”.
11. Столбец с формулами нам больше не нужен. Щелкаем по его обозначению на горизонтальной панели координат правой кнопкой мыши и в открывшемся контекстном меню выбираем команду “Удалить”.
12. В результате проделанных действий мы получили тот же самый столбец, но уже без пустых ячеек.

Заключение

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

Удалить пустые ячейки в столбце Excel

Часто встречающейся операцией при работе с таблицами Excel является удаление пустых строк. Существуют как стандартные средства при помощи которых можно удалить пустые строки, так и нестандартные, с использованием программирования на Visual Basic for Application.

Стандартные способы удаления пустых строк

Удаление вручную

Самый распространенный, но при этом самый примитивный способ, при котором необходимо навести курсор на номер пустой строки и кликнуть по нему левой кнопкой мыши. Вся строка выделится. Если нажать и удерживать клавишу Ctrl на клавиатуре, можно выделить несколько строк. После этого их можно удалить все разом, кликнув правой кнопкой мыши в выделенной области и выбрав команду «Удалить» в контекстном меню. Этот способ целесообразно использовать когда необходимо удалить несколько строк.

Удаление при помощи сортировки

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

Удаление при помощи выделения группы ячеек

Для того чтобы удалить пустые строки в Excel 2007 и 2010 можно использовать возможностью выделения группы ячеек. Для этого выделяем столбец, содержащий пустые ячейки, после чего находим на ленте Excel вкладку «Главная», в группе «Редактирование» выбираем кнопку «Найти и выделить» и выбираем пункт «Выделение группы ячеек». В появившемся диалоговом окне «Выделение группы ячеек» включаем опцию «Пустые ячейки» и нажимаем кнопку ОК. В предварительно выделенном столбце будут выделены все пустые ячейки. Остается кликнуть правой кнопкой мыши в любом месте выделенного поля и выбрать пункт «Удалить…» в контекстном меню, а в появившемся диалоговом окне «Удаление ячеек» выбрать пункт «Строка» и нажать ОК. При этом, строго говоря, удаляются не пустые строки, а строки, содержащие пустые ячейки в предварительно выделенном столбце.

Нестандартные методы удаления пустых строк

Программное удаление макросом VBA

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

Для того, чтобы перенести этот программный код на свой компьютер, наведите курсор мыши на поле с программным кодом , нажмите на одну из двух кнопкок в правом верхнем углу этого поля, скопируйте программный код и вставьте его в модуль проекта на своем компьютере (подробнее о том, как сохранить программный код макроса).

Автоматическое удаление с использованием надстройки

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

Пользователь на свой выбор может удалять либо скрывать пустые строки. Может удалять либо скрывать строки содержащие либо не содержащие заданный текст, при этом на свой выбор учитывать либо не учитывать регистр. Возможен одновременный поиск сразу нескольких значений, введенных через точку с запятой (;). Пользователь также может на свое усмотрение расширять область действия макроса и ограничивать его, задавая номера первой и последней строки. Кроме того, надстройка освобождает пользователя от поиска нужного макроса, так как позволяет вызывать диалоговое окно прямо из панели инструментов Excel.

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

Другие материалы по теме: