Създайте календар в Excel с една формула, като използвате формула, въведена в масив.
Вижте тази цифра:
Тази формула, =Coolе една и съща формула във всяка клетка от B5: H10! Виж:
Той беше въведен в масив, след като за първи път беше избран B5: H10. В тази статия ще видите какво се крие зад формулата.
Между другото, има клетка, която все още не е показана, който е месецът за показване. Тоест клетка J1 съдържа =TODAY()(и пиша това през декември), но ако го промените на 8.5.2012 г., ще видите:
Това е май 2012 г. Добре, определено готино! Започнете отначало и преминете към тази формула в календара и вижте как работи.
Освен това приемете, че днес е 8 май 2012 г.
Първо, вижте тази цифра:
Формулата всъщност няма смисъл. Би било, ако беше заобиколено от =SUM, но искате да видите какво се крие зад формулата, така че ще я разширите, като я изберете и натиснете клавиша F9.
Фигурата по-горе става фигурата отдолу при натискане на клавиша F9.
Забележете, че след трите има двоеточие - това показва нов ред. Новите колони са представени със запетая. Така че ще се възползвате от това.
Броят на седмиците в месеца варира, но нито един календар не се нуждае от повече от шест реда, за да представя всеки месец, и разбира се, всички те имат седем дни. Вижте тази цифра:
Въведете ръчно стойностите от 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.5.2012 г. и наистина трябва да използвате датата на първото от месеца. Така че, да предположим, че сте поставили =DATE(YEAR(J1),MONTH(J1),1)J2:
Клетка J1 съдържа 8.5.2012 г., а клетка J2 променя това към първото от месеца на каквото и да е въведено в J1. Така че, ако промените J1 във формулата на календара на J2:
По-близо, но все пак не е наред. Необходима е още една корекция и това е, че трябва да извадите делничния ден от първия ден. Тоест клетка J3 съдържа =WEEKDAY(J2). 3 представлява вторник. Така че, ако извадите J3 от тази формула, ще получите:
И това всъщност е точно за май 2012 г.!
Добре, наистина сте близки. Това, което все още не е наред, е, че 29 и 30 от април се появяват в календара на май, а от 1 до 9 юни също се появяват. Трябва да ги изчистите.
Можете да дадете име на формулата за по-лесна справка. Наречете го "Cal" (все още не "cool"). Вижте тази фигура:
След това можете да промените формулата, за да бъде просто =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. Резултатът трябва да бъде:
Остават две неща за вършене. Можете да вземете тази формула и да й дадете име "Cool":
След това използвайте това във формулата, показана тук:
Между другото, дефинираните имена се третират така, сякаш са въведени в масив.
Това, което остава да направите, е да форматирате клетките и да въведете Дните от седмицата и името на месеца. Така разширявате колоните, увеличавате височината на реда, увеличавате размера на шрифта и подравнявате текста:
След това поставете граници около клетките:
Обединете и центрирайте месеца и годината и го форматирайте:
След това изключете линиите на мрежата и voila:
Тази статия за гости е от Excel MVP Боб Умлас. Това е от книгата „Excel Outside the Box“. За да видите останалите теми в книгата, щракнете тук.
