ПЗ-3. Области ввода. Вклады. Условное форматирование. Номера недель.

Таблица умножения с областью ввода.

Для области ввода надо на листе ТабУмн добавить но­вые строки. Выделим строки с 1 по 6, в выделенной области щелкнем правой кнопкой мыши и выберем пункт контек­стного меню Добавить ячейки (Вставить - для Excel -2010). Появится 6 пустых строк в верхней части листа.

Самое главное – формулы в ячейках таблицы умножения, которая теперь начнется с 7 строки, автоматически будут пересчитаны (!). В бывшей ячейке В2 (теперь она имеет адрес В8), где была формула =$А2*В$1, появится формула =$A8*B$7. Соответственно изменятся другие формулы листа.

Заполним область ввода надписями, как на рисунке слева, для этого надо вво­дить значения в первом столбце (столбце А), объединять ячейки и задавать формат надписи.

Введем в ячейки D2:D5 единицы и свяжем область ввода с расчетной таблицей: формулы приведены на этом же рисунке. Размножим формулы из ячеек А9 (вниз) и С7 (вправо) до необходимого предела (17-я строка и столбец К). В итоге получим школьную таблицу умножения.

Теперь можно получить любую таблицу умножения для произвольных исходных дан­ных, которые можно ввести в диапазоне D2:D5: например таблица умножения для двоек.

Или для нечетных значений аргументов. Или для троек. Или для множителей, кратных 60.

Построив такие таблицы умножения, мы научились табулировать функцию двух переменных, параметры которых можно изменять. Этот прием можно применять для многих видов вычис­лений, например, для расчетов процентов по вкладу.

Таблица расчета процентов по вкладу.

Для таких расчетов необходимо знать процентную ставку по вкладу, срок размещения вклада и размер самого вклада.

Используем разработанную таблицу умножения с областью ввода. Скопируем ее на но­вый лист, назовем его Вклады. Изменим таблицу умножения (см.рисунок ниже): область ввода должна содержать следующие управляющие параметры:

Ø первоначальную сумму вклада;

Ø начальное значение процентной ставки по депозиту и шаг ее изменения;

Ø начальное значение периода времени и шаг его изменения.

Процентные ставки будут располагаться в столбце Процент, а периоды времени – в строке Годы. В области вычислений должны отображаться суммы, величина которых зависит от срока размещения вклада и от процентной ставки. Предполагаем, что процент по вкладу сложный, начисляется в конце года. В каждом следующем году сумма вклада увеличивается на сумму процента и из нового значения вклада вычисляется процент. При такой схеме сумма вклада на конец периода (P1) рассчитывается по формуле: P1=P0*(1+ r) ⁿ, где P0 – сумма, размещенная на депозите, r – ставка по депо­зиту, n – число периодов (лет). Фрагмент таблицы с расчетными формулами приведен ниже.

 

Изменение внешнего вида новой таблицы по отношению к исходной таблице умножения можно выявить из приведенных выше рисунков. Ниже указана последовательность созда­ния основной расчетной формулы в ячейке В10 вместо исходной формулы =$A8*B$7 и размножение ее на всю область значений. Это еще один способ заполнения формулами диа­пазона ячеек.

1. Выделить диапазон В10:К19. Ячейка В10 останется активной.

2. Ввести знак равенства, щелкнуть по ячейке D2 и нажатием функциональной клавиши F4 задать абсолютную ссылку на эту ячейку ($D$2).

3. Ввести знак умножения (*), открыть круглую скобку, набрать 1 и знак "+".

4. Щелкнуть по ячейке А10 и три раза нажать функциональную клавишу F4 – будет соз­дана смешанная ссылка на эту ячейку ($А10), закрыть круглую скобку. Такая смешанная ссылка означает абсолютную ссылку на столбец А.

5. Ввести знак возведения в степень (^), перейдя в английскую раскладку и нажав Shift+6.

6. Щелкнуть по ячейке В9 и дважды нажать на функциональную клавишу F4 – будет соз­дана смешанная ссылка на эту ячейку (В$9). Такая смешанная ссылка означает абсолют­ную ссылку на строку 9.

7. Завершить ввод формулы нажатием сочетания клавиш Ctrl+Enter. При этом формулами будет заполнен весь выделенный диапазон.

На заполнение формулами таблицы указанным способом уходит около 1 минуты.

 

Данная таблица позволяет изменять сумму депозита, размер процентной ставки, полу­чать результат на определенный временной период. Ниже приведена таб­лица расчетов в ре­жиме отра­жения результатов. Из нее видно, что, разместив вклад под 11% годовых, по­лучите удвое­ние вклада не ранее, чем через 7 лет (строка выделена курси­вом и ячейка обведена овалом).

 

 

Переход от отображения формул в ячейках таблицы к режиму отражения результатов расчетов производится следующим путем: Файлà Параметры à Дополнительно à Показывать формулы, а не их значения (поставить или снять «галочку»).

 

Платежи по займу.

В состав MS Office входит большое число шаблонов, макетов и образцов документов. Их можно найти в меню кнопки Пуск-Создать. В открывающемся окне име­ется несколько вкладок, каждая из которых открывает набор шаблонов определенной тема­тики (например, Базы данных, Презентации, Шаблоны оформления и др.).

Выберем шаблон Рассрочка (Файл à Создать à Образцы шаблонов à Рассрочка). Откроется книга Excel, в ко­торой будет один лист: Таблица рассрочки. Введем значения в область ввода и после щелчка по клавише Enter получим данные по займу. Параметр Всего в счет процентов пока­зывает сумму переплаты.

Если ввести 4 платежа в год (по кварталам) вместо 12, как в приведенной таблице, пере­плата составит уже 48361,48р. (на 2 с лишним тысячи рублей больше). Введя несколько соче­таний исходных данных можно выбрать оптимальный по каким-то критериям вариант займа.

 

Условное форматирование

В Excel имеется возможность наложить особый формат (шрифт, границы, цвет заливки и др.) на ячейку или блок - в зависимости от значения, появляющегося в ячейке.

На листе Данные ввести в блок А16:А40 арифметическую прогрессию: ряд чисел от 1 до 25.

Требуется наложить на диапазон А16:А40 формат: если число лежит в диапазоне от 1 до 10, то выводится курсивом; если от 11 до 20, то выводится полужирным курсивом в рамке; если больше или равно 21, то выводится красными цифрами на голубом фоне.

 

Выделить блок А16:А40. Выбрать меню: Главная à Условное форматирование à Создать правило à Форматировать только ячейки, которые содержат…

В диало­говом окне указать для 1 условия: значение между 1 и 10 (заполняется четыре поля ввода – рисунок выше), щелкнуть кнопку "Формат". Появится новое диа­логовое окно с тремя вкладками. На вкладке "Шрифт" вы­брать "курсив" и щелкнуть "ОК". По­сле этого снова вызвать окно условного форматирования и выбрать пункт «Управление правилами». Появится окно Диспетчера правил. Надо добавлять правила для других (указанных выше) диапазонов данных.

Условное форматирование можно задавать формулой. Перейдем на лист 13пт. Введем в Е15:Е45 прогрессию дат: в Е15 запишем 01.01.10, щелкнем по галочке (закончится ввод, поя­вится четыре цифры обозначения года), размножим ячейку вниз до 45 строки – появятся даты от 1 до 31 января 2010 года. Нужно выделить красным цветом ячейку, в которой будет отображаться число, соответствующее дню недели Воскресенье, и голубым цветом – суббот­ние дни.

Выделим диапазон Е15:Е45, выберем команду меню: Главная à Условное форматирование à Создать правило à Использовать формулу для определения форматируемых ячеек.

В диалоговом окне надо указать формулу, как показано на рисунке.

Щелкнуть кнопку " Ф ормат" (показана на рисунке выше), вкладку Заливка и выбрать голубой цвет, щелкнуть ОК. Снова вызвать окно условного форматирования и щелкнуть пункт Управление правилами, затем за­дать параметры (формулу и цвета) для второго условия (воскресенье), щелкнуть ОК и еще раз ОК. В диапазоне дат столбца Е красным цветом будут выделены воскресные дни, а голубым – субботние.

Для задания нескольких условий необходимо каждый раз заново вызывать окно условного форматирования или использовать Диспетчера (пункт Управление правилами), добавляя строки с формулами.

 

Номера недель.

Вычислим номера недель в году на листе 13пт. Считаем, что номер недели изменяется в понедельник. Поэтому надо определить, какой день месяца приходится на понедельник, и тогда для этого дня вычислить номер недели.

В ячейку F15 с помощью мастера функций введем формулу: =ЕСЛИ(ДЕНЬНЕД(E15;2)=1;"ПН";"") и размножим ее вниз.

В ячейку G15 введем фор­мулу:

=ЕСЛИ(F15="ПН";НОМНЕДЕЛИ(E15;2);"")

размножим ее вниз.

Эти формулы можно свести в одну и сразу вычислять номер недели:

=ЕСЛИ(ДЕНЬНЕД(E15;2)=1;НОМНЕДЕЛИ(E15;2);"")

В итоге получим значения в столбцах E, F, G – как на рисунке слева. Можно продолжить прогрессию дат в столбце Е до нужного нам предела, например, до 30 июня. Формулы в столб­цах F и G для увеличенного диапазона дат можно размножить двойным щелчком левой кнопки мыши по маркеру размножения из любой ячейки, содержащей формулу.

Условное форматирование ячеек (цвет дней субботы и воскресенья) размножается в столбце Е таким же способом (двойным щелчком мыши по маркеру размножения).

 

Для справки: после ПЗ-3 в книге должны быть листы:

Данные, График, 13пт, Титул, ТабУмн, Вклады.