Краткий обзор Microsoft Office 2003
Выполнение расчетов по формулам в Excel 2003
В этой главе вы познакомитесь со следующими вопросами, связанными с выполнением расчетов по формулам:
ввод формулы в ячейку; использование в формуле ссылок и функций; копирование формул; настройка Excel на выполнение вычислений.
Использование формул в Excel
Excel содержит мощные средства вычислений по формулам. Формула позволяет определить значения в заданной ячейке листа.
В качестве операндов могут использоваться постоянные значения (числовые или текстовые константы), ссылки на ячейки или диапазоны ячеек, заголовки, имена, функции, логические величины (например, ИСТИНА или ЛОЖЬ) и массивы. Константой считается число или текст, которые непосредственно вводятся в ячейку, например, текст «Московские известия».
Формула также может включать встроенные функции, которые обеспечивают выполнение стандартных вычислительных операций. В Excel используется более 200 встроенных функций.
Для обращения к ячейке введите ее адрес в формуле. Например, если ввести в ячейкуА2 формулу =СЗ, то после пересчета текущего листа Excel в ячейке А2 покажет текущее значение СЗ.
Операторы, используемые в Microsoft Excel
В Excel используют следующие операторы:
Арифметические операторы — применяются при работе с числами. Результатом выполнения арифметической операции всегда является число. Операторы сравнения — используются для сравнения двух чисел. В результате выполнения операции сравнения получается логическое значение: истина или ложь. Текстовый оператор — применяется для обозначения операции объединения нескольких последовательностей символов в одну последовательность символов. Адресные операторы — используются при ссылках на ячейки. Назначение операторов приведено в таблице 16.1.
Таблица 16.1. Назначение используемых в Excel операторов
| Арифметические операторы | Назначение операторов |
| + (знак плюс) | Сложение |
| - (знак минус) | Вычитание (или унарный минус, например, —1) |
| / (косая черта) | Деление |
| * (звездочка) | Умножение |
| % (знак процента) | Процент |
| А (крышка) | Возведение в степень |
| Операторы сравнения | |
| = (знак равенства) | Равно |
| > (знак больше) | Больше |
| < (знак меньше) | Меньше |
| >= (знак больше и знак равенства) | Больше или равно |
| <= (знак меньше и знак равенства) | Меньше или равно |
| <> (знак меньше и знак больше) | Не равно |
| Текстовый оператор | |
| & (амперсанд) | Объединение двух текстовых строк в одну |
| Адресные операторы | |
| : (двоеточие) | Ссылка на все ячейки между границами диапазона включительно. |
| , (запятая) | Ссылка на объединение ячеек диапазонов. |
| (пробел) | Ссылка на общие ячейки диапазонов. |
Арифметические операторы
Арифметическими операторы получили наиболее широкое распространение. Они обеспечивают сложение, вычитание, умножение, деление, возведение в степень, нахождение процента по данным, приведенным в ячейках электронной таблицы. Примеры использования арифметических операторов приведены в таблице 16.2. Предполагается, что в ячейках Al, A2 и A3 содержатся числа 4, 5 и 10 соответственно.
Таблица 16.2. Примеры использования арифметических операторов
| Оператор | Пример формулы | Результат |
| + | А1+А2 | 9 |
| - | А2-А1 | 1 |
| * | А1*А2 | 20 |
| / | АЗ/А2 | 2 |
| л | АГ2 | 16 |
| % | 2Л3 | 8 |
Excel выполняет вычисления в формулах слева направо и соблюдает принятый в математике приоритет выполнения арифметических операций. Первыми выполняются операции возведения в степень, затем умножение и деление, в последнюю очередь сложение и вычитание, для изменения порядка выполнения операций используются скобки.
Скобки должны быть парными, пробелы перед скобками или после них не допускаются, например:
=(АЗ+15)/В4,
где A3, В4 — ссылки на ячейки, 15 — числовая константа, «+» и «/» — операторы сложения и деления.
Операции в скобках выполняются первыми. Например, в формуле =(А1+А2+АЗ)/3 сначала вычисляется сумма чисел, содержащихся в ячейках Al, A2 и A3, потом найденная сумма делится на 3, в то время как в формуле =А1+А2+АЗ/3 на 3 делится только последнее слагаемое, а не вся сумма. Внутри скобок можно помещать другие скобки, что называется вложением скобок.
Ввод формулы в ячейку
Для ввода формулы в ячейку выделите ее и введите в строку формул или в ячейку знак «=».
При переходе в режим ввода формул поле Имя (Name), расположенное в левой
части строки формул, заменяется кнопкой, на которой отображена последняя использовавшаяся функция. Расположенная справа от нее кнопка со стрелкой открывает список, который содержит 10 последних использовавшихся функций и пункт Другие функции (More/unctions) (рис. 16.1).
Рис. 16.1
Окно Excel в режиме ввода формул
В качестве примера введем формулу в ячейку D4. Закончив ввод формулы, нажмем клавишу Enter или щелкнем в строке формул по кнопке Ввод (Enter). В ячейке, содержащей формулу, отобразится результат вычисления, сама формула будет видна в строке формул (рис. 16.2).
Рис. 16.2
Отображение расчетной формулы в строке формул
Использование ссыпок в формуле
Формула может содержать ссылку на ячейку (ее адрес) или на диапазон ячеек, а также на имена, представляющие ячейки или диапазоны ячеек. Для описания ссылок на диапазоны ячеек используются операторы, приведенные в таблице 16.3.
Таблица 16.3. Операторы ссылки
| Операторы ссылки | Значение (пример) |
| : (двоеточие) | Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссыпкой на диапазон (В5:В15). |
| ; (точка с запятой) | Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ(В5:В15;О5:В15)). |
| (пробел) | Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 С6:С8). |
Чтобы сослаться на диапазон ячеек, введите ссылку на верхнюю левую ячейку диапазона, поставьте двоеточие (:), а затем — ссылку на правый нижний угол диапазона. Например, А2:С5. Ссылка на все ячейки между 6-й и 15-й строками включительно имеет вид 6:15, на все ячейки в столбце С — С:С.
Для задания обращения ко всем ячейкам, находящимся в нескольких диапазонах одновременно используется пересечение. Для задания пересечения диапазонов используется пробел. Например, формула =СУММ(А2:ВЗ В2:СЗ) вычисляет сумму чисел в пересекающихся диапазонах А2:ВЗ и В2:СЗ. Для обращения ко всем ячейкам, включенным в два диапазона, используется объединение. Если в ячейке ВЗ записано Петров, а ячейке A3 — Владимир, то формула АЗ=ВЗ&",
"&АЗ будет означать объединение указанных выше имен, разделенных запятой (Петров, Владимир). При использовании операторов объединения и пересечения удобно пользоваться именованными диапазонами.
В формулу можно вводить ссылки на ячейки различных рабочих листов и книг. Ссылки, распространяющиеся на несколько рабочих листов, называются трехмерными. Ячейка, содержащая формулу, называется зависимой ячейкой. Ее значение зависит от значений ячеек, на которые имеются ссылки в формуле.
Примеры записей диапазонов ячеек в функции:
=СУММ(Е:Е) — определяется сумма числовых значений, содержащихся в столбце Е; =СУММ(Е2:С5) — подсчитывается сумма числовых значений, расположенных в диапазоне от Е2 до G5; =СУММ(5:5) — суммируются данные всех ячеек пятой строки; =СУММ(2;4) — подсчитывается сумма 2+4.
Допускается смешанная запись адресов ячеек и блоков ячеек. В этом случае формула может выглядеть следующим образом:
=СУММ(С8;О4;Е2:Е5;Р5)
Ввод в формулу ссылок на ячейки с помощью мыши
Так, если вам необходимо после знака = в формуле дать ссылку на ячейку А4, щелкните на этой ячейке мышью, вокруг ячейки появится бегущая рамка, а в формуле отобразится ссылка на эту ячейку. Для ввода ссылки на диапазон ячеек щелкните по угловой ячейке диапазона и, удерживая нажатой кнопку мыши, перетащите указатель в противоположный угол для выделения всего диапазона.
Отображение формулы в ячейке
При стандартной настройке в ячейке с формулой отображается результат вычислений, а не сама формула. В некоторых случаях, например, при составлении и проверки сложных расчетов в ячейке удобнее отображать не числовое значение, а саму формулу.
Для возврата к принятому режиму отображения формул снимите флажок. . Чтобы перейти в режим отображения формул или вернуться в обычный режим нажмите клавиши Ctrl+' (клавиша обратного апострофа находится на той же клавише, что и тильда «~» — ниже клавиши Esc).
Подсказки аргументов функций
При создании новой формулы в Excel отображается информация об аргументах функции. Кроме того, всплывающие подсказки обеспечивают быстрый доступ к нужным разделам справки. Пользователю достаточно щелкнуть мышью имя любой функции или аргумента, отображаемых во всплывающей подсказке.
Абсолютная и относительная ссылка
Ссылка — это указание адреса ячейки. Различают относительные или абсолютные ссылки.
Например, нам надо подсчитать сумму чисел, хранящихся в ячейках Al, A2, A3. Щелкнем дважды ячейку А5, и поместим в нее формулу=А1+А2+АЗ. Чтобы вычислить сумму чисел, хранящихся в ячейках Cl, C2, СЗ, можно не писать заново формулу, а скопировать ее из ячейки А5 в G5 (см. главу 14, раздел «Копирование и перемещение содержимого ячеек»). Excel автоматически изменит относительные ссылки и формула примет вид =С1+С2+СЗ. Измененную формулу можно увидеть, щелкнув ячейку С5. (Для отображения формул в окне приложения установите флажок формулы на вкладке Вид диалогового окна Параметры (рис. 19.8).)
Например, если скопировать формулу из ячейки А7 с абсолютными ссылками в ячейку С7, то формула не изменится. Для указания абсолютной ссылки используется знак доллара $. $А$7, $С$7 (рис. 16.3). Адреса ячеек, использованные в формуле, и границы этих ячеек будут закрашены в одинаковые цвета.
Рис. 16.3
Отображение формул с абсолютными, и относительными ссылками в окне Excel
В тех случаях, когда при копировании или перемещении формулы необходимо сохранить неизменным только номер строки или только наименование столбца, применяют смешанную ссылку, например $D7 или F$5. Более подробно использование абсолютной ссылки в формуле рассмотрено ниже в разделе «Копирование формулы».
Внешние и умаленные ссылки
Можно ссылаться на ячейки, находящиеся на других листах книги или в другой книге, или на данные другого приложения. Ссылки на ячейки других книг называются внешними ссылками. Ссылки на данные других приложений называются удаленными ссылками.
Смарт-тег проверки ошибок
0 возможной ошибке в ячейке пользователя оповещает смарт-тег проверки ошибок. Пользователю предлагается исправить ошибку, пропустить ее или получить доступ к дополнительным параметрам средства проверки ошибок. Попробуйте вычислить сумму по столбцу, пропустив последнюю ячейку группы. Наведите указатель на ячейку, содержащую сумму, чтобы отобразились варианты, предлагаемые функцией проверки ошибок.
Панель аудита формул
Панель аудита формул предлагает ряд элементов управления для доступа к функциям аудита, таким как средство проверки формул и окно контрольного значения. Данная функция позволяет выполнить вычисление по шагам и понять, каким образом получается итоговый результат. Она также предоставляет некоторые дополнительные возможности, например переключение в режим отображения всех формул на листе.
Для проверки вычислений в формулах выделите ячейку, которую необходимо проверить. Откройте панель аудита формул и нажмите кнопку Вычислить формулу ().
Различные способы суммирования данных
В большинстве случаев Excel предусматривает несколько вариантов выполнения расчетов. Покажем это на примере суммирования данных. Для сложения чисел в диапазоне ячеек используется функция СУММ, которая является самой часто используемой функцией.
Введем слагаемые в ячейки D3, D4, D5. Суммирование можно выполнить следующими способами:
1 способ.
Выделим ячейку D6 и введем в нее формулу для расчета, сделав ссылку на ячейки, содержащие исходные данные: -D3+D4+D5. Нажмем клавишу Enter, и в ячейке D6 появится результат.
2 способ.
Введем знак «=» в ячейку D6, щелкнем ячейку D3 — после знака равенства в ячейке D6 отобразится адрес ячейки D3. Поставим знак «+» и щелкнем ячейку D4. В ячейке D6 появится запись «=D3+D4». Введем знак «+», щелкнем ячейку D5 и нажмем клавишу Enter. В ячейке D6 появится результат.
3 способ.
Щелкнем ячейку D6 и нажмем кнопку Автосумма (AutoSum) на панели инструментов Стандартная. В ячейке появится запись =СУММ(ВЗ:О5), т.е. предлагается провести суммирование данных, записанных в ячейках от D3 до D5, расположенных в одном столбце с D6. Нажмем клавишу Enter и мы увидим результат суммирования.
При выделении диапазона ячеек и нажатии кнопки Автосумма (AutoSum) в пустую ячейку, следующую за диапазоном, будет вставлена формула подсчета суммы этих ячеек. Эта возможность также может быть использована для подсчета общей суммы по столбцу, содержащему частичные суммы данного столбца.
Работа с формулами с клавиатуры
При работе с формулами с клавиатуры используют клавиши, приведенные в таблице 16.3.
Таблица 16.4 Клавиши, используемые при работе с формулами с клавиатуры
| Выполняемая операция | Клавши |
| Начать формулу | Знак равенства |
| Скопировать формулу (значение) верхней ячейки в текущую ячейку или в строку формул | Ctrl+' (апостроф) |
| Выполнить автосуммирование | Alt+= (знак равенства) |
| Пересчитать текущий лист | Shift+F9 |
| Скопировать содержимое верхней ячейки в текущую ячейку или з строку формул | Ctrl+Shift+" (двойная кавычка) |
| Переключить режимы отображения значения ячейки и формулы ячейки | Ctrl+' (знак левой кавычки) |
| Ввести набранную формулу в качестве ,формулы массива | Ctrl+Shift+Enter |
| После ввода имени функции в формулу отобразить панель формул | Ctrl+Ф |
| Вставить в круглых скобках список аргументов, после набора в формуле имени функции | Ctrl-fShift+Ф |
Упражнения
1. Используйте ячейку A3 для расчета выражения 5+(2*3-1)/5. Обратите внимание, что в ячейке отобразится результат, а в строке формул — расчетная формула. Не забудьте поставить знак равенства перед тем, как начать вводить формулу.
2. Для проверки, как работает формат времени, решите простейшую задачу: поезд отправляется в 22 часа и. через 4 часа прибывает на станцию назначения. Определить время прибытия поезда на станцию назначения. Решение: запишите в ячейке С2 время 22:00 и в ячейку СЗ — время 4:00. Выделите ячейку С4. и нажмите кнопку
Автосумма (AutoSum) 
Нажмите клавишу Enter. В ячейке появится результат 2:00.
3. Создайте ведомость, учитывающую продажу компьютеров через магазин, которая показана на рис. 16.4. Для этого выполните следующие операции:
выделите ячейки А1,В1,С1иВ1и выберите в меню Формат команду Ячейки. Откройте вкладку Выравнивание и поставьте флажок Объединение ячеек (см. рис. 15.1); введите в ячейку А1 текст: «Продажа компьютеров»; выберите в меню Формат команду Строка, Автоподбор высоты, чтобы был виден заголовок, написанный более крупным шрифтом; выделите текст и, раскрыв список Размер на панели форматирования, увеличьте размер шрифта до 20 пунктов; нажмите кнопку Ж на панели форматирования, чтобы заголовок был написан полужирным шрифтом; увеличьте ширину столбцов А, В, С и D, выбрав в меню Формат команду Столбец, Автоподбор ширины чтобы была виден весь текст в столбцах; введите в ячейку А2 название столбца Модель; в В2 — Цена, в С2 — Количество, в D2 — Сумма; выделите все четыре ячейки, установите на панели форматирования для них размер 14 и расположение По центру; заполните ячейки, в которых приводятся модели, цены и количество; выделите ячейку D3 и введите в нее формулу =ВЗ*СЗ; установите курсор на маркере заполнения ячейки D3 (маркер заполнения расположен в правом нижнем углу ячейки и имеет вид черно го квадратика) и, нажав кнопку мыши, растяните вниз рамку так, чтобы все строки с моделями компьютеров попали в рамку (в данном случае это ячейки D3 — D5). В ячейках D4 — D5 будут отображаться результаты расчетов по скопированным формулам;
Рис. 16.4
Пример таблицы, исходные данные которой
используются для вычислений по определенным формулам
в ячейку А7 введите Итого; выделите ячейку D7, нажмите кнопку Автосумма (AutoSum) на панели Стандартная. В ячейке появится запись = CУMM(D3:D6). Нажмите клавишу Enter; выделите все ячейки таблицы, и на панели форматирования в раскрывающемся списке границы нажмите кнопку Все границы, обеспечивающую проведение линий наружных границ таблицы и границ между ячейками.
Использование функций
Excel содержит обширный список стандартных функций, призванных облегчить выполнение простых и сложных вычислений.
Например, функция ДОХОД используется для вычисления дохода по облигациям, который составляет периодические процентные выплаты. Все функции имеют одинаковый формат записи, который включает имя функции и перечень аргументов. Аргументы располагаются в последовательности, определяемой синтаксисом функции, и разделяются запятой.
Запись функции начинается с указания ее имени, затем следует открывающаяся скобка, аргументы и закрывающая скобка. Функция может не иметь аргументов. Она может вводиться в ячейку листа как часть формулы. Функция позволяет выполнить вычисления на листах книги и на листах макросов.
Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Excel можно использовать до семи уровней вложения функций. При неправильной записи формулы на экране может появиться сообщение о циклической ссылке. Имена функций можно набирать строчными буквами. Они будут преобразованы в прописные после нажатия клавиши Enter.
Для вставки функции нажмите кнопку Вставка функции (Insert Function), На экране отобразится панель формул (Formula Toolbar) 
Средства статистического анализа данных
В состав Microsoft Excel входит пакет анализа, предназначенный для решения сложных статистических и инженерных задач. Средства, которые включены в пакет анализа данных, доступны через команду
Анализ данных в меню Сервис. Если этой команды нет в меню, необходимо загрузить надстройку Пакет анализа.
Средства анализа данных позволяют выполнять несколько видов дисперсионного
анализа, корреляционный и ковариационный анализ, создавать одномерный статистический отчет, содержащего информацию о центральной тенденции и изменчивости входных данных, производить выборку из генеральной совокупности и т.д.
Выполнение расчета с использованием стандартной функции
С Excel 2003 поставляются следующие стандартные функции:
Финансовые. Дата и времени. Математические. Статистические. Ссылки и массивы. Работа с базой данных. Текстовые. Логические. Проверка свойств и значений.
Рассмотрим несколько наиболее широко распространенных функций.
Математические функции
Математические функции используются в научных и инженерных расчетах для выполнения различных математических операций: вычисления логарифмов, тригонометрических функций и т.д. Пример использования математической функции рассмотрен ниже в упражнениях 1 и 2.
Статистические функции
Статистические функции используются для анализа диапазонов данных, вычисления параметров, характеризующих случайные величины, представленных множеством чисел, или их распределений, например, стандартного отклонения, среднего значения, и т.п. В частности, мы можем найти уравнение прямой или экспоненциальной кривой, оптимально согласующейся с опытными данными. Пример использования статистической функции рассмотрен ниже в упражнении 3.
Функции для работы с датами и временем
Для работы с датами и временем используется более десятка функций. Выберите команду
Функция (Function) в меню Вставка (Insert). В диалоговом окне Мастер функций — шаг 1 из 2
(Insert Function) (рис. 16.6) в списке или категорию (Function category) выделите Дата и время (Date&Time).
Пример использования функции Дата и время (Date&Time) рассмотрен ниже (см. Упражнения).
Текстовые
Функция Текстовые (Text) преобразует числовое значение в форматированный текст, и результат больше не участвует в вычислениях как число. Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку.
Например, в ячейке ВЗ вы можете записать фамилию продавца (Петров), в ячейке СЗ — объем его продаж (5000). После записи в какой-нибудь ячейке ВЗ& «продал»&ТЕКСТ(СЗ; «0,00 руб.»)&" единиц товара» при проведении вычислений произойдет объединение содержимого ячеек в одну фразу: Петров продал на 5000,00 руб. единиц товара.
Выполнение расчета с использованием стандартных функций
Чтобы выполнить расчет, используя стандартную функцию, выполните следующие действия:
1. Выделите ячейку, в которую надо вставить функцию, введите «=», а затем в раскрывающемся списке Функции в строке формул выберите нужную из списка (см. рис. 16.1). На экране отобразится диалоговое окно
Аргументы функции (Function Arguments) (рис. 16.6). Если в раскрывающемся списке выбрать
Другие функции (More functions) то откроется диалоговое окно Мастер функций — шаг 1 из 2
(Insert Function) (рис. 16.5).
Другие способы отображения диалогового окна Мастер функций — шаг 1 из 2 (Insert Function):
нажмите кнопку Вставка функции (Insert Function) в строке формул;
выберите команду Функция (Function) в меню Вставка (Insert);
нажмите клавиши Shift+F3.
2. В диалоговом окне Мастер функций — шаг 1 из 2 (Insert Function) (рис. 16.5) в поле Поиск функции (Search for a function)
введите описание действия, которое вы хотели бы выполнить. Например, чтобы найти функции, связанные с логарифмами чисел, введите log. Если вы не знаете, к какой категории относится ваша функция, то в раскрывающемся списке или категория
(Or select a category) выберите строку Полный алфавитный перечень (АН) и просмотрите список всех функций в алфавитном порядке. Полоса прокрутки позволяет просмотреть невидимые в данный
Рис. 16.5
Диалоговое окно мастера функций — шаг 1 из 2
момент элементы списка. В нижней части окна дается определение выделенной функции и ее аргументов. Чтобы получить описание функции, выберите функцию в списке и щелкните ссылку
Справка по этой функции (Help on this function). Выделите нужную строку в списке
Выберите функцию (Select a function) и нажмите кнопку (Жили клавишу Enter. 3. На экране отобразится диалоговое окно
Аргументы функции (Function Arguments) (рис. 16.6). В верхней части окна размещаются поля, предназначенные для ввода аргументов, в нижней части — справочная информация: имя выбранной функции, все ее аргументы, назначение функции и каждого аргумента, текущий результат функции и всей формулы. В тех случаях, когда аргумент приведен полужирным шрифтом, он является обязательным, если обычным шрифтом, то его можно пропустить. Чтобы панель формул не закрывала диапазон ячеек с данными, ее можно переместить, удерживая нажатой кнопку мыши. Чтобы ввести в качестве аргумента ссылку на ячейку, щелкните значок 
Высота диалогового окна уменьшиться. Выбрав ячейки, щелкните значок, позволяющий вернуть диалоговому окну первоначальный размер. Если диалоговое окно Аргументы функции (Function Arguments)
позволяет ввести несколько аргументов, то переход от одного поля аргумента к другому можно выполнять клавишей Tab. После ввода аргументов будет выведен текущий результат.
Рис. 16.6
Использование панели формул для оценки дисперсии по выборке
Формула массива
Если формула массива возвращает одно значение, укажите ячейку, в которую необходимо ввести формулу. Если формула массива возвращает несколько значений, то выделите диапазон ячеек, в которые необходимо ввести формулу. Наберите формулу и нажмите клавиши Ctrl+Shift+Enter. При вводе формулы массива Excel автоматически заключает ее в фигурные скобки {} после нажатия указанных клавиш.
Использование массива позволяет ввести формулу один раз и не повторять ее для всех ячеек диапазона. В связи с тем, что массив обрабатывается как единый модуль, то нельзя перемещать или удалять часть массива.
Автокоррекция при вводе формул
Excel 2003 может распознать наиболее распространенные ошибки, допускаемые пользователями при вводе формул в ячейку. Например, автоматически исправляются ошибки, связанные с неправильными ссылками, полученными в результате перемещения ячеек. Введенный по ошибке символ «х» автоматически преобразовывается в знак умножения и т.д. При этом на экране появляется запрос о необходимости произвести исправление.
Использование поля Введите вопрос пая решения вопросов, связанных с использованием функции
Для решения вопросов, связанных с использованием функции, ее синтаксисом, определением к какой категории она относится, можно ввести название функции в поле
Введите вопрос (Type a question for help) и нажать клавишу Enter. Так, если у вас возникли вопросы по использованию корреляционной функции, введите в поле:
корреляция (correlation). Будут предложены возможные варианты использования функции (рис. 16.7).
Рис. 16.7
Информация, предоставляемая помощником о функции
Нажмите кнопку интересующего вас варианта ответа. В приведенной справке будет указано назначение функции, ее синтаксис и даны примеры.
Упражнения
1. Excel помогает вам найти вычислить различные справочные данные, не пользуясь справочниками. Найдите десятичный логарифм числа 250. Выполните упражнение следующим образом:
Выделите ячейку, в которую надо вставить функцию, и нажмите кнопку Вставка функции (Insert Function)
2. Найдите значения тригонометрических функций: синуса, косинуса, тангенса, котангенса для угла 10 градусов.
Рис. 16.8
Вычисление десятичного логарифма.
Выделите ячейку, в которую надо вставить функцию, и нажмите кнопку Вставка функции (Insert Function)
3. Предположим, что из инструментов, отштампованных одной и той же машиной, выбраны наугад 10 образцов и испытаны на излом. Значения выборки (1345, 1301, 1368, 1322, 1310, 1370, 1318, 1350, 1303, 1299) сохранены в ячейках А2:ЕЗ соответственно. Оцените дисперсию по выборке. Для решения упражнения используем статистическую функцию. Сделаем текущей первую свободную ячейку в столбце А, например #* А4, и нажмем кнопку
Вставка функций (Paste Function)
Функция ДИСП предполагает, что аргументы являются только выборкой из генеральной совокупности. Если данные представляют всю генеральную совокупность, вычисляйте дисперсию, используя функцию ДИСПР. Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если они не должны игнорироваться, пользуйтесь функцией рабочего листа ДИСПА (VARA). Синтаксис функции: ДИСП(число1;число2;...)
Число1, число2, ... — это от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности.
Нажмите кнопку ОК или клавишу Enter. В окне Excel ниже строки формул отобразится панель формул. Выделите диапазон ячеек А2:ЕЗ, в котором хранятся данные. Внизу панели формул увидите надпись Значение (Formula Result): 754,3. Нажмите кнопку ОК. Таким образом, дисперсия сопротивления на излом для всех инструментов будет равна 754,3.
Рис. 16.9
Окно, используемое для вычислений функции времени
4. Найдите значение времени в виде десятинной дроби для 8 часов 53 минут 14 секунд. Выполните упражнение следующим образом: Нажмите клавиши Shift+F3. В диалоговом окне Мастер функций — шаг 1 из 2 (Insert Function) в раскрывающемся списке Категорию выделите строку Дата и время (Date& Time), а в нижнем списке Выберите функцию (Select a function) — ВРЕМЯ (Time) (рис. 16.5) и нажмите кнопку ОК. На экране отобразится диалоговое окно Аргументы функции (Function Arguments) (рис. 16.9). В поле Час (Hour) введите 8, в поле Минута (Minute) — 53, в поле Секунда (Second) — 14 и нажмите кнопку ОК. В ячейке получим значение 0,370301.
Копирование формул
Копирование формул производят по тем же правилам, что и копирование данных листа. Ниже рассмотрены различные методы копирования формул.
Копирования формулы методом перетаскивания
Приведем пример копирования формулы методом перетаскивания. Предположим надо возвести в третью степень числа 5, 7 и 10, которые находятся в ячейках A3, А4 и А5. Результаты вычислений запишем соответственно в ячейки ВЗ, В4 и В5. Выделим ячейку ВЗ и введем в нее формулу =АЗА3 (символ «Л» используется как оператор возведения в степень). Нажмем клавишу Enter. В ячейке появится результат 125. Выделим еще раз ячейку ВЗ. Установим указатель мыши на маленький черный квадратик — маркер заполнения. Нажмем кнопку мыши и растянем рамку еще на две ячейки вниз. В выделенных ячейках отобразятся результаты вычислений: 343 и 1000. Щелкнем ячейку В4 — в строке формул увидим =А4Л3, т.е. относительный адрес ячейки изменился.
Если при копировании формулы необходимо оставить ее адрес неизменным, то используется абсолютная ссылка. Напишем формулу в виде =$А$3^3.
При копировании этой формулы в любое место таблицы всегда будут возведены в третью степень данные, находящиеся в ячейке A3.
Копирование формул с помощью команды Копировать в меню Правка
Чтобы скопировать формулу, выделите ячейку с формулой и выберите в меню Правка (Edit) команду Копировать (Сору). Затем выделите ячейку или диапазон ячеек, куда будет вставлена формула, и выберите команду
Вставить (Insert) в меню Правка (Edit). Ячейки, в которые копируется формула, могут находиться на другом листе или в другой книге.
Копирование формул с помощью команды Заполнить в меню Правка
При повторных вычислениях по одним и тем же формулам можно воспользоваться еще одним способом копирования формул — командой Заполнить (Fill)
в меню Правка (Edit). В качестве примера используем эту команду для нахождение среднего арифметического значения данных по столбцам в ячейках В10 и В11, С10 и С11, D10 и D11 (рис. 16.10).
Введем в ячейки исходные данные. Поставим курсор в ячейку В12 и выберем команду Функция (Function) в меню Вставка (Insert). В диалоговом окне Мастер функций — шаг 1 из 2 (Insert Function) (рис. 16.6) в списке Категория (Function category) в раскрывающемся списке Категория выберем строку Статистические (Statistical), в списке Выберите функцию (Select a function) элемент СРЗНАЧ (AVERAGE) и нажмем кнопку ОК. В диалоговом окне Аргументы функции (Function Arguments) (рис. 16.6). Мастер функции предложит найти среднее арифметическое значения в ячейках В10:В11. После нажатия кнопки ОК в ячейке В12 появится результат.
Рис. 16.10
Копирование формул
с помощью команды Заполнить
в меню Правка
Выделим ячейки В12, С12 и D12. Выберем в меню Правка (Edit) команду Заполнить (Fill), Вправо (Right).
Формула из ячейки В12 скопируется в ячейки С12 и D12 и мы увидим результаты вычислений. Если в ячейке В12 находилась формула =СРЗНАЧ(В10:В11) (AVERAGE)(B10:B11), то в ячейках'С12 и D12, она примет соответственно вид =СРЗНАЧ(С10:С11) и =CP3HA4(D10:D11).
Сообщение об ошибке
Сообщение об ошибке начинается со знака #, например, если при выполнении расчетов в ячейке появится #ЗНАЧ!, то это означает, что программа не может найти исходные данные. В зависимости от причины возникновения ошибки меняется вид сообщения. Так сообщение #ДЕЛ/0! (#DIV/0!) появляется, когда в формуле предлагается провести деление на ноль (рис. 16.11). При подводе указателя мыши к значку смарт-тега рядом с ними отображается, кнопка. Щелкните значок, чтобы открыть меню, из которого вы узнаете, какого типа ошибка обнаружена, сможете просмотреть этапы вычислений, провести изменения в строке формул и т.д.
Рис. 16.11
Сообщение об ошибке
Если Excel считает, что ошибку во введенном выражении можно исправить, то появится окно с предложением, как отредактировать формулу.
Обнаружение и исправление ошибок в выполненных расчетах
Кнопка Проверка наличия ошибок (Error Checking tool) позволяет найти на рабочем листе неверные, с точки зрения Excel формулы. После нахождения ошибки отображается диалоговое окно с возможными вариантами исправлений. Если по вашему мнению ошибки нет, то нажмите кнопку
Пропустить ошибку (Ignore).
Excel 2003 следит за тем, не имеет ли какая-либо ячейка, содержащая число, текстовый формат; контролирует, не берет ли какая-нибудь формула данные из пустой ячейки (вследствие неправильного указания ее параметров). Параметры отслеживания ошибок можно задать после выбора команды
Параметры (Options) в меню Сервис (Tools) на вкладке Проверка ошибок (Error Checking). Если в ячейке обнаружена ошибка, эта ячейка помечается зеленым треугольником в левом верхнем углу.
Отслеживание ячеек, участвующих в вычислениях
Ячейки, из которых берутся значения для формулы в активной ячейке, называются влияющими. Ячейки, содержащие результат вычислений или формулы, полученные с использованием текущей в данной момент ячейки, называются зависимыми.
Проследить путь от исходных данных к результатам позволяют команды Зависимости формул (Formula Auditing),
Влияющие ячейки (Trace Precedents) и Зависимые ячейки (Trace dependents)
в меню Сервис (Tools) или одноименные кнопки на панели инструментов
Зависимости (Formula Auditing).
В качестве примера проследим, какие ячейки влияют на результат вычислений по формуле, приведенной в ячейке F5 (рис. 16.12). Выделим ячейку F5 и нажмем кнопку Влияющие ячейки панели инструментов Зависимости. В таблице появятся стрелки, показывающие ячейки, участвующие в формуле, Чтобы убрать стрелки, нажмите кнопку
Перед тем, как отобразить панель инструментов Зависимости следует убедиться, что на вкладке
Вид (View) (см. главу 19, рис. 19.8) в окне команды Параметры (Options) из меню
Сервис (Tools) переключатель в группе Объекты (Objects) не стоит в положении
не отображать (Hide all).
Рис. 16.12
Отображение стрелок, показывающих, какие ячейки участвуют в получении результата
После выделения ячейки, содержащей ошибку, можно нажать кнопку 
Кнопка Убрать все стрелки (Remove All Arrows) удаляет все стрелки на вя листе 
Нахождение ячеек, влияющих на заданную ячейку
Excel позволяет найти ячейки, влияющие на интересующую нас ячейку. Для определения этих ячеек выполните следующие действия:
выделите заданную ячейку; выберите команду Перейти (Go To) в меню Правка (Edit) и нажмите кнопку Выделить (Special) в диалоговом окне Переход (Go To) (см. главу 13, рис. 13.2); на экране отобразится диалоговое окно Выделение группы ячеек (Go To Special) (рис. 16.13). Чтобы найти ячейки, влияющие на заданную ячейку, установите переключатель влияющие ячейки (Precedents) и один из связанных с ним переключателей на всех уровнях (All levels) или только непосредственно (Direct only); после того, как вы нажмете кнопку ОК, на листе будут отмечены все влияющие ячейки.
Рис. 16.13
Диалоговое окно Выделение группы ячеек
Окно контрольного значения
Окно контрольного значения позволяет следить за тем, как меняются после ввода в таблицу новых исходных данных значения в ячейках связанных между собою формулами. Новые значения могут вводиться, например, для получения требуемого результата.
Для наблюдения за формулами и их результатами выполните следующие действия:
До отображения окна выделите ячейки для просмотра. Чтобы выделить на листе все ячейки, содержащие формулы, в меню Правка (Edit) выберите команду Перейти (Go To), нажмите кнопку Выделить (Special), а затем установите кнопку переключателя в положение формулы (formulas).
Рис. 16.14
Вкладка Вычисления диалогового окна Параметры,
позволяющая ограничить время, затрачиваемое на вычисления
В меню Сервис (Tool) укажите на пункт Зависимости формул (Formula Auditing), а затем выберите команду Показать окно контрольного значения (Show Watch Window). Нажмите кнопку Добавить контрольное значение. (Add Watch). Нажмите кнопку Добавить (Add). Переместите панель инструментов Окно контрольного значения в верхнюю, нижнюю, левую или правую часть окна. Чтобы изменить ширину столбца, перетащите правую границу заголовка столбца. Чтобы отобразить ячейку, на которую ссылается ячейка на панели инструментов Окно контрольного значения, дважды щелкните ячейку.
Отметим, что ячейки, которые имеют связи с другими книгами, отображаются на панели инструментов Окно контрольного значения, только если те книги открыты.
Указать ячейку, текущее значение которой будут отображаться в окне после вычисления по формуле, позволяет кнопка Показать окно контрольного значения
(Add watch) панели инструментов Зависимости.
Настройка Excel на выполнение вычислений
Внесение новых данных в ячейки приводит к автоматическому пересчету всех формул. Если открытые листы содержат большое количество формул, которые требуют много времени на автоматический пересчет, то можно сократить временные затраты. Чтобы отменить автоматический пересчет после каждого внесения изменений в таблицу, выберите команду
Параметры (Options) в меню Сервис (Tools), а затем откройте вкладку Вычисления (Calculation)
(рис. 16.14).
В группе Вычисления (Calculation) можно установить переключатель в одно из положений:
автоматически (Automatic) — обеспечивает вычисление значений формул при каждом внесении изменений в данные, формулу или название. Эта установка используется по умолчанию; автоматически кроме таблиц (Automatic except tables) — обеспечивает вычисление всех зависящих формул, за исключением таблиц данных. Пересчет таблиц данных будет производиться только при нажатии кнопки Вычислить (Calc Now), отображенной на вкладке, или клавиши F9; вручную (Manual) —- выполнение вычислений на всех открытых листах будет производиться после нажатия кнопки Вычислить (Calc Now), отображенной на вкладке Вычисления или клавиши F9. Для выполнения расчетов только на активном листе нажмите клавиши Shift+F9. После установки переключателя в это положение Excel автоматически устанавливает флажок пересчет перед сохранением (Recalculate before save) с тем, чтобы обеспечить сохранение и учет всех введенных данных.
Установив флажок итерации (Iterations), в поля Предельное число итераций (Maximum iterations) и
Относительная погрешность (Maximum change) можно ввести числовые значения.
Назначение некоторых флажков в разделе Параметры книги (Workbook options):
обновлять удаленные ссылки (Update remote references) — обеспечивает вычисление и обновление формул, содержащих ссылки на другие приложения; точность как на экране (Precision as displayed) — понижает точность вычислений с внутреннего представления чисел (15 знаков после запятой) до точности, используемой при отображении ячеек на экране. Эта операция приводит к потере значащих разрядов, и отменить ее невозможно; сохранять значения внешних связей (Save external link values) — сохраняет копии значений, содержащихся во внешнем документе, соединенном с листом Microsoft Excel. Если лист, связанный с большим диапазоном ячеек во внешних документах, требует большого количества дисковой памяти или открывается очень долго, снимите флажок, чтобы улучшить эти показатели.
Упражнение
Найдите значения 24, З4 и 44, используя метод копирования формулы перетаскиванием.
Выводы
1. Формула в Excel начинается со знака равенства «=», за которым следуют вычисляемые элементы (операнды), разделенные операторами. При изменении хотя бы одного значения в ячейках, участвующих в формуле, автоматически происходит перерасчет результата по новым данным. Результат вычисления помещается в ячейку, в которой находится формула.
2. Относительные ссылки автоматически изменяются при копировании формул в другие строки и столбцы. Абсолютная ссылка сохраняет адрес определенной ячейки независимо местоположения ячейки с формулой формулы.
3. Копирование упрощает ввод в таблицу однотипных формул. При копировании формулы автоматически изменяются относительные ссылки ячеек, входящие в формулу, в соответствии с ее новым положением на листе книги.
4.При проверке выполненных расчетов можно проследить путь от исходных данных к результатам с помощью команды
Зависимости формул (Formula Auditing), Влияющие ячейки (Trace Precedents) в меню
Сервис (Tools) или панели инструментов Зависимости (Formula Auditing).
5. Чтобы отменить автоматический пересчет после каждого внесения изменений в таблиду, содержащую большое количество формул, которые трeбуют много времени на автоматический пересчет, выберите команду
Параметры (Options) в меню Сервис (Tools), а затем откройте вкладку Вычисления (Calculation) (рис. 16.14). Чтобы изменить предельное число итераций и относительную погрешность, ограничивающие итерации, установите флажок
итерации (Iterations), введите значения в поля Предельное число итераций (Maximum iterations) и
Относительная погрешность (Maximum change).
