Skip to main content

Conas earraí a úsáideadh roimhe seo a cheilt ar an liosta anuas?

In Excel, is féidir leat gnáthliosta anuas a chruthú go tapa, ach an ndearna tú iarracht riamh liosta anuas a dhéanamh nuair a roghnaíonn tú mír amháin, bainfear an ceann a úsáideadh roimhe seo den liosta? Mar shampla, má tá liosta anuas agam le 100 ainm, de réir mar a roghnaíonn mé ainm, ba mhaith liom an t-ainm seo a scriosadh ón liosta anuas, agus anois tá 99 ainm ar an gcnaipe anuas, agus mar sin de go dtí go mbeidh an liosta anuas folamh. B’fhéidir, tá sé seo deacair don chuid is mó dínn, agus anseo, is féidir liom labhairt faoi conas liosta anuas den sórt sin a chruthú in Excel.

Folaigh míreanna a úsáideadh roimhe seo ar an liosta anuas le colúin chúntóirí

mboilgeog cheart gorm saighead Folaigh míreanna a úsáideadh roimhe seo ar an liosta anuas le colúin chúntóirí

Má tá liosta ainmneacha agat i gColún A mar a leanas an pictiúr a thaispeántar, ansin lean na céimeanna thíos ceann ar cheann chun an tasc seo a chríochnú.

doc-hide-use-items-dropdown-list-1

1. Seachas do liosta ainmneacha, iontráil an fhoirmle seo le do thoil = IF (COUNTIF ($ F $ 1: $ F $ 11, A1)> = 1, "", ROW ()) isteach i gcill B1, féach an pictiúr:

doc-hide-use-items-dropdown-list-1

nótaí: San fhoirmle thuas, F1: F11an bhfuil an raon cille ar mhaith leat an liosta anuas a chur, agus A1 an bhfuil do chill ainm.

2. Ansin tarraing an láimhseáil líonta chuig an raon ina bhfuil an fhoirmle seo, agus gheobhaidh tú an toradh seo a leanas:

doc-hide-use-items-dropdown-list-1

3. Agus lean ar aghaidh le foirmle a chur i bhfeidhm i gColún C, clóscríobh an fhoirmle seo le do thoil: =IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$11),"",INDEX(A:A,SMALL(B$1:B$11,1+ROW(A1)-ROW(A$1)))) isteach i gcill C1, féach an pictiúr:

doc-hide-use-items-dropdown-list-1

4. Ansin líon isteach an fhoirmle seo go dtí an raon atá uait, féach an pictiúr:

doc-hide-use-items-dropdown-list-1

5. Anois ní mór duit ainm raon a shainiú do na hainmneacha seo i gColún C, roghnaigh C1: C11 (an fhoirmle a chuireann tú i bhfeidhm i gcéim 4), agus ansin cliceáil Foirmlí > Sainmhínigh Ainm, féach ar an scáileán:

doc-hide-use-items-dropdown-list-1

6. Sa an Ainm Nua bosca dialóige, clóscríobh ainm sa bhosca téacs Ainm, agus ansin cuir isteach an fhoirmle seo =OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$11)-COUNTBLANK(Sheet2!$C$1:$C$11),1) isteach sa Tagraíonn réimse, féach an scáileán:

doc-hide-use-items-dropdown-list-1

nótaí: San fhoirmle thuas, is é C1: C11 an raon colún cúntóra atá cruthaithe agat i gCéim 3, agus is é Bileog 2 an bhileog reatha atá á úsáid agat.

7. Tar éis duit na socruithe a chríochnú, ansin is féidir leat liosta anuas a chruthú, roghnaigh cill F1: F11 áit ar mhaith leat an liosta anuas a chur, ansin cliceáil Dáta > Bailíochtú Sonraí > Bailíochtú Sonraí, féach ar an scáileán:

doc-hide-use-items-dropdown-list-1

8. Sa an Bailíochtú Sonraí dialóg, cliceáil Socruithe cluaisín, ansin roghnaigh liosta ó na Ceadaigh liosta anuas, agus ansin faoi Foinse alt, iontráil an fhoirmle seo: ainmainm, (ainmainm an t-ainm raon a chruthaítear duit i gCéim 6), féach an pictiúr:

doc-hide-use-items-dropdown-list-1

9. Agus ansin cliceáil OK cnaipe chun an dialóg seo a dhúnadh, anois, cruthaíodh an liosta anuas sa raon roghnaithe, agus tar éis ainm amháin a roghnú ón liosta anuas, bainfear an t-ainm úsáidte seo den liosta agus ní thaispeánann sé ach na hainmneacha nár úsáideadh , féach ar an scáileán:

doc-hide-use-items-dropdown-list-1

Leid: Ní féidir leat na colúin chúntóra a dhéanann tú sna céimeanna thuas a bhaint, má bhainfidh tú iad, beidh an liosta anuas neamhbhailí.

Earraí gaolmhara:

Conas an liosta anuas a chur isteach in Excel?

Conas liosta anuas dinimiciúil a chruthú go tapa in Excel?

Conas liosta anuas a chruthú le híomhánna in Excel?

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

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 ...

Tuairisc

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á!
Comments (19)
No ratings yet. Be the first to rate!
Juanbeing empty
Juanbeing empty
This comment was minimized by the moderator on the site
Does any one know how to get the formula to reset itself once all the items on the list are selected?
e.g.
List 1, 2, 3, 4 - Then after selecting 1, List 2, 3, 4 - Then after Selecting 2, List 3, 4 - Then after selecting 3, List 4 and last, after selecting 4, list empty. How can this be reset so that after you select 4, all the items in the list reset to original list? So, after selecting 4, instead of List being empty, for list to go back to List 1, 2, 3, 4.

Thank you
Reply
0
0
KL
KL
This comment was minimized by the moderator on the site
This worked like a charm ... expect I need to have this "repeat" in three places on the same spreadsheet. I can't make it function properly, the second time. The items from the first drop down list are eliminated but when an entry is selected in the second location, it isn't removed from the list. Any idea on how to may this function properly?
KL
Reply
0
0
Kelly
Kelly
This comment was minimized by the moderator on the site
Can this formula be used successfully across multiple columns on one spreadsheet? I'm trying to have three columns where someone can select up to three items from a drop down list, i.e. item 1 in column 3, items2 in column 4, and item 3 in column 5. I can get the formula to work at the first occurrence, however, the second time I try to copy the formula, the selection(s) don't disappear from the drop down list, as they do in the column of the first occurrence.
Reply
0
0
Pearson
Pearson
This comment was minimized by the moderator on the site
Bravo! Fantastic solution! I have my workbook set up such that the items in my list are in a separate worksheet that I'm index-matching to through my number and helper columns leaving only them two on my calculation page. Again, very clean solution, Programmer!
Reply
0
0
Stacey
Stacey
This comment was minimized by the moderator on the site
Step 6 isn't working for me. I keep getting an error message saying the syntax of this name isn't correct... Can anyone help?
Reply
0
0
John
John
This comment was minimized by the moderator on the site
Is there a way to have only some of the options get removed when selected and others be permanent?
Reply
0
0
Keith Race
Keith Race
This comment was minimized by the moderator on the site
How do I get this activity to work if I transpose from Row to Column
Reply
0
0
SBell
SBell
This comment was minimized by the moderator on the site
I’ve entered all the formulas correctly, but the only name showing up is the first one on the list. What am I doing wrong??
Reply
Report
0
0
Stefan
Stefan
This comment was minimized by the moderator on the site
Works great, however, if you have two people on the list with the same name e.g. John Smith it removes both incidents of 'John Smith' from the list when you select one of them.


Is there a way to amend this so that you have have multiple versions of the one name without them all being removed?


Thanks.
Reply
0
0
Derric
Derric
This comment was minimized by the moderator on the site
How would you change this formula for use of data validation list across multiple rows instead of a single column. Is that possible? Thanks
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations