excel公式組合解釋

Question

下列公式 看不懂
=PRODUCT(--MID(A2,ROW($A$2:INDIRECT("A"&LEN(A2))),1))
能理解的 大概就是外面這個PRODUCT
那MID這部份 裡面這個用法...看不出來位啥這樣用

還有$A$2:INDIRECT
這種用法可以用其他的方式來做變動的範圍嗎?
像是INDEX之類的?

原始問題網頁http://tw.knowledge.yahoo.com/question/?qid=1106111214556

Answer 1

如A2=12345MID(A2,ROW($A$1:INDIRECT("A"&LEN(A2))),1)MID(text,start_num,num_chars)MID(A2,ROW(A1:A"&LEN(A2),1)　;如LEN(A2)=5 則=ROW(A1:A5)MID(A2,ROW(A2:A5),1)MID(A2,{1;2;3;4;5},1)陣列公式按Ctrl+Shift+Enter為={MID(A2,1,1);MID(A2,2,1);MID(A2,3,1);MID(A2,4,1);MID(A2,5,1)}={1;2;3;4;5}=PRODUCT({1;2;3;4;5})=120如此分解,不知明白否=SUMPRODUCT(--MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1))=SUMPRODUCT({1;2;3;4;5})=15SUMPRODUCT本身為陣列公式固無須再按Ctrl+Shift+Enter

2006-11-13 16:29:28 補充：
INDEX很難呈現｛1;2;3;4;5｝形態OFFSET()用法:=SUMPRODUCT(--MID(A2,ROW(OFFSET(A1,,,LEN(A2))),1))異曲同工;試看看

2006-11-13 16:54:05 補充：
MID()函數所解出為文字格式無法計算固在前面加個"--"將其轉為數值資料以利計算

2006-11-13 20:15:38 補充：
ROW($A$1:INDIRECT("A"&LEN(A2)))=ROW(INDIRECT("$A$1:A"&LEN(A2)))=ROW(OFFSET(A1,,,LEN(A2)))={1;2;3;4;5}以上公式寫在儲存格中所顯示的只是第一筆資料{1}以陣列公式{=SUM(ROW(OFFSET(A1,,,LEN(A2))))}即可看出是15;如像此類公式我較喜好SUMPRODUCT函數=SUMPRODUCT(ROW(OFFSET(A1,,,LEN(A2))))直接Enter就可得解而不用陣列公式的手法按什麼跟什麼,有的人無法吸收.

2006-11-13 20:50:46 補充：
類似題參考:
http://tw.knowledge.yahoo.com/question/?qid=1206110803277&r=w#openions

Answer 2

2位專家真是高手，百般佩服!

Answer 3

AB1數值 乘積 212345 120323 6 4245 405234567 5040看來左兄對「陣列公式」也有強烈的興趣，那筆者就慢慢說故事囉。話說，原發問者僅是想把 1+2+3+4+5 使用公式加以計算之，而我卻霧裡看花，錯看成將每個數字相乘。因此，無意中寫出這段陣列公式：{ =PRODUCT(--MID(A2,ROW($A$1:INDIRECT("A"&LEN(A2))),1)) }現在，依照上述工作表內容將公式慢慢拆解：一、LEN(A2) 在一般公式中（非陣列公式）時，LEN(A2) =5 當公式往下複製時，得到 LEN(A3)=2, LEN(A4)=2, LEN(A5)=5，亦即使用 LEN 函數求得 A2,A3,A4,A5 等儲存格的字元長度。二、INDIRECT("A"&LEN(A2))在一般公式中（非陣列公式）時，B2 公式中 INDIRECT("A"&LEN(A2)) =A5，用意在依照 A2 儲存格的字元長度，再使用 INDIRECT 函數求得該儲存格的參照位址，以供上一層函數 ROW 來求得 A5 儲存格之列號，得到 5。三、{ROW($A$1:INDIRECT("A"&LEN(A2)))}開始進入陣列公式拆解：B2 公式中 {ROW($A$1:INDIRECT("A"&LEN(A2)))}，開始拆解：{ROW($A$1:INDIRECT("A"&5)))}，再拆解：{ROW($A$1:A5)}，再拆解：{1;2;3;4;5}將公式往下複製到 B3:B5時，由於 A3:A5 各儲存格字元長度依序為 2,3,6，而公式中之 ROW(...) 運算式依序會得到 {1;2}，{1;2;3}，{1;2;3;4;5;6}四、{--MID(A2,ROW($A$1:INDIRECT("A"&LEN(A2))),1)}B2 公式中 {--MID(A2,ROW($A$1:INDIRECT("A"&LEN(A2))),1)}，依據第三，開始拆解：{--MID(A1,{1;2;3;4;5},1)}，再拆解：{--{MID(A1,1,1);--MID(A1,2,1);--MID(A1,3,1);--MID(A1,4,1);MID(A1,5,1)}}，再拆解：{--{"1";"2";"3";"4";"5"}}，請注意此時的大括弧位置，由於使用 MID 函數運算之後會得到文字資料 "1";"2";"3";"4";"5"，因此使用 -- 轉換成數值資料，再拆解：{1;2;3;4;5}將公式往下複製 B3:B5，依序得到：{2;3}，{2;4;5}，{2;3;4;5;6;7}五、{=PRODUCT(--MID(A2,ROW($A$1:INDIRECT("A"&LEN(A2))),1))}這是最外一層陣列公式了，B2 公式中{=PRODUCT(--MID(A2,ROW($A$1:INDIRECT("A"&LEN(A2))),1))}， 依據第四，開始拆解：{=PRODUCT({1;2;3;4;5}) }，而 PRODUCT 函數就求出引數中的每個引數之乘積，因此得到 120 公式往下複製到 B3:B5，依序得到 6，40，5040 備註：關於 -- 連續兩個減號的相關資訊，請參考：http://tw.knowledge.yahoo.com/question/?qid=1106110113392

2006-11-14 02:27:02 補充：
後記：其實 applerot 頂級高手回答的已經相當仔細了！

2006-11-14 11:52:07 補充：
對了， applerot 的公式是「加總」，我的公式則是「乘積」，有點不一樣~