การแสดงข้อมูลที่ตรงตามเงื่อนไขที่ระบุ

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


จากการสอบถามในเบื้องต้น เกี่ยวกับลักษณะของไฟล์ข้อมูลในปัจจุบัน ผมก็ลองสร้างข้อมูลตัวอย่างคร่าวๆ (จากคำบอกเล่า) ออกมาเป็นดังรูปที่ 1


รูปที่ 1 แสดงตัวอย่างการเก็บข้อมูลในปัจจุบัน


จากรูปที่ 1 ต้องหาระยะเวลาการถือครองบัตรของแต่ละคนว่า ตั้งแต่รับบัตรไป จนถึงเวลาส่งคืน รวมเป็นระยะเวลากี่ชั่วโมง ดังนั้นที่ D4 พิมพ์สูตร =C4-B4 หรือ วันเวลาที่คืนบัตร – วันเวลาที่รับบัตร นั่นเอง


รูปที่ 2 การจัดรูปแบบเพื่อแสดงผลลัพธ์เป็นจำนวนชั่วโมง


ผลลัพธ์ที่ได้จะมีรูปแบบเหมือนกับในคอลัมน์ B และ C ให้เราเข้าไปเปลี่ยนรูปแบบเซลล์ เพื่อให้แสดงเฉพาะ จำนวน ชั่วโมง : นาทีเท่านั้น โดยการคลิกขวาที่ D4 เลือก Format cells… แล้วจัดรูปแบบเป็น [h]:mm ดังรูปที่ 2

เสร็จแล้วก็คัดลอกสูตรลงมาจนครบทุกบรรทัด จะได้ผลลัพธ์ดังรูปที่ 3



รูปที่ 3 แสดงระยะเวลาถือครองบัตรของแต่ละคน


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


รูปที่ 4 การจัดรูปแบบตามเงื่อนไข


1. เลือกช่วงข้อมูล D4 ถึง D10
2. คลิกที่เมนู Format > Conditional Formatting…
3. ในช่อง Condition 1 เลือก Formula Is
4. ในช่องว่างๆ ทางขวา พิมพ์สูตร **
=AND(D4>=(2/24),D4<(14/24))

5. คลิกที่ปุ่ม Format
6. ในแถบ Font ให้เลือกสีตัวอักษรเป็นสีขาว
7. ในแถบ Patterns ให้เลือกสีพื้นเป็นสีแดง แล้วคลิก OK เพื่อกลับมาหน้าเดิม
8. คลิก OK อีกครั้งหนึ่ง จะได้ผลลัพธ์ดังรูปที่ 5


รูปที่ 5 ผลลัพธ์หลังจากกำหนดรูปแบบเซลตามเงื่อนไข


** หมายเหตุ
เนื่องจาก 1 วันมี 24 ชั่วโมง ดังนั้น 2 ชั่วโมงจึงเท่ากับ 2/24 และ 14 ชั่วโมงจึงเท่ากับ 14/24 นั่นเอง


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


เพื่อที่จะสามารถ แยกเฉพาะรายการที่ตรงตามเงื่อนไขออกมาได้ เราจะต้องหาวิธีที่จะระบุ ว่าข้อมูลใดบ้างที่ต้องการ หรือข้อมูลใดบ้างที่ไม่ต้องการ โดยใช้วิธีการต่อไปนี้

1. ที่เซล E1 ใส่เลข 0 (ศูนย์)
2. ที่เซล E4 พิมพ์สูตร
=IF(AND(D4>=(2/24),D4<(14/24)), MAX(E$1:E3)+1, "")

3. เสร็จแล้วทำการคัดลอกสูตรลงมา จนครบข้อมูลทุกบรรทัด จะได้ผลลัพธ์ดังรูปที่ 6


รูปที่ 6 การเขียนสูตรเพื่อระบุข้อมูลเฉพาะแถวที่ต้องการ


อธิบายสูตร 
ในสูตรข้างต้น หมายความว่า ถ้าผลลัพธ์ในคอลัมน์ D แถวใดๆ ตรงตามเงื่อนไขที่ระบุ (มากกว่าหรือเท่ากับ 2 ชม. และน้อยกว่า 14 ชม.) ก็ให้ค้นหาตัวเลขที่มากที่สุด ในคอลัมน์ E ตั้งแต่ E1 จนถึงแถวก่อนหน้า แล้วบวกเพิ่มค่าอีก 1 แต่ถ้าไม่ตรงตามเงื่อนไข ก็ไม่ต้องใส่ค่าใดๆ


ต่อไป เราก็จะดึงข้อมูลเฉพาะแถวที่มีตัวเลขในคอลัมน์ E ไปแสดงเป็น Report ในชีทใหม่ ผมจึงไปสร้างรูปแบบรายงานไว้ในชีทที่ 2 เพื่อจะใช้สรุปข้อมูลที่ต้องการ ดังรูปที่ 7


รูปที่ 7 แสดงตัวอย่างรายงานที่ต้องการ


หลังจากสร้างหัวรายงาน ดังในรูปที่ 7 แล้ว เราจะเขียนสูตรเพื่อดึงข้อมูล เฉพาะแถวที่ต้องการจากชีทที่ 1 มาแสดง โดยการ

1. ที่เซลล์ A5 ของชีทที่ 2 พิมพ์สูตร
=IF(ROWS($5:5)<=MAX(Sheet1!$E:$E), LOOKUP(ROWS($5:5), Sheet1!$E:$E, Sheet1!A:A),"")

2. แล้วคัดลอกสูตรไปยัง B5, C5 และ D5 ตามลำดับ

3. แต่รูปแบบผลลัพธ์ที่ออกมาอาจจะดูแปลกๆ ไม่ต้องตกใจครับ เราสามารถเข้าไปกำหนดรูปแบบใหม่ ให้เหมือนกับในชีท 1 โดยคอลัมน์ B และ C กำหนดรูปแบบเป็น d/m/yyyy h:mm และคอลัมน์ D กำหนดรูปแบบเป็น [h]:mm

4. เมื่อได้รูปแบบเซลล์ผลลัพธ์ตามต้องการแล้ว ก็ทำการคัดลอกสูตรจากบรรทัดที่ 5 (A5:D5) ลงมาข้างล่าง จำนวนบรรทัดไม่น้อยกว่า จำนวนข้อมูลที่คาดว่าจะมี (เกินไว้ไม่เป็นไรครับ เพราะสูตรดังกล่าวจะแสดงข้อมูลเท่าที่มีข้อมูลจริงเท่านั้น) ก็จะได้ผลลัพธ์ดังในรูปที่ 7 ซึ่งสามารถสั่ง Print ไปส่งหัวหน้าได้เลย


อธิบายสูตร 
ในสูตรข้างต้น หมายความว่า ถ้าจำนวนบรรทัดที่แสดงรายงานนั้น ยังน้อยกว่าหรือเท่ากับค่าสูงสุด (จำนวนรายการที่พบ) ในคอลัมน์ E ของชีท1, ก็ให้แสดงข้อมูลเฉพาะแถวที่ตรงกับตัวเลขในคอลัมน์ E ของชีท1 โดยเลือกว่าจะเอาคอลัมน์ไหน (จากชีท 1) มาแสดง 


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

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

คำตอบก็คือ "ได้" แต่...

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

ตัวอย่างเช่น ถ้าวันนี้หัวหน้าต้องการดูข้อมูลของคนที่ถือครองบัตร เป็นระยะเวลาตั้งแต่ 2 ชั่วโมงขึ้นไป แต่ไม่ถึง 14 ชั่วโมง แต่วันพรุ่งนี้อาจจะอยากทราบว่า ใครถือครองบัตรตั้งแต่ 60 ชั่วโมงขึ้นไป แต่ไม่ถึง 120 ชั่วโมงบ้าง เราจะทำอย่างไร?


รูปที่ 8 เพิ่มช่องสำหรับให้ปรับเปลี่ยนเงื่อนไขได้


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

เราจำเป็นต้องปรับแก้สูตรเดิมในเซลล์ E6 เพื่อให้สอดคล้องกับลักษณะของข้อมูลใหม่ ดังนี้

=IF(AND(D6>=($D$2/24),D6<($D$3/24)), MAX(E$1:E5)+1,"")

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


เสร็จแล้วก็คัดลอกสูตรลงมาจนครบทุกแถวเช่นเดิม จากนั้นก็ทดลองเปลี่ยนเงื่อนไข เช่น ใส่ 60 ที่ D2 และใส่ 120 ที่ D3 แล้วดูผลการเปลี่ยนแปลงที่เกิดขึ้น

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

โดยทำการเลือกช่วงข้อมูล D4 ถึง D10 แล้วเข้าเมนู Format > Conditional Formatting… จากนั้นก็ให้ทำการแก้ไขสูตรเดิม ให้เป็นดังนี้

=AND(D6>=($D$2/24),D6<($D$3/24))

แล้วคลิก OK


สำหรับหน้ารายงานในชีทที่ 2 นั้น ก็ไม่ต้องแก้ไขสูตรใดๆ เพราะสูตรที่เขียนไว้เดิมนั้น ก็ยังสามารถแสดงผลลัพธ์ได้ถูกต้อง


แต่อย่างไรก็ตาม เนื่องจากเราทำให้ในชีทที่ 1 สามารถปรับเปลี่ยนเงื่อนไขจำนวนชั่วโมงได้ ดังนั้น เวลาจะออกรายงาน หัวกระดาษก็ควรแสดงผล ให้สอดคล้องกับเงื่อนไขด้วย มิฉะนั้นก็จะแสดงข้อความเดิมๆ ตลอด (ระยะเวลาตั้งแต่ 2 ชั่วโมง แต่ไม่ถึง 14 ชั่วโมง)


ที่เซลล์ A2 ของชีทที่ 2 ให้พิมพ์สูตร ดังนี้
="ระยะเวลาตั้งแต่ "&Sheet1!D2&" ชั่วโมง แต่ไม่ถึง "&Sheet1!D3&" ชั่วโมง"


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



รูปที่ 9 หัวรายงานที่แสดงข้อความตามเงื่อนไขที่เปลี่ยนไป


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