Создайте календарь в Excel с одной формулой, используя формулу, введенную в массив.
Взгляните на этот рисунок:
Эта формула, =Coolявляется одной и той же формулой во всех ячейках из B5: H10! Смотреть:
Он был введен в массив после первого выбора B5: H10. В этой статье вы увидите, что стоит за формулой.
Кстати, есть еще не показанная ячейка, в которой отображается месяц. То есть, ячейка J1 содержит =TODAY()(и я пишу это в декабре), но если вы измените его на 5/8/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, в строке формул вокруг формулы появятся фигурные скобки, а в B5: H10 появятся числа от 1 до 42, как показано здесь:
Обратите внимание, что вы берете числа от 0 до 5, разделенные точкой с запятой (новая строка для каждого), и умножаете их на 7, что фактически дает следующее:
Вертикальная ориентация этих значений, добавленная к горизонтальной ориентации значений от 1 до 7, дает те же значения, как показано. Расширение этого идентично тому, что у вас было раньше. Допустим, теперь вы добавите СЕГОДНЯ к этим числам?
Примечание. Редактировать существующую формулу массива очень сложно. Внимательно выполните следующие действия: Выберите B5: H10. Щелкните на панели формул, чтобы изменить существующую формулу. Введите + J1, но не нажимайте Enter. Чтобы принять измененную формулу, нажмите Ctrl + Shift + Enter.
Результат на 8 мая 2012 года:
Это порядковые номера (количество дней с 01.01.1900). Если вы отформатируете их как короткие даты:
Ясно, что неправильно, но вы доберетесь туда. Что, если вы отформатируете их как просто "d" для дня месяца:
Почти похоже на месяц, но ни один месяц не начинается с девятого числа. Ах, вот одна проблема. Вы использовали J1, который содержит 5/8/2012, и вам действительно нужно использовать дату первого числа месяца. Итак, предположим, вы добавили =DATE(YEAR(J1),MONTH(J1),1)J2:
Ячейка J1 содержит 8 мая 2012 г., а ячейка J2 изменяет это значение на первое число месяца, введенного в J1. Итак, если вы измените J1 в формуле календаря на J2:
Ближе, но все же не так. Требуется еще одна корректировка, и вам нужно вычесть рабочий день первого дня. То есть ячейка J3 содержит =WEEKDAY(J2). 3 представляет вторник. Итак, теперь, если вы вычтете J3 из этой формулы, вы получите:
И это действительно право на май 2012 года!
Хорошо, вы очень близки. Что все еще неправильно, так это то, что 29 и 30 апреля появляются в календаре мая, а также появляются с 1 по 9 июня. Вам нужно очистить их.
Вы можете дать формуле имя для более удобного использования. Назовите это «Кэл» (пока не «круто»). Смотрите этот рисунок:
Затем вы можете просто изменить формулу =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. Результат должен быть:
Осталось сделать две вещи. Вы можете взять эту формулу и дать ей имя "Круто":
Затем используйте это в формуле, показанной здесь:
Между прочим, определенные имена обрабатываются так, как будто они введены в массив.
Что осталось сделать, это отформатировать ячейки и ввести дни недели и название месяца. Итак, вы расширяете столбцы, увеличиваете высоту строки, увеличиваете размер шрифта и выравниваете текст:
Затем обведите ячейки рамками:
Объедините и отцентрируйте месяц и год и отформатируйте его:
Затем отключите линии сетки и вуаля:
Это гостевая статья от Боба Умласа, MVP по Excel. Это из книги Excel Outside the Box. Чтобы увидеть другие темы в книге, щелкните здесь.
