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

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

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

Метод 1. Грубо и стремительно

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

Метод 2. Формула массива

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

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

Сейчас выделим первую ячейку второго спектра (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. Чтоб применять эту функцию в нашем примере:

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

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

Методы удаления

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

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

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

Метод 1: выделение групп ячеек

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

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

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

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

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

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

Метод 3: применение сложной формулы

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

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

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

Мы рады, что смогли посодействовать Для вас в решении трудности.

Кроме данной нам статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

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

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

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

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

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

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

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

1. При помощи сортировки инфы;
2. При помощи наложения фильтра;
3. При помощи меню выделения группы ячеек;
4. При помощи формулы массива;
5. При помощи макроса (VBA).

Сортировка

Это самый обычный метод убрать с вашей рабочей области либо вычисляемого спектра незапятнанные строчки. Для вас необходимо выделить спектр, избрать на вкладке «Данные» клавишу «Сортировка» и в предоставленном диалоговом окне избираем вариант сортировки. Программка всё отсортирует по полочкам, и вы сможете удалить пустые строчки.

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

Фильтрация

Этот метод также является обычным и легкодоступным полностью хоть какому юзеру. Для внедрения фильтра на таблицу для вас нужно на вкладке «Основная» в разделе «Редактирование» жать клавишу «Сортировка и фильтр» и в открывшемся пт меню выбираете «Фильтр».

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

Я бы и этот метод причислил к довольно обычным и лёгким. Для внедрения этого варианта для вас нужен инструмент «Выделение группы ячеек». Удалить незаполненые строчки может быть в несколько шагов:

• выделение подходящего спектра;
• избираем вкладку «Основная», в блоке «Редактирование», кликаем «Отыскать и выделить» и в выпадающем меню выбираете «Выделение группы ячеек»;

• в диалоговом окне, в разделе «Выделить» отмечаем пункт «пустые ячейки»;

• перебегаем во вкладку «Основная» и жмем клавишу «Ячейки» и избираем пункт «Удалить»;
• в диалоговом окне выбираете пункт «удалить ячейки со сдвигом ввысь» и все незаполненые строчки будут удалены.

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

Этот метод уже на вкусившего вкус юзера. Хотя ничего уж очень сложного и нет, тем не наименее, этот метод я узрел у Николая Павлова на его веб-сайте и решил добавить его для полноты инфы о способностях очистки от пустых строк с применением формулы массива.

Итак, в чём же состоит этот метод по удалению пустых строк в Excel и как его использовать:

• для большей простоты дадим имена нашим спектрам, для этого на вкладке «Формулы» нажимаете «Диспетчер имен» и присваиваете для 2-ух диапазонов имена, к примеру для изначальных данных с пустыми ячейками — «Пустые», а для обработанных — «БезПустых». Непременное условие в том, что спектры обязаны иметь однообразный итог, но вот размещение этих диапазонов могут и отличатся, это не критично;

• последующим шагом это введение в первую ячейку второго спектра формулу:

=ЕСЛИ( СТРОКА() — СТРОКА (БезПустых) +1> ЧСТРОК( Пустые) — СЧИТАТЬПУСТОТЫ (Пустые);»»; ДВССЫЛ ( АДРЕС (НАИМЕНЬШИЙ ((ЕСЛИ (Пустые <>«»; СТРОКА (Пустые); СТРОКА()+ ЧСТРОК( Пустые))); СТРОКА() — СТРОКА (БезПустых)+1); СТОЛБЕЦ( Пустые);4))). Да я сам понимаю, что формула ужасная, сам, когда ее узрел, обалдел, тем не наименее, она рабочая и исправно делает возложенные на нее обязанности;

• опосля прописания формулы, для вас необходимо окончить введение формулы «гарячей» композицией кнопок Ctrl+Shift+Enter, это нужно для того, что бы формула была введена как формула массива. Сейчас используя возможность в Excel как авто наполнение (копирование формулы, протягивая за крестик в углу) вы получили начальный спектр, но уже без пустых ячеек.

Макрос

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

Перейдите во вкладку «Сервис», изберите раздел «Макрос» и нажмите клавишу «Редактор Visual Basic» либо нажав ALT+F11 и в показавшемся диалоговом окне редактора жмем «Insert» — «Module».

Как удалить пустые ячейки в Excel. 3 способа удаления пустых ячеек в Эксель

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

1

Случаи, в каких может быть удаление незаполненных ячеек

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

• Во всей строке либо столбце нет никакой инфы.
• Меж ячейками нет логической связи.

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

Решение 1: удаляем выделением группы ячеек

Проще всего пользоваться особым инвентарем выделения групп ячеек. Процесс выполнения:

1. Выделите проблемную зону, где скопились пустые ячейки, опосля нажмите кнопку F5.

1. Экран должен открыть последующее командное окно. Нажмите на интерактивную клавишу «Выделить».
2. Программка откроет очередное окно. Избираем «Пустые ячейки». Ставим галочку и жмем «ОК».
3. Происходит автоматическое выделение незаполненных мест. Нажатие ПКМ по хоть какой безинформационной области активирует открытие окна, где нужно надавить «Удалить».
4. Дальше раскроется «Удаление ячеек». Ставим галочку около «Ячейки со сдвигом ввысь». Соглашаемся нажатием клавиши «ОК».

1. В итоге, программка произведет автоматическое удаление нуждающихся в корректировке мест.
2. Для снятия выделения кликните ЛКМ в любом месте таблицы.

Примечание! Способ удаления со сдвигом выбирается только в вариантах, когда опосля области выделения нет строк, несущих какую-либо информацию.

Решение 2: применяем фильтрацию и условное форматирование

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

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

Разглядим последовательное описание фильтрации данных:

1. Выделяем область 1-го столбца. Отыскиваем на панели инструментов пункт «Редактирование». Нажав на него, покажется окно со перечнем опций. Перебегаем во вкладку «Сортировка и фильтр».

1. Избираем фильтр и активируем ЛКМ.

1. В итоге активизируется верхняя ячейка. Сбоку покажется значок в форме квадрата со стрелкой вниз. Это гласит о способности открытия окна с доп функциями.
2. Жмем на клавишу и в открывшейся вкладке убираем галочку напротив позиции «(Пустые)», щелкаем «ОК».

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

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

Сейчас разберем, как произвести условное форматирование вместе с фильтрацией:

1. Для этого произведите выделение проблемной зоны и, обнаружив на панели инструментов «Стили», активируем клавишу «Условное форматирование».

1. В открывшемся окне находим строчку «Больше» и перебегаем по данной ссылке.
2. Дальше в показавшемся окне в левом поле вписываем значение «0». В правом поле избираем понравившийся вариант цветовой заливки либо оставляем обычные значения. Кликаем «ОК». В итоге все ячейки с информацией окрасятся в избранный вами цвет.
3. В случае снятия программкой ранее выполненного выделения проводим его повторно и включаем инструмент «Фильтр». Наводим курсор на значение «Фильтр по цвету ячейки» либо по шрифту и активируем одно из положений.
4. В итоге останутся только ячейки, окрашенные цветом, а как следует, заполненные данными.

1. Повторно выделяем окрашенную цветом зону и находим в высшей части панели инструментов клавишу «Копировать», жмем ее. Она представлена 2-мя листами, наложенными друг на друга.
2. Выбрав другую область на этом листе, проводим очередное выделение.
3. Щелчком ПКМ открываем меню, где находим «Значения». Значок представлен в виде планшета с цифровым перечислением 123, жмем.

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

1. По итогу скопированные данные переносятся без внедрения цветового фильтра.

Предстоящая работа с данными может делается по месту либо с переносом ее в другую область листа.

Решение 3: применяем формулу

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

1. Выделяем спектр ячеек, нуждающихся в корректировке.
2. Потом кликаем ПКМ и находим команду «Присвоить имя». Присваиваем имя выделенному столбцу, нажимаем «ОК».

1. В любом месте на листе избираем вольную зону, которая соответствовует размеру области, где проводится корректировка. Кликаем ПКМ и вводим другое имя.

1. Опосля нужно активировать самую верхнюю ячейку вольной области и вписать в нее формулу: =ЕСЛИ(СТРОКА()-СТРОКА(Корректировка)+1>ЧСТРОК(Фамилии)-СЧИТАТЬПУСТОТЫ(Фамилии);””;ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(Фамилии <>””;СТРОКА(Фамилии);СТРОКА()+ЧСТРОК(Фамилии)));СТРОКА()-СТРОКА(Корректировка)+1);СТОЛБЕЦ(Фамилии);4))).

Примечание! Имена для областей выбираются произвольно. В нашем примере это «Фамилии» и «Корректировка».

1. Как данные формулы будут введены, жмем сочетание кнопок «Ctrl+Shift+Enter». Это нужно, поэтому как в формуле имеются массивы.

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

Заключение

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