Google ชีตสามารถใช้ได้มากกว่าการสะสมข้อมูลและการจัดองค์กร คุณยังสามารถใช้เพื่อกำหนดเวลาปัจจุบันสร้างแผนภูมิและคำนวณอายุโดยใช้วันเกิด สิ่งหลังนี้ถูกค้นพบจากการใช้สูตรและฟังก์ชันที่สร้างไว้ใน Google ชีต
การกำหนดอายุจากวันเกิดใน Google ชีต
เมื่อใช้ Google ชีตคุณมีสองวิธีที่โดดเด่นในการกำหนดอายุจากวันเกิด มี DATEDIF ซึ่งเป็นตัวเลือกที่ยืดหยุ่นกว่าและ YEARFRAC ทางเลือกที่ง่ายกว่า ในตอนท้ายของบทความคุณควรสามารถระบุอายุของแต่ละบุคคลได้ไม่เพียง แต่ระบุอายุของบุคคลหลายกลุ่มพร้อมกัน
ฉันจะเริ่มต้นด้วยฟังก์ชัน DATEDIF
ฟังก์ชัน DATEDIF
ก่อนที่เราจะดำดิ่งลงไปในฟังก์ชันนี้เราจำเป็นต้องทราบว่ามันทำงานอย่างไร สิ่งนี้จะเรียนรู้ไวยากรณ์เพื่อใช้กับฟังก์ชัน DATEDIFแต่ละส่วนที่คุณได้พิมพ์ในฟังก์ชันจะเชื่อมโยงกับงานดูงานเหล่านี้ด้านล่าง:
ไวยากรณ์
= DATEDIF (start_date, end_date, unit)
- วันที่เริ่มต้น
- การคำนวณจะต้องเริ่มต้นด้วยวันเกิด
- end_date
- นี่จะเป็นวันที่สรุปการคำนวณ เมื่อกำหนดอายุปัจจุบันตัวเลขนี้น่าจะเป็นวันที่ของวันนี้
- หน่วย
- ตัวเลือกเอาต์พุตซึ่งประกอบด้วย: Y, M, D, YM, YD หรือ MD
- Y - จำนวนปีที่ผ่านไปทั้งหมดระหว่างวันที่เริ่มต้นและวันที่สิ้นสุดที่ป้อน
- YM - ตัว 'M' ย่อมาจากเดือน ผลลัพธ์นี้แสดงจำนวนเดือนหลังจากปีที่ผ่านไปอย่างสมบูรณ์สำหรับ 'Y' จำนวนจะไม่เกิน 11
- YD - ตัว 'D' ย่อมาจากวัน ผลลัพธ์นี้แสดงจำนวนวันหลังจากปีที่ผ่านไปอย่างสมบูรณ์สำหรับ 'Y' จำนวนจะไม่เกิน 364
- M - จำนวนเดือนที่ผ่านไปทั้งหมดระหว่างวันที่เริ่มต้นและวันที่สิ้นสุดที่ป้อน
- MD - เช่นเดียวกับหน่วยอื่น ๆ 'D' หมายถึงวัน ผลลัพธ์นี้แสดงจำนวนวันหลังจากเดือนที่ผ่านไปอย่างสมบูรณ์สำหรับ 'M' ต้องไม่เกิน 30
- D - จำนวนวันที่ผ่านไปทั้งหมดระหว่างวันที่เริ่มต้นและวันที่สิ้นสุดที่ป้อน
การคำนวณ
เมื่อคุณเข้าใจไวยากรณ์ที่จะใช้แล้วเราสามารถตั้งค่าสูตรได้ ตามที่ระบุไว้ก่อนหน้านี้ฟังก์ชัน DATEDIF เป็นตัวเลือกที่ยืดหยุ่นกว่าเมื่อกำหนดอายุจากวันเกิด ด้วยเหตุนี้คุณจึงสามารถคำนวณรายละเอียดอายุทั้งหมดในรูปแบบปีเดือนและวันได้
ในการเริ่มต้นเราต้องมีตัวอย่างวันที่เพื่อใช้ในเซลล์ ฉันตัดสินใจวางวันที่ 14/7/1972 เข้าไปในเซลล์ A1 . เราจะทำสูตรในเซลล์ทางด้านขวาของมัน B1 หากคุณต้องการทำตามเพื่อให้ได้มา
เราจะเริ่มต้นด้วยสูตรพื้นฐานที่สุดในการคำนวณอายุ หากคุณใช้ไวยากรณ์ด้านบนเพื่อค้นหาว่าอะไรคืออะไร A1 ในทางเทคนิคคือ วันที่เริ่มต้น , วันนี้ จะเป็นไฟล์ end_date และเราจะกำหนดอายุเป็นปีโดยใช้ ย . นั่นคือเหตุผลที่สูตรแรกที่ใช้จะมีลักษณะดังนี้:
= วันที่ถ้า (A1 วันนี้ (), Y)
คำแนะนำที่เป็นประโยชน์: คัดลอกและวางสูตรลงใน B2 โดยตรงแล้วกด Enter เพื่อรับผลลัพธ์ที่เหมาะสม
เมื่อทำถูกต้องตัวเลขที่ระบุอายุที่คำนวณได้จะอยู่ใน B1 เป็น ' 4 8'.
ลองใช้สูตรเดียวกันในครั้งนี้เราจะกำหนดอายุเป็นเดือนโดยใช้ ม แทน Y.
= วันที่ถ้า (A1 วันนี้ (), M)
รวมจะเป็น 559 เดือน อายุ 559 เดือน
อย่างไรก็ตามตัวเลขนี้ค่อนข้างไร้สาระและฉันคิดว่าเราสามารถลดมันลงได้โดยใช้ YM แทนที่ M.
= วันที่ถ้า (A1 วันนี้ () YM)
ผลลัพธ์ใหม่ควรเป็น 7 ซึ่งเป็นตัวเลขที่จัดการได้ง่ายกว่ามาก
เพื่อให้ละเอียดถี่ถ้วนเรามาดูกันว่าวันนี้จะเป็นอย่างไรเมื่อใช้ทั้ง YD และ MD
= วันที่ถ้า (A1 วันนี้ () ปปป.)
= วันที่ถ้า (A1 วันนี้ (), MD)
คราวนี้ผลลัพธ์ของ YD จะแสดงใน B1 และผลลัพธ์ของ MD จะอยู่ในเซลล์ B2
ถึงเวลาแล้วหรือยัง?
ต่อไปเราจะรวมสิ่งเหล่านี้เข้าด้วยกันเพื่อพยายามคำนวณรายละเอียดเพิ่มเติมให้กับตัวเอง สูตรอาจค่อนข้างยุ่งยากในการพิมพ์ดังนั้นเพียงแค่คัดลอกและวางสูตรที่ให้ไว้ในเซลล์ B1
สูตรที่จะใช้คือ:
= วันที่ถ้า (A1, วันนี้ (), Y) & ปี & วันที่ถ้า (A1, วันนี้ (), YM) & เดือน & & & วันที่ถ้า (A1, วันนี้ (), MD) & วัน
เครื่องหมายแอมเพอร์แซนด์ถูกใช้เพื่อรวมแต่ละสูตรเข้าด้วยกันเหมือนการเชื่อมโยง นี่เป็นสิ่งจำเป็นในการคำนวณแบบเต็ม Google Sheet ของคุณควรมีสูตรเดียวกับ:
การคำนวณอย่างละเอียดครบถ้วนทำให้เรามีเวลา 46 ปี 7 เดือนและ 26 วัน คุณยังสามารถใช้สูตรเดียวกันโดยใช้ฟังก์ชัน ArrayFormula ซึ่งหมายความว่าคุณสามารถคำนวณได้มากกว่าวันที่เดียว แต่มีวันที่หลายวันได้เช่นกัน
ฉันเลือกวันที่แบบสุ่มสองสามวันและเสียบเข้ากับเซลล์เพิ่มเติม A2-A5 . เลือกวันที่ของคุณเองและสนุกไปกับมัน ในการใช้ฟังก์ชัน ArrayFormula ให้คัดลอกและวางสิ่งต่อไปนี้ลงในเซลล์ B1:
= ArrayFormula (วันที่ถ้า (B2, C2 (), Y) & ปี & วันที่ถ้า (B2, C2 (), YM) & เดือน & & & amp; ลงวันที่ (B2, C2 (), MD) & วัน)
นี่คือผลลัพธ์ของฉัน:
สมมติว่าคุณต้องการแยกแต่ละส่วนของวันที่ออกเป็นคอลัมน์เล็ก ๆ ที่เป็นระเบียบเรียบร้อยเพื่อจัดระเบียบสาเก ใน Google ชีตให้เพิ่ม start_date (วันเกิด) ของคุณลงในคอลัมน์เดียวและ end_date ลงในคอลัมน์อื่น ฉันได้เลือกเซลล์ B2 สำหรับวันที่เริ่มต้นและ C2 สำหรับวันที่สิ้นสุดในตัวอย่างของฉัน วันที่ของฉันสัมพันธ์กับการเกิดและการเสียชีวิตล่าสุดของคนดังเบิร์ตเรย์โนลด์, จอห์นนี่แคชและลุคเพอร์รี
google chrome os สามารถดาวน์โหลดได้หรือไม่?
ดังที่แสดงคอลัมน์ A คือชื่อของแต่ละบุคคลคอลัมน์ B เป็นที่ตั้งของ start_date และ C end_date ตอนนี้ฉันจะเพิ่มอีกสี่คอลัมน์ทางด้านขวา หนึ่งสำหรับแต่ละ Y, YM, YD และการรวมกันของทั้งสาม ตอนนี้คุณต้องเพิ่มสูตรที่ถูกต้องในแต่ละแถวสำหรับคนดังแต่ละคน
เบิร์ตเรย์โนลด์ส:
= DATEDIF (B2, C2, Y) เปลี่ยน 'Y เป็นคอลัมน์ที่เกี่ยวข้องที่คุณกำลังพยายามคำนวณ
จอห์นนี่เงินสด:
= DATEDIF (B3, C3, Y) เปลี่ยน 'Y เป็นคอลัมน์ที่เกี่ยวข้องที่คุณกำลังพยายามคำนวณ
ลุคเพอร์รี่:
= DATEDIF (B4, C4, Y) เปลี่ยน 'Y เป็นคอลัมน์ที่เกี่ยวข้องที่คุณกำลังพยายามคำนวณ
ในการรับสูตรที่เข้าร่วมคุณจะต้องใช้ ArrayFormula เหมือนกับที่เราทำก่อนหน้านี้ในบทความ คุณสามารถเพิ่มคำเช่น ปี เพื่อระบุผลลัพธ์ของปีโดยวางไว้หลังสูตรและอยู่ระหว่างวงเล็บ
= ArrayFormula (วันที่ถ้า (B2, C2, Y) & ปี & วันที่ถ้า (B2, C2, YM) & เดือน & & & วันที่ถ้า (B2, C2, MD) & วัน)
สูตรข้างต้นเป็นสูตรสำหรับคนดัง อย่างไรก็ตามหากคุณต้องการเพียงแค่เคาะ em ทั้งหมดในคราวเดียวให้คัดลอกและวางสูตรต่อไปนี้ลงในเซลล์ G2:
= ArrayFormula (ลงวันที่ถ้า (B2: B4, C2: C4, Y) & ปีและวันที่ถ้า (B2: B4, C2: C4, YM) & เดือน & และวันที่ (B2: B4, C2: C4, MD) และวัน)
Google Sheet ของคุณควรมีลักษณะดังนี้:
สวยเรียบร้อยเหรอ? มันง่ายมากเมื่อใช้ฟังก์ชัน DATEDIF ตอนนี้เราสามารถเปลี่ยนไปใช้ฟังก์ชัน YEARFRAC
ฟังก์ชัน YEARFRAC
ฟังก์ชัน YEARFRAC เป็นฟังก์ชันที่เรียบง่ายสำหรับผลลัพธ์ที่เรียบง่าย ตรงประเด็นที่ให้ผลลัพธ์สุดท้ายโดยไม่ต้องมีเอาท์พุตพิเศษเพิ่มเติมทั้งหมดเป็นปีเดือนและวัน
นี่คือสูตรพื้นฐานที่ใช้ได้กับเซลล์เดียวเท่านั้น:
= int (YEARFRAC (A1 วันนี้ ()))
คุณจะต้องเพิ่มวันเกิดในเซลล์ A1 และวางสูตรลงใน B1 สำหรับผลลัพธ์ เราจะใช้วันเดือนปีเกิด 04/11/1983 :
ผลคืออายุ 35 ปี ง่ายเช่นเดียวกับเมื่อใช้ฟังก์ชัน DATEDIF สำหรับเซลล์เดียว จากตรงนี้เราสามารถเปลี่ยนไปใช้ YEARFRAC ภายใน ArrayFormula สูตรนี้มีประโยชน์มากขึ้นสำหรับคุณเมื่อคุณต้องคำนวณอายุของกลุ่มใหญ่เช่นนักเรียนคณาจารย์สมาชิกในทีม ฯลฯ
เราจะต้องเพิ่มคอลัมน์วันเดือนปีเกิดที่แตกต่างกัน ฉันได้เลือกคอลัมน์ B เป็น A จะใช้สำหรับชื่อบุคคล คอลัมน์ C จะถูกใช้สำหรับผลลัพธ์สุดท้าย
ในการเติมอายุในคอลัมน์ที่อยู่ติดกันเราจะต้องใช้สูตรต่อไปนี้:
= ArrayFormula (int (yearfrac (B2: B8, วันนี้ (), 1)))
วางสูตรด้านบนลงในเซลล์ C2 เพื่อให้ได้ผลลัพธ์
หากคุณต้องการดำเนินการกับทั้งคอลัมน์และไม่อยากกังวลกับการหาว่าสิ้นสุดที่ใดคุณสามารถเพิ่มรูปแบบเล็กน้อยลงในสูตรได้ ใช้ IF และ LEN ไปที่จุดเริ่มต้นของ ArrayFormula ดังนี้:
= ArrayFormula (ถ้า (len (B2: B), (int (yearfrac (B2: B, วันนี้ (), 1)))))
สิ่งนี้จะคำนวณผลลัพธ์ทั้งหมดภายในคอลัมน์นั้นโดยเริ่มจาก B2