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

Сортировка и фильтрация данных

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

• кнопками сортировки на панели инструментов Стандартная: в этом случае сортировка осуществляется в пределах одного поля, в котором находится курсор;
• командой Данные / Сортировка с установкой диапазона сортировки и необходимых параметров.

Сортировка осуществляется на том же листе, где находится исходный список.

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

В среде Calc предусмотрены три уровня сортировки, которые определяются в диалоговом окне Данные / Сортировка параметром Сортировать по. Соответственно сортировка каждого нового уровня зависит от того, как отсортировался уровень предыдущий.

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

• автофильтр: фильтрация по одному критерию;
• стандартный фильтр: фильтрация по нескольким критериям;
• расширенный фильтр: фильтрация по диапазону критериев. Для осуществления фильтрации требуется:

• установить курсор в любой ячейке внутри списка (чтобы программа прочитала все записи в базе данных);
• выбрать команду Данные / Фильтр.

Ход работы

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

1. Загрузите электронные таблицы Calc, используя Пуск / Офис / Электронная таблица.
2. Откройте файл «Работа 3».
3. Перейдите на Лист 2. Переименуйте лист как «Сортировка».
4. Скопируйте с листа Ввод данных всю область данных и вставьте ее на лист Сортировка начиная с ячейки А1.
5. Сортировка по одному полю:

• щелкните в ячейку с фамилией первого студента;
• щелкните на кнопке сортировки Сортировка по возрастанию, расположенной на панели инструментов Стандартная (рис. 1);

Рис. 1. Кнопки сортировки на панели инструментов «Стандартная»

• убедитесь, что программа расположила фамилии в алфавитном порядке;
• аналогичным образом отсортируйте записи по городам в порядке, обратном алфавитному (Сортировка по убыванию).

1. Сортировка по нескольким полям.

Выполните сортировку по нескольким полям, например, по полю Класс по возрастанию (предварительно проверьте, чтобы классы у всех стояли разные. Если это не так, поменяйте классы вручную), затем по полю Средняя успеваемость по убыванию. Для этого:

• выберите команду Данные / Сортировка;
• в диалоговом окне Сортировка в списке Сортировать по выберите поле Класс, установите опцию по возрастанию. В списке Затем по выберите поле Средняя успеваемость, установите опцию по убыванию. Нажмите кнопку ОК (рис. 2).

Рис. 2. Сортировка данных

2. Внимательно проанализируйте перестановку строк в списке и сделайте вывод, почему именно в таком порядке они расположились.
3. Аналогичным образом отсортируйте города по убыванию и номер группы по возрастанию.
4. Сохраните файл.

1. Фильтрация базы данных. Автофильтр: фильтрация по одному критерию.

1. Перейдите на Лист 3. Переименуйте его в «Автофильтр».
2. Скопируйте с листа Ввод данных всю область данных и вставьте ее на лист Автофильтр начиная с ячейки А1.
3. Для того чтобы начать фильтрацию базы данных, щелкните левой кнопкой мыши по любой ячейке внутри списка.
4. Выполните команду Данные /  Автофильтр. LibreOffice Calc проанализирует список и добавит в строку заголовков полей кнопки раскрывающихся списков (кнопки автофильтра) (рис. 3).

Рис. 3. Автофильтр

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

2. Стандартный фильтр: фильтрация по нескольким критериям.

1. Перейдите на Лист 4. Переименуйте лист как «Стандартный фильтр».
2. Скопируйте с листа Ввод данных всю область данных и вставьте ее на лист Стандартный фильтр начиная с ячейки А1.
3. Для того чтобы начать фильтрацию базы данных, щелкните левой кнопкой мыши в любой ячейке внутри списка.
4. Выполните команду Данные / Еще фильтры / Стандартный фильтр. Открывшееся диалоговое окно Стандартный фильтр позволяет фильтровать списки с использованием нескольких критериев. Например, следует отфильтровать всех учеников старше 17 лет, мужского пола. Для этого:

• установите параметры первого критерия в первой строке: в списке

Имя поля выберите Возраст, Условие – >, Значение – 17;

• соедините критерии оператором И;
• укажите параметры второго критерия во второй строке: в списке Имя поля выберите Пол, Условие – =, Значение – Мужской (рис. 4).

Рис. 4. Стандартный фильтр

5. Аналогичным образом проведите фильтрацию по следующим критериям:

• все ученики 9А, 9Б и 11 классов, изучающих математику;
• все студенты, фамилии которых начинаются с буквы В, имеющие среднюю успеваемость выше 3.

3. Расширенный фильтр: фильтрация по диапазону критериев.

1. Перейдите на Лист 5. Переименуйте лист как «Расширенный фильтр».
2. Скопируйте с листа Ввод данных всю область данных и вставьте ее на лист Расширенный фильтр начиная с ячейки А1.
3. Для того чтобы запустить расширенный фильтр, необходимо задать диапазон критериев. Для этого:

• выделите все имена полей базы данных A1:I1;
• скопируйте их (Правка / Копировать);
• щелкните по ячейке К1 и вставьте имена полей (Правка / Вставить).

1. Отфильтруйте всех учащихся 10 класса, имеющих среднюю успеваемость выше 3. Для этого:

• в ячейку О2 под именем поля Класс введите значение 10, а в ячейку S2 под именем Средняя успеваемость введите >3;
• для того чтобы начать фильтрацию базы данных, щелкните левой кнопкой мыши по любой ячейке внутри исходного списка;
• выполните команду Данные / Еще фильтры /Расширенный фильтр. Разверните появившееся окно в полный размер, щелкнув на кнопку .    Задайте в строке Взять условия фильтра из: K1:S2; в строке Поместить результат в: любую свободную ячейку, например, К5 (рис. 5);

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

• выполните фильтрацию, нажав кнопку ОК;
• убедитесь, что начиная с ячейки К5 появились нужные записи.

1. Аналогичным образом отфильтруйте всех учеников начиная с 10 класса, женского пола. Обратите внимание, что из ячеек О2 и  S2  следует удалить значения, оставшиеся от  предыдущего задания,  и внести в ячейки О2 и R2 новые условия: >=10 и Женский. Результат поместите в любую свободную область листа.
2. Сохраните файл.

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

Подведение итогов

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

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

Чтобы вставить в таблицу значения промежуточного итога:

• проверьте наличие подписей к столбцам таблицы;

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

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

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

• в поле Использовать функцию выберите функцию для расчета промежуточных итогов;

• нажмите кнопку OK.

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

Ход работы

1. Перейдите на Лист 6. Переименуйте лист как «Итоги».
2. Скопируйте с листа Ввод данных всю область данных и вставьте ее на лист Итоги начиная с ячейки А1.
3. Проведите сортировку данных по классу в порядке возрастания.
4. Для того чтобы подвести итоги, выделите всю заполненную табличную область и выполните команду Данные / Промежуточные итоги (рис. 1):

• В поле Группировать по укажите столбец Класс. Так как список был отсортирован по полю Класс, то строки с одинаковым классом располагаются непосредственно рядом друг с другом. Как только происходит изменение в столбце Класс, значит, информация об учащихся одного класса закончилась, и далее следуют строки, касающиеся учащихся другого класса.
• В поле Использовать функцию выберите Количество – это функция, с помощью которой будут подводиться итоги (будет вычисляться количество записей в уровне).
• В поле Вычислить промежуточные итоги для укажите столбец Пол – по этим полям будут подводиться итоги.
• Нажмите кнопку ОК.

Рис. 1. Промежуточные итоги

5. Проанализируйте полученные итоги. Исследуйте назначения кнопок управления уровнями, расположенных слева от списка. Сверните и разверните промежуточные уровни, скрывая и раскрывая по очереди разные курсы.
6. Убедитесь, что промежуточные итоги динамически связаны с исходными данными. Внесите какие-либо изменения в ячейки столбца Класс и проанализируйте результат.
7. Перейдите на Лист 7. Переименуйте лист как «Итоги 2». Скопируйте с листа Ввод данных всю область данных и вставьте ее на лист Итоги 2 начиная с ячейки А1.

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

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

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

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

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

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

Для создания сводной таблицы:

• Установите курсор в любой ячейке внутри списка.
• Выберите команду Данные / Сводная таблица / Создать. Появится диалоговое окно Выбор источника. Установите флажок Текущее выделение и подтвердите выбор нажатием кнопки ОК.
• Сформируйте будущую таблицу из заголовков столбцов списка, которые будут отображены в виде кнопок в диалоговом окне Сводная таблица. Создайте сводную таблицу, перетаскивая их в области разметки: Поля страниц, Поля столбцов, Поля строк и Поля данных.

Рассмотрим технологию создания сводной таблицы на примере.

Ход работы

1. Перейдите на Лист 8. Переименуйте лист как «Сводная таблица».
2. Скопируйте с листа Ввод данных всю область данных и вставьте ее на лист Сводная таблица начиная с ячейкиА1.
3. Создайте сводную таблицу, показывающую фамилии учащихся, имеющих максимальный средний балл, в каждом классе и с разбивкой на пол учащихся.

1. Щелкните левой кнопкой мыши по любой ячейке внутри списка.
2. Выполните команду Данные / Сводная таблица / Создать. На данном этапе выдается запрос об источнике исходных данных. Проверьте правильность предложенного программой диапазона и нажмите ОК.
3. В открывшемся диалоговом окне представлены Шаблон сводной таблицы и Список полей сводной таблицы.
4. Раскройте список Источник и назначение и в разделе Назначение выберите Новый лист (сводная таблица будет располагаться на отдельном листе).
5. Сформируйте строки и столбцы будущей сводной таблицы:

• в область Поля страниц левой кнопкой мыши перетащите Изучаемый предмет (область Поля страницы имеет значение страниц, которые можно листать. Эта область как бы добавляет третье измерение в сводную таблицу: раскрывающееся меню, которое располагается над таблицей, позволяет выбирать любые значения этого поля);
• в область Поля строк перетащите Класс;
• в область Поля столбцов перетащите Фамилия и Изучаемый предмет.

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

• в область Поле данных перетащите Средний балл. Обратите внимание, что по умолчанию установится функция Сумма. Щелкните по ней два раза левой кнопкой мыши и выберите функцию Максимум (рис. 1).

Рис. 1. Создание сводной таблицы

2. Для просмотра сводной таблицы нажмите кнопку ОК.

4. Проанализируйте полученную сводную таблицу, которая создана на отдельном листе Сводная таблица_Лист1_1. Измените представление данных в сводной таблице, выбирая различные опции в раскрывающихся списках полей. Выберите из списков Класс, Изучаемый предмет и Фамилия и проанализируйте, как меняются данные в сводной таблице.
5. Убедитесь, что сводная таблица легко обновляется:

4. перейдите на лист Сводная таблица;
5. измените для всех классов максимальную среднюю успеваемость;
6. перейдите на лист Сводная таблица_Лист1_1;
7. выполните команду Данные / Сводная таблица / Обновить;
8. убедитесь, что в сводной таблице также изменился максимальный балл.

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

Объединение данных

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

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

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

Существуют различные варианты объединения данных: по расположению данных для одинаково организованных областей-источников (фиксированное расположение); по категориям для различающихся по своей структуре областей данных.

Технология объединения содержимого ячеек включает следующие этапы:

• Откройте документ, содержащий диапазоны ячеек, которые нужно объединить.
• Выберите команду Данные / Объединить.

–        В открывшемся диалоговом окне Объединить установите курсор в поле Исходный диапазон данных и выделите диапазон при помощи мыши или введите ссылку на первый диапазон исходных данных.

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

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

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

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

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

Ход работы

1. Добавьте еще два рабочих листа, переименуйте лист как «Стипендия_сентябрь» и «Стипендия_октябрь».
2. Скопируйте на лист Стипендия_сентябрь столбец Фамилия с листа Ввод данных. Добавьте еще три столбца – Стипендия, Надбавка, Сумма к выдаче.
3. Введите в столбцы Стипендия и Надбавка данные для каждого студента. Столбец Сумма к выдаче рассчитайте как сумму Стипендия и Надбавка. Для ячеек с числами укажите Формат ячеек–Денежный.
4. Аналогичным образом внесите данные на лист Стипендия_октябрь, скопировав туда фамилии и заполнив три столбца – Стипендия, Надбавка, Сумма к выдаче – новыми значениями.
5. Объедините данные о стипендии за два месяца и расположите результат на листе Стипендия_октябрь, используя объединение по расположению.  Для этого:

11. Выполните команду Данные / Объединить.
12. В открывшемся диалоговом окне Объединить выберите функцию Сумма.
13. Укажите список диапазонов, которые необходимо объединить (учтите, что заголовки столбцов должны быть включены в области-источники): установите курсор в поле Исходный диапазон данных и выделите весь диапазон данных на листе Стипендия_сентябрь начиная с первой ячейки списка. Убедитесь, что в поле появилась правильная ссылка на диапазон.

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

–Щелкните по кнопке ОК.

• Откорректируйте ширину столбцов таблицы так, чтобы был виден текст всех заголовков столбцов.
• Проанализируйте результат.

6. Добавьте еще один лист – Стипендия_ноябрь. Внесите аналогичные данные на группу студентов, но добавьте еще несколько новых фамилий.
7. Объедините данные о стипендии за три месяца и расположите результат на листе Стипендия_ноябрь, используя объединение по категориям.
8. Проанализируйте результат, убедитесь, что в итоговой таблице появились сведения о новых студентах.
9. Сохраните файл.