BlogGang.com : : ครูเอก : สูตรการตัดเกรดคะแนนโดยใช้ฟังก์ชัน LOOKUP

สูตรการตัดเกรดคะแนนโดยใช้ฟังก์ชัน LOOKUP

มีคนส่งคำถามมาให้ผม ดังนี้

ขอทราบสูตรการตัดเกรดที่มี 10 ระดับด้วยครับ คือผมทดลองใช้ฟังก์ชัน IF แล้ว ไม่สามารถทำได้ เพราะติดข้อจำกัดที่ฟังก์ชัน IF ซ้อนกันได้ไม่เกิน 7 ชั้น แต่ เนื่องจากงานของผมมีเกรด 10 ระดับ (ดังรูปที่แนบมา) เคยได้ยินมาว่า มีวิธีที่ทำให้ฟังก์ชัน IF สามารถคำนวณซ้อนกันได้มากกว่า 7 ชั้น ขอคำแนะนำด้วยครับ

รูปที่ 1

ใน EXCEL 2003 และรุ่นก่อนหน้านั้น มีข้อจำกัดว่า ฟังก์ชันซ้อนกันได้ไม่เกิน 7 ชั้น ผู้ผลิตเองก็คงคิดว่า ไม่น่าจะมีการใช้ฟังก์ชัน ซ้อนกันเกินกว่า 7 ชั้น เพราะลำพัง 2–3 ชั้น ก็แทบจะไล่สูตรกันไม่ถ้วนแล้ว แต่ก็ยังไม่พอกับความต้องการ ของผู้ใช้บางท่าน ดังนั้นใน EXCEL 2007 จึงเพิ่มความสามารถ ในการซ้อนฟังก์ชันได้ถึง 64 ชั้น ไล่สูตรกันให้ตาลายไปข้างหนึ่งเลยล่ะ คราวนี้

แต่ถ้าจำเป็นต้องซ้อนฟังก์ชันมากกว่า 7 ชั้น ก็พอจะมีวิธีการพลิกแพลงอยู่บ้าง แต่ผมไม่ใคร่จะแนะนำให้ใช้ โดยเฉพาะคนที่มีพื้นฐาน การใช้สูตรและฟังก์ชัน EXCEL ที่ยังไม่ค่อยแข็งแรงนัก ก็อย่างที่บอกไปแล้วว่า แค่สูตรที่มีการซ้อนฟังก์ชันเพียง 2–3 ชั้น ก็ยังไล่กันไม่ค่อยถูก งงแล้ว งงอีก ดังนั้นการซ้อนฟังก์ชันที่มากกว่า 7 ชั้น ก็ขอให้ลืมไปได้เลย เปลี่ยนความคิดไปศึกษาสูตรแบบอื่น ที่สั้นและกระชับจะดีกว่า

รูปที่ 2

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

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

ถึงแม้เราจะสามารถใช้ฟังก์ชัน IF เพื่อตรวจสอบเงื่อนไขที่มีมากถึง 10 เงื่อนไขได้ก็ตาม แต่การเขียนสูตรแบบนี้จะยาวมาก ดังนั้นในโจทย์ข้อนี้ ผมแนะนำให้เปลี่ยนจาก การใช้ฟังก์ชัน IF ไปใช้ฟังก์ชัน LOOKUP แทน เพราะสูตรจะสั้นและกระชับกว่า

อย่าไปยึดติดว่า การคิดเกรด หรือการทดสอบเงื่อนไข (ถ้า....) จะต้องใช้ฟังก์ชัน IF เสมอไป เพราะการเขียนสูตร เพื่อให้ได้มาซึ่งผลลัพธ์ใดๆ ใน EXCEL นั้น อาจจะมีวิธีการมากกว่า 1 วิธี ส่วนจะเลือกใช้วิธีไหนนั้น ขึ้นอยู่กับประสบการณ์ในการเรียนรู้ EXCEL ของผู้ใช้แต่ละคน

ดังนั้น จากรูปที่ 1 (ในโจทย์) ที่ตำแหน่ง E2 พิมพ์สูตรดังนี้

=LOOKUP(D2, $B$11:$B$20, $E$11:$E$20)

หรือถ้าต้องการแสดงผลลัพธ์เป็นตัวเลข ก็แก้ไขสูตรให้เป็นดังนี้

=LOOKUP(D2, $B$11:$B$20, $F$11:$F$20)

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

รูปที่ 3

คำแนะนำเพิ่มเติม

การตัดเกรดโดยใช้ฟังก์ชัน LOOKUP และสร้าง ตารางเงื่อนไขคะแนน แยกต่างหากนั้น มีข้อดีคือว่า หากต้องการปรับช่วงระดับคะแนนใหม่ สามารถปรับแก้ตัวเลขในตารางได้เลย โดยไม่ต้องไปแก้ไขในสูตร

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

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

Create Date : 19 มกราคม 2552

Last Update : 19 มกราคม 2552 3:21:17 น.

22 comments

Counter : 21757 Pageviews.

ตี3ยังไม่นอนอีกหรอ
นอนเช้าเลยนะเนี่ย
ระวังเสียสุขภาพนะจ๊ะ

โดย: kai (aitai

) วันที่: 19 มกราคม 2552 เวลา:10:11:32 น.

ขอบคุณสำหรับไอเดีย

โดย: น้ำเค็ม IP: 124.157.180.211 วันที่: 19 มกราคม 2552 เวลา:18:23:08 น.

ตามมาขอบคุณ สำหรับข้อมูลดีดี ค่ะ คุณครูเอก

โดย: puppyjrnaja IP: 58.8.252.94 วันที่: 20 มกราคม 2552 เวลา:0:01:28 น.

สวัสดีค่ะครูเอก..

ขอเวลานอกหน่อยได้ไหมค่ะ..

อยากจะถามว่า ถ้าครูเอกทำเสื้อยืดใส่พร้อมกับแฟน

อยากจะเขียนคำพูดอะไรไว้บนเสื้อบ้าง

ดูตัวอย่างด้านล่างนี้นะค่ะ..

ขอให้มีความสุขมากๆนะค่ะ

โดย: อ้อมแอ้ม (คนผ่านทางมาเจอ

) วันที่: 20 มกราคม 2552 เวลา:8:34:20 น.

อ้าวคุณแซมไปไหนละ ยังไม่ได้ชมเลย
เห็นแว๊บๆ

โดย: kai (aitai

) วันที่: 20 มกราคม 2552 เวลา:10:49:40 น.

ตอบคุณอ้อมแอ้มแทนครูเอกฮะ

เขียนว่า "ทิ้งกรู มรึงตาย"

ก๊ากกกกกกกกกกกกก

โดย: ไมรอน IP: 202.90.6.36 วันที่: 20 มกราคม 2552 เวลา:15:34:58 น.

^
^
^

โดย: ครูเอก

วันที่: 20 มกราคม 2552 เวลา:15:40:01 น.

สวัสดีค่ะ ครูเอก
เดินสายขอบคุณผู้ที่ไปอวยพรวันเกิด เลยมาช้าไปหน่อยนะคะ

ขอบคุณมากๆนะคะที่ไปอวยพรวันเกิด

พรทุกประการ ขอส่งกลับไปยังผู้ให้ด้วยนะคะ

โดย: ม่านฟ้านาคราช

วันที่: 21 มกราคม 2552 เวลา:15:33:09 น.

สวัสดีครับครูเอก ผมมีปัญหามาถามนิดหน่อยครับ
คือว่า ผม copy files มา แต่ต้องมาจัดหน้าใหม่ทุกครั้งเลย
ไม่รู้ว่าเป็นเพราะอะไร ไม่เข้าใจ ปัญหาที่ตามมาคือ เมื่อแก้
เสร็จแล้ว จัดเสร็จแล้ว ส่งคืน คนที่รับก็ต้องจัดใหม่อีก
ทำให้งานล้าช้าไปอีก ถูกเจ้านายบ่นเป็นประจำ ช่วยหน่อยครับ sb_tt_12@hotmail.com

โดย: salomon12 IP: 61.7.185.121 วันที่: 21 มกราคม 2552 เวลา:17:17:20 น.

ตอบคุณ salomon12

ผมเดาว่า คุณคงจะหมายถึงไฟล์ MS Word เพราะหลายๆ คนก็มักจะเจอปัญหาแบบนี้บ่อยๆ ทั้งนี้เป็นเพราะ ไฟล์แม่แบบ (Documents Template) ที่มีชื่อว่า Normal.dot ที่อยู่ในแต่ละเครื่อง ตั้งค่าไว้ไม่เหมือนกัน ดังนั้น ไฟล์ที่สร้างจากเครื่องที่ใช้ไฟล์แม่แบบอย่างหนึ่ง พอเอาไปเปิดในเครื่องที่ใช้ไฟล์แม่แบบอีกอย่างหนึ่ง ก็จะทำให้รูปแบบที่จัดไว้ผิดเพี้ยนไป

วิธีการแก้ไขโดยทั่วไป มีอยู่ด้วยกัน 2 วิธีคือ

1. ถ้าเอกสารนั้นต้องการเพียงเพื่อ ส่งไปให้ผู้ใช้คนอื่นๆ ดูเท่านั้น หรือให้เขาไป print ได้ แต่ไม่ต้องแก้ไขเปลี่ยนแปลงข้อมูลใดๆ ก็มักจะแปลงให้เป็นไฟล์ .pdf เสียก่อน

2. ในหน่วยงาน หรือในองค์กรเดียวกัน ถ้าจำเป็นต้องมีการแลกเปลี่ยนไฟล์กันอยู่บ่อยๆ เช่น เอาไฟล์เครื่องนี้ไปทำต่อเครื่องโน้น แล้วย้ายกลับมาทำเครื่องนี้ สลับเครื่องไปมาอยู่บ่อยๆ แนะนำว่า ให้ใช้ไฟล์แม่แบบเอกสาร Normal.dot ตัวเดียวกัน

โดยไฟล์แม่แบบที่ว่านี้ โดยทั่วไปจะอยู่ที่ C:\\Documents and Settings\\ชื่อผู้ใช้งาน\\Application Data\\Microsoft\\Templates\\Normal.dot
ให้เอาไฟล์เครื่องใดเครื่องหนึ่งเป็นต้นแบบ แล้ว Copy ไฟล์ Normal.dot จากเครื่องต้นแบบ ไปแทนที่ในทุกๆ เครื่องที่เกี่ยวข้อง ต่อไปเวลาเอาไฟล์ไปเปิดที่เครื่องไหนๆ ก็จะเป็นรูปแบบเดียวกันครับ

ปล. โฟลเดอร์ Application Data โดยปกติจะมีสถานะเป็น Hidden ถ้ามองไม่เห็น ให้เข้าไปที่เมนู Tools > Folder Options.. > View > แล้วติ๊กเลือก Show hidden files and folder > OK

โดย: ครูเอก

วันที่: 22 มกราคม 2552 เวลา:0:41:19 น.

อัพบล๊อกแว้ว

โดย: kai (aitai

) วันที่: 22 มกราคม 2552 เวลา:9:19:30 น.

ตลกได้อีกนะเนี้ยะ บลอกนี้

ตกลงไม่ใช่เพราะเพลงคุณน้องชิมิ ที่ทำให้คอมเจ้งน่ะ

คอมเก่าก็ซื้อใหม่เซ่ รวยออกขนาดนี้

ฮี้ๆๆๆๆๆๆๆๆๆๆๆๆๆๆๆๆ

โดย: patra_vet

วันที่: 22 มกราคม 2552 เวลา:12:30:32 น.

เรื่องดีทีเดียวครับครูเอก

แต่ผมไม่มีโอกาสจะนำไปใช้งาน

โดย: yyswim

วันที่: 23 มกราคม 2552 เวลา:13:10:14 น.

โดย: kai (aitai

) วันที่: 23 มกราคม 2552 เวลา:22:20:01 น.

^
^
^
ตกลงว่า ครูเอกจะสกรีนเสื้อคำๆนั้นจริงๆเหรอคะ
กร๊ากก

แวะมาขอบคุณด้วยค่ะ ที่ไปอวยพรวันเกิด ขอให้พรทุกประการส่งกลับคืนสู่คุณครูเอก ด้วยเช่นกันค่ะ
ขอบคุณมากๆค่ะ

โดย: Shallow Grave

วันที่: 24 มกราคม 2552 เวลา:10:50:07 น.

สวัสดีคับ คือผมมีปัญหานิดหน่อยอ่ะคับ คือมีอยู่ว่า ผมได้ทราบจากเพื่อนมาว่า เเชร์การ์ดสามารถเล่นเน็ตได้ซึ่งเป็นระบบ EDGE เพื่อนผมบอกว่าไวกว่า จีพีอาเอสมาก ผมเลยอยากรู้ว่ามันเล่นได้จริงหรอ แล้วเเชร์การ์ดมันมีหน้าตายังไง แล้วราคาเเพงไหม ยังไงก็รบกวนครูเอกส่งข้อมูลที่ได้มาที่เมลล์ผมหน่อยนะคับ
Prasong_tik@hotmail.com

โดย: เด็กอุบล IP: 202.149.25.197 วันที่: 24 มกราคม 2552 เวลา:14:59:00 น.

แวะมาอ่านจ้าครูเอก

โดย: อุ้มสี

วันที่: 24 มกราคม 2552 เวลา:17:09:01 น.

ขอบคุณมากเลยค่ะ เพราะบล๊อกนี้ ทำให้เราเอาไปใช้คำนวณคะแนนการทำงานของพนักงานเพื่อนำเกรดมาตัดโบนัสได้อีกด้วย มีประโยชน์จริงๆ คอนเฟิร์ม!! *-*

โดย: พนง.บริษัท IP: 117.121.208.2 วันที่: 29 มกราคม 2552 เวลา:10:33:34 น.

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

โดย: คนความรู้น้อย แต่ใฝ่รู้ IP: 58.10.80.243 วันที่: 29 มกราคม 2552 เวลา:11:06:52 น.

สวัสดีครับ คุณ พนง.บริษัท
ขอบคุณที่แวะมา "คอนเฟิร์ม!!" นะครับ
ที่แท้..หมอกฤษ มาเอง

สวัสดีครับ คุณ คนความรู้น้อย แต่ใฝ่รู้
ลองใช้ฟังก์ชัน ROUND ครอบชุดผลลัพธ์นะครับ
เช่น จากเดิม หน่วยกิตคูณด้วยผลการเรียนแล้วหารด้วยหน่วยกิต

ลองใส่เป็น ROUND(หน่วยกิตคูณด้วยผลการเรียนแล้วหารด้วยหน่วยกิต, 2)

โดย: ครูเอก

วันที่: 29 มกราคม 2552 เวลา:12:55:09 น.

ขอบพระคุณมาก ๆ เลยนะคะ ดีใจจังเลย ทำได้แล้วววววววววว

โดย: คนความรู้น้อย แต่ไฝ่รู้ IP: 58.10.80.50 วันที่: 30 มกราคม 2552 เวลา:15:14:04 น.

ขอบคุนคร้าแต่อยากด้ายด้วยว่า ชายกี่คน หญิงกี่คน
ผ่านกี่คน ม่ายผ่านกี่คนน ขอบคุนร่วงหน้าคะ

โดย: เด็กkjnม.2 IP: 125.24.68.189 วันที่: 17 สิงหาคม 2554 เวลา:18:32:58 น.