อยากหาตำรา 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/

เลิกใช้ VLookup หรือแม้แต่ XLookup หันมาใช้สูตรนี้ใน Excel 365 แทนดีกว่า

เวลาใช้สูตร VLookup หรือ XLookup แล้วรู้สึกไม่สบายใจ 2 ชั้น แบบนี้หรือเปล่า 

1. ไม่แน่ใจว่าสูตรจะหาค่าเจอไหม ถ้าไม่เจอแล้วเกิด error เตือนขึ้นมาจะต้องมาสร้างสูตรเพื่อแก้ error กันอีก

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

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

เหนือชั้นขึ้นอีกนิด ให้ใช้สูตร CountIF ช่วยหาก่อนว่ารหัสนั้นมีกี่รายการ จะได้ไม่ต้องเสียเวลามาแก้ error ตามทีหลัง

* ถ้ามีเพียงรายการเดียว CountIF ในเซลล์ F8 = 1 จึงใช้ VLooup หรือ XLookup ตามสูตรในภาพนี้

=IF( $F$8=1, VLOOKUP( $F$5, MyData, 2, 0 ), 0)
=IF( $F$8=1, XLOOKUP( $F$5, ID, C5:D9 ), 0)

** ถ้ามีรายการเดียวหรือหลายรายการ CountIF ในเซลล์ F8 >= 1 จึงใช้ Filter ตามสูตรในภาพนี้

=IF( $F$8>=1, FILTER( C5:D9, ID=F5 ), 0 )

ถ้าคุณใช้ 365 เป็นหลัก เลิกใช้ VLookup หรือแม้แต่ XLookup หันมาใช้สูตร Filter แทนดีกว่า

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

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

Copilot ตอบ:

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

📌 ข้อดีของ FILTER เทียบกับ XLOOKUP ใน Dynamic Array:

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

📌 แต่ XLOOKUP ก็มีจุดเด่นของมัน:

• หากต้องการ ค้นหาค่าที่เจาะจง และ ไม่มีหลายค่าที่ต้องคืนกลับ XLOOKUP อาจทำงานได้ตรงไปตรงมากว่า
• สามารถใช้ค้นหาค่าที่ใกล้เคียง (Approximate Match) ได้ ซึ่ง FILTER ไม่รองรับโดยตรง

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

นี่คือข้อดีและข้อเสียของ FILTER และ XLOOKUP เพื่อช่วยให้คุณเลือกใช้ให้เหมาะสมกับงานของคุณ

🏆 FILTER

✅ ข้อดี:

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

❌ ข้อเสีย:

• ไม่รองรับ Approximate Match → ไม่สามารถค้นหาค่าที่ใกล้เคียงที่สุดได้
• ไม่สามารถค้นหาจากหลายตารางพร้อมกันโดยตรง → ต้องใช้สูตรช่วย เช่น VSTACK หรือ INDIRECT

🎯 XLOOKUP

✅ ข้อดี:

• สามารถค้นหาค่าที่ใกล้เคียงที่สุดได้ (Approximate Match) → ใช้ในงานคำนวณเช่น ช่วงคะแนน
• รองรับค่าเริ่มต้นเมื่อไม่พบข้อมูล → ป้องกัน Error โดยสามารถกำหนดค่า Default ได้
• ค้นหาจากหลายตารางได้ → ใช้หลาย Lookup Array ผ่าน IF หรือ CHOOSE ได้

❌ ข้อเสีย:

• คืนค่าได้เพียงหนึ่งค่าต่อเซลล์ → ไม่สามารถคืนค่าเป็น Dynamic Array ได้โดยตรง
• ไม่ยืดหยุ่นเมื่อข้อมูลเปลี่ยนแปลง → ต้องใช้ OFFSET หรือ INDIRECT ช่วย

🧐 สรุป:

• ใช้ FILTER ถ้าต้องการ คืนค่าหลายรายการ และรองรับ Dynamic Array
• ใช้ XLOOKUP ถ้าต้องการ ค้นหาค่าที่แน่นอนหรือใกล้เคียง ในช่วงข้อมูล

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

เมื่อใช้ XLOOKUP กับ Dynamic Array คุณต้องระวังเรื่องช่วงข้อมูลที่อาจขยายหรือเปลี่ยนแปลงได้
วิธีที่แนะนำคือใช้ XLOOKUP ร่วมกับ Dynamic Array Functions เช่น SEQUENCE หรือ FILTER

✨ ตัวอย่าง 1: ค้นหาค่าใน Dynamic Array

สมมติว่าคุณมีข้อมูลรายชื่อสินค้าอยู่ในช่วง A2:A10 และราคาสินค้าอยู่ใน B2:B10
แต่ช่วงข้อมูลนี้สามารถเปลี่ยนแปลงขนาดได้ (Dynamic Array)

=XLOOKUP("สินค้า1", A2:A10, B2:B10)

☑ คำสั่งนี้จะค้นหาราคา "สินค้า1" จากช่วงข้อมูลที่กำหนด

🏆 วิธีใช้ XLOOKUP ร่วมกับ FILTER สำหรับ Dynamic Array

หากต้องการค้นหาราคาเฉพาะสินค้าที่ตรงตามเงื่อนไข เช่น ราคามากกว่า 500
ใช้ FILTER เพื่อกำหนดช่วงข้อมูลให้เหมาะกับ Dynamic Array

=XLOOKUP("สินค้า1", FILTER(A2:A10, B2:B10>500), FILTER(B2:B10, B2:B10>500))

☑ สูตรนี้จะค้นหาสินค้าเฉพาะที่ราคามากกว่า 500 และใช้กับ Dynamic Array

🔄 XLOOKUP ร่วมกับ SEQUENCE สำหรับ Dynamic Array

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

=XLOOKUP(1000, SEQUENCE(10,1,500,100), SEQUENCE(10,1,1000,100))

☑ สูตรนี้จะค้นหาค่าที่ใกล้เคียงกับ 1000 ในชุดตัวเลขที่สร้างขึ้นแบบ Dynamic

🚀 สรุป

• XLOOKUP สามารถใช้ร่วมกับ FILTER เพื่อค้นหาข้อมูลที่ตรงเงื่อนไข
• XLOOKUP ร่วมกับ SEQUENCE เหมาะกับการดึงค่าตามช่วงที่เปลี่ยนไป
• เลือกช่วงข้อมูลที่ Dynamic Array รองรับ เพื่อให้สูตรทำงานได้ถูกต้อง

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

ผมถามกลับไปตามนี้

สูตรนี้ =XLOOKUP("สินค้า1", A2:A10, B2:B10) ถ้าช่วงข้อมูลเป็น dynamic array การที่อ้างอิง A2:A10 กับ B2:B10 ไว้ พอตารางที่เป็น dynamic array ขยายขนาดทั้งจำนวน column และจำนวน row
สูตร =XLOOKUP("สินค้า1", A2:A10, B2:B10) จะขยายตำแหน่งอ้่างอิงในวงเล็บตามไหม

Copilot ตอบ

สูตร =XLOOKUP("สินค้า1", A2:A10, B2:B10) จะ ไม่ขยายอัตโนมัติ ตามขนาดของ Dynamic Array หากตารางมีการขยายทั้งแถวและคอลัมน์
เนื่องจาก XLOOKUP ใช้ช่วงแบบ Fixed References (เช่น A2:A10, B2:B10) ซึ่งไม่ได้ปรับขนาดตาม Spilled Range

🔄 วิธีแก้ไขให้ XLOOKUP รองรับ Dynamic Array

✅ ใช้ชื่อช่วงแบบ Dynamic Range:
ลองใช้ INDEX หรือ INDIRECT เพื่อช่วยกำหนดช่วงข้อมูลให้เปลี่ยนแปลงตาม Dynamic Array

=XLOOKUP("สินค้า1", A2:INDEX(A:A,ROWS(A2#)), B2:INDEX(B:B,ROWS(B2#)))

📌 สูตรนี้ใช้ A2# และ B2# เพื่อให้ XLOOKUP อ้างอิงช่วงข้อมูลที่ขยายอัตโนมัติ

✅ ใช้ FILTER เพื่อช่วยกำหนดช่วง:

=XLOOKUP("สินค้า1", FILTER(A2:A100, A2:A100<>""), FILTER(B2:B100, A2:A100<>""))

📌 สูตรนี้ช่วยคัดกรองค่าที่มีอยู่จริง และปรับช่วงให้รองรับ Dynamic Array

🚀 สรุป

• XLOOKUP ไม่สามารถปรับขนาดช่วงข้อมูลเอง → ต้องใช้ INDEX หรือ FILTER ช่วย
• ช่วงที่เป็น Dynamic Array ใช้ # (Spilled Range) ได้ → เช่น A2# เพื่อให้ XLOOKUP ดึงข้อมูลที่ขยายอัตโนมัติ

คุณลองใช้กับข้อมูลที่มีอยู่หรือยังครับ? 😊

 

 

ชมคลิปเรียน Excel ออนไลน์ 600 กว่าบทเรียน ฟรี ตลอดชีพ

 

 

รวมลิงก์คลิปเรียน Excel ออนไลน์ถึงปี 2024 ของอาจารย์สมเกียรติ ฟุ้งเกียรติ
ผู้สร้างเว็บ ExcelExpertTraining.com 

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

เชิญเข้าเรียนที่เว็บ Vimeo.com ได้ฟรีตลอดชีพ ไม่ต้องสมัคร ไม่ต้องลงทะเบียน

เรียนแล้วสงสัย เชิญถามได้ที่ Facebook กลุ่มคนรัก Excel
https://www.facebook.com/groups/ThaiExcelLover/

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

สุดยอดเคล็ดลับและลัดของ Excel
    
Tips Tricks and Traps รวมเคล็ดลับและวิธีลัดขั้นตอนเพื่อใช้ Excel สร้างงานได้เหนือกว่าที่ทราบกันทั่วไป
https://vimeo.com/user/113132977/folder/4028430?isPrivate=false

Download ตัวอย่าง
https://www.excelexperttraining.com/online/download/tips.zip  
 
+++++++++++++++++++++++++++++++
ฉลาดใช้สารพัดสูตร Excel สร้างงานอย่างมืออาชีพ
    
เรียนรู้วิธีวิเคราะห์ปัญหาเพื่อเลือกใช้สูตรให้ตรงกับงาน สร้างแฟ้มที่สามารถทำงานได้ยืดหยุ่น ง่ายต่อการใช้งานร่วมกัน
https://vimeo.com/user/113132977/folder/4166216?isPrivate=false    

Download ตัวอย่าง
https://www.excelexperttraining.com/online/download/fml.zip

+++++++++++++++++++++++++++++++
เรื่องรีบรู้เพื่อพร้อมใช้ Excel ทำงานแบบ Fast and Easy
    
เรียนรู้วิธีใช้ Excel สร้างงานได้ง่ายขึ้น เร็วขึ้น และสะดวกขึ้น ไม่ต้องใช้เวลานานกว่าจะสร้างเสร็จ … แล้วคุณจะรัก Excel มากขึ้น

https://vimeo.com/user/113132977/folder/1991348?isPrivate=false

Download ตัวอย่าง
https://www.excelexperttraining.com/online/download/fast.zip  
 
+++++++++++++++++++++++++++++++
บนเส้นทางลัด Pivot Table สู่การสร้าง Dashboards
    
เคล็ดการสร้าง Pivot Table ที่เหนือกว่าวิธีที่รู้จักกัน ทำให้ใช้งานได้ยืดหยุ่นมากกว่าเดิม เพื่อใช้คู่กับกราฟบน Dashboards
https://vimeo.com/user/113132977/folder/1987722?isPrivate=false

Download ตัวอย่าง
https://www.excelexperttraining.com/online/download/pivot.zip   

+++++++++++++++++++++++++++++++
Excel Dynamic Reports
    
สร้างรายงานสำหรับผู้บริหาร ชี้ตัวเลขสำคัญได้ชัดเจน สามารถวางแผนและตัดสินใจได้ง่ายขึ้น ยืดหยุ่น และรวดเร็วยิ่งขึ้น
https://vimeo.com/user/113132977/folder/2062894?isPrivate=false
https://vimeo.com/user/113132977/folder/2095295?isPrivate=false

Download ตัวอย่าง
https://www.excelexperttraining.com/online/download/reports.zip   

+++++++++++++++++++++++++++++++
คิดจะใช้ Power BI ... หันมาใช้ Excel จัดการข้อมูลก่อนดีกว่า
    
เจาะลึกวิธีจัดการกับข้อมูลสารพัดได้อย่างให้จุใจ พร้อมนำไปคำนวณหรือใช้กับ Access, Power BI, หรือแอปอื่นได้ทันที
https://vimeo.com/user/113132977/folder/2133108?isPrivate=false
https://vimeo.com/user/113132977/folder/2167672?isPrivate=false

Download ตัวอย่าง
https://www.excelexperttraining.com/online/download/data.zip

+++++++++++++++++++++++++++++++
เคล็ดการสร้างกราฟให้เห็นปุ๊บ-เข้าใจปั๊บ + ปรับแต่ง User Interfaces ให้ดูดี
    
เคล็ดลับการสร้างกราฟและปรับแต่งหน้าตาตารางให้ดูเด่น ใช้งานง่าย เพื่อใช้นำเสนอผลงาน ช่วยในการวางแผนและตัดสินใจได้อย่างยืดหยุ่น
https://vimeo.com/user/113132977/folder/2222911?isPrivate=false
https://vimeo.com/user/113132977/folder/2254932?isPrivate=false
https://vimeo.com/user/113132977/folder/2235684?isPrivate=false

Download ตัวอย่าง
https://www.excelexperttraining.com/online/download/chartui.zip   

+++++++++++++++++++++++++++++++
เคล็ดการเพิ่มผลงาน ลดความซับซ้อนของงานด้วย Excel VBA +Macro
    
ใช้เคล็ดลับพิเศษที่ไม่เหมือนใคร ทำให้ VBA ที่ว่ายาก เป็นขั้นสูง กลายเป็นเรื่องง่ายจนนึกไม่ถึง เพื่อใช้จัดการข้อมูลอัตโนมัติ
https://vimeo.com/user/113132977/folder/2951051?isPrivate=false
https://vimeo.com/user/113132977/folder/2921584?isPrivate=false

Download ตัวอย่าง
https://www.excelexperttraining.com/online/download/vba.zip   

+++++++++++++++++++++++++++++++
ประยุกต์ใช้ Excel ในงานหายอดคงเหลือและสร้าง Invoice
    
เรียนรู้แบบฝึกปฏิบัติ เพื่อสร้างตารางคำนวณยอดคงเหลือทีละขั้น ต่อจากนั้นจะเรียนรู้วิธีการนำข้อมูลไปสร้าง Invoice
https://vimeo.com/user/113132977/folder/7451123?isPrivate=false

Download ตัวอย่าง
https://www.excelexperttraining.com/online/download/ProductSummaryInvoice.zip
   
+++++++++++++++++++++++++++++++
ดูดวงให้สนุกด้วย Excel
    
เลิกท่อง เลิกจำ ไม่ต้องใช้ปฏิทิน เบาสมองขึ้นเยอะ ด้วยโปรแกรม 4ZSuriya บน Excel โหราศาสตร์ไทยง่ายขึ้น กลายเป็นของสนุก
https://vimeo.com/user/113132977/folder/2025323?isPrivate=false

Download ตัวอย่าง
https://www.excelexperttraining.com/download/test4zr.xlsb

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

What's New in Excel
https://vimeo.com/user/113132977/folder/15774454?isPrivate=false

Excel Expert Guide รวมสูตรที่ใช้เป็นประจำ
https://vimeo.com/user/113132977/folder/2001435?isPrivate=false
https://www.ExcelExpertTraining.com/download/ExpertGuide.pdf

Excel Baby Steps
https://vimeo.com/user/113132977/folder/3240213?isPrivate=false

Date and Time วิธีใช้วันที่และเวลา
https://vimeo.com/user/113132977/folder/4261828?isPrivate=false

Excel Expert Training On TV
https://vimeo.com/user/113132977/folder/4882167?isPrivate=false

Excel System Development
https://vimeo.com/user/113132977/folder/6338907?isPrivate=false

Excel Expert Training at SCG
https://vimeo.com/user/113132977/folder/3159484?isPrivate=false
https://vimeo.com/user/113132977/folder/3159800?isPrivate=false
https://vimeo.com/user/113132977/folder/3159913?isPrivate=false
https://vimeo.com/user/113132977/folder/3159932?isPrivate=false
https://vimeo.com/user/113132977/folder/3159952?isPrivate=false

Download ตัวอย่าง
https://drive.google.com/file/d/1a0M-FVjFrnRxQPcvNN6N9-Qqjb3Wjk2Y/view?usp=sharing

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

 

ตั้งแต่ 13 มิถุนายน 2024 จนถึงวันนี้มีผู้สนใจสมัครเรียนออนไลน์ฟรี 1 ปีที่เว็บ XLSiam.com รวม 2,231 คน
.
วันนี้เปิดให้เรียนฟรีได้ที่เว็บ Vimeo.com ซึ่งเป็นเว็บต้นทางที่ผมใช้เก็บคลิปวิดีโอทั้งหมดไว้ จะได้เข้าเรียนได้อย่างอิสระ ไม่ต้องลงทะเบียน ฟรีตลอดไป ... ตราบเท่าที่ผมยังใช้บริการของ Vimeo.com อยู่นะครับ
.
ลิงก์ที่แจกคราวนี้มีมากกว่าที่ทำไว้ใน XLSiam.com เรียกว่าเป็นคลิปแทบทั้งหมดทีเดียว จะแตกต่างจากการเรียนใน XLSiam.com อยู่บ้างตรงที่ใน XLSiam.com นั้นมีระบบช่วยในการจำบทเรียนว่าเรียนบทไหนไปแล้วบ้างกับมีระบบ search ค้นหาเรื่องที่อยากเรียน โดยต้องสมัครลงทะเบียน เมื่อครบ 1 ปีแล้วก็ต้องสมัครใหม่ นอกจากนั้นผู้เข้าเรียนจากบางประเทศจะใช้ XLSiam.com ไม่ได้เพราะผมตั้งระบบป้องกันพวก hacker เอาไว้
.
ส่วนการเรียนในเว็บ Vimeo.com นี้ อิสระทุกอย่างครับ โดยต้องใช้ลิงก์ที่ผมรวบรวมมาให้จึงจะเข้าดูคลิปได้

 ด้านล่างนี้เป็นความเห็นจากผู้เข้าเรียนออนไลน์ 

 

    

เลิกใช้ Filter บนเมนูที่ไม่ได้บอกว่ากรองอะไรอยู่ ... หันมาใช้แบบนี้แทนดีกว่า

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

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

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

=FILTER( BudgetTable, ( COUNTIF( ITEMChoice, ITEM )>=1 ) + (COUNTA( ITEMChoice )=0) ) 

BudgetTable เป็นชื่อตารางข้อมูลด้านซ้ายทั้งหมด
ITEMChoice เป็นตารางในกรอบสีเขียว มีไว้ใส่ชื่อ Item ที่อยากให้แสดง
Item เป็นชื่อตารางใน Column Item ในตารางด้านซ้าย

COUNTIF( ITEMChoice, ITEM )>=1 เป็นเงื่อนไขที่ใช้กรองตามที่ใส่ไว้ในกรอบสีเขียว

(COUNTA( ITEMChoice )=0 เป็นเงื่อนไขให้นับว่าถ้าไม่ได้กรอกอะไรไว้เลยให้แสดงรายการทั้งหมด

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

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

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

สำหรับคนที่ชอบใช้ PivotTable ให้จัดลำดับการสร้างใหม่โดยให้สร้างจากตารางที่ได้จากสูตร Filter นี้ เวลาใช้ Slicer จะได้มีตัวเลือกตามที่กรองมาก่อนแล้ว โดยปรับสูตรใหม่ให้มีหัวตารางติดมาด้วยเพื่อใช้เป็น Field Name ใน Pivot

=VSTACK( B2:G2,
FILTER(BudgetTable,
(COUNTIF(ITEMChoice,ITEM)>=1)+(COUNTA(ITEMChoice)=0)) )

 

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

 

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

Copilot แนะนำตามนี้

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

🔹 ข้อดีของสูตร FILTER เทียบกับเมนู Filter

✅ เห็นเงื่อนไขชัดเจน – สูตรระบุเกณฑ์การกรองไว้ในเซลล์ ทำให้เห็นว่าเรากรองข้อมูลตามเงื่อนไขอะไร
✅ อัปเดตอัตโนมัติ – ถ้าข้อมูลเปลี่ยนแปลง หรือมีการเพิ่มแถว สูตร FILTER จะอัปเดตโดยอัตโนมัติ
✅ คำนวณต่อได้ง่าย – สามารถใช้ผลลัพธ์จาก FILTER ไปประมวลผลต่อในสูตรอื่น เช่น SUM, AVERAGE
✅ ไม่มีผลกระทบกับข้อมูลต้นฉบับ – เมนู Filter อาจทำให้ข้อมูลบางส่วนถูกซ่อนไปชั่วคราว แต่ FILTER สร้างตารางข้อมูลใหม่ที่แสดงเฉพาะข้อมูลที่ตรงเงื่อนไข

🔹 ตัวอย่างการใช้ FILTER แทนเมนู Filter

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

=FILTER(A2:E100, B2:B100="Sales")

• กรองเฉพาะข้อมูลที่อยู่ในคอลัมน์ B2:B100 ที่เป็น "Sales"
• แสดงผลลัพธ์ออกมาในช่วงที่กำหนด
• ข้อมูลอัปเดตอัตโนมัติเมื่อช่วงข้อมูลต้นฉบับเปลี่ยน

---

💡 แนวทางที่ดีขึ้น:
หากต้องการให้ตารางที่ได้จาก FILTER สามารถนำไปใช้กับ PivotTable หรือ Slicer ได้ ลองใช้ VSTACK รวมหัวตาราง เช่น:

=VSTACK(A1:E1, FILTER(A2:E100, B2:B100="Sales"))

แบบนี้จะได้ตารางที่มีหัวข้อครบถ้วน สามารถใช้ต่อกับ PivotTable ได้ทันที!

 

ดูยังไงว่าใช้ของใหม่ๆใน Excel 365 แล้วได้ประโยชน์เต็มที่

แฟ้มที่สร้างขึ้นต้องสามารถทำงานแบบ Dynamic โดยไม่ต้องพึ่ง PivotTable, VBA, Power Query อีกต่อไป

ลักษณะการใช้งานแบบ Dynamic เป็นยังไง

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

2. พื้นที่ตารางที่สร้างสูตรไว้จะขยายขอบเขตตารางให้เองเพื่อรับกับจำนวนรายการที่เพิ่มขึ้น

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

4. ลดจำนวนตาราง จำนวนชีท จำนวนแฟ้ม ไม่จำเป็นต้องมีหลายชีท หลายแฟ้มที่มีหน้าตาเหมือนกัน แต่ต่างกันแค่ตัวเลข

5. ผู้ใช้แฟ้มสามารถใช้งานได้ง่าย เมื่อสั่งให้ Excel ทำงานซ้ำสามารถทำได้ด้วยตนเอง โดยไม่ต้องพึ่งพาการใช้เมนูสั่งงานใหม่หรือใช้ VBA

Excel เพิ่มความสามารถในการทำงานแบบ Dynamic ตั้งแต่กันยายน 2018 โดยเริ่มใช้สูตรที่ทำงานแบบ Dynamic Array จากนั้นก็ทะยอยปรับปรุง Excel ให้มีสูตรที่ทำงานแบบ Dynamic เพิ่มขึ้น ซึ่งเป็นไปได้ว่าในไม่ช้าจะมีสูตรที่สามารถทำงานแบบ Power Query ได้อีกด้วย

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

หรือไปที่  https://techcommunity.microsoft.com/category/microsoft365/blog/excelblog?wt.mc_id=M365-MVP-4000499

 

ที่มาของภาพ https://exceljet.net/new-excel-functions

Copilot แนะนำตามนี้
.
ถ้าต้องการให้แน่ใจว่าใช้ฟีเจอร์ใหม่ของ **Excel 365** ได้เต็มประสิทธิภาพ ลองพิจารณาแนวทางเหล่านี้:
.
### 1. **ติดตามฟีเจอร์ใหม่**
Microsoft อัปเดต Excel 365 อย่างต่อเนื่อง ฟีเจอร์ล่าสุดที่น่าสนใจ เช่น:
- **Dynamic Arrays** ที่ช่วยให้สูตรสามารถขยายผลลัพธ์ได้อัตโนมัติ
- **XLOOKUP** ที่มาแทน VLOOKUP และ INDEX-MATCH ให้ใช้งานง่ายขึ้น
- **GROUPBY** ที่ช่วยจัดกลุ่มข้อมูลแบบไดนามิก
- **Copilot ใน Excel** ที่ช่วยวิเคราะห์ข้อมูลและจัดระเบียบข้อมูลอัตโนมัติ
.
### 2. **ทดลองใช้ฟีเจอร์ใหม่กับงานจริง**
ลองนำฟีเจอร์ใหม่มาใช้กับงานที่ทำเป็นประจำ เช่น:
- ใช้ **FILTER** แทน AutoFilter เพื่อให้ข้อมูลอัปเดตตามเงื่อนไขแบบไดนามิก
- ใช้ **SEQUENCE** สร้างลำดับตัวเลขโดยไม่ต้องพึ่ง VBA
- ใช้ **CHOOSEROWS** ดึงข้อมูลเฉพาะแถวที่ต้องการจากชุดข้อมูลใหญ่
.
### 3. **เปรียบเทียบกับวิธีเดิม**
ลองเปรียบเทียบการใช้ฟีเจอร์ใหม่กับวิธีเดิม เช่น:
- ใช้ **GROUPBY** แทน PivotTable แล้วดูว่าการวิเคราะห์ข้อมูลเร็วขึ้นหรือไม่
- ใช้ **Power Pivot** เพื่อดูว่าการจัดการข้อมูลขนาดใหญ่มีประสิทธิภาพขึ้นหรือไม่
.
### 4. **ติดตามแหล่งข้อมูล**
https://support.microsoft.com/th-th/office/มีอะไรใหม่ใน-excel-2024-สําหรับ-windows-และ-mac-faee26b6-ad74-40a8-9304-aa6db716553f
+++++++++++++++++++++++++++++++
.
แนวคิดการทำงานแบบ **Dynamic** ใน Excel 365 เป็นการเปลี่ยนแปลงครั้งใหญ่ที่ช่วยให้การจัดการข้อมูลมีความยืดหยุ่นและลดขั้นตอนที่ยุ่งยากลงอย่างมาก โดยเฉพาะเมื่อใช้ **Dynamic Arrays** ซึ่งเปิดตัวตั้งแต่ปี 2018 และได้รับการพัฒนาอย่างต่อเนื่อง
.
### **ลักษณะการทำงานแบบ Dynamic**
1. **ไม่ต้อง Refresh หรือแก้ไขสูตร** – เมื่อข้อมูลเพิ่มขึ้น ตารางจะขยายอัตโนมัติ
2. **ลดการใช้สูตรซ้อนกันหลายชั้น** – ไม่ต้องพึ่ง **Helper Columns** หรือสูตรที่ซับซ้อน
3. **ลดจำนวนชีทและแฟ้ม** – ไม่ต้องสร้างชีทซ้ำๆ เพียงเพื่อแสดงข้อมูลที่แตกต่างกัน
4. **ใช้งานง่ายขึ้น** – ผู้ใช้สามารถสั่งให้ Excel ทำงานซ้ำได้โดยไม่ต้องใช้ VBA หรือ Power Query
.
### **ฟีเจอร์ที่ช่วยให้ Excel ทำงานแบบ Dynamic**
- **FILTER** – ดึงข้อมูลตามเงื่อนไขโดยไม่ต้องใช้ AutoFilter
- **SEQUENCE** – สร้างลำดับตัวเลขอัตโนมัติ
- **XLOOKUP** – แทนที่ VLOOKUP และ INDEX-MATCH ให้ใช้งานง่ายขึ้น
- **CHOOSEROWS** – เลือกแถวที่ต้องการจากชุดข้อมูลขนาดใหญ่
- **GROUPBY** – จัดกลุ่มข้อมูลแบบไดนามิกโดยไม่ต้องใช้ PivotTable
.
### **ติดตามการพัฒนา Excel 365**
Microsoft กำลังพัฒนา Excel ให้รองรับการทำงานแบบ Dynamic มากขึ้น และมีแนวโน้มว่าในอนาคตสูตรบางตัวอาจสามารถทำงานแบบเดียวกับ **Power Query** ได้โดยตรง ซึ่งจะช่วยให้การจัดการข้อมูลมีประสิทธิภาพมากขึ้น
.
- [ข้อมูลจาก Microsoft Support](https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531)

 

 

 

 

 

ทำไมสูตร FILTER มักจะเป็นสูตรที่คิดถึงแรกสุดเมื่อใช้ Excel 365

Copilot ตอบ :
.
สูตร `FILTER` ใน Excel 365 เป็นหนึ่งในฟังก์ชันที่ได้รับความนิยมมากที่สุดสำหรับการจัดการข้อมูลแบบไดนามิก เพราะมันช่วยให้สามารถกรองข้อมูลตามเงื่อนไขได้อย่างง่ายดายและยืดหยุ่น โดยไม่ต้องใช้ตัวกรองแบบตาราง (`Table Filters`) หรือ `Advanced Filter` แบบเดิมที่ต้องตั้งค่าใหม่ทุกครั้งที่มีการเปลี่ยนแปลงข้อมูล
.
เหตุผลที่ `FILTER` มักจะเป็นสูตรที่คิดถึงแรกสุดเมื่อใช้ Excel 365 ได้แก่:

1. **ความยืดหยุ่น** – สามารถดึงข้อมูลตามเงื่อนไขที่กำหนดได้แบบไดนามิก โดยไม่ต้องปรับช่วงข้อมูลเอง
.
2. **ทำงานร่วมกับสูตรอื่นได้ดี** – สามารถใช้ร่วมกับ `SORT`, `UNIQUE`, หรือแม้แต่ `SEQUENCE` เพื่อสร้างชุดข้อมูลแบบไดนามิก
.
3. **แทนการใช้ PivotTable ในบางกรณี** – เมื่อไม่ต้องการตั้งค่า PivotTable แต่ต้องการดึงข้อมูลเฉพาะส่วนโดยอัตโนมัติ
.
4. **ลดการใช้ VBA** – ช่วยลดความจำเป็นในการเขียนโค้ด VBA เพื่อทำการกรองข้อมูลแบบอัตโนมัติ
.
ถ้าคุณเคยใช้ `FILTER` กับสูตรอื่น ๆ เช่น `CHOOSEROWS` หรือ `XLOOKUP` ในบริบทของการจัดการข้อมูลที่มีขนาดใหญ่ ก็น่าจะเห็นภาพชัดขึ้นว่า Excel 365 ได้เปลี่ยนแนวทางการทำงานกับข้อมูลไปอย่างมาก!
. 

เมื่อมีการเปลี่ยนแปลงข้อมูลในแฟ้ม Excel อาจต้องดำเนินการหลายอย่างเพื่อให้ข้อมูลถูกต้องและเป็นปัจจุบัน

สิ่งที่อาจต้องทำใหม่เมื่อข้อมูลเปลี่ยนแปลง

1. รีเฟรชการคำนวณ

   • Excel โดยปกติจะคำนวณสูตรใหม่อัตโนมัติ แต่ถ้าเป็นการตั้งค่า Manual Calculation ต้องกด F9 เพื่อรีเฟรช

2. อัปเดตช่วงข้อมูล

   • ถ้าข้อมูลใหม่เพิ่มขึ้น อาจต้องปรับช่วงข้อมูลในสูตรที่อ้างอิง เช่น VLOOKUP, INDEX, หรือ MATCH
   • แต่ถ้าใช้ สูตรแบบ Dynamic เช่น FILTER, SORT, UNIQUE, หรือ CHOOSEROWS ข้อมูลจะอัปเดตอัตโนมัติ

3. ตรวจสอบ PivotTable

   • ถ้ามีการเพิ่มข้อมูลในแหล่งข้อมูล ต้องกด Refresh เพื่อให้ PivotTable ดึงข้อมูลล่าสุด

4. อัปเดตแผนภูมิ (Charts)

   • ถ้าช่วงข้อมูลเปลี่ยน อาจต้องปรับช่วงข้อมูลของกราฟใหม่ หรือใช้ Table หรือ Dynamic Named Ranges เพื่อให้กราฟอัปเดตอัตโนมัติ

5. ตรวจสอบเงื่อนไขการกรองข้อมูล

   • ถ้าใช้ AutoFilter อาจต้องตั้งค่ากรองใหม่ แต่ถ้าใช้ FILTER ข้อมูลจะอัปเดตตามเงื่อนไขเอง

6. ตรวจสอบการเชื่อมโยงระหว่างไฟล์ (External Links)

   • ถ้าข้อมูลมาจากแหล่งภายนอก อาจต้องอัปเดตลิงก์ (Edit Links) หรือใช้ Power Query เพื่อดึงข้อมูลใหม่

7. ตรวจสอบ Power Query และ Power Pivot

   • ถ้าใช้ Power Query อาจต้องกด Refresh All เพื่อให้ข้อมูลในตารางเชื่อมโยงอัปเดต
   • ถ้าใช้ Power Pivot อาจต้องอัปเดตโมเดลข้อมูล

8. ตรวจสอบการจัดรูปแบบตามเงื่อนไข (Conditional Formatting)

   • ถ้ามีการเพิ่มข้อมูล อาจต้องขยายช่วงของเงื่อนไขการจัดรูปแบบ

หากคุณกำลังใช้ Excel 365 ที่มี Dynamic Arrays และสูตรเช่น FILTER หรือ SEQUENCE คุณจะสังเกตว่าหลายขั้นตอนเหล่านี้สามารถลดลงไปได้มาก เพราะข้อมูลจะปรับเปลี่ยนตามเงื่อนไขโดยอัตโนมัติ  

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

วิธีที่ FILTER ช่วยทำให้ Slicer ใช้งานง่ายขึ้น

1. ดึงข้อมูลเฉพาะตาม Slicer Selection

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

2. ลดความจำเป็นในการสร้างหลาย PivotTables

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

3. ทำงานร่วมกับ Dynamic Arrays

   • ข้อมูลที่ผ่าน FILTER จะอัปเดตอัตโนมัติเมื่อมีการเปลี่ยนค่าใน Slicer
   • ทำให้การนำเสนอข้อมูลดูเป็นระบบมากขึ้น โดยไม่ต้องกด Refresh เหมือนใน PivotTable

ตัวอย่างการใช้งาน

สมมติว่าคุณมีตารางข้อมูล SalesData และต้องการกรองข้อมูลตาม ประเภทสินค้า ที่เลือกใน Slicer

คุณสามารถใช้ FILTER ดึงข้อมูลที่ตรงกับค่าที่เลือก เช่น:

=FILTER(SalesData, SalesData[Category]=SelectedCategory)

โดยที่ SelectedCategory เป็นเซลล์ที่รับค่าจาก Slicer

เพิ่มเติม

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

 

++++++++++++++++++++++++++++
.
ผมให้ข้อสังเกตจากคำตอบ Copilot
.
1. สูตร Filter ช่วยทำให้ไม่ต้องใช้ตัวกรองแบบตาราง (`Table Filters`) หรือ `Advanced Filter` แบบเดิมที่ต้องตั้งค่าใหม่ทุกครั้งที่มีการเปลี่ยนแปลงข้อมูล
.
2. สูตรรุ่นใหม่ใน Excel 365 ได้เปลี่ยนแนวทางการทำงานกับข้อมูลไปอย่างมาก

.

 

Power BI สู้ Excel ไม่ได้ตอนไหนบ้าง

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

ที่น่าห่วงอย่างมากคือการหาคนใหม่มาใช้ Power BI แทนคนเก่าครับ และยากที่จะแกะว่าคนเก่าทำอะไรไว้บ้าง

 

Power BI สู้ Excel ไม่ได้ตอนไหนบ้าง Copilot ตอบตามนี้ครับ

 

Power BI มีจุดแข็งมากในการวิเคราะห์ข้อมูลขนาดใหญ่, การสร้าง Dashboard ที่มีความ Interactive และการเชื่อมต่อข้อมูลจากหลายแหล่งแบบอัตโนมัติ แต่ก็มีบางด้านที่ Excel ยังเหนือกว่า เช่น:

 

- การจัดการข้อมูลแบบละเอียด – Excel ให้ความยืดหยุ่นในการจัดการเซลล์, แก้ไขข้อมูลแบบ Manual, คำนวณสูตรต่าง ๆ ได้โดยตรง ซึ่ง Power BI เน้นการนำเสนอข้อมูลมากกว่า

 

- การสร้างสูตรและการคำนวณขั้นสูง – Excel มีฟังก์ชันมากมายที่ช่วยให้คุณสามารถทำการคำนวณได้อย่างอิสระ เช่น การใช้ Dynamic Arrays, Power Pivot และ DAX ขณะที่ Power BI เน้นไปที่การใช้ DAX ใน Data Model

 

- การวิเคราะห์แบบ Ad-hoc – Excel เหมาะกับการวิเคราะห์ข้อมูลแบบเจาะจงที่ต้องการความยืดหยุ่นสูง สามารถสร้าง PivotTable, Data Table หรือแม้แต่ใช้ Solver เพื่อทำการวิเคราะห์ที่ซับซ้อนได้ทันที

 

- การแชร์และการทำงานร่วมกัน – แม้ว่าทั้ง Power BI และ Excel สามารถแชร์ข้อมูลผ่าน Cloud ได้ แต่ Excel ยังมีความสะดวกในการแชร์ไฟล์ให้ผู้ใช้แก้ไขร่วมกันได้ง่ายผ่าน OneDrive หรือ SharePoint โดยตรง ขณะที่ Power BI ต้องพึ่งพาการกำหนดสิทธิ์และการเผยแพร่ผ่าน Power BI Service

 

- เหมาะกับการทำงานทั่วไปในองค์กร – Excel เป็นเครื่องมือพื้นฐานที่ผู้ใช้ทั่วไปสามารถใช้งานได้โดยไม่ต้องมีการ Training มากนัก ขณะที่ Power BI ต้องมีความเข้าใจเกี่ยวกับ Data Modeling และ DAX ในระดับหนึ่งจึงจะใช้งานได้อย่างมีประสิทธิภาพ

 

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

 

ผมแนะนำตามนี้

 

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

 

Copilot ตอบเพิ่มเติม

 

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

 

ยิ่งถ้าต้องสร้าง Measures หรือ Calculated Columns ที่ซับซ้อน การ Debug สูตร DAX ก็ไม่ใช่เรื่องง่าย เพราะมันไม่ได้แสดงผลลัพธ์ให้เห็นทีละขั้นตอนแบบ Excel แต่ต้องพึ่ง Performance Analyzer หรือใช้ตัวแปรเพื่อแยกการคำนวณเป็นขั้นตอน

 

มาชมให้เห็นกับตาว่า ทำไมสูตร PivoyBY ของ Excel 365 จึงเหนือกว่าการใช้ PivotTable

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

สูตรในเซลล์ M4
=PIVOTBY( MONTH(Date), INDIRECT(M2), Sales, SUM,,,,,, YEAR(Date)=K2 )

- MONTH(Date) ใช้สร้างหัวตารางเป็น Row Field ตามเลขเดือน
- INDIRECT(M2) ใช้สร้างหัวตารางเป็น Column Field ตามเรื่องที่ต้องการตามค่าในเซลล์สีส้ม
- Sales คือ Valuse ที่อยากให้หายอดรวมตามสูตร SUM
- YEAR(Date)=K2 เป็นสูตร Filter เพื่อหายอดเฉพาะเลขปีในเซลล์สีเขียว

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

🥰 เทียบ PivotBY กับ PivotTable
1. ไม่ต้องเสียเวลาไปสั่ง Refresh
2. ไม่ต้องสร้างหลายตารางหรือตามคนอื่นมาปรับแก้ให้
3. ไม่ต้องเสียเวลาไปสั่ง Filter หรือ Slicer แค่คลิกในเซลล์ที่ใช้ Data Validation แบบ List แทน

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

=IFERROR( INDEX( M4#, MATCH( M26:M37, CHOOSECOLS(M4#,1),), N24#), 0 )

- IFERROR(สูตร, 0) เพื่อปรับค่าเดือนที่หาไม่พบให้เป็น 0
- M4# เป็นพื้นที่ตาราง Dynamic Array ที่ได้มาจากสูตร PivotBY
- MATCH( M26:M37, CHOOSECOLS(M4#,1),) หาเลขที่ Row ตามเลขเดือน
- N24# เป็นเลข Column ของหัวตาราง PivotBY ที่ต้องการ

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

 

Dynamic Dashboard ต้องมาพร้อมกับ Dynamic Chart รูปเดียวแต่แสดงภาพเรื่องอะไรก็ได้

ในการนำเสนอผลงานต้องทำให้สามารถแสดงรายงานพร้อมภาพกราฟ "รูปเดียว" แต่เปลี่ยนให้แสดงรายละเอียดเรื่องอะไรก็ได้

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

พระเอกที่ช่วยทำงานแบบนี้ได้มาจากการนำสูตร Choose มาซ้อนลงไปในสูตร VLookup ที่สร้างไว้ในเซลล์ K22

=IFERROR( VLOOKUP( J22:J33,
CHOOSE( KeyNum,J5#,M5#,P5#,S5#,V5#),
2, 0), NA())

☝️ เดิมนั้นสูตร VLookupที่ค้นหาแบบ Exact Match มีโครงสร้างตามนี้
=VLookup( ค่าที่ใช้หา, พื้นที่ตารางที่เก็บค่า, เลขที่Column คำตอบ , 0)

ค่าที่ใช้หา J22:J33 ใช้หาเลขเดือนทั้ง 12 เดือนพร้อมกัน

พื้นที่ตาราง ให้สูตร Choose ทำหน้าที่หาพื้นที่มาใช้ตามลำดับ
👉 CHOOSE( KeyNum,J5#,M5#,P5#,S5#,V5#)
โดย KeyNum มาจากเซลล์ด้านขวาบนสุดของตาราง เป็นสูตร Match หาเลขที่ตารางมาใช้

กำหนดให้ใช้พื้นที่ตารางเรียงลำดับตามเซลล์หัวมุม J5#,M5#,P5#,S5#,V5# ซึ่งได้มาจากสูตร GroupBY ที่หาค่ามาให้แบบ Dynamic Array ดังนั้นจึงใช้เครื่องหมาย # แทนพื้นที่ตารางทั้งหมด

ถ้า KeyNum =1 จะใช้ตารางที่มาจาก J5#
ถ้า KeyNum =2 จะใช้ตารางที่มาจาก M5#
ถ้า KeyNum =3 จะใช้ตารางที่มาจาก P5#
ถ้า KeyNum =4 จะใช้ตารางที่มาจาก S5#
ถ้า KeyNum =5 จะใช้ตารางที่มาจาก V5#

Download Dynamic Chart ได้จาก
https://drive.google.com/file/d/1tiLD10kRpevmek8V3o_ut7Oudrf2hKTU/view?usp=sharing

 

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

ถ้าใช้ Excel version อื่นก่อน 365 จะใช้สูตร VLookup ผสมกับ Choose ได้เช่นกัน โดยเลือกพื้นที่ตาราง มาใส่ลงไปใน Choose ตามนี้ (สมมติมี 3 ตาราง)

 

=VLookup( ค่าที่ใช้หา,

Choose( เลขที่ตาราง, พื้นที่ตารางที่1, พื้นที่ตารางที่2, พื้นที่ตารางที่3),

เลขที่ Column คำตอบที่ต้องการ, 0)

 

ชมคลิปและแฟ้มตัวอย่างได้จาก

https://www.excelexperttraining.com/book/index.php/a-to-z/u-v-w-x-y-z/v-v-v-v-v/vlookup-with-choose

 

สูตรที่ขาดไม่ได้ในการสร้าง Dashboard ใน Excel 365 ให้ใช้สูตร GroupBy ที่มี Filter ในตัว

แทนที่จะต้องเสียเวลาไป Filter เพื่อกรองให้เหลือรายการที่ต้องการเท่านั้นก่อน ให้ใช้สูตร GroupBy กำหนดเงื่อนไขการกรองไว้ในตัวสูตร

ตัวอย่างนี้อยากได้รายงานเรื่องอะไรของปีไหนให้คลิกเลือกในเซลล์สีเขียวด้านบนใน Row 2 :
K2 เลือกปี
N2 เลือกเขต
Q2 เลือกชื่อสินค้า
T2 เลือก Outlet
W2 เลือกชื่อพนักงานขาย 

J5 =GROUPBY( MONTH(Date), Sales, SUM,0,0,,YEAR(Date)=K2)

YEAR(Date)=K2 เป็นการสั่งให้สูตร GroupBy จัดการหายอดเฉพาะในปี 2016 หรือปีใดก็ได้ตามที่เลือกไว้ในเซลล์ K2

ถ้าอยากจะกรองทั้งปีด้วยหรือเงื่อนไขอื่นด้วย ให้นำเงื่อนไขอื่นคูณต่อเข้าไป
M5 =GROUPBY( MONTH(Date), Sales, SUM,0,0,,
(YEAR(Date)=K2)*(Region=N2))

P5 =GROUPBY( MONTH(Date), Sales, SUM,0,0,,
(YEAR(Date)=K2)*(Product=Q2))

S5 =GROUPBY( MONTH(Date), Sales, SUM,0,0,,
(YEAR(Date)=K2)*(Outlet=T2))

V5 =GROUPBY( MONTH(Date), Sales, SUM,0,0,,
(YEAR(Date)=K2)*(Sales_Person=W2))

พอได้ตารางสรุปยอดที่ต้องการแล้วก็นำไปสร้างกราฟต่อ โดยใช้สูตร VLookup หายอดรายเดือน

K22 =IFERROR( VLOOKUP(J22:J33,J5#,2,0), NA() )

เดือนไหนไม่มียอดขายให้แสดง NA แทนเพื่อทำให้กราหไม่แสดงเดือนนั้น

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