มีการศึกษา (Education) ไม่ได้แปลว่า มีความรู้ (Knowledge)
กระดาษหนึ่งแผ่น..ไม่ได้ทำให้คนฉลาดขึ้น การเรียนรู้..ไม่ได้มีอยู่แต่ในห้องเรียน
การต่อยอดจากสิ่งที่ดี ย่อมได้สิ่งที่ดีกว่า
Group Blog
 
<<
กรกฏาคม 2551
 12345
6789101112
13141516171819
20212223242526
2728293031 
 
13 กรกฏาคม 2551
 
All Blogs
 
การแสดงข้อมูลที่ตรงตามเงื่อนไขที่ระบุ ตอนที่ 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.

ชื่อ :
Comment :
  *ใช้ code html ตกแต่งข้อความได้เฉพาะสมาชิก
 

ครูเอก
Location :
กรุงเทพฯ Thailand

[ดู Profile ทั้งหมด]

ฝากข้อความหลังไมค์
Rss Feed
Smember
ผู้ติดตามบล็อก : 112 คน [?]




เนื้อหาบทความ ภาพประกอบ ไฟล์ตัวอย่าง ทั้งหมดใน blog นี้ "สงวนลิขสิทธิ์ตามพระราชบัญญัติลิขสิทธิ์ พุทธศักราช ๒๕๓๗" อนุญาตให้นำไปเผยแพร่ได้ โดยต้องระบุแหล่งที่มาของเนื้อหาให้ชัดเจน เพื่อแสดงถึงการรับรู้ในความเป็นเจ้าของลิขสิทธิ์ ทั้งนี้ไม่อนุญาตในการนำไปใช้เพื่อการแสวงหาผลกำไรทางธุรกิจ โดยไม่ได้รับความยินยอมเป็นลายลักษณ์อักษร

ส่งข้อความหลังไมค์ถึงครูเอก
MSN : ysamroeng@hotmail.com
ชมรมนักเรียนสาธิตเสริมสมอง มหาวิทยาลัยสุโขทัยธรรมาธิราช
กิตติกรรมประกาศ

ผมใช้คอมพิวเตอร์ครั้งแรก โดยมีหนังสือชื่อ "เรียน DBASE III PLUS ด้วยตนเอง" ของ พ.ต.ประพัฒน์ อุทโยภาศ เป็นเสมือนอาจารย์ และมี บร.โรเบิร์ต ปาแนสโต (ซดบ.) เป็นผู้ให้โอกาส และ้คำแนะนำ ถือเป็นก้าวแรก ที่้ผมจับคอมพิวเตอร์ และสนใจเรียนรู้ มาตั้งแต่วันนั้น นอกจากเรื่อง "การเขียนโปรแกรมด้วย Clipper" แล้ว ผมไม่เคย ไปเรียนคอมพิวเตอร์ จากสถาบันใด อาศัยที่เป็น คนชอบอ่านหนังสือ และซื้อหนังสือเยอะมาก บวกกับลงทุน ซื้อเครื่องไว้ใช้งานเอง (เครื่องแรก Intel 386DX-40) จึงได้ฝึกฝน เรียนรู้ ต่อเนื่องมาจนทุกวันนี้
มีของมาขาย

1. หนังสือ "Excel for HR"


การใช้ไมโครซอฟต์เอ็กเซล ในงาน HR แบบมืออาชีพ พิมพ์ครั้งที่ 2 เป็นหนังสือที่เก็บเกี่ยวประสบการณ์ จากงานจริงๆ มาเป็นวัตถุดิบ เป็นหนังสือคอมพิวเตอร์เล่มแรก ที่เขียนขึ้นมาเพื่อ นักบริการทรัพยากรมนุษย์ (HR) โดยเฉพาะ เป็นตัวอย่างของการใช้โปรแกรม MS Excel ในงานประจำวันของ HR หาซื้อได้ที่ ร้านซีเอ็ดบุ๊ค ทุกสาขา, HR Center, ศูนย์หนังสือ สสท., ศูนย์หนังสือจุฬา, Thailand Book Tower, B2S เป็นต้น
หรือสั่งซื้อโดยตรงได้ที่ 02-347-1066, 081-423-9828
ราคาเล่มละ 200 บาท จัดส่งฟรี

2. CD รวมไฟล์ตัวอย่าง Excel จากงานจริง


มีไฟล์ตัวอย่างมากที่สุด สามารถนำไปใช้งานได้ทันที หรือใช้ศึกษาเทคนิคการเขียนสูตร Excel อัพเดตใหม่ทุกสัปดาห์
ของแท้ไม่มีวางจำหน่ายที่ไหน
สนใจสั่งซื้อโดยตรงที่ 02-347-1066, 081-423-9828
ราคาแผ่นละ 200 บาท ค่าจัดส่งฟรี

หมายเหตุ : ปัจจุบันมีจำหน่ายทั้งสิ้น 3 ชุด ดูรายละเอียดเพิ่มเติมที่ https://sites.google.com/site/excel4hr/product
กิจกรรมของพวกเราที่ผ่านมา

โครงการห้องสมุดเพื่อน้อง รร.บ้านซับงูเหลือม จ.ลพบุรี

โครงการห้องสมุดเพื่อคนพิการ มูลนิธิส่งเสริมและพัฒนาคนพิการ ปากเกร็ด
รูปภาพหรือข้อความแสดงความเห็น เกิดจากการแสดงความคิดเห็นโดยอิสระ ของบุคคลทั่วไป และถูกส่งขึ้นแสดงในหน้า blog โดยอัตโนมัติ เจ้าของ blog มิได้มีส่วนรู้เห็น หรือพิสูจน์ข้อเท็จจริงใดๆ ทั้งสิ้น อีกทั้งไม่จำเป็นต้องร่วมรับผิดชอบ ต่อทุกความคิดเห็นใดๆ
Friends' blogs
[Add ครูเอก's blog to your web]
Links
 
MY VIP Friend


 Pantip.com | PantipMarket.com | Pantown.com | © 2004 BlogGang.com allrights reserved.