Анализ данных в Excel

Новогрудский  государственный торгово-экономический  колледж

Р Е Ф Е Р А Т

на  тему: «Анализ данных в Excel»

Новогрудок

2004

Подведение промежуточных  итогов.

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

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

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

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

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

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

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

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

Значения промежуточных  итогов и общего итога автоматически изменяются при изменении данных списка.

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

Для создания списка с  промежуточными итогами нужно:

• Отсортировать список по полю, в котором отдельные записи должны быть разбиты на группы, используя команду Данные - Сортировка.
• Выберите команду Данные – Итоги. На экране появится диалоговое окно Промежуточные итоги.

Рис 1. Окно «Промежуточные итоги»

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

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

По умолчанию промежуточные  итоги будут выводиться под отдельными группами записей, а общий итог — в конце списка. Если вы привыкли к другой форме представления списка — сначала результирующая запись, затем данные — можно представить список в привычном виде. Для этого снимите флажок Итоги подданными в диалоговом окне промежуточные итоги (см. рис. 1).

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

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

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

Чтобы разом удалить все промежуточные итоги, включая промежуточные итоги подгрупп, установите указатель ячейки в область списка, выберите команду Данные – Итоги, в появившемся диалоговом окне Промежуточные итоги щелкните на кнопке Убрать все. Список примет исходный вид. Самый простой способ убрать промежуточные итоги из списка когда угодно после их создания — отсортировать список. При этом на экране появится предупреждающее сообщение. Если на предупреждение дать положительный ответ, список будет отсортирован, а промежуточные итоги из него удалены.

Консолидация.

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

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

Исходные диапазоны  могут располагаться в различных  частях рабочего листа, в различных  рабочих листах, в различных рабочих  книгах, а также, например, в таблицах программы Lotus 1-2-3. Количество исходных диапазонов ограничено числом 255. При этом файлы, содержащие данные, не должны быть обязательно открыты во время консолидации.

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

Диапазон назначения можно задать двумя способами:

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

Исходный диапазон и  диапазон назначения могут располагаться как на отдельных рабочих листах, так и на одном.

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

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

Для консолидации нужно:

• Укажите диапазон назначения. Для этого установите указатель ячейки в левый верхний угол будущего диапазона назначения. Позаботьтесь, чтобы ячейки справa от выделенной не содержали никакой полезной информации, так как она будет заменена новой.
• Выберите команду Данные – Консолидация. На экране появится диалоговое окно Консолидация (рис. 2). По умолчанию в данном диалоговом окне определена только используемая функция, а остальная информация введена для консолидации данных примера.

Рис 2. Консолидация данных

• В раскрывающемся списке функций в поле Функция  (в верхней части диалогового окна) выберите необходимый элемент.
• Щелкните на поле Ссылка и введите ссылку на первый исходный диапазон. Сделать это можно вручную с клавиатуры, или указав нужный диапазон на рабочем листе. Используя кнопку Обзор, можно вывести ссылку на другой файл, содержащий исходный диапазон. Имя листа и ссылку на диапазон в этом случае нужно ввести в поле Ссылка вручную с клавиатуры. Существенное преимущество данного метода — не нужно открывать документ. Кроме того, в имени файла исходного диапазона можно использовать символы подстановки «*» и «?», смысл которых: «*» — любое количество любых знаков, «?» — один произвольный символ. В этом случае исходный диапазон должен находиться на первом листе указанной книги.
• После того, как исходный диапазон обозначен в поле Ссылка, щелкните на кнопке Добавить, чтобы добавить исходный диапазон к списку исходных диапазонов.
• Занести все диапазоны данных для консолидации в список Список диапазонов. Если возникнет необходимость удалить какой-либо диапазон из списка, выберите его и щелкните на кнопке Удалить.
• В поле Использовать в качестве имен определите, будут ли использоваться в качестве названий строк и столбцов консолидированной таблицы подписи верхней строки и значения левого столбца.
• Щелкните на кнопке ОК, чтобы начать процесс консолидации. Результат консолидации - таблица, представленная в диапазоне назначения.

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

Подбор параметра.

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

Предлагаем вам сразу  же перейти от слов к делу и осваивать  новое средство на примере. Пусть  организации N необходимо выполнить  срочный (в течение 6 дней) заказ на отладку программного обеспечения. По предварительным оценкам выполнение всего объема работ потребую 600 машино-часов. При этом продолжительность рабочего дня — 8 часов, а сотрудников отдела, обеспеченных ПК, всего 6 человек. Срок исполнения работ рассчитываете» по формуле, представленной на рис. 3 справа от текущего значения в соответствующей строке.

Рис. 3. Таблица  данных

Для решения задачи воспользуемся  подбором параметра:

• Выберите команду Сервис – Подбор параметра. На экране появится диалоговое окно Подбор параметра рис. 4.

Рис. 4. Указание целевой и изменяемой ячеек

• В поле Установить в ячейке укажите целевую ячейку — ячейку, в которой мы хотим получить желаемое значение. Сделать это можно, введя с клавиатуры адрес ячейки или выделив ее прямо в рабочем листе. В нашем примере целевой будет ячейка С5 (см. рис. 4). Разумеется, при выполнении вычислений с помощью функции подбора параметра необходимо, чтобы целевая ячейка была прямо или косвенно связана с ячейкой, в которой находится изменяемое значение.
• Введите в поле Значение числовое значение, которое должно быть получено в целевой ячейке. В этом поле допустимо только число, в противном случае при запуске появится соответствующее сообщение. Для нашего примера значение в целевой ячейке должно равняться 6 дням.

Теперь нам необходимо решить, за счет изменения какого из параметров (времени работы — увеличения продолжительности рабочего дня, или количества рабочих мест - привлечения сотрудников других отделов) мы будем стараться получить требуемый результат. Для начала попробуем выяснить, сколько нам потребуется работников, чтобы выдержать сроки, не меняя продолжительность рабочего дня.

• В поле Изменяя значение ячейки укажите адрес ячейки, значение которой будет изменено для получения желаемого результата. Как мы ранее договорились, изменять мы будем количество работников, то есть значение ячейки СЗ.
• Щелкните на кнопке ОК, чтобы запустить поиск нужного значения. На экране появится диалоговое окно Результат подбора параметра с сообщением о результате поиска (рис. 5).

Рис. 5. Результат  подбора параметра.

Кроме того, что на экране появилось диалоговое окно, на рабочем  листе тут же отразился результат. У нас получилось, что требуется 12,5, то есть 13, человек. Очевидно, мы не сможем привлечь еще семь человек. После переговоров с начальством выяснилось, что нам выделят самое большее еще 3 рабочих места.

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

Теперь выясним, насколько нужно увеличить рабочий день, чтобы максимальное число работников (6+3=9 человек) выполнили работу в срок.

• В свете последних событий необходимо изменить количество работников в таблице данных с 6 до 9.
• Снова выберите команду Сервис – Подбор параметра, чтобы вызвать диалоговое окно Подбор параметра (см. рис. 4) и ввести необходимые значения.
• В поле Изменяя значение ячейки укажите адрес ячейки, содержащей информацию о продолжительности рабочего дня, то есть ячейки С2.
• Щелкните на кнопке ОК, чтобы запустить поиск нужного значения. На экране появится диалоговое окно Результат подбора параметра с сообщением о результате поиска.
• Щелкните на кнопке ОК, чтобы полученный результат был сохранен.

Рис.6. Таблица  данных с результатом подбора параметра.

Как видно на рис. 6, если привлечь к  работе 9 человек, они выполнят работу в срок, только если будут работать немного дольше — 11 часов в день.

Таким образом можно  подобрать подходящее для конкретной ситуации решение.

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

Таблица подстановки.

Мы научились отыскивать решение задачи методом подбора, однако возможны самые разнообразные задачи, и никто не гарантирует, что, приняв один из предложенных вариантов, вы не окажетесь всего в одном шаге от оптимального решения, но уже не узнаете об этом. Что же нужно, чтобы этого не произошло? Наверное, получить как можно больше информации для анализа и выбора. Средство, которое предоставит нам эти возможности, — Таблица подстановки.

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

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

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

• На свободном месте рабочего листа создайте список значений подстановки для переменной — это можно сделать в отдельном столбце или строке. На рис. 7 представлен список значений подстановки, в котором длительность рабочего дня составляет от 8 до 14 часов с интервалом в 30 минут (ЕЗ:Е15). (Количество значений подстановки не ограничено.)

Рис. 7. Рабочий лист перед  созданием таблицы подстановки, с одной переменной

• Теперь нужно ввести формулу, в которую будут подставляться значения из списка. Введите ее в качестве заголовка следующего столбца справа. Как показано на рис. 7, вводимая формула должна буква в букву копировать соответствующую формулу (со ссылкой на ячейку С2) из таблицы с исходными данными, содержащуюся в ячейке С5.
• Выделите диапазон, содержащий значения подстановки и формулы, как это сделано на рис. 7.
• Выберите команду Данные – Таблица подстановки. На экране появится диалоговое окно Таблица подстановки (рис. 8).

Рис. 8. Указание ячейки подстановки.

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

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

• В поле Подставлять значения по строкам в укажите адрес ячейки в исходной таблице, содержащей данные о продолжительности рабочего дня, так как метки строк в таблице подстановки соответствуют возможным значениям продолжительности рабочей смены. В нашем примере адрес ячейки подстановки — С2.
• Щелкните на кнопке ОК, чтобы запустить процесс создания таблицы подстановки. Созданная таблица подстановки представлена на рис. 9.

Рис. 9. Таблица  подстановки с одной переменной

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

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

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

• На новом свободном месте рабочего листа создайте списки значений подстановки для двух переменных: значения одной переменной будут представлены в столбце (продолжительность рабочего дня — диапазон КЗ:К13 на рис. 10), а значения другой переменной — в строке заголовков столбцов справа (количество работников от 6 до 9 человек — диапазон L2:O2 на рис. 10).

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

• Формулу, в которую нужно подставлять значения столбца и строки, введите в ячейку на пересечении столбца и строки со значениями подстановки (К2 на рис. 10). Как и в случае с таблицей подстановки с одной переменной, вводимая формула должна буква в букву копировать соответствующую формулу из таблицы с исходными данными, содержащуюся в ячейке С5.
• Выделите диапазон, содержащий значения подстановки и формулу, как показано на рис. 10.
• Выберите команду Данные – Таблица подстановки. На «экране появится диалоговое окно Таблица подстановки (см. рис. 8).
• В поле Подставлять значения по столбцам в укажите адрес ячейки из таблицы данных, содержащий информацию о количестве работников (ячейка СЗ на рис. 10), так как в названиях столбцов у нас стоят значения о количестве работников.
• В поле Подставлять значения по строкам в укажите адрес ячейки в исходной таблице, содержащей данные о продолжительности рабочего дня (ячейка С2 на рис. 10), так как метки строк в таблице подстановки соответствуют возможным значениям продолжительности рабочей смены.
• Щелкните на кнопке ОК, чтобы запустить процесс создания таблицы подстановки. Созданная таблица подстановки представлена на рис. 11.

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

Поиск решения

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

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

Рассмотрим работу процедуры  поиска решения на примере. Составим план загрузки оборудования. Для этого  создадим таблицу, изображенную на рис. 12.

Рис. 12. Таблица-пример.

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

• на используемом оборудовании можно производить не более 35000 единиц товара;
• общие издержки не должны превышать 4,5 млн.

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

• Запустите процедуру поиска решения. Для этого активизируйте команду Поиск решения из меню Сервис, вследствие чего откроется одноименное диалоговое окно (см. рис 13). Если эта команда отсутствует в меню, то активизируйте команду Надстройки из меню Сервис. В предложенном списке надстроек найдите Поиск решения и активизируйте опцию рядом с ним. Щелкните по кнопке ОК и данная надстройка будет установлена на ваш компьютер.

Рис. 13. Диалоговое окно Поиск решения

• В поле Установить целевую ячейку введите адрес ячейки (целевой) значение которой используется в качестве критерия оптимизации. В нашем примере таковой является ячейка D11, в которой представлены данные о суммарных издержках при текущей загрузке оборудования. Обратите внимание, что данная ячейка содержит формулу и связана с изменяемыми ячейками. Задайте значение для целевой ячейки. В нашем примере – это значение 4,5 млн. Активизируйте переключатель значению и введите в поле ввода значение 4500000.

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

• И поле Изменяя ячейки укажите, в каких ячейках программа должна изменить значения для получения оптимального результата. Активизируйте указанное поле ввода и выделите на рабочем листе ячейки B4 – C9.

Примечание: При нажатии кнопки Предположить Excel 2000 выделяет диапазон ячеек на которые есть ссылка в целевой ячейке.

• Теперь необходимо указать два ограничения. Для этого выполните щелчок на кнопке Добавить в диалоговом окне Поиск решения. В результате откроется диалоговое окно Добавление ограничения (см. рис. 14).

Рис. 14. Диалоговое окно Добавление ограничения

• В поле Ссылка на ячейку этого окна введите адрес ячейки содержимое горой должно удовлетворять заданному ограничению. В поле ограничение укажите значение, выступающее в качестве ограничения или адрес ячейки с таким значением. Между этими двумя полями находится поле, в  котором  устанавливается оператор, определяющий отношения между значением ячейки и ограничением.
• Чтобы задать первое ограничение, активизируйте поле Ссылка на ячейку  диалогового окна Добавление ограничения и выделите ячейку C11, а в поле Добавление ограничение введите значение 35000. Выберите оператор <=, поскольку количество производимых единиц товара не должно превышать 35000. В завершение установки ограничения выполните щелчок на  кнопке Добавить. Диалоговое окно Добавление ограничения останется открытым, и вы приступите к определению второго ограничения, которое заключ<span class="dash041e_0431_044b_