Перегруппируйте записи по алфавиту фамилий.

Для этого выполните следующее:

· выделите список записей, включая имена полей (интервал ячеек A4:F14);

· выберите на вкладке Данные в группе Сортировка и фильтр кнопку Сортировка. В окне Сортировка щелкните на стрелку около поля Сортировать по. из раскрывшегося списка, выберите поле Фамилии. Выберите порядок По возрастанию и щелкните на кнопке ОК.

Записи в таблице перегруппируются. Скопируйте их в новую область рабочего листа. Для этого:

· выделите интервал ячеек с результатами выполненной задачи (имена полей и данные);

· по команде ГлавнаяàКопировать результат работы скопируется в буфер;

· щелкните на ячейку, относительно которой будет расположена копия;

· по команде ГлавнаяàВставить результат выполненной задачи появится в выбранной области. Сделайте соответствующую надпись над копией.

Перегруппировать записи по убыванию зарплаты.

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

 

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

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

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

Для этого выполните следующее:

· Подсчитайте среднее значение по полю Зарплата;

· выделите интервал ячеек списка вместе с полями (A4:F14);

· выберите вкладку Данные вгруппе Сортировка и фильтр кнопку Фильтр. Около каждого поля появятся кнопки со стрелками;

· щелкните на стрелку около поля Зарплата. Из раскрывшегося списка выберите Числовые фильтры далее Меньше... В диалоговом окне Пользовательский Автофильтр выберите среднее значение зарплаты (щелчком на кнопке со стрелками у текстовых полей можно раскрыть список операций отношения и возможных значений, можно в текстовое поле ввести требуемое числовое значение равное средней величине зарплаты вычисленной ранее);

· после щелчка на кнопке OK в базе будут отображаться только те записи, которые удовлетворяют заданному условию. Cкопируйте оставшиеся записи в новую область, аналогично тому, как это выполняется при сортировке;

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

Среди всех записей выберите тех сотрудников, фамилии которых начинаются с буквы А.

3. Среди всех записей выберите тех сотрудников, у которых оклад больше наименьшего и меньше наибольшего (в окне диалога Пользовательский автофильтр для этого случая необходимо задать два И условия ).

Приемы работы с расширенным фильтром

4. Среди всех записей выберите тех сотрудников, фамилии которых начинаются с буквы Г и далее, а зарплата меньше 600.

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

· наименования полей поиска Фамилии и Зарплата из ячеек B4 и F4 cкопируйте в ячейки H4 и I4 соответственно;

· в ячейку H5 введите условие поиска по полю Фамилии >=Г (фамилии начинаются с буквы Г). В ячейку I5 введите условие поиска по полю Зарплата <600 (зарплата которых меньше 600);

· выделите ячейки базы данных вместе с названиями полей (интервал A4:F14);

· выберите вкладку Данные в группе Сортировка и фильтр щелкните на кнопку Дополнительно;

· в окне Расширенный фильтр для пункта Обработка включите селекторную кнопку Скопировать результат в другое место;

· Исходный диапазон A4:F14 будет присутствовать в окне в окне Расширенного фильтра, если интервал данных был выделен;

· Диапазон критериев H4:I5 необходимо задать;

· Поместить результат в диапазон H7 (необходимо задать);

· затем щелкнуть кнопку ОК.

Проверьте результат выполнения задания.

5. Сохраните рабочую книгу на диске командой OfficeàСохранить.

ПРИМЕЧАНИЕ. Если какое-то действие вы выполнили неверно, сразу откажитесь от него, щелкнув на кнопке Отменить панели быстрого доступа.

 

 

Задание № 6. Связывание данных разных рабочих листов

Цель работы

1. Навыки в построении формул, использующих данные различных рабочих листов (книг).

2. Работа с несколькими открытыми окнами.

Для работы в качестве исходного материала воспользуемся таблицей " Ведомость по начислению зарплаты ".

Запустите Excel и откройте свою рабочую книгу.

Методические указания

1. Вставьте четыре новых рабочих листа. Назовите листы соответственно Январь, Февраль, Март и Квартал_1.

2. Откройте рабочий лист Ведомость.

3. Скопируйте полностью таблицу «Ведомость по начислению зарплаты» на листы с названиями месяцев. Для этого:

· выделите на листе Ведомость интервал ячеек с A1 до конца таблицы;

· выберите команду ГлавнаяàКопировать;

· щелкните на корешок Январь, затем на ячейку A1 этого листа;

· выберите команду ГлавнаяàВставить. На Листе появится таблица.

3. Аналогично выполните операцию копирования ведомости на листы Февраль и Март. При необходимости на новых листах выполните форматирование данных.

Рабочими листами занятия будут листы с именами Январь, Февраль, Март и Квартал_1.

4. Сформируйте на рабочем листе Квартал_1 Итоговую ведомость за 1 квартале по зарплате, макет которой представлен в табл.6.

Таблица 6.

5. Итоговые значения Окладов, Районных и Подоходных налогов необходимо для каждого месяца считывать из соответствующих ячеек таблиц, находящихся на листах Январь, Февраль и Март соответственно. Для этого необходимо организовать связь данных листа Квартал_1 с данными, находящимися на соответствующих листах. Для этого:

· откройте лист Январь. Выделите ячейку C10. Скопируйте содержимое ячейки в буфер (команда ГлавнаяàКопировать);

· откройте лист Квартал_1. Щелкните на ячейку B3;

· выберите вкладку Главная в группе Буфер обмена раскрыть список у кнопки Вставить выбрать Специальная вставка;

· в диалоговом окне Специальной вставки щелкните на кнопку Вставить связь. В ячейке B3 появится результат, вычисленный по формуле связи =Январь!$C$10. Аналогично введите формулы связи в ячейки C3 и E3 для суммарного значений районных и подоходного налога за Январь.

· Расчетные формулы Всего начислено и Зарплата простые (=B3+C3 и =D3-E3) вводите в ячейки D3 и F3.

6. Повторите описанные действия ввода формул связи для данных с листов Февраль и Март.

7. Скопируйте расчетные формулы для Всего начислено и Зарплата для расчетов за Февраль и Март. Полученные результаты по месяцам одинаковы, так как использованы одинаковые данные.

8. Используя кнопку Автосумма панели инструментов, подведите итоги за квартал по столбцам.

9. Выполните операции форматирования результатов.

10. Проверьте правильность выполненных действий. Для этого откройте новое окно выбрав вкладку Вид из группы Окно щелкните на кнопку Новое окно. В имени файла появится двоеточие и цифра два (Имя_файла:2). Затем расположите окна один над другим щелкните в группе Окно кнопку Упорядочить все. В окне Расположение окон РасположитьàСверху вниз. На экране отобразятся два одинаковых окна. В верхнем окне щелкните на ярлычок Январь, а в нижнем - Квартал_1.

11. Измените значение оклада за Январь Авдееву на порядок. Обратите внимание на изменения значений за месяц Январь листа Квартал_1.

12. В верхнем окне щелкните на ярлычок листа Февраль. Измените на порядок оклад за февраль Марушкину. Обратите внимание на пересчет значений в итоговой таблице за Февраль.

13. В верхнем окне отобразите ведомость за Март месяц. Измените себе оклад на порядок. Следите за пересчетом расчетных формул в нижнем окне.

14. Закройте текущее окно и разверните книги на весь экран. Сохраните книгу.

ЗАДАНИЕ № 7. АНАЛИЗ ДАННЫХ