Hướng dẫn part of array excel - một phần của mảng excel

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

• Công thức mảng đa tế bào

  

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

  Khi bạn 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 được tô sáng 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 các tế bào H10: H19 được giải thích. Họ 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 công thức chính sống.Enter, you'll see the results spill down to cells H10:H19. Notice that the spill range is highlighted with a border when you select any cell within the spill range. You might also notice that the formulas in cells H10:H19 are grayed out. They’re just there for reference, so if you want to adjust the formula, you’ll need to select cell H10, where the master formula lives.

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

  Trong ô H20 của sổ làm việc ví dụ, loại hoặc sao chép và dán = sum (f10: f19*g10: g19), sau đó nhấn enter.=SUM(F10:F19*G10:G19), and then press Enter.

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

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

• Công thức mảng động cũng cung cấp những lợi thế này:

  • Tính nhất quán & nbsp; & nbsp; & nbsp; 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. Sự nhất quán đó có thể giúp đảm bảo độ chính xác cao hơn.    If you click any of the cells from H10 downward, you see the same formula. That consistency can help ensure greater accuracy.

  • An toàn & nbsp; & nbsp; & nbsp; Bạn không thể ghi đè lên một thành phần của công thức mảng đa tế bào. Ví dụ: nhấp vào ô H11 và nhấn xóa. Excel đã giành được sự thay đổi đầu ra của mảng. Để thay đổi nó, bạn cần chọn ô trên cùng bên trái trong mảng hoặc ô H10.    You can't overwrite a component of a multi-cell array formula. For example, click cell H11 and press Delete. Excel won’t change the array’s output. To change it, you need to select the top-left cell in the array, or cell H10.

  • Kích thước tệp nhỏ hơn & nbsp; & nbsp; & nbsp; Bạn thường có thể sử dụng một công thức mảng duy nhất thay vì một số công thức trung gian. Ví dụ: ví dụ bán hàng xe sử dụng một 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ác công thức tiêu chuẩn như = F10*G10, F11*G11, F12*G12, v.v., bạn đã sử dụng 11 công thức khác nhau để tính toán kết quả tương tự. Đó không phải là một vấn đề lớn, nhưng nếu bạn có hàng ngàn hàng lên tổng cộng thì sao? Sau đó, nó có thể tạo ra một sự khác biệt lớn.    You can often use a single array formula instead of several intermediate formulas. For example, the car sales example uses one array formula to calculate the results in column E. If you had used standard formulas such as =F10*G10, F11*G11, F12*G12, etc., you would have used 11 different formulas to calculate the same results. That’s not a big deal, but what if you had thousands of rows to total? Then it can make a big difference.

  • Hiệu quả & nbsp; & nbsp; & nbsp; Các chức năng 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).    Array functions can be an efficient way to build complex formulas. The array formula =SUM(F10:F19*G10:G19) is the same as this: =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

  • Spilling & nbsp; & nbsp; & nbsp; Các công thức mảng động sẽ tự động đổ 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ác 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.    Dynamic array formulas will automatically spill into the output range. If your source data is in an Excel table, then your dynamic array formulas will automatically resize as you add or remove data.

  • #TRÀN! lỗi & nbsp; & nbsp; & nbsp; Mảng động đã giới thiệu #spill! Lỗi, chỉ ra rằng phạm vi tràn dự định bị chặn vì một số lý do. Khi bạn giải quyết tắc nghẽn, công thức sẽ tự động tràn.    Dynamic arrays introduced the #SPILL! error, which indicates that the intended spill range is blocked for some reason. When you resolve the blockage, the formula will automatically spill.

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 và sau đó thủ công xung quanh danh sách với niềng răng ({}), như thế này:

= {1,2,3,4,5} hoặc = {"tháng 1", "tháng 2", "tháng 3"} or ={"January","February","March"}

Nếu bạn tách các mục bằng cách sử dụng dấu phẩy, bạn sẽ 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 với dấu phẩy và phân định từng hàng với 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 ra 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 trình tự để 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 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à enter = sequence (1,5). Hàm trình tự xây dựng một mảng cột 1 Row 5 giống như = {1,2,3,4,5}. Kết quả sau đây được hiển thị:=SEQUENCE(1,5). The SEQUENCE function builds a 1 row by 5 column array the same as ={1,2,3,4,5}. The following result is displayed:

• Tạo hằng số thẳng đứng

  Chọn bất kỳ ô trống nào có phòng bên dưới nó và enter = sequence (5) hoặc = {1; 2; 3; 4; 5}. Kết quả sau đây được hiển thị:=SEQUENCE(5), or ={1;2;3;4;5}. The following result is displayed:

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

  Chọn bất kỳ ô trống nào có phòng ở bên phải và bên dưới nó, và nhập = chuỗi (3,4). Bạn thấy kết quả sau:=SEQUENCE(3,4). You see the following result:

  Bạn cũng có thể nhập: & nbsp; hoặc = {1,2,3,4; 5,6,7,8; 9,10,11,12}, nhưng bạn sẽ muốn chú ý đến nơi bạn đặt bán đại tá so với dấu phẩy.

  Như bạn có thể thấy, tùy chọn trình tự cung cấp các lợi thế đáng kể so với việc nhập thủ công các giá trị không đổi mảng của bạn. Chủ yếu, 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 từ mục nhập thủ công. Nó cũng dễ đọc hơn, đặc biệt là khi các đại học có thể khó phân biệt với các dấu phân cách dấu phẩy.

Dưới đây là một ví dụ sử dụng các 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, đi đến liên tục trong một bảng tính công thức hoặc tạo một bảng tính mới.Constant in a formula worksheet, or create a new worksheet.

Trong ô D9, chúng tôi đã nhập = chuỗi (1,5,3,1), nhưng bạn cũng có thể nhập 3, 4, 5, 6 và 7 trong các ô A9: H9. Không có gì đặc biệt về lựa chọn số cụ thể đó, chúng tôi chỉ chọn một cái gì đó không phải là 1-5 để phân biệt.=SEQUENCE(1,5,3,1), but you could also enter 3, 4, 5, 6, and 7 in cells A9:H9. There's nothing special about that particular number selection, we just chose something other than 1-5 for differentiation.

Trong ô E11, enter = sum (d9: h9*chuỗi (1,5)), hoặc = sum (d9: h9*{1,2,3,4,5}). Các công thức trả về 85.=SUM(D9:H9*SEQUENCE(1,5)), or =SUM(D9:H9*{1,2,3,4,5}). The formulas return 85.

Hàm trình tự xây dựng tương đương của hằng số mảng {1,2,3,4,5}. Bởi vì Excel thực hiện các hoạt động trên các biểu thức được đặt trong ngoặc đơn đầu tiên, hai yếu tố tiếp theo được phát huy 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 lên các giá trị trong mảng được lưu trữ bở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), or =SUM(3*1,4*2,5*3,6*4,7*5)

Cuối cùng, hàm Sum thêm các giá trị và trả về 85.

Để tránh sử dụng mảng được lưu trữ và giữ hoàn toàn hoạt động trong bộ nhớ, bạn có thể thay thế nó bằng hằng số mảng khác:

= Sum (trình tự (1,5,3,1)*trình tự (1,5)) hoặc = sum ({3,4,5,6,7}*{1,2,3,4,5}), or =SUM({3,4,5,6,7}*{1,2,3,4,5})

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

• Các hằng số mảng có thể chứa các số, văn bản, giá trị logic (chẳng hạn như đúng và sai) và các giá trị lỗi như #n/a. Bạn có thể sử dụng các số trong các định dạng nguyên, thập phân và khoa học. Nếu bạn bao gồm văn bản, bạn cần bao quanh nó với các dấu ngoặc kép ("văn bản trực tuyến).

• Các hằng số mảng không thể chứa các mảng, công thức hoặc chức năng 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 một công thức như {1,2, A1: D4} hoặc {1,2, Sum (Q2: Z8)}. Ngoài ra, các giá trị số không thể chứa các dấu hiệu phần trăm, dấu hiệu đô la, dấu phẩy hoặc dấu ngoặc đơn.

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

Chuyển đến Công thức> Tên xác định> Xác định tên. Trong hộp tên, nhập quý1. Trong hộp tham chiếu, nhập hằng số sau (nhớ nhập niềng răng bằng tay):Formulas > Defined Names > Define Name. In the Name box, type Quarter1. In the Refers to box, enter the following constant (remember to type the braces manually):

={"January","February","March"}

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

Nhấp vào OK, sau đó chọn bất kỳ hàng nào với ba ô trống và Enter = Quarter1.OK, then select any row with three blank cells, and enter =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 = Chuyển đổi (quý1). =TRANSPOSE(Quarter1).

Nếu bạn muốn hiển thị danh sách 12 thá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ể dựa trên năm hiện tại với chức năng trình tự. Điều gọn gàng về chức năng này là mặc dù chỉ có tháng hiển thị, 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 các ví dụ này trên các bảng dữ liệu mẫu được đặt tên và bộ dữ liệu mẫu nhanh trong sổ làm việc ví dụ.Named array constant and Quick sample dataset worksheets in the example workbook.

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

Điều này sử dụng hàm ngày để tạo một ngày dựa trên năm hiện tại, trình tự tạo ra một hằng số mảng từ 1 đến 12 cho tháng 1 đến tháng 12, sau đó hàm văn bản chuyển đổi định dạng hiển thị thành "MMM" (Jan, Feb, Mar, v.v. ). Nếu bạn muốn hiển thị tên đầy đủ, 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ố được đặt tên làm công thức mảng, hãy nhớ nhập dấu bằng nhau, như trong = tứ kết1, không chỉ phần tư1. Nếu bạn không, Excel diễn giải mảng như 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 hàm, văn bản và số. Tất cả phụ thuộc vào mức độ sáng tạo bạn muốn có được.

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

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

  Enter = chuỗi (1,12)*2 hoặc = {1,2,3,4; 5,6,7,8; 9,10,11,12}*2=SEQUENCE(1,12)*2, or ={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 (-)./), add with (+), and subtract with (-).

• Vuông các vật phẩm trong một mảng

  Enter = sequence (1,12)^2, hoặc = {1,2,3,4; 5,6,7,8; 9,10,11,12}^2=SEQUENCE(1,12)^2, or ={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

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

• Chuyển hàng một hàng một chiều

  Enter = Chuyển đổi (trình tự (1,5)) hoặc = chuyển vị ({1,2,3,4,5}))=TRANSPOSE(SEQUENCE(1,5)), or =TRANSPOSE({1,2,3,4,5})

  Mặc dù bạn đã nhập một hằng số mảng ngang, hàm chuyển đổi chuyển đổi hằng số mảng thành một cột.

• Chuyển đổi cột một chiều

  Enter = chuyển vị (trình tự (5,1)) hoặc = chuyển vị ({1; 2; 3; 4; 5}))=TRANSPOSE(SEQUENCE(5,1)), or =TRANSPOSE({1;2;3;4;5})

  Mặc dù bạn đã nhập một hằng số mảng dọc, hàm chuyển đổi chuyển đổi hằng số thành một hàng.

• Chuyển đổi hằng số hai chiều

  Enter = Chuyển đổi (trình tự (3,4)) hoặc = chuyển vị ({1,2,3,4; 5,6,7,8; 9,10,11,12})=TRANSPOSE(SEQUENCE(3,4)), or =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

  Hàm chuyển đổi chuyển đổi từng hàng thành một loạt các cột.

Phần này cung cấp các ví dụ về các 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 đây giải thích cách sử dụng các công thức mảng để tạo một mảng mới từ một mảng hiện có.

  Enter = trình tự (3,6,10,10), OR = {10,20,30,40,50,60; 70,80,90,100,110,120; 130,140,150,160,170,180}=SEQUENCE(3,6,10,10), or ={10,20,30,40,50,60;70,80,90,100,110,120;130,140,150,160,170,180}

  Hãy chắc chắn nhập {(mở nẹp) trước khi bạn nhập 10 và} (đóng niềng răng) sau khi bạn nhập 180, vì bạn đang tạo một mảng số.

  Tiếp theo, Enter = D9#, hoặc = D9: I11 & nbsp; trong một ô trống. Một mảng 3 x 6 & nbsp; của các ô xuất hiện với cùng các giá trị bạn thấy trong D9: D11. Dấu # được gọi là toán tử phạm vi bị đổ và cách tham chiếu toàn bộ phạm vi mảng thay vì phải gõ nó ra.=D9#, or =D9:I11 in a blank cell. A 3 x 6 array of cells appears with the same values you see in D9:D11. The # sign is called the spilled range operator, and it’s Excel’s way of referencing the entire array range instead of having to type it out.

• 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 bị đổ và chuyển đổi nó thành các bộ phận thành phần 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 các tham chiếu ô thành các giá trị, sau đó chuyển đổi thành một hằng số mảng. Khi bạn nhấn Enter, công thức, = D9#, bây giờ sẽ được = {10,20,30; 40,50,60; 70,80,90}.F2 to switch to edit mode. Next, press F9 to convert the cell references to values, which Excel then converts into an array constant. When you press Enter, the formula, =D9#, should now be ={10,20,30;40,50,60;70,80,90}.

• Đếm các ký tự trong một loạt các ô

  Ví dụ sau đây cho bạn biết cách đếm số lượng ký tự trong một loạt các ô. Điều này & nbsp; bao gồm không gian.

  =SUM(LEN(C9:C13))

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

  =AVERAGE(LEN(C9:C13))

• Nội dung của ô dài nhất trong phạm vi C9: C13

  =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

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

  Chúng ta hãy xem xét kỹ hơn về công thức, bắt đầu từ các yếu tố bên trong và làm việc bên ngoài. Hàm LEN trả về chiều 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, có trong ô D3.

  Đây là nơi mọi thứ có một chút phức tạp. Hàm khớp 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ó yêu cầu ba đối số: giá trị tra cứu, mảng tra cứu và loại khớp. Hàm khớp tìm kiếm mảng tra cứu cho giá trị tra cứu được 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:

  MAX(LEN(C9:C13)

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

  LEN(C9:C13)

  Đối số loại khớp trong trường hợp này là 0. Loại khớp có thể là giá trị 1, 0 hoặc -1.

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

  • 0 -& nbsp; 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 được chỉ định

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

  Cuối cùng, hàm chỉ mục có các đối số này: một mảng, và một số và số cột trong mảng đó. Phạm vi ô C9: C13 cung cấp mảng, hàm khớp 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 có được 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 phạm vi

  Ví dụ này cho thấy cách tìm ba giá trị nhỏ nhất trong một loạt các ô, trong đó một mảng dữ liệu mẫu trong các ô B9: B18HAs đã được tạo bằng: = int (randarray (10,1)*100). Lưu ý rằng Randarray là một hàm dễ bay hơi, vì vậy bạn sẽ nhận được một bộ số ngẫu nhiên mới mỗi khi Excel tính toán.=INT(RANDARRAY(10,1)*100). Note that RANDARRAY is a volatile function, so you’ll get a new set of random numbers each time Excel calculates.

  Enter = small (b9#, chuỗi (d9), = small (b9: b18, {1; 2; 3}))=SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1;2;3})

  Công thức này sử dụng hằng số mảng để đánh giá hàm nhỏ ba lần và trả về 3 thành viên nhỏ nhất trong mảng mà có chứa trong các ô B9: B18, trong đó 3 là giá trị thay đổi trong ô D9. Để tìm thêm các giá trị, bạn có thể tăng giá trị trong hàm trình tự hoặc thêm nhiều đối số vào hằng số. Bạn cũng có thể sử dụng các 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 các 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 nhỏ bằng hàm lớn. Ngoài ra, ví dụ sau sử dụng các hàm hàng và gián tiếp.

  Enter = lớn (b9#, hàng (gián tiếp ("1: 3"))) hoặc = lớn (B9: b18, hàng (gián tiếp ("1: 3"))))=LARGE(B9#,ROW(INDIRECT("1:3"))), or =LARGE(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 chức năng hàng và gián tiếp. Bạn có thể sử dụng hàm hàng để tạo một loạt các số nguyên liên tiếp. Ví dụ: chọn trống và nhập:

  = Hàng (1:10)

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

  = Hàng (gián tiếp ("1:10"))

  Hàm gián tiếp 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 các dấu ngoặc kép). Excel không điều chỉnh các giá trị văn bản khi bạn chèn hàng hoặc di chuyển công thức mảng. Do đó, hàm hàng 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 trình tự:

  =SEQUENCE(10)

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

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

  Hàm tổng trong Excel không hoạt động khi bạn cố gắng tổng hợp 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 biết cách tổng hợp các giá trị trong một phạm vi dữ liệu có tên có chứa lỗi:

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

  Công thức tạo ra một mảng mới chứa các giá trị ban đầu trừ đi bất kỳ giá trị lỗi nào. Bắt đầu từ các hàm bên trong và làm việc bên ngoài, hàm isError tìm kiếm phạm vi ô (dữ liệu) cho các lỗi. Hàm nếu trả về một giá trị cụ thể nếu một điều kiện bạn chỉ định đánh giá thành true và một giá trị khác nếu nó đánh giá là sai. 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á thành true và nó trả về các giá trị còn lại từ phạm vi (dữ liệu) vì chúng đánh giá là sai, có nghĩa là chúng không chứa các giá trị lỗi. Hàm tổng sau đó tính tổng số cho mảng được 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 phạm vi có tên dữ liệu thay vì lọc chúng ra:

  =SUM(IF(ISERROR(Data),1,0))

  Công thức này tạo ra một mảng chứa giá trị 1 cho các ô 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 và đạt được kết quả tương tự bằng cách xóa đối số thứ ba cho hàm IF, như thế này:

  =SUM(IF(ISERROR(Data),1))

  Nếu bạn không chỉ định đối số, chức năng IF trả về sai nếu ô không chứa giá trị lỗi. Bạn có thể đơn giản hóa công thức thậm chí nhiều hơn:

  =SUM(IF(ISERROR(Data)*1))

  Phiên bản này hoạt động vì true*1 = 1 và false*1 = 0.

Bạn có thể cần phải tổng hợp 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ổng số các số nguyên dương trong một phạm vi có tên 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ột mảng các giá trị dương và sai. Hàm tổng 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ổng hợp 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 các giá trị lớn hơn 0 và nhỏ hơn 2500:AND less than 2500:

=SUM((Sales>0)*(Sales

Hãy nhớ rằng công thức này trả về một 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ác công thức mảng sử dụng một loại hoặc điều kiện. Ví dụ: bạn có thể tổng hợp các giá trị lớn hơn 0 & nbsp; hoặc nhỏ hơn 2500:OR less than 2500:

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

Bạn không thể sử dụng trực tiếp các chức năng và và hoặc hoặc các hàm trong các công thức mảng vì các hàm đó trả về một kết quả duy nhất, đúng hoặc sai 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 thao tác toán học, chẳng hạn như bổ sung hoặc nhân & nbsp; trên các giá trị đáp ứng hoặc điều kiện.

Ví dụ này chỉ cho bạn cách loại bỏ các số không khỏi phạm vi khi bạn cần trung bình các giá trị trong phạm vi đó. Công thức sử dụng phạm vi dữ liệu có tên Sales:

=AVERAGE(IF(Sales0,Sales))

Hàm IF tạo ra một mảng các giá trị không bằng 0 và sau đó chuyển các giá trị đó đến hàm trung bình.

Công thức mảng này so sánh các giá trị trong hai phạm vi ô có tên MyData và YourData và trả về số lượng khác biệt giữa hai ô. 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, phạm vi ô cần phải có cùng kích thước và cùng một chiều. Ví dụ: nếu MyData là phạm vi 3 hàng cho 5 cột, thì bạn cũng phải là 3 hàng 5 cột:

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

Công thức tạo ra 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 lấp đầy mảng với giá trị 0 và giá trị 1 (0 cho sự không phù hợp và 1 cho các ô giống hệt nhau). Hàm tổng 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*(MyDataYourData))

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ị tối đa trong phạm vi một cột có tên là dữ liệu:

=MIN(IF(Data=MAX(Data),ROW(Data),""))

Hàm IF tạo một mảng mới tương ứng với phạm vi dữ liệu được đặt tên. Nếu một ô tương ứng chứa giá trị tối đa trong phạm vi, mảng chứa số hàng. Mặt khác, mảng chứa một chuỗi trống (""). Hàm Min sử dụng mảng mới làm đối số thứ hai và trả về giá trị nhỏ nhất, tương ứng với số hàng của giá trị tối đa trong dữ liệu. Nếu phạm vi dữ liệu có tên chứa các giá trị tối đa giống hệt nhau, 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ủa giá trị tối đa, hãy sử dụng công thức này:

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

Bạn sẽ tìm thấy các ví dụ tương tự trong sổ làm việc mẫu về sự khác biệt giữa bảng tính của bộ dữ liệu.Differences between datasets worksheet.

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

• Công thức mảng đa tế bào

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

1. Công thức (tổng số lớn)=C2:C11*D2:D11, and then press Ctrl+Shift+Enter.

2. Tổng cộng=SUM(C2:C11*D2:D11), and then press Ctrl+Shift+Enter.

Để xem tổng doanh số của coupe và xe hơi cho mỗi 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, sau đó nhấn Ctrl+Shift+Enter.Ctrl+Shift+Enter, Excel surrounds the formula with braces ({ }) and inserts an instance of the formula in each cell of the selected range. This happens very quickly, so what you see in column E is the total sales amount for each car type for each salesperson. If you select E2, then select E3, E4, and so on, you'll see that the same formula is shown: {=C2:C11*D2:D11}. 

• Để xem tổng số lớn của tất cả các doanh số, chọn ô F11, nhập công thức = sum (c2: c11*d2: d11), sau đó nhấn Ctrl+shift+enter.

Khi bạn nhấn Ctrl+Shift+Enter, Excel bao quanh công thức với niềng răng ({}) và chèn một thể hiện công thức trong mỗi ô của phạm vi đã chọn. Điều này xảy ra rất nhanh, vì vậy những gì bạn thấy trong cột E là tổng số tiền bán cho mỗi loại xe cho mỗi nhân viên bán hàng. Nếu bạn chọn E2, thì hãy chọn E3, E4, v.v., bạn sẽ thấy rằng cùng một công thức được hiển thị: {= C2: C11*D2: D11}. & NBSP;Ctrl+Shift+Enter:

=SUM(C2:C11*D2:D11)

Tạo công thức mảng đơn bào

Trong ô D13 của sổ làm việc, nhập công thức sau và sau đó nhấn Ctrl+Shift+Enter:

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 chức năng & nbsp; sum & nbsp; để thêm tổng số lại với nhau. Kết quả là tổng doanh thu lớn 1.590.000 đô la. 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ổng hợp một phần hoặc tất cả dữ liệu đó bằng cách tạo một công thức mảng trong một ô duy nhất thay vì kéo công thức xuống qua 1.000 hàng.

• Ngoài ra, lưu ý rằng công thức đơn bào trong tế bào D13 & NBSP; hoàn toàn độc lập với công thức đa tế bào (công thức trong các tế bào E2 đến E11). Đây là một lợi thế khác của việc sử dụng các công thức mảng & nbsp; - tính linh hoạt. Bạn có thể thay đổi các công thức trong cột E hoặc xóa hoàn toàn cột đó, mà không ảnh hưởng đến công thức trong D13.    If you click any of the cells from E2 downward, you see the same formula. That consistency can help ensure greater accuracy.

• Công thức mảng cũng cung cấp những lợi thế này:    You cannot overwrite a component of a multi-cell array formula. For example, click cell E3 and press Delete. You have to either select the entire range of cells (E2 through E11) and change the formula for the entire array, or leave the array as is. As an added safety measure, you have to press Ctrl+Shift+Enter to confirm any change to the formula.

• Tính nhất quán & nbsp; & nbsp; & nbsp; & nbsp; 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. Sự nhất quán đó có thể giúp đảm bảo độ chính xác cao hơn.    You can often use a single array formula instead of several intermediate formulas. For example, the workbook uses one array formula to calculate the results in column E. If you had used standard formulas (such as =C2*D2, C3*D3, C4*D4…), you would have used 11 different formulas to calculate the same results.

An toàn & nbsp; & nbsp; & nbsp; & nbsp; bạn không thể ghi đè lên một thành phần của công thức mảng đa tế bào. Ví dụ: nhấp vào ô E3 và nhấn xóa. Bạn phải chọn toàn bộ phạm vi của ô (E2 đến E11) và thay đổi công thức cho toàn bộ mảng hoặc để lại mảng như vậy. Là một biện pháp an toàn bổ sung, bạn phải nhấn Ctrl+Shift+Enter để xác nhận mọi thay đổi đối với công thức.Ctrl+Shift+Enter to enter your formula. When you do this, Excel surrounds your array formula with braces — if you type the braces manually, your formula will be converted to a text string, and it won't work.

Kích thước tệp nhỏ hơn & nbsp; & nbsp; & nbsp; & nbsp; bạn thường có thể sử dụng một công thức mảng duy nhất thay vì một số 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 toán 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ư = C2*D2, C3*D3, C4*D4 kết quả tương tự.=SUM(C2:C11*D2:D11) is the same as this: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Nói chung, các công thức mảng sử dụng cú pháp công thức tiêu chuẩn. Tất cả đều bắt đầu với một dấu hiệu (=) bằng nhau và bạn có thể sử dụng hầu hết các hàm Excel tích hợp trong các công thức mảng của bạn. Sự 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 bạn. Khi bạn làm điều này, Excel bao quanh công thức mảng của bạn với niềng răng & nbsp; - Nếu bạn nhập niềng răng bằng tay, công thức của bạn sẽ được chuyển đổi thành chuỗi văn bản và nó sẽ không hoạt động.Press Ctrl+Shift+Enter whenever you need to enter an array formula. This applies to both single-cell and multi-cell formulas.

Các chức năng 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).

• Quan trọng: & nbsp; 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ả hai công thức đơn bào và đa tế bào.

• Bất cứ khi nào bạn làm việc với các công thức đa tế bào, cũng hãy nhớ:

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

• Bạn không thể thay đổi nội dung của một ô riêng lẻ trong một công thức mảng. Để thử điều này, chọn ô E3 trong sổ làm việc và nhấn xóa. Excel hiển thị một 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ột mảng.E2:E11), then press Delete.

• Bạn có thể di chuyển hoặc xóa toàn bộ công thức mảng, nhưng bạn 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ỏ một công thức mảng, trước tiên bạn xóa công thức hiện có và sau đó bắt đầu lại.

Để xóa một công thức mảng, chọn toàn bộ phạm vi công thức (ví dụ: E2: E11), sau đó nhấn xóa.F2 to edit the formula,  then press CTRL+SHIFT+ENTER to confirm the formula once you've adjusted the formula range. The key is to select the entire range, starting with the top-left cell in the array. The top-left cell is the one that gets edited.

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

• Thỉnh thoảng bạn có thể quên nhấn Ctrl+Shift+Enter. Nó có thể xảy ra với ngay cả những người dùng Excel có kinh nghiệm nhất. Hãy nhớ nhấn kết hợp khóa này bất cứ khi nào bạn nhập hoặc chỉnh sửa công thức mảng.Ctrl+Shift+Enter. It can happen to even the most experienced Excel users. Remember to press this key combination whenever you enter or edit an array formula.

• 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ác công thức của bạn. Trong thực tế, các công thức mảng thường không được giải thích trong một bảng tính. Do đó, nếu người khác cần sửa đổi sổ làm việc của bạn, bạn nên tránh các công thức mảng hoặc đảm bảo 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ủa bạn, các công thức mảng lớn có thể làm chậm các 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 các hằng số mảng bằng cách nhập danh sách các mục và sau đó thủ công xung quanh danh sách với niềng răng ({}), như thế này:{ }), like this:

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

Đến bây giờ, bạn biết bạn cần nhấn Ctrl+Shift+Enter khi bạn tạo các công thức mảng. Bởi vì các hằng số mảng là một thành phần của các công thức mảng, bạn bao quanh các hằng số bằng niềng răng bằng cách gõ thủ công chúng. Sau đó, bạn sử dụng Ctrl+Shift+Enter để nhập toàn bộ công thức.Ctrl+Shift+Enter when you create array formulas. Because array constants are a component of array formulas, you surround the constants with braces by manually typing them. You then use Ctrl+Shift+Enter to enter the entire formula.

Nếu bạn tách các mục bằng cách sử dụng dấu phẩy, bạn sẽ 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 cách sử dụng dấu chấm phẩy.

Đây là một mảng trong một hàng duy nhất: {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 gồm hai hàng và bốn cột: {1,2,3,4; 5,6,7,8}. Trong hai mảng 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 duy nhất ngăn cách hai hàng, giữa 4 và 5.

Như với các công thức mảng, bạn có thể sử dụng các hằng số mảng với hầu hết các hàm tích hợp 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.

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 ra các hằng số ngang, dọc và hai chiều.

Tạo hằng số ngang

1. Trong một bảng tính trống, chọn các ô A1 đến E1.

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

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

   Trong trường hợp này, bạn nên nhập niềng răng mở và đóng ({}) và Excel sẽ thêm bộ thứ hai cho bạn.{ }), and Excel will add the second set for you.

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

Tạo hằng số thẳng đứng

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

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

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

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

Tạo hằng số thẳng đứng

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

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

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

   Trong trường hợp này, bạn nên nhập niềng răng 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 hằng số thẳng đứng

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

2. Tạo hằng số hai chiều3, and then type 4 in B1, 5 in C1, 6 in D1, and 7 in E1.

3. Trong sổ làm việc của bạn, chọn một khối các ô rộng bốn cột với ba hàng cao. Ctrl+Shift+Enter:

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

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

   Dưới đây là một ví dụ đơn giản sử dụng các hằng số:85 appears in cell A3.

Trong sổ làm việc mẫu, tạo một bảng tính mới.

Trong ô A1, loại 3, và sau đó loại 4 trong B1, 5 trong C1, 6 trong D1 và 7 trong E1.

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

= Sum (a1: e1*{1,2,3,4,5}))

Lưu ý rằng Excel bao quanh hằng số với một bộ niềng răng khác, bởi vì bạn đã nhập nó 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 thức hoạt động của công thức.{1,2,3,4,5}. Remember that Excel does not surround array constants with braces; you actually type them. Also remember that after you add a constant to an array formula, you press Ctrl+Shift+Enter to enter the formula.

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

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

1. HàmSUM function adds the values, and the sum 85 appears in cell A3.

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

=SUM({3,4,5,6,7}*{1,2,3,4,5})

3. Nhà điều hànhCtrl+Shift+Enter. You'll see the same result as you did in the earlier exercise that used the array formula:

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

Các hằng số mảng có thể chứa các số, văn bản, giá trị logic (chẳng hạn như đúng và sai) 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 các định dạng số nguyên, thập phân và khoa học. Nếu bạn bao gồm văn bản, bạn cần bao quanh văn bản với dấu ngoặc kép (").").

Các hằng số mảng không thể chứa các mảng, công thức hoặc chức năng 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 một công thức như {1,2, A1: D4} hoặc {1,2, Sum (Q2: Z8)}. Ngoài ra, các giá trị số không thể chứa các dấu hiệu phần trăm, dấu hiệu đô la, dấu phẩy hoặc dấu ngoặc đơn.

Một trong những cách tốt nhất để sử dụng hằng số mảng là đặt tên cho 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ác công thức mảng của bạn với các công thức khác. Để đặt tên cho một hằng số mảng và sử 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 được xác định, bấm Xác định tên. Hộp thoại Xác định xuất hiện.Formulas tab, in the Defined Names group, click Define Name.
   The Define Name dialog box appears.

2. Trong hộp tên, nhập quý1.Name box, type Quarter1.

3. Trong hộp tham chiếu, nhập hằng số sau (nhớ nhập niềng răng bằng tay):Refers to box, enter the following constant (remember to type the braces manually):

   ={"January","February","March"}

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

4. Nhấp vào OK, sau đó chọn một hàng ba ô trống.OK, and then select a row of three blank cells.

5. Nhập công thức sau, sau đó nhấn Ctrl+Shift+Enter.Ctrl+Shift+Enter.

   =Quarter1

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

Khi bạn sử dụng một hằng số được đặ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, Excel diễn giải mảng như 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ố.

Tìm kiếm các vấn đề 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 với 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í, hằng số mảng có thể không được tạo chính xác hoặc bạn có thể thấy một thông báo cảnh báo.

• Bạn có thể đã chọn một loạt các ô không khớp với số lượng phần tử trong hằng số của bạn. 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 tế bào, 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 ô, Excel bỏ qua các giá trị không có ô tương ứng.

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

Nhân mỗi mục trong một mảng

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

2. Nhập công thức sau, sau đó nhấn Ctrl+Shift+Enter:Ctrl+Shift+Enter:

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

Vuông các vật phẩm trong một mảng

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

2. Nhập công thức mảng sau, sau đó nhấn Ctrl+Shift+Enter: 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}

   Ngoài ra, nhập công thức mảng này, sử dụng toán tử Caret (^):^):

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

Chuyển hàng 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, sau đó nhấn Ctrl+Shift+Enter:Ctrl+Shift+Enter:

   =TRANSPOSE({1,2,3,4,5})

   Vuông các vật phẩm trong một mảngTRANSPOSE function converts the array constant into a column.

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

1. Nhập công thức mảng sau, sau đó nhấn Ctrl+Shift+Enter:

2. Ngoài ra, nhập công thức mảng này, sử dụng toán tử Caret (^):Ctrl+Shift+Enter:

   =TRANSPOSE({1;2;3;4;5})

Chuyển hàng một hàng một chiềuTRANSPOSE function converts the constant into a row.

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

1. Mặc dù bạn đã nhập một hằng số mảng ngang, hàm chuyển đổi chuyển đổi hằng số mảng thành một cột.

2. Chuyển đổi cột một chiềuCtrl+Shift+Enter:

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

   Chọn một hàng năm ô trống.TRANSPOSE function converts each row into a series of columns.

Nhập công thức sau, sau đó nhấn Ctrl+Shift+Enter:

Mặc dù bạn đã nhập một hằng số mảng dọc, hàm chuyển đổi chuyển đổi hằng số thành một hàng.

Chuyển đổi hằng số hai chiều

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

1. Nhập hằng số sau, sau đó nhấn Ctrl+Shift+Enter:

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

   Hàm chuyển đổi chuyển đổi từng hàng thành một loạt các cột.{ (opening brace) before you type 10, and } (closing brace) after you type 90, because you're creating an array of numbers.

2. Phần này cung cấp các ví dụ về các công thức mảng cơ bản.Ctrl+Shift+Enter, which enters this array of numbers in the cell range C8:E10 by using an array formula. On your worksheet, C8 through E10 should look like this:

   10	20	30
   40	50	60
   70	80	90

3. Chọn phạm vi ô C1 đến E3.

4. Nhập công thức sau vào thanh công thức, sau đó nhấn Ctrl+Shift+Enter:Ctrl+Shift+Enter:

   =C8:E10

   Một mảng 3x3 của các ô xuất hiện trong các ô C1 đến E3 với cùng các giá trị bạn thấy & nbsp; 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: C3 được chọn, nhấn F2 để chuyển sang chế độ chỉnh sửa. & NBSP;F2 to switch to edit mode. 

2. Nhấn F9 để chuyển đổi các tham chiếu ô thành các giá trị. & NBSP; 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}.F9 to convert the cell references to values. Excel converts the values into an array constant. The formula should now be ={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.Ctrl+Shift+Enter to enter the array constant as an array formula.

Đếm các ký tự trong một loạt các ô

Ví dụ sau đây cho bạn biết cách đếm số lượng ký tự, bao gồm cả khoảng trắng, trong một loạt các ô.

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

   Dữ liệu
   Đây là một
   một loạt các tế bào
   đến với nhau
   để tạo thành a
   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,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).Ctrl+Shift+Enter to see the total number of characters in cells A2:A6 (66).

3. Chọn ô A10, sau đó nhấn Ctrl+Shift+Enter để xem nội dung của các ô dài nhất A2: A6 (ô A3).Ctrl+Shift+Enter to see the contents of the longest of cells A2:A6 (cell A3).

Công thức sau đây được sử dụng trong ô A8 & NBSP; tính tổng số ký tự (66) trong các ô A2 đến A6.

=SUM(LEN(A2:A6))

Trong trường hợp này, hàm LEN trả về chiều dài của mỗi chuỗi văn bản trong mỗi ô trong phạm vi. Hàm tổng sau đó thêm các giá trị đó lại với nhau và hiển thị kết quả (66).LEN function returns the length of each text string in each of the cells in the range. The SUM function then adds those values together and displays the result (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 loạt các ô.

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

2. Chọn các ô C1 đến C3. & NBSP; bộ ô này sẽ giữ kết quả được trả về bởi công thức mảng.

3. Nhập công thức sau, sau đó nhấn Ctrl+Shift+Enter: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 nhỏ ba lần và trả về các thành viên nhỏ nhất (1), nhỏ thứ hai (2) và thứ ba (3) trong mảng có trong các ô A1: A10 & NBSP; để tìm thêm các giá trị , bạn thêm nhiều đối số vào hằng số. Bạn cũng có thể sử dụng các 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ụ:SMALL function three times and return the smallest (1), second smallest (2), and third smallest (3) members in the array that is contained in cells A1:A10 To find more values, you add more arguments to the constant. You can also use additional functions with this formula, such as SUM or AVERAGE. For example:

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

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

Tìm các 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 nhỏ bằng hàm lớn. Ngoài ra, ví dụ sau sử dụng các hàm hàng và gián tiếp.SMALL function with the LARGE function. In addition, the following example uses the ROW and INDIRECT functions.

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: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 chức năng hàng và gián tiếp. Bạn có thể sử dụng hàm hàng để tạo một loạt 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 và sau đó nhấn Ctrl+Shift+Enter:ROW and INDIRECT functions. You can use the ROW function to create an array of consecutive integers. For example, select an empty column of 10 cells in your practice workbook, enter this array formula, and then press Ctrl+Shift+Enter:

=ROW(1:10)

Công thức tạo ra một cột gồm 10 số nguyên liên tiếp. Để xem một vấn đề tiềm năng, chèn một hàng trên phạm vi chứa công thức mảng (nghĩa là, trên hàng 1). Excel điều chỉnh các tham chiếu hàng và công thức tạo số nguyên từ 2 đến 11. Để khắc phục sự cố đó, bạn thêm hàm gián tiếp vào công thức:INDIRECT function to the formula:

=ROW(INDIRECT("1:10"))

Hàm gián tiếp 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 các dấu ngoặc kép). Excel không điều chỉnh các giá trị văn bản khi bạn chèn hàng hoặc di chuyển công thức mảng. Do đó, hàm hàng luôn tạo ra mảng số nguyên mà bạn muốn.INDIRECT function uses text strings as its arguments (which is why the range 1:10 is surrounded by double quotation marks). Excel does not adjust text values when you insert rows or otherwise move the array formula. As a result, the ROW function always generates the array of integers that you want.

Hãy xem công thức mà bạn đã sử dụng trước đó & nbsp; - = lớn (A5: A14, ROW (gián tiếp ("1: 3"))) của các giá trị văn bản, trong trường hợp này, các giá trị 1 đến 3. Hàm hàng lần lượt tạo ra một mảng cột ba tế bào. Hàm lớn 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 được trả về bởi hàm hàng. Các giá trị 3200, 2700 và 2000 được trả lại cho mảng cột ba tế bào. Nếu bạn muốn tìm thêm giá trị, bạn thêm một phạm vi ô lớn hơn vào hàm gián tiếp.=LARGE(A5:A14,ROW(INDIRECT("1:3"))) — starting from the inner parentheses and working outward: The INDIRECT function returns a set of text values, in this case the values 1 through 3. The ROW function in turn generates a three-cell columnar array. The LARGE function uses the values in the cell range A5:A14, and it is evaluated three times, once for each reference returned by the ROW function. The values 3200, 2700, and 2000 are returned to the three-cell columnar array. If you want to find more values, you add a greater cell range to the INDIRECT function.

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

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

Quay trở lại ví dụ 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:Ctrl+Shift+Enter:

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

Văn bản "Bunch of tế bào" xuất hiện.

Chúng ta hãy xem xét kỹ hơn về công thức, bắt đầu từ các yếu tố bên trong và làm việc bên ngoài. Hàm LEN trả về chiều 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, có trong ô A3.LEN function returns the length of each of the items in the cell range A2:A6. The MAX function calculates the largest value among those items, which corresponds to the longest text string, which is in cell A3.

Đây là nơi mọi thứ có một chút phức tạp. Hàm khớp 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ó yêu cầu ba đối số: giá trị tra cứu, mảng tra cứu và loại khớp. Hàm khớp tìm kiếm mảng tra cứu cho giá trị tra cứu được 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:MATCH function calculates the offset (the relative position) of the cell that contains the longest text string. To do that, it requires three arguments: a lookup value, a lookup array, and a match type. The MATCH function searches the lookup array for the specified lookup value. In this case, the lookup value is the longest text string:

(MAX(LEN(A2:A6))

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

LEN(A2:A6)

Đối số loại khớp là 0. Loại khớp 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 thấy giá trị nhỏ nhất lớn hơn hoặc bằng với giá trị tra cứu được chỉ định. Nếu bạn bỏ qua một loại khớp, Excel giả định 1.0. The match type can consist of a 1, 0, or -1 value. If you specify 1, MATCH returns the largest value that is less than or equal to the lookup value. If you specify 0, MATCH returns the first value exactly equal to the lookup value. If you specify -1, MATCH finds the smallest value that is greater than or equal to the specified lookup value. If you omit a match type, Excel assumes 1.

Cuối cùng, hàm chỉ mục có các đối số này: một mảng, và một số và số cột trong mảng đó. Phạm vi ô A2: A6 & NBSP; cung cấp mảng, hàm khớp 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.INDEX function takes these arguments: an array, and a row and column number within that array. The cell range A2:A6 provides the array, the MATCH function provides the cell address, and the final argument (1) specifies that the value comes from the first column in the array.

Phần này cung cấp các ví dụ về các công thức mảng tiên tiến.

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

Hàm tổng trong Excel không hoạt động khi bạn cố gắng tổng hợp 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 biết cách tổng hợp các giá trị trong một phạm vi dữ liệu có tên có chứa lỗi.SUM function in Excel does not work when you try to sum a range that contains an error value, such as #N/A. This example shows you how to sum the values in a range named Data that contains errors.

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

Công thức tạo ra một mảng mới chứa các giá trị ban đầu trừ đi bất kỳ giá trị lỗi nào. Bắt đầu từ các hàm bên trong và làm việc bên ngoài, hàm isError tìm kiếm phạm vi ô (dữ liệu) cho các lỗi. Hàm nếu trả về một giá trị cụ thể nếu một điều kiện bạn chỉ định đánh giá thành true và một giá trị khác nếu nó đánh giá là sai. 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á thành true và nó trả về các giá trị còn lại từ phạm vi (dữ liệu) vì chúng đánh giá là sai, có nghĩa là chúng không chứa các giá trị lỗi. Hàm tổng sau đó tính tổng số cho mảng được lọc.ISERROR function searches the cell range (Data) for errors. The IF function returns a specific value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. In this case, it returns empty strings ("") for all error values because they evaluate to TRUE, and it returns the remaining values from the range (Data) because they evaluate to FALSE, meaning that they don't contain error values. The SUM function then calculates the total for the filtered array.

Đế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 phạm vi dữ liệu có tên thay vì lọc chúng ra:

=SUM(IF(ISERROR(Data),1,0))

Công thức này tạo ra một mảng chứa giá trị 1 cho các ô 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 và đạt được kết quả tương tự bằng cách xóa đối số thứ ba cho hàm IF, như thế này:IF function, like this:

=SUM(IF(ISERROR(Data),1))

Nếu bạn không chỉ định đối số, chức năng IF trả về sai nếu ô không chứa giá trị lỗi. Bạn có thể đơn giản hóa công thức thậm chí nhiều hơn:IF function returns FALSE if a cell does not contain an error value. You can simplify the formula even more:

=SUM(IF(ISERROR(Data)*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 các điều kiện

Bạn có thể cần phải tổng hợp 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ổng số các số nguyên dương trong một phạm vi có tên Sales:

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

Hàm IF tạo ra một mảng các giá trị dương và giá trị sai. Hàm tổng 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.IF function creates an array of positive values and false values. The SUM function essentially ignores the false values because 0+0=0. The cell range that you use in this formula can consist of any number of rows and columns.

Bạn cũng có thể tổng hợp 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 các giá trị lớn hơn 0 và nhỏ hơn hoặc bằng 5:

=SUM((Sales>0)*(Sales

Hãy nhớ rằng công thức này trả về một 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ác công thức mảng sử dụng một loại hoặc điều kiện. Ví dụ: bạn có thể tổng hợp các giá trị nhỏ hơn 5 và lớn hơn 15:

=SUM(IF((Sales15),Sales))

Hàm IF tìm thấy tất cả các giá trị nhỏ hơn 5 và lớn hơn 15 và sau đó chuyển các giá trị đó đến hàm tổng.IF function finds all values smaller than 5 and greater than 15 and then passes those values to the SUM function.

Bạn không thể sử dụng trực tiếp các chức năng và và hoặc hoặc các hàm trong các công thức mảng vì các hàm đó trả về một kết quả duy nhất, đúng hoặc sai 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 thao tác toán học, chẳng hạn như bổ sung hoặc nhân, trên các giá trị đáp ứng hoặc điều kiện.AND and OR functions in array formulas directly because those functions return a single result, either TRUE or FALSE, and array functions require arrays of results. You can work around the problem by using the logic shown in the previous formula. In other words, you perform math operations, such as addition or multiplication, on values that meet the OR or AND condition.

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 loại bỏ các số không khỏi phạm vi khi bạn cần trung bình các giá trị trong phạm vi đó. Công thức sử dụng phạm vi dữ liệu có tên Sales:

=AVERAGE(IF(Sales0,Sales))

Hàm IF tạo ra một mảng các giá trị không bằng 0 và sau đó chuyển các giá trị đó đến hàm trung bình.IF function creates an array of values that do not equal 0 and then passes those values to the AVERAGE function.

Đếm số lượng 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 MyData và YourData và trả về số lượng khác biệt giữa hai ô. 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, phạm vi ô cần phải có cùng kích thước và cùng một chiều (ví dụ: nếu mydata là phạm vi 3 hàng với 5 cột, YourData cũng phải là 3 hàng theo 5 cột):

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

Công thức tạo ra 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 lấp đầy mảng với giá trị 0 và giá trị 1 (0 cho sự không phù hợp và 1 cho các ô giống hệt nhau). Hàm tổng sau đó trả về tổng của các giá trị trong mảng.IF function fills the array with the value 0 and the value 1 (0 for mismatches and 1 for identical cells). The SUM function then returns the sum of the values in the array.

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

=SUM(1*(MyDataYourData))

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ị tối đa trong một 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 một cột có tên là dữ liệu:

=MIN(IF(Data=MAX(Data),ROW(Data),""))

Hàm IF tạo một mảng mới tương ứng với phạm vi dữ liệu được đặt tên. Nếu một ô tương ứng chứa giá trị tối đa trong phạm vi, mảng chứa số hàng. Mặt khác, mảng chứa một chuỗi trống (""). Hàm Min sử dụng mảng mới làm đối số thứ hai và trả về giá trị nhỏ nhất, tương ứng với số hàng của giá trị tối đa trong dữ liệu. Nếu phạm vi dữ liệu có tên chứa các giá trị tối đa giống hệt nhau, công thức sẽ trả về hàng của giá trị đầu tiên.IF function creates a new array that corresponds to the range named Data. If a corresponding cell contains the maximum value in the range, the array contains the row number. Otherwise, the array contains an empty string (""). The MIN function uses the new array as its second argument and returns the smallest value, which corresponds to the row number of the maximum value in Data. If the range named Data contains identical maximum values, the formula returns the row of the first value.

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

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))