Excel Tips Part 7

เมื่อเปิด File ไม่ได้หรือเผลอลบ File ไปจะทำอย่างไร

• เมื่อเปิดด้วย Excel ไม่ได้ ให้ลองใช้โปรแกรมอื่นเปิดแทน
• ใช้ Windows Explorer (File Manager) หา file แล้วลอง double click ที่ file เพื่อเปิดโดยไม่ผ่าน Excel
• สร้างสูตร Link ไปยัง file ที่เปิดไม่ได้แล้วใช้ Edit > Links > Update Now
• ถ้าลบ file ไปแล้วให้ใช้ Recycle Bin แก้ไข Recover ขึ้นมา แต่ขนาดของ Recycle Bin ที่เหลืออาจมีไม่พอเพียงที่จะเก็บ File เก่าที่ Delete ไปแล้วก็ได้ ดังนั้นคุณควรคอยล้าง Recycle Bin ให้ว่างไว้เสมอหรือเพิ่มขนาดของ Recycle Bin ขึ้นอีก

---

วิธีสร้างสูตรที่เกี่ยวข้องกับเวลา ประโยชน์ของสูตร Mod, Match, Index และสูตรอื่นของมืออาชีพที่มักถูกมองข้าม

• สูตร Date (Year, Month, Day) เช่น Date(1998,2,14) จะให้ค่าเป็น Date Serial Number แล้วแสดงผลลัพธ์ตาม Format > Cells > Number > Date ที่คุณเลือกไว้เป็น 14 February 1998 (หากต้องการแสดงปีไทยให้เลือก Format ที่มีอักษรไทย)
• ระบบการนับวันเวลาของ Excel จะใช้แบบสากลเสมอ โดยเริ่มนับ 1/1/1900 0:00:00 ให้ค่า Serial Number เท่ากับ 1.0 และใช้ส่วนของเลขเศษทศนิยมแทนสัดส่วนของวันซึ่งแปลงเป็น Format ของเวลาจาก 0:00:00 - 23:59:59 น.
• DATE(YEAR(H7)+$G$8,MONTH(H7)+$G$9,DAY(H7)+$G$10) จะปรับวันจาก Serial Number ใน H7 เพิ่มขึ้นอีกทีละ $G$8 ปี และ $G$9 เดือน และ $G$10 วัน
• DATE(YEAR(H8),MONTH(H8)+1,0) จะปรับวันจาก Serial Number ใน H8 เพิ่มขึ้นอีกทีละ 1 เดือน และกำหนดนับจากวันที่ 0 ทำให้ได้วันที่สิ้นสุดของเดือน H8
• DATE(YEAR(J8),MONTH(J8),1) จะปรับวันจาก Serial Number ใน J8 โดยเลือกเฉพาะวันที่ 1
• Day(Serial Number) ให้ผลเป็นวันที่ (Serial Number ได้จากสูตร Date, Now)
• Month(Serial Number) ให้ผลเป็นเลขที่เดือน (1 = มกราคม)
• Year(Serial Number) ให้ผลเป็นปีค.ศ.
• Hour(Serial Number), Minute(Serial Number) ให้ผลเป็นเวลาชั่วโมง และนาที
• MOD(number, divisor) ให้ผลเป็นเศษที่เหลือจากการหาร เช่น Mod(9,7) = 2 โดยคำนวณจาก 9 / 7 เหลือเศษ 2
• MATCH(lookup_value, lookup_array, match_type) เพื่อหาว่า lookup value อยู่ในตำแหน่งใดใน lookup array โดยนับตำแหน่งจากบนลงล่าง หรือจากซ้ายไปขวา ส่วน match type เลือกใช้ -1,0,1 ตามลักษณะของการเรียงลำดับของข้อมูลจากมากไปน้อย จากไม่เรียงลำดับ หรือเรียงจากน้อยไปมาก
• INDEX(array, row_num, column_num) เพื่อหาค่าที่อยู่ในพิกัดระหว่าง row number และ column number ตัดกันใน array ที่เป็นตารางข้อมูล

---

วิธี Cloning file เดียวเป็นหลาย file โดยไม่ต้องสร้าง file ใหม่อีก

• โปรดศึกษาจาก file ตัวอย่างเป็นหลัก และใช้คำอธิบายนี้ประกอบการแยกสูตร
• File ชื่อ Clonfile.xls เป็น file สูตรที่ใช้เก็บสูตรมาตรฐานไว้ โดยนำค่าที่ link มาจากfile input ซึ่งใช้สูตร Index เพื่อเลือกค่ามาคำนวณ แล้วส่งผลลัพธ์กลับจาก Clonfile.xls ไปยัง file output ในส่วนที่ใช้ Data Table
• ตัวอย่างที่ใช้นี้ไม่ได้แยกส่วนของ Input ออกจาก Output เป็นคนละ File แต่คุณอาจใช้หลักการเดียวกันนี้แยกส่วนดังกล่าวออกจากกัน กล่าวคือ
• แยกส่วน File ที่ใช้เก็บข้อมูล Input แล้วใช้สูตร Index เพื่อเลือกชุดข้อมูลที่ต้องการมาคำนวณโดยส่งค่าไปยัง file สูตร
• แยกส่วน file ที่ใช้รับผลลัพธ์ Output จาก file สูตร โดยใช้ตาราง Data Table ทำหน้าที่ยิงค่าดัชนีของชุด Input ไปยังสูตร Index แล้วรับผลคำนวณกลับมายัง Table
• ประโยชน์ของการใช้ Index ร่วมกับ Data Table ทำให้ใช้ตัวแปรได้ไม่จำกัดจำนวน
• ในกรณีที่ธุรกิจมีทางเลือกหลายๆกรณี อาจใช้ Index ซ้อน Index เพื่อเรียกใช้ข้อมูลจากตาราง database หลายๆชุด เช่น ตารางสินค้ามีหลายชนิด และสินค้าแต่ละชนิดยังอ้างถึงตารางสูตรส่วนผสมหลายๆแบบ และอ้างถึงตารางเงื่อนไขการกำหนดราคาขายต่างกันไปอีก
• นอกจากนี้ยังใช้ตาราง Data Table ซ้อนตาราง Data Table ได้อีก เพื่อนำผลจากตารางแรกไปใช้เป็นผลลัพธ์อีกรูปแบบในตารางที่สอง เช่น ตารางแรกใช้หาผลคำนวณรายรับ จากการขายสินค้าประเภทหนึ่งในแต่ละเดือนซึ่งรวมกันเป็นยอดขายทั้งปี แล้วใช้ตารางที่สองคำนวณหายอดขายทั้งปีของสินค้าทุกชนิด โดยตารางแรกใช้เลขที่เดือนเป็นตัวแปร ส่วนตารางที่สองใช้เลขที่สินค้าเป็นตัวแปร แต่ละตารางใช้ตัวแปรนี้ยิงไปที่สูตร Index ซึ่งประโยชน์จะเกิดขึ้นมหาศาลได้นั้น ต้องเริ่มต้นจากการแยกตัวแปรออกจากสูตรเป็นหลักแรกที่สำคัญที่สุด ทำให้ได้ผลลัพธ์โดยไม่ต้องใช้ VBA สั่งคำนวณเป็น Loop แม้แต่น้อย

---

วิธีสร้างสูตร SUM สูตรเดียว เพื่อหาผลรวมเฉพาะส่วนหรือตามเงื่อนไข โดยไม่ต้องกลับมาสร้างสูตรใหม่

• คุณต้องใช้สูตร Sum และสูตร IF ร่วมกันโดยสร้างขึ้นแบบ Arrays Formula ซึ่งต้องกด Ctrl + Shift + Enter พร้อมกันเพื่อทำให้เกิดเครื่องหมายวงเล็บปีกกาขึ้นปิดหัวและท้ายสูตร
• Excel มีสูตรสำเร็จรูปของ SumIF และ CountIF ไว้ให้ใช้แล้วโดยคุณไม่ต้องสร้างสูตรขึ้นมาเองก็ได้ แต่สูตรสำเร็จรูปนี้ไม่สามารถดัดแปลงให้ใช้กับ Average, Max, Min ฯลฯ จึงขอแนะนำให้เรียนรู้การสร้างสูตร Arrays ของ Sum และ IF ขึ้นเอง
• หากต้องการผลรวมของ TotalCost เฉพาะส่วนที่รหัส Code = ค่าที่ต้องการ ซึ่งในตัวอย่างเป็นค่าในช่อง C11 ใช้สูตร {=SUM(IF(Code=$C$11,TotalCost))}
• หากต้องการผลรวมของ TotalCost เฉพาะส่วนที่รหัส Code ไม่เท่ากับ ค่าที่ต้องการซึ่งในตัวอย่างนี้เป็นค่าในช่อง C11 ใช้สูตร {=SUM(IF(Code<>$C$11, TotalCost))} สังเกตว่าใช้เครื่องหมาย <> แทนซึ่งมีความหมายว่า ไม่เท่ากับ
• หากต้องการผลรวมของ TotalCost โดยใช้การคำนวณของ UnitSold x Cost แทนการใช้ค่าของ TotalCost โดยตรง เฉพาะส่วนที่รหัส Code = ค่าที่ต้องการซึ่งในตัวอย่างนี้เป็นค่าในช่อง C11 ใช้สูตร {=SUM(IF(Code=$C$11,UnitSold*Cost))}
• หากต้องการผลรวมของ TotalCost โดยใช้การคำนวณของ UnitSold x Cost แทนการใช้ค่าของ TotalCost โดยตรง เฉพาะส่วนที่รหัส Code ไม่เท่ากับ ค่าที่ต้องการซึ่งในตัวอย่างนี้เป็นค่าในช่อง C11 ใช้สูตร {=SUM(IF(Code<>$C$11,UnitSold*Cost))}
• ในกรณีที่ต้องการใช้สูตรเดิม แต่แยกเงื่อนไขการคำนวณเป็น 2 วิธีซึ่งสามารถหาผลลัพธ์ตามตัวแปรที่คุณกำหนดภายหลังได้ เช่น กำหนดค่า 1 = หาค่าที่ต้องการ หรือ กำหนดค่า 0 = หาค่าที่เหลือ ให้ใช้สูตร IF ในส่วนของ True หรือ False ช่วย ดังนี้

รูปแบบ {=SUM(IF(Check,TRUE,FALSE)*สูตรอื่นๆ)}
{=SUM(IF(Code=$C$11,1,0)*UnitSold*Cost)}
{=SUM(IF(Code=$C$11,0,1)*UnitSold*Cost)}

• นอกจากการใช้สูตร SUM with IF แล้วยังใช้แค่สูตร Sum เฉยๆโดยไม่ต้องใช้ IF ร่วมด้วยก็ได้ โดยตัดส่วนของ IF ออกแล้วนำมาคูณกับสูตรคำนวณหา TotalCost เลย ดังนี้

{=SUM((Code=$C$11)*TotalCost)}สูตรส่วน (Code=$C$11) จะให้คำตอบเป็น Arrays ของ True/False เป็น {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE} โดยค่า True หมายถึงตำแหน่งที่ทำให้ (Code=$C$11) เมื่อนำมาคูณกับ Arrays TotalCost จึงให้ผลลัพธ์เฉพาะลำดับข้อมูลที่ตรงกับลำดับค่าที่เท่ากับ True เท่านั้น

• หากต้องการหาผลรวมของรหัส Code มากกว่าค่าเดียว ใช้สูตรทำนองเดียวกันดังนี้

{=SUM(((Code=$C$16)+(Code=$C$17))*TotalCost)}
{=SUM(((Code<>$C$16)*(Code<>$C$17))*TotalCost)}

---

สูตรอะไรที่นำมาใช้แทนคำสั่ง Sort, Filter, และ Pivot Table

• ปกติการใช้คำสั่งเมนูนั้นต้องใช้มนุษย์ในการเรียกใช้คำสั่ง ซึ่งทำให้ไม่สามารถใช้ Excel แบบคอมพิวเตอร์ได้สมบูรณ์ จุดประสงค์ของการใช้สูตร จึงมุ่งให้คุณได้ผลลัพธ์ที่ต้องการในทันทีที่กด F9 หรือทันทีที่เปิด File ที่สั่ง Tools > Options > Calculation > Automatic Calculation ไว้แล้ว
• โปรดศึกษาจากตัวอย่างประกอบ และใช้คำอธิบายนี้เป็นแนวทางการคิดสร้างสูตร
• สูตรที่ใช้แทนการใช้คำสั่ง Data > Sort สมมติต้องการเรียงลำดับของ Customer Code จากน้อยไปหามาก ใช้สูตร Small, Row, Index ผสมกันเป็นสูตรใหม่ ดังนี้

{=SMALL(CustomerCode, ROW(CustomerCode)- INDEX(ROW(CustomerCode),1)+1)}

• สูตรที่ใช้แทนการใช้คำสั่ง Data > Filter ใช้สูตรเดิมข้างต้นแต่ผสมสูตร IF เพื่อทำให้ตัวเลขอื่นที่ไม่ตรงกับความต้องการหลุดออกจากการ Sort ลำดับต้นๆ
• สูตรที่ใช้แทนการใช้คำสั่ง Data > Pivot Table ใช้สูตร Arrays IF เพื่อหาผลลัพธ์ระหว่างตัวแปรที่ต้องการให้แสดงบนหัวตาราง แล้วใช้ Data Table ยิงค่า Input ไปยังสูตร Arrays IF