|
Первое по порядку положительное значение с помощью ПОИСКПОЗ |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
[/offtop]
[/offtop]
Как найти первое / последнее положительное / отрицательное число в Excel?
На листе есть список чисел, которые имеют как положительные, так и отрицательные числа, если вы хотите найти первое или последнее положительное или отрицательное число в этом столбце, как вы могли бы это сделать?
Найдите первое положительное / отрицательное число с помощью формулы массива
Найдите последнее положительное / отрицательное число с помощью формулы массива
Найдите первое положительное / отрицательное число с помощью формулы массива
Чтобы получить первое положительное или отрицательное число в списке данных, вам может помочь следующая формула, сделайте следующее:
1. В пустой ячейке, помимо ваших данных, введите следующую формулу массива: =INDEX(A2:A18,MATCH(TRUE,A2:A18>0,0))(A2: A18 это список данных, который вы хотите использовать), см. снимок экрана:
2, Затем нажмите Shift + Ctrl + Enter вместе, чтобы вернуть первое положительное число в списке, см. снимок экрана:
Внимание: Чтобы получить первое отрицательное число в списке, вам просто понадобится эта формула: =INDEX(A2:A18,MATCH(TRUE,A2:A18<0,0)), и не забудьте нажать Shift + Ctrl + Enter ключи одновременно.
Найдите последнее положительное / отрицательное число с помощью формулы массива
Если вам нужно найти последнее положительное или отрицательное число в списке значений, вы также можете применить формулу массива.
1. Введите следующую формулу в пустую ячейку рядом с вашими данными, = ПРОСМОТР (9.99999999999999E + 307; ЕСЛИ ($ A $ 2: $ A $ 18> 0, $ A $ 2: $ A $ 18))( A2: A18 это список данных, который вы хотите использовать), см. снимок экрана:
2, Затем нажмите Shift + Ctrl + Enter ключи одновременно, чтобы вернуть последнее положительное число в списке, см. снимок экрана:
Внимание: Чтобы получить последнее отрицательное число в списке, вам просто нужна эта формула: = ПРОСМОТР (9.99999999999999E + 307; ЕСЛИ ($ A $ 2: $ A $ 18 <0, $ A $ 2: $ A $ 18)), и не забудьте нажать Shift + Ctrl + Enter ключи вместе.
Статьи по теме:
Как найти первое / последнее значение больше X в Excel?
Как найти максимальное значение в строке и заголовке столбца возврата в Excel?
Как найти максимальное значение и вернуть значение соседней ячейки в Excel?
Как найти максимальное или минимальное значение на основе критериев в Excel?
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (1)
Оценок пока нет. Оцените первым!
На чтение 2 мин. Просмотров 311 Опубликовано 21.05.2021
На листе есть список чисел, которые имеют как положительные, так и отрицательные числа. Если вы хотите найти первое или последнее положительное или отрицательное число в этом столбце, как вы могли бы это сделать? ?
Найти первое положительное/отрицательное число с помощью формулы массива
Найти последнее положительное/отрицательное число с формулой массива
Найдите первый положительное/отрицательное число с формулой массива
Чтобы получить первое положительное или отрицательное число в списке данных, вам может помочь следующая формула. Сделайте следующее:
1 . В пустой ячейке, помимо ваших данных, введите следующую формулу массива: = INDEX (A2: A18, MATCH (TRUE, A2: A18> 0,0)) , ( A2: A18 – это список данных, который вы хотите использовать), см. снимок экрана:
2 . Затем нажмите одновременно клавиши Ctrl + Shift + Enter , чтобы вернуть первое положительное число в списке, см. Снимок экрана:
: чтобы получить первое отрицательное число в списке, вам просто понадобится следующая формула: = INDEX (A2: A18, MATCH (TRUE, A2: A18 и не забудьте одновременно нажать клавиши Ctrl + Shift + Enter .
Найдите последнее положительное/отрицательное число с помощью формулы массива
Если вам нужно найти последнее положительное или отрицательное число в списке значений, вы также можете применить формулу массива.
1 . Введите следующую формулу в пустую ячейку рядом с вашими данными: = LOOKUP (9.99999999999999E + 307, IF ($ A $ 2: $ A $ 18> 0, $ A $ 2: $ A $ 18)) ( – это список данных, который вы хотите использовать), см. снимок экрана:
2 . Затем нажмите одновременно клавиши Ctrl + Shift + Enter , чтобы вернуть последнее положительное число в списке, см. Снимок экрана:
: чтобы получить последнее отрицательное число в списке, вам просто понадобится следующая формула: = ПРОСМОТР (9.99999999999999E + 307, IF ($ A $ 2: $ A $ 18 и не забудьте нажать Ctrl + Shift + Enter вместе.
Как найти первое/последнее значение больше X в Excel?
Как найти наивысшее значение в строке и заголовок возвращаемого столбца в Excel?
Как найти максимальное значение и вернуть значение соседней ячейки в Excel?
Как найти максимальное или минимальное значение на основе критериев в Excel?
Мы уже научились находить позицию первой буквы в ячейке, формула поиска позиции числа имеет схожий принцип – превращает строку в массив символов и проверяет каждый на принадлежность к цифрам.
Вариант 1
{=ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)-1);0)}
Как она это делает? Из массива вычитается единица (это может быть любое число), далее полученный массив ошибок и валидных значений обрабатывается функцией ЕОШ. Ошибки вычисления (там, где были нечисловые символы) вернут ИСТИНА, цифровые символы – ЛОЖЬ.
Функция ПОИСКПОЗ возвращает позицию первой цифры через поиск первого значения ЛОЖЬ.
Как и все формулы массива в Excel, данная формула вводится сочетанием клавиш Ctrl+Shift+Enter.
Вариант 2
Есть и вот такая вариация формулы массива, использующая функции МИН, ПОИСК, СИМВОЛ и ЕСЛИОШИБКА:
{=МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(48:57));A1);""))}Её принцип несколько иной –
- каждый из 10 цифровых символов ищется внутри строки функцией ПОИСК, возвращающей в успешных случаях его позицию, а в неуспешных – ошибку
- массив результатов обрабатывается функцией ЕСЛИОШИБКА и вместо ошибок вставляет в массив пустоты, чтобы из-за ошибок не прерывалась работа функции МИН
- результирующий массив обрабатывает функция МИН и возвращает минимальную из позиций – это и есть позиция первой цифры в ячейке
Функция более требовательна к ресурсам компьютера (строковые функции “дороже” математических), поэтому рекомендуется первый вариант.
Применение формул
Наиболее понятное применение – это когда нужно отделить в ячейке текст от цифр или удалить текст до первой цифры.
Если в тексте ячейки встречаются нечисловые знаки, то приходится извлекать текст числа (для примера из ячейки A1) специальной формулой, рассматривающей текст ячейки как массив, поэтому вводить формулы надо как формулу массива.
=ПСТР(A1;НачЧисла;ДлинаЧисла)
НачЧисла = ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР(A1) )); 1))); 0)
Окончание числа определяем как индекс пробела в исходной строке после начала числа (которого может и не быть, поэтому добавляем пробел)
ДлинаЧисла = НАЙТИ(” “; A1&” “;НачЧисла)-НачЧисла
после подстановки получаем:
ДлинаЧисла = НАЙТИ(” “; A1&” “;ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР(A1) )); 1))); 0))- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР(A1) )); 1))); 0)
Формула для текста извлечённого числа:
=ПСТР(A1; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР(A1) )); 1))); 0); НАЙТИ(” “; A1&” “;ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР(A1) )); 1))); 0))-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР(A1) )); 1))); 0))
Формула для значения извлечённого числа:
=ЗНАЧЕН(ПСТР(A1; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР(A1) )); 1))); 0); НАЙТИ(” “; A1&” “;ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР(A1) )); 1))); 0))-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР(A1) )); 1))); 0)))
Эти формулы не учитывают, что на разных компьютерах могут быть разные настройки для десятичного разделителя (или запятая или пробел), а в случае отсутствия цифр в строке эти формулы дают ошибку.
Заменим десятичный разделитель в числе на правильный десятичный разделитель и обработаем ошибку если в ячейке вообще нет цифр:
Формула для значения извлечённого числа:
=ЕСЛИОШИБКА( ЗНАЧЕН( ПСТР( ЕСЛИ( ПСТР(1/3; 2; 1)=”,”; ПОДСТАВИТЬ(A1; “.”; “,”); ПОДСТАВИТЬ(A1; “,”; “.”)); ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР( A1 ))); 1))); 0); НАЙТИ(” “; A1&” “; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР(A1) )); 1))); 0))-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ(“1:”&ДЛСТР( A1 ))); 1))); 0))); “—-“)
Формула для текста извлечённого числа:
=ЕСЛИОШИБКА( ПСТР( ЕСЛИ( ПСТР(1/3; 2; 1)=”,”; ПОДСТАВИТЬ(A1; “.”; “,”); ПОДСТАВИТЬ(A1; “,”; “.”)); ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР( A1 ))); 1))); 0); НАЙТИ(” “; A1&” “; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР( A1; СТРОКА( ДВССЫЛ( “1:”&ДЛСТР(A1) )); 1))); 0))-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ(“1:”&ДЛСТР( A1 ))); 1))); 0)); “—-“)
Пример использования формул, позволяющих извлекать первое число из текстовой строки, независимо от установленного на компьютере десятичного разделителя.
