Thep Excel
ARRAYFORMULA – กระจายสูตรเดียวไปทั้งคอลัมน์แบบอัตโนมัติ
ARRAYFORMULA กระจายผลลัพธ์จากสูตรเดียวออกเป็นหลายแถวหรือหลายคอลัมน์พร้อมกัน โดยไม่ต้องลากสูตร ช่วยประหยัดเวลา ลดข้อผิดพลาด และอัปเดตอัตโนมัติเมื่อมีข้อมูลใหม่ ทำให้ชีททำงานเร็วขึ้นเพราะลดจำนวนสูตรจากหลายพันเหลือแค่หนึ่ง
=ARRAYFORMULA(array_formula)
Function Metrics
Popularity
9/10
Difficulty
6/10
Usefulness
10/10
Syntax & Arguments
=ARRAYFORMULA(array_formula)
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| array_formula | Range/Array/Formula | Yes | สูตรหรือช่วงข้อมูลที่ต้องการให้กระจายผลลัพธ์ออกหลายเซลล์ สามารถเป็น: – Range เดียว เช่น A2:A100 – การคำนวณระหว่าง Range เช่น A2:A100 * B2:B100 – ฟังก์ชันที่ทำงานกับ Range เช่น IF(A2:A100>100, “สูง”, “ต่ำ”) – ฟังก์ชันที่รวมหลาย Range เช่น A2:A100 & ” ” & B2:B100 |
How it works
คำนวณทั้งคอลัมน์ในครั้งเดียว
แทนที่จะลากสูตร =A2*B2 ลงมาหลายพันแถว ใช้ ARRAYFORMULA ครั้งเดียวแล้วให้มันคำนวณทุกแถวอัตโนมัติ ประหยัดเวลาและทำให้ชีทเร็วขึ้น
สร้างสูตรที่อัปเดตอัตโนมัติเมื่อมีข้อมูลใหม่
เมื่อมีข้อมูลแถวใหม่เพิ่มเข้ามา ARRAYFORMULA จะคำนวณให้ทันทีโดยไม่ต้องแก้ไขสูตรหรือลากสูตรใหม่
ใช้ฟังก์ชันที่ปกติทำงานทีละเซลล์กับข้อมูลหลายแถว
ฟังก์ชันอย่าง IF, VLOOKUP, LEFT, MID ปกติทำงานทีละเซลล์ แต่ ARRAYFORMULA ทำให้ใช้กับทั้ง Range ได้พร้อมกัน
ลดจำนวนสูตรเพื่อเพิ่มประสิทธิภาพชีท
ชีทที่มีสูตรหลายหมื่นเซลล์จะทำงานช้าและ lag ใช้ ARRAYFORMULA แทนจะลดจำนวนสูตรจากหลายพันเหลือแค่หนึ่ง
Examples
ตัวอย่างที่ 1: การคำนวณพื้นฐาน (คูณทั้งคอลัมน์)
ARRAYFORMULA({1;2;3;4;5} * 2)
.
ในการใช้งานจริง จะเขียนว่า =ARRAYFORMULA(A2:A6 * 2) โดย A2:A6 มีค่า 1, 2, 3, 4, 5
.
แทนที่จะเขียน =A2*2 แล้วลากลงมา 5 เซลล์ ใช้ ARRAYFORMULA ครั้งเดียวจบครับ ✨
=ARRAYFORMULA({1;2;3;4;5} * 2)
{2; 4; 6; 8; 10}
ตัวอย่างที่ 2: คำนวณระหว่าง 2 คอลัมน์ (ราคา x จำนวน)
ARRAYFORMULA(A2:A100 * B2:B100)
– คอลัมน์ A: ราคาต่อชิ้น (100, 250, 80, …)
– คอลัมน์ B: จำนวนชิ้น (5, 3, 10, …)
.
สูตรนี้คูณ A*B ทุกแถวตั้งแต่แถว 2 ถึง 100 พร้อมกันเลย ผลลัพธ์ออกมาเป็น 500, 750, 800, …
.
ที่ต้องระวังคือ Range ทั้งสองต้องมีขนาดเท่ากัน ถ้า A2:A100 กับ B2:B50 จะ Error ครับ 💡
=ARRAYFORMULA(A2:A100 * B2:B100)
คำนวณ ราคา x จำนวน ทุกแถวพร้อมกัน
ตัวอย่างที่ 3: ใช้กับ IF (เงื่อนไขทั้งคอลัมน์)
ARRAYFORMULA(IF(A2:A100>500, "เกินงบ", "ในงบ"))
.
สูตรนี้เช็คทุกแถวว่ามากกว่า 500 หรือไม่ แล้วแสดงผลทันที ไม่ต้องลาก IF ลงมาทีละเซลล์
.
ส่วนตัวผมใช้เทคนิคนี้บ่อยมากเวลาทำ Dashboard หรือรายงานแบบ Conditional เพราะแค่สูตรเดียวจัดการได้ทั้งหมดเลยครับ 😎
=ARRAYFORMULA(IF(A2:A100>500, "เกินงบ", "ในงบ"))
แสดง "เกินงบ" หรือ "ในงบ" ตามเงื่อนไขทุกแถว
ตัวอย่างที่ 4: ใช้กับ IF + LEN (ข้ามแถวว่าง)
ARRAYFORMULA(IF(LEN(A2:A100), A2:A100 * B2:B100, ""))
.
เทคนิคนี้ใช้ IF(LEN(A2:A100), …, "") เพื่อเช็คว่ามีข้อมูลก่อนคำนวณ LEN คืน 0 ถ้าว่าง (=FALSE) และคืนจำนวนตัวอักษรถ้ามีข้อมูล (=TRUE)
.
ส่วนตัวผมใช้ pattern นี้เกือบทุกครั้งที่ใช้ ARRAYFORMULA เพราะป้องกันปัญหาแถวว่างได้ดีมากครับ 💡
=ARRAYFORMULA(IF(LEN(A2:A100), A2:A100 * B2:B100, ""))
คำนวณเฉพาะแถวที่มีข้อมูล แถวว่างไม่แสดงผล
FAQs
ARRAYFORMULA ต่างจากการลากสูตรปกติอย่างไร?
การลากสูตรปกติจะสร้างสูตรแยกในแต่ละเซลล์ ถ้ามี 10,000 แถว ก็จะมี 10,000 สูตร ทำให้ชีทช้าและกิน Memory เยอะ
.
ส่วน ARRAYFORMULA ใช้สูตรเดียว คำนวณครั้งเดียว แล้วกระจายผลลัพธ์ออกมา ทำให้ชีทเร็วขึ้นและไม่ lag นอกจากนี้ยังอัปเดตอัตโนมัติเมื่อมีข้อมูลใหม่ด้วยครับ 😎
ทำไมต้องใส่ IF(LEN(…)) ก่อนใช้ ARRAYFORMULA?
เพราะ ARRAYFORMULA จะคำนวณทุกเซลล์ใน Range ที่กำหนด รวมถึงแถวว่างด้วย ทำให้ได้ผลลัพธ์ 0 หรือ Error เต็มคอลัมน์
.
ใช้ IF(LEN(A2:A100), [สูตร], “”) เพื่อเช็คว่ามีข้อมูลก่อนคำนวณ จะได้ผลลัพธ์แค่แถวที่มีข้อมูลจริงๆ ครับ 💡
ARRAYFORMULA ใช้ได้กับฟังก์ชันทุกตัวหรือเปล่า?
ใช้ได้กับฟังก์ชันที่ปกติทำงานทีละเซลล์ เช่น IF, VLOOKUP, LEFT, MID, CONCATENATE, SUMIF
.
แต่ไม่จำเป็นต้องใช้กับฟังก์ชันที่ส่งคืน Array อยู่แล้ว เช่น FILTER, SORT, QUERY, UNIQUE เพราะมันกระจายผลลัพธ์เองอัตโนมัติ
.
นอกจากนี้ บางฟังก์ชันไม่รองรับ ARRAYFORMULA เช่น SUM, SUMIFS, COUNT, COUNTA, COUNTIFS, JOIN, TEXTJOIN ครับ
ชีท lag หลังใช้ ARRAYFORMULA ทำอย่างไรดี?
มีเทคนิคหลักๆ ดังนี้ครับ:
.
1. **ระบุ Range ชัดเจน** – อย่าใช้ A:A ให้ใช้ A2:A1000 แทน จะเร็วขึ้นเยอะ
2. **ใช้ IF(LEN(…))** – ป้องกันการคำนวณแถวว่างที่ไม่จำเป็น
3. **หลีกเลี่ยง ARRAYFORMULA ซ้อนกัน** – ถ้าซับซ้อนมากให้แยกเป็น Helper Column
4. **Paste Values** – ถ้าข้อมูลไม่เปลี่ยนบ่อย ลอง Copy > Paste as Values
.
ส่วนตัวผมพบว่าข้อ 1 กับ 2 ช่วยได้มากที่สุดครับ 😊
ARRAYFORMULA กับ Dynamic Array ใน Excel 365 เหมือนกันหรือเปล่า?
หลักการเดียวกันคือใช้สูตรเดียวกระจายผลลัพธ์หลายเซลล์ แต่วิธีใช้ต่างกันครับ:
.
**Google Sheets:** ต้องห่อสูตรด้วย =ARRAYFORMULA(…) แบบ explicit
.
**Excel 365:** มี Dynamic Array อัตโนมัติ ไม่ต้องห่อ เช่น =A2:A10*2 จะ spill เองเลย
.
ถ้าคุ้นเคยกับ Excel 365 แล้วมาใช้ Google Sheets อย่าลืมห่อด้วย ARRAYFORMULA นะครับ
Resources & Related
Resources
-
Google Docs Editors Help: ARRAYFORMULA
Official Documentation -
Google Sheets ARRAYFORMULA With Examples – Coupler.io
Tutorial
Related functions
Additional Notes
ARRAYFORMULA เป็นฟังก์ชันพิเศษของ Google Sheets ที่ทำให้สูตรเดียวสามารถกระจายผลลัพธ์ออกเป็นหลายแถวหรือหลายคอลัมน์พร้อมกัน โดยไม่ต้องลากสูตรลงมาทีละเซลล์แบบเดิมอีกต่อไป
.
ที่เจ๋งคือเมื่อมีข้อมูลแถวใหม่เพิ่มเข้ามา ARRAYFORMULA จะคำนวณให้อัตโนมัติทันทีครับ ไม่ต้องมานั่งลากสูตรใหม่ ประหยัดเวลาได้เยอะมาก และยังช่วยลดจำนวนสูตรในชีทจากหลายพันสูตรเหลือแค่สูตรเดียว ทำให้ชีททำงานเร็วขึ้นไม่ lag
.
ส่วนตัวผมคิดว่า ARRAYFORMULA เป็นหนึ่งในฟังก์ชันที่ทรงพลังที่สุดใน Google Sheets ถ้าใช้เป็นจะช่วยให้ทำงานกับข้อมูลขนาดใหญ่ได้อย่างมีประสิทธิภาพ และสร้าง Dashboard หรือรายงานแบบ dynamic ได้สวยมากครับ 😎