Các tính năng nâng cao trong Excel là gì?

Tìm kiếm mục tiêu

Tìm kiếm Mục tiêu là một hàm được tích hợp sẵn trong Hàm Excel Nâng cao cho phép bạn có được đầu ra mong muốn bằng cách thay đổi các giả định. Quá trình này phụ thuộc vào phương pháp thử và sai để đạt được kết quả mong muốn.  

Khóa học miễn phí về phân tích dữ liệu

Hãy xem một ví dụ để hiểu rõ hơn

Thí dụ

Trong ví dụ này, chúng ta muốn tìm lãi suất nếu người đó muốn trả

$5000 mỗi tháng để thanh toán khoản vay

Hàm PMT được sử dụng khi bạn muốn tính số tiền phải trả hàng tháng để tất toán khoản vay

Chúng ta hãy giải quyết vấn đề này theo từng bước để xem làm thế nào chúng ta có thể tính lãi suất sẽ giải quyết khoản vay 400.000 đô la bằng khoản thanh toán 5.000 đô la một tháng

• Bây giờ, công thức PMT sẽ được nhập vào ô là ô Thanh toán liền kề. Hiện tại, không có giá trị nào trong ô lãi suất, Excel cung cấp cho chúng tôi khoản thanh toán $3,333. 33 vì nó giả định lãi suất là 0%. Phớt lờ nó đi

• Chuyển đến Dữ liệu > Phân tích What - If > Goal Seek

• Đặt khoản thanh toán hàng tháng thành -5.000. Khoản khấu trừ về số lượng biểu thị giá trị âm
  
  Đặt lãi suất làm ô thay đổi

• Nhấp vào OK. Bạn sẽ thấy chức năng tìm kiếm mục tiêu tự động đưa ra mức lãi suất cần thiết để thanh toán số tiền vay.  

Vào Home > Number và thay đổi giá trị thành Percentage

Kết quả của bạn sẽ giống như dưới đây

Chương trình chứng chỉ chuyên nghiệp trong phân tích dữ liệu

Phân tích What-If với Bộ giải

Phân tích What-If là phương pháp thay đổi các giá trị để thử các kịch bản khác nhau cho các công thức trong Excel nâng cao

Một số bộ giá trị khác nhau có thể được sử dụng trong một hoặc nhiều công thức excel Nâng cao này để khám phá các kết quả khác nhau

Một bộ giải là lý tưởng cho phân tích what-if. Nó là một add-in Excel và hữu ích ở nhiều cấp độ. Tính năng này có thể được sử dụng để xác định giá trị tối ưu cho một công thức trong ô được gọi là ô mục tiêu. Tuy nhiên, một số ràng buộc hoặc giới hạn có thể áp dụng cho các giá trị ô công thức khác trên trang tính.  

Bộ giải làm việc với các biến quyết định là một nhóm các ô được sử 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ị của các ô biến quyết định để hoạt động trên các giới hạn trên các ô ràng buộc. Quá trình này hỗ trợ trong việc xác định kết quả mong muốn cho ô mục tiêu

cũng đọc. Hướng dẫn tốt nhất để xây dựng bảng điều khiển Excel

Kích hoạt bổ trợ bộ giải

• Trên tab Tệp, bấm vào Tùy chọn
• Chuyển đến Phần bổ trợ, chọn Phần bổ trợ bộ giải, rồi bấm vào nút Bắt đầu

• Kiểm tra bổ trợ bộ giải và bấm OK

• Trong tab Dữ liệu, trong nhóm Phân tích, bạn có thể thấy tùy chọn Bộ giải được thêm vào

Cách sử dụng Bộ giải trong Excel

Trong ví dụ này, chúng tôi sẽ cố gắng tìm giải pháp cho một vấn đề tối ưu hóa đơn giản

Vấn đề. Giả sử bạn là chủ doanh nghiệp và bạn muốn thu nhập của mình là $8000

Mục tiêu. Tính toán các đơn vị được bán và giá cho mỗi đơn vị để đạt được mục tiêu

Ví dụ: chúng tôi đã tạo mô hình sau

• Trên tab Dữ liệu, trong nhóm Phân tích, bấm vào nút Bộ giải
• Trong mục tiêu đã đặt, chọn ô thu nhập và đặt giá trị của nó thành $8000
• Để Đổi ô biến ta chọn các ô C5, C6, C10

• Nhấp vào Giải quyết.  

Mô hình dữ liệu của bạn sẽ thay đổi theo các điều kiện

Nếu khác

Hàm IF được sử dụng để kiểm tra điều kiện và trả về một giá trị nếu điều kiện đó thực sự đúng và một giá trị khác được xác định trước nếu nó hóa ra là sai

cú pháp. =IF[kiểm tra, kết quả đúng, kết quả sai]

Nếu-Lỗi

Hàm Excel IFERROR trả về kết quả thay thế khi công thức tạo ra lỗi và kết quả mong đợi khi không phát hiện thấy lỗi.  

cú pháp. =IFERROR [giá trị, giá_trị_nếu_lỗi]

Ví dụ: Excel trả về lỗi chia cho 0 khi một công thức cố chia một số cho 0

Bằng cách sử dụng hàm IFERROR, bạn có thể thêm một thông báo nếu công thức đánh giá là có lỗi

Xây dựng sự nghiệp khoa học dữ liệu của bạn với Purdue U

Bảng tổng hợp

Bảng Pivot về cơ bản là một công cụ tóm tắt dữ liệu Excel cho phép người dùng báo cáo và khám phá các xu hướng dựa trên thông tin của bạn một cách chính xác trong một khoảng thời gian ngắn.  

Bạn có thể tóm tắt theo từng phần bằng cách sử dụng tính năng kéo và thả của bảng tổng hợp và chọn các chức năng thích hợp trong

Dữ liệu mẫu

Chúng ta sẽ sử dụng dữ liệu mẫu gồm 41 bản ghi với 5 trường thông tin về thông tin người mua. Dữ liệu này là hoàn hảo để hiểu bảng tổng hợp

Chèn bảng Pivot

Để chèn bảng tổng hợp vào trang tính của bạn, hãy làm theo các bước được đề cập bên dưới

• Nhấp vào bất kỳ ô nào trong tập dữ liệu
• Trên tab Chèn, trong nhóm Bảng, hãy bấm vào PivotTable

Một hộp thoại sẽ xuất hiện. Excel sẽ tự động chọn tập dữ liệu của bạn. Nó cũng sẽ tạo một trang tính mới cho bảng tổng hợp của bạn

• Nhấp vào được. Sau đó, nó sẽ tạo một trang tính bảng tổng hợp

Trường kéo

Để biết tổng số mặt hàng mà mỗi người mua đã mua, hãy kéo các trường sau vào các khu vực sau

• Trường người mua đến khu vực Hàng
• Trường mục thành vùng Giá trị

Excel VBA

VBA là viết tắt của Phân tích cơ bản trực quan. Excel VBA là ngôn ngữ lập trình hướng sự kiện của Microsoft dành cho các ứng dụng Office. Macro là thứ mà hầu hết những người viết mã VBA sử dụng

Kích hoạt tùy chọn nhà phát triển trong Excel

Theo mặc định, tab Nhà phát triển được ẩn trên ruy-băng và để tùy chỉnh ruy-băng, cần thực hiện theo các bước được đề cập bên dưới

• Bấm chuột phải vào bất kỳ đâu trên ruy-băng, sau đó bấm vào tùy chọn Tùy chỉnh ruy-băng

• Đi đến Tùy chỉnh Ribbon và chọn hộp kiểm Nhà phát triển

Giao diện soạn thảo VBA

Bạn có thể mở giao diện VBA bằng cách sử dụng phím tắt ALT + F11 hoặc bạn có thể chuyển đến tab Nhà phát triển và nhấp vào Visual Basic

Tạo Nút Lệnh và Gán Macro cho Nút Lệnh

Sau khi bật tab nhà phát triển và đã làm quen với trình soạn thảo VBA, hãy bắt đầu tạo macro với sự trợ giúp của nút lệnh

Để đặt nút lệnh trên trang tính mà bạn đang làm việc, bạn cần thực hiện theo các bước được đề cập bên dưới

• Chuyển đến tab Nhà phát triển > Chèn > Điều khiển ActiveX > Nút lệnh

• Kéo nút lệnh trên trang tính của bạn

Để gán macro cho nút lệnh, hãy làm theo các bước sau

• Nhấp chuột phải vào các nút lệnh và chọn Xem mã.  

• Thêm các dòng mã sau được hiển thị bên dưới

• Đóng trình soạn thảo VBA và bấm vào nút lệnh trên trang tính. Đảm bảo bỏ chọn chế độ thiết kế

Chỉ mục và Kết hợp

Đây là một hàm Excel nâng cao. Hàm MATCH được thiết kế để trả về vị trí của một giá trị trong một phạm vi đã chỉ định, trong khi hàm INDEX trả về một giá trị cụ thể có trong một phạm vi một chiều

Hàm MATCH trả về vị trí của ID bạn đang tìm kiếm. Hàm INDEX sẽ trả về giá trị của mức lương tương ứng với vị trí

Chức năng bù đắp

Hàm OFFSET trả về một tham chiếu đến một dải ô là một số hàng và cột được chỉ định từ một ô hoặc dải ô

cú pháp. OFFSET[tham chiếu, hàng, cột, [chiều cao], [chiều rộng]]

Thí dụ

Hãy xem xét các dữ liệu sau.  

Để tham chiếu C4 bắt đầu từ A1,  tham chiếu là A1, hàng là 3 và cột là 2

Hàm SUM Với OFFSET

Trong ví dụ này, chúng tôi có dữ liệu bán hàng hàng tháng trong hai năm. Mục tiêu là tìm tổng doanh thu cho một tháng cụ thể

Hàm OFFSET trả về phạm vi 1x2, 8 hàng bên dưới ô A2 và 1 cột bên phải ô A2. Hàm SUM sau đó tính tổng của phạm vi này

Tích lũy kiến ​​thức chuyên môn về các công cụ và kỹ thuật phân tích kinh doanh mới nhất với Chương trình Thạc sĩ phân tích nghiệp vụ. đăng ký ngay bây giờ

Phần kết luận

Trong bài viết này, bạn đã học được một số hàm excel nâng cao. Chúng tôi đã thảo luận về một số chức năng bao gồm bảng tổng hợp, tìm kiếm mục tiêu và điều gì xảy ra nếu cùng với bộ giải

Tăng tốc sự nghiệp phân tích của bạn với các kỹ năng Microsoft Excel mạnh mẽ và được yêu cầu ngày nay, bao gồm đào tạo về cách sử dụng Power BI với khóa học Phân tích kinh doanh với Excel của Simplilearn

Khóa học cấp chứng chỉ Business Analytics này cung cấp cho bạn sự hiểu biết thấu đáo về các khái niệm thống kê và phân tích dữ liệu cốt lõi để giúp đưa ra quyết định dựa trên dữ liệu. Khóa đào tạo giúp bạn hiểu rõ về Power BI và khám phá sâu các khái niệm thống kê mà bạn sẽ cần để đưa ra thông tin chuyên sâu từ dữ liệu có sẵn và trình bày những phát hiện bạn nhận được bằng cách sử dụng bảng thông tin cấp điều hành

Bạn có câu hỏi nào cho chúng tôi không? .