Excel Căn bản: Biểu thức Định dạng (Số, Thời gian, ...)

HeSanbi · 2/4/20

(*Chuyển sang Chủ đề Tối để dễ dàng đọc hơn)

Bài viết này sẽ hướng dẫn các bạn về căn bản định dạng Số, thời gian, ... hoặc một chuỗi có cấu trúc để định dạng trong Excel.

Đa số các bạn sử dụng Excel thường hay thắc mắc:
1000000 định dạng thành 1,000,000 như thế nào?
20/10/2020 định dạng thành October, 2020 như thế nào?
Muôn kiểu định dạng trong Excel như thế nào?

Các bạn thường hỏi những câu như vậy bởi vì các bạn chưa nắm được biểu thức chính để tạo nên các định dạng.
Chính vì đó, bài viết này sẽ giúp các bạn vượt qua "bài học định dạng trong Excel" một cách dễ dàng.

Hình ảnh Form định dạng:
-----------------------------------

-----------------------------------

Biểu thức định dạng trong Excel được định nghĩa như sau:

*****************************************

Định dạng số

[Trống]	Hiển thị số mà không có bất kỳ định dạng. (Bạn thường không muốn sử dụng tùy chọn này.)
0	Giữ chỗ cho một chữ số. Nếu không có chữ số, hiển thị số không. Nếu số có ít chữ số hơn bạn sử dụng số không, VBA hiển thị hàng đầu hoặc dấu số không cho phù hợp.
#	Giữ chỗ cho một chữ số. Nếu không có chữ số, không hiển thị gì cả.
.	Giữ chỗ cho một số thập phân. Cho biết nơi dấu tách thập phân nên rơi. Dấu phân cách thập phân thay đổi theo miền địa phương (ví dụ: một dấu thập phân ở Hoa Kỳ, dấu phẩy ở Đức).
%	Giữ chỗ cho một cho một phần trăm ký tự.
,	Dấu phân cách hàng nghìn (tùy thuộc vào miền địa phương, dấu phẩy hoặc một giai đoạn).
:	Dấu phân cách thời gian (thường là dấu hai chấm, nhưng một lần nữa phụ thuộc vào địa phương).
/	Dấu phân cách ngày. (Một lần nữa, những gì bạn sẽ thấy phụ thuộc vào miền địa phương.)
E- E+ e- e+	Định dạng khoa học: E- hoặc e- đặt dấu trừ bên cạnh số mũ âm. E + hoặc e + đặt một điểm trừ ký bên cạnh số mũ âm và đặt dấu cộng ký bên cạnh số mũ dương.
- + $ ( )	Hiển thị một ký tự bằng chữ.
\[character]	Hiển thị ký tự chữ.
"[Chuỗi]"	Hiển thị ký tự bằng chữ. Sử dụng Chr (34) ( mã ký tự cho dấu ngoặc kép) để cung cấp các dấu ngoặc kép.

Các ký tự để tạo định dạng ngày và giờ:

:	Dấu phân cách thời gian (thường là dấu hai chấm, nhưng điều này phụ thuộc vào trên miền địa phương).
/	Dấu phân cách ngày (cũng phụ thuộc miền địa phương).
C	Hiển thị ngày (nếu có ngày hoặc số nguyên giá trị) trong định dạng ngày ngắn của hệ thống và thời gian (nếu có ngày hoặc giá trị phân số) trong định dạng thời gian mặc định của hệ thống.
D	Hiển thị ngày (1 đến 31) mà không có số 0 đứng đầu cho các số có một chữ số.
Dd	Hiển thị ngày có số 0 đứng đầu cho một chữ số số (01 đến 31).
Ddd	Hiển thị ngày dưới dạng viết tắt ba chữ cái (Sun, Thứ Hai, Thứ Ba, Thứ Tư, Thu, Thứ Sáu, Thứ Bảy) không có thời gian.
Dddd	Hiển thị tên đầy đủ trong ngày.
Ddddd	Hiển thị ngày hoàn chỉnh (ngày, tháng và năm) trong định dạng ngày ngắn của hệ thống.
Dddddd	Hiển thị ngày hoàn chỉnh (ngày, tháng và năm) trong định dạng ngày dài của hệ thống.
aaaa	Hiển thị tên đầy đủ, địa phương trong ngày.
w	Hiển thị số nguyên từ 1 (Chủ nhật) đến 7 (Thứ hai) chứa ngày trong tuần.
ww	Hiển thị số nguyên từ 1 đến 54 cho số các tuần trong năm. Số tuần là 54 chứ hơn 52 vì hầu hết các năm bắt đầu và kết thúc với một phần tuần thay vì có 52 tuần bắt đầu để kết thúc.
m	Hiển thị số nguyên từ 1 đến 12 cho số tháng không có số 0 đứng đầu trong các tháng có một chữ số. Khi được sử dụng sau h, trả về phút thay vì tháng.
mm	Hiển thị số từ 01 đến 12 cho hai chữ số số của tháng. Khi được sử dụng sau h, trả về phút thay vì tháng
mmm	Hiển thị tháng dưới dạng viết tắt ba chữ cái (trừ tháng 5) không có thời gian.
mmmm	Hiển thị tên đầy đủ của tháng.
oooo	Hiển thị tên địa phương đầy đủ của tháng.
q	Hiển thị một số từ 1 đến 4 cho một phần tư năm.
y	Hiển thị số nguyên từ 1 đến 366 cho ngày năm.
yy	Hiển thị một số từ 00 đến 99 cho hai chữ số năm.
yyyy	Hiển thị số từ 0100 đến 9999 cho năm bốn chữ số.
h	Hiển thị số từ 0 đến 23 trong giờ.
Hh	Hiển thị một số từ 00 đến 23 cho hai chữ số giờ
N	Hiển thị số từ 0 đến 60 trong một phút.
Nn	Hiển thị số từ 00 đến 60 cho phút hai chữ số.
S	Hiển thị một số từ 0 đến 60 cho số thứ hai.
Ss	Hiển thị số từ 00 đến 60 cho hai chữ số thứ hai.
ttttt	Hiển thị toàn thời gian (giờ, phút và giây) trong định dạng thời gian mặc định của hệ thống.
AM/PM	Sử dụng đồng hồ 12 giờ và hiển thị AM hoặc PM dưới dạng thích hợp.
am/pm	Sử dụng đồng hồ 12 giờ và hiển thị sáng hoặc chiều như thích hợp.
A/P	Sử dụng đồng hồ 12 giờ và hiển thị A hoặc P là thích hợp.
a/p	Sử dụng đồng hồ 12 giờ và hiển thị a hoặc p là thích hợp.
AMPM	Sử dụng đồng hồ 12 giờ và hiển thị AM hoặc Chuỗi PM được định nghĩa cho hệ thống.

TẠO MỘT ĐỊNH DẠNG CHUNG

@	Giữ chỗ cho một nhân vật. Hiển thị một ký tự nếu có một và một không gian nếu không có.
&	Giữ chỗ cho một nhân vật. Hiển thị một ký tự nếu có một và không có gì nếu không có.
<	Hiển thị chuỗi bằng chữ thường.
>	Hiển thị chuỗi bằng chữ hoa.
!	Làm cho VBA điền vào chỗ dành sẵn từ trái sang phải thay vì từ phải sang trái (mặc định phương hướng).

Ví dụ: Hàm Text trong Excel:

Công thức	Miêu tả
= TEXT (1234.567, "$ #, ## 0,00" )	Tiền tệ có dấu phân cách hàng nghìn và 2 số thập phân, như $ 1,234,57. Lưu ý rằng Excel làm tròn giá trị đến 2 chữ số thập phân.
= TEXT (NOW (), "MM / DD / YY" )	Ngày hôm nay ở định dạng MM / DD / YY, như 14/03/12
= TEXT (NOW (), "DDDĐ" )	Hôm nay là ngày trong tuần, như thứ Hai
= TEXT (NOW(), "H: MM AM / PM" )	Thời gian hiện tại, như 1:29 PM
= TEXT (0,285, "0,0%" )	Tỷ lệ, như 28,5%
= TEXT (4.34, "# ?/?" )	Phân số, như 4 1/3
= TRIM (TEXT (0,34, "#? /?" ))	Phân số, như 1/3. Lưu ý điều này sử dụng hàm TRIM để xóa khoảng trắng hàng đầu có giá trị thập phân.
= TEXT (12200000, "0,00E + 00" )	Ký hiệu khoa học, như 1.22E + 07
= TEXT (1234567898, "[<= 9999999] ### - ####; (###) ### - ####" )	Đặc biệt (Số điện thoại), như (123) 456-7898
= TEXT (1234, "0000000" )	Thêm các số 0 đứng đầu (0), như 0001234
= TEXT (123456, "## 0 ° 00 '00' '" )	Tùy chỉnh - Vĩ độ / Kinh độ

-------------------------------------

Chúc bạn thành công!

dazkangel · 2/4/20

Nhân bài này hỏi có ai biết cách làm ra như sau, không thu sát lại được sẽ thành dấu ####

HieuCD · 3/4/20

dazkangel đã viết:
Nhân bài này hỏi có ai biết cách làm ra như sau, không thu sát lại được sẽ thành dấu ####

Chạy code

Mã:

Sub ABC()
  Range("A1").NumberFormat = "dd/mm/yyyy" & Chr(10) & "hh:mm;@"
End Sub

Sẽ có định dạng mới trong custom, lần sau lấy theo định dạng nầy

huonglien1901 · 3/4/20

dazkangel đã viết:
Nhân bài này hỏi có ai biết cách làm ra như sau, không thu sát lại được sẽ thành dấu ####

Wrap text tương ứng char (10) thì phải. Em đoán vậy.

dazkangel · 3/4/20

HieuCD đã viết:
Chạy code

Mã:

Sub ABC() Range("A1").NumberFormat = "dd/mm/yyyy" & Chr(10) & "hh:mm;@" End Sub

Sẽ có định dạng mới trong custom, lần sau lấy theo định dạng nầy

Có 1 cách cho xuống dòng trong number format nhưng chiều ngang vẫn phải đủ, thiếu là nó thành dấu ### nên lên đây xem có cách nào không anh ạ.

dazkangel · 3/4/20

huonglien1901 đã viết:
Wrap text tương ứng char (10) thì phải. Em đoán vậy.

Phải sử dụng Wrap text, gửi bạn cái file cho dễ xem vậy:

gttrongvn · 3/4/20

Anh HeSanBi cho mình hỏi có format nào để áp dụng chung cho các trường hợp này không?
TH1: 123456789 kết quả trả về 123,456,789
TH2: 123456789.10 kết quả trả về 123,456,789.1
TH3: 123456789.123 kết quả trả về 123,456,789.12
TH4: 123456789.1256 kết quả trả về 123,456,789.13
Trước mình tìm hiểu để format cho giá trị số trong listbox, nhưng không biết format nào để thỏa mản được kết quả trả về như vậy, hay là mình phải dùng hàm tự tạo để định dạng lại nhỉ?

HeSanbi · 3/4/20

gttrongvn đã viết:
Anh HeSanBi cho mình hỏi có format nào để áp dụng chung cho các trường hợp này không?
TH1: 123456789 kết quả trả về 123,456,789
TH2: 123456789.10 kết quả trả về 123,456,789.1
TH3: 123456789.123 kết quả trả về 123,456,789.12
TH4: 123456789.1256 kết quả trả về 123,456,789.13
Trước mình tìm hiểu để format cho giá trị số trong listbox, nhưng không biết format nào để thỏa mản được kết quả trả về như vậy, hay là mình phải dùng hàm tự tạo để định dạng lại nhỉ?

-----------------------

Sao bạn không đọc hướng dẫn ở trên:

Format với Custom Format Excel: #,##0.#######
Format với Hàm Text Excel: TEXT (123456789.10, "#,##0.#######")
Format với VBA: Format(123456789.10, "#,##0.#######")

gttrongvn · 3/4/20

HeSanbi đã viết:
-----------------------

Sao bạn không đọc hướng dẫn ở trên:

Format với Custom Format Excel: #,##0.#######
Format với Hàm Text Excel: TEXT (123456789.10, "#,##0.#######")
Format với VBA: Format(123456789.10, "#,##0.#######")

Mình đã thử rồi anh, trong listbox nếu 123 thì nó vẫn hiện 123.
Nó vẫn hiển thị dấu "." dù đằng sau nó không có số thập phân.

HeSanbi · 3/4/20

gttrongvn đã viết:
Mình đã thử rồi anh, trong listbox nếu 123 thì nó vẫn hiện 123.
Nó vẫn hiển thị dấu "." dù đằng sau nó không có số thập phân.

Ý của bạn là muốn giữ lại hai số cuối nếu là số 0 thì không lấy, nếu vậy thì để hai dấu # đằng sau dấu phân cách thập phân.
#,##0.##

gttrongvn · 3/4/20

HeSanbi đã viết:
Ý của bạn là muốn giữ lại hai số cuối nếu là số 0 thì không lấy, nếu vậy thì để hai dấu # đằng sau dấu phân cách thập phân.
#,##0.##

Mình đã thử rồi anh, kết quả nó vẫn trả về như trong hình.

HieuCD · 3/4/20

gttrongvn đã viết:
Anh HeSanBi cho mình hỏi có format nào để áp dụng chung cho các trường hợp này không?
TH1: 123456789 kết quả trả về 123,456,789
TH2: 123456789.10 kết quả trả về 123,456,789.1
TH3: 123456789.123 kết quả trả về 123,456,789.12
TH4: 123456789.1256 kết quả trả về 123,456,789.13
Trước mình tìm hiểu để format cho giá trị số trong listbox, nhưng không biết format nào để thỏa mản được kết quả trả về như vậy, hay là mình phải dùng hàm tự tạo để định dạng lại nhỉ?

Dùng hàm tự tạo với 1 If là được

gttrongvn · 3/4/20

HieuCD đã viết:
Dùng hàm tự tạo với 1 If là được

Mình cũng nghĩ vậy, hiện tại mình đang xài theo kiểu này =IIf((Round(SoHienThi, 0) / Round(SoHienThi, 2)) <> 1, Format(SoHienThi, "#,##0.##"), Format(SoHienThi, "#,##0")). Bạn có cách viết nào khác nào tốt hơn không?

HieuCD · 3/4/20

gttrongvn đã viết:
Mình cũng nghĩ vậy, hiện tại mình đang xài theo kiểu này =IIf((Round(SoHienThi, 0) / Round(SoHienThi, 2)) <> 1, Format(SoHienThi, "#,##0.##"), Format(SoHienThi, "#,##0")). Bạn có cách viết nào khác nào tốt hơn không?

Chỉnh lại tí tránh lỗi chia 0
=IIf(Round(SoHienThi, 0) <> Round(SoHienThi, 2), Format(SoHienThi, "#,##0.##"), Format(SoHienThi, "#,##0"))
Hoặc
=IIf(int(SoHienThi) <> Round(SoHienThi, 2), Format(SoHienThi, "#,##0.##"), Format(SoHienThi, "#,##0"))

dazkangel · 3/4/20

HieuCD đã viết:
Chỉnh lại tí tránh lỗi chia 0
=IIf(Round(SoHienThi, 0) <> Round(SoHienThi, 2), Format(SoHienThi, "#,##0.##"), Format(SoHienThi, "#,##0"))
Hoặc
=IIf(int(SoHienThi) <> Round(SoHienThi, 2), Format(SoHienThi, "#,##0.##"), Format(SoHienThi, "#,##0"))

Sao không dùng Mod anh

HieuCD · 3/4/20

dazkangel đã viết:
Sao không dùng Mod anh

Hàm Mod trong VBA cho kết quả số nguyên khác với hàm Excel có thể trả về số lẽ thập phân
Dùng dư hàm Round
=IIf(int(SoHienThi) <> SoHienThi, Format(SoHienThi, "#,##0.##"), Format(SoHienThi, "#,##0"))

HieuCD · 3/4/20

VetBikini đã viết:
Tại sao không dùng hàm Len và Text kết hợp mà phải Mod

Dùng như thế nào bạn

HieuCD · 3/4/20

VetBikini đã viết:
cái đó là tôi đang hỏi bạn. bạn đi hỏi ngược tôi. Dùng kỷ thuật Phân tách chuổi kết hợp mãng

Tách chuỗi rồi hợp lại là hàng xịn rồi, bạn đi vài đường để anh em chiêm ngưỡng

HieuCD · 3/4/20

VetBikini đã viết:

trước tôi có làm 1 dự án cho bên quân đội. Tôi năm nay gần 70 tuổi lúc nhớ lúc không. trước tôi viết bằng JAva góp vui tí

Mã:

/
import java.util.regex.Pattern;
import java.util.regex.Matcher;

class SplitExample
{
    private static Pattern twopart = Pattern.compile("(\\d+)-(\\d+)");

    public static void checkString(String s)
    {
        Matcher m = twopart.matcher(s);
        if (m.matches()) {
            System.out.println(s + " matches; first part is " + m.group(1) +
                               ", second part is " + m.group(2) + ".");
        } else {
            System.out.println(s + " does not match.");
        }
    }

    public static void main(String[] args) {
        checkString("123-4567");
        checkString("foo-bar");
        checkString("123-");
        checkString("-4567");
        checkString("123-4567-890");
    }
}

/CODE]

Quá ngưỡng mộ, đã làm thì làm cho trót, làm sao đưa vào ListBox theo yêu cầu của chủ thớt

gttrongvn · 3/4/20

VetBikini đã viết:

trước tôi có làm 1 dự án cho bên quân đội. Tôi năm nay gần 70 tuổi lúc nhớ lúc không. trước tôi viết bằng JAva góp vui tí

Mã:

/
import java.util.regex.Pattern;
import java.util.regex.Matcher;

class SplitExample
{
    private static Pattern twopart = Pattern.compile("(\\d+)-(\\d+)");

    public static void checkString(String s)
    {
        Matcher m = twopart.matcher(s);
        if (m.matches()) {
            System.out.println(s + " matches; first part is " + m.group(1) +
                               ", second part is " + m.group(2) + ".");
        } else {
            System.out.println(s + " does not match.");
        }
    }

    public static void main(String[] args) {
        checkString("123-4567");
        checkString("foo-bar");
        checkString("123-");
        checkString("-4567");
        checkString("123-4567-890");
    }
}

/CODE]

https://ask.xiaolee.net/questions/1003846 bài #2, dự án cho quân đội mà để lọt ra ngoài vậy là chết rồi.

Excel Căn bản: Biểu thức Định dạng (Số, Thời gian, ...)

Nam Nhân✨Hiếu Lễ Nghĩa Trí Tín✨

<New Horizons>

File đính kèm

Chuyên gia GPE

GPE là ngôi nhà thứ 2 của tôi!!!

<New Horizons>

<New Horizons>

File đính kèm

Thành viên hoạt động

Nam Nhân✨Hiếu Lễ Nghĩa Trí Tín✨

Thành viên hoạt động

File đính kèm

Nam Nhân✨Hiếu Lễ Nghĩa Trí Tín✨

Thành viên hoạt động

Chuyên gia GPE

Thành viên hoạt động

Chuyên gia GPE

<New Horizons>

Chuyên gia GPE

Chuyên gia GPE

Chuyên gia GPE

Chuyên gia GPE

Thành viên hoạt động

Facebook

Group