การกำหนด Custom Number Format

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

วิธีการคือ ต้องกำหนด “รูปแบบ” (Format) ของทั้งสองช่องให้ไม่เหมือนกัน ยกตัวอย่างเช่น

ในช่อง A1, A2, และ A3 มีเลข 2.345 ทั้งคู่

• ช่องแรกกำหนดรูปแบบเป็น General ก็จะเห็นเป็น 2.345
• ช่องที่สองกำหนดเป็น Number ที่มีทศนิยม 2 ตัวแหน่ง จะเห็นค่าเป็น 2.35 แทน (แต่ค่าที่แท้จริงของช่องนั่นก็ยังเป็น 2.345 อยู่นะครับ)
• ช่องสุดท้ายกำหนดรูปแบบเป็นวันที่และเวลา เราจะเห็นค่าเป็น 2/1/1900  8:16:48 (แต่ค่าที่แท้จริงของช่องนั่นก็ยังเป็น 2.345 อยู่นะครับ… ใครงงว่าทำไมเป็นแบบนี้ สามารถไปอ่านได้ที่ การทำงานเกี่ยวกับวันและเวลา )

นี่คือความสามารถของการเปลี่ยนหรือกำหนด Format ที่ทำให้ค่าเดียวกัน แสดงผลไม่เหมือนกันได้

การกำหนด Format เบื้องต้น

ซึ่งปกติการกำหนด Format ทำได้โดยเลือกช่องที่เราต้องการ จากนั้นเลือกแถบ Home => ในช่อง Number สามารถเลือก Drop Down Format ต่างๆ ที่โปรแกรม Excel ได้ทำไว้ให้แล้ว เช่น General, Number, Currency, Date, Text, และอีกมากมาย ให้ลองกดเล่นดูนะครับ ปกติแล้ว Format พวกนี้จะเอาไว้ใช้กับข้อมูลที่เป็นตัวเลขนะครับ

การกำหนด Format แบบขั้นสูง (Custom Format)

เราจะใช้ Custom Number Format เมื่อต้องการกำหนดรูปแบบตัวเลขที่ไม่มีให้เลือกในรูปแบบมาตรฐาน พูดง่ายๆ คือ ถ้าของสำเร็จรูปไม่มีให้ใช้ เราก็เลยต้อง Customize ขึ้นมาซะเองเลย

ซึ่ง Custom Format มีวิธีการทำดังนี้

1. ให้เลือก Cell/Range ช่องที่ต้องการ จากนั้นคลิ๊กขวา => Format Cell => Custom
   หรือ เลือกช่องที่เราต้องการ จากนั้น กด Ctrl+1 => Custom
2. จากนั้นไปตรง Type: แล้วพิมพ์รูปแบบที่ต้องการได้เลยครับ…

แล้วไอ้รูปแบบที่เราต้องพิมพ์ลงไปคืออะไรล่ะ?? ผมจะบอกวิธีให้ครับ

วิธีการเขียน Custom Number Format

หากข้อมูลจริงๆ ที่เราพิมพ์ลงไป เป็นเลข  43210.525 แต่เราต้องการเปลี่ยนให้มีรูปแบบที่แสดงแตกต่างกันไปตาม Custom Format ซึ่งหลังจากเลือก Format Cell –> Custom แล้วจะเห็นว่ามีตัวอย่างวิธีเขียนขึ้นมาให้ดูมากมาย

ซึ่งหลักการที่สำคัญคือ Custom Format แต่ละตัวที่เราพิมพ์ลงไปจะแทนตำแหน่งของตัวเลข 1 ตำแหน่ง โดยมีความหมายไม่เหมือนกัน ซึ่งผมขอแบ่งออกเป็นกลุ่มๆ ได้ดังนี้

กลุ่มแทนตำแหน่งตัวเลข

• 0 (เลขศูนย์) บ่งบอกว่าตำแหน่งนั้นจะต้องแสดงตัวเลขออกมาเสมอ แปลว่า เลข 0 จะถูกแสดงออกมาแม้ไม่จำเป็นก็ตาม
  
• # (เครื่องหมายชาร์ป) บ่งบอกว่าตำแหน่งนั้นไม่ต้องแสดงตัวเลข (เช่น 0) ถ้าไม่จำเป็นจะเห็นว่าเลข 0 ที่เป็นตัวหนาสีแดงยังคงถูกแสดงค่าแม้จะไม่มีความหมายใดๆ
  
  จะเห็นว่ามันจะไม่แสดงเลข 0 ที่ไม่จำเป็น เช่น  เลข 0 ก่อนเลข 4 และหลังเลข 5 ตัวสุดท้าย
• ? (เครื่องหมายคำถาม) บ่งบอกว่าตำแหน่งนั้นถ้าเลข 0 ไม่มีความหมาย ให้แทนด้วยช่องว่าง (ต่างจาก # ตรงที่จะตัดเลข 0 ที่ไม่มีความหมายทิ้งไปเลย)
  
  ว สีแดง จริงๆ คือ ช่องว่าง (space) มีการกินตำแหน่งตัวอักษรจริงๆ

กลุ่มเครื่องหมายประกอบตัวเลข

• . (จุด) จะบอกตำแหน่งของทศนิยมว่าอยู่ตรงไหน (มีการปัดเศษทศนิยม)
  เช่น00 (หลังจุดมี 2 ตัว = ทศนิยม 2 ตำแหน่ง ) ค่าจะจะแสดงเป็น43210.53 (ปัดขึ้น)
  

• , (Comma) เป็นการลดตำแหน่งลงไปทีละ 3 หลัก (หารทีละ 1000) ซึ่งมีประโยชน์มากๆ ในการทำเลขเยอะๆ ยาวๆ ให้อ่านง่ายขึ้น
  • ใส่ comma ตามหลัง 1 ตัวเลขหายไป 3 หลัก (หารพัน)
    เช่น0000, จะได้ว่า = 43210.525/1000 = 43.210525 = 43.2105
  • ใส่ comma ตามหลัง 2 ตัวเลขหายไป 6 หลัก (หารล้าน)
    เช่น0000,, จะได้ว่า = 42310.525/1000000 = 0.043210525 = 0.0432
• E+ เป็นการใส่สัญลักษณ์เลข Scientific เช่น 00 E+00 จะทำให้เห็นเลข เป็น 4.32 E+04
• ใส่ * ตามด้วยอักขระที่ต้องการ เพื่อให้ Excel ใส่เครื่องหมายนั้นๆ ไปจนเต็มเนื้อที่ Cell เช่น 0,000*-
  • จะแสดงเป็น 43,211—————— (ขีดไปจนสุด cell)
• ใส่ _ตามด้วยอักขระที่ต้องการ เพื่อให้ Excel เว้นวรรคด้วยความกว้างของอักขระนั้น
  เช่น _H จะเป็นการเว้นช่องว่างด้วยความกว้างเท่าตัว H (พิมพ์ใหญ่)
  หากใส่เป็น 0,0_H00 จะเห็นเป็น 43,2 11นั่นเอง

กลุ่มตัวอักษร

• หากจะใส่ข้อความ (Text) ร่วมใน Custom Format ด้วย (เช่นเป็น Label หรือเป็น หน่วยการนับ) ให้ใส่ในเครื่องหมายคำพูด
  • ยกเว้น ตัว $ – + / ( ) : space ไม่ต้องใส่เครื่องหมายคำพูดครอบก็ได้
    เช่น “ผมมีเงิน” #,##0.00 “บาท” จะได้ว่า ผมมีเงิน 43,210.53 บาท
    
    สังเกต! ค่าที่แท้จริงใน Cell ยังเป็นตัวเลข ซึ่งสามารถเอาไปคำนวณได้อยู่
• @ เป็นตำแหน่งที่ให้วาง Value ตัวหนังสือที่อยู่ใน Cell (Text Place Holder)
  เช่น @ “บาท” แม้ว่าเวลาพิมพ์ใน Cell ว่า “ห้าร้อย” เฉยๆ แต่ Excel จะแสดงออกมาเป็น “ห้าร้อย บาท” แต่ถ้าใช้ Custom Format ว่า “ผมมีเงิน” @ “บาท” ก็จะขึ้นว่า ผมมีเงิน ห้าร้อย บาท นั่นเอง
   

กลุ่มแทน วันที่ และ เวลา

• วันที่ ใช้สัญลักษณ์ d m y (วัน เดือน ปี) มาแทน ซึ่งใส่ได้หลายรูปแบบมาก ดังนี้
  
• เวลา  ใช้เครื่องหมาย h m s(ชม. นาที วินาที ) มาแทนดังนี้ 

• สัญลักษณ์หลัก คือhแทน ชั่วโมง/ mmแทน นาที / ssแทน วินาที
  • ถ้าใส่เครื่องหมาย [ ] เวลาใส่ลงไปแล้วจะหมายถึงให้ใช้ค่าแบบที่เกินเวลาปกติได้ เช่น [h] เกิน 24 ชม. [m] เกิน 60 นาที [s] เกิน 60 วินาทีได้
• AM/PM, am/pm, A/P, a/p ให้เป็นระบบ AM/PM ถ้าไม่ใส่จะเป็นระบบ 24 ชม.
  

Escape Character

บางทีเราต้องการใส่ character พิเศษให้เป็นคำธรรมดาๆ นอกจากจะใส่ ” ” ครอบแล้ว ยังสามารถใช้ \นำหน้า code นั้นๆ ได้ด้วย

เช่น อยากให้เลข 123 มองเห็นเป็น #123

เราสามารถใส่ custom number format ได้ 2 แบบ คือ

• “#”0
• \#0

การกำหนดสี ด้วย Code [ ]

• [BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW]
• [COLOR 1-56]

Excel จะใส่สีให้กับ format ที่ตามหลัง Code สีต่างๆ
เช่น [blue]0.00 ค่าที่ได้จะเหมือนกับใส่ Format 0.00 แต่ตัวอักษรจะเป็นสีฟ้าแทน

การกำหนด Format โดยขึ้นกับค่าของตัวเลขข้างใน

วิธีนี้ทำให้เรากำหนดให้ใช้ Format โดยขึ้นอยู่กับค่าข้างใน โดยไม่ต้องพึ่งพา Conditional Formatting เลย สามารถทำได้ 2 แบบหลักๆ คือ

กำหนดให้ใช้ Format ให้ขึ้นอยู่กับค่าข้างใน ว่าเป็นเลขบวก เลขลบ เลขศูนย์  หรือ Text

มีรูปแบบคือ ให้ใส่ Syntax โดยคั่นด้วยเครื่องหมาย Semicolon ( ; )

โดยมีรูปแบบดังนี้

Format ทุกค่า  // อันนี้เป็นแบบปกติที่เราใช้กัน คือไม่มี ; เลย

Formatเลขบวก หรือ Formatเลขศูนย์ ; Formatเลขลบ   // อันนี้มี ; 1 ที

Formatเลขบวก ; Formatเลขลบ ; Formatเลขศูนย์   // อันนี้มี ; 2 ที

Formatเลขบวก ; Formatเลขลบ ; Formatเลขศูนย์ ; Formatตัวหนังสือ  // อันนี้เต็มยศสุดๆ มี ; 3 รอบ

ตัวอย่าง

ใส่ Format ให้ต่างกันได้ 3 แบบ โดยกำหนดเงื่อนไขเอง

มีรูปแบบคือ

[เงื่อนไขแรก]formatแรก;[เงื่อนไขที่สอง]formatที่สอง;formatอื่นๆ

เช่น [<-400] [Red](-0.00);[=200][yellow]0.00;[blue]0.00 แปลว่า  

• หากน้อยกว่า -400 ให้ เป็นสีแดง format (-0.00)
• หากเท่ากับ 200 ให้เป็นสีเหลืองformat 0.00
• นอกนั้นให้เป็นสีฟ้า format 0.00\

แปลว่า หากเราใส่ Custom Format เป็นคำว่า ;;; จะทำให้มองไม่เห็นค่าที่พิมพ์ลงไปใน Cell (แต่ยังมองเห็นใน Formula Bar อยู่) แบบนี้เอาไปแกล้งคนอื่นได้ครับ รับรองว่าเห็นแล้วงงแน่นอนว่าทำไมมองไม่เห็นสิ่งที่อยู่ใน Cell 5555

ทำให้เลขกลายเป็น Text ด้วย Format ที่กำหนด

ก่อนหน้านี้เราเรียนวิธีเปลี่ยน Format ไปแล้ว ซึ่งค่าที่แท้จริงจะยังคงเป็นเลขเหมือนเดิมอยู่ อย่างไรก็ตามเราสามารถทำให้ข้อมูลกลายเป็นข้อความจริงๆ ได้ด้วยการใช้ฟังก์ชัน TEXT มาช่วย เช่น

TEXT(value, format_text) 
โดยที่ format_text ให้ใส้ Code แบบเดียวกับ Custom Format Code เลย แต่ให้เป็น Text

สมมติ A1 มีเลข 1234

แล้วเขียนสูตรใน B1 ว่า

=TEXT(A1, "00-000") 

ผลลัพธ์จะได้เป็นข้อความว่า 01-234 ทันที และเป็นข้อความจริงๆ ด้วย ไม่ใช่แค่ Format ครับ

ตารางสรุป