Thực hành công thức google sheet

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

• Chuyể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 trố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ố<2500)*(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 là số

Bạn cũng có thể tạo công thức mảng sử dụng một loại đ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((Doanh số>0)+(Doanh số<2500),Doanh số))

Bạn không thể sử dụng trực tiếp các hàm AND và OR 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 và các hàm mảng yêu cầu các mảng kết quả. Bạn có thể giải quyết vấn đề bằng cách sử dụng logic được hiển thị trong công thức trước đó. Nói cách khác, bạn thực hiện các phép toán, chẳng hạn như cộng hoặc nhân trên các giá trị đáp ứng điều kiện OR hoặc AND

Ví dụ này chỉ cho bạn cách xóa các số 0 khỏi một 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 sử dụng một phạm vi dữ liệu có tên là Bán hàng

=AVERAGE(IF(Doanh số<>0,Doanh số))

Hàm IF tạo một mảng các giá trị không bằng 0 rồi chuyển các giá trị đó cho hàm AVERAGE

Đếm số lượng khác biệt giữa hai dãy ô

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, đồng thời trả về số điểm khác biệt giữa hai phạm vi đó. Nếu nội dung của hai phạm vi giống hệt nhau, công thức trả về 0. Để sử dụng công thức này, các phạm vi ô cần phải có cùng kích thước và cùng thứ nguyên. Ví dụ: nếu MyData là phạm vi 3 hàng x 5 cột thì YourData cũng phải là 3 hàng x 5 cột

=SUM(IF(MyData=YourData,0,1))

Công thức tạo một mảng mới có cùng kích thước với các phạm vi mà bạn đang so sánh. Hàm IF điền vào mảng với giá trị 0 và giá trị 1 (0 cho các ô không khớp và 1 cho các ô giống hệt nhau). Hàm SUM sau đó trả về tổng của các giá trị trong mảng

Bạn có thể đơn giản hóa công thức như thế này

=SUM(1*(Dữ liệu của tôi<>Dữ liệu của bạn))

Giống như công thức đếm các giá trị lỗi trong một 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ị lớn nhất trong phạm vi một cột có tên là Dữ liệu

=MIN(IF(Dữ liệu=MAX(Dữ liệu),ROW(Dữ liệu),""))

Hàm IF tạo một mảng mới tương ứng với phạm vi có tên là Dữ liệu. Nếu một ô tương ứng chứa giá trị lớn nhất trong phạm vi, thì mảng chứa số hàng. Mặt khác, mảng chứa một chuỗi rỗng (""). Hàm MIN sử dụng mảng mới làm đối số thứ hai của nó và trả về giá trị nhỏ nhất, tương ứng với số hàng của giá trị lớn nhất trong Dữ liệu. Nếu phạm vi có tên là Dữ liệu chứa các giá trị tối đa giống hệt nhau, thì công thức sẽ trả về hàng của giá trị đầu tiên

Nếu bạn muốn trả về địa chỉ ô thực tế có giá trị lớn nhất, hãy sử dụng công thức này

=ADDRESS(MIN(IF(Dữ liệu=MAX(Dữ liệu),ROW(Dữ liệu),"")),COLUMN(Dữ liệu))

Bạn sẽ tìm thấy các ví dụ tương tự trong sổ làm việc mẫu trên trang tính Sự khác biệt giữa các bộ dữ liệu

Công thức 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 ô

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

Nhân viên bán hàng

Loại xe ô tô

Số  Đã bán

Đơn vị Giá

Tổng doanh số bán hàng

Barnhill

xe mui trần

5

33000

xe mui trần

4

37000

nghiêng

xe mui trần

6

24000

xe mui trần

8

21000

Jordan

xe mui trần

3

29000

xe mui trần

1

31000

Pica

xe mui trần

9

24000

xe mui trần

5

37000

Sánchez

xe mui trần

6

33000

xe mui trần

8

31000

Công thức (Tổng cộng)

Tổng cộng

'=TỔNG (C2. C11*D2. D11)

=SUM(C2. C11*D2. D11)

1. Để xem Tổng Doanh số bán xe coupe và sedan của từng nhân viên bán hàng, hãy chọn các ô 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 của tất cả doanh số, 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 phiên bản của công thức vào mỗi ô của phạm vi đã chọn. Điều này xảy ra rất nhanh, vì vậy những gì bạn thấy trong cột E là tổng số tiền bán được cho từng loại xe của 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}.  

• 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, rồi nhấn Ctrl+Shift+Enter

=SUM(C2. C11*D2. D11)

Trong trường hợp này, Excel sẽ nhân các giá trị trong mảng (phạm vi ô từ C2 đến D11) rồi 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, hãy lưu ý rằng công thức một ô trong ô D13 hoàn toàn độc lập với công thức nhiều ô (công thức trong các ô từ E2 đến E11). Đâ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ông thức ở cột E hoặc xóa hẳn cột đó mà không ảnh hưởng đến công thức ở D13

Công thức mả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ừ E2 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 ô E3 và 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 để nguyên mảng đó. Như một biện pháp an toàn bổ sung, 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 thước tệp nhỏ hơn    Bạn thường có thể sử 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 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 (chẳng hạn như =C2*D2, C3*D3, C4*D4…), bạn sẽ phải sử dụng 11 công thức khác nhau để tính toán cùng một kết quả

Cú pháp công thức mảng

Nói chung, công thức mảng sử dụng cú pháp công thức chuẩn. Tất cả chúng đều bắt đầu bằng dấu bằng (=) và bạn có thể sử dụng hầu hết các hàm Excel tích hợp sẵn trong công thức mảng của mình. Điểm khác biệt chính là khi sử dụng công thức mảng, bạn nhấn Ctrl+Shift+Enter để nhập công thức của mình. Khi bạn thực hiện điều này, Excel sẽ bao quanh công thức mảng của bạn bằng 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 đổi thành chuỗi văn bản và công thức sẽ không hoạt động

Các 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(C2. C11*D2. D11) 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)

Nhập và thay đổi công thức mảng

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 bạn làm việc với các công thức nhiều ô, hãy nhớ

• Chọn phạm vi ô để giữ kết quả của bạn trước khi bạn nhập công thức. Bạn đã làm điều này khi tạo công thức mảng nhiều ô khi bạn chọn các ô từ E2 đến E11

• Bạn không thể thay đổi nội dung của một ô riêng 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 hiển thị thông báo cho bạn biết rằng 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 toàn bộ công thức mảng nhưng không thể di chuyển hoặc xóa một phần của nó. Nói cách khác, để thu nhỏ công thức mảng, trước tiên bạn xóa công thức hiện có rồi bắt đầu lại

• Để xóa 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), sau đó nhấn Delete

• Bạn không thể chèn ô trống vào hoặc xóa ô khỏi công thức mảng nhiều ô

Mở rộng công thức mảng

Đôi khi, bạn có thể cần mở rộng một 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 thành. Nhấn F2 để chỉnh 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. Điều quan trọng là chọn toàn bộ phạm vi, bắt đầu với ô trên cùng bên trái trong mảng. Ô trên cùng bên trái là ô được chỉnh sửa

Nhược điểm của việc sử dụng công thức mảng

Công thức mảng rất tuyệt, nhưng chúng có thể có một số nhược điểm

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

• Những người dùng khác trong 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 dùng 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à hiểu cách thay đổi chúng, nếu họ 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

Tìm hiểu về hằng mảng

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}

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

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 cách sử dụng dấu phẩy và phân định từng 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. {1;2;3;4}. Và đây là một 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 đầu tiên 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 đơn ngăn cách hai hàng, giữa 4 và 5

Cũng như công thức mảng, bạn có thể sử dụng hằng mảng với hầu hết các hàm có sẵn mà Excel cung cấp. Các phần sau đây giải thích cách tạo từng loại hằng số và cách sử dụng các hằng số này với các hàm trong Excel

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

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

Tạo một 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, nhập công thức sau, rồi nhấn Ctrl+Shift+Enter

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

   Trong trường hợp này, bạn nên gõ dấu ngoặc mở và đóng ({ }) và Excel sẽ thêm bộ thứ hai cho bạn

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

   

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

1. Trong sổ làm việc của bạn, hãy chọn một cột gồm năm ô

2. Trong thanh công thức, nhập công thức sau, rồi nhấn Ctrl+Shift+Enter

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

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

   

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 ô rộng bốn cột, cao ba hàng

2. Trong thanh công thức, nhập công thức sau, rồi nhấn Ctrl+Shift+Enter

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

   Bạn thấy kết quả sau

   

Sử dụng hằng số trong công thức

Đây là một ví dụ đơn giản 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, nhập 3, rồi nhập 4 trong B1, 5 trong C1, 6 trong D1 và 7 trong E1

3. Trong ô A3, nhập công thức sau, rồi nhấn Ctrl+Shift+Enter

   =SUM(A1. E1*{1,2,3,4,5})

   Lưu ý rằng Excel bao quanh hằng số bằng một bộ dấu ngoặc nhọn khác vì bạn đã nhập hằng số đó dưới dạng công thức 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ú pháp hằng mảng

Công thức bạn vừa sử dụng chứa một số phần

1. Hàm số

2. Mảng được lưu trữ

3. Nhà điều hành

4. Hằng số mảng

Phần tử cuối cùng bên trong cặp ngoặc đơn là hằng số mảng. {1,2,3,4,5}. Hãy nhớ rằng Excel không bao quanh các hằng số mảng bằng dấu ngoặc nhọn; . Cũng nên nhớ rằng sau khi bạn thêm 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 các biểu thức nằm 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ị đượ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à tổng 85 xuất hiện trong ô A3

Để tránh sử dụng mảng được lưu trữ và chỉ giữ toàn bộ hoạt động 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ử điều này, hã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, rồi nhấn Ctrl+Shift+Enter. Bạn sẽ thấy kết quả tương tự như bạn đã làm trong bài tập trước đó sử dụng công thức mảng

=SUM(A1. E1*{1,2,3,4,5})

Các yếu tố mà bạn có thể sử dụng trong hằng số

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à giá trị lỗi (chẳng hạn 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 bao quanh văn bản bằng dấu ngoặc kép (")

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

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

2. Trong hộp Tên, nhập Quý1

3. 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"}

   Nội dung của hộp thoại bây giờ trông như thế này

   

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

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

   =Quý1

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

   

Khi bạn sử dụng 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 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 văn bản và số

Khắc phục sự cố hằng số mảng

Tìm kiếm các sự cố sau 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ự thích hợp. Nếu bạn bỏ qua dấu phẩy hoặc dấu chấm phẩy hoặc nếu bạn đặt sai vị trí, thì hằng số mảng có thể không được tạo 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 một dải ô không khớp với số lượng phần tử trong hằng số của mình. Ví dụ: nếu bạn chọn một cột gồm sáu ô để sử dụng với hằng số năm ô, 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 những giá trị không có ô tương ứng

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

1. Tạo một trang tính mới, rồi chọn một khối các ô trống rộng bốn cột và cao ba hàng

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

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

Bình phương các mục trong một mảng

1. Chọn một khối các ô trống rộng bốn cột, cao ba hàng

2. Nhập công thức mảng sau, 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,

   Ngoài ra, hãy nhập công thức mảng này, sử dụng toán tử dấu mũ (^)

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

Hoán vị một hàng một chiều

1. Chọn một cột gồm năm ô trống

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

   =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

Chuyển vị cột một chiều

1. Chọn một hàng gồm năm ô trống

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

   =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

1. Chọn một khối ô rộng ba cột, cao bốn hàng

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

   =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ảng và hằng 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 liên kết giữa các phạm vi ô trong các trang tính khác nhau. Nó cũng chỉ cho bạn cách tạo một hằng số mảng từ cùng một tập giá trị

Tạo một 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 này

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

   Đả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 90, vì bạn đang tạo một mảng số

2. Nhấn Ctrl+Shift+Enter, thao tác này sẽ nhập dãy số này vào 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. Chọn phạm vi ô từ C1 đến E3

4. Nhập công thức sau vào thanh công thức, rồi nhấn Ctrl+Shift+Enter

   =C8. E10

   Một mảng ô 3x3 xuất hiện trong các ô từ C1 đến E3 với các giá trị giống như bạn thấy trong C8 đến E10

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

1. Với các ô C1. Đã chọn C3, 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 các giá trị thành hằng số mảng. Công thức bây giờ 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 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ự, kể cả khoảng trắng, trong một dải ô

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

   Dữ liệu

   Đây là một

   bó tế bào mà

   đến với nhau

   để tạo thành một

   câu đơn

   Tổng số ký tự trong A2. A6

   =SUM(LEN(A2. A6))

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

   =INDEX(A2. A6,TRẬN ĐẤU(TỐI ĐA(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 ô A2. A6 (66)

3. Chọn ô A10, rồi nhấn Ctrl+Shift+Enter để xem nội dung của ô dài nhất 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 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)

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 ô

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

2. Chọn các ô từ C1 đến C3. Tập hợp các ô này sẽ chứa kết quả trả về của công thức mảng

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

   =NHỎ(A1. A11,{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ề các phần tử nhỏ nhất (1), nhỏ thứ hai (2) và nhỏ thứ ba (3) trong mảng được chứa trong các ô A1. A10 Để tìm thêm giá trị, bạn 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(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 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

1. Chọn các ô từ D1 đến D3

2. Trong thanh công thức, nhập công thức này, rồi nhấn Ctrl+Shift+Enter

   =LỚN(A1. A10, HÀNG( 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 cột trống gồm 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 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 các tham chiếu hàng và công thức tạo ra các 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

Hãy xem công thức mà bạn đã sử dụng trước đó — =LARGE(A5. A14, HÀNG( GIÁN TIẾP("1. 3"))) — bắt đầu từ dấu ngoặc đơn bên trong và mở rộng ra bên 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 ô A5. A14 và nó được đánh giá ba lần, một lần cho mỗi tham chiếu do hàm ROW trả về. Các giá trị 3200, 2700 và 2000 được trả về mảng cột ba ô. 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

Cũng như các 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ư SUM và AVERAGE

Tìm chuỗi văn bản dài nhất trong một dải ô

Quay lại ví dụ về chuỗi văn bản trước đó, nhập công thức sau vào một ô trống và nhấn Ctrl+Shift+Enter

=INDEX(A2. A6,TRẬN ĐẤU(TỐI ĐA(LEN(A2. A6)),LEN(A2. A6),0),1)

Dòng chữ "bó ô đó" xuất hiện

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 ô 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, 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 độ 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(A2. A6))

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

LEN(A2. A6)

Đối số loại đối sánh là 0. Loại đối sánh có thể bao gồm giá trị 1, 0 hoặc -1. Nếu bạn chỉ định 1, 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 chỉ định 0, MATCH trả về giá trị đầu tiên chính xác bằng giá trị tra cứu. Nếu bạn chỉ định -1, MATCH sẽ tìm 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 ô A2. A6 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

Đưa công thức mảng nâng cao vào hoạt động

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

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ó chứa giá trị lỗi, chẳng hạn như #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 trố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 tương tự 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ố

=SUM(IF(Doanh số>0,Doanh số))

Hàm IF tạo một mảng các giá trị dương và giá trị 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 hoặc bằng 5

=SUM((Doanh số>0)*(Doanh số<=5)*(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 là số

Bạn cũng có thể tạo công thức mảng sử dụng một loại điều kiện OR. Ví dụ: bạn có thể tính tổng các giá trị nhỏ hơn 5 và 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à lớn hơn 15 rồi chuyển các giá trị đó cho hàm SUM

Bạn không thể sử dụng trực tiếp các hàm AND và OR 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 và các hàm mảng yêu cầu các mảng kết quả. Bạn có thể giải quyết vấn đề bằng cách sử dụng logic được hiển thị trong công thức trước đó. Nói cách khác, bạn thực hiện các phép toán, chẳng hạn như cộng hoặc nhân, trên các giá trị đáp ứng điều kiện OR hoặc AND

Tính trung bình không bao gồm các số không

Ví dụ này chỉ cho bạn cách xóa các số 0 khỏi một 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 sử dụng một phạm vi dữ liệu có tên là Bán hàng

=AVERAGE(IF(Doanh số<>0,Doanh số))

Hàm IF tạo một mảng các giá trị không bằng 0 rồi chuyển các giá trị đó cho hàm AVERAGE

Đếm số lượng khác biệt giữa hai dãy ô

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, đồng thời trả về số điểm khác biệt giữa hai phạm vi đó. Nếu nội dung của hai phạm vi giống hệt nhau, công thức trả về 0. Để sử dụng công thức này, các phạm vi ô cần phải có cùng kích thước và cùng thứ nguyên (ví dụ: nếu MyData là một phạm vi gồm 3 hàng x 5 cột, thì YourData cũng phải là 3 hàng x 5 cột)

=SUM(IF(MyData=YourData,0,1))

Công thức tạo một mảng mới có cùng kích thước với các phạm vi mà bạn đang so sánh. Hàm IF điền vào mảng với giá trị 0 và giá trị 1 (0 cho các ô không khớp và 1 cho các ô giống hệt nhau). Hàm SUM sau đó trả về tổng của các giá trị trong mảng

Bạn có thể đơn giản hóa công thức như thế này

=SUM(1*(Dữ liệu của tôi<>Dữ liệu của bạn))

Giống như công thức đếm các giá trị lỗi trong một 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ị lớn nhất trong một phạm vi

Công thức mảng này trả về số hàng của giá trị lớn nhất trong phạm vi một cột có tên là Dữ liệu

=MIN(IF(Dữ liệu=MAX(Dữ liệu),ROW(Dữ liệu),""))

Hàm IF tạo một mảng mới tương ứng với phạm vi có tên là Dữ liệu. Nếu một ô tương ứng chứa giá trị lớn nhất trong phạm vi, thì mảng chứa số hàng. Mặt khác, mảng chứa một chuỗi rỗng (""). Hàm MIN sử dụng mảng mới làm đối số thứ hai của nó và trả về giá trị nhỏ nhất, tương ứng với số hàng của giá trị lớn nhất trong Dữ liệu. Nếu phạm vi có tên là Dữ liệu chứa các giá trị tối đa giống hệt nhau, thì công thức sẽ trả về hàng của giá trị đầu tiên

Nếu bạn muốn trả về địa chỉ ô thực tế có giá trị lớn nhất, hãy sử dụng công thức này

=ADDRESS(MIN(IF(Dữ liệu=MAX(Dữ liệu),ROW(Dữ liệu),"")),COLUMN(Dữ liệu))