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

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

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

สารบัญ

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

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

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

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

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

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

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

=โตโรว์(A1:C3)

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

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

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

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

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

=โทคอล(A1:C3)

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

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

=โทคอล(A1:C3,,จริง)

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

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

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

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

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

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

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

=ตัวเลือก(A1:B6,1,2,6)

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

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

เมื่อใช้จำนวนลบ คุณจะใช้สูตรนี้:

=ตัวเลือก(A1:B6,1,-1,-5)

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

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

=ตัวเลือก(A1:B6,1,6,2)

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

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

=ตัวเลือก(A1:D6,1,4)

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

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

=ตัวเลือก(A1:D6,4,2,1)

=ตัวเลือก(A1:D6,4,-3,-4)

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

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

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

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

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

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

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

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

=สรุป(A1:E1,3)

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

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

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

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

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

=WRAPCOLS(A1:E1,3)

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

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

อาร์เรย์ใหม่นี้ดูดีขึ้นมากเมื่อใช้คำแทนที่จะเป็นข้อผิดพลาด

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

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

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

• Range1 : ช่วงเซลล์แรกที่คุณต้องการใช้สำหรับอาร์เรย์ โดยจัดรูปแบบเป็น “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 ชีต