Как автоматически удалить пустые строки в Excel
Допустим, у вас есть список имен с пустыми строками в диапазоне.
Для того, чтобы удалить пустые значения, вам нужно выделить список имен, перейти во вкладку Главная контекстного меню, далее в Найти и выделить – Перейти…
В открывшемся окне выбрать Выделить.
Далее, выбрать кнопку Пустые ячейки.
Вы увидите как Excel выделил пустые строки в диапазоне.
Теперь остается только нажать правую кнопку мыши и в открывшемся меню выбрать пункт Удалить.
Вот и все, что нужно сделать.
Как удалить пустые строки в excel автоматически
Есть столбец в нем много ячеек с текстом в котором встречаются пустые строки, как их автоматически можно убрать?
П-р:
___________________
Y-T2|Abcdifg dsfgf
|пустая строка
| dfgfdhgdhsdg
| fgfffffffffg
——————-
Пользователь
Сообщений: 4825 Регистрация: 06.01.2013
13.09.2009 19:06:17
Предлагаю так. Допустим ваши данные находятся в Столбце А, то
Sub DeleteEmptyRows()
With Range(«A1:A» & Cells(Rows.Count, «A»).End(xlUp).Row)
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub
Пользователь
Сообщений: 19 Регистрация: 01.01.1970
13.09.2009 19:07:04
Есть столбец в нем много ячеек с текстом в котором встречаются пустые строки, как их автоматически можно убрать?
П-р:
___________________
Y-T2|Abcdifg dsfgf
\\\\|пустая строка
\\\\| dfgfdhgdhsdg
\\\\|dgfd пустая строка
\\\\| fgfffffffffg
P.S. Сделать сплошной текст в одну строку.
——————-
Пользователь
Сообщений: 4825 Регистрация: 06.01.2013
13.09.2009 19:10:01
Так же могу рассказать, как это сделать без макроса.
1. Выделяете ячейки в нужном столбце (заполненные и пустые), например, А1:А20
2. Меню Правка — Перейти (Ctrl+G), Выделить — Пустые ячейки — ОК
3. Меню Правка — Удалить — Строку — ОК
Пользователь
Сообщений: 4825 Регистрация: 06.01.2013
13.09.2009 19:12:50
Хм. в одну строку? Как плохо, что вы ленитесь открыть Excel и сделать небольшой пример в файле, а затем его выложить на форум.
А что если, заполненных ячеек в столбце 20000, а мы сейчас их развернём и в одну строку — вы думаете они поместятся в строке?
Может всё-таки пример выложите?
Пользователь
Сообщений: 4825 Регистрация: 06.01.2013
13.09.2009 19:17:42
В одну строку . так подойдёт?
Пользователь
Сообщений: 19 Регистрация: 01.01.1970
13.09.2009 19:20:29
Оу, пустые строки в самой ячейке (в одной), сейчас выложу пример.
Пользователь
Сообщений: 4825 Регистрация: 06.01.2013
13.09.2009 19:39:21
Эх, Олег, Олег. вас пока дождёшься всё воскресенье пройдёт )
Sub Макрос1()
Dim rCell As Range
For Each rCell In Range(«A1:A10»)
rCell = Replace(rCell, Chr(10), » «)
Next rCell
MsgBox «The end!», , «»
End Sub
Пользователь
Сообщений: 19 Регистрация: 01.01.1970
13.09.2009 19:47:27
Sub Макрос1()
Dim rCell As Range
For Each rCell In Range(«A1:A10»)
rCell = Replace(rCell, Chr(10), » «)
Next rCell
MsgBox «The end!», , «»
End Sub
Вроде все получилось, сейчас окончательно проверю, спасибо за ожидание.
Пользователь
Сообщений: 19 Регистрация: 01.01.1970
14.09.2009 09:20:56
Есть ли что-подобное этому скрипту для openoffice?
Страницы: 1
Читают тему
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Удаление пустых строк и столбцов в данных
Пустые строки и столбцы могут быть головной болью в таблицах во многих случаях. Стандартные функции сортировки, фильтрации, подведения итогов, создания сводных таблиц и т.д. воспринимают пустые строки и столбцы как разрыв таблицы, не подхватывая данные, расположенные за ними далее. Если таких разрывов много, то удалять их вручную может оказаться весьма затратно, а удалить сразу всех «оптом», используя фильтрацию не получится, т.к. фильтр тоже будет «спотыкаться» на разрывах. Давайте рассмотрим несколько способов решения этой задачи.
Способ 1. Поиск пустых ячеек
Это, может, и не самый удобный, но точно самый простой способ вполне достойный упоминания. Предположим, что мы имеем дело вот с такой таблицей, содержащей внутри множество пустых строк и столбцов (для наглядности выделены цветом):
Допустим, мы уверены, что в первом столбце нашей таблицы (колонка B) всегда обязательно присутствует название какого-либо города. Тогда пустые ячейки в этой колонке будут признаком ненужных пустых строк. Чтобы быстро их все удалить делаем следующее:
- Выделяем диапазон с городами (B2:B26)
- Нажимаем клавишу F5 и затем кнопку Выделить (Go to Special) или выбираем на вкладке Главная — Найти и выделить — Выделить группу ячеек (Home — Find&Select — Go to special) .
- В открывшемся окне выбираем опцию Пустые ячейки (Blanks) и жмём ОК – должны выделиться все пустые ячейки в первом столбце нашей таблицы.
- Теперь выбираем на вкладке Главная команду Удалить — Удалить строки с листа (Delete — Delete rows) или жмём сочетание клавиш Ctrl + минус — и наша задача решена.
Само-собой, от пустых столбцов можно избавиться совершенно аналогично, взяв за основу шапку таблицы.
Способ 2. Поиск незаполненных строк
Как вы, возможно, уже сообразили, предыдущий способ сработает только в том случае, если в наших данных обязательно присутствую полностью заполненные строки и столбцы, за которые можно зацепиться при поиске пустых ячеек. Но что, если такой уверенности нет, и в данных могут содержаться и пустые ячейки в том числе?
Взгляните, например, на следующую таблицу — как раз такой случай:
Здесь подход будет чуть похитрее:
-
Введём в ячейку A2 функцию СЧЁТЗ (COUNTA) , которая вычислит количество заполненных ячеек в строке правее и скопируем эту формулу вниз на всю таблицу:
К сожалению, со столбцами такой трюк уже не проделать – фильтровать по столбцам Excel пока не научился.
Способ 3. Макрос удаления всех пустых строк и столбцов на листе
Для автоматизации подобной задачи можно использовать и простой макрос. Нажмите сочетание клавиш Alt + F11 или выберите на вкладке Разработчик — Visual Basic (Developer — Visual Basic Editor) . Если вкладки Разработчик не видно, то можно включить ее через Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) .
В открывшемся окне редактора Visual Basic выберите команду меню Insert — Module и в появившийся пустой модуль скопируйте и вставьте следующие строки:
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns(r)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub
Закройте редактор и вернитесь в Excel.
Теперь нажмите сочетание Alt + F8 или кнопку Макросы на вкладке Разработчик. В открывшемся окне будут перечислены все доступные вам в данный момент для запуска макросы, в том числе только что созданный макрос DeleteEmpty. Выберите его и нажмите кнопку Выполнить (Run) — все пустые строки и столбцы на листе будут мгновенно удалены.
Способ 4. Запрос Power Query
Ещё один способ решить нашу задачу и весьма частый сценарий — это удаление пустых строк и столбцов в Power Query.
Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Можно конвертировать её в динамическую «умную» сочетанием клавиш Ctrl+T или же просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразовав в именованный:
Теперь используем команду Данные — Получить данные — Из таблицы/диапазона (Data — Get Data — From table/range) и грузим всё в Power Query:
Дальше всё просто:
- Удаляем пустые строки командой Главная — Сократить строки — Удалить строки — Удалить пустые строки (Home — Remove Rows — Remove empty rows).
- Щёлкаем правой кнопкой мыши по заголовку первого столбца Город и выбираем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot Other Columns). Наша таблица будет, как это технически правильно называется, нормализована — преобразована в три столбца: город, месяц и значение с пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает в исходных данных пустые ячейки, что нам и требуется:
Ссылки по теме
- Что такое макрос, как он работает, куда копировать текст макроса, как запустить макрос?
- Заполнение всех пустых ячеек в списке значениями вышестоящих ячеек
- Удаление всех пустых ячеек из заданного диапазона
- Удаление всех пустых строк на листе с помощью надстройки PLEX
4 лучших способа удалить пустые строки в Microsoft Excel
Microsoft Excel — популярный инструмент, который можно использовать для выполнения функции анализа данных. Это работает, если данные не запутаны. Примером возможной проблемы с данными Excel может быть наличие нескольких пустых строк в электронной таблице Excel, что может полностью нарушить ваш анализ. К счастью, есть несколько способов удалить пустые строки в веб-версии Microsoft Excel или в автономной версии.
Оглавление
Шаг 2: Откройте файл Excel, который вы хотите исправить, и поместите курсор между прямоугольным треугольником между строкой и столбцом на листе Excel. Это выделит все ячейки на листе.
Шаг 3: На ленте выберите вкладку Данные.
Шаг 4: в группе «Получить и преобразовать данные» щелкните «Из таблицы/диапазона».
Шаг 5: В диалоговом окне «Создать таблицу» вручную введите или перетащите курсор в ячейки Excel, чтобы заполнить сведения о расположении данных в таблице.
Шаг 6: нажмите «ОК» и дождитесь появления редактора Power Query.
Шаг 7: на вкладке «Главная страница Power Query» щелкните раскрывающийся список «Удалить строки», расположенный в группе «Уменьшить строки».
Шаг 8: Нажмите «Удалить пустые строки» в параметрах.
Шаг 9: на вкладке «Главная страница Power Query» нажмите «Закрыть и загрузить», чтобы сохранить изменения.
Ваш очищенный файл должен быть загружен на новый лист без пустых строк.
Использование функции фильтра
Еще один вариант, который вы можете рассмотреть при удалении пустых строк из набора данных, — это функция фильтра Excel. Вот как это работает:
Связанный : 5 лучших исправлений для приложения калькулятора, не работающего на Mac
Шаг 1: Запустите файл Microsoft Excel.
Шаг 2: выберите диапазон ячеек, содержащих все данные в вашем наборе данных. Это также должно включать пустые строки.
Шаг 3: На ленте выберите вкладку Данные.
Шаг 4: в группе «Сортировка и фильтр» нажмите «Фильтр». Как только вы это сделаете, вы должны увидеть стрелки рядом с каждым именем поля.
Шаг 5: нажмите на стрелку рядом с именем поля, в строках которого есть пустые ячейки, которые вы хотите удалить.
Шаг 6: снимите флажок «Выбрать все».
Шаг 7: прокрутите список параметров вниз и выберите Пробелы.
Шаг 8: выберите все заголовки отфильтрованных строк. Для этого щелкните первую строку, затем, удерживая клавишу Shift на клавиатуре, щелкните заголовок последней строки.
Шаг 9: щелкните правой кнопкой мыши любой из выбранных вами заголовков, чтобы открыть список меню, и щелкните Удалить строку.
Шаг 10: снова щелкните вкладку «Данные» и выберите «Очистить» в группе «Сортировка и фильтр», чтобы избавиться от фильтрации.
Использование функции сортировки
Один из самых простых способов удалить пустые строки в Excel — использовать функцию сортировки. Когда вы используете этот метод, он перемещает пустые строки вниз, чтобы их можно было легко игнорировать. Вот как использовать этот метод:
Шаг 1: Запустите файл Microsoft Excel.
Шаг 2: Поместите курсор между прямоугольным треугольником между строкой и столбцом листа Excel. Это выделит все ячейки на листе.
Шаг 3: На ленте выберите вкладку Данные.
Шаг 4: в группе «Сортировка и фильтр» нажмите «Сортировка». Ваше действие откроет диалоговое окно. Если в вашем выборе есть строка заголовка, установите флажок рядом с Мои данные имеют заголовки.
Шаг 5: наведите указатель мыши рядом с параметром «Сортировать по» и щелкните столбец или поле с пробелами, от которых вы хотите избавиться. Выберите наилучший вариант сортировки, соответствующий вашим данным, например, от меньшего к большему, от А до Я, по убыванию или возрастанию. Это отобразит пробелы в базе вашего набора данных.
Шаг 6: нажмите ОК.
Использование функции «Перейти к»
Наконец, вы также можете использовать функцию «Перейти к специальному», чтобы удалить пустые строки в Excel. Вот как это работает.
Шаг 1: выберите столбец с пустыми ячейками (при условии, что другие строки в столбце пусты).
Шаг 2: одновременно нажмите Ctrl + G на клавиатуре, чтобы открыть диалоговое окно «Перейти».
Шаг 3: Нажмите «Специальный», чтобы открыть диалоговое окно «Перейти к специальному».
Шаг 4: В диалоговом окне «Перейти к специальному» нажмите «Пробелы», затем нажмите «ОК». Excel автоматически выбирает каждую пустую ячейку в выбранном диапазоне.
Шаг 5: щелкните правой кнопкой мыши любую из выбранных пустых ячеек и выберите «Удалить» в списке параметров меню.
Шаг 6: в диалоговом окне «Удалить» выберите «Вся строка», затем нажмите «ОК».
Связанный : Как удалить дату из URL-адресов WordPress
Отслеживание изменений в Excel
Очистка данных, содержащихся в файле Excel, может привести к нескольким изменениям. Чтобы убедиться, что вы можете отслеживать изменения, происходящие с документом, убедитесь, что вы включили функцию отслеживания изменений в Excel.