C7. Thao tác bảng tính

7.4. Phân tích dữ liệu

7.4.1 – Thao Tác Với PivotTables

Chức năng: Thống kê dữ liệu theo nhiều cấp độ khác nhau với nhiều hình thức đa dạng từ một bảng dữ liệu chính.

Cách thực hiện:

  • Đặt con trỏ trong bảng dữ liệu.
  • Chọn lệnh Tab Insert \ Group Tables \ PivotTable \ PivotTable xuất hiện hộp thoại yêu cầu chọn vùng dữ liệu làm thống kê và nơi xuất bảng thống kê.
  • Select a Table or range: Cho phép chọn vùng dữ liệu là Sheet hiện hành.
  • Use an external data source: Cho phép chọn vùng dữ liệu từ file Excel có sẵn.
  • New Worksheet: Phát sinh bảng thống kê trên sheet mới.
  • Existing Worksheet: Phát sinh bảng thống kê từ địa chỉ được nhập vào.
  • Click OK xuất hiện hộp thoại cho phép kéo thả các field là điều kiện thống kê.
  • Vùng Row Labels và Column Labels: sẽ chứa field làm điều kiện thống kê.
  • Vùng Values: chứa những field số liệu muốn thống kê.
  • Dùng chuột kéo và thả field vào vùng tương ứng kết quả sẽ tự động cập nhật tạo thành bảng thống kê.

7.4.2 – Thống Kê Bằng Chức Năng Consolidate

  • Chức năng: Consolidate cho phép hợp nhất dữ liệu từ nhiều vùng dữ liệu nguồn (Sources) và hiển thị kết quả trong vùng dữ liệu đích (Destination).
  • Giả sử công ty ABC có 3 cửa hàng, mỗi cửa hàng có một bảng báo cáo doanh thu năm 2013. Công ty có nhu cầu tổng hợp các báo cáo của 3 cửa hàng thành một báo cáo doanh thu năm 2013 của công ty.

Thực hiện các bước sau:

  1. Chọn vùng dữ liệu đích bằng cách trỏ chuột vào một ô bất kỳ ở vùng đó.
  2. Chọn tab Data \ group Data Tools \ Consolidate, xuất hiện hộp thoại Consolidate.
  • Function: chọn hàm sử dụng (Sum, Min, Max, …), thông thường khi tổng hợp dữ liệu bạn chọn hàm Sum để tính tổng.
  • Reference: để tham chiếu lần lượt các vùng dữ liệu nguồn.
  • All references: chứa tất cả các vùng dữ liệu nguồn cần thiết cho việc hợp nhất. Để xóa một vùng dữ liệu trong hộp All references, bạn chọn vùng đó, rồi nhấp nút Delete.
  • Top row: chọn nếu muốn dùng tên cột của vùng nguồn,
  • Left column: chọn nếu muốn dùng các giá trị của cột đầu tiên của vùng nguồn, ở đây là giá trị của cột Mặt hàng,
  • Create links to source data: chọn nếu muốn dữ liệu hợp nhất được cập nhật mỗi khi có thay đổi ở vùng dữ liệu nguồn.

7.4.3 – Phân tích độ nhạy ( What – If Analysis )

Excel luôn có dữ liệu đồ thị và biểu đồ rất phong phú để mô tả dữ liệu và xu hướng. Với What-If Analysis (phân tích điều gì sẽ xảy ra) là phương pháp cơ bản nhất để truy vấn dữ liệu trong bảng tính.

Giả sử, bạn thiết lập công thức tính Lợi nhuận = Doanh thu – Chi phí. Bạn tự hỏi: Điều gì sẽ xảy với kết quả Lợi nhuận nếu Doanh thu thay đổi hoặc Chi phí thay đổi hoặc cả 2 cùng thay đổi?

7.4.3.1 – Phân tích 1 chiều

Ví dụ: Một người kinh doanh máy tính, mua sản phẩm Laptop với giá 15,000,000 đồng và bán với giá 17,000,000 đồng.

Lợi nhuận sẽ là: 17,000,000-15,000,000= 2,000,000

Lập bài toán:

Giá mua biến động theo thời gian như hình:

Ta xét độ nhạy của lợi nhuận khi giá mua thay đổi.

  • Chọn vùng: B7:F8
  • Vào tab Data \ group Data Tools \ What – If Analysis \ Data Table

  • Nếu bạn chạy độ nhạy theo hàng, như trong ví dụ này thì bạn chọn Row input cell và nhập vào đang chứa giá trị thay đổi (ở đây ta đang xét giá mua thay đổi). Nếu bạn thiết kế giá trị thay đổi theo Cột thì bạn sẽ đưa địa chỉ ô có giá trị thay đổi là: Column input cell.

  • Chọn OK để xem kết quả.

“Với mỗi thay đổi  của giá mua sẽ làm cho lợi nhuận thay đổi, như ở ví dụ này ta thấy. khi giá mua >= 15,000,000 thì lợi nhuận <= 0. người bán không có lợi nhuận”

7.4.3.2 – Phân tích 2 chiều

Phân tích độ nhạy 2 chiều xét tới sự ảnh huỏng của 2 biến x1 và x2 lên biến y. y là biến phụ thuộc của x1 và x2.

Quay lại với ví dụ trên. ta lấy trường hợp giá mua là x1: thay đổi và giá bán sản phẩm x2 thay đổi.

  • Chọn vùng: B7:F10
  • Vào tab Data \ group Data Tools \ What – If Analysis \ Data Table

  • Sau đó chọn Row input cellColumn input cell theo bảng mà bạn thiết kế.
  • Chọn OK hoàn thành.

Ngoài ra ta còn có Goal seek (Hàm mục tiêu) thường áp dụng trong các bài toán như tính doanh thu hòa vốn, thay đổi một chỉ tiêu chi phí nào đó để có được lợi nhuận như mong muốn, tính tổng chi phí cho tổng chi phí ròng phải trả… Và dạng Phân tích tình huống (Scenario) : Là dạng phân tích “what-if”, phân tích tình huống thừa nhận rằng các biến nhất định có quan hệ tương hỗ với nhau. Do vậy, một số ít biến số có thể thay đổi theo một kiểu nhất định tại cùng một thời điểm. Tập hợp các hoàn cảnh có khả năng kết hợp lại để tạo ra “các trường hợp” hay “các tình huống” khác nhau là:

  1. Trường hợp xấu nhất/ Trường hợp bi quan
  2. Trường hợp kỳ vọng/ Trường hợp ước tính tốt nhất
  3. Trường hợp tốt nhất/ Trường hợp lạc quan

Ghi chú: Phân tích tình huống không tính tới xác suất của các trường hợp xảy ra

Comment here