Другие применения логических функций

Все логические функции, рассматриваемые в данном разделе, можно использо­вать в качестве автономных формул. Хотя такие функции, как И, ИЛИ, НЕ обычно использу­ются совместно с функцией ЕСЛИ, но можно использовать также и формулы вроде

=И(А1>А2;А2<АЗ)

для задания простого условия. Эта формула возвращает логическое значение ИСТИНА, если значение в А1 больше значения в А2 изначение в А2 меньше значения в АЗ. Этот тип формул можно применять для присваивания диапазону ячеек значений ИСТИНА и ЛОЖЬ с тем, чтобы в дальнейшем использовать эти значения в качестве критериев отбора при печати специального отчета. См. Рисунок 25.

 

Рисунок 25.

 

Вложенные функции ЕСЛИ

Иногда бывает очень трудно решить логическую задачу, используя только операторы сравнения и функции И, ИЛИ и НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ. Например, формула

=ЕСЛИ(А1=100;''Всегда";ЕСЛИ(И(А1>=80;А1<100);''Обычно"; ЕСЛИ(И (А1>=60;А1<80);"Иногда"; "Увы!")))

использует три функции ЕСЛИ. Если значение в ячейке А1 является целым числом, формула читается следующим образом: «Если значение в ячейке А1 равно 100, возвратить строку Всегда.В противном случае, если значение в ячейке А1 находится между 80 и 100 (точнее, от 80 до 99 включительно), возвратить строку Обычно. В противном случае, если значение в ячейке А1 находится между 60 и 80 (от 60 до 79 включительно), возвратить строку Иногда.И наконец, если ни одно из этих условий не выполняется, возвратить строку Увы!См.Рис.26.

 

Рисунок 26.

 

Всего можно использовать до семи уровней вложения функций ЕСЛИ, не выходя при этом, конечно, за пределы максимальной длины значения в ячейке (255 символов).

 

Создание собственных форматов дат и времени

В дополнение к стандартным форматам даты и времени можно создать пользо­вательские форматы. Техника их создания в основном такая же, как при создании пользовательских числовых форматов. Например, чтобы создать формат, который выводит дату в самом полном виде, так что введенное значение даты, скажем, 14 февраля 2003 г., отображается как ,выполните следующие действия:

1. Выделите ячейку, которая содержит дату.

2. В меню Формат выберите команду Ячейки и щелкните на корешке вкладки Число.

3 На вкладке Число в списке Числовые форматы выберите категорию Все форматы.

4. В поле Тип введите свой пользовательский формат, в данном случае ДДДД, Д ММММ, ГГГГ.

5. Нажмите кнопку ОК. Excel сохранит новый формат в списке Тип для категории Все форматы и выведет в выделенной ячейке дату в новом формате

Этот же способ можно использовать для отображения только части даты или времени. Например, при вводе формата ММММ Excel отображает дату . как слово

После добавления пользовательского формата даты или времени в список Тип можно применять этот формат к любым введенным значениям дат или времени. Для этого нужно просто выделить ячейку, которая содержит введенное значение, выбрать в меню Формат команду Ячейки, на вкладке Число выбрать категорию. Все форматы, выбрать формат в списке Тип и нажать кнопку ОК.

Функции дат и времени

 

Функции дат и времени Excel позволяют выполнять вычисления в рабочем листе быстро и точно. Например, если рабочий лист используется для ведения ежемесячной платежной ведомости фирмы, вы можете использовать функцию ЧАС (HOUR) для определения числа рабочих часов и функцию ДЕНЬНЕД для определения ставки оплаты: стандартной (с понедельника по пятницу) или повышенной (с учетом суббот и воскресений).

 

Функция ДАТА

 

Основную функцию, ДАТА, можно использовать для ввода даты в ячейку. Эта функция особенно полезна, когда вся дата или ее часть является значением формулы, а не заранее известным значением. Эта функция имеет следующий синтаксис:

= ДАТА (год, месяц, день)

Аргументы в функции ДАТА располагаются по убыванию значимости: год, месяц, день.

Функция ДАТА возвращает десятичное значение даты, которое представляет собой количество дней между базовой и заданной датой.

Например, если использовать формулу

=ДАТА(2003;03;05)

для ввода даты 3 марта 2003г., результатом функции будет десятичное значение даты 37685, которое Excel выведет как 05.03.03.

Excel «творчески» интерпретирует аргументы функции ДАТА. Если вы введете функцию ДАТА с аргументом День, превосходящим число дней в указанном месяце. Excel просто продолжит счет дней дальше в следующие месяцы.

Например, при вводе формулы =ДАТА(03;03;50) Excel сохранит десятичное значение даты для 19 апреля 2003 г. Такая гибкость бывает очень удобной при выполнении вычислений с датами.

Аргумент деньможет быть любым, если он не превосходит максимальное десятичное значение даты 65380. Подобным образом аргумент месяц может быть больше 12. В этом случае при интерпретации аргументов функции ДАТА Excel считает месяцы дальше в последующие годы.

При использовании 0 в качестве значения аргумента деньфункции ДАТА Excel интерпретирует это значение как последний день предыдущего месяца.

Напри­мер, при вводе =ДАТА(03;3;0) Excel возвращает десятичное значение для отображаемой даты 28 февраля 2003 г. Подобным образом при вводе 0 в качестве значения аргумента месяц отображается декабрь предыдущего года.

Например, при вводе =ДАТА(03;0;0) Excel сдвигает назад значение месяца и дня и сохраняет десятичное значение даты для 30 ноября 2002 г.

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

Например, при вводе =ДАТА(2003;8;-6) Excel сохраняет десятичное значение даты для 25 июля 2003 г.

Excel может также вычислять значения аргументов функции ДАТА. Например, чтобы вывести дату за 26 дней до 3 сентября 2003 г., можно использовать формулу

=ДАТА(2003;9;3-26) Результат будет равен 37476, или 08.08.03.

 

 

Функция СЕГОДНЯ

 

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

=СЕГОДНЯ()

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

 

Функция ДЕНЬНЕД

 

Функция ДЕНЬНЕД возвращает день недели для заданной даты и имеет следующий синтаксис:

= ДЕНЬНЕД(десятичная дата; тип)

См. Рисунок 27.

 

Рисунок 27.

Аргумент десятичная - датаможет быть десятичным значением даты, ссылкой на ячейку, которая содержит функцию даты или десятичное значение даты, либо такой текст, как 25.03.03или 25 марта 2003.Если вы используете текст, обязательно заключите его в кавычки.

Функция ДЕНЬНЕД возвращает число, представляющее собой день недели для заданной даты. Необязательный аргумент типопределяет тип представления результата. Если тип равен 1 или опущен, функция возвращает число от 1 до 7, где 1 - воскресенье и 7 - суббота. Если тип равен 2, функция возвращает число от 1 до 7, где 1 - понедельник и 7 - воскресенье. Если типравен 3, функция возвращает число от 0 до 6, где 0 - понедельник и 6 - воскресенье.

 

Функции ГОД, МЕСЯЦ и ДЕНЬ

Функции ГОД, МЕСЯЦ и ДЕНЬ возвращают год, месяц и день для заданного десятичного значения даты/времени. Эти функции имеют следующий синтаксис:

= ГОД (десятичная дата)

= МЕСЯЦ (десятичная дата)

= ДЕНЬ (десятичная дата)

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

Результатом этих функций является значение соответствующей части даты, заданной аргументом десятичная дата. Например, если ячейка A1содержит дату 25.03.2003 то формула

=ГОД(А1)

возвратит значение 2003.

формула

=МЕСЯЦ (А1)

возвратит значение 3.

формула

=ДЕНЬ(А1)

возвратит значение 25.

 

 

Функции ДАТАЗНАЧ и ВРЕМЗНАЧ

 

Функция ДАТАЗНАЧ преобразует дату в десятичное значение даты. Она аналогична функции ДАТА за исключением того, что использует текстовый аргумент. Функция ДАТАЗНАЧ имеет следующий син­таксис:

=ДАТАЗНАЧ(текст даты)

Аргумент текст даты представляет собой любую дату между 1 января 1900 г. и 31 декабря 2078 г. в любом из встроенных форматов даты Excel. (Текст необходимо заключать в кавычки.) Например, формула

=ДАТАЗНАЧ("25.03.03")

возвратит десятичное значение даты 37705. См. Рисунок 28.

 

Рисунок 28.

Если вы введете текст датыбез указания года. Excel определит текущий год по встроенным часам компьютера.

Функция ВРЕМЗНАЧ преобразует время в десятичное значение. Она аналогична функции ВРЕМЯ за исключением того, что вы должны ввести текстовый аргумент. Функция ВРЕМЗНАЧ имеет следующий синтаксис:

=ВРЕМЗНАЧ (текст времени)

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

=ВРЕМЗНАЧ("4:30 РМ")

функция возвратит десятичное значение времени 0,6875.

Функция ДНЕЙЗ60

Некоторые вычисления с датами, являющиеся обыденными при обращении ценных бумаг, используют “искусственны” 360-дневный год, который содер­жит двенадцать месяцев по тридцать дней. Если нужно выполнить такие вычисления, вместо вычитания одной даты из другой используйте функцию ДНЕЙЗ6О. Эта функция имеет следующий синтаксис:

= ДНЕЙЗ60 (начальная дата; конечная дата; метод )

Например, чтобы определить число дней между 24марта 2003 г. и 1июня 2003 основываясь на 360-дневном годе, используйте формулу

=ДНЕЙЗ60("24.03.03";"01.06.03").См. Рисунок 29.

Рисунок 29.

 

которая возвращает 67. Обратите внимание, что если в качестве аргументов начальная датаи конечная датавместо десятичных значений дат использу­ются даты, введенные “в формате”, вы должны заключить их в кавычки. В качестве альтернативы можно использовать ссылки на ячейки, которые содержат начальную и конечную даты. Например, если ячейка А1 содержит значение 24.03.03 и в ячейке А2 записано 01.06.03, можно определить интервал между этими датами, используя формулу

=ДНЕЙЗ60(А1;А2). См. Рисунок 30.

Рисунок 30.

Необязательный аргумент методможет принимать значение 1 (ИСТИНА) или 0 (ЛОЖЬ). Если вы введете 1, то используется европейский метод вычисления. Это значит, что если число месяца начальной даты равно 31, то вместо него используется 30, а если число месяца конечной даты равно 31, используется первый день следующего месяца (за исключением случая, когда число месяца начальной даты равно 30 ). При этом вместо 28 или 29 февраля всегда исполь­зуется 30 февраля.

 

ФУНКЦИИ СЧЕТ И СЧЁТЗ

 

Функция СЧЕТ определяет количество ячеек в заданном диапазоне, которые содержат числа, в том числе - даты и формулы, возвращающие числа. Эта функция имеет следующий синтаксис:

= СЧЕТ(значение;значение2;...)

Например, в листе, представленном на Рисунок 31.

 

 

Рисунок 31.

 

=СЧЁТ(A1:A9) возвратит значение 5 - число ячеек в диапазоне (A1:A9) содержащих числа и дату. Функция СЧЕТ игнорирует ячейки, которые не содержат чисел. Функция СЧЕТ учитывает только числовые значения в диапазоне и игнорирует пустые ячейки и ячейки, содержащие текстовые, логические или ошибочные значения.

Для определения количества непустых ячеек (независимо от их содержимого) используется Ф ункция СЧЁТЗ Эта функция имеет следующий синтаксис:

= СЧЕТЗ ( значение1;значение2;...)

Формула = СЧЁТЗ(A1:A9) возвратит значение 9, так как диапазон (A1:A9) содержит пять и четыре текстовых значения.

 

Функции СУММЕСЛИ и СЧЁТЕСЛИ

 

Функция СУММЕСЛИ аналогична функции СУММ (SUM), но про­веряет каждую ячейку в диапазоне, прежде чем добавить ее к итогу. Эта функция имеет следующий синтаксис:

= СУММЕСЛИ(диапазон; условие; диапазон суммирования)

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

 

 

Рисунок 32.

Например, в листе, представленном на рис.8 найдено суммарная стипендия

для студентов, имеющих количество пропусков >3.

Для вычисления общих затрат на выплату заработной платы сотрудникам младше 35 лет можно использовать формулу

=СУММЕСЛИ (G2:G19;"<35";F2:F19)

Подобным образом функция СЧЕТЕСЛИ определяет количество ячеек, которые удовлетворяют заданному критерию. Функция имеет следующий синтаксис:

= СЧЕТЕСЛИ (диапазон; критерий)

Рисунок 33.

На рис.33 найдено количество студентов, которые имеют количество пропусков >3.

Например, чтобы подсчитать количество сотрудниц в списке, представленном в примере см. Рисунок 34. Таблица пример. можно использовать формулу:

=СЧЁТЕСЛИ (Е2:Е24;"Ж"). А для подсчета числа сотрудников в возрасте 45 лет или старше, можно использовать формулу:

= СЧЁТЕСЛИ(G2:G19;''>==45'')