Вычисления в Excel
Создание простых формул
Формула - это математическое выражение, которое создается для вычисления результата и которое может зависеть от содержимого других ячеек. Формула в ячейке может содержать данные, ссылки на другие ячейки, а также обозначение действий, которые необходимо выполнить.
Использование ссылок на ячейки позволяет пересчитывать результат по формулам, когда происходят изменения содержимого ячеек, включенных в формулы.
В Excel формулы начинаются со знака =. Скобки ( ) могут использоваться для определения порядка математических операции.
Excel поддерживает следующие арифметические операции:
- сложение (+);
- умножение (*);
- нахождение процента (%);
- вычитание (-);
- деление (/);
- экспонента (^).
Операторы сравнения:
- = равно;
- < меньше;
- > больше;
- <= меньше или равно;
- >= больше или равно;
- <> не равно.
Операторы связи:
- : диапазон;
- ; объединение;
- & оператор соединения текстов.
Таблица 22. Примеры формул
| Формула | Составляющие элементы |
|
=27+36 =А1+А2-АЗ =45%*АЗ , |
Цифры Ссылки на ячейки Цифры и ссылки на ячейки |
|
=СУММ(А1:А5), =МАКС(АЗ:А5) |
Предварительно определенные функции |
| =(А1+А2)/АЗ | Ссылки на ячейки при заданном порядке действий |
Упражнение
Вставка формулы -25-А1+АЗ
Предварительно введите любые числа в ячейки А1 и A3.
1. Выберите необходимую ячейку, например В1.
2. Начните ввод формулы со знака=.
3. Введите число 25, затем оператор (знак -).
4. Введите ссылку на первый операнд, например щелчком мыши на нужную ячейку А1.
5. Введите следующий оператор(знак +).
6. Щелкните мышью в той ячейке, которая является вторым операндом в формуле.
7. Завершите ввод формулы нажатием клавиши Enter. В ячейке В1 получите результат.
Автосуммирование
Кнопка Автосумма (AutoSum) - ? может использоваться для автоматического создания формулы, которая суммирует область соседних ячеек, находящихся непосредственно слева в данной строке и непосредственно выше в данном столбце.
1. Выберите ячейку, в которую надо поместить результат суммирования.
2. Щелкните кнопку Автосумма - ? или нажмите комбинацию клавиш Alt+=. Excel примет решение, какую область включить в диапазон суммирования, и выделит ее пунктирной движущейся рамкой, называемой границей.
3. Нажмите Enter для принятия области, которую выбрала программа Excel, или выберите с помощью мыши новую область и затем нажмите Enter.
Функция "Автосумма" автоматически трансформируется в случае добавления и удаления ячеек внутри области.
Упражнение
Создание таблицы и расчет по формулам
1. Введите числовые данные в ячейки, как показано в табл. 23.
Таблица 23. Исходная таблица данных
| А | В | С | D | Б | F | |
| 1 | Распределение сотрудников по образованию | |||||
| 2 | Магнолия | Лилия | Фиалка | Всего | ||
| 3 | Высшее | 25 | 20 | 9 | ||
| 4 | Среднее спец. | 28 | 23 | 21 | ||
| 5 | ПТУ | 27 | 58 | 20 | ||
| в | Другое | 8 | 10 | 9 | ||
| 7 | Всего | |||||
| 8 | Без высшего |
Выберите ячейку В7, в которой будет вычислена сумма по вертикали.
3. Щелкните кнопку Автосумма - ? или нажмите Alt+=.
4. Повторите действия пунктов 2 и 3 для ячеек С7 и D7.
Вычислите количество сотрудников без высшего образования (по формуле В7-ВЗ).
5. Выберите ячейку В8 и наберите знак (=). 6. Щелкните мышью в ячейке В7, которая является первым операндом в формуле.
7. Введите с клавиатуры знак (-) и щелкните мышью в ячейке ВЗ, которая является вторым операндом в формуле (будет введена формула).
8. Нажмите Enter (в ячейке В8 будет вычислен результат).
9. Повторите пункты 5-8 для вычислений по соответствующим формулам в ячейках С8 и 08.
10. Сохраните файл с именем Образование_сотрудников.х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. Выберите ячейку, содержащую формулу для тиражирования.
2. Перетащите маркер заполнения в нужном направлении. Формула будет размножена во всех ячейках.
Обычно этот процесс используется при копировании формул внутри строк или столбцов, содержащих однотипные данные.
При тиражировании формул с помощью маркера заполнения меняются так называемые относительные адреса ячеек в формуле (подробно относительные и абсолютные ссылки будут описаны далее).
Упражнение
Тиражирование формул
1.Откройте файл Образование_сотрудников.х1s.
2. Введите в ячейку ЕЗ формулу для автосуммирования ячеек =СУММ(ВЗ:03).
3. Скопируйте, перетащив маркер заполнения, формулу в ячейки Е4:Е8.
4. Просмотрите как меняются относительные адреса ячеек в полученных формулах (табл. 25) и сохраните файл.
Таблица 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) |
Формулы, реализующие вычисления в таблицах, для адресации ячеек используют так называемые ссылки. Ссылка на ячейку может быть относительной или абсолютной.
Использование относительных ссылок аналогично указанию направления движения по улице - "идти три квартала на север, затем два квартала на запад". Следование этим инструкциям из различных начальных мест будет приводить в разные места назначения.
Обычно ссылки на ячейки описываются и используются как относительные (формат записи А1). Когда формула, содержащая эти ссылки, копируется, происходит изменение формулы для поддержания относительности ссылок.
Например, формула, которая суммирует числа в столбце или строке, затем часто копируется для других номеров строк или столбцов. В таких формулах используются относительные ссылки (см. предыдущий пример в табл. 25).
Абсолютная ссылка на ячейку .иди область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца.
При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Например, когда формула вычисляет доли от общей суммы, ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.
Абсолютная ссылка может быть создана только при наборе формулы, перед адресом строки и столбца вводится знак доллара - $.
Для создания абсолютной ссылки удобно использовать клавишу абсолютной ссылки F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот.
Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 - так называемые смешанные ссылки).
Упражнение
Создание абсолютной ссылки
Создайте таблицу, аналогичную представленной ниже.
Таблица 26. Расчет зарплаты
| A | B | C | |
| 1 | Часовая ставка | 100р. | |
| 2 | Ф. И. 0 . | Часов | Зарплата |
| 3 | Иванов | 40 | |
| 4 | Петров | 30 | |
| 5 | Сидоров | 25 |
При тиражировании формулы данного примера с относительными ссылками в ячейке С4 появляется сообщение об ошибке (#ЗНАЧ!), так как изменится относительный адрес ячейки В1, и в ячейку С4 скопируется формула =В2*В4;
3. Задайте абсолютную ссылку на ячейку В1, поставив курсор в строке формул на В1 и нажав клавишу F4, Формула в ячейке СЗ будет иметь вид =$В$1*ВЗ.
4. Скопируйте формулу в ячейки С4 и С5.
5. Сохраните файл (табл. 27) под именем Зарплата.xls.
Таблица 27. Итоги расчета зарплаты
| A | B | C | |
| 1 | Часовая ставка | 100р. | |
| 2 | ФИО | Часов | Зарплата |
| 3 | Иванов | 40 | 4000р. |
| 4 | Петров | 30 | 3000р. |
| 5 | Сидоров | 25 | 2500р. |
Имена в формулах легче запомнить, чем адреса ячеек, поэтому вместо абсолютных ссылок можно использовать именованные области (одна или несколько ячеек). Необходимо соблюдать следующие правила при создании имен:
- имена могут содержать не более 255 символов;
- имена должны начинаться с буквы и могут содержать любой символ, кроме пробела;
- имена не должны быть похожи на ссылки, такие, как ВЗ, С4;
- имена не должны использовать функции Excel, такие, как СУММ, ЕСЛИ и т. п.
Команда Создать позволяет задать (ввести) требуемое имя (только одно), команда Присвоить использует метки, размещенные на рабочем листе, в качестве имен областей (разрешается создавать сразу несколько имен).
Создание имени
1. Выделите ячейку В1 (табл. 26).
2. Выберите в меню Вставка, Имя (Insert, Name) команду Присвоить (Define).
3. Введите имя Часовая ставка и нажмите ОК.
4. Выделите ячейку В1 и убедитесь, что в поле имени указано Часовая ставка.
Создание нескольких имен
1. Выделите ячейки ВЗ:С5 (табл. 27).
2. Выберите в меню Вставка, Имя (Insert, Name) команду Создать (Create), появится диалоговое окно Создать имена (рис. 88).
3. Убедитесь, что переключатель в столбце слева помечен и нажмите ОК.
4. Выделите ячейки ВЗ:СЗ и убедитесь, что в поле имени указано Иванов.
Рис. 88. Диалоговое окно Создать имена
Можно в формулу вставить имя вместо абсолютной ссылки.
1. В строке формул установите курсор в то место, где будет добавлено имя.
2. Выберите в меню Вставка, Имя (Insert, Name) команду Вставить (Paste), появится диалоговое окно Вставить имена.
3. Выберите нужное имя из списка и нажмите ОК.
Ошибки в формулах
Бели при вводе формул или данных допущена ошибка, то в результирующей ячейке появляется сообщение об ошибке. Первым символом всех значений ошибок является символ #. Значения ошибок зависят от вида допущенной ошибки.
Excel может распознать далеко не все ошибки, но те, которые обнаружены, надо уметь исправить.
Ошибка # # # # появляется, когда вводимое число не умещается в ячейке. В этом случае следует увеличить ширину столбца.
Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на нуль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.
Ошибка #Н/Д! является сокращением термина "неопределенные данные". Эта ошибка указывает на использование в формуле ссылки на пустую ячейку.
Ошибка #ИМЯ? появляется, когда имя, используемое в формуле, было удалено или не было ранее определено. Для исправления определите или исправьте имя области данных, имя функции и др.
Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. Чаще всего ошибка указывает, что допущена ошибка при вводе ссылок на диапазоны ячеек.
Ошибка #ЧИСЛО! появляется, когда в функции с числовым аргументом используется неверный формат или значение аргумента.
Ошибка #ССЫЛКА! появляется, когда в формуле используется недопустимая ссылка на ячейку. Например, если ячейки были удалены или в эти ячейки было помещено содержимое других ячеек.
Ошибка #ЗНАЧ! появляется, когда в формуле используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения для оператора или функции введен текст.
Кроме перечисленных ошибок, при вводе формул может появиться циклическая ссылка.
Циклическая ссылка возникает тогда, когда формула прямо или косвенно включает ссылки на свою собственную ячейку. Циклическая ссылка может вызывать искажения в вычислениях на рабочем листе и поэтому рассматривается как ошибка в большинстве приложений. При вводе циклической ссылки появляется предупредительное сообщение (рис. 89).
Рис. 89. Циклическая ссылка
Индикатор циклической ссылки в строке состояния показывает ««ссылку на последнюю зависимую формулу.
Когда формула, содержащая циклическую ссылку, находится на рабочем листе, появится сообщение, указанное на рис. 89.
Для исправления ошибки удалите ячейку, которая вызвала циклическую ссылку, отредактируйте или введите заново формулу.
Функции в Excel
Более сложные вычисления в таблицах Excel осуществляются с помощью специальных функций (рис. 90). Список категорий функций доступен при выборе команды Функция в меню Вставка (Insert, Function).
Финансовые функции осуществляют такие расчеты, как вычисление суммы платежа по ссуде, величину выплаты прибыли на вложения и др.
Функции Дата и время позволяют работать со значениями даты и времени в формулах. Например, можно использовать в формуле текущую дату, воспользовавшись функцией СЕГОДНЯ.
Рис. 90. Мастер функций
Математические функции выполняют простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, абсолютной величины числа, округление чисел и др.
Статистические функции позволяют выполнять статистический анализ данных. Например, можно определить среднее значение и дисперсию по выборке и многое другое.
Функции Ссылки и массивы позволяют осуществить поиск данных в списках или таблицах, найти ссылку на ячейку в массиве. Например, для поиска значения в строке таблицы используется функция ГПР.
Функции работы с базами данных можно использовать для выполнения расчетов и для отбора записей по условию.
Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ.
Логические функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.
Функции Проверка свойств и значений предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ.
Для вычислений в таблице с помощью встроенных функций рекомендуется использовать мастер функций. Диалоговое окно мастера функций доступно при выборе команды Функция в меню Вставка или нажатии кнопки
В процессе диалога с мастером требуется задать аргументы выбранной функции, для этого необходимо заполнить поля в диалоговом окне соответствующими значениями или адресами ячеек таблицы.
Упражнение
Вычисление величины среднего значения для каждой строки в файле Образование.хls.
1. Выделите ячейку F3 и нажмите на кнопку мастера функций.
2. В первом окне диалога мастера функций из категории Статистические выберите функцию СРЗНАЧ, нажмите на кнопку Далее.
3. Во втором диалоговом окне мастера функций должны быть заданы аргументы. Курсор ввода находится в поле ввода первого аргумента. В это поле в качестве аргумента число! введите адрес диапазона B3:D3 (рис. 91).
4. Нажмите ОК.
5. Скопируйте полученную формулу в ячейки 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 | |
| 8 | Без высшего | 63 | 91 | 50 | 208 |
Ввод аргументов можно выполнить и с клавиатуры.
 |
 |
 |
