макет MS Excel. Лабораторная работа 1 Редактирование рабочей книги. Построение диаграмм

• Название: Лабораторная работа 1 Редактирование рабочей книги. Построение диаграмм
• Дата: 27.10.2022
• Размер: 0.97 Mb.
• Имя файла: макет MS Excel.docx
• Тип: Лабораторная работа; #757385
• страница: 2 из 9

Лабораторная работа №2 Создание формул

Цель работы: Создание и использование простых формул в MS Excel.

Задание №1. Торговая фирма имеет в своем ассортименте следующую сельскохозяйственную продукцию: пшеница продовольственная и фуражная, ячмень фуражный, овес, рожь, кукуруза фуражная. Дается цена за 1 тонну продукции и количество проданного. Используя возможности Excel, найти сумму выручки от продаж в рублях и долларах.
Методика выполнения работы.

1. 
   Откройте новую рабочую книгу и сохраните ее в своей папке.
   
2. 
   Создайте таблицу, внесите в нее исходные данные задачи (рис. 5.49).
   

Рисунок 5.49 – Макет таблицы

3. 
   Для подсчета выручки от продажи в долларах в ячейки столбца внесите соответствующие формулы. В формулах использована относительная адресация ячеек. Формула вводится лишь в одну ячейку, а остальные формулы в столбце получены при помощи автозаполнения.
   
4. 
   Подсчитайте выручку от продажи в рублях. В формулах использована смешанная и абсолютная адресация ячеек. Для введения абсолютного и смешанного адреса необходимо после введения ссылки нажать клавишу F4 и выбрать из предлагаемых вариантов нужный.
   
5. 
   Подсчитайте сумму выручки от продажи всех видов товаров. Выделить столбец и нажать кнопку Автосуммана ленте Формулы..
   

Задание №2.

1. 
   Изучите создание и использование простых формул, используя тематику финансового и банковского менеджмента.
   
2. 
   Сопоставьте доходность акции по уровню дивидендов за 2010 г. по отдельным эмитентам. Исходные данные задачи представлены в таблице 5.3.
   

Методика выполнения работы.

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

Дивиденды, руб. = Номинал акции, руб. * Дивиденды, %

Доходность к номиналу, % = Дивиденды, %

Доходность фактическая, руб. = Цена продажи, руб. - Номинал акции, руб. + Дивиденды, руб.

Таблица 5.3

Эмитент	Номинал акции, руб.	Цена продажи, руб.	Дивиденды, объявленные в расчете на год		Доходность по дивидендам
			%	руб.	К номиналу, %	Фактическая, руб.
Юниаструмбанк	10000	17780	400%
Инкомбанк	10000	22900	400%
Россельхозбанк	3000	5600	320%
Импэксбанк	5000	2015	653%
Сбербанк России	10000	2482	736%
МДМ банк	1000	1000	325%
Промстройбанк	1000	1200	153%

3. 
   Визуально проанализируйте полученные результаты.
   

4. 
   Добавьте внизу таблицы строку «Среднее значение» и введите формулы по всем столбцам.
   
5. 
   На основании исходного документа «Доходность акций по отдельным дивидендам» рассчитайте следующие значения:
   

• 
  средняя цена продажи акций по всем эмитентам (выделить столбец «Цена продажи» без заголовка, воспользоваться функцией СРЗНАЧ);
  
• 
  максимальная цена продажи акций по всем эмитентам (выделить столбец «Цена продажи» без заголовка, воспользоваться функцией МАКС);
  
• 
  минимальная цена продажи акций (выделить столбец «Цена продажи» без заголовка, воспользоваться функцией МИН);
  
• 
  максимальная фактическая доходность акций по уровню дивидендов (выделить столбец «Фактическая доходность» без заголовка, воспользоваться функцией МАКС);
  
• 
  минимальная фактическая доходность акций по уровню дивидендов (выделить столбец «Фактическая доходность» без заголовка, воспользоваться функцией МАКС).
  
• 
  Результаты расчетов оформите в виде табл. 5.4.
  

Таблица 5.4

Расчетная величина	Значение
Средняя цена продажи акций
Максимальная цена продажи акций
Минимальная цена продажи акций
Максимальная фактическая доходность акций
Минимальная фактическая доходность акций

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

• 
  выделить данные таблицы с прилегающей одной строкой заголовка;
  
• 
  выполнить команду Данные Фильтр;
  
• 
  в заголовке столбца «Фактическая доходность» нажать кнопку раскрывающегося списка и выбрать Числовые фильтры;
  

• 
  в раскрывающемся списке выбрать условие «выше среднего значения».
  

8. 
   Вернитесь к исходному виду с помощью команды Данные Очистить.
   
9. 
   Постройте на отдельном рабочем листе Excel круговую диаграмму, отражающую фактическую доходность по дивидендам каждого эмитента в виде соответствующего сектора (выделить столбцы «Эмитент» и «Фактическая доходность», выполнить команду Вставка Диаграмма). На графике показать значения доходности, вывести легенду и название графика «Анализ фактической доходности акций по уровню дивидендов».
   
10. 
    Постройте на новом рабочем листе Excel смешанную диаграмму, в которой представьте в виде гистограмм значения номиналов и цены продажи акций каждого эмитента, а их фактическую доходность покажите в виде линейного графика на той же диаграмме. Выведите легенду и название графика «Анализ доходности акций различных эмитентов». Алгоритм построения смешанного графика следующий:
    

• 
  выделить столбцы «Эмитент», «Номинал акции» и «Цена продажи»;
  
• 
  выполнить команду меню Вставка  Диаграмма- тип диаграммы Гистограмма - введите название диаграммы и по осям - Готово;
  
• 
  для добавления линейного графика «Фактическая доходность по дивидендам правой клавишей мыши на пустом поле диаграммы Выбрать данные Добавить, в поле Имя ввести название ряда «Доходность», в поле Значения ввести числовой интервал, соответствующий фактической доходности по дивидендам;
  
• 
  на полученной диаграмме, курсор мыши установить на столбец, соответствующий значению «Доходность», правой клавишей мыши активизировать контекстное меню, выбрать команду Изменить тип диаграммы для ряда, где выбрать тип диаграммы – График;
  

8. 
   Подготовьте результаты расчетов и диаграммы к выводу на печать.
   

Самостоятельное задание
На предприятии работники имеют следующие оклады: начальник отдела – 1000 руб., инженер 1кат. – 860 руб., инженер – 687 руб., техник – 315 руб., лаборант – 224 руб. Предприятие имеет два филиала: в средней полосе и в условиях крайнего севера. Все работники получают надбавку 10% от оклада за вредный характер работы, 25% от оклада ежемесячной премии. Со всех работников удерживают 20% подоходный налог, 3% профсоюзный взнос и 1% в пенсионный фонд. Работники филиала, расположенного в средней полосе, получают 15% районного коэффициента, работники филиала, расположенного в районе крайнего севера, имеют 70% районный коэффициент и 50% северной надбавки от начислений.

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