Hướng dẫn và ví dụ về công thức mảng

Công thức mảng là công thức có thể thực hiện nhiều phép tính đối với một hoặc nhiều mục trong mảng. Bạn có thể hình dung mảng là một hàng hoặc một cột giá trị hoặc là một kết hợp các hàng và cột giá trị. Công thức mảng có thể trả về nhiều kết quả hoặc một kết quả duy nhất.

Bắt đầu từ bản cập nhật tháng 9 năm 2018 cho Microsoft 365, mọi công thức có thể trả về nhiều kết quả đều sẽ tự động tràn kết quả xuống hoặc tràn sang các ô bên cạnh. Thay đổi hành vi này cũng đi kèm với một vài hàm mảng động mới. Công thức mảng động, dù đang sử dụng các hàm hiện có hay các hàm mảng động, chỉ cần được nhập vào một ô đơn, rồi được xác nhận bằng cách nhấn Enter. Trước đây, công thức mảng thừa tự yêu cầu trước hết phải chọn toàn bộ phạm vi đầu ra, rồi xác nhận công thức bằng Ctrl+Shift+Enter. Chúng thường được gọi là công thức CSE.

Bạn có thể dùng công thức mảng để thực hiện các tác vụ phức tạp, chẳng hạn như:

Nhanh chóng tạo tập dữ liệu mẫu.
Đếm số ký tự chứa trong một phạm vi ô.
Chỉ tính tổng các số đáp ứng một số điều kiện nhất định, chẳng hạn như các giá trị thấp nhất trong một phạm vi hoặc các số nằm giữa một giới hạn trên và giới hạn dưới nào đó.
Tính tổng mọi giá trị thứ N trong phạm vi giá trị.

Các ví dụ sau sẽ cho bạn thấy cách tạo các công thức mảng đa ô và đơn ô. Nếu có thể, chúng tôi đã đưa vào các ví dụ với một số hàm mảng động, cũng như các công thức mảng hiện có được nhập dưới dạng cả mảng động và mảng thừa tự.

Tải xuống ví dụ của chúng tôi

Tải xuống sổ làm việc ví dụ với tất cả các ví dụ về công thức mảng trong bài viết.

Microsoft 365 Windows

Bài tập này sẽ cho bạn thấy cách dùng các công thức mảng đa ô và đơn ô để tính toán bộ số liệu doanh số bán hàng. Bộ các bước đầu tiên dùng công thức đa ô để tính toán các tổng phụ. Bộ thứ hai dùng công thức đơn ô để tính toán tổng cộng.

Công thức mảng đa ô
Ở đây, chúng ta tính toán Tổng Doanh thu của các loại xe coupes và sedan cho từng nhân viên bán hàng bằng cách nhập =F10:F19*G10:G19 ô H10.

Khi nhấn Enter, bạn sẽ thấy kết quả tràn xuống các ô H10:H19. Lưu ý rằng phạm vi tràn sẽ được tô sáng bằng viền khi bạn chọn ô bất kỳ trong phạm vi tràn. Bạn cũng có thể nhận thấy rằng công thức trong các ô H10:H19 bị mờ đi. Chúng chỉ ở đó để tham chiếu, vì vậy nếu muốn điều chỉnh công thức, bạn sẽ cần chọn ô H10, nơi chứa công thức chính.
Công thức mảng ô đơn

Trong ô H20 của sổ làm việc ví dụ, nhập hoặc sao chép và dán =SUM(F10:F19*G10:G19), rồi nhấn Enter.

Trong trường hợp này, Excel nhân các giá trị trong mảng (phạm vi ô từ F10 đến hết G19), rồi dùng hàm SUM để cộng các tổng số với nhau. Kết quả tổng cộng doanh số bán hàng là $1.590.000.

Ví dụ này cho thấy kiểu công thức này có hiệu quả tới mức nào. Ví dụ, giả sử bạn có 1.000 hàng dữ liệu. Bạn có thể cộng một phần hoặc tất cả các dữ liệu đó với nhau bằng cách tạo công thức mảng trong một ô duy nhất thay vì kéo công thức xuống qua hết 1.000 hàng. Ngoài ra, hãy lưu ý rằng công thức đơn ô trong ô H20 hoàn toàn độc lập với công thức đa ô (công thức trong các ô từ H10 đến H19). Đây là một ưu điểm khác của việc dùng công thức mảng — tính linh hoạt. Bạn có thể thay đổi các công thức khác trong cột H mà không ảnh hưởng đến công thức trong ô H20. Bạn cũng có thể thực hiện tốt để có các tổng độc lập như thế này, vì nó giúp xác thực độ chính xác của kết quả của bạn.
Công thức mảng động còn có các ưu điểm sau đây:
- Tính nhất quán Nếu bạn bấm bất kỳ ô nào từ H10 trở xuống, bạn sẽ nhìn thấy cùng một công thức. Sự nhất quán đó có thể giúp đảm bảo độ chính xác lớn hơn.
- An toàn Bạn không thể ghi đè thành phần của một công thức mảng đa ô. Ví dụ, hãy bấm ô H11 và nhấn Delete. Excel sẽ không thay đổi kết quả của mảng. Để thay đổi, bạn cần chọn ô trên cùng bên trái trong mảng hoặc ô H10.
- Kích cỡ tệp nhỏ hơn Bạn có thể thường xuyên dùng một công thức mảng duy nhất thay vì nhiều công thức trung gian. Ví dụ, ví dụ bán xe dùng một công thức mảng để tính toán kết quả ở cột E. Nếu bạn dùng công thức chuẩn chẳng hạn như =F10*G10, F11*G11, F12*G12, v.v. thì bạn phải dùng 11 công thức khác nhau để tính toán cùng kết quả đó. Đó không phải là vấn đề quan trọng nhưng nếu bạn có hàng nghìn hàng để tính tổng thì sao? Khi đó, nó có thể tạo ra sự khác biệt lớn.
- Hiệu quả Hàm mảng có thể là một cách hiệu quả để xây dựng công thức phức tạp. Công thức mảng =SUM(F10:F19*G10:G19) giống như thế này: =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).
- Tràn Công thức mảng động sẽ tự động tràn vào phạm vi đầu ra. Nếu dữ liệu nguồn của bạn nằm trong một bảng Excel thì công thức mảng động của bạn sẽ tự động đổi kích cỡ khi bạn thêm hoặc loại bỏ dữ liệu.
- Lỗi #SPILL! Mảng động đã giới thiệu Lỗi #SPILL!, điều này cho biết phạm vi tràn dự kiến sẽ bị chặn vì một số lý do. Khi bạn giải quyết khối lượng, công thức sẽ tự động tràn.

Hằng số mảng là một thành phần của công thức mảng. Bạn tạo hằng số mảng bằng cách nhập danh sách các mục, sau đó đặt danh sách này trong một cặp dấu ngoặc nhọn ({ }) theo cách thủ công, như thế này:

={1\2\3\4\5} hoặc ={"tháng 1"\"tháng 2"\"tháng 3"}

Nếu bạn phân cách các mục bằng dấu phẩy, bạn sẽ tạo ra một mảng ngang (hàng). Nếu bạn phân cách các mục bằng dấu chấm phẩy, bạn sẽ tạo ra một mảng dọc (cột). Để tạo mảng hai chiều, bạn hãy phân cách các mục trong mỗi hàng bằng dấu phẩy và phân cách các hàng bằng dấu chấm phẩy.

Quy trình sau đây sẽ giúp bạn thực hành việc tạo các hằng số ngang, dọc và hai chiều. Chúng tôi sẽ hiển thị các ví dụ bằng cách sử dụng hàm SEQUENCE để tự động tạo hằng số mảng, cũng như nhập thủ công các hằng số mảng.

Tạo hằng số ngang

Hãy dùng sổ làm việc từ các ví dụ trên đây hoặc tạo một sổ làm việc mới. Chọn ô trống bất kỳ và nhập =SEQUENCE(1,5). Hàm SEQUENCE xây dựng một hàng 1 cho 5 cột mảng giống như ={1\2\3\4\5}. Kết quả sau đây được hiển thị:
Tạo hằng số dọc

Chọn một ô trống bất kỳ có chỗ bên dưới nó và nhập =SEQUENCE(5), hoặc ={1;2;3;4;5}. Kết quả sau đây được hiển thị:
Tạo hằng số hai chiều

Chọn ô trống bất kỳ có chỗ bên phải và bên dưới nó, rồi nhập =SEQUENCE(3,4). Bạn sẽ thấy kết quả sau đây:

Bạn cũng có thể nhập: hoặc ={1\2\3\4;5\6\7\8;9\10\11\12}, nhưng bạn sẽ muốn chú ý đến vị trí đặt dấu chấm phẩy so với dấu phẩy.

Như bạn có thể thấy, tùy chọn SEQUENCE có những lợi thế đáng kể so với việc nhập thủ công các giá trị hằng số mảng của bạn. Chủ yếu, việc này giúp tiết kiệm thời gian của bạn nhưng còn có thể giúp giảm lỗi từ mục nhập thủ công. Cũng dễ đọc hơn, đặc biệt là dấu chấm phẩy có thể khó phân biệt với dấu tách bằng dấu phẩy.

Đây là ví dụ dùng hằng số mảng như một phần của công thức lớn hơn. Trong sổ làm việc mẫu, hãy đến trang tính Hằng số trong công thức hoặc tạo một trang tính mới.

Trong ô D9, chúng ta đã nhập =SEQUENCE(1,5,3,1), nhưng bạn cũng có thể nhập 3, 4, 5, 6 và 7 vào các ô A9:H9. Không có gì đặc biệt về lựa chọn số cụ thể đó, chúng tôi chỉ chọn một số khác 1-5 để phân biệt.

Trong ô E11, hãy nhập =SUM(D9:H9*SEQUENCE(1,5)), hoặc =SUM(D9:H9*{1\2\3\4\5}). Công thức trả về 85.

Sử dụng hằng số mảng trong công thức. Trong ví dụ này, chúng tôi sử dụng =SUM(D9:H(*SEQUENCE(1,5))

Hàm SEQUENCE xây dựng giá trị tương đương với hằng số mảng {1\2\3\4\5}. Vì Excel thực hiện các thao tác trên biểu thức được đặt trong dấu ngoặc đơn trước tiên, hai thành phần tiếp theo bắt đầu hoạt động là các giá trị ô trong D9:H9 và toán tử nhân (*). Tại thời điểm này, công thức nhân các giá trị trong mảng được lưu trữ với các giá trị tương ứng trong hằng số. Nó tương đương với:

=SUM(D9*1,E9*2,F9*3,G9*4,H9*5)hoặc =SUM(3*1,4*2,5*3,6*4,7*5)

Cuối cùng, hàm SUM cộng các giá trị với nhau và trả về 85.

Để tránh dùng mảng được lưu trữ và giữ thao tác hoàn toàn trong bộ nhớ, bạn có thể thay thế nó bằng hằng số mảng khác:

=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)), hoặc =SUM({3\4\5\6\7}*{1\2\3\4\5})

Các thành phần bạn có thể dùng trong hằng số mảng

Hằng số mảng có thể chứa số, văn bản, giá trị lô-gic (chẳng hạn như TRUE và FALSE) và các giá trị lỗi như #N/A. Bạn có thể dùng số ở các định dạng số nguyên, thập phân và khoa học. Nếu bạn đưa văn bản vào hằng số mảng, bạn cần đặt văn bản trong dấu ngoặc kép ("văn bản”).
Hằng số mảng không được chứa thêm các mảng, công thức hoặc các hàm khác. Nói cách khác, chúng chỉ có thể chứa văn bản hoặc số được phân cách bằng dấu phẩy hoặc dấu chấm phẩy. Excel sẽ hiển thị thông báo cảnh báo khi bạn nhập một công thức như {1\2\A1:D4} hoặc {1\2\SUM(Q2:Z8)}. Ngoài ra, giá trị số không được chứa dấu phần trăm, dấu đô-la, dấu phẩy hoặc dấu ngoặc đơn.

Một trong những cách tốt nhất để dùng hằng số mảng là đặt tên cho chúng. Các hằng số đã được đặt tên có thể dễ sử dụng hơn nhiều và chúng có thể ẩn bớt phần nào sự phức tạp trong công thức mảng để người khác không phải thấy. Để đặt tên cho hằng số mảng và dùng nó trong công thức, bạn hãy làm như sau:

Đi tới Công thức > Tên đã xxác định > Xác định tên. Trong hộp Tên, hãy gõ Quarter1. Trong hộp Tham chiếu đến, bạn hãy nhập hằng số sau đây (hãy nhớ nhập dấu ngoặc nhọn theo cách thủ công):

={"Tháng Một"\"Tháng Hai"\"Tháng Ba"}

Hộp thoại giờ sẽ trông giống như thế này:

Thêm hằng số mảng đã đặt tên từ Công thức > Tên đã xác định > Trình quản lý tên > Mới

Bấm OK, rồi chọn hàng bất kỳ có ba ô trống và nhập =Quarter1.

Kết quả sau đây được hiển thị:

Sử dụng hằng số mảng đã đặt tên trong một công thức, chẳng hạn =Quarter1, với Quarter1 được xác định ={"January","February","March"}

Nếu muốn kết quả tràn theo chiều dọc thay vì theo chiều ngang, bạn có thể dùng =TRANSPOSE(Quarter1).

Nếu bạn muốn hiển thị danh sách 12 tháng, giống như bạn có thể sử dụng khi xây dựng báo cáo tài chính, bạn có thể tạo cơ sở cho một năm hiện tại bằng hàm SEQUENCE. Điểm khác biệt về hàm này là ngay cả khi chỉ hiển thị tháng, vẫn có ngày hợp lệ mà bạn có thể sử dụng trong các phép tính khác. Bạn sẽ tìm thấy các ví dụ này trên Hằng số mảng đã đặt tên và các trang tính Tập dữ liệu mẫu nhanh trong sổ làm việc ví dụ.

=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"mmm")

Sử dụng tổ hợp hàm TEXT, DATE, YEAR, TODAY và SEQUENCE để xây dựng danh sách động gồm 12 tháng

Công thức này dùng hàm DATE để tạo ngày dựa trên năm hiện tại, SEQUENCE tạo hằng số mảng từ 1 đến 12 cho tháng 1 tới tháng 12, sau đó hàm TEXT chuyển đổi định dạng hiển thị thành "mmm" (tháng 1, tháng 2, tháng 3, v.v.). Nếu bạn muốn hiển thị tên tháng đầy đủ, chẳng hạn như Tháng Một, bạn có thể sử dụng "mmmm".

Khi bạn dùng một hằng số đã đặt tên làm công thức mảng, hãy nhớ nhập dấu bằng, như trong =Quarter1, chứ không chỉ Quarter1. Nếu bạn không làm vậy, Excel sẽ hiểu mảng này là chuỗi văn bản và công thức của bạn sẽ không hoạt động như mong đợi. Cuối cùng, hãy nhớ rằng bạn có thể dùng kết hợp cả hàm, văn bản và số. Tất cả những việc này tùy thuộc vào mức độ sáng tạo mà bạn muốn có.

Các ví dụ sau đây thể hiện một vài cách sử dụng hằng số mảng trong công thức mảng. Một số ví dụ dùng hàm TRANSPOSE để chuyển đổi hàng thành cột và ngược lại.

Nhân từng mục trong mảng

Nhập =SEQUENCE(1,12)*2hoặc ={1\2\3\4;5\6\7\8;9\10\11\12}*2

Bạn cũng có thể chia bằng (/), thêm bằng (+) và trừ bằng (-).
Lấy bình phương các mục trong mảng

Nhập =SEQUENCE(1,12)^2, hoặc ={1\2\3\4;5\6\7\8;9\10\11\12}^2
Tìm căn bậc hai của các mục bình phương trong một mảng

Nhập =SQRT(SEQUENCE(1,12)^2), hoặc =SQRT({1\2\3\4;5\6\7\8;9\10\11\12}^2)
Hoán đổị hàng một chiều

Nhập =TRANSPOSE(SEQUENCE(1,5)), hoặc =TRANSPOSE({1\2\3\4\5})

Ngay cả khi bạn nhập hằng số mảng ngang, hàm TRANSPOSE sẽ chuyển đổi hằng số mảng thành cột.
Hoán vị cột một chiều

Nhập =TRANSPOSE(SEQUENCE(5,1)), hoặc =TRANSPOSE({1;2;3;4;5})

Ngay cả khi bạn nhập hằng số mảng dọc, hàm TRANSPOSE sẽ chuyển đổi hằng số mảng thành hàng.
Hoán đổi hằng số hai chiều

Nhập =TRANSPOSE(SEQUENCE(3,4)), hoặc =TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})

Hàm TRANSPOSE sẽ chuyển đổi mỗi hàng thành một chuỗi cột.

Phần này cung cấp ví dụ về công thức mảng cơ bản.

Tạo mảng từ các giá trị hiện có

Ví dụ sau đây giải thích cách dùng công thức mảng để tạo mảng mới từ mảng hiện có.

Nhập =SEQUENCE(3,6,10,10), hoặc ={10\20\30\40\50\60;70\80\90\100\110\120;130\140\150\160\170\180}

Nhớ nhập { (dấu ngoặc nhọn mở) trước khi bạn nhập 10 và } (dấu ngoặc nhọn đóng) sau khi bạn nhập 180 vì bạn đang tạo ra một mảng các con số.

Tiếp theo, =D9#, hoặc =D9:I11 vào một ô trống. Một mảng các ô 3 x 6 xuất hiện với các giá trị tương tự mà bạn nhìn thấy trong D9:D11. Dấu # được gọi là toán tử phạm vi bị trànvà đây là cách Excel tham chiếu toàn bộ dải ô mảng thay vì phải nhập ra.
Tạo hằng số mảng từ các giá trị hiện có

Bạn có thể lấy kết quả của một công thức mảng bị tràn và chuyển đổi thành các phần của nó. Chọn ô D9, rồi nhấn F2 để chuyển sang chế độ sửa. Tiếp theo, nhấn F9 để đổi tham chiếu ô thành giá trị, Excel sau đó chuyển đổi thành hằng số mảng. Khi bạn nhấn Enter, công thức, =D9#, giờ sẽ là ={10\20\30;40\50\60;70\80\90}.
Đếm số ký tự trong phạm vi ô

Ví dụ sau đây cho bạn thấy cách đếm số ký tự trong một phạm vi ô. Bao gồm khoảng trắng.

=SUM(LEN(C9:C13))

Trong trường hợp này, hàm LEN trả về độ dài của từng chuỗi văn bản trong mỗi ô trong phạm vi đó. Sau đó, hàm SUM cộng các giá trị đó với nhau và hiển thị kết quả (66). Nếu bạn muốn có số ký tự trung bình, bạn có thể sử dụng:

=AVERAGE(LEN(C9:C13))
Nội dung của ô dài nhất trong khoảng C9:C13

=INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

Công thức này chỉ hoạt động khi phạm vi dữ liệu chứa một cột ô duy nhất.

Chúng ta hãy xem xét kỹ hơn công thức này, bắt đầu tư các thành phần bên trong và hướng ra bên ngoài. Hàm LEN trả về độ dài của từng mục trong phạm vi ô D2:D6. Hàm MAX tính giá trị lớn nhất trong số các mục đó, mà giá trị đó tương ứng với chuỗi văn bản dài nhất, trong ô D3.

Đây là nơi mọi thứ trở nên phức tạp một chút. Hàm MATCH tính toán khoảng chừa trống (vị trí tương đối) của ô chứa chuỗi văn bản dài nhất. Để làm điều đó, nó đòi hỏi ba đối số: giá trị tra cứu, mảng tra cứu và kiểu khớp. Hàm MATCH tìm kiếm giá trị tra cứu đã xác định trong mảng tra cứu. Trong trường hợp này, giá trị tra cứu là chuỗi văn bản dài nhất:

MAX(LEN(C9:C13)

và chuỗi đó nằm trong mảng này:

LEN(C9:C13)

Tham đối kiểu khớp trong trường hợp này là 0. Kiểu khớp có thể là giá trị 1, 0 hoặc -1.
- 1 - trả về giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu
- 0 - trả về giá trị đầu tiên chính xác bằng với giá trị tra cứu
- -1 - trả về giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tra cứu đã xác định
- Nếu bạn bỏ qua kiểu khớp, Excel giả định nó bằng 1.
Cuối cùng, hàm INDEX lấy các đối số này: mảng và số hàng và cột trong mảng đó. Phạm vi ô C9:C13 cung cấp mảng, hàm MATCH cung cấp địa chỉ ô và đối số cuối cùng (1) chỉ rõ rằng giá trị đến từ cột đầu tiên trong mảng.

Nếu bạn muốn tải nội dung của chuỗi văn bản nhỏ nhất, bạn sẽ thay thế hàm MAX trong ví dụ trên bằng hàm MIN.
Tìm n giá trị nhỏ nhất trong một phạm vi

Ví dụ này cho thấy cách tìm ba giá trị nhỏ nhất trong một phạm vi ô, nơi đã tạo mảng dữ liệu mẫu trong các ô B9:B18 đã được tạo bằng: =INT(RANDARRAY(10,1)*100). Lưu ý rằng RANDARRAY là một hàm biến đổi, vì vậy bạn sẽ có một tập hợp số ngẫu nhiên mới mỗi khi Excel tính toán.

Nhập =SMALL(B9#;SEQUENCE(D9), =SMALL(B9:B18;{1;2;3})

Công thức này dùng hằng số mảng để đánh giá hàm SMALL ba lần và trả về 3 thành viên nhỏ nhất trong mảng chứa trong các ô B9:B18, trong đó 3 là giá trị biến số trong ô D9. Để tìm thêm các giá trị, bạn có thể tăng giá trị trong hàm SEQUENCE hoặc thêm nhiều đối số vào hằng số. Bạn cũng có thể dùng thêm các hàm khác trong công thức này, chẳng hạn như hàm SUM hay AVERAGE. Ví dụ:

=SUM(SMALL(B9#,SEQUENCE(D9))

=AVERAGE(SMALL(B9#,SEQUENCE(D9))
Tìm n giá trị lớn nhất trong một phạm vi

Để tìm giá trị lớn nhất trong một phạm vi, bạn có thể thay thế hàm SMALL bằng hàm LARGE. Ngoài ra, ví dụ sau đây dùng các hàm ROW và INDIRECT.

Nhập =LARGE(B9#,ROW(INDIRECT("1:3"))), or =LARGE(B9:B18,ROW(INDIRECT("1:3")))

Lúc này, hiểu biết một chút về hàm ROW và INDIRECT có thể hữu ích. Bạn có thể dùng hàm ROW để tạo một mảng gồm các số nguyên liên tiếp. Ví dụ: chọn một mục trống, rồi nhập:

=ROW(1:10)

Công thức này tạo ra một cột gồm 10 số nguyên liên tiếp. Để xem vấn đề tiềm ẩn, hãy chèn một hàng bên trên phạm vi chứa công thức mảng (tức là, trên hàng 1). Excel sẽ điều chỉnh tham chiếu hàng và công thức hiện sẽ tạo ra các số nguyên từ 2 đến 11. Để khắc phục vấn đề đó, bạn hãy thêm hàm INDIRECT vào công thức:

=ROW(INDIRECT("1:10"))

Hàm INDIRECT dùng chuỗi văn bản làm đối số của nó (đó là lý do tại sao phạm vi 1:10 được đặt trong dấu ngoặc). Excel không điều chỉnh giá trị văn bản khi bạn chèn hàng hay di chuyển công thức mảng theo cách khác. Do đó, hàm ROW sẽ luôn luôn tạo ra mảng số nguyên mà bạn muốn. Bạn có thể dễ dàng sử dụng SEQUENCE:

=SEQUENCE(10)

Chúng ta hãy kiểm tra công thức mà bạn dùng ở trước — =LARGE(B9#,ROW(INDIRECT("1:3"))) — bắt đầu từ cặp dấu ngoặc đơn bên trong và tiến hành hướng ra ngoài: Hàm INDIRECT trả về một bộ các giá trị văn bản, trong trường hợp này là các giá trị từ 1 đến 3. Đến lượt mình, hàm ROW tạo ra mảng cột gồm ba ô. Hàm LARGE dùng các giá trị trong phạm vi ô B9:B18 và nó được đánh giá ba lần, một lần cho mỗi tham chiếu mà hàm ROW trả về. Nếu muốn tìm thêm giá trị, bạn hãy thêm một phạm vi ô lớn hơn vào hàm INDIRECT. Cuối cùng, như với các ví dụ hàm SMALL, bạn có thể dùng công thức này với các hàm khác, chẳng hạn như hàm SUM và AVERAGE.

Cộng tổng một phạm vi chứa các giá trị lỗi

Hàm SUM trong Excel không hoạt động khi bạn tìm cách cộng tổng một phạm vi chứa giá trị lỗi, chẳng hạn như #VALUE! hoặc #N/A. Ví dụ này cho bạn thấy cách cộng các giá trị trong phạm vi tên là Data, trong đó có chứa lỗi:
=SUM(IF(ISERROR(Data),"",Data))

Công thức này tạo ra một mảng mới, trong đó chứa các giá trị ban đầu nhưng loại trừ mọi giá trị lỗi. Bắt đầu từ hàm bên trong và làm việc hướng ra bên ngoài, hàm ISERROR tìm kiếm các lỗi trong phạm vi ô (Data). Hàm IF trả về một giá trị cụ thể nếu điều kiện mà bạn xác định định trị là TRUE, trả về giá trị khác nếu nó định trị là FALSE. Trong trường hợp này, nó trả về chuỗi trống ("") cho tất cả các giá trị lỗi vì chúng định trị là TRUE, nó trả về các giá trị còn lại từ phạm vi (Data) vì chúng định trị là FALSE, có nghĩa là chúng không chứa giá trị lỗi. Sau đó, hàm SUM tính tổng cộng cho mảng đã lọc.
Đếm số giá trị lỗi trong phạm vi

Ví dụ này giống công thức ở trước, chỉ khác ở chỗ nó trả về số giá trị lỗi trong phạm vi có tên là Data thay vì lọc bỏ các giá trị lỗi đó:

=SUM(IF(ISERROR(Data),1,0))

Công thức này tạo ra một mảng chứa giá trị 1 cho các ô chứa lỗi và giá trị 0 cho các ô không chứa lỗi. Bạn có thể đơn giản hóa công thức này và thu được cùng một kết quả bằng cách loại bỏ đối số thứ ba cho hàm IF, giống như thế này:

=SUM(IF(ISERROR(Data),1))

Nếu bạn không chỉ rõ đối số, hàm IF trả về FALSE nếu ô không chứa giá trị lỗi. Thậm chí bạn có thể đơn giản hóa công thức hơn nữa:

=SUM(IF(ISERROR(Data)*1))

Phiên bản công thức này hoạt động vì TRUE*1=1 và FALSE*1=0.

Bạn có thể cần tính tổng các giá trị theo các điều kiện.

Bạn có thể sử dụng mảng để tính toán dựa trên các điều kiện nhất định. =SUM(IF(Sales>0;Sales)) sẽ tính tổng tất cả các giá trị lớn hơn 0 trong khoảng có tên Doanh số.

Ví dụ, công thức mảng này chỉ tỉnh tổng các số nguyên dương trong phạm vi có tên là Sales, đại diện cho các ô E9: E24 trong ví dụ trên:

=SUM(IF(Sales>0,Sales))

Hàm IF tạo ra mảng gồm các trị dương và false. Về cơ bản, hàm SUM bỏ qua các giá trị false vì 0+0=0. Phạm vi ô bạn dùng trong công thức này có thể chứa bất kỳ số hàng và cột nào.

Bạn cũng có thể tính tổng các giá trị đáp ứng nhiều điều kiện. Ví dụ: công thức mảng này tính toán các giá trị lớn hơn 0 AND nhỏ hơn 2500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

Hãy nhớ rằng công thức này trả về lỗi nếu phạm vi đó chứa một hoặc nhiều ô không phải dạng số.

Bạn cũng có thể tạo công thức mảng dùng kiểu điều kiện OR. Ví dụ: bạn có thể tính tổng các giá trị lớn hơn 0 OR nhỏ hơn 2500:

=SUM(IF((Sales>0)+(Sales<2500),Sales))

Bạn không dùng được hàm AND và OR trực tiếp trong công thức mảng vì các hàm đó trả về một kết quả duy nhất, TRUE hoặc FALSE, còn hàm mảng đòi hỏi mảng kết quả. Bạn có thể giải quyết vấn đề này bằng cách dùng lô-gic được trình bày trong công thức trên đây. Nói cách khác, bạn thực hiện các phép toán, chẳng hạn như phép cộng hoặc phép nhân đối với các giá trị đáp ứng điều kiện OR hoặc AND.

Ví dụ này cho bạn thấy cách loại bỏ số không khỏi phạm vi khi bạn cần tính trung bình các giá trị trong phạm vi đó. Công thức này dùng phạm vi dữ liệu có tên là Sales:

=AVERAGE(IF(Sales<>0,Sales))

Hàm IF tạo ra mảng chứa các giá trị khác 0 rồi chuyển các giá trị này đến hàm AVERAGE.

Công thức mảng này so sánh các giá trị trong hai phạm vi ô có tên là MyData và YourData và trả về số lượng các khác biệt giữa hai phạm vi này. Nếu nội dung của hai phạm vi này giống hệt nhau, công thức trả về 0. Để sử dụng công thức này, các dải ô phải có cùng kích cỡ và có cùng kích thước. Ví dụ: nếu MyData là phạm vi gồm 3 hàng và 5 cột thì Dữ liệu của Bạn cũng phải là 3 hàng và 5 cột:

=SUM(IF(MyData=YourData,0,1))

Công thức này tạo ra mảng mới có cùng kích cỡ với các phạm vi bạn đang so sánh. Hàm IF điền vào mảng với giá trị 0 và 1 (0 cho các ô không khớp và 1 cho các ô giống nhau). Sau đó, hàm SUM trả về tổng giá trị trong mảng.

Bạn có thể đơn giản hóa công thức này như sau:

=SUM(1*(MyData<>YourData))

Giống như công thức đếm số giá trị lỗi trong phạm vi, công thức này hoạt động vì TRUE*1=1 và FALSE*1=0.

Công thức mảng này trả về số hàng của giá trị tối đa trong phạm vi đơn cột có tên là Data:

=MIN(IF(Data=MAX(Data),ROW(Data),""))

Hàm IF tạo ra một mảng mới tương ứng với phạm vi có tên là Data. Nếu một ô tương ứng chứa giá trị tối đa trong phạm vi đó, thì mảng sẽ chứa số hàng. Nếu không, mảng chứa chuỗi trống (""). Hàm MIN dùng mảng mới làm đối số thứ hai của mình và trả về giá trị nhỏ nhất tương ứng với số hàng của giá trị tối đa trong phạm vi Data. Nếu phạm vi có tên là Data chứa nhiều giá trị tối đa giống hệt nhau, thì công thức trả về hàng chứa giá trị đầu tiên.

Nếu bạn muốn trả về địa chỉ thực tế của ô chứa giá trị tối đa, hãy dùng công thức này:

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

Bạn sẽ tìm thấy các ví dụ tương tự trong sổ làm việc mẫu trên trang tính Khác nhau giữa các bộ dữ liệu.

Công thức mảng đa ô

Sao chép toàn bộ bảng bên dưới và dán nó vào ô A1 trong một trang tính trống.

Người Bán hàng	Loại Xe	Số Đã bán	Đơn Giá
Barnhill	Sedan	5	33000
	Coupe	4	37000
Ingle	Sedan	6	24000
	Coupe	8	21000
Jordan	Sedan	3	29000
	Coupe	1	31000
Pica	Sedan	9	24000
	Coupe	5	37000
Sanchez	Sedan	6	33000
	Coupe	8	31000
Công thức (Tổng Cộng)			Tổng Cộng
'=SUM(C2:C11*D2:D11)			=SUM(C2:C11*D2:D11)

Để xem Tổng Doanh thu của các kiểu xe coupe và sedan cho từng nhân viên bán hàng, hãy chọn các ô E2:E11, nhập công thức =C2:C11*D2:D11 và nhấn Ctrl+Shift+Enter.
Đối với Tổng Cộng tất cả doanh thu, hãy chọn ô F11, nhập công thức =SUM(C2:C11*D2:D11) và nhấn Ctrl+Shift+Enter.

Khi bạn nhấn Ctrl+Shift+Enter, Excel sẽ đưa công thức vào trong một dấu cắp ({ }) và chèn một bản sao công thức vào từng ô của dải ô đã chọn. Điều này xảy ra rất nhanh, vì vậy bạn sẽ thấy cột E chứa tổng doanh thu bán hàng cho mỗi kiểu xe cho mỗi nhân viên bán hàng. Nếu bạn chọn E2, rồi chọn E3, E4, v.v. thì bạn sẽ nhìn thấy cùng một công thức được hiển thị: {=C2:C11*D2:D11}.

Các tổng số trong cột E do công thức mảng tính toán

Tạo công thức mảng đơn ô

Trong ô D13 của sổ làm việc, hãy nhập công thức mảng, rồi nhấn Ctrl+Shift+Enter:

=SUM(C2:C11*D2:D11)

Trong trường hợp này, Excel nhân các giá trị trong mảng (phạm vi ô từ C2 đến hết D11), rồi dùng hàm SUM để cộng các tổng số với nhau. Kết quả tổng cộng doanh số bán hàng là $1.590.000. Ví dụ này cho thấy kiểu công thức này có hiệu quả tới mức nào. Ví dụ, giả sử bạn có 1.000 hàng dữ liệu. Bạn có thể cộng một phần hoặc tất cả các dữ liệu đó với nhau bằng cách tạo công thức mảng trong một ô duy nhất thay vì kéo công thức xuống qua hết 1.000 hàng.

Ngoài ra, hãy lưu ý rằng công thức đơn ô trong ô D13 hoàn toàn độc lập với công thức đa ô (công thức trong các ô từ E2 đến E11). Đây là một ưu điểm khác của việc dùng công thức mảng — tính linh hoạt. Bạn có thể thay đổi các công thức trong cột E hoặc xóa bỏ hoàn toàn cột đó mà không ảnh hưởng đến công thức trong ô D13.

Công thức mảng còn có các ưu điểm sau đây:

Tính nhất quán Nếu bạn bấm bất kỳ ô nào từ E2 trở xuống, bạn sẽ nhìn thấy cùng một công thức. Sự nhất quán đó có thể giúp đảm bảo độ chính xác lớn hơn.
An toàn Bạn không thể ghi đè thành phần của một công thức mảng đa ô. Ví dụ, hãy bấm ô E3 và nhấn Delete. Bạn phải chọn toàn bộ phạm vi ô (E2 đến E11) và thay đổi công thức cho toàn bộ mảng hoặc giữ nguyên mảng đó. Như một biện pháp an toàn bổ sung, bạn phải nhấn Ctrl+Shift+Enter để xác nhận mọi thay đổi đối với công thức.
Kích cỡ tệp nhỏ hơn Bạn có thể thường xuyên dùng một công thức mảng duy nhất thay vì nhiều công thức trung gian. Ví dụ, sổ làm việc dùng một công thức mảng để tính toán kết quả ở cột E. Nếu bạn dùng công thức chuẩn (chẳng hạn như =C2*D2, C3*D3, C4*D4…) thì bạn phải dùng 11 công thức khác nhau để tính toán cùng kết quả đó.

Nhìn chung, công thức mảng dùng cú pháp công thức chuẩn. Tất cả các công thức này đều bắt đầu với dấu bằng (=) và bạn có thể dùng hầu hết các hàm Excel dựng sẵn trong công thức mảng của mình. Sự khác biệt chính là ở chỗ khi dùng công thức mảng, bạn nhấn Ctrl+Shift+Enter để nhập công thức. Khi bạn làm điều này, Excel đặt công thức mảng của bạn trong một cặp dấu ngoặc nhọn — nếu bạn nhập dấu ngoặc nhọn theo cách thủ công, thì công thức của bạn sẽ được chuyển thành chuỗi văn bản và nó sẽ không hoạt động.

Hàm mảng có thể là một cách hiệu quả để xây dựng công thức phức tạp. Công thức mảng =SUM(C2:C11*D2:D11) giống như công thức sau đây: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Quan trọng: Nhấn Ctrl+Shift+Enter bất cứ khi nào bạn cần nhập một công thức mảng. Điều này áp dụng cho cả công thức đơn ô và đa ô.

Bất cứ khi nào làm việc với công thức đa ô, bạn cũng hãy nhớ rằng:

Hãy chọn phạm vi ô để chứa kết quả của bạn trước khi nhập công thức. Bạn đã làm điều này khi tạo công thức mảng đa ô, bằng cách chọn từ E2 đến hết E11.
Bạn không thể thay đổi nội dung của một ô đơn lẻ trong công thức mảng. Để thử điều này, hãy chọn ô E3 trong sổ làm việc và nhấn Delete. Excel sẽ hiển thị thông báo cho biết bạn không thể thay đổi một phần của mảng.
Bạn có thể di chuyển hoặc xóa bỏ toàn bộ một công thức mảng nhưng bạn không thể di chuyển hoặc xóa bỏ một phần của nó. Nói cách khác, để thu nhỏ một công thức mảng, trước tiên bạn phải xóa bỏ công thức hiện có và làm lại từ đầu.
Để xóa bỏ công thức mảng, hãy chọn toàn bộ phạm vi công thức (ví dụ: E2:E11), sau đó nhấn Delete.
Bạn không thể chèn thêm ô trống hoặc xóa bỏ ô ra khỏi công thức mảng đa ô.

Đôi khi bạn có thể cần mở rộng công thức mảng. Chọn ô đầu tiên trong dải ô mảng hiện có và tiếp tục cho đến khi đã chọn được toàn bộ phạm vi mà bạn muốn mở rộng công thức. Nhấn F2 để chỉnh sửa công thức, rồi nhấn CTRL+SHIFT+ENTER để xác nhận công thức sau khi bạn đã điều chỉnh phạm vi công thức. Khóa là để chọn toàn bộ phạm vi, bắt đầu với ô trên cùng bên trái trong mảng. Ô phía trên cùng bên trái là ô được chỉnh sửa.

Công thức mảng rất tuyệt vời nhưng chúng có thể có một vài hạn chế:

Đôi khi bạn có thể quên nhấn Ctrl+Shift+Enter. Điều này có thể xảy ra thậm chí với những người dùng Excel dạn dày kinh nghiệm. Hãy nhớ nhấn tổ hợp phím này bất cứ khi nào bạn nhập hay sửa công thức mảng.
Những người khác dùng sổ làm việc của bạn có thể không hiểu công thức của bạn. Trên thực tế, công thức mảng thường không được giải thích trong trang tính. Vì vậy, nếu người khác cần sửa đổi sổ làm việc của bạn, thì bạn nên tránh dùng công thức mảng hoặc hãy bảo đảm rằng những người đó biết rõ mọi công thức mảng trong đó và biết cách thay đổi các công thức đó nếu họ cần.
Tùy thuộc vào tốc độ xử lý và bộ nhớ của máy tính, công thức mảng lớn có thể làm chậm quá trình tính toán.

Hằng số mảng là một thành phần của công thức mảng. Bạn tạo hằng số mảng bằng cách nhập danh sách các mục, sau đó đặt danh sách này trong một cặp dấu ngoặc nhọn ({ }) theo cách thủ công, như thế này:

={1\2\3\4\5}

Đến bây giờ, bạn đã biết rằng mình cần nhấn Ctrl+Shift+Enter khi tạo công thức mảng. Vì hằng số mảng là thành phần của công thức mảng, bạn hãy đặt hằng số mảng trong cặp dấu ngoặc nhọn theo cách thủ công bằng cách gõ nhập các dấu ngoặc nhọn đó. Sau đó bạn dùng Ctrl+Shift+Enter để nhập toàn bộ công thức.

Đây là mảng trong một hàng: {1\2\3\4}. Đây là mảng trong một cột: {1;2;3;4}. Và đây là mảng gồm hai hàng và bốn cột: {1\2\3\4;5\6\7\8}. Trong mảng hai hàng, hàng thứ nhất là 1, 2, 3 và 4 và hàng thứ hai là 5, 6, 7 và 8. Một dấu chấm phẩy phân cách hai hàng, giữa 4 và 5.

Giống như với công thức mảng, bạn có thể dùng hằng số mảng với hầu hết các hàm dựng sẵn mà Excel cung cấp. Phần sau đây giải thích cách tạo mỗi loại hằng số và cách dùng các hằng số này với các hàm trong Excel.

Quy trình sau đây sẽ giúp bạn thực hành việc tạo các hằng số ngang, dọc và hai chiều.

Tạo hằng số ngang

Trong một trang tính trống, hãy chọn các ô từ A1 đến E1.
Trong thanh công thức, hãy nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:

={1\2\3\4\5}

Trong trường hợp này, bạn cần các dấu mở và đóng ({ }) và Excel sẽ thêm bộ thứ hai cho bạn.

Kết quả sau đây được hiển thị.

Tạo hằng số dọc

Trong sổ làm việc, hãy chọn một cột chứa năm ô.
Trong thanh công thức, hãy nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:

={1;2;3;4;5}

Kết quả sau đây được hiển thị.

Tạo hằng số hai chiều

Trong sổ làm việc của bạn, hãy chọn một khối các ô rộng bốn cột và cao ba hàng.
Trong thanh công thức, hãy nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:

={1\2\3\4;5\6\7\8;9\10\11\12}

Bạn sẽ thấy kết quả sau đây:

Dùng hằng số trong công thức

Đây là một ví dụ đơn giản có sử dụng hằng số:

Trong sổ làm việc mẫu, hãy tạo một trang tính mới .
Trong ô A1, hãy nhập số 3, rồi nhập số 4 trong B1, 5 trong C1, 6 trong D1 và 7 trong E1.
Trong ô A3, hãy nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:

=SUM(A1:E1*{1\2\3\4\5})

Lưu ý rằng Excel đặt hằng số trong một cặp dấu ngoặc nhọn khác, vì bạn đã nhập nó ở dạng công thức mảng.

Giá trị 85 xuất hiện trong ô A3.

Phần tiếp theo giải thích cách hoạt động của công thức.

Công thức mà bạn vừa dùng có một vài phần.

Cú pháp của công thức mảng có hằng số mảng

1. Hàm

2. Mảng đã lưu trữ

3. Toán tử

4. Hằng số mảng

Thành phần cuối cùng trong ngoặc đơn là hằng số mảng: {1\2\3\4\5}. Hãy nhớ rằng Excel không đặt các hằng số mảng trong cặp dấu ngoặc nhọn; mà thực ra là bạn đã nhập các dấu ngoặc nhọn đó. Ngoài ra, hãy nhớ rằng sau khi thêm hằng số vào công thức mảng, bạn hãy nhấn Ctrl+Shift+Enter để nhập công thức.

Vì Excel thực hiện các thao tác trên biểu thức được đặt trong dấu ngoặc đơn trước tiên, hai thành phần tiếp theo bắt đầu hoạt động là các giá trị được lưu trữ trong sổ làm việc (A1:E1) và toán tử. Tại thời điểm này, công thức nhân các giá trị trong mảng được lưu trữ với các giá trị tương ứng trong hằng số. Nó tương đương với:

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

Cuối cùng, hàm SUM cộng các giá trị với nhau và tổng cộng 85 xuất hiện trong ô A3.

Để tránh dùng mảng được lưu trữ và chỉ giữ thao tác hoàn toàn trong bộ nhớ, hãy thay thế mảng được lưu trữ bằng hằng số mảng khác:

=SUM({3\4\5\6\7}*{1\2\3\4\5})

Để thử làm điều này, hãy sao chép hàm, chọn một ô trống trong sổ làm việc của bạn, dán công thức vào thanh công thức, rồi nhấn Ctrl+Shift+Enter. Bạn sẽ thấy kết quả giống như khi bạn thực hiện trong bài tập trước dùng công thức mảng:

=SUM(A1:E1*{1\2\3\4\5})

Hằng số mảng có thể chứa số, văn bản, giá trị lô-gic (chẳng hạn như TRUE và FALSE) và giá trị lỗi (chẳng hạn như #N/A). Bạn có thể dùng số ở các định dạng số nguyên, thập phân và khoa học. Nếu bạn đưa văn bản vào hằng số mảng, bạn cần đặt văn bản trong dấu ngoặc kép (").

Hằng số mảng không được chứa thêm các mảng, công thức hoặc các hàm khác. Nói cách khác, chúng chỉ có thể chứa văn bản hoặc số được phân cách bằng dấu phẩy hoặc dấu chấm phẩy. Excel sẽ hiển thị thông báo cảnh báo khi bạn nhập một công thức như {1\2\A1:D4} hoặc {1\2\SUM(Q2:Z8)}. Ngoài ra, giá trị số không được chứa dấu phần trăm, dấu đô-la, dấu phẩy hoặc dấu ngoặc đơn.

Trên tab Công thức, trong nhóm Tên Đã Xác định, hãy bấm Xác định Tên. The Hộp thoại Xác định Tên xuất hiện.
Trong hộp Tên, hãy gõ Quarter1.
Trong hộp Tham chiếu đến, bạn hãy nhập hằng số sau đây (hãy nhớ nhập dấu ngoặc nhọn theo cách thủ công):

={"Tháng Một"\"Tháng Hai"\"Tháng Ba"}

Nội dung của hộp thoại giờ đây trông giống như thế này:
Bấm OK, rồi chọn hàng gồm ba ô trống.
Gõ công thức sau đây, rồi nhấn Ctrl+Shift+Enter.

=Quarter1

Kết quả sau đây được hiển thị.

Khi bạn dùng một hằng số đã đặt tên làm công thức mảng, hãy nhớ nhập dấu bằng. Nếu bạn không làm vậy, Excel sẽ hiểu mảng này là chuỗi văn bản và công thức của bạn sẽ không hoạt động như mong đợi. Cuối cùng, hãy nhớ rằng bạn có thể dùng kết hợp cả văn bản và số.

Hãy tìm các vấn đề sau đây khi hằng số mảng của bạn không hoạt động:

Có thể một số thành phần không được phân cách bằng ký tự phù hợp. Nếu bạn bỏ qua dấu phẩy hay dấu chấm phẩy hoặc nếu bạn đặt dấu vào sai chỗ, hằng số mảng có thể không được tạo đúng cách hoặc bạn có thể thấy thông báo cảnh báo.
Có thể bạn đã chọn phạm vi ô không khớp với số thành phần trong hằng số của mình. Ví dụ, nếu bạn chọn cột gồm sáu ô để dùng với hằng số năm ô, thì giá trị lỗi #N/A sẽ xuất hiện trong ô trống. Ngược lại, nếu bạn chọn quá ít ô, thì Excel sẽ bỏ qua các giá trị không có ô tương ứng.

Nhân từng mục trong mảng

Tạo một trang tính mới và chọn một khối ô trống rộng bốn cột và cao ba hàng.
Gõ công thức sau đây, rồi nhấn Ctrl+Shift+Enter:

={1\2\3\4;5\6\7\8;9\10\11\12}*2

Lấy bình phương các mục trong mảng

Hãy chọn một khối ô trống rộng bốn cột và cao ba hàng.
Nhập công thức mảng sau đây, rồi nhấn Ctrl+Shift+Enter:

={1\2\3\4;5\6\7\8;9\10\11\12}*{1\2\3\4;5\6\7\8;9\10\11\12}

Hoặc nhập công thức mảng này, công thức dùng toán tử mũ (^):

={1\2\3\4;5\6\7\8;9\10\11\12}^2

Hoán vị hàng một chiều

Hãy chọn một cột gồm năm ô trống.
Gõ công thức sau đây, rồi nhấn Ctrl+Shift+Enter:

=TRANSPOSE({1\2\3\4\5})

Ngay cả khi bạn nhập hằng số mảng ngang, hàm TRANSPOSE sẽ chuyển đổi hằng số mảng thành cột.

Hoán vị cột một chiều

Hãy chọn một hàng gồm năm ô trống.
Nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:

=TRANSPOSE({1;2;3;4;5})

Ngay cả khi bạn nhập hằng số mảng dọc, hàm TRANSPOSE sẽ chuyển đổi hằng số mảng thành hàng.

Hoán vị hằng số hai chiều

Hãy chọn khối ô rộng ba cột và cao bốn hàng.
Nhập hằng số sau đây và nhấn Ctrl+Shift+Enter:

=TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})

Hàm TRANSPOSE sẽ chuyển đổi mỗi hàng thành một chuỗi cột.

Phần này cung cấp ví dụ về công thức mảng cơ bản.

Tạo mảng và hằng số mảng từ các giá trị hiện có

Ví dụ sau đây giải thích cách dùng công thức mảng để tạo nối kết giữa các phạm vi ô trong các trang tính khác nhau. Nó cũng cho bạn thấy cách tạo hằng số mảng từ cùng một bộ giá trị.

Tạo mảng từ các giá trị hiện có

Trên một trang tính trong Excel, hãy chọn các ô C8:E10 và nhập công thức sau:

={10\20\30;40\50\60;70\80\90}

Nhớ nhập { (dấu ngoặc nhọn mở) trước khi bạn nhập 10 và } (dấu ngoặc nhọn đóng) sau khi bạn nhập 90 vì bạn đang tạo ra một mảng các con số.

Nhấn Ctrl+Shift+Enter, thao tác này sẽ nhập mảng số này trong phạm vi ô C8:E10 bằng cách dùng một công thức mảng. Trên trang tính của bạn, C8 đến E10 phải trông giống như thế này:

10	20	30
40	50	60
70	80	90

Hãy chọn phạm vi ô từ C1 đến E3.
Nhập công thức sau đây vào thanh công thức, rồi nhấn Ctrl+Shift+Enter:

=C8:E10

Một mảng các ô 3x3 sẽ xuất hiện trong các ô C1 đến E3 với các giá trị tương tự mà bạn nhìn thấy ở C8 đến E10.

Tạo hằng số mảng từ các giá trị hiện có

Với các ô C1:C3 được chọn, hãy nhấn F2 để chuyển sang chế độ chỉnh sửa.
Nhấn F9 để chuyển đổi tham chiếu ô thành giá trị. Excel chuyển đổi giá trị thành hằng số mảng. Công thức hiện nên là ={10\20\30;40\50\60;70\80\90}.
Hãy nhấn Ctrl+Shift+Enter để nhập hằng số mảng ở dạng công thức mảng.

Đếm số ký tự trong phạm vi ô

Ví dụ sau đây cho bạn thấy cách đếm số ký tự, gồm cả khoảng trắng, trong một phạm vi ô.

Sao chép toàn bộ bảng này và dán vào trong một trang tính ở ô A1.

Dữ liệu
This is a
bunch of cells that
come together
to form a
single sentence.

Tổng các ký tự trong A2:A6
=SUM(LEN(A2:A6))

Nội dung của ô dài nhất (A3)
=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

Chọn ô A8 và nhấn Ctrl+Shift+Enter để xem tổng số lượng ký tự trong các ô A2:A6 (66).
Chọn ô A10 và nhấn Ctrl+Shift+Enter để xem nội dung của phần dài nhất trong phạm vi ô A2:A6 (ô A3).

Công thức sau đây trong ô A8 sẽ đếm tổng số ký tự (66) trong các ô từ A2 đến A6.

=SUM(LEN(A2:A6))

Trong trường hợp này, hàm LEN trả về độ dài của từng chuỗi văn bản trong mỗi ô trong phạm vi đó. Sau đó, hàm SUM cộng các giá trị đó với nhau và hiển thị kết quả (66) trong ô chứa công thức, A9.

Tìm n giá trị nhỏ nhất trong một phạm vi

Ví dụ này cho thấy cách tìm ba giá trị nhỏ nhất trong một phạm vi ô.

Nhập vài số ngẫu nhiên vào các ô A1:A11.
Chọn các ô từ C1 đến C3. Những ô này sẽ chứa các giá trị được công thức mảng trả về.
Nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:

=SMALL(A1:A11,{1;2;3})

Công thức này dùng hằng số mảng để đánh giá hàm SMALL ba lần và trả về phần tử nhỏ nhất (1), nhỏ thứ hai (2) và nhỏ thứ ba (3) trong mảng chứa trong các ô A1:A10. Để tìm nhiều giá trị hơn, bạn hãy thêm các đối số vào hằng số. Bạn cũng có thể dùng thêm các hàm khác trong công thức này, chẳng hạn như hàm SUM hay AVERAGE. Ví dụ:

=SUM(SMALL(A1:A10,{1\2\3})

=AVERAGE(SMALL(A1:A10,{1\2\3})

Tìm n giá trị lớn nhất trong một phạm vi

Để tìm giá trị lớn nhất trong một phạm vi, bạn có thể thay thế hàm SMALL bằng hàm LARGE. Ngoài ra, ví dụ sau đây dùng các hàm ROW và INDIRECT.

Chọn các ô từ D1 đến D3.
Trong thanh công thức, hãy nhập công thức này, rồi nhấn Ctrl+Shift+Enter:

=LARGE(A1:A10,ROW(INDIRECT("1:3")))

Lúc này, hiểu biết một chút về hàm ROW và INDIRECT có thể hữu ích. Bạn có thể dùng hàm ROW để tạo một mảng gồm các số nguyên liên tiếp. Ví dụ, hãy chọn một cột trống gồm 10 ô trong sổ làm việc thực tế của bạn, nhập công thức mảng này, rồi nhấn Ctrl+Shift+Enter:

=ROW(1:10)

Công thức này tạo ra một cột gồm 10 số nguyên liên tiếp. Để xem vấn đề tiềm ẩn, hãy chèn một hàng bên trên phạm vi chứa công thức mảng (tức là, trên hàng 1). Excel sẽ điều chỉnh tham chiếu hàng và công thức sẽ tạo ra các số nguyên từ 2 đến 11. Để khắc phục vấn đề đó, bạn hãy thêm hàm INDIRECT vào công thức:

=ROW(INDIRECT("1:10"))

Hàm INDIRECT dùng chuỗi văn bản làm đối số của nó (đó là lý do tại sao phạm vi 1:10 được đặt trong dấu ngoặc kép). Excel không điều chỉnh giá trị văn bản khi bạn chèn hàng hay di chuyển công thức mảng theo cách khác. Do đó, hàm ROW sẽ luôn luôn tạo ra mảng số nguyên mà bạn muốn.

Chúng ta hãy nhìn vào công thức mà bạn dùng ở trước — =LARGE(A5:A14,ROW(INDIRECT("1:3"))) — bắt đầu từ cặp dấu ngoặc đơn bên trong và tiến hành hướng ra ngoài: Hàm INDIRECT trả về một bộ các giá trị văn bản, trong trường hợp này là các giá trị từ 1 đến 3. Đến lượt mình, hàm ROW tạo ra mảng cột gồm ba ô. Hàm LARGE dùng các giá trị trong phạm vi ô A5:A14 và nó được đánh giá ba lần, một lần cho mỗi tham chiếu mà hàm ROW trả về. Các giá trị 3200, 2700 và 2000 được trả về cho mảng cột gồm ba ô đó. Nếu muốn tìm thêm giá trị, bạn hãy thêm một phạm vi ô lớn hơn vào hàm INDIRECT.

Như với các ví dụ trước, bạn có thể dùng công thức này với các hàm khác, chẳng hạn như hàm SUM và AVERAGE.

Tìm chuỗi văn bản dài nhất trong phạm vi ô

Quay lại ví dụ chuỗi văn bản trước đó, nhập công thức sau đây vào một ô trống và nhấn Ctrl+Shift+Enter:

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

Văn bản "bunch of cells that" sẽ xuất hiện.

Chúng ta hãy xem xét kỹ hơn công thức này, bắt đầu tư các thành phần bên trong và hướng ra bên ngoài. Hàm LEN trả về độ dài của từng mục trong phạm vi ô A2:A6. Hàm MAX tính giá trị lớn nhất trong số các mục đó, tương ứng với chuỗi văn bản dài nhất, ở trong ô A3.

Đây là nơi mọi thứ trở nên phức tạp một chút. Hàm MATCH tính toán khoảng chừa trống (vị trí tương đối) của ô chứa chuỗi văn bản dài nhất. Để làm điều đó, nó đòi hỏi ba đối số: giá trị tra cứu, mảng tra cứu và kiểu khớp. Hàm MATCH tìm kiếm giá trị tra cứu đã xác định trong mảng tra cứu. Trong trường hợp này, giá trị tra cứu là chuỗi văn bản dài nhất:

(MAX(LEN(A2:A6))

và chuỗi đó nằm trong mảng này:

LEN(A2:A6)

Đối số kiểu khớp là 0. Kiểu khớp có thể chứa giá trị 1, 0 hoặc -1. Nếu bạn xác định kiểu khớp là 1, hàm MATCH trả về giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu. Nếu bạn xác định kiểu khớp là 0, hàm MATCH trả về giá trị đầu tiên chính xác bằng với giá trị tra cứu. Nếu bạn xác định kiểu khớp là -1, hàm MATCH tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tra cứu đã xác định. Nếu bạn bỏ qua kiểu khớp, Excel giả định nó bằng 1.

Cuối cùng, hàm INDEX lấy các đối số này: mảng và số hàng và cột trong mảng đó. Phạm vi ô A2:A6 cung cấp mảng, hàm MATCH cung cấp địa chỉ ô và đối số cuối cùng (1) chỉ rõ rằng giá trị đến từ cột đầu tiên trong mảng.

Phần này cung cấp ví dụ về công thức mảng nâng cao.

Cộng tổng một phạm vi chứa các giá trị lỗi

Hàm SUM trong Excel không hoạt động khi bạn tìm cách cộng tổng một phạm vi chứa giá trị lỗi, chẳng hạn như #N/A. Ví dụ này cho bạn thấy cách cộng các giá trị trong phạm vi tên là Data, trong đó có chứa lỗi.

=SUM(IF(ISERROR(Data),"",Data))

Công thức này tạo ra một mảng mới, trong đó chứa các giá trị ban đầu nhưng loại trừ mọi giá trị lỗi. Bắt đầu từ hàm bên trong và làm việc hướng ra bên ngoài, hàm ISERROR tìm kiếm các lỗi trong phạm vi ô (Data). Hàm IF trả về một giá trị cụ thể nếu điều kiện mà bạn xác định định trị là TRUE, trả về giá trị khác nếu nó định trị là FALSE. Trong trường hợp này, nó trả về chuỗi trống ("") cho tất cả các giá trị lỗi vì chúng định trị là TRUE, nó trả về các giá trị còn lại từ phạm vi (Data) vì chúng định trị là FALSE, có nghĩa là chúng không chứa giá trị lỗi. Sau đó, hàm SUM tính tổng cộng cho mảng đã lọc.

Đếm số giá trị lỗi trong phạm vi

Ví dụ này tương tự như công thức ở trước, chỉ khác ở chỗ nó trả về số giá trị lỗi trong phạm vi có tên là Data thay vì lọc bỏ các giá trị lỗi đó:

=SUM(IF(ISERROR(Data),1,0))

Công thức này tạo ra một mảng chứa giá trị 1 cho các ô chứa lỗi và giá trị 0 cho các ô không chứa lỗi. Bạn có thể đơn giản hóa công thức này và thu được cùng một kết quả bằng cách loại bỏ đối số thứ ba cho hàm IF, giống như thế này:

=SUM(IF(ISERROR(Data),1))

Nếu bạn không chỉ rõ đối số, hàm IF trả về FALSE nếu ô không chứa giá trị lỗi. Thậm chí bạn có thể đơn giản hóa công thức hơn nữa:

=SUM(IF(ISERROR(Data)*1))

Phiên bản công thức này hoạt động vì TRUE*1=1 và FALSE*1=0.

Tính tổng các giá trị theo các điều kiện

Bạn có thể cần tính tổng các giá trị theo các điều kiện. Ví dụ, công thức mảng này chỉ tỉnh tổng các số nguyên dương trong phạm vi có tên là Sales:

=SUM(IF(Sales>0,Sales))

Hàm IF tạo ra mảng gồm các trị dương và giá trị false. Về cơ bản, hàm SUM bỏ qua các giá trị false vì 0+0=0. Phạm vi ô bạn dùng trong công thức này có thể chứa bất kỳ số hàng và cột nào.

Bạn cũng có thể tính tổng các giá trị đáp ứng nhiều điều kiện. Ví dụ, công thức mảng này tính toán những giá trị lớn hơn 0 và nhỏ hơn hoặc bằng 5:

=SUM((Sales>0)*(Sales<=5)*(Sales))

Hãy nhớ rằng công thức này trả về lỗi nếu phạm vi đó chứa một hoặc nhiều ô không phải dạng số.

Bạn cũng có thể tạo công thức mảng dùng kiểu điều kiện OR. Ví dụ, bạn có thể tính tổng các giá trị nhỏ hơn 5 và các giá trị lớn hơn 15:

=SUM(IF((Sales<5)+(Sales>15),Sales))

Hàm IF tìm tất cả các giá trị nhỏ hơn 5 và các giá trị lớn hơn 15, rồi chuyển các giá trị đó đến hàm SUM.

Bạn không dùng được hàm AND và OR trực tiếp trong công thức mảng vì các hàm đó trả về một kết quả duy nhất, TRUE hoặc FALSE, còn hàm mảng đòi hỏi mảng kết quả. Bạn có thể giải quyết vấn đề này bằng cách dùng lô-gic được trình bày trong công thức trên đây. Nói cách khác, bạn thực hiện các phép toán, chẳng hạn như phép cộng hoặc phép nhân, đối với các giá trị đáp ứng điều kiện OR hoặc AND.

Tính giá trị trung bình loại trừ số không

=AVERAGE(IF(Sales<>0,Sales))

Hàm IF tạo ra mảng chứa các giá trị khác 0 rồi chuyển các giá trị này đến hàm AVERAGE.

Đếm số lượng các khác biệt giữa hai phạm vi ô

Công thức mảng này so sánh các giá trị trong hai phạm vi ô có tên là MyData và YourData và trả về số lượng các khác biệt giữa hai phạm vi này. Nếu nội dung của hai phạm vi này giống hệt nhau, công thức trả về 0. Để dùng công thức này, các phạm vi ô cần có cùng kích cỡ và kích thước (ví dụ, nếu phạm vi ô MyData có 3 hàng 5 cột, thì phạm vi ô YourData cũng phải có 3 hàng 5 cột):

=SUM(IF(MyData=YourData,0,1))

Công thức này tạo ra mảng mới có cùng kích cỡ với các phạm vi bạn đang so sánh. Hàm IF điền vào mảng với giá trị 0 và 1 (0 cho các ô không khớp và 1 cho các ô giống nhau). Sau đó, hàm SUM trả về tổng giá trị trong mảng.

Bạn có thể đơn giản hóa công thức này như sau:

=SUM(1*(MyData<>YourData))

Giống như công thức đếm số giá trị lỗi trong phạm vi, công thức này hoạt động vì TRUE*1=1 và FALSE*1=0.

Tìm vị trí của giá trị tối đa trong phạm vi

Công thức mảng này trả về số hàng của giá trị tối đa trong phạm vi đơn cột có tên là Data:

=MIN(IF(Data=MAX(Data),ROW(Data),""))

Hàm IF tạo ra một mảng mới tương ứng với phạm vi có tên là Data. Nếu một ô tương ứng chứa giá trị tối đa trong phạm vi đó, thì mảng sẽ chứa số hàng. Nếu không, mảng chứa chuỗi trống (""). Hàm MIN dùng mảng mới làm đối số thứ hai của mình và trả về giá trị nhỏ nhất tương ứng với số hàng của giá trị tối đa trong phạm vi Data. Nếu phạm vi có tên là Data chứa nhiều giá trị tối đa giống hệt nhau, thì công thức trả về hàng chứa giá trị đầu tiên.

Nếu bạn muốn trả về địa chỉ thực tế của ô chứa giá trị tối đa, hãy dùng công thức này:

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

Xác nhận

Các phần của bài viết này dựa trên chuỗi bài viết Người dùng Excel Toàn năng, được viết bởi Colin Wilcox và được chuyển thể từ chương 14 và 15 của cuốn sách Các công thức Excel 2002, được viết bởi John Walkenbach, một cựu MVP về Excel.