Занятие 3. Работа с формулами и функциями
В той ячейке, где находится формула, виден только результат вычислений над данными, содержащимися в заданных ячейках. Если меняются значения в ячейках, то автоматически меняется и результат в формуле. Саму формулу можно увидеть в строке формул, если сделать активной ячейку содержащую ее. Функция представляет собой выражение с уникальным именем, для которого пользователь должен задать конкретные значения аргументов. Аргументы указываются в скобках после имени функции. Функцию можно считать частным случаем формулы.
Ввод формулы в ячейку. Чтобы научиться вводить формулы в ячейки, создадим накладную, содержащую четыре графы: наименование товара, цена, количество и сумма. Сумма — это вычисляемая величина, поэтому нам необходимо в ячейки с суммой ввести формулы.
Начнем с оформления документа.
1. Заполните ячейки значениями, приведенными в табл.
2.
В ячейку D4 введем формулу =В4*С4. Можно
ввести формулу, набрав адреса ячеек с
клавиатуры, но гораздо удобнее вводить
формулы с помощью мыши (причем ячейки
могут находиться на разных листах):
в ячейке D4 наберите знак =;
щелкните в ячейке В4 (обратите внимание, что ее адрес при этом попал в редактируемую ячейку);
наберите математический знак умножить (*);
щелкните во второй ячейке, которая участвует в формуле — С4;
нажмите Enter.
После заполнения граф Цена и Количество в графе Сумма автоматически появится результат.
Если изменить значения граф Количество и Цена, в графе Сумма результат будет автоматически пересчитан. Если при вводе формулы была допущена ошибка, то в ячейке вместо результата будет выведено сообщение об ошибке. Сообщения об ошибке могут быть следующими:
В формуле может указываться диапазон ячеек: он показывается двоеточием. Например, диапазон ячеек от А1 до А10 будет указан так: А1:А10. В формулах могут также использоваться операторные скобки и логические функции. Например, при вычислении подоходного налога формула будет выглядеть так:
=(СЗ-400)*0,13,
где в ячейке с адресом СЗ находится оклад сотрудника.
Функции. Функции Excel — это специальные формулы, хранящиеся в памяти программы. Каждая функция включает две части: имя функции и ее аргументы. Аргументы — это данные, которые используются функцией для получения результата. Аргументом функции могут быть имена ячеек, текст, числа, дата, время. Аргументы указываются в круглых скобках справа от имени функции и разделяются точкой с запятой. У некоторых функций может не быть аргументов. Чтобы познакомиться с набором функций Excel, щелкните на кнопке панели инструментов Вставка функции. Откроется окно Мастера функций. Первые две категории функций — это группа Последние использовавшиеся (в списке10 функций, которые использовались последними) и Полный алфавитный перечень, включающий все функции, перечисленные в алфавитном порядке. Следующая группа функций — Финансовые. В этой группе перечислены специальные функции, вычисляющие финансовые величины: проценты во вкладу или кредиту, амортизационные отчисления, норму прибыли и тому подобные величины. При выборе конкретной функции в окне мастера появляется информационная строка, описывающая действие выбранной функции. Рассмотрим примеры использования некоторых наиболее часто используемых функций.
Функция СУММ. Наиболее простой способ суммирования — сделать активной ячейку, в которую необходимо поместить сумму, и нажать кнопку Автосумма на панели инструментов. Таким образом, удобно суммировать данные в столбцах и строках. Если суммируются ячейки, которые расположены в разных столбцах, удобнее воспользоваться Мастером функций. Откройте окно Мастера функций, выберите необходимую категорию — Математические, затем выберите функцию — СУММ и нажмите OK. В открывшемся диалоговом окне наберите адрес ячейки или выберите его щелчком мыши на рабочем листе.
Для того чтобы лист стал
доступным, щелкните на кнопке
справа в поле ввода.
Выделите ячейки листа и
вернитесь в окно Мастера функций, нажав
кнопку
справа в свернутой строке ввода.
Примеры использования некоторых функций: СУММ(СЗ:Р5) — суммируются числа из диапазона C3-F5, СРЗНАЧ(А1:АЗ;ВЗ:В5) — вычисляется среднее значение всех чисел из указанных диапазонов.
ОКРУГЛ(М8;2) — округляет значение ячейки М8 до второго знака после запятой.
Относительная и абсолютная адресация
Особенностью электронной таблицы является возможность автоматического изменения адресов ячеек при копировании и перемещении формул. Вернемся к примеру с накладной. Скопируем формулу из ячейки D5 в ячейку D6. Самым быстрым способом копирования является копирование с помощью маркера в нижнем правом углу выделенной ячейки: подведите указатель мыши к маркеру и, удерживая левую кнопку, перетащите формулу в соседние ячейки. Обратите внимание: при копировании в формулах автоматически изменяются адреса ячеек. В строке формул после копирования показана формула, в самой ячейке — результат выполнения формулы.
Бывают случаи, когда не нужно изменять ссылки (адреса ячеек), использующиеся в формуле. Тогда применяется специальное имя, которое называется абсолютным. В этом случае в адрес ячейки вносится знак абсолютной адресации $:
• $С$7 — абсолютная адресация: при копировании адрес ячейки не меняется;
• В$4 — частичная абсолютная адресация: при копировании не меняется номер строки;
• $В4 — частичная абсолютная адресация: при копировании не меняется номер столбца.
1. Создайте таблицу согласно рисунку.
2. Задайте Денежный формат ячеек в столбце «Цена», выбрав обозначение «р.».
Допустим, необходимо сосчитать сумму в Евро и при этом учесть возможность изменения курса.
3. Введите в ячейку B1 курс Евро (например, 35). Задайте Денежный формат этой ячейки, выбрав обозначение «р.».
4. Добавьте к таблице еще один столбец «Сумма в Евро» и укажите в нем (в ячейке E4) формулу =D4/$B$1. Абсолютный адрес ячейки можно получить, указав в формуле ячейку и нажав затем функциональную клавишу F4. При последующих нажатиях F4 будут выведены частичные абсолютные адреса.
5. С помощью автозаполнения скопируйте введенную формулу в ячейки E5 и E6. Убедитесь, что при копировании формулы в графе «Сумма в Евро» адрес ячейки B1 не изменится.
6. Задайте Денежный формат ячеек в столбце «Сумма в Евро», выбрав обозначение «€».
Контрольное задание [Селезнева Р.С.]
Каждого физического лица в нашей стране касается такой федеральный налог, как государственная пошлина. Госпошлина – это обязательный платеж, взимаемый за совершение юридически значимых действий либо за выдачу документов уполномоченными на это организациями или должностными лицами. Так, например, госпошлина за совершение нотариальных действий нотариусами государственных нотариальных контор или должностными лицами органов исполнительной власти и консульских учреждений, вычисляется по следующей таблице:
Подготовить таблицу в электронном виде (расположите данные в тех же ячейках, что и на образце).
Установить ширину столбцов C – 20,14; D – 21,57; E – 18,43.
Заголовок «Минимальная оплата труда» оформить: шрифт Times Roman, размер 13, полужирный курсив.
Ячейки D5 и E5, объединить и установить перенос по словам; оформить: шрифт Times Roman, размер 12, полужирный, выравнивание по центру.
Содержимое ячеек D6 и E6 оформить: шрифт Calibri, размер 11, выравнивание по центру.
К ячейкам D8, E8, D10, E3, Е10 применить денежный формат числа (число десятичных знаков 0).
К ячейкам D7, E7, D9, E9 применить процентный формат (число десятичных знаков 0).
Ввести в эти ячейки данные.
Содержимое ячеек С8, С10 оформить: шрифт Calibri, размер 11, выравнивание по центру.
В ячейку Е3 введите значение минимальной заработной платы – 6890.
В ячейку D8 ввести формулу = D7*E3. Проставьте недостающие значки абсолютной адресации.
Скопируйте формулу вправо (с помощью маркера автозаполнения).
Выделите ячейку D8 и скопируйте (правка – копировать) в ячейку D10.
Распространите формулу вправо.
Таблицу оформите по образцу, используя обрамление, заливку и узор.