Меню

Excel не работают формулы с датами

Excel не работают формулы с датами

Что такое даты для Excel?

То есть 12.07.2016 12:50:30 для Excel значение — 42563,5350694,

Где 42563 — это порядковый номер дня с 1 января 1900 года, а часть после запятой — это время.

С датами можно производить вычисления. Например, вычитать или складывать даты.

Основные ошибки с датами и их решение

Перевод разных написаний дат

Разные системы в выгрузках выдают даты по-разному, например: 12.07.2016 12-07-16 16-07-12 и так далее. Иногда месяца пишут текстом. Для того, чтобы привести даты к одному формату мы используем функцию ДАТА:

Данный вариант подходит, когда количество символов в дате одинаковое. Если вы работаете с однотипными выгрузками еженедельно, создание дополнительного столбца и протягивание формулы будет простым решением.

Дата определяется как текст

Проблемы с датами начинаются, когда мы импортируем данные из других источников. При этом, даты могут выглядеть нормально, но при этом являться текстом. С ними нельзя проводить вычисления, группировки в сводных таблицах, сортировать. Есть 2 решения, которые позволяют сделать это автоматически: 1. Получение значения даты; 2. Умножение текстового значения на единицу.

Получение значения даты

С помощью формулы ЗНАЧ мы выводим текстовое значение даты, потом его форматируем как Дату:

Умножение текстового значения на единицу

Принцип аналогичный предыдущему, только мы текстовое значение умножаем на единицу и после этого форматируем как дату:

Примечание: если дата определилась как текст, то вы не сможете делать группировки. При этом дата будет выровнена по левому краю. Excel выравнивает числа и даты по правому краю.

Очистка дат от некорректных символов
Чтобы привести к нужному стандарту, часть дат можно очистить с помощью функции Найти и Заменить. Например, поменять слэши («/») на точки:

То же самое можно сделать формулой ПОДСТАВИТЬ

Источник

Проблемы с вычислением формул в Microsoft Excel

Одной из наиболее востребованных возможностей Excel является работа с формулами. Благодаря данной функции программа самостоятельно производит различного рода расчеты в таблицах. Но иногда случается так, что пользователь вписывает формулу в ячейку, но она не выполняет своего прямого назначения – вычисления результата. Давайте разберемся, с чем это может быть связано, и как решить данную проблему.

Устранение проблем с вычислением

Причины проблем с вычислением формул в Экселе могут быть совершенно разными. Они могут быть обусловлены, как настройками конкретной книги или даже отдельного диапазона ячеек, так и различными ошибками в синтаксисе.

Способ 1: изменение формата ячеек

Одной из самых распространенных причин, по которой Эксель вообще не считает или не правильно считает формулы, является неверно выставленный формат ячеек. Если диапазон имеет текстовый формат, то расчет выражений в нем вообще не производится, то есть, они отображаются как обычный текст. В других случаях, если формат не соответствует сути рассчитываемых данных, результат, выводимый в ячейку, может отображаться некорректно. Давайте выясним, как решить эту проблему.

  1. Для того, чтобы посмотреть, какой формат имеет конкретная ячейка или диапазон, переходим во вкладку «Главная». На ленте в блоке инструментов «Число» имеется поле отображения текущего формата. Если там указано значение «Текстовый», то формула точно вычисляться не будет.

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

Но выбор типов формата через ленту не такой обширный, как через специализированное окно. Поэтому лучше применить второй вариант форматирования. Выделяем целевой диапазон. Кликаем по нему правой кнопкой мыши. В контекстном меню выбираем пункт «Формат ячеек». Можно также после выделения диапазона нажать комбинацию клавиш Ctrl+1.

Читайте также:  Нет работы временно предприятие не работает
  • Выделите поочередно ячейки, в которых функция не считалась, и для пересчета нажмите функциональную клавишу F2.
  • Теперь будет производиться расчет формулы в стандартном порядке с выводом результата в указанную ячейку.

    Способ 2: отключение режима «Показать формулы»

    Но возможно причина того, что вместо результатов расчета у вас отображаются выражения, состоит в том, что в программе включен режим «Показать формулы».

    1. Чтобы включить отображение итогов, переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул», если кнопка «Показать формулы» активна, то кликаем по ней.

  • После этих действий в ячейках снова вместо синтаксиса функций начнет отображаться результат.
  • Способ 3: исправление ошибки в синтаксисе

    Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».

    В таких случаях нужно внимательно пересмотреть синтаксис тех формул, которые неправильно отображаются, и внести в них соответствующие коррективы.

    Способ 4: включение пересчета формулы

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

      Перейдите во вкладку «Файл». Находясь в ней, следует кликнуть по пункту «Параметры».

  • Откроется окно параметров. Нужно перейти в раздел «Формулы». В блоке настроек «Параметры вычислений», который расположен в самом верху окна, если в параметре «Вычисления в книге», переключатель не установлен в позицию «Автоматически», то это и есть причина того, что результат вычислений неактуальный. Переставляем переключатель в нужную позицию. После выполнения вышеуказанных настроек для их сохранения в нижней части окна жмем на кнопку «OK».
  • Теперь все выражения в данной книге будут автоматически пересчитываться при изменении любого связанного значения.

    Способ 5: ошибка в формуле

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

    В этом случае нужно проверить, правильно ли записаны данные в ячейках, на которые ссылается выражение, нет ли в них ошибок в синтаксисе или не заложено ли в самой формуле какое-либо некорректное действие (например, деление на 0).

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

      Выделяем ячейку с ошибкой. Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» кликаем по кнопке «Вычислить формулу».

  • Открывается окно, в котором представляется полный расчет. Нажимаем на кнопку «Вычислить» и просматриваем вычисление пошагово. Ищем ошибку и устраняем её.
  • Как видим, причины того, что Эксель не считает или не правильно считает формулы, могут быть совершенно различными. Если вместо расчета у пользователя отображается сама функция, тот в этом случае, скорее всего, либо ячейка отформатирована под текст, либо включен режим просмотра выражений. Также, возможна ошибка в синтаксисе (например, наличие пробела перед знаком «=»). В случае если после изменения данных в связанных ячейках результат не обновляется, то тут нужно посмотреть, как настроено автообновление в параметрах книги. Также, нередко вместо корректного результата в ячейке отображается ошибка. Тут нужно просмотреть все значения, на которые ссылается функция. В случае обнаружения ошибки следует устранить её.

    Читайте также:  Путевки всем не работает

    Помимо этой статьи, на сайте еще 12333 инструкций.
    Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Источник

    Применение функции СЕГОДНЯ в Microsoft Excel

    Одной из интересных функций Microsoft Excel является СЕГОДНЯ. С помощью этого оператора производится ввод в ячейку текущей даты. Но его можно также применять и с другими формулами в комплексе. Рассмотрим основные особенности функции СЕГОДНЯ, нюансы ее работы и взаимодействия с другими операторами.

    Использование оператора СЕГОДНЯ

    Функция СЕГОДНЯ производит вывод в указанную ячейку даты, установленной на компьютере. Она относится к группе операторов «Дата и время».

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

    Для того, чтобы проверить установлен ли автоматический пересчет в конкретном документе нужно выполнить ряд последовательных действий.

      Находясь во вкладке «Файл», переходим по пункту «Параметры» в левой части окна.

  • После того, как активировалось окно параметров, переходим в раздел «Формулы». Нам понадобится самый верхний блок настроек «Параметры вычислений». Переключатель параметра «Вычисления в книге» должен быть установлен в позицию «Автоматически». Если он находится в другой позиции, то следует его установить так, как было сказано выше. После изменения настроек нужно нажать на кнопку «OK».
  • Теперь при любом изменении в документе будет выполняться его автоматический пересчет.

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

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

    Способ 1: введение функции вручную

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

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

  • Для того, чтобы произвести расчет и вывести результат на экран, жмем на кнопку Enter.
  • Способ 2: применение Мастера функций

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

      Выделяем ячейку на листе, в которую будет выводиться дата. Кликаем по пиктограмме «Вставить функцию», расположенной у строки формул.

    Происходит запуск Мастера функций. В категории «Дата и время» или «Полный алфавитный перечень» ищем элемент «СЕГОДНЯ». Выделяем его и жмем на кнопку «OK» в нижней части окна.

    Открывается небольшое информационное окошко, в котором сообщается о назначении данной функции, а также говорится о том, что она не имеет аргументов. Жмем на кнопку «OK».

  • После этого дата, установленная на компьютере пользователя в данный момент, будет выведена в предварительно указанную ячейку.
  • Способ 3: изменение формата ячейки

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

    Читайте также:  Не могу работать бухгалтером что делать

    Для того, чтобы посмотреть значение формата отдельной ячейки или области на листе, нужно выделить нужный диапазон и находясь во вкладке «Главная» взглянуть, какое значение установлено в специальной форме формата на ленте в блоке инструментов «Число».

    Если после ввода формулы СЕГОДНЯ в ячейке автоматически не был установлен формат «Дата», то функция будет некорректно отображать результаты. В этом случае необходимо произвести изменение формата вручную.

      Кликаем правой кнопкой мыши по ячейке, в которой нужно изменить формат. В появившемся меню выбираем позицию «Формат ячеек».

    Открывается окно форматирования. Переходим во вкладку «Число» в случае, если оно было открыто в другом месте. В блоке «Числовые форматы» выделяем пункт «Дата» и жмем на кнопку «OK».

  • Теперь ячейка отформатирована правильно и в ней отображается именно сегодняшняя дата.
  • Кроме того, в окне форматирования также можно поменять представление сегодняшней даты. По умолчанию установлен формат по шаблону «дд.мм.гггг». Выделяя различные варианты значений в поле «Тип», которое расположено в правой части окна форматирования, можно изменять внешний вид отображения даты в ячейке. После изменений не забывайте жать на кнопку «OK».

    Способ 4: использование СЕГОДНЯ в комплексе с другими формулами

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

    Оператор СЕГОДНЯ очень удобно применять для вычисления временных интервалов, например, при указании возраста человека. Для этого в ячейку записываем выражение такого типа:

    Для применения формулы жмем на кнопку ENTER.

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

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

    Если нужно постоянно иметь на виду дату на три дня назад, то формула будет выглядеть так:

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

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

    То есть, в феврале в ячейке будет стоять цифра 2, в марте – 3 и т.д.

    С помощью более сложной формулы можно вычислить, сколько дней пройдет от сегодняшнего дня до наступления конкретной даты. Если настроить правильно пересчет, то таким способом можно создать своеобразный обратный таймер отсчета до заданной даты. Шаблон формулы, которая имеет подобные возможности, выглядит следующим образом:

    Вместо значения «Заданная дата» следует указать конкретную дату в формате «дд.мм.гггг», до которой нужно организовать отсчет.

    Обязательно следует отформатировать ячейку, в которой будет выводиться данный расчет, под общий формат, иначе отображение результата будет некорректным.

    Существует возможность комбинации и с другими функциями Excel.

    Как видим, с помощью функции СЕГОДНЯ можно не только просто выводить актуальную на текущий день дату, но и производить многие другие вычисления. Знание синтаксиса этой и других формул поможет моделировать различные комбинации применения данного оператора. При правильной настройке пересчета формул в документе его значение будет обновляться автоматически.

    Помимо этой статьи, на сайте еще 12333 инструкций.
    Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Источник

    Adblock
    detector