Hiểu thêm về Number Format

Thảo luận trong 'Ứng dụng Excel' bắt đầu bởi Bình_OverAC, 20 Tháng mười hai 2004.

5,965 lượt xem

  1. Bình_OverAC

    Bình_OverAC Over Abnormal / Crazy

    Bài viết:
    845
    Đã được thích:
    7
    Nơi ở:
    Nha Trang
    Không biết các bạn biết về Number Format như thế nào?
    Đồi với tôi Number format có nhiều điều để nói. Sau đây là tất cả những gì tôi biết về Number Format mời các bác xem và cho ý kiến.

    1. Kích hoạt hộp thoại định dạng và các kiểu định dạng số cơ bản.


    a. Kích hoạt hộp thoại định dạng: Trình đơn định dạng (format menu) à Cells… Hay nhấn phím chuột phải (hay nút lệnh menu) và chọn “Format Cells” (hay có thể nhấn “F”), hay Ctrl + 1

    b. Các kiểu định dạng cơ bản:

    i. Các kiểu cơ bản
    - Gerneral (chung chung)
    - Number (số)
    - Currency (tiền tệ)
    - Accounting (kế toán)
    - Date (ngày)
    - Time (tháng)
    - Percentage (phần trăm)
    - Fraction (phân số)
    - Scientific (khoa học)
    - Text (chuổi)
    - Special (định dạng đặc biệt).
    - Custom (Tự định dạng)

    ii. Cách sử dụng:

    - Chọn một kiểu định dạng cơ bản trong phần category
    - Chọn tiếp kiểu định dạng chi tiết (type)
    - Xem thử xem bạn đã định được định dạng như thế nào thể hiện trên khung mẩu (“Sample”).

    Một phím tắt cho các định dạng phổ biến (phổ biến cho dân Mỹ thì có!!)
    Tổ hợp phím --> Mã định dạng --> Ví dụ

    Ctrl + Shift + ~ => General => 12345.67
    Ctrl + Shift + ! =>0.00 => 12345.67
    Ctrl + Shift + @ =>h.mm => 6:61
    Ctrl + Shift + # =>d-mmm-yy => 15- Apr-95
    Ctrl + Shift + $ =>$#,##0.00);($#,##0.00) => $12,345.67
    Ctrl + Shift + % =>0% =>12%
    Ctrl + Shift + ^ =>0.00E+00 =>1.23E+04
    2. Tự định dạng: Đây là phần chính mà tôi muốn đề cập trong công cụ này.


    · Trước tiên Bạn muốn tự định dạng thì phải chọn Custom trong kiểu định dạng Category.
    · Bạn sẽ thấy hộp danh sách “Type” bây giờ bị chèn giữa bởi một hộp chuổi (text box) để bạn gõ vào mã định dạng mà bạn muốn.

    a. Cấu trúc của mã định đạng:

    Mã định dạng gồm bốn phần: Phần dương; Phần âm; Phần zero; Phần chữ

    Các phần này có nghĩa là nếu giá trị được nhập vào trong ô là bao nhiêu thì nó sẽ xem có tương ứng với phần nào sẽ định dạng theo kiểu đó, nếu không tìm thấy kiểu định dạng thích hợp thì sẽ đưa về dạng chung nên bạn không cần thiết phải nhập đủ 4 phần

    Thư xem xét một mã định dạng sau:

    #,###;[Red](#,###); "-"??;[Red] \"@\" "is not allowed"

    Dịch mã:

    - Phần dương #,### : không có gì đặc biệt có dấu “,”ngăn cách hàng ngàn

    - Phần âm [Red](#,###): màu đỏ nằm trong ngoặc tròn

    - Phần số 0: thì ghi thành một dấu gạch và cách lề một khoảng cở 2 ký tự

    - Phần chữ: thì hiện ra thành ““--------” is not allowed” màu đỏ, với ---- là chuổi ký tự mà bạn nhập vào?

    b. Mã định dạng có điều kiện:

    Phần cấu trúc mã định dạng trên là phần cấu trúc mặc định. Nếu có điều kiện thì Excel hiểu các mã như sau: Điều kiện 1, mã 1; điều kiện 2, mã 2; điều kiện 3, mã 3.. Nếu điều kiện 3 trùng với điều kiện 2 hoặc là một phần trong điều kiện 2 thì điều kiện 3 sẽ không được xem xét tới. Nghĩa là trong phần mã này Excel chỉ quan tâm đến điều kiện mà nó thấy là được thoả mãn trước tiên., Bạn hãy xem xét mã sau:

    [Blue][>100] #,##0;[Green][<=100]#,##0;[Red]

    - Màu xanh nếu số được nhập lớn hơn 100

    - Màu xanh lá nếu nhỏ hơn hoặc bằng 100

    - Màu đỏ thật ra không được xem xét tới vì nó có đươc hiểu là nếu không thoả mãn hai điều kiện đầu thì tới phiên nó nhưng nó làm gì có cữa!!

    - Còn phần thứ 4 (ở đây thì không có phần thứ tư) (nếu có) phải là chữ
     
    #1
  2. Bình_OverAC

    Bình_OverAC Over Abnormal / Crazy

    Bài viết:
    845
    Đã được thích:
    7
    Nơi ở:
    Nha Trang
    tiếp theo ở trên (đây là bảng các loại mã định dạng)

    c. Bảng mã định dạng


    Ký hiệu --> Giải thích

    General

    Từ general thực sự chỉ là một ký hiệu định dạng. Ví dụ bạn có thể định dạng cho một số dương nhưng lại để số âm là general (Mã: “#,###;General”)

    Ký hiệu General hiển thị gần như chính xác như khi nó nhập vào.

    0

    Ký hiệu 0 đóng vai trò như số thể chổ cho một ký hiệu số. Ký hiệu 0 chỉ ra rằng nếu số được định dạng không có nhiều ký tự số như số 0 trong mã định dạng thì số 0 sẽ thế chổ số đó vì dụ nếu mã định dạng là 0000.000 thì số 123.45 mà bạn gõ vào sẽ được hiển thị là 0123.450

    #

    Ký hiệu # đóng vai trò như ký hiệu 0 chỉ khác là nó không buộc một ký số phải thế chổ nếu không có số nào tương ứng. Ví dụ nếu mã định dạng là #,###.## thì số 1234.1 sẽ được hiển thị là 1,234.5.

    ?

    Ký hiệu này cũng giống ký hiệu 0 chí có khác là nếu trống chổ thì nó thay bằng một khoản trắng chứ không phải là số 0 điều này hữu ích nếu bạn muốn canh thẳng đấu phân cách thập phân trong bảng. Ngoài ra ký hiệu ? trong cũng được sử dụng trong phần định dạng phân số. Ví dụ: # ???/??? thì số được thể hiện sẽ đuợc trình bày chính xác đến 3 con số và nếu có thể giản ước tốt thì nó hiện ra đến mức dưới 2 và có thêm khoản trắng.

    .

    Ký hiệu . cho phép bạn xác định số chữ số xuất hiện sau dấu chấm phần thập phân. Nếu mã định dạng có dấu chấm phần thập phân thì một dấu chấm thể hiện phần thập phân luôn xuất hiện cho dù có hay không có các chữ số sau phần thập phân. Nếu mã định dạng có dấu # trước dấu chấm thập phân thì một con số nhỏ hơn 1 sẽ được Excel thể hiện bằng số bắt đầu bằng dấu chấm thập phân ví dụ .23 chứ không phải là 0.23. Để tránh trường hợp này bạn nên ký hiệu số 0 thay vì # trước dấu thập phân. Ví dụ : #,##0.00

    %

    Nếu bạn sử dụng ký hiệu này thì khi hiển thi số Excel sẽ tự động nhân số đó với 100 và thêm dấu này đằng sau.

    e+,e-, E+, E-

    Thể hiện một số dưới dạng khoa học ví dụ: mã là 0.00E+00 thì số 12345.56 được thể hiên 1.23E+04

    -+()$:

    khong trng

    Các ký hiệu này được thể hiện trong mã định dạng thì sẽ được thể hiện trong phần hiển thị cúa số đó. Các ký hiệu khác muốn được thể hiện ra thì bạn phải tham khảo ký tự \ bên dưới

    \

    Đây là ký hiệu đặc biệt không thể hiện trong định dạng nhưng nó bằt buộc Excel thể hiện ký tự tiếp theo sau nó dù ký tự đó là ký tự gì ví dụ: \” thì dấu ” sẽ được thể hiện. Nếu bạn muốn thể hiện dấu \ thì bạn phải ghi trong mã \\

    *

    Giống như ký hiệu \ nhưng khác một điều là Excel sẽ hiện ra ký tự sau nó chiếm đến hết ô

    _

    Ký hiệu này nhắc nhở Excel chen một khoảng trắng vào vị trí của nó. Ví dụ: trong một định dạng có dấu ngoặc bao quanh số âm, dấu chấm thập phân của một số dương không được canh, bởi vì số âm sẽ mất nhiều vị trí hơn bên phải dấu thập phân vì phải thể hiện dấu đóng ngoặc. Trong trường hợp này bạn dùng một dấu _ tại cuối phần định dạng của số dương để Excel dành một khoảng trắng sau số dương một khoảng trăng tương đương với vị trí dấu đóng ngoặc trong số âm. Ví dụ: #,##0_);(#,##0).

    “text”

    Trong trường hợp bạn muốn thể hiện một chuổi ký tự sau hay trước một số thì bạn bỏ chuỗi ký tự đó trong dấu ngoặc kép như thế này."VND" #,##0.00 thì một số bất kỳ sẽ được thêm vào đằng trước “VND”

    @

    Ký hiệu @ dùng để chỉ chuổi ký tự đã được nhập vào ô. Trong ví dụ ở phần trên ký hiệu @ thể hiện những gì đã nhập vào ô.

    m

    Chỉ tháng (1-12)

    mm

    Chỉ tháng nhưng được thể hiện bởi 2 chứ sô ví dụ tháng 1 thành 01

    mmm

    Chỉ tháng nhưng là với 3 chữ đầu của tháng (theo tiếng Anh) vì dụ tháng 1: Jan

    mmmm

    Chỉ tháng và hiện đầy đủ tên tháng ví dụ tháng 1: January

    mmmmm

    Chỉ tháng nhưng chỉ là ký tự đầu tiên của tháng thôi ví dụ; tháng 1: J

    d

    Chỉ ngày với 1 chứ số đối với số nhỏ hơn 10

    dd

    Chỉ ngày vơi 2 chữ số đối với số nhỏ hơn 10 ví dụ ngày 8 thành 08

    ddd

    Chỉ thứ trong tuần với 3 ký tự ví dụ thứ Hai: Mon, thứ Ba: Tue

    dddd

    Chỉ thứ trong tuần với đầy đủ các ký tự

    yy

    Chỉ năm với 2 số cưối của năm

    yyyy

    Chỉ năm vơi đủ 4 số của năm

    h

    Chỉ giờ với 1 chử số

    hh

    Chỉ giở với 2 chữ số

    m

    Chỉ phút với 1 chữ số nếu đứng sau giờ nếu không đuợc hiểu là tháng

    mm

    Chỉ phút vơi 2 chữ số nếu đứng sau giờ nếu không đuợc hiểu là tháng

    s

    Chỉ giây với 1 chữ số

    ss

    Chỉ giây với 2 chữ số

    AM/PM

    Buôc Excel hiển thị giờ với 12 tiếng, và hiển thị AM, PM hay bạn cũng có thể dùng am/pm, A/P, a/p

    [color]

    Dùng để thể hiện màu. Với các màu là [Green], [Blue], [Yellow], [Red], [Magenta], [Black], [Cyan], [White]

    [condition]

    Dùng để ra điều kiện vơi các toán tử so sánh: >, <, =, >=, <= , <> (không bằng). Ví du: [>100] : điều kiện lớn hơn 100.
     
    #2
  3. WhoamI

    WhoamI Thành viên thân thiết

    Bài viết:
    534
    Đã được thích:
    0
    Nơi ở:
    Cố lên, cứ đi rồi sẽ tới!
    Thêm nữa này:
    VD: Nếu bạn muốn ngày 01/01/05 của bạn được hiện ra với hình thức như sau: Ngày 01 tháng 01 năm 2005 trong các bảng biểu như Tờ khai thuế GTGT, bảng lương...v...v....bình thường bạn thường phải gõ:
    = "Ngày "&day($A$1)&" tháng "&month($A$1)&" năm "&year($A$1)

    Tiện hơn rất nhiều khi bạn chỉ cần vào:
    - Format Cells (hay có thể nhấn “F”), hay Ctrl + 1
    - Custom (Tự định dạng)
    - Ô Type bạn gõ nhập: " Ngày " dd " tháng " mm " năm " yyyy
    Thử xem sao nhé!

    Khi bạn để hộp sáng tại ô này trên Formular bạn sẽ thấy 01/01/05 mặc dù nó vẫn được hiển thị Ngày 01 tháng 01 năm 2005 và bạn vẫn thao tác được kiểu date bình thường trên ô này vì:

     
    Last edited: 26 Tháng ba 2005
    #3
  4. ForestC

    ForestC Thành viên sơ cấp

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    E'rywhere
    hi ..
    WhoamI đã post bài này thì nhân đây MaiKa cũng vào đây bổ xung thêm để bài của em trở nên hoàn chỉnh ạ:

    Các bạn hãy dùng công thức công của WhoamI ở dạng dưới đây thì kết quả hiện ra sẽ đẹp hơn:

    ="Ngày "&if(day($A$1)>9;day($A$1);"0"&day($A$1))&" tháng "&if(month($A$1)>9;month($A$1);"0"&month($A$1))&" năm "&year($A$1)

    Giải thích tý nhé: Nếu ngày hay tháng mà chỉ có 1 số thì thêm số không ("0") vào đằng trước nhìn cho nó đẹp, (đẹp như WhoamI nhỉ : P )

    P/S : OverAC cho bọn anh ký gửi 2 bài này vào đây tý nhé, bởi anh thấy nó bắt đầu đi chệch khỏi đường băng của em mà, hihih ...
     
    Last edited: 26 Tháng ba 2005
    #4
  5. Bình_OverAC

    Bình_OverAC Over Abnormal / Crazy

    Bài viết:
    845
    Đã được thích:
    7
    Nơi ở:
    Nha Trang
    Anh Maica ơi chúng ta có cách khác đẹp hơn đấy:

    ="Ngày " & text(day(A1),"00") & " Tháng " & text(month(A1),"00") & " Năm " & year(A1)

    Nó đẹp hơn vì nó gọn hơn cái công thức của anh. Em sẽ có môt bài về các hàm xử lý chuổi của Excel. Nhưng ở đây giới thiệu sơ về hàm text vì nó liên quan đến cái number format của em như sau:

    Cú pháp: = text(value,format_text)

    Đây là hàm cho biến một số thành một chuổi theo các dang thể hiện khác nhau.
    Value: là giá trị hoặc ô có giá trị cần được thể hiện
    Format_text là dạng thể hiện của giá trị đó ra ngoài.

    theo hàm trên của em thì dạng thể hiện là "00" (phải đặt trong ngoặc kép) có nghĩa là nều nhỏ hơn 10 thì có thêm số 0 ở trước. Và để hiểu thêm vì ngoài cái "00" còn cái gì không xin mời chúng ta lại xem lại phần bảng mã định dạng mà em đã trình bày ở trên.
     
    #5
  6. WhoamI

    WhoamI Thành viên thân thiết

    Bài viết:
    534
    Đã được thích:
    0
    Nơi ở:
    Cố lên, cứ đi rồi sẽ tới!
    Bọn anh ở đâu đấy ông anh!? hi`!
    Thực ra theo mình để tên topic này là Number format có vẻ như không thích hợp lắm hoặc mình không hiểu ý của OverAC. Format trong Excel có lẽ hợp hơn?
     
    #6
  7. Bình_OverAC

    Bình_OverAC Over Abnormal / Crazy

    Bài viết:
    845
    Đã được thích:
    7
    Nơi ở:
    Nha Trang
    Không! đây chỉ là định dạng của số thôi. Nên mới là "Number format"
    Việc các bác cho nó chệnh vậy mà lại hay. Vì OverAC định giới thiệu về hàm text có liên quan đến đây như thế nào! Nhưng lười quá nên chưa chịu làm hìhì.
    Ở trên là giới thiệu về liên hệ giữa hàm text và number format ở mức độ giới thiệu thôi. Chứ chưa có nhiều đâu. và cách trên chỉ là để phản biện tương xứng lại với anh Maica thôi. Đây còn có hàm xử lý chuổi ngày gọn hơn nữa:

    ="Ngày "&TEXT(A1,"dd")&" Tháng "&TEXT(A1,"mm")&" năm "&YEAR(A1)
     
    Last edited: 26 Tháng ba 2005
    #7
  8. ForestC

    ForestC Thành viên sơ cấp

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    E'rywhere
    Hic hic, đang định tinh vi với em Hằng tý mà chú ... hì, nhưng mà cũng hay, học thêm được một cái mà cái này của chú cũng hay thật đấy, tý về anh test luôn ..
     
    Last edited: 1 Tháng tư 2005
    #8

Chia sẻ trang này