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 What-If phân tích. 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 tình thế giả thiết.

Phân tích nếu-thì là quá trình thay đổi giá trị trong ô để xem những thay đổi đó sẽ ảnh hưởng đến kết quả của công thức trên trang tính như thế nào. 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 thanh toán hàng tháng tiềm năng.

Các loại phân tích nếu-thì    

Có ba loại công cụ phân tích tình huống giả thiết trong Excel: kịch bản, bảng dữ liệu và tìm mục tiêu. 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 mục tiêu khác biệt hoàn toàn, nó 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 các 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 một loạt các khả năng trong nháy mắt. 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 bạn muốn phân tích nhiều hơn hai biến, thay vào đó bạn nên 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 ô nhập hàng và một cho ô đầu vào cột), bảng dữ liệu có thể bao gồm nhiều giá trị biến khác nhau tùy thích. Một kịch bản có thể có tối đa 32 giá trị khác nhau nhưng bạn có thể tạo bao 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 What-If tích.

Tạo bảng dữ liệu một biến hoặc hai biến số, 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 số    

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 số để xem mức lãi suất khác nhau ảnh hưởng đến 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 số vào một cột hoặc hàng và kết quả đượ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 ô đầu vào B3.

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

Dùng bảng dữ liệu hai biến số để xem các giá trị khác nhau của hai biến số 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 số để xem sự kết hợp khác nhau giữa lãi suất và điều khoả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 ô đầu 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, bất kỳ bảng dữ liệu nào cũng sẽ tính toán lại—ngay cả khi không có thay đổi nào đối với dữ liệu. Để tăng tốc độ tính toán của trang tính có 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 có 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 nó trong một cột duy nhất (định hướng cột) hoặc qua một hàng (định hướng 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 công ô đư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 ô nhập—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 là định hướng cột (giá trị biến số 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 số này được định hướng 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ủa công thức đầu tiên.

   • Nếu bảng dữ liệu là định hướng hàng (giá trị biến số của bạn nằm trong một hàng), hãy nhập công thức vào ô một 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 vào các ô bên dưới công thức đầu tiên.

3. Chọn phạm vi ô có chứa các công thức và giá trị mà 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 Nếu-Thì > Liệu (trong nhóm Công cụ Dữ liệu hoặc nhóm Dự báo Excel 2016 ).

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

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

   • Nếu bảng dữ liệu là định hướng hàng, hãy nhập tham chiếu ô cho ô nhập vào trường ô nhập Hàng .

     Lưu ý: Sau khi tạo bảng dữ liệu, bạn có thể muốn 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 số phải tham chiếu đến cùng một ô đầu 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 là định hướng 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 có định hướng 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 ô có 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 Nếu-Thì > Liệu Bảng (trong nhóm Công cụ Dữ liệu hoặc nhóm Dự báo Excel 2016 ).

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

   • Nếu bảng dữ liệu là định hướng cột, hãy nhập tham chiếu ô cho ô nhập vào hộp Ô đầu vào Cột.

   • Nếu bảng dữ liệu có định hướng hàng, hãy nhập tham chiếu ô cho ô nhập vào hộp Ô nhập hàng.

Bảng dữ liệu hai biến sử dụng công thức có chứa hai danh sách giá trị đầu vào. Công thức phải tham chiếu đến hai ô đầu 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 ô đầu 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 hãy nhập công thức =PMT(B3/12,B4,-B5) vào ô C2.

2. Nhập một danh sách giá trị đầu vào 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 các điều khoản vay (theo tháng) vào các ô D2 và E2.

4. Chọn phạm vi ô có 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 Nếu-Xảy-ra > 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 ô nhập Hàng , nhập tham chiếu đến ô nhập cho các giá trị đầu vào trong hàng.
   Nhập ô B4 vào hộp Ô nhập hàng.

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

8. Chọn OK.

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

Bảng dữ liệu hai biến số có thể cho thấy sự kết hợp khác nhau giữa lãi suất và điều khoả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 ô đầu vào, B3 và B4.

Khi bạn đặt tùy chọn tính toán này, sẽ không có tính toán bảng dữ liệu nào xảy ra khi việc tính toán lại được thực hiện trên toàn bộ sổ làm việc. Để tính toán lại thủ công bảng dữ liệu của bạn, 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 > 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 số công cụ Excel khác để thực hiện phân tích nếu bạn có mục tiêu cụ thể hoặc tập hợp dữ liệu biến số lớn hơn hoặc mục tiêu cụ thể.

Tìm Mục tiêu

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

Bộ giải Excel

Bạn có thể sử dụng phần bổ trợ Trình giải quyết Excel để tìm giá trị tối ưu cho tập hợp các biến số đầu vào. Trình giải quyết làm việc với một nhóm các ô (được gọi là biến số quyết định, hay đơn giản là các ô biến số) được dùng để tính toán các công thức trong các ô 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 quyết vấn đề bằng cách sử dụng Bộ giải.