Проведение расчетов в программе Microsoft Excel 2007

 

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

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

· знаки арифметических действий: +, -, ∙, /, ^ (знак возведения числа в степень), знак %;

· числа, строки (они берутся в кавычки);

· ссылки на ячейки и диапазоны ячеек (как на текущем листе, так и на других листах книги) для определения порядка вычислений, скобки;

· встроенные функции.

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

· =А2+В2 — сложение значений двух ячеек;

· =А1∙0,8 — умножение числа из ячейки А1 на 0,8;

· =D1^2+1 — возведение числа из ячейки D1 в квадрат и прибавление единицы к результату;

· =СУММ(А1: А5) — суммирование значений из диапазона ячеек А1: А5. Это пример использования встроенной функции. Здесь СУММ — имя функции, А1:А5 — диапазон ячеек, ее единственный аргумент, заключенный в скобки;

· =МУМНОЖ(B1:B2;B7:C7) — вычисление произведения матриц B1:B2 и B7:C7. Эта функция имеет два аргумента, которые являются массивами данных из выделенных диапазонов.

При наличии у функции нескольких аргументов они отделяются друг от друга точкой с запятой. В качестве аргументов функций вы можете использовать ссылки на ячейки и диапазоны на текущем и других листах. В последнем случае перед адресом ячейки или диапазона следует ввести название листа в кавычках и поставить разделитель "!". Например, «Лист1»! В2, «Лист 3»! А1: С4.

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

 

 

Рис. 3.8. Просмотр назначения функции

 

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

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

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

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

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

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

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

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

 

 

 

 

Рис. 3.9. Результаты вычислений в строке состояния для выделенного диапазона

 

Относительная и абсолютная адресация. Адреса ячеек и диапазонов в Excel могут быть относительными и абсолютными. До сих пор рассказывалось об относительных ссылках на ячейки и диапазоны, которые состоят только из номера строки и буквы столбца, например В2 или D4:D8. Преимущество относительной адресации состоит в том, что при копировании ячеек с использованием автозаполнения, ссылки в скопированных формулах меняются автоматически (относятся ячейкам текущей, а не исходной строки), поэтому нет необходимости набирать вручную каждую формулу. Однако на практике встречаются ситуации, когда адрес ячейки или диапазона ячеек необходимо зафиксировать, чтобы он не изменялся при копировании или автозаполнении ячеек. Для этого необходимо добавить перед номером строки и буквой столбца знак $. Так, если сделать адрес ячейки В2 абсолютным, он будет выглядеть как $B$2. Кроме того, можно зафиксировать в ссылке адрес только столбца ($B2) или только строки (B$2). Это называется смешанной адресацией. Для быстрого изменения адресации в готовой формуле дважды щелкните на ней, установите курсор на нужную ссылку и последовательно нажимайте клавишу F4 для изменения типа адреса. Знак $ можно добавлять в формулы вручную с клавиатуры.

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

При обнаружении ошибки в синтаксисе формулы (например, если между аргументами функции будет отсутствовать разделитель «;», будут пропущены или поставлены лишние скобки, обнаружится меньше аргументов, чем того требуется для данной функции) Excel выдаст сообщение об ошибке. Текст сообщения будет различным в зависимости от того, удалось ли Excel определить источник ошибки; когда система не может определить источник ошибки, появляется сообщение представленное на рис. 3.10.

 

 

Рис. 3.10. Сообщение об ошибке, не распознанной Excel

 

В этом случае вернитесь к ячейке с формулой, перепроверьте ее и исправьте ошибку. Иногда системе удается определить, какие изменения следует внести в формулу, чтобы она стала синтаксически правильной. При этом появляется сообщение , указанное на рис. 3.11.

 

 

Рис. 3.11. Сообщение о распознанной ошибке

 

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

Иногда после введения формулы в ячейке вместо результата появляется текстовое сообщение об ошибке. Это связано с тем, что при проведении вычислений система столкнулась с каким-либо противоречием. Вот список наиболее часто встречающихся сообщений в ячейках:

1. #ЗНАЧ! — ошибка в типе данных, используемых в формуле. Возможно, в одной из ячеек диапазона находится текст;

2 #ИМЯ? — ошибка в имени функции или адресах ячеек и диапазонов, присутствующих в формуле;

3. #ССЫЛКА! — удалены или перемещены ячейки или диапазоны, на которые ссылается формула;

4. #ДЕЛ/0! — при расчете происходит деление на ноль;

5. ###### — данные не умещаются по ширине в ячейку; увеличьте ширину столбца протаскиванием границы заголовка.

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

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

 

 

Рис. 3.12. Выделение цветными рамками ячеек,

ссылка на которые имеется в формуле

 

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

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

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

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

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

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

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

 

Рис. 3.18. Прайс-лист для расчета цены с учетом скидки

 

Поиск и замена данных в ячейках Excel осуществляется по таким же принципам, что и поиск текстовых фрагментов Word. Над книгами Excel можно выполнять все стандартные операции: открытие, сохранение, распечатку, за которые отвечают команды Кнопки «Office» в верхнем левом углу окна программы. В Excel их действие абсолютно аналогично.