Khi bạn có một công thức phức tạp phụ thuộc vào nhiều biến số và muốn hiểu cách thay đổi những dữ liệu đầu vào đó tác động đến kết quả một cách hiệu quả như thế nào thì bảng dữ liệu phân tích What-If trong Excel là một công cụ mạnh mẽ. Nó cho phép bạn xem nhanh tất cả các kết quả có thể xảy ra. Dưới đây là hướng dẫn từng bước về cách tạo bảng dữ liệu trong Excel. Ngoài ra, chúng ta sẽ thảo luận một số điểm chính để sử dụng bảng dữ liệu một cách hiệu quả và trình bày các thao tác khác như xóa, chỉnh sửa và tính toán lại bảng dữ liệu.

Bảng dữ liệu trong Excel là gì?

Tạo bảng dữ liệu một biến

• Bảng dữ liệu theo cột
• Bảng dữ liệu theo hàng
• Nhiều công thức cho bảng dữ liệu một biến

Tạo bảng dữ liệu hai biến

Những điểm chính khi sử dụng bảng dữ liệu

Các thao tác khác để sử dụng bảng dữ liệu

• Xóa bảng dữ liệu
• Chỉnh sửa kết quả bảng dữ liệu
• Tính toán lại bảng dữ liệu theo cách thủ công

Bảng dữ liệu trong Excel là gì?

Trong Excel, bảng dữ liệu là một trong những công cụ Phân tích What-If cho phép bạn thử nghiệm các giá trị đầu vào khác nhau cho công thức và quan sát những thay đổi trong đầu ra của công thức. Công cụ này cực kỳ hữu ích để khám phá các tình huống khác nhau và tiến hành phân tích độ nhạy, đặc biệt khi một công thức phụ thuộc vào nhiều biến.

Có hai loại bảng dữ liệu trong Excel:

Bảng dữ liệu một biến: Điều này cho phép bạn phân tích xem các giá trị khác nhau của một biến sẽ tác động như thế nào đến công thức. Bạn có thể thiết lập bảng dữ liệu một biến theo định dạng theo hàng hoặc theo cột, tùy thuộc vào việc bạn muốn thay đổi dữ liệu đầu vào theo hàng hay xuống cột.

Bảng dữ liệu hai biến: Loại này cho phép bạn xem tác động của việc thay đổi hai biến khác nhau đối với kết quả của công thức. Trong bảng dữ liệu hai biến, bạn thay đổi các giá trị dọc theo cả hàng và cột.

Tạo bảng dữ liệu một biến

Tạo bảng dữ liệu một biến trong Excel là một kỹ năng có giá trị để phân tích xem những thay đổi trong một đầu vào có thể tác động đến nhiều kết quả khác nhau như thế nào. Phần này sẽ hướng dẫn bạn qua quá trình tạo các bảng dữ liệu một biến theo hướng cột, theo hàng và nhiều công thức.

Tuyệt vời! Sử dụng các tab hiệu quả trong Excel như Chrome, Edge, Firefox và Safari!
Tiết kiệm 50% thời gian của bạn và giảm hàng ngàn lần nhấp chuột cho bạn mỗi ngày!

---

Bảng dữ liệu theo cột

Bảng dữ liệu theo cột rất hữu ích khi bạn muốn kiểm tra xem các giá trị khác nhau của một biến duy nhất tác động như thế nào đến kết quả đầu ra của công thức, với các giá trị biến được liệt kê dọc theo một cột. Hãy xem xét một ví dụ tài chính đơn giản:

Giả sử bạn đang cân nhắc một khoản vay trị giá 50,000 USD mà bạn dự định trả trong khoảng thời gian 3 năm (tương đương 36 tháng). Để đánh giá khoản thanh toán hàng tháng nào có thể chấp nhận được dựa trên mức lương của bạn, bạn muốn khám phá xem các mức lãi suất khác nhau sẽ ảnh hưởng như thế nào đến số tiền bạn cần phải trả mỗi tháng.

Bước 1: Thiết lập công thức cơ sở

Để tính số tiền phải trả ở đây mình sẽ đặt lãi suất là 5%. Trong ô B4, nhập công thức PMT, nó tính toán khoản thanh toán hàng tháng dựa trên lãi suất, số kỳ và số tiền vay. Xem ảnh chụp màn hình:

= -PMT($B$1/12, $B$2*12, $B$3)

Bước 2: Liệt kê lãi suất theo cột

Trong một cột, hãy liệt kê các mức lãi suất khác nhau mà bạn muốn kiểm tra. Ví dụ: liệt kê các giá trị từ 4% đến 11% với mức tăng 1% trong một cột và để lại ít nhất một cột trống ở bên phải cho kết quả. Xem ảnh chụp màn hình:

Bước 3: Tạo bảng dữ liệu

1. Trong ô E2, nhập công thức này: = B4.
   
   Chú thích: B4 là ô chứa công thức chính của bạn, đây là công thức có kết quả mà bạn muốn thấy thay đổi khi bạn thay đổi lãi suất.
   
2. Chọn phạm vi bao gồm công thức của bạn, danh sách lãi suất và các ô liền kề để có kết quả (ví dụ: chọn D2 đến E10). Xem ảnh chụp màn hình:
   
3. Đi tới ribbon, bấm vào Ngày tab, sau đó nhấp vào Những gì nếu phân tích > Bảng dữ liệu, xem ảnh chụp màn hình:
   
4. Trong tạp chí Bảng dữ liệu hộp thoại, nhấp vào Ô nhập cột (vì chúng tôi đang sử dụng một cột cho các giá trị đầu vào) và chọn ô biến được tham chiếu trong công thức của bạn. Trong ví dụ này, chúng tôi chọn B1 chứa lãi suất. Cuối cùng, nhấp vào OK nút, xem ảnh chụp màn hình:
   
5. Excel sẽ tự động điền vào các ô trống cạnh mỗi giá trị biến của bạn (các mức lãi suất khác nhau) với kết quả tương ứng. Xem ảnh chụp màn hình:
   
6. Áp dụng định dạng số mong muốn cho kết quả (Tiền tệ) theo nhu cầu của bạn. Bây giờ, bảng dữ liệu theo cột đã được tạo thành công và bạn có thể nhanh chóng xem xét kết quả để đánh giá số tiền thanh toán hàng tháng nào sẽ phù hợp với bạn khi lãi suất thay đổi. Xem ảnh chụp màn hình:
   

Bảng dữ liệu theo hàng

Tạo bảng dữ liệu theo hàng trong Excel bao gồm việc sắp xếp dữ liệu của bạn sao cho các giá trị biến được liệt kê trên một hàng thay vì dọc theo một cột. Lấy ví dụ trên làm tham khảo, chúng ta cùng tiến hành các bước để hoàn thành việc tạo bảng dữ liệu theo hàng trong Excel.

Bước 1: Liệt kê các lãi suất liên tiếp

Đặt các giá trị biến (lãi suất) thành một hàng, đảm bảo có ít nhất một cột trống ở bên trái cho công thức và một hàng trống bên dưới để chứa kết quả, xem ảnh chụp màn hình:

Bước 2: Tạo bảng dữ liệu

1. Trong ô A9, nhập công thức này: = B4.
   
2. Chọn phạm vi bao gồm công thức của bạn, danh sách lãi suất và các ô liền kề để có kết quả (ví dụ: chọn A8 đến I9). Sau đó nhấn vào Ngày > Những gì nếu phân tích > Bảng dữ liệu.
3. Trong tạp chí Bảng dữ liệu hộp thoại, nhấp vào Ô nhập hàng (vì chúng tôi đang sử dụng một hàng cho các giá trị đầu vào) và chọn ô biến được tham chiếu trong công thức của bạn. Trong ví dụ này, chúng tôi chọn B1 chứa lãi suất. Cuối cùng, nhấp vào OK nút, xem ảnh chụp màn hình:
   
4. Áp dụng định dạng số mong muốn cho kết quả (Tiền tệ) theo nhu cầu của bạn. Bây giờ, bảng dữ liệu theo hàng đã được tạo, xem ảnh chụp màn hình:
   

Nhiều công thức cho bảng dữ liệu một biến

Tạo bảng dữ liệu một biến với nhiều công thức trong Excel cho phép bạn xem việc thay đổi một đầu vào ảnh hưởng như thế nào đến nhiều công thức khác nhau cùng một lúc. Trong ví dụ trên, nếu bạn muốn thấy sự thay đổi về lãi suất trên cả số tiền hoàn trả và tổng tiền lãi thì sao? Đây là cách thiết lập nó:

Bước 1: Thêm công thức mới tính tổng tiền lãi

Trong ô B5, nhập công thức sau để tính tổng tiền lãi:

=B4*B2*12-B3

Bước 2: Sắp xếp dữ liệu nguồn của bảng dữ liệu

Trong một cột, hãy liệt kê các mức lãi suất khác nhau mà bạn muốn kiểm tra và để lại ít nhất hai cột trống ở bên phải để biết kết quả. Xem ảnh chụp màn hình:

Bước 3: Tạo bảng dữ liệu:

1. Trong ô E2, nhập công thức này: = B4 để tạo tham chiếu đến tính toán hoàn trả trong dữ liệu gốc.
2. Trong ô F2, nhập công thức này: = B5 để tạo tham chiếu đến tổng mức quan tâm đến dữ liệu gốc.
   
3. Chọn phạm vi bao gồm các công thức, danh sách lãi suất và các ô liền kề cho kết quả (ví dụ: chọn D2 đến F10). Sau đó nhấn vào Ngày > Những gì nếu phân tích > Bảng dữ liệu.
4. Trong tạp chí Bảng dữ liệu hộp thoại, nhấp vào Ô nhập cột (vì chúng tôi đang sử dụng một cột cho các giá trị đầu vào) và chọn ô biến được tham chiếu trong công thức của bạn. Trong ví dụ này, chúng tôi chọn B1 chứa lãi suất. Cuối cùng, nhấp vào OK nút, xem ảnh chụp màn hình:
   
5. Áp dụng định dạng số mong muốn cho kết quả (Tiền tệ) theo nhu cầu của bạn. Và bạn có thể xem kết quả cho từng công thức dựa trên các giá trị biến khác nhau.
   

Tạo bảng dữ liệu hai biến

Bảng dữ liệu hai biến trong Excel hiển thị tác động của các kết hợp khác nhau của hai bộ giá trị biến đối với kết quả của công thức, thể hiện sự thay đổi trong hai đầu vào của công thức ảnh hưởng đồng thời như thế nào đến kết quả của công thức đó.

Ở đây, tôi đã vẽ một bản phác thảo đơn giản để giúp bạn hiểu rõ hơn về hình thức và cấu trúc của bảng dữ liệu hai biến.

Dựa trên ví dụ tạo bảng dữ liệu một biến, bây giờ chúng ta cùng tìm hiểu cách tạo bảng dữ liệu hai biến trong Excel.

Trong tập dữ liệu bên dưới, chúng tôi có lãi suất, thời hạn cho vay và số tiền cho vay và đã tính toán khoản thanh toán hàng tháng bằng cách sử dụng công thức này: =-PMT($B$1/12, $B$2*12, $B$3) cũng. Ở đây, chúng tôi sẽ tập trung vào hai biến số chính từ dữ liệu của chúng tôi: lãi suất và số tiền cho vay, để quan sát những thay đổi của cả hai yếu tố này đồng thời ảnh hưởng đến số tiền trả nợ như thế nào.

Bước 1: Thiết lập hai biến thay đổi

1. Trong một cột, hãy liệt kê các mức lãi suất khác nhau mà bạn muốn kiểm tra. xem ảnh chụp màn hình:
   
2. Trong một hàng, nhập các giá trị số tiền vay khác nhau ngay phía trên giá trị cột (bắt đầu từ một ô ở bên phải ô công thức), xem ảnh chụp màn hình:
   

Bước 2: Tạo bảng dữ liệu

1. Đặt công thức của bạn ở giao điểm của hàng và cột nơi bạn liệt kê các giá trị biến. Trong trường hợp này, tôi sẽ nhập công thức này: = B4 vào ô E2. Xem ảnh chụp màn hình:
   
2. Chọn phạm vi bao gồm số tiền vay, lãi suất, ô công thức và các ô nơi kết quả sẽ được hiển thị.
   
3. Sau đó nhấn vào Ngày > Những gì nếu phân tích > Bảng dữ liệu. Trong hộp thoại Bảng dữ liệu:
   • Trong tạp chí Ô nhập hàng chọn ô tham chiếu đến ô đầu vào cho các giá trị biến trong hàng (trong ví dụ này, B3 chứa số tiền vay).
   • Trong tạp chí Cột đầu vào celhộp l, chọn tham chiếu ô đến ô đầu vào cho các giá trị biến trong cột (B1 chứa lãi suất).
   • Và sau đó, nhấp vào OK Button.
     
4. Bây giờ Excel sẽ điền vào bảng dữ liệu kết quả cho từng tổ hợp số tiền vay và lãi suất. Nó cung cấp cái nhìn trực tiếp về sự kết hợp khác nhau giữa số tiền vay và lãi suất ảnh hưởng đến khoản thanh toán hàng tháng như thế nào, khiến nó trở thành một công cụ có giá trị để lập kế hoạch và phân tích tài chính.
   
5. Cuối cùng, bạn nên áp dụng định dạng số mong muốn cho kết quả (Tiền tệ) theo nhu cầu của mình.
   

Những điểm chính khi sử dụng bảng dữ liệu

• Bảng dữ liệu mới tạo phải nằm trong cùng một bảng tính với dữ liệu gốc.
• Đầu ra của bảng dữ liệu phụ thuộc vào ô công thức trong tập dữ liệu nguồn và mọi thay đổi đối với ô công thức này sẽ tự động cập nhật đầu ra.
• Khi các giá trị đã được tính toán bằng bảng dữ liệu, chúng không thể được hoàn tác bằng Ctrl + Z. Tuy nhiên, bạn có thể chọn thủ công tất cả các giá trị và xóa chúng.
• Bảng dữ liệu tạo ra các công thức mảng, do đó không thể thay đổi hoặc xóa các ô riêng lẻ trong bảng.

Các thao tác khác để sử dụng bảng dữ liệu

Khi bạn đã tạo bảng dữ liệu, bạn có thể yêu cầu các thao tác bổ sung để quản lý bảng dữ liệu một cách hiệu quả, bao gồm xóa bảng dữ liệu, sửa đổi kết quả của bảng và thực hiện các phép tính lại thủ công.

Xóa bảng dữ liệu

Vì kết quả bảng dữ liệu được tính bằng công thức mảng nên bạn không thể xóa một giá trị riêng lẻ khỏi bảng dữ liệu. Tuy nhiên, bạn chỉ có thể xóa toàn bộ bảng dữ liệu.

Chọn tất cả các ô của bảng dữ liệu hoặc chỉ những ô có kết quả, sau đó nhấn Xóa bỏ phím trên bàn phím.

Chỉnh sửa kết quả bảng dữ liệu

Thật vậy, không thể chỉnh sửa trực tiếp các ô riêng lẻ trong bảng dữ liệu vì chúng chứa các công thức mảng mà Excel tạo tự động.

Để thực hiện thay đổi, thông thường bạn cần xóa bảng dữ liệu hiện có rồi tạo bảng mới với những sửa đổi mong muốn. Điều này liên quan đến việc điều chỉnh công thức cơ sở hoặc các giá trị đầu vào, sau đó thiết lập lại bảng dữ liệu để phản ánh những thay đổi này.

Tính toán lại bảng dữ liệu theo cách thủ công

Thông thường, Excel sẽ tính toán lại tất cả các công thức trong tất cả các sổ làm việc đang mở mỗi khi thực hiện thay đổi. Nếu một bảng dữ liệu lớn chứa nhiều giá trị biến và công thức phức tạp đang khiến sổ làm việc Excel của bạn chạy chậm.

Để tránh việc Excel tự động thực hiện các phép tính cho tất cả các bảng dữ liệu mỗi khi có thay đổi trong sổ làm việc, bạn có thể chuyển chế độ tính toán từ Tự động sang Thủ công. Vui lòng thực hiện như sau:

Tới Công thức tab, và sau đó nhấp vào Tùy chọn tính toán > Tự động ngoại trừ bảng dữ liệu, xem ảnh chụp màn hình:

Sau khi bạn sửa đổi cài đặt này, khi bạn tính toán lại toàn bộ sổ làm việc của mình, Excel sẽ không còn tự động cập nhật các phép tính trong bảng dữ liệu của bạn nữa.

Nếu bạn cần cập nhật bảng dữ liệu theo cách thủ công, chỉ cần chọn các ô hiển thị kết quả (các ô chứa công thức TABLE()), sau đó nhấn F9 Chìa khóa.

Bằng cách làm theo các bước được nêu trong hướng dẫn này và ghi nhớ các điểm chính, bạn có thể sử dụng hiệu quả các bảng dữ liệu cho nhu cầu phân tích dữ liệu của mình. Nếu bạn muốn khám phá thêm các mẹo và thủ thuật Excel, trang web của chúng tôi cung cấp hàng nghìn bài hướng dẫn, vui lòng bấm vào đây để truy cập chúng. Cảm ơn bạn đã đọc và chúng tôi mong muốn cung cấp cho bạn nhiều thông tin hữu ích hơn trong tương lai!