Пример 6.
В диапазоне ячеек А1:А5 из примера 1 определить максимальное значение. Результат должен быть получен в ячейке А8.
Пошаговыми действиями Мастера функций в ячейку А8 следует ввести формулу: =МАКС(А1 :А5). В ячейке А8 получится число 2000.
6.2.7. Определение минимального значения
МИН(ДИАПАЗОН1; ДИАПАЗОН2;...) - группа статистических функций. В текущую ячейку возвращается минимальное число из данного диапазона.
Пример 7.
В диапазоне ячеек В1:В5 из примера 2 определить минимальное значение. Результат должен быть получен в ячейке В8.
Пошаговыми действиями Мастера функций в ячейку В8 следует ввести формулу: =МИН(В1:В5). В ячейке В8 получится число 800.
6.2.8. Функции прогнозирования[3]
Для прогнозирования используется ряд функций (ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ) и диаграммы.
Функция ПРЕДСКАЗ
ПРЕДСКАЗ (X; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X).
Функция ПРЕДСКАЗ вычисляет или предсказывает будущее значение по существующим значениям.
X — это точка данных, для которой предсказывается значение.
ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y — это зависимый массив или интервал данных.
ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X — это независимый массив или интервал данных.
Функция РОСТ
РОСТ(ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X; НОВЫЕ ЗНАЧЕНИЯ X; КОНСТ).
Функция РОСТ рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения Y для последовательности новых значений X, задаваемых с помощью существующих Х- и Y-значений. Функция рабочего значения РОСТ может применяться также для аппроксимации существующих Х- и Y-значений экспоненциальной кривой.
ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y — это множество значений Y, которые уже известны для соотношения Y = b*m^X.
ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X — это необязательное множество значений х, которые уже известны для соотношения Y = b*m^Х.
НОВЫЕ ЗНАЧЕНИЯ X — это новые значения X, для которых РОСТ возвращает соответствующие значения Y.
КОНСТ — это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1.
Функция ТЕНДЕНЦИЯ
ТЕНДЕНЦИЯ(ИЗВЕСТНЫЕ ЗНАЧЕНИЯ 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. ОЦЕНКА ЕЖЕМЕСЯЧНЫХ ВЫПЛАТ
Функция ПЛТ предназначена для расчёта выплаты, производимой в каждый период и не меняющейся за все время.
Функция ПЛТ может быть использована для анализа всевозможных ссуд.
Возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки. Аннуитетом называется поток платежей одинакового размера, поступающих через равные промежутки времени. Период времени между двумя последовательными платежами является расчетным при начислении процентов.