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

Trong bài viết này, Học Excel Online sẽ  hướng dẫn công thức 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 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 ích và cũng khó 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ế hàng ngàn công thức thông thường. Tuy nhiên, ước chừ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ì họ sợ phải bắt đầ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 mở màn khám phá về hàm và những công thức mảng Excel, thì tất cả chúng ta hãy tìm hiểu và 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 những mục. Các mục hoàn toàn có thể là văn bản hoặc số và chúng hoàn toàn 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 sách mua hàng hàng tuần của 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 những ô 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ẽ nhận được tác dụng như sau :

Những gì bạn vừa làm là tạo một mảng một chiều theo chiều ngang. Nãy giờ mọi thứ vẫn rất đơn thuần, phải không nào ?
Bây giờ, công thức mảng Excel là gì ? Sự độc lạ giữa một hàm mảng và những hàm Excel thường thì khác là hàm mảng sẽ giải quyết và xử lý 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 nhận toàn bộ những giá trị riêng không liên quan gì đến nhau trong một mảng, và thực thi nhiều phép tính trên một hoặc nhiều mục mà có thỏa những điều kiện kèm theo được biểu lộ trong công thức .
Một công thức mảng không riêng gì giải quyết và xử lý đồng thời 1 số ít giá trị, mà nó cũng hoàn toàn có thể cùng 1 lúc trả lại 1 số ít những giá trị. Vì thế, những hiệu 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ộ những phiên bản của Excel 2013, Excel 2010, Excel 2007 và trước đó nữa .
Và giờ đây, có vẻ như như đã đến lúc bạn nên tạo công thức mảng tiên phong cho bạn .

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

Giả sử bạn có một số mục trong cột B, và cột C biểu thị giá của chúng, bài toán của bạn là tính tổng doanh thu .
Tất nhiên, không có gì ngăn cản bạn tính những tổng phụ ( Sub total ) trong mỗi hàng với một công thức đơn thuần như = B2 * C2, và rồi sau đó cộng những giá trị đó lại với nhau :

Tuy nhiên, một công thức 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 tàng trữ hiệu quả trung gian trong bộ nhớ, thay vì phải tạo thêm cột bổ trợ. Vì vậy, một công thức mảng đơn chỉ cần triển khai 2 bước nhanh gọn nho nhỏ như sau để xử lý 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 )

2. Nhấn phím tắt CTRL + SHIFT + ENTER để hoàn tất công thức mảng.

Khi nhấn tổng 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 tín hiệu dễ nhìn thấy cho biết đó 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 ( những ô từ B2 đến C6 ), có thêm những tổng ( phụ ), và hiệu quả ở đầu cuối là tổng doanh thu .

Ví dụ đơn thuần trên cho thấy sức mạnh của công thức mảng trong Excel. Khi thao tác với hàng trăm hoặc hàng ngàn hàng chứa tài liệu, thì bạn hoàn toàn có thể xem xét khi nó sẽ tiết kiệm ngân sách và chi phí bao nhiêu thời hạn với một công thức mảng trong mỗi ô .

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

• Tính tổng các số thỏa các điều kiện đã cho, ví dụ tính tổng N 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ứ N.
• Đếm các 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.

Xem ngay : những hàm thường dùng trong Excel

Làm thế nào để 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à một phương pháp kỳ diệu để biến một công thức thường thì 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 quan trọng mà bạn cần ghi nhớ :

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

Bởi vì tổng thể những công thức mảng trong Excel đều yên cầu phải nhấn tổng hợp phím Ctrl + Shift + Enter, nhiều lúc được gọi là công thức 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 với những công thức mảng trong Excel, bạn hoàn toàn có thể quan sát cách chúng đo lường và thống kê và tàng trữ những mục của mảng ( mảng nội bộ ) để hiển thị hiệu quả sau cuối 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 chính sách nhìn nhận công thức, hãy nhấn phím Esc .
Trong ví dụ trên, để xem những tổng phụ ( sub-total ) của tổng thể những loại sản phẩm, bạn cần chọn B2 : B6 * C2 : C6, rồi nhấn F9 và thu được hiệu quả như sau :

Lưu ý : Hãy quan tâm rằng bạn phải chọn 1 số ít phần của công thức trước khi nhấn phím F9, nếu không khi nhấn F9 thì nó sẽ chỉ đơn thuần thay thế sửa chữa công thức mảng của bạn bằng ( những ) giá trị đo lường và thống kê mà thôi .

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

Công thức mảng hoàn toàn có thể trả lại tác dụng 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 phong cách thiết kế để trả về những mảng đa ô, ví dụ như TRANSPOSE, TREND, FREQUENCY, LINEST, v.v.
Các hàm khác, ví dụ điển hình như SUM, AVERAGE, AGGREGATE, MAX, MIN, hoàn toàn có thể đo lường và thống kê biểu thức mảng khi 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 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 loại sản phẩm được bán trong 2 tháng khác nhau, ví dụ cột B và cột C, và bạn muốn tìm số lệch giá tăng lớn nhất hoàn toàn có thể .
Thông thường, bạn sẽ thêm một cột bổ trợ, ví dụ cột D, tính lệch giá biến hóa cho mỗi mẫu 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ổ trợ đó = MAX ( D : D ) .
Tuy nhiên, một công thức mảng Excel không cần một cột bổ trợ vì nó tàng trữ tuyệt vời và hoàn hảo nhất tổng thể những tác dụng trung gian trong bộ 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 )

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 muốn tính số tiền trả thuế cho mỗi mẫu sản phẩm chỉ với một công thức .
Trước tiên, 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 bạn trong mỗi ô trong dải đã chọn, và bạn sẽ nhận được hiệu quả trả về như sau :

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 cung ứng một vài cái gọi là “ những hàm mảng ” được phong cách thiết kế đặc biệt quan trọng để thao tác với những mảng đa ô. Hàm TRANSPOSE là một trong những hàm như vậy và chúng tôi sẽ sử dụng nó để quy đổi vị trí trong bảng ở trên, tức là chuyển hàng sang cột và ngược lại .

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

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

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

Khi thao tác với những công thức mảng đa ô trong Excel, hãy bảo vệ tuân theo những quy tắc dưới đây để có được tác dụng đúng chuẩn :

1. Chọn dải ô mà bạn 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 tất cả các ô chứa nó và nhấn DELETE, hoặc bạn phải chọn toàn bộ công thức trong thanh công thức, nhấn DELETE, và sau đó nhấn Ctrl + Shift + Enter.
3. Bạn không thể chỉnh sửa hoặc di chuyển nội dung của một ô riêng lẻ trong một công thức mảng của Excel, và bạn cũng không thể chèn các ô mới vào hoặc xóa các ô hiện tại từ một công thức mảng đa ô. Bất cứ khi nào bạn thử làm điều này, thì Microsoft Excel sẽ đều đưa ra cảnh báo 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 hiện tại 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 tất cả 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 nhấn F2 để chuyển sang chế độ chỉnh sửa, để điều chỉnh các tham chiếu trong công thức và nhấn Ctrl + Shift + Enter để cập nhật nó.
6. Bạn không thể sử dụng công thức mảng đa ô trong định dạng bảng Excel.
7. Bạn nên nhập một công thức mảng đa ô trong một dải ô có cùng kích cỡ với mảng có kết quả được trả về. Nếu công thức mảng trong 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ị vượt quá sẽ không xuất hiện 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 # N / A sẽ xuất hiện trong các ô bổ sung.

Nếu công thức của bạn hoàn toàn có thể trả về một mảng với một số lượng những thành phần hoàn toàn có thể biến hóa, 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 công thức 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 thuần là một tập những giá trị cố định và thắt chặt. Các giá trị này không khi nào đổi khác khi bạn sao chép công thức vào những ô hoặc những giá trị khác .
Bạn đã làm một ví dụ về mảng được tạo từ một list sản phẩm & hàng hóa cần mua trong phần đầu của bài hướng dẫn này. Bây giờ, tất cả chúng ta hãy cùng xem những loại mảng khác như thế nào và cách bạn hoàn toà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 những 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 }
Lưu ý. Khi tạo một hằng số mảng, bạn phải nhập bằng tay những 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 những ô 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 nối nhấn Ctrl + Shift + Enter. Kết quả sẽ giống 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, 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 ra nó bằng cách tựa như như một mảng ngang, với sự độc lạ duy nhất là 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 hai chiều

Để tạo một mảng hai chiều trong Excel, bạn tách những 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 }

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 tảng của những công thức mảng trong Excel. Các thông tin và mẹo nhỏ dưới đây hoàn toàn có thể giúp bạn sử dụng chúng một cách hiệu suất cao 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 hoàn toàn có thể chứa những số lượng, giá trị văn bản, giá trị Booleans ( TRUE và FALSE ) và những 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 hoàn toà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 những 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 không hề gồm có những mảng, tham chiếu ô, dải, ngày tháng, tên xác lập, công thức, hoặc những hàm trong Excel khác .

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

Để làm cho mảng trở nên dễ sử dụng hơn, hãy đặt cho nó một cái tên :

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

• 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à những mục trong mảng của 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ả đưa ra như sau :

3. Ngăn chặn lỗi

Nếu mảng của bạn không hoạt động giải trí được, hãy kiểm tra những yếu tố 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 – 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 bạn. Nếu bạn chọn nhiều ô hơn, thì mỗi ô phụ sẽ có lỗi # N / A. Nếu í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 công thức của Excel

Bây giờ bạn đã quen thuộc với khái niệm những 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 hoàn toàn có thể sử dụng những mảng trong những công thức của Excel để xử lý những thực tiễn việc làm của bạn .

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

Bạn mở màn bằng cách tạo một mảng dọc có chứa những số bạn muốn tính tổng. Ví dụ : nếu bạn 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 lập hàng loạt dải của ô trong tham số tiên phong 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ẽ nhận được hiệu quả như sau :

Tương tự như vậy, bạn hoàn toàn có thể tính giá trị trung bình của N giá 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ỏ nhất : = AVERAGE ( SMALL ( range, { 1,2,3 } ) )

Tham khảo thêm : Học Excel văn phòng với những 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 list đơn đặt hàng và bạn muốn biết người bán hàng đã bán được bao nhiêu lần mẫu sản phẩm được chỉ định .
Cách đơn thuần nhất sẽ sử dụng một công thức COUNTIFS với nhiều điều kiện kèm theo. Tuy nhiên, nếu bạn muốn đưa nhiều loại sản phẩm vào, thì công thức COUNTIFS của bạn hoàn toàn có thể sẽ quá dài. Để làm cho nó nhỏ gọn hơn, bạn hoàn toàn có thể sử dụng COUNTIFS cùng với SUM và gồm có 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ụ của chúng tôi có gồm có chỉ có hai yếu tố với tiềm năng là để đưa cho bạn chiêu thức tiếp cận. Thực tế, công thức mảng của bạn, hoàn toàn có thể gồm có nhiều yếu tố như phép ràng buộc logic, với nhu yếu chiều 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ở lại trước ) và máy tính của bạn đủ mạnh để giải quyết và xử lý 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 muốn giải quyết và xử lý 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 giá 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 đã cho là 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 doanh thu tương ứng mà người bán hàng là Mike và mẫu 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 toàn bộ những bán hàng ứng với người bán hàng là Mike hoặc loại sản phẩm là Apples :
= SUM ( IF ( ( ( A2 : A9 = “ Mike ” ) + ( B2 : B9 = “ Apples ” ) ), ( C2 : C9 ) ) )

Mách nhỏ : Trong những phiên bản văn minh của Excel 2013, 2010 hoặc 2007, việc sử dụng những công thức mảng trong những trường hợp như vậy thực sự không thiết yếu và một công thức SUMIFS đơn thuần hoàn toàn có thể cho ra cùng một tác dụng như trên. Tuy nhiên, toán tử AND và OR trong những công thức mảng hoàn toàn có thể hữu dụng trong những trường hợp phức tạp hơn, điều này tương quan đến việc rèn luyện những sáng tạo độc đáo cho não của bạn : )

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

Nếu bạn đã từng thao tá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 qua một vài công thức có chứa hai dấu gạch ngang ( – ) và bạn hoàn toà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ử đơn vị chức năng đôi, được sử dụng để chuyển những giá trị Boolean không bằng số ( TRUE / FALSE ) bởi một số ít biểu thức thành 1 và 0 – thứ mà một hàm mảng hoàn toàn có thể hiểu được .
Ví dụ sau kỳ vọng làm cho mọi thứ dễ hiểu hơn cho bạn. Giả sử bạn có một list ngày trong cột A và bạn muốn biết có 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 sau đây sẽ xử lý yếu tố 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 chăm sóc đến một tháng khác, hãy thay thế sửa chữa 1 bằng 1 số ít tương ứng. Ví dụ, 2 là Tháng 2, 3 có nghĩa là Tháng 3, v.v. Để làm cho công thức linh động hơn, thì bạn hoàn toàn có thể chỉ định số của Tháng trong 1 số ít ô, như trong hình :

Và bây giờ, chúng ta hãy cùng phân tích xem rằng công thức mảng Excel làm việc như thế nào nhé. Hàm MONTH trả về kết quả là mỗi 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 ra 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 này là một mảng những giá trị Boolean ( TRUE và FALSE ). Như bạn nhớ, thì bạn hoàn toàn có thể chọn một phần nhất đị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 quy đổi những giá trị Boolean này thành 1 và 0 để hàm SUM hoàn toàn có thể hiểu được. Và đây là những gì những toán tử đơn vị chức năng đôi cho là thiết yếu. Các toán tử đơn vị chức năng tiên phong 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à hầu hết những hàm của Excel hoàn toàn có thể hiểu và thao tác với nó. Nếu bạn vô hiệu toán tử đơn vị chức năng đôi khỏi công thức trên, thì nó sẽ không hoạt động giải trí .
Rất nhiều kỹ năng và kiến thức phải không nào ? Toàn bộ những kỹ năng và kiến thức này những bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên viên của Học Excel Online. Đây là khóa học giúp bạn mạng lưới hệ thống kỹ năng và kiến thức một cách vừa đủ, cụ thể. Hơn nữa không hề có số lượng giới hạn về thời hạn học tập nên bạn hoàn toàn có thể tự do học bất kỳ khi nào, thuận tiện tra cứu lại kỹ năng và kiến thức khi cần. Hiện nay mạng lưới hệ thống đang có khuyến mại rất lớn cho bạn khi ĐK tham gia khóa học .

Source: http://wp.ftn61.com
Category: Thủ Thuật