excel 年月數相加

例子:

14/01(14年/1個月) + 05/11(5年/11個月) = 20年

請問像這樣的formula應該怎樣寫

更新:

14/01(14年/1個月) + 05/11(5年/11個月) = 20/00 (年)

再來一個例子:

05/02(5年/2個月) + 02/01(2年/1個月) = 07/03 (7年/3個月)

2 個解答

評分
  • 7 年前
    最愛解答

    不難~

    可以用乘除運算寫,但我想先問問你,你希望用什麼形式輸入資料?

    最容易的做法是分開四個格輸入 A年,B月,C年,D月。

    然後用兩格作輸出,一格輸出 年的結果(X),一格輸出 月的結果(Y)。

    X = A + C + INT((B+D)/12)

    Y = MOD(B+D,12)

    這樣可以嗎?

    如果可以我再作答。

    2014-01-08 16:21:29 補充:

    可以,但你確定你輸入的格式必定是「ab/xy」這個五字模式吧?

    2014-01-08 16:38:07 補充:

    剛剛測試過,希望幫到你:

    先把 A1, B1 變成文字格式。

    (否則你輸入後會自動變日期,並非你想要的。)

    在C1輸入

    =RIGHT("0"&TEXT(VALUE(MID(A1,1,2))+VALUE(MID(B1,1,2))+INT((VALUE(MID(A1,4,2))+VALUE(MID(B1,4,2)))/12),0),2)&"/"&RIGHT("0"&TEXT(MOD(VALUE(MID(A1,4,2))+VALUE(MID(B1,4,2)),12),0),2)

    圖片參考:http://imgcld.yimg.com/8/n/HA00430218/o/2014010816...

    2014-01-08 17:29:42 補充:

    努力!

    不用客氣~

    ☆ヾ(◕‿◕)ノ

    2014-01-08 17:46:26 補充:

    如果像下圖這樣累積加起來可以嗎?

    http://s16.postimg.org/6nubfyhhf/20140108_hk_excel...

    2014-01-08 17:47:37 補充:

    上圖中,A1至A9均為文字格式。

    B1 輸入=A1

    B2 輸入=IF(A2="-",B1,RIGHT("0"&TEXT(VALUE(MID(B1,1,2))+VALUE(MID(A2,1,2))+INT((VALUE(MID(B1,4,2))+VALUE(MID(A2,4,2)))/12),0),2)&"/"&RIGHT("0"&TEXT(MOD(VALUE(MID(B1,4,2))+VALUE(MID(A2,4,2)),12),0),2))

    2014-01-08 17:48:03 補充:

    再把 B2 向下拉到 B9

    2014-01-09 10:31:45 補充:

    http://postimage.org/

    這裏有免費貼圖空間~

    [可能我今天要晚一點才能處理你的問題]

    努力!

    2014-01-09 23:50:07 補充:

    你是否確定只有六個columns?

    如果是則容易辦,因為可以 hard code 了計算六個 columns。

    2014-01-10 13:00:18 補充:

    無限 row 沒問題,只要你 column 數 fix 實(11, 12, 13都可,但要知數字),咁都可以 hard code。

    我 confirm 一次,你係sure column A 至 column I (共 9 columns)都會入 "ab/xy" 的格式 或 "-" 代表沒有,而你想在 column J 顯示左邊九格的相加結果。

    是否這樣?

    2014-01-10 14:46:32 補充:

    好,剛剛測試成功了~

    記緊 A1 至 G1 要是文字格式,且在 H1 輸入以下:

    〔由於函數太長,我分三部份貼出,建議你先用 記事本 Notepad 把字串放在一起,再一次過貼在 Excel 的H1格。〕

    =RIGHT("0"&TEXT((IFERROR(VALUE(MID(A1,1,2)),0)+IFERROR(VALUE(MID(B1,1,2)),0)+IFERROR(VALUE(MID(C1,1,2)),0)+IFERROR(VALUE(MID(D1,1,2)),0)+IFERROR(VALUE(MID(E1,1,2)),0)

    2014-01-10 14:46:59 補充:

    +IFERROR(VALUE(MID(F1,1,2)),0)+IFERROR(VALUE(MID(G1,1,2)),0)+INT((IFERROR(VALUE(MID(A1,4,2)),0)+IFERROR(VALUE(MID(B1,4,2)),0)+IFERROR(VALUE(MID(C1,4,2)),0)+IFERROR(VALUE(MID(D1,4,2)),0)+IFERROR(VALUE(MID(E1,4,2)),0)+IFERROR(VALUE(MID(F1,4,2)),0)+IFERROR(VALUE(MID(G1,4,2)),0))/12)),0),2)

    2014-01-10 14:47:08 補充:

    &"/"&RIGHT("0"&TEXT(MOD((IFERROR(VALUE(MID(A1,4,2)),0)+IFERROR(VALUE(MID(B1,4,2)),0)+IFERROR(VALUE(MID(C1,4,2)),0)+IFERROR(VALUE(MID(D1,4,2)),0)+IFERROR(VALUE(MID(E1,4,2)),0)+IFERROR(VALUE(MID(F1,4,2)),0)+IFERROR(VALUE(MID(G1,4,2)),0)),12),0),2)

    2014-01-13 15:54:38 補充:

    應該無問題,請看看:

    http://s17.postimg.org/ynb9he3hq/20140113_hk_excel...

    2014-01-13 16:05:00 補充:

    哦~

    我再幫你測試過,我明白發生什麼事了~

    幸好你有 cap 圖給我看,否則想很久也不會知道發生何事。

    問題是:你用的是 Excel 2003

    我用 Excel 2010,我嘗試把檔案存成 Excel 2003 時,它就有通知我問題所在了。

    因為我們用了太多層的函數,因為把七個格加起來,舊版Excel 不支援一次過加那麼多層,所以出了 #NAME? 這個 error。

    有辦法解決。放心。

    2014-01-13 20:40:38 補充:

    Lillian,測試成功了,請看看下圖:

    http://postimg.org/image/wcuss001z/

    我需要你在 I 行輸入一個暫存的公式,用以先加上部份數據~

    再在 H 行輸入最後的公式,得到 A 至 G 行共七項數據的總和。

    2014-01-13 20:43:22 補充:

    請在 I1 格輸入〔由於太長,在此分三次貼〕:

    =RIGHT("0"&TEXT((IF(ISERROR(VALUE(MID(A1,1,2))),0,VALUE(MID(A1,1,2)))+IF(ISERROR(VALUE(MID(B1,1,2))),0,VALUE(MID(B1,1,2)))+IF(ISERROR(VALUE(MID(C1,1,2))),0,VALUE(MID(C1,1,2)))+IF(ISERROR(VALUE(MID(D1,1,2))),0,VALUE(MID(D1,1,2)))

    2014-01-13 20:43:29 補充:

    +INT((IF(ISERROR(VALUE(MID(A1,4,2))),0,VALUE(MID(A1,4,2)))+IF(ISERROR(VALUE(MID(B1,4,2))),0,VALUE(MID(B1,4,2)))+IF(ISERROR(VALUE(MID(C1,4,2))),0,VALUE(MID(C1,4,2)))+IF(ISERROR(VALUE(MID(D1,4,2))),0,VALUE(MID(D1,4,2))))/12)),0),2)&"/"&

    2014-01-13 20:43:36 補充:

    RIGHT("0"&TEXT(MOD((IF(ISERROR(VALUE(MID(A1,4,2))),0,VALUE(MID(A1,4,2)))+IF(ISERROR(VALUE(MID(B1,4,2))),0,VALUE(MID(B1,4,2)))+IF(ISERROR(VALUE(MID(C1,4,2))),0,VALUE(MID(C1,4,2)))+IF(ISERROR(VALUE(MID(D1,4,2))),0,VALUE(MID(D1,4,2)))),12),0),2)

    2014-01-13 20:44:16 補充:

    請在 H1 格輸入〔由於太長,在此也分三次貼〕:

    =RIGHT("0"&TEXT((IF(ISERROR(VALUE(MID(I1,1,2))),0,VALUE(MID(I1,1,2)))+IF(ISERROR(VALUE(MID(E1,1,2))),0,VALUE(MID(E1,1,2)))+IF(ISERROR(VALUE(MID(F1,1,2))),0,VALUE(MID(F1,1,2)))+IF(ISERROR(VALUE(MID(G1,1,2))),0,VALUE(MID(G1,1,2)))

    2014-01-13 20:44:23 補充:

    +INT((IF(ISERROR(VALUE(MID(I1,4,2))),0,VALUE(MID(I1,4,2)))+IF(ISERROR(VALUE(MID(E1,4,2))),0,VALUE(MID(E1,4,2)))+IF(ISERROR(VALUE(MID(F1,4,2))),0,VALUE(MID(F1,4,2)))+IF(ISERROR(VALUE(MID(G1,4,2))),0,VALUE(MID(G1,4,2))))/12)),0),2)&"/"&

    2014-01-13 20:44:31 補充:

    RIGHT("0"&TEXT(MOD((IF(ISERROR(VALUE(MID(I1,4,2))),0,VALUE(MID(I1,4,2)))+IF(ISERROR(VALUE(MID(E1,4,2))),0,VALUE(MID(E1,4,2)))+IF(ISERROR(VALUE(MID(F1,4,2))),0,VALUE(MID(F1,4,2)))+IF(ISERROR(VALUE(MID(G1,4,2))),0,VALUE(MID(G1,4,2)))),12),0),2)

    2014-01-13 20:45:00 補充:

    這次應該可以了吧?

    我等你的好消息~!

    ☆ヾ(◕‿◕)ノ

  • 7 年前

    不好意思, 不可以用四個格分開輸入, 一定要以以下格式

    A格:14/01 + B格:05/11 = C格:20/00

    不知道可不可以?

    謝謝指教!

    2014-01-08 17:18:16 補充:

    可以了, 真的太感謝批卷貓, 太開心了, 謝謝!

    2014-01-08 17:33:18 補充:

    批卷貓,對不起,還有一個問題,如果不止是兩個格數相加,而是有七個以上的格數相加,又當如何計算了,例如:

    A1: 05/01

    B1: - (代表無數字)

    C1: 01/02

    D1: -

    E1: 03/06

    F1 : 01/07

    G1: -

    H1: -

    I1: 08/09

    將A1至I1相加結果等於:12/01

    麻煩你了, 謝謝!

    2014-01-09 09:34:00 補充:

    對不起, 可以做個效果像下面那樣嗎?謝謝您!!

    Item 1Item 2Item 3Item 4Item 5Item 6"Sum

    (Item 1-6)"

    -03/05---13/0817/01

    ---05/08-05/0511/01

    -02/00---05/0907/09

    ---07/09-05/0813/05

    2014-01-09 09:38:16 補充:

    不知你看得明白嗎?怎樣可以在這裏加入圖片?讓你可以看明白點

    2014-01-10 09:05:13 補充:

    不是只有六個columns, 我只是舉例子, columns 應該有7-9個, 現在還沒確定, 至於rows是無限的, 是不是很難做到

    2014-01-10 11:48:45 補充:

    有7個columns, 無限rows

    2014-01-10 14:19:07 補充:

    Confirm 係column A至G (共7個columns) 輸入"ab/xy" or "-" , 在H column 合計A-G相加結果, 謝謝

    2014-01-13 10:05:24 補充:

    不知道為什麼不可以, 如下所示:

    http://postimg.org/image/edgio2uqn/

    2014-01-13 16:35:09 補充:

    喔, 太厲害了, 麻煩你了

    2014-01-14 15:33:05 補充:

    批卷貓,問題解決了,多謝您。

    下次還要向您賜教^^

還有問題嗎?立即提問即可得到解答。