函數與條件格式的完美結合:會變色的Excel銷售統計查詢模板

2020-12-11 Excel與財務

【溫馨提示】親愛的朋友，閱讀之前請您點擊【關注】，您的支持將是我最大的動力！

小編最近幾天一直在分享與銷售統計報表有關的教程，有需要的小夥伴可以點擊我的頭像去主頁查看相關教程。今天小編再分享一個銷售統計查詢模板，支持動態查詢，並且查詢的數據會突出顏色顯示。需要模板的可以評論區留言或私信我。

先來看效果圖：

實現這個效果圖用到了條件格式和數據驗證功能，用到的主要函數有SUM、MATCH、OFFSET等函數。具體操作步驟如下：

1、設置查詢部門菜單：選中B12單元格，點擊【數據】選項卡中的【數據驗證】按鈕，在彈出的新窗口中選擇【設置】頁面，驗證條件允許選擇【序列】，點擊下方的來源文本框，然後選擇A2：A10單元格區域，最後點擊【確定】返回工作區，部門下拉菜單就製作好了。

2、設置開始月份下拉菜單：選中B13單元格，點擊【數據】選項卡中的【數據驗證】按鈕，在彈出的新窗口中選擇【設置】頁面，驗證條件允許選擇【序列】，在來源文本框中輸入【1,2,3,4,5,6,7,8,9,10,11,12】，最後點擊【確定】返回工作區。注意來源文本框中輸入逗號前先把輸入法切換到英文狀態。

3、設置結束月份下拉菜單：選中B14單元格，重複上一步操作。

4、計算銷售合計：在B15單元格輸入公式：

=SUM(OFFSET(A1,MATCH(B12,A2:A10,0),B13+2,1,B14-B13+1))

這個公式的關鍵是OFFSET函數

OFFSET函數：以指定的引用為參照系，通過給定的偏移量返回新的引用。

語法：OFFSET(參照系引用區域,偏移的行數,偏移的列數,[新引用區域的行數],[新引用區域的列數])

公式中A1單元格作為起始位置，偏移行數用MATCH函數值來確定（查找部門所在的行），偏移列數使用開始月份，然後再加上前面的兩列，即B13+2，參數3引用1行，參數4列數為結束月份-開始月份+1

5、設置顏色突出顯示：選中表格A1:O10單元格區域，點擊【開始】選項卡中的【條件格式】按鈕，選擇【新建規則】

在編輯格式規則窗口中選擇【使用公式確定要設置格式的單元格】，為符合此公式的值設置格式文本框中輸入公式：=AND($A1=$B$12,COLUMN(A1)>=$B$13+3,COLUMN(A1)<=$B$14+3)，再點擊【格式】按鈕，選擇【填充】頁面，選擇一個顏色，最後點擊【確定】返回工作區

通過以上設置，一個動態查詢並突出顏色顯示的銷售統計模板就做好了，教程中公式難度較大，希望小夥伴們多多練習。

小夥伴們，在使用Excel中還碰到過哪些問題，評論區留言一起討論學習，堅持原創不易，您的點讚轉發就是對小編最大的支持，更多教程點擊下方專欄學習。

如何將Excel表格中的金額顯示為不同單位，一招搞定自由切換

銷售統計經常用到的6個Excel函數公式，做出的銷售報表老闆最喜歡

在Excel中輸入以「0」開頭的數字的2種方法

相關焦點

  • excel函數應用技巧：這個銷售統計模板，能動態變色查詢
    今天向大家分享一個銷售統計表模板。該模板支持動態查詢功能，並且在查詢的時候，相應數據會變色，如此，查詢結果一目了然。統計模板將使用SUM、AND、COLUMN、MATCH、OFFSET函數並結合條件格式和數據驗證。學習更多技巧，請收藏關注部落窩教育excel圖文教程。
  • 銷售MM用excel 做了張會 自動變色 的動態查詢報表,老闆都看傻了【excel函數公式】
    點擊圖片 了解雙11  1元搶購
  • 6個Excel條件格式功能的應用技巧,職場人士必學教程
    在Excel中，有個條件格式功能，能夠對表格中的內容根據指定條件，返回設定好的格式，其中內置了很多條件格式類型，比如數據條顯示數據、顯示重複值等等。除了這些內置的條件格式設置外，如果將函數公式與條件格式結合，那可以說是相當完美，省去我們手工設置單元格格式，今天小編就分享幾個條件格式中使用公式的用法，看看會有哪些精彩展現。
  • excel條件格式和函數公式應用前奏篇:條件格式的作用
    excel在上一篇文章中，我們介紹了函數index和函數column、函數row的聯合運用在實際操作中的應用。上訴的案例都是講述關於特定數據的提取和整理的問題，今天我們將開啟新的話題，關於excel中的條件格式和函數公式的應用。今天我們內容主要就是關於excel中條件格式的基礎應用，在以後的文章中，我們會逐漸深入，將excel中的條件格式問題與函數公式的應用充分結合起來。
  • EXCEL數據區間個數多方法統計,四種函數公式,條件格式靈活應用
    メ大家請看範例圖片，左側為數據源，我們要統計該數據中位於【80-90】（包含80以及90）的個數。メメ首先我們用條件格式來統計。選中數據點擊條件格式找到【介於】功能。メメ第二種函數，輸入函數：=COUNTIFS(B2:B12,">=80",B2:B12,"<=90")，該函數是最常用的。メ
  • excel中函數countif與條件格式功能的聯合運用
    excel今天我們講述的內容還是承接了上一篇文章「excel中函數if與函數countif的聯合運用中的案例，在上一篇文章中，我們將函數if和函數countif結合起來，寫出「=IF(COUNTIF(D:D,A2)=1,"參加考試","未參加考試")」或「=IF(COUNTIF(D:D,A2)=0,"未參加考試","參加考試")形式的函數式，從而對同學們是否參加考試的結果進行了判定。
  • 無法對條件格式設置的顏色進行數據統計,這是為什麼? - Excel從零...
    >對帶有顏色的單元格進行數據統計我們可以將它看作是條件計數或者是條件求和的一種情況，每一種顏色都有對應的RGB值，我們對帶有顏色單元格進行數據統計，本質上就是將RGB值一樣的數據放在一起統計而在excel中常見的單元格填色有兩種，一種是手動填色還有一種就是利用條件格式來進行填充顏色。
  • EXCEL函數公式大全之利用TODAY函數和條件格式自動突顯一周內工作
    EXCEL函數公式大全之利用TODAY函數和條件格式自動突顯一周以內日期單元格。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數TODAY函數和條件格式的巧妙組合。
  • excel數據統計:三個公式提高統計工作效率
    在日常的辦公中，我們經常會統計excel裡各種數據。在excel裡關於統計的函數也是數不勝數，SUM、SUIMIF、SUMIFS、COUNT、COUNTIFS等等。，結合近期學員們遇到的問題，老菜鳥總結了三個非常實用的公式，每個公式都可以解決一類問題。
  • HR必備excel模板,自動統計+圖表分析!
    ├─1-高顏值自動生成模板（23個）人事檔案員工信息臺帳及人員結構分析表Excel(設置提醒).xlsx人事管理員工信息查詢系統Excel.xlsx公司行政費用統計表（多圖表精細分析）.xls可用於面試的性格測試（自動生成分析）.xls各部門人員統計報告excel（圖表實時預覽，更清晰
  • excel小技巧:多條件查詢統計計數,match函數真的很友好
    今天跟大家分享一個實際案例：查詢滿足多個條件的人數，當更改班級科目以及分數線後查詢人數，比如要查到三班數學分數大於70的個數如果是確定位置的咱們使用countifs多條件統計即可，今天咱們案例中科目的位置是變化，所以需要嵌套
  • EXCEL條件格式妙用,搭配VLOOKUP函數完成指定查詢
    今天跟大家分享一下EXCEL條件格式妙用，搭配VLOOKUP函數完成指定查詢，結果特殊標記。大家請看範例圖片，下表是員工工資信息表，如何在查詢指定員工某項工資時特殊標記出工資信息呢？先在A9點擊數據驗證，來選擇員工姓名。再在B8點擊數據驗證來選擇工資類型。這樣就可以在A9選擇員工，在B8選擇工資類型。現在設置結果特殊標記。
  • EXCEL函數公式大全之利用YEAR函數COUNTIF函數統計員工入職年份
    EXCEL函數公式大全之利用YEAR函數與COUNTIF函數的組合統計各個年份員工入職的人數。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數YEAR函數與COUNTIF函數的組合。
  • Excel函數匯總:S開頭函數釋義
    為了方便大家學習excel函數，一飛把excel2010的所有函數整理了出來，按字母排序更好找。
  • Excel條件格式實現選中查找的單元格高亮顯示
    、條件格式、VLOOKUP函數和其他的知識點完成。公式剖析：VLOOKUP函數以B11單元格的小組為查詢值，查詢區域為A:E列。MATCH(B10,A1:E1,)部分，由MATCH函數查詢出B10在A1:E1單元格區域的位置。MATCH函數的結果作為VLOOKUP函數指定要返回的列數。當調整B10單元格中的月份時，MATCH函數的結果是動態變化的，作用給VLOOKUP函數，就返回對應列的內容。
  • excel新建格式規則與函數公式在實際操作中的應用
    excel前兩篇文章中，我們一直在了解excel中的條件格式設置的作用。相信看過前兩篇文章的朋友們，一定對條件格式的使用方法和用途有所了解。後面還講述了【紅-黃-綠色階】在數據中的應用，中間還穿插介紹了清除之前設置條件格式規則的方式。(對於excel條件格式的基本應用方法感興趣的朋友可以在看完該篇文章之後參考文章
  • Excel函數匯總:T開頭函數釋義
    為了方便大家學習excel函數，一飛把excel2010的所有函數整理了出來，按字母排序更好找。
  • 財務懂得這三個函數,辦公效率會大大提高。 - 小豬談教育
    作為一名財務人員，在工作中常常會用到excel等辦公軟體。下面小編就簡單介紹一下幾個重要的函數用法，供大家參考。雖然看似很簡單的函數，如果會用，工作效率會大大提高。圖13.實例：某公司財務系統內價格庫，導出成excel格式後，生成了《產品單價表》，如下圖：
  • excel函數應用技巧:那些名不副實的函數列舉
    說到「廢柴」一詞，相信小夥伴們很難把它和excel中各類神通廣大的函數聯繫在一起。但是隨著excel版本的不斷更新，不少函數逐漸被取代、淘汰，我們把這樣的函數稱為「廢柴」函數。學習更多技巧，請收藏部落窩教育excel圖文教程。
  • 銷售統計經常用到的6個Excel函數公式,做出的銷售報表老闆最喜歡
    作為公司的銷售統計，天天面對的銷售數據匯總求和、找出最大銷量、計算平均銷量等等，今天小編分享幾個Excel函數公式，輕鬆完成銷售統計報表，最後還可以生成圖表讓數據更直觀。上圖表格中，是3家店鋪上一周的產品銷售數量，以此為例對銷售報表作如下統計處理：一、按部門統計各產品的日均銷量在K2單元格輸入公式：=AVERAGEIF($B:$B,$J2,C:C)