การตรวจสอบข้อมูล One Stop Service กับแฟ้ม Service ที่ถูกส่งออกมา

วันนี้ได้รับโจทย์จาก รพ.สต.ในเครือข่าย ว่าอยากจะดูข้อมูลแฟ้ม SERVICE.TXT ที่ถูกส่งออกจาก 21 แฟ้ม เนื่องจากตรวจสอบกับหน้า One Stop Service ใน HOSxP PCU แล้วได้ข้อมูลไม่เท่ากัน

ในแฟ้ม SERVICE ที่ถูกส่งออกมามีข้อมูล 788 Record ขณะที่ข้อมูลจากหน้า One Stop Service มี 803 Record

แล้วข้อมูลที่ไม่ถูกส่งออกล่ะ? คือ Record ไหน? จะตรวจสอบได้อย่างไร? ถ้ามานั่งไล่ราย Record คงตาลายพอดี หากข้อมูลเยอะกว่านี้ก็ ไมเป็นอันทำอะไรแล้ว

ความรู้ที่มีและพอจะนึกออกได้ ก็คือการนำเข้ามาหาข้อมูลใน Excel โดยใช้ฟังก์ชัน VLOOKUP ช่วย

งั้นลองมาทำดูดีกว่า

ส่งออกข้อมูลจากหน้า One Stop Service โดยใช้ปุ่ม Excel

จะได้ข้อมูลไฟล์ Excel ออกมา ซึ่งมีข้อมูลเช่นเดียวกับหน้า One Stop Service

ขั้นต่อมาคือการนำเอาไฟล์ SERVICE.TXT ที่ได้จาก 21 แฟ้ม มาแปลงเป็น Excel ในที่นี้ขอใช้ Excel เวอร์ชัน 2013 นะครับ

เลือกแท็บ Data แล้วเลือก Get External Data From Text ตามภาพ

จะมีหน้าต่างให้เลือกไฟล์ขึ้นมา เลือกไฟล์ SERVICE.TXT จาก 21 แฟ้มที่ส่งออกมา กดปุ่ม Import

เลือก Encoding เป็นภาษาไทย จากนั้นกด Next

เลือก Delimiters เป็นสัญลักษณ์ Pipe (|) เพื่อให้แบ่งคอลัมน์ Excel ตามโครงสร้างของ 21 แฟ้ม กด Next

หน้านี้ยังไม่ต้องทำอะไร กด Finish ผ่านไปได้เลย

โปรแกรม Excel จะถามว่าให้นำเข้าที่ Worksheet ไหน ให้เลือก New Worksheet แล้วกด OK

จะเห็นว่าเราจะได้ Worksheet เพิ่มมาอีก 1 sheet ชื่อว่า Sheet1 ตรงนี้สามารถเปลี่ยนชื่อได้ครับ เพื่อให้เข้าใจง่าย ขอเปลี่ยนเป็นชื่อว่า Service นะครับ

ขั้นตอนการใช้งาน VLOOKUP มีหลักการดังนี้

Lookup_value คือค่าที่ต้องการให้ไปหา ในกรณีนี้คือการใช้ SeqID จากข้อมูลที่ Export จาก One Stop Service

Table_array คือ ช่วงข้อมูลที่จะให้ไปหา โดยคอลัมน์แรกจะต้องตรงกับ Lookup_value ที่เราหา และ จะต้องเป็นข้อมูลประเภทเดียวกัน และ ช่วงข้อมูลนี้จะต้องเรียงลำดับด้วย

Col_index_num คือ ลำดับคอลัมน์ในช่วงของ Table_array ที่จะนำค่ามาแสดง

Range_lookup คือ การระบุว่าค่าที่ค้นหาจะต้องตรงตามข้อมูลเลยหรือไม่ โดยมีค่าเป็น True คือไม่ต้องตรงก็ได้ เอาค่าใกล้เคียงมาแสดง และหากเป็น False จะต้องค่าตรงกับที่ระบุใน Lookup_value เท่านั้น

ดังนั้นขั้นแรกก่อนใช้งานฟังก์ชันดังกล่าวต้องมาเตรียมตารางสำหรับ Lookup ก่อน โดนใช้ Sheet ชื่อ Service ที่เพิ่งนำเข้ามานั่นเอง

จากหน้า Sheet Service ให้กดเมนู Data > Filter

เลือกคอลัมน์ C ซึ่งเป็นคอลัมน์ของข้อมูล SEQ ที่เราจะใช้เป็นตาราง Lookup แล้วเลือกเรียงลำดับจากน้อยไปมาก

กลับมาที่ Sheet Onestopservice_export กดเมนู Data > Filter เช่นเดียวกันกับ Sheet Service

ทำการซ่อนข้อมูลที่ไม่จำเป็น เพื่อให้ง่ายต่อการมอง

เลือกคอลัมน์ Q2 (คอลัมน์สุดท้ายของแถวแรกของข้อมูล) กดปุ่ม Fx เพื่อใส่สูตร

ค้นหาคำว่า VLOOKUP หรือเลือกจากกล่ม Lookup & Reference

ค่า Lookup_value คือค่าที่เราจะหา ให้หาจาก SeqID ตรงกับเซลล์ F2

คลิกให้เคอร์เซอร์อยู่ในช่อง Table_array จากนั้น คลิก Sheet Service แล้วลากข้อมูลเพื่อเลือก SEQ มาเป็นตารางอ้างอิง

มีข้อมูลใน Sheet Service อยู่กี่แถวก็ให้เลือกมาให้หมด ซึ่งจากภาพจะเลือกมาแค่คอลัมน์เดียว เพราะ จะเอาแค่คอลัมน์นี้มาแสดงผล ตามสูตรช่อง Col_index_num ก็เลยใส่ 1 คือคอลัมน์ที่ 1 และสุดท้าย Range_lookup ใส่ค่า FALSE เพื่อให้หาข้อมูลที่ตรงกันเท่านั้น จากนั้นกด OK

เราจะเห็นว่าค่าที่ได้คือ #N/A แสดงว่าข้อมูลในแถวนี้ไม่ได้ถูกส่งออกมาในแฟ้ม SERVICE ซึ่งสามารถไปตรวจสอบได้

ที่นี้เราจะทำการแก้ไขสูตรของ VLOOKUP เพื่อจะทำการคัดลอกไปใช้ในแถวอื่นๆ ด้วย ดังนี้

ให้ใส่เครื่องหมาย $ หน้าตัวแสดงตำแหน่งแถวและคอลัมน์ที่เป็น Table_array เพื่อตรึงค่าการค้นหาให้อยู่ช่วงนี้เท่านั้น

ก่อนใส่

หลังใส่

จากนั้นจึงคัดลอกสูตรไปวางในแถวอื่นๆ ได้เลย

จะเห็นว่า ข้อมูลแถวที่มีค่าตาม Lookup_array จะแสดงค่า SEQ ให้เห็น ส่วนข้อมูลแถวไหนไม่พบใน Lookup_array ก็จะแสดงออกมาเป็น #N/A ดังภาพ เราก็สามารถเข้าไปตรวจสอบได้เลยว่าข้อมูลที่ไม่ถูกส่งออก อยู่ที่ไหน และทำการแก้ไขต่อไป