Baby Steps สำหรับเตรียมพร้อมเริ่มทำงาน

พอเปิดแฟ้มใหม่ขึ้นมาจะเริ่มสร้างงานให้หัวหน้ายังไงดี สำหรับมือใหม่ที่ไม่คุ้นเคยกับการใช้ Excel มาก่อนมักคิดไม่ออกว่าจะต้องทำอะไรก่อนหลัง ขอให้คิดถึง I-C-O

I-C-O ย่อมาจากคำว่า Input-Calculate-Output เป็นหลักการสำหรับแบ่งพื้นที่ตารางในแฟ้มออกเป็น 3 ส่วน

1. Input เป็นส่วนของตารางฐานข้อมูล หรือข้อมูลที่ต้องกรอกค่าใหม่อยู่เสมอ

2. Calculate เป็นส่วนของตารางคำนวณ ใช้สำหรับสร้างสูตรเพื่อหาคำตอบที่ต้องการ

3. Output เป็นส่วนของตารางรายงาน

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

O-I นี่เป็นขั้นตอนสำหรับให้คิดถึง แต่เมื่อจะสร้างงานจริง ขั้นตอนต้องเริ่มจาก I > C > O

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

2. สร้างตารางฐานข้อมูลบันทึกการขายเรียงตามระยะเวลา ซึ่งมักเรียงลำดับตามวันและเวลาที่เกิดรายการนั้นขึ้น เป็นรายการที่แสดงว่าวันนั้นเวลานั้นมีการขายสินค้าอะไรออกไปบ้าง หรือจะเรียงตามรหัส Invoice ก็ได้ โดยตารางนี้จะมีจำนวนรายการเพิ่มขึ้นเรื่อยๆตามระยะเวลาที่ผ่านไป

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

4. จัดการตรวจสอบข้อมูลในตารางที่จะนำมาใช้จริงว่ามีอะไรบ้างที่ต้องปรับแก้ไข cleaning บ้างไหม จัดการตรวจสอบลบรายการซ้ำโดยใช้คำสั่ง Remove Duplicates

5. ตั้งชื่อ Range Name ให้กับพื้นที่ตารางส่วนที่จะนำมาใช้ค้นหาหรือนำมาคำนวณ

6. ใช้คำสั่ง Data Validation แบบ List เพื่อใช้ในการเลือกรหัสหรือชื่อมาใช้แทนจะได้ไม่ต้องพิมพ์เอง โดยแนะนำให้ใช้ Range Name ที่ตั้งชื่อไว้ลิงก์มาใช้กับ List จะได้สามารถใช้งานข้ามชีทหรือข้ามแฟ้มได้ด้วย

7. ใช้สูตร CountIF เพื่อตรวจสอบซ้ำอีกครั้งว่ารหัสหรือชื่อที่ใช้หาจาก List นั้นมีเพียงค่าเดียวหรือไม่

8. หากพบว่ามีเพียงค่าเดียวก็จะสามารถใช้สูตร VLookup หรือ XLookup หาค่าในรายการนั้น

9. ลิงก์ข้อมูลที่ค้นหาได้ไปใช้สร้างตารางคำนวณหรือจะใช้กับ Pivot Table

10. สร้างตาราง O-Output เพื่อลิงก์ผลจากการคำนวณ มาสร้างเป็นหน้ารายงานตามที่หัวหน้าต้องการ ซึ่งถ้าลิงก์จาก Pivot Table ให้ใช้สูตร GetPivotData ดึงค่าที่ต้องการออกมาใช้ต่อ

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

12. ตกแต่งตารางรายงานให้สวยงาม ตีกรอบ ใส่สีให้เห็นเด่นชัดโดยใช้คำสั่ง Format

ทั้งหมดนี้เป็นเพียงขั้นตอนคร่าวๆ Baby Steps

สมาธิช่วยในการใช้ Excel ได้อย่างไร

สมาธิช่วยในการใช้ Excel ได้อย่างไร

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

 

สมาธิกับปัญญาเป็นสิ่งที่ใช้ควบคู่กันเสมอ 

คนที่ชอบคิดอยู่แล้วให้ใช้ปัญญาอบรมสมาธิ ส่วนคนที่มีจิตใจสงบอยู่แล้วให้ใช้สมาธิอบรมปัญญา

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

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

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

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

ลูกศิษย์ที่เคยเรียนกับผมมาก่อน เจอวิธีการเปิดตัวแบบนี้มาแล้วทั้งนั้น

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

ผมรวบรวมคำสอนวิธีการฝึกสมาธิสายวัดป่าไว้ที่เว็บ YaJai.com 

แนะนำเรื่องปัญญาอบรมสมาธิ ของพระอาจารย์หลวงตามหาบัว
https://yajai.com/index.php/dhamma-master/lt-bua/117-panya-samathi

วิธีฝึกสมาธิแบบลมหายใจ ของท่านพ่อลี ธมฺมธโร วัดอโศการาม
https://yajai.com/index.php/dhamma-master/tp-lee

เชิญชมคลิปวิธีการเริ่มเรียนและ download แฟ้มที่ใช้ได้จาก
https://www.excelexperttraining.com/book/index.php/a-to-z/others/and-sign/1-begin

 

 

 

 

 

 

 

 

ก่อนจะยกนิ้วให้ว่าแน่ เป็นสูตรขั้นเทพขั้นเซียน ต้องดูยังไง

 

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

1. ทดสอบกับค่าที่มากหรือน้อยกว่าปกติ หรือใส่ค่าติดลบ หรือใส่ตัวเลขแทนตัวอักษร หรือใส่ตัวอักษรแทนตัวเลข ยังคงได้รับคำตอบถูกต้องตามต้องการหรือไม่

2. ลองกลั่นแกล้งทุกวิถีทางว่า สูตรยังคงให้คำตอบถูกต้องตามเดิมไหม ถ้าโครงสร้างตารางต่างไปจากเดิม ให้ลองย้ายเซลล์ไปที่อื่น Insert Column/Row แทรก ลองเปลี่ยนชื่อชีทหรือชื่อแฟ้ม

3. ลองเปลี่ยนเงื่อนไขในการคำนวณให้ต่างไปจากเดิมแล้วดูว่าสูตรนั้นยังคงให้คำตอบได้ตามเดิมหรือไม่ หรือถ้าต้องปรับแก้ไขใหม่ ต้องวุ่นวายมากน้อยขนาดไหน 

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

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

6. ลองนำแฟ้มไปเปิดที่เครื่องอื่นโดยเฉพาะเครื่องที่ใช้ Excel ต่างรุ่นกันว่ายังคงคำนวณหาคำตอบได้ถูกต้องตามเดิมหรือไม่

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

หน้าตาสูตรแบบผู้ดี เห็นแล้วจะได้ไม่ส่ายหัว

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

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

ภาพแรกบนสุดเดิมทีสูตรที่สร้างไว้ติดกันแบบนี้

=IF($K15>=$D$17,$F$17,IF($K15>=$D$16,$F$16,IF($K15>=$D$15,$F$15,$F$14)))

สูตรล่างสุดล่ะ

=INDEX(SMALL(IF(ISNA(MATCH(WEEKDAY(ROW(INDIRECT(PushFrom&":"&PushFrom

+MaxDays))),WeekdayNum,0))*ISNA(MATCH(ROW(INDIRECT(PushFrom&":"&PushFrom

+MaxDays)),SpecialHoliday,0)),ROW(INDIRECT(PushFrom&":"&PushFrom+MaxDays))),

ROW(INDIRECT("1:"&MaxDays))),PushWrkDays,1)

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

ลูกศิษย์ตั้งชื่อให้ว่า สูตรแบบผู้ดี้ผู้ดี

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

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

ทางออกที่ดีกว่าคือหันไปสร้างสูตรด้วย VBA ทำเป็น Add-in แล้วเวลาใช้สูตรจะได้เหลือสูตรสั้นๆหน้าตาแบบสูตร VLookup XLookup นั่นแหละครับ

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

เซลล์ A1 สร้างสูตร =Sum(B11:B15)

เซลล์ A2 สร้างสูตร =Sum(G1:G34)

เซลล์ A3 สร้างสูตร =A1+A2

ให้ลอกสูตรใน A1 กับ A2 มาใส่ลงไปในสูตรเซลล์ A3 จะได้ =Sum(B11:B15)+Sum(G1:G34)

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

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

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

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

.

ลอกมาให้ชมกัน

.

=LET(Wz,SORT(UNIQUE(A8:D17)),E,E8:E17,Ty,UNIQUE(C8:C17),Re,UNIQUE(D8:D17),Mx,C8:C17,Cx,F8:F17,Cd,INDEX(Ty,1),Yh,INDEX(Ty,2),data,TEXTJOIN(",",TRUE,FILTER(Cx,Mx=Cd)),data2,TEXTJOIN(",",TRUE,FILTER(Cx,Mx=Yh)),To,SUMIFS(E,Mx,Cd),Ti,SUMIFS(E,Mx,Yh),G,VSTACK(HSTACK(To,data),HSTACK(Ti,data2)),R,HSTACK(Wz,G),R)

.

=LET(Ty,UNIQUE(C8:C17),Re,UNIQUE(D8:D17),Mx,C8:C17,Cx,F8:F17,Cd,INDEX(Ty,1),Yh,INDEX(Ty,2),data,TEXTJOIN(",",TRUE,FILTER(Cx,Mx=Cd)),data2,TEXTJOIN(",",TRUE,FILTER(Cx,Mx=Yh)),To,SUMIFS(E8:E17,Mx,Cd),Ti,SUMIFS(E8:E17,Mx,Yh),VSTACK(HSTACK(Cd,INDEX(Re,1),To,data),HSTACK(Yh,INDEX(Re,2),Ti,data2)))

.

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

.

เรื่องที่คิดว่าไม่ง่ายที่จะตอบ ควรมาถามที่ fb กลุ่มคนรัก Excel ดีกว่าครับ ติดตามโพสต์ของตัวเองได้ง่าย 

 

 

 

 

เชิญ Download โปรแกรมดูดวง แฟ้มนี้สร้างด้วย Excel

เชิญ Download โปรแกรมดูดวงได้จาก https://www.excelexperttraining.com/download/test4zr.xlsb

ผมปรับให้แฟ้มนี้ใช้ทำงานได้เกือบ 99.99% ของโปรแกรม 4ZSuriya ชุดล่าสุด เรียนรู้วิธีใช้งานได้จากคลาสเรียนออนไลน์ที่แจกให้เรียน ฟรี โดยใช้ลิงก์นี้ในการสมัคร https://xlsiam.com/purchase/?plan=1318

แนะนำให้สมัครเรียนออนไลน์ก่อน จะมีคำอธิบายวิธีใช้แฟ้มนี้ว่าจะเปิดกันยังไงแล้วกรอกวันได้ด้วยวิธีไหน

👍 แฟ้มนี้เป็นตัวอย่างที่ใช้ฝีมือแบบสุดๆของผม จะได้เปิดหูเปิดตาว่า Excel ใช้ทำอะไรได้บ้าง โดยเฉพาะการสร้างเป็น Dashboards เพื่อนำเสนอผลงาน ทุกภาพที่เห็นนี้เป็นกราฟ Excel ทั้งหมด สามารถปรับการแสดงผลให้ยืดหยุ่นตามผลการคำนวณหรือจะเปิดปิดแบบ Manual ก็ยังได้

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

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

 

รูปแบบเวลา 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 ก็เสร็จเรียบร้อยแล้ว