Лабораторная работа 5 относительная и абсолютная адресация. Вычисления с помощью логических функций

Цель работы:

закрепить умения использования ссылок в формулах, познакомится с логическими функциями и возможностями их применения на практике.

Задание 5.1.

Рассчитать общий объем продаж магазина Электрошок используя данные таблицы (рис. 5.1).

Рис. 5.1. Объем продаж магазина Электрошок

Величина наценки будет рассчитываться по следующему алгоритму: если цена товара по прайс-листу меньше 400 у.е., то наценка составит 50% от цены, в противном случае – 30%.

Рекомендации по выполнению:

1. Создайте на листе 1 таблицу (рис. 5.1) и сохраните ее в своей папке под именем Лабораторная работа 5.

Перед вводом значений в ячейки В12:В14 вначале задайте их формат (команда Главная (Число ) → вкладка Число).

Для ячейки В12: числовой формат – Денежный; число десятичных знаков – 1; обозначение – р.

Для ячеек В13, В14: числовой формат – Процентный; число десятичных знаков – 1.

2. Для расчета данных в графе Цена, руб. необходимо выполнить следующие действия:

• активизировать ячейку С3.

• ввести знак = после чего щелкнуть левой кнопкой мыши по ячейке В3, затем ввести знак * после чего щелкнуть левой кнопкой мыши по ячейке В12, нажать функциональную клавишу F4 (так как значение в ячейке В12 является абсолютным в нашей формуле, т.е. при копировании не должно изменяться). В ячейке должна отразиться формула =B3*$B$12. Нажать Enter.

• скопировать значение ячейки С3 в диапазон С4:С9.

3. Для расчета данных в графе Наценка, % необходимо выполнить следующие действия:

• активизировать ячейку D3.

• установить указатель мыши на пиктограмму Вставка функции в строке формул и щелкнуть левой кнопкой мыши.

• в появившемся окне Мастер функций выбрать категорию функций Логические. В списке функций выбрать ЕСЛИ. Нажать ОК.

• подвинуть появившееся на экране окно за пределы таблицы, чтобы не мешать указателю мыши работать с ячейками таблицы.

• установить курсор в поле Лог_выражение. Щелкнуть левой кнопкой мыши по ячейке В3 и с клавиатуры набрать <400.

• перевести курсор в поле Значение_если_истина и щелкнуть левой кнопкой мыши в ячейке В13, нажать функциональную клавишу F4.

• перевести курсор в поле Значение_если_ложь и щелкнуть левой кнопкой мыши в ячейке В14, нажать функциональную клавишу F4 (рис. 5.2). Нажать ОК.

Рис. 5.2. Использование логической функции ЕСЛИ

• скопировать значение ячейки D3 в диапазон D4:D9.

4. Для расчета данных в графе Цена с учетом наценки, у.е. необходимо выполнить следующие действия:

• активизировать ячейку Е3.

• ввести в ячейку формулу =B3+B3*D3. Нажать Enter.

• скопировать значение ячейки Е3 в диапазон Е4:Е9.

5. Для расчета графы Стоимость, у.е. в ячейку G3 ввести формулу =E3*F3 (Цена с учетом наценки, у.е. * Количество) и скопировать ее в ячейки G4:G9.

6. В ячейку H2 ввести название графы Налог на добавленную стоимость (НДС), у.е.

Рассчитать значение в этом столбце по формуле Стоимость, у.е.*20% (НДС – это налог, перечисляемый в бюджет государства в размере 20% от стоимость продаж):

• выделить ячейку Н3 и ввести формулу =G3*20%

• скопировать ее в ячейки диапазона Н4:Н9.

7. Для определения выручки магазина по каждому виду товара:

• в ячейку I2 ввести заголовок Выручка, у.е.;

• в ячейку I3 ввести формулу= G3+Н3 (Стоимость, у.е. + Налог на добавленную стоимость (НДС), у.е.);

• скопировать ее в ячейки диапазона I4:I9.

8. Для граф Стоимость, у.е., Налог на добавленную стоимость (НДС), у.е., Выручка, у.е. подсчитать общую сумму по столбцам, отобразив значения в строке Итого.

9. К столбцам Н и I применить форматирование, как в предыдущих столбцах таблицы.

В результате должны получить значения как на рис. 5.3

Рис. 5.3. Объем продаж магазина Электрошок

Задание 5.2.

Создайте таблицу для расчета зарплаты преподавателей за учебные часы (рис. 5.4).

Рис. 5.4. Данные таблицы Расчет зарплаты преподавателей за учебные часы

Категория преподавателя может быть только: высшая, первая, вторая или без категории (б/к).

Стоимость одного часа с учетом категории рассчитывается по формуле =Ставка 1-го разряда / 72 * тарификационный коэффициент преподавателя соответствующей категории.

Стоимость одного часа с учетом стажа рассчитывается по формуле =Стоимость одного часа с учетом категории + Надбавка за стаж, где надбавка за стаж:

• от 0 до 10 лет – 10% от стоимости одного часа с учетом категории;

• от 10 до 15 лет –15% от стоимости одного часа с учетом категории;

• свыше 15 лет – 20% от стоимости одного часа с учетом категории.

Рекомендации по выполнению:

1. Создайте на листе 2 таблицу (рис. 5.4), переименуйте лист 2 в Зарплата за часы и сохраните книгу в своей папке под именем Лабораторная работа 5.

2. Выделить ячейку С9 и внести формулу для определения размера стоимости одного часа с учетом категории. Для этого вызвать Мастер функций и в категории Логические выберите функцию ЕСЛИ и заполните поля Лог_выражение и Значение_если_истина (рис. 5.5).

Рис. 5.5. Фрагмент заполнения аргументов функции ЕСЛИ

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

3. Для заполнения поля Значение_если_ложь вставьте новую функцию ЕСЛИ. Чтобы вставить вторую функцию ЕСЛИ в поле первой функции воспользуйтесь кнопкой в строке формул. Эта кнопка появляется при работе с Мастером функций на месте адреса активной ячейки (рис. 5.6). При вставки новой функции Мастер функций открывает новое окно Аргументы функции.

Рис 5.6. Фрагмент вставки вложенной функции

4. Заполните поля для второй функции ЕСЛИ (рис. 5.7). В данном случае рассчитывается стоимость одного часа для преподавателей первой категории.

Рис. 5.7. Фрагмент заполнения аргументов вложенной функции ЕСЛИ

5.  В поле Значение_если_ложь вставьте функцию ЕСЛИ для определения размера стоимости одного часа для преподавателей второй категории и заполните аргументы функции ЕСЛИ в соответствии с рис. 5.8.

Рис. 5.8. Фрагмент заполнения аргументов вложенной функции ЕСЛИ

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

=ЕСЛИ(B9="высшая";$G$2/72*$G$3;ЕСЛИ(B9="первая";$G$2/72*$G$4;ЕСЛИ(B9="вторая";$G$2/72*$G$5;$G$2/72*$G$6)))

6. Скопировать полученную формулу в диапазон ячеек С10:С13.

7. Используя вложенные функции ввести формулу в ячейку Е9 – Стоимость одного часа с учетом стажа. Формула будет иметь вид:

=C9*(1+ЕСЛИ(D9<=10;$B$16;ЕСЛИ(D9<=15;$B$17;$B$18)))

Скопировать ее в ячейки Е10:Е13.

8. Рассчитать значения столбца Итого за часы. Таблица после расчетов будет иметь вид рис. 5.9.

Рис. 5.9 Фрагмент таблицы с расчетами

9. Определим количество преподавателей высшей категории, количество преподавателей первой категории, количество преподавателей второй категории и количество преподавателей без категории. Для этого:

• в ячейку D15 ввести текст Количество преподавателей;

• в ячейку D16 – высшей категории;

• в ячейку D17 – первой категории;

• в ячейку D18 – второй категории;

• в ячейку D19 – без категории.

10. В ячейку F16 используя Мастер функций ввести функцию СЧЕТЕСЛИ, которая примет вид (рис. 5.10):

=СЧЁТЕСЛИ(B9:B13;"высшая")

Рис. 5.10. Фрагмент заполнения аргументов вложенной функции СЧЕТЕСЛИ

11. Используя функцию СЧЕТЕСЛИ подсчитать количество преподавателей каждой из оставшихся категорий.

Окончательно таблица примет вид (рис. 5.11).

Рис. 5.11. Итоговый вид таблицы Расчет зарплаты преподавателей за учебные часы

лабораторная работа 6

относительная и абсолютная адресация.

Использование финансовых функций в mS excel

(УСРС 2 часа)

Цель работы:

рассмотреть применение различных ссылок на ячейки в формулах и научиться применять финансовые функции MS Excel для решения экономических задач.

Задание 6.1.

Используя относительную и абсолютную адресацию ячеек создать таблицу, для расчета функции (рис. 6.1).

Рис. 6.1. Образец вычисления функции

Рекомендации по выполнению:

1. Создайте книгу и сохраните ее в своей папке под именем Лабораторная работа 6.

В соответствии со своим вариантом в книге с именем Коэффициент на Листе 1 создайте и сохраните таблицу 1, образец заполнения которой представлен на рис. 15.

1. Создайте книгу и сохраните ее в своей папке под именем Лабораторная работа 6-ФИН.

2. Введите название таблицы, заполните значения периодов и значения ставки процента в соответствии с данными рис. 6.1.

3. Выделите ячейку В4 и введите формулу =(B$3+1,3)^($A4*2,2).

4. Скопируйте формулу в диапазон ячеек С4:Е4 и В5:Е9.

Задание 6.2.

Определение срока платежа и процентной ставки: для покрытия будущих расходов фирма создает фонд. Средства в фонд поступают в виде постоянной годовой ренты постнумерандо. Сумма разового платежа 16 000 руб. На поступившие взносы начисляются 11,2 % годовых.

Необходимо определить, когда величина фонда будет равна 100 000 руб.

Рекомендации по выполнению:

1. Создайте книгу и сохраните ее в своей папке под именем Лабораторная работа 6-ФИН.

2. Переименовать лист 1 книги в КПЕР.

Введем начальные значения в таблицу (рис. 6.2).

Рис. 6.2. Начальные значения для расчета

Для ячейки В1 задать процентный формат, для В2, В3 – денежный формат с двумя десятичными знаками и обозначением – р.

3. Для определения общего числа периодов, через которое будет достигнута нужная сумма, воспользуемся функцией КПЕР с аргументами:

ставка = 11,2%; плт = -16000; бс = 100000 (рис. 6.3).

Рис. 6.3. Аргументы функции КПЕР

В результате вычислений получим, что через 5 лет величина фонда достигнет отметки 100 000 руб.: = КПЕР (11,2%;-16;;100) = 5

Задание 6.3.

Определить платежи по процентам за первый месяц от трехгодичного займа в 100 000 рублей из расчета 10 % годовых.

Рекомендации по выполнению:

1. Перейти на лист 2 книги Лабораторная работа 6-ФИН и переименовать его в ПРПЛТ.

2. Введем начальные значения в таблицу (рис. 6.4).

Рис. 6.4. Начальные значения для расчета

Для ячейки В1 задать денежный формат с двумя десятичными знаками и обозначением – р., для В2 – процентный.

3. Для определения платежа по процентам за первый месяц заданного периода применим функцию ПРПЛТ со следующими аргументами:

Ставка = 10 % / 12 (процентная ставка за месяц);

Период = 1 (месяц);

Кпер = 3*12=36 (месяцев);

ПС = 100 000 (величина займа).

Тогда платежи по процентам за первый месяц составят:

= ПРПЛТ (10%/12 ; 1 ; 36 ; 100 000) = - 833,33 руб.

Знак «минус» означает расход для заемщика, то есть что платеж по процентам необходимо внести (рис. 6.5).

Рис. 6.5. Применение функции ПРПЛТ

4. Сохранить книгу Лабораторная работа 6-ФИН.