Thep Excel
ARRAYFORMULAกระจายสูตรเดียวไปทั้งคอลัมน์แบบอัตโนมัติ
ARRAYFORMULA กระจายผลลัพธ์จากสูตรเดียวออกเป็นหลายแถวหรือหลายคอลัมน์พร้อมกัน โดยไม่ต้องลากสูตร ช่วยประหยัดเวลา ลดข้อผิดพลาด และอัปเดตอัตโนมัติเมื่อมีข้อมูลใหม่ ทำให้ชีททำงานเร็วขึ้นเพราะลดจำนวนสูตรจากหลายพันเหลือแค่หนึ่ง
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| array_formula | Range/Array/Formula | สูตรหรือช่วงข้อมูลที่ต้องการให้กระจายผลลัพธ์ออกหลายเซลล์ สามารถเป็น: – Range เดียว เช่น A2:A100 – การคำนวณระหว่าง Range เช่น A2:A100 * B2:B100 – ฟังก์ชันที่ทำงานกับ Range เช่น IF(A2:A100>100, “สูง”, “ต่ำ”) – ฟังก์ชันที่รวมหลาย Range เช่น A2:A100 & ” ” & B2:B100 |
Additional Notes
ARRAYFORMULA เป็นฟังก์ชันพิเศษของ Google Sheets ที่ทำให้สูตรเดียวสามารถกระจายผลลัพธ์ออกเป็นหลายแถวหรือหลายคอลัมน์พร้อมกัน โดยไม่ต้องลากสูตรลงมาทีละเซลล์แบบเดิมอีกต่อไป
.
ที่เจ๋งคือเมื่อมีข้อมูลแถวใหม่เพิ่มเข้ามา ARRAYFORMULA จะคำนวณให้อัตโนมัติทันทีครับ ไม่ต้องมานั่งลากสูตรใหม่ ประหยัดเวลาได้เยอะมาก และยังช่วยลดจำนวนสูตรในชีทจากหลายพันสูตรเหลือแค่สูตรเดียว ทำให้ชีททำงานเร็วขึ้นไม่ lag
.
ส่วนตัวผมคิดว่า ARRAYFORMULA เป็นหนึ่งในฟังก์ชันที่ทรงพลังที่สุดใน Google Sheets ถ้าใช้เป็นจะช่วยให้ทำงานกับข้อมูลขนาดใหญ่ได้อย่างมีประสิทธิภาพ และสร้าง Dashboard หรือรายงานแบบ dynamic ได้สวยมากครับ 😎
How it works
คำนวณทั้งคอลัมน์ในครั้งเดียว
แทนที่จะลากสูตร =A2*B2 ลงมาหลายพันแถว ใช้ ARRAYFORMULA ครั้งเดียวแล้วให้มันคำนวณทุกแถวอัตโนมัติ ประหยัดเวลาและทำให้ชีทเร็วขึ้น
สร้างสูตรที่อัปเดตอัตโนมัติเมื่อมีข้อมูลใหม่
เมื่อมีข้อมูลแถวใหม่เพิ่มเข้ามา ARRAYFORMULA จะคำนวณให้ทันทีโดยไม่ต้องแก้ไขสูตรหรือลากสูตรใหม่
ใช้ฟังก์ชันที่ปกติทำงานทีละเซลล์กับข้อมูลหลายแถว
ฟังก์ชันอย่าง IF, VLOOKUP, LEFT, MID ปกติทำงานทีละเซลล์ แต่ ARRAYFORMULA ทำให้ใช้กับทั้ง Range ได้พร้อมกัน
ลดจำนวนสูตรเพื่อเพิ่มประสิทธิภาพชีท
ชีทที่มีสูตรหลายหมื่นเซลล์จะทำงานช้าและ lag ใช้ ARRAYFORMULA แทนจะลดจำนวนสูตรจากหลายพันเหลือแค่หนึ่ง
Examples
ตัวอย่างที่ 1: การคำนวณพื้นฐาน (คูณทั้งคอลัมน์)ARRAYFORMULA({1;2;3;4;5} * 2)
สูตรนี้คูณทุกค่าใน Array ด้วย 2 พร้อมกันในครั้งเดียว ผลลัพธ์กระจายออกมา 5 แถว
ในการใช้งานจริง จะเขียนว่า =ARRAYFORMULA(A2:A6 * 2) โดย A2:A6 มีค่า 1, 2, 3, 4, 5
แทนที่จะเขียน =A2*2 แล้วลากลงมา 5 เซลล์ ใช้ ARRAYFORMULA ครั้งเดียวจบครับ ✨
ตัวอย่างที่ 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 ครับ 💡
ตัวอย่างที่ 3: ใช้กับ IF (เงื่อนไขทั้งคอลัมน์)ARRAYFORMULA(IF(A2:A100>500, "เกินงบ", "ในงบ"))
สมมติ A2:A100 เป็นยอดค่าใช้จ่าย
สูตรนี้เช็คทุกแถวว่ามากกว่า 500 หรือไม่ แล้วแสดงผลทันที ไม่ต้องลาก IF ลงมาทีละเซลล์
ส่วนตัวผมใช้เทคนิคนี้บ่อยมากเวลาทำ Dashboard หรือรายงานแบบ Conditional เพราะแค่สูตรเดียวจัดการได้ทั้งหมดเลยครับ 😎
ตัวอย่างที่ 4: ใช้กับ IF + LEN (ข้ามแถวว่าง)ARRAYFORMULA(IF(LEN(A2:A100), A2:A100 * B2:B100, ""))
ปัญหาของ ARRAYFORMULA คือมันจะคำนวณทุกแถวใน Range แม้แถวที่ว่างด้วย ทำให้ได้ 0 หรือ Error เต็มไปหมด
เทคนิคนี้ใช้ IF(LEN(A2:A100), …, "") เพื่อเช็คว่ามีข้อมูลก่อนคำนวณ LEN คืน 0 ถ้าว่าง (=FALSE) และคืนจำนวนตัวอักษรถ้ามีข้อมูล (=TRUE)
ส่วนตัวผมใช้ pattern นี้เกือบทุกครั้งที่ใช้ ARRAYFORMULA เพราะป้องกันปัญหาแถวว่างได้ดีมากครับ 💡
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
ฟังก์ชันที่ผู้เขียนโยงไว้กับ ARRAYFORMULA จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
QUERY ใช้ภาษา SQL-like (Google Visualization API Query Language) ในการจัดการข้อมูลจาก Range ที่กำหนด รองรับ 9 clauses ตามลำดับ: SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL, FORMAT พร้อม Aggregate Functions (SUM, COUNT, AVG, MIN, MAX) และ Scalar Functions สำหรับวันที่และข้อความ ทำให้สามารถกรอง จัดเรียง และสรุปข้อมูลได้แบบ Dynamic ด้วยสูตรเดียว เหมาะสำหรับการสร้างรายงานและ Dashboard ที่ซับซ้อน
SPARKLINE สร้างกราฟเส้น กราฟแท่ง หรือกราฟ Win/Loss ขนาดจิ๋วภายในเซลล์เดียว เหมาะสำหรับสร้าง Dashboard ที่ต้องการแสดงแนวโน้มข้อมูลหลายๆ รายการในพื้นที่จำกัด (In-cell chart)
FILTER กรองข้อมูลจาก Range แล้วแสดงเฉพาะแถว/คอลัมน์ที่ตรงเงื่อนไข รองรับหลายเงื่อนไข (AND/OR) และอัปเดตผลลัพธ์แบบ Real-time เมื่อข้อมูลต้นทางเปลี่ยน ทำให้เหมาะกับการสร้างรายงานไดนามิก
IMPORTRANGE ดึงข้อมูลจาก Google Sheets ไฟล์อื่นมาแสดงแบบ Real-time โดยใช้ URL หรือ Spreadsheet ID ข้อมูลจะ sync ตามต้นฉบับอัตโนมัติทุก 30 นาที (หรือเร็วกว่า) ต้อง Allow Access ครั้งแรกที่เชื่อมต่อระหว่างไฟล์คู่ใดๆ หลังจากนั้น Editor ทุกคนในไฟล์ปลายทางสามารถใช้ IMPORTRANGE จากไฟล์ต้นทางนั้นได้เลย ข้อมูลที่ดึงมาเป็น Read-only เหมาะสำหรับการรวมศูนย์ข้อมูล สร้าง Dashboard และแชร์ข้อมูลบางส่วนโดยไม่ต้องเปิดเผยไฟล์ต้นฉบับทั้งหมด
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่