Công thức mảng với một ô và nhiều ô trong Excel

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Công thức mảng với một ô và nhiều ô trong Excel

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Công thức mảng có thể trả lại kết quả trong một ô hoặc trong nhiều ô. Một công thức mảng được nhập vào trong một dải ô được gọi là công thức đa ô. Công thức mảng nằm trong một ô duy nhất được gọi là công thức ô đơn.

Có một vài hàm mảng được thiết kế để trả về các mảng đa ô, ví dụ như TRANSPOSE, TREND, FREQUENCY, LINEST, v.v.

Các hàm khác, chẳng hạn như SUM, AVERAGE, AGGREGATE, MAX, MIN, có thể tính toán biểu thức mảng khi nhập vào một ô duy nhất bằng tổ hợp phím Ctrl + Shift + Enter.

Các ví dụ sau minh hoạ cách sử dụng một công thức mảng một ô và nhiều ô trong Excel.

1. Công thức mảng với ô đơn trong Excel

Giả sử bạn có hai cột liệt kê số lượng mặt hàng được bán trong 2 tháng khác nhau, ví dụ cột B và cột C, và bạn muốn tìm số doanh thu tăng lớn nhất có thể.

Thông thường, bạn sẽ thêm một cột bổ sung, ví dụ cột D, tính doanh thu thay đổi cho mỗi sản phẩm, sử dụng công thức như = C2-B2, và sau đó tìm giá trị lớn nhất trong cột bổ sung đó = MAX (D: D).

Tuy nhiên, một công thức mảng trong Excel không cần một cột bổ sung vì nó lưu trữ hoàn hảo tất cả các kết quả trung gian trong bộ nhớ. Vì vậy, bạn chỉ cần nhập công thức sau và nhấn Ctrl + Shift + Enter mà thôi:

= MAX (C2: C6-B2: B6)

Công thức mảng với ô đơn trong Excel

Công thức mảng với ô đơn trong Excel

2. Công thức mảng đa ô trong Excel

Trong ví dụ trước của hàm SUM, giả sử bạn phải trả 10% thuế cho mỗi lần bán và bạn muốn tính số tiền trả thuế cho mỗi sản phẩm chỉ với một công thức.

Trước tiên, bạn cần chọn dải ô trong một cột trống, ví như D2: D6, và nhập công thức sau vào thanh công thức:

= B2: B6 * C2: C6 * 0,1

Một khi bạn nhấn Ctrl + Shift + Enter, thì Excel sẽ đặt công thức mảng của bạn trong mỗi ô trong dải đã chọn, và bạn sẽ nhận được kết quả trả về như sau:

3. Sử dụng một hàm mảng Excel để trả về mảng nhiều ô

Như đã đề cập, thì Microsoft Excel cung cấp một vài cái gọi là “các hàm mảng” được thiết kế đặc biệt để làm việc với các mảng đa ô. Hàm TRANSPOSE là một trong những hàm như vậy và chúng tôi sẽ sử dụng nó để chuyển đổi vị trí trong bảng ở trên, tức là chuyển hàng sang cột và ngược lại.

  1. Chọn một dải các ô trống mà bạn muốn xuất ra bảng sau khi chuyển đổi. Vì chúng ta đang chuyển đổi các hàng thành các cột, nên hãy chắc chắn rằng bạn chọn cùng một số hàng và cột như số các cột và các hàng tương ứng trong bảng nguồn (bảng gốc) bạn có. Trong ví dụ này, chúng tôi đang lựa chọn 6 cột và 4 hàng.
  2. Nhấn F2 để vào chế độ chỉnh sửa.
  3. Nhập công thức mảng: = TRANSPOSE (array) và nhấn Ctrl + Shift + Enter. Trong ví dụ trên, công thức là = TRANSPOSE ($A$1: $D$6).

Kết quả sẽ tương tự như sau:

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ