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

2020-12-05 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從零...
    ，每一種顏色都有對應的RGB值，我們對帶有顏色單元格進行數據統計，本質上就是將RGB值一樣的數據放在一起統計而在excel中常見的單元格填色有兩種，一種是手動填色還有一種就是利用條件格式來進行填充顏色。
  • excel數據統計:三個公式提高統計工作效率
    在日常的辦公中，我們經常會統計excel裡各種數據。在excel裡關於統計的函數也是數不勝數，SUM、SUIMIF、SUMIFS、COUNT、COUNTIFS等等。今天我們總結了三類小夥伴們經常遇到的統計問題，也將分享三種對應的解決方法，以後再面對這三類統計問題，就再也不怕啦~＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊善於在工作中使用函數、公式可以提高工作效率，結合近期學員們遇到的問題，老菜鳥總結了三個非常實用的公式，每個公式都可以解決一類問題。學會這三個公式套路，就能解決日常遇到的很多麻煩事。
  • 銷售統計經常用到的6個Excel函數公式,做出的銷售報表老闆最喜歡
    作為公司的銷售統計，天天面對的銷售數據匯總求和、找出最大銷量、計算平均銷量等等，今天小編分享幾個Excel函數公式，輕鬆完成銷售統計報表，最後還可以生成圖表讓數據更直觀。上圖表格中，是3家店鋪上一周的產品銷售數量，以此為例對銷售報表作如下統計處理：一、按部門統計各產品的日均銷量在K2單元格輸入公式：=AVERAGEIF($B:$B,$J2,C:C)然後再向右、向下拖動填充公式即可得出結果。AVERAGEIF 函數：計算某個區域內滿足給定條件的所有單元格的平均值（算術平均值）。
  • 掌握這7條excel函數,自動化生成數據周報上篇
    excel的二八原則曾經在面試時候被問到VLOOKUP和HLOOKUP有什麼區別，我回答的是前者是以列匹配，後者是以行匹配。面試完我一個勁兒後悔沒有回答好，對這個函數不熟悉，回答太簡單。諷刺的是已經過去好幾年，我卻一次未用到過HLOOKUP。所以真的沒必要抱著一本excel大全在那挨個學函數，浪費時間。
  • Excel函數公式:巧用「條件格式」,讓數據完美差異化顯示 - Excel...
    這個功能就是【條件格式】。一、標註大於X的值。目的：將大於等於95分的值填充為「淺紅色」。方法：1、選定數據源。2、【條件格式】-【突出顯示單元格規則】-【大於】。3、數據94，選擇填充色並【確定】。二、在指定區域標註數值區域大於X個值的區域。
  • excel數據處理技巧:組合函數統計產品批號
    正如前面分析的，批號是由兩部分組成的，第一部分很容易，可以直接用TEXT函數從生產日期中得到，公式為：TEXT(A2,"yymm")。TEXT函數的教程之前分享過很多篇，不再細說了，公式中的"yymm"表示將日期按照兩位年兩位月的格式顯示結果。
  • excel排序求和:如何統計前幾名數據合計
    今天我們要說說，如何在excel中，統計前幾名數據的合計。這個問題難倒了不少小夥伴，尤其是遇到數據是雜亂無序的情況，那更是要了老命。不過，這對於excel大神來說，還是非常簡單的，分分鐘列出一個公式，就完美解決了問題！今天我們就一起來破解一下面對這類問題時，大神都是怎麼做的吧！＊＊＊＊＊＊＊＊＊什麼叫做統計前幾名合計呢？
  • excel函數應用技巧:按區間統計個數,就用Frequency
    編按：價格帶統計與按成績統計優良中差的人數是一樣的，都是按區間統計個數。最簡單、最快速的辦法是用高級函數Frequency。學習更多技巧，請收藏關注部落窩教育excel圖文教程。價格帶分析是一項基礎的數據分析，在某醫藥銷售公司工作的小王，最近就遇上一個這樣的任務……領導給了50個護肝類藥品的價格信息，讓小王統計出每個價格區間的品規數，數據要求如圖所示： 註：表中價格數據為模擬值並非市場實際價格。明確需求：A、B、C三列是50種同類藥品的明細，價格範圍在3~160元之間。
  • excel中的經典查找引用函數之lookup函數的使用
    excel表格的眾多函數中，我們常用的查找引用函數大致有3個，分別是有縱向查找功能的vlookup函數、有橫向查找功能的hlookup函數和可以任意多條件查找引用的lookup函數。下面我們來介紹lookup函數的用法。
  • EXCEL函數學習:CountIF,CountIFs(條件統計函數) - 網際網路上的小蜘蛛
    下面學習一下條件統計函數。對於單條件統計，可以使用CountIFCOUNTIF 是一個單條件統計函數，用於統計滿足某個條件的單元格的數量；語法 COUNTIF(range,criteria) Range 為需要計算其中滿足條件的單元格數目的單元格區域。 Criteria 為確定哪些單元格將被計算在內的條件，其形式可以為數字、表達式或文本。
  • 一文學透:excel表格數據統計圖表製作,班主任必看!
    ，除了常規的制表外，其實excel還隱藏著諸多便捷功能，今天就先和老師們來聊一下excel表格如何進行數據統計及圖表製作；其實，如果對於常規的信息錄入，老師們只要打開製作excel的軟體，直接往表格裡添加序列名，然後在對應的列名裡填入對應信息，保存後，就可以完成簡單的信息錄入；但是很多時候，老師們都需要對錄入的數據進行其他操作
  • 讓excel單元格顏色隨日期自動變化,你會嗎?用條件格式1分鐘搞定
    Hello，大家好，今天跟大家分享下如何讓表格的填充色跟隨日期自動變化，這種效果常用於數據的標記，我們可以快速的找到想要查看的數據，這個的操作也不難，使用條件格式以及函數即可輕鬆搞定，下面就讓我們來一起操作下吧 一、today函數 在這裡我們需要用到today
  • Excel函數公式
    解讀：AND函數的主要作用就是「並且」的意思，所有條件必須符合。二、VLOOKUP：數據查詢。3、【條件格式】-【新建規則】。4、在【選擇規則類型】中選定【使用公式確定要設置的單元格格式】。5、在【為符合此公式的值設置格式】中輸入：=($L$4=$B4)並單擊右下角【格式】-【填充】，選取填充色並【確定】-【確定】。
  • 案例|Excel九宮格矩陣評價統計,離不開這些函數
    營長說在績效管理方案中，經常會遇到矩陣評價，常見的有2*2和3*3矩陣。總有學員問與矩陣查詢統計的問題，有一定的代表性。今天營長就以經典的3*3九宮格矩陣為例，向你介紹涉及到的函數。01.這涉及到矩陣中行列交叉查詢的問題，需要用到經典的INDEX+MATCH函數。MATCH函數（雷達）MATCH函數返回對應的單元格坐標，即某個數據在指定數據區域裡面排第幾。
  • excel函數應用技巧:如何按不同要求,改變數字格式
    【ANSWER 1】【函數解析】我們可以直接將A2單元格的格式為日期格式，也可以得到日期值。但是我們是來玩函數的，那就還是用函數來解決吧。TEXT函數，格式寫作「yyyy-mm-dd」，年月日的英文首字母。【ANSWER 2】同答案1一樣依然使用TEXT函數，但是表達式改變了，「e-mm-dd」。這裡的e相當於yyyy，即4位的年份表達式。
  • 如何讓excel自動填充顏色?使用條件格式即可輕鬆搞定
    這一章我們來學習下excel中的條件格式。，我們選擇條件格式選擇其中的新建規則，然後選擇使用公式確定要設置的單元格，操作步驟如下圖在編輯規則中我們可以設置公式（公式必須以等號開頭）然後給滿足公式的單元格填充一個格式，下圖所示我們點擊格式，會看到可以設置數字格式，字體格式，有無邊框以及填充色下面就跟大家演示下如何使用公式進行條件格式的設置（設置一個大於500就填充紅色）首選我們選擇想要設置的區域
  • Excel中最值得珍藏的16個函數公式
    今天蘭色分享的excel函數公式都不常用，但一旦遇到就會讓你感覺頭痛，只能到處提問和查找。今天蘭色把這些公式收集到一起。以備急時之需。 14、Vlookup多表查找公式 工資表模板中，每個部門一個表。
  • Excel函數公式:使用好「條件格式」,數據一目了然
    如果我們需要對一些數據做一些特殊的標記，我們最常用的就是【條件格式】功能，今天我們要學習的是【條件格式】的【最值】技巧。一、利用公式標記同行最小（大）值。2、【條件格式】-【新建規則】-【使用公式確定要設置的單元格格式】。3、在【為符合此公式的值設置格式】下方輸入公式：=C3=MIN($C3:$J3)。4、單擊【格式】-【填充】，選取填充色（例如紅色）-【確定】-【確定】。
  • 數據查詢還在使用vlookup?跟我一起製作酷炫的聚光燈查詢效果吧
    對於數據查詢這一類的問題，相信很多人都會選擇使用vlookup函數來解決，但是今天要跟大家分享另一種十分酷炫的數據查詢方式：使用聚光燈來進行數據查詢，在這裡十字光標中心就是我們要查找的數據。>首先我們點擊A1單元格然後按ctrl+A選擇整個據區域，然後點擊條件格式選擇新建規則，然後選擇使用公式確定格式輸入公式：=OR($A1=$O$2,A$1=$P$2)然後點擊格式選擇填充，找一個自己喜歡的顏色即可點擊確定，至此我們就看到聚光燈了在這裡利用了or函數來構建條件第一參數：$A1=$O
  • excel函數公式應用:多列數據條件求和公式知多少?
    如果是根據條件求單列數據之和，SUMIF函數即可解決，但如果是求多列數據呢？我們這裡分享12種方法，各有各的特色。學習更多技巧，請收藏關注部落窩教育excel圖文教程。 先來看一下什麼是按條件求多列數據之和。 類似下圖這樣的數據，需要根據G列的產品名稱在H列匯總數據。條件區域在B列，而要求和的數據在C、D、E三列中。