เนื้อหา
ในการทำงานอย่างมืออาชีพกับสเปรดชีต การโต้ตอบกับวันที่และเวลาไม่ใช่เรื่องแปลก คุณจะไม่สามารถทำได้โดยไม่ต้อง ดังนั้นพระเจ้าเองจึงสั่งให้เรียนรู้วิธีทำงานกับข้อมูลประเภทนี้ วิธีนี้จะช่วยคุณประหยัดเวลาได้มาก และป้องกันข้อผิดพลาดมากมายขณะทำงานกับสเปรดชีต
ขออภัย ผู้เริ่มต้นจำนวนมากไม่ทราบว่าข้อมูลได้รับการประมวลผลอย่างไร ดังนั้นก่อนที่จะพิจารณาการดำเนินการในชั้นเรียนนี้ จำเป็นต้องจัดทำโปรแกรมการศึกษาที่มีรายละเอียดมากขึ้น
วิธีแสดงวันที่ใน Excel
ข้อมูลวันที่จะถูกประมวลผลเป็นจำนวนวันตั้งแต่ 0 มกราคม 1900 ใช่ คุณไม่ผิด แน่นอนจากศูนย์ แต่นี่เป็นสิ่งจำเป็นเพื่อให้มีจุดเริ่มต้นเพื่อให้วันที่ 1 มกราคมถือว่าเป็นหมายเลข 1 แล้วเป็นต้น ค่าวันที่ที่รองรับสูงสุดคือ 2958465 ซึ่งในทางกลับกันคือ 31 ธันวาคม 9999
วิธีนี้ทำให้สามารถใช้วันที่ในการคำนวณและสูตรได้ ดังนั้น Excel ทำให้สามารถกำหนดจำนวนวันระหว่างวันที่ โครงร่างเรียบง่าย: ตัวเลขที่สองถูกลบออกจากตัวเลขหนึ่งตัว จากนั้นค่าผลลัพธ์จะถูกแปลงเป็นรูปแบบวันที่
เพื่อความชัดเจนยิ่งขึ้น นี่คือตารางที่แสดงวันที่พร้อมค่าตัวเลขที่สอดคล้องกัน
ในการกำหนดจำนวนวันที่ผ่านไปจากวันที่ A ถึงวันที่ B คุณต้องลบค่าแรกออกจากค่าสุดท้าย ในกรณีของเรา นี่คือสูตร = B3-B2. เข้าไปแล้วได้ผลดังนี้ครับ
สิ่งสำคัญคือต้องสังเกตว่าค่าเป็นวันเนื่องจากเราได้เลือกรูปแบบสำหรับเซลล์ที่แตกต่างจากวันที่ หากเราเลือกรูปแบบ "วันที่" ในตอนแรก ผลลัพธ์จะเป็นแบบนี้
สิ่งสำคัญคือต้องใส่ใจกับจุดนี้ในการคำนวณของคุณ
กล่าวคือ ในการแสดงหมายเลขซีเรียลที่ถูกต้องซึ่งตรงกับวันที่ทั้งหมด คุณต้องใช้รูปแบบอื่นที่ไม่ใช่วันที่ ในทางกลับกัน หากต้องการเปลี่ยนตัวเลขให้เป็นวันที่ คุณควรกำหนดรูปแบบที่เหมาะสม
วิธีแสดงเวลาใน Excel
วิธีแสดงเวลาใน Excel จะแตกต่างจากวันที่เล็กน้อย วันถูกใช้เป็นฐาน และชั่วโมง นาที วินาทีเป็นส่วนที่เป็นเศษส่วน นั่นคือ 24 ชั่วโมงคือ 1 และค่าที่น้อยกว่าถือเป็นเศษส่วน ดังนั้น 1 ชั่วโมงคือ 1/24 ของวัน 1 นาทีคือ 1/1140 และ 1 วินาทีคือ 1/86400 หน่วยเวลาที่เล็กที่สุดใน Excel คือ 1 มิลลิวินาที
คล้ายกับวันที่ วิธีการแสดงนี้ทำให้สามารถคำนวณตามเวลาได้ จริงอยู่มีสิ่งหนึ่งที่ไม่สะดวกที่นี่ หลังจากคำนวณแล้ว เราจะได้ส่วนหนึ่งของวัน ไม่ใช่จำนวนวัน
ภาพหน้าจอแสดงค่าในรูปแบบตัวเลขและรูปแบบ "เวลา"
วิธีการคำนวณเวลาจะคล้ายกับวันที่ จำเป็นต้องลบเวลาก่อนหน้าออกจากเวลาภายหลัง ในกรณีของเรา นี่คือสูตร = B3-B2.
เนื่องจากเซลล์ B4 มีรูปแบบทั่วไปในตอนแรก จากนั้นเมื่อสิ้นสุดการแนะนำสูตร ก็จะเปลี่ยนเป็น "เวลา" ทันที
เมื่อทำงานกับเวลา Excel จะดำเนินการทางคณิตศาสตร์ตามปกติด้วยตัวเลข ซึ่งจะถูกแปลเป็นรูปแบบเวลาที่คุ้นเคย
รูปแบบวันที่และเวลา
เท่าที่เราทราบ วันที่และเวลาสามารถจัดเก็บในรูปแบบต่างๆ ได้ ดังนั้นคุณจำเป็นต้องรู้วิธีการป้อนอย่างถูกต้องเพื่อให้การจัดรูปแบบถูกต้อง
แน่นอน คุณสามารถใช้หมายเลขซีเรียลของวันหรือบางส่วนของวันเมื่อป้อนวันที่และเวลาได้ แต่วิธีนี้ไม่สะดวกมาก นอกจากนี้ คุณจะต้องใช้รูปแบบบางอย่างกับเซลล์อย่างต่อเนื่อง ซึ่งจะเพิ่มความรู้สึกไม่สบายเท่านั้น
ดังนั้น Excel จึงอนุญาตให้คุณระบุเวลาและวันที่ในรูปแบบต่างๆ หากคุณใช้หนึ่งในนั้น โปรแกรมจะแปลงข้อมูลเป็นตัวเลขที่เหมาะสมทันทีและนำรูปแบบที่ถูกต้องไปใช้กับเซลล์
ดูตารางด้านล่างสำหรับรายการวิธีการป้อนวันที่และเวลาที่รองรับโดย Excel คอลัมน์ด้านซ้ายแสดงรูปแบบที่เป็นไปได้ และคอลัมน์ด้านขวาจะแสดงรูปแบบที่จะแสดงใน Excel หลังการแปลง สิ่งสำคัญคือต้องทราบว่าหากไม่ระบุปี ระบบจะกำหนดปีปัจจุบันซึ่งตั้งค่าไว้ในระบบปฏิบัติการโดยอัตโนมัติ
อันที่จริง มีวิธีแสดงอีกมากมาย แต่สิ่งเหล่านี้ก็เพียงพอแล้ว นอกจากนี้ ตัวเลือกการบันทึกวันที่เฉพาะอาจแตกต่างกันไปตามประเทศหรือภูมิภาค ตลอดจนการตั้งค่าของระบบปฏิบัติการ
จัดรูปแบบเอง
ขณะทำงานกับเซลล์ ผู้ใช้สามารถกำหนดรูปแบบได้ เขาสามารถทำให้แสดงเฉพาะเวลา เดือน วัน และอื่นๆ เท่านั้น นอกจากนี้ยังสามารถปรับลำดับการกำหนดวันที่และตัวคั่นได้อีกด้วย
ในการเข้าถึงหน้าต่างแก้ไข คุณต้องเปิดแท็บ "หมายเลข" ซึ่งคุณจะพบหน้าต่างตัวเลือก "จัดรูปแบบเซลล์" ในกล่องโต้ตอบที่เปิดขึ้น จะมีหมวดหมู่ "วันที่" ซึ่งคุณสามารถเลือกรูปแบบวันที่ที่ถูกต้องได้
หากคุณเลือกหมวดหมู่ "เวลา" ตามลำดับ รายการพร้อมตัวเลือกสำหรับแสดงเวลาจะปรากฏขึ้น
หากต้องการใช้ตัวเลือกการจัดรูปแบบเฉพาะกับเซลล์ คุณต้องเลือกรูปแบบที่ต้องการแล้วคลิกตกลง หลังจากนั้นผลจะถูกนำไปใช้ หากมีรูปแบบไม่เพียงพอที่ Excel เสนอ คุณสามารถค้นหาหมวดหมู่ "รูปแบบทั้งหมด" มีตัวเลือกมากมายเช่นกัน
หากไม่มีตัวเลือกใดที่เหมาะสม คุณสามารถสร้างตัวเลือกของคุณเองได้เสมอ มันง่ายมากที่จะทำเช่นนี้ คุณเพียงแค่ต้องเลือกรูปแบบที่กำหนดไว้ล่วงหน้าเป็นตัวอย่างและทำตามขั้นตอนเหล่านี้:
- เลือกเซลล์ที่มีรูปแบบที่คุณต้องการเปลี่ยน
- เปิดกล่องโต้ตอบ "จัดรูปแบบเซลล์" และค้นหาแท็บ "หมายเลข"
- ถัดไป หมวดหมู่ "รูปแบบทั้งหมด" จะเปิดขึ้น ซึ่งเราจะพบช่องป้อนข้อมูล "ประเภท" คุณต้องระบุรหัสรูปแบบตัวเลข หลังจากที่คุณป้อน คลิก "ตกลง"
- หลังจากขั้นตอนเหล่านี้ เซลล์จะแสดงข้อมูลวันที่และเวลาในรูปแบบที่กำหนดเอง
การใช้ฟังก์ชันกับวันที่และเวลา
เมื่อทำงานกับวันที่และเวลา ผู้ใช้สามารถใช้ฟังก์ชันต่างๆ ได้มากกว่า 20 ฟังก์ชัน และแม้ว่าจำนวนเงินนี้อาจมากเกินไปสำหรับใครบางคน แต่ก็สามารถใช้เพื่อบรรลุเป้าหมายบางอย่างได้
ในการเข้าถึงฟังก์ชันที่เป็นไปได้ทั้งหมด คุณต้องไปที่หมวดหมู่ "วันที่และเวลา" ของกลุ่ม "ไลบรารีฟังก์ชัน" เราจะพิจารณาเฉพาะฟังก์ชันหลักบางอย่างที่ทำให้สามารถแยกพารามิเตอร์ต่างๆ จากวันที่และเวลาได้
ปี()
ให้ความสามารถในการรับปีที่ตรงกับวันที่ที่ระบุ ดังที่คุณทราบแล้ว ค่านี้สามารถอยู่ระหว่าง 1900 ถึง 9999
เซลล์ 1 แสดงวันที่ในรูปแบบ DDDD DD.MM.YYYY hh:mm:ss นี่คือรูปแบบที่เราสร้างไว้ก่อนหน้านี้ ลองมาดูตัวอย่างสูตรที่กำหนดจำนวนปีที่ผ่านไประหว่างวันที่สองวัน
ในขณะเดียวกัน หากมองให้ละเอียดมากขึ้น ปรากฎว่า ฟังก์ชันไม่ได้คำนวณผลลัพธ์ที่ถูกต้องทั้งหมด เหตุผลก็คือมันใช้เฉพาะวันที่ในการคำนวณเท่านั้น
เดือน()
ด้วยฟังก์ชันนี้ คุณสามารถเน้นจำนวนเดือนที่ตรงกับวันที่ที่ระบุได้ ส่งกลับผลลัพธ์ตั้งแต่ 1 ถึง 12 ตัวเลขนี้จะสอดคล้องกับจำนวนของเดือน
วัน()
คล้ายกับฟังก์ชันก่อนหน้านี้ ฟังก์ชันนี้จะคืนค่าจำนวนวันในวันที่ที่กำหนด ผลการคำนวณสามารถอยู่ในช่วงตั้งแต่ 1 ถึง 31
เวลา()
ตามชื่อที่แนะนำ ฟังก์ชันนี้จะคืนค่าตัวเลขชั่วโมง ซึ่งมีตั้งแต่ 0 ถึง 23
นาที()
ฟังก์ชันที่ส่งกลับจำนวนนาทีในเซลล์ที่ระบุ ค่าที่เป็นไปได้ที่ส่งคืนคือ 0 ถึง 59
วินาที()
ฟังก์ชันนี้ส่งคืนค่าเดียวกันกับค่าก่อนหน้า ยกเว้นว่าจะส่งกลับค่าวินาที
วัน()
ด้วยฟังก์ชันนี้ คุณสามารถค้นหาจำนวนวันในสัปดาห์ที่ใช้ในวันที่นี้ ค่าที่เป็นไปได้คือตั้งแต่ 1 ถึง 7 แต่โปรดจำไว้ว่าการนับถอยหลังเริ่มต้นจากวันอาทิตย์ ไม่ใช่วันจันทร์ อย่างที่เรามักจะทำ
อย่างไรก็ตาม เมื่อใช้อาร์กิวเมนต์ที่สอง ฟังก์ชันนี้จะช่วยให้คุณปรับแต่งรูปแบบได้ ตัวอย่างเช่น หากคุณส่งค่า 2 เป็นพารามิเตอร์ที่สอง คุณสามารถตั้งค่ารูปแบบเพื่อให้ตัวเลข 1 หมายถึงวันจันทร์แทนที่จะเป็นวันอาทิตย์ สะดวกกว่าสำหรับผู้ใช้ในประเทศ
ถ้าเราเขียน 2 ในอาร์กิวเมนต์ที่สอง ในกรณีของเรา ฟังก์ชันจะส่งคืนค่า 6 ซึ่งตรงกับวันเสาร์
วันนี้ ()
ฟังก์ชันนี้ง่ายมาก: ไม่จำเป็นต้องมีอาร์กิวเมนต์ในการทำงาน ส่งกลับหมายเลขประจำเครื่องของวันที่ที่ตั้งค่าไว้ในคอมพิวเตอร์ หากใช้กับเซลล์ที่มีการตั้งค่ารูปแบบทั่วไป ระบบจะแปลงเป็นรูปแบบ "วันที่" โดยอัตโนมัติ
ทาทา ()
ฟังก์ชันนี้ยังไม่ต้องการอาร์กิวเมนต์ใดๆ มันทำงานในลักษณะเดียวกับก่อนหน้านี้ เฉพาะกับวันที่และเวลา ใช้หากจำเป็นต้องแทรกวันที่และเวลาปัจจุบันที่กำหนดไว้ในคอมพิวเตอร์ลงในเซลล์ และเช่นเดียวกับในฟังก์ชันก่อนหน้านี้ เมื่อใช้เซลล์นี้ เซลล์จะถูกแปลงเป็นรูปแบบวันที่และเวลาโดยอัตโนมัติ โดยจะต้องตั้งค่ารูปแบบ "ทั่วไป" ไว้ก่อนหน้านี้
ทั้งฟังก์ชันก่อนหน้าและฟังก์ชันนี้จะเปลี่ยนแปลงโดยอัตโนมัติทุกครั้งที่มีการคำนวณแผ่นงานใหม่ ทำให้สามารถแสดงเวลาและวันที่ล่าสุดได้
ตัวอย่างเช่น สูตรดังกล่าวสามารถกำหนดเวลาปัจจุบันได้
=วันนี้ () - วันนี้ ()
ในกรณีนี้ สูตรจะกำหนดเศษส่วนของวันในรูปแบบทศนิยม จริงอยู่ คุณจะต้องใช้รูปแบบเวลากับเซลล์ที่มีการเขียนสูตร หากคุณต้องการแสดงเวลาทุกประการ ไม่ใช่ตัวเลข
วันที่()
ฟังก์ชันนี้มีสามอาร์กิวเมนต์ ซึ่งแต่ละอาร์กิวเมนต์ต้องป้อน หลังการคำนวณ ฟังก์ชันนี้จะคืนค่าหมายเลขซีเรียลของวันที่ เซลล์จะถูกแปลงเป็นรูปแบบ "วันที่" โดยอัตโนมัติหากมีรูปแบบ "ทั่วไป" มาก่อน
อาร์กิวเมนต์วันหรือเดือนอาจเป็นค่าบวกหรือค่าลบก็ได้ ในกรณีแรก วันที่จะเพิ่มขึ้น และในกรณีที่สองจะลดลง
คุณยังสามารถใช้การดำเนินการทางคณิตศาสตร์ในอาร์กิวเมนต์ของฟังก์ชัน DATE ได้ ตัวอย่างเช่น สูตรนี้บวก 1 ปี 5 เดือนและ 17 วันเป็นวันที่ในเซลล์ A1
และสูตรดังกล่าวทำให้สามารถเปลี่ยนสตริงข้อความให้เป็นวันที่ทำงานเต็มเปี่ยมได้ ซึ่งสามารถนำไปใช้ในฟังก์ชันอื่นๆ ได้
เวลา()
เช่นเดียวกับฟังก์ชั่น วันที่(), ฟังก์ชันนี้มีพารามิเตอร์ที่จำเป็นสามตัว – ชั่วโมง นาที และวินาที หลังจากใช้แล้ว ตัวเลขทศนิยมจะปรากฏในเซลล์ผลลัพธ์ แต่ตัวเซลล์จะถูกจัดรูปแบบในรูปแบบ "เวลา" หากก่อนหน้านี้มีรูปแบบ "ทั่วไป"
โดยหลักการทำงาน ฟังก์ชัน เวลา() и วันที่() หลายอย่างที่คล้ายกัน ดังนั้นจึงไม่มีเหตุผลที่จะมุ่งเน้นไปที่มัน
สิ่งสำคัญคือต้องทราบว่าฟังก์ชันนี้ไม่สามารถส่งคืนเวลาที่มากกว่า 23:59:59 ได้ หากผลลัพธ์มีค่ามากกว่านี้ ฟังก์ชันจะถูกรีเซ็ตโดยอัตโนมัติเป็นศูนย์
ฟังก์ชั่น วันที่() и เวลา() สามารถใช้ร่วมกันได้
ในภาพหน้าจอนี้ เซลล์ D1 ซึ่งใช้ฟังก์ชันทั้งสองนี้มีรูปแบบวันที่และเวลา
ฟังก์ชันคำนวณวันที่และเวลา
มีทั้งหมด 4 ฟังก์ชันที่ให้คุณดำเนินการทางคณิตศาสตร์พร้อมวันที่และเวลาได้
ข้อมูล()
เมื่อใช้ฟังก์ชันนี้ คุณสามารถค้นหาเลขลำดับของวันที่ที่อยู่หลังจำนวนเดือนที่ทราบ (หรือก่อนหน้าวันที่กำหนด) ฟังก์ชันนี้รับสองอาร์กิวเมนต์: วันที่เริ่มต้นและจำนวนเดือน อาร์กิวเมนต์ที่สองอาจเป็นบวกหรือลบก็ได้ ต้องระบุตัวเลือกแรกหากคุณต้องการคำนวณวันที่ในอนาคต และตัวเลือกที่สองคือตัวเลือกก่อนหน้า
EOMONTH()
ฟังก์ชันนี้ทำให้สามารถกำหนดเลขลำดับของวันสุดท้ายของเดือนที่อยู่หลังหรือก่อนวันที่ที่กำหนดได้ มีข้อโต้แย้งเช่นเดียวกับข้อก่อนหน้า
วันทำงาน()
เช่นเดียวกับฟังก์ชั่น ข้อมูล()เฉพาะความล่าช้าหรือล่วงหน้าที่เกิดขึ้นตามจำนวนวันทำการที่กำหนด ไวยากรณ์จะคล้ายกัน
ฟังก์ชันทั้งสามนี้จะคืนค่าตัวเลข หากต้องการดูวันที่ คุณต้องแปลงเซลล์ให้อยู่ในรูปแบบที่เหมาะสม
แจ่มใส()
ฟังก์ชันอย่างง่ายนี้กำหนดจำนวนวันทำการระหว่างวันที่ 1 ถึงวันที่ 2