麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 含有文字的加總 [打印本頁]

作者: PJChen 時間: 2019-6-26 22:26 標題: 含有文字的加總

各位先進好,

請問用以下公式計算出D2:D6的答案,如何在D7求得總合387箱+24瓶
D2=INT(C2/B2)&"箱"&IF(MOD(C2,B2)=0,"","+"&MOD(C2,B2)&"瓶")
[attach]30942[/attach]

作者: hcm19522 時間: 2019-6-27 09:46

https://blog.xuite.net/hcm19522/twblog/587869523

作者: 劉大胃 時間: 2019-6-27 11:17

我覺得只能用下列方式

1. 在E欄位輸入箱數 => E2=RoundDown(C2/B2,0)
2. 在F欄位輸入瓶數 => F2=MOD(C2,B2)
3. D7=SUM(E2:E6) & "箱" & IF(SUM(F2:F6)=0,"","+" & SUM(F2:F6) & "瓶")

作者: PJChen 時間: 2019-6-27 22:15

回復 3# 劉大胃

謝謝你,這個方法我會,我要的是可以直接在D7加總的方法

作者: hcm19522 時間: 2019-6-28 11:19

https://blog.xuite.net/hcm19522/twblog/587875528

作者: 准提部林 時間: 2019-6-29 08:36

利用B/C欄加總也可以, 為何一定要用D欄?
可上傳檔案參考~~

作者: 劉大胃 時間: 2019-6-29 14:27

回復 6# 准提部林

Hello, 准大

他這個的每一行的子數與母數是不同的!
如統一要在一個欄位裡,各別相除後, 又再各別加總的話?
可有涵數可用??
Thanks ~

作者: 准提部林 時間: 2019-6-29 15:57

回復 7# 劉大胃

陣列公式(三鍵):
=MID(TEXT(SUM(INT(C2:C6/B2:B6)),"+0箱;;")&TEXT(SUM(MOD(C2:C6,B2:B6)),"+0瓶;;"),2,29)

作者: 准提部林 時間: 2019-6-29 17:02

硬要用D欄文字加總的話.
陣列公式
=MID(TEXT(SUM(-TEXT(LEFT(D2:D6,FIND("箱",D2:D6&"箱")-1),"0;;;!0")),";+0箱;")&TEXT(SUM(-TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(D2:D6,"瓶",),"+",10^9),9),"0;;;!0")),";+0瓶;"),2,29)
或
=SUBSTITUTE(SUBSTITUTE(TEXT(SUM(--TEXT({0,"0 "}&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2:D6,"+",),"箱"," "),"瓶","/9999"),"0 0/9999;;0;!0")),"#箱+0瓶/9999"),"/9999",),"+0瓶",)

作者: PJChen 時間: 2019-6-29 22:05

回復 5# hcm19522
回復 7# 劉大胃
回復 9# 准提部林

各位好,
跟大家道歉,口語化我們公司內都會說?箱+?瓶,但在文件中,我都只show出?箱+?,而把瓶的單位省略,我一下不查,把瓶的單位給加進去了....不好意思
更正如下:
[attach]30964[/attach]
這是一個範例檔,平日用的檔案料號很多,且各自入數都不相同,所以會用到各自的C欄/B欄,算出每個料號的總箱數及瓶數,但因為用了文字,我不知如何在每一個D欄中的數目再合計, 一方面工作需要用到,另一方面,我想知道可不可能在不增加欄數的情形下加總D欄,看到各位的回覆,真的眼睛為之一亮,原來是可以的...
想麻煩各位幫忙修正函數將瓶的單位省略掉之外,可否也幫我說明下公式的用法！不好意思我沒用過這樣的方式,自己又拆解不出來,感謝大家...
[attach]30963[/attach]

作者: 准提部林 時間: 2019-6-29 22:20

回復 10# PJChen

陣列公式:
=MID(TEXT(SUM(INT(C2:C6/B2:B6)),"+0箱;;")&TEXT(SUM(MOD(C2:C6,B2:B6)),"+0;;"),2,29)

作者: PJChen 時間: 2019-6-29 23:00

本帖最後由 PJChen 於 2019-6-29 23:08 編輯

回復 11# 准提部林

感謝准大,
公式OK,
請問"+0箱;;"的意思?

另外這個公式我也用得到,想請問如何改為沒有瓶的用法,方便也解說這個公式嗎?
=MID(TEXT(SUM(-TEXT(LEFT(D2:D14,FIND("箱",D2:D14&"箱")-1),"0;;;!0")),";+0箱;")&TEXT(SUM(-TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(D2:D14,"瓶",),"+",10^9),9),"0;;;!0")),";+0瓶;"),2,29)

作者: 准提部林 時間: 2019-6-30 09:18

回復 12# PJChen

完全沒有"瓶"字:
=MID(TEXT(SUM(-TEXT(LEFT(D2:D6&"X",FIND("箱",D2:D6&"X箱")-1),"0;;0;!0")),";+0箱;")&TEXT(SUM(-TEXT(RIGHT(SUBSTITUTE(D2:D6,"+",10^9),9),"0;;0;!0")),";+0;"),2,29)

含或不含"瓶", 都適用:
=MID(TEXT(SUM(-TEXT(LEFT(D2:D6&"X",FIND("箱",D2:D6&"X箱")-1),"0;;;!0")),";+0箱;")&TEXT(SUM(-TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(D2:D6,"瓶",),"+",10^9),9),"0;;;!0")),";+0;"),2,29)

作者: PJChen 時間: 2019-6-30 21:32

回復 13# 准提部林
請問准大,

這二個公式當D欄其中一格顯示0或空白時,加總會出現不同情形?
完全沒有"瓶"字: 當D欄其中一格顯示0 或空白時,加總都正常
=MID(TEXT(SUM(-TEXT(LEFT(D2:D6&"X",FIND("箱",D2:D6&"X箱")-1),"0;;0;!0")),";+0箱;")&TEXT(SUM(-TEXT(RIGHT(SUBSTITUTE(D2:D6,"+",10^9),9),"0;;0;!0")),";+0;"),2,29)

含或不含"瓶", 都適用: 當D欄其中一格顯示空白時,加總正常,但當D欄其中一格顯示 0時,加總就變成#VALUE!,
=MID(TEXT(SUM(-TEXT(LEFT(D2:D6&"X",FIND("箱",D2:D6&"X箱")-1),"0;;;!0")),";+0箱;")&TEXT(SUM(-TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(D2:D6,"瓶",),"+",10^9),9),"0;;;!0")),";+0;"),2,29)
另外，我很想了解這二個公式的意思，可否幫個忙註解一下?

作者: 劉大胃 時間: 2019-7-1 08:45

回復 9# 准提部林

Hello, 准大

謝謝你的教導, 又學到一課了!!
Thanks ~

作者: 准提部林 時間: 2019-7-1 11:31

回復 14# PJChen

含或不含"瓶", 都適用: 當D欄其中一格顯示空白時,加總正常,但當D欄其中一格顯示 0時,加總就變成#VALUE!,
=MID(TEXT(SUM(-TEXT(LEFT(D2:D6&"X",FIND("箱",D2:D6&"X箱")-1),"0;;0;!0")),";+0箱;")&TEXT(SUM(-TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(D2:D6,"瓶",),"+",10^9),9),"0;;0;!0")),";+0;"),2,29)

另外，我很想了解這二個公式的意思，可否幫個忙註解一下?
__這....有點困難....技巧問題, 須經驗累積, 要講也講不清楚~~

作者: 准提部林 時間: 2019-7-1 11:50

回復 14# PJChen

分解動作, 自行去研究:
[attach]30968[/attach]

作者: 劉大胃 時間: 2019-7-1 16:11

Hello, PJ

我覺得准大的這個公式比較簡單, 我也稍微改了一下,MID的公式不用應可以.
其餘各別公式拆出來, 就比較好理解了!

PS. 因為INT有4捨5入的問題, 所以比較建議用無條件捨去.

=TEXT(SUM(ROUNDDOWN(C2:C6/B2:B6,0)),"0箱;;")&TEXT(SUM(MOD(C2:C6,B2:B6)),"+0;;")

Hello, 准大

請教一下Text公式裡, 都有";;"這個結尾, 其用意為何?
再請不吝賜教! Thanks ~

作者: 准提部林 時間: 2019-7-1 16:43

本帖最後由准提部林於 2019-7-1 16:45 編輯

回復 18# 劉大胃

TEXT(??,"大于0;小于0;等于0;文字")
依照那四個型態各自設定要顯示的格式,
;;; 中間省略, 表示顯示空白
TEXT(??,"0;;;") > 除了大于0外, 其餘都顯示空白

用MID, 是可能加總後只有"箱", 或只有"瓶", 會留前後多餘的"+"

作者: PJChen 時間: 2019-7-1 23:01

回復 19# 准提部林

謝謝准大,
這樣解說一下,總比瞎子摸象來得強...

作者: 劉大胃 時間: 2019-7-2 08:40

Hello, 准大

了解! 謝謝你的教導.

作者: PJChen 時間: 2019-7-2 12:20

回復 18# 劉大胃

很感謝你的提問，讓我也有收獲，也謝謝你的公式，我會好好再研究，感謝....

作者: PJChen 時間: 2020-8-3 19:33

回復 11# 准提部林

請問准大,
我有另一個的含文字加總 [attach]32364[/attach]
B4:AF10為加班時數,因是公用表格,各自填入自己的加班時數,設定好的格式常會被變動
依AJ1指定的值,加總A4:A10=AJ1且B4:AF10,所有含"加"的數值
要如何使用函數加總?
有些是文字"加",有些是格式化的"加",但只要看到"加",就視為要加總的對象
EX: AJ1=P3時,含"加"的數值為20

作者: 准提部林 時間: 2020-8-4 12:11

回復 23# PJChen

用vba自訂函數,
P3應為21(其中有格式的加1)
[attach]32366[/attach]

作者: PJChen 時間: 2020-8-5 20:29

回復 24# 准提部林

准大好,
因為公用表格不能使用VBA程式,所以我改了另一作法,
將檔名設定在"加班計算.xlsx"的"加班"工作表O1儲存格中,
所有需要統計的資料都在"加班"工作表內,因為使用了跨檔案,
請問程式要如何修改可以套用在D欄位中?
另外來源檔"2020年假表"常有人任意變動格式,
不過人員一律放在A欄,日期欄則一律是第二列,一直到每月的日期結束
[attach]32375[/attach]

作者: 准提部林 時間: 2020-8-6 11:10

回復 25# PJChen

我的版本檔案開不了,
讓別人來做吧~~

作者: jcchiang 時間: 2020-8-6 12:41

回復 26# 准提部林

直接用准大提供的Function程式
D欄公式=加班數(INDIRECT("'["&fl&"]"&$A2&"'!"&"A4:AF12"),J$1)下拉

作者: PJChen 時間: 2020-8-6 20:06

回復 27# jcchiang

可以了！感謝

作者: Andy2483 時間: 2023-3-9 14:11

回復 24# 准提部林

謝謝前輩
後學學習到 Range.Text 屬性與區域存格相對位置核取...等
心得註解如下,請前輩再指導

執行結果:
[attach]35911[/attach]

Function 加班數(Rng As Range, S1$) As Long
'↑加班數()自訂函數,Rng變數是儲存格變數,S1是字串變數,函數回傳值是長整數
Dim R&, S&, C%, V%, T$
'↑宣告變數:(R,S)是長整數變數,(C,V)是短整數變數,T是字串變數
If S1 = "" Then Exit Function
'↑如果S1這字串變數是空字元!,就結束自訂函數
For R = 1 To Rng.Rows.Count
'↑設順迴圈!R從1到 Rng變數的列數
If Rng(R, 1) <> S1 Then GoTo r01
'↑如果Rng變數裡的相對R迴圈列第1欄儲存格值不是 S1變數值
For C = 2 To Rng.Columns.Count
'↑設順迴圈!C從2到 Rng變數的欄數
T = Rng(R, C).Text
'↑令T這字串變數是 Rng變數裡的相對R迴圈列第C迴圈欄儲存格值
'.Text:傳回指定之物件的格式化文字
'https://learn.microsoft.com/zh-tw/office/vba/api/excel.range.text
V = InStr(T, "加")
'↑令V這短整數變數是 "加"字串在 T變數裡的字元位置
If V > 0 Then 加班數 = 加班數 + Val(Mid(T, V + 1))
'↑如果V變數 >0!就令加班數()回傳:
'加班數()+ 增量值
'增量值:T變數取第(V變數+1)字開始的右側全部字元,再轉化為數字值
Next C
r01: Next R
End Function

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)