Размещено на http://www.allbest.ru/

Лабораторная работа

«Математические модели в экономике»

Введение

Цель работы: Изучение основных принципов моделирования экономических процессов в экономике с консолидации данных в Excel.

Содержание отчета и его форма:

Ознакомьтесь с теоретическим обоснованием. Выполните все описанные в методике выполнения пункты заданий, письменно оформите отчет, который должен содержать:

– тему, цель лабораторной работы;

– виды ссылок в Excel;

– способы вызова Мастера формул;

– перечень изученных в данной работе возможностей Excel;

– описание созданных самостоятельно фильтров.

Методика и порядок выполнения работы

Задание 1

Создать рабочий лист с ЭТ, анализирующей заработную плату некоторого отдела в соответствии с приведенной таблицей 1.

1. Методика выполнения задания 1

1.1 Формирование заголовка и шапки таблицы

Щелкните левой кнопкой мыши на ячейке А1, ячейка станет активной. На клавиатуре наберите «Ведомость начисления заработной платы отдела №4 за октябрь 2011 г.» без кавычек и нажмите клавишу ввода. Выделите диапазон ячеек A1:I1, правой кнопкой вызовите контекстное меню выберите Формат ячеек> Выравнивание и выберите: по горизонтали - по центру и отображение - объединение ячеек (смотри таблицу 1). Эту же операцию можно сделать выбрав на панели инструментов вкладки Главная>Выравнивание щелкните кнопку Объединить и поместить в центре.

Таблица 1 - Ведомость начисления заработной платы отдела №4 за октябрь 2011 г.

Объедините ячейки А2:A3, используя любой известный способ, затем щёлкните по получившейся ячейке. Она станет активной, введите «№». Аналогично объедините ячейки В2:B3, введите «ФИО», ячейки С2:С3 и введите «Стаж», ячейки D2:E2 и введите «Начислено», ячейки F2:H2 и введите в них «Удержано». Объедините ячейки I2:I3, введите в них «К выдаче» и выполните операцию Перенос по словам: щелкните левой кнопкой мыши на ячейке С2:С3, затем правой кнопкой мыши вызовите контекстное меню. В котором выполните команды Формат ячеек> выравнивание>отображение>переносить по словам> ОК. В ячейку D3 введите «Оклад», в ячейку E3 «Премия», в ячейку F3 «Подоходный налог» и выполните операцию перенос по словам, в ячейку G3 «Пенсионный фонд» и выполните операцию перенос по словам, в ячейку H3 «Аванс». Аналогично в соответствии с таблицей 1 заполните ячейки A4:D13, где необходимо выполните перенос по словам. Объедините диапазон ячеек A14:H14 и введите в получившуюся ячейку «Средняя заработная плата». Объедините диапазон ячеек A15:H15 и введите в получившуюся ячейку «Всего к выдаче». Объедините диапазон ячеек A16:H16 и введите в получившуюся ячейку «Максимальная заработная плата».

1.2 Заполнение таблицы данными

Знаки вопроса (?), указанные в некоторых графах, означают, что данные значения необходимо рассчитать в процессе выполнения задания. В ячейки C4:D13 введите приведенные исходные данные из таблицы 1. Если какие либо данные не помещаются в ячейку необходимо выполнить следующие действия: установите курсор мыши на границу между ячейками, при этом курсор мыши получит форму крестика с двунаправленной горизонтальной стрелкой. Нажмите левую кнопку мыши и, удерживая ее, протащите вправо, расширяя столбик, до тех пор, пока в нем целиком не появится необходимые данные, после чего отпустите кнопку мыши. Так, например, поместите курсор между ячейками B и C,протащите курсор вправо, пока данные по фамилии не поместятся в ячейке.

В ячейках D4: I16 установите денежный формат. Для этого выделите необходимый диапазон, правой кнопкой вызовите контекстное меню, выберете Формат ячеек>Число>Денежный число десятичных знаков 2 и нажмите Ок.

1.3 Ввод расчетных формул и функций

Рассчитаем столбец E, т.е. Премия которая вычисляется как процент от оклада, в зависимости от стажа (20% от оклада, если проработано менее 10 лет, 25% если более 10). Введем формулу в ячейку E4 для этого: щелкните на ячейке и наберите =ЕСЛИ(C4<10;D4*0,2;D4*0,25), затем нажмите клавишу ввода. После этого в ячейке появится результат вычисления 2000,00р. Данная формула является логической. Она проверяет, выполняется ли условие и возвращает одно значение, если оно выполняется, другое значение, если не выполняется. Общий вид функции:

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь))

Щелкните на ячейке E4, проследите, что в строке ввода появляется формула, находящаяся в данной ячейке. В случае необходимости ее можно редактировать. Скопируем формулу из ячейки E4 вниз на ячейки E5:E13. Для этого: установите курсор мыши в нижний правый угол ячейки E4, при этом курсор изменит свою форму на крестик. Нажмите левую кнопку мыши и, удерживая ее, протащите вниз до ячейки E13 включительно. Формула скопируется и в столбике E появятся результаты расчетов. Снимите выделение. При копировании Excel автоматически корректирует адреса ячеек. Убедитесь в этом, для чего переведите курсор к ячейке E5 и посмотрите на строку ввода. Там сейчас находится формула: =ЕСЛИ(C5<10;D5*0,2;D5*0,25). В ячейке E6 - формула = ЕСЛИ(C6<10;D6*0,2;D6*0,25) и так далее.

Аналогично введите формулы в графу «Подоходный налог» в столбец F, учитывая, что подоходный налог составляет 13% от начисленной суммы, т.е. в ячейку F4 введите =(D4+E4)*0,13. Скопируйте ее вниз по ячейку F13. Аналогично введите формулы в графу «Пенсионный фонд» - столбец G , в ячейку G4 введите =(D4+E4)*0,01. Осуществите её ввод и скопируйте вниз по ячейку H13.

В ячейку H4, введите формулу, вычисляющую «Аванс» =((D4+E4)-(F4+G4))*0,3. Осуществите её ввод и скопируйте вниз по ячейку H13.

В ячейку I4, введите формулу, вычисляющую «К выдаче» =(D4+E4)-(F4+G4+H4). Осуществите её ввод и скопируйте вниз по ячейку H13.

Рассчитаем «Среднюю заработную плату». Для этого в ячейку I14 введите формулу =ОКРУГЛ(СРЗНАЧ(I4:I13);0). Теперь рассчитаем «Всего к выдаче». Для этого в ячейку I15 введите формулу =СУММ(I4:I13). Найдём значение «Максимальной заработной платы ». Для этого в ячейку I16 введите =МАКС(I4:I13).

Если вы не можете вспомнить, как правильно вводится та или иная формула, то формат ввода можно найти, перейдя на вкладку Формулы, Библиотека функций, на панели инструментов.

Рассмотрите панель инструментов раздела Библиотека функций. Команда автосуммирование позволит найти необходимые итоговые суммы. Для этого выделите тот блок ячеек, сумму которых надо найти, на вкладке Формулы в Библиотеке функций щелкните кнопку Автосумма. В ячейке итоговой суммы появится результат суммирования. Второй способ: щелкните в ячейке итоговой суммы и дважды щелкните кнопку Автосумма. Третий способ - через Мастер функций: щелкните в ячейке итоговой суммы, в строке функций выше Рабочего листа выберите f_x или на панели инструментов щелкните кнопку Вставить функцию f_x. Появится окно Мастера функций. В первом диалоговом окне Мастера выберите Категорию - математическая, Имя функции - СУММ, щелкните «ОК». Во втором диалоговом окне мастера введите аргументы - диапазон ячеек сумму которых надо найти, щелкните «ОК». Аналогично, применяя формулу вычисления среднеарифметического СРЗНАЧ из Категории Статистических функций, рассчитайте среднее значение и т. д.

1.4 Форматирование и редактирование данных

В Excel есть возможность выполнить отбор данных, т. е. их фильтрацию. Например, отберем только тех сотрудников, у которых оклад не превышает 11 тыс. руб. Для этого выделите столбец «Оклад» со всеми значениями на вкладке Главная, выберите раздел Сортировка и фильтр>Фильтр, в ячейке «Оклад» при этом появится метка контекстного меню, щелкните по ней. В появившемся меню выберите Числовые фильтры >Меньше или равно. В новом окне Пользовательского автофильтра введите 11000 и нажмите ОК. В результате в таблице останутся только интересующие нас данные. Для отмены фильтра выберите Сортировка и фильтр>Очистить или в контекстном окне выберите раздел Снять фильтр.

Задания:

1) Придумайте по аналогии три других фильтра по разным значениям таблицы.

2) Добавьте в конец таблицы свою фамилию, данные и выполните перерасчет значений.

заголовок таблица форматирование ведомость

2. Методика выполнения задания 2

2.1 Формирование листов книги

Для анализа заработной платы отдела №4 понадобится ещё две таблицы: за последующие два месяца со структурой, аналогичной имеющейся, которые создадим, используя копирование, и сводная таблица с новой структурой. Таким образом, рабочая книга будет состоять из 4-х листов.

Для этого откроем книгу «Заработная плата отдела №4 за 4 квартал» по команде Файл> Открыть. Скопируем таблицу с 1-го листа два раза. Для этого щелкните в нижней части окна на ярлыке Лист 1, затем щелкните правой кнопкой мыши, появится контекстное меню. В нем выполните команду Переместить> Скопировать, в диалоговом окне выберите установить перед листом 2 и установите флажок Создавать копию. Перед листом 2 появится копия первого листа Лист 1(2). Создайте еще одну копию. В результате получится три одинаковых таблицы. Переименуем Лист 1, для этого на ярлыке Лист 1 в нижней части окна щелкните правой кнопкой мыши. В контекстном меню выполните команду Переименовать. Затем наберите на клавиатуре «Октябрь». Аналогично переименуйте Лист 1(2) на «Ноябрь» и Лист 1(3) на «Декабрь». Отредактируем полученные таблицы. В первой таблице в первую строке останется «Ведомость начисления заработной платы отдела №4 за октябрь 2011 г.», во второй таблице в первую строку введите - «Ведомость начисления заработной платы отдела №4 за ноябрь 2011 г.», в третьей - за декабрь. Во второй таблице и третьей таблицах в столбец «Оклад» внесите новые данные по своему усмотрению.

Сохраните рабочую книгу, щелкнув на кнопке Сохранить. Теперь в рабочей книге «Заработная плата отдела №4 за 4 квартал» подшиты три таблицы о заработной плате отдела за три месяца.

2.2 Объединение и связывание таблиц для формирования итоговой ведомости

Microsoft Excel предоставляет возможность объединять и связывать рабочие листы таким образом, чтобы в результате ввода значений или выполнения вычислений в одной из таблиц, изменялось содержимое других таблиц.

Создадим сводную таблицу для анализа данных за три месяца, связывающую показатели оклада за эти месяцы (таблица 2). Поместите эту таблицу на четвертом листе, дайте ему название «Анализ заработной платы за три месяца». Введите заголовок и шапку таблицы. Скопируйте содержимое столбца В с фамилиями сотрудников. Для этого выделите эти данные В4:В15, щелкните кнопку Скопировать на вставке Главная в разделе Буфер обмена, вернитесь на лист «Анализ заработной платы за три месяца» и в ячейке А3 щелкните кнопку Вставить.

Таблица 2 - Сводные показатели за 3 месяца