作者cutekid (可愛小孩子)
看板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 (臺灣)
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1565448218.A.025.html
2F：→ cutekid: 謝謝 s 大，為什麼原本的 sum 會那樣呢 08/11 01:38
3F：→ soyoso: 公式內的sum只有回傳一個加總後的元素值，因此以index指定 08/11 09:45
4F：→ soyoso: 第二個元素就會回傳#ref 08/11 09:45
5F：→ cutekid: 那第一個元素的 4 是怎麼得到的呢 08/11 10:23
6F：→ cutekid: A1 + B1 嗎? 背後處理的邏輯好像跟所想的不太一樣 08/11 10:27
7F：→ soyoso: 陣列公式的話，會加總a:a和b:b的第一個儲存格，為1+3 08/11 10:33
8F：→ cutekid: 所以 sum 裡面有 multiple range 的話，它會將每個 range 08/11 11:27
9F：→ cutekid: 第一個儲存格的值做加總 ? 08/11 11:28
10F：→ soyoso: =INDEX(SUM(OFFSET(A:A,0,ROW(1:2)-1)),1)只會有一個元素 08/11 11:28
11F：→ soyoso: 值 08/11 11:28
12F：→ soyoso: 看起來應該也要和sum(a:a,b:b)回傳10才是 08/11 11:28
13F：→ soyoso: 如這個邏輯是原po疑問的話，個人覺得這是sum函數在配合 08/11 11:28
14F：→ soyoso: offset要在加上column讓其產生二維陣列，如=INDEX(SUM( 08/11 11:28
15F：→ soyoso: OFFSET(A:A,ROW(1:2)-1,column(a:b)-1)),1) 08/11 11:28
16F：→ soyoso: sum參照多個範圍，並不會只以第一個儲存格做加總 08/11 11:34
17F：→ soyoso: 而是配合offset時要給於足夠的元素位置，原公式內只有一維 08/11 11:34
18F：→ soyoso: 二個元素位置 08/11 11:34
19F：→ soyoso: sum無法在這二個元素位置內就展開所有的值，因此帶入範圍 08/11 11:34
20F：→ soyoso: 內的第一個儲存格值 08/11 11:34
21F：→ soyoso: 回文11:28的公式應是 08/11 11:39
22F：→ soyoso: =INDEX(SUM(OFFSET(A1,ROW(1:2)-1,column(a:b)-1)),1) 08/11 11:39
意外發現:
選取 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