HƯỚNG DẪN CÁCH MỞ RỘNG NGÀY VÀ CHỈ ĐỊNH GIÁ TRỊ TRONG GOOGLE SHEETS

Nếu bạn có một tập dữ liệu và muốn note lại dữ liệu đó trong một vài khoảng thời gian (ngày - tháng - năm) cụ thể nào đó thì việc biết cách mở rộng ngày và chỉ định giá trị trong Google Sheets thật sự hữu ích đấy. 

Trong bài viết này, mình sẽ hướng dẫn bạn kết hợp hai hàm và gộp chúng trong hàm QUERY để mở rộng ngày và chỉ định giá trị trong Google Sheets. Và hai hàm đó là:

• Hàm SEQUENCE để mở rộng ngày tháng 
• Hàm VLOOKUP để tìm giá trị thích hợp.

Mình có phạm vi các ngày trong tập dữ liệu của mình và mỗi ngày có một giá trị riêng mà mình sẽ chỉ định. Mỗi ngày trong phạm vi sẽ xuất hiện trong danh sách mở rộng và giá trị được chỉ định của chúng sẽ hiển thị bên cạnh.

Ví dụ, đây là lịch trình du lịch của một du khách.

Ở đây, ta có một bảng đầu vào với phạm vi các ngày (vùng E2: F5) và các thành phố mà du khách đó đến du lịch được gán cho những ngày này (vùng G2: G5). Ở đầu ra, ta sẽ tạo danh sách mở rộng về các ngày cụ thể đó ở vùng A1: B21. Mỗi một ô trong vùng sẽ đại diện cho mỗi ngày cụ thể trong phạm vi đã xác định và tên thành phố được chỉ định sẽ được đặt bên cạnh ngày.

CHÚ Ý: 

1. Mỗi ngày đều phải có một giá trị được chỉ định, tức là sẽ KHÔNG có ngày nào được trống. 
2. Phạm vi các ngày đó phải có ngày bắt đầu cũng như ngày kết thúc.
3. Các giá trị được gán với ngày KHÔNG được trùng lặp (ví dụ, trong 1 ngày thì du khách không thể đi 1 lần nhiều thành phố được).
4. Đối với các khoảng ngày (từ ngày bắt đầu đến ngày kết thúc) trong vùng trên thì không nhất thiết phải liên tiếp nhau, tức có thể cách nhau vài ngày, vài tuần hoặc tháng (ví dụ, đó là những ngày du khách trên sẽ ở nhà, nghỉ ngơi,...).

Mở rộng ngày với hàm SEQUENCE trong Google Sheets

Đầu tiên, mình muốn mở rộng ngày trong phạm vi trên và mình sẽ sử dụng hàm SEQUENCE với cú pháp như sau: 

=SEQUENCE (rows, columns, start, step)

1. "=": Mở đầu hàm/ công thức

2. SEQUENCE: tên hàm

3. rows (bắt buộc): số lượng hàng đánh số. VD với 10 hàng, hàm sẽ trả về kết quả 1 mảng từ 1 tới 10.

4. columns (tùy chọn): số lượng cột đánh số, mặc định là 1

5. start (tùy chọn): số đầu tiên trong mảng, mặc định là 1

6. step (tùy chọn): khoảng cách nhảy bậc giữa các số, mặc định là 1. Nếu giá trị step thay đổi, chẳng hạn bằng 3, ta sẽ có dãy 1, 4, 7…

Và số hàng là những gì ta cần bởi vì đó là phạm vi từ ngày bắt đầu đến ngày kết thúc.

BƯỚC 1: Tìm giá trị nhỏ nhất và giá trị lớn nhất của vùng

Bước đầu tiên là ta tìm ngày đầu tiên du khách đó bắt đầu cũng như ngày cuối cùng người đó kết thúc du lịch trong vùng E2: F5 (phạm vi các ngày). Ở đây, cách đơn giản nhất vẫn là dùng hàm MIN và MAX.

Để tìm ngày đầu tiên của vùng, ta nhập hàm MIN: 

=MIN(E2:F5)

Và để tìm ngày cuối cùng của vùng đó, ta nhập hàm MAX:

=MAX(E2:F5)

BƯỚC 2: Mở rộng ngày với hàm SEQUENCE

Mình tính tổng số ngày giữa ngày đầu tiên & ngày cuối cùng. Và vì mình muốn bao gồm luôn ngày bắt đầu & ngày kết thúc nên mình sẽ cộng thêm (+1) vào.

Mình cũng muốn xác định giá trị nào sẽ bắt đầu trong danh sách mở rộng và nhập giá trị đó ở đối số thứ ba [start] của hàm SEQUENCE. Để đối số bắt đầu là ngày nhỏ nhất trong tập dữ liệu thì giá trị đó phải là ngày đầu tiên của phạm vi các ngày.

Như vậy, mình đã xác định được đối số [rows] và [start]. Còn hai đối số còn lại thì mình để mặc định là 1. Dù để là mặc định nhưng bạn phải điền ở đối số [column] là 1 mới có thể tiếp tục điền ở đối số [start] nhé, và đây là hàm SEQUENCE của ta: 

=SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5))

Khi bạn nhập công thức này vào ô A2, Google Sheets sẽ trả về các số thay vì ngày tháng. Lúc này, để định dạng các giá trị thành định dạng ngày - tháng - năm thích hợp, bạn chọn thẻ Format => Number => Date.

Vấn đề duy nhất là sẽ có một số ngày bổ sung ở đây KHÔNG thuộc bất kỳ khoảng (từ ngày bắt đầu đến ngày kết thúc) nào của vùng. Ví dụ, ngày 4/11/2020 hoặc 15/11/2020 không hề thuộc khoảng nào trong dữ liệu cho trước. Vì thế, ta sẽ phải lọc ra những ngày này.

BƯỚC 3: Xác định ngày bắt đầu & kết thúc của mỗi giá trị được gán

Bước tiếp theo là tạo bảng để chỉ định các giá trị cho các ngày.

Mình sao chép các ngày bắt đầu vào một cột riêng biệt và cả tên thành phố vào cột tiếp theo. Sau mỗi ngày bắt đầu trong bảng là một hàng mới có ngày kết thúc được cộng thêm +1. Đối với những ngày này, mình sẽ không đặt bất kỳ tên thành phố nào mà chỉ để TRỐNG các ô đó.

 

Bằng cách này, mình đã xác định tên thành phố được hiển thị ở ngày bắt đầu cũng như sẽ biến mất trước ngày kết thúc.

Tìm trị với hàm VLOOKUP trong Google Sheets

Hàm VLOOKUP được dùng để tìm kiếm giá trị theo chiều dọc, với cú pháp như sau:

=VLOOKUP(search_key, range, index, is_sorted)

1. "=": bắt đầu hàm/công thức

2. VLOOKUP: tên hàm

3. search_key: giá trị cần tìm

4. range: vùng giới hạn cần tìm, bạn cần F4 để Fix cố định giá trị cho mục đích copy công thức tự động.

5. index: số thứ tự của cột lấy dữ liệu trong range cần dò tìm.

6. is_sorted: Là giá trị Logic (TRUE=1, FALSE=0) quyết định số chính xác hay số tương đối với range.

BƯỚC 1: Sử dụng danh sách mở rộng các ngày trong hàm VLOOKUP

1.  search_key sẽ là danh sách mở rộng các ngày, bởi mình muốn tìm các ngày này
2.  range: mô tả giá trị nào sẽ được chỉ định cho ngày tháng nào, vì vậy ta nên sử dụng bảng ta tạo thêm ở trên để xác định ngày bắt đầu và ngày kết thúc cho mỗi thành phố.
3. index sẽ là 2 vì mình muốn trả về tên các thành phố và chúng hiện nằm trong cột thứ hai của bảng.
4. is_sorted: mình để mặc định là 1 vì không ảnh hưởng gì đến giá trị văn bản.

Và đây là công thức để ta gán tên thành phố với các ngày: 

=VLOOKUP(A2:A,E9:F,2,1)

Nếu chúng ta viết đây là ô đầu tiên của cột các giá trị được gán (ví dụ là B2), nó hoạt động vì chúng ta nhận được kết quả là London.

BƯỚC 2: Dùng hàm ARRAYFORMULA để lặp lại công thức trên cho toàn bộ dữ liệu của ta

Chúng ta có thể lặp lại biểu thức trước đó trong mỗi ô của cột B để gán tên các thành phố, nhưng có một cách đơn giản hơn, đó là dùng hàm ARRAYFORMULA:

=ArrayFormula(VLOOKUP(A2:A,E9:F,2,1))

Bằng cách này, ta chỉ cần nhập biểu thức trong ô đầu tiên và nó sẽ tự động được thực hiện trên toàn bộ mảng này.

Kết hợp hai hàm trên và lọc ra các ngày không cần thiết với hàm QUERY

Ở đây, mình muốn gộp hai hàm SEQUENCE & VLOOKUP  trong một hàm duy nhất.

BƯỚC 1: Sử dụng hai hàm trước trong một mảng

Hãy sử dụng hai hàm trước đó trong cùng một mảng bằng cách đặt hai biểu thức vào dấu ngoặc {}, cách nhau bởi dấu phẩy (,). Đầu tiên, ta chèn hàm SEQUENCE và sau đó là hàm VLOOKUP được lồng trong hàm ARRAYFORMULA:

={SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5)), ArrayFormula(VLOOKUP(A2:A,E9:F,2,1))}

BƯỚC 2: Thay thế các ô gồm các ngày mở rộng bằng công thức SEQUENCE

VLOOKUP đang dùng kết quả của công thức SEQUENCE để làm [search_key], vì vậy mình cũng có thể thay thế đối số đầu tiên của hàm VLOOKUP bằng công thức SEQUENCE:

={SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5)), ArrayFormula(VLOOKUP(SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5)),E9:F,2,1))}

BƯỚC 3: Loại bỏ ô TRỐNG bằng hàm QUERY

Bây giờ là lúc để ta lọc ra các hàng KHÔNG có giá trị được chỉ định. Như bạn thấy, có nhiều hàng KHÔNG hiển thị tên thành phố. Cách này có thể sẽ hơi phức tạp một chút, vì liên quan đến hàm QUERY.

Mình sẽ sử dụng một truy vấn để loại bỏ các hàng KHÔNG có giá trị được chỉ định (không có tên thành phố) trong cột thứ hai.

Mặc dù hàm này nâng cao hơn một chút, nhưng cú pháp của hàm QUERY rất giống với các hàm khác:

=QUERY(data, query, headers)

1. "=": bắt đầu hàm

2. QUERY: tên hàm

3. data: vùng dữ liệu để thực hiện truy vấn, đây sẽ là tất cả các tập kết quả trước đó chúng ta vừa tính toán ở trên. Mình sẽ đặt phạm vi ô kết quả ở đây.

4. query: truy vấn để thực hiện, được viết bằng ngôn ngữ truy vấn của Google Sheets. Mình muốn giữ lại các hàng có giá trị được gán (cột thứ hai), tức là các hàng KHÔNG trống nên nhập như sau: “Select * where Col2 <>”.

5. headers (tùy chọn): số dòng tiêu đề ở đầu dữ liệu

Kết hợp hai hàm trên với hàm QUERY, ta được: 

=QUERY({SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5)), ArrayFormula(VLOOKUP(SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5)),E9:F,2,1))}, "Select * where Col2<>''", 0)

Ta chỉ cần nhập công thức kết hợp này vào ô A2 và nhấn Enter. Kết quả là một danh sách với các ngày được mở rộng và các giá trị được chỉ định của chúng.

Trên đây là những hướng dẫn về cách mở rộng ngày và chỉ định giá trị cụ thể cho ngày đó trong Google Sheets. Ngoài ra, các bạn có thể tham khảo khóa học TẠI ĐÂY của Gitiho để nâng cao hiệu quả làm việc trên máy tính nhé. Đây là các khóa học cung cấp rất đầy đủ và chi tiết các kiến thức từ cách sử dụng Excel, Word, Google Sheets,... đến các nghiệp vụ kế toán, lập trình. Bạn sẽ thấy rõ sự khác biệt rõ rệt sau khi tham gia khóa học này.