В Excel, как я могу автоматически считать записи, сделанные в соответствии с каждым месяцем?

Question

В следующем листе Excel есть даты в столбце A2:A23 в формате dd-mm-yyyy . В B2:B23 дни B23 записываются по формуле =TEXT(A2,"ddd") . В столбце C2:C23 записи "Y" или "N". Записи были сделаны для каждого месяца с октября 2016 года по март 2017 года в столбце C Как я могу автоматически записывать все месяцы из диапазона A2:A23 , скажем, например, в столбце E и отображать количество записей «Y /N», соответствующих каждому месяцу?

Например, я хочу увидеть результат, подобный приведенному в столбцах E и F Записи в E и F заполняются вручную, но я хочу заполнить эти записи автоматически.

Мне известна формула типа =SUMPRODUCT(--(MONTH(A2:A23)=MONTH(E))) , но для этого мне нужно вручную написать месяцы в столбце E из A2:A23 .

+------------+-----+------------+--+-------+---------------+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
|    Date    | Day | Entry(Y/N) |  | Month | Number of Y/N |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
+------------+-----+------------+--+-------+---------------+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| 15-10-2016 | Sat | Y          |  | Oct   |             4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 17-10-2016 | Mon | Y          |  | Nov   |             5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 19-10-2016 | Wed | Y          |  | Dec   |             5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 23-10-2016 | Sun | Y          |  | Jan   |             4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 01-11-2016 | Tue | Y          |  | Feb   |             2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 05-11-2016 | Sat | N          |  | Mar   |             2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 11-11-2016 | Fri | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 15-11-2016 | Tue | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 22-11-2016 | Tue | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 05-12-2016 | Mon | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 09-12-2016 | Fri | N          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 11-12-2016 | Sun | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 23-12-2016 | Fri | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 26-12-2016 | Mon | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 02-01-2017 | Mon | N          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 08-01-2017 | Sun | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 10-01-2017 | Tue | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 22-01-2017 | Sun | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 08-02-2017 | Wed | N          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 10-02-2017 | Fri | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 11-03-2017 | Sat | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 15-03-2017 | Wed | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
|            |     |            |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
+------------+-----+------------+--+-------+---------------+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

Обновление: позвольте мне объяснить, что я сделал. В E2 я написал =A2 и отформатировал эту ячейку, используя пользовательский формат mmm, который помог мне отобразить Oct в E2 , в E3 я использовал формулу =DATE(YEAR(E2),MONTH(E2)+1,DAY(E2)) , эта формула помогла мне отобразить ноябрь в E3 и так далее. В F2 я написал =SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2))) который дал счет 4, и впоследствии в F3 я написал =SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2)+1)) , в F4 =SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2)+2)) и так далее. Все хорошо до Dec , после этого нулевой результат отображается для Jan , Feb и March . Я думаю, что приращение в команде = SUMPRODUCT(-(MONTH($ A $ 2:$ A $ 23) = MONTH(A2)+1)) дает мне ошибочный результат после Dec , который, конечно, не дает Jan, когда приращение равно данный месяц Dec

Answer 1

Два подхода:

Грязная формула:

Установите E2 на =A2 .  Установите E3 в

=IF(OR(E2=0,E2=""), "", MIN(IF(A$2:A$23>=DATE(YEAR(E2),MONTH(E2)+1,1), A$2:A$23, "")))

и нажмите Ctrl+Shift+Enter.  Перетащите / заполните это.  Это будет изначально производить

15-Oct-2016
1-Nov-2016
5-Dec-2016
2-Jan-2017
8-Feb-2017
11-Mar-2017
0-Jan-1900

(Здесь я отформатировал ячейки как встроенный формат 14-Mar-2012 , который реализован как [$-409]d-mmm-yyyy;@ .)  E2 явно A2 , первое свидание.  Затем DATE(YEAR(E2),MONTH(E2)+1,1) оценивается до 1 ноября 2016 года; первый день месяца, следующего за датой в E2 .  И наконец, полная формула в E3 (приведенная выше) находит самую низкую / самую раннюю (минимальную) дату в A2:A23 которая ≥ этой даты.  Несколько запутанно, это 1 ноября 2016 года, так как это в вашем списке дат.  Но E4 - 5 декабря 2016 года, потому что это самая низкая дата ≥ 1 декабря 2016 года.

E8 ищет самую низкую дату ≥ 1 апреля 2017 года.  Их нет, поэтому мы получаем MIN("") , который оценивается как 0, который отображается как 0-Jan-1900.  Вот почему формула начинается с IF(OR(E2=0,E2=""), "", … - чтобы предотвратить связывание формулы в узлах.  Если вы измените формат отображения для столбца E на [$-409]mmm yyyy;; (удалив начальный d- , (необязательно) изменив второй - на пробел, и изменив конечный @ на второй ;), вы получите

Oct 2016
Nov 2016
Dec 2016
Jan 2017
Feb 2017
Mar 2017

Это подавляет отображение дня месяца и полностью подавляет отображение записи за январь 1900 года.

Два вспомогательных столбца:

Установите M1 в =DATE(YEAR(A2), MONTH(A2), 1) .  Установите N2 в =N1+COUNTIF(M$2:M$23, E2) .  Установите E2 в =IFERROR(SMALL(M$2:M$23, N1+1), "") .  Перетащите / заполните.  Столбец M должен быть перемещен вниз до строки 23; двум другим нужно пройти достаточно далеко, чтобы создать отдельные месяцы.  Это дает нам

+----+------------+------------+-----+
|    |     E      |     M      |  N  |
+----+------------+------------+-----+
|  1 |            |            |     |
|  2 | 1-Oct-2016 | 1-Oct-2016 |  4  |
|  3 | 1-Nov-2016 | 1-Oct-2016 |  9  |
|  4 | 1-Dec-2016 | 1-Oct-2016 | 14  |
|  5 | 1-Jan-2017 | 1-Oct-2016 | 18  |
|  6 | 1-Feb-2017 | 1-Nov-2016 | 20  |
|  7 | 1-Mar-2017 | 1-Nov-2016 | 22  |
|  8 |            | 1-Nov-2016 | 22  |
|  9 |            | 1-Nov-2016 | 22  |
| 10 |            | 1-Nov-2016 | 22  |
| 11 |            | 1-Dec-2016 | 22  |
| 12 |            | 1-Dec-2016 | 22  |
                  |     ︙     |  ︙  |

где столбец M отслеживает столбец A и столбец N просто повторяет общее количество дней в вашей электронной таблице.

Столбец M четко показывает первый день месяца соответствующей даты в столбце A E2 - наименьшее из этих значений; SMALL(M$2:M$23, 1) . N2 подсчитывает даты в столбце M , которые равны этому значению (в октябре 4 даты), и добавляет их к N1 , которое является пустым (т. Е. Ноль).  (Вы можете поставить явный 0 в N1 если хотите.)  Тогда E3 является 5-ой самой маленькой датой, SMALL(M$2:M$23, 5) .  И так далее.  И, конечно, вы можете снова отформатировать столбец E чтобы подавить отображение дня месяца.

После того, как я набрал все вышесказанное, мне приходит в голову, что я мог бы сделать это с помощью INDEX вместо SMALL .

Заметка

В обоих приведенных выше решениях указаны только месяцы, указанные в столбце A Например, если в столбце A указаны даты в октябре 2016 г., ноябре 2016 г., декабре 2016 г., феврале 2017 г. и марте 2017 г. (но не в январе 2017 г.), в столбце E будут отображаться данные октября 2016 г., ноября 2016 г., декабря 2016 г., февраля 2017 г. и марта 2017 г. ( но не январь 2017).  Я сделал это, потому что вопрос говорит:«Как я могу автоматически писать все месяцы из диапазона A2:A23 ,…?»  Если вы хотите включить каждый месяц с октября 2016 г. ( A2) по март 2017 г. ( A23), включая месяцы, которых нет в столбце A , необходимо уточнить вопрос.

В любом случае,

Чтобы посчитать строки «Y» для каждого месяца, установите F2 в

=SUMPRODUCT(--(MONTH($A$2:$A$99)=MONTH(E2)), --($C$2:$C$99="Y"))

и перетащите / заполните.

PS Диагностика сбоя

Итак, вы уже понимаете проблему: последние восемь дат в столбце A относятся к январю, февралю и марту (2017 года), поэтому они имеют значения MONTH() 1, 2 и 3.  Но когда вы используете MONTH(A2)+3 и т.д., В F5 и т.д., Вы учитываете строки со значениями MONTH() 13, 14 и 15 (которые, конечно, не существуют).  Если у вас есть список месяцев в столбце E , вы должны использовать эти значения при вычислении столбца F

Answer 2

В столбце E в E2, если ваши даты начинаются в A2, напишите:
=VALUE(TEXT(A2,"mm"))
и перетащите его до последнего дня E23
Выбрать все E2:E23 скопировать вставить специальные значения в F2 (оно станет F2:F23) удалить столбец E выбрать E2:E23 (новые месяцы)
Вкладка "Данные", "Удалить дубликаты" (просто из столбца E не расширять выделение)
Вы будете каждый месяц один раз, в F2 напишите свою формулу:
=SUMPRODUCT(--(MONTH($A$2:$A$23)=E2)) (формула массива)
Если вы хотите считать только "Y" для каждого месяца, используйте:
=SUMPRODUCT(--(MONTH($A$2:$A$23)=VALUE(F2))*(--($C$2:$C$23="Y"))) (формула массива)
нажмите Ctrl+Shift+Enter после обеих формул, и вы можете перетащить его

Вы можете просто использовать формулу непосредственно в столбце D, например:
В D2 напишите (просто в D2):
=SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2)))
В D3
=IF(MONTH(A3)<>MONTH(A2),SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A3))),"")
Также массив формул и перетащите его вниз
Когда месяц изменится, он подсчитает
Обновить
Что касается вашего комментария, вы все равно можете использовать свою формулу:
=SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(E2)))
Е2 не А2
Если вы написали в E2 = A2, это означает, что E2 по-прежнему Дата
и вы можете использовать Месяц (E2)
Но вы должны вставить критерии для Года, если у вас нет одного и того же месяца в 2016 и 2017 годах.
Потому что на январь влияют пустые клетки
Используйте следующую формулу, она будет выполнять ту же работу, что и приведенная выше, но также проверяет, является ли ячейка пустой:
=SUM(IF($A$2:$A$23<>"",IF(MONTH($A$2:$A$23)=MONTH(E2),1,0),0))
нажмите Ctrl+Shift+введите формулу массива и перетащите ее
If вернет 1, если месяц (A2:A23)= месяц (E2)
И 0, если пусто или отличается
Сумма добавит 1