Mảng chính thì như thế nào so với mảng phụ

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 ô

• Ở đây, chúng ta tính toán Tổng Doanh thu của các loại xe coupes và sedan cho từng nhân viên bán hàng bằng cách nhập =F10:F19*G10:G19 ô H10.

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

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

  Trong ô H20 của sổ làm việc ví dụ, nhập hoặc sao chép và dán =SUM[F10:F19*G10: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 ô từ F10 đến hết G19], rồi dùng hàm SUM để cộng các tổng số 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 ô 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. Bạn cũng có thể thực hiện tốt để có các tổng độc lập như thế này, vì nó giúp xác thực độ chính xác của kết quả của bạn.

• Công thức mảng độ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ừ H10 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ụ, hãy bấm ô H11 và nhấn Delete. Excel sẽ không thay đổi kết quả của mảng. Để thay đổi, 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ụ bán xe 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ư =F10*G10, F11*G11, F12*G12, v.v. thì bạn phải dùng 11 công thức khác nhau để tính toán cùng kết quả đó. Đó không phải là vấn đề quan trọng nhưng nếu bạn có hàng nghìn hàng để tính tổng thì sao? Khi đó, nó có thể tạo ra sự 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. Công thức mảng =SUM[F10:F19*G10:G19] 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 một 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.

  • Lỗi #SPILL! Mảng động đã giới thiệu Lỗi #SPILL!, điều này cho biết phạm vi tràn dự kiến sẽ bị chặn vì một số lý do. Khi bạn giải quyết khối lượng, 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 1"\"tháng 2"\"tháng 3"}

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.

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ị các ví dụ bằng cách sử dụng hàm SEQUENCE để tự động tạo hằng số mảng, cũng như nhập thủ công các hằng số mả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 ô trống bất kỳ và nhập =SEQUENCE[1,5]. Hàm SEQUENCE xây dựng một hàng 1 cho 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ố dọc

  Chọn một ô trống bất kỳ có chỗ 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ố hai chiều

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

  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 vị trí đặt dấu chấm phẩy so với dấu phẩy.

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

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

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

Trong ô E11, hãy 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.

Hàm SEQUENCE xây dựng giá trị 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 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ị ô trong D9:H9 và toán tử 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ới nhau 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ế nó 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}]

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

• 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 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 ["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 để dùng hằng số mảng là đặt tên cho chúng. 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 đã xxác định > Xác định tên. Trong hộp Tên, hãy gõ 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 giống như thế này:

Bấm OK, rồi chọn hàng bất kỳ có ba ô trống và nhập =Quarter1.

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

Nếu muốn kết quả tràn theo chiều dọc thay vì theo chiều ngang, bạn có thể dùng =TRANSPOSE[Quarter1].

Nếu bạn muốn hiển thị danh sách 12 tháng, giống như bạn có thể sử dụng khi xây dựng báo cáo tài chính, bạn có thể tạo cơ sở cho một năm hiện tại bằng hàm SEQUENCE. Điểm khác biệt về hàm này là ngay cả khi chỉ hiển thị tháng, vẫn có ngày hợp lệ 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ụ này trên Hằng số mảng đã đặt tên và các trang tính Tập dữ liệu mẫu nhanh trong sổ làm việc ví dụ.

=TEXT[DATE[YEAR[TODAY[]],SEQUENCE[1,12],1],"mmm"]

Công thức này dùng hàm DATE để tạo ngày dựa trên năm hiện tại, SEQUENCE tạo hằng số mảng từ 1 đến 12 cho tháng 1 tới tháng 12, sau đó hàm TEXT chuyển đổi định dạng hiển thị thành "mmm" [tháng 1, tháng 2, tháng 3, 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 có thể sử dụng "mmmm".

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, như trong =Quarter1, chứ 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ể dùng kết hợp cả hàm, văn bản và số. Tất cả những việc này tùy thuộc vào mức độ sáng tạo mà bạn muố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ụ 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

  Nhập =SEQUENCE[1,12]*2hoặc ={1\2\3\4;5\6\7\8;9\10\11\12}*2

  Bạn cũng có thể chia bằng [/], thêm bằng [+] và trừ bằng [-].

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

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

• Tìm căn bậc hai của các mục bình phương 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 đổị 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 đổi 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 dùng công thức mảng để tạo mảng mới từ mảng hiện có.

  Nhập =SEQUENCE[3,6,10,10], hoặc ={10\20\30\40\50\60;70\80\90\100\110\120;130\140\150\160\170\180}

  Nhớ nhập { [dấu ngoặc nhọn mở] trước khi bạn nhập 10 và } [dấu ngoặc nhọn đóng] sau khi bạn nhập 180 vì bạn đang tạo ra một mảng các con số.

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

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

  Bạn có thể lấy kết quả của một công thức mảng bị tràn và chuyển đổi thành các phần của nó. Chọn ô D9, rồi nhấn F2 để chuyển sang chế độ sửa. Tiếp theo, nhấn F9 để đổi tham chiếu ô thành giá trị, Excel sau đó chuyển đổi thành hằng số mảng. Khi bạn nhấn Enter, công thức, =D9#, giờ sẽ là ={10\20\30;40\50\60;70\80\90}.

• Đếm số ký tự trong phạm vi ô

  Ví dụ sau đây cho bạn thấy cách đếm số ký tự trong một phạm vi ô. Bao gồm khoảng trắng.

  =SUM[LEN[C9:C13]]

  Trong trường hợp này, hàm LEN trả về độ dài của từng chuỗi văn bản trong mỗi ô trong phạm vi đó. Sau đó, hàm SUM cộng các giá trị đó với nhau và hiển thị kết quả [66]. Nếu bạn muốn có số ký tự trung bình, bạn có thể sử dụng:

  =AVERAGE[LEN[C9:C13]]

• Nội dung của ô dài nhất trong khoảng C9:C13

  =INDEX[C9:C13,MATCH[MAX[LEN[C9:C13]],LEN[C9:C13],0],1]

  Công thức này chỉ hoạt động khi phạm vi dữ liệu chứa một cột ô duy nhất.

  Chúng ta hãy xem xét kỹ hơn công thức này, bắt đầu tư các thành phần bên trong và hướng ra bên ngoài. Hàm LEN trả về độ dài của từng mục trong phạm vi ô D2:D6. Hàm MAX tính giá trị lớn nhất trong số các mục đó, mà giá trị đó tương ứng với chuỗi văn bản dài nhất, trong ô D3.

  Đây là nơi mọi thứ trở nên phức tạp một chút. Hàm MATCH tính toán khoảng chừa trống [vị trí tương đối] của ô chứa chuỗi văn bản dài nhất. Để làm điều đó, nó đòi hỏi ba đối số: giá trị tra cứu, mảng tra cứu và kiểu khớp. Hàm MATCH tìm kiếm giá trị tra cứu đã xác định trong mảng tra cứu. Trong trường hợp này, giá trị tra cứu là chuỗi văn bản dài nhất:

  MAX[LEN[C9:C13]

  và chuỗi đó nằm trong mảng này:

  LEN[C9:C13]

  Tham đối kiểu khớp trong trường hợp này là 0. Kiểu khớp có thể là giá trị 1, 0 hoặc -1.

  • 1 - trả về giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu

  • 0 - trả về giá trị đầu tiên chính xác bằng với giá trị tra cứu

  • -1 - trả về giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tra cứu đã xác định

  • Nếu bạn bỏ qua kiểu khớp, Excel giả định nó bằng 1.

  Cuối cùng, hàm INDEX lấy các đối số này: mảng và số hàng và cột trong mảng đó. Phạm vi ô C9:C13 cung cấp mảng, hàm MATCH cung cấp địa chỉ ô và đối số cuối cùng [1] chỉ rõ rằng giá trị đến từ cột đầu tiên trong mảng.

  Nếu bạn muốn tải nội dung của chuỗi văn bản nhỏ nhất, bạn sẽ thay thế hàm MAX trong ví dụ trên bằng hàm MIN.

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

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

  Nhập =SMALL[B9#;SEQUENCE[D9], =SMALL[B9:B18;{1;2;3}]

  Công thức này dùng hằng số mảng để đánh giá hàm SMALL ba lần và trả về 3 thành viên nhỏ nhất trong mảng chứa trong các ô B9:B18, trong đó 3 là giá trị biến số trong ô D9. Để tìm thêm các giá trị, bạn có thể tăng giá trị trong hàm SEQUENCE hoặc thêm nhiều đối số vào hằng số. Bạn cũng có thể dùng thêm các hàm khác trong công thức này, chẳng hạn như hàm SUM hay AVERAGE. Ví dụ:

  =SUM[SMALL[B9#,SEQUENCE[D9]]

  =AVERAGE[SMALL[B9#,SEQUENCE[D9]]

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

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

  Nhập =LARGE[B9#,ROW[INDIRECT["1:3"]]], or =LARGE[B9:B18,ROW[INDIRECT["1:3"]]]

  Lúc này, hiểu biết một chút về hàm ROW và INDIRECT có thể hữu ích. Bạn có thể dùng hàm ROW để tạo một mảng gồm các số nguyên liên tiếp. Ví dụ: chọn một mục trống, rồi nhập:

  =ROW[1:10]

  Công thức này tạo ra một cột gồm 10 số nguyên liên tiếp. Để xem vấn đề tiềm ẩn, hãy chèn một hàng bên trên phạm vi chứa công thức mảng [tức là, trên hàng 1]. Excel sẽ điều chỉnh tham chiếu hàng và công thức hiện sẽ tạo ra các số nguyên từ 2 đến 11. Để khắc phục vấn đề đó, bạn hãy thêm hàm INDIRECT vào công thức:

  =ROW[INDIRECT["1:10"]]

  Hàm INDIRECT dùng chuỗi văn bản làm đối số của nó [đó là lý do tại sao phạm vi 1:10 được đặt trong dấu ngoặc]. Excel không điều chỉnh giá trị văn bản khi bạn chèn hàng hay di chuyển công thức mảng theo cách khác. Do đó, hàm ROW sẽ luôn luôn tạo ra mảng số nguyên mà bạn muốn. Bạn có thể dễ dàng sử dụng SEQUENCE:

  =SEQUENCE[10]

  Chúng ta hãy kiểm tra công thức mà bạn dùng ở trước =LARGE[B9#,ROW[INDIRECT["1:3"]]] bắt đầu từ cặp dấu ngoặc đơn bên trong và tiến hành hướng ra ngoài: Hàm INDIRECT trả về một bộ các giá trị văn bản, trong trường hợp này là các giá trị từ 1 đến 3. Đến lượt mình, hàm ROW tạo ra mảng cột gồm ba ô. Hàm LARGE dùng các giá trị trong phạm vi ô B9:B18 và nó được đánh giá ba lần, một lần cho mỗi tham chiếu mà hàm ROW trả về. Nếu muốn tìm thêm giá trị, bạn hãy thêm một phạm vi ô lớn hơn vào hàm INDIRECT. Cuối cùng, như với các ví dụ hàm SMALL, bạn có thể dùng công thức này với các hàm khác, chẳng hạn như hàm SUM và AVERAGE.

• 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ư #VALUE! hoặc #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 giống 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.

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, đạ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ảng gồm các 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 các giá trị lớn hơn 0 AND nhỏ hơn 2500:

=SUM[[Sales>0]*[Sales0]+[Sales