Sign in with Microsoft
Sign in or create an account.

Bảng dữ liệu là một phạm vi ô, trong đó bạn có thể thay đổi giá trị ở một số ô và đưa ra các câu trả lời khác cho vấn đề. Một ví dụ hay về bảng dữ liệu dùng hàm PMT với số tiền vay và lãi suất khác nhau để tính toán số tiền phải trả cho khoản vay thế chấp mua nhà. Thử nghiệm với các giá trị khác nhau để quan sát biến thể tương ứng trong kết quả là một tác vụ phổ biến trong phân tích 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ác công What-If tích hợp lệnh. Khi xây dựng và phân tích bảng dữ liệu, bạn đang thực hiện phân tích nếu-thì.

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

Lưu ý: Bạn có thể thực hiện tính toán nhanh hơn với bảng dữ Visual Basic for Applications (VBA). Để biết thêm thông tin, hãy xem Excel What-If Bảng Dữ liệu: Tính toán nhanh hơn với VBA.

Các kiểu 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 bộ giá trị đầu vào để tính toán kết quả có thể có. Tìm mục tiêu khác biệt biệt, nó sử dụng một kết quả duy nhất và tính toán giá trị đầu vào có thể 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ả khả thi. Không giống như các kịch bản, bảng dữ liệu hiển thị cho bạn toàn bộ kết quả trong một bảng trên một trang tính. Việc 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ể chứa 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ù tính năng này được giới hạn chỉ với 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 như bạn muốn. 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 nhiều kịch bản như bạn muốn.

Tìm hiểu thêm trong bài viết, Giới thiệu về phân What-If .

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ể 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),vốn tham chiếu đến ô đầu vào B3.

Bảng dữ liệu với một biến số

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 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ể dùng bảng dữ liệu hai biến số để xem sự kết hợp giữa lãi suất và số hạng vay khác nhau sẽ ảnh hưởng đến khoản thanh toán vay 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 là =PMT(B3/12,B4,-B5),sử dụng hai ô đầu vào, B3 và B4.

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

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 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 mà 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 số chứa giá trị đầu vào của nó trong một cột duy nhất (định hướng cột) hoặc trong một hàng (định hướng hàng). Mọi công thức trong bảng dữ liệu một biến đều phải chỉ tham chiếu đến 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 ô đầu vào—xuống dưới một cột hoặc trên một hàng. Để trống một vài hàng và cột ở cả hai bên của 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 theo 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 đây 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 chứa trong ô D2.


      Bảng dữ liệu với một biến số
      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 phải của công thức đầu tiên.

    • Nếu bảng dữ liệu định hướng theo 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 về 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 ô chứa 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, bấm Phân tích Tình >Dữ 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:

    • Nếu bảng dữ liệu định hướng theo cột, hãy nhập định tham chiếu ô cho ô đầ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 định hướng theo hàng, hãy nhập tham chiếu ô cho ô nhập vào trong 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 phải tham chiếu đến cùng một ô nhập.

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

  1. Thực hiện một trong những bước sau:

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

    • Nếu bảng dữ liệu định hướng theo hàng, hãy nhập công thức mới vào một ô 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,bấm Phân tích Tình> Dữ liệu (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 định hướng theo cột, hãy nhập tham chiếu ô cho ô nhập vào hộp Ô nhập cột.

    • Nếu bảng dữ liệu định hướng theo 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, nhập công thức tham chiếu đến hai ô đầu vào.

    Trong ví dụ sau đây —tại đó các giá trị bắt đầu của 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 các giá trị đầu vào trong cùng một cột, bên dưới công thức.

    Trong trường hợp này, nhập 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 — về bên phải.

    Nhập điều khoả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 ô 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), bấm Phân tích Tình thế >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 ô nhập 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. Bấm 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 giữa lãi suất và điều khoản vay khác nhau sẽ ảnh hưởng đến khoản thanh toán vay 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 là =PMT(B3/12,B4,-B5),sử dụng hai ô đầu vào, B3 và B4.

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

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. Bấm Tệp > chọn Công > thức.

  2. Trong mục Tùy chọn tính toán, bên dưới Tính toán,bấm vào Tự động ngoại trừ bảng dữ liệu.

    Mẹo: Hoặc trên tab Công thức, bấm vào mũi tên trên Tùy chọn Tínhtoán , rồi bấm vào Tự động Ngoại trừ Bảng Dữ liệu (trong nhóm Tính toán).

Bạn có thể 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.

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 chính xác giá trị đầu vào mà công thức cần để có được kết quả đó, hãy dùng tính năng Goal-Seek số. 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.

Excel Bộ giải

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

Bằng cách nhập các số khác nhau vào một ô, bạn có thể nhanh chóng đưa ra các câu trả lời khác nhau cho vấn đề. Một ví dụ tuyệt vời là sử dụng hàm PMT với lãi suất và số kỳ hạn vay khác nhau (theo tháng) để tìm hiểu số tiền bạn có thể chi trả cho một khoản vay mua nhà hoặc mua xe hơi. Bạn nhập số của mình vào phạm vi ô gọi là bảng dữ liệu.

Ở đây, bảng dữ liệu là phạm vi ô B2:D8. Bạn có thể thay đổi giá trị trong B4, số tiền vay và số tiền thanh toán hàng tháng trong cột D sẽ tự động cập nhật. Dùng lãi suất 3,75 %, D2 trả về khoản thanh toán hàng tháng là $1.042,01 bằng cách dùng công thức này: =PMT(C2/12,$B$3,$B$4).

Phạm vi ô này, B2:D8, là một bảng dữ liệu

Bạn có thể dùng một hoặc hai biến số, tùy thuộc vào số biến số và công thức mà bạn muốn kiểm tra.

Dùng kiểm tra một biến số để xem các giá trị khác nhau của một biến trong một công thức sẽ thay đổi kết quả như thế nào. Ví dụ, bạn có thể thay đổi lãi suất cho 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 các giá trị biến số (lãi suất) trong một cột hoặc hàng và kết quả được hiển thị trong cột hoặc hàng gần đó.

Trong sổ làm việc trực tiếp này, ô D2 chứa công thức thanh toán =PMT(C2/12,$B$3.$B$4). Ô B3 là ô biến số, nơi bạn có thể nhập vào một độ dài thuật ngữ khác (số kỳ thanh toán hàng tháng). Trong ô D2, hàm PMT nhập lãi suất 3,75%/12, 360 tháng và khoản vay $225.000, đồng thời tính toán khoản thanh toán hàng tháng là $1.042,01.

Dùng kiểm tra hai biến số để 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ả như thế nào. Ví dụ, bạn có thể kiểm tra các kết hợp khác nhau của lãi suất và số kỳ thanh toán hàng tháng để tính toán một khoản thanh toán vay thế chấp.

Trong sổ làm việc trực tiếp này, ô C3 chứa công thức thanh toán, =PMT($B$3/12,$B$2,B4), dùng hai ô biến số, B2 và B3. Trong ô C2, hàm PMT nhập lãi suất 3,875%/12, 360 tháng, và khoản vay $225.000, đồng thời tính toán khoản thanh toán hàng tháng là $1.058,03.

Bạn cần thêm trợ giúp?

Bạn luôn có thể hỏi một chuyên gia trong Cộng đồng Kỹ thuật Excel hoặc nhận sự hỗ trợ trongCộng đồng trả lời.

Bạn cần thêm trợ giúp?

Phát triển các kỹ năng của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Microsoft Office nội bộ

Thông tin này có hữu ích không?

Bạn hài lòng đến đâu với chất lượng dịch thuật?
Điều gì ảnh hưởng đến trải nghiệm của bạn?

Cảm ơn phản hồi của bạn!

×