Тема: Обработка данных, представленных в виде списка - polpoz.ru o_O
Главная
Поиск по ключевым словам:
страница 1
Похожие работы
Тема: Обработка данных, представленных в виде списка - страница №1/1

лабораторная работа №5_3

Тема: Обработка данных, представленных в виде списка

3.1 Краткие теоретические сведения

Для того, чтобы использовать средства сортировка, фильтрация и итоги требуется представить электронную таблицу в виде базы данных или списка.



Список- это одно из средств организации данных на рабочем листе. Список создается как непрерывная прямоугольная область клеток, которая состоит из строк с однотипными данными. Например, перечень работников из примера, приведенного в предыдущей лабораторной работе, в котором столбики имеют соответственно такие имена: № бригады, Фамилия, Сорт, Количество, Начислено, - представляет собой список данных. Данные, организованные в список, в терминологии Ехсеl называются базой данных (БД). При этом строки таблицы - это записи базы данных, а столбцы - поля базы данных.

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

При создании списка на рабочем письме Ехсеl необходимо выполнять такие правила:


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

  2. Следует отделять список от других данных рабочего письма хотя бы одним пустым столбцом или одной пустой строкой. Это поможет Ехсеl автоматически выделить список при выполнении фильтрации или при сортировке данных.

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

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

  5. Ч
    тобы отделить имена столбцов от данных, следует разместить рамку по нижнему краю клеток строки с именами столбцов

Рисунок 3.1 - Пример списка

3.1.1 Сортировка данных

Д
ля быстрой сортировки на панели инструментов Стандартная находятся две кнопки: сортировать по возрастанию; сортировать по убыванию. Ключом сортировки в этом случае является столбец с текущей клеткой.

Рисунок 3.2 – Кнопки сортировки на панели инструментов

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

Команда Сортировка осуществляется также и через диалоговое окно пункта меню Данные/Сортировка. Здесь можно указать сортировку списка по трем полям. В трех полях для ввода окна Сортировка можно задать ключи – имена полей, по которым будет выполнена сортировка. Excel сортирует список по первому выбранному полю, а при совпадении значений в первом поле, записи сортируются по второму выбранному полю. Например, данные в примере можно отсортировать по столбику № бригады. Во втором поле диалогового окна Сортировка можно задать следующий ключ сортировки, например Фамилия. Тогда список будет упорядочен по бригадам, а внутри бригад – по фамилиям (по алфавиту). Действие третьего ключа сортировки аналогично.

3.1.2 Фильтрация данных в списке

С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям. Ехсel разрешает быстро и удобно просматривать необходимые данные из списка с помощью простого средства - автофильтра. Более сложные запросы к базе данных можно реализовать с помощью команды Расширенный фильтр.



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

Например, если выбрать значение № бригады равное 1, то будут выбраны только те сотрудники, которые работают в первой бригаде.

Элемент столбца, который выделен в раскрывающемся списке, называется критерием фильтра. Можно продолжить фильтрацию списка с помощью критерия из другого столбца. Например, если теперь в поле Сорт выбрать значение «Семеренко», то на экран будет выведена только одна строка, как показано на рис. 3.3.

Чтобы удалить критерии фильтра для отдельного столбца, надо выбрать параметр Все в раскрывающемся списке.

Р
исунок 3.3 – Окно с установленным автофильтром

С помощью автофильтра можно для каждого столбца задать нужные критерии отбора записей, например вывести на экран только те записи, значение полей которых находятся в границах заданного интервала. Чтобы задать нужный критерий, надо в раскрывающемся списке выбрать параметр Условие..., а потом в диалоговом окне Пользовательский автофильтр ввести нужные критерии. На рисунке 3.4 показан пример задания условий для поля Количество. Будут выбираться записи о сотрудниках, которые собрали больше 120 кг яблок и меньше 180 кг.



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

О
тличие этой команды от команды Автофильтр состоит в том, что, кроме вышеперечисленных возможностей, отфильтрованные записи можно вынести в другое место рабочего листа Ехсel, не испортив начальный список.

Рисунок 3.4 – Установка условия в окне

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

Рисунок 3.5 – Таблица критериев для расширенного фильтра

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

Рисунок 3.6 – Блок критериев с двойным условием

Кроме таблицы критериев, для команды Расширенный фильтр надо определить блок вывода. Это означает, что следует скопировать в свободное место рабочего листа имена тех полей списка, которые вы хотите видеть в отобранных данных. Например, для таблицы из примера необходимо получить список сотрудников, которым начислена сумма от 20 до 23 гривен. Тогда блок вывода может содержать имена полей Фамилия и Начислено (грн.).

Количество строк в результате Ехсel определит самостоятельно. Таким образом, для выполнения команды Расширенный фильтр надо выполнить три действия:



  • сформировать в свободном месте рабочего листа таблицу критериев (блок критериев);

  • сформировать шапку диапазона результата (блок вывода);

  • выделить область исходного списка. Для примера, который мы рассматриваем, подготовленные для фильтрации таблица критериев и блок вывода, а также список, который фильтруется, приведены на рисунке 3.7.

Рисунок 3.7 – Применение расширенного фильтра

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

И, наконец, задать строку диапазона вывода. В области Обработка надо указать как будет выполняться фильтрация. Если выбран режим - «фильтровать список на месте», то Ехсеl спрячет все строки исходного списка, которые не удовлетворяют заданным критериям.

Если установлен переключатель «Только уникальные записи», то повторяемые строки исходного списка не будут показаны в области результата. Если условия поиска заданы в таблице критериев в одной строке, то эти условия связаны оператором И, если же условия поиска заданы в разных строках, то они связаны оператором ИЛИ.



Использование вычисляемого критерия

Задание вычисляемых критериев требует выполнения правил:



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

  2. Формула должна ссылаться хотя бы на один столбец в списке.

Н
апример, создадим таблицу вычисляемых критериев, по которым ведется поиск тех записей, где размер заработка (поле Начислено (грн.)) превышает среднее значение по всем работникам. Формула для вычисляемого критерия использует функцию СРЗНАЧ и имеет вид:

=Е3>СРЗНАЧ ($Е$3:$Е$9).

Рисунок 3.8 – Использование вычисляемого критерия

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



3.1.3 Работа с функциями базы данных.

Microsoft Excel предлагает 14 функций для работы со списками. Любая из этих функций возвращает информацию об элементах списка, которые удовлетворяют некоторым условиям.

1) Функция СЧЕТЕСЛИ(диапазон; критерий) подсчитывает количество элементов в диапазоне, которые удовлетворяют критерию. Диапазон записывается в виде блока, критерий записывается в виде текстовой строки, которая содержит условия. Например, чтобы посчитать количество работников, которые собрали больше 150 кг яблок, запишите в свободной клетке таблицы формулу: =СЧЕТЕСЛИ(D3:D9;”>150”).

2) Функция



СУММЕСЛИ(диапазон; критерий; диапазон_суммирования)

суммирует значения в указанном диапазоне суммирования, учитывая только те записи, которые удовлетворяют критерию. Например, чтобы посчитать суммарную зарплату работников первой бригады, запишите в свободном месте таблицы формулу: =СУММЕСЛИ(А3:А9;”1”;Е3:Е9).

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

ДСРЗНАЧ(диапазон_списка;поле;блок_критериев)

- вычисляет среднее значение в указанном поле среди записей, которые удовлетворяют условию, записанному в блоке критериев. Например, чтобы вычислить среднюю зарплату работников первой бригады создадим блок критериев в клетках K3:K4, записав в K3 - № бригады, а в K4 - 1. Тогда формула вычислит необходимое.

=ДСРЗНАЧ(A3:E9;”Начислено (грн.)”;K3:K4)

3.2 Задание для самостоятельной работы

Для списка, который вы создали в предшествующей лабораторной работе, выполните такие действия:



  1. Отсортируйте список по нескольким полям всеми возможными средствами.

  2. С помощью команды Данные/Фильтр/Автофильтр сделайте отбор данных, удовлетворяющих нескольким условиям (выберите условия самостоятельно).

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

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

3.3 Контрольные вопросы


  1. Какая часть таблицы может быть списком? Чем список (база данных) отличается от электронной таблицы?

  2. Объясните различие в возможностях сортировки с помощью кнопок панели инструментов и с помощью пункта меню Данные/Сортировка.

  3. Какие возможности предоставляет пользователю опция Автофильтр?

  4. Перечислите правила задания блока критериев для расширенного фильтра. Каковы особенности задания вычисляемого критерия?

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



izumzum.ru