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

 

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

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

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

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

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

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

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

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

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

 

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

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

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

 

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

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

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

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

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

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

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

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

 

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

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

 

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

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

 

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

 

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

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

 

 

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

 

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

 

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

 

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

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

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

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

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

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

 

 

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

Функция	Результат
Отличие	Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках поле и элемент
Доля	Значения ячеек области данных отображаются в процентах к заданному элементу, указанному в списках поле и элемент
Приведенное отличие	Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках поле и элемент, нормированной к значению этого элемента
С нарастающим итогом в поле	Значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Следует выбрать поле, элементы которого будут отображаться в нарастающем итоге
Доля от суммы по строке	Значения ячеек области данных отображаются в процентах от итога строки
Доля от суммы по столбцу	Значения ячеек области данных отображаются в процентах от итога столбца
Доля от общей суммы	Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы
Индекс	При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог)) / ((Итог строки) * (Итог столбца))