Kết hợp các hàm MEDIAN và IF trong một công thức mảng
Ví dụ hướng dẫn này sử dụng công thức mảng MEDIAN IF để tìm giá thầu trung gian cho hai dự án khác nhau.
Bản chất của công thức cho phép chúng tôi tìm kiếm nhiều kết quả đơn giản bằng cách thay đổi tiêu chí tìm kiếm - trong trường hợp này là tên dự án.
Công việc của từng phần của công thức là:
- Hàm MEDIAN tìm giá trị trung bình của một dự án
- Hàm IF cho phép chúng ta chọn dự án nào chúng ta muốn đấu thầu bằng cách thiết lập một điều kiện bằng cách sử dụng tên dự án
- Công thức mảng cho phép hàm IF kiểm tra nhiều điều kiện trong một ô duy nhất, và khi điều kiện được đáp ứng, công thức mảng xác định dữ liệu nào (giá thầu dự án) mà hàm MEDIAN sẽ kiểm tra để tìm giá thầu trung bình
Công thức CSE
Công thức mảng được tạo bằng cách nhấn các phím Ctrl , Shift và Enter trên bàn phím cùng lúc khi công thức đã được nhập.
Vì các phím được nhấn để tạo công thức mảng, đôi khi chúng được gọi là công thức CSE .
MEDIAN IF Cú pháp và đối số công thức lồng nhau
Cú pháp cho công thức MEDIAN IF là:
& # 61; MEDIAN (IF (logical_test, value_if_true, value_if_false))
- Vì hàm IF được lồng trong hàm MEDIAN, toàn bộ hàm IF trở thành đối số duy nhất cho hàm MEDIAN
Các đối số cho hàm IF là:
- logical_test - (bắt buộc) một giá trị hoặc biểu thức được kiểm tra xem nó có đúng hay sai
- value_if_true - (bắt buộc) giá trị được hiển thị nếu logical_test là true
- value_if_false - (tùy chọn) giá trị được hiển thị nếu logical_test là false
Ví dụ về công thức mảng MEDIAN IF Array của Excel
Như đã đề cập, ví dụ về tìm kiếm hồ sơ dự thầu cho hai dự án khác nhau để tìm giá thầu trung bình hoặc trung bình. Các đối số cho hàm IF thực hiện điều này bằng cách thiết lập các điều kiện và kết quả sau:
- kiểm tra logic cố gắng tìm một kết quả phù hợp cho tên dự án đã gõ vào ô D10 của trang tính
- Đối số value_if_true sẽ có, với sự trợ giúp của hàm MEDIAN, giá thầu trung bình cho dự án đã chọn
- Đối số value_if_false sẽ bị bỏ qua vì nó không cần thiết và sự vắng mặt của nó sẽ rút ngắn công thức. Nếu tên dự án không có trong bảng dữ liệu - chẳng hạn như dự án C - được nhập vào ô D10 thì công thức sẽ trả về một số không (0)
Nhập dữ liệu hướng dẫn
- Nhập dữ liệu sau vào ô D1 đến E9 như trong hình trên: Dự án đấu thầu Dự án đấu thầu Dự án $ 15,785 Dự án $ 15,365 Dự án $ 16,472 B $ 24,365 Dự án B $ 24,612 Dự án B $ 23,999 Dự án Trung thầu
- Trong ô D10 loại "Dự án A" (không có dấu ngoặc kép). Công thức sẽ tìm trong ô này để tìm dự án nào khớp.
Nhập MEDIAN IF Nested Formula
Vì chúng ta đang tạo cả một công thức lồng nhau và một công thức mảng, chúng ta sẽ cần phải nhập toàn bộ công thức vào một ô trang tính đơn lẻ.
Khi bạn đã nhập công thức KHÔNG nhấn phím Enter trên bàn phím hoặc nhấp vào một ô khác bằng chuột khi chúng ta cần chuyển công thức thành công thức mảng.
- Nhấp vào ô E10 - vị trí nơi kết quả công thức sẽ được hiển thị
- Gõ như sau:
= MEDIAN (IF (D3: D8 = D10, E3: E8))
Tạo công thức mảng
- Nhấn và giữ các phím Ctrl và Shift trên bàn phím
- Nhấn phím Enter trên bàn phím để tạo công thức mảng
- Câu trả lời 15875 ($ 15,875 với định dạng) sẽ xuất hiện trong ô E10 vì đây là phần mềm trung gian cho Dự án A
- Công thức mảng hoàn chỉnh
{= MEDIAN (IF (D3: D8 = D10, E3: E8))}}
có thể thấy trong thanh công thức phía trên trang tính
Kiểm tra công thức
Kiểm tra công thức bằng cách tìm giá thầu trung gian cho Dự án B
Gõ Project B vào ô D10 và nhấn phím Enter trên bàn phím.
Công thức sẽ trả về giá trị 24365 ($ 24,365) trong ô E10.