การแสดงข้อมูลที่ตรงตามเงื่อนไขที่ระบุ ตอนที่ 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 ชื่อหัวข้อ "การแสดงข้อมูลที่ตรงตามเงื่อนไขที่ระบุ" |
บทความทั้งหมด
|
 |
|
ผู้ติดตามบล็อก : 112 คน [