Cách tạo Bảng động trong Excel (Sử dụng hàm TABLE & OFFSET)
Bảng động trong excel là bảng mà khi một giá trị mới được chèn vào nó, bảng sẽ tự điều chỉnh kích thước của nó, để tạo bảng động trong excel chúng ta có hai phương pháp khác nhau cùng một lúc là tạo bảng dữ liệu từ phần bảng trong khi phần khác là bằng cách sử dụng hàm bù đắp, trong bảng động, báo cáo và bảng tổng hợp cũng thay đổi khi dữ liệu trong bảng động thay đổi.
Bảng động trong Excel
Động tự bản thân nó có nghĩa là một hệ thống xử lý được đặc trưng cho sự thay đổi liên tục hoặc thay đổi trong hoạt động. Tương tự, trong Excel khi chúng tôi tạo danh sách hoặc dữ liệu trong sổ làm việc và tạo báo cáo từ đó, nhưng nếu chúng tôi thêm bất kỳ dữ liệu nào hoặc xóa một hoặc di chuyển hoặc thay đổi dữ liệu thì toàn bộ báo cáo có thể không chính xác. Excel có một giải pháp cho nó là Bảng động.
Bây giờ câu hỏi đặt ra là tại sao chúng ta cần Dynamic Range hoặc Dynamic Tables. Câu trả lời cho điều đó là bởi vì bất cứ khi nào danh sách hoặc phạm vi dữ liệu được cập nhật hoặc sửa đổi, nó không chắc chắn rằng báo cáo sẽ được thay đổi theo sự thay đổi dữ liệu.
Về cơ bản, có hai ưu điểm chính của Bảng động:
- Một phạm vi động sẽ tự động mở rộng hoặc thu hẹp theo sự thay đổi dữ liệu.
- Bảng tổng hợp dựa trên bảng động trong excel có thể được cập nhật tự động khi tổng hợp được làm mới.
Làm thế nào để tạo một bảng động trong Excel?
Có hai cách cơ bản để sử dụng bảng động trong excel - 1) Sử dụng TABLES và 2) Sử dụng hàm OFFSET.
Bạn có thể tải xuống Mẫu Excel Bảng Động này tại đây - Mẫu Excel Bảng Động# 1 - Sử dụng Bảng để tạo Bảng động trong Excel
Sử dụng Bảng, chúng ta có thể xây dựng một bảng động trong excel và làm cơ sở cho một tổng hợp trên bảng động.
Thí dụ
Chúng tôi có dữ liệu sau,
Nếu chúng tôi tạo một bảng tổng hợp với dải dữ liệu bình thường này từ A1: E6 thì nếu chúng tôi chèn dữ liệu vào hàng 7, nó sẽ không phản ánh trong bảng tổng hợp.
Vì vậy, trước tiên chúng ta sẽ tạo một dải động.
# 1 - Chọn dữ liệu tức là A1: E6.
# 2 - Trong phần chèn, nhấp vào Tab vào Bảng bên dưới phần bảng.
# 3 - Một hộp thoại bật lên.
Vì dữ liệu của chúng ta có tiêu đề nên hãy nhớ chọn hộp “Bảng của tôi có tiêu đề” và nhấp vào ok.
# 4 - Phạm vi động của chúng tôi đã được tạo.
# 5 - Chọn dữ liệu và trong Tab Chèn bên dưới phần bảng excel, hãy nhấp vào bảng tổng hợp.
# 6 - Như chúng ta đã tạo bảng, nó có một phạm vi như Bảng 2. Nhấp vào OK và trong bảng tổng hợp Kéo Sản phẩm vào Hàng và Bán hàng trong Giá trị.
# 7 - Bây giờ trong Trang tính nơi chúng tôi có bảng của chúng tôi chèn Dữ liệu khác vào thứ 7
Trong bảng tổng hợp, hãy làm mới bảng tổng hợp.
Bảng tổng hợp động của chúng tôi đã tự động cập nhật dữ liệu của Sản phẩm 6 trong bảng tổng hợp.
# 2 - Sử dụng Hàm OFFSET để tạo Bảng Động trong Excel
Chúng ta cũng có thể sử dụng Hàm OFFSET để tạo bảng động trong excel. Hãy để chúng tôi xem xét một ví dụ như vậy.
Thí dụ
Tôi có một bảng giá cho các sản phẩm mà tôi sử dụng để tính toán,
Chọn dữ liệu và đặt tên cho nó
Bây giờ, bất cứ khi nào tôi tham khảo bảng giá tập dữ liệu, nó sẽ đưa tôi đến dữ liệu trong phạm vi B2: C7 có bảng giá của tôi. Nhưng nếu tôi cập nhật một hàng khác vào dữ liệu, nó sẽ vẫn đưa tôi đến phạm vi B2: C7 vì danh sách của chúng tôi là tĩnh.
Chúng tôi sẽ sử dụng Hàm Offset để làm cho phạm vi dữ liệu là động.
# 1 - Trong Tab Công thức trong Phạm vi đã Xác định, hãy nhấp vào Tên Đã xác định và một hộp thoại bật lên.
# 2 - Trong Hộp Tên, nhập bất kỳ tên nào, tôi sẽ sử dụng PriceA. Phạm vi là sổ làm việc hiện tại và hiện tại, nó đang tham chiếu đến ô hiện tại được chọn là B2.
Trong Đề cập đến cách viết công thức sau,
= offset (Sheet2! $ B $ 2,1,0, counta (Sheet2! $ B: $ B) -1,2)
= bù đắp (
# 3 - Bây giờ chọn ô bắt đầu là B2,
# 4 - Bây giờ chúng ta cần nhập 1,0 vì nó sẽ đếm có bao nhiêu hàng hoặc cột để đi
# 5 - Bây giờ chúng ta cần nó đếm bất kỳ dữ liệu nào có trong cột B và sử dụng dữ liệu đó làm số hàng, vì vậy hãy sử dụng hàm COUNTA và chọn cột B.
# 6 - Vì chúng tôi không muốn Hàng đầu tiên là tiêu đề sản phẩm được tính như vậy (-) 1 từ nó.
# 7 - Bây giờ số cột sẽ luôn là hai, vì vậy hãy nhập 2 và nhấp vào OK.
# 8 - Phạm vi dữ liệu này sẽ không được hiển thị theo mặc định, vì vậy để xem phạm vi này, chúng ta cần nhấp vào Trình quản lý tên trong Tab Công thức và chọn Sản phẩm,
# 9 - Nếu chúng tôi nhấp vào đề cập đến nó sẽ hiển thị phạm vi dữ liệu,
# 10 - Bây giờ hãy thêm một sản phẩm khác trong bảng Sản phẩm 6.
# 11 - Bây giờ hãy nhấp vào Bảng sản phẩm trong Trình quản lý tên, nó cũng đề cập đến dữ liệu mới được chèn vào,
Đây là cách chúng ta có thể sử dụng chức năng Offset để tạo Bảng động.
Những điều cần ghi nhớ
- Bảng tổng hợp dựa trên Phạm vi động sẽ tự động được cập nhật khi được làm mới.
- Sử dụng chức năng bù đắp trong Tên được xác định có thể được nhìn thấy từ Trình quản lý tên trong Tab công thức.
