Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности

46

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРАКТИКУМ:

Открыть файл 02_2 Дубликаты.

На листе ЗАДАНИЕ1, ЗАДАНИЕ2, ЗАДАНИЕ3 выполнить соответствующие задания.

Сохранить изменения в файле и закрыть его.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 11.

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

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

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

Сортировка по одному критерию

1.Выделить любую ячейку столбца (не выделять столбец!).

2.На панели инструментов нажмите кнопку – сортировка от А до Я (по возрастанию) или

кнопку – сортировка от я до А (по убыванию).

Существует и другой удобный способ сортировки данных – щелкнуть правой кнопкой мыши по ячейке столбца и в контекстном меню выбрать Сортировка [Sort], далее – нужный вариант.

Многоуровневая сортировка

1.Выделить любую ячейку таблицы.

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

2.На вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter], выбрать Сортировка

[Sort].

3.Указать последовательность сортировки столбцов. Чтобы задать следующий уровень сортировки, следует нажать кнопку Добавить уровень [Add Level].

47

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Для сортировки по дням недели, названиям месяцев в списке поля Порядок выбрать

Настраиваемый список

Сортировка по форматированию

1.Выделить любую ячейку таблицы.

2.На вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter], выбрать

Сортировка [Sort].

3.Выбрать в поле Столбец [Column] поле, по которому будет производиться сортировка.

4.В поле Сортировка [Sort On] выбрать по какому критерию будет сортировка: цвет ячейки (заливка), цвет шрифта или значок ячейки.

5.В зависимости от критерия сортировки в поле Порядок [Order] выбрать цвет или значок ячейки, а так же схему упорядочения Сверху [On Top] или Снизу [On Bottom].

6.Нажать кнопку Копировать уровень [Copy Level], чтобы быстро задать следующий уровень сортировки, изменив в поле Порядок цвет или значок. Повторить нужное количество раз.

7.ОК.

48

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРАКТИКУМ:

Открыть файл 02_3 Сортировка.

На листе ЗАДАНИЕ1, ЗАДАНИЕ2, ЗАДАНИЕ3 выполнить соответствующие задания.

Сохранить сделанные изменения в файле и закрыть его.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 12.

Фильтрация данных

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

Автофильтр – позволяет отбирать записи из базы данных с простыми условиями.

Срезы – интерактивные средства фильтрации данных в таблицах.

Расширенный фильтр – возможность решения задач фильтрации данных со сложными критериями отбора.

Автофильтр

Установка Автофильтра:

1.Указать любую ячейку таблицы;

2.На вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter], выбрать

Фильтр [Filter].

49

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Отбор записей:

1.Раскрыть список фильтрации в нужном столбце строки заголовков таблицы.

2.Выбрать условие фильтрации:

Установить флажки внизу списка для отбора нужных записей;

Фильтр по цвету – выбор по форматированию ячейки: по цвету ячейки, по цвету шрифта и по значку ячейки (если установлено условное форматирование);

Числовые фильтры, Текстовые фильтры, Фильтры по дате – в зависимости от содержимого ячейки;

 Мгновенный поиск;

Первые 10… [Top 10…] – несколько наибольших [Top] или наименьших [Bottom] элементов списка [Items] или % от элементов списка [Percent];

Настраиваемый фильтр… [Custom…] – настраиваемые условия отбора, максимум 2 условия: И [And] – одновременное выполнение 2-х условий, ИЛИ [Or] – выполнение хотя бы одного условия

(Выделить все) [All] или Удалить фильтр из столбца «Имя поля» – снятие условия фильтрации по выбранному столбцу.

Если в отфильтрованные данные были внесены изменения, то для отображения правильного ответа, необходимо повторно применить условия отбора данных – нажать кнопку Повторить [Reapply].

Чтобы удалить все условия отбора, надо на вкладке Данные [Data], в группе

Сортировка и фильтр [Sort&Filter], выбрать Очистить [Clear].

ПРАКТИКУМ:

Открыть файл 02_4 Фильтры.

На листе ЗАДАНИЕ1, ЗАДАНИЕ2 решить соответствующие задания.

Срезы

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

50

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Последовательность действия:

1.Преобразовать исходный диапазон в таблицу (см. тему Создание и ведение таблиц)

2.Выделить любую ячейку таблицы и на вкладке Конструктор [Design] в группе Сервис [Tools] (или на вкладке Вставка [Insert] в группе Фильтры [Filters]), выбрать

Вставить срез [Insert Slicer].

3.Выбрать нужные поля, OK.

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

Срезы можно форматировать – необходимо выделить срез, и на вкладке Параметры [Options] выбрать Стили срезов [Slicer Styles].

В срезе можно выбирать как один, так и несколько элементов (при

использовании клавиш Ctrl и Shift ), при этом в таблице будут

отображаться только отфильтрованные данные по отобранным элементам. Нажатие на кнопку – удаление условий фильтрации.

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

Для удаления среза – щелкнуть по нему правой кнопкой мыши и выбрать Удалить «имя поля»

[Delete «имя поля»] или выделить срез и нажать Delete .

ПРАКТИКУМ:

Открыть файл 02_4 Фильтрация данных.

На листе ЗАДАНИЕ3 выполнить соответствующие задания.

Сохранить сделанные изменения.