Bạn có thể sử dụng các công thức TEXT, SEQUENCE và XLOOKUP để chuyển đổi tên các ngày trong tuần thành số trong Google Sheets.
Tại sao bạn nên học cách thực hiện kỹ thuật này?
Nếu bạn có các tên ngày trong tuần trong một bảng và bạn muốn sắp xếp chúng, thì các ngày sẽ được sắp xếp theo thứ tự chữ cái, không theo thứ tự các ngày trong tuần.
Biết cách chuyển đổi tên các ngày trong tuần thành số sẽ rất hữu ích, vì bạn có thể sử dụng biểu thị số để sắp xếp cột.
Một trường hợp sử dụng khác là khi tạo biểu đồ. Giá trị số thường phù hợp hơn để tạo biểu đồ, giúp đại diện và giải thích các mẫu dữ liệu qua các ngày trong tuần dễ dàng hơn.
Giả sử bạn có các chuỗi ngày trong tuần trong một cột từ Chủ Nhật đến Thứ Bảy hoặc từ CN đến T7.
Làm thế nào để trả về các số ngày trong tuần tương ứng trong một cột khác sử dụng các công thức mảng hoặc không mảng?
Bạn có thể sử dụng nhiều sự kết hợp của các công thức, nhưng chúng ta sẽ tuân thủ một phương pháp duy nhất sử dụng TEXT, SEQUENCE và XLOOKUP.
Cách chuyển đổi tên các ngày trong tuần thành số trong Google Sheets
Chúng ta sẽ sử dụng XLOOKUP để tìm kiếm các khóa tìm kiếm (search_key), trong trường hợp này là tên các ngày trong tuần, trong một mảng (lookup_range) và trả về các số tương ứng từ một mảng khác (result_range).
Cú pháp: XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
Lưu ý: Các đối số tùy chọn trong dấu ngoặc vuông không yêu cầu cho loại tìm kiếm này.
Mảng đầu tiên sẽ chứa tên các ngày trong tuần và mảng thứ hai sẽ chứa các số tương ứng.
Công thức thú vị ở đây là cách chúng ta tạo ra hai mảng này. Chúng ta sẽ sử dụng sự kết hợp của TEXT và SEQUENCE cho mảng đầu tiên và chỉ dùng SEQUENCE cho mảng thứ hai.
Hãy tìm hiểu chi tiết và chuyển đổi tên các ngày trong tuần thành số.
Bước 1: Khóa tìm kiếm
Bạn có thể chọn chuyển đổi nhiều tên ngày trong tuần thành số tương ứng cùng một lúc hoặc từng bước một. Chúng ta sẽ bắt đầu bằng một giá trị trong ô A1 và sau đó sử dụng công thức để chuyển đổi A1:A hoặc bất kỳ phạm vi nào bạn muốn.
Ví dụ, giả sử khóa tìm kiếm là “Thứ Tư” trong ô A1.
Bước 2: Tạo mảng tìm kiếm
Trong một trong các hướng dẫn trước đó của tôi có tựa đề “Cách Tự động điền các ngày trong tuần trong Google Sheets”, tôi đã chỉ hai phương pháp để tự động điền tên các ngày trong tuần vào một cột. Phương pháp đầu tiên sử dụng một cách tiếp cận không sử dụng công thức, và phương pháp thứ hai sử dụng một công thức mảng. Chúng ta sẽ sử dụng công thức mảng đó để tạo ra mảng tìm kiếm.
=ArrayFormula(TEXT(SEQUENCE(7), “DDDD”))
Tạm thời, hãy nhập công thức này vào ô C1.
Bước 3: Tạo mảng kết quả
Phạm vi kết quả đơn giản là các số thứ tự từ 1 đến 7. Chúng ta sẽ sử dụng công thức SEQUENCE sau đây.
=SEQUENCE(7)
Tạm thời, hãy nhập công thức này vào ô D1.
Công thức để chuyển đổi tên các ngày trong tuần thành số:
Để chuyển đổi tên ngày trong tuần trong ô A1 thành số ngày trong tuần tương ứng, hãy sử dụng công thức sau đây trong ô B1.
=XLOOKUP(A1, ArrayFormula(TEXT(SEQUENCE(7),”DDDD”)), SEQUENCE(7))
Trong đó:
- A1 là khóa tìm kiếm.
- ArrayFormula(TEXT(SEQUENCE(7),”DDDD”)) là mảng tìm kiếm.
- SEQUENCE(7) là mảng kết quả.
Nhưng đợi đã. Là một thực hành tiêu chuẩn, hãy di chuyển hàm ArrayFormula lên đầu công thức.
Công thức cuối cùng:
=ArrayFormula(XLOOKUP(A1, TEXT(SEQUENCE(7),”DDDD”), SEQUENCE(7)))
Để chuyển đổi tên ngày trong tuần trong cột A (A1:A), bạn có thể thay thế khóa tìm kiếm A1 bằng A1:A. Đó là tất cả.
Mẹo bổ sung
1. Quản lý việc viết tắt của các tên ngày trong tuần trong cột khóa tìm kiếm
Khi chuyển đổi tên ngày trong tuần thành số, rất quan trọng phải xem xét điều này. Công thức trước đó không được cấu hình để xử lý các tên ngày viết tắt như “CN” cho “Chủ Nhật”.
Nếu bạn có các tên ngày viết tắt, hãy sửa công thức bằng cách thay đổi kiểu định dạng văn bản “DDDD” bằng “DDD”:
=ArrayFormula(XLOOKUP(A1:A, TEXT(SEQUENCE(7), “DDD”), SEQUENCE(7)))
Nếu dữ liệu của bạn bao gồm cả các biểu thức viết tắt và không viết tắt của tên ngày trong tuần, bạn có thể sử dụng công thức sau:
=ArrayFormula(XLOOKUP(A1:A, VSTACK(TEXT(SEQUENCE(7), “DDDD”), TEXT(SEQUENCE(7), “DDD”)), VSTACK(SEQUENCE(7), SEQUENCE(7))))
Công thức này linh hoạt, có thể xử lý các biểu thức viết tắt, không viết tắt và một sự kết hợp của cả hai trong các tên ngày trong tuần.
Hàm VSTACK được sử dụng để xếp chồng dọc các loại tên ngày trong tuần trong mảng tìm kiếm và xếp chồng dọc các số tương ứng một cách hợp lý.
2. Chuyển đổi tên ngày trong tuần thành số với các loại tuần hóa tùy chỉnh (ví dụ: 1 cho Thứ Hai, 7 cho Chủ Nhật)
Hàm WEEKDAY, một trong các hàm ngày tháng, trong Google Sheets hỗ trợ trích xuất số thứ tự ngày trong tuần từ các ngày.
Như bạn có thể biết, chúng ta có thể lấy được số thứ tự ngày trong tuần 1-7 cho Chủ Nhật-Thứ Bảy hoặc 1-7 cho Thứ Hai-Chủ Nhật bằng cách chỉ định ‘type’ trong hàm này.
Các công thức trên, dùng để chuyển đổi tên các ngày trong tuần (không phải ngày) thành số, được viết để trả về các số từ 1 đến 7 cho Chủ Nhật – Thứ Bảy. Trong hầu hết các trường hợp, như sắp xếp và biểu đồ, điều này có thể là đủ.
Nhưng nếu bạn muốn thay đổi nó và nhận được các số từ 1 đến 7 cho Thứ Hai – Chủ Nhật, bạn có thể dễ dàng làm điều đó bằng cách sửa phần công thức SEQUENCE trong mảng tìm kiếm.
Phần công thức hiện tại là SEQUENCE(7). Bạn cần sửa đổi thành SEQUENCE(7, 1, 2).
Vì vậy, công thức sẽ là:
=ArrayFormula(XLOOKUP(A1:A, TEXT(SEQUENCE(7, 1, 2), “DDDD”), SEQUENCE(7)))
Thay “DDDD” bằng “DDD” dựa trên việc bạn có các tên ngày không viết tắt hoặc viết tắt.
Nếu bạn ưa thích công thức “all-weather”, nó sẽ là như sau:
=ArrayFormula(XLOOKUP(A1:A, VSTACK(TEXT(SEQUENCE(7, 1, 2), “DDDD”), TEXT(SEQUENCE(7, 1, 2), “DDD”)), VSTACK(SEQUENCE(7), SEQUENCE(7))))
Với điều này, chúng ta kết thúc bài hướng dẫn này. Chúng tôi hoan nghênh ý kiến và phản hồi của bạn.
Hãy ghé thăm Crawlan.com để biết thêm nhiều bài viết hữu ích khác!
