Advance Excel

วิธีทำรายงานแบบ Dynamic สามารถดูได้หลายมิติ
Pivot Table เป็น Function ตัวเก่งที่สุดของ Excel ก็ว่าได้ สามารถสร้างรายงานแบบ Dynamic ที่ทำครั้งเดียวแต่ดูได้หลากหลายมิติ ไม่ว่าจะดูยอดขายต่อเดือน ยอดซื้อต่อลูกค้า ยอดสินค้าที่ขาย ฯลฯ ตามแต่ความสนใจของเรา
แต่ที่น่าเสียดายที่คนใช้ Excel ส่วนใหญ่ โดยเฉพาะ SME ไม่ค่อยรู้จัก Function นี้กันเท่าไหร่
Excel Pivot Table Tutorial
  
เติมเต็มช่องว่างด้วยคำสั่ง Go To Blank
บ่อยครั้งที่เรามักคีย์ข้อมูลไม่ครบโดยละไว้เป็นที่เข้าใจว่าเหมือนข้างบน แต่พอจะ sort ขึ้นมาก็ทำลำบากเพราะหลัง sort เสร็จ เจ้าข้างบนนี่ มันไม่เดิมซะแล้ว
เลยบ่อยๆ ต้องมานั่ง copy เติมให้เต็มแล้วค่อย sort

Trick นี้ เรามีวิธีย่นย่อให้เติมเต็มได้อย่างรวดเร็ว โดย

1.	ป้ายเลือกข้อมูลทั้งหมดที่ต้องการเติมเต็ม
2.	ไปที่เมนู Edit > Go To > คลิ๊กปุ่ม Special > เลือก Blanks
3.	พิมพ์ = cell ที่อยู่ถัดขึ้นไปด้านบน แล้วกด Ctrl + Enter

สูตรคำนวณยอดคงเหลือ แบบพันยอด สำหรับแต่ละ Parts ใน Stock
เมื่อต้องการหายอดคงเหลือของแต่ละ Parts โดยเอายอดคงเหลือบรรทัดที่แล้ว + ยอดของเข้าใหม่ - ยอดของเบิกจ่าย เราจะได้ยอดคงเหลือมา แต่น่าเสียดายที่ไม่สามารถ Copy สูตรไปได้ตลอดลอดฝั่ง เพราะพอเปลี่ยน Parts ทีก็ต้องเปลี่ยนสูตรเป็น = ยอดเข้าใหม่แทน เพราะมันไม่มียอดคงเหลือจากบรรทัดที่แล้ว
ทำยังไง Excel ถึงจะรู้ว่าพอมี Parts ใหม่ ให้เอายอดเข้าใหม่ - ยอดเบิก(ถ้ามี) เลย
วิธีทำ : - ก่อนอื่นให้ทำการ เติมเต็มทุกช่องก่อนนะครับ หากมันโหว่ๆ อยู่ เสร็จแล้วก็ทำการ Sort ให้ Sort Parts No. ก่อน แล้วค่อย Sort วันที่ทีหลัง เพื่อให้ Parts เดียวกันมาอยู่ที่เดียวกันก่อน แล้วค่อยเรียงวันที่ตามลำดับก่อนหลัง เวลาทำยอดคงเหลือจะได้ถูกต้อง
เสร็จแล้วใส่สูตรนี้ที่ช่อง Bal เลย         =IF(A3=A2,G2+E3-F3,E3)


การแตก BOM (Bill of Materials หรือ สูตรการผลิต) เพื่อหายอดรวมวัตถุดิบที่ต้องสั่ง ด้วยคำสั่ง SUMPRODUCT
สูตรปกติสำหรับคำนวณหายอดรวมของวัตถุดิบจะเป็นดังนี้

ถ้าสั้นๆไม่กี่สูตรคงคำนวณหากันไม่ยาก นั่งพิมพ์ นั่งบวก กันไปเรื่อยๆ แต่ถ้ายาวๆ เป็นสิบๆสูตรละก็ คงพิมพ์กันเงกไปเลย
ด้วยคำสั่ง SUMPRODUCT   =SUMPRODUCT($B$2:$D$2,B3:D3) เพียงแค่นี้ ทุกอย่างก็เรียบร้อยได้ในพริบตา
ตัดคำด้วยคำสั่ง Left, Mid, Right และ Len
Excel เตรียมเครื่องมือตัดคำไว้ให้เราใช้กันอยู่ 4 แบบ คือ Left, Mid, Right และ Len
ด้วย 4 เครื่องมือนี้ เราสามารถจะเข้าไปแยกเอาข้อมูลจากเซลล์ใดๆ ก็ได้ที่เราต้องการ

  การตัดคำนำหน้า เช่น นาย, นาง, น.ส. หรือ บริษัท, หจก. ด้วยคำสั่ง If, Left และ Mid
บ่อยครั้งที่เรามักเจอกับ Excel ที่มีคำนำหน้านามในลักษณะนี้ ซึ่งทำให้ไม่สามารถทำการจัดเรียงข้อมูล หรือ สรุปข้อมูลตามต้องการได้
เราจึงต้องใช้วิธีตัดต่อเพื่อให้ได้ชื่อแท้จริงออกมา

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

 การเติมเต็ม 0 ข้างหน้าหมายเลขอ้างอิงให้เต็มจำนวน

บางครั้งเราอาจต้องการเลขอ้างอิง อย่าง เลข Invoice หรือ เลขใบสั่งซื้อ ให้มันมี 0 เติมเติมอยู่ข้างหน้า โดยเราคีย์แค่ตัวเลขต่อเนื่องอย่างเดียว
เราสามารถใช้คำสั่ง Len และ If เพื่อเติมเต็ม 0 ข้างหน้าหมายเลขอ้างอิงให้เต็มจำนวน digit ที่ต้องการได้ อย่าง 0001, 0012, 0123, 1234

 

ใส่สีให้บรรทัดแบบมีเงื่อนไข เช่น จะระบายสีส้มให้เฉพาะกับรายการเบิกจ่ายเท่านั้น

เพื่อความสวยงานของ Worksheet เราอาจอยากใส่สีสลับไปมาระหว่างรายการ เช่น รายการออก ให้ระบายสี รายการเข้าให้เป็นสีขาวปกติ

 วิธี Copy หรือเคลื่อนย้าย Sheet งานไปไฟล์อื่น เพื่อสงวนต้นฉบับ

เพื่อไม่ให้ต้นฉบับเสียหาย บางทีเราอาจต้องการ copy หรือ move sheet ที่เปิดอยู่ ไปเปิดเป็นไฟล์ใหม่ หรือ ย้ายไปต่อ sheet อื่นในอีกไฟล์งานหนึ่ง

 การทำ EUC (End User Computation) เพื่อตัดต่อข้อมูลที่ดาวน์โหลดมาจากโปรแกรมหลักในองค์กร

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