Сортировка табличных данных, выбор по критерию, задание фильтров, получение итогов

Одним из способов организации данных на рабочем столе является представление данных в виде списка. Список рассматривают как таб­личную базу данных.

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

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

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

Сортировка табличных данных позволяет переупорядочить строки в таблице по любому полю.

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

В поле списка Сортировать по выбирается поле, по которому бу­дут отсортированы данные, и тип сортировки: по возрастанию - циф­ры сортируются по возрастанию, текст - в алфавитном порядке, логические выражения - ЛОЖЬ предшествует ИСТИНА; по убыванию - сортировка в обратном порядке.

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

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

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

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

Поиск и фильтрация данных позволяет выводить на экран и про­сматривать данные списка, которые удовлетворяют определенным условиям (критериям).

Excel поддерживает несколько типов критериев поиска:

■ Поиск точного соответствия. Позволяет находить точные соот­ветствия данных. В критериях на основе сравнения можно исполь­зовать различные операции сравнения: =, >, >=, <, <=, <>. Математические вычисления и логические операции не используются. Данный критерий ориентирован преимущественно на работу с чи­словыми данными.

■ Поиск близкого соответствия с использованием образца. По­зволяет находить данные по соответствию некоторому шаблону Используются символы шаблона * и?. Символ * заменяет любое количество любых символов, символ? - один любой символ. Дан­ный критерий ориентирован на работу с текстом либо с числами отформатированными как текст.

■ Поиск соответствия с использованием множественного крите­рия с операциями И и ИЛИ. Позволяет объединить несколько ус­ловий с помощью логических операций И, ИЛИ.

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

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

Фильтрация данных может выполняться с помощью:

■ автофильтра;

■ расширенного фильтра.

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

Для того чтобы применить средство автофильтрации, следует:

1. Выделить область списка.

2. Выполнить команду Данные - Фильтр - Дополнительно.

3. После выполнения команды список будет преобразован следую­щим образом: изменится вид верхней строки списка - в каждом за­головке появится кнопка раскрывающегося списка.

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

Автофильтр позволяет задать несколько вариантов условия |- фильтрации по полю:

§ Все - фильтрация по полю отключена, выводятся все записи.

§ Первые 10 - отбор заданного числа (по умолчанию 10) первых или последних записей.

§ Условие - вывод на экран записей по условию, заданному в диалоговом окне Пользовательский Автофильтр.

§ Можно выбирать Точное значение - вывод на экран записей, поля которых в точности совпадают с выбранным значением.

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

Диапазон условий позволяет произвести фильтрацию при более сложных условиях отбора:

§ условиях (соединенных логическим оператором ИЛИ) для несколь­ких столбцов;

§ |трех и более условиях для конкретного столбца;

§ вычисляемых условиях.

Для того, чтобы применить средство расширенного фильтра, нужно:

1. Выделить область списка.

2. Выполнить команду Данные - Фильтр - Расширенный фильтр.

3. Диалоговое окно Расширенный фильтр заполнить следующим образом:

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

§ в поле Исходный диапазон указать интервал ячеек, содержа­щий область списка;

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

§ в поле Поместить результат в диапазон задать интервал яче­ек, где предполагается разместить отфильтрованные записи;

§ для того чтобы повторяющие строки списка в области выход­ного документа не отображались, следует установить пере­ключатель Только уникальные записи.

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

1. Отсортировать список по столбцу, для которого необходимо вы­числить итоги.

2. Выделить область списка.

3. Выполнить команду Данные - Итоги.

4. Заполнить открывшееся диалоговое окно Промежуточные итоги следующим образом:

■ из списка При каждом изменении в выбрать столбец (по нему проводилась сортировка), по которому необходимо подвести итоги;

■ из списка Операция выбрать функцию, нужную для вычисле­ния итогов. Для подведения итогов автоматически могут быть вычислены следующие функции: Сумма, Количество значе­ний, Среднее, Максимум, Минимум, Произведение, Количест­во чисел, Смещенное отклонение, Несмещенное отклоне­ние, Смещенная дисперсия, Несмещенная дисперсия;

■ из списка Добавить итоги по указать, по какому (каким) полю (полям) следует подводить итоги;

■ установка флажка Заменить текущие итоги приведет к тому, что итоги, полученные ранее, будут заменены новыми. Если флажок сброшен, то каждый раз к предыдущим итогам будут добавляться новые;

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

■ если установить флажок Итоги под данными, то итоги будут располагаться под данными, а если флажок сброшен - то над данными.

Чтобы убрать все итоги, используется кнопка Убрать все.

Порядок выполнения задания

1. Запустите программу Excel и создайте электронную таблицу:

Анализ себестоимости производства зерна по статьям затрат

Статьи затрат	Затраты на 1т продукции, тыс. руб.	Структура затрат, %
План	Факт	+,-	План	Факт	+,-
Заработная плата
Отчисления в фонд социальной защиты		45,2
Удобрения
Семена и посадочный материал
Средства защиты растений		31,5
Итого:	*	*	*			*

* - Обозначает необходимость подсчета итогов по столбцам

Графа 4 = Гр.3 – Гр.2

Графа 5,6 = Структура затрат - это отношение части затрат к общей сумме

(например, Зар. плата по плану разделить на Итого по плану и т.д.)…

Графа 7 = Гр.6 – Гр.5

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

3. Скопируйте формулы на все другие ячейки в столбцах, для чего необхо­димо:

• выделить мышью ячейку с введенными формулами,

• «зацепить» мышью за черный прямоугольник в правом нижнем углу зоны выделенных ячеек (указатель мыши должен превратиться в крестик) и «протянуть» (т.е. нажать левую клавишу и не отпуская ее передвинуть указатель мыши) на все оставшиеся ячейки в столбцах. После этого программа Excel скопирует и пересчитает результаты, рассчитанные по аналогичным формулам, но соответствующим уже новым адресам ячеек в новых строках.

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

• МАКС;

• МИН;

• СРЗНАЧ;

• ЧИСЛО.

5. Произведите вычисления в добавленных строках.

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

7. Добавьте два листа в книгу.

8. Удалите лишние листы так, чтобы в книге осталось два листа.

9. Переименуйте первый лист

10. Выполните сортировку и фильтрацию данных

11. Запишите файл с оформленной электронной таблицей на диск.

12. Выполните индивидуальное задание, предложенное преподавателем.

9. Завершите работу с программой.

Выходной контроль:

1. Как удалять и вставлять строки и столбцы?

2. Как копировать и перемещать данные?

3. Как изменять ширину строк и столбцов?

4. Как выбрать форму представления данных?

5. Как отличить число и текст по способу выравнивания в ячейке?

6. С какого символа начинается ввод формулы?

7. Как выделить диапазон ячеек?

8. Как скопировать формулу?

9. Что такое абсолютная адресация ячейки?

10. Как выполнить сортировку данных?

11. Как выполняется фильтрация данных?

12. Как закрепить заголовки таблицы?

13. Как скрыть отображение строки или столбца?