Как найти первое / последнее положительное / отрицательное число в 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))Эта формула работает за счет использования поведения функции LOOKUP, которая возвращает последнее найденное числовое совпадение для очень большого числа. Здесь IF($A$2:$A$18 >0, $A$2:$A$18) фильтрует только положительные числа, а LOOKUP затем возвращает последнее вхождение. Смотрите иллюстрацию ниже:
2. Подтвердите формулу, нажав Ctrl + Shift + Enter (если ваша версия Excel не поддерживает динамические массивы). Результат покажет последнее положительное значение в указанном диапазоне, как продемонстрировано ниже:
Чтобы вернуть последнее отрицательное число, используйте следующую формулу массива, также с нажатием Ctrl + Shift + Enter:
=LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 <0, $A$2:$A$18))Если ни одно положительное или отрицательное значение не найдено, формула вернет ошибку (#N/A). Чтобы элегантно обработать такие случаи, заключите формулу в IFERROR. Например:
=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 for Applications. Затем в редакторе VBA щелкните Insert > Module и скопируйте следующий код в новый модуль:
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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек