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

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

Một công thức mảng là một công thức có thể thực hiện nhiều phép tính trên một hoặc nhiều mục trong một mảng. Bạn có thể nghĩ đến một mảng như một hàng hoặc cột giá trị, hoặc tổ hợp các hàng và cột của các giá trị. Các 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 2018 tháng chín cho Office 365, bất kỳ công thức nào có thể trả về nhiều kết quả sẽ tự động đổ chúng xuống hoặc qua vào các ô lân cận. Điều này thay đổi trong hành vi cũng đi kèm với một số hàm mảng độnglực mới. Các công thức mảng động lực, cho dù họ đang sử dụng các hàm hiện có hoặc hàm Array động, chỉ cần nhập vào một ô duy nhất, sau đó xác nhận bằng cách nhấn Enter. Trước đó, công thức mảng kế thừa yêu cầu đầu tiên chọn phạm vi toàn bộ đầu ra, sau đó xác nhận công thức với Ctrl + Shift + Enter. Chúng thường được gọi là công thức CSE .

Bạn có thể sử 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 biểu dữ liệu mẫu.

  • Đếm số ký tự chứa trong một phạm vi ô.

  • Chỉ tính tổng các số đáp ứng các điều kiện nhất định, chẳng hạn như các giá trị thấp nhất trong một phạm vi hoặc các số nằm giữa ranh giới phía trên và bên dưới.

  • Tính tổng mọi giá trị thứ n trong một phạm vi giá trị.

Ví dụ sau đây cho bạn biết cách tạo các công thức mảng nhiều ô và một ô đơn. Nếu có thể, chúng tôi đã bao gồm các ví dụ với một số hàm Array động, cũng như các công thức mảng hiện có được nhập vào như cả mảng năng động và thừa kế.

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

Tải xuống một 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 này.

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 cá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 ô

    Hàm Multi-Cell Array trong ô H10 = F10: F19 * 5Mp: G19 để tính toán số xe được bán theo đơn giá

  • Ở đây, chúng tôi đang tính toán tổng doanh thu của các số điện thoại và sedan cho từng nhân viên bán hàng bằng cách nhập = F10: F19 * 5MP: G19 trong ô H10.

    Khi bạn nhấn Enter, bạn sẽ thấy các kết quả tràn xuống đến 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ỳ ô nào trong phạm vi tràn. Bạn cũng có thể thấy rằng các công thức trong các ô H10: H19 bị xám. Họ vừa mới có tham chiếu, vì vậy nếu bạn muốn điều chỉnh công thức, bạn sẽ cần phải chọn ô H10, trong đó có cuộc sống công thức chính.

  • Công thức mảng một ô

    Công thức mảng một ô để tính tổng số lớn với = SUM (F10: F19 * 5Mp: G19)

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

    Trong trường hợp này, Excel nhân các giá trị trong mảng (phạm vi ô F10 qua G19), rồi sử dụng hàm SUM để thêm tổng cộng với nhau. Kết quả tổng cộng doanh số bán hàng là $1.590.000.

    Ví dụ này cho thấy kiểu công thức này có hiệu quả tới mức nà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. Ngoài ra, hãy lưu ý rằng công thức một ô đơn lẻ 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 là 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. Nó cũng có thể thực hành tốt để có tổng số độc lập như thế này, vì điều này giúp xác thực tính chính xác của kết quả của bạn.

  • Công thức mảng động cũng cung cấp các ưu điểm sau:

    • Tính nhất quán    Nếu bạn bấm vào bất kỳ ô nào từ H10 xuống, bạn có thể 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ụ, bấm ô H11, rồi nhấn Delete. Excel sẽ không thay đổi đầu ra của mảng. Để thay đổi nó, bạn cần chọn ô trên cùng bên trái trong mảng hoặc ô H10.

    • Kích cỡ tệp nhỏ 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ụ về xe bán hàng sử dụng một công thức mảng để tính toán các kết quả trong cột E. Nếu bạn đã sử dụng các công thức tiêu chuẩn, chẳng hạn như = F10 * 5Mp, F11 * G11, F12 * G12, v.v., bạn đã sử dụng 11 công thức khác nhau để tính toán cùng một kết quả. Đó không phải là vấn đề lớn, nhưng nếu bạn có hàng nghìn hàng cho tổng? Sau đó, nó có thể tạo ra sự khác biệt lớn.

    • Hiệu quả    Hàm Array có thể là 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 * 5Mp: G19) tương tự như thế này: = SUM (F10 * $, F11 * G11, F12-G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * G18, F19 * G19).

    • Tràn    Các 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 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.

    • #SPILL! báo    Mảng động giới thiệu #SPILL! lỗi, cho biết phạm vi tràn dự định bị chặn vì lý do nào đó. Khi bạn giải quyết việc tắc nghẽn, 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 sách này trong một cặp dấu ngoặc nhọn ({ }) theo cách thủ công, như thế này:

= {1, 2, 3, 4, 5} hoặc = {"tháng một", "tháng hai", "Tháng ba"}

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 hãy phân cách các mục trong mỗi hàng bằng dấu phẩy và delimit mỗi hàng có dấu chấm phẩy.

Quy trình sau đây sẽ giúp bạn thực hà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ị ví dụ bằng cách dùng hàm SEQUENCE để tự động tạo các hằng số mảng, cũng như các hằng số mảng được nhập theo cách thủ cô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 bất kỳ ô trống nào và nhập = SEQUENCE (1, 5). Hàm SEQUENCE xây dựng một hàng theo 5 mảng cột giống như = {1, 2, 3, 4, 5}. Kết quả sau đây được hiển thị:

    Tạo hằng số mảng ngang với = SEQUENCE (1, 5) hoặc = {1, 2, 3, 4, 5}

  • Tạo hằng số dọc

    Chọn bất kỳ ô trống nào với phòng bên dưới nó, rồi 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ố mảng dọc với = SEQUENCE (5), hoặc = {1; 2; 3; 4; 5}

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

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

    Tạo hằng số 3 hàng theo mảng 4 cột với = SEQUENCE (3, 4)

    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 nơi bạn đặt bán dấu chấm so với dấu phẩy.

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

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

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

Trong ô E11, 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 đã dùng = SUM (D9: H (* SEQUENCE (1, 5))

Hàm SEQUENCE xây dựng tương đương với hằng số mảng {1, 2, 3, 4, 5}. Vì Excel thực hiện các thao tác trên các biểu thức được đặt trong dấu ngoặc đơn đầu tiên, hai thành phần tiếp theo đi vào phát là các giá trị ô trong D9: H9 và toán tử phép nhân (*). Tại thời điểm này, công thức nhân các giá trị trong mảng được lưu trữ với cá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à trả về 85.

Để tránh sử dụng mảng đã lưu trữ và giữ lại thao tác hoàn toàn trong bộ nhớ, bạn có thể thay thế nó bằng một 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 yếu tố mà bạn có thể dùng trong hằng số mảng

  • Các 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 chẳng hạn như #N/A. Bạn có thể sử dụng các số theo định dạng số nguyên, thập phân và khoa học. Nếu bạn bao gồm văn bản, bạn cần bao quanh dấu ngoặc kép ("văn bản").

  • Hằng số mảng không được chứa thêm các mảng, công thức hoặc các hàm khá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 sẽ 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)}. Ngoà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 để sử dụng hằng số mảng là đặt tên cho họ. 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 tạp trong công thức mảng để người khá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, bạn hãy làm như sau:

Đi tới công thức > tên đã xác định > xác định tên. Trong hộp tên , nhập Quarter1. Trong hộp Tham chiếu đến, bạn hãy nhập hằng số sau đây (hãy nhớ nhập dấu ngoặc nhọn theo cách thủ công):

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

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

Thêm một 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, sau đó chọn bất kỳ hàng nào với ba ô trống, rồi nhập = Quarter1.

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

Sử dụng hằng số mảng đã đặt tên trong công thức, chẳng hạn như = Quarter1, trong đó Quarter1 đã được xác định là = {"tháng một", "tháng hai", "Tháng ba"}

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

Nếu bạn muốn hiển thị một danh sách 12 tháng, chẳng hạn như bạn có thể sử dụng khi xây dựng một câu lệnh tài chính, bạn có thể căn cứ một ra khỏi năm hiện tại với hàm SEQUENCE. Điều gọn gàng về hàm này là ngay cả khi chỉ hiển thị tháng, có một ngày hợp lệ phía sau nó 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ụ sau đây trên các trang tính của mảng dữ liệu được đặt tên mẫu nhanh trong sổ làm việc ví dụ.

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

Sử dụng kết hợp các hàm văn bản, ngày tháng, năm, hôm nay và trình tự để xây dựng một danh sách năng động trong 12 tháng

Tính năng này sử dụng hàm date để tạo ngày dựa trên năm hiện tại, trình tự tạo một hằng số mảng từ 1 đến 12 cho tháng một đến tháng mười hai, sau đó là hàm Text sẽ chuyển đổi định dạng hiển thị thành "Mmm" (Jan, Feb, Mar, 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 muốn sử dụng "Mmmm".

Khi bạn sử dụng một hằng số có tên là công thức mảng, hãy nhớ nhập dấu bằng, như trong = Quarter1, 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ể sử dụng tổ hợp hàm, văn bản và số. Tất cả phụ thuộc vào cách sáng tạo bạn muốn nhậ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ụ sử dụng hàm transpose để chuyển đổi hàng thành cột và ngược lại.

  • Nhiều mục trong một mảng

    Nhập = trình tự (1, 12) * 2, hoặc = {1, 2, 3; 5, 6, 7, 8; 9, 10, 11, 12} * 2

    Bạn cũng có thể phân chia với (/), thêm bằng (+) và trừ bằng (-).

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

    Nhập = trình tự (1, 12) ^ 2, hoặc = {1, 2, 3; 5, 6, 7, 8; 9, 10, 11, 12} ^ 2

  • Tìm căn bậc chính của các mục bình phương trong một mảng

    Nhập =sqrt(trình tự (1, 12) ^ 2), hoặc = sqrt ({1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} ^ 2)

  • Hoán vị 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 sẽ chuyển đổi hằng số mảng thành cột.

  • Hoán vị 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 sẽ chuyển đổi hằng số mảng thành hàng.

  • Hoán vị 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 sẽ 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ừ các giá trị hiện có

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

    Enter = SEQUENCE (3, 6, 10, 10), hoặc = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    Hãy đảm bảo 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 một mảng số.

    Tiếp theo, nhập = D9 #, hoặc = D9: I11 trong một ô trống. Một mảng 3 x 6 của các ô xuất hiện cùng các giá trị mà bạn nhìn thấy trong D9: D11. Dấu # được gọi là toán tử phạm vi spilledvà đó là cách để tham chiếu toàn bộ phạm vi mảng thay vì phải nhập nó ra.

    Sử dụng toán tử phạm vi spilled (#) để tham chiếu một mảng hiện có

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

    Bạn có thể thực hiện kết quả của công thức mảng spilled và chuyển đổi thành các phần cấu phần của nó. Chọn ô D9, rồi nhấn F2 để chuyển sang chế độ chỉnh sửa. Tiếp theo, hãy nhấn F9 để chuyển đổ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ờ đây 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 biết cách đếm số lượng ký tự trong một phạm vi ô. Điều này bao gồm khoảng trống.

    Đếm tổng số ký tự trong một dải ô và các mảng khác để làm việc với chuỗi văn bản

    = SUM (LEN (C9: C13))

    Trong trường hợp này, hàm len trả về độ dài của mỗi chuỗi văn bản trong mỗi ô trong phạm vi. Hàm SUM sau đó cộng các giá trị này với nhau và hiển thị kết quả (66). Nếu bạn muốn có số lượng 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 phạm vi 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 mỗi mục trong phạm vi ô D2: D6. Hàm Max tính toán 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, vốn nằm 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 bù trừ (vị trí tương đối) của ô có 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)

    Đối số khớp kiểu trong trường hợp này là 0. Loại kết hợ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 vùng tra cứu Val

    • 0-trả về giá trị đầu tiên chính xác bằng 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 sẽ có các đối số sau đây: một mảng và một 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) xác định giá trị xuất phát từ cột đầu tiên trong mảng.

    Nếu bạn muốn lấy nội dung của chuỗi văn bản nhỏ nhất, bạn sẽ thay thế tối đa trong ví dụ ở trên với phút.

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

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

    Công thức mảng Excel để tìm giá trị nhỏ nhất Nth: = SMALL (B9 #, trình tự (D9))

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

    Công thức này sử 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 có chứa trong các ô B9: B18, trong đó 3 là một 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 tham đối 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 (NHỎ (B9 #, TRÌNH TỰ (D9))

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

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

    Để tìm các giá trị lớn nhất trong một phạm vi, bạn có thể thay thế hàm SMALL với hàm Large. Ngoài ra, ví dụ sau đây dùng các hàm ROWINDIRECT.

    Nhập = Large (B9 #, Row (gián tiếp ("1:3"))), hoặc = Large (B9: B18, Row (gián tiếp ("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 trống và 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 điều chỉnh các tham chiếu hàng và công thức hiện đang tạo 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 gián tiếp sử 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 bao quanh bởi dấu ngoặc ké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 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 chỉ có thể dễ dàng sử dụng trình tự:

    = TRÌNH TỰ (10)

    Hãy kiểm tra công thức mà bạn đã sử dụng trước đó — = LARGE (B9 #, ROW (gián tiếp ("1:3"))-bắt đầu từ dấu ngoặc đơn bên trong và làm việc ra nước ngoài: hàm gián tiếp trả về một tập giá trị văn bản, trong trường hợp này, các giá trị từ 1 đến 3. Hàm ROW in the Turn tạo ra một mảng cột 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 được trả về bởi hàm ROW. 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ụ nhỏ, bạn có thể sử dụng công thức này với các hàm khác, chẳng hạn như tổng và trung bình.

  • Cộng tổng một phạm vi chứa các giá trị lỗi

    Hàm SUM trong Excel không hoạt động khi bạn tìm cách tính tổng phạm vi có chứa một giá trị lỗi, chẳng hạn như #VALUE! hoặc #N/A. Ví dụ này cho bạn biết cách tính tổng các giá trị trong một phạm vi có tên là dữ liệu có chứa lỗi:

    Dùng mảng để xử lý lỗi. Ví dụ, = SUM (IF (ISERROR (dữ liệu), "", dữ liệu) sẽ tính tổng phạm vi có tên dữ liệu ngay cả khi nó bao gồm các lỗi, chẳng hạn như #VALUE! hoặc #NA!.

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

    Công thức này tạo ra một mảng mới, trong đó chứa các giá trị ban đầu nhưng loại trừ mọi giá trị 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 cá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 định trị là TRUE, trả về giá trị khác nếu nó định trị là 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 vì chúng định trị là TRUE, nó trả về các giá trị còn lại từ phạm vi (Data) vì chúng định trị là FALSE, có nghĩa là chúng không chứa giá trị lỗi. Sau đó, hàm SUM tính tổng cộng cho mảng đã lọc.

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

    Ví dụ này giống như công thức trước đó, nhưng nó trả về số lượng giá trị lỗi trong phạm vi có tên là dữ liệu thay vì lọc chúng ra:

    =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 nà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 chỉ rõ đối số, hàm IF trả về FALSE nếu ô không chứa giá trị lỗi. Thậm chí 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 vì TRUE*1=1 và FALSE*1=0.

Bạn có thể cần tính tổng các giá trị theo các điều kiện.

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

Ví dụ, công thức mảng này chỉ tính số nguyên dương trong một phạm vi có tên là doanh số, đạ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ột mảng các giá trị dương và false. Về cơ bản, hàm SUM bỏ qua các giá trị false vì 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 giá trị lớn hơn 0 nhỏ hơn 2500:

= SUM ((doanh số>0) * (doanh số<2.500) * (doanh số))

Hãy nhớ rằng công thức này trả về lỗi nếu phạm vi đó 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 hoặc nhỏ hơn 2500:

= SUM (IF ((Sales>0) + (doanh số<2500))

Bạn không dùng được hàm AND và OR trực tiếp trong công thức mảng vì các hàm đó trả về một kết quả duy nhất, TRUE hoặc FALSE, cò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 các thao tác toán học, chẳng hạn như cộng hoặc phép tính đối với các giá trị đáp ứng hoặc hoặc và điều kiện.

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 các giá trị trong phạm vi đó. Công thức này dùng phạm vi dữ liệu có tên là Sales:

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

Hàm IF tạo ra mảng chứa các giá trị khá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 các giá trị trong hai phạm vi ô có tên là MyData và YourData và trả về số lượng các khác biệt giữa hai phạm vi này. Nếu nội dung của hai phạm vi này giống hệt nhau, công thức trả về 0. Để sử dụng công thức này, phạm vi ô cần có cùng kích cỡ và cùng kích thước. Ví dụ, nếu dữ liệu mylà một phạm vi của 3 hàng theo 5 cột, thì dữ liệu cũng phải là 3 hàng bằ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 cá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 đó, 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 nà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 vì TRUE*1=1 và 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 là 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 là Data. Nếu một ô tương ứng chứa giá trị tối đa trong phạm vi đó, thì mảng sẽ 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 phạm vi Data. Nếu phạm vi có tên là Data chứa nhiều giá trị tối đa giống hệt nhau, thì 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ế củ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 về sự khác biệt giữa trang tính biểu dữ liệu.

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 cá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 ô

Sao chép toàn bộ bảng bên dưới và dán nó vào ô A1 trong một trang tính trống.

Doanh số Người

Xe hơi Nhập

Số Đã bán

Đơn vị Giá cả

Tổng cộng Doanh số

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)

  1. Để xem tổng doanh số bán hàng và sedan cho từng nhân viên bán hàng, hãy chọn ô E2: E11, nhập công thức = C2: C11 * D2: D11, rồi nhấn Ctrl + Shift + Enter.

  2. Để xem tổng số lớn của tất cả doanh thu, hãy chọn ô F11, nhập công thức = sum (C2: C11 * D2: D11), rồi nhấn Ctrl + Shift + Enter.

Khi bạn nhấn Ctrl + Shift + Enter, Excel sẽ bao quanh công thức bằng dấu ngoặc nhọn ({}) và chèn một thể hiện của công thức trong mỗi ô của phạm vi đã chọn. Điều này xảy ra rất nhanh, vì vậy bạn sẽ 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 chọ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 sau đây, 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 (phạm vi ô C2 đến D11), rồi sử dụng hàm Sumđể thêm tổng cộng với nhau. Kết quả tổng cộng doanh số bán hàng là $1.590.000. Ví dụ này cho thấy kiểu công thức này có hiệu quả tới mức nà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.

Ngoà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 ô E2 qua E11). Đây là 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ông thức trong cột E hoặc xóa cột đó hoàn toàn, 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ụ, bấm vào ô E3, rồi nhấn Delete. Bạn phải chọn toàn bộ phạm vi ô (E2 đến E11) và thay đổi công thức cho toàn bộ mảng hoặc giữ nguyên mảng đó. Với tư cách là một biện pháp an toàn đã thêm, bạn phải nhấn Ctrl + Shift + Enter để xác nhận bất kỳ thay đổi nào đối với công thức.

  • Kích cỡ tệp nhỏ 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 phả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à khi sử dụng một công thức mảng, bạn hãy nhấn Ctrl + Shift + Enter để nhập công thức của bạn. Khi bạn làm điều này, Excel đặt công thức mảng của bạn trong một cặp dấu ngoặc nhọn — nếu bạn nhập dấu ngoặc nhọn theo cách thủ công, thì công thức của bạn sẽ được chuyển thành chuỗi văn bản và nó sẽ không hoạt động.

Hàm Array có thể là cách hiệu quả để xây dựng công thức phức tạp. Công thức mả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 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 ô, bạn cũng hãy nhớ rằng:

  • Hãy chọn phạm vi ô để chứa kết quả của bạn trước khi nhập công thức. Bạn đã làm điều này khi tạo công thức mảng đa ô, bằng cá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 sẽ hiển thị thông báo cho biết bạn không thể thay đổi một phần củ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 bạn không thể di chuyển hoặc xóa bỏ một phần của nó. Nói cách khác, để thu nhỏ một công thức mảng, trước tiên bạn phải xóa bỏ công thức hiện có và làm lại từ đầu.

  • Để xóa bỏ một công thức mảng, hãy chọn toàn bộ phạm vi công thức (ví dụ, E2: E11), rồi nhấn Delete.

  • Bạn không thể chèn các ô trống vào hoặc xóa ô khỏ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 phạm vi mảng hiện có, rồi tiếp tục cho đến khi bạn đã chọn toàn bộ phạm vi mà bạn muốn mở rộng công thức. Nhấn F2 để 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. Phím là chọn toàn bộ phạm vi, bắt đầu bằng ô phía trên cùng bên trái trong mảng. Ô trên cùng bên trái là một bản sửa đượ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 vài hạ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 dạn dà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 khá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. Trong thực tế, công thức mảng thường không được giải thích trong trang tính. Do đó, nếu người khác cần sửa đổi sổ làm việc của bạn, bạn nên tránh công thức mảng hoặc đảm bảo rằng những người đó biết về bất kỳ công thức mảng nào và tìm hiểu cách thay đổi chúng, nếu 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 sách này trong một cặp dấu ngoặc nhọn ({ }) theo cách thủ công, như thế này:

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

Bây giờ, bạn biết rằng bạn cần nhấn Ctrl + Shift + Enter khi bạn tạo công thức mảng. Vì hằng số mảng là thành phần của công thức mảng, bạn hãy đặt hằng số mảng trong cặp dấu ngoặc nhọn theo cách thủ công bằng cách gõ nhập các dấu ngoặc nhọn đó. Sau đó, bạn sử dụng Ctrl + Shift + Enter để nhập toàn bộ công thức.

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 hã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 các hàng bằng dấu chấm phẩy.

Dưới đây là một mảng trong một hàng đơn: {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 hành việc tạo các hằng số ngang, dọc và hai chiều.

Tạo hằng số ngang

  1. Trong một trang tính trống, hãy chọn các ô từ A1 đến E1.

  2. 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 nên nhập dấu ngoặc mở và đóng ({}) và Excel sẽ thêm tập thứ hai cho bạn.

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

    Hằng số mảng ngang trong công thức

Tạo hằng số dọc

  1. Trong sổ làm việc, hãy chọn một cột chứa năm ô.

  2. 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ị.

    Hằng số mảng dọc trong công thức mảng

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

  1. 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.

  2. 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:

    Hằng số mảng hai chiều trong công thức mảng

Dùng hằng số trong công thức

Đây là một ví dụ đơn giản có sử dụng hằng số:

  1. Trong sổ làm việc mẫu, hãy tạo một trang tính mới .

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

  3. 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 nhọn khác, vì bạn đã nhập nó ở dạng công thức mảng.

    Công thức mảng với hằng số 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 củ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 của công thức mảng có hằng số mảng

1. Hà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 nhọn; mà thực ra là bạn đã nhập các dấu ngoặc nhọn đó. Cũng nên nhớ rằng sau khi bạn thêm hằng số vào một công thức mảng, bạn hãy nhấn Ctrl + Shift + Enter để nhập công thức.

Vì 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 các giá trị trong mảng được lưu trữ với cá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ử việc này, sao chép hàm, hãy 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 vào hằng số mảng, bạn cần đặt văn bản trong dấu ngoặc kép (").

Hằng số mảng không được chứa thêm các mảng, công thức hoặc các hàm khá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 sẽ 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)}. Ngoà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 các hằng số mảng là đặt tên cho họ. 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 tạp trong công thức mảng để người khá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, bạn hãy làm như sau:

  1. Trên tab Công thức, trong nhóm Tên Đã Xác định, hãy bấm Xác định Tên.
    Hộp thoại xác định tên sẽ xuất hiện.

  2. Trong hộp Tên, hãy gõ Quarter1.

  3. Trong hộp Tham chiếu đến, bạn hãy nhập hằng số sau đây (hãy nhớ nhập dấu ngoặc nhọn theo cách thủ 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:

    Hộp thoại Sửa Tên với công thức

  4. Bấm OK, rồi chọn hàng gồm ba ô trống.

  5. Nhập công thức sau đây, rồi nhấn Ctrl + Shift + Enter.

    =Quarter1

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

    Mảng đã được đặt tên được nhập ở dạng công thức

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 cả 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:

  • Có 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 một dấu phẩy hoặc dấu chấm phẩy, hoặc nếu bạn đặt một chỗ sai, thì không thể tạo mảng không được tạo ra chính xác hoặc bạn có thể thấy thông báo cảnh báo.

  • Có thể bạn đã chọn phạ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 ô, thì giá trị lỗi #N/A sẽ xuất hiện trong ô trống. Ngược lại, nếu bạn chọn quá ít ô, thì Excel sẽ bỏ qua các giá trị không có ô tương ứng.

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ụ sử 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

  1. 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.

  2. 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}*2

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

  1. Hãy chọn một khối ô trống rộng bốn cột và cao ba hàng.

  2. 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 vị hàng một chiều

  1. Hãy chọn một cột gồm năm ô trống.

  2. 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 ngang, hàm TRANSPOSE sẽ chuyển đổi hằng số mảng thành cột.

Hoán vị cột một chiều

  1. Hãy chọn một hàng gồm năm ô trống.

  2. 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 sẽ chuyển đổi hằng số mảng thành hàng.

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

  1. Hãy chọn khối ô rộng ba cột và cao bốn hàng.

  2. Nhập hằng số sau đây, rồi nhấn Ctrl + Shift + Enter:

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

    Hàm TRANSPOSE sẽ 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 phạ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ừ các giá trị hiện có

  1. Trên một trang tính trong Excel, hãy chọn các ô C8:E10 và nhập công thức sau:

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

    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 90 vì bạn đang tạo ra một mảng các con số.

  2. Nhấn Ctrl + Shift + Enter, vốn sẽ nhập vào mảng số này trong phạm vi ô C8: E10 bằng cách dùng công thức mảng. Trên trang tính của bạn, C8 đến E10 phải trông giống như thế này:

    10

    31

    25

    40

    50

    60

    70

    80

    90

  3. Hãy chọn phạm vi ô từ C1 đến E3.

  4. 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 mảng các ô 3x3 xuất hiện trong các ô từ C1 đến E3 với cùng các giá trị mà bạn nhìn thấy trong C8 thông qua E10.

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

  1. Với các ô C1: C3 được chọn, nhấn F2 để chuyển sang chế độ chỉnh sửa. 

  2. Nhấn F9 để chuyển đổi tham chiếu ô thành giá trị. Excel chuyển đổi giá trị thành hằng số mảng. Giờ đây, công thức sẽ là = {10, 20, 30; 40, 50, 60; 70, 80, 90}.

  3. Nhấn Ctrl + Shift + Enter để nhập hằng số mảng dưới dạng công thức mảng.

Đếm số ký tự trong phạ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 phạm vi ô.

  1. Sao chép toàn bộ bảng này và dán vào trong một trang tính ở ô A1.

    Dữ liệu

    This is a

    bunch of cells that

    come together

    to form a

    single sentence.

    Tổng các ký tự trong A2:A6

    =SUM(LEN(A2:A6))

    Nội dung của ô dài nhất (A3)

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

  2. Chọn ô A8, rồi nhấn Ctrl + Shift + Enter để xem tổng số ký tự trong các ô A2: A6 (66).

  3. Chọn ô A10, rồi nhấn Ctrl + Shift + Enter để xem nội dung của từ dài nhất của các ô A2: A6 (ô A3).

Công thức sau đây được sử dụng trong ô A8 đế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 của từng chuỗi văn bản trong mỗi ô trong phạm vi đó. Hàm Sum sau đó cộng các giá trị này với nhau và hiển thị kết quả (66).

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 ô.

  1. Nhập một số số ngẫu nhiên trong các ô A1: A11.

  2. 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ề.

  3. 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 sử dụng hằng số mảng để đánh giá hàm SMALL ba lần và trả về giá trị nhỏ nhất (1), nhỏ nhất (2), và nhỏ nhất thứ ba (3) trong mảng có trong các ô A1: A10 để tìm thêm các giá trị, bạn thêm nhiều đối số vào mộ. 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 (A1: A10, {1, 2, 3})

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

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 ROWINDIRECT.

  1. Chọn các ô D1 qua D3.

  2. 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 ("1:3")))

Lúc này, hiểu biết một chút về hàm ROWINDIRECT 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 cột trống của 10 ô trong sổ làm việc thực hành 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 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 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 ké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 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.

Chúng ta hãy xem công thức mà bạn đã sử dụng trước đó — = Large (A5: A14, Row (gián tiếp ("1:3") ) — bắt đầu từ dấu ngoặc đơn bên trong và làm việc ra nước ngoài: hàm gián tiếp trả về một tập giá trị văn bản, trong trường hợp này, 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 ô A5:A14 và nó đượ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 ô đó. 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.

Như với ví dụ trước đó, bạn có thể sử dụng công thức này với các hàm khác, chẳng hạn như tổngTrung bình.

Tìm chuỗi văn bản dài nhất trong phạ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, rồi 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" 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 mỗi mục trong phạm vi ô A2: A6. Hàm Max tính toán 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, vốn nằm trong ô A3.

Đâ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ứukiể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 (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 là 1, hà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 là 0, hà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 là -1, hà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) xác định giá trị xuất phát 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 hoạt động khi bạn tìm cá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 là Data, trong đó 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 đó chứa các giá trị ban đầu nhưng loại trừ mọi giá trị 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 cá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 định trị là TRUE, trả về giá trị khác nếu nó định trị là 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 vì chúng định trị là TRUE, nó trả về các giá trị còn lại từ phạm vi (Data) vì chúng định trị là FALSE, có nghĩa là chúng không chứa giá trị lỗi. Sau đó, hàm SUM tính tổng cộng cho mảng đã 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, chỉ khác ở chỗ nó trả về số giá trị lỗi trong phạm vi có tên là Data thay vì lọc bỏ các giá trị lỗi đó:

=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 nà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 chỉ rõ đối số, hàm IF trả về FALSE nếu ô không chứa giá trị lỗi. Thậm chí 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 vì TRUE*1=1 và FALSE*1=0.

Tính tổng các giá trị theo các điều kiện

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

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

Hàm IF tạo ra mảng gồm các trị dương và giá trị false. Về cơ bản, hàm SUM bỏ qua các giá trị false vì 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 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 đó 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à các giá trị 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à các giá trị 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 ANDOR trực tiếp trong công thức mảng vì các hàm đó trả về một kết quả duy nhất, TRUE hoặc FALSE, cò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 các phép toán, chẳng hạn như phé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

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 các giá trị trong phạm vi đó. Công thức này dùng phạm vi dữ liệu có tên là Sales:

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

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

Đếm số lượng các khác biệt giữa hai phạm vi ô

Công thức mảng này so sánh các giá trị trong hai phạm vi ô có tên là MyData và YourData và trả về số lượng các khác biệt giữa hai phạm vi này. Nếu nội dung của hai phạm vi này giống hệt nhau, công thức trả về 0. Để dùng công thức này, các phạm vi ô cần có cùng kích cỡ và kích thước (ví dụ, nếu phạm vi ô MyData có 3 hàng 5 cột, thì phạ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 cá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 đó, 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 nà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 vì TRUE*1=1 và FALSE*1=0.

Tìm vị trí củ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 là 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 là Data. Nếu một ô tương ứng chứa giá trị tối đa trong phạm vi đó, thì mảng sẽ 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 phạm vi Data. Nếu phạm vi có tên là Data chứa nhiều giá trị tối đa giống hệt nhau, thì 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ế củ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

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

Bạn cần thêm trợ giúp?

Bạn luôn có thể nhờ chuyên gia trong Cộng đồng Kỹ thuật Excel, tìm sự hỗ trợ trongCộng đồng Giải pháp hoặc đề xuất tính năng hay cải tiến mới trên Excel User Voice.

Xem thêm

Các mảng động và hành vi mảng bị tràn

Công thức mảng động lực vs công thức mảng CSE kế thừa

Hàm FILTER

Hàm RANDARRAY

Hàm SEQUENCE

Hàm SORT

Hàm SORTBY

Hàm UNIQUE

Lỗi #SPILL! trong Excel

Toán tử giao nhau tiềm ẩn: @

Tổng quan về công thức

Lưu ý:  Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Cho chúng tôi biết thông tin này có hữu ích hay không? Dưới đây là bài viết bằng tiếng Anh để bạn tham khảo..​

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×