Обработка данных с помощью средств MS Excel

    Функция ТЕНДЕНЦИЯ

    ТЕНДЕНЦИЯ(ИЗВЕСТНЫЕ  ЗНАЧЕНИЯ Y; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X; НОВОЕ  ЗНАЧЕНИЕ X; КОНСТ).

    В текущую ячейку возвращается новое  значение X, рассчитанное на основании  известных значений. Выполняется  линейная аппроксимация.

    ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y — это множество значений Y, которые уже известны для соотношения Y = mX + b.

    ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X — это необязательное множество значений X, которые уже  известны для соотношения y = mX + b.

    НОВЫЕ ЗНАЧЕНИЯ X — новые значения X, для  которых ТЕНДЕНЦИЯ возвращает соответствующие  значения Y.

    КОНСТ — это логическое значение, которое  указывает, требуется ли, чтобы константа b была равна 0.

6.3. ФУНКЦИИ ДАТЫ И ВРЕМЕНИ

    Функция текущей даты

    СЕГОДНЯ() — возвращает текущую дату компьютера.

    Функция текущей даты и  времени

    ТДАТА() — возвращает текущую дату и время  в числовом формате.

    Функция определения дня  недели

    ДЕНЬНЕД(ДАТА_КАК_ЧИСЛО;ТИП) — преобразует дату в числовом формате в номер дня недели. Если ТИП не указан или равен 1, то первым днем недели считается воскресенье, последним (7-м) — суббота. Если тип равен 2, первый день недели — понедельник.

    Пример  8

    Определить  день недели от даты рождения.

    Вернуться на Лист1 рабочей книги. В ячейку G8 ввести дату рождения в числовом формате: например, 14.09.1980. Пошаговыми действиями Мастера функций в ячейку G9 ввести формулу: =ДЕНЬНЕД(С8;2).

    В ячейке G9 получится число 7, что соответствует  «воскресенью».

    Функция определения количества дней между двумя  датами

    ДНЕЙ360(НАЧАЛЬНАЯ_ДАТА;КОНЕЧНАЯ^АТА;МЕТОД) - количество дней определяется как  разница между начальной и  конечной датами, исходя из 360 дней в  году. МЕТОД — задает использование  европейского стандарта (необязательный параметр).

    Пример  9

    Определить  количество дней от даты рождения по текущую  дату.

    Пошаговыми  действиями Мастера функций в  ячейку СЮ ввести формулу: =ДНЕЙ360(С8;СЕГОДНЯ()).

    Функция определения номера месяца

    МЕСЯЦ(ДАТА_КАК_ЧИСЛО) - преобразует дату в числовом формате  в номер месяца.

6.4. ЛОГИЧЕСКИЕ ФУНКЦИИ

6.4.1. Функция проверки условия

    ЕСЛИ(УСЛОВИЕ; ВЫРАЖЕНИЕ1; ВЫРАЖЕНИЕ2) - группа логических функций.

    В текущую ячейку заносится величина, вычисленная в соответствии с выражением 1, если условие (одно или несколько) истинно', в противном случае эта величина вычисляется по выражению 2.

    Пример 10.

    Скопировать в ячейки А11:А15 информацию из диапазона  А1:А5 примера 1. В зависимости от значений ячеек А11:А15 выполнить: если значение больше 1000, делим его на 100, если нет  делим на 10. Результат должен быть получен в ячейках В11:В15.

    Функция вводится сначала в ячейку В11, затем  копируется для всех ячеек до В15. В ячейку В11 вводим:

    =ЕСЛИ(А11>1000;А11/100;А11/10). Словами это условие можно  выразить так: «Если значение  в ячейке А11 больше 1000, то его  делим на 100; в противном случае  делим его на 10». Результат  деления получится в ячейке  В11 (там, куда вводилась функция  ЕСЛИ).

6.4.2. Использование функции И/ИЛИ

    Логические  функции И и ИЛИ предназначены  для проверки выполнения нескольких условий.

    1. Когда условия соединены логическим  И, результатом проверки нескольких  условий считается:

• значение ИСТИНА, если все условия имеют значение ИСТИНА;
• % значение ЛОЖЬ, если хотя бы одно условие имеет значение ЛОЖЬ.

    2. Когда условия соединены логическим ИЛИ, результатом проверки условий считается:

• значение ИСТИНА, если хотя бы одно из условий имеет значение ИСТИНА;
• значение ЛОЖЬ, если все условия имеют значение ЛОЖЬ.

6.4.3. Использование функции И

    ЕСЛИ (И (УСЛОВИЕ1; УСЛОВИЕ2); ВЫРАЖЕНИЕ1; ВЫРАЖЕ-НИЕ2).

    Вычисление  выражения 1 выполняется только при  истинности всех указанных условий; в противном случае вычисляется  выражение 2.

    Пример 11.

    Для каждой ячейки из диапазона А11:А15 примера 13 проверить условие: если значение ячейки {Аi} больше 900 и одновременно меньше 1500, то умножить его на 100, в противном случае оставить значение ячейки {А;} неизменным. Результат должен быть получен в ячейках С11:С15.

    В ячейку СП ввести: =ЕСЛИ(И (А! !>900; А11<1500);А11*10;А11).

    Сначала вызывается функция ЕСЛИ, затем из списка встроенных функций в левой  части строки формул вызывается функция  И для ввода логического условия. После завершения ввода условий  в соответствующие поля окна ИЛИ, следует щелкнуть указателем мыши в  конце строки формулы и продолжить ввод значений в окне ЕСЛИ. Из ячейки СИ формула копируется вниз в С12:С15.

6.4.4. Использование функции ИЛИ

    ЕСЛИ (ИЛИ (УСЛОВИЕ1; УСЛОВИЕ2); ВЫРАЖЕНИЕ1; ВЫРАЖЕНИЕ2).

    В случае истинности одного из условий (условия 1 или условия 2) расчет текущей величины выполняется по выражению 1; в противном  случае выполняется расчет по выражению 2.

    Пример 12.

    Для каждой ячейки из диапазона А11:А15 примера 13 проверить: если значение ячейки {А_i} больше или меньше 1000, то умножить его на 10, в противном случае (если равно 1000) оставить значение ячейки {А_i} неизменным. Результат должен быть получен в ячейках D11:D15.

    В ячейку D11 вводим:

    =ЕСЛИ(ИЛИ  (А11>1000;А11<1000);А11*10;А11).

    Сначала вызывается функция ЕСЛИ, затем из списка встроенных функций в левой  части строки формул вызывается функция  ИЛИ для ввода логического  условия. После завершения ввода  условий в соответствующие поля окна ИЛИ следует щелкнуть указателем мыши в конце строки формулы и  продолжить ввод значений в окне ЕСЛИ. Из ячейки D11 формула копируется в D12:D15.

6.5. ФУНКЦИЯ ПОИСКА ДАННЫХ В НЕКОТОРОМ ДИАПАЗОНЕ 

    ПРОСМОТР(ИСКОМОЕ  ЗНАЧЕНИЕ; ДИАПАЗОН) группа функций ссылки и массивы 
(1-й вариант — векторный просмотр; 2-й вариант — массив).

    Диапазон  представляет из себя блок, состоящий  из двух колонок. Поиск ведется по искомому значению в первой колонке  диапазона, а в текущую ячейку выбирается соответствующее значение из второй колонки диапазона. Если точное соответствие не обнаружено, то выбирается наибольшее значение в диапазоне, меньшее  или равное искомому.

    Пример 13.

    По  номеру месяца определить его название. Для этого создается отдельная таблица, где номеру месяца ставится в соответствие его название.

1. Используя автозаполнение, ввести: в ячейки с J1:J2 цифры от 1 до 12; в ячейки К1:К12 названия месяцев с января по декабрь.
2. В ячейку Н9 ввести любое число от 1 до 12.
3. В ячейку I9 вставить функцию ПРОСМОТР, выбрав первый способ задания аргументов (отдельно вектор просмотра и вектор результата): =ПРОСМОТР(Н9;$и$1:$^12;$К$1:$К$12) – знак $ устанавливает абсолютные ссылки на адреса областей расположения номеров и названий месяца. (Для фиксации адреса нажмите <F4> в конце адреса). В ячейке I9 появится название соответствующего месяца.
4. В ячейку Н10 введите новое число от 1 до 12.
5. Скопируйте в ячейку I10 формулу из ячейки I9.

   Пример 14.

    По  номеру месяца от текущей даты определить название месяца. Результат должен быть получен в ячейке I11.

    В ячейку I11 вставить функцию ПРОСМОТР, выбрав второй способ задания аргументов (сразу весь массив). В качестве искомого значения укажите номер текущего месяца, воспользовавшись вложенными функциями:

    =ПРОСМОТР(МЕСЯЦ(СЕГОДНЯ());$Д1:$К$12).

    В ячейке I11 получим название месяца текущей  даты.

6.6. ОЦЕНКА ЕЖЕМЕСЯЧНЫХ ВЫПЛАТ 

    Функция ПЛТ

    Функция ПЛТ предназначена для расчёта  выплаты, производимой в каждый период и не меняющейся за все время.

    Функция ПЛТ может быть использована для  анализа всевозможных ссуд.

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

    ПЛТ(СТАВКА;КПЕР;ПС;БС;ТИП)

• СТАВКА — процентная ставка за период. Например, если получена ссуда на автомобиль под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12 или 0,83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0,83% или 0,0083.
• КПЕР — общее число периодов платежей по аннуитету. Например, если получена ссуда на 4 года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента КПЕР в формулу нужно ввести число 48.
• ПС — приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
• БС — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение БС равно 0. Например, если предполагается накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость. Можно сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц.
• ТИП — число 0 (нуль) или 1, обозначающее, должна ли производиться выплата в конце периода (0) или в начале периода (1).

    Пример 15.

    Предположим, что нужно воспользоваться 9-процентной 15-летней ссудой. Объем ссуды составляет 150 000 000 рублей. С помощью Мастера функций можно определить величины ежемесячных выплат. Предварительно следует привести все другие значения к месячной норме.

 

     Ввести таблицу (рис. 11), начиная с  ячейки А15: 

Рис. 11. Определение величины ежемесячных  выплат 

    В ячейки В16 и В17 ввести соответствующие  формулы.

    Процентная  ставка — годовая, поэтому для получения месячной ставки (Удельная ставка) соответствующее значение делится на 12 (0,09/12).

    Срок  действия ссуды — 15 лет, поэтому  с учетом 12 платежей в год общее  количество месячных выплат (КПЕР) составит 12x15.

    Для ячейки В20 пошаговыми действиями Мастера  функций выполните настройку  функции ПЛТ. После этого в  поле Значение диалогового окна Мастера  функций вы увидите сумму ежемесячною  взноса. А после нажатия на кнопку Готово результат отобразится в  ячейке.

    Пример  16.

    Определить  какими будут выплаты по ссуде при меняющейся процентной ставке.

    В ячейки А22:В26 введите следующие значения, оставив пустой строку перед числовыми  значениями (рис. 12): 

Рис. 12. Определение  величины ежемесячных выплат 
с использованием таблицы подстановки