එක්සෙල් හි කර්මාන්තශාලා දින දර්ශනය

නිෂ්පාදන දින දර්ශනය, එනම් දින ලැයිස්තුවක්, සියලුම නිල වැඩ කරන දින සහ නිවාඩු ඒ අනුව සලකුණු කර ඇත - Microsoft Excel හි ඕනෑම පරිශීලකයෙකුට අත්‍යවශ්‍ය දෙයකි. ප්රායෝගිකව, ඔබට එය නොමැතිව කළ නොහැක:

• ගිණුම්කරණ ගණනය කිරීම් වලදී (වැටුප්, සේවා කාලය, නිවාඩු ...)
• ලොජිස්ටික්ස් - සති අන්ත සහ නිවාඩු දින සැලකිල්ලට ගනිමින් බෙදා හැරීමේ වේලාවන් නිවැරදිව තීරණය කිරීම සඳහා (සම්භාව්‍ය "නිවාඩු දිනෙන් පසු එන්න?")
  
• ව්‍යාපෘති කළමනාකරණයේදී - නියමයන් නිවැරදිව තක්සේරු කිරීම සඳහා, නැවතත්, වැඩ නොකරන දින සැලකිල්ලට ගනිමින්
• වැනි කාර්යයන් භාවිතා කිරීම වැඩ දිනය (වැඩදින) or පිරිසිදු කම්කරුවන් (NETWORKDAYS), ඔවුන් තර්කයක් ලෙස නිවාඩු දින ලැයිස්තුවක් අවශ්ය නිසා
• Power Pivot සහ Power BI හි Time Intelligence ශ්‍රිත (TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, ආදිය) භාවිතා කරන විට
  
• ... ආදිය ආදිය - උදාහරණ ගොඩක්.
  

1C හෝ SAP වැනි ආයතනික ERP පද්ධතිවල වැඩ කරන අයට නිෂ්පාදන දින දර්ශනය ගොඩනගා ඇති බැවින් එය පහසු වේ. නමුත් Excel භාවිතා කරන්නන් ගැන කුමක් කිව හැකිද?

ඇත්ත වශයෙන්ම, ඔබට එවැනි දින දර්ශනයක් අතින් තබා ගත හැකිය. නමුත් එවිට ඔබට එය අවම වශයෙන් වසරකට වරක්වත් යාවත්කාලීන කිරීමට සිදුවනු ඇත (හෝ ඊටත් වඩා බොහෝ විට, “ජොලි” 2020 දී මෙන්), අපගේ රජය විසින් සොයා ගන්නා ලද සියලුම සති අන්ත, ස්ථාන මාරුවීම් සහ වැඩ නොකරන දින ප්‍රවේශමෙන් ඇතුළත් කරන්න. ඉන්පසු සෑම ඊළඟ වසරකම මෙම ක්රියා පටිපාටිය නැවත කරන්න. කම්මැලිකම.

ටිකක් පිස්සු හැදිලා Excel හි "සදාකාලික" කර්මාන්තශාලා දින දර්ශනයක් සාදා ගන්නේ කෙසේද? එයම යාවත්කාලීන කරන, අන්තර්ජාලයෙන් දත්ත ලබා ගන්නා සහ ඕනෑම ගණනය කිරීමකදී පසුකාලීන භාවිතය සඳහා සෑම විටම වැඩ නොකරන දින පිළිබඳ යාවත්කාලීන ලැයිස්තුවක් උත්පාදනය කරන එකක්ද? පෙළඹවීමක් ද?

මෙය සිදු කිරීම, ඇත්ත වශයෙන්ම, කිසිසේත් අපහසු නැත.

දත්ත මූලාශ්රය

ප්රධාන ප්රශ්නය වන්නේ දත්ත ලබා ගන්නේ කොහෙන්ද? සුදුසු ප්‍රභවයක් සෙවීමේදී, මම විකල්ප කිහිපයක් හරහා ගියෙමි:

• මුල් නියෝග රජයේ වෙබ් අඩවියේ PDF ආකෘතියෙන් ප්‍රකාශයට පත් කර ඇත (මෙහි, ඒවායින් එකක්, උදාහරණයක් ලෙස) සහ වහාම අතුරුදහන් වේ - ප්‍රයෝජනවත් තොරතුරු ඒවායින් පිටතට ඇද ගත නොහැක.
• බැලූ බැල්මට සිත් ඇදගන්නා විකල්පයක් ලෙස පෙනුනේ “ෆෙඩරේෂනයේ විවෘත දත්ත ද්වාරය”, එහිදී අනුරූප දත්ත කට්ටලයක් ඇත, නමුත්, සමීපව විමසා බැලීමේදී, සියල්ල කණගාටුදායක විය. Excel වෙත ආයාත කිරීම සඳහා වෙබ් අඩවිය අතිශයින් අපහසුය, තාක්ෂණික සහාය ප්‍රතිචාර නොදක්වයි (ස්වයං-හුදකලාද?), සහ දත්ත දිගු කලක් එහි යල් පැන ගොස් ඇත - 2020 සඳහා නිෂ්පාදන දින දර්ශනය අවසන් වරට යාවත්කාලීන කරන ලද්දේ 2019 නොවැම්බර් මාසයේදීය (අපකීර්තිය!) සහ , ඇත්ත වශයෙන්ම, අපගේ “කොරෝනා වයිරසය’ සහ 2020 ‘ඡන්ද ප්‍රකාශ කිරීමේ’ සති අන්තය අඩංගු නොවේ, උදාහරණයක් ලෙස.

නිල මූලාශ්‍ර ගැන කලකිරුණු මම නිල නොවන මූලාශ්‍ර හාරා ගැනීමට පටන් ගතිමි. අන්තර්ජාලයේ ඒවායින් බොහොමයක් ඇත, නමුත් ඒවායින් බොහොමයක් නැවතත්, Excel වෙත ආනයනය කිරීම සඳහා සම්පූර්ණයෙන්ම නුසුදුසු වන අතර අලංකාර පින්තූර ආකාරයෙන් නිෂ්පාදන දින දර්ශනයක් ලබා දෙයි. ඒත් ඒක බිත්තියේ එල්ලන එක අපිට නෙවෙයි නේද?

සෙවීමේ ක්‍රියාවලියේදී අහම්බෙන් අපූරු දෙයක් සොයා ගන්නා ලදී - වෙබ් අඩවිය http://xmlcalendar.ru/

අනවශ්ය "ෆ්රිල්ස්" නොමැතිව, සරල, සැහැල්ලු සහ වේගවත් වෙබ් අඩවියක්, එක් කාර්යයක් සඳහා මුවහත් කර ඇත - XML ​​ආකෘතියෙන් අපේක්ෂිත වසර සඳහා සෑම කෙනෙකුටම නිෂ්පාදන දින දර්ශනයක් ලබා දීම. විශිෂ්ටයි!

හදිසියේම, ඔබ නොදන්නේ නම්, XML යනු විශේෂිත ලෙස සලකුණු කර ඇති අන්තර්ගතය සහිත පෙළ ආකෘතියකි . Excel ඇතුළු බොහෝ නවීන වැඩසටහන් මගින් සැහැල්ලු, පහසු සහ කියවිය හැකිය.

යම් අවස්ථාවක දී, මම වෙබ් අඩවියේ කතුවරුන් සම්බන්ධ කර ගත් අතර, ඔවුන් තහවුරු කළේ වෙබ් අඩවිය වසර 7 ක් පවතින බවත්, එහි දත්ත නිරන්තරයෙන් යාවත්කාලීන වන බවත් (මේ සඳහා ඔවුන්ට github හි ශාඛාවක් පවා ඇති බවත්) සහ ඔවුන් එය වසා දැමීමට යන්නේ නැත. තවද Excel හි අපගේ ඕනෑම ව්‍යාපෘතියක් සහ ගණනය කිරීම් සඳහා ඔබ සහ මම එයින් දත්ත පූරණය කිරීම මට කිසිසේත් ප්‍රශ්නයක් නොවේ. නොමිලේ වේ. මේ වගේ අය තවම ඉන්න බව දැනගන්න ලැබීම සතුටක්! ගෞරවය!

බල විමසුම් ඇඩෝනය භාවිතයෙන් මෙම දත්ත Excel වෙත පැටවීමට ඉතිරිව ඇත (එක්සෙල් 2010-2013 අනුවාද සඳහා එය මයික්‍රොසොෆ්ට් වෙබ් අඩවියෙන් නොමිලයේ බාගත කළ හැකි අතර, Excel 2016 සහ නව අනුවාද වල එය පෙරනිමියෙන් දැනටමත් ගොඩනගා ඇත. )

ක්රියාවන්ගේ තර්කනය පහත පරිදි වනු ඇත:

1. ඕනෑම වසරක් සඳහා වෙබ් අඩවියෙන් දත්ත බාගත කිරීමට අපි ඉල්ලීමක් කරමු
2. අපගේ ඉල්ලීම කාර්යයක් බවට පත් කිරීම
3. අපි මෙම ශ්‍රිතය 2013 සිට වත්මන් වර්ෂය දක්වා පවතින සියලුම වසරවල ලැයිස්තුවට යොදන්නෙමු - සහ ස්වයංක්‍රීය යාවත්කාලීන කිරීම් සහිත “සදාකාලික” නිෂ්පාදන දින දර්ශනයක් අපට ලැබේ. Voila!
   

පියවර 1. වසරක් සඳහා දින දර්ශනයක් ආනයනය කරන්න

පළමුව, ඕනෑම වසරක් සඳහා නිෂ්පාදන දින දර්ශනය පූරණය කරන්න, උදාහරණයක් ලෙස, 2020 සඳහා. මෙය කිරීමට, Excel හි, ටැබයට යන්න දත්ත (හෝ බල විමසුමඔබ එය වෙනම ඇඩෝනයක් ලෙස ස්ථාපනය කර ඇත්නම්) සහ තෝරන්න අන්තර්ජාලයෙන් (වෙබ් එකෙන්). විවෘත වන කවුළුව තුළ, වෙබ් අඩවියෙන් පිටපත් කරන ලද අදාළ වර්ෂයට සබැඳිය අලවන්න:

ක්ලික් කිරීමෙන් පසු OK පෙරදසුන් කවුළුවක් දිස්වේ, එහි ඔබ බොත්තම ක්ලික් කළ යුතුය දත්ත පරිවර්තනය කරන්න (දත්ත පරිවර්තනය කරන්න) or දත්ත වෙනස් කිරීමට (දත්ත සංස්කරණය කරන්න) සහ අපි බල විමසුම් විමසුම් සංස්කාරක කවුළුව වෙත පිවිසෙමු, එහිදී අපි දත්ත සමඟ දිගටම වැඩ කරන්නෙමු:

වහාම ඔබට දකුණු පුවරුවේ ආරක්ෂිතව මකා දැමිය හැක ඉල්ලීම් පරාමිති (විමසුම් සැකසුම්) පියවර වෙනස් කරන ලද වර්ගය (වෙනස් කළ වර්ගය) අපිට එයාව ඕන නෑ.

නිවාඩු තීරුවේ ඇති වගුවේ වැඩ නොකරන දින වල කේත සහ විස්තර අඩංගු වේ - හරිත වචනය මත ක්ලික් කිරීමෙන් ඔබට එහි අන්තර්ගතය දෙවරක් "වැටීමෙන්" දැකිය හැකිය. වගුව:

ආපසු යාමට, ඔබට නැවත දිස් වූ සියලුම පියවර දකුණු පුවරුවේ මකා දැමිය යුතුය මූලාශ්රය (මූලාශ්රය).

ඒ හා සමාන ආකාරයකින් ප්‍රවේශ විය හැකි දෙවන වගුවේ අපට අවශ්‍ය දේ හරියටම අඩංගු වේ - සියලුම වැඩ නොකරන දින වල දිනයන්:

මෙම තහඩුව සැකසීමට ඉතිරිව ඇත, එනම්:

1. දෙවන තීරුවෙන් නිවාඩු දින (එනම් ඒවා) පමණක් පෙරන්න ගුණාංගය:t

2. පළමු තීරුව හැර අනෙකුත් සියලුම තීරු මකන්න - පළමු තීරුවේ ශීර්ෂය මත දකුණු-ක්ලික් කර විධානය තේරීමෙන් අනෙකුත් තීරු මකන්න (වෙනත් තීරු ඉවත් කරන්න):

3. විධානය සමඟ මාසය සහ දවස සඳහා වෙන වෙනම පළමු තීරුව තිතෙන් බෙදන්න තීරුව බෙදීම - පරිසීමකය අනුව ටැබ් පරිවර්තනය (පරිවර්තනය - තීරු බෙදීම - පරිසීමකය අනුව):

4. අවසාන වශයෙන් සාමාන්‍ය දිනයන් සමඟ ගණනය කළ තීරුවක් සාදන්න. මෙය සිදු කිරීම සඳහා, ටැබය මත තීරුවක් එකතු කිරීම බොත්තම ක්ලික් කරන්න අභිරුචි තීරුව (තීරුව එක් කරන්න - අභිරුචි තීරුව) සහ දිස්වන කවුළුවේ පහත සූත්‍රය ඇතුළත් කරන්න:

=#දිනැති(2020, [#»ගුණාංග:d.1″], [#»ගුණාංග:d.2″])

මෙහිදී, #දින ක්‍රියාකරුට තර්ක තුනක් ඇත: පිළිවෙලින් වර්ෂය, මාසය සහ දිනය. ක්ලික් කිරීමෙන් පසු OK අපි සාමාන්‍ය සති අන්ත දිනයන් සමඟ අවශ්‍ය තීරුව ලබා ගන්නා අතර, 2 පියවරේදී මෙන් ඉතිරි තීරු මකන්න

පියවර 2. ඉල්ලීම කාර්යයක් බවට පත් කිරීම

අපගේ මීළඟ කාර්යය වන්නේ 2020 සඳහා නිර්මාණය කරන ලද විමසුම ඕනෑම වසරක් සඳහා විශ්ව ශ්‍රිතයක් බවට පරිවර්තනය කිරීමයි (වසර අංකය එහි තර්කය වනු ඇත). මෙය සිදු කිරීම සඳහා, අපි පහත සඳහන් දේ කරන්නෙමු:

1. පැනලය පුළුල් කිරීම (දැනටමත් පුළුල් කර නොමැති නම්). විමසීම් (විමසුම්) බල විමසුම් කවුළුවේ වම් පසින්:

2. ඉල්ලීම කාර්යයක් බවට පරිවර්තනය කිරීමෙන් පසු, ඉල්ලීම සකස් කරන පියවර දැකීමට සහ ඒවා පහසුවෙන් සංස්කරණය කිරීමට ඇති හැකියාව, අවාසනාවකට මෙන්, අතුරුදහන් වේ. එමනිසා, අපගේ ඉල්ලීමෙහි පිටපතක් සාදා එය සමඟ දැනටමත් විහිළු කිරීම සහ මුල් පිටපත රක්ෂිතයේ තැබීම අර්ථවත් කරයි. මෙය සිදු කිරීම සඳහා, අපගේ දින දර්ශන ඉල්ලීම මත වම් කවුළුවෙහි දකුණු-ක්ලික් කර අනුපිටපත් විධානය තෝරන්න.

ලැබෙන කැලැන්ඩරයේ (2) පිටපත මත නැවත දකුණු-ක්ලික් කිරීමෙන් විධානය තෝරා ගනු ඇත Rename (නැවත නම් කරන්න) සහ නව නමක් ඇතුළත් කරන්න - එය එසේ වීමට ඉඩ දෙන්න, උදාහරණයක් ලෙස, fxYear:

3. අපි විමසුම් මූලාශ්‍ර කේතය අභ්‍යන්තර බල විමසුම් භාෂාවෙන් විවෘත කරමු (එය කෙටියෙන් “M” ලෙස හැඳින්වේ) විධානය භාවිතයෙන් උසස් සංස්කාරක ටැබ් සමාලෝචන(බලන්න - උසස් සංස්කාරක) සහ අපගේ ඉල්ලීම ඕනෑම වසරක් සඳහා කාර්යයක් බවට පත් කිරීමට එහි කුඩා වෙනස්කම් කරන්න.

විය:

පසු:

ඔබ විස්තර ගැන උනන්දුවක් දක්වන්නේ නම්, මෙහි:

• (අංකය ලෙස වසර)=>  - අපගේ ශ්‍රිතයට එක් සංඛ්‍යාත්මක තර්කයක් ඇති බව අපි ප්‍රකාශ කරමු - විචල්‍යයක් වර්ෂය
• විචල්‍යය ඇලවීම වර්ෂය පියවරෙන් පියවර වෙබ් සබැඳියට මූලාශ්රය. බල විමසුම ඔබට අංක සහ පෙළ ඇලවීමට ඉඩ නොදෙන බැවින්, අපි ශ්‍රිතය භාවිතා කර පියාසර කරන විට වසර අංකය පෙළ බවට පරිවර්තනය කරමු අංකය.Text
• අපි අවසාන පියවරේදී වසර විචල්‍යය 2020 සඳහා ආදේශ කරමු #”අභිරුචි වස්තුව එක් කරන ලදී«, අපි කොටස් වලින් දිනය සෑදුවේ එහිදී.
  

ක්ලික් කිරීමෙන් පසු අවසානයි අපගේ ඉල්ලීම කාර්යයක් බවට පත් වේ:

පියවර 3. සියලුම වසර සඳහා දින දර්ශන ආනයනය කරන්න

ඉතිරිව ඇති අවසාන දෙය නම් අවසාන ප්‍රධාන විමසුම සිදු කිරීමයි, එය පවතින සියලුම වසර සඳහා දත්ත උඩුගත කරන අතර ලැබුණු සියලුම නිවාඩු දින එක් වගුවකට එක් කරයි. මේ වෙනුවෙන්:

1. අපි දකුණු මූසික බොත්තම සමඟ අළු හිස් අවකාශයේ වම් විමසුම් පැනලය ක්ලික් කර අනුක්රමිකව තෝරන්න නව ඉල්ලීම - වෙනත් මූලාශ්‍ර - හිස් ඉල්ලීම (නව විමසුම - වෙනත් මූලාශ්‍ර වලින් - හිස් විමසුම):

2. අපි දින දර්ශන ඉල්ලා සිටින සියලුම වසරවල ලැයිස්තුවක් ජනනය කළ යුතුය, එනම් 2013, 2014 ... 2020. මෙය සිදු කිරීම සඳහා, දිස්වන හිස් විමසුමේ සූත්‍ර තීරුවේ, විධානය ඇතුළත් කරන්න:

ව්යුහය:

={NumberA..NumberB}

… බල විමසුමේදී A සිට B දක්වා පූර්ණ සංඛ්‍යා ලැයිස්තුවක් ජනනය කරයි. උදාහරණයක් ලෙස, ප්‍රකාශනය

={1..5}

… 1,2,3,4,5 ලැයිස්තුවක් නිෂ්පාදනය කරනු ඇත.

හොඳයි, 2020 ට දැඩි ලෙස බැඳී නොසිටීම සඳහා, අපි කාර්යය භාවිතා කරමු DateTime.LocalNow() - එක්සෙල් ශ්‍රිතයේ ප්‍රතිසමයක් අද (අද) බල විමසුම තුළ - සහ එයින් උපුටා ගැනීම, අනෙක් අතට, ශ්‍රිතය මගින් වත්මන් වර්ෂය දිනය.වසර.

3. ප්‍රතිඵලයක් ලෙස ලැබෙන වසර සමූහය, එය ප්‍රමාණවත් ලෙස පෙනුනද, බල විමසුම සඳහා වගුවක් නොව, විශේෂ වස්තුවකි - ලැයිස්තුව (ලැයිස්තුව). නමුත් එය වගුවකට පරිවර්තනය කිරීම ගැටළුවක් නොවේ: බොත්තම ක්ලික් කරන්න මේසයට (මේසයට) ඉහළ වම් කෙළවරේ:

4. අවසන් රේඛාව! අප කලින් නිර්මාණය කළ ශ්‍රිතය යෙදීම fxYear ප්රතිඵල වසර ලැයිස්තුවට. මෙය සිදු කිරීම සඳහා, ටැබය මත තීරුවක් එකතු කිරීම බොත්තම ඔබන්න අභිරුචි කාර්යය අමතන්න (තීරුව එක් කරන්න - අභිරුචි කාර්යය කැඳවන්න) සහ එහි එකම තර්කය - තීරුව සකසන්න Column1 වසර ගණනාවක් පුරා:

ක්ලික් කිරීමෙන් පසු OK අපගේ කාර්යය fxYear ආනයනය සෑම වසරකම ක්‍රියාත්මක වන අතර, සෑම කොටුවකම වැඩ නොකරන දිනයන් සහිත වගුවක් අඩංගු තීරුවක් අපට ලැබෙනු ඇත (ඔබ ඊළඟ කොටුවේ පසුබිම මත ක්ලික් කළහොත් වගුවේ අන්තර්ගතය පැහැදිලිව දැකගත හැකිය. වචනය වගුව):

තීරු ශීර්ෂයේ ද්විත්ව ඊතල සහිත අයිකනය මත ක්ලික් කිරීමෙන් කැදැලි වගු වල අන්තර්ගතය පුළුල් කිරීමට එය ඉතිරිව ඇත. දිනයන් (ටික් මුල් තීරු නම උපසර්ගය ලෙස භාවිතා කරන්න එය ඉවත් කළ හැකිය:

… සහ ක්ලික් කිරීමෙන් පසු OK අපට අවශ්‍ය දේ අපට ලැබේ - 2013 සිට වත්මන් වර්ෂය දක්වා සියලුම නිවාඩු ලැයිස්තුවක්:

පළමු, දැනටමත් අනවශ්ය තීරුව, මකා දැමිය හැකි අතර, දෙවනුව, දත්ත වර්ගය සකසන්න දිනය (දිනය) තීරු ශීර්ෂයේ පතන ලැයිස්තුවේ:

විමසුමට වඩා අර්ථවත් යමක් නැවත නම් කළ හැක ඉල්ලීම 1 ඉන්පසු විධානය භාවිතා කරමින් ගතික "ස්මාර්ට්" වගුවක ආකාරයෙන් ප්‍රතිඵල පත්‍රයට උඩුගත කරන්න වසා බාගත කරන්න ටැබ් මුල් පිටුව (නිවස - වසන්න සහ පැටවීම):

ඔබට මේසය මත දකුණු-ක්ලික් කිරීමෙන් හෝ විධානය හරහා දකුණු කවුළුවෙහි විමසීමෙන් අනාගතයේදී නිර්මාණය කළ දින දර්ශනය යාවත්කාලීන කළ හැකිය. යාවත්කාලීන කර සුරකින්න. නැතහොත් බොත්තම භාවිතා කරන්න සියල්ල නැවුම් කරන්න ටැබ් දත්ත (දිනය - සියල්ල නැවුම් කරන්න) හෝ යතුරුපුවරු කෙටිමං Ctrl+alt+F5.

එච්චරයි.

දැන් ඔබට නැවත කිසි දිනක නිවාඩු දින ලැයිස්තුවක් සෙවීමට සහ යාවත්කාලීන කිරීමට කාලය නාස්ති කිරීමට අවශ්‍ය නැත - දැන් ඔබට "සදාකාලික" නිෂ්පාදන දින දර්ශනයක් ඇත. ඕනෑම අවස්ථාවක, http://xmlcalendar.ru/ වෙබ් අඩවියේ කතුවරුන් ඔවුන්ගේ දරුවන්ට සහයෝගය දක්වන තාක් කල්, එය ඉතා දිගු කාලයක් පවතිනු ඇතැයි මම බලාපොරොත්තු වෙමි (නැවතත් ඔවුන්ට ස්තූතියි!).

• Power Query හරහා අන්තර්ජාලයෙන් විශිෂ්ට වීමට bitcoin අනුපාතය ආනයනය කරන්න
• WORKDAY ශ්‍රිතය භාවිතයෙන් ඊළඟ ව්‍යාපාරික දිනය සොයා ගැනීම
  
• දින විරාමවල ඡේදනය සොයා ගැනීම