Встроенные функции. Логические функции. Статистические функции.

 

Excel содержит большой набор встроенных функций, которые можно использовать в формулах. Помимо обычных функций, таких как СУММ или СРЗНАЧ, в этот набор включены функции для выполнения более сложных операций, которые трудно или невозможно выполнить другим способом. Например, функция КОРРЕЛ вычисляет коэффициент корреляции между двумя наборами данных. С помощью макроязыка VBA вы можете разработать свои функции (это не так трудно, как кажется).

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

 

 

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

Название встроенной функции можно ввести с клавиатуры (что крайне нежелательно ввиду

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

в группе Библиотека функций на вкладке Формулы, или же из окна Мастера функций. О двух последних вариантах будет рассказано в разделе «Построение графиков и диаграмм».

Часто применяемые на практике функции вынесены в меню кнопки    , которая находится

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

 

Простейшие расчеты

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

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

 

Среднее — вызывает функцию =СРЗНАЧ(), с помощью которой можно подсчитать арифметическое среднее диапазона ячеек (просуммировать все данные, а затем разделить на их количество.

 

 

Число — вызывает функцию =СЧЕТ(), которая определяет количество ячеек в выделенном

диапазоне.

Максимум — вызывает функцию =МАКС(), с помощью которой можно определить самое

большое число в выделенном диапазоне.

 

 

Минимум — вызывает функцию =МИН() для поиска самого маленького значения в выделенном

диапазоне.

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

на строку состояния Excel. Слева от регулятора масштаба появятся значения суммы,

количества ячеек в диапазоне и среднего арифметического (рис. 19.9).

 

Комплексные расчеты

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

Задача 1.

Выбрать оптимальный тарифный план при подключении к сети сотовой связи, если в месяц планируется 2,5 часа разговоров внутри сети и 0,5 часа разговоров 12 с абонентами городской сети и других сотовых операторов. Цены на услуги представлены в таблице на рис. без учета НДС.

 

 

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

 

 

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

 

= Равно

> Больше

< Меньше

>= Больше или равно

<= Меньше или равно

<> Не равно

 

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

 

Функция ЕСЛИ

Функция ЕСЛИ (IF) имеет следующий синтаксис:

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

 

 

Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае - 20:

=ЕСЛИ(А1>3;10;20)

 

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:

=ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал")

 

 

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

Например:

=ЕСЛИ(СУММ(А1:А3)=30;А10;"")

 

 

Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например:

=ЕСЛИ(А1="Динамо";10;290)

 

Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

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

Функции И (AND), ИЛИ (OR), НЕ (NOT) - позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

 

 

=И(логическое_значение1;логическое_значение2...)

 =ИЛИ(логическое_значение1;логическое_значение2...)

 

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

 

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

 

Приведем пример. Пусть Excel возвращает текст "Прошел", если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:

=ЕСЛИ(И(А2>4;А3<3);"Прошел";"Не прошел")

 

 

Не смотря на то, что функция ИЛИ имеет те же аргументы, что и И, результаты получаются совершенно различными. Так, если в предыдущей формуле заменить функцию И на ИЛИ, то ученик будет проходить, если выполняется хотя бы одно из условий (средний балл более 4 или пропуски занятий менее 3). Таким образом, функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одно из логических выражений истинно, а функция И возвращает логическое значение ИСТИНА, только если все логические выражения истинны.

 

Функция НЕ меняет значение своего аргумента на противоположное логическое значение и обычно используется в сочетании с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значение ИСТИНА.

Вложенные функции ЕСЛИ

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

 

=ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Никогда")))

 

Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7 уровней вложения функций ЕСЛИ.

 

Функции ИСТИНА и ЛОЖЬ

Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:

=ИСТИНА()

 =ЛОЖЬ()

 

 

Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение "Проходите", если выражение в ячейке А1 имеет значение ИСТИНА:

 

=ЕСЛИ(А1=ИСТИНА();"Проходите";"Стоп")

 

В противном случае формула возвратит "Стоп".

 

Функция ЕПУСТО

Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:

 

=ЕПУСТО(значение)

 

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

 

3. Фильтрация (выборка) данных из списка. Сортировка данных.

 

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

 

Как правило, список состоит из записей (строк) и полей (столбцов). Столбцы должны содержать однотипные данные. Список не должен содержать пустых строк или столбцов. Если в списке присутствуют заголовки, то они должны быть отформатированы другим образом, нежели остальные элементы списка.

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

Сделайте небольшой список для тренировки.

Выделите его.

Нажмите кнопку " Сортировка и фильтр " на панели " Редактирование " ленты " Главная ".

 

Выберите " Сортировка от А до Я ". Наш список будет отсортирован по первому столбцу, т.е. по полю ФИО.

 

Если надо отсортировать список по нескольким полям, то для этого предназначен пункт " Настраиваемая сортировка..".

 

Сложная сортировка подразумевает упорядочение данных по нескольким полям. Добавлять поля можно при помощи кнопки " Добавить уровень ".

 

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

 

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

Перемещать уровни сортировки можно при помощи кнопок " Вверх " и " Вниз ".

Не следует забывать и о контекстном меню. Из него, также, можно настроить сортировку списка. К тому же есть такие интересные варианты сортировки, связанные с выделением того или иного элемента таблицы.

 

 

 

Фильтрация списков

Основное отличие фильтра от упорядочивания - это то, что во время фильтрации записи, не удовлетворяющие условиям отбора, временно скрываются (но не удаляются), в то время, как при сортировке показываются все записи списка, меняется лишь их порядок.

Фильтры бывают двух типов: обычный фильтр (его еще называют автофильтр) и расширенный фильтр.

Для применения автофильтра нажмите ту же кнопку, что и при сортировке - " Сортировка и фильтр " и выберите пункт " Фильтр " (конечно же, перед этим должен быть выделен диапазон ячеек).

 

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

 

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

 

Для формирования более сложных условий отбора предназначен пункт " Текстовые фильтры " или " Числовые фильтры ". В окне " Пользовательский автофильтр " необходимо настроить окончательные условия фильтрации.

 

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

 

Для этого надо сделать следующее.

· Скопируйте и вставьте на свободное место шапку списка.

· В соответствующем поле (полях) задайте критерии фильтрации.

 

 

Выделите основной список.

 

Нажмите кнопку " Фильтр " на панели " Сортировка и фильтр " ленты " Данные ".

На той же панели нажмите кнопку " Дополнительно ".

 

В появившемся окне " Расширенный фильтр " задайте необходимые диапазоны ячеек.

 

В результате отфильтрованные данные появятся в новом списке.

 

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

 

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

 

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

 

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

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