小夥伴們好啊,今天老祝和大家分享一個非常有趣的實用技巧,先來看動畫,選擇查詢條件的時候,數據區域會自動高亮顯示:
這個技巧在查詢核對數據時非常方便,接下來咱們就一起來說說具體的做法。
首先單擊H1,按下圖步驟來設置數據有效性,數據來源是B1:E1,也就是季度所在單元格區域。
單擊H2,按同樣的方法設置數據有效性,數據來源為A2:A8,也就是姓名所在的單元格區域。
這樣設置後,就可以通過下拉菜單來選擇季度和姓名了。
接下來在H3單元格輸入查詢公式:
=VLOOKUP(H2,A:E,MATCH(H1,A1:E1,),) 公式中,VLOOKUP函數以H2單元格的姓名為查詢值,查詢區域為A:E列。 MATCH(H1,A1:E1,)部分,由MATCH函數查詢出H1在A1:E1單元格區域的位置,本例結果是2。 MATCH函數的結果作為VLOOKUP函數指定要返回的列數。 當調整H1單元格中的季度時,MATCH函數的結果是動態變化的,作用給VLOOKUP函數,就返回對應列的內容。
下一步就是設置條件格式了,在設置條件格式之前,咱們先來觀察一下規律:
當列標題等於H1中的季度時,這一列的內容就高亮顯示。
當行標題等於H2中的姓名時,這一行的內容就高亮顯示。
選中B2:E8,按下圖設置條件格式:
條件格式的公式是:
=(B$1=$H$1)+($A2=$H$2)
在設置條件格式時,公式是針對活動單元格來設置的,設置後會自動將規則應用到選中的區域中。
如果所選的範圍是多行多列的數據區域,在設置公式的相對引用和絕對引用時,很多小夥伴會比較暈。其實,你這樣想一下就會容易理解:
-
假如要設置條件格式的範圍是一列,你就考慮在活動單元格中輸入公式後,如果要將公式下拉,要使用什麼引用方式。
-
假如要設置條件格式的範圍是一行,你就考慮在活動單元格中輸入公式後,如果要將公式向右復制,需要使用什麼引用方式。
-
假如要設置條件格式的範圍是多行多列,你就考慮在活動單元格中輸入公式後,如果要將公式同時下拉、右拉,要使用什麼樣的引用方式。
公式中的「+」意思是表示兩個條件滿足其一,就是「或者」的意思。
如果單元格所在列的列標題等於H1中的季度,或者行標題等於H2中的姓名,兩個條件滿足其一,即可高亮顯示符合條件的該區域。
接下來,還有一個焦點的設置。
如果同時符合行標題和列標題兩個條件,則高亮顯示。
按照剛剛設置條件格式的步驟,使用以下公式:
=(B$1=$H$1)*($A2=$H$2) 這里的公式和剛剛的公式類似,只是將加號+變成了乘號*,表示要求兩個條件同時成立。
設置完畢,看效果吧。
今天的內容就是這些,祝各位小夥伴們一天好心情!
圖文製作:祝洪忠