Обработка списков в Microsoft Excel: сортировка, фильтрация, создание итоговых таблиц. Списки и базы данных Microsoft Excel

 

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

– список целиком помещается на одном рабочем листе, максимальный размер списка – 65 536 строк и 256 столбцов;

 

– список должен оделяться от других данных рабочего листа хотя бы одним незаполненным столб-цом и/или строкой;

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

 

– ячейки одного столбца списка содержат однородную информацию.

Традиционные операции со списками:

 

– ввод и редактирование данных списка с помощью экранной формы;

 

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

– фильтрация (отбор) записей списка по условиям;

 

– агрегирование информации списка, вычисление статистических оценок (сводные таблицы, проме-жуточные итоги);

– структурирование данных (формирование группировок строк и столбцов);

– консолидация (объединение данных нескольких списков с формированием общих итогов).

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

 

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

 

Примечание. Если списокMicrosoft Excelотделяется от прочей информации хотя бы одной пустойстрокой или пустым столбцом, достаточно установить курсор в любую ячейку списка перед выполне-нием команды сортировки.

 

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

1. числа;

2. текст;

3. логические значения;

4. значения ошибок;

5. пустые ячейки.

Сортировка по одному ключу.

 

Для выполнения простой сортировки (сортировки по одному ключу), необходимо:

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

2. нажать кнопку на Стандартной панели инструментов Сортировка по возрастанию (или

Сортировка по убыванию).

Сложная сортировка.

При выполнении сложной сортировки, т.е. по двум, трем можно и по одному ключу с установкой до-полнительных параметров, нужно выполнить команду меню Данные► Сортировка и ввести парамет-ры сортировки в окно диалога Сортировка диапазона. Блок ячеек списка всегда включает имена полей, поэтому нужно указать Идентифицировать поля по подписям. Выбираются ключи сортировки и на-правление сортировки – по возрастанию или убыванию значений этих ключей. Кнопка < Параметры> обеспечивает дополнительную настройку для выполнения операции сортировки списка:

– выбор способа сортировки по первому ключу;

– различие прописных и строчных букв;

– тип сортировки таблицы – по строкам или по столбцам.

Фильтрация списков

Различают два способа фильтрации (отбора) записей списка в команде меню Данные► Фильтр:

Автофильтр и Расширенный фильтр.

Автофильтр.

Автофильтр создает для каждого поля список значений, используемый для отбора записей: Все –нет ограничений на значения поля;

 

Первые 10 –вывод установленного числа или процент записей с наибольшими/наименьшимизначениями поля;

Условие –формирование условий отбора в Пользовательском автофильтре; Конкретное значение –отбор записей с указанным значением поля; Пустые –в поле пусто(нет значений); Непустые –в поле содержится какое-либо значение.

Условие для первого поля формирует записи списка Microsoft Excel. Условие для второго поля фильтрует результат предыдущей фильтрации и т.д. Все условия отбора связаны логической связкой И. Если условие задается по одному полю то, с помощью пользовательского автофильтра условия могут быть связаны как связкой И, так и связкой ИЛИ. Для задания условий отбора могут использоваться шаблоны – заменители отдельных символов –? или всех символов – *.

 

В списке в результате фильтрации остаются видимыми лишь записи, удовлетворяющие заданным условиям отбора. Отобранные записи можно копировать, удалять или редактировать. Для сброса всех

 

условий фильтрации служит команда меню Данные► Фильтр► Показать все. Для отказа от автофильтра повторно выполняется команда меню Данные► Фильтр► Автофильтр.

Расширенный фильтр.

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

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

 

Команда меню Данные► Фильтр► Расширенный фильтр выводит окно диалога для указания диапазона ячеек: исходного диапазона – списка Microsoft Excel, диапазона условий, диапазона ячеекдля вывода результата фильтрации.

 

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

Существуют два способа фильтрации записей списка с использованием расширенного фильтра Microsoft Excel: фильтровать список на месте и копировать рузультат фильтрации в новое место.

⇐ Предыдущая6789101112131415Следующая ⇒

Поделиться: