Как вернуть первую/последнюю непустую ячейку в строке или столбце?
В повседневной работе с Excel часто встречаются наборы данных, где только некоторые ячейки содержат значения, а остальные пустые. Часто может потребоваться быстро найти самую первую или последнюю заполненную ячейку в определенной строке или столбце. Хотя это легко сделать при малом количестве записей, в длинных строках или столбцах ручной поиск становится неэффективным, трудоемким и подверженным ошибкам.
Существует несколько практических способов эффективно получить значение первой или последней непустой ячейки в строке или столбце, что поможет вам автоматизировать эту задачу и сэкономить драгоценное время. Ниже вы найдете как формулы, так и другие практические решения, подходящие для различных реальных ситуаций, включая работу с динамическими диапазонами данных или большими наборами данных.
Вернуть первую непустую ячейку в строке или столбце с помощью формулы
Вернуть последнюю непустую ячейку в строке или столбце с помощью формулы
Вернуть значение первой или последней непустой ячейки с помощью макроса VBA
Найти первую или последнюю непустую ячейку, используя функцию фильтра в Excel
Вернуть первую непустую ячейку в строке или столбце с помощью формулы
Чтобы извлечь первую ячейку, содержащую данные из строки или столбца, можно использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ. Этот подход применяется, когда вы хотите получить значение программно, сохраняя динамичность листа при изменении базовых данных. Вот как это сделать:
1. В пустой ячейке рядом с вашим диапазоном данных введите следующую формулу:
=INDEX(A1:A13,MATCH(TRUE,INDEX((A1:A13<>0),0),0))Например, если ваши данные находятся в ячейках A1:A13, эта формула вернет первую непустую и ненулевую ячейку. См. пример ниже:
2. Нажмите клавишу Enter. Значение первой ячейки в диапазоне, которая не является пустой (или не равна нулю, согласно логике формулы), будет отображено следующим образом:
Примечания и советы:
- В приведенной выше формуле вы можете изменить A1:A13 на ссылку на любой столбец или строку (например, 1:1 для строки 1 или B2:M2 для части строки).
- Этот метод надежно работает для одной строки или одного столбца. Для таблиц или многострочных диапазонов рекомендуется применять формулу к каждой строке или столбцу индивидуально.
- Если формула возвращает ошибку (#Н/Д), убедитесь, что ваш диапазон содержит хотя бы одну непустую и ненулевую ячейку.
- Помните, для истинных пустых ячеек (""), замените
<>0на<>"", если вы хотите игнорировать только пустые ячейки, а не нули.
Раскройте магию Excel с Kutools AI
- Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
- Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
- Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
- Интерпретация формул: Легко разбирайтесь в сложных формулах.
- Перевод текста: Преодолейте языковые барьеры в ваших таблицах.
Вернуть последнюю непустую ячейку в строке или столбце с помощью формулы
Чтобы получить значение из последней непустой ячейки в заданном диапазоне, формула на основе массива ПРОСМОТР предоставляет эффективное и простое решение для динамических или изменяющихся данных. Это особенно полезно для автоматического определения последней записи в списке или сводной таблице.
1. Введите следующую формулу в пустую ячейку рядом с вашим целевым диапазоном:
=LOOKUP(2,1/(A1:A13<>""),A1:A13)Эта формула сканирует указанный диапазон и возвращает значение последней ячейки, которая не является пустой. Например, используя A1:A13 в качестве диапазона:
2. После нажатия Enter, Excel рассчитает и отобразит значение в последней непустой ячейке:
Примечания и рекомендации:
- Вы можете использовать эту формулу с любым одиночным столбцом или строкой (B1:B20, F8:F30 или 2:2 и т.д.). Обновите ссылку на диапазон по необходимости.
- Если ваши данные включают нули, которые вы хотите игнорировать, вы можете заменить
A1:A13<>""наA1:A13<>0, но будьте внимательны, чтобы различать истинные пустые ячейки и нули. - Этот подход лучше всего работает для простых диапазонов данных. Для диапазонов с формулами, возвращающими "" (пустой текст), эта формула считает такие ячейки пустыми.
- Если все ячейки пустые, формула вернет ошибку #Н/Д.
Вернуть значение первой или последней непустой ячейки с помощью макроса VBA
Для пользователей, работающих с большими наборами данных или нуждающихся в автоматизации повторяющихся задач, простой макрос VBA может значительно упростить процесс — особенно когда диапазоны меняются или включают много строк или столбцов. В отличие от формул, VBA выполняет действия по запросу, такие как поиск первой или последней непустой ячейки, что делает его идеальным для повторяющихся операций на нескольких диапазонах.
1. Откройте редактор VBA, перейдя в Разработчик > Visual Basic. В открывшемся окне VBA нажмите Вставка > Модуль и вставьте одну из следующих процедур в окно модуля:
Макрос для поиска первой непустой ячейки в выбранном диапазоне:
Sub FindFirstNonBlankCell()
Dim rng As Range
Dim cell As Range
Dim firstValue As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
firstValue = ""
For Each cell In rng
If cell.Value <> "" Then
firstValue = cell.Value
Exit For
End If
Next cell
If firstValue <> "" Then
MsgBox "The first non blank cell value is: " & firstValue, vbInformation, xTitleId
Else
MsgBox "No non blank cells found.", vbExclamation, xTitleId
End If
End SubАналогично, вот код для поиска последней непустой ячейки:
Sub FindLastNonBlankCell()
Dim rng As Range
Dim cell As Range
Dim lastValue As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
lastValue = ""
For Each cell In rng
If cell.Value <> "" Then
lastValue = cell.Value
End If
Next cell
If lastValue <> "" Then
MsgBox "The last non blank cell value is: " & lastValue, vbInformation, xTitleId
Else
MsgBox "No non blank cells found.", vbExclamation, xTitleId
End If
End Sub2. Чтобы выполнить код, нажмите Выполнить кнопку 
в редакторе VBA. Вам будет предложено выбрать целевой диапазон для поиска непустых ячеек. После выбора и подтверждения, диалоговое окно покажет либо первую, либо последнюю непустую ячейку в зависимости от того, какой макрос вы запустили.
- Эти макросы гибкие и подходят для строк и столбцов независимо от размера данных.
- VBA позволяет автоматизировать и выполнять повторяющуюся обработку, что делает его идеальным для частых или крупномасштабных задач.
- При запуске макросов обязательно сохраните свою книгу и, при необходимости, включите макросы. Всегда тестируйте на пробных данных, чтобы проверить точность перед применением к важным данным.
Найти первую или последнюю непустую ячейку, используя функцию фильтра в Excel
Для пользователей, которым нужен быстрый визуальный способ определить непустые значения — особенно с очень большими столбцами данных — встроенная функция фильтра в Excel может помочь выделить непустые записи одним взглядом. Хотя этот метод не автоматически возвращает значение в другую ячейку, он очень эффективен для просмотра или навигации во время анализа данных.
Вот как визуально найти первые или последние непустые ячейки через фильтрацию:
- Выберите столбец или строку, содержащую ваши данные. Вы можете выбрать весь столбец (например, щелкнув по букве столбца) для более удобной фильтрации.
- Нажмите на вкладку Данные, затем выберите Фильтр.
- Нажмите маленькую стрелку фильтра в заголовке вашего диапазона или таблицы.
- Снимите флажок (Пустые), чтобы оставить видимыми только заполненные ячейки.
- После фильтрации первое видимое значение вверху столбца является вашей первой непустой ячейкой; прокрутите вниз, чтобы увидеть последнее.
Преимущества: Метод фильтрации быстрый, не требует формул и хорошо работает даже для столбцов с тысячами строк.
Недостатки: Решение только визуальное — оно не выводит результат в ячейку и не поддерживает автоматизацию, как формулы и VBA. Тем не менее, оно идеально подходит для ручных проверок, обзоров и интерактивного исследования данных.
Устранение неполадок и рекомендации:
Если фильтр, кажется, не работает, убедитесь, что вы не выбрали только часть данных, так как это может привести к некорректному применению фильтра. Удалите фильтры после завершения, нажав Данные > Очистить чтобы восстановить полный вид ваших данных.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек