ฟังก์ชัน INDIRECT และตัวอย่างการใช้งาน

มีคำถามว่า Indirect หมายความอย่างไร และ มีวิธีการใช้อย่างไร?


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





ตัวอย่างที่1

ถ้าในเซล B6 เราพิมพ์สูตร =A1+A2 ซึ่งจะได้ผลลัพธ์เท่ากับ 15 แต่ถ้าเราจะเปลี่ยนสูตรเป็น =A1+A3 หรือ A1+A4 เราก็ต้องมาแก้ไขสูตรใหม่ทุกครั้ง

ซึ่งจากในรูปตัวอย่างข้างบนจะเห็นว่า เซล A1 เป็นเซลหลักที่เราจะนำไปบวกกับ A2, A3 หรือ A4 ดังนั้น ผมจะเขียนสูตรใหม่เป็น=A1+INDIRECT(B1) แล้วใน B1 ผมก็จะใส่ข้อความว่า A2, A3 หรือ A4 (อาจทำเป็น Drop down list) ผลลัพธ์ที่ได้ใน B6 ก็จะเปลี่ยนไป ซึ่งขึ้นอยู่กับว่าเราพิมพ์อะไรใน B1 โดยที่เราไม่ต้องไปแก้ไขสูตรใน B6 เลย



ตัวอย่างที่2

ตัวอย่างต่อไป (อ้างอิงจากรูปข้างบนเช่นกัน) ถ้าในเซล B7 ผมต้องการพิมพ์สูตร =A1+A ตัวสุดท้ายที่มีข้อมูล หมายความว่า ถ้ามีข้อมูลถึง A4 สูตรก็คือ =A1+A4 หรือถ้ามีข้อมูลถึง A7 สูตรก็ต้องเปลี่ยนเป็น =A1+A7 เป็นต้น

ในกรณีนี้ เราก็สามารถใช้ INDIRECT มาช่วยได้เช่นกัน โดยการพิมพ์สูตร =A1+INDIRECT("A"&COUNT(A:A)) โดยที่...

COUNT(A:A) จะนับเซลที่มีข้อมูลตัวเลขในคอลัมน์ A ว่ามีกี่รายการ ผลลัพธ์คือ 4

"A"&COUNT(A:A) ก็เสมือนเป็นการเอาตัวอักษร "A" ไปเชื่อมต่อค่าตัวเลขที่ได้ ผลลัพธ์คือ A4

INDIRECT("A"&COUNT(A:A)) จะแปลงค่าที่อยู่ในเซลให้ใช้อ้างอิงได้ในสูตร

ดังนั้น =A1+INDIRECT("A"&COUNT(A:A)) จึงมีความหมายเหมือนกับ =A1+A4 นั่นเอง

ให้เราทดลอง เพิ่มหรือลด ข้อมูลในคอลัมน์ A ก็จะเห็นความแตกต่างครับ



ตัวอย่างที่3

ลองดูอีกสักตัวอย่างนะครับ (อ้างอิงจากรูปข้างบนเช่นกัน) ถ้าในเซล B7 ผมต้องการพิมพ์สูตร =SUM(A1:Aตัวสุดท้ายที่มีข้อมูล)หมายความว่า ถ้ามีข้อมูลถึง A4 สูตรก็คือ =SUM(A1:A4) หรือถ้ามีข้อมูลถึง A7 สูตรที่ต้องการก็คือ =SUM(A1:A7) เป็นต้น

ในกรณีนี้ เราก็สามารถใช้ INDIRECT มาช่วยได้อีกเช่นกัน โดยการพิมพ์สูตร =SUM(A1:INDIRECT("A"&COUNT(A:A))) โดยที่...

COUNT(A:A) จะนับเซลที่มีข้อมูลตัวเลขในคอลัมน์ A ว่ามีกี่รายการ ผลลัพธ์คือ 4

"A"&COUNT(A:A) ก็เสมือนเป็นการเอาตัวอักษร "A" ไปเชื่อมต่อค่าตัวเลขที่ได้ ผลลัพธ์ คือ A4

INDIRECT("A"&COUNT(A:A)) จะแปลงค่าที่อยู่ในเซลให้ใช้อ้างอิงได้ในสูตร

ดังนั้น =SUM(A1:INDIRECT("A"&COUNT(A:A))) จึงมีความหมายเหมือนกับ =SUM(A1:A4) นั่นเอง

ทดลอง เพิ่มหรือลด ข้อมูลในคอลัมน์ A ก็จะเห็นความแตกต่างของผลลัพธ์เช่นกันครับ

Read More

ตารางเตือนวันครบกำหนดทดลองงาน

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

รูปที่ 1 ตารางแจ้งเตือนวันครบกำหนดทดลองงาน

เงื่อนไขการทำงาน

1. วันครบทดลองงาน คือ วันเริ่มงาน + 119 วัน (รวมเป็น 120 วัน)
2. ถ้าวันครบทดลองงาน "ตรงกับ" วันปัจจุบัน ให้แสดงคำว่า “ครบแล้ว” หรือ “ครบวันนี้” ในคอลัมน์สถานะ ด้วยอักษรสีแดงตัวหนา
3. ถ้าวันปัจจุบัน "เลย" วันครบทดลองงานมาแล้ว ไม่ต้องแสดงข้อความใดๆ ในคอลัมน์สถานะ
4. ถ้าวันปัจจุบัน "ยังไม่ถึง" วันครบทดลองงาน ให้แสดงจำนวนวันที่เหลือในคอลัมน์คำเตือน และ
   • ถ้าเหลือเวลามากกว่า 15 วัน ให้แสดงคำว่า “ช่วงทดลองงาน” ในคอลัมน์สถานะ
   • ถ้าเหลือระยะเวลาไม่เกิน 15 วัน ให้แสดงคำว่า “ช่วงประเมินผล” ในคอลัมน์สถานะ ด้วยอักษรสีขาวบนพื้นสีแดง พร้อมทั้งกำหนดรูปแบบเซลล์คำเตือน ให้เป็นอักษรสีขาวพื้นสีแดงด้วยเช่นกัน

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


สร้างตารางการแจ้งเตือน

รูปที่ 2 ตารางแจ้งเตือนวันครบกำหนดทดลองงาน

ขอให้ลองทำตารางข้อมูล ดังตัวอย่างในรูปที่ 2 เพื่อจะได้ลองฝึกปฏิบัติไปพร้อมๆ กันกับที่ผมจะอธิบายแต่ละขั้นตอน
ก่อนอื่น...ที่เซลล์ F1 พิมพ์สูตร =TODAY()
เพื่อให้แสดงวันที่ปัจจุบันเสมอ (แต่...อย่าลืมตั้งวันที่ในเครื่องคอมพิวเตอร์ ให้ตรงกับวันปัจจุบันด้วยนะครับ เดี๋ยวจะยุ่ง)


หาวันครบทดลองงาน

จากที่โจทย์กำหนด วันครบทดลองงานคือ วันเริ่มงาน + 119 ดังนั้นในเซลล์ G3 พิมพ์ =F3+119 เสร็จแล้วก็คัดลอกสูตรจาก G3 ลงมาถึง G10


การแสดงข้อความเตือนในคอลัมน์ "คำเตือน"

โจทย์บอกว่า “ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน ให้แสดงจำนวนวันที่เหลือในคอลัมน์คำเตือน” ซึ่งวันที่เหลือก็คือ อีกกี่วันจึงจะครบกำหนดทดลองงานนั่นเอง ดังนั้น โดยพื้นฐานแล้ว จำนวนวันที่เหลือ (I3) ก็คือ วันครบทดลองงาน (G3) – วันปัจจุบัน (F$1)

แต่หากพิจารณาต่อไปว่า ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน เมื่อนำวันครบทดลองงานมาลบวันปัจจุบัน ก็จะได้เป็น “ค่าบวก”แต่ในทางกลับกัน ถ้าวันปัจจุบันเลยวันครบทดลองงานแล้ว ก็จะได้เป็น “ค่าลบ”

เราจึงต้องตั้งเงื่อนไขว่า "ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน ก็ให้เอา วันครบทดลองงาน – วันปัจจุบัน แล้วแจ้งเตือนว่าเหลืออีกกี่วัน, แต่ถ้าวันปัจจุบันเท่ากับวันครบทดลองงานพอดี หรือเลยวันครบทดลองงานไปแล้ว ก็ไม่ต้องแจ้งเตือนใดๆ" ดังนั้นในเซลล์ I3 จึงเขียนสูตรดังนี้

=IF(F$1 < $G3,"เหลืออีก "&$G3-F$1&" วัน","") 

แล้วคัดลอกสูตรจาก I3 ลงมาถึง I10 


ผังแนวคิดการแสดงข้อความในคอลัมน์ "สถานะ"

รูปที่ 3 แผนผังแสดงแนวคิดในการแสดงข้อความในคอลัมน์ “สถานะ”

จากแผนผังจะเห็นว่า ในขั้นตอนแรกเราจะตรวจสอบดูก่อนว่า วันปัจจุบัน (F$1) เลยกำหนดครบทดลองงาน (G3) หรือยัง ซึ่งถ้าเลยมาแล้ว ก็ไม่ต้องแสดงข้อความใดๆ (หรือใครจะให้แสดงข้อความว่า “เกินวันแล้ว” ก็ได้เหมือนกัน)

ถ้าวันปัจจุบัน (F$1) ยังไม่เกินกำหนดครบทดลองงาน (G3) แต่ตรงกับวันครบทดลองงานพอดี (F$1=G3) ก็ให้แสดงคำว่า “ครบวันนี้”

ส่วนเงื่อนไขสุดท้ายคือ ถ้าวันปัจจุบัน (F$1) ยังไม่ถึงกำหนดทดลองงาน (G3) ให้ตรวจสอบเงื่อนไขเพิ่มเติมอีกว่า ยังเหลืออีกกี่วัน (G3-F$1) ซึ่งถ้าเหลือมากกว่า 15 วัน ให้แสดงข้อความว่า “ช่วงทดลองงาน” แต่ถ้าวันที่ยังเหลือนั้นน้อยกว่าหรือเท่ากับ 15 วันพอดี ก็ให้แสดงข้อความว่า “ช่วงประเมินผล”

รวมแล้วมีด้วยกันทั้งสิ้น 3 เงื่อนไข เราจึงใช้ฟังก์ชัน IF ซ้อนกัน 3 ชั้น โดยพิมพ์สูตรที่ H3 ดังนี้

=IF(F$1>G3,"",IF(F$1=G3,"ครบวันนี้",
IF(G3-F$1>15,"ช่วงทดลองงาน","ช่วงประเมินผล")))

แล้วคัดลอกสูตรจาก H3 ลงมาถึง H10


การกำหนดรูปแบบเซลล์ตามเงื่อนไข

ผมขอแนะนำ การกำหนดรูปแบบตามเงื่อนไข (Conditional Formatting) เพื่อนำมาประยุกต์ใช้ในหัวข้อนี้ เนื่องจากโจทย์กำหนดเงื่อนไขรูปแบบเซลล์ ในคอลัมน์สถานะไว้ว่า ถ้าแสดงข้อความว่า “ครบวันนี้” ให้รูปแบบอักษรเป็นตัวหนาสีแดง แต่ถ้าแสดงข้อความว่า “ช่วงประเมินผล” ให้รูปแบบอักษรเป็นสีขาวบนพื้นสีแดง ดังนั้นรูปแบบในคอลัมน์สถานะจึงมี 2 เงื่อนไข

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


การกำหนดรูปแบบเซลล์ในคอลัมน์ "สถานะ"

รูปที่ 4 การกำหนดรูปแบบเซลล์ในคอลัมน์ “สถานะ” 

1. เลือกช่วงเซลล์ H3:H10
2. คลิกเมนู Format -> Conditional Formatting (รูปแบบ -> การจัดรูปแบบตามเงื่อนไข)
3. กำหนดเงื่อนไขแรก โดยเลือกเงื่อนไขเป็น Formula is (สูตรคือ) 
4. พิมพ์สูตรลงในช่องว่างทางขวา ดังนี้
   =F$1=G3 (วันปัจจุบันเท่ากับวันเริ่มงาน)
5. คลิกปุ่ม Format… (รูปแบบ..)
6. ตั้งค่ารูปแบบตัวอักษร ให้เป็นตัวหนา สีแดง
7. คลิกปุ่ม OK (ตกลง) เพื่อกลับมาที่หน้าเดิม
8. เพิ่มเงื่อนไขที่สอง โดยการคลิกที่ปุ่ม Add>> (เพิ่ม>>) ที่อยู่ด้านล่าง และเลือกเงื่อนไขเป็น Formula is (สูตรคือ)
9. พิมพ์สูตรลงในช่องว่างทางขวา ดังนี้
   =AND(G3-F$1<=15,G3-F$1>0) 
   (วันครบทดลองงาน ลบ วันปัจจุบัน น้อยกว่าหรือเท่ากับ 15 วัน และมีค่ามากกว่าศูนย์)
10. คลิกปุ่ม Format… (รูปแบบ..)
11. ตั้งค่ารูปแบบตัวอักษร ให้เป็นสีขาว และตั้งค่าสีพื้นให้เป็นสีแดง
12. คลิกปุ่ม OK (ตกลง) เพื่อกลับมาที่หน้าเดิมอีกครั้ง
13. คลิกปุ่ม OK (ตกลง) อีกครั้ง เพื่อจบการตั้งค่า

การกำหนดรูปแบบเซลล์ในคอลัมน์ "คำเตือน"

รูปที่ 5 การกำหนดรูปแบบเซลล์ในคอลัมน์ “คำเตือน”

1. เลือกช่วงเซลล์ I3:I10
2. คลิกเมนู Format -> Conditional Formatting (รูปแบบ -> การจัดรูปแบบตามเงื่อนไข)
3. เลือกเงื่อนไขเป็น Formula is (สูตรคือ)
4. พิมพ์สูตรลงในช่องว่างทางขวา ดังนี้
   =AND( G3-F$1<=15,G3-F$1>0)(วันครบทดลองงาน ลบ วันปัจจุบัน น้อยกว่าหรือเท่ากับ 15 วัน และมีค่ามากกว่าศูนย์)
5. คลิกปุ่ม Format… (รูปแบบ..)
6. ตั้งค่ารูปแบบตัวอักษร ให้เป็นสีขาว และตั้งค่าสีพื้นให้เป็นสีแดง
7. คลิกปุ่ม OK (ตกลง) เพื่อกลับมาที่หน้าเดิม
8. คลิกปุ่ม OK (ตกลง) อีกครั้ง เพื่อจบการตั้งค่า จะได้รูปแบบผลลัพธ์ดังรูปที่ 1 นั่นเอง

ทดลองทำตามทีละขั้นตอนนะครับ หวังว่าจะมีประโยชน์สำหรับทุกท่าน ในการนำไปประยุกต์ใช้งานจริง ในเรื่องนี้มีข้อแม้อยู่เรื่องหนึ่ง เนื่องจากมีการใช้ฟังก์ชัน TODAY ในการคำนวณ ดังนั้น การบันทึกวันเข้างาน จึงต้องบันทึกในรูปแบบ ปี ค.ศ. เท่านั้น (อ่านเพิ่มเติมเรื่อง การกำหนดรูปแบบเซลล์ประเภทวันที่ (Date format) ซึ่งผมเขียนไว้ก่อนหน้านี้แล้ว) และวันที่เข้างานนั้น จะต้องเป็นรูปแบบเดียวกันกับวันที่ ที่ได้จากฟังก์ชัน TODAY ซึ่งอาจจะเป็นรูปแบบ d/m/yyyy หรือ m/d/yyyy สุดแล้วแต่การตั้งค่าในแต่ละเครื่อง

Read More

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

วันนี้ได้รับโทรศัพท์สายด่วนจากน้องปู สอบถามปัญหาเกี่ยวกับ 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 หัวรายงานที่แสดงข้อความตามเงื่อนไขที่เปลี่ยนไป


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

Read More