Skip to main content

Conas cuardach formáidithe coinníollach a chur i bhfeidhm ar iliomad focal in Excel?

tá aon fhocail 1 i mbuaicphointe doc

B’fhéidir go mbeadh sé éasca dúinn sraitheanna atá bunaithe ar luach ar leith a aibhsiú, an t-alt seo, labhróidh mé faoi conas aird a tharraingt ar chealla i gcolún A ag brath má tá siad le fáil i gcolún D, rud a chiallaíonn, má tá téacs ar bith san ábhar cille liosta sonrach, ansin aibhsigh mar a thaispeántar an scáileán ar chlé.

Tá ceann de roinnt luachanna i bhformáidiú coinníollach chun aird a tharraingt ar na cealla

Tá luachanna ar leith i gcealla scagtha agus leagann siad béim orthu ag an am céanna

Tá ceann de roinnt luachanna i bhformáidiú coinníollach chun aird a tharraingt ar na cealla

Go deimhin, ar an Formáidiú Coinníollach in ann cabhrú leat an post seo a réiteach, déan na céimeanna seo a leanas le do thoil:

1. Ar dtús, cruthaigh ainm raon don liosta focal sonrach, roghnaigh an téacs cille agus cuir isteach ainm raon Míliosta (is féidir leat a athainmniú de réir mar is gá duit) isteach sa Ainm bosca, agus brúigh Iontráil eochair, féach an scáileán:

tá aon fhocail 2 i mbuaicphointe doc

2. Ansin roghnaigh na cealla ar mhaith leat aird a tharraingt orthu, agus cliceáil Baile > Formáidiú Coinníollach > Riail Nua, I Riail Nua Formáidithe bosca dialóige, críochnaigh na hoibríochtaí thíos:

(1.) Cliceáil Úsáid foirmle chun a fháil amach cé na cealla atá le formáidiú faoi ​​na Roghnaigh Cineál Riail bosca liosta;

(2.) Ansin cuir isteach an fhoirmle seo: = SUM (COUNTIF (A2, "*" & Mylist & "*")) (A2 Is í an chéad chill den raon is mian leat a aibhsiú, Míliosta an t-ainm raon a chruthaigh tú i gcéim 1) isteach sa Luachanna formáide nuair atá an fhoirmle seo fíor Bosca téacs;

(3.) Agus ansin cliceáil déanta cnaipe.

tá aon fhocail 3 i mbuaicphointe doc

3. Téigh go dtí an Cealla Formáid bosca dialóige, agus roghnaigh dath amháin chun aird a tharraingt ar na cealla faoin Líon cluaisín, féach an scáileán:

tá aon fhocail 4 i mbuaicphointe doc

4. Agus ansin cliceáil OK > OK chun na dialóga a dhúnadh, aibhsítear na cealla uile ina bhfuil aon cheann de na luachanna sonracha liosta liosta ag an am céanna, féach an scáileán:

tá aon fhocail 5 i mbuaicphointe doc

Tá luachanna ar leith i gcealla scagtha agus aibhsigh iad ag an am céanna

Má tá tú Kutools le haghaidh Excel, Lena Scagaire Super áirgiúlacht, is féidir leat na cealla ina bhfuil luachanna sonraithe téacs a scagadh go tapa, agus ansin iad a aibhsiú ag an am céanna.

Kutools le haghaidh Excel : le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. 
Téigh go dtí Íoslódáil
Triail In Aisce 30 láCeannach
PayPal / MyCommerce

Tar éis a shuiteáil Kutools le haghaidh Excel, déan mar a leanas le do thoil:

1. Cliceáil Kutools Plus > Scagaire Super, féach ar an scáileán:

2. Sa an Scagaire Super pána, déan na hoibríochtaí seo a leanas le do thoil:

  • (1.) Seiceáil Sonraithe rogha, agus ansin cliceáil tá aon fhocail 11 i mbuaicphointe doc cnaipe chun an raon sonraí a theastaíonn uait a scagadh a roghnú;
  • (2.) Roghnaigh an gaol i measc na gcritéar scagaire de réir mar is gá duit;
  • (3.) Ansin socraigh na critéir sa bhosca liosta critéar.

tá aon fhocail 8 i mbuaicphointe doc

3. Tar éis duit na critéir a shocrú, cliceáil scagairí chun na cealla a scagadh tá na luachanna sonracha de réir mar is gá duit. Agus ansin roghnaigh dath líonta amháin do na cealla séalaithe faoi Baile cluaisín, féach an scáileán:

tá aon fhocail 9 i mbuaicphointe doc

4. Agus aibhsítear na luachanna sonracha sna cealla go léir, anois, is féidir leat an scagaire a chealú trí chliceáil Clear cnaipe, féach ar an scáileán:

tá aon fhocail 10 i mbuaicphointe doc

Cliceáil Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!

Taispeántas: Tá luachanna ar leith i gcealla scagtha agus aibhsigh iad ag an am céanna

Kutools le haghaidh Excel: le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. Íoslódáil agus triail saor in aisce Anois!

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 (34)
Rated 4.5 out of 5 · 1 ratings
AKASH
AKASH
This comment was minimized by the moderator on the site
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(Mylist<>"",Mylist),A2)))>0
How to Format whole row in this formula
Reply
0
0
AKASH
AKASH
This comment was minimized by the moderator on the site
THANK YOU VERY MUCH, YOU SAVED A LOT OF TIME OF ME.
Reply
0
0
skyyang
skyyang     AKASH
This comment was minimized by the moderator on the site
Hello, Akash,
Glad it can help you!
Have a good new year! 😄
Reply
Report
0
0
Akash
Akash
This comment was minimized by the moderator on the site
Above formula not working, coz , My condition (i.e if my list is blank). And if my list is remaining blank excel formatting all cells. Help me.
Reply
0
0
skyyang
skyyang     Akash
This comment was minimized by the moderator on the site
Hello, Akash,
If your data list contains blank cell, please apply the below formula into the Conditional Formatting:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(Mylist<>"",Mylist),A2)))>0
Please have a try, hope it can help you!
Reply
Report
0
0
Akash
Akash     skyyang
This comment was minimized by the moderator on the site
I want to format whole Row. In this formula how to format whole Row.
Reply
0
0
AKASH
AKASH
This comment was minimized by the moderator on the site
I want to format only the text contained in my list. I do not want to format if my list (one or two cell of my list) is blank
Reply
0
0
skyyang
skyyang     AKASH
This comment was minimized by the moderator on the site
Hello, AKASH
To solve your problem, the following article may help you:
https://www.extendoffice.com/documents/excel/2499-excel-conditional-formatting-ignore-blank-zero.html

Please have a try, thank you!
Reply
Report
0
0
Sykopaten
Sykopaten
This comment was minimized by the moderator on the site
Greetings!

I am trying to use a range of words in a row, from another sheet.
My formula looks like this:

=Info!=SUM(COUNTIF($A1,"*"&LGreen&"*"))

"Info" is the sheet, "LGreen" is the row of words i want to use, and the words start at A1 in "Info" sheet.

But i still get "There is a problem with this formula.......
Does it matter if the words are in a row/column? And how do i make that change in the formula?
I would like to use the whole row (continuous), so that i can put in more words later on.
If not, i can select specific range/cells, as long as i understand whats wrong x)

Thanks in advance!
Reply
0
0
skyyang
skyyang     Sykopaten
This comment was minimized by the moderator on the site
Hello, Sykopaten

In fact, you just need follow the method in the article, in the first step, you should create a range name for your data list in aother sheet, which can be applied to the entire workbook. And then use the formula: =SUM(COUNTIF(A2,"*"&Mylist&"*")) into the conditional formatting of the worksheet you want to highlihgt the cells.

Or you can insert your excel file as attachment here for us to check.

Thank you!
Reply
Report
0
0
Sykopaten
Sykopaten     skyyang
This comment was minimized by the moderator on the site
Thank you so much to the reply! This surely helped a lot!
Have a great new year! :D
Reply
0
0
skyyang
skyyang     Sykopaten
This comment was minimized by the moderator on the site
Hello, Sykopaten,
Glad it can help you!
Happy new year！😀
Reply
Report
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
Can you provide instructions for how to reverse this formula. In the same example, how would you format cells that did NOT contain a word from your keyword list?
Reply
0
0
Rated 4.5 out of 5
skyyang
skyyang     Jeff
This comment was minimized by the moderator on the site
Hello, Jeff,
To solve your problem, please apply the below formula:
=IF(SUM(COUNTIF(A2,"*"&Mylist&"*"))=1, 0,1)

Please have a try, hope it can help you!
Reply
Report
0
0
Jaqueline Moreira de Oliveira
Jaqueline Moreira de Oliveira
This comment was minimized by the moderator on the site
Consegui, a formula é funcional e adorei!! obrigado!
Reply
0
0
aparnag228@gmail.com
This comment was minimized by the moderator on the site
Hello,

is the formula sensitive to space?
Reply
Report
0
0
pri
pri
This comment was minimized by the moderator on the site
I have a List that contains "Numbers" and when I run this formula it only work on "Word/Text". What shall be my approach for numbers? For example, I have two rego ( registration) number for management team eg 1234 and 6789, so my data in A column will contain a lot of different vehicle number, i want to hightlight GREEN only these two regos.
Reply
0
0
Jesse
Jesse
This comment was minimized by the moderator on the site
This is for the "OR" function. How do I go abouts this with the "AND"?

As in I need to highlight ONLY the cells that have all of the list and not just either of the list.
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