Blog

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

อย่างเช่น การเปลี่ยนเลขอารบิก ให้เป็นเลขไทย เป็นต้น

ก็อย่างที่ทราบกันล่ะนะ Excel ออกแบบมาให้ใช้งานกับตัวเลข และตัวเลขใน Excel นั้นมีหลากหลายรูปแบบ ตั้งแต่ “ตัวเลข” (แหงล่ะ) เศษส่วน เปอร์เซ็นต์ วันที่ (อันนี้ก็เก็บเป็นตัวเลข แล้วจะเขียนถึงอีกที) เวลา โอ้ย อีกเยอะะแยะ

ซึ่งถ้าเราไปดู Ribbon Home จะเห็นส่วนเครื่องมือที่เรียกว่า Numbers โดย ค่าตั้งต้น ปกติจะเป็น General ไว้ให้ แต่เราเปลี่ยนเองได้ เปลี่ยนให้เป็น Text ก็ยังได้

ค่าตั้งต้น General นั้น ทาง Excel จะเลือกรูปแบบที่เหมาะสมให้ คือถ้าเราพิมพ์ในลักษณะวันเดือนปี มันก็จะแสดงเป็นวันเดือนปีให้เลย แบบนี้เป็นต้น และที่จะเป็นอัตโนมัติอีกอย่างคือ การแสดงผลจะคำนึงถึงความกว้างของ Cell ประกอบด้วย

ยกตัวอย่างที่เห็นได้ชัดก็คือ การแสดงทศนิยม สมมติว่าเรามีทศนิยมหลายตำแหน่ง เช่นตัวอย่างจะเป็น 123.456789 ถ้าเป็น Cell ที่กว้างมากพอ มันก็จะแสดงค่า 123.456789 แต่ถ้า Cell แคบมันก็จะจำกัดการแสดงทศนิยมลง ไปจนถึงขั้นที่ว่า ปัดเศษไม่มีทศนิยมเลยก็ได้ หรือเปลี่ยนเป็นใช้รูปแบบเลขทางวิทยาศาสตร์ (เลขสัญกรณ์วิทยาศาสตร์ – scientific notation) หรือ ถ้าแคบเกินกว่าจะแสดงค่าจำนวนเต็มได้ ก็จะแสดงเป็นอักขระพิเศษ คือ # เพื่อความเหมาะสม

เรียกใช้ Custom Number Formats

วิธีการเรียกใช้งาน ก็เพียงแค่คลิกขวาที่ Cell เลือก Format Cells แล้วไปที่แท็บ Number เลือก Custom

หรือจะไปที่ ribbon Home ไปที่แท็บ Numbers กดลูกศรตรงมุมขวาล่าง ก็ได้เหมือนกัน

ลองดูที่หน้าต่างด้านซ้าย (Categories) จะเห็นว่า Excel ได้แยกประเภทของรูปแบบต่าง ๆ ไว้ให้เราเลือกใช้เยอะแยะอยู่ ทั้ง General, Number, Currency ไปจนถึง Custom เมื่อเราคลิกเลือกอันใดอันหนึ่ง เราจะได้เห็นรูปแบบที่ Excel ทำไว้สำเร็จรูปมาให้มากมาย

สำหรับ Custom Formats จะอยู่ล่างสุด คลิกไปก็จะเห็นตัวอย่างขึ้นมามากมาย เราจะเลือกที่เขาทำไว้ให้มาใช้ก็ได้ หรือจะเลือกมาปรับแต่งเล็ก ๆ น้อย ๆ ก็ได้ (คลิกเลือกแบบที่ต้องการ แล้วแก้ไขในช่อง Type) หรือจะเขียนรูปแบบที่ต้องการลงใน Type เลยก็ได้

TIPS: เราสามารถใช้ คีย์บอร์ดช็อตคัต ในการกำหนดรูปแบบของตัวเลข โดยคลิกเลือก Cell ที่ต้องการ แล้วคลิก คีย์บอร์ดช็อตคัต ตามตารางนี้

Format	Shortcut
General format	Ctrl Shift ~
Currency format	Ctrl Shift $
Percentage format	Ctrl Shift %
Scientific format	Ctrl Shift ^
Date format	Ctrl Shift #
Time format	Ctrl Shift @
Custom formats	Ctrl 1

ประเภท	คำอธิบาย	ตัวอย่าง
General	ไม่ได้กำหนดตายตัว	1234.56
Number	กำหนดเป็นตัวเลข โดยจะมีหรือไม่มีคัวคั่นหลักพันและทศนิยมก็ได้	1,234.56
Currency	รูปแบบสกุลเงิน	$1,234.56
Accounting	รูปแบบการบัญชี ที่กำหนดรูปแบบสัญลักษณ์สกุลเงินแยกจากตัวเลข โดยแสดงจุดทศนิยมหรือไม่ก็ได้	$     1,234.56
Date	รูปแบบวันที่ (เก็บข้อมูลเป็นตัวเลขเรียงลำดับ)	11/05/2021
Time	รูปแบบเวลา (เก็บข้อมูลเป็นตัวเลขเรียงลำดับ)	13:17:33
Percentage	รูปแบบเปอร์เซ็นต์  (คูณค่าเซลล์ด้วย 100) แสดงสัญลักษณ์ร้อยละ (%)	12.30%
Fraction	รูปแบบเศษส่วน (แสดงตัวเลขเป็นเศษส่วนแทนทศนิยม)	1 3/4
Scientific	รูปแบบทางวิทยาศาสตร์ แสดงตัวเลขในรูปแบบเอกซ์โพเนนเชียลแทนที่ส่วนหนึ่งของจำนวนด้วย E + n โดยที่ E (ซึ่งย่อมาจาก Exponent) จะคูณจำนวนที่อยู่ก่อนหน้าด้วย 10 ยกกำลัง n ตัวอย่างเช่นรูปแบบ Scientific ทศนิยม 2 ตำแหน่งจะแสดง 12345678901 เป็น 1.23E + 10 ซึ่งก็คือ 1.23 คูณ 10 ยกกำลัง 10	1.23E+08
Text	รูปแบบข้อความ แม้จะพิมพ์ตัวเลขเข้าไป แต่จะได้รับกำหนดเสมือนเป็นข้อความ	1234
Special	รูปแบบพิเศษ เช่น รหัสไปรษณีย์ หมายเลขโทรศัพท์	12345 12345-1234 (123) 456-7899 123-45-6789
Custom	กำหนดรูปแบบเอง	N/A

ซึ่งเราต้องมาทำความรู้จักกันว่า สัญลักษณ์อักขระต่าง ๆ ที่ใช้นั้น มันแทนรูปแบบอะไรกันแน่ ซึ่งก็คือเนื้อหาของบล็อกนี้แหละ

ไวยากรณ์กำหนดรูปแบบ Custom Number Formats

ไวยากรณ์ หรือ โครงสร้าง ในการกำหนดรูปแบบการแสดงผลตัวเลข จะมีด้วยกัน 4 รูปแบบหลัก ซึ่งจะใช้ ; (เซมิโคลอน หรือ semi-colon) เป็นตัวแบ่งแต่ละส่วนออกจากกัน โดยแต่ละส่วนนั้นประกอบด้วย

อ้างอิงรายละเอียดตรงนี้ อ่านได้จากเว็บไซต์ของไมโครซอฟต์ได้โดยตรง

<ค่าตัวเลขเป็นบวก>; <ค่าตัวเลขเป็นลบ>; <ค่าตัวเลขเป็นศูนย์>; <ตัวอักขระ>

ตัวอย่างเช่น

[Blue]#,##0.00_);[Red](#,##0.00);0.00;"sales "@

ถึงจะบอกว่าประกอบด้วย 4 ส่วน แต่มีเพียงแค่ 1 ส่วนก็ใช้งานได้แล้ว ไม่จำเป็นต้องมีครบทั้งหมด อย่างเช่น ตำแหน่งค่า 0 ไม่ต้องใส่ก็ได้ แต่ในตัวอย่างข้างต้น อยากให้แสดงทศนิยมสองตำแหน่งด้วย ก็ต้องกำกับเอาไว้

ใส่ไปส่วนหนึ่งส่วนใดก็ได้ Excel อันชาญฉลาดจะปรับปรุงส่วนที่เหลือให้โดยอัตโนมัติ อันนี้เป็นข้อดีอย่างมากของ Excel

ยกตัวอย่างเช่น ตัวอย่างด้านล่างจะมี 2 ส่วน คือ ค่าตัวเลขเป็นบวก; ค่าตัวเลขเป็นลบ โดยที่แตกต่างกันคือค่าที่เป็นลบจะเป็นสีแดง

฿#,##0;[Red]-฿#,##0

อักขระและวิธีการใช้

สำหรับอักขระหลายตัว สามารถใช้เพื่อแสดงผลได้เลย นั่นคือ

อักขระ	เครื่องหมาย
$	เงินดอลลาร์
฿	เงินบาท
+ –	บวก และ ลบ
()	วงเล็บ
{}	ปีกกา
<>	น้อยกว่า มากกว่า
=	เท่ากับ
:	ทวิภาค
^	ยกกำลัง
‘	Apostrophe
/	ทับ
!	อัศเจรีย์t
&	Ampersand
~	ตัวหนอน
	Space character

แต่ อักขระพิเศษ บางตัว จะไม่สามารถให้แสดงผลได้ ถ้าต้องการให้แสดงผลต้องใส่ \ (backslash) เพื่อระบุให้แสดง นั่นคือ * #  และ % ถ้าจะใช้ ต้องมี \ นำหน้าเสมอ สาเหตุก็เพราะ ตัวอักขระเหล่านี้จะมีฟังก์ชันของตัวเองใน format

อย่างเช่น ถ้าเราอยากให้แสดงผลเป็น #12345 เราจะมีวิธีเขียนโค้ดแบบนี้

\##

คือถ้าเราใส่ # ไปอย่างเดียว มันจะเป็นการใส่ให้ตัวเลขแสดงผลแบบไม่บังคับหลัก เราต้องใส่ \# เพื่อบอกให้ Excel แสดงเครื่องหมาย # ส่วน # ตัวที่ 2 เป็นการบอกให้แสดงจำนวนเต็มแบบไม่บังคับหลัก จะมีกี่หลักก็ได้

หรือในอีกกรณีหนึ่ง เราอาจจะเลี่ยงไปโดยบังคับให้ # เป็น text โดยใส่เครื่องหมายคำพูด ” ” ครอบไว้

"#"# 

แบบนี้ก็ได้เหมือนกัน

ความหมายของอักขระ

0 (เลขศูนย์) จะเป็นการบังคับหลักของตัวเลขที่จะแสดง อย่างเช่นเราต้องการให้แสดงผลออกมา 4 หลัก เราก็ใส่ 0000 ลงไปตรงนี้ ถ้าค่าตัวเลข 1 จะแสดงเป็น 0001 หรือถ้ากำหนดเป็น 000.000 แล้วใส่เลข 1.01 ผลลัพธ์ที่ได้ก็จะเป็น 001.010 แบบนี้เป็นต้น

# (ชาร์ป – sharp หรือ Pound sign หรือบางคนเรียก สี่เหลี่ยม) จะไม่บังคับหลักของตัวเลขที่แสดง เช่นใส่ #### ถ้าใส่เลข 1 ก็จะแสดงผลเป็น 1 ถ้ากำหนด ###.### แล้วใส่เลข 1.01 ผลลัพธ์ที่ได้ก็จะเป็น 1.01 แบบนี้เป็นต้น

? (ปรัศนี หรือ Question Mark หรือหลายคนเรียก เครื่องหมายคำถาม) ตัวนี้เหมือนกับ 0 แต่แทนที่จะเป็นแสดงผลเป็นศูนย์ จะแสดงผลแทนที่ด้วยช่องว่าง ซึ่งถ้าเป็นตรงจำนวจเต็ม จะไม่ค่อยเห็นความแตกต่างเท่าไหร่ เพราะช่องว่างด้านหน้าไม่แสดงผลอะไรให้เห็นอยู่แล้ว (ไม่เหมือน 0) พูดง่าย ๆ คือ ทำให้ “ตำแหน่งของจุดทศนิยมตรงกัน” พอใส่ ? ก็จะปัดเศษตามจำนวนหลักทศนิยม และถ้าตรงไหนไม่มีเลข จะเพิ่มช่องว่างเข้ามาให้ครบตามหลักที่กำหนด อย่างเช่น เราใส่ format เป็น 0.0? คือจะกำหนดให้มีทศนิยม 2 ตำแหน่ง แล้วลองใส่ตัวเลข 1.11 และ 11.1 จะเห็นว่าตำแหน่งจุดทศนิยมจะตรงกัน เพราะใน 11.1 มันจะเพิ่มช่องว่างไว้หลัง “.1” ให้ด้วย เสมือนทศนิยม 2 ตำแหน่ง แต่แสดงแค่ตำแหน่งเดียว

. (จุด หรือ period หรือ dot) จะเป็นตัวคั่นทศนิยม

, (จุลภาค หรือ comma) เป็นตัวคั่นหลักพัน

* (ดอกจัน หรือ asterisk ) ตัวนี้จะต้องตามด้วยสัญลักษณ์อะไรสักอย่างเสมอ เป็นตัวกำหนดว่าให้ใส่สัญลักษณ์ที่ตามหลังดอกจันทร์ให้เต็ม Cell เช่น *- จะใส่ – ตามหลังตัวเลขใน Cell นั้นจนเต็ม

_ (ขีดล่าง หรือ Underscore) ตัวนี้จะเป็นการใส่ “วรรค” ใช้เพิ่มช่องว่างในรูปแบบตัวเลข อักขระที่อยู่ถัดจาก _ (ขีดล่าง) จะบอกว่าเพิ่มช่องว่างเท่าใด โดยทั่วไปจะเพิ่มช่องว่างเพื่อจัดแนวค่าบวกและค่าลบ ตัวอย่างเช่นรูปแบบตัวเลข “0 _); (0)” คือเพิ่มช่องว่างทางด้านขวาของจำนวนบวกเพื่อให้สอดคล้องกับจำนวนลบซึ่งอยู่ในวงเล็บ

E+ (exponent) เป็นการใส่สัญลักษณ์เลขยกกำลัง) แทนความหมายของ ×10ยกกำลังa  ตัวอย่างเช่น 5.9736E+24 หรือ 5.9736E24 ก็คือ 5.9736×10ยกกำลัง24 (ขออภัย ข้าพเจ้าเขียนเครื่องหมายยกกำลังในเครื่องที่กำลังพิมพ์นี้ไม่ได้)

@ (at sign) เป็นตำแหน่งที่ให้วาง Value ตัวอักขระที่อยู่ใน Cell เช่น กำหนดไว้เป็น @ “บาท” พิมพ์ใน Cell “สิบ” จะแสดงออกมาเป็น “สิบ บาท” หรือถ้าใส่เป็น “ค่ารถ” @ “บาท” ก็จะแสดงว่า “ค่ารถ สิบ บาท

ถ้าอยากใส่คำหรือประโยคใดใด ลงไปในอักขระพิเศษ ให้ใช้เครื่องหมายคำพูด “” ครอบไว้

การทำงานกับวันที่และเวลา

Excel เก็บวันที่เป็นตัวเลข แล้วค่อยแสดงผลเป็นวันที่ ดังนั้นเราจึงสามารถกำหนดรูปแบบการแสดงผลได้หลากหลาย ตามปกติ ถ้าเราพิมพ์ข้อมูลวันที่ลงไปตรง ๆ เช่น 5/11/2020 Excel ก็จะรับค่าเป็นวันที่ให้ แสดงออกมาเป็น 5 /11/ 2020 ให้เลย แต่เราสามารถกำหนดลักษณะการแสดงผลได้จาก Category DATE ซึ่งมีรูปแบบให้เลือกมากมาย ปกติใช้จากตรงนี้ก็เพียงพออยู่แล้ว

TIPS: Custom Number Formats สำหรับปีพุทธศักราช หรือ วันแบบไทย ๆ นี้ เราสามารถเลือกได้ ให้ตั้ง locale เป็น Thai และ Calendar Type เป็น Thai Buddhist

อย่างไรก็ตาม เราสามารถปรับแต่งกำหนดได้ตามใช้ชอบดังนี้

Code	การแสดงผล
d	วัน 1 – 31
dd	วัน 01–31
ddd	ชื่อวันแบบย่อ
dddd	ชื่อวันแบบเต็ม
m	เดือน 1–12
mm	เดือน 01–12
mmm	ชื่อเดือนแบบย่อ
mmmm	ชื่อเดือนแบบเต็ม
mmmmm	แสดงตัวอักษรตัวแรกของเดือน
yy	ปี แบบย่อ 2 หลักหลัง
yyyy	ปีแบบ 4 หลัก

เรื่องวันที่ ได้เขียนอีกครั้งที่บล็อกนี้ โปรดอ่านประกอบ

ใน Excel จะเก็บเวลาเป็นรูปแบบ เศษส่วนของวัน ตัวอย่างเช่น 12:00 (เที่ยง) คือ 0.5 และ 18:00 นาฬิกา คือ 0.75 ซึ่งเราสามารถกำหนด Custom Number Formats ปรับแต่งให้แสดงเวลาได้อย่างที่เราต้องการเช่นกัน

code	การแสดงผล
h	ชั่วโมง 0–23
hh	ชั่วโมง 00–23
m	นาที 0–59
mm	นาที 00–59
s	วินาที 0–59
ss	วินาที 00–59
h AM/PM	ชั่วโมง ในรูปแบบ AM/PM
h:mm AM/PM	แสดงชั่วโมงนาทีในแบบ AM/PM
h:mm:ss A/P	ชั่วโมง นาที วินาที ในแบบ AM/PM แต่ตัวกำกับจะแสดงแค่ A หรือ P
[h]:mm	นับจำนวนชั่วโมง
[mm]:ss	นับจำนวนนาที
[ss]	นับจำนวนวินาที
h:mm:ss.00	เศษของวินาที

สำหรับการนับเวลา จะใช้วงเล็บเหลี่ยม [ ] ครอบไว้เพื่อให้แตกต่างจากการแสดงเวลา

โปรดระวัง เนื่องจากตัว m เป็นอักขระใช้แทน เดือน ด้วย ถ้าใช้โค้ด m โดยไม่มี H หรือ SS กำกับ จะแสดงผลเป็นเดือน

โปรดศึกษาจากเว็บของไมโครซอฟต์อีกครั้ง

กำหนดรูปแบบด้วยสี

เราสามารถกำหนดสี ใน Custom Number Formats ที่ต้องการมีทั้งหมด 8 สี ใส่โดยมีวงเล็บเหลี่ยม [ ] กำกับ คือ [black] [white] [red][green] [blue] [yellow] [magenta] [cyan] ใส่นำหน้าตรงบริเวณที่ต้องการ

ขณะเดียวกัน เราสามารถกำหนด เงื่อนไข ให้แสดงสีที่แตกต่างกันได้ โดยใช้ให้เงื่อนไขอยู่ในวงเล็บเหลี่ยม [ ] ซึ่งถ้าค่านั้นเป็นไปตามเงื่อนไขก็จะเปลี่ยนสีค่านั้นไปอย่างที่กำหนด เช่น ถ้าสร้างเงื่อนไขว่า ถ้าค่าที่ใส่นั้น มากกว่า 1,000 ให้เป็นสีแดง จะเขียนโค้ดได้ว่า

[Red][>1000]0;0

และอาจจะกำหนดซ้อนไปในอีกเงื่อนไขก็ได้ เช่น ถ้าค่านั้น น้อยกว่าหรือเท่ากับ 1000 ให้เป็นสีน้ำเงิน ก็จะได้โค้ดว่า

[Red][>1000]0;[Blue][<=1000]0 

นี่คือเรื่องราวเกี่ยวกับ Custom Number Formats หรือ การกำหนดรูปแบบตัวเลขใน Excel หวังว่าจะช่วยให้ทำงานกันสะดวกขึ้น

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