การนำข้อมูลมาแสดงตามจำนวนรายการที่กำหนดโดยสามารถระบุหน้าที่ต้องการ

การนำข้อมูลมาแสดงตามจำนวนรายการที่กำหนดโดยสามารถระบุหน้าที่ต้องการ

กรณีฐานข้อมูลมีรายการจำนวนมากและเราต้องการนำมาแสดงเฉพาะจำนวนบรรทัดที่ต้องการ เฉพาะหน้าที่ต้องการเพื่อที่ว่าปริมาณข้อมูลจะได้ไม่มากเกินไป เหมาะกับการประยุกต์ใช้ในการนำข้อมูลมาลงแบบฟอร์มต่าง ๆ เหมาะแก่การดูหรือเพื่อการพิมพ์ เราสามารถประยุกต์ใช้ Scroll Bar มาช่วยในการทำงานลักษณะนี้ได้ More »

การรวมข้อมูลหลายชีตด้วย Wildcard

การรวมข้อมูลหลายชีตด้วย Wildcard

ในการรวมข้อมูลจากหลาย ๆ ชีต โดยมีชีตหลักเพื่อไว้สรุปข้อมูลจากชีตย่อย ๆ นั้น หากชีตที่ต้องการรวมข้อมูลอยู่ติดกัน สามารถทำได้โดยเลือกเซลล์ที่ต้องการใช้แสดงผลรวมในชีตหลัก คีย์สูตร =Sum( คลิกแถบชีตแรกที่ต้องการวมข้อมูล กดแป้น Shift ค้างไว้ คลิกเลือกแถบชีตสุดท้ายที่ต้องการรวมข้อมูล More »

การเลือกข้อมูลจากฐานข้อมูลมาแสดงตามเงื่อนไขที่ต้องการ

การเลือกข้อมูลจากฐานข้อมูลมาแสดงตามเงื่อนไขที่ต้องการ

นการนำข้อมูลจากแหล่งใด ๆ มาแสดงตามเงื่อนไขนั้นสามารถทำได้หลายวิธี เช่น Advanced Filter, Array Fomulas, PivotTable, Consolidation หรือ เขียนโปรแกรมด้วย VBA ซึ่งจากที่กล่าวมาทั้งหมดเป็นการกระทำโดยไม่ต้องเพิ่มคอลัมน์ช่วยที่ฐานข้อมูลแต่อย่างใด ลักษณะเช่นนี้เป็นแนวทางการทำงานกับฐานข้อมูลที่ควรจะเป็น More »

การแทรกบรรทัดว่างระหว่างข้อมูล

การแทรกบรรทัดว่างระหว่างข้อมูล

กรณีที่มีข้อมูลเรียงต่อเนื่องกันไปลงด้านล่างแล้วต้องการจะแทรกบรรทัดว่างระหว่างแต่ละบรรทัดของข้อมูล โดยเลือกที่จะกำหนดได้ว่าจะให้แทรกกี่บรรทัด นอกจากจะใช้สูตรและ VBA แล้ว เราสามารถใช้เทคนิคการจัดเรียงเข้ามาช่วยได้ครับ More »

ทิปการเพิ่มประสิทธิภาพให้กับ Microsoft Excel

ทิปการเพิ่มประสิทธิภาพให้กับ Microsoft Excel

Link ไปข้างหน้าหรือหลังดีกว่ากัน หลีกเลี่ยงการ Link ไปข้างหน้า เช่นใช้ A1 ทำการ Link ไป Z1000 อาจจะเกิดการคำนวณที่ช้าลงหากมีสูตรที่หลากหลายจำนวนมากเนื่องจาก Excel จะปรับลำดับการคำนวณ ทำให้เกิดการรอผลการคำนวณจากสูตรอื่น ๆ More »

 

เทคนิคการรวมข้อมูลจากหลายไฟล์มาไว้ในไฟล์เดียว

 เราทราบมาแล้วว่าสามารถที่จะรวมข้อมูลที่อยู่ในไฟล์เดียวกันแต่อยู่หลาย Sheet จากหัวข้อ การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียว มาแล้ว ในหัวข้อนี้จะกล่าวถึงเทคนิคและแสดงตัวอย่างการนำข้อมูลจากหลายไฟล์มาต่อกันในไฟล์เดียวบ้าง ซึ่งเป็นเทคนิคที่น่ามหัศจรรย์

การ Paste Link แบบ Transpose

การคัดลอกแล้ววางแบบสลับแกน (Transpose) สำหรับข้อมูลที่เป็นการเชื่อมโยง (Link) มาจากพื้นที่อื่น จะทำให้การเชื่อมโยงนั้นแสดงผลไม่ถูกต้องตามข้อมูลเดิม หากจำเป็นจะต้องทำการวางแบบสลับแกน สามารถใช้การประยุกต์เข้ามาช่วยได้ โดยมีขั้นตอนดังนี้ครับ ทำการเปลี่ยนเครื่องหมาย = ให้เป็นเครื่องหมายอื่นเช่น #

การหารายการเฉพาะที่ไม่ซ้ำ

สำหรับรายการที่ซ้ำ ๆ กัน เราสามารถที่หาเฉพาะที่ไม่ซ้ำได้มาใช้งานได้ครับ ซึ่งสามารถแจกแจงได้ 5 วิธีหลัก ๆ ตามด้านล่าง นั่นคือ การหารายการที่ไม่ซ้ำด้วย PivotTable การหารายการที่ไม่ซ้ำด้วย Consolidation การหารายการที่ไม่ซ้ำด้วย Advanced Filter การหารายการที่ไม่ซ้ำด้วย Function การหารายการที่ไม่ซ้ำด้วย Remove Duplicates (Excel 2007 เป็นต้นไป) โดยแต่ละวิธีที่นำเสนอนี้เป็นการประยุกต์เพื่อนำมาใช้หารายการที่ไม่ซ้ำเท่านั้น ทั้งนี้เพื่อให้เห็นว่าเราสามารถใช้วิธีการใด ๆ มาจัดการให้ได้ข้อมูลเฉพาะรายการที่ไม่ซ้ำได้โดยไม่ยึดติดกับวิธีใดวิธีหนึ่ง ซึ่งโดยหน้าที่หลักของแต่ละวิธีอาจจะแตกต่างกันออกไป ภาพตัวอย่างตามด้านล่างจะแสดงขั้นตอนของแต่ละวิธีครับ 1. การหารายการที่ไม่ซ้ำด้วยการใช้ PivotTable 2. การหารายการที่ไม่ซ้ำด้วย Consolidation 3. การหารายการที่ไม่ซ้ำด้วย Advanced

เทคนิคการปลด Hyperlink

ปกติเมื่อเราคีย์ URL เช่น http://www.snasui.com หรือคีย์อีเมลเช่น snasui@gmail.com โปรแกรมจะสร้าง Link มาให้ด้วยเสมอ ซึ่งสามารถไปกำหนดไม่ให้สร้าง Link ได้ครับ แต่ถ้าหากเดิมเราไม่ได้กำหนดไว้ก่อนและต้องการแก้ไขไม่ให้มี Link ในภายหลังก็สามารถทำได้เช่นกัน สมมุติข้อมูลที่มี Hyperlink อยู่ที่ A1:A100

การลบ Text Format

 ในการนำข้อมูลจากโปรแกรมอื่นเช่น SQL, Oracle, หรือจาก Web เข้ามายัง Excel ในบางครั้งพบว่าจะมีเครื่องหมาย ‘ (เครื่องหมายฝนทอง) ติดมาหน้าตัวอักษรและตัวเลขด้วย เครื่องหมายฝนทองที่อยู่ด้านหน้าสุดนำหน้าอักขระใด ๆ ในเซลล์เช่นนี้ใน Excel ถือว่าเป็นการจัดรูปแบบข้อมูลให้เป็น Text ครับ

การเปลี่ยนตัวเลข 1, 2, 3, …0 ให้เป็น A, B, C, …J

การเปลี่ยนข้อความลักษณะนี้สามารถใช้สูตร Substitute มาช่วยได้ครับ แต่หากตัวที่ต้องการเปลี่ยนมีจำนวนมากใน Excel 2003 จะต้องพึ่งเซลล์ช่วยหลายเซลล์เนื่องจากสามารถซ้อนสูตรได้เพียง 7 ชั้น แต่หากเป็น Excel 2007 ขึ้นไป สามารถที่จะซ้อนสูตรได้ถึง 64 ชั้น

การรวมข้อมูลโดยอ้างอิงค่าที่เว้นเป็นช่วง ๆ

ปกติรายงานที่ได้มาจากสรุปข้อมูลแล้วมีการเว้นช่วงเอาไว้เช่นรายงานที่ได้จาก PivotTable แล้วมีการ Copy ค่าตามรูปแบบเดิม ๆ มาใช้ จากภาพด้านล่างจะเห็นว่าคอลัมน์ B มีการเว้นข้อมูลไว้เป็นช่วง ๆ การจะให้ง่ายต่อการใช้งานต้องเติม Field ด้านล่างให้เหมือนด้านบน

การ Lookup ข้อมูลพร้อมทำ Hyperlink กลับไปยังต้นแหล่ง

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

การคำนวณโดยระบุกลุ่มข้อมูลจากข้อมูลทั้งหมด

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

การรวมข้อมูลหลายเงื่อนไขด้วย Sumif

เราสามารถรวมข้อมูลตามเงื่อนไขโดยมีเงื่อนไขเพียงเงื่อนไขเดียวด้วยฟังก์ชั่น Sumif ซึ่งเป็นฟังก์ชั่นที่คำนวณตามกรณีนี้ได้รวดเร็วที่สุดหรือหากเป็นการรวมหลายเงื่อนไขและแต่ละเงื่อนไขอยู่ในคอลัมน์เดียวกัน เราสามารถใช้ Sumif+Sumif+Sumif+… ไปเรื่อย ๆ

เทคนิคการ Match Data ด้วย Subtotal

ผู้ที่ทำงานเกี่ยวกับตัวเลขโดยเฉพาะนักบัญชีมักจะมีปัญหาด้านนี้กันมากครับ คือ ต้องการนำตัวเลขทางด้านบวกจับคู่กับตัวเลขทางด้านลบซึ่งมีค่าเท่ากัน เป็นการหาตัวเลขที่ไม่เข้าคู่ เพื่อจะได้ดำเนินการหาสาเหตุต่อไป VDO ทางด้านล่างนี้ สาธิตการจัดกลุ่มตัวเลข

การสรุปข้อมูลตามเงื่อนไขด้วย Sumproduct

สูตร Sumproduct นั้นใช้รวมหรือนับค่าตามเงื่อนไข คือ ถ้าระบุเฉพาะช่วงข้อมูลที่เป็นเงื่อนไข จะเป็นการนับ ถ้าระบุช่วงข้อมูลที่ต้องการผลลัพธ์ไว้ด้วยจะเป็นการรวม ยกตัวอย่างการใช้ฟังก์ชั่น Sumproduct เพื่อสรุปข้อมูลที่มีลักษณะเป็น Database ใน Sheet1