Скачать 0.67 Mb.
|
Задание № 10
Исходные данные для решения задачи следующие:
В результате решения задачи должен быть сформирован следующий документ:
РАБОТА №2 Обработка и анализ экономической информации в EXCEL I. Создать таблицы для расчета заработной платы 10 работникам предприятия: 1. Создать рабочую книгу Расчет зарплаты.xls. Переименовать текущий лист рабочей книги в лист с именем «Справочник».
Необходимо рассчитать:
ЗП=ЗПР*ФТ/Т;
УПФ=ЗП*0.01;
УПН=(ЗП-УПФ-МЗП*Л)*ПРПДН/100;
УПВЗН=ЗП*ПРВЗН/100 (только для членов профсоюза);
УАЛ=ПРАЛ*(ЗП-УПН)/100 (только для лиц, выплачивающих алименты);
ЗПВ=ЗП-УПФ-УПН-УПВЗН, где: ФT – фактически отработанное время (дней); Л – количество льгот; ЗПР – оклад работника в соответствии с его разрядом; МЗП – минимальный размер оплаты труда; Т – плановое количество рабочих дней в месяце; ПРАЛ – процент удержания алиментов; ПРВЗН – процент удержания профсоюзных взносов; ПРПДН – процент удержания подоходного налога.
Таблица 2 Разрядная сетка
В процессе решения задачи необходимо будет задавать размер минимальной зарплаты и количество рабочих дней в месяце.
ЧИСТРАБДНИ (нач_дата;кон_дата;праздники) Перед вводом этой функции убедиться, что она доступна, т. е. имеется в списке стандартных функций категории «Дата и время». Если этой функции в списке нет, то ввести ее в список, выполнив команду СЕРВИС/Надстройка и включить на вкладке флажок «Пакет анализа». Ввести в качестве начальной даты текст: «01.01.1998». Ввести в качестве конечной даты текст: «31.01.1998». Ввести в качестве 3-го аргумента (праздники) диапазон ячеек на листе «Справочник», содержащий список праздничных дней.
Задание. Сформировать сводную таблицу с итогами по начислениям и удержаниям заработной платы методом консолидации. Необходимо создать новую таблицу, содержащую итоги по каждому работнику за полгода и имеющую связь с исходными данными в таблицах расчета заработной платы за период январь-июнь. Технология формирования сводной таблицы
Списком называется таблица, содержащая однородные данные по строкам и столбцам. Списки можно сортировать, осуществлять в них поиск данных по различным критериям, обрабатывать с помощью фильтров и форм данных. Найденные в списке данные могут в дальнейшем подвергаться дополнительной обработке: удалению, корректировке, включению в вычисления, построению на их основе диаграмм. 1. Использование формы данных. С помощью форм можно просматривать и вводить данные в список, искать строки по содержимому ячеек, а также удалять строки из списка. Задание 1. Использовать форму данных для просмотра и корректировки записей таблицы «Справочник работников». Форма — это наиболее удобный способ для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям. Перед работой с формой необходимо задать заголовки столбцов списка. Эти заголовки используются для создания полей формы. Технология:
2. Использование автофильтра. В Microsoft Excel предусмотрены различные методы анализа данных в списке. К списку можно применить фильтр, чтобы отобрать только записи, соответствующие определенным условиям. Для этого служит команда ДАННЫЕ/Автофильтр. Задание 2. Использовать автофильтр для вывода в таблице «Справочник работников» информации только о работниках, оклад которых меньше заданного значения. Технология:
Задание 3. Использовать автофильтр для вывода в таблице «Справочник работников» информации о работниках:
3. Использование расширенного фильтра. Расширенный фильтр позволяет более гибко настроить фильтр для просмотра сведений из списка данных. Просмотр осуществляется на основании условий отбора. В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение. Порядок составления условий отбора смотрите в разделе помощи Excel «Примеры условий отбора расширенного фильтра». Задание 4. Использовать расширенный фильтр для получения данных о работниках 1-го отдела с окладом меньшим 5000000 руб. и не являющихся членами профсоюза. Технология:
Задание 5. Использовать расширенный фильтр для получения данных о работниках 1-го и 3-его отделов, с окладом меньшим 500000 руб. и не являющихся членами профсоюза. Использование электронных таблиц при решении задач с использованием функции «Подбор параметра» Подбор параметра является способом прогнозирования значений с помощью анализа «что-если». При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение. Задание 1. Подобрать цену на продукцию и величину накладных расходов для получения заданной прибыли, используя метод подбора параметров. Технология решения: 1. На Лист 1 новой книги ввести данные калькуляции цены книги, приведенные в таблице 1. Константами должны быть: количество экземпляров, % накладных расходов, затраты на зарплату, затраты на рекламу, цена продукции и себестоимость продукции (в таблице эти значения показаны на сером фоне). Остальные данные должны быть вычислены на основании расчетных формул. Введите формулы и сверьте результаты расчета по ним с данными, приведенными в таблице. Таблица 1
2. Переименовать Лист 1 в Калькуляция и скопировать отлаженную таблицу с формулами в Лист 2. Исследуйте информацию, представленную на таблице листа Калькуляция. Увеличение прибыли может быть достигнуто за счет изменения количества экземпляров, себестоимости, валовых издержек и уровня накладных расходов. 3. Подобрать такую цену книги, чтобы прибыль от продукции составила 50 000 000 руб. Для этого:
4. Ознакомиться с результатами выполнения операции подбора параметра в окне «Состояние подбора параметра» и щелкнуть кнопку «Ok» для изменения значений ячеек таблицы в соответствии с найденным решением. 5. Вернуться к исходному состоянию таблицы, используя описанный в пунктах 3, 4 способ подбора параметра. 6. Самостоятельно определить, каков должен быть показатель % накладных расходов, чтобы прибыль за продукцию составила 40 000 000 рублей. Справка для составления расчетных формул: Доход = Цена продукции * Количество экземпляров. Себестоимость реализованной продукции = Себестоимость продукции* Количество кземпляров. Валовая прибыль = Доход – Себестоимость реализованной продукции. Накладные расходы = Доход * % накладных расходов. Валовые издержки = Накладные расходы + Затраты на зарплату + Затраты на рекламу. Прибыль от продукции = Доход – Себестоимость реализованной продукции. РАБОТА № 3 Решение экономических задач средствами сводных таблиц Цель работы: Изучение технологии создания сводных таблиц; Группировка и обновление данных в сводных таблицах; Консолидация диапазонов исходных таблиц. Пример: Сводная таблица – инструмент обработки баз данных. Поскольку в этом случае сразу подводятся итоги, выполняется сортировка и фильтрация списков, то сводная таблица является мощным инструментом обработки данных. Перед построением сводной таблицы на основе списка следует убрать из него промежуточные итоги и фильтры. Рассмотрим создание сводной таблицы на примере Таблицы1, содержащей данные о продажах издательской фирмы. Список упорядочен по кварталам, инвентарным номерам, каналам распространения, количеству проданных книг и полученным от продажи средствам. Таблица 1 При создании сводной таблицы можно использовать один из четырех типов источников данных: Список Excel; Внешний источник данных; Несколько диапазонов консолидации; Другую сводную таблицу. В данном примере создадим сводную таблицу из списка Excel. Прежде всего, выделим ячейку в списке, на основе которого будем создавать таблицу. Этот шаг не является обязательным, но позволяет экономить время. В меню Данные выберем команду Сводная таблица. Будет запущен мастер сводных таблиц, включающий в себя следующие шаги: Задание типа источника данных и вида создаваемого отчета. Указание местонахождения исходных данных. Указание места размещения таблицы. Первое окно диалога мастера сводных таблиц представлено на рис.1 рис. 1 На следующем шаге исходный диапазон будет определен автоматически, если перед построением была выделена только одна ячейка списка. В последнем окне мастера следует указать место, где будет расположена сводная таблица. После нажатия кнопки Готово, на рабочем листе будет отображен пустой макет таблицы и панель инструментов Сводные таблицы с кнопками для каждого поля из источника данных (рис. 2). рис.2 Перетащите кнопки полей Инв.№ и Канал в область строк, кнопки Год и Квартал – в область столбцов, кнопку Получено – в область данных. В каждую область можно поместить любое количество полей. Чтобы удалить поле, перетащите его кнопку за пределы макета. Вы получите сводную таблицу следующего вида: Таблица 2 Реорганизация сводной таблицы Чтобы реорганизовать сводную таблицу, просто нужно перетащить одну или несколько кнопок полей. Например, чтобы переместить поле с оси столбцов на ось строк, достаточно перетащить его кнопку из области столбцов в область строк. Можно также изменять порядок отображения полей по оси столбцов или строк сводной таблицы. В нашем примере мы поместили поле Инв.№ слева от поля Канал. Если поменяем их местами, получим сводную таблицу следующего вида: Таблица 3 Создание сводной диаграммы Можно создать сводную диаграмму, при построении сводной таблицы, установив соответствующий переключатель в первом окне мастера сводных таблиц. Сводную диаграмму также можно создать после построения сводной таблицы. Для этого нужно выделить любую ячейку в сводной таблице и нажать кнопку Мастер диаграмм на панели инструментов Сводные таблицы. Диаграмма и таблица являются объектами и изменения в одном из объектов, сразу же отражаются в другом. На рис. 3 показана сводная диаграмма, связанная с Таблицей 3. рис. 3 Можно перестроить сводную диаграмму так же, как и таблицу, перетаскивая кнопки полей с одной оси на другую. Для удаления поля достаточно перетащить его за пределы диаграммы. Обновление сводной таблицы Хотя сводная таблица связана с исходными данными, но она не обновляется автоматически при изменении исходных данных. Чтобы обновить сводную таблицу, следует выделить в ней ячейку и в меню Данные выбрать команду Обновить данные, или использовать одноименную кнопку на панели инструментов Сводные таблицы. Группировка данных Excel автоматически группирует элементы внутреннего поля для каждого заголовка внешнего поля и, если требуется, создает промежуточные итоги для каждой группы элементов внутреннего поля. Но иногда удобнее группировать элементы иным способом, например, собрать мелкие элементы в большие группы. Excel предлагает несколько вариантов группировки элементов. Предположим, что после создания сводной таблицы (Таблица 2), требуется сравнить подписку (Заказ по почте), с розничной продажей, объединив каналы Внутри страны и Международный в один сводный канал с именем Розничный. Чтобы создать такую группу, следует: Выделить заголовки Внутри страны и Международный в любом месте таблицы. Выбрать команду Группировать в подменю Группа и структура меню Данные. Будет создано новое поле с именем Канал2 и выделенные элементы будут сгруппированы в новый элемент с именем Группа1. Выделить любую ячейку с заголовком Группа1 и ввести новое имя Розничный. рис. 4 Чтобы удалить все группы и вернуть поле к первоначальному (не сгруппированному) состоянию, нужно выделить сгруппированный элемент и выбрать команду Разгруппировать в подменю Группа и структура меню Данные. Внимание! Если одна сводная таблица служит источником данных для другой, группировка в любой из них влияет на связанную таблицу. Вычисления в сводных таблицах К числовым полям, помещенным в область данных сводной таблицы, по умолчанию применяется функция Сумма, а к любым нечисловым полям – функция Количество. Чтобы использовать другие итоговые функции, нужно выделить любую ячейку в области данных и нажать кнопку Параметры поля на панели инструментов Сводные таблицы. В появившемся диалоговом окне Вычисление поля сводной таблицы, показанное на рис. 5, выбрать требуемую функцию. рис. 5 Кнопка Дополнительно, позволяет использовать ряд дополнительных вычислений. Например, можно в области данных отобразить долю от итога строки или столбца, в котором находится ячейка, долю текущего значения от заданного элемента или вывести накопленный итог (рис.6). рис. 6 По умолчанию дополнительные вычисления не производятся. Чтобы применить дополнительную операцию нужно раскрыть список Дополнительных вычислений, выбрать в нем нужный вариант и затем в списках Поле и Элемент, указать базовое поле и базовый элемент, которые определяют данные, используемые при дополнительном вычислении. В Таблице 4 приведены краткие описания дополнительных вычислений. Таблица 4
Использование вычисляемых полей и элементов В сводную таблицу можно включать вычисляемые поля и элементы. Вычисляемое поле – это новое поле, полученное с помощью операций над существующими полями сводной таблицы. Вычисляемый элемент – это новый элемент в существующем поле, полученный с помощью операций над другими элементами этого поля. При создании вычисляемых полей и элементов допускаются арифметические операции с любыми данными сводной таблицы, но нельзя ссылаться на данные рабочего листа, находящиеся вне таблицы. Чтобы создать вычисляемое поле, выделите в сводной таблице любую ячейку. Затем выберите команду Формулы в меню Сводная таблица на панели инструментов Сводные таблицы. В подчиненном меню Формулы выберите команду Вычисляемое поле, и появится окно диалога, представленное на рис. 7. рис. 7 В поле Имя нужно ввести имя вычисляемого поля. В поле Формула создать формулу, используя стандартные арифметические операции и ссылки на другие поля. Чтобы ввести в формулу имя поля, нужно выделить его в списке и нажать кнопку Добавить поле. Консолидация диапазонов исходных таблиц С помощью сводных таблиц можно консолидировать данные из таблиц расположенных на разных рабочих листах. Обязательным условием такой консолидации является единая структура таблиц. При этом каждая таблица должна содержать данные одного временного (или другого типа) диапазона. Например, данные о продажах сформированы на разных листах с именами 2003год, 2004 год, 2005 год. Для построения сводной ведомости за несколько лет, не обязательно переносить данные на один лист, можно воспользоваться средством консолидации мастера сводных таблиц. Для этого следует: перейти на новый лист, выбрать в меню Данные/Сводная таблица. На первом шаге мастера надо установить флажок В нескольких диапазонах консолидации. На втором шаге по умолчанию переключатель Создать одно поле страницы. Далее необходимо указать диапазон каждой таблицы, включая в него заголовки столбцов и строк, кроме итоговых. Затем формируется макет итогового документа, только не отображаются имена полей консолидируемых таблиц. Можно задать любые имена полей после завершения формирования сводной таблицы. Суммирование значений исходных диапазонов происходит по умолчанию. Кроме функции суммирования могут использоваться и другие функции: СЧЕТ(), СРЗНАЧ(), МАКС(), МИН(), ПРОИЗВЕД(), ДИСП() – несмещенная дисперсия, ДИСПР() – смещенная дисперсия, СТАНДОТКЛОН() – несмещенное отклонение, СТАНДОТКЛОНП() – смещенное отклонение. Для того, чтобы выбрать другую итоговую функцию для вычисления поля данных, нужно выделить любой элемент в этом поле, и на панели Сводные таблицы выбрать кнопку Параметры поля. В появившемся диалоговом окне кроме выбора функции, можно изменить имя поля. Задание для выполнения: Создать таблицу по интересующей Вас теме, например: Учет выпуска продукции; Начисление заработной платы; Учет успеваемости студентов, и т.д. Таблица должна быть создана за определенный период времени (год, квартал, месяц). На основе созданной таблицы разработать: Сводную таблицу; Диаграмму на основе сводной таблицы; Группировку данных в сводной таблице; Провести вычисления в сводной таблице (на основе встроенных функций и с помощью вычисляемых полей); Провести консолидацию данных (для этого разработать несколько дополнительных таблиц, по аналогии с исходной. Каждая таблица разрабатывается за отдельный временной период и расположена на отдельном листе). |
Если номер зачетной книжки превышает число предлагаемых вариантов, то отсчет начинается опять с первого номера (например, номер зачетной... | Настоящее положение об олимпиаде по информатике и информационным технологиям (далее Олимпиада) определяет порядок организации и проведения... | ||
Примерной программы среднего (полного) общего образования по информатике и информационным технологиям | В. Г. Питеев кандидат технических наук, доцент, заведующий кафедрой экономики и управления | ||
Данная рабочая программа составлена на основании: Стандарта среднего (полного) общего образования по информатике и информационным... | Гончарова Н. А. Учебная практика: методические указания по прохождению учебной практики / Н. А. Гончарова – Братск: фгбоу впо «БрГУ»,... | ||
Учебная практика относится к профессиональному циклу. В структуре магистерской программы учебная практика является основой для изучения... | Практика – одна из важнейших составляющих профессиональной подготовки студента. Практика является составной частью основной образовательной... | ||
Учебная практика: рабочая учебная программа /Авт сост к ю н., Завьялова Л. П. – г. Калининград: мфюа, 2016. – 39 с | Правила проведения вступительных испытаний информатике и инфокоммуникационным технологиям |
Поиск Главная страница   Заполнение бланков   Бланки   Договоры   Документы    |