วิธีใช้สูตรอาร์เรย์ใน Google ชีต

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

ด้วยความยืดหยุ่นที่มากขึ้นในการทำงานกับอาร์เรย์และเหนือกว่าฟังก์ชัน ARRAYFORMULA พื้นฐาน มาดูวิธีใช้ฟังก์ชันอาร์เรย์เหล่านี้กับสูตรใน Google ชีต

เคล็ดลับ : ฟังก์ชันบางอย่างเหล่านี้อาจดูคุ้นเคยหากคุณใช้ Microsoft Excel ด้วย

แปลงอาร์เรย์: TOROW และ TOCOL

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

ไวยากรณ์สำหรับแต่ละฟังก์ชันจะเหมือนกัน TOROW(อาร์เรย์ เพิกเฉย สแกน) และ TOCOL(อาร์เรย์ เพิกเฉย สแกน) โดยที่อาร์กิวเมนต์แรกเท่านั้นที่จำเป็นสำหรับทั้งสอง

• Array : อาร์เรย์ที่คุณต้องการแปลงรูปแบบเป็น “A1:D4”
• ละเว้น : ตามค่าเริ่มต้น จะไม่มีพารามิเตอร์ใดถูกละเว้น (0) แต่คุณสามารถใช้ 1 เพื่อละเว้นช่องว่าง 2 เพื่อละเว้นข้อผิดพลาด หรือ 3 เพื่อละเว้นช่องว่างและข้อผิดพลาด
• Scan : อาร์กิวเมนต์นี้กำหนดวิธีการอ่านค่าในอาร์เรย์ ตามค่าเริ่มต้น ฟังก์ชันจะสแกนตามแถวหรือใช้ค่า False แต่คุณสามารถใช้ True เพื่อสแกนตามคอลัมน์ได้หากต้องการ

มาดูตัวอย่างการใช้ฟังก์ชัน TOROW และ TOCOL และสูตรของฟังก์ชันเหล่านี้กัน

ในตัวอย่างแรกนี้ เราจะใช้อาร์เรย์ A1 ถึง C3 และเปลี่ยนเป็นแถวโดยใช้อาร์กิวเมนต์เริ่มต้นที่มีสูตรนี้:

=TOROW(A1:C3)

อย่างที่คุณเห็น ตอนนี้อาร์เรย์อยู่ในแถว เนื่องจากเราใช้อาร์กิวเมนต์ การสแกน เริ่มต้น ฟังก์ชันจึงอ่านจากซ้ายไปขวา (A, D, G) ลง จากนั้นอ่านจากซ้ายไปขวาอีกครั้ง (B, E, H) จนกระทั่งเสร็จสิ้น—สแกนทีละแถว

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

=TOROW(A1:C3,,TRUE)

ตอนนี้คุณเห็นฟังก์ชันอ่านอาร์เรย์จากบนลงล่าง (A, B, C) จากบนลงล่าง (D, E, F) และบนลงล่าง (G, H, I)

ฟังก์ชัน TOCOL ทำงานในลักษณะเดียวกัน แต่แปลงอาร์เรย์เป็นคอลัมน์ ใช้ช่วงเดียวกัน A1 ถึง C3 ต่อไปนี้เป็นสูตรที่ใช้อาร์กิวเมนต์เริ่มต้น:

=TOCOL(A1:C3)

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

หากต้องการอ่านอาร์เรย์ตามคอลัมน์แทนแถว ให้ใส่ True สำหรับอาร์กิวเมนต์ การสแกน ดังนี้:

=TOCOL(A1:C3,,จริง)

ตอนนี้คุณเห็นฟังก์ชันอ่านอาร์เรย์จากบนลงล่างแทน

สร้างอาร์เรย์ใหม่จากแถวหรือคอลัมน์: CHOOSEROWS และ CHOOSECOLS

คุณอาจต้องการสร้างอาร์เรย์ใหม่จากอาร์เรย์ที่มีอยู่ วิธีนี้ช่วยให้คุณสร้างช่วงเซลล์ใหม่โดยมีค่าเฉพาะจากเซลล์อื่น สำหรับสิ่งนี้ คุณจะใช้ฟังก์ชัน CHOOSEROWS และ CHOOSECOLS ของ Google ชีต

ไวยากรณ์สำหรับแต่ละฟังก์ชันจะคล้ายกันคือ CHOOSEROWS (อาร์เรย์, row_num, row_num_opt) และ CHOOSECOLS (อาร์เรย์, col_num, col_num_opt) โดยที่ทั้งสองอาร์กิวเมนต์จำเป็นต้องมีอาร์กิวเมนต์สองตัวแรก

• Array : อาร์เรย์ที่มีอยู่ซึ่งจัดรูปแบบเป็น “A1:D4”
• Row_num หรือ Col_num : จำนวนแถวหรือคอลัมน์แรกที่คุณต้องการส่งคืน
• Row_num_opt หรือ Col_num_opt : ตัวเลขสำหรับแถวหรือคอลัมน์เพิ่มเติมที่คุณต้องการส่งคืน Google แนะนำให้คุณใช้จำนวนลบเพื่อส่งกลับแถวจากล่างขึ้นบนหรือคอลัมน์จากขวาไปซ้าย

ลองดูตัวอย่างการใช้ CHOOSEROWS และ CHOOSECOLS และสูตรของพวกเขา

ในตัวอย่างแรกนี้ เราจะใช้อาร์เรย์ A1 ถึง B6 เราต้องการคืนค่าในแถวที่ 1, 2 และ 6 นี่คือสูตร:

=CHOOSEROWS(A1:B6,1,2,6)

อย่างที่คุณเห็น เราได้รับสามแถวนั้นเพื่อสร้างอาร์เรย์ใหม่ของเรา

อีกตัวอย่างหนึ่ง เราจะใช้อาร์เรย์เดียวกัน คราวนี้เราต้องการคืนแถว 1, 2 และ 6 แต่ให้ 2 และ 6 เรียงกลับกัน คุณสามารถใช้จำนวนบวกหรือลบเพื่อรับผลลัพธ์เดียวกัน

การใช้จำนวนลบ คุณจะใช้สูตรนี้:

=CHOOSEROWS(A1:B6,1,-1,-5)

เพื่ออธิบาย 1 คือแถวแรกที่จะกลับ -1 คือแถวที่สองที่จะกลับซึ่งเป็นแถวแรกที่เริ่มต้นที่ด้านล่าง และ -5 คือแถวที่ห้าจากด้านล่าง

เมื่อใช้จำนวนบวก คุณจะใช้สูตรนี้เพื่อให้ได้ผลลัพธ์เดียวกัน:

=CHOOSEROWS(A1:B6,1,6,2)

ฟังก์ชัน CHOOSECOLS ทำงานคล้ายกัน ยกเว้นว่าคุณจะใช้เมื่อต้องการสร้างอาร์เรย์ใหม่จากคอลัมน์แทนแถว

เมื่อใช้อาร์เรย์ A1 ถึง D6 เราสามารถคืนค่าคอลัมน์ 1 (คอลัมน์ A) และ 4 (คอลัมน์ D) ด้วยสูตรนี้:

=CHOOSECOLS(A1:D6,1,4)

ตอนนี้เรามีอาร์เรย์ใหม่ที่มีเพียงสองคอลัมน์นั้น

อีกตัวอย่างหนึ่ง เราจะใช้อาร์เรย์เดียวกันที่เริ่มต้นด้วยคอลัมน์ 4 จากนั้นเราจะเพิ่มคอลัมน์ 1 และ 2 ด้วย 2 (คอลัมน์ B) ก่อน คุณสามารถใช้ตัวเลขบวกหรือลบก็ได้:

=CHOOSECOLS(A1:D6,4,2,1)

=CHOOSECOLS(A1:D6,4,-3,-4)

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

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

ตัดเพื่อสร้างอาร์เรย์ใหม่: WRAPROWS และ WRAPCOLS

หากคุณต้องการสร้างอาร์เรย์ใหม่จากอาร์เรย์ที่มีอยู่แต่รวมคอลัมน์หรือแถวด้วยค่าจำนวนหนึ่งในแต่ละค่า คุณสามารถใช้ฟังก์ชัน WRAPROWS และ WRAPCOLS

ไวยากรณ์สำหรับแต่ละฟังก์ชันเหมือนกัน WRAPROWS (range, count, pad) และ WRAPCOLS (range, count, pad) โดยที่อาร์กิวเมนต์สองตัวแรกจำเป็นสำหรับทั้งสอง

• ช่วง : ช่วงเซลล์ที่มีอยู่ที่คุณต้องการใช้สำหรับอาร์เรย์ ซึ่งจัดรูปแบบเป็น “A1:D4”
• จำนวน : จำนวนเซลล์สำหรับแต่ละแถวหรือแต่ละคอลัมน์
• Pad : คุณสามารถใช้อาร์กิวเมนต์นี้เพื่อวางข้อความหรือค่าเดียวในเซลล์ว่าง ซึ่งจะแทนที่ข้อผิดพลาด #N/A ที่คุณจะได้รับสำหรับเซลล์ว่าง รวมข้อความหรือค่าไว้ในเครื่องหมายคำพูด

มาดูตัวอย่างการใช้ฟังก์ชัน WRAPROWS และ WRAPCOLS และสูตรของฟังก์ชันเหล่านี้กัน

ในตัวอย่างแรกนี้ เราจะใช้ช่วงเซลล์ A1 ถึง E1 เราจะสร้างแถวการรวมอาร์เรย์ใหม่โดยมีค่าสามค่าในแต่ละแถว นี่คือสูตร:

=WRAPROWS(A1:E1,3)

อย่างที่คุณเห็น เรามีอาร์เรย์ใหม่พร้อมผลลัพธ์ที่ถูกต้อง มีค่าสามค่าในแต่ละแถว เนื่องจากเรามีเซลล์ว่างในอาร์เรย์ ข้อผิดพลาด #N/A จึงปรากฏขึ้น สำหรับตัวอย่างถัดไป เราจะใช้อาร์กิวเมนต์ pad เพื่อแทนที่ข้อผิดพลาดด้วยข้อความ “ไม่มี” นี่คือสูตร:

=WRAPROWS(A1:E1,3,”ไม่มี”)

ตอนนี้ เราสามารถเห็นคำแทนข้อผิดพลาดของ Google ชีต

ฟังก์ชัน WRAPCOLS ทำสิ่งเดียวกันโดยสร้างอาร์เรย์ใหม่จากช่วงเซลล์ที่มีอยู่ แต่ทำได้โดยการล้อมคอลัมน์แทนแถว

ในที่นี้ เราจะใช้อาร์เรย์เดียวกัน A1 ถึง E3 ล้อมคอลัมน์ด้วยค่าสามค่าในแต่ละคอลัมน์:

=WRAPCOLS(A1:E1,3)

เช่นเดียวกับตัวอย่าง WRAPROWS เราได้รับผลลัพธ์ที่ถูกต้องแต่ยังมีข้อผิดพลาดเนื่องจากเซลล์ว่าง ด้วยสูตรนี้ คุณสามารถใช้อาร์กิวเมนต์ แพด เพื่อเพิ่มคำว่า "ว่าง":

=WRAPCOLS(A1:E1,3,”ว่าง”)

อาร์เรย์ใหม่นี้ดูดีขึ้นมากด้วยคำแทนข้อผิดพลาด

รวมเพื่อสร้างอาร์เรย์ใหม่: HSTACK และ VSTACK

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

ไวยากรณ์สำหรับแต่ละฟังก์ชันจะเหมือนกัน HSTACK (range1, range2,…) และ VSTACK (range1, range2,…) โดยที่ต้องมีอาร์กิวเมนต์แรกเท่านั้น อย่างไรก็ตาม คุณมักจะใช้อาร์กิวเมนต์ที่สอง ซึ่งรวมช่วงอื่นเข้ากับช่วงแรก

• ช่วง 1 : ช่วงเซลล์แรกที่คุณต้องการใช้สำหรับอาร์เรย์ ซึ่งจัดรูปแบบเป็น “A1:D4”
• Range2,… : ช่วงเซลล์ที่สองที่คุณต้องการเพิ่มในช่วงเซลล์แรกเพื่อสร้างอาร์เรย์ คุณสามารถรวมช่วงเซลล์ได้มากกว่าสองช่วง

ลองดูตัวอย่างการใช้ HSTACK และ VSTACK และสูตรของมัน

ในตัวอย่างแรกนี้ เราจะรวมช่วง A1 ถึง D2 กับ A3 ถึง D4 โดยใช้สูตรนี้:

=HSTACK(A1:D2,A3:D4)

คุณสามารถดูช่วงข้อมูลของเรารวมกันเพื่อสร้างอาร์เรย์แนวนอนเดียว

ตัวอย่างเช่น ฟังก์ชัน VSTACK เรารวมสามช่วงเข้าด้วยกัน โดยใช้สูตรต่อไปนี้ เราจะใช้ช่วง A2 ถึง C4, A6 ถึง C8 และ A10 ถึง C12:

=VSTACK(A2:C4,A6:C8,A10:C12)

ตอนนี้ เรามีหนึ่งอาร์เรย์ที่มีข้อมูลทั้งหมดของเราโดยใช้สูตรในเซลล์เดียว

จัดการอาร์เรย์ได้อย่างง่ายดาย

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

สำหรับบทช่วยสอนเพิ่มเติมเช่นนี้แต่มีฟังก์ชันที่ไม่ใช่อาร์เรย์ โปรดดูวิธีใช้ฟังก์ชัน COUNTIF หรือ SUMIF ใน Google ชีต