瞬間搞定一月數據匯總!這個Excel求和公式太牛了

2021-02-24 Excel精英培訓

前幾天蘭色推過一期跨表公式合集,其中有一個是利用sum進行多表求和

【例】如下圖所示,需要在匯總表中統計1~30日的各個商品銷量合計(日報表和匯總表格式、位置完全一樣)

在匯總表B2中輸入公式:

=sum('*'!b2)

輸入後會自動替換為多表引用方式

=SUM('1日:30日 '!B2)

有同學提問:如果各個表中商品的位置(所在行數)不一樣,該怎麼求和?蘭色今天要分享一個更強大的支持行數不同的求和公式。

分析及公式設置過程:

如果對單個表(比如1日)進行對A商品進行求和,可以直接用sumif函數搞定:

1日表

在匯總表中設置求和公式:

=SUMIF('1日'!A:A,A2,'1日'!B:B)

依此類推,如果對30天求和,公式應為:

=SUMIF('1日'!A:A,A2,'1日'!B:B)+SUMIF('2日'!A:A,A2,'2日'!B:B)

+..+SUMIF('30日'!A:A,A2,'30日'!B:B)

這公式也太長了吧.

細心的同學會發現,公式雖然,但還是有規律的:對各個表的求和除了表名外,其他公式部分都相同。

利用這個特點,我們可以用row函數自動生成對1~30天的引用。

=Row(1:30) 的結果為

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}

為證明這一點,可以在單元格中輸入公式後,選中row(1:30)按F9鍵

連接成對各個表A列和B列的引用

=ROW(1:30)&"日!A:A"

=ROW(1:30)&"日!B:B"

連接成的只是字符串,並不能代表1:30日的A列和B列。把字符串地址轉換成真正的引用,這是indirect函數的特長:

=Inidrect(ROW(1:30)&"日!A:A")

=Indirect(ROW(1:30)&"日!B:B")

有地址了,把它套進sumif函數中會怎麼樣?

=SUMIF(Inidrect(ROW(1:30)&"日!A:A"),A2,Indirect(ROW(1:30)&"日!B:B"))

結果是會把各個表中的A產品銷量分別進行求和,查看結果按F9。

最後用sumproduct函數進行求和(這裡不用sum的原因是:sum無法直接支持數組運算,本公式中同時對多數組進行運算屬數組運算)

最終的公式為:

=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$30)&"日!a:a"),A2,INDIRECT(ROW($1:$30)&"日!b:b")))

由於公式複製後row(1:30)中的行數會發生變化,所以這裡必須要添加絕對引用符號$


註:如果是多表多條件求和,可以用sumifs函數,原理相同。

蘭色說:這是蘭色第1次對多表求和進行這麼詳細的解釋,這種解釋公式的形式如果同學們覺得好就點右下角在看支持,以後蘭色會繼續用這種形式剖析更多excel公式。

如果你是同學,長按下面二維碼 - 識別圖中二維碼 - 關注,就可以每天和蘭色一起學Excel了。

相關焦點

  • 這個Excel求和公式太牛了,1秒搞定一個月的數據匯總!
    老師曾經推過一期跨表公式合集,其中有一個是利用sum進行多表求和。【例】如下圖所示,需要在匯總表中統計1~30日的各個商品銷量合計(日報表和匯總表格式、位置完全一樣)B2)有同學提問:如果各個表中商品的位置(所在行數)不一樣,該怎麼求和?老師今天要分享一個更強大的支持行數不同的求和公式。分析及公式設置過程:如果對單個表(比如1日)進行對A商品進行求和,可以直接用sumif函數搞定:1日表
  • 瞬間搞定Excel數據互換行列!這個Excel技巧太牛了!
    作者:阿湯  學會計有方法原創首發小會最近在做一個數據透視表的時候,遇到了一個小小問題,可是這個問題似乎很常見,而其中的技巧大家可能會用得到,就先給大家分享這個技巧。一般在後續進行統計或者匯總操作,都需要把內容填充回去,所以我們選擇拆分並填充內容。步驟:選中已合併的單元格,找到菜單欄的合併居中(見圖二)
  • Excel中多個工作表不同位置數據,如何進行求和?
    問題:如何對雷哥等數據進行快速匯總求和?由於A列數據不重複,因此sumif函數就表示查找後的結果一列數組求和使用函數sumproduct即可求和根據上述思路,直接輸入函數公式即可求出結果=SUMPRODUCT(SUMIF(INDIRECT({"一月","二月","三月
  • 用這個公式,5秒鐘搞定100張工作表匯總
    (ID:ExcelLiRui)多表匯總問題經常會在工作中遇到,比如不同分公司的報表,不同期間的報表,不同產品的報表.對於這類分散在不同工作表中的多表數據匯總,是絕大多數人的噩夢。這是因為,要進行多表匯總,要麼需要SQL查詢,要麼需要VBA多表合併,要麼需要Power Query,所以,普通小白用戶是望而卻步的。但是對於相同結構,有規則的多工作表匯總,卻可以用一個公式輕鬆搞定。下面就結合一個實際案例,介紹思路及方法,下文詳述。原始文件中包含100張工作表,分別放置了每一個分公司的銷售數據,如下圖所示。
  • Excel中多工作表跨表按條件匯總求和
    點擊上方藍字關注 Office職場學堂多工作表跨表按條件匯總求和
  • Excel按單元格顏色求和,自定義公式一鍵完成,再也不用加班匯總數據了
    Excel數據求和相信大家都操作過,但是有一種特殊的求和方式,相信許多朋友至今都還在加班點擊求和,這就是按照單元格顏色進行數據求和
  • Power Query 閃開,最牛的Excel合併公式來了
    插入數據透視表 - 對日期進行月、日分組  - 把月拖到篩選標籤中 -  添加切片器:月分組依據 - 選取高級 - 添加和設置月份和日期 為分組項,然後對員工進行求和。修改編輯欄中的公式原公式:= Table.Group(重命名的列, {"月份", "日期"}, {{"過生日的員工", each List.Sum([員工]), type text}})
  • 工作常用的Excel數據對比和特殊求和
    Excel單元格文字行間距調整設置,可以借鑑這個思路。今天的教程為大家分享常用的數據對比,以及對比之後的求和。下面截圖的Excel數據源,「流水報表」工作表是小雅平時記錄的訂單情況,「核對報表」是銷售發給小雅的。
  • 多工作表跨表按條件匯總求和
    微信公眾號 | Excel函數與公式(ID:ExcelLiRui)個人微信號 | (ID:ExcelLiRui520)多工作表跨表按條件匯總求和之前在文章中講過條件求和的常用方法,單個工作表的條件求和,很多同學都已經會藉助Excel自動計算了,但當遇到多表條件匯總的問題,很多人還沒有思路。
  • Excel多工作表不同條件篩選匯總求和公式,看過的都已果斷收藏!
    (ID:ExcelLiRui)職場辦公中什麼問題都有可能遇到,比如多表求和、篩選求和、跨表匯總.當這幾種問題同時混合在一起時,你還能順利解決嗎?今天介紹一種幾乎沒人知道的多表多條件篩選求和公式,為了大家更好理解,下面結合案例介紹。
  • Excel表格製作教程:12種多列數據求和,最後一種方法太厲害!
  • 翻頁編輯,匯總求和,這3個技巧讓你「跨工作表處理」得心應手!
    比如:多個工作表來回切換,點來點去,手都酸了日報做了20多天了,老闆讓每頁加一個匯總行,一頁一頁改,淚奔了加完匯總,老闆又讓把20多頁的數據求個總和,最後還是求助計數器了這些工作,每天都在折磨著我。直到我發現了下面這3個技巧。
  • 集齊所有Excel求和公式,再不收藏就是你的錯了!
    求和是工作中最常用的數據統計要求。今天蘭色今天蘭色再次進行一次大整理,把和求和有關的函數公式來一次大歸集。千萬別錯過了這次收藏的機會。1、SUM求和快捷鍵在表格中設置sum求和公式我想每個excel用戶都會設置,所以這裡學習的是求和公式的快捷鍵。
  • 只會Sumif函數Out了,Excel常用的求和公式全在這兒!
    求和是工作中最常用的數據統計要求。今天蘭色再次進行一次大整理,把和求和有關的函數公式來一次大歸集。千萬別錯過了這次收藏的機會。
  • 在Excel中,加法和求和竟然還有這麼多學問!
    SUM函數的功能很簡單,就是用來求和。當你不知道這個函數的時候,可能會用這樣的方法求和:=1+2 或 = A+B雖然輸入 =1+2+3 或 =A1+B1+C2 即可得出很準確的結果,但是使用SUM函數則會更加簡單。以下分享在SUM函數中都有哪些知識點,看看是否有你遺漏的內容。
  • 總結三個Excel多條件求和的通用公式
    2、微信回覆:數字1-7,可查看近7天的技術文章。3、微信回覆:「教程」二字,可獲取海量Excel視頻學習教程下載地址。→ 操作方法在F20單元格輸入下列公式,按Enter鍵結束。=SUMIFS(H3:H18,C3:C18,"男",F3:F18,"本科")→ 原理分析SUMIFS函數多條件求和通用公式SUMIFS函數可以設置多達128個區域/條件對對單元格區域進行求和,本例中只應用了其中的兩個區域/條件對,即「性別/男」、「學歷/本科」,然後對「工資」列進行條件求和,即可得出性別為「男」
  • 史上最牛的Excel表格合併方法,瞬間合併12000個表格
    前言:匯總多個excel文件的所有工作表數據,是蘭色首次發布。網上很難搜到相關教程,所以同學們一定要轉發或收藏起來備用。
  • 別被這個Excel函數,拖慢了你的excel表格
    =SUMPRODUCT((A2:A100=E3)*(B2:B100=F3)*C2:C100)Sumproduct函數後來被很多同學運用的爐火純青,多條件求和變得不再是什麼難題。蘭色雖然知道這個函數運算速度慢,但一直也沒覺得有什麼,直到最近幾個同學的提問,蘭色才意識到這個函數的副作用有多麼的大。
  • 如何使用Excel分類匯總功能,快速做出多級統計求和
    在Excel中做統計求和,相信大家會想到很多辦法,函數公式?數據透視表?都不是,今天給大家介紹Excel一種常常讓人忽視,但卻非常實用的功能:分類匯總。分類匯總,各位小夥伴基本都能做出第一級,但二級、三級甚至是多級分類匯總怎麼做?相信很多小夥伴都是一臉懵逼。希望通過下面的介紹能對網友有所幫助。使用Excel分類匯總功能,快速做出多級統計求和大家好,我是案例:
  • Excel多條件匯總數據處理方式,你不會,但是你同事1分鐘就完成啦
    今天準備和大家分享是Excel函數篇中間有關多條件計算的知識點,當你學會了這幾個匯總公式,你完全理解了,就會發現你處理數據是如此之高效+簡單+愉悅!!其實SUMIFS函數的語法,和SUMIF的語法稍微有點不太一樣,我們可以這麼理解:=SUMIFS(求和的區域,條件區域1,條件1,條件區域2,條件二….)