расчет в excel. Минобранауки России Федеральное бюджетное государственное образовательное учреждение высшего образования Армавирский государственный педагогический университет экономические расчеты в ms excel методические указания
 Скачать 1.87 Mb.
|
1 2 1 Минобранауки России Федеральное бюджетное государственное образовательное учреждение высшего образования Армавирский государственный педагогический университет ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MS EXCEL Методические указания к лабораторным работам Направление подготовки 38.03.01 Экономика Направленность – Бухгалтерский учет, анализ и аудит Экономика предприятий и организаций Направление подготовки 44.03.05 Педагогическое образование Направленность (профиль) – Экономика и право 2018 2 Содержание ВВЕДЕНИЕ ....................................................................................................... 3 Лабораторная работа №1: ОРГАНИЗАЦИЯ РАСЧЕТОВ В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL Лабораторная работа №2: СОЗДАНИЕ ЭЛЕКТРОННОЙ КНИГИ. ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИИ В MS EXCEL. .......................................... 8 Лабораторная работа №3: СВЯЗАННЫЕ ТАБЛИЦЫ. РАСЧЕТ ПРОМЕЖУТОЧНЫХ ИТОГОВ В ТАБЛИЦАХ MS EXCEL ............................................................................. 14 Лабораторная работа №4: ПОДБОР ПАРАМЕТРА. ОРГАНИЗАЦИЯ ОБРАТНОГО РАСЧЕТА ....... 18 Лабораторная работа №5: ЗАДАЧИ ОПТИМИЗАЦИИ (ПОИСК РЕШЕНИЯ) ..................................... 24 Лабораторная работа №6: ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MS EXCEL ........................................... 32 Лабораторная работа №7: СВОДНЫЕ ТАБЛИЦЫ .................................................................................. 39 Лабораторная работа №8: КОМПЛЕКСНОЕ ИСПОЛЬЗОВАНИЕ ПРИЛОЖЕНИЙ MICROSOFT OFFICE ДЛЯ СОЗДАНИЯ ДОКУМЕНТОВ ......................... 47 Лабораторная работа 9: МЕТОДЫ ОБРАБОТКИ И АНАЛИЗА ИНФОРМАЦИИ В EXCEL .......... 52 Литература ....................................................................................................... 54 3 ВВЕДЕНИЕ Процесс изучения компьютерных технологий должен отвечать целому ряду требований и условий, при которых он становится эффективным. Эффект обучения прежде всего заключается в достижении конечной целите. в приобретении определенного объема знаний и практических навыков использования программных средств. Важнейшее значение при этом имеет организация процесса изучения и используемые источники знаний. Данные методические указания предназначены для проведения лабораторных занятий по дисциплине Информационные технологии, Экономические расчеты в MS EXCEL», Профессиональные компьютерные программы и рассматривают несколько тем данного раздела. Особенностью данного электронного учебного пособия является качественно новый подход к подбору задач при изучении стандартного программного обеспечения MS Excel. Поскольку предлагаемые студентам задачи, должны способствовать формированию именно экономического мышления, то это осуществляется и через фабулу профессионально- ориентированных задачи при их решении, и при анализе результата решения. Цель методических указаний – получить практические навыки решения экономических вопросов с помощью электронных таблиц, применяя математические методы и алгоритмы экономических расчетов, при организации которых происходит более глубокое осмысление теоретических основ экономики. Методические указания рекомендованы для студентов Направление подготовки 38.03.01 Экономика. Направленность (профиль) – Бухгалтерский учет, анализ и аудит Экономика предприятий и организаций, 44.03.05 Педагогическое образование Направленность – Экономика и право. 4 Лабораторная работа №1: Тема ОРГАНИЗАЦИЯ РАСЧЕТОВ В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL Цель занятия Изучение информационной технологии использования встроенных вычислительных функций Excel для финансового анализа. Задание 1. Создать таблицу финансовой сводки за неделю, произвести расчеты, построить диаграмму изменения финансового результата, произвести фильтрацию данных. Исходные данные представлены на рис. 1, результаты работы — на рис. 2, 3, 4. Порядок работы 1. Запустите редактор электронных таблиц Microsoft Excel и создайте в своей папке новую электронную книгу под своей фамилией. Рис. 1. Исходные данные для Задания 1 2. Введите заголовок таблицы Финансовая сводка за неделю тыс. р, начиная с ячейки А. 3. Для оформления шапки таблицы выделите ячейки на третьей строке A3:D3 и создайте стиль для оформления. Для этого выполните команду Формат/Стиль ив открывшемся окне Стиль наберите имя стиля Шапка таблиц и нажмите кнопку Изменить. В открывшемся окне на вкладке Выравнивание задайте Переносить по словами выберите горизонтальное и вертикальное выравнивание — по центру, на вкладке Число укажите формат — Текстовый, на вкладке Шрифт установите Arial Cyr, размер 12, начертание полужирный, на вкладке Границы – внешние.После этого нажмите кнопку Добавить 4. На третьей строке введите названия колонок таблицы — Дни недели, Доход, Расход, Финансовый результат, далее заполните таблицу исходными данными согласно рис. 1. Краткая справка. Для ввода дней недели наберите Понедельники произведите автокопирование до Воскресенья (левой кнопкой мыши за маркер автозаполнения в правом нижнем углу ячейки. При 5 заполнении таблицы пользуйтесь цифровыми клавишами в правой нижней части клавиатуры. 5. Произведите расчеты в графе Финансовый результат последующей формуле Финансовый результат = Доход – Расход. Для этого в ячейке D4 наберите формулу =В4-С4. Краткая справка. Введите расчетную формулу только для расчета по строке Понедельник, далее произведите автокопирование формулы (так как в графе Расход нет незаполненных данными ячеек, автокопирование можно производить двойным щелчком мыши помарке- ру автозаполнения в правом нижнем углу ячейки. 6. Для ячеек с результатом расчетов задайте формат Денежный с выделением отрицательных чисел красным цветом (Фор- мат/Ячейки/вкладка Число/формат— Денежный отрицательные числа — красные. Число десятичных знаков задайте равное 2). Обратите внимание, что цвет отрицательных значений финансового результата изменился на красный. 7. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка f x ). Функция Среднее значение (СРЗНАЧ) находится в разделе Статистические. Для расчета среднего значения дохода установите курсор в ячейке В, запустите мастер функций (Вставка/Функция/категория — Статистические/СРЗНАЧ). В качестве первого числа выделите группу ячеек сданными для расчета среднего значения — В4:В10. Аналогично рассчитайте Среднее значение расхода. В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу Финансовый результат. Для его выполнения удобно пользоваться кнопкой Автосуммирования (Σ) на панели инструментов или функцией СУММ. В качестве первого числа выделите группу ячеек сданными для расчета суммы — D4:D10. 9. Проведите форматирование заголовка таблицы. Для этого выделите интервал ячеек от А до D1, объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню (Формат/Ячейки/вкладка Выравнивание/отображение — Объединение ячеек. Задайте начертание шрифта — полужирное цвет — по вашему усмотрению. Конечный вид таблицы приведен на рис. 2. 6 Рис. 2. Таблица расчета финансового результата (Задание 1) 10. Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с использованием мастера диаграмм. Для этого выделите интервал ячеек сданными финансового результата и выберите команду Вставка/Диаграмма. На первом шаге работы с мастером диаграмм выберите тип диаграммы — линейчатая на втором шаге на вкладке Ряд в окошке Подписи оси Х укажите интервал ячеек с днями недели — А4:А10. Далее введите название диаграммы и подписи осей. Дальнейшие шаги построения диаграммы осуществляются автоматически по подсказкам мастера. Дальнейшее форматирование выполните самостоятельно в соответствии с видом диаграммы на рис. 3. Рис. 3. Конечный вид диаграммы Задания 1 11. Произведите фильтрацию значений дохода, превышающих р. Краткая справка. В режиме фильтра в таблице отображаются только те данные, которые удовлетворяют некоторому заданному критерию, при этом остальные строки таблицы скрыты. В этом режиме все 7 операции форматирования, копирования, автозаполнения, автосуммиро- вания и т.д. применяются только к видимым ячейкам листа. Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поляна которое будет наложено условие (в столбце Доходи вы увидите список всех неповторяющихся значений этого поля. Выберите команду для фильтрации — Условие. В открывшемся окне Пользовательский автофильтр задайте условие Больше 4000». Произойдет отбор данных по заданному условию. Проследите, как изменились вид таблицы (рис. 4) и построенная диаграмма. 12. Сохраните созданную электронную книгу в своей папке. Рис. 4. Вид таблицы и диаграммы после фильтрации данных САМОСТОЯТЕЛЬНАЯ РАБОТА Задание 2. Заполнить таблицу, произвести расчеты, выделить минимальную и максимальную суммы покупки (рис. 5). По результатам расчета построить круговую диаграмму суммы продаж с обозначением долевых значений вырученных сумм. Рис. 5. Исходные данные для Задания 2 Формулы для расчета 8 Сумма = Цена * Количество Всего = сумма значений колонки Сумма. Краткая справка. Для выделения максимального значений установите курсор в ячейке расчета, выберите встроенную функцию МАКС (МИН) из категории Статистические, в качестве первого числа выделите диапазон ячеек значений столбца Сумма (ячейки ЕЗ:Е10). Произвести фильтрацию данных по цене, не превышающей 500 р. Построить гистограмму отфильтрованных значений изменения выручки по видам продукции. Лабораторная работа №2 Тема СОЗДАНИЕ ЭЛЕКТРОННОЙ КНИГИ. ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИИ В MS EXCEL Цель занятия. Применение относительной и абсолютной адресаций для финансовых расчетов. Сортировка, условное форматирование и копирование созданных таблиц. Работа с листами электронной книги. Задание 2.1. Создать таблицы ведомости начисления заработной платы за два месяца на разных листах электронной книги, произвести расчеты, форматирование, сортировку и защиту данных. Исходные данные представлены на рис. 2.1, результаты работы - на рис. 2.6. Порядок работы 1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу. 2. Создайте таблицу расчета заработной платы по образцу (см. рис. 2.1). Введите исходные данные — Табельный номер, ФИО и Оклад, % Премии = 27 %, % Удержания = 13 %. Примечание. Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4). 9 Рис. 2.1. Исходные данные для Задания 2.1 Произведите расчеты во всех столбцах таблицы. При расчете Премии используется формула Премия = Оклад * % Премии, в ячейке D5 наберите формулу = $D$4 * С (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением. Рекомендации Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (те. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]). Формула для расчета Всего начислено Всего начислено = Оклад + Премия. При расчете Удержания используется формула Удержание = Всего начислено * % Удержания, для этого в ячейке F5 наберите формулу = $F$4 * E5. Формула для расчета столбца К выдаче К выдаче = Всего начислено - Удержания. 3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы поданным колонки К выдаче 10 (Формулы/Вставить функцию/категория — Статистические функции. 4. Переименуйте ярлычок Листа 1, присвоив ему имя Зарплата Октябрь. Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены на рис. 2.2. Краткая справка Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того, чтобы хранить большие последовательные наборы данных на одном листе. Рис. 2.2. Итоговый вид таблицы заработной платы за октябрь 5. Скопируйте содержимое листа Зарплата октябрь на новый лист. Можно воспользоваться командой Переместить Скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошке Создавать копию рис. 2.3). Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]). 11 Рис. 2.3. Копирование листа электронной книги 6. Присвойте скопированному листу название Зарплата ноябрь. Исправьте название месяца в названии таблицы. Измените значение Премии на 32 %. Убедитесь, что программа произвела пересчет формул. 7. Между колонками Премия и Всего начислено вставьте новую колонку Доплата (Вставить/Вставить столбцы на лист) и рассчитайте значение доплаты по формуле Доплата = Оклад * % Доплаты. Значение доплаты примите равным 5 %. 8. Измените формулу для расчета значений колонки Всего начислено Всего начислено = Оклад + Премия + Доплата. 9. Проведите условное форматирование значений колонки К выдаче. Установите формат вывода значений между 7000 и 10 000 — зеленым цветом шрифта меньше 7000 — красным больше или равно 10 000 — синим цветом шрифта (Условное форматирование/Правило выделения ячеек Больше(Меньше/Между)) рис. 2.4). 12 Рис. 2.4. Условное форматирование данных 10. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент с 5 по 18 строки таблицы — без итогов, выберите меню Данные/Сортировка, сортировать по - Столбец В) рис. 2.5). Рис. 2.5. Сортировка данных 11. Поставьте к ячейке D3 комментарии Премия пропорциональна Окладу (Рецензирование/СоздатьПримечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен на рис. 2.6. Рис. 2.6 Конечный вид зарплаты за ноябрь 12. Защитите лист Зарплата ноябрь от изменений Рецензирование Защитить лист. Задайте пароль на лист (рис. 2.7), сделайте подтверждение пароля (рис. 2.8). 13 Рис. 2.7. Защита листа электронной книги Рис. 2.8. Подтверждение пароля Убедитесь, что лист защищен и невозможно удаление данных. (Снимите защиту листа (Рецензирование/Снять защиту листа 13. Сохраните созданную электронную книгу под именем Зарплата в своей папке. Дополнительные задания Задание 2.2. Сделать примечания к двум-трем ячейкам. Задание 2.3. Выполнить условное форматирование оклада и премии за ноябрь месяц до 2000 р. — желтым цветом заливки от 2000 до 10 000 р. — зеленым цветом шрифта 14 свыше 10 000 р. — малиновым цветом заливки, белым цветом шрифта. Задание 2.4. Защитить лист зарплаты за октябрь от изменений. Проверьте защиту. Убедитесь в неизменяемости данных. Снимите защиту со всех листов электронной книги Зарплата. Задание 2.5. Построить круговую диаграмму начисленной суммы к выдаче всех сотрудников за ноябрь месяц. Лабораторная работа №3 Тема СВЯЗАННЫЕ ТАБЛИЦЫ. РАСЧЕТ ПРОМЕЖУТОЧНЫХ ИТОГОВ В ТАБЛИЦАХ MS EXCEL Цель занятия. Связывание листов электронной книги. Расчет промежуточных итогов. Структурирование таблицы. Задание 3.1. Рассчитать зарплату за декабрь и построить диаграмму. Создать итоговую таблицу ведомости квартального начисления заработной платы, провести расчет промежуточных итогов по подразделениям. Порядок работы 1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в Практической работе 2 файл Зарплата. 2. Скопируйте содержимое листа Зарплата ноябрь на новый лист электронной книги. Можно воспользоваться командой Переместить Скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошке Создавать копию 3. Присвойте скопированному листу название Зарплата декабрь. Исправьте название месяца в ведомости на декабрь. Рис. 3.1. Ведомость зарплаты за декабрь 15 4. Измените значение Премии на 46 %, Доплаты — на 8 %. Убедитесь, что программа произвела пересчет формул (рис. 3.1). 5. Поданным таблицы Зарплата декабрь постройте гистограмму доходов сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите форматирование диаграммы. Конечный вид гистограммы приведен на рис. 3.2. Рис. 3.2. Гистограмма зарплаты за декабрь 6. Перед расчетом итоговых данных за квартал проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь—декабрь. 7. Скопируйте содержимое листа Зарплата октябрь на новый лист Переместить Скопировать лист. Не забудьте для копирования поставить галочку в окошке Создавать копию 8. Присвойте скопированному листу название Итоги за квартал. Измените название таблицы на Ведомость начисления заработной платы за квартал. 9. Отредактируйте лист Итоги за квартал согласно образцу на рис. 3.3. Для этого удалите в основной таблице (см. рис. 3.1) колонки Оклада и Премии, а также строку 4 счисленными значениями % Премии и % Удержания и строку 19 Всего. Удалите также строки с расчетом максимального, минимального и среднего доходов под основной таблицей. Вставьте пустую третью строку. 16 Рис. 3.3. Таблица для расчета итоговой квартальной зарплаты 10. Вставьте новый столбец Подразделение Правая кнопка мы- ши/Вставить) между столбцами Фамилия и Всего начислено. Заполните столбец Подразделение данными по образцу (см. рис. 3.3). 11. Произведите расчет квартальных начислений, удержаний и суммы к выдаче как сумму начислений за каждый месяц (данные по месяцам располагаются на разных листах электронной книги, поэтому к адресу ячейки добавится адрес листа. Краткая справка . Чтобы вставить в формулу адрес или диапазон ячеек с другого листа, следует вовремя ввода формулы щелкнуть по закладке этого листа и выделить на нем нужные ячейки. Вставляемый адрес будет содержать название этого листа. В ячейке D5 для расчета квартальных начислений Всего начислено формула имеет вид = 'Зарплата декабрь + 'Зарплата ноябрь + + 'Зарплата октябрь'!Е5. Аналогично произведите квартальный расчет Удержания и К выдаче. Примечание. При выборе начислений за каждый месяц делайте ссылку на соответствующую ячейку из таблицы соответствующего листа электронной книги Зарплата. При этом произойдет связывание информации соответствующих ячеек листов электронной книги. 12. В силу однородности расчетных таблиц зарплаты по месяцам для расчета квартальных значений столбцов Удержание и К выдаче достаточно скопировать формулу из ячейки D5 в ячейки Е ирис. Для расчета квартального начисления заработной платы для всех сотрудников скопируйте формулы в столбцах D, Е и F. Ваша электронная таблица примет вид, как на рис. 3.4. 17 Рис. 3.4. Расчет квартального начисления зарплаты, связыванием листов электронной книги 13. Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений — по фамилиям. Таблица примет вид, как на рис. 3.5. Рис. 3.5. Вид таблицы начисления квартальной зарплаты после сортировки по подразделениям 14. Подведите промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/Итоги рис. 3.6). Задайте параметры подсчета промежуточных итогов при каждом изменении в — Подразделение операция — Сумма добавить итоги по Всего начислено, Удержания, К выдаче. Отметьте галочкой операции Заменить текущие итоги и Итоги подданными. Примерный вид итоговой таблицы представлен на рис. 3.7. 15. Изучите полученную структуру и формулы подведения промежуточных итогов, устанавливая курсор на разные ячейки таблицы. Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «- »). Краткая справка Под структурированием понимается многоуровневая группировка строки столбцов таблицы и создание элементов управления, с помощью которых легко можно скрывать и раскрывать эти группы. Рис. 3.6. Окно задания параметров расчета промежуточных итогов. Лабораторная работа № 4 Тема ПОДБОР ПАРАМЕТРА. ОРГАНИЗАЦИЯ ОБРАТНОГО РАСЧЕТА 19 Цель занятия. Изучение технологии подбора параметра при обрат- ныхрасчетах. Задание 4.1. Используя режим подбора параметра, определить, при каком значении % Премии общая сумма заработной платы за октябрь будет равна 250 000 р. (на основании файла Зарплата, созданного в Практических работах 1...2). Краткая справка. К исходным данным этой таблицы относятся значения Оклада и % Премии, одинакового для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов. Использование операции Подбор параметра в MS Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и поэтому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета. Порядок работы 1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в Практических работ 2... 3 файл Зарплата. 2. Скопируйте содержимое листа Зарплата октябрь на новый лист электронной книги (/Переместить/Скопировать лист Не забудьте для копирования поставить галочку в окошке Создавать копию. Присвойте скопированному листу имя Подбор параметра. 3. Осуществите подбор параметра командой Данные/Анализ что ес- ли»/Подбор параметра рис. 4.1). В диалоговом окне Подбор параметра на первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты ячейка G19), на второй строке наберите заданное значение 250 000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка D4), затем нажмите кнопку ОК. В окне Результат подбора параметра дайте подтверждение подобранному параметру нажатием кнопки ОК рис. 4.2). Произойдет обратный пересчет % Премии. Результаты подбора (рис. 4.3): если сумма к выдаче равна 250 000 р, то % Премии должен быть 203 %. 20 Рис. Задание параметров подбора параметра Рис. 4.2. Подтверждение результатов подбора параметра Рис. 4.3. Подбор значения % Премии для заданной общей суммы зарплаты, равной р. Задание 4.2. Используя режим подбора параметра, определить штатное расписания фирмы. Исходные данные приведены на рис. 1 2 |