Clár ábhair
Foirmiú na faidhbe
Breathnaímid ar réiteach álainn ar cheann de na cásanna an-chaighdeánacha a bhíonn roimh an chuid is mó d'úsáideoirí Excel luath nó mall: ní mór duit sonraí a bhailiú go tapa agus go huathoibríoch ó líon mór comhad i dtábla deiridh amháin.
Cuir i gcás go bhfuil an fillteán seo a leanas againn, ina bhfuil roinnt comhad le sonraí ó chathracha brainse:
Ní hionann líon na gcomhad agus d'fhéadfadh sé athrú sa todhchaí. Tá bileog ainmnithe ar gach comhad Díolacháináit a bhfuil an tábla sonraí suite:
Tá líon na sraitheanna (orduithe) sna táblaí, ar ndóigh, difriúil, ach tá sraith na gcolún caighdeánach i ngach áit.
Tasc: sonraí a bhailiú ó na comhaid go léir in aon leabhar amháin le nuashonrú uathoibríoch ina dhiaidh sin agus comhaid cathrach nó sraitheanna i dtáblaí á gcur leis nó á scriosadh. De réir an tábla comhdhlúite deiridh, ansin beidh sé indéanta aon tuarascálacha, táblaí pivot, sonraí a shórtáil de réir a chéile, etc. a thógáil. Is é an rud is mó a bheith in ann a bhailiú.
Roghnaimid airm
Chun an réiteach a fháil, ní mór dúinn an leagan is déanaí de Excel 2016 (tá an fheidhmiúlacht riachtanach ionsuite cheana féin) nó leaganacha roimhe seo de Excel 2010-2013 agus an breiseán saor in aisce suiteáilte Ceist Chumhachta ó Microsoft (íoslódáil anseo ). Uirlis thar a bheith solúbtha agus thar a bheith cumhachtach is ea Power Query chun sonraí a luchtú isteach in Excel ón domhan lasmuigh, ansin iad a bhaint agus a phróiseáil. Tacaíonn Power Query le beagnach gach foinse sonraí atá ann cheana féin - ó chomhaid téacs go SQL agus fiú Facebook 🙂
Mura bhfuil Excel 2013 nó 2016 agat, ansin ní féidir leat tuilleadh a léamh (ach kidding). I leaganacha níos sine de Excel, ní féidir tasc den sórt sin a chur i gcrích ach trí mhacra a ríomhchlárú in Visual Basic (rud atá an-deacair do thosaitheoirí) nó trí chóipeáil láimhe monotonous (a thógann tamall fada agus a chruthaíonn earráidí).
Céim 1. Iompórtáil comhad amháin mar shampla
Ar dtús, déanaimis sonraí a allmhairiú ó leabhar oibre amháin mar shampla, ionas go mbeidh Excel “ag iarraidh an smaoineamh”. Chun seo a dhéanamh, cruthaigh leabhar oibre bán nua agus…
- má tá Excel 2016 agat, ansin oscail an cluaisín Dáta agus ansin Cruthaigh Iarratas – Ó Chomhad – Ó Leabhar (Sonraí - Iarratas Nua - Ó Chomhad - Ó Excel)
- má tá Excel 2010-2013 agat agus an breiseán Power Query suiteáilte, ansin oscail an cluaisín Ceist Chumhachta agus roghnaigh ar sé Ó chomhad - Ó leabhar (Ó chomhad - Ó Excel)
Ansin, sa fhuinneog a osclaíonn, téigh go dtí ár bhfillteán le tuarascálacha agus roghnaigh aon cheann de na comhaid cathrach (is cuma cén ceann, toisc go bhfuil siad go léir tipiciúil). Tar éis cúpla soicind, ba chóir go mbeadh an fhuinneog Navigator le feiceáil, áit ar gá duit an leathán a theastaíonn uainn (Díolacháin) a roghnú ar an taobh clé, agus taispeánfar a bhfuil ann ar an taobh deas:
Má chliceálann tú ar an gcnaipe sa chúinne íochtarach ar dheis den fhuinneog seo íoslódáil (Luchtaigh), ansin déanfar an tábla a allmhairiú láithreach chuig an mbileog ina bhunfhoirm. I gcás comhad amháin, tá sé seo go maith, ach ní mór dúinn a luchtú go leor comhaid den sórt sin, mar sin beidh muid ag dul beagán difriúil agus cliceáil ar an gcnaipe Ceartú (Edit). Ina dhiaidh sin, ba cheart an t-eagarthóir ceisteanna Power Query a thaispeáint i bhfuinneog ar leith lenár sonraí ón leabhar:
Is uirlis an-chumhachtach é seo a ligeann duit an tábla a “chríochnú” de réir mar a theastaíonn uainn. Thógfadh fiú cur síos superficial ar a chuid feidhmeanna go léir thart ar céad leathanach, ach, más rud é go hachomair, ag baint úsáide as an bhfuinneog seo is féidir leat:
- scagadh amach sonraí neamhriachtanach, línte folmha, línte le hearráidí
- sonraí a shórtáil de réir colún amháin nó níos mó
- fáil réidh le athrá
- roinn an téacs greamaitheach ar cholúin (de réir teorannóirí, líon na gcarachtar, etc.)
- cuir an téacs in ord (bain spásanna breise, cás ceart, etc.)
- tiontaigh cineálacha sonraí ar gach bealach is féidir (cuir uimhreacha cosúil le téacs ina ngnáthuimhreacha agus vice versa)
- táblaí a thrasuí (rothlú) agus tras-táblaí déthoiseacha a leathnú ina gcinn chomhréidh
- cuir colúin bhreise leis an tábla agus bain úsáid as foirmlí agus feidhmeanna iontu ag baint úsáide as an teanga M atá mar chuid de Power Query.
- ...
Mar shampla, cuirimis colún le hainm téacs na míosa chuig ár tábla, ionas go mbeadh sé níos éasca níos déanaí tuarascálacha tábla pivot a thógáil. Chun seo a dhéanamh, deaschliceáil ar cheannteideal an cholúin sonraíagus roghnaigh an t-ordú Colún dúblach (Colún Dúblach), agus ansin cliceáil ar dheis ar cheanntásc an cholúin dhúbailt atá le feiceáil agus roghnaigh Orduithe Claochlú – Mí – Ainm na Míosa:
Ba chóir colún nua a chur le chéile le hainmneacha téacs na míosa do gach ró. Trí chliceáil faoi dhó ar cheannteideal colúin, is féidir leat é a athainmniú ó Cóipeáil Dáta go dtí níos compordaí Mí, m.sh.
Más rud é i roinnt colúin nár aithin an clár an cineál sonraí i gceart, ansin is féidir leat cabhrú leis trí chliceáil ar an deilbhín formáid ar thaobh na láimhe clé de gach colún:
Is féidir leat línte le hearráidí nó línte folamh a eisiamh, chomh maith le bainisteoirí nó custaiméirí nach bhfuil gá leo, ag baint úsáide as scagaire simplí:
Ina theannta sin, socraítear gach claochlú a dhéantar sa phainéal ceart, áit ar féidir iad a rolladh siar (tras) nó a bparaiméadar (fearas) a athrú:
Éadrom agus galánta, nach ea?
Céim 2. Déanaimis ár n-iarratas a thiontú ina fheidhm
Chun na claochluithe sonraí go léir a rinneadh do gach leabhar iompórtáilte a athdhéanamh ina dhiaidh sin, ní mór dúinn ár n-iarratas cruthaithe a thiontú ina fheidhm, a chuirfear i bhfeidhm ansin, ar a seal, ar ár gcomhaid go léir. Chun é seo a dhéanamh i ndáiríre an-simplí.
Sa Eagarthóir Iarratas, téigh go dtí an táb View agus cliceáil ar an gcnaipe Ardeagarthóir (Amharc - Ardeagarthóir). Ba cheart go n-osclófar fuinneog ina mbeidh ár ngníomhartha go léir roimhe seo scríofa i bhfoirm cód sa teanga M. Tabhair faoi deara le do thoil go bhfuil an cosán chuig an gcomhad a d'allmhairíomar mar shampla códaithe crua sa chód:
Anois déanaimis cúpla coigeartú:
Tá an bhrí atá leo simplí: an chéad líne (cosán comhaid) => déanann sé ár nós imeachta ina fheidhm le hargóint cosán, agus thíos athraíonn muid an cosán seasta go luach an athróg seo.
Gach. Cliceáil ar Críochnaigh agus ba cheart é seo a fheiceáil:
Ná bíodh eagla ort go bhfuil na sonraí imithe - go deimhin, tá gach rud ceart go leor, ba chóir go mbeadh cuma mar seo ar gach rud 🙂 D'éirigh linn ár bhfeidhm saincheaptha a chruthú, áit a ndéantar cuimhne ar an algartam iomlán chun sonraí a allmhairiú agus a phróiseáil gan a bheith ceangailte le comhad ar leith . Tá sé fós le hainm níos sothuigthe a thabhairt dó (mar shampla getData) sa phainéal ar dheis sa réimse Céad ainm agus is féidir leat a bhaint as Baile - Dún agus íoslódáil (Baile - Dún agus Luchtaigh). Tabhair faoi deara le do thoil go bhfuil an cosán chuig an gcomhad a d'allmhairíomar mar shampla códaithe crua sa chód. Fillfidh tú ar an bpríomhfhuinneog Microsoft Excel, ach ba chóir go mbeadh painéal a bhfuil an nasc cruthaithe aige lenár bhfeidhm le feiceáil ar dheis:
Céim 3. Na comhaid go léir a bhailiú
Tá an chuid is deacra ar fad taobh thiar de, fanann an chuid taitneamhach agus éasca. Téigh go dtí an cluaisín Sonraí - Cruthaigh Iarratas - Ó Chomhad - Ó Fillteán (Sonraí - Iarratas Nua - Ó chomhad - Ó fhillteán) nó, má tá Excel 2010-2013 agat, mar an gcéanna leis an táb Ceist Chumhachta. Sa fhuinneog atá le feiceáil, sonraigh an fillteán ina bhfuil ár gcomhad foinse cathrach suite agus cliceáil OK. Ba cheart go n-osclódh an chéad chéim eile fuinneog ina liostófar na comhaid Excel go léir a fhaightear san fhillteán seo (agus a fhofhillteáin) agus sonraí gach ceann acu:
cliceáil Athrú (Edit) agus arís téann muid isteach i bhfuinneog an eagarthóra fiosrúcháin.
Anois ní mór dúinn colún eile a chur lenár tábla lenár bhfeidhm chruthaithe, a “tharraingt” na sonraí ó gach comhad. Chun seo a dhéanamh, téigh go dtí an cluaisín Cuir Colún leis - Colún an Chustaim (Cuir Colún leis - Cuir Colún Saincheaptha leis) agus sa fhuinneog atá le feiceáil, cuir isteach ár bhfeidhm getData, ag sonrú dó mar argóint an chonair iomlán chuig gach comhad:
Tar éis duit cliceáil ar OK ba chóir an colún cruthaithe a chur lenár tábla ar dheis.
Anois scriosfaimid gach colún neamhriachtanach (mar atá in Excel, ag baint úsáide as an gcnaipe luiche ar dheis - Bain), ag fágáil ach an colún breise agus an colún leis an ainm comhaid, mar go mbeidh an t-ainm seo (níos cruinne, an chathair) úsáideach a bheith sna sonraí iomlána do gach ró.
Agus anois an “wow moment” - cliceáil ar an deilbhín lena saigheada féin sa chúinne uachtarach ar dheis den cholún breise lenár bhfeidhm:
… díthiceáil Úsáid ainm bunaidh an cholúin mar réimír (Úsáid bunainm an cholúin mar réimír)agus cliceáil OK. Agus déanfaidh ár bhfeidhm na sonraí ó gach comhad a luchtú agus a phróiseáil, ag leanúint an algartam taifeadta agus ag bailiú gach rud i dtábla coiteann:
Ar mhaithe le háilleacht iomlán, is féidir leat freisin na síntí .xlsx a bhaint den chéad cholún le hainmneacha comhaid - trí “rud ar bith” a chur in ionad caighdeánach (cliceáil ar dheis ar cheanntásc an cholúin - Comhalta ionadaíoch) agus athainmnigh an colún seo go Cathair. Agus freisin ceart an fhormáid sonraí sa cholún leis an dáta.
Gach! Cliceáil ar Baile - Dún agus Luchtaigh (Baile - Dún & Luchtaigh). Déanfar na sonraí go léir a bhailíonn an cheist do na cathracha go léir a uaslódáil chuig an mbileog Excel reatha san fhormáid “Tábla Cliste”:
Ní gá an nasc cruthaithe agus ár bhfeidhm tionóil a shábháil ar leithligh ar bhealach ar bith - déantar iad a shábháil mar aon leis an gcomhad reatha ar an ngnáthbhealach.
Sa todhchaí, má dhéantar aon athruithe ar an bhfillteán (cathracha a chur leis nó a bhaint) nó i gcomhaid (líon na línte a athrú), beidh sé go leor cliceáil ar dheis go díreach ar an tábla nó ar an gceist sa phainéal ar dheis agus roghnaigh an ordú Nuashonraigh & Sábháil (Athnuaigh) – Déanfaidh Power Query “atógáil” na sonraí go léir arís i gceann cúpla soicind.
PS
Leasú. Tar éis nuashonruithe Eanáir 2017, d'fhoghlaim Power Query conas leabhair oibre Excel a bhailiú leis féin, ie ní gá feidhm ar leith a dhéanamh níos mó - tarlaíonn sé go huathoibríoch. Mar sin, níl an dara céim ón alt seo ag teastáil a thuilleadh agus éiríonn an próiseas iomlán níos simplí:
- Roghnaigh Cruthaigh Iarratas - Ó Chomhad - Ó Fillteán - Roghnaigh Fillteán - OK
- Tar éis an chuma ar an liosta comhad, brúigh Athrú
- I bhfuinneog an Eagarthóra Iarratas, leathnaigh an colún Dénártha le saighead dhúbailte agus roghnaigh ainm na bileoige atá le baint as gach comhad
Agus sin uile! Amhrán!
- Athdhearadh an chroschluaisín go ceann comhréidh atá oiriúnach chun táblaí maighdeog a thógáil
- Ag tógáil cairte mboilgeog bheoite i Power View
- Macra chun bileoga ó chomhaid Excel éagsúla a chur le chéile i gceann amháin