[發問] 如何分別計算一個儲存格中的兩段數字

如例圖所示 D2到G2 都由 [數字][*字號][數字]組成

希望能夠做到
B2 負責總和 *字號後段數字總和也就是 10 + 2.9 + 0.08 + 200 = 212.98
同理 C2則計算 *字號前段數字總和也就是 20 + 1.5 + 900 + 0.09 = 921.59

目前自己摸索的辦法是
D3可用公式 =(LEFT(D2,(FIND("*",D2)-1))) 先取出D2前段數字 20
D4可用公式 =(RIGHT(D2,LEN(D2)-(FIND("*",D2)))) 再取出D2後段數字 10

之後再分別用C2=D3+E3+F3+G3 來總和
B2=D4+E4+F4+G4 來總和

問題1: 為什麼C2=SUM(D4:G4) 這樣會顯示為0 但C2=D3+E3+F3+G3 卻可以正確計算 (雖然還需手動設定格式為"數值")
問題2:實際用到的表格中不方便再使用額外的[列數]來計算排版設計關係
問題3:縱使我可以把C2寫成 = (LEFT(D2,(FIND("*",D2)-1))) + (LEFT(E2,(FIND("*",E2)-1))) + (LEFT(F2,(FIND("*",F2)-1))) + (LEFT(G2,(FIND("*",G2)-1)))
暫時達到一串算式完成計算,不用額外多增加其他欄位,但這樣公式實在太長,而且一旦 H2欄增加新的數值,就不試用了

以上是自己摸索的笨方法遇到的問題麻煩先進指導改善

樓主熱帖

subvert951

小學生 Rank: 1

帖子: 7
主題: 4
精華: 0
積分: 16
點名: 0
作業系統: WIN10
軟體版本: OFFICE 2003
閱讀權限: 10
註冊時間: 2019-12-18
最後登錄: 2020-6-23

2^#

發表於 2019-12-25 22:49 | 只看該作者

更正問題中的文字敘述

問題2:實際用到的表格中不方便再使用額外的[列數]來計算排版設計關係

不是列數應該是"行數"

TOP

hcm19522

大學生 Rank: 4

帖子: 1387
主題: 4
精華: 0
積分: 1397
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-11-25

熱烈參與勳章

3^#

發表於 2019-12-26 10:30 | 只看該作者

非數字字串*1=數字
https://blog.xuite.net/hcm19522/twblog/588769335

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-26

4^#

發表於 2019-12-28 10:05 | 只看該作者

問題1: 為什麼C2=SUM(D4:G4) 這樣會顯示為0 但C2=D3+E3+F3+G3 卻可以正確計算 (雖然還需手動設定格式為"數值")
__D4:G4公式產生的數字是[文字格式], 所以SUM出來當然為0, C2=D3+E3+F3+G3, "+"可將文字格式的數字轉為數值計算, 所以沒問題

問題2及3:
前和:=SUMPRODUCT(--(0&LEFT(D2:H2,FIND("*",D2:H2&"*")-1)))
尾和:=SUMPRODUCT(--(0&MID(D2:H2,FIND("*",D2:H2&"*")+1,9)))

TOP

subvert951

小學生 Rank: 1

帖子: 7
主題: 4
精華: 0
積分: 16
點名: 0
作業系統: WIN10
軟體版本: OFFICE 2003
閱讀權限: 10
註冊時間: 2019-12-18
最後登錄: 2020-6-23

5^#

發表於 2019-12-30 03:47 | 只看該作者

首先感謝【H大】及【准大】的熱心回答
目前分別嘗試了兩位的方法，雖然在照抄回範例中使用
都完全可以達到我想要的結果，但由於我是數理&EXCEL菜雞

目前我的摸索方式是，拆開公式個別去看各個函數的功用
我會知道在MID(123,2,2)會得到23，是什麼原理
我也會知道SUBSTITUTE(12341234,23,"AA",1)會得到231AA41234是什麼原理
但一些諸如 {1;9} {1;1;1;1} 9^9 這樣的字串，實在是不明白它在公式中的意義

導致若套用到實際需要使用的表格中，要修改一些參照還是範圍之類的
會不知道從何改起，例如H大提供的公式當中
B2:C6=INDEX(MMULT(TRIM(MID(SUBSTITUTE($D2:$G2,"*",REPT(" ",9)),{1;9},9))*1,{1;1;1;1}),3-COLUMN(A1))
因為實際運用中，可能會有另外的數據會增加在H2．甚至是I2、J2 等…
本來想說依樣畫葫蘆，把$G2,改成$H2就好，結果發現不行，然後就卡關了
以上算是單純分享菜雞心得，以下回到主體中延伸的新問題

綜合上述，我最後選擇了准大的方式，但同時另外想問
SUMPRODUCT(--(0&LEFT(D2:H2,FIND("*",D2:H2&"*")-1)))當中
也像我所提到的，如果資料會往Ｈ、Ｉ、Ｊ、　Ｋ欄等…增加
雖然目前我把D2:H2改成D2:FF2，就還是可以達成計算
（FF2算是想把資料的擷取範圍拉長）
但總覺得太土法煉鋼，之後或許會遇到不適合這樣改的情況
想問看看，想要表示在第2行中資料要從D2往後延伸的寫法？

以上，謝謝大家熱心指導～

TOP