返回列表 上一主題 發帖

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

subvert951

帖子
7 
主題
4 
精華
0 
積分
16 
點名
0  
作業系統
WIN10 
軟體版本
OFFICE 2003 
閱讀權限
10 
註冊時間
2019-12-18 
最後登錄
2020-6-23 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2019-12-25 21:55 | 只看該作者

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

如例圖所示 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

帖子
7 
主題
4 
精華
0 
積分
16 
點名
0  
作業系統
WIN10 
軟體版本
OFFICE 2003 
閱讀權限
10 
註冊時間
2019-12-18 
最後登錄
2020-6-23 
2#
發表於 2019-12-25 22:49 | 只看該作者
更正問題中的文字敘述

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

不是列數  應該是"行數"

TOP

hcm19522

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

熱烈參與勳章
3#
發表於 2019-12-26 10:30 | 只看該作者
非數字字串*1=數字
https://blog.xuite.net/hcm19522/twblog/588769335
google"EXCEL迷"  blog  或google網址:https://hcm19522.blogspot.com/

TOP

准提部林

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

帖子
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

        靜思自在 : 【生命在呼吸間】佛陀說：「生命在呼吸間。」人無法管住自己的生命，更無法擋住死期，讓自己永住人間。既然生命去來這麼無常，我們更應該好好地愛惜它、利用它、充實它，讓這無常、寶貴的生命，散發它真善美的光輝，映照出生命真正的價值。
返回列表 上一主題