Hướng Dẫn Và Ví Dụ Về Công Thức Mảng - Microsoft Support

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

• Tạo 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 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 nhọn mở) trước khi bạn nhập 10 và } (dấu ngoặc nhọn đóng) sau khi bạn nhập 180 vì bạn đang tạo ra một mảng cá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ừ cá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 phạm vi ô

  Ví dụ sau đây cho bạn thấy cách đếm số ký tự trong một phạ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 của từng chuỗi văn bản trong mỗi ô trong phạm vi đó. Sau đó, hà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 của ô dà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 phạ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 là 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 mả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) chỉ rõ 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 phạ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 các hàm khác trong công thức này, chẳng hạn như hàm SUM hay AVERAGE. Ví dụ:

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

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

• Tìm n giá trị lớn nhất trong một phạm vi

  Để tìm giá trị lớn nhất trong một phạ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")))

  Lúc nà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 cá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 phạm vi chứa công thức mảng (tức là, trên hàng 1). Excel sẽ điều chỉnh tham chiếu hàng và công thức hiện sẽ tạo ra các số nguyên từ 2 đến 11. Để khắc phục vấn đề đó, bạn hã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 phạ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 cách khác. Do đó, hàm ROW sẽ 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ộ các giá trị văn bản, trong trường hợp này là các giá trị từ 1 đến 3. Đến lượt mình, hàm ROW tạo ra mảng cột gồm ba ô. Hàm LARGE dùng các giá trị trong phạm vi ô B9:B18 và nó đượ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 hãy thêm một phạ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ư hàm SUM và AVERAGE.