Tìm Hiểu Về Công Thức Mảng Trong Excel Là Gì ? Hướng Dẫn Và Ví Dụ Về Công Thức Mảng

Trong nội dung này, Học Excel Online sẽ chỉ dẫn phương pháp mảng Excel, phương pháp để nhập nó chuẩn xác trong bảng tính của các bạn, cách dùng các hằng số & các phương pháp mảng trong Excel.

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

Các phương pháp mảng trong Excel là một dụng cụ vô cùng hữu hiệu & cũng khó nhất để kiểm tra được nó. Một phương pháp mảng Excel đơn có thể thực hiện nhiều phép tính & thay thế hàng ngàn phương pháp thông thường. Bên cạnh đó, áng chừng đến 90% người dùng Excel chưa lúc nào sử dụng các phương pháp mảng trong bảng tính, chỉ vì họ sợ phải khởi đầ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 khởi đầu khám phá về hàm & các phương pháp mảng Excel, thì tất cả chúng ta hãy tham khảo rằng thế nào là thuật ngữ “mảng”. Về căn bản, một mảng trong Excel là một tập hợp các mục. Các mục có thể là văn bản hoặc số & chúng có thể nằm trong một hàng/cột, hoặc trong nhiều hàng & cột.

Ví dụ: nếu bạn nhập mục lục mua hàng hàng tuần của các 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 các ô từ A1 đến D1, thì hãy nhập mảng ở trên với dấu bằng (=) trên thanh phương pháp & nhấn CTRL + SHIFT + ENTER, & bạn sẽ thu được kết quả như sau:

Những gì bạn vừa làm là tạo một mảng 1 chiều theo chiều ngang. Nãy giờ mọi thứ vẫn không quá khó, phải không nào?

Hiện thời, phương pháp mảng Excel là gì? Sự độc đáo giữa một hàm mảng & các hàm Excel thông thường khác là hàm mảng sẽ giải quyết nhiều thay vì chỉ một giá trị mà thôi. Nói cách khác, một phương pháp mảng trong Excel sẽ nhận xét toàn bộ các giá trị riêng rẽ trong một mảng, & thực hiện 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 biểu thị trong phương pháp.

Một phương pháp mảng không những giải quyết song song một số giá trị, mà nó cũng có thể cùng một thời điểm trả lại một số các giá trị. Vì vậy, các kết quả trả lại bởi phương pháp mảng cũng là một mảng.

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

Và hiện giờ, có lẽ như đã tới lúc chúng ta nên tạo phương pháp mảng trước hết cho bạn.

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

Giả sử bạn có một số mục trong cột Ɓ, & cột ₵ biểu thị giá của chúng, bài toán của các bạn là tính tổng thu nhập.

Hẳn nhiên, không có gì ngăn cản bạn tính các tổng phụ (Sub total) trong mỗi hàng với một phương pháp dễ dàng như = B2 * C2, & rồi sau đó cộng các giá trị này lại với nhau:

*

Bên cạnh đó, một phương pháp mảng có thể đem đến cho bạn những điểm bột phát trọng yếu, từ khi nó được Excel dùng trong lưu trữ kết quả trung gian trong bộ nhớ lưu trữ, thay vì phải tạo thêm cột bổ sung. Chính vì thế, một phương pháp mảng đơn chỉ cần thực hiện 2 bước mau chóng be bé như sau để khắc phục bài toán trên:

Chọn một ô trống & nhập vào phương pháp sau:

Chọn một ô trống & nhập vào phương pháp 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.

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 phương pháp trên trong {dấu ngoặc nhọn}, hay được gọi một biểu hiện bắt mắt thấy cho biết này là một phương pháp 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 (các ô từ B2 đến C6), có thêm các tổng (phụ), & kết quả cuối cùng là tổng thu nhập.

*

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

Vì sao phải sử dụng phương pháp mảng trong Excel?

Công thức mảng Excel là dụng cụ tiện lợi nhất để thực hiện các phép tính tinh xảo & các tác vụ cầu kỳ. Một phương pháp mảng đơn có thể thay thế hàng trăm phương pháp thông thường. Công thức mảng có thể sử dụng cho các công việc như:

Tính tổng các số thỏa các điều kiện đã cho, chẳng hạn tính tổng И 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ứ И.Đếm các giá trị của toàn bộ hoặc đếm những ký tự khẳng định trong một dải xác nhận.

Làm cách nào để nhập phương pháp mảng trong Excel (Ctrl + Shift + Enter)

Tính tổng các số thỏa các điều kiện đã cho, chẳng hạn tính tổng И 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ứ И.Đếm các giá trị của toàn bộ hoặc đếm những ký tự khẳng định trong một dải xác nhận.

Như bạn đã biết, sử dụng tổ hợp phím CTRL + SHIFT + ENTER là một mẹo kỳ diệu để biến một phương pháp thông thường thành một phương pháp mảng.

Khi nhập một phương pháp mảng trong Excel, có 4 điều trọng yếu mà bạn cần ghi nhớ:

Một khi bạn đã giải quyết gõ phương pháp & song song nhấn các phím CTRL + SHIFT+ ENTER, thì Excel sẽ auto đặt phương pháp đó trong {dấu ngoặc nhọn}. Khi bạn chọn một/các ô như thế, bạn có thể thấy các dấu ngoặc nhọn trong thanh phương pháp, điều này cho bạn biết rằng có một phương pháp mảng trong đó.Việc nhập các dấu ngoặc nhọn bao quanh phương pháp bằng tay sẽ không chuyển phương pháp trên sang định dạng mảng. Bạn phải nhấn tổ hợp phím Ctrl + Shift + Enter để giải quyết phương pháp mảng.Mỗi lần bạn căn chỉnh một phương pháp mảng, các dấu ngoặc nhọn sẽ bặt tăm & bạn phải nhấn Ctrl + Shift + Enter để lưu các biến đổi trong phương pháp của các bạn.Nếu bạn quên nhấn Ctrl + Shift + Enter, thì phương pháp của các bạn sẽ hoạt động như một phương pháp Excel thông thường & chỉ giải quyết các giá trị trước hết trong mảng được chỉ định.

Một khi bạn đã giải quyết gõ phương pháp & song song nhấn các phím CTRL + SHIFT+ ENTER, thì Excel sẽ auto đặt phương pháp đó trong {dấu ngoặc nhọn}. Khi bạn chọn một/các ô như thế, bạn có thể thấy các dấu ngoặc nhọn trong thanh phương pháp, điều này cho bạn biết rằng có một phương pháp mảng trong đó.Việc nhập các dấu ngoặc nhọn bao quanh phương pháp bằng tay sẽ không chuyển phương pháp trên sang định dạng mảng. Bạn phải nhấn tổ hợp phím Ctrl + Shift + Enter để giải quyết phương pháp mảng.Mỗi lần bạn căn chỉnh một phương pháp mảng, các dấu ngoặc nhọn sẽ bặt tăm & bạn phải nhấn Ctrl + Shift + Enter để lưu các biến đổi trong phương pháp của các bạn.Nếu bạn quên nhấn Ctrl + Shift + Enter, thì phương pháp của các bạn sẽ hoạt động như một phương pháp Excel thông thường & chỉ giải quyết các giá trị trước hết trong mảng được chỉ định.

Xem Thêm  Cách tạo liên kết để tải xuống (các) tệp trong SharePoint - liên kết để tải xuống tệp

Bởi vì toàn bộ các phương pháp mảng trong Excel đều đòi hỏi phải nhấn tổ hợp phím Ctrl + Shift + Enter, đôi lúc được gọi là phương pháp CSE.

Sử dụng phím F9 để nhận xét các phần trong một phương pháp mảng:

Khi làm việc với các phương pháp mảng trong Excel, bạn có thể xem xét cách chúng tính toán & lưu trữ các mục của mảng (mảng nội bộ) để hiển thị kết quả cuối cùng 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 xét phương pháp, hãy nhấn phím Esc.

Trong chẳng hạn trên, để xem các 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 nhấn F9 & nhận được kết quả như sau:

*

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

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

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

Có một vài hàm mảng được kiến trúc để trả về các mảng đa ô, chẳng hạn như TRANSPOSE, TREND, FREQUENCY, LINEST, ?.?.

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

Các chẳng hạn sau minh hoạ cách dùng một phương pháp mảng một ô & nhiều ô trong Excel.

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

Giả sử bạn có hai cột liệt kê số lượng mặt hàng được bán trong 2 tháng khác nhau, chẳng hạn cột Ɓ & cột ₵, & bạn mong muốn tìm số thu nhập tăng lớn nhất có thể.

Thông thường, bạn sẽ thêm một cột bổ sung, chẳng hạn cột ?, tính thu nhập biến đổi cho mỗi sản phẩm, sử dụng phương pháp như = C2-B2, & sau đó tìm giá trị lớn nhất trong cột bổ sung đó = MAX (?: ?).

Bên cạnh đó, một phương pháp mảng Excel không cần một cột bổ sung vì nó lưu trữ hoàn hảo toàn bộ các kết quả trung gian trong bộ nhớ lưu trữ. Chính vì thế, bạn chỉ cần nhập phương pháp sau & 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 chẳng hạn trước của hàm SUM, giả sử bạn phải trả 10% thuế cho mỗi lần bán & bạn mong muốn tính số vốn trả thuế cho mỗi sản phẩm chỉ với một phương pháp.

Trước hết, bạn cần chọn dải ô trong một cột trống, ví như D2: D6, & nhập phương pháp sau vào thanh phương pháp:

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

Một khi bạn nhấn Ctrl + Shift + Enter, thì Excel sẽ đặt phương pháp mảng của các bạn trong mỗi ô trong dải đã chọn, & bạn sẽ thu được kết 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 phân phối một vài cái gọi là “các hàm mảng” được kiến trúc đặc biệt để làm việc với các mảng đa ô. Hàm TRANSPOSE là một trong những hàm như thế & http://phptravels.vn/ sẽ sử dụng nó để chuyển hóa địa điểm trong bảng ở trên, tức là chuyển hàng sang cột & trái lại.

Tham khảo thêm: Nhựa Plastic Là Gì ? Phân Loại Nhựa Tái Sinh Và Nguyên Sinh Nhựa Plastic Và Ứng Dụng Của Nó Là Gì

Chọn một dải các ô trống mà bạn mong muốn xuất ra bảng sau thời điểm chuyển hóa. Vì tất cả chúng ta đang chuyển hóa các hàng thành các cột, nên hãy chắc rằng rằng bạn chọn cùng một số hàng & cột như số các cột & các hàng tương ứng trong bảng nguồn (bảng gốc) bạn có. Trong chẳng hạn này, http://phptravels.vn/ đang lựa chọn 6 cột & 4 hàng.Nhấn F2 để vào chính sách căn chỉnh.Nhập phương pháp mảng: = TRANSPOSE (array) & nhấn Ctrl + Shift + Enter. Trong chẳng hạn trên, phương pháp là = TRANSPOSE ($?$1: $?$6).

Chọn một dải các ô trống mà bạn mong muốn xuất ra bảng sau thời điểm chuyển hóa. Vì tất cả chúng ta đang chuyển hóa các hàng thành các cột, nên hãy chắc rằng rằng bạn chọn cùng một số hàng & cột như số các cột & các hàng tương ứng trong bảng nguồn (bảng gốc) bạn có. Trong chẳng hạn này, http://phptravels.vn/ đang lựa chọn 6 cột & 4 hàng.Nhấn F2 để vào chính sách căn chỉnh.Nhập phương pháp mảng: = TRANSPOSE (array) & nhấn Ctrl + Shift + Enter. Trong chẳng hạn trên, phương pháp là = TRANSPOSE ($?$1: $?$6).

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

*

Làm cách nào để làm việc với các phương pháp mảng nhiều ô

Khi làm việc với các phương pháp mảng đa ô trong Excel, hãy bảo đảm tuân theo các nguyên tắc dưới đây để có được kết quả chuẩn xác:

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

Chọn dải ô mà bạn mong muốn xuất ra kết quả trước khi nhập phương pháp.Để xóa một phương pháp mảng đa ô, hoặc là bạn sẽ chọn toàn bộ các ô chứa nó & nhấn DELETE, hoặc bạn phải chọn toàn thể phương pháp trong thanh phương pháp, nhấn DELETE, & sau đó nhấn Ctrl + Shift + Enter.Bạn chẳng thể căn chỉnh hoặc di chuyển bài viết của một ô riêng rẽ trong một phương pháp mảng của Excel, & bạn cũng chẳng thể chèn các ô mới vào hoặc xóa các ô lúc này từ một phương pháp mảng đa ô. Bất kì bao giờ bạn thử làm điều này, thì Microsoft Excel sẽ đều đặt ra nhắc nhở(“Bạn không thể thay đổi một phần của mảng”).Để thu nhỏ phương pháp mảng, tức là vận dụng nó cho ít ô hơn, bạn cần phải xoá phương pháp lúc này trước rồi mới nhập một phương pháp mới vào.Để mở rộng phương pháp mảng, chẳng hạn vận dụng nó cho nhiều ô hơn, bạn cần chọn toàn bộ các ô có chứa phương pháp lúc này cộng với các ô rỗng mà bạn mong muốn có phương pháp trong đó, rồi nhấn F2 để chuyển sang chính sách căn chỉnh, để bố trí các tham chiếu trong phương pháp & nhấn Ctrl + Shift + Enter để update nó.Bạn chẳng thể sử dụng phương pháp mảng đa ô trong định dạng bảng Excel.Chúng ta nên nhập một phương pháp mảng đa ô trong một dải ô có cùng kích thước với mảng có kết quả được trả về. Nếu phương pháp mảng trong Excel của các bạn chứa một mảng mà to hơn dải bạn đã chọn, thì những giá trị vượt quá sẽ không hiện ra trên bảng tính. Nếu một mảng trả về bởi phương pháp nhỏ mà hơn dải đã chọn, lỗisẽ hiện ra trong các ô bổ sung.

Xem Thêm  13 cách căn giữa các phần tử HTML theo chiều dọc với CSS - css căn chỉnh tâm dọc

Nếu phương pháp của các bạn có thể trả về một mảng với một số lượng các phần tử có thể biến đổi, thì hãy nhập nó vào một dải bằng hoặc to hơn mảng lớn nhất mà được bởi phương pháp trả về & hãy lồng phương pháp của các 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ỉ dễ dàng là một tập các giá trị cố định. Các giá trị này không thay đổi khi bạn copy phương pháp vào các ô hoặc các giá trị khác.

Bạn đã làm một chẳng hạn về mảng được tạo từ một mục lục hàng hóa cần mua trong phần đầu của bài chỉ dẫn này. Hiện thời, tất cả chúng ta hãy cùng xem các loại mảng khác như vậy nào & cách bạ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 các giá trị được cách nhau bởi dấu phẩy, & đặt chúng trong dấu ngoặc nhọn, chẳng hạn: {1,2,3,4}

Cảnh báo. Khi tạo một hằng số mảng, bạn phải nhập bằng tay các dấu đóng & 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 các ô trống trong một hàng, & nhập phương pháp = {1,2,3,4} trong thanh phương pháp, rồi tiếp theo 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 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 nên nó bằng cách cũng giống như một mảng ngang, với sự độc đáo duy đặc biệt là bạn ngăn cách các mục bằng dấu chấm phẩy, chẳng hạn: = {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 các mục trong mỗi hàng ra bằng dấu phẩy, & 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 móng của các phương pháp mảng trong Excel. Các thông tin & phương thức nhỏ dưới đây có thể giúp bạn sử dụng chúng một cách tốt 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 có thể chứa các con số, giá trị văn bản, giá trị Booleans (TRUE & FALSE) & các 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 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 các giá trị văn bản, chúng sẽ được đặt trong dấu nháy kép (“”) như trong bất kỳ phương pháp Excel nào.

Hằng số mảng chẳng thể bao gồm các mảng, tham chiếu ô, dải, ngày tháng, tên xác nhận, phương pháp, hoặc các hàm trong Excel khác.

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

Để khiến cho mảng trở nên dễ sử dụng hơn, hãy đặt cho nó một tên gọi:

Đi đến tab Formulas > nhóm Defined Names & nhấn Define Name. Hoặc là nhấn Ctrl + F3 & nhấp vàoNhập tên bạn mong muốn đặt vào ô NameTrong 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ụ:

Đi đếnvàgt; nhómvà nhấn. Hoặc là nhấn Ctrl + F3 & nhấp vàoNhập tên bạn mong muốn đặt vàoTrong hộp, 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 & đó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à các mục trong mảng của các 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 & nhấn Ctrl + Shift + Enter.

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

*

3. Ngăn chặn lỗi

Nếu mảng của các bạn không hoạt động được, hãy kiểm soát 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ự phù hợp – dấu phẩy với hằng số của mảng ngang & 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 các bạn. Nếu bạn chọn nhiều ô hơn, thì mỗi ô phụ sẽ có lỗi # И / ?. Nếu thấp 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 phương pháp của Excel

Ngăn cách các thành phần của hằng số mảng với các ký tự phù hợp – dấu phẩy với hằng số của mảng ngang & 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 các bạn. Nếu bạn chọn nhiều ô hơn, thì mỗi ô phụ sẽ có lỗi # И / ?. Nếu thấp hơn, thì chỉ một phần của mảng sẽ được xuất ra.

Hiện thời bạn đã thân thuộc với định nghĩa các 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 có thể sử dụng các mảng trong các phương pháp của Excel để khắc phục các thực tiễn công việc của các bạn.

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

Bạn khởi đầu bằng cách tạo một mảng dọc có chứa những số bạn mong muốn tính tổng. Ví dụ: nếu bạn mong 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}.

Xem Thêm  How to view the HTML source code of a web page - view source

Sau đó, bạn sử dụng một trong hai hàm phương pháp sau LARGE hoặc SMALL, xác nhận toàn thể dải của ô trong tham số trước hết & đặ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 phương pháp mảng, & bạn sẽ thu được kết quả như sau:

*

Cũng giống như vậy, bạn có thể tính giá trị bình quân của И giá trị nhỏ nhất hoặc lớn nhất trong một dải:

Bình quân của 3 số lớn nhất: = AVERAGE (LARGE (range, {1,2,3}))

Bình quân của 3 số nhỏ nhất: = AVERAGE (SMALL (range, {1,2,3}))

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 mục lục đơn đặt mua & bạn mong muốn biết người bán hàng đã bán được bao nhiêu lần sản phẩm được chỉ định.

Cách dễ dàng nhất sẽ sử dụng một phương pháp COUNTIFS với nhiều điều kiện. Bên cạnh đó, nếu bạn mong muốn mang nhiều sản phẩm vào, thì phương pháp COUNTIFS của các bạn có thể sẽ quá dài. Để khiến cho nó nhỏ gọn hơn, bạn có thể sử dụng COUNTIFS cùng với SUM & bao gồm một hằng số mảng trong một hoặc một vài đối số, chẳng hạn:

= 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 chẳng hạn của http://phptravels.vn/ có bao gồm chỉ có hai yếu tố với mục tiêu là để mang cho bạn bí quyết tiếp cận. Thực tiễn, phương pháp mảng của các bạn, có thể bao gồm nhiều yếu tố như phép ràng buộc logic, với yêu cầu bề dài của phương pháp không vượt quá 8,192 ký tự trong Excel 2013, 2010 & 2007 (1,024 ký tự trong Excel 2003 trở về trước) & laptop của các bạn đủ mạnh để giải quyết mảng lớn.

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

Một toán tử của mảng cho thấy cách bạn mong muốn giải quyết mảng – sử dụng hàm logic AND & OR.

Toán tử AND là dấu hoa thị (*). Nó chỉ dẫn Excel trả về TRUE nếu TẤT CẢ các điều kiện được nhìn nhận 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 rằng TRUE.

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

Toán tử AND là dấu hoa thị (*). Nó chỉ dẫn Excel trả về TRUE nếu TẤT CẢ các điều kiện được nhìn nhận 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 rằng TRUE.

Trong chẳng hạn này, http://phptravels.vn/ cho biết tổng thu nhập tương ứng mà người bán hàng là Mike & 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 (+) cho phép thêm toàn bộ các bán hàng ứng với người bán hàng là Mike hoặc sản 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 phương pháp mảng trong các tình huống như thế thực sự không thiết yếu & một phương pháp SUMIFS dễ dàng có thể cho ra cùng một kết quả như trên. Bên cạnh đó, toán tử AND & OR trong các phương pháp mảng có thể hữu hiệu trong các tình huống cầu kỳ hơn, điều này liên quan tới việc luyện tập các sáng kiến cho não của các bạn : )

Toán tử nhà cung cấp đôi điều hành trong phương pháp mảng của Excel

Nếu bạn đã từng làm việc với các phương pháp mảng trong Excel, rất có thể bạn đã lướt qua một vài phương pháp có chứa hai dấu gạch ngang (–) & bạ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ử nhà cung cấp đôi, được sử dụng để 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 & 0 – thứ mà một hàm mảng có thể hiểu được.

Ví dụ sau mơ ước khiến cho mọi thứ dễ hiểu hơn cho bạn. Giả sử bạn có một mục lục ngày trong cột ? & bạn mong muốn biết có bao nhiêu ngày xảy ra hồi tháng Giêng, bất kì năm nào.

Các phương pháp sau đây sẽ khắc phục vấn đề trên:

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

Vì đây là phương pháp mảng Excel, hãy nhớ nhấn Ctrl + Shift + Enter để hoàn tất.

Nếu bạn quan tâm đến một tháng khác, hãy thay thế 1 bằng một số tương ứng. Ví dụ, 2 là Tháng 2, 3 có nghĩa là Tháng 3, ?.?. Để khiến cho phương pháp linh động hơn, thì bạn có thể chỉ định số của Tháng trong một số ô, như trong hình:

*

Và hiện giờ, tất cả chúng ta hãy cùng nghiên cứu xem rằng phương pháp mảng Excel làm việc như vậy nào nhé. Hàm MONTH trả về kết quả là hàng ngày trong các ô A2 đến A10 nằm vào tháng nào, chúng sẽ được biểu thị bởi một số sê-ri, tạo nên mảng {2; 1; 4; 2; 12; 1; 2; 12; 1}.

Tham khảo thêm: Game Plants Vs Zombies 2 4H, Game Trồng Cây Bắn Ma, Tro Choi Trong Cay Ban Ma

Sau đó, mỗi phần của mảng sẽ được so sánh với giá trị trong ô D1, là số 1 trong chẳng hạn này. Kết quả của phép so sánh đó là một mảng các giá trị Boolean (TRUE & FALSE). Như bạn nhớ, thì bạn có thể chọn một phần khẳng định trong một phương pháp mảng & nhấn F9 để xem phần đó tương tự với:

Cuối cùng, bạn phải chuyển hóa các giá trị Boolean này thành 1 & 0 để hàm SUM có thể hiểu được. Và đây là những gì các toán tử nhà cung cấp đôi cho rằng thiết yếu. Các toán tử nhà cung cấp trước hết 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 & 0, để mà đa phần các hàm của Excel có thể hiểu & làm việc với nó. Nếu bạn loại bỏ toán tử nhà cung cấp đôi khỏi phương pháp trên, thì nó sẽ không hoạt động.

Rất là nhiều học thức phải không nào? Toàn thể những học thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ că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 học thức một cách đầy đủ, cụ thể. Hơn nữa chẳng hề có hạn chế về thời gian học hành nên bạn có thể dễ chịu học bất kể bao giờ, đơn giản tra cứu lại học thức khi cần. Hiện tại hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký gia nhập khóa học. Cụ thể xem tại: hufa.edu.vn.Online

Viết một bình luận