Подготовительные упражнения
Откройте таблицу KADR.xlsx.
В таблице — 68 записей. Оклады приведены в рублях до деноминации. Имеются четыре отдела: АПС, ОНК, ОТД, ТКБ. (В сокращенных названиях отделов не следует искать какого-либо смысла и пытаться их расшифровывать.)
ПРИМЕР 7.1.
Закрепление "шапки" и "боковика" таблицы. Просматривать таблицу неудобно: если перейти к последним записям, то с экрана исчезают заголовки столбцов; если хотим посмотреть телефоны, то пропадают фамилии. Нужно, чтобы заголовки столбцов (1-я строка) и фамилии (столбец А) постоянно присутствовали на экране. Для блокировки строк и столбцов щелкните ячейку, снизу и справа от которой требуется разделить лист (у нас это ячейка B2). На вкладке Вид в группе Окно щелкните пункт Закрепить области, а затем выберите нужный вариант (у нас - «Закрепить области»). При закреплении областей параметр Закрепить области изменяет значение на Снять закрепление областе й, что позволяет снять закрепление строк или столбцов.
Теперь Вы можете просматривать последние строки и столбцы, не теряя из виду информацию, содержащуюся в первой строке и первом столбце. Проверьте.
Использование команды "Специальная вставка" для изменения массива чисел. В поле Оклад размещены оклады в старом масштабе. После 01/01/98 произошла деноминация. Поэтому все числа в этом столбце надо уменьшить в тысячу раз. Поместим где-нибудь на свободном месте рабочего листа, например в ячейке L2, число 1000. Скопируем его в буфер (клавиши Ctrl+Insert). Выделим диапазон с окладами и в контекстном меню выберем пункт "Специальная вставка". В диалоговомокне выберем "разделить". Все оклады уменьшатся в 1000 раз. Нажмем клавишу Esc, чтобы убрать бегущую рамку вокруг ячейки L2. Воспользуемся тем, что оклады выделены, и установим формат: две цифры после точки. Удалим содержимое L2 (больше не нужное). Подгоним ширину столбца с окладами.
Начисление премии. Для иллюстрации некоторых важных моментов работы со списками решим уже знакомую задачу: начислим каждому работнику премию в размере 10% оклада (при этом оставим возможность изменения премии).
Вставим две пустые строки перед таблицей. В ячейку F1 поместим строку "премия", в G1 — 10%. Вторую строку специально оставили пустой, чтобы список был ограничен пустыми ячейками. После столбца "Оклад" добавьте два пустых столбца, дайте заголовки "Премия" и "Всего" и заполните столбцы соответствующими формулами.
Сохраните рабочую книгу (клавиши Ctrl+S), закройте ее (клавиши Ctrl+F4) и заново откройте (клавиши Ctrl+O).
Сортировка
ПРИМЕР 7.2.
Сортировка по одному ключу. Для списков Excel ключ — это поле. Отсортируйте список по полю "Пол" по возрастанию. Сделайте текущей любую ячейку таблицы. На вкладке Данные в разделе Сортировка и фильтр щелкните по кнопке Сортировка. В окне Сортировка в поле Сортировать по укажите соответствующее поле (выберите это поле в выпадающем списке) и выберите переключатель "от А до Я". Сначала в списке будут фигурировать женщины, затем мужчины, так как по алфавиту буква "ж" предшествует букве "м". Обратите также внимание, что автоматически установлен флажок "Мои данные содержат заголовки", т.е. строка заголовков останется при сортировке на месте.
Заново отсортируйте список, но чтобы на этот раз мужчины предшествовали женщинам.
Теперь отсортируйте список по полю "Отдел". Сотрудники будут упорядочены по отделам в алфавитном порядке их аббревиатур: АПС, ОНК, ОТД, ТКБ. Как поступить, если нужно располагать отделы в другом порядке, например ОТД, ОНК, АПС, ТКБ?
Можно задать пользовательский список и сортировать в соответствии с порядком элементов в этом списке. Для этого:
1. на вкладке Данные в разделе Сортировка и фильтр щелкните по кнопке Сортировка. В окне Сортировка в поле Сортировать по укажите Отдел и в поле Порядок выберите Настраиваемый список;
2. В окне Списки выберите "НОВЫЙ СПИСОК";
3. справа введем список отделов:
ОТД
ОНК
АПС
ТКБ
и щелкнем кнопку "Добавить" (к стандартным спискам добавится и наш, пользовательский);
4) сортируем по столбцу Отдел, теперь в поле Порядок указан наш список.
Пользовательский список подходит только для сортировки по первому ключу.
Сортировка по двум или трем ключам. Отсортируйте список по полю «Пол» по убыванию и по полю «Номер отдела» по возрастанию. Затем выполните сортировку сначала по номеру отдела по возрастанию и по полу по убыванию. Результаты будут различными. При постоянном значении первого ключа изменяется второй ключ.
Сортировка по четырем ключам. Для нашего списка пример несколько надуманный: нужно отсортировать список в таком порядке: пол, отдел, оклад, фамилии (все по возрастанию).
Выполните сортировку: по отделам, внутри отделов — сначала мужчин, потом женщин и, наконец, по фамилиям.
Сортировка с учетом регистра букв. При сортировке текстовых строк по умолчанию не учитывается, какими буквами набраны эти строки — прописными или строчными. Это удобно: ведь при заполнении базы данных пол работника могли обозначать и строчной буквой "м" (нижний регистр), и прописной буквой "М" (верхний регистр). Но если регистр букв существен, это можно указать в диалоговом окне "Параметры сортировки". Замените в некоторых записях обозначение пола прописной буквой и дважды отсортируйте список по полу: сначала без учета регистра, а затем с учетом регистра.
Как восстановить исходный порядок записей. Закройте рабочую книгу (клавиши Ctrl+F4) без сохранения изменений. Вновь откройте KADR.xlsx. Мы опять планируем сортировать записи, но хотим сохранить возможность восстановления их первоначального порядка. Для этого надо завести скрытый столбец с порядковыми номерами записей.
Упражнение 7.1. Отсортируйте список по отделам, внутри отделов по возрастанию табельных номеров.
Упражнение 7.2. Отсортируйте по отделам, внутри отдела поместите сначала мужчин, потом женщин, их, в свою очередь, упорядочите по убыванию количества детей, а для одинакового количества детей по алфавитному порядку фамилий. Восстановите первоначальный порядок записей сортировкой по столбцу А (с именем "N").
Упражнение 7.3. Вычислите возраст (число полных лет) каждого работника. Выполнить сортировку по отделам, а внутри отделов — по убыванию возрастов.
ЗАДАЧА 7. 1. Вы хотите составить для каждого отдела график празднования дней рождений. Отсортировать список по отделам, внутри отделов по месяцам рождений, внутри месяцев — по дням.
Упражнение 7.4. Замените столбцы с фамилией, именем, отчеством на один столбец "Фамилия И.О." (У одного из работников отчество отсутствует!).
Для отбора записей, удовлетворяющих некоторому критерию (например, работники, имеющие оклад ниже некоторого порогового значения), можно использовать сортировку с последующим копированием группы идущих подряд записей на другой рабочий лист или в другую рабочую книгу, но предпочтительнее для этого использовать фильтрацию, которая будет изучена ниже. Тогда не нужно использовать скрытый столбец с исходными номерами, да и возможности отбора у фильтрации выше, чем у сортировки.
Итоги
Excel обладает очень полезным средством автоматического подведения основных и промежуточных итогов.
Закройте без сохранения предыдущую таблицу (клавиши Ctrl+F4) и вновь откройте KADR.xlsx. Проставьте оклады в деноминированных денежных единицах (т.е. уменьшите их в 1000 раз). Премию не вычисляйте — она нам больше не понадобится.
ПРИМЕР 7.3.
Получим суммарные оклады по отделам и в целом по предприятию. Отсортируйте список по отделам. Выберите на вкладке «Данные/ Промежуточные итоги». В диалоговом окне укажите следующее: "При каждом изменении в:" выберите из списка Отдел, "Операция:" выберите "Сумма", "Добавить итоги по:" поставьте галочку против поля Оклад. Щелкните "ОК".
Получен список с итоговыми строками по полю Оклад. Если бы мы не отсортировали список по отделам, то строк с промежуточными итогами в списке оказалось бы очень много (можете попробовать). Подгоните ширину столбца Отдел.
Слева от адресной полосы с номерами строк Вы видите символы структуры. При щелчке по кнопке с номером уровня 1 Вы получаете только общие итоги по предприятию в целом, при щелчке по кнопке 2 Вы получаете итоги по отделам, при щелчке по кнопке 3 Вы получаете детальный исходный список, в котором, однако, сохранены итоги.
Вернитесь к уровню 2. Перед Вами короткий список: четыре отдела и общие итоги. Отсортируйте этот список по полю Оклад в порядке убывания. Щелкните по кнопке с номером уровня 3. Результаты только что проведенной сортировки сохранились: первыми идут работники отдела с наибольшим суммарным окладом.
Упражнение 7.5. Постройте круговую диаграмму с суммарными окладами по отделам.
Упражнение 7. 6. Скопируйте суммарные оклады по отделам на новый рабочий лист.
Вновь вернитесь к уровню 2. Предположим, мы хотим посмотреть только список сотрудников отдела ОНК. Щелкните по кнопке +, расположенной слева от строки с итогами по отделу ОНК. В кнопке появится знак минус, а список сотрудников отдела ОНК развернется. Итак, помимо промежуточных итогов мы получили "бесплатное приложение"— структуру. Если структура Вам не нужна, можно удалить ее или скрыть («Данные/ Структура/ Разгруппировать/ Удалить структуру»).
В ячейке с итогами используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Разберитесь самостоятельно с помощью справочной системы, что это за функция и что означают ее параметры.
Поставим перед собой новую задачу: наряду с итоговым окладом мы хотим получить среднее количество детей на одного работника отдела. Выведем диалоговое окно "Данные/ Итоги" (вкладке «Данные/ Промежуточные итоги»). Во-первых, снимите флажок "Заменить текущие итоги" (если этого не сделать, то суммарный оклад исчезнет). Далее выберите: при каждом изменении в Отдел, операция "Среднее", итоги по полю Дети. Щелкните кнопку "ОК". Обратите внимание, что структура изменилась, теперь в ней четыре уровня. Исследуйте ее.
Верните исходный порядок записей в списке.
Автофильтр
Отфильтровать список — показать только те записи, которые удовлетворяют заданному критерию.
ПРИМЕР 7.4.
Отбор по одному полю. Покажем строки с информацией о сотрудниках отдела ТКБ.
Выделяем одну из ячеек списка. На вкладке «Данные/ Сортировка и фильтр/ Фильтр»
В ячейках, содержащих заголовки столбцов, появляются кнопки списков. Щелкнем кнопку в ячейке Отдел. В выпадающем списке перечислены все отделы (т.е. таким образом мы сразу получили неочевидную информацию: какие именно отделы представлены в списке). Выбираем ТКБ. На кнопке в поле Отдел появился рисунок фильтра. Это показывает, что список подвергся фильтрации. В строке состояния сообщение: "Найдено записей: 22 из 68". Отменить отбор по критерию можно, еще раз щелкнув кнопку в поле Отдел и выбрав пункт «Снять фильтр с Отдел». Чтобы полностью отменить режим фильтрации, повторно нажимаем кнопку «Фильтр»
Отбор по нескольким полям. Усложним задачу: выведем на экран только бездетных мужчин из отдела ОНК. Для этого выберем соответствующие элементы в выпадающих списках для кнопок в полях Отдел, Пол, Дети. Отмените фильтрацию, щелкнув кнопку «Очистить», в разделе «Сортировка и фильтр»
Фильтрация записей с пустыми элементами. Если в столбце имеется хотя бы одна запись с пустым (незаполненным) полем, то в выпадающем списке для этого поля есть пункт (Пустые). Найдите запись, в которой пропущено отчество.
Настройка автофильтра для более сложных критериев. Для каждого столбца можно создать критерий, состоящий из одного или двух условий, соединенных логическими операторами И, ИЛИ.
Выведем записи с работниками из отделов ТКБ и ОНК.
Упражнение 7.7. Выведите список работников отдела АПС, имеющих оклад от 200 до 300 руб.
Упражнение 7.8. Выведите список мужчин из отделов АПС и ТКБ, родившихся в 50-е годы.
С текстовыми данными можно использовать операторы сравнения, как и с числовыми величинами, ведь символы кодируются числовыми значениями. Например, чтобы вывести список работников, чьи фамилии начинаются на М — Р, нужно задать критерий " (больше или равно М) И (меньше С)". Испытайте это.
Но есть и дополнительные интересные возможности. Можно использовать символы шаблона: * — заменяет любую цепочку символов (в том числе и пустую),? — заменяет ровно один символ. Например, чтобы узнать, кто из работников проживает на улице Вавилова, надо задать критерий "равно * Вавилова*",а чтобы узнать, в каких телефонных номерах вторая цифра 7, а третья цифра 2, нужно задать критерий "равно?72*".Испытайте эти критерии.
Упражнение 7.9. Выведите список работников, имена которых заканчиваются на "на".
Упражнение 7.10. Выведите список работников, имена которых содержат "на".
Расширенный (усиленный) фильтр
В большинстве практических задач достаточно возможностей автофильтра. Но профессиональный пользователь должен владеть и более богатыми возможностями, которыми обладает расширенный фильтр.
Расширенный фильтр позволяет:
· сразу копировать отфильтрованные записи в другое место рабочего листа (но, к сожалению, того же листа, на котором находится исходный список; на другой лист или в другую рабочую книгу придется копировать "вручную");
· сохранять критерий отбора для дальнейшего использования (это полезно, когда список изменяется, а нужно периодически извлекать из него информацию в соответствии с критерием);
· показывать в отфильтрованных записях не все столбцы, а только указанные;
· объединять оператором ИЛИ условия для разных столбцов; для одного столбца объединять операторами И, ИЛИ более двух условий;
· создавать вычисляемые критерии;
· выводить только уникальные значения.
Сейчас мы решим серию примеров и задач. Критерий занимает некоторый блок рабочего листа. Обычно эти блоки располагают над списком, но так как мы создадим несколько критериев, отведем для них рабочий лист. Вставьте в текущую рабочую книгу новый рабочий лист (меню "Вставка / Лист") и назовите его "Критерии".
ПРИМЕР 7.5.
Выбрать сотрудников отдела АПС, чей оклад меньше 200 руб.
| Отдел | Оклад |
| АПС | <200 |
| Рис. 7.2 |
Решение. На листе "Критерии" заполним четыре ячейки (например, А1:В2), — рис. 7.2.
Дадим этому блоку имя "кр1". (В дальнейшем адрес блока указывать не будем, создавая блок с критерием, присваивайте ему имя.).
В первой строке блока указываются названия полей, для которых создаются условия (в нижележащих строках). Названия полей должны точно совпадать с названиями полей из списка, поэтому рекомендуется не набирать эти названия вручную, а копировать их из соответствующих ячеек. Условие записывается в виде: отношение значение. Здесь отношение может быть таким: равно (не указывается, так как с этого символа начинается формула), <>,>,>=,<,<=. Значение: строка, число или ссылка на ячейку. Условия, расположенные в одной строке, соединены логическим оператором И. Переведем табличный критерий в логическую формулу: "(Отдел = АПС) И (Оклад<200)".
Вывод отфильтрованных данных осуществим в трех вариантах.
1. Выведем отфильтрованный список на месте исходного списка. Перейдем на лист kadr и выберем на «Данные/ Сортировка и фильтр/ Дополнительно» В диалоговом окне выбираем переключатель "Фильтровать список на месте". Поле "Исходный диапазон" уже заполнено: $А$1:$К$69. В поле "Диапазон условий" вводим "кр1". Щелкаем кнопку "ОК". На месте исходного списка выводится отфильтрованный список.
Восстановим исходный список.
2. Выведем отфильтрованный список в другом месте рабочего листа kadr. Выполним те же действия, что и в предыдущем варианте, но выберем переключатель "Скопировать результат в другое место". Заполним поле "Поместить результат в диапазон": укажем ячейку А72. Отфильтрованный список расположится справа и вниз от этой ячейки. Удалим его (клавиши Ctrl+*и Del).
ПРИМЕР 7.6. Выбрать сотрудников отдела АПС, чей оклад в интервале от 150 до 200 руб.
Решение. Переформулируем задание: "(Отдел = АПС) И (Оклад > 150) И (Оклад < 200)" (нельзя записать 150 < Оклад < 200). Теперь несложно сформулировать критерий в виде блока (рис. 7.4)
| Отдел | Оклад | Оклад |
| АПС | >150 | <200 |
Рис 7.4.
(Можно было поместить "Оклад" в С1, ">150" — в С2 и дать блоку А1:С2 имя "кр2".)
Повторите три варианта размещения отфильтрованного списка из предыдущего примера.
ПРИМЕР 7.7.
| OTDEL | OKLAD |
| АПС | |
| <200 | |
| Рис. 7.5 |
Выбрать сотрудников либо из отдела АПС, либо тех, чей оклад меньше 200 руб.
Решение. Критерий (имя "крЗ") показан на рис. 7.5. Пустая ячейка означает "все". Условия в разных строках соединены логическим оператором ИЛИ. Переформулируем критерий: "((Отдел = АПС) И (Оклад любой)) ИЛИ ((Отдел любой) И (Оклад < 200))".
Выполните фильтрацию (каким-нибудь одним вариантом размещения).
ПРИМЕР 7.8.
Какие отделы представлены в списке?
Решение. Нам нужно выбрать записи по критерию "Отдел любой". Решением будет рис. 7.6.
| Отдел |
| Рис 7.6 |
Именно такой диапазон с пустой ячейкой назовите "кр4". На рабочем листе kadr создайте в свободной ячейке заголовок Отдел для отфильтрованного списка. Если теперь задать расширенный фильтр, как это делалось в предыдущих примерах, но с критерием "кр4", то будет выведено 68 строк, каждая размером в одну ячейку. Уничтожим их. А теперь заново зададим расширенный фильтр, но на этот раз в диалоговом окне установим флажок "Только уникальные записи". Теперь будет выведено только четыре отдела.
ПРИМЕР 7.11.
Кто из сотрудников отделов ОНК и ОТД проживает на улице Вавилова?
Решение. Условия для текстовых строк задаются так же, как и в Автофильтре: можно использовать символы шаблона * и?. Сформулируем критерий: "((OTDEL = ОНК) ИЛИ (OTDEL = ОТД)) И (ADR содержит подстроку "Вавилова")". Непосредственно в таком виде таблицу для критерия не составить.Придется предварительно применить дистрибутивный закон для раскрытия скобок:
.
Напомню, что значок Ú — дизъюнкция, операция ИЛИ, а 
— конъюнкция, операция И; сам дистрибутивный закон аналогичен применяемому в элементарной алгебре тождеству (а + b)с = ас + bc. После этого нетрудно составить табличный критерий (рис. 7.7). >
| Отдел | Адрес |
| ОНК | *Вавилова* |
| ОТД | *Вавилова* |
Рис.7.7
ПРИМЕР 7.9.
Кто из сотрудников отдела ОНК имеет оклад, на 20% превышающий средний оклад по предприятию?
Решение. Дадим два незначительно различающихся решения.
Сначала вычислим среднее значение. На листе kadr в ячейке Н71 разместим формулу =СРЗНАЧ(Н2:Н69). На листе "Критерии" сформируем критерий (рис. 7.8).
| Отдел | |
| ОНК | =H2>KADR!$H$71*1.2 |
Рис.7.8
Во втором столбце критерия нет заголовка (можно проставить любой заголовок, лишь бы он не совпадал ни с одним из заголовков фильтруемого списка). В этом столбце помещена формула. В формуле сравнивается первая ячейка с окладом из списка (Н2) с ячейкой вне списка (KADR!$H$71), содержимое которой умножено на 1.2 (т.е. увеличено на 20%). Обратите внимание, что ссылка на ячейку из списка относительная, а на ячейку вне списка — абсолютная. Excel будет последовательно настраивать адреса в вычисляемом критерии: сначала Н2, потом Н3 и т.д. Если бы ссылка на Н71 не была сделана абсолютной, то Н2 сравнивалась бы с Н72 и т.д.
Формулу мы видим в строке ввода. В самой ячейке выводится ее значение ЛОЖЬ, так как именно это значение имеет место для первой записи списка. Конечно, на это значение в блоке критерия не следует обращать внимание.
Другое решение не использует промежуточной ячейки (рис. 7.9).
| Отдел | |
| ОНК | =H2>CP3HA4(kadr!$H$2:$H$69)*1.2 |
| Рис.7.9 |