Удаляем в EXCEL пустые строки в таблице
Часто, особенно при импорте данных в EXCEL, на листе могут формироваться таблицы с ПОЛНОСТЬЮ пустыми строками. Научимся быстро удалять эти ненужные строки, которые в дальнейшем могут затруднить работу с таблицей.
Представим, что у нас есть таблица с какими-то значениями, в которой есть полностью пустые строки.
Приведем методы, которые используются при удалении пустых строк. Зачем нужно удалять пустые строки можно прочитать в статье Советы по построению таблиц .
Сортировка
Самым простым способом удаления пустых строк является сортировка таблицы: выделяем диапазон, в котором содержится таблица ( А2:C17 ), затем вызываем команду меню Данные/ Сортировка и фильтр/ Сортировка от минимального к максимальному или просто /Сортировка .
При сортировке таблиц с несколькими столбцами нужно быть осторожным, чтобы ячейки не перескочили из своих строк в другие. Поэтому, для сортировки нужно выделять всю таблицу. После сортировки по возрастанию пустые строки окажутся внизу таблицы.
Если важен порядок строк до сортировки, то перед сортировкой нужно создать столбец с порядковой нумерацией строк, а после удаления пустых строк, заново отсортировать таблицу уже по этому столбцу.
Фильтр
Для нахождения пустых ячеек можно также воспользоваться Фильтром ( Главная/ Редактирование/ Сортировка и фильтр/ Фильтр ). Выбираем подходящий столбец, отображаем только пустые ячейки, выделяем строки не содержащие данных и удаляем их.
Выделение группы ячеек
Другим способом выделения пустых ячеек является использование инструмента Выделение группы ячеек .
- выделяем диапазон А2:C17 ;
- выбираем пункт меню Главная/ Редактирование/ Найти и выделить/ Выделение группы ячеек… ,
выберите пункт пустые ячейки в разделе Выделить ;
- далее нажимаем маленькую стрелочку в меню Главная/ Ячейки/ Удалить ;
- выберем Удалить ячейки (удалить ячейки, со сдвигом вверх) .
Здесь нужно быть аккуратным: если таблица сдержит помимо пустых строк, строки с заполненными и пустыми ячейками, то часть ячеек перескочит со своих строк на другие, что испортит таблицу. Если таблица в формате EXCEL 2007 , то EXCEL не даст испортить таким образом таблицу: появится сообщение «Данная команда неприменима для перекрывающихся диапазонов».
СОВЕТ: Стоит помнить, что ячейки, содержащие формулы (даже если это ссылки на пустые ячейки), не считаются пустыми по определению.
Функция удаления дубликатов
Как в Excel удалить пустые строки
Как удалить скрытые строки в Excel?
Однажды пользователь скрыл некую информацию в строках, чтобы она не отвлекала от работы. Думал, что впоследствии данные еще понадобятся. Не понадобились – скрытые строки можно удалить: они влияют на формулы, мешают.
В тренировочной таблице скрыты ряды 5, 6, 7:
Будем их удалять.
- Переходим на «Файл»-«Сведения»-«Поиск проблем» — инструмент «Инспектор документов».
- В отрывшемся окне ставим галочку напротив «Скрытые строки и столбцы». Нажимаем «Проверить».
- Через несколько секунд программа отображает результат проверки.
- Нажимаем «Удалить все». На экране появится соответствующее уведомление.
В результате проделанной работы скрытые ячейки удалены, нумерация восстановлена.
Таким образом, убрать пустые, повторяющиеся или скрытые ячейки таблицы можно с помощью встроенного функционала программы Excel.
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Карта сайта
- Скачать примеры
Как удалить каждую вторую строку в Excel?
Проредить таблицу можно с помощью макроса. Например, такого:
А можно ручками. Предлагаем простенький способ, доступный каждому пользователю.
- В конце таблицы делаем вспомогательный столбец. Заполняем чередующимися данными. Например, «о у о у о у» и т.д. Вносим значения в первые четыре ячейки. Потом выделяем их. «Цепляем» за черный крестик в правом нижнем углу и копируем буквы до конца диапазона.
- Устанавливаем «Фильтр». Отфильтровываем последний столбец по значению «у».
- Выделяем все что осталось после фильтрации и удаляем.
- Убираем фильтр – останутся только ячейки с «о».
Вспомогательный столбец можно устранить и работать с «прореженной таблицей».
Как удалить все пустые строки в Excel
В этой статье я объясню, почему удаление пустых строк в Excel при помощи выделить пустые ячейки > удалить строку – это плохая идея, и покажу Вам 2 быстрых и правильных способа, как удалить пустые строки не разрушая данные. Все эти способы работают в Excel 2013, 2010 и в более старых версиях.
Если Вы читаете эту статью, то, скорее всего, постоянно работаете в Excel с большими таблицами. Вы знаете, что пустые строки периодически появляются среди данных, ограничивая работу большинства инструментов Excel с таблицами (сортировка, удаление дубликатов, промежуточные итоги и так далее), не позволяя им правильно определять диапазон данных. И каждый раз приходится вручную определять границы, иначе итогом будет неверный результат и уйма потраченного времени на исправление ошибок.
Существует множество причин, почему появляются пустые строки. К примеру, Вы получили рабочую книгу Excel от другого человека или в результате экспорта из корпоративной базы данных, или ненужные данные в строках были удалены вручную. В любом случае, если Ваша цель – удалить все эти пустые строки и получить чистую и аккуратную таблицу, то следуйте простым шагам, приведённым ниже:
Добавление и удаление строк
Удаление пустых ячеек из диапазона
Постановка задачи
Имеем диапазон ячеек с данными, в котором есть пустые ячейки:
Задача — удалить пустые ячейки, оставив только ячейки с информацией.
Способ 1. Грубо и быстро
- Выделяем исходный диапазон
- Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.
Выделяются все пустые ячейки в диапазоне.
Даем в меню команду на удаление выделенных ячеек: правой кнопкой мыши —Удалить ячейки (Delete Cells) со сдвигом вверх.
Способ 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, чтобы ввести функцию как формулу массива.
Как удалить пустые строки в таблице Excel
Использование автофильтра для перемещения пустых ячеек
Следующий этап может показаться немного странным, потому что он будет перемещать и реорганизовывать ваши данные таким образом, который в лучшем случае кажется нелогичным, а в худшем — деструктивным.
После выбора фильтра щелкните зеленый трехстрочный значок в столбце A1 вашей электронной таблицы, где вы установили заголовок ранее. В этом меню выберите «Сортировать от А до Я». Вы увидите, как ваши данные располагаются в алфавитном порядке, начиная с цифр и заканчивая буквами.
Между тем, пустые места будут сдвинуты в нижнюю часть вашей таблицы. Продолжайте преобразовывать свою электронную таблицу столбец за столбцом, пока пустые ячейки не переместятся в нижнюю часть экрана, и у вас не появится один сплошной блок данных, отображаемый в верхней части Google Таблиц. Это, скорее всего, сделает ваши данные запутанными и нечитаемыми — не волнуйтесь, в конце концов все получится.
Методы удаления
До этого всего, необходимо разобраться, а вправду ли можно в определенном массиве либо таблице удалять пустые ячейки? Обозначенная процедура приводит к смещению данных, а это далековато не постоянно является допустимым. На самом деле, элементы можно удалять лишь в 2-ух вариантах:
- Если строчка (столбец) на сто процентов является пустой (в таблицах);
- Если ячейки в строке и столбце логически не соединены вместе (в массивах).
Если пустых ячеек не достаточно, то их полностью можно удалить при помощи обыденного ручного метода удаления. Но, если таковых незаполненных частей огромное количество, то в этом случае, данную функцию необходимо заавтоматизировать.
Метод 1: выделение групп ячеек
Более обычный метод удалить пустые элементы – это пользоваться инвентарем выделения групп ячеек.
- Выделяем спектр на листе, над которым будем проводить операцию поиска и удаления пустых частей. Нажимаем на многофункциональную кнопку на клавиатуре F5.
Опосля этих манипуляций все пустые элементы снутри обозначенного спектра будут удалены.
Метод 2: условное форматирование и фильтрация
Удалить пустые ячейки можно также методом внедрения условного форматирования и следующей фильтрации данных. Этот способ труднее предшествующего, но, тем не наименее, некие юзеры предпочитают конкретно его. Не считая того, необходимо сходу обмолвиться, что этот метод подступает лишь в этом случае, если значения находятся в одном столбце и не содержат формулы.
- Выделяем спектр, который собираемся обрабатывать. Находясь во вкладке «Основная», нажимаем на пиктограмму «Условное форматирование», которая, в свою очередь, размещается в блоке инструментов «Стили». Перебегаем в пункт открывшегося перечня «Правила выделения ячеек». В показавшемся перечне действий избираем позицию «Больше…».
Метод 3: применение сложной формулы
Не считая того, убрать пустые ячейки из массива можно, применив сложную формулу, состоящую из нескольких функций.
- До этого всего, нам необходимо будет отдать имя спектру, который подвергается трансформации. Выделяем область, делаем щелчок правой клавишей мышки. В активировавшемся меню избираем пункт «Присвоить имя…».
Существует несколько методов удаления пустых частей в Microsoft Excel. Вариант с выделением групп ячеек более обычный и резвый. Но ситуации бывают различные. Потому, как доп методы, можно применять варианты с фильтрованием и применением всеохватывающей формулы.
Мы рады, что смогли посодействовать Для вас в решении трудности.
Кроме данной нам статьи, на веб-сайте еще 12327 инструкций. Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.
Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.
Настройка фильтра по данным таблицы
Метод 1: выравниваем область ячеек
Защита ячеек от редактирования в excel: защита в ваших руках
Нестандартные методы удаления пустых строк
Программное удаление макросом VBA
Стандартные способы просты в использовании, но имеют свои минусы. Если ни один из стандартных способов не подходит, то для решения этой задачи можно использовать процедуру, написанную на языке VBA, которая удалит все пустые строки в используемом диапазоне листа.
Для того, чтобы перенести этот программный код на свой компьютер, наведите курсор мыши на поле с программным кодом , нажмите на одну из двух кнопкок в правом верхнем углу этого поля, скопируйте программный код и вставьте его в модуль проекта на своем компьютере (подробнее о том, как сохранить программный код макроса).
Автоматическое удаление с использованием надстройки
Для тех, кому функциональности представленной процедуры будет недостаточно, предлагаю воспользоваться надстройкой, позволяющей удалять и скрывать не только пустые строки, но и строки, удовлетворяющие условиям пользователя, которые можно задавать в диалоговом окне.
Пользователь на свой выбор может удалять либо скрывать пустые строки. Может удалять либо скрывать строки содержащие либо не содержащие заданный текст, при этом на свой выбор учитывать либо не учитывать регистр. Возможен одновременный поиск сразу нескольких значений, введенных через точку с запятой (;). Пользователь также может на свое усмотрение расширять область действия макроса и ограничивать его, задавая номера первой и последней строки. Кроме того, надстройка освобождает пользователя от поиска нужного макроса, так как позволяет вызывать диалоговое окно прямо из панели инструментов Excel.
Еще одна надстройка, позволяющая скрывать и удалять строки и столбцы, в зависимости от значения ячейки и заданного условия, а также удаляющая пустые ячейки и ячейки с заданными значениями со сдвигом вверх и со сдвигом влево.
Метод 3: использование формулы
Этот метод, вряд ли, пользуется большой популярностью у пользователей Эксель, но мы, все же, о нем расскажем. В данном случае будем использовать формулу, которая состоит из нескольких функций.
- Для начала нужно задать имя диапазону ячеек, с которым планируем работать. Для этого выделяем его, затем кликаем по нему правой кнопкой мыши и в контекстном меню выбираем команду “Присвоить имя”.
- В окне создания имени в поле “Имя” указываем любое название (без пробелов). Остальные поля оставляем без изменений и нажимаем OK.
- В любом свободном месте производим выделение соразмерного диапазона ячеек (для удобства, если возможно, лучше это сделать в тех же строках другого столбца). Затем также в контекстном меню, вызываемом щелчком правой кнопки мыши по нему, выбираем пункт “Присвоить имя”.
- Как и для первого диапазона, задаем имя (в нашем случае – “Без_пустых_ячеек”).
- Встаем в самую верхнюю ячейку диапазона “Без_пустых_ячеек” (в нашем случае – это D2) и пишем в нее следующую формулу:
.Примечание: в данном случае указаны имена диапазонов, заданные нами. Вам нужно заменить их на свои названия.
Как удалить пустые ячейки в Excel
Эту статью я написал с целью описать 5 простых вариантов, как возможно удалить пустые строки в Excel. Частенько после импорта данных или простого копирования с другого источника мы получаем пустые строки и столбцы, что в дальнейшем может негативно сказаться и осложнить работу с информацией.
Разрывы, которые возникают в таблице благодаря возникшим пустотам, могут помешать вам эффективно ее использовать. Возникают такие проблемы:
- ошибки при автоматическом построении разнообразных формул, графиков, диаграмм;
- пустые строки, выполняют роль разделителя вычисляемой области и т.п.;
Избежать таких ошибок очень легко и очень просто, нужно всегда следить постоянно за качеством вашей информации и при выявлении ненужных пробелов в ваших данных, удалять их. Найти и удалить незаполненые строки, столбцы и ячейки можно 5 способами:
- С помощью сортировки информации;
- С помощью наложения фильтра;
- С помощью меню выделения группы ячеек;
- С помощью формулы массива;
- С помощью макроса (VBA).
Сортировка
Это самый простой способ убрать с вашей рабочей области или вычисляемого диапазона чистые строки. Вам нужно выделить диапазон, выбрать на вкладке «Данные» кнопку «Сортировка» и в предоставленном диалоговом окне выбираем вариант сортировки. Программа всё отсортирует по полочкам, и вы можете удалить пустые строки.
Если очень важен порядок следования строк в таблице, вам стоит для начала создать столбик с порядковой нумерацией рядов, а после сортировки и удаления пустых срок в таблице Excel, снова выстроить данные по этому столбику.
Фильтрация
Этот способ также является простым и доступным абсолютно любому пользователю. Для применения фильтра на таблицу вам необходимо на вкладке «Главная» в разделе «Редактирование» нажимать кнопку «Сортировка и фильтр» и в открывшемся пункте меню выбираете «Фильтр».
Выделение группы ячеек
Я бы и этот способ причислил к достаточно простым и лёгким. Для применения этого варианта вам нужен инструмент «Выделение группы ячеек». Удалить незаполненые строки возможно в несколько этапов:
- выделение нужного диапазона;
- выбираем вкладку «Главная», в блоке «Редактирование», кликаем «Найти и выделить» и в выпадающем меню выбираете «Выделение группы ячеек»;
в диалоговом окне, в разделе «Выделить» отмечаем пункт «пустые ячейки»;
- переходим во вкладку «Главная» и нажимаем кнопку «Ячейки» и выбираем пункт «Удалить»;
- в диалоговом окне выбираете пункт «удалить ячейки со сдвигом вверх» и все незаполненые строки будут удалены.
Формула массива
Этот способ уже на искушенного пользователя. Хотя ничего уж слишком сложного и нет, тем не менее, этот способ я увидел у Николая Павлова на его сайте и решил добавить его для полноты информации о возможностях чистки от пустых строк с применением формулы массива.
Итак, в чём же состоит этот способ по удалению пустых строк в Excel и как его применять:
для большей простоты дадим имена нашим диапазонам, для этого на вкладке «Формулы» нажимаете «Диспетчер имен» и присваиваете для двух диапазонов имена, например для изначальных данных с пустыми ячейками — «Пустые», а для обработанных — «БезПустых». Обязательное условие в том, что диапазоны должны иметь одинаковый результат, но вот размещение этих диапазонов могут и отличатся, это не критично;
следующим шагом это введение в первую ячейку второго диапазона формулу:
=ЕСЛИ( СТРОКА() — СТРОКА (БезПустых) +1> ЧСТРОК( Пустые) — СЧИТАТЬПУСТОТЫ (Пустые);»»; ДВССЫЛ ( АДРЕС (НАИМЕНЬШИЙ ((ЕСЛИ (Пустые <>«»; СТРОКА (Пустые); СТРОКА()+ ЧСТРОК( Пустые))); СТРОКА() — СТРОКА (БезПустых)+1); СТОЛБЕЦ( Пустые);4))). Да я сам знаю, что формула страшная, сам, когда ее увидел, обалдел, тем не менее, она рабочая и исправно выполняет возложенные на нее обязанности;
после прописания формулы, вам нужно окончить введение формулы «гарячей» комбинацией клавиш Ctrl+Shift+Enter, это необходимо для того, что бы формула была введена как формула массива. Теперь используя возможность в Excel как авто заполнение (копирование формулы, протягивая за крестик в углу) вы получили исходный диапазон, но уже без пустых ячеек.
Макрос
Этот вариант также не прост, но, используя предоставленные коды, и инструкции к их применению ничего сложного и неосуществимого вы не увидите. Макрос стоит использовать, если вы уверены что такую процедуру по удалению пустых строк в Excel, а также, столбцов или ячеек будет очень частым и для выполнения этих работ стоит прописать скрипт.
Перейдите во вкладку «Сервис», выберите раздел «Макрос» и нажмите кнопку «Редактор Visual Basic» или нажав ALT+F11 и в появившемся диалоговом окне редактора нажимаем «Insert» — «Module».
Метод 2: фильтр и условное форматирование
Этот метод несколько сложнее рассмотренного выше и предполагает применение фильтра или условного форматирования с дальнейшей фильтрацией (сортировкой) данных. Однако тут есть один нюанс – его можно применять только для работы с одним столбцом, в котором, к тому же, нет формул.
Фильтрация данных:
- Для начала нужно любым удобным способом выделить нужную область ячеек. Затем в главной вкладке в блоке инструментов “Редактирование” щелкаем по значку “Сортировка и фильтр”. В раскрывшемся перечне выбираем пункт “Фильтр”.
- Внутри самой верхней ячейки выделенного диапазона появится характерный значок фильтра в виде небольшого квадрата с треугольником, направленным вниз. Щелкаем по нему. Откроется меню, в котором убираем галочку напротив пункта “Пустые” и жмем OK.
- В таблице останутся только ячейки, содержащие какие-либо данные.
Условное форматирование и фильтрация:
Выполняем выделение диапазона ячеек. В главной вкладке в группе инструментов “Стили” нажимаем кнопку “Условное форматирование”. В раскрывшемся перечне останавливаемся на варианте “Правила выделения ячеек”, затем – выбираем “Больше”.
На экране отобразится окно форматирования:
в поле слева указываем число ;
в правом – щелкнув по текущему значению раскрываем список возможных цветовых схем и выбираем понравившийся вариант (или оставляем стандартный);
по готовности жмем OK.
Ко всем заполненным ячейкам будет применена цветовая схема, которую мы выбрали, в то время, как заливка пустых ячеек не изменилась
Повторно выделяем тот же самый диапазон (если выделение было снято) и включаем “Фильтр”.
Кликаем по значку фильтра, в открывшемся меню наводим указатель мыши на строку “Фильтр по цвету”, после чего откроется еще одно подменю, где останавливаемся на варианте “Фильтр по цвету ячейки” или “Фильтр по цвету шрифта” (в данном случае, это неважно).
В результате, из всего выделенного диапазона мы оставили только заполненные ячейки.
Независимо от того, каким из способов выше мы воспользовались (фильтр или условное форматирование с последующей фильтрацией), дальнейшие шаги следующие:
- Нажимаем кнопку “Копировать” в группе инструментов “Буфер обмена” (вкладка “Главная”).
- На этом же (или другом) листе отмечаем соразмерный диапазон, затем щелчком правой кнопки мыши по нему раскрываем меню, в котором кликаем по варианту вставки – “Значения” (значок в виде папки с цифрами 123). Тут есть один нюанс – выделять нужно диапазон, который располагается ниже самой нижней строки области с первоначальными данными.
- Нам удалось вставить данные (без первоначального форматирования ячеек). Мы можем продолжить работу с ними здесь или перенести (скопировать) в любое другое удобное место.
Сортировка вместо фильтрации:
В данном случае, мы выполняем те же самые шаги, что и при условном форматировании. Но вместо фильтра теперь выбираем “Сортировку по цвету”.
В отличие от фильтрации, переносить данные никуда не нужно, так как порядок строк не нарушен. Все, что требуется – это удалить пустые строки в выделенном диапазоне.
Защита листа и ячеек в Excel
Возможность и методы удаления
Для начала нужно выяснить, существует ли в принципе такая возможность, как удаление пустых ячеек в выбранном диапазоне данных (таблице), и подобрать оптимальный способ для реализации этой операции. Например, при простом выделении и удалении будет происходить смещение ячеек, что в некоторых случаях нежелательно или, и вовсе, недопустимо. В целом, удалить пустые ячейки проще всего при следующих условиях:
- Весь столбец (строка) не заполнен;
- Элементы в столбце (строке) не взаимосвязаны.
Когда незаполненных ячеек не так много, их удаление можно выполнить вручную. Однако в случаях, когда приходится иметь дело с большим количеством элементов, временные затраты нужно минимизировать, в чем поможет автоматизация процесса.
Удаление пустых строк в таблице без ключевого столбца
Воспользуйтесь этим способом, если в Вашей таблице присутствуют многочисленные пустые ячейки, разбросанные по разным столбцам, а Вам нужно удалить только те строки, которые не имеют ни одной ячейки с данными.
В этом случае у нас нет ключевого столбца, который бы помог определить пуста строка или нет. Поэтому, мы добавляем вспомогательный столбец к таблице:
- В конце таблицы добавьте столбец с именем Blanks и вставьте следующую формулу в первую ячейку столбца:
Эта формула, как и обещает её имя, подсчитывает пустые ячейки в заданном диапазоне. A2 и C2 – это соответственно первая и последняя ячейки текущей строки.
Скопируйте формулу на весь столбец. Как это сделать – смотрите пошаговую инструкцию Как вставить одинаковую формулу сразу во все выбранные ячейки.
Теперь в нашей таблице есть ключевой столбец! Примените фильтр к столбцу Blanks (выше дана пошаговая инструкция, как это сделать) чтобы показать строки только с максимальным значением (3). Число 3 означает, что все ячейки в этой строке пусты.
Далее выделите все отфильтрованные строки и удалите их целиком. Как это сделать – описано выше.В результате пустая строка (строка 5) будет удалена, все остальные строки (с пустыми ячейками или без) останутся на своём месте.
Формула массива
Этот метод уже на вкусившего вкус юзера. Хотя ничего уж очень сложного и нет, тем не наименее, этот метод я узрел у Николая Павлова на его веб-сайте и решил добавить его для полноты инфы о способностях очистки от пустых строк с применением формулы массива.
Итак, в чём же состоит этот метод по удалению пустых строк в Excel и как его использовать:
для большей простоты дадим имена нашим спектрам, для этого на вкладке «Формулы» нажимаете «Диспетчер имен» и присваиваете для 2-ух диапазонов имена, к примеру для изначальных данных с пустыми ячейками — «Пустые», а для обработанных — «БезПустых». Непременное условие в том, что спектры обязаны иметь однообразный итог, но вот размещение этих диапазонов могут и отличатся, это не критично;
последующим шагом это введение в первую ячейку второго спектра формулу:
=ЕСЛИ( СТРОКА() — СТРОКА (БезПустых) +1> ЧСТРОК( Пустые) — СЧИТАТЬПУСТОТЫ (Пустые);»»; ДВССЫЛ ( АДРЕС (НАИМЕНЬШИЙ ((ЕСЛИ (Пустые <>«»; СТРОКА (Пустые); СТРОКА()+ ЧСТРОК( Пустые))); СТРОКА() — СТРОКА (БезПустых)+1); СТОЛБЕЦ( Пустые);4))). Да я сам понимаю, что формула ужасная, сам, когда ее узрел, обалдел, тем не наименее, она рабочая и исправно делает возложенные на нее обязанности;
опосля прописания формулы, для вас необходимо окончить введение формулы «гарячей» композицией кнопок Ctrl+Shift+Enter, это нужно для того, что бы формула была введена как формула массива. Сейчас используя возможность в Excel как авто наполнение (копирование формулы, протягивая за крестик в углу) вы получили начальный спектр, но уже без пустых ячеек.
Удаление пустых ячеек из диапазона
Постановка задачи
Имеем диапазон ячеек с данными, в котором есть пустые ячейки:
Задача — удалить пустые ячейки, оставив только ячейки с информацией.
Способ 1. Грубо и быстро
- Выделяем исходный диапазон
- Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.
Выделяются все пустые ячейки в диапазоне.
Даем в меню команду на удаление выделенных ячеек: правой кнопкой мыши —Удалить ячейки (Delete Cells) со сдвигом вверх.
Способ 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, чтобы ввести функцию как формулу массива.
Как удаляются ячейки
Ячейки бывают двух типов: содержащие данные и пустые. В зависимости от этого отличается и сам процесс их удаления, т.к. в программе предусмотрена возможность автоматизации процесса выделения и последующего удаления пустых ячеек.
Сразу стоит отметить, что во время выполнения процедуры удаления одной или нескольких ячеек, табличные данные могут менять свою структуру, поскольку в результате проделанных шагов может происходить смещение некоторых областей таблицы. По этой причине, прежде чем приступать к удалению, необходимо предусмотреть нежелательные последствия, и на всякий случай, сохранить копию исходного документа.