Динамический ежемесячный календарь Excel

Question

Я хочу создать ежемесячный календарь (1 месяц на листе), в котором может измениться только год (и, следовательно, дата). Мой шаблон календаря уже создан. Все, что я хочу, это добавить формулу, которая изменяет дату в соответствии с днем недели, когда я меняю год. Неделя начинается в воскресенье

Спасибо !

Answer 1

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

---

инструкции

1. Установите A1 на значение года для месяца, который вы хотите представить.
   • На дату первой редакции этого поста это будет "2015".
2. Установите А2 на полное название месяца, который вы хотите представить.
   • Для даты первой редакции этого поста это будет "май".
3. Заполните A3:G3 днями недели, начиная с "Sunday" в A3 и заканчивая "Saturday" в G3.
4. Поместите следующую формулу в A4: =IF(WEEKDAY(DATEVALUE(CONCATENATE($A$2," 1, ",$A$1)))=1,1,"")
   • В зависимости от месяца / года эта формула может возвращать пустое значение здесь. (Это будет в мае 2015 года.) Это нормально.
5. Поместите следующую формулу в B4: =IF(A4="",IF(WEEKDAY(DATEVALUE(CONCATENATE($A$2," 1, ",$A$1)))=COLUMNS($A$4:B4),1,""),A4+1)
   • В зависимости от месяца / года эта формула может возвращать пустое значение здесь. (Это будет в мае 2015 года.) Это нормально.
6. Скопируйте формулу из B4 через ячейки C4:G4. Excel должен автоматически корректировать ссылки на ячейки в формуле по мере необходимости.
   • На этом этапе формула должна иметь значение "1" в одной из ячеек в строке 4, а каждая последующая ячейка должна возвращать приращение, превышающее предыдущую ячейку. Ячейки до "1" должны быть пустыми. В мае 2015 года вы должны увидеть пробелы в формате A4: E4, "1" в F4 (пятница) и "2" в G4 (суббота).
7. Поместите следующую формулу в A5: =IF(ISERROR(DATEVALUE(CONCATENATE($A$2," ",G4+1,", ",$A$1))),"",G4+1)
   • Результат формулы должен быть на единицу выше значения G4.
8. Скопируйте формулу в ячейки A5 вниз ячейки A6: A9. Excel должен автоматически корректировать ссылки на ячейки в формуле по мере необходимости.
   • A6: A9 покажет пробелы. Это нормально.
9. Поместите следующую формулу в B5: =IF(ISERROR(DATEVALUE(CONCATENATE($A$2," ",A5+1,", ",$A$1))),"",A5+1)
   • Результат формулы должен быть на единицу выше, чем значение A5.
10. Скопируйте формулу в B5 через ячейки C5:G5. Excel должен автоматически корректировать ссылки на ячейки в формуле по мере необходимости.
    • Формулы в строке 5 теперь должны вычисляться для набора чисел, постепенно увеличивающегося от столбца A к столбцу G.
11. Выберите B5:G5 и скопируйте эти ячейки вниз через B6:G9. Excel должен автоматически корректировать ссылки на ячейки в формуле по мере необходимости.
    • На этом этапе календарь должен быть соответствующим образом заполнен на данный месяц. Ячейки до первого месяца и после последнего дня месяца должны быть пустыми.
    • В определенные месяцы, например, в январе 2015 года, строка 9 будет полностью пустой. Это нормально.

---

функции

• CONCATENATE объединяет текстовые строки вместе.
• DATEVALUE возвращает значение Excel "серийный номер" для даты, представленной текстовой строкой. Он вернет ошибку, если строка не соответствует известному формату даты или если строка не представляет фактическую дату (например: «32 января 2015 г.» или «29 февраля 2015 г.»).
• WEEKDAY возвращает значение от 1 до 7, представляющее день недели данной даты.
• COLUMNS возвращает количество столбцов, охватываемых указанным массивом ячеек.
• IF позволяет вам возвращать выбранное значение (или результат формулы) в зависимости от того, оценивает ли указанная формула значение ИСТИНА или ЛОЖЬ.
• ISERROR возвращает TRUE, если данная формула приводит к ошибке, или FALSE, если формула не генерирует ошибку.

---

Пояснения к формуле

• A4 собирает информацию из A1 и A2 для создания текстовой строки, представляющей первый день месяца. (например: «1 мая 2015 года».) Затем он проверяет, будет ли будний день этой даты воскресенье. Возвращает 1, если это воскресенье, пустое, если нет.

• B4: G4 проверить предыдущую ячейку, чтобы увидеть, если она пуста. Если нет, возвращает значение на единицу выше, чем в предыдущей ячейке. Если предыдущая ячейка пуста, проверяет, совпадает ли текущий день недели с первым днем данного месяца - возвращает 1, если это так, пустым, если нет.

• A5: A9 добавьте 1 к значению в конце предыдущей строки, затем объедините результат с информацией из A1 и A2, чтобы сформировать строку даты. Проверяет, существует ли дата на самом деле. Возвращает номер дня месяца, если это так, пустым, если нет.

• B5: G9 добавляет 1 к значению из предыдущей ячейки, затем объединяет результат с информацией из A1 и A2, чтобы сформировать строку даты. Проверяет, существует ли дата на самом деле. Возвращает номер дня месяца, если это так, пустым, если нет.

---

Скриншот

Пример вывода за май 2015 года.

Answer 2

Создать New Workbook из шаблона. Calendar sunday строка поиска.

Выберите область дней и нажмите Manage Rules в меню Conditional Formatting .

Добавить New Rule

Добавить правило, равное =TODAY()

Результат:

Answer 3

Введите эти элементы в указанные ячейки:

A1: 2015    B1: First day       C1: =DATEVALUE(A1&"-"&A2&"-01")
A2: 5       B2: Week-Sunday     C2: =C1-WEEKDAY(C1;2)

C4: =TEXT(COLUMNS($A$4:A4);"Ddd")
C5: =IF(TEXT($C$2-1+COLUMNS($A$4:A4)+(ROWS($A$4:A4)-1)*7;"YYYY-MM")=TEXT($C$1;"YYYY-MM");$C$2-1+COLUMNS($A$4:A4)+(ROWS($A$4:A4)-1)*7;"")

Затем скопируйте C4 и C5, чтобы заполнить семь (7) ячеек шириной.
Затем следует копия строки 7, чтобы заполнить в общей сложности шесть (6) строк.
Выделите ячейки в последних 6 строках и нажмите CTRL+1, нажмите «Пользовательский» на левой панели и введите один D для пользовательского формата.

Результат будет выглядеть так:

2015    First day   2015-05-01              
5       Week-Sunday 2015-04-26              

Sun Mon Tue Wed Thu Fri Sat
                    1   2
3   4   5   6   7   8   9
10  11  12  13  14  15  16
17  18  19  20  21  22  23
24  25  26  27  28  29  30
31                      

Это будет работать в Excel, LibreOffice и, скорее всего, в любой другой электронной таблице.

Answer 4

Linux:

Откройте терминал, затем введите:

ncal 2015 и нажмите ENTER (в приглашении $ ).

Вывод будет выглядеть так:

                                  2015
    January           February          March             April             
Su     4 11 18 25     1  8 15 22        1  8 15 22 29        5 12 19 26   
Mo     5 12 19 26     2  9 16 23        2  9 16 23 30        6 13 20 27   
Tu     6 13 20 27     3 10 17 24        3 10 17 24 31        7 14 21 28   
We     7 14 21 28     4 11 18 25        4 11 18 25        1  8 15 22 29   
Th  1  8 15 22 29     5 12 19 26        5 12 19 26        2  9 16 23 30   
Fr  2  9 16 23 30     6 13 20 27        6 13 20 27        3 10 17 24      
Sa  3 10 17 24 31     7 14 21 28        7 14 21 28        4 11 18 25      

    May               June              July              August            
Su     3 10 17 24 31     7 14 21 28        5 12 19 26        2  9 16 23 30
Mo     4 11 18 25     1  8 15 22 29        6 13 20 27        3 10 17 24 31
Tu     5 12 19 26     2  9 16 23 30        7 14 21 28        4 11 18 25   
We     6 13 20 27     3 10 17 24        1  8 15 22 29        5 12 19 26   
Th     7 14 21 28     4 11 18 25        2  9 16 23 30        6 13 20 27   
Fr  1  8 15 22 29     5 12 19 26        3 10 17 24 31        7 14 21 28   
Sa  2  9 16 23 30     6 13 20 27        4 11 18 25        1  8 15 22 29   

    September         October           November          December          
Su     6 13 20 27        4 11 18 25     1  8 15 22 29        6 13 20 27   
Mo     7 14 21 28        5 12 19 26     2  9 16 23 30        7 14 21 28   
Tu  1  8 15 22 29        6 13 20 27     3 10 17 24        1  8 15 22 29   
We  2  9 16 23 30        7 14 21 28     4 11 18 25        2  9 16 23 30   
Th  3 10 17 24        1  8 15 22 29     5 12 19 26        3 10 17 24 31   
Fr  4 11 18 25        2  9 16 23 30     6 13 20 27        4 11 18 25      
Sa  5 12 19 26        3 10 17 24 31     7 14 21 28        5 12 19 26      

... с текущей датой, помеченной номером "обратный блок". Скопируйте и вставьте как текст.
Объем требуемой работы зависит от используемого вами инструмента.

---

редактирование: ввод в командной строке Bash;
$ y=2015; for (( i=1 ; i<13 ; i++)) do cal $i $y ; done | sed -re 's/([^ ]+ 2015|[a-zåäö]{2}|[ 0-9]{2} )/"\1",/g' | sed -re 's/"\,[ ]*$/"/' >calendar-$y.csv

Создает CSV-файл, который можно загрузить в любую электронную таблицу. измените y=2015 чтобы отразить год, для которого вы хотите создать календарь.