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

Bạn đang xem: Hướng dẫn và ví dụ về công thức mảng array formulas trong excel

Các công thức mảng vào Excel là một trong công thế vô cùng có ích và cũng nặng nề nhất để kiểm soát điều hành được nó. Một bí quyết mảng Excel đơn có thể thực hiện các phép tính và sửa chữa hàng ngàn bí quyết thông thường. Mặc dù nhiên, ước chừng đến 90% người dùng Excel chưa lúc nào sử dụng các công thức mảng vào bảng tính, chỉ do 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 thực hiện công thức mảng trong Excel?Công thức mảng với cùng một ô và những ô vào ExcelHằng số mảng của ExcelLàm câu hỏi với hằng số mảng của ExcelSử dụng hằng số mảng trong công thức của ExcelCác toán tử và và OR trong bí quyết mảng của Excel 

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

Trước khi chúng ta ban đầu tìm gọi về hàm và những công thức mảng Excel, thì chúng ta hãy khám phá 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. Những 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ố hàng với cột.

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

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

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

*


*

*

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

Bây giờ, cách làm 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 đang xử lý nhiều thay vì chỉ một giá trị nhưng thôi. Nói biện pháp khác, một cách làm mảng trong Excel sẽ review tất cả các giá trị đơn thân trong một mảng, và triển khai 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 bộc lộ trong công thức.

Một phương pháp mảng không những xử lý đồng thời một vài giá trị, mà nó cũng có thể cùng 1 dịp trả lại một số các giá bán trị. Bởi thế, các hiệu quả trả lại bởi cách làm mảng cũng là một trong mảng.

Công thức mảng có sẵn trong toàn bộ các phiên bạn dạng của Excel 2013, Excel 2010, Excel 2007 cùng trước đó nữa.

Và bây giờ, dường như như đã tới khi bạn đề nghị tạo công thức mảng trước tiên cho bạn.

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

Giả sử các bạn có một vài mục vào cột B, cùng cột C biểu thị giá của chúng, bài xích 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 các bạn tính các tổng phụ (Sub total) trong những hàng với một công thức dễ dàng như = B2 * C2, với rồi sau đó cộng các giá trị đó lại với nhau:

*

Tuy nhiên, một cách làm mảng hoàn toàn có thể mang đến cho bạn những điểm bứt phá quan trọng, từ lúc nó được Excel dùng trong lưu trữ tác dụng trung gian trong cỗ nhớ, cố vì phải lập thêm cột ngã sung. Vị vậy, một công thức mảng đơn chỉ việc thực hiện 2 bước hối hả nho nhỏ tuổi như sau để giải quyết và xử lý bài toán trên:

Chọn một ô trống với nhập vào bí quyết 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ổ hợp phím trên, Microsoft Excel đặt cách làm trên vào dấu ngoặc nhọn, tuyệt được gọi một dấu hiệu dễ chú ý thấy cho thấy thêm đó là một công thức mảng.

Công thức vẫn nhân giá trị trong những hàng riêng biệt của mảng được chỉ định và hướng dẫn (các ô tự B2 mang lại C6), gồm thêm các tổng (phụ), và hiệu quả cuối thuộc là tổng doanh thu.

*

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

Tại sao phải sử dụng công thức mảng vào Excel?

Công thức mảng Excel là công cụ tiện dụng nhất để tiến hành các phép tính tinh tướng và các tác vụ phức tạp. Một bí quyết 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 rất có thể sử dụng mang lại các công việc như:

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

Làm cố nào để 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 trong những phương thức kỳ diệu để đổi thay 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ông thức mảng trong Excel, bao gồm 4 điều quan trọng mà bạn cần ghi nhớ:

Một khi chúng ta đã kết thúc gõ bí quyết và đôi khi nhấn các phím CTRL + SHIFT+ ENTER, thì Excel sẽ tự động hóa đặt bí quyết đó trong dấu ngoặc nhọn. Khi chúng ta chọn một/các ô như vậy, bạn cũng có thể thấy những 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ách làm mảng trong đó.Việc nhập những dấu ngoặc nhọn bao quanh công thức bằng tay sẽ ko chuyển công thức trên sang định hình mảng. Các bạn phải nhấn tổng hợp phím Ctrl + Shift + Enter để kết thúc công thức mảng.Mỗi lần bạn sửa đổi một bí quyết mảng, các dấu ngoặc nhọn sẽ mất tích và các bạn phải dấn Ctrl + Shift + Enter nhằm lưu các biến hóa trong bí quyết của bạn.Nếu bạn quên dìm Ctrl + Shift + Enter, thì cách làm của các bạn sẽ hoạt động như một cách làm Excel thường thì 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 phương pháp mảng vào Excel đều yên cầu phải nhấn tổng hợp phím Ctrl + Shift + Enter, thỉnh thoảng được hotline là cách làm CSE.

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

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

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

*

Lưu ý: Hãy để ý rằng chúng ta phải lựa chọn 1 số phần của công thức trước khi nhấn phím F9, nếu như không khi nhận F9 thì nó vẫn chỉ đơn giản và dễ dàng thay thế cách làm mảng của khách hàng 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 tác dụng trong một ô hoặc trong vô số ô. Một cách làm mảng được nhập vào trong một dải ô được điện thoại tư vấn là bí quyết đa ô. Bí quyết mảng phía bên trong một ô độc nhất vô nhị được gọi là bí quyết ô đơn.

Có một vài hàm mảng có thiết kế để trả về những mảng nhiều ô, ví như TRANSPOSE, TREND, FREQUENCY, LINEST, v.v.

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 lúc nhập vào một ô duy nhất bằng tổng hợp phím Ctrl + Shift + Enter.

Các ví dụ sau minh hoạ cách sử dụng một bí quyết mảng một ô và các ô trong Excel.

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

Giả sử bạn có nhì cột liệt kê số lượng mặt hàng được phân phối trong 2 tháng không giống nhau, lấy ví dụ như cột B cùng 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, các bạn sẽ thêm một cột té sung, ví dụ như cột D, tính doanh thu đổi khác cho mỗi sản phẩm, sử dụng công thức như = C2-B2, và kế tiếp tìm giá chỉ trị lớn nhất trong cột bổ sung đó = MAX (D: D).

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

= MAX (C2: C6-B2: B6)

*

Ví dụ 2. Bí quyết mảng nhiều ô trong Excel

Trong lấy ví dụ như trước của hàm SUM, đưa sử chúng ta phải trả 10% thuế cho mỗi lần cung cấp và bạn muốn tính số chi phí 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 ô trong một cột trống, ví như D2: D6, và nhập phương pháp 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 đang đặt phương pháp mảng của doanh nghiệp trong mỗi ô trong dải đang chọn, và các 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 các ô

Như vẫn đề cập, thì Microsoft Excel cung ứng một vài dòng gọi là “các hàm mảng” được thiết kế với đặc biệt để làm việc với các mảng nhiều ô. Hàm TRANSPOSE là trong số những hàm do đó và shop chúng tôi sẽ áp dụng nó để biến hóa vị trí trong bảng ngơi nghỉ trên, tức là chuyển hàng sang cột cùng ngược lại.

Xem thêm: Học Tiếng Nhật Với Những Cách Phát Âm Tiếng Nhật Chuẩn Như Người Bản Xứ

Chọn một dải những ô trống mà bạn muốn xuất ra bảng sau khi chuyển đổi. Vì họ đang thay đổi các mặt hàng thành các cột, đề xuất hãy chắc chắn là rằng chúng ta chọn cùng một số trong những hàng và cột như số những cột và những hàng khớp ứng trong bảng nguồn (bảng gốc) bạn có. Trong lấy ví dụ này, công ty chúng tôi đang chọn 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) và nhấn Ctrl + Shift + Enter. Trong ví dụ trên, phương pháp là = TRANSPOSE ($A$1: $D$6).

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

*

Làm cố gắng nào để làm việc với những công thức mảng các ô

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

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

Nếu phương pháp của chúng ta cũng có thể trả về một mảng với một số lượng các bộ phận có thể vậy đổi, thì nên nhập nó vào một trong những dải bởi hoặc lớn hơn mảng lớn số 1 mà được bởi công thức trả về cùng hãy lồng công thức của 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ỉ đơn giản dễ dàng là một tập những giá trị vắt định. Các giá trị này không khi nào thay đổi khi bạn coppy công thức vào những ô hoặc những giá trị khác.

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

Tồn tại 3 các 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 để chỉ vào một hàng. Để sản xuất một hằng số mảng hàng, hãy nhập các giá trị được giải pháp nhau vì chưng dấu phẩy, và đặt nó trong vết ngoặc nhọn, ví dụ: 1,2,3,4

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

*

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, y hệt như khi nhiều người đang nhập một phương pháp 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ư một mảng ngang, cùng với sự biệt lập duy độc nhất là chúng ta ngăn cách các 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

Để sản xuất 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à phân làn mỗi cột bằng dấu chấm phẩy = “a”, “b”, “c”; 1, 2, 3

*

Làm câu hỏi với hằng số mảng của Excel

Hằng số mảng là giữa những nền tảng của những công thức mảng trong Excel. Các thông tin với mẹo nhỏ tuổi dưới đây có thể giúp bạn sử dụng chúng một cách hiệu 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 hoàn toàn có thể chứa các con số, cực hiếm văn bản, quý giá Booleans (TRUE và FALSE) và các giá trị lỗi, bọn chúng được ngăn tách bóc bằng dấu phẩy hoặc che dấu chấm phẩy.

Bạn rất có thể nhập một quý giá số bên dưới dạng số nguyên, thập phân, hoặc cam kết hiệu khoa học. Nếu như bạn sử dụng những giá trị văn bản, chúng sẽ được đặt trong lố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 gồm các mảng, tham chiếu ô, dải, ngày tháng, thương hiệu xác định, công thức, hoặc những hàm trong Excel khác.

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

Để khiến cho mảng trở bắt buộc dễ sử dụng hơn, hãy đặt mang đến nó một chiếc tên:

Đi mang lại tab Formulas > team Defined Names và nhấn Define Name. Hoặc là dìm Ctrl + F3 với nhấp vàoNhập tên bạn muốn đặt vào ô NameTrong vỏ hộp Refers to, nhập những mục của mảng, cơ mà được đặt trong vết 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 sẽ đặt thương hiệu và đóng cửa sổ.

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

Kết quả giới thiệu như sau:

*

3. Ngăn chặn lỗi

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

Ngăn cách những 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 những mảng dọc.Chọn một dải có những ô đúng với số lượng các mục trong hằng số mảng của bạn. Nếu khách hàng chọn những ô hơn, thì mỗi ô phụ sẽ có lỗi # N / A. Nếu như ít hơn, thì chỉ một phần của mảng sẽ tiến hành xuất ra.

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

Bây giờ bạn đã thân quen 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ó thể sử dụng những mảng trong số công thức của Excel để giải quyết các thực tế quá trình của bạn.

Ví dụ 1. Cộng N những số lớn nhất / nhỏ dại nhất trong một dải

Bạn bắt đầu bằng phương pháp tạo một mảng dọc tất cả chứa đầy đủ số bạn có nhu cầu tính tổng. Ví dụ: nếu bạn có nhu cầu cộng 3 số nhỏ dại nhất hoặc lớn số 1 trong một dải, thì hằng số mảng đang là 1,2,3.

Sau đó, chúng ta sử dụng một trong hai hàm bí quyết sau LARGE hoặc SMALL, xác định cục bộ dải của ô vào tham số trước tiên và để 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ố béo nhất: = SUM (LARGE (range, 1,2,3))

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

Đừng quên nhấn Ctrl + Shift + Enter khi bạn nhập cách làm mảng, và các bạn sẽ nhận được hiệu quả như sau:

*

Tương từ bỏ như vậy, chúng ta cũng có thể tính quý hiếm trung bình của N giá chỉ trị nhỏ tuổi nhất hoặc lớn nhất trong một dải:

Trung bình của 3 số béo nhất: = AVERAGE (LARGE (range, 1,2,3))

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

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

Giả sử bạn có một danh sách đơn đặt 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 thành phầm được chỉ định.

Cách dễ dàng và đơn giản nhất sẽ áp dụng một công thức COUNTIFS với khá nhiều điều kiện. Mặc dù nhiên, nếu bạn muốn đưa nhiều thành phầm vào, thì cách làm COUNTIFS của chúng ta có thể sẽ thừa dài. Để làm cho nó nhỏ dại gọn hơn, chúng ta có thể sử dụng COUNTIFS với SUM và gồm một hằng số mảng vào 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 rất có thể trông như sau:

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

*

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

Các toán tử and và OR trong cách làm mảng của Excel

Một toán tử của mảng cho thấy cách bạn có nhu cầu xử lý mảng – áp dụng hàm logic AND và OR.

Toán tử và là vệt hoa thị (*). Nó khuyên bảo Excel trả về TRUE giả dụ TẤT CẢ các điều khiếu nại đượ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 cùng với toán tử AND

Trong lấy một ví dụ này, bọn chúng tôi cho thấy thêm tổng lệch giá tương ứng như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)))

 
*

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 (+) được cho phép thêm tất cả các bán sản phẩm ứng cùng với người bán sản phẩm là Mike hoặc thành phầm là Apples:

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

*

Mách nhỏ: trong số phiên bạn dạng hiện đại của Excel 2013, 2010 hoặc 2007, câu hỏi sử dụng các công thức mảng trong các tình huống như vậy thực sự không quan trọng và một cách làm SUMIFS 1-1 giản rất có thể cho ra cùng một kết quả như trên. Tuy nhiên, toán tử & và OR trong các công thức mảng có thể hữu ích trong số tình huống phức tạp hơn, vấn đề này liên quan tới sự việc luyện tập các phát minh cho não của người sử dụng : )

Toán tử solo vị đôi điều hành trong công thức mảng của Excel

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

Hai lốt gạch ngang đôi, call là toán tử đơn vị đôi, được sử dụng để chuyển những giá trị Boolean không ngay 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 có thể hiểu được.

Ví dụ sau hy vọng làm cho đông đảo thứ dễ nắm bắt hơn đến bạn. Trả sử bạn có một danh sách ngày trong cột A và bạn có nhu cầu biết có bao nhiêu ngày xảy ra trong thời điểm tháng Giêng, bất cứ năm nào.

Các công thức tiếp sau đâ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ớ thừa nhận Ctrl + Shift + Enter để hoàn tất.

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

*

Và bây giờ, bọn họ hãy cùng phân tích coi rằng công thức mảng Excel thao tác làm việc như núm nào nhé. Hàm MONTH trả về kết quả là hằng ngày trong các ô A2 mang đến A10 nằm trong tháng nào, bọn chúng sẽ được biểu lộ bởi một vài 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 vẫn được đối chiếu với quý giá trong ô D1, là số 1 trong ví dụ này. Công dụng của phép so sánh này là 1 mảng những giá trị Boolean (TRUE cùng FALSE). Như chúng ta nhớ, thì chúng ta có thể chọn 1 phần nhất định vào một công thức mảng và nhấn F9 giúp thấy phần đó tương tự với:

Cuối cùng, các bạn phải biến hóa các cực hiếm Boolean này thành 1 và 0 để hàm SUM rất có thể hiểu được. Và đây là những gì những toán tử đơn vị đôi đến là đề nghị thiết. Những toán tử solo vị đầu tiên sẽ có tác dụng TRUE / FALSE thành -1/0, tương ứng. Loại thứ nhì thì sẽ bao phủ định giá chỉ trị, có nghĩa là đảo ngược dấu, đổi mới chúng thành +1 cùng 0, để mà hầu như các hàm của Excel có thể hiểu và làm việc với nó. Ví như bạn thải trừ toán tử đơn vị đôi khỏi phương pháp trên, thì nó sẽ không còn hoạt động.

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