Giả sử rằng bạn có một trang tính khổng lồ chứa tất cả các số liệu về lượng mưa cho một khu vực nhất định trong hàng trăm năm qua hoặc lâu hơn. Trong ô A2: A37987, bạn có các ngày, từ 1 tháng 1 năm 1903 đến hết ngày 31 tháng 12 năm 2006.

Trong các ô B2: B37987, bạn có các phép đo cho mỗi ngày. Hơn nữa, một số phép đo có thể bằng 0 (nếu không có lượng mưa trong ngày) hoặc để trống (nếu không có số đọc nào được thực hiện vào ngày cụ thể đó). Với tất cả thông tin này, bạn muốn tính lượng mưa lịch sử trung bình cho bất kỳ ngày nhất định nào trong năm.

Một giải pháp liên quan đến việc sử dụng công thức mảng, như chi tiết ở đây:

  1. Chọn tất cả các ô trong cột A có chứa ngày tháng và gán cho phạm vi này tên là Ngày.

  2. Chọn tất cả các ô trong cột B có chứa dữ liệu về lượng mưa và gán cho phạm vi này tên Lượng mưa.

  3. Trong cột D, bắt đầu từ ô D2, hãy đặt tất cả các ngày trong năm. Bạn sẽ kết thúc với D2 đến D366 với đầy ngày tháng.

  4. Trong ô E2, nhập công thức mảng sau (kết thúc công thức bằng cách nhấn Shift + Ctrl + Enter). Kết quả của công thức là tổng của tất cả các ô trong phạm vi Lượng mưa, cho ngày được chỉ định trong ô D2.

=SUM((MONTH(Dates)=MONTH(D2))(DAY(Dates)=DAY(D2))Rainfall)

Trong ô F2, nhập công thức mảng sau (kết thúc công thức bằng cách nhấn Shift + Ctrl + Enter). Kết quả của công thức là số lượng ô trong phạm vi Lượng mưa, cho ngày trong ô D2, có giá trị trong đó.

=SUM((MONTH(Dates)=MONTH(D2))(DAY(Dates)=DAY(D2))(Rainfall<>""))

Trong ô G2, nhập công thức thông thường sau. Đây là mức trung bình của bạn cho ngày trong ô D2.

=IF(F2<>0,E2/F2,"")

Chọn phạm vi E2: G2 và sao chép tất cả các ngày được hiển thị trong cột D.

Cách làm này có hiệu quả nhưng phải mất khá nhiều thời gian để tính toán. Điều này là do bạn đã nhập hiệu quả 730 công thức mảng, mỗi công thức kiểm tra hơn 37.000 ô. Đây là rất nhiều công việc, và do đó nó có thể xuất hiện như máy của bạn bị “treo” sau khi bạn hoàn thành bước 7. Nó không bị treo; nó sẽ chỉ mất một lúc để hoàn thành các phép tính.

Để giảm số lượng phép tính phải thực hiện, bạn có thể sử dụng một biến thể ở các bước trên. Thực hiện theo các bước từ 1 đến 3, như đã lưu ý, sau đó đặt công thức mảng sau vào ô E2:

=AVERAGE(IF(ISNUMBER(Dates)ISNUMBER(Rainfall)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))

Sau đó, bạn có thể sao chép công thức cho tất cả các ngày được hiển thị trong cột D.

Kết quả của công thức này là lượng mưa trung bình thực tế, giống như đã được trình bày trong cột G trong cách tiếp cận trước đó.

Công thức này hoạt động do cách thức hoạt động của số học Boolean trong Excel. Hàm ISNUMBER trả về True hoặc False và các phép so sánh (MONTH và DAY) trả về True hoặc False. Tất cả các kết quả này đều được nhân với nhau, kết quả là True chỉ khi tất cả các thử nghiệm riêng lẻ là True. Chỉ khi tất cả đều Đúng thì mới tính được lượng mưa trung bình cho ngày cụ thể đó.

Bạn có thể giảm chi phí tính toán hơn nữa bằng cách loại bỏ tất cả bảng tính trung bình cho mỗi ngày trong năm. Với ngày tháng và lượng mưa của bạn trong cột A và B, hãy làm theo các bước sau:

  1. Chọn tất cả các ô trong cột A có chứa ngày tháng và gán cho phạm vi này tên là Ngày.

  2. Chọn tất cả các ô trong cột B có chứa dữ liệu lượng mưa và gán cho phạm vi này tên Lượng mưa.

  3. Trong ô D2, đặt ngày bạn muốn kiểm tra lượng mưa trung bình. (Năm không quan trọng; chỉ tháng và ngày được sử dụng trong phép tính.)

  4. Nhập công thức sau vào ô E2:

=AVERAGE(IF(ISNUMBER(Dates)ISNUMBER(Rainfall)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))

Đó là nó. Bây giờ, bạn có thể thay đổi ngày trong ô D2 như mong muốn và ô E2 sẽ luôn cho biết lượng mưa trung bình cho ngày đó. Công thức trong ô E2 giống với công thức được sử dụng trong cách tiếp cận cuối cùng; sự khác biệt là bạn không tính toán nó cho tất cả các ngày trong năm và do đó việc tính toán được thực hiện nhanh hơn nhiều.

Một cách tiếp cận khác liên quan đến việc sử dụng các khả năng lọc của Excel.

Tuy nhiên, trước khi có thể sử dụng chúng đúng cách, bạn phải tạo một cột chỉ hiển thị tháng và ngày cho mỗi ngày trong dữ liệu của mình. Sử dụng công thức này trong ô C2:

=MONTH(A2) & "-" & DAY(A2)

Bây giờ, hãy bật Tự động lọc (Dữ liệu | Bộ lọc | Bộ lọc Tự động) và sử dụng danh sách thả xuống ở đầu cột mới để chọn ngày bạn muốn có giá trị trung bình. Sau đó, bạn sử dụng công thức sau, được đặt trong bất kỳ ô nào bạn muốn, để hiển thị lượng mưa trung bình cho ngày đã chọn:

=SUBTOTAL(1,B:B)

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (2350) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: