Лабораторная работа № 7. Фильтрация данных, организованных в списки
Цель: Освоить возможности фильтрации табличных данных, организованных в списки.
1. Фильтрация записей с помощью функции автофильтра.
1.1. Для выполнения задания скопировать итоговый список сотрудников из Задания 6 в новую книгу.
1.2. Установить автофильтр (меню "Данные"-"Фильтр"-"Автофильтр") и, выбирая соответствующие должности, принять на работу новых сотрудников. Штат должен содержать троих менеджеров, четырех инженеров, двух секретарей, двух референтов, одного водителя, директора и заместителя директора (с одинаковыми окладами для одинаковых должностей).
1.3. Применяя автофильтр и функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая вызывается кнопкой S на панели инструментов "Стандартная", определить:
· сколько в фирме женщин и каков их средний заработок;
· список Совета Старейшин (трое самых великовозрастных, независимо от пола);
· какой максимальный оклад имеет сотрудница, не получающая надбавку;
· кто из мужчин живет в центральном районе (телефоны начинаются от 310... до 315...);
· каков суммарный заработок у менеджеров и инженеров;
· сколько в фирме работает Ивановых, и каков их суммарный оклад;
· сколько сотрудников получают больше 900 руб. или меньше 500 руб., и кто из них не получает надбавки;
· составить список трех самых молодых инженеров.
2. Расширенная Фильтрация.
Вызвать справочную систему Excel, познакомиться со справочным материалом по этой теме, рассмотреть примеры.
|
|
|
2.1. Для выполнения задания скопировать "Ведомость оценок" (рис.6.2 из Задания 6), расположить ее в начале чистого листа, добавить в таблицу столбец "Ср. балл".
2.2. Определить область критериев справа от таблицы.
2.3. Извлечь (на месте) данные о студентах, имеющих:
· средний балл меньше 4;
· средний балл больше, чем 3,5 и оценку по математике больше 3;
· 4 и 5 по всем предметам;
· средний балл больше, чем 3,5, но меньше 4 (реализацию логической функции И для данных одного столбца осуществлять повторным размещением в области критериев заголовка данного столбца.).
· средний балл не меньше, чем 4,5 или меньше 4, но по биологии - 5.
2.4. Определив область для извлечения данных под таблицей, найти студентов, имеющих:
· тройки по математике;
· тройки по математике, но четверки по физике;
· двойки по математике, но пять по физике и средний балл больше 3,5;
· тройки по математике или тройки по физике;
· двойку по любому предмету (хотя бы одну).
2.5. Извлечь только фамилии студентов:
· не имеющих двоек;
· не имеющих двоек и средний балл не меньше 4;
· имеющих хотя бы одну двойку.
Примечание. Для выполнения этого задания в ячейку, с которой будет начинаться область для извлечения данных, ввести название заголовка столбца "Фамилия".
|
|
|
3. Предъявить результаты преподавателю.
4. Оформить отчет (приложение 1).
Лабораторная работа № 8. Связывание таблиц
Цель: Научиться создавать связи между таблицами.
1. Создать три таблицы, содержащие сведения о ценах на программные продукты, по образцу, приведенному на рис.8.1. Для каждого месяца первого квартала на отдельном листе книги Имя_8_1 создается собственная таблица с названием "Прайс-лист (Месяц)", где месяц - Январь, Февраль, Март.
1.1. При создании таблиц организовать связь между таблицами "Прайс-лист(Январь)" и таблицами "Прайс-лист (Февраль)" и "Прайс-лист (Март)", для чего скопировать диапазон ячеек А3:В13 январской таблицы цен в буфер, перейти в таблицу "Прайс-лист (Февраль)" и воспользоваться режимом "Правка"-"Специальная вставка-Вставить ссылку". Аналогично установить связь с таблицей "Прайс-лист(Март)".
1.2. Переменную часть таблиц (столбец "Цена") отредактировать согласно данным, приведенным на рис.8.1. Переименовать листы, дав им соответствующие имена (Январь, Февраль, Март).
1.3. Просмотреть, как выглядят ссылки в строке формул при активизации связанных ячеек в таблицах февраля и марта. Изменив содержимое ячейки А7 в январской таблице, просмотреть, как изменится соответствующая ячейка в февральской таблице. Попытаться изменить текст в ячейке А7 февральской таблицы, просмотреть сообщения и сделать выводы о направленности установленной связи.
|
|
|
Рис.8.1
2. Создать таблицы "Отгрузка (Январь)", "Отгрузка (Февраль)" и "Отгрузка (Март)"по образцу, приведенному на рис.8.2, пользуясь режимом группового заполнения, и дать листам книги названия: Отгр_ЯНВ, Отгр_ФЕВ, Отгр_МАР.
Рис.8.2
2.1. В ячейке D4 записать формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Январь)". Эта формула приведена в строке формул, показанной на рис.8.2 в верхней части.
2.2. Скопировать формулу в ячейки D5:D13.
2.3. Записать в ячейку D14 формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13).
2.4. Активизировать инструментальную панель "Зависимости", пользуясь меню "Вид"-"Панели инструментов". Отобразить и просмотреть влияющие ячейки для ячейки D14.
2.5. Установить курсор в ячейку D4 и отобразить влияющие ячейки. Пронаблюдать, как отображается зависимость от внешней таблицы "Прайс_лист (Январь)", связанной с таблицей "Отгрузка(Январь)". Обратить внимание, как в строке формул выглядит формула со ссылкой на ячейку из другой таблицы, и из каких элементов состоит эта ссылка.
|
|
|
2.6. Сохранить созданную книгу с шестью листами под именем Имя_8_1.
2.7. Сохранить копию книги под именем Имя_8_2.
2.8. Удалить из книги Имя_8_1 листы "Отгр_ЯНВ", "Отгр_ФЕВ" и "Отгр_МАР", сохранив в ней только прайс_листы, а из книги Имя_8_1 удалить Прайс-листы.
3. Оставить открытыми обе книги. Заполнить таблицу "Отгрузка(Январь)" книги Имя_8_2 , пользуясь "Прайс_листом (Январь)" книги Имя_8_1.
3.1. В ячейке D4 записать формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Январь)" книги Имя_8_1. При конструировании формулы для указания ссылки на адрес в другой книге использовать курсор мыши. Обратить внимание на то, как изменилась формула.
3.2. Скопировать формулу в ячейки D5:D13.
3.3. Аналогичным образом отредактировать листы отгрузок "Отгр_ФЕВ" и "Отгр_МАР".
3.4. Сохранить обе книги.
4. Просмотреть связи между закрытыми книгами.
4.1. Закрыть книгу Имя_8_1. Просмотреть, как изменилась запись формул в ячейках D4:D13 книги Имя_8_2.
4.2. Закрыть книгу Имя_8_2, открыть книгу Имя_8_1 и отредактировать значения Цены в Прайс-листах. Сохранить и закрыть книгу Имя_8_1.
5. Открыть книгу Имя_8_2 и проанализировать, как обновляются связи между книгами. На каждом листе “Отгрузки” записать в ячейку D14 формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13).
6. Создать новую таблицу "Суммарный доход за три месяца" (см. рис.8.3)
Рис.8.3
6.1. В таблицу свести итоговые значения выручки за все кварталы за счет организации "трехмерной связи", т.е. связи между одинаковыми клетками однотипных таблиц. Принцип создания такой таблицы представлен на рис.8.3.
6.2. В создаваемой таблице записать две формулы для получения одного и того же значения, но в одной из них записать формулу с непосредственным обращением к каждой таблице, а в другой - с обращением к блоку таблиц, так называемую "объемную" формулу. Примеры записи таких формул приведены на рис.8.3 непосредственно под ячейками В4, В7 и выделены курсивом.
6.3. Произвести изменения исходных данных в книгах Имя_8_1 и Имя_8_2, проанализировать пересчет итоговых результатов.
7. Оформить отчет (приложение 1).
Дата добавления: 2018-04-05; просмотров: 681; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!