Sukurkite kalendorių „Excel“ naudodami vieną formulę naudodami masyvo įvestą formulę.
Pažvelkite į šį paveikslą:
Ši formulė =Coolyra ta pati formulė kiekvienoje ląstelėje iš B5: H10! Pažvelk:
Jis buvo įvestas į masyvą, kai pirmą kartą buvo pasirinktas B5: H10. Šiame straipsnyje pamatysite, kas slypi už formulės.
Beje, yra dar nerodomas langelis, kuris yra mėnuo, kurį reikia parodyti. Tai reiškia, kad langelyje J1 yra =TODAY()(ir aš tai rašau gruodžio mėnesį), bet jei pakeisite jį į 2012-05-08, pamatysite:
Tai 2012 m. Gegužė. Gerai, tikrai šaunu! Pradėkite nuo pradžių ir pereikite prie šios formulės kalendoriuje ir pažiūrėkite, kaip ji veikia.
Taip pat tarkime, kad šiandien yra 2012 m. Gegužės 8 d.
Pirmiausia pažiūrėkite į šį paveikslą:
Formulė iš tikrųjų nėra prasminga. Būtų, jei jis būtų apsuptas =SUM, bet norite pamatyti, kas slypi formulėje, todėl ją išplėsite pasirinkdami ir paspausdami klavišą F9.
Viršutinė figūra tampa žemiau esančia, kai paspaudžiamas klavišas F9.
Atkreipkite dėmesį, kad po 3 yra kabliataškis - tai reiškia naują eilutę. Naujus stulpelius žymi kablelis. Taigi jūs ketinate tuo pasinaudoti.
Mėnesio savaičių skaičius skiriasi, tačiau nė vienam kalendoriui nereikia daugiau nei šešių eilučių, kad būtų rodomas bet kuris mėnuo, ir, žinoma, visi jie turi septynias dienas. Pažvelkite į šį paveikslą:
Rankiniu būdu įveskite reikšmes nuo 1 iki 42 į B5: H10, o jei įvesite =B5:H10langelį ir išplėsite formulės juostą, pamatysite, kas čia rodoma:
Atkreipkite dėmesį į kabliataškių vietą po kiekvieno 7 kartotinio, nurodydami naują eilutę. Tai yra formulės pradžia, tačiau vietoj tokios ilgos galite naudoti šią trumpesnę formulę. Pasirinkite B5: H10. Tipas
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
kaip formulę, bet nespauskite „Enter“.
Norėdami pasakyti „Excel“, kad tai yra masyvo formulė, kairėje rankoje turite palaikyti „Ctrl“ + „Shift“. Laikydami „Ctrl“ + „Shift“, dešine ranka paspauskite „Enter“. Tada atleiskite „Ctrl“ + „Shift“. Likusioje šio straipsnio dalyje šis klavišų paspaudimų rinkinys bus vadinamas „Ctrl“ + „Shift“ + „Enter“.
Jei teisingai atlikote „Ctrl“ + „Shift“ + „Enter“, formulės juostoje aplink formulę pasirodys garbanotieji petnešos, o B5: H10 bus rodomi skaičiai nuo 1 iki 42, kaip parodyta čia:
Atkreipkite dėmesį, kad jūs skaičiuojate nuo 0 iki 5, atskirtus kabliataškiais (kiekvienam nauja eilutė), ir padauginkite juos iš 7, suteikdami tai:
Šių verčių vertikali orientacija, pridėta prie vertybių 1–7 horizontalios padėties, duoda tas pačias vertes, kaip parodyta. Tai praplečiama tuo, ką turėjote anksčiau. Tarkime, kad dabar prie šių skaičių pridėsite ŠIANDIEN?
Pastaba: redaguoti esamą masyvo formulę yra labai keblu. Atidžiai atlikite šiuos veiksmus: Pasirinkite B5: H10. Norėdami redaguoti esamą formulę, spustelėkite „Formulės“ juostoje. Įveskite + J1, bet nespauskite Enter. Norėdami priimti redaguotą formulę, paspauskite Ctrl + Shift + Enter.
2012 m. Gegužės 8 d. Rezultatas:
Šie skaičiai yra serijos numeriai (dienų skaičius nuo 1 01/1900). Jei formatuosite jas kaip trumpas datas:
Aišku, kad neteisinga, bet jūs ten pateksite. Ką daryti, jei mėnesio dieną juos suformatuojate tiesiog „d“:
Beveik atrodo kaip mėnuo, bet nė vienas mėnuo neprasideda devinta mėnesio diena. Ak, čia yra viena problema. Naudojote J1, kuriame yra 2012-05-08, ir jums tikrai reikia naudoti mėnesio pirmojo datą. Taigi tarkime, kad =DATE(YEAR(J1),MONTH(J1),1)įdėjote J2:
Langelyje J1 yra 2012-05-08, o langelyje J2 tai pakeičiama į mėnesio pirmąją, nesvarbu, kas įrašyta į J1. Taigi, jei kalendoriaus formulėje pakeisite J1 į J2:
Arčiau, bet vis tiek neteisinga. Reikia dar vieno koregavimo, tai yra, reikia atimti pirmosios dienos savaitės dieną. Tai yra ląstelėje J3 =WEEKDAY(J2). 3 reiškia antradienį. Taigi dabar, jei iš šios formulės atimsite J3, gausite:
Ir tai iš tikrųjų tinka 2012 m. Gegužę!
Gerai, tu esi tikras artimas. Vis dar negerai - balandžio 29 ir 30 dienos rodomos gegužės kalendoriuje, taip pat rodomos birželio 1–9 d. Turite tai išvalyti.
Norėdami lengviau rasti formulę, galite suteikti pavadinimą. Pavadink jį „Cal“ (dar ne „kietas“). Žiūrėkite šį paveikslą:
Tada galite pakeisti formulę tiesiog būti =Cal(vis tiek Ctrl + Shift + Enter):
Dabar galite pakeisti formulę ir skaityti, kad jei rezultatas yra 5 eilutėje, o rezultatas yra daugiau nei 20, tarkime, tada tas rezultatas turėtų būti tuščias. 5 eilutėje bus pirmoji bet kurio mėnesio savaitė, todėl niekada neturėtumėte matyti jokių verčių, viršijančių 20 (arba bet koks skaičius virš septynių būtų neteisingas - toks skaičius kaip 29, kurį matote aukščiau esančio paveikslo B5 langelyje, yra ankstesnio mėnesio). Taigi galite naudoti =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Pirmiausia atkreipkite dėmesį, kad langeliai B5: D5 yra tušti. Formulė dabar skamba „jei tai 5 eilutė, tada, jei rezultato DAY yra daugiau nei 20, rodyti tuščią“.
Pabaigoje galite tęsti mažus skaičius - kito mėnesio vertes. Štai kaip tai padaryti lengvai.
Redaguokite formulę ir pasirinkite galutinę nuorodą į „Cal“
Pradėkite rašyti IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal), kad pakeistumėte galutinę Cal.
Galutinė formulė turėtų būti
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Paspauskite Ctrl + Shift + Enter. Rezultatas turėtų būti:
Liko padaryti du dalykai. Galite naudoti šią formulę ir suteikti jai pavadinimą „Cool“:
Tada naudokite tai čia pateiktoje formulėje:
Beje, apibrėžti vardai traktuojami taip, tarsi jie būtų įvesti į masyvą.
Belieka formatuoti langelius ir įrašyti savaitės dienas bei mėnesio pavadinimą. Taigi praplatinsite stulpelius, padidinsite eilutės aukštį, padidinsite šrifto dydį ir sulygiuosite tekstą:
Tada aplink ląsteles uždėkite kraštus:
Sujunkite ir sutvarkykite mėnesį bei metus ir suformatuokite juos:
Tada išjunkite tinklelius ir voila:
Šis svečio straipsnis yra iš „Excel MVP Bob Umlas“. Tai iš knygos „Excel Outside the Box“. Norėdami pamatyti kitas knygos temas, spustelėkite čia.
