9.Использование стандартных функций.

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

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

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

После этого открывается диалоговое окно со списком категорий и списками конкретных функций в каждой категории.

Каждая стандартная функция имеет уникальное имя, после которого в круглых скобках в большинстве случаев задается список параметров, т.е. условных обозначений входных данных и иногда - выходных (результирующих) данных. Параметры отделяются друг от друга точкой с запятой. Чаще всего параметрами являются адреса ячеек. Если эти ячейки образуют непрерывную последовательность, их можно задать как диапазон, используя символ двоеточия (см. раздел 1.2).

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

• Указание мышью ячейки, где надо получить результат работы стандартной функции

• Выбор необходимой функции

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

Наиболее часто на практике используются функции вычисления сумм значений и нахождение средних значений. В силу важности первой из них для нее в панели Стандартная предусмотрена отдельная кнопка с изображением математического символа суммы S (греческая буква "сигма"). Сама функция имеет имя СУММ. Примеры задания функции суммирования:

• СУММ(A1; A5; A9) - суммирование трех чисел в ячейках A1, A5 и A9

• СУММ(B3:B9) - суммирование значений в ячейках столбца В и в строках с 3-ей по 9-ю

• СУММ(A2:D2; P2:Z2; BB2:CC2) - суммирование значений в ячейках второй строки, находящихся в трех диапазонах (столбцы с A по К, с Р по Z и с BB по CC)

• СУММ(В2:В100; D2:D100; F2:F100) - суммирование значений в строках со 2-ой по 100-ю в трех НЕ соседних столбцах B, D и F

Особенностью последних двух примерах является то, что исходные данные НЕ ОБРАЗУЮТ единый непрерывный блок ячеек, а разбросаны по нескольким диапазонам. Конечно, можно задавать границы этих диапазонов с клавиатуры, но удобнее сделать это с помощью мыши. Для этого надо при нажатой клавише Ctrl последовательно указать все необходимые диапазоны и после этого отпустить клавишу Ctrl. Все границы автоматически будут подставлены в стандартную функцию.

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

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

ЕСЛИ, имеющая три параметра.

Первый параметр - это логическое условие, т.е. выражение, которое может быть либо истинным, либо ложным. Например, условие 5 > 2 всегда истинно, условие 7 = 10 всегда ложно, а условие B3<>0 (где символы <> обозначают операцию "не равно") может быть истинным или ложным в зависимости от значения в ячейке B3. Логические выражения могут включать числовые константы, адреса ячеек и операции отношений (>, >=, <, <=, =, <>).

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

Третий параметр - это выражение, которое выполняется, если заданное условие ложно.

Примеры записи логической функции ЕСЛИ:

• ЕСЛИ(B5 <> 0; A3+2; 0)

• ЕСЛИ((A3+D3)/C3 > E3; A3^2; A3^3)

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

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

• Выражение И(A3=0; B2 > 0; 3 <> 2) будет истинным только если в ячейке A3 находится 0, а в ячейке B2 - положительное число

• Выражение И(5 > 0; 2 = 3; 1 < 5) всегда будет ложным, т.к. второе условие всегда ложно

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

Например:

• Выражение ИЛИ(5 > 0; 2 = 3; 1 < 5) всегда будет истинным, т.к. первое условие (да и третье тоже, что уже не существенно) всегда истинно · Выражение ИЛИ(7 > 10; 4 = 0) всегда ложно, т.к. ложны оба его условия

Функции И и ИЛИ очень часто используются в первом параметре функции ЕСЛИ. Например:

• Выражение ЕСЛИ(И(A1>0; B2<0); 1; 0) заносит в ячейку значение 1 только тогда, когда выполнены сразу оба условия, а если хотя бы одно из них не выполняется, в ячейку заносится 0

• Выражение ЕСЛИ(ИЛИ(A1>0; B2<0); 1; 0) заносит в ячейку значение 1, когда выполнено хотя бы одно их двух условий; значение 0 будет занесено только тогда, когда сразу оба условия не выполняются

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

Сортировка базы данных

Поиск данных и сортировка списков являются наиболее частыми задачами при работе с Microsoft Excel.

Теперь возможен еще более быстрый поиск необходимых данных и подсчет итоговых значений.

Возможности Описание

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

• Сортировка с использованием заголовков столбцов При сортировке списка можно задавать порядок сортировки с использованием заголовков столбцов списка.

• Задание порядка сортировки Порядок сортировки можно задавать с помощью дополнительного списка. Это полезно для сортировки в специальном порядке, отличном от алфавитного. Например, если задать список из значений "Большое", "Среднее" и "Малое", данные могут быть отсортированы так, чтобы вначале шли значения "Большое", затем "Среднее" и "Малое" -- в конце.

• Сводные таблицы Сводные таблицы позволяют интерактивно вычислять итоговые значения для больших объемов данных. Сводные таблицы введены вместо перекрестных таблиц Microsoft Excel версии. Перекрестные таблицы могут быть просто преобразованы в сводные таблицы.

• Автоматические итоговые и частичные суммы Автоматическое добавление промежуточных и итоговых значений в списки.

• Автоввод При вводе текста в ячейку производится его сравнение с текстом других ячеек данного столбца и предлагается закончить ввод на основе уже введенного текста.

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