Nội dung chính
Công thức mảng trong Excel là một công cụ thật sự mang lại sức mạnh cho người dùng trong việc giải quyết những vấn đề khó. Một công thức mảng đơn giản có thể biểu diễn nhiều phép tính cùng lúc và vì thế nó có thể thay thế cho rất nhiều công thức thường dùng. Thật lạ là hơn 90% người dùng chưa bao giờ sử dụng công thức mảng trong bảng tính của họ bởi vì họ e ngại cách bắt đầu với một công thức mảng.
Vì vậy, công thức mảng là một trong số những điều mơ hồ nhất trong Excel đối với người dùng và người học. Bài viết này sẽ giúp bạn hiểu rõ về công thức mảng theo cách dễ dàng nhất.
Mảng trong Excel là tập hợp các phần tử, mỗi phần tử có thể là chuỗi, số, một dòng, một cột hay nhiều dòng, nhiều cột.
Ví dụ, bạn có danh sách các món ăn và bạn định đặt chúng vào một mảng trong Excel, danh sách các món ăn gồm ("Milk", "Eggs", "Butter", "Corn flakes")
Thực hiện
Kết quả là bạn sẽ có một mảng như sau:
Điều tạo nên sự khác biệt giữa công thức mảng và công thức thường là một công thức mảng có thể xử lí nhiều giá trị kết quả thay vì chỉ một như công thức thường. Hay nói cách khác, một công thức mảng trong Excel sẽ xác định được các giá trị riêng của các phần tử trong mảng. Nó có thể trả về nhiều giá trị cùng lúc. Vì thế, kết quả trả về cho một công thức mảng sẽ là một mảng giá trị.
Công thức mảng được hỗ trợ trong các phiên bản Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 và các Phiên bản thấp hơn nữa.
Chúng ta có dữ liệu mẫu cho ví dụ như sau:
Chúng ta sẽ dùng công thức để tính tổng số tiền bán được cho mỗi sản phẩm ở cột D và tổng thu ở ô D7.
Thông thường, bạn sẽ tính tổng tiền thu được cho mỗi sản phẩm ở cột D bằng công thức “= B2 * C2“, “= B3 * C3”, … Sau đó tính tổng thu ở ô D7:= SUM (D2:D6)
Điều khác biệt gì xảy ra khi tôi sử dụng công thức mảng?
Tổng số tiền thu được ở mỗi sản phẩm từ ô D2 đến ô D6 sẽ được tính bằng thật nhanh và đơn giản như sau:
Bạn sẽ có kết quả cho tổng thu của mỗi sản phẩm ở các ô tương ứng D2:D6
Việc tính tổng thu cho tất cả các sản phẩm có thể thực hiện bằng một trong 2 cách sau:
- Cách thông thường: D7:= SUM (D2:D6)
- Công thức mảng:
Khi sử dụng công thức mảng để tính tổng thu cho tất cả các sản phẩm, thậm chí bạn không cần tính tổng số tiền thu được của mỗi sản phẩm ở các ô D2:D6
Việc kết hợp tổ hợp phím CTRL + SHIFT + ENTER là một điều kỳ diệu để biến công thức bình thường thành công thức mảng.
Khi bạn nhập một công thức mảng trong Excel, những điều quan trọng sau đây bạn nhất định phải nhớ:
Một công thức mảng trong Excel có thể trả về kết quả gồm một hoặc nhiều ô. Một công thức mảng được nhập trên một vùng gồm nhiều ô thì được gọi là công thức nhiều ô. Một công thức mảng chỉ trú ngụ trong một ô thì được gọi là công thức ô đơn.
Dữ liệu mẫu liệt kê các sản phẩm được bán trong tháng 1 và tháng 2. Việc bạn cần làm là tìm xem số lượng bán ra chênh lệch cao nhất giữa tháng 1 với tháng 2 của các sản phẩm là bao nhiêu?
Thông thường, để tìm được kết quả này, bạn sẽ chèn thêm cột D vào bên phải của bảng dữ liệu. Sau đó, bạn tính chênh lệch ở từng sản phẩm bằng công thức như D2:= C2 – B2. Cuối cùng F1:= MAX (D2:D6)
Sử dụng công thức mảng, bạn không cần phải chèn thêm cột D vào bảng dữ liệu và sử dụng công thức để tính chênh lệch cho từng sản phẩm. Việc bạn cần làm là nhập công thức vào ô F1 như sau: = MAX (C2:C6 – B2:B6) rồi nhấn CTRL + SHIFT + ENTER, bạn sẽ nhận được kết quả ngay lập tức.
Chúng ta có bảng dữ liệu sau:
Ở ví dụ này, chúng ta sẽ sử dụng công thức mảng cho nhiều ô để tính thuế cho các sản phẩm bán được. Thuế được tính 10% tổng giá trị sản phẩm bán được. Chúng ta thực hiện theo các bước sau:
Chúng ta có bảng dữ liệu sau:
Trong Excel, có một số hàm đã được thiết kế dùng cho mảng gồm nhiều ô gọi là “hàm mảng”. TRANSPOSE là một trong số các hàm mảng đó, hàm TRANSPOSE dùng để chuyển đổi dữ liệu của bảng từ cột thành dòng. Chúng ta thực hiện như sau:
Trong ví dụ này, bảng nguồn gồm 6 dòng, 4 cột. Như vậy vùng trống cần chọn cho bảng đích gồm 6 cột, 4 dòng.
1. Bạn phải chọn vùng (gồm nhiều ô) là nơi bạn sẽ đặt kết quả trả về rồi mới nhập công thức mảng vào
2. Xóa công thức mảng bằng cách nào?
3. Bạn không thể hiệu chỉnh bất cứ gì hay di chuyển nội dung của một phần tử bất kỳ trong một mảng. Bạn cũng không thể thêm mới một ô hay xóa một ô trong vùng mảng. Khi bạn cố gắng làm một trong các điều vừa kể, Excel sẽ cảnh báo cho bạn biết điều này không thể “You cannot change part of an array”
Trong Excel, một hằng mảng đơn giản là một tập hợp các giá trị tĩnh. Những giá trị này sẽ không bị thay đổi khi bạn copy công thức đến vị trí khác.
Một hằng mảng ngang sẽ nằm trên một dòng (row).
Kết quả bạn sẽ có một hằng mảng được Excel bao bọc bởi một dấu ngoặc nhọn { } khác.
Một hằng mảng dọc sẽ nằm trên một cột
Kết quả bạn sẽ có một hằng mảng được Excel bao bọc bởi một dấu ngoặc nhọn { }
Tạo hằng mảng theo 2 hướng cũng tương tự như việc tạo hằng mảng ngang hay hằng mảng dọc. Tuy nhiên, bạn sẽ dùng dấu “ , “ để phân cách các phần tử giữa các cột, dùng dấu “ ; “ để phân cách phần tử giữa các dòng.
1. Các thành tố trong hằng mảng
2. Đặt tên hằng mảng
3. Đặt hằng mảng vào Sheet
Bạn sẽ được hướng dẫn học cách sử dụng hằng mảng trong công thức bằng các ví dụ đơn giản, dễ hiểu.
Bây giờ, bạn hãy tạo một hằng mảng dọc chứa nhiều số tự nhiên. Trong ví dụ này, bạn muốn tính tổng của 3 số tự nhiên lớn nhất trong vùng mảng của bạn.
Trong ví dụ này, bạn sẽ tính tổng số tiền mà nhân viên Mike đã bán các sản phẩm Apples.
Như vậy, với điều kiện là Seller = “Mike” và Product = “Apples”, số tiền bán được chỉ có 2 lượt là $10 và $30 như dữ liệu hình bên trên. Theo cách thực hiện thông thường, bạn sẽ gặp khó khăn một chút và trước tiên bạn sẽ phải lọc ra dữ liệu mà Mike bán các sản phẩm Apples. Sau đó mới tính tổng số tiền bán được của các mặt hàng này. Bằng cách sử dụng công thức mảng, yêu cầu này trở nên đơn giản hơn bao giờ hết.
Nhập công thức mảng tại ô F3 như sau:
Giải thích kết quả công thức mảng có được:
{= SUM ((A2:A9 = “Mike”) * (B2:B9 = “Apples”) * (C2:C9))}
Hi vọng, bài viết này thật sự hữu ích cho bạn trong việc học cách sử dụng công thức mảng trong Excel. Đừng quên theo dõi Gitiho ngay hôm nay nhé, chúng tôi còn nhiều mẹo và thủ thuật tin học văn phòng thú vị khác chờ bạn khám phá.
Để nâng cao kiến thức Excel, bạn hãy tham gia ngay khóa học Tuyệt đỉnh Excel, trở thành bậc thầy Excel trong 16 giờ của Gitiho.com. Trong khóa học này bạn sẽ được tìm hiểu cách sử dụng các công cụ: conditional formatting, data validation, pivot table… để ứng dụng vào trong công việc. Ngoài ra bạn còn biết cách sử dụng các hàm trong excel, từ các hàm cơ bản cho tới các hàm nâng cao, phức tạp. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: Gitiho.com
Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Nâng Cao
0 Bình luận