МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ
В.Е. Медведев, С.В. Воробьев
ПРАКТИКУМ по информационным системам в экономике: расчеты в электронной таблице Excel
УКОРОЧЕННЫЙ ВАРИАНТ Полную версию можно взять в библиотеке
ЕЛЕЦ 2006
В.Е. Медведев, С.В. Воробьев Практикум по информационным системам в экономике: расчеты в электронной таблице Excel. – Елец, 2006.
Данное издание предназначено для того, чтобы помочь читателям научиться эффективно применять встроенные средства электронной таблицы Microsoft Excel в процессе решения экономических задач. Пособие разделено на темы, каждая из которых содержит минимальный объем теоретической информации, необходимой для решения поставленных задач, практические задания с подробным объяснением технологии их решения, а также задачи для самостоятельного выполнения. Материал излагается в доступной форме по принципу от простого к сложному. Практикум может использоваться на лабораторных занятиях в процессе изучения дисциплины «Информационные системы в экономике» и других подобных дисциплин. Пособие будет полезно студентам, аспирантам и преподавателям экономических специальностей, практикующим бухгалтерам и экономистам, которые желают самостоятельно повысить и закрепить свой уровень знаний и умений в области экономических информационных систем и технологий.
© В.Е. Медведев, 2006 © С.В. Воробьев, 2006
2
Содержание Введение ..............................................................................................................................4 Тема №1. Электронная таблица Microsoft Excel: основные понятия и интерфейс пользователя ........................................................................................................................4 Тема №2. Базовые операции с объектами электронной таблицы (ячейки, рабочие листы) ...................................................................................................................................11 Тема №3. Решение задачи «Бюджет фирмы «Витязь» с использованием основных функций ЭТ Microsoft Excel...............................................................................................22 Тема №4. Решение задачи «Отчет продаж за неделю» с использованием основных функций ЭТ Microsoft Excel...............................................................................................24 Тема №5. Решение задачи «Прайс-лист спортивного салона» с использованием основных функций ЭТ Microsoft Excel .............................................................................25 Тема №6. Решение задачи «Электронная ведомость для расчета стипендии студентов высшего учебного заведения» с использованием функций ЭТ Microsoft Excel......................................................................................................................................26 Тема №7. Решение задачи «Продажа товаров на оптовой базе» с использованием функций ЭТ Microsoft Excel...............................................................................................29 Тема №8. Использование функции Суммесли для подсчета значений с нарастающим итогом в ЭТ Microsoft Excel ......................................................................................31 Тема №9. Организация базы данных «Заказы» с использованием функций списка «сортировка» и «фильтрация» в Excel ..............................................................................32 Тема №10. Автоматическое подведение итогов в базе данных электронной таблицы Microsoft Excel...............................................................................................................40 Тема №11. Анализ базы данных «Заказы покупателей» с помощью Мастера сводных таблиц в Microsoft Excel .............................................................................................43 Тема №12. Консолидация данных в электронной таблице Microsoft Excel..................45 Тема №13. Графическое представление данных в электронной таблице Microsoft Excel......................................................................................................................................48 Тема №14. Программная надстройка «Поиск решения» в электронной таблице Microsoft Excel.....................................................................................................................52
3
Введение В настоящее время в нашей стране становится все труднее найти предприятие, организацию или даже небольшую фирму, в работе которой вообще не используются новые компьютерные технологии. Преуспевающие руководители, менеджеры, экономисты и аналитики не могут эффективно и качественно выполнять свою работу, вести бизнес, не имея в своем распоряжении компьютерного оборудования, оснащенного необходимым современным программным обеспечением. В условиях конкуренции добиться успеха на рынке помогают автоматизированные системы управления предприятием, системы взаимодействия с клиентами, автоматизированные системы ведения бухгалтерского учета, анализа и аудита, электронный офис и другие программы. Среди экономистов, аналитиков, инженеров и специалистов других профилей, электронная таблица Microsoft Excel, наряду с текстовым редактором Microsoft Word, считается одним из наиболее популярных приложений, входящих в интегрированный пакет прикладных программ Microsoft Office. Это обусловлено многими причинами. Во-первых, электронная таблица Microsoft Excel располагает мощными и удобными средствами для проведения различной сложности расчетов (математических, экономических, аналитических, инженерных и др.). Во-вторых, данная программа включает в себя большое количество разнообразных средств визуального представления данных, средств автоматизированной обработки многих вычислительных и логических операций. В-третьих, интеграция электронной таблицы Microsoft Excel и всех офисных программ пакета Microsoft Office, в частности, Microsoft Word, Microsoft PowerPoint, предоставляет пользователям дополнительные удобства в процессе работы с различными проектами. Настоящая книга представляет собой одну из частей практикума по учебному курсу «Информационные системы в экономики», который имеет место в государственном образовательном стандарте по многим экономическим специальностям. Целью практикума является обучение читателей некоторым основным методам и приемам эффективной обработки экономической информации с использованием мощных средств приложения Microsoft Excel. Однако, мы не стремились отразить в данной работе информацию обо всех возможностях и функциях указанного программного продукта. Достаточно было показать на конкретных жизненных примерах, с подробным описанием выполняемых действий, возможность применения Excel для достижения успеха в профессиональной деятельности специалиста экономического профиля. Материал практикума излагается в доступной для понимания неопытному пользователю форме, логически структурирован с учетом принципа «от простого к сложному». Книга разделена на темы, каждая из которых содержит минимально необходимое для усвоения количество теоретической информации, подробно (пошагово) описанный ход решения соответствующей задачи, а также задания для самостоятельного выполнения. Все рассмотренные в книге теоретические и практические примеры, так или иначе, относятся к сфере экономики и могут быть полезны не только студентам, аспирантам и преподавателям экономических специальностей, но и практикующим экономистам, бухгалтерам, аналитикам, аудиторам и иным категориям специалистов, желающим освоить новые информационные технологии. Авторский коллектив заранее извиняется за обнаруженные в процессе прочтения книги какиелибо неточности и упущения. Будем благодарны за предложения и замечания, которые можно направлять по электронной почте на адрес:
[email protected] . Желаем приятного изучения материала! Тема № 1 Электронная таблица Microsoft Excel: основные понятия и интерфейс пользователя Тема № 2 Базовые операции с объектами электронной таблицы (ячейки, рабочие листы) ЗАДАНИЕ. Создайте отчет о выручке Торгового дома «Атлантис» за 1 квартал 2005 года согласно рис. 10.
4
Рис. 10. Табличная форма отчета о выручке за 1 квартал ЗАДАНИЕ. Создайте отчет о продаже товаров бытовой техники в магазине «Чайка» за 25 мая 2005 года согласно рис. 15. Поля Сумма в рублях и Сумма в долларах должны заполняться с помощью формул, причем Сумма в долларах рассчитывается с учетом переводного курса. Учтите, что если значение переводного курса изменить на другое, то должен произойти пересчет столбца Сумма в долларах.
Рис. 15. Форма отчета о продаже бытовой техники ЗАДАНИЕ. Самостоятельно в книге Тема 2 на новом рабочем листе Зарплата создайте автоматизированную таблицу для расчета заработной платы сотрудников колбасного цеха за октябрь 2005 года в соответствии с рис. 16. Столбцы Премия, Итого начислено, Сумма вычета на детей, НДФЛ, Итого к выдаче рассчитываются по формулам, остальные заносятся данными с клавиатуры. Следует учесть, что премия составляет 17% от оклада, сумма вычета на 1 ребенка равна 600 рублей, сумма вычета на сотрудника равна 400 рублей.
5
Рис. 16. Пример таблицы для расчета зарплаты ЗАДАНИЕ. Вычислите, используя возможности y ( x) = Sin(Cos ( x − 1)) для x∈ [-10;10] с шагом 0,2.
мастера
функций,
значения
функции
ЗАДАНИЕ. Постройте график функции y ( x) = Sin(Cos ( x − 1)) , используя для построения подсчитанные в задании выше значения. РЕШЕНИЕ. Постройте объемную гистограмму, используя данные для построения из задачи Выручка за 1 квартал 2005 года Торгового дома «Атлантис». Требуется проследить динамику по каждому магазину в разрезе трех отчетных месяцев (рис. 21). ЗАДАНИЕ. Постройте гистограмму, используя данные для построения из задачи Отчет о продаже бытовой техники за 25 мая 2005 года в магазине «Чайка». Требуется проследить динамику Суммы в рублях в разрезе приобретаемых товаров (рис. 22). Постройте гистограмму, используя данные для построения из задачи Таблица расчета заработной платы сотрудников колбасного цеха за октябрь 2005 года. Требуется проследить динамику Оклада по сравнению с Итого к выдаче в разрезе сотрудников (рис. 23).
Рис. 21. Объемный вариант обычной гистограммы
6
Рис. 22. Динамика Суммы в рублях в разрезе товаров
Рис. 23. Динамика Оклада и Итого к выдаче в разрезе сотрудников ЗАДАНИЕ. Постройте график функции y =
Sin(1 − x) для всех х, принадлежащих отрезку от 0 до Cos ( x + 5 )
100, с шагом 0,5 (рис. 24).
7
Рис. 24. График функции y=f(x) Тема № 3 Решение задачи «Бюджет фирмы «Витязь» с использованием основных функций ЭТ Microsoft Excel ЗАДАНИЕ. Составьте форму электронной таблицы для автоматизированного ежемесячного учета доходов и расходов супермаркета «Витязь» за первое полугодие 2005 года, причем таблица со значениями за каждый месяц должна располагаться на отдельном листе, который будет называться именем отчетного месяца. На последнем листе данной рабочей книги подсчитать финансовые результаты отчетного полугодия. Тема № 4 Решение задачи «Отчет продаж за неделю» с использованием основных функций ЭТ Microsoft Excel ЗАДАНИЕ. Составьте средствами электронной таблицы Microsoft Excel автоматизированную форму, представляющую собой отчет работы книжного магазина «Лагуна» за полную рабочую неделю – с понедельника по пятницу включительно. Примерный вид формы документа представлен на рис. 28. Требуется ввести соответствующие формулы для расчета содержимого ячеек незаполненных столбцов. Для облегчения операций используйте функции копирования.
Рис. 28. Форма документа о продаже книг за понедельник Тема № 5 Решение задачи «Прайс-лист спортивного салона» с использованием основных функций ЭТ Microsoft Excel ЗАДАНИЕ. Составьте средствами электронной таблицы Microsoft Excel автоматизированную форму, представляющую собой прайс-лист спортивного магазина-салона «Атлет». Форма прайс-листа показана на рис. 29. Пустые поля заполняются с помощью формул, используя функцию копирования.
8
Рис. 29. Табличная форма Прайс-листа Тема № 6 Решение задачи «Электронная ведомость для расчета стипендии студентов высшего учебного заведения» с использованием функций ЭТ Microsoft Excel ЗАДАНИЕ. Учет результатов экзаменационной сессии студентов высшего учебного заведения ведется с использованием электронных ведомостей. Типовые ведомости создаются для каждой группы в виде отдельной рабочей книги и содержат следующие рабочие листы: 1) несколько листов (в зависимости от числа экзаменов) в которых находится список студентов по каждому отдельному экзамену с полями Номер по порядку, Фамилия Имя Отчество, Номер зачетной книжки, Оценка, Подпись экзаменатора, Дата (рис. 30); 2) один общий лист Ведомость с полями Номер по порядку, Фамилия Имя Отчество, Количество сданных экзаменов, Средний балл, Стипендия, Премия, Итого к выдаче (рис. 31). На каждом листе с названием экзамена подсчитайте количество пятерок, четверок, троек и двоек по данному экзамену. Для определенности допустим, что рассматриваемая Вами группа сдавала четыре экзамена. В листе Ведомость средний балл вычисляется по результатам сдачи экзаменов по каждому студенту. Учтите, что средний балл будет рассчитываться только в том случае, если студент сдал все экзамены, в противном случае средний балл должен быть равен нулю. При подсчете Стипендии следует учесть следующие условия: • если средний балл не менее 4,6, выплачивается 30% надбавка к минимальной стипендии; • если средний балл от 3,6 (включительно) до 4,6 – выплачивается минимальная стипендия; • если средний балл меньше 3,6 – стипендия не выплачивается. Значение с минимальным размером стипендии помещается в отдельную ячейку. При изменении минимального размера перерасчет значений в поле Стипендия должен происходить автоматически. Премия в размере 20% от минимального размера стипендии дается студентам только в том случае, если сданы все экзамены на пятерки. Подсчитайте сумму общего стипендиального фонда для группы.
9
Рис. 30. Форма экзаменационной ведомости по математике Тема № 7 Решение задачи «Продажа товаров на оптовой базе» с использованием функций ЭТ Microsoft Excel ЗАДАНИЕ. Разработайте автоматизированную учетную документацию продажи товара на оптовой базе «Финист» с учетом скидок для постоянных клиентов. Исходные данные расположите на двух листах: лист Товары (рис. 34) и лист Клиенты (рис. 35). Расчетным будет являться лист Заказы (рис. 36). Тема № 8 Использование функции СУММЕСЛИ для подсчета значений с нарастающим итогом в ЭТ Microsoft Excel ЗАДАНИЕ. Используя данные Справочника клиентов (Код клиента, Клиент) и Ценника (Код товара, Наименование товара, Цена за единицу), создайте автоматизированную форму (Январь) для ежемесячного учета продаж мелкооптового магазина «Стройматериалы» следующей структуры: Номер документа, Дата заказа, Код клиента, Клиент, Код товара, Наименование товара, Цена за единицу, Количество, Сумма покупки, Сумма покупок клиента с начала месяца. Следует учесть, что не подчеркнутые столбцы вводятся с клавиатуры, а подчеркнутые вычисляются по формулам. ЗАДАНИЕ. Самостоятельно подсчитайте, на какую сумму с начала месяца покупается каждый товар (добавьте еще один столбец и назовите его Сумма продаж товара с начала месяца). ЗАДАНИЕ. Самостоятельно, используя данные Справочника клиентов и Ценника, создайте автоматизированную форму для ежемесячного учета продаж торгово-закупочной базы «Парадокс» следующей структуры: Номер документа; Дата заказа; Код клиента; Клиент; № товара; Наименование товара; Цена за единицу; Количество; Сумма покупки; Сумма покупок клиента с начала месяца; Скидка
10
(!!!); Сумма покупок клиента с учетом скидки. Обратите внимание, что в течение месяца на фирме действует система скидок: 3%, если сумма покупок клиента с начала месяца больше 10000 рублей; 5%, если сумма покупок клиента с начала месяца больше 50000 рублей. Тема № 9 Организация базы данных «Заказы» с использованием функций списка «сортировка» и «фильтрация» в Excel Электронную таблицу Microsoft Excel можно использовать в качестве базы данных, в которой строки будут соответствовать записям в базе данных, а столбцы – полям базы данных. С такой базой данных в электронной таблице можно производить различные преобразования, которые организованы и в системе управления базой данных, например, сортировка, фильтрация и др. Рассмотрим на примере указанные возможности электронной таблицы Microsoft Excel. ЗАДАНИЕ. Составьте список покупателей, список предлагаемых товаров и организуйте учет заказов торгового центра «Виола» по продаже продуктов питания. Предусмотрите возможность печати данных для каждого отдельного заказа. Проанализируйте данные полученного списка. ЗАДАНИЕ. Составьте табличную базу данных «Успеваемость студентов за неделю» по форме, изображенной на рис. 50. Задайте структуру и получите итоги по трем уровням: 1-го уровня – средний балл по каждой учебной группе; 2-го уровня – средний балл по каждому предмету по всем учебным группам; 3-го уровня – средний балл по каждому виду занятий определенных предметов по всем учебным группам.
Рис. 50. Форма листа Успеваемость студентов
11
ЗАДАНИЕ. Самостоятельно в таблице «Успеваемость студентов» создайте новые промежуточные итоги вида: на первом уровне – по учебному предмету, на втором уровне – по виду занятия, на третьем уровне – по номеру учебной группы. ЗАДАНИЕ. Самостоятельно создайте промежуточные итоги для листа Заказы покупателей из задания Темы 9, куда включите итоги следующего вида: на первом уровне – по коду продукта вывести итоги в столбцах Сумма заказа и Уплачено, на втором уровне – по коду продукта вывести количество одинаковых названий продукта. Обратите внимание, что при сортировке таблицы по любому полю столбец Номер заказа теряет свою автоматизацию, поэтому его целесообразно скрыть.
Рис. 52. Фрагмент окна таблицы после подведения итогов Тема № 11 Анализ базы данных «Заказы покупателей» с помощью Мастера сводных таблиц в Microsoft Excel В электронной таблице Microsoft Excel предусмотрена возможность создания сводной таблицы из исходной базы данных. Это упрощает работу пользователей с большими информационными потоками, придавая им вид упорядоченных данных. Команда Сводная таблица для создания сводной таблицы находится в меню Данные. Рассмотрим на конкретном примере технологию построения сводной таблицы. ЗАДАНИЕ. Проведите анализ данных списка Заказы покупателей из Темы 9 с помощью сводной таблицы. Проследите динамику сбыта отдельных товаров по месяцам. ЗАДАНИЕ. Самостоятельно на новом рабочем листе создайте сводную таблицу, в которой бы отражались детальные сведения о продаже конкретного продукта за определенный месяц. Самостоятельно на новых рабочих листах создайте сводные таблицы, в которых отражаются детальные сведения о:
12
а) покупках конкретной фирмой продуктов за определенный месяц; б) наибольшей уплаченной сумме по каждому отдельному продукту за каждый конкретный месяц.
Рис. 57. Сводная таблица по Заказам покупателя Тема № 12 Консолидация данных в электронной таблице Microsoft Excel При обработке большого объема табличной информации часто возникает потребность в объединении (консолидации) данных из нескольких однотипных таблиц в одну итоговую таблицу. Такая возможность имеется в электронной таблице Microsoft Excel. Запускается процедура консолидации через меню Данные команда Консолидация. Рассмотрим на практическом примере технологию реализации данной функции. ЗАДАНИЕ. Проанализируйте данные списка Заказы покупателей с помощью процедуры консолидации данных, которые поставляются из пяти филиалов нашей фирмы. Проследите, какой товар больше всего способствовал росту объема продаж во всех вместе взятых филиалах. ЗАДАНИЕ. Самостоятельно определите доли Объема продаж. Для этого добавьте столбец Доля продаж в конце таблицы Консолидация. Заполните этот столбец соответствующей формулой. ЗАДАНИЕ. Проанализируйте объем продаж по филиалам из предыдущего задания с помощью сводной таблицы, источники данных для которой находятся в нескольких диапазонах консолидации. Проследите динамику сбыта отдельных продуктов по месяцам.
13
Тема № 13 Графическое представление данных в электронной таблице Microsoft Excel Электронная таблица Microsoft Excel представляет собой не только мощное приложение для обработки табличной информации, но и содержит в себе дополнительные возможности по графическому представлению этих табличных данных. Для этого в электронную таблицу встроен Мастер диаграмм, работу которого мы рассмотрим на примерах. ЗАДАНИЕ. Проведите анализ данных задачи из Темы 12 по объему продаж различных продуктов в разрезе месяцев с помощью диаграммы. ЗАДАНИЕ. Самостоятельно отобразите графически с помощью создания диаграммы доли отдельных продуктов в общем объеме продаж, используя данные листа Заказы покупателей. Подготовьте числовой материал для составления диаграммы, создав соответствующую сводную таблицу (рис. 67). А затем, на основе этой сводной таблицы постройте диаграмму, показанную на рис. 68.
Рис. 67. Сводная таблица с долей в итоге ПРИМЕЧАНИЕ ♦ Для изменения операции над итоговыми значениями достаточно осуществить двойной щелчок мыши на кнопке сводной таблицы Сумма по полю уплачено. В открывшемся окне Вычисление поля сводной таблицы нажмите кнопку Дополнительно. Затем в поле Дополнительные вычисления установите параметр (для условия нашей задачи) Доля от общей суммы. Нажмите ОК. ЗАДАНИЕ. Самостоятельно постройте график для сравнения учета доходов и расходов фирмы «Быт» от деятельности, связанной с выполнением услуг по доставке товара на автотранспорте, по месяцам (за год). Данные о доходах и расходах от выполненных услуг представлены на рис. 69. Примерное оформление графика показано на рис. 70.
14
Рис. 68. Объемный вариант круговой диаграммы
Рис. 69. Табличные данные расходов и доходов фирмы Быт
Рис. 70. График доходов и расходов фирмы Быт
15
Тема № 14 Программная надстройка «Поиск решения» в электронной таблице Microsoft Excel Программная надстройка Поиск решения – это встроенная в Microsoft Excel 2003 подпрограмма, которая облегчает пользователю нахождение оптимального решения поставленной задачи при условии соблюдения некоторых ограничений. Прежде чем программа начнет выполнять поиск решения задачи, необходимо подготовить в понятном компьютеру виде начальные данные, т.е. предварительно поставить и формализовать задачу, установив исходные числовые значения. Как правило, в электронной таблице основой для формализации задачи служит созданная пользователем таблица, в которой указан весь необходимый числовой материал. При этом таблица должна содержать формулы, отражающие зависимости между определенными данными таблицы. После создания таблицы с достаточными параметрами можно активировать программу Поиск решения. Полученные оптимальные решения можно автоматически занести в таблицу или представить в виде отдельного отчета. ЗАДАНИЕ. Проведя маркетинговые исследования, Вы пришли к выводу о наличии высокого спроса на муку. Кроме того, имеется возможность приобретать зерно по относительно невысокой цене. Поэтому Вы решили выбрать в качестве инвестиционного проекта мукомольную промышленность. Вам предстоит рассмотреть вопрос о том, каким образом можно повлиять на окупаемость затрат путем моделирования технологического процесса. Другими словами, требуется решить задачу оптимизации, а именно, задачу оптимальной загрузки оборудования (имеется три варианта загрузки оборудования) при наличии ограничений на доступные ресурсы. ЗАДАНИЕ. Допустим, что Вы знаете целевое значение, которое хотите получить в результате определенных манипуляций с вариантами загрузки оборудования, а также условие, сопровождающее достижение этого целевого значения, выражающее взаимосвязь между итоговой и изменяемой ячейкой в виде формулы. В результате остается только узнать, какие следует произвести затраты, чтобы при соблюдении некоторых условий своевременно достичь целевого значения. После выполнения программы Поиск решения с заданными исходными условиями сума покрытия составила 584000 рублей. Вам необходимо узнать, сколько тонн зерна необходимо переработать дополнительно, чтобы при указанном варианте загрузки общая сумма покрытия составила ровно 600000 рублей при условии дополнительной загрузки оборудования.
16
