Перечислите те функции программы excel: Функции Excel (по категориям) — Служба поддержки Майкрософт

Содержание

Статистические функции Excel, которые необходимо знать


Автор Антон Андронов На чтение 4 мин Опубликовано

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

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

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

Содержание

  1. СРЗНАЧ()
  2. СРЗНАЧЕСЛИ()
  3. МАКС()
  4. МИН()
  5. НАИБОЛЬШИЙ()
  6. НАИМЕНЬШИЙ()
  7. МЕДИАНА()
  8. МОДА()

СРЗНАЧ()

Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.

Данная функция может принимать до 255 аргументов и находить среднее сразу в нескольких несмежных диапазонах и ячейках:

Если в рассчитываемом диапазоне встречаются пустые или содержащие текст ячейки, то они игнорируются. В примере ниже среднее ищется по четырем ячейкам, т.е. (4+15+11+22)/4 = 13

Если необходимо вычислить среднее, учитывая все ячейки диапазона, то можно воспользоваться статистической функцией СРЗНАЧА. В следующем примере среднее ищется уже по 6 ячейкам, т.е. (4+15+11+22)/6 = 8,6(6).

Статистическая функция СРЗНАЧ может использовать в качестве своих аргументов математические операторы и различные функции Excel:

СРЗНАЧЕСЛИ()

Если необходимо вернуть среднее арифметическое значений, которые удовлетворяют определенному условию, то можно воспользоваться статистической функцией СРЗНАЧЕСЛИ. Следующая формула вычисляет среднее чисел, которые больше нуля:

В данном примере для подсчета среднего и проверки условия используется один и тот же диапазон, что не всегда удобно. На этот случай у функции СРЗНАЧЕСЛИ существует третий необязательный аргумент, по которому можно вычислять среднее. Т.е. по первому аргументу проверяем условие, по третьему – находим среднее.

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

Если требуется соблюсти несколько условий, то всегда можно применить статистическую функцию СРЗНАЧЕСЛИМН, которая позволяет считать среднее арифметическое ячеек, удовлетворяющих двум и более критериям.

МАКС()

Статистическая функция МАКС возвращает наибольшее значение в диапазоне ячеек:

МИН()

Статистическая функция МИН возвращает наименьшее значение в диапазоне ячеек:

НАИБОЛЬШИЙ()

Возвращает n-ое по величине значение из массива числовых данных. Например, на рисунке ниже мы нашли пятое по величине значение из списка.

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

НАИМЕНЬШИЙ()

Возвращает n-ое наименьшее значение из массива числовых данных. Например, на рисунке ниже мы нашли четвертое наименьшее значение из списка.

Если отсортировать числа в порядке возрастания, то все станет гораздо очевидней:

МЕДИАНА()

Статистическая функция МЕДИАНА возвращает медиану из заданного массива числовых данных. Медианой называют число, которое является серединой числового множества. Если в списке нечетное количество значений, то функция возвращает то, что находится ровно по середине. Если же количество значений четное, то функция возвращает среднее для двух чисел.

Например, на рисунке ниже формула возвращает медиану для списка, состоящего из 14 чисел.

Если отсортировать значения в порядке возрастания, то все становится на много понятней:

МОДА()

Возвращает наиболее часто встречающееся значение в массиве числовых данных.

Если отсортировать числа в порядке возрастания, то все становится гораздо понятней:

Статистическая функция МОДА на данный момент устарела, точнее, устарела ее форма записи. Вместо нее теперь используется функция МОДА.ОДН. Форма записи МОДА также поддерживается в Excel для совместимости.

Как известно, категория Статистические в Excel содержит более 100 самых разноплановых функций. Но, как показывает практика, львиная доля этих функций практически не применяется, а особенно начинающими пользователями. В этом уроке мы постарались познакомить Вас только с самыми популярными статистическими функциями Excel, которые Вы рано или поздно сможете применить на практике. Надеюсь, что данный урок был для Вас полезен. Удачи Вам и успехов в изучении Excel.

Оцените качество статьи. Нам важно ваше мнение:

7 программ, кроме Excel, которые помогут организовать вашу работу

Автор:

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

Достичь того, что Excel не удается

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

Во-вторых, Excel не всегда хорошо взаимодействует со всеми приложениями и программами. Например, если пользователь использует эту программу на MacOS, он обязательно заметит, что Excel не приспособлен для работы с этой операционной системой. В-третьих, современный мир деловых партнерских отношений порождает большую потребность в управлении проектами и входе, предназначенном для пользователей из различных источников, а Excel такую ​​потребность удовлетворить не может. Когда то Microsoft Office был единственным таким «всплеском» в водовороте современных технологий. Теперь ситуация кардинально изменилась и пользователи могут бесплатно загрузить отдельные приложения и программы, которые выполняют все те же функции, что когда-то мог выполнить только Excel. Вот 7 таких программ:

# 1: Электронная таблица Smartsheet + Инструменты эффективного пользования

Smartsheet — это программа, сочетающая функционал электронной таблицы Excel с интерфейсом программы для управления проектами. Автоматизированные особенности этой программы помогают упорядочить работу и привести в порядок все мелкие детали проекта.

# 2 Надежная и скоростная приборная панель от Workzone

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

# 3: Реализация проекта Microsoft с помощью подписки Office 365

Когда речь идет об офисном программном обеспечении, трудно игнорировать компанию Microsoft. Мы внесли в список лучших «неекселивських» программ и Microsoft Project, которая идет в наборе с подпиской на Office 365. Программа сочетает оригинальный Excel и премиум-инструменты для управления проектами.

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

# 4: Таблицы Google как альтернатива Excel

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

# 5: Лучший инструмент для организации работы малого бизнеса

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

# 6: Электронная таблица для управления проектами, которая отслеживает выполнение задач

Asana — это хорошо известная программа для управления проектами. Она содержит функцию отслеживания проектов, мобильные приложения, а также синхронизируется с Google, правда, последняя функция ограничена в рамках электронных таблиц программы. С помощью Asana пользователи могут правильно распределять информацию, встречи, сообщения, задачи. Эта программа отнюдь не напоминает электронную таблицу Excel, однако, как ее альтернатива, Вы можете использовать Asana для формирования информации, встреч, сообщений и задач.

# 7: Отслеживание проектов в электронной таблице

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

Источник: NAI Global

Список функций Excel с примерами формул

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

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

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

Большинство функций, перечисленных ниже, являются функциями рабочего листа Excel , которые используются как часть формул в ячейке. Последняя категория включает в себя пользовательские функции Excel, также известные как пользовательские функции (UDF). Это функции на основе VBA, расширяющие базовые возможности Excel за счет настраиваемых функций. Выберите категорию, чтобы перейти к списку функций с краткими описаниями и ссылками на примеры использования.

Текстовые функции Excel (строковые функции)

Существует множество функций Microsoft Excel для работы с текстовыми строками. Вот самые важные из них:

Функция ТЕКСТ

ТЕКСТ(значение, формат_текст) используется для преобразования числа или даты в тестовую строку в указанном формате, где:

  • Значение — это числовое значение, которое необходимо преобразовать в текст.
  • Format_text — желаемый формат.

Следующие формулы демонстрируют функцию ТЕКСТ Excel в действии:

=ТЕКСТ(A1,"мм/дд/гггг") — преобразовать дату в ячейке A1 в текстовую строку в традиционном формате даты США, например, «01/01/2015» (месяц/день/год) .

=ТЕКСТ(A1,"€#,##0.00") — преобразует число в A1 в текстовую строку валюты, такую ​​как «3,00€».

Примеры формул ТЕКСТ:

  • Функция ТЕКСТ для преобразования даты в текстовый формат
  • ТЕКСТОВЫЕ формулы для преобразования числа в текст

Функция СЦЕПИТЬ

СЦЕПИТЬ(текст1, [текст2], …) предназначен для объединения нескольких фрагментов текста или объединения значений из нескольких ячеек в одну ячейку. Аналогичного результата можно добиться с помощью оператора Excel и , как показано на следующем снимке экрана.

Вы можете найти множество других примеров формул в учебнике СЦЕПИТЬ в Excel: объединить текстовые строки, ячейки и столбцы.

Функция ТРИМ

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

.

Дополнительные примеры формул см. в разделе 3 способа удаления пробелов между словами/цифрами в Excel.

Функция ЗАМЕНА

ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра]) заменяет один набор символов другим в указанной ячейке или текстовой строке. Синтаксис функции ПОДСТАВИТЬ следующий:

  • Текст — исходная текстовая строка или ссылка на ячейку, в которую нужно подставить определенные символы.
  • Old_text — символы, которые вы хотите заменить.
  • New_text — символы, которыми вы хотите заменить старый текст.
  • Nth_appearance — необязательный параметр, указывающий, какое вхождение old_text вы хотите заменить новым_текстом. Если опущено, то каждое вхождение старого текста будет заменено новым текстом.

Например, следующая формула ПОДСТАВИТЬ заменяет все запятые в ячейке A1 точкой с запятой:

=ЗАМЕНИТЬ(A2, ",", ";")

ПОДСТАВИТЬ примеры формул:

  • Удаление разрывов строк в ячейке
  • Преобразование текстовых строк с пользовательскими разделителями в даты

Функция ЗНАЧЕНИЕ

ЗНАЧЕНИЕ(текст) — преобразует текстовую строку в число.

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

Примеры формул ЗНАЧЕНИЕ:

  • Преобразование цифр в текстовом формате в число
  • Функция ЗНАЧЕНИЕ для преобразования текста в дату

ТОЧНАЯ функция

EXACT(text1, text2) сравнивает две текстовые строки и возвращает TRUE, если оба значения совпадают, включая регистр, в противном случае FALSE.

Например, если A2 — это «яблоки», а B2 — это «яблоки», формула =ТОЧНО(A2, B2) вернет ЛОЖЬ, поскольку они не являются точным совпадением.

Функция EXACT редко используется сама по себе, но она полезна в более сложных задачах, таких как выполнение Vlookup с учетом регистра в Excel.

Функции для изменения регистра текста (ВЕРХНИЙ, НИЖНИЙ, ПРОПИСНОЙ)

Microsoft Excel предоставляет 3 текстовые функции для преобразования между ПРОПИСНЫМИ, нижними и правильными регистрами.

ПРОПИСНЫЕ(текст) — преобразует все символы указанной текстовой строки в верхний регистр.

LOWER(текст) — изменяет все прописные буквы в текстовой строке на строчные.

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

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

Дополнительные примеры формул для преобразования регистра текста можно найти в разделе Изменение регистра текста в Excel на ПРОПИСНОЙ, нижний или правильный.

Извлечение текстовых символов (ЛЕВЫЙ, ПРАВЫЙ, СРЕДНИЙ)

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

LEFT(text, [num_chars]) — возвращает указанное количество символов от начала текстовой строки.

ПРАВО(текст,[число_символов]) — возвращает указанное количество символов с конца текстовой строки.

MID(text, start_num, num_chars) — возвращает определенное количество символов из текстовой строки, начиная с любой указанной вами позиции.

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

  • Текст — текстовая строка или ссылка на ячейку, содержащую символы, которые вы хотите извлечь.
  • Start_num — указывает, с чего начать (т.е. позицию первого символа, который вы хотите извлечь).
  • Num_chars — количество символов, которые вы хотите извлечь.

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

Логические функции в Excel

Microsoft Excel предоставляет несколько логических функций, которые оценивают заданное(ые) условие(я) и возвращают соответствующее значение.

Функции И, ИЛИ, исключающее ИЛИ

И(логический1, [логический2], …) — возвращает ИСТИНА, если все аргументы оцениваются как ИСТИНА, в противном случае — ЛОЖЬ.

ИЛИ(логический1, [логический2], …) — возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА.

Исключающее ИЛИ(лог1, [лог2],…) — возвращает логических исключающих или всех аргументов. Эта функция появилась в Excel 2013 и недоступна в более ранних версиях.

Примеры формул логических функций:

  • И примеры формул
  • Примеры формулы ИЛИ
  • Примеры формулы XOR

Функция НЕ

НЕ(логическое) — инвертирует значение своего аргумента, т.е. если логическое оценивается как ЛОЖЬ, функция НЕ возвращает ИСТИНА и наоборот.

Например, обе следующие формулы вернут ЛОЖЬ:

= НЕ(ИСТИНА)

= НЕ(2*2=4)

Дополнительные примеры функции НЕ см. в разделе Использование функции НЕ в Excel.

Функция ЕСЛИ

Функцию ЕСЛИ в Excel иногда называют «условной функцией», поскольку она возвращает значение на основе заданного вами условия. Синтаксис IF следующий:

ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

Формула ЕСЛИ проверяет условия, выраженные в аргументе logical_test , и возвращает одно значение ( value_if_true ), если условие выполняется, и другое значение ( value_if_false ), если условие не выполняется.

Например, формула =ЕСЛИ(A1<>"", "хорошо", "плохо") возвращает «хорошо», если в ячейке A1 есть какое-либо значение, и «плохо» в противном случае.

А вот пример вложенной формулы ЕСЛИ, которая «расшифровывает» экзаменационную оценку в ячейке А2:

=ЕСЛИ(A2>80, "Великолепно", ЕСЛИ(A2>50, "Хорошо", ЕСЛИ(A2>30, "Удовлетворительно", "Плохо")))

Примеры формул ЕСЛИ:

  • ЕСЛИ формулы для чисел, текста, дат, пустых ячеек
  • Оператор IF с несколькими условиями И/ИЛИ
  • Вложенные функции ЕСЛИ в Excel
  • Функция ЕСЛИ в формулах массива
  • Использование ЕСЛИ с другими функциями Excel

Функции ЕСЛИОШИБКА и IFNA

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

ЕСЛИОШИБКА(значение, значение_если_ошибка) — проверяет, дает ли формула или выражение ошибку. Если это так, формула возвращает значение, указанное в аргументе value_if_error , в противном случае возвращается результат формулы. Эта функция обрабатывает все возможные ошибки Excel, включая ЗНАЧЕНИЕ, Н/Д, ИМЯ, ССЫЛКА, ЧИСЛО и другие. Он доступен в Excel 2007 и выше.

IFNA(value, value_if_na) — введен в Excel 2013, работает аналогично ЕСЛИОШИБКА, но обрабатывает только ошибки #N/A.

Следующие примеры демонстрируют простейшую формулу ЕСЛИОШИБКА:

Примеры формулы IFERROR / IFNA:

  • Использование функций ЕСЛИОШИБКА и ЕСЛИНА в Excel
  • Excel VLOOKUP с ЕСЛИОШИБКА / ЕОШИБКА

Математические функции Excel

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

Нахождение суммы ячеек

Ниже приведены четыре основные функции Excel для суммирования значений ячеек в указанном диапазоне.

Функция СУММ

СУММ(число1,[число2],…) возвращает сумму своих аргументов. Аргументы могут быть числами, ссылками на ячейки или числовыми значениями на основе формул.

Например, простейшая математическая формула =СУММ(A1:A3, 1) суммирует значения в ячейках A1, A2 и A3, а затем добавляет к результату 1.

Примеры формулы СУММ:

  • Функция СУММ для суммирования столбца В Excel
  • Excel СУММ в формулах массива
Функции СУММЕСЛИ и СУММЕСЛИМН (условная сумма)

Обе функции суммируют ячейки указанного диапазона, соответствующие определенному условию. Разница в том, что СУММЕСЛИМН может оценивать только один критерий, а СУММЕСЛИМН, представленный в Excel 2007, позволяет использовать несколько критериев. Обратите внимание, что порядок аргументов в каждой функции разный:

СУММЕСЛИ(диапазон, критерии, [сумма_диапазон])

СУММЕСЛИМН(сумма_диапазон, критерий_диапазон1, критерий1, [критерий_диапазон2, критерий2], …)

  • диапазон / критерии_диапазон — диапазон ячеек для оценки по соответствующим критериям.
  • критерии — условие, которое должно быть выполнено.
  • sum_range — ячейки для суммирования при выполнении условия.

На следующем снимке экрана показано, как функции СУММЕСЛИ и СУММЕСЛИМН можно использовать с реальными данными:

Примеры формул СУММЕСЛИ и СУММЕСЛИМН:

  • СУММЕСЛИ в Excel — формулы для условного суммирования ячеек
  • Суммарные ячейки с несколькими критериями
Функция СУММПРОИЗВ

СУММПРОИЗВ(массив1,массив2, …) — одна из немногих функций Microsoft Excel, обрабатывающих массивы. Он умножает предоставленные компоненты массива и возвращает сумму произведений.

Суть функции СУММПРОИЗВ может быть трудна для понимания, и мы надеемся, что следующие примеры прольют свет на ее основные области применения.

Примеры формулы СУММПРОИЗВ:

  • Формула SUMPRODUCT для Vlookup с учетом регистра
  • Подсчет дубликатов между двумя столбцами
  • Суммарные ячейки с несколькими критериями

Генерация случайных чисел (СЛУЧАЙ и СЛУЧМЕЖДУ)

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

RAND() — возвращает случайное действительное (десятичное) число от 0 до 1.

RANDBETWEEN(bottom, top) — возвращает случайное целое число между нижними и верхними числами, которые вы укажете.

В следующем учебном пособии довольно хорошо объясняется суть каждой функции с примерами формул: Как использовать функции СЛЧИС и СЛУЧМЕЖДУ в Excel.

Функции округления

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

  • ОКРУГЛ — округлить число до указанного количества цифр.
  • ROUNDUP — округление числа в большую сторону до указанного количества цифр.
  • ROUNDDOWN — округлить число в меньшую сторону до указанного количества цифр.
  • ОКРУГЛ — округляет число в большую или меньшую сторону до указанного кратного.
  • ЭТАЖ — округлить число в меньшую сторону до указанного кратного.
  • ПОТОЛОК — округлить число до указанного кратного.
  • INT — округлить число до ближайшего целого числа в меньшую сторону.
  • TRUNC — обрезать число до указанного количества знаков после запятой.
  • EVEN — округлить число до ближайшего четного целого числа.
  • ODD — округлить число до ближайшего нечетного целого числа.

Получение остатка после деления (функция MOD)

MOD(число, делитель) возвращает остаток после деления аргумента число на делитель .

Функция очень удобна во многих случаях, например:

  • Сумма значений в каждой второй или N -й -й строке
  • Чередование цветов строк в Excel

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

Нахождение наибольшего, наименьшего и среднего значения

MIN(число1, [число2], …) — возвращает минимальное значение из списка аргументов.

МАКС(число1, [число2], …) — возвращает максимальное значение из списка аргументов

СРЗНАЧ(число1, [число2], …) — возвращает среднее значение аргументов.

SMALL(массив, k) — возвращает k-е наименьшее значение в массиве.

НАИБОЛЬШИЙ(массив, k) — возвращает k-е наибольшее значение в массиве.

На следующем снимке экрана показаны основные статистические функции в действии.

Примеры формул:

  • Использование ЕСЛИ с функциями СРЗНАЧ, МИН и МАКС
  • Вычисление среднего значения первых N элементов списка — Пример 1. Вычисление среднего значения первых N элементов списка
  • ВПР со СРЗНАЧ, МАКС., МИН. (формулы массива)
  • Сумма N наибольших/наименьших чисел в диапазоне (формула массива)

Счетные ячейки

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

COUNT(значение1, [значение2], …) — возвращает количество числовых значений (числа и даты) в списке аргументов.

СЧЁТ(значение1, [значение2], …) — возвращает количество непустых ячеек в списке аргументов. Он подсчитывает ячейки, содержащие любую информацию, включая значения ошибок и пустые текстовые строки («»), возвращаемые другими формулами.

СЧИТАТЬПУСТОТЫ(диапазон) — подсчитывает количество пустых ячеек в указанном диапазоне. Ячейки с пустыми текстовыми строками («») также считаются пустыми ячейками.

СЧЁТЕСЛИ(диапазон, критерии) — подсчитывает количество ячеек в диапазоне, соответствующих заданным критериям.

СЧЕТЧИСЛО(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерии2]…) — подсчитывает количество ячеек, соответствующих всем указанным критериям.

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

Примечание. Поскольку даты хранятся в Excel как числа, они также учитываются статистическими функциями Excel. Например, формула =СЧЁТЕСЛИ(A2:A9, ">5") подсчитывает дату в ячейке A8 на снимке экрана выше, поскольку во внутренней системе Excel она хранится как 42005.

Примеры формул для подсчета ячеек:

  • Подсчет ячеек с текстом (функции COUNTA и COUNT)
  • Подсчет пустых ячеек в Excel (COUNTBLANK)
  • Подсчет пустых или не пустых ячеек (COUNTIF)
  • Использование СЧЁТЕСЛИ в Excel
  • Подсчет ячеек с несколькими критериями (COUNTIFS)

Функции поиска и ссылок в Excel

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

Функция ВПР

Функция ВПР ищет указанное значение в первом столбце и извлекает соответствующие данные из той же строки в другом столбце. Требуются следующие аргументы:

ВПР (искомое_значение, массив_таблиц, номер_индекса_столбца, [диапазон_просмотра])

  • lookup_value — значение для поиска.
  • table_array — два или более столбца данных.
  • col_index_num — номер столбца, из которого извлекаются данные.
  • range_lookup — определяет, следует ли искать с точным совпадением (FALSE) или с приблизительным соответствием (TRUE или опущено).

Например, формула =ВПР("яблоки", A2:C10, 3) ищет «яблоки» в ячейках с A2 по A10 и возвращает соответствующее значение из столбца C:

Примеры формулы ВПР:

  • Учебное пособие по ВПР Excel для начинающих
  • Как использовать функции ВПР и СУММ или СУММЕСЛИ в Excel
  • Двусторонний поиск, вложенный ВПР с несколькими критериями
  • 4 способа выполнения Vlookup с учетом регистра в Excel
  • Excel VLOOKUP не работает — решения для ошибок N/A, NAME и VALUE

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

ИНДЕКС(массив, номер_строки, [номер_столбца]) — возвращает ссылку на ячейку в массиве на основе указанных вами номеров строки и столбца.

Вот простая формула ИНДЕКС: =ИНДЕКС(A1:C10, 3 ,4) , которая выполняет поиск в ячейках от A1 до C10 и возвращает значение на пересечении строки 3 rd и столбца 4 th , который это ячейка D3.

В следующем учебном пособии подробно объясняется функция ИНДЕКС: 6 наиболее эффективных способов использования функции ИНДЕКС в Excel.

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

ПОИСКПОЗ(искомое_значение, искомое_массив, [тип_сопоставления]) — ищет искомое_значение в искомом_массиве, а затем возвращает относительное положение этого элемента в диапазоне.

Комбинацию функций ПОИСКПОЗ и ИНДЕКС можно использовать как более мощную и универсальную альтернативу ВПР в Excel, как показано в следующем руководстве: ИНДЕКС и ПОИСКПОЗ — лучшая альтернатива ВПР в Excel.

НЕПРЯМАЯ функция

ДВССЫЛ(ref_text, [a1]) — возвращает ссылку на ячейку или диапазон, заданную текстовой строкой.

Вот пример простейшей формулы ДВССЫЛ, чтобы получить общее представление:

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

Примеры формул НЕПРЯМОЙ:

  • Как использовать функцию ДВССЫЛ в Excel
  • Как сделать зависимый выпадающий список в Excel

Функция СМЕЩЕНИЯ

OFFSET(reference, rows, cols, [height], [width]) — возвращает ссылку на диапазон ячеек, который смещен от начальной ячейки или диапазона ячеек на указанное количество строк и столбцов.

Например, =СМЕЩ(A1, 1, 2) возвращает значение в ячейке C2, поскольку она находится на 1 строку ниже и на 2 столбца левее от A1.

Дополнительные примеры формул СМЕЩ см. в разделе Использование функции СМЕЩ в Excel.

Функция ТРАНСП

ТРАНСП(массив) — преобразует горизонтальный диапазон ячеек в вертикальный диапазон и наоборот, т.е. преобразует строки в столбцы и столбцы в строки.

В следующем учебном пособии приводятся примеры формул и объясняются сильные и слабые стороны функции: Функция ТРАНСП в Excel для преобразования строки в столбец.

Функция ГИПЕРССЫЛКИ

HYPERLINK(link_location, [friendly_name]) — Создает гиперссылку на документ, хранящийся в локальной сети или Интернете.

Пошаговые инструкции по использованию функции ГИПЕРССЫЛКА см. в разделе Как добавить гиперссылку на другой лист Excel.

Финансовые функции Excel

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

Функция FV

FV(rate, nper ,pmt ,[pv], [type]) — вычисляет будущую стоимость инвестиций на основе постоянной процентной ставки.

В следующем учебном пособии подробно объясняется каждый из аргументов и содержатся инструкции по созданию универсального калькулятора сложных процентов на рабочих листах: Создание расширенного калькулятора сложных процентов для Excel.

Функции даты Excel

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

Создание дат

  • ДАТА — возвращает порядковый номер указанной даты.
  • ДАТАЗНАЧ — преобразует текстовую строку, представляющую формат даты в дату.

Текущие дата и время

  • СЕГОДНЯ — возвращает текущую дату.
  • СЕЙЧАС — возвращает текущую дату и время.

Извлечение дат и компонентов даты

  • ДЕНЬ — возвращает день месяца.
  • МЕСЯЦ — возвращает месяц указанной даты.
  • ГОД — возвращает год указанной даты.
  • КОНМЕСЯЦ — возвращает последний день месяца.
  • ДЕНЬ НЕД — возвращает день недели.
  • WEEKNUM — возвращает номер недели даты.

Расчет разницы дат

  • РАЗНДАТ — возвращает разницу между двумя датами.
  • EDATE — возвращает дату, которая является указанным числом месяцев до или после даты начала.
  • YEARFRAC — вычисляет долю года между двумя датами.

Расчет рабочих дней

  • РАБДЕНЬ — вычисляет дату, которая является заданным числом рабочих дней до или после даты начала.
  • РАБДЕНЬ.INTL — вычисляет дату, которая представляет собой указанное количество дней недели до или после даты начала, с пользовательскими параметрами выходных.
  • ЧИСТРАБДНИ — возвращает количество рабочих дней между двумя датами.
  • NETWORKDAYS.INTL — возвращает количество рабочих дней между двумя датами с настраиваемыми выходными.

Функции времени Excel

Ниже приводится список основных функций Excel для работы со временем.

ВРЕМЯ(час,минута,секунда) — возвращает порядковый номер, представляющий время.

TIMEVALUE(time_text) — преобразует время, введенное в виде текстовой строки, в порядковый номер, представляющий время.

NOW() — возвращает серийный номер, соответствующий текущей дате и времени.

ЧАС(серийный_номер) — преобразует указанный порядковый номер в час.

МИНУТЫ(серийный_номер) — преобразует указанный серийный номер в минуты.

SECOND(serial_number) — преобразует указанный серийный номер в секунды.

Примеры формулы времени:

  • Преобразование времени в десятичное число, часы, минуты или секунды
  • Расчет времени в Excel — разница во времени, сложение/вычитание времени
  • Вставка времени в Excel с использованием функций СЕЙЧАС и ВРЕМЯ

Подсчет и суммирование ячеек по цвету (определяемые пользователем функции)

Microsoft Excel не имеет встроенной функции для подсчета и суммирования ячеек по цвету, поэтому мы сделали шаг вперед и создали несколько пользовательских. Эти функции написаны на языке VBA, и вы можете добавлять их на листы через среду программирования Excel — редактор Visual Basic for Applications. Вот наш список пользовательских функций Excel:

  • GetCellColor — возвращает код цвета фона указанной ячейки.
  • GetCellFontColor( — возвращает цветовой код цвета шрифта указанной ячейки.
  • CountCellsByColor) — считает ячейки с заданным цветом фона.
  • CountCellsByFontColor — подсчитывает ячейки с указанным цветом шрифта.
  • SumCellsByColor( — вычисляет сумму ячеек с определенным цветом фона.
  • SumCellsByFontColor( — возвращает сумму ячеек с определенным цветом шрифта.
  • WbkCountCellsByColor — подсчитывает ячейки с указанным цветом фона во всей книге.
  • WbkSumCellsByColor — суммирует ячейки с указанным цветом фона во всей книге.

Вы можете скачать код VBA вышеуказанных функций вместе с подробными инструкциями по их использованию здесь: Суммирование и подсчет ячеек по цвету заливки и шрифта.

Конечно, Microsoft Excel имеет гораздо больше функций, чем перечислено на этой странице. Но мы надеемся, что эти важные функции окажутся полезными в вашей повседневной работе. В любом случае, хорошо иметь некоторые базовые знания для начала, которые вы можете расширить позже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

10 лучших функций для анализа данных в Excel

Электронные таблицы по-прежнему в моде! Использование электронных таблиц восходит к 1979 году и до сих пор остается одним из самых популярных способов просмотра и обработки данных.

Сегодня вездесущая программа Microsoft для работы с электронными таблицами Excel насчитывает более 750 миллионов пользователей и используется в некоторых крупнейших компаниях по всему миру. Я лично использую Excel почти каждый день, потому что иногда это может привести к более быстрым результатам, чем запуск Python или загрузка данных в базу данных.

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

  1. Сводные таблицы и сводные диаграммы
  2. Условное форматирование
  3. Удаление дубликатов
  4. XLOOKUP
  5. ЕСЛИОШИБКА
  6. СЧЕТЧИК
  7. СЧИТАТЬ ПУСТО
  8. ДНИ и ЧИСТРАБДНИ
  9. РАНГ
  10. СУММПРОИЗВ

Вы можете щелкнуть любую функцию, чтобы перейти к ней.

Прежде чем мы начнем…

Чтобы продемонстрировать возможности анализа данных в Excel, нам нужны некоторые данные, с которыми можно поиграться и построить график. В этих примерах я использую набор данных Customer Personality Analysis от Kaggle. Вы можете скачать его, чтобы следовать дальше.

… и, если вы хотите посмотреть вместе…

Вот наш собственный старший научный сотрудник Том, объясняющий эти функции в видео:

1. Сводные таблицы и сводные диаграммы

Одной из моих любимых функций Excel являются сводные диаграммы и, как близкая дополняющая функция, сводные таблицы. Сводные диаграммы визуализируют данные, представленные в сводной таблице, что дает нам представление с первого взгляда. Сводные таблицы обеспечивают простой подход к переформатированию столбцов и строк, преобразованию их в группы, статистику или сводки. Мы можем создать диаграмму вместе с таблицей, используя функцию PivotChart под Insert 9.0012 .

Допустим, мы хотели посчитать записи, сгруппированные по Семейное_Статус . Использование сводной таблицы делает это очень простым, поскольку она преобразует данные и объединяет их для нас.

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

Выбранный диапазон появится в Таблица/диапазон поле по умолчанию. Нажмите OK , и сводная таблица будет сгенерирована.

В полях сводной диаграммы перетащите Семейное положение из списка полей в поле Ось (Категории) . Затем перетащите Marital_Status из списка полей в поле Values ​​. Поскольку данные Marital_Status представляют собой строку, агрегация значений по умолчанию должна иметь значение Count . Если тип данных был числовым, агрегирование по умолчанию равно 9.0011 Сумма .

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

2. Условное форматирование

Если подумать, я использую условное форматирование больше, чем любую другую функцию Excel. Условное форматирование позволяет выделять или скрывать ячейки на основе указанного вами правила. Примените правила к одной или нескольким ячейкам на одном листе. Это полезно для выделения выбросов, дубликатов или закономерностей в данных.

Допустим, мы хотим выделить в наборе данных все значения Year_Birth больше 1987 года.

Выберите столбец Year_Birth и нажмите Условное форматирование > Правила выделения ячеек > Больше . Появится редактор:

Введите 1987 и нажмите OK . Ячейки в столбце со значением больше 1987 станут светло-красными.

Если мы решим, что хотим изменить только что созданное правило условного форматирования, следуйте пути Условное форматирование > Диспетчер правил условного форматирования .

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

3. Удалить дубликаты

Данные часто беспорядочны, поэтому важно знать, как удалять дубликаты. Используя правила условного форматирования, вы можете выделить повторяющиеся данные, чтобы просмотреть их перед удалением. Удаление дубликатов 9Функция 0012 доступна в разделе Данные > Инструменты данных > Удалить дубликаты .

Выделите набор данных в Excel и нажмите Удалить дубликаты . Появится редактор удаления дубликатов:

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

Нажмите OK . Дубликаты будут удалены из набора данных. Он расскажет нам, сколько уникальных значений осталось.

4. XLOOKUP

XLOOKUP похож на комбинацию функций VLOOKUP и HLOOKUP, поскольку он может выполнять поиск значения в диапазоне как по вертикали, так и по горизонтали. По сути, это позволяет вам использовать выбранный диапазон в качестве таблицы поиска и возвращать «просмотренный» результат в ячейку. Синтаксис следующий:

=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])

Допустим, мы хотим найти Year_Birth на основе введенного Значение идентификатора . В ячейку AD2 введите значение идентификатора, например, 8755. В ячейку AE2 введите формулу XLOOKUP:

=XLOOKUP(

хочу искать по функции , поэтому мы вводим AD2 .

Lookup_array — это столбец или строка, которая содержит значение поиска, поэтому мы введем A2: A2241 , так как это даст нам массив идентификаторов.

return_array — это столбец или строка, содержащая значение, которое мы хотим вернуть, поэтому мы выбираем B2:B2241 , так как это даст нам значения Year_Birth .

Завершенная формула будет выглядеть следующим образом: =XLOOKUP(AD2, A2:A2241, B2:B2241)

Введите разные идентификаторы, и будет возвращен соответствующий Year_Birth .

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

5. ЕСЛИОШИБКА

Функция ЕСЛИОШИБКА используется для создания пользовательского сообщения об ошибке, когда формула приводит к ошибке. Например, мы можем использовать его, чтобы обернуть нашу функцию XLOOKUP, чтобы она возвращала четкое сообщение, если идентификатор не найден. Синтаксис относительно прост.

=ЕСЛИОШИБКА(значение, значение_если_ошибка)

Возвращаясь к функции XLOOKUP, если мы введем идентификатор в AD2 , который не существует в массиве поиска, AE2 выводит #Н/Д . Вместо этого давайте вернем «ID не найден». Для значения в функции ЕСЛИОШИБКА используйте функцию XLOOKUP. Это должно выглядеть так:

=ЕСЛИОШИБКА(XLOOKUP(AD2, A2:A2241, B2:B2241), «ID не найден»)

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

6. МАТЧ

Подобно функциям поиска, ПОИСКПОЗ можно использовать, когда вам нужна позиция значения в диапазоне вместо самого значения.

Это синтаксис для ПОИСКПОЗ:

=ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_сопоставления])

При написании функции важно знать, какой тип соответствия использовать. Хотя это необязательно и по умолчанию равно 1, доступны следующие варианты: -1, 0 или 1.

  • -1: находит наименьшее значение, большее или равное 9.0011 искомое_значение .
  • 0: Находит первое значение, точно равное искомое_значение .
  • 1: Находит наибольшее значение, которое меньше или равно искомое_значение .

Если мы хотим найти первый раз, когда указанный год рождения встречается, добавьте следующую формулу ПОИСКПОЗ в ячейку AF2 :

=ПОИСКПОЗ(AE2,B:B, 0)

7 СЧИТАТЬ ПУСТО

Функция СЧИТАТЬПУСТОТЫ довольно интуитивно понятна, но она важна для обработки данных в аналитике, поскольку многие алгоритмы машинного обучения чувствительны к нулям. Зная, сколько значений являются нулевыми, вы лучше понимаете, как к ним подходить. Например, если многие значения равны нулю, вы должны удалить столбец. Если несколько значений равны нулю, вы должны ввести значение, чтобы заполнить нулевое значение. СЧИТАТЬПУСТОТЫ подсчитывает количество пустых ячеек в диапазоне. Синтаксис прост:

=СЧЕТПУСТОТЫ(диапазон)

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

=СЧЕТПУСТОТЫ(E2: E2241)

8.

ДНИ и ЧИСТЫЕ ДНИ

ДНИ и ЧИСТРАБДНИ — это отдельные функции, но они достаточно похожи, чтобы не требовать отдельных записей в моем списке. Функция ДНИ просто возвращает количество дней между двумя датами, тогда как функция ЧИСТРАБДНИ немного отличается тем, что исключает выходные и указанные праздники. Он возвращает только количество рабочих дней между двумя датами. Синтаксис обоих легко запомнить:

=ДНИ(дата_начала, дата_окончания)

=ЧИСТРАБДНИ(дата_начала, дата_окончания, [праздники])

На моей работе я анализирую данные об использовании. Я использую эти функции для таких вещей, как подсчет количества дней с тех пор, как пользователь вошел в систему или использовал инструмент. Поскольку наше программное обеспечение используется в школах, иногда имеет смысл исключить выходные и праздничные дни из наших данных об использовании, поэтому я благодарен за функцию ЧИСТРАБДНИ.

9. РАНГ

Функция РАНГ упорядочивает число по его размеру относительно других значений в списке и возвращает желаемый ранг. Это означает, что ранг числа будет его позицией, если список будет отсортирован по возрастанию или по убыванию. Например, отсортируйте доход в порядке убывания, чтобы наибольшее значение находилось вверху и могло иметь ранг 1. РАНГ присваивает повторяющимся числам один и тот же ранг, но учитывается кумулятивно. Это означает, что если два значения имеют ранг четыре, следующий ранг будет шести, а не пяти (1,2,3,4,4,6). Синтаксис RANK принимает 3 аргумента.

=РАНГ(число, ссылка, [порядок])

Важно отметить, что [порядок] может быть установлен на 0 для убывания и 1 (или больше) для возрастания.

Мы хотим ранжировать доход (столбец E) наших клиентов, поэтому мы будем использовать следующую формулу в ячейке Ah3 :

=РАНГ(E2, E2:E2241, 0)

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

10. СУММПРОИЗВ

Последняя функция в моем списке 10 лучших — СУММПРОИЗВ . Это здорово, когда вам нужно выполнить арифметические действия для определенных групп значений. Его можно использовать для сложения, вычитания, умножения или деления выбранных числовых значений для соответствующих диапазонов. Хотя это звучит сложно, логика довольно интуитивно понятна, как только вы попробуете. Синтаксис, однако, не очень интуитивно понятен.

=СУММПРОИЗВ(массив1, [массив2], [массив3], …)

Мы хотим просуммировать KidHome для всех строк, где Образование равно Выпускной (C2) и Семейное положение равно Холост (D2) 90 012 . Мы добавим эту формулу в ячейку AI:

=СУММПРОИЗВ((C2:C2241=C2)*(D2:D2241=D2)*(F2:F2241))

Если бы мы хотели увидеть другое Значение образования, например, PhD, мы можем изменить C2 на C6 в формуле.

Резюме и последующие шаги

Это ни в коем случае не исчерпывающий список всех возможностей и функций, предлагаемых Excel, но эти 10 функций помогают мне анализировать и очищать данные без хлопот, связанных с загрузкой Python или загрузкой данных на сервер SQL.