Những vấn đề liên quan đến hằng số 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

Những vấn đề liên quan đến hằng số 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 Microsoft Excel, một hằng số mảng chỉ đơn giản là một tập các giá trị cố định. Các giá trị này không bao giờ thay đổi khi bạn sao chép công thức vào các ô hoặc các giá trị khác.

1. Các loại hằng số mảng

Tồn tại 3 loại của hằng số mảng trong Excel:

1.1. Hằng số mảng ngang

Hằng số của mảng ngang được đặt chỉ trong một hàng. Để tạo một hằng số mảng hàng, hãy nhập các giá trị được cách nhau bởi dấu phẩy, và đặt chúng trong dấu ngoặc nhọn, ví dụ: {1,2,3,4}

Lưu ý. Khi tạo một hằng số mảng, bạn phải nhập bằng tay các dấu đóng và mở ngoặc. Để nhập một mảng theo chiều ngang trong một bảng tính, hãy chọn số tương ứng của các ô trống trong một hàng, và nhập công thức = {1,2,3,4} trong thanh công thức, rồi kế tiếp nhấn Ctrl + Shift + Enter. Kết quả sẽ giống như sau:

Hằng số mảng ngang

Hằng số mảng ngang

Như bạn thấy trong hình, thì Microsoft Excel đặt hằng số mảng trong một tập hợp các dấu ngoặc nhọn khác, giống như khi bạn đang nhập một công thức mảng trong Excel.

1.2. Hằng số mảng dọc

Hằng số của mảng dọc được đặt trong một cột. Bạn tạo ra nó bằng cách tương tự như một mảng ngang, với sự khác biệt duy nhất là bạn ngăn cách các mục bằng dấu chấm phẩy, ví dụ: = {11; 22; 33; 44}

1.3. Hằng số mảng hai chiều

Để tạo một mảng hai chiều trong Excel, bạn tách các mục trong mỗi hàng ra bằng dấu phẩy, và ngăn cách mỗi cột bằng dấu chấm phẩy = {“a”, “b”, “c”; 1, 2, 3}

2. Cách sử dụng hằng số mảng trong Excel

Hằng số mảng là một trong những nền tảng của các công thức mảng trong Excel. Các thông tin và mẹo nhỏ dưới đây có thể giúp bạn sử dụng chúng một cách hiệu quả nhất.

2.1. Các thành phần của một hằng số mảng

Hằng số của mảng có thể chứa các con số, giá trị văn bản, giá trị Booleans (TRUE và FALSE) và các giá trị lỗi, chúng được ngăn tách bằng dấu phẩy hoặc dấu chấm phẩy.

Bạn có thể nhập một giá trị số dưới dạng số nguyên, thập phân, hoặc ký hiệu khoa học. Nếu bạn sử dụng các giá trị văn bản, chúng sẽ được đặt trong dấu nháy kép (“”) như trong bất kỳ công thức Excel nào.

Hằng số mảng không thể bao gồm các mảng, tham chiếu ô, dải, ngày tháng, tên xác định, công thức, hoặc các hàm trong Excel khác.

2.2. Hằng số mảng được đặt tên

Để làm cho mảng trở nên dễ sử dụng hơn, hãy đặt cho nó một cái tên:

  • Đi đến tab Formulas > nhóm Defined Names và nhấn Define Name. Hoặc là nhấn Ctrl + F3 và nhấp vào
  • Nhập tên bạn muốn đặt vào ô Name
  • Trong hộp Refers to, nhập các mục của mảng, mà được đặt trong dấu ngoặc nhọn, với dấu bằng ở trước (=). Ví dụ:

= {“Su”, “Mo”, “Tu”, “We”, “Th”, “Fr”, “Sa”}\

  • Nhấn OK để lưu mảng đã đặt tên và đóng cửa sổ.

Để nhập hằng số của mảng đã được đặt tên trong một trang tính, hãy chọn nhiều ô trong một hàng hoặc cột là các mục trong mảng của bạn, rồi nhập tên của mảng vào thanh công thức với dấu = đằng trước và nhấn Ctrl + Shift + Enter.

Kết quả đưa ra như sau:

2.3. Ngăn chặn lỗi

Nếu mảng của bạn không hoạt động được, hãy kiểm tra các vấn đề sau:

  • Ngăn cách các thành phần của hằng số mảng với các ký tự thích hợp – dấu phẩy với hằng số của mảng ngang và dấu chấm phẩy với các mảng dọc.
  • Chọn một dải có các ô đúng với số lượng các mục trong hằng số mảng của bạn. Nếu bạn chọn nhiều ô hơn, thì mỗi ô phụ sẽ có lỗi # N / A. Nếu ít hơn, thì chỉ một phần của mảng sẽ được xuất ra.

3. Sử dụng hằng số mảng trong công thức của Excel

Bây giờ bạn đã quen thuộc với khái niệm các hằng số của mảng, nên chúng ta hãy cùng xem cách mà bạn có thể sử dụng các mảng trong các công thức của Excel để giải quyết các thực tế công việc của bạn.

3.1. Cộng n các số lớn nhất / nhỏ nhất trong một dải

Bạn bắt đầu bằng cách tạo một mảng dọc có chứa những số bạn muốn tính tổng. Ví dụ: nếu bạn muốn cộng 3 số nhỏ nhất hoặc lớn nhất trong một dải, thì hằng số mảng sẽ là {1,2,3}.

Sau đó, bạn sử dụng một trong hai hàm công thức sau LARGE hoặc SMALL, xác định toàn bộ dải của ô trong tham số đầu tiên và đặt hằng số mảng trong tham số thứ hai. Cuối cùng, lồng nó vào hàm SUM, như sau:

Tính tổng 3 số lớn nhất: = SUM (LARGE (range, {1,2,3}))

Tính tổng nhỏ nhất 3 số: = SUM (SMALL (range, {1,2,3}))

Đừng quên nhấn Ctrl + Shift + Enter khi bạn nhập công thức mảng, và bạn sẽ nhận được kết quả như sau:

Tương tự như vậy, bạn có thể tính giá trị trung bình của N giá trị nhỏ nhất hoặc lớn nhất trong một dải:

Trung bình của 3 số lớn nhất: = AVERAGE (LARGE (range, {1,2,3}))

Trung bình của 3 số nhỏ nhất: = AVERAGE (SMALL (range, {1,2,3}))

3.2. Công thức mảng để tính các ô thỏa nhiều điều kiện

Giả sử bạn có một danh sách đơn đặt hàng và bạn muốn biết người bán hàng đã bán được bao nhiêu lần sản phẩm được chỉ định.

Cách đơn giản nhất sẽ sử dụng một công thức COUNTIFS với nhiều điều kiện. Tuy nhiên, nếu bạn muốn đưa nhiều sản phẩm vào, thì công thức COUNTIFS của bạn có thể sẽ quá dài. Để làm cho nó nhỏ gọn hơn, bạn có thể sử dụng COUNTIFS cùng với SUM và bao gồm một hằng số mảng trong một hoặc một vài đối số, ví dụ:

= SUM (COUNTIFS (range1, “criteria1”, range2, {“criteria1”, “criteria2”})))

Công thức thực có thể trông như sau:

= SUM (COUNTIFS (B2: B9, “sally”, C2: C9, {“Apples”, “lemons”}))

Trong mảng ví dụ của chúng tôi có bao gồm chỉ có hai yếu tố với mục tiêu là để đưa cho bạn phương pháp tiếp cận. Thực tế, công thức mảng của bạn, có thể bao gồm nhiều yếu tố như phép ràng buộc logic, với yêu cầu chiều dài của công thức không vượt quá 8,192 ký tự trong Excel 2013, 2010 và 2007 (1,024 ký tự trong Excel 2003 trở về trước) và máy tính của bạn đủ mạnh để xử lý mảng lớn.

Chúc bạn thực hiện thành công.

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Đ