วิธีใช้ Data Consolidation
คำสั่ง Data > Consolidate จะช่วยสร้างสูตรลิงก์หายอดรวมตัวเลขจากหลายตารางให้ทันที จากตารางที่อยู่ต่างชีทหรือต่างแฟ้มกัน ซึ่งโครงสร้างตารางอาจเหมือนกันหรือต่างกันก็ได้
คำสั่ง Data > Consolidate จะช่วยสร้างสูตรลิงก์หายอดรวมตัวเลขจากหลายตารางให้ทันที จากตารางที่อยู่ต่างชีทหรือต่างแฟ้มกัน ซึ่งโครงสร้างตารางอาจเหมือนกันหรือต่างกันก็ได้
ต้องการหายอดรวมของ Amount ตามรหัส Id ที่กรอกไว้ในพื้นที่สีเหลือง จะสร้างสูตรอย่างไร โดยในพื้นที่สีเหลืองจะใส่หรือไม่ใส่รหัสอะไรก็ได้ ใส่รหัสที่ไม่มีก็ได้
ปัญหาแบบนี้เขาเรียกว่า
SUM with Multiple Criteria on the Same Column
แปลว่า วิธีหายอดรวมจากหลายเงื่อนไขที่มาจาก column เดียวกัน
ลองนำประโยคภาษาอังกฤษนี้ไปค้นหาจาก google ดูจะพบว่ามีผู้ให้คำตอบเยอะแยะไปหมด
ใช้ SumIF บวกกัน 3 สูตร
หรือใช้สูตร =SUM(SUMIF(Id, {“a002″,”a003″,”a005”}, Amount))
ผมไม่อยากให้ทำแบบสูตรนี้ เพราะการใส่รหัสแบบค่าคงที่ลงไปในวงเล็บของสูตร ขัดกับหลักการสร้างสูตรที่ดี
หรือใช้สูตร =SUMPRODUCT(((Id=E3)+(Id=E4)+(Id=E5))*Amount)
Download ตัวอย่างได้จาก
https://drive.google.com/file/d/1X2GUM1Aw6f3KeuFt90_Fs8REnt71xLde/view?usp=sharing
เป็นยังไงครับ รู้สึกตื่นเต้นไหมว่า Excel สามารถหาคำตอบแบบนี้ได้ด้วย
ที่น่าแปลกมาก คือ สูตร SumIFs กลับใช้กับปัญหาแบบนี้ไม่ได้
=SumIFs(Amount,Id,E3,Id,E4,Id,E5)
จะหาคำตอบไม่ได้ ถึงจะใช้ Excel 365 ก็หาคำตอบไม่ได้
บางคนเห็นสูตรมาเยอะ อาจไม่ตื่นเต้นอะไรเลย
เท่าที่ค้นหาจาก google หนีไม่พ้นสูตร 3 แบบแค่นี้แหละครับ ทั้งโลกเขาใช้กันแบบนี้
นี่เป็นเรื่องที่อยากเล่าให้ฟังครับ จะได้ทราบว่าสูตรที่ผมสอนให้ใช้ในหลักสูตร Excel Dynamic Reports นั้นยังไม่เห็นใครที่ไหนเขาทำกัน
หากสามารถทำแบบนี้ได้ จะเกิดประโยชน์อย่างมาก
สามารถหายอดรวมของ Amount ตามรหัส Id ที่กรอกไว้ในพื้นที่สีเหลือง จะสร้างสูตรอย่างไร โดยในพื้นที่สีเหลืองจะใส่หรือไม่ใส่รหัสอะไรก็ได้ ใส่รหัสที่ไม่มีก็ได้
อีกหน่อยจะขยายพื้นที่ตารางสีเหลืองให้ใหญ่ขึ้นเป็นหลายเซลล์หลาย column ก็ยังหาคำตอบได้
สูตรจ้อยๆเพียงสูตรเดียวนี่แหละครับที่เปิดทางให้เราสามารถสร้างรายงานที่ไม่มีใครที่ไหนทำได้มาก่อน
นอกจากเรื่องสูตรนี้แล้ว ยังมีอีกหลายเรื่องที่ผมสอนให้ใช้แบบธรรมดาๆ ไม่น่าตื่นเต้นอะไร แต่ไม่ใช่วิธีการที่คนอื่นเขาสอนให้ใช้กัน
เชิญสมัครเข้าเรียนออนไลน์ได้ที่
https://www.excelexperttraining.com/365/index.php/online-training/registering/task-ready
ราคาค่าอบรมจ่ายน้อยกว่าประโยชน์ที่คุณจะได้รับมากมาย
ยุคนี้ใครๆก็หันมาสนใจเรื่อง Big Data อยากเรียนอยากใช้ Power Query, Power BI, หรือ Tableu ทำรายงาน สร้างภาพออกมาเป็นกราฟกันทั้งนั้น ถ้าคุณเคยไปเรียนมาแล้ว กำลังอยากจะไปเรียน หรืออยากจะใช้โปรแกรมพวกที่มี Power นำหน้าเหล่านี้ ขอถามหน่อยว่า ตัวคุณมี Power สามารถตอบคำถามต่อไปนี้ได้ถูกต้องหรือเปล่า
สมการทางคณิตศาสตร์ Y = aX +b
ถ้าค่า Y กับ X มีความสัมพันธ์กันสูงมาก คำนวณค่า R-Square ได้เกือบเต็มร้อย
“สมมติว่า X เป็นจำนวนปี ส่วน Y เป็นยอดขาย”
“เมื่อค่า X จำนวนปีเพิ่ม ย่อมทำให้ค่า Y ยอดขายเพิ่มตาม” ใช่หรือไม่
คำถามนี้อาจารย์ระดับโปรเฟสเซอร์ชาวอินเดีย ยกขึ้นมาถามในห้องเรียนวิชาสถิติ สมัยที่ผมเรียน MBA ที่ University of Wisconsin
เป็นยังไงครับ ตอบได้ไหม ตอบกันว่ายังไง ใช่ หรือ ไม่ใช่
ใครที่คิดจะทำงานเกี่ยวข้องกับ Big Data ไม่ใช่แค่คิดว่าจะกดปุ่มให้โปรแกรมทำงานแล้วสร้างกราฟออกมา จบแค่ดูสวยดีแค่นั้น แต่ต้องสามารถสร้างรายงานที่ดี มีความหมาย สามารถใช้ในการทำนายหรือพยากรณ์ผลการทำธุรกิจในอนาคตได้ด้วย
ผู้บริหารและเจ้าของธุรกิจทุกคนต้องการความมั่นใจว่า เงินที่เขาลงทุนไป จะให้ผลตอบแทนได้ตามที่ตั้งใจไว้หรือไม่
คำถามนี้อาจารย์ให้เวลาคิดตั้งอาทิตย์นึง พอเข้ามาในห้องก็ถามนักเรียนว่า เป็นยังไง ได้คำตอบกันมาว่ายังไง
ทุกคนในห้องยกมือตอบว่า “ใช่”
อาจารย์ตอบว่า “ใช่” เหมือนกัน แล้วอธิบายต่อว่า ทุกคน ถูกแค่ครึ่งเดียว
คำตอบอีกครึ่งหนึ่ง ในทางคณิตศาสตร์ ต้องตอบด้วยว่า เมื่อค่า Y เพิ่มย่อมทำให้ค่า X เพิ่มได้เช่นกัน
เมื่อยอดขายเพิ่ม ย่อมทำให้จำนวนปีเพิ่มด้วย ซึ่งขัดกับความเป็นจริง เรื่องของจำนวนปีมันเพิ่มของมันอยู่แล้วตามธรรมชาติ
ในหลักเหตุผล ยอดขาย ไม่ได้เป็นสาเหตุ ทำให้จำนวนปีเพิ่ม
“ในแง่ของคณิตศาสตร์ ไม่ได้คำนึงถึงหลักเหตุผลตามความเป็นจริง”
นี่คือคำสอนที่ต้องจดจำไว้ชั่วชีวิต
แล้วยังไง มาคิดกันเองต่อ คราวนี้ยกกลับมาใช้กับ Excel บ้าง
Excel มีกราฟประเภท Scatter X-Y ให้ใช้กันอยู่ใช่ไหม รายงานเรื่องยอดขายนี้ต้องวางข้อมูลให้ถูกข้าง
ในหน้ารายงาน เรื่องของปีต้องจัดวางเป็น column ซ้ายสุด จากนั้น column ถัดไปจึงเป็นตัวเลขยอดขาย พอนำไปสร้างเป็นกราฟก็จะออกมาเป็นภาพตามนี้ แกนนอนของกราฟเป็นเลขปี แกนตั้งของกราฟเป็นยอดขาย
หรือคิดว่าจะใช้จำนวนปีแทน ตามกราฟต่อไปนี้ จึงจะสมเหตุสมผลกว่ากันครับ
เฉลย การหาสมการเส้นแนวโน้ม
ตัวเลขปีที่ใช้ในการคำนวณ ควรใช้เลขลำดับปี 1, 2, 3 … ซึ่งทำให้สมการเส้นแนวโน้มได้ตัวเลขที่สมกับความเป็นจริง ตามภาพกราฟสีเขียว เมื่อแทนค่า x เท่ากับ 0 ลงไปจะได้ตัวเลขเริ่มต้นของรายได้ 94.543
ถ้าใช้ตัวเลขปีค.ศ.ในการพยากรณ์ สมการที่ได้จะไม่สมเหตุผล เพราะต้องใช้เวลาหลายพันปีทีเดียวกว่าจะเกิดยอดขายขึ้นมาให้เห็น ส่วนในสมการที่คำนวณได้เมื่อใช้เลขปีค.ศ.แทนค่า x = 0 ลงไปจะได้ยอดขายตัวเลข ติดลบ 33712 ซึ่งไม่สมเหตุผลเข้าไปใหญ่
ในการแสดงภาพด้วยกราฟจะใช้เลขปีค.ศ.หรือพ.ศ.ได้ทั้งนั้น แต่เมื่อใช้ในการคำนวณต้องใช้เลขนับจำนวนปีนะครับ
ผู้ที่ใช้ตัวเลขเหล่านี้คือผู้บริหารครับ ระวังตัวกันหน่อย
ส่วนวิธีหาสมการเส้นแนวโน้ม ทำได้ไม่ยาก ไม่ต้องเสียเวลาไปสร้างสูตร
แค่คลิกขวาไปที่เส้น เลือกคำสั่ง Add Trendline
จากนั้นในจอขวามือที่เปิดขึ้น ให้เลือกประเภทของแนวโน้มที่น่าจะเป็น แล้วกาช่องด้านล่างเพื่อแสดงสูตรของเส้นแนวโน้มพร้อมกับ R-Squared
คุณได้ Login เรียบร้อยแล้ว
เชิญไปที่เมนู Online Training > เนื้อหาสำหรับสมาชิก
คลิกเลือกตามรายชื่อหลักสูตรที่ลงทะเบียนไว้