Note: The other languages of the website are Google-translated. Back to English

Sut i wirio a yw dyddiad yn ddiwrnodau gwyliau cyhoeddus a chyfrif ac eithrio gwyliau yn Excel?

Er enghraifft, mae gennych chi restr o ddyddiadau ac eisiau gwirio a oes unrhyw wyliau cyhoeddus yn bodoli ar y rhestr, sut allech chi ei wneud yn gyflym? Yn yr erthygl hon, byddaf yn dangos i chi'r ffyrdd i gyfrifo holl ddyddiadau gwyliau cyhoeddus America o fewn blynyddoedd penodol, ac yna gwirio a yw dyddiad yn wyliau cyhoeddus Americanaidd yn Excel yn hawdd.


Rhan 1: Cyfrifwch wyliau cyhoeddus gyda'r flwyddyn benodol yn Excel

Cyn gwirio a yw dyddiad yn wyliau cyhoeddus, rhaid i chi restru'r holl wyliau o fewn blynyddoedd penodol yn Excel. Felly bydd paratoi bwrdd fel y llun sgrin canlynol a ddangosir yn gwneud eich gwaith yn haws.

gwirio doc os yw gwyliau cyhoeddus 1

Mae yna dri math o wyliau cyhoeddus Americanaidd:

(1) Y math cyntaf yw gwyliau cyhoeddus ar ddyddiad penodol, fel Dydd Calan yw ar Ionawr 1af. gallwn gyfrifo'r Dydd Calan yn hawdd gyda'r fformiwla = DYDDIAD (O ystyried y flwyddyn, 1,1);

(2) Yr ail fath yw gwyliau cyhoeddus ar ddiwrnod penodol o'r wythnos, fel Diwrnod yr Arlywydd. Gallwn gyfrifo diwrnod y Llywydd yn hawdd gyda'r fformiwla = DYDDIAD (O ystyried y flwyddyn, 1,1) + 14 + DEWIS (WYTHNOS (DYDDIAD (O ystyried y Flwyddyn, 1,1)), 1,0,6,5,4,3,2);

(3) A'r math olaf yw'r Diwrnod Coffa, gallwn yn hawdd gyfrifo'r Diwrnod Coffa gyda'r fformiwla = DYDDIAD (O ystyried y flwyddyn, 6,1) -WYTH WYTHNOS (DYDDIAD (O ystyried y flwyddyn, 6,6)).

Yma, rwy'n rhestru fformwlâu i gyfrifo'r holl wyliau cyhoeddus yn y tabl canlynol. Rhowch y fformwlâu i mewn i'r gell iawn a gwasgwch Rhowch allwedd fesul un.

gwyliau Cell Fformiwlâu
Dydd Calan C2 = DYDDIAD (C1,1,1)
Diwrnod Martin Luther King Jr C3 = DYDDIAD (C1,1,1) + 14 + DEWIS (WYTHNOS (DYDDIAD (C1,1,1)), 1,0,6,5,4,3,2)
Diwrnod y Llywydd C4 = DYDDIAD (C1,2,1) + 14 + DEWIS (WYTHNOS (DYDDIAD (C1,2,1)), 1,0,6,5,4,3,2)
Memorial Day C5 = DYDDIAD (C1,6,1) -WEEKDAY (DYDDIAD (C1,6,6))
Diwrnod Annibyniaeth C6 = DYDDIAD (C1,7,4)
Diwrnod Labor C7 = DYDDIAD (C1,9,1) + DEWIS (WYTHNOS (DYDDIAD (C1,9,1)), 1,0,6,5,4,3,2)
Columbus Day C8 = DYDDIAD (C1,10,1) + 7 + DEWIS (WYTHNOS (DYDDIAD (C1,10,1)), 1,0,6,5,4,3,2)
Diwrnod yr Hen Filwyr C9 = DYDDIAD (C1,11,11)
Diolchgarwch Diwrnod C10 = DYDDIAD (C1,11,1) + 21 + DEWIS (WYTHNOS (DYDDIAD (C1,11,1)), 4,3,2,1,0,6,5)
Dydd Nadolig C11 = DYDDIAD (C1,12,25)

Nodyn: Yn y fformwlâu yn y tabl uchod, y C1 yw'r gell gyfeirio sy'n lleoli'r flwyddyn benodol. Yn ein enghraifft ni, mae'n golygu Blwyddyn 2015, a gallwch ei newid yn seiliedig ar eich anghenion.

Gyda'r fformwlâu hyn, gallwch chi gyfrifo dyddiadau'r gwyliau cyhoeddus yn hawdd gyda blynyddoedd penodol. Gweler y llun sgrin isod:

gwirio doc os yw gwyliau cyhoeddus 2

Arbedwch ystod fel cofnod AutoText (y fformatau celloedd a'r fformwlâu sy'n weddill) i'w hailddefnyddio yn y dyfodol

Rhaid ei bod yn ddiflas iawn cyfeirio celloedd a defnyddio fformwlâu ar gyfer cyfrifo pob gwyliau. Mae Kutools for Excel yn darparu gweithfan giwt o Testun Auto cyfleustodau i achub yr ystod fel cofnod AutoText, a all aros yn fformatau a fformwlâu celloedd yn yr ystod. Ac yna byddwch chi'n ailddefnyddio'r ystod hon gyda dim ond un clic. Daw'r gwaith yn hawdd trwy ddim ond un clic i fewnosod y tabl hwn a newid y flwyddyn yn y tabl hwn! Nodwedd Llawn Am Ddim Treial 30-diwrnod!
gwyliau awto Americanaidd 1

Kutools ar gyfer Excel - Yn cynnwys mwy na 300 o offer defnyddiol ar gyfer Excel. Treial nodwedd lawn am ddim 30 diwrnod, nid oes angen cerdyn credyd! Get It Now

Rhan 2: Gwiriwch a yw dyddiad yn wyliau cyhoeddus yn Excel

Ar ôl rhestru holl ddyddiadau gwyliau cyhoeddus blynyddoedd penodol, gallwn wirio'n hawdd a yw dyddiad yn wyliau cyhoeddus ai peidio gyda fformwlâu yn Excel. Gan dybio bod gennych chi restr dyddiad fel y dangosir y sgrinlun isod, a byddaf yn cyflwyno'r ffyrdd i'w wneud yn hawdd.

Dewiswch gell wag ar wahân i'r rhestr ddyddiadau, meddai Cell B18, nodwch y fformiwla = OS (COUNTIF ($ C $ 2: $ D $ 11, A18), "Gwyliau", "Na") i mewn iddo, ac yna llusgwch y Llenwi Trin i'r ystod sydd ei hangen arnoch chi. Gweler y screenshot uchod:

Nodiadau:

(1) Yn y fformiwla = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Gwyliau", "Na"), y $ C $ 2: $ D $ 11 yw'r ystod o wyliau cyhoeddus mewn blynyddoedd penodol, a A18 yw'r gell gyda'r dyddiad rydych chi am wirio a yw'n wyliau cyhoeddus, a gallwch eu newid yn seiliedig ar eich anghenion. A bydd y fformiwla hon yn dychwelyd "Gwyliau" os yw'r dyddiad penodol yn wyliau cyhoeddus, ac yn dychwelyd "Na" os nad ydyw.

(2) Gallwch hefyd gymhwyso'r fformiwla arae hon = OS (NEU ($ C $ 2: $ D $ 11 = A18), "Gwyliau", "NA") i wirio a yw'r dyddiad cyfatebol yn wyliau ai peidio.


Rhan 3: Cyfrif diwrnodau rhwng dau ddiwrnod ac eithrio penwythnosau a gwyliau yn Excel

Yn Rhan 1 rydym wedi rhestru'r holl wyliau mewn blwyddyn benodol, a nawr bydd y dull hwn yn eich tywys i gyfrif nifer y diwrnodau ac eithrio'r penwythnosau a'r gwyliau i gyd mewn dyddiad.

Dewiswch gell wag byddwch yn dychwelyd nifer y dyddiau, ac yn nodi'r fformiwla = RHWYDWAITH (E1, E2, B2: B10) i mewn iddo, a gwasgwch y Rhowch allweddol.

Nodyn: Mewn celloedd uchod, E1 yw dyddiad cychwyn yr ystod dyddiad penodedig, E2 yw'r dyddiad gorffen, a B2: B10 yw'r rhestr wyliau rydyn ni'n ei chyfrifo yn Rhan 1.

gwirio doc os yw gwyliau cyhoeddus 6

Nawr fe gewch nifer y diwrnodau ac eithrio penwythnosau a gwyliau yn yr ystod dyddiad penodedig.

Copïwch fformwlâu yn union / yn statig heb newid cyfeiriadau celloedd yn Excel

Kutools ar gyfer Excel Copi Union gall cyfleustodau eich helpu chi i gopïo fformiwlâu lluosog yn hawdd heb newid cyfeiriadau celloedd yn Excel, gan atal cyfeiriadau celloedd cymharol rhag diweddaru'n awtomatig. Nodwedd Llawn Am Ddim Treial 30-diwrnod!
fformiwlâu copi union 3

Kutools ar gyfer Excel - Yn cynnwys mwy na 300 o offer defnyddiol ar gyfer Excel. Treial nodwedd lawn am ddim 30 diwrnod, nid oes angen cerdyn credyd! Get It Now


Yr Offer Cynhyrchedd Swyddfa Gorau

Mae Kutools for Excel yn Datrys y rhan fwyaf o'ch Problemau, ac yn Cynyddu Eich Cynhyrchedd 80%

  • Ailddefnyddio: Mewnosod yn gyflym fformwlâu cymhleth, siartiau ac unrhyw beth rydych chi wedi'i ddefnyddio o'r blaen; Amgryptio Celloedd gyda chyfrinair; Creu Rhestr Bostio ac anfon e-byst ...
  • Bar Fformiwla Gwych (golygu llinellau lluosog o destun a fformiwla yn hawdd); Cynllun Darllen (darllen a golygu nifer fawr o gelloedd yn hawdd); Gludo i'r Ystod Hidlo...
  • Uno Celloedd / Rhesi / Colofnau heb golli Data; Cynnwys Celloedd Hollt; Cyfuno Rhesi / Colofnau Dyblyg... Atal Celloedd Dyblyg; Cymharwch y Meysydd...
  • Dewiswch Dyblyg neu Unigryw Rhesi; Dewiswch Blank Rows (mae pob cell yn wag); Darganfyddiad Gwych a Darganfyddiad Niwlog mewn Llawer o Lyfrau Gwaith; Dewis ar Hap ...
  • Copi Union Celloedd Lluosog heb newid cyfeirnod fformiwla; Auto Creu Cyfeiriadau i Daflenni Lluosog; Mewnosod Bwledi, Blychau Gwirio a mwy ...
  • Testun Detholiad, Ychwanegu Testun, Tynnu yn ôl Swydd, Tynnwch y Gofod; Creu ac Argraffu Subtotals Paging; Trosi rhwng Cynnwys a Sylwadau Celloedd...
  • Hidlo Super (arbed a chymhwyso cynlluniau hidlo i ddalenni eraill); Trefnu Uwch yn ôl mis / wythnos / dydd, amlder a mwy; Hidlo Arbennig gan feiddgar, italig ...
  • Cyfuno Llyfrau Gwaith a Thaflenni Gwaith; Uno Tablau yn seiliedig ar golofnau allweddol; Rhannwch Ddata yn Daflenni Lluosog; Trosi Swp xls, xlsx a PDF...
  • Mwy na 300 o nodweddion pwerus. Yn cefnogi Swyddfa / Excel 2007-2019 a 365. Yn cefnogi pob iaith. Defnydd hawdd yn eich menter neu sefydliad. Nodweddion llawn treial am ddim 30 diwrnod. Gwarant arian yn ôl 60 diwrnod.
tab kte 201905

Mae Tab Office yn Dod â rhyngwyneb Tabbed i'r Swyddfa, a Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
gwaelod officetab
sylwadau (8)
Dim sgôr eto. Byddwch y cyntaf i sgorio!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
ceisio gwneud fformiwla ar gyfer tynnu DIWRNODAU CALENDR a gwyliau. Rwyf wedi gallu darganfod am DYDDIAU GWAITH a GWYLIAU, ond ni allaf ddarganfod sut i wneud diwrnodau a gwyliau CALENDR. dyma beth rydw i'n ei ddefnyddio ar hyn o bryd ar gyfer GWAITH A GWYLIAU. Help! Felly mae angen i hwn fod yn ddiwrnodau CALENDR yn lle GWAITH DYDD.] =DYDD GWAITH(B28-5,1, GWYLIAU)
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Sut allwn i wneud i hyn weithio ar gyfer Gwyliau Ffederal? Sy'n golygu os yw dyddiad gwyliau yn digwydd disgyn ar benwythnos yna byddai'r gwyliau Ffederal naill ai ar ddydd Gwener neu ddydd Llun.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Defnyddiais y fformiwlâu uchod i gyfrifo diwrnod gwirioneddol y gwyliau a gwnes ail golofn ar gyfer gwyliau a Arsylwyd. Gwneuthum y fformiwla hon i gyflawni hyn: =IF((WEEKDAY(B15))=1,B15+1,IF((WEEKDAY(B15)))=7,B15-1,B15)). Mae'r cyfeirnod cell B15 yn cyfeirio at y gwyliau sydd yn y golofn gwyliau gwirioneddol, yn yr achos hwn Dydd Calan. Pan fydd y gwyliau gwirioneddol yn disgyn ar ddydd Sadwrn, bydd y gwyliau a Arsylwyd yn cael eu rhestru fel dydd Gwener ac ar gyfer gwyliau gwirioneddol sy'n disgyn ar ddydd Sul, bydd y gwyliau a arsylwir yn cael eu rhestru fel dydd Llun. Gobeithio bod hyn yn helpu.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Mae hon yn swyddogaeth gywir a fydd yn gweithio ar gyfer Dydd Calan a fyddai'n disgyn ar benwythnos (blynyddoedd 2022 a 2023): =DYDD GWAITH(DYDDIAD(Blwyddyn Galendr,1,1),--(DYDD WYTHNOS(DYDDIAD(Blwyddyn Galendr,1,1) ,2)>5))
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Diolch, rwyf wedi llunio system i benderfynu a yw gwyliau cyhoeddus yn ddiwrnod o'r wythnos, ond mae hyn hefyd yn rhoi dewis arall. Y broblem yw, os bydd rhywun yn ei wneud bob mis, yna mae bylchau rhwng dyddiau lle mae gwyliau cyhoeddus yn digwydd yn ystod dyddiau'r wythnos. Enghraifft fel isod sy'n cymryd rhan o fis Rhagfyr 2017. y ffigurau i'r dyddiadau cywir (Col B) yw'r gwerthoedd DYDD WYTHNOS. Os yw'r dyddiad yn disgyn ar ddydd Sadwrn neu ddydd Sul (gwerth 6 neu 7) yna mae'r Golofn C yn adlewyrchu cell wag ("") os oes gan y gell "1" yn ystod yr wythnos, os yw'n Ŵyl Cyhoeddus yn ystod diwrnod o'r wythnos yna "0" 21 /12/2017 4 1 22/12/2017 5 1 23/12/2017 6 24/12/2017 7 25/12/2017 1 0 26/12/2017 2 0 27/12/2017 3 1 28 12 2017 4/1/29 12 2017 5/1/30 12 2017/6/31 12 Yna gallaf ddidoli â llaw gan ddefnyddio'r dull Hidlo i gael yr 2017 mewn un golofn barhaus o resi heb y bylchau neu'r 7au. Copïwch a gludwch i daflen waith lle gallaf fewnforio'r data i'r siartiau tymheredd. Rwy'n ceisio cael yr adran hidlo yn awtomataidd naill ai trwy fformiwla trwy ddileu'r holl 1's a chelloedd gwag gyda'r canlyniad o symud i fyny o gelloedd sy'n cynnwys yr 0's, neu drwy VBA. Y wobr yn y pen draw fyddai cyfuno’r camau yng Ngholofn A a Cholofn C yn un fformiwla. Y gêm olaf yw llenwi siart tymheredd gydag enw'r diwrnod gwaith ac yn y rhes gyfatebol nesaf diwrnod y mis gofynnol Llun Mawrth Iau Gwe 0 1 7 8 Defnyddio Awst fel enghraifft lle mae'r 10fed yn wyliau cyhoeddus sy'n disgyn yn ystod gwaith diwrnod, gan arwain at ddileu'r data sy'n ymwneud â'r Mers a gweddill y golofn yn symud i fyny un (neu fwy) o leoedd. Yna trawsosod i mewn i'r celloedd uchod. Rwy'n gobeithio fy mod yn egluro'n ddigon clir :-)
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Sut i wneud iddo ddangos enw gwirioneddol y gwyliau yn hytrach na dim ond "gwyliau"?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo KC,
Gallwch newid y testun “gwyliau” i INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)) yn y fformiwla, a bydd y fformiwla gyfan yn cael ei newid i
=IF(COUNTIF($C$2:$C$11,A18),INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)),"No")

Sylwch y dylid gosod y dyddiadau y byddwch yn eu gwirio mewn un golofn.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Sut byddech chi'n cyfrifo 15 diwrnod calendr o ddyddiad penodol gan gynnwys penwythnosau ond heb gynnwys gwyliau gan ddefnyddio rhestr/tabl o ddyddiadau gwyliau?
Nid oes unrhyw sylwadau wedi'u postio yma eto
Gadewch eich sylwadau
Postio fel Gwestai
×
Graddiwch y swydd hon:
0   Cymeriadau
Lleoliadau a Awgrymir