Mảng và công thức mảng

[handung107]

Phần 1 : Mảng một chiều, mảng hai chiều và hằng mảng
1/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 thẳng đứng)
Các phần tử trong một 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 thẳng đứng được cách nhau bằng dấu chấm phẩy
Td : {1,2,3,4,5} (mảng ngang ) và {10;20;30;40;50} (mảng thẳng đứng)
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 thẳng đứng, ta cần 5 ô liên tục trên cùng một hàng hay một cột
Td : Ta chọn 5 ô từ A1:E1, nhập : ={1,2,3,4,5} và nhấn Ctrl+Shift+Enter
Hoặc chọn 5 ô từ A1:A5, nhập : ={1;2;3;4;5} và nhấn Ctrl+shift+Enter
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 6 ô để nhập mảng thì ô thứ 6 sẽ cho giá trị #N/A
2/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
Td : {1,2,3,4;5,6,7,8;9,10,11,12}
Để hiển thị mảng này, ta chọn vùng A13, nhập :
={1,2,3,4;5,6,7,8;9,10,11,12} và nhấn Ctrl+Shift+Enter
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
3/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.
Td : 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}). Công thức này 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 như sau :{5,12,21,32}
Sau đó, mảng này lại là đối số cho hàm SUM và cho ra 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)
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%}
Việc đặt tên cho hằng mảng được thông qua hộp thọai Insert/Name/Define
Tên của mảng đặt tại hộp Name : DayNames
Tại hộp Refers to ta đặt dấu ={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}
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.
Để 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.

 

[handung107]

Làm việc với công thức mảng

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

Giả sử ta có một 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

2/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

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

Ta có thể chọn bằng tay, hoặc dùng Edit/Go to (hoặc nhấn F5), nhấp nút Special, sau đó chọn Current array, nhấp OK để đóng hộp thọai

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

-Bạn khôn gthể 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
-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)
Để 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, và khi bạn hiệu chỉnh xong, bạn sẽ nhấn Ctrl+Shift+Enter để kết thúc

 

[handung107]

Công thức mảng một ô và công thức mảng nhiều ô

A/ Làm việc với công thức mảng một ô :

1/ Đếm các ký tự trong một dãy : (bạn có thể tham khảo File ví dụ của levanduyet)
Giả sử bạn có một dãy ô, chứa các dữ liệu dạng Text từ A1:A14, 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:B14.
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 B14. Sau đó, tại B15, bạn dùng công thức tính tổng : =SUM(B1:B14)
Để 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 A15 {=SUM(LEN(A1:A14)}. 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.

2/Đế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
{=SUM(IF(ISTEXT(A115),1,0))}
Hay : {=SUM(ISTEXT(A115)*1)}

3/Đế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(A115),1,0))}
Hay : {=SUM(IF(ISERROR(A115),1))}
{=SUM(ISERROR(A115)*1)}

4/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(A115),"",A115)}

5/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.

5/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}))}

6/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

Trên đây là một số thí dụ đơn giản về công thức mảng cho một ô, nghĩa là ta chỉ chọn một ô và nhập vào đó công thức mảng, sau đó sao chép sang các ô khác. Còn công thức mảng nhiều ô nghĩa là ta chọn cùng một lúc nhiều ô và nhập vào đó cùng một công thức mảng. Chúng ta sẽ tiếp tục trong những kỳ sau

 

[handung107]

Công thức mảng một ô & công thức mảng nhiều ô (tt)

7/Tạo một mảng các số nguyên liên tục :

Ở phần 6, 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

8/Tìm một giá trị trung bình lọai trừ giá trị zero :

Giả sử ta muốn tính giá trị trung bình doanh thu trong một năm, nhưng trong năm, ta có 2 tháng doanh số =0. Giá trị doanh thu hàng tháng (lọai trừ tháng không có DT) được đặt trong cột B5:B16. Ta lập công thức mảng như sau :
{=AVERAGE(IF(B5:B16)<>0,B5:B16)}.
Công thức này sẽ tạo ra một mảng mới gồm các giá trị khác 0, và hàm AVERAGE sử dụng mảng mới này làm đối số của nó. Bạn cũng có thể sử dụng công thức tương đương sau, nếu không muốn dùng công thức mảng :
=SUM(B5:B16)/COUNTIF(B5:B16,"<>0")

9/Lọai bỏ các công thức trung gian :

Một trong những ưu điểm của việc sử dụng công thức mảng là lọai trừ được những công thức trung gian. Giả sử ta có cột A là tên Mặt Hàng, cột B là Giá Vốn, cột C là Giá Bán và cột D tạm gọi là Lãi.
Ta có mảng dữ liệu từ hàng thứ 2 D2=C2-B2 và sao chép công thức đến hàng thứ 10.
Sau đó, ta tính tổng D11=SUM(D210).
Nếu ta dùng công thức mảng, ta sẽ tính ngay tại một ô nào đó, TD : C11
C11 :{=SUM(C2:C10-B2:B10)}
Nếu muốn xem, mặt hàng nào lãi nhiều nhất, ta dùng : {=MAX(C2:C10-B2:B10)}
và mặt hàng nào lãi ít nhất sẽ là : {=MIN(C2:C10-B2:B10)}. Ta hầu như lọai bỏ được cột trung gian là cột D

 

[handung107]

Công thức mảng một ô & công thức mảng nhiều ô (tt)

10/ Xác định 1 giá trị cụ thể có trong 1 dãy không ?

Để xác định một giá trị cụ thể có trong dãy đó không, ta thường sử dụng chức năng Edit / Find. Nhưng ta vẫn có thể làm việc này chỉ với công thức mảng. Các bạn có thể tham khảo công thức trong File của bạn Levanduyet
Giả sử ta có một DS các tên từ A4:C13. Ta dùng ô B2 để nhập tên mà ta muốn kiểm tra xem có trong mảng không ? Ta đặt tên B2 là TheName và mảng tên là NameList. Nếu tìm thấy, sẽ cho kết quả tại Cell E4 là "Name in the List", và nếu không có là "Name not Found".
Công thức trong E4 là :
{=IF(OR(TheName=NameList),"Name in the List","Name not Found")}
Công thức này so sánh TheName với mỗi ô trong dãy NameList. Nó tạo ra một mảng mới gồm các giá trị True và False. Hàm OR trả về True nếu bất kỳ một trong các giá trị trong mảng mới là True. Hàm IF sử dụng kết quả này để xác định thông báo nào được hiển thị
Đơn giản hơn, ta có thể dùng : {=OR(TheName=NameList)} sẽ cho kết quả True nếu tên được tìm thấy và False nếu tên không tìm thấy

11/ 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))}

 

[SA_DQ]

Hàm tự tạo trả về 1 mảng như một kết quả lọc

CSDL là trường cấp 2 -3 tại: http://webketoan.com/forum/showthread.php?t=17161 ( ở đây trường MaTinh - cột 7) các bạn tự thêm vô giúp)
Function Loc4DK(Sh1 As Object, Lop As String)
1 Dim K_Qua(1 To 30, 1 To 4) As Variant
2 Dim jZ As Integer, zJ As Integer
3 Application.ScreenUpdating = 0
For jZ = 1 To 999
5 If Sh1.Cells(jZ, 6) = Lop And Sh1.Cells(jZ, 5) = 0 And Mid(Sh1.Cells(jZ, 4), 4, 2) > "09" And Sh1.Cells(jZ, 7) = "08" Then
6 zJ = zJ + 1
7 K_Qua(zJ, 2) = Sh1.Cells(jZ, 3): K_Qua(zJ, 3) = Sh1.Cells(jZ, 4)
8 K_Qua(zJ, 4) = Sh1.Cells(jZ, 6): K_Qua(zJ, 1) = Sh1.Cells(jZ, 1)
9 End If
10 Next jZ
11 Loc4DK = K_Qua
End Function
Để nhận được kết quả ta chọn từ ô O1:R30 tại sheet 'S1' & nhập hàm tự tạo: =Loc4DK( A2:G989; K1) lên thanh CT & kết thúc bằng tổ hợp 3 fím sẽ cho kết quả các em HS nam ngụ tại TP HCM (có mã tỉnh = '08), có ngày sinh thuộc quí 4 theo lớp chọn từ ComboBox sẵn có trên S1 của CSDL.
Có nghĩa dùng Combo sẽ lọc được danh sách HS lần lượt từng lớp thoả 3 Đ/K còn lại nêu trên.

 

[vit_dong]

Cám ơn chủ thread rất nhiều.
Bài viết đã được viết từ rất lâu rồi bây giờ mình mới được học thật là quá lạc hậu