Вопрос или проблема
Рассмотрим следующий пример:
Общая картина
Мне нужно вычесть 2 недели из всех дат в столбце B, только когда время в столбце D совпадает с определенным временем (в этом примере 21:00), независимо от типа категории в столбце C. Игнорируйте столбец E, так как это вычисляемый столбец (F – D).
Проблема
Мне трудно представить, как я смогу пометить строку 6 и строку 11, если я сгруппирую по датам (B2 и B15). Ошибаюсь ли я, считая, что каждая строка должна иметь дату для работы INDEX/MATCH?
Я нашел следующую формулу, которая помогает, но буду признателен за любую помощь или другой тип формулы. Возможно, я слишком зацикливаюсь на этом:
- Вычитание недель из даты – легкая часть
- Сопоставление индекс и время – не так уж легко. Обратите внимание, что я хочу, чтобы время совпадало, а не находилось между. Я также думал о VLOOKUP, если сопоставление не сработает, но опять же, нужна ли мне дата в каждой строке?
Ответ или решение
Для решения вашей задачи по вычитанию 2 недель из дат в столбце B на основе времени в столбце D, мы можем использовать формулу Excel, которая проверяет время и при необходимости производит вычитание. Предположим, что ваши данные находятся в диапазоне от B2 до D15.
Шаги для создания формулы:
-
Определение условий: Нам нужно вычесть 2 недели только из дат, когда время в столбце D равно 9PM (21:00).
-
Формула для вычитания: Для начала, используем следующую формулу в столбце E (например, в ячейке E2), чтобы вывести новую дату с учетом нашего условия. Формула будет выглядеть так:
=ЕСЛИ(D2="21:00", B2-14, B2)В данной формуле:
ЕСЛИпроверяет условие, равен ли элемент в D2 21:00 (9PM).- Если условие истинно, из даты в B2 вычитаются 14 дней (2 недели).
- Если условие ложно, возвращается изначальная дата.
-
Распространение формулы: После ввода формулы в ячейку E2, вы можете протянуть её вниз до конца вашего диапазона (E15), чтобы применить её ко всем строкам.
Объяснение проблемы с INDEX/MATCH:
Ваше беспокойство о необходимости наличия даты для работы формул INDEX/MATCH оправдано, однако в данном случае использование формулы типа ЕСЛИ является более подходящим, так как мы можем без проблем работать с каждой строкой и применять нужное изменение в зависимости от условия.
Функции INDEX/MATCH, как вы заметили, могут быть сложными для применения в данной ситуации, и для данной задачи проще использовать подход с условием.
Применение VLOOKUP (если это необходимо):
Если вам всё же необходимо использовать VLOOKUP или любую другую подобную функцию для поиска и сопоставления данных, вы можете создать дополнительный столбец, который будет содержать только те строки, где время соответствует вашему требованию (например, 9PM). Это позволит вам упростить поиск и управление данными.
Заключение:
Таким образом, с помощью вышеуказанной формулы вы сможете без труда вычитать 2 недели из нужных дат в столбце B на основании времени в столбце D. Это решение является простым и эффективным, не требуя создания дополнительных сложных формул для сопоставлений. Если у вас возникнут дополнительные вопросы или понадобятся уточнения, пожалуйста, не стесняйтесь обращаться.