Liên kết ngày trong lịch với sự kiện trong Google Sheets

GiGiKing·Tháng ba 29, 2024·0 comments

Trong bài viết này, chúng ta sẽ hướng dẫn bạn cách sử dụng công thức HYPERLINK để liên kết ngày trong lịch với các sự kiện trong Google Sheets. Điều này sẽ rất hữu ích để tổ chức các sự kiện và cải thiện quy trình làm việc của bạn.

Chúng tôi sẽ chỉ cho bạn cách tạo một mẫu lịch tương tác có thể đáp ứng đến việc chọn tháng và thay đổi năm, và thiết lập liên kết giữa các ngày và sự kiện trong một bảng khác.

Trước tiên, hãy xem một mẫu lịch đã được tạo sẵn với tất cả các công thức cần thiết.

Tạo một Lịch Tương tác trong Google Sheets

Để có thể liên kết ngày với các sự kiện, chúng ta cần hai bảng trong một tập tin: một bảng lịch tương tác có thể đáp ứng đến việc chọn tháng trong một danh sách thả xuống và một bảng có ngày trong một cột và các sự kiện trong cột khác.

Hãy bắt đầu bằng cách tạo bảng lịch tương tác trong Google Sheets.

Tạo bố cục cho Lịch Tương tác

  1. Truy cập vào https://sheets.new/ để tạo một tập tin Google Sheets với một bảng trống.
  2. Nhấp đúp vào tên bảng ở phía dưới và đổi tên thành “Calendar”.
  3. Trong ô H1, nhập năm mà bạn muốn tạo lịch tương tác trong Google Sheets.
  4. Trong ô G1, nhấp vào Chèn > Thả xuống và tạo một danh sách thả xuống với các tên tháng từ Tháng 1 đến Tháng 12. Hãy tránh việc nhập sai chính tả khi nhập tên tháng.
  5. Trong các ô B3:H3, nhập các ngày trong tuần, bắt đầu từ Chủ Nhật và kết thúc vào Thứ Bảy.

Layout for linking calendar dates to events in Google Sheets

Công thức để điền các ngày trong Lịch

Trong ô B5, nhập công thức sau để có được Chủ Nhật đầu tiên của tuần, tương ứng với ngày bắt đầu của tháng và năm được chọn:

=LET( start_dt, DATE($H$1, MONTH($G$1&1), 1), start_dt-WEEKDAY(start_dt)+1 )

Cú pháp của hàm LET:

LET( name1, value_expression1, [name2, …], [value_expression2, …], formula_expression )

Trong đó:

  • start_dt là tên (name1) của value_expression1.
  • value_expression1 là công thức DATE($H$1, MONTH($G$1&1), 1)
    • Nó tạo ra ngày bắt đầu của tháng và năm trong ô G1 và H1. Ví dụ, nếu tháng là tháng 1 và năm là 2023, nó sẽ trả về ngày 1 tháng 1, 2023.
  • formula_expression là start_dt-WEEKDAY(start_dt)+1.
    • Nếu tháng là tháng 1 và năm là 2023, nó sẽ trừ số thứ tự của ngày Chủ Nhật 1 tháng 1, 2023 khỏi ngày 1 tháng 1, 2023 và cộng thêm 1. Điều này đảm bảo rằng ngày trong ô B5 luôn là ngày Chủ Nhật.

Trong ô C5, nhập =B5+1, và sao chép mẫu này cho tất cả các ô khác trong phạm vi B5:H20, cụ thể là các hàng 5, 8, 11, 14, 17 và 20. Điều này bao gồm việc thêm 1 vào ngày trước đó trong mỗi trường hợp. Ví dụ, công thức trong ô D5 sẽ là C5+1.

Áp dụng định dạng có điều kiện để ẩn các ngày thuộc tháng trước hoặc tháng sau, tập trung đặc biệt vào tuần đầu và tuần cuối của tháng.

  • Chọn B5:H20 và nhấp vào Định dạng > Định dạng có điều kiện.
  • Nhập =AND(MONTH(B5)<>MONTH($G$1&1), ISDATE(B5)) trong ‘Công thức tùy chỉnh là’ và chọn màu chữ trắng để làm nổi bật.

Bây giờ, chúng ta đã tạo một lịch tương tác trong Google Sheets có thể đáp ứng đến việc chọn tháng và năm.

Hyperlink interactive calendar dates to events in Google Sheets

Bây giờ, chúng ta sẽ tiến tới bước thứ hai, đó là tạo một bảng dữ liệu sự kiện để liên kết các ngày trong lịch tương tác.

Bảng Dữ liệu để Liên kết Ngày trong Lịch với Sự kiện

Ở bước này, không cần sử dụng công thức nào cả. Chỉ cần nhập dữ liệu cần thiết để liên kết với các ngày trong lịch.

  1. Nhấp vào nút + ở góc dưới cùng bên trái của bảng “Calendar” để thêm một bảng mới.
  2. Nhấp đúp vào nó và đổi tên thành “Events”.
  3. Trong cột A của bảng “Events”, nhập các ngày mà bạn muốn liên kết với các ngày trong bảng “Calendar”.
  4. Trong cột B, nhập tên sự kiện hoặc mô tả.

Bây giờ, chúng ta sẽ tiếp tục các bước cuối cùng để liên kết các ngày trong lịch với các sự kiện trong tab “Events”.

Cách Liên kết Ngày trong Lịch với Sự kiện trong Google Sheets

Hãy quay trở lại ô B5 trong “Calendar”.

Công thức hiện tại trong ô đó là =LET(start_dt, DATE($H$1, MONTH($G$1&1), 1), start_dt-WEEKDAY(start_dt)+1)

Phân tích công thức này:

  • name1 = start_dt
  • value_expression1 = DATE($H$1, MONTH($G$1&1), 1)
  • formula_expression = start_dt-WEEKDAY(start_dt)+1

Để liên kết với ngày tương ứng trong cột A của tab “Events”, chúng ta cần chỉnh sửa công thức như sau.

=LET( start_dt, DATE($H$1, MONTH($G$1&1), 1), dt, start_dt-WEEKDAY(start_dt)+1, look_up, MATCH(dt, Events!$A:$A, 0), IF(IFNA(look_up, dt), HYPERLINK("URL"&look_up, dt), dt) )

Công thức này là quan trọng để liên kết các ngày trong lịch với các sự kiện trong Google Sheets. Các công thức khác trong lịch là các biến thể của công thức này với các thay đổi nhỏ. Chúng tôi sẽ giải thích chúng sau thông qua các công thức giải thích sau.

Cấu trúc của Công thức

Dưới đây là giải thích các thành phần trong công thức:

  • name1 = start_dt
  • value_expression1 = DATE($H$1, MONTH($G$1&1), 1)
    • Trả về ngày bắt đầu của tháng.
  • name2 = dt
  • value_expression2 = start_dt-WEEKDAY(start_dt)+1
    • Trả về Ngày bắt đầu của tuần, tức là Chủ Nhật của ngày bắt đầu.
  • name3 = look_up
  • value_expression3 = MATCH(dt, Events!$A:$A, 0)
    • Tìm kiếm dt trong cột A của tab “Events” và trả về vị trí tương đối.
  • formula_expression = IF(IFNA(look_up, dt), HYPERLINK("URL"&look_up, dt), dt)
    • Nếu look_up trả về NA() (không có kết quả khớp), công thức sẽ trả về dt.
    • Nếu có một kết quả khớp, nó sẽ thực hiện công thức HYPERLINK sau: HYPERLINK("URL"&look_up, dt)

Giải thích về phần HYPERLINK:

Cú pháp:

HYPERLINK(URL, [link_label])

link_label là chính dt. URL là URL của ô đầu tiên trong tab “Events”, nhưng bỏ đi số thứ tự của ô.

Để lấy URL:

  1. Truy cập ô A1 trong tab “Events”.
  2. Nhấp chuột phải và chọn “Xem thêm công việc trên ô này” > “Lấy liên kết đến ô này”.
  3. Dán liên kết vào bất kỳ ô nào và xóa số hàng cuối cùng (1 trong phần cuối của công thức).
  4. Cuối cùng, nối với số hàng trả về bởi look_up, và đó là “URL”&look_up.

Liên kết Ngày trong Lịch với Sự kiện trong Các Ô Khác

Sau khi hiểu cách liên kết ngày đầu tiên trong lịch với tab “Events”, bây giờ chúng ta sẽ xem xét các ngày còn lại.

  1. Sao chép và dán cùng một công thức vào ô C5.
  2. Thay đổi value_expression2, tức là start_dt-WEEKDAY(start_dt)+1, thành B5+1. Sau đó, loại bỏ name1 và value_expression1.

Vậy là công thức trong ô C5 sẽ là:

=LET( dt, B5+1, look_up, MATCH(dt, Events!$A:$A, 0), IF(IFNA(look_up, dt), HYPERLINK("URL"&look_up, dt), dt) )

  1. Sao chép và dán công thức này vào tất cả các ô khác trong lịch, cụ thể là các hàng 5, 8, 11, 14, 17 và 20.
  2. Thay thế B5 bằng tham chiếu đến ô chứa ngày trước đó.

Quá trình này đảm bảo rằng mỗi ô trong lịch có công thức chính xác, tự động điều chỉnh đến các ngày và sự kiện tương ứng trong tab “Events”.

GiGiKing

Related posts