ใช้สูตรกับคอลัมน์ด้วย ARRAYFORMULA

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

Google ชีตมีวิธีที่ดีกว่านั้นคือ ARRAYFORMULA สูตรเดียวในเซลล์เดียวสามารถคำนวณทั้งคอลัมน์ได้พร้อมกัน และขยายตามข้อมูลโดยอัตโนมัติ

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

ดังนั้น แทนที่จะเขียน =A2*B2 ซ้ำ 100 ครั้ง คุณสามารถเขียนสูตรเดียวที่อ้างอิง A2:A และ B2:B แล้วผลลัพธ์จะเติมลงมาเอง

สมมติว่าคอลัมน์ A มีจำนวนสินค้า และคอลัมน์ B มีราคา หากต้องการคำนวณยอดรวมของแต่ละแถว ให้ใส่สูตรเดียวนี้ใน C2

สูตรจะคูณค่าแต่ละค่าใน A กับค่าที่ตรงกันใน B ลงไปจนสุด และคุณไม่ต้องแตะต้อง C3 เป็นต้นไป

สังเกตว่าใช้ A2:A แทน A2:A100 รูปแบบที่ไม่กำหนดจุดสิ้นสุดหมายถึงทุกแถวตั้งแต่แถวที่ 2 ลงไป ดังนั้นข้อมูลที่เพิ่มใหม่จะถูกรวมโดยอัตโนมัติ

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

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

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

เคล็ดลับที่มีประโยชน์คือใส่ส่วนหัวของคอลัมน์ไว้ภายในสูตรอาร์เรย์ เพื่อให้ทั้งคอลัมน์ขับเคลื่อนด้วยเซลล์เดียว ให้ใส่สูตรนี้ใน C1

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

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

สูตรนี้จะเชื่อมชื่อใน A แต่ละรายการกับนามสกุลใน B ที่ตรงกัน โดยเว้นวรรคระหว่างกลาง และทำให้ทุกแถวเสร็จพร้อมกัน

ฟังก์ชันบางอย่างจัดการอาร์เรย์ได้ด้วยตัวเอง จึงไม่จำเป็นต้องใช้ ARRAYFORMULA SUMIF, FILTER, UNIQUE และ QUERY ทำงานกับช่วงข้อมูลได้อยู่แล้ว

โดยหลักแล้วคุณจะใช้ ARRAYFORMULA เมื่อการทำงานที่ปกติใช้กับเซลล์เดียว เช่น *, & หรือ LEFT ต้องนำไปใช้กับทุกแถว

โดยปกติฟังก์ชันอย่าง LEFT, UPPER และ TRIM จะทำงานกับเซลล์เดียว เมื่ออยู่ภายใน ARRAYFORMULA ฟังก์ชันเหล่านี้จะทำงานกับทั้งช่วง

สูตรนี้จะเปลี่ยนอีเมลทุกค่าในคอลัมน์ A ให้เป็นตัวพิมพ์ใหญ่ได้ในครั้งเดียว หากไม่มีสูตรครอบ คุณจะเปลี่ยนรูปแบบได้เฉพาะเซลล์แรกเท่านั้น

คุณไม่จำเป็นต้องพิมพ์ชื่อฟังก์ชันนี้เสมอไป ใน Google ชีต ให้พิมพ์สูตรพร้อมการอ้างอิงช่วง จากนั้นกด Ctrl บวก Shift บวก Enter (บน Mac ให้กด Cmd บวก Shift บวก Enter)

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

สำหรับการทำงานแบบจับคู่ทีละรายการ ช่วงข้อมูลควรมีความสูงเท่ากัน การผสม A2:A กับ B2:B50 อาจทำให้เกิดข้อผิดพลาดหรือผลลัพธ์ไม่ตรงแถวกัน

ให้ใช้ช่วงที่ไม่กำหนดจุดสิ้นสุดทั้งสองฝั่ง (A2:A และ B2:B) หรือกำหนดจุดสิ้นสุดให้มีขนาดเท่ากัน ความสม่ำเสมอจะช่วยให้ผลลัพธ์ตรงกันทีละแถว

ทดสอบความเข้าใจ ARRAYFORMULA ของคุณ

คุณได้เรียนรู้การคำนวณทั้งคอลัมน์ด้วยสูตรเดียว:

• ARRAYFORMULA ดำเนินการกับช่วงข้อมูลทั้งหมด
• ช่วงข้อมูลแบบไม่กำหนดจุดสิ้นสุด เช่น A2:A จะรวมแถวใหม่โดยอัตโนมัติ
• ครอบด้วย IF(A2:A = "", "", ...) เพื่อข้ามเซลล์ว่าง
• ใช้ได้กับการคำนวณทางคณิตศาสตร์ การต่อข้อความ และฟังก์ชันอย่าง UPPER
• กด Ctrl/Cmd ร่วมกับ Shift และ Enter เพื่อเพิ่มส่วนครอบให้โดยอัตโนมัติ

เซลล์เดียว คอลัมน์ที่อัปเดตอยู่เสมอ