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

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

Допустим, у вас есть список имен с пустыми строками в диапазоне.

Для того, чтобы удалить пустые значения, вам нужно выделить список имен, перейти во вкладку Главная контекстного меню, далее в Найти и выделить – Перейти…

В открывшемся окне выбрать Выделить.

Далее, выбрать кнопку Пустые ячейки.

Вы увидите как Excel выделил пустые строки в диапазоне.

Теперь остается только нажать правую кнопку мыши и в открывшемся меню выбрать пункт Удалить.

Вот и все, что нужно сделать.

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

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

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

1. Щелкните в любом месте таблицы, чтобы отобразить вкладку Работа с таблицами.
2. Выберите Конструктор >Размер таблицы.
3. Выделите весь диапазон ячеек, который нужно включить в таблицу, начиная с верхней левой ячейки. В приведенном ниже примере исходная таблица охватывает диапазон A1:C5. После изменения размера с добавлением двух столбцов и трех строк таблица будет включать в себя диапазон A1:E8.

Совет: Вы также можете нажать кнопку Свернуть диалоговое , чтобы временно скрыть диалоговое окно «Избавьтесь от таблицы», выбрать диапазон на этом сайте, а затем нажать кнопку Развернуть .

Другие способы добавления строк и столбцов

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

Ввод данных

• Чтобы добавить строку внизу таблицы, начните вводить текст в ячейке под последней строкой. Таблица расширится, и в нее будет включена новая строка. Чтобы добавить столбец справа от таблицы, начните вводить текст в ячейку справа от последнего столбца.

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

В приведенном ниже примере добавления столбца при вводе значения в ячейку C2 таблица расширяется за счет столбца C. Ему присваивается имя «Кв3», так как Excel выявляет закономерность присвоения имен по заголовкам «Кв1» и «Кв2».

Вставка данных

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

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

В приведенном ниже примере добавления строк при вставке значений из диапазона A10:B12 в первую строку под таблицей (строка 5) она расширяется за счет вставленных данных.

В приведенном ниже примере добавления столбцов при вставке значений из диапазона C7:C9 в первый столбец справа от таблицы (столбец C) она расширяется за счет вставленных данных, а столбцу присваивается заголовок «Кв3».

Добавление строки с помощью команды «Вставить»

1. Чтобы вставить строку, выберите ячейку или строку (но не строку заголовков) и щелкните ее правой кнопкой мыши. Чтобы вставить столбец, выберите любую ячейку в таблице и щелкните ее правой кнопкой мыши.
2. Наведите указатель мыши на пункт Вставить и выберите команду Строки таблицы выше или Столбцы таблицы слева, чтобы вставить новую строку или столбец соответственно.

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

В приведенном ниже примере новая строка будет вставлена над строкой 3.

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

В приведенном ниже примере новый столбец будет вставлен слева от столбца 1.

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

1. Выделите строки или столбцы таблицы, которые требуется удалить. Можно просто выделить одну или несколько ячеек в строках или столбцах таблицы, которые требуется удалить.
2. На вкладке Главная в группе Ячейки, щелкните стрелку рядом с кнопкой Удалить, а затем выберите команду Удалить строки таблицы или Удалить столбцы таблицы. Вы также можете щелкнуть правой кнопкой мыши одну или несколько строк или столбцов, в меню ярлыка нажать кнопку Удалить, а затем — Столбцы таблицы или Строки таблицы. Можно также щелкнуть правой кнопкой мыши одну или несколько ячеек в строке или столбце таблицы, нажать кнопку Удалить ищелкнуть Строки таблицы или Столбцы таблицы.

Удаление одинаковых строк из таблицы

Дубликаты удаляются из таблицы также легко, как и из любых выбранных данных в приложении Excel.

1. Щелкните любое место таблицы. Откроется окно Работа с таблицами и вкладка Конструктор.
2. На вкладке Конструктор в группе Сервис выберите команду Удалить дубликаты.
3. В диалоговом окне Удалить дубликаты в группе Столбцы выберите столбцы, содержащие дубликаты, которые требуется удалить. Можно также нажать кнопку Снять выделение и выбрать нужные столбцы или нажать кнопку Выделить все, чтобы выделить все столбцы.

Примечание: Дубликаты удаляются с листа. Если вы случайно удалили данные, которые вы хотите сохранить, вы можете восстановить их, нажав или CTRL+Z. Вы также можете использовать условное форматирование для выделения повторяющихся значений, прежде чем удалять их. Дополнительные сведения см. в статье Добавление, изменение и отмена условного форматирования.

Удаление пустых строк из таблицы

1. Убедитесь, что активная ячейка находится в столбце таблицы.
2. Щелкните стрелку в заглавной области столбца.
3. Чтобы выполнить отбор пустых ячеек, в меню «Автофильтр» в верхней части списка значений снимите флажок (Выделить все), а в нижней части установите флажок (Пустые).

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

Таким же образом можно отфильтровать и удалить пустые строки. Дополнительные сведения о том, как отфильтровать пустые строки на листе, см. в статье Фильтрация данных в диапазоне или таблице.

1. Вы выберите таблицу, а затем выберите конструктор > Таблица.
2. При необходимости настройли диапазон ячеек, который содержит таблица, и выберите ОК.

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

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

Выборка ячеек

Самый простой способ удалить пустые строки в Excel — это сделать выборку. Но ее не стоит делать в очень больших таблицах на несколько листов. Дело в том, что при удалении незаполненных ячеек таким способом, при ее наличии в строке с информацией, удалится вся строчка. И, таким образом, потеряется важная информация, которую пользователь не сразу заметит в большом объеме.

Делается выборка по ячейкам следующим образом:

1. Выделить курсором всю таблицу.
2. Нажать на кнопку F5.
3. Откроется диалоговое окно, в котором необходимо нажать на “Выделить”.
4. Отметить пункт “Пустые ячейки”.
5. Найти незаполненное звено в любом месте и нажать “Удалить” на клавиатуре. В появившемся окне выбрать пункт “Строку”.

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

Использование ключевого столбца

Ключевой столбец помогает определить, чистая строка или нет. Также важно сохранять порядок строчек, именно поэтому простая сортировка не поможет. Стирание происходит по следующей схеме:

1. Сначала нужно определить, какой столбец будем считать ключевым.
2. При помощи мышки выделить всю таблицу.
3. Задать к выделению фильтр, расположенный на верхней панели программы.
4. Задать параметры для столбца, где присутствует нумерация (кликнуть «Выделить все», опуститься в самый низ списка и выбрать «пустые»).
5. Выделить все ненужные строки. Теперь это намного проще, так как остались только они.
6. Стереть кликом по правой кнопке мышки и выбором пункта “Удалить строки” (тут же можно выбрать «Удалить столбцы»).
7. Очистить таблицу от фильтра, чтобы показались скрытые данные.

Как провести регрессионный анализ в Excel

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

Без ключевого столбца

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

1. В самом конце таблицы добавить новый чистый столбик и назвать его “Счетчик” или по-другому.
2. В первое звено вставить формулу =СЧИТАТЬПУСТОТЫ(A2:C2). В скобках устанавливаем диапазон поиска. Она посчитает количество незаполненных ячеек. В нашем случае всего 3 столбца, поэтому 3 будет считаться полностью незаполненной строкой.
3. Добавить формулу во весь новый столбик.
4. Применить фильтр к ключевому столбцу со следующими данными. Стоит отметить, что цифра 3 как раз и означает строку без информации.
5. Удалить все ненужное простым выделением, затем вспомогательный столбец и фильтр.

Использование сортировки

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

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

Использование макроса

Макрос позволяет выделить и удалить пустые строчки автоматически. Чтобы его использовать, необходимо сделать следующее:

1. Открыть разработчик комбинацией клавиш Alt+F11.
2. Создать новый модуль для используемой книги таблиц.
3. Вставить макрос:

Sub SelectLine() Dim i As Long Dim diapaz1 As Range Dim diapaz2 As Range Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _ ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)) For i = 1 To diapaz1.Rows.Count If WorksheetFunction.CountA(diapaz1.Rows(i).EntireRow) = 0 Then If diapaz2 Is Nothing Then Set diapaz2 = diapaz1.Rows(i).EntireRow Else Set diapaz2 = Application.Union(diapaz2, diapaz1.Rows(i).EntireRow) End If End If Next If diapaz2 Is Nothing Then MsgBox "Не найдено ни одной не заполненной строки!" Else diapaz2.Select End If End Sub

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

Макросы – что это такое

Этот макрос только выделяет пустые ячейки. Но можно таким же образом массово их удалить. Для этого требуется использовать тот же макрос, но заменить сроку diapaz2.Select на diapaz2.[Delete]. После внесенных изменений все будет выделяться и удаляться автоматически.

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

Формула массива действует следующим образом:

1. Берётся два диапазона. Первый это исходная таблица, второй новый столбец, в котором после действия формулы переместятся все данные.
2. Для примера требуется дать столбцам имена следующим образом.
3. В чистый столбец в первую строчку вставить формулу:

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

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

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

Как макрос работает

В этом макросе, мы используем свойство UsedRange объекта ActiveSheet, чтобы определить диапазон, с которым мы работаем. Свойство UsedRange дает диапазон, который охватывает ячейки, которые были использованы для ввода данных. Затем устанавливает счетчик, который начинается с последнего столбца, используемого диапазона, проверяя, весь ли столбец пуст. Если весь столбец действительно пуст, мы удалим столбец. Мы продолжаем делать то же самое для каждого цикла, каждый раз, когда приращение счетчика двигается к предыдущей колонке.

Код макроса

Sub UdalitPustieStolbci() ' Шаг 1: объявляем переменные Dim MyRange As Range Dim iCounter As Long 'Шаг 2: Определяем целевой диапазон Set MyRange = ActiveSheet.UsedRange 'Шаг 3: Запускаем обратный цикл через диапазон For iCounter = MyRange.Columns.Count To 1 Step -1 'Шаг 4: Если весь столбец пуст, то удаляем его If Application.CountA(Columns(iCounter).EntireColumn) = 0 Then Columns(iCounter).Delete End If ' Шаг 5: Увеличиваем счетчик Next iCounter End Sub

Как этот код работает

1. Шаг 1 объявляет две переменные. Первая переменная является переменной объекта под названием MyRange. Это переменная объекта, который определяет целевой диапазон. Другая переменная является переменной Long Integer называется iCounter.
2. Шаг 2 заполняет переменную MyRange со свойством UsedRange объекта ActiveSheet. Свойство UsedRange дает диапазон, который охватывает клетки, которые были использованы для ввода данных. Обратите внимание, что если мы хотим указать фактический диапазон или именованный диапазон, мы могли бы просто ввести свое название — Range («MyNamedRange»).
3. На этом этапе макрос устанавливает параметры для нашего инкрементного счетчика, чтобы начать на максимальном количестве для диапазона (MyRange.Columns.Count) и заканчивается в 1 (первая строка выбранного диапазона). Обратите внимание, что мы используем Шаг-1 классификатор. Поскольку мы указываем шаг -1, Excel знает, что мы будем увеличивать счетчик в обратном направлении; перемещение назад один шаг на каждой итерации. В целом, Шаг 3 говорит Excel, что мы хотим, чтобы начать в последнем столбце выбранного диапазона, двигаясь назад, пока мы не получим в первом столбце диапазона. При работе с диапазоном, вы можете явно вызывать определенный столбец в диапазоне, передавая номер столбца индекса к коллекции Columns диапазона. Например, Range («A1: D17»). Columns(2) указывает на второй столбец в диапазоне (столбец B).
4. На шаге 4, макрос использует переменную iCounter как номер индекса для коллекции ColumnsMyRange. Это помогает определить, каким именно столбцом мы работаем в текущем цикле. Макрос проверяет все ячейки в этом столбце, являются ли они пустыми. Если они есть, макрос удаляет весь столбец.
5. На шаге 5, макрос возвращается к началу цикла увеличивать счетчик.

Как использовать

1. Активируйте редактор Visual Basic, нажав ALT + F11.
2. Щелкните правой кнопкой мыши personal.xlb в окне Project.
3. Выберите Insert➜Module.
4. Введите или вставьте код.