Excel SUMIFS Формула Критерии последовательной даты?

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

Итак... вместо текста, такого как "1 июня", выберите что-то вроде ROW(), которое даст вам число, основанное на строке, в которой вы находитесь. Допустим, вы начинаете со строки 3:

=ROW()-2

даст "1" для результата. Объедините его с конкатенацией или аналогичной опцией:

=CONCATENATE("June "&(ROW()-2))

в результате вы получите "1 июня" в строке 3. Замените июньскую часть на "6" и "/", а затем переведите год в конец (вместе со своим собственным "/") для ввода в Функция DATEVALUE:

=DATEVALUE( CONCATENATE( "6/" & (ROW()-2) & "/2018" ))

yielding = DATEVALUE ("01.06.2008"), который даст "реальную" дату, которую Excel может использовать в качестве третьего аргумента в вашей функции.

Когда вы копируете его в строки, он будет добавлять "1" и давать вам даты повышения, о которых вы просили.

Чтобы отобразить дату "1 июня", а не какой-то непривычный для Excel способ отображения даты, отформатируйте ячейки следующим образом:

мммм д

Использование четырех символов "m" даст вам часть "июнь", пробел даст вам... пробел... и один "d" даст вам день без использования стиля "01". Это будет "1" или "22", но без "0". (Или используйте два символа "d", чтобы получить двухзначные дни с ведущими 0 с 1-го по 9-е, если хотите).

Теперь, если вы хотите стать более модным, вы можете проверить, существует ли дата или нет. Например, вы должны скопировать 31 ячейку, чтобы охватить месяцы, например, июль. Но когда наступит февраль, последние три покажут ужасную ОШИБКУ! ОШИБКА! ОШИБКА! ярлыки, которые будут беспокоить ваших пользователей. Поэтому оберните приведенную выше формулу DATEVALUE() в формулу IFERROR():

=IFERROR( DATEVALUE( CONCATENATE( "6/" & (ROW()-2) & "/2018" )), "" )

Вы можете изменить часть "6/" для ссылки на некоторую ячейку, которая содержит название месяца, или некоторые другие варианты, даже такие звонки и свистки, как выпадающие списки, которые выбирает пользователь, чтобы изменить месяцы. Поиск номера месяца по названию и по скрытой где-то таблице с помощью VLOOKUP() является довольно стандартным, или вы можете получить фанки и использовать имя месяца в формуле DATEVALUE(), которая меняет его, и в стандартном добавлении, например "-2018" (год подойдет, если он пройдет после 1900 года) и обернуть его в МЕСЯЦ (): (скажем, название вашего месяца в ячейке А1)

=MONTH( A1 & "-2018" )

который дает "6". Оберните его в IFERROR() на случай, если введено что-то неправильное, и у вас может быть возможность выдать собственное сообщение об ошибке:

=IFERROR( MONTH( A1 & "-2018" ), "Mmm... spelling?" )

или что есть у тебя.

Это становится сложнее, если вы хотите, чтобы показывалось более одного месяца, но не намного. Просто измените приведенную выше формулу, в которой используется обертка IFERROR() и используется "" (пусто), если номер дня был слишком высоким в течение месяца. Замените часть "" на часть DATEVALEU(), но добавьте к ней "1", чтобы перейти к следующему дню, который начнется в следующем месяце, а затем...

Да, и тогда... проблема возникает. Это не пойдет на второй день. Может быть, тестирование в течение месяца и добавление "1"... здесь появляется более простая идея... почему бы просто не взять предыдущую дату и добавить "1" в ИТ-отдел вместо того, чтобы продолжать делать все более сложную формулу работы?

Но... вы знаете... мы могли бы сделать это во 2-й ячейке... хорошая формула, показанная в первой ячейке, тогда этот более простой подход для ВСЕХ ячеек впоследствии и месячных забот о вас позаботятся... прыжок лет тоже...

Формула для использования в строке 4 и во всех строках после нее:

=A3 + 1

(замените "A" на любой столбец, который вы используете, конечно).

Теперь у вас осталась одна проблема, если вы хотите только один месяц за раз, и вы делаете это. (Помните, что формула работала очень хорошо в течение одного месяца, так что вы можете просто придерживаться ее.) В настоящее время нет способа заметить изменение месяца. Таким образом, февраль будет идти 2-28, 3-1, 3-2, 3-3... и выглядеть немного... ну, suppid...

Вы могли бы добавить немного больше, чтобы решить это. Проверьте MONTH() результата против MONTH() ячейки выше и выведите "", если они не совпадают. Это решает первую такую ​​камеру, все месяцы, кроме февраля. Добавьте тест, используя OR (), чтобы увидеть, является ли указанная выше ячейка пустой, и выведите пустую, если она есть. Это решит февраль

=IF( OR( MONTH( A3 + 1 ) <> MONTH( A2 ), A2 = "" ), "", A3 + 1 )

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

Заметьте также, что решение каждой проблемы по мере ее приближения приводило к все большей и большей сложности, пока все ваши начальные проблемы не были решены, каким-то образом и разумными подходами, но не оставили вас с трудом улучшать и расширять часть работы, хотя вы ничего не делали глупо или неправильно. Стратегическая мысль, которую вы вложили в нее с самого начала, возможно, дала бы вам совершенно простой подход "просто добавьте 1" в начале, и никакие проблемы на самом деле не нуждались бы в решении. У вас была бы простая, расширяемая часть работы за меньшее время и с меньшим обострением с самого начала.

(Недостаток: вы ДЕЙСТВИТЕЛЬНО учитесь, изучая сложный путь надстройки и джерри-риг. Это должно быть забавно, но это тоже правда. Но это не было вашей главной целью, я держу пари.)

Excel обычно хорошо разбирается в последовательности чисел, но иногда ваши данные не всегда в предсказуемой последовательности, поэтому вам нужно дать некоторые подсказки. Часто просто вставить "1 июня" в одну ячейку, отформатировать его так, как вы хотите, и затем перетащить квадратный маркер в нижнем правом углу прямоугольника выбора. С другой стороны, если некоторые из ваших дат перепрыгивают или какие-то другие факторы, Excel не всегда будет делать правильное предположение и может просто напечатать одно и то же значение снова и снова.

Если вы хотите перетащить даты:

1. Введите 1 июня в одну клетку
2. Введите 2 июня в клетку под ним. Это дает Excel подсказку относительно шаблона, который вы хотите повторить, что необходимо, если все остальные элементы в
3. Скорее всего, Excel определит их как дату и переформатирует ваши ячейки в некоторый формат даты, отличный от "1 июня" (например, "1 июня"). Выделите ячейки и измените форматирование чисел на пользовательский формат: [$-409]mmmm d;@ так что он будет отображать ячейки, как вы описываете, без печати года.
4. Как только ячейки отформатированы так, как вы хотите, выберите обе ячейки.
5. Наведите курсор на нижнюю правую квадратную точку в прямоугольнике выбора. Ваш курсор должен измениться на символ "+".
6. Перетащите "+" вниз с помощью основной кнопки мыши, и рисунок повторится. Для более сложных параметров (например, только повторяющиеся дни недели) перетащите с помощью дополнительной кнопки мыши, и появится контекстное меню с различными вариантами.

Это должно повторить последовательность. Это также хорошо для других моделей, таких как пропуск каждые 3 дня, если вы использовали 1 июня и 4 июня в качестве семян шаблона.