Выявление зависимостей и структуры.

Самый простой способ установления ячеек, влияющих на данную ячейку, это двойной щелчок по ней мышью или нажатие клавиши F2 – влияющие ячейки будут обведены рамкой. Если необходимы более детальные сведения о связях данных, нужно обратиться к другому инструменту. Графическое отображение зависимостей (трассировка) доступно через меню Сервис+Зависимости, которое, в свою очередь, открывает собственное меню управления индикацией зависимостей:

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

Зависимые ячейки – показывает стрелками ячейки, содержимое которых зависит от данной ячейки.

Источник ошибки – позволяет выявить клетки, являющиеся источниками ошибок (вида #ЗНАЧ, #ДЕЛ/0, #ЧИСЛО и др.) в текущей ячейке, показав стрелками связи с такими влияющими ячейками.

Убрать все стрелки – удаляет все стрелки, показывающие зависимости.

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

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

Скрыть детали

Отобразить детали

Группировать …

Разгруппировать …

 

Создание структуры

Удалить структуру

Настройка …

Пункт меню Создание структуры автоматически структурирует выделенный интервал или весь рабочий лист, основываясь на формулах и направлении ссылок. Все ссылки на формулы должны идти в одном направлении. Выше и слева от бордюра находятся элементы, описывающие структуру и управляющие ею. Маленькие кнопки с цифрами обозначают уровни структуры таблицы. Самый нижний уровень составляют собственно данные. Следующий уровень фиксирует первое обобщение данных в таблице, где установлены кнопки со знаком «-». Данные, относящиеся к этому уровню, охвачены горизонтальной скобкой. Самый высокий первый уровень по горизонтали имеет единственную группу. Аналогична структура по вертикали. Нажатие на любую кнопку со знаком минус скрывает данные, относящиеся к соответствующей группе, оставляя только итоговую строку/столбец. На самой кнопке знак минус замещается на плюс. Повторное нажатие на эту кнопку возвращает прежний вид группе. Нажатие на кнопки с цифрами соответствует скрытию данных всех групп данного уровня. Восстановление показа скрытых данных из групп может быть осуществлено нажатием на кнопку более низкого уровня (с большей цифрой). Если пользователя не удовлетворяет структура, найденная с помощью средства Создание структуры, он может самостоятельно определить собственную структуру, выделив соответствующие строки/столбцы и воспользовавшись пунктом Группировать. Пункт Разгруппировать изымает выделенные строки/столбцы из их группы. Действие пунктов Отобразить/Скрыть детали, в основном, аналогично действию кнопок управления структурой. Пункт Настройки определяет параметры, используемые при создании структуры.

ОБОБЩЕНИЕ ДАННЫХ.

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

Данные

Сортировка… Фильтр Форма… Итоги… Проверка…

Таблица подстановки… Текст по столбцам… Мастер шаблонов… Консолидация… Группа и структура

Сводная таблица

Внешние данные Обновить данные

СОРТИРОВКА.

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

Универсальное средство сортировки содержится в меню Данные+Сортировка. Здесь окно диалога, позволяющее сразу указать иерархию сортировок (до трех уровней вложенности), а также определить, включается или нет верхняя строка в область сортировки (раздел Идентифицировать поля по). Если установлен переключатель по подписям – заголовки остаются неприкосновенными, если по обозначениям столбцов – они включаются в сортировку. Кнопка Параметры открывает окно для первой колонки из таблицы, уточняющее режим сортировки. По умолчанию предлагается Обычный порядок. Если нажать кнопку прокрутки (кнопку с треугольником) откроется меню, содержащее все списки (собственные списки Excel или созданные пользователем), в соответствии с которыми отсортированы данные. Применение списков позволяет сортировать данные в последовательности, определяемой списком, т.е. в порядке, установленном самим пользователем Excel. Кроме того, здесь может быть учтен регистр букв (заглавные/строчные буквы) при сортировке и, наконец, только здесь можно выбрать сортировку и по горизонтали (Сортировать – столбцы по диапазона).

ФИЛЬТРАЦИЯ.

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

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

Показать все – отмена фильтра (предъявляются все данные).

Расширенный фильтр – вызов средств установки сложного фильтра.

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

Пункты, заключенные в скобки, выполняют функции:

(Все) – предъявляются все данные (фильтр по этому полю отменяется).

(Первые 10) – предъявляется подмножество данных, находящихся в начале/конце отфильтрованной таблицы. Этот пункт позволяет выделить некоторое подмножество из имеющихся данных. Здесь можно задать любое (а не только 10) число наибольших/наименьших элементов списка или процентов от всего множества данных. Управление отбором осуществляется через интерфейс.

(Условия...) – вызываются средства задания более сложного фильтра. Этот пункт дает возможность организовать более сложный фильтр, нежели просто выбор одного из существующих значений. При задании ключа фильтрации могут использоваться символы шаблона * и?. Их применение позволяет осуществлять поиск по не полностью заданному ключу. Очевидное ограничение здесь – это невозможность задать более двух условий на одну колонку одновременно и слабая возможность комбинировать сложные условия для разных колонок.

(Пустые) – предъявляются строки, не содержащие данных в колонке.

(Непустые) – предъявляются только непустые строки в колонке.

Последние два пункта включаются в меню, если в колонке имеются незаполненные клетки.

Расширенная фильтрация. Пункт Данные+Фильтр+Расширенный фильтр дает возможность задать сложный критерий отбора данных непосредственно в клетках рабочего листа без ограничения числа условий. Этот критерий должен содержать точные названия заголовков колонок (лучше создать их копированием), для которых формируется критерий, а под ними собственно условия фильтрации. Условия, находящиеся в одной строке, объединяются в фильтре логической функцией И, условия, находящиеся на отдельных строках – функцией ИЛИ. Допускается соединение условий И и ИЛИ в одном фильтре.

ИТОГИ.

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

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

Сумма – сумма элементов. Используется по умолчанию для подведения итогов по числовым полям.

Кол-во значений – число строк данных. Используется по умолчанию для нечисловых полей.

Среднее – среднее арифметическое элементов.

Максимум – максимальное числовое значение в группе.

Минимум – минимальное числовое значение в группе.

Произведение – произведение всех значений в группе.

Кол-во чисел – количество строк, содержащих числовые данные.

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

Смещенная/несмещенная дисперсия – смещенная/несмещенная оценка дисперсии по выборке.

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

Заменять текущие итоги – заменяет все полученные ранее итоги новыми. Если убрать этот флажок, то новые итоги будут вставлены под полученными ранее, а не заместят их. Это позволяет, при необходимости, выполнить процедуру Итоги несколько раз.

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

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

Убрать все – удаляет все итоги из таблицы.

Дальнейшую возможность обобщения предоставляет появившаяся слева «карта» структуры данных с кнопками свертки + и -.

КОНСОЛИДАЦИЯ.

Инструмент Консолидация позволяет объединить таблицы, находящиеся в разных местах и, даже, в разных листах и разных книгах. Все такие таблицы должны иметь абсолютно идентичную структуру. Соединение не является механическим. Итоговая таблица будет содержать только одну строку с ключевым полем, а числовые данные в ней будут суммами (или другими функциями) всех строк объединенного подмножества. Ключевым полем считается самое левое поле (колонка) таблицы или ее выделенной части. Консолидация может быть применена и к одной таблице. Для выполнения консолидации предварительная сортировка не нужна. Окно Консолидация имеет следующие разделы. В поле Функция можно выбрать одну из доступных обобщающих функций. Чаще всего это функция Сумма. Затем в разделе Ссылка следует задать блок, для которого выполняется анализ. Диапазон может быть задан с клавиатуры, а может быть и выделен мышью непосредственно на листе (выделяемый блок обрамляется бегущим пунктиром). После этого следует нажать кнопку Добавить – координаты выделенного блока переместятся в окошко Список диапазонов. При необходимости объединить несколько таблиц эти действия следует повторить столько раз, сколько имеется таблиц. В случае потребности изъять какие-то блоки используется кнопка Удалить. Флаги в разделе Использовать в качестве имен определяют, указывать или нет в консолидирующей таблице названия колонок (флаг Подписи верхней строки) и значений ключевого поля (Значения левого столбца). Флаг Создать связи с исходными данными используется для установления связи с таблицами-источниками, находящимися на других листах/книгах. В этом случае изменение данных в них повлечет автоматическое изменение в консолидирующей таблице. Итоги консолидации тогда должны формироваться на отдельном листе.

СВОДНАЯ ТАБЛИЦА.

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

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

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