Evolcom.ru

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

Как посчитать, как часто слово или значение встречается в Excel?

Excel наиболее часто встречающееся значение

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

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

В такой ситуации задачу придется решать немного по-разному, в зависимости от того, с чем мы имеем дело — с числами или с текстом.

Поиск самых часто встречающихся чисел

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

Наиболее распространенное количество товаров, которые покупают клиенты, — три.

Если существует несколько значений, появляющихся одинаковое максимальное количество раз (несколько режимов), вы можете использовать функцию MODE.MULT для их идентификации. Ее следует вводить как формулу таблицы, т.е. выделить сразу несколько пустых ячеек, чтобы хватило на все модели, затем ввести строку формулы =MODA.NSC(B2:B16) и нажать комбинацию клавиш Ctrl+Shift+Enter.

На выходе мы получим список всех модов в наших данных:

Т.е., судя по нашим данным, часто берут не только по 3, но и по 16 шт. товаров. Обратите внимание, что в наших данных только две моды (3 и 16), поэтому остальные ячейки, выделенные «про запас», будут с ошибкой #Н/Д.

Частотный анализ по диапазонам функцией ЧАСТОТА

Для анализа дробных, а не целых чисел лучше подсчитывать количество единиц, а не их количество, попадающее в определенные диапазоны. Важно знать вес товаров, которые мы покупаем чаще всего, чтобы выбрать тележку и сумки нужного размера для магазина. Другими словами, нам нужно определить, сколько чисел попадает в диапазон 1,5 кг, сколько — в диапазон 5,10 кг и т.д.

Для решения этой проблемы можно использовать функцию FREQUENCY. Для этого заранее подготовьте ячейки с интересующими интервалами (карманами), затем выделите пустой диапазон ячеек (G2:G5), который на одну ячейку больше диапазона карманов (F2:F4), и введите его как формулу массива, нажав в конце Ctrl+Shift+Enter:

Читайте так же:
Как преобразовать дату в порядковый формат даты в Excel?

Частотный анализ сводной таблицей с группировкой

Альтернативный вариант решения задачи: создать сводную таблицу, где поместить вес покупок в область строк, а количество покупателей в область значений, а потом применить группировку — щелкнуть правой кнопкой мыши по значениям весов и выбрать команду Группировать (Group) . В появившемся окне можно задать пределы и шаг группировки:

. и после нажатия кнопки OK отобразится таблица с количеством обращений клиентов в каждой области группировки:

Недостатки этого способа

  • шаг группировки может быть только постоянным, в отличие от функции ЧАСТОТА, где карманы можно задать абсолютно любые
  • сводную таблицу нужно обновлять при изменении исходных данных (щелчком правой кнопки мыши — Обновить), а функция пересчитывается автоматически "на лету"

Поиск самого часто встречающегося текста

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

Самое простое и очевидное решение — добавить столбец с функцией COUNTIF, которая подсчитывает количество вхождений каждого элемента в столбце A:

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

Можно также добавить к исходному списку колонку единиц и построить на ее основе сводную таблицу, подсчитав общее количество единиц для каждого элемента:

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

Мы разберем его по частям:

  • READ(A2:A20;A2:A20) — формула массива, которая поочередно находит количество вхождений каждого элемента в интервал A2:A100 и выводит массив с количеством вхождений, т.е. фактически заменяет лишний столбец
  • MAX — находит наибольшее число в массиве вхождений, т.е.
  • SEARCH — вычисляет номер индекса строки таблицы, в которой MAX нашел наибольшее число
  • INDEX — возвращает содержимое ячейки с числом, найденным SEARCHом

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

Если необходимо найти ячейку с несколькими значениями, в том числе повторяющимися, этот метод может оказаться полезным. Если ячейка содержит 10 значений, проще найти их вручную, но если значений больше 100, лучше сделать это автоматически.

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

Шаг 1: Создайте таблицу с значениями

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

Читайте так же:
Как посчитать все ячейки, кроме определенного значения в Excel?

Этап 2: Добавление специальной формулы

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

А это и есть сама формула:

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

Шаг 3: Окончательный результат

Теперь можно проверить формулу на работоспособность. В нашем примере наиболее часто повторялись «Материнские платы».

Взгляните на функции, составляющие эту формулу:

MAX — возвращает максимальное значение.

S EARCH — ищет указанный элемент в диапазоне ячеек и возвращает относительное положение этого элемента в диапазоне. В нашем случае это диапазон A2:A10.

Count — подсчитывает, какие ячейки соответствуют заданному условию.

Функция NDEX принимает все полученные значения в качестве аргументов и возвращает конечный результат в ячейку.

A — это ячейка, в которой находится значение.

2 — это линия, с которой начинается отсчет времени.

10 лет — это уже конец пути.

На этом мы завершаем данный урок. Спасибо за внимание!

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

Поиск наиболее повторяющегося значения в Excel

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

Теперь выполним простой анализ наиболее часто и редко повторяющихся значений таблицы в столбце «Город». Для этого:

  1. Сначала найдите самые распространенные названия городов. Введите следующую формулу в ячейку E2:
  2. Обязательно нажмите комбинацию клавиш CTRL+Shift+Enter после ввода формулы, так как ее необходимо выполнить в матрице.
  3. Для вычисления наиболее редко повторяющегося названия города введите очень похожую формулу:

Ниже приведен результат поиска самых популярных и самых редких городов в регистре заказов:

В случае, если в одной колонке указано более двух городов, то отображается первый из них.

Как работает поиск популярных ценностей с помощью повторения:

Если вы посмотрите на синтаксис формул, то легко увидите, что они отличаются только одним из имен функций: =MAX() и =MIN(). Все остальные аргументы формулы идентичны. Функция =CountingEASE() подсчитывает, сколько раз повторяется название каждого города в диапазоне ячеек C2:C16. Это создает условный массив значений в памяти.

Используя функцию MAX или MIN, можно выбрать наибольшее или наименьшее значение из условного массива. С помощью функции =DISCREATION() пользователю возвращается наибольшее или наименьшее количество повторений в столбце. Мы передадим результат в качестве аргумента функции =ИНДЕКС(), которая возвращает окончательный результат в ячейку.

Читайте так же:
Как перейти в полноэкранный режим в Excel?

Функция ЕСЛИ в Excel

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

Функция IF является одной из логических функций в MS Excel. Он позволяет создавать очень сложные конструкции, основанные на проверке условия и выборе двух или более альтернатив.

Используя функцию ЕСЛИ в Excel, можно строить разветвленные алгоритмы, деревья решений и другие системы и формулы, которые используют вложенность одной функции ЕСЛИ в другую и т.д. В Excel 2010 можно использовать до 64 вложенных функций. Это позволяет построить действительно крутой алгоритм вычислений.

В общем случае функция IF в Excel имеет следующий синтаксис:

IF(log_expression;значение_если_истина;значение_если_ложь)

Функция возвращает одно из двух значений, в зависимости от значения условия (log_выражение). Если условие равно TRUE, функция IF возвращает значение value_if_true, а если условие равно FALSE, она возвращает значение value_li_false.

Рассмотрим синтаксис этой функции в качестве примера алгоритма расчета суммы с НДС или без НДС.

Log_expression — это может быть любое значение или выражение (формула), которое принимает значение TRUE или FALSE. C5=»с НДС» логически эквивалентно выражению «с НДС». Если ячейка C5 содержит текст «с НДС», логическое выражение принимает значение TRUE, в противном случае оно принимает значение FALSE, например, если ячейка содержит текст «без НДС».

If_true_value — это значение или выражение (формула), которое возвращается, если аргумент «log_expression» имеет значение TRUE. Так, например, если этот аргумент C12*1.18, если значение логического выражения равно TRUE, то вычисление производится по этой формуле.

Если параметр «log_expression» установлен в TRUE, а параметр «value_if_truth» не установлен, возвращается 0 (ноль).

Value_if_false — значение или выражение (формула), возвращаемое, если аргумент «log_expression» равен FALSE. Так, например, если аргументом является C12*1 или просто C12, то если значение логического выражения равно FALSE, то вычисление производится по этой формуле, то есть, как в нашем примере, берется только значение суммы из ячейки C12.

Если аргумент «log_expression» равен FALSE, а аргумент «value_if_ false» опущен (т.е. после аргумента «value_if_truth» нет точки с запятой), возвращается логическое значение FALSE. Если аргумент ‘log_expression’ равен FALSE, а аргумент ‘value_if_ false’ пуст (т.е. после аргумента ‘value_if_truth’ стоит точка с запятой, за которой следует закрывающая скобка), возвращается 0 (ноль).

Читайте так же:
Как очистить стиль форматирования таблицы без потери данных таблицы в Excel?

Давайте рассмотрим некоторые наиболее распространенные случаи использования IF в экономике и финансовом моделировании.

Пример 1. Использование текстовых значений.

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

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

Создайте формулу с помощью функции IF:

= IF(P20>1000; «выше нормы»; «в пределах нормы»).

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

Пример 2. Скрытие значений. Скрытие нулевых значений.

Почти повсеместной ситуацией является возникновение ошибки #DEL/0! при подготовке исходных форматов. Подготовим исходный формат, в котором рассчитаем темп роста одного показателя относительно другого в одной из колонок. Как вы знаете, для этого используется формула A/B*100%, но поскольку у нас еще нет данных, возникает ошибка при делении значений в столбце A на нулевые значения в столбце B.

Для того чтобы этого избежать, мы используем функцию IF.

=ЕСЛИ(В10=0;0;А10/В10) или =ЕСЛИ(В10=0;””;А10/В10)

В первом случае вместо ошибки #DEL/0. будет возвращен 0, а во втором случае это будет просто пустая ячейка. Ячейки, содержащие двойные кавычки, являются пустыми.

Сходимость баланса также можно проверить. В этом случае нужно сравнить сумму активов и сумму пассивов баланса, и если они равны, т.е., например, C85-C160=0, то нужно скрыть нулевое значение, а если есть разница — отобразить это значение.

Пример 3. Многоуровневые, вложенные вычисления.

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

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

Типичным примером является расчет скидки в зависимости от различных уровней объема продаж. Или прибыли, основанные на различных уровнях производства.

Предположим, что у нас имеется следующая матрица дисконтирования

До 100 000 рублей — 0 %.

От 100 001 рубля до 300 000 рублей: 3%.

От 300 001 до 500 000 рублей — 5%.

Более 500 001 — 7%.

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

Читайте так же:
Как посчитать количество прописных или строчных букв в ячейке?

Создайте формулу, используя функцию IF:

Предположим, что ячейка D10 содержит значение продаж.

Эта формула устанавливает определенный уровень скидки в зависимости от уровня объема продаж. Количество инвестиционных уровней и, следовательно, количество уровней скидок может быть гораздо больше.

Операторы сравнения.

В булевых выражениях для сравнения значений и формул можно использовать следующие операторы сравнения:

Больше или равно.

= меньше или равна.

I F в Excel можно использовать для более эффективного решения прикладных экономических задач с помощью операторов сравнения.

В примере №3 мы просто использовали >= больше или равно, чтобы указать диапазон ссылок.

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

Функции И, ИЛИ, НЕ

В Excel функция И работает следующим образом:

Функция возвращает TRUE, если вычисление всех аргументов приводит к TRUE; она возвращает FALSE, если вычисление хотя бы одного аргумента приводит к FALSE.

Функция ИЛИ в Excel имеет аналогичный синтаксис:

Однако он возвращает TRUE, если хотя бы один из аргументов равен TRUE. Если все аргументы равны FALSE, возвращается FALSE.

В Excel выражение NOT имеет следующий синтаксис:

N OT используется для проверки того, что значение не равно какому-либо определенному значению. Если значение равно TRUE, возвращается FALSE и наоборот.

Пример 4. Использование нескольких условий.

Предположим, что нам нужно отобрать должников, которые должны нам более 100 000 рублей за 3 месяца. Предположим, что в этом случае формула должна учитывать критерий «постоянный должник».

В ячейке B10 указан срок задолженности в месяцах, а в ячейке C10 — сумма задолженности.

Формула выглядит следующим образом

Если рассматривать критическую задолженность более трех месяцев или более 100 000 рублей, то формулу можно записать следующим образом:

Как вы можете себе представить, возможности использования функции ЕСЛИ в Excel безграничны.

Самое главное — продумать алгоритм расчета, и для этого в MS Excel достаточно инструментов.

Excel имеет дополнительные функции для анализа данных с использованием условий. Например, функции READ и READ можно использовать для подсчета количества появлений текстовой строки или числа в диапазоне ячеек. Функции SUMMESLY и SUMMESLIMN можно использовать для суммирования значений, которые попадают в интервал, заданный текстовой строкой или числом.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector