Sawasdee Branded Hangbags and Fashion: 10/11/10

Monday, October 11, 2010

Excel Tips Part 8

สรุปปุ่มสำคัญ

F1 Help
F2 Edit
F3 Insert Name ใช้คู่กับเครื่องหมาย =
F4 ใส่เครื่องหมาย $
F4 ทวนคำสั่งล่าสุดซ้ำ
F5 Goto
F9 คำนวณทั้งหมด
Shift+F9 คำนวณเฉพาะ sheet
Ctrl+Alt+F9 คำนวณอย่างสมบูรณ์ ใช้กรณีแก้ Bug
Alt+Enter ขึ้นบรรทัดใหม่ในเซลล์เดิม
Ctrl+Enter พิมพ์ซ้ำลงไปในเซลล์ทั้งหมด

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

Excel Tips Part 6

วิธีจัดขนาดหน้าจอใหม่ให้อ่านตัวอักษรได้ง่าย ทำงานสบาย โดยไม่ต้องซื้อจอขนาดใหญ่

Start > Settings > Control Panel > Displays > Settings แล้วเลือกระดับความละเอียดของจอ หากจอมีขนาด 14 - 15 นิ้วควรเลือก 800 x 600 Pixels ถ้าจอใหญ่ขึ้นควรเลือกความละเอียดมากขึ้น ซึ่งในขณะที่จอมีความละเอียดมากขึ้น จะทำให้คุณสามารถดูเนื้อที่ในตาราง Excel ได้หลาย Row/Column มากขึ้น แต่ในขณะเดียวกันระดับการแสดงสีบนจอจะลดลง ทั้งนี้ขึ้นกับคุณสมบัติของ VGA Card ของเครื่องคอมพิวเตอร์ด้วย การกำหนดแบบนี้จะมีผลถาวรต่อทุกโปรแกรม
View > Zoom เพื่อลดหรือขยายขนาดตัวอักษรบนจอ ซึ่งจะมีผลเฉพาะการแสดงผลบนจอของ Sheet นั้นๆ แต่ไม่มีผลต่อรูปแบบการพิมพ์
อย่าใช้ Font ขนาดต่างๆกันมากเกินไปเพื่อให้มองชัด โดยหลักการทำงานที่ดีควรเลือกใช้ Font ตัวอักษรไม่เกิน 1-2 แบบ และใช้ขนาดตัวอักษรไม่เกิน 1-2 ขนาด และคุณสามารถใช้การขยับช่องไฟระหว่างตัวอักษรให้อ่านง่ายขึ้น
อย่าใช้ Pattern มากไป โดยเฉพาะงานที่ต้องส่ง Fax เพราะจะทำให้ปลายทางได้รับเอกสารไม่ชัดเลย แต่ควรใช้สำหรับงานที่ให้ความสำคัญกับเอกสารต้นฉบับ
Window > Freeze Panes เพื่อกัน Row/Column ด้านบน/ซ้าย ไม่ให้เลื่อนโดยคุณต้องเลือกตำแหน่งช่องที่ต้องการ ให้เป็นช่องซ้ายบนสุดของพื้นที่ส่วนที่เลื่อนได้ก่อน แล้วจึงค่อยเลือกใช้คำสั่งนี้ เวลาจะล้างออกให้เลือก Window > Unfreeze Panes

ถ้าเดิมสร้างสูตรคำนวณไว้เป็นหลักบาท แล้วต่อมาต้องการเปลี่ยนเป็นหลักพันบาท จะแก้อย่างไรในพริบตา

Format > Cells > Number > Custom ใช้เครื่องหมาย Comma , ต่อท้ายเพื่อนับทีละพัน เช่น #,##0, (เฉพาะการแสดงตัวเลขเท่านั้นที่เปลี่ยนไปตาม Format แต่ค่าของตัวเลขยังคงเท่าเดิม)
=THAINUMSTRING(E9) &"บาท" ช่วยแปลงตัวเลขเป็นอักษรไทยให้ได้ แต่ไม่สามารถแยกส่วนสตางค์ออกมาจากบาท

วิธีพิมพ์แบบ Fit to Page ที่สามารถแบ่งขึ้นหน้าใหม่ได้ตามต้องการ

วิธีที่ 1 : View > Page Break Preview แล้วใช้ mouse ลากเส้นประแบ่งหน้าไปส่วนที่ต้องการ
วิธีที่ 2 : ปกติเมื่อเลือก File > Page Setup > Page > Fit to page จะเลือกขึ้นหน้าใหม่ในส่วนที่เราต้องการไม่ได้ เพราะโปรแกรมจะเลือกให้เข้ากับขนาดของหน้าให้เอง (แม้จะใช้ Insert > Page Break ด้วยก็ตาม ในตัวอย่างสมมติขึ้นหน้าใหม่ใน FY 13)
วิธีแก้ไข

เลือก Insert > Page Break ตามส่วนของ Column/Row ที่ต้องการขึ้นหน้าใหม่
ใน File > Page Setup > Page เลือก Fit to Page เพื่อหา % ที่ใกล้เคียงพอจะทำให้พื้นที่ข้อมูลที่กำหนดถูกพิมพ์ย่อลงในจำนวนหน้าตามต้องการ
กลับมาเลือกใช้ Adjust to % แทน Fit to Page แล้วปรับเลข % อีกเล็กน้อย
ใช้ Print Preview ตรวจสอบดูว่า งานพิมพ์ถูกแบ่งขึ้นหน้าใหม่ตามต้องการหรือยัง หากยังให้กลับไปปรับเลข % ใหม่อีก

วิธีพิมพ์หลายพื้นที่จากต่าง Sheet ด้วยคำสั่งเดียว ไม่ต้องใช้ VBA

กรณี พิมพ์หลายพื้นที่ใน sheet เดียวกันให้ File > Page Setup > Sheet > Print Area ใส่พื้นที่หรือชื่อพื้นที่ ซึ่งต้องการพิมพ์หลายๆส่วนโดยคั่นด้วย comma ระหว่างแต่ละพื้นที่
กรณี พิมพ์หลายพื้นที่จากหลายๆ sheet ให้กด Ctrl ค้างไว้แล้วใช้ mouse คลิ้กชื่อ Sheet Tab ที่ต้องการพิมพ์ (สังเกตว่า ชื่อ File ด้านบนของจอจะมีคำว่า Group) แล้วสั่ง File > Print ตามปกติ
กรณีที่คุณต้องการจัดหน้าจอหลายๆแบบ และให้พิมพ์ตามแบบหน้าจอนั้นๆ ให้ใช้ Custom View เพื่อตั้งชื่อ (Add Name) ให้กับหน้าจอแบบที่คุณต้องการ แล้วใช้ Report Manager ตั้งชื่อชุดงานพิมพ์แล้วจึงเลือก Add หาชื่อ View ที่คุณต้องการเข้ากับชุดงานพิมพ์ ตามขั้นตอนต่อไปนี้

เตรียมจัดรูปแบบแต่ละ Sheet ให้พร้อมและกำหนด Page Setup ตามต้องการ
View > Custom Views > Add สำหรับตั้งชื่อให้กับ Sheet แต่ละรูปแบบ ซึ่ง Excel จะจดจำรูปแบบการพิมพ์ที่คุณกำหนดไว้ใน Page Setup ไว้กับแต่ละ View อีกด้วย
ใช้ View > Report Manager แล้ว Add ตั้งชื่อ Report Name แล้วเลือก Sheet และ View ที่เคยตั้งชื่อไว้แล้ว Add เข้ากับ Report Name นั้น เมื่อกลับไปใช้ Report Manager อีกจะสามารถสั่งพิมพ์แต่ละชุดของ Report ที่คุณต้องการ (ในแต่ละชุดของ Report ประกอบไปด้วย View หลายๆแบบ)

ถ้าบนเมนูไม่มี Report Manager ให้ Install ผ่าน Tools > Add-ins แล้วกากบาดหน้าบันทัด Report Manager
ถ้าในรายชื่อของ Add-ins ที่ปรากฏไม่มีบันทัดของ Report Manager แสดงว่าไม่ได้ Install ไว้ จะต้อง Install Excel แบบสมบูรณ์ใหม่

ถ้า File ของคุณ มีขนาดใหญ่มากๆ เกินกว่าจะเก็บลงแผ่นขนาด 3.5 นิ้วได้ จะทำอย่างไร

Save ลงใน Hard disk แล้วใช้โปรแกรม zip เช่น Norton Commander หรือ WinZip ทำการ compress บีบอัดข้อมูลให้เล็กลง เมื่อต้องการใช้งานก็ต้อง Decompress ขยายกลับเป็นขนาดปกติก่อนจึงจะใช้งานต่อได้
แบ่งเป็น file ขนาดเล็กลง แล้ว Link file เพื่อไม่ให้มีขนาดข้อมูลใหญ่เกินไป
เมื่อแบ่ง File เล็กลงแล้ว ใช้ File > Save Workspace เพื่อเก็บชื่อแฟ้มข้อมูลที่ต้องใช้ร่วมกัน ทำให้เกิด File ใหม่มีนามสกุล Xlw ซึ่งใช้เก็บเฉพาะชื่อ File แต่เมื่อเปิด File Xlw นี้จะทำให้เปิด File ชุดที่เคยใช้งานร่วมกันให้เอง ทั้งนี้เมื่อทำงานเสร็จแล้วต้อง save ข้อมูลใหม่เสมอทุกครั้งแม้จะใช้ Save Workspace แล้วก็ตาม

วิธีใช้ Excel ให้เต็มกำลัง
ผู้ใช้ Excel สมัยนี้ได้รับความสะดวกจากการใช้โปรแกรม Excel มากขึ้นกว่าแต่ก่อน เนื่องจาก Excel จัดเตรียมเมนูต่างๆไว้ช่วยคุณทำงานได้ง่ายขึ้น และได้ผลลัพธ์เร็วขึ้นกว่าเดิม ผู้ใช้ส่วนใหญ่ยึดติดกับความคล่องตัวเหล่านี้จนมักคิดเข้าใจผิดไปว่า เมื่อตนสามารถใช้ทุกเมนูคำสั่งของ Excel ก็แสดงว่าใช้ Excel ได้เต็มที่แล้ว ผู้เข้าอบรมบางคนอาจนึกเข้าใจผิดไปอีกว่า สุดยอดเคล็ดลับและลัดของ Excel แฝงไว้กับการใช้คำสั่งบนเมนู แต่เมื่อเข้าอบรมจริงกลับพบว่า เนื้อหาการอบรมเพียงบางส่วนเท่านั้นที่นำลูกเล่นของการใช้เมนูมาอธิบาย อาจทำให้เกิดความสับสนไม่เข้าใจว่าสิ่งที่วิทยากรแนะนำนั้นมีความสำคัญอย่างไร
สาเหตุสำคัญที่ทำให้ผู้ใช้ Excel ส่วนมากหลงเข้าใจผิดว่า ตนมีความรู้ความเข้าใจและสามารถใช้งานได้เต็มกำลังแล้ว เป็นเพราะการขาดโอกาสเรียนรู้ หรือไม่เคยพบเห็นงานที่สร้างขึ้นด้วยศาสตร์และศิลป์ชั้นสูงมาก่อน (คุณสามารถเรียนรู้จากตัวอย่างงานซึ่งแสดงท้ายการอบรม หรือเรียนรู้อย่างเต็มที่จากหลักสูตรฉลาดใช้ Excel ในงานธุรกิจอย่างมืออาชีพ ซึ่งเป็นหลักสูตรต่อเนื่องจากหลักสูตรนี้) เมื่อจำกัดมุมมองเช่นนี้จึงส่งผลให้ไม่มีแนวคิดนำ Excel ไปประยุกต์ใช้ได้เต็มที่ อีกทั้งบางคนที่เคยผ่านการฝึกอบรมหลักสูตร Excel ซึ่งเรียกชื่อหลักสูตรว่าเป็นขั้น Advance เลยทำให้ตนเข้าใจผิดไปอีกว่า ตนรู้วิธีใช้ Excel หมดแล้ว และไม่เฉลียวใจแม้แต่น้อยว่า ยังมีขั้นที่เหนือกว่าหลักสูตร Advance ขั้นสูงนั้นอยู่อีกมากมายนัก
การที่หลักสูตร Excel ในท้องตลาดทั่วไป แบ่งหลักสูตรออกเป็นขั้นพื้นฐานกับขั้น Advance ซึ่งมักใช้ชื่อภาษาไทยว่าหลักสูตรขั้นสูง หรือบางแห่งถึงกับอ้างว่าเป็นขั้นมืออาชีพ เนื่องจากเนื้อหาของ Excel มีมากมาย ยากจะจัดอบรมได้ครบถ้วนในหลักสูตรเดียว จึงเรียกเนื้อหาที่แบ่งสอนช่วงแรกว่าเป็นขั้นต้นหรือขั้นพื้นฐาน และเรียกเนื้อหาส่วนถัดมาว่าเป็นขั้นสูง ทำนองเดียวกับการแบ่งเนื้อหาของหนังสือเล่มโตออกเป็นสองภาค แล้วนำแต่ละภาคมาจัดอบรมกันนั่นเอง ทั้งๆที่หนังสือที่หยิบมาเปิดสอนกันก็เป็นเพียงตำราเล่มหนึ่ง จากหนังสือตำราอีกนับไม่ถ้วนของ Excel ทำให้เนื้อหาที่สอนกันจึงเป็นเพียงแค่บางส่วนของวิธีใช้ Excel ทั้งหมด ซึ่งกว่า 10 ปีที่วิทยากรรู้จัก Excel มานั้น ยังเรียนรู้ไม่ครบทั้งหมดเลยด้วยซ้ำ หากจะเรียกชื่อหลักสูตรให้ถูกต้อง ควรเรียกว่า Excel 101, Excel 102, และ Excel 103 เรื่อยไป แต่ถ้าตั้งชื่อกันเช่นนี้ก็จะไม่สะดุดตาเป็นที่สนใจของคนเรียน ดังนั้นก่อนที่คุณจะเข้าอบรมหลักสูตรใด โปรดพิจารณาเนื้อหาที่สอนเป็นหลักสำคัญยิ่งกว่าชื่อของหลักสูตรนั้นๆ (ยกเว้นหลักสูตรของวิทยากร ซึ่งใช้ชื่อตรงกับเนื้อหาที่คุณจะได้รับ)
จากประสบการณ์การสอนและชีวิตการทำงานของวิทยากร พบว่า ผู้ใช้ Excel ส่วนมากทั้งที่เคยใช้ Excel มานานนับสิบปีหรือแม้แต่เคยผ่านการอบรมหลักสูตรขั้นสูงมาแล้ว เป็นผู้ที่มีพื้นฐานที่ช่วยทำให้พอจะขึ้นชื่อว่า ตนสามารถใช้ Excel ได้แค่นั้น แต่ยังไม่อาจเรียกได้ว่าใช้ Excel เป็นจริงๆ ประโยชน์จาก การใช้ได้ นั้นยังน้อยกว่าประโยชน์จาก การใช้เป็น อย่างเทียบกันไม่ติด เพราะการใช้เมนูคำสั่งของ Excel ได้ทั้งหมด ยังไม่ได้หมายถึงว่า คุณสามารถใช้ Excel ได้เต็มกำลัง เนื่องจากคุณยังไม่สามารถใช้ Excel เป็นอย่างเครื่องคอมพิวเตอร์ ได้เต็ม 100% เลยด้วยซ้ำ และการใช้คำสั่งผ่านเมนูนั้น ยังคงอาศัยมนุษย์เป็นผู้สั่งให้โปรแกรมทำงานตามที่ต้องการ ยกตัวอย่างเช่น หากคุณต้องการค้นหาข้อมูลของลูกค้า คุณสามารถใช้เมนูคำสั่ง Data > Sort หรือ Data > Filter เพื่อดึงข้อมูลที่ต้องการขึ้นมา ซึ่งลักษณะการที่คุณยังต้องใช้มือเลื่อน mouse ไปกดเมนูเหล่านี้นี่แหละ เป็นสิ่งที่ทำให้ยังไม่ได้ใช้ Excel ให้เต็มกำลัง เรามักยึดติดกับการใช้คำสั่งบนเมนู จนไม่รู้รอบว่ายังมีวิธีอื่นอยู่อีก
วิธีใช้ Excel ให้เต็มกำลัง หมายถึงคุณต้องใช้ Excel ให้ได้และให้เป็นแบบเครื่องคอมพิวเตอร์จริงๆ พอคุณใส่ข้อมูลลูกค้าเสร็จ คุณจะได้รับคำตอบที่ต้องการทันทีโดยไม่ต้องใช้คำสั่งใดๆอีกเลย ซึ่งวิธีการใช้งานแบบนี้ คุณต้องรู้จักการใช้สูตรของ Excel เช่น ตามตัวอย่างข้างต้น สามารถค้นหาข้อมูลลูกค้าโดยใช้สูตร Sort แทนคำสั่ง Data > Sort แต่สูตร Sort นั้นคุณต้องสร้างขึ้นมาเองจากสูตรอื่นๆ และต้องสร้างแบบ Arrays Formula โดยใช้สูตร Small สูตร Row และสูตร Index ประกอบกันขึ้นเป็นสูตรเดียว ดังนี้

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

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

รอบรู้คำสั่งทั้งหมดบนเมนูที่ Excel เตรียมไว้
รอบรู้สูตรสำเร็จรูปที่ Excel เตรียมไว้ เฉพาะสูตรสำคัญและจำเป็นต้องใช้ในงาน (ตามปกติ หลักสูตรขั้นสูงทั่วไปจะมีเนื้อหาครอบคลุมถึงแค่การรอบรู้ข้อนี้เท่านั้น)
รอบรู้วิธีสร้างคำสั่ง วิธีสร้าง VBA และวิธีสร้างสูตรใหม่ขึ้นมาใช้เอง
รอบรู้และรู้ลึกการเลือกใช้เครื่องมือต่างๆข้างต้นแล้วนำมาใช้งานร่วมกัน
รอบรู้ รู้ลึก และคิดค้นวิธีใช้ Excel นอกเหนือจากวิธีที่ Excel เปิดเผย

หลักสูตรสุดยอดเคล็ดลับและลัดของ Excel มีเนื้อหาครอบคลุมการรอบรู้ทั้ง 5 ระดับดังกล่าว โดยเลือกนำเฉพาะประเด็นสำคัญมาเสนอภายในระยะเวลาอบรมที่มีอยู่อย่างจำกัด เพื่อพอเป็นแนวทางจุดประกายความคิดให้คุณนำไปใช้ในงานที่ทำอยู่ได้ และขอแนะนำให้ผู้ที่ต้องการศึกษางานขั้นสูง ตลอดจนเรียนรู้เคล็ดลับต่างๆเพิ่มเติม ศึกษาได้จากหลักสูตรฉลาดใช้ Excel ในงานธุรกิจอย่างมืออาชีพ (แต่ถ้าคุณมีพื้นฐานที่ดีมาก่อน ก็อาจจะสร้างสรรค์งานขั้นสูงได้เอง โดยไม่จำเป็นต้องศึกษาหลักสูตรดังกล่าวเพิ่มเติม)
ในหลักสูตรฉลาดใช้ Excel ในงานธุรกิจอย่างมืออาชีพ เต็มไปด้วยทั้งศาสตร์และศิลป์ของ Excel ซึ่งเป็นอีกมิติหนึ่งเหนือกว่าการรอบรู้ 5 ระดับข้างต้น โดยจะนำเสนอตัวอย่างงานจริงมากมาย พร้อมแนะนำวิธีคิด วิธีออกแบบ และวิธีสร้างงาน แล้วนำความรู้ทั้งหมดมาประยุกต์ใช้ร่วมกันเพื่อใช้ Excel ให้สุดกำลังทั้งของตัวคุณเอง และเต็มพิกัดของ Excel หรือแม้แต่ทำให้เกินกว่าขีดจำกัดที่ไม่น่าเป็นไปได้

Excel Tips Part 4

วิธี Save file แบบที่คุณมั่นใจได้ว่า save ปลอดภัยจริงๆ

ถ้าใช้ Manual Calculation ควรกำหนดให้เครื่องคำนวณก่อนเก็บข้อมูลทุกครั้ง โดยใช้เมนูคำสั่ง Tools > Options > Calculation > Recalculate before save เพื่อให้ผู้เปิดใช้ข้อมูลครั้งต่อไป ได้รับข้อมูลที่คำนวณครบถ้วนแล้วโดยไม่ต้องสั่งคำนวณอีก
Save หลายๆครั้งและบ่อยๆ เพื่อป้องกันอุบัติเหตุไฟฟ้าดับก่อนทำงานเสร็จ และยังเป็นการบังคับให้ Windows save file ครั้งแรกให้เสร็จก่อนด้วย
Save ลงใน Hard Disk แล้วใช้โปรแกรม File Manager จาก My Computer ทำสำเนา Copy Hard Disk ไปยัง Floppy Disk
ไม่ควรใช้การ Save จาก Excel เพื่อ Save ลง Floppy Disk โดยเฉพาะเมื่อสร้าง Link File ซึ่งอยู่ต่าง Directory/Folder กัน
Save แล้วรอไฟสัญญาณดับก่อนเลิกงาน บางครั้งไฟสัญญาณแสดงการทำงานของ Disk Drive จะติดๆดับๆหลายครั้งจึงจะแสดงว่าทำงานเสร็จแล้วจริง
Save หลายๆชุด มีชุดสำรองเสมอ แล้วแยกเก็บคนละสถานที่

Work Hard ต่างจาก Work Smart ด้วย Excel อย่างไร และคนที่เรียกว่าเก่ง Excel จริงนั้นเป็นอย่างไร

Work Hard จะใช้คอมพิวเตอร์ได้แค่ แบบเครื่องคิดเลขหรือเครื่องพิมพ์ดีด ส่วน Work Smart จะมีลักษณะเด่น ดังนี้
คิดวางแผนการสร้างการใช้งาน บนกระดาษก่อน แล้วจึงค่อยเริ่มใช้ Excel
แยกส่วน Input-Calculate-Output และเริ่มต้นออกแบบงานจากส่วนที่เป็น Output ก่อนตามด้วย Input สำหรับส่วน Calculate นั้นให้สร้างหลังสุด
ใช้ Format เท่าที่จำเป็นเฉพาะเพื่อให้ดูดีแบบธุรกิจ ไม่ใช่ใช้แบบศิลปะ
สร้างงานที่ง่ายต่อผู้ใช้งาน สามารถนำโปรแกรมที่สร้างไปใช้ได้กับทุกเครื่อง
นำงานเก่าที่สร้างไว้มาดัดแปลงแก้ไขได้ง่าย ขอให้ใช้หลักการ ถอดหัวใจออก เช่น แยกส่วนของแบบรายงาน ส่วนของสูตรสำคัญ ส่วนของตัวแปร ออกเป็น File ย่อยแต่ละเรื่อง ต่อเมื่อต้องการสร้างงานใหม่จึงค่อยนำ File เหล่านี้มารวมตัวกันใหม่
ใช้เวลาน้อยลงเมื่อทำงานซ้ำๆ
คุ้มค่ากับเวลาที่ใช้สร้าง
คนเก่ง Excel ต้องสามารถใช้ Excel กับงานได้ทุกชนิด และไม่หยุดการเรียนรู้
ผู้บริหารต้องสร้างสภาพแวดล้อมการทำงาน และพิจารณาผลงานให้เหมาะสมด้วย มิฉะนั้นพนักงานอาจเลือกทำงานแบบ Work Hard ทั้งๆที่รู้ว่าจะ Work Smart ได้อย่างไรก็ได้ ผู้บริหารควรใส่ใจกับวิธีใช้ Excel สร้างงานและระยะเวลาที่ใช้ทำงานแต่ละครั้ง เป็นเกณฑ์พิจารณาความดีความชอบ มากกว่าดูจากผลงานที่พิมพ์เสร็จแล้ว

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

Data Table ใช้ยืมสูตรที่สร้างไว้แล้วเพื่อหา sensitivity ของผลลัพธ์เมื่อค่าของตัวแปรเปลี่ยนไป
Data Table ใช้ตัวแปรได้จำกัดแค่ 2 ตัวเท่านั้น
ใช้ Index ร่วมกับ Data Table ทำให้ใช้ตัวแปรได้ไม่จำกัดจำนวน โดยใช้เลขดัชนีของ Row/Column ของชุดตัวแปรที่ต้องการ ยิงเข้าในเมนูคำสั่ง Data > Table แทนการยิงค่าของตัวแปร
Data Table ทำให้คำนวณช้าลง อาจเลือกคำนวณแบบ Automatic Except Table ก็ได้โดยใช้เมนูคำสั่ง Tools > Options > Calculation > Automatic Except Tables

วิธีสร้างกราฟและนำตารางข้อมูลไปแสดงในกราฟ ซึ่งตัวเลขในตารางนี้จะเปลี่ยนแปลงไป เมื่อผลคำนวณเปลี่ยนไป

ใช้ Text formula = fml & "Text" & fml ผสมคำที่ต้องการขึ้นใน Worksheet ก่อนนำไปแสดงต่อบนกราฟ
การนำข้อมูลไปแสดงบนกราฟเพิ่มเติม ให้ key เครื่องหมาย = บนกราฟ แล้วสร้างสูตรอ้างอิงจากกราฟกลับมาที่ตัวข้อมูลใน sheet จะทำให้ค่าที่แสดงบนกราฟปรับเปลี่ยนไปตามผลการคำนวณใหม่เสมอ
การสร้างตารางบนกราฟ ใช้ Shift+Edit > Copy Picture เพื่อนำภาพตารางไปแปะ Paste บนกราฟ แล้วสร้างสูตรอ้างอิงจาก Picture กลับมาที่ตัวตารางข้อมูลใน Worksheet

วิธีใช้ VBA ที่ถูกต้อง เมื่อใดไม่ควรใช้ VBA

คุณควรใช้ VBA ในกรณีต่อไปนี้

ใช้กับงานที่ทำซ้ำ เช่น การเปิดปิด File จัดรูปแบบ สั่งคำนวณ และสั่งพิมพ์ โดยใช้ Macro Recorder จากเมนู Tools > Macro > Record New Macro
ใช้กับงานซับซ้อนที่ง่ายต่อการทำผิดขั้นตอน โดยเฉพาะงานที่เกี่ยวข้องกับ Fileที่ link กันหลายๆชุด และงานที่มีลำดับการทำงานที่ผิดพลาดได้ง่าย
ใช้กับงานที่ไม่มีสูตรสำเร็จรูปของ Excel โดยใช้ Function VBA สร้างสูตรขึ้นมาใหม่แล้วเรียกใช้ได้เช่นเดียวกับสูตรสำเร็จรูป แต่ Function VBA ทำงานช้ากว่าสูตรสำเร็จรูปมาก และแทบไม่จำเป็นต้องสร้างสูตรใหม่ขึ้นมาเองเลยเพราะ Excel จัดเตรียมสูตรสำเร็จรูปไว้มากมายอยู่แล้ว
ใช้กับงานที่ผู้ใช้โปรแกรมไม่มีความรู้ลึกในการใช้งาน ช่วยให้ทำงานได้ทันทีโดยไม่ต้องมีความรู้ Excel มาก่อน

ไม่ควรใช้ VBA ในกรณีต่อไปนี้

ไม่ควรใช้ VBA กับงานที่ใช้ครั้งเดียวแล้วทิ้ง หรือใช้เวลาสร้างนานเกินไป โดยเฉพาะในงานที่ผู้สร้างต้องลงทุนลงแรงของตนเองไปมาก แต่ได้ประโยชน์กลับมาไม่คุ้มกัน
ไม่ควรใช้ VBA กับงานที่สามารถใช้สูตรสำเร็จรูปได้อยู่แล้ว และไม่ควรพยายามสร้างลำดับการคำนวณขึ้นเองด้วย VBA (คุณควรสร้างสูตรใน Worksheet แล้วใช้ VBA ช่วยในการรับ Input สั่งคำนวณ แล้วส่งผลลัพธ์ Output ตามรูปแบบที่ใช้ VBA จัดขึ้นแล้วแต่กรณี)
ไม่ควรใช้ VBA กับงานที่ใช้โปรแกรมอื่นทำได้ดีกว่า
ไม่ควรใช้ VBA กับงานที่ยังไม่มีมาตรฐานแน่นอน ทั้งในเรื่องของลำดับการทำงาน ตัวแปรและสูตรที่ใช้ ชื่อเรียกหัวข้องาน และรูปแบบของตารางต่างๆ โดยคุณควรรอให้งานที่จะใช้ VBA มีสถานะที่คงตัว และไม่มีการเปลี่ยนแปลงสาระสำคัญใดๆอีกก่อนแล้วจึงนำ VBA มาใช้
ไม่ควรใช้ VBA กับงานที่ผู้สร้างกำลังจะลาออกหรือถูกโยกย้ายงาน แล้วผู้ที่จะมารับผิดชอบต่อไปแทนไม่มีประสบการณ์ความรู้ Excel เท่าเทียมกัน

Excel Tips Part 3

วิธีงมเข็มในมหาสมุทร หาสิ่งที่ต้องการ หาข้อผิดพลาดที่ซ่อนอยู่ในตารางข้อมูลหลายล้านเซลล์

Edit > Find

Edit > Goto > Special > จะค้นหาค่าได้ทั้งที่เป็น Precedent/Dependent ตลอดจนรูปแบบต่างๆของข้อมูลที่ต้องการอีกมากมาย เช่น Constant, Text, Object เป็นต้น

F5 > Special > (เป็นวิธีลัดขั้นตอน โดยไม่จำเป็นต้องใช้เมนู Edit)

เมื่อกด F5 จะแสดงตำแหน่งของ Cursor ล่าสุดก่อนหน้านี้เสมอ ทำให้คุณสามารถกลับไปหาช่องเดิมได้ทันทีด้วยการกด F5 แล้วกด Enter ทันที

Tools > Auditing จะค้นหาค่าได้ทั้งที่เป็น Precedent/Dependent และจะแสดงเป็นเส้นชี้ตามความสัมพันธ์ ระหว่างช่องข้อมูล หากใช้ลูกศรชี้ที่เส้นชี้นี้แล้ว Double Click จะทำให้ Cursor เลื่อนไปที่ช่องที่อ้างถึงเอง

Double Click เพื่อค้นหา Precedent และหากเป็นค่าที่อ้างอิงมาจาก File ที่ link มาจะทำให้เปิด file นั้นโดยอัตโนมัติ

Data > Filter > Auto Filter ใช้หาข้อมูลที่ต้องการจากตารางฐานข้อมูล โดยจะแสดงบันทัด Row ของข้อมูลที่ต้องการเท่านั้น

วิธีป้องกันความลับแบบสุดยอด แม้คนที่แอบได้ Password ไปก็ยังใช้งานไม่ได้

ใช้สูตร password ที่สร้างเองผสมกับสูตร IF เพื่อกันไม่ให้สูตรส่วนสำคัญทำงาน
ใช้ Format ;;; ทำให้ข้อมูลไม่แสดงออกมาเลย ทั้งที่ข้อมูลยังคงอยู่เหมือนเดิม
ใช้ สีตัวอักษรให้เป็นสีเดียวกับสีของพื้นด้านหลัง ทำให้ตัวอักษรกลืนไปกับฉากหลัง
ใช้ Text Box ทับช่องที่มีข้อมูล แล้วกำหนด Pattern ของ Text Box ให้เหมือนกับพื้นด้านหลัง
ใช้ Tools > Protection ทั้งในระดับ Worksheet และ Workbook ซึ่งควรใช้ทุกครั้งที่สร้างงานเสร็จ
ใช้ Format > Sheet > Hide ทำให้ Sheet หายไปจากจอ ทั้งที่ Sheet นั้นยังคงอยู่เพียงแต่มองไม่เห็นเท่านั้น เมื่อต้องการแสดง Sheet เหมือนเดิมให้ใช้ Format > Sheet > Unhide
Save as with Password ระวังต้องจำรหัสให้ได้ว่าใช้ตัวใหญ่ตัวเล็กส่วนใดด้วย

วิธีนำเสนอผลคำนวณตามแบบตัวอักษรของคนอ่าน แต่ยังใช้ผลลัพธ์ไปใช้ในการคำนวณต่อได้อีก

การใช้คำสั่งเมนู Format > Cells > Number > Custom เป็นการเปลี่ยนรูปแบบของการแสดงผลบนจอและการพิมพ์ โดยค่าของข้อมูลยังคงเดิม
พิมพ์รูปแบบใน Custom Type โดยแยกส่วนของค่าบวก ลบ ศูนย์ และ Text โดยใช้เครื่องหมาย Semi-colon แยกแต่ละส่วน ดังนี้ positive; negative; zero; text ทำให้ การแสดงผลเปลี่ยนแปลงไปตามค่าของตัวเลขในช่องนั้นๆ เช่น

#,###.00;(#,###.00);0000;

แต่ละส่วนของ Semi-colon ยังสามารถเพิ่มคำอธิบายให้แสดงขึ้นด้านหน้า หรือหลัง หรือแม้แต่ระหว่างตัวเลขได้ด้วย โดยเป็นส่วนที่มีผลต่อการแสดงผลบนจอ และการพิมพ์เท่านั้น ค่ายังคงเดิมต่างจากสิ่งที่แสดง เช่น

"is Positive "#,##0;"is Negative "(#,##0);"Non";"Here is Text"

หากต้องการให้แสดงเป็นประโยคร่วมกับตัวเลข ให้สร้างสูตร Text Formula

= "อัตราผลตอบแทน เท่ากับ " & B17*100 & " %" & IF ($B$17>0.1, " เป็นไปได้ในการลงทุน"," ต่ำเกินไป ไม่ควรลงทุน")

Text Formula ให้ผลเป็น Text ซึ่งไม่สามารถนำไปคำนวณต่อได้อีก ตรงกันข้ามกับการใช้ Format ซึ่งยังเป็นค่าตัวเลขที่นำไปคำนวณต่อได้อีก

วิธีใช้ Excel ร่วมกับโปรแกรม Word และ Database

Excel มีความสามารถในการ Object Linking and Embedding (OLE) ช่วยให้นำโปรแกรมอื่นมาใช้งานในพื้นที่ตารางของ Excel ได้โดยใช้เมนูคำสั่ง Insert > Object แล้วเลือกใช้โปรแกรมที่ต้องการ เช่น Microsoft Word จะทำให้เมนูของ Excel กลายเป็นเมนูของ Word และเนื้อที่ตารางกลายเป็นพื้นที่สำหรับพิมพ์ข้อความแบบ Word
สำหรับใช้ในงาน Database นั้น Excel สามารถเปิด File ข้อมูลได้โดยตรง โดยใช้ File > Open ตามปกติ และสามารถ Save กลับในรูปของ Database File ได้ด้วย
กรณีที่ต้องการนำเฉพาะบางส่วนของข้อมูลทั้งหมดใน file ประเภทฐานข้อมูลมาใช้ใน Excel สามารถใช้ MS Query Add-ins ช่วย Extract ข้อมูลมาใช้ได้โดยผ่านเมนูคำสั่ง Data > Get External Data ทั้งนี้ต้อง Install Excel แบบสมบูรณ์จึงจะมีเมนูนี้
หาก Install Access จะมีเมนูของโปรแกรม Access เพิ่มขึ้นในเมนู Data ของ Excel ทำให้สามารถเรียกใช้โปรแกรม Access Wizard ช่วยจัดการข้อมูลใน Excel

วิธีพิมพ์แบบประหยัดกระดาษกว่า 50% แถมอ่านได้ง่ายขึ้น โดยไม่ได้ลดขนาดหน้าที่จะพิมพ์

คุณนั่นแหละที่ต้อง คิด วางรูปแบบของตารางข้อมูล ตามแนวนอนหรือแนวตั้ง
การสร้างตารางที่ดูมีระเบียบสวยงาม ควรกำหนดขนาดความกว้างของ Row หรือ Column ให้เท่ากัน ซึ่งทำให้ Column ที่ไม่มีค่าหรือมีค่าน้อยมาก ต้องมีขนาดความกว้างเกินจำเป็น หากเป็นเช่นนี้ขอให้ลองปรับตารางจากแนวตั้งเป็นแนวนอน
ใช้ Format > Cells > Alignment > Wrap Text หรือใช้การกด Alt + Enter เพื่อจัดข้อความให้ขึ้นบันทัดใหม่ได้ในช่องเดียวกัน แล้วจึงทำให้ Column กว้างเท่ากัน
ในการกลับด้านของตาราง หลังจาก Copy ตารางทั้งหมดแล้ว ใช้เมนูคำสั่ง Edit > Paste Special > Transpose ต้องระวังส่วนของสูตรในตารางด้วย เพราะการกลับด้านตารางอาจทำให้สูตรเพี้ยนไปจากเดิม ขอแนะนำให้เลือก Paste แบบ Value คู่กับ Transpose เสมอ ทำให้ตารางใหม่ที่กลับด้าน ปรับส่วนที่เคยเป็นสูตรให้มีค่าเป็นตัวเลขผลลัพธ์แทน

จะได้ประโยคว่า "อัตราผลตอบแทน เท่ากับ 12 % เป็นไปได้ในการลงทุน" และการใช้สูตร IF ทำให้ได้ประโยคซึ่งขึ้นกับผลลัพธ์ที่คำนวณอีก

Excel Tips Part 2

วิธีทำให้ Excel คำนวณและทำงานเร็วขึ้น โดยไม่ต้องใช้ Pentium

เลือก Tools > Options > Calculation > Manual และ ใช้ F9 สั่งคำนวณทั้งหมด หรือใช้ Shift+F9 สั่งคำนวณเฉพาะ Sheet ที่ทำงานอยู่
ใช้ Hard disk แทน Floppy Disk ทุกครั้ง และขอให้ใช้ Floppy Disk สำหรับการ Backup ข้อมูลสำรองจาก Hard disk เท่านั้นโดยใช้การ copy จากโปรแกรม File Manager (อย่าใช้ Excel save file ขนาดใหญ่หรือมี link ลงใน Floppy Disk)
สร้าง Mega Formula เป็นสูตรรวมสูตรทุกขั้นตอนไว้ในสูตรเดียว ทำให้ขนาดของ file เล็กลง คำนวณเร็วขึ้น แต่จะทำให้สูตรดูยากขึ้นมาก
แบ่ง file เป็น file เล็กๆซึ่งอาจสร้างสูตร link กัน แล้วเปิดเฉพาะส่วนที่ใช้คำนวณ เท่าที่จำเป็นเพื่อให้ได้ผลลัพธ์ตามต้องการ
รู้รอบ ก่อน รู้ลึก ต้องหาสูตรที่ต้องการให้เจอ โดยมีสมมติฐานไว้เสมอว่า Excel มีสูตรต่างๆเตรียมไว้ให้ใช้งานครบทุกด้านแล้ว อย่าพยายามใช้ VBA สร้างสูตรใหม่ขึ้น เพราะนอกจากจะเสียเวลาเรียนรู้ VBA และเสียเวลาสร้างสูตรขึ้นเองแล้ว สูตรที่สร้างขึ้นด้วย VBA ยังคำนวณช้ากว่าสูตรสำเร็จรูปที่มีอยู่แล้วด้วย

วิธีย่อสูตรยาวๆให้เหลือสั้นนิดเดียว เวลาจะเรียกใช้สูตรก็ไม่ต้องท่องจำหรือพิมพ์เอง

Insert > Name > Define ตั้งชื่อให้กับสูตร หรือตั้งชื่อให้กับค่าคงที่ ทำให้คุณเปลี่ยนแปลงสูตรหรือค่าคงที่ใหม่ได้ง่าย โดยทุกส่วนที่อ้างถึงชื่อนี้จะปรับค่าใหม่เองทันที
อาจแบ่งสูตรยากๆยาวเป็นสูตรย่อยแล้วตั้งชื่อให้กับสูตรย่อยก่อน แล้วนำชื่อสูตรย่อยมาประกอบเป็นสูตรเดิม แล้วตั้งชื่อให้กับสูตรนี้ซ้อนอีกครั้ง
ใช้ F3 เรียกชื่อมาใช้ในสูตร เท่ากับการเรียกเมนูคำสั่ง Insert > Name > Paste

สุดยอดของสูตร คือ Arrays Formula เป็นสูตรที่มีมาตั้งนานแล้ว แต่น้อยคนนักจะใช้เป็น

Arrays Formula เป็นสูตรที่นำตำแหน่งคู่ลำดับของข้อมูลมาใช้คำนวณ โดยนำค่าที่อยู่ในตำแหน่งคู่ลำดับเดียวกันมาคำนวณตามต้องการ
สร้างสูตรตามปกติ แล้วใช้กด Ctrl + Shift + Enter แทน Enter เพื่อสร้างสูตรให้มีวงเล็บปีกกา เช่น {=SUM(Quantity x Cost)} จะนำค่าที่อยู่ใน Range ชื่อ Quantity มาคูณกับค่าที่อยู่ใน Range ชื่อ Cost ตามตำแหน่งคู่ลำดับเดียวกัน แล้ว Sum ผลคูณทั้งหมดเป็นคำตอบเดียว
ถ้าส่วนบริเวณของผลลัพธ์เป็น Range ต้องเลือกช่องพื้นที่ผลลัพธ์ทั้งหมดก่อน แล้วสร้างสูตรพร้อมกันครั้งเดียว จะได้คำตอบใส่ใน Range นั้นเรียงลำดับตามตำแหน่งคู่ลำดับของตัวแปรในสูตร
Arrays Formula ทำให้ขนาด file เล็กลง แต่คำนวณช้ากว่าเดิม ยิ่งถ้าสร้างสูตร Arrays หลายชุดหรือร่วมกับการใช้ Data Table แล้ว ยิ่งทำให้คำนวณช้าขึ้นเป็นทวีคูณ จึงอาจต้องแยกสูตร Arrays ไว้หลายๆ file แล้วเลือกเปิดใช้เฉพาะ file ตามลำดับการคำนวณ เพื่อป้องกันไม่ให้สั่งคำนวณสูตร Arrays พร้อมกัน

วิธีแก้ความกลัว เมื่อต้องสร้างสูตรยากๆยาวๆ หรือเมื่อต้องนำสูตรเก่าที่จำไม่ได้แล้วมาแก้ไข

ใช้ Pointing ในการหาตำแหน่งของช่องข้อมูลแทนการพิมพ์
ใช้ Range Name กำหนดชื่อมาตรฐานที่มีความหมายแสดงถึงจุดประสงค์ของสูตร
ใช้ Insert > Function เพื่อลดปัญหาการจดจำรูปแบบของสูตร
ใช้ตัวเล็กทั้งหมดในการเขียนสูตร เมื่อกด Enter แล้วสูตรจะเปลี่ยนเป็นตัวอักษรขนาดใหญ่ให้เองในส่วนที่ถูกต้อง
ใช้เครื่องหมายวงเล็บ เพื่อแยกลำดับของการคำนวณก่อนหลัง และจะมีสีดำทึบขึ้นจับคู่วงเล็บให้ด้วยเมื่อพิมพ์หรือตรวจสอบคู่วงเล็บ
ใช้กด Alt + Enter เพื่อแยกส่วนของสูตรหรือข้อความขึ้นบันทัดใหม่ตรงตำแหน่งที่ต้องการ ทำให้สูตรยาวๆแถวเดียวแยกออกเป็นโครงสร้างหลายขั้นตอน แล้วอ่านเข้าใจง่ายขึ้นมาก
ใช้กด F9 หาผลลัพธ์เฉพาะส่วนของสูตร โดยใช้ mouse ลากเลือกส่วนของสูตรที่ต้องการคำนวณแสดงค่าที่แสดงบน Formula Bar เมื่อดูผลลัพธ์เสร็จแล้วให้กดปุ่ม Esc เพื่อย้อนกลับไปเป็นสูตรตามเดิม อย่าเผลอกด Enter ก่อนกด Esc เพราะจะทำให้สูตรเปลี่ยนเป็นค่าผลลัพธ์เลย
แยกตัวแปรออกจากสูตร เพื่อไม่ต้องแก้ไขสูตรในภายหลังเมื่อค่าที่อ้างในสูตรเปลี่ยนไปอีก
แยกสูตรยาวออกเป็นสูตรย่อยๆ เพื่อหาผลคำนวณเป็นขั้นๆก่อน แล้วจึงนำผลลัพธ์มาคำนวณขั้นต่อไป ทำให้เข้าใจสูตรที่ยากๆยาวๆได้ง่ายขึ้น โดยเฉพาะเมื่อนำสูตรเก่าที่สร้างไว้นานแล้วมาแก้ไขใหม่ (แต่ทำให้ file มีขนาดใหญ่ขึ้นและทำงานช้าลง)
แยกพื้นที่ในส่วน Input - Calculate - Output ออกจากกัน เพื่อป้องกันไม่ให้ผู้ใช้เผลอไปแก้ไขสูตรโดยไม่เจตนา และยังทำให้ตรวจสอบการ link file ได้ง่ายขึ้น เช่น ใช้ผล Output จาก file หนึ่งนำไปใช้ในส่วน Input ของอีก file หนึ่ง เมื่อจะหาส่วนที่ link เพื่อนำมาแก้ไข ก็เพียงแต่แก้สูตรที่อยู่ในบริเวณที่กำหนดให้เป็น Input
ใส่สีต่างกัน โดยเฉพาะเพื่อแยกส่วน Input - Calculate - Output
Format > Cells > Protection > Lock แล้วใช้ Tools > Protection > Protect Sheet แล้วกำหนด Password เพื่อกันไม่ให้เปลี่ยนแปลงสูตรโดยไม่จำเป็น
ทำคู่มือหรือใช้ Insert > Comment เพื่อบันทึกรายละเอียดของสูตรต่างๆที่สร้างขึ้น โดยเฉพาะในบางครั้งที่มีเงื่อนไขให้เลือกใช้สูตรแล้วแต่กรณี ทำให้ผู้สร้างงานสามารถรื้อฟื้นความจำได้ง่ายขึ้น และใช้เป็นคู่มือสำหรับช่วยการใช้งานของผู้อื่น

วิธีใช้ Excel เอาใจผู้บริหาร แบบสั้น ง่าย และรวดเร็ว

งานผู้บริหารส่วนใหญ่เกี่ยวข้องกับการวางแผนและตัดสินใจ ซึ่งอาจเปลี่ยนแปลงได้ในอนาคตตามเงื่อนไขและผลลัพธ์ของการคำนวณ โดยถือความละเอียดสวยงาม มีความสำคัญเป็นลำดับรองลงไปจากความรวดเร็ว และความยืดหยุ่นในการใช้งาน ยิ่งกว่านั้นบางครั้งความถูกต้องยังสำคัญน้อยกว่า การให้คำตอบได้ตรงเวลาที่ต้องการ
รายงานต้องสั้น สรุปและให้รายละเอียดที่สำคัญในหน้าเดียว ใช้ File > Page Setup > Page > Fit to 1 Page เพื่อสั่งพิมพ์ให้ย่อลงมาในหน้าหนึ่งได้พอดี
แยกตัวแปรออกจากสูตร ในตัวอย่างใช้สีชมพู (ตัวแปร หมายถึง ค่าผันแปร ค่าคงที่ซึ่งอาจเปลี่ยนแปลงได้ ชื่อคำอธิบาย และสูตรต่างๆที่อาจเลือกใช้ตามเงื่อนไข)
เพียงเปลี่ยนค่าของตัวแปรใหม่ จะได้คำตอบทันทีโดยไม่ต้องปรับเปลี่ยนหรือแก้ไขสูตรอีกเลย ควรสร้างงานที่สามารถใช้กับเงื่อนไขต่างๆได้ทั้งหมด ยึดหลักสร้างขึ้นเพียงครั้งเดียวแต่สามารถใช้งานได้ทุกสถานการณ์
ใช้ Text formula = fml & "Text" & fml เพื่อนำผลลัพธ์หรือค่าตัวเลข ไปผสมกับตัวอักษร ให้เกิดเป็นคำอธิบายใหม่
ใช้สูตร Choose, If, Index เพื่อเปลี่ยนการคำนวณตามเงื่อนไขของการตัดสินใจ
สูตร Choose (ลำดับที่, ค่าเรียงลำดับ) เช่น Choose (2, 123,345,567,324) = 345

ใช้กราฟแสดงประกอบ และใช้ Text Formula นำคำอธิบายพร้อมผลลัพธ์ไปแสดงบนกราฟ ทำให้กราฟแสดงภาพตามผลลัพธ์ใหม่เสมอทุกครั้ง

Excel Tips Part 1

Memory Full คืออะไร ทำไมจึงเกิดขึ้น จะป้องกันแก้ไขอย่างไร โดยไม่ต้องลงทุนเพิ่ม
คือ คำเตือนที่จะแสดงบนหน้าจอ แสดงว่าขณะนั้นเครื่องไม่มีเนื้อที่ความทรงจำ (RAM) เหลือเพียงพอสำหรับเก็บข้อมูล หรือไม่สามารถแสดงผลบนจอได้ครบถ้วน (โดยไม่เกี่ยวข้องกับเนื้อที่ใน disk ว่ามีเพียงพอที่จะเก็บ file หรือไม่) สาเหตุสำคัญมีดังนี้

เปิดหลายโปรแกรมพร้อมกัน ทั้งที่ใช้อยู่ก่อนเปิดใช้ Excel เพราะบางครั้งอาจหลงลืมไม่ได้สั่ง File > Close แต่กลับใช้วิธี Minimize Window แทน และบางครั้งสั่งพิมพ์ค้างไว้หลายงานแต่พิมพ์ไม่สำเร็จ
ใช้ format มากไปกับความสวยงามของตัวอักษร, Border, Pattern
ภาพกราฟซับซ้อนมากไป มีเส้นจำนวนมาก หรือนำภาพมาปะซ้อนบนกราฟ
File มีขนาดใหญ่เกินไป ซึ่งคุณควรแบ่งข้อมูลออกเป็นหลายๆเรื่อง อาจแบ่งตามประเภทแหล่งที่มาหรือตามระยะเวลา เพื่อลดขนาด file แล้วใช้การ link แทน
บางครั้งมีสาเหตุทางอ้อมมาจาก การไม่จัดเรียงตัวของข้อมูลใน Hard Disk ทำให้ส่วนของ Swap File ขาดการต่อเนื่องกันเกินไป และเมื่อข้อมูลมีขนาดใหญ่ขึ้น หรืองานมีความซับซ้อนมากขึ้น จะทำให้เกิดปัญหานี้ได้

วิธีตรวจสอบ ป้องกัน และแก้ไข

เลือกคำสั่งบนเมนู Help > About MS Excel > System Info เพื่อตรวจสอบว่ามี Memory Available เหลืออยู่เท่าใด หากเหลือน้อยมากทั้งๆที่คุณยังไม่ได้เปิด file ใดเลยแสดงว่า ต้องมีสิ่งผิดปกติเกิดขึ้นอยู่ก่อนแล้ว
ปิดโปรแกรมที่ไม่ได้ใช้งาน เช่น โปรแกรมตรวจสอบไวรัส โปรแกรม Screen Saver และโปรแกรม Utility ให้เหลือเฉพาะส่วนที่จำเป็นต้องใช้งานจริงๆ เปิดขึ้นเท่านั้น
แบ่ง file ขนาดใหญ่เป็นขนาดเล็กหลาย file
เลือกเปิด file ที่ต้องการคำนวณเท่านั้น โดยเฉพาะเมื่อ link file ให้เลือกเปิดเฉพาะส่วนที่เกี่ยวข้องกับการคำนวณตามตัวแปรที่เปลี่ยนแปลง
ใช้ Format เท่าที่จำเป็นตามแบบแผนของธุรกิจ อย่าใช้แบบศิลปะ
ใช้ Range Name เท่าที่จำเป็น เฉพาะการอ้างถึงสูตรหรือช่องข้อมูลที่สำคัญ
สร้าง Mega formula และ Arrays formula เพื่อลดขนาดข้อมูล
ปรับ Calculation เป็น Manual โดยเลือกคำสั่งเมนู Tools > Options > Calculation > Manual แล้วใช้วิธี กด F9 เพื่อสั่งคำนวณทีเดียว
กด F9 ใน Worksheet ไม่กดใน Chartsheet เพราะบางครั้งเมื่อกราฟมีความซับซ้อนมากขึ้น จะทำให้การคำนวณไม่สมบูรณ์ขึ้นได้
กด Shift+F9 เพื่อคำนวณเฉพาะ sheet ที่กำลังใช้ทำงานอยู่เท่านั้น
จัด Fragment ใน disk ให้ file เรียงตัวกัน เพื่อให้เรียกใช้ข้อมูลได้ทันที
ใช้ Hard disk แทนแผ่น Diskette
สั่งพิมพ์ทีละชุดให้เสร็จก่อนพิมพ์ชุดต่อไป
คำเตือน อย่า save file ที่คำนวณไม่ครบ 100% แม้จะสั่งคำนวณด้วย F9 แล้วก็ตาม ซึ่งจะมีคำว่า Calculate ค้างในส่วนล่างของจอ แต่ให้ทิ้ง file นั้นแล้วนำ file ที่สำรองไว้มาใช้งานแทน

วิธีลดเวลาที่คุณใช้ Excel สร้างสูตร หรือบันทึกข้อมูลลงอย่างน้อยครึ่งหนึ่ง โดยไม่ต้องใช้ VBA

ใช้การ Pointing แทนพิมพ์เองโดยเริ่มจาก ใส่เครื่องหมายเท่ากับ แล้วใช้ mouse เลือกช่องที่ต้องการ แล้วใช้แป้นพิมพ์ใส่เครื่องหมายคำนวณ ()+ - * / ^ แล้วใช้ mouse เลือกช่องต่อๆไป
ใช้ Insert > Function Wizard เพื่อลดความผิดพลาดในการสร้างสูตร
ใช้ Insert > Name > Define เพื่อตั้งชื่อให้กับช่องข้อมูล ทำให้มีชื่อเรียกติดไปกับช่องนั้นแม้จะถูก move ไปที่อื่นก็ตาม และเหมาะสำหรับการใช้อ้างถึงใน VBA ด้วย
ใช้ F3 เพื่อเรียกชื่อที่ตั้งไว้มาใช้งาน
เลือกเมนูคำสั่ง Tools > Options > Calculation > Manual แล้วใช้การกด F9 เพื่อสั่งคำนวณทั้งหมดครั้งเดียวเมื่อต้องการ
เลือกพื้นที่แล้ว input แล้วกด Ctrl+Enter กรณีที่คุณต้องการเลือกพื้นที่ซึ่งไม่ติดต่อกัน ให้กด Ctrl แช่ไว้ในการกำหนดพื้นที่
เตรียมพื้นที่แล้วใช้ Tab เลื่อนไป วิธีเตรียมมี 2 ขั้นตอน

1. Format > Cells > Protection > Unlocked เฉพาะบริเวณช่องที่เป็น input
2. Tools > Protection > Protect Sheet
คุณต้องใช้ทั้งสองขั้นตอน จึงจะทำให้กด Tab เลื่อนไปเฉพาะช่องที่ Unlocked ไว้แล้วเท่านั้น

Format สีแต่ละพื้นที่ให้แตกต่างกัน เช่น ใช้ตัวอักษรสีชมพูแทนส่วนที่เป็น Input ใช้สีเขียวแทนส่วนที่เป็นสูตร link มาจาก file อื่น หรือใช้สีฟ้าเข้มแทนส่วนที่เป็นสูตร link มาจาก sheet อื่นใน file เดียวกัน
ใช้ Edit > Replace เพื่อแก้ไขตัวอักษรหรือตัวเลขเก่าให้เป็นตัวเลขใหม่ตามต้องการ โดยคุณไม่ต้องค้นหาและแก้ไขเอง

วิธีสร้างสูตร Link ข้าม file หรือข้าม sheet ให้ง่ายเหมือนกับสร้างสูตรในหน้าเดียวกันแบบไม่ต้องท่องจำ

ใช้การ Pointing แทนการพิมพ์สูตรเอง
Link ข้าม sheet ให้เลือกที่ sheet tab เพื่อเข้าไปใน sheet นั้นแล้วจึงเลือกช่อง
Link ข้าม file ให้เลือกจากเมนู Windows ซึ่งจะแสดงชื่อ file ทั้งหมดที่เปิดอยู่ไว้ตอนท้ายล่างสุดของเมนู พอเข้าไปใน File นั้นแล้วให้ใช้วิธีเหมือน Link ข้าม sheet
ใช้ F3 เรียกชื่อที่ตั้งไว้แล้วมาใส่ในสูตร
ระวังการ Insert, Move, Delete, Rename ซึ่งทำให้ส่วนที่ link เปลี่ยนแปลงไปโดยไม่รู้ตัว ให้ยึดหลักเปิดทุก file ที่ link กันและต้องการ Insert, Move, Delete, Rename ขึ้นในความจำของเครื่องเสมอทุกครั้งก่อนที่จะเริ่มแก้ไขใดๆ และสั่ง Calculate ให้สมบูรณ์ก่อนแล้วจึง save file ทั้งหมด

Circular Reference คืออะไร เมื่อเกิดข้อผิดพลาดจะแก้อย่างไร

Circular Reference คือ การสร้างสูตรที่วนเป็นวงจรย้อนกลับมาที่ช่องเริ่มต้น และนำผลคำนวณที่ต้องการไปคำนวณต่ออีก เช่น สร้างสูตรในช่อง C3 = C1 + C2 +C3 ทำให้ไม่มีทางได้คำตอบที่ถูกต้อง และ Excel จะแสดงคำเตือนขึ้นบนจอว่า Microsoft Excel can not calculate a formula และส่วนล่างสุดของจอจะแสดง ตำแหน่งสูตรต้นตอที่ทำให้เกิดปัญหา Circular ซึ่งจะต้องเริ่มแก้ไขที่ช่องนั้นก่อน
ตามปกติทั่วไป Circular Reference แสดงว่ามีการสร้างสูตรที่ผิดพลาด แต่ในกรณีที่เจตนาสร้างสูตรคำนวณแบบนี้ ให้แก้โดย Tools > Options > Calculation > Iteration จะทำให้เครื่องคำนวณหลายรอบตามจำนวนรอบที่กำหนดใน Iteration จนกระทั่งได้ผลลัพธ์ที่ต้องการ บางครั้งถ้าสูตรซับซ้อนมากขึ้นอาจต้องเพิ่มจำนวนรอบให้มากขึ้นจากเดิมซึ่งกำหนดให้คำนวณ 100 รอบสูงสุดขึ้นอีก
โดยทั่วไปห้ามเลือก Iteration เด็ดขาดเพราะ Excel จะไม่เตือน Circular ถ้าสูตรผิด

วิธีสร้างสูตร Smart Formula สูตรเดียวแต่ใช้งานได้สารพัด

ต้องรู้จักใช้หลัก Relative และ Absolute Reference ในการสร้างสูตรที่ช่องหนึ่งแล้ว copy สูตรไปใช้ที่อื่น ทำให้สูตรปรับตัวเองให้สามารถใช้คำนวณในช่องอื่นได้ทันที โดยไม่ต้องแก้ไขสูตรใหม่เองอีก
ใช้ F2 Edit สูตรแล้วใช้ F4 เลือกเครื่องหมาย $ โดยทุกครั้งที่กด F4 จะปรับตำแหน่งของ $ ที่กำกับ Column/Row ที่ต้องการกำหนดให้เป็น Absolute Reference
รู้จักใช้สูตร if (Check, ifTrue, ifFalse) เพื่อปรับสูตรให้คำนวณตามเงื่อนไข โดยคุณไม่ต้องสร้างสูตรใหม่ทุกครั้งที่เงื่อนไขการคำนวณเปลี่ยนแปลงไปจากเดิมอีกเลย
ตั้งชื่อให้กับสูตรหลัก แล้วใช้การอ้างถึงชื่อนี้แทนการใช้ตำแหน่งของช่อง