Анализ данных в 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 дням.

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