※ 本文為 lecheck 轉寄自 ptt.cc 更新時間: 2019-08-13 19:52:52
看板 Office
作者 標題 [算表] INDEX 疑問
時間 Sat Aug 10 22:43:36 2019
軟體: Excel
版本: 2010
儲存格狀況:
A B C D E F ...
1 1 3
2 2 4
問題 1: 公式 =INDEX(SUM(OFFSET(A:A,0,ROW(1:2)-1)),1) 得到結果: 4
問題 2: 公式 =INDEX(SUM(OFFSET(A:A,0,ROW(1:2)-1)),2) 得到結果: #REF
問題 1 預期結果: 3，因為 INDEX(SUM({A:A,B:B},1) = INDEX({3,7},1) = 3
問題 2 預期結果: 7，因為 INDEX(SUM({A:A,B:B},2) = INDEX({3,7},2) = 7
不知道哪邊理解錯誤
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 1.168.25.150 (臺灣)
※ 文章代碼(AID): #1TJjWQ0b (Office)
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1565448218.A.025.html
![[圖]](https://i.imgur.com/UjEKEpu.jpg)
→ : 謝謝 s 大，為什麼原本的 sum 會那樣呢2F 08/11 01:38
→ : 公式內的sum只有回傳一個加總後的元素值，因此以index指定3F 08/11 09:45
→ : 第二個元素就會回傳#ref
→ : 第二個元素就會回傳#ref
→ : 那第一個元素的 4 是怎麼得到的呢5F 08/11 10:23
→ : A1 + B1 嗎? 背後處理的邏輯好像跟所想的不太一樣
→ : A1 + B1 嗎? 背後處理的邏輯好像跟所想的不太一樣
→ : 陣列公式的話，會加總a:a和b:b的第一個儲存格，為1+37F 08/11 10:33
→ : 所以 sum 裡面有 multiple range 的話，它會將每個 range8F 08/11 11:27
→ : 第一個儲存格的值做加總 ?
→ : 第一個儲存格的值做加總 ?
→ : =INDEX(SUM(OFFSET(A:A,0,ROW(1:2)-1)),1)只會有一個元素10F 08/11 11:28
→ : 值
→ : 看起來應該也要和sum(a:a,b:b)回傳10才是
→ : 如這個邏輯是原po疑問的話，個人覺得這是sum函數在配合
→ : offset要在加上column讓其產生二維陣列，如=INDEX(SUM(
→ : OFFSET(A:A,ROW(1:2)-1,column(a:b)-1)),1)
→ : sum參照多個範圍，並不會只以第一個儲存格做加總
→ : 而是配合offset時要給於足夠的元素位置，原公式內只有一維
→ : 二個元素位置
→ : sum無法在這二個元素位置內就展開所有的值，因此帶入範圍
→ : 內的第一個儲存格值
→ : 回文11:28的公式應是
→ : =INDEX(SUM(OFFSET(A1,ROW(1:2)-1,column(a:b)-1)),1)
意外發現:→ : 值
→ : 看起來應該也要和sum(a:a,b:b)回傳10才是
→ : 如這個邏輯是原po疑問的話，個人覺得這是sum函數在配合
→ : offset要在加上column讓其產生二維陣列，如=INDEX(SUM(
→ : OFFSET(A:A,ROW(1:2)-1,column(a:b)-1)),1)
→ : sum參照多個範圍，並不會只以第一個儲存格做加總
→ : 而是配合offset時要給於足夠的元素位置，原公式內只有一維
→ : 二個元素位置
→ : sum無法在這二個元素位置內就展開所有的值，因此帶入範圍
→ : 內的第一個儲存格值
→ : 回文11:28的公式應是
→ : =INDEX(SUM(OFFSET(A1,ROW(1:2)-1,column(a:b)-1)),1)
選取 C,D 欄，公式填上 =OFFSET(A:A,0,ROW(1:2)-1)，按Ctrl+Shift+Enter
會無法複製 A,B 欄的資料，改成 =OFFSET(OFFSET(A:A,0,ROW(1:2)-1),0,0)
就可以複製。
※ 編輯: cutekid (1.168.25.150 臺灣), 08/11/2019 21:10:04
大約懂 s 大的意思了:
offset 所產生的多組 range 應用在 sum 函數裡，沒有辦法達到類似下面的效果
{sum(range1),sum(range2),..} ←多組結果
只能牽生 range_start:range_end 給 sum 使用，所以最後還是只回傳一個結果
反觀 sumif 即可達到: {sumif(range1,..),sumif(range2,..),..} ←多組結果
※ 編輯: cutekid (1.168.25.150 臺灣), 08/11/2019 22:11:36
根據 sum 函數 range_start:range_end 的靈感
試出類似的語法: =SUM(OFFSET(A,0,{0,0;1,1}))
這樣就可以達到 { SUM(A:A),SUM(B:B) } 多組 sum 結果的陣列
※ 編輯: cutekid (1.168.25.150 臺灣), 08/12/2019 15:26:03
--
※ 看板: whatla 文章推薦值: 0 目前人氣: 0 累積人氣: 19
回列表(←)
分享