Tổng python (phạm vi danh sách)

Mảng nhiều ô và một ô

Bài tập này chỉ cho bạn cách sử dụng các công thức mảng nhiều ô và một ô để tính một tập hợp các số liệu bán hàng. Tập hợp các bước đầu tiên sử dụng công thức nhiều ô để tính toán một tập hợp các tổng phụ. Bộ thứ hai sử dụng công thức một ô để tính tổng lớn

• Công thức mảng nhiều ô

• Ở đây, chúng tôi đang tính Tổng doanh số bán xe coupe và sedan cho mỗi nhân viên bán hàng bằng cách nhập =F10. F19*G10. G19 trong ô H10

  Khi nhấn Enter sẽ thấy kết quả tràn xuống ô H10. H19. Lưu ý rằng phạm vi tràn được đánh dấu bằng đườ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ể nhận thấy rằng các công thức trong ô H10. H19 chuyển sang màu xám. Chúng 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 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 mẫu, 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 sẽ nhân các giá trị trong mảng [phạm vi ô từ F10 đến G19], sau đó sử dụng hàm SUM để cộng các tổng lại với nhau. Kết quả là tổng doanh thu là $1.590.000

  Ví dụ này cho thấy loại công thức này có thể mạnh mẽ như thế nào. 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 toàn bộ dữ liệu đó 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 trong 1.000 hàng. Ngoài ra, 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 ô H10 đến H19]. Đây là một lợi thế khác của việc sử 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 ở cột H mà không ảnh hưởng đến công thức ở H20. Cũng có thể là một phương pháp hay để có các tổng số độc lập như thế này, vì nó 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 mang lại những lợi thế này

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

  • An toàn    Bạn không thể ghi đè lên một thành phần của công thức mảng nhiều ô. Ví dụ: nhấp vào ô H11 và nhấn Delete. Excel sẽ không thay đổi đầu ra mảng. Để thay đổi, bạn cần chọn ô trên cùng bên trái của mảng hoặc ô H10

  • Kích thước tệp nhỏ hơn    Bạn thường có thể sử dụng một công thức mảng thay vì nhiều công thức trung gian. Ví dụ: ví dụ về doanh số bán ô tô sử dụng một công thức mảng để tính kết quả trong cột E. Nếu bạn đã sử dụng các công thức tiêu chuẩn như =F10*G10, F11*G11, F12*G12, v.v. , bạn sẽ phải sử dụng 11 công thức khác nhau để tính toán các kết quả giống nhau. Đó không phải là vấn đề lớn, nhưng nếu bạn có tổng cộng hàng nghìn hàng thì sao?

  • Hiệu quả    Hàm mảng có thể là một cách hiệu quả để xây dựng các 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 bảng Excel, thì công thức mảng động của bạn sẽ tự động thay đổi kích thước khi bạn thêm hoặc xóa dữ liệu

  • #TRÀN. lỗi    Mảng động đã giới thiệu lỗi #SPILL. lỗi, cho biết phạm vi tràn dự kiến ​​bị chặn vì lý do nào đó. Khi bạn giải quyết tắc nghẽn, công thức sẽ tự động tràn ra

Tạo hằng mảng một và hai chiều

Hằng số mảng là một thành phần của công thức mảng. Bạn tạo các hằng số mảng bằng cách nhập danh sách các mục rồi bao quanh danh sách theo cách thủ công bằng dấu ngoặc nhọn [{ }], như thế này

={1,2,3,4,5} hoặc ={"Tháng Giêng","Tháng Hai","Tháng Ba"}

Nếu bạn phân tách các mục bằng dấu phẩy, bạn tạo một mảng ngang [một hàng]. Nếu bạ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 [một cột]. Để tạo một mảng hai chiều, bạn phân định các mục trong mỗi hàng bằng dấu phẩy và phân cách mỗi hàng bằng dấu chấm phẩy

Các quy trình sau đây sẽ cung cấp cho bạn một số thực hành trong 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ụ sử 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 thủ công

• Tạo một hằng số ngang

  Sử dụng sổ làm việc từ các ví dụ trước hoặc tạo 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 tạo mảng 1 hàng x 5 cột giống như ={1,2,3,4,5}. Kết quả sau đây được hiển thị

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

  Chọn bất kỳ ô trống nào có khoảng trống bên dưới 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 bất kỳ ô trống nào có khoảng trống ở bên phải và bên dưới ô đó, rồi nhập =SEQUENCE[3,4]. Bạn thấy kết quả sau

  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 mang lại 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. Về cơ bản, nó giúp bạn tiết kiệm thời gian, nhưng nó cũng có thể giúp giảm lỗi khi nhập thủ công. Nó cũng dễ đọc hơn, đặc biệt là vì dấu chấm phẩy có thể khó phân biệt với dấu phẩy phân cách

Cú pháp hằng mảng

Đây là một ví dụ sử 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 đi đến Hằng số trong trang tính công thức hoặc tạo 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 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 thứ gì đó khác với 1-5 để phân biệt

Trong ô E11, nhập =SUM[D9. H9*SEQUENCE[1,5]], hoặc =SUM[D9. H9*{1,2,3,4,5}]. Các công thức trả về 85

Hàm SEQUENCE xây dựng giá trị tương đương của 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 trước nên hai phần tử tiếp theo sẽ phát huy tác dụ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à trả về 85

Để tránh sử dụng mảng được lưu trữ và giữ hoạt động 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 phần tử mà bạn có thể sử dụng trong hằng số mảng

• Hằng số mảng có thể chứa số, văn bản, giá trị logic [chẳng hạn như TRUE và FALSE] và các giá trị lỗi như #N/A. Bạn có thể sử dụng các số ở đị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 đặt nó trong dấu ngoặc kép ["văn bản”]

• Hằng số mảng không thể chứa các mảng, công thức hoặc hàm bổ sung. Nói cách khác, chúng chỉ có thể chứa văn bản hoặc số được phân tách bằng dấu phẩy hoặc dấu chấm phẩy. Excel hiển thị thông báo cảnh báo khi bạn nhập công thức chẳng hạn như {1,2,A1. D4} hoặc {1,2,SUM[Q2. Z8]}. Ngoài ra, các giá trị số không được chứa dấu phần trăm, ký hiệu đô la, dấu phẩy hoặc dấu ngoặc đơn

Đặt tên cho hằng mảng

Một trong những cách tốt nhất để sử 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ể che giấu một số độ phức tạp của công thức mảng của bạn khỏi những người khác. Để đặt tên cho hằng số mảng và sử dụng nó trong công thức, hãy làm như sau

Chuyển đến Công thức> Tên được xác định> Xác định tên. Trong hộp Tên, nhập Quý1. Trong hộp Refers to, nhập hằng số sau [nhớ gõ dấu ngoặc bằng tay]

={"Tháng Giêng","Tháng Hai","Tháng Ba"}

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

Bấm OK, sau đó chọn bất kỳ hàng nào có ba ô trống và nhập =Quarter1

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

Nếu bạn muốn kết quả tràn ra theo chiều dọc thay vì theo chiều ngang, bạn có thể sử 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 lập báo cáo tài chính, bạn có thể căn cứ vào năm hiện tại bằng hàm SEQUENCE. Điều thú vị về chức năng này là mặc dù chỉ hiển thị tháng, nhưng có một ngày hợp lệ đằng sau nó mà bạn có thể sử dụng trong các tính toán khác. Bạn sẽ tìm thấy những ví dụ này trên trang tính tập dữ liệu mẫu nhanh và hằng số mảng được đặt tên trong sổ làm việc mẫu

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

Điều này sử dụng hàm DATE để tạo ngày dựa trên năm hiện tại, SEQUENCE tạo một hằng số mảng từ 1 đến 12 cho tháng 1 đến 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 đầy đủ của tháng, chẳng hạn như tháng 1, bạn sẽ sử dụng "mmmm"

Khi bạn sử 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, không chỉ Quarter1. Nếu không, Excel sẽ diễn giải mảng dưới dạng 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 đợi. Cuối cùng, hãy nhớ rằng bạn có thể sử dụng kết hợp các chức năng, văn bản và số. Tất cả phụ thuộc vào mức độ sáng tạo mà bạn muốn có được

Hằng số mảng đang hoạt động

Các ví dụ sau minh họa một số 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ụ sử dụng hàm TRANSPOSE để chuyển hàng thành cột và ngược lại

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

  Nhập =SEQUENCE[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 bằng [/], cộng bằng [+] và trừ bằng [-]

• Bình phương các mục trong một 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 phần tử 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 vị một hàng một chiều

  Nhập =TRANSPOSE[SEQUENCE[1,5]] hoặc =TRANSPOSE[{1,2,3,4,5}]

  Mặc dù bạn đã nhập một hằng số mảng ngang, nhưng hàm TRANSPOSE sẽ chuyển đổi hằng số mảng thành một 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}]

  Mặc dù bạn đã nhập một hằng số mảng dọc, nhưng hàm TRANSPOSE sẽ chuyển đổi hằng số đó thành một 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 chuyển đổi từng hàng thành một loạt cột

Đưa các công thức mảng cơ bản vào hoạt động

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

• Tạo một mảng từ các giá trị hiện có

  Ví dụ sau 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}

  Đảm bảo nhập { [dấu ngoặc mở] trước khi bạn nhập 10 và } [dấu ngoặc nhọn] 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 xuất hiện với các giá trị giống như bạn thấy trong D9. D11. Dấu # được gọi là toán tử phạm vi tràn và đó là cách tham chiếu toàn bộ phạm vi mảng của Excel thay vì phải nhập nó ra

• Tạo một 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 tràn ra và chuyển nó thành các phần cấu thành của nó. Chọn ô D9, sau đó nhấn F2 để chuyển sang chế độ chỉnh sửa. Tiếp theo, nhấn F9 để chuyển đổi tham chiếu ô thành giá trị, sau đó Excel sẽ chuyển đổi thành hằng số mảng. Khi bạn nhấn Enter, công thức =D9# bây giờ sẽ là ={10,20,30;40,50,60;70,80,90}

• Đếm các ký tự trong một dải ô

  Ví dụ sau đây cho bạn biết cách đếm số lượng ký tự trong một dải ô. Điều này 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 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ị đó lại với nhau và hiển thị kết quả [66]. Nếu bạn muốn lấy 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 phạm vi C9. C13

  =INDEX[C9. C13,TRẬN ĐẤU[TỐI ĐA[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 ô

  Hãy xem xét kỹ hơn công thức, bắt đầu từ các yếu tố 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 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, 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 độ lệch [vị trí tương đối] của ô chứa chuỗi văn bản dài nhất. Để làm điều đó, nó cần ba đối số. giá trị tra cứu, mảng tra cứu và loại đối sánh. Hàm MATCH tìm kiếm mảng tra cứu cho giá trị tra cứu đã chỉ định. Trong trường hợp này, giá trị tra cứu là chuỗi văn bản dài nhất

  TỐI ĐA[LEN[C9. C13]

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

  LEN[C9. C13]

  Đối số loại đối sánh trong trường hợp này là 0. Loại đối sánh 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 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 đã chỉ định

  • Nếu bạn bỏ qua một loại đối sánh, Excel sẽ giả định 1

  Cuối cùng, hàm INDEX nhận các đối số này. một mảng và một 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ỉ định rằng giá trị đến 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ế MAX trong ví dụ trên bằng MIN

• Tìm n giá trị nhỏ nhất trong một dãy

  Ví dụ này cho thấy cách tìm ba giá trị nhỏ nhất trong một dải ô, trong đó một mảng dữ liệu mẫu trong các ô B9. B18 đã được tạo với. =INT[RANDARRAY[10,1]*100]. Lưu ý rằng RANDARRAY là một hàm biến động, vì vậy, bạn sẽ nhận đượ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 sử dụng một hằng số mảng để đánh giá hàm NHỎ ba lần và trả về 3 phần tử nhỏ nhất trong mảng được chứa trong các ô B9. B18, trong đó 3 là một giá trị biến trong ô D9. Để tìm thêm giá trị, bạn có thể tăng giá trị trong hàm SEQUENCE hoặc thêm nhiều đối số hơn vào hằng số. Bạn cũng có thể sử dụng các hàm bổ sung với công thức này, chẳng hạn như SUM hoặc 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 dãy

  Để 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 bằng hàm LARGE. Ngoài ra, ví dụ sau sử dụng các hàm ROW và INDIRECT

  Nhập =LARGE[B9#,ROW[INDIRECT["1. 3"]]] hoặc =LARGE[B9. B18,ROW[gián tiếp["1. 3"]]]

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

  =ROW[1. 10]

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

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

  Hàm INDIRECT sử dụng các 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 hoặc di chuyển công thức mảng. Kết quả là hàm ROW luôn tạo ra mảng các số nguyên mà bạn muốn. Bạn có thể dễ dàng sử dụng SEQUENCE

  =SEQUENCE[10]

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

Xử lý lỗi

• Tính 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 cố tính tổng một phạm vi có giá trị lỗi, chẳng hạn như #VALUE. hoặc #N/A. Ví dụ này chỉ cho bạn cách tính tổng các giá trị trong một dải ô có tên là Dữ liệu chứa lỗi

• =SUM[IF[ISERROR[Dữ liệu],"",Dữ liệu]]

  Công thức tạo một mảng mới chứa các giá trị ban đầu trừ đi mọi giá trị lỗi. Bắt đầu từ các hàm bên trong và hoạt động bên ngoài, hàm ISERROR tìm kiếm lỗi trong phạm vi ô [Dữ liệu]. Hàm IF trả về một giá trị cụ thể nếu một điều kiện bạn chỉ định đánh giá là TRUE và một giá trị khác nếu nó đánh giá là FALSE. Trong trường hợp này, nó trả về các chuỗi rỗng [""] cho tất cả các giá trị lỗi vì chúng đánh giá là TRUE và nó trả về các giá trị còn lại từ dải ô [Dữ liệu] vì chúng đánh giá là FALSE, nghĩa là chúng không chứa giá trị lỗi. Hàm SUM sau đó tính tổng cho mảng đã lọc

• Đếm số lượng giá trị lỗi trong một 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 một dải ô có tên là Dữ liệu thay vì lọc chúng ra

  =SUM[IF[ISERROR[Dữ liệu],1,0]]

  Công thức này tạo một mảng chứa giá trị 1 cho các ô có lỗi và giá trị 0 cho các ô không có lỗi. Bạn có thể đơn giản hóa công thức và đạt được kết quả tương tự bằng cách loại bỏ đối số thứ ba cho hàm IF, như sau

  =SUM[IF[ISERROR[Dữ liệu],1]]

  Nếu bạn không chỉ định đối số, hàm IF sẽ trả về FALSE nếu một ô không chứa giá trị lỗi. Bạn có thể đơn giản hóa công thức hơn nữa

  =SUM[IF[ISERROR[Dữ liệu]*1]]

  Phiên bản này hoạt động vì TRUE*1=1 và FALSE*1=0

Tổng giá trị dựa trên điều kiện

Bạn có thể cần tính tổng các giá trị dựa trên 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 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[Doanh số>0,Doanh số]]

Hàm IF tạo một mảng các giá trị dương và sai. Hàm SUM về cơ bản bỏ qua các giá trị sai 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ố lượng 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 VÀ nhỏ hơn 2500

=SUM[[Doanh số>0]*[Doanh số0]+[Doanh số