การใช้สูตรวันที่ใน Excel โดยอัตโนมัติ วิธีค้นหาและเน้นค่าวันที่และรูปแบบที่ไม่ถูกต้องใน Excel

หากเซลล์ประกอบด้วย รูปแบบผิดค่า - สิ่งนี้สามารถนำไปสู่การคำนวณที่ผิดพลาดในสูตร ตัวอย่างเช่น แทนที่จะเป็นประเภทค่า "วันที่" เซลล์จะมีประเภทค่า "ข้อความ" เมื่อเตรียมข้อมูลจำนวนมาก คุณควรตรวจสอบประเภทค่าทั้งหมดเพื่อให้แน่ใจว่าสอดคล้องกับรูปแบบเฉพาะ ตัวอย่างเช่น ตารางเต็มไปด้วยข้อมูลจากแหล่งต่างๆ ที่เข้ามา วิธีการที่แตกต่างกันวันที่ถูกบันทึก คุณไม่สามารถคำนวณต่างๆ กับตารางดังกล่าวได้ เป็นการยากที่จะสังเกตเห็นว่ารายการป้อนในรูปแบบที่ไม่ถูกต้อง วันที่ผิดจากมุมมองของ Excel

ค้นหารูปแบบข้อความแทนวันที่ใน Excel

เราจะใช้หากต้องการค้นหาค่าที่ผิดพลาดใน Excel อย่างรวดเร็วและไฮไลต์เซลล์ทั้งหมดที่มีรูปแบบไม่ถูกต้อง การจัดรูปแบบตามเงื่อนไข- ยกตัวอย่างตารางง่ายๆ:

ค้นหาและเน้นเซลล์ที่มีรูปแบบการแสดงค่าที่ไม่ถูกต้อง:

ดังที่คุณเห็นในรูป วันที่ทั้งหมดในรูปแบบข้อความจะถูกเน้นด้วยสี:

ในเงื่อนไขการจัดรูปแบบ เราใช้ฟังก์ชันธรรมดา =ETEXT() ซึ่งมีเพียง 1 อาร์กิวเมนต์ - ลิงก์ไปยังเซลล์ที่กำลังตรวจสอบ ที่อยู่ของลิงก์ในอาร์กิวเมนต์ไปยังฟังก์ชัน ITEXT จะต้องสัมพันธ์กัน เนื่องจากแต่ละเซลล์ในช่วงที่เลือกจะถูกตรวจสอบ หากเซลล์ปัจจุบันที่กำลังตรวจสอบมีข้อความ (แทนที่จะเป็นวันที่) ก็จะเป็นเช่นนั้น ค่าไม่ถูกต้องเอ็กเซล จากนั้นฟังก์ชัน ITEXT จะส่งกลับค่า TRUE และรูปแบบใหม่ (เติมสีเขียว) จะถูกกำหนดให้กับเซลล์นี้ทันที ชื่อของฟังก์ชัน ITEXT ควรอ่านเป็นตัวย่อของคำสองคำ: If TEXT

เราอ่านฟังก์ชันลอจิคัลที่สามารถใช้ตรวจสอบรูปแบบและประเภทข้อมูลอื่นๆ ในเซลล์ได้ในลักษณะเดียวกัน:

• ENETEXT – หากไม่ใช่ข้อความ (ฟังก์ชันนี้ยังช่วยให้คุณค้นหาวันที่ในข้อความ Excel ได้อย่างรวดเร็ว)
• ISNUMBER – ถ้าเป็นตัวเลข (ช่วยให้คุณค้นหารูปแบบตัวเลขที่ไม่ถูกต้องใน Excel ได้อย่างรวดเร็ว)
• EOS – หากมีข้อผิดพลาด
• ข้อผิดพลาด - หากมีข้อผิดพลาด
• IFERROR – หากมีข้อผิดพลาด (นี่ไม่ใช่ฟังก์ชันเชิงตรรกะ แต่ง่ายต่อการปรับให้เหมาะสมสำหรับงานที่กำหนด)
• ว่างเปล่า - ถ้าว่างเปล่า;
• ELOGIC – หากเป็นค่าตรรกะ
• UND – หากค่าไม่พร้อมใช้งาน (#N/A)
• UNEVEN – หากค่าเป็นคี่
• EVEN – ถ้าค่าเป็นเลขคู่
• LINK – ถ้าเป็นลิงค์
• FORMULA – ถ้าเป็นสูตร

หากต้องการคุณสามารถทดสอบฟังก์ชันทั้งหมดในเชิงทดลองได้

หนึ่งในคุณสมบัติที่น่าสนใจ ไมโครซอฟต์ เอ็กเซลเป็น วันนี้- โอเปอเรเตอร์นี้ใช้เพื่อป้อนวันที่ปัจจุบันลงในเซลล์ แต่ยังใช้ร่วมกับสูตรอื่นๆ ร่วมกันได้ มาดูคุณสมบัติหลักของฟังก์ชั่นกันดีกว่า วันนี้ความแตกต่างของงานและการโต้ตอบกับผู้ปฏิบัติงานรายอื่น

การทำงาน วันนี้ส่งออกวันที่ที่ตั้งไว้ในคอมพิวเตอร์ไปยังเซลล์ที่ระบุ เธออยู่ในกลุ่มผู้ปฏิบัติงาน "วันและเวลา".

แต่คุณต้องเข้าใจว่าสูตรนี้เองจะไม่อัปเดตค่าในเซลล์ นั่นคือถ้าคุณเปิดโปรแกรมในอีกไม่กี่วันต่อมาและไม่ได้คำนวณสูตรในนั้นใหม่ (ด้วยตนเองหรือโดยอัตโนมัติ) วันที่เดียวกันจะถูกตั้งค่าในเซลล์ไม่ใช่วันที่ปัจจุบันในขณะนี้

ในการตรวจสอบว่ามีการติดตั้งการคำนวณใหม่อัตโนมัติในเอกสารใดเอกสารหนึ่งหรือไม่ คุณต้องดำเนินการตามลำดับ

ตอนนี้การเปลี่ยนแปลงใดๆ ในเอกสารจะคำนวณใหม่โดยอัตโนมัติ

หากคุณไม่ต้องการติดตั้งการคำนวณใหม่อัตโนมัติด้วยเหตุผลบางประการ ให้ทำการอัปเดต วันที่ปัจจุบันเนื้อหาของเซลล์ที่มีฟังก์ชัน วันนี้คุณต้องเลือกมัน วางเคอร์เซอร์ในแถบสูตรแล้วกดปุ่ม เข้า.

ในกรณีนี้ เมื่อปิดใช้งานการคำนวณใหม่อัตโนมัติ จะดำเนินการเฉพาะกับเซลล์นี้เท่านั้น ไม่ใช่สำหรับทั้งเอกสาร

วิธีที่ 1: การป้อนฟังก์ชันด้วยตนเอง

โอเปอเรเตอร์นี้ไม่มีข้อโต้แย้ง ไวยากรณ์ของมันค่อนข้างง่ายและมีลักษณะดังนี้:

วันนี้()

วิธีที่ 2: การใช้ตัวช่วยสร้างฟังก์ชัน

หรือคุณสามารถใช้ ตัวช่วยสร้างฟังก์ชัน- ตัวเลือกนี้เหมาะอย่างยิ่งสำหรับผู้ใช้ Excel มือใหม่ที่ยังสับสนเกี่ยวกับชื่อของฟังก์ชันและไวยากรณ์ แม้ว่าในกรณีนี้จะง่ายที่สุดก็ตาม

วิธีที่ 3: การเปลี่ยนรูปแบบเซลล์

หากก่อนจะเข้าฟังก์ชั่น วันนี้หากเซลล์มีรูปแบบทั่วไป เซลล์นั้นจะถูกจัดรูปแบบใหม่เป็นรูปแบบวันที่โดยอัตโนมัติ แต่หากมีการจัดรูปแบบช่วงสำหรับค่าอื่นแล้ว ช่วงนั้นจะไม่เปลี่ยนแปลง ซึ่งหมายความว่าสูตรจะให้ผลลัพธ์ที่ไม่ถูกต้อง

หากต้องการดูค่ารูปแบบของแต่ละเซลล์หรือพื้นที่บนแผ่นงานคุณต้องเลือกช่วงที่ต้องการและเมื่ออยู่ในแท็บ "หน้าแรก" ให้ดูว่าค่าใดที่ตั้งไว้ในรูปแบบพิเศษบนริบบิ้นใน บล็อกเครื่องมือ "ตัวเลข".

ถ้าหลังจากเข้าสูตรแล้ว วันนี้รูปแบบเซลล์ไม่ได้ถูกตั้งค่าโดยอัตโนมัติ "วันที่"จากนั้นฟังก์ชันจะแสดงผลลัพธ์ไม่ถูกต้อง ในกรณีนี้ คุณต้องเปลี่ยนรูปแบบด้วยตนเอง

นอกจากนี้ในหน้าต่างการจัดรูปแบบคุณยังสามารถเปลี่ยนการนำเสนอวันที่ปัจจุบันได้อีกด้วย รูปแบบเริ่มต้นคือเทมเพลต "วว.ปปปป"- การเน้นตัวเลือกค่าต่างๆ ในช่อง "พิมพ์"ซึ่งอยู่ทางด้านขวาของหน้าต่างการจัดรูปแบบ สามารถเปลี่ยนได้ รูปร่างการแสดงวันที่ในเซลล์ หลังจากทำการเปลี่ยนแปลงแล้วอย่าลืมคลิกที่ปุ่ม "ตกลง".

วิธีที่ 4: ใช้ TODAY ร่วมกับสูตรอื่นๆ

นอกจากนี้ฟังก์ชั่น วันนี้สามารถใช้เป็นส่วนหนึ่งของสูตรที่ซับซ้อนได้ ในแง่นี้ โอเปอเรเตอร์นี้ช่วยให้คุณสามารถแก้ไขปัญหาได้กว้างกว่าเมื่อใช้อย่างอิสระ

ผู้ดำเนินการ วันนี้สะดวกในการคำนวณช่วงเวลา เช่น เมื่อระบุอายุของบุคคล เมื่อต้องการทำเช่นนี้ ให้เขียนนิพจน์ประเภทนี้ลงในเซลล์:

ปี(วันนี้())-1965

หากต้องการใช้สูตรให้คลิกที่ปุ่ม เข้า.

ตอนนี้หากกำหนดค่าการคำนวณสูตรเอกสารใหม่อย่างถูกต้อง เซลล์จะแสดงอายุปัจจุบันของบุคคลที่เกิดในปี 2508 อย่างต่อเนื่อง สำนวนที่คล้ายกันนี้สามารถนำมาใช้สำหรับปีเกิดอื่นๆ หรือเพื่อคำนวณวันครบรอบของเหตุการณ์ก็ได้

นอกจากนี้ยังมีสูตรที่แสดงค่าในเซลล์ล่วงหน้าหลายวัน ตัวอย่างเช่น หากต้องการแสดงวันที่สามวันนับจากนี้ จะเป็นดังนี้:

วันนี้()+3

หากคุณต้องการนัดเดทเมื่อสามวันก่อนตลอดเวลา สูตรจะมีลักษณะดังนี้:

วันนี้()-3

หากคุณต้องการแสดงเฉพาะจำนวนวันปัจจุบันในเดือนในเซลล์และไม่ใช่วันที่ทั้งหมด ให้ใช้นิพจน์ต่อไปนี้:

วันต่อวัน())

การดำเนินการที่คล้ายกันเพื่อแสดงหมายเลขเดือนปัจจุบันจะมีลักษณะดังนี้:

เดือน(วันนี้())

ฟังก์ชันวันที่() , เวอร์ชันภาษาอังกฤษของ DATE() ใน ส่งกลับจำนวนเต็มที่แสดงวันที่ที่ระบุ สูตร =DATE(2011;02;28) จะส่งกลับตัวเลข 40602 หากรูปแบบเซลล์ถูกตั้งค่าเป็น General ก่อนที่จะป้อนสูตรนี้ ผลลัพธ์จะถูกจัดรูปแบบเป็นวันที่ เช่น 28/02/2554

ไวยากรณ์ของฟังก์ชัน

วันที่(ปี;เดือน;วัน)

ปี- อาร์กิวเมนต์ที่สามารถมีได้ตั้งแต่หนึ่งถึงสี่หลัก
เดือนเป็นจำนวนเต็มบวกหรือลบตั้งแต่วันที่ 1 (มกราคม) ถึงวันที่ 12 (ธันวาคม) ซึ่งแสดงถึงเดือนของปี
วันเป็นจำนวนเต็มบวกหรือลบตั้งแต่ 1 ถึง 31 แทนวันของเดือน

ใน วันที่ใน Excelจะถูกจัดเก็บเป็นลำดับของตัวเลข (1, 2, 3, ...) ซึ่งช่วยให้คุณสามารถคำนวณได้ ตามค่าเริ่มต้น วันที่ 1 มกราคม 1900 จะมีหมายเลขเป็น 1 และวันที่ 28 กุมภาพันธ์ 2554 จะมีหมายเลขเป็น 40602 เนื่องจากช่วงระหว่างวันที่เหล่านี้คือ 40,602 วัน หากต้องการเรียนรู้วิธีที่ EXCEL เก็บวันที่และเวลา โปรดอ่านบทความนี้

ตัวอย่าง

เพื่อที่จะเพิ่มวันที่ 02/28/2011 ที่มีอยู่ในเซลล์ A1 เช่น 5 ปี คุณสามารถใช้สูตรต่อไปนี้:
=วันที่(ปี(A1)+5,เดือน(A1),วัน(A1))
เราได้รับผลลัพธ์ในวันที่ 28/02/2559

หากต้องการเพิ่ม เช่น 15 เดือนถึงวันที่ 28/02/2554 คุณสามารถใช้สูตรต่อไปนี้:
=วันที่(ปี(A1),เดือน(A1)+15,วัน(A1))หรือสูตร =DATEMES(A1,15)
เราได้รับผลลัพธ์ในวันที่ 28/05/2555

บันทึก- มีความแตกต่างระหว่าง DATEMES() และ DATE() เมื่อบวกเดือน เพิ่มหนึ่งเดือนในวันที่ 30/01/2552:

• =DATEMES("01/30/2009";1)จะกลับมาในวันที่ 28/02/2552 เพราะ ไม่มีวันที่ 30 กุมภาพันธ์ ฟังก์ชันจะส่งคืนวันสุดท้ายของเดือน เช่น 28/02/2552 (“พิเศษ” 2 วันจะถูกละทิ้ง);
• =วันที่(ปี("30/01/2552");เดือน("30/01/2552")+1;วัน("30/01/2552"))จะกลับมาในวันที่ 03/02/2552: จะเพิ่ม "พิเศษ" 2 วัน (29 และ 30 กุมภาพันธ์) เข้ากับวันที่

หากคุณเขียนสูตร =DATE(2008;3;) สูตรจะส่งคืนวันที่ 29/02/2008 เหล่านั้น. โดยการละเว้นวันอาร์กิวเมนต์สุดท้าย เราจะตั้งค่าให้เท่ากับ 0 ดังนั้น สูตรจึงส่งคืน