2015-05-10
2882
Команды меню Данные работают корректно, когда шапка таблицы расположена в одной строке (если бы колонки таблицы были пронумерованы, то для работы команды Данные можно было бы использовать нумерацию в качестве названия колонок). При работе с командой Данные Excel рассматривает таблицу как базу данных, в которой строки являются записями, а столбцы полями.
Для дальнейшей работы преобразуем на новых листах таблицы из Задания 5.1. и 5.2. к виду:
Задание 5.5. Найти в преобразованной таблице Список (см. выше) тех студентов, у которых фамилия начинается с буквы "Б".
Выполнение.
1. Установить курсор в позиции «Ф.И.О»,
2. Выполнить команду Данные → Фильтр. Раскрыть список команд столбца «Ф.И.О».
3. Выбра позицию Текстовые фильтры, затем – Начинается с..
4. В окне Пользовательский автофильтр в поле Ф.И.О., которое будет участвовать в определении критерия, ввести критерий: Б*.
6. Нажать кнопку ОК, чтобы перейти к записи, удовлетворяющей введенному критерию.
Задание 5.6. Отсортировать таблицу Список по столбцам Группа и Номер зачетки.
Выполнение.
1) Выделить диапазон таблицы, включая шапку (А2:F14).
2) Выполнить команду Данные → Сортировка.
3) В диалоговом окне Сортировка диапазона в поле Сортировать по выбрать из списка имя столбца Группа, а в поле Затем по – Номер зачетки, установив переключатели для обоих столбцов – По убыванию.
4) Нажать кнопку [ОК].
Задание 5.7. Использование числового фильтра. Выбрать из таблицы Успеваемость, тех студентов, средний балл которых >=6.
Выполнение.
1) Выделить диапазон таблицы, включая шапку (A3:F15).
2) Выполнить команду Данные → Фильтр. Ячейки с названиями полей превратятся в раскрывающиеся списки.
3) Раскрыть список Автофильтра в столбце Средний балл студента.
4) Выбрать команду Числовые фильтры → Больше или равно. В новом окне Пользовательский автофильр ввести значение 6.
5) Нажать кнопку [ОК].
6) Для отмены условия выбрать в раскрывающемся списке столбца Средний балл студента команду Снять фильтр.
Задание 5.8. Работа с расширенным фильтром. Выбрать из таблицы Список, записи со студентами группы 113010, у которых Вид оплаты =1. Результат поместить в отдельный выходной блок ниже исходной таблицы.
Выполнение.
1. Скопируем преобразованную таблицу Список (заголовки располагаются в одной строке каждый) на отдельный лист.
2. На этом же листе сформируем диапазон условий (A18:D19), отделив его от исходного диапазона (A2:D14) тремя пустыми строками. Чтобы создать диапазон условий необходимо скопировать заголовки полей исходного диапазона, которые будут ключевыми при отборе записей (для нашего случая - это Группа и Вид оплаты), и заполнить строки критериев:
– в ячейку A18:D1 8 скопировать заголовки полей;
– в ячейку A19 ввести 113010;
– в ячейку D19 ввести 1.
3. Установить курсор в любую ячейку исходного диапазона.
4. Выполнить команду Данные → Сортировка и фильтр → Дополнительно. В диалоговом окне Расширенный фильтр выполнить следующие действия:
– В группе Обработка выберем Скопировать результат в другое место – исходная таблица не изменится, а отобранные записи будут помещены в выходной диапазон.
– В поле Исходный диапазон уже введена ссылку на диапазон, содержащий исходную таблицу (A2:D14).
– В поле Диапазон условий ввести ссылку на диапазон условий (A18:D19). Ввод ссылок во всех полях данного диалогового окна легче и лучше всего осуществлять путем протаскивания указателя мыши по нужному диапазону.
– В поле Поместить результат в диапазон установить ссылку на выходной диапазон (ячейка A21), так как выбран переключатель Скопировать результат в другое место.
– Необходимо установить флажок Только уникальные записи, чтобы oдинаковые записи не повторялись (будет выводиться только первая из всех удовлетворяющих критерию одинаковых записей).
– Нажать кнопку [ОК].
- Получаем новую таблицу со строками исходного диапазона, отобранными согласно условию:
Задание 5.9. Подведение частных и общих итогов. Рассчитать средние баллы по всем дисциплинам, каждой из учебных групп (на примере таблицы Список).
Выполнение.
1. Выделить диапазон A3:F15 – всятаблица, включая заголовки столбцов.
2. Выполнить команду Данные → Структура → Промежуточные итоги. В диалоговом окне Промежуточные итоги:
– в поле При каждом изменении в: из раскрывающегося списка выбрать Группа;
– в поле Операция из раскрывающегося списка выбрать Среднее;
– в поле Добавить итоги по: установить флажки: Математика, Информатика, Философия;
– установить флажок Итоги под данными.
3. Нажать кнопку [ОК ].
4. Округлить полученные итоги до двух десятичных знаков с помощью команды Формат → Ячейки.
Результат выполненного задания:
В результате подсчитаны средние баллы по группе и общий средний балл. Если с экрана убрать детали, нажав кнопку с изображением цифры "2"(второй уровень итогов), расположенную левее нумерации строк, то на экране останутся только полученные итоги.
5. Покажите результат второго уровня итогов.
Для получения развернутой информации по группе следует нажать кнопку с изображением знака "+" для соответствующей группы; для свертывания - кнопку с изображением знака "-".
Все промежуточные результаты скопировать в отчет.
Задание 5.10. Создание связанных таблиц. Сформировать в Excel таблицу для расчета сумм стипендий студентам факультета ПСФ в соответствии с формой, представленной в таблице Ведомость начисления. При этом учесть следующие условия:
– стипендия не начисляется студентам, обучающимся на платной основе;
– студенты получают минимальную стипендию, если средний экзаменационный балл больше 3;
– студенты, имеющие средний балл более 4.5, получают 50% надбавки к стипендии.
Выполнение.
1. В рабочей книге ПСФ.Иванов.xls перейти на новый лист и переименовать его на Начисления.
2. В строку 1 ввести название таблицы Ведомость начисления стипендии студентам ПСФ 1-го курса.
3. В ячейку B2 ввести текст Сумма минимальной стипендии.
4. В ячейку D2 ввести сумму минимальной стипендии – 120000.
5. Оформить шапку таблицы. Для этого в ячейки А3:Е3 ввести заголовки столбцов таблицы.
6. Столбец Номер зачетки заполнить данными. Чтобы не набирать еще раз номера зачеток, в столбце Номер зачетки установить ссылки на ячейки B5:B16, находящиеся на листе Список. Для того, чтобы при изменении номеров зачеток на листе Список, на листе Начисления они изменялись автоматически, выполните действия:
– перейти на лист Начисления, активизировать ячейку А4 и ввести знак " = " для определения формулы;
– перейти на лист Список. Ярлык листа Начисления выделится подсветкой, а в строке формул появится ссылка на лист-источник в следующем виде: Список!;
– выполнить щелчок левой клавишей мыши на ячейке В5 и нажать клавишу Enter;
– на листе Начисления в ячейке А4 будет введена формула вида: =Список!B5;
– скопировать формулу из ячейки А4 в остальные ячейки столбца А5:А15.
Замечание. Если нужная информация находится в другой книге (например, в книге с именем Отчет, на листе Ведомость, в ячейке В5), то необходимо перейти в нее с помощью команды Вид → Окно → Перейти в другое окно, при этом книга Отчет должна быть заранее открыта, затем перейти на нужный лист и выделить необходимую ячейку (например, В5) или диапазон ячеек. В строке формул появится ссылка вида: [Отчет.xls]Ведомость!В5.
8. Заполнить столбец Ф.И.О. данными. Для этого в ячейках В4:В15 листа Начисления установить ссылки на ячейки С5:С16 из листа Список.
9. Ввести формулы для вычисления суммы стипендии студентам:
– для расчета стипендии первому студенту активизируйте ячейку С5 на листе Начисления;
– перейти к пункту меню Формулы, выбрать категорию Логические, а в списке – функцию Если;
– щелкнуть на кнопке [ОК];
– во втором диалоговом окне задать аргументы функции. Для нашего примера, устанавливая курсор в каждой строке, ввести следующие операнды логической функции: Логическое выражение: И(Список!D5=1;Успеваемость!F5>=6), Значение если истина: $D$2, Значение если ложь: 0;
– нажать кнопку [ОК].
В результате в ячейке С4 появится значение, рассчитанное по формуле: =ЕСЛИ(И(Список!D5=1;Успеваемость!F5>=3);$D$2;0).
Для задания логического выражения используется логическая функция И, которая возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и возвращает ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Чтобы ссылка на ячейку D2 оставалась неизменной при копировании формулы, необходимо сделать ее абсолютной путем указания знака $ перед именем столбца и номером строки – $D$2.
Для вычисления величины стипендии всем студентам скопировать формулу из ячейки С4 в диапазон ячеек С5:С15.
10. Ввести формулы для вычисления величины надбавки к стипендии студентам:
– ввести формулу для расчета величины надбавки первому студенту в ячейку D4 на листе Начисления, загрузить Мастер функций и выбрать функцию Если;
– ввести следующие операнды логической функции:
Логическое выражение: И(Список!D5=1;Успеваемость!F5>=4,5),
Значение если истина: $D$2*0,5,
Значение если ложь: 0;
– нажать кнопку [ОК].
В ячейке D5 появится значение, рассчитанное по формуле:
=ЕСЛИ(И(Список!D5=1;Успеваемость!F5>=4,5);$D$2*0,2;0).
– Для расчета величины надбавки всем студентам скопировать формулу из ячейки D4 в диапазон ячеек D6:D15;
– ввести формулы для расчета общей суммы начислений каждому студенту. Для этого в ячейку Е4 ввести формулу: =C4+D4 и скопировать ее в диапазон ячеек Е5:Е15.
В результате получится следующая таблица:
11. Сохранить рабочую книгу.
Контрольные вопросы
1. Порядок сортировки по убыванию.
2. Использование Автофильтра.
3. Консолидация данных.
4. Что такое Фильтрация?
5. Фильтры типа Первые 10…?
6. Как консолидировать данные по категории?
7. Функции консолидации.
8. Использование функции Расширенный фильтр.
9. Назначение средства Промежуточные итоги.
10. Для чего нужна Сводная таблица?
11. Этапы работы Мастера сводных таблиц.
12. Какие логические функции вы знаете?
13. Формат функции ЕСЛИ.
14. Логические функции И и ИЛИ.
15. Как получить связанные таблицы?
16. Что означает знак «!» в ссылках?
Варианты заданий
Вариант 1
1. На 3-х листах создайте списки групп вашего потока и заполните графы:
Каждый лист назовите по номеру группы.
2. С помощью инструмента Фильтр на любом из листов:
а) отобразите только те записи, средний балл которых в диапазоне от 6 до 8;
б) отобразите только те записи, у которых прочерк в графе количество пропусков.
3. С помощью инструмента Сводная таблица создайте сводную таблицу успеваемости по предметам, посчитав средний балл по каждому предмету по всему потоку
4. Создайте лист Стипендия.
Стипендию начислите по следующим правилам:
а) ставку стипендии укажите в отдельной ячейке, присвойте этой ячейке имя Stavka;
б) количество пропусков для начисления не более 5;
в) при среднем балле от 4,5 до 5,5 начисляется 65%; от 5,6 до 7 – 75%; от 7,1 до 8,9 – 85%, от 9 до 10 – 100% указанной ставки. Сумма стипендии заносится в графу Сумма, начисляемый процент – в графу Тариф.
Лист, на котором находится таблица, защитите паролем, скройте расчетные формулы, уберите сетку.
5. С помощью инструмента Промежуточные итоги на листе Стипендия просмотрите:
а) количество студентов, получающих стипендию по каждому из тарифов;
б) общую сумму стипендии;
в) количество студентов, получающих стипендию, в каждой группе;
г) среднюю стипендию по каждой группе.
Вариант 2
- Создать телефонный справочник:
2. Заполните справочник:
а) в справочнике должно быть не менее пяти записей с одинаковыми фамилиями и инициалами;
б) с помощью текстовых (левсимв, правсимв, пстр) и логических функций (если) определите и внесите в примечания принадлежность номера телефонной компании (по первой цифре: 2-город, 4-Белсел, 6- Velcom, 7-MTC).
3. Выполните сортировку справочника:
– по возрастанию номеров телефонов;
– по алфавитному порядку фамилий.
4. Выделите записи из справочника при помощи фильтра (Данные → Фильтр → Автофильтр):
– выделить записи, у которых номер телефона принадлежит Velcom или MTC;
– затем среди выделенных записей выделить записи, в которых фамилии начинаются с буквы П.
5. С помощью инструмента Промежуточные итоги подсчитайте количество абонентов в каждой сети.
6. Скопируйте списки абонентов каждой сети на отдельные листы, сохранив связь с исходным листом, каждый лист назовите по имени сети.
7. Создайте лист Итог, на котором поместите Сводную таблицу, подсчитывающую количество абонентов каждой компании.
Вариант 3
1. Создать таблицу учета товарооборота реализации продукции через торговые точки:
2. Заполните таблицы:
а) заполните первую;
б) для второй скопируйте данные из первой.
2. С помощью инструмента Фильтр отобразите на любой из таблиц:
а) продукцию определенной ценовой категории (больше одной цены, но меньше другой);
б) только хлебобулочные изделия.
3. С помощью инструмента Промежуточные итоги отобразите на любой из таблиц:
а) сумму продаж по каждому виду продукции;
б) среднюю цену по каждому виду продукции.
4. По полученным таблицам построить сводную таблицу, содержащую итоговую информацию по всем наименованиям, и по всем торговым точкам.
Вариант 4
1. Создайте таблицу следующего вида:
2. Заполните ее таким образом, чтобы некоторые фамилии повторялись. После заполнения, выполните подгон ширины столбцов, отформатируйте текст заголовка по вашему вкусу, выберите шрифт, его размер и начертание, расположите заголовок по центру блока ячеек, включите перенос по словам.
3. Применяя текстовые функции и функции даты рассчитайте зарплату на сегодняшний день с учетом обычных выходных (функции Сегодня, Чистрабдни, Левсимвол, Сцепить):
2. С помощью инструмента Фильтр отобразите:
а) премированных работников;
б) работников, получивших зарплату в заданном диапазоне (например, больше 100$, но меньше 200$).
3. С помощью инструмента Промежуточные итоги отобразите в 1-ой таблице:
а) количество различных тарифных ставок (9$, 10$, 15$...);
б) сумму удержаний по каждому работнику.
4. Спроектируйте итоговую таблицу начислений так, чтобы каждая фамилия встречалась один раз.
Вариант 5
1. Создайте таблицу следующего вида:
2. Заполните ее, перечислив не менее 15 товаров 5-ти категорий:
2. С помощью инструментов Фильтр, Сортировать, Итоги выполните:
а) сортировку по наименованиям, по категориям и датам (т.е. по датам в каждой категории), по цене;
б) поиск товаров, цена которых находится в некотором диапазоне;
в) поиск товаров категории Кондитер. изд., купленных по цене, не превышающей 1500 р.;
г) стоимостные итоги по товарам, относящимся к одинаковым категориям;
д) максимальную и минимальную цену товаров каждой категории.
3. Создайте сводную таблицу, отражающую стоимость товаров по категориям за различные периоды времени.
4. Поработайте со сводной таблицей:
– внесите изменения в исходную таблицу и обновите сводную;
– уберите из исходной несколько записей и перестройте сводную;
– сгруппируйте графу дата по месяцам.
Вариант 6
1. Cоздайте таблицу для начисления квартплаты:
В таблице ячейки, выделенные зеленым цветом, должны содержать ссылки на ячейку с соответствующей информацией; синим цветом – формулы для вычислений. Курсивом набраны пояснения. Пеня начисляется в размере 0,25% на каждый день просрочки платежа, платеж должен проходить не позднее 10-ого числа текущего месяца, дата везде должна стоять текущая. Сверхнормативной считается площадь свыше 20 кв.м на человека.
2. Заполните неизменные данные (тарифы, перерасчет, ссылки и формулы) и сохраните как шаблон.
3. Отдельным файлом создайте список жильцов:
4. На основе шаблона и файла создайте книгу платежей, в которой извещения должны формироваться автоматически.
Вариант 7
1. Заполните таблицу. Зачет получает только тот студент, который сдал все лабораторные, количество пропусков при этом не более 10 часов, средний балл контрольных работ не менее 6, неудовлетворительных оценок (1, 2, 3) на контрольных нет.
2. С помощью инструмента Фильтр выявите тех, у кого:
– количество пропусков более 10 часов;
– средний балл контрольных работ 8 и выше;
– нет несданных лабораторных работ.
3. Создайте аналогичные таблицы для нескольких предметов, назвав листы по предмету.
4. Выполните консолидацию данных, подсчитав количество пропусков для каждого студента
Вариант 8
1. Создать таблицу учета товарооборота реализации продукции через торговые точки:
2. Заполните таблицы:
а) заполните первую;
б) для второй скопируйте данные из первой.
2. С помощью инструмента Фильтр отобразите на любой из таблиц:
а) продукцию определенной ценовой категории (больше одной цены, но меньше другой);
б) только хлебобулочные изделия.
3. С помощью инструмента Промежуточные итоги отобразите на любой из таблиц:
а) сумму продаж по каждому виду продукции;
б) среднюю цену по каждому виду продукции.
4. По полученным таблицам построить сводную таблицу, содержащую итоговую информацию по всем наименованиям, и по всем торговым точкам.
Вариант 9
1. Составить таблицу данных по погоде в городах Республики Беларусь:
2. Используя Пользовательский автофильтр определить:
– города, температура в которых за конкретную дату превышала 9 °С. Отсортировать полученные данные ла по городу, а затем — по возрастанию температуры;
– данные по погоде для заданного города за конкретный промежуток времени. Отсортировать полученные данные сначала по виду, а затем — по возрастанию количества осадков;
– города, в которых наблюдалось северо-восточное направление ветра за конкретный период времени. Отсортировать эти данные сначала по городам, а затем возрастанию даты;
– города, температура в которых наблюдалась в пределах от 5 до 14 °С за конкретную дату. Отсортировать полученные данные сначала по городам, а затем — возрастанию температуры.
3. Используя расширенный фильтр определить:
– города, для которых направление ветра – северное или северо-западное, температура воздуха в которых больше 8 °С, но меньше 12 °С;
– данные о погоде для Санкт-Петербурга или Минска за некоторый конкретный промежуток времени;
– города, в которых за конкретный промежуток времени выпал снег или снег с дождем, а также – температура в которых находилась в пределах от – 5 °С до +3 °С;
– города, в которых сила ветра не превышает 10% от средней силы ветра для города Гродно, и количество осадков – больше либо равно среднему значению для всех городов, или города, вид осадков в которых – град с дождем;
– города с западным или юго-западным направлением ветра, сила которого больше минимальной для Минска, или города, температура в которых за некоторый промежуток времени составляет 20% от средней температуры для Могилева.
4. Выведите следующие промежуточные итоги:
– среднее количество осадков данного вида с учетом данного города и конкретной даты;
– суммарное и среднее количество осадков данного вида для конкретного города;
– количество случаев определенного направления ветра с учетом конкретной даты;
– средние значения температуры и давления для конкретного города с учетом конкретной даты;
– средняя сила ветра определенного направления для конкретного города.
5. Используя консолидацию, определите среднее количество осадков, средние значения температуры и давления, для некоторых городов области.
Вариант 10
1. Составьте таблицу Спортивные достижения студентов.
2. Используя Пользовательский автофильтр, определить:
– студентов конкретного года рождения, оценки которых за спортивные нормативы больше. Отсортировать данные сначала по факультету, затем – по фамилиям студента;
– студентов конкретного года рождения, которые сдали определенный норматив. Отсортировать эти данные сначала по году поступления студентов, а затем по результатам сдачи;
– студентов конкретного факультета, которые сдали определенный норматив. Отсортировать эти данные снам по курсу, а затем – по результатам сдачи;
– студентов конкретного курса, оценка которых за сдачу спортивного норматива больше 2, но меньше либо равна на 4. Отсортировать полученные данные сначала по факультету, а затем – по оценке.
3. Используя Расширенный фильтр, определить:
– студентов одного года рождения, обучающихся на математическом, физико-техническом или экономическом факультетах, сдавших с оценкой "4" бег на 100 м или с оценкой "5" прыжки в длину;
– студентов первого или второго курсов исторического и юридического факультетов, оценки которых за спортивный норматив больше либо равны средней оценке для всех студентов;
– студентов с максимальными показателями (оценками) по всем спортивным нормативам для каждого курса факультета;
– студентов с неудовлетворительными оценками для всех курсов и факультетов;
– студентов данного года рождения и данного года поступления, сдавших бег на 100 м или прыжки в длину с оценкой "4" и выше.
4. Выведите следующие промежуточные итоги:
– средний балл по факультету;
– суммарный и средний балл для каждого спортивного норматива;
– количество студентов, приходящихся на каждый год поступления;
– средний результат по каждому курсу.
Создание макросов
