Excel มีสูตร Groupby Pivotby แล้ว ทำไมยังต้องใช้ PivotTable

Excel มีสูตร Groupby Pivotby แล้ว ทำไมยังต้องใช้ PivotTable
.
Copilot ตอบมาว่า
.
GROUPBY และ PivotBY ใน Excel เป็นสูตรที่ช่วยสร้างตารางแบบไดนามิกในแบบที่เหมาะกับการวิเคราะห์ข้อมูลเฉพาะด้านได้ดี เช่นการจับกลุ่มข้อมูลและการคำนวณผลรวมโดยอัตโนมัติ อย่างไรก็ตาม PivotTable ยังคงมีจุดเด่นที่สำคัญในแง่ของความสะดวกและความสามารถในการจัดการข้อมูลที่หลากหลาย ทั้งนี้เพราะ:
.
1. **อินเตอร์เฟสและการใช้งานที่ง่าย**: PivotTable สามารถปรับแต่งการแสดงผลข้อมูลได้ง่าย ๆ ผ่านการลากและวาง ไม่ต้องเขียนสูตร ทำให้ประหยัดเวลาสำหรับผู้ที่ไม่ถนัดการใช้สูตรที่ซับซ้อน
.
2. **การทำงานแบบไดนามิก**: PivotTable ให้ความยืดหยุ่นในการเพิ่มและจัดเรียงข้อมูล รวมถึงการเพิ่มชั้นข้อมูล (Multi-level) เพื่อดูภาพรวมและรายละเอียดในเวลาเดียวกัน
.
3. **การแสดงผลข้อมูลเชิงภาพ**: PivotTable สามารถเชื่อมต่อกับ PivotChart เพื่อสร้างกราฟสรุปข้อมูลอย่างเป็นมืออาชีพได้ทันที
.
4. **การปรับแต่งขั้นสูง**: มีตัวเลือกต่าง ๆ เช่น การจัดรูปแบบตามเงื่อนไข (Conditional Formatting) หรือการเพิ่ม Calculated Field เพื่อสร้างตัวชี้วัดเฉพาะที่ตอบโจทย์
.
ดังนั้น ถึงแม้ GROUPBY และ PivotBY จะมีข้อได้เปรียบในด้านการสร้างแบบไดนามิกผ่านสูตร แต่ PivotTable ยังคงเป็นเครื่องมือสำคัญในงานที่ต้องการจัดการข้อมูลปริมาณมาก ๆ อย่างมีประสิทธิภาพ และตอบโจทย์การนำเสนอในรูปแบบที่ดูง่ายและเข้าใจได้ทันทีครับ 🙂
.

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

Excel มีสูตร Groupby Pivotby แล้ว ทำไมยังต้องใช้ PivotTable

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

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

... ผมตอบครับ 

 

วิธีทำ Dynamic GroupBY สูตรเดียว สร้างรายงานได้หลายๆๆๆๆแบบ

จากสูตรธรรมดาที่มีโครงสร้างตามนี้
=GroupBY( ตารางที่ต้องการจัดกลุ่ม, ตารางตัวเลขคำตอบ, ชื่อสูตร)

ทำให้สูตรนี้กลายเป็นสูตรที่ทำงานแบบ Dynamic
=GROUPBY(
CHOOSECOLS( B3:E27, I1 ),
CHOOSECOLS( F3:G27, K1 ),
VSTACK( SUM, MIN, MAX ) )

👉 CHOOSECOLS( B3:E27, I1 ) ช่วยเลือกส่วนของตารางที่ต้องการจัดกลุ่มให้จัดตาม Code, Department, Category, Item แล้วแต่จะคลิกเลือกในเซลล์สีเหลือง ตามลูกศรชี้ด้านซ้าย

👉 CHOOSECOLS( F3:G27, K1 ) ช่วยเลือกส่วนของตารางตัวเลขคำตอบว่าจะใช้ยอดจาก Budget หรือ Actual ก็ได้ตามแต่จะคลิกเลือกในเซลล์สีเขียว ตามลูกศรชี้ด้านขวา

โดยใช้สูตร Match ช่วยหาเลขที่ Column มาแสดงในเซลล์ I1 กับ K1

👉 VSTACK( SUM, MIN, MAX ) แสดงยอดรวม ยอดต่ำสุด ยอดสูงสุด

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

Download แฟ้ม Dynamic GroupBY
https://drive.google.com/file/d/1CttIhtKwiKnari73j_qQXpDvDGjTrW9k/view?usp=sharing

 

บ๊ายบาย Pivot ... เปลี่ยนไปใช้ GroupBY หายอดรวมรายเดือนรายวัน

แทนการใช้ PivotTable หายอดรายวันรายเดือนแล้วยากเหลือเกินที่จะเปลี่ยนหัวตารางให้แสดงวันเดือนปีใน Format ที่อยากได้ ให้ใช้สูตร GroupBY ตามภาพนี้สะดวกกว่า

สรุปยอดขายรายวัน
=GROUPBY ( B5:B384, H5:H384, SUM )

B5:B384 เป็น column วันที่
H5:H384 เป็น column ยอดขาย 

สรุปยอดขายรายเดือน
=GROUPBY( MONTH(B5:B384), H5:H384, SUM )
อยากให้แสดงตามเลขเดือน ให้ใส่สูตร MONTH(B5:B384) เข้าไป สูตรจะแยกตามรายเดือนให้เองโดยไม่ต้องเสียเวลาไปปรับเพิ่มในตารางต้นทาง

ถ้าอยากแยกรายปีรายเดือนด้วย ใช้สูตร
=GROUPBY( HSTACK( YEAR(B5:B384), MONTH(B5:B384) ), H5:H384, SUM )

HSTACK( YEAR(B5:B384), MONTH(B5:B384) ) ทำหน้าที่เพิ่มปีกับเดือน 

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

ถ้าอยากดูว่าแต่ก่อนโน้นสมัยที่ใช้ Pivot ต้องปรับแก้ยังไง เชิญไปย้อนอดีตดูได้ที่
https://www.excelexperttraining.com/book/index.php/excel-articles-and-videos/general/change-thai-date-in-pivot-table

 

จะเลือกคนใช้ Excel แบบไหนระหว่าง ทำได้ vs ทำเป็น

Excel Table, Range Name, Pivot Table, VLookup, XLookup, Logic Operations, Filter, Conditional Format, Data Validation ผมกำหนดหัวข้ออบรมเหล่านี้เพื่อใช้สอนให้กับนิสิตจุฬา เมื่อเรียนจบออกมาจะได้พร้อมที่จะทำงานได้ทันที

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

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

ทำได้ vs ทำเป็น คือประเด็นสำคัญ

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

ทำสำคัญคือ กว่าจะทำออกมาได้แบบนั้นเหมือนๆกัน กว่าจะใช้ VLookup, XLookup, Pivot Table หาคำตอบออกมาได้นั้น ต้องผ่านขั้นตอนการสร้างงานอย่างไรบ้าง ไม่ใช่ว่าอยู่ดีๆก็ยกสูตรนั้นสูตรนี้มาเรียนกัน ก่อนอื่นต้องเข้าใจวิธีการใช้ Excel อีกตั้งหลายเรื่อง เช่น การใช้เครื่องหมาย $ วิธีสร้างสูตรลิงก์ วิธีแกะสูตร และที่สำคัญที่สุดคือ ต้องเรียนรู้วิธีการสร้างตารางเก็บข้อมูล ถ้าไม่เริ่มตรงนี้ก่อนก็จะใช้อะไรๆต่อไม่ได้

Excel Table, Range Name, Pivot Table, VLookup, XLookup, Logic Operations, Filter, Conditional Format, Data Validation

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

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

มีคนเล่าให้ฟังว่า Conditional Format มีคนใช้เป็นไม่ถึง 5%

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

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

เคล็ดอยู่แค่การใช้เมาส์เลือกพื้นที่จากมุมซ้ายมาล่างแล้วใช้ $ ควบคุมสูตร

ชมคลิป https://www.excelexperttraining.com/book/index.php/a-to-z/f-g-h-i-j/f-f-f-f-f/conditional-format

ตัวอย่างการทำ Gantt Chart
https://www.excelexperttraining.com/book/index.php/excel-articles-and-videos/general/gantt-chart-tip-and-trick

https://www.excelexperttraining.com/book/index.php/excel-articles-and-videos/general/highlighting-input-cells

เคยรู้สึกแบบนี้ไหม นี่ไม่ใช่ฝีมือของฉัน ฉันไม่ใช้ฉันหรอกตอนที่สร้างแฟ้มนี้

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

ผลงานที่น่าประหลาดใจของผม

1. คิดหลักการออกแบบตารางคำนวณ ช่วยทำให้สามารถสร้างสูตรยากได้ง่ายขึ้น สามารถนำตารางไปใช้ต่อและแก้ไขได้ง่าย ต่อมาเว็บของบริษัทไมโครซอฟท์(ประเทศไทย)ได้นำบทความนี้ไปแสดง

Download ได้จาก https://drive.google.com/file/d/0Bxpfgh-rqhIWaFMxWWdXaUo0ekE/view?usp=sharing&resourcekey=0-PzTafKwVSBDQM_hOFE70mw

2. สามารถนำหลักการออกแบบตารางคำนวณมาประยุกต์ใช้กับงาน Material Requirements Planning โดยไม่จำกัดจำนวนชั้นของโครงสร้าง Bill of Materials (BOM) ช่วยทำให้ประหยัดเงินไม่ต้องซื้อโปรแกรมสำเร็จรูปราคาแพงมาใช้ ซึ่งโดยทั่วไปแนะนำกันว่าไม่สามารถใช้ Excel คำนวณได้หรือไม่ก็ใช้ Excel กับ BOM ได้เพียงชั้นเดียวเท่านั้น

Download ได้จาก https://www.excelexperttraining.com/online/courses/03-free-free-free/lessons/mrp/

3. สร้างแนวทางการใช้ Excel จัดการฐานข้อมูล ทำให้ไม่ต้องพึ่งพาโปรแกรมอื่นที่ใช้งานยากกว่า แนะนำให้สมัครเรียนออนไลน์ ฟรี้ 1 ปีที่เว็บ https://xlsiam.com/ ซึ่งนำประเด็นนี้อธิบายไว้ในแทบบทุกหลักสูตร

4. สร้างตารางวางแผนรับจ่ายเงิน Scheduling ช่วยวางแผนกำหนดกำหนดการหลากหลายเงื่อนไข

https://drive.google.com/file/d/0Bxpfgh-rqhIWX1B6YkQzaHlJMTQ/view?usp=sharing&resourcekey=0-ZFTnc4nJEaSfOO8xUn-LOQ

5. สามารถใช้ Excel ช่วยในการวิเคราะห์ Sensitivity Analysis ได้อย่างไม่จำกัดตัวแปร ซึ่งโดยทั่วไป Excel จะรับตัวแปรได้สูงสุดไม่กี่ตัวเท่านั้น

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

6. คิดวิธีคำนวณต้นทุนขายแบบ FIFO โดยใช้คำสั่ง Data Table ช่วยทำให้หาคำตอบได้ง่าย ซึ่งที่ผ่านมาทราบกันแต่ว่าไม่สามารถใช้สูตรคำนวณใน Excel ได้ แต่ต้องใช้ VBA ช่วยคำนวณให้เท่านั้น

https://drive.google.com/file/d/0Bxpfgh-rqhIWbzZncWJtaDdoeGs/view?usp=sharing&resourcekey=0-YjSZi3NXlc3ZH2q_67GErA 

https://drive.google.com/file/d/0Bxpfgh-rqhIWTFNBV1Z5SlFMVTg/view?usp=sharing&resourcekey=0-2qWpICZOz7u2C5WljtonPA

7. สร้างโปรแกรมโหราศาสตร์ไทยซึ่งคำนวณหาตำแหน่งดาวทุกดวงในสุริยจักรวาลตามหลักวิทยาศาสตร์สมัยใหม่

https://www.excelexperttraining.com/book/index.php/excel-articles-and-videos/excel-expert-application/4zsuriya

Download แฟ้มล่าสุด
https://www.excelexperttraining.com/download/test4zr.xlsb

8. สามารถใช้ VBA แบบ Evaluate ช่วยทำให้เขียนรหัส VBA ได้ง่ายและยืดหยุ่นต่อการแก้ไขเปลี่ยนแปลง ไม่ต้องคอยตามแก้ไขรหัสใหม่ให้ยุ่งยาก

https://drive.google.com/file/d/0Bxpfgh-rqhIWY2VoM1dQSjV4bGc/view?usp=sharing&resourcekey=0-Bzd5vpGazAevUg5NtzjlIw

9. นำเสนอวิธีใช้ Excel ช่วยในการแสดงรายงานได้อย่างยืดหยุ่นและประทับใจกว่าการใช้ Pivot Table

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

ลิงก์รวบรวมผลงานและเกียรติประวัติ
https://www.excelexperttraining.com/online/hall-of-fame-2/

คลิปอบรม Excel ให้กับ SCG

 

 

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

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

ฉลาดใช้เคล็ดลับของ Excel อย่างมืออาชีพ
ตอนที่ 1 : https://vimeo.com/489201230
ตอนที่ 2 : https://vimeo.com/489202269
ตอนที่ 3 : https://vimeo.com/489203374
ตอนที่ 4 : https://vimeo.com/489204495

การจัดการข้อมูลด้วย Excel
ตอนที่ 1 : https://vimeo.com/489228487
ตอนที่ 2 : https://vimeo.com/489231400
ตอนที่ 3 : https://vimeo.com/489231765
ตอนที่ 4 : https://vimeo.com/489232783

เครื่องมือที่ขาดไม่ได้ของคนรัก Excel
ตอนที่ 1 : https://vimeo.com/489238888
ตอนที่ 2 : https://vimeo.com/489239122
ตอนที่ 3 : https://vimeo.com/489242824
ตอนที่ 4 : https://vimeo.com/489239265

ฉลาดใช้ Excel ในงานประจำวันให้เสร็จเร็วขึ้น
ตอนที่ 1 : https://vimeo.com/489239810
ตอนที่ 2 : https://vimeo.com/489240438
ตอนที่ 3 : https://vimeo.com/489240146
ตอนที่ 4 : https://vimeo.com/489239986

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

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

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

ถ้าจะจัดอบรมแบบปกติในห้องที่รับได้ครั้งละ 20 คน กว่าจะให้ความรู้ครบทุกคนแบบนี้ต้องใช้เวลาสอนกันหลายปี ช่วยประหยัดเวลาและค่าอบรมได้เยอะทีเดียว

Download แฟ้มที่ใช้อบรม SCG (เท่าที่หาพบครับ)
https://drive.google.com/file/d/1a0M-FVjFrnRxQPcvNN6N9-Qqjb3Wjk2Y/view?usp=sharing

 

 

  

แจกแฟ้มตัวอย่างสูตร Min Max สำหรับคำนวณภาษี

.
C4 ใช้กรอกยอดเงินได้สุทธิ หลังหักค่าใช้จ่ายทั้งหมดแล้ว
.
C7 =MIN( 150000, MAX( 0, $C$4) )
คำนวณหาเงินได้ขั้นแรก สูตร Min ช่วยคิดยอดหักไม่เกิน 150000
ส่วน MAX( 0, $C$4) ช่วยกันไม่ให้ใช้ยอดติดลบ
.
C8 =MIN(150000, MAX(0,$C$4)-SUM($C$7:C7))
สูตร Min ช่วยคิดยอดหักไม่เกิน 150000
MAX(0,$C$4)-SUM($C$7:C7) หายอดงินได้ที่คงเหลือจากที่หักไปแล้ว
.
SUM($C$7:C7) หายอดรวมสะสมว่าขั้นก่อนๆหักไปแล้วเท่าไร
.
จากนั้นให้ copy สูตรนี้ลงไป แล้วแก้ตัวเลขด้านหน้าสุดให้เท่ากับช่วงเงินได้ในแต่ละขั้น
.
Download ตัวอย่างได้จาก
https://drive.google.com/file/d/11INJfjYVvtBDlzUVe9cK9At9eacmlc4u/view?usp=sharing

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

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

เชิญติดตามดูโครงการ MECC : Microsoft Excel Collegiate Challenge ได้จากลิงก์

https://mecc.college/
https://mecc.college/excel-outside-the-classroom
https://www.youtube.com/@MECC.college
https://eller.arizona.edu/news/2022/12/2022-microsoft-excel-collegiate-challenge

 

ผมมีโอกาสได้รับเชิญให้ไปสอนนิสิตจุฬาเมื่อต้นเดือนเมษาที่ผ่านมา มีหัวข้อตามภาพ

 

 

ปกติสูตร XLookup ใช้กับเงื่อนไขเดียวได้เท่านั้น ถ้ามีหลายเงื่อนไขล่ะ

.
ปกติสูตร XLookup ใช้กับเงื่อนไขเดียวได้เท่านั้น ถ้ามีหลายเงื่อนไขล่ะ 

เคยเจอสูตร XLookup ที่ใช้หาเลข 1 แบบนี้ไหม
=XLookup( 1, จับ Range เงื่อนไขมาคูณกัน, Range คำตอบ )
.
เช่น ต้องการหาค่าจากรายการที่ column แรกมีค่าเท่ากับเลข 123 และอีก column มีค่าเท่ากับ 999
.
ให้นำเงื่อนไขมาคูณกัน ถ้าตรงไหนที่คูณกันแล้วเท่ากับ 1 นั่นแหละคือตำแหน่งของค่าที่ต้องการ
.
=XLookup( 1, (B2:B5=123) * (C2:C5=999), D2:D5)
.
ทำไม จึงต้องคูณกัน
เพราะ TRUE*TRUE = 1
.
ก่อนอื่นต้องเข้าใจว่า
TRUE ไม่ได้มีค่าเท่ากับ 1 จนกว่าจะนำไปบวกลบคูณหารต่อ
FALSE ไม่ได้มีค่าเท่ากับ 0 จนกว่าจะนำไปบวกลบคูณหารต่อ
.

 
จากภาพตัวอย่างนี้ เซลล์ B2 มีค่า =TRUE() จากนั้นเรามาทดสอบค่าของ B2 กัน จะพบว่าในเซลล์ C2 เมื่อนำมาเทียบค่ากันโดยตรง ซึ่งมีสูตร =B2=1 บอกเราว่า False แสดงว่า True ไม่ได้เท่ากับ 1 แต่เมื่อนำค่าจาก B2 ไปคำนวณต่อในเซลล์ C3:C8 ไม่ว่าจะนำ B2 ไป *1, /1, +0, -0, หรือใส่เครื่องหมายลบลบไว้ข้างหน้า หรือนำไปคูณกับ TRUE() จะกระตุ้นให้แสดงค่าเท่ากับเลข 1 ออกมาให้เห็น
.
ทำนองเดียวกัน เซลล์ G2 มีค่า =FALSE() จากนั้นเรามาทดสอบค่าของ G2 กัน จะพบว่าในเซลล์ H2 เมื่อนำมาเทียบค่ากันโดยตรง ซึ่งมีสูตร =G2=0 บอกเราว่า False แสดงว่า False ไม่ได้เท่ากับ 0 แต่เมื่อนำค่าจาก G2 ไปคำนวณต่อในเซลล์ H3:H8 ไม่ว่าจะนำ G2 ไป *1, /1, +0, -0, หรือใส่เครื่องหมายลบลบไว้ข้างหน้า หรือนำไปคูณกับ TRUE() จะกระตุ้นให้แสดงค่าเท่ากับเลข 0 ออกมาให้เห็น
.
ถ้ารายการที่ 3 ในตาราง B2:B5 = 123 ตรงนั้นจะถือว่าเท่ากับ TRUE
ถ้ารายการที่ 3 ในตาราง C2:C5 = 999 ตรงนั้นจะถือว่าเท่ากับ TRUE
.
TRUE * FALSE = 0
FALSE * FALSE = 0
TRUE * TRUE = 1
FALSE * TRUE = 0
.
พอเอา Range D2:D5 เข้ามาเทียบ สูตร XLookup ก็จะหาตำแหน่งรายการที่ 3 ออกมาเป็นคำตอบที่ต้องการ
.
Download ตัวอย่างได้จาก
https://drive.google.com/file/d/1iYg4I8UEutSFUWSGdIXxURPFxp-ihcru/view?usp=sharing
.
หลักการเดียวกันนี่แหละที่นำไปใช้กับสูตรที่ทำงานแบบ Dynamic Array

ถ้าอยากเก่งสูตร Array ต้องสร้างไปแกะไป โดยแยกแต่ละส่วนของสูตรออกมาสร้างลงไปในเซลล์ข้างนอก ตามภาพนี้จะเห็นวิธีคิดของ Excel

  

90/10 Rule

จงสร้างงานโดยใช้เมนูช่วยเพียง 10% เท่านั้น อีก 90% ให้สร้างตารางโดยใช้สูตร

 

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

 

สาเหตุสำคัญมาจากสูตรจะคำนวณหาคำตอบใหม่มาให้โดยอัตโนมัติ ส่วนเมนูจะทำใหม่ให้ต่อเมื่อคนต้องไปสั่งใหม่เองเสมอ

ยิ่งตอนนี้ใน Excel 365 มีสูตรใหม่ๆเกิดขึ้นเยอะแยะเพื่อมาใช้แทนเมนู เช่น สูตร GroupBy, PivotBy, Unique, Filter, Sort สามารถทำงานแทน Pivot Table หรือคำสั่งบนเมนู Data แนวโน้มนี้ยิ่งเห็นชัดเจนมากขึ้น

สูตร VLookup หน้าเก่า ต้องปรับวิธีใช้แบบใหม่ด้วย เพื่อใช้กับ Excel 365

 

ไม่ใช่แค่สูตร VLookup เท่านั้น สูตรเก่าที่เคยใช้ทุกสูตร เมื่อนำมาใช้กับ Excel 365 ที่ทำงานแบบ Dynamic Array จะมีวิธีการสร้างสูตรแบบใหม่ตามนี้

1. ไม่ต้องใส่เครื่องหมาย $ อีกต่อไป

2. ไม่ต้องสร้างสูตรหนึ่งเพื่อหาค่าหนึ่งอีกต่อไป

3. ไม่ต้องเสียเวลา Copy สูตรไปใช้หาค่าอื่นอีกต่อไป

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

วิธีสร้างสูตรแบบเดิม

=VLookup( ค่าที่ใช้หา, พื้นที่ตารางฐานข้อมูล, เลขที่ column ของคำตอบ, 0 )

เปลี่ยนตรงส่วนของค่าที่ใช้หาเป็น

=VLookup( พื้นที่ตารางเก็บค่าที่ใช้หา, พื้นที่ตารางฐานข้อมูล, เลขที่ column ของคำตอบ, 0 )

ตามภาพนี้ เมื่อต้องการใช้ทุกค่าในตาราง F3:F4 ว่ามีชื่ออะไรกับตัวเลขเป็นเท่าไร

เซลล์ G3 สร้างสูตร =VLOOKUP( F3:F4, B3:D7, 2, 0 )

เซลล์ H3 สร้างสูตร =SUMIF( B3:B7, F3:F4, D3:D7 )

ลองแกะตัวอย่างนี้ดู จะพบว่าสูตร Match กับ Index ทำงานแบบ Dynamic Array เช่นกัน

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

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

 

 

 

Dynamic Array เป็นกุญแจดอกสำคัญ ที่จะไขสู่ความมหัศจรรย์ของ Excel 365

Dashboard ที่สร้างร่วมกับ Dynamic Array จะกลายเป็น Dynamic Dashboard ที่ปรับการแสดงผลได้ยืดหยุ่นยื่งขึ้น สามารถใช้พื้นที่ตารางเดียวที่จำกัดพื้นที่บนหน้าจอให้เปลี่ยนไปแสดงผลแบบอื่น

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

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

Dynamic array จะช่วยทำให้ไม่ต้องเลือกพื้นที่ทั้ง column เช่น =Sum(A:A) โดยเปลี่ยนไปให้อ้างอิงกับเซลล์แรกของพื้นที่ที่เป็น Dynamic Array =Sum(A1#) แทน Excel จะหายอดรวมจากเซลล์ A1 ถึงเซลล์สุดท้ายให้ทันที

Array คือ ข้อมูลที่มีค่าตั้งแต่ 2 ค่าขึ้นไป หรือพื้นที่ตารางตั้งแต่ 2 เซลล์ขึ้นไป ซึ่งใน Excel รุ่นก่อน 365/2021 หากอยากนำมาใช้งานต้องเสียเวลาเลือกพื้นที่ให้มีขนาดพอดีแล้วกดปุ่ม Ctrl + Shift + Enter เพื่อสร้างวงเล็บปีกกาปิดหัวท้ายสูตรก่อนเสมอ ส่วนใน Excel รุ่นใหม่สามารถใช้งานได้ทันทีโดยไม่ต้องกด 3 ปุ่มนี้พร้อมกันอีกต่อไป แค่สร้างสูตรลงไปในเซลล์เดียว พอกดปุ่ม Enter จะพบว่า Excel สร้างสูตรกระจายค่าทั้งหมดลงไปในพื้นที่ตารางหลายเซลล์ให้เอง   

ถ้าพื้นที่ไม่ว่างพอ ไม่สามารถกระจายค่าลงไปให้ครบก็จะแสดงคำเตือนว่า #SPILL!

Dynamic array จะช่วยทำให้ไม่ต้องเลือกพื้นที่ทั้ง column เช่น =Sum(A:A) โดยเปลี่ยนไปให้อ้างอิงกับเซลล์แรกของพื้นที่ที่เป็น Dynamic Array =Sum(A1#) แทน Excel จะหายอดรวมจากเซลล์ A1 ถึงเซลล์สุดท้ายให้ทันที

แต่ถ้าอยากดึงค่าเพียงค่าเดียวในพื้นที่ Dynamic Array ให้ใส่ @ นำหน้าตำแหน่งเซลล์ จะได้ค่าตรงตำแหน่ง row เดียวกันออกมาใช้ 

นอกจาก Dynamic Array ที่ Excel จัดการให้แล้ว ต้องเรียนรู้วิธีการคำนวณแบบ Array ด้วยจะทำให้สามารถพลิกแพลงสูตรรุ่นใหม่ได้ตามใจ

เปิดดูทีละหน้า-วิธีแก้ข้อมูลล้นแล้วเจอ Error Spill

#SPILL! เป็น Error ที่พบบ่อยมากใน Excel 365 หรือรุ่นตั้งแต่ 2021 เป็นต้นมา ที่เริ่มต้นความสามารถพิเศษที่เรียกว่า Dynamic Array โดยสาเหตุหนึ่งที่คำเตือนนี้จะเกิดขึ้น คือ ต่อเมื่อสูตรที่สร้างขึ้นนั้นไม่สามารถกระจายค่าที่หาได้ออกไปจนครบทั้งตาราง

Spill แปลว่า ล้น หรือ หก เช่น เวลาเทน้ำจนเต็มแก้วแล้วล้น หรือมีอะไรมาขวางทางน้ำที่ไหล น้ำก็จะล้น

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

=IF( CountA(Range) = 0, สูตรที่ต้องการใช้, 0)

ถ้านับแล้วพบว่าในพื้นที่นั้นสูตร CountA = 0  แสดงว่าไม่มีอะไรมาขวางก็ให้ใช้สูตรนั้นได้ แต่ถ้าไม่ใช่ให้แสดงค่า 0 แทน

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

=CHOOSEROWS( MyData, SEQUENCE( Transactions, 1, (Transactions*(PageNum-1))+1) )

พอกรอกเลขหน้า PageNum ที่อยากจะดูลงไปในเซลล์สีส้ม K2 สูตรนี้จะเลือกแสดงข้อมูลออกมาให้เห็นทีละหน้า

MyData เป็นพื้นที่ข้อมูลทั้งหมด ซึ่งมีจำนวน 380 รายการ หาได้จากสูตร Rows(MyData) ในเซลล์ C2

SEQUENCE( Transactions, 1, (Transactions*(PageNum-1))+1) ) ทำหน้าที่ดึงข้อมูลแต่ละหน้า โดย

เซลล์สีเขียว F2 ตั้งชื่อว่า Transactions เป็นจำนวนรายการต่อหน้า ตอนนี้สมมติว่าอยากให้แสดงทีละ 20 รายการต่อหน้า

(Transactions*(PageNum-1))+1) เป็นสูตรหาเลขที่ลำดับรายการเริ่มต้นของแต่ละหน้า

หน้าที่ 1 หาเลขที่รายการเริ่มต้นมาจาก 20*(1-1)+1 ก็จะเริ่มต้นที่รายการที่ 1 

หน้าที่ 2 หาเลขที่รายการเริ่มต้นมาจาก 20*(2-1)+1 ก็จะเริ่มต้นที่รายการที่ 21

หน้าที่ 3 หาเลขที่รายการเริ่มต้นมาจาก 20*(3-1)+1 ก็จะเริ่มต้นที่รายการที่ 41

หน้าที่ 9 หาเลขที่รายการเริ่มต้นมาจาก 20*(9-1)+1 ก็จะเริ่มต้นที่รายการที่ 161

หน้าที่ 15 หาเลขที่รายการเริ่มต้นมาจาก 20*(15-1)+1 ก็จะเริ่มต้นที่รายการที่ 281 

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

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

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

 

 

สูตร GroupBy ให้ทางเลือกในการ "มองหาของคน" ที่เหนือกว่า PivotTable

ถ้าเป็นแฟนพันธุ์แท้ของ PivotTable พอใช้คำสั่ง Filter เลือกให้แสดงรายการไหนก็จะเหลือแต่รายการนั้นๆ รายการอื่นที่ไม่ใช่จะหายไปใช่ไหม

สูตร GroupBy มีทางเลือกที่เหนือกว่า PivotTable 

1. อยากให้แสดงเฉพาะรายการนั้นโดยตัดรายการอื่นที่ไม่ใช่ทิ้งไป แบบ PivotTable ตามภาพด้านหลัง

หรือ

2. อยากให้ตำแหน่งรายการนั้นยังคงอยู่ที่เดิม โดยรายการอื่นแสดงค่าเท่ากับ 0 ตามภาพด้านหน้า

 

วิธีการใช้สูตร GroupBy แต่ละแบบ ต่างกันตรงวิธีนำเงื่อนไขไปใส่ลงไปในวงเล็บว่าจะใส่ไว้ในส่วนไหน ซึ่งตามตัวอย่างนี้ใช้เงื่อนไข ((COUNTIF(ITEMChoice,ITEM)>=1)+(COUNTA(ITEMChoice)=0)) ที่ทำหน้าที่ควบคุมการแสดงรายการตามที่กรอกไว้ในตาราง ItemChoice

แบบแรก ให้นำสูตรที่ใช้เป็นเงื่อนไข ใส่ลงไปในวงเล็บหลังคอมมา 4 ตัว ซึ่งเป็นส่วนที่เรียกว่า filter-array

=GROUPBY( CHOOSECOLS(B3:E27,K1), F3:G27, SUM,,,,((COUNTIF(ITEMChoice,ITEM)>=1)+(COUNTA(ITEMChoice)=0))) 

แบบที่สอง นำส่วนของเงื่อนไขไปคูณกับ Range ตัวเลข

=GROUPBY( CHOOSECOLS(B3:E27,K1), F3:G27
*((COUNTIF(ITEMChoice,ITEM)>=1)+(COUNTA(ITEMChoice)=0)),SUM)

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

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

คุณคิดว่าหลักการสร้างรายงานที่ดี ควรเลือกใช้แบบไหนดีกว่ากัน 

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

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

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

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

https://excelexpertlibrary.blogspot.com/2025/04/pivottable-groupby-excel-365.html

https://excelexpertlibrary.blogspot.com/2025/04/groupbycountif-pivottable.html

https://excelexpertlibrary.blogspot.com/2025/04/pivottable-filter-365-groupby-data.html  

 

เลิกใช้ PivotTable คู่กับ Filter ไปใช้สูตรใน 365 GroupBy คู่กับ Data Validation

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

ปรับสูตรนิดหน่อย จากเดิม
=GROUPBY(CHOOSECOLS(B3:E27,K1),F3:G27*(COUNTIF(ITEMChoice,ITEM)>=1),SUM)

เพิ่มสูตรนับต่อท้ายเข้าไปเป็น
=GROUPBY(CHOOSECOLS(B3:E27,K1),F3:G27*((COUNTIF(ITEMChoice,ITEM)>=1)
+(COUNTA(ITEMChoice)=0)),SUM)

☝️ ถ้าไม่ได้เลือกแสดง Item อะไรเลย สูตร COUNTA(ITEMChoice) จะนับว่า =0 ซึ่งพอสูตร COUNTA(ITEMChoice)=0 นี้เป็นจริงขึ้นมา จะคืนค่า TRUE ออกมา ทำให้เมื่อนำไปคูณกับ F3:G27 ก็จะเลือกตัวเลขทั้งหมดมาใช้คำนวณหายอดรวม

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

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

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

https://excelexpertlibrary.blogspot.com/2025/04/pivottable-groupby-excel-365.html

https://excelexpertlibrary.blogspot.com/2025/04/groupbycountif-pivottable.html

https://excelexpertlibrary.blogspot.com/2025/04/pivottable-filter-365-groupby-data.html

GroupBy+CountIF ไม่ใช่แค่ใช้แทน PivotTable แต่ทำได้เหนือกว่า

PivotTable ว่ากันตรงๆเป็นของใช้กับรายงานสำหรับเด็กๆ ไม่เหมาะสำหรับให้ผู้ใหญ่หรือผู้บริหารนักหรอก

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

 

หลักการใช้ Excel สร้างรายงานสำหรับผู้บริหาร ต้องยึดหลัก Management by Exception นั่นคือ ตั้งแต่แรกต้องทำให้แสดงเฉพาะข้อมูลที่สำคัญหรืออยากจะแสดงเท่านั้น ไม่ใช่ว่าต้องมาเสียเวลาตัดทิ้งทีหลัง

ตัวอย่างตามภาพนี้ ในกรอบสี่เหลี่ยมสีเขียว ใช้คำสั่ง Data > Validation สำหรับให้ผู้บริหารคลิกเลือกเฉพาะ ItemChoice ที่อยากจะแสดง จากนั้นตารางฐานข้อมูลด้านซ้ายกับตารางหายอดรวมแยกประเภทด้านขวาสุดก็จะเปลี่ยนสีพื้นแสดงรายการที่ตรงกับ ItemChoice ที่เลือกให้เห็นชัดเจนว่าอยู่ตรงไหน พร้อมหายอดรวมที่เกี่ยวข้องให้เท่านั้นด้วย รายการอื่นจะกลายเป็น 0 ไปเลย

สูตรที่ทำหน้าที่แบบนี้ได้มาจากการซ้อนสูตร CountIF เข้าไป

=GROUPBY( CHOOSECOLS( B3:E27,K1 ), F3:G27*(COUNTIF(ITEMChoice,ITEM)>=1), SUM ) 

CHOOSECOLS( B3:E27,K1 ) ทำหน้าที่เลือกแสดงตามหัวตารางที่เลือกได้ในช่องสีเหลือง

F3:G27 เป็นพื้นที่ตารางตัวเลข Budget กับ Actual

(COUNTIF(ITEMChoice,ITEM)>=1) เป็นสูตรสำคัญที่ช่วยเลือกเฉพาะรายการที่ตรงกับที่เลือกใน ItemChoice ซึ่งสูตรนี้แหละที่ทำให้เหนือกว่าการใช้ PivotTable

ถ้าลากทับส่วนของสูตร (COUNTIF(ITEMChoice,ITEM)>=1) แล้วกดปุ่ม F9

จะพบว่าสูตรนี้ทำหน้าที่ตรวจสอบหาตำแหน่งรายการว่าตรงไหนที่ตรงกับ ItemChoice บ้าง

{FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}

ตำแหน่งที่เป็น TRUE เป็น Item ที่ตรงกับที่เลือก พอนำไปคูณกับ F3:G27 จะเก็บตัวเลขเฉพาะตำแหน่งที่เท่ากับ TRUE ส่วนที่เป็น FALSE จะไม่นำไปใช้หายอดรวม

สาเหตุที่ใช้สมการ >=1 นั่นเพื่อให้ไม่ว่าจะคลิกช่องสีเขียวแล้วเผลอเลือก Item ซ้ำก็จะไม่ทำให้คำนวณผิดพลาด ลองดูแล้วจะเห็นเองครับ

Download ได้จาก

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

ตัวอย่างนี้ต่อเนื่องมาจากตัวอย่างก่อนหน้าที่ไม่ซับซ้อนเท่าไร เชิญติดตามดูได้จาก Excel Expert Blog ที่

https://excelexpertlibrary.blogspot.com/

PivotTable หมดยุค เมื่อใช้สูตร GroupBy ของ Excel 365

 

"ต้องหาทางทำตารางเดียวชีทเดียวเปลี่ยนเป็นอะไรก็ได้"
นี่เป็นหลักการใช้ Excel สร้างรายงานที่ผมแนะนำมาตลอด 

จากภาพนี้พอคลิกเปลี่ยนชื่อหัวตารางในเซลล์ I2 ที่ใส่สีเหลืองจากคำว่า CODE > DEPARTMENT > CATEGORY > ITEM จะสร้างรายงานสรุปยอดตัวเลข BUDGET กับ ACTUAL ออกมาให้ทันที ... ตรงตารางตำแหน่งเดียวกัน

เซลล์ I3 ใช้สูตร =GROUPBY( CHOOSECOLS(B3:E27, I1), F3:G27, SUM)

เซลล์ I1 ใช้สูตร =MATCH(I2, B2:E2, 0) ทำหน้าที่หาว่าชื่อหัวตารางในเซลล์ I2 ที่คลิกเปลี่ยนได้นั้นมาจาก Column ที่เท่าไร เช่น 

CODE เป็น Column ที่ 1
DEPARTMENT เป็น Column ที่ 2
CATEGORY เป็น Column ที่ 3
ITEM เป็น Column ที่ 4

พอเซลล์ I1 หาเลขที่ Column ได้แล้ว สูตร CHOOSECOLS( B3:E27, I1) จะเลือกพื้นที่ Column ตามออกมาใช้ในสูตร GroupBy 

=GROUPBY( CHOOSECOLS(B3:E27, I1), F3:G27, SUM)

มีโครงสร้างภายในวงเล็บตามนี้

=GROUPBY( พื้นที่ column ที่ต้องการจัดกลุ่ม, พื้นที่ column ที่เก็บตัวเลข, หายอดโดยใช้สูตร SUM) 

นอกจากสูตร GroupBy ใช้แทน PivotTable ได้แล้วยังทำหน้าที่แทนสูตร Filter, Unique, SumIF, CountIF ได้อีกด้วย หากเปลี่ยนคำว่า SUM ด้านท้ายเป็นสูตรอื่นได้อีกสิบกว่าสูตรทีเดียว

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

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

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

นอกจาก GroupBy แล้วยังมีสูตร PivotBy ที่ทำหน้าที่เหมือน PivotTable ที่ใช้ 2 แกนทั้ง Row Field กับ Column Field ได้อีกด้วย 

 

หมอดู คู่หมอเดา หมอมีเชาว์ เดาน่าดู

เวลาไปหาหมอดู จะถูกถามว่าเกิดวันไหน เวลาอะไร เกิดที่ไหน เพื่อนำไปผูกดวงหาลัคนาและตำแหน่งดาว จะผิดหรือถูก เริ่มต้นนี่แหละสำคัญมาก

แค่วันที่เกิดนี่แหละ ถ้าถือตามตำราไทยเดิมจะมองว่าตราบใดที่ยังไม่ถึงหกโมงเช้า จะเป็นวันเดิม ไม่เปลี่ยนขึ้นเป็นวันใหม่ ต้องซักถามกันให้ชัดว่าที่เจ้าตัวบอกมานั้นพอมองแบบสากลที่ถือว่าเริ่มต้นวันใหม่ตั้งแต่เวลา 0:00:00 - 23:59:59 น่ะจะเป็นวันไหนกันแน่

เวลาตกฟาก คือ ตอนที่ออกมาท้องคุณแม่แล้วร้องอุแว้แรก ถือว่าธาตุครบทั้งดินน้ำไฟลมครบถ้วนเป็นเวลาไหน ถ้าไม่รู้จะทำนายให้ถูกยากมาก ทุก 4 นาที ลัคนาจะเปลี่ยนไป 1 องศา

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

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

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

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

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

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

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

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

ค่าอัยนางศ์ มีวิธีคิดหลายแบบว่าจะใช้ตามแบบไหน จะใช้ค่าคงที่ตามนาย Lahiri หรือตามนาย Fagan Bradley คิดมาให้ หรือจะใช้สูตรคำนวณที่คิดค้นกันขึ้นมาก็ยังได้อีก

จากเกิดวันไหน เวลาอะไร เกิดที่ไหน ยังมีตัวแปรอีกหลายอย่างที่ใช้ประกอบในการคำนวณ ตำแหน่งลัคนา ตำแหน่งดาว หรือแม้แต่ราศี อาจแปรเปลี่ยนต่างไปก็ได้ ยิ่งการทำนายที่ใช้ราศีหรือภพแบ่งเป็นช่องละ 30 องศา แค่ได้ตำแหน่งที่คร่อมแกนของราศีหรือขยับข้ามแกนไปนิดๆไม่กี่องศา คำทำนายจะกลายเป็นอีกอย่างไปแล้ว

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

วิธีทำให้กราฟยืดได้หดได้แบบ Moveable Dynamic Range ด้วยสูตรใหม่ใน Excel 365

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

พอเปลี่ยนมาใช้ Excel 365 ที่เซลล์ F7 ให้ใช้สูตรตามนี้แทน

=CHOOSEROWS( ChartData, SEQUENCE(5,1,Start) )

โดย SEQUENCE(5,1,Start) ทำหน้าที่สร้างเลขลำดับทีละ 5 ตัว อยากให้เลขเริ่มจากเลขอะไรก็กำหนดได้ตาม Start

ถ้า Start = 1 จะได้เลขลำดับ 1; 2; 3; 4; 5
ถ้า Start = 2 จะได้เลขลำดับ 2; 3; 4; 5; 6
ถ้า Start = 3 จะได้เลขลำดับ 3; 4; 5; 6; 7

พอนำสูตร Sequence ไปซ้อนในสูตร ChooseRows จะช่วยเลือกรายการจากตารางเก็บข้อมูล ChartData ด้านซ้ายสุดได้รายการตามเลขลำดับที่ต้องการออกมาให้ แสดงไว้ในพื้นที่ตารางตรงกลางที่เตรียมไว้เป็น Chart Template สำหรับนำไปสร้างกราฟด้านขวาต่ออีกที

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

แทนที่จะดึงข้อมูลออกมาพร้อมกันทุก Column ใน Chart Data ถ้าอยากจะดึงข้อมูลออกมาทีละ Column จะใช้สูตร ChooseCols ซ้อนเข้าไปอีกชั้นก็ได้ แต่เพื่อทำให้เข้าใจได้ง่ายขึ้นก็เพียงเปลี่ยนสูตรมาเป็น

=CHOOSEROWS( Year, SEQUENCE(Interval,1,Begin) ) เพื่อดึงช่วงปีที่ต้องการ
=CHOOSEROWS( Income, SEQUENCE(Interval,1,Begin) ) เพื่อดึงรายการ Income ช่วงเดียวกันกับปีที่ต้องการ

ตามภาพที่ 2 สูตรนี้ทำหน้าที่เหนือกว่าสูตรข้างต้น โดยใช้เลข Interval ช่วยในการกำหนดขนาดจำนวนรายการได้ด้วย 

ถ้า Interval = 6 และ Begin = 1 จะได้เลขลำดับ 1; 2; 3; 4; 5; 6
ถ้า Interval = 7 และ Begin = 1 จะได้เลขลำดับ 1; 2; 3; 4; 5; 6; 7
ถ้า Interval = 7 และ Begin = 2 จะได้เลขลำดับ 2; 3; 4; 5; 6; 7; 8

นำสูตรไปตั้งเป็น Formula Name ในชื่อ XAxis กับ YAxis แล้วนำชื่อไปเปลี่ยนสูตร Series บนเส้นกราฟ

จากสูตรเดิมที่ใช้ตำแหน่งอ้างอิง $B$5:$B$16 กับ $C$5:$C$16 ตามสูตรนี้

=SERIES( Dynamic!$C$4, Dynamic!$B$5:$B$16, Dynamic!$C$5:$C$16, 1)

ให้พิมพ์ชื่อที่ตั้งไว้ XAxis ทับ $B$5:$B$16 และพิมพ์ YAxis ทับ $C$5:$C$16 แล้วกดปุ่ม Enter สูตรจะนำชื่อแฟ้มนำหน้าชื่อสูตรมาใส่ให้เอง สูตร Series จะเปลี่ยนเป็น

=SERIES( Dynamic!$C$4, DynamicChart365.xlsb!XAxis, DynamicChart365.xlsb!YAxis, 1)

พอคลิกเปลี่ยนเลขในช่อง Interval กับ Begin เส้นกราฟก็จะยืดได้หดได้และเลื่อนปีที่เริ่มได้ตามสบาย โดยไม่ต้องพึ่งตาราง Template อีกต่อไป

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

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

+++++++++++

สำหรับ Excel รุ่นเก่าก่อน 365 เชิญดูวิธีการได้ที่หลักสูตรเคล็ดการสร้างกราฟให้เห็นปุ๊บ เข้าใจปั้บ บทเรียนที่ 26 ใช้สูตร Offset

เชิญสมัครเรียนออนไลน์ ฟรี 1 ปีได้ที่เว็บ XLSiam.com

 

 

 

 

 

  

 

Ready to Work อบรมให้กับนิสิตจุฬา คณะวิทยาศาสตร์ ภาควิชา Biochemistry

เรียน 2 วันๆละ 3 ชั่วโมง จะได้เรียนกันเบาๆครับ

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

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

ขอบคุณมากที่ให้โอกาสได้รับเชิญมาสอนคลาสนี้ เป็นความฝันที่อยากทำมานานครับ

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

ระยะเวลาเรียนวันละ 3 ชั่วโมงก็สำคัญมาก ถ้าให้เรียนเต็มวัน 6 ชมก็ไม่เหมาะเพราะไม่คุ้นเคยกัน คิดถึงสมัยตัวเองเป็นนักเรียนแล้วเรียนไม่ถึงชมก็หลับแล้ว 

 

  

เดี๋ยวนี้มีที่ให้เรียนฟรีเยอะแยะ ดูคลิปในยูทูปหรือค้นหาจากกูกเกิลก็ได้ ทำไมยังอยากมาเรียนกับผมครับ

 

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

 

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

 

จะเลือกเรียนกับใคร ก็เหมือนกับการเลือกหมอ หมอที่ดูว่าเก่งสารพัดก็สู้หมอที่เก่งเฉพาะทางไม่ได้ หมอคิวทองที่มีคนไข้เยอะมาก ต้องดูด้วยว่าเยอะเพราะโฆษณาเก่ง หรือผ่านประสบการณ์มามากน้อยแค่ไหน ถ้าเลือกหมอตี๋หน้าใหม่ราคาถูก คุณภาพที่ได้รับล่ะเป็นยังไง

 

เผื่ออยากจะอ่านที่ผมเขียนครับ ผม zip เอาไว้ แฟ้มขนาด 31 MB

 

Download ได้จาก https://drive.google.com/file/d/1PtLgpR-TF-RBWB0hd7lxx21TYcsuVZLx/view?usp=sharing

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

 

แฟ้มตัวอย่าง ฟรี เพิ่มเติม
https://www.excelexperttraining.com/online/courses/03-free-free-free/

 

วิธีเปลี่ยนสูตร Excel ที่ยากๆยาวๆ ให้เป็นชื่อสูตรสั้นๆที่ฉันรู้จักคนเดียวว่าเบื้องหลังเป็นยังไง

จากภาพนี้ในเซลล์ G3 ใช้สูตร =MyLookup ทำหน้าที่ค้นหาว่ารหัส a002 มีชื่อ b กับ จำนวน 20 ออกมาให้

MyLookup นี้เป็นชื่อที่ตั้งให้กับสูตรที่ใช้อยู่เดิม วิธีนี้เรียกว่าการตั้งชื่อ Formula Name ซึ่งจะเกิดประโยชน์ ดังนี้

1. จะตั้งชื่อให้เป็นอะไรก็ได้ตามใจ ช่วยสื่อความหมายว่าสูตรนั้นทำหน้าที่อะไร เหมาะอย่างยิ่งในการใช้งานร่วมกัน

2. ช่วยย่อสูตรยากๆยาวๆให้เหลือสั้นลง เวลาจะใช้สูตรแค่กดปุ่ม F3 ดึงชื่อมาสูตรมาใช้หรือจะพิมพ์เองก็แค่พิมพ์ =ชื่อที่ตั้งไว้

3. เมื่อต้องการแก้ไข ไม่จำเป็นต้องค้นหาว่าเคยสร้างสูตรไว้ที่เซลล์ไหนบ้าง แค่เข้าไปแก้ได้โดยตรงที่เมนู Formulas > Name Manager > Edit แล้วแก้สูตรในช่อง Refers to จะส่งผลให้ทุกเซลล์ที่ใช้สูตรชื่อนั้นปรับการคำนวณใหม่ให้ทันที

วิธีการตั้งชื่อสูตร 

ให้ใช้เมนู Formulas > Define Name แล้วตั้งชื่อสูตรลงไปในช่อง Name กับใส่สูตรที่ต้องการลงไปในช่อง Refers to:

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

ในช่อง Refer to: อย่าไปสร้างสูตรลงไปในช่องนี้โดยตรง เพราะยากที่จะตรวจสอบว่าสูตรทำงานได้ตามที่ต้องการหรือไม่ แต่ให้สร้างสูตรที่ต้องการลงไปในเซลล์ที่ไหนก็ได้โดยใส่ $ ให้ครบทั้งหน้า row และ column ทดสอบสูตรว่าทำงานได้เรียบร้อยก่อน จากนั้นจึงลอกสูตรไปวางลงไปในช่อง Refers to 

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

=XLOOKUP( $F$3, $B$3:$B$7, $C$3:$D$7 )

พอลอกสูตรไปวางในช่อง Refers to แล้วกดปุ่ม OK สูตรจะเปลี่ยนไปเป็น

=XLOOKUP( Sheet1!$F$3, Sheet1!$B$3:$B$7, Sheet1!$C$3:$D$7 )

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

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

Download แฟ้มตัวอย่างนี้ได้จาก

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

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