Tính nhiều kết quả bằng cách dùng bảng dữ liệu

Trong Microsoft Excel, bảng dữ liệu là một phần của bộ lệnh được gọi là công cụ Phân tích cái gì xảy ra nếu. Khi bạn tạo và phân tích các bảng dữ liệu, bạn đang thực hiện phân tích cái gì xảy ra nếu.

Phân tích cái gì xảy ra nếu là quy trình thay đổi giá trị trong các ô để xem mức độ ảnh hưởng của những thay đổi đó với kết quả của các công thức trên trang tính. Ví dụ: bạn có thể sử dụng bảng dữ liệu để thay đổi lãi suất và thời hạn cho một khoản vay để đánh giá số tiền hàng tháng có thể thanh toán.

Các loại phân tích cái gì xảy ra nếu    

Có ba loại công cụ phân tích cái gì xảy ra nếu trong Excel: kịch bản, bảng dữ liệu và tìm theo mục đích. Kịch bản và bảng dữ liệu sử dụng tập giá trị đầu vào để tính toán kết quả khả thi. Tìm theo mục đích khác biệt hoàn toàn. Công cụ này sử dụng một kết quả duy nhất và tính toán các giá trị đầu vào khả thi sẽ tạo ra kết quả đó.

Giống như các kịch bản, bảng dữ liệu giúp bạn khám phá một tập hợp kết quả có thể có. Không giống như kịch bản, bảng dữ liệu hiển thị tất cả các kết quả trong một bảng trên một trang tính. Sử dụng bảng dữ liệu giúp dễ dàng kiểm tra hàng loạt các khả năng một cách nhanh chóng. Vì bạn chỉ tập trung vào một hoặc hai biến nên kết quả rất dễ đọc và chia sẻ trong biểu mẫu dạng bảng.

Bảng dữ liệu không thể xử lý quá hai biến. Nếu muốn phân tích nhiều hơn hai biến, bạn có thể sử dụng kịch bản. Mặc dù chỉ giới hạn ở một hoặc hai biến (một cho ô đưa vào của hàng và một cho ô đưa vào của cột), bảng dữ liệu có thể bao gồm nhiều giá trị biến khác nhau tùy ý của bạn. Kịch bản có thể có tối đa 32 giá trị khác nhau nhưng bạn có thể tạo nhiều kịch bản tùy thích.

Tìm hiểu thêm trong bài viết, Giới thiệu về phân tích cái gì xảy ra nếu.

Tạo bảng dữ liệu một biến hoặc hai biến, tùy thuộc vào số biến số và công thức mà bạn cần kiểm tra.

Bảng dữ liệu một biến    

Dùng bảng dữ liệu một biến nếu bạn muốn xem các giá trị khác nhau của một biến trong một hoặc nhiều công thức sẽ thay đổi kết quả của những công thức đó như thế nào. Ví dụ: bạn có thể sử dụng bảng dữ liệu một biến để xem mức lãi suất khác nhau ảnh hưởng đến một khoản thanh toán vay thế chấp hàng tháng bằng cách sử dụng hàm PMT. Bạn nhập giá trị biến vào một cột hoặc hàng và kết quả sẽ được hiển thị trong cột hoặc hàng liền kề.

Trong hình minh họa sau đây, ô D2 chứa công thức thanh toán, =PMT(B3/12,B4,-B5), tham chiếu đến ô đưa vào B3.

Bảng dữ liệu hai biến    

Dùng bảng dữ liệu hai biến để xem các giá trị khác nhau của hai biến trong một công thức sẽ thay đổi kết quả của công thức đó như thế nào. Ví dụ: bạn có thể sử dụng bảng dữ liệu hai biến để xem sự kết hợp khác nhau giữa lãi suất và thời hạn vay sẽ ảnh hưởng đến khoản thanh toán thế chấp hàng tháng như thế nào.

Trong hình minh họa sau đây, ô C2 chứa công thức thanh toán, =PMT(B3/12,B4,-B5), sử dụng hai ô đưa vào, B3 và B4.

 

Tính toán bảng dữ liệu    

Bất cứ khi nào một trang tính tính toán lại, mọi bảng dữ liệu đều sẽ tính toán lại, ngay cả khi dữ liệu không có thay đổi. Để tăng tốc độ tính toán của trang tính chứa bảng dữ liệu, bạn có thể thay đổi tùy chọn Tính toán để tự động tính toán lại trang tính chứ không phải bảng dữ liệu. Để tìm hiểu thêm, hãy xem mục Tăng tốc tính toán trong trang tính chứa bảng dữ liệu.

Bảng dữ liệu một biến chứa các giá trị đầu vào của biến trong một cột duy nhất (sắp theo cột) hoặc qua một hàng (sắp theo hàng). Bất kỳ công thức nào trong bảng dữ liệu một biến đều phải tham chiếu đến chỉ một ô đưa vào.

Hãy làm theo các bước sau:

1. Nhập danh sách giá trị mà bạn muốn thay thế trong ô đưa vào xuống dưới một cột hoặc qua một hàng. Để một vài hàng và cột trống ở một trong hai bên của các giá trị.

2. Thực hiện một trong những thao tác sau:

   • Nếu bảng dữ liệu sắp theo cột (giá trị biến của bạn nằm trong một cột), hãy nhập công thức vào ô ở trên và một ô bên phải cột giá trị. Bảng dữ liệu một biến này được sắp theo cột và công thức nằm trong ô D2.
     
     
     
     Nếu bạn muốn kiểm tra ảnh hưởng của các giá trị khác nhau trên các công thức khác, hãy nhập công thức bổ sung trong các ô ở bên phải công thức đầu tiên.

   • Nếu bảng dữ liệu sắp theo hàng (giá trị biến của bạn nằm trong một hàng), hãy nhập công thức vào ô nằm ở cột ở bên trái giá trị đầu tiên và một ô bên dưới hàng giá trị.
     
     Nếu bạn muốn kiểm tra ảnh hưởng của các giá trị khác nhau trên các công thức khác, hãy nhập công thức bổ sung trong các ô ở bên dưới công thức đầu tiên.

3. Chọn phạm vi ô chứa các công thức và giá trị bạn muốn thay thế. Trong hình ở trên, phạm vi này là C2:D5.

4. Trên tab Dữ liệu, chọn Phân tích cái gì xảy ra nếu > Bảng dữ liệu (trong nhóm Công cụ dữ liệu hoặc nhóm Dự báo của Excel 2016).

5. Thực hiện một trong những thao tác sau đây:

   • Nếu bảng dữ liệu sắp theo cột, hãy nhập tham chiếu ô cho ô đưa vào trong trường Ô đưa vào của cột. Trong hình ở trên, ô đưa vào là B3.

   • Nếu bảng dữ liệu sắp theo hàng, hãy nhập tham chiếu ô cho ô đưa vào trong trường Ô đưa vào của hàng.

     Lưu ý: Sau khi tạo bảng dữ liệu, bạn có thể thay đổi định dạng của các ô kết quả. Trong hình, các ô kết quả được định dạng dưới dạng tiền tệ.

Công thức được sử dụng trong bảng dữ liệu một biến phải tham chiếu đến cùng một ô đưa vào.

Hãy làm theo các bước sau

1. Thực hiện một trong những thao tác sau:

   • Nếu bảng dữ liệu sắp theo cột, hãy nhập công thức mới vào ô trống ở bên phải công thức hiện có ở hàng trên cùng của bảng dữ liệu.

   • Nếu bảng dữ liệu sắp theo hàng, hãy nhập công thức mới vào ô trống bên dưới công thức hiện có trong cột đầu tiên của bảng dữ liệu.

2. Chọn phạm vi ô chứa bảng dữ liệu và công thức mới.

3. Trên tab Dữ liệu, chọn Phân tích cái gì xảy ra nếu > Bảng dữ liệu (trong nhóm Công cụ dữ liệu hoặc nhóm Dự báo của Excel 2016).

4. Thực hiện một trong những thao tác sau:

   • Nếu bảng dữ liệu sắp theo cột, hãy nhập tham chiếu ô cho ô đưa vào trong hộp Ô đưa vào của cột.

   • Nếu bảng dữ liệu sắp theo hàng, hãy nhập tham chiếu ô cho ô đưa vào trong hộp Ô đưa vào của hàng.

Bảng dữ liệu hai biến sử dụng công thức chứa hai danh sách giá trị đầu vào. Công thức phải tham chiếu đến hai ô đưa vào khác nhau.

Hãy làm theo các bước sau:

1. Trong một ô trên trang tính, hãy nhập công thức tham chiếu đến hai ô đưa vào.

   Trong ví dụ sau đây, trong đó các giá trị bắt đầu công thức được nhập vào các ô B3, B4 và B5, bạn nhập công thức =PMT(B3/12,B4,-B5) vào ô C2.

2. Nhập một danh sách giá trị đưa vào trong cùng một cột, bên dưới công thức.

   Trong trường hợp này, hãy nhập các mức lãi suất khác nhau vào các ô C3, C4 và C5.

3. Nhập danh sách thứ hai trong cùng hàng với công thức, ở bên phải.

   Nhập thời hạn vay (theo tháng) vào các ô D2 và E2.

4. Chọn phạm vi ô chứa công thức (C2), cả hàng và cột giá trị (C3:C5 và D2:E2) và các ô mà bạn muốn tính toán giá trị (D3:E5).

   Trong trường hợp này, hãy chọn phạm vi C2:E5.

5. Trên tab Dữ liệu, trong nhóm Công cụ dữ liệu hoặc nhóm Dự báo (trong Excel 2016), chọn Phân tích cái gì xảy ra nếu > Bảng dữ liệu (trong nhóm Công cụ dữ liệu hoặc nhóm Dự báo của Excel 2016).

6. Trong trường Ô đưa vào của hàng, nhập tham chiếu đến ô đưa vào cho các giá trị đầu vào trong hàng.
   Nhập ô B4 trong hộp Ô đưa vào của hàng.

7. Trong trường Ô đưa vào của cột, nhập tham chiếu đến ô đưa vào cho các giá trị đầu vào trong cột.
   Nhập B3 trong hộp Ô đưa vào của cột.

8. Chọn OK.

Ví dụ về bảng dữ liệu hai biến

Bảng dữ liệu hai biến có thể cho thấy tổ hợp khác nhau giữa lãi suất và thời hạn vay sẽ ảnh hưởng đến khoản thanh toán thế chấp hàng tháng như thế nào. Trong hình ở đây, ô C2 chứa công thức thanh toán, =PMT(B3/12,B4,-B5), sử dụng hai ô đưa vào, B3 và B4.

Khi bạn thiết lập tùy chọn tính toán này, các phép tính của bảng dữ liệu sẽ không được thực hiện khi toàn bộ sổ làm việc được tính toán lại. Để tính toán lại thủ công bảng dữ liệu, hãy chọn công thức của bảng, rồi nhấn F9.

Làm theo các bước sau để cải thiện hiệu suất tính toán:

1. Chọn Tệp > Tùy chọn > Công thức.

2. Trong mục Tùy chọn tính toán, chọn Tự động.

   Mẹo: Hoặc trên tab Công thức, chọn mũi tên trên Tùy chọn tính toán, rồi chọn Tự động.

Bạn có thể sử dụng một vài công cụ khác của Excel để thực hiện phân tích cái gì xảy ra nếu nếu bạn có những mục đích cụ thể hoặc tập dữ liệu biến lớn hơn.

Tìm Mục tiêu

Nếu bạn biết kết quả mong muốn từ một công thức nhưng không biết chính xác giá trị đầu vào cần nhập để đạt được kết quả đó, hãy sử dụng tính năng Tìm theo mục đích. Xem bài viết Sử dụng Tìm theo mục đích để tìm kết quả bạn muốn bằng cách điều chỉnh giá trị đầu vào.

Trình giải Excel

Bạn có thể sử dụng phần bổ trợ Trình giải Excel để tìm giá trị tối ưu cho tập hợp các biến đầu vào. Trình giải làm việc với một nhóm ô (gọi là biến quyết định hoặc đơn giản là ô biến) được sử dụng để tính toán các công thức trong ô mục tiêu và ô ràng buộc. Bộ giải điều chỉnh giá trị trong ô biến số quyết định để đáp ứng giới hạn trong ô ràng buộc và tạo kết quả bạn muốn cho ô mục tiêu. Tìm hiểu thêm trong bài viết này: Xác định và giải bài toán bằng Trình giải.