excel 欄列交叉比對

c8836109

c8836109
個人積分：0分
文章編號：83586074

0分

樓主

2021-11-25 11:52

請教各位大神，
有辦法將同項目並同月份的做合計嗎？

excel 欄列交叉比對

K欄的單一比對的目前會用這兩種公式
K2=OFFSET($A$1,MATCH(J2,$A$2:$A$6,0),MATCH($K$1,$B$1:$G$1,0))
或
K3=VLOOKUP(J3,$A$1:$G$6,MATCH($K$1,$A$1:$G$1,0),0)

但是同月份合計的就想不到要怎麼做了...
懇請大神賜教！

2021-11-25 11:52 發佈

文章關鍵字 excel 欄列交叉比對

Yaude Huang

Yaude Huang
個人積分：5279分
文章編號：83586442

5279分

2樓

2021-11-25 12:31

我想到兩個方式

第一種
=SUMIFS(B2:G2,B7:G7,11)
其中B7:G7的部分自己想辦法放在你想要的欄位裡，我把它暫時放在你畫面裡還沒用到的欄位裡
裡面對應的就是
=MONTH(B1) ... =MONTH(B7)

就是有一個欄位去自動算出該日期的月份

這樣=SUMIFS(B2:G2,B7:G7,11)運算式就可以把所有11月份的都加總起來了

第二種
=SUMIFS(B2:G2,B1:G1,">="&DATE(2021,11,1),B1:G1,"<"&DATE(2021,12,1))
把所有日期在包含2021/11/1以後，在不包含2021/12/1以前的全部加總起來
這個缺點是如果複製表格剛好換年分，要自己全部修改，但如果有一個欄位剛好填寫年分，用欄位取代2021就比較好處理

c8836109

c8836109
個人積分：0分
文章編號：83587791

0分

樓主

2021-11-25 15:18

Yaude Huang wrote:
我想到兩個方式第一種(恕刪)

感謝大神分享！

但是這兩種方法好像都是在已知該項目的列數，才能直接用SUMIFS去指定要搜尋的範圍並加總；
若是不知道該項目所在列數的情況下，有辦法再加公式去判斷該項目所在的列數，然後才去做同月份的合計嗎？

snare

snare
個人積分：3046分
文章編號：83588655

3046分

4樓

2021-11-25 16:52

c8836109 wrote:
若是不知道該項目所在列數的情況下，有辦法再加公式去判斷該項目所在的列數，然後才去做同月份的合計嗎？

可以，但未來不好維護
如果您資料不多，或是才剛開始建立資料，建議重打換成下面這種排列方式

錦子

錦子
個人積分：2074分
文章編號：83589966

2074分

5樓

2021-11-25 20:18

二表格項目同一列
L2:
=SUMPRODUCT((MONTH($B$1:$G$1)=11)*(B2:G2))
按CTRL+SHIFT+ENTER鍵完成陣列公式輸入。
11可以直接設定或抓取某一儲存格內容。
點我看大圖

二表格項目不同列
L2:
=SUMPRODUCT((MONTH($B$1:$G$1)=11)*(OFFSET($A$1,MATCH(J2,$A$2:$A$6,0),1,1,6)))
按CTRL+SHIFT+ENTER鍵完成陣列公式輸入。
11可以直接設定或抓取某一儲存格內容。
1,1,6的6代表要抓取的欄數。
點我看大圖

錦色如月，子耀光芒。

Yaude Huang

Yaude Huang
個人積分：5279分
文章編號：83591122

5279分

6樓

2021-11-25 23:17

c8836109 wrote:
感謝大神分享！但是這(恕刪)

sumifs只能加總一整排的數字，後面是過濾條件
像你這樣的表格，如果要加總二維的表格可能就不適合，那就只能參考其他人sumproduct的作法

要不然你可以修改一下資料的陳列方式
例如這樣

我習慣會在資料後面塞一排特別用其他顏色分隔
公式套用分隔那一排
以後只要插入新的一排，完全不用修改公式
向這個範例第15排特別標成黃色，以後只要插入在這排之前，就不用傷腦筋位置跑掉

Mystique Hsiao

Mystique Hsiao
個人積分：7527分
文章編號：83591784

7527分

7樓

2021-11-26 3:11

同樣建議將資料的 X 軸及 Y 軸對調！

如果會考慮到跨年度，
或增加不同日期與項目，
可參考以下公式！

即便日期、項目繼續增加，
公式無需再做修改，
可直接向下、向右複製使用！

My Interior Knowledge is Extraordinaire

Yaude Huang

Yaude Huang
個人積分：5279分
文章編號：83593672

5279分

8樓

2021-11-26 10:57

=MMULT(TRANSPOSE(IF(($A$2:$A$6=J2),1,0)),MMULT(IF(B$2:G$6="",0,B$2:G$6), TRANSPOSE(IF((MONTH($B$1:$G$1)=11),1,0))))

改這樣吧
裡面的紅字部分分別對應第一直排的A~E字串
第一橫排的日期，月份11自己改
還有整個數字區域
應該很好理解

原理就是用整個array去乘另一組1與0的array
這組1與0的array，就是用月份判斷去轉換的
會得到一組橫排總和的array
再去與另一組1與0的array做最後乘法，過濾出A~E
也就是說最左邊直排的部分，甚至你可以有兩個以上的A~E都可以運算出來

Yaude Huang

Yaude Huang
個人積分：5279分
文章編號：83597156

5279分

9樓

2021-11-26 17:12

目前想到最簡單的作法

=SUM(FILTER(B$2:G$6,A$2:A$6=J2)*IF((MONTH($B$1:$G$1)=11),1,0))

細部分析
=FILTER(B$2:G$6,A$2:A$6=J2)
會將第一排是C的過濾出來

=IF((MONTH($B$1:$G$1)=11),1,0)
會將11月份過濾出來

=FILTER(B$2:G$6,A$2:A$6=J2)*IF((MONTH($B$1:$G$1)=11),1,0)
兩者合併就是同時是C與11月份的過濾出來

最後就是用SUM加總

但是這有個缺點
就是第一排的A~E，裡面不能有重複
否則第一步過濾C的那個動作會超過一排，最後會出現錯誤

Yaude Huang

Yaude Huang
個人積分：5279分
文章編號：83599190

5279分

10樓

2021-11-26 23:09

繼續改變作法
支援A~E可以重複

=SUM(FILTER(FILTER(B$2:G$6,MONTH($B$1:$G$1)=11, 0),A$2:A$6=J2, 0))

分解做法

=FILTER(B$2:G$6,MONTH($B$1:$G$1)=11, 0)
先將11月份的資料過濾出來，後面的0是如果都沒有資料傳回 0

=FILTER(FILTER(B$2:G$6,MONTH($B$1:$G$1)=11, 0),A$2:A$6=J2, 0)
加上第二層過濾，挑出最左邊屬於J2的部分範例是"C"

最後用SUM做加總

可以達成的方式有很多方法，過去我沒想過這種需求
我大都用能搭配sumifs的版面設計來達成需求
sumproduct 早期我也會使用，但我嫌它閱讀性較差，學會sumifs這類的函數後就很少用了
至於後來陳列的這些方法是今天去查excel函數表剛學來的
多參考一些範例，多嘗試就能夠找出方法來了

Yaude Huang

相容性的問題確實會有，在查使用說明時，FILTER目前只支援365 excel跟2021以後的excel, 自己版本用太舊就只能用較麻煩的方式了。

2021-11-27 20:07

c8836109 樓主

感謝Yaude Huang大大，若是我自已做，我也喜歡用sumifs的公式，再搭配一些輔助欄位就很好用了。只是因為要幫不懂公式的同事讓他們後續使用，就只能這樣了...

2021-11-29 17:01