[算表] INDEX 疑問

[cutekid (可愛小孩子)]

軟體: 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 (臺灣)
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1565448218.A.025.html
→ soyoso: https://i.imgur.com/UjEKEpu.jpg 以函數sumif
→ cutekid: 謝謝 s 大，為什麼原本的 sum 會那樣呢
→ soyoso: 公式內的sum只有回傳一個加總後的元素值，因此以index指定
→ soyoso: 第二個元素就會回傳#ref
→ cutekid: 那第一個元素的 4 是怎麼得到的呢
→ cutekid: A1 + B1 嗎? 背後處理的邏輯好像跟所想的不太一樣
→ soyoso: 陣列公式的話，會加總a:a和b:b的第一個儲存格，為1+3
→ cutekid: 所以 sum 裡面有 multiple range 的話，它會將每個 range
→ cutekid: 第一個儲存格的值做加總 ?
→ soyoso: =INDEX(SUM(OFFSET(A:A,0,ROW(1:2)-1)),1)只會有一個元素
→ soyoso: 值
→ soyoso: 看起來應該也要和sum(a:a,b:b)回傳10才是
→ soyoso: 如這個邏輯是原po疑問的話，個人覺得這是sum函數在配合
→ soyoso: offset要在加上column讓其產生二維陣列，如=INDEX(SUM(
→ soyoso: OFFSET(A:A,ROW(1:2)-1,column(a:b)-1)),1)
→ soyoso: sum參照多個範圍，並不會只以第一個儲存格做加總
→ soyoso: 而是配合offset時要給於足夠的元素位置，原公式內只有一維
→ soyoso: 二個元素位置
→ soyoso: sum無法在這二個元素位置內就展開所有的值，因此帶入範圍
→ soyoso: 內的第一個儲存格值
→ soyoso: 回文11:28的公式應是
→ soyoso: =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
※ 編輯: cutekid (1.168.25.150 臺灣), 08/15/2019 14:54:28