Как найти первое или последнее положительное или отрицательное число в Excel?
При работе со столбцом чисел, содержащим как положительные, так и отрицательные значения, часто возникает необходимость быстро найти первое или последнее положительное (или отрицательное) число в диапазоне. Это особенно полезно при анализе данных, выявлении трендов или определении ключевых точек входа в больших наборах данных. Ручной поиск по объёмным массивам не только неэффективен, но и подвержен ошибкам. К счастью, Excel предлагает несколько практичных методов для решения этой задачи — от простых формул до автоматизированных подходов, позволяющих мгновенно извлекать нужные значения. Ниже представлены различные решения, адаптированные под разные сценарии, включая продвинутые техники, идеально подходящие для повторяющихся или масштабных операций.
Поиск первого положительного / отрицательного числа с помощью формулы массива
Поиск последнего положительного / отрицательного числа с помощью формулы массива
Макрос VBA для поиска первого / последнего положительного / отрицательного числа
Поиск первого положительного / отрицательного числа с помощью формулы массива
Чтобы извлечь первое положительное или отрицательное число из серии значений, можно воспользоваться формулами массива Excel. Этот метод идеально подходит пользователям, которым нужен быстрый и надёжный способ обработки умеренных по размеру диапазонов и которые уверенно работают с формулами — особенно в средах, где запрещено использование сторонних надстроек или макросов. Подход на основе массивов автоматически обновляется при изменении исходных данных, что делает его удобным для работы с динамическими списками. Ниже приведено описание реализации:
1. Выберите пустую ячейку и введите следующую формулу массива, чтобы получить первое положительное число:
=INDEX(A2:A18,MATCH(TRUE,A2:A18>,0,0))Здесь A2:A18 обозначает список данных, в котором выполняется поиск. Эта формула находит первую ячейку в диапазоне со значением больше 0 и возвращает её содержимое. См. следующий снимок экрана:
2. После ввода формулы нажмите одновременно Ctrl + Shift + Enter, а не просто Enter. Это гарантирует корректную работу формулы массива и вернёт первое положительное число из вашего списка, как показано в примере ниже:
Совет:Чтобы получить первое отрицательное число, используйте эту формулу (не забудьте нажать)Ctrl + Shift + Enterпосле ввода):
=INDEX(A2:A18,MATCH(TRUE,A2:A18<,0,0))В обеих формулах изменение условия ()>0 для положительных, <0 для отрицательных) позволяет выбрать нужный тип чисел. Обратите внимание: формулы массива не поддерживают ссылки на пустые ячейки, поэтому убедитесь, что ваш диапазон данных не содержит пустых ячеек — это обеспечит стабильные результаты. Если все числа в диапазоне только положительные или только отрицательные, формула может вернуть ошибку. В таких случаях рекомендуется использовать функцию IFERROR, чтобы скрыть ошибку и отобразить собственное сообщение.
Примечание: В последних версиях Excel (Office 365 и Excel 2021 и новее) может не потребоваться использовать Ctrl + Shift + Enter — достаточно просто нажать Enter благодаря поддержке динамических массивов.
Поиск последнего положительного / отрицательного числа с помощью формулы массива
Если ваша цель — найти последнее положительное или отрицательное значение в столбце, можно воспользоваться другой формулой массива. Этот подход идеально подходит для быстрого анализа конечных тенденций или определения самого свежего значения заданного типа. Отметим, что данный метод динамически реагирует на обновления данных, что особенно ценно при регулярном добавлении новых чисел в список.
1. Выберите пустую ячейку рядом со столбцом данных и введите следующую формулу массива, чтобы найти последнее положительное число:
=LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 >,0, $A$2:$A$18))Эта формула работает благодаря особенности функции ПРОСМОТР: при поиске очень большого числа она возвращает последнее найденное числовое совпадение. Выражение IF($A$2:$A$18 >0, $A$2:$A$18) сначала фильтрует только положительные числа, а затем ПРОСМОТР возвращает последнее из них. См. иллюстрацию ниже:
2. Подтвердите формулу, нажав Ctrl + Shift + Enter (если ваша версия Excel не поддерживает динамические массивы). Результат покажет последнее положительное значение в ограниченном диапазоне, как продемонстрировано ниже:
Чтобы вернуть последнее отрицательное число, используйте вместо этого следующую формулу массива, также с нажатием Ctrl + Shift + Enter:
=LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 <,0, $A$2:$A$18))Если положительные или отрицательные значения не найдены, формула вернёт ошибку ()#Н/Д). Чтобы корректно обрабатывать такие случаи, оберните формулу в функцию ЕСЛИОШИБКА. Например:
=IFERROR(LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 >,0, $A$2:$A$18)), "No match found")Важно избегать использования объединённых или комбинированных текстово-числовых форматов в вашем диапазоне, поскольку они могут нарушить корректность расчётов. Всегда проверяйте целостность данных перед применением этих методов, чтобы обеспечить максимальную точность.
Макрос VBA для поиска первого / последнего положительного / отрицательного числа
Если вам часто приходится искать первое или последнее положительное либо отрицательное число в нескольких диапазонах или объёмных наборах данных, автоматизация этой задачи с помощью макроса VBA существенно сэкономит время и снизит риск ошибок. Такой подход позволяет мгновенно находить нужное значение в выбранном диапазоне, что идеально подходит для пакетной обработки и регулярных аналитических операций. Решение на основе VBA особенно эффективно при работе со сложными критериями или настраиваемыми рабочими процессами, хотя и требует базового знакомства с инструментами разработчика Excel.
1. Нажмите Разработчик > Visual Basic, чтобы открыть окно Microsoft Visual Basic для приложений. Затем в редакторе VBA выберите Вставка > Модуль и скопируйте приведённый ниже код в новый модуль:
Sub FindFirstOrLastPosNegNumber()
Dim rng As Range
Dim cell As Range
Dim result As Variant
Dim firstPos As Variant, firstNeg As Variant
Dim lastPos As Variant, lastNeg As Variant
Dim selType As String
On Error Resume Next
Set rng = Application.InputBox("Select the data range", "KutoolsforExcel", Selection.Address, Type:=8)
If rng Is Nothing Then Exit Sub
selType = Application.InputBox("Type 'FirstPos' for first positive, 'FirstNeg' for first negative, 'LastPos' for last positive, or 'LastNeg' for last negative:", "KutoolsforExcel", "FirstPos", Type:=2)
If selType = "" Then Exit Sub
firstPos = Empty
firstNeg = Empty
lastPos = Empty
lastNeg = Empty
' Find first positive and first negative
For Each cell In rng
If IsNumeric(cell.Value) Then
If firstPos = Empty And cell.Value > 0 Then
firstPos = cell.Value
End If
If firstNeg = Empty And cell.Value < 0 Then
firstNeg = cell.Value
End If
If cell.Value > 0 Then
lastPos = cell.Value
End If
If cell.Value < 0 Then
lastNeg = cell.Value
End If
End If
Next cell
Select Case UCase(selType)
Case "FIRSTPOS"
result = firstPos
Case "FIRSTNEG"
result = firstNeg
Case "LASTPOS"
result = lastPos
Case "LASTNEG"
result = lastNeg
Case Else
result = "Invalid input"
End Select
If IsEmpty(result) Then
MsgBox "No matching value found in the selected range.", vbInformation, "KutoolsforExcel"
Else
MsgBox "Result: " & result, vbInformation, "KutoolsforExcel"
End If
End Sub2. Чтобы запустить макрос, нажмите F5(или кнопку)
Выполнить) и выполните следующие шаги:
- Появится диалоговое окно, предлагающее выбрать диапазон чисел (например, A2:A18).
- Далее укажите тип поиска: FirstPos — для первого положительного числа, FirstNeg — для первого отрицательного числа, LastPos — для последнего положительного числа или LastNeg — для последнего отрицательного числа (регистр не учитывается).
- После ввода выбранного варианта и подтверждения результат появится в окне сообщения.
Советы:
- Этот макрос умеет обрабатывать любой непрерывный числовой диапазон, выбранный пользователем, обеспечивая максимальную гибкость при работе с разнообразными структурами данных.
- Если указанный тип не соответствует ни одному числу в диапазоне, вместо ошибки вы получите уведомление.
- Убедитесь, что макросы включены в Excel, — это необходимо для корректной работы кода VBA.
- Если ваши данные содержат нечисловые значения, макрос будет игнорировать их при обработке.
Устранение неполадок и рекомендации: Для всех решений всегда убедитесь, что выделенный диапазон соответствует ожидаемому и не включает заголовки. При работе с большими диапазонами рекомендуем ограничивать их размер — это поможет избежать задержек при вычислениях и снижения производительности, особенно при использовании формул массива или макросов.
Если вы часто выполняете эту задачу или стремитесь расширить возможности настройки, подумайте о том, чтобы объединить несколько критериев в макросе или создать специальную кнопку для быстрого доступа. Всегда сохраняйте свою работу перед запуском новых сценариев VBA и тестируйте их на резервных копиях, особенно если вы только начинаете работать с программированием.
См. также:
Как найти первое или последнее значение, превышающее X, в Excel?
Как найти наибольшее значение в строке и столбце, чтобы вернуть соответствующий заголовок в Excel?
Как найти наибольшее значение и получить содержимое соседней ячейки в Excel?
Как найти максимальное или минимальное значение по условию в Excel?
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек