Các hàm, công thức mảng trong Excel, ví dụ và hướng dẫn sử dụng

Trong nội dung này, Học Excel Online sẽ  hướng dẫn công thức mảng Excel, phương pháp để nhập nó đúng đắn trong bảng tính của các bạn, cách sử dụng các hằng số và các công thức mảng trong Excel.

Các công thức mảng trong Excel là một công cụ vô cùng hữu hiệu và cũng khó nhất để làm chủ được nó. Một công thức mảng Excel đơn có thể thực hiện nhiều phép tính và thay thế hàng ngàn công thức thông thường. Bên cạnh đó, áng chừng đến 90% người dùng Excel chưa khi nào sử dụng các công thức mảng trong bảng tính, chỉ vì họ sợ phải khởi đầu học chúng từ con số 0.

Hàm mảng trong Excel là gì?

Trước khi tất cả chúng ta khởi đầu tìm tòi về hàm và các công thức mảng Excel, thì tất cả chúng ta hãy tham khảo rằng thế nào là thuật ngữ “mảng”. Về căn 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 mục mua hàng hàng tuần của các 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ẽ thu được kết quả như sau:

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

Hiện thời, công thức mảng Excel là gì? Sự độc đáo 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ẽ giải quyết 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ận xét toàn bộ các giá trị riêng rẽ 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 chẳng những giải quyết cùng lúc một số giá trị, mà nó cũng có thể cùng một thời điểm trả lại một số các giá trị. Chính vì như 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 toàn bộ các phiên bản của Excel 2013, Excel 2010, Excel 2007 và trước đây nữa.

Và hiện giờ, có lẽ như đã tới lúc chúng ta nên tạo công thức mảng trước hết cho bạn.

Ví dụ dễ dàng của công thức mảng Excel

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

Đương 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 dễ dàng như = B2 * C2, và rồi sau đó cộng các giá trị này lại với nhau:

A usual Excel formula

Bên cạnh đó, một công thức mảng có thể đem đến cho bạn những điểm bộc phát trọng yếu, từ khi nó được Excel dùng trong lưu trữ kết quả trung gian trong bộ nhớ lưu trữ, thay vì phải tạo thêm cột bổ sung. Chính vì vậy, một công thức mảng đơn chỉ cần thực hiện 2 bước mau lẹ be bé như sau để khắc phục 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 biểu hiện ưa nhìn thấy cho biết này 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 thu nhập.

An array formula

Ví dụ dễ dàng 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ể suy xét 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 ô.

Vì sao phải sử dụng công thức mảng trong Excel?

Công thức mảng Excel là công cụ tiện dụng nhất để thực hiện các phép tính tinh xảo và các tác vụ cầu kỳ. 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 Ռ 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ứ Ռ.
  • Đếm các giá trị của toàn bộ hoặc đếm những ký tự khẳng định trong một dải xác nhận.

Xem ngay: các hàm thường dùng trong Excel

Làm sao để 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 trọng yếu mà bạn cần ghi nhớ:

  1. Một khi bạn đã giải quyết gõ công thức và cùng lúc nhấn các phím CTRL + SHIFT+ ENTER, thì Excel sẽ auto đặt công thức đó trong {dấu ngoặc nhọn}. Khi bạn chọn một/các ô như thế, 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 để giải quyết công thức mảng.
  3. Mỗi lần bạn canh chỉnh một công thức mảng, các dấu ngoặc nhọn sẽ mất tích và bạn phải nhấn Ctrl + Shift + Enter để lưu các biến đổi trong công thức của các bạn.
  4. Nếu bạn quên nhấn Ctrl + Shift + Enter, thì công thức của các bạn sẽ hoạt động như một công thức Excel thông thường và chỉ giải quyết các giá trị trước hết trong mảng được chỉ định.

Bởi vì toàn bộ 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 lúc được gọi là công thức CSE.

Sử dụng phím F9 để nhận xét các phần trong một công thức mảng:

Khi làm việc với các công thức mảng trong Excel, bạn có thể xem xé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 cơ chế nhận xét 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 toàn bộ các sản phẩm, bạn cần chọn B2: B6 * C2: C6, rồi nhấn F9 và nhận được kết quả như sau:

Use the F9 key to see how they calculate and store internal arrays.

Chú ý: Hãy chú ý 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, còn nếu không khi nhấn F9 thì nó sẽ chỉ dễ dàng thay thế công thức mảng của các bạn bằng (các) giá trị tính toán mà thôi.

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

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 kiến trúc để trả về các mảng đa ô, ví dụ như TRANSPOSE, TREND, FREQUENCY, LINEST, ?.?.

Các hàm khác, ví dụ 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.

Ví dụ 1. Một công thức mảng với ô đơn

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 β và cột ₵, và bạn mong muốn tìm số thu nhập 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 ?, tính thu nhập biến đổ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 (?: ?).

Bên cạnh đó, một công thức mảng Excel không cần một cột bổ sung vì nó lưu trữ hoàn hảo toàn bộ các kết quả trung gian trong bộ nhớ lưu trữ. Chí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)

A single-cell MAX array formula

Ví dụ 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 mong muốn tính số vốn trả thuế cho mỗi sản phẩm chỉ với một công thức.

Trước hết, 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 các bạn trong mỗi ô trong dải đã chọn, và bạn sẽ thu được kết quả trả về như sau:

A multi-cell array formula in Excel

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

Như đã đề cập, thì Microsoft Excel phân phối một vài cái gọi là “các hàm mảng” được kiến trúc đặ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ư thế và chúng tôi sẽ sử dụng nó để chuyển hóa địa điểm trong bảng ở trên, tức là chuyển hàng sang cột và trái lại.

  1. Chọn một dải các ô trống mà bạn mong muốn xuất ra bảng sau thời điểm chuyển hóa. Vì tất cả chúng ta đang chuyển hóa các hàng thành các cột, nên hãy chắc rằng 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 cơ chế canh chỉnh.
  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 ($?$1: $?$6).

Kết quả sẽ cũng giống như sau:

Using an array function in Excel

Làm sao để làm việc với các công thức mảng nhiều ô

Khi làm việc với các công thức mảng đa ô trong Excel, hãy bảo đảm tuân theo các nguyên tắc dưới đây để có được kết quả đúng đắn:

  1. Chọn dải ô mà bạn mong muốn xuất ra kết quả trước khi nhập công thức.
  2. Để xóa một công thức mảng đa ô, hoặc là bạn sẽ chọn toàn bộ các ô chứa nó và nhấn DELETE, hoặc bạn phải chọn toàn thể công thức trong thanh công thức, nhấn DELETE, và sau đó nhấn Ctrl + Shift + Enter.
  3. Bạn chẳng thể canh chỉnh hoặc di chuyển bài viết của một ô riêng rẽ trong một công thức mảng của Excel, và bạn cũng chẳng thể chèn các ô mới vào hoặc xóa các ô lúc này từ một công thức mảng đa ô. Bất kể lúc nào bạn thử làm điều này, thì Microsoft Excel sẽ đều đặt ra nhắc nhở You cannot change part of an array (“Bạn không thể thay đổi một phần của mảng”).
  4. Để thu nhỏ công thức mảng, tức là áp dụng nó cho ít ô hơn, bạn cần phải xoá công thức lúc này trước rồi mới nhập một công thức mới vào.
  5. Để mở rộng công thức mảng, ví dụ áp dụng nó cho nhiều ô hơn, bạn cần chọn toàn bộ các ô có chứa công thức lúc này cộng với các ô rỗng mà bạn mong muốn có công thức trong đó, rồi nhấn F2 để chuyển sang cơ chế canh chỉnh, để bố trí các tham chiếu trong công thức và nhấn Ctrl + Shift + Enter để update nó.
  6. Bạn chẳng thể sử dụng công thức mảng đa ô trong định dạng bảng Excel.
  7. Chúng ta nên nhập một công thức mảng đa ô trong một dải ô có cùng kích thước với mảng có kết quả được trả về. Nếu công thức mảng trong Excel của các bạn chứa một mảng mà to hơn dải bạn đã chọn, thì những giá trị vượt quá sẽ không hiện ra trên bảng tính. Nếu một mảng trả về bởi công thức nhỏ mà hơn dải đã chọn, lỗi # Ռ / ? sẽ hiện ra trong các ô bổ sung.

Nếu công thức của các bạn có thể trả về một mảng với một số lượng các phần tử có thể biến đổi, thì hãy nhập nó vào một dải bằng hoặc to hơn mảng lớn nhất mà được bởi công thức trả về và hãy lồng công thức của các bạn trong hàm IFERROR.

Hằng số mảng của Excel

Trong Microsoft Excel, một hằng số mảng chỉ dễ dàng là một tập các giá trị cố định. Các giá trị này không thay đổi khi bạn copy công thức vào các ô hoặc các giá trị khác.

Bạn đã làm một ví dụ về mảng được tạo từ một danh mục hàng hóa cần mua trong phần đầu của bài hướng dẫn này. Hiện thời, tất cả chúng ta hãy cùng xem các loại mảng khác như vậy nào và cách bạn có thể lập ra chúng.

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

1. Hằng số của 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}

Chú ý. 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 tiếp theo nhấn Ctrl + Shift + Enter. Kết quả sẽ giống như sau:

Horizontal array constant

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.

2. Hằng số của mảng dọc:

Hằng số của mảng dọc được đặt trong một cột. Bạn tạo nên nó bằng cách cũng giống như một mảng ngang, với sự độc đáo duy đặc biệ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}

Vertical array constant

3. Hằng số của 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}

Two-dimensional array constant

Làm việc với hằng số mảng của Excel

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

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 chẳng thể bao gồm các mảng, tham chiếu ô, dải, ngày tháng, tên xác nhận, công thức, hoặc các hàm trong Excel khác.

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

Để khiến cho mảng trở nên dễ sử dụng hơn, hãy đặt cho nó một tên gọi:

  • Đi đến tab Formulas > nhóm Defined Names và nhấn Define Name. Hoặc là nhấn Ctrl + F3 và nhấn vào
  • Nhập tên bạn mong 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”}

Creating a named array in Excel

  • 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 các 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ả đặt ra như sau:

Entering the named array constant in a sheet

3. Ngăn chặn lỗi

Nếu mảng của các bạn không hoạt động được, hãy kiểm soát 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ự phù 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 các bạn. Nếu bạn chọn nhiều ô hơn, thì mỗi ô phụ sẽ có lỗi # Ռ / ?. Nếu thấp hơn, thì chỉ một phần của mảng sẽ được xuất ra.

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

Hiện thời bạn đã thân thuộc với định nghĩa các hằng số của mảng, nên tất cả 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 để khắc phục các thực tiễn công việc của các bạn.

Ví dụ 1. Cộng Ռ các số lớn nhất / nhỏ nhất trong một dải

Bạn khởi đầu bằng cách tạo một mảng dọc có chứa những số bạn mong muốn tính tổng. Ví dụ: nếu bạn mong 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ận toàn thể dải của ô trong tham số trước hết 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ẽ thu được kết quả như sau:

Excel array formula to sum N largest / smallest numbers in the range

Cũng giống như vậy, bạn có thể tính giá trị bình quân của Ռ giá trị nhỏ nhất hoặc lớn nhất trong một dải:

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

Bình quân của 3 số nhỏ nhất: = AVERAGE (SMALL (range, {1,2,3}))

Tìm hiểu thêm: Học Excel văn phòng với các Chuyên Viên

Ví dụ 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 mục đơn đặt mua và bạn mong 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 dễ dàng nhất sẽ sử dụng một công thức COUNTIFS với nhiều điều kiện. Bên cạnh đó, nếu bạn mong muốn mang nhiều sản phẩm vào, thì công thức COUNTIFS của các bạn có thể sẽ quá dài. Để khiến 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”}))

An array formula to count cells with multiple conditions

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à để mang cho bạn bí quyết tiếp cận. Thực tiễn, công thức mảng của các 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 bề 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à laptop của các bạn đủ mạnh để giải quyết mảng lớn.

Các toán tử AND và OR trong công thức mảng của Excel

Một toán tử của mảng cho thấy cách bạn mong muốn giải quyết mảng – sử dụng hàm logic AND và OR.

  • Toán tử AND là dấu hoa thị (*). Nó hướng dẫn Excel trả về TRUE nếu TẤT CẢ các điều kiện được nhìn nhận là TRUE.
  • Toán tử OR là dấu cộng (+). Nó trả về TRUE nếu bất kỳ điều kiện nào trong một biểu thức đã nghĩ rằng TRUE.

Công thức mảng với toán tử AND

Trong ví dụ này, chúng tôi cho biết tổng thu nhập tương ứng mà người bán hàng là Mike và sản phẩm là Apples:

= SUM ((A2: A9 = “Mike”) * (B2: B9 = “Apples”) * (C2: C9))

Hoặc là

= SUM (IF (((A2: A9 = “Mike”) * (B2: B9 = “Apples”)), (C2: C9)))

 An array formula with the AND operator

Công thức mảng Excel với toán tử OR

Công thức mảng sau với toán tử OR (+) cho phép thêm toàn bộ các bán hàng ứng với người bán hàng là Mike hoặc sản phẩm là Apples:

= SUM (IF (((A2: A9 = “Mike”) + (B2: B9 = “Apples”)), (C2: C9)))

Excel array formula with the OR operator

Mách nhỏ: Trong các phiên bản hiện đại của Excel 2013, 2010 hoặc 2007, việc sử dụng các công thức mảng trong các tình huống như thế thực sự không thiết yếu và một công thức SUMIFS dễ dàng có thể cho ra cùng một kết quả như trên. Bên cạnh đó, toán tử AND và OR trong các công thức mảng có thể hữu hiệu trong các tình huống cầu kỳ hơn, điều này liên quan tới việc luyện tập các sáng kiến cho não của các bạn : )

Toán tử nhà cung cấp đôi điều hành trong công thức mảng của Excel

Nếu bạn đã từng làm việc với các công thức mảng trong Excel, rất có thể bạn đã lướt qua một vài công thức có chứa hai dấu gạch ngang (–) và bạn có thể đã tự hỏi rằng nó được sử dụng cho những việc gì.

Hai dấu gạch ngang đôi, gọi là toán tử nhà cung cấp đôi, được sử dụng để chuyển các giá trị Boolean không bằng số (TRUE / FALSE) bởi một số biểu thức thành 1 và 0 – thứ mà một hàm mảng có thể hiểu được.

Ví dụ sau ao ước khiến cho mọi thứ dễ hiểu hơn cho bạn. Giả sử bạn có một danh mục ngày trong cột ? và bạn mong muốn biết có bao nhiêu ngày xảy ra hồi tháng Giêng, bất kì năm nào.

Các công thức sau đây sẽ khắc phục vấn đề trên:

= SUM (–(MONTH (A2: A10) = 1))

Vì đây là công thức mảng Excel, hãy nhớ nhấn Ctrl + Shift + Enter để hoàn tất.

Nếu bạn quan tâm đến một tháng khác, hãy thay thế 1 bằng một số tương ứng. Ví dụ, 2 là Tháng 2, 3 có nghĩa là Tháng 3, ?.?. Để khiến cho công thức linh động hơn, thì bạn có thể chỉ định số của Tháng trong một số ô, như trong hình:

Using the double unary operator in Excel array formulas

Và hiện giờ, tất cả chúng ta hãy cùng nghiên cứu xem rằng công thức mảng Excel làm việc như vậy nào nhé. Hàm MONTH trả về kết quả là hàng ngày trong các ô A2 đến A10 nằm trong tháng nào, chúng sẽ được biểu thị bởi một số sê-ri, tạo nên mảng {2; 1; 4; 2; 12; 1; 2; 12; 1}.

Sau đó, mỗi phần của mảng sẽ được so sánh với giá trị trong ô D1, là số 1 trong ví dụ này. Kết quả của phép so sánh đó là một mảng các giá trị Boolean (TRUE và FALSE). Như bạn nhớ, thì bạn có thể chọn một phần khẳng định trong một công thức mảng và nhấn F9 để xem phần đó tương tự với:

Cuối cùng, bạn phải chuyển hóa các giá trị Boolean này thành 1 và 0 để hàm SUM có thể hiểu được. Và đây là những gì các toán tử nhà cung cấp đôi nghĩ rằng thiết yếu. Các toán tử nhà cung cấp trước hết sẽ làm TRUE / FALSE thành -1/0, tương ứng. Cái thứ hai thì sẽ phủ định giá trị, tức là đảo ngược dấu, biến chúng thành +1 và 0, để mà đa số các hàm của Excel có thể hiểu và làm việc với nó. Nếu bạn loại bỏ toán tử nhà cung cấp đôi khỏi công thức trên, thì nó sẽ không hoạt động.

Rất là nhiều kiến thức phải không nào? Toàn thể những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ căn bản tới Chuyên Viên của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, cụ thể. Hơn nữa chẳng hề có hạn chế về thời gian học hành nên bạn có thể dễ chịu học bất kể bao giờ, đơn giản tra cứu lại kiến thức khi cần. Hiện tại hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký gia nhập khóa học.

Xem Thêm  JavaScript Lấy độ dài mảng - độ dài của mảng trong javascript

Viết một bình luận