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

Lưu ý: Chúng tôi muốn cung cấp cho bạn nội dung trợ giúp mới nhất ở chính ngôn ngữ của bạn, nhanh nhất có thể. 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. Vui lòng cho chúng tôi biết ở cuối trang này rằng thông tin có hữu ích với bạn không? Dưới đây là bài viết bằng Tiếng Anh để bạn tham khảo dễ hơn.

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ĩ của một mảng dưới dạng một hàng hoặc cột chứa giá trị, hoặc kết hợp các hàng và cột của các giá trị. Công thức mảng có thể trả về nhiều kết quả hoặc một kết quả duy nhất.

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

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

  • Nhanh chóng tạo bộ dữ liệu mẫu.

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

  • Tính tổng các số duy nhất đáp ứng đ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 số nằm giữa một ranh giới phía trên cùng và dưới.

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

Các ví dụ sau đây Hiển thị cho bạn cách tạo công thức mảng đa ô và đơn ô. Nếu có thể, chúng tôi đã bao gồm trong ví dụ với một số chức năng động mảng, cũng như các công thức mảng có sẵn được nhập vào dưới dạng mảng linh động và kế thừa.

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

Tải xuống sổ làm việc ví dụ với tất cả các mảng thức ví dụ 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 mảng đa ô trong ô H10 = F10:F19 * G10:G19 để tính toán số xe đã bán bằng đơn giá

  • Dưới đây, chúng tôi đang tính toán tổng doanh thu của Coupe và sedan cho từng nhân viên bán hàng bằng cách nhập = F19:F19 * G10:G19 trong ô H10.

    Khi bạn nhấn Enter, bạn sẽ thấy kết quả tràn xuống đến ô H10:H19. Lưu ý rằng phạm vi tràn được tô sáng với viền khi bạn chọn bất kỳ ô nào trong phạm vi tràn. Bạn cũng có thể nhận thấy rằng công thức trong ô H10:H19 bị mờ đi. Họ đang ở đó chỉ để tham khảo, 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, công thức cái nằm ở đâu.

  • Công thức mảng đơn ô

    Công thức mảng đơn ô để tính tổng với =SUM(F10:F19*G10:G19)

    Trong ô H20 của sổ làm việc mẫu, hãy nhập hoặc sao chép và dán =SUM(F10:F19*G10:G19), và sau đó 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), và sau đó sử dụng hàm SUM để thêm các tổng cùng nhau. Kết quả là tổng $1,590,000 doanh số.

    Ví dụ này hiển thị cách mạnh mẽ kiểu công thức này có thể. Ví dụ, giả sử bạn có 1.000 hàng dữ liệu. Bạn có thể tính tổng một phần hoặc tất cả các dữ liệu đó bằng cách tạo công thức mảng trong một ô thay vì kéo công thức thông qua các hàng 1.000. Ngoài ra, lưu ý rằng công thức đơn ô trong ô H20 hoàn toàn độc lập của công thức đa ô (công thức trong ô H10 đến H19). Đây là một lợi thế của việc sử dụng công thức mảng — linh hoạt. Bạn có thể thay đổi 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ể là nên có tổng độc lập như thế này, khi điều này sẽ giúp xác nhận độ chính xác của kết quả của bạn.

  • Công thức mảng động cũng có các ưu điểm:

    • Nhất quán    Nếu bạn bấm vào bất kỳ các ô từ H10 xuống, bạn thấy cùng một công thức. Nhất quán đó có thể giúp đảm bảo chính xác hơn.

    • An toàn    Bạn không thể ghi đè lên một cấu phần của một công thức mảng đa ô. Ví dụ, hãy bấm vào ô H11 và nhấn Delete. Excel sẽ không thay đổi của mảng đầu ra. Để thay đổi nó, bạn cần phải chọn ô phía 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 đơn thay vì một số công thức trung gian. Ví dụ, ví dụ xe hơi bán hàng sử dụng công thức mảng để tính toán kết quả trong cột E. Nếu bạn đã sử dụng công thức chuẩn chẳng hạn như = F10 * G10, F11 * G11, F12 * G12, v.v., bạn sẽ có sử dụng công thức khác nhau 11 để tính toán kết quả tương tự. Mà không phải là một việc lớn, nhưng nếu bạn đã có hàng ngàn hàng để tổng? Sau đó nó có thể thực hiện một điểm khác biệt lớn.

    • Hiệu quả    Hàm mảng có thể là một cách hiệu quả để xây dựng công thức phức tạp. Mảng thức =SUM(F10:F19*G10:G19) là giống như thế này: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Tràn    Công thức mảng động sẽ tự động tràn vào phạm vi đầu ra. Nếu dữ liệu nguồn của bạn nằm trong bảng Excel, rồi 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! lỗi    Động mảng giới thiệu các #SPILL! lỗi, mà chỉ ra phạm vi chủ định tràn bị chặn vì lý do. Khi bạn giải quyết 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 tách các mục bằng cách dùng dấu phẩy, bạn tạo ra mảng ngang (hàng). Nếu bạn phân tách các mục bằng cách sử dụng dấu chấm phẩy, bạn tạo một mảng dọc (cột). Để tạo một mảng hai chiều, bạn delimit các mục trong mỗi hàng bằng dấu phẩy và delimit mỗi hàng với dấu chấm phẩy.

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

  • Tạo hằng số ngang

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

    Tạo hằng số 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 với phòng bên dưới nó và nhập =SEQUENCE(5)hoặc = {1; 2 3; 4; 5}. Kết quả sau đây được hiển thị:

    Tạo hằng số 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 với phòng ở bên phải và bên dưới nó và nhập =SEQUENCE(3,4). Bạn hãy xem kết quả sau đây:

    Tạo một hàng 3 bằng hằng số 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ẽ cần chú ý đến nơi bạn đặt dấu chấm so với dấu phẩy.

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

Đây là một ví dụ về cách sử dụng mảng có hằng số 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, đi tới trang tính hằng số trong một công thức , hoặc tạo một trang tính mới.

Trong ô D9, chúng tôi đã nhập =SEQUENCE(1,5,3,1), nhưng bạn cũng có thể nhập 3, 4, 5, 6 và 7 trong ô A9:H9. Không đặc biệt về đó lựa chọn số cụ thể, chúng tôi chỉ đã chọn số không phải là 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 ta đã dùng = SUM (D9:H(*SEQUENCE(1,5))

Hàm trình tự dựng tương đương của mảng không đổi {1,2,3,4,5}. Vì Excel thực hiện các thao tác biểu thức được đặt trong dấu ngoặc đơn trước tiên, các thành phần tiếp theo hai đi vào chơi 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ữ theo các giá trị tương ứng trong hằng số. Đó là 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 dùng mảng được lưu trữ và giữ thao tác hoàn toàn trong bộ nhớ, bạn có thể thay thế bằng hằng số mảng khác:

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

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

  • Hằng số mảng có thể bao gồm 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ố trong định dạng số nguyên, số thập phân, và khoa học. Nếu bạn bao gồm văn bản, bạn cần phải bao quanh nó với 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ên chúng. Hằng số đã đặt tên có thể dễ dàng hơn để sử dụng, và họ có thể ẩn một số phức tạp của công thức mảng của bạn từ những người khác. Để đặt tên cho hằng số mảng và dùng nó trong một công thức, hãy làm như sau:

Đi đến công thức > đã xác định tên > xác định tên. Trong hộp tên , hãy gõ Quarter1. Trong hộp tham chiếu tới , 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ờ đây 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 và nhập = Quarter1.

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

Dùng hằng số mảng đã đặt tên trong công thức, như = Quarter1, nơi Quarter1 đã được xác định như = {"Tháng một", "Tháng hai", "Tháng ba"}

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

Nếu bạn muốn hiển thị danh sách 12 tháng, như bạn có thể dùng khi xây dựng một báo cáo tài chính, bạn có thể xây dựng một tắt năm hiện tại bằng hàm của trình tự. Điều gọn gàng về hàm này là ngay cả khi chỉ tháng Hiển thị, có là ngày hợp lệ nằm phía sau mà bạn có thể dùng trong phép tính khác. Bạn sẽ tìm thấy các ví dụ trên các trang tính có tên là hằng số mảng mẫu nhanh chóng tập dữ liệu trong sổ làm việc mẫu.

=Text(Date(year(Today()),SEQUENCE(1,12),1),"Mmm")

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

Điều này dùng hàm DATE để tạo ngày dựa trên năm hiện tại, trình tự tạo hằng số mảng từ 1 đến 12 cho tháng một đến tháng mười hai, sau đó hàm TEXT chuyển đổi định dạng hiển thị để "mmm" (Jan, ngày, Tháng ba, 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 sẽ sử dụng "mmmm".

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

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

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

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

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

  • 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,4 5,6,7,8; 9,10,11,12} ^ 2

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

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

  • Hoán 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 = hoán đổi ({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 = hoán đổi ({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 một mảng mới từ mộ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 một mảng số.

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

    Dùng toán tử phạm vi đổ (#) để 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ể lấy kết quả của công thức mảng đổ và chuyển mà thành phần cấu phần của nó. Chọn ô D9, sau đó nhấn F2 để chuyển sang chế độ soạn thảo. Tiếp theo, hãy nhấn F9 để chuyển đổi các tham chiếu ô đến giá trị, Excel rồi chuyển đổi thành một hằng số mảng. Khi bạn nhấn Enter, công thức = D9 #, giờ đây sẽ = {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ố ký tự trong một phạm vi ô. Bao gồm khoảng trắng.

    Đếm tổng số ký tự trong một phạm vi và 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ị với nhau và hiển thị kết quả (66). Nếu bạn muốn lấy trung bình số ký tự, 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 hoạt động chỉ khi một phạm vi dữ liệu chứa một cột đơn gồm các ô.

    Chúng ta hãy tìm hiểu kỹ hơn một công thức, bắt đầu từ các thành phần bên trong và làm việc ra 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 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, nằm ở ô D3.

    Đây là nơi điều nhận hơi phức tạp. Hàm MATCH tính toán giá trị bù trừ (vị trí tương đối) của ô có chứa chuỗi văn bản dài nhất. Để thực hiện điều này, nó yêu cầu ba đối số: một giá trị tra cứu, một mảng tra cứu và một kiểu khớp. Hàm MATCH tìm kiếm mảng tra cứu cho giá trị đã xác định 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ố kiểu khớp trong trường hợp này là 0. Kiểu khớp có thể là một 1, 0 hoặc giá trị-1.

    • 1 - trả về giá trị lớn nhất nhỏ hơn hoặc bằ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 có lớn hơn hoặc bằng giá trị đã xác định tra cứu

    • Nếu bạn bỏ qua một kiểu khớp, Excel giả định 1.

    Cuối cùng, hàm INDEX có các đối số: một mảng và số hàng và cột trong mảng đó. Phạm vi ô C9:C13 cung cấp các 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ác 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ế 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 mảng của dữ liệu mẫu trong ô B9:B18has được tạo ra 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ẽ nhận được một bộ số ngẫu nhiên mới mỗi khi Excel tính toán.

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

    Nhập =SMALL(B9#,SEQUENCE(D9), = nhỏ (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ề các thành viên 3 nhỏ nhất trong mảng trong ô B9:B18, nơi 3 là một giá trị biến trong ô D9. Để tìm giá trị khác, bạn có thể tăng giá trị trong hàm trình tự hoặc thêm nhiều đối số với hằng số. Bạn cũng có thể dùng chức năng bổ sung với công thức này, chẳng hạn như tổng hoặc Trung bình. 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 các giá trị lớn nhất trong một phạm vi, bạn có thể thay thế hàm SMALL có hàm LARGE. Ngoài ra, ví dụ sau đây sử dụng các hàm hànggián tiếp .

    Nhập = lớn (B9 #, hàng (INDIRECT ("1:3"))), hoặc = lớn (B9:B18,ROW(INDIRECT("1:3")))

    Tại thời điểm này, nó có thể giúp để biết một chút về các hàm hàng và gián tiếp. Bạn có thể sử dụng hàm ROW để tạo một mảng số nguyên lần liên tiếp. Ví dụ, chọn trắng và nhập:

    =ROW(1:10)

    Công thức tạo một cột số nguyên lần liên tiếp 10. Để xem vấn đề tiềm năng, chèn hàng phía 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 ngay bây giờ tạo số nguyên từ 2 đến 11. Để khắc phục vấn đề đó, bạn thêm hàm INDIRECT công thức:

    = HÀNG (INDIRECT ("1:10"))

    Hàm INDIRECT sử dụng chuỗi văn bản dưới dạng các đối số (đây 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 hoặc nếu không di chuyển công thức mảng. Kết quả, hàm ROW luôn tạo ra mảng của số nguyên mà bạn muốn. Bạn có thể dễ dàng sử dụng trình tự:

    =SEQUENCE(10)

    Hãy cùng kiểm tra công thức mà bạn đã sử dụng phiên bản cũ hơn — lớn (B9 #, hàng (hàm INDIRECT ("1:3"))) = — bắt đầu từ bên trong dấu ngoặc đơn và làm việc ra ngoài: hàm The INDIRECT trả về tập giá trị văn bản, trong trường hợp này các giá trị 1 đến 3. Hàm ROW nói tạo ra một mảng ô ba cột. Hàm LARGE sử 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 trả về bằng hàm ROW. Nếu bạn muốn tìm giá trị khác, bạn thêm một phạm vi ô lớn đến hàm INDIRECT. Cuối cùng, giố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ư hàm SUM và AVERAGE.

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

    Hàm SUM trong Excel không hoạt động khi bạn tìm cách để tính tổng một phạm vi có chứa 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:

    Sử dụng mảng để xử lý lỗi. Ví dụ, =SUM(IF(ISERROR(Data),"",Data) sẽ tính tổng các phạm vi có tên là dữ liệu ngay cả khi nó bao gồm 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 là giống như công thức trước đó, nhưng nó trả về số lượng giá trị lỗi trong một phạm vi có tên là dữ liệu thay vì lọc chúng ra ngoà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.

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

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

Ví dụ, công thức mảng này tính tổng chỉ là các số nguyên dương trong một phạm vi có tên là doanh số, biểu thị ô E9:E24 trong ví dụ ở trên:

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

Hàm IF tạo ra mảng của giá trị dương và false. Hàm SUM thiết phải bỏ qua các giá trị false vì 0 + 0 = 0. Phạm vi ô mà bạn sử dụng trong công thức này có thể bao gồm bất kỳ số hàng và cột.

Bạn cũng có thể tính tổng các giá trị đáp ứng nhiều hơn một đ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((Sales>0)*(Sales<2500)*(Sales))

Hãy nhớ rằng công thức này trả về lỗi nếu phạm vi đó 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 sử dụng một kiểu OR điều kiện. 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)+(Sales<2500),Sales))

Bạn không thể sử dụng AND và OR hoạt động trong công thức mảng trực tiếp vì những hàm này trả về một kết quả đơn lẻ, hoặc TRUE hoặc FALSE, và các hàm mảng yêu cầu mảng kết quả. Bạn có thể tránh vấn đề bằng cách dùng lô-gic Hiển thị trong công thức trước đó. Nói cách khác, bạn thực hiện phép toán học, như cộng hay nhân trên các giá trị đáp ứng OR 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ố của sự khác biệt giữa hai. Nếu nội dung của các phạm vi hai giống 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 thước và của cùng một chiều. Ví dụ, nếu MyData là một phạm vi 3 hàng 5 cột, YourData cũng phải 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.

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 tương tự như ví dụ trong sổ làm việc mẫu trên trang tính khác biệt giữa các bộ 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 dưới đây và dán nó vào ô A1 trong một trang tính trống.

Doanh số Người

Xe hơi Kiểu

Số Đã bán

Đơn vị Giá

Tổ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 thu của Coupe và sedan cho từng nhân viên bán hàng, 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 cộng tất cả doanh thu, hãy chọn ô F11, nhập công thức =SUM(C2:C11*D2:D11)và sau đó nhấn Ctrl + Shift + Enter.

Khi bạn nhấn Ctrl + Shift + Enter, Excel chứa công thức với dấu ngoặc nhọn ({}) và chèn công thức mẫu trong mỗi ô của phạm vi đã chọn. Điều này xảy ra rất nhanh chóng để những gì bạn nhìn thấy trong cột E là tổng doanh số cho mỗi loại xe hơi cho mỗi nhân viên bán hàng. Nếu bạn chọn E2, sau đó chọn E3, E4, v.v., bạn sẽ 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, và sau đó 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) và sau đó sử dụng hàm SUMđể thêm các tổng cùng nhau. Kết quả là tổng $1,590,000 doanh số. Ví dụ này hiển thị cách mạnh mẽ kiểu công thức này có thể. Ví dụ, giả sử bạn có 1.000 hàng dữ liệu. Bạn có thể tính tổng một phần hoặc tất cả các dữ liệu đó bằng cách tạo công thức mảng trong một ô thay vì kéo công thức thông qua các hàng 1.000.

Ngoài ra, lưu ý rằng công thức đơn ô trong ô D13 hoàn toàn độc lập của công thức đa ô (công thức trong ô E2 đến E11). Đây là một lợi thế của việc sử dụng công thức mảng — linh hoạt. Bạn có thể thay đổi công thức trong cột E hoặc xóa bỏ 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:

  • 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 đè lên một cấu phần của một công thức mảng đa ô. Ví dụ, hãy bấm vào ô E3 và nhấn xóa bỏ. Bạn có thể chọn toàn bộ phạm vi ô (E2 qua E11) và thay đổi công thức cho toàn bộ mảng, hoặc rời khỏi mảng là. Dưới dạng một thước đo được thêm an toàn, bạn phải nhấn Ctrl + Shift + Enter để xác nhận bất kỳ thay đổ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ả đó.

Nói chung, công thức mảng dùng cú pháp công thức chuẩn. Tất cả bắt đầu với dấu bằng (=) và bạn có thể sử dụng hầu hết các hàm Excel dựng sẵn trong công thức mảng. Sự khác biệt chính là khi dùng công thức mảng, bạn nhấn Ctrl + Shift + Enter để nhập công thức của bạn. Khi bạn thực hiện điều này, Excel đặt công thức mảng của bạn với dấu ngoặc nhọn — nếu bạn nhập dấu ngoặc nhọn theo cách thủ công, công thức của bạn sẽ được chuyển thành một chuỗi văn bản, và nó sẽ không hoạt động.

Hàm mảng có thể là một cách hiệu quả để xây dựng công thức phức tạp. Mảng thức =SUM(C2:C11*D2:D11) là giống như thế nà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 phải nhập một công thức mảng. Điều này áp dụng cho 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ỏ công thức mảng, hãy chọn toàn bộ phạm vi công thức (ví dụ, E2: E11), sau đó nhấn xóa bỏ.

  • Bạn không thể chèn thêm ô trống hoặc xóa bỏ ô khỏi một 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ó, và 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, sau đó 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. Chìa khóa là để chọn toàn bộ phạm vi, bắt đầu từ ô phía trên cùng bên trái trong mảng. Ô phía trên cùng bên trái là đã được 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ế:

  • Bạn có thể quên thỉnh thoảng nhấn Ctrl + Shift + Enter. Nó có thể xảy ra với thậm chí cả những người dùng giàu kinh nghiệm nhất của Excel. Hãy nhớ nhấn tổ hợp phím này bất cứ khi nào bạn nhập hoặc sửa công thức mảng.

  • Người dùng khác của sổ làm việc của bạn không có thể tìm hiểu về công thức của bạn. Trong thực tế, công thức mảng được nói chung không được giải thích trong trang tính. Do đó, nếu những người khác cần sửa đổi sổ làm việc của bạn, bạn sẽ tránh công thức mảng hoặc đảm bảo rằng mọi người biết về bất kỳ công thức mảng và tìm hiểu về 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 phải nhấn Ctrl + Shift + Enter khi bạn tạo công thức mảng. Vì hằng số mảng là một cấu phần của công thức mảng, bạn bao quanh hằng số với dấu ngoặc nhọn theo cách thủ công nhập chúng. Bạn 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.

Đây là một mảng trong một hàng: {1,2,3,4}. Đây là một mảng trong một cột duy nhất: {1; 2 3; 4}. Và đây là một mảng hai hàng và cột bốn: {1,2,3,4; 5,6,7,8}. Trong hàng hai mảng, hàng đầu tiên là 1, 2, 3 và 4, và hàng thứ hai là 5, 6, 7 và 8. Dấu chấm phẩy đơn phân tách các hàng hai 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 ô từ A1 đến E1.

  2. Trong thanh công thức, nhập công thức sau đây, và sau đó nhấn Ctrl + Shift + Enter:

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

    Trong trường hợp này, bạn sẽ nhập mở và dấu ngoặc nhọn đóng ({}) và Excel sẽ thêm bộ 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, nhập công thức sau đây, và sau đó 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, nhập công thức sau đây, và sau đó 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, nhập công thức sau đây, sau đó 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 bên trong dấu ngoặc đơn là hằng số mảng: {1,2,3,4,5}. Hãy nhớ rằng Excel không bao quanh hằng số mảng với dấu ngoặc nhọn; bạn thực sự nhập chúng. Cũng hãy nhớ rằng sau khi bạn thêm một hằng số vào công thức mảng, bạn 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})

Hãy thử cách này, sao chép hàm, chọn một ô trống trong sổ làm việc của bạn, dán công thức vào thanh công thức, và sau đó nhấn Ctrl + Shift + Enter. Bạn sẽ thấy kết quả giống như bạn đã thực hiện trong bài tập trước sử 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 cách tốt nhất để sử dụng hằng số mảng là tên chúng. Hằng số đã đặt tên có thể dễ dàng hơn để sử dụng, và họ có thể ẩn một số phức tạp của công thức mảng của bạn từ những người khác. Để đặt tên cho hằng số mảng và dùng nó trong một công thức, 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 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, sau đó 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:

  • Một số yếu tố có thể không được phân tách bằng ký tự viết. 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 trí sai, hằng số mảng có thể không được tạo ra đúng cách, hoặc bạn có thể thấy một 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 minh họa một vài cách mà bạn có thể đặt hằng số mảng để sử dụng trong công thức mảng. Một số ví dụ dùng hàm TRANSPOSE để chuyển đổi hàng thành cột và ngược lại.

Nhân từng mục trong mảng

  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, sau đó 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, sau đó 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, sau đó 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, sau đó 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, sau đó 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 này mảng của số trong phạm vi ô C8: E10 bằng cách sử dụng công thức mảng. Trên trang tính của bạn, C8 đến E10 sẽ trông như thế này:

    10

    20

    30

    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 trong thanh công thức, sau đó nhấn Ctrl + Shift + Enter:

    =C8:E10

    Một mảng các ô 3 x 3 sẽ xuất hiện trong ô C1 đến E3 với các giá trị cùng bạn nhìn thấy ở C8 đến E10.

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

  1. Với ô C1:C3 được chọn, nhấn F2 để chuyển sang chế độ soạn thảo.

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

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

Đếm số ký tự trong 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, sau đó nhấn Ctrl + Shift + Enter để xem tổng số ký tự trong ô A2: A6 (66).

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

Công thức sau đây được dùng trong ô A8 đếm tổng số ký tự (66) trong các ô từ A2 tới A6.

=SUM(LEN(A2:A6))

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ị 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ố ngẫu nhiên số trong ô A1:A11.

  2. Chọn các ô từ C1 đến C3. Này đặt ô sẽ giữ các kết quả trả về bởi công thức mảng.

  3. Nhập công thức sau đây, sau đó 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ề nhỏ nhất (1), nhỏ nhất thứ hai (2), và nhỏ nhất thứ ba (3) thành viên trong mảng trong ô A1:A10 để tìm giá trị khác, bạn thêm nhiều đối số cho các hằng số. Bạn cũng có thể dùng chức năng bổ sung với công thức này, chẳng hạn như tổng hoặc Trung bình. Ví dụ:

= SUM (NHỎ (A1:A10, {1,2,3})

= AVERAGE (NHỎ (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 đến D3.

  2. Trong thanh công thức, nhập công thức này, và sau đó nhấn Ctrl + Shift + Enter:

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

Tại thời điểm này, nó có thể giúp để biết một chút về các hàng và các hàm INDIRECT . Bạn có thể sử dụng hàm ROW để tạo một mảng số nguyên lần liên tiếp. Ví dụ, chọn một cột trống 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, và sau đó 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.

Hãy cùng xem những công thức mà bạn đã sử dụng phiên bản cũ hơn — = lớn (A5:A14,ROW(INDIRECT("1:3"))) — bắt đầu từ bên trong dấu ngoặc đơn và làm việc ra ngoài: hàm INDIRECT trả về tập giá trị văn bản, trong trường hợp này các giá trị 1 đến 3. Hàm ROW nói tạo ra một mảng cột ba ô. Hàm LARGE sử 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 trả về bằng hàm ROW . Các giá trị 3200, 2700 và 2000 được trả về mảng cột ba ô. Nếu bạn muốn tìm giá trị khác, bạn thêm một phạm vi ô lớn đến hàm INDIRECT .

Dưới dạng 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 trở về trước văn bản chuỗi ví dụ, nhập công thức sau đây trong một ô trống và nhấn Ctrl + Shift + Enter:

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

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

Chúng ta hãy tìm hiểu kỹ hơn một công thức, bắt đầu từ các thành phần bên trong và làm việc ra 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 giá trị lớn nhất trong số các mục đó, tương ứng với chuỗi văn bản dài nhất trong ô A3.

Đây 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 có các đối số: một mảng và số hàng và cột trong mảng đó. Phạm vi ô A2: A6 cung cấp các 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ác 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 nhận

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 người dùng viết bằng Colin Wilcox và thích từ các chương 14 và 15 Excel 2002 công thức, một sổ viết bằng John Walkenbach, một MVP Excel cũ.

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 so với công thức mảng CSE kế thừa

Hàm FILTER

Hàm RANDARRAY

Hàm SEQUENCE

Hàm SINGLE

Hàm SORT

Hàm SORTBY

Hàm UNIQUE

Lỗi #SPILL! trong Excel

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

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.

×