3. Ham mang

Một trong những tính năng độc đáo và mạnh mẽ nhất của Excel chính là khả năng tính toán với các mảng dữ liệu trong công thức. Khi hiểu rõ được khái niệm này sẽ giúp chúng ta tạo ra được các công thức thực hiện các phép tính khó một cách kỳ diệu. Bài viết này sẽ giới thiệu các khái niệm về các mảng số liệu mà bất kỳ ai nếu muốn trở thành chuyên gia sử dụng công thức trong Excel đều phải biết về nó, ngoài ra trong bài cũng trình bày các ví dụ về công thức mảng rất hữu ích.

Nếu bạn đã từng lập trình trên bất kỳ ngôn ngữ lập trình nào thì chắc bạn cũng đã nghe đến khái niệm mảng (array). Một array đơn thuần chỉ là một tập hợp các phần tử có quan hệ hay độc lập với nhau. Trong Excel, một array có thể là array một chiều hoặc array hai chiều. Chiều của array ở đây chính là chỉ các dòng và cột trong array. Ví dụ như array một chiều thì có thể hiểu đó là một vùng (range) số liệu trên bảng tính mà vùng này sẽ có một dòng (khi array nằm ngang) hoặc một cột (array nằm dọc). Một array hai chiều có thể hiểu đó là một vùng số liệu trên bảng tính (có dạng hình chữ nhật) bao gồm nhiều dòng và nhiều cột. Excel không hỗ trợ array 3-chiều (VBA thì hỗ trợ).

Công thức mảng không cần lưu trữ các số liệu trong quá trình tính toán trong các ô (cell), mà Excel sẽ xử lý các mảng số liệu này trong bộ nhớ máy tính. Sau đó các công thức mảng sẽ lấy kết quả tính toán trả về trên bảng tính. Một công thức mảng có thể trả về kết quả là nhiều ô (range) hay chỉ một ô.

Công thức mảng trả kết quả về một vùng nhiều ô

Hình minh hoạ bên dưới trình bay ví dụ đơn giản về việc tính toán cột Total (Tổng doanh thu của một loại sản phầm), thông thường để tính cột Total (cột D) này bạn sẽ nhập vào công thức sau:

Tại ô D2 nhập vào công thức =B2*C2 sau đó chép xuống D3:D7

Hình 1: Tính cột [Total] = [Units Sold] * [Unit Price]

Để tính cho cột Total ở đây chúng ta dùng tới 6 công thức. Ngoài cách này chúng ta có thể dùng 1 công thức mảng để tính ra kết quả cho cả cột Total và lưu kết quả trả về tại D2:D7.

Để tạo một công thức mảng tính toán cho trường hợp này hãy làm theo các bước sau:

Chọn vùng các ô sẽ lưu kết quả trả về của công thức mảng, trong ví dụ này chọn vùng D2:D7.
Nhập vào công thức sau =B2:B7*C2:C7 (sau khi chọn vùng thì gõ công thức này vào)
Vì đây là công thức mảng bạn hãy nhấn tổ hợp phím Ctrl+Shift+Enter để nhận kết quả công thức trả về. (Công thức thông thường thì chỉ cần Enter)

Lúc này toàn bộ vùng D2:D7 đã có kết quả tính toán và khi vào xem thì sẽ có công thức như sau:

{=B2:B7*C2:C7}

Lưu ý, Excel sẽ đặt cặp ngoặc móc bao quanh công thức để chỉ đây là công thức mảng.

Công thức mảng trên khi tính toán nó sử dụng dữ liệu từ hai mảng đó là mảng chứa số liệu về [Units Sold] được lưu tại vùng địa chỉ B2:B7 và mảng chứa số liệu về [Unit Price] được lưu tại vùng địa chỉ C2:C7 trong bảng tính.

Vì ứng với mỗi mặt hàng sẽ có một kết quả Total khác nhau, nên đó là lý do chúng ta phải chọn trước vùng địa chỉ D2:D7 để lưu trữ kết quả trả về của công thức mảng.

Công thức mảng này trả về kết quả hoàn toàn chính xác cũng giống như kết quả trả về từ 6 công thức riêng lẽ sau:

=B2*C2
=B3*C3
=B4*C4
=B5*C5
=B6*C6
=B7*C7

Sử dụng công thức mảng thay cho công thức đơn có một số ưu điểm như:

Là cách tính toán đảm bảo sự chính xác về kết quả (tránh trường hợp vô tình sao chép sai công thức do chạy địa chỉ tham chiếu)
Dùng công thức mảng tránh được việc vộ tình xoá hay làm thay đổi công thức trong một ô nào đó của vùng công thức mảng. Vì công thức mảng không cho phép xoá, sửa chữa một ô trong vùng công thức mảng.
Dùng công thức mảng sẽ giúp tránh trường hợp người chưa thành thạo Excel làm xáo trộng các công thức của bạn.

Công thức mảng trả kết quả về một ô

Bây giờ chúng ta cần tính Tổng doanh thu của tất cả các sản phẩm, tuy nhiên yêu cầu là tính toán căn cứ vào cột [Units Sold] và [Unit Price] bằng công thức mảng (nghĩa là không tính toán thông qua cột Total). Khi đó chúng ta có thể dùng công thức mảng sau:

Tại ô D8 bạn nhập vào công thức: =SUM(B2:B7*C2:C7) và kết thúc bằng tổ hợp phím Ctrl+Shift+Enter. Khi đó công thức trong ô D8 có dạng sau {=SUM(B2:B7*C2:C7)}

Công thức mảng này trả về kết quả tính toán tổng doanh thu cho tất cả các sản phẩm trong một ô D8. Công thức này cũng tính toán căn cứ vào 2 mảng số liệu được lưu trong vùng B2:B7 và C2:C7, trong quá trình tính toán công thức mảng sẽ tạo ra trong bộ nhớ một mảng số liệu mới chứa kết quả của phép nhân các cặp số liệu, sau đó hàm SUM được dùng để cộng tất cả các phần tử trong mảng mới này và trả về một giá trị duy nhất lưu vào ô D8.

Trong trường hợp này chúng ta cũng có thể dùng công thức thông thường là hàm SUMPRODUCT như sau:

Tại ô D8 nhập vào công thức =SUMPRODUCT(B2:B7,C2:C7) sau đó Enter.

Mảng một chiều

Ta có thể xem mảng một chiều là một hàng (mảng ngang) hay một cột (mảng dọc). Các phần tử trong mảng một chiều (mảng ngang) được cách biệt nhau bằng một dấu phẩy, và trong mảng dọc được cách nhau bằng dấu chấm phẩy.

Ví dụ: {1,2,3,4,5} (mảng ngang) và {10;20;30;40;50} (mảng dọc)

Hay {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"} (phần tử là Text dạng chuỗi)

Để hiển thị một mảng một chiều 5 phần tử theo dạng ngang hay dọc, ta cần 5 ô liên tục trên cùng một hàng hay một cột

Ví dụ: Tạo mảng 1 chiều

Ta chọn 5 ô từ B3:F3, nhập: ={1,2,3,4,5} và nhấn Ctrl+Shift+Enter
Ta chọn 5 ô từ B6:B10, nhập: ={10;20;30;40;50} và nhấn Ctrl+shift+Enter
Ta chọn 7 ô từ B13:H13, nhập ={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"} và nhấn Ctrl+shift+Enter

Hình 2. Mảng một chiều ngang và dọc

Các phần tử trong mảng sẽ lần lượt được nhập vào 5 ô liên tục theo thứ tự trong mảng. Nếu trong mảng chỉ có 5 phần tử nhưng ta chọn nhiều hơn 5 ô để nhập mảng thì từ ô thứ 6 sẽ cho giá trị #N/A

Hình 3. Mảng lỗi

Mảng hai chiều

Mảng hai chiều là một hình chữ nhật bao gồm nhiều hàng và nhiều cột. Tương tự như mảng một chiều, ta sử dụng các dấu phẩy để ngăn cách các phần tử trong cùng một hàng và dấu chấm phẩy để ngăn cách các phần tử trong cùng một cột.

Ví dụ : Tạo mảng 2 chiều sau {1,2,3,4;5,6,7,8;9,10,11,12}

Để hiển thị mảng này trên bảng tính, ta chọn vùng A1:D3, nhập :

={1,2,3,4;5,6,7,8;9,10,11,12} và nhấn Ctrl+Shift+Enter

Hình 4. Tạo mảng 2 chiều

Cũng vậy, nếu bạn nhập một mảng vào một dãy vốn có các ô nhiều hơn các phần tử mảng, Excel sẽ hiển thị #N/A trong các ô trống còn lại.

Hình 5. Mảng lỗi

Hằng mảng

Bạn có thể tạo một hằng mảng, đặt cho nó một cái tên, sau đó sử dụng mảng được đặt tên này cho công thức. Ví dụ: Ta có hằng mảng như sau: {1,0,1,0,1}. Công thức sau sử dụng hàm SUM với hằng mảng đứng trước là đối số của nó. Công thức trả về tổng của các giá trị trong mảng (ở đây là 3). Công thức này sử dụng mảng là đối số nhưng không phải là công thức mảng: =SUM({1,0,1,0,1}) và công thức này có cùng kết quả với công thức sau =SUM(1,0,1,0,1). Vào thời điểm này, có thể bạn chưa thấy ưu điểm của việc sử dụng hằng mảng.

Công thức sau sử dụng hai hằng mảng: =SUM({1,2,3,4}*{5,6,7,8}). Công thức này sẽ tạo ra một mảng mới trong bộ nhớ máy tính như sau :{5,12,21,32} và sau đó mảng này lại là đối số cho hàm SUM và trả về kết quả là 70. Bạn sẽ thấy công thức trên tương tự như công thức sau : =SUM(1*5,2*6,3*7,4*8) không là công thức mảng.

Một công thức mảng có thể tính tóan trên các mảng số liệu lưu trữ trên bảng tính và hàng mảng. Ví dụ như công thức =SUM((A1:D1*{1,2,3,4})), công thức này tương đương với công thức =SUM(A1*1,B1*2,C1*3,D1*4). (A1:D2 là mảng ngang lưu trữ trên bảng tính).

Một hằng mảng sẽ không chứa các công thức, các hàm, các giá trị có chứa dấu dollar, dấu phẩy, chấm phẩy... Sau đây là một hằng mảng không hợp lệ: {SUM(3,2),$56,12,5%}

Đặt tên cho hằng mảng

Việc đặt tên cho hằng mảng được thông qua hộp thọai Insert/Name/Define (Excel 2007 vào Ribbon | Formulas | Defined Names | Define Name.). Gọi nhanh hộp thọai Name Manager (Ctrl+F3), chèn nhanh Name vào công thức nhấn F3.

Tên của mảng đặt tại hộp Name: Ví dụ ta đặt tên là DayNames

Scope: Chọn phạm vi họat động của Name

Tại hộp Refers to ta đặt dấu ={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}

Hình 6. Đặt tên cho hằng mảng

Nếu bây giờ, ta chọn dãy A1:G1 và nhập công thức mảng sau :{=DayNames}, ta sẽ thấy các phần tử của mảng lần lượt hiện ra trong các ô này.

Hình 7. Sử dụng tên hằng mảng vào công thức

Để chuyển mảng này thành mảng dọc, ta dùng công thức: ={TRANSPOSE(DayNames)}

Để truy cập từng phần tử riêng lẻ của mảng, ta dùng hàm: =INDEX(DayNames,4) sẽ cho kết quả là Wed.

Top

2. Các thao tác với công thức mảng

Phần này sẽ trình bày các thao tác đối với các ô chứa công thức mảng trong bảng tính.

Tạo một mảng từ các giá trị trong một dãy

Giả sử ta có một tập số dữ liệu được nhập vào A1:C4. Ta chọn dãy D8:F11, và nhập vào công thức mảng : {=A1:C4}. Mảng D8:F11 bây giờ sẽ chứa những dữ liệu liên kết với mảng A1:C4, mọi thay đổi giá trị bất kỳ trong A1:C4, thì ô tương ứng trong D8:F11 cũng phản ánh sự thay đổi theo.

Hình 8. Tham chiếu một tập số liệu bằng công thức mảng

Tạo một hằng mảng từ các giá trị trong một dãy

Ở trên, ta đã có công thức mảng trong D8:F11 liên kết với các ô trong mảng A1:C4, bây giờ ta sẽ cắt đứt sự liên kết này để tạo ra một hằng mảng gồm các giá trị trong A1:C4. Để thực hiện, chúng ta làm như sau :

Chọn mảng D8:F11
Nhấn F2 để hiệu chỉnh công thức mảng
Nhấn F9 để chuyển đổi các tham chiếu ô thành các giá trị
Nhấn Ctrl+Shift+Enter để nhập lại công thức mảng

Ta sẽ thấy các giá trị của hằng mảng ở trên thanh công thức

Chọn một dãy công thức mảng

Hiệu chỉnh một công thức mảng

Bạn không thể thay đổi nội dung của bất kỳ ô nào vốn tạo nên một công thức mảng (nếu là mảng nhiều ô).
Bạn không thể xóa các ô vốn hình thành của một công thức mảng (nhưng bạn có thể xóa tòan bộ một mảng)
Bạn không thể chèn các ô mới vào một dãy mảng (nghĩa là chèn hàng và chèn cột)

Hình 9. Excel cảnh báo khi bạn sửa đổi một thành phần thuộc mảng

Để hiệu chỉnh công thức mảng, bạn chọn tất cả các ô trong dãy mảng, kích họat thanh công thức hay nhấn F2, Excel sẽ lọai bỏ các dấu ngoặc móc, và khi bạn hiệu chỉnh xong, bạn sẽ nhấn Ctrl+Shift+Enter để kết thúc.

Mở rộng và thu hẹp công thức mảng nhiều ô

Bạn làm theo trình tự sau:

Chọn tòan bộ vùng chứa công thức mảng trên bảng tính.
Nhấn F2 vào chế độ hiệu chỉnh
Nhấn Ctrl+Enter để chuyển về dạng công thức thường cho tất cả các ô đang chọn.
Chọn lại vùng xuất kết quả công thức mảng mới (mở rộng hay thu hẹp)
Nhấn F2 để vào chế độ hiệu chỉnh
Nhấn Ctrl+Shift+Enter

Top

3. Ví dụ và ứng dụng

Công thức mảng trả kết quả về một ô

a. Đếm các ký tự trong một dãy

Giả sử bạn có một dãy ô, chứa các dữ liệu dạng Text từ A1:A8, bây giờ bạn cần đếm tổng số ký tự có trong dãy trên. Nếu không dùng công thức mảng, bạn sẽ phải dùng một cột phụ, thí dụ cột B từ B1:B8. Tại B1, bạn nhập công thức B1=LEN(A1) và sao chép công thức xuống đến B8. Sau đó, tại B9, bạn dùng công thức tính tổng: =SUM(B1:B8)

Để thay thế cho tất cả công việc trên, ta dùng công thức mảng ngay tại Cell D1 {=SUM(LEN(A1:A8)}. công thức mảng sử dụng hàm LEN để tạo ra một mảng mới (trong bộ nhớ) gồm số ký tự trong mỗi ô của dãy rồi sau đó, tính tổng của dãy.

b. Đếm các ô Text trong một dãy

Công thức mảng sau đây dùng hàm IF để kiểm tra từng ô một trong dãy. Sau đó, nó tạo ra một mảng mới (có cùng kích cỡ và các chiều với mảng gốc) gồm các số 0 và 1 phụ thuộc vào ô đó có chứa dữ liệu Text hay không. Mảng mới này được chuyển sang hàm SUM để tính tổng các ô trong mảng và cho kết quả là số ô Text được đếm trong dãy.

Các công thức mảng dưới cho kết quả như nhau:

{=SUM(IF(ISTEXT(A11:D15),1,0))}

Hay : {=SUM(ISTEXT(A11:D15)*1)}

Hay: {=SUM(ISTEXT(A11:D15)+0)}

Hay: {=SUM(--ISTEXT(A11:D15))}

Lưu ý: True*1=1; False*1=0; x*1=x; x+0=x; --x=x

c. Đếm các ô lỗi trong một dãy

Tương tự công thức trên, ta dùng công thức sau để đếm ô có lỗi trong một dãy:

{=SUM(IF(ISERROR(A3:D14),1,0))}

Hay: {=SUM(IF(ISERROR(A3:D15),1))}

Hay: {=SUM(ISERROR(A3:D14)*1)} …

d. Tính tổng một dãy có chứa các lỗi

Bạn thấy đó, hàm SUM của Excel không họat động nếu bạn dùng để tính tổng một dãy có chứa một hoặc nhiều giá trị lỗi (#DIV/0! hoặc #N/A). Công thức mảng sau đây trả về tổng của một dãy ngay cả dãy này có chứa các lỗi

{=SUM(IF(ISERROR(C1:C10),"", C1:C10)}

e. Tính tổng 3 giá trị nhỏ nhất trong một dãy

Công thức sau đây trả vế tổng của 3 giá trị nhỏ nhất trong một dãy có tên là Data (đặt tên mảng, các bạn tham khảo trong phần "Tên và nhãn trong công thức").

{=SUM(SMALL(Data, {1,2,3}))}

Hàm sử dụng một hằng mảng làm đối số thứ hai cho hàm SMALL. Hàm SMALL được tính tóan 3 lần với 3 đối số thứ hai khác biệt là 1, 2, 3 tương ứng với các giá trị nhỏ nhất, nhỏ thứ hai, và nhỏ thứ ba trong dãy. Sau đó, kết quả tạo ra một mảng mới gồm 3 số hạng nhỏ nhất trong dãy và hàm SUM sẽ tính lại tổng của mảng mới này.

f. Tính tổng 3 giá trị lớn nhất trong một dãy

Tương tự, bạn có thể tính tổng của 3 giá trị lớn nhất trong một dãy như sau

{=SUM(LARGE(Data, {1,2,3}))}

g. Tính tổng n giá trị lớn nhất trong một dãy

{=SUM(LARGE(Data,ROW(INDIRECT("1:n"))))} (n: giá trị bạn muốn tính )

Hàm ROW(INDIRECT("1:n")) sẽ tạo ra một mảng gồm các đối số từ 1 đến n và hằng mảng này sẽ được dùng làm đối số thứ hai cho hàm LARGE như đã giải thích ở phần trên.

h. Tính tổng theo điều kiện

Thông thường chúng ta tính tổng dựa vào một hay nhiều điều kiện. Xét ví dụ sau:

{=SUM(IF(DATA>0,DATA))}

Hàm IF sẽ tạo ra một mảng chỉ chứa các số dương và giá trị FALSE. Sau đó nạp vào hàm SUM, hàm SUM bỏ qua các giá trị FALSE và chỉ cộng các giá trị dương lại cho ra kết quả. Công thức trên tương đương hàm sau: =SUMIF(DATA,”>0”).

Khi có nhiều điều kiện thì Excel 2007 có hàm SUMIFS. Ví dụ muốn tính tổng các giá trị lớn hơn 0 và nhỏ hơn hoặc bằng 5. Dùng hàm SUMIFS như sau: = SUMIFS(DATA,DATA,”<=5”,DATA,”>0”) và công thức mảng sau là tương đương:

{=SUM((DATA>0)*(DATA<=5)*DATA)}. Tuy nhiên công thức mảng này sẽ báo lỗi nếu tập số liệu có chứa chuỗi

i. Tính bình quân lọai bỏ giá trị 0

Muốn tính bình quân cho tập số liệu B5:B12 nhưng loại bỏ các số 0 hãy nhập vào công thức sau:

{=Average(If(Data<>0,Data))}

Hàm này sẽ tạo ra một mảng trong bộ nhớ chỉ chứa các giá trị khác 0 và các giá trị False. Sau đó nạp vào hàm Average tính ra trung bình của các giá trị khác 0. Công thức mảng trên tương đương với công thức sau: =SUM(Data)/CountIf(Data,”<>0”).

j. Kiểm tra Text cần tìm có trong dãy Text không?

Cho mảng tên A5:E24 (đặt tên là NameList) như hình bên dưới, tại ô C3 (đặt tên là TheName) nhập vào tên cần tìm, nếu tên có trong mảng thì tại ô D3 hiện lên chữ Found (tìm thấy), còn không D3 hiện lên Not Found (không tìm thấy).

Công thức mảng tại ô D3 là: {=IF(OR(Thename=NameList),”Found”,”Not Found”)}

Công thức sau sẽ cho kết quả tương đương.

=IF(COUNTIF(NameList,TheName)>0,"Found","Not Found")

k. So sánh 2 dãy

Điều kiện so sánh là 2 dãy phải có cùng một kích cỡ và cùng chiều với nhau. Ta gọi 2 dãy này là MyData và YourData. Công thức mảng sau đây sẽ cho kết quả có bao nhiêu số phần tử khác nhau trong 2 dãy {=SUM(IF(MyData=YourData,0,1))}

Công thức này sẽ tạo ra một mảng mới có cùng kích cỡ với 2 mảng đang được so sánh. Hàm IF sẽ lấp đầy mảng này bằng các phần tử 0, 1 (0 nếu có một phần tử khác nhau được tìm thấy và 1 nếu các phần tử của 2 dãy giống nhau). Sau đó, hàm SUM sẽ trả về tổng của các giá trị 0 trong mảng (số phần tử khác nhau trong 2 dãy). Ta có thể đơn giản hóa công thức trên như sau : {=SUM(1*(MyData<>YourData))}

l. Trả về vị trí của giá trị lớn nhất trong mảng

Công thức mảng sau sẽ trả về số dòng của của giá trị lớn nhất trong mảng dọc một chiều:

{=MIN(IF(Data=MAX(Data),ROW(Data), ""))}.

Nếu mảng Data chứa nhiều hơn 1 giá trị lớn nhất thì dòng chứa giá trị lớn nhất đầu tiên được chọn. Công thức mảng sau sẽ trả về địa chỉ của ô chứa giá trị lớn nhất trong mảng dọc một chiều:

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

Nếu mảng có nhiều cột thì dùng công thức sau:

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

m. Tìm số dòng chứa giá trị xuất hiện thứ n trong tổng số lần xuất hiện của giá trị trong dãy

Công thức mảng sau sẽ trả về số dòng chứa giá trị xuất hiện thứ n trong tổng số lần xuất hiện của giá trị trong dãy dọc

{=SMALL(IF(A4:A18=E4,ROW(A4:A18),""),E5)}

Hàm If sẽ tạo một mảng mới trong bộ nhớ chứa số thứ tự dòng của các giá trị trong vùng A4:A18 bằng với giá trị của ô E4. Các giá trị trong vùng A4:A18 không bằng với giá trị trong E4 sẽ được thay bằng một chuỗi rỗng. Sau đó hàm SMALL sẽ tìm ra số dòng nhỏ thứ n trong tổng số lần xuất hiện của giá trị trong dãy.

Hàm sẽ trả về lỗi #NUM khi giá trị cần tìm không có trong dãy hoặc n lớn hơn tổng số lần xuất hiện.

n. Trả về chuỗi dài nhất trong dãy các chuỗi

Muốn tìm chuỗi ký tự dài nhất trong các chuỗi thuộc dãy dùng công thức mảng sau:

{=INDEX(Data,MATCH(MAX(LEN(Data)),LEN(Data),FALSE),1)}

Hàm này tạo ra hai mảng chứa độ dài của mỗi chuỗi trong dãi được tạo ra bằng 2 hàm LEN. Sau đó dùng hàm MAX để xác định giá trị lớn nhất, và hàm MATCH sẽ tính ra vị trí trong dãy số liệu chứa chuỗi dài nhất này. Cuối cùng hàm INDEX sẽ trả về nội dung của ô chứa chuỗi dài nhất. Hàm này chỉ làm việc trên dãy một chiều dọc.

o. Xác định dãy chứa các giá trị hợp lệ hay không

Bạn có 2 dãy số liệu 1 và 2, bạn muốn kiểm tra xem các phần tử của dãy 1 (tên là MyList) có thuộc dãy 2 (tên là Master) không? Công thức sau trả về TRUE nếu toàn bộ dãy 1 thuộc dãy 2, lưu ý 2 dãy số liệu phải được bố trí dạng cột và không bắt buộc số phần tử bằng nhau.

{=ISNA(MATCH(TRUE,ISNA(MATCH(MyList,Master,0)),0))}

Hàm MATCH ở trên sẽ so sánh từng cặp phần tử trong MyList và Master, nó sẽ trả về một dãy trong bộ nhớ máy tính chứa các con số và lỗi #NA cho các cặp không trùng nhau. Sau đó hàm ISNA sẽ chuyển đổi dãy giá trị trong bộ nhớ này thành các giá trị luận lý TRUE (#NA à TRUE) và FALSE. Tiếp theo hàm MATCH phía ngoài sẽ tìm vị trí của các giá trị TRUE trong dãy của ISNA vừa trả về, nếu tìm thấy giá trị TRUE thì ngay lập tức hàm ISNA ngoài cùng sẽ trả về FALSE (nghĩa có phần tử của MyList nằm ngoài Master), còn nếu không tìm thấy giá trị TRUE nào thì hàm ISNA trả về TRUE (nghĩa là toàn bộ phần tử MyList đều có trong Master).

Hàm trên chỉ cho ta biết thông tin là một danh sách này có thuộc hay không thuộc một danh sách khác, hàm bên dưới sẽ cho ta biết có bao nhiêu phần tử của MyList không thuộc Master.

{=SUM(1*ISNA(MATCH(MyList,Master,0)))}

Hàm sau sẽ cho biết phần tử đầu tiên trong MyList không thuộc Master là phần tử nào:

{=INDEX(MyList,MATCH(TRUE,ISNA(MATCH(MyList,Master,0)),0))}

p. Cộng các con số của số nguyên

Ví dụ ô A1 chứa số nguyên 8668, bây giờ chúng ta muốn cộng các con số của số nguyên trên 8+6+6+8=28. Để thực hiện điều đó ta dùng công thức sau:

{=SUM(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)}

Công thức này khi tính nó sẽ tạo ra một dãy số nguyên bắt đầu là số 1 và kết thúc lá số các con số chứa trong ô A1. Ví dụ A1 chứa số 8668 thì thì hàm LEN trả về số 4 và hàm ROW khi tính sẽ tạo ra một mảng chứa các giá trị {1,2,3,4}. Sau đó kết quả từ hàm ROW sẽ làm đối số cho hàm MID như sau:

{=MID(8668,{1,2,3,4},1)*1}

Và công thức trên sau khi tính toán sẽ trả về mảng {8,6,6,8}

Sau đó mảng này sẽ làm đối số cho hàm SUM ngoài cùng và trả về kết quả là 28.

Tuy nhiên công thức trên không áp dụng cho số nguyên âm, công thức sau sẽ giải quyết vấn đề được tổng quát hơn.

{=SUM(VALUE(MID(ABS(A1),ROW(INDIRECT("1:"&LEN(ABS(A1)))),1)))}

q. Cộng các giá trị đã làm tròn

Thông thường khi tính toán các giá trị đã làm tròn trên bảng tính, Excel vẫn lấy các giá trị lưu trữ thực sự trong các ô để tính toán chứ không lấy các giá trị làm tròn đang hiển thị trên bảng tính để tính (do định dạng việc hiển thị các con số), do vậy kết quả có thể không như ý muốn. Bạn hãy dùng công thức mảng sau để giải quyết vấn đề này:

=SUM(ROUND(E4:E6,2))

r. Cộng các giá trị cách nhau n khoảng trong dãy

Giả sử bạn có một tập dữ liệu và bạn muốn cộng các phần tử cách nhau 5 phần tử, khi đó bạn nên nghĩ tới công thức sau:

{SUM(IF(MOD(ROW(INDIRECT("1:"&COUNT(Data)))-1,n)=0,Data,""))}

Công thức này trước tiên sẽ tạo ra một mảng trong bộ nhớ chứa các con số liên tục từ 1 đến số phần tử của danh sách (hàm count trả về). Sau đó mảng này sẽ là đối số thứ nhất của hàm MOD và đối số thứ 2 chính là giá trị cách nhau n. Hàm MOD sẽ tạo ra trong bộ nhớ một mảng chứa các số dư của phép chia số thứ tự dòng cho n (các giá trị 0 trong mảng mới này tượng trưng cho việc chia hết cho n). Sau đó mảng này trở thành đối số cho hàm IF, nếu giá trị trong mảng là 0 thì hàm IF trả về giá trị tương ứng của tập dữ liệu và cuối cùng là hàm SUM sẽ cộng tất cả các giá trị thõa điều kiện lại.

Công thức trên sẽ bị sai nếu n là 0 (nghĩa là tổng của tập hợp rỗng). Công thức hiệu chỉnh sau sẽ giải quyết lỗi này:

{=IF(n=0,0,SUM(IF(MOD(ROW(INDIRECT("1:"&COUNT(data)))-1,n)=0,data,"")))}

Lưu ý công thức này chỉ áp dụng cho một cột dữ liệu mà thôi. Công thức trên khi tính lúc nào cũng có giá phần tử thứ nhất trong tập số liệu. Ví dụ n là 5 thì công thức sẽ tính tổng các phần tử thứ 1, 6, 11, 16 … Nếu bạn không muốn phần tử thứ nhất có trong kết quả tính thì hãy dùng công thức hiệu chỉnh sau, công thức này chỉ tính giá trị thứ 5, 10, 15 …:

{=IF(n=0,0,SUM(IF(MOD(ROW(INDIRECT("1:"&COUNT(Data)))-n,n)=0,Data,"")))}

Khi muốn công thức tính cho mảng nằm ngang thì dùng thêm hàm TRANSPOSE như sau;

{=IF(n=0,0,SUM(IF(MOD(TRANSPOSE(ROW(INDIRECT("1:"&COUNT(Data))))-1,n)=0,Data,"")))}

s. Loại bỏ các ký tự khác số khỏi chuỗi

Công thức mảng sau sẽ giúp chúng ta trích được các con số từ một chuỗi có chứa các con số. Ví dụ chuỗi là ABC145Z thì sau khi dùng công thức này sẽ trả về kết quả là 145.

{=MID(A1,MATCH(0,(ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)*1),0),
LEN(A1)-SUM((ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)*1)))}

Lưu ý công thức này chỉ áp dụng cho 1 chuỗi có 1 con số trong đó, kết quả sẽ ra sai nếu có từ 2 con số xen kẽ là ký tự ví dụ như Z253X8 thì kết quả sẽ sai.

t. Xác định giá trị gần đúng nhất trong dãy với giá trị cho trước

Công thức mảng sau sẽ giúp xác định giá trị gần nhất với giá trị cần tìm trong một dãy số. Ví dụ giá trị cần tìm đặt tên là Target và tập số liệu tên là Data.

{=INDEX(Data,MATCH(SMALL(ABS(Target-Data),1),ABS(Target-Data),0))}

Nếu trong Data có 2 giá trị gần nhất so với Target thì công thức sẽ trả về giá trị tìm thấy trước trong danh sách. Như trong hình minh họa thì giá trị gần nhất với 45 là 48.

u. Trả về giá trị cuối cùng trong một cột

Bạn có một bảng tính dùng để cập nhật số liệu hàng ngày vào các cột. Bạn muốn biết giá trị của ô cuối cùng trong cột A có chứa số liệu, nếu cột A không có chứa các ô rỗng (empty) thì ta có thể dùng hàm OFFSET để thực hiện như sau (không dùng công thức mảng):

=OFFSET(A1,COUNTA(A:A)-1,0)

Hàm COUNTA sẽ đếm xem có bao nhiêu ô không rỗng trong cột A và kết quả sẽ trừ bớt 1, kết quả này sẽ là đối số thứ 2 của hàm OFFSET. Ví dụ dòng có giá trị cuối cùng trong cột A là 100, hàm COUNTA sẽ trả về 100 (do vậy phải trừ bớt 1) vì hàm OFFSET sẽ trả về địa chỉ cách ô A1 (làm mốc) 99 dòng.

Nếu cột A có các ô rỗng thì công thức trên sẽ không đúng nữa do hàm COUNTA chỉ đếm các ô không rỗng. Công thức mảng sau sẽ giải quyết được vấn đề nêu trên:

=INDEX(A:A,MAX(ROW(A:A)*(A:A<>"")))

Cẩn thận với việc dùng cách khai báo cả cột A:A trong Excel 2007 (có trên 1 triệu dòng) vì điều này sẽ làm tăng khối lượng tính toán rất đáng kể, thay vào đó bạn nên dự trù số dòng cần kiểm tra thôi ví dụ 500 dòng:

{=INDEX(A1:A500,MAX(ROW(A1:A500)*(A1:A500<>"")))}

v. Trả về giá trị cuối cùng trong một dòng

Tương tự như trên, nhưng ở đây yêu cầu cho biết giá trị của ô cuối cùng trong một dòng. Ví dụ như tìm tại dòng 1 từ A1 đến GR1 (200 cột).

{=INDEX(A1:GR1,MAX(COLUMN(A1:GR1)*(A1:GR1<>"")))}

Nếu bạn muốn kiểm tra toàn bộ dòng 1 thì dùng:

{=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>"")))}

Tuy nhiên vì Excel 2007 có 16384 cột nên cách này sẽ ảnh hưởng rất lớn đến tốc độ tính toán của bảng tính.

w. Xếp hạng bằng công thức mảng

Hàm xếp hạng RANK của Excel đôi khi không đáp ứng được nhu cầu xếp hạng của chúng ta. Giả sử hình bên dưới có 2 giá trị bằng nhau do vậy hàm Rank sẽ xếp đồng hạng 3, tuy nhiên ta muốn xếp hạng theo giá trị trung bình tức là hạng 3.5. Khi đó chúng ta dùng công thức mảng sau:

Tại ô D5 nhập vào: {=SUM(1*(B5<=Sales))-(SUM(1*(B5=Sales))-1)/2}

Sau đó chép công thức cho các dòng còn lại.

x. Tạo bảng chéo động

Ta muốn tạo bảng báo cáo thống kê về từng nhóm hàng (Category) trong từng tháng như hình trên. Khi đó chúng ta lập các nhãn các nhóm hàng tại F5:H5 và lập nhãn các tháng tại E6:E9. Tại ô F6 hãy nhập vào công thức mảng sau: (A5:A23 đặt tên là Date; B5:B23 tên là Category và C5:C23 đặt tên là Amount)

{=SUM(($E6=Date)*(F$5=Category)*Amount)} à chép công thức cho các ô còn lại

y. Đếm nhiều điều kiện

Đếm xem có bao nhiêu dòng thõa 2 điều kiện là bằng A và 1980.

Dùng hàm SUM: {=SUM((A1:A8="A")*(B1:B8=1980))}

Dùng IF: {=SUM(IF(A1:A8="A",1,0)*IF(B1:B8=1980,1,0))}

Dùng Sumproduct: =SUMPRODUCT((A1:A8="A")*1,(B1:B8=1980)*1) (hàm thường)

z. Đếm số phần tử duy nhất trong danh sách

Phép đếm này sẽ loại ra các phần tử bị trùng trong danh sách chỉ giữ lại một phần tử. Ví dụ đếm trong vùng A1:A8 có bao nhiêu phần tử khác nhau.

Công thức tổng quát:

Dùng Sum: {=sum(1/countif(vùngđếm, vùngđếm))}

Nếu tập số liệu có chứa ô rỗng thì dùng

{=SUM(IF(COUNTIF(vùngđếm, vùngđếm)=0,"",1/COUNTIF(vùngđếm, vùngđếm)))}

Sumproduct: =sumproduct((1/countif(vùngđếm, vùngđếm))*1) (công thức thường)

Ví dụ:

Dùng Sum: {=SUM(1/COUNTIF(A1:A8,A1:A8))}

Sumproduct: =SUMPRODUCT((1/COUNTIF(A1:A8,A1:A8))*1)

aa. Cộng các phần tử trong mảng và không tính nếu phần tử xuẩt hiện lần thứ 2 trở lên.

{=sum(vungso/countif(vungso,vungso))}

Ý tưởng thì giống như trên như ở trên có điều mỗi lần lước qua một phần tử trong mảng ta không tính giá trị là 1 là tính giá trị là chính con số đó.

Sắp xếp hạng liên tục

[=SUM(1/(IF($A$2:$A$13>A2,COUNTIF($A$2:$A$13,$A$2:$A$13),9.999999E+307)))+1}

Vận dụng công thức của SoiBien, chuyển thành hàm sumproduct, khỏi phải Ctr Shift Enter
D5=1+SUMPRODUCT((VungRank>$A2)*(1/COUNTIF(VungRank,VungRank)))
VungRank là vùng có số liệu cần xếp TT.

Top

Công thức mảng trả kết quả về nhiều ô

a. Tạo mảng số nguyên liên tục

Ở phần trên, các bạn chú ý hàm {=ROW(INDIRECT("1:n")} sẽ cho ra 1 dãy số gồm các số nguyên liên tục từ 1 đến n. Và đây cũng chính là công thức mảng giúp bạn nhập số thứ tự vào 1 dãy chọn trước. Ta thử xem hàm này họat động ra sao nhé

Hàm ROW trong Excel trả kết quả về một số hàng. Nếu ta nhập công thức mảng : {=ROW(1:12)} vào một dãy A1:A12 nằm dọc có 12 ô, ta sẽ nhận được một mảng gồm các số nguyên liên tục từ 1 đến 12. Nhưng nếu ta chèn thêm một hàng vào dãy trên nằm ở trên A1, ta sẽ thấy công thức trên trở thành {=ROW(2:13)} và mảng của ta sẽ có 12 số nguyên liên tục từ 2 đến 13.

Do đó, ta phải sử dụng kết hợp với hàm INDIRECT. Hàm INDIRECT lấy đối số là một chuỗi Text. INDIRECT("1:12") sẽ luôn luôn cho kết quả là (1:12), vì Excel không thể điều chỉnh được các tham số chuỗi trong hàm INDIRECT được. Sự kết hợp này luôn đảm bảo cho hàm {=ROW(INDIRECT("1:12")} cho kết quả là một mảng gốm các số từ 1 đến 12 trong mọi trường hợp.

b. Chỉ trả về các giá trị dương trong dãy

Ta có một cột số liệu (tên là DATA) có các giá trị âm và dương, và ta muốn trích ra các số dương trong cột số liệu đó (không trích các số 0 và số âm). Để dùng hàm mảng trước tiên bạn chọn một vùng trên bảng tính có kích thước bằng với cột số liệu và nhập công thức sau vào

{=INDEX(Data,SMALL(IF(Data>0,ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data)))))}

Nhớ nhấn tổ hợp phím Ctrl+Shift+Enter khi kết thúc. Kết quả như hình cột C ở bên dưới

Kết quả trong cột C chưa hoàn hảo vì có các lỗi #NUM, do ta chưa kiểm tra lỗi trước khi xuất kết quả. Công thức sau sẽ giải quyết ổn thoã:

Excel 2007 thì dùng:

{=IFERROR(INDEX(Data,SMALL(IF(Data>0,ROW(INDIRECT("1:"&ROWS(Data)))),
ROW(INDIRECT("1:"&ROWS(Data))))),"")}

Muốn tính tương thích cao với các phiên bản cũ thì dùng công thức sau:

{=IF(ISERR(SMALL(IF(Data>0,ROW(INDIRECT("1:"&ROWS(Data)))),
ROW(INDIRECT("1:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data>0,
ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))))}

c. Trả về các ô Nonblank trong dãy

Có dãy số liệu tên là DATA, trong đó có chứa các blank (ô trống). Chúng ta dùng các công thức sau:

Excel 2007:

{=IFERROR(INDEX(Data,SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))),"")}

Công thức dưới sẽ tương thích các phiên bản trước:

{=IF(ISERR(SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))),"",
INDEX(Data,SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))))}

Lưu ý chọn trước vùng kết quả tương ứng với kích thước cột dữ liệu và kết thúc công thức bằng tổ hợp phím Ctrl+Shift+Enter.

d. Đảo thứ tự các ô trong dãy

Ta có một tập số liệu tên là DATA trong một cột của bảng tính, và ta muốn đảo thứ tự sắp xếp của cột số liệu này theo chiều ngược lại. Khi đó bạn hãy xem công thức mảng sau:

{=IF(INDEX(Data,ROWS(data)-ROW(INDIRECT("1:"&ROWS(Data)))+1)="","",
INDEX(Data,ROWS(Data)-ROW(INDIRECT("1:"&ROWS(Data))) +1))}

Thứ tự dữ liệu trong cột C đã bị đảo ngược so với cột A. Lưu ý chọn trước vùng kết quả tương ứng với kích thước cột dữ liệu và kết thúc công thức bằng tổ hợp phím Ctrl+Shift+Enter.

Ghi chú: Các ô trong bảng tính khi mới tạo ra sẽ có thuộc tính Blank (ô trống) (hay Null). Các ô Empty (ô rỗng) là các ô có chứa chuỗi rỗng "". Trong VB (hay VBA) phép so sánh với Nothing là đã bao gồm cả blank và empty.

e. Sắp xếp các giá trị số trong một dãy động

Ta có một cột chứa các giá trị số trong bảng tính (đặt tên là DATA), bây giờ ta muốn sắp xếp các giá trị này từ lớn xuống nhỏ một cách động với dữ liệu nguồn. Khi đó ta dùng công thức sau:

{=LARGE(Data,ROW(INDIRECT("1:"&ROWS(Data))))}

Công thức trên sẽ bị lỗi #NUM khi trong cột số liệu DATA có các ô trống, để khắc phục ta dùng hàm sau:

Excel 2007:

{=IFERROR(LARGE(Data,ROW(INDIRECT("1:"&ROWS(Data)))),"")}

Công thức dưới sẽ tương thích các phiên bản trước:

{=IF(ISERR(LARGE(Data,ROW(INDIRECT("1:"&ROWS(Data))))),"",LARGE(Data,ROW(INDIRECT("1:"&ROWS(Data)))))}

Muốn sắp xếp theo trình tự từ nhỏ đến lớn thì thay hàm SMALL cho hàm LARGE.

f. Trả về danh sách các phần tử duy nhất trong một dãy

Ta có một sột dữ liệu tên là DATA có các dữ liệu trùng nhau, công thức sau sẽ giúp trích ra một sách mà mỗi phần tử là duy nhất trong danh sách này.

{=INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0),""),
ROW(INDIRECT("1:"&ROWS(data)))))}

Công thức trên sẽ bị lỗi #NUM nếu cột dữ liệu có các ô trống (blank), công thức sau sẽ giải quyết được lỗi này:

Excel 2007:

{=IFERROR(INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECTd("1:"&ROWS(data))), MATCH(Data,Data,0),""),ROW(INDIRECT("1:"&ROWS(Data))))),"")}

Công thức dưới sẽ tương thích các phiên bản trước:

{=IF(ISERR(INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))), MATCH(data,data,0),""),ROW(INDIRECT("1:"&ROWS(data)))))),"",

INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))), MATCH(data,data,0),""),ROW(INDIRECT("1:"&ROWS(data))))))}

g. Tạo dương lịch

Để tạo ra cuốn lịch tây như ý của mình, sau khi nhập tháng và năm vào ô B2, làm nhãn các ngày trong tuần cho các ô B3:H3. Sau đó chọn các ô B4:H9 và nhập vào công thức mảng:

{=IF(MONTH(DATE(YEAR(B2),MONTH(B2),1))<>MONTH(DATE(YEAR(B2),
MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",
DATE(YEAR(B2),MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}

Kết thúc công thức nhấn tổ hợp phím Ctrl+Shift+Enter.

Top