Поиск нескольких значений с максимальной длиной строки в MS 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, не содержащих имен, содержится ошибка #ССЫЛКА! - это результат формулы массива. С помощью Условного форматирования эту ошибку можно скрыть.

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

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)

Комментарии

Екатерина (не проверено)

Здравствуйте! Подскажите как вычислить значение: даны две строки с данными, мне необходимо найти какое значение будет в первой строке при максимуме во второй строке!

Creator

Попробуйте воспользоваться решением из статьи Максимальный и Минимальный по условию в MS EXCEL

Яндекс.Метрика