Как найти самую длинную строку в excel

Обновлено: 07.07.2024

На листе, если вы хотите узнать, какая ячейка содержит самые длинные или самые короткие символы, а затем извлечь ее из столбца, как вы могли бы это сделать? Обычно вы можете посчитать количество текстовых строк в списке одну за другой и сравнить их, чтобы получить результат. Но здесь я могу рассказать о простой формуле, которая поможет вам найти самый длинный или самый короткий текст по мере необходимости.

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Найдите самые длинные или самые короткие текстовые строки из столбца с формулой массива

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

1. Рядом со списком данных в пустой ячейке введите следующую формулу:

=INDEX(A2:A11,MATCH(MAX(LEN(A2:A11)),LEN(A2:A11),0)) , см. снимок экрана:

Чаевые: В приведенной выше формуле A2: A11 указывает диапазон данных, который вы хотите использовать, вы можете изменить его по своему усмотрению.

2, Затем нажмите Shift + Ctrl + Enter вместе, и была извлечена самая длинная текстовая строка. Смотрите скриншот:

Внимание: Если вам нужно получить самую короткую текстовую строку, примените эту формулу массива:

=INDEX(A2:A11,MATCH(MIN(LEN(A2:A11)),LEN(A2:A11),0)) , и не забудьте нажать Shift + Ctrl + Enter одновременно.

Здесь я представляю метод определения максимальной длины и относительного значения в столбце Excel.

Найдите максимальную длину в столбце

Вот формула, с помощью которой можно быстро найти максимальную длину столбца.

Выберите пустую ячейку, которая будет выводить результат, введите эту формулу = МАКС (ДЛИН (A1: A63)) (A1: A63 - это диапазон, который вы используете, если хотите использовать столбец, измените его на A: A) и нажмите Shift + Ctrl + Enter ключи вместе. Смотрите скриншот:

Найдите максимальную длину и относительное значение в столбце

Если вы хотите найти максимальную длину и относительное значение, сделайте следующее:

1. Тип = LEN (A1) в B1, чтобы подсчитать общее количество символов в ячейке A1, а затем перетащите дескриптор автозаполнения вниз, чтобы получить количество символов в каждой ячейке. Смотрите скриншот:

2. Не снимая выделения с ячеек формулы, нажмите Kutools > Выберите > Выберите ячейки с максимальным и минимальным значением. Смотрите скриншот:

3. в Выберите ячейки с максимальным и минимальным значением диалоговое окно, выберите Только ячейки формулы в раскрывающемся списке Искать в и отметьте Максимальное значение, Ячейка и Все ячейки параметры. Смотрите скриншот:

4. Нажмите Ok, и была выбрана максимальная длина, и нажмите < or > Клавиша выбора относительного значения.

максимальная длина документа в столбце 5

максимальная длина документа в столбце 6

Наконечник. Если вы хотите получить бесплатную пробную версию Выберите ячейки с максимальным и минимальным значением Функция, пожалуйста, перейдите на бесплатную пробную версию Kutools for Excel сначала, а затем перейдите к применению операции в соответствии с вышеуказанными шагами.

Чтобы найти самую длинную или самую короткую текстовую строку в столбце или строке в Excel, вы можете использовать ИНДЕКС, МАТЧ, LEN и MAX или MIN работают вместе.

Как найти самую длинную или самую короткую строку в столбце или строке в Excel?

Чтобы быстро найти самое длинное или самое короткое имя перечисленные в указанном выше диапазоне имен, формула ИНДЕКС, ПОИСКПОЗ и ДЛСТР, которая включает функцию МАКС или МИН, поможет вам в этом: функция МАКС или МИН будет извлекать наибольшее или наименьшее значение в диапазоне длин строк, предоставляемых ДЛСТР. функция. Затем ПОИСКПОЗ найдет позицию наибольшего или наименьшего значения, а ИНДЕКС получит значение в соответствующей позиции.

Общий синтаксис

Find longest string: =INDEX( range ,MATCH(MAX(LEN( range )),LEN( range ),0))

Find shortest string: =INDEX( range ,MATCH(MIN(LEN( range )),LEN( range ),0))

√ Примечание. Это формулы массива, которые необходимо вводить с помощью Ctrl + Shift + Enter.

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

Чтобы найти самое длинное или самое короткое имя, скопируйте или введите приведенные ниже формулы в ячейку F5 или F6 и нажмите Ctrl + Shift + Enter чтобы получить результат:

Найдите самое длинное имя (ячейка H5):
= ИНДЕКС ( B5: B11 , ПОИСКПОЗ (МАКС. (ДЛИН ( B5: B11 )), LEN ( B5: B11 ), 0))

Найдите самое короткое имя (ячейка H6):
= ИНДЕКС ( B5: B11 , ПОИСКПОЗ (МИН (ДЛИН ( B5: B11 )), LEN ( B5: B11 ), 0))

Пояснение формулы

Здесь мы используем формулу ниже в качестве примера:

=INDEX( B5:B11 , MATCH( MAX( LEN(B5:B11) ) , LEN(B5:B11) ,0) )

LEN (B5: B11) : Функция LEN возвращает массив длин каждого имени в B5: B11 как это: .
МАКСИМУМ( LEN (B5: B11) ) = МАКСИМУМ( ) : Затем функция MAX возвращает наибольшее значение массива. , Которая является 8.
СООТВЕТСТВИЕ( МАКСИМУМ( LEN (B5: B11) ) , LEN (B5: B11) , 0) = СООТВЕТСТВИЕ( 8 , , 0) : Освободи Себя match_type 0 заставляет функцию ПОИСКПОЗ найти позицию точного числа 8 в массиве . Итак, он возвращается 6 так как номер находится на 6-я позиция.
ПОКАЗАТЕЛЬ( B5: B11 , СООТВЕТСТВИЕ( МАКС (ДЛИНА (B5: B11)) , LEN (B5: B11) , 0)) = ИНДЕКС ( B5: B11 , 6): Функция ИНДЕКС возвращает 6th значение в диапазоне имен B5: B11, Которая является Саманта.

Связанные функции

Функция ИНДЕКС Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.

Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.

Функция LEN возвращает количество символов в текстовой строке.

Связанные формулы

В этом руководстве мы поговорим о том, как искать самую длинную текстовую строку в столбце или строке с критериями в Excel с помощью функций ИНДЕКС, ПОИСКПОЗ, ДЛСТР и МАКС.

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

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

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

Пусть в диапазоне 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, как и имен в Исходном списке;
функция ЕСЛИ() возвращает массив номеров строк, в которых содержатся значения с максимальной длиной строкой 3 :9999:9999:9999:9999: 8 :9999>. Остальные элементы массива равны 9999 (требуется для дальнейшей сортировки);
функция НАИБОЛЬШИЙ() сортирует полученный выше массив. Формула СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список))) возвращает массив последовательных чисел от 1 до 9 (9 – количество значений в исходном массиве);
функция ИНДЕКС() возвращает значения с максимальной длиной строки из столбца А .

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

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

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

Пусть имеется таблица с ячейками заполненными текстовыми значениями.

Автоподбор ширины столбца

Как видно из рисунка выше, текстовые значения в ячейках А2 и А3 отображаются не полностью, т.к. мешает текст в столбце B . Нам необходимо, чтобы содержимое всех ячеек в столбце A отображалось полностью. Для этого необходимо сделать ширину столбца A достаточной для отображения самого длинного текста в столбце. Делается это элементарно: подводим указатель мыши к разделу столбцов А и B (на сером заголовке столбцов), курсор примет следующий вид:

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

Если нужно выровнять ширину по содержимому у сразу нескольких столбцов, то делаем следующее:

выделяем необходимые столбцы (за их серые заголовки);
подводим курсор к любому разделу выделенных столбцов и дважды кликаем.

Выберите столбец или столбцы, ширину которых нужно изменить;
На вкладке Главная в группе Ячейки выберите команду Формат ;
В группе Размер ячейки выберите пункт Автоподбор ширины столбца .

Автоподбор высоты строки

Если ячейки содержат значения с очень большой длиной строки (например, если длина текстовой строки без переносов сравнима с шириной видимой части листа), то ширина столбца может стать слишком большой, работать с данными будет не удобно. В этом случае нужно выделить ячейки и включить опцию Переносить по словам через Формат ячеек (или через меню Главная/ Выравнивание/ Перенос текста ).

Ширина столбца останется прежней, зато высота строки автоматически увеличится, чтобы полностью отобразить значение ячейки.

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

После этого текстовое значение будет отображено в ячейке полностью:

Реальный пример

Теперь применим рассмотренный метод в реальной ситуации – отредактируем таблицу, созданную путем копирования данных из MS ACCESS. Скопируем через Буфер обмена какую-нибудь таблицу из MS ACCESS на лист EXCEL.

Обратите внимание, что у ячеек, в которые мы вставили значения из Буфера обмена , включена опция Переносить по словам, хотя, по умолчанию, она отключена (EXCEL сам включил ее при вставке). Кроме того, EXCEL не стал изменять ширину столбцов, установленную по умолчанию, а была изменена только высота строк, чтобы полностью отобразить все значения. Такое форматирование таблицы не всегда устраивает пользователя. Используя вставленную таблицу, решим 2 задачи.

Задача1. Предположим, нам нужно, чтобы все значения отображались в таблице в одну строку.

выделите вставленные значения (для этого выделите любую ячейку таблицы и нажмите CTRL+A (выделится текущая область ), если таблица не содержит пустых строк и столбцов, то будут выделены все вставленные значения);
выключите опцию Переносить по словам (через меню Главная/ Выравнивание/ Перенос текста );
высота ячеек будет уменьшена так, чтобы отображать только одну строку, как следствие часть значений станет невидна;

Задача решена: содержимое всех ячеек полностью отображается.

Задача2. Теперь предположим, что нам нужно, чтобы все столбцы имели определенную, заданную пользователем, ширину, а значения полностью бы отображались в ячейке. При этом высота строки должна быть минимальной.

выделите все строки, содержащие вставленные значения, или все ячейки листа (все ячейки листа можно выделить нажав кнопку Выделить все в левом верхнем углу листа или дважды нажав CTRL+A );
подведите указатель мыши к разделу любых двух заполненных строк (на сером заголовке столбцов) и дважды кликните.

Задача решена: содержимое всех ячеек полностью отображается.

Случай с объединенными ячейками

В случае, если две или несколько ячеек объединены в одну, то рассмотренный подход не сработает. Можно ли осуществить автоподбор высоты строки в случае объединенных ячеек? Об этом читайте в одноименной статье Автоподбор высоты строки по содержимому при объединении ячеек .

Читайте также: