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

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

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

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

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

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

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

• Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
• Более 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 > Клавиша выбора относительного значения.

Наконечник. Если вы хотите получить бесплатную пробную версию Выберите ячейки с максимальным и минимальным значением Функция, пожалуйста, перейдите на бесплатную пробную версию 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 );
• подведите указатель мыши к разделу любых двух заполненных строк (на сером заголовке столбцов) и дважды кликните.

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

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

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

Длинный текст в ячейке Excel: как его скрыть или уместить по высоте. ✔

Здравствуйте.

Не знаю почему, но при работе с Excel у многих не искушенных пользователей возникает проблема с размещением длинного текста: он либо не умещается по ширине (высоте), либо «налезает» на другие ячейки. В обоих случаях смотрится это не очень. 👀

Чтобы уместить текст и корректно его отформатировать — в общем-то, никаких сложных инструментов использовать не нужно: достаточно активировать функцию «переноса по словам» . А далее просто подправить выравнивание и ширину ячейки.

Собственно, ниже в заметке покажу как это достаточно легко можно сделать.

Примечание:

1) скрины в статье из Excel 2019 (в Excel 2010, 2013, 2016 — все действия выполняются аналогичным образом);

2) если у вас «глючит» Excel — можно пользоваться также его аналогами: https://ocomp.info/chem-zamenit-word-i-excel.html

Вариант 1

И так, например в ячейке B3 (см. скрин ниже) у нас расположен длинный текст (одно, два предложения). Наиболее простой способ «убрать» эту строку из вида: поставить курсор на ячейку C3 (следующую после B3) и написать в ней любой символ, подойдет даже пробел .

Обратите внимание на скриншот ниже: справа показано, что строка из ячейки B3 была скрыта, т.к. в C3 появилось три единички.

Написание текста в следующей ячейке

Кстати, чтобы увидеть полный текст, расположенной в B3 — достаточно навести на нее курсор и посмотреть в строку формул.

Чтобы увидеть полный текст.

Вариант 2

Второй способ — это уместить текст в ячейку, расширив при этом высоту ячейки (т.е. ячейка сама станет выше, и текст в ней можно будет прочитать целиком).

Делается это не сложно: сначала нужно выбрать нашу ячейку с текстом (B3), нажать по ней правой кнопкой мышки и выбрать в меню «Формат ячеек» .

Формат ячеек (правый клик по ячейке)

После перейти во вкладку «Выравнивание» и поставить галочку рядом с пунктом «Переносить по словам» .

Переносить по словам

Высота строки с ячейкой станет такой, чтобы в нее вместился весь ваш текст (прим.: Excel сам ее подберет исходя из длины текста) . Далее вам останется только подкорректировать ширину ячейки.

Что получилось в итоге

Вариант 3

Если вы не хотите, чтобы высота строк (ячеек) менялась в большую строну — можно прибегнуть к другому варианту:

• сначала нужно выделить несколько ячеек по высоте (вместе с нашим текстом B3);
• далее использовать инструмент «Объединить ячейки» .

Выделение нескольких ячеек

В результате появится одна большая ячейка, которая займет сразу несколько строк: в нее, разумеется, и впишется наш текст.

Обратите внимание, что для корректного размещения текста в ячейке необходимо использовать инструмент «Перенести текст» (стрелка 2 на скрине ниже).

Текст удачно вписался в одну большую ячейку

Вариант 4

В некоторых случаях, повернув тест на определенный угол, можно удачно вписать его в таблицу. Чтобы сделать это, достаточно в Excel в разделе «Главная» нажать на значок . Пример ниже.

Текст по вертикали

PS

Как еще один вариант: можно вместо текста разместить картинку (скриншот). В некоторых случаях этот способ куда удобнее, чем все вышеприведенные.