Технологии анализа данных, представленных таблично

Цель: научиться работать со списками, расширенными фильтрами, пользоваться различными методами сортировки данных.

 

Теоретическое введение

 

ОРГАНИЗАЦИЯ МОДЕЛИ ДАННЫХ В ВИДЕ СПИСКОВ

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

Эффективная работа со списками возможна при выполнении некоторых правил:

– одна или две верхние строки должны содержать заголовки, раскрывающие смысл расположенного ниже столбца;

– каждый столбец должен содержать информацию одного типа;

– в списках не должно быть пустых строк и столбцов;

– если нет возможности расположить список на отдельном листе, то нужно отделить его как минимум одним пустым столбцом и одной пустой строкой;

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

На рисунке 3.1 в качестве примера приведен фрагмент списка, в котором учитываются операции банка по счетам клиентов.

ПРИМЕНЕНИЕ ФОРМЫ ПРИ РАБОТЕ СО СПИСКАМИ

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

Рис.3.1 Пример списка в Excel

 

Для того чтобы включить Форму, нужно выполнить операции:

– установить курсор в любую ячейку таблицы (списка);

– выполнить команду меню Данные/Форма, которая включает форму для вывода или редактирования (рис. 3.2).

Рис. 3.2 Диалоговое окно Форма

 

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

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

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

Для удаления строки из списка нужно перейти к ней и щелкнуть на кнопке Удалить.

Применяя форму, можно быстро найти нужную строку в списке. Для этого нужно выполнить операции:

– щелкнуть на кнопке формы Критерии;

– в поля формы, значения которых являются условиями для поиска, ввести соответствующие данные (рис. 3.3);

– щелкать на кнопке Далее (Назад) - в полях формы будут отображаться данные списка, удовлетворяющие условиям поиска.

Рис. 3.3 Поиск строки в списке по заданному критерию

 

АНАЛИЗ ДАННЫХ НА ОСНОВЕ ИХ СОРТИРОВКИ

Сортировка строк списка

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

Чтобы сортировать список, нужно выполнить операции:

– установить курсор в любую ячейку списка;

– выполнить команду меню Данные/Сортировка. Табличный процессор предварительно определит размера списка, определит строку заголовка, исключаемую из сортировки, и выведет на экран диалоговое окно Сортировка диапазона (рис. 3.4):

– выбрать в списках окна Сортировка диапазонов нужные названия столбцов списка и установить соответствующие переключатели, определяющие порядок сортировки;

– щелкнуть на кнопке ОК - список будет отсортирован в соответствии с заданными параметрами;

– приведения списка в исходное состояние нужно выполнить команду меню Правка/Отменить/Сортировка.

Рис. 3.4 Диалоговое окно Сортировка диапазона

 

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

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

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

Сортировка строк диапазона

Для сортировки не всего списка, а только его части нужно выполнить операции:

– выделить диапазон, который нужно сортировать;

– выполнить команду меню Данные/Сортировка - откроется диалоговое окно Сортировка диапазона (рис. 3.5);

– установить нужные параметры сортировки и щелкнуть на кнопке ОК.

Рис. 3.5 Сортировка части списка

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

При решении некоторых задач бывает необходимо сортировать список не по строкам, а по столбцам. Чтобы сортировать список по столбцам, следует выполнить операции:

– в диалоговом окне Сортировка диапазона щелкнуть на кнопке Параметры. Откроется диалоговое окно Параметры сортировки (рис. 3.6);

– в диалоговом окне Параметры сортировки установить переключатель Столбцы диапазона и щелкнуть на кнопке ОК;

– в диалоговом окне Сортировка диапазона в качестве ключей сортировки указать соответствующие строки (рис. 3.7).

Рис. 3.6 Диалоговое окно Параметры сортировки

 

Рис. 3.7 Сортировка списка по столбцам