1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро найти или вернуть второе по величине или наименьшее значение в Excel?

Excel: как найти второе по величине значение в диапазоне, определяемом другим столбцом?

То, что мне нужно от Excel, это второе по величине значение в диапазоне, определенном «Code», который определен в другом столбце.

При настройке таблицы значение находится в первом столбце, а код — во втором. Для диапазона значений с кодом «201» я хотел бы получить второе по величине значение в третьем столбце.

Значение код второе по величине значение
20 201 60 30 201 60 60 201 60 10 201 60 80 201 60 34 301 21 21 301 21 50 401 50 10 401 50 70 401 50

Я пытался использовать функцию «Large». Однако мне нужно иметь возможность ограничивать массив на основе значения в другой ячейке, и я не уверен, какую формулу мне нужно использовать.

Надеюсь, кто-нибудь поможет мне в этом вопросе!

2 ответа

  • Как найти второе наибольшее число с помощью javascript?

Как найти второе по значению число в массиве

У меня есть проблема показать второе по величине значение. Вот код program testeFeldZweitMax (input, output); < testet die Funktion FeldZweitMax >const FELDGROESSE = 10; type tIndex = 1..FELDGROESSE; tFeld = array [tIndex] of integer; var Feld : tFeld; i : integer; function FeldZweitMax (var.

Ты сможешь это сделать:

в этой формуле:
$B$2:$B$11 -это столбец вашего кода
$A$2:$A$11 -это столбец значений

Является формулой массива, поэтому нажмите ctrl + shift + enter, чтобы вычислить формулу, перетащите и заполните ее до конца.

Предположим, что ваши данные начинаются в ячейке A5, где столбец A — это значение, а столбец B — код.

В ячейку E5 введите код, на который вы ориентируетесь. Введите позицию в ячейку E6. 1 — самая высокая, 2 — вторая по высоте и т. Д.

Воспользуйтесь этой формулой в F5.

Нажмите control+shift+enter, и он будет выглядеть следующим образом. Это формула массива

По сути, это вопрос просмотра ячеек с B5 по B14, проверки, равны ли они какому-либо коду в E5, а затем записи формы соответствующей ячейки в столбце A в таблице. Большая часть формулы затем возвращает самый высокий уровень, основанный на диапазоне, который вы имеете в E6.

Похожие вопросы:

Я пытаюсь найти второе по значимости значение для этого критерия в Excel. Итак, формула, которую я пытался использовать, была =LARGE(IF($B$1:$B$216=1,$F$1:$F$216,),2) . Проблема в том, что она возвращает.

Я пытаюсь найти второе по величине значение в столбце и только второе по величине значение. select a.name, max(a.word) as word from apple a where a.word < (select max(a.word) from apple a) group.

Читайте так же:
Как быстро найти позицию первой буквы (альфа) из строки в Excel?

Таблица EmpSalary в таблице сотрудников. Мне нужно найти вторую по величине зарплату, выплачиваемую компанией. Как найти второе по величине значение (зарплату) в таблице?

Найти второе по величине число в матрице

У меня есть проблема показать второе по величине значение. Вот код program testeFeldZweitMax (input, output); < testet die Funktion FeldZweitMax >const FELDGROESSE = 10; type tIndex =.

На листе (HISTORICALS) столбец даты (A) отфильтрован таким образом, что я могу получить наибольшее и второе наибольшее значение даты. В настоящее время этот столбец имеет.

Если у меня есть список полов в одном столбце и числовые значения в другом столбце, я хочу найти наибольшее значение f (женский пол), второе наибольшее значение и третье наибольшее значение.

Это второе наибольшее значение, которое мне нужно. По значению я могу получить первое, используя max(имя поля), но по значению я могу получить второе. Есть ли какой-нибудь пользовательский код. Я начинающий, поэтому.

Чтобы исключить повторяющиеся значения, я преобразовал список в набор, а не в список, а затем снова преобразовал его в список. К сожалению.

В SQL, как я могу выбрать второе наибольшее значение в каждой группе? Например, в таблице ниже, значение IDs ID1 2 ID1 3 ID1 4 ID2 1 ID2 2 ID2 5 При группировке по IDs.

Как получить первое или второе наименьшее значение в таблице Excel

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

Как получить первое минимальное значение?

Давайте создадим простую таблицу, чтобы показать, как решить эту задачу:

Список транзакций.

В этом примере данные таблицы, в которой вы хотите найти значение, находятся в диапазоне A6:B18. На рисунке видно, что столбец A содержит значения, а столбец B — соответствующие им суммы. Кроме того, вы можете видеть, что среди сумм есть несколько минимальных значений, разбросанных по разным строкам.

Для определения значения столбца A, соответствующего первой наименьшей сумме в столбце B, необходимо выполнить два простых действия:

  1. В ячейке B3 введите следующую формулу:
  2. Подтвердите ввод формулы комбинацией горячих клавиш CTRL+SHIFT+Enter, так как она должна выполнится в массиве. Если все сделано правильно в строке формул появятся фигурные скобки <>.

Результатом является значение, соответствующее первому наименьшему слагаемому.

Детальный разбор формулы для первого наименьшего значения

Функция ИНДЕКС — самая важная функция в этой формуле. Подобные функции обычно используются для поиска значения в заданной таблице (в диапазоне A6:A18, указанном в первом аргументе) по координатам, указанным во втором (номер строки таблицы) и третьем (номер столбца таблицы) аргументах. Поскольку таблица, указанная для функции INDEX, имеет только один столбец A, третий аргумент может быть опущен (необязательно). При этом второй аргумент использует одновременно несколько функций в диапазоне B6:B18.

Читайте так же:
Как быстро вставить строку по текущей дате в Excel?

Первый аргумент функции IF проверяет, является ли значение каждой ячейки в диапазоне B6:B18 наименьшим числом. Это создает массив булевых значений TRUE и FALSE в памяти. В данном примере массив содержит только три элемента с положительным результатом, так как в этом числе содержатся те же минимальные значения, что и в исходном массиве. Если результат положительный, то формула переходит к следующему шагу вычисления, а если отрицательный, то функция возвращает в массив памяти пустое текстовое значение.

Следующим вычислительным шагом формулы является определение того, какие номера строк содержат эти минимальные суммы. Этот шаг необходимо выполнить для того, чтобы определить первое минимальное значение между B6 и B18. Для выполнения этой задачи используется функция STROCK, которая создает в памяти программы следующий массив, содержащий номер каждой строки в файле. В вашей исходной таблице вычтите из этих номеров номер первой строки. Поскольку функция ИНДЕКС работает с номерами строк таблиц, а не с номерами строк листов Excel, это принципиально. Мы вычитаем строки, которые находятся над таблицей, из номера каждой строки рабочего листа, чтобы получить истинный номер исходной таблицы.

В памяти программы создается массив номеров строк. MIN возвращает номер строки, соответствующий наименьшему значению в таблице. Функция INDEX использует это число в качестве второго аргумента, поэтому возвращает значение из A6:B18 с тем же номером строки.

Как получить первое наибольшее значение?

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

Не забудьте для подтверждения формулы нажать комбинацию горячих клавиш CTRL+SHIFT+Enter, чтобы на была выполнена в массиве <>.

ЕСЛИ МИН.

Просто измените одну из функций MIN на MAX в требуемом месте.

Как получить второе наименьшее значение?

Чтобы решить эту проблему, введите новую модифицированную формулу в ячейку F3

Нажмите CTRL+SHIFT+Enter еще раз, чтобы запустить формулу в массиве и сразу увидеть ее результат:

НАИМЕНЬШИЙ.

Эта формула основана на первой формуле, но на этот раз мы используем новую функцию NATIVE вместо первой функции MIN. Сложная функция LESS является усовершенствованием функции MIN. Он дает первый наименьший, второй наименьший, третий наименьший и так далее. Порядковый номер наименьших значений указывается во втором аргументе функции. В данном случае это число 2. Поэтому мы извлекаем второй наименьший порядковый номер из памяти массива для функции INDEX.

Читайте так же:
Как быстро преобразовать формат даты между европейским и американским в Excel?

Как получить первое наибольшее число?

Чтобы получить второе по величине число, замените функцию LOWEST в формуле на функцию OTHER:

Принцип работы функции NEXT обратен принципу работы функции MINIMUM. Поэтому здесь мы изменим вторую функцию с MIN на MAX.

Предупреждение. Если количество дубликатов в диапазоне меньше, чем число во втором аргументе функций: ЧИСЛО или ЧИСЛО, то по понятным причинам эти функции возвращают ошибку «#ЧИСЛО!». Всегда заранее решайте, какую задачу вы хотите, чтобы выполняла формула!

Получить первое, последнее или определенное значение или совпадение по определенному критерию

Получить первое не пустое значение в списке

Если вам нужно получить первое непустое значение (текст или число) диапазона в столбце, вы можете использовать табличную формулу на основе функций ИНДЕКС, ПОИСК и ЭПУСТА.

В этом примере мы используем данную формулу:

Нам нужна именно первая непустая ячейка, но Excel не предоставляет никаких формул для этого. Мы могли бы использовать БПФ, использующий шаблон *, но это работает только для текста, а не для чисел.

Таким образом, нам нужно строить функциональные возможности для нужных нам формул. Способ сделать это состоит в использовании функции массива, которая «тестирует» ячейки и возвращает массив истина/ложь значения, которые мы можем сопрягать с ПОИСКПОЗ.

Когда EPUSO оценивает ячейки в диапазоне B3: B11, он возвращает массив, выглядящий следующим образом:

Каждый FALSE представляет непустую ячейку в диапазоне.

Затем SEARCH ищет FALSE в массиве и возвращает позицию первого найденного совпадения, в данном случае 2. На этом этапе формула в примере выглядит следующим образом

Наконец, функция INDEX выводит значение в позиции 2 массива, в данном случае 10.

Получить первое текстовое значение в списке

Когда вам нужно получить первое текстовое значение в диапазоне значений (первая строка), вы можете использовать функцию FFT для установки точного совпадения или использовать звездочку для поиска подстановочного знака.

Получить первое текстовое значение в списке

В данном примере формула в D7 имеет вид:

Каждому текстовому значению соответствует звездочка (*).

Получить первое текстовое значение с ГПР

Получить первое текстовое значение с ГПР

Чтобы найти и извлечь первое текстовое значение в наборе столбцов, можно использовать функцию GPR с групповым символом. В примере формула в F5 выглядит следующим образом:

Значение поиска является «*», групповым символом, который соответствует одному или более текстовому значению.

Получить позицию последнего совпадения

Для получения позиции последнего совпадения (т.е. последнего вхождения) из значения поиска можно использовать формулу, основанную на функциях IF, PIL, INDEX, SEARCH и MAX.

Получить позицию последнего совпадения

В примере формула находится в формате G6:

Читайте так же:
Как вернуть предыдущий рабочий или рабочий день в Excel?

Используя эту формулу, мы строим список номеров строк, соответствующих заданному диапазону, сопоставляем их по значению, а затем используем функцию MAX для получения наибольшего количества строк, которые соответствуют последнему значению.

Получить последнее совпадение содержимого ячейки

= ПРОСМОТР( 2 ; 1 / ПОИСК ( вещи ; А1 ); вещи )

Используя формулу на основе SURVEY и SEARCH, вы можете проверить ячейку на наличие нескольких вещей и вернуть последнее найденное в списке совпадение. Если найдено более одного совпадения, формула вернет последнее совпадение из списка «вещей».

Получить последнее совпадение содержимого ячейки

В примере паттерн находится в тональности C5:

Получить n-е совпадение

= НАИМЕНЬШИЙ( ЕСЛИ( логический тест; СТРОКА( список ) — МИН( СТРОКА( список )) + 1 ); n )

Для того чтобы получить позицию n-го совпадения (например, второе значение совпадения с заданным, третье значение совпадения и т.д.), можно воспользоваться формулой, основанной на функции ДРУГОЙ.

= НАИМЕНЬШИЙ( ЕСЛИ( список = E5 ; СТРОКА( список ) — МИН( СТРОКА( список )) + 1 ); F5 )

Эта формула возвращает позицию второго появления «красных» в списке.

Сутью этой формулы является функция НАИМЕНЬШИЙ, которая просто возвращает n-е наименьшее значение в списке значений, которое соответствует номеру строки. Номера строк были «отфильтрованы» функцией ЕСЛИ, которая применяет логику для совпадения.

Получить n-ое совпадение с ИНДЕКС/ПОИСКПОЗ

Получить n-ое совпадение с ИНДЕКС/ПОИСКПОЗ

Для получения n-го совпадения с помощью INDEX и SEARCH можно использовать формулу массива с функциями IF и LARGE для поиска номера строки совпадения.

Получить n-ое совпадение с ВПР

N-ое совпадение с TPP можно получить, добавив в таблицу столбец с уникальным идентификатором, который включает в себя счетчик.

Получить n-ое совпадение с ВПР

Эта формула зависит от вспомогательного столбца, который добавляется в качестве первого столбца таблицы исходных данных. Вспомогательный столбец содержит формулу, которая строит уникальное значение поиска на основе существующего идентификатора и счетчика. Счетчик подсчитывает, сколько раз уникальный идентификатор появляется в таблице данных.

В примере формула в ячейке J6 вспомогательной колонки имеет вид

Если ячейка содержит одну из многих вещей

Чтобы проверить ячейку по нескольким критериям и вернуть пользовательский результат для первого найденного совпадения, можно использовать формулу ИНДЕКС/ПОИСК на основе функции поиска

Эта формула использует два названных диапазона: E5: E8 называется «вещи» и F5: F8 называется «Результаты». Убедитесь, что вы используете диапазоны имен с одинаковыми именами (на основе ваших данных). Если вы не хотите использовать именованные диапазоны, используйте абсолютные ссылки вместо этого.

Поиск первой ошибки

Если вам нужно найти первую ошибку в диапазоне ячеек, вы можете использовать табличную формулу на основе функций FIND и ERROR.

Поиск первой ошибки

В представленном примере формула

Если подтвержденная ошибка найдена, функция возвращает TRUE, в противном случае она возвращает FALSE.

Читайте так же:
Как быстро перейти к ячейке с текущей датой в Excel?

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

Обратите внимание, что шестое значение (которое соответствует шестой ячейке диапазона) истинно, потому что ячейка B9 содержит #H/A.

Поиск следующего наибольшего значения

(данные; ПОИСК ( поиск ; значения ) + 1).

Поиск следующего наибольшего значения

Для того, чтобы найти «следующее наибольшее» значение в справочной таблице, можно использовать формулу, основанную на ИНДЕКС и ПОИСКПОЗ. В примере формула в F6 является:

Несколько совпадений в списке, разделенных запятой

Для поиска и извлечения нескольких совпадений, разделенных запятыми (в одной ячейке), можно использовать функцию ЕСЛИ с функцией КОМБИНАЦИЯ.

Эта формула использует «имя» — именованный диапазон (B5: B11) и «группа» — (C5: C11).

Частичное совпадение чисел с шаблоном

Если вы хотите выполнить частичное совпадение (подстроку) чисел, вы можете использовать формулу массива, основанную на SEARCH и TEXT.

Частичное совпадение чисел с шаблоном

Excel поддерживает символы подстановки «*» и «?». Тем не менее, если вы используете специальные символы с номером, вы будете преобразовывать числовое значение в текстовое значение. Другими словами, «*» & 99 & «*» = «* 99 *» (текстовая строка).

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

Решение

Можно преобразовать числа в диапазоне поиска в текстовые значения и затем выполнить обычный поиск с помощью SEARCH, UPP и т. д.

Другое решение

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

Частичное совпадение с ВПР

Если вы хотите получить информацию из таблицы на основе частичного совпадения, вы можете сделать это с помощью БПФ в режиме точного совпадения и групповых символов.

Частичное совпадение с ВПР

В данном примере формула для IAP выглядит следующим образом:

В этой формуле значение относится к именованному диапазону в H2, а данные — к именованному диапазону в B3: E102. Если в формулу не включены именованные диапазоны, ее можно записать следующим образом:

Положение первого частичного совпадения

Чтобы получить позицию первого частичного совпадения (то есть ячейки, содержащей искомый текст), можно использовать функцию ПОИСК со специальными символами.

Положение первого частичного совпадения

В данном примере формула находится в E7:

Функция ПОИСКПОЗ возвращает позицию или «индекс» в первом совпадении на основании значения поиска в диапазоне.

ПОИСКПОЗ поддерживает подстановочное согласование со звездочкой «*» (один или несколько символов) или знаком вопроса «?» (один символ), но только тогда, когда третий аргумент, тип_сопоставления, установлен в ЛОЖЬ или ноль.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector