Bạn cảm thấy mình tốn quá nhiều thời gian loay hoay chỉnh sửa, thêm bớt các bộ lọc hay công thức để phân tích bảng dữ liệu của mình? UniTrain gợi ý cho bạn 3 phương pháp phân tích tích hợp sẵn của Excel tạo hiệu quả đáng kể cho công việc của bạn. Show
1. Hàm DSUMHàm này cho phép nhập điều kiện cho ô và cho ra kết quả mà không cần dùng chuột chọn nhiều thao tác. Cú pháp hàm là =DSUM(database; field; criteria) – Database: là cơ sở dữ liệu được tạo từ 1 phạm vi ô. Danh sách dữ liệu này sẽ chứa các dữ liệu là các trường, gồm trường để kiểm tra điều kiện và trường để tính tổng. Danh sách chứa hàng đầu tiên là tiêu đề cột. – Field: chỉ rõ tên cột dùng để tỉnh tổng các số liệu. Có thể nhập tên tiêu đề cột trong dấu ngoặc kép, hoặc dùng 1 số thể hiện vị trí cột trong danh sách không dùng dấu ngoặc kép, hay tham chiếu tới tiêu đề cột muốn tính tổng. – Criteria: là phạm vi ô chứa điều kiện muốn hàm DSUM kiểm tra. Ví dụ: Tính tổng doanh thu mặt hàng bia trong năm 2017  Công thức là =DSUM(A1:E10;C1;G4:G5) 2. Dùng Data TableExcel có thể kiểm tra bất kỳ số lượng kết hợp đầu vào nào và ghi lại kết quả đầu ra cho mỗi kết hợp. Ví dụ: Tính lợi nhuận bán hàng dựa trên doanh thu và chi phí 1. Chuẩn bị thông tin dữ liệu cần dự đoán tự động doanh thu 2. Chọn vùng dữ liệu 3. Tab Data > What-If Analysis > Data Table 4. Chọn thông tin 5. Nhấn OK 3. Hàm FREQUENCYBằng cách xác định các “bins” sử dụng giới hạn, FREQUENCY cho biết có bao nhiêu giá trị rơi vào mỗi danh mục. Phân tích này giúp dễ dàng hình dung và cung cấp một bức tranh nhanh chóng về tập dữ liệu. \= FREQUENCY(data_array, bins_array) – data_array: Mảng hay tham chiếu của một tập hợp các giá trị dùng để đếm số lần xuất hiện. Nếu data_array không có giá trị, FREQUENCY() trả về một mảng các trị zero (0). – bins_array: Mảng hay tham chiếu chứa các khoảng giá trị làm mẫu, và các trị trong data_array sẽ được nhóm lại theo các trị mẫu này. Nếu bins_array không có giá trị, FREQUENCY() sẽ trả về số phần tử trong data_array. Ví dụ: Trên đây là các phương pháp phân tích dữ liệu nhanh chóng và hiệu quả. Thực hành nhiều hơn để có thể thành thạo các phương pháp này nhé. Nếu bạn muốn tính toán giá trị logic hoặc đạt được mục tiêu với số liệu có sẵn, Goal Seek là công cụ không thể thiếu. Trong bài viết này, mình sẽ hướng dẫn cách sử dụng Goal Seek để tìm mục tiêu trong Excel, kèm theo bài tập và lời giải chi tiết.Bài viết này thực hiện trên laptop chạy hệ điều hành Windows với phiên bản Excel 2016. Tuy nhiên, bạn cũng có thể thực hiện trên các phiên bản Excel 2003, 2007, 2010, 2013, 2019 với thao tác tương tự. I. Goal Seek là gì? Ứng dụng của Goal Seek trong Excel1. Khái niệm về Goal SeekGoal Seek trong Excel là một công cụ hữu ích để tìm kiếm mục tiêu trong các bài toán Excel. Đây là giải pháp giúp rút ngắn bước tính toán, tiết kiệm thời gian và đem lại hiệu quả cao. 2. Các Ứng Dụng của Goal SeekNgoài việc tìm kiếm mục tiêu, Goal Seek còn có những ứng dụng khác hữu ích, bao gồm:
II. Sử Dụng Goal Seek để Tìm Mục Tiêu trong Excel1. Ví dụ 1: Áp Dụng Goal Seek để Xác Định Điểm Cần Đạt để Lên LớpGiả sử bạn có bảng điểm như dưới đây: Ví dụ 1 Nếu điểm trung bình hiện tại là 5.1 và bạn cần ít nhất 5.5 để vượt qua lớp, tùy thuộc vào điểm môn Anh văn. Bạn muốn biết điểm môn Anh văn ít nhất cần đạt để qua môn, Goal Seek sẽ giúp giải quyết vấn đề này. Bước 1: Mở file Excel với số điểm cần tính > Chọn thẻ Data. Mở file Excel có số điểm cần tính > Chọn thẻ Data Bước 2: Vào phần Data Tools > Chọn lệnh What-If Analysis > Chọn Goal Seek… từ menu thả xuống. Trong phần Data Tools > Chọn lệnh What-If Analysis > Chọn Goal Seek… từ menu thả xuống Bước 3: Trong hộp thoại Goal Seek xuất hiện > Nhập các dữ liệu như sau > Nhấn OK để kích hoạt Goal Seek.
Trong hộp thoại Goal Seek hiện lên > Nhập dữ liệu theo hình > Bấm OK để kích hoạt Goal Seek. Bước 4: Goal Seek sẽ thực hiện tính toán và đưa ra kết quả là để vượt qua lớp, môn Anh văn cần đạt 8,5 điểm. Goal Seek sẽ thực hiện tính toán và cho biết để qua môn, bạn cần đạt 8,5 điểm ở môn Anh văn. Mua sản phẩm Microsoft Office tại Mytour: 2. Bài toán 2: Sử dụng Goal Seek để xác định số tiền vay với trả góp hàng tháng là 3.500.000 đồngVí dụ: Anh A muốn vay 500,000,000 đồng để mua nhà, trong thời kỳ 120 tháng, với lãi suất 7.5%/năm. Sử dụng công thức =PMT(Lãi suất vay/12, số tháng trả góp, số tiền vay), anh A xác định số tiền trả góp hàng tháng là -5,935,088.46 đồng (số âm thể hiện khoản phải trả). Tuy nhiên, Anh A chỉ có khả năng trả góp 3,500,000 đồng/tháng, vì vậy cần phải điều chỉnh thời hạn vay. Sử dụng Goal Seek để xác định số tháng vay cần thiết với khoản trả góp là 3,500,000 đồng. Bài toán 2 Bước 1: Khởi động file Excel > Chọn tab Data. Khởi động file Excel chứa số điểm cần tính > Chọn tab Data Bước 2: Trong phần Data Tools > Chọn lệnh What-If Analysis > Chọn Goal Seek… từ menu thả xuống. Trong phần Data Tools > Chọn lệnh What-If Analysis > Chọn Goal Seek… từ menu thả xuống Bước 3: Mở cửa sổ Goal Seek > Nhập các dữ liệu như sau > Nhấn OK.
Mở cửa sổ Goal Seek > Nhập các dữ liệu như hình > Nhấn OK Bước 4: Goal Seek sẽ thực hiện tính toán, phân tích và đưa ra kết luận với khả năng trả góp 3,500,000 đồng/tháng, bạn có thể chọn thời hạn vay là 358 tháng. Goal Seek sẽ thực hiện tính toán, phân tích và đưa ra kết luận với khả năng trả góp 3,500,000 đồng/tháng, bạn có thể chọn thời hạn vay là 358 tháng Lưu ý:
3. Độ chính xác của Goal Seek trong ExcelKhi sử dụng Goal Seek để tính toán, bạn có thể nhận được kết quả không như mong đợi, gần đúng với đáp án. Để tăng độ chính xác, bạn có thể thiết lập lại cài đặt như sau: Bước 1: Dùng công thức trong ô B1 để tính bình phương của giá trị trong ô A1. Công thức trong ô B1 sẽ tính bình phương của giá trị trong ô A1 Bước 2: Sử dụng Goal Seek để tìm giá trị đầu vào sao cho kết quả bình phương là 25. Sử dụng Goal Seek để tìm giá trị đầu vào để có kết quả bình phương là 25 Goal Seek tính toán kết quả gần đúng. Goal Seek tính toán kết quả gần đúng Bước 3: Mở thẻ File > Chọn Options. Mở thẻ File > Chọn Options Bước 4: Chọn phần Formulas > Dưới mục Calculation options > Giảm giá trị của Maximum Change bằng cách thêm vào vài số 0, so với giá trị mặc định là 0,001 > Nhấn OK. Chọn phần Formulas > Dưới mục Calculation options > Giảm giá trị của Maximum Change bằng cách thêm vào vài số 0, so với giá trị mặc định là 0,001 > Nhấn OK Bước 5: Sử dụng Goal Seek lại và Excel sẽ trả cho chúng ta đáp án chính xác nhất. Sử dụng Goal Seek lại và Excel sẽ trả cho chúng ta đáp án chính xác nhất 4. Sự Quan Trọng của Goal Seek mà Bạn Cần HiểuGoal Seek không phải là giải pháp cho mọi vấn đề. Ví dụ, nếu có nhiều ô đầu vào và đầu ra, Goal Seek sẽ không thể giải quyết được. Trong trường hợp như vậy, bạn có thể sử dụng Solver trong Excel để tìm ra đáp án khi có nhiều giá trị đầu vào. Sự sáng tạo là chìa khóa để giải quyết vấn đề. Nếu bạn đang đối mặt với tình huống này, hãy thử sử dụng Solver trong Excel. Điều này đặc biệt hữu ích khi có nhiều giá trị đầu vào. Đôi khi, việc thêm giá trị đầu vào mới cũng có thể giúp bạn tìm ra giải pháp. Sự linh hoạt là quan trọng khi giải quyết các vấn đề phức tạp. Bước 1: Công thức trong ô B1 dưới đây sẽ cho bạn kết quả là -0.25. Kết quả của công thức trong ô B1 là -0.25 Bước 2: Sử dụng Goal Seek để tìm giá trị đầu vào để công thức trả kết quả là +0.25. Khi áp dụng Goal Seek để tìm giá trị đầu vào cho kết quả của công thức là +0.25 Bước 3: Goal Seek sẽ gặp lỗi và không thể thực hiện > Bạn nhấn Cancel để loại bỏ thông báo lỗi. Goal Seek sẽ thông báo lỗi và không thực hiện được > Bạn nhấn Cancel để loại bỏ thông báo lỗi. Bước 4: Bắt đầu với giá trị đầu vào lớn hơn 8. Bắt đầu với giá trị đầu vào lớn hơn 8 Bước 5: Sử dụng Goal Seek một lần nữa và Excel sẽ trả về đáp án cho chúng ta. Sử dụng Goal Seek một lần nữa và Excel sẽ trả về cho chúng ta đáp án Giải thích ý nghĩa của ví dụ trên: Trong phép tính y = 1 / (x - 8) không liên tục tại x = 8 (vì 1 không thể chia cho 0). Trong trường hợp này, Goal Seek không thể tiếp cận trục hoành (x > 8) khi bắt đầu từ phía bên kia trục hoành (x < 8) hoặc ngược lại. Và đó là cách sử dụng Goal Seek giúp bạn đạt được mục tiêu trong Excel với ví dụ minh họa dễ hiểu. Nếu bạn có bất kỳ thắc mắc nào, hãy để lại bình luận bên dưới. Chúc bạn thành công! Nội dung được phát triển bởi đội ngũ Mytour với mục đích chăm sóc và tăng trải nghiệm khách hàng. Mọi ý kiến đóng góp xin vui lòng liên hệ tổng đài chăm sóc: 1900 2083 hoặc email: [email protected] |
