วิธีใช้ Goal Seek

เลขอะไรเอ่ยนำไปคูณกับเลข 3 แล้วได้ผลลัพธ์เป็นเลข 30 คุณก็ต้องตอบว่าเลข 10 เพราะคุณคิดสูตรคูณในใจออกมาได้ทันทีเลยใช่ไหมว่า 10*3 = 30 แต่ถ้าเปลี่ยนโจทย์ใหม่ว่า เลขอะไรเอ่ยนำไปคูณกับเลข 3 แล้วได้ผลลัพธ์เป็นเลข 77.77 คราวนี้สมองของคุณก็จะคิดในใจอย่างรวดเร็วว่า “ไม่ไหว คิดไม่ไหว” ถ้าต้องเสียเวลากดเครื่องคิดเลขหรือสร้างสูตรเอาเลข 77.77 ตั้งแล้วหารด้วย 3 เพื่อหาเลขอะไรเอ่ยตัวนั้นก็ได้ ย่อมแสดงว่าคุณใช้ Goal Seek ไม่เป็น

Goal Seek เป็นเครื่องมือที่ช่วยคิดย้อนโดยคุณไม่ต้องเสียเวลาสร้างสูตรใหม่เพื่อคิดย้อนเองแม้แต่น้อย เพียงแต่ว่า Goal Seek จะทำงานได้ต่อเมื่อมีเซลล์รับตัวแปรที่สามารถถูก Excel ทำหน้าที่เปลี่ยนแปลงเป็นเลขใหม่ตามต้องการ แต่หากเป็นเซลล์สูตรจะไม่สามารถใช้ Goal Seek เข้าไปแก้ไขสูตรใดๆในเซลล์ให้เป็นค่าใหม่ได้

จากโจทย์เดิม เลขอะไรเอ่ยนำไปคูณกับเลข 3 แล้วได้ผลลัพธ์เป็นเลข 77.77

  1. สร้างตัวอย่าง 2*3 ได้ 6 ตามตัวอย่างที่ผ่านมา
  2. สั่ง Data > What-If Analysis > Goal Seek 

    image112

  3. คลิกในช่อง Set cell: แล้วให้คลิกต่อไปที่เซลล์ D2 โดยเซลล์นี้ต้องเป็นเซลล์สูตรคำนวณ ส่วนช่อง To value: ให้พิมพ์เลข 77.77 ลงไป แล้วให้คลิกในช่อง By changing cell: แล้วคลิกต่อไปที่เซลล์ B2 โดยเซลล์ B2 นี้ต้องเป็นเซลล์รับตัวแปรที่กำลังต้องการหา ถือเป็นตัวเลขคำตอบใหม่ที่ต้องการ

    image114

  4. กดปุ่ม OK จะพบว่า Excel เปลี่ยนตัวเลข 2 ในเซลล์ B2 เป็นตัวเลข 25.92333 ซึ่งทำให้เกิดผลลัพธ์ที่เซลล์ C2 เป็นเลข 77.77 ตามต้องการ แล้วให้กดปุ่ม OK เพื่อยอมรับคำตอบที่ Goal Seek หาให้

    image116

  5. หากต้องการหาตัวเลขคำตอบที่ต่างจากเดิม ต้องสั่ง Data > What-If Analysis > Goal Seek ซ้ำเองใหม่

คราวนี้มาลองใช้ Goal Seek กับปัญหาที่ยากขึ้น โดยให้หาว่าตัวเลขค่าโฆษณาในเซลล์ G18 ต้องเป็นเท่าใด จึงจะคิดเป็น 15% ของกำไรขั้นต้น

image118

จากภาพตารางข้างบนนี้ เซลล์ G16 และเซลล์ H17 เป็นเซลล์รับตัวแปร ซึ่งมีค่าเป็นตัวเลขยอดขาย 1,000 บาทและตัวเลขเปอร์เซ็นต์ต้นทุนขายเท่ากับ 20% ตามลำดับ ส่วนเซลล์สูตรคำนวณมีในเซลล์ G17 มีสูตร =H17*G16 เพื่อหายอดต้นทุนขายเท่ากับ 200 บาท (คิดเป็น 20% ของยอดขาย) ส่วนเซลล์ G19 เป็นเซลล์สูตรคำนวณหากำไรขั้นต้นมีสูตร =G16-G17-G18 มาจากยอดขายหักต้นทุนขายและหักค่าโฆษณา เซลล์ที่เว้นว่างไว้คือเซลล์ G18 ให้ใช้หาค่าโฆษณา ซึ่งตอนเริ่มต้นนี้เว้นว่างไว้ จึงทำให้เซลล์ H18 ซึ่งมีสูตรคำนวณหาสัดส่วนร้อยละของค่าโฆษณาต่อกำไรขั้นต้น =G18/G19 ซึ่งขณะนี้คืนค่าเท่ากับ 0

ตัวอย่างนี้ไม่สามารถใช้วิธีคิดง่ายๆว่า ในเมื่อต้องการค่าโฆษณาเป็น 15% ของกำไรขั้นต้น ก็ให้สร้างสูตร =.15*G19 ลงในในเซลล์ G18 ก็สิ้นเรื่อง เพราะหากสร้างสูตร =.15*G19 ลงไปในเซลล์ G18 จะพบว่าเกิดการอ้างอิงแบบวงกลม (ต้องหันไปใช้การคำนวณแบบ Iteration เข้ามาช่วย ซึ่งเป็นอีกวิธีหนึ่งในการแก้ปัญหานี้) เพราะหากค่าโฆษณาเพิ่ม จะทำให้กำไรลด แล้วพอกำไรลด ย่อมส่งผลให้ค่าโฆษณาลดตาม พอค่าโฆษณาลดลง กำไรก็ต้องเพิ่ม กลายเป็นการคำนวณแบบวงกลมที่ไม่สามารถหาคำตอบสุดท้ายได้โดยวิธีธรรมดาทั่วไป

คุณสามารถแก้ปัญหานี้โดยใช้ Goal Seek โดยเริ่มต้นจากสั่ง สั่ง Data > What-If Analysis > Goal Seek

image120

  1. คลิกในช่อง Set cell: แล้วให้คลิกต่อไปที่เซลล์ H18 โดยเซลล์นี้เป็นเซลล์สูตรคำนวณหาว่าค่าโฆษณาเป็นร้อยละเท่าใดของกำไรขั้นต้น =G18/G19
  2. ช่อง To value: ให้พิมพ์เลข .15 ซึ่งเป็นตัวเลขคำตอบที่ต้องการ
  3. คลิกในช่อง By changing cell: แล้วคลิกต่อไปที่เซลล์ G18 เพื่อให้หาตัวเลขค่าโฆษณา
  4. กดปุ่ม OK > OK จะได้ตัวเลขคำตอบค่าโฆษณาเท่ากับ 104.50377964375 ที่ Goal Seek คิดให้

image122

Goal Seek เป็นเครื่องมือพิสดารที่เหมาะในการวางแผนงบประมาณ ช่วยตั้งราคาขายสินค้าเพื่อทำให้ได้กำไรตามต้องการ หรือช่วยในการคำนวณย้อนกลับในงานที่คนเราคิดเองไม่ไหวได้อย่างไม่น่าเชื่อ ซึ่งในงานวางแผนตัดสินใจนั้น แทบเป็นไปไม่ได้เลยที่เราจะมีความเชื่อมั่น 100% ต่อคำตอบที่มีอยู่เสมอไป ดังนั้นแทนที่จะเสี่ยงกับการใช้ตัวเลขเพียงตัวเดียว ขอแนะนำให้ใช้ Goal Seek คิดย้อนหาช่วงค่าที่เป็นไปได้ว่าอยู่ในช่วงตัวเลขระหว่างค่าใด เพื่อทำให้สามารถวางแผนได้อย่างยืดหยุ่นมากขึ้น เช่น เดิมเคยถามว่า ตัวเลขค่าโฆษณาในเซลล์ G18 ต้องเป็นเท่าใด จึงจะคิดเป็น 15% ของกำไรขั้นต้น ก็ให้ใช้ Goal Seek ค้นหาคำตอบที่ยืดหยุ่นกว่าเดิมว่า ตัวเลขค่าโฆษณาในเซลล์ G18 ต้องเป็นค่าระหว่างเลขใด จึงจะคิดเป็น 15% - 20% ของกำไรขั้นต้น

Related Articles

© Copyright 1999

สงวนลิขสิทธิ์ตามกฎหมาย

ห้ามนำข้อความหรือส่วนหนึ่งส่วนใดของบทความหรือวิดีโอหรือรูปภาพไปใช้เพื่อการค้าขาย หรือเพื่อประโยชน์ส่วนตัว

อนญาตให้นำไปใช้เพื่อสาธารณประโยชน์โดยขอให้ระบุที่มาและชื่อผู้เขียนกำกับไว้ด้วยเสมอ

ลิงก์เว็บ Excel Expert Training

เว็บสำหรับ เรียนออนไลน์

เว็บสำหรับ เรียนแบบกลุ่ม-ส่วนตัว

ติดตามข่าวสารได้จาก facebook

ถามปัญหาได้ที่ กลุ่มคนรัก Excel

และไลน์กลุ่ม Excel Expert Group

ที่อยู่และการติดต่อ

สมเกียรติ ฟุ้งเกียรติ 7/1 รามคำแหง ซอย 35 หัวหมาก บางกะปิ กทม 10240 โทร 097-140-5555, 02-718-9331

Excel@ExcelExpertTraining.com

sfk234x234