Перегруппируйте записи по алфавиту фамилий.
Для этого выполните следующее:
· выделите список записей, включая имена полей (интервал ячеек A4:F14);
· выберите на вкладке Данныев группеСортировка и фильтркнопкуСортировка. В окне Сортировка щелкните на стрелку около поля Сортировать по . Из раскрывшегося списка, выберите поле Фамилии.Выберите порядок По возрастанию и щелкните на кнопке ОК.
Записи в таблице перегруппируются. Скопируйте их в новую область рабочего листа. Для этого:
· выделите интервал ячеек с результатами выполненной задачи (имена полей и данные);
· по команде ГлавнаяàКопировать результат работы скопируется в буфер;
· щелкните на ячейку, относительно которой будет расположена копия;
· по команде ГлавнаяàВставить результат выполненной задачи появится в выбранной области. Сделайте соответствующую надпись над копией.
Перегруппировать записи по убыванию зарплаты.
Перегруппировать записи по возрастанию оклада.
Фильтрация данных
Результаты фильтрации также необходимо копировать в нижележащие свободные области, надписывая содержание выполненной задачи задач.
1. Среди всех записей отфильтруйте те, у которых зарплата примерно меньше среднего значения.
Для этого выполните следующее:
· выделите интервал ячеек списка вместе с полями (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àСохранить.
ПРИМЕЧАНИЕ. Если какое-то действие вы выполнили неверно, сразу откажитесь от него, щелкнув на кнопке Отменить панели быстрого доступа.
Задание N 6
Связывание данных разных рабочих листов
Цель работы
1. Навыки в построении формул, использующих данные различных рабочих листов (книг).
2. Работа с несколькими открытыми окнами.
Для работы в качестве исходного материала воспользуемся таблицей "Ведомость по начислению зарплаты".
Запустите Excel и откройте свою рабочую книгу.
Методические указания
1. Откройте рабочий лист Ведомость.
2. Вставьте четыре новых рабочих листа. Назовите листы соответственно Январь, Февраль, Март и Квартал_1. Скопируйте полностью таблицу «Ведомость по начислению зарплаты» на листы с названиями месяцев. Для этого:
· выделите на листе Ведомость интервал ячеек с A1 до конца таблицы;
· выберите команду ГлавнаяàКопировать;
· щелкните на корешокЯнварь, затем на ячейку A1 этого листа;
· выберите команду ГлавнаяàВставить. На Листе появится таблица.
3. Аналогично выполните операцию копирования ведомости на листыФевраль и Март.При необходимости на новых листах выполните форматирование данных.
Рабочими листами занятия будут листы с именами Январь, Февраль, Март и Квартал_1.
4.Сформируйте на рабочем листе Квартал_1 Итоговую ведомость по начислению зарплаты в 1 квартале,макет которой представлен в табл.7.
Таблица 7.
 |
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. Закройте текущее окно и разверните оставшееся на весь экран.
15. Перезапишите рабочую книгу.
ЗАДАНИЕ N 7
АНАЛИЗ ДАННЫХ
I. Вычисление итогов
Выполним задачу анализа значений для таблицы, содержащей данные о продаже автомобилей. Исходные данные представлены в табл. 7. Эту таблицу сформируйте на новом листе Итоги вашей рабочей книги.
Таблица 7
