000818.ru

Финансовые и Бух. услуги
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти наиболее распространенное значение (число или текстовую строку) из списка в Excel?

Подстановочные знаки (символы *, ? и

Приветствую вас, дорогие читатели блога TutorExcel. Ru блога.

В начале предлагаю вспомнить определение подстановочных знаков и понять, что же это такое и для каких целей они применяются в Excel. А затем уже разберем применение на конкретных примерах.
Подстановочные знаки — это специальные символы, которые могут принимать вид любого произвольного количества символов, другими словами, являются определенными масками комбинаций символов.
Всего в Excel есть 3 типа подобных знаков:

  • * (звездочка); Обозначает любое произвольное количество символов. Например, поиск по фразе «*ник» найдет слова типа «понедельник», «всадник», «источник» и т.д.
  • ? (вопросительный знак); Обозначает один произвольный символ. К примеру, поиск по фразе «ст?л» найдет «стол», «стул» и т.д.

(тильда), за которым следует *, ? или

. Обозначает конкретный символ *, ? или

. Например, поиск по фразе «хор*» найдет все фразы начинающиеся на «хор» («хоровод», «хорошо» и т.д.). Поэтому для точного поиска «хор*» нужно использовать символ «

» и искать по фразе «хор

Использование подстановочных знаков может быть полезно для фильтрации данных, сравнения текста, поиска и замены текстовых значений. Давайте рассмотрим каждый из основных видов использования более подробно.

Автозаполнение в Excel из списка данных

Конечно, помимо дней и месяцев, могут понадобиться и другие списки. Например, часто необходимо ввести список городов, в которых расположены сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Сначала необходимо создать и сохранить (в правильном порядке) полный список имен. Перейдите в меню Файл — Параметры — Дополнительно — Общие — Редактировать списки.

Изменить списки для автозаполнения в Excel

В следующем окне отображаются уже созданные списки.

Диалоговое окно для изменения списков в Excel

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

Добавление нового списка

Нажмите OK. Список создан, вы можете использовать его для автозаполнения.

Помимо текстовых списков, чаще всего создаются последовательности чисел и дат. Один из вариантов был упомянут в начале статьи, но он примитивен. Есть и более интересные техники. Сначала необходимо выбрать одно или несколько первых значений в серии, а также диапазон (вправо или вниз), в котором будет расширяться последовательность значений. Затем откройте диалог прогрессии: Start — Fill — Progression.

Команда Прогрессия в Excel

Настройки диалогового окна Прогрессия

Для определения направления последовательности используйте переключатель в левой части окна: вниз (по строкам) или вправо (по столбцам).

Необходимый тип выбирается в центре:

  • Арифметическая прогрессия — каждое последующее значение умножается на число, указанное в поле Step
  • Geometric progression — каждое последующее значение умножается на число, указанное в поле Step
  • Dates — создает последовательность дат. При выборе этого типа переключатели справа становятся активными, где можно выбрать тип устройства. Имеется 4 варианта:
      • День — список календарных дат (по шагам)
      • Рабочий день — порядок рабочих дней (выходные пропускаются)
      • Месяц — изменяются только месяцы (число фиксируется как в первой ячейке)
      • Год — изменяются только года
      • Автозаполнение — эта команда эквивалентна перемещению левой кнопки мыши. Другими словами, Excel сам определяет, продолжить ли последовательность чисел или расширить список. Если вы предварительно заполните две ячейки значениями 2 и 4, 6, 8 и т.д. появятся в других выделенных ячейках. Если вы предварительно заполните больше ячеек, Excel рассчитает аппроксимацию линейной регрессии, т.е. предсказание прямолинейного тренда (приятная особенность — подробнее см. ниже).

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

      Предельное значение в прогрессии

      В результате вы получите полный столбец от 2 до 1000. Таким же образом можно создать последовательность рабочих дней для будущего года (значение отсечки должно быть последней датой, например, 31.12.2016). Очень полезно иметь возможность заполнить столбец (или строку) последним значением, так как это избавляет вас от лишней работы при извлечении. На этом параметры автозаполнения заканчиваются. Давайте двигаться дальше.

      Exceltip

      Google показывает, что ВПР гораздо популярнее функции ИНДЕКС при поиске функций подстановки. Это вполне объяснимо, поскольку для того, чтобы придать функции ИНДЕКС ту же функциональность, что и ВПР, необходимо использовать другую формулу для повторения функциональности ВПР — SEARCHPOPE. Что касается меня, то для меня всегда было сложной задачей пытаться освоить две новые функции одновременно. Эти программы позволяют создавать электронные таблицы более легко и гибко. Но сначала о главном.

      Функция ВПР()

      Формула ВПР

      Допустим, у вас есть таблица с данными о сотрудниках. В первом столбце указан номер табеля сотрудника; в других столбцах представлена другая информация (например, имя, отдел). Используйте функцию FFT для доступа к информации о сотруднике на основе номера табеля. В синтаксисе формулы это =ВРП(искать_значение; таблица; номер_столбца; [интервал_просмотра]). Excel получает указание найти строку, в которой первая ячейка совпадает со значением_поиска, и вернуть значение ячейки с тем же порядковым номером, что и номер_столбца.

      формула ВПР не работает

      Однако возможно, что у вас есть имя сотрудника и вам нужно вернуть номер его табеля учета рабочего времени. В ячейке A10 указывается имя сотрудника, а в ячейке B10 — номер табеля.

      Если ключевое поле находится справа от извлекаемых данных, VPR бесполезен. Если бы, конечно, можно было установить номер столбца в -1, то проблемы бы не было. Обычным решением является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнение номеров табелей учета рабочего времени с помощью VPR, сохранение их как значений и удаление временного столбца A.

      Функция ИНДЕКС()

      Чтобы решить нашу задачу за один шаг, необходимо использовать формулы INDEX и SEARCH. Сложность этого подхода заключается в том, что он заставляет вас применять две функции, которые вы, возможно, никогда не применяли раньше. Для простоты понимания мы будем решать эту задачу в два этапа.

      Начнем с функции INDEX. Это кошмарное название. Когда кто-то говорит «индекс», у меня в голове не укладывается, что она делает. Более того, она требует три аргумента: =INDEX(array; number_row; [column_number]).

      По сути, Excel проходит через массив данных и возвращает точку пересечения указанной строки и столбца. Как бы просто это ни звучало. Таким образом, формула =INDEX($A$2:$C$6;4;2) вернет значение, найденное в ячейке B5.

      формула ИНДЕКС

      В нашей задаче формула должна выглядеть следующим образом, чтобы вернуть номер табеля сотрудника: =INDEX($A$2:$A$6;?;1). Это кажется бессмыслицей, но если мы заменим знак вопроса формулой SEARCH, то получим решение.

      Функция ПОИСКПОЗ()

      Синтаксис функции следующий: =Search_value; browsable_array; [match_type]).

      Он сообщает Excel: «Найдите значение_поиска в наборе данных и верните номер строки таблицы, в которой это значение встречается». Итак, чтобы найти строку, в которой имя сотрудника находится в ячейке A10, введите формулу =Найти_значение(A10; $B$2:$B$6; 0). Если ячейка A10 содержит имя «Colin Farrell», FIND возвращает 5-ю строку таблицы B2:B6.

      Описание формулы ПОИСКПОЗ

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

      формула индекс и поискпоз

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

      Поисковая функция в Excel

      Функция поиска в Microsoft Excel позволяет находить нужные текстовые или числовые значения с помощью окна «Найти и заменить». В программе также есть возможность расширенного поиска данных.

      Способ 1: простой поиск

      Простой поиск в Excel позволяет найти все ячейки, содержащие символы, которые вы ввели в поле поиска (буквы, цифры, слова и т.д.). без учета регистра.

      1. Нажмите кнопку «Найти и выделить» на вкладке «Главная» в панели инструментов «Правка» на ленте. В появившемся меню выберите «Найти…». Вместо этого вы можете просто набрать на клавиатуре Ctrl+F.

      Переход к поиску в Microsoft Excel

      Обычный поиск в Microsoft Excel

      Результаты ищутся и выводятся строка за строкой. Сначала перебираются все ячейки в первом ряду. Если данных, соответствующих условию, не найдено, программа начинает поиск во втором ряду, и так далее, пока не будет найден удовлетворительный результат.

      Символы поиска не обязательно должны быть независимыми элементами. Так, если запрос будет задан выражением «справа», то в выводе будут представлены все ячейки, содержащие этот последовательный набор символов даже в пределах слова. Здесь слово «справа» считается релевантным запросу. При задании в поисковой системе числа «1», вы получите ячейки, содержащие, например, число «514».

      Чтобы перейти к следующему результату, снова нажмите «Find Next» (Найти далее).

      Результат обычного поиска в Microsoft Excel

      Найти всё в Microsoft Excel

      Способ 2: поиск по указанному интервалу ячеек

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

        Выделяем область ячеек, в которой хотим произвести поиск.

      Выделение интервала в Microsoft Excel

      Поиск по интервалу в Microsoft Excel

      Способ 3: Расширенный поиск

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

      Кроме того, вывод может включать не только содержимое конкретной ячейки, но и ее адрес, если он ссылается на другой элемент. Эта формула представляет собой сумму ячеек A4 и C3 в ячейке E2. В ячейке E2 появляется сумма 10. При установке номера поиска на «4» мы все равно находим ту же ячейку E2. Как такое могло произойти? В частности, ячейка E2 содержит формулу, указывающую на ячейку A4, которая содержит нужную цифру 4.

      Результат поиска в Microsoft Excel

      Но как отфильтровать эти и другие неуместные результаты поиска? Для этого и предназначена функция расширенного поиска в Excel.

        После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».

      Переход в параметры поиска в Microsoft Excel

      Параметры поиска по умолчанию в Microsoft Excel

      Если галочки установлены возле соответствующих элементов, то в этом случае при формировании результата будет учитываться регистр и точное совпадение. Использование строчной буквы приводит к тому, что в результаты поиска не включаются ячейки, содержащие вариант слова с заглавной буквы, как это было бы по умолчанию. Более того, если включена функция «Целые ячейки», в результатах поиска будут перечислены все ячейки, содержащие точное название. Задав поисковый запрос «Николаев», вы не увидите ячеек, содержащих текст «Николаев А. Д.».

      Настройки поиска в Microsoft Excel

      По умолчанию поиск осуществляется только в активном листе Excel. Однако если установить для параметра «Поиск» значение «В книге», поиск будет производиться по всем рабочим листам в открытом файле.

      Область поиска в Microsoft Excel

      Направление поиска можно изменить в опции «Обзор». По умолчанию, как упоминалось выше, поиск выполняется построчно. Установив переключатель в положение «По столбцам», вы можете определить порядок, в котором формируются результаты вывода, начиная с первого столбца.

      Содержимое поиска в Microsoft Excel

      Колонка «Область поиска» определяет конкретные элементы, по которым ведется поиск. По умолчанию это формулы, т.е. данные, которые появляются в строке формул при нажатии на ячейку. Это может быть слово, число или ссылка на ячейку. Таким образом, при выполнении поиска программа видит только ссылку, а не результат. Этот эффект уже обсуждался выше. Для поиска по результатам, по данным, отображаемым в ячейке, а не в строке формул, переместите переключатель из положения «Формулы» в положение «Значения». Также возможен поиск по заметкам. В этом случае переведите переключатель в положение «Примечания».

      Область поиска в программе Microsoft Excel

      Нажав на кнопку «Формат», вы можете еще больше уточнить свой поиск.

      Переход к формату поиска в Microsoft Excel

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

      Формат поиска в Microsoft Excel

      Вы можете использовать формат конкретной ячейки, нажав на ссылку «Использовать этот формат ячейки» в нижней части окна. кнопка.

      Переход к выбору ячейки для установки формата в Microsoft Excel

      После этого появится инструмент под названием «Пипетка». Это позволит вам выбрать, какую ячейку форматировать.

      Выбор ячейки для установки формата в Microsoft Excel

      После установки формата поиска нажмите «ОК».

      Установка формата поиска в Microsoft Excel

      Поиск по отдельным словам в Microsoft Excel

      Запуск расширенного поиска в Microsoft Excel

      Как видите, Excel — это довольно простой, но очень функциональный набор инструментов поиска. Чтобы выполнить простой запрос, просто откройте окно поиска, введите свой запрос и нажмите кнопку. Но в то же время вы можете настроить свой собственный поиск с большим количеством различных параметров и расширенных настроек.

      ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

      Помимо этой статьи, на сайте еще 12677 инструкций.
      Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

      Расскажите о статье в социальных сетях.

      ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

      • Главная
      • Все статьи

      Как посчитать строки в которых содержится нужное слово

      В качестве примера рассмотрим статистику по ключевым запросам.

      У меня есть таблица со значениями в первом столбце — поисковый запрос, во втором — количество просмотров страниц в месяц.

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

      1. Выберите область и скопируйте ее, например, в столбец «F» .
      2. Затем уменьшите ширину столбца до одного символа.
      3. Выберите столбец «F» и выберите Меню — Правка — Заливка — Выровнять.

      Теперь необходимо выбрать уникальные слова.

      Применение расширенного фильтра.

      1. Выберите нужную область с заголовком.
      2. Перейдите в Меню — Данные — Фильтр — Расширенный фильтр
      3. Выберите опцию «скопировать результат в другое место».

      Эта формула содержит таблицу. Поэтому завершите ввод с помощью CTRL + SHIFT + ENTER.

      1. Функция ПОИСКПОЗ() ищет вхождение слова в ячейке «D2» в каждой ячейке фиксированного диапазона «$A$2:$A$2001» . Появляется массив значений, равный длине диапазона «$A$2:$A$2001».
      2. Если он найден, функция EOSH() возвращает «false», а функция IF() возвращает 1, иначе ноль. Массив состоит из нулей и единиц.
      3. SUM() суммирует все значения в массиве.

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

      Вы можете подсчитать ячейки, содержащие нужное слово, с помощью инструмента Найти и заменить. Как это сделать, описано в статье «Как найти ячейки, содержащие нужное слово».

      3 комментария:

      Вы не поверите, я три дня пытаюсь понять, как автоматизировать статистику контекстной рекламы vmlink, и ваше предложение просто бомбическое, а именно: =SUM(IF(IF(IF(SEARCH(D2;$A$2:$A$2001));0;1)))))

      Эта формула содержит таблицу. Поэтому завершите ввод с помощью CTRL + SHIFT + ENTER.

      Работает формула так
      Функция НАЙТИ() ищет вхождение слова из ячейки "D2" в каждой из ячеек фиксированного диапазона "$A$2:$A$2001". Появляется массив значений по количеству значений равный длине диапазона "$A$2:$A$2001".
      Если находит, то функция ЕОШ() возвращает значение "ложь", а функция ЕСЛИ() возвращает 1, в противном случае ноль. Массив состоит из нулей и единиц.
      СУММ() суммирует все значения массива.

      Как ни странно, я тоже, как и Юрий Диньков, ломаю голову 3 дня над этим)
      Спасибо, наконец-то нашел, что искал, жаль, что не в первый же день.

      Фильтр

      Вот как осуществлять поиск в Excel с помощью фильтра:

      1. Выберите заполненную ячейку.
      2. Нажмите Пуск — Сортировка — Фильтр.
      3. В верхнем ряду ячеек появляются стрелки. Это выпадающее меню. Откройте его.
      4. Введите свой запрос в текстовое поле и нажмите «OK».
      5. В столбце появляются только ячейки, содержащие поисковый запрос.
      6. Чтобы сбросить результаты, выберите «Выбрать все» в выпадающем меню.
      7. Чтобы деактивировать фильтр, снова щелкните его в сортировке.

      Поиск по фильтру

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

      В Excel используйте встроенные функции поиска. Можно выбрать другие параметры поиска и включить фильтры.

      голоса
      Рейтинг статьи
      Читайте так же:
      Как отключить печать линий сетки в Excel?
Ссылка на основную публикацию
Adblock
detector