Note: The other languages of the website are Google-translated. Back to English
登入  \/ 
x
or
x
註冊  \/ 
x

or

如何在Excel中對多個單詞應用條件格式搜索?

doc高亮包含任何單詞1

對於我們而言,根據特定值突出顯示行可能很容易,本文中,我將討論如何突出顯示A列中的單元格,具體取決於是否在D列中找到它們,這意味著,如果單元格內容中包含任何文本,特定列表,然後突出顯示如左圖所示的屏幕截圖。

突出顯示單元格的條件格式包含多個值之一

篩選器單元格包含特定值並立即突出顯示它們


突出顯示單元格的條件格式包含多個值之一

事實上, 條件格式 可以幫助您解決這項工作,請執行以下步驟:

1. 首先,請為特定單詞列表創建一個範圍名稱,選擇單元格文本並輸入範圍名稱 我的清單 (您可以根據需要重命名)到 您的大名 框,然後按 Enter 鍵,請參見屏幕截圖:

doc高亮包含任何單詞2

2。 然後選擇要突出顯示的單元格,然後單擊 首頁 > 條件格式 > 新規則 新格式規則 對話框中,完成以下操作:

(1.)點擊 使用公式來確定要格式化的單元格選擇規則類型 列錶框;

(2.)然後輸入以下公式: = SUM(COUNTIF(A2,“ *”&Mylist&“ *”)) (A2 是要突出顯示範圍的第一個單元格, 我的清單 是您在步驟1)中創建的範圍名稱 格式化此公式為真的值 文本框;

(3.)然後單擊 格式 按鈕。

doc高亮包含任何單詞3

3。 去的 單元格格式 對話框,然後選擇一種顏色以突出顯示 標籤,請參見屏幕截圖:

doc高亮包含任何單詞4

4. 然後點擊 OK > OK 要關閉對話框,包含任何特定列表單元格值的所有單元格會立即突出顯示,請參見屏幕截圖:

doc高亮包含任何單詞5


篩選器單元格包含特定值並立即突出顯示它們

如果你有 Kutools for Excel,其 超級濾鏡 實用程序,您可以快速篩選包含指定文本值的單元格,然後立即突出顯示它們。

Kutools for Excel : 帶有300多個便捷的Excel加載項,可以在30天內免費試用. 

安裝後 Kutools for Excel,請執行以下操作:

1。 點擊 Kutools Plus > 超級濾鏡,請參見屏幕截圖:

2。 在 超級濾鏡 窗格,請執行以下操作:

  • (1.)檢查 規定 選項,然後單擊 doc高亮包含任何單詞11按鈕選擇要過濾的數據范圍;
  • (2.)根據需要選擇過濾條件之間的關係;
  • (3.)然後在條件列錶框中設置條件。

doc高亮包含任何單詞8

3。 設置條件後,單擊 篩選 過濾單元格包含所需的特定值。 然後為下面的選中單元格選擇一種填充顏色 首頁 標籤,請參見屏幕截圖:

doc高亮包含任何單詞9

4。 並且所有包含特定值的單元格都突出顯示,現在,您可以通過以下方式取消過濾器: 重設 按鈕,請參見屏幕截圖:

doc高亮包含任何單詞10

單擊立即下載並免費試用Kutools for Excel!


演示:過濾器單元格包含特定值並立即突出顯示它們

Kutools for Excel:具有300多個方便的Excel加載項,可以在30天內免費試用,沒有任何限制。 立即下載並免費試用!

最佳辦公效率工具

Kutools for Excel 解決了您的大部分問題,並將您的生產力提高了 80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 300 多項強大功能。 支持Office / Excel 2007-2019和365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能30天免費試用。 60天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
officetab底部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    aparnag228@gmail.com · 1 years ago
    Hello,

    is the formula sensitive to space?
  • To post as a guest, your comment is unpublished.
    pri · 1 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Jesse · 1 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Neel · 2 years ago
    In the countif function, a comma did NOT work for me. I had to use a semicolon instead.
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Neel,
      Maybe you have a different language version of Excel instead of English version.
      Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    umairaziz · 2 years ago
    The problem with above KUtools function is 01. It doesn't give the option to highlight the entire row. 02. It searches based on exact match of the cell, and partial match option is not there...... Anyone got a solution, please share as I need this way... thank you!
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Umair,
      The Kutools function in this article can solve your problems, the first one, if you need to highlight the entire rows, please select all the filtered whole rows, and then apply the fill color for them; the second once, you should choose the criteria Contains from the criteria drop down list to filter the rows based on the partial text.
      Please try it again, hope it can help you!
  • To post as a guest, your comment is unpublished.
    HCAZ · 2 years ago
    First great formula my only problem is that I have 3 list and although I changed the list names and used different colors - when I use a drop down all the names have the same colors.
  • To post as a guest, your comment is unpublished.
    Antony · 2 years ago
    this formula doesnot work for me
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello,Antony,
      Could you explain your problem more clearly, or you can insert a screenshot here. Thank you!
      • To post as a guest, your comment is unpublished.
        Gwen · 2 years ago
        I actually have the same problem, the formula highlights ALL cells in the range, why would that be?
        • To post as a guest, your comment is unpublished.
          skyyang · 2 years ago
          Hi, Gwen,
          The formula in this article works well in my worksheet, could you give your problem more detailed? Or you can insert a screenshot here for your problem.
          Thank you!
  • To post as a guest, your comment is unpublished.
    Rebecca · 3 years ago
    I made my named range table on another tab.

    I highlighted the row before I started making my rule. =$B$7:$X$7

    =SUM(COUNTIF(E7,"*"&CANCELLED&"*"))

    I double checked the 'applies to" and it correctly includes the whole row of the table.

    The rule is working - but only colors the first cell in the row, not the entire row.


    Any idea what I'm doing wrong?

    Can the fact the E7 cell a dynamic drop-down based on a drop-down in D7 be the cause?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hi, Rebecca,
      If you want to highlight the whole row in your selected range, please use this formula:

      =SUM(COUNTIF($A2,"*"&Mylist&"*"))
      The cell reference should be converted to column absolute ($A2).

      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Shirtman · 3 years ago
    Hi, I am using this formula to find IP addresses in a column (from a list).

    It misses out some cells which it should highlight, and vice versa (highlights some which shouldn't be highlighted). The cells do seem to have some similarity to the IP addresses which I am looking for.

    Is it something to do with the fact that there are 4 decimal points? (E.g. if I am looking for 10.54.31.7, it might highlight 10.54.31.5 as well).

    Any ideas as to how I can fix this would be greatly appreciated - it's so close to working!
    • To post as a guest, your comment is unpublished.
      Gwen · 2 years ago
      I have the same problem! Did you ever fix it? I wonder why they replied others but never you...
  • To post as a guest, your comment is unpublished.
    su · 3 years ago
    im unable to do use that formula, can you help in what i might be missing?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, su,
      Make sure the range name in the formula is your own, or you can insert your problem screenshot here.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Rob · 4 years ago
    That's useful technique. Would you mind walking through how it works? Thanks