Автофильтр

Списки

Создать таблицу по образцу и сделать в ней 4 записи

В столбце «отделы» присвоить записям любой из 4-х: ФИН, КАД, СТАТ, ЮР

Сохранить файл под именем Списки_Фамилия в папке Документы и скопировать на сетевой диск в папку своей группы.

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

Упражнение 1. Осуществите подгонку ширины столбцов (поставить курсор внутри таблицы с данными – клавиши Ctrl+*, команда Главная/Формат/ Авто­подбор ширины столбца). Выровняйте также высоту строк.

Закрепление "шапки" и "боковика" таблицы. Просматривать будет таблицу неудобно, если она содержит записи непомещающиеся на экране: если перейти к последним записям, то с эк­рана исчезают заголовки столбцов; если хотим посмотреть те­лефоны, то пропадают фамилии. Нужно, чтобы заголовки столбцов (1-я строка) и фамилии (столбец А) постоянно присут­ствовали на экране. Для этого выберите в меню команду Вид/ Разделить (на рабочем листе появляются горизонтальная и вертикальная разделительные полосы), перемещаем мышью полосы так, чтобы ими были отделены первая строка и столбец А. Команда Закрепить области – Закрепить области.

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

Чтобы убрать закрепление выберите Закрепить области – Снять закрепление областей, отменить команду Разделить.

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

Упражнение 2. Начисление премии. Для иллюстрации некоторых важных моментов работы со списками решим уже знакомую задачу: на­числим каждому работнику премию в размере 10% оклада (при этом оставим возможность изменения премии).

Вставим две пустые строки перед таблицей. В ячейку F1 введите слово "премия", в G1 — 10%. Мы специально оста­вили вторую строку пустой, чтобы список был ограничен пус­тыми ячейками. После столбца " ок­лад " добавьте два пустых столбца, дайте заголовки "Премия" и "Всего" и заполните столбцы соответствующими формулами.

Сохраните рабочую книгу (клавиши Ctrl+S), закройте ее (клавиши Ctrl+F4) и заново откройте (клавиши Ctrl+O).

Упражнение 3. Сортировка по одному ключу. Для списков Excel ключ — это поле. Отсортируйте список по полю "Пол" по возрастанию. В меню Данные/ Сортировка в диалоговом окне укажите в первом поле ввода "Сортировать по" поле «пол» (выберите это поле в выпадающем списке) и оставьте сортировку значения. Сначала в списке будут фигурировать женщины, затем мужчины, так как по алфавиту буква "ж" предшествует букве "м".

Заново отсортируйте список, но чтобы на этот раз мужчины предшествовали женщинам (по убыванию, то есть от А до Я). Теперь отсортируйте список по полю "OTДЕЛ". Сотрудники будут упорядочены по отделам в алфавитном порядке их аббре­виатур: КАД, СТАТ, ФИН, ЮР.

Упражнение 4. Сортировка по двум или трем ключам. Отсортируйте спи­сок по полю «пол» по убыванию и по полю «отдел» по возрастанию. Затем выполните сортировку сначала по номеру отдела по возрастанию и по полу по убыванию. Результаты бу­дут различными. При постоянном значении первого ключа из­меняется второй ключ.

Результат сортировки скопируйте на лист 2.

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

Упражнение 5. Сортировка по четырем ключам. Н ужно отсортировать список в таком порядке: пол, отдел, оклад, фамилии (все по возрастанию). Для этого добавляйте уровни сортировки в окне Сортировка. Результат сортировки скопируйте на лист 4.

Упражнение 6.1. Отсортируйте список по отделам, внутри отделов по возрастанию табельных номеров.

Упражнение 6.2. Отсортируйте по отделам, внутри отдела поместите сначала мужчин, потом женщин, их, в свою очередь, упорядочите по убыванию количества детей, а для одинакового количества детей по алфавитному порядку фамилий. Результаты сортировки упр 6.1 и 6.2 скопируйте на лист 5 и 6. Восстано­вите первоначальный порядок записей сортировкой по столбцу А на первом листе.

Автофильтр

Отфильтровать список — показать только те записи, кото­рые удовлетворяют заданному критерию.

Отбор по одному полю. Покажем строки с информацией о сотрудниках отдела СТАТ. Выделяем одну из ячеек списка. Вы­бираем в меню Данные/ Фильтр. В ячейках, содержащих заголовки столбцов, появляются кнопки со стрелкой, направленной вниз. Щелкнем кнопку в ячейке «Отдел». В выпадающем списке перечислены все отделы (т.е. таким образом мы сразу получили неочевидную информацию: какие именно отделы представлены в списке). Снимаем флажок с пункта Выделить все и затем отмечаем только СТАТ. В кнопке со стрелочкой в поле «Отдел» появилось изображение воронки. В синий цвет окрасились номера строк. Это показывает, что список подвергся фильтрации. В строке состояния сообщение: "Найдено запи­сей: … ИЗ …" (через некоторое время оно заменяется сообще­нием "Фильтр:отбор"). Отменить отбор по критерию можно, еще раз щелкнув кнопку в поле«Отдел» и выбрав пункт Выделить все.

Сохранять результаты фильтрации данных будем в представлениях: Вид – Представления – Добавить. Чтобы иметь возможность быстро возвращаться к начальному виду сохраните текущее представление под именем «Общий вид».

Чтобы полностью отменить режим фильтрации, можно повторно выбрать Данные/ Фильтр.

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

Числовые фильтры – Первые 10... Можно вывести к (любое заданное количество) наибольших или наименьших элементов поля, где к задается в диалоговом окне "Наложение условия по списку". Но элементы списка должны быть сравнимы, т.е. быть числами. Вам не удастся выбрать записи по этому условию в поле OTDEL.

Выберите 5 самых низкооплачиваемых сотрудников.

Выберите 20% самых высокооплачиваемых сотрудников (в третьем поле в диалоговом окне укажите "% от количества элементов"). Скопируйте на лист 6. Отмените фильтрацию.

Если имеются за­писи с совпадающими значениями полей, то количество выво­димых записей может быть большим, чем количество запраши­ваемых элементов.

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

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

Выведем записи с работниками из отделов ФИН и ЮР. Щелкаем по кнопке в ячейке «отдел» и выбираем (Текстовые или числовые фильтры – Настраеваемый фильтр). В Диалоговом окне "Пользовательский автофильтр" в рамке с над­писью «отдел», т.е. с именем поля, в котором происходит отбор, сверху и снизу расположены пары из двух полей ввода. В левых полях можно ввести отношение (равно, не равно, больше и т.д.). Проще всего его выбрать из выпадающего списка. В правых по­лях ввода можно указать конкретное значение (название отде­ла), которое в данном случае опять-таки проще выбрать из вы­падающего списка. Итак, в первых двух полях указываем "равно ФИН", во вторых двух полях — "равно ЮР". Теперь нужно вы­брать логический оператор: И или ИЛИ. Здесь нужно быть вни­мательным. В условии задания сказано: "...из отделов ФИН и ЮР ". Но одновременно в двух отделах никто не работает — если выбрать в диалоговом окне переключатель И, то будет вы­брано 0 записей. Нужно указать переключатель ИЛИ. Естест­венно-языковое "И" здесь соответствует логическому "ИЛИ"!

Упражнение 1. Выведите список работников отдела СТАТ, имеющих оклад от 7000 до 15000 руб.

Упражнение 2. С текстовыми данными можно использовать операторы сравнения, как и с числовыми величинами, ведь символы коди­руются числовыми значениями. Например, чтобы вывести спи­сок работников, чьи фамилии начинаются на М — Р, нужно за­дать критерий " (больше или равно М) И (меньше С)". Испы­тайте это.

Но есть и дополнительные интересные возможности. Можно использовать символы шаблона: * — заменяет любую цепочку символов (в том числе и пустую),? — заменяет ровно один сим­вол. Например, чтобы узнать, кто из работников проживает на улице Вавилова, надо задать критерий "равно *Вавилова*, а чтобы узнать, в каких телефонных номерах вторая цифра 7, а третья цифра 2, нужно задать критерий "равно?72*". Испытайте эти критерии.

Упражнение 3. Выведите список работников, имена ко­торых заканчиваются на "на".

Упражнение 4. Выведите список работников, имена ко­торых содержат "на".