Конспект урока на тему “Формулы в Excel”

Практическая работа №18

“Формулы в Excel”

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

Задание № 1. Торговая фирма имеет в своем ассортименте следующий товар: телевизоры стоимостью $300, видеомагнитофоны стоимостью $320, музыкальные центры стоимостью $550, видеокамеры стоимостью $700, видеоплееры стоимостью $198, аудиоплееры стоимостью $40. В январе было продано телевизоров – 10, видеомагнитофонов – 5, музыкальных центров – 6, видеокамер – 2, видеоплееров – 7, аудиоплееров – 4. Используя возможности Excel, найти сумму выручки от продаж в рублях и долларах.

Ход работы:

1. Создайте таблицу, внесите в нее исходные данные задачи.

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

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

4. Подсчитайте сумму выручки от продажи всех видов товаров.

Выделить столбец и нажать кнопку Автосумма на стандартной панели инструментов или установить курсор в последнюю ячейку столбца Е в строку “Итого сумма выручки” и воспользоваться кнопкой Вставка функции, расположенной также на стандартной панели, в окне Мастера функций следует выбрать СУММ из категории Математические.

Таблица 6.3

В	С	D	Е	F	G
Наименование продукции	Цена за ед., Долл.	Продано, Шт.	Выручка от продажи, долл.	Выручка от продажи, руб.	Долл.
300	10	=C3D3	=$E3$G$3	27.1
320	5	=С4 + D4	=$E4$G$3
Музыкальные центры	550	6	=C5D5	=$E5$G$3
700	2	=С6 + D6	=$E6$G$3
198	7	=C7D7	=$E7$G$3
40	4	=С8 + D8	=$E8$G$3
Итого сумма выручки	=СУММ(ЕЗ:Е8)	=CУMM(F3:F8)

Задание № 2.

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

2. Сопоставьте доходность акции по уровню дивидендов за 1999 г. по отдельным эмитентам. Исходные данные задачи представлены в табл. № 6.4:

(NA) – номинал акции;

(СР) – цена продажи;

(Div) – дивиденды, объявленные в расчете на год.

Таблица 6.4

Эмитент

Номинал Акции, руб.	Цена продажи, Руб.	Дивиденды, объявленные В расчете на год	Доходность по дивидендам
NA	СР	% Div	Руб. DivR	К номиналу DN	Фактическая DF
10000	17780	400%
10000	22900	400%
5000	5600	320%
1000		653%
1000	2482	736%
КБ Аккобанк	1000	1000	325%
СКБ банк	50000	27050	360%
1000	1200	1535%

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

Ход работы:

1. В соответствующие столбцы введите формулы для расчета выходных

Показателей:

DivR(i) = NA(i)Div(i);

DN(i) = Div(i);

DF(i) = DivR(i)/CP(i),

Где i = , N – число рассматриваемых эмитентов.

2. На основании исходного документа “Доходность акций по отдельным дивидендам” рассчитайте следующие значения:

Средняя цена продажи акций по всем эмитентам (выделить столбец “Цена продажи” без заголовка, вызвать из стандартной панели Мастер функций > категория Статистическая > Функция = СРЗНАЧ;

Максимальная цена продажи акций по всем эмитентам (выделить столбец “Цена продажи” без заголовка, вызвать из стандартной панели Мастер функций > категория Статистическая > Функция = МАКС;

Минимальная цена продажи акций (выделить столбец “Цена продажи” без заголовка, вызвать из стандартной панели Мастер функций > Категория Статистическая > Функция = МИН;

Максимальная фактическая доходность акций по уровню дивидендов (выделить столбец “Фактическая доходность” без заголовка, вызвать Мастер функций > категория Статистическая > функция = МАКС;

Минимальная фактическая доходность акций по уровню дивидендов (выделить столбец “Фактическая доходность” без заголовка, вызвать Мастер функций > категория Статистическая > функция = МАКС.

3. Результаты расчетов оформите в виде табл. 6.5.

Таблица 6.5

Расчетная величина

Значение

Средняя цена продажи акций

Максимальная цена продажи акций

Минимальная цена продажи акций

Максимальная фактическая доходность акций

Минимальная фактическая доходность акций

4. В исходной таблице отсортируйте записи в порядке возрастания фактической доходности по дивидендам (выделить таблицу без заголовков и строки “Среднее значение”, выполните команду Сортировка Меню Данные).

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

Алгоритм фильтрации следующий:

Выделить данные таблицы с прилегающей одной строкой заголовка;

Выполнить команду Фильтр – Автофильтр Меню Данные;

В заголовке столбца “Фактическая доходность” нажать кнопку раскрывающегося списка и выбрать Условие;

В окне пользовательского автофильтра задать условие >”среднее значение”.

6. Результаты фильтрации поместите на новый рабочий лист,

Включив в него следующие графы:

Эмитент;

Номинал акции;

Цена продажи;

Доходность по дивидендам фактическая.

7. Постройте на отдельном рабочем листе Excel круговую диаграмму, отражающую фактическую доходность по дивидендам каждого эмитента в виде соответствующего сектора (выделить столбцы “Эмитент” и “Фактическая доходность”, выполнить команду меню Вставка > Диаграмма). На графике показать значения доходности, вывести легенду и название графика “Анализ фактической доходности акций по уровню дивидендов”.

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

Алгоритм построения смешанного графика следующий:

Выделить столбцы “Эмитент”, “Номинал акции” и “Цена продажи”;

Выполнить команду меню Вставка > Диаграмма > тип диаграммы Гистограмма;

Для добавления линейного графика “Фактическая доходность по дивидендам” Правой клавишей мыши активизировать меню Диаграмма > Исходные данные -> во вкладке Ряд, Выбрать кнопку Добавить, В поле Имя Ввести название ряда “Доходность”, в поле Значения Ввести числовой интервал, соответствующий фактической доходности по дивидендам;

На полученной диаграмме курсор мыши установить на столбец, соответствующий значению “Доходность”, правой клавишей мыши активизировать контекстное меню, выбрать команду Тип диаграммы, Где выбрать тип диаграммы – График.

Конспект урока на тему “Формулы в Excel”

Похожие сочинения: