Функции этой категории являются мощным средством для автоматизации и управления данными, содержащими даты и временные значения. Например, если вам необходимо предоставлять ежедневные отчеты, можно вводить текущую дату вручную, но проще поручить ввод функции СЕГОДНЯ(). Функции даты и времени предназначены не только для простого ввода дат в ячейки рабочего листа, они могут вычислять длительность временных интервалов, определять количество рабочих дней или недель между двумя датами, сравнивать даты и т.п.
При вводе в ячейку даты или при вычислении даты с помощью формулы Microsoft Excel автоматически отображает эти данные в подходящем формате дат и времени. Но на самом деле даты хранятся в Microsoft Excel в виде обыкновенных чисел. Единица соответствует одним суткам. По умолчанию дате 1 января 1900 года соответствует число 1, 2 января 1900 года – 2, 1 января 2008 года – 39448 (интервал между 1 января 1900 года и 1 января 2008 года в сутках). Время сохраняется в виде дробной части числа того же числа (1 час равен 1/24 суток, 1 минута – 1/1440 суток, 1 секунда – 1/86400 суток, все это переводится в десятичную дробь). Поскольку даты и время представляются числами, их можно складывать и вычитать, а также использовать в других вычислениях. Результаты вычислений вновь преобразуются в один из форматов записи даты.
Функции даты и времени содержатся в выпадающем списке кнопки «
Дата и время» на вкладке Формулы ленты инструментов.
В табл. 12.6 представлены основные функции для работы с датой и временем.
Табл. 12.6
| Функция | Синтаксис | Описание |
| ВРЕМЗНАЧ | =ВРЕМЗНАЧ (время_как_текст) | Преобразует время из текстового формата в числовой |
| ВРЕМЯ | =ВРЕМЯ(часы; минуты; секунды) | Возвращает заданное время в числовом формате |
| ГОД | =год(дата_в_числовом_формате) | Возвращает год, соответствующий аргументу (в виде числа) |
| ДАТА | =ДАТА(год; месяц; день) | Возвращает заданную дату в форме числа |
| ДАТАЗНАЧ | =ДАТАЗНАЧ(дата_как_текст) | Преобразует дату из текстового формата в числовой |
| ДАТАМЕС | =ДАТАМЕС(нач_дата; число месяцев) | Возвращает дату в числовом формате, отстоящую от начальной на заданное число месяцев |
| ДЕНЬ | =ДЕНЬ (дата_в_числовом_формате) | Преобразует дату в числовом формате в день месяца |
| ДЕНЬНЕД | =ДЕНЬНЕД(дата_в_числовом_формате; тип) | Преобразует дату в числовом формате в день недели |
| МЕСЯЦ | =МЕСЯЦ(дата_в_числовом_формате) | Возвращает номер месяца в дате (в формате числа) |
| МИНУТЫ | =МИНУТЫ(время_в_числовом_формате) | Возвращает количество минут, соответствующие аргументу функции |
| НОМНЕДЕЛИ | =НОМНЕДЕЛИ(дата_в_числовом_формате; начало_недели) | Возвращает порядковый номер недели; начало недели равно 2 для стран, где первый день недели – понедельник |
| СЕГОДНЯ | =СЕГОДНЯ() | Возвращает текущую дату в числовом формате |
| СЕКУНДЫ | =СЕКУНДЫ(время_в_числовом_формате) | Возвращает количество секунд, соответствующее аргументу функции |
| ТДАТА | = ТДАТА() | Возвращает текущую дату и время в числовом формате |
| ЧАС | =ЧАС(время в_числовом_формате) | Возвращает час, соответствующий заданному времени, в числовом формате. Час определяется как целое в интервале от 0 до 23 |
| ЧИСТРАБДНИ | =ЧИСТРАБДНИ(нач_дата;кон_дата;праздники) | Возвращает в числовой форме количество рабочих дней между двумя датами; в качестве праздников можно указать даты государственных праздничных дней |
Текстовые функции
Текстовые функции могут решать большой круг задач. С их помощью можно определить, сколько символов содержится в строке, удалить лишние пробелы и непечатаемые символы, выделить из общего текста некоторый фрагмент, автоматически сгенерировать запись числа текстом, объединить текст из разных ячеек в одну строку и т.д. Если текстовая информация была импортирована из какого-нибудь внешнего источника, эти функции помогут упорядочить ее и привести к единому формату.
Текстовые функции содержатся в выпадающем списке кнопки «
Текстовые» на вкладке Формулы ленты инструментов. Основные функции приведены в табл. 12.7
Табл. 12.7
| Функция | Синтаксис | Описание |
| ТЕКСТ | =ТЕКСТ(число;формат) | Преобразует число в текст в заданном числовом формате |
| ЗНАЧЕН | =ЗНАЧЕН(текст) | Преобразует текстовый аргумент в число |
| СТРОЧН | =СТРОЧН(текст) | Делает все знаки в тексте строчными (малыми) |
| ПРОПИСН | =ПРОПИСН(текст) | Делает все буквы в тексте прописными (большими) |
| ПРОПНАЧ | =ПРОПНАЧ(текст) | Все первые буквы в словах текста делаются прописными (верхний регистр). Все остальные буквы делаются строчными (нижний регистр) |
| СЖПРОБЕЛЫ | =СЖПРОБЕЛЫ(текст) | Удаляет из текста лишние пробелы (двойные, перед знаками препинания и т.п.) |
| ПЕЧСИМВ | =ПЕЧСИМВ(текст) | Удаляет все непечатаемые знаки из текста |
| СИМВОЛ | =СИМВОЛ(числ_код) | Возвращает знак, определенный числовым кодом |
| СЦЕПИТЬ | =СЦЕПИТЬ(текст1;текст2;…) | Объединяет несколько текстовых элементов в один |
| ЗАМЕНИТЬ | =ЗАМЕНИТЬ (старый_текст;нач_поз;число_знаков;новый_текст) | Замещает указанную часть знаков текстовой строки другой строкой текста |
| ПОВТОР | =ПОВТОР(текст;число_повторений) | Повторяет текст заданное число раз |
| ПОДСТАВИТЬ | =ПОДСТАВИТЬ(текст;старый_фрагмент;новый_фрагмент;номер_вхождения) | Заменяет в текстовой строке старый фрагмент новым; если номер вхождения не указан, все встретившиеся фрагменты старого текста заменяются новыми |
| ДЛСТР | =ДЛСТР(текст) | Возвращает количество знаков в текстовой строке |
| ЛЕВСИМВ | =ЛЕВСИМВ(текст;число_знаков) | Возвращает указанное число знаков с левого края текстовой строки |
| ПРАВСИМВ | =ПРАВСИМВ(текст;число_знаков) | Возвращает заданное число знаков с правого края текстовой строки |
| НАЙТИ | =НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция) | Возвращает позицию, с которой в просматриваемом тексте начинается искомый текст; начальная позиция позволяет производить поиск не с начала; прописные и строчные буквы различаются |
| ПОИСК | =ПОИСК(искомый_текст; просматриваемый_текст;нач_позиция) | Возвращает позицию, с которой в просматриваемом тексте начинается искомый текст; начальная позиция позволяет производить поиск не с начала; прописные и строчные буквы не различаются |
| ПСТР | =ПСТР(текст;нач_позиция;число_знаков) | Возвращает заданное число знаков из строки текста, начиная с указанной позиции |
| СОВПАД | =СОВПАД(текст1;текст2) | Сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают, и ЛОЖЬ в противном случае; прописные и строчные буквы различаются |
| Т | =Т(значение) | Если значение является текстом, возвращает его без изменения, если нет ‒ возвращает пустые кавычки |
В качестве значений аргументов может использоваться как сам текст, так и ссылки на ячейки с текстом. Если в качестве аргумента функции используется текст, он обязательно должен быть заключен в кавычки, например, =ПРАВСИМВ ("Песок";2) (результатом формулы будет значение «ок»). Чтобы не заботиться о кавычках, лучше использовать в формуле ссылку на ячейку с текстом.
Логические функции
Логические функции проверяют ячейки либо их диапазоны на определенное условие и возвращают значение ИСТИНА, если условие выполняется, и ЛОЖЬ, если не выполняется. Логические функции содержатся в выпадающем списке кнопки «
Логические» на вкладке Формулы ленты инструментов. Краткое описание некоторых логических функций дано в табл. 12.8
Табл. 12.8
| Функция | Синтаксис | Описание |
| ЕСЛИ | =ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) | Возвращает одно значение, если логическое выражение имеет значение ИСТИНА, и другое значение, если ЛОЖЬ |
| И | =И(логическое_значение1;логическое_значение2;…) | Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ в противном случае |
| ИЛИ | =ИЛИ(логическое_значение1;логическое_значение2;…) | Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ в противном случае |
| НЕ | =НЕ(логическое_значение) | Меняет логическое значение своего аргумента на противоположное |
| ИСТИНА | =ИСТИНА() | В любом случае возвращает логическое значение ИСТИНА. Скобки могут содержать любую информацию, быть пустыми, или их может не быть вообще |
| ЛОЖЬ | =ЛОЖЬ() | В любом случае возвращает логическое значение ЛОЖЬ |
| ЕСЛИОШИБКА | =ЕСЛИОШИБКА(значение;значение_если_ошибка) | Возвращает значение_если_ошибка, если выражение ошибочно; в противном случае возвращает значение без изменений |
Функция ЕСЛИ – одна из наиболее часто применяемых в Microsoft Excel. Она может быть использована в различных комбинациях с другими функциями; кроме того, до семи функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов (значение_если_истина) и (значение_если_ложь). Это позволяет конструировать весьма сложные проверки.
К логическим функциям близки по смыслу функции проверки свойств и значений. Они возвращают логический результат (ИСТИНА или ЛОЖЬ) и используются в комбинации с другими функциями для автоматизации обработки данных. Для вставки функций служит выпадающий список кнопки «
Проверка свойств и значений» (по умолчанию находится в выпадающем списке кнопки «
Другие функции») на вкладке Формулы ленты инструментов. Функции данной категории приведены в табл. 12.9
Табл. 12.9
| Функция | Синтаксис | Описание |
| ЕОШ | =ЕОШ(значение) | Возвращает значение ИСТИНА, если при попытке вычисления значения была выдана ошибка, и ЛОЖЬ в противном случае |
| ЕОШИБКА | =ЕОШИБКА(значение) | Аналогично предыдущей, однако игнорирует ошибки «#Н/Д» |
| ЕНД | =ЕНД(значение) | Возвращает значение ИСТИНА, если имеется ошибка «#Н/Д» |
| ЕТЕКСТ | =ЕТЕКСТ(значение) | Возвращает значение ИСТИНА, если значение является текстом |
| ЕНЕТЕКСТ | =ЕНЕТЕКСТ(значение) | Возвращает значение ИСТИНА, если значение не является текстом (пустые ячейки текстового формата не являются текстом) |
| ЕЧИСЛО | =ЕЧИСЛО(значение) | Возвращает значение ИСТИНА, если значение является числом |
| ЕССЫЛКА | =ЕССЫЛКА(значение) | Возвращает значение ИСТИНА, если значение является ссылкой на другую ячейку |
| ЕЛОГИЧ | =ЕЛОГИЧ(значение) | Возвращает значение ИСТИНА, если значение является логическим, т.е. имеет значение либо ИСТИНА, либо ЛОЖЬ |
| ЕПУСТО | =ЕПУСТО(значение) | Возвращает значение ИСТИНА, если значение является пустой ячейкой |
| ЕЧЁТН | =ЕЧЁТН(значение) | Возвращает значение ИСТИНА, если число четное |
| ЕНЕЧЁТ | =ЕНЕЧЁТ(значение) | Возвращает значение ИСТИНА, если число нечетное |
| ТИП | =ТИП(значение) | Возвращает тип значения. Например, если значение является числом, то функция возвращает 1, если текст – 2, логическое значение – 4, значение ошибки – 16, массив – 64 (удобно работать после перевода в двоичную форму) |
Помимо рассмотренных, в Microsoft Excel имеется еще две категории функций: для работы с базами данных (будут рассмотрены в Лабораторной работе №14) и финансовые. Последняя категория в данном пособии не рассматривается.
Функции ссылок и массивов
Функции ссылок и массивов позволяют заставлять Microsoft Excel автоматически решать достаточно сложные задачи с данными. На начальном этапе студенты не работают со столь большими объемами данных и могут обрабатывать их вручную, просматривая таблицы, выделяя, копируя и перемещая ячейки и т.д. В то же время при обработке больших таблиц (сотни и тысячи строк) ряд действий по поиску значений в таблице, выделению этих значений, переносу соответствующих строк в новую таблицу или на новый лист можно осуществить при помощи функций данной категории.
Функции массивов содержатся в выпадающем списке кнопки «
Ссылки и массивы» на вкладке Формулы ленты инструментов. В табл. 12.10 приведены лишь некоторые функции этого типа.
Под массивом в Microsoft Excel понимается любая прямоугольная область ячеек, заполненных данными, без пустых строк или столбцов (пустой столбец или строка интерпретируются как конец массива). Особенностью массива является то, что его положение относительно начала листа в общем случае может быть произвольным – многие функции ссылок и массивов работают с относительными номерами строк и столбцов в массиве, т.е. с порядковыми номерами строк или столбцов относительно первой строки или первого столбца массива.
Табл. 12.10
| Функция | Синтаксис | Описание |
| ВПР | =ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр) | Ищет значение в крайнем левом столбце заданной таблицы и возвращает значение из ячейки на пересечении той же строки и заданного столбца таблицы. «Интервальный просмотр» определяет, будет ли значение искаться точно или приближенно |
| ГПР | =ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр) | Ищет значение в крайней верхней строке таблицы и возвращает значение из ячейки на пересечении того же столбца и заданной строки таблицы |
| СТОЛБЕЦ | =СТОЛБЕЦ(ссылка) | Возвращает номер столбца по заданной ссылке |
| ЧИСЛСТОЛБ | =ЧИСЛСТОЛБ(ссылка) | Возвращает количество столбцов в ссылке |
| СТРОКА | =СТРОКА(ссылка) | Возвращает номер строки, определяемой ссылкой |
| ЧСТРОК | =ЧСТРОК(ссылка) | Возвращает количество строк в ссылке |
| ОБЛАСТИ | =ОБЛАСТИ(ссылка) | Возвращает количество областей (ячеек или диапазонов ячеек с содержимым, разделенных пустыми строками или столбцами) в ссылке |
| ВЫБОР | =ВЫБОР(номер_индекса;значение1,значение2;...) | Использует порядковый номер индекса (1,2,…7), чтобы выбрать и вернуть одно из значений из списка аргументов-значений (до 7 значений) |
| ИНДЕКС | =ИНДЕКС(ссылка;номер_строки;номер;столбца) | Возвращает значение из ячейки, которая лежит в ссылке на пересечении заданных порядковыми номерами строки и столбца |
| ПОИСКПОЗ | =ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления) | Ищет в просматриваемом массиве значение и возвращает его позицию относительно начала массива. Тип сопоставления: -1 – ближайшее сверху; 1 – ближайшее снизу; 0 – поиск точного совпадения |
| ТРАНСП | =ТРАНСП(ссылка) | Транспонирует (меняет местами строки и столбцы) заданный диапазон ячеек |
Если аргумент «интервальный_просмотр» функций ВПР и ГПР имеет значение ИСТИНА или опущен, то при отсутствии точного соответствия возвращается ближайшее снизу значение. При этом значения в первом столбце или строке таблицы должны быть отсортированы по возрастанию, в противном случае функция может выдать неправильный результат. Если аргумент имеет значение ЛОЖЬ, то функция ищет точное соответствие. Если значения не могут быть отсортированы, предпочтительнее использовать функции ПОИСКПОЗ и ИНДЕКС.
