Như bạn đã biết, hàm AVERAGEIFS không hoạt động trong một ArrayFormula trong Google Sheets để trả về trung bình có điều kiện của các giá trị mở rộng. Giải pháp cho vấn đề này chính là MMULT.
Phương pháp sử dụng MMULT không mới với độc giả của tôi. Tôi đã sử dụng MMULT để tính trung bình qua các hàng (kết quả mở rộng mà không cần kéo và thả). Nhưng đó không chứa việc sử dụng điều kiện.
Bạn quan tâm? Dưới đây là cách sử dụng MMULT như một phương thức thay thế cho công thức ArrayFormula của AVERAGEIFS trong Google Sheets.
Để hiểu các hàm được sử dụng trong việc viết các công thức dưới đây, hãy xem hướng dẫn sử dụng các hàm Google Sheets của tôi.
Lưu ý:
Công thức mà tôi sẽ cung cấp trên trang này có thể không hoạt động trong tập dữ liệu lớn vì MMULT có các hạn chế.
Mong đợi của tôi từ kết quả của AVERAGEIFS ArrayFormula
Trước tiên, tôi sẽ cung cấp dữ liệu mẫu và các công thức AVERAGEIFS không-array hoạt động. Điều này giúp bạn hiểu rõ kết quả tôi mong đợi.
Sau đó, chúng ta có thể đi vào cách chuyển đổi các công thức AVERAGEIFS không-array đó thành công thức AVERAGEIFS có-array bằng cách sử dụng MMULT.
Cú pháp của AVERAGEIFS (cho tham khảo):
AVERAGEIFS(phạm vi_trung_bình; phạm vi_điều_kiện1; điều_kiện1; [phạm vi_điều_kiện2;…]; [điều_kiện2;…])
Dữ liệu mẫu và giải thích
Đây là Dữ liệu mẫu của tôi mà tôi sẽ sử dụng để tính toán trung bình dựa trên các điều kiện (trong một phạm vi ngày).
Hãy hiểu dữ liệu mẫu trên một cách giới hạn.
Dữ liệu mẫu của tôi liên quan đến việc đặt phòng khách sạn. Các cột A và B chứa các ngày đến và ngày đi (ngày đặt phòng) của khách.
Cột C chứa sự khác biệt giữa các ngày, có nghĩa là số đêm đã đặt.
Tôi đã sử dụng công thức mảng dưới đây trong ô C2 để tính toán sự khác biệt về ngày, tức là số đêm đã đặt.
=ArrayFormula(DAYS(B2:B13,A2:A13))
Hãy hiểu rằng công thức DAYS trên không liên quan gì đến công thức AVERAGEIFS mảng trong Google Sheets của chúng ta.
Cột tiếp theo là D, dành cho việc nhập số lượng phòng đã đặt.
Cột E chứa thông tin doanh thu đã thu được.
Sử dụng các chi tiết có sẵn trên (đầu vào), tôi đã tính toán tỷ lệ trung bình hàng ngày (ADR) trong cột F.
Bạn có thể tìm kiếm trên Google để tìm hiểu thêm chi tiết về ADR. Nhưng đây là cách tính ADR trong Google Sheets.
Một lần nữa, tất cả những điều này đều là một phần của dữ liệu mẫu của chúng ta. Tôi chưa bắt đầu phần công thức AVERAGEIFS.
Tính toán ADR trong Google Sheets
Tỷ lệ trung bình hàng ngày (ADR) = Doanh thu Phòng / Số Phòng Bán.
Theo dữ liệu mẫu của chúng ta, ADR có dạng:
Doanh thu Phòng (Cột E) / Số Phòng (Cột D)
Vì doanh thu của chúng ta bao gồm nhiều đêm (không chỉ một ngày duy nhất), chúng ta phải sử dụng công thức ADR như sau.
ADR = Doanh thu Phòng (Cột E) / Số Phòng (Cột D) / Tổng số đêm (Cột C)
Đây là công thức ADR được sử dụng trong ô F3, một lần nữa là một công thức mảng.
=ArrayFormula(E2:E13/D2:D13/C2:C13)
Chủ đề chính của chúng ta bắt đầu dưới đây.
Tiêu chí cho việc tính AVERAGEIFS và công thức không-array
Các tiêu chí nằm trong cột H và công thức AVERAGEIFS không-array nằm trong ô I2:I9. Công thức dưới đây trong ô I2 được sao chép và dán vào ô I3:I9.
=iferror(averageifs($F$2:$F$13,$A$2:$A$13,"<="&H2,$B$2:$B$13,">"&H2))
Công thức trên ô I2 làm gì?
Nếu ngày đến nhỏ hơn hoặc bằng ngày trong ô H2 và ngày đi lớn hơn ngày trong ô H2, công thức sẽ tìm trung bình các giá trị tương ứng từ cột F (ADR).
Điều đó có nghĩa là tìm trung bình ADR cho đến ngày được chỉ định trong tiêu chí.
Thực sự, bạn không cần quá quan tâm đến các tiêu chí hoặc dữ liệu mẫu. Hãy chỉ nhìn vào công thức AVERAGEIFS. Đó là đủ để chúng ta tiếp tục.
Hãy xem cách thay thế các công thức đó (I2:I9) bằng một AVERAGEIFS ArrayFormula sử dụng MMULT trong Google Sheets.
Hãy đi vào công thức ngay bây giờ.
MMULT như là AVERAGEIFS
Công thức AVERAGEIFS chung:
AVERAGEIFS (ArrayFormula) = SUMIFS (ArrayFormula) / COUNTIFS (ArrayFormula)
Trước tiên, chúng ta hãy thực hiện phần Sumifs bằng cách sử dụng MMULT.
Xóa các công thức AVERAGEIFS không-array khỏi I2:I9. Sau đó, chèn công thức Sumifs sử dụng MMULT dưới đây vào ô I2.
=ArrayFormula(iferror(IF(LEN(H2:H),(MMULT(((TRANSPOSE(A2:A)<=H2:H)*(TRANSPOSE(B2:B)>H2:H)),N(F2:F))))))
Tiêu chí: Các ngày từ dải H2:H.
Công thức kiểm tra ngày ‘Đến’ (A2:A) <= ngày trong tiêu chí và ngày ‘Đi’ > ngày trong tiêu chí.
Chúng ta có thể chuyển đổi SUMIFS trên thành COUNTIFS.
Đơn giản thay thế cột tổng N(F2:F) trong công thức trên bằng Row(A2:A)^0 như dưới đây.
Chèn vào ô J2.
=ArrayFormula(iferror(IF(LEN(H2:H),(MMULT(((TRANSPOSE(A2:A)<=H2:H)*(TRANSPOSE(B2:B)>H2:H)),Row(A2:A)^0)))))
Dựa trên công thức chung, AVERAGEIFS = SUMIFS / COUNTIFS, đây là AVERAGEIFS ArrayFormula để sử dụng trong một phạm vi ngày trong Google Sheets.
=ArrayFormula(iferror(IF(LEN(H2:H),(MMULT(((TRANSPOSE(A2:A)<=H2:H)*(TRANSPOSE(B2:B)>H2:H)),N(F2:F)))/(MMULT(((TRANSPOSE(A2:A)<=H2:H)*(TRANSPOSE(B2:B)>H2:H)),Row(A2:A)^0)),),0))
Tôi đã kết hợp các công thức Sumifs và Countifs. Cắt công thức J2 và kết hợp (bằng cách đặt toán tử chia) với công thức I2.
Lợi ích của AVERAGEIFS ArrayFormula trong Google Sheets
Tôi đã đề cập đến giới hạn của công thức mảng này ở đầu (như một lời từ chối) của bài viết này. Bây giờ chúng ta hãy nói về những lợi ích.
-
Chỉ cần chèn công thức vào ô I2, nó sẽ mở rộng thành I2:I9. Khi bạn thêm nhiều tiêu chí vào cột H, nó sẽ tự động mở rộng.
-
Khi bạn chèn thêm các hàng mới giữa phạm vi hiện có, bạn phải sao chép và dán công thức không-array. Vì công thức AVERAGEIFS ArrayFormula của chúng ta dựa trên MMULT nên không cần sao chép và dán.
Đó là tất cả. Chúc bạn thành công!
