Если ячейки содержат неправильный формат значений – это может привести к ошибочным вычислениям в формулах. Например, вместо типа значения «Дата», ячейка содержит тип значения «Текст». При подготовке больших объемов данных следует выполнить проверку всех типов значений на соответствие определенному формату. Например, таблица заполнялась данными из разных источников где в разный способ фиксировалась дата. С такой таблицей нельзя выполнять различных вычислений. Визуально сложно заметить где в неправильном формате введена неправильная дата с точки зрения программы Excel.
Поиск формата текста вместо даты в Excel
Чтобы быстро найти ошибочные значения в Excel и выделить цветом все ячейки с неправильным форматом, будем использовать условное форматирование. Для примера возьмем простую таблицу:
Поиск и выделение цветом ячеек с неправильным форматом отображения значений:
Как видно на рисунке все даты в формате текст выделились цветом:
В условиях форматирования мы использовали простую функцию =ЕТЕКСТ(), у которой всего только 1 аргумент – ссылка на проверяемую ячейку. Арес ссылки в аргументе функции ЕТЕКСТ должен быть относительным, так как будет проверятся каждая ячейка выделенного диапазона. Если текущая проверяемая ячейка содержит текст (а не дату) – это неправильное значение Excel. Тогда функция ЕТЕКСТ возвращает значение ИСТИНА и к этой ячейке сразу же присваивается новый формат (зеленая заливка). Название функции ЕТЕКСТ следует читать как сокращение от двух слов: Если ТЕКСТ
Читаем логические функции, которыми можно проверить другие форматы и типы данных в ячейках таким же способом:
- ЕНЕТЕКСТ – если не текст (функция так же позволяет быстро найти дату в тексте Excel);
- ЕЧИСЛО – если число (позволяет быстро находить неправильный формат чисел в Excel);
- ЕОШ – если ошибка;
- ЕОШИБКА – если ошибка;
- ЕСЛИОШИБКА – если ошибка (это не логическая функция, но ее легко оптимизировать под данную задачу);
- ЕПУСТО – если пусто;
- ЕЛОГИЧ – если логическое значение;
- ЕНД – если недоступное значение (#Н/Д);
- ЕНЕЧЁТ – если нечетное значение;
- ЕЧЁТ – если четное значение;
- ЕССЫЛКА – если ссылка;
- ЕФОРМУЛА – если формула.
При желании можете проверить все функции в действии экспериментальным путем.
Одной из интересных функций Microsoft Excel является СЕГОДНЯ . С помощью этого оператора производится ввод в ячейку текущей даты. Но его можно также применять и с другими формулами в комплексе. Рассмотрим основные особенности функции СЕГОДНЯ , нюансы ее работы и взаимодействия с другими операторами.
Функция СЕГОДНЯ производит вывод в указанную ячейку даты, установленной на компьютере. Она относится к группе операторов «Дата и время» .
Но нужно понимать, что сама по себе данная формула не будет обновлять значения в ячейке. То есть, если вы через несколько дней откроете программу и не пересчитаете в ней формулы (вручную или автоматически), то в ячейке будет установлена все та же дата, а не актуальная на данный момент.
Для того, чтобы проверить установлен ли автоматический пересчет в конкретном документе нужно выполнить ряд последовательных действий.
Теперь при любом изменении в документе будет выполняться его автоматический пересчет.
Если по каким-то причинам вы не желаете устанавливать автоматический пересчет, то для того, чтобы актуализировать на текущую дату содержимое ячейки, которая содержит функцию СЕГОДНЯ , нужно её выделить, установить курсор в строку формул и нажать кнопку Enter .
В этом случае, при отключении автоматического пересчета он будет выполнен только относительно данной ячейки, а не по всему документу.
Способ 1: введение функции вручную
Данный оператор не имеет аргумента. Синтаксис его довольно прост и выглядит следующим образом:
СЕГОДНЯ()
Способ 2: применение Мастера функций
Кроме того, для введения этого оператора можно использовать Мастер функций . Такой вариант особенно подойдет начинающим пользователям Excel, которые ещё путаются в названиях функций и в их синтаксисе, хотя в данном случае он максимально прост.
Способ 3: изменение формата ячейки
Если перед вводом функции СЕГОДНЯ ячейка имела общий формат, то она автоматически будет переформатирована в формат даты. Но, если диапазон был уже отформатирован под другое значение, то оно не изменится, а значит, формула будет выдавать некорректные результаты.
Для того, чтобы посмотреть значение формата отдельной ячейки или области на листе, нужно выделить нужный диапазон и находясь во вкладке «Главная» взглянуть, какое значение установлено в специальной форме формата на ленте в блоке инструментов «Число» .
Если после ввода формулы СЕГОДНЯ в ячейке автоматически не был установлен формат «Дата» , то функция будет некорректно отображать результаты. В этом случае необходимо произвести изменение формата вручную.
Кроме того, в окне форматирования также можно поменять представление сегодняшней даты. По умолчанию установлен формат по шаблону «дд.мм.гггг» . Выделяя различные варианты значений в поле «Тип» , которое расположено в правой части окна форматирования, можно изменять внешний вид отображения даты в ячейке. После изменений не забывайте жать на кнопку «OK» .
Способ 4: использование СЕГОДНЯ в комплексе с другими формулами
Кроме того, функцию СЕГОДНЯ можно использовать, как составляющую часть комплексных формул. В данном качестве этот оператор позволяет решать гораздо более широкие задачи, чем при самостоятельном использовании.
Оператор СЕГОДНЯ очень удобно применять для вычисления временных интервалов, например, при указании возраста человека. Для этого в ячейку записываем выражение такого типа:
ГОД(СЕГОДНЯ())-1965
Для применения формулы жмем на кнопку ENTER .
Теперь в ячейке при правильной настройке пересчета формул документа постоянно будет отображаться актуальный возраст человека, который был рожден в 1965 году. Аналогичное выражение можно применить и для любого другого года рождения или для вычисления годовщины события.
Существует также формула, которая в ячейке отображает значения на несколько дней вперед. Например, для отображения даты через три дня она будет выглядеть следующим образом:
СЕГОДНЯ()+3
Если нужно постоянно иметь на виду дату на три дня назад, то формула будет выглядеть так:
СЕГОДНЯ()-3
Если нужно отобразить в ячейке только номер текущего числа в месяце, а не дату полностью, то применяется такое выражение:
ДЕНЬ(СЕГОДНЯ())
Аналогичная операция для показа номера актуального месяца будет выглядеть следующим образом:
МЕСЯЦ(СЕГОДНЯ())
Функция ДАТА() , английский вариант DATE(), в озвращает целое число, представляющее определенную дату. Формула =ДАТА(2011;02;28) вернет число 40602. Если до ввода этой формулы формат ячейки был задан как Общий, то результат будет отформатирован как дата, т.е. 28.02.2011.
Синтаксис функции
ДАТА (год ;месяц ;день )
Год
- аргумент, который может иметь от одной до четырех цифр.
Месяц
- положительное или отрицательное целое число в диапазоне от 1 (январь) до 12 (декабрь), представляющее месяц года.
День
- положительное или отрицательное целое число в диапазоне от 1 до 31, представляющее день месяца.
В EXCEL даты хранятся в виде последовательности чисел (1, 2, 3, ...), что позволяет выполнять над ними вычисления. По умолчанию день 1 января 1900 г. имеет номер 1, а 28 февраля 2011 г. - номер 40602, так как интервал между этими датами составляет 40 602 дня. О том как EXCEL хранит дату и время, читайте эту .
Примеры
Для того, чтобы прибавить к дате 28.02.2011, содержащейся в ячейке А1
, например, 5 лет, можно использовать следующую формулу:
=ДАТА(ГОД(A1)+5;МЕСЯЦ(A1);ДЕНЬ(A1))
получим результат 28.02.2016
Для того, чтобы прибавить к дате 28.02.2011, например, 15 месяцев, можно использовать следующую формулу:
=ДАТА(ГОД(A1);МЕСЯЦ(A1)+15;ДЕНЬ(A1))
или формулу =ДАТАМЕС(A1;15)
получим результат 28.05.2012
Примечание . При прибавлении месяцев между ДАТАМЕС() и ДАТА() существует разница. Прибавим к 30.01.2009 один месяц:
- =ДАТАМЕС("30.01.2009";1) вернет 28.02.2009, т.к. 30 февраля не существует, то функция вернет последний день месяца, т.е. 28.02.2009 ("лишние" 2 дня будут отброшены);
- =ДАТА(ГОД("30.01.2009");МЕСЯЦ("30.01.2009")+1;ДЕНЬ("30.01.2009")) вернет 02.03.2009: "лишние" 2 дня (29 и 30 февраля) будут прибавлены к дате.
Если записать формулу =ДАТА(2008;3;) , то формула вернет 29.02.2008. Т.е. опуская последний аргумент День мы тем самым задаем его равным 0. Поэтому формула возвращает