Календарные функции играют в экономических и, особенно, финансовых расчетах, важную роль. Недаром говорится: время - деньги.
Для даты в 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, то используется фактическое число дней в каждом месяце и фактическое число дней в году. Данная функция может применяться, например, для вычисления доли годового гонорара, приходящегося на заданный период.
Как уже говорилось, необязательно вручную вводить имена функций и аргументы. Для этого можно использовать мастер функций. После двойного щелчка по ячейке, в которой хотим получить результат, следует щелкнуть по кнопке мастера функций, на которой есть значок fx, затем выбрать необходимую категорию функций (в данном случае - категория “Дата и время ”), затем саму функцию, нажать кнопку шаг и ввести (щелкнув по ячейке) адрес ячейки, содержащей аргумент. Для ввода функции от функции следует щелкнуть по кнопке fx. в окне мастера функций.
Например, нужно вычислить, число лет между датами 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 строк). Произвести расчет длительности разговоров (полных минут), а также сумм. Рекомендуется использовать функцию МИНУТЫ. По последнему столбцу подвести итоги.