Задание 1. В таблице «Доход сотрудников» выполнить сортировку и фильтрацию данных.

Практическая работа

Тема: «Работа со списками в MS EXCEL»

Цель занятия: и зучение информационной технологии органи­зации отбора и сортировки данных в таблицах MS Excel.

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

Работа со списками в Excel

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

Строки списка называют записями, а столбцы – полями. Заголовки столбцов являются именами полей базы данных.

Рекомендации по созданию списков:

Не рекомендуется помещать на рабочий лист более одного списка.

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

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

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

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

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

Разделение окна

При работе с большими списками могут возникнуть неудобства, связанные с тем, что таблица выходит за границы экрана. В этом случае можно разделить экран на области. Например, можно сделать так, чтобы на экране постоянно были видны заголовки столбцов таблицы и первый столбец. Для этого воспользуемся пунктом меню Окно Разделить, перенесем мышью разделительные линии на другое место. Окно разделится на четыре области. У каждой области появятся свои вертикальные и горизонтальные полосы прокрутки. Затем дадим команду Окно закрепить области. Чтобы снять разделение, следует воспользоваться командами: снять закрепление областей, снять разделение.

Разделительные линии можно также установить, пользуясь разделителями

над вертикальной и справа от горизонтальной полос прокрутки – . Перетаскивая их мышью, можно разделить окно по вертикали и/или по горизонтали. Убрать линии можно двойным щелчком мыши.

 

Сортировка

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

При выделении некоторого диапазона ячеек перед вызовом команды сортировки программа сделает запрос: автоматически расширить диапазон или сортировать в пределах указанного выделения. При выборе второго варианта будет нарушен порядок в записях списка. Как правило, следует выбирать вариант автоматического расширения диапазона, чтобы строки таблицы перемещались целиком.

Если требуется отсортировать список по одному столбцу, достаточно сделать текущей любую ячейку в этом столбце и воспользоваться кнопками сортировки на панели инструментов – .

 

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

Автофильтр

Фильтры позволяют показать в таблице только нужные данные, а ненужные– скрыть.

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

Варианты указания критериев отбора:

«Первые 10» – позволяет оставить заданное число (1-500) строк с максимальными или минимальными значениями ячеек текущего столбца.

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

Например, если будет задано условие, представленное на рис. 2.5.21, то в результате фильтрации будут отобраны строки с окладами в интервале от 2000 до 5000. Если же заменить в фильтре связку «И» на «ИЛИ», то изменения исходной таблицы не последует, так как те строки, которые не удовлетворяют первому условию, будут удовлетворять второму и наоборот.

«Пустые» – при выборе данного пункта в результате фильтрации будут оставлены только строки с пустыми ячейками в текущем столбце.

«Непустые» – при выборе данного пункта в результате фильтрации будут оставлены все строки с непустыми ячейками в текущем столбце.

Пункт «Все» позволяет отменить фильтр по данному столбцу.

Чтобы отменить фильтр по всем столбцам, можно воспользоваться пунктом меню Данные Фильтр Отобразить все или отключить «Автофильтр» (Данные Автофильтр).

 

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

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

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

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

 

 

Пример 1. Исходная таблица содержит следующие столбцы: № п/п, Фамилия, Имя, Отчество, Отдел, Оклад.

1.	Оклад	Оклад	2.	Оклад
	>2000	<4000		>2000
				<4000

Если в качестве условия отбора задать первую таблицу, то результатом фильтрации будет список тех, у кого оклад находится в пределах от 2000 до 4000. Если условием отбора является вторая таблица, то результат не будет отличаться от исходного списка, так как в него войдут все, у кого оклад больше 2000, и все у кого оклад меньше 4000.

Пример 2. Исходная таблица та же.

1.	Отдел	2.	Отдел	Отдел
	Бухгалтерия		Бухгалтерия	Сбыт
	Сбыт

Предполагается, что каждый работник числится только в одном отделе. По первому условию отбора мы получим список работающих в бухгалтерии и отделе сбыта. По второму условию мы получим пустой список (так как в одной строке не могут находиться наименования двух отделов).

3.Сделайте активной любую ячейку в исходном списке.

4.Воспользуйтесь пунктом меню Данные Фильтр Расширенный фильтр.

5.Проверьте, правильно ли указан адрес исходного диапазона.

6.Введите в поле «Диапазон критериев» ссылку на диапазон условий отбора, вместе с заголовками столбцов.

7.Чтобы показать результат фильтрации на месте исходного списка, скрыв ненужные строки, установите переключатель «Обработка» в положение «Фильтровать список на месте».

Чтобы скопировать отфильтрованные строки в другую область листа, установите переключатель «Обработка» в положение «Скопировать результаты в другое место», перейдите в поле «Поместить результат в диапазон», а затем укажите первую (верхнюю левую) ячейку области вставки.

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

 

Задание 1. В таблице «Доход сотрудников» выполнить сортировку и фильтрацию данных.

 

Порядок работы

1. Изучите теоретические сведения.

2. Запустите редактор электронных таблиц Microsoft Excel.

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

Исходные данные представлены на рис. 1.

Порядок работы

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

2. Введите значения констант и исходные данные. Форматы данных (денежный или процентный) задайте по образцу задания.

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

Формулы для расчетов:

Подоходный налог = (Оклад - Необлагаемый налогом доход) * % подоходного налога, в ячейку D10 введите формулу = (С10-$С$3)*$С$4;

Отчисления в благотворительный фонд = Оклад * % отчисления в благотворительный фонд, в ячейку ЕЮ введите форму­лу = С10*$С$5;

Всего удержано = Подоходный налог - Отчисления в благотво­рительный фонд, в ячейку F10 введите формулу = D10 + E10; К выдаче = Оклад - Всего удержано, в ячейку G10 введите формулу = C10-F10.

Рис. 6.1. Исходные данные для задания 6.1

4.Переименуйте лист электронной книги, присвоив ему имя «Ваша фамилия_Доход сотрудников».

5. Выполните текущее сохранение файла (Файл/Схранить), дав ему ФАМИЛИЯ_16.04

6. Произведите сортировку по фамилиям сотрудников в алфавитном порядке по возрастанию (выделите блок ячеек B10:G17 без итогов, выберите в меню Данные
команду Сортировка, сортировать поФ.И.О.)

7. Произведите фильтрацию значений дохода, превышающих 1600 р.

 

Рис. 1

 

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

Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «К выдаче»), и вы увидите список всех не­повторяющихся значений этого поля. Выберите команду для фильтрации Условие. В открывшемся окне Пользовательский автофильтр задайте условие — больше 1600

Произойдет отбор данных по заданному условию.

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

Конечный вид таблицы и диаграммы после сортировки и фильтрации представлен на рис. 2.

 

6. Выполните текущее сохранение файла (Файл/Сохранить).

 

Рис. 2.