Hướng dẫn cách sử dụng công thức mảng trong Excel

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

Hướng dẫn cách sử dụng công thức mảng 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

Trong bài viết này, chúng tôi sẽ hướng dẫn sử dụng công thức mảng trong Excel, cách để nhập nó chính xác trong bảng tính của bạn, cách sử dụng các hằng số và các công thức mảng trong Excel.

1. Công thức mảng trong Excel

Trước khi chúng ta bắt đầu tìm hiểu về hàm và các công thức mảng trong Excel, thì chúng ta hãy tìm hiểu rằng thế nào là thuật ngữ “mảng”. Về cơ bản, một mảng trong Excel là một tập hợp các mục. Các mục có thể là văn bản hoặc số và chúng có thể nằm trong một hàng/cột, hoặc trong nhiều hàng và cột.

Ví dụ: nếu bạn nhập danh sách mua hàng hàng tuần của bạn vào một định dạng mảng Excel, thì nó sẽ kiểu như:

{“Milk”, “Eggs”, “Butter”, “Corn flakes”}

Sau đó, nếu bạn chọn các ô từ A1 đến D1, thì hãy nhập mảng ở trên với dấu bằng (=) trên thanh công thức và nhấn CTRL + SHIFT + ENTER, và bạn sẽ nhận được kết quả như sau:

Công thức mảng trong Excel

Công thức mảng trong Excel

Những gì bạn vừa làm là tạo một mảng một chiều theo chiều ngang. Nãy giờ mọi thứ vẫn rất đơn giản, phải không nào?

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

Sự khác biệt giữa một hàm mảng và các hàm Excel thông thường khác là hàm mảng sẽ xử lý nhiều thay vì chỉ một giá trị mà thôi. Nói cách khác, một công thức mảng trong Excel sẽ đánh giá tất cả các giá trị riêng lẻ trong một mảng, và thực hiện nhiều phép tính trên một hoặc nhiều mục mà có thỏa các điều kiện được biểu thị trong công thức.

Một công thức mảng không chỉ xử lý đồng thời một số giá trị, mà nó cũng có thể cùng 1 lúc trả lại một số các giá trị. Vì thế, các kết quả trả lại bởi công thức mảng cũng là một mảng.

Công thức mảng có sẵn trong tất cả các phiên bản của Excel 2013, Excel 2010, Excel 2007 và trước đó nữa.

Và bây giờ, có vẻ như đã đến lúc bạn nên tạo công thức mảng đầu tiên cho bạn.

1.2. Ví dụ đơn giản của công thức mảng trong Excel

Giả sử bạn có một số mục trong cột B, và cột C biểu thị giá của chúng, bài toán của bạn là tính tổng doanh thu.

Tất nhiên, không có gì ngăn cản bạn tính các tổng phụ (Sub total) trong mỗi hàng với một công thức đơn giản như = B2 * C2, và rồi sau đó cộng các giá trị đó lại với nhau:

Tuy nhiên, một công thức mảng có thể mang đến cho bạn những điểm đột phá quan trọng, từ khi nó được Excel dùng trong lưu trữ kết quả trung gian trong bộ nhớ, thay vì phải tạo thêm cột bổ sung. Vì vậy, một công thức mảng đơn chỉ cần thực hiện 2 bước nhanh chóng nho nhỏ như sau để giải quyết bài toán trên:

  1. Chọn một ô trống và nhập vào công thức sau:

= SUM (B2: B6 * C2: C6)

  1. Nhấn phím tắt CTRL + SHIFT + ENTER để hoàn tất công thức mảng.

Khi nhấn tổ hợp phím trên, Microsoft Excel đặt công thức trên trong {dấu ngoặc nhọn}, hay được gọi một dấu hiệu dễ nhìn thấy cho biết đó là một công thức mảng.

Công thức sẽ nhân giá trị trong mỗi hàng riêng của mảng được chỉ định (các ô từ B2 đến C6), có thêm các tổng (phụ), và kết quả cuối cùng là tổng doanh thu.

Ví dụ đơn giản trên cho thấy sức mạnh của công thức mảng trong Excel. Khi làm việc với hàng trăm hoặc hàng ngàn hàng chứa dữ liệu, thì bạn có thể cân nhắc khi nó sẽ tiết kiệm bao nhiêu thời gian với một công thức mảng trong mỗi ô.

2. Tại sao phải sử dụng công thức mảng trong Excel?

Công thức mảng trong Excel là công cụ tiện dụng nhất để thực hiện các phép tính tinh vi và các tác vụ phức tạp. Một công thức mảng đơn có thể thay thế hàng trăm công thức thông thường. Công thức mảng có thể sử dụng cho các công việc như:

  • Tính tổng các số thỏa các điều kiện đã cho, ví dụ tính tổng N giá trị lớn nhất hoặc nhỏ nhất trong một dải.
  • Tính tổng các giá trị các hàng, hoặc trong hàng/cột thứ N.
  • Đếm các giá trị của tất cả hoặc đếm những ký tự nhất định trong một dải xác định.

3. Nhập công thức mảng trong Excel (ctrl + shift + enter)

Như bạn đã biết, sử dụng tổ hợp phím CTRL + SHIFT + ENTER là một phương thức kỳ diệu để biến một công thức thông thường thành một công thức mảng.

Khi nhập một công thức mảng trong Excel, có 4 điều quan trọng mà bạn cần ghi nhớ:

  1. Một khi bạn đã hoàn thành gõ công thức và đồng thời nhấn các phím CTRL + SHIFT+ ENTER, thì Excel sẽ tự động đặt công thức đó trong {dấu ngoặc nhọn}. Khi bạn chọn một/các ô như vậy, bạn có thể thấy các dấu ngoặc nhọn trong thanh công thức, điều này cho bạn biết rằng có một công thức mảng trong đó.
  2. Việc nhập các dấu ngoặc nhọn bao quanh công thức bằng tay sẽ không chuyển công thức trên sang định dạng mảng. Bạn phải nhấn tổ hợp phím Ctrl + Shift + Enter để hoàn thành công thức mảng.
  3. Mỗi lần bạn chỉnh sửa một công thức mảng, các dấu ngoặc nhọn sẽ biến mất và bạn phải nhấn Ctrl + Shift + Enter để lưu các thay đổi trong công thức của bạn.
  4. Nếu bạn quên nhấn Ctrl + Shift + Enter, thì công thức của bạn sẽ hoạt động như một công thức Excel thông thường và chỉ xử lý các giá trị đầu tiên trong mảng được chỉ định.

Bởi vì tất cả các công thức mảng trong Excel đều đòi hỏi phải nhấn tổ hợp phím Ctrl + Shift + Enter, đôi khi được gọi là công thức CSE.

4. Sử dụng phím f9 để đánh giá các phần trong một công thức mảng trong Excel

Khi làm việc với các công thức mảng trong Excel, bạn có thể quan sát cách chúng tính toán và lưu trữ các mục của mảng (mảng nội bộ) để hiển thị kết quả cuối cùng trong một ô. Để làm điều này, hãy chọn một hoặc nhiều đối số bên trong ngoặc đơn của một hàm, rồi sau đó nhấn phím F9. Để tắt chế độ đánh giá công thức, hãy nhấn phím Esc.

Trong ví dụ trên, để xem các tổng phụ (sub-total) của tất cả các sản phẩm, bạn cần chọn B2: B6 * C2: C6, rồi nhấn F9 và thu được kết quả như sau:

Lưu ý: Hãy lưu ý rằng bạn phải chọn một số phần của công thức trước khi nhấn phím F9, nếu không khi nhấn F9 thì nó sẽ chỉ đơn giản thay thế công thức mảng của bạn bằng (các) giá trị tính toán mà thôi.

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Đ