Bài viết: Làm việc với công thức mảng trong Excel

[BNTT]

Làm việc với các mảng

​

Khi bạn làm việc với một dãy các ô, dường như bạn đang làm việc với một thứ duy nhất. Trong thực tế, Excel xem dãy này như một "đơn vị số" riêng biệt.

Điều này hơi trái với chủ đề của phần này: mảng (array). Mảng là một nhóm ô hoặc giá trị mà Excel xem như là một đơn vị. Ví dụ, trong một dãy được cấu hình dưới dạng một mảng, Excel không còn xử lý các ô một cách riêng lẻ nữa. Thay vào đó, nó làm việc với tất cả các ô trong cùng một lúc, nghĩa là cho phép bạn làm những điều như áp dụng một công thức vào mỗi ô trong dãy chỉ với một thao tác.

Bạn có thể tạo các mảng bằng cách chạy một hàm mà kết quả trả về là một mảng (ví dụ như hàm LOGEST() hoặc bằng cách nhập một công thức mảng (array formula), là một công thức đơn nhưng sử dụng các mảng làm đối số hoặc nhập các kết quả của nó trong nhiều ô.
1. Sử dụng các công thức mảng

Sau đây là một ví dụ đơn giản minh họa công thức mảng làm việc như thế nào. Trong bảng tính Expenses ở hình 1, các ô tính tổng 2008 BUDGET tính toán bằng cách sử dụng từng công thức riêng biệt trong mỗi ô, như sau đây:
January 2008 BUDGET: =C11*$C$3
February 2008 BUDGET: =D11*$C$3
March 2008 BUDGET: =E11*$C$3

Bạn có thể thay thế cả 3 công thức ấy bằng một công thức mảng đơn giản, theo từng bước sau đây:

1. Chọn dãy ô mà bạn muốn dùng công thức mảng. Ví dụ trong bảng tính 2008 BUDGET, bạn chọn dãy ô C13:E13.
   
2. Gõ công thức, và ở những nơi mà bạn thường nhập một tham chiếu đến một ô, hãy nhập vào đó tham chiếu đến dãy bao gồm những ô mà bạn muốn dùng để tính toán. Đừng (tôi nhắc lại, đừng) nhấn Enter sau khi nhập xong công thức. Trong ví dụ này, bạn nhập công thức sau:
   = C11:E11*$C$3
   
3. Để nhập một công thức ở dạng một công thức mảng, nhấn Ctrl+Shift+Enter.
   
   Các ô 2008 BUDGET (C13, D13 và E13) bây giờ đều có chung một công thức:
   {=C11:E11*$C$3}

Hình 1

Nói cách khác, bạn có thể nhập một công thức vào 3 ô khác nhau chỉ với một thao tác. Điều này có thể giúp bạn tiết kiệm được một lượng thời gian đáng kể so với việc nhập cùng một công thức vào nhiều ô khác nhau.

Chú ý rằng công thức trên được đặt trong một cặp dấu ngoặc {}. Điều này cho ta biết đó một công thức mảng. (Khi bạn nhập một công thức mảng, bạn không bao giờ tự tay nhập cặp dấu này, mà Excel sẽ tự thêm nó vào, sau khi bạn nhấn Ctrl+Shift+Enter. (Nói thêm, khi bạn nhấn F2 để sửa công thức, bạn sẽ thấy cặp dấu {} tự động biến mất; và nhớ rằng, sau khi sửa công thức, bạn phải nhấn Ctrl+Shift+Enter chứ không phải nhấn Enter như bình thường.)

Ghi chú: Như đã nói ở trên, bởi vì Excel xem mảng như một đơn vị, nên bạn không thể di chuyển hay xóa một thành phần nào trong một mảng. Nếu bạn cần phải làm việc với một mảng, bạn phải chọn toàn bộ mảng. Nếu bạn muốn thay đổi kích thước của một mảng, bạn chọn nó, kích hoạt thanh công thức (formula bar), rồi nhấn Ctrl+Enter để chuyển công thức trong đó trở thành công thức bình thường. Bây giờ bạn có thể chọn lại một dãy ô nhỏ hơn (hoặc lớn hơn) và nhập lại công thức mảng.

Ghi chú thêm: Bạn có thể chọn nhanh một mảng bằng cách kích hoạt một ô bất kỳ trong mảng đó và nhấn Ctrl+/ (dấu gạch chéo)

â– Tìm hiểu các công thức mảng

Để hiểu Excel xử lý một mảng như thế nào, bạn cần ghi nhớ rằng Excel luôn luôn thiết lập một sự tương ứng giữa các ô trong mảng và các ô của bất kỳ dãy nào mà bạn đã nhập vào công thức mảng.

Trong ví dụ 2008 BUDGET, mảng bao gồm các ô C13, D13 và E13; và dãy được sử dụng trong công thức bao gồm các ô C11, D11, và E11. Excel thiết lập sự tương ứng giữa ô mảng C13 với ô nhập liệu C11, ô D13 với ô D11, và ô E13 với ô E11. Ví dụ, để tính giá trị của ô C13 (January 2008 BUDGET), Excel chỉ việc lấy giá trị nhập liệu từ ô C11 và thay thế giá trị đó trong công thức. Hình 2 minh họa một biểu đồ của tiến trình này.

Hình 2

Các công thức mảng có thể sẽ làm cho bạn bối rối, nhưng nếu bạn nhớ những điểm tương ứng tôi vừa nói này, thì bạn sẽ không gặp khó khăn gì khi hiểu được điều gì đang xảy ra.
â– Các công thức mảng thao tác trên nhiều dãy

Trong ví dụ trước, công thức mảng thao tác trên một dãy đơn, nhưng các công thức mảng cũng có thể thao tác trên nhiều dãy. Ví dụ, xem bảng tính Invoice Template được minh họa ở hình 3. Các tổng trong cột Extension (từ ô F12 đến ô F16) được tạo bằng một chuỗi công thức lấy giá của món hàng nhân với số lượng được đặt hàng:
F12 = B12*E12
F13 = B13*E13
F14 = B14*E14
F15 = B15*E15
F16 = B16*E16

Hình 3

Bạn có thể thay thế tất cả các công thức này bằng cách nhập công thức mảng sau đây vào dãy F12:F16:
= B12:B16*E12:E16

Một lần nữa, bạn đã tạo công thức mảng bằng cách thay thế mỗi tham chiếu ô bằng dãy tương ứng (và bằng cách nhấn Ctrl+Shift+Enter sau khi nhập xong công thức).
Ghi chú: Bạn không cần phải nhập công thức mảng vào nhiều ô. Ví dụ, nếu bạn không muốn tính các tổng ở cột Extension trong bảng tính Invoice Template, mà bạn chỉ cần tính giá trị Subtotal, bạn nhập công thức sau đây vào ô F17:
= SUM(B12:B16*E12:E16)

2. Sử dụng các hằng mảng

Trong các công thức mảng mà tôi đã trình bày từ đầu topic đến giờ, các đối số mảng là những dãy ô. Bạn cũng có thể sử dụng các giá trị hằng số để làm các đối số mảng. Thủ tục này cho phép bạn nhập thẳng các giá trị vào trong một công thức mà không làm xáo trộn bảng tính.

Để nhập một hằng mảng vào trong công thức, bạn nhập các giá trị ngay trong công thức và chú ý đến những quy tắc sau đây:

• Đặt các giá trị trong cặp dấu ngoặc {}.

• Nếu bạn muốn Excel xem các giá trị là nằm trên một hàng, bạn tách biệt các giá trị bằng một dấu chấm phẩy (.

• Nếu bạn muốn Excel xem các giá trị là nằm trong một cột, bạn tách biệt các giá trị bằng một dấu phẩy (,).

Ví dụ, hằng mảng sau đây tương đương với việc nhập các giá trị riêng lẻ trong mỗi cột trong bảng tính:
{1;2;3;4}

Tương tự, hằng mảng sau đây tương đương với việc nhập các giá trị riêng lẻ trong một dãy của bảng tính gồm có 3 cột và 2 hàng:
{1,2,3;4,5,6}

Hình 4 minh họa hai ví dụ về công thức mảng khác nhau. công thức ở bên trái (trong dãy E4:E7) tính các khoản vay phải thanh toán với những lãi suất khác nhau trong dãy C5:C8. Còn công thức ở bên phải (trong dãy F4:F7) cũng tính toán tương tự, nhưng các giá trị lãi suất được nhập trực tiếp dưới dạng một hằng mảng trong công thức.

Hình 4

3. Những hàm sử dụng các mảng hoặc trả về kết quả là các mảng

Có nhiều hàm trong bảng tính của Excel đòi hỏi đối số phải là mảng hoặc trả về kết quả là mảng (hay cả hai). Bảng 1 liệt kê danh sách một số hàm này và giải thích mỗi hàm sử dụng các mảng như thế nào.

Bảng 1 - Một số hàm sử dụng mảng trong Excel

Khi bạn sử dụng các hàm trả về kết quả là mảng, bạn nhớ chọn một dãy đủ lớn để chứa mảng kết quả được tạo ra, sau đó nhập hàm dưới dạng một công thức mảng.

Một số bài viết có liên quan:
1/ VLOOKUP với Cột động
2/ 6 thói quen cá nhân khi làm việc với dữ liệu và Excel
3/ Tổng quan về Go To Special
4/ Giới thiệu VBA trong Excel
5/ Bỏ túi 7 thủ thuật Excel không phải ai cũng biết
6/ 5 phương pháp học Excel cơ bản hiệu quả nhất
7/ Sử dụng hàm Subtotal
8/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 1)
9/ Phân tích tồn kho theo phương pháp ABC
10/ Cách viết hàm hiệu quả