Nếu như họ biết công dụng mà ta ước ao có được từ một công thức, vậy thì chỉ việc sử dụng Goal Seek trong Excel nhằm tìm ra được giá trị nguồn vào của công thức khiến cho ra hiệu quả mà ta mong mỏi tính.
Bạn đang xem: Sử dụng goal seek trong excel để tìm mục tiêu
Ví dụ trong thực tế, bọn họ có giá cả đôi giầy là 200 ngàn đồng thì thu nhập cá nhân là 1.639.890.000đ. Mong mỏi thu nhập là 2 tỷ thì giá bán phải là bao nhiêu. Hoặc còn nếu như không muốn tăng giá thành thì ta cần tăng con số đôi giày thì số giày đẩy ra phải là bao nhiêu. Bằng việc thực hiện Goal Seek trên Excel họ sẽ dễ dàng giải được việc này. (Kết quả được tính ra cho tình huống này là giá cả phải tăng thành 214 nghìn đồng hoặc bắt buộc tăng số lượng đẩy ra đạt 33,232 đôi để đạt được doanh thu 2 tỷ đồng).
Trong bài bác này Gitiho.com đang hướng dẫn bạn cách sử dụng Goal Seek vào Excel bằng minh họa bỏ ra tiết.
Nội dung chính
Ví dụ 1: thực hiện Goal Seek nhằm tìm ra điểm số cần đạt
Sử dụng Goal Seek trong Excel nhằm tìm ra điểm số cần đã đạt được trong bài kiểm tra sản phẩm công nghệ 4 để có được điểm trung bình vào cuối kỳ là 70.
Bước 1: Nhập công tính mức độ vừa phải điểm cuối kỳ.
Chọn ô trống cần trả công dụng là điểm trung bình vào cuối kỳ (trong lấy một ví dụ minh họa này là ô B7), nhập cách làm tính trung bình sau với nhấn phím Enter để lấy kết quả.
=AVERAGE(B2:B5)
Bước 2: Điểm của bài thi vật dụng 4 là ô dữ liệu đầu vào (trong minh họa này của họ là ô B5).
Bước 3: trên tab Data, trong nhóm Forecast, nhấn con chuột chọnWhat-If Analysis.
Bước 4: nhấn chuột chọn Goal Seek.
Lúc này vỏ hộp thoại Goal Seek đã xuất hiện.
Bước 5: Nhấn chọn ô B7.
Bước 6: nhấn vào hộpTo value cùng gõ số 70.
Bước 7: nhấp vào hộpBy changing cell và nhấn chọn ô B5.
Bước 8: Nhấn lựa chọn nút OK.
Lúc này chúng ta đã thu được tác dụng cần tìm. Với bài xích kiểm tra thứ tư đạt 90 điểm thì điểm trung bình cuối kỳ ta đã đạt được là 70.
Ví dụ 2: áp dụng Goal Seek để tính ra số tiền mang đến vay
Sử dụng Goal Seek trên Exel để tính ra số tiền giải ngân cho vay để hàng tháng được trả 1500 đô la.
Bước 1. Bí quyết ở vào ô B5 dùng làm tính số chi phí trả sản phẩm tháng.
=PMT(B1/12,B2*12,B3)
Chú thích: Hàm PMT dùng để làm tính khoản giao dịch cho một khoản cho vay. Số tiền vay càng tốt thì số tiền buộc phải trả hàng tháng càng cao. Nếu như từng tháng chỉ có thể chi trả 1500 đô la thì rất có thể vay tối đa là từng nào tiền.
Bước 2. Khoản tiền giải ngân cho vay được để trong ô B3, đây cũng là ô tài liệu đầu vào.
Bước 3. Trên tab Data, tìm nhóm Forecasst, dấn chọnWhat-If Analysis.
Bước 4. Nhấn lựa chọn Goal Seek.
Lúc này một hộp thoại Goal Seek đã xuất hiện. Thực hiện các bước sau và bọn họ có hình minh họa bên dưới.
Bước 5. Nhấn lựa chọn ô B5.
Bước 6. Nhấp vào hộp To value với gõ -1500 (dấu trừ sinh sống đây tức là ta đang trả tiền).
Bước 7. Bấm vào hộpBy changing cell và chọn ô B3.
Bước 8. Nhấn chọn OK.
Ta được kết quả như hình dưới đây, cùng với khoản tiền giải ngân cho vay là 250,187 đô la thì hàng tháng trả 1500 đô la.
Tính chính xác của Goal Seek
Sử dụng Goal Seek trả về cho họ đáp án ngay gần đúng. Bạn hoàn toàn có thể biến hóa thiết lập lặp lại trong Excel để thu được đáp án chính xác hơn.
Bước 1. Công thức trong ô B1 dưới đây được dùng để tính bình phương quý giá trong ô A1.
Bước 2. Sử dụng Goal Seek để tìm ra cực hiếm đầu vào để sở hữu được kết quả bình phương là 25.
Tới đây bọn họ được trả công dụng là cực hiếm gần đúng.
Bước 3. Trên tab File, nhấn lựa chọn Options, chọn Formulas.
Bước 4. Dưới mục Calculation options, ta tiết kiệm chi phí với chính sách giảm giá trị của Maximum Change bằng phương pháp thêm vào vài số 0. So với cái giá trị mang định vốn tất cả là 0,001.
Bước 5. Nhấn chọn OK.
Bước 6. áp dụng Goal Seek lần nữa. Excel vẫn trả cho chúng ta đáp án bao gồm xác.
Có thể bạn chưa biết
Có tương đối nhiều vấn đề mà lại Goal Seek không thể giải quyết và xử lý được. Ví như Goal Seek được cho phép một dữ liệu đầu vào là ô riêng biệt và một ô tài liệu đầu ra cũng là ô solo lẻ. Vì thế nếu ta có tài liệu đầu vào là các ô thì phải giải quyết và xử lý như nỗ lực nào.
Trong tình huống này ta hoàn toàn có thể sử dụng Solver vào Excel để xử lý vấn đề nếu bọn họ có nhiều giá trị đầu vào. Hoặc thỉnh thoảng ta cũng cần nhập giá trị đầu vào khác để tìm ra đáp án.
Bước 1. Phương pháp trong ô B1 tiếp sau đây trả ra kết quả là -0.25.
Bước 2. Sử dụng Goal Seek nhằm tìm định giá trị nguồn vào để trả kết quả từ bí quyết là +0.25.
Tới phía trên ta thu được tác dụng là Excel bắt buộc tìm ra đáp án.
Bước 3. Thừa nhận Cancel.
Bước 4. Bước đầu với quý giá đầu vào lớn hơn 8.
Bước 5. Sử dụng Goal Seek một lần tiếp nữa và Excel đã trả cho họ đáp án.
Giải thích: vào phép tính y = 1 / (x - 8) không liên tục tại x = 8 (vì 1 bắt buộc chia đến 0). Trong lấy ví dụ này, Goal seek thiết yếu tiếp cận trục hoành (x > 8) ví như nó bắt đầu ở phía bên kia trục hoành (x học tập Excel cấp tốc
Khi bạn tùy chỉnh cấu hình một bảng tính để thực hiện Goal Seek, bạn thông thường sẽ có một phương pháp trong một ô, và các biến cho công thức này (với một quý giá ban đầu) một trong những ô khác. Công thức gồm thể có khá nhiều biến, nhưng Goal Seek chỉ chất nhận được bạn xử lý mỗi lần một vươn lên là mà thôi.Goal Seek hoạt động bằng phương pháp sử dụng phương pháp lặp đi lặp lại (iterative method) để tìm ra lời giải. Nghĩa là, Goal Seek đã thử giá trị ban đầu của biến đổi để xem nó bao gồm tạo ra công dụng mà bạn có nhu cầu hay không. Trường hợp không, Goal Seek đang thử tiếp với đầy đủ giá trị khác nhau, cho tới khi nó tìm thấy lời giải, tuyệt nói phương pháp khác, cho đến bao giờ kết quả nhưng nó tìm kiếm được gần giống với kết quả của bạn có nhu cầu nhất.
Trước lúc chạy Goal Seek, bạn quan trọng lập bảng tính theo một mẫu mã nào đó, mà ví dụ là nên triển khai 3 điều sau đây:* tùy chỉnh thiết lập một ô có tác dụng ô thay đổi (changing cell). Đây là giá trị mà Goal Seek sẽ xử trí lặp đi lặp lại để cố gắng đạt được công dụng gần đúng nhất. Rồi nhập một giá trị ban đầu trong ô này, đến nó bằng 0 chẳng hạn. * thiết lập cấu hình các quý hiếm nhập liệu không giống cho bí quyết và đặt cho chúng phần đông giá trị ưng ý hợp. * tạo thành một cách làm để Goal Seek áp dụng trong vượt trình nỗ lực đạt được mục tiêu.VD1: gồm số liệu của dự án chi tiêu khai thác
Trước hết nhập mô hình bài toán, khai báo biến nên tìm, trong ví dụ như này là lãi xuất vay vốn tại ô B15Lên Tool, call Goal Seek, lộ diện hộp hội thoại Goal Seek, điền những thông số:Set cell: ô cất hàm mục tiêu
To value: hàm kim chỉ nam bằng
By changing cell: ô chứa biến
Nhấn OK là xong
VD2: nhiều người đang ước mơ chọn được một thiết bị có trị giá chỉ $50,000, và mong là trong tầm 5 năm tính trường đoản cú hôm nay, mong mơ sẽ thành hiện tại thực. Trả sử rằng, chi phí gửi ngân hàng có lãi suất vay là 5% một năm, vậy bạn cần phải gửi vào ngân hàng mỗi năm buổi tối thiểu là từng nào để đạt được ước mơ của mình? Hình 15.9 minh họa một bảng tính đang được tùy chỉnh cấu hình để sử dụng Goal Seek cho cầu mơ này:
Trong hình trên:Ô C6 là ô biến hóa (changing cell): số tiền buổi tối thiểu phải gửi vào bank mỗi năm (với giá chỉ trị ban sơ là không có đồng như thế nào cả). Các ô C4 cùng C5 được áp dụng làm những hằng mang đến hàm FV() nghỉ ngơi ô C8.Ô C8 chứa hàm FV(), là 1 trong hàm chuyên dùng làm tính một giá trị tương lai cho 1 khoản đầu tư. Và bạn có nhu cầu rằng hiệu quả ở đây đã là $50,000.Bạn theo quá trình sau trên đây để chạy Goal Seek1) chọn Data, What-If Analysis, Goal Seek. Excel hiển thị vỏ hộp thoại Goal Seek.2) Nhập tham chiếu đến ô chứa công thức trong hộp Set Cell. Vào trường hợp này, là $C$8.3) Nhập quý giá mà bạn muốn sẽ là công dụng của cách làm trong vỏ hộp To Value. Vào trường hòa hợp này, chúng ta nhập vào 50000.4) Nhập theo chiếu cho ô rứa đổi, tốt nói phương pháp khác, để dành được giá trị ở to Value cho phương pháp ở set Cell, thì đổi khác giá trị làm việc đây: By changing cell (là ô C6 trong hình 15.9).
5) sau khoản thời gian nhấn OK của vỏ hộp thoại, Goal Seek sẽ “rùng mình” một tí, với hiển thị kết quả mà nó tìm kiếm được vào ô biến đổi (là giá trị $-9,059 làm việc ô C6) với hộp thoại Goal Seek Status cho bạn biết nó có tìm được lời giải hay không, cũng cho mình so sánh hiệu quả áp dụng giải thuật này (Current value) với kết quả mà bạn có nhu cầu có (Target value):
6) Ở đây, Goal Seek tính ra rằng, nếu như muốn có được $50,000 sau 5 năm, thì ngay lập tức từ bây giờ, từng năm chúng ta phải gởi vào bank ít tốt nhất $9,059, với đưa thiết lãi suất tiền gửi trong xuyên suốt 5 năm là 5% một năm.7) nếu như bạn ưng ý với hiệu quả của Goal Seek search được, thì nhận OK để gật đầu kết quả ở ô biến đổi (là quý giá $-9,059 nghỉ ngơi ô C6), còn nếu như muốn bỏ qua công dụng này, chúng ta nhấn Cancel.
VD3: Bài toán tối ưu hóa roi sản phẩmCó các doanh nghiệp thực hiện lợi nhuận từ thành phầm như là thước đo cho tình trạng tài chính của mình. Một mức lợi nhuận mạnh, tức là các giá thành đang được kiểm soát điều hành tốt, và cho biết thị trường bằng lòng với ngân sách chi tiêu của sản phẩm. Dĩ nhiên, lợi nhuận còn phụ thuộc vào những yếu tố khác nữa, và chúng ta cũng có thể sử dụng Goal Seek nhằm tìm ra nút lợi nhuận tối ưu phụ thuộc một một trong những yếu tố này.Ví dụ, bạn có nhu cầu đưa ra một dòng sản phẩm mới, và bạn có nhu cầu thu được 30% roi từ nó trong những năm đầu tiên. Cùng giả sử rằng bạn có phần đông giả định sau đây: trong năm trước tiên này sẽ bán tốt 100,000 sản phẩm (Unit Sold).Mức ưu đãi trung bình (Average Discount) cho các đại lý là 40%Tổng chi phí cố định (Fixed Costs) là $750,000, và ngân sách riêng cho mỗi sản phẩm (Costs per Unit) là $12.63Từ những thông tin trên, họ sẽ tìm ra mức giá cả hợp lý độc nhất (Price per Unit) cho sản phẩm để tìm được 30% lợi nhuận (Margin).Hình 15.12 minh họa một bảng tính được tùy chỉnh cấu hình để giải việc này. Một giá trị ban đầu là $1.00 được nhập sống ô Price Per Unit (giá bán ra cho mỗi sản phẩm, ô C4). Với mức chi phí này, nếu buôn bán hết 100,000 sản phẩm, và sau thời điểm chiết khấu 40% đến đại lý, bọn họ sẽ có doanh thu là $60,000. Giá thành cho 100,000 thành phầm này cùng với giá thành cố định sẽ là $2,013,000. Như vậy, giả dụ như xuất kho với giá $1.00 một sản phẩm, bọn họ sẽ lỗ $1,953,000, hay tương đương với -3255% (!)Để tìm ra giá bán cho sản phẩm (giá trị ở C4) mà tìm kiếm được 30% lợi tức đầu tư (ở C14), tôi cấu hình thiết lập các tham số trong hộp thoại Goal Seek như sau:Tham chiếu cho Set Cell là C14Giá trị mang đến To Value là 0.3 (tức 30%)Tham chiếu đến By Changing Cell là C4
sau khi chạy Goal Seek, nó giới thiệu một giá bán đề nghị là $47.87 để giành được mức roi gần bởi mức lợi nhuận họ muốn (29,92%), như minh họa làm việc hình 15.13:
Ở hình 15.13 của bài trên, bạn thấy rằng Goal Seek đưa ra một số lượng xấp xỉ: 29.92%, chứ không phải là 30% như chúng ta mong muốn. Quý giá đó khá ngay gần (lệch chỉ 0.008) tuy thế không thiết yếu xác. Nguyên nhân Goal Seek không kiếm ra giải mã chính xác?
Câu trả lời nằm ở trong số những tùy lựa chọn mà Excel thực hiện để điều khiển và tinh chỉnh các phép tính lặp đi lặp lại (iterative calculations). Đôi khi, việc lặp đi lặp lại rất có thể phải mất một thời gian rất là dài nhằm tìm ra được lời giải chính xác, bởi vì đó, Excel đã liên minh bằng bài toán xác lập những giới hạn nhất định trong quá trình lặp lại.Để thấy những số lượng giới hạn này, bạn mở Excel Options ra rồi bấm vào ngăn Formulas(xem hình 15.14). Trong đó có nhị tùy lựa chọn xác lập cho câu hỏi lặp lại:Maximum Iterations — giá trị trong text box này chế độ số lần tái diễn tối đa. Vào Goal Seek, đó là số giá bán trị về tối đa nhưng mà Excel đưa vào ô thay đổi (changing cell) nhằm thử.Maximum Change — quý giá trong text box này là giới hạn mà Excel sử dụng để đưa ra quyết định xem nó có hội tụ đến một giải thuật hay không. Nếu hiệu số giữa lời giải hiện hành và phương châm muốn đạt được nhỏ tuổi hơn hoặc bằng giá trị này, Excel sẽ dứt lại.
Chính cực hiếm Maximum Change = 0.001 (mặc định) đã chống Excel đưa ra lời giải chính xác được cho phép tính roi ở bài trước. Trong một lần lặp lại nào đó, Goal Seek vẫn tìm ra đáp số là 0.2992 (tương đương 29.92%), nhưng bởi vì 0.008 (chênh lệch thân 0.3 và 0.2992) thì nhỏ hơn giá trị mặc định 0.001 vào text box Maximum Change, phải Excel đã ngưng quá trịnh lặp lại và giới thiệu lời giải.Để có được lời giải chính xác = 30%, xuất xắc 0.3, bạn chỉ việc sửa lại con số trong Maximum Change là 0.0001.VD 4: việc phân tích điểm hòa vốnBạn được yêu mong tìm số thành phầm tối thiểu phải phân phối được để làm cho lợi nhuận bởi 0 (nghĩa là tổng túi tiền cho sản phẩm bằng lợi nhuận từ sản phẩm), hay nói biện pháp khác, phải bán được ít nhất là bao nhiêu thành phầm thì mới hòa vốn? tùy chỉnh một phương trình roi có kết quả là 0 bằng cách thay đổi số sản phẩm sẽ phân phối ra, là giữa những thế mạnh của Goal Seek.Để thử có tác dụng điều đó, bọn họ quay lại với bài toán “Tối ưu lợi nhuận sản phẩm”. Mà lại trong trường hòa hợp này, tôi đã giả sử rằng giá cả của thành phầm đã được xác định là $47.95 (lời giải của việc trên). Để đưa ra số thành phầm tối thiểu phải bán được để hữu ích nhuận bởi 0, tôi tùy chỉnh thiết lập các tham số cho Goal Seek như sau (xem hình 15.15):Tham chiếu mang đến Set Cell là C13, là ô tính lợi tức đầu tư theo số tiền.Giá trị cho To Value là 0 (tức là kim chỉ nam của lợi nhuận, trong trường hòa hợp này)Tham chiếu mang lại By Changing Cell là C5 (số thành phầm được bán)
Và lời giải cho chúng ta được Goal Seek tìm thấy như minh họa sống hình 15.16: Phải bán tốt ít tốt nhất là 46,468 sản phẩm, thì chúng ta mới hòa vốn.
Xem thêm: Hàm So Sánh Trong Excel - Hướng Dẫn Sử Dụng Mới Nhất
VD 5: Giải phương trình đại sốCác phương trình đại số thì thường không xuất hiện thêm trong một quy mô kinh doanh, tuy nhiên, vì đây là một giữa những khả năng của Goal Seek, nhất là so với các phương trình bao gồm một biến, buộc phải tôi nhắc đến vụ việc này trên đây.Ví dụ, bạn phải giải một phương trình hơi giận dữ như sau:
Trước hết, bạn phải viết lại vế phía bên trái của phương trình theo phong cách mà Excel có thể hiểu được (với A2 là ô tượng trưng cho vươn lên là x):=(((3 * A2 – 8) ^ 2) * (A2 – 1)) / (4 * A2 ^ 2 – 5)Tiếp theo, các bạn dùng Goal Seek để xác lập phương châm cho phương pháp trên bằng 1 (vế cần của phương trình), bằng phương pháp thay đổi giá trị của A2, như minh họa sống hình 15.17 sau đây:
Hình 15.18 minh họa kết quả, với cái giá trị tại ô A2 là giải mã cho nghiệm x của phương trình. Chúng ta chú ý, tác dụng của phương trình (ô B2) không đúng mực bằng 1. Như đã nói ở bài bác 15.2.4, nếu như khách hàng cần kết quả đúng chuẩn hơn, các bạn phải biến đổi giới hạn hội tụ (Maximum Change) của Excel. Ví dụ, bạn có thể thiết lập đến Maximum Change là 0.000001.
Sắp xếp (Sort) dữ liệu
Lọc những hàng trùng lặp
Bộ lọc nâng cấp (Advanced Filter)
Bộ lọc auto (Auto Filter)Tính tổng số phụ của danh sách
Tài liệu: áp dụng Solver vào Excel 97-2010Giới thiệu hộp thoại Edit links