Внешний вид рабочего листа

Лабораторная работа № 2.

Создание формул. Элементарные операции с данными. Адресация ячеек в формуле.

Цели работы:

1. Научиться использовать возможности Excel для автоматизации расчетов.

2. Познакомиться с видами адресации, используемыми в формулах.

3. Закрепить приобретенные навыки по заполнению и форматированию таблиц.

Краткие теоретические сведения

Основное преимущество электронных таблиц заключается в возможности помещать в них не только данные, но и формулы.

Правила создания формул:

1. Любая формула должна начинаться со знака «=». Вводимая формула отображается в строке формул, находящейся под панелями инструментов Excel (см.рис.1 в лабораторной работе №1).

2. Формула может включать константы, знаки операций, функции, адреса ячеек.

3. В Excel допустимы следующие операторы:

-- смена знака

%- операция процента (применима к отдельному числу),

^- операция возведения в степень,

*,/- умножение, деление,

+,-- сложение, вычитание.

Операции выполняются слева-направо в порядке их приоритетов, которые могут быть изменены круглыми скобками.

Примеры формул:

Формулы в обычной записи	Формулы, введенные в ячейки таблицы
	=A5/(C7-4)+(4+F4)/(8-D5)*2,4
2 + sin x2	= 2 + sin(x^2)

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

Для ссылки на ячейки используется абсолютная и относительная адресация. Оба способа указывают на одни и те же объекты – на ячейки. Их особенности проявляются при копировании.

ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ.

Пусть в ячейке С3 (рис. 4) записана формула =A1+B2. Если выполнить ее копирование в разные стороны, мы увидим, что она изменяется. При копировании по горизонтали изменяется имя столбца, при копировании по вертикали – номер строки, при копировании по диагонали – и то и другое. Так при копировании в направлении стрелки формула перемещается на два столбца вправо. Поэтому адреса столбцов получили приращение 2 – вместо А - С, вместо В - D. Также формула перемещается на две строки вниз. Поэтому адреса строк также увеличились на 2.

Это правило изменения формул при копировании достаточно часто удовлетворяет пользователя. Ему не приходится вручную создавать множество однотипных формул, которые должны отличаться только номерами ячеек. Но так бывает не всегда.

АБСОЛЮТНАЯ АДРЕСАЦИЯ.

Чтобы предотвратить автоматическое изменение адресов, перед «замораживаемой» координатой нужно поставить знак $ и превратить ее в абсолютную ссылку. Знак $ может быть установлен и только перед одной координатой (только перед номером столбца или только перед номером строки) или перед обоими координатами сразу. Например, формула =$A$1 не будет изменяться ни при каком копировании. В формуле =A$1 при копировании не будет изменяться адрес строки, а в формуле =$A1 не будет изменяться номер столбца.

Знак $ можно вводить непосредственно с клавиатуры, а можно с помощью клавиши F4 в режиме ввода формулы для текущей ссылки. Последовательное нажатие этой клавиши влечет поочередный ввод знака $ перед элементами адреса: A1 Þ $A$1 Þ A$1 Þ $A1 Þ A1. Поэтому для получения желаемого результата следует последовательно нажимать F4.

Пример задания.

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

Исходные данные: известны площадь каждой квартиры и количество проживающих. Известны также тарифы оплаты за газ и отопление.

Правила расчета:

Оплата = количество жильцов * тариф за газ + площадь квартиры * тариф за отопление.

Планируемый результат работы приведен на рис.5.

Рис.5. Таблица расчета квартплаты.

Порядок работы

Этап №1. Занести исходные данные в таблицу. Для этого:

èВвести в ячейку А1 название таблицы «Тарифы оплаты»

èВвести в ячейку А2 название услуги «Газ»

èВвести в ячейку В2 тариф за газ – 2.

èВвести в ячейку С2 название услуги «Отопление». При необходимости задать ширину столбца так, чтобы название полностью помещалось в ячейке.

èВвести в ячейку D2 тариф за отопление – 5

èВвести в ячейку А4 название таблицы «Квартплата»

èВвести в ячейку А5 название столбца таблицы «Квартира». Подобрать ширину столбца так, чтобы название полностью помещалось в ячейке.

èВвести в ячейку В5 название столбца «Площадь». Подобрать ширину столбца.

èВвести в ячейку С5 название столбца «Оплата». Подобрать ширину столбца.

èВвести в ячейку D5 название столбца «Жильцов». Подобрать ширину столбца.

èВвести в ячейки A6-A9 номер квартир с помощью автозаполнения следующим образом: в ячейку А6 ввести №1, в А7 - №2; выделить эти ячейки, «взяться» за маркер заполнения в правом нижнем углу выделения и охватить рамкой ячейки А8 и А9. При этом справа от указателя мыши отображается следующий номер.

èВвести в ячейки В6-В9 значения площади соответствующих квартир.

èВвести в ячейки С6-С9 количество жильцов каждой квартиры.

Этап №2. Занести в таблицу формулу для расчета оплаты квартиры №1.

èВ ячейку D6 для начала формирования формулы расчета оплаты первой квартиры ввести знак равенства.

èЩелкнуть левой кнопкой мыши ячейку с количеством жильцов квартиры №1 – это С6. Адрес С6 отобразиться в строке формул после знака равенства.

èВвести в формулу с клавиатуры знак умножения *.

èЩелкнуть левой кнопкой мыши ячейку с тарифом за газ – это В2. Адрес В2 отобразиться в строке формул после знака *.

èВвести в формулу с клавиатуры знак сложения +.

èЩелкнуть левой кнопкой мыши ячейку с площадью первой квартиры – это В6. Адрес В6 отобразиться в строке формул после знака +.

èВвести в формулу с клавиатуры знак умножения *.

èЩелкнуть левой кнопкой мыши ячейку с тарифом за отопление – это D2. Адрес D2 отобразиться в строке формул после знака *.

èЗавершить ввод формул нажатием Enter.

Замечание. Получившаяся формула использует только относительную адресацию.

Этап №3. Изучить особенности абсолютной и относительной адресации.

Введенная в ячейку D6 формула теперь должна повториться в ячейках D7-D9 с корректировкой на площадь и количество жильцов. Пробуем ее скопировать.

èСкопировать содержимое ячейки D6 в буфер обмена.

èВыделить ячейки D7-D9.

èВставить содержимое буфера обмена в выделенные ячейки. Вид экрана при этом представлен на рис.6.

Рис.6. Документ после размножения формулы оплаты, использующей только
относительную адресацию.

Видим, что расчет оплаты остальных квартир производится неверно. Рассмотрим, почему. Сравним, из каких ячеек выбираются данные для расчета квартир №1 и №2. Для этого:

èАктивизировать ячейку D6.

èЩелкнуть в строке формул (см.рис.7). Excel цветными рамками выделяет ячейки, значения которых используются в формуле. Получившийся вид экрана представлен на рис.7.

Рис.7.Выделение ячеек, используемых в формуле активной ячейки, производимое
после щелчка в строке формул.

Видим, что для формулы первой квартиры все данные выбираются правильно. Сравним с формулой для второй квартиры.

èАктивизировать ячейку D7.

èЩелкнуть в строке формул левой кнопкой мыши. Excel цветными рамками выделяет ячейки, используемые в формуле ячейки D7. Получившийся вид экрана представлен на рис.8.

Рис.8.Выделение ячеек, используемых в формуле ячейки D7.

Видим, что формула изменилась. Рассмотрим и проанализируем эти изменения.

На первом и третьем местах в формуле должны подставляться данные о количестве жильцов и площадь соответствующей квартиры: в исходной формуле – для квартиры №1, в формуле ячейки D7 – квартиры №2. При копировании исходной формулы в ячейку D7 эти ссылки изменились: В6 на В7, а С6 на С7. Это произошло потому, что они были заданы с помощью относительной адресации. Формула из D6 была скопирована по вертикали вниз на одну строку в ячейку D7. Поэтому номера строк увеличились на 1, а номера столбцов не изменились. Это изменение нас полностью устраивает

На втором и четвертом местах в формуле должны подставляться данные о тарифах оплаты за коммунальные услуги из ячеек В2 и D2. Для ячейки D7 мы видим, что тарифы выбираются неправильно из пустых ячеек В3 и D3., т.к. при копировании исходной формулы в относительных ссылках В2 и D2 произошло увеличение на 1 номеров строк. Это нас не устраивает, т.к. эти данные общие для всех квартир и при копировании формулы расчета оплаты ссылки на ячейки B2 и D2 не должны меняться. Поэтому для них нужно применить абсолютную адресацию. Для этого:

èАктивизировать ячейку D6.

èЩелкнуть в строке формул левой кнопкой мыши.

èВ строке формул подвести текстовый курсор к ссылке В2 и нажать на клавиатуре клавишу F4. В ссылке В2 перед адресом столбца и номером строки появляются значки $.

èВ строке формул подвести текстовый курсор к ссылке D2 и нажать на клавиатуре клавишу F4. В ссылке D2 перед адресом столбца и номером строки появляются значки $.Вид формулы должен быть таким: =C6*$B$2+B6*$D$2

èСкопировать полученную формулу в ячейки D7-D9. Вид экрана после копирования представлен на рис.9.

Рис.9. Вид экрана после копирования формулы с полученными формулами (слева) и с результатами вычислений (справа).

Этап №4.Оформление и сохранение документа.

èДля задания формата тарифа за газ, задающего подпись «руб/чел.» использовать возможность создания формата пользователя. Для этого:

èВыделить ячейку В2. Выбрать пункт меню Формат/Ячейки, выбрать вкладку «Число». Вид окна «Формат ячейки» представлен на рис.10.

èВ списке «Числовые форматы» данного окна выбрать строку «(Все форматы)».

èВ списке под строкой «Тип» щелчком левой кнопки мыши выбрать строку «Основной», которая помещается в поле ввода нового формата (см.рис.10).

èЩелчком левой кнопки мыши в поле ввода нового формата поместить текстовый курсор.

èПосле слова Основной набрать с клавиатуры подпись « руб/чел.» (кавычки обязательны!). В поле Образец отображается внешний вид ячейки. Вид окна должен соответствовать рис.10.

èНажать ОК.

èПо аналогии задать форматы для ячеек D5 (руб/кв.м), B6:B9 (кв.м).

èПо аналогии с лабораторной работой №1 задайте оформление остальной таблицы по образцу на рис.5.

èСохранить законченный документ в папке, созданной в ходе работы №1, под названием «Квартплата».

Рис.10. Окно задания числового формата ячеек.

Индивидуальные задания.

1. Составить и оформить документ Excel согласно индивидуальному варианту.

2. Сохранить таблицу в созданной ранее папке под названием «Лаб2».

Вариант 1.

Задание: пользуясь возможностями Excel создать таблицу для расчета годовой премии вкладчикам банка.

Исходные данные: проценты премии по кварталам за год, остатки на вкладах по кварталам (1 кв., 2кв., 3 кв., 4 кв.). Количество вкладов – 10.

Правила расчета:

Премия = остаток 1 кв * процент 1 кв + остаток 2 кв * процент 2 кв + остаток 3 кв * процент 3 кв + остаток 4 кв * процент 4 кв.

ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА

Порядок работы

1. Составить таблицу по образцу для расчета годовой премии 10 вкладчикам банка. Значения столбца «Премия» и строки «Сумма» вычисляются по формулам и заполняются позднее.

2. Заполнить первую ячейку столбца «Премия» формулой для расчета согласно заданным правилам. Формула должны использовать абсолютную и относительную адресацию.

3. Проверить правильность расчета для первого вклада.

4. Размножить формулу по ячейкам остальных вкладов.

5. Заполнить последнюю строку таблицы формулами для подсчета суммы по столбцам.

6. Оформить рабочий лист по образцу, используя возможности форматирования ячеек.

Вариант 2.

Задание: пользуясь возможностями Excel создать ведомость расчета заработной платы отдела предприятия.

Исходные данные: основной штат отдела 10 человек. Среди них:

1. 2 человека имеют по 2 ребенка.

2. 3 человека имеют по 1 ребенку.

3. 1 человек участвовал в ликвидации аварии на ЧАЭС.

Правила расчета:

Сумма начисленная = Оклад * отработано дней / кол-во рабочих дней в месяце

Сумма к выдаче = Сумма начисленная – ПФ-ПН-Сб

Профсоюз: ПФ = Оклад * 0,01

Подоходный налог: ПН = (Сумма начисленная – ПФ – Сумма леготированая)*0,12

Сбор на содержание муниципальной милиции:Сб = минималь. заработная плата * 0,03.

Сумма леготированная для работника основного штата складывается из 2 минимальных заработных плат, суммы, зависящей от количества детей и суммы, причитающейся ликвидатору аварии на ЧАЭС. При этом на каждого ребенка к сумме начисленной полагается еще по 2 минимальных заработных платы, Участнику ликвидации последствий аварии на ЧАЭС плюс еще 3 минимальных заработных платы. ( Для отметки, что данный работник является ликвидатором аварии на ЧАЭС, в соответствующей ему строке таблицы в колонку ЧАЭС нужно занести 1, для всех других работников в эту колонку занести 0).

Поэтому сумма леготированная: СЛ = минимальная заработная плата*2 + минимальная заработная плата * кол-во детей * 2 + минимальная заработная плата * 3 *ЧАЭС.