Công thức mảng trong excel 2010

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

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

Các công thức mảng vào Excel là 1 trong công cố gắng vô cùng hữu dụng và cũng cực nhọc nhất để kiểm soát được nó. Một công thức mảng Excel đơn hoàn toàn có thể thực hiện nhiều phép tính và sửa chữa hàng ngàn công thức 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 vì họ sợ phải ban đầu học 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 vào Excel?Công thức mảng với cùng một ô và các ô trong 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 công thức của ExcelCác toán tử & và OR trong bí quyết mảng của Excel 

Hàm mảng vào Excel là gì?

Trước khi bọn chúng ta bắt đầu tìm hiểu về hàm và những công thức mảng Excel, thì bọn họ hãy mày mò rằng cầm nào là thuật ngữ “mảng”. Về cơ bản, một mảng vào Excel là 1 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ố với chúng rất có thể nằm trong một hàng/cột, hoặc trong nhiều hàng cùng cột.

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

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

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

*


*

*

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

Bây giờ, bí quyết mảng Excel là gì? Sự biệt lập giữa một hàm mảng và những hàm Excel thường thì khác là hàm mảng đã xử lý những thay vày chỉ một giá bán trị nhưng mà thôi. Nói biện pháp khác, một bí quyết mảng vào Excel sẽ nhận xét tất cả những giá trị biệt lập 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à gồm 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 chỉ xử lý đồng thời một số giá trị, cơ mà nó cũng có thể cùng 1 thời điểm trả lại một số trong những các giá chỉ trị. Bởi thế, các hiệu quả trả lại bởi công thức mảng cũng là một trong mảng.

Công thức mảng tất 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 buộc phải tạo phương pháp mảng trước tiên cho bạn.

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

Giả sử bạn có một vài mục trong cột B, với cột C biểu hiện giá của chúng, bài bác toán của người sử dụng là tính tổng doanh thu.

Tất nhiên, không có gì chống cản chúng ta 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ới rồi kế tiếp cộng các giá trị này lại với nhau:

*

Tuy nhiên, một phương pháp mảng rất có thể mang đến cho bạn những điểm cải tiến vượt bậc quan trọng, từ khi nó được Excel dùng trong lưu giữ trữ hiệu quả trung gian trong cỗ nhớ, vậy vì phải khởi tạo thêm cột té sung. Vì vậy, một công thức mảng đơn chỉ việc thực hiện tại 2 bước gấp rút 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 cùng nhập vào bí quyết sau:

= SUM (B2: B6 * C2: C6)

Nhấn phím tắt CTRL + SHIFT + ENTER nhằm hoàn tất bí quyết mảng.

Khi nhấn tổng hợp phím trên, Microsoft Excel đặt cách làm trên trong dấu ngoặc nhọn, tốt được điện thoại tư vấn một dấu hiệu dễ quan sát thấy cho biết thêm đó là 1 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 chỉ định và hướng dẫn (các ô từ B2 mang đến C6), bao gồm thêm những tổng (phụ), và công dụng 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 mẽ của công thức mảng vào Excel. Khi thao tác làm việc với hàng ngàn hoặc hàng vạn hàng đựng dữ liệu, thì chúng ta cũng 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 bí quyết mảng trong mỗi ô.

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 lợi nhất để triển khai các phép tính tinh tướng và các tác vụ phức tạp. Một cách làm mảng đơn rất có thể thay thế hàng ngàn công thức thông thường. Phương pháp mảng hoàn toàn có thể sử dụng mang đến các công việc như:

Tính tổng các số thỏa những điều kiện vẫn cho, lấy ví dụ tính tổng N giá bán trị lớn số 1 hoặc bé dại nhất vào một dải.Tính tổng các giá trị những hàng, hoặc vào hàng/cột thiết bị N.Đếm những giá trị của tất cả hoặc đếm đều ký tự nhất mực 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ư bạn đã biết, sử dụng tổng hợp phím CTRL + SHIFT + ENTER là 1 trong những phương thức vi diệu để biến một công thức thông thường thành một cách làm mảng.

Khi nhập một công thức mảng vào Excel, tất cả 4 điều quan trọng đặc biệt mà bạn cần ghi nhớ:

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

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

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

Trong lấy một ví dụ trên, nhằm xem các 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 nhấn F9 và thu được kết quả như sau:

*

Lưu ý: Hãy lưu ý rằng các bạn phải lựa chọn một số phần của công thức trước lúc nhấn phím F9, còn nếu không khi dấn F9 thì nó vẫn chỉ đơn giản và 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 1 ô và các ô trong Excel

Công thức mảng rất có thể trả lại công dụng trong một ô hoặc trong nhiều ô. Một cách làm mảng được nhập vào trong một dải ô được call là phương pháp đa ô. Phương pháp mảng nằm trong một ô nhất được gọi là công thức ô đơn.

Có một vài 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, chẳng hạn như SUM, AVERAGE, AGGREGATE, MAX, MIN, hoàn toàn có thể tính toán biểu thức mảng khi nhập vào một ô độc nhất bằng tổ hợp phím Ctrl + Shift + Enter.

Các lấy ví dụ như sau minh hoạ cách sử dụng một phương pháp mảng một ô và các ô vào Excel.

Ví dụ 1. Một phương pháp mảng cùng với ô đơn

Giả sử bạn có nhì cột liệt kê số lượng món đồ được chào bán trong 2 tháng khác nhau, lấy một ví dụ cột B và 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, các bạn sẽ thêm một cột ngã sung, ví dụ cột D, tính doanh thu biến hóa cho từng 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 số 1 trong cột bổ sung đó = MAX (D: D).

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

= MAX (C2: C6-B2: B6)

*

Ví dụ 2. Công thức 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 buôn bán và bạn muốn 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 cần chọn dải ô vào một cột trống, ví như D2: D6, cùng nhập phương pháp sau vào thanh công thức:

= B2: B6 * C2: C6 * 0,1

Một khi chúng ta nhấn Ctrl + Shift + Enter, thì Excel sẽ đặt phương pháp mảng của công ty trong từng ô trong dải đã chọn, và các 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ư đã đề cập, thì Microsoft Excel hỗ trợ một vài dòng gọi là “các hàm mảng” được thiết kế đặ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ậy và công ty chúng tôi sẽ áp dụng nó để biến hóa vị trí vào bảng nghỉ ngơi trên, có nghĩa là chuyển mặt hàng sang cột cùng ngược lại.

Xem thêm: Phim 108 Anh Hùng Lương Sơn Bạc Tap 30, Tân Thủy Hử, Tân Thủy Hử

Chọn một dải những ô 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 biến hóa các mặt hàng thành các cột, phải hãy chắc hẳn rằng rằng bạn chọn cùng một trong những hàng và cột như số các cột và các hàng khớp ứng trong bảng nguồn (bảng gốc) bạn có. Trong ví dụ như này, công ty chúng tôi đang lựa chọn 6 cột cùng 4 hàng.Nhấn F2 để vào chính sách chỉnh sửa.Nhập công thức mảng: = TRANSPOSE (array) cùng nhấn Ctrl + Shift + Enter. Trong ví dụ trên, cách làm là = TRANSPOSE ($A$1: $D$6).

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

*

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

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

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

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

Bạn đã có tác dụng một lấy một ví dụ về mảng được tạo từ 1 danh sách sản phẩm & hàng hóa cần tải trong phần đầu của bài xích hướng dẫn này. Bây giờ, chúng ta hãy thuộc xem các 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 nhiều 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ỉ vào một hàng. Để tạo một hằng số mảng hàng, hãy nhập các giá trị được bí quyết nhau do dấu phẩy, và đặt chúng trong lố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, các bạn phải nhập thủ công các lốt đóng cùng 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 lựa số tương ứng của các ô 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 sau đó nhấn Ctrl + Shift + Enter. Hiệu quả sẽ hệt như sau:

*

Như các 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 hệt như khi ai đ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 thành nó bằng cách tương tự như 1 mảng ngang, cùng với sự khác biệt duy tuyệt nhất là bạn ngăn cách những mục bởi dấu chấm phẩy, ví dụ: = 11; 22; 33; 44

*

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

Để sinh sản một mảng hai phía 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ở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à giữa những nền tảng của các công thức mảng trong Excel. Những thông tin cùng 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 tác 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 hoàn toàn 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 bóc tách bằng vệt phẩy hoặc giấ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 bạn sử dụng những giá trị văn bản, chúng sẽ tiến hành đặ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 vào Excel khác.

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

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

Đi mang lại tab Formulas > nhóm Defined Names với nhấn Define Name. Hoặc là thừa nhận Ctrl + F3 và 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 để trong dấu ngoặc nhọn, với dấu bởi ở trước (=). Ví dụ:

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

*

Nhấn OK để lưu mảng đang đặt tên và ngừng hoạt động sổ.

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

Kết quả chỉ dẫn như sau:

*

3. Ngăn chặn lỗi

Nếu mảng của bạn không chuyể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 – vết phẩy cùng 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ó 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 những ô hơn, thì từng ô phụ sẽ sở hữu lỗi # N / A. Giả dụ ít hơn, thì chỉ một trong những 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ờ chúng ta đã không còn xa lạ với khái niệm những hằng số của mảng, nên chúng ta hãy thuộc xem bí quyết 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 và xử lý các thực tế các bước của bạn.

Ví dụ 1. Cộng N những số lớn số 1 / nhỏ dạ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 những 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 nhất trong một dải, thì hằng số mảng vẫn là 1,2,3.

Sau đó, bạn sử dụng 1 trong hai hàm phương pháp sau LARGE hoặc SMALL, xác định toàn cục dải của ô vào tham số đầu tiên và để hằng số mảng vào tham số máy 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ỏ dại nhất 3 số: = SUM (SMALL (range, 1,2,3))

Đừng quên thừa nhận Ctrl + Shift + Enter khi chúng ta nhập bí quyết 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 cực hiếm trung bình của N giá trị bé dại nhất hoặc lớn nhất trong một dải:

Trung bình của 3 số khủng 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ách làm mảng nhằm tính các ô 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 tốt bao nhiêu lần sản phẩm được chỉ định.

Cách dễ dàng và đơn giản nhất sẽ sử dụng một bí quyết COUNTIFS với tương đối 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ì bí quyết COUNTIFS của bạn có thể sẽ thừa dài. Để tạo cho nó bé 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 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 hoàn toàn có thể trông như sau:

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

*

Trong mảng ví dụ như của cửa hàng chúng tôi có bao gồm chỉ bao gồm hai nguyên tố với mục tiêu là để lấy cho bạn phương 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 ràng buộc logic, với yêu mong chiều lâu năm của bí quyết không vượt vượt 8,192 ký tự trong Excel 2013, 2010 cùng 2007 (1,024 cam kết tự trong Excel 2003 quay trở lại trước) cùng máy tính của bạn đủ bạo phổi để cách xử lý mảng lớn.

Các toán tử and và OR trong bí quyết mảng của Excel

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

Toán tử và là vết hoa thị (*). Nó lý giải Excel trả về TRUE trường hợp TẤT CẢ những điều khiếu nại được đánh giá 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 đã cho rằng TRUE.

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

Trong ví dụ này, chúng tôi cho thấy tổng doanh thu tương ứng mà 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 với toán tử OR

Công thức mảng sau với toán tử OR (+) có thể chấp nhận được thêm toàn bộ các bán sản phẩm ứ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 những phiên bản 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 vào các trường hợp như vậy đích thực không quan trọng và một cách làm SUMIFS đối chọi giản rất có thể cho ra cùng một hiệu quả như trên. Mặc dù nhiên, toán tử and và OR trong số công thức mảng hoàn toàn có thể hữu ích trong các tình huống phức tạp hơn, điều đó liên quan đến việc luyện tập các phát minh cho não của doanh nghiệp : )

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

Nếu các bạn đã 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 1 vài công thức gồm chứa hai lốt gạch ngang (–) và chúng ta cũng có thể đã từ hỏi rằng nó được thực hiện cho những câu hỏi gì.

Hai vệt gạch ngang đôi, điện thoại tư vấn là toán tử đơn vị đôi, được áp 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ột hàm mảng hoàn toàn có thể hiểu được.

Ví dụ sau mong muốn làm cho đầy đủ thứ dễ nắm bắt hơn đến bạn. Mang sử bạn có một list ngày trong cột A và bạn muốn biết tất cả bao nhiêu ngày xảy ra trong 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à xử lý vấn đề trên:

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

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

Nếu bạn lưu ý đến một tháng khác, hãy thay thế sửa chữa 1 bằng một số trong những tương ứng. Ví dụ, 2 là mon 2, 3 tức là Tháng 3, v.v. Để tạo cho công thức linh động hơn, thì chúng ta 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ờ, họ hãy thuộc phân tích coi rằng bí quyết mảng Excel thao tác làm việc như nỗ lực nào nhé. Hàm MONTH trả về hiệu quả là hằng ngày trong những ô A2 mang lại A10 nằm hồi tháng nào, bọn chúng sẽ được bộc lộ bởi một vài sê-ri, tạo ra mảng 2; 1; 4; 2; 12; 1; 2; 12; 1.

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

Cuối cùng, chúng ta phải biến hóa các cực hiếm Boolean này thành 1 với 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 mang lại là nên thiết. Những toán tử đối chọi vị đầu tiên sẽ làm cho TRUE / FALSE thành -1/0, tương ứng. Dòng thứ nhị thì sẽ đậy định giá chỉ trị, có nghĩa là đảo ngược dấu, đổi mới chúng thành +1 và 0, nhằm mà số đông các hàm của Excel hoàn toàn có thể hiểu và làm việc với nó. Ví như bạn loạ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 không nào? toàn cục những kỹ năng này các bạn đều rất có thể học được trong khóa học EX101 – Excel trường đoản cú cơ bản tới chuyên gia của học tập Excel Online. Đây là khóa học giúp cho bạn hệ thống kỹ năng một biện pháp đầy đủ, bỏ ra 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ó thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu vớt lại kỹ năng và kiến thức khi cần. Bây chừ hệ thống đang xuất hiện ưu đãi vô cùng lớn cho bạn khi đăng ký tham gia khóa học.