Поиск нескольких значений с максимальной длиной строки в EXCEL

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

Пусть в диапазоне A4:A12 имеется список имен.

Задача

Найти самое длинное имя (значение с максимальной длиной строки) (см. файл примера ). Так как имен с одинаковыми длинами может быть несколько, например Ярослава и Владимир имеют по 8 букв, то и имен с максимальной длиной может быть также несколько. Выведем их все в отдельный диапазон.

Решение

Создадим для удобства Динамический диапазон Список . Это нам позволит добавлять при необходимости в Исходный список новые имена. Новые имена будут автоматически учтены при работе нижеследующих формул. Формула для создания Динамического диапазона : =СМЕЩ(лист1!$A$7;;;СЧЁТЗ(лист1!$A$7:$A$19))

Теперь найдем максимальную длину строки с использованием формулы массива =МАКС(ДЛСТР(Список)) . В нашем случае это 8 (имена Ярослава и Владимир имеют длину по 8 букв). Формулу разместим в ячейке D7 .

Также найдем количество значений с максимальной длины строки =СУММПРОИЗВ((ДЛСТР(Список)=D7)*1) . В нашем случае это 2 (имена Ярослава и Владимир).

Наконец выведем значения, имеющие максимальную длину строки, в отдельный диапазон. Для этого используем формулу массива : =ИНДЕКС(Список; НАИМЕНЬШИЙ(ЕСЛИ(ДЛСТР(Список)=$D$7;СТРОКА(Список)-СТРОКА($A$6);9999); СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))

Разберем подробнее:

• часть формулы ДЛСТР(Список)=$D$7 возвращает массив {ЛОЖЬ: ЛОЖЬ: ИСТИНА : ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА : ЛОЖЬ}. В этом можно убедиться, выделив в Строке формул соответствующее выражение и нажать клавишу F9 . Значение ИСТИНА соответствует значению с максимальной длиной строки. Как видим, это третье и восьмое значение. Всего значений 9, как и имен в Исходном списке;
• функция ЕСЛИ() возвращает массив номеров строк, в которых содержатся значения с максимальной длиной строкой {9999:9999: 3 :9999:9999:9999:9999: 8 :9999}. Остальные элементы массива равны 9999 (требуется для дальнейшей сортировки);
• функция НАИБОЛЬШИЙ() сортирует полученный выше массив. Формула СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список))) возвращает массив последовательных чисел от 1 до 9 (9 – количество значений в исходном массиве);
• функция ИНДЕКС() возвращает значения с максимальной длиной строки из столбца А .

Формула массива возвращает несколько значений и должна быть введена в диапазон, совпадающий по размерности с исходным списком (хотя и необязательно), например, в A7:A15 . Для этого: выделите диапазон, в Строке формул введите вышеуказанную формулу массива и нажмите CTRL + SHIFT + ENTER .

В ячейках диапазона A7:A15 , не содержащих имен, содержится ошибка #ССЫЛКА! - это результат формулы массива. С помощью Условного форматирования эту ошибку можно скрыть .

С помощью Условного форматирования также выделены имена с максимальной длиной в Исходном диапазоне.

СОВЕТ: Как найти значение с максимальной или минимальной длиной строки и вывести только первое найденное значение рассказано в статье Поиск значения с максимальной или минимальной длиной строки .