作者yuitmko (yuitmko)
看板Office
標題[算表] excel如何計算區間數值?
時間Thu Oct 6 20:05:22 2022
現在要設計一個統計表格，遇到一個問題
因為可計算的數值只能是固定值
但資料中某些數值非固定值，而是介於區間範圍 (上下限值已知)
(1)希望能對該區間數值直接進行計算
(2)並且計算結果也能呈現相應的區間範圍
如下例效果：
A B C
-------------------
1 3 0~2 3~5
A1=3 (固定值)，B1=0~2 (區間值)，C1=sum(A1:B1)=3~5
請問有函式可用嗎？或者表格該如何修正？
上下限值分開計算當然是個方法，但如何將上下限值弄到同一行計算式中呢？
因為實際資料遠不只兩筆，不可能把每個區間數值都分成兩行計算式，太沒效率！
PS.
網路找到的範例，都是對區間數值進行判斷再執行「分類」或「計數」
我不是要這樣的效果，勿會錯意喔！
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 111.251.0.111 (臺灣)
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1665057924.A.A8B.html
1F：推 kinomon: 設法取出~前的數字及~後的數字，此即區間的下限及上限 10/06 21:01
2F：→ kinomon: find(“~”, B1)可回傳~在B1字串中的位置序數 10/06 21:04
3F：→ kinomon: left( B1, find(“~”, B1)-1 )即~前的字串 10/06 21:09
4F：→ kinomon: 但left或mid取出的字串必須放入value函數才可另做計算 10/06 21:11
5F：→ kinomon: C1= A1 + value( left( B1, find(“~”, B1)-1 ) ) & 10/06 21:20
6F：→ kinomon: “~” & A1 + value( mid( B1, find(“~”, B1)+1, 100 10/06 21:20
7F：→ kinomon: ) ) 10/06 21:20
感謝大大，基本解決問題了！
想進一步請教，如何簡化算式，不必個別輸入呢？
例如固定值加總可寫成sum(A1:F1)，就不用一個個輸入
如果將區間值放在連續欄位G1~M1，如何撰寫類似算式呢？
※ 編輯: yuitmko (111.251.0.111 臺灣), 10/06/2022 22:50:20
8F：→ kinomon: 不太明白，請具體示例 10/07 21:55
G H ... M N
------------------------------------------
1 ga~gb ha~hb ma~mb sum
如上例，G1~M1連續欄位均儲放區間值
標示a者為下限值，標示b者為上限值
N1=G1~M1之和
大大提供的算式已可計算N1值，但必須G1~M1一個個輸入
如何簡化算式，類似固定值總和函式sum(A:Z)，就不用A~Z一個個輸入了？
※ 編輯: yuitmko (111.251.0.111 臺灣), 10/08/2022 09:39:25
9F：推 kinomon: 沒什麼好主意 10/08 17:17
10F：→ kinomon: 如果覺得輸入公式內容麻煩，利用填充是一個點 10/08 17:19
11F：→ kinomon: 但說實話G到M也才7個數6個複製點，想更快解法的時間不一 10/08 17:21
12F：→ kinomon: 定划得來 10/08 17:21
謝謝
本來想說是否用巨集迴圈方式處理，但想不出怎麼寫
※ 編輯: yuitmko (111.251.0.111 臺灣), 10/08/2022 20:13:19
13F：推 windknife18: =SUMPRODUCT(--(LEFT(SUBSTITUTE(G1:M1,"~", 10/11 18:26
14F：→ windknife18: REPT(" ",20)),10)))&"~"&SUMPRODUCT(--(RIGHT( 10/11 18:26
15F：→ windknife18: SUBSTITUTE(G1:M1,"~",REPT(" ",20)),10))) 10/11 18:27
感謝，沒想過SUMPRODUCT()可以這樣用！ b^^
※ 編輯: yuitmko (111.251.4.97 臺灣), 10/13/2022 20:10:50