Scagadh colún cothrománach in Excel

Mura bhfuil tú i d'úsáideoir nua-aimseartha go leor, ansin caithfidh tú a bheith tugtha faoi deara cheana féin go bhfuil 99% de gach rud in Excel deartha chun oibriú le táblaí ingearacha, áit a dtéann paraiméadair nó tréithe (réimsí) trí na colúin, agus go bhfuil faisnéis faoi rudaí nó imeachtaí suite. sna línte . Táblaí maighdeog, fo-iomlán, foirmlí cóipeála le cliceáil dúbailte - tá gach rud in oiriúint go sonrach don fhormáid sonraí seo.

Mar sin féin, níl aon rialacha gan eisceachtaí agus go minic iarrtar orm cad ba cheart dom a dhéanamh má tháinig tábla le treoshuíomh cothrománach séimeantach, nó tábla ina bhfuil an meáchan céanna ó thaobh brí de ag sraitheanna agus colúin, trasna san obair:

Agus má tá a fhios ag Excel fós conas a shórtáil go cothrománach (leis an ordú Sonraí – Sórtáil – Roghanna – Sórtáil colúin), ansin tá an scéal níos measa maidir le scagadh - níl ann ach aon uirlisí ionsuite chun colúin a scagadh, ní sraitheanna in Excel. Mar sin, má tá tú ag tabhairt aghaidh ar a leithéid de thasc, beidh ort teacht suas le réitigh oibre de leibhéil éagsúla castachta.

Modh 1. Feidhm Scagaire Nua

Má tá tú ar an leagan nua de Excel 2021 nó ar shíntiús Excel 365, is féidir leat leas a bhaint as an ngné nua a tugadh isteach Scagaire (Scagaire), ar féidir leis na sonraí foinse a scagadh ní hamháin de réir sraitheanna, ach freisin de réir colúin. Chun oibriú, éilíonn an fheidhm seo sraith aontoiseach cothrománach cúnta eagair, ina gcinneann gach luach (TRUE nó BRÉAGACH) cé acu a thaispeánaimid nó, os a choinne sin, an chéad cholún eile sa tábla a cheilt.

Cuirimis an líne seo a leanas os cionn ár dtábla agus scríobhaimid stádas gach colúin ann:

• Ligean le rá ba mhaith linn i gcónaí a thaispeáint ar an chéad agus deiridh colúin (ceanntásca agus hiomláin), mar sin dóibh sa chéad agus deiridh cealla an eagar shocraigh muid an luach = TRUE.
• Maidir leis na colúin atá fágtha, beidh ábhar na gcealla comhfhreagracha mar fhoirmle a sheiceálann an riocht a theastaíonn uainn ag baint úsáide as feidhmeanna И (AGUS) or OR (NÓ). Mar shampla, go bhfuil an t-iomlán sa raon ó 300 go 500.

Tar éis sin, tá sé fós ach a bhaint as an fheidhm Scagaire chun colúin a roghnú a bhfuil luach TRUE ag ár n-eagar cúnta os a chionn:

Mar an gcéanna, is féidir leat colúin a scagadh de réir liosta ar leith. Sa chás seo, cabhróidh an fheidhm COUNTIF (COUNTIF), a sheiceálann líon na dtarluithe d'ainm an chéad cholúin eile ó cheanntásc an tábla sa liosta ceadaithe:

Modh 2. Tábla pivot in ionad an ghnáthcheann

Faoi láthair, tá scagadh cothrománach ionsuite ag Excel de réir colúin ach amháin i dtáblaí pivot, mar sin má éiríonn linn ár tábla bunaidh a thiontú ina tábla pivot, is féidir linn an fheidhmiúlacht ionsuite seo a úsáid. Chun seo a dhéanamh, ní mór dár tábla foinse na coinníollacha seo a leanas a shásamh:

• bíodh ceannlíne aonlíne “cheart” agat gan cealla fholmha agus chumaisc – nó ní oibreoidh sé chun tábla mhaighdeog a thógáil;
• nach bhfuil dúblaigh iontu i lipéid na rónna agus na gcolún – “titfidh siad” san achoimre i liosta de luachanna uathúla amháin;
• nach bhfuil iontu ach uimhreacha sa raon luachanna (ag crosbhealach na sraitheanna agus na gcolún), mar is cinnte go gcuirfidh an tábla maighdeog feidhm chomhiomlánaithe de chineál éigin i bhfeidhm orthu (suim, meán, etc.) agus ní oibreoidh sé seo leis an téacs

Má chomhlíontar na coinníollacha seo go léir, ansin chun tábla pivot a thógáil atá cosúil lenár tábla bunaidh, beidh gá é (an ceann bunaidh) a leathnú ón crosstab isteach i gceann comhréidh (normalaithe). Agus is é an bealach is éasca é seo a dhéanamh leis an mbreiseán Power Query, uirlis chumhachtach claochlaithe sonraí a tógadh in Excel ó 2016. 

Is iad sin:

1. Déanaimis an tábla a thiontú ina ordú dinimiciúil “cliste”. Baile - Formáid mar tábla (Baile - Formáid mar Tábla).
2. Ag luchtú isteach Power Query leis an ordú Sonraí – Ó Tábla / Raon (Sonraí – Ó Tábla / Raon).
3. Déanaimid an líne a scagadh leis na hiomláin (beidh a iomláin féin ag an achoimre).
4. Cliceáil ar dheis ar an gcéad cheannteideal colún agus roghnaigh Bain na colúin eile amach (Colúin Eile Unpivot). Déantar gach colún neamhroghnaithe a thiontú ina dhá cheann - ainm an fhostaí agus luach a tháscaire.
5. An colún a scagadh leis na hiomláin a chuaigh isteach sa cholún Tréith.
6. Tógann muid tábla maighdeog de réir an tábla cothrom (normalaithe) leis an ordú Baile - Dún agus Luchtaigh - Dún agus Luchtaigh i… (Baile - Dún & Luchtaigh - Dún & Luchtaigh go…).

Anois is féidir leat úsáid a bhaint as an gcumas chun na colúin atá ar fáil sna táblaí mhaighdeogacha a scagadh – na gnáthmharcanna os comhair na n-ainmneacha agus na míreanna Scagairí Síniú (Scagairí Lipéid) or Scagairí de réir luacha (Scagairí Luacha):

Agus ar ndóigh, agus na sonraí á n-athrú agat, beidh ort ár bhfiosrúchán agus an achoimre a nuashonrú le haicearra méarchláir Ctrl+Eile+F5 nó foireann Sonraí - Athnuaigh Uile (Sonraí - Athnuaigh Uile).

Modh 3. Macra i VBA

Níl na modhanna go léir roimhe seo, mar is féidir leat a fheiceáil go héasca, ag scagadh go díreach - ní fholaíonn muid na colúin sa liosta bunaidh, ach foirmimid tábla nua le sraith colún tugtha ón gceann bunaidh. Má tá sé riachtanach na colúin a scagadh (i bhfolach) sna sonraí foinseacha, tá gá le cur chuige bunúsach difriúil, eadhon, macra.

Cuir i gcás gur mhaith linn colúin a scagadh ar an eitilt ina sásaíonn ainm an bhainisteora sa cheanntásc tábla an masc atá sonraithe sa chill buí A4, mar shampla, a thosaíonn leis an litir “A” (is é sin, faigh “Anna” agus “Arthur " mar thoradh). 

Mar atá sa chéad mhodh, cuirimid raon cúnta i bhfeidhm ar dtús, áit a ndéanfar ár gcritéar a sheiceáil le foirmle i ngach cill agus taispeánfar na luachanna loighciúla TRUE nó BRÉAGACH do cholúin infheicthe agus i bhfolach, faoi seach:

Ansin a ligean ar a chur ar macra simplí. Deaschliceáil ar an táb bileog agus roghnaigh ordú Foinse (Cód foinse). Cóipeáil agus greamaigh an cód VBA seo a leanas isteach sa fhuinneog a osclaíonn:

Fo-Bileog Oibre Príobháideach_Athraigh(ByVal Target As Range) If Target.Address = "$A$4" Ansin Do Gach cill i Raon ("D2:O2") Más cill = Fíor Ansin cell.EntireColumn.Hidden = Bréagach cill eile.EntireColumn.Hidden = Deireadh Fíor Má Deireadh an Chéad Chill Eile Más Deireadh Fo  

Seo a leanas a loighic:

• Go ginearálta, is láimhseálaí imeachtaí é seo Bileog Oibre_Athraigh, ie reáchtálfar an macra seo go huathoibríoch ar aon athrú ar aon chill ar an mbileog reatha.
• Beidh an tagairt don chill athraithe san athróg i gcónaí Sprioc.
• Ar dtús, déanaimid seiceáil go bhfuil an t-úsáideoir tar éis an chill a athrú go díreach leis an gcritéar (A4) - déanann an t-oibreoir é seo if.
• Ansin tosaíonn an timthriall Do Gach… a atriall thar chealla liath (D2:O2) le luachanna táscairí TRUE / BRÉAGACH do gach colún.
• Má tá luach na chéad chillín liath eile TRUE (fíor), níl an colún i bhfolach, nó cuirimid i bhfolach é (airí Hidden).

•  Feidhmeanna eagar dinimiciúil ó Oifig 365: Scagaire, SORT, agus UNIC
• Tábla pivot le ceanntásc illíne ag baint úsáide as Power Query
• Cad is macraí ann, conas iad a chruthú agus a úsáid