У меня есть электронная таблица со столбцом под названием «Дата состояния», расположенным в ряд с несколькими другими ячейками, некоторые с другими датами, некоторые с просто текстом, а некоторые оценивают, является ли набор условий истинным, и в этом случае результатом является ответ «Да» или «Нет», который затем условно форматируется для заполнения этой ячейки на основе ответа.
Я хотел бы иметь возможность заполнять и «блокировать» значение ячейки «Status Date» сегодняшней датой, если какая-либо из других ячеек в этой строке изменится из-за редактирования или добавления/удаления информации, включая те, которые содержат формулы, основанные на значениях в других ячейках. Она должна оставаться статичной, пока что-то еще не изменится в той же строке в более позднее время. Ни одно из других значений ячеек не оценивается в столбце «Status Date», поэтому, если его нужно переместить в столбец A или B для работы формулы или, может быть, для использования макроса VBA, это не должно быть проблемой.
решение1
Метод VBA
Этот тип автоматического временного штампа возможен только при написании очень простого VBA в фоновом режиме Excel. Посмотрите, например, на эту ссылку, чтобы начать:
https://docs.microsoft.com/en-us/office/troubleshoot/excel/run-macro-cells-change
К сожалению, макросы VBA не всегда являются вариантом, особенно если файл будет использоваться совместно. Некоторые настройки безопасности предприятия полностью блокируют макросы, не говоря уже о сложности написания кода VBA, если вы не знакомы с основами программирования.
Метод проверки данных с временной меткой
К счастью, есть два обходных пути для временной метки в Excel без VBA. Один из них — использовать проверку данных типа списка для «выборки» текущего времени. Установите одну ссылочную ячейку или именованный диапазон равными =TODAY() или =NOW(), которые будут автоматически обновляться каждый раз, когда вы редактируете любую ячейку (т. е. пересчитываете), поскольку NOW/TODAY — это изменчивая функция. Установите проверку данных (на ленте данных) для ячейки ввода даты, выберите список и обратитесь к ячейке TODAY/NOW. Это дает раскрывающийся список со временем, отражающим последний пересчет. После выбора из раскрывающегося списка он копирует значение времени и «заблокирует» его — НО вам все равно нужно выбрать его самостоятельно.
Метод круговой формулы
Другой метод требует изменения настроек Excel для разрешения циклических формул/итеративных вычислений, что не идеально, поскольку это глобальная настройка. Но это работает очень хорошо и именно так, как вы ожидаете в своем вопросе. Смотрите здесь:
https://howtoexcelatexcel.com/blog/create-a-timestamp-in-excel-with-formulas/
За и против
Метод VBA работает очень надежно и наиболее подходит для одного пользователя или в случаях, когда пользователи знакомы с разрешением макросов на основе настроек безопасности. Требуются некоторые базовые навыки кодирования. Кроме того, после выполнения макроса ваша история отмен обычно теряется.
Метод проверки данных очень прост в реализации и работает из коробки без специальных настроек или допусков безопасности. Однако требуется, чтобы пользователь был последователен в использовании раскрывающейся ячейки для временной метки (после внесения других изменений в строку).
Метод круговой формулы автоматически отображает временную метку по мере ввода данных в другие ячейки, но необходимо включить глобальную настройку Excel для итерации с некоторым неинтуитивным поведением. Хотя настройка сохраняется с рабочей книгой, если вы откроете любую другую рабочую книгу до этой, настройка сбрасывается на отсутствие итерации. Открытие этой рабочей книги не меняет ее обратно. Сначала необходимо открыть эту рабочую книгу. Кроме того, открытие и сохранение других рабочих книг затем сохраняет глобальную настройку с этими файлами, что может стать (незначительной) проблемой. В основном это отключает отчеты об ошибках при вводе круговой формулы в обычных рабочих книгах.
Ps. Метод сочетания клавиш
Иногда самый простой метод не самый идеальный с точки зрения IT-специалиста, но он работает для конечного пользователя. Вы можете нажать одну из этих комбинаций клавиш, чтобы просто вставить текущее время или дату в текущую ячейку в качестве значения:
CTRL and ; (to insert the current date)
CTRL SHIFT and : (to insert the current time)