Trong công việc quản lý dữ liệu, kế toán, lập báo cáo hay đơn giản là sắp xếp danh sách, việc đánh số thứ tự một cách tự động trong Excel luôn là nhu cầu thiết yếu. Bài viết này sẽ hướng dẫn bạn cách thiết lập số thứ tự tự động với nhiều phương pháp khác nhau – từ công thức cơ bản, hàm tự động, đến cách sử dụng VBA và Power Query. Bạn cũng sẽ tìm thấy những mẹo nâng cao để làm cho quy trình làm việc của mình trở nên nhanh chóng, chính xác và linh hoạt hơn.
Có thể bạn quan tâm: Đánh Số Trang Từ Trang 2 Trong Excel
1. Tại sao đặt số thứ tự tự động lại quan trọng?
- Tổ chức dữ liệu: Số thứ tự giúp người đọc nhanh chóng nhận dạng vị trí của một mục trong danh sách, đặc biệt khi dữ liệu dài.
- Tự động cập nhật: Khi thêm, xóa hoặc sắp xếp dữ liệu, số thứ tự sẽ tự động điều chỉnh mà không cần thao mää thủ công.
- Tăng tính chuyên nghiệp: Các báo cáo, bảng tính, tài liệu kế toán thường yêu cầu số thứ tự rõ ràng, nhất quán.
- Tiết kiệm thời gian: Tránh lỗi khi đánh số tay và giảm thiểu thao tác.
Có thể bạn quan tâm: Cách Đánh Số Trang Trong Excel 2026 Theo Ý Muốn
2. Phương pháp 1: Sử dụng công thức tự động trong ô
2.1 Công thức đơn giản với hàm ROW()
Công thức =ROW()-ROW($A$1)+1 dùng để tạo số thứ tự bắt đầu từ 1 trong cột A. Giải thích:
ROW()trả về số dòng hiện tại.ROW($A$1)là số dòng của ô tham chiếu cố định.- Trừ đi và cộng 1 để đưa số thứ tự bắt đầu từ 1.
Ví dụ:
| A | B |
|---|---|
| 1 | |
| 2 | |
| 3 |
- Vào ô A1:
=ROW()-ROW($A$1)+1 - Kéo xuống dưới.
2.2 Công thức với hàm COUNTA()
Nếu bạn muốn số thứ tự bắt đầu từ một giá trị khác (ví dụ 100) hoặc muốn đánh số liên tục qua nhiều cột dữ liệu, dùng COUNTA():
=COUNTA($B$1:B1)+99
COUNTA($B$1:B1)đếm số ô có dữ liệu từ B1 đến ô hiện tại.- Cộng 99 để bắt đầu từ 100.
2.3 Công thức hợp nhất với OFFSET()
Khi bạn muốn số thứ tự liên tục sau khi lọc dữ liệu, OFFSET() có thể giúp:
=ROW()-ROW($A$1)+1+COUNTIF($A$1:A1,"<="&A1)-1
Bằng cách này, khi lọc dữ liệu, số thứ tự vẫn giữ nguyên.
Có thể bạn quan tâm: Cách Đánh Số Thứ Tự Trong Excel Khi Ẩn Dòng
3. Phương pháp 2: Sử dụng tính năng AutoFill và Fill Series
3.1 AutoFill nhanh chóng
- Nhập số 1 vào ô đầu tiên (ví dụ A2).
- Nhấn vào góc dưới phải của ô (điểm nhỏ).
- Kéo xuống tới ô cần phủ.
Excel sẽ tự động tăng dần. Nếu muốn bắt đầu từ số 10, nhập 10 rồi kéo, Excel sẽ tăng dần 11, 12, …
3.2 Fill Series cho các chuỗi số tùy chỉnh
- Chọn ô muốn bắt đầu.
- Đi tới
Home>Editing>Fill>Series. - Chọn
Columnsvà thiết lậpStep value(độ tăng) vàStop value(giá trị dừng).
Điều này rất tiện lợi khi cần số thứ tự với bước nhảy lớn hơn 1.
4. Phương pháp 3: Sử dụng hàm SEQUENCE() (Excel 365)
4.1 Giới thiệu hàm SEQUENCE()
Hàm SEQUENCE() tạo mảng số tự động. Cú pháp:
=SEQUENCE(rows, columns, start, step)
rows: số dòng.columns: số cột.start: giá trị bắt đầu.step: bước nhảy.
4.2 Ví dụ thực tế
Giả sử bạn muốn tạo danh sách số thứ tự từ 1 đến 50 trong cột A:
=SEQUENCE(50,1,1,1)
Hệ televised: SEQUENCE(50,1,1,1) sẽ tạo 50 dòngendent.
Bởi vì Excel 365 hỗ trợ mảng động, bạn không cần kéo xuống. Chỉ cần nhập công thức vào ô và nó sẽ tự động lấp đầy.
5. Phương pháp 4: Sử dụng hàm INDEX() và SMALL() cho dữ liệu lọc
Khi dùng AutoFilter, số thứ tự có thể bị gián đoạn. Để giữ số thứ tự liên tục, bạn có thể kết hợp INDEX() và SMALL():
=IF(SUBTOTAL(103,$B$2:$B2)=1,ROW()-ROW($B$1),INDEX($B$2:$B2,SMALL(IF($B$2:$B$100<>"",$B$2:$B$100-ROW($B$2)+1),ROW()-ROW($B$1))))
SUBTOTAL(103,...)trả về 1 nếu dòng hiển thị, 0 nếu ẩn.SMALL()vàIF()tạo mảng số thứ tự liên tục.
Bạn cần nhấn Ctrl+Shift+Enter để nhập công thức mảng (trong các phiên bản Excel không hỗ trợ mảng động).
6. Phương pháp 5: Sử dụng VBA để tạo số thứ tự tự động
6.1 Tạo macro đơn giản
Sub AutoNumber() Dim rng As Range Set rng = Range("A2:A100") ' Định nghĩa vùng cần số thứ tự Dim i As Long For i = 1 To rng.Rows.Count rng.Cells(i, 1).Value = i Next i
End Sub
Hướng dẫn:
- Nhấn
Alt+F11để mở VBA Editor. - Chèn một Module mới (
Insert > Module). - Dán đoạn mã trên.
- Chạy macro (
F5hoặcRun).
Macro sẽ điền số thứ tự từ 1 đến 99 vào cột A.
6.2 Macro tự động khi thay đổi dữ liệu
Bạn có thể gắn macro vào sự kiện Worksheet_Change:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then Call AutoNumber End If
End Sub
Khi thay đổi dữ liệu trong cột B, macro sẽ tự động cập nhật số thứ tự trong cột A.
Có thể bạn quan tâm: Đưa രീത Con Trỏ Về Ô A1 Trong Excel: Hướng Dẫn Chi Tiết Và Các Mẹo Hữu Ích
7. Phương pháp 6: Sử dụng Power Query (Get & Transform)
Power Query cho phép tạo số thứ tự khi nhập dữ liệu:
- Chọn dữ liệu, vào
Data>From Table/Range. - Trong Power Query Editor, chọn
Add Column>Index Column>From 1. - Lưu và tải dữ liệu trở lại Excel.
Power Query tự động tạo cột số thứ tự ngay khi dữ liệu được nhập. Khi dữ liệu thay đổi, bạn chỉ cần click Refresh để cập nhật.
8. Mẹo nâng cao: Số thứ tự theo nhóm
Đôi khi bạn cần số thứ tự được reset khi nhóm thay đổi (ví dụ: danh sách khách hàng theo khu vực).
8.1 Sử dụng công thức COUNTIFS()
Giả sử cột A là Khu vực, cột B là khách hàng, bạn muốn số thứ tự trong cột C:
=COUNTIFS($A$2:A2,$A2)
COUNTIFSđếm số dòng trong phạm vi từ A2 tới ô hiện tại mà có Khu vực giống nhau.- Khi Khu vực đổi, đếm sẽ reset.
8.2 Sử dụng SEQUENCE() cho nhóm
Nếu dữ liệu đã được sắp xếp theo nhóm, bạn có thể tạo số thứ tự với công thức:
=SEQUENCE(COUNTIFS($A$2:$A$100,$A2),1,1,1)
Kết quả sẽ là mảng số thứ tự cho từng nhóm, nhưng cần nhập vào ô đầu tiên và nhấn Ctrl+Shift+Enter (đối với Excel không hỗ trợ mảng động).
9. Mẹo 2: Số thứ tự khi có dữ liệu trống
Khi có ô trống trong dữ liệu, số thứ tự có thể bị gián đoạn. Dùng công thức IF kết hợp với ROW():
=IF(B2<>"",ROW()-ROW($B$1)+1,"")
- Nếu ô B2 không trống, hiển thị số thứ tự.
- Nếu trống, hiển thị trống.
Điều này giúp tránh số thứ tự liên tiếp khi có dòng trống.
10.ակում: Tùy chỉnh hiển thị số thứ tự
Bạn có thể tùy chỉnh cách hiển thị bằng cáchнего:
- Cột thập phân: Định dạng cột thành
Numbervới số thập phân tùy ý. - Text: Dùng công thức
="Item " & ROW()để hiển thị dạng “Item 1”, “Item 2”. - Thêm tiền tố: Ví dụ
="S" & TEXT(ROW(),"000")→ “S001”, “S002”.
11. Kiểm tra và bảo trì
11.1 Kiểm tra tính chính xác
- Dùng công thức
=MAX(A:A)để kiểm tra số lớn nhất. - Kiểm tra
=COUNT(A:A)để đảm bảo số lượng dòng.
11.2 Bảo trì khi thay đổi cột
Nếu bạn thêm cột mới, công thức ROW() vẫn hoạt động. Tuy nhiên, công thức COUNTIFS() cần cập nhật phạm vi dữ liệu.
11.3 Sử dụng tên định danh
Đặt tên cho vùng dữ liệu (định danh) giúp công thức dễ đọc:
- Chọn vùng dữ liệu, vào
Formulas>Define Name. - Đặt tên như
DataRange. - Dùng công thức:
=COUNTIFS(DataRange, $A2).
12. So sánh các phương pháp
| Phương pháp | Ưu điểm | Nhược điểm | Thích hợp |
|---|---|---|---|
Công thức ROW() | Đơn giản, nhanh | Phải điều chỉnh nếu cột dữ liệu thay đổi | Dữ liệu cố định |
мавCOUNTA()` | Xử lý dữ liệu không liên tục | Cần điều chỉnh phạm vi | Dữ liệu dãy không liên tục |
| AutoFill | Thao tác nhanh | Không tự động cập nhật khi lọc | Tự động số dọc |
SEQUENCE() | Động, không cần kéo | Yêu cầu Excel 365/2026 | Dữ liệu lớn, mảng động |
INDEX/SUMALL | Động khi lọc | Công thức phức tạp | Khi lọc dữ liệu |
| VBA | Tự động hoàn toàn | Cần viết mã | Tự động khi sửa đổi |
| Power Query | Tự видео dữ liệu | Cần refresh | Khi nhập dữ liệu từ nguồn |
Nhóm với COUNTIFS() | Reset theo nhóm | Phức tạp | Danh sách theo nhóm |
13. Kết luận
Đặt số thứ tự tự động trong Excel không chỉ là một thao tác đơn giản mà còn là một kỹ năng quan trọng giúp tăng tính chuyên nghiệp, giảm lỗi và nâng cao hiệu quả làm việc. Dựa vào mục đích sử dụng, bạn có thể chọn một trong nhiều phương pháp kể trên – từ công thức cơ bản, tính năng AutoFill, hàm SEQUENCE() trong Excel 365, macro VBA, cho tới Power Query.
Bên cạnh đó, những mẹo nâng cao như số thứ tự theo nhóm, xử lý ô trống, chuyển đổi định dạng hiển thị hay bảo trì dữ liệu sẽ giúp bạn quản lý danh sách một cách linh hoạt và chính xác hơn. Hãy thử áp dụng các phương pháp trong bài viết, tùy chỉnh cho phù hợp với quy trình công việc của bạn, và trải nghiệm sự thay đổi trong năng suất làm việc!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.