ในการหาผลรวมใดๆ เราคุ้นเคยกับการใช้ฟังก์ชัน SUM ซึ่งจะเป็นการหาผลรวมของข้อมูลทั้งหมด ในช่วงที่ระบุ แต่ในบางลักษณะงาน เราต้องการที่จะหาผลรวมแบบมีเงื่อนไข เช่น ต้องการรวมจำนวนคน "เฉพาะ" หน่วยงานนี้ หรือ ต้องการรวมยอดขาย "เฉพาะ" เดือนนี้ฯลฯ เป็นต้น
เพื่อให้เห็นภาพชัดเจนยิ่งขึ้น ผมลองสร้างตารางข้อมูลบุคลากรขึ้นมา ดังรูปที่ 1 แสดงถึงจำนวนคนในแต่ละตำแหน่ง ในแต่ละหน่วยงาน ซึ่งถ้าถามว่า สรุปแล้วองค์กรนี้มีบุคลากรกี่คน เราก็จะเขียนสูตร =SUM(E16:E29) ลงในเซลล์ E30
รูปที่ 1 ตัวอย่างตารางข้อมูล
การใช้ SUMIF หาผลรวมแบบมีเงื่อนไขเดียว
ถ้าเราต้องการรวมจำนวนบุคลากร โดยมีเงื่อนไขว่า "แยกเป็นรายแผนก" หากเราใช้ SUM ก็คงต้องมาไล่ดูเอง ว่ารายการใดบ้างที่ตรงกับแผนกที่ต้องการ และยิ่งถ้าข้อมูลมีเป็นจำนวนมาก ก็คงเสียเวลาและมีโอกาสผิดพลาดสูง ในกรณีนี้ Excel เตรียมฟังก์ชัน SUMIF ไว้ให้เราเลือกใช้
โครงสร้างฟังก์ชัน
SUMIF(range, criteria, sum_range)
- range หรือ "ช่วงของเงื่อนไข" หมายถึง ช่วงของข้อมูลที่จะเปรียบเทียบเงื่อนไข
- criteria หรือ "เงื่อนไข" หมายถึง ข้อแม้ที่จะใช้ในการคำนวณ สามารถเป็นได้ทั้งตัวเลข, ข้อความ หรือเซลล์อ้างอิง เช่น 32, "32", ">32", A1 or "August" เป็นต้น
- sum_range หรือ "ช่วงของผลรวม" หมายถึง ช่วงของตัวเลขที่จะนำมารวม เมื่อเป็นรายการที่ตรงกับเงื่อนไขที่ระบุ
ในการเลือกใช้ SUMIF สิ่งแรกที่จะต้องพิจารณาก็คือ "เงื่อนไข" ที่ต้องการคืออะไร? จากตารางผลลัพธ์ในรูปที่ 2 เราต้องการทราบผลรวมของจำนวนบุคลากรในแต่ละหน่วยงาน ดังนั้น เงื่อนไข (criteria) ในที่นี้ก็คือ "ชื่อหน่วยงาน" นั้นเอง
เมื่อเงื่อนไขคือชื่อหน่วยงาน คำว่า "ช่วงของเงื่อนไข" (range) จึงหมายถึง ช่วงเซลล์ที่มีชื่อหน่วยงาน ซึ่งจากรูปที่ 1 ก็คือ ช่วง C16:C29 นั่นเอง ดังนั้น จากรูปที่ 2 ที่เซลล์ E33 พิมพ์สูตร
=SUMIF(C$16:C$29, D33, E$16:E$33)
Enter แล้วคัดลอกสูตรลงมาถึง E37 ก็จะได้ผลลัพธ์ดังรูปที่ 2
รูปที่ 2 ตารางสรุปจำนวนพนักงานแยกตามหน่วยงาน
หรือถ้าเราจะเปลี่ยนเงื่อนไขจาก "ชื่อหน่วยงาน" มาเป็น "ตำแหน่งงาน" ช่วงของเงื่อนไขก็จะหมายถึง ช่วง D16:D29 โดยผมทำการเปลี่ยนตารางผลลัพธ์ใหม่ เป็นดังรูปที่ 3 จากนั้น ที่เซลล์ E41 พิมพ์สูตร
=SUMIF(D$16:D$29, D41, E$16:E$33)
Enter แล้วคัดลอกสูตรลงมาถึง E43 ก็จะได้ผลลัพธ์ดังรูปที่ 3
รูปที่ 3 ตารางสรุปจำนวนพนักงานแยกตามตำแหน่งงาน
จากทั้งสองตัวอย่างที่กล่าวมาข้างต้น จะเห็นว่า SUMIF ใช้ในการหาผลรวมแบบมีเงื่อนไข เฉพาะกรณีที่ระบุเงื่อนไขเพียงเงื่อนไขเดียว (Single criteria) เช่น ชื่อหน่วยงาน หรือ ชื่อตำแหน่งงาน เพียงอย่างใดอย่างหนึ่งเท่านั้น
การใช้ SUMIF ในลักษณะปกติ ไม่สามารถใช้หาผลรวม กรณีที่มีเงื่อนไขมากกว่าหนึ่งเงื่อนไขได้ แต่ถ้ามีการพลิกแพลง หรือประยุกต์สักเล็กน้อย ก็สามารถทำได้
การประยุกต์ใช้ SUMIF หาผลรวม แบบมีมากกว่าหนึ่งเงื่อนไข
การหาผลรวมแบบมีเงื่อนไขมากกว่าหนึ่งเงื่อนไข (Multiple criteria) นั้น หากจะใช้ SUMIF ก็ต้องมีการประยุกต์กันนิดหน่อย และยังต้องจัดเงื่อนไขให้เป็นเงื่อนไขเดียวเหมือนเดิม เพียงแต่เงื่อนไขดังกล่าวนั้น เป็นเงื่อนไขที่เราสร้างขึ้นมาใหม่ โดยรวมเงื่อนไขที่ 1 กับเงื่อนไขที่ 2 (หรือมากกว่านั้น) เข้าด้วยกัน
จากรูปที่ 1 ถ้าเราต้องการรวมจำนวนบุคลากร โดยมีเงื่อนไขว่าเฉพาะตำแหน่ง "ผู้จัดการ" ของ "ฝ่ายธุรการ" ซึ่งกรณีนี้จะถือว่าเป็น 2 เงื่อนไข
เราสร้าง "ช่วงของเงื่อนไข" G16:G29 ขึ้นมาใหม่ โดยเป็นการรวม 2 เงื่อนไขข้างต้น ให้เป็นเงื่อนไขเดียว เพื่อใช้กับ SUMIF ดังนั้น จากรูปที่ 1 ที่เซลล์ G16 พิมพ์สูตร
=C16&D16
Enter แล้ว copy สูตรลงไปถึง G29 จะได้ผลลัพธ์ดังรูปที่ 4
รูปที่ 4 การสร้างเงื่อนไขหลอก เพื่อให้ SUMIF ทำงานได้
ลองสร้างตารางผลลัพธ์ขึ้นมาใหม่ ดังรูปที่ 5 และที่ E48 พิมพ์สูตร
=SUMIF($G$16:$G$29, $D48&E$47, $E$16:$E$33)
Enter แล้ว copy สูตรลงไปถึง G52 จะได้ผลลัพธ์ดังรูปที่ 5
รูปที่ 5 ตารางสรุปจำนวนพนักงานแยกตามหน่วยงานและตามตำแหน่งงาน
เนื่องจากช่วงของเงื่อนไข ในช่วง G16:G29 เป็นการเชื่อมเงื่อนไขระหว่าง "หน่วยงาน" และ "ตำแหน่ง" โดยเอาหน่วยงานมาก่อน แล้วตามด้วยตำแหน่ง ดังนั้น ในการระบุเงื่อนไขในสูตร ในตารางผลลัพธ์ (E48) จึงต้องอ้างถึงหน่วยงานก่อนแล้วตามด้วยตำแหน่งเช่นกัน ($D48&E$47)
ส่วนการใส่เครื่องหมาย $ เพื่อล๊อกตำแหน่งแถวและคอลัมน์นั้น ก็เป็นไปตามกฏเกณฑ์การอ้างอิงพื้นฐานของ Excel ดังนั้น ผมจะไม่ขออธิบาย ณ ตรงนี้ ซึ่งหวังว่าผู้อ่านคงจะเข้าใจอยู่แล้ว
สำหรับการหาผลรวมแบบมีเงื่อนไข มากกว่า 2 เงื่อนไขขึ้นไป ถ้าจะใช้ SUMIF ก็ต้องทำการรวมเงื่อนไขทั้งหมด ให้เป็นเงื่อนไขเดียวก่อน ดังที่กล่าวมาข้างต้น แต่อาจจะไม่สะดวก เมื่อมีเงื่อนไขเพิ่มมากขึ้น ควรเปลี่ยนไปใช้วิธีการอื่น ซึ่งจะได้กล่าวถึงในโอกาสต่อไป
0 ความคิดเห็น:
แสดงความคิดเห็น