Как найти самое частое текстовое значение в excel
Все что требуется — это сводная таблица.
ЕСЛИ(СЧЁТЕСЛИ(A1:A20;МОДА(A1:A20))=СЧЁТЕСЛИ(A1:A20;МОДА(ЕСЛИ(A1:A20=МОДА(A1:A20);"";A1:A20)));МИН(МОДА(A1:A20);МОДА(ЕСЛИ(A1:A20=МОДА(A1:A20);"";A1:A20)));МОДА(A1:A20))
ЕСЛИ(A1:А4=5;…
Почти то же, но проверяется МАССИВ значений, результат проверки условия – массив значений ИСТИНА или ЛОЖЬ.
попробовал сделать, как ты описал в примере. У меня англ.версия..
результат — в ячейке имеем 28.
Результатом выполнения ф-лы ISNUMBER(A1:a4) является #VALUE!
может, надо куда-то вставить фигурные скобки? Помоги пожалуйста.
Поиск ТЕКСТовых значений в EXCEL с выводом их в отдельный список. Часть1. Обычный поиск
history 14 апреля 2013 г.
- Группы статей
- Вывод отобранных значений в отдельный диапазон
- Поиск ТЕКСТовых значений
- Условное форматирование
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в отдельный диапазон удобно использовать формулы массива.
Пусть Исходный список значений (например, перечень инструментов) находится в диапазоне A10:A19 (см. Файл примера ).
Задача
Выведем в отдельный диапазон значения, которые удовлетворяют критерию. Рассмотрим различные варианты поиска.
А. Найти значения, которые содержат критерий
Диапазон может охватить в том числе и незаполненные ячейки перечня. В дальнейшем пользователь может расширить перечень инструментов, указанные ниже формулы автоматически учтут новые значения.
Выведем в отдельный диапазон все значения Исходного списка, в которых содержится текст-критерий (например, слово дрель ). Критерий вводится в ячейку С6 .
Для создания списка, содержащего найденные значения, воспользуемся формулой массива :
=ИНДЕКС(Список; НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($C$6;Список);0)>0;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))
Алгоритм работы формулы следующий (для просмотра промежуточных шагов работы формулы воспользуйтесь клавишей F9 ):
- Функция ПОИСК() , перебирая элементы исходного списка, определяет, содержится ли в нем значение-критерий. Если значение не содержится, то возвращается ошибка #ЗНАЧ! В противном случае возвращается числовое значение, соответствующее номеру начальной позиции вхождения критерия в значение из списка (здесь нам не важен номер позиции, важно, что это число);
- Функция ЕСЛИОШИБКА() используется для подавления ошибки #ЗНАЧ! заменяя ее на число 0;
- Функция ЕСЛИ() заменяет числовые значения, возвращенные функцией ПОИСК() , на номер позиции значения в списке. Если значение =0 (соответствует ошибке #ЗНАЧ!), то возвращается число 30. В принципе, вместо 30 можно указать любое число, которое больше номера последней заполненной позиции Исходного списка (это нужно для правильной сортировки функцией НАИМЕНЬШИЙ() );
- Функция НАИМЕНЬШИЙ() сортирует массив номеров строк по возрастанию;
- Функция ДВССЫЛ() возвращает массив последовательных чисел ;
- Функция ИНДЕКС() возвращает текстовые значения из Исходного списка, из строк, номера которых были получены на предыдущем шаге.
В предельном случае м.б. найдено столько же значений, сколько содержится в исходном списке (когда все значения удовлетворяют критерию). Поэтому формулу массива нужно распространять на диапазон той же размерности, что и исходный список. Вышеуказанная формула массива будет возвращать несколько значений , поэтому перед вводом формулы нужно выделить сразу весь диапазон, т.е. ячейки С10:С19 , ввести формулу в Строке формул и нажать CRTL+SHIFT+ENTER .
Для скрытия ошибок #ССЫЛКА!, возвращаемой формулой массива, к диапазону С10:С19 применено правило Условного форматирования .
Б. Найти значения, которые совпадают с критерием (точное совпадение)
В этом случае будут выведены все значения, которые совпадают с критерием (без учета РЕгиСТра ). Критерий вводится в ячейку E 6 .
Для создания списка, содержащего найденные значения, воспользуемся формулой массива : =ИНДЕКС(Список; НАИМЕНЬШИЙ( ЕСЛИ($E$6=Список;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))
B. Найти значения, которые начинаются с критерия
В этом случае будут выведены все значения, которые начинаются или совпадают с критерием. Критерий вводится в ячейку G 6 .
Для создания списка, содержащего найденные значения, воспользуемся формулой массива : = ИНДЕКС(Список;НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($G$6;Список);0)=1;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))
Г. Найти значения, которые заканчиваются на критерий
В этом случае будут выведены все значения, которые заканчиваются или совпадают с критерием. Критерий вводится в ячейку I 6 .
Для создания списка, содержащего найденные значения, воспользуемся формулой массива : =ИНДЕКС(Список;НАИМЕНЬШИЙ( ЕСЛИ($I$6=ПРАВСИМВ(Список;ДЛСТР($I$6));СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))
СОВЕТ: О поиске текстовых значений с использованием подстановочных знаков читайте в статье Поиск текстовых значений в списках. Часть2. Подстановочные знаки . В статье Выделение ячеек c ТЕКСТом с применением Условного форматирования приведено решение аналогичной задачи с использованием Условного форматирования .