Việc chuyển một vài công thức trong trang tính của bạn thành công thức mảng có thể làm cho trang tính của bạn hiệu quả hơn, đồng thời đơn giản hóa trang tính. Một công thức được dùng làm công thức mảng có thể thực hiện tính toán trên các hàng và cột gồm nhiều ô, mà nếu không dùng công thức mảng thì có thể bạn phải dùng nhiều công thức.
Tìm hiểu về công thức mảng
Công thức mảng thường được gọi là công thức CSE (Ctrl+Shift+Enter), bởi thay vì chỉ nhấn Enter, bạn cần nhấn Ctrl+Shift+Enter để hoàn tất công thức.
Tại sao dùng công thức mảng?
Nếu bạn có kinh nghiệm dùng công thức trong Excel, bạn biết rằng bạn có thể thực hiện một số thao tác khá phức tạp. Ví dụ, bạn có thể tính toán tổng chi phí cho một khoản vay trong bất kỳ số năm đã biết nào. 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ư:
- Đếm số ký tự chứa trong một phạm vi ô.
- Chỉ tính tổng các số đáp ứng một số điều kiện nhất định, chẳng hạn như các giá trị thấp nhất trong một phạm vi hoặc các số nằm giữa một giới hạn trên và giới hạn dưới nào đó.
- Tính tổng mọi giá trị thứ n trong phạm vi giá trị.
Giới thiệu ngắn gọn về mảng và công thức mảng
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ể tưởng tượng mảng là một hàng giá trị, một cột giá trị hoặc 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. Ví dụ, bạn có thể tạo công thức mảng trong một phạm vi ô và dùng công thức mảng đó để tính toán cột hay hàng tổng phụ. Bạn cũng có thể đặt công thức mảng vào một ô đơn lẻ, rồi tính toán một con số đơn lẻ. Công thức mảng bao gồm nhiều ô được gọi là công thức đa ô và công thức mảng trong một ô duy nhất được gọi là công thức đơn ô.
Các ví dụ trong phần tiếp theo sẽ cho bạn thấy cách tạo các công thức mảng đa ô và đơn ô.
Hãy thử ngay!
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.
Tạo công thức mảng đa ô
Đây là sổ làm việc được nhúng vào trình duyệt. Mặc dù sổ này chứa dữ liệu mẫu, bạn cần biết rằng bạn sẽ không tạo hoặc thay đổi được công thức mảng trong sổ làm việc nhúng – bạn cần chương trình Excel. Bạn có thể thấy các câu trả lời trong sổ làm việc nhúng và nội dung giải thích cách hoạt động của công thức mảng nhưng để thực sự đánh giá về công thức mảng, bạn sẽ cần xem sổ làm việc trong Excel.
Sao chép toàn bộ bảng bên dưới và dán nó vào trong ô A1 trong một trang tính trống trong Excel. Sau đó làm theo các hướng dẫn bên dưới bảng.
Để xem Tổng Doanh thu của các kiểu xe coupe và sedan cho từng nhân viên bán hàng, hãy chọn E2:E11, nhập công thức =C2:C11*D2:D11 và nhấn Ctrl+Shift+Enter.
|
Nhân viên
Bán hàng |
Kiểu
Xe |
Số lượng
Bán |
Giá
Đơn vị |
Tổng
Doanh thu |
|
Barnhill
|
Sedan
|
5
|
33000
|
|
|
Coupe
|
4
|
37000
|
||
|
Ingle
|
Sedan
|
6
|
24000
|
|
|
Coupe
|
8
|
$21.000
|
||
|
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)
|
|||
Đối với Tổng Cộng tất cả doanh thu, hãy chọn ô F11, nhập công thức =SUM(C2:C11*D2:D11) và nhấn Ctrl+Shift+Enter.
Bạn có thể tải xuống sổ làm việc này bằng cách bấm nút Excel màu xanh lục trên thanh màu đen ở dưới cùng sổ làm việc. Sau đó bạn có thể mở tệp trong Excel, chọn ô chứa công thức mảng và nhấn Ctrl+Shift+Enter để làm cho công thức đó hoạt động.
Nếu bạn đang làm việc trong Excel, hãy bảo đảm Sheet1 đang hoạt động, rồi chọn các ô E2:E11. Nhấn F2 và nhập công thức =C2:C11*D2:D11 vào ô hiện tại, E2. Nếu bạn nhấn Enter, bạn sẽ thấy rằng nó chỉ nhập công thức vào ô E2 và hiển thị 165000. Thay vào đó, sau khi nhập công thức, bạn hãy nhấn Ctrl+Shift+Enter thay vì chỉ nhấn Enter. Bây giờ bạn sẽ nhìn thấy kết quả trong các ô E2:E11. Và hãy lưu ý rằng trong thanh công thức, công thức này xuất hiện ở dạng {=C2:C11*D2:D11}. Điều đó cho bạn biết rằng đó là công thức mảng. Điều này được thể hiện dưới đây.
Khi bạn nhấn Ctrl+Shift+Enter, Excel sẽ đặt công thức trong cặp dấu ngoặc nhọn ({ }) và chèn công thức đó vào mỗi ô của phạm vi được 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 số 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}.
Tạo công thức mảng đơn ô
Trong ô F10 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 ô G11 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 ô G11.
Công thức mảng còn có các ưu điểm sau đây:
- Nhất quán Nếu bạn bấm bất kỳ ô nào từ E2 trở xuống, bạn sẽ nhìn thấy cùng một công thức. Sự nhất quán đó có thể giúp đảm bảo độ chính xác lớn hơn.
- An toàn Bạn không thể ghi đè thành phần của một công thức mảng đa ô. Ví dụ, hãy bấm ô 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 giữ 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 việc thay đổi công thức.
- Kích cỡ tệp nhỏ hơn Bạn có thể thường xuyên dùng một công thức mảng duy nhất thay vì nhiều công thức trung gian. Ví dụ, sổ làm việc dùng một công thức mảng để tính toán kết quả ở cột E. Nếu bạn dùng công thức chuẩn (chẳng hạn như =C2*D2, C3*D3, C4*D4…) thì bạn phải dùng 11 công thức khác nhau để tính toán cùng kết quả đó.
Xem xét cú pháp công thức mảng
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.
Các hàm mảng là một cách thực sự 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).
=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).
Điều quan trọng về việc nhập và thay đổi công thức mảng
Hãy nhấn Ctrl+Shift+Enter bất cứ khi nào bạn cần nhập hay sửa 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:
- Hãy chọn phạm vi ô để chứa kết quả của bạn trước khi nhập công thức. Bạn đã làm điều này khi tạo công thức mảng đa ô, bằng cách chọn từ E2 đến hết E11.
- Bạn không thể thay đổi nội dung của một ô đơn 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 sẽ hiển thị thông báo cho biết 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 bỏ toàn bộ một công thức mảng nhưng bạn không thể di chuyển hoặc xóa bỏ 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 phải xóa bỏ công thức hiện có và làm lại từ đầu.
- Để xóa bỏ công thức mảng, hãy chọn toàn bộ công thức (ví dụ, =C2:C11*D2:D11), nhấn Delete, rồi nhấn Ctrl+Shift+Enter.
- Bạn không thể chèn thêm ô trống hoặc xóa bỏ ô ra khỏi công thức mảng đa ô.
Mở rộng công thức mảng
Đôi khi bạn có thể cần mở rộng công thức mảng. Quá trình này không hề phức tạp nhưng hãy bảo đảm bạn làm theo hướng dẫn trên đây.
Trên trang tính này, chúng tôi đã thêm vào đó một vài hàng doanh số bán hàng, trong các hàng từ 12 đến 17. Ở đây, chúng tôi muốn cập nhật công thức mảng để các công thức đó chứa các hàng bổ sung này.
Đảm bảo thực hiện việc này trong chương trình Excel trên máy tính (sau khi bạn đã tải sổ làm việc về máy tính của mình).
Sao chép toàn bộ bảng này vào ô A1 trong bảng tính Excel rồi thực hiện quy trình bốn bước theo bảng bên dưới.
Hãy chọn ô E18, nhập công thức Tổng cộng =SUM(C2:C17*D2:D17) vào ô A20 và nhấn Ctrl+Shift+Enter. Câu trả lời sẽ là 2.131.000.
Nhân viên
Bán hàng |
Kiểu
Xe |
Số lượng
Bán |
Giá
Đơn vị |
Tổng
Doanh thu |
Barnhill
|
Sedan
|
5
|
33000
|
165000
|
Coupe
|
4
|
37000
|
148000
|
|
Ingle
|
Sedan
|
6
|
24000
|
144.000 $
|
Coupe
|
8
|
$21.000
|
168000
|
|
Jordan
|
Sedan
|
3
|
29000
|
87000
|
Coupe
|
{1}
|
31000
|
31000
|
|
Pica
|
Sedan
|
9
|
24000
|
216000
|
Coupe
|
5
|
37000
|
185000
|
|
Sanchez
|
Sedan
|
6
|
33000
|
198000
|
Coupe
|
8
|
31000
|
248000
|
|
Toth
|
Sedan
|
2
|
27000
|
|
Coupe
|
3
|
$30.000
|
||
Wang
|
Sedan
|
4
|
22000
|
|
Coupe
|
{1}
|
41000
|
||
Young
|
Sedan
|
5
|
32000
|
|
Coupe
|
3
|
36000
|
Tổng Cộng
|
- Hãy chọn phạm vi ô chứa công thức mảng hiện tại (E2:E11), cộng thêm các ô trống (E12:E17) bên cạnh dữ liệu mới. Nói cách khác, hãy chọn các ô E2:E17.
- Nhấn F2 để chuyển sang chế độ sửa.
- Trong thanh công thức, hãy thay đổi C11 thành C17, thay đổi D11 thành D17, rồi nhấn Ctrl+Shift+Enter. Excel sẽ cập nhật công thức trong các ô từ E2 đến E11 và đặt công thức đó vào từng ô mới này, từ ô E12 đến E17.
- Hãy gõ công thức mảng = SUM(C2:C17*D2*D17) trong ô F17 để nó tham chiếu đến các ô từ hàng 2 đến hàng 17, rồi nhấn Ctrl+Shift+Enter để nhập công thức mảng. Tổng cộng mới sẽ là 2.131.000.
Các điểm hạn chế của việc dùng công thức mảng
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ế:
- Đôi khi bạn có thể quên nhấn Ctrl+Shift+Enter. Điều này có thể xảy ra thậm chí với những người dùng Excel dạn dày kinh nghiệm. Hãy nhớ nhấn tổ hợp phím này bất cứ khi nào bạn nhập hay sửa công thức mảng.
- Những người khác dùng sổ làm việc của bạn có thể không hiểu công thức của bạn. Trên thực tế, công thức mảng nhìn chung không được giải thích trong trang tính, vì vậy nếu người khác cần sửa đổi sổ làm việc của bạn, thì bạn nên tránh dùng công thức mảng hoặc hãy bảo đảm rằng những người đó biết rõ mọi công thức mảng trong đó và biết cách thay đổi các công thức đó 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 số mảng
Phần này giới thiệu về hằng số mảng và giải thích cách nhập, sửa và khắc phục sự cố về hằng số mảng.
Giới thiệu ngắn gọn về hằng số 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 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 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à 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 nhấn 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 tạo ra 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 tạo ra 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.
Tạo hằng số một chiều và hai chiều
Quy trình sau đây sẽ giúp bạn thực hành tạo các hằng số ngang, dọc và hai chiều.
Tạo hằng số ngang
- Hãy dùng sổ làm việc từ các ví dụ trên đây hoặc tạo một sổ làm việc mới.
- Chọn các ô từ A1 đến E1.
- Trong thanh công thức, hãy nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:={1,2,3,4,5}Trong trường hợp này, bạn cần gõ các dấu ngoặc nhọn mở và đóng ({ }).
Kết quả sau đây được hiển thị.
Tạo hằng số dọc
- Trong sổ làm việc, hãy chọn một cột chứa năm ô.
- Trong thanh công thức, hãy nhập công thức sau đây và 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
- Trong sổ làm việc của bạn, hãy chọn một khối các ô rộng bốn cột và cao ba hàng.
- Trong thanh công thức, hãy nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:={1,2,3,4;5,6,7,8;9,10,11,12}
Bạn sẽ thấy kết quả sau đây:
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ố:
- Trong sổ làm việc mẫu, hãy tạo một trang tính mới .
- Trong ô A1, hãy nhập số 3, rồi nhập số 4 trong B1, 5 trong C1, 6 trong D1 và 7 trong E1.
- Trong ô A3, hãy nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:=SUM(A1:E1*{1,2,3,4,5})
Lưu ý rằng Excel đặt hằng số trong một cặp dấu ngoặc nhọn khác, vì bạn đã nhập nó ở 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.
Xem xét cú pháp hằng số mảng
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ên đây, trong đó dùng công thức mảng: =SUM(A1:E1*{1,2,3,4,5}).
Các thành phần bạn có thể dùng trong hằng số
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.
Đặt tên hằng số mảng
Có lẽ 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 một phần sự phức tạp trong công thức mảng để người khác không thấy sự phức tạp đó. Để đặ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:
- Trên tab Công thức, trong nhóm Tên Đã Xác định, hãy bấm Xác định Tên.
Hộp thoại Xác định Tên xuất hiệ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"}Nội dung của hộp thoại sẽ trông giống như thế này:
- Bấm OK, rồi chọn hàng gồm ba ô trống.
- Gõ công thức sau đây, rồi nhấn Ctrl+Shift+Enter.=Quarter1
Kết quả sau đây được hiển thị.
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ố.
Khắc phục sự cố về hằng số mảng
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ó thể một số thành phần không được phân cách bằng ký tự phù hợp. Nếu bạn bỏ qua dấu phẩy hay dấu chấm phẩy hoặc nếu bạn đặt dấu vào sai chỗ, hằng số mảng có thể không được tạo đúng cách hoặc bạn có thể thấy thông báo cảnh báo.
- Có thể bạn đã chọn phạm vi ô không khớp với số thành phần trong hằng số của mình. Ví dụ, nếu bạn chọn cột gồm sáu ô để dùng với hằng số năm ô, thì 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 các giá trị không có ô tương ứng.
Sử dụng hằng số mả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àmTRANSPOSE để chuyển đổi hàng thành cột và ngược lại.
Nhân từng mục trong mảng
- Tạo một trang tính mới và chọn một khối ô trống rộng bốn cột và cao ba hàng.
- Gõ công thức sau đây, rồi nhấn Ctrl+Shift+Enter.={1,2,3,4;5,6,7,8;9,10,11,12}*2
Lấy bình phương các mục trong mảng
- Hãy chọn một khối ô trống rộng bốn cột và cao ba hàng.
- Nhập công thức mảng sau đây, 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,12}Hoặc nhập công thức mảng này, công thức dùng toán tử mũ (^):={1,2,3,4;5,6,7,8;9,10,11,12}^2
Hoán vị hàng một chiều
- Hãy chọn một cột gồm năm ô trống.
- Nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:=TRANSPOSE({1,2,3,4,5})
Ngay cả khi bạn nhập hằng số mảng ngang, hàm TRANSPOSE sẽ chuyển đổi hằng số mảng thành cột.
Hoán vị cột một chiều
- Hãy chọn một hàng gồm năm ô trống.
- Nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:=TRANSPOSE({1;2;3;4;5})
Ngay cả khi bạn nhập hằng số mảng dọc, hàm TRANSPOSE sẽ chuyển đổi hằng số mảng thành hàng.
Hoán vị hằng số hai chiều
- Hãy chọn khối ô rộng ba cột và cao bốn hàng.
- Nhập hằng số sau đây và nhấn Ctrl+Shift+Enter.=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})
Hàm TRANSPOSE sẽ chuyển đổi mỗi hàng thành một chuỗi cột.
Làm cho công thức mảng cơ bản hoạt động
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ó
- Trên một trang tính trong Excel, hãy chọn các ô C8:E10 và nhập công thức sau:={10,20,30;40,50,60;70,80,90}Nhớ nhập { (dấu ngoặc nhọn mở) trước khi bạn nhập 10 và } (dấu ngoặc nhọn đóng) sau khi bạn nhập 90 vì bạn đang tạo ra một mảng các con số. Sau đó, nhấn Ctrl+Shift+Enter, thao tác này sẽ nhập mảng số này trong phạm vi ô C8:E10 bằng cách dùng một công thức mảng. Trên trang tính của bạn, C8 đến E10 phải trông giống như thế này:
10%20%=3/040%50%6070(80)9,0% - Bây giờ, hãy chọn phạm vi ô từ C1 đến E3.
- Nhập công thức sau đây vào thanh công thức, rồi nhấn Ctrl+Shift+Enter:=C8:E10
Một mảng các ô 3x3 sẽ xuất hiện trong các ô C1 đến E3 với các giá trị tương tự mà bạn đã nhìn thấy ở C8 đến E10.
Tạo hằng số mảng từ các giá trị hiện có
- Với các ô C1:C3 được chọn, hãy nhấn F2 để chuyển sang chế độ chỉnh sửa. Công thức mảng vẫn phải là = C8:E10.
- Nhấn F9 để chuyển đổi tham chiếu ô thành giá trị. Excel chuyển đổi giá trị thành hằng số mảng. Công thức đó giờ đây phải là ={10,20,30;40,50,60;70,80,90}, giống như C8:E10.
- Hãy nhấn Ctrl+Shift+Enter để nhập hằng số mảng ở dạng công thức mảng.
Đế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 ô.
Sao chép toàn bộ bảng này và dán vào trong một trang tính ở ô A1.
- Chọn ô A9 và nhấn Ctrl+Shift+Enter để xem tổng số lượng ký tự trong các ô A2:A6 (66).
- Chọn ô A12 và nhấn Ctrl+Shift+Enter để xem nội dung của phần dài nhất của các ô A2:A6 (ô A3).
Dữ liệuThis is abunch of cells thatcome togetherto form asingle sentence.Tổng các 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) - Công thức sau đây trong ô A9 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àmSUM 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 ô.
- Hãy chọn các ô từ A16 đến A18. Những ô này sẽ chứa các giá trị được công thức mảng trả về.
- Trong thanh công thức, hãy nhập công thức sau đây, rồi nhấn Ctrl+Shift+Enter:=SMALL(A5:A14,{1;2;3})
Các giá trị 400, 475 và 500 xuất hiện tương ứng trong các ô từ A16 đến A18.
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ố và số ô kết quả tương ứng vào phạm vi A12:A14. 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(A 5 :A1 4 ,{1;2;3}))
=AVERAGE(SMALL(A 5 :A1 4 ,{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.
- Chọn các ô từ A1 đến A3.
- Trong thanh công thức, hãy nhập công thức này, rồi nhấn Ctrl+Shift+Enter:=LARGE(A5:A14,ROW(INDIRECT("1:3")))
Các giá trị 3200, 2700 và 2000 xuất hiện tương ứng trong các ô từ A1 đến A3.
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 hành của bạn, nhập công thức mảng này vào các ô A5:A14, 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 kiểm tra 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.
Cuối cùng, 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 ô
Ví dụ này tìm chuỗi văn bản dài nhất trong một phạm vi ô. Công thức này chỉ hoạt động khi phạm vi dữ liệu chứa một cột ô duy nhất. Trên Sheet3, hãy nhập công thức sau đây vào ô A16 và nhấn Ctrl+Shift+Enter:
=INDEX(A6:A9,MATCH(MAX(LEN(A6:A9)),LEN(A6:A9),0),1)
Văn bản "bunch of cells that" sẽ xuất hiện trong ô A16.
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 LENtrả về độ dài của mỗi mục trong phạm vi ô A6:A9. Hàm MAX tính toán giá trị lớn nhất trong số các mục đó, mà giá trị đó tương ứng với chuỗi văn bản dài nhất, giá trị này nằm trong ô A7.
Đâ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àmMATCH 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( A6 : A9 ))
và chuỗi đó nằm trong mảng này:
LEN( A6:A9 )
Đố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 ô A6:A9 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.
Làm cho công thức mảng nâng cao hoạt độ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))
Nguồn: Sưu tầm.
0 comments :
Post a Comment