Обработка информации в списках. Сортировка строк и столбцов, создание пользовательского порядка сортировки, использование автофильтра для поиска записей
7, 8 Обработка информации в списках. Сортировка строк и столбцов, создание пользовательского порядка сортировки, использование автофильтра для поиска записей.
Списком называется таблица с данными, разделенная на столбцы-поля и строки-записи. В сущности, список представляет собой базу данных. Со списком можно выполнять следующие процедуры:
• Использовать список ячеек в качестве базы данных.
• Сортировать записи по одному или нескольким полям.
• Найти важные записи с помощью фильтров.
• Создавать сводные таблицы.
Создание списка в 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. В верхней ячейке каждого столбца появляется раскрывающийся список. Щелкнуть мышкой стрелку списка в том столбце, который используется для сбора записей. Появляется список возможных вариантов фильтрации.
Excel скрывает все записи, не удовлетворяющие указанному критерию, и выделяет стрелку активного фильтра.
Для отображения НУЖНЫХ записей можно поставить несколько фильтров. Очень удобно для больших списков. Для возвращения на экран всех записей списка выполнить команду Данные/Фильтр/ Показать всё. Удаление раскрывающихся списков автофильтра производится выключением режима Автофильтр в подменю Фильтр.
Создание пользовательского автофильтра.
При возникновении необходимости в отборе записей по диапазону числовых значений или иной настройке критерия следует выбрать значение Условие из раскрывающегося списка Автофильтра. Откроется окно диалога Пользовательский автофильтр. В окне имеются два раскрывающихся списка с операторами отношения и еще два, служащие для создания нестандартных диапазонов в фильтрах.
Процедура создания пользовательского автофильтра следующая:
1. Выделить любую ячейку в списке.
2.Выполнить команду Данные/Фильтр/Автофильтр. В верхней ячейке каждого столбца появляется раскрывающийся список.
3. Щелкнуть на стрелке раскрывающегося списка рядом с тем заголовком, который будет использоваться в пользовательском фильтре, и выбрать в меню значение Условие. Открывается окно диалога Пользовательский Автофильтр.
4. Щелкнуть на первом списке с операторами и оказать оператор отношения (=, <, < =, > и т д.), который будет применяться в фильтре , после чего щелкнуть на первом списке со значениями и ввести границу для критерия.
5. При необходимости задания 2-го диапазона установить переключатель И или ИЛИ. Выбрать оператор отношения и границу критерия из 2-го списка значений.
6. Нажать кнопку ОК.
Создание сводных таблиц
Наиболее мощным средством обработки данных в Excel является команда Сводная таблица. В результате её выполнения создастся на-страиваемая таблица для организации полей на - тете в новых сочетаниях. Методика сводных таблиц позволяет превратить строки в столбцы и наоборот, а также решать многие другие задачи.
Сводная таблица создается с помощью Мастера сводных таблиц, который следит за вводом информации, помогает производить вычисления и форматировать таблицу. Сводная таблица создается следующим образом:
1. Выделить любую ячейку в списке, который будет рассматриваться с помощью сводной таблицы.
2. Выполнить команду Данные/Сводная таблица. Появится окно диалога Мастер сводных таблиц, в котором следует указать источник данных для таблицы. Для этого установить переключатель В списке или базе данных Microsoft Excel и нажать кнопку Далее.
3. Поскольку список находился на экране в момент запуска Мастера, Excel выделит его, иначе его следует выделить мышью.
4. Нажать кнопку Далее: в следующем окне необходимо указать расположение полей в сводной таблице. Поля перечислены на подвижных кнопках в правой части окна.
5. Определить исходную структуру сводной таблицы перетаскиванием кнопок с полями в области Строка и Столбец, Страница и Данные. Поля в области Данные будут просуммированы функцией СУММ. Область Страница предназначена для полей, которые необходимо просмотреть более внимательно.
6. Нажать кнопку Далее - открывается последнее окно Мастера сводных таблиц. По умолчанию Excel создает сводную таблицу на новом листе, однако, можно выбрать уже существующий лист и даже указать точное расположение ячеек. Затем нажать кнопку Готово. В книге появится сводная таблица.