Có thể bạn quan tâm: Cách Tạo Và Quản Lý Form Giấy Tomy Thường Dùng Trong Word Và Excel
Giới thiệu
Việc đánh số thứ tự trong Excel là một thao tác thường gặp, đặc biệt khi bạn cần theo dõi, phân loại hoặc báo cáo dữ liệu. Thông thường, người ta sử dụng công thức đơn giản =ROW() hoặc =ROW()-n để tạo chuỗi số liên tục. Tuy nhiên, trong nhiều tình huống thực tế, bạn sẽ cần một chuỗi số không liên tục – tức là có những số bị bỏ qua, lặp lại hoặc thay đổi theo quy luật riêng. Ví dụ: bạn đang làm báo cáo tài chính, muốn đánh số từng khoản mục theo tuần, tháng, năm; hoặc bạn muốn tạo danh sách mã sản phẩm theo quy định của công ty, trong đó số thứ tự không phải là số liên tiếp. Bài viết này sẽ cung cấp cho bạn một loạt phương pháp, công thức, và macro để thực hiện đánh số không liên tục trong Excel một cách nhanh chóng, chính xác và linh hoạt.
Có thể bạn quan tâm: Tạo File Quản Lý Hợp Đồng Mua Bán Bằng Excel: Hướng Dẫn Chi Tiết Và Thực Tiễn
1. Khi nào cần đánh số thứ tự không liên tục?
Trước khi đi vào công thức cụ thể, hãy liệt kê một số tình huống phổ biến:
- Danh sách mã sản phẩm: Mỗi dòng sản phẩm có mã gồm một số chuẩn bị theo quy trình Perso, ví dụ:
PROD-001,PROD-003,PROD-004,PROD-007. Số 2 và 5 bị bỏ qua vì không hợp lệ. - Bảng KPI theo quý: Bạn muốn đánh số theo quý, nhưng một số quý bị thiếu dữ liệu. Ví dụ:
Q1-2026,Q3-2026,Q4-2026. - Dự án theo giai đoạn: Giai đoạn 1, 2, 3, 5, 6, 7 (bỏ qua giai đoạn 4 vì bị hủy).
- Dữ liệu thống kê theo nhóm: Bạn muốn đánh số mỗi nhóm riêng biệt, nhưng không muốn số thứ tự trở thành một chuỗi liên tục trên toàn bảng.
- Báo cáo tài chính theo năm tài chính: Năm tài chính 2026 không phải là năm dương tính, năm 2026 có số âm (-1) do điều kiện đặc biệt.
Những tình huống này yêu cầu một cách đánh số linh hoạt, có thể tùy chỉnh theoongs.
Có thể bạn quan tâm: File Excel Tính Kết Cấu Bê Tông Cốt Thép
2. Cách đánh số thứ tự không liên tục dùng công thức cơ bản
2.1 Sử dụng IF và LOOKUP để bỏ qua số nhất định
Giả sử bạn có một danh sách các số có thể bị bỏ qua: 1, 3, 4, 6, 7. Bạn có thể tạo một bảng tham chiếu và dùng hàm VLOOKUP để lấy giá trị liên quan.
| A | B |
|---|---|
| 1 | 1 |
| 3 | 2 |
| 4 | 3 |
| 6 | 4 |
| 7 | 5 |
Trong ô C2, nhập công thức:
=VLOOKUP_run(A2, $A$2:$B$6, 2, FALSE)
Đây là cách làm cho số thứ tự không liên tục. Khi bạn sao chép công thức xuống, Excel sẽ tự động tra cứu giá trị thứ tự tương ứng.
Lưu ý: Hàm
VLOOKUPyêu cầu cột tìm kiếm phải sắp xếp tăng dần nếu dùngTRUE, nhưng trong trường hợp này chúng ta sử dụngFALSEđể xác định chính xác.
2.2 Sử dụng COUNTIF để đếm số xuất hiện trong một dải
Nếu bạn muốn đánh số thứ tự tùy theo điều kiện “có xuất hiện hay không”, bạn có thể dùng COUNTIF.
Giả sử ô B2 chứa giá trị A, B3 chứa B, B4 chứa C và bạn muốn đánh số thứ tự chỉ cho những ô có giá trị A hoặc C.
=IF(OR(B2="A", B2="C"), COUNTIF($B$2:B2, "A")+COUNTIF($B$2:B2, "C"), "")
Công thức này đếm số lần xuất hiện của A và C cho tới ô hiện tại, qua đó tạo ra một số thứ tự không liên tục: 1, 2, 1, 2, 3, … Khi sao chép xuống, bạn sẽ thấy số thứ tự tăng dần tùy theo điều kiện.
2.3 Dùng MOD và INT để tạo chuỗi số không liên tục
Đối với các chuỗi số theo quy luật, ví dụ bạn muốn đánh số 1, 3, 5, 7… (cứ tăng 2), bạn có thể dùng công thức:
=(ROW(A1)-1)2+1
Khi nhập vào ô A1 và sao chép xuống, sẽ hiển thị 1, 3, 5, 7, 9, .... Nếu muốn bắt đầu từ 0, thay +1 thành +0.
Tương tự, để tạo chuỗi 2, 4, 6, 8, …:
=ROW(A1)2
2.4 Sử dụng SEQUENCE trong Excel 365
Excel 365 cung cấp hàm SEQUENCE, rất tiện lợi để tạo mảng số không liên tục.
Ví dụ, tạo chuỗi 1, 3, 5, 7:
=SEQUENCE(5, 1, 1, 2)
Cú pháp: SEQUENCE(rows, columns, start, step). Khi nhập vào một ô và nhấn Enter, Excel sẽ trả về một mảng 5 dòng, 1 cột, bắt đầu từ 1, tăng 2. Bạn có thể thay đổi start và step tùy ý.
3. Đánh số thứ tự theo nhóm (cứ mỗi nhóm có số riêng)
Trong nhiều báo cáo, bạn muốn đánh số thứ tự theo nhóm, ví dụ: nhóm A có số 1-3, nhóm B có số 1-4,ّت. Để làm điều này, bạn có thể kết hợp IF, COUNTIF và SUMPRODUCT.
Giả sử cột A chứa tên nhóm, cột B chứa dữ liệu:
| A | B |
|---|---|
| A | X |
| A | Y |
| A | Z |
| B | X |
| B | Y |
| B | Z |
| B | W |
Bạn muốn đánh số thứ tự trong cột C, bắt đầu từ 1 cho mỗi nhóm.
Công thức:
=COUNTIF($A$2:A2, A2)
Giải thích: COUNTIF đếm số lần xuất hiện của nhóm hiện tại trong dải từ ô đầu tiên tới ô hiện será. Khi sao chép xuống, số thứ tự sẽ tự độnggraphic: 1,2,3 cho nhóm A và 1,2,3,4 cho nhóm B.
Nếu bạn muốn thêm một ký tự hoặc chuỗi tiền tố, ví dụ A Sunset 01, bạn có thể dùng:
=A2 & " " & TEXT(COUNTIF($A$2:A2, A2), "00")
Số thứ tự sẽ là A 01, A 02, B 01, …
4. Cách đánh số thứ tự tùy theo điều kiện phức tạp
4.1 Đánh số theo ngày, nhưng bỏ qua ngày cuối tuần
Bạn có một danh sách ngày trong cột A, muốn đánh số thứ tự chỉ cho các ngày làm việc (bỏ qua ngày thứ 6 và thứ 7).けば:
=IF(OR(WEEKDAY(A2)=6, WEEKDAY(A2)=7), "", COUNTIF($A$2:A2, "<=" & A2) - COUNTIF($A$2:A2, "<_REGISTERED_DATE>"))
Tuy nhiên, công thức này hơi phức tạp. Dùng hàm NETWORKDAYS:
=NETWORKDAYS($A$2, A2) - NETWORKDAYS($A$2, A2-1)
Công thức này trả về 1 nếu ngày hiện tại là ngày làm việc, 0 nếu là cuối tuần. Bạn có thể kết hợp với SUMPRODUCT để tính tổng số ngày làm việc tới ngày hiện tại.
4.2 Đánh số theo điều kiện “đã thực hiện” hoặc “chưa thực hiện”
Bạn có cột trạng thái (Có/Không). Muốn đánh số thứ tự chỉ cho những dòng “Có”.
=IF(B2="Có", COUNTIF($B$2:B2, "Có"), "")
Nếu trạng thái là True/False, thay B2="Có" thành B2=TRUE.
4.3 Đánh số theo điều kiện “có dữ liệu” trong cột khác
Nếu bạn muốn đánh số thứ tự cho những dòng có dữ liệu trong cột C:
=IF(C2<>"", COUNTIF($C$2:C2, "<>"""), "")
5. Sử dụng VBA để tạo số thứ tự không liên tục
Trong một số trường hợp, bạn cần một công cụ mạnh mẽ hơn để tự động hoá quy trình đánh số. Dưới đây là một macro đơn giản:
Sub AutoNumberNonContinuous() Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim count As Long Set ws = ActiveSheet Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) count = 0 For Each cell In rng If cell.Value <> "" Then count = count + 1 cell.Offset(0, 1).Value = count End If Next cell
End Sub
Macro này sẽ:
- Lấy dải ô cột A từ dòng 2 đến dòng cuối cùng có dữ liệu.
- Duyệt từng ô, nếu ô không rỗng thì tăng biến
count. - Ghi giá trị
countvào cột B tương ứng.
Bạn có thể tùy chỉnh macro để:
- Bỏ qua các dòng thỏa mãn điều kiện nhất định (sử dụng
If). - Đánh số theo nhóm (sử dụng biến
currentGroup). - Thêm tiền tố/định dạng.
6. Thực hành: Đánh số thứ tự trong Excel mà không cần kéo công thức
6.1 Dùng tính năng Fill Series với tùy chỉnh bước
Có thể bạn quan tâm: Cách Chuyển File Pdf Sang Excel Không Cần Phần Mềm
Excel cung cấp tính năng Fill Series (Điền chuỗi) rất mạnh mẽ. Bạn có thể:
- Nhập số đầu tiên vào ô A1 (ví dụ
1). - Nhập số thứ hai vào ô A2 (ví dụ
3). - Chọn hai ô này.
- Kéo thả chuột xuống hoặc chọn
Home > Fill > Series. - Trong hộp thoại, chọn
Series in Columns,Type: Linear,Step value: +2,Stop value: ...(hoặcBy). - Nhấn OK.
Kết quả: 1, 3, 5, 7, ....
6.2 Dùng tính năng AutoFill với công thức tính toán
Bạn cũng có thể nhập công thức vào ô A1 và kéo thả:
=ROW(A1)2-1
Như đã trình bày ở mục 2.3, khi kéo thả, Excel sẽ tự động điều chỉnh tham chiếu ROW(A1) thành ROW(A2), ROW(A3),… và tạo chuỗi số không liên tục.
6.3 Dùng tính năng Custom Lists để đánh số theo danh sách tùy chỉnh
Nếu bạn có một danh sách số đã được xác định sẵn (ví dụ 1, 3, 4, 6, 7), bạn có thể tạo một danh sách tùy chỉnh:
- Vào
File > Options > Advanced > Edit Custom Lists. - Chọn
New List. - Nhập danh sách số, mỗi số cách nhau bằng dấu phẩy hoặc xuống dòng.
- Nhấn OK.
Sau đó, khi bạn nhập 1 vào ô A1 và kéo thả, Excel sẽ lặp lại chuỗi số.side. Bạn có thể lặp lại chuỗi tự động như một chuỗi liên tục.
7. Thực hành: Đánh số thứ tự theo chuỗi có quy luật phức tạp
Giả sử bạn có quy luật:
- Nếu giá trị trong cột B là “Hoa”, đánh số 1, 3, 5, 7…
- Nếu giá trị trong cột B là “Rau”, đánh số 2, 4, 6, 8…
- Nếu giá trị trong cột B là “Thịt”, đánh số 10, 20, 30…
Bạn có thể tạo công thức như sau:
=IF(B2="Hoa", (ROW(A1)-1)2+1, IF(B2="Rau", (ROW(A1)-1)2+2, IF(B2="Thịt", (ROW(A1)-1)10+10, "")))
Công thức này kiểm tra giá trị trong cột B, sau đó tính số thứ tự dựa trên bước (step) và bắt đầu (start) theo quy tắc đã xác định.
8. Kiểm tra lỗi và xử lý dữ liệu sai
8.1 Kiểm tra dữ liệu trùng lặp
Để tránh lặp lại số thứ tự, bạn có thể kiểm tra trước khi đánh số:
=IF(COUNTIF($C$2:C2, C2)=1, C2, "")
Nếu số thứ tự đã tồn tại, công thức sẽ trả về chuỗi rỗng.
8.2 Xử lý ô trống
Trong nhiều trường hợp, bạn muốn bỏ qua ô trống khi đánh số. Bạn có thể dùng:
=IF(A2="", "", COUNTIF($A$2:A2, "<>"""))
Điều này giúp giữ trật tự khi có ô trống.
8.3 Xử lý dữ liệu ngoại lệ (độ dài dòng quá lớn)
Nếuvement, bạn có thể dùng hàm ARRAYFORMULA (đối với Google Sheets) hoặc FILTER (đối với Excel 365) để lọc dữ liệu hợp lệ trước khi đánh số.
9. Thực hành trong thực tế: Đánh số thứ tự trong báo cáo tài chính
9.1 Bảng doanh thu theo tháng
Bạn có một bảng doanh thu theo tháng, nhưng trong một số tháng doanh thu không có (vì bị gián đoạn). Bạn muốn đánh số thứ tự chỉ cho những tháng có doanh thu.
| Tháng | Doanh thu |
|---|---|
| Jan | 10,000 |
| Feb | 0 |
| Mar | 15,000 |
| Apr | 0 |
| May | 12,000 |
Công thức trong cột C:
=IF(B2>0, COUNTIF($B$2:B2, ">0"), "")
Kết quả: 1, “”, 2, “”, 3.
9.2 Đánh số các khoản mục trong bảng ngân sách
Bạn muốn đánh số các khoản mục theo mức độ quan trọngمنٹ. Mức độ quan trọng được đánh 1-3, và bạn muốn đánh số thứ tự cho mỗi mức độ riêng.
| Khoản mục | Quan trọng | Số thứ tự |
|---|---|---|
| A | 1 | 1 |
| B | 1 | 2 |
| C | 2 | 1 |
| D | 3 | 1 |
| E | 2 | 2 |
Công thức:
=COUNTIF($B$2:B2, B2)
10. Mẹo và thủ thuật nâng cao
10.1 Sử dụng OFFSET để tạo chuỗi tùy chỉnh
Nếu bạn muốn đánh số bắtCharsets từ một số bất kỳ, có thể dùng OFFSET:
=OFFSET($A$1, ROW(A1)-1, 0)
10.2 Sử dụng FILTER và SEQUENCE trong Excel 365
Bạn có thể kết hợp FILTER để lọc dữ liệu và SEQUENCE để tạo số thứ tự:
=SEQUENCE(COUNT(FILTER(A1:A100, A1:A100<>"")), 1, 1, 1 Conversation)
Điều này sẽ tạo một chuỗi số liên tục từ 1 tới số lượng dòng dữ liệu hợp lệ.
10.3 D regolazioni per la formattazione condizionale
Bạn có thể áp dụng Conditional Formatting để đánh dấu các số thứ tự có giá trị đặc biệt, ví dụ: đánh số 5, 10, 15.
- Chọn cột số thứ tự.
Home > Conditional Formatting > New Rule > Use a formula.Component.- Công thức: entfernen
=MOD(A2,5)=0. - Đặt màu nền.
10.4 Tự động cập nhật khi dữ liệu thay đổi
Nếu bạn muốn số thứ tự tự động cập nhật khi thêm hoặc xóa dòng, hãy sử dụng công thức COUNTA và INDEX:
=INDEX($A$2:$A$100, MATCH(ROW(A1), IF($B$2:$B$100<>"", ROW($B$2:$B$100)-ROW($B$2)+1), 0))
11. Lưu ý khi làm việc với số thứ tự không liên tục
- Không lặp lại số: Nếu bạn cần duy trì tính duy nhất, hãy kiểm tra trước khi đánh số.
- Số thứ tự không đồng nhất: Nếu bạn cần số thứ tự liên tục trong mỗi nhóm, hãy đặt công thức đúng dải nhóm.
- Tính năng tự động: Sử dụng
Table(Insert > Table) để tự động áp dụng công thức cho dòng mới. - Ghi chú: Luôn ghi chú trong bảng dữ liệu vì số thứ tự không liên tục có thể gây nhầm lẫn.
5ાવરણ Tối ưu hóa công thức: Khi số lượng dữ liệu lớn, tránh công thức phức tạp, sử dụngINDEX/MATCHthay vìCOUNTIFđể giảm thời gian tính toán.
12. Kết luận
Đánh số thứ tự không liên tục trong Excel không chỉ là một kỹ thuật đơn giản mà còn là một công cụ mạnh mẽ giúp bạn quản lý dữ liệu một cách chuyên nghiệp. Dù bạn là người mới bắt đầu hay là quản trị viên dữ liệu, những công thức và macro trên sẽ giúp bạn thực hiện công việc một cách nhanh chóng, chính xác và linh hoạt.
Các phương pháp đã được giới thiệu trong bài viết bao gồm:
- Công thức cơ bản (
IF,COUNTIF,VLOOKUP,SEQUENCE). - Tính năng điền chuỗi và
Table. - Macro VBA để tự động hoá quy trình.
- Kiểm tra lỗi và xử lý dữ liệu ngoại lệ.
- Thực hành trong các tình huống thực tế: báo cáo tài chính, ngân sách, nhóm dữ liệu, và nhiều hơn nữa.
Hãy thử áp dụng những công thức và thủ thuật này vào dự án của bạn, và bạn sẽ thấy Excel trở thành một công cụ mạnh mẽ hơn rất nhiều trong việc quản lý và phân tích dữ liệu. Happy Excel!
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ẻ.