Лабораторная работа Базовые информационные технологии Excel. Выполнение расчётов в электронных таблицах по дисциплине Информационные технологии в профессиональной деятельности

Лабораторная работа №15

Базовые информационные технологии Excel (заполнение ячеек,

редактирование, форматирование). Выполнение расчётов в электронных таблицах

Цель:

1) Изучение информационной технологии создания и сохранения документов MSExcel; заполнение ячеек в табличном процессоре, форматирование и редактирование данных; изучение уровней защиты данных в программе Excel.

2) Изучение технологии экономических расчетов в табличном процессоре.

I.Форматирование и редактирование данных

Форматирование ячеек выполняется с помощью команды меню Формат - Формат ячеек на вкладкеГлавная, либо из контекстного меню с помощью команды Формат ячеек. Выводится диалоговое окно Формат ячеек, содержащее ряд вкладок: для задания составляющих формата ячеек. На вкладке Число осуществляется выбор стандартной либо создание новой маски формата для представления значений в ячейке. Для создания нового пользовательского формата выбирается Все форматы, в строке Тип задаётся маска формата. На вкладке Выравнивание задается ориентация содержимого ячейки, выравнивание по вертикали и горизонтали, разбиение по строкам текстовых данных, объединение выделенных ячеек. По умолчанию действуют установки - по значению, текст выравнивается слева;числа и дата/время - вправо, логические значения ИСТИНА/ЛОЖЬ - по центру. Шрифтовое оформление содержимого ячеек выполняется на вкладке Шрифт. На вкладке Граница делаются установки для линий, ограничивающих ячейки. Каждая линия характеризуется цветом и типом (сплошная, пунктирная), определенной толщины. С помощью линии определяется форма границы для выделенных ячеек: внешняя, внутренняя. Для каждой границы можно изменять настройки линии. На вкладке Вид выбирается вариант цветовой заливки ячеек.

Задание 1.

Создайте и отформатируйте на Листе1 таблицу1 (рис. 1), начиная с ячейки А1. Установите границы таблицы (рис.1), шрифт и цвет шапки таблицы (полужирный, 12, TimesNewRoman), выравнивание для шапки по центру и по вертикали и по горизонтали. Переименуйте Лист1 на Задание1. Выберите цвет для ярлычка Листа 1 на свое усмотрение.

Рис. 1. Исходные данные для Задания 1

Для ускорения форматирования диапазона ячеек служит команда менюАвтоформат таблицы, которая позволяет дополнительно настроить выбранный стандартный формат таблицы. MicrosoftExcel позволяет применять именованные форматы - стили к выделенным диапазонам ячеек.Стиль - объединение под общим именем всех или определенных форматных характеристик ячеек: числовой формат, шрифтовое оформление, выравнивание, рамки, фон, защита. Команда Выбор стиля оформления для таблицыв области «Стили таблицы» на вкладке Конструктор (данная вкладка активна, если форматируемая таблица выделена) выводит диалоговое окно для создания новых стилей, изменения или удаления стилей, приписывания выбранного стиля к выделенному диапазону ячеек.

Задание 2.Примените стиль (на ваше усмотрение) к созданной таблице, используя команду Стили ячеек на вкладке Главная, после чего добавьте в середину данной таблицы следующие строки и столбец (рис. 2):

Рис. 2. Исходные данные для Задания 1

Задание 3.

Переименуйте Лист2 на «Компания», на котором создайте таблицу оценки рекламной компании по образцу рис.3. Введите исходные данные: Месяц, Расходы на рекламу А(0) (р.), Сумма покрытия В(0) (р.), Рыночная процентная ставка (j) =13,7%. Отформатируйте данную таблицу на своё усмотрение.

Рис. 3. Исходные данные для задания 3

II. Вычисление с помощью формул

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

Задание 4. Вычислите сумму налога на добавленную стоимость (НДС), «чистую» сумму (без НДС) и общую сумму, если: Цена единицы товара, включая НДС, -12500 руб.; Количество проданного товара - 27 шт.; Ставка налога на добавленную стоимость -20%.

Порядок выполнения

1. Переименуйте Лист3 на Расчет.

2. Начиная с ячеек: А1 (название параметров) и В1 (значения параметров) создайте таблицу по образцу рис.4.

3. Отформатируйте таблицу: для ячеек В2, В3, В4 установите форматы Денежный», «Числовой», «Процентный» соответственно; для шапки таблицы выполните: выравнивание - по горизонтали и по вертикали «по центру», отображение - переносить по словам, шрифт- TimesNewRoman, размер-13, цвет - темно-красный.

Рис. 4. Исходные данные для Задания 4

4. Рассчитайте формулу для графы «Общая сумма», для этого активизируйте ячейку В5 и введите в нее формулу: =В2*В3 (результат - 337500р.).

5. Рассчитайте формулу для графы «Сумма НДС», для этого активизируйте ячейку В6 и введите в нее формулу: =В5*20/120 (результат - 56250р).

6. Рассчитайте формулу для графы «Чистая сумма, для этого активизируйте ячейку В7 и введите в нее формулу: =В5*100/120 или =В5-В6 (результат - 281250р.).

III.Вычисление с помощью формул и функций

Задание 5. Оценка рентабельности рекламной компании фирмы.

Порядок выполнения

1. Выделите для рыночной процентной ставки, являющейся константой, отдельную ячейку - С3, и дайте этой ячейке имя «Ставка1».

Краткая справка. Присваивание имени ячейке или группе ячеек.

• Выделите ячейку (группу ячеек или несмежный диапазон), которой необходимо присвоить имя.

• Перейдите на вкладку Формулы.

• Щелкните на поле «Присвоить имя» и в появившемся диалоговом окне «Создание имени» в поле «Имя» введите имя ячейки (ячеек).

• Нажмите клавишу OK.

2. Произведите расчеты во всех столбцах таблицы.

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

Формула для расчета:

А(n) =A(0)*, в ячейке С6 наберите формулу = В6*(1+ставка1/12)ˆ(1-$А6).

Примечание. Ячейка А6 в формуле имеет комбинированную адресацию: абсолютную адресацию по столбцу и относительную по строке, и записывается в виде $А6.

При расчете расходов на рекламу нарастающим итогом надо учесть, что первый платеж равен значению текущей стоимости расходов на рекламу, значит в ячейку D6 введите значение =С6, но в ячейке D7 формула примет вид =D6+С7. Далее формулу ячейки D7 скопируйте в ячейки D8:D17.

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

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

Для расчета текущей стоимости покрытия скопируйте формулу из ячейки С6 в ячейку F6. В ячейке F6 должна быть формула

= E6*(1+ставка/12)^(1-$A6).

Далее с помощью маркера автозаполнения скопируйте формулу в ячейки F7:F17.

Сумма покрытия нарастающим итогом рассчитывается аналогично расходам на рекламу нарастающим итогом, поэтому в ячейке G6 поместите содержимое ячейки F6, а в G7 введите формулу =G6+F7.

Далее формулу из ячейки G7 скопируйте в ячейкиG8:G17. В последних трех ячейках столбца будет представлено одно и то же значение, ведь результаты рекламной кампании за последние три месяца на сбыте продукции уже не сказывались.

Сравнив значения в столбцах D и G, уже можно сделать вывод о рентабельности рекламной кампании, однако расчет денежных потоков в течение года (колонка Н), вычисляемый как разница колонок G и D, показывает, в каком месяце была пройдена точка окупаемости инвестиций. В ячейке Н6 введите формулу =G6-D6, и скопируйте ее на всю колону.

Проведите условное форматирование результатов расчета, колонки Н: отрицательных чисел - синим курсивом, положительных чисел - красным цветом шрифта. По результатам условного форматирования видно, что точка окупаемости приходится на июль месяц.

2. В ячейке Е19 произведите расчет количества месяцев, в которых сумма покрытия имеется (используйте функцию «СЧЕТ», указав в качестве диапазона «Значение 1» интервал ячеек Е7:Е14). После расчета формула в ячейке Е19 будет иметь вид =СЧЕТ(Е7:Е14).

2. В ячейке Е20 произведите расчет количества месяцев, в которых сумма покрытия больше 100000р. (Используйте функцию СЧЕТЕСЛИ, указав в качестве диапазона «Значение» интервал ячеек Е7:Е14, а в качестве условия >100000). После расчета формула в ячейке Е20 будет иметь вид =СЧЕТЕСЛИ(Е7:Е14)

3. Вычисление с применением ссылок (указанием адреса ячейки)

Задание 6. Вычислить стоимость товарных запасов и налог на добавленную стоимость.

Порядок выполнения

1. Свободный лист Вашей рабочей книги переименуйте в Товар и создайте таблицу, начиная с ячейки А1.

Рис. 5. Исходные данные для задания 6

2. Введите формулу стоимости запаса в ячейку Е3: =С3*D3.

3. Скопируйте формулу ячейки Е3 в ячейки Е4:Е5.

4. Введите формулу суммы НДС в ячейку F3: =E3*20/120. Скопируйте формулу ячейки F3 в ячейки F4:F5.

5. Ведите формулу стоимости товарных запасов в условных единицах в ячейку G3: =E3/$B$1 (абсолютная ссылка на ячейку В1, в которой содержится значение курса условной единицы - доллара).

6. Скопируйте формулу G3 в ячейки G4:G5.

Задание 7.Сохраните рабочую книгу с именем «Лабораторные». Отчитайтесь по проделанной работе преподавателю и оформите отчёт по данной лабораторной работе, ответив письменно на контрольные вопросы.

Контрольные вопросы:

1. С помощью какой команды выполняется форматирование ячеек?

2. Охарактеризуйте назначение всех вкладок диалогового окна «Формат ячеек».

3. Каким образом можно установить внешние и внутренние границы таблицы, задать цвет шрифта шапки таблицы?

4. Для чего предназначена команда Автоформат? Что такое Стиль?

5. Что такое формула? С чего начинается формула?

6. Что не может включать в себя формула?

7. Что такое именованный блок? Как создать именованный блок?

8. Что такое диапазон ячеек?

9. С помощью какой команды вызывается Мастер функций?

10. Какие категории функций Вы знаете? С какими категориями функций работали на занятии?

5