Отчет к практике вариант 2. Задание для варианта 2 6 2 Использованные функции 7
 Скачать 1.1 Mb.
|
|
Титульный лист Содержани Введение 4 Задание для варианта 2 6 2 Использованные функции 7 3 Создание справочников 8 5 Выполнение вычислений 16 Список использованных источников 27 Введение 5 Задание для варианта 2 7 2 Использованные функции 8 3 Создание справочников 9 4 Оформление таблицы БюджетПродаж 11 5 Выполнение вычислений 17 6 Промежуточные итоги 20 7 Фильтрация данных 22 8 Сводная таблица 24 9 Построение гистограммы 26 Заключение 29 Список использованных источников 30 ВведениеВ настоящее время невозможно представить себе современного человека без знания компьютерных технологий а профессиональную деятельность без использования программ пакета Microsoft Office. Знание программ этого пакета Word и Excel позволяют удобно и быстро выполнять множество поставленных задач, сохраняя при этом огромное количество данных. Процесс функционирования холдинга «МИР ТЕПЛА» – сложный процесс, включающий все этапы предоставления услуг (проектирование, комплектацию, монтаж т.п.) и смежные процессы (формирование отчетности, сезонная наценка и т.п.). В ходе выполнения работы необходимо составить бюджет продаж холдинга «МИР ТЕПЛА». Составление бюджета продаж является важной задачей, так как содержит информацию о запланированном объеме продаж, наценках, цене и ожидаемом доходе от реализации каждого вида продукции. Актуальность в том, что качество составления бюджета продаж напрямую влияет на успешную работу холдинга. Цель работы ‒ составить бюджет продаж холдинга «МИР ТЕПЛА». Задачи работы: ‒ получить практические навыки работами с программами Word и Excel; – рассмотреть все функции, которые используются для расчета в таблице недостающих данных; – улучшить навыки форматирования таблиц: заливка, установка границ, форматов данных; – провести вычисления с помощью абсолютных и относительных ссылок; – получить по товарам общий объем и сумму используя инструмент промежуточные итоги; – выполнить фильтрацию для отбора необходимых данных с сезонными наценками; – построить сводную таблицу; ‒ по сводной таблице построить гистограмму. Задание для варианта 21. На листе Справочники подготовить таблицу.
2. На листе БюджетПродаж оформить таблицу согласно образцу. Ячейке, содержащей 1,50% дать имя СезонНац, 1,00% ‒ имя Инфляция. Подготовить именованные блоки на листе Справочники для ввода данных на листе БюджетПродаж. Столбец Товар заполнить в виде списка; а Цена с помощью функции ВПР. Бюджет продаж холдинга «МИР ТЕПЛА»
Ожидаемая инфляция в месяц:
3. Выполнить вычисления: ▪ Наценка на инфляцию = Цена * Инфляция (Месяц-1); ▪ Сезонная наценка = Цена * СезонНац. Наценка начисляется на кондиционеры до сентября или на обогреватели после сентября. В остальных случаях вывести 0. ▪ Сумма = Объем * (Цена товара + Сезонная наценка + Наценка на инфляцию); 4. На листе Итоги по товарам получить общий объем и сумму. 5. На листе Фильтр с помощью расширенного фильтра выбрать записи, для которых начислена сезонная наценка. Результат вывести в новой таблице:
6. Построить Сводную таблицу, в которой по месяцам вывести объем и сумму. Сгруппировать данные по полю месяц - шаг 3. В созданную сводную таблицу добавить товар в область столбцов. 7. По сводной таблице построить гистограмму. Отобразить сумму по товарам за полученные периоды. 8. Средствами Word подготовить документ «Информация о наценка», установить связь с таблицей листа БюджетПродаж в Excel. 2 Использованные функции1. Функция ВПР используется для поиска нужного нам значения в первом столбце таблицы по строкам и возвращения значение из найденной ячейки. Синтаксис функции ВПР: ВПР(искомое_значение, инфо_таблица, номер_столбца, интервальный_просмотр) искомое значение – задает значение, которое функция ищет в первой колонке матрицы (если это значение не будет найдено, будет взято ближайшее меньшее; если меньшего не существует, возникнет ошибка #Н/Д); инфо_таблица – таблица, содержащая искомые данные; номер_столбца – колонка в найденной строке, из которой должно быть взято значение; интервальный_просмотр – логическое значение, которое определяет характер поиска: точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возращается значение ошибки # Н/Д.[3] 2.Функция ЕСЛИ проверяет, выполняется ли определенное условие на выполнение. Синтаксис функции ЕСЛИ: ЕСЛИ(лог_выражение;[значение_если_истина]; [значение_если_ложь]) лог_выражение – проверяемое значение ячейки; значение_если_истина ‒ значение появится в ячейке, если заданное условие верно; значение_если_ложь ‒ значение появится в ячейке, если заданное условие не верно это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ. 3. Функция ИЛИ возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.[3] Синтаксис функции ИЛИ: ИЛИ [логическое_значение1, логическое_значение2,…] логическое _значение1, логическое значение2, ‒ проверяемых условий от одного до 30, значение либо Истина, либо ЛОЖЬ. 3 Создание справочниковВ рабочей книге переименуем лист 1 на Справочники, лист 2 на БюджетПродаж в соответствии с рисунком 1.  Рисунок 1 ‒ Переименование листов Подготовим таблицу на листе Справочники. Для создания таблицы проводим форматирование, выделяем нужное нам количество ячеек, выбираем на панели задач вкладку Главная и щелкаем кнопку Границы, выпадает меню, выбираем нужный тип границ ячеек в соответствии с рисунком 2.  Рисунок 2 – Установка границы таблицы Вводим данные в ячейки, выбрали нужный шрифт, размер шрифта и заливаем шапку таблицы, выбираем на панели задач вкладку Главная, щелкаем кнопку Цвет заливки выпадает меню, выбираем нужный цвет заливки в соответствии с рисунком 3.  Рисунок 3– Заливка ячеек В результате получим следующий вид таблицы в соответствии с рисунком 4.
Рисунок 4 – Внешний вид таблицы 4 Оформление таблицы БюджетПродаж Введем заголовок таблицы и для того чтобы наш заголовок был по центру нам надо на вкладке Главная, выбрать иконку объединить и поместить в центре в соответствии с рисунком 5.  Рисунок 5 ‒ Объединение и выравнивание по центру Оформим макет таблицы согласно образцу и получим таблицу в соответствии с рисунком 6.  Рисунок 6 – Макет таблицы БюджетПродажЯчейке содержащей 1,50% дадим имя СезонНац, делается это так: 1. выделяем ячейку для которой хотим задать имя; 2. щелкаем на выделенном фрагменте правой кнопкой мыши выбираем в контекстном меню команду Имя диапазона, открывается диалоговое окно, введем в него данные и нажимаем ОК в соответствии с рисунком 7.  Рисунок 7‒ Создание имени СезонНац Точно также задаем имя ячейке Инфляция в соответствии с рисунком 8.  Рисунок 8 ‒ Создание имени Инфляция Подготовим точно также именованные блоки на листе Справочники и получим в соответствии с рисунком 9.   Рисунок 9 ‒ Создание именных блоков После этого нам необходимо столбец Товар заполнить в виде выпадающего списка, выбираем на панели задач вкладку Данные и кнопку Проверка данных, введем данные в диалоговое окно Проверка вводимых данных в соответствии с рисунком 10.  Рисунок 10 ‒Источник списка Далее создадим вспомогательные сообщения для ввода данных с помощью списка в соответствии с рисунком 11.  Рисунок 11 – Установка сообщения для ввода Также зададим сообщение об ошибке:  Рисунок 12 – Установка сообщения об ошибке В результате получим следующую таблицу в соответствии с рисунком 13.  Рисунок 13 – Таблица Заполним Цену с помощью функции ВПР, для этого нам надо на вкладке Формулы выбрать иконку вставить функцию щелкаем, всплывает диалоговое окно, выбираем из списка ВПР и нажимаем ОК в соответствии с рисунком 14.  Рисунок 14‒ Поиск функции ВПР После нажатия ОК у нас всплывает новое диалоговое окно, Аргументы функции, введем данные в диалоговое окно и нажимаем ОК в соответствии с рисунком 14.  Рисунок 14 – Аргументы функции ВПР Формулу протягиваем по всем ячейкам вниз. В результате получим следующую таблицу в соответствии с рисунком 15.  Рисунок 15‒ Таблица 5 Выполнение вычисленийНаценка на инфляцию вычисляется по формуле(1) Наценка на инфляцию = Цена * Инфляцию * (Месяц-1), 1) Получаем формулу(2) Наценка на инфляцию = С8 * Инфляцию * (В8-1), 2) где С8 ‒ цена; Инфляция ‒ ячейка Е5 с присвоенным именем Инфляция; В8-1 ‒ месяц -1. Полученную формулу протягиваем по всем ячейкам вниз и получим результат в соответствии с рисунком 16.  Рисунок 16 ‒ Таблица Для вычисления сезонной наценки нам надо наценку начислить на кондиционеры до сентября или на обогреватели после сентября а в остальных случаях вывести 0. Для данного вычисления нам надо использовать функцию ЕСЛИ. Вкладка Формулы, выбираем логические функции из выпавшего меню ЕСЛИ, введем данные в диалоговое окно в соответствии с рисунком 17.  Рисунок 17 – Использование функции ЕСЛИ Протягиваем данную формулу вниз по всем ячейкам и получаем таблицу в соответствии с рисунком 18.  Рисунок 18 ‒ Таблица Сумма вычисляется по формуле(3) Сумма = Объем *(Цена товара + Сезонная наценка +Наценка на инфляцию), 3) Получаем формулу(4) Сумма = F8 * (C8 + E8 + D8), 4) Полученную формулу протягиваем по всем ячейкам вниз и получим результат в соответствии с рисунком 17.  Рисунок 17 – Заполненная таблица 6 Промежуточные итоги Для получения по товарам общий объем и сумму используем инструмент Промежуточные итоги. Для этого скопируем таблицу Бюджет продаж на новый лист Итоги. На вкладке Данные выбираем иконку Промежуточные итоги, зададим следующие настройки в соответствии с рисунком 18.  Рисунок 18 – Промежуточные итоги В результате получим таблицу в соответствии с рисунком 19.   Рисунок 19 – Результат подведения промежуточных итогов 7 Фильтрация данных На листе Фильтр с помощью расширенного фильтра нам надо выбрать записи, для которых начислена сезонная наценка и результат вывести в новой таблице . Скопируем нашу таблицу с листа Бюджет продаж на лист Итоги. Подготовим критерий фильтрации в соответствии с рисунком 20.  Рисунок 20 – Критерий фильтрацииПодготовим таблицу для выведения результата в соответствии с рисунком 21.  Рисунок 21 ‒ Таблица После этого откроем вкладку Данные, выбираем иконку Фильтр Дополнительно и щелкаем, всплывает диалоговое окно Расширенный фильтр, введем в него данные в соответствии с рисунком 22.  Рисунок 22 – Расширенный фильтр В результате этого получим отфильтрованную таблицу в соответствии с рисунком 23.  Рисунок 23 – Результат фильтрации 8 Сводная таблица Для создания сводной таблицы, выбираем вкладку Вставка, иконку Сводная таблица и Щелкаем, введем данные в сплывшее диалоговое окно в соответствии с рисунком 24.  Рисунок 24 ‒ Создание сводной таблицы Получим на листе Сводная, макет сводной таблицы, заполним его в соответствии с рисунком 25.  Рисунок 25 – Макет сводной таблицы Данные сгруппируем по полю месяц – шаг три, для этого выделяем диапазон ячеек, В9:В15, щелкаем правой кнопкой мыши и зададим настройки в сплывшем диалоговом окне в соответствии с рисунком 26.  Рисунок 26‒ Группирование по полю месяц Получим таблицу в соответствии с рисунком 27.  Рисунок 27 – Сводная таблица 9 Построение гистограммы По сводной таблице нам надо построить гистограмму для этого на листе Сводная выделяем нашу таблицу сводную, выбираем вкладку Вставка, иконку Гистограмма и из выпавшего меню Гистограмма с группировкой щелкаем получаем в соответствии с рисунком 28.  Рисунок 28 ‒ Гистограмма Для переноса гистограммы на лист Диаграмма выбираем вкладку Конструктор, иконку Переместить диаграмму и зададим настройки в сплывшее окно в соответствии с рисунком 29.  Рисунок 29 ‒ Перемещение диаграммы. И получаем гистограмму на листе Диаграмма. Заключение При выполнении задания учебной практики были изучены и применены функции пакета Microsoft Excel, также закреплены навыки работы с пакетом Word. В ходе проделанной работы были выполнены следующие задачи: – рассмотрены все функции, которые используются для расчета в таблице недостающих данных; – усовершенствованы навыки форматирования таблиц: заливка, установка границ, форматов данных; – проведено вычисление с помощью абсолютных и относительных ссылок; – использован инструмент Промежуточные итоги для получения по товарам общего объема и суммы; – выполнена фильтрация для отбора необходимых данных с сезонными наценками; – построена сводная таблица; ‒ по сводной таблице построена гистограмма. Составлен бюджета продаж холдинга «МИР ТЕПЛА», который содержит информацию о цене, наценках на инфляцию, сезонной наценке, о запланированном объеме продаж и сумме. Бюджет продаж холдинга «МИР ТЕПЛА» показал, что объем продаж кондиционеров возрастает до сентября, но в этот период падает спрос на обогреватели и после сентября возрастает спрос на обогреватели, но падает спрос на кондиционеры. Поставленные задачи выполнены, и цель задания достигнута. Список использованных источников1 Волков, В.Б. Понятный самоучитель Excel 2010 / В.Б. Волков ‒Питер, 2010. ‒256 с. 2 Комягина, В. Б. Компьютер для студентов.Самоучитель.Быстрый старт.: учебное пособие / В.Б. Комягина. ‒ М.: Издательство ТРИУМФ, 2003. ‒ 400 с. 3 Пикуза, В. Экономические и финансовые расчеты в Excel. Самоучитель / В. Пикуза, А. Гаращенко. ‒ СПб.: Питер; К.: Издательская группа ВНV, 2008. ‒ 397 с. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||