Bạn đã bao giờ cảm thấy nhàm chán khi phải tạo các ID duy nhất sử dụng các số tuần tự, một số chữ cái được trích xuất từ các mục và thêm số 0 dẫn đầu không cần thiết? Với Google Sheets, chúng ta có thể tạo ra các ID đọc được, duy nhất và độc đáo.
Trong bài viết này, chúng ta sẽ tìm hiểu cách tạo ID đọc được hoặc mã sản phẩm/mục trong Google Sheets bằng cách sử dụng các công thức.
Ví dụ về việc Tạo ID Đọc Được Duy Nhất trong Google Sheets
Dưới đây là hai ví dụ cơ bản. Bạn có thể thay đổi công thức của tôi theo hướng dẫn được cung cấp ở cuối bài viết để phù hợp với nhu cầu của bạn.
1. Tạo ID Đọc Được cho Các Cuốn Sách Đã Mua
Trước tiên, hãy xem ví dụ về tạo một ID cho cuốn sách “Last Man” được viết bởi Mary Shelly.
Trong hàng số 5, bạn có thể thấy các chi tiết cần thiết của cuốn sách để tạo mã số duy nhất. Theo công thức của tôi, mã số duy nhất đọc được được tạo ra là 000321 (ô D5).
Trong đó, ba số 0 đứng đầu không có ý nghĩa gì. Đó chỉ là để làm đẹp.
Còn con số 321 thì sao và làm sao nó trở thành một mã số đọc được độc nhất?
Nó có nghĩa như sau. Con số 3 đại diện cho mã tác giả, số 2 đại diện cho thể loại và số 1 đại diện cho số lần xuất hiện của cuốn sách cùng tác giả dưới cùng thể loại.
Điều đó có nghĩa là bạn có thể đọc các ID duy nhất này bằng cách sử dụng một bảng khác (bảng tra cứu) được duy trì trong cùng một bảng hoặc bảng khác.
Trong mục đích minh họa, tôi đã duy trì bảng tra cứu trong cùng một bảng. Đây là bảng đó.
Lưu ý: Bạn có thể thêm nhiều tên tác giả và thể loại trong bảng tra cứu. Công thức trong cột D (nghĩa là công thức mảng trong ô D2 mà tôi sẽ cung cấp sau trong bài hướng dẫn này) sẽ xem xét các bản ghi mới được thêm vào trong quá trình tạo mã.
2. Tạo ID Đọc Được cho Các Tài Sản Đã Mua
Trong ví dụ này, để giải thích, tôi sẽ lấy mã số từ ô E5 là 0001121. Hãy xem cách giải mã nó với sự giúp đỡ của bảng tra cứu.
Con số cuối cùng đại diện cho số lần xuất hiện và tôi sẽ đến phần đó sau.
Công thức ID và Cách Điều Chỉnh Nó cho Bảng Tra Cứu Khác
Để tạo ra các ID duy nhất như trên, tôi đã sử dụng nhiều công thức Vlookup và kết hợp chúng. Ngoài ra, tôi đã sử dụng một công thức đếm chạy.
Dưới đây là công thức mà tôi đã sử dụng trong “ví dụ 1” ở trên (để chèn vào ô D2).
Kết hợp nhiều công thức Vlookup để tạo mã nhận dạng duy nhất
=ArrayFormula(if(A2:A="",,"000"&ifna(vlookup(B2:B,G2:H,2,false))&ifna(vlookup(C2:C,I2:J,2,false))&L2:L))Có một công thức khác (một công thức hỗ trợ) trong ô L2. Tôi sẽ đến phần đó sau. Dưới đây là giải thích cho công thức này trong 5 bước.
Bước 1
Như bạn có thể thấy, có hai công thức Vlookup kết hợp trong công thức trên.
Vlookup đầu tiên tìm kiếm tên tác giả (cột B2:B) trong G2:G và trả về mã tương ứng từ cột thứ hai trong phạm vi G2:H.
=ArrayFormula(ifna(vlookup(B2:B,G2:H,2,false)))Bước 2
Tương tự, công thức Vlookup thứ hai tìm kiếm thể loại (cột C2:C) của các cuốn sách trong I2:I và trả về giá trị từ cột thứ hai trong phạm vi I2:J.
=ArrayFormula(ifna(vlookup(C2:C,I2:J,2,false)))Bước 3
Bằng cách sử dụng dấu “&”, tôi đã kết hợp hai công thức Vlookup như sau và kết quả sẽ được đánh dấu trong cột D như ảnh trên.
=ArrayFormula(ifna(vlookup(B2:B,G2:H,2,false)&ifna(vlookup(C2:C,I2:J,2,false))))Bước 4
Trong bước này, chúng ta có thể thêm ba số 0 (hoặc bất kỳ số lượng số 0 nào bạn muốn) vào đầu các mã số duy nhất mà chúng tôi đang tạo ra. Bước này không bắt buộc.
Chỉ cần chèn công thức này sau “=ArrayFormula(” trong công thức trên để thêm ba số 0 ở đầu các mã số dạng số.
if(A2:A="",,"000"Bây giờ công thức sẽ trông như sau:
=ArrayFormula(if(A2:A="",,"000"&ifna(vlookup(B2:B,G2:H,2,false)&ifna(vlookup(C2:C,I2:J,2,false)))))Thêm số 0 ở đầu như trên là tùy chọn vì màu mè thư mục kết quả, tức là chúng ta có thể thêm các số 0 dẫn đầu mà không cần sử dụng công thức, tức là định dạng cột kết quả chính nó, chúng ta có thể thêm các số 0 dẫn đầu.
Nếu bạn thích phương pháp định dạng này, dưới đây là hướng dẫn: Sử dụng Định dạng Đầu số dẫn đầu trong Google Sheets.
Bước 5
Đây là bước cuối cùng để tránh các ID trùng lặp.
Làm thế nào để tìm các cuốn sách từ cùng một tác giả trong cùng một thể loại?
Ở đây, chúng ta cần sử dụng công thức đếm chạy trong một cột trợ giúp. Sau đó, nếu bạn muốn, bạn có thể bỏ qua việc sử dụng cột trợ giúp này.
Trong công thức của tôi (công thức cuối cùng, không phải công thức dưới bước 1 đến 4), bạn có thể thấy phạm vi L2:L (đếm chạy) được kết hợp ở phần cuối. Ô L2 trong phạm vi đó chứa công thức đếm chạy dưới đây.
Số chạy:
=ARRAYFORMULA(if(len(C2:C),(COUNTIFS(B2:B&C2:C,B2:B&C2:C,ROW(C2:C),"<="&ROW(C2:C))),))Để giải thích công thức này, tham khảo hướng dẫn trước đó của tôi với tiêu đề – Số Chạy trong Google Sheets.
Các cuốn sách của tác giả Mary Shelly lặp lại ba lần trong thể loại kinh dị, đúng không?
Trong cột L, số chạy trả về các số tuần tự cho những cuốn sách này. Thêm các số này vào công thức bước 4 sẽ làm cho các ID trở nên duy nhất.
Mà không có các số chạy này, các ID của những cuốn sách đó sẽ là trùng lặp (00033). Điều đó tạo ra các ID là 000331, 000332 và 000333.
Chỉ cần thêm phạm vi L2:L (số chạy) vào công thức bước 4 (hoặc chính công thức của ô L2 để tránh việc sử dụng một cột phụ).
Công thức ID Đọc Được Duy Nhất sử dụng Phạm vi L2:L (Cột Trợ Giúp):
=ArrayFormula(if(A2:A="",,"000"&ifna(vlookup(B2:B,G2:H,2,false))&ifna(vlookup(C2:C,I2:J,2,false))&L2:L))Công thức ID Đọc Được Duy Nhất sử dụng Công thức L2 (Không Cột Trợ Giúp):
=ArrayFormula(if(A2:A="",,"000"&ifna(vlookup(B2:B,G2:H,2,false))&ifna(vlookup(C2:C,I2:J,2,false))&if(len(C2:C),(COUNTIFS(B2:B&C2:C,B2:B&C2:C,ROW(C2:C),"<="&ROW(C2:C))),)))Trong ví dụ thứ hai của tôi, tức là tạo ID đọc được duy nhất cho các tài sản đã mua, tôi đã sử dụng lại công thức trên nhưng có một số chỉnh sửa nhỏ. Tìm hiểu chi tiết dưới tiêu đề phụ dưới đây.
Làm thế nào để Thay Đổi Công thức Tạo ID Đọc Được để Phù Hợp với Dữ Liệu Của Tôi?
Công thức 1 trong ô E2:
=ArrayFormula(if(A2:A="",,"000"&ifna(vlookup(B2:B,H2:I,2,false))&ifna(vlookup(C2:C,J2:K,2,false))&ifna(vlookup(D2:D,L2:M,2,false))&O2:O))
Công thức Số Chạy 2 trong ô O2:
=ARRAYFORMULA(if(len(C2:C),(COUNTIFS(B2:B&C2:C&D2:D,B2:B&C2:C&D2:D,ROW(C2:C),"<="&ROW(C2:C))),))Thay đổi đầu tiên nằm trong công thức số chạy. Trong ví dụ đầu tiên, nó là B2:B&C2:C và lần này có một cột nữa. Vì vậy, nó sẽ là B2:B&C2:C&D2:D.
Lưu ý: Bên trong công thức ROW, bạn có thể sử dụng bất kỳ tham chiếu cột nào. Đó là C2:C trong các công thức của tôi trong ví dụ thứ nhất và thứ hai. Nhưng nó cũng có thể là A2:A. Nó chỉ trả về các số tuần tự và đó là những gì chúng tôi muốn.
Đó là tất cả. Để tạo mã số duy nhất, đọc được và số duy nhất trong Google Sheets như vậy.
Cách Tạo Mã Số Alpha-Numeric Duy Nhất trong Google Sheets?
Mà không cần nỗ lực nào, chúng ta có thể làm cho các công thức của chúng ta trả về các mã số alpha-numeric duy nhất! Chỉ cần thay đổi các số trong bảng tra cứu thành các chữ cái.
Phải, không có thay đổi nào trong công thức.
Hãy thử thay đổi các số thành các chữ cái trong bảng tra cứu và xem kết quả!
