1. Практические задания 2 Задание Структура документа и ввод данных. 2
 Скачать 13.33 Mb.
|
Задание № 9. Фильтрация данных.Цель: Освоить возможности фильтрации табличных данных. Темы: Фильтрация данных с помощью автофильтра. Расширенная фильтрация и сложные критерии. 1. Фильтрация записей с помощью функции автофильтра. 1.1. Для выполнения задания скопируйте итоговый список сотрудников из Задания №8 в новую книгу. 1.2. Примените автофильтр, пользуясь командой Данные – Сортировка и фильтр - Фильтр, или Главная – Редактирование – Сортировка и фильтр – Фильтр, определите: сколько в фирме женщин и каков их средний заработок; троих самых великовозрастных, независимо от пола; какой максимальный оклад имеет сотрудница, не получающая надбавку; кто из мужчин живет в центральном районе (телефон начинается от 310 до 315); каков суммарный заработок у менеджеров и инженеров; сколько в фирме работает Ивановых, и каков их суммарный оклад; сколько сотрудников получают больше 3 000 руб., но меньше 10 000 тыс. руб. Сколько из них женщин; сколько сотрудников получают больше 10 000 руб. или меньше 3000 руб., и кто из них не получает надбавки; 2. Расширенная фильтрация. Для активизации расширенной фильтрации воспользуйтесь командой Данные – Сортировка и фильтр – Дополнительно. 2.1. Для выполнения задания скопируйте "Ведомость оценок" (рис.8.2 из Задания № 8), расположите ее в начале чистого листа; определите область критериев справа от таблицы. 2.2. Определив область для извлечения данных под таблицей, найдите студентов, имеющих: тройки по математике; тройки по математике, но четверки по физике; двойки по математике, но пять по физике и средний балл больше 3,5 (предварительно добавив в таблицу столбец "Ср. балл"); тройки по математике или тройки по физике; двойку по любому предмету (хотя бы одну). 2.3. Извлеките (на месте) данные о студентах, имеющих: средний балл меньше 4; средний балл больше, чем 3,5 и оценку по математике больше 3; средний балл не меньше, чем 4,5 или меньше 4, но по биологии - 5; средний балл больше, чем 3,5, но меньше 42. 2.4. Извлеките только фамилии студентов3: не имеющих двоек; имеющих хотя бы одну двойку. не имеющих двоек и имеющих средний балл не меньше 4; 3. Предъявите результаты преподавателю. Задание № 10. Связывание таблиц.Цель: Научиться создавать связи между таблицами. Темы: Создание и использование связей между данными. Использование «объемных формул». 1. Создайте три таблицы, содержащие сведения о ценах на программные продукты, по образцу, приведенному на рис.10.1. Для каждого месяца первого квартала на отдельном листе книги Имя_10_1 создается собственная таблица с названием "Прайс-лист (Месяц)", где месяц - Январь, Февраль, Март. 1.1. При создании таблиц организуйте связи между таблицами "Прайс-лист (Январь)" и таблицами "Прайс-лист (Февраль)" и "Прайс-лист (Март)", для чего скопируйте диапазон ячеек А3:В13 январской таблицы цен в буфер, перейдите в таблицу "Прайс-лист (Февраль)" и воспользуйтесь командами Главная – Буфер обмена – Вставить – Вставить связь (или Главная – Буфер обмена – Вставить – Специальная вставка – Вставить связь). Аналогично установите связь с таблицей "Прайс-лист(Март)".  Рис.10.1 1.2. Переменную часть таблиц (столбец "Цена") отредактируйте согласно данным, приведенным на рис.10.1. Переименуйте листы, дав им соответствующие имена (Январь, Февраль, Март). 1.3. Посмотрите, как выглядят ссылки в строке формул при активизации связанных ячеек в таблицах февраля и марта. Изменив содержимое ячейки А7 в январской таблице, просмотрите, как изменится соответствующая ячейка в февральской таблице. Попытайтесь изменить текст в ячейке А7 февральской таблицы и сделайте выводы о направленности установленной связи. 2. Создайте таблицы "Отгрузка (Январь)", "Отгрузка (Февраль)" и "Отгрузка (Март)"по образцу, приведенному на рис.10.2, пользуясь режимом группового заполнения, и дайте листам книги названия: Отгр_ЯНВ, Отгр_ФЕВ, Отгр_МАР. 2.1. В ячейке D4 запишите формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Январь)". Эта формула приведена в строке формул, показанной на рис.10.2 в верхней части. 2.2. Скопируйте формулу в ячейки D5:D13. 2.3. Запишите в ячейку D14 формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13). 2.4. Активизируйте команду Формулы – Зависимости формул – Влияющие ячейки для ячейки D14. 2.5. Установите курсор в ячейку D4 и отобразите влияющие ячейки. Пронаблюдайте, как отображается зависимость от внешней таблицы "Прайс_лист (Январь)", связанной с таблицей "Отгрузка(Январь)". Обратите внимание, как в строке формул выглядит формула со ссылкой на ячейку из другой таблицы, и из каких элементов состоит эта ссылка. 2.6. Сохраните созданную книгу с шестью листами под именем Имя_10_1. 2.7. Сохраните копию книги под именем Имя_10_2. 2.8. Удалите из книги Имя_10_1 листы "Отгр_ЯНВ", "Отгр_ФЕВ" и "Отгр_МАР", сохранив в ней только прайс_листы.  Рис.10.2 3. Оставьте открытыми обе книги. Заполните таблицу "Отгрузка(Февраль)" книги Имя_10_2 , пользуясь "Прайс_листом(Февраль)" книги Имя_10_1. 3.1. В ячейке D4 запишите формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Февраль)". Эта формула приведена в строке формул, показанной на рис.10.3.а, в верхней части. 3.2. Скопируйте формулу в ячейки D5:D13. 4. Заполните таблицу "Отгрузка(Март)" книги Имя_10_2, пользуясь "Прайс_листом(Март)" книги Имя_10_1 аналогично п.3.1 и 3.2 4.1. Закройте книгу Имя_10_1. Просмотрите формулу в D4.Она приведена в строке формул, показанной на рис.10.3.б, в верхней части. 4.2. Запишите в ячейки D14 отгрузочных листов формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13).  Рис.10.3.а Рис.10.3.б 5. Создайте новую таблицу "Суммарный доход за три месяца", в которой будут сведены итоговые значения выручки за все кварталы за счет организации "трехмерной связи", т.е. связи между одинаковыми клетками однотипных таблиц. Принцип создания такой таблицы представлен на рис.10.4. В создаваемой таблице запишите две формулы для получения одного и того же значения, но в одной из них запишите формулу с непосредственным обращением к каждой таблице, а в другой - с обращением к блоку таблиц, так называемую "объемную" формулу. Примеры записи таких формул приведены на рис.10.4 непосредственно под ячейками В4, В7 и выделены курсивом.  Рис.10.4 6. Предъявите результаты преподавателю. |