close

Вход

Забыли?

вход по аккаунту

?

Урок 9. Сортировка и фильтрация.

код для вставки
Excel
Урок 9. Сортировка и фильтрация.
Иногда таблицы могут содержать довольно большое количество данных, которые желательно сортировать по заранее заданным полям и фильтровать (выводить только нужную информацию). Для этого данные надо представлять в виде списка.
Как правило, список состоит из записей (строк) и полей (столбцов). Столбцы должны содержать однотипные данные. Список не должен содержать пустых строк или столбцов. Если в списке присутствуют заголовки, то они должны быть отформатированы другим образом, нежели остальные элементы списка.
Сортировка списков
Сортировка или упорядочивание списков значительно облегчает поиск информации. После сортировки записи отображаются в порядке, определенном значениями столбцов (по алфавиту, по возрастанию/убыванию цены и пр.).
1. Выделите список, на ленте "Главная" нажмите кнопку "Сортировка и фильтр".
2. Если выбрать "Сортировка от А до Я", список будет отсортирован по первому столбцу.
3. Если надо отсортировать список по нескольким полям, то выбираем пункт "Настраиваемая сортировка..". Добавлять поля можно при помощи кнопки "Добавить уровень", определить его сортировку (по возрастанию или убыванию).
4. Уровни можно перемещать, изменяя их значимость, ненужные уровни удаляются
Фильтрация списков
Основное отличие фильтра от упорядочивания - это то, что во время фильтрации записи, не удовлетворяющие условиям отбора, временно скрываются (но не удаляются), в то время, как при сортировке показываются все записи списка, меняется лишь их порядок.
Фильтры бывают двух типов: обычный фильтр (его еще называют автофильтр) и расширенный фильтр.
1. Для применения автофильтра нажмите ту же кнопку, что и при сортировке - "Сортировка и фильтр" и выберите пункт"Фильтр" (перед этим должен быть выделен нужный диапазон ячеек).
2. В столбцах списка появятся кнопки со стрелочками, нажав на которые можно настроить параметры фильтра.
3. Поля, по которым установлен фильтр, отображаются со значком воронки. Если подвести указатель мыши к такой воронке, то будет показано условие фильтрации.
4. Для формирования более сложных условий отбора предназначен пункт "Текстовые фильтры" или "Числовые фильтры".
5. Можно выделить два основных типа фильтров:
a. фильтр по определенному условию или гуппе условий, связанных логическими командами "И", "ИЛИ"
b. Фильтр "Первые 10.." где можно выбрать несколько первых или последних элементов списка (или процент от элементов списка)
c. В текстовых списках данные можно выбирать с помощью маски, например:
"равно ??б* или "равно к*" - выбираются все записи со второй буквой Б и все записи, начинающиеся на К
При использовании расширенного фильтра критерии отбора задаются на рабочем листе. Для этого надо сделать следующее:
1. Скопируйте и вставьте на свободное место шапку списка (поля, по которым не будет условий, можно убрать из шапки списка, а можно вставлять несколько одинаковых полей).
2. В соответствующем поле (полях) под шапкой списка задайте критерии фильтрации.
3. Нажмите кнопку "Дополнительно" на панели "Сортировка и фильтр" ленты "Данные". 4. В появившемся окне "Расширенный фильтр" задайте необходимые диапазоны ячеек (в первой строке диапазон основного списка с заголовками, во второй строке - шапку списка с введенными под ней условиями). В результате данные отфильтруются в основном списке
5. Если надо создать новый список на основе фильтра, выберите "скопировать результат в другое место", в появившейся третьей строке укажите начальную ячейку нового списка и нажмите "Ок"
Задание к уроку 11.
1. создайте зарплатную ведомость (назовите лист "зарплата") согласно примеру:
Расчетная ведомость по заработной плате НАЧИСЛЕНО ЗА март 2011 годаколичество рабочих дней (8 марта - праздник)22№Фамилия и инициалыОтработано днейТариф за 1 день, грнЗа отработанное времяПремияСтажБольничныеНачислено, грн1Иванов А.В.866,67533,36533,36141073,392140,112Петров К.С.1559,50892,50133,8810478,9751505,353Сидоров А.Д.449,16196,64196,64141017,611410,894Кермен П.И.647,06282,36282,361752,961317,685Соловьев Ж.И.1863,351140,30342,094253,41735,796Сисмий А.Р.248,2096,4096,4131108,61301,407Токман Г.С.1035,02350,20350,24420,241120,648Данова К .Т.1285,671028,04154,2116985,2052167,459Жвикова К.И.670,12420,72420,72131290,212131,6510Олейник М.У.1957,781097,82329,3510199,3411626,51a. Количество рабочих дней исчисляется по формуле (вычисляются рабочие дни в указанном диапазоне за вычетом праздников):
=ЧИСТРАБДНИ("01.03.2011";"31.03.2011";"8.03.2011")
b. Порядковый номер (A) - вводим 1 и протягиваем до 10, удерживая Ctrl
c. Фамилия и инициалы (B) - из списка (файл - параметры - изменить списки)
d. Отработано дней (C) - случайное число от 1 до количества рабочих дней (целое):=ОКРУГЛ(СЛЧИС()*($E$2-1)+1;0)
Обратите внимание, что адрес ячейки, где находится количество рабочих дней - абсолютный (он не должен меняться при копировании) - клавиша F4
e. Тариф за день (D) - случайное число от 20 до 100 в грн (два знака после запятой)
f. Сумма за отработанное время (E) = Отработано дней*Тариф за 1 день ( = C4*D4)
g. Премия (F) - если человек отработал более 70% дней -30%, более 50% - 15%, половину рабочего месяца и меньше - премии нет:
=ЕСЛИ(C4>$E$2*70%;E4*30%;ЕСЛИ(C4>$E$2*50%;E4*15%;E4))
h. Стаж (G) - случайное число от 1 до 25 (целое), например: =ОКРУГЛ(СЛЧИС()*24+1;0)
i. Больничные (H) - если стаж менее 10 лет - выплачиваем только тариф за дни болезни, 10 и более - тариф плюс 15% премии (дни болезни вычисляем как разницу Количество рабочих дней - Отработано дней: $E$2-C4)
=ЕСЛИ(G4<10;($E$2-C4)*D4;($E$2-C4)*D4+($E$2-C4)*D4*15%)
j. Начислено, грн (I) = За отработанное время + Премия + Больничные (=СУММ(E7:G7))
2. Введите имя листу "Начислено" и сделайте его копию "Начислено (2)". (Правая кнопка на ярлыке листа - переместить или скопировать.. - создать копию). Далее работаем на копии. Выделите столбцы, где есть случайные числа, и "заморозьте" их, т.е выделите и скопируйте нужный столбец, затем вставьте, используя специальную вставку - "Только значения". Это столбцы C,D и G.
3. Выделите таблицу (без двух заглавных строк) и отсортируйте ее настраиваемой сортировкой. 1 уровень - отработано дней - по возрастанию, 2 уровень - премия - по возрастанию, 3 уровень - больничные - по убыванию
Скопируйте лист и назовите его "1 сортировка"
Автор
sob-mk
sob-mk146   документов Отправить письмо
Документ
Категория
Школьные материалы
Просмотров
346
Размер файла
21 Кб
Теги
урок, фильтрация, excel, сортировка
1/--страниц
Пожаловаться на содержимое документа