Round ใน Power Query ได้ผลลัพท์ไม่เหมือนใน Excel!!

บทความนี้เป็นบทความจาก Content Creator
เขียนโดย กิตติ ดิศกัมพล (เจ้าของเพจ Excel Begin)
บรรณาธิการ ตรวจสอบโดย ศิระ เอกบุตร (เทพเอ็กเซล)
กลั่นมาจากความคิดทั้งสองคน จึงมั่นใจได้ในความถูกต้องมากขึ้นไปอีกครับ

1. ระวัง Round ใน Power Query ได้ผลลัพท์ไม่เหมือนใน Excel!!

การ Round ใน Power Query โดยปกติก็จะใช้ user interface จาก Ribbon

โดยไปที่ Transform หรือ Add Column จากนั้นก็เลือกวิธีปัดเศษ (ซึ่งมี 3 แบบให้เลือก คือ Round Up, Round Down และ Round)

รูปแสดงวิธีการ round ใน Power Query

แต่ผลลัพท์จากการปัดเศษจากทั้ง 3 วิธีมันไม่เหมือนใน Excel เลยครับ 😮

• ⚠️ Round ใน Power Query ไม่ใช่ Excel ROUND
• ⚠️ RoundUp ใน Power Query ไม่ใช่ Excel ROUNDUP
• ⚠️ RoundDown ใน Power Query ไม่ใช่ Excel ROUNDDOWN

ลองดูการเปรียบเทียบผลลัพท์จากตารางด้านล่างนี้

ตารางเปรียบเทียบผลลัพท์จากการปัดเศษของ Excel และ Power Query

จากตารางจะเห็นปัญหาที่เลขลงท้ายด้วย 5 หรือ ค่าติดลบ

• ROUND มีปัญหากับเลขที่ลงท้ายด้วย 5 ซึ่งได้ผลลัพท์ไม่ตรงกับ Round ใน Excel
  • ROUND ใน Excel = ROUND(2.5, 0) = 3 แต่ Power Query ได้ 2😒
  • ROUND ใน Excel = ROUND(-2.5, 0) = -3 แต่ Power Query ได้ -2😒
• ROUNDUP & ROUNDDOWN มีปัญหากับเลขติดลบ
  • ถ้าค่าบวก จะได้ผลลัพท์เหมือนกันทั้ง Excel และ Power Query
  • แต่ ถ้าค่าลบ ใน Excel กับ Power Query จะได้ผลลัพท์ต่างกัน 😒

แล้วทำไมมันจึงได้คำตอบไม่เหมือนกัน?

การ Round ใน Power Query มี 5 แบบ (ฟังไม่ผิดครับ มีถึง 5 แบบ) คือ

1. Number.Round( [Number] )
   • Number.Round([Number], 0,RoundingMode.AwayFromZero)
   • Number.Round([Number], 0,RoundingMode.Down)
   • Number.Round([Number], 0, RoundingMode.ToEven)
   • Number.Round([Number], 0,RoundingMode.TowardZero)
   • Number.Round([Number], 0, RoundingMode.Up)
2. Number.RoundUp( [Number] )        
3. Number.RoundDown( [Number] )
4. Number.RoundTowardZero( [Number] )
5. Number.RoundAwayFromZero( [Number] )

ค่อยๆ ดูกันที่ละแบบนะครับ.. เริ่มจาก Number.Round

2. วิธีปัดเศษใน Power Query

2.1 การปัดเศษโดยใช้ Number.Round

Number.Round เป็นการปัดเศษแบบ Excel กล่าวคือ ถ้ามากกว่า 5 จะปัดขึ้น แต่ถ้าน้อยกว่า 5 จะปัดลง

แต่ๆๆ .. ถ้าเป็นเลข 5 มันมีวิธีปัดเศษหลายแบบย่อยอีกครับ ลองดูตัวอย่างการปัดเศษที่ลงท้ายด้วยเลข 5 ในตารางด้านล่างนี้ครับ

ตารางเปรียบเทียบ RoundingMode แบบต่างๆ ของ Number.Round

หมายเหตุ : ความหมายของ Column ในตาราง

• Column “Value” คือ ค่าเดิมก่อนการปัดเศษ
• Column “Excel” คือ การปัดเศษโดยใช้ฟังก์ชั่น Round ใน Excel
• Column อื่นๆ คือ RoundingMode แบบต่างๆ ของ Number.Round

**Number.Round ทุก RoundingMode จะปัดเศษแบบ Excel คือ ถ้าเลขมากกว่า 5 จะปัดขึ้น แต่ถ้าเลขน้อยกว่า 5 จะปัดลง (จะมีวิธีปัดเศษไม่เหมือนกันแค่เลข 5) ดังนั้นเคสต่อไปนี้ อธิบายเฉพาะการปัดเศษที่ลงท้ายด้วย 5 ของ การใช้ RoundingMode แบบต่างๆ ใน Number.Round**

• RoundingMode.AwayFromZero

ปัดเศษเหมือนใน Excel Round (ปัดไกลศูนย์) เช่น ถ้า 1.5 ปัดเป็น 2 , ถ้า -1.5 ปัดเป็น -2

• RoundingMode.TowardZero

ปัดเศษเหมือน Excel Round ยกเว้นตัวเลขที่ลงท้ายด้วย 5 จะปัดใกล้ศูนย์ เช่น ถ้า 1.5 เป็นเป็น 1 , ถ้า -1.5 ปัดเป็น -1

• RoundingMode.Down

ปัดเศษเหมือน Excel Round ยกเว้นตัวเลขที่ลงท้ายด้วย 5 จะปัดลง

เทียบเคียงได้กับ excel สูตร = IF(MOD(A1,0)=0.5,FLOOR.MATH(A1,1),ROUND(A1,0))

เช่น ถ้า 1.5 ปัดเป็น 1

• RoundingMode.Up

ปัดเศษเหมือน Excel Round ยกเว้นตัวเลขที่ลงท้ายด้วย 5 จะปัดขึ้น

เทียบเคียงได้กับ excel สูตร =IF(MOD(A1,0)=0.5,CEILING.MATH(A1,1),ROUND(A2,0))

เช่น ถ้า 1.5 ปัดเป็น 2 , ถ้า -1.5 ปัดเป็น -1

• RoundingMode.ToEven

แบบนี้เป็นค่า default ของ Power Query เมื่อใช้ user interface จาก Ribbon Manu และเป็นวิธีที่ทำให้สับสนที่สุด มันปัดเศษเหมือน Excel Round ยกเว้นตัวเลขที่ลงท้ายด้วย 5

โดยมีเงื่อนไขว่า ถ้าตัวเลขถัดไปเท่ากับ 5 ให้พิจารณาตัวเลขต่อไปทางขวามือ ให้พิจารณาดังนี้

• ถ้ามีตัวเลขตามหลังเลข 5 ให้ปัดไกลศูนย์ (Away From Zero)
• ถ้าไม่มีตัวเลขตามหลังเลข 5 ให้ดูตัวเลขที่อยู่ก่อนหน้า 5 ทางซ้ายมือ
  • หากเป็นเลขคี่ (1 3 5 7 9) ให้ปัดไกลศูนย์ (Away From Zero)
  • หากเป็นเลขคู่ (0 2 4 6 8) ให้ปัดใกล้ศูนย์ (Toward Zero)

เช่น

• 2.5 ปัดเป็นจำนวนเต็ม → เลขซ้ายมือของหลักที่ round คือเลข 2 เป็นเลขคู่ ดังนั้นจะต้องปัดลง จึงได้ 2.5 ปัดเป็น 2
• 3.5 ปัดเป็นจำนวนเต็ม → เลขซ้ายมือของหลักที่ round คือเลข 3 เป็นเลขคี่ ดังนั้นจะต้องปัดขึ้นตามปกติ จึงได้ 3.5 ปัดเป็น 4

ตารางแสดงตัวอย่างการปัดเศษแบบ To Even

แล้วทำไมต้อง ROUND TO EVEN ให้ยุ่งยาก?

การปัดเศษแบบ Round to Even หรือ Bankers’ Rounding เป็นวิธีการปัดเศษที่ใช้ในมาตรฐาน IEEE754 มีอีกหลายชื่อเรียก เช่น convergent rounding, statistician’s rounding, Dutch rounding, Gaussian rounding, odd-even rounding เป็นวิธีปัดเศษเพื่อหลีกเลี่ยง bias จากการปัดเศษที่เกิดจากเลข 5 ซึ่งอาจทำให้ผลรวมของค่าตัวเลขเพี้ยน

ROUND TO EVEN หลีกเลี่ยง bias จากการปัดเศษที่อาจทำให้ผลรวมของค่าตัวเลขเพี้ยนได้ยังไง?

ตารางเปรียบเทียบการปัดเศษแบบปกติกับแบบ To Even

จากข้อมูลใน column “Value” จะมีค่าเฉลี่ยของข้อมูลเท่ากับ 5.50

แต่เมื่อปัดเศษแบบปกติเป็นจำนวนเต็ม จะทำให้ค่าเฉลี่ยเท่ากับ 6.00

ในขณะที่ใช้การปัดเศษแบบ To Even จะได้ค่าเฉลี่ยเท่ากับ 5.45 ซึ่งใกลัเคียงกับค่าเดิมมากกว่าการปัดเศษแบบปกติ นี่คือเหตุผลทำให้การคำนวณบางอย่างจึงเลือกใช้การปัดเศษแบบ To Even

2.2 การปัดเศษลงโดยใช้ Number.RoundDown และ Number.RoundTowardZero

ถ้าเลขเป็นบวก การปัดเศษของ Excel และ Power Query จะได้ค่าตรงกัน

• การปัดเศษลงใน Excel ใช้สูตร RoundDown เช่น

ROUNDDOWN( 1.5 , 0 ) = 1 คือการปัดเศษเลข 1.5 ให้เป็นจำนวนเต็ม จะได้ค่าเท่ากับ 1

• การใช้ ROUNDDOWN ใน Power Query ก็ได้ค่าเท่ากัน สูตรก็คือ

Number.RoundDown( 1.5 ) = 1

แต่ปัญหาจะเกิดเมื่อเป็นเลขติดลบ

การใช้ ROUNDOWN ใน Excel จะไม่เท่า Number.RoundDown ใน Power Query 😮

• ROUNDDOWN( -1.5 , 0 ) = -1
• Number.RoundDown( -1.5 ) = -2

แล้ว ROUNDDOWN ใน Excel มันคืออะไรใน Power Query?

จริงๆ แล้วการ ROUNDDOWN ใน Excel มันคือ การปัดเศษแบบใกล้ศูนย์ นั่นคือ Number.RoundTowardZero ใน Power Query

• ROUNDDOWN( -1.5 , 0 ) = -1
• Number.RoundTowardZero( -1.5 ) = -1

แต่ Number.RoundTowardZero มันไม่มี user interface ที่ ribbon

ดังนั้นจะต้องแก้สูตรเองใน formula bar โดยใช้ mcode (ทำยังไง จะอธิบายให้ตอนท้ายครับ)

อ้าว.. แล้วแบบนี้ Number.RoundDown ใน Power Query มันคืออะไร?

Number.RoundDown ใน Power Query มันคือ FLOOR.MATH ใน Excel เป็นการปัดเศษลงจริงๆ

• Number.RoundDown( -1.5 ) = -2
• FLOOR.MATH( -1.5 ) = -2
  ** หมายเหตุ FLOOR.MATH มีค่า significant default คือ 1 ดังนั้นสูตรข้างบน จึงหมายถึง FLOOR.MATH( -1.5,1 ) แปลว่าให้ปัดลงจนกว่าจะหาร 1 ลงตัว

📝สรุปก็คือ..

• Excel ROUNDDOWN เท่ากับ Number.RoundTowardZero ใน Power Query (ไม่มีใน user interface)
• Excel FLOOR.MATH เท่ากับ Number.RoundDown ใน Power Query (มี user interface)

(งงมั้ย.. พักปาดเหงื่อแป๊บ แล้วไปกันต่อครับ 😓)

2.3 การปัดเศษขึ้นโดยใช้ Number.RoundUp และ Number.AwayFromZero

เมื่อเข้าใจการปัดเศษลงแล้ว การปัดเศษขึ้นก็เข้าใจไม่ยากครับ เพราะให้หลักการเดียวกัน

การ ROUNDUP ใน Excel มันคือ การปัดเศษแบบไกลศูนย์ นั่นคือ Number.RoundAwayZero ใน Power Query

• Number.RoundAwayZero( -1.5 ) = -2
• ROUNDUP( -1.5 , 0) = -2

ส่วน Number.Roundup ใน Power Query มันก็คือ CEILING.MATH ใน Excel ซึ่งคือ การปัดเศษขึ้น

• Number.RoundUp( -1.5 ) = -1
• CEILING.MATH( -1.5) = -1

📝สรุปก็คือ..

• Excel ROUNDUP เท่ากับ Number.RoundAwayZero ใน Power Query (ไม่มีใน user interface)
• Excel CEILING.MATH เท่ากับ Number.RoundUp ใน Power Query (มี user interface)

3. การใช้ user interface ที่ Ribbon ปัดเศษ

การปัดเศษ สามารถใช้ user interface ที่ Ribbon ได้ 3 แบบ คือ

1. Round Up คือ Number.RoundUp ซึ่งเทียบเท่า CEILING.MATH ใน Excel
2. Round Down คือ Number.RoundDown ซึ่งเทียบเท่า FLOOR.MATH ใน Excel
3. Round คือ Number.Round แบบ RoundingMode.ToEven (อันนี้ไม่มีใน Excel)

โดยคลิก Transform หรือ Add Column แล้วเลือกวิธี Round ตามที่ต้องการได้ตามรูปด้านล่าง

รูปแสดงวิธีการ Round โดยใช้ user interface

แต่ถ้าต้องการ round แบบอื่นๆ ต้องเขียน mcode เองครับ

4. การแก้ไข mcode ที่ formula bar

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

4.1 ถ้าต้องการ Round Up หรือ Round Down

เลือก Round Up หรือ Round Down เพื่อเอา mcode เป็นตัวอย่าง จากนั้นแก้ไข mcode ตามที่ต้องการโดยเพิ่มข้อความหลังจุดตามที่ต้องการ

4.2 ถ้าต้องการ Round

เลือก Round แล้วเข้าไปเพิ่ม argument ที่ Formula Bar จะง่ายกว่าวิธีแรก

5. MROUND ใน Power Query ต้องทำยังไง

แต่ Excel ยังมี MROUND นะ.. แล้วใน Power Query มันคืออะไร?

MROUND เป็นการปัดเลขตามช่วงที่ต้องการ

MROUND (number, significance)

• number คือ จำนวนที่ต้องการปัด (The number that should be rounded)
• significance คือ ความกว้างของช่วงข้อมูล (The multiple to use when rounding)

วิธีการปัดเศษของ MROUND มีหลักการคือ

• ถ้าค่าของตัวเลขน้อยกว่าครึ่งของช่วงข้อมูล ก็ให้ปัดลง
• ถ้าค่าของตัวเลขมากกว่าหรือเท่ากับครึ่งหนึ่งของช่วงข้อมูล ก็ให้ปัดขึ้น
• แต่ถ้าเครื่องหมายของ Number กับ Significance ต่างกัน จะ ERROR

ตารางแสดงตัวอย่างการ ROUND

MROUND(A1, 50) หมายความว่า ปัดเลขในช่อง A1 ให้เป็นค่าที่หารด้วย 50 ลงตัว เช่น

• MROUND(120 ,50) =100 เพราะ 120 น้อยกว่า 125 จึงปัดลง
• MROUND(140 ,50) =150 เพราะ 140 มากกว่า 125 จึงปัดขึ้น

แต่ MROUND ใช้กับ Number ที่ติดลบไม่ได้

วิธีแก้คือ ต้องเอาค่า Significant ไปคูณกับเครื่องหมายโดยใช้ SIGN ช่วย

• SIGN(เลขบวก) มีค่าเท่ากับ 1
• SIGN(เลขลบ) มีค่าเท่ากับ -1

= MROUND(-120, 50*SIGN(-120) ) จึงเท่ากับ MROUND(-120, -50 ) = -100

MROUND ใน Power Query

Power Query ไม่มีสูตร MROUND ตรงๆ แต่สามารถทำได้โดยใช้ mcode จากสูตรนี้ครับ

Number.Round([Number]/[Significance],0,RoundingMode.AwayFromZero) * [Significance]

รูปแสดงสูตร MROUND ใน Power Query

6. สรุปส่งท้าย

ตารางสรุปการปัดเศษรูปแบบต่างๆ ใน Excel และ Power Query

จากตารางด้านบน สามารถเปรียบเทียบวิธีปัดเศษของ Excel และ Power Query ได้ดังนี้

• ROUND
  • Excel คือ ROUND( number, num_digits )
  • Power Query คือ Number.Round([Number], 0, RoundingMode.AwayFromZero)    
• ROUNDDOWN
  • Excel คือ ROUNDDOWN( number ,num_digits )
  • Power Query คือ Number.RoundTowardZero( [Number] )        
• ROUNDUP
  • Excel คือ ROUNDUP( number, num_digits )
  • Power Query คือ Number.RoundAwayFromZero( [Number] )
• FLOOR.MATH
  • Excel คือ FLOOR.MATH( number, [significance], [mode] )
  • Power Query คือ Number.RoundDown( [Number] )
• CEILING.MATH
  • Excel คือ CEILING.MATH( number, [significance], [mode] )
  • Power Query คือ Number.RoundUp( [Number] )
• MROUND
  • Excel คือ MROUND( number, significance )
  • Power Query คือ Number.Round([Number]/[Significance],0,RoundingMode.AwayFromZero) * [Significance]

ดังนั้นเวลาปัดเศษใน Power Query ถ้าอยากทำให้ผลลัพท์เหมือน Excel ก็อย่าลืมแก้ RoundingMode ให้เป็น AwayFromZero ทุกครั้งนะครับ 😊

อ้างอิง

• https://www.mrexcel.com/excel-tips/helpful-secrets-about-rounding-in-power-query
• https://powerquery.how/number-round
• https://reportingengineer.com/2020/10/09/power-query-number-round-ep1/
• https://en.wikipedia.org/wiki/Rounding#Round_half_to_even
• https://exceljet.net/functions/mround-function