CÔNG THỨC MẢNG TRONG EXCEL

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

Bạn đang xem: Công thức mảng trong excel

Các công thức mảng trong Excel là một trong công nỗ lực vô cùng bổ ích và cũng cạnh tranh nhất để kiểm soát điều hành được nó. Một công thức mảng Excel đơn rất có thể thực hiện những phép tính và sửa chữa hàng ngàn công thức thông thường. Mặc dù nhiên, ước lượng đến 90% người dùng Excel chưa bao giờ sử dụng các công thức mảng trong bảng tính, chỉ vì chưng họ hại phải bắt đầu học chúng từ số lượng 0.


Xem nhanh


Hàm mảng trong Excel là gì?Tại sao phải áp 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 việc với hằng số mảng của ExcelSử dụng hằng số mảng trong bí quyết của ExcelCác toán tử and và OR trong cách làm mảng của Excel 

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

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

Ví dụ: nếu khách hàng nhập danh sách mua sắm 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 bạn chọn các ô tự A1 mang đến D1, thì hãy nhập mảng sinh hoạt trên với dấu bởi (=) trên thanh phương pháp và dìm CTRL + SHIFT + ENTER, và bạn sẽ nhận được hiệu quả như sau:

*


*

*

Những gì bạn vừa có tác dụng là tạo nên một mảng một chiều theo chiều ngang. Nãy giờ các thứ vẫn rất solo giản, nên không nào?

Bây giờ, công thức mảng Excel là gì? Sự khác biệt giữa một hàm mảng và những hàm Excel thông thường khác là hàm mảng vẫn xử lý nhiều thay do chỉ một giá trị mà thôi. Nói giải pháp khác, một phương pháp mảng vào Excel sẽ review tất cả những giá trị trật 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 biểu lộ trong công thức.

Một công thức mảng không chỉ là xử lý đồng thời một số giá trị, cơ mà nó cũng có thể cùng 1 thời gian trả lại một số trong những các giá bán trị. Vì chưng thế, các công dụng trả lại bởi bí quyết 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 phiên bản của Excel 2013, Excel 2010, Excel 2007 cùng trước đó nữa.

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

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

Giả sử bạn có một trong những mục trong cột B, cùng cột C bộc lộ giá của chúng, bài xích toán của người tiêu dùng 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 đơn giản dễ dàng 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 hoàn toàn có thể mang đến cho bạn những điểm nâng tầm quan trọng, từ khi nó được Excel dùng trong lưu trữ hiệu quả trung gian trong bộ nhớ, vắt vì phải tạo thêm cột té sung. Vị vậy, một bí quyết mảng đơn chỉ cần thực hiện tại 2 bước gấp rút nho nhỏ như sau để giải quyết và xử lý bài toán trên:

Chọn một ô trống và 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 cách làm mảng.

Khi nhấn tổng 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 điện thoại tư vấn một tín hiệu dễ chú ý thấy cho thấy thêm đó là 1 trong những công thức mảng.

Công thức vẫn nhân giá chỉ trị trong mỗi 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), có thêm những tổng (phụ), và tác dụng cuối thuộc là tổng doanh thu.

*

Ví dụ đơn giản dễ dàng 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ì bạn có thể cân đề cập khi nó sẽ tiết kiệm chi phí bao nhiêu thời hạn với một cách làm mảng trong mỗi ô.

Tại sao phải thực hiện công thức mảng trong Excel?

Công thức mảng Excel là công cụ tiện lợi nhất để triển khai các phép tính phức tạp và những tác vụ phức tạp. Một bí quyết mảng đơn rất có thể thay thế hàng trăm công thức thông thường. Công thức mảng hoàn toàn có thể sử dụng mang lại các các bước như:

Tính tổng các số thỏa các điều kiện sẽ cho, lấy một ví dụ tính tổng N giá trị lớn số 1 hoặc nhỏ dại 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 đồ vật N.Đếm những 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.

Làm vắt 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 trong những phương thức kỳ lạ để biến đổi một công thức thông thường thành một bí quyết mảng.

Khi nhập một công thức mảng trong Excel, bao gồm 4 điều đặc biệt quan trọng mà bạn phải ghi nhớ:

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

Sử dụng phím F9 để reviews các phía bên trong một công thức mảng:

Khi thao tác với các công thức mảng trong Excel, bạn có thể quan sát giải pháp chúng tính toán và giữ trữ những mục của mảng (mảng nội bộ) nhằm 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 những đối số bên trong ngoặc solo của một hàm, rồi sau đó nhấn phím F9. Để tắt 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 toàn bộ các sản phẩm, bạn phải chọn B2: B6 * C2: C6, rồi nhận F9 và thu được kết quả như sau:

*

Lưu ý: Hãy để ý rằng chúng ta phải lựa 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 thừa nhận F9 thì nó đã chỉ đơn giản dễ dàng thay thế bí quyết mảng của doanh nghiệp bằng (các) giá trị đo lường và tính toán mà thôi.

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

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

Có một vài ba hàm mảng có phong cách thiết kế để trả về các 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, hoàn toàn 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 lấy một ví dụ sau minh hoạ cách sử dụng một phương pháp mảng một ô và nhiều ô vào Excel.

Ví dụ 1. Một bí quyết mảng với ô đơn

Giả sử các bạn có hai cột liệt kê số lượng món đồ được bán trong 2 tháng khác nhau, lấy ví dụ như cột B với cột C, và bạn muốn tìm số lợi nhuận tăng lớn số 1 có thể.

Thông thường, bạn sẽ thêm một cột xẻ sung, ví dụ cột D, tính doanh thu biến hóa cho từng sản phẩm, áp dụng công thức như = C2-B2, và kế tiếp tìm giá bán trị lớn nhất trong cột bổ sung cập nhật đó = MAX (D: D).

Tuy nhiên, một cách làm mảng Excel không nên một cột bổ sung vì nó giữ trữ hoàn hảo nhất tất cả các công dụng trung gian trong cỗ nhớ. Vì vậy, bạn chỉ việc nhập bí quyết sau với nhấn Ctrl + Shift + Enter cơ mà thôi:

= MAX (C2: C6-B2: B6)

*

Ví dụ 2. Cách làm mảng đa ô trong Excel

Trong lấy ví dụ trước của hàm SUM, trả sử 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 mỗi sản phẩm chưa đến 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 đã đặt phương pháp mảng của người sử dụng trong mỗi ô trong dải vẫn chọn, và bạn sẽ nhận được kết quả trả về như sau:

*

Ví dụ 3. áp dụng một hàm mảng Excel nhằm trả về mảng những ô

Như vẫn đề cập, thì Microsoft Excel hỗ trợ một vài cái gọi là “các hàm mảng” có thiết kế đặc biệt để triển khai việc với các mảng đa ô. Hàm TRANSPOSE là trong những hàm bởi vậy và chúng tôi sẽ thực hiện nó để thay đổi vị trí vào bảng sống trên, có nghĩa là chuyển hàng sang cột và ngược lại.

Xem thêm: Những Câu Chuyện Kinh Dị Có Thật Trên Thế Giới, Liệu Có Những Bí Ẩn Gì Chưa Được Giải Đáp

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ì họ đang đổi khác các mặt hàng thành những cột, đề xuất hãy chắc chắn rằng bạn chọn cùng một số hàng cùng cột như số các cột và những hàng khớp ứng trong bảng mối cung cấp (bảng gốc) bạn có. Trong lấy một ví dụ này, cửa hàng chúng tôi đang tuyển lựa 6 cột cùng 4 hàng.Nhấn F2 nhằm vào chính sá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ụ như trên, công thức là = TRANSPOSE ($A$1: $D$6).

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

*

Làm nỗ lực nào để triển khai việc với các công thức mảng các ô

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

Nếu công thức của chúng ta có thể trả về một mảng với một số trong những lượng các thành phần có thể rứa đổi, thì hãy nhập nó vào một dải bằng hoặc lớn hơn mảng lớn nhất mà được bởi bí quyết trả về và 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ị cụ định. Các giá trị này không lúc nào thay thay đổi khi bạn sao chép công thức vào những ô hoặc những giá trị khác.

Bạn đã có tác dụng một lấy ví dụ như 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 xích hướng dẫn này. Bây giờ, bọn họ hãy cùng xem những loại mảng khác ra làm sao và cách chúng ta cũng có thể lập ra chúng.

Tồn trên 3 một số 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 đặ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 phương pháp nhau vì chưng dấu phẩy, và đặt chúng trong vết ngoặc nhọn, ví dụ: 1,2,3,4

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

*

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

Bạn hoàn toàn có thể nhập một quý giá số bên dưới dạng số nguyên, thập phân, hoặc ký kết hiệu khoa học. Nếu khách hàng sử dụng các giá trị văn bản, chúng sẽ được đặt trong vệt 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 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 tên:

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

Đi mang đến tab Formulas > nhóm Defined Names và nhấn Define Name. Hoặc là nhấn Ctrl + F3 và nhấp vàoNhập tên bạn muốn đặt vào ô NameTrong hộp Refers to, nhập các mục của mảng, mà lại được đặt trong vế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 vẫn đặt tên và ngừng hoạt động sổ.

Để nhập hằng số của mảng đã được đặt 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 phương pháp với dấu = đằng trước với nhấn Ctrl + Shift + Enter.

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

*

3. Ngăn ngừa lỗi

Nếu mảng của bạn không vận độ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 – vết phẩy cùng 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ó những ô đúng với con số các mục vào hằng số mảng của bạn. Nếu như khách hàng chọn nhiều ô hơn, thì từng ô phụ sẽ sở hữu lỗi # N / A. Nếu ít hơn, thì chỉ 1 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 thuộc xem cách mà chúng ta có thể sử dụng các mảng trong số công thức của Excel để giải quyết các thực tế các bước của bạn.

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

Bạn bước đầu bằng giải pháp tạo một mảng dọc gồm chứa hồ hết số bạn muốn tính tổng. Ví dụ: nếu bạn có nhu cầu cộng 3 số nhỏ 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 đó, các bạn sử dụng 1 trong 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ố thứ nhất và đặt hằng số mảng trong tham số sản phẩm hai. Cuối cùng, lồng nó vào hàm SUM, như sau:

Tính tổng 3 số mập 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 chúng ta nhập phương pháp mảng, và các bạn sẽ nhận được công dụng như sau:

*

Tương tự như vậy, chúng ta cũng có thể tính quý giá trung bình của N giá bán trị bé dại nhất hoặc lớn số 1 trong một dải:

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

Trung bình của 3 số bé dại nhất: = AVERAGE (SMALL (range, 1,2,3))

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

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

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

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

Một toán tử của mảng cho biết cách bạn muốn xử lý mảng – sử dụng hàm ngắn gọn xúc tích AND và OR.

Toán tử and là vệt hoa thị (*). Nó gợi ý Excel trả về TRUE nếu TẤT CẢ những điều kiện được đánh giá là TRUE.Toán tử OR là dấu cùng (+). Nó trả về TRUE nếu bất kỳ điều khiếu nại nào vào một biểu thức đã cho là TRUE.

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

Trong lấy một ví dụ này, chúng tôi cho biết tổng lợi nhuận tương ứng mà người bán sản phẩm 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 cùng với toán tử OR (+) 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 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 dạng hiện đại của Excel 2013, 2010 hoặc 2007, câu hỏi sử dụng những công thức mảng vào các trường hợp như vậy thực sự không quan trọng và một bí quyết SUMIFS solo giản hoàn toàn có thể cho ra thuộc một hiệu quả như trên. Mặc dù nhiên, toán tử and và OR trong những công thức mảng có thể hữu ích trong số tình huống tinh vi hơn, vấn đề đó liên quan đến sự việc luyện tập các ý tưởng phát minh cho não của người tiêu dùng : )

Toán tử solo vị 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 rất có thể bạn đang lướt qua một vài công thức có chứa hai lốt gạch ngang (–) và bạn có thể đã tự 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, gọi là toán tử đơn vị chức năng đôi, được sử dụng để chuyển các giá trị Boolean không ngay số (TRUE / FALSE) bởi một vài 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 phần đa thứ dễ nắm bắt hơn mang lại bạn. Giả sử bạn có một list ngày trong cột A và bạn có nhu cầu biết gồm 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 tiếp sau đây sẽ giải quyết và xử lý 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 xem xét một mon khác, hãy sửa chữa thay thế 1 bằng một vài 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 mon trong một số ô, như vào hình:

*

Và bây giờ, họ hãy cùng phân tích xem rằng công thức mảng Excel thao tác như cụ nào nhé. Hàm MONTH trả về tác dụng là từ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 trong những 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 đã được so sánh với cực hiếm trong ô D1, là số một trong ví dụ này. Tác dụng của phép đối chiếu này là một mảng những giá trị Boolean (TRUE cùng FALSE). Như bạn nhớ, thì chúng ta cũng có thể chọn một phần nhất định vào một bí quyết mảng và nhấn F9 giúp xem phần đó tương tự với:

Cuối cùng, các bạn phải chuyển đổi các giá trị Boolean này thành 1 với 0 nhằm 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à yêu cầu thiết. Các toán tử đối kháng vị đầu tiên sẽ làm cho TRUE / FALSE thành -1/0, tương ứng. Dòng thứ nhì thì sẽ bao phủ định giá bán trị, tức là đảo ngược dấu, biến đổi chúng thành +1 cùng 0, để mà hầu như các hàm của Excel rất có thể hiểu và thao tác làm việc với nó. Trường hợp bạn loại bỏ toán tử đơn vị chức năng đôi khỏi phương pháp trên, thì nó sẽ không hoạt động.

Rất nhiều 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 các bạn đều rất có thể học được trong 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 bí quyết đầy đủ, chi tiết. Rộng nữa không còn có giới hạn về thời hạn học tập nên bạn có thể thoải mái học bất kể lúc nào, dễ dãi tra cứu lại kỹ năng khi cần. Hiện nay hệ thống đang sẵn có ưu đãi hết sức lớn cho bạn khi đk tham gia khóa học.