Công thức và hàm Excel 97-2013 (Tài liệu cần thiết cho người sử dụng bảng tính): Phần 2

Phần mềm bảng tính số một thế giới Microsoft Excel có một thư viện hàm phong phú và đa dạng, đáp ứng được hầu hết các nhu cầu tính toán của người sử dụng thuộc rất nhiều lĩnh vực khác nhau. Việc nhớ tất cả các hàm trong Excel và cách vận dụng chúng là một việc "rất khó khăn hoặc thậm chí là không thể nhớ hết". Tài liệu điện tử "Công thức và hàm Excel 97-2013" này sẽ giúp hệ thống hóa các hàm và công thức theo một cấu trúc nhất quán giúp người đọc dễ tiếp thu và tra cứu. Phần 2 của tài liệu sẽ là phần hướng dẫn sử dụng hàm mảng trong Excel; Macro, VBA và một số hàm tự tạo. Mời các bạn cùng tham khảo để biết thêm chi tiết.
Nội dung trích xuất từ tài liệu:
Công thức và hàm Excel 97-2013 (Tài liệu cần thiết cho người sử dụng bảng tính): Phần 2 Các Khái Niệm Về Công Thức Mảng Giải Pháp Excel | 419 Phần III SỬ DỤNG HÀM MẢNG TRONG EXCEL III.1. CÁC KHÁI NIỆM VỀ CÔNG THỨC MẢNG Một trong những tính năng độc đáo và mạnh mẽ nhất của Excel chính là khả năng tính toán với các mảng dữ liệu trong công thức. Khi hiểu rõ được khái niệm này sẽ giúp chúng ta tạo ra được các công thức thực hiện các phép tính khó một cách kỳ diệu. Bài viết này sẽ giới thiệu các khái niệm về các mảng số liệu mà bất kỳ ai nếu muốn trở thành chuyên gia sử dụng công thức trong Excel đều phải biết về nó, ngoài ra trong bài cũng trình bày các ví dụ về công thức mảng rất hữu ích. Nếu bạn đã từng lập trình trên bất kỳ ngôn ngữ lập trình nào thì chắc bạn cũng đã nghe đến khái niệm mảng (array). Một array đơn thuần chỉ là một tập hợp các phần tử có quan hệ hay độc lập với nhau. Trong Excel, một array có thể là array một chiều hoặc array hai chiều. Chiều của array ở đây chính là chỉ các dòng và cột trong array. Ví dụ như array một chiều thì có thể hiểu đó là một vùng (range) số liệu trên bảng tính mà vùng này sẽ có một dòng (khi array nằm ngang) hoặc một cột (array nằm dọc). Một array hai chiều có thể hiểu đó là một vùng số liệu trên bảng tính (có dạng hình chữ nhật) bao gồm nhiều dòng và nhiều cột. Excel không hỗ trợ array 3-chiều (VBA thì hỗ trợ). Công thức mảng không cần lưu trữ các số liệu trong quá trình tính toán trong các ô (cell), mà Excel sẽ xử lý các mảng số liệu này trong bộ nhớ máy tính. Sau đó các công thức mảng sẽ lấy kết quả tính toán trả về trên bảng tính. Một công thức mảng có thể trả về kết quả là nhiều ô (range) hay chỉ một ô. www.giaiphapexcel.com Các Khái Niệm Về Công Thức Mảng Giải Pháp Excel | 420 III.1.1. Công thức mảng trả kết quả về một vùng nhiều ô Hình minh hoạ bên dưới trình bay ví dụ đơn giản về việc tính toán cột Total (Tổng doanh thu của một loại sản phầm), thông thường để tính cột Total (cột D) này bạn sẽ nhập vào công thức sau: Tại ô D2 nhập vào công thức =B2*C2 sau đó chép xuống D3:D7 Tính cột [Total] = [Units Sold] * [Unit Price] Để tính cho cột Total ở đây chúng ta dùng tới 6 công thức. Ngoài cách này chúng ta có thể dùng 1 công thức mảng để tính ra kết quả cho cả cột Total và lưu kết quả trả về tại D2:D7. Để tạo một công thức mảng tính toán cho trường hợp này hãy làm theo các bước sau: 1. Chọn vùng các ô sẽ lưu kết quả trả về của công thức mảng, trong ví dụ này chọn vùng D2:D7. 2. Nhập vào công thức sau =B2:B7*C2:C7 (sau khi chọn vùng thì gõ công thức này vào) 3. Vì đây là công thức mảng bạn hãy nhấn tổ hợp phím Ctrl+Shift+Enter để nhận kết quả công thức trả về. (Công thức thông thường thì chỉ cần Enter) Lúc này toàn bộ vùng D2:D7 đã có kết quả tính toán và khi vào xem thì sẽ có công thức như sau: {=B2:B7*C2:C7} Lưu ý, Excel sẽ đặt cặp ngoặc móc bao quanh công thức để chỉ đây là công thức mảng. Công thức mảng trên khi tính toán nó sử dụng dữ liệu từ hai mảng đó là mảng chứa số liệu về [Units Sold] được lưu tại vùng địa chỉ B2:B7 và mảng chứa số liệu về [Unit Price] được lưu tại vùng địa chỉ C2:C7 trong bảng tính. Vì ứng với mỗi mặt hàng sẽ có một kết quả Total khác nhau, nên đó là lý do chúng ta phải chọn trước vùng địa chỉ D2:D7 để lưu trữ kết quả trả về của công thức mảng. Công thức mảng này trả về kết quả hoàn toàn chính xác cũng giống như kết quả trả về từ 6 công thức riêng lẽ sau: =B2*C2 =B3*C3 =B4*C4 =B5*C5 =B6*C6 =B7*C7 www.giaiphapexcel.com Các Khái Niệm Về Công Thức Mảng Giải Pháp Excel | 421 Sử dụng công thức mảng thay cho công thức đơn có một số ưu điểm như:  Là cách tính toán đảm bảo sự chính xác về kết quả (tránh trường hợp vô tình sao chép sai công thức do chạy địa chỉ tham chiếu)  Dùng công thức mảng tránh được việc vộ tình xoá hay làm thay đổi công thức trong một ô nào đó của vùng công thức mảng. Vì công thức mảng không cho phép xoá, sửa chữa một ô trong vùng công thức mảng.  Dùng công thức mảng sẽ giúp tránh trường hợp người chưa thành thạo Excel làm xáo trộng các công thức của bạn. III.1.2. Công thức mảng trả kết quả về một ô Bây giờ chúng ta cần tính Tổng doanh thu của tất cả các sản phẩm, tuy nhiên yêu cầu là tính toán căn cứ vào cột [Units Sold] và [Unit Price] bằng công thức mảng (nghĩa là không tính toán thông qua cột Total). Khi đó chúng ta có thể dùng công thức mảng sau: Tại ô D8 bạn nhập vào công thức: =SUM(B2:B7*C2:C7) và kết thúc bằng tổ hợp phím Ctrl+Shift+Enter. Khi đó công thức trong ô D8 có dạng sau {=SUM(B2:B7*C2:C7)} Công thức mảng này trả về kết quả tính toán tổng doanh thu cho tất cả các sản phẩm trong một ô D8. Công thức này cũng tính toán căn cứ vào 2 mảng số liệu được lưu trong vùng B2:B7 và C2:C7, trong quá trình tính toán công thức mảng sẽ tạo ra trong bộ nhớ một mảng số liệu mới chứa kết quả của phép nhân các cặp số liệu, sau đó hàm SUM được dùng để cộng tất cả các phần tử trong mảng mới này và trả về một giá trị duy nhất lưu vào ô D8. Trong trường hợp này chúng ta cũng có thể dùng công thức thông thường là hàm SUMPRODUCT như sau: Tại ô D8 nhập vào công thức =SUMPRODUCT(B2:B7,C2:C7) sau đó Enter. Mảng một chiều Ta có thể xem mảng một chiều là một hàng (mảng ngang) hay một cột (mảng dọc). Các phần tử trong mảng một chiều (mảng ngang) được cách biệt nhau bằng một dấu phẩy, và trong mảng dọc được cách nhau bằng dấu chấm phẩy. Ví dụ: {1,2,3,4,5} (mảng ngang) và {10;20;30;40;50} (mảng dọc) Hay {'Sun','Mon','Tue','Wed&q ...