СРЕДСТВА СТРУКТУРИЗАЦИИ И ПЕРВИЧНОЙ ОБРАБОТКИ ДАННЫХ В MS

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

Строка заголовков полей
Поле
Запись

ПРАВИЛА ФОРМИРОВАНИЯ СПИСКА

ОСНОВНЫЕ ПРИЕМЫ РАБОТЫ СО СПИСКАМИ
Сортировка
Использование фильтров
Агрегирование данных:
Итоги
Сводная таблица
Консолидация
Структура данных

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

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

2. Алфавитно-цифровая сортировка.
При сортировке алфавитно-цифрового текста Excel сравнивает значения посимвольно слева направо.

Текст, в том числе содержащий числа, сортируется в следующем порядке:
числа, пробелы, знаки, буквы латинского алфавита, буквы русского алфавита
0 1 2 3 4 5 6 7 8 9 (пробел) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я
Апострофы (') и дефисы (-) игнорируются
Исключение: если две строки текста одинаковы, не считая дефиса, текст с дефисом ставится в конец.

3. Логические значения - логическое значение ЛОЖЬ ставится перед значением ИСТИНА.
4. Значения ошибки - все значения ошибки равны.
5. Значения Пустые значения - всегда ставятся в конец.

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

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

ФИЛЬТРАЦИЯ ДАННЫХ
Фильтры могут быть использованы только для одного списка на листе.
Чтобы отобрать только записи, соответствующие определенным условиям к списку можно применить Автофильтр или Расширенный фильтр.
АВТОФИЛЬТР
Установить курсорную рамку в любой ячейке списка.
Выполнить Данные, Фильтр, Автофильтр.
В строке заголовков появятся кнопки с раскрывающимися списками элементов выбранного поля.

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

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

РАСШИРЕННЫЙ ФИЛЬТР
Расширенный фильтр применяется для фильтрации списка со сложными критериями отбора, включая вычисляемые критерии (с использование функций и формул)
Для работы с расширенным фильтром нужно сформировать диапазон критериев (условий), который можно разместить в любом месте текущего листа, на другом листе книги или даже в другой книге.
Удобно размещать диапазон условий выше списка, отделяя его от списка пустой строкой

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

АЛГОРИТМ ПРИМЕНЕНИЯ РАСШИРЕННОГО ФИЛЬТРА
Установить курсорную рамку в любую ячейку в списке.
Выполнить Данные, Фильтр, Расширенный фильтр.
В окне диалога в соответствующие поля ввести ссылки на исходный диапазон и диапазон условий.
В диапазоне условий недолжно быть пустых строк
Значения условий, размещенные в одной строке
объединяются логическим оператором «И»,
в смежных строках – «ИЛИ»
По умолчанию результаты фильтрации отображаются в диапазоне исходного списка.

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

Примеры запросов
Условие поиска с использованием оператора «И»
Данные в поля запроса вносят в одну строку, непосредственно под заголовками полей
Условие поиска с использованием оператора «Или»
Данные в поля запроса вводят в смежные строки

Поиск уникальных данных в списке
Контекстный поиск: под заголовком поля вводится формула ="=текст"
Поиск данных, например, какой-либо суммы в некотором диапазоне
Можно использовать в запросе операторы сравнения.
Пример1
запрос: отобрать все записи по полю К выплате, превышающие 1000

Пример2
запрос: отобрать все записи по полю К выплате до 1000;
Пример 3
запрос: отобрать все записи по полю К выплате в диапазоне от 1000 до 2000
Пример 4
запрос: отобрать все записи по полю Фамилия Имя, по алфавиту после буквы Л

Пример 5
запрос: отобрать все записи по полю Фамилия Имя,
по алфавиту после буквы Л и по полю К выплате с суммой 646
Пример 6
запрос: отобразить перечисленные записи по полю Фамилия Имя, для которых по полю Начислено выполняется условие >700

СОЗДАНИЕ ЗАПРОСА С ВЫЧИСЛЯЕМЫМ КРИТЕРИЕМ
1. Заголовок над вычисляемым условием должен отличаться от заголовков полей списка (он может быть пустым или содержать произвольный текст)
2. Ссылки на ячейки, находящиеся вне списка должны быть абсолютными
3. Ссылки на ячейки списка должны быть относительными
При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона в ячейке будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

Например, нужно отфильтровать из списка фамилии работников, начисления у которых выше среднего:
1. В отдельной ячейке записать формулу, вычисления среднего значения по полю Начислено, например, в F3
2. В другую ячейку, например, С3, ввести формулу, сравнивающую значение в первой ячейке диапазона с вычисленным в F3 средним значением.
В формуле ссылка на F3 должна быть абсолютной, а на первой ячейку диапазона - относительной

3. Установить курсорную рамку в первой ячейке диапазона Начислено и выполнить Данные, Фильтр, Расширенный фильтр

АГРЕГИРОВАНИЕ ДАННЫХ
Агрегирование данных в Excel выполняется для списков, в записях которых имеются поля с повторяющимися значениями
Создание промежуточных итогов, обобщающих данные
2. Сводные таблицы
3. Консолидация данных
4. Структурирование таблицы

АЛГОРИТМ СОЗДАНИЯ ИТОГОВ
Дан журнал отчетов о выручке филиалов по месяцам.
Шаг 1.
Отсортировать список по столбцу, для которого необходимо подвести промежуточный итоги.

Выполнить сортировку для столбца Филиал
Команда Данные, Сортировка

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

Указать ячейку в списке и выполнить Данные, Итоги.
Слева появляется служебное поле с элементами структуры

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

Для отображения списка в обычном виде, т.е. для отключения режима Итоги
Установить курсорную рамку в поле списка
Выполнить Данные, Итоги
Нажать кнопку Убрать все

СВОДНЫЕ ТАБЛИЦЫ
Формирование массива агрегированной информации и представление данных в структурированном виде, а также построение связанной со сводной таблицей диаграммы.
Для формирования сводной таблицы можно использовать данные, которые хранятся в списке Excel, файлы, содержащие табличные данные, другие сводные таблицы, массивы консолидированных данных

Формирование сводной таблицы с помощью пошагового руководства - Мастер сводных таблиц
Шаг 1. Выбор источника
- В списке или базе данных Microsoft Excel – если данные берутся с одного рабочего листа
- Во внешнем источнике данных - если данные берутся из внешней базы данных
- В нескольких диапазонах консолидации - если данные берутся с нескольких рабочих листов
- В другой сводной таблице – если сводная таблица создается на основе другой сводной таблицы

Шаг 2. Выбор диапазона данных
Указать ссылку на диапазон
Шаг 3. Формирование макета (структуры) сводной таблицы.
Поля базы данных, на основе которой строится сводная таблица, представлены в виде кнопок с названиями этих полей.
Для формирования структуры
кнопки
перетаскивают
в соответствующие
области

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

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

...
…
…
Пример. Создать сводную таблицу для базы данных

Установите курсор в любой ячейке списка и выполните Данные, Сводная таблица

2. Укажите ссылку на диапазон списка
3. Укажите где вы хотите разместить сводную таблицу
4. Нажмите кнопку Макет

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

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

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

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

КОНСОЛИДАЦИЯ ДАННЫХ
Агрегирование данных с помощью выбранной функции обработки данных, представленных в областях-источниках
(т.е. особый способ вычисления итогов для определенных диапазонов ячеек)
С помощью команды Консолидация можно объединять информацию с исходных листов (до 255) в одном итоговом.
Исходные листы м.б. расположены в одной книге с итоговым листом или в других книгах.

Можно консолидировать листы из открытых книг и закрытых книг, находящихся на диске (т.е. книги предварительно д.б. сохранены)
Важно правильно указать путь к файлу. =[имя книги]имя листа!ссылка на диапазон

Способы консолидации данных:
1. По расположению – для одинаково организованных листов (фиксированное расположение).
2. По категориям – для различающиеся по расположению данных.
3. Консолидация внешних данных – нажать кнопку Обзор в окне диалога Консолидация и указать файл и ссылку на ячейку или указать имя блока ячеек.

Все источники имеют одинаковое расположение данных источников
Это позволяет использовать ссылки на файлы и ячейки для консолидированной таблицы (метки категорий данных в выделяемые источники не включаются)
2. Данные имеют одинаковую структуру
Фиксированное расположение ячеек и данные
могут быть консолидированы с определенной функцией обработки (среднее значение, максимальное, и т.д.)
КОНСОЛИДАЦИЮ ПО РАСПОЛОЖЕНИЮ

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

Области источники содержат однотипные данные, но в различных областях-источниках организованы не одинаково
Например,
Табель учета рабочего времени для нескольких бригад (разные списки работников) или диапазон данных какого либо из табелей, например, смещен вниз на несколько строк (т.е. имеет другой адрес)
КОНСОЛИДАЦИЯ ПО КАТЕГОРИИ

Для выполнения операции Консолидация
1. Курсор установить в область местоназначения
2. Выполнить Данные, Консолидация
3. В окне диалога выбрать задать условия консолидации
При консолидации по категориям указать метки

СТРУКТУРИРОВАНИЕ ТАБЛИЦ
Применяется для работы с большими таблицами, если есть необходимость закрывать и открывать отдельные строки таблицы

Для создания иерархической структуры большой таблицы выполнить:
Сортировку списка по нужной классификации
2. Вставить пустые строки для разделенных групп.
3. Выделить первую группу, выполнить Данные, Группа и структура, Группировать
Аналогичные действия выполнить для других групп. Слева появится значок «–». При щелчке на этот значок данные открываются.