17.Сортировка,фильтрация данных.

• Фильтрация данных

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

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

• Сортировка данных

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

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

Для этого предназначена команда Сортировка меню Данные. В результате на экране появится диалоговое окно.

В этом окне можно задавать вид сортировки: по возрастанию или убыванию. Возможно до трех уровней сортировки.

18.Использование автофильтра.

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

Воспользуйтесь командой меню Данные - Фильтр - Автофильтр.

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

Кроме значений полей каждый список содержит еще три элемента:

• Все - предназначен для восстановления отображения на экране всех записей после применения фильтра

• Первые 10 - обеспечивает представление на экране первых 10 записей фильтра

• Условие - используется для формирования более сложного критерия отбора, в котором можно применять условные операторы отбора И и ИЛИ.

Предположим нас интересуют только в пределах стоимости от 6000 до 10000. Воспользуемся строкой условие. В результате откроется диалоговое окно Пользовательский Автофильтр

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

19.Промежуточные итоги.Консолидация данных.

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

Microsoft Excel может автоматически вычислять промежуточные и общие итоги в списке. При вставке автоматических промежуточных итогов Microsoft Excel

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

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

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

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

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

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

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

Общие итоги.

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

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

Автоматические вычисления.

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

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

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

1. Внешние итоги

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

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

• Итоговые отчеты и диаграммы

Создание итоговых отчетов.

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

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

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

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

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

• Использование трехмерных формул

При использовании в формулах трехмерных ссылок не существует

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

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

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

• Консолидация по расположению

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

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

• Консолидация по категории

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

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

• Другие способы объединения данных

Можно создать отчет сводной таблицы из нескольких диапазонов

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