Найти самую длинную текстовую строку с критериями
В этом руководстве мы расскажем, как найти самую длинную текстовую строку в столбце или строке с определенными критериями в Excel с помощью функций INDEX, MATCH, LEN и MAX.
Как найти самую длинную тестовую строку с критериями в Excel?
Чтобы быстро найти человека из Америки с самым длинным именем в указанном диапазоне имен, формула, объединяющая функции INDEX, MATCH, LEN и MAX, поможет вам следующим образом: Функция MAX найдет наибольшее значение в диапазоне длин строк, предоставленных функцией LEN. Затем MATCH определит позицию первого наибольшего значения в диапазоне, а INDEX вернет значение на соответствующей позиции.
Общий синтаксис
=INDEX(return_range,MATCH(MAX(LEN(return_range)*(criteria_range=criteria_value)),LEN(return_range)*(criteria_range=criteria_value),0))
√ Примечание: Это формула массива, для которой требуется ввод с помощью Ctrl + Shift + Enter.
- return_range: Диапазон, из которого вы хотите, чтобы комбинированная формула возвращала самое длинное имя. Здесь имеется в виду диапазон имен.
- criteria_range: Диапазон, где перечислены критерии. Здесь имеется в виду диапазон стран.
- criteria_value: Критерий, который вы задаете.
Чтобы найти человека из Америки с самым длинным именем, скопируйте или введите формулы ниже в ячейку F6 и нажмите Ctrl + Shift + Enter, чтобы получить результат:
=INDEX(B5:B11,MATCH(MAX(LEN(B5:B11)*(C5:C11="America")),LEN(B5:B11)*(C5:C11="America"),0))
Или используйте ссылку на ячейку, чтобы сделать формулу динамической:
=INDEX(B5:B11,MATCH(MAX(LEN(B5:B11)*(C5:C11=F5)),LEN(B5:B11)*(C5:C11=F5),0))
Объяснение формулы
=INDEX(B5:B11,MATCH(MAX(LEN(B5:B11)*(C5:C11=F5)),LEN(B5:B11)*(C5:C11=F5),0))
- LEN(B5:B11)*(C5:C11=F5): Функция LEN возвращает массив длин каждого имени в B5:B11 следующим образом: {5;5;6;3;4;8;5}; Часть (C5:C11=F5) проверяет каждое значение в диапазоне C5:C11 на равенство значению в F5 и создает массив следующего вида: {FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}, который преобразуется в {0;0;1;0;1;0;1}, так как будет умножаться. И вот что мы получим после умножения: {0;0;6;0;4;0;5}.
- MAX(MAX(LEN(B5:B11)*(C5:C11=F5))) = MAX(MAX({0;0;6;0;4;0;5})): Функция MAX затем возвращает наибольшее значение из массива {0;0;6;0;4;0;5}, которое равно 6.
- MATCH(MATCH(MAX(LEN(B5:B11)*(C5:C11=F5)LEN(B5:B11)*(C5:C11=F5)),,LEN(B5:B11)*(C5:C11=F5)LEN(B5:B11)*(C5:C11=F5),0),0) = MATCH(MATCH(6,,{0;0;6;0;4;0;5},0),0): Тип совпадения 0 заставляет функцию MATCH найти позицию первого точного числа 6 в массиве {0;0;6;0;4;0;5}. Таким образом, она возвращает 3, поскольку число находится на 3-й позиции.
- INDEX(B5:B11B5:B11,,,MATCH(MAX(LEN(B5:B11)*(C5:C11=F5)LEN(B5:B11)*(C5:C11=F5))),,,LEN(B5:B11)*(C5:C11=F5)LEN(B5:B11)*(C5:C11=F5),0))) = INDEX(B5:B11B5:B11,,,3): Функция INDEX возвращает третье значение в диапазоне имен B5:B11, которое является Oliver.
Связанные функции
Функция Excel INDEX возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Функция Excel MATCH ищет определенное значение в диапазоне ячеек и возвращает относительную позицию этого значения.
Функция LEN возвращает количество символов в текстовой строке.
Связанные формулы
Найти самую длинную или короткую текстовую строку в столбце или строке
Чтобы найти самую длинную или короткую текстовую строку в столбце или строке в Excel, можно использовать функции INDEX, MATCH, LEN и MAX или MIN вместе.
Приблизительное совпадение с INDEX и MATCH
Бывают случаи, когда нам нужно найти приблизительные совпадения в Excel для оценки производительности сотрудников, выставления оценок студентам, расчета почтовых расходов на основе веса и т.д. В этом руководстве мы расскажем, как использовать функции INDEX и MATCH для получения нужных результатов.
Поиск ближайшего совпадающего значения по нескольким критериям
В некоторых случаях может потребоваться поиск ближайшего или приблизительного совпадающего значения на основе более чем одного критерия. С помощью комбинации функций INDEX, MATCH и IF вы можете быстро это сделать в Excel.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
Kutools для Excel имеет более 300 функций, гарантируя, что нужный вам инструмент находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Один щелчок мыши, чтобы переключаться между десятками открытых документов!
- Сократите сотни кликов мышью ежедневно, попрощайтесь с болью в руке.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.