Contents
Salnameya hilberînê, ango navnîşek dîrokan, ku hemî rojên xebatê yên fermî û betlaneyê li gorî wê têne nîşankirin - ji bo her bikarhênerek Microsoft Excel-ê tiştek bi tevahî hewce ye. Di pratîkê de, hûn nikarin bêyî wê bikin:
- di hesabên hesabkirinê de (meaş, dirêjahiya karûbarê, betlane ...)
- di lojîstîkê de - ji bo destnîşankirina rast a demên radestkirinê, girtina dawiya heftê û betlaneyan (ji bîr bînin bîra xwe ya klasîk "Piştî betlaneyê werin?")
- di rêveberiya projeyê de - ji bo nirxandina rast ya şertan, ji nû ve, rojên kar-ne-xebitî têne hesibandin
- her karanîna fonksiyonên mîna WORKDAY (ROJA KAR) or KARKERÊN PAK (NETWORKROJÊN), ji ber ku ew wekî arguman navnîşek betlaneyan hewce dikin
- dema ku fonksiyonên Dem Intelligence (wek TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, hwd.) di Power Pivot û Power BI de bikar tînin
- … hwd. hwd. – gelek mînak.
Ji bo kesên ku di pergalên ERP yên pargîdanî yên wekî 1C an SAP de dixebitin hêsantir e, ji ber ku salnameya hilberînê di nav wan de hatî çêkirin. Lê li ser bikarhênerên Excel çi ye?
Bê guman, hûn dikarin salnameyek wusa bi destan biparêzin. Lê wê hingê hûn neçar in ku wê salê bi kêmî ve carek nûve bikin (an jî pir caran, wekî di "jolly" 2020 de), bi baldarî têkevin hemî dawiya hefteyê, veguheztin û rojên ne-kar ên ku ji hêla hukûmeta me ve hatine vedîtin. Û paşê vê pêvajoyê her salek din dubare bikin. Acizî.
Ma hûn hinekî dîn bibin û di Excel de salnameya kargehek "berdewamî" çêbikin? Yê ku xwe nûve dike, daneyan ji Înternetê digire û her gav navnîşek rojane ya rojên ne-xebatî çêdike ji bo ku paşê di her hesaban de bikar bîne? Dilkêş?
Ji bo vê yekê, bi rastî, ne zehmet e.
Çavkaniya daneyê
Pirsa sereke ev e ku meriv daneyan ji ku derê bigire? Di lêgerîna çavkaniyek maqûl de, min çend vebijarkan derbas kir:
- Biryarnameyên orîjînal li ser malpera hukûmetê bi formata PDF-ê têne weşandin (li vir, yek ji wan, mînakî) û tavilê winda dibin - agahdariya kêrhatî ji wan nayê derxistin.
- Vebijarkek ceribandî, di nihêrîna pêşîn de xuya bû ku "Portala Daneyên Vekirî ya Federasyonê" ye, ku tê de komek daneya têkildar heye, lê, piştî lêkolînek nêzîk, her tişt xemgîn bû. Malper ji bo îtxalkirina Excel-ê pir nerehet e, piştgirîya teknîkî bersivê nade (xwe veqetandî?), û dane bi xwe ji bo demek dirêj li wir kevnar e - salnameya hilberînê ya 2020-an herî dawî di Mijdara 2019-an de hate nûve kirin (şerm!) û , bê guman, mînakî "koronavirus" û dawiya hefteya 2020-an a "dengdanê" ya me tune.
Ji çavkaniyên fermî bêhêvî bûm, min dest bi kolandina yên nefermî kir. Li ser Înternetê gelek ji wan hene, lê piraniya wan, dîsa, bi tevahî neguncayî ne ji bo têketina Excel-ê û salnameyek hilberînê di forma wêneyên xweşik de didin. Lê ne ji bo me ye ku em wê bi dîwar ve daliqînin, rast?
Û di pêvajoya lêgerînê de, tiştek ecêb bi xeletî hat dîtin - malpera http://xmlcalendar.ru/
Bêyî "xemgîniyên" nehewce, malperek hêsan, sivik û bilez, ku ji bo yek peywirê tûj bûye - ji bo her kesê salnameya hilberînê ya sala xwestî di formata XML de bide. Pirrbidilî!
Heke, ji nişkê ve, hûn nizanin, wê hingê XML formatek nivîsê ye ku naverok bi taybetî hatî nîşankirin.
Tenê di rewşê de, min bi nivîskarên malperê re têkilî danî û wan piştrast kir ku malper 7 sal e heye, daneyên li ser wê bi domdarî têne nûve kirin (ji bo vê yekê şaxek wan li ser github jî heye) û ew ê wê negirin. Û ez qet aciz nabim ku ez û hûn ji bo proje û hesabên me yên li Excel daneyan jê bar dikin. Belaş e. Pir xweş e ku meriv bizane ku hîn jî mirovên bi vî rengî hene! Rûmet!
Dimîne ku meriv van daneyan li Excel-ê bi karanîna pêveka Power Query bar bike (ji bo guhertoyên Excel 2010-2013 ew dikare belaş ji malpera Microsoft-ê were dakêşandin, û di guhertoyên Excel 2016 û nûtir de ew jixwe ji hêla xwerû ve hatî çêkirin ).
Mantiqa çalakiyan dê wiha be:
- Em daxwaz dikin ku ji bo salekê daneyan ji malperê dakêşin
- Daxwaza me veguherîne fonksiyonek
- Em vê fonksiyonê di navnîşa hemî salên berdest de, ji sala 2013-an û heya heya niha bicîh dikin - û em salnameya hilberîna "berdewamî" ya bi nûvekirina otomatîkî digirin. Voila!
Gav 1. Salnameyek ji bo salekê derxînin
Pêşî, salnameya hilberînê ji bo salek salek bar bike, mînakî, ji bo 2020. Ji bo vê yekê, di Excel de, biçin tabê Jimare (an Pirsa Hêzêheke we ew wekî pêvekek cûda saz kir) û hilbijêrin Ji Înternetê (Ji Webê). Di pencereya ku vedibe de, lînka sala têkildar a ku ji malperê hatî kopî kirin bişopînin:
Piştî hilweşînê OK pencereyek pêşdîtinê xuya dike, ku tê de hewce ye ku bişkojkê bikirtînin Daneyên veguherîne (Daneyên veguherîne) or Ji bo guhertina daneyan (Daneyên biguherîne) û em ê bigihîjin pencereya edîtorê pirsê ya Power Query, li wir em ê bi daneyan re xebata xwe bidomînin:
Di cih de hûn dikarin bi ewlehî di panela rastê de jêbirin Daxwaza Parametreyên (Mîhengên pirsê) gav cureyê guherî (Tîpa guherî) Em ne hewceyî wî ne.
Tabloya di stûna betlaneyê de kod û danasîna rojên ne-xebitî dihewîne - hûn dikarin naveroka wê bi du caran "hilweşînê" bi tikandina li ser peyva kesk bibînin. Mêz:
Ji bo vegerê, hûn ê neçar bin ku di panela rastê de hemî gavên ku li paş xuya bûne jêbirin Kanî (Kanî).
Tabloya duyemîn, ku dikare bi rengekî wekhev were gihîştin, tam tiştê ku ji me re hewce dike dihewîne - tarîxên hemî rojên ne-kar:
Ew dimîne ku meriv vê plakê pêvajoy bike, bi taybetî:
1. Tenê tarîxên betlaneyê (ango yên) li gorî stûna duyemîn fîlter bikin Taybetmendî:t
2. Ji bilî ya yekem hemî stûnan jêbirin - bi tikandina rastê li ser serê stûna yekem û bijartina fermanê. Stûnên din jêbirin (Stûnên Din Rake):
3. Bi fermanê ji bo meh û roj stûna yekem bi xal ji hev veqetînin Split Column - By Delimiter tab veguherîna (Veguheztin - Stûna veqetandî - Bi veqetandinê):
4. Û di dawiyê de stûnek hesabkirî bi tarîxên normal biafirînin. Ji bo vê yekê, li ser tabê Zêdekirina stûnek pêl bişkoka bike Stûna Custom (Stûn lê zêde bike - Stûna Xweser) û di pencereya ku xuya dike de formula jêrîn binivîse:
=#date(2020, [#»Taybetmendî:d.1″], [#»Taybetmendî:d.2″])
Li vir, operator #date sê arguman hene: sal, meh û roj, bi rêzê. Piştî tikandina li ser OK em stûna pêwîst bi tarîxên dawiya hefteyê yên normal digirin, û stûnên mayî wekî di gava 2 de jêbirin
Gav 2. Zivirîna daxwaza nav a function
Karê meya paşîn ev e ku em pirsa ku ji bo 2020-an hatî afirandin veguhezînin fonksiyonek gerdûnî ya her sal (hejmara salê dê argumana wê be). Ji bo vê yekê, em jêrîn dikin:
1. Berfirehkirin (heke jixwe nehatibe berfireh kirin) panelê Agahdarî (Pirs) li milê çepê di pencereya Power Query de:
2. Piştî veguheztina daxwazê ji fonksiyonek, şiyana dîtina gavên ku daxwazê pêk tîne û bi hêsanî wan biguhezîne, mixabin, winda dibe. Ji ber vê yekê, maqûl e ku em kopiyek daxwaziya xwe çêbikin û berê xwe bidin wê, û orîjînal di rezervan de bihêlin. Ji bo vê yekê, li ser daxwaza salnameya me di pencereya çepê de rast-klîk bikin û fermana Duplicate hilbijêrin.
Rast-klîkkirina dîsa li ser kopiya salnameyê ya encam (2) dê fermanê hilbijêre Rename (Navê biguherîne) û navek nû binivîsin - bila bibe, mînakî, fxYear:
3. Em bi karanîna fermanê koda çavkaniya pirsê bi zimanê hundurîn Power Query vedikin (ew bi kurtî jê re "M" tê gotin. Edîtorê pêşkeftî tab Axaftin(Nêrîn - Edîtorê Pêşkeftî) û li wir guheztinên piçûk çêkin da ku daxwaza me ji bo her salekê veguherîne fonksiyonek.
Ev bû:
Piştî:
Heke hûn bi hûrguliyan re eleqedar dibin, wê hingê li vir:
- (sal wek hejmar)=> - em eşkere dikin ku fonksiyona me dê yek argumanek hejmarî hebe - guhêrbar sal
- Veqetandina guhêrbar sal di gavê de girêdana malperê Kanî. Ji ber ku Power Query destûrê nade ku hûn jimare û nivîsê bizeliqînin, em bi karanîna fonksiyonê hejmara salê vediguherînin nivîsê. Hejmar.ToText
- Em guhêrbara salê ji bo sala 2020-an di gava paşîn de cîh digirin #"Tişta xwerû lê zêde kir«, ku me ji perçeyan date biderxistin.
Piştî hilweşînê Qedandin daxwaza me dibe fonksiyonek:
Gav 3. salnameyên Import ji bo hemû salan
Tiştê paşîn mayî ev e ku meriv pirsa sereke ya paşîn bike, ku dê daneyên hemî salên berdest bar bike û hemî tarîxên betlaneyê yên wergirtî li yek tabloyê zêde bike. Ji bo vê:
1. Em bi bişkoja rastê ya mişkê ve di panela pirsê ya çepê de li cîhek vala gewr bikirtînin û bi rêzê hilbijêrin Daxwaza nû - Çavkaniyên din - Daxwaza vala (Pirsa Nû - Ji çavkaniyên din - Pirsa vala):
2. Pêdivî ye ku em navnîşek hemî salên ku em ê salnameyan daxwaz bikin çêbikin, ango 2013, 2014… 2020. Ji bo vê yekê, di barika formula ya pirsiyariya vala ya ku xuya dike de, fermanê binivîse:
Awayî:
={HejmarA..HejmarB}
… di Power Query de lîsteyek ji jimareyên bêkêmasî ji A heya B çêdike. Mînakî, îfade
={1..5}
… dê navnîşek 1,2,3,4,5 çêbike.
Welê, ji bo ku bi 2020-an re hişk neyê girêdan, em fonksiyonê bikar tînin DateTime.LocalNow() - analoga fonksiyona Excel TODAY (ÎRO) di Power Query de - û ji hêla fonksiyonê ve, sala heyî jê derxînin Dîrok.Sal.
3. Berhevoka salan a encam, her çend ew pir têr xuya dike, ne tabloyek ji bo Power Query ye, lê tiştek taybetî ye - rêzok (Rêzok). Lê veguhertina wê li tabloyek ne pirsgirêk e: tenê bişkojkê bikirtînin Ser sifrê (Li ser maseyê) li quncikê çepê yê jorîn:
4. Xeta qedandinê! Sepandina fonksiyona ku me berê çêkiriye fxYear ji bo lîsteya encam salan. Ji bo vê yekê, li ser tabê Zêdekirina stûnek bişkojk bike Banga fonksiyona xwerû (Stûn lê zêde bike - Fonksîyona Xweserî Vegerîne) û argumana xwe ya yekane - stûnê danîn Column1 bi salan:
Piştî hilweşînê OK fonksiyona me fxYear îtxal dê her sal bi rêzê bixebite û em ê stûnek werbigirin ku tê de her şanek dê tabloyek bi tarîxên rojên nexebitîne (heke hûn li pişta şaneya li teniştê bitikînin naveroka tabloyê bi zelalî xuya dike. peyva Mêz):
Dimîne ku meriv naveroka tabloyên hêlînkirî bi tikandina li ser îkona bi tîrên ducar di serê stûnê de berfireh bike. Nexşe (kêz Navê stûna orîjînal wekî pêşgir bikar bînin ew dikare were rakirin):
… û piştî tikandina li ser OK em tiştê ku me dixwest distînin - navnîşek hemî betlaneyên ji 2013-an heya sala niha:
Stûna yekem, jixwe nepêwist, dikare were jêbirin, û ji bo ya duyemîn, celebê daneyê saz bike rojek (Rojek) di navnîşa dakêşanê ya di sernavê stûnê de:
Navê pirsê bi xwe dikare tiştek jê watedartir were binav kirin Daxwaz1 û dûv re encaman bi karanîna fermanê di forma tabloyek "aqilmend" a dînamîkî de li ser pelê barkirin bigire û dakêşin tab Xane (Mal - Girtin & Barkirin):
Hûn dikarin di pêşerojê de bi klîkkirina rastê ya li ser maseyê an jî di pencereya rastê de bi fermanê bipirsin salnameya hatî afirandin nûve bikin. Nûvekirin & Save. An jî bişkojkê bikar bînin Hemî nûve bikin tab Jimare (Dîrok - Hemî nûve bike) an jî kurteya klavyeyê Ctrl+alt+F5.
Navê pêger.
Naha hûn careke din ne hewce ne ku dem û raman-şewitandinê li lêgerîn û nûvekirina navnîşa betlaneyan winda bikin - naha we salnameya hilberîna "berdewamî" heye. Di her rewşê de, heya ku nivîskarên malperê http://xmlcalendar.ru/ piştgirî bidin dûndana xwe, ku, ez hêvî dikim, dê ji bo demek pir, pir dirêj be (dîsa ji wan re spas!).
- Rêjeya bitcoin-ê têxe ku hûn ji înternetê bi rêya Query Query-ê ji înternetê derxînin
- Bi karanîna fonksiyona WORKDAY roja karsaziya din bibînin
- Dîtina xaçerêya navberên tarîxê