Cách sử dụng công thức mảng trong Excel

Công thức mảng là một phần quan trọng của bộ công cụ giúp Excel trở nên linh hoạt. Tuy nhiên, những biểu thức này có thể gây khó khăn cho người mới bắt đầu.

Mặc dù thoạt nhìn chúng có vẻ phức tạp, nhưng những điều cơ bản của chúng rất dễ nắm bắt. Việc học mảng rất hữu ích vì các công thức này giúp đơn giản hóa việc quản lý dữ liệu Excel phức tạp.

Công thức mảng trong Excel là gì?

Mảng là bất kỳ tập hợp các ô nào trong một cột, hàng hoặc tổ hợp được tập hợp lại với nhau thành một nhóm.

Trong Microsoft Excel, thuật ngữ công thức mảng dùng để chỉ một họ công thức thực hiện một hoặc nhiều thao tác trên một dải ô như vậy, tất cả cùng một lúc, thay vì từng ô một.

Các phiên bản Excel trước yêu cầu người dùng nhấn Ctrl + Shift + Enter để tạo hàm mảng, dẫn đến tên hàm CSE (Ctrl, Shift, Escape), mặc dù điều này không còn đúng với Excel 365.

Một ví dụ có thể là một hàm lấy tổng của tất cả doanh số bán hàng trên 100 đô la vào một ngày nhất định. Hoặc để xác định độ dài (bằng chữ số) của năm số khác nhau được lưu trong năm ô.

Công thức mảng cũng có thể là cách hoàn hảo để lấy dữ liệu mẫu từ bộ dữ liệu lớn. Họ có thể sử dụng các đối số bao gồm một phạm vi ô từ một cột, một phạm vi ô từ một hàng hoặc các ô bao trùm nhiều hàng và cột.

Ngoài ra, câu lệnh điều kiện có thể được áp dụng khi kéo dữ liệu vào một công thức mảng, chẳng hạn như chỉ kéo các số lên trên hoặc dưới một giá trị cụ thể hoặc kéo mọi giá trị thứ n.

Kiểm soát chi tiết này cho phép bạn đảm bảo rằng bạn đang lấy chính xác tập hợp con dữ liệu của mình, lọc ra các giá trị ô không mong muốn hoặc lấy một mẫu mục ngẫu nhiên cho mục đích thử nghiệm.

Cách xác định và tạo một mảng trong Excel

Các công thức mảng, ở mức cơ bản nhất, rất dễ tạo. Ví dụ đơn giản, chúng ta có thể xem xét hóa đơn cho đơn đặt hàng phụ tùng của khách hàng với thông tin chi tiết đơn hàng sau: SKU sản phẩm (Đơn vị giữ hàng), số lượng đã mua, trọng lượng tích lũy của các sản phẩm đã mua và giá cho mỗi đơn vị đã mua.

Để hoàn thành hóa đơn, bạn phải thêm ba cột vào cuối bảng bên dưới, một cột cho tổng phụ của các bộ phận, một cột cho chi phí vận chuyển và cột thứ ba cho tổng giá của từng chi tiết đơn hàng.

Bạn có thể sử dụng một công thức đơn giản để tính toán từng mục hàng một cách độc lập. Nhưng nếu hóa đơn dành cho nhiều mục hàng, nó có thể nhanh chóng trở nên phức tạp hơn nhiều. Vì vậy, thay vào đó, “Tổng phụ” có thể được tính bằng một công thức duy nhất được đặt trong ô E4:

=B4:B8 * D4:D8

Cả hai số chúng ta đang nhân đều là các dãy ô chứ không phải các ô đơn lẻ. Trong mỗi hàng, công thức lấy các giá trị ô thích hợp từ các mảng và đặt kết quả vào đúng ô mà không cần nhập thêm từ người dùng.

Nếu chúng ta giả định mức phí vận chuyển cố định là $1,50 mỗi pound, thì chúng ta có thể tính toán cột vận chuyển của mình bằng một công thức tương tự được đặt trong ô F4:

=C4:C8 * B11

Lần này, chúng tôi chỉ sử dụng một mảng ở phía bên trái của toán tử nhân. Các kết quả vẫn được nhập tự động nhưng lần này mỗi kết quả được nhân với một giá trị tĩnh.

Cuối cùng, chúng ta có thể sử dụng cùng một loại công thức mà chúng ta đã sử dụng cho Tổng phụ để tính tổng bằng cách thêm các mảng Tổng phụ và Chi phí vận chuyển vào ô G4:

=E4:E8 + F4:F8

Một lần nữa, một công thức mảng đơn giản đã biến năm công thức thành một.

Trong tương lai, việc thêm các phép tính thuế vào bảng tính này sẽ chỉ yêu cầu thay đổi một công thức duy nhất thay vì thay đổi từng mục hàng.

Quản lý điều kiện trong mảng Excel

Ví dụ trước sử dụng các công thức số học cơ bản trên các mảng để tạo ra kết quả, nhưng phép toán đơn giản sẽ không còn hiệu quả đối với các tình huống phức tạp hơn.

Ví dụ: giả sử công ty tạo hóa đơn trong ví dụ trước đã thay đổi thành một hãng vận chuyển hàng hóa khác. Trong trường hợp đó, phí vận chuyển tiêu chuẩn có thể giảm giá nhưng có thể tính phí đối với các đồ vật vượt quá một trọng lượng nhất định.

Đối với mức giá vận chuyển mới, vận chuyển tiêu chuẩn sẽ là $1,00 mỗi poundvà vận chuyển trọng lượng cao hơn sẽ được $1,75 mỗi pound. Vận chuyển trọng lượng cao áp dụng cho bất cứ thứ gì trên bảy cân.

Thay vì quay lại tính từng dòng phí vận chuyển, một công thức mảng có thể bao gồm câu lệnh điều kiện IF để xác định chi phí vận chuyển phù hợp cho từng mục hàng:

=IF(C4:C8 < 7, C4:C8*B11, C4:C8*B12)

Với một thay đổi nhanh đối với một hàm trong một ô và việc thêm phí vận chuyển mới vào bảng phí của chúng tôi, giờ đây chúng tôi có thể thấy rằng không chỉ toàn bộ cột vận chuyển được tính toán dựa trên trọng lượng, mà tổng số cũng tự động chọn giá trị mới. giá cả.

Do đó, hóa đơn được kiểm chứng trong tương lai, đảm bảo rằng bất kỳ thay đổi nào trong tương lai đối với cách tính chi phí sẽ chỉ yêu cầu thay đổi một chức năng.

Kết hợp nhiều hoặc các điều kiện khác nhau có thể cho phép chúng tôi thực hiện các hành động khác nhau. Logic này có thể được sử dụng theo nhiều cách trên cùng một mảng dữ liệu. Ví dụ,

• Có thể thêm một hàm IF bổ sung để bỏ qua vận chuyển nếu tổng phụ vượt quá một số tiền nhất định.
• Một bảng thuế có thể được thêm vào để đánh thuế các loại sản phẩm khác nhau ở các mức khác nhau.

Bất kể hóa đơn phức tạp đến mức nào, chỉ cần chỉnh sửa một ô duy nhất là có thể tính toán lại bất kỳ phần nào của hóa đơn.

Ví dụ: Tính trung bình các số thứ N

Ngoài việc làm cho việc tính toán và cập nhật các dải dữ liệu lớn trở nên đơn giản, công thức mảng có thể lấy một phần của tập dữ liệu cho mục đích thử nghiệm.

Như ví dụ sau cho thấy, việc lấy một phần dữ liệu ra khỏi tập dữ liệu lớn cho mục đích xác thực rất dễ dàng bằng cách sử dụng công thức mảng:

Trong ví dụ trên, ô D20 sử dụng một hàm đơn giản để lấy trung bình mọi kết quả thứ n từ cảm biến 1. Trong trường hợp này, chúng ta có thể xác định thứ n bằng cách sử dụng giá trị trong ô D19, cho phép chúng ta kiểm soát cỡ mẫu xác định giá trị trung bình:

=AVERAGE(IF(MOD(ROW(B2:B16)-2,D19)=0,B2:B16,""))

Điều này cho phép bạn nhanh chóng và dễ dàng phân đoạn một tập dữ liệu lớn thành các tập hợp con.

Nắm vững các công thức mảng trong Excel là điều cần thiết

Những ví dụ đơn giản này là điểm khởi đầu tốt để nắm bắt logic đằng sau công thức mảng. Hãy suy nghĩ kỹ về chúng để bạn có thể thực hiện nhiều thao tác một cách nhanh chóng ngay hôm nay và trong tương lai bằng cách điều chỉnh chỉ một vài tham chiếu ô và chức năng. Công thức mảng là một cách nhẹ nhàng và hiệu quả để chia nhỏ dữ liệu. Nắm vững chúng khi bắt đầu hành trình học Excel để bạn có thể thực hiện các phép tính thống kê nâng cao một cách tự tin.