Лабораторные работы Excel / Лабораторная работа №9 / Лабораторная работа 9
.docЛАБОРАТОРНАЯ РАБОТА № 9.
«ИСПОЛЬЗОВАНИЕ ОТНОСИТЕЛЬНОЙ И АБСОЛЮТНОЙ АДРЕСАЦИИ В ВЫЧИСЛЕНИЯХ».
По указанному пути преподавателем найдите файл заготовка 9.xls, скопируйте его в личную папку и переименуйте в ФИО_лабExcel2_группа.xls. В этом документе выполните все задания.
ЗАДАНИЕ 1.
-
Переименуйте Лист 1 в Задание 1 и на этом листе создайте таблицу по Образцу 1 (значения в ячейках, к которым применена заливка серым цветом, подсчитать с помощью формул):
– в ячейку D2 введите формулу, в которой по умолчанию используются относительные адреса ячеек, и скопируйте её в ячейки для других товаров (D3, D4) с помощью маркера автозаполнения;
– в ячейку D5 введите формулу расчета суммы затрат на приобретение товаров;
– в ячейку E2 введите формулу: = Стоимость * 100 / Всего, в которой используются относительные адреса ячеек, и скопируйте её в ячейки Е3, Е4 с помощью маркера автозаполнения. В формуле появится ошибка «Деление на ноль», потому что при вычислении по формулам ячеек E3 и E4 происходит деление на ноль – при копировании формулы адрес ячейки D5 изменился соответственно на D6 и D7.
Для того, чтобы избежать подобных ошибок в расчетах необходимо применять абсолютную адресацию к адресам тех ячеек, данные которых неизменны при расчетах – в нашем случае такой ячейкой является D5. Измените в формуле, расположенной в ячейке E2, относительный адрес ячейки D5 на абсолютный, поставив знак $ перед именем столбца и номером строки ($D$5), затем скопируйте формулу с помощью маркера автозаполнения.
– в ячейку E5 введите формулу расчета сумму долей затрат (%) на приобретение товаров;
О бразец 1
ЗАДАНИЕ 2.
Переименуйте Лист 2 на Табуляция и на этом листе протабулируйте функцию при x[-5; 5] с шагом 1.
Указания к решению.
-
Значения коэффициентов p, q, и k поместите в ячейки B1, B2, B3.
-
Введите символ X в ячейку D1. Значения аргумента x= –5, –4, …, 5 поместите в ячейки D2:D12.
-
Введите символы F(x) в ячейку E1, формулы для вычисления F(x) поместите в ячейки E2:E12, при этом для ссылки на ячейки D2:D12 примените относительную адресацию, а для B1:B3 – абсолютную.
ЗАДАНИЕ 3.
-
Переименуйте Лист 3 на Адресация и создайте на этом листе таблицу по Образцу 2. Значения в столбцы «Премия» и «Налог» введите с помощью формул, в которых используется абсолютная адресация (премия=оклад*% премии, налог рассчитывается аналогично).
Значение в столбце «Итог» рассчитывается по формуле: итог = оклад+премия-налог.
Образец 2
-
Скопируйте таблицу (диапазон A1:H9) на лист Лист 4. Переименуйте Лист 4 на Адресация 2. Измените процент премии на 20%.
Обратите внимание, что после изменения только одного значения автоматически произошел пересчет премии и итога для всех строк.
-
На листе Адресация 2 определите минимальный и максимальный оклад, среднюю зарплату сотрудников и количество сотрудников, получивших премию больше 3000 р. (статистическая функция СЧЁТЕСЛИ).
-
Скопируйте таблицу (диапазон A1:F9) на Лист 5. Переименуйте Лист 5 на Адресация 3.
Пересчитайте размер налога и итоговые значения при введении прогрессивного налога. Шкала прогрессивного налогообложения занесена на Лист 5 и приведена в Таблице 1. Результат должен соответствовать Образцу 3.
Указания к решению. При расчете необходимо использовать логическую функцию ЕСЛИ и ссылки на таблицу ставок налога (каждая ставка действует, если доход не превышает указанную для нее величину).
Образец 3
ЗАДАНИЕ 4.
Создайте новый лист (команда меню Вставка\ Лист) и назовите его Отчет. Создайте таблицу по Образцу 4 для оценки эффективности работы сотрудников. Исходными данными являются величины реального дохода от деятельности персонала за три месяца и запланированные значения. Необходимо рассчитать итоговые величины и составить формулу для автоматической оценки выполнение плана.
Указание к решению. Используйте функции СУММ и ЕСЛИ.
Образец 4