ЭЛЕКТРОННАЯ ТАБЛИЦА EXCEL ВСТРОЕННЫЕ ФУНКЦИИ. ЧАСТЬ 3 Встроенные функции в Excel 1

Данные типа « дата » и « время » 2 В MS Excel можно ввести значения даты и времени в рабочем листе, чтобы указать момент создания документа или выполнить вычисления с датами и временем. Основной единицей измерения времени в Excel являются сутки.

Данные типа « дата » и « время » 3 Любой дате из допустимого диапазона дат ( ) соответствует определенное число или ее порядковый номер. Базовой датой, которой соответствует порядковый номер 1, является 1 января 1900 года.

Данные типа « дата » и « время » 4 Когда в ячейку ЭТ вводится дата, то Excel сохраняет ее в виде соответствующего ей порядкового номера, который равен количеству дней между базовой и заданной датой. Например, порядковым номером 10 января 1900 года будет 10, а 9 октября 2014 года

Данные типа « дата » и « время » 5 Время суток это десятичная дробь, которая представляет часть суток между началом (12:00 ночи) и заданным временем. Например, 12:00 дня представляется значением 0,5 (это соответствует половине суток).

Данные типа « дата » и « время » 6 Любому числу из диапазона [0; ] соответствует определенная дата и время. При этом дата определяется целой частью числа, а время его дробной частью. Так, например, абсолютное время 12:15:30 в день 9 октября 2014 года представляется десятичным значением 41921,51076.

Ввод дат и времени в клетки ЭТ 7 Хотя Excel хранит дату и время как десятичные значения, вводить их можно в привычном формате. Так, например, дату 9 октября 2014 года можно ввести в ячейку таблицы в одном из следующих форматов: /10/

Ввод дат и времени в клетки ЭТ 8 В качестве разделителя элементов даты (число, месяц, год) можно использовать точку, наклонную черту или дефис. При вводе времени его элементы (часы, минуты, секунды) отделяются двоеточием /10/

Форматирование данных типа « дата » и « время » 9 К выделенным ячейкам, содержащих данные типа «дата» и «время», можно назначить формат отображения, используя команду Формат/Формат ячейки. Для этого из списка числовых форматов (вкладка «Число») следует выбрать группу форматов «Дата» или «Время», а затем из списка «Тип» конкретный формат для желаемого вида представления данных.

Форматирование данных типа « дата » и « время » 10 а) б) Фрагмент диалогового окна «Формат ячеек»: а) числовой формат «Дата»; б) числовой формат «Время» Команда Формат/Формат ячейки

Арифметические операции над данными с типа « дата » и « время » 11 Назначая десятичные значения дням, часам, минутам и секундам, Excel позволяет выполнять арифметические вычисления с датами и временем. По отношению к датам имеют смысл следующие операции: вычитание дат; сложение даты с заданным числом; вычитание из даты заданного числа.

Арифметические операции над данными с типа « дата » 12 Первая операция позволяет определить промежуток времени (в сутках), отделяющий одну дату от другой. Пример 1. Если в клетку A1 введена текущая дата, а в клетку B1 дата выхода в отпуск, то с помощью формулы =B1–A1 определим, сколько дней осталось до начала отпуска.

Арифметические операции над данными с типа « дата » 13 Определить дату, которая наступит через заданное количество дней, можно с помощью операции второго типа. Пример 2. Гарантийный срок использования прибора составляет 20 недель. Если в клетку A1 ввести дату покупки прибора, то с помощью формулы =A1+7*20 можно узнать дату окончания гарантийного срока.

Арифметические операции над данными с типа « дата » 14 Вычитая из даты заданное число, можно установить, какая дата была несколько дней назад. Пример 3. Предварительная продажа билетов на поезд начинается за 45 суток до его отправления. Если в клетку A1 ввести дату отправления поезда, то с помощью формулы =A1–45 можно определить дату, с которой начинается предварительная продажа.

Арифметические операции над данными с типа « время » 15 Данные типа «время» можно интерпретировать, во- первых, как моменты времени в некотором процессе, и, во-вторых, как длительность процесса или его этапов.

Арифметические операции над данными с типа « время » 16 Пример 4. Если данные задают моменты времени начала и конца процесса, то их разность определяет его продолжительность. Так, если в клетку A1 ввести время начала телефонного разговора, а в клетку A2 – время его окончания, то с помощью формулы =A2–A1 можно узнать его продолжительность.

Арифметические операции над данными с типа « время » 17 Пример 5. Предположим, что требуется определить момент времени, который наступит через 2 часа 23 минуты и 17 секунд (содержимое клетки A1 ) после 12:35:23 (содержимое клетки A2 ). Формула =A1+A2, введенная в клетку A3, возвратит требуемое значение.

Арифметические операции над данными с типа « время » 18 Пример 6. Если в клетке B1 введена дата и время прекращения приема радиосигнала, а в клетке B2 – дата и время его возобновления, то длительность отсутствия сигнала определим по формуле =B2-B1, введя ее в клетку B4 (и B5 ). При этом длительность будет определена в сутках.

Основные функции категории « дата » и « время » 19 ДАТА (год; месяц; день) Возвращает порядковый номер определенной даты ВРЕМЯ (часы; минуты; секунды) Возвращает десятичное число, представляющее определенное время

Основные функции категории « дата » и « время » 20 ГОД (дата) Возвращает год, соответствующий заданной дате. Год определяется как целое число в диапазоне от 1900 до 9999 МЕСЯЦ (дата) Возвращает номер месяца – число от 1 ( январь ) до 12 ( декабрь ) ДЕНЬ (дата) Возвращает число месяца – число от 1 до 31

Основные функции категории « дата » и « время » 21 ДЕНЬНЕД (дата; тип) Возвращает число, соответствующее номеру дня недели для заданной даты Если указан тип 1 (или опущен), то функция возвращает число от 1 (воскресенье) до 7 (суббота). Для наиболее распространенной в России нумерации: первый день недели – понедельник, необходимо указать тип 2 – отсчет дней недели начинается с 1 (понедельник) до 7(воскресенье).

Основные функции категории « дата » и « время » 22 ЧАС (время) Возвращает часы в виде числа от 0 до 23 МИНУТЫ (время) Возвращает минуты в виде числа от 0 до 59 СЕКУНДЫ (время) Возвращает секунды в виде числа от 0 до 59

Основные функции категории « дата » и « время » 23 СЕГОДНЯ () Возвращает текущую дату в числовом формате ТДАТА () Возвращает число, представляющее текущие дату и время.

Работа с данными типа « дата » и « время » 24 Пример 7. Сколько дней в феврале текущего года, можно определить по формуле =ДЕНЬ(ДАТА(ГОД(СЕГОДНЯ());3;1)-1)

Работа с данными типа « дата » и « время » 25 Пример 8. Проверить, соответствует ли дата, значение которой содержится в клетке A1, выходному дню (суббота или воскресенье), можно с помощью выражения =ЕСЛИ(ДЕНЬНЕД(A1;2)>=6;да;нет)

Работа с данными типа « дата » и « время » 26 Пример 9. С помощью формулы =ВЫБОР(ДЕНЬНЕД(ДАТА(ГОД(СЕГОДНЯ()); 9;1);2);пн;вт;ср; чт;пт;сб;вс) можно установить, на какой день недели приходится 1 сентября текущего года.

Работа с данными типа « дата » и « время » 27 Пример 10. Пусть в клетке A1 содержится дата получения кредита сроком на 18 месяцев. Дату погашения кредита можно определить по формуле =ДАТА(ГОД(A1);МЕСЯЦ(A1)+18;ДЕНЬ(A1))

Работа с данными типа « дата » и « время » 28 Пример 11. По дате, значение которой содержится в клетке A1, можно определить время года с помощью формулы =ЕСЛИ(ИЛИ(МЕСЯЦ(A1)

Работа с данными типа « дата » и « время » 29 Пример 12. Известно, что длительность экскурсии составляет 5 часов 25 минут. Если время ее начала введено в клетку A1, то по формуле =A1+ВРЕМЯ(5;25;0) определим время ее окончания.

Работа с данными типа « дата » и « время » 30 Пример 13. Известно, что с 10 до 11 часов включительно в магазине предусмотрена скидка 10 % на покупку товаров. С помощью формулы =ЕСЛИ(ИЛИ(ЧАС(A1)=10;И(ЧАС(A1)=11; МИНУТЫ(A1)=0));10%;0) можно определить размер скидки, если время покупки (чч:мм) записано в клетку A1.

Работа с данными типа « дата » и « время » 31 Время сеанса Рабочие дни Выходные дни 11:00-14: :00-18: :00-00: Пример 14. Имеется информация о стоимости билетов на киносеанс:

Работа с данными типа « дата » и « время » 32 Для определения стоимости билета, зная дату и время начала сеанса, можно подготовить в ЭТ, например, на листе «Цены», справочник следующим образом:

Работа с данными типа « дата » и « время » 33 Если на другом листе в клетку A1 ввести дату, а в клетку A2 – время сеанса, то стоимость билета определим с помощью формулы: =ВПР(A2;Цены!A3:C5;ЕСЛИ(ДЕНЬНЕД(A1;2)

Задача « Учет продажи билетов на киносеансы » 34 Время сеанса С понедельника по четверг Пятница Суббота, воскресенье, праздничные дни 8:00-11: :00-14: :00-17: :00-21: :00-00: Постановка задачи Цена билета на киносеанс зависит от времени сеанса, дня недели и места в зрительном зале. Цены билетов приведены в таблице:

Задача « Учет продажи билетов на киносеансы » 35 Лист: Справочники Диапазоны: Цены Праздники

Задача « Учет продажи билетов на киносеансы » 36 Лист: Учет

Задача « Учет продажи билетов на киносеансы » 37 Заполнение колонки B По дате надо определить ценовую категорию. Прежде всего построим условия, которые характеризуют ценовые категории.

Задача « Учет продажи билетов на киносеансы » 38 Условие «выходного дня (суббота или воскресенье)» ДЕНЬНЕД(A2;2)>5

Задача « Учет продажи билетов на киносеансы » 39 Условие «праздничного дня» НЕ(ЕОШИБКА(ПОИСКПОЗ(A2;Праздники;0)))

Задача « Учет продажи билетов на киносеансы » 40 Объединяя два условия с помощью функции ИЛИ, получим условие ценовой категории с номером 3: ИЛИ(ДЕНЬНЕД(A2;2)>5; НЕ(ЕОШИБКА(ПОИСКПОЗ(A2;Праздники;0))))

Задача « Учет продажи билетов на киносеансы » 41 Условие ценовой категории с номером 2 можно записать так: ДЕНЬНЕД(A2;2)=5

Задача « Учет продажи билетов на киносеансы » 42 Теперь для определения номера ценовой категории можно воспользоваться функцией ЕСЛИ: =ЕСЛИ(ИЛИ(ДЕНЬНЕД(A2;2)>5; НЕ(ЕОШИБКА(ПОИСКПОЗ(A2;Праздники;0)))); 3; ЕСЛИ(ДЕНЬНЕД(A2;2)=5;2;1))

Задача « Учет продажи билетов на киносеансы » 43 Формула в клетке E2: =ВПР(C2;Цены;B2+1)*D2