รูปแบบเวลา h:mm เริ่มจาก 0:00 - 23:59 ส่วน 24:00 ต้องใช้รูปแบบระยะเวลา [h]:mm

รูปแบบของเวลาใน Excel h:mm:ss ไม่มีเที่ยงคืน และไม่มีเลข 60 ด้วยโดยเริ่มจาก 0:00:00 - 23:59:59 ซึ่งมีค่าน้อยกว่า 1 เสมอ หากใช้รูปแบบนี้แล้วพิมพ์เลข 1 ลงไปจะกลายเป็น 1/1/1900 0:00:00 ขึ้นวันใหม่ไปแล้ว

ตัวเลข 1 นี้ ถ้าอยากแสดง 24:00:00 ให้ใช้รูปแบบของระยะเวลาแทน โดยใส่วงเล็บสี่เหลี่ยมคร่อมตัว h ให้เป็น [h] เพื่อแสดงระยะเวลาชั่วโมงสะสม

พอกรอกเลข 2 ลงไปจะแสดงเป็น 48:00:00

หากต้องการแสดงเป็นระยะเวลาของนาทีสะสมให้ใช้ [m]:ss หรือวินาทีสะสมใช้ [ss]

วันไหนที่เป็นฤกษ์งามยามดีสำหรับเริ่มต้นเรียน VBA หรือ Power Q+P+B+???

ที่แน่ๆต้องไม่ใช่วันเสาร์ วันอาทิตย์ หรือวันที่เป็นวันหยุดทำงาน

หัวหน้าควรส่งลูกน้องมาเรียน VBA, Power Query, Power Pivot, Power BI, Python, Office Script หรือแอปอื่นโดยเลือกวันทำงาน อย่าคิดแต่ว่าไม่อยากให้เสียเวลาทำงานเลยส่งไปเรียนในวันหยุดทำงาน ถ้าคิดแบบนี้แสดงว่าพลาดไปแล้วหลายเรื่อง

1. ตั้งแต่แรกก่อนจะเรียนอะไร หัวหน้าไม่ควรปล่อยให้ลูกน้องใช้บริษัทเป็นที่ประลองฝีมือโดยที่ไม่ได้รับอนุญาต เพราะการใช้เครื่องมือในแอปเหล่านี้ ไม่เหมือนการใช้ Excel ที่สามารถแกะดูว่าสร้างสูตรลงไปที่เซลล์ไหน ทำอะไรลงไปบ้างในตาราง

2. หัวหน้าควรถือว่าการเรียนเป็นการลงทุน ซึ่งจะให้ผลเป็นกำไรสร้างประโยชน์ให้กับที่ทำงานในอนาคตโดยเฉพาะจะช่วยให้ทำงานได้ง่ายขึ้น เสร็จเร็วขึ้น มีเวลาไปสร้างงานอืื่นได้มากขึ้น การยอมให้ใช้เวลาทำงานส่งไปเรียนแสดงถึงความเข้าใจเรื่องเหล่านี้

3. หัวหน้าควรมีส่วนรับผิดชอบร่วมกับลูกน้อง รับรู้ว่าจากนี้ไปต้องอาศัยฝีมือในการสร้างงานมากขึ้น การที่ลูกน้องสามารถลดเวลาในการทำงานได้นั้น ไม่ได้เกิดจากแอปที่ไปเรียนทำให้ แต่คนที่ใช้แอปต่างหากที่่ต้องใช้ความคิดสร้างสรรค์ทำขึ้นมาให้

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

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

 

ขอโทษด้วยนะครับ สาเหตุที่ผมสอนสูตรยาวๆ เพราะอยากให้เรียนหลายๆวิธี ไม่ใช่คิดไม่ออก

สูตรพวกค้นหาค่าแทบทั้งหมด ไม่ว่า XLookup, VLookup, Match+Index ตามปกติจะใช้กับการหาค่าที่จัดเก็บไวัในตารางตามแนวเดียวกันกับค่าที่ใช้ค้นหา เช่น หาจากชื่อสินค้าว่าขายออกไปเท่าไร ทั้งเซลล์ชื่อกับเซลล์ยอดขายก็ต้องเก็บไว้ในรายการเดียวกัน

แต่ถ้าในหน้ารายงานการขาย ยอดขายไม่ได้วางไว้ในแนวเดียวกันกับชื่อสินค้าล่ะ จะใช้สูตรยังไง เช่น ตามภาพนี้ตารางด้านซ้ายใน Column B ใส่ชื่อสินค้า a b c d เอาไว้ ส่วนยอดขายของสินค้าแต่ละตัวใน Column D กลับวางเยื้องกับชื่อสินค้า 

ซึ่งหน้าตาแบบนี้พบเสมอในหน้ารายงานที่ด้านล่างสุดของแต่ละตารางจะแสดงยอดรวมเอาไว้

ตามภาพนี้ในเซลล์ H2 พอใส่ชื่อสินค้า c ลงไป จะต้องหายอดขาย 333 ออกมาด้วยสูตรอะไรดีหนอ

กว่าจะหายอดขายออกมา ได้เรียนสูตรกันหนำใจไปเลย ทำได้อย่างน้อย 3 วิธี

สำหรับคนที่ใช้ 365 จะใช้สูตร XLookup ก็ยังได้ กลายเป็นวิธีที่ 4
=XLookup( H2, B2:B17, D5:D20)

ทั้ง 4 วิธีนี้สามารถหาค่าได้ทั้งตัวเลขหรือตัวอักษร แต่ถ้ากำหนดว่าให้หายอดขาย ซึ่งแน่นอนว่ายอดขายต้องเป็นตัวเลข ไม่มีทางที่จะกรอกยอดขายเป็นตัวอักษร จะมีสูตร SumIF อีกวิธี

=SumIF( B2:B17, H2, D5)

นี่เป็นนิสัยวิธีการสอนของผม ชอบไหมครับ ได้เรียนแบบนี้ ตอนจบจะได้เรียนวิธีซ้อนสูตร ทำให้เป็น Mega Formula ใช้แค่เซลล์เดียวสร้างสูตรหาคำตอบได้เลย

================================

=SumIF( B2:B17, H2, D5) ดีกว่าสูตรอื่นๆตามภาพนี้ยังไง

1. สั้นกว่า

2. ถ้าหาค่าไม่พบจะไม่ error แต่จะหาค่า 0 ออกมาให้ซึ่งนำไปคำนวณต่อได้ทันที ไม่เสียเวลามาแก้ error

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

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

================================ 

ผมอธิบายที่ไปที่มาของ 3 วิธีไว้ที่ https://www.excelexperttraining.com/book/index.php/course-manuals/excel-expert-managing-data/finding-data-from-last-line 

เคล็ดลับ อยากรู้ว่าค่าที่สูตร XLookup หาค่ามาให้นั้นมาจากเซลล์ไหน

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

 

"สูตรใดก็ตามที่ใช้หาค่าได้โดยตรง สูตรนั้นย่อมบอกตำแหน่งได้"

 

สูตรที่เข้าข่าย ได้แก่ Index, Offset, XLookup (ส่วน VLookup ใช้หลักนี้ไม่ได้เพราะไม่ได้หาค่าโดยตรง)

วิธีการที่ใช้มีหลักการง่ายๆโดยกดปุ่ม F5 หรือคำสั่ง Go to อยากไปที่ไหนให้ลอกสูตรมาใส่ลงไปในช่อง Reference > OK  

 

 

 

ให้คลิกลากทับสูตรในช่อง Formula Bar แล้วกดปุ่ม F5 > Enter สูตรจะเปลี่ยนไปแสดงตำแหน่งเซลล์ว่าเก็บค่าไว้ที่ไหน พอเจอแล้วให้กด ESC เพื่อย้อนกลับไปเป็นสูตรตามเดิม

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

1. คลิกลากทับบนสูตร
2. คลิกขวาบนสูตรแล้วสั่ง Copy แล้วกดปุ่ม ESC
3. กดปุ่ม F5
4. ให้กดปุ่ม Ctrl+v เพื่อ Paste สูตรลงไปในช่อง Reference
5. กดปุ่ม Enter จะพบว่า Excel พาไปที่เซลล์ที่เก็บค่านั้นให้

ชมคลิป 

 

Slicer ที่ใช้ Filter เป็นจุดอ่อนที่แย่ที่สุดของ Pivot Table

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

ที่มาของภาพนี้ Use slicers to filter data

https://support.microsoft.com/en-us/office/use-slicers-to-filter-data-249f966b-a9d5-4b0f-b31a-12651785d29d?wt.mc_id=M365-MVP-4000499

ปัญหาแบบนี้แหละครับที่ลูกศิษย์ชาวอินเดียที่เป็น Regional Director ตั้งใจมาเรียนกับผมเพื่อถามปัญหานี้โดยเฉพาะ เขาบอกว่า "หาค่าที่ต้องการไม่เจอ จะทำยังไงดี"

เท่าที่ค้นหาดูว่าจะทำยังไงให้เราสามารถควบคุมจำนวนตัวเลือกที่แสดงใน Slicer คำตอบที่พบก็คือ ไม่มีทางทำได้ เพราะ Slicer หรือหัวข้อที่แสดงใน Filter นั้นขึ้นกับตารางฐานข้อมูลต้นทางว่ามีอะไรมาบ้าง

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

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

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

วิธีที่ผมชอบอีกทางออกหนึ่ง แทนที่จะสร้างรายงานด้วย Pivot ให้ใช้สูตร SumIF, SumIFS, SumProduct ช่วยคำนวณหายอดเฉพาะรายการที่หัวหน้าต้องการ ให้เปลี่ยนจากการใช้ Slicer ไปใช้ Data Validation แบบ List ที่รวบรวมเฉพาะรายการเรื่องที่หัวหน้าอยากดู

 

ชมคลิปวิธีการใช้ SumProduct ร่วมกับ Data Validation ได้จากหลักสูตร Excel Dynamic Reports for Management ซึ่งเปิดให้เรียนออนไลน์ ฟรี ได้ที่เว็บ XLSiam.com

ผมเปิดเผยเคล็ดลับการใช้สูตร CountIF แบบที่นึกไม่ถึงว่ามีวิธีการใช้งานแบบนี้ด้วย ดีไม่ดีจะได้เลิกใช้ Pivot Table ไปเลย 

+++++++++++++++++

Copilot :

 

 

 

 

 

 

 

 

ยากที่สุดของ Excel อยู่ที่การวางตำแหน่งเซลล์ ต้องทำให้ใช้คำสั่งเมนูจัดการต่อได้ง่ายๆ

 

รูปแบบหนึ่งของตารางที่ใช้เมนูจัดการต่อได้

 

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

 

ตารางวางแผน MRP ชื่อรายการที่เหมือนกันแต่แยกได้ว่าเป็นรายการของสินค้าอะไร

 

 

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

 

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

 

ในการแยกชีทหรือแยกแฟ้มเพื่อทำรายงานแต่ละเดือน ถ้าวางตำแหน่งหัวตารางด้านบนกับด้านข้างไว้ดีหรือแม้ไม่มีหัวตารางแต่วางตำแหน่งเซลล์เรื่องเดียวกันในแต่ละชีทให้ตรงกัน จะสามารถใช้คำสั่งบนเมนูเพื่อหายอดรวม consolidate ได้

 

หัวตารางด้านบนและด้านข้างมีคำอธิบายว่าเป็นรายการเรื่องอะไร (Labels)

 

 

Excel มีคำสั่ง Change sources ใช้ในการเปลี่ยนการดึงข้อมูลจากแฟ้มที่ต้องการ ซึ่งมีข้อกำหนดว่าในแต่ละแฟ้มต้องตั้งชื่อชีทให้เหมือนกัน และในตัวชีทต้องวางข้อมูลเรื่องเดียวกันไว้ตำแหน่งตรงกันด้วย

 

สมมุติว่าตัวเลขรายได้ของแต่ละเดือนแยกเป็น 12 ชีท กรอกตัวเลขไว้ในตำแหน่งที่ไม่ตรงกันและอาจถูกมือดีมาแอบย้ายเซลล์ไปที่อื่น จะใช้เมนู Filter ไม่ได้แน่นอน จะทำยังไงล่ะ

 

ให้ใช้วิธีที่ผมตั้งชื่อว่า ตบเท้าเข้าแถว โดยสร้างสูตรลิงก์แต่ละเซลล์มาวางเรียงติดกันตามแนวตั้งซึ่งสามารถใช้เมนูจัดการต่อได้

 

ออกแบบหน้าตาตารางในชีทให้เหมือนกัน 

 

 

 

สูตรอะไรใช้กับจำนวนเงิน สูตรอะไรใช้กับจำนวนสินค้า

 

จับหลักให้ได้ก่อนว่า "จำนวนเงินมีเศษ จำนวนสินค้าขายเป็นชิ้นไม่มีเศษ"

+++++++++++++++++++++++++++

สูตรจำนวนเงิน ใช้สูตร Round ไว้ปัดเศษ หรือสูตร Trunc ไว้ตัดเศษ (ย่อมาจาก Truncate แปลว่าตัด)

+++++++++++++++++++++++++++

สำหรับนักบัญชีนักการเงิน โดยทั่วไปถูกกำหนดตามหลักบัญชีและภาษี ให้ใช้ทศนิยม 2 หลัก ส่วนงานฝ่ายขายฝ่ายการตลาด จะปัดหรือตัดก็ได้

=Round(1234.567, 2) ได้ค่า 1234.57 เพราะเลขหลักเศษหลักถัดไปมากกว่าหรือเท่ากับ 5 จึงปัดขึ้น

=Round(1234.564, 2) ได้ค่า 1234.56

=Trunc(1234.567, 2) ได้ค่า 1234.56 ไม่สนใจหลักเศษหลักถัดไป ตัดเศษทิ้งไปเลย

=Trunc(1234.564, 2) ได้ค่า 1234.56

+++++++++++++++++++++++++++

สูตรจำนวนสินค้า นับจำนวนเป็นชิ้น เป็นจำนวนเต็มเสมอ ไม่มีเศษ ใช้สูตร Ceiling ไว้ปัดขึ้น หรือ Floor ไว้ปัดลง

ถ้าต้องการซื้อสินค้า 5 ชิ้น แต่ผู้ขายไม่ยอมขายทีละชิ้น จะขายเป็นโหล ดังนั้นต้องซื้อ 12 ชิ้น

=Ceiling(5,12) ได้จำนวน 12

=Ceiling(15,12) ได้จำนวน 24

ถ้าปัดลง =Ceiling(15,12) ได้จำนวน 12

นอกจากนี้ ยังใช้กับกรณีที่ต้องการปัดตัวเลขขึ้นหรือลงเป็นเท่าตัวได้อีกด้วย เช่น ต้องการปัดราคาสินค้าให้เป็นจำนวนเท่าของ 5 บาท

=Ceiling(2,5) ได้ราคา 5 บาท

=Ceiling(12,5) ได้ราคา 15 บาท เพราะปัดขึ้นราคาทีละเท่าของ 5 > 10 > 15

=Floor(12,5) ได้ราคา 10 บาท ใช้ลดราคา

ปุ่มอะไรเอ่ยที่ควรใช้ให้บ่อยที่สุด แต่มักไม่เคยใช้กัน

ปุ่มอะไรเอ่ยที่สามารถใช้แทนการคลิก OK

ปุ่ม Enter บนแป้นพิมพ์ไงครับ ให้สังเกตว่า Excel จะ highlight ใส่กรอบสีรอบปุ่ม OK ไว้ตลอด นั่นแสดงว่าพร้อมจะให้คุณกดปุ่ม Enter เพื่อสั่ง OK

 

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

1.ขนาดแฟ้ม 2.ความเร็วในการคำนวณ 3.ใช้งานง่าย สำหรับคุณแล้วข้อไหนสำคัญมากที่สุด หรือ...

สำหรับผมเรียงตามความสำคัญมากไปน้อย 3 2 1

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

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

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

ถ้าอยากให้คำนวณเร็วขึ้น วิธีที่ง่ายที่สุด คือ ให้เปลียนไปใช้เครื่องใหม่ที่มี core เยอะๆ เพราะจะช่วยให้ Excel คำนวณแบบคู่ขนานกัน หรือใช้ SDD Solid State Drive ทะเปิดปิดแฟ้มได้เร็วมาก

ส่วนขนาดแฟ้ม ไม่น่าห่วงเพราะเดี๋ยวนี้ Hard disk หรือ Flash Drive มีพื้นที่ใหญ่มาก

พอเจอว่าเปิดแฟ้มช้ามากนั่นเกิดจากการกำหนดให้คำนวณแบบ Automatic Calculation ไว้ครับ Excel จะเสียเวลาคำนวณทุกเซลล์ใหม่ทุกครั้งที่เปิดแฟ้มหรือมีการกรอกค่าใหม่ลงไป แฟ้มขนาดเล็กแต่มีสูตรเยอะมากมีปัญหาแบบเดียวกัน

 

ให้แก้โดยเปลี่ยนระบบการคำนวณของแฟ้มนั้นไปเป็น Manual Calculation จะช่วยให้ Excel เปิดแฟ้มขึ้นมาหรือเมื่อกรอกค่าใหม่จะยังไม่คำนวณ จนกว่าจะกดปุ่ม F9

 

ผมเขียนแนะนำวิธีลดความอ้วนอุ้ยอ้ายของแฟ้มไว้ที่ https://www.excelexperttraining.com/book/index.php/course-manuals/excel-expert-managing-data/file-size-and-speed-management

 

จะรู้ได้ยังไงว่าแฟ้มนี้ใช้ทำอะไร โดยไม่ต้องเสียเวลามาไล่เปิดทีละแฟ้ม

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

แทนที่จะต้องไล่เปิดแฟ้มไปเรื่อยๆ จากภาพนี้ในรูปด้านซ้าย ก่อนจะ Save ใน Excel ให้ใช้เมนู File > Info แล้วคลิกที่คำว่า Properties จะพบจอเล็กๆให้กรอกข้อมูลเกี่ยวข้องกับแฟ้มลงไป โดยเฉพาะช่อง Comments กรอกคำอธิบายได้ยาวเหยียด

ตอนค้นหาแฟ้มใน Windows File Explorer ตามรูปด้านขวา ให้คลิกที่ปุ่ม Details ตรงมุมขวาบนสุด จากนั้นอยากรู้ว่าแฟ้มไหนเป็นอะไร ให้คลิกที่ชื่อแฟ้ม จะพบข้อมูลจาก Properties มาแสดงให้เห็น

วิธีกำหนด Trusted Folders

 😍 วิธีกำหนด Trusted Folders เพื่อจัดเก็บแฟ้มที่คุณมั่นใจว่าใช้งานได้อย่างปลอดภัย 100%

เมื่อเปิดแฟ้มที่เก็บไว้ใน Trusted Folders จะไม่ต้องเสียเวลาไป Update Links หรือสั่ง Enable Content อีกต่อไปครับ Excel จะจัดการเปิดให้แล้วพร้อมใช้งานต่อได้ทันที

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

ตอนที่ไปคลิก Browse หาชื่อโฟลเดอร์ได้แล้ว ถ้าต้องการให้รวมถึง Subfolders ด้วยอย่าลืมไปกาช่องนี้ด้วย

รับแฟ้มของคนอื่นมาใช้หรือริจะใช้แฟ้มที่มี VBA ต้องระมัดระวังอะไรบ้าง

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

เมื่อได้รับแฟ้มของคนอื่นมาใช้ 

1. จัดการเก็บแฟ้มต้นฉบับเอาไว้ เพื่อเป็นหลักฐานว่าที่คนเก่าเขาทำไว้นั้นเป็นอย่างไรบ้าง ถ้าเจอว่าผิดจะได้แยกความรับผิดชอบได้ชัดว่าไม่ใช่ฝีมือคุณนะ
   
   
2. พอเปิดแฟ้มขึ้นมาให้สังเกตว่ามีแถบสีเหลืองคาดด้านบนของหน้าจอแสดงคำเตือนว่า SECURITIES WARNING Macros has been Disabled มีปุ่ม Enable Content ซึ่งจะเตือนขึ้นมาเมื่อในแฟ้มนั้นมีรหัส VBA ติดมาด้วย ซึ่งต้องระมัดระวังอย่างยิ่ง ถ้าไว้ใจว่าแฟ้มนั้นสร้างขึ้นมาจากคนที่ไว้ใจได้จึงจะกดปุ่ม Enable และห้ามไปโยกย้ายเซลล์ไปที่อื่น ห้ามเปลี่ยนชื่อชีท หรือแม้แต่เปลี่ยนชื่อแฟ้มให้ต่างไปจากเดิม
   
   
3. ถ้าถูกเตือนให้ Update Links แสดงว่าแฟ้มนั้นเป็นแฟ้มปลายทางที่มีสูตรลิงก์รับข้อมูลมาจากแฟ้มอื่น ให้ไปหารายชื่อแฟ้มต้นทางได้จากเมนู Data > Workbook Links (หรือ Update Links ใน Excel รุ่นเก่าก่อน 365) แล้วไปติดตามหาแฟ้มต้นทางเหล่านั้นให้ครบ ถ้าหาตัวแฟ้มต้นทางไม่ได้เลย เวลาเปิดแฟ้มอย่าไป Update Links ถ้าหาแฟ้มเจอแต่ชื่อแฟ้มต่างไปแล้ว หรือไม่ได้อยู่ในโฟลเดอร์ที่ Excel แสดงไว้ในหน้าจอ Workbook Links ให้แก้ใขลิงก์ให้ตรงโดยสั่ง Change Sources
   
   
4. ให้แยกแยะแต่ละส่วนของตารางว่าส่วนไหนเป็นเซลล์สำหรับกรอกค่า โดยกดปุ่ม F5 > Special > Constants หรือ Formulas เพื่อหาว่าเซลล์ตรงไหนเป็นสูตรบ้าง แล้วจัดการใส่สีพื้นหรือสีฟอนต์แยกแต่ละส่วนให้ต่างกัน
   
   
   
   
   
5. ให้กดปุ่ม F3 > Paste List เพื่อให้ Excel สรุปรายชื่อ Range Name ที่ตั้งไว้ในแฟ้มนั้นว่าตั้งชื่อไว้ให้กับพื้นที่ตารางตรงไหนบ้าง จะได้ระมัดระวังการไปสั่ง Delete/Insert Row หรือ Column ซึ่งจะกระทบกับชื่อที่ตั้งไว้ แต่ถ้าไม่พบว่ามีการตั้งชื่อ Range Name ไว้เลย จะแกะสูตรยากขึ้นหลายเท่าทีเดียวว่าตำแหน่งที่อ้างอิงไว้ในสูตรอยู่ตรงไหนบ้าง
   
   
6. ถ้าแฟ้มนั้นมี VBA ใช้อยู่ ให้กดปุ่ม ALT+F11 เพื่อเปิดดูรหัส VBA ว่ามีการอ้างอิงถึงตำแหน่งเซลล์ในชีทชื่ออะไรบ้าง ถ้าพบว่ามีการอ้างอิงถึงตำแหน่ง reference แบบ A1 ไว้หรืออ้างอิงกับชื่อชีทชื่อแฟ้มไว้ แสดงว่าในแฟ้มนั้นห้ามโยกย้ายเซลล์หรือเปลี่ยนชื่อชีทชื่อแฟ้มโดยเด็ดขาด
   
   บอกได้อย่างเดียวว่า แฟ้มใดที่มี VBA ใช้งานอยู่ด้วย "ให้ใช้เพื่อกรอกค่าใหม่เพื่อดูผลลัพธ์เท่านั้น" เว้นแต่รหัส VBA ใช้วิธีอ้างอิงกับ Range Name จะสามารถใช้แฟ้มนั้นได้ยืดหยุ่นมากขึ้น สามารถโยกย้ายเซลล์หรือเปลี่ยนชื่อชีทหรือแม้แต่ชื่อแฟ้มได้ตามสบาย
   
   
7. ให้แกะสูตรโดยคลิกลงไปดูโครงสร้างสูตรในช่อง Formula Bar หรือสั่ง Formulas > Show Formulas เพื่อแกะดูส่วนของพื้นที่ตารางที่อ้างอิงไว้ในสูตร Sum, VLookup, XLookup มีการใส่เครื่องหมาย $ ไว้หรือไม่ ถ้าไม่ได้ใส่ $ ไว้ แสดงว่าสูตรนั้นสามารถใช้ได้ที่เซลล์เดิมเท่านั้น ห้าม Copy ไปวางที่อื่น หรือถ้าใส่ $ ไว้ตัวเดียว แสดงว่าเวลา Copy ไปวางที่อื่นต้องวางในแนวเดียวกันกับ $ ที่ใส่ไว้หน้า row/column แต่ถ้าใส่ $ ไว้ 2 ตัวทั้งหน้า row หน้า column จะสามารถ copy สูตรไปวางที่อื่นได้
   
   
8. ตรวจสอบพื้นที่ตารางฐานข้อมูลที่นำไปอ้างอิงในสูตรต่างๆว่า กำหนดขอบเขตไว้แค่ไหน ถ้ากำหนดพื้นที่ไว้แบบตายตัวก็แสดงว่า ห้ามกรอกค่าใหม่เป็นรายการใหม่ต่อท้ายลงไปในตารางฐานข้อมูลเพราะสูตรจะไม่รับรู้ถึงรายการใหม่นั้น เว้นแต่พื้นที่ตารางฐานข้อมูลนั้นได้ถูกเปลี่ยนให้เป็น Table ไว้แล้ว ซึ่งสังเกตได้ง่ายว่าเมื่อคลิกลงไปในตารางตรงไหนก็ได้ จะพบเมนู Table แสดงเพิ่มขึ้นมาให้เห็น หรือสังเกตุว่าพื้นตารางมีสีใส่เป็นลายสลับกันไปเรื่อยๆ (แต่วิธีนี้ไม่แน่นอนนัก)
   
   
9. ทดลองกรอกค่าลงไป หากพบว่าสูตรที่อ้างอิงไว้กับเซลล์ที่กรอกค่า ไม่ได้คำนวณหาค่าใหม่มาให้แต่ยังคงเป็นค่าเดิม แสดงว่าแฟ้มนั้นใช้ระบบการคำนวณแบบ Manual Calculation เอาไว้ หากต้องการสั่งให้คำนวณต้องกดปุ่ม F9 หรือเปลี่ยนระบบให้เป็น Automatic Calculation ได้ที่เมนู Formulas

วิธีลบล้างความจำที่ Excel จดไว้

ใครมือไวแบบนี้บ้าง

เคยไหมพอเปิดแฟ้มขึ้นมาแล้วเห็นแถบสีเหลืองด้านบน แสดงคำเตือนขึ้นมาว่า SECURITIES WARNING Macros has been Disabled มีปุ่ม Enable Content ตามภาพนี้ 

 

👆 พอเห็นแล้วทำยังไงกันครับ มือไวใจเร็วคลิกปุ่ม Enable Content กันทันทีหรือเปล่า พอคลิกไปแล้วจะพบว่าพอเปิดแฟ้มนั้นคราวต่อไปจะไม่มีแถบเหลืองๆมากวนใจอีกเลย

การกดปุ่ม Enable Content เป็นการยอมให้ VBA หรือ Macro ที่ติดมากับแฟ้มนั้นทำงานทันที ถ้าโชคดีไม่มีอะไรเสียหายก็แล้วไป แต่ถ้าในแฟ้มนั้นมีไวรัสหรือคำสั่งของผู้ร้ายสร้างไว้ติดมาด้วย เครื่องคอมพิวเตอร์ที่ใช้อยู่อาจถูกลบอะไรทิ้งไปหรือกลายเป็นแหล่งเผยแพร่ไวรัายไวรัสให้ติดแฟ้มอื่นต่อไปก็ได้

☝️ ต้องมั่นใจ 100% ว่า แฟ้มนั้นไว้ใจได้จริงๆเท่านั้น จึงจะกดปุ่ม Enable Content ครับ

ที่แย่มากๆก็คือระบบใน Excel ที่ตั้งไว้ในตอนแรกจะกำหนดให้จดจำไว้ด้วยว่าตอนแรกที่แฟ้มนั้นเปิดขึ้นมา เคย Enable Content หรือเปล่า ถ้าคุณเคย Enable คราวต่อไป Excel ก็จะเลิกถาม ไม่มีแถบสีเหลืองมากวนใจอีก เพราะ Excel รับรู้ว่าแฟ้มนั้นถือว่าเป็น Trusted Documents ไปแล้ว

ผมแนะนำอย่างยิ่งว่า ควรปล่อยให้ Excel ถามทุกครั้งที่เปิดแฟ้มดีกว่าครับ โดยทำตามภาพ

ไปที่ Excel Options > Trust Center > Trust Center Settings จะพบหน้าจอ Trust Center เปิดต่อมาให้คลิกด้านซ้ายที่คำว่า Trusted Documents แล้วไปกดปุ่ม Clear พร้อมกับไปกาช่อง Disable Trusted Documents เพื่อสั่งให้ Excel เลิกจำ 

ที่สำคัญกว่านั้น ต้องฝึกมือตัวเองให้ "คิดก่อนคลิก" 

 

++++++++

บทเรียนหนึ่งนานมาแล้ว

ลูกศิษย์ส่งแฟ้มมาให้ ผมไว้ใจว่าเป็นลูกศิษย์เลยคลิก Enable

เมนู Excel บนหน้าจอของผมถูกจัดใหม่ทันที ย้ายเมนูจากตรงนี้ไปไว้ตรงนั้น เปลี่ยนที่ไปหมด ผมต้องเสียเวลามาจัดเมนูกลับมาเอง

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

ถ้าเปิดแฟ้มอะไรขึ้นมาบนคอมหรือมือถือ ไม่ว่าจะเป็นแฟ้มนามสกุลใดก็ตาม เปิดแล้วพบว่า บนหน้าจอมีปุ่ม OK แสดงขึ้นมาปุ่มเดียว ไม่มีปุ่ม Cancel ... ให้ปิดเครื่องเลยครับ

โดยเฉพาะจอที่ถามว่าอยากจะติดตั้ง update ระบบใหม่ให้ไหม ผมจะไม่คลิก OK แต่จะปิดหน้าจอนั้นแล้วจะไปสั่งติดตั้ง update ระบบใหม่ด้วยตัวเองดีกว่า

++++++++

ฝ่ายบุคคลรับสมัครพนักงาน ผู้ร้ายส่ง resume มาทางอีเมล พอจนทบุคคลมือไวไปคลิก Enable ตอนเปิดแฟ้ม โปรแกรมที่ทำติดมาด้วยจะสั่งให้แอบติดตามการรับส่งอีเมล แล้วส่งไปให้ผู้ร้ายทราบ

 

ผู้ร้ายอีเมลมาหาทำตัวว่าเป็นลูกค้าแจ้งอีเมลใหม่ บอกว่าให้บริษัทใช้อีเมลนี้แทน ผู้ร้ายอีเมลแจ้งลูกค้าแอบอ้างว่าเป็นบริษัท แจ้งหมายเลขบัญชีของผู้ร้ายให้ลูกค้าโอนเงินมาให้

 

กว่าจะรู้ตัวเห็นว่าเสียเงินกันหลายล้านครับ เรื่องจริง