Глава 4 (О.Б. Калугина, В.С. Люцарев - Работа с электронными таблицами. Microsoft Office Excel 2003)

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

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

Если задача пользователя полностью решается встроенной функцией, достаточно подготовить в ячейке формулу,состоящую только из вызова соответствующей функции. В других случаях расчетная формула может включать в себя сразу несколько функций вкачестве операндов вычисляемого выражения. Вызовы функций в формуле допускается вкладывать друг в друга – аргумент «внешней» функцииявляется результатом обращения к другой, «вложенной» функции.Уровень сложности формул рабочего листа определяется как постановкой конкретной вычислительной задачи, так и навыками и умениемпользователя грамотно подойти к решению задачи. Постараемся помочьчитателю понять и успешно применять на практике этот мощный инструмент Excel.Мастер функцийСоразмерно назначению, специфике аргументов, результата и т.п,функции Microsoft Excel сгруппированы по различным категориям, таким как Математические, Статистические, Дата и время, Текстовые и другие.

Но, несмотря на все многообразие, есть общие черты в синтаксисеи организации вызова встроенных функций Microsoft Excel.=СЕГОДНЯ()=ПИ()НазначениеВозвращает текущую дату в формате Дата.Возвращает значение числа π.Даже если точно известно имя функции, количество и порядок следования ее аргументов, лучше не заниматься «ручным набором» – достаточно одного неверно введенного символа, и формула будет составленанеправильно.

Для вставки в формулу функции советуем обращаться кМастеру функций Microsoft Excel.Если формула в текущей ячейке должна содержать вызов функции,достаточно щелкнуть по кнопкеВставка функции в строке формул(что равноценно команде Вставка Функция…или клавишной комбинации Shift + F3) – на экране появится диалоговое окно первого шагаМастера функций, в котором приведены списки функций, сгруппированных по разным категориям.Если затруднительно самостоятельно определить категорию и имяфункции, в этом окне также можно воспользоваться поиском нужнойфункции, введя в поле Поиск функции: краткое описание действия, которое нужно выполнить.Выбрав категорию и указав название функции подсветкой, ознакомьтесь с синтаксисом и назначением функции.60Глава 4Для более полной информации предусмотрена гиперссылка Справка по этой функции – щелчком по этой ссылке вызывается соответствующий раздел справки в отдельном окне Справка Microsoft Excel.Когда выбор имени функции в окне первого шага мастера сделан,щелчок по кнопке ОК предложит окно Аргументы функции.

Спецификуработы с этим окном рассмотрим на конкретном примере. Предположим, что в приведенной на рисунке ниже таблице в ячейке B13 нужнонайти среднее арифметическое чисел первого столбца этой таблицы.Имя функции уже выбрано (СРЗНАЧ), как задать ее аргументы?В качестве аргумента функцииможно задать диапазон ячеекОрганизация вычислений в рабочих книгах61можно указать как константу, так и ссылку на ячейку или диапазон ячеек со значениями предусмотренного типа. «От 1 до 30 аргументов» означает возможность задания значений не более чем в 30-ти однотипныхполях этого диалогового окна. Конечно, в диалоговом окне видны далеко не тридцать полей для задания аргументов. По мере заполнения дополнительные поля аргументов добавляются автоматически либо нажатием клавиши Tab при активном поле «последнего» аргумента.Когда на листе рядом с ячейкой, в которой составляется формула,имеются данные, подходящие по типу в качестве аргумента функции, топосле указания имени функции ссылки на соответствующие ячейки сданными подставляются автоматически.

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

Аргументы функции указываются в круглыхскобках после имени функции и отделяются друг от друга точкой с запятой. Так, например, функция =СРЗНАЧ(B3:B7;B8:B12) имеет два аргумента (диапазоны ячеек B3:B7 и B8:B12).Ошибки в формулахМожно оценитьтекущий результатНазвание полей (Число1, Число2) подсказывает тип данных аргумента. Жирное начертание названия поля свидетельствует об обязательности заполнении этого поля для правильной организации вызовафункции.

В большинстве случаев (в т.ч. и в этом примере) в каждом полеСамый «простой» тип ошибки в ячейке рабочего листа уже был рассмотрен нами. Напомним, что если в ячейке отображаются символы#####, то, скорее всего, текущей ширины столбца просто не хватаетдля корректного отображения числового значения в ячейке. В этом случае достаточно отрегулировать ширину столбца. Но далеко не всегдавозникшая проблема решается так быстро, особенно в случае вычислений по формулам.Если по заданной формуле невозможно произвести корректныевычисления, в ячейке указывается сообщение об ошибке. В зависимости от причины ошибки диагностика может быть различной.62Значениеошибки#####Глава 4Когда возникаетПримеры и поясненияДля отображениязначения не хватаетширины столбца.Формат Дата ивремя назначенотрицательномучислу.#ЗНАЧ!#ДЕЛ/0!Недопустимыйоперанд формулыили аргументфункции.В ячейке А1 – текст, а не число (десятичный разделитель задан неверно).Деление числа наноль.Отсутствие данных во влияющей ячейкеравноценно нулевому значению.#ИМЯ!Организация вычислений в рабочих книгах#ПУСТО!63Задано пересечениедвух областей, которыене имеют общих ячеек.Оператором пересечения областей являетсяпробел между ссылками.

Вместо двоеточия в указании ссылки нанепрерывный диапазон ячеек стоит пробел.В левом верхнем углу ячейки с ошибкой индицируется зеленый треугольный маркер. Когда эта ячейка активна,рядом с ней появляется смарт-тег, раскрывающееся менюкоторого содержит возможности, позволяющие понять иисправить допущенную ошибку.Последовательно рассмотрим строки этого меню.В первой указан тип ошибки, во второй – возможность открыть справку по этой ошибке.Команда Показать этапы вычисления… откроетдиалоговое окно Вычисление формулы, котороепоможет, вычисляя формулу по шагам, найтипричину ошибки.Невозможно распознать используемоев формуле имя.Имя функции задано неправильно.#Н/Д!Нет необходимыхданных.Отсутствует значение одного из аргументов функции.#ССЫЛКА!Неправильноуказана ссылкана ячейку.На листе была удалена ячейка, на которуюссылается формула.#ЧИСЛО!Задано неправильноечисловое значение.Недопустимое числовое значение аргумента функции.Щелчком по кнопке Вычислить вместо подчеркнутой в формулессылки будет подставлено вычисленное значение.

Если в ячейке с подчеркнутой ссылкой, в свою очередь, тоже находится формула, кнопкаШаг с заходом даст возможность по шагам вычислить и ее.64Глава 4Организация вычислений в рабочих книгах65Если был задействован Шаг с заходом, то Шаг с выходом вернет вычисления на предыдущий уровень исходной формулы.Так можно последовательно выполнять все действия, предписанные формулой.

На последнем шаге вместо кнопки Вычислить появитсякнопка Заново, которая позволит выполнить вычисления по формулеповторно. Кнопка Закрыть завершает работу с диалоговым окном Вычисление формулы.Вернемся к меню смарт-тега. Такие его строки, как Пропуститьошибку и Исправить в строке формул, однозначно говорят о своем назначении.Выбор строки Параметры проверки ошибок приведет к появлениюдиалогового окна Параметры с вкладкой Проверка ошибок.Кнопка Показать этапы вычисления… предложит окно Вычислениеформулы, которое уже было рассмотрено в предыдущем параграфе.Кстати, для вызова окнаВычисление формулы в панелиинструментов Зависимостипредусмотрена и отдельнаякнопка.С помощью инструментов панели Зависимости можно отобразитьстрелками влияющие и зависимые ячейки.

Щелчок по кнопкеукажет все ячейки, влияющие на активную,Это диалоговое окно позволяет снять или добавить параметры,предусмотренные для проверки ошибок в Microsoft Excel. Щелчок покнопке в правом верхнем углу диалогового окна откроет справку для более подробного знакомства с ними.И, наконец, последняя строка меню смарт-тега Показать панельаудита формул вызовет панель инструментов Зависимости, в которой собраны сразу все средства, позволяющие проследить связи между влияющими и зависимыми ячейками, проверить наличие ошибок и выявитьих источник.Панель инструментов ЗависимостиКнопкапроверяет наличие ошибок на рабочем листе.