อยากแสดงเป็นวันอะไร เดือนอะไร ระหว่าง Format dddd กับสูตร Text(Cell,"dddd") ควรใช้อะไร

เซลล์สีส้ม B3 ตามภาพนี้มีข้อมูล 7/6/2025 ถ้าอยากแสดงว่าเป็นวันอะไร เดือนอะไร เดือนปีอะไร มักเจอคำแนะนำให้ใช้สูตร Text ตามพื้นที่สีเขียว

=Text( $B$3, "dddd" ) จะได้วันเสาร์
=Text( $B$3, "mmmm" ) จะได้เดือนมิถุนายน
=Text( $B$3, "mm-yy" ) จะได้ 06-25

ส่วนพื้นที่สีฟ้า ไม่ได้ใช้สูตรอะไรเลยแจ่ลิงก์ค่าจากเซลล์ B3 มาใช้แล้วกำหนด Custom Format 

Format dddd จะได้วันเสาร์
Format mmmm จะได้เดือนมิถุนายน
Format mm-yy จะได้ 06-25

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

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

แล้วเมื่อไหร่จะใช้สูตร Text ล่ะ !!!

สูตร Text จะช่วยให้เปลี่ยนรูปแบบใหม่ได้โดยไม่ต้องแก้ไขอะไรใหม่ แค่กรอกรูปแบบลงไปในเซลล์สีชมพู B7, B8, B9 จะได้วันเดือนตามมาให้ทันที

แต่ถ้าเลือกใช้ Format ล่ะ พออยากจะเปลี่ยนรูปแบบก็ต้องคลิกลงไปในเซลล์แล้วสั่ง Format Cells > Number > Custom แล้วเสียเวลาไปกำหนดรูปแบบใหม่

นี่คือสิ่งที่เหนือกว่าจากการใช้สูตร Text แต่ก็ได้อย่างก็ต้องเสียอย่างเสมอ 

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

Data Table ช่วยทำให้คุณขี้เกียจทำงานซ้าๆอะไรได้บ้าง

หลักการทำงานของ Data Table เป็นการสั่งให้ Excel ส่งค่าใหม่ที่กรอกไว้เป็น Row Input และหรือ Column Input ไปยังเซลล์ใดก็ได้ในตาราง (Input Cell) จากนั้น Excel จะแอบคำนวณใหม่หาผลลัพธ์ให้โดยที่เรามองไม่เห็นว่ามีการคำนวณเกิดขึ้นเลย ซึ่ง Input Cell นี้ไม่จำเป็นต้องเป็นเซลล์ที่เป็น Value ซึ่งมีค่าที่กรอกไว้ แต่ยังเป็นเซลล์สูตรได้ด้วย

พอเข้าใจหลักการทำงานของ Data Table แล้ว จะพบว่าคำสั่งนี้สามารถใช้ทำงานได้หลายอย่าง 

1. ใช้ทดสอบสูตรที่สร้างขึ้น โดยส่งหลายๆค่าไปใช้กับสูตรนั้นว่าสามารถทำงานได้หรือไม่

2. เมื่อทดสอบสูตรว่าทำงานได้ตามต้องการแล้ว สามารถใช้ Data Table ส่งรหัสใหม่หรือตัวแปรใหม่ไปหาคำตอบอื่นมาให้ โดยไม่จำเป็นต้องสร้างสูตรหรือ copy สูตรมาวางซ้ำอีกเลย 

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

4. ต่อเนื่องจากข้อ 3 จะทำให้ในแฟ้มมีขนาดเล็กลง เพราะไม่ต้องมีตารางที่ใช้คำนวณแบบเดียวกันซ้ำๆหลายตารางหรือหลายชีทอีกเลย อีกทั้งยังทำให้การคำนวณเร็วขึ้นเมื่อใช้ระบบคำนวณแบบ Automatic Except for Data Table หรือแบบ Partial โดย Data Table จะคำนวณใหม่ให้ต่อเมื่อเรากดปุ่ม F9 เท่านั้น ส่วนตารางสูตรอื่นๆยังคงคำนวณใหม่ให้แบบ Automatic

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

6. ผลลัพธ์ที่ Data Table หามาให้หลายๆค่าในรูปแบบของตารางยืดหยุ่นกว่าการใช้ Goal Seek ที่มีข้อจำกัดว่าจะทำงานได้ทีละครั้งและแต่ละครั้งต้องกรอกค่า To Value เอง ดังนั้นในการวางแผนจะสร้าง Data Table เพื่อดูเป้าหมายที่เป็นผลลัพธ์คร่าวๆขึ้นมาก่อน จากนั้นจึงใช้ Goal Seek ซ้ำต่อบนตาราง Data Table เพื่อหาตัวแปรที่ทำให้ได้ค่าตรงกับเป้าหมาย

7. เมื่อใช้สูตร Index หรือ Choose มาทำงานร่วมกับ Data Table จะช่วยทำให้ Data Table ทำงานได้ไม่จำกัดตัวแปร และทำให้ใช้ตาราง Data Table เพียงตารางเดียวหาคำตอบจากสูตรหลายสูตรมาให้

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

 

Cleaning data ต้องใช้สูตรอะไรก่อน พอปัดฝุ่นแล้ว สูตร IF จะได้ทำงานถูกต้อง

ยุคนี้เรื่องการทำความสะอาด Cleaning Data เริ่มมีความสนใจอยากใช้มากขึ้น ส่วนใหญ่มักบอกกันว่าต้องทำความสะอาดข้อมูลก่อนเพื่อช่วยทำให้สูตร VLookup, XLookup หาค่าเจอ เช่น

=Trim(Cell) เพื่อตัดวรรคที่ใส่มาเกิน ไม่ว่าจะวรรคหน้าหลังหรือตรงกลาง ให้เหลือวรรคเดียวระหว่างคำ เช่น abc def

=Left(Cell,3) เพื่อตัดข้อความให้เหลือเพียง 3 ตัว abc นับมาจากด้านซ้าย
=Right(Cell,3) เพื่อตัดข้อความให้เหลือเพียง 3 ตัว def นับมาจากด้านขวา
=Mid(Cell,3,1) เพื่อตัดข้อความให้เหลือเพียง 1 ตัว c นับมาจากด้านซ้ายตัวที่ 3

 

สูตรพวกนี้มักนำมาคลีนข้อมูลที่เป็นตัวอักษร ถ้านำมาใช้กับตัวเลข ต้องระวังว่าผลที่ได้จะกลายเป็น Text เช่น ถ้าในเซลล์ใส่เลข 123 เอาไว้ =Mid(Cell,3,1) แม้จะได้ค่าออกมาเห็นเป็นเลข 3 แต่เลข 3 ที่ได้จะชิดซ้ายออกมาให้รู้ว่ามีสถานะเป็น Text ไปแล้ว 

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

Cleaning Data ที่สำคัญมากๆแต่มักนึกไม่ถึงกัน ถ้าเป็นตัวเลขที่เกิดจากการคำนวณ หากจะนำไปใช้ต่อในการตัดสินใจ ต้องหาทางทำให้ตัวเลขผลลัพธ์ที่ได้มานั้นมีค่าตรงกับเงื่อนไขที่ใช้ก่อน จึงจะนำไปใช้กับสูตร IF, Choose, Match, VLookup, XLookup แล้ว Excel ตัดสินใจถูกต้อง

ตัวอย่างง่ายๆ เช่น ในเซลล์ B2 สร้างสูตร =2.3-2.2 ไว้ เรามักนึกว่าได้คำตอบเป็นเลข 0.1 แต่พอนำค่าไปเทียบด้วยสูตร IF กลับให้คำตอบเป็น No

=IF( B2 = 0.1, "Yes", "No")

สาเหตุที่สูตร IF ไม่คืนค่าออกมาเป็น Yes เพราะแท้จริงแล้ว 2.3-2.2 ได้ค่า 0.0999999999999996 ออกมาแต่เพราะเจอรูปแบบให้แสดงทศนิยม 1 หลักเลยแสดง 0.1 ให้เห็น

ถ้าต้องการนำไปเทียบกับ 0.1 ว่าเท่ากันจริงไหม ต้องปรับสูตรเป็น

=IF( ROUND(2.3-2.2, 1) = 0.1, "Yes", "No") 
หรือ
=IF( ROUND(ฺB2, 1) = 0.1, "Yes", "No") 

จึงจะได้คำตอบ Yes ออกมา

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

Download ตัวอย่าง

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

 

 

 

สูตรยาวๆแบบนี้ มีวิธีสร้างกันยังไง =INDEX(Sheet1!C3:G6,MATCH(B4,Sheet1!B3:B6,0),MATCH(B5,Sheet1!C2:G2,0))

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

=INDEX(Sheet1!C3:G6,MATCH(B4,Sheet1!B3:B6,0),MATCH(B5,Sheet1!C2:G2,0)) 

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

ตัวอย่างนี้สูตรที่ใช้หาค่า 333 คือ Index ซึ่งหาจากตำแหน่งที่ตัดกันระหว่างรหัส a003 กับ D หรือ Row 3 ตัดกับ Column 4 ในตารางพื้นสีส้ม

ให้ลองสร้างสูตรง่ายๆ เพื่อลองดูก่อนว่า concept แบบนี้ใช้ได้ไหม 

=Index( C3:G6, 3, 4) พบว่าหา 333 ออกมาได้ก็ใช้ได้แล้วสูตรนี้

จากนั้นจึงหาทางสร้างสูตร Match ที่จะหาว่ารหัส a003 อยู่ใน Row 3 กับ D มาจาก Column 4

เซลล์ C11 =Match( B11, B3:B6, 0 ) หาเลข 3 ได้
เซลล์ C12 =Match( B12, C2:G2, 0 หาเลข 4 ได้

พอได้สูตรหาตำแหน่งตัดกันระหว่าง Row 3 กับ Column 4 ได้แล้ว จึงลิงก์ค่าจาก C11 กับ C12 ไปใช้ต่อในสูตรเดิม =Index( C3:G6, 3, 4) ให้กลายเป็น

=Index( C3:G6, C11, C12)

จากนั้นจึงลอกตัวสูตร Match จากเซลล์ C11 กับ C12 ไปใส่ในสูตร Index แทน จะได้สูตรซ้อนกันตามที่เคยสร้างไว้ตามนี้

=INDEX( C3:G6, MATCH( B11, B3:B6, 0 ), MATCH( B12, C2:G2, 0)) 

พอ Cut สูตรไปวางที่ชีทอื่น สูตรจะมีชื่อชีทมาใส่เพิ่มให้เอง กลายเป็น

=INDEX( Sheet1!C3:G6, MATCH( B4, Sheet1!B3:B6, 0 ), MATCH( B5, Sheet1!C2:G2, 0))

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

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

ผมเพิ่มอีกสูตร =SUMPRODUCT((Top=B12)*(Left=B11)*MyData) ใช้ได้กรณีที่เป็นตัวเลขครับ ดูในตัวอย่าง

   

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

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

 

หลักการจับคู่วงเล็บ

พอเจอวงเล็บเปิดให้นับเลข 1 2 3 เพิ่มไปเรื่อยๆ

 

พอเจอวงเล็บปิดให้นับเลขลด

 

เลขเริ่มที่ 1 ต้องจบที่ 1 แสดงว่าใส่วงเล็บครบทุกคู่

 

เลขที่ตรงกัน คือ ตำแหน่งวงเล็บคู่เดียวกัน

 

=(((abc)*(def))*ghi)

=1 2 3abc3 * 3def3 2 * ghi1

 

((( นับเพิ่ม 123

) นับลด 3 ทำให้เหลือ 12

( นับเพิ่ม 3

)) นับลด 32

) นับลด 1

 

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

 

=SUM( ((Id=G3)*(Name=H3)) *Amount) 

 

สูตรนี้ใส่วงเล็บส่วนของเงื่อนไขแยกส่วนให้เห็นชัดเจน จะลดวงเล็บลงก็ได้ เพราะคูณเหมือนกัน เป็นสูตรที่ใช้หายอดรวมของ Amount เฉพาะรหัสเท่ากับ G3 และชื่อเท่ากับ H3

=SUM( (Id=G3)*(Name=H3) *Amount)
1 22 22 1

 

ถ้าใช้กับ Excel รุ่นก่อน 365 ต้องกดปุ่ม Ctrl+Shift+Enter พร้อมกันเพื่อใส่วงเล็บปีกกา {} ปิดหัวท้ายสูตรหรือเปลี่ยน Sum ไปเป็น SumProduct 

ดูตัวอย่างที่
https://www.excelexperttraining.com/book/index.php/course-manuals/excel-expert-tips-tricks-and-traps/array-if-multiply

 

  

ใช้ Excel ยังไง แล้วทำให้หัวหน้ารัก หัวหน้าหลง

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

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

ผมเล่าให้หัวหน้าฟังว่า ตารางคำนวณที่ผมสร้างสามารถรับตัวแปรได้ไม่จำกัด Unlimited Variables

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

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

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

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

ลองดูตัวอย่างนี้ ใช้หลักการเดียวกันครับ
https://docs.google.com/spreadsheets/d/19MlyLtP_JJd_L1Oq90ia_NHO0A8BJaxe/edit?usp=sharing&ouid=116915095293786385908&rtpof=true&sd=true

ตอนย้ายงานได้ใบชมมาตามนี้ครับ

   

Lahiri ลาหิรี คืออะไร

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

Download พระคัมภีร์สุริยยาตรศิวาคม
https://drive.google.com/file/d/1i9jBxohuU-7-S-_9CsWuMQOde2LdrMSQ/view?usp=sharing  
.
มีแต่ที่จะต่างกันคือถ้าจะใช้หลักโหราศาตร์ของตะวันตกก็ต้องไม่ปรับค่าอายนางศ์ เวลาซื้อหาโปรแกรมคำนวณตำแหน่งดาวมาใช้ต้องดูว่าคนที่สร้างโปรแกรมเขาสร้างมาให้ใช้กับแบบตะวันออกของเราหรือแบบตะวันตก
.
ในโปรแกรม 4ZSuriya ที่ผมสร้างขึ้นกำหนดให้ใช้ตัวเลขของนายลาหิรีเป็นหลัก แต่ยังสามารถเลือกได้ตามใจว่าจะใช้ค่าอายนางศ์ตามแบบของใคร ถ้าเลือก N จะไม่ปรับ จะได้ภาพจักราศีตามแบบตะวันตก
.
เชิญสมัครเรียน Excel ออนไลน์เพื่อเข้าไป download โปรแกรมนี้ได้ที่ XLSiam.com จะได้เรียนออนไลน์ ฟรีด้วยครับ

 

 

 

 

 

 

PivotTable สร้างง่ายแต่ให้คนอื่นใช้ต่อยาก PivotBy สร้างยากแต่ใช้ง่าย

จับประเด็นนี้ให้ได้ครับ เราติดนิสัยสบายตอนสร้างกันมานาน

 

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

 

 

Copilot : 

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

ข้อดีของ Dynamic Array เมื่อเทียบกับ PivotTable:

• อัปเดตอัตโนมัติ → ไม่ต้องกด Refresh ทุกครั้งที่ข้อมูลเปลี่ยน
• ใช้งานง่ายกว่า → ผู้ใช้ไม่ต้องตั้งค่า Pivot หรือสร้าง Layout เอง
• ทำให้ข้อมูลโปร่งใส → ทุกสูตรมองเห็นได้ ไม่มีการซ่อนข้อมูลไว้ใน Pivot Cache
• รองรับการใช้ร่วมกับ Dynamic Functions เช่น FILTER, SORT, UNIQUE → ทำให้การวิเคราะห์ข้อมูลมีความยืดหยุ่นมากขึ้น

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

คุณกำลังวางแนวทางการทำงานแบบนี้อยู่ใช่ไหม? หรือมีปัญหาที่กำลังพยายามแก้ไขอยู่?

 

PivotTable ต้อง Refresh สูตร PivotBy คำนวณใหม่ทันที : Refresh VS Auto แบบไหนดีกว่ากัน

ระบบการคำนวณของ Excel มีให้เลือก 3 แบบ

1. Automatic Calculation
2. Partial เดิมใช้คำว่า Automatic except for Data Table
3. Manual Calculation

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

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

การใช้สูตร PivotBY ร่วมกับ Manual Calculation จะช่วยทำให้ควบคุมตารางคำนวณได้ดีกว่า PivotTable อีกทั้งให้ 2 ทางเลือกว่าจะ Auto หรือ Manual ดีกว่าการใช้ระบบ Refresh ที่เป็นเพียงทางเลือกเดียวเท่านั้น

Copilot ชมกลับมาครับว่า
.
แนวคิดของคุณน่าสนใจมาก และถือเป็นการบริหารประสิทธิภาพของ Excel อย่างมีหลักการ! 🧐
.
การใช้ **Manual Calculation** มีข้อดีเด่นเรื่อง **การควบคุมกระบวนการคำนวณ** เพราะช่วยให้ Excel ไม่ต้องเสียทรัพยากรไปกับการคำนวณทุกครั้งที่มีการเปลี่ยนแปลงเล็กน้อย โดยเฉพาะในไฟล์ที่มีสูตรซับซ้อนและข้อมูลจำนวนมาก ซึ่งสอดคล้องกับกรณีที่คุณต้องการเร่งความเร็วในการเปิดไฟล์และจัดการข้อมูลเป็นขั้นตอนก่อนสั่งคำนวณ
.
การเลือกใช้ **PivotBY** ร่วมกับ **Manual Calculation** นั้นได้เปรียบเรื่องความ **ยืดหยุ่น** ที่ PivotTable ไม่สามารถให้ได้—เพราะ PivotTable ยังต้องพึ่ง **Refresh** เป็นหลักและไม่มีตัวเลือกให้คำนวณทันทีหรือตามต้องการ
.
**ข้อดีที่ชัดเจนของแนวทางคุณ:**
✔ ควบคุมช่วงเวลาที่ Excel คำนวณ ลดปัญหาความล่าช้า
✔ สามารถเลือกคำนวณแบบ **Auto หรือ Manual** ได้ตามสถานการณ์
✔ ตัดปัญหาเรื่อง Refresh ที่เป็นทางเลือกเดียวของ PivotTable
✔ ใช้ประโยชน์จาก Dynamic Calculation ของ PivotBY ได้เต็มที่
.
แนวทางนี้เหมาะกับการจัดการ **ไฟล์ขนาดใหญ่** หรือ **งานที่ต้องการควบคุมลำดับการคำนวณ** เช่น งานด้าน **วิเคราะห์ข้อมูล** หรือ **รายงานที่ต้องจัดการชุดข้อมูลซับซ้อนก่อนสรุปผล**

การใช้ระบบ **Refresh** กับ PivotTable และ Power Query มีเหตุผลสำคัญหลายประการ:

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

2. **การควบคุมกระบวนการ** – บางครั้งเราอาจไม่ต้องการให้ทุกการเปลี่ยนแปลงส่งผลต่อ PivotTable หรือ Query ทันที เช่น ในกรณีที่เรากำลังแก้ไขข้อมูลหลายรายการ การ Refresh ตามต้องการช่วยให้ผู้ใช้ควบคุมได้ดีขึ้น

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

4. **การทำงานร่วมกับแหล่งข้อมูลภายนอก** – Power Query มักใช้เชื่อมต่อกับฐานข้อมูลหรือไฟล์ CSV ที่ไม่ต้องการโหลดซ้ำทุกครั้ง แต่ต้องการโหลดใหม่ตามเงื่อนไขที่กำหนด เช่น ทุกเช้าหรือเมื่อคลิก Refresh

หากต้องการให้ข้อมูล PivotTable หรือ Power Query อัปเดตอัตโนมัติ สามารถตั้งค่า **Automatic Refresh** ได้ แต่ต้องคำนึงถึงข้อดีข้อเสียด้วย

สูตร **PIVOTBY** มีข้อได้เปรียบเหนือ **PivotTable** หลายด้าน โดยเฉพาะเรื่องการคำนวณแบบ **Dynamic** ที่ไม่ต้อง Refresh:

1. **คำนวณอัตโนมัติ** – PIVOTBY เป็น **Dynamic Array Function** ซึ่งหมายความว่าเมื่อข้อมูลต้นทางเปลี่ยนไป ผลลัพธ์จะอัปเดตทันที โดยไม่ต้องกด Refresh เหมือน PivotTable.

2. **ความยืดหยุ่นในการปรับแต่ง** – สามารถกำหนดโครงสร้างของผลลัพธ์ได้โดยตรงในสูตร เช่น การจัดกลุ่มข้อมูล, การใช้ฟังก์ชันสรุปผลที่หลากหลาย (SUM, AVERAGE, COUNT ฯลฯ) และการกรองข้อมูล.

3. **ลดข้อจำกัดของ PivotTable** – PivotTable มีข้อจำกัดเรื่องการอ้างอิงข้อมูล เช่น การใช้ในสูตรอื่น ๆ อาจต้องใช้ GETPIVOTDATA หรือแปลงเป็นตารางปกติ แต่ PIVOTBY สามารถใช้ร่วมกับฟังก์ชันอื่น ๆ ได้ง่ายกว่า.

4. **เหมาะกับการทำงานแบบ Dynamic** – ถ้าต้องการให้ข้อมูลเปลี่ยนแปลงแบบเรียลไทม์โดยไม่ต้องกด Refresh หรือใช้ VBA, PIVOTBY จะเป็นตัวเลือกที่ดีกว่า PivotTable.

อย่างไรก็ตาม **PivotTable ยังมีข้อดี** เช่น การจัดรูปแบบที่ง่ายกว่า, การใช้ Slicer และ Timeline เพื่อกรองข้อมูล และการรองรับการทำงานร่วมกับ Power Pivot ได้ดีขึ้น.
😊

อยากหาตำรา Excel 365 ที่ทันสมัย ต้องเลือกให้ดีว่า พิมพ์หลังวันที่ 25 กันยายน 2024

ควรหาตำราที่ออกวางตลาดหลังวันที่ 25 กันยายน 2024 เยอะๆครับ เพราะเป็นวันที่ Microsoft update Excel 365 ให้มีสูตร GroupBy กับ PivotBY เริ่มให้ใช้งานแทนการใช้ PivotTable
.
ทางที่ดีกว่าการอ่านตำรา ซึ่งยากมากที่คนเขียนจะตามของใหม่ๆใน 365 ทัน แนะนำให้ดูจากเว็บนี้ครับ https://techcommunity.microsoft.com/category/microsoft365/blog/excelblog?wt.mc_id=M365-MVP-4000499
.
หรือจาก
https://exceljet.net/
https://excelexpertlibrary.blogspot.com/
.
อย่ามองแค่ว่ามีสูตรใหม่รวบรวมไว้เท่านั้น ควรดูว่ามีคำแนะนำให้เลือกใช้สูตรอะไรเพราะอะไร เนื่องจาก Microsoft ออกสูตรใหม่มาเป็นชุดเพื่อมุ่งให้ใช้งานร่วมกัน มีเป้าหมายเพื่อช่วยให้ผู้ใช้แฟ้มที่ไม่คล่อง Excel สามารถนำแฟ้มนั้นมาใช้ต่อร่วมกันได้โดยไม่จำเป็นต้องพึ่งพาคนสร้างแฟ้มอีกต่อไป
.
สูตรใหม่ๆจะช่วยทำให้คนใช้แฟ้มลดภาระไม่ต้องเรียนรู้ว่าต้องไปคลิกที่เมนูไหนอีก พอกรอกค่าใหม่ก็จะได้คำตอบออกมาให้ทันทีโดยไม่ต้องสั่ง Refresh 

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

Copilot แนะนำหนังสือมาให้ครับ "คู่มือใช้งาน Excel 365 ฉบับสมบูรณ์"
.
ใช่เลย! ถ้าต้องการตำรา Excel 365 ที่ทันสมัยและครอบคลุมฟีเจอร์ล่าสุด การเลือกหนังสือที่พิมพ์หลังวันที่ **25 กันยายน 2024** จะช่วยให้มั่นใจว่าเนื้อหาครอบคลุมฟังก์ชันใหม่ ๆ ที่ Microsoft อัปเดตในช่วงปีที่ผ่านมา
.
ฉันพบว่ามีหนังสือ **"คู่มือใช้งาน Excel 365 ฉบับสมบูรณ์"** ที่อาจตรงกับความต้องการของคุณ สามารถดูรายละเอียดเพิ่มเติมได้ที่ [CHULABOOK](https://www.chulabook.com/computer/162767) และ [SERAZU](https://serazu.com/web/product/4223) ซึ่งมีเนื้อหาครอบคลุมตั้งแต่พื้นฐานไปจนถึงการใช้ฟังก์ชันขั้นสูง เช่น **LAMBDA, PivotTable, Slicer และ VBA** 

 
.

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

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

ควรรีบใช้สูตรใหม่ๆที่ทำงานแบบ Dynamic Array ใน Excel 365 ไหม

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

.

แต่ถ้าต้องการช่วยทำให้ทุกคนที่ได้รับแฟ้มที่แชร์ไป สามารถใช้แฟ้มต่อได้ทันทีโดยไม่ต้องห่วงว่าเขาจะมีพื้นฐาน Excel มากน้อยแค่ไหน สูตรที่ทำงานแบบ Dynamic Array เหล่านี้ โดยเฉพาะสูตร GroupBY กับ PivotBY จะมีประโยชน์อย่างมาก

.

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

 

 

 

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

 

Copilot เสริม :

.

แนวทางที่คุณให้ไว้นั้นยอดเยี่ยมและตรงประเด็นมาก! ฉันขอเสริมแนวคิดที่ช่วยให้ผู้ใช้เข้าใจประโยชน์ของ Dynamic Array Functions ได้ชัดเจนขึ้น และเพิ่มมุมมองเกี่ยวกับการปรับตัวให้เข้ากับ Excel รุ่นใหม่:

.

### **แนวทางเพิ่มเติมเพื่อเพิ่มประสิทธิภาพการใช้สูตรรุ่นใหม่**

.

1. ** ปรับแนวคิดสู่การทำงานแบบอัตโนมัติ**

- Dynamic Arrays ไม่ใช่แค่ช่วยให้ข้อมูลอัปเดตอัตโนมัติ แต่ยังลดข้อผิดพลาดจากการแก้ไขด้วยมือ

- เมื่อใช้ฟังก์ชันเช่น `GROUPBY`, `PIVOTBY` หรือ `FILTER`, เราจะลดขั้นตอนที่ต้องกดเมนูหลายครั้ง

.

2. ** รองรับการทำงานร่วมกันอย่างราบรื่น**

- หากแชร์ไฟล์ให้ผู้อื่น **ฟังก์ชันใหม่ช่วยให้ทุกคนใช้งานได้ง่ายขึ้น**

- ไม่ต้องกังวลว่าผู้รับไฟล์จะต้องรู้จัก PivotTable มาก่อน

.

3. ** ลดการพึ่งพาการแก้ไขแฟ้มแบบ Manual**

- ใช้ `SORT`, `UNIQUE` และ `CHOOSECOLS` เพื่อให้ข้อมูลจัดเรียงและกรองอัตโนมัติ

- ไม่ต้องกลับมาแก้ไขสูตรหรือ Refresh PivotTable ด้วยตัวเอง

.

4. ** รองรับไฟล์ขนาดใหญ่โดยไม่ต้องกังวลประสิทธิภาพ**

- Dynamic Arrays ช่วยให้คำนวณข้อมูลที่เปลี่ยนแปลงตลอดเวลาได้เร็วขึ้น

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

.

5. ** ปรับปรุงการวิเคราะห์ข้อมูลให้คมชัดขึ้น**

- ฟังก์ชันอย่าง `TEXTSPLIT`, `SEQUENCE` และ `VSTACK` ช่วยให้สามารถจัดการข้อมูลให้เหมาะสมกับการวิเคราะห์

- ไม่ต้องใช้เทคนิคเดิมที่ซับซ้อน เช่น Helper Columns หรือ VBA

.

---

.

### **สรุป**

แนวทางที่คุณให้ไว้นั้นสามารถใช้เป็นพื้นฐานที่ดีมาก! หากต้องการเพิ่มการโน้มน้าวใจ ผู้อ่านอาจสนใจกรณีศึกษาหรือตัวอย่างจริงที่แสดงให้เห็นว่า Dynamic Arrays ช่วยลดเวลาทำงานไปได้แค่ไหน เช่น **เปรียบเทียบไฟล์ที่ใช้ PivotTable กับไฟล์ที่ใช้ GROUPBY และ PIVOTBY**.

 

 

25 กย 24 เริ่มมีสูตร GroupBy กับ PivotBy เกิดขึ้นมาเพื่อใคร

Excel 365 วางตลาดเปิดให้ใช้กันตั้งแต่วันที่ 30 มิถุนายน 2011 ใช้เวลาสิบกว่าปีทีเดียวพัฒนาตัวเองให้มีความสามารถดีขึ้นเรื่อยๆ ก่อนหน้านี้เป็นเจ้าตลาดจึงนิ่งมานานไม่ได้ปรับปรุงอะไรใหม่ แถมช่วงก่อนหน้านี้หันไปสร้าง Power BI เพื่อรักษาตลาดกลุ่มบริษัทองค์กรขนาดใหญ่ให้ไม่หนีไปไหน จนกระทั่ง Power BI เริ่มครองใจของลูกค้า ตอนนี้จึงหันกลับมาปรับปรุงตัว Excel บ้าง โดยเฉพาะอย่างยิ่งใน Excel 2021 ที่เริ่มต้นการใช้ Dynamic Array

.

Excel 2021 กับ Excel 365 มีสูตรใหม่ๆที่ทำงานแบบ Dynamic Array เช่น Filter, Sequence, Sort, SortBy, Unique มุ่งเปลี่ยนวิธีการใช้ Excel จากแต่เดิมที่ต้องใช้มือไปคลิกบนเมนูเพื่อสั่งงาน เปลี่ยนวิธีการใช้ Excel มาเป็นการใช้สูตรที่ทำงานได้ใหม่ทันที แบบเดียวกันกับระบบการคำนวณ Automatic Calculation

.

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

.

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

.

Microsoft ใช้เวลาพัฒนา Excel ต่ออีก 3 ปี จนกระทั่งในวันที่ 25 กันยายน 2024 ได้ออกสูตรใหม่เอี่ยมที่สามารถนำมาใช้แทน PivotTable ได้แก่สูตร GroupBY กับ PivotBY ซึ่งสามารถคำนวณใหม่ให้คำตอบใหม่ให้ทันทีโดยไม่ต้องสั่ง Refresh อีกต่อไป

 

 

.

Copilot แนะนำเพิ่มเติม

. 

ข้อเสนอของคุณมีความครอบคลุมและให้ภาพรวมที่ดีเกี่ยวกับวิวัฒนาการของ Excel! ฉันมีข้อเสนอแนะเล็กน้อยเพื่อเสริมข้อมูลให้สมบูรณ์ยิ่งขึ้น:

 

1. **การพัฒนา Dynamic Array** 

 

คุณกล่าวถึงว่า Excel 2021 เป็นจุดเริ่มต้นของการใช้ Dynamic Arrays ซึ่งถูกต้อง แต่ฟีเจอร์นี้ได้ถูกนำมาใช้ก่อนหน้านั้นใน **Excel 365 (รุ่น Insider) ตั้งแต่ปี 2019** ก่อนจะเป็นทางการใน Excel 2021. คุณอาจระบุให้ชัดเจนว่า Excel 365 ได้ใช้ Dynamic Arrays ก่อนหน้า Excel 2021

 

2. **PivotTable กับฟังก์ชันใหม่** 

 

`GROUPBY` และ `PIVOTBY` เป็นสองฟังก์ชันสำคัญที่ช่วยลดการพึ่งพา PivotTable แต่ยังมีฟังก์ชันอื่นที่ช่วยเสริมการทำงาน เช่น `CHOOSECOLS`, `CHOOSEROWS`, `VSTACK`, `HSTACK` ที่ช่วยให้การสร้างรายงานและสรุปข้อมูลมีความยืดหยุ่นมากขึ้น คุณอาจพิจารณาเพิ่มฟังก์ชันเหล่านี้เพื่อขยายมุมมองเกี่ยวกับการเปลี่ยนแปลง

 

3. **Power BI กับ Excel** 

 

คุณกล่าวถึงว่า Microsoft หันไปพัฒนา **Power BI** เพื่อรักษาตลาดองค์กร ซึ่งเป็นเรื่องที่ถูกต้อง อย่างไรก็ตาม Power BI ไม่ได้มาแทน Excel แต่เป็นเครื่องมือที่ทำงานร่วมกัน โดย Excel ได้รับการปรับปรุงให้รองรับ **Power Query** และ **Data Model** ทำให้สามารถทำงานแบบ BI ได้โดยไม่ต้องออกจาก Excel นี่เป็นประเด็นที่สามารถเสริมให้เห็นว่า Excel ไม่ได้เพียงแข่งขันกับ Power BI แต่ยังใช้ประโยชน์จากมันด้วย

 

4. **ระบบอัปเดตของ Excel 365** 

 

เนื่องจาก Excel 365 เป็นบริการแบบ **Subscription-based**, Microsoft ได้เพิ่มฟีเจอร์ใหม่ๆ อย่างต่อเนื่อง เช่นการนำ AI มาช่วยใน Excel (เช่น Copilot) ซึ่งไม่ได้เกิดขึ้นใน Excel 2021 ที่เป็นรุ่นซื้อขาด ข้อมูลส่วนนี้สามารถช่วยให้ผู้ใช้เข้าใจว่าทำไม Excel 365 มีการพัฒนาเร็วกว่ารุ่นซื้อขาด

 

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

 

Copilot อธิบายให้ใหม่ตามนี้ :

.

แน่นอน! ฉันจะจัดเรียงข้อมูลให้ลึกขึ้น และเพิ่มรายละเอียดเพื่อให้เห็นภาพรวมที่ชัดเจนยิ่งขึ้นของวิวัฒนาการของ Excel และฟังก์ชันสำคัญที่เปลี่ยนแปลงการใช้งาน:

.

---

.

### **วิวัฒนาการของ Excel: จาก Office ไปสู่ยุค Dynamic Array และ AI**

.

#### **1. การเปิดตัว Excel 365 (2011) และการพัฒนาต่อเนื่อง**

- **เปิดตัว:** 30 มิถุนายน 2011 เป็นส่วนหนึ่งของ Microsoft Office 365

- **ยุคแรก:** Excel 365 ยังคงใช้ฟังก์ชันแบบดั้งเดิมเช่น `VLOOKUP`, `INDEX`, `MATCH`

- **PivotTable ยังครองตลาด:** Microsoft ให้ความสำคัญกับ PivotTable เป็นเครื่องมือสรุปข้อมูลหลัก

- **การเปลี่ยนผ่านสู่ Power BI:** ในช่วงปี 2015-2016 Microsoft มุ่งเน้นไปที่ **Power BI** เพื่อรักษาตลาดองค์กร

.

#### **2. การเปลี่ยนแปลงครั้งใหญ่กับ Dynamic Arrays (2019 - 2021)**

- **Excel 365 รุ่น Insider (2019):** Microsoft แนะนำ **Dynamic Array Functions** ให้ทดลองใช้

- **Excel 2021 (5 ต.ค. 2021):** ฟีเจอร์ Dynamic Arrays ถูกนำมาใช้เป็นทางการ

- **สูตรใหม่:**

- `FILTER` – กรองข้อมูลตามเงื่อนไข

- `SEQUENCE` – สร้างชุดตัวเลข

- `SORT` และ `SORTBY` – เรียงลำดับข้อมูลแบบไดนามิก

- `UNIQUE` – ดึงค่าที่ไม่ซ้ำกัน

.

- **Impact:** Dynamic Arrays ลดการพึ่งพาการคลิกเมนู เปลี่ยนวิธีใช้ Excel ให้เน้น **สูตร** มากขึ้น

.

#### **3. ฟังก์ชันใหม่แทน PivotTable: GROUPBY และ PIVOTBY (2024)**

- **PivotTable มีข้อจำกัด:** ต้องกด Refresh และแก้ไขยาก

- **เปิดตัวฟังก์ชันใหม่:** 25 กันยายน 2024

- `GROUPBY` – จัดกลุ่มข้อมูลโดยอัตโนมัติ

- `PIVOTBY` – สร้าง Pivot แบบไดนามิก **โดยไม่ต้อง Refresh**

.

- **เสริมด้วยฟังก์ชันใหม่:**

- `CHOOSECOLS` และ `CHOOSEROWS` – เลือกเฉพาะคอลัมน์หรือแถวที่ต้องการ

- `VSTACK` และ `HSTACK` – รวมข้อมูลจากหลายช่วง

- `TEXTSPLIT` และ `TEXTJOIN` – แยกและรวมข้อความ

.

#### **4. การพัฒนา Excel ด้วย AI และ Copilot**

- **Excel 365 ปัจจุบัน:** Microsoft ใช้ AI มาปรับปรุงฟังก์ชันและแนะนำ Copilot

- **Copilot ช่วยอะไรบ้าง:**

- แนะนำสูตรที่เหมาะสม

- วิเคราะห์ข้อมูลอัตโนมัติ

- ช่วยสร้างรายงานแบบไดนามิก

.

---

.

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

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

 

Copilot ตอบเสริม :

.

นอกจาก `GROUPBY` และ `PIVOTBY` ซึ่งเป็นฟังก์ชันใหม่ที่ช่วยให้สามารถสรุปข้อมูลแบบไดนามิกโดยไม่ต้องใช้ PivotTable ยังมีฟังก์ชันอื่น ๆ ที่สามารถใช้ร่วมกันเพื่อสร้างการวิเคราะห์ข้อมูลที่มีประสิทธิภาพ เช่น:

.

- **`CHOOSECOLS` และ `CHOOSEROWS`** – ช่วยเลือกเฉพาะคอลัมน์หรือแถวที่ต้องการจากชุดข้อมูล

- **`GROUPBY`** – ใช้จัดกลุ่มข้อมูลตามคอลัมน์ที่กำหนดและสรุปค่าต่าง ๆ

- **`PIVOTBY`** – ใช้สร้างตาราง Pivot แบบไดนามิกโดยไม่ต้องใช้ PivotTable

- **`VSTACK` และ `HSTACK`** – ใช้รวมข้อมูลจากหลายช่วงเข้าด้วยกันในแนวตั้งหรือแนวนอน

- **`TEXTSPLIT` และ `TEXTJOIN`** – ใช้แยกหรือรวมข้อความจากเซลล์ต่าง ๆ

- **`FILTER`** – ใช้กรองข้อมูลตามเงื่อนไขที่กำหนด

- **`SORT` และ `SORTBY`** – ใช้เรียงลำดับข้อมูลแบบไดนามิก

- **`UNIQUE`** – ใช้ดึงค่าที่ไม่ซ้ำกันจากช่วงข้อมูล

.

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

 

 

 

วิธีฉลาดใช้สูตร Dynamic Array จัดการข้อมูล ให้ง่ายขึ้น

สูตรใหม่ใน Excel 365 มีความสามารถพิเศษที่จะขยายพื้นที่คำตอบให้เอง (Dynamic Array) ช่วยทำให้ไม่ต้องสร้างสูตรแล้วต้อง copy ไปใช้เพื่อหาคำตอบอื่นอีกต่อไป

 

ตามภาพนี้ ตารางตรงกลางใช้สูตร Filter เพื่อหาว่ารหัสที่กรอกไว้ในเซลล์สีส้ม F5 มีรายการอะไรบ้าง โดยใช้เงื่อนไข ID=$F$5

H5 =FILTER( C5:D9, ID=$F$5 ) 

การกำหนด C5:D9 ไว้นี่แหละที่ทำให้สูตร Filter หาคำตอบทั้ง Name กับ Amount จากพื้นที่ในตารางฐานข้อมูลด้านซ้ายให้พร้อมกันทีเดียวว่ารหัส a001 มีรายการซ้ำที่ใช้รหัสเดียวกัน มีชื่ออะไรบ้างและจำนวนเป็นเท่าไร

ตารางตรงกลางเป็นพื้นที่ Dynamic Array ที่สูตรนี้หาคำตอบมาให้ ซึ่งจะยืดได้หดได้ ทำให้เมื่อนำพื้นที่ส่วนนี้ไปใช้ต่อต้องสร้างสูตรที่จะทำหน้าที่หาพื้นที่ซึ่งจะยืดได้หดได้นี้ตามไปด้วย

สุดยอดเลยใช่ไหมครับ สูตรของ 365 พิสดารมาก ช่วยทำเรื่องยากให้กลายเป็นง่าย ... แต่ 

ถ้าจะนับว่ามีกี่รายการที่ซ้ำกัน หากใช้สูตร =CountA(H5:H6) ก็จะผิดพลาดได้ทันทีเพราะรายการที่สูตร Filter หาให้นั้นอาจมีจำนวนรายการมากกว่าที่เห็น จำเป็นต้องสร้างสูตรยาวขึ้นโดยต้องซ้อนสูตร ChooseCols เพิ่มเข้าไปตามนี้

H17 =COUNTA( CHOOSECOLS(H5#,1) ) 

H5# ในสูตร CHOOSECOLS(H5#,1) เป็นวิธีการอ้างอิงหาพื้นที่ตารางทั้งหมดที่สูตร Filter หาคำตอบให้ โดยลิงก์มาจากเซลล์หัวมุมซ้ายบนสุดของตาราง การเติมเครื่องหมาย # เข้าไปเพื่อทำให้รับรู้ถึงขนาดตารางที่จะยืดได้หดได้ให้โดยอัตโนมัติ

ส่วนเลข 1 เป็นเลขที่ Column ที่ 1 ในตารางที่ต้องการนำมานับจำนวนด้วยสูตร CountA อยากนำ Column ไหนมาใช้ก็ต้องใส่เลขให้ตรง (ทำนองเดียวกันกับสูตร VLookup ที่พึ่งพาเลขที่ Column ของค่าที่ต้องการหาคำตอบ)

สูตร ChooseCols นี่เองคือความซับซ้อนที่ตามมา ต้องคอยนำสูตรนี้มาใส่ลงไปเสมอในสููตรใดๆก็ตามที่ไปอ้างอิงกับพื้นที่ Dynamic Array ทำให้การสร้างสูตรยากขึ้นมาก 

ถ้าใช้สูตร XLookup กับพื้นที่ Dynamic Array ก็ต้องซ้อนสูตร ChooseCols ลงไป 2 ครั้งเพื่อให้ค้นหาจาก Column นึงไปหาคำตอบจากอีก Column

แทนที่จะสร้างความยุ่งยากตามมาทีหลัง ให้เปลี่ยนวิธีสร้างสูตร Filter ตั้งแต่แรกดีกว่า

จากเดิม H5 =FILTER( C5:D9, ID=$F$5 ) 

ให้แยกเป็น 2 สูตรเพื่อหาคำตอบที่ละ Column

L5 =FILTER( C5:C9, ID=$F$5 ) เพื่อหาส่วนของชื่อของรหัสซ้ำ
M5 =FILTER( D5:D9, ID=$F$5 ) เพื่อหาส่วนของจำนวนของรหัสซ้ำ

จากนั้นเมื่อต้องการนับจำนวนรายการก็ใช้สูตรสั้นๆง่ายๆตามนี้ ไม่ต้องใช้ ChooseCols ซ้อนเข้าไปอีกแล้ว

L17 =COUNTA(L5#)

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

หลักการในการใช้ Excel 365 ที่ควรยึดถือ

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

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

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

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

Copilot :

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

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

อีกทางเลือกหนึ่งที่อาจช่วยได้คือ
✅ ใช้ LET เพื่อเก็บค่าผลลัพธ์จาก Dynamic Array แล้วเรียกใช้ภายในสูตรต่อไป
✅ ใช้ WRAPROWS / WRAPCOLS หากต้องการปรับการจัดเรียงข้อมูลแบบยืดหยุ่น
✅ ใช้ TOCOL / TOCOLS เพื่อแปลงข้อมูลเป็นรูปแบบที่นำไปใช้งานต่อได้สะดวก

😃

 

รวมชีทมาต่อกัน ใช้แค่ 2 Steps หมดยุค Power Query

 

สมมติในแฟ้มมีอยู่ 3 ชีท

Step 1 คลิกในแต่ละตารางในแต่ละชีทสั่ง Insert > Table จะเกิด Table Name ชื่อ Table1, Table2, Table3

Step 2 เปิดชีทใหม่ขึ้นมา ลอกหัวตารางมาใส่ไว้ก่อน แล้วในเซลล์ด้านล่างให้สร้างสูตร =VSTACK( Table1, Table2, Table3 )

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

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

ชมคลิปที่ทำไว้ซึ่งไม่ต้องใช้ VBA หรือ Power BI มาช่วยอีกต่อไป
https://xlsiam.com/lesson/13-vstack-choosecols/