Конспект урока на тему “Формулы в 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”
Конспект урока на тему “Формулы в Excel”