AVERAGEIF / AVERAGEIFS หาค่าเฉลี่ยแบบมีเงื่อนไข

AVERAGEIF เป็นฟังก์ชันสำหรับคำนวณค่าเฉลี่ยเลขคณิต หรือค่า mean แบบเดียวกับฟังก์ชัน AVERAGE แต่ว่าจะมีเงื่อนไขว่าจะเลือกคำนวณจากอะไร

สำหรับการคำนวณค่าเฉลี่ยเลขคณิต โดยกำหนดเงื่อนไขนี้ จะมีอยู่ 2 ฟังก์ชันที่คล้ายคลึงกัน คือ AVERAGEIF กับ AVERAGEIFS (ต่างกันแค่มี s กับไม่มี s) ซึ่งความแตกต่างก็คือ AVERAGEIF ตั้งเงื่อนไขเพียงแค่ 1 เงื่อนไข แต่ถ้าเป็น AVERAGEIFS จะตั้งเงื่อนไขได้มากกว่า 1 เงื่อนไข

การใช้ฟังก์ชัน AVERAGEIF 

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

AVERAGEIF(ช่วงข้อมูล, เงื่อนไข, [ช่วงที่ต้องการคำนวณ])

ช่วงข้อมูล คือช่วงข้อมูลที่ต้องการหาเงื่อนไข ถ้าไม่ได้กำหนด [ช่วงที่ต้องการคำนวณ]ไว้ จะคำนวณผลจากช่วงข้อมูลนี้

เงื่อนไข คือสิ่งที่ต้องการใช้ในการกำหนดว่า ถ้าเป็นไปตามเงื่อนไขนี้ ให้คำนวณค่ามัชฌิมเลขคณิตออกมา

[ช่วงที่ต้องการคำนวณ] เป็นส่วนเสริม คือจะใส่หรือไม่ใส่ก็ได้ ถ้าไม่กำหนดตรงนี้ไว้ จะคำนวณที่ช่วงข้อมูล แต่ถ้าใส่ไว้ จะนำข้อมูลจาก [ช่วงที่ต้องการคำนวณ] นี้มาคำนวณ

ตัวอย่าง AVERAGEIF

ตัวอย่างเช่น เรามีข้อมูลการขายสินค้า จะมี Order Date (วันที่สั่ง) Region (ภาค) REP (ชื่อพนักงาน) Item (ประเภทสินค้า) Units (จำนวนหน่วย) Unit Cost (ราคาต่อหน่วย) Total (รวมยอดเงิน)

– ข้อมูลตัวอย่างนี้จาก https://www.contextures.com/xlsampledata01.html#data

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

สมมติ เราจะสร้างเงื่อนไขง่าย ๆ ก่อนว่า จะหาค่าเฉลี่ย จาก Total ที่ไม่เกิน 200 เหรียญ

ถ้า ไม่ใช้ฟังก์ชัน AVERAGEIF เราอาจจะต้องเรียงลำดับคอลัมน์ Totla หรือ filter ข้อมูลที่มี Total ไม่เกิน 200 หน่วยขึ้นไป แล้วค่อยเอามาหาค่าเฉลี่ย

แต่ในเมื่อมีฟังก์ชัน AVERAGEIF เราก็เพียงแค่เขียนสูตรเข้าไป

=AVERAGEIF(G2:G44, "<=200")

ช่วงข้อมูล คือ ข้อมูลใน Total จาก G2 ถึง G44

เงื่อนไข คือ น้อยกว่าหรือเท่ากับ 200  นั่นคือ “<=200”

[ช่วงที่ต้องการคำนวณ] ไม่ใส่ เพราะจะให้คำนวณจาก G2 ถึง G44

ก็จะได้ผลลัพธ์ที่ต้องการ คือ 105.9052941

ตัวอย่างที่ 2 หาค่าเฉลี่ยของช่วงข้อมูลหนึ่ง โดยใช้เงื่อนไขจากอีกช่วงข้อมูลหนึ่ง

เราต้องการหาว่า Pencil จำนวนที่ขายได้เฉลี่ยกี่ units ก็เขียนเป็นสูตรดังนี้

=AVERAGEIF(D2:D44,"Pencil",E2:E44)

ช่วงข้อมูล คือช่วงที่ต้องการหาเงื่อนไข ในที่นี้คือ D2:D44 หรือ Item ในคอลัมน์ D

เงื่อนไข คือ Pencil

[ช่วงที่ต้องการคำนวณ] คือ E2:E44 หรือ Units ในคอลัมน์ E

เพียงเท่านี้ก็เรียบร้อย ได้ผลลัพธ์ 55.07692308

ตัวอย่างที่ 3 ใช้ wildcard ในเงื่อนไข

สำหรับ wildcard หรือ “อักขระตัวแทนพิเศษ” นี้ ที่ใช้กันอยู่จะมี 2 ตัว คือ ดอกจัน (*) กับ ปรัศนี (?)

ดอกจัน (*) จะแทนที่อักขระกี่ตัวก็ได้ เช่น *cel ก็หมายถึง คำอะไรก็ได้ ที่ลงท้ายว่า celเช่น excel, Parcel, Cancel 

หรือ def* หมายถึงคำอะไรก็ได้ ที่ขึ้นต้นด้วย def โดยไม่จำกัดจำนวนอักขระ

ปรัศนี (?) จะใช้แทนอักขระ 1 ตัว นั่นคือ จะต้องระบุให้ชัดเจนว่า มีจำนวนกี่อักขระ อย่างเช่น ??? cel หมายถึง คำนั้น จะต้องประกอบด้วยอักขระ 6 ตัว ซึ่ง สามตัวท้ายจะเป็น celเช่น parcel, cancel, marcel เป็นต้น ถ้าเป็น excel จะไม่เข้าข่าย เพราะมีอักขระ 5 ตัว ไม่ตรงกับเงื่อนไข

Tip: เนื่องจาก * และ ? เป็น wildcard หรืออักขระที่มีฟังก์ชันพิเศษ ถ้าเราจะค้นหาคำที่มี * หรือ ? จริง ๆ จะต้องใส่ตัว escape เพื่อละเว้นการใช้ฟังก์ชันพิเศษของอักขระนี้เสียก่อน นั่นคือใส่เครื่องหมาย – เข้าไปข้างหน้า

ตัวอย่างการใช้ wildcard

เราต้องการหาค่าเฉลี่ยของอุปกรณ์ที่ใช้เขียน คือ pen, pen set และ pencil ค่าเฉลี่ยของจำนวน Units ต่อครั้ง อยู่ที่เท่าไหร่

ช่วงข้อมูล คือช่วงที่ต้องการหาเงื่อนไข ในที่นี้คือ D2:D44 หรือ item ในคอลัมน์ D

เงื่อนไข คือ ทุกอย่างที่ขึ้นต้นด้วย pen นั่นคือใส่ค่า “pen*”

[ช่วงที่ต้องการคำนวณ] คือ E2:E44 หรือ Units ในคอลัมน์ E

=AVERAGEIF(D2:D44,"pen*",E2:E44)

ก็จะได้ผลลัพธ์ 55.56

Tip: เราสามารถใช้ operator ร่วมกับเงื่อนไขได้ เช่น ถ้าเราต้องการหาปริมาณ units ต่อยอดคำสั่งซื้อ ของทุกอย่าง ยกเว้น ที่ขึ้นต้นด้วย pen เราเขียนสูตรแบบนี้ 

=AVERAGEIF(D2:D44,"<>pen*",E2:E44)

Tips ถ้าเงื่อนไขที่เราต้องการคือ cell ที่ว่างเปล่า ไม่มีอะไรเลย ไม่มีสูตร ไม่มีข้อความว่าง ให้ใช้ “=“ 

ถ้าเงื่อนไข คือ cell ที่แสดงผลว่างเปล่า แต่จริง ๆ มีสูตรอยู่ (แต่แสดงค่าเป็นว่าง) ให้ใช้ “ “

AVERAGEIFS

คุณสมบัติแทบทุกอย่าง จะคล้ายกับ AVERAGEIF เพียงแค่กำหนดเงื่อนไขได้มากกว่า 1 เงื่อนไข ความแตกต่างคือ จะต้องเอาช่วงข้อมูลที่จะคำนวณค่า mean มาไว้หน้าสุด แล้วค่อยต่อด้วย ช่วงที่จะหาเงื่อนไข กับ เงื่อนไข ที่ต้องการ

รูปแบบการเขียนสูตร AVERAGEIFS

AVERAGEIFS(ช่วงที่ต้องการคำนวณ, ช่วงสำหรับเงื่อนไข1, เงื่อนไข1, [ช่วงสำหรับเงื่อนไข2, เงื่อนไข2], …)

ตัวอย่าง

จากตัวอย่าง 1 ของ AVERAGEIF ลองมาดูว่า ถ้าจะเขียนสูตรจากโจทย์เดิม คือ จะหาค่าเฉลี่ย จาก Total ที่ไม่เกิน 200 เหรียญ 

ถ้าเป็น AVERAGEIF จะเป็น

=AVERAGEIF(G2:G44, "<=200")

แต่ถ้าเป็น AVERAGEIFS จะได้หน้าตาแบบนี้

=AVERAGEIFS(G2:G44, G2:G44"<=200")

ทั้ง 2 สูตร จะได้ผลลัพธ์เท่ากัน คือ 105.9052941

ตัวอย่างการใช้เงื่อนไขมากกว่า 1 เงื่อนไข

ต้องการหาค่าเฉลี่ย Total ของ Pencil ที่มีปริมาณต่อคำสั่งซื้อ ตั้งแต่ 50 หน่วยขึ้นไป เราจะเขียนสูตรได้ดังนี้

=AVERAGEIFS(G2:G44, D2:D44,"pencil",E2:E44,">=50")

ช่วงที่ใช้คำนวณคือ Total (G2:G44)

เงื่อนไขแรก item (D2:D44) คือ pencil

เงื่อนไขสอง Units (E2:E44) มากกว่าเท่ากับ 50