Excel — Как удалить пустые строки внутри ячеек?
Office
✅ Всем добрый день! Сегодня хочу поделиться с вами полезной командой которая мне очень часто экономит много времени и нервов при работе с документами Excel, а именно когда я для своего магазина обрабатываю прайсы которые потом буду загружать на сайт.
🔥 В моем случае поставщик мне предоставляет автоматически сгенерированные файлы для загрузки для определенной CMS а так как мой интернет магазин работает на совершенно другой CMS возникает много «допилок» которые необходимо делать каждый раз когда нужно обновить цены или добавить новый товар.
Сегодня я хочу рассказать об очень интересной команде о которой мало кто знает и по незнанию используют для этих целей макросы и какие манипуляция с другими командами.
✅ Убрать пустые строчки в ячейках эксель
⭐️ Что бы наглядно показать, о чем мы будем говорить приведу пример. Представим пример, что у нас в ячейках есть информация, но по мимо этого в каждой ячейки присутствуют и пустые строчки которые нам необходимо убрать из документа (рис.1)
Для того что бы убрать эти пустую ячейку не нужно использовать макросы, скрипты и другие манипуляция с данными, для этого достаточно использовать:
- Нажимаем комбинацию клавиш CTRL + F (включаем меню поиск)
- Переходим на вкладку Заменить (рис.1)
- В строке поиске ставим курсор и нажимаем комбинацию клавиш CTRL + J (вы увидите, что в строке курсор переместился на строчку ниже) (рис.2)
- В строке замены ничего не вводим и оставляем его пустым
- Нажимаем кнопку Заменить все и радуемся результату
✅ Вот такой нехитрой комбинацией CTRL + J можно удалять пустые строки в ячейках.
Удаление пустых ячеек из диапазона
Постановка задачи
Имеем диапазон ячеек с данными, в котором есть пустые ячейки:
Задача — удалить пустые ячейки, оставив только ячейки с информацией.
Способ 1. Грубо и быстро
- Выделяем исходный диапазон
- Жмем клавишу 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. Чтобы использовать эту функцию в нашем примере:
- Выделите достаточный диапазон пустых ячеек, например F3:F10.
- Идем в меню Вставка — Функция (Insert — Function) или жмем на кнопку Вставить функцию(Insert Function) на вкладке Формулы (Formulas) в новых версиях Excel. В категории Определенные пользователем (User Defined) выберите нашу функцию NoBlanks.
- В качестве аргумента функции укажите исходный диапазон с пустотами (B3:B10) и нажмите Ctrl+Shift+Enter, чтобы ввести функцию как формулу массива.