30. Обработка информации в списках: сортировка строк и столбцов, использование автофильтра для поиска записей.

Списком называется таблица с данными, разделенная на столб­цы-поля и строки-записи. В сущности, список представляет собой базy данных. Со списком можно выполнять следующие процедуры:

• Использовать список ячеек в качестве базы данных.

• Сортировать записи по одному или нескольким полям.

• Найти важные записи с помощью фильтров.

• Использовать команду Итоги для организации списков.

• Создавать сводные таблицы.

Создание списка в Excel происходит следующим образом:

1. Открыть новую книгу или новый лист в существующей книге.

2. Создать заголовки для каждого поля в списке, затем выров­нять и отформатировать жирным шрифтом.

3. Отформатировать находящиеся под заголовками ячейки в со­ответствии с данными, которые в них должны храниться. Это подразумевает наложение числовых форматов (денежных и да­ты), изменение выражения и т.д.

4. Ввести новые записи (т.е. данные) под заголовками. При этом нужно соблюдать единый стиль заполнения, чтобы позднее взаи­мосвязанные записи могли быть выделены в группы. Количество строк может быть любым, но не более 65 536 записей.

5. Сохранить книгу.

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

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

Сортировка строк и столбцов.

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

Порядок:

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

2.Данные/Сортировка.

3.Вокне диалога Сортировка диапазона указать или установить а)заголовок столбца, по кот. производится сортировка, б)тип сортировки.

4. ОК

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

6. Отмена сортировки осуществляется сразу же после ее проведения командами Правка/Отменить/Сортировка.

Задание пользовательского порядка сортировки.

Создание пользовательского списка сортировки выполняется так:

1.выполнить команду Сервис – Параметры – вкл. Списки

2.выбрать из перечня Списки значение Новый Список, в поле Элементы списка появляется курсор. Здесь следует ввести значения, образующие пользовательский порядок сортировки.

3.ввести значения в нужном порядке для сортировки и нажать кнопку Добавить. Вводить значения через запятую.

4.закрыть окно диалога Параметры кнопкой ОК.

Сортировка в пользовательском порядке.

1.выделить любую ячейку в списке

2.команда Данные – Сортировка. Открывается окно Сортировка диапазона.

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

4. открыть окно диалога Параметры сортировки кнопкой Параметры. В нём имеется раскрывающийся список с пользовательскими порядками сортировки.

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

6.нажать ОК в каждом из окон диалога.

Использование автофильтра для поиска записей.

Иногда требуется временно скрыть все записи в списке кроме тех, кот. удовлетворяют некоторому критерию; для этого применяется команда Данные/Фильтр/Автофильтр. Эта команда помеша­ет в верхней ячейке каждого столбца раскрывающийся список.

Применение команды Автофильтр для поиска записей следую­щее:

1. Выделить любую ячейку в списке.

2. Выполнить команду Даиные/Фильтр/Автофильтр.

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

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

Для отображения нужных записей можно поставить несколько фильтров. Очень удобно для больших списков. Для возвращения на экран всех записей списка выполнить команду Данные/Фильтр/ По­казать всё. Удаление раскрывающихся списков автофильтра производится выключением режима Автофильтр в подменю Фильтр.

Создание пользовательского автофильтра.

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

Процедура создания пользовательского автофильтра следующая:

1. Выделить любую ячейку в списке.

2.Выполнить команду Данные/Фильтр/Автофильтр. В верх­ней ячейке каждого столбца появляется раскрывающийся список.

3. Щелкнуть на стрелке раскрывающегося списка рядом с тем заголовком. который будет использоваться в пользовательском фильтре, и выбрать в меню значение Условие. Открывается окно диалога Пользовательский Автофильтр.

4. Щелкнуть на первом списке с операторами и оказать оператор отношения (=, <, < =, > и т д.), который будет применяться в фильтре , после чего щелкнуть на первом списке со значениями и ввести границу для критерия.

5. При необходимости задания 2-го диапазона установить переключатель И или ИЛИ. Выбрать оператор отношения и границу критерия из 2-го списка значений.

6. Нажать кнопку ОК.

Команда ИТОГИ.

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

Порядок:

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

2.выполнить команду Данные – Итоги. Открывается окно диалога Промежуточные итоги.

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

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

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

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