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

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

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

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

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

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

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

อาร์เรย์: อาร์เรย์ที่คุณต้องการแปลง โดยจัดรูปแบบเป็น “A1:D4”

ละเว้น: ตามค่าเริ่มต้น จะไม่มีการละเว้นพารามิเตอร์ (0) แต่คุณสามารถใช้ 1 เพื่อละเว้นช่องว่าง 2 เพื่อละเว้นข้อผิดพลาด หรือ 3 เพื่อละเว้นช่องว่างและข้อผิดพลาด

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

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

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

=TOROW(A1:C3)

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

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

=TOROW(A1:C3,,TRUE)

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

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

=TOCOL(A1:C3)

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

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

=TOCOL(A1:C3,,TRUE)

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

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

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

ไวยากรณ์สำหรับแต่ละฟังก์ชันจะคล้ายกัน 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 นี่คือสูตร:

=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) ก่อน คุณสามารถใช้ตัวเลขบวกหรือลบก็ได้:

=ชอสคอล(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”

นับ: จำนวนเซลล์สำหรับแต่ละแถวหรือคอลัมน์

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

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

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

=WRAPROWS(A1:E1,3)

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

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

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

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

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

=WRAPCOLS(A1:E1,3)

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

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

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

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

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

ไวยากรณ์สำหรับแต่ละฟังก์ชันเหมือนกัน 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)

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

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

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

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

.

กระทู้ที่เกี่ยวข้อง: