Специфические шкалы (например, шкала Ликерта с масками, такими как «Полностью согласен», «Согласен», «Не знаю», «Не согласен», «Полностью не согласен»).

Неупорядоченные имена (например, названия элементов, географические названия или имена людей).

· Гистограмма с накоплением и объемная гистограмма с накоплением Гистограммы с накоплением показывают отношение отдельных составляющих к их совокупному значению, сравнивая по категориям вклад каждой величины в общую сумму. Гистограмма с накоплением представляет значения в виде плоских вертикальных прямоугольников с накоплением. Объемная гистограмма с накоплением показывает эти же данные только в трехмерном виде. Третья ось значений (ось глубины) не используется.

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

· Нормированная гистограмма с накоплением и объемная нормированная гистограмма с накоплением Нормированные гистограммы с накоплением и объемные нормированные гистограммы с накоплением сравнивают по категориям процентный вклад каждой величины в общую сумму. Нормированная гистограмма с накоплением показывает значения в виде плоских вертикальных нормированных прямоугольников с накоплением. Объемная нормированная гистограмма с накоплением представляет эти же данные в трехмерном виде. Третья ось значений (ось глубины) не используется.

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

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

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

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

Форматирование диаграммы

Хотя на вкладке ленты Работа с диаграммами имеются великолепные возможности для работы с диаграммами, иногда их нужно расширить. В ранних версиях пакета Office дополнительные параметры форматирования были далеко спрятаны в труднодоступных сложных диалоговых окнах. Теперь эти параметры доступны в понятных, превосходно оформленных областях задач. Выберите элементы диаграммы (например, ряды данных, оси или заголовки), щелкните их правой кнопкой мыши и выберите в контекстном меню пункт Формат <элемент диаграммы>. Готово! Новая область задач Формат чудесным образом отобразится вместе с параметрами, специально предназначенными для выбранных элементов диаграммы.

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

Например, чтобы изменить форматирование оси, выполните следующие действия:

1. Щелкните ось диаграммы правой кнопкой мыши и выберите в контекстном меню пункт Формат оси.

2. В области задач Формат оси внесите нужные изменения.

· Выберите пункт Переместить, а затем перетащите область в новое расположение.

· Выберите пункт Размер, а затем измените размер области, потянув за ее край.

Графики

Данные, которые расположены в столбцах или строках, можно изобразить в виде графика. Графики позволяют изображать непрерывное изменение данных с течением времени в едином масштабе; таким образом, они идеально подходят для изображения трендов изменения данных с равными интервалами. На графиках категории данных равномерно распределены вдоль горизонтальной оси, а значения равномерно распределены вдоль вертикальной оси.

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

Графики содержат следующие подтипы диаграмм

Круговые диаграммы

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

Круговые диаграммы рекомендуется использовать, если:

· Требуется отобразить только один ряд данных.

· Все значения, которые требуется отобразить, неотрицательны.

· Почти все значения, которые требуется отобразить, больше нуля.

· Количество категорий не более семи.

· Категории соответствуют частям общего круга.

Линейчатые диаграммы

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

Линейчатые диаграммы рекомендуется использовать, если:

· Метки осей имеют большую длину.

· Выводимые значения представляют собой длительности.

Диаграммы с областями

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

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

Точечные диаграммы

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

Точечная диаграмма имеет две оси значений, при этом одни числовые значения выводятся вдоль горизонтальной оси (оси X), а другие -- вдоль вертикальной оси (оси Y). На точечной диаграмме эти значения объединяются в одну точку и выводятся через неравные интервалы или кластеры. Точечные диаграммы обычно используются для иллюстрации и сравнения числовых значений, например научных, статистических или технических данных.

Точечные диаграммы рекомендуется использовать, если:

· Требуется изменять масштаб горизонтальной оси.

· Требуется использовать для горизонтальной оси логарифмическую шкалу.

· Значения расположены на горизонтальной оси неравномерно.

· На горизонтальной оси имеется множество точек данных.

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

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

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

Для вывода данных таблицы в виде точечной диаграммы следует поместить данные по оси X в одну строку или столбец, а соответствующие данные по оси Y -- в соседние строки или столбцы.

Биржевые диаграммы

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

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

Поверхностные диаграммы

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

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

Кольцевые диаграммы

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

Пузырьковые диаграммы

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

Так, данные можно организовать в соответствии с приведенным примером.

Лепестковые диаграммы

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

Другие типы диаграмм, которые можно создать в приложении Excel

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

Редактирование диаграмм

Готовую диаграмму можно перемещать по рабочему листу, изменять ее размер. Чтобы удалить диаграмму, нужно выделить ее и нажать клавишу <Del>. Если диаграмма расположена на отдельном листе - удалить лист.

Диаграмма состоит из элементов, каждый из которых можно редактировать отдельно.

Элементы диаграммы:

· область диаграммы - это вся диаграмма;

· область построения - область размещения графиков, гистограмм (для круговых диаграмм этой области нет);

· ряды данных - это сами графики, столбики гистограммы, сектора круговой диаграммы;

· оси координат - ось Х (ось категорий) и ось Y (ось значений);

· заголовок диаграммы;

· легенда;

· линии сетки (для круговых диаграмм - отсутствуют).

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

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

· двойным щелчком на элементе;

· контекстное меню элемента Формат;

· меню Формат для выделенного элемента.

· Контекстное меню области диаграммы, кроме команды Формат, содержит команды:

1. Тип диаграммы

2. Исходные данные

3. Параметры диаграммы

4. Размещение

Эти команды открывают соответствующие окна Мастера диаграмм, что дает возможность исправить недочеты, допущенные при построении диаграммы.

Особенности выделения рядов данных:

1. щелчок на линии графика выделяет весь график, повторный щелчок - выделит отдельный его участок, соответствующий одному значению ряда;

2. щелчок на любом столбике гистограммы выделяет все столбики соответствующего ряда данных; повторный щелчок - выделит этот столбик;

3. щелчок на круговой диаграмме выделяет всю диаграмму, повторный щелчок на любом секторе - выделит этот сектор.

Добавление рядов данных (способы):

· выделить данные в таблице и перетащить мышью в область построения;

· контекстное меню области диаграммы Исходные данные вкладка Ряд кнопка Добавить.

Таким же образом можно добавить подписи по оси Х.

Шрифт заголовка, легенды, подписей

· кнопки параметров шрифта в панели инструментов;

· в диалоговом окне Формат (соответствующего элемента) вкладка Шрифт.

Цвет области построения (по умолчанию серый цвет):

· кнопка Цвет заливки в панели инструментов;

· в диалоговом окне Формат области построения вкладка Вид.

Цвет и узор столбиков гистограммы или секторов круговой диаграммы:

· кнопка Цвет заливки в панели инструментов;

· в диалоговом окне Формат рядов данных вкладка Вид (кнопка Способы заливки).

Линия графика:

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

Линии сетки области построения графиков и гистограмм:

· в диалоговом окне Параметры диаграммы на вкладке Линии сетки устанавливается наличие или отсутствие линий сетки.

· в диалоговом окне Формат линий сетки на вкладке Вид устанавливается тип, цвет и толщина линий сетки.

Изменение типа диаграммы

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

1. Чтобы изменить тип диаграммы, выполните одно из следующих действий.

· Для целой диаграммы выберите диаграмму.

· Для ряда данных выберите ряд данных.

2. В меню Диаграмма выберите команду Тип диаграммы.

3. На вкладке Стандартные выберите необходимый тип диаграммы.

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

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

27. Понятие списка. Сортировка списка. Фильтрация записей списка: автофильтрация, расширенный фильтр. Форма данных

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

Пример списка Excel

Фамилия	Возраст	Пол
Петухова	17	ж
Петров	17	м
Зайцева	17	ж
Морев	19	м
Иванов	19	м
Петрова	21	ж

В приведенной таблице Excel данные в первом и в третьем столбце имеют текстовый формат, а данные во втором столбце - числовой. Названия полей списка должны быть помещены в одну ячейку. Данные списка и другие данные на этом же листе должны быть отделены, по крайней мере, одной пустой ячейкой, то есть они не должны соприкасаться. Список Excel является типичной базой данных и к нему применимо большое число специфичных операций. Большинство таблиц, с которыми работают пользователи Excel, являются списками или могут быть приведены к виду списка. Так, если таблица, создавалась для каждой группы студентов, то их можно было бы объединить в одну таблицу, которая также была бы списком, добавив еще одно поле «Группа».

Объединение таблиц в список

Группа	Фамилия	Возраст	Пол	Рост	Вес
99-л-3	Петухова	17	ж	170	65
99-л-3	Петров	17	м	187	90
99-л-3	Зайцева	19	ж	180	75
97-л-1	Попов	19	м	176	80
97-л-1	Козлов	19	м	171	70

Сортировка списка.

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

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

Действие	Содержание действия
Сортировка списка	1. Установить курсор в области списка 2. Выполнить команду Данные, Сортировка 3. Указать порядок и направление сортировки для каждого ключа сортировки 4. Нажать кнопку <Параметры> и выбрать параметры сортировки (порядок по первому ключу, учет регистра, направление сортировки -- по строкам или по столбцам)

Фильтрация

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

· Автофильтр, включая фильтр по выделенному, для простых условий отбора;

· Расширенный фильтр для более сложных условий отбора.

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

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

Форма данных:

Ввод данных в список Excel можно осуществлять двумя способами:

ввод данных непосредственно в строку списка Excel;

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

Ввод данных непосредственно в строку списка Excel рассмотрен в предыдущем разделе. В этом разделе рассмотрим ввод, удаление и изменение данных в списке с помощью формы.

"Форма" - это окно диалога, предназначенное для удобного ввода, удаления и поиска данных в списках. Для создания полей в форме редактор Excel использует заголовки списка.

Для отображения формы на экране необходимо в меню "Данные" выбрать команду "Форма".

Ввод данных в форме осуществляется в текстовые поля. После ввода данных необходимо нажать кнопку Добавить, чтобы данные появились в списке Excel. Для просмотра записей можно воспользоваться линией прокрутки или кнопками "Далее" или "Назад".

Вставка записей в список с помощью формы:

Выделите заголовки списка Excel для создания полей в форме;

Выполните команду "Данные" - "Форма";

Введите данные новой записи;

Нажмите кнопку "Добавить" для добавления записи или "Вернуть", чтобы отменить добавление записи;

Нажмите кнопку "Закрыть".

Изменение записей в списке Excel с помощью формы:

Выделите ячейку в списке;

Выполните команду "Данные" - "Форма";

Найдите запись, которую необходимо изменить;

Измените данные в записи;

Нажмите кнопку "Закрыть".

Удаление записи из списка Excel с помощью формы:

Выделите ячейку списка;

Выполните команду "Данные" - "Форма";

Найдите запись, которую необходимо удалить;

Нажмите кнопку "Удалить";

Нажмите кнопку "Закрыть".

Поиск данных в списке Excel с помощью Формы:

Выделите ячейку списка;

Выполните команду "Данные" - "Форма";

Нажмите кнопку "Критерии";

Введите условия отбора записей (номер склада, поставщик и т.д.);

Нажмите кнопку "Далее", а затем кнопку "Назад" для просмотра отобранных записей;



28. Составление итоговых отчетов. Промежуточные итоги. Вложенные промежуточные итоги. Автоматическое подведение итогов. Структурирование рабочих листов. Автоструктурирование

Составление отчетов

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

Составление итоговых отчетов

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

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

MS Excel позволяет подводить промежуточные итоги автоматически. Для этого используется командуИтоги (Subtotals).

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

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

Добавление вложенных промежуточных итогов

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

Промежуточные итоги.

Для вычисления значений промежуточных итогов используетсяитоговая функция, например СУММ или СРЗНАЧ. Промежуточные итоги могут быть отображены в списке с помощью нескольких типов вычислений одновременно.

Вложение промежуточных итогов

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

Вложенные итоги

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

Автоматическое подведение итогов

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

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

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

Для правильного подведения итогов необходимо:

* данные должны быть созданы в виде списка, в котором каждое поле (столбец) имеет заголовок, расположенный в одной ячейке;

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

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

Создание промежуточных итогов:

* выделить любую ячейку списка;

* выполнить команду Данные - Итоги; появится ДО;

* выбрать поле, при изменении значения которого подводятся итоги (поле, по которому группируются данные);

* выбрать операцию для каждой группы данных;

Структурирование рабочего листа

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

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

Автоструктурирование.

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

1. Перейдите в Данные > Группировать > Создание структуры. Для более старых версий Excel перейдите в Данные > Группировка и структура > Создание структуры.

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

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

3. В структуре примера данные между первым рядом (шапкой) и последними двумя (содержащими формулы) были сгруппированы автоматически. Например, ячейка B7 содержит формулу СУММ.

Щелкните на кнопку [-], чтобы свернуть группу данных.

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

29. Консолидация данных. Области-источники и области-назначения

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

Консолидация - агрегирование (объединение) данных, представленных в исходных областях - источниках

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

Представление о консолидации данных

Существуют следующие варианты консолидации данных:

· с помощью формул, где используются ссылки;

· по расположению данных для одинаково организованных областей-источников (фиксированное расположение);

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

· с помощью сводной таблицы;

· консолидация внешних данных.

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

· все области на одном листе -- в ссылках указывается адрес блока ячеек, например D1.C8;

· области на разных листах -- в ссылках указывается название листа, диапазон, например

· лист1!D1 :лист2!С8;

· области в разных книгах, на разных листах -- в ссылках указывается название книги, название листа, диапазон, например [книга1] лист1!D1: [книга2] лист2!С8.

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

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

Пример областей-источников при консолидации данных по расположению

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

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

Пример областей-источников при консолидации данных по категориям

Условия консолидации задаются в диалоговом окне «Консолидация». В окне «Функция» выбирается функция консолидации данных. Для каждой области-источника строится ссылка, для чего курсор устанавливается в поле ссылки, затем переходят в область-источника для выделения блока ячеек и нажимается кнопка <Добавить>.

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

Диалоговое окно «Консолидация» для задания условий консолидации

Области-источники и области-назначения

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

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

- выделение исходного диапазона с помощью мыши;

- ввод ссылки на диапазон с клавиатуры.

Выделение исходного диапазона с помощью мыши осуществляется стандартными приемами.

Ввод ссылок на диапазон с клавиатуры. Существует два вида ссылок: внутренние и внешние.

Внутренняя ссылка - это ссылка на диапазоны ячеек рабочей книги. Она является частным случаем внешней ссылки.

Синтаксис внутренней ссылки:

='Имя_листа'!Адрес_диапазона

Удаление и редактирование ссылок

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

Чтобы удалить ссылку из диапазонов консолидации, выделите ее в списке диапазонов и нажмите кнопку Удалить .

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

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

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

Тип ( функция) консолидации. Обобщение исходных данных может быть осуществлено с использованием следующих функций:

- Сумма	- Количество значений
- Среднее значение	- Смещенное отклонение
- Максимум	- Несмещенное отклонение
- Минимум	- Смещенная дисперсия
- Произведение	- Несмещенная дисперсия
- Количество чисел

По умолчанию используется функция Сумма.

Консолидация данных по физическому расположению

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

Консолидация по заголовкам строк и столбцов

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

30. Создание сводных таблиц. Мастер сводных таблиц. Области строк, столбцов, страниц. Изменение сводной таблицы

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

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

2. Щелкните любую ячейку в диапазоне или таблице.

3. Выберите Вставка > Рекомендуемые сводные таблицы.

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

5. Нажмите кнопку ОК.

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

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

В примере ниже столбец Названия строк отфильтрован и отображает только продажи для регионов ''Юг'' и ''Запад''.

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

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

Вместо мастера сводных таблиц и диаграмм из более ранних версий Excel для создания сводных таблиц теперь следует использовать команды ленты Сводная таблица иРекомендуемые сводные таблицы. При этом мастер по-прежнему доступен, и его можно добавить на панель быстрого доступа либо вызвать с помощью клавиш ALT + D + P.

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

1. Выделите таблицу.

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

3. Шаг первый. Выберите пункты:

4. a) создать таблицу в списке или базе данных Microsoft Excel.

5. b) вид создаваемого отчета выберите Сводная диаграмма (со сводной таблицей).

6. Шаг второй. Укажите диапазон, содержащий исходные данные. Мастер предлагает свой диапазон, при необходимости его можно изменить.

7. Шаг третий. Выберите Поместить таблицу в новый лист и нажмитеГотово. На экране появится поле для диаграммы и список полей сводной таблицы.

8. Перетащите мышкой в ячейку для полей категорий поле Спорт. В поля рядов - Год, и в элементы данных - Продажи.

9. Теперь отредактируйте тип диаграммы - сделайте обычную гистограмму.

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

a) Выберите вкладку Диаграммы и отметьте Пользовательский размер.

b) Теперь во вкладке Страница выберите ориентацию Книжная.

Отформатируйте диаграмму.

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

Возможности табличного процессора MS Excel по осуществлению анализа данных дополняют методы, которым разработчики MS Excel дали название “Что - если?”. Они предлагают несколько возможных способов решения и прогнозирования той или иной задачи. Каждый из этих методов имеет определенное назначение, а их использование зависит от характера решаемой задачи. С их помощью можно ответить на вопросы:

- Какой результат принесет повышение разницы между себестоимостью и отпускной ценой?

- Какие изменения претерпят экономические показатели при росте объема реализации продукции на 10 %?

Методы “Что - если?” позволяют найти ответ на все вопросы, подобные этим. При обычной установке пакета программ MS Office пользователь получает возможность использовать некоторые инструменты анализа, принадлежащие к этой группе:

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

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

- поиск решения осуществляет вычисление оптимального значения целевой функции.

Рассмотрим более подробно сущность этих методов.

В практической деятельности специалистам приходится решать как прямые, так и обратные задачи. Прямые задачи отвечают на вопрос, к какому результату приведут действия, предпринятые в определенных условиях? Например, вычислить значения функции f{x) для N значений аргумента х, принадлежащих отрезку [А, В].

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

Подбор параметра позволяет решить задачу проще. Это средство применимо только в одной конкретной ситуации: когда известен требуемый ответ, формула для вычисления этого ответа и существует только одно входное значение для получения этого ответа. В диалоговом окне Подбор параметра показаны три необходимых компонента: Установить в ячейке, Значение и Изменяя значение ячейки. Таким образом, в этом диалоговом окне необходимо указать только одну установленную и одну изменяемую ячейки. Причем диапазон ячеек в этом случае использовать нельзя.

Работа этого средства основана на оценке наибольшего числа в изменяющейся ячейке и сравнении с ответом в фиксированной ячейке. После чего этот ответ сравнивается с содержимым поля Значение. Затем происходит оценка наименьшего числа и соответствующее сравнение. Поочередно выбирая большие и малые числа, средство Подбор параметра пытается подобрать значение в изменяющейся ячейке, необходимое для получения ответа, заданного в фиксированной ячейке. Иногда это средство не может найти ответ - неверны, например, входные данные или ответа вообще не существует. В этом случае появляется сообщение об ошибке. По умолчанию средство Подбор параметра производит до 100 итераций (попыток найти ответ). Поиск прекращается, когда найден ответ с точностью до 0,001. Если ответ не найден по той причине, что число, предложенное средством Подбор параметра, отличалось от ответа больше, чем позволяет точность, определенная по умолчанию, а при более внимательном рассмотрении установлено, что такое число существует, то можно изменить число итераций или точность вычислений с помощью команды Сервис/Параметры. В появившемся диалоговом окне Параметры выбрать вкладку Вычисления, а затем увеличить число итераций (опция Предельное число итераций) или уменьшить относительную погрешность (опция Относительная погрешность). Данный инструмент может быть использован при обязательном выполнении двух условий:

- в установленной ячейке должно быть число;

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

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



32. Анализ данных на основе таблицы подстановки. Построение таблицы подстановки для одной и двух переменных

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

Создание таблицы подстановки с одной переменной

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

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

2. Выполните одно из следующих действий.

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

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

3. Выделите диапазон ячеек, содержащий формулы и значения подстановки.

4. В меню Данные выберите команду Таблица.

5. Выполните одно из следующих действий:

· если значения в таблице расположены по столбцам, введите ссылку на ячейку ввода в поле Подставлять значения по строкам в;

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

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

1. В ячейку листа введите формулу, которая ссылается на две ячейки ввода.

В приведенном ниже примере, где исходные значения формулы введены в ячейки B3, B4 и B5, формулу =ПЛТ(B3/12;B4;-B5) следует ввести в ячейку C2.

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

В приведенном ниже примере различные процентные ставки следует ввести в ячейки C3, C4 и C5.

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

В приведенном ниже примере срок погашения ссуды (в месяцах) следует ввести в ячейки D2 и E2.

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

В приведенном ниже примере следует выделить диапазон C2:E5.

5. В меню Данные выберите команду Таблица подстановки.

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

В приведенном ниже примере в поле Подставлять значения по столбцам в следует ввести B4.

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

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

8. Нажмите кнопку OK.

Пример

Таблица данных с двумя переменными может показать влияние на размер ежемесячных выплат по ссуде различных процентных ставок и сроков займа. В следующем примере ячейка C2 содержит формулу вычисления платежа, =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейки ввода B3 и B4.

33. Сценарный подход для решения задач, реализованный с помощью средства Excel диспетчер сценариев

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

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

Определение сценариев

Чтобы Вы смогли получить общее представление о средстве Диспетчер сценариев, начнем с простого примера - производственной модели.

Пример производственной модели

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



Три сценария производственной модели Excel

Сценарий	Почасовая оплата	Стоимость единицы материала
В лучшем случае	30	57
В худшем случае	38	62
Наиболее вероятно	34	59

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

Диалоговое окно Диспетчер сценариев

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

Диалоговое окно Добавление сценария

· Название сценария. В этом поле можно указать любое имя сценария, но желательно, чтобы оно что-то обозначало.

· Изменяемые ячейки. Ячейки, в которых находятся исходные данные для сценария. В это поле можно ввести абсолютный адрес ячейки или ее имя. Разрешается выбирать несколько ячеек, причем все они не обязательно должны быть смежными. В каждом сценарии, которому присвоено имя, можно использовать одни и те же наборы изменяемых ячеек или разные изменяемые ячейки. Количество изменяемых ячеек для одного сценария ограничено числом 32.

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

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

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

Диалоговое окно Значение ячеек сценария

Примечание. Для удобства использования сценариев необходимо поместить на любую панель инструмент Сценарий:

· Выполнить команду Сервис | Настройка.

· В диалоговом окне Настройка выбрать вкладку Команды.

· Выбрать категорию Сервис.

· Выделить инструмент Сценарий и перетащить его на любую панель инструментов.