Excel выбрать непустые ячейки из диапазона

Обновлено: 07.07.2024

Иногда вам может потребоваться заменить все непустые ячейки тем же текстом в выбранном диапазоне. Чтобы заменить все непустые ячейки в диапазоне, вы должны сначала выбрать их. С Выбрать непустые ячейки полезности Kutools for Excel, вы можете выбрать все непустые ячейки из диапазона одним щелчком мыши.

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Нажмите на Kutools > Выберите > Выбрать непустые ячейки. Смотрите скриншот:

Выбрать непустые ячейки в диапазоне в Excel

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

2. Нажмите Kutools > Выберите > Выбрать непустые ячейки. Смотрите скриншот:

3. Затем появится диалоговое окно с информацией о том, сколько непустых ячеек было выбрано в указанном диапазоне. Щелкните значок OK чтобы закрыть его.

Пока все непустые ячейки были выбраны, как показано ниже:

Внимание: Эта функция также может быть применена к нескольким разделам на листе.

Демо

Рекомендуемые инструменты для повышения производительности
Следующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?
Office Tab : Использование удобных вкладок в вашем офисе , как и в случае Chrome, Firefox и New Internet Explorer.
Kutools for Excel : Более 300 дополнительных функций для Excel 2019, 2016, 2013, 2010, 2007 и Office 365.
Classic Menu for Office : Верните знакомые меню в Office 2007, 2010, 2013, 2016, 2019 и 365, как если бы это были Office 2000 и 2003.

Kutools for Excel

Описанная выше функциональность - лишь одна из 300 мощных функций Kutools for Excel.

Разработано для Excel (Office) 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатная загрузка и использование в течение 60 дней.

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

Задача: найти значение продаж в последнем месяце по каждому филиалу, т.е. для Москвы это будет 78, для Питера - 41 и т.д.

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

Универсальным решением будет использование функции ПРОСМОТР (LOOKUP) :

У этой функции хитрая логика:

Она по очереди (слева-направо) перебирает непустые ячейки в диапазоне (B2:M2) и сравнивает каждую из них с искомым значением (9999999).
Если значение очередной проверяемой ячейки совпало с искомым, то функция останавливает просмотр и выводит содержимое ячейки.
Если точного совпадения нет и очередное значение меньше искомого, то функция переходит к следующей ячейке в строке.

Легко сообразить, что если в качестве искомого значения задать достаточно большое число, то функция пройдет по всей строке и, в итоге, выдаст содержимое последней проверенной ячейки. Для компактности, можно указать искомое число в экспоненциальном формате, например 1E+11 (1*10 11 или сто миллиардов).

Если в таблице не числа, а текст, то идея остается той же, но "очень большое число" нужно заменить на "очень большой текст":

Применительно к тексту, понятие "большой" означает код символа. В любом шрифте символы идут в следующем порядке возрастания кодов:

латиница прописные (A-Z)
латиница строчные (a-z)
кириллица прописные (А-Я)
кириллица строчные (а-я)

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

Вот так. Не совсем очевидное, но красивое и компактное решение. Для поиска последней непустой ячейки в столбцах работает тоже "на ура".

Добрый день, снова прошу помощи
Как с листа1 на лист2 перенести значения не пустых ячеек

Больше всего смущают "разрывные" диапозоны (AB и F)

Добрый день, снова прошу помощи
Как с листа1 на лист2 перенести значения не пустых ячеек

Больше всего смущают "разрывные" диапозоны (AB и F) HoBU4OK

Я думал, ты остроглазый лев, а ты слепая собака :-)

Больше всего смущают "разрывные" диапозоны (AB и F) Автор - HoBU4OK
Дата добавления - 09.07.2014 в 08:59

Можно начать с подготовительных облегчений. Под исходным диапазоном через строку можно создать "конкатенирующий диапазон", по виду значений напоминающий сбегающую "лесенку" в каждой колонке (в прилагаемом файле - синим шрифтом). Еще через строку - обрезание концевых разделителей (красным шрифтом). Дальше - подумать, что делать дальше с достигнутым красным состоянием Можно начать с подготовительных облегчений. Под исходным диапазоном через строку можно создать "конкатенирующий диапазон", по виду значений напоминающий сбегающую "лесенку" в каждой колонке (в прилагаемом файле - синим шрифтом). Еще через строку - обрезание концевых разделителей (красным шрифтом). Дальше - подумать, что делать дальше с достигнутым красным состоянием Gustav Не получается, много ПЕРЕПРОБОВАЛ, а ЛЕВСИМ убирает из фамилий общие буквы )))))
И желательно СЧИТАТЬ "ЗНАЧЕНИЯ" ЯЧЕЕК, Т.К при "перетаскивании" значений формула не "считает". Не получается, много ПЕРЕПРОБОВАЛ, а ЛЕВСИМ убирает из фамилий общие буквы )))))
И желательно СЧИТАТЬ "ЗНАЧЕНИЯ" ЯЧЕЕК, Т.К при "перетаскивании" значений формула не "считает". HoBU4OK

Я думал, ты остроглазый лев, а ты слепая собака :-)

Значения в ячейках постоянно меняются, при этом как "правило" их туда перетаскивают

формулы не пересчитывают. значение ссылок меняеться.

Может все таки есть какие нибудь способы.

Значения в ячейках постоянно меняются, при этом как "правило" их туда перетаскивают

формулы не пересчитывают. значение ссылок меняеться.

Может все таки есть какие нибудь способы. HoBU4OK

формулы не пересчитывают. значение ссылок меняеться.

Может все таки есть какие нибудь способы. Автор - HoBU4OK
Дата добавления - 10.07.2014 в 18:17

Есть функция UDF. На мой взгляд очень удобная функция. Написал её Alex_ST, за что ему Спасибо! Для работы этой функции макросы должны быть включены. Есть функция UDF. На мой взгляд очень удобная функция. Написал её Alex_ST, за что ему Спасибо! Для работы этой функции макросы должны быть включены. gling

Ещё вариант с UDF (за основу взят макрос nilem из Готовых решений)

upd Перевложила файл

Ещё вариант с UDF (за основу взят макрос nilem из Готовых решений)

upd Перевложила файл Pelena

upd Перевложила файл Автор - Pelena
Дата добавления - 10.07.2014 в 22:59

Pelena, Ваш вариант по внешнему виду наиболее симпатичен, только я совершенно не умею пользоваться макросами и ВБА, подскажите КАК и КУДА их добавлять (можно ссылку на литературу, а лучше своими словами).
gling, Ваш ответ также удобен и симпатичен, просьба такая же - поясните как этим пользоваться.
"Перенос" и "вставка" как формулы в данном случае не помогает. или я что-то не так делаю?

Я думал, ты остроглазый лев, а ты слепая собака :-)

Заранее спасибо. Автор - HoBU4OK
Дата добавления - 11.07.2014 в 15:44

Открываете файл с решением, нажимаете Alt+F11, видите текст функции пользователя (UDF), выделяете его и даёте команду Копировать. Дальше открываете файл, в котором она должна работать, Alt+F11, в меню Insert выбираете команду Module и даёте команду Вставить. Функция скопирована, теперь она доступна как любая другая функция Excel. Осталось только в ячейке написать формулу. Обратите внимание, что в моём варианте, если надо задействовать несмежные диапазоны, в функции должны ставиться дополнительные скобки.

Начальные сведения о макросах можно почерпнуть из этой статьи

Начальные сведения о макросах можно почерпнуть из этой статьи Pelena

Начальные сведения о макросах можно почерпнуть из этой статьи Автор - Pelena
Дата добавления - 11.07.2014 в 16:16

Допустим, в диапазоне существует много несмежных пустых ячеек, и теперь вам нужно выбрать все ячейки с текущим содержимым. Как легко выбрать все непустые ячейки в Excel? Есть несколько уловок, чтобы решить эту проблему.

Выберите непустые ячейки в диапазоне с помощью специальной функции Перейти
Выберите непустые ячейки в диапазоне с помощью VBA (только один шаг)

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

Функция Go To Special может помочь нам выбрать непустые ячейки в выбранном диапазоне с помощью следующих шагов.

1. Выберите диапазон, из которого вы выберете все непустые ячейки, и нажмите F5 Клавиша открытия диалогового окна "Перейти". Затем нажмите кнопку Особый кнопку, чтобы открыть диалоговое окно Перейти к специальному.

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

2. В диалоговом окне "Перейти к специальному" установите флажок Константы вариант, Номера вариант, Текст вариант, Логика вариант, и ошибки и нажмите OK кнопку.

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

Один щелчок, чтобы выбрать все непустые ячейки в диапазоне в Excel

Kutools для Excel Выбрать непустые ячейки Утилита может помочь вам быстро выбрать все непустые ячейки из выбранного диапазона одним щелчком мыши. Полнофункциональная бесплатная 30-дневная пробная версия!

Выберите непустые ячейки в диапазоне с помощью VBA

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

1, нажмите другой + F11 клавиши, чтобы открыть окно Microsoft Visual Basic для приложений.

2, Нажмите Вставить > Модуль, а затем вставьте следующий код VBA в открывающееся окно модуля.

VBA: выберите непустые ячейки из диапазона

3, нажмите F5 ключ или щелкните Запустите кнопку для запуска этого VBA.

4. В открывшемся диалоговом окне укажите диапазон, в котором вы будете выбирать только непустые ячейки, и нажмите кнопку OK кнопку.

Теперь все непустые ячейки выбираются сразу в указанном диапазоне.

Выберите непустые ячейки в диапазоне с помощью Kutools for Excel

Kutools для Excel Выбрать непустые ячейки Утилита позволяет выбрать все непустые ячейки из определенного диапазона одним щелчком мыши.

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Выберите диапазон, в котором вы выберете только непустые ячейки, и нажмите кнопку Kutools > Выберите > Выбрать непустые ячейки.

Теперь все непустые ячейки в указанном диапазоне выбираются сразу.

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Читайте также: