10 наиболее полезных функций при анализе данных в Excel
Excel содержит огромное количество самых разнообразных функций, однако не все они нужны при анализе данных. В этой статье вы узнаете о 10 наиболее популярных функций, которые будут нужны при работе с информацией. Эти функции позволяют выполнить большинство задач, которые появляются при анализе данных.
Эта функция является одной из самых популярных и часто используемых в Excel. Если вам необходимо найти данные в одном столбце в таблице и получить значение из другого столбца таблицы, то эта функция вам поможет. Ее синтаксис:
— Искомое значение — это то значение, которое мы будем искать в таблице с данными
— Таблица — диапазон данных, в первом столбце которого мы будем искать искомое значение
— Номер столбца — этот параметр обозначает, на какое количество столбцов надо сдвинуться вправо в таблице для получения результата
— Интервальный просмотр — Может принимать параметр 0 или ЛОЖЬ, что обозначает что совпадение между искомым значением и значением в первом столбце таблицы должен быть точным; либо 1 или ИСТИНА, соответственно совпадение должно быть неточным. Настоятельно рекомендую использовать только параметр ЛОЖЬ, иначе можно получать непредсказуемые результаты.
Если хотите изучить более подробно, как работает функция ВПР, прочитайте нашу статью «Функция ВПР в Excel».
Как сравнить два столбца в Excel на совпадения — ЭКСЕЛЬ ХАК
Часто данные, которые мы получаем из внешних источников, имеют текстовый формат и мы не можем производить с ними математических действий (складывать, вычитать и т.п.). Нам требуется сначала преобразовать текст в число, для этого используйте функцию ЗНАЧЕН. Синтаксис у функции следующий:
Как сделать выпадающий список в Excel?
Начнем с основ. Для того, чтобы создать выпадающий список потребуется список с данными и инструмент «Проверка данных».
Выбираем ячейку, в которой будем создавать выпадающий список. Далее переходим к инструменту «Проверка данных», тип данных – «Список». В поле «Источник» указываем диапазон списка.
Такой способ позволяет представить обычный диапазон в виде выпадающего списка. Повторы данных остались в списке (в диапазоне A2:A16 названия городов повторяются и в выпадающем списке они также повторяются). Это, конечно, не удобно. О том, как сделать выпадающий список уникальных значений в Excel мы поговорим далее, пока остановимся на этом варианте.
Найти несколько значений в Excel — Как создать поиск значений в Excel функцией ВПР по нескольким листам? Как в офисе.
Подобно функции ПОИСК, функция НАЙТИ возвращает ошибку, если искомый текст не найден. Таким образом, альтернативную версию предыдущей формулы можно записать в следующем виде:
Excel количество уникальных значений в столбце
В большинстве примеров, представленных в предыдущем разделе, используются функции и формулы, которые подсчитывают количество ячеек, удовлетворяющих определенным условиям. В этом разделе рассмотрены более сложные примеры подсчета ячеек рабочего листа с применением дополнительных формул, включающих различные виды условий.
Подсчет ячеек с помощью функции СЧЁТЕСЛИ
Функцию СЧЁТЕСЛИ чаще всего используют в формулах, ограниченных одним условием. Функция СЧЁТЕСЛИ принимает два аргумента.
• Диапазон. Содержит значения, определяющие, включать ли конкретную ячейку в расчет. • Логическое_условие. Определяет, включать ли конкретную ячейку в расчет.
Подсчет ячеек, удовлетворяющих множеству условий
Во многих случаях формула должна учитывать только те ячейки, которые удовлетворяют двум или более условиям. Выбор условий основывается на ячейках, которые уже подсчитаны, или на строго определенном диапазоне ячеек.
Все примеры этого раздела приведены для простого рабочего листа, показанного на рисунке, в котором в столбцах Месяц, Представитель и Тип содержатся данные о продажах товара фирмой. Диапазоны ячеек данного рабочего листа имеют имена, соответствующие значениям, введенным в строку 1.
Некоторые примеры этого раздела используют функцию СЧЁТЕСЛИМН, введенную в версии Excel 2007. В разделе также будут представлены альтернативные версии формул, которые нужно использовать, если создаваемую рабочую книгу требуется совместно использовать с теми, у кого установлены предыдущие версии Excel.
До выхода в свет Excel 2007 вам пришлось бы использовать формулу следующего вида:
Возможно, вид данной формулы вводит в заблуждение. Несмотря на то, что ее цель состоит в подсчете значений, которые меньше или равны 200, но больше ста, в формуле написано “>200”. Объяснение простое: знак “минус” означает, что эти ячейки вычитаются из общего количества. Существует еще один метод, в котором используется формула массива (этот метод проще предыдущего):
При вводе формулы массива не забывайте нажимать Ctrl+Shift+Enter> вместо Enter>; также не вводите вручную фигурные скобки – они будут вставлены автоматически.
Условия подсчета можно задать не только для суммируемых, но и для других ячеек. Например, можно подсчитать количество продаж в ячейках, соответствующих следующим условиям:
• месяц – январь и • представитель – Данилкин и • сумма больше 1000.
Следующая формула возвращает количество ячеек массива, соответствующих всем трем условиям:
В качестве альтернативы можно использовать следующую формулу массива:
Чтобы подсчитать количество ячеек с помощью условия ИЛИ, иногда задействуется несколько функций СЧЁТЕСЛИ. Например, следующая формула подсчитывает количество значений 1, 3 и 5 в диапазоне Данные:
Функция СЧЁТЕСЛИ также используется в формуле массива. Например, формула массива, приведенная ниже, возвращает тот же результат, что и предыдущая формула.
Если условие ИЛИ применяется не к подсчитываемым ячейкам, а к ячейкам другого диапазона, функция СЧЁТЕСЛИ работать не будет. Вернемся снова к рисунку выше и предположим, что необходимо подсчитать количество продаж, соответствующих следующим условиям:
• месяц – январь или • представитель – Данилкин или • объем – более 1000.
Следующая формула массива возвращает правильный результат подсчета:
Одновременное использование условий И и ИЛИ
При подсчете ячеек условия И и ИЛИ можно комбинировать. Предположим, что требуется подсчитать продажи, удовлетворяющие следующим условиям:
• месяц – январь и • представитель Данилкин или • представитель Кукина.
Следующая формула массива возвращает объем продаж, соответствующих этим условиям:
Так как в данном случае в аргументах функции приходится повторять операцию “И”, использование СЧЁТЕСЛИМН может привести к созданию длинных формул с множеством критериев. Когда список критериев велик, удобнее использовать формулы массивов. К примеру, следующая формула массива вернет тот же результат, что и предыдущая:
Подсчет наиболее часто встречающихся записей
Функция МОДА возвращает значение, наиболее часто встречающееся в диапазоне ячеек. На рисунке показан рабочий лист, содержащий диапазон ячеек А1:А10 (с именем Диапазон1). Формула, приведенная ниже, возвращает значение 10, поскольку это значение в данном диапазоне ячеек встречается наиболее часто.
Эта формула возвращает значение #Н/Д, если диапазон, указанный в ее аргументе, не содержит дублирующихся значений.
Чтобы подсчитать количество наиболее часто встречающихся в заданном диапазоне значений (иными словами, частоту моды), воспользуйтесь следующей формулой:
Представленная формула возвращает значение 3, поскольку значение моды (10) встречается в диапазоне Диапазон1 три раза.
Имейте в виду, что функция МОДА работает только с числовыми значениями. В том случае, если заданная ячейка содержит текст, функция ее игнорирует. Для того чтобы найти текстовую строку, наиболее часто встречающуюся в заданном диапазоне, используйте формулу массива.
Чтобы подсчитать количество элементов, наиболее часто встречающихся в заданном диапазоне Данные (речь идет и о тексте, и о числовых значениях), используйте следующую формулу массива:
Представленная ниже формула массива работает подобно функции МОДА, за исключением того, что в данном случае могут использоваться как числовые, так и текстовые значения.
Если существует несколько значений, встречающихся с одинаковой максимальной частотой, данная формула вернет первое из них.
Подсчет количества вхождений заданного текста
Чтобы подсчитать количество ячеек, которые содержат только значение, введенное в ячейку Текст (и не содержат никаких других символов), воспользуйтесь функцией СЧЁТЕСЛИ. Для этого создайте следующую формулу:
Следующая формула массива подобна предыдущей, за исключением того, что учитывается регистр символов:
Чтобы подсчитать количество ячеек, которые содержат строку, включающую в себя строку ячейки Текст в качестве подстроки, используйте следующую формулу:
Например, если в ячейке Текст находится текст Альфа, данная формула возвращает значение 3, поскольку в диапазоне Диапазон2 текст Альфа присутствует в трех ячейках: А2, А8 и А10. Обратите внимание, что регистр в данном случае не учитывается.
Альтернативная формула массива, использующая функцию ПОИСК, немного длиннее:
Функция ПОИСК возвращает ошибку, если искомый текст не найден. В приведенном примере подсчитываются все ячейки, в которых не была сгенерирована ошибка. Так как функция ПОИСК не чувствительна к регистру символов, то же можно сказать и о формуле.
Если нужно подсчитать вхождения с учетом регистра символов, можете использовать следующую формулу массива:
Если ячейка Текст содержит слово “Альфа”, предыдущая формула возвращает значение 2, поскольку данная текстовая строка встречается в двух ячейках диапазона (А2 и А8).
Подобно функции ПОИСК, функция НАЙТИ возвращает ошибку, если искомый текст не найден. Таким образом, альтернативную версию предыдущей формулы можно записать в следующем виде:
В отличие от ПОИСК, функция НАЙТИ различает регистр символов.
Общее количество экземпляров строки в заданном диапазоне
Чтобы подсчитать общее количество экземпляров строки в заданном диапазоне ячеек, используйте следующую формулу массива:
Если ячейка Текст содержит символ Б, данная формула возвращает значение 7 – этот символ есть в семи ячейках заданного диапазона. Имейте в виду, что эта формула учитывает регистр символа. Чтобы игнорировать регистр, используйте следующую измененную формулу массива:
Подсчет количества уникальных значений
Следующая формула массива возвращает количество уникальных значений в диапазоне Интервал:
Для полного понимания того, как работает эта формула, вам необходимо усвоить основные принципы работы формул массива. На рисунке показан рабочий лист, содержащий два диапазона ячеек: диапазон А1:А12 (Интервал) и диапазон С1:С12, во все ячейки которого включена следующая формула массива (одна формула скопирована во все 12 ячеек диапазона):
Данный массив состоит из значений, полученных путем деления 1 на значения массива в диапазоне ячеек Cl:С12. Например, если какая-либо ячейка исходного диапазона Интервал содержит значение 200, то соответствующая ей ячейка в диапазоне Dl:D12 будет иметь значение 0,05.
Суммирование значений диапазона D1:D12 дает число одинаковых элементов, содержащихся в массиве Интервал. По существу, формула, приведенная в начале этого раздела, создает массив значений в диапазоне ячеек Dl:D12 и суммирует значения этого массива.
Однако эта формула имеет серьезное ограничение: в том случае, если диапазон содержит пустые ячейки, формула возвращает ошибку. Приведенная ниже формула массива решает эту проблему.
• функция ЧАСТОТА; • собственная формула; • надстройка “Пакет анализа”; • сводная таблица.
Если данные представлены в форме базы данных, для создания распределения частот можно также использовать сводную таблицу.
Самый простой способ создания таблицы распределения частот в приложении Excel – использование функции ЧАСТОТА. Эта функция возвращает массив, поэтому она всегда должна применяться в формуле массива, введенной в диапазон ячеек.
На рисунке в диапазон ячеек A1:Е20 (Диапазон3) введены значения от 1 до 500. Диапазон G2:G11 задает уровни дискретизации (интервалы), используемые в распределении частот. Каждая ячейка этого диапазона представляет собой верхний предел интервала. В данном случае используются интервалы 1-50, 51-100, 101-150 и т.д. Более простой способ создания интервалов рассматривается далее.
Чтобы создать график распределения частот, выберите диапазон ячеек, значения в которых соответствуют интервалам. После чего введите следующую формулу массива:
Приведенная формула массива подсчитывает количество значений в диапазоне Диапазон3, которые попадают в каждый интервал. Чтобы представить распределение частот в виде процентной зависимости, используйте следующую функцию:
На рисунке показаны два варианта распределения частот: один в виде числовых значений, а другой – в процентах. Кроме того, здесь показана диаграмма (или гистограмма), созданная на основе полученного распределения частот.
Создание интервалов для распределения частот
Прежде чем приступить к созданию распределения частот в диапазоне значений, нужно определить интервалы. Количество интервалов задает количество категорий в распределении. В большинстве случаев используются интервалы одинаковой длины.
Чтобы создать 10 равномерно удаленных друг от друга уровней дискретизации для значений диапазона Данные, в диапазон, состоящий из 10 ячеек отдельного столбца, введите следующую формулу массива.
Эта формула создает 10 интервалов на основе значений диапазона Данные. Верхнее значение элемента всегда соответствует максимальному значению интервала.
Чтобы создать большее или меньшее количество интервалов, необходимо использовать другое значение, отличное от 10, а саму формулу массива ввести в диапазон, содержащий такое же количество ячеек. Например, чтобы создать пять интервалов, введите следующую формулу массива в диапазон, состоящий из пяти расположенных вертикально ячеек.
Использование формул для создания таблицы распределения частот
Формула в ячейке G2, приведенная ниже, представляет собой формулу массива, которая подсчитывает количество баллов, относящихся к оценке А.
Формулы в столбце Н вычисляют процентное соотношение оценок. Формула, содержащаяся в ячейке Н2, скопирована в четыре ячейки, находящиеся ниже Н2.
Использование надстройки “Пакет анализа” для создания таблицы распределения частот
Учтите, что в данном случае распределение частот состоит из значений, а не формул. Поэтому при внесении изменений во входные данные нужно повторно открыть диалоговое окно Гистограмма, чтобы обновить отображаемые графические результаты.
Чтобы проверить, установлен ли в Excel пакет анализа, откройте вкладку Данные. Если на ленте есть группа Анализ, а в ней – кнопка Анализ данных, значит, пакет анализа установлен, а если нет – его нужно установить. Для этого выполните следующие действия.
Применение сводной таблицы для создания гистограммы частот
Если данные представлены в табличном виде, гистограмму частот можно создать с помощью сводной таблицы. На рисунке показаны данные успеваемости студентов, обобщенные в сводной таблице. Полосы данных добавлены с помощью средств условного форматирования.
Применение настраиваемых уровней дискретизации для создания гистограмм
Данная диаграмма использует в формуле РЯД два динамических имени. Имя Категории определяется с помощью следующей формулы:
Имя Частота определяется следующей формулой:
В результате при изменении содержимого ячейки РазмерыИнтервалов диаграмма автоматически корректируется.
Excel количество уникальных значений в столбце — Офис Ассист
Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.
Извлекаем 2-е, 3-е и т.д. значения, используя ВПР
Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!
Предположим, в одном столбце таблицы записаны имена клиентов (Customer Name), а в другом – товары (Product), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные заданным клиентом.
Простейший способ – добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с номером повторения каждого имени, например, John Doe1, John Doe2 и т.д. Фокус с нумерацией сделаем при помощи функции COUNTIF (СЧЁТЕСЛИ), учитывая, что имена клиентов находятся в столбце B:
После этого Вы можете использовать обычную функцию ВПР, чтобы найти нужный заказ. Например:
-
Находим 2-й товар, заказанный покупателем Dan Brown:
=VLOOKUP(«Dan Brown2»,$A$2:$C$16,3,FALSE) =ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)
=VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE) =ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)
На самом деле, Вы можете ввести ссылку на ячейку в качестве искомого значения вместо текста, как представлено на следующем рисунке:
Если Вы ищите только 2-е повторение, то можете сделать это без вспомогательного столбца, создав более сложную формулу:
- $F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);
- $B$ – столбец Customer Name;
- Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);
- $C16 – конечная ячейка Вашей таблицы или диапазона.
Эта формула находит только второе совпадающее значение. Если же Вам необходимо извлечь остальные повторения, воспользуйтесь предыдущим решением.
Если Вам нужен список всех совпадений – функция ВПР тут не помощник, поскольку она возвращает только одно значение за раз – и точка. Но в Excel есть функция INDEX (ИНДЕКС), которая с легкостью справится с этой задачей. Как будет выглядеть такая формула, Вы узнаете в следующем примере.
Excel формула уникальные значения из столбца
Excel содержит огромное количество самых разнообразных функций, однако не все они нужны при анализе данных. В этой статье вы узнаете о 10 наиболее популярных функций, которые будут нужны при работе с информацией. Эти функции позволяют выполнить большинство задач, которые появляются при анализе данных.
