Обработка данных с помощью средств MS Excel (стр. 7 из 12)
6.9.8. Фильтрация списка с использованием сложных критериев
В данном случае должна быть выполнена следующая процедура:
1. Создать область критериев таким образом, чтобы она не мешала дополнению и расширению списка. Область критериев представляет собой минимум 2 строки, в первой из которых содержатся названия полей из заглавной строки списка, а в остальных строках указываются критерии поиска.
• Для установки нескольких критериев для одного поля (логическое И), в интервал критериев должно быть включено несколько столбцов с названием этого поля.
• Если на экран надо вывести записи, удовлетворяющие одному из критериев (логическое ИЛИ), то ввод условий производится в разные строки одного столбца.
2. Установить курсор в любую ячейку списка и задать команду Данные ® Фильтр, а затем выбрать пункт Расширенный фильтр.
• Включить параметр Фильтровать список на месте, если результат фильтрации будет располагаться на том же месте, где и сам список или параметр Скопировать результат в другое место, если результат нужно поместить в целевую область. Целевую область тоже следует располагать так, чтобы избежать конфликтов с частями таблицы, выделенными под список и критерии. В первой строке целевой области следует привести имена полей, содержимое которых нужно увидеть в найденных записях (порядок и количество полей может быть произвольным).
• В поле Диапазон критериев указать диапазон тех ячеек, где размещается область критериев.
• Если требуется поместить результат в целевую область, то в поле Поместить результат в диапазон следует указать диапазон, содержащий заголовок целевой области.
• Нажать кнопку ОК.
Для восстановления списка следует выбрать команду Данные ® Фильтр ® Показать все.
6.9.9. Сводные таблицы
Сводные таблицы предоставляют еще один способ обработки больших списков данных. С помощью сводной таблицы можно быстро извлечь из больших баз данных необходимую информацию, благодаря ее возможности одновременно выполнять различные операции (подведение итогов, сортировку и фильтрацию).
Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц.
Перед построением сводной таблицы необходимо убрать все ранее созданные промежуточные итоги и наложенные фильтры.
1. Установите курсор в любую ячейку списка и выберите команду Данные - Сводная таблица.
2. В открывшемся диалоговом окне Мастер сводных таблиц отметьте опцию в списке или базе данных МS Ехсеl.
3. Далее определите диапазон, с которым будет работать Мастер сводных таблиц. Щелкните на кнопке Далее.
4. В следующем окне определите, значения каких полей списка будут использоваться в качестве заголовков строк (зона Строка), каких — в качестве заголовков столбцов (зона Столбец) и каких — в качестве данных (зона Данные), по которым следует подвести необходимые итоги. (По умолчанию предлагается просуммировать значения выбранного поля. Для того чтобы изменить способ обработки данных по этому полю необходимо дважды щелкнуть по образовавшемуся в зоне Данные полю и выбрать; нужную операцию). В зону Страница помещается кнопка поля, по которому предполагается фильтровать данные. В каждой зоне может быть несколько кнопок. Для того, чтобы в новой таблице получить только итоговые значения, следует все зоны, кроме зоны Данные, оставить пустыми.
5. Далее определяется место, в которое будет помещена разработанная сводная таблица (например, ячейка нового листа). Там же определяются параметры сводной таблицы: следует ли выводить общие итоги по строкам и по столбцам.
6. После нажатия на кнопку Готово в указанном месте появляется таблица со сводными данными. В левом верхнем углу таблицы располагается кнопка с полем, помещённым в зону Страница. По умолчанию в таблице отображается вся информация по этому полю. Используя выпадающий список значений данного поля (в соседней справа ячейке), можно указать значение для фильтрации.
Используя панель инструментов Сводные таблицы, можно изменять вид сводной таблицы.
7. РЕКОМЕНДУЕМАЯ ЛИТЕРАТУРА
1. Практикум по экономической информатике. Учеб. Пособие: ч. 1/ Под ред. Е. Л. Шуремова и др.
2. Информатика. Базовый курс. Под ред. Симоновича. — СПб.: Питер, 2002
3. Гусева О. Л., Миронова Н. Н.Практикум по Excel. — М.: Финансы и статистика, 1997
4. Нельсон, Стивен Л. Анализ данных в Microsoft Excel для «чайников». — М.: Издательский дом «Вильямс», 2003
Приложение 1
ОБРАЗЕЦ ОФОРМЛЕНИЯ ТИТУЛЬНОГО ЛИСТА
МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ ОТКРЫТЫЙ УНИВЕРСИТЕТ
Филиал в г. Воскресенске
Кафедра Прикладной математики
Контрольная работа
по дисциплине «Информатика»
на тему:
_____________________________________________
Выполнил(а) студент (ка)
__________________форме обучения
специальности ___________________
___________курса, _________группы,
шифр: __________________________
______________ _________________
| (подпись) | (ФИО) |
Руководитель работы
______________________ _______________ ________________
| (ученая степень, звание, должность) | (подпись) | (ФИО) |
200_ – 200_ уч. год
Приложение 2
Варианты заданий к контрольной работе
по теме «Обработка данных с помощью средств MS Excel»
Вариант 1
Используя соответствующие финансовые функции, решите следующие задачи.
1. Определите, какая сумма окажется на счете, если вклад размером 900 руб. положен под 9 % годовых на 19 лет, а проценты начисляются ежеквартально.
2. Какая сумма должна быть выплачена, если 6 лет назад была выдана ссуда 1500 руб. под 15 % годовых с ежемесячным начислением процентов.
3. Взносы на сберегательный счет составляют 200 руб. в начале каждого года. Определите, сколько будет на счете через 7 лет при ставке 10 %.
4. Есть два варианта вложения средств в сумме 300 тыс. руб. в течении 4 лет: в начале каждого года под 26% и в конце каждого года под 38 % годовых. Определите, сколько денег окажется на счете для каждого варианта через 4 года.
5. Ссуда в 5000 руб. погашается ежемесячными платежами по 141,7 руб. Через сколько лет произойдет погашение ссуды, если годовая ставка процента 16 %.
6. Какую сумму необходимо положить на депозит под 16,5 %, чтобы через 3 года получить 44 тыс. руб. при полугодовом начислении процентов.
7. По сертификату, погашаемому через 3 года выплатой в 250 тыс. руб., начисляются проценты раз в полгода. Определите цену продажи, если номинальная ставка 38 %.
8. Определите необходимую сумму текущего вклада, чтобы через 12 лет он достиг 5000 руб., если процентная ставка по нему составляет 12 %.
9. Рассматриваются два варианта покупки дома: заплатить сразу 100000 руб. или платить в рассрочку в течении 15 лет по 940 руб. ежемесячно. Какой вариант выгодней, если ставка процента 8 % годовых.
10. Какую сумму нужно ежемесячно вносить на счет, чтобы через 3 года получить 10 млн. руб., если годовая ставка 18,6 %.
11. Определите ежемесячные выплаты по займу в 10 млн. руб., взятому на 7 месяцев под 9 % годовых.
12. Определите величину ежегодной амортизации оборудования начальной стоимостью 8000 тыс.руб., если срок эксплуатации его 10 лет, а остаточная стоимость 500 тыс. руб. Выполнить расчеты, используя функцию АПЛ.
Вариант 2
1. Создать таблицу, содержащую сведения о сотрудниках предприятия. Названия колонок: «Фамилия», «Должность», «Отдел», «Дата поступления на работу», «Стаж работы», «Зарплата», «Надбавка», «Премия», «Всего начислено», «Пенсионный фонд», «Налогооблагаемая база», «Налог», «Выплатить». Колонки «Фамилия», «Должность», «Отдел», «Дата поступления на работу», «Зарплата» заполнить произвольными данными, при этом в колонке «Зарплата» использовать значения от 500 до 3000 р., в колонке «Должность» использовать 5-6 названий (например, техник, инженер, экономист, водитель и т.д.), в колонке «Отдел» использовать 3-4 названия (например, бухгалтерия, отдел кадров, транспортный отдел, конструкторский отдел). Значения в остальных колонках рассчитать по формулам:
Стаж работы = (Текущая дата — Дата поступления на работу)/365.
Результат округлить до целого.
0, если стаж работы меньше 5 лет;
Надбавка = 5 % от зарплаты, если стаж работы от 5 до 10 лет;
10% от зарплаты, если стаж работы больше 10 лет.
Премия = 20 % (Зарплата + Надбавка).
Всего начислено = Зарплата + Надбавка + Премия.
Пенсионный фонд = 1 % от «Всего начислено».
Налогооблагаемая база = Всего начислено — Пенсионный фонд.
| Налог = |
12 % от Налогооблагаемой базы, если Налогооблагаемая база меньше 1000 руб.
20 % от Налогооблагаемой базы, если Налогооблагаемая база больше 1000 руб.
Выплатить = Всего начислено — Пенсионный фонд — Налог.
В таблице должно быть не менее 10 строк.
2. Организовать таблицу как базу данных: константы (проценты премии, налога, пенсионного фонда, текущую дату и т.д.) расположить выше шапки таблицы, т.е. имен полей так, чтобы между константами и шапкой оставалась хотя бы одна пустая строка, между именами полей и первой записью не должно быть ни одной пустой строки.
3. Присвоить рабочему листу имя Сведения о сотрудниках. Это же название можно использовать в качестве заголовка таблицы.
4. Используя форму данных, добавить в список еще 10 записей.
5. Используя форму данных, выполнить поиск записей по следующим критериям:
· заданная должность;
· заданный отдел;
· стаж работы больше заданного;