co-create
บทความนี้คือส่วนหนึ่งของโครงการ ร่วมสร้าง “หนังสือคู่มือ Excel ที่เจ๋งที่สุด” ใครที่มี comment เพื่อแนะนำ ปรับปรุงหนังสือได้ คุณจะได้เครดิตในฐานะผู้ร่วมเขียน ลงในหนังสือที่จะพิมพ์จริงๆ ด้วย! อ่านรายละเอียด และดูสารบัญหนังสือ คลิ๊กที่นี่


 

เราจะใช้ 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

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

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

custom-format-number

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

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

custom-format-number2

  • . (จุด) จะบอกตำแหน่งของทศนิยมว่าอยู่ตรงไหน (มีการปัดเศษทศนิยม)
    เช่น00 (หลังจุดมี 2 ตัว = ทศนิยม 2 ตำแหน่ง ) ค่าจะจะแสดงเป็น43210.53 (ปัดขึ้น)
    dot
  • , (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 บาท
      custom-format-text1
      สังเกต! ค่าที่แท้จริงใน Cell ยังเป็นตัวเลข ซึ่งสามารถเอาไปคำนวณได้อยู่
  • @ เป็นตำแหน่งที่ให้วาง Value ตัวหนังสือที่อยู่ใน Cell (Text Place Holder)
    เช่น @ “บาท” แม้ว่าเวลาพิมพ์ใน Cell ว่า “ห้าร้อย” เฉยๆ แต่ Excel จะแสดงออกมาเป็น “ห้าร้อย บาท” แต่ถ้าใช้ Custom Format ว่า “ผมมีเงิน” @ “บาท” ก็จะขึ้นว่า ผมมีเงิน ห้าร้อย บาท นั่นเอง
    custom-format-text2 

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

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

การกำหนดสี ด้วย 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 ( ; )
    โดยมีรูปแบบแยกเป็น 4 ก้อน ดังนี้
    Formatเลขบวก ; Formatเลขลบ ; Formatเลขศูนย์ ; Formatตัวหนังสือ
    เช่น [Color43]0.00;[Red]-0.00;[blue]0.00; @“บาท”
    condition-custom1
  • ใส่ 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