By combining a few formulas we can create a follow-up schedule table in Google Sheets. It will be useful for you to timely make billing or appointment follow-ups systematically.
Đầu tiên, bạn cần chuẩn bị các dữ liệu sau đây:
- Ngày theo dõi đầu tiên.
- Tên người liên hệ.
- Liên hệ (số điện thoại hoặc email).
- Số lần theo dõi.
- Thời gian theo dõi.
Dùng các chi tiết này dưới dạng bảng, chúng ta có thể tạo bảng lịch theo dõi trong Google Sheets. Hãy xem chi tiết ở dạng bảng dưới đây:
Dữ liệu mẫu (Dữ liệu nguồn để tạo lịch):
| Ngày theo dõi đầu tiên | Tên | Liên hệ | Số ngày theo dõi | Khoảng cách |
|---|---|---|---|---|
| 13/06/2020 | Evan | 123456 | 2 | 5 ngày |
Chúng ta muốn nhìn thấy các thông tin sau (số dòng ở dòng #2 và #3) tương ứng với Evan.
Lịch trình:
Làm thế nào để tạo bảng trên từ dữ liệu nguồn bằng cách sử dụng công thức?
Tạo lịch trình theo dõi thanh toán | Bổ nhiệm trong Google Sheets
Tôi viết công thức cho tối đa 10 lần theo dõi cho mỗi người liên hệ.
Tức là, giá trị tối đa trong cột thứ tư (số ngày theo dõi) của bảng ‘Dữ liệu mẫu’ sẽ là 10. Hy vọng điều đó sẽ đủ. Bắt đầu thôi.
-
Trong Google Sheets, nhập dữ liệu mẫu. Dữ liệu được nhập trong phạm vi A1:E5. Tôi đã đặt tên tab là “Follow-Up”.
-
Tôi giữ tổng số hàng trong bảng tính này là 20, nhằm nâng cao hiệu năng. Bạn có thể sử dụng nhiều hàng hơn (ít nhất 500 hàng) mà không gặp vấn đề.
-
Tổng số cột trong bảng tính này (Follow-Up) giới hạn là 15. Tức là cột cuối cùng là cột O. Trong đó, 5 cột đầu tiên chứa dữ liệu mẫu và 10 cột tiếp theo là cho 10 lần theo dõi.
-
Bây giờ bạn cần làm theo các bước dưới đây, bắt đầu từ kéo và thả công thức trong ô F2 để chuẩn bị tạo bảng lịch trình theo dõi bằng cách sử dụng các công thức trong Google Sheets.
Tạo một chuỗi ngày (thời gian theo dõi) (Bước 1):
Đầu tiên, hãy xem cú pháp của công thức chuỗi (một hàm tạo số hoặc ngày tuần tự) và sau đó là một công thức chung dựa trên nó.
Cú pháp: SEQUENCE(rows, [columns], [start], [step])
Công thức chung:
=iferror( sequence( 1, No_of_follow-up_days, First_follow-up_date + How_often, How_often ) )Các tham số trong hàm và tham chiếu tương ứng:
- rows – 1
- columns – No_of_follow-up_days
- start – First_follow-up_date + How_often
- step – How_often
Dựa trên công thức chung này, hãy sử dụng công thức chuỗi dưới đây trong ô F2 và kéo xuống đến ô F20.
=iferror( sequence( 1, D2, A2+E2, E2 ) )
Bây giờ đến bước thứ hai.
Kết hợp dữ liệu nguồn và các ngày thời gian theo dõi và làm phẳng (Bước 2):
Bây giờ, chúng ta sẽ bắt đầu tạo bảng lịch trình theo dõi bằng công thức mảng trong một tab mới có tên là “Sch”, trong cùng một tệp.
Để bạn hiểu rõ công thức của tôi, tôi sẽ sử dụng trước hết phạm vi A2:O5 (phạm vi đóng) thay vì A2:O (phạm vi mở) từ tab “Follow-Up”.
Tôi sẽ kết hợp các cột dữ liệu nguồn theo cách cụ thể bằng cách sử dụng các toán tử dấu & sau đó làm phẳng nó bằng cách sử dụng hàm FLATTEN không được tài liệu ghi chú. Xem phần bên dưới để hiểu rõ hơn.
Công thức phạm vi đóng:
=ArrayFormula( flatten( 'Follow-Up'!A2:A5&"|"& 'Follow-Up'!B2:B5&"|"& 'Follow-Up'!C2:C5&"|"& 'Follow-Up'!F2:O5,"|" ) )
Trên đây là một phần quan trọng của việc tạo bảng lịch trình theo dõi trong Google Sheets. Trước khi tiếp tục đến các bước cuối cùng, cho phép tôi giải thích cách sử dụng một phạm vi mở trong công thức trên.
Công thức phạm vi mở:
Dưới đây là các tham chiếu mảng để thay đổi công thức từ “đóng” thành “mở” – ‘Follow-Up’!A2:A5, ‘Follow-Up’!B2:B5, ‘Follow-Up’!C2:C5 và ‘Follow-Up’!F2:O5.
Tất nhiên, chúng ta có thể sử dụng A2:A, B2:B, C2:C và F2:O là phạm vi mở. Nhưng tôi không làm như vậy ở đây. Tại sao?
Bởi vì sau này, khi tạo lịch trình theo dõi cuối cùng, tôi sẽ sử dụng các hàm Split và Query để định dạng dữ liệu đã kết hợp và được làm phẳng ở trên.
Trong hai hàm này, Query có thể không hoạt động chính xác trong một phạm vi mở như vậy. Bởi vì có một cột (cột đầu tiên) có dữ liệu loại hỗn hợp (ngày tháng và biểu tượng dấu ống) sau khi tách. Điều này sẽ gây vấn đề trong Query.
Vì vậy, đây là phương pháp đúng để sử dụng một phạm vi mở cho tình huống cụ thể của chúng ta.
Tôi sử dụng hàm Array_Constrain để giới hạn phạm vi A2:A mở, B2:B, C2:C và F2:O đến ô cuối cùng không rỗng trong cột A.
Bạn có thể thay thế A2:A5 trong công thức trên bằng Array_Constrain dưới đây.
array_constrain( ‘Follow-Up’!A2:A, MATCH(2,1/(‘Follow-Up’!A2:A<>””),1) ,1 )
Đối với B2:B5, sử dụng công thức Array_Constrain trên và thay A2:A bằng B2:B.
array_constrain( ‘Follow-Up’!B2:B, MATCH(2,1/(‘Follow-Up’!A2:A<>””),1) ,1 )
Công thức cho C2:C5:
array_constrain( ‘Follow-Up’!C2:C, MATCH(2,1/(‘Follow-Up’!A2:A<>””),1) ,1 )
và cuối cùng là công thức cho F2:O5:
array_constrain( ‘Follow-Up’!F2:O, MATCH(2,1/(‘Follow-Up’!A2:A<>””),1) ,10 )
Vì vậy, công thức sau khi thực hiện các thay đổi trên sẽ như sau.
=ArrayFormula( flatten( array_constrain( 'Follow-Up'!A2:A, MATCH(2,1/('Follow-Up'!A2:A<>""),1),1 )&"|"& array_constrain( 'Follow-Up'!B2:B, MATCH(2,1/('Follow-Up'!A2:A<>""),1),1 )&"|"& array_constrain( 'Follow-Up'!C2:C, MATCH(2,1/('Follow-Up'!A2:A<>""),1),1 )&"|"& array_constrain( 'Follow-Up'!F2:O, MATCH(2,1/('Follow-Up'!A2:A<>""),1),10 ) ) )Lịch trình theo dõi (Bước 3):
Chúng ta đang ở giai đoạn cuối cùng của việc tạo bảng lịch trình theo dõi trong Google Sheets. Hãy chỉnh sửa công thức trên ở ô A1 trong tab “Sch”.
Khi bạn tách đầu ra công thức trên (đóng hộp công thức trên bằng cách sử dụng hàm SPLIT) bằng cách sử dụng dấu “Ống” làm ngăn cách trong hàm SPLIT, bạn sẽ nhận được bảng lịch trình theo dõi thanh toán/bổ nhiệm như đã nêu ở trên trong một dạng chưa định dạng.
=Query( ArrayFormula( split( flatten( array_constrain( 'Follow-Up'!A2:A, MATCH(2,1/('Follow-Up'!A2:A<>""),1),1 )&"|"& array_constrain( 'Follow-Up'!B2:B, MATCH(2,1/('Follow-Up'!A2:A<>""),1),1 )&"|"& array_constrain( 'Follow-Up'!C2:C, MATCH(2,1/('Follow-Up'!A2:A<>""),1),1 )&"|"& array_constrain( 'Follow-Up'!F2:O, MATCH(2,1/('Follow-Up'!A2:A<>""),1),10 ) ),"|" ) ), "Select * where Col4 is not null" )Bạn có thể tạo bảng lịch trình theo dõi thanh toán/bổ nhiệm trong Google Sheets theo cách này.
Lưu ý: Ngày trong cột A và D có thể được trả về dưới dạng giá trị ngày. Trong trường hợp đó, chọn các cột đó và nhấp vào menu Format > Number > Date.
Đó là tất cả. Cám ơn bạn đã đọc bài viết này. Chúc bạn thành công trong việc tạo lịch trình theo dõi!”,
“Crawlan.com”)
