Решение задач бизнес-анализа средствами аппарата сводных таблиц MS Excel

⇐ Назад

Сводная таблица — удобный и мощный инструмент обработки больших списков (баз данных), позволяющий автоматически подводить итоги, а также выполнять сортировку и фильтрацию списков.

Рассмотрим построение сводной таблицы на примере формирования Ведомости зарплаты за полугодие на основе Ведомостей зарплаты сотрудников за 1-й и 2-й кварталы. Прежде всего необходимо разместить исходные ведомости на одном рабочем листе EXCEL, как этo представлено в таблице 18.^{^[5]}

Таблица 18 – Подготовка списка для создания сводной ведомости за полугодие

Ф.И.О.	Всего начислено, руб.	Всего удержано, руб.	Сумма к выдач, руб.е	Отдел	Дата
Иванов И.А.	4 500	487,80	4 012,20		1 кв 1998
Малаев В.П.	3 900	441,12	3 458,88		1 кв 1998
Климов Ф.Ф. Ф.	4 000	484,60	3 515,40		1 кв 1998
Иванов И.А.	7 700	899,96	6 800,04		2 кв 1998
Малаев В.П.	6 100	724,48	5 375,52		2 кв 1998
Климов Ф.Ф.	4 800	587,64	4 212,36		2 кв 1998

В меню Данные следует выбрать команду Сводная таблица, по которой на экран выводится окно Мастер сводных таблиц для задания области обрабатываемых данных, в нашем примере - это весь список.

На следующем шаге формируется макет итогового документа из полей исходного списка - макет сводной таблицы, диалоговое окно которого применительно к описанному выше примера приведено на рисунке 35.

Рисунок 35 – Образец подготовки списка для создания сводной ведомости
за полугодие в MS Excel

Макет сводной таблицы содержит 4 области (рисунок 7):

• Страница - обеспечивает фильтрацию данных сводной таблицы по значениям выбранных полей, может содержать О-N полей списка;

• Строка, Столбец - обеспечивают группирование строк и столбцов списка для вычисления итогов (О-N полей списка);

• Данные - обязательная область макета; содержит произвольное число полей, не включенных в другие области. Одно и то же поле списка может быть многократно размещено в области данных, если для него нужны разные виды итогов (сумма, среднее значение и т.д.).

Порядок следования полей в областях Страница, Строка, Столбец определяет иерархию группирования данных и формируемых итогов.

На последнем шаге определяется место размещения результатов построения сводной таблицы. Конечный результат формирования Ведомости зарплаты за полугодие приведен в таблице 19.

Таблица 19 – Сводная ведомость зарплаты за полугодие

Ф.И.О.	Данные	Дата
1 кв 1998	2 кв 1998	Общий итог, руб.
Иванов И.А.	Сумма по полю Всего начислено	4 500,00	7 700,00	12 200,00
Сумма по полю Всего удержано	487,80	899,96	1 387,76
Сумма по полю Сумма к выдаче	4 012,20	6 800,04	10 812,24
Климов Ф.Ф.	Сумма по полю Всего начислено	4 000,00	4 800,00	8 800,00
Сумма по полю Всего удержано	484,60	587,64	1 072,24
Сумма по полю Сумма к выдаче	3 515,40	4 212,36	7 727,76
Малаев В.П.	Сумма по полю Всего начислено	3 900,00	6 100,00	10 000,00
Сумма по полю Всего удержано	441,12	724,48	1 165,60
Сумма по полю Сумма к выдаче	3 458,88	5 375,52	8 834,40
Итог Сумма по полю Всего начислено	12 400,00	18 600,00
Итог Сумма по полю Всего удержано	1413,52	2212,08	3625,6
Итог Сумма по полю Сумма к выдаче	10986,48	16387,92	27374,4

При подведении итогов можно объединять данные в группы в зависимости от типа данных разными способами:

• группировать выделенные элементы по выбранным категориям;

• автоматически группировать числовые элементы;

• автоматически группировать даты и время в более крупные единицы времени, например, дни, месяцы, кварталы, годы.

Можно создавать подгруппы в группах, например, сгруппировать даты по месяцам, а затем месяцы в кварталы.

Для группировки данных следует выделить элементы, которые группируются, и воспользоваться кнопкой Группировка на панели инструментов Сводные таблицы. Например, для объединения сотрудников по отделам и получения дополнительных итогов для предыдущего примера (таблица 18) следует в построенной сводной таблице выделить требуемые фамилии, а затем выполнить группировку описанным способом.

С помощью мастера сводных таблиц можно также консолидировать данные из таблиц, расположенных на разных рабочих листах EXCEL. Непременным условием такой консолидации является единая структура таблиц. При этом каждая таблица должна содержать данные одного временного (или другого типа) диапазона. Например, ведомости зарплаты за каждый квартал 1998 года сформированы на разных рабочих листах EXCEL с именами 1кв-98, 2 кв-98, 3 кв-98, 4 кв-98. Для построения сводной ведомости за весь 1998 год не обязательно переносить их на один рабочий лист EXCEL, а можно воспользоваться средством консолидации мастера сводных таблиц, для чего выполнить следующие действия:

- в команде Сводная таблица установить флажок в нескольких диапазонах консолидации;

- в режиме переключателя Создать одно поле страницы указать диапазон каждой консолидируемой таблицы, включая в него заголовки, кроме итоговых строк и столбцов;

- сформировать макет итогового документа,[6] выбирая для расчетов требуемые функции.

Таблица 20 - Образец сводной ведомости зарплаты за 1998 год в MS Excel

(Все)
Сумма по полю Значение	Столбец
Ф.И.0.	Начислено	Удержано	К выдаче
Иванов И.А		2763,93	21546,07
КлимовФ.Ф.		2505,43	18134,57
Малаев В.П.		2587,51	19402,49
Общий итог		3625,6	27374,4

Результат формирования Ведомости зарплаты за 1998 год методом консолидации диапазонов, расположенных на листах 1кв-98, 2 кв-98, 3 кв-98, 4 кв-98, приведен на рисунке 38.

По списку (Все) можно получить сведения по каждому кварталу.

Варианты заданий для самостоятельной работы

Задача 1. «Переоценка основных средств производства»

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

Наименование объекта	Балансовая стоимость	Износ	Остаточная стоимость	Восстановительная полная стоимость	Восстановительная остаточная стоимость
Заводо- управление	11576,2	568,.0
Диспетчерская	176,0	45,4
Цex Nl	710,2	120,3
Цex N2	804,6	240.0
Цex N3	933,0	150,2
Цex N4	474.,4	174,5
Склад N1	570,5	221,2
Склад N2	430,4	92,2
Склад N3	564,9	118,0
Склад N4	320,5	87,5
Итого

1. Используя значения балансовой стоимости (БС) и износа объекта (ИО), рассчитать:

- остаточную стоимость объекта (ОС) по формуле:
ОС = БС — ИО;

- восстановительную полную стоимость объекта (ВП) и восстановительную остаточную стоимость объекта (ВО) по следующим формулам: ВП == БС - К, ВО = ОС - К, где К = 3.0, если БС > 500 млн. руб., иначе К = 2.0, если БС < 500 млн. руб.

2. Добавить в ведомость новую графу Вид объекта и присвоить всем объектам Цех N1 — Цех N4 вид основной, а всем остальным объектам - вид вспомогательный.

3. Выполнить сортировку ведомости по возрастанию видов объектов, а внутри каждого вида — по возрастанию наименования объектов.

4. Выполнить фильтрацию ведомости, оставив в ней только вспомогательные объекты. После анализа результатов фильтрации вернуть таблицу в исходное состояние, когда она содержала все виды объектов.

5. Рассчитать общую (суммарную) балансовую стоимость, износ и общую (суммарную) остаточную стоимость всех основных и вспомогательных видов объектов с помощью команды Итоги. После анализа результатов расчета вернуть таблицу в исходное состояние.

6. С помощью команды Расширенный фильтр сформировать накопительную ведомость по тем объектам, балансовая стоимость которых > 500 млн. руб. Включить в новую ведомость следующие графы: Наименование объекта; Балансовая стоимость; Остаточная стоимость; Восстановительная полная стоимость.

7. Показать на графике (гистограмме) структуру балансовой, остаточной и восстановительной (полной) стоимостей для всех объектов основного вида. Вывести на графике значения максимальной балансовой, остаточной и восстановительной стоимостей, а также легенду и название графика Переоценка основных средств производства.

8. Построить на отдельном рабочем листе MS XCEL смешанную диаграмму, в которой показать значения балансовой и остаточной стоимостей для всех вспомогательных объектов в виде гистограмм, а значения восстановительной (полной) стоимости всех вспомогательных объектов представить в виде линейного графика на той же диаграмме. Вывести легенду и название графика Оценка основных средств производства (вспомогательные объекты).

9. На основании исходной накопительной ведомости Переоценка основных средств производства с помощью аппарата Функции базы данных рассчитать и сформировать следующий документ:

Тип объекта	Основной	Вспомогательный
Средняя балансовая стоимость
Максимальный износ
Минимальный износ
Максимальная остаточная стоимость
Средняя остаточная стоимость
Количество объектов

10. Подготовить результаты расчетов и диаграммы к выводу на печать.

Задача 2. «Расчет структуры кредитных вложений банка»

Для решения задачи используется следующая входная информация: объем ссуд, предоставленных государственным предприятиям; объем ссуд, предоставленных кооперативам; объем ссуд, предоставленных совместным предприятиям; объем ссуд, предоставленных предпринимателям; объем ссуд, предоставленных физическим лицам; объем ссуд, предоставленных инофирмам; объем ссуд, предоставленных сельскохозяйственным предприятиям; объем ссуд, предоставленных предприятиям, организованным в форме АО и ТОО; объем ссуд, предоставленных ИЧП; объем межбанковских кредитов.

В результате решения задачи необходимо сформировать следующий выходной документ:

Структура кредитных вложений коммерческого банка

Вложения коммерческого банка	Сумма, млн. руб.	Уд. вес, %
Объем ссуд государственным предприятиям		U(1)
Объем ссуд кооперативам		U(2)
Объем ссуд совместным предприятиям		U(3)
Объем ссуд предпринимателям		U(4)
Объем ссуд физическим лицам		U(5)
Объем ссуд инофирмам		U(6)
Объем ссуд с/х предприятиям		U(7)
Объем ссуд предприятиям в форме АО и ТОО		U(8)
Объем ссуд ИЧП		U(9)
Объем межбанковских кредитов		U(10)
ИТОГО	SS	100%

Формулы для расчета выходных показателей имеют следующий вид:

SS = SUM (S(I)), где S(I) — сумма i-й ссуды (млн. руб.);

U(I) - S(I) / SS • 100, где U(I) — удельный вес i-й ссуды,

I = [1,N], N — количество видов предоставляемых ссуд.

1. Рассчитать и показать на графике структуру кредитных вложений коммерческого банка.

2. Выполнить сортировку документа по возрастанию объемов вложений коммерческого банка.

3. Построить на отдельном листе MS EXCEL круговую диаграмму, отражающую структуру сумм каждого вида ссуды в виде соответствующего сектора, вывести значения объемов вложений по каждому виду ссуды, а также легенду и название графика Структура кредитных вложений банка.

4. Построить на новом листе MS EXCEL смешанную диаграмму, в которой суммы объемов каждого вида ссуды коммерческого банка были бы представлены в виде гистограмм, а их удельные веса в виде линейного графика на той же диаграмме. Вывести легенду и название графика Анализ кредитных вложений коммерческого банка.

5. Сформировать новый выходной документ, содержащий только те кредитные вложения коммерческого банка, объем ссуд которых больше среднего значения этого показателя но всей таблице. Вид выходного документа:

Вложения коммерческого банка	Сумма, млн. руб.

6. Подготовить результаты расчетов и диаграммы к выводу на печать.

Задача 3. «Расчет структуры привлеченных средств банка»

Для решения задачи используется следующая входная информация, отраженная в таблице "Структура привлеченных средств коммерческого банка". В результате решения задачи необходимо сформировать следующий выходной документ:

Структура привлеченных средств коммерческого банка

Привлеченные средства коммерческого банка	Сумма, млн. руб.	Уд. вес (%)
Депозиты государственных предприятий		U(1)
Депозиты с/х предприятий		U(2)
Депозиты СП		U(3)
вклады населения		U(4)
Депозиты внебюджетных фондов		U(5)
Депозиты АО и ТОО		U(6)
Депозиты ИЧП		U(7)
Остатки на расчетных и текущих счетах клиентов		U(8)
Депозиты юридических лиц в валюте, руб.		U(9)
ИТОГО	SS	100 %

Формулы для расчета выходных показателей имеют следующий вид:

SS = SUM(S(I)), где S(I) — сумма i-гo привлеченного средства;

U(I) = (S(I) / SS)•100, где U(I) - удельный вес i-гo привлеченного средства,

I = [I, N], N — количество видов привлеченных средств банка.

1. Рассчитать и показать на графике структуру привлеченных средств коммерческого банка (в млн. руб.).

2. Выполнить сортировку документа по возрастанию наименований привлеченных средств коммерческого банка.

3. Выполнить фильтрацию сформированного документа, оставив в нем только депозиты коммерческого банка. Вернуть документ в исходное состояние, когда он содержал все привлеченные средства банка.

4. Рассчитать общую сумму всех депозитов и сумму всех остальных привлеченных средств коммерческого банка с помощью команды Итоги. После сравнения полученных результатов вернуть документ в исходное состояние.

5. Построить на отдельном рабочем листе MS EXCEL круговую диаграмму, отражающую структуру сумм привлеченных средств коммерческого банка в виде соответствующих секторов. Показать на графике процентное соотношение привлеченных средств, выделить самый большой сектор, вывести легенду и название графика Структура привлеченных средств коммерческого банка.

6. Построить на новом рабочем листе MS XCEL смешанную диаграмму, в которой представить в виде гистограмм суммы привлеченных средств банка, а их удельные веса показать в виде линейного графика на той же диаграмме. Вывести легенду и название графика Анализ привлеченных средств коммерческого банка.

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

8. С помощью средства Расширенный фильтр сформировать новый документ, в который поместить только те привлеченные средства коммерческого банка, сумма каждого из которых больше среднего значения этого показателя по всему исходному документу. Поместить в новый документ все графы исходного документа.

9. На основании исходного документа Структура привлеченных средств коммерческого банка рассчитать и сформировать следующий документ только для депозитных средств банка:

Анализ депозитных средств коммерческого банка

Расчетная величина	Значение
Средняя сумма всех депозитных средств
Количество депозитных средств
Максимальная сумма депозитных средств
Минимальная сумма депозитных средств

10. Подготовить результаты расчетов и диаграммы к выводу на печать.

Задача 4. «Расчет структуры депозитной базы банка»

Для решения задачи используется следующая входная информация:

- остатки на расчетных и текущих счетах клиентов банка - R(1,j) по каждому j-му кварталу;

- депозиты предприятий, организаций и кооперативов - R(2,j) по каждому j-му кварталу;

- межбанковские кредиты - R(3,j) по каждому j-му кварталу;

- кредиты граждан - R(4,j) по каждому j-му кварталу.

В результате решения задачи необходимо сформировать следующий выходной документ:

Структура депозитной базы привлеченных ресурсов коммерческого банка

Наименование показателя	В том числе по каждому j-му кварталу	Всего за 1997 год
1 квартал	2 квартал	3 квартал	4 квартал
млн. руб.	% к итогу	млн. руб.	% к итогу	млн. руб.	% к итогу	млн. руб.	% к итогу	млн. руб.	% к итогу
Остатки на расчетных и текущих счетах
Депозиты предприятий и кооперативов
Межбанковские кредиты
Вклады граждан
ИТОГО:

Рассчитать структуру депозитной базы привлеченных ресурсов коммерческого банка за четыре квартала 1997 года.

2. Построить на отдельном рабочем листе MS EXCEL круговую диаграмму, отражающую структуру сумм объемов вкладов граждан
(в млн. руб.) по кварталам в виде соответствующих секторов. Показать на графике значения вкладов граждан, вывести легенду и название графика Структура вкладов граждан коммерческого банка по кварталам.

3. Построить на новом рабочем листе MS EXCEL смешанную диаграмму, в которой представить в виде гистограмм значения депозитов предприятий и кооперативов, а также межбанковские кредиты (млн. руб.) по кварталам, а в виде линейного графика вывести на той же диаграмме значения вкладов граждан (млн. руб.) по кварталам. Вывести легенду и название графика Структура привлеченных ресурсов коммерческого банка по кварталам.

4. Построить на отдельном рабочем листе MS EXCEL диаграмму, отражающую в виде гистограмм значения объемов остатков на расчетных и текущих счетах клиентов банка, депозитов предприятий, организаций и кооперативов, а также межбанковские кредиты (млн. руб.) по кварталам. Вывести на графике максимальные значения привлеченных ресурсов банка, легенду и название графика Структура депозитной базы привлеченных ресурсов коммерческого банка по кварталам.

5. С помощью средства Расширенный фильтр сформировать новый документ, в который поместить информацию только о депозитах первого полугодия работы коммерческого банка. Рассчитать итоговые показатели в стоимостном и процентном соотношении за полугодие.

6. Построить на отдельном рабочем листе MS EXCEL диаграмму, отражающую в виде гистограмм значения каждого привлеченного ресурса за полугодие (в млн. руб.).

7. Подготовить результаты расчетов и диаграммы к выводу на печать.

Литература

1. Козырев А.А. Информационные технологии в экономике и управлении: Учебник. Четвертое издание. – СПб.: Изд-во Михайлова В.А., 2003.

2. Информационные технологии (для экономистов): учеб. пособие/Под общ. Ред. А.К. Волкова. – М.: ИНФРА-М, 2003. – 310 с.

3. Веревченко А.П. и др. Информационные ресурсы для принятия решений. – Учебное пособие. – М.: Академический Проект; Екатеринбург: Деловая книга, 2002.

4. Прокопчук Л.О., Козырев А.А. Применение компьютерных программных продуктов при прогнозировании деятельности предприятия. – СПб.: Издательство СПбГТУ, 2002.

5. Симонович С., Евсеев Г., Алексеев А. Специальная информатика: универсальный курс. – М.: АСТ-ПРЕСС; Информ-Пресс, 2002.

6. Информатика: Учебник для экономических специальностей вузов / Под ред. Н.В. Макаровой. М.: Финансы и статистика, 2003.

8. Цисарь И.Т., Нейман В.Г. Компьютерное моделирование экономики. – М.: “ДиалогМИФИ”, 2002.

9. Тюрин Ю.Н., Макаров А.А. Статистический анализ данных на компьютере. / Под ред. Фигурнова В.Э.. – Москва.: ИНФРА-М., 1998.

10. Богатова Т. Специальные системы для финансовых и аналитических отчетов // PC Week/RE/.- 2003. - № 9.

11. Курицкий Б.Я. Поиск оптимальных решений средствами Excel. - СПб.: Торгово-издательское бюро BHV, 2001.

12. Карлберг, Конрад. Бизнес-анализ с помощью Excel.: Пер. с англ. – К.: Диалектика, 2003.

13. Каплан А.В., Каплан В.Е., Мащенко М.В., Овечкина Е.В. Решение экономических задач на компьютере. - СПб.: Питер, 2004.

Оглавление

Введение- 3

операции с текстом в Текстовом процессоре word 5

Элементы издательской работы-- 14

Создание графических объектов и применение редактора формул 19

Создание таблиц. Вычисления в word. Построение диаграмм 24

Приемы проектирования электронных таблиц 30

решениЕ задач оптимизации- 36

аНАЛИЗ И ПРОГНОЗ РЯДОВ НАБЛЮДЕНИЙ- 46

Методы обработки и анализа экономической информации в EXCEL 50

Литература- 50

[1] Модель не обязательно должна быть формализована в виде каких-то математических уравнений. Житейский опыт - это тоже одна из форм модели, построенной на знании и понимании поведения объекта в аналогичных ситуациях.

[2] Понятие временного ряда не ограничивается набором значений изменения состояния объекта во времени, это может быть изменение в пространстве, где роль оси времени выполняет любой другой параметр.

[3] Использование в формулах имен блоков вместо координат ячеек облегчает
их применение

[4] При задании аргументов функции через координаты ячеек обращение к ней получает менее удобный вид: ДСРЗНАЧ(В5:G15;G5;В1:В2)

[5] При копировании Ведомостей зарплаты на общий лист необходимо воспользоваться режимом Специальная вставка в меню Правка и вкладкой Связать по ссылке, чтобы сохранить связь с соответствующими формулами

[6] Следует обратить внимание на тот факт, что при консолидации данных MS EXCEL не отображает имена полей, их можно задать после процесса консолидации

⇐ Назад