Criathradh colbh còmhnard ann an Excel

Mura h-eil thu nad neach-cleachdaidh ùr, feumaidh tu a bhith air mothachadh mar-thà gu bheil 99% den a h-uile càil ann an Excel air a dhealbhadh gus obrachadh le bùird dìreach, far am bi paramadairean no feartan (raointean) a ’dol tro na colbhan, agus tha fiosrachadh mu nithean no tachartasan suidhichte anns na sreathan. Clàran pivot, subtotals, copaidh foirmlean le cliog dùbailte - tha a h-uile dad air a dhealbhadh gu sònraichte airson an cruth dàta seo.

Ach, chan eil riaghailtean ann gun eisgeachdan agus le tricead gu math cunbhalach thathar a’ faighneachd dhomh dè a nì mi ma nochdas clàr le taobh còmhnard semantach, no clàr far a bheil an aon chuideam ann an ciall aig sreathan is colbhan san obair:

Agus ma tha fios aig Excel fhathast mar a nì thu rèiteachadh gu còmhnard (leis an àithne Dàta - Deasaich - Roghainnean - Deasaich colbhan), an uairsin tha an suidheachadh le sìoladh nas miosa - dìreach chan eil innealan togte ann airson colbhan a shìoladh, chan e sreathan ann an Excel. Mar sin, ma tha a leithid de dh’ obair agad mu choinneimh, feumaidh tu a bhith a’ lorg dòighean-obrach de dhiofar ìrean iom-fhillteachd.

Dòigh 1. New FILTER function

Ma tha thu air an dreach ùr de Excel 2021 no fo-sgrìobhadh Excel 365, faodaidh tu brath a ghabhail air an fheart a chaidh a thoirt a-steach às ùr FILTAR (SGILEAN), as urrainn an dàta tùsail a shìoladh chan ann a-mhàin le sreathan, ach cuideachd le colbhan. Gus obrachadh, feumaidh an gnìomh seo sreath aon-mheudach còmhnard còmhnard taiceil, far am bi gach luach (TRUE no FALSE) a’ dearbhadh a bheil sinn a’ sealltainn no, air an làimh eile, a’ falach an ath cholbh sa chlàr.

Nach cuir sinn an loidhne a leanas os cionn a’ bhùird againn agus sgrìobh sinn inbhe gach colbh innte:

• Canaidh sinn gu bheil sinn an-còmhnaidh ag iarraidh a’ chiad agus na colbhan mu dheireadh (cinn-cinn is àireamhan) a thaisbeanadh, agus mar sin dhaibh anns a’ chiad agus an cealla mu dheireadh den raon shuidhich sinn an luach = TRUE.
• Airson na colbhan a tha air fhàgail, bidh susbaint nan ceallan co-fhreagarrach mar fhoirmle a nì sgrùdadh air an t-suidheachadh a dh’ fheumas sinn le bhith a’ cleachdadh ghnìomhan И (AGUS) or OR (NO). Mar eisimpleir, gu bheil an àireamh iomlan anns an raon bho 300 gu 500.

Às deidh sin, chan eil air fhàgail ach an gnìomh a chleachdadh FILTAR gus colbhan a thaghadh far a bheil luach TRUE aig an raon taiceil againn:

San aon dòigh, faodaidh tu colbhan a shìoladh le liosta ainmichte. Anns a 'chùis seo, cuidichidh an gnìomh COUNTIF (COUNTIF), a nì sgrùdadh air an àireamh de thachartasan den ath ainm colbh bho bhann-cinn a’ bhùird air an liosta ceadaichte:

Dòigh 2. Clàr pivot an àite an tè àbhaisteach

An-dràsta, tha Excel air sìoladh còmhnard a chuir a-steach le colbhan a-mhàin ann an clàran pivot, mar sin ma thèid againn air ar clàr tùsail a thionndadh gu clàr pivot, is urrainn dhuinn an gnìomh togte seo a chleachdadh. Gus seo a dhèanamh, feumaidh an clàr stòr againn na cumhaichean a leanas a choileanadh:

• bidh loidhne-cinn aon-loidhne “ceart” agad gun cheallan falamh agus aonaichte - air neo cha obraich e gus clàr pivot a thogail;
• chan eil dùblaidhean anns na bileagan de shreathan is cholbhan - bidh iad “a’ tuiteam sìos ”anns a’ gheàrr-chunntas gu liosta de luachan sònraichte a-mhàin;
• chan eil ann ach àireamhan anns an raon de luachan (aig an eadar-ghearradh de shreathan is cholbhan), oir bidh an clàr pivot gu cinnteach a’ cur an gnìomh gnìomh cruinneachaidh de sheòrsa air choreigin (suim, cuibheasachd, msaa) agus chan obraich seo leis an teacsa

Ma thèid na cumhaichean sin uile a choileanadh, an uairsin gus clàr pivot a thogail a tha coltach ris a ’bhòrd tùsail againn, feumar e (am fear tùsail) a leudachadh bhon crosstab gu bhith na fhear còmhnard (àbhaisteach). Agus is e an dòigh as fhasa seo a dhèanamh leis an tuilleadan Power Query, inneal cruth-atharrachaidh dàta cumhachdach a chaidh a thogail a-steach do Excel bho 2016. 

Is iad sin:

1. Tionndaidhidh sinn am bòrd gu bhith na àithne fiùghantach “smart”. Dachaigh - Cruth mar chlàr (Dachaigh - Cruth mar Clàr).
2. A 'luchdachadh a-steach gu Power Query leis an àithne Dàta - Bho Chlàr / Raon (Dàta - Bhon Chlàr / Raon).
3. Bidh sinn a’ sìoladh na loidhne leis na h-àireamhan gu lèir (bidh a h-àireamhan fhèin aig a’ gheàrr-chunntas).
4. Dèan briogadh deas air ceann a’ chiad cholbh agus tagh Na cuir às do na colbhan eile (Unpivot Other Colbhan). Tha a h-uile colbh nach deach a thaghadh air a thionndadh gu dà - ainm an neach-obrach agus luach a chomharra.
5. A’ sìoladh a’ cholbh leis na h-àireamhan a chaidh a-steach don cholbh buadhan.
6. Bidh sinn a’ togail clàr pivot a rèir a’ bhùird rèidh (àbhaisteach) a thig às leis an àithne Dachaigh - Dùin is Luchdaich - Dùin is luchdaich a-steach… (Dachaigh - Dùin & Luchdaich - Dùin & Luchdaich gu ...).

A-nis faodaidh tu an comas a chleachdadh gus colbhan a tha rim faighinn ann an clàran pivot a shìoladh - na comharran-seic àbhaisteach air beulaibh ainmean agus nithean Criathragan ainm-sgrìobhte (Sìoltachain Label) or Filter a rèir luach (Sìoltachain Luach):

Agus gu dearbh, nuair a dh’ atharraicheas tu an dàta, feumaidh tu ar ceist agus an geàrr-chunntas ùrachadh le ath-ghoirid meur-chlàr Ctrl+Alt+F5 no sgioba Dàta - Ùraich na h-uile (Dàta - Ùraich na h-uile).

Dòigh 3. Macro ann an VBA

Chan eil a h-uile dòigh roimhe, mar a chì thu gu furasta, dìreach a’ sìoladh - cha bhith sinn a ’falach na colbhan anns an liosta thùsail, ach bidh sinn a’ cruthachadh clàr ùr le seata sònraichte de cholbhan bhon fhear thùsail. Ma tha feum air sìoladh (falach) na colbhan anns an dàta stòr, tha feum air dòigh-obrach bunaiteach eadar-dhealaichte, is e sin, macro.

Osbarr gu bheil sinn airson colbhan a shìoladh air a’ chuileag far a bheil ainm a’ mhanaidsear ann am bann-cinn a’ bhùird a’ sàsachadh na masg a tha air a shònrachadh anns a’ chill bhuidhe A4, mar eisimpleir, a’ tòiseachadh leis an litir “A” (is e sin, faigh “Anna” agus “Arthur " Mar thoradh). 

Mar a tha anns a’ chiad dhòigh, bidh sinn an-toiseach a’ cur an gnìomh sreath-raoin taiceil, far am bi ar slat-tomhais air a sgrùdadh le foirmle anns gach cealla agus thèid na luachan loidsigeach TRUE no FALSE a thaisbeanadh airson colbhan faicsinneach agus falaichte, fa leth:

An uairsin cuiridh sinn macro sìmplidh ris. Dèan briogadh deas air tab na duilleige agus tagh àithne Stòr (Còd stòr). Dèan lethbhreac agus pasg air a’ chòd VBA a leanas a-steach don uinneag a tha a’ fosgladh:

Fo-dhuilleag-obrach prìobhaideach_Change(ByVal Target As Range) If Target.Address = "$A$4" An uairsin Airson gach cealla ann an Raon ("D2:O2") Ma tha ceal = Fìor An uairsin cell.EntireColumn.Hidden = False cealla eile.EntireColumn.Hidden = Deireadh fìor Ma tha an ath chill a’ tighinn gu crìch ma tha an deireadh fo  

Tha an loidsig aige mar a leanas:

• San fharsaingeachd, is e neach-làimhseachaidh tachartais a tha seo Duilleag-obrach_Atharraich, ie ruithidh am macro seo gu fèin-ghluasadach air atharrachadh sam bith gu cealla sam bith air an duilleag làithreach.
• Bidh an iomradh air a’ chill atharraichte an-còmhnaidh anns a’ chaochladair targaid.
• An toiseach, bidh sinn a ’dèanamh cinnteach gu bheil an neach-cleachdaidh air an cealla atharrachadh gu dìreach leis an t-slat-tomhais (A4) - tha seo air a dhèanamh leis a’ ghnìomhaiche if.
• An uairsin tòisichidh an cearcall Airson gach… gus ath-aithris thairis air ceallan glasa (D2: O2) le luachan comharran TRUE / FALSE airson gach colbh.
• Ma tha luach na h-ath chealla glas TRUE (fìor), chan eil an colbh falaichte, air neo bidh sinn ga fhalach (seilbh falaichte).

•  Gnìomhan raon fiùghantach bho Office 365: FILTER, SORT, agus UNIC
• Clàr pivot le bann-cinn ioma-loidhne a’ cleachdadh Power Query
• Dè a th’ ann am macros, mar a chruthaicheas agus a chleachdas iad iad