Phân tích tập dữ liệu thay thế trong Microsoft Excel 2013

Khi lưu trữ dữ liệu trong một bảng tính Microsoft Excel 2013, bạn có thể sử dụng chính dữ liệu đó, hoặc một phần của công thức tính toán, để khám phá thông tin quan trọng của tổ chức của bạn.

Khi theo dõi doanh số bán hàng trên cơ sở thời gian, bạn có thể thấy thời điểm tốt nhất và kém nhất trong việc bán hàng và tương quan của chúng với các sự kiện bên ngoài. Ví dụ đối với các doanh nghiệp dịch vụ ăn uống, quà tặng, có thể doanh số những ngày nghỉ tăng đáng kể.

Các dữ liệu trong bảng tính của bạn là rất tốt cho trả lời câu hỏi: "Điều gì đã xảy ra- What happened?" Các dữ liệu là ít hữu ích để trả lời câu hỏi "what-if ", giống như, "chúng ta tiết kiệm bao nhiêu tiền nếu giảm lao động xuống 20 phần trăm tổng chi phí? "

Bạn luôn luôn lưu một phiên bản thay thế cho một bảng tính và tạo ra công thức tính toán những tác động của các thay đổi, nhưng bạn có thể làm điều tương tự trong sổ tính hiện tại bằng cách định nghĩa một hoặc nhiều tập hợp dữ liệu thay thế và chuyển đổi giữa các dữ liệu ban đầu và các bộ dữ liệu mới được tạo ra.

Excel cũng cung cấp các công cụ để xác định các giá trị đầu vào sẽ được yêu cầu cho một công thức để đưa ra một kết quả nhất định. Ví dụ, bạn có thể tìm ra mức doanh thu cho ba ngày vận chuyển sẽ cần phải tăng 25 phần trăm chi phí của tổng doanh thu.

Trong bài này, bạn sẽ học cách để xác định tập hợp dữ liệu thay thế và xác định các đầu vào cần thiết để thực hiện một tính toán ra một kết quả cụ thể.

Định nghĩa một tập dữ liệu thay thế

Khi lưu dữ liệu trong một bảng tính Excel, bạn tạo ra bản ghi phản ánh những đặc điểm của một sự kiện hoặc đối tượng. Dữ liệu có thể mô tả cho số lượng giao hàng trong một giờ vào một ngày đặc biệt, giá của một tùy chọn phân phối mới, hoặc tỷ lệ phần trăm của tổng doanh thu chiếm bởi một tùy chọn phân phối. Sau khi dữ liệu được đặt ra, bạn có thể tạo ra công thức để tạo tính tổng, trung bình, và sắp xếp các hàng dữ liệu trong một bảng tính dựa trên nội dung của một hoặc nhiều cột. Tuy nhiên, nếu muốn thực hiện phân tích với mô hình what-if hoặc khám phá tác động của những thay đổi trong dữ liệu dựa trên bất kỳ tính toán nào trong sổ tính bạn cần phải thay đổi dữ liệu của mình.
Vấn đề với thao tác dữ liệu phản ánh một sự kiện hoặc mục tin là khi thay đổi bất kỳ tính toán ảnh hưởng tới dữ liệu bạn có nguy cơ phá hủy dữ liệu gốc nếu vô tình lưu thay đổi. Bạn có thể tránh làm hỏng dữ liệu ban đầu bằng cách tạo ra một bảng tính trùng lặp và thực hiện thay đổi nó, nhưng bạn cũng có thể tạo ra bộ dữ liệu thay thế, hoặc kịch bản, trong một bảng tính hiện tại.
Khi tạo ra một kịch bản, bạn báo cho Excel các giá trị thay thế của một danh sách các ô trong một bảng tính. Bạn có thể quản lý sử dụng công cụ Scenario Manager để thêm, xóa, và chỉnh sửa kịch bản.

Bấm nút Add để hiển thị hộp thoại Add Scenario
Từ bên trong hộp thoại này, bạn có thể đặt tên cho kịch bản và xác định các ô mà bạn muốn xác định nghĩa giá trị thay thế. Sau khi nhấn OK, một hộp thoại mới để có thể gõ giá trị mới.
Bấm nút Ok để quay về hộp thoại Scenario Manager. Từ đó, nhấn vào nút Show thay thế các giá trị trong bảng ban đầu với các giá trị thay thế, bạn vừa định nghĩa trong kịch bản. Bất kỳ ô chứa công thức tham chiếu thay đổi giá trị sẽ tính toán lại kết quả. Bạn có thể loại bỏ các kịch bản bằng cách nhấn vào nút Undo trên Quick Access Toolbar.

Định nghĩa các tập dữ liệu thay thế

Một tính năng tuyệt vời của các kịch bản Excel là không bị giới hạn việc tao ra dữ liệu thay thế, có thể tạo ra nhiều trường hợp kịch bản bạn muốn và áp dụng chúng bằng cách sử dụng Scenario Manager. Áp dụng nhiều hơn một kịch bản bằng cách sử dụng Scenario Manager, nhấp vào tên của kịch bản đầu tiên bạn muốn hiển thị, nhấn nút Show, và sau đó làm tương tự cho bất kỳ kịch bản tiếp theo. Các giá trị bạn định nghĩa như là một phần của các kịch bản sẽ xuất hiện trong bảng tính, và Excel sẽ cập nhật các tính toán liên quan đến ô thay đổi.
Áp dụng nhiều kịch bản làm thay đổi các giá trị trong bảng tính. Bạn có thể thấy những thay đổi tác động đến công thức, nhưng Excel cũng cung cấp cho bạn một cách để xem kết quả của tất cả các kịch bản trong một vị trí duy nhất, bảng tính riêng biệt. Để tạo ra một bảng tính trong sổ tính hiện tại tổng hợp những thay đổi bởi kịch bản, mở Scenario Manager, và sau đó nhấn nút Summary. Khi đó hộp thoại Scenario Summary thoại sẽ mở ra.

Từ trong hộp thoại, bạn có thể chọn loại bảng tóm tắt muốn tạo và các ô bạn muốn hiển thị trong bảng tổng hợp. Để lựa chọn các ô để hiển thị trong phần tổng hợp, nhấn vào nút Collapse Dialog trong hộp, chọn các ô bạn muốn hiển thị, và sau đó mở rộng hộp thoại. Sau khi bạn xác nhận rằng phạm vi trong hộp đại diện cho các ô bạn muốn đặt bảng tổng hợp, nhấn OK để tạo các bảng tính mới.
Một một ý tưởng tốt để tạo ra một kịch bản " undo " có tên là Normal để giữ các giá trị ban đầu của các ô bạn sẽ thay đổi trước khi chúng được thay đổi trong kịch bản khác. Ví dụ, nếu tạo ra một kịch bản có tên là High Fuel Costs làm thay đổi số liệu bán hàng trong ba ô, kịch bản Normal phục hồi các ô với các giá trị ban đầu. Bằng cách đó, ngay cả khi bạn vô tình sửa đổi bảng tính, bạn có thể áp dụng các kịch bản Normal và tái tạo lại bảng tính từ đầu.

Tìm kết quả với Goal Seek

Khi làm việc trong một tổ chức, có thể bạn phải theo dõi nhiều vấn đề cần thực hiện, theo cả thuật ngữ tuyệt đối và tương đối liên quan đến các bộ phận khác của tổ chức. Nếu là lãnh đạo, bạn cũng có thể muốn thưởng cho nhân viên của mình dựa trên kết quả kinh doanh. Bạn cũng có thể muốn dừng cung cấp các sản phẩm không thể bán.
Khi có một kế hoạch phát triển kinh doanh, bạn cần phải có mục tiêu cụ thể cho từng bộ phận, danh mục sản phẩm. Ví dụ, công ty bạn thể chọn mục tiêu giảm chi phí lao động của công ty bằng 20 phần trăm so với năm trước. Tìm kiếm số lượng lao động đại diện cho việc giảm 20 phần trăm là đơn giản, nhưng thể hiện mục tiêu theo các cách khác có thể là một việc khó khăn trong vấn đề giải pháp. Thay vì giảm chi phí lao động 20 phần trăm so với năm trước, bạn có thể giảm chi phí lao động thể hiện với mức chi phí không quá 20 phần trăm của tổng số kinh phí của công ty.
Ví dụ, hãy xem xét một bảng tính chứa con số chi phí cho các hoạt động kinh doanh và sử dụng những con số để tính toán cả tổng chi phí và chia sẻ cho từng danh mục trong trong số đó.

Mặc dù chắc chắn có thể tìm số mục tiêu làm cho chi phí lao động đại diện cho 20 phần trăm của tổng số chi phí, có một cách dễ dàng hơn để làm điều đó trong Excel là sử dụng công cụ: Goal Seek. Để sử dụng Goal Seek, bạn sẽ hiển thị tab Data và sau đó, trong nhóm Data Tools, What-If Analysis. Trên menu xuất hiện, nhấn Goal Seek để mở hộp thoại Goal Seek

Trong hộp thoại, bạn xác định ô với các giá trị mục tiêu, trong ví dụ này, nó là ô C4, trong đó có tỷ lệ phần trăm của chi phí tính cho danh mục Labor lao động. Trường To Value có giá trị mục tiêu (0,2, tương đương với 20 phần trăm), và trường By Changing Cell xác định ô với giá trị thay đổi để tạo ra các giá trị mục tiêu của 20 phần trăm trong ô C4. Trong ví dụ này, các ô được thay đổi là C3.
Kích OK để Excel tìm một giải pháp cho mục tiêu bạn đặt. Excel khi kết thúc công việc, là những giá trị mới xuất hiện trong các ô được chỉ định, và hộp thoại Goal Seek Status sẽ mở ra.

Tìm các giải pháp tối ưu với Solver

Goal Seek là một công cụ tuyệt vời cho việc tìm cách bạn cần phải thay đổi một giá trị đầu vào duy nhất để tạo ra một kết quả mong muốn từ một công thức, nhưng nó không giúp ích gì nếu muốn tìm giá trị tối ưu cho một tập hỗn hợp các giá trị đầu vào. Ví dụ, trưởng phòng marketing của bạn muốn quảng cáo trong bốn tạp chí quốc gia để đưa khách hàng đến thăm trang web của công ty bạn, nhưng có thể ông không biết kết hợp tốt nhất các yếu tố liên quan đến quảng cáo để đạt được số lượng độc giả lớn nhất. Ông yêu cầu các nhà xuất bản cho giá quảng cáo và số độc giả, trong đó ông ghi lại trong một bảng tính, cùng với số lượng tối thiểu của mỗi ấn phẩm quảng cáo (ba) và số lần tối thiểu ông muốn quảng cáo (10.000.000).
Bởi vì một trong những tạp chí có một tỷ lệ phần trăm độc giả đọc cao, trưởng phòng Marketing muốn đưa ra ít nhất bốn quảng cáo trong ấn phẩm đó, mặc dù độc giả tương đối thấp. Mục tiêu của chiến dịch quảng cáo với số lượng độc giả nhiều nhất có thể mà ngân sách cho chi phí không vượt quá $ 3.000.000.

Nếu đã cài đặt đầy đủ tính năng của Excel trên máy tính, bạn sẽ thấy nút Solver trong tab Data trong nhóm Analysis. Nếu không, bạn cần phải cài đặt Add-in Solver. Để làm như vậy, nhấp vào tab File, và sau đó bấm Options. Trong hộp thoại Excel Options, bấm Add-in để hiển thị các trang Add-Ins. Ở dưới cùng của hộp thoại, trong danh sách Manage, nhấn Excel Add-in, và sau đó nhấn Go để hiển thị hộp thoại Add-Ins. Chọn hộp kiểm Solver Add-in tra và nhấn OK để cài đặt Solver.

Sau khi cài đặt hoàn tất, Solver xuất hiện trên tab Data, trong nhóm Analysis. Nhấn Solver hiển thị hộp thoại Solver.

Bước đầu tiên của việc thiết lập vấn đề Solver là xác định các ô có chứa công thức tổng hợp mà bạn muốn thiết lập mục tiêu. Để thực hiện ô đó, kích vào Set Objective, kích vào ô mục tiêu trong bảng tính, và sau đó chọn tùy chọn đại diện cho mục tiêu của bạn dưới dạng tìm phương án tối đa hóa giá trị hoặc tối thiểu hóa giá trị của ô, hoặc nhập một giá trị cụ thể. Tiếp theo, bạn nhấp vào hộm kiểm By Changing Variable Cells và chọn ô Solver nên thay thế để thay đổi giá trị trong các ô mục tiêu. Cuối cùng, bạn thiết lập các ràng buộc để giới hạn các giá trị Solver có thể sử dụng. Để làm điều này, nhấn Add để mở hộp thoại Add Constraint.

Bạn thêm các ràng buộc Solver bằng cách chọn ô mà bạn muốn áp dụng các ràng buộc, lựa chọn các phép toán so sánh (chẳng hạn như nhỏ hơn hoặc bằng, lớn hơn hoặc bằng, hoặc phải là một số nguyên), và nhấp vào hộp Constraint để lựa chọn ô với giá trị của ràng buộc. Bạn cũng có thể nhập một giá trị trong Constraint, nhưng tham chiếu đến một ô có thể thay đổi các ràng buộc sau này mà không cần mở Solver.

Phân tích dữ liệu với sử dụng thống kê mô tả

Người kinh doanh có kinh nghiệm có thể nói rất nhiều về con số chỉ bằng cách nhìn vào họ để xem điều đó có đúng. Tuy nhiên, khi cần một đánh nhiều hơn trước khi đưa ra quyết định chính thức, bạn có thể sử dụng các công cụ trong Analysis ToolPak.
Nếu không nhìn thấy mục Data Analysis trong nhóm Analysis trên tab Data, bạn có thể cài đặt nó. Để thực hiện điều đó, nhấp vào tab File, và sau đó bấm Options. Trong hộp thoại Excel Options, bấm Add-in để hiển thị các trang Add-Ins. Ở dưới cùng của hộp thoại, trong danh sách Manage, nhấn Excel Add-in, và sau đó nhấn Go để hiển thị các Add-Ins hộp thoại. Chọn hộp kiểm Analysis ToolPak và nhấn OK.

Sau khi hoàn tất việc cài đặt, mục Data Analysis xuất hiện trong nhóm Analysis trên tab Data.
Sau đó, bạn nhấp vào mục mô tả các loại hình phân tích dữ liệu mà bạn muốn thực hiện, click OK, và sử dụng các lệnh trong hộp thoại kết quả phân tích dữ liệu.