Главная/Стили/Условное форматирование/Правила выделения
ячеек/Другие правила.
Данная команда позволяет пользователю создавать свои правила выделения ячеек. Кроме этого, имеется еще множество встроенных правил выделения, доступных по той же команде.
Задача о транспортной компании.
Обработать совокупные данные о грузоперевозках транспортной компании за 2009 год, применяя условное форматирование.
Таблица 8
Объем грузоперевозок за 2009 год (в тыс.$)
| 1 квартал | 2 квартал | 3 квартал | 4 квартал | ||
| Москва | 3,7 | 4,1 | 3,5 | 3,2 | |
| Казань | 2,3 | 2,3 | 3,1 | 3,5 | |
| Саратов | 1,2 | 0,4 | 3,2 | 2,7 | |
| Краснодар | 4,3 | 2,4 | 3,6 | 4,1 | |
| Владимир | 5,6 | 4,7 | 4,3 | 3,6 | |
| Новороссийск | 6,3 | 4,8 | 4,9 | 5,5 | |
| Чебоксары | 3,4 | 5,4 | 4,2 | 3,9 | |
| За 2009 г. | |||||
| суммарно | |||||
| максимум | |||||
| минимум | |||||
| среднеквартальное | |||||
Таблица 9
Процент перевозок (100% –суммарно по кварталу)
1 квартал 2 квартал 3 квартал 4 квартал
Москва
Казань
Саратов
Краснодар
Владимир
Новороссийск
Чебоксары
По р яд о к раб о ты :
1. Перенести на рабочий лист таблицы 8 и 9 вместе с данными.
2. Создать формулы для заполнения данными строк «суммарно», «максимум», «минимум», «среднеквартальное».
3. Получить итоговые данные в столбце «За 2009 год». Они должны быть представлены в числовом формате с одним знаком после запятой.
4. На основании имеющихся и полученных данных построить:
a. диаграмму (тип – "график с маркерами"), сравнивающую изменение объема перевозок по направлениям за 1, 2, 3 и 4 кварталы одновременно (каждый ряд данных отображает объемы перевозок на протяжении одного квартала по всем направлениям); заголовок и подписи данных не показывать, легенда должна отражать подписи рядов: 1квартал, 2 квартал и т.д.
b. диаграмму (тип – "круговая"), отражающую долю перевозок каждого квартала 2009 года (суммарно) в сумме перевозок за год; секторы
должны быть подписаны кварталами, легенду показывать не обязательно;
заголовок: "Объем перевозок за 2009 год по кварталам".
5. Задайте условный формат для верхней части таблицы 8, в результате применения которого ячейки, значение которых меньше среднего значения за год,были бы помечены светло-розовым фоном исиним жирным шрифтом, а ячейки, значение которых больше среднего значения за год,были бы помечены светло-голубым фоном и краснымипунктирными границами.
6. Заполните таблицу 9 данными, полученными в результате вычисления процента перевозок, принимая за 100% суммарное количество перевозок за квартал (см. расчеты таблицы 8). При этом формула для вычисления процента должна быть введена только в одну ячейку
(например, Москва-1 квартал), а в остальные скопирована. Чтобы это было возможным, примените смешанные ссылки. Формулу для вычисления создайте самостоятельно, используя известные соотношения в пропорциях.
Данные должны быть отражены в процентном формате с одним десятичным знаком после запятой.
7. Примените условный формат к полученным данным. В
результате ячейки, содержащие значения менее 10%, должны быть помечены синим шрифтом, а ячейки, содержащие значения более 20%,
должны быть помечены красным шрифтом.
Лабораторная работа № 9. Работа с функциями Даты и Времени
Методические указания
В таблице 10 перечислены некоторые функции, которые входят в категорию Дата и Время (Data&Time).
| Таблица 10 | |
| Функция | Описание |
| СЕГОДНЯ (TODAY) | Возвращает текущую дату в формате даты |
| ТДАТА (NOW) | Возвращает текущую дату в формате текущей даты и |
| времени | |
| ДЕНЬНЕД | Преобразует дату в числовом формате в номер дня недели, |
| [WEEKDAY] | если Тип не указан или равен 1, то первым днем недели |
| считается воскресенье, последним (7-м) – суббота, если Тип | |
| равен 2, первый день недели – понедельник. | |
| ДНЕЙ360 | Вычисляет количество дней между двумя датами, если |
| считать год равный 360 дням, каждый месяц равен 30 дням. | |
Задание 1
1. Создайте таблицу по образцу (таблица 11):
Таблица 11
Текущая дата
Дата дня Вашего рождения
День недели Вашего рождения
Количество дней между текущей датой и датой Вашего рождения, в году 360 дней.
2. Во втором столбце таблицы введите необходимые данные и формулы.
Задание 2
1. Создайте таблицу по образцу (таблица 12).
2. Введите данные в столбцы «Дата», «Сумма», «Дата оплаты» по своему усмотрению.
3. В столбце срок оплаты произведите вычисления с помощью функции ДНЕЙ360.
4. В столбце «Пени» произведите вычисление пени. Пени начисляются в случае, если оплата произведена в срок более 5 дней в
размере 7% от начисленной суммы за каждый просроченный день.
Вычисления проводить с помощью функции ЕСЛИ.
5. Подсчитайте сумму к оплате в столбце «Всего к оплате».
6. Постройте диаграмму, показывающую доли каждого заказчика
в доходе транспортной компании.
7. С помощью фильтра покажите данные только о тех заказчиках,
у которых есть пени. Скопируйте эти данные в отдельный диапазон.
Постройте диаграмму, сравнивающую размеры выплаченных пени
должников.
Таблица 12
| Оплата услуг транспортной компании | |||||||
| Пени | 7% | ||||||
| Заказчик | Дата | Сумма | Дата оплаты | Срок оплаты | Пени | Всего к | |
| оплате | |||||||
| ИП Соколов | |||||||
| ОАО "Мечта" | |||||||
| АО "Горняк" | |||||||
| ИП Никонов | |||||||
| ИП Сорокина | |||||||
| ИП Редькин | |||||||
| ОАО "Мечта" | |||||||
| АО "Горняк" | |||||||
Подготовка к промежуточной аттестации Методические указания
В данном блоке приводится примерный вариант Контрольной работы №1, предназначенной для проверки знаний, сформированных в первой половине семестра. Предполагается, что данную работу студенты
выполняют абсолютно самостоятельно. Для проверки правильности выполнения графического задания приводятся эскизы графиков и поверхностей.
Вариант 1
Задача 1. Рассчитать стипендию студентам по итогам сессии.
Стипендия начисляется следующим образом: 2 минимальные стипендии,
если средний балл студента не менее 4,25, и 1 минимальная стипендия,
если средний балл студента менее 4,25, но более 3,9. Минимальная стипендия является постоянной величиной и содержится в отдельной ячейке таблицы (таблица 13).
| Таблица 13 | |||||||||||||
| Минимальная | |||||||||||||
| стипендия | 400р. | ||||||||||||
| Фамилия | Статистика | Экология | Математика | Средний | Стипендия | ||||||||
| балл | |||||||||||||
| Гусев | |||||||||||||
| Мамедова | |||||||||||||
| Николаева | |||||||||||||
| Морозов | |||||||||||||
| Редькин | |||||||||||||
| Соколова | |||||||||||||
| Задача 2. Построить график функции при x 1,5;1,5с шагом0,1: | |||||||||||||
| 2x | |||||||||||||
| 3 sin | , | x 0 | |||||||||||
| g 1 cos2 x | |||||||||||||
| x 0 | |||||||||||||
| 2 1 2x, |
Вариант 2
Задача 1. Автоматизировать процесс выдачи призов покупателямторгового центра, используя функции просмотра (ВПР или ГПР). Призы зависят от суммы покупки и выдаются следующим образом: сумма покупки от 1000 до 2500 рублей – кружка, от 2500 до 4000 рублей – скатерть, от 4000 до 6000 – зонт, более 6000 руб. – VIP-карта. При сумме покупки менее 1000 рублей призы не выдаются.
| Таблица 14 | ||||
| ФИО | Цена | Кол-во | Сумма | Приз |
| Комарова | ||||
| Соболев | ||||
| Корнев | ||||
| Носова | ||||
| Маслова | ||||
| Коркина | ||||
| Сорокин |
Задача 2. Построить поверхность при при x, y 1;1,шаг0,1
| 2y | , | x | 0,5 | |||||||
| x | ||||||||||
| z | . | |||||||||
| 2y x2 | , | x | 0,5 | |||||||
Вариант 3
Задача 1
1. Создайте таблицу по образцу (таблица 15).
2. Заполните столбец Цена (руб).
3. Добавьте столбцы Наценка и Цена с наценкой.
4. В столбце Наценка с помощью логических функций сделайте наценку 10% на те товары, чья стоимость в рублях превышает 2000 руб., и наценку 18% на те, чья стоимость менее 1500 руб.
5. Выполните вычисления в столбце Цена с наценкой.
| Таблица 15 | |||
| 1$= | 35,0р. | ||
| Наименование товара | Цена ($) | Цена (руб) | |
| Стул для компьютера | |||
| Стеллаж | |||
| Кресло рабочее | |||
| Стол приставной | |||
| Стойка компьютерная | |||
| Стол рабочий | |||
| Тумба выкатная | |||
| Шкаф офисный | |||
Задача 2. Построить график функции при x 2;1,5с шагом0,1:
| 3x | , | x 0 | ||||||||
| 1 x | ||||||||||
| g | ||||||||||
| 2x | ||||||||||
| , x 0 | ||||||||||
| 1 x | ||||||||||
| . |
Вариант 4
Задача 1.Автоматизировать процесс присвоения квалификацииоператорам ПК с помощью функций просмотра (ВПР или ГПР).
Квалификация присваивается в зависимости от числа воспроизведенных печатных знаков минуту следующим образом: от 40 до 80 знаков – «третий разряд», от 80 до 110 знаков – «второй разряд», от 110 до 150 знаков –
«первый разряд», более 150 знаков – «высший разряд». При количестве знаков менее 40 разряд не присваивается.
Таблица 16
Задача 2. Построить поверхность при при x, y 1;1,шаг0,1
| x 2y, | y | 0,5 | ||||||
| cos | ||||||||
| z | sin x2, | |||||||
| y | y | 0,5 | ||||||
Ответы на вторые задания
Вариант 1
Y
-2 -1,5 -1 -0,5 0 0,5 1 1,5 2
Вариант 2
-1
-2
| -1-0,280,28 | 0,68 | 0,92 | 0,92 |
| 0,68 | 0,28 | 0,28 | -1 |
| - |
| 2-3 | |||
| 0,8 | 1-2 | ||
| 0-1 | |||
| 0,2 | |||
| -0,4 | -1-0 | ||
| -1 | -2--1 | ||
Вариант 3
Вариант 4
Лабораторная работа №10. Работа с простейшей базой данных в
Excel
Простейшая база данных в Excel – это список заданной структуры,
представляющий собой прямоугольную таблицу. Структура списка определяется именами полей (столбцов таблицы) и типами данных,
хранящихся в них. Правила создания списков не допускают внутри списка пустых строк, столбцов, столбцов без заголовков, столбцов с одинаковыми заголовками. Совокупность данных, хранящихся в одной строке таблицы,
называется записью. Запись является неделимым элементом списка, то
есть при выполнении операций над списками сохраняется его целостность.
Операции над списками: сортировка, фильтрация, подведение промежуточных итогов и другие выполняются с помощью команд меню
Данные и Вставка.
Порядок работы:
1. Создать таблицу по образцу на Листе 1. Озаглавьте его ярлык
«Расчет стипендии» (рис. 29).
Рис. 29. Исходные данные для задачи
2. С помощью логических функций ЕСЛИ() и И() рассчитайте стипендию. Минимальная стипендия начисляется студентам, сдавшим все зачеты и все экзамены с оценкой не ниже 4. Повышенная стипендия (на
30% от минимальной) начисляется студентам, сдавшим все зачеты и все экзамены на 5. Остальные студенты не получают стипендию.
3. Скопируйте данные расчетной таблицы и вставьте их на Лист 2 (его ярлык назовите «Работа со списком») с помощью специальной вставки (Главная/Вставить/Специальная вставка/ флажок «Данные»).
4. Выполните сортировку списка по: а) столбцу «Стипендия»,
б) столбцу «Группа», в) столбцу «ФИО». Выполните многоуровневую
сортировку с помощью Данные/Сортировка по столбцам «Группа» и «ФИО» одновременно. Результаты сортировки копируйте и помещайте на Листе 3 (ярлык назвать «Результаты сортировки»).
5. Находясь на листе «Работа со списком», выполните фильтрацию данных с помощью простого фильтра (Данные/Фильтр) по следующим запросам: а) студенты, получающие минимальную стипендию;
б) студенты, не сдавшие зачет по математике; в) студенты 103 группы,
имеющие по статистике 4; г) студенты 102 группы, не имеющие 3;
д) студенты, не имеющие ни одной четверки; е) студенты 102 группы,
фамилии которых начинаются на М или Н; ж) студенты, имена которых начинаются на А или О и имеющих 3 по экономике; з) студенты, не сдавшие хотя бы один зачет. Результаты фильтрации копируйте и помещайте на Листе 4 (ярлык назвать «Результаты фильтрации»).
6. Находясь на листе «Работа со списком», создайте условия
(критерии) расширенного фильтра (Данные/Дополнительно) по каждому из запросов п.5: а)-в), д)-ж). Результаты фильтрации помещать в диапазон под каждым условием.
7. Создайте копию листа «Работа со списком», переименуйте его ярлык в «Итоги». Подведите итоги в виде среднего балла по каждому экзамену в каждой группе (используйте команду Данные/
Структура/Промежуточные итоги).
8. Вернитесь на лист «Работа со списком» и сделайте еще одну его копию, назовите ярлык «Итоги-стипендия». На новом листе подведите промежуточные итоги по суммарной стипендии по каждой группе.
9. Находясь на листе «Работа со списком», создайте сводную таблицу, показывающую, сколько человек в каждой группе и по потоку в целом имеют минимальных и повышенных стипендий, сколько не получают стипендию (используйте команду Вставка/Сводная таблица).
Задание для самостоятельной работы Методические указания
Задание направлено на закрепление навыка подведения промежуточных итогов. Важно помнить, что промежуточные итоги подводятся только после сортировки списка по тому полю, которое будет управлять подведением итогов. Это значит, что после сортировки и задания типа итогов и тех полей, в которых их нужно подвести, итоги будут вычисляться каждый раз, когда значение в управляющем поле будет изменяться. По умолчанию итогом считается сумма, но нередко требуется найти количество или среднее значение, другие типы итогов также доступны пользователю.
Промежуточные итоги являются альтернативой построения сводных таблиц и диаграмм.
1. Создайте таблицу по образцу (таблица 17).
2. Выполните вычисления в столбце «Длина участка»
3. Найдите суммарную длину участков пути, пройденных автомобилями каждого производителя, и суммарное время движения автомобилей каждого производителя.
4. Найдите среднюю длину участков пути, пройденных автомобилями каждого производителя, и среднее время движения автомобилей каждого производителя.
5. Найдите суммарную длину участков пути, пройденных автомобилями каждого типа, и суммарное время движения автомобилей каждого типа.
6. Найдите среднюю длину участков пути, пройденных автомобилями каждого типа, и среднее время движения автомобилей каждого типа.
7. Найдите суммарную длину участков пути, пройденных
однотипными автомобилями каждого производителя (например,
грузовыми Fiat, легковыми Nissan и т.п.), и суммарное время движения
однотипных автомобилей каждого производителя.
8. Найдите среднюю длину участков пути, пройденных
однотипными автомобилями каждого производителя (например,
грузовыми Fiat, легковыми Nissan и т.п.), и среднее время движения однотипных автомобилей каждого производителя.
Таблица 17
Лабораторная работа №11. Консолидация данных Методические указания
Консолидация является простой и эффективной процедурой, во время которой происходит объединение однотипных данных и подведение итогов. Работа ведется в рамках группы команд «Данные».
Последовательность выполнения:
1. Создать новую рабочую книгу (4 листа). Присвоить листам книги имена: "Январь", "Февраль", "Март", "1квартал".
2. Разместить на листе "Январь" таблицу:
| ФИО | Сделки | Объем |
| Иванов И.И. | ||
| Антонов А.А. | ||
| Медведев М.М. | ||
3. Разместить на листе "Февраль" таблицу:
| ФИО | Объем | Сделки |
| Сидоров С.С. | ||
| Иванов И.И. | ||
4. Разместить на листе "Март" таблицу:
| ФИО | Сделки | Объем |
| Иванов И.И. | ||
| Сидоров С.С. | ||
| Антонов А.А. | ||
| Медведев М.М. | ||
5. Перейти на лист "1 квартал" и выделить ячейку А1. Выполнить команду Данные/группа Работа с данными/Консолидация.
6. В поле "Функция" установить "Сумма".
7. В поле "Ссылка" установить фокус ввода и по очереди выделять диапазоны для консолидации, находящиеся на предыдущих листах. Выделение каждого диапазона заканчивать нажатием кнопки
"Добавить", при этом адрес диапазона будет помещен в окно "Список диапазонов".
8. Установить флажки "Подписи верхней строки" и "Значение левого столбца". Флажок "Создавать связи с исходными данными" пока не устанавливать. Нажать "ОК". Результат консолидации появится на листе
"1 квартал".
9. Исследуйте полученные результаты. Например, измените количественные данные (объем сделок или их количество по какому-либо агенту) на листе "Март", проверьте, изменились ли при этом данные листа
"1 квартал" (если все верно сделано, то изменений не будет). А теперь выполните команду Данные/Консолидация и сразу "ОК", убедитесь в обновлении таблицы на листе "1 квартал".
10. Выделите на листе "1 квартал" ячейку А1 и выполните команду
Данные/Консолидация и установите флажок"Создавать связи с исходнымиданными" и "ОК". Внесите количественные изменения в исходные данные листа "Январь" и проследите за тем, как изменились итоги на листе
"1 квартал", теперь они будут обновляться автоматически.
Лабораторная работа №12. Финансовые операции с элементарными потоками платежей (по А.В.Елесину, [3])
Методические указания
Простейший (элементарный) денежный поток состоит из одной выплаты и последующего поступления либо разового поступления с последующей выплатой, разделенных n периодами времени (например,
лет). Пример: срочные депозиты, единовременные ссуды, некоторые виды ценных бумаг.
Операции с элементарными потоками платежей характеризуются следующими параметрами (в скобках приведены функции Excel,
предназначенные для их нахождения):
FVn –будущая стоимость потока за n периодов(БС);
PVn –современная стоимость потока за n периодов(ПС);
CFt –величина потока платежей в периоде(ПЛТ); r –процентная ставка(СТАВКА);
n–количество периодов проведения операции(КПЕР).
Для вычисления характеристик финансовых операций с элементарными потоками платежей удобно использовать функции БС,
КПЕР, СТАВКА, ПС (таблица 18).
| Таблица 18 | ||||||||||||
| Название | Вычисляемая | Синтаксис функции | ||||||||||
| функции | величина | |||||||||||
| БС | Будущая | величина | БС (ставка; | число периодов; плт; пс; | ||||||||
| потока платежей | [тип]) | |||||||||||
| КПЕР | Количество периодов | КПЕР (ставка; выплата; пс; бс; [тип]) | ||||||||||
| выплат | ||||||||||||
| СТАВКА | Процентная ставка за | СТАВКА (кпер; плт; | пс; | бс; [тип]; | ||||||||
| период выплат | [предположение]) | |||||||||||
| Современная | ПС (ставка; кпер; плт; бс; [тип]) | |||||||||||
| ПС | ценность | потока | ||||||||||
| платежей | ||||||||||||
| Основные аргументы функций приведены в таблице 19. | ||||||||||||
| Таблица 19 | ||||||||||||
| Аргумент | Описание | |||||||||||
| Ставка | Процентная ставка за период | |||||||||||
| КПЕР (Число периодов) | Количество | периодов | совершения | |||||||||
| операции | ||||||||||||
| Плт | Величина периодического платежа | |||||||||||
| ПС | Начальная сумма | |||||||||||
| БС | Будущая стоимость | |||||||||||
| Тип | Тип начисления процентов. Ставится | |||||||||||
| одно из двух значений – 0 или 1 (1 в | ||||||||||||
| случае, если начисление процентов в | ||||||||||||
| начале | соответствующего | периода, | ||||||||||
| 0 – в конце периода) | ||||||||||||
| Пример | оформления | листа | для | решения задач | по | расчету |
параметров операций с элементарными потоками платежей приводится на
рисунке 30.
Рис. 30. Шаблон для решения задач о простейших потоках
Часть ячеек заполняется базовыми значениями, другие представляют собой расчетные величины.
При заполнении данной формы необходимо обращать внимание на следующее: все аргументы, означающие выплачиваемые суммы должны вводиться отрицательными значениями, и обратно, если аргумент представляет собой получаемые суммы, его значение положительно.
Любой элементарный поток платежей должен обязательно содержать оба значения.
Задание 1. Расчет будущей величины элементарного потокаплатежей.
Пусть сумма в 10 000 рублей помещена в банк на депозит сроком на десять лет. Ставка по депозиту 20% годовых. Проценты начисляются один раз в год. Какая сумма будет находиться на счете в конце срока?
Порядок работы:
1. Подготовить таблицу-форму для расчетов.
2. В соответствующие ячейки подготовленной таблицы-формы ввести исходные данные – величины PV, n, r.
3. В ячейку, зарезервированную для расчета величины FV ввести функцию БС, используя в качестве аргументов ссылки на соответствующие ячейки.
Указание.Обратите внимание,что аргумент«Ставка»определяетсякак отношение годовой процентной ставки из условия и количества начислений в году. Аргумент «Число_периодов» определяется как
произведение срока проведения операции и количества начислений за год.
Такой вариант записи аргументов учитывает случаи, когда начисление процентов осуществляется чаще чем 1 раз в год.
Результат решения задачи приведен на рисунке 31.
Рис. 31. Результат решения задачи 1
Задание 2. Расчет современной величины элементарного потокаплатежей.
Выплаченная по 4-летнему депозиту сумма составила 14 641 руб.
Определить первоначальную величину вклада, если ставка по депозиту равна 10% годовых.
Указания к решению:Использовать для расчетов функцию ПС.
Результат решения задачи: –10 000,00р.
Задание 3. Расчет годовой процентной ставки.
Страховая компания реализует полисы стоимостью 2000 денежных единиц. Определить годовую доходность данной операции, если по условиям договора предполагается выплата 10 000 денежных единиц по истечении 5 лет. Банковская практика предполагает ежеквартальное начисление процентов.
Указания к решению:
1. Использовать для решения таблицу-форму предыдущей задачи.
2. Для вычисления годовой процентной ставки использовать функцию СТАВКА. Учесть, что данная функция позволяет определить процентную ставку за период начисления процентов (в данном случае – за
квартал). Искомый показатель будет получен путем умножения найденного значения на количество начислений за год (здесь – 4).
Результат решения задачи: 33,5%.
Задания для самостоятельной работы Задание 4.За какой срок в годах сумма,равная75 000у.е.достигнет
200 000 у.е. при ставке 15% годовых, начисляемых ежеквартально?
Задание 5. Коммерческий банк принимает вклады от населения наследующих условиях:
a) с выплатой 12% годовых, начисляемых ежегодно;
b) с выплатой 11,5% годовых, начисляемых ежеквартально.
Какой вид вклада Вы предпочтете? Обоснуйте свой ответ с помощью расчетов для суммы 1 000 000 руб.
Задание 6. На какую сумму следует заключить договор состраховой компанией, чтобы через 5 лет обладать суммой в 20000 у.е.,
если процентная ставка равна:
a) 5%;
b) 15%?
Лабораторная работа №13. Денежные потоки в виде серии равных платежей (аннуитеты) (по А.В.Елесину, [3]).
Методические указания
Поток платежей, все элементы которого распределены во времени так, что интервалы между любыми двумя последовательными платежами постоянны, называют финансовой рентой, или аннуитетом.
В финансовой практике часто встречаются так называемые простые, или обыкновенные, аннуитеты, которые предполагают получение или выплаты одинаковых по величине сумм в течение всего срока операции в конце каждого периода (года, полугодия, квартала, месяца).
Простой аннуитет обладает двумя важными свойствами: все его элементы равны между собой и отрезки времени между выплатой/получением сумм CF одинаковы. В отличие от разовых платежей здесь необходимы все характеристики денежных потоков: FV,
РV, CF, r, n.
Автоматизация расчетов ведется с помощью функций EXCEL БС,
ПС, КПЕР, СТАВКА и функции определения размера периодического платежа ПЛТ, аргументы которой приведены в таблице 20.