Вычислительные функции MS Excel для

ПРАКТИЧЕСКАЯ РАБОТА №1

ВЫЧИСЛИТЕЛЬНЫЕ ФУНКЦИИ MS EXCEL ДЛЯ

ФИНАНСОВОГО АНАЛИЗА

Задание №1.

Создать таблицу финансовой сводки за неделю, произвести расчеты, построить диаграмму изменения финансового результата, произвести фильтрацию данных. Исходные данные представлены на Рис.1.1.

Рис. 1.1. Исходные данные

Произведите расчеты в графе «Финансовый результат» по следующей формуле: Финансовый результат = Доход – Расход. Вводите расчетные формулы только для расчета по строке «Понедельник», далее произведите автокопирование формул.

Для ячеек с результатом расчетов задайте формат – «Денежный» с выделением отрицательных чисел красным цветом. Для этого выделите необходимые ячейки и выполните команду Формат – Формат ячеек. В появившемся диалоговом окне (рис. 1.2) выберите числовой формат «Денежный» и отрицательные числа красным цветом. Число десятичных знаков задайте равное 2. Обратите внимание, как изменился цвет отрицательных значений финансового результата на красный.

 

Рис. 1.2. Диалоговое окно Формат ячеек

 

Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка ). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические». Для расчета функции СРЗНАЧ дохода установите курсор в соответствующей ячейке (В11), запустите Мастер функций и выберите функцию СРЗНАЧ. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В4:В10.
Аналогично рассчитайте среднее значение расхода.

В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирования на панели инструментов или функцией СУММ. В качестве первого числа выделите группу ячеек с данными для расчета суммы – D4:D10.

Конечный вид таблицы приведен на рис. 1.3.

 

 

Рис. 1.3. Итоговая таблица

 

Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с использованием мастера диаграмм.
Для этого выделите интервал ячеек с данными финансового результата D4:D10 и выберите команду Вставка – Линейчатая Диаграмма. Затем щелкните правой кнопкой мыши на диаграмме и в контекстном меню выберите пункт Выбрать данные (рис. 1.4).

В появившемся диалоговом окне Выбор источника данных (рис. 1.5)в разделе Подписи оси X нажмите кнопку Изменить и укажите в качестве источника строк ячейки A4:A10, затем нажмите кнопку OK.

 

Рис. 1.4. Контекстное меню

 

Рис. 1.5. Диалоговое окно Выбор источника данных

 

Окончательный вариант диаграммы приведен на рис. 1.6:

 

 

Рис. 1.6. Окончательный вид диаграммы

 

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

Нажмите кнопку OK. Произойдет отбор данных по заданному условию. Проследите, как изменился вид таблицы и построенная диаграмма (рис. 1.8 и 1.9).

 

Рис. 1.7. Диалоговое окно Пользовательский автофильтр

 

Рис. 1.8. Таблица после фильтрации

 

Рис. 1.9. Диаграмма после фильтрации

 

Задание №2.

Заполнить таблицу «Анализ продаж» (рис.1.10), произвести расчеты, выделить минимальную и максимальную сумму покупки; по результатам расчета построить круговую диаграмму суммы продаж.

 

Рис. 1.10. Таблица Анализ Продаж

Задание №3.

Заполнить ведомость учета брака (рис.1.11), произвести расчеты, выделить минимальную, максимальную и среднюю сумму брака, а также средний процент брака; произвести фильтрацию данных по условию процента брака < 9 %, построить график отфильтрованных значений изменения суммы брака по месяцам.

Рис. 1.11. Ведомость учета брака

ПРАКТИЧЕСКАЯ РАБОТА №2

ГРАФИЧЕСКОЕ ИЗОБРАЖЕНИЕ СТАТИСТИЧЕСКИХ ДАННЫХ И ПРОГНОЗИРОВАНИЕ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

Задание №1.

С помощью диаграммы (обычная гистограмма) отобразить данные о численности населения России (млн. чел.) за 1970- 2005 гг.

Рис. 2.1. Исходная таблица

 

Создайте на листе 1 таблицу численности населения по образцу (рис.2.1). Для ввода значений лет создайте ряд чисел с интервалом в 7 лет. Затем заполните года с помощью маркера Автозаполнения.

Постройте диаграмму (обычная гистограмма) по данным таблицы. Для этого выделите интервал ячеек с данными численности населения А3:G3 и выберите команду Вставка - Гистограмма. Затем щелкните правой кнопкой мыши на появившейся диаграмме и выберите пункт Выбрать данные в контекстном меню. В появившемся диалоговом окне в разделе Подписи горизонтальной оси измените значения ячеек на необходимые. Готовая диаграмма должна выглядеть следующим образом (рис. 2.2):

 

 

Рис. 2.2. Диаграмма Численности населения

 

Задание №2.

Рис. 2.6. Исходные данные

Задание №5.

По данным о численности работников научных организаций (тыс. чел.) в России за 2000 - 2005 гг. произвести расчеты и построить графики численности работников по категориям. Добавить линию тренда и составить прогноз изменения численности специалистов на три года вперед.

Рис. 2.7. Исходные данные

 

ПРАКТИЧЕСКАЯ РАБОТА №3

ПРАКТИЧЕСКАЯ РАБОТА №4

СВЯЗАННЫЕ ТАБЛИЦЫ. РАСЧЕТ ПРОМЕЖУТОЧНЫХ ИТОГОВ В ТАБЛИЦАХ MS EXCEL.

Задание №1.

ПРАКТИЧЕСКАЯ РАБОТА №5

Задание 1.

Используя режим подбора параметра, определить, при каком значении % Премии общая сумма заработной платы за октябрь будет равна 250 000 р. (на основании файла «Зарплата», созданного в Практических работах 3-4).

Использование операции «Подбор параметра» в MS Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое, удовлетворяющее заданным условиям, значение исходного параметра расчета.

Откройте редактор электронных таблиц Microsoft Excel и откройте файл «Зарплата», созданный в практической работе 3-4. Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги. Присвойте скопированному листу название «Подбор параметра». Выполните команду Сервис – Подбор параметра. В появившемся диалоговом окне установите значения согласно рис. 5.1.

 

 

Рис. 5.1. Диалоговое окно Подбор параметра

 

После нажатия на кнопку OK откроется диалоговое окно Результат подбора параметра (рис. 5.2).

 

Произойдет обратный пересчет %Премии.

Результаты подбора параметра представлены на рис. 5.3.

 

 

Рис. 5.2. Результат подбора параметра

 

 

Рис. 5.3. Результаты работы

 

 

Задание №2.

Задание 3.

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

Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2». Выберите коэффициенты уравнений для расчета согласно рис. 5.7. (один из пяти вариантов расчетов).

Рис. 5.7. Исходные данные

 

Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100000, 150000, 200000, 250000, 300000, 350000, 400000 р. Результаты подбора значений зарплат скопируйте в таблицу, представленную на рис. 5.8 в виде специальной вставки.

 

 

Рис. 5.8. Таблица для заполнения

 

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

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

 

 

ПРАКТИЧЕСКАЯ РАБОТА №6

Задание 1.

Минимизация фонда заработной платы фирмы. Пусть известно, что для нормальной работы фирмы требуется 5…7 курьеров, 8…10 младших менеджеров, 10 менеджеров, 3 заведующих отделами, главный бухгалтер, программист, системный аналитик, генеральный директор фирмы.
Общий месячный фонд зарплаты должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников фирмы, при условии, что оклад курьера не должен быть меньше 1400 р.

В качестве модели решения этой задачи возьмем линейную модель. Тогда условие задачи имеет вид:

N1*A1*х+N2*(A2*х+B2)+...+N8*(A8*х+B8) = Минимум, где

Ni - количество работников данной специальности;

х - зарплата курьера;

Аi и Вi - коэффициенты заработной платы сотрудников фирмы.

Откройте редактор электронных таблиц Microsoft Excel и откройте созданный в практической работе № 5 файл «Штатное расписание».
Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 3».

Надстройка «Поиск решения» вычисляет решения для сценариев «что - если» на основе ячеек перебора и ячеек ограничений. Устанавливается командой Сервис - Надстройки - Поиск решения.

В меню Сервис активизируйте команду Поиск решения.(рис. 6.1).

Рис. 6.1. Диалоговое окно Поиск решения

В окне Установить целевую ячейку укажите ячейку F14, содержащую модель – суммарный фонд заработной платы. Поскольку необходимо минимизировать общий месячный фонд зарплаты, активизируйте кнопку равной - Минимальному значению. В окне Изменяя ячейки укажите адреса ячеек, в которых будет отражено количество курьеров и младших менеджеров, а также зарплата курьера - $E$6:$E$7;$D$3 (при задании ячеек Е6, Е7 и D3 держите нажатой клавишу [Ctrl]).
Используя кнопку Добавить в окнах Поиск решения и Добавление ограничений, опишите все ограничения задачи: количество курьеров изменяется от 5 до 7, младших менеджеров от 8 до 10, а зарплата курьера>1400 (рис.6.2).

 

 

Рис. 6.2. Добавление ограничения

 

Ограничения наберите в виде:

$D$3 >= 1400;

$E$6 >= 5;

$E$6 <= 7;

$E$7 >= 8;

$E$7 <= 10.

 

Нажмите кнопку Параметры и введите параметры поиска, как показано на рис. 6.3.

 

 

Рис. 6.3. Параметры поиска решения

 

Запустите процесс поиска решения нажатием кнопки Выполнить.
В открывшемся диалоговом окне Результаты поиска решения задайте опцию Сохранить найденное решение. Решение задачи приведено на рис.6.4.
Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месячный фонд заработной платы.

 

 

Рис. 6.4. Решение задачи

Задание 2.

Составление плана выгодного производства.

Фирма производит несколько видов продукции из одного и того же сырья - A, B и C. Реализация продукции А дает прибыль 10 р., В - 15 р. и С - 20 р. на единицу изделия. Продукцию можно производить в любых количествах, поскольку считаем, что сбыт обеспечен, но ограничены запасы сырья. Необходимо определить, какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной.

Нормы расхода сырья на производство продукции каждого вида приведены на рис. 6.5.

 

 

Рис. 6.5. Нормы расхода сырья

 

Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу. Создайте расчетную таблицу (рис.6.6). Введите исходные данные и формулы в электронную таблицу. Расчетные формулы имеют такой вид:

· Расход сырья 1 = (количество сырья 1) * (норма расхода сырья А) + (количество сырья 1) * (норма расхода сырья В) + (количество сырья 1) * (норма расхода сырья С);

· Общая прибыль по А = (прибыль на ед.изд. А) * (количество А);

· Итоговая общая прибыль = (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С).

 

 

 

Рис. 6.6. Расчетная таблица

 

Активизируйте команду Поиск решения и введите параметры поиска, как указано на рис.6.7. В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль» (Е10), в качестве изменяемых ячеек – ячейки количества сырья (В9:D9). Не забудьте задать максимальное значение суммарной прибыли и указать ограничения на запас сырья:
расход сырья 1 <= 350; расход сырья 2 <= 200; расход сырья 3 <= 100,
а также положительные значения количества сырья А, В, С >=0.

 

Рис. 6.7. Параметры поиска решения

Установите параметры поиска решения согласно рис. 6.8.

 

 

Рис. 6.8. Параметры поиска решения

 

Кнопкой Выполнить запустите Поиск решения. Если вы сделали все верно, то решение будет выглядеть как на рис. 6.9.

 

 

Рис. 6.9. Готовое решение

Сохраните созданный документ под именем «План производства».

Из решения видно, что оптимальный план выпуска предусматривает изготовление 5,56 кг. продукции В и 22,22 кг. продукции С. Продукцию А производить не стоит. Полученная прибыль при этом составит 527,78 р.

 

Дополнительные задания.

Используя файл «План производства» (Задание 2) определите план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реализации была максимальной. Выберите нормы расхода сырья на производство продукции каждого вида и ограничения по запасам сырья из таблицы соответствующего варианта (5 вариантов):

 

 

 

 

 

 

 

 

ПРАКТИЧЕСКАЯ РАБОТА №7

В MS EXCEL

Задание 1.

Задание связей между файлами.

Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу. Создайте таблицу «Отчет о продажах 1 квартал» по образцу. Введите исходные данные (Доходы и Расходы): Доходы = 234,58 р.; Расходы = 75,33 р. и проведите расчет Прибыли: Прибыль = Доходы – Расходы. Сохраните файл под именем «1 квартал».

Создайте таблицу «Отчет о продажах 2 квартал» в виде нового файла. Для этого создайте новый документ и скопируйте таблицу отчета о продаже за первый квартал, после чего подправьте заголовок таблицы и измените исходные данные: Доходы = 452,6 руб.; Расходы = 125,8 руб.
Обратите внимание, как изменился расчет прибыли. Сохраните этот файл под именем «2 квартал».

Создайте таблицу «Отчет о продажах за полугодие» в виде нового файла. Для этого создайте новый документ и скопируйте таблицу отчета о продаже за первый квартал, после чего подправьте заголовок таблицы и в колонке В удалите все значения исходных данных и результаты расчетов. Сохраните файл под именем «Полугодие».

Для расчета полугодовых итогов свяжите формулами файлы «1 квартал» и «2 квартал». Для связи файлов Excel формулами выполните действия: 1) откройте эти файлы (все три файла); начните в файле-клиенте ввод формулы (в файле «Полугодие» введите формулу для расчета «Доход за полугодие»). Формула для расчета:
Доход за полугодие = Доход за 1 квартал + Доход за 2 квартал.
Чтобы вставить в формулу адрес ячейки или диапазона ячеек из другого файла (файла-источника), щелкните мышью по этим ячейкам, при этом расположите окна файлов на экране так, чтобы они не перекрывали друг друга.Полный адрес ячейки состоит из названия рабочей книги в квадратных скобках, имени листа, восклицательного знака и адреса ячейки на листе.В ячейке В3 файла «Полугодие» формула для расчета полугодового дохода имеет вид:

='[1 квартал.xls]Лист1'!$B$3+'[2 квартал.xls]Лист1'!$B$3

Аналогично рассчитайте полугодовые значения Расходов и Прибыли, используя данные файлов «1 квартал» и «2 квартал». Результаты работы представлены на рис.7.1. Сохраните текущие результаты расчетов.

 

 

Рис. 7.1. Результат вычисления

 

Задание 2.

ПРАКТИЧЕСКАЯ РАБОТА №8

Задание №1.

Задание 2.

Фирма поместила в коммерческий банк 45 000 р. на 6 лет под 10,5% годовых. Какая сумма окажется на счете, если проценты начисляются ежегодно? Рассчитать, какую сумму надо поместить в банк на тех же условиях, чтобы через шесть лет накопить 250 000 р.

Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу. Создайте таблицу констант и таблицу для расчета наращенной суммы вклада по образцу (рис. 8.4).

 

Рис. 8.4. Исходные данные

 

Произведите расчеты. А(n) двумя способами:

· с помощью формулы А(n)= А(0) Н (1+j)n (В ячейку D10 ввести формулу =$B$3*(1+$B$4)^A10 или использовать функцию СТЕПЕНЬ);

· с помощью функции БС (рис.8.5).

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

Синтаксис функции БС: БС (ставка;кпер; плт; пс; тип), где ставка – это процентная ставка за период; кпер – это общее число периодов платежей по аннуитету; плт (плата) – это выплата, производимая в каждый период, вводится со знаком «–», это значение не может меняться в течении всего периода выплат. Обычно плата состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов;

пс – это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.

Если аргумент пс опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента плата.

Тип - это число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент тип опущен, то он полагается равным 0 (0 – платеж в конце периода, 1 –платеж в начале периода).

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

Для ячейки С10 задание параметров расчета функции БС имеет вид, как на рис.8.5.

 

 

Рис. 8.5. Аргументы функции БС

 

Конечный вид расчетной таблицы приведен на рис.8.6.

 

 

 

Рис. 8.6. Расчетная таблица

 

Используя режим Подбор параметра рассчитайте, какую сумму надо поместить в банк на тех же условиях, чтобы через шесть лет накопить 250 000 р. (рис. 8.7).

В результате подбора выясняется, что для первоначальная сумма для накопления 137 330,29 р. позволит накопить заданную сумму 250000 р.

 

 

Рис. 8.7. Подбор параметра

Задание 3.

Сравнить доходность размещения средств предприятия, положенных в банк на один год, если проценты начисляются m раз в год, исходя из процентной ставки j=9,5% годовых. По результатам расчетов построить график изменения доходности инвестиционной операции от количества раз начисления процентов в году (капитализации).
Выясните, при каком значении j доходность составит 15% (при капитализации m=12).

Исходные данные представлены на рис 8.8.

 

Рис. 8.8. Исходные данные

Формула для расчета доходности: Доходность = (1 + j/m)m – 1.
Установите формат значений доходности – процентный. Для проверки правильности ваших расчетов сравните полученный результат с правильным ответом: для m=12 доходность =9,92%. Произведите обратный расчет (используя режим Подбор параметра, рис. 8.9) для выяснения, при каком значении j доходность составит 15% (при капитализации m=12).

 

 

Рис. 8.9. Подбор параметра

 

ПРАКТИЧЕСКОЕ ЗАДАНИЕ №9

Задание 1.

Создать таблицу расчета прибыли фирмы, произвести расчеты суммарных доходов, расходов (прямых и прочих) и прибыли; произвести пересчет прибыли в условные единицы по курсу. Выяснить, при каком значении зарплаты прибыль будет равна 500000 р. (используйте режим Подбор параметра).

Исходная таблица представлена на рис. 9.1.

Рис. 9.1. Исходные данные

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

Расходы: всего = Прямые расходы + Прочие расходы

Прибыль = Доходы: всего – Расходы: всего

Прибыль (у.е.) = Прибыль * Курс 1 у.е.

Задание 2.

Фирма хочет накопить деньги для реализации нового проекта. С этой целью в течении пяти лет она кладет на счет ежегодно по 1250 $ в конце каждого года под 8% годовых. Определить сколько будет на счете фирмы к концу пятого года. Построить диаграмму по результатам расчетов. Выяснить, какую сумму надо ежегодно класть на счет, чтобы к концу пятого года накопить 10000 $. Исходные данные представлены на рис. 9.2.

Рис. 9.2. Исходные данные

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

Сумма на счете = D * ((1+j)^n – 1)/j

Сравните полученный результат с правильным ответом:
для n=5 сумма на счете составляет 7333,25$.

Для расчета суммы ежегодного вклада для накопления к концу пятого года 10 000 $, используйте режим Подбор параметра.

 

ПРАКТИЧЕСКАЯ РАБОТА №1

ВЫЧИСЛИТЕЛЬНЫЕ ФУНКЦИИ MS EXCEL ДЛЯ

ФИНАНСОВОГО АНАЛИЗА

Задание №1.

Создать таблицу финансовой сводки за неделю, произвести расчеты, построить диаграмму изменения финансового результата, произвести фильтрацию данных. Исходные данные представлены на Рис.1.1.

Рис. 1.1. Исходные данные

Произведите расчеты в графе «Финансовый результат» по следующей формуле: Финансовый результат = Доход – Расход. Вводите расчетные формулы только для расчета по строке «Понедельник», далее произведите автокопирование формул.

Для ячеек с результатом расчетов задайте формат – «Денежный» с выделением отрицательных чисел красным цветом. Для этого выделите необходимые ячейки и выполните команду Формат – Формат ячеек. В появившемся диалоговом окне (рис. 1.2) выберите числовой формат «Денежный» и отрицательные числа красным цветом. Число десятичных знаков задайте равное 2. Обратите внимание, как изменился цвет отрицательных значений финансового результата на красный.

 

Рис. 1.2. Диалоговое окно Формат ячеек

 

Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка ). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические». Для расчета функции СРЗНАЧ дохода установите курсор в соответствующей ячейке (В11), запустите Мастер функций и выберите функцию СРЗНАЧ. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В4:В10.
Аналогично рассчитайте среднее значение расхода.

В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирования на панели инструментов или функцией СУММ. В качестве первого числа выделите группу ячеек с данными для расчета суммы – D4:D10.

Конечный вид таблицы приведен на рис. 1.3.

 

 

Рис. 1.3. Итоговая таблица

 

Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с использованием мастера диаграмм.
Для этого выделите интервал ячеек с данными финансового результата D4:D10 и выберите команду Вставка – Линейчатая Диаграмма. Затем щелкните правой кнопкой мыши на диаграмме и в контекстном меню выберите пункт Выбрать данные (рис. 1.4).

В появившемся диалоговом окне Выбор источника данных (рис. 1.5)в разделе Подписи оси X нажмите кнопку Изменить и укажите в качестве источника строк ячейки A4:A10, затем нажмите кнопку OK.

 

Рис. 1.4. Контекстное меню

 

Рис. 1.5. Диалоговое окно Выбор источника данных

 

Окончательный вариант диаграммы приведен на рис. 1.6:

 

 

Рис. 1.6. Окончательный вид диаграммы

 

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

Нажмите кнопку OK. Произойдет отбор данных по заданному условию. Проследите, как изменился вид таблицы и построенная диаграмма (рис. 1.8 и 1.9).

 

Рис. 1.7. Диалоговое окно Пользовательский автофильтр

 

Рис. 1.8. Таблица после фильтрации

 

Рис. 1.9. Диаграмма после фильтрации

 

Задание №2.

Заполнить таблицу «Анализ продаж» (рис.1.10), произвести расчеты, выделить минимальную и максимальную сумму покупки; по результатам расчета построить круговую диаграмму суммы продаж.

 

Рис. 1.10. Таблица Анализ Продаж

Задание №3.

Заполнить ведомость учета брака (рис.1.11), произвести расчеты, выделить минимальную, максимальную и среднюю сумму брака, а также средний процент брака; произвести фильтрацию данных по условию процента брака < 9 %, построить график отфильтрованных значений изменения суммы брака по месяцам.

Рис. 1.11. Ведомость учета брака

ПРАКТИЧЕСКАЯ РАБОТА №2

ГРАФИЧЕСКОЕ ИЗОБРАЖЕНИЕ СТАТИСТИЧЕСКИХ ДАННЫХ И ПРОГНОЗИРОВАНИЕ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

Задание №1.

С помощью диаграммы (обычная гистограмма) отобразить данные о численности населения России (млн. чел.) за 1970- 2005 гг.

Рис. 2.1. Исходная таблица

 

Создайте на листе 1 таблицу численности населения по образцу (рис.2.1). Для ввода значений лет создайте ряд чисел с интервалом в 7 лет. Затем заполните года с помощью маркера Автозаполнения.

Постройте диаграмму (обычная гистограмма) по данным таблицы. Для этого выделите интервал ячеек с данными численности населения А3:G3 и выберите команду Вставка - Гистограмма. Затем щелкните правой кнопкой мыши на появившейся диаграмме и выберите пункт Выбрать данные в контекстном меню. В появившемся диалоговом окне в разделе Подписи горизонтальной оси измените значения ячеек на необходимые. Готовая диаграмма должна выглядеть следующим образом (рис. 2.2):

 

 

Рис. 2.2. Диаграмма Численности населения

 

Задание №2.