Створіть календар у Excel за допомогою однієї формули за допомогою введеної в масив формули.
Подивіться на цю цифру:
Ця формула,, =Cool- однакова формула в кожній клітинці з В5: Н10! Подивіться:
Його було введено в масив після того, як вперше було обрано B5: H10. У цій статті ви побачите, що стоїть за формулою.
До речі, є комірка, яка ще не відображається, який місяць відображатиметься. Тобто, клітинка J1 містить =TODAY(), (і я пишу це в грудні), але якщо ви зміните її на 8.05.2012, ви побачите:
Це травень 2012 року. Добре, однозначно круто! Почніть із самого початку, і просуньтесь до цієї формули в календарі і подивіться, як це працює.
Також припустимо, що сьогодні 8 травня 2012 року.
Спочатку подивіться на цю цифру:
Формула насправді не має сенсу. Було б, якби воно було оточене =SUM, але ви хочете побачити, що стоїть за формулою, тому ви розгорнете її, вибравши її та натиснувши клавішу F9.
Рисунок вище стає малюнком нижче, коли натискається клавіша F9.
Зверніть увагу, що після 3 є крапка з комою - це вказує на новий рядок. Нові стовпці представлені комою. Тож ви збираєтеся цим скористатися.
Кількість тижнів у місяці різниться, але жоден календар не потребує більше шести рядків для представлення будь-якого місяця, і звичайно, всі вони мають сім днів. Подивіться на цю цифру:
Введіть вручну значення від 1 до 42 у B5: H10, і якщо ви введете =B5:H10в клітинку, а потім розгорнете рядок формул, ви побачите, що показано тут:
Зверніть увагу на розміщення крапки з комою - після кожного кратного 7 - із зазначенням нового рядка. Це початок формули, але замість такої довгої ви можете використовувати цю коротшу формулу. Виберіть B5: H10. Тип
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
як формулу, але не натискайте Enter.
Щоб сказати Excel, що це формула масиву, потрібно утримувати Ctrl + Shift лівою рукою. Утримуючи Ctrl + Shift, натисніть клавішу Enter правою рукою. Потім відпустіть Ctrl + Shift. Решту цієї статті цей набір натискань клавіш називатиметься Ctrl + Shift + Enter.
Якщо ви правильно ввели Ctrl + Shift + Enter, фігурні дужки з'являться навколо формули в рядку формул, а цифри від 1 до 42 відображатимуться в B5: H10, як показано тут:
Зверніть увагу, що ви берете числа від 0 до 5, розділені крапками з комою (новий рядок для кожного) і множите їх на 7, фактично даючи це:
Вертикальна орієнтація цих значень, додана до горизонтальної орієнтації значень від 1 до 7, дає ті самі значення, що показані. Розширення цього ідентично тому, що ви мали раніше. Припустимо, тепер ви додасте СЬОГОДНІ до цих чисел?
Примітка: Редагувати існуючу формулу масиву дуже складно. Обережно виконайте такі дії: Виберіть B5: H10. Клацніть на рядку формул, щоб відредагувати існуючу формулу. Введіть + J1, але не натискайте Enter. Щоб прийняти відредаговану формулу, натисніть Ctrl + Shift + Enter.
Результат за 8 травня 2012 року:
Ці номери є серійними номерами (кількість днів з 1/1/1900). Якщо відформатувати їх як короткі дати:
Очевидно, не правильно, але ви туди потрапите. Що робити, якщо ви відформатуєте їх як просто "d" для дня місяця:
Майже схожий на місяць, але жоден місяць не починається з дев’ятого числа. Ах, ось одна проблема. Ви використовували J1, який містить 8.05.2012, і вам дійсно потрібно вказати дату першого числа місяця. Отже, припустимо, ви =DATE(YEAR(J1),MONTH(J1),1)введете J2:
Клітинка J1 містить 8.05.2012, а клітинка J2 змінює це до першого числа місяця того, що було введено в J1. Отже, якщо ви зміните J1 у формулі календаря на J2:
Ближче, але все одно не правильно. Потрібно ще одне коригування, а саме потрібно відняти день тижня першого дня. Тобто, комірка J3 містить =WEEKDAY(J2). 3 представляє вівторок. Отже, якщо відняти J3 з цієї формули, ви отримаєте:
І це насправді правильно на травень 2012 року!
Гаразд, ти дуже близький. Все ще не так: 29 та 30 квітня з’являються у травневому календарі, а також з 1 по 9 червня. Вам потрібно це очистити.
Ви можете дати формулі назву для зручності посилання. Називайте це "Cal" (ще не "круто"). Дивіться цей малюнок:
Тоді ви можете змінити формулу на просто =Cal(все ще Ctrl + Shift + Enter):
Тепер ви можете змінити формулу, щоб прочитати, що якщо результат знаходиться в рядку 5 і результат перевищує 20, скажімо, тоді цей результат повинен бути порожнім. Рядок 5 міститиме перший тиждень будь-якого місяця, тому ви ніколи не повинні бачити значень понад 20 (або будь-яке число понад сім було б помилковим - число на зразок 29, яке ви бачите в комірці B5 на малюнку вище, з попереднього місяця). Таким чином, ви можете використовувати =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Спочатку зверніть увагу, що клітинки B5: D5 порожні. Тепер формула читає "якщо це рядок 5, то якщо ДЕНЬ результату перевищує 20, показувати пустим".
Ви можете продовжувати видаляти низькі цифри в кінці - значення наступного місяця. Ось як це легко зробити.
Відредагуйте формулу та виберіть остаточне посилання на "Cal"
Почніть набирати IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal), щоб замінити остаточну Cal.
Кінцева формула повинна бути
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Натисніть Ctrl + Shift + Enter. Результат повинен бути:
Залишилося зробити дві справи. Ви можете взяти цю формулу і дати їй назву "Круто":
Тоді скористайтеся цим у наведеній тут формулі:
До речі, визначені імена обробляються так, ніби вони введені в масив.
Залишилося лише відформатувати комірки та ввести дні тижня та назву місяця. Отже, ви розширюєте стовпці, збільшуєте висоту рядка, збільшуєте розмір шрифту та вирівнюєте текст:
Потім розмістіть межі навколо комірок:
Об’єднайте та відцентруйте місяць та рік та відформатуйте:
Потім вимкніть лінії сітки і вуаля:
Ця гостьова стаття від Excel MVP Боба Умласа. Це з книги "Excel нестандартно". Щоб переглянути інші теми книги, натисніть тут.
