Календарные и логические функции

Календарные функции играют в экономических и, особенно, финансовых расчетах, важную роль. Недаром говорится: время - деньги.

Для даты в EXCEL возможны различные форматы. Например, 1 октября 1990 года можно записать как 1.10.1990 или 1.10.90. Четыре цифры года необходимо задавать, когда год находится в диапазоне от 2000 до 2078. Но, «для спокойствия», лучше всегда задавать четыре цифры. Для изменения формата даты следует обратиться к пункту меню Формат, Ячейки и выбрать необходимый формат даты (разумеется, прежде надо выделить ячейку или блок ячеек, даты в которых вы хотите представить в определенном формате).

Второй формат представления даты - числовой, когда отсчет ведется от 1.01.1900. Каждый день имеет порядковый номер в интервале от 1 до 65380. День с порядковым номером 1 соответствует воскресенью 1 января 1900 года. Последний день с порядковым номером 65380 - 31 декабря 2078 года. Поэтому 1.10.90 преобразуется в число 33147. Для вывода даты в виде числа следует подать команду Формат, Ячейки, выбрать категорию Все, формат Основной (в EXCEL7 для WINDOWS95 этот формат носит название Общий). При выборе формата обратите внимание на образец представления, выводимый в том же окне, это избавляет от необходимости действовать “вслепую”.

Время вводится после даты. Наиболее удобен такой формат: день.месяц.год час:минуты:секунды. Например, 12 часов дня, 00 минут, 00 секунд 1.10.90 надо вводить так: 1.10.90 12:00:00. Время суток тоже преобразуется и хранится в виде числа - десятичной дроби, равной доле суток, прошедшей от их начала до заданного момента времени. Так, полдень (12:00 часов дня) выражается числом 0.5, потому что ровно половина суток проходит от их начала до полудня. Таким образом, 12 часов 00 минут 1.10.90 (в ячейку вводим 1.10.95 12:00:00) в числовом формате выглядит так: 33147.5. Если вводятся секунды, то следует предусмотреть необходимую точность числового представления. Например, 14 часов 09 минут 3 секунды 25 октября 1995 года (в ячейку вводим 25.10.95 14:09:03) в числовом формате выглядит как 34997.589618.

Если в ячейку введена дата, то во многих функциях EXCEL (например, финансовых), можно использовать ссылку на адрес этой ячейки, так как при этом происходит автоматическое преобразование даты в числовой формат. В противном случае следует использовать функцию ДАТАЗНАЧ(дата), которая преобразует дату в числовой формат.

Необходимо освоить работу с рядом функций EXCEL из раздела Дата и Время. Функция СЕГОДНЯ () вычисляет текущую дату в числовом формате (эта функция не имеет аргументов, но круглые скобки и знак равенства в начале должны быть обязательно). Однако результат действия этой функции можно увидеть в числовом формате, если установить формат представления командой Формат, Формат ячейки категория Все, Основной. Если же установить формат представления командой Формат, Формат ячейки категория Дата, то увидим дату как «день.месяц.год».

Функция ТДАТА() аналогична функции СЕГОДНЯ (), но учитывает также время в часах и минутах. Например, если воспользоваться функцией СЕГОДНЯ () в 17 часов 41 минуту 29 июля 1997 года, то получим в формате даты 29.07.97, а перейдя в числовой формат - 35640. Функция ТДАТА() выдаст соответственно 29.07.97 17:41 (если командой Формат, Формат ячейки установлен формат Дата, разумеется, с кодом, допускающим представление часов и минут) и 35640.73741 (если командой Формат, Формат ячейки установлен формат Все, Основной). Обновление значения времени происходит каждый раз при открытии рабочего листа, при вводе каких-либо новых данных, либо по нажатию клавиши F9. Если Вы заметите, что эти функции выдают ошибку - значит неисправен таймер компьютера, питающийся от аккумулятора.

Не следует вводить функции СЕГОДНЯ() и ТДАТА() вручную. Удобнее воспользоваться Мастером функций: щелчок по кнопке f _x, выбрать категорию функций Дата и время, а затем с помощью линейки прокрутки найти требуемую функцию. Это замечание относится и к другим функциям.

Функция ДОЛЯГОДА предназначена для вычисления доли года, прошедшей между заданной начальной (нач_дата) и конечной (кон_дата) датами:

=ДОЛЯГОДА(нач_дата; кон_дата; базис)

Аргумент базис задает используемый способ вычисления доли. Если этот аргумент отсутствует или равен 0, то используется американский метод 30/360, т.е. 30 дней в каждом месяце при 360 днях в году. Если базис равен 1, то используется фактическое число дней в каждом месяце и фактическое число дней в году. Данная функция может применяться, например, для вычисления доли годового гонорара, приходящегося на заданный период.

Как уже говорилось, необязательно вручную вводить имена функций и аргументы. Для этого можно использовать мастер функций. После двойного щелчка по ячейке, в которой хотим получить результат, следует щелкнуть по кнопке мастера функций, на которой есть значок f_x, затем выбрать необходимую категорию функций (в данном случае - категория “Дата и время ”), затем саму функцию, нажать кнопку шаг и ввести (щелкнув по ячейке) адрес ячейки, содержащей аргумент. Для ввода функции от функции следует щелкнуть по кнопке f_x. в окне мастера функций.

Например, нужно вычислить, число лет между датами 20.09.47 и 29.07.97. Поместим первую дату в ячейку B1, а вторую - в C1. Вызовем мастер функций, функцию ДОЛЯГОДА. Введем в нач_дата адрес В1,в кон_дата - адрес С1,в базис - число 1. В ячейке, где получим результат, установим формат: категория Все, код формата основной. Результат: 49.89041096 года.

Функции ГОД(дата), МЕСЯЦ(дата), ДЕНЬ(дата), определяют год, месяц и день, к которым относится данная дата. Аналогичным образом “работают” функции ЧАС(дата, время), МИНУТЫ(дата, время), СЕКУНДЫ(дата, время). Пример: введем в ячейку A10 дату и время 29.07.97 18:52. Значение функции МИНУТЫ(А10) равно 52. Однако если вы введете команду так: МИНУТЫ(29.07.97 18:52), то получите сообщение об ошибке, поскольку аргумент этих функций должен быть либо представлен в числовом формате, либо - в виде ссылки на ячейку, в которой допустим формат дата/время. Есть и такая возможность: МИНУТЫ(ВРЕМЗНАЧ(29.07.97 18:52)) = 52 (функция ВРЕМЗНАЧ(дата) преобразует дату в числовой формат).

До сих пор мы имели дело с данными типа число, текст, дата. Но EXCEL может оперировать и с логическими выражениями. Как Вам уже известно из курса лекций по информатике, логическое выражение может принимать одно из двух значений - «истина» или «ложь». Для «работы» с логическими выражениями имеются следующие логические функции.

Логическая функция ЕСЛИ имеет формат:

=ЕСЛИ(логическое выражение; формула1; формула2), Если значение логического выражения истинно, то функция принимает значение формулы 1, если ложно - формулы 2. В качестве условия выступают равенства и неравенства. Например, функция =ЕСЛИ(А6>8; 10; 20) принимает значение 10, если значение ячейки А6>8 и значение 20, если значение ячейки А6 <=8. Разумеется, эти значения появятся в той ячейке, которая на данный момент активна. Задавая аргументы функции ЕСЛИ, можно использовать другие функции. Например, результатом выполнения функции =ЕСЛИ(СУММ(А1:А10)>0; СУММ(А1:А10); 0), будет сумма в ячейках А1:А10, если эта сумма больше нуля, и 0, если она равна нулю или отрицательна.

Логические функции И -логическое умножение и ИЛИ - логическое сложение могут иметь в качестве аргументов до 30 логических выражений:

=И(логич.выр1; логич.выр2; логич.выр3;...)

=ИЛИ(логич.выр1; логич.выр2; логич.выр3;...)

Функция И принимает значение истина, только в том случае, когда все ее аргументы имеют значение истина, во всех остальных случаях эта функция принимает значение ложь. Функция ИЛИ принимает значение ложь только в том случае, когда все ее аргументы принимают значение ложь, во всех остальных случаях эта функция принимает значение истина. Функции И и ИЛИ могут использоваться как аргументы функции ЕСЛИ. Например, функция =ЕСЛИ(И(F4>10; A1=0); 1; 0) при F4=100, A1=0. Очевидно, что значение функции И(F4>10; A1=0) равно «ложь», поскольку один из аргументов (первый) принимает значение «ложь». Таким образом, =ЕСЛИ(И(F4>10; A1=0); 1; 0) = 0. Однако функция =ЕСЛИ(ИЛИ(F4>10; A1=0); 1; 0) = 1, поскольку функция ИЛИ(F4>10; A1=0) принимает значение «истина».

Задание 5. Подготовьте документ, соответствующий Вашему варианту - оформите заголовок, “шапку”, заполните графы с исходными данными. Введите необходимые для расчетов формулы и произведите расчеты.

Вариант 1. Ведомость поступления взносов от граждан в счет погашения ссуды за “____” __________199__ г.:

N	Фамилия, И.О.	Сумма	Срок	К-во просро-	Сумма	Сумма
п/п		взноса	платежа	ченных дней	штрафа	платежа
1.
...
15.
	Итого

За каждый просроченный день берется штраф в размере 1% от суммы взноса. Размер штрафа определяется по формуле: сумма взноса*количество просроченных дней/100, если количество просроченных дней > 0; в противном случае штраф равен нулю. Количество просроченных дней определяется как разность между датой платежа (дата в заголовке документа) и сроком платежа.

Надо по каждой строке определить сумму штрафа, сумму платежа (сумма взноса + штраф) и количество просроченных дней. Заполнить строку итогов для трех граф: сумма взноса, сумма штрафа, сумма платежа.

Вариант 2. Ведомость на списание бензина и диз. топлива АТП _______________.

N п/п	Путевой лист	Номерной знак АТС	Вид топлива	Пробег (тыс. км)	Норма расхода л/100км	Расход
	N	Дата					бензина	диз топ-лива

Расход = Норма расхода*Пробег*10.

Заполните строку итогов для двух последних граф.

Вариант 3. Ведомость определения страховой суммы плавсредств.

N п/п	Наимен. плав. ср-ва	Дата приобретен.	Первонач. стоимость	Дата страхования	Срок службы	Страховая сумма
1.
...
12.
	Итого:

По каждой строке надо определить срок службы (в годах) и страховую сумму. Срок службы - это разность между датой страхования и датой приобретения. Страховая сумма уменьшается на 8% от первоначальной стоимости за каждый год эксплуатации плав. средства и по истечении 13 лет становится равной нулю. В строке итогов заполните последнюю графу.

Вариант 5. Счет-квитанция за междугородные телефонные разговоры

Город	Стоимость 1 мин., руб.	Начало разговора	Окончание	Длитель-ность, мин.	Сумма, руб.
Москва		29.07.97 23:58:20	30.07.97 00:04:30
...

Необходимо ввести наименования городов, стоимость 1 минуты разговора, время начала и окончания разговора (не менее 10 строк). Произвести расчет длительности разговоров (полных минут), а также сумм. Рекомендуется использовать функцию МИНУТЫ. По последнему столбцу подвести итоги.