Công thức mảng là công thức có thể thực hiện nhiều phép tính đối với một hoặc nhiều mục trong mảng. Bạn có thể hình dung mảng là một hàng hoặc một cột giá trị hoặc là một kết hợp các hàng và cột giá trị. Công thức mảng có thể trả về nhiều kết quả hoặc một kết quả duy nhất. Bắt đầu từ bản cập nhật tháng 9 năm 2018 cho Microsoft 365, mọi công thức có thể trả về nhiều kết quả đều sẽ tự động tràn kết quả xuống hoặc tràn sang các ô bên cạnh. Thay đổi hành vi này cũng đi kèm với một vài hàm mảng động mới. Công thức mảng động, dù đang sử dụng các hàm hiện có hay các hàm mảng động, chỉ cần được nhập vào một ô đơn, rồi được xác nhận bằng cách nhấn Enter. Trước đây, công thức mảng thừa tự yêu cầu trước hết phải chọn toàn bộ phạm vi đầu ra, rồi xác nhận công thức bằng Ctrl+Shift+Enter. Chúng thường được gọi là công thức CSE. Bạn có thể dùng công thức mảng để thực hiện các tác vụ phức tạp, chẳng hạn như:
Các ví dụ sau sẽ cho bạn thấy cách tạo các công thức mảng đa ô và đơn ô. Nếu có thể, chúng tôi đã đưa vào các ví dụ với một số hàm mảng động, cũng như các công thức mảng hiện có được nhập dưới dạng cả mảng động và mảng thừa tự. Tải xuống ví dụ của chúng tôiTải xuống sổ làm việc ví dụ với tất cả các ví dụ về công thức mảng trong bài viết. Bài tập này sẽ cho bạn thấy cách dùng các công thức mảng đa ô và đơn ô để tính toán bộ số liệu doanh số bán hàng. Bộ các bước đầu tiên dùng công thức đa ô để tính toán các tổng phụ. Bộ thứ hai dùng công thức đơn ô để tính toán tổng cộng.
Hằng số mảng là một thành phần của công thức mảng. Bạn tạo hằng số mảng bằng cách nhập danh sách các mục, sau đó đặt danh sách này trong một cặp dấu ngoặc nhọn ({ }) theo cách thủ công, như thế này: \={1\2\3\4\5} hoặc ={"tháng 1"\"tháng 2"\"tháng 3"} Nếu bạn phân cách các mục bằng dấu phẩy, bạn sẽ tạo ra một mảng ngang (hàng). Nếu bạn phân cách các mục bằng dấu chấm phẩy, bạn sẽ tạo ra một mảng dọc (cột). Để tạo mảng hai chiều, bạn hãy phân cách các mục trong mỗi hàng bằng dấu phẩy và phân cách các hàng bằng dấu chấm phẩy. Quy trình sau đây sẽ giúp bạn thực hành việc tạo các hằng số ngang, dọc và hai chiều. Chúng tôi sẽ hiển thị các ví dụ bằng cách sử dụng hàm SEQUENCE để tự động tạo hằng số mảng, cũng như nhập thủ công các hằng số mảng.
Đây là ví dụ dùng hằng số mảng như một phần của công thức lớn hơn. Trong sổ làm việc mẫu, hãy đến trang tính Hằng số trong công thức hoặc tạo một trang tính mới. Trong ô D9, chúng ta đã nhập =SEQUENCE(1,5,3,1), nhưng bạn cũng có thể nhập 3, 4, 5, 6 và 7 vào các ô A9:H9. Không có gì đặc biệt về lựa chọn số cụ thể đó, chúng tôi chỉ chọn một số khác 1-5 để phân biệt. Trong ô E11, hãy nhập =SUM(D9:H9*SEQUENCE(1,5)), hoặc =SUM(D9:H9*{1\2\3\4\5}). Công thức trả về 85. Hàm SEQUENCE xây dựng giá trị tương đương với hằng số mảng {1\2\3\4\5}. Vì Excel thực hiện các thao tác trên biểu thức được đặt trong dấu ngoặc đơn trước tiên, hai thành phần tiếp theo bắt đầu hoạt động là các giá trị ô trong D9:H9 và toán tử nhân (*). Tại thời điểm này, công thức nhân các giá trị trong mảng được lưu trữ với các giá trị tương ứng trong hằng số. Nó tương đương với: \=SUM(D9*1,E9*2,F9*3,G9*4,H9*5)hoặc =SUM(3*1,4*2,5*3,6*4,7*5) Cuối cùng, hàm SUM cộng các giá trị với nhau và trả về 85. Để tránh dùng mảng được lưu trữ và giữ thao tác hoàn toàn trong bộ nhớ, bạn có thể thay thế nó bằng hằng số mảng khác: \=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)), hoặc =SUM({3\4\5\6\7}*{1\2\3\4\5}) Các thành phần bạn có thể dùng trong hằng số mảng
Một trong những cách tốt nhất để dùng hằng số mảng là đặt tên cho chúng. Các hằng số đã được đặt tên có thể dễ sử dụng hơn nhiều và chúng có thể ẩn bớt phần nào sự phức tạp trong công thức mảng để người khác không phải thấy. Để đặt tên cho hằng số mảng và dùng nó trong công thức, bạn hãy làm như sau: Đi tới Công thức > Tên đã xxác định > Xác định tên. Trong hộp Tên, hãy gõ Quarter1. Trong hộp Tham chiếu đến, bạn hãy nhập hằng số sau đây (hãy nhớ nhập dấu ngoặc nhọn theo cách thủ công): \={"Tháng Một"\"Tháng Hai"\"Tháng Ba"} Hộp thoại giờ sẽ trông giống như thế này: Bấm OK, rồi chọn hàng bất kỳ có ba ô trống và nhập =Quarter1. Kết quả sau đây được hiển thị: Nếu muốn kết quả tràn theo chiều dọc thay vì theo chiều ngang, bạn có thể dùng =TRANSPOSE(Quarter1). Nếu bạn muốn hiển thị danh sách 12 tháng, giống như bạn có thể sử dụng khi xây dựng báo cáo tài chính, bạn có thể tạo cơ sở cho một năm hiện tại bằng hàm SEQUENCE. Điểm khác biệt về hàm này là ngay cả khi chỉ hiển thị tháng, vẫn có ngày hợp lệ mà bạn có thể sử dụng trong các phép tính khác. Bạn sẽ tìm thấy các ví dụ này trên Hằng số mảng đã đặt tên và các trang tính Tập dữ liệu mẫu nhanh trong sổ làm việc ví dụ. \=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"mmm") Công thức này dùng hàm DATE để tạo ngày dựa trên năm hiện tại, SEQUENCE tạo hằng số mảng từ 1 đến 12 cho tháng 1 tới tháng 12, sau đó hàm TEXT chuyển đổi định dạng hiển thị thành "mmm" (tháng 1, tháng 2, tháng 3, v.v.). Nếu bạn muốn hiển thị tên tháng đầy đủ, chẳng hạn như Tháng Một, bạn có thể sử dụng "mmmm". Khi bạn dùng một hằng số đã đặt tên làm công thức mảng, hãy nhớ nhập dấu bằng, như trong =Quarter1, chứ không chỉ Quarter1. Nếu bạn không làm vậy, Excel sẽ hiểu mảng này là chuỗi văn bản và công thức của bạn sẽ không hoạt động như mong đợi. Cuối cùng, hãy nhớ rằng bạn có thể dùng kết hợp cả hàm, văn bản và số. Tất cả những việc này tùy thuộc vào mức độ sáng tạo mà bạn muốn có. Các ví dụ sau đây thể hiện một vài cách sử dụng hằng số mảng trong công thức mảng. Một số ví dụ dùng hàm TRANSPOSE để chuyển đổi hàng thành cột và ngược lại.
Phần này cung cấp ví dụ về công thức mảng cơ bản.
Bạn có thể cần tính tổng các giá trị theo các điều kiện. Ví dụ, công thức mảng này chỉ tỉnh tổng các số nguyên dương trong phạm vi có tên là Sales, đại diện cho các ô E9: E24 trong ví dụ trên: \=SUM(IF(Sales>0,Sales)) Hàm IF tạo ra mảng gồm các trị dương và false. Về cơ bản, hàm SUM bỏ qua các giá trị false vì 0+0=0. Phạm vi ô bạn dùng trong công thức này có thể chứa bất kỳ số hàng và cột nào. Bạn cũng có thể tính tổng các giá trị đáp ứng nhiều điều kiện. Ví dụ: công thức mảng này tính toán các giá trị lớn hơn 0 AND nhỏ hơn 2500: \=SUM((Sales>0)*(Sales<2500)*(Sales)) Hãy nhớ rằng công thức này trả về lỗi nếu phạm vi đó chứa một hoặc nhiều ô không phải dạng số. Bạn cũng có thể tạo công thức mảng dùng kiểu điều kiện OR. Ví dụ: bạn có thể tính tổng các giá trị lớn hơn 0 OR nhỏ hơn 2500: \=SUM(IF((Sales>0)+(Sales<2500),Sales)) Bạn không dùng được hàm AND và OR trực tiếp trong công thức mảng vì các hàm đó trả về một kết quả duy nhất, TRUE hoặc FALSE, còn hàm mảng đòi hỏi mảng kết quả. Bạn có thể giải quyết vấn đề này bằng cách dùng lô-gic được trình bày trong công thức trên đây. Nói cách khác, bạn thực hiện các phép toán, chẳng hạn như phép cộng hoặc phép nhân đối với các giá trị đáp ứng điều kiện OR hoặc AND. Ví dụ này cho bạn thấy cách loại bỏ số không khỏi phạm vi khi bạn cần tính trung bình các giá trị trong phạm vi đó. Công thức này dùng phạm vi dữ liệu có tên là Sales: \=AVERAGE(IF(Sales<>0,Sales)) Hàm IF tạo ra mảng chứa các giá trị khác 0 rồi chuyển các giá trị này đến hàm AVERAGE. Công thức mảng này so sánh các giá trị trong hai phạm vi ô có tên là MyData và YourData và trả về số lượng các khác biệt giữa hai phạm vi này. Nếu nội dung của hai phạm vi này giống hệt nhau, công thức trả về 0. Để sử dụng công thức này, các dải ô phải có cùng kích cỡ và có cùng kích thước. Ví dụ: nếu MyData là phạm vi gồm 3 hàng và 5 cột thì Dữ liệu của Bạn cũng phải là 3 hàng và 5 cột: \=SUM(IF(MyData=YourData,0,1)) Công thức này tạo ra mảng mới có cùng kích cỡ với các phạm vi bạn đang so sánh. Hàm IF điền vào mảng với giá trị 0 và 1 (0 cho các ô không khớp và 1 cho các ô giống nhau). Sau đó, hàm SUM trả về tổng giá trị trong mảng. Bạn có thể đơn giản hóa công thức này như sau: \=SUM(1*(MyData<>YourData)) Giống như công thức đếm số giá trị lỗi trong phạm vi, công thức này hoạt động vì TRUE*1=1 và FALSE*1=0. Công thức mảng này trả về số hàng của giá trị tối đa trong phạm vi đơn cột có tên là Data: \=MIN(IF(Data=MAX(Data),ROW(Data),"")) Hàm IF tạo ra một mảng mới tương ứng với phạm vi có tên là Data. Nếu một ô tương ứng chứa giá trị tối đa trong phạm vi đó, thì mảng sẽ chứa số hàng. Nếu không, mảng chứa chuỗi trống (""). Hàm MIN dùng mảng mới làm đối số thứ hai của mình và trả về giá trị nhỏ nhất tương ứng với số hàng của giá trị tối đa trong phạm vi Data. Nếu phạm vi có tên là Data chứa nhiều giá trị tối đa giống hệt nhau, thì công thức trả về hàng chứa giá trị đầu tiên. Nếu bạn muốn trả về địa chỉ thực tế của ô chứa giá trị tối đa, hãy dùng công thức này: \=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data)) Bạn sẽ tìm thấy các ví dụ tương tự trong sổ làm việc mẫu trên trang tính Khác nhau giữa các bộ dữ liệu. Bài tập này sẽ cho bạn thấy cách dùng các công thức mảng đa ô và đơn ô để tính toán bộ số liệu doanh số bán hàng. Bộ các bước đầu tiên dùng công thức đa ô để tính toán các tổng phụ. Bộ thứ hai dùng công thức đơn ô để tính toán tổng cộng.
Sao chép toàn bộ bảng bên dưới và dán nó vào ô A1 trong một trang tính trống. Người Bán hàng Loại Xe Số Đã bán Đơn Giá Tổng Doanh số Barnhill Sedan 5 33000 Coupe 4 37000 Ingle Sedan 6 24000 Coupe 8 21000 Jordan Sedan 3 29000 Coupe 1 31000 Pica Sedan 9 24000 Coupe 5 37000 Sanchez Sedan 6 33000 Coupe 8 31000 Công thức (Tổng Cộng) Tổng Cộng '=SUM(C2:C11*D2:D11) \=SUM(C2:C11*D2:D11)
Khi bạn nhấn Ctrl+Shift+Enter, Excel sẽ đưa công thức vào trong một dấu cắp ({ }) và chèn một bản sao công thức vào từng ô của dải ô đã chọn. Điều này xảy ra rất nhanh, vì vậy bạn sẽ thấy cột E chứa tổng doanh thu bán hàng cho mỗi kiểu xe cho mỗi nhân viên bán hàng. Nếu bạn chọn E2, rồi chọn E3, E4, v.v. thì bạn sẽ nhìn thấy cùng một công thức được hiển thị: {=C2:C11*D2:D11}.
Trong ô D13 của sổ làm việc, hãy nhập công thức mảng, rồi nhấn Ctrl+Shift+Enter: \=SUM(C2:C11*D2:D11) Trong trường hợp này, Excel nhân các giá trị trong mảng (phạm vi ô từ C2 đến hết D11), rồi dùng hàm SUM để cộng các tổng số với nhau. Kết quả tổng cộng doanh số bán hàng là $1.590.000. Ví dụ này cho thấy kiểu công thức này có hiệu quả tới mức nào. Ví dụ, giả sử bạn có 1.000 hàng dữ liệu. Bạn có thể cộng một phần hoặc tất cả các dữ liệu đó với nhau bằng cách tạo công thức mảng trong một ô duy nhất thay vì kéo công thức xuống qua hết 1.000 hàng. Ngoài ra, hãy lưu ý rằng công thức đơn ô trong ô D13 hoàn toàn độc lập với công thức đa ô (công thức trong các ô từ E2 đến E11). Đây là một ưu điểm khác của việc dùng công thức mảng — tính linh hoạt. Bạn có thể thay đổi các công thức trong cột E hoặc xóa bỏ hoàn toàn cột đó mà không ảnh hưởng đến công thức trong ô D13. Công thức mảng còn có các ưu điểm sau đây:
Nhìn chung, công thức mảng dùng cú pháp công thức chuẩn. Tất cả các công thức này đều bắt đầu với dấu bằng (=) và bạn có thể dùng hầu hết các hàm Excel dựng sẵn trong công thức mảng của mình. Sự khác biệt chính là ở chỗ khi dùng công thức mảng, bạn nhấn Ctrl+Shift+Enter để nhập công thức. Khi bạn làm điều này, Excel đặt công thức mảng của bạn trong một cặp dấu ngoặc nhọn — nếu bạn nhập dấu ngoặc nhọn theo cách thủ công, thì công thức của bạn sẽ được chuyển thành chuỗi văn bản và nó sẽ không hoạt động. Hàm mảng có thể là một cách hiệu quả để xây dựng công thức phức tạp. Công thức mảng =SUM(C2:C11*D2:D11) giống như công thức sau đây: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11). Quan trọng: Nhấn Ctrl+Shift+Enter bất cứ khi nào bạn cần nhập một 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 làm việc với công thức đa ô, bạn cũng hãy nhớ rằng:
Đôi khi bạn có thể cần mở rộng công thức mảng. Chọn ô đầu tiên trong dải ô mảng hiện có và tiếp tục cho đến khi đã chọn được toàn bộ phạm vi mà bạn muốn mở rộng công thức. Nhấn F2 để chỉnh sửa công thức, rồi 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. Khóa 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. Ô phía trên cùng bên trái là ô được chỉnh sửa. Công thức mảng rất tuyệt vời nhưng chúng có thể có một vài hạn chế:
Hằng số mảng là một thành phần của công thức mảng. Bạn tạo hằng số mảng bằng cách nhập danh sách các mục, sau đó đặt danh sách này trong một cặp dấu ngoặc nhọn ({ }) theo cách thủ công, như thế này: \={1\2\3\4\5} Đến bây giờ, bạn đã biết rằng mình cần nhấn Ctrl+Shift+Enter khi tạo công thức mảng. Vì hằng số mảng là thành phần của công thức mảng, bạn hãy đặt hằng số mảng trong cặp dấu ngoặc nhọn theo cách thủ công bằng cách gõ nhập các dấu ngoặc nhọn đó. Sau đó bạn dùng Ctrl+Shift+Enter để nhập toàn bộ công thức. Nếu bạn phân cách các mục bằng dấu phẩy, bạn sẽ tạo ra một mảng ngang (hàng). Nếu bạn phân cách các mục bằng dấu chấm phẩy, bạn sẽ tạo ra một mảng dọc (cột). Để tạo mảng hai chiều, bạn hãy phân cách các mục trong mỗi hàng bằng dấu phẩy và phân cách các hàng bằng dấu chấm phẩy. Đây là mảng trong một hàng: {1\2\3\4}. Đây là mảng trong một cột: {1;2;3;4}. Và đây là 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 thứ nhất 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 phân cách hai hàng, giữa 4 và 5. Giống như với công thức mảng, bạn có thể dùng hằng số mảng với hầu hết các hàm dựng sẵn mà Excel cung cấp. Phần sau đây giải thích cách tạo mỗi loại hằng số và cách dùng các hằng số này với các hàm trong Excel. Quy trình sau đây sẽ giúp bạn thực hành việc tạo các hằng số ngang, dọc và hai chiều. Tạo hằng số ngang
Tạo hằng số dọc
Tạo hằng số hai chiều
Dùng hằng số trong công thức Đây là một ví dụ đơn giản có sử dụng hằng số:
Phần tiếp theo giải thích cách hoạt động của công thức. Công thức mà bạn vừa dùng có một vài phần. 1. Hàm 2. Mảng đã lưu trữ 3. Toán tử 4. Hằng số mảng Thành phần cuối cùng trong ngoặc đơn là hằng số mảng: {1\2\3\4\5}. Hãy nhớ rằng Excel không đặt các hằng số mảng trong cặp dấu ngoặc nhọn; mà thực ra là bạn đã nhập các dấu ngoặc nhọn đó. Ngoài ra, hãy nhớ rằng sau khi thêm hằng số vào công thức mảng, bạn hãy nhấn Ctrl+Shift+Enter để nhập công thức. Vì Excel thực hiện các thao tác trên biểu thức được đặt trong dấu ngoặc đơn trước tiên, hai thành phần tiếp theo bắt đầu hoạt động là các giá trị được lưu trữ trong sổ làm việc (A1:E1) và toán tử. Tại thời điểm này, công thức nhân các giá trị trong mảng được lưu trữ với các giá trị tương ứng trong hằng số. Nó tương đương với: \=SUM(A1*1,B1*2,C1*3,D1*4,E1*5) Cuối cùng, hàm SUM cộng các giá trị với nhau và tổng cộng 85 xuất hiện trong ô A3. Để tránh dùng mảng được lưu trữ và chỉ giữ thao tác hoàn toàn trong bộ nhớ, hãy thay thế mảng được lưu trữ bằng hằng số mảng khác: \=SUM({3\4\5\6\7}*{1\2\3\4\5}) Để thử làm đ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ả giống như khi bạn thực hiện trong bài tập trước dùng công thức mảng: \=SUM(A1:E1*{1\2\3\4\5}) Hằng số mảng có thể chứa số, văn bản, giá trị lô-gic (chẳng hạn như TRUE và FALSE) và giá trị lỗi (chẳng hạn như N/A). Bạn có thể dùng số ở các định dạng số nguyên, thập phân và khoa học. Nếu bạn đưa văn bản vào hằng số mảng, bạn cần đặt văn bản trong dấu ngoặc kép (").Hằng số mảng không được chứa thêm các mảng, công thức hoặc các hàm khác. Nói cách khác, chúng chỉ có thể chứa văn bản hoặc số được phân cách bằng dấu phẩy hoặc dấu chấm phẩy. Excel sẽ hiển thị thông báo cảnh báo khi bạn nhập một công thức như {1\2\A1:D4} hoặc {1\2\SUM(Q2:Z8)}. Ngoài ra, giá trị số không được chứa dấu phần trăm, dấu đô-la, dấu phẩy hoặc dấu ngoặc đơn. Một trong những cách tốt nhất để dùng hằng số mảng là đặt tên cho chúng. Các hằng số đã được đặt tên có thể dễ sử dụng hơn nhiều và chúng có thể ẩn bớt phần nào sự phức tạp trong công thức mảng để người khác không phải thấy. Để đặt tên cho hằng số mảng và dùng nó trong công thức, bạn hãy làm như sau:
Khi bạn dùng một hằng số đã đặt tên làm công thức mảng, hãy nhớ nhập dấu bằng. Nếu bạn không làm vậy, Excel sẽ hiểu mảng này là chuỗi văn bản và công thức của bạn sẽ không hoạt động như mong đợi. Cuối cùng, hãy nhớ rằng bạn có thể dùng kết hợp cả văn bản và số. Hãy tìm các vấn đề sau đây khi hằng số mảng của bạn không hoạt động:
Các ví dụ sau đây thể hiện một vài cách sử dụng hằng số mảng trong công thức mảng. Một số ví dụ dùng hàm TRANSPOSE để chuyển đổi hàng thành cột và ngược lại. Nhân từng mục trong mảng
Lấy bình phương các mục trong mảng
Hoán vị hàng một chiều
Hoán vị cột một chiều
Ngay cả khi bạn nhập hằng số mảng dọc, hàm TRANSPOSE sẽ chuyển đổi hằng số mảng thành hàng. Hoán vị hằng số hai chiều
Phần này cung cấp ví dụ về công thức mảng cơ bản. Tạo mảng và hằng số mảng từ các giá trị hiện có Ví dụ sau đây giải thích cách dùng công thức mảng để tạo nối kết giữa các phạm vi ô trong các trang tính khác nhau. Nó cũng cho bạn thấy cách tạo hằng số mảng từ cùng một bộ giá trị. Tạo mảng từ các giá trị hiện có
Tạo hằng số mảng từ các giá trị hiện có
Đếm số ký tự trong phạm vi ô Ví dụ sau đây cho bạn thấy cách đếm số ký tự, gồm cả khoảng trắng, trong một phạm vi ô.
Công thức sau đây trong ô A8 sẽ đế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 từng chuỗi văn bản trong mỗi ô trong phạm vi đó. Sau đó, hàm SUM cộng các giá trị đó với nhau và hiển thị kết quả (66) trong ô chứa công thức, A9. Tìm n giá trị nhỏ nhất trong một phạm vi Ví dụ này cho thấy cách tìm ba giá trị nhỏ nhất trong một phạm vi ô.
Công thức này dùng hằng số mảng để đánh giá hàm SMALL ba lần và trả về phần tử nhỏ nhất (1), nhỏ thứ hai (2) và nhỏ thứ ba (3) trong mảng chứa trong các ô A1:A10. Để tìm nhiều giá trị hơn, bạn hãy thêm các đối số vào hằng số. Bạn cũng có thể dùng thêm các hàm khác trong công thức này, chẳng hạn như hàm SUM hay AVERAGE. Ví dụ: \=SUM(SMALL(A1:A10,{1\2\3}) \=AVERAGE(SMALL(A1:A10,{1\2\3}) Tìm n giá trị lớn nhất trong một phạm vi Để tìm giá trị lớn nhất trong một phạm vi, bạn có thể thay thế hàm SMALL bằng hàm LARGE. Ngoài ra, ví dụ sau đây dùng các hàm ROW và INDIRECT.
Lúc này, hiểu biết một chút về hàm ROW và INDIRECT có thể hữu ích. Bạn có thể dùng hàm ROW để tạo một mảng gồm các số nguyên liên tiếp. Ví dụ, hãy chọn một cột trống gồm 10 ô trong sổ làm việc thực tế 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 này tạo ra một cột gồm 10 số nguyên liên tiếp. Để xem vấn đề tiềm ẩn, hãy chèn một hàng bên trên phạm vi chứa công thức mảng (tức là, trên hàng 1). Excel sẽ điều chỉnh tham chiếu hàng và công thức sẽ tạo ra các số nguyên từ 2 đến 11. Để khắc phục vấn đề đó, bạn hãy thêm hàm INDIRECT vào công thức: \=ROW(INDIRECT("1:10")) Hàm INDIRECT dùng chuỗi văn bản làm đối số của nó (đó là lý do tại sao phạm vi 1:10 được đặt trong dấu ngoặc kép). Excel không điều chỉnh giá trị văn bản khi bạn chèn hàng hay di chuyển công thức mảng theo cách khác. Do đó, hàm ROW sẽ luôn luôn tạo ra mảng số nguyên mà bạn muốn. Chúng ta hãy nhìn vào công thức mà bạn dùng ở trước — =LARGE(A5:A14,ROW(INDIRECT("1:3"))) — bắt đầu từ cặp dấu ngoặc đơn bên trong và tiến hành hướng ra ngoài: Hàm INDIRECT trả về một bộ các giá trị văn bản, trong trường hợp này là các giá trị từ 1 đến 3. Đến lượt mình, hàm ROW tạo ra mảng cột gồm ba ô. Hàm LARGE dùng các giá trị trong phạm vi ô A5:A14 và nó được đánh giá ba lần, một lần cho mỗi tham chiếu mà hàm ROW trả về. Các giá trị 3200, 2700 và 2000 được trả về cho mảng cột gồm ba ô đó. Nếu muốn tìm thêm giá trị, bạn hãy thêm một phạm vi ô lớn hơn vào hàm INDIRECT. Như với các ví dụ trước, bạn có thể dùng công thức này với các hàm khác, chẳng hạn như hàm SUM và AVERAGE. Tìm chuỗi văn bản dài nhất trong phạm vi ô Quay lại ví dụ chuỗi văn bản trước đó, nhập công thức sau đây vào một ô trống và nhấn Ctrl+Shift+Enter: \=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1) Văn bản "bunch of cells that" sẽ xuất hiện. Chúng ta hãy xem xét kỹ hơn công thức này, bắt đầu tư các thành phần bên trong và hướng ra bên ngoài. Hàm LEN trả về độ dài của từng mục trong phạm vi ô A2:A6. Hàm MAX tính giá trị lớn nhất trong số các mục đó, tương ứng với chuỗi văn bản dài nhất, ở trong ô A3. Đây là nơi mọi thứ trở nên phức tạp một chút. Hàm MATCH tính toán khoảng chừa trống (vị trí tương đối) của ô chứa chuỗi văn bản dài nhất. Để làm điều đó, nó đòi hỏi ba đối số: giá trị tra cứu, mảng tra cứu và kiểu khớp. Hàm MATCH tìm kiếm giá trị tra cứu đã xác định trong mảng tra cứu. Trong trường hợp này, giá trị tra cứu là chuỗi văn bản dài nhất: (MAX(LEN(A2:A6)) và chuỗi đó nằm trong mảng này: LEN(A2:A6) Đối số kiểu khớp là 0. Kiểu khớp có thể chứa giá trị 1, 0 hoặc -1. Nếu bạn xác định kiểu khớp là 1, hàm MATCH trả về giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu. Nếu bạn xác định kiểu khớp là 0, hàm MATCH trả về giá trị đầu tiên chính xác bằng với giá trị tra cứu. Nếu bạn xác định kiểu khớp là -1, hàm MATCH tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tra cứu đã xác định. Nếu bạn bỏ qua kiểu khớp, Excel giả định nó bằng 1. Cuối cùng, hàm INDEX lấy các đối số này: mảng và 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ỉ rõ rằng giá trị đến từ cột đầu tiên trong mảng. Phần này cung cấp ví dụ về công thức mảng nâng cao. Cộng tổng một phạm vi chứa các giá trị lỗi Hàm SUM trong Excel không hoạt động khi bạn tìm cách cộng tổng một phạm vi chứa giá trị lỗi, chẳng hạn như N/A. Ví dụ này cho bạn thấy cách cộng các giá trị trong phạm vi tên là Data, trong đó có chứa lỗi.\=SUM(IF(ISERROR(Data),"",Data)) Công thức này tạo ra một mảng mới, trong đó chứa các giá trị ban đầu nhưng loại trừ mọi giá trị lỗi. Bắt đầu từ hàm bên trong và làm việc hướng ra bên ngoài, hàm ISERROR tìm kiếm các lỗi trong phạm vi ô (Data). Hàm IF trả về một giá trị cụ thể nếu điều kiện mà bạn xác định định trị là TRUE, trả về giá trị khác nếu nó định trị là FALSE. Trong trường hợp này, nó trả về chuỗi trống ("") cho tất cả các giá trị lỗi vì chúng định trị là TRUE, nó trả về các giá trị còn lại từ phạm vi (Data) vì chúng định trị là FALSE, có nghĩa là chúng không chứa giá trị lỗi. Sau đó, hàm SUM tính tổng cộng cho mảng đã lọc. Đếm số giá trị lỗi trong phạm vi Ví dụ này tương tự như công thức ở trước, chỉ khác ở chỗ nó trả về số giá trị lỗi trong phạm vi có tên là Data thay vì lọc bỏ các giá trị lỗi đó: \=SUM(IF(ISERROR(Data),1,0)) Công thức này tạo ra một mảng chứa giá trị 1 cho các ô chứa lỗi và giá trị 0 cho các ô không chứa lỗi. Bạn có thể đơn giản hóa công thức này và thu được cùng một kết quả bằng cách loại bỏ đối số thứ ba cho hàm IF, giống như thế này: \=SUM(IF(ISERROR(Data),1)) Nếu bạn không chỉ rõ đối số, hàm IF trả về FALSE nếu ô không chứa giá trị lỗi. Thậm chí bạn có thể đơn giản hóa công thức hơn nữa: \=SUM(IF(ISERROR(Data)*1)) Phiên bản công thức này hoạt động vì TRUE*1=1 và FALSE*1=0. Tính tổng các giá trị theo các điều kiện Bạn có thể cần tính tổng các giá trị theo các điều kiện. Ví dụ, công thức mảng này chỉ tỉnh tổng các số nguyên dương trong phạm vi có tên là Sales: \=SUM(IF(Sales>0,Sales)) Hàm IF tạo ra mảng gồm các trị dương và giá trị false. Về cơ bản, hàm SUM bỏ qua các giá trị false vì 0+0=0. Phạm vi ô bạn dùng trong công thức này có thể chứa bất kỳ số hàng và cột nào. Bạn cũng có thể tính tổng các giá trị đáp ứng nhiều điều kiện. Ví dụ, công thức mảng này tính toán những giá trị lớn hơn 0 và nhỏ hơn hoặc bằng 5: \=SUM((Sales>0)*(Sales<=5)*(Sales)) Hãy nhớ rằng công thức này trả về lỗi nếu phạm vi đó chứa một hoặc nhiều ô không phải dạng số. Bạn cũng có thể tạo công thức mảng dùng kiểu điều kiện OR. Ví dụ, bạn có thể tính tổng các giá trị nhỏ hơn 5 và các giá trị lớn hơn 15: \=SUM(IF((Sales<5)+(Sales>15),Sales)) Hàm IF tìm tất cả các giá trị nhỏ hơn 5 và các giá trị lớn hơn 15, rồi chuyển các giá trị đó đến hàm SUM. Bạn không dùng được hàm AND và OR trực tiếp trong công thức mảng vì các hàm đó trả về một kết quả duy nhất, TRUE hoặc FALSE, còn hàm mảng đòi hỏi mảng kết quả. Bạn có thể giải quyết vấn đề này bằng cách dùng lô-gic được trình bày trong công thức trên đây. Nói cách khác, bạn thực hiện các phép toán, chẳng hạn như phép cộng hoặc phép nhân, đối với các giá trị đáp ứng điều kiện OR hoặc AND. Tính giá trị trung bình loại trừ số không Ví dụ này cho bạn thấy cách loại bỏ số không khỏi phạm vi khi bạn cần tính trung bình các giá trị trong phạm vi đó. Công thức này dùng phạm vi dữ liệu có tên là Sales: \=AVERAGE(IF(Sales<>0,Sales)) Hàm IF tạo ra mảng chứa các giá trị khác 0 rồi chuyển các giá trị này đến hàm AVERAGE. Đếm số lượng các khác biệt giữa hai phạm vi ô Công thức mảng này so sánh các giá trị trong hai phạm vi ô có tên là MyData và YourData và trả về số lượng các khác biệt giữa hai phạm vi này. Nếu nội dung của hai phạm vi này giống hệt nhau, công thức trả về 0. Để dùng công thức này, các phạm vi ô cần có cùng kích cỡ và kích thước (ví dụ, nếu phạm vi ô MyData có 3 hàng 5 cột, thì phạm vi ô YourData cũng phải có 3 hàng 5 cột): \=SUM(IF(MyData=YourData,0,1)) Công thức này tạo ra mảng mới có cùng kích cỡ với các phạm vi bạn đang so sánh. Hàm IF điền vào mảng với giá trị 0 và 1 (0 cho các ô không khớp và 1 cho các ô giống nhau). Sau đó, hàm SUM trả về tổng giá trị trong mảng. Bạn có thể đơn giản hóa công thức này như sau: \=SUM(1*(MyData<>YourData)) Giống như công thức đếm số giá trị lỗi trong phạm vi, công thức này hoạt động vì TRUE*1=1 và FALSE*1=0. Tìm vị trí của giá trị tối đa trong phạm vi Công thức mảng này trả về số hàng của giá trị tối đa trong phạm vi đơn cột có tên là Data: \=MIN(IF(Data=MAX(Data),ROW(Data),"")) Hàm IF tạo ra một mảng mới tương ứng với phạm vi có tên là Data. Nếu một ô tương ứng chứa giá trị tối đa trong phạm vi đó, thì mảng sẽ chứa số hàng. Nếu không, mảng chứa chuỗi trống (""). Hàm MIN dùng mảng mới làm đối số thứ hai của mình và trả về giá trị nhỏ nhất tương ứng với số hàng của giá trị tối đa trong phạm vi Data. Nếu phạm vi có tên là Data chứa nhiều giá trị tối đa giống hệt nhau, thì công thức trả về hàng chứa giá trị đầu tiên. Nếu bạn muốn trả về địa chỉ thực tế của ô chứa giá trị tối đa, hãy dùng công thức này: \=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data)) Xác nhậnCác phần của bài viết này dựa trên chuỗi bài viết Người dùng Excel Toàn năng, được viết bởi Colin Wilcox và được chuyển thể từ chương 14 và 15 của cuốn sách Các công thức Excel 2002, được viết bởi John Walkenbach, một cựu MVP về Excel. |