Excel ไม่มีฟังก์ชันเฉพาะสำหรับการคำนวณอายุ แต่มีหลายวิธีที่เราสามารถใช้วันเกิดของใครบางคนเพื่อคำนวณอายุของบุคคลนั้นได้ เราจะไม่เพียงแค่เรียนรู้วิธีคำนวณอายุของใครบางคนเป็นปีเท่านั้น เรายังต้มอายุของพวกเขาให้เหลือเป็นเดือนและเป็นวันอีกด้วย
มีหลายสูตร รวมทั้งสูตรต่างๆ ที่เราสามารถใช้คำนวณอายุใน excel ได้ เราจะพูดถึงแต่ละสูตรทีละสูตร เพื่อไม่ให้เป็นการเสียเวลา เรามาเริ่มกันเลย
การอ่านที่แนะนำ:วิธีคำนวณปีบริการใน Excel
สารบัญ
การคำนวณอายุใน Excel ด้วยฟังก์ชัน DATEDIF
เดอะฟังก์ชัน DATEDIFทำในสิ่งที่ชื่อแนะนำ โดยจะคำนวณ DATE+DIF (ซึ่ง DIF อาจถูกอนุมานเป็น DIFFERENCE) และให้ผลลัพธ์ในหน่วยที่เราต้องการ ตัวอย่างเช่น สมมติว่า DOB ของ David คือ 10/01/1990
เราต้องการทราบว่าวันนี้เดวิดอายุเท่าไหร่
ดังนั้น เราจะใช้สูตรต่อไปนี้เพื่อคำนวณอายุของ David:
=วันที่(A2,วันนี้()"ย")&"ปี)"// อายุปัจจุบันเป็นปี
=วันที่(A2,วันนี้()"ม")&"เดือน"// อายุปัจจุบันเป็นเดือน
=วันที่(A2,วันนี้()"ง")&"วัน"// อายุปัจจุบันเป็นวัน
อาร์กิวเมนต์แรกคือ DOB ของ David อาร์กิวเมนต์ที่สองคือวันที่ของวันนี้ และ "y" บอกสูตรว่าเราต้องการผลลัพธ์เป็นปี ในที่สุด,เราได้เชื่อมต่อข้อความ"ปี" ซึ่งแสดงเป็นตัวระบุอายุ ดังนั้น ณ วันที่เขียน (24 มิ.ย. 2021) Excel บอกเราว่า David อายุ 30 ปี
Excel จะลบเลขลำดับของวันที่ที่ป้อนในอาร์กิวเมนต์แรกออกจากเลขลำดับของวันที่ที่ป้อนในอาร์กิวเมนต์ที่สองเพื่อคำนวณความแตกต่างในรูปของวัน จากนั้นจึงแปลงเป็นปีและตัดค่าทศนิยมออกเพื่อให้ผลลัพธ์สุดท้ายแก่เรา
ในแถวถัดไป เราเปลี่ยน "y" เป็น "m" และตัวระบุจาก "ปี" เป็น "เดือน" สิ่งนี้เปลี่ยนสูตรเพื่อให้ตอนนี้เราอายุของดาวิดเป็นจำนวนเดือน ซึ่งก็คือ 368 เดือน
ในการคำนวณจำนวนเดือน จะทำเช่นเดียวกัน ยกเว้นว่าวันจะถูกแปลงเป็นเดือนแทนที่จะเป็นปี เราสามารถก้าวไปอีกขั้นและใช้ "d" ในอาร์กิวเมนต์สุดท้ายเพื่อคำนวณอายุของ David เป็นจำนวนวัน ในกรณีนี้ เราทำในแถวที่สาม ซึ่งผลลัพธ์สุดท้ายของเรามาถึง 11,210 วัน
การคำนวณอายุตามวันที่กำหนดในอดีต
นอกจากนี้ เรายังสามารถปรับแต่งฟังก์ชัน DATEDIF เล็กน้อยและคำนวณอายุของ David ณ วันที่ใด ๆ ก็ได้ สำหรับสิ่งนี้ เราจะใช้ฟังก์ชัน DATEแทนฟังก์ชัน TODAY และซ้อนไว้ในฟังก์ชัน DATEDIFend_dateการโต้แย้ง.
สมมติว่าเราต้องการคำนวณอายุของ David ณ วันคริสต์มาสปี 2018
เราจะใช้สูตรต่อไปนี้:
=วันที่(A2,วันที่(2561,12,25),"ย")&"ปี)"// อายุที่ผ่านมาในปี
=วันที่(A2,วันที่(2561,12,25),"ม")&"เดือน"// อายุที่ผ่านมาในเดือน
=วันที่(A2,วันที่(2561,12,25),"ง")&"วัน"// อายุที่ผ่านไปเป็นวัน
สูตรนี้ทำงานเหมือนกับสูตรก่อนหน้าทุกประการ อย่างที่คุณเห็น ข้อแตกต่างเพียงอย่างเดียวคือในอาร์กิวเมนต์ที่สองซึ่งเราได้ระบุวันที่ที่ระบุแทนการใช้ฟังก์ชันทูเดย์.
แน่นอนว่าผลลัพธ์คือ 28 ปี สิ่งนี้สมเหตุสมผลเพราะวันที่ที่เราป้อนนั้นผ่านมาประมาณ 2.5 ปี เนื่องจากเอาต์พุตไม่สนใจค่าหลังจุดทศนิยม เราจึงได้เอาต์พุตเป็น 28 ปี (เช่น 30 – 2) ปี ในแถวถัดไป เราจะได้ 338 เดือนเป็นผลลัพธ์เมื่อเราป้อน "m" ในอาร์กิวเมนต์สุดท้าย และ 10,298 วันเป็นเอาต์พุตเมื่อเราป้อน "d" ในอาร์กิวเมนต์สุดท้าย
การคำนวณอายุ ณ วันที่กำหนดในอนาคต
แบบฝึกหัดนี้เป็นแรงบันดาลใจให้กับความอยากรู้อยากเห็นของเดวิด เขาหวังว่าจะยุ่งกับการวางแผนทางการเงิน เขาตั้งเป้าหมายทางการเงินสองสามข้อสำหรับตัวเองและปรารถนาที่จะคำนวณอายุของเขาในอนาคตเพื่อคำนวณความสามารถในการเสียภาษีและระยะเวลาครบกำหนดสำหรับการลงทุน
ไม่ต้องกังวล มาช่วย David หาอายุของเขาโดยใช้สูตรต่อไปนี้:
=วันที่(A2,วันที่(2045,12,25),"ย")&"ปี)"// อายุในอนาคตเป็นปี
=วันที่(A2,วันที่(2045,12,25),"ม")&"เดือน"// อายุในอนาคตเป็นเดือน
=วันที่(A2,วันที่(2045,12,25),"ง")&"วัน"// อายุในอนาคตเป็นวัน
สูตรนี้จะช่วยให้เราคำนวณอายุของ David ในรูปแบบปีในวันคริสต์มาสของปี 2045 ไม่มีการเปลี่ยนแปลงในสูตร ยกเว้นปีในฟังก์ชัน DATE โดยปกติแล้ว สูตรจะส่งกลับอายุของ David ณ วันที่ระบุเป็น 55 ปี การเปลี่ยนอาร์กิวเมนต์สุดท้ายเป็น "m" ทำให้เรามีเวลา 662 เดือน และการเปลี่ยนอาร์กิวเมนต์สุดท้ายเป็น "d" จะทำให้เรามีเวลา 20,160 วัน
การคำนวณอายุเป็นปีด้วยฟังก์ชัน YEARFRAC
ก่อนที่เราจะเริ่มดำเนินการในเรื่องนี้ โปรดทราบว่าโดยทั่วไปแล้ว DATTIF เป็นวิธีที่ดีกว่าในการคำนวณอายุ เป็นไปได้ที่จะคำนวณอายุโดยใช้ฟังก์ชัน YEARFRACเช่นกัน แต่ช่วยให้เราสามารถคำนวณอายุได้ในรูปของปีเท่านั้น
ในการคำนวณอายุของ David โดยใช้ฟังก์ชัน YEARFRAC เราจะใช้สูตรต่อไปนี้:
=INT(YEARFRAC(A2,วันนี้()))
เรามาเริ่มกันที่สาเหตุที่เรามีฟังก์ชัน INT ในสูตร ฟังก์ชัน INT มีจุดประสงค์เพียงอย่างเดียว – ลบค่าหลังจุดทศนิยม เมื่อฟังก์ชัน YEARFRAC ส่งกลับ เช่น 10.3 ปี และส่งต่อไปยังฟังก์ชัน INT ก็จะแปลงเป็น 10 ปี
ต่อไป เรามาดูกันว่าฟังก์ชัน YEAFRAC ทำหน้าที่อะไร อาร์กิวเมนต์แรกในฟังก์ชันคือวันที่เริ่มต้นอาร์กิวเมนต์และอาร์กิวเมนต์ที่สองคือend_dateการโต้แย้ง. มันคล้ายกับที่เราเห็นด้วยฟังก์ชัน DATEDIF ยกเว้นว่าไม่มีอาร์กิวเมนต์ที่สามและเอาต์พุตของฟังก์ชัน YEARFRACทำมีค่าทศนิยม
ในตัวอย่างของเรา ฟังก์ชัน YEARFRAC ส่งกลับ 30.69 ปี เอาต์พุตนี้ส่งต่อไปยังฟังก์ชัน INT ซึ่งให้เอาต์พุตสุดท้ายของเราเป็นเวลา 30 ปี
การอ่านที่แนะนำ:วิธีเพิ่มปีเป็นวันที่ใน Excel
การคำนวณอายุเป็นปี เดือน และวัน
สูตรก่อนหน้านี้ช่วยดาวิดคำนวณอายุเป็นปี เดือน และวัน อย่างไรก็ตามเขาต้องใช้สูตรใดในการคำนวณของเขาที่แน่นอนอายุ? ตัวอย่างเช่น ในรูปแบบนี้ – 20 ปี 6 เดือน 15 วัน
เราทราบดีว่าเมื่อเราต้องการเชื่อมต่อผลลัพธ์ของสองสูตร เราจะใช้การต่อข้อมูล (&) ดังนั้น เราจะใช้สูตรต่อไปนี้:
=วันที่(A2,วันนี้()"ย")&"ปี)"& วันที่(A2,วันนี้()"อืม")&"เดือน"& วันที่(A2,วันนี้()"นพ")&"วัน"
เหตุใดจึงต้องใช้ "ym" และ "md" แทน "m" และ "d" เหมือนที่เราเคยทำมาก่อน "ym" สั่งให้สูตรคำนวณความแตกต่างเท่านั้นระหว่างเดือนที่ป้อนในอาร์กิวเมนต์ที่หนึ่งและสอง ในทำนองเดียวกัน "md" สั่งให้สูตรคำนวณความแตกต่างเท่านั้นในวันที่ป้อนในอาร์กิวเมนต์ที่หนึ่งและสอง
ในกรณีนี้ นี่คือความแตกต่างที่เราต้องการ ลองคิดดู—การใช้ "m" และ "d" จะทำให้เรามีเดือน/วันทั้งหมดระหว่าง DOB ของ David ถึงวันนี้ และนั่นไม่ใช่สิ่งที่เราต้องการ
ผลลัพธ์ของเราคือ "30 ปี 8 เดือน 9 วัน" เราสามารถจัดรูปแบบสตริงข้อความในสูตรเพื่อเพิ่มลูกน้ำหรือช่องว่างได้ตามต้องการ
การคำนวณเมื่อบุคคลจะมีอายุ X ปี
ความอยากรู้อยากเห็นของเดวิดกำลังทำให้เขาคลั่งไคล้ โชคดีสำหรับเรา เขาต้องการเรียนรู้ทุกอย่างเกี่ยวกับการคำนวณอายุใน Excel ตอนนี้เขาต้องการที่จะรู้ว่าเมื่อไหร่ที่เขาจะอายุ 35 ปี 45 ปี และ 55 ปี เพื่อที่เขาจะได้วางแผนชีวิตได้อย่างเหมาะสม
ฉันบอกว่ามีพลังมากขึ้นสำหรับเขา มาช่วยเขาด้วยสูตรต่อไปของเรา:
=วันที่(ปี(A2)+35,เดือน(A2),วัน(A2))//เมื่อเดวิดอายุ 35 ปี
=วันที่(ปี(A3)+45,เดือน(A2),วัน(A2))//เมื่อเดวิดอายุ 45 ปี
=วันที่(ปี(A2)+55,เดือน(A2),วัน(A2))//เมื่อเดวิดอายุครบ 55 ปี
นี่เป็นสูตรที่ค่อนข้างตรงไปตรงมา ซึ่งเราใช้ฟังก์ชัน DATE เพื่อเพิ่ม 35 ปีให้กับ DOB ของ David อาร์กิวเมนต์ DATE เลือกองค์ประกอบปี เดือน และวัน แล้วคอมไพล์ให้เป็นวันที่ที่ถูกต้องของ Excel
อันที่จริง ถ้าวันเดือนปีเกิดของ David แบ่งออกเป็นสามเซลล์ที่แตกต่างกัน โดยแต่ละเซลล์มีส่วนประกอบที่แตกต่างกันของ DOB (เช่น ปี เดือน และวัน) สูตรนี้ก็ยังใช้ได้อยู่ เราเพียงแค่ต้องอ้างอิงเซลล์ที่เกี่ยวข้องแทนเซลล์ A2
เมื่อสูตรเลือกองค์ประกอบปี เราก็เพียงเพิ่ม 35 เข้าไป ซึ่งให้ผลลัพธ์สุดท้ายแก่เรา ในตัวอย่างของเรา เดวิดอายุครบ 35 ปีในวันที่ 15 ตุลาคม 2025 อายุครบ 45 ปีในวันที่ 15 ตุลาคม 2035 และอายุครบ 55 ปีในวันที่ 15 ตุลาคม 2045
การคำนวณความแตกต่างของอายุระหว่างบุคคลสองคน
เดวิดไม่สามารถหยุดยั้งได้ แต่สัญญาว่านี่จะเป็นแบบฝึกหัดสุดท้ายของเขาสำหรับยุคคอมพิวเตอร์ เราอยู่บนเรือ คราวนี้ เดวิดต้องการดูว่าเขาจะคำนวณความแตกต่างระหว่างอายุของบุคคลสองคนได้อย่างไร เขามีรายการ DOB ที่เขาต้องการเปรียบเทียบอายุของเขาเอง
เราจะใช้สูตรต่อไปนี้:
=วันที่(เอทู,บีทู,"ย")&"ปี)"
เราทราบดีว่าเมื่อใดก็ตามที่เราต้องการคำนวณความแตกต่างของวันที่ เราจะตั้งค่าเริ่มต้นเป็นฟังก์ชัน DATEDIF
อย่างไรก็ตาม มีปัญหาเล็กน้อยอย่างหนึ่ง เมื่อเดวิดต้องการเปรียบเทียบอายุของเขากับคนที่อายุน้อยกว่า การอ้างอิงเซลล์ของฟังก์ชัน DATEDIF จะต้องเปลี่ยน วันที่ในวันที่เริ่มต้นอาร์กิวเมนต์จะต้องเกิดขึ้นก่อนค่าในend_dateอาร์กิวเมนต์ มิฉะนั้น ฟังก์ชันจะส่งกลับข้อผิดพลาด #NUM (เหมือนที่เราเห็นในเซลล์ C5)
แก้ไขได้โดยอ้างอิงเซลล์ B2 ในอาร์กิวเมนต์แรกและ A2 ในอาร์กิวเมนต์ที่สอง อย่างไรก็ตาม หากคุณกำลังทำงานกับชุดข้อมูลขนาดใหญ่ คุณอาจต้องการทางเลือกอื่นที่สะดวกกว่า สูตรที่คุณสามารถลากลงมาในคอลัมน์ได้
เพื่อแก้ไขปัญหานี้ ให้ใช้ฟังก์ชันง่ายๆ สองฟังก์ชันและพีชคณิตธรรมดา เช่น:
=INT(เอบีเอส((A2-B2)/365))&"ปี)"
เริ่มจากสิ่งที่อยู่ในวงเล็บด้านในสุด (A2-B2) คือการลบสองวัน. Excel จะลบหมายเลขซีเรียลของแต่ละวันที่และผลลัพธ์จะเป็นความแตกต่างระหว่างวันที่เหล่านี้ในรูปของวัน ต่อไปเราก็หารมันด้วย 365 เพื่อแปลงเป็นปี
จากนั้นเรามีฟังก์ชั่น ABS ฟังก์ชัน ABS ช่วยให้มั่นใจว่าเรามีค่าเป็นบวกเสมอ ตัวอย่างเช่น ในแถวที่สอง วันที่ใน B3 อยู่หลังวันที่ใน A3 และนั่นทำให้เราได้จำนวนลบ ฟังก์ชัน ABS จะแปลงค่านี้เป็นจำนวนบวกและส่งต่อไปยังฟังก์ชัน INT
ฟังก์ชัน INT ดังที่เราได้กล่าวไว้ก่อนหน้านี้ ให้ค่าจำนวนเต็มแก่เรา (เช่น ลบค่าที่อยู่หลังจุดทศนิยม) สิ่งนี้ทำให้เรามีผลผลิตที่ดีและสะอาดเป็นเวลา 4 ปี 4 ปีและ 6 ปีตามลำดับ
สิ่งสำคัญที่ควรทราบที่นี่คือ คุณต้องซ้อนฟังก์ชัน ABS ไว้ในฟังก์ชัน INT เสมอ ไม่ใช่ในทางกลับกัน นี่เป็นเพราะฟังก์ชัน INT ปัดเศษจำนวนบวกให้เป็นศูนย์ และจำนวนลบห่างจาก 0 ตัวอย่างเช่น ในแถวที่สอง ถ้าเราซ้อนฟังก์ชัน INT ไว้ในฟังก์ชัน ABS เอาต์พุตของเราจะเปลี่ยนเป็น 5
นี่เป็นการสรุปความสนุกสนานในการคำนวณอายุของเรากับเดวิด เราใช้ฟังก์ชันต่างๆ ที่สามารถช่วยให้เราคำนวณอายุของคนๆ หนึ่ง หรือแม้แต่หาว่าเมื่อไหร่ที่คนๆ หนึ่งจะมีอายุ x จำนวนปี ในขณะที่คุณพยายามฝึกฝนเทคนิคเหล่านี้ให้เชี่ยวชาญ เราจะรวบรวมบทช่วยสอนอื่นให้คุณ จนกว่าจะถึงครั้งต่อไป.
