Déan Liosta Droim-Síos Dinimiciúla Cleithiúnach in Excel (Céim ar Chéim)
Anseo sa rang teagaisc seo, tabharfaimid isteach céim ar chéim conas liosta anuas spleách a chruthú a thaispeánann roghanna ag brath ar na luachanna a roghnaíodh sa chéad liosta anuas. I bhfocail eile, déanfaimid liosta bailíochtaithe sonraí Excel bunaithe ar luach liosta eile.
Déan liosta anuas cleithiúnaí dinimiciúil
10s chun liosta anuas cleithiúnach a dhéanamh le huirlis áisiúil
Déan liosta anuas cleithiúnaí dinimiciúil in Excel 2021 nó Excel 365
Roinnt ceisteanna a d’fhéadfá a chur faoin rang teagaisc seo
Saor in aisce íoslódáil an comhad samplach
Físeán: Déan liosta anuas ag brath ar Excel
Déan liosta anuas cleithiúnaí dinimiciúil
Céim 1: Clóscríobh na hIontrálacha Do na Liostaí Anuas
1. Ar dtús, clóscríobh na hiontrálacha is mian leat a thaispeáint sna liostaí anuas, gach liosta i gcolún ar leithligh.
Fógra go mbeidh na míreanna sa chéad cholún (Táirge) mar ainmneacha Excel do na liostaí cleithiúnacha níos déanaí. Mar shampla, anseo is iad Torthaí agus Glasraí na hAinmneacha do cholúin B2:B5 agus C2:C6 ar leithligh.
Féach an pictiúr:
2. Ansin cruthaigh táblaí do gach liosta sonraí.
Roghnaigh raon colún A1: A3, cliceáil Ionsáigh > tábla, ansin sa Cruthaigh Tábla dialóg, cuir tic Tá ceanntásca ar mo thábla bosca seiceála. Cliceáil OK.
Ansin déan an chéim seo arís chun táblaí a chruthú don dá liosta eile.
Is féidir leat na táblaí go léir agus an tagairt do raonta a fheiceáil in Ainm Bainisteoir (brúigh Ctrl + F3 chun é a oscailt).
Céim 2: Cruthaigh Ainmneacha Raon
Sa chéim seo, ní mór duit a chruthú ainmneacha don phríomhliosta agus do gach liosta cleithiúnach.
1. Roghnaigh na míreanna atá le feiceáil sa phríomhliosta (A2: A3).
2. Ansin téigh go dtí an Bosca ainm atá in aice le Barra foirmle.
3. Clóscríobh an t-ainm isteach ann, seo é a ainmniú mar táirge.
4. Brúigh Iontráil eochair a chur i gcrích.
Ansin déan na céimeanna thuas arís chun Ainmneacha a chruthú do gach liosta cleithiúnach ar leithligh.
Ainmnítear anseo an dara colún (B2:B5) mar Torthaí, agus an tríú colún (C2:C6) mar Glasraí.
Is féidir leat gach raon ainmneacha a fheiceáil i Bainisteoir Ainm (brúigh Ctrl + F3 chun é a oscailt).
Céim 3: Cuir an Príomh-Buail Liosta
Ansin, cuir an príomhliosta anuas (Táirge) leis, ar gnáth-liosta anuas bailíochtaithe sonraí é, ní liosta anuas spleách.
1. Ar dtús, cruthaigh tábla.
Roghnaigh cill (E1), agus clóscríobh ceanntásc an chéad cholúin (táirge), agus bog go dtí an chéad chill colún eile (F1), clóscríobh ceanntásc an dara colún (Mír). Cuirfidh tú an liosta anuas leis an tábla seo.
Ansin roghnaigh an dá cheanntásc seo (E1 agus F1), cliceáil Ionsáigh tab, agus roghnaigh tábla sa ghrúpa Táblaí.
Sa Cruthaigh Tábla dialóg, cuir tic Tá ceanntásca ar mo thábla bosca, agus cliceáil OK.
2. Roghnaigh cill E2 a bhfuil tú ag iarraidh an príomh-liosta anuas a chur isteach, cliceáil Dáta cluaisín agus téigh go dtí Uirlisí Sonraí grúpa le cliceáil Bailíochtú Sonraí > Bailíochtú Sonraí.
3. Sa dialóg Bailíochtú Sonraí,
- Roghnaigh liosta sa Ceadaigh alt,
- Cineál thíos foirmle isteach Foinse barra, Is é an Táirge Ainm an phríomhliosta,
- cliceáil OK.
=Product
Is féidir leat a fheiceáil go bhfuil an príomh-liosta anuas cruthaithe.
Céim 4: Cuir Liosta Buail Anuas Cleithiúnach
1. Roghnaigh cill F2 a bhfuil tú ag iarraidh an liosta anuas cleithiúnach a chur leis, cliceáil Dáta tab, agus téigh go dtí Grúpa Uirlisí Sonraí chun cliceáil Bailíochtú Sonraí > Bailíochtú Sonraí.
2. Sa dialóg Bailíochtú Sonraí,
- Roghnaigh liosta sa Ceadaigh alt,
- Clóscríobh an fhoirmle thíos Foinse barra, is é E2 an chill ina bhfuil an príomh-liosta anuas.
- cliceáil OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
Má tá an E2 folamh (ní roghnaíonn tú aon mhír amháin sa phríomhliosta anuas), feicfidh tú teachtaireacht a thagann amach mar atá thíos, cliceáil Is ea chun leanúint ar aghaidh.
Anois tá an liosta anuas cleithiúnach déanta.
Céim 5: Déan an Liosta Buail Anuas a Thástáil.
1. Roghnaigh Torthaí sa phríomhliosta anuas (E2), ansin téigh go dtí an liosta anuas cleithiúnach (F2) cliceáil ar an deilbhín saigheada, féach an bhfuil na míreanna torthaí ar an liosta, ansin roghnaigh mír amháin ón liosta anuas spleách.
2. Brúigh Tab eochair chun sraith nua a thosú sa tábla iontrála sonraí, roghnaigh Glasraí, agus bogadh go dtí an chéad chill eile ar dheis, féach an bhfuil na míreanna glasraí sa liosta, ansin roghnaigh mír amháin ón liosta anuas spleách.
- Mura bhfuil aon mhír roghnaithe sa phríomhliosta anuas (Colún Táirge), ní oibreoidh an liosta anuas cleithiúnach (Colún na Míre).
- Más mian leat a bhfuil sa liosta anuas cleithiúnach a athshocrú nó a ghlanadh tar éis athrú a roghnú, téigh go dtí an t-alt seo le do thoil Conas cill liosta anuas spleách spleách a ghlanadh tar éis duit athrú a roghnú in Excel?, tugann sé isteach cód VBA chun cabhrú leat.
- Más mian leat liosta anuas 3-leibhéal a chruthú, an t-alt seo Conas liosta anuas spleách spleách illeibhéil a chruthú in Excel? cabhróidh sé leat.
10s chun liosta anuas cleithiúnach a dhéanamh le huirlis áisiúil
Kutools le haghaidh Excel cuireann sé uirlis chumhachtach ar fáil chun liosta anuas spleách a dhéanamh níos éasca agus níos tapúla, feicfimid:
Sula leanann tú na céimeanna seo, le do thoil cliceáil chun íoslódáil Kutools le haghaidh Excel le haghaidh trialach saor in aisce 30-lá ar dtús.
Céim 1: Clóscríobh na hIontrálacha Don Liosta Anuas
Ar an gcéad dul síos, socraigh do shonraí mar an seat thíos a thaispeántar:
Céim 2: Uirlis Kutools a chur i bhfeidhm
1. Roghnaigh na sonraí a chruthaigh tú, cliceáil Kutools cluaisín, agus cliceáil Liosta anuas chun an fo-roghchlár a thaispeáint, cliceáil Liosta anuas dinimiciúil.
2. Sa Liosta anuas Cleithiúnach
- seiceáil an Mód B. a mheaitseálann do mhodh sonraí,
- Roghnaigh an raon aschuir, ní mór don cholún raon aschuir a bheith comhionann leis an gcolún raon sonraí,
- cliceáil Ok.
Anois tá an liosta anuas cleithiúnach cruthaithe.
- Tacaíonn Mód B le liosta anuas tríú leibhéal nó níos mó a chruthú:
- Má tá do shonraí socraithe mar a thaispeánann an seat thíos, ní mór duit Mód A a úsáid, ní thacaíonn Mód A ach amháin chun liosta anuas spleách 2-leibhéal a chruthú.
- Tuilleadh sonraí faoi conas Kutools a úsáid chun liosta anuas cleithiúnach a chruthú, tabhair cuairt le do thoil an teagaisc .
Déan liosta anuas cleithiúnaí dinimiciúil in Excel 2021 nó Excel 365
Má tá tú in Excel 2021 nó Excel 365, tá bealach eile ann ar féidir liosta anuas spleách dinimiciúil a chruthú go tapa trí úsáid a bhaint as feidhmeanna nua AONAIR agus Scagaire.
Más rud é go bhfuil do shonraí foinse socraithe mar an seat a thaispeántar, lean na céimeanna seo thíos le do thoil chun an liosta dinimiciúil anuas a chruthú.
Céim 1: Ag baint úsáide as foirmle chun míreanna a fháil don phríomhliosta anuas
Roghnaigh cill, mar shampla, cill G3, agus bain úsáid as feidhmeanna UNIQUE agus FILTER chun na luachanna uathúla a bhaint as an táirge liosta a bheidh mar fhoinse don phríomhliosta anuas, agus brúigh Iontráil eochair.
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Céim 2: Cruthaigh an príomh-liosta anuas
1. Roghnaigh cill ar mhaith leat an príomh-liosta anuas a chur, mar shampla, cill D3, Cliceáil Dáta cluaisín, agus téigh go Uirlisí Sonraí grúpa le cliceáil Bailíochtú Sonraí > Bailíochtú Sonraí.
2. Sa dialóg Bailíochtú Sonraí,
- Roghnaigh liosta sa Ceadaigh alt,
- Cineál thíos foirmle isteach Foinse barra,
- cliceáil OK.
=$G$3#
Anois tá an príomh-liosta anuas cruthaithe.
Céim 3: Ag baint úsáide as foirmle chun míreanna a fháil don liosta anuas spleách
Roghnaigh cill, mar shampla, cill H3, ag baint úsáide as feidhm FILTER chun na míreanna a scagadh bunaithe ar an luach i gcill D3 (an mhír roghnaithe sa phríomhliosta anuas), brúigh Iontráil eochair.
=FILTER(B3:B20, A3:A20=D3)
Céim 4: Cruthaigh an liosta anuas cleithiúnach
1. Roghnaigh cill a chuirfidh an liosta anuas cleithiúnach, mar shampla, cill E3, Cliceáil Dáta cluaisín, agus téigh go Uirlisí Sonraí grúpa le cliceáil Bailíochtú Sonraí > Bailíochtú Sonraí.
2. Sa dialóg Bailíochtú Sonraí,
- Roghnaigh liosta sa Ceadaigh alt,
- Cineál thíos foirmle isteach Foinse barra,
- cliceáil OK.
=$H$3#
Anois cruthaítear an liosta anuas cleithiúnach go rathúil.
Nuair a chuireann tú míreanna nua leis nó nuair a dhéanann tú roinnt athruithe ar A3:A20, déanfar an liosta anuas a nuashonrú go huathoibríoch.
Sórtáil liosta anuas in ord aibítre
Más mian leat na míreanna sa liosta anuas a shocrú in ord aibítre, is féidir leat an fhoirmle thíos a úsáid leis an tábla ullmhúcháin.Don phríomh-anuas (an fhoirmle i gcill G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))
Don chleithiúnaí anuas (an fhoirmle i gcill H3):
=SORT(FILTER(B3:B20, A3:A20=D3))
Anois déantar an dá liosta anuas a shórtáil in ord aibítre A go Z.
Chun tú a shórtáil in ord aibítre Z go A, bain úsáid as an bhfoirmle seo thíos:
Don phríomh-anuas (an fhoirmle i gcill G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)
Don chleithiúnaí anuas (an fhoirmle i gcill H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)
Roinnt ceisteanna a d’fhéadfá a chur:
1. Cén fáth a gcuirtear tábla isteach do gach liosta sonraí?
Cabhróidh ionsá tábla don liosta sonraí leat an liosta anuas a nuashonrú go huathoibríoch bunaithe ar na hathruithe ar an liosta sonraí. Mar shampla, nuair a chuirtear 'Daoine Eile' leis an gcéad liosta sonraí, cuirfear 'Daoine Eile' leis an bpríomhliosta anuas go huathoibríoch.
2. Cén fáth a n-úsáidfí tábla chun liostaí anuas a chur?
Nuair a bhrúnn tú an eochair Tab chun líne nua a chur leis an tábla, cuirfear na liostaí anuas leis go huathoibríoch sa líne nua freisin.
3. Conas a oibríonn feidhm INDIRECT?
INDIRECT úsáidtear feidhm chun teaghrán téacs a thiontú ina thagairt bhailí.
4. Conas a oibríonn an fhoirmle INDIRECT(SUBSTITUTE(E2&F2," ","")?
Gcéad dul síos, SUBSTAITIÚIL cuireann feidhm téacs eile in ionad téacs. Anseo bhain sé na spásanna as na comhainmneacha (E2 agus F2). Ansin INDIRECT tiontaíonn feidhm teaghrán an téacs (an t-ábhar comhcheangailte le E2 agus F2) go tagairt bhailí.
Uirlisí Táirgiúlachta Oifige is Fearr
Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am. Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...
Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca
- Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
- Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
- Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!