Формула - это математическое выражение, которое создается для вычисления результата и которое может зависеть от содержимого других ячеек. Формула в ячейке может содержать данные, ссылки на другие ячейки, а также обозначение действий, которые необходимо выполнить.
Использование ссылок на ячейки позволяет пересчитывать результат по формулам, когда происходят изменения содержимого ячеек, включенных в формулы.
В Excel формулы начинаются со знака =. Скобки () могут использоваться для определения порядка математических операции.
Excel поддерживает следующие операторы:
- Арифметические операции:
- сложение (+);
- умножение (*);
- нахождение процента (%);
- вычитание (-);
- деление (/);
- экспонента (^).
- Операторы сравнения:
- = равно;
- < меньше;
- > больше;
- <= меньше или равно;
- >= больше или равно;
- <> не равно.
- Операторы связи:
- : диапазон;
- ; объединение;
- & оператор соединения текстов.
Таблица 22. Примеры формул
Упражнение
Вставка формулы -25-А1+АЗ
Предварительно введите любые числа в ячейки А1 и A3.
- Выберите необходимую ячейку, например В1.
- Начните ввод формулы со знака=.
- Введите число 25, затем оператор (знак -).
- Введите ссылку на первый операнд, например щелчком мыши на нужную ячейку А1.
- Введите следующий оператор(знак +).
- Щелкните мышью в той ячейке, которая является вторым операндом в формуле.
- Завершите ввод формулы нажатием клавиши Enter . В ячейке В1 получите результат.
Автосуммирование
Кнопка Автосумма (AutoSum) - ∑ может использоваться для автоматического создания формулы, которая суммирует область соседних ячеек, находящихся непосредственно слева в данной строке и непосредственно выше в данном столбце.
- Выберите ячейку, в которую надо поместить результат суммирования.
- Щелкните кнопку Автосумма - ∑ или нажмите комбинацию клавиш Alt+=. Excel примет решение, какую область включить в диапазон суммирования, и выделит ее пунктирной движущейся рамкой, называемой границей.
- Нажмите Enter для принятия области, которую выбрала программа Excel, или выберите с помощью мыши новую область и затем нажмите Enter.
Функция "Автосумма" автоматически трансформируется в случае добавления и удаления ячеек внутри области.
Упражнение
Создание таблицы и расчет по формулам
- Введите числовые данные в ячейки, как показано в табл. 23.
| А | В | С | D | Б | F | |
| 1 | ||||||
| 2 | Магнолия | Лилия | Фиалка | Всего | ||
| 3 | Высшее | 25 | 20 | 9 | ||
| 4 | Среднее спец. | 28 | 23 | 21 | ||
| 5 | ПТУ | 27 | 58 | 20 | ||
| в | Другое | 8 | 10 | 9 | ||
| 7 | Всего | |||||
| 8 | Без высшего |
Таблица 23. Исходная таблица данных
- Выберите ячейку В7, в которой будет вычислена сумма по вертикали.
- Щелкните кнопку Автосумма - ∑ или нажмите Alt+= .
- Повторите действия пунктов 2 и 3 для ячеек С7 и D7.
Вычислите количество сотрудников без высшего образования (по формуле В7-ВЗ).
- Выберите ячейку В8 и наберите знак (=).
- Щелкните мышью в ячейке В7, которая является первым операндом в формуле.
- Введите с клавиатуры знак (-) и щелкните мышью в ячейке ВЗ, которая является вторым операндом в формуле (будет введена формула).
- Нажмите Enter (в ячейке В8 будет вычислен результат).
- Повторите пункты 5-8 для вычислений по соответствующим формулам в ячейках С8 и 08.
- Сохраните файл с именем Образование_сотрудников.х1s.
Таблица 24. Результат расчета
| А | B | С | D | Е | F | |
| 1 | Распределение сотрудников по образованию | |||||
| 2 | Магнолия | Лилия | Фиалка | Всего | ||
| 3 | Высшее | 25 | 20 | 9 | ||
| 4 | Среднее спец. | 28 | 23 | 21 | ||
| 5 | ПТУ | 27 | 58 | 20 | ||
| 6 | Другое | 8 | 10 | 9 | ||
| 7 | Всего | 88 | 111 | 59 | ||
| 8 | Без высшего | 63 | 91 | 50 | ||
Тиражирование формул при помощи маркера заполнения
Область ячеек (ячейка) может быть размножена при помощи использования маркера заполнения. Как было показано в предыдущем разделе, маркер заполнения представляет собой контрольную точку в правом нижнем углу выделенной ячейки.
Часто бывает необходимо размножать не только данные, но и формулы, содержащие адресные ссылки. Процесс тиражирования формул при помощи маркера заполнения позволяет колировать формулу при одновременном изменении адресных ссылок в формуле.
- Выберите ячейку, содержащую формулу для тиражирования.
- Перетащите маркер заполнения в нужном направлении. Формула будет размножена во всех ячейках.
Обычно этот процесс используется при копировании формул внутри строк или столбцов, содержащих однотипные данные. При тиражировании формул с помощью маркера заполнения меняются так называемые относительные адреса ячеек в формуле (подробно относительные и абсолютные ссылки будут описаны далее).
Упражнение
Тиражирование формул
1.Откройте файл Образование_сотрудников.х1s.
- Введите в ячейку ЕЗ формулу для автосуммирования ячеек =СУММ(ВЗ:03).
- Скопируйте, перетащив маркер заполнения, формулу в ячейки Е4:Е8.
- Просмотрите как меняются относительные адреса ячеек в полученных формулах (табл. 25) и сохраните файл.
| А | В | С | D | Е | F | |
| 1 | Распределение сотрудников по образованию | |||||
| 2 | Магнолия | Лилия | Фиалка | Всего | ||
| 3 | Высшее | 25 | 20 | 9 | =СУММ{ВЗ:03) | |
| 4 | Среднее спец. | 28 | 23 | 21 | =СУММ(В4:04) | |
| 5 | ПТУ | 27 | 58 | 20 | =СУММ(В5:05) | |
| 6 | Другое | 8 | 10 | 9 | =СУММ(В6:06) | |
| 7 | Всего | 88 | 111 | 58 | =СУММ(В7:07) | |
| 8 | Без высшего | 63 | 91 | 49 | =СУММ(В8:08) | |
Таблица 25. Изменение адресов ячеек при тиражировании формул
Относительные и абсолютные ссылки
Формулы, реализующие вычисления в таблицах, для адресации ячеек используют так называемые ссылки. Ссылка на ячейку может быть относительной или абсолютной.
Использование относительных ссылок аналогично указанию направления движения по улице - "идти три квартала на север, затем два квартала на запад". Следование этим инструкциям из различных начальных мест будет приводить в разные места назначения.
Например, формула, которая суммирует числа в столбце или строке, затем часто копируется для других номеров строк или столбцов. В таких формулах используются относительные ссылки (см. предыдущий пример в табл. 25).
Абсолютная ссылка на ячейку.иди область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Например, когда формула вычисляет доли от общей суммы, ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.
Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 - так называемые смешанные ссылки).
- Создайте таблицу, аналогичную представленной ниже.
Таблица 26. Расчет зарплаты
- В ячейку СЗ введите формулу для расчета зарплаты Иванова =В1*ВЗ.
При тиражировании формулы данного примера с относительными ссылками в ячейке С4 появляется сообщение об ошибке (#ЗНАЧ!), так как изменится относительный адрес ячейки В1, и в ячейку С4 скопируется формула =В2*В4;
- Задайте абсолютную ссылку на ячейку В1, поставив курсор в строке формул на В1 и нажав клавишу F4, Формула в ячейке СЗ будет иметь вид =$В$1*ВЗ.
- Скопируйте формулу в ячейки С4 и С5.
- Сохраните файл (табл. 27) под именем Зарплата.xls.
Таблица 27. Итоги расчета зарплаты
Имена в формулах
Имена в формулах легче запомнить, чем адреса ячеек, поэтому вместо абсолютных ссылок можно использовать именованные области (одна или несколько ячеек). Необходимо соблюдать следующие правила при создании имен:
- имена могут содержать не более 255 символов;
- имена должны начинаться с буквы и могут содержать любой символ, кроме пробела;
- имена не должны быть похожи на ссылки, такие, как ВЗ, С4;
- имена не должны использовать функции Excel, такие, как СУММ, ЕСЛИ и т. п.
В меню Вставка, Имя существуют две различные команды создания именованных областей: Создать и Присвоить.
Команда Создать позволяет задать (ввести) требуемое имя (только одно ), команда Присвоить использует метки, размещенные на рабочем листе, в качестве имен областей (разрешается создавать сразу несколько имен ).
Создание имени
- Выделите ячейку В1 (табл. 26).
- Выберите в меню Вставка, Имя (Insert, Name) команду Присвоить (Define) .
- Введите имя Часовая ставка и нажмите ОК .
- Выделите ячейку В1 и убедитесь, что в поле имени указано Часовая ставка .
Создание нескольких имен
- Выделите ячейки ВЗ:С5 (табл. 27).
- Выберите в меню Вставка, Имя (Insert, Name) команду Создать (Create) , появится диалоговое окно Создать имена (рис. 88).
- Убедитесь, что переключатель в столбце слева помечен и нажмите ОК .
- Выделите ячейки ВЗ:СЗ и убедитесь, что в поле имени указано Иванов.
Рис. 88. Диалоговое окно Создать имена
Можно в формулу вставить имя вместо абсолютной ссылки.
- В строке формул установите курсор в то место, где будет добавлено имя.
- Выберите в меню Вставка, Имя (Insert, Name) команду Вставить (Paste), появится диалоговое окно Вставить имена.
- Выберите нужное имя из списка и нажмите ОК.
Ошибки в формулах
Бели при вводе формул или данных допущена ошибка, то в результирующей ячейке появляется сообщение об ошибке. Первым символом всех значений ошибок является символ #. Значения ошибок зависят от вида допущенной ошибки.
Excel может распознать далеко не все ошибки, но те, которые обнаружены, надо уметь исправить.
Ошибка # # # # появляется, когда вводимое число не умещается в ячейке. В этом случае следует увеличить ширину столбца.
Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на нуль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.
Ошибка #Н/Д! является сокращением термина "неопределенные данные". Эта ошибка указывает на использование в формуле ссылки на пустую ячейку.
Ошибка #ИМЯ? появляется, когда имя, используемое в формуле, было удалено или не было ранее определено. Для исправления определите или исправьте имя области данных, имя функции и др.
Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. Чаще всего ошибка указывает, что допущена ошибка при вводе ссылок на диапазоны ячеек.
Ошибка #ЧИСЛО! появляется, когда в функции с числовым аргументом используется неверный формат или значение аргумента.
Ошибка #ЗНАЧ! появляется, когда в формуле используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения для оператора или функции введен текст.
Кроме перечисленных ошибок, при вводе формул может появиться циклическая ссылка.
Циклическая ссылка возникает тогда, когда формула прямо или косвенно включает ссылки на свою собственную ячейку. Циклическая ссылка может вызывать искажения в вычислениях на рабочем листе и поэтому рассматривается как ошибка в большинстве приложений. При вводе циклической ссылки появляется предупредительное сообщение (рис. 89).
Для исправления ошибки удалите ячейку, которая вызвала циклическую ссылку, отредактируйте или введите заново формулу.
Функции в Excel
Более сложные вычисления в таблицах Excel осуществляются с помощью специальных функций (рис. 90). Список категорий функций доступен при выборе команды Функция в меню Вставка (Insert, Function).
Финансовые функции осуществляют такие расчеты, как вычисление суммы платежа по ссуде, величину выплаты прибыли на вложения и др.
Функции Дата и время позволяют работать со значениями даты и времени в формулах. Например, можно использовать в формуле текущую дату, воспользовавшись функцией СЕГОДНЯ .
Рис. 90. Мастер функций
Математические функции выполняют простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, абсолютной величины числа, округление чисел и др.
Статистические функции позволяют выполнять статистический анализ данных. Например, можно определить среднее значение и дисперсию по выборке и многое другое.
Функции работы с базами данных можно использовать для выполнения расчетов и для отбора записей по условию.
Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ .
Логические функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.
Функции Проверка свойств и значений предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ .
Для вычислений в таблице с помощью встроенных функций рекомендуется использовать мастер функций. Диалоговое окно мастера функций доступно при выборе команды Функция в меню Вставка или нажатии кнопки, на стандартной панели инструментов. В процессе диалога с мастером требуется задать аргументы выбранной функции, для этого необходимо заполнить поля в диалоговом окне соответствующими значениями или адресами ячеек таблицы.
Упражнение
Вычисление величины среднего значения для каждой строки в файле Образование.хls.
- Выделите ячейку F3 и нажмите на кнопку мастера функций.
- В первом окне диалога мастера функций из категории Статистические выберите функцию СРЗНАЧ , нажмите на кнопку Далее .
- Во втором диалоговом окне мастера функций должны быть заданы аргументы. Курсор ввода находится в поле ввода первого аргумента. В это поле в качестве аргумента число! введите адрес диапазона B3:D3 (рис. 91).
- Нажмите ОК .
- Скопируйте полученную формулу в ячейки F4:F6 и сохраните файл (табл. 28).
Рис. 91. Ввод аргумента в мастере функций
Таблица 28. Таблица результатов расчета с помощью мастера функций
| А | В | С | D | Е | F | |
| 1 | Распределение сотрудников по образованию | |||||
| 2 | Магнолия | Лилия | Фиалка | Всего | Среднее | |
| 3 | Высшее | 25 | 20 | 9 | 54 | 18 |
| 4 | Среднее спец. | 28 | 23 | 21 | 72 | 24 |
| 8 | ПТУ | 27 | 58 | 20 | 105 | 35 |
| в | Другое | 8 | 10 | 9 | 27 | 9 |
| 7 | Всего | 88 | 111 | 59 | 258 | 129 |
Для ввода диапазона ячеек в окно мастера функций можно мышью обвести на рабочем листе таблицы этот диапазон (в примере B3:D3). Если окно мастера функций закрывает нужные ячейки, можно передвинуть окно диалога. После выделения диапазона ячеек (B3:D3) вокруг него появится бегущая пунктирная рамка, а в поле аргумента автоматически появится адрес выделенного диапазона ячеек.
Для выполнения различных вычислений в таблицах Excel 2007 используют формулы и функции. Основные понятия о формулах и функциях в Excel 2003 изложены на страничке . Изложенные сведения о формулах и функциях для Excel 2003 являются характерными и для Excel 2007/2010.
Выполнение вычислений с помощью формул в Excel 2007 представлено на рисунке 1.
Рис. 1
Вычисления с помощью формул всегда начинаются со знака равенства "=". Кроме того, в формулу могут входить ссылки на ячейки, в которых указаны определенные значения в одном из числовых форматов, имена, числовые величины и функции, объединенные знаками арифметических действий (^; *; /; +; -;). В ячейке, в которой осуществлены вычисления, отображаются значения, а формула отображается в строке ввода.
Ссылки на ячейки бывают относительными и абсолютными. Абсолютные ссылки отличаются от относительных тем, что содержат символ “$” (например, $B$2) перед всеми атрибутами адреса ячейки. К смешанным ссылкам относятся ссылки на ячейки, в которых перед одним из атрибутов адреса ячейки установлен символ “$” (например, B$2 или $B2).
Образец формулы (Y=2sin(пx)cos 5 (2пx) + sin(5 пx) 2 , где п - число ПИ = 3, 14), которая содержит функции sin(), cos(), ПИ(), переменную x - относительную ссылку на ячейку "A2", представлен на скриншоте рисунок 2.
Рис. 2
В формуле могут быть использованы и имена диапазона ячеек, но для этого надо присвоить имена диапазонам ячеек. Чтобы присвоить имя диапазону ячеек в Excel 2007, необходимо выполнить следующие операции. Выделить диапазон требуемых ячеек, перейти на вкладку "Формулы" и щелкнуть на кнопке раскрывающегося списка "Присвоить имя" в группе "Определенные имена".
Из списка выбрать надпись "Присвоить имя...", откроется окно диалога "Создание имени". В строке "Имя" ввести имя, например "Закупка1" и щелкнуть на кнопке ОК. В результате выбранному диапазону ячеек будет назначено соответствующее имя (рисунок 3).
Рис. 3
Для использования имени диапазона ячеек в формуле надо выделить ячейку и вставить требуемую формулу, например СУММ(). В качестве аргумента используем имя диапазона ячеек, например "Закупка1". Для этого на вкладке "Формулы" в группе "Определенные имена" надо щелкнуть на команде "Использовать в формуле", и в открывшемся списке имен выбрать требуемое имя (рисунок 4).
Рис. 4
Формулы в Excel можно перемещать и копировать. Абсолютные ссылки при копировании формулы (распространении формулы) не изменяются, а изменятся только те атрибуты адреса ячейки, перед которыми не установлен символ “$” (т.е. относительные ссылки изменяются). При перемещении формулы в другое место таблицы абсолютные и относительные ссылки в формуле не изменяются.
Основное назначение 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. Штрих можно ввести, нажав клавишу «Э» при активной английской раскладке.
Разумеется, синтаксис всех встроенных функций Excel запомнить невозможно, да и делать это не нужно, поскольку в повседневной практике для решения наиболее часто встречающихся задач вы будете использовать всего несколько встроенных функций.
Опции вставки в документ встроенных функций находятся на вкладке Формулы в группе Библиотека функций. Функции распределены по категориям в зависимости от того, для решения каких типов задач они предназначены. Назначение той или иной функции можно прочесть на всплывающей подсказке, которая появляется при наведении указателя мыши на имя функции с меню (рис. 17).
Если вы хотите просмотреть полный список встроенных функций Excel, нажмите кнопку Вставить функцию, которая находится в строке формул. В открывшемся окне Мастера функций выберите в раскрывающемся списке Категория пункт Полный алфавитный перечень и щелчком выделите имя функции, чтобы ниже прочитать о выполняемых ею действиях.
Название встроенной функции можно ввести с клавиатуры (что крайне нежелательно ввиду высокой вероятности ошибки), вставить из соответствующего меню кнопок, расположенных в группе Библиотека функций на вкладке Формулы или же из окна Мастера функций. О двух последних вариантах будет упомянуто ниже.
| Рис. 17. Просмотр назначения функции |
Часто применяемые на практике функции вынесены в меню кнопки , которая находится в группе Редактирование на вкладке Главная. Рассмотрим задачи, связанные с их использованием.
Простейшие расчеты
Функция суммирования данных является самой востребованной, именно поэтому задействовать ее в Excel проще всего.
Если данные расположены в одном столбце или строке, выделите их и нажмите кнопку . Результат сложения тут же отобразится внизу (в случае столбца) или справа (в случае строки) ряда. Выполните на нем щелчок, и в строке формул вы увидите, что Excel задействовал функцию =СУММ().
Если необходимо просуммировать значения из нескольких строк или столбцов (не важно, являются они смежными или нет), выделите ряды и вновь воспользуйтесь данной кнопкой. Суммы тут же отобразятся в следующей за рядом ячейке.
Для суммирования ячеек, находящихся в прямоугольной области или в разных частях документа, выполните следующие действия.
1. Выделите щелчком ячейку, в которой нужно отобразить результат суммирования.
2. Нажмите кнопку . При этом в ячейку будет помещена функция суммирования, а в ее скобках будет мигать курсор, указывающий на то, что нужно ввести аргументы.
3. Выделите нужный диапазон ячеек (при необходимости - несколько диапазонов, удерживая нажатой клавишу). При этом он будет охвачен бегущей рамкой, а скобках формулы появятся ссылки на диапазоны ячеек (рис. 18).
4. Нажмите для получения результата.
 |
| Рис. 18. Суммирование диапазона ячеек |
Даже после того, как ввод формулы будет завершен, вы всегда сможете отредактировать ее, изменив или добавив аргументы или функции. Так, чтобы изменить диапазон ячеек в только что рассмотренном примере, действуйте следующим образом.
1. Выполните двойной щелчок на ячейке, содержащей формулу. При этом диапазон, принимающий участие в расчетах, будет охвачен синей рамкой с маркерами в углах.
2. Подведите указатель мыши к нужному углу и, когда он примет вид двунаправленной стрелки, протащите границы рамки так, чтобы захватить новые ячейки (или, наоборот, исключить старые). При этом адрес диапазона в скобках формулы будет изменяться автоматически.
3. Нажмите для подсчета нового результата.
Щелкнув на стрелке кнопки , вы раскроете список команд, вызывающих функции, которые можно задействовать так же быстро, как и функцию суммирования. Схема действий при их использовании не отличается от последовательности шагов для функции суммирования. Ниже приводится краткое описание функций, вызываемых командами кнопки.
Результат работы некоторых из перечисленных функций можно видеть, не обращаясь непосредственно к самим функциям. Выделите интересующий вас диапазон и посмотрите вниз на строку состояния Excel. Слева от регулятора масштаба появятся значения суммы, количества ячеек в диапазоне и среднего арифметического (рис. 19).
Комплексные расчеты
Вы изучили примеры простейших расчетов в Excel. Теперь попробуйте разобраться с комплексной задачей, требующей комбинации действий, рассмотренных нами ранее.
Задача 1. Выбрать оптимальный тарифный план при подключении к сети сотовой связи, если в месяц планируется 2,5 часа разговоров внутри сети и 0,5 часа разговоров с абонентами городской сети и других сотовых операторов. Цены на услуги представлены в таблице на рис. 20 без учета НДС.
В первую очередь необходимо создать таблицу и внести в нее расценки на услуги сотовых операторов так, как это сделано на рис. 20. Для подсчета конечной суммы с учетом НДС в конце таблицы следует обозначить отдельный столбец.
Выделите ячейку А1 и наберите в ней название работы. В ячейках следующей строки создайте заголовки таблицы. Воспользуйтесь кнопкой Перенос текста из группы Выравнивание, чтобы отрегулировать высоту строки. Ширину столбцов подберите вручную перетаскиванием границ их заголовков. С помощью кнопок выравнивания поместите текст в «шапке» посередине (относительно верхней и нижней границы ячейки) и по центру (относительно левой и правой границ).
Далее заполните сетку таблицы значениями, представленными на рис. 20. Поскольку тарифы представлены в расчете за минуту связи, планируемое время разговоров также необходимо ввести в минутах в столбцах Звонки внутри сети и Звонки в другие сети. Заполнять оба столбца вручную не нужно. Достаточно ввести значение в первой ячейке, а затем скопировать его на остальные с помощью автозаполнения.
В последнем столбце Суммарно с учетом НДС 18% будут находиться расчетные формулы, к созданию которых мы сейчас и приступим. Внизу столбца создайте заголовок Оптимум, ниже которого поместите функцию расчета минимального значения ряда, чтобы определить наименьшую стоимость. Для этого выполните щелчок на стрелке кнопки , задействуйте команду Минимум, выделите диапазон пока еще пустых ячеек в столбце Суммарно с учетом НДС 18% и нажмите.
Суммарные затраты будут складываться из абонентской платы, стоимости разговоров внутри сети и с абонентами других сетей, а также из налога на добавленную стоимость. Это нужно представить в виде формулы. Начнем расчеты с первого тарифного плана Минутка. К содержимому ячейки В3 (абонплата) нужно прибавить произведение ячеек С3 и Е3 (общая стоимость разговоров внутри сети) и произведение ячеек D3 и F3 (общая стоимость разговоров с абонентами других сетей). Так будет подсчитана сумма затрат без учета НДС. Чтобы прибавить к полученной сумме налоговую ставку, необходимо умножить результат на 18% и прибавить его к стоимости услуг. Это результат и должен отобразиться в ячейках последнего столбца.
Выделите ячейку G3, наберите в ней знак «=» и нажмите кнопку . При этом будет вставлена функция суммирования, автоматически определен диапазон ячеек, а его адрес будет выделен в скобках функции. Поскольку данный диапазон нам не подходит, нажмите, чтобы удалить ссылку из скобок. Затем выполните щелчок на ячейке В3, чтобы поместить ее адрес в формулу, и поставьте «;» для отделения следующего аргумента (слагаемого). Далее щелкните на ячейке С3 для ввода ссылки на нее в формулу, наберите знак умножения «*» и выделите ячейку Е3. Отделите новый аргумент точкой с запятой. Для ввода последнего слагаемого щелкните на ячейке D3, введите «*» и выделите ячейку F3. Так мы создали часть формулы, которая отвечает за подсчет суммарных затрат без учета налогов. Она должна выглядеть следующим образом: =СУММ(B3;C3*E3;D3*F3). Если вы где-то допустили ошибку, установите курсор возле нее щелчком мыши или с помощью клавиш движения и внесите исправления. Далее нужно прибавить налоговую часть формулы. Переместите курсор в конец за скобку и наберите «+». Ставка НДС составляет 18%. Следовательно, нужно умножить 18% на результат, полученный в первой части формулы. Наберите 18% и знак умножения «*», затем выделите первую часть формулы, не включая знака «=» (точно так же, как вы бы выделили текстовый фрагмент в Word), выполните на ней правый щелчок и задействуйте команду Копировать контекстного меню. Щелчком установите курсор в конец формулы, выполните правый щелчок и обратитесь к команде Вставить. Расчетная формула готова: =СУММ(B3;C3*E3;D3*F3)+18%*СУММ(B3;C3*E3;D3*F3). Нажмите, чтобы увидеть итоговую сумму.
Набирать вручную формулу в остальных ячейках не нужно. Воспользуйтесь автозаполнением - выделите ячейку с формулой и протяните рамку за маркер вниз. В результате формула будет скопирована в нижние ячейки, а ссылки в ней автоматически заменятся на адреса ячеек из той же строки.
Обратите внимание, автоматически посчиталась и минимальная сумма затрат. В нашем случае она соответствует тарифному плану Будь на связи. Попробуйте изменить начальные условия задачи и задать другое количество планируемых минут разговоров внутри сети и с абонентами других операторов. Пересчет суммарных затрат будет выполнен мгновенно, и вполне вероятно, что для новых значений оптимальным окажется другой тарифный план.
В завершении работы отформатируйте таблицу, чтобы она имела презентабельный вид. В первую очередь стоит перевести ячейки с тарифами в денежный формат. Для этого выделите все диапазоны с денежными величинами в столбцах Абонплата, Минута внутри сети, Минута в другие сети, Суммарно с учетом НДС 18% и Оптимум, щелкните на стрелке раскрывающегося списка Числовой формат в группе Число и выберите пункт Денежный. Выделенные ячейки будут переведены в денежный формат, но поскольку в Excel по умолчанию отображается два десятичных знака, в конце каждого числа появится незначащий ноль. Чтобы скрыть его, выделите диапазоны и щелкните на кнопке Уменьшить разрядность . Для некоторых ячеек может понадобиться скрыть и второй ноль после запятой. Сделать это можно аналогичным образом.
Теперь примените к таблице один из встроенных стилей. Для этого выделите сам прямоугольник таблицы, не включая название задания и ячейки подсчета оптимума, щелкните на кнопке Форматировать как таблицу в группе Стили и выберите понравившийся эскиз. В появившемся окошке нужно установить флажок Таблица с заголовками и нажать ОК. При необходимости уменьшите ширину столбцов перетаскиванием границ заголовков.
В заключение отформатируйте название задания и ячейку с конечным результатом. Выделите ячейки, на которые по длине распространяется название, и щелкните на стрелке кнопки и задействуйте команду Объединить ячейки. Затем задайте для ячейки с названием определенные параметры шрифта и цвет фона с помощью инструментов группы Шрифт или примените к ней один из стилей, воспользовавшись кнопкой Стили ячеек в группе Стили. Оформите и «шапку» ячейки с оптимальной суммой. После выполнения всех операций форматирования таблица с расчетом должна принять вид, подобный тому, что представлен на рис. 21.
Относительная и абсолютная адресация
Адреса ячеек и диапазонов в Excel могут быть относительными и абсолютными. До сих пор мы говорили об относительных ссылках на ячейки и диапазоны, которые состоят только из номера строки и буквы столбца, например В2 или D4:D8. Преимущество относительной адресации состоит в том, что при копировании ячеек и использовании автозаполнения ссылки в скопированных формулах меняются автоматически (относятся к ячейкам текущей, а не исходной строки), поэтому нет необходимости набирать вручную каждую формулу. Наглядный пример: в предыдущем примере мы набрали только одну формулу в первой ячейке столбца Суммарно с учетом НДС 18%, а затем воспользовались автозаполнением. Однако на практике встречаются ситуации, когда адрес ячейки или диапазона необходимо зафиксировать, чтобы он не изменялся при копировании или автозаполнении ячеек. Для этого необходимо добавить перед номером строки и буквой столбца знак «$». Так, если сделать адрес ячейки В2 абсолютным, он будет выглядеть как $B$2. Также можно зафиксировать в ссылке адрес только столбца ($B2) или только строки (B$2). Это называется смешанной адресацией. Чтобы быстро изменить адресацию в готовой формуле, выполните по ней двойной щелчок, установите курсор на нужную ссылку и последовательно нажимайте клавишу для изменения типа адреса. Знак «$» можно добавлять в формулы и вручную с клавиатуры.
Рассмотрим использование абсолютной адресации на конкретном примере.
Вообразим следующую ситуацию. Вы являетесь оптовым поставщиком и для некоторых групп товаров, залежавшихся на складке, готовы предоставить скидку. Размер скидки варьируется в зависимости от того, насколько вы удачно договоритесь с покупателем. После переговоров вы должны предоставить ему прайс-лист, в котором цены будут скорректированы с учетом скидки, и по ним можно будет проводить дальнейшие расчеты.
В первую очередь вам необходимо сформировать сам прайс-лист со стандартными ценами, в одной из ячеек набрать размер скидки в процентах (его впоследствии можно будет варьировать), а также обозначить столбец, в ячейках которого будут находиться формулы, вычисляющие стоимость товара с учетом скидки. Пример такого прайс-листа приведен на рис. 22. Описание подробностей оформления опустим, поскольку вы уже приобрели в этом вопросе определенные навыки. Перейдем к сути проблемы.
Чтобы рассчитать цену с учетом скидки, необходимо от текущей цены отнять процент скидки. В нашем примере он равняется десяти. На первый взгляд решение задачи очень похоже на предыдущее, где прибавлялась процентная ставка НДС. Глядя на рис. 22, можно предположить, что в ячейке C4 необходимо из цены в ячейке В4 вычесть скидку, которая представляет собой произведение размера скидки из ячейки С1 на цену товара (В4). В виде формулы это запишется как =В4–С1*В4. Останется только распространить формулу на остальные ячейки столбца с помощью автозаполнения. Попробуйте выполнить описанные действия, и в конечном итоге вы обнаружите, что в вычислениях произошла ошибка. А заключается она в следующем.
При использовании автозаполнения ссылки в ячейках автоматически изменяются по мере копирования формул. В нашем случае формула, введенная в первую ячейку прайса, была правильной, но при попытке распространить ее на остальные ячейки столбца ссылка на ячейку с размером скидки стала меняться «сползая вниз» (превращаясь в С2, С3 и т.д.). Чтобы этого не происходило, ее адрес необходимо зафиксировать - сделать абсолютным. Для этого выполните на первой ячейке столбца скидки двойной щелчок (С4), поместите курсор на адрес ячейки, где находится величина скидки (в нашем случае это ячейка С1) и нажмите клавишу. При этом к номеру строки и букве столбца будет добавлен знак $ ($C$1), и адрес ячейки станет абсолютным - не изменится при автозаполнении столбца. В результате конечная формула будет выглядеть так: =В4–$C$1*В4. Теперь можно повторить процедуру автозаполнения, чтобы получить корректный результат. Щелкните на любой ячейке столбца скидок, чтобы убедиться в неизменности абсолютной ссылки. При изменении величины скидки весь ряд будет автоматически пересчитан.
Гораздо нагляднее использовать вместо абсолютной ссылки, содержащей знаки «$», имя ячейки, которое можно присвоить следующим образом: выделить щелчком ячейку и слева в строке формул набрать для нее уникальное имя. Щелкните на ячейке С1 (где указан размер скидки), слева в строке формул наберите Скидка. Затем в первой ячейке столбца цены со скидкой исправьте абсолютную ссылку $C$1 на имя ячейки Скидка. В результате должна получиться формула =В4–Скидка*В4. Осталось распространить формулу на все ячейки столбца с помощью автозаполнения.
В описанных выше примерах мы не упоминали об использовании кнопок группы Библиотека функций на вкладке Формулы и о Мастере функций для вставки встроенных функций в формулу. Эти моменты мы рассмотрим в примере расчета выручки от продажи товара, приведенном в подразделе «Построение графиков и диаграмм».
Ошибки в формулах
При работе с формулами в Excel нередко возникают ошибки, связанные не только с правильностью написания формулы, но и с корректным определением адресов ячеек и диапазонов с данными. Проведем краткий обзор способов выявления и устранения ошибок. Если Excel обнаружит ошибку в синтаксисе формулы (например, если между аргументами функции будет отсутствовать разделитель «;», будут пропущены или поставлены лишние скобки, обнаружится меньше аргументов, чем того требуется для данной функции), он выдаст сообщение об ошибке. Текст сообщения будет различным в зависимости от того, удалось Excel определить источник ошибки или нет. Когда система не может определить источник ошибки, появляется сообщение, представленное на рис. 23.
 |
| Рис. 23. Сообщение об ошибке, не распознанной Excel |
В этом случае вернитесь к ячейке с формулой, перепроверьте ее и исправьте ошибку.
Иногда системе удается определить, какие изменения следует внести в формулу, чтобы она стала синтаксически правильной. При этом появляется сообщение, подобное тому, что представлено на рис. 24. Проверьте, удовлетворяет ли предлагаемое системой исправление условиям расчета, и в зависимости от сделанного вывода, согласитесь с автоматическим исправлением, нажав Да, или же исправьте ошибку вручную в другом участке формулы.
 |
| Рис. 24. Сообщение о распознанной ошибке |
Иногда возникает ситуация, когда после введения формулы в ячейке вместо результата появляется текстовое сообщение об ошибке. Это связано с тем, что при проведении вычислений система столкнулась с каким-либо противоречием. Список наиболее часто встречающихся сообщений в ячейках приведен ниже.
Самая опасная ошибка - правильность ввода адресов ячеек и диапазонов в формулу. Система определяет только математические и синтаксические ошибки, но предугадать, данные каких именно ячеек должны присутствовать в формуле, не может. За эти должны внимательно следить вы.
Проще всего проверить правильность указания адресов ячеек и диапазонов в формуле можно следующим образом. Выполните двойной щелчок на ячейке содержащей формулу. При этом ячейки и диапазоны, входящие в нее, будут выделены в таблице рамками с маркерами, цвет которых соответствует цвету ссылки в формуле (рис. 25). Если был указан неправильный адрес, перетащите рамку на нужную ячейку (или измените ее размеры протаскиванием маркера при увеличении или уменьшении диапазона).
Строкой формул называется специальная строка, расположенная над заголовками столбцов и предназначенная для ввода и редактирования формул и иной информации. Фрагмент строки формул представлен на рисунке 3.4.
Рисунок 3.4 - Строка формул
Строка формул состоит из двух основных частей: адресной строки, которая расположена слева, и строки ввода и отображения информации. На рисунке 3.4 в адресной строке отображается имя последней использованной функции (в данном случае функции вычисления суммы), а в строке ввода и отображения информации - формула «=А1+5».
Адресная строка предназначена для отображения адреса выделенной ячейки либо диапазона ячеек, а также для ввода с клавиатуры требуемых адресов. Однако при выделении группы ячеек в адресной строке будет показан только адрес первой ячейки диапазона, расположенной в его левом верхнем углу.
В табличном редакторе Excel 2007 можно полностью автоматизировать выполнение расчетов, используя для этого тип данных «Формула». Формула - это специальный инструмент Excel 2007, предназначенный для расчетов, вычислений и анализа данных.
Формула начинается со знака «=», после чего следуют операнды и операторы. Список арифметических операторов приведен в таблице 3.1. Старшинство операций при вычислении формул Excel следующее:
− операторы связи (выполняется в первую очередь);
− оператор процент;
− унарный минус;
− оператор возведение в степень;
− операторы умножение и деление;
− операторы сложение и вычитание (в последнюю очередь). Таблица 3.1 – Символы для обозначения операторов в Excel
взять процент |
||
возведение в степень |
||
Операторы связи |
||
задание диапазона |
СУММ(А1:В10) |
|
объединение |
СУММ(А1;А3) |
Скобки в формулах Excel выполняют привычную, с точки зрения алгебры, роль указания приоритета вычисления той или иной части выражения. Например:
= 10*4+4^2 дает результат 56
= 10*(4+4^2) дает результат 200
Особенно внимательно надо расставлять скобки при задании унарного минуса. Например: = -10^2 дает результат 100, а =-(10^2) даст результат -100; - 1^2+1^2 дает результат 2, а 1^2-1^2 даст результат 0.
Если формула не может быть правильно вычислена, вместо ожидаемого результата Excel выводит в ячейку код ошибки (таблица 3.2).
Таблица 3.2 – Сообщения об ошибках при вычислении формул
Код ошибки |
Возможные причины |
В формуле делается попытка деления на нуль (пустые ячейки |
|
считаются нулями) |
|
Нет доступного значения |
|
Не распознается имя, использованное в формуле |
|
но пересечение двух областей, которые не имеют общих ячеек) |
|
В функции с числовым аргументом используется неприемле- |
|
мый аргумент |
|
Формула неправильно ссылается на ячейку |
|
Используется недопустимый тип аргумента |
Функции в Excel
Многие виды расчетов можно также выполнять с помощью специальных встроенных в Excel 2007 функций. Функция - это изначально созданная и заложенная в программу Excel процедура, которая выполняет в определенном порядке вычисления по заданным аргументам.
В состав каждой функции в обязательном порядке входят следующие элементы: имя или название (примеры имен - СУММ, СРЗНАЧ, СЧЕТ, МАКС и т. д.), а также аргумент (либо несколько аргументов), который задается в круглых скобках сразу после имени функции. Аргументами функций могут быть числа, ссылки, формулы, текст, логические величины и др.. Если аргумен-
тов у функции несколько, то они задаются через запятую. Если аргументов у функции нет, например, у функции ПИ (), то внутри скобок ничего не задается. Скобки позволяют определить, где начинается и где заканчивается список аргументов. Между названием функции и скобками ничего вставлять нельзя. Поэтому символ возведения функции в степень задается после записи аргумента. Например, SIN(A1)^3. Если правила записи функции нарушены, то Excel выдает сообщение о том, что в формуле имеется ошибка.
Вводить функции можно как в ручном, так и в автоматическом режиме. В последнем случае используют мастер функций, открываемый кнопкой Вставить функцию , которая расположена на ленте Excel 2007 на вкладке Формулы .
Все имеющиеся в программе функции для удобства работы сгруппированы в категории. Выбор категории осуществляется из раскрывающегося списка Категория , при этом в нижней части окна отображается перечень функций, входящих в эту категорию. Если выделить требуемую функцию и нажать кнопку ОК, то откроется о кно (его содержимое зависит от конкретной функции), в котором указываются аргументы функции.
В инженерных расчетах часто используются тригонометрические функции. Следует иметь в виду, что аргумент тригонометрической функции должен быть задан в радианах. Поэтому, если аргумент задан в градусах, его необходимо перевести в радианы. Это можно реализовать либо через формулу пересчета «=А1*ПИ()/180» (предполагается, что аргумент записан в ячейку с адресом А1), либо с помощью функции РАДИАНЫ(А1).
Пример . Записать формулу Excel= − для2 + вычисления3 1+e функции tg 3 (5 2 ) b .
Предполагая, что значение x задано в градусах и записано в ячейку А1, а значение b в ячейку B1, формула в ячейке Excel будет выглядеть следующим образом:
=(- (B1^2) + (1+exp(B1))^(1/3)) /TAN(5*РАДИАНЫ(А1)^2) ^3
Относительные и абсолютные адреса ячеек
Для записи в формулы Excel констант следует использовать абсолютную адресацию ячеек. В этом случае при копировании формулы в другую ячейку адрес ячейки с константой не изменится. Чтобы изменить в формуле относительный адрес ячейки В2 на абсолютный $B$2, следует последовательно нажить клавишу F4, либо вручную добавить символы доллара. Существуют также смешанные адреса ячеек (B$2 и $B2). При копировании формулы содержащей
смешанные адреса меняется только не зафиксированная (знаком $ слева) часть адреса.
При копировании формулы в соседнюю ячейку по строке в относительном адресе ссылки меняется буквенная составляющая. Например, ссылка А3 заменится ссылкой В3, а смешанный адрес $А1 при копировании вдоль строки не изменится. Соответственно, при копировании формулы в соседнюю ячейку по столбцу в относительном адресе ссылки меняется цифровая составляющая. Например, ссылка А1 заменится ссылкой А2, а смешанный адрес А$1 при копировании вдоль столбца не изменится.
Построение диаграмм
В Excel под термином диаграмма понимается любое графическое представление числовых данных. Построение диаграмм производится на основе ряда данных – группы ячеек с данными в пределах одной строки или столбца. На одной диаграмме можно отображать несколько рядов данных.
Наиболее простой способ построения диаграмм следующий: выделить один или несколько рядов данных, в группе Диаграммы вкладки Вставка ленты Excel выбрать нужный тип диаграммы. Диаграмма будет помещена на текущий лист рабочей книги. При необходимости ее можно перенести на другой лист с помощью команды Переместить диаграмму вкладки Конструктор Работы с диаграммами. С помощью вкладки Макет Работы с диаграммами
можно изменить внешний вид диаграммы: добавить название диаграммы, осей, изменить шрифты и т.п.. При необходимости можно изменить подписи на горизонтальной оси. Для этого в контекстном меню диаграммы следует выбрать ко-
манду Выбрать данные и в диалоговом окне Выбор источника данных (рису-
нок 3.5) изменить подписи горизонтальной оси.
Рисунок 3.5 – Окно для изменения данных на оси Х
Программа Microsoft Excel это не только большая таблица, но еще и суперсовременный калькулятор с множеством функций и возможностей. В этом уроке мы научимся пользоваться им по назначению.
Все вычисления в Excel называются формулы , и все они начинаются со знака равно (=).
Например, я хочу посчитать сумму 3+2. Если я нажму на любую ячейку и внутри напечатаю 3+2, а затем нажму кнопку Enter на клавиатуре, то ничего не посчитается - в ячейке будет написано 3+2. А вот если я напечатаю =3+2 и нажму Enter, то в всё посчитается и будет показан результат.
Запомните два правила:
Все вычисления в Excel начинаются со знака =
После того, как ввели формулу, нужно нажать кнопку Enter на клавиатуре
А теперь о знаках, при помощи которых мы будем считать. Также они называются арифметические операторы:
Сложение
Вычитание
* умножение
/ деление. Есть еще палочка с наклоном в другую сторону. Так вот, она нам не подходит.
^ возведение в степень. Например, 3^2 читать как три в квадрате (во второй степени).
%
процент. Если мы ставим этот знак после какого-либо числа, то оно делится на 100. Например, 5% получится 0,05.
При помощи этого знака можно высчитывать проценты. Если нам нужно вычислить пять процентов из двадцати, то формула будет выглядеть следующим образом: =20*5%
Все эти знаки есть на клавиатуре либо вверху (над буквами, вместе с цифрами), либо справа (в отдельном блоке кнопок).
Для печати знаков вверху клавиатуры нужно нажать и держать кнопку с надписью Shift и вместе с ней нажимать на кнопку с нужным знаком.
А теперь попробуем посчитать. Допустим, нам нужно сложить число 122596 с числом 14830. Для этого щелкните левой кнопкой мышки по любой ячейке. Как я уже говорил, все вычисления в Excel начинаются со знака «=». Значит, в ячейке нужно напечатать =122596+14830
И для того, чтобы получить ответ, нужно нажать кнопку Enter на клавиатуре. После чего в ячейке будет уже не формула, а результат.
А теперь обратите внимание вот на такое верхнее поле в программе Эксель:
Это «Строка формул». Она нам нужна для того, чтобы проверять и изменять наши формулы.
Для примера нажмите на ячейку, в которой мы только что посчитали сумму.
И посмотрите на строку формул. В ней будет показано, как именно мы получили данное значение.
То есть, в строке формул мы видим не само число, а формулу, при помощи которой это число получилось.
Попробуйте в какой-нибудь другой ячейке напечатать цифру 5 и нажать Enter на клавиатуре. Затем щелкните по этой ячейке и посмотрите в строку формул.
Так как это число мы просто напечатали, а не вычислили при помощи формулы, то только оно и будет в строке формул.
Как правильно считать
Но, как правило, этот способ «счета» используется не так часто. Существует более продвинутый вариант.
Допустим, есть вот такая таблица:
Начну с первой позиции «Сыр». Щелкаю в ячейке D2 и печатаю знак равно.
Затем нажимаю на ячейку B2, так как нужно ее значение умножить на C2.
Печатаю знак умножения *.
Теперь щелкаю по ячейке C2.
И, наконец, нажимаю кнопку Enter на клавиатуре. Все! В ячейке D2 получился нужный результат.
Щелкнув по этой ячейке (D2) и посмотрев в строку формул, можно увидеть, как получилось данное значение.
Объясню на примере этой же таблицы. Сейчас в ячейке B2 введено число 213. Удаляю его, печатаю другое число и нажимаю Enter.
Посмотрим в ячейку с суммой D2.
Результат изменился. Это произошло из-за того, что поменялось значение в B2. Ведь формула у нас следующая: =B2*C2
Это означает, что программа Microsoft Excel умножает содержимое ячейки B2 на содержимое ячейки C2, каким бы оно не было. Выводы делайте сами:)
Попробуйте составить такую же таблицу и вычислить сумму в оставшихся ячейках (D3, D4, D5).