ในแฟ้มมีหลายชีทที่มีหน้าตาเดียวกัน จะสร้างสูตรเดียว ที่ใช้งานในทุกชีทได้ยังไง

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

ปกติชื่อ Range Name ที่สร้างขึ้น จะถูกกำหนดพื้นที่ตารางที่จำกัดเฉพาะชีทใดชีทหนึ่งเท่านั้น สังเกตุได้จากช่อง Refers to จะมีชื่อชีทกำกับด้านหน้าตำแหน่งเซลล์ไว้เสมอ เช่น ตามภาพตัวอย่างนี้ เมื่อตั้งชื่อพื้นที่ตัวเลขว่า MyData จะพบการอ้างอิงในช่อง Refers to : =Sheet1!$B$2:$B$10

เมื่อมีชื่อชีทกำกับไว้ด้านหน้านี่แหละ ทำให้เวลาดึงข้อมูลโดยใช้สูตร =Sum(MyData) ก็จะได้ยอดรวมเท่ากับ 9 ซึ่งมาจากตารางในชีทชื่อ Sheet1 เสมอ ไม่ว่าจะสร้างสูตร Sum นี้ไว้ในชีทใดก็ตาม

แต่ถ้าอยากจะหายอดรวมจากพื้นที่ $B$2:$B$10 จากชีทอื่นล่ะ วิธีที่มักพบว่าใช้กันก็คือจะตั้งชื่อใหม่ที่ไม่ซ้ำกับชื่อเดิมขึ้นมาอีก ทำให้ในแฟ้มมีหลายชื่อ หรือไม่ก็ใช้วิธีเติมเลขต่อท้ายชื่อ กลายเป็น MyData1, MyData2, MyData3

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

=Sheet1!$B$2:$B$10

ให้ลบส่วนของชื่อชีททิ้งไป เหลือแต่เครื่องหมาย ! แก้เป็น

=!$B$2:$B$10

คราวนี้แหละพอลิงก์ค่าจาก MyData มาใช้ ก็จะดึงข้อมูลจากพื้นที่ $B$2:$B$10 ของแต่ละชีทมาให้ โดยในแฟ้มจะมีชื่อ MyData เพียงชื่อเดียวเท่านั้น

Download ตัวอย่างได้จาก

https://drive.google.com/file/d/1bDlmiNmMc8exeGo1M4o7Dy_OefCXqIyV/view?usp=sharing

ตัวอย่างนี้ใส่ตัวเลขในแต่ละชีทไว้ต่างกัน กรอกเลข 1, 2, 3, 4 สังเกตว่าทุกชีทมีสูตร Sum(MyData) เหมือนกัน แต่ให้คำตอบเป็นยอดรวมของตัวเลขในแต่ละชีท

 

มาว่ากันจริงๆเสียทีว่า เก่ง Excel ขั้นเทพขั้นเซียนเป็นยังไง คุณว่ายังมีขั้นอะไรที่เหนือกว่านั้นอีกไหม

ขั้นเทพ ขั้นเซียน เป็นคำที่ขึ้นอยู่กับ "คนมอง" กับ "สิ่งที่ถูกมอง" 

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

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

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

ถ้าตอนที่ใส่ $ เคยใช้แต่วิธีพิมพ์ $ เองลงไปเอง พอพบได้เห็นคนอื่นที่กดปุ่ม F4 เป็นก็ตาโตเรียกเป็นขั้นเทพขั้นเซียนไปแล้ว

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

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

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

ขั้นเทพ ขั้นเซียน ยังขึ้นกับ "กลุ่มของคนใช้"

อย่างที่ผมเป็น MVP มีโอกาสได้เข้ากลุ่มลับที่มีแต่คนที่ไมโครซอฟท์ยกย่องให้เป็น Most Valuable Professional ถ้าคุณได้เห็นอย่างที่ผมเห็น ที่คนคนเก่งๆเขาคุยกัน แลกเปลี่ยนความรู้กันนั้น ต้องคิดในใจเลยว่า "อะไรกันวะเนี่ย" แล้วจะรู้ตัวเลยว่าเหนือฟ้ายังมีฟ้าอีกหลายชั้น

 

วิธีเปลี่ยนชื่อยาวให้สั้น เปลี่ยนตัวเลขให้เป็นความลับ แก้ทีเดียวเปลี่ยนตามทั้งแฟ้ม

 

 

ภาพนี้แสดงการใช้ชื่อแบบพิเศษ ไม่ใช่ชื่อที่ตั้งให้กับเซลล์ (Range Name) แต่เป็นชื่อที่ตั้งให้กับค่าคงที่ จะเป็นตัวอักษรก็ได้ ตัวเลขก็ได้ ชื่อแบบนี้เรียกว่า Constant Name

1. แทนที่จะกรอกค่าลงไปในเซลล์แล้วจึงลิงก์ไปใช้ต่อ ใช้ไปใช้มาแล้วจำไม่ได้ว่าเซลล์ต้นทางอยู่ที่ไหน 

2. แทนที่จะเปิดเผยให้เห็นว่าตัวเลขที่ใช้คำนวณเป็นอะไร

3. แทนที่จะต้องไปไล่แก้ไขที่เซลล์โน้นเซลล์นี้ ชีทโน้น ชีทนี้

ให้ใช้เมนู Formulas > Name Manager > New แล้วตั้งชื่อที่ต้องการลงไปในช่อง Name  ส่วนในช่อง Refer to ด้านล่าง ให้ใส่เครื่องหมายเท่ากับแล้วตามด้วยตัวเลข หรือเท่ากับแล้วตามด้วยตัวอักษรในเครื่องหมายคำพูดลงไป

Company    ="บริษัท ชื่อนี้ย้าวยาว ไม่จำกัด"
DateStart    =2024
SecretNum    =555

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

อยากแก้ไขให้ย้อนมาแก้ไขใน Name Manager > Edit ชื่อที่ต้องการ พอแก้เสร็จจะพบว่าทุกเซลล์ที่อ้างถึงชื่อนั้นจะแก้ตามข้อมูลใหม่ในทุกชีททุกเซลล์ให้ทันที

Download ตัวอย่างจาก

https://drive.google.com/file/d/16afQv8ir3MIn2qGjfFYWUdPvQ502rnkI/view?usp=sharing

เมื่อ I-O ครองโลก ไม่จำเป็นต้องมี I-C-O หรือแม้แต่ Excel อีกแล้ว

สมัยที่เพิ่งมีโปรแกรม Spreadsheet มีเรื่องเล่าว่า ในงานแสดงคอมพิวเตอร์โชว์ ขณะที่กำลังอวดให้ผู้ชมดูว่าเจ้าโปรแกรมนี้ทำงานยังไง มีนักบัญชีคนหนึ่งยกมือที่กำลังสั่นเทาชี้มาที่หน้าจอแล้วบอกว่า ขอซื้อโปรแกรมนี้ไปใช้ งานที่เห็นนี้เดิมทีเขาต้องใช้เวลาทำเป็นอาทิตย์ กลับทำได้ในพริบตา
.
ถ้านักบัญชีคนนั้นมาเจอ Pivot Table ก็คงจะตาโตหงายหลังไปเลยเพราะอยู่ดีๆ Excel ก็สร้างรายงานออกมาให้ จากเดิม I-C-O ที่ย่อมาจาก Input-Calculate-Output ที่คนใช้งานต้องคิดสร้างสูตรขึ้นมาคำนวณ กลับกลายเป็น I-P-O โดยใช้ Pivot เป็นตัวกลางหาคำตอบมาให้
.
แต่ Pivot มีข้อจำกัดที่ต้องเสียเวลาไปจัดการกับตารางข้อมูลให้มีโครงสร้างที่ใช้งานได้ก่อน ใน Excel ก็มีคำสั่ง Flash Fill เพื่อช่วยแยกแต่ละส่วนของคำ มี Remove Duplicate เพื่อใช้ลบรายการซ้ำ มี Power Query เพื่อดึงข้อมูลจากหลายแหล่งมาใช้ต่อใน Excel และตอนนี้เพิ่งมีปุ่ม Clean Data ให้ใช้ได้ทำความสะอาดพวกเว้นวรรคเกินมาอีก และที่สำคัญคือมีสูตรใหม่ๆที่ใช้จัดการข้อมูลสร้างขึ้นมาให้พวกเราใช้งาน
.
ว่าไปแล้ว I-P-O ของจริงไม่ได้เป็นตามนี้หรอก แต่เป็น I-C-P-O ยาวขึ้น เพราะคนยังต้องใช้ C หรือสูตรมาจัดการคำนวณให้มีข้อมูลพร้อมก่อนแล้วจึงส่งต่อไปให้ P-Pivot
.
.
.
. Tik Tok Tik Tok ๆๆๆๆ
.
โลกในฝันของผม อีกไม่นานจะเหลือแต่ I-O เพราะมีเจ้า AI เช่น ChatGPT หรือ Copilot มาช่วย
.
ไม่ว่าข้อมูลจะเก็บไว้แบบใด เก็บไว้ที่ไหน แม้แต่เก็บแบบมั่วๆไว้ก็ตาม พอ "พูดบอก" ไปที่หน้าจอว่าฉันต้องการรายงานหน้าตาแบบนี้นะ เจ้าคอมก็จัดการสร้างรายงานออกมาให้เอง
.
โลกในฝันของคุณเป็นยังไงบ้างครับ
.

วิธีทำชีทเดียว ตารางเดียว ให้ "ปิ๊ง" ถูกใจหัวหน้าหลายๆคน

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

Excel จะจดจำพื้นที่ตามการจัดการซ่อน Row/Column ซึ่งเกิดจากการสั่ง Hide ที่ทำเอง หรือตามที่สั่ง Data > Filter ให้กรองข้อมูลให้เลือกแสดงตามที่เลือกไว้

👉 เริ่มต้นจากจัดการปรับโครงสร้างตารางให้แสดงตามที่ต้องการไว้ก่อน แล้วสั่ง View > Custom View > Add > แล้วตั้งชื่อให้กับ View นั้น

เมื่ออยากจะแสดงโครงสร้างแบบไหนก็เพียงคลิกชื่อ View ที่ต้องการแล้วกดปุ่ม Show

นอกจากนั้น Custom View ยังช่วยจดจำหน้าตาตารางตาม Print Settings ให้ด้วย ช่วยจัดหน้ากระดาษให้ทันทีตาม View ที่แสดง

ที่นึกไม่ถึงกันอีกอย่าง Custom View ไม่จำกัดเพียงแค่ชีทเดียวเท่านั้น หากสามารถช่วยทำให้ Excel ย้ายไปแสดงหน้าตาตารางที่ชีทใดก็ได้อีกด้วย

สูตรใหม่ คำสั่งใหม่ มีไว้ให้ใช้คู่กับ Copilot ชีวิตจะง่ายขึ้น ไม่ต้องท่อง ไม่ต้องคิดเองอีกต่อไป ชมคลิป...

ผมเป็นคน 4 สมัย 1. PC 2. Internet 3. มือถือ 4. AI

ไม่น่าเชื่อว่าเทคโนโลยีพัฒนาไปเร็วมาก ไม่กี่สิบปีคนเราเปลี่ยนวิธีการใช้ชีวิตต่างไปจากเดิมอย่างสิ้นเชิง 

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

มีของใหม่เกิดขึ้นมาให้เราใช้เร็วมาก ทั้งสูตรใหม่ คำสั่งบนเมนูใหม่ๆ ไม่ใช่แค่ Excel ที่สามารถใช้งานร่วมกับ VBA เท่านั้น ตอนนี้ยังมี Power BI, Power Query, Power Pivot, Python, Office Script หรืออีกหลายอย่างที่กำลังแอบสร้างขึ้น เช่น Quantum Computer ที่มากับชิบ AI เป็นความลับที่จะทะยอยเปิดตัว...ให้เราใช้

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

ไหวไหมที่จะคอยติดตามการเปลี่ยนแปลงได้ทัน ไม่กี่วันก็มีอะไรๆใหม่ๆเกิดขึ้นอีกแล้ว

ไม่กี่ปีก่อนโน้น ก่อนยุค AI ก่อนยุค 365 ผมเคยแนะนำว่า ควรสร้างหน่วยงานที่เป็น Excel User Specialist ขึ้นมาให้เขาติดตามเรียนรู้ Excel แล้วทำหน้าที่สร้างแฟ้มให้คนอื่นใช้ แต่พอถึงวันนี้ที่มีแอปใหม่ๆเยอะแยะแบบนี้ แทบไม่มีทางเลยที่คนๆเดียวจะเก่งไปทุกอย่าง ยากมากที่จะเป็น Generalist 

จะให้เก่งแบบเป็ด รู้นิดรู้หน่อย หัวสมองรับไหวไหม

เรื่องที่ควรเรียนรู้ตอนนี้ ควรมุ่งหาทางใช้เจ้า AI หรือ Copilot ให้ทำงานแทน ขอเพียงคอยติดตามเรียนรู้ไว้บ้างว่า Excel มีอะไรใหม่ๆไว้ให้ใช้บ้างก็พอ

แนะนำให้ติดตามของใหม่ใน Excel ได้จากเว็บของ Microsoft > Microsoft Excel Blog

จะมีสูตรใหม่ๆ อะไรใหม่อีกเยอะแยะ สร้างขึ้นมาให้ใช้งานร่วมกับ Excel 365 โดยเตรียมไว้ให้เจ้า Copilot ใช้เป็นหลัก ขอให้ตั้งใจไว้แบบนี้นะครับ
 

เชิญชมคลิปเหล่านี้

   

 

ให้งบโฆษณา =15% ของกำไร จะหางบได้ยังไง เมื่อกำไรเพิ่ม โฆษณาย่อมเพิ่ม แต่พอค่าใช้จ่ายเพิ่ม กำไรต้องลด

อยากโฆษณา ต้องมั่นใจว่าต้องได้กำไรเพิ่ม

ให้งบโฆษณาไว้เป็น 15% ของกำไร

แต่พอค่าใช้จ่ายเพิ่ม กำไรจะลด เพราะค่าโฆษณาเป็นค่าใช้จ่าย

พอกำไรลด ค่าโฆษณาก็จะลดตาม

 

เมื่อค่าโฆษณาลด กำไรก็จะเพิ่ม แล้วงบ 15% ทำให้ได้ค่าโฆษณาเพิ่ม

พอค่าใช้จ่ายเพิ่ม กำไรก็จะลด แล้วทำให้ค่าโฆษณาลด

 

จะใช้ Excel คำนวณหาค่าโฆษณาตามภาพนี้ได้ยังไง

 

ตัวอย่างนี้มาจากหลักสูตร สุดยอดเคล็ดลับและลัดของ Excel ซึ่งเปิดให้สมัครเรียนออนไลน์ฟรี 1 ปี เชิญสมัครและเข้าเรียนได้ทุกที่ทุกเวลาที่สะดวกได้ที่เว็บ XLSiam.com

 

Download ตัวอย่างนี้ได้จาก
https://drive.google.com/file/d/1wQ1IRrrioc4RHd9nKR5V9OH2-ZGY5BJw/view?usp=sharing 

 

ปัญหานี้ไม่ง่ายอย่างที่คิด หลายคนหายอดโฆษณาโดยสร้างสูตร =0.15*D7

ซึ่งสูตรนี้จะทำให้ Excel โวยวายออกมามาว่าเกิดการคำนวณแบบวงกลม (Circular) ขึ้นมาแล้ว ตามภาพ

การสร้างสูตร =0.15*D7 ลงไปแล้วเกิด Circular ต้องแก้โดยการไปกาช่อง Enable iterative calculation ใน Excel Options > Formulas ตามภาพ

 

 

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

ปัญหานี้ สามารถแก้ได้โดยใช้ Data > What-if Analysis > Goal Seek ตามภาพ ซึ่งไม่จำเป็นต้องไปใช้วิธีสร้างสูตรที่จะเกิด Circular Calculation

 

Excel จะสุ่มค่าโฆษณาใส่ค่าไปเรื่อยๆจนกระทั่งพบว่าคิดเป็น 15% ของกำไร

 

 

 

 

 

จงใช้ Excel หาว่า เลขอะไรเอ่ย คูณ 3 ได้ผลลัพธ์เท่ากับ 77.77

อย่าไปใช้วิธีคิดแบบเอาเลข 77.77 ตั้งแล้วหารด้วย 3 ทีเดียวนะครับ นั่นไม่ใช่วิธีใช้ Excel

ถ้าใช้ Excel ต้องสามารถหาคำตอบอื่นได้ด้วยว่า 

เลขอะไรเอ่ย คูณ 3 ได้ผลลัพธ์เท่ากับ 123.45

เลขอะไรเอ่ย คูณ 3 ได้ผลลัพธ์เท่ากับ 55.55

เลขอะไรเอ่ย คูณ 3 ได้ผลลัพธ์เท่ากับ เลขอะไรก็ได้ที่กำหนดไว้

เริ่มจากกรอกเลข 2 กับ 3 ลงไปในเซลล์ B2 กับ C2 (หรือตรงไหนก็ได้) จากนั้นจับ 2 เซลล์นี้มาคูณกัน เช่น ตามภาพนี้เซลล์ D2 สร้างสูตร =B2*C2 เอาไว้ ซึ่งตอนนี้ได้คำตอบเท่ากับ 6

1. คลิกเลือกเซลล์ B2 เอาไว้ก่อน ซึ่งถือว่าเป็นเซลล์ที่อยากจะให้ Excel หาเลขใหม่ให้ เรียกว่า Changing Cell (Excel รุ่นใหม่ๆมีข้อกำหนดให้เลือกเซลล์นี้ไว้ก่อนเสมอ)

2. ใช้เมนู Data > What-IF Analysis > Goal Seek จะพบหน้าจอเล็กๆขึ้นมา

3. ในช่อง Set Cell ให้คลิกไปที่เซลล์ D2 ซึ่งเป็นสูตรหาคำตอบที่ต้องการ

4. ในช่อง To Value ให้พิมพ์เลข 77.77

5. ในช่อง By Changing Cell ให้คลิกเซลล์ B2 ซึ่งมีเลข 2 กรอกไว้ก่อนแล้ว

6. กดปุ่ม OK

ความมหัศจรรย์ก็จะบังเกิดขึ้นมาให้เห็น Excel จะสุ่มตัวเลขใหม่ลงไปในเซลล์แทนที่เลข 2 ไปเรื่อยๆ จนได้ตัวเลขคำตอบเป็นเลข 25.9233333333333

Goal Seek เป็นเครื่องมือที่ขาดไม่ได้สำหรับการวางแผนตัดสินใจ โดยเฉพาะการวิเคราะห์โครงการ ศึกษา Sensitivity Study Analysis หรือแก้ปัญหาที่ต้องคำนวณแบบ Circular References

Goal Seek สำหรับนักคณิตศาสตร์ ใช้ในการแก้สมการ

จุดอ่อนของ Goal Seek

1. ต้องเสียเวลากรอกตัวเลขเป้าหมายใหม่ลงไปเองทุกครั้ง ต้องใช้ VBA ช่วยจะสามารถลิงก์ค่าจากเซลล์ไปใช้โดยไม่ต้องมากรอกค่าใหม่

2. เซลล์ที่ต้องการให้เปลี่ยนไปหรือ Changing Cell ต้องเป็นเซลล์ที่กรอกตัวเลขไว้เท่านั้น ห้ามเป็นเซลล์สูตรหรือมีค่าเป็นตัวอักษร

ชมคลิปการใช้ Goal Seek ได้จาก https://www.youtube.com/watch?v=uOomN33uzpI

 

Download แฟ้มตัวอย่างการใช้วางแผนภาษี

https://drive.google.com/file/d/1lJXBnO33ArdRM1Iqu3veTX06vcGHs9zE/view?usp=sharing

 

แฟ้มตัวอย่างต้องไปกาช่อง Unblock ใน File Properties ก่อนจึงจะเปิดใน Excel แล้ว VBA ทำงานได้ครับ

 

 

 

 

เซลล์อะไรหนอ ลอยออกมาอยู่นอกเซลล์ คร่อมเซลล์ได้ด้วย

 

ตารางแสดงยอด Daily Report ตรงกลางภาพนี้สามารถแสดงตัวเลขตามแต่ว่าอยากจะให้แสดงเป็นยอดรับ ยอดจ่าย หรือยอดสุทธิ โดยไปคลิกเลือกปุ่มกลมๆในกรอบด้านล่างขวา

ในกรอบที่เห็นคำว่า Receive ตรงปลายลูกศร แสดงสูตรว่าลิงก์มาจากเซลล์ N22 ซึ่งจะเปลี่ยนข้อความจาก Receive > Deliver > Net ตามการคลิกกาช่องวงกลม

😂 กรอบนี้ไม่ใช่เซลล์หรอกครับ แต่เป็นรูปภาพสี่เหลี่ยมที่สร้างขึ้นแล้วจัดการสร้างสูตรลิงก์ =N22 ลงไปในรูปสี่เหลี่ยม ซึ่งดูเหมือนเป็นเซลล์แต่จริงๆแล้วไม่ใช่เซลล์

👉 ทำไว้เพื่อไปวางทับเซลล์ ใช้ซ่อนไม่ให้เป็นค่าของจริงในเซลล์ด้านล่าง

ส่วนเซลล์ด้านล่าง ถูกซ่อนอีกหลายอย่างเพื่อทำให้ไม่ให้เห็นค่า โดยใช้ Font สีเดียวกับสีพื้น และยังใช้ Custom Format ;;; ไว้อีก

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

Download แฟ้มตัวอย่างนี้ไปลองแกะกันได้จาก
.
https://docs.google.com/spreadsheets/d/1_sQaEuC-vtCz2vtMRoR9aL1ui2dv_m7t/edit?usp=sharing&ouid=116915095293786385908&rtpof=true&sd=true

แฟ้มนี้ต้องสั่ง Unprotect Sheet ก่อนครับ ไม่ได้ใส่รหัสอะไรป้องกันไว้ เชิญแกะได้ตามสบาย 

Range Name ประโยชน์มหาศาล

ตามภาพในกรอบสี่เหลี่ยมเป็นช่องที่เรียกว่า Name Box ครับ ใช้สำหรับตั้งชื่อ Range Name ให้กับเซลล์หรือพื้นที่ตาราง อยากตั้งชื่อพื้นที่ตรงไหนให้เลือกพื้นที่เซลล์ตามต้องการไว้ก่อนจากนั้ค่อยพิมพ์ชื่อที่ต้องการตั้งแล้ว Enter ลงไปในช่องนี้ ... ขั้นตอนการตั้งชื่อมีง่ายๆแค่นี้เอง

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

2. Range Name ช่วยทำให้สูตรมีความหมายในตัวเอง แกะสูตรได้ง่าย เช่น ตามภาพนี้มีสูตร =SUM((Product=ProductChoice)*(Date>=From)*(Date<=To)*Receive)

ซึ่งบอกว่าเป็นการหายอดรวมของการรับเงิน Receive จากสินค้า Product ที่มีชื่อตรงกับชื่อสินค้าที่เลือก ProductChoice จากช่วงวันที่ Date ตั้งแต่วันนั้น From ถึงวันนี้ To ซึ่งตอนที่สร้างสูตรให้กดปุ่ม F3 เพื่อนำชื่อมาใส่ลงไปในสูตร

3. การกดปุ่ม F3 เพื่อนำชื่อมาใช้ในสูตร ช่วยประหยัดเวลาไม่ต้องไปคลิกเลือกพื้นที่ ลดความผิดพลาดที่อาจเลือกพื้นที่ไม่ครบหรือผิดที่

4. Range Name เป็นสื่อกลางที่เชื่อม Excel กับ VBA ช่วยทำให้รหัส VBA สั้นลงอย่างมาก และรหัสยังคงทำงานต่อได้ตามเดิมแม้มีการโยกย้ายตำแหน่งเซลล์หรือเปลี่ยนชื่อชีทชื่อแฟ้ม

5. Range Name เป็นชื่อที่น่าใช้กว่า Table Name ซึ่งจะถูกลบทิ้งหมดเกลี้ยงเมื่อไปสั่งเปลี่ยน Table ให้กลับไปเป็น Normal Range

☝️ หลักการตั้งชื่อ

ให้ตั้งชื่อเป็นภาษาอังกฤษตัวใหญ่ผสมตัวเล็ก ห้ามเว้นช่องว่าง ถ้าต้องการผสมกับตัวเลขต้องให้ตัวเลขต่อท้ายตัวอักษร และตัวอักษรด้านหน้าตัวเลขต้องมี 4 ตัวขึ้นไปเพื่อไม่ให้ซ้ำกับตำแหน่ง Reference A1 - XFDnnn

✋ เมื่อใดที่ไม่จำเป็นต้องไปตั้งชื่อ Range Name

ถ้างานใดทำเพียงครั้งเดียวแล้วไม่ต้องนำมาใช้ซ้ำอีก ไม่จำเป็นต้องใช้ Range Name

ถ้าอยากแก้ไขหรือลบชื่อที่ตั้งไว้ผิด ให้ทำผ่านเมนู Formulas > Name Manager

จะส่งแฟ้มให้ใครใช้ ต้องผ่านมือมิสเตอร์คีดะก่อน

รู้จักมิสเตอร์คีดะไหมครับ

คีดะ มาจากคำว่า Keyboard กับดะ เป็นคำภาษาไทยที่แปลว่า ตะลุยไป ไม่เลือกหน้า เช่น กินดะ เก็บดะ ไหว้ดะ

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

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

ทำให้ดีขึ้นควรทำให้เมื่อกดปุ่ม TAB ไปเรื่อยๆ Excel จะกระโดดไปหาเซลล์ Input ให้เอง 

1. เริ่มต้นให้เลือกเซลล์ Input ที่ทำไว้

2. คลิกขวาที่เซลล์ สั่ง Format Cells > Protection > ตัดกาช่อง Locked ทิ้งไป

 

3. สั่ง Review > Protect Sheet

Download แฟ้มนี้แล้วลองกดปุ่ม TAB (พิมพ์ค่าใหม่แล้วกดปุ่ม TAB แทน Enter)

https://docs.google.com/spreadsheets/d/1_sQaEuC-vtCz2vtMRoR9aL1ui2dv_m7t/edit?usp=sharing&ouid=116915095293786385908&rtpof=true&sd=true 

ปล

ช่อง Hidden ที่เห็นกาไว้นั้นเพื่อทำให้เมื่อสั่ง Protect Sheet แล้ว เซลล์นั้นจะมองไม่เห็นสูตรหรือค่าที่อยู่ในเซลล์นั้น และทำให้เมื่อ Copy เซลล์นั้นออกไปจะได้เฉพาะค่าผลลัพธ์ของสูตร ไม่ติดตัวสูตรไปด้วย

Power Excel AI ที่ใช้ในการให้คำพยากรณ์ทางโหราศาสตร์ไทย (แจกแฟ้มหาดูได้ยาก)

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

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

ตัวเลขตำแหน่งราศีและภพ หาได้จากภาพจักรราศีที่จะได้รับมาตอนที่ไปดูดวงนั่นแหละครับ สมมติว่าได้ภาพมาตามนี้

ตัวเลขสีแดงนอกวงกลม เป็นตัวเลขราศี เริ่มจากเลข 0 - 11 ส่วนเลขภพนั้นต้องนับกันเองจากช่องลัคนาที่มีตัว ล นับเป็นภพที่ 1 แล้วเวียนทวนเข็มนาฬิกา เรียงเลขที่ภพจากเลข 1 - 12 ซึ่งจากภาพนี้จะพบว่าในช่องที่มี ล ถือว่าเป็นราศีที่ 11 ภพที่ 1

ดาวเลข 6, 8, n อยู่ในราศีที่ 11 ภพที่ 1

ดาวเลข u อยู่ในราศีที่ 0 ภพที่ 2

ดาวเลข 5 อยู่ในราศีที่ 1 ภพที่ 3

ดาวเลข 3 อยู่ในราศีที่ 2 ภพที่ 4

ดาวเลข 9 อยู่ในราศีที่ 5 ภพที่ 7 

ดาวเลข 2, p  อยู่ในราศีที่ 9 ภพที่ 11 

ดาวเลข 1, 4, 7 อยู่ในราศีที่ 10 ภพที่ 12

(ดาว u, n, p ตามปกติไม่ได้ใช้ในโหราศาสตร์ไทย ถ้าในภาพราศีจักรไม่ได้แสดงไว้ก็กรอกอะไรก็ได้)

Download แฟ้มวิเคราะห์นี้ได้จาก

https://drive.google.com/file/d/1_pD_FkMHpauwlapLFvghSCltaKbhJ-nQ/view?usp=sharing

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

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

ทำไม Excel ถูกตั้งชื่อว่า Excel

Excel เกืดขึ้นมาในปีค.ศ.1985 ก่อนหน้านั้นมีชื่อว่า Multiplan ทำไมจึงต้องเปลี่ยนชื่อด้วย

 

เท่าที่ค้นหามาเจอว่าเพราะ Multiplan สร้างมาเพื่อใช้กับเครื่องรุ่น 8 bit และมีนับสิบรุ่นด้วย Microsoft ไม่อยากใช้ชื่อเก่าเพราะอยากให้ทันสมัยหน่อยเพื่อรับกับเครื่อง 16 bit

ตอนนั้น Lotus 1-2-3 ครองตลาดอยู่ก่อนแล้ว ที่ได้ชื่อว่า Excel นี้น่าจะเพื่อให้มีความหมายว่า excellence เพื่อให้สื่อว่าเหนือกว่า เยี่ยมกว่า 1-2-3 ซึ่งไม่รู้ว่าใครเป็นคนตั้งให้ทราบแต่ว่าเป็นผจกคนหนึ่งให้ชื่อนี้มา

ส่วนตัวผมเองเดาว่าเพราะช่วงนั้นมีหนังสือ In Search of Excellence ออกมาตั้งแต่ปี 1982 และได้รับความนิยมอย่างมากด้วย ใช้ชื่อ Excel นี้เลยติดปากได้ง่าย