請問有沒有人知道如果在excel的A欄中輸入一些只得年份、一些就有年月日,我想在B欄中會自動計算年齡,請問有什麼公式可以做到,如果沒有的話,請問有沒有別的方法可以解決這個問題呢? 謝謝各方好友!!(問題如下)
A .................. ......B
年份.................年齡
1922
1/1/1945
8/7/1950
1930
1899
Excel 問問
2007-01-04 1:59 am
回答 (4)
2007-01-04 6:58 am
✔ 最佳答案
建議應統一A欄的日期格式, 全部是年份 或 全部是日/月/年(祇有年份的便加上1月1日), 否則B欄便要分兩種不同的公式輸入, 影響公式下拉, 及容易出錯。如A1是只得年份, 公式非常簡單
在B1輸入公式:=YEAR(TODAY())-A1
如A1是日/月/年, 公式頗長, 如下
在B1輸入公式:=YEAR(TODAY())-YEAR(A1) -IF(OR(MONTH(TODAY())
2007-01-03 23:25:38 補充：
由於系統問題, 小于的符號無法載入, 現將最後一行補充, 如見到小于便要改用符號在B1輸入公式:=YEAR(TODAY())-YEAR(A1) -IF(OR(MONTH(TODAY())小于MONTH(A1),AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())小于DAY(A1))),1,0)此公式是計算足歲年齡, 即差1天是20歲, 都作19歲計
參考: 自己
2007-01-05 8:07 am
建議將A欄的日期格式統一,
如必須只顯示出年份, 可於該儲存格「格式」->「儲存格」->「數值」自訂為yyyy
我的方法要加用一欄, 假設為C欄, 但就不需複雜的計算,
C欄輸入想年齡計算到何時的日期,
如想每次都UPDATE到今天的歲數, 就於C欄輸入: =TODAY()
這樣就會每次自動讀取當天的日子, 無需人手改動
或是輸入固定的日期, 例如某個截止日期: 2006/10/1
然後 B欄 = C欄 - A欄 ( 例B1: =C1-A1 ) = 2007/1/4-1945/1/1
這時會見到B1的顯示會是 1962/1/2
只要將B欄的「格式」->「儲存格」->「數值」自訂為yy
便可看到顯示變成62
就會知道 1945/1/1 出生的人, 於2007/1/4時是62歲!
如必須只顯示出年份, 可於該儲存格「格式」->「儲存格」->「數值」自訂為yyyy
我的方法要加用一欄, 假設為C欄, 但就不需複雜的計算,
C欄輸入想年齡計算到何時的日期,
如想每次都UPDATE到今天的歲數, 就於C欄輸入: =TODAY()
這樣就會每次自動讀取當天的日子, 無需人手改動
或是輸入固定的日期, 例如某個截止日期: 2006/10/1
然後 B欄 = C欄 - A欄 ( 例B1: =C1-A1 ) = 2007/1/4-1945/1/1
這時會見到B1的顯示會是 1962/1/2
只要將B欄的「格式」->「儲存格」->「數值」自訂為yy
便可看到顯示變成62
就會知道 1945/1/1 出生的人, 於2007/1/4時是62歲!
2007-01-05 12:36 am
首先，要說的是 Excel 本身不支援 1900年1月1日以前的日期的運算。
所以，若你的 A 欄內真的有 1899 ，那麼你就要下載一些 Add-in 或用更複雜的公式去克服 Excel 這先天的問題。不過，因這個問題很普遍，你只要到 Google 搜尋一下便會找到千百個解決方法。
好了，回到你的問題。
先說容易的部分。
對於有齊年月日的 Cell ，計年齡很簡單，就是用 YEAR() 這個 Function 去取得 Cell 裏的年，然後與今年的年份相減，亦即是 B 欄要輸入以下公式：
=YEAR(TODAY())-YEAR(A1)
難的部分是只有年份沒有月日的 Cells 。
要明白以下步驟，你先要知道在 Excel 內部，日期是用一個正數循序序列值 (sequential serial number) 表達的，而 1900 年1月1日就是用 1 代表。1/1/1900 = 1, 2/1/1900 = 2 ，如此類推。這個也就是為何 Excel 只支援最早 1900年1月1日的原因。
所以 1922 在 Excel 來說不是 1922 年，而是 1900年1月1日之後的 1922 日，亦即是 1905年4月5日。
要解決這個問題，你就要將 1922 正確地變成 Excel 所用的循序序列值。方法是用 DATE() 這個 Function 。 DATE(1922, 1, 1) 會回給你代表 1922年1月1日的循序序列值。有了循序序列值，你就可以從中抽取年份，然後計算年齡。所需公式如下：
=YEAR(TODAY())-YEAR(DATE(A1,1,1))
原來的 YEAR(A1) 變成 YEAR(DATE(A1,1,1)) 了。
最後一步就是要想辦法令 Excel 分辨有齊年月日的 Cell 及只有年的 Cell 。
因為有齊年月日的 Cell 用的 Excel Format 名稱是 D1, D2, D3,..., D9 中其中一個。
而只有年的 Cell 用的則是 G (General) Format 。
所以你可以用一個叫 CELL() 的 Function 去查一下某一個 Cell 是用甚麼 Format ，公式是：
=CELL("format", A1)
這公式會回給你 D1, D2,..., D9, 或 G 。
然後，你可以用 LEFT() 這個 Function 去抽取最左邊的那個字母，即「D」或「G」來做判斷。
最後，用 IF() 來分開兩種不同情況所對需的不同公式就大功告功：
=IF(LEFT(CELL(㊣format㊣,A1))=㊣G㊣, YEAR(TODAY())-YEAR(DATE(A1,1,1)), YEAR(TODAY())-YEAR(A1))
由於 Yahoo! 限制編碼，英文的雙引號若是一對的話便無法正常顯示，而上述公式又一定要用雙引號，所以請你將上面公式裏的 ㊣ 改成 "
所以，若你的 A 欄內真的有 1899 ，那麼你就要下載一些 Add-in 或用更複雜的公式去克服 Excel 這先天的問題。不過，因這個問題很普遍，你只要到 Google 搜尋一下便會找到千百個解決方法。
好了，回到你的問題。
先說容易的部分。
對於有齊年月日的 Cell ，計年齡很簡單，就是用 YEAR() 這個 Function 去取得 Cell 裏的年，然後與今年的年份相減，亦即是 B 欄要輸入以下公式：
=YEAR(TODAY())-YEAR(A1)
難的部分是只有年份沒有月日的 Cells 。
要明白以下步驟，你先要知道在 Excel 內部，日期是用一個正數循序序列值 (sequential serial number) 表達的，而 1900 年1月1日就是用 1 代表。1/1/1900 = 1, 2/1/1900 = 2 ，如此類推。這個也就是為何 Excel 只支援最早 1900年1月1日的原因。
所以 1922 在 Excel 來說不是 1922 年，而是 1900年1月1日之後的 1922 日，亦即是 1905年4月5日。
要解決這個問題，你就要將 1922 正確地變成 Excel 所用的循序序列值。方法是用 DATE() 這個 Function 。 DATE(1922, 1, 1) 會回給你代表 1922年1月1日的循序序列值。有了循序序列值，你就可以從中抽取年份，然後計算年齡。所需公式如下：
=YEAR(TODAY())-YEAR(DATE(A1,1,1))
原來的 YEAR(A1) 變成 YEAR(DATE(A1,1,1)) 了。
最後一步就是要想辦法令 Excel 分辨有齊年月日的 Cell 及只有年的 Cell 。
因為有齊年月日的 Cell 用的 Excel Format 名稱是 D1, D2, D3,..., D9 中其中一個。
而只有年的 Cell 用的則是 G (General) Format 。
所以你可以用一個叫 CELL() 的 Function 去查一下某一個 Cell 是用甚麼 Format ，公式是：
=CELL("format", A1)
這公式會回給你 D1, D2,..., D9, 或 G 。
然後，你可以用 LEFT() 這個 Function 去抽取最左邊的那個字母，即「D」或「G」來做判斷。
最後，用 IF() 來分開兩種不同情況所對需的不同公式就大功告功：
=IF(LEFT(CELL(㊣format㊣,A1))=㊣G㊣, YEAR(TODAY())-YEAR(DATE(A1,1,1)), YEAR(TODAY())-YEAR(A1))
由於 Yahoo! 限制編碼，英文的雙引號若是一對的話便無法正常顯示，而上述公式又一定要用雙引號，所以請你將上面公式裏的 ㊣ 改成 "
2007-01-04 2:18 am
you can simply add Jan 01 as the date if you only know the year.
收錄日期: 2021-04-12 22:38:30
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20070103000051KK03035