Page 1 of 1
การแก้ไขข้อมูลวันเดือนปี
Posted: Wed Mar 25, 2020 5:49 pm
by Jack_IT
การแก้ไขข้อมูลวันเดือนปี
ผมดึงข้อมูลจากเครื่องสแกนนิ้วมือ
แต่วันที่ 29/2/2563 กลับพบปัญหาว่า ไม่สามารถดึงข้อมูลออกมาเป็นรูป date ได้นะครับ
ไม่ทราบว่า เป็นเพราะอะไร และจะแก้ไขอย่างไรครับ
Re: การแก้ไขข้อมูลวันเดือนปี
Posted: Wed Mar 25, 2020 6:29 pm
by norkaz
ลองเปลี่ยนปี 2563 เป็น 2020
Ctrl + H
Find what คีย์ 2563
Replace with คีย์ 2020
Replace All
a) เปลี่ยนปี 2563 เป็น 2020 การคีย์ ปีควรคีย์เป็นปีฝรั่ง ไม่ใช่ปีไทย
b) Excel มอง 2563 คือ ปี --- เดือน February ค.ศ. 2563 มี 28 วัน สาเหตุที่ 29/2/2563 ไม่สามารถดึงข้อมูลออกมาเป็นรูป date ได้ เนื่องจากวันนี้ ไม่มีอยู่จริง ปี ค.ศ 2563 ครับ
Norkaz
Re: การแก้ไขข้อมูลวันเดือนปี
Posted: Wed Mar 25, 2020 7:34 pm
by Jack_IT
ขอบคุณครับ
1. ใช่ครับ ปีต้องเป็น ค.ศ. แต่เนื่องด้วยเป็นข้อมูลที่ดึงมาจากเครื่องสแกนนิ้วมือนะครับ
เคยสอบถามกับบริษัทที่เป็นตัวแทนจำหน่ายแล้ว เขาแจ้งว่า ระบบนี้เขาสร้างฐานเป็น พ.ศ. ครับ
เราจึงต้องมาแก้ไขเอง
2. ผมลืมข้อนี้จริง ๆ ทำให้ติดกับอยู่นาน ใช้คำสั่งการ ค้นหาแทนที่ไม่ได้ครับ
เพราะต้องสร้างเป็นโปรแกรมไว้ให้ hr นะครับ
จึงต้องกำหนดฟังก์ชั่นให้ เพื่อจะใช้ได้กับข้อมูลในทุก ๆ วัน
ซึ่งตอนนี้แก้ไขได้แล้ว
B2 =IFERROR(VALUE(TEXT(A2,"dd/mm/yyyy")),VALUE(MID(A2,1,FIND(" ",A2)-5)&MID(A2,7,4)-543))
C2 =IFERROR(VALUE(TEXT(A2,"hh:mm:ss")),VALUE(MID(A2,FIND(" ",A2),8)))
Re: การแก้ไขข้อมูลวันเดือนปี
Posted: Wed Mar 25, 2020 8:31 pm
by norkaz
ขอแสดงความเห็นเพิ่มเติม ถือเป็นการพูดคุยกันนะครับ
a) กฺรณีคอลัมน์ B วันที่ ที่ไม่เป็นจริง
ข้อน่าสังเกตุคือ วันที่ ที่ไม่เป็นจริง ในปีแบบฝรั่ง เช่น 29/02/2563 เมื่อใส่สูตร ด้านบนไปแล้ว
B2 =IFERROR(VALUE(TEXT(A2,"dd/mm/yyyy")),VALUE(MID(A2,1,FIND(" ",A2)-5)&MID(A2,7,4)-543))
จะแสดงผลเป็นปี ฝรั่ง เช่น 29/02/2020 แต่แถวอื่นๆ ที่ถูกอยู่แล้ว แสดงเป็นปีไทย (ตามฐานข้อมูลเดิม ที่ ยอมรับให้แสดงปีแบบไทยได้)
และหากผู้ใช้งาน ( Users) ยอมรับ ในการแสดง ทั้ง ปี ไทย และ ปีฝรั่ง ใน Report เดียวกันได้ ก็สามารถทำได้ ตามนั้นครับ
b) อีกกรณี ที่ขอแยกออกมาคุยกัน ไม่ได้หมายถึง การใช้ ปี แบบไทย คือเรื่องที่ผิด หาก Users สามารถใช้ ปีแบบไทย เป็นปกติ ก็ทำได้เช่นกัน แต่การนำไปคำนวณต่อ โดยใช้ปี แบบไทยนั้น อาจเกิดประเด็นภายหลังได้ง่ายครับ
c) ในการแก้ปัญหาเรื่อง วันที่นั้น "บางครั้ง" ไม่สามารถหาจุดสมบูรณ์แบบ หรือ ผิด ถูก ได้ แน่นอนขึ้นกับ Users ต้องการแบบใด
แม้ว่า การพยายามเขียนสูตรให้ถูกต้อง สมบูรณ์แบบ แต่หากไม่สอดคล้อง กับความต้องการของ Users ความสมบูรณ์แบบนั้น อาจจะไม่เกิดประโยชน์ในเคสนั้นๆก็ได้ ตรงกันข้าม หากสิ่งที่เรามองกันว่า มันมีข้อขัดแย้ง ตามหลักการของ Excel แต่ Users ยินดี และ ยอมรับให้เป็นแบบนั้น สูตรที่เขียนแบบขัดแย้งกับ เรื่องปกติทั่วไป อาจกลับเป็นเรื่อง สมบูรณ์แบบ สำหรับผู้ใช้ ในเคสนั้นๆก็ได้เช่นกัน
Norkaz
Re: การแก้ไขข้อมูลวันเดือนปี
Posted: Thu Mar 26, 2020 12:06 am
by Bo_ry
B2
=--(LEFT(TEXT(A2,"dd/mm/"),6)&MID(TEXT(A2,"dd/mm/yyyy"),7,4)-543)
ปรับให้แสดงเป็นปี พ.ศ. custom format dd/mm/bbbb
C2
=--RIGHT(TEXT(A2,"hh:mm:ss"),8)
Re: การแก้ไขข้อมูลวันเดือนปี
Posted: Thu Mar 26, 2020 12:32 am
by norkaz
ขอบคุณ อาจารย์ Bo ที่มาช่วยแจมครับ
Norkaz
Re: การแก้ไขข้อมูลวันเดือนปี
Posted: Thu Mar 26, 2020 12:54 am
by norkaz
แถมไปอีกแบบ แสดงค่าแบบปี ค.ศ.
B2
=IFERROR(DATE(YEAR(A2)-543,MONTH(A2),DAY(A2)),--(LEFT(A2,6)&RIGHT(LEFT(A2,10),4)-543))
ถ้าต้องการให้แสดงปี พ.ศ. ปรับ Format ตามที่ อาจารย์ Bo_ry แนะนำครับ
custom format dd/mm/bbbb
์Norkaz
Re: การแก้ไขข้อมูลวันเดือนปี
Posted: Thu Mar 26, 2020 1:59 pm
by Jack_IT
norkaz wrote: Wed Mar 25, 2020 8:31 pm
ขอแสดงความเห็นเพิ่มเติม ถือเป็นการพูดคุยกันนะครับ
a) กฺรณีคอลัมน์ B วันที่ ที่ไม่เป็นจริง
ข้อน่าสังเกตุคือ วันที่ ที่ไม่เป็นจริง ในปีแบบฝรั่ง เช่น 29/02/2563 เมื่อใส่สูตร ด้านบนไปแล้ว
B2 =IFERROR(VALUE(TEXT(A2,"dd/mm/yyyy")),VALUE(MID(A2,1,FIND(" ",A2)-5)&MID(A2,7,4)-543))
จะแสดงผลเป็นปี ฝรั่ง เช่น 29/02/2020 แต่แถวอื่นๆ ที่ถูกอยู่แล้ว แสดงเป็นปีไทย (ตามฐานข้อมูลเดิม ที่ ยอมรับให้แสดงปีแบบไทยได้)
และหากผู้ใช้งาน ( Users) ยอมรับ ในการแสดง ทั้ง ปี ไทย และ ปีฝรั่ง ใน Report เดียวกันได้ ก็สามารถทำได้ ตามนั้นครับ
b) อีกกรณี ที่ขอแยกออกมาคุยกัน ไม่ได้หมายถึง การใช้ ปี แบบไทย คือเรื่องที่ผิด หาก Users สามารถใช้ ปีแบบไทย เป็นปกติ ก็ทำได้เช่นกัน แต่การนำไปคำนวณต่อ โดยใช้ปี แบบไทยนั้น อาจเกิดประเด็นภายหลังได้ง่ายครับ
c) ในการแก้ปัญหาเรื่อง วันที่นั้น "บางครั้ง" ไม่สามารถหาจุดสมบูรณ์แบบ หรือ ผิด ถูก ได้ แน่นอนขึ้นกับ Users ต้องการแบบใด
แม้ว่า การพยายามเขียนสูตรให้ถูกต้อง สมบูรณ์แบบ แต่หากไม่สอดคล้อง กับความต้องการของ Users ความสมบูรณ์แบบนั้น อาจจะไม่เกิดประโยชน์ในเคสนั้นๆก็ได้ ตรงกันข้าม หากสิ่งที่เรามองกันว่า มันมีข้อขัดแย้ง ตามหลักการของ Excel แต่ Users ยินดี และ ยอมรับให้เป็นแบบนั้น สูตรที่เขียนแบบขัดแย้งกับ เรื่องปกติทั่วไป อาจกลับเป็นเรื่อง สมบูรณ์แบบ สำหรับผู้ใช้ ในเคสนั้นๆก็ได้เช่นกัน
Norkaz
ขอบคุณมาก ๆ เลยครับ
สำหรับประเด็นที่ห่วงเกรงว่าจะเกิดปัญหาในการคำนวณ กรณีที่ปีพ.ศ.คนละรูปแบบ จริง ๆ ผมได้ดำเนินการแก้ไขในส่วนนั้นแล้ว เพียงแต่ที่ผมลงมา ผมลงในส่วนให้เห็นการแก้ปัญหาให้เป็นรูปแบบวันเดือนปีให้ได้ก่อนนะครับ
จากนั้นผมก็มาใส่ฟังก์ชั่นเพิ่มภายหลังนะครับ
=IFERROR(VALUE(TEXT(A2,"dd/mm/")&YEAR(A2)-543),VALUE(MID(A2,1,FIND(" ",A2)-5)&MID(A2,7,4)-543))