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

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

Bạn đang xem: Công thức mảng là gì

Các công thức mảng vào Excel là 1 trong những công nạm vô cùng bổ ích và cũng khó khăn nhất để kiểm soát đượ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ế sửa chữa hàng ngàn phương pháp thông thường. Tuy nhiên, ước đạt đến 90% người tiêu dùng Excel chưa bao giờ sử dụng những công thức mảng trong bảng tính, chỉ bởi họ sợ hãi phải ban đầu học bọn chúng từ con số 0.


Xem nhanh


Hàm mảng vào Excel là gì?Tại sao phải sử dụng công thức mảng trong Excel?Công thức mảng với 1 ô và những ô vào ExcelHằng số mảng của ExcelLàm vấn đề với hằng số mảng của ExcelSử dụng hằng số mảng trong phương pháp của ExcelCác toán tử và và OR trong công thức mảng của Excel 

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

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

Ví dụ: nếu như khách hàng nhập danh sách mua hàng hàng tuần của chúng ta vào một format mảng Excel, thì nó vẫn kiểu như:

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

Sau đó, nếu như khách hàng chọn các ô tự A1 đến D1, thì nên nhập mảng ngơi nghỉ trên cùng với dấu bằng (=) trên thanh cách làm và dấn CTRL + SHIFT + ENTER, và bạn sẽ nhận được tác dụng như sau:

*


*

*

Những gì chúng ta vừa làm cho là sinh sản một mảng một chiều theo hướng ngang. Nãy giờ phần lớn thứ vẫn rất đối chọi giản, đề xuất không nào?

Bây giờ, bí quyết mảng Excel là gì? Sự khác hoàn toàn giữa một hàm mảng và các hàm Excel thường thì khác là hàm mảng đã xử lý các thay vày chỉ một giá chỉ trị nhưng mà thôi. Nói cách khác, một bí quyết mảng trong Excel sẽ review tất cả những giá trị đơn lẻ trong một mảng, và thực hiện nhiều phép tính bên trên một hoặc các mục mà có thỏa những điều khiếu nại được thể hiện trong công thức.

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

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

Và bây giờ, có vẻ như đã tới lúc bạn đề xuất tạo phương pháp mảng thứ nhất cho bạn.

Ví dụ dễ dàng của phương pháp mảng Excel

Giả sử chúng ta có một số trong những mục trong cột B, cùng cột C biểu lộ giá của chúng, bài toán của doanh nghiệp là tính tổng doanh thu.

Tất nhiên, không tồn tại gì chống cản chúng ta tính các tổng phụ (Sub total) trong mỗi hàng với cùng 1 công thức dễ dàng và đơn giản như = B2 * C2, với rồi tiếp nối cộng các giá trị này lại với nhau:

*

Tuy nhiên, một cách làm mảng có thể mang đến cho chính mình những điểm cải tiến vượt bậc quan trọng, từ lúc nó được Excel cần sử dụng trong lưu giữ trữ hiệu quả trung gian trong cỗ nhớ, gắng vì phải tạo thêm cột ngã sung. Bởi vậy, một cách làm mảng đơn chỉ việc thực hiện tại 2 bước nhanh chóng nho nhỏ dại như sau để giải quyết bài toán trên:

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

= SUM (B2: B6 * C2: C6)

Nhấn phím tắt CTRL + SHIFT + ENTER để hoàn tất phương pháp mảng.

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

Công thức đã nhân giá trị trong những hàng riêng biệt của mảng được hướng đẫn (các ô từ bỏ B2 mang đến C6), có thêm các tổng (phụ), và tác dụng cuối cùng là tổng doanh thu.

*

Ví dụ dễ dàng và đơn giản trên cho biết thêm sức mạnh của công thức mảng vào Excel. Khi thao tác với hàng ngàn hoặc hàng chục ngàn hàng chứa dữ liệu, thì chúng ta có thể cân kể khi nó sẽ tiết kiệm chi phí bao nhiêu thời hạn với một phương pháp mảng trong những ô.

Tại sao phải áp dụng công thức mảng trong Excel?

Công thức mảng Excel là công cụ tiện nghi nhất để tiến hành các phép tính rắc rối và các tác vụ phức tạp. Một công thức mảng đơn hoàn toàn có thể thay thế hàng ngàn công thức thông thường. Công thức mảng hoàn toàn có thể sử dụng mang đến các quá trình như:

Tính tổng những số thỏa các điều kiện đang cho, lấy ví dụ tính tổng N giá chỉ trị lớn số 1 hoặc nhỏ tuổi nhất vào một dải.Tính tổng những giá trị những hàng, hoặc vào hàng/cột máy N.Đếm các giá trị của toàn bộ hoặc đếm phần đa ký tự nhất quyết trong một dải xác định.

Làm nuốm nào nhằm nhập công thức mảng trong Excel (Ctrl + Shift + Enter)

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

Khi nhập một cách làm mảng trong Excel, bao gồm 4 điều đặc trưng mà bạn phải ghi nhớ:

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

Bởi vì tất cả các cách làm mảng vào Excel đều đòi hỏi phải nhấn tổ hợp phím Ctrl + Shift + Enter, đôi lúc được điện thoại tư vấn là phương pháp CSE.

Sử dụng phím F9 để đánh giá các phần nằm trong một bí quyết mảng:

Khi thao tác làm việc với những công thức mảng vào Excel, chúng ta cũng có thể quan sát phương pháp chúng đo lường và tính toán và lưu trữ các mục của mảng (mảng nội bộ) để hiển thị tác dụng cuối cùng trong một ô. Để làm cho điều này, hãy lựa chọn 1 hoặc các đối số phía bên trong ngoặc đối kháng của một hàm, rồi kế tiếp nhấn phím F9. Để tắt chính sách đánh giá chỉ công thức, hãy thừa nhận phím Esc.

Trong ví dụ trên, để xem những 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 dấn F9 và thu được kết quả như sau:

*

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

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

Công thức mảng rất có thể trả lại kết quả trong một ô hoặc trong vô số ô. Một công thức mảng được nhập vào vào một dải ô được call là bí quyết đa ô. Công thức mảng nằm trong một ô duy nhất được hotline là cách làm ô đơn.

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

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

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

Ví dụ 1. Một cách làm mảng với ô đơn

Giả sử các bạn có nhì cột liệt kê số lượng mặt hàng được cung cấp trong 2 tháng không giống nhau, ví dụ cột B cùng cột C, và bạn có nhu cầu tìm số lợi nhuận tăng lớn số 1 có thể.

Thông thường, các bạn sẽ thêm một cột té sung, lấy một ví dụ cột D, tính doanh thu đổi khác cho từng sản phẩm, áp 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 phương pháp mảng Excel không bắt buộc một cột bổ sung cập nhật vì nó lưu giữ trữ hoàn hảo và tuyệt vời nhất tất cả các tác dụng trung gian trong cỗ nhớ. Bởi vậy, bạn chỉ cần nhập bí quyết sau cùng nhấn Ctrl + Shift + Enter mà lại thôi:

= MAX (C2: C6-B2: B6)

*

Ví dụ 2. Phương pháp mảng đa ô trong Excel

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

Trước tiên, bạn phải chọn dải ô vào một cột trống, ví như D2: D6, và nhập cách làm 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ách làm mảng của công ty trong từng ô vào dải sẽ chọn, và bạn sẽ nhận được công dụng trả về như sau:

*

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

Như vẫn đề cập, thì Microsoft Excel cung ứng 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 những mảng nhiều ô. Hàm TRANSPOSE là trong những hàm như vậy và chúng tôi sẽ áp dụng nó để chuyển đổi vị trí vào bảng sinh hoạt trên, có nghĩa là chuyển sản phẩm sang cột với ngược lại.

Xem thêm:

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

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

*

Làm nuốm nào để triển khai việc với những công thức mảng nhiều ô

Khi làm việc với những công thức mảng nhiều ô vào Excel, hãy đảm bảo tuân theo những quy tắc dưới đây để có được kết quả chính xác:

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

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

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

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

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

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

1. Hằng số của mảng ngang

Hằng số của mảng ngang được để chỉ trong một hàng. Để tạo nên một hằng số mảng hàng, hãy nhập các giá trị được bí quyết 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, chúng ta phải nhập thủ công các vệt đóng và mở ngoặc. Để nhập một mảng theo chiều ngang vào một bảng tính, nên chọn lựa số tương ứng của các ô trống vào một hàng, và nhập cách làm = 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 hệt như sau:

*

Như chúng ta thấy vào hình, thì Microsoft Excel đặt hằng số mảng vào một tập hợp những dấu ngoặc nhọn khác, giống hệt như khi ai đang nhập một bí quyết mảng trong Excel.

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

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

*

3. Hằng số của mảng nhì chiều

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

*

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

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

1. Những thành phần của một hằng số mảng

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

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

Hằng số mảng ko thể bao hà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 vào Excel khác.

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

Để tạo cho mảng trở phải dễ áp dụng hơn, hãy đặt cho nó một cái tên:

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

= “Su”, “Mo”, “Tu”, “We”, “Th”, “Fr”, “Sa”

*

Nhấn OK nhằm lưu mảng đang đặt tên và đóng cửa sổ.

Để nhập hằng số của mảng đã chọn cái tên trong một trang tính, nên chọn nhiều ô vào một 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 phương pháp với vệt = đằng trước cùng nhấn Ctrl + Shift + Enter.

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

*

3. Ngăn ngừa 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 những ký tự tương thích – dấu phẩy với hằng số của mảng ngang với dấu chấm phẩy với các mảng dọc.Chọn một dải có những ô đúng với con số các mục vào hằng số mảng của bạn. Nếu bạn chọn các ô hơn, thì mỗi ô phụ sẽ sở hữu lỗi # N / A. Trường hợp ít 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 bí quyết của Excel

Bây giờ các bạn đã quen thuộc với khái niệm những hằng số của mảng, nên họ hãy cùng xem phương pháp mà bạn cũng có thể sử dụng những mảng trong những công thức của Excel để giải quyết và xử lý các thực tế các bước của bạn.

Ví dụ 1. Cùng N các số lớn số 1 / bé dại nhất vào một dải

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

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

Tính tổng 3 số khủng nhất: = SUM (LARGE (range, 1,2,3))

Tính tổng bé dại nhất 3 số: = SUM (SMALL (range, 1,2,3))

Đừng quên nhận Ctrl + Shift + Enter khi bạn nhập phương pháp mảng, và bạn sẽ nhận được công dụng như sau:

*

Tương từ như vậy, chúng ta có thể tính cực hiếm trung bình của N giá chỉ 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ỏ dại nhất: = AVERAGE (SMALL (range, 1,2,3))

Ví dụ 2. Công thức mảng để tính những ô thỏa các điều kiện

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

Cách đơn giản và dễ dàng nhất sẽ sử dụng một bí quyết COUNTIFS với nhiều điều kiện. Mặc dù nhiên, nếu bạn có nhu cầu đưa nhiều sản phẩm vào, thì công thức COUNTIFS của bạn cũng có thể sẽ quá dài. Để tạo nên nó nhỏ gọn hơn, bạn cũng 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 ba đố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 công ty chúng tôi có bao gồm chỉ gồm hai nhân tố với mục tiêu là để lấy cho bạn phương pháp tiếp cận. Thực tế, phương pháp mảng của bạn, bao gồm thể bao hàm nhiều nhân tố như phép buộc ràng logic, với yêu ước chiều dài của cách làm không vượt quá 8,192 ký tự vào Excel 2013, 2010 và 2007 (1,024 ký tự vào Excel 2003 về bên trước) với máy tính của doanh nghiệp đủ táo tợn để giải pháp xử lý mảng lớn.

Các toán tử & và OR trong phương pháp mảng của Excel

Một toán tử của mảng cho thấy thêm cách bạn muốn xử lý mảng – thực hiện hàm xúc tích và ngắn gọn AND và OR.

Toán tử & là lốt hoa thị (*). Nó gợi ý Excel trả về TRUE nếu như TẤT CẢ những điều kiện được review là TRUE.Toán tử OR là dấu cùng (+). Nó trả về TRUE nếu ngẫu nhiên điều khiếu nại nào vào một biểu thức đã chỉ ra rằng TRUE.

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

Trong ví dụ như này, bọn chúng tôi cho thấy tổng doanh thu tương ứng mà lại người bán sản phẩm là Mike và thành 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)))

 
*

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

Công thức mảng sau với toán tử OR (+) chất nhận được thêm toàn bộ các bán hàng ứng cùng với người bán hàng là Mike hoặc thành phầm là Apples:

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

*

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 trường hợp như vậy thực thụ không cần thiết và một phương pháp SUMIFS đối chọi giản hoàn toàn có thể cho ra thuộc một kết quả như trên. Tuy nhiên, toán tử và và OR trong số công thức mảng rất có thể hữu ích trong những tình huống tinh vi hơn, điều đó liên quan đến việc luyện tập các phát minh cho não của công ty : )

Toán tử đối kháng vị vài nét hành trong phương pháp mảng của Excel

Nếu bạn đã từng thao tác làm việc với những công thức mảng vào Excel, rất có thể bạn đang lướt sang 1 vài công thức có chứa hai vệt gạch ngang (–) và bạn có thể đã tự hỏi rằng nó được thực hiện cho những việc gì.

Hai vệt gạch ngang đôi, gọi là toán tử đơn vị chức năng đôi, được thực hiện để 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ới 0 – thứ nhưng mà một hàm mảng hoàn toàn có thể hiểu được.

Ví dụ sau hy vọng làm cho hầu như thứ dễ hiểu hơn cho bạn. Trả sử chúng ta có một danh sách ngày vào cột A và bạn có nhu cầu biết gồm bao nhiêu ngày xảy ra vào tháng Giêng, bất kể năm nào.

Các công thức dưới đây sẽ giải quyết vấn đề trên:

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

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

Nếu bạn xem xét một mon khác, hãy sửa chữa 1 bằng một số tương ứng. Ví dụ, 2 là tháng 2, 3 có nghĩa là Tháng 3, v.v. Để khiến cho công thức linh hoạt hơn, thì chúng ta có thể chỉ định số của tháng trong một trong những ô, như trong hình:

*

Và bây giờ, bọn họ hãy thuộc phân tích coi rằng công thức mảng Excel làm việc như cụ nào nhé. Hàm MONTH trả về kết quả là hằng ngày trong những ô A2 mang đến A10 nằm hồi tháng nào, chúng sẽ được bộc lộ bởi một số sê-ri, tạo thành mảng 2; 1; 4; 2; 12; 1; 2; 12; 1.

Sau đó, từng phần của mảng sẽ được so sánh với quý giá trong ô D1, là số 1 trong ví dụ này. Công dụng của phép đối chiếu này là 1 trong mảng những giá trị Boolean (TRUE với FALSE). Như các bạn nhớ, thì chúng ta cũng có thể chọn một phần nhất định trong một bí quyết mảng và nhấn F9 để thấy phần đó tương đương với:

Cuối cùng, các bạn phải chuyển đổi các quý giá Boolean này thành 1 cùng 0 nhằm hàm SUM rất có thể hiểu được. Và đó là những gì các toán tử đơn vị chức năng đôi đến là bắt buộc thiết. Những toán tử 1-1 vị đầu tiên sẽ có tác dụng TRUE / FALSE thành -1/0, tương ứng. Dòng thứ hai thì sẽ tủ định giá bán trị, có nghĩa là đảo ngược dấu, vươn lên là chúng thành +1 và 0, để mà số đông các hàm của Excel có thể hiểu và thao tác làm việc với nó. Nếu như bạn sa thải toán tử đơn vị đôi khỏi bí quyết trên, thì nó sẽ không còn hoạt động.

Rất nhiều kỹ năng và kiến thức phải không nào? toàn cục những kiến thức và kỹ năng này chúng ta đều rất có thể học được vào khóa học EX101 – Excel tự cơ phiê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 kỹ năng một biện pháp đầy đủ, đưa ra tiết. Rộng nữa không hề có số lượng giới hạn về thời gian học tập nên bạn cũng có thể thoải mái học bất kể lúc nào, thuận lợi tra cứu vớt lại kiến thức khi cần. Bây giờ hệ thống đang xuất hiện ưu đãi hết sức lớn cho chính mình khi đk tham gia khóa học.