ค่าเฉลี่ย – Mean, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS

ค่าเฉลี่ยนี้สามารถคำนวณใน excel ง่าย ๆ โดยใช้ฟังก์ชัน AVERAGE ซึ่งเป็นสิ่งสามัญที่มีการคำนวณบ่อยมากอย่างหนึ่ง เช่น ค่าเฉลี่ยเวลาทำงานของพนักงาน เงินเดือน ฯลฯ ค่าเฉลี่ย ที่จะเขียนถึงนี้ เรียกกันโดยทั่วไปว่า average หรือ มัชฌิมเลขคณิต (arithmetic mean) หรือ จะเรียกสั้น ๆ ว่า ค่า mean

สูตรการหาค่าเฉลี่ย

มัชฌิมเลขคณิต (arithmetic mean) คำนวณจาก ผลรวมของข้อมูลหารด้วยจำนวนข้อมูล ง่าย ๆ แบบนี้เลย เช่น เรามีข้อมูลทั้งหมด 7 ตัว คือ 4, 5, 6, 7, 7, 8, 9 ค่า mean หรือ average ของข้อมูลชุดนี้คือ

(4 + 5 + 6 +7 + 7 + 8 + 9) /7

คือตัวเลขแต่ละตัวมาบวกกัน แล้วหารด้วยจำนวนข้อมูล

การใช้ AVERAGE

AVERAGE ก็คือค่าเฉลี่ย แปลตรงตัวเลย เป็นการคำนวณ มัชฌิมเลขคณิต โดยการนำแต่ละค่ามารวมกันแล้วหารด้วยจำนวนข้อมูล

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

การใช้งานฟังก์ชัน AVERAGE ไม่ยุ่งงาก แค่ใส่ตัวเลขที่ต้องการคำนวณเข้าไป นั่นคือ

AVERAGE(เลขตัวที่ 1, เลขตัวที่ 2, เลขตัวที่ 3, …)

ง่าย ๆ แค่นี้เอง (ถ้าจำไม่ผิด จะมีข้อจำกัดอยู่ที่ 255 ค่านะ โปรดตรวจสอบข้อจำกัดจากเว็บไซต์ของไมโครซอฟต์อีกครั้ง แต่ปกติทั่วไป จะไม่มีใครใส่ตัวเลขไปตรง ๆ แน่นอน)

อย่างเช่น คำนวณตัวเลข  4, 5, 6, 7, 7, 8, 9 เราใช้สูตรว่า

=AVERAGE(4, 5, 6, 7, 7, 8, 9)

ก็ได้ผลลัพธ์ 6.57 โดยประมาณ

TIP: ในการใช้งานจริง ถ้าต้องการปัดเศษ แนะนำให้ใช้ฟังก์ชัน Round ครอบทับอีกชั้น หากใช้วิธีปรับ format cell โปรดระวังเรื่องการนำค่าที่ได้ไปใช้ อาจมีปัญหาความเข้าใจไม่ตรงกันได้)

เขียนสูตรโดยอ้างอิงตำแหน่ง cell

ในความเป็นจริง เวลาใครใช้งาน Excel คงไม่มีใครมานั่งใส่ตัวเลขลงในสูตรหรอกเนาะ เอาใหม่ สมมติว่าเราใส่ข้อมูล ใน C2 ถึง C8 เรียงลำดับ 4, 5, 6, 7, 7, 8, 9 ตามตัวอย่างเดิม เราจะเขียนสูตรได้ว่า

=AVERAGE(C2:C8)

ใช้ได้ตามวิธีอ้างอิงตำแหน่ง cell ทั่วไปนี่แหละจ้ะ จะเป็นตัวเลข หรือ ชื่อช่วง หรือ การอ้างอิงเซลล์ที่มีตัวเลข ได้หมดเลย ยกตัวอย่างเช่น

คำนวณค่า mean จากข้อมูลทั้งหมดในคอลัมน์ C จะเขียนสูตรแบบนี้

=AVERAGE(C:C)

หาค่า mean จากข้อมูลทั้งหมดในแถวที่ 1 จะเขียนสูตรแบบนี้

=AVERAGE(1:1)

หรือจะเว้นช่วง ก็ได้ แบบนี้

=AVERAGE(A2:A5, A12:A20)

ค่า 0 กับ Cell ว่าง ไม่เหมือนกัน

สิ่งที่ต้องระวังอย่างหนึ่งคือ สำหรับ Cell ที่ไม่มีค่าอะไรอยู่ในนั้น จะโดน “ละเว้น” จากฟังก์ชัน AVERAGE ในขณะที่ ถ้าใส่ค่า 0 จะถือว่ามีข้อมูลที่มีค่าเป็น 0

ยกตัวอย่างเลขชุดเดิม แต่แทรก cell ว่างไป 1 cell จะเห็นว่ายังได้ผลลัพธ์เป็น 6.57 เหมือนเดิม แต่ทันทีที่ใส่ค่า 0 ลงไปใน cell นั้น ผลลัพธ์จะกลายเป็น 5.75 เพราะจะกลายเป็นหารด้วย 8 (ข้อมูล) แทนที่จะหารด้วย 7 (ข้อมูล) แบบตัวอย่างแรก  

การละเว้นไม่นำมาคำนวณ

สมมติว่า ใน C2 ถึง C13 มีค่าใส่เรียงตามลำดับดังนี้

4, 5, 6, 7, “”, TRUE, FALSE, , 7, 8, 9, #N/A (มี Cell ว่างคือ C9)

ถ้าเราใส่สูตร

AVERAGE(C2:C13)

ผลลัพธ์จะเป็น #N/A เพราะ ถ้า cell ใด cell หนึ่ง แสดงค่าเป็น #N/A (เป็น error จากการอ้างอิงไม่ถูกต้อง) ผลลัพธ์ที่ได้จะเป็น #N/A

ค่าเฉลี่ย

ถ้าเราใส่สูตร

AVERAGE(C2:C12)

(อันนี้คือตัด cell ที่มีค่า #N/A ออก) ผลลัพธ์ก็ยังได้ 6.57 เหมือนตัวอย่างแรก เพราะ ฟังก์ชัน AVERAGE จะไม่นับค่าตรรกะ (Boolean – True False) และ ข้อความ (text) หากเขียนสูตร (หรือ argument) อ้างอิงช่วงหรือเซลล์ที่มี ข้อความ ค่าตรรกะ หรือ เซลล์ว่าง ค่าเหล่านั้นจะถูกละเว้น

 ถ้าต้องการรวมค่าตรรกะและการแสดงข้อความของตัวเลขในการอ้างอิงเป็นส่วนหนึ่งของการคำนวณให้ใช้ฟังก์ชัน AVERAGEA

AVERAGEA

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

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

การใช้งานฟังก์ชัน AVERAGE ไม่ยุ่งงาก แค่ใส่ตัวเลขที่ต้องการคำนวณเข้าไป นั่นคือ

AVERAGEA(ค่าที่ 1, ค่าที่ 2, ค่าที่ 3, …)

ง่าย ๆ แค่นี้เอง อย่างเช่นตัวอย่างเดิม สมมติว่าเราใส่ข้อมูล ใน C2 ถึง C13 เรียงลำดับตามนี้

4, 5, 6, 7, “”, TRUE, FALSE, , 7, 8, 9, #N/A (มี Cell ว่างคือ C9

เหมือนเดิม ถ้ามีค่า #N/A จะได้ค่า  #N/A

ถ้าเขียนสูตร

AVERAGEA(C2:C12)

ผลลัพธ์ที่ได้จะเป็น 4.7 ไม่เท่ากับ AVERAGE(C2:C12) เพราะว่า ค่า “” คำนวณด้วย มีค่าเป็น 0 True มีค่าเป็น 1 False มีค่าเป็น 0 ส่วน cell ว่างจะไม่นำมาคำนวณแบบเดียวกับ AVERAGE

ข้อควรจำ

Cell ว่างจะโดนละเว้น

ถ้ามีข้อความ หรือแม้แต่ข้อความที่เป็นค่าว่าง (“”) ที่ได้จากสูตรคำนวณหรือฟังก์ชันอื่นแสดงมาก็ตาม จะคำนวณโดยให้มีค่าเท่ากับ 0

ค่าทางตรรกะ (Boolean) ถ้าเป็นค่า TRUE จะคำนวณโดยมีค่าเท่ากับ 1 ถ้าเป็น FALSE จะคำนวณโดยมีค่าเท่ากับ 0

ความแตกต่างระหว่าง AVERAGE กับ AVERAGEA อยู่ที่ AVERAGE จะไม่คำนวณค่าตรรกะ และ ข้อความ แต่ถ้าเป็น AVERAGEA จะนำค่าตรรกะและข้อความมาคำนวณด้วยด้วย

AVERAGEIF

AVERAGEIF จะเป็นการคำนวณค่า mean โดยมีเงื่อนไขว่าจะเลือกคำนวณจากอะไร

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

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

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

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

เงื่อนไข คือสิ่งที่ต้องการหาว่า ตามเงื่อนไขนี้ จะให้คำนวณค่า mean ออกมา

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

ตัวอย่างการนำไปใช้

ตัวอย่างเช่น เรามีข้อมูลการขายสินค้า  จะมีประเภทสินค้า อยู่ในคอลัมน์ B จำนวนหน่วยที่ขายได้ อยู่ในคอลัมน์ C  ราคาต่อหน่วยอยู่ในคอลัมน์ D และ ยอดจำหน่ายที่ได้ในคอลัมน์ E

หาแบบพื้น ๆ เลือกเงื่อนไขจากช่วงข้อมูล

ถ้าเราอยากรู้ว่า ค่า mean ของจำนวนหน่วยที่ขายได้ตั้งแต่ 100 หน่วยขึ้นไป มีค่าเฉลี่ยอยู่ที่เท่าไหร่ (เอาจริงก็ไม่ make sense เท่าไหร่ เอาเหอะ แค่เป็นตัวอย่างเฉย ๆ อ่ะนะ)

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

 แต่ตอนนี้ เราเพียงแค่เขียนสูตร ดังนี้

=AVERAGEIF(C2:C56,">=100")

ช่วงข้อมูล คือช่วงที่ต้องการหาเงื่อนไข ในที่นี้คือ C2 จนถึง C56 หรือ Quantity (ปริมาณ) ในคอลัมน์ C

เงื่อนไข คือ ตั้งแต่ 100 หน่วยขึ้นไป นั่นคือ >=100

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

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

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

หรือ เราต้องการหาว่า Regular beer ขายได้ปริมาณเฉลี่ยต่อคำสั่งซื้อได้เท่าไหร่ ก็เขียนเป็นสูตร

=AVERAGEIF(B2:B56,"Regular beer",C2:C56)

ช่วงข้อมูล คือช่วงที่ต้องการหาเงื่อนไข ในที่นี้คือ B2:B56 หรือ Product ในคอลัมน์ B

เงื่อนไข คือ ประเภท Regular beer นั่นคือใส่ค่า Regular beer

[ช่วงที่ต้องการคำนวณ] คือ C2:C56 หรือ ค่หรือ Quantity (ปริมาณ) ในคอลัมน์ C

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

ค่าเฉลี่ย Averageif

ใช้ wildcard ในเงื่อนไข เพื่อค้นหาค่าเฉลี่ย

หรืออีกตัวอย่างหนึ่ง เราต้องการหาข้อมูลที่เป็น beer ทุกประเภท ทั้ง Regular และ Light และ อื่น ๆ ที่ลงท้ายว่า beer โดยหาว่า ค่าเฉลี่ยของราคาต้นทุนต่อหน่วย อยู่ที่เท่าไหร่

ช่วงข้อมูล คือช่วงที่ต้องการหาเงื่อนไข ในที่นี้คือ B2:B56 หรือ Product ในคอลัมน์ B

เงื่อนไข คือ ทุกประเภท beer นั่นคือใส่ค่า

[ช่วงที่ต้องการคำนวณ] คือ D2:D56 หรือ Unit Cost (ราคาต่อหน่วย) ในคอลัมน์ D

ปัญหาคือ เงื่อนไขว่า เราจะเอาทุกประเภทที่ลงท้ายว่า beer ซึ่ง เราจะใช้ตัวช่วย นั่นคือ ตัว wildcard

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

ถ้าเป็น ดอกจัน (*) จะแทนว่า เป็นตัวอักษรกี่ตัวก็ได้ เช่น *ive ก็หมายถึง คำอะไรก็ได้ ที่ลงท้ายว่า ive หรือ dis* หมายถึงคำอะไรก็ได้ ที่ขึ้นต้นด้วย dis โดยไม่จำกัดจำนวนอักขระ

ถ้าเป็น ปรัศนี (?) จะใช้แทนตัวอักษร 1 ตัว นั่นคือ จะต้องระบุให้ชัดเจนว่า มีจำนวนกี่อักขระ อย่างเช่น ??ve หมายถึง คำนั้น จะต้องประกอบด้วยอักขระ 4 ตัวอักษร ซึ่งสองตัวท้ายจะเป็น ve เช่น love dove have save dave wive อะไรแบบนี้เท่านั้น จะเป็น grave, glove ไม่ได้ เพราะมีอักขระ 5 ตัวอักษร ข้อความที่ตรงตามเงื่อนไขนี้จะมีอักขระเพียง 4 ตัวเท่านั้น

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

จากโจทย์ เราต้องการทุกอย่างที่ลงท้ายว่า beet ก็ต้องใช้ *beer แบบนี้

=AVERAGEIF(B2:B56,"*beer",D2:D56)
AVERAGEIF ค่าเฉลี่ย

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

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

=AVERAGEIF(B2:B56,"<>*beer",C2:C56)
Tips ถ้าเงื่อนไขที่เราต้องการคือ cell ที่ว่างเปล่า ไม่มีอะไรเลย ไม่มีสูตร ไม่มีข้อความว่าง ให้ใช้ “=“

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

AVERAGEIFS

จะคล้ายกับ AVERAGEIF เพียงแค่เลือกเงื่อนไขได้มากกว่า 1 เงื่อนไข

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

AVERAGEIFS(ช่วงข้อมูลที่จะคำนวณค่าเฉลี่ย, ช่วงสำหรับเงื่อนไข1, เงื่อนไข1, [ช่วงสำหรับเงื่อนไข2, เงื่อนไข2], …)

ตัวอย่างการเขียนสูตร

จากตัวอย่างเดิม ลองมาดูว่า ถ้าจะเขียนสูตรจากโจทย์เดิม คือ ต้องการหาค่าเฉลี่ยของ beer ทุกประเภท ทั้ง Regular และ Light และ อื่น ๆ ที่ลงท้ายว่า beer โดยหาว่า ค่าเฉลี่ยของราคาต้นทุนต่อหน่วย อยู่ที่เท่าไหร่ ซึ่งตอนที่เราเขียนสูตรโดยใช้ฟังก์ชัน AVERAGEIF เราจะได้สูตรแบบนี้

=AVERAGEIF(B2:B56,"*beer",D2:D56)

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

=AVERAGEIFS(D2:D56,B2:B56,"*beer")

ความแตกต่างคือ จะเอา ช่วงข้อมูลที่จะคำนวณค่า mean (ในที่นี้คือ D2:D56) มาไว้หน้าสุด แล้วค่อยต่อด้วย ช่วงที่จะหาเงื่อนไข (ในที่นี้คือ B2:B56) กับ เงื่อนไข (ในที่นี้คือ “*beer”) ซึ่งเราสามารถเพิ่มเงื่อนไขเข้าไปอีกได้ ยกตัวอย่างเช่น ต้องการหาค่าเฉลี่ยของ beer ทุกประเภท ทั้ง Regular และ Light และ อื่น ๆ ที่ลงท้ายว่า beer ที่มีปริมาณต่อคำสั่งซื้อ ตั้งแต่ 100 หน่วยขึ้นไป ให้หาว่า ค่าเฉลี่ยของราคาต้นทุนต่อหน่วย อยู่ที่เท่าไหร่ เราจะเขียนสูตรได้ดังนี้

=AVERAGEIFS(D2:D56,B2:B56,"*beer",C2:C56,">=100")

นั่นคือ เขียนสูตรเหมือนเดิม เพิ่มเติมเงื่อนไขว่า ปริมาณต่อคำสั่งซื้อ ตั้งแต่ 100 หน่วยขึ้นไป โดยเพิ่มช่วงข้อมูลปริมาณการสั่งซื้อ (ในที่นี้คือ C2:C56) และเงื่อนไข “>=100”

ค่าเฉลี่ย  AVERAGEIFS

ข้อพึงระวัง

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

แต่ถ้าเงื่อนไขนั้น ประกอบด้วย เครื่องหมาย operator พร้อมตำแหน่งอ้างอิง เช่น

=AVERAGEIFS(D2:D56,B2:B56,H2,C2:C56,H3)

โดย H2 และ H3 คือเงื่อนไขที่เราจะใส่ค่าใน Cell นั้นตรง ๆ เช่น >=100

แต่ถ้าเราใส่ operator ในสูตร โดย Cell ที่อ้างอิงนั้นจะใส่แค่คำ หรือ ตัวเลข เราต้องใส่ “” ครอบไว้ด้วย พร้อม & เพื่อเชื่อมกับ cell ที่จะอ้างอิง

เช่น

=AVERAGEIFS(D2:D56,B2:B56,H2,C2:C56,">="&H4)

สรุป

การใช้ AVERAGE AVERAGEA AVERAGEIF AVERAGEIFS คือการหาค่าเฉลี่ยแบบ มัชฌิมเลขคณิต (arithmetic mean)

AVERAGE AVERAGEA คือการคำนวณแบบไม่มีเงื่อนไข โดย AVERAGE จะละเว้นข้อความ ค่าตรรกะ ไม่นำมาคำนวณ แต่ AVERAGEA จะนำมาคำนวณ แต่ทั้งสองแบบ จะละเว้น Cell ที่มีค่าว่าง ไม่นำมาคำนวณทั้งคู่

AVERAGEIF AVERAGEIFS คือการคำนวณแบบมีเงื่อนไข โดย AVERAGEIF จะมีเพียงเงื่อนไขเดียว ส่วน AVERAGEIFS มีได้หลายเงื่อนไข

การขึ้น #DIV0! Error หมายความว่าไม่สามารถคำนวณได้ เช่น ในกรณี AVERAGE ไม่มีข้อมูลที่เป็นตัวเลขให้หาค่า mean หรือ ถ้าเป็นการคำนวณแบบมีเงื่อนไข (AVERAGEIF AVERAGEIFS) แล้วพบว่า ไม่มี cell ใดมีข้อมูลตรงตรงตามเงื่อนไข

ความคิดเห็นของคุณ :)

%d bloggers like this: