Список и сортировка данных. Фильтрация данных

 

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

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

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

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

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

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

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

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

В среде Excel предусмотрены три уровня сортировки, которые определяются в диалоговом окне (Сортировка диапазона) параметром <<Сортировать по>>.

 

Таблица 1.4 – Типовые технологические операции по сортировке данных

Название технологической операции Технология выполнения с помощью меню
Сортировка списка 1. Установить курсор в области списка 2. Перейти на панель Сортировка и фильтр, выполнить команду Сортировка 3. Указать порядок и направление сортировки 4. Нажать кнопку Параметры и выбрать необходимые параметры сортировки
Создать новый список для сортировки 1. Перейти на панель Сортировка и фильтр, выполнить команду Сортировка 2.Нажать кнопку Добавить уровень 3. Указать необходимые параметры сортировки

Структурирование таблиц

 

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

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

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

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

§ линии уровней структуры, показывающие соответствующие группы иерархического уровня;

§ кнопка «плюс» - для раскрытия групп структурированной таблицы;

§ кнопка «минус» - для скрытия групп структурированной таблицы;

§ кнопки «номера уровней 1, 2, 3 и т.д.» - для открытия или скрытия соответствующего уровня.

 

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

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

 

Таблица 1.5 – Функции при подведении автоматических итогов по группам

Операция Значение в строке итогов по группе
Сумма Сумма значений
Количество значений Число непустых значений
Среднее Среднее значение в группе
Максимум Наибольшее числовой значение в группе
Минимум Наименьшее числовое значение в группе
Произведение Произведение всех значений в группе
Количество чисел Количество записей или строк, которые содержат числовые данные в группе
Смещенное отклонение Смешная оценка среднего квадратического отклонения генеральной совокупности по выборке данных
Несмещенное отклонение Несмещенная оценка среднего квадратического отклонения генеральной совокупности по выборке данных
Смещенная дисперсия Смещенная оценка дисперсии генеральной совокупности по выборке данных
Несмещенная дисперсия Несмещенная оценка дисперсии генеральной совокупности по выборке данных

 

Создание сводных таблиц

 

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

Чтобы создать сводную таблицу, необходимо перейти на вкладку Вставкаи с панелиТаблицывыполнить командуВставить сводную таблицу. Будет открыто диалоговое окно мастера создания сводных таблиц и диаграмм (рис. 1.8).

 

 

Рисунок 1.8 – Мастер построения сводных таблиц

 

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

 

[имя_книги]имя_листа!диапазон ячеек

 

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

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

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

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

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

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

 

 

Таблица 1.6 – Виды дополнительных функций над полем в области данных