Анализ документа с помощью диаграммы

Диаграммы можно использовать для наглядного представления результатов расчетов и их анализа.

Постановка задачи.

Постройте таблицу Расчетно-платежная ведомость, отражающую начисления каждого сотрудника на листе, который назовите Платежная ведомость. Данные для разработки таблицы представлены на рис.4.10. На основе готовой таблицы построить диаграмму

Рисунок 4.10. Платежная ведомость

Формулы для решения задачи:

Тариф=Стоимость нормочаса*Количество отработанных часов

Уральский коэффициент=Тариф*0,15

Пенсионный фонд=(Тариф+Уральский коэффициент)*0,01

Подоходный налог=(Тариф+Уральский коэффициент-Пенсионный фонд)*0,13

Итого к получению. В эту ячейку запишите формулу, реализующую следующее правило:

«Если у сотрудника нет разряда, то и зарплату он не получит. Если же у него есть разряд, то зарплата начисляется по следующей формуле: Тариф + Уральский коэффициент – Пенсионный фонд – Подоходный налог».

Этапы решения задачи:

1. На новом листе Платежная ведомость постройте таблицу, приведенную на рис.4.10. Самостоятельно определите, какие ячейки должны содержать исходные данные, а какие – формулы для расчетов. Обратите внимание на формулу в строке формул. Введите исходные данные и формулы.

2. Для построения диаграммы, показывающей начисления сотрудников, требуетсявыделить два столбца таблицы: Фамилия И. О. и Итого к получению. Эти столбцы не расположены рядом (не смежные), и традиционным способом их нельзя выделить. Однако, если удерживать нажатой клавишу Ctrl, то можно одновременно выделять ячейки в разных местах таблицы. Выделите заполненные данными ячейками таблицы, относящиеся к столбцам Фамилия И. О. и Итого к получению вместе с заголовками столбцов.

3. Постройте круговую диаграмму, используя команду Вставка, Диаграммы, Круговая.

4. Будет создана круговая диаграмма и появятся вкладки Работа с диаграммами: Конструктор, Макет, Формат. С их помощью можно поменять вид и расположение диаграммы. Используя эти вкладки, разместите на диаграмме легенду.

5. Сопоставьте свои результаты с результатами, приведенными на рис. (рис. 4.11).

6. Для того чтобы проверить, какая связь существует между таблицей начисления и диаграммой, перейдите на лист Платежная ведомость, в середину таблицы вставьте новую строку. Распространите на новую строку формулы, заполните данные на нового сотрудника. Проверьте, как новые данные отразились на диаграмме – новый сотрудник сразу же внесён в диаграмму.

7. Самостоятельно постройте диаграмму для решения другой информационной задачи.

Рисунок 4.11. Платежная ведомость с диаграммой

 

Выводы:

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

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

Таблица подстановки и диспетчер сценариев как средства решения задач экономического характера

5.1. Электронная таблица как динамическая модель

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

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

Для того чтобы выполнить анализ изменения исходных данных можно:

· изменить исходные данные вручную;

· написать соответствующие макросы для изменения исходных данных;

· использовать таблицы подстановки;

· использовать диспетчер сценариев.

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

К примеру, необходимо вычислить спрос и предложение некоторого товара, если они вычисляются по следующей формуле:

На рис.5.1. представлены фрагменты рабочего листа. Ячейке B1 присвоено имя Цена, а ячейкам С2 и D2 – имена Спрос и Предложение соответственно. Для присвоения имени ячейке или диапазону ячеек используется диалоговое окно Создание имени, которое открывается командой Формулы, Определение имени, Присвоить имя.

В ячейку введите формулу С2 = 2000-75*Цена, а в ячейку D2 – формулу = 740+65*Цена.

 

 

Рисунок 5.1. Формулы могут содержать числовые данные, адреса и имена ячеек

С помощью команды Формулы, Зависимости формул, Зависимые ячейки можно проследить связи между ячейками B1, C2, D2, Перед выполнением команды нужно установить рамку выделения на ячейку B1 с именем Цена.

 

 

Для создания таблицы, в которой производятся вычисления, выполните следующую последовательность шагов:

1. Для того, чтобы рабочая таблица была наглядной, в соответствующие ячейки можно поместить комментарии: Введите заголовки Цена=, Спрос, Предложение в ячейки А1, C1 и D1 соответственно.

2. В ячейку B1 поместите первоначальное значение (например, 10).

3. Измените значение в ячейке Цена и посмотрите, как изменятся значения в ячейках Спрос и Предложение.

 

Таблица подстановки с одной ячейкой исходных данных

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

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

Таблицу располагают в любом месте рабочего листа.

Структура таблицы подстановок и порядок работы с ней таковы:

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

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

 

Рисунок 5.2. Структура таблицы подстановки с одной ячейкой исходных данных

- Ячейка, которая стоит на пересечении строки формул и столбца значений входного параметра остаётся пустой (ее можно использовать для размещения входного параметра).

- Microsoft Excel вычисляет значения выражений, определенных формулами, подставляя в ячейку, отведенную для входного параметра, используемого в формулах, поочередно все значения из столбца исходных данных. Результаты вычислений записываются в строку, левее соответствующих исходных данных. Вычисления выполняются при инициализации команд: Данные, Работа с данными, Анализ «что-если», Таблица данных.

Рассмотрим использование таблицы данных для анализа изменений результатов вычислений в зависимости от изменений исходных данных (изменение спроса и предложения в зависимости от цены). Для этого используйте созданную в предыдущем задании таблицу.

Задание 5.1. Исследуйте возможности таблицы данных с одной переменной, выполнив следующие операции:

1. Скопируйте созданную в предыдущем задании таблицу на новый рабочий лист.

2. Заголовок Цена = из ячейки A1 перенесите в ячейку B1, удалите знак равенства = (рис. 3).

3. Измените формулы, введенные в ячейки C2 и D2, задав вместо ссылки на ячейку Цена, расположенную на первом рабочем листе, ссылку на ячейку B2 данного рабочего листа.

4. В диапазон ячеек B3:B14 введите последовательность чисел 1, 2, …, 12, которые будут служить исходными данными для расчета формул.

Ячейки C2 и D2 содержат формулы для выполнения команды подстановки, а в ячейку B2 будут подставляться исходные значения из диапазона B3:B14 для заполнения таблицы.

5. В ячейку E3 введите формулу =ЕСЛИ(C3=D3;"равновесные спрос и предложение";" "). Для ввода формулы используйте Мастер функций, диалоговые окна которого открываются командой Вставить функцию (f_x), расположенной в строке формул.

Скопируйте введенную формулу в ячейки E4:E14. Ячейки диапазона E3:E14 будут представлять результаты анализа. Для совпадающих значений спроса и предложения в соответствующей строке будут выведен текст о том, что спрос и предложение являются равновесными.

 

 

6. Выделите диапазон B2:D14.

7. Выполните команду: Данные, Работа с данными, Анализ «что-если», Таблица данных.

Рисунок 5.3. Результаты работы таблицы подстановки

 

8. В диалоговом окне команды в поле ввода Подставлять значения по строкам в (значения из диапазона-столбца будут использоваться для заполнения строк таблицы подстановки) укажите ссылку на ячейку B2, введя координаты ячейки с клавиатуры или щелкнув по ней мышью после переноса курсора в это поле.

 

В ячейки таблицы после выполнения команды оказываются введенными формулы {=ТАБЛИЦА(;B2)} (фигурные скобки показывают, что это формулы массива). Результат выполнения команд приведен на рис. 5.4. Из результатов видно, что равновесные спрос и предложения возникают при установленной цене товара в 9 денежных единиц.

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

 

Таблица подстановки с двумя ячейками исходных данных

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

Структура таблицы подстановки с двумя ячейками исходных данных приведена на рис. 5.5.

 

Рисунок 5.4. Структура таблицы данных с двумя переменными

Рассмотрим задачу: необходимо определить, как будет изменяться прибыль от продажи изделия, если изменится стоимость материала, необходимого для производства этого изделия, и оплата труда рабочих.

Расчет себестоимости выполняется по формуле:

Себестоимость = Стоимость материала * Количество материала +
Количество часов на изготовление изделия * Оплату 1 часа труда рабочих.

Задание5.2. Исследуйте возможности таблицы данных с двумя переменными, выполнив следующие операции:

1. Вставьте новый рабочий лист.

2. В ячейку A1 ведите строку Исходные данные.

3. В ячейке A2 наберите текст Количество материала, в ячейке A3 – Стоимость материала, A4 – Количество часов. A5 – Оплата одного часа.

4. В ячейку А6 введите строку Расчет прибыли. Выполните выравнивание столбца наименований (выделите столбец A и выполнить команду Главная, Формат, Формат, Автоподбор ширины столбца).

5. Присвойте ячейкам B2, B3, B4 и B5 с помощью команды присваивания имени имена: Количество_материала, Cтоимость_материала, Количество_часов, Оплата_часа. (Замечание: идентификатор (имя) не может состоять из нескольких слов, поэтому в именах, присваиваемых ячейкам, между словами были использованы знаки подчеркивания.)

6. Внесите соответствующие значения (рис. 5.5) в ячейки, предварительно отформатировав ячейки с именами «Стоимость материала» и «Оплата часа» с помощью диалогового окна Формат ячеек, выбрав формат Денежный и установить число десятичных знаков равное 2. Ячейку B2 (Количество_материала) отформатируйте, используя пользовательский формат и учитывая, что количество материала исчисляется в метрах: на вкладке Число окна Формат ячеек выберите в списке Числовые форматы формат (все форматы) и введите в поле ввода Тип образец пользовательского формата ## “м.”

7. В ячейки A7, A8, A9 внесите соответственно текст: Себестоимость, Отпускная цена, Прибыль.

8. Присвойте ячейкам B7, B8, B9 имена: Себестоимость, Отпускная _ цена, Прибыль соответственно.

9. Предварительно отформатировав ячейки B7, B8, B9 как Денежные, введите в ячейку B7 формулу для вычисления себестоимости:

= Количество_материала * Стоимость_материала + Количество_часов * Оплата_часа

10. В ячейку B8 введите отпускную цену (Например: 220р.), в ячейку B2 – количество материала 10, в ячейку B4 – количество часов, необходимых на изготовление изделия – 5.

11. Внесите в ячейку B9 формулу =Отпускная_цена–Себестоимость

Ячейка B9, содержащая формулу, является ячейкой таблицы подстановки и находится в левом верхнем её углу.

12. Поскольку нашей задачей является исследование влияния стоимости материала и оплаты одного часа работы на прибыль, расположите в диапазоне ячеек С9:H9 возможные значения стоимости материала, а в диапазоне B10:B20 – значения оплаты часа работы (рис. 6). Предварительно следует отформатировать соответствующие диапазоны как Денежный и установите число десятичных знаков, равное 2.

13. Выделите диапазон C10:H20 и отформатируйте его, задав свой формат:

 

# ##0,00р.;[Красный]-# ##0,00р.;[Синий]# ##0,00р.

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

14. Выделите диапазон B9:H20 и выполните команду Данные, Работа с данными, Анализ «что-если», Таблица данных. В диалоговом окне Таблица данных в строке Подставлять значения по столбцам укажите ссылку на ячейку B3 с именем Стоимость_материала, а в строке Подставлять значения по строкам – ссылку на ячейку B5 (Оплата_часа). Нажмите командную кнопку OK.

Рисунок 5.6. Результаты работы таблицы данных с двумя переменными

На рис. 5.6 приведены результаты расчета прибыли от производства изделия с помощью таблицы подстановки с двумя входными параметрами, принимающими значения из заданных диапазонов. Прибыль определяется как разность между отпускной стоимостью изделия и его себестоимостью.

Проанализируем результаты расчетов. Из таблицы видно, что наибольшая прибыль может быть достигнута при стоимости материала, равной 6 руб. и при оплате часа работы 25 руб. Прибыль не может быть получена, если стоимость материала возрастет до значения 10 р. (при той же почасовой оплате). Нет прибыли и в том случае, когда стоимость оплаты часа работы вырастет до 32 руб.

Чтобы удалить значения из таблицы данных, выделите диапазон ячеек, содержащих эти значения, и затем подайте команду Главная, Редактирование, Очистить, Очистить содержимое.

Чтобы удалить таблицу данных целиком, выделите все ячейки в диапазоне, который охватывает таблица, включая все формулы, и затем подайте команду Главная, Редактирование, Очистить, Очистить все.

Таким образом, таблица подстановки с двумя ячейками исходных данных является удобным средством анализа данных.

Недостатки:

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

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

 

Диспетчер сценариев

Таблица подстановок – хорошее средство, когда в расчетах принимает участие не более двух переменных. Однако реальные ситуации могут оказаться более сложными, и на помощь придет другое средство анализа – диспетчер сценариев.

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

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

Создайте рабочий лист по образцу, представленному на рис.5.7.

 

Рисунок 5.7. Рабочий лист для демонстрации сценариев

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

· Столбец Продажи – входные данные. Общий итог внизу столбца суммирует вместе объемы продаж по каждому продукту.

· Столбец Расходы – итоговые показатели затрат (входные данные). Общий итог внизу столбца суммирует вместе затраты по каждому продукту.

· Столбец Прибыль – данные получены вычитанием стоимости каждого продукта из его продаж. Общий итог внизу столбца суммирует вместе прибыль по каждому продукту.

· Столбец Рентабельность – данные получены делением прибыли от каждого продукта на его итоговые продажи. Значение внизу столбца – общая рентабельность, вычисленная делением общей прибыли на общее число продаж.

· Столбец Доля – данные получены делением прибыли от каждого продукта на общую прибыль компании (ячейка D11). Общий итог внизу столбца суммирует вместе показатели доли прибыли. Сумма должна составлять 100%.

Теперь с помощью сценария можно исследовать, что произойдет при изменении значений в столбцах Продажи и Расходы. Чтобы было сразу понятно, с чем вы работаете, каждое значение в этих столбцах (кроме итогов) должно иметь имя, состоящее из имени строки и столбца: Товары_для_здоровья_Продажи, Товары_для_здоровья_Расходы, Продукты_питания_Продажи, Продукты_питания_Расходы и т.д.

После создание рабочего листа, сохраните его перед продолжением работы.

Для работы со сценарием используется диалоговое окно Диспетчер сценариев, которое открывается командой Данные, Работа с данными, Анализ «что-если», Диспетчер сценариев. При первом появлении это окно не содержит сценариев.

 

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

Чтобы создать исходный сценарий необходимо:

1. Открыть диалоговое окно Диспетчер сценариев и нажать кнопку Добавить. Откроется диалоговое окно Добавление сценария. Введите название в текстовое окно Название сценария – например, Стартовый сценарий. В поле Изменяемые ячейки укажите ячейки, которые будут изменяться в вашем сценарии. При необходимости введите комментарий в текстовое поле Примечание. Определитесь с флажками в области Защита. Нажмите кнопку ОК.

Рисунок 5.8. Средства для создания и изменения сценария

 

2. Откроется диалоговое окно Значения ячеек сценария.

3. Вам не нужно изменять существующие значения для начального сценария. Нажмите кнопку ОК. Excel закроет диалоговое окно Значения ячеек сценария и вновь откроет диалоговое окно Диспетчер сценариев.

Добавление новых сценариев. Новый сценарий создается аналогично стартовому, но со следующими отличиями:

1. Введите другое имя для сценария в диалоговом окне Добавление сценария.

2. Не меняйте выделение изменяемых ячеек без необходимости. Excel автоматически выделит те ячейки, которые вы определили при создании первого сценария.

3. Измените значения в диалоговом окне Значения ячеек сценария для отображения изменений в рабочем листе. Кроме значений в данном диалоговом окне вы можете ввести формулы для изменения текущего содержимого ячеек. Например, чтобы посмотреть, какой эффект будет при уменьшении расходов на 25%, введите перед существующим значением =,75*.

4. Если вы хотите запретить изменения сценария, то установите флажок Запретить изменения (рис. 5.8). Если вы хотите скрыть сценарий от других пользователей, установите флажок Скрыть. После этого вам необходимо настроить защиту с помощью диалогового окна Защита листа, выбрав команду Рецензирование, Изменения, Защитить лист.

Самостоятельно создайте новый сценарий – Сценарий №1.

 

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

1. Открыть диалоговое окно Диспетчер сценариев и нажать кнопку Отчет. Откроется диалоговое окно Отчет по сценарию. Установите переключатель Тип отчета в положение структура. В поле Ячейки результата введите ссылки на ячейки, значения которых изменяются с помощью сценариев. Нажмите кнопку ОК.

 

2. Excel добавит новый лист Структура сценария, содержащий отчет.

 

3. Если вы выбрали отчет в виде сводной таблицы, Excel добавит новый лист отчета Сводная таблица по сценарию. Сводные таблицы подробно описаны в разделе 8 данного учебного пособия.

 

Выводы:

1. Таблицы подстановок являются удобным механизмом экономического анализа. Однако область применения этого механизма ограничена.
2. Таблица подстановки с одной ячейкой позволяет анализировать множество формул (до 32), каждая из которых зависит от одной и той же переменной.
3. Таблица подстановки с двумя ячейками позволяет обрабатывать только одну формулу, зависящую от двух переменных.
4. Если реальный процесс не укладывается в рамки указанных моделей, то нужно применять диспетчер сценариев.

 

 

6. Создание связей между таблицами

Специальная вставка

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

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

Рассмотрим пример.

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

Создадим таблицу для ведения списка работников и выполняемых ими работ. Эта таблица должна иметь вид (Рис. 6.1):

 

Рисунок 6.1. Первая связываемая таблица

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

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

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

 

Рисунок 6.2. Вторая связываемая таблица

Рассмотрим работу с командой специальной вставки для организации связей между таблицами.

Для организации связей выполните следующие шаги:

1. Переключитесь на рабочий лист, содержащий исходные данные (лист Текущие заказы).
2. Выделите диапазон, в котором будут содержаться исходные данные (диапазон B8:B17).
3. Выполните команду Копировать с помощью вкладки Главная или контекстного меню.
4. Перейдите на рабочий лист Стоимость выполненных работ.
5. Установите рамку выделения в ячейку B4.
6. Подайте команду Главная, Вставить, Специальная вставка. В диалоговом окне Специальная вставка в группе Вставить установите переключатель Все, в группе Операции – переключатель Нет и нажмите кнопку Вставить связь.

 

 

7. После выполнения команды рабочий лист примет вид

 

Для проверки того, как работают связи переключитесь на первый рабочий лист Текущие заказы и «примите на работу» еще одного исполнителя – Амирова А.А., вписав его в пустую строку (в ячейку B13). Переключитесь на второй рабочий лист Стоимость выполненных работ. Принятый на работу исполнитель автоматически появился на этом листе.