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

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

วันนี้ได้รับโทรศัพท์สายด่วนจากน้องปู สอบถามปัญหาเกี่ยวกับ 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)), LOOKUP(9.99999999999999E+307,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(A$5:A5)<=MAX(Sheet1!$E:$E), LOOKUP(ROWS(A$5:A5), 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) มาแสดง

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

สนใจดาวน์โหลดไฟล์ตัวอย่าง กรุณาเข้าไปที่ //www.e-hrit.com/it4hr/ อยู่ในห้อง Excel ชื่อหัวข้อ "การแสดงข้อมูลที่ตรงตามเงื่อนไขที่ระบุ"

Create Date : 13 กรกฎาคม 2551

Last Update : 31 มกราคม 2552 15:52:56 น.

0 comments

Counter : 6142 Pageviews.