有網友想要詢問：在 Excel 的工作表中處理資料時，能夠使用公式來處理變動的儲存格範圍。例如下圖中的各欄資料可能會有所增／刪，該如何處理呢？
一般當你計算加總時，例如儲存格F2：=SUM(A2:A18)
當你在Data1的欄位資料中刪除或是插入一筆資料時，公式=SUM(A2:A18)會跟著調整，可是如果你新增的資料是在最後一筆以外的位置，則 SUM 公式中的儲存格範圍，不會自動調整，該如何使用公式讓他可以自動調整呢？
你可以試試 OFFSET 函數和 INDIRECT 函數，假設每欄資料不會超過999列：
(1) 儲存格F2：=SUM(OFFSET(A2,ROW(1:1)-1,0,COUNTA(A2:A999),))
COUNTA(A1:A999)：計算 Data1 中含有數字的儲存格個數。
透過 OFFSET 函數配合 COUNTA(A2:A999) 的結果，即可達到變動儲存格範圍。
(2) 儲存格F2：=SUM(INDIRECT("A2:A"&COUNTA(A2:A999)+1))
使用 INDIRECT 函數配合 COUNTA(A2:A999) 的結果，即可達到變動儲存格範圍。
【自行練習】
修改公式，求出 Data2 和 Data3 的總和！
文章標籤
全站熱搜
老師您好，我想要 1.在A格內可選"空運"或"海運" 2.選"空運"的話在B格內，輸入數值區間的其中一個數值，會在C格帶出特定的數值，如輸入1~100的數值區間其中之一，另一格帶出75%。但有好多個區間要設定。 3.選"海運"的話，要帶出不同的數值區間，如1~1000，且很多區間分別帶出不同的百分比， 以上該如何作呢?請賜教指導，感恩謝謝^^
請參考：http://isvincent.pixnet.net/blog/post/43810012
請問如何得到data1+data2+data3的總和? 謝謝
簡單一點的做法： =SUM(OFFSET(A2,0,0,COUNTA(A2:A999),))+SUM(OFFSET(B2,0,0,COUNTA(B2:B999),))+SUM(OFFSET(C2,0,0,COUNTA(C2:CA999),)) 或 =SUM(OFFSET(A2,0,0,999,3)) 複雜一點的做法： =SUM(OFFSET(A2,0,0,max(COUNTA(A2:A999),COUNTA(B2:B999),COUNTA(C2:C999)),3))
老師您好請問我原本的公式 SUMPRODUCT(($B$3:$B5=B5)*($D$3:$D5=D5)) $B$3:$B5跟$D$3:$D5這時候還可以判斷邏輯值 但是我B5跟D5的欄是向下拉的時候要變成自動浮動配合塞選功能的我改成 SUMPRODUCT((INDIRECT("$B$3:$B"&row([@代號a])=B5)*($D$3:$D5=D5)) 這一段INDIRECT("$B$3:$B"&row([@代號a])=B5)的邏輯值都只會判斷成false 請問我哪邊需要調整嗎
您好，想請問OFFSET(A2,0,ROW(1:1)-1,COUNTA(A2:A999),)這部分 OFFSET參數格式OFFSET(reference, rows, cols, [height], [width]) 所以請問 OFFSET(A2,0,ROW(1:1)-1,COUNTA(A2:A999),) 這部分的使用或是判斷原理
原公式有誤！ 調整為：=SUM(OFFSET(A2,ROW(1:1)-1,0,COUNTA(A2:A999),))， 其中： ROW(1:1)-1：是因為OFFSET函數的參數以0為起始。 COUNTA(A1:A999)：計算 Data1 中含有數字的儲存格個數。
老師請問，如果要將大範圍儲存格在不同工作表(分成12月+統計頁)加總並同步，要使用何種參數？ 原先是使用合併彙算:加總，但發現前面工作表修改但最後統計工作表不會變動，且數值還會亂跳，已確定每個工作表皆相同
假設工作表名稱是1月,2月,...,11月,12月，並且儲存格位置都相同，如果使用公式：=SUM('1月:12月'!A1)，即可將1月至12月的儲存格予以加總。
*****
*****
老師您好，最近用Excel加總時，已經下拉填滿儲存格公式(例如=sum(B5:R5))，但在輸入前方要加總的部分後，加總公式的儲存格範圍居然會跳掉，變成=sum(B5:V5)，然後會跳出錯誤檢查的小三角，但我並沒有更動該儲存格的公式，不知道是什麼問題讓公式會自動修正之類的？還請老師指導... 已拜讀老師的部落格多時，從中學習很多實用的技巧，感謝您不吝分享這一切～
老師您好，我想請問一下，最近我想設計一個表格，依據我的資料加總同樣商品對應的數值，我使用了SUNIF，可是我的資料會一直往下增加，我該怎麼設定讓計算公式在我新增資料後自動修改範圍？
請參考：https://isvincent.pixnet.net/blog/post/49696978
老師您好，我有遇到一個瓶頸卡關請問OFFSET可以直接寫等於OR嗎？像是 =IF($A1="","",IF(OFFSET($A1,0,0,1,1)=OR("你","我","他"),"早安","跑跑跑")) 像這樣的巢狀判斷式，請問函數和函數之間可以直接用等於嗎？？？謝謝回答~(^_^;)
您好，我猜你的公式：OR("你","我","他")是想要表達，這三個字的其中一個。公式可以： 陣列公式：=IF($A1="","",IF(OR(OFFSET($A1,0,0,1,1)={"你","我","他"}),"早安","跑跑跑")) 或 陣列公弍：{=IF($A1="","",IF(OR(OFFSET($A1,0,0,1,1)={"你","我","他"}),"早安","跑跑跑"))}