Skip to main content

Conas luachanna i liosta amháin a eisiamh ó liosta eile in Excel?

Má cheaptar go bhfuil dhá liosta sonraí agat mar an scáileán ar chlé a thaispeántar. Anois ní mór duit ainmneacha i gcolún A a bhaint nó a eisiamh má tá an t-ainm atá ann i gcolún D. Conas é a bhaint amach? Agus cad a tharlaíonn má aimsíonn an dá liosta in dhá bhileog oibre éagsúla? Soláthraíonn an t-alt seo dhá mhodh duit.

Luachanna a eisiamh i liosta amháin ó liosta eile le foirmle

Déan luachanna i liosta amháin a eisiamh go tapa ó liosta eile le Kutools for Excel

Luachanna a eisiamh i liosta amháin ó liosta eile le foirmle

Féadfaidh tú na foirmlí seo a leanas a chur i bhfeidhm chun é a bhaint amach. Déan mar a leanas le do thoil.

1. Roghnaigh cill bhán atá cóngarach don chéad chill den liosta a theastaíonn uait a bhaint, ansin cuir isteach an fhoirmle = COUNTIF ($ D $ 2: $ D $ 6, A2) isteach sa Bharra Foirmle, agus ansin brúigh an Iontráil eochair. Féach an pictiúr:

nótaí: San fhoirmle, is é $ D $ 2: $ D $ 6 an liosta a scriosfaidh tú luachanna bunaithe air, is é A2 an chéad chill den liosta atá tú ag dul a bhaint. Athraigh iad de réir mar is gá duit.

2. Coinnigh ort an chill toradh a roghnú, tarraing an Láimhseáil Líon isteach go dtí go sroicheann sé an chill dheireanach den liosta. Féach an pictiúr:

3. Coinnigh ort ag roghnú liosta na dtorthaí, ansin cliceáil Dáta > Sórtáil A go Z..

Ansin feiceann tú go bhfuil an liosta curtha in eagar mar atá thíos an pictiúr a thaispeántar.

4. Anois roghnaigh na sraitheanna iomlána ainmneacha le toradh 1, cliceáil ar dheis ar an raon roghnaithe agus cliceáil Scrios chun iad a bhaint.

Anois tá luachanna eisiata agat i liosta amháin bunaithe ar liosta eile.

nótaí: Más é an “liosta le baint” atá suite i raon A2: A6 de bhileog oibre eile Bileog2 den sórt sin, cuir an fhoirmle seo i bhfeidhm = MÁ (ISERROR (VLOOKUP (A2, Sheet2! $ A $ 2: $ A $ 6,1, BRÉAGACH)), "Coinnigh", "Scrios") a fháil ar fad Coinnigh agus Scrios torthaí, téigh ar aghaidh chun liosta na dtorthaí ó Ato Z a shórtáil, agus ansin gach sraith ainm a scriosadh de láimh tá an toradh Scrios sa bhileog oibre reatha.

Déan luachanna i liosta amháin a eisiamh go tapa ó liosta eile le Kutools for Excel

Molfaidh an chuid seo an Roghnaigh Cealla Mar an gcéanna & Éagsúla fóntais de Kutools le haghaidh Excel chun an fhadhb seo a réiteach. Déan mar a leanas le do thoil.

Roimh iarratas a dhéanamh Kutools le haghaidh Excel, Le do thoil é a íoslódáil agus a shuiteáil ar dtús.

1. cliceáil Kutools > Roghnaigh > Roghnaigh Cealla Mar an gcéanna & Éagsúla. Féach an pictiúr:

2. Sa Roghnaigh Cealla Mar an gcéanna & Éagsúla bosca dialóige, ní mór duit:

  • 2.1 Roghnaigh an liosta a mbainfidh tú luachanna as sa Faigh luachanna i bosca;
  • 2.2 Roghnaigh an liosta scriosfaidh tú luachanna bunaithe ar an De réir bosca;
  • 2.3 roghnaigh an Cill aonair rogha sa Bunaithe ar alt;
  • 2.4 Cliceáil ar an OK cnaipe. Féach an pictiúr:

3. Ansin tagann bosca dialóige suas chun a insint duit cé mhéad cealla a roghnaíodh, cliceáil ar an OK cnaipe.

4. Anois roghnaítear luachanna i gcolún A má tá siad ann i gcolún D. Is féidir leat an Scrios eochair chun iad a scriosadh de láimh.

  Más mian leat triail saor in aisce (30 lá) a bheith agat ar an bhfóntas seo, cliceáil le do thoil chun é a íoslódáil, agus ansin téigh chun an oibríocht a chur i bhfeidhm de réir na gcéimeanna thuas.

Déan luachanna i liosta amháin a eisiamh go tapa ó liosta eile le Kutools for Excel

Earraí gaolmhara:

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 (16)
No ratings yet. Be the first to rate!
Claudio
Claudio
This comment was minimized by the moderator on the site
"foruma contains an error!"
Wondeful help...
Reply
0
0
Elisa
Elisa     Claudio
This comment was minimized by the moderator on the site
the function is correct: maybe you have Excel in a different language than english. Change the function names in that way. :)
Reply
0
0
Sarah
Sarah
This comment was minimized by the moderator on the site
Thank you for this. It solved my problem of wanting to include only companies from a list
Reply
0
0
Keith
Keith
This comment was minimized by the moderator on the site
This formula subtracts list in column B from list in columnA:=FILTER(IFNA(MATCH(A2:A12,B2:B6,0),A2:A12),ISNUMBER(IFNA(MATCH(A2:A12,B2:B6,0),A2:A12))=FALSE)
Reply
0
0
Keith
Keith     Keith
This comment was minimized by the moderator on the site
But this one's simpler (Subtracts list in A2:A20 from list in D2:D6):=FILTER(A2:A20,ISERROR(MATCH(A2:A20,D2:D6,0))=TRUE)
Reply
0
0
Bruno
Bruno     Keith
This comment was minimized by the moderator on the site
Keith,

This formula was incredibly helpful (even more helpful than the actual blog post!) You're a life saver! This formula gets really powerful if you use it in dynamic arrays.
Reply
0
0
Penny D Gaddy
Penny D Gaddy     Keith
This comment was minimized by the moderator on the site
what if I have multiple columns?
Reply
0
0
Keith
Keith     Penny D Gaddy
This comment was minimized by the moderator on the site
Depends on the structure of your lists, but if you want to exclude several lists Y and Z from a single big list X, try something like this:
=FILTER(X3:X21,ISERROR(MATCH(X3:X21,Y3:Y6,0))*ISERROR(MATCH(X3:X21,Z3:Z5,0))=1)
this returns listX minus listY and listZ.
The "ISERROR(MATCH(X3:X21,Y3:Y6,0))" returns "TRUE" if an item in listX is missing from listY, likewise ISERROR(MATCH(X3:X21,Z3:Z5,0)) for listX and listZ.
A quirk of Excel is that "=TRUE*TRUE" will return "1". In fact, any number of TRUEs multiplied will return "1", but include a single FALSE and it will return "0".
So the FILTER will return only those items from the list where all the "ISERROR(MATCH....." formulae are TRUE.
For each additional list of items to exclude, add an extra *ISERROR(MATCH(listX,list_exclude,0)) before the "=1)"
Reply
0
0
Keith
Keith     Penny D Gaddy
This comment was minimized by the moderator on the site
You could try a filter of a filter:=FILTER(A2:A20,ISERROR(MATCH(A2:A20,FILTER(Array2,Include_criteria2)))=TRUE)
Reply
0
0
Keith
Keith     Keith
This comment was minimized by the moderator on the site
Apologies, that should read "Subtracts list in D2:D6 from list in A2:A20."
Reply
0
0
Keith
Keith     Keith
This comment was minimized by the moderator on the site
Apologies, that should read "Subtracts list in D2:D6 from list in A2:A20."
Reply
0
0
Keith
Keith     Keith
This comment was minimized by the moderator on the site
Apologies, that should read "Subtracts list in D2:D6 from list in A2:A20"
Reply
0
0
Geo
Geo
This comment was minimized by the moderator on the site
Good find. Thank you for this clever workaround. I thought was only possible via macros.
Possible to do the same using method 1, for a range consisting in two columns. i.e City, State in col D2:E20 while my A2:C1000 (B:C has city,state) has the set I need to get marked?
tried this did not work: =COUNTIF($D$2:$E$20,B2:C2). Unless you have another wonderful guide.
Reply
0
0
Стас
Стас
This comment was minimized by the moderator on the site
Как безграмотный человек писал эту статью? Тот, кто русский язык не учил, ни одной книги не прочел и не общался в социуме никогда? Уже с самого начала статьи обороты почти в каждом предложении такие, что на голову не натянешь. Зачем писать, если не умеешь писать?
Reply
0
0
Zuvay
Zuvay
This comment was minimized by the moderator on the site
I receive other values in the COUNTIF column like 2 but also higher numbers other than 0 and 1. What does that mean?
Reply
0
0
LX
LX     Zuvay
This comment was minimized by the moderator on the site
The values you get in the COUNTIF column are equal to the number of occurrences of the element in the right column. For example, if you have 5 displayed against a cell in the Name column, it means that this name was found in the To-remove-list not one, but five times.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations