แนวทางและตัวอย่างของสูตรอาร์เรย์

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

• สูตรอาร์เรย์หลายเซลล์

คัดลอกทั้งตารางด้านล่างและวางลงในเซลล์ A1 ในเวิร์กชีตเปล่า

พนักงานขาย	ประเภท รถ	จํานวนที่ ขายได้	ราคาต่อหน่วย	ยอดขายรวม
ชาติ	ซีดาน	5	33000
	คูเป้	4	37000
สัญญา	ซีดาน	6	24000
	คูเป้	8	21000
มโน	ซีดาน	3	29000
	คูเป้	1	31000
พัชระ	ซีดาน	9	24000
	คูเป้	5	37000
สุเชาว์	ซีดาน	6	33000
	คูเป้	8	31000
สูตร (ผลรวมทั้งหมด)			ผลรวมทั้งหมด
'=SUM(C2:C11*D2:D11)			=SUM(C2:C11*D2:D11)

1. เมื่อต้องการดูยอดขายรวมของคูเป้และซีดานสําหรับพนักงานขายแต่ละคน ให้เลือกเซลล์ E2:E11 ใส่สูตร =C2:C11*D2:D11 แล้วกด Ctrl+Shift+Enter

2. เมื่อต้องการดูผลรวมทั้งหมดของยอดขายทั้งหมด ให้เลือกเซลล์ F11 ใส่สูตร =SUM(C2:C11*D2:D11) แล้วกด Ctrl+Shift+Enter

เมื่อคุณกด Ctrl+Shift+Enter Excel จะล้อมรอบสูตรด้วยวงเล็บปีกกา ({ }) และแทรกอินสแตนซ์ของสูตรในแต่ละเซลล์ของช่วงที่เลือก ซึ่งเกิดขึ้นอย่างรวดเร็ว ดังนั้นสิ่งที่คุณเห็นในคอลัมน์ คือยอดขายรวมสําหรับรถยนต์แต่ละชนิดสําหรับพนักงานขายแต่ละคน ถ้าคุณเลือก E2 จากนั้นเลือก E3, E4 และอื่นๆ คุณจะเห็นว่าสูตรเดียวกันแสดงอยู่: {=C2:C11*D2:D11} 

• สร้างสูตรอาร์เรย์เซลล์เดียว

ในเซลล์ D13 ของเวิร์กบุ๊ก ให้พิมพ์สูตรต่อไปนี้ แล้วกด Ctrl+Shift+Enter

=SUM(C2:C11*D2:D11)

ในกรณีนี้ Excel จะคูณค่าในอาร์เรย์ (ช่วงเซลล์ C2 ถึง D11) แล้วใช้ฟังก์ชัน SUMเพื่อเพิ่มผลรวมเข้าด้วยกัน ผลลัพธ์คือยอดขายรวมทั้งหมด $1,590,000 ตัวอย่างนี้แสดงให้เห็นว่าสูตรชนิดนี้มีประสิทธิภาพมากเพียงใด ตัวอย่างเช่น สมมติว่าคุณมีแถวข้อมูล 1,000 แถว คุณสามารถหาผลรวมของข้อมูลบางส่วนหรือทั้งหมดได้โดยการสร้างสูตรอาร์เรย์ในเซลล์เดียวแทนที่จะลากสูตรลงไปตามแถว 1,000 แถว

นอกจากนี้ โปรดสังเกตว่าสูตรเซลล์เดียวในเซลล์ D13 จะเป็นอิสระจากสูตรหลายเซลล์ (สูตรในเซลล์ E2 ถึง E11) โดยสิ้นเชิง นี่คือข้อดีอีกอย่างหนึ่งของการใช้สูตรอาร์เรย์ ซึ่งก็คือความยืดหยุ่น คุณสามารถเปลี่ยนสูตรในคอลัมน์ หรือลบคอลัมน์นั้นไปพร้อมกันโดยไม่มีผลต่อสูตรใน D13

นอกจากนี้ สูตรอาร์เรย์ยังมีประโยชน์ต่างๆ ดังต่อไปนี้

• สอดคล้องกัน    ถ้าคุณคลิกเซลล์ใดๆ จากเซลล์ E2 ลงมา คุณจะเห็นสูตรเดียวกัน ความสอดคล้องกันนั้นสามารถช่วยรับรองความถูกต้องได้มากขึ้น

• ความปลอดภัย    คุณไม่สามารถเขียนทับคอมโพเนนต์ของสูตรอาร์เรย์หลายเซลล์ได้ ตัวอย่างเช่น คลิกเซลล์ E3 แล้วกด Delete คุณต้องเลือกช่วงของเซลล์ทั้งหมด (E2 ถึง E11) และเปลี่ยนสูตรสําหรับอาร์เรย์ทั้งหมด หรือปล่อยให้อาร์เรย์เป็นเช่นนั้น เพื่อเป็นมาตรการด้านความปลอดภัยเพิ่มเติม คุณต้องกด Ctrl+Shift+Enter เพื่อยืนยันการเปลี่ยนแปลงใดๆ กับสูตร

• ขนาดไฟล์ที่เล็กลง    คุณสามารถใช้สูตรอาร์เรย์เดียวแทนสูตรขั้นกลางหลายๆ สูตรได้ ตัวอย่างเช่น เวิร์กบุ๊กใช้สูตรอาร์เรย์เดียวในการคำนวณผลลัพธ์ในคอลัมน์ E ถ้าคุณใช้สูตรมาตรฐาน (เช่น =C2*D2, C3*D3, C4*D4…) คุณจะใช้สูตรที่แตกต่างกัน 11 สูตรในการคำนวณผลลัพธ์เดียวกัน

โดยทั่วไป สูตรอาร์เรย์จะใช้ไวยากรณ์ของสูตรมาตรฐาน โดยทั้งหมดจะเริ่มต้นด้วยเครื่องหมายเท่ากับ (=) และคุณสามารถใช้ฟังก์ชัน Excel ที่มีอยู่ภายในส่วนใหญ่ได้ในสูตรอาร์เรย์ของคุณ ความแตกต่างที่สําคัญคือ เมื่อใช้สูตรอาร์เรย์ ให้คุณกด Ctrl+Shift+Enter เพื่อใส่สูตรของคุณ เมื่อคุณทําเช่นนี้ Excel จะล้อมรอบสูตรอาร์เรย์ของคุณด้วยวงเล็บปีกกา ถ้าคุณพิมพ์วงเล็บปีกกาด้วยตนเอง สูตรของคุณจะถูกแปลงเป็นสตริงข้อความ และจะไม่ทํางาน

ฟังก์ชันอาร์เรย์เป็นวิธีที่มีประสิทธิภาพในการสร้างสูตรที่ซับซ้อน สูตรอาร์เรย์ =SUM(C2:C11*D2:D11) เหมือนกันดังนี้: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11)

เมื่อใดก็ตามที่คุณทำงานกับสูตรหลายเซลล์ อย่าลืมว่าให้

• เลือกช่วงของเซลล์เพื่อเก็บผลลัพธ์ของคุณ ไว้ก่อนที่คุณจะ ใส่สูตร คุณทําเช่นนี้เมื่อคุณสร้างสูตรอาร์เรย์หลายเซลล์เมื่อคุณเลือกเซลล์ E2 ถึง E11

• คุณไม่สามารถเปลี่ยนเนื้อหาของแต่ละเซลล์ในสูตรอาร์เรย์ได้ เมื่อต้องการลองทําสิ่งนี้ ให้เลือกเซลล์ E3 ในเวิร์กบุ๊ก แล้วกด Delete Excel จะแสดงข้อความที่บอกคุณว่าคุณไม่สามารถเปลี่ยนแปลงส่วนหนึ่งของอาร์เรย์ได้

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

• เมื่อต้องการลบสูตรอาร์เรย์ ให้เลือกช่วงสูตรทั้งหมด (ตัวอย่างเช่น E2:E11) แล้วกด Delete

• คุณไม่สามารถแทรกเซลล์ว่างหรือลบเซลล์จากสูตรอาร์เรย์หลายเซลล์ได้

ในบางครั้ง คุณอาจต้องขยายสูตรอาร์เรย์ เลือกเซลล์แรกในช่วงอาร์เรย์ที่มีอยู่ แล้วดําเนินการต่อจนกว่าคุณจะเลือกช่วงทั้งหมดที่คุณต้องการขยายสูตรไป กด F2 เพื่อแก้ไขสูตร แล้วกด CTRL+SHIFT+ENTER เพื่อยืนยันสูตรเมื่อคุณปรับช่วงสูตรแล้ว สิ่งสําคัญคือการเลือกช่วงทั้งหมด โดยเริ่มต้นจากเซลล์ด้านบนซ้ายในอาร์เรย์ เซลล์ด้านบนซ้ายคือเซลล์ที่ถูกแก้ไข

สูตรอาร์เรย์คือเครื่องมือชั้นยอด แต่ก็อาจมีข้อเสียอยู่บ้าง ดังนี้

• บางครั้งคุณอาจลืมกด Ctrl+Shift+Enter ซึ่งอาจเกิดขึ้นได้กับผู้ใช้ Excel ที่มีประสบการณ์มากที่สุด อย่าลืมกดแป้นพิมพ์พร้อมกันนี้ทุกครั้งที่คุณใส่หรือแก้ไขสูตรอาร์เรย์

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

• สูตรอาร์เรย์ขนาดใหญ่อาจทำให้การคำนวณช้าลง ทั้งนี้ขึ้นอยู่กับความเร็วในการประมวลผลและหน่วยความจำของคอมพิวเตอร์ของคุณ

ค่าคงที่อาร์เรย์เป็นคอมโพเนนต์ของสูตรอาร์เรย์ คุณสร้างค่าคงที่อาร์เรย์โดยการใส่รายการของข้อมูล แล้วใส่วงเล็บปีกกา ({ }) ล้อมรอบรายการด้วยตนเอง ดังนี้:

={1,2,3,4,5}

ถึงตอนนี้ คุณคงทราบว่าคุณจําเป็นต้องกด Ctrl+Shift+Enter เมื่อคุณสร้างสูตรอาร์เรย์ เนื่องจากค่าคงที่อาร์เรย์เป็นคอมโพเนนต์ของสูตรอาร์เรย์ คุณจึงใส่วงเล็บปีกกาคร่อยค่าคงที่ไว้ด้วยการพิมพ์ด้วยตนเอง จากนั้นให้คุณใช้ Ctrl+Shift+Enter เพื่อใส่สูตรทั้งหมด

ถ้าคุณแยกรายการโดยใช้เครื่องหมายจุลภาค คุณจะสร้างอาร์เรย์แนวนอน (แถว) ถ้าคุณแยกรายการโดยใช้เครื่องหมายอัฒภาค คุณจะสร้างอาร์เรย์แนวตั้ง (คอลัมน์) เมื่อต้องการสร้างอาร์เรย์แบบสองมิติ ให้คุณคั่นรายการในแต่ละแถวโดยใช้เครื่องหมายจุลภาคและคั่นแต่ละแถวโดยใช้เครื่องหมายอัฒภาค

นี่คืออาร์เรย์ในแถวเดียว: {1,2,3,4} ต่อไปนี้คืออาร์เรย์ในคอลัมน์เดียว: {1; 2; 3; 4}. และนี่คืออาร์เรย์ของสองแถวและสี่คอลัมน์: {1,2,3,4; 5,6,7,8}. ในอาร์เรย์แถวสองแถว แถวแรกคือ 1, 2, 3 และ 4 และแถวที่สองคือ 5, 6, 7 และ 8 เครื่องหมายอัฒภาคเดี่ยวจะแยกสองแถวระหว่าง 4 และ 5

เช่นเดียวกับสูตรอาร์เรย์ คุณสามารถใช้ค่าคงที่อาร์เรย์กับฟังก์ชันที่มีอยู่ภายในส่วนใหญ่ที่ Excel มีให้ ส่วนต่อไปนี้จะอธิบายวิธีการสร้างค่าคงที่แต่ละชนิด และวิธีการใช้ค่าคงที่เหล่านี้กับฟังก์ชันใน Excel

ขั้นตอนต่อไปนี้จะให้คุณฝึกสร้างค่าคงที่แนวนอน ค่าคงที่แนวตั้ง และค่าคงที่สองมิติ

สร้างค่าคงที่แนวนอน

1. ในเวิร์กชีตเปล่า ให้เลือกเซลล์ A1 ถึง E1

2. ในแถบสูตร ให้ใส่สูตรต่อไปนี้ แล้วกด Ctrl+Shift+Enter

   ={1,2,3,4,5}

   ในกรณีนี้ คุณควร พิมพ์วงเล็บปีกกาเปิดและวงเล็บปีกกาปิด ({ }) และ Excel จะเพิ่มชุดที่สองให้คุณ

   ซึ่งจะได้ผลลัพธ์ดังนี้

   

สร้างค่าคงที่แนวตั้ง

1. เลือกเซลล์ห้าเซลล์ในคอลัมน์เดียวในเวิร์กบุ๊กของคุณ

2. ในแถบสูตร ให้ใส่สูตรต่อไปนี้ แล้วกด Ctrl+Shift+Enter

   ={1;2;3;4;5}

   ซึ่งจะได้ผลลัพธ์ดังนี้

   

สร้างค่าคงที่สองมิติ

1. ในเวิร์กบุ๊กของคุณ ให้เลือกกลุ่มเซลล์โดยให้มีความกว้างสี่คอลัมน์ และความสูงสามแถว

2. ในแถบสูตร ให้ใส่สูตรต่อไปนี้ แล้วกด Ctrl+Shift+Enter

   ={1,2,3,4;5,6,7,8;9,10,11,12}

   คุณจะเห็นผลลัพธ์ที่ได้ดังนี้

   

ใช้ค่าคงที่ในสูตร

และนี่คือตัวอย่างง่ายๆ ที่ใช้ค่าคงที่

1. ให้สร้างเวิร์กชีตใหม่ในเวิร์กบุ๊กตัวอย่าง

2. ในเซลล์ A1 ให้พิมพ์ 3 แล้วพิมพ์ 4 ใน B1, พิมพ์ 5 ใน C1, พิมพ์ 6 ใน D1 และพิมพ์ 7 ใน E1

3. ในเซลล์ A3 ให้พิมพ์สูตรต่อไปนี้ แล้วกด Ctrl+Shift+Enter

   =SUM(A1:E1*{1,2,3,4,5})

   จะสังเกตเห็นว่า Excel ใส่วงเล็บปีกกาอีกชุดหนึ่งคร่อมค่าคงที่ไว้ เนื่องจากคุณใส่ค่าคงที่ดังกล่าวเป็นสูตรอาร์เรย์

   

   ค่า 85 จะปรากฏในเซลล์ A3

ส่วนต่อไปจะอธิบายวิธีการทำงานของสูตร

สูตรที่คุณใช้ประกอบด้วยส่วนต่างๆ

1. ฟังก์ชัน

2. อาร์เรย์ที่เก็บไว้

3. ตัวดำเนินการ

4. ค่าคงที่อาร์เรย์

องค์ประกอบสุดท้ายภายในวงเล็บคือค่าคงที่อาร์เรย์: {1,2,3,4,5} โปรดจําไว้ว่า Excel จะไม่ใส่วงเล็บปีกกาล้อมรอบค่าคงที่อาร์เรย์ จริงๆ แล้วคุณพิมพ์ โปรดจําไว้ว่า หลังจากที่คุณเพิ่มค่าคงที่ลงในสูตรอาร์เรย์ แล้ว ให้คุณกด Ctrl+Shift+Enter เพื่อใส่สูตร

เนื่องจาก Excel จะดําเนินการกับนิพจน์ที่อยู่ในวงเล็บก่อน องค์ประกอบสององค์ประกอบถัดไปที่เข้ามาเล่นคือค่าที่ถูกเก็บไว้ในเวิร์กบุ๊ก (A1:E1) และตัวดําเนินการ ในขั้นตอนนี้ สูตรจะคูณค่าต่างๆ ในอาร์เรย์ที่เก็บไว้ด้วยค่าที่สอดคล้องกันในค่าคงที่ ซึ่งเทียบเท่ากับ:

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

สุดท้าย ฟังก์ชัน SUM จะรวมค่าต่างๆ และผลรวม 85 จะปรากฏในเซลล์ A3

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

=SUM({3,4,5,6,7}*{1,2,3,4,5})

เมื่อต้องการลองทําสิ่งนี้ ให้คัดลอกฟังก์ชัน เลือกเซลล์ว่างในเวิร์กบุ๊กของคุณ วางสูตรลงในแถบสูตร แล้วกด Ctrl+Shift+Enter คุณจะเห็นผลลัพธ์แบบเดียวกับที่คุณทําในแบบฝึกหัดก่อนหน้านี้ที่ใช้สูตรอาร์เรย์:

=SUM(A1:E1*{1,2,3,4,5})

ค่าคงที่อาร์เรย์สามารถประกอบด้วยตัวเลข ข้อความ ค่าตรรกะ (เช่น TRUE และ FALSE) และค่าความผิดพลาด (เช่น #N/A) คุณสามารถใช้ตัวเลขในรูปแบบจํานวนเต็ม ทศนิยม และเชิงวิทยาศาสตร์ได้ ถ้าคุณใส่ข้อความ คุณจําเป็นต้องล้อมรอบข้อความด้วยเครื่องหมายอัญถูก (")

ค่าคงที่อาร์เรย์ไม่สามารถมีอาร์เรย์ สูตร หรือฟังก์ชันเพิ่มเติมได้ กล่าวคือ เครื่องหมายจุลภาคหรือเครื่องหมายอัฒภาคสามารถมีได้เฉพาะข้อความหรือตัวเลขที่คั่นด้วยเครื่องหมายจุลภาคหรือเครื่องหมายอัฒภาคเท่านั้น Excel จะแสดงข้อความเตือนเมื่อคุณใส่สูตร เช่น {1,2,A1:D4} หรือ {1,2,SUM(Q2:Z8)} นอกจากนี้ ค่าตัวเลขไม่สามารถมีเครื่องหมายเปอร์เซ็นต์ เครื่องหมายดอลลาร์ เครื่องหมายจุลภาค หรือวงเล็บได้

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

1. บนแท็บ สูตร ในกลุ่ม ชื่อที่กำหนด ให้คลิก ชื่อที่กำหนด
   กล่องโต้ตอบ กําหนดชื่อ จะปรากฏขึ้น

2. ในกล่อง ชื่อ ให้พิมพ์ ไตรมาส1

3. ในกล่อง อ้างอิงไปยัง ให้ใส่ค่าคงที่ต่อไปนี้ (โปรดอย่าลืมพิมพ์วงเล็บปีกกาเข้าไปด้วยตัวเอง)

   ={"มกราคม","กุมภาพันธ์","มีนาคม"}

   เนื้อหาของกล่องโต้ตอบควรมีลักษณะดังนี้

   

4. คลิก ตกลง แล้วเลือกแถวของเซลล์เปล่าสามเซลล์

5. พิมพ์สูตรต่อไปนี้ แล้วกด Ctrl+Shift+Enter

   =ไตรมาส1

   ซึ่งจะได้ผลลัพธ์ดังนี้

   

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

โปรดตรวจสอบปัญหาต่อไปนี้เมื่อค่าคงที่อาร์เรย์ของคุณใช้ไม่ได้

• องค์ประกอบบางอย่างอาจไม่ถูกแบ่งด้วยอักขระที่เหมาะสม ถ้าคุณละเครื่องหมายจุลภาคหรือเครื่องหมายอัฒภาค หรือถ้าคุณใส่เครื่องหมายจุลภาคผิดตําแหน่ง ค่าคงที่อาร์เรย์อาจสร้างไม่ถูกต้อง หรือคุณอาจเห็นข้อความเตือน

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

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

การคูณข้อมูลแต่ละรายการในอาร์เรย์

1. สร้างเวิร์กชีตใหม่ แล้วเลือกกลุ่มเซลล์เปล่าโดยให้มีความกว้างสี่คอลัมน์และความสูงสามแถว

2. พิมพ์สูตรต่อไปนี้ แล้วกด Ctrl+Shift+Enter

   ={1,2,3,4;5,6,7,8;9,10,11,12}*2

การยกกำลังสองข้อมูลในอาร์เรย์

1. เลือกกลุ่มเซลล์เปล่าโดยให้มีความกว้างสี่คอลัมน์ และความสูงสามแถว

2. พิมพ์สูตรอาร์เรย์ต่อไปนี้ แล้วกด Ctrl+Shift+Enter

   ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

   หรือใส่สูตรอาร์เรย์นี้ ซึ่งใช้ตัวดำเนินการยกกำลัง (^) ดังนี้

   ={1,2,3,4;5,6,7,8;9,10,11,12}^2

สับเปลี่ยนแถวมิติเดียว

1. เลือกคอลัมน์เซลล์เปล่าห้าเซลล์

2. พิมพ์สูตรต่อไปนี้ แล้วกด Ctrl+Shift+Enter

   =TRANSPOSE({1,2,3,4,5})

   ถึงแม้ว่าคุณจะใส่ค่าคงที่อาร์เรย์แนวนอนเข้าไป ฟังก์ชัน TRANSPOSE ก็จะแปลงค่าคงที่อาร์เรย์เป็นคอลัมน์

สับเปลี่ยนคอลัมน์มิติเดียว

1. เลือกแถวเซลล์เปล่าห้าเซลล์

2. ใส่สูตรต่อไปนี้ แล้วกด Ctrl+Shift+Enter

   =TRANSPOSE({1;2;3;4;5})

ถึงแม้ว่าคุณจะใส่ค่าคงที่อาร์เรย์แนวตั้งเข้าไป ฟังก์ชัน TRANSPOSE ก็จะแปลงค่าคงที่อาร์เรย์เป็นแถว

สับเปลี่ยนค่าคงที่สองมิติ

1. เลือกกลุ่มเซลล์โดยให้มีความกว้างสามคอลัมน์ และความสูงสี่แถว

2. ใส่ค่าคงที่ต่อไปนี้ แล้วกด Ctrl+Shift+Enter

   =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

   ฟังก์ชัน TRANSPOSE จะแปลงแถวแต่ละแถวให้เป็นชุดคอลัมน์

ส่วนนี้จะมีตัวอย่างสูตรอาร์เรย์พื้นฐาน

สร้างอาร์เรย์และค่าคงที่อาร์เรย์จากค่าที่มีอยู่

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

สร้างอาร์เรย์จากค่าที่มีอยู่

1. บนเวิร์กชีตใน Excel ให้เลือกเซลล์ C8:E10 และใส่สูตรนี้

   ={10,20,30;40,50,60;70,80,90}

   ตรวจสอบให้แน่ใจว่าได้พิมพ์ { (วงเล็บปีกกาเปิด) ก่อนที่คุณจะพิมพ์ 10 และ } (วงเล็บปีกกาปิด) หลังจากที่คุณพิมพ์ 90 เนื่องจากคุณกำลังสร้างอาร์เรย์ของตัวเลข

2. กด Ctrl+Shift+Enter ซึ่งจะใส่อาร์เรย์ของตัวเลขนี้ในช่วงเซลล์ C8:E10 โดยใช้สูตรอาร์เรย์ บนเวิร์กชีตของคุณ C8 ถึง E10 ควรมีลักษณะดังนี้:

   10	20	30
   40	50	60
   70	80	90

3. เลือกช่วงเซลล์ตั้งแต่ C1 ถึง E3

4. ใส่สูตรต่อไปนี้ในแถบสูตร แล้วกด Ctrl+Shift+Enter

   =C8:E10

   อาร์เรย์ 3x3 ของเซลล์จะปรากฏในเซลล์ C1 ถึง E3 ที่มีค่าเดียวกับที่คุณเห็นใน C8 ถึง E10

สร้างค่าคงที่อาร์เรย์จากค่าที่มีอยู่

1. เมื่อเลือกเซลล์ C1:C3 แล้ว ให้กด F2 เพื่อสลับไปยังโหมดแก้ไข 

2. กด F9 เพื่อแปลงการอ้างอิงเซลล์เป็นค่า Excel จะแปลงค่าต่างๆ ให้เป็นค่าคงที่อาร์เรย์ สูตรควรเป็น ={10,20,30 แล้ว 40,50,60; 70,80,90}

3. กด Ctrl+Shift+Enter เพื่อใส่ค่าคงที่อาร์เรย์เป็นสูตรอาร์เรย์

นับอักขระในช่วงของเซลล์

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

1. คัดลอกทั้งตารางนี้และวางลงในเวิร์กชีตในเซลล์ A1

   ข้อมูล
   นี่คือกลุ่มเซลล์ที่มาพร้อมกัน
   เพื่อรวมเป็นเพื่อรวมกันเป็น
   <!--_blank-->
   <!--_blank-->
   <!--_blank-->
   อักขระทั้งหมดใน A2:A6
   =SUM(LEN(A2:A6))
   เนื้อหาของเซลล์ที่ยาวที่สุด (A3)
   =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

2. เลือกเซลล์ A8 แล้วกด Ctrl+Shift+Enter เพื่อดูจํานวนอักขระทั้งหมดในเซลล์ A2:A6 (66)

3. เลือกเซลล์ A10 แล้วกด Ctrl+Shift+Enter เพื่อดูเนื้อหาของเซลล์ A2:A6 ที่ยาวที่สุด (เซลล์ A3)

ใช้สูตรต่อไปนี้ในเซลล์ A8 นับจํานวนอักขระทั้งหมด (66) ในเซลล์ A2 ถึง A6

=SUM(LEN(A2:A6))

ในกรณีนี้ ฟังก์ชัน LEN จะส่งกลับความยาวของแต่ละสตริงข้อความในแต่ละเซลล์ในช่วง จากนั้น ฟังก์ชัน SUM จะรวมค่าเหล่านั้นเข้าด้วยกันและแสดงผลลัพธ์ (66)

ค้นหาค่า n ที่น้อยที่สุดในช่วง

ตัวอย่างนี้จะแสดงวิธีการหาค่าที่น้อยที่สุดสามค่าในช่วงของเซลล์

1. ใส่ตัวเลขสุ่มในเซลล์ A1:A11

2. เลือกเซลล์ C1 ถึง C3 ชุดเซลล์นี้จะเก็บผลลัพธ์ที่ส่งกลับโดยสูตรอาร์เรย์

3. ใส่สูตรต่อไปนี้ แล้วกด Ctrl+Shift+Enter

   =SMALL(A1:A11,{1; 2; 3})

สูตรนี้ใช้ค่าคงที่อาร์เรย์เพื่อประเมินฟังก์ชัน SMALL สามครั้งและส่งกลับค่าที่น้อยที่สุด (1) ค่าที่น้อยที่สุดอันดับที่สอง (2) และสมาชิกที่น้อยที่สุดอันดับที่สาม (3) ในอาร์เรย์ที่มีอยู่ในเซลล์ A1:A10 เมื่อต้องการค้นหาค่าเพิ่มเติม ให้คุณเพิ่มอาร์กิวเมนต์เพิ่มเติมลงในค่าคงที่ คุณยังสามารถใช้ฟังก์ชันเพิ่มเติมกับสูตรนี้ เช่น SUM หรือ AVERAGE ตัวอย่างเช่น

=SUM(SMALL(A1:A10,{1,2,3})

=AVERAGE(SMALL(A1:A10,{1,2,3})

ค้นหาค่า n ที่มากที่สุดในช่วง

เมื่อต้องการค้นหาค่าที่มากที่สุดในช่วง คุณสามารถแทนที่ฟังก์ชัน SMALL ด้วยฟังก์ชัน LARGE นอกจากนี้ ตัวอย่างต่อไปนี้ใช้ฟังก์ชัน ROW และ INDIRECT

1. เลือกเซลล์ D1 ถึง D3

2. ในแถบสูตร ให้ใส่สูตรนี้ แล้วกด Ctrl+Shift+Enter

   =LARGE(A1:A10,ROW(INDIRECT("1:3")))

ณ จุดนี้ อาจช่วยให้ทราบเล็กน้อยเกี่ยวกับฟังก์ชัน ROW และ INDIRECT คุณสามารถใช้ฟังก์ชัน ROW เพื่อสร้างอาร์เรย์ของจํานวนเต็มที่ต่อเนื่องกันได้ ตัวอย่างเช่น เลือกคอลัมน์ว่างที่มีเซลล์ 10 เซลล์ในเวิร์กบุ๊กฝึกหัดของคุณ ให้ใส่สูตรอาร์เรย์นี้ แล้วกด Ctrl+Shift+Enter:

=ROW(1:10)

สูตรจะสร้างคอลัมน์ที่มีจํานวนเต็มติดกัน 10 ตัว เมื่อต้องการดูปัญหาที่อาจเกิดขึ้น ให้แทรกแถวเหนือช่วงที่มีสูตรอาร์เรย์ (ซึ่งอยู่เหนือแถวที่ 1) Excel จะปรับการอ้างอิงแถว และสูตรจะสร้างจํานวนเต็มตั้งแต่ 2 ถึง 11 เมื่อต้องการแก้ไขปัญหา ให้คุณเพิ่มฟังก์ชัน INDIRECT ลงในสูตร:

=ROW(INDIRECT("1:10"))

ฟังก์ชัน INDIRECT ใช้สตริงข้อความเป็นอาร์กิวเมนต์ (ซึ่งเป็นสาเหตุที่ช่วง 1:10 ถูกล้อมรอบด้วยเครื่องหมายอัญประกาศคู่) Excel จะไม่ปรับค่าข้อความเมื่อคุณแทรกแถวหรือย้ายสูตรอาร์เรย์ ดังนั้น ฟังก์ชัน ROW จะสร้างอาร์เรย์ของจํานวนเต็มที่คุณต้องการเสมอ

มาดูสูตรที่คุณใช้ก่อนหน้านี้ — =LARGE(A5:A14,ROW(INDIRECT("1:3"))) — เริ่มต้นจากวงเล็บด้านในและด้านนอก: ฟังก์ชัน INDIRECT จะส่งกลับชุดของค่าข้อความ ในกรณีนี้ ค่า 1 ถึง 3 ฟังก์ชัน ROW จะสร้างอาร์เรย์แบบคอลัมน์สามเซลล์ ฟังก์ชัน LARGE จะใช้ค่าในช่วงเซลล์ A5:A14 และจะถูกประเมินสามครั้ง หนึ่งครั้งสําหรับแต่ละการอ้างอิงที่ส่งกลับโดยฟังก์ชัน ROW ค่า 3200, 2700 และ 2000 จะถูกส่งกลับไปยังอาร์เรย์แบบคอลัมน์สามเซลล์ ถ้าคุณต้องการค้นหาค่าเพิ่มเติม ให้คุณเพิ่มช่วงเซลล์ที่มากกว่าลงในฟังก์ชัน INDIRECT

เช่นเดียวกับตัวอย่างก่อนหน้านี้ คุณสามารถใช้สูตรนี้กับฟังก์ชันอื่นๆ เช่น SUM และ AVERAGE

ค้นหาสตริงข้อความที่ยาวที่สุดในช่วงของเซลล์

กลับไปยังตัวอย่างสตริงข้อความก่อนหน้า ใส่สูตรต่อไปนี้ในเซลล์ว่าง แล้วกด Ctrl+Shift+Enter:

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

ข้อความ "กลุ่มของเซลล์ที่" จะปรากฏขึ้น

ลองมาดูสูตรอย่างละเอียดโดยเริ่มจากองค์ประกอบภายในและทํางานภายนอก ฟังก์ชัน LEN จะส่งกลับความยาวของรายการแต่ละรายการในช่วงเซลล์ A2:A6 ฟังก์ชัน MAX จะคํานวณค่าที่มากที่สุดระหว่างรายการเหล่านั้น ซึ่งสอดคล้องกับสตริงข้อความที่ยาวที่สุด ซึ่งอยู่ในเซลล์ A3

นี่คือสิ่งที่ซับซ้อนเล็กน้อย ฟังก์ชัน MATCH จะคํานวณออฟเซต (ตําแหน่งสัมพันธ์) ของเซลล์ที่มีสตริงข้อความที่ยาวที่สุด เมื่อต้องการทําเช่นนั้น จําเป็นต้องมีอาร์กิวเมนต์สามรายการ: ค่าการค้นหาอาร์เรย์การค้นหา และชนิดการจับคู่ ฟังก์ชัน MATCH จะค้นหาอาร์เรย์การค้นหาสําหรับค่าการค้นหาที่ระบุ ในกรณีนี้ ค่าการค้นหาคือสตริงข้อความที่ยาวที่สุด:

(MAX(LEN(A2:A6))

และสตริงดังกล่าวจะอยู่ในอาร์เรย์นี้

LEN(A2:A6)

อาร์กิวเมนต์ชนิดการจับคู่คือ 0 ชนิดการจับคู่อาจประกอบด้วยค่า 1, 0 หรือ -1 ถ้าคุณระบุ 1 ฟังก์ชัน MATCH จะส่งกลับค่าที่มากที่สุดที่น้อยกว่าหรือเท่ากับค่าการค้นหา ถ้าคุณระบุ 0 ฟังก์ชัน MATCH จะส่งกลับค่าแรกที่เท่ากับค่าการค้นหาทุกประการ ถ้าคุณระบุ -1 ฟังก์ชัน MATCH จะค้นหาค่าที่น้อยที่สุดที่มากกว่าหรือเท่ากับค่าการค้นหาที่ระบุ ถ้าคุณละชนิดที่ตรงกัน Excel จะถือว่าเป็น 1

สุดท้าย ฟังก์ชัน INDEX จะใช้อาร์กิวเมนต์เหล่านี้ คือ อาร์เรย์ และหมายเลขแถวและคอลัมน์ภายในอาร์เรย์นั้น ช่วงเซลล์ A2:A6 มีอาร์เรย์ ฟังก์ชัน MATCH มีที่อยู่เซลล์ และอาร์กิวเมนต์สุดท้าย (1) ระบุว่าค่ามาจากคอลัมน์แรกในอาร์เรย์

ส่วนนี้จะมีตัวอย่างสูตรอาร์เรย์ขั้นสูง

รวมช่วงที่มีค่าความผิดพลาด

ฟังก์ชัน SUM ใน Excel จะไม่ทํางานเมื่อคุณพยายามรวมช่วงที่มีค่าความผิดพลาด เช่น #N/A ตัวอย่างนี้แสดงวิธีการรวมค่าในช่วงที่ชื่อ ข้อมูล ที่มีข้อผิดพลาด

=SUM(IF(ISERROR(Data),"",Data))

สูตรจะสร้างอาร์เรย์ใหม่ที่มีค่าดั้งเดิมลบด้วยค่าความผิดพลาดใดๆ เริ่มต้นจากฟังก์ชันภายในและการทํางานภายนอก ฟังก์ชัน ISERROR จะค้นหาข้อผิดพลาดในช่วงของเซลล์ (ข้อมูล) ฟังก์ชัน IF จะส่งกลับค่าที่เฉพาะเจาะจง ถ้าเงื่อนไขที่คุณระบุประเมินเป็น TRUE และส่งกลับค่าอื่นถ้าประเมินเป็น FALSE ในกรณีนี้ จะส่งกลับสตริงว่าง ("") สําหรับค่าความผิดพลาดทั้งหมดเนื่องจากค่าเหล่านั้นประเมินเป็น TRUE และจะส่งกลับค่าที่เหลือจากช่วง (ข้อมูล) เนื่องจากค่าเหล่านี้ประเมินเป็น FALSE ซึ่งหมายความว่าค่าเหล่านี้ไม่มีค่าความผิดพลาด จากนั้น ฟังก์ชัน SUM จะคํานวณผลรวมสําหรับอาร์เรย์ที่กรองแล้ว

นับจำนวนค่าความผิดพลาดในช่วง

ตัวอย่างนี้จะคล้ายคลึงกับสูตรก่อนหน้านี้ แต่จะแสดงจำนวนค่าความผิดพลาดในช่วงที่ชื่อ Data แทนที่จะกรองออกไป

=SUM(IF(ISERROR(Data),1,0))

สูตรนี้จะสร้างอาร์เรย์ที่มีค่า 1 สําหรับเซลล์ที่มีข้อผิดพลาด และค่า 0 สําหรับเซลล์ที่ไม่มีข้อผิดพลาด คุณสามารถลดความซับซ้อนของสูตรและได้ผลลัพธ์เดียวกันโดยการเอาอาร์กิวเมนต์ที่สามสําหรับฟังก์ชัน IF ออก ดังนี้

=SUM(IF(ISERROR(Data),1))

ถ้าคุณไม่ระบุอาร์กิวเมนต์ ฟังก์ชัน IF จะส่งกลับค่า FALSE ถ้าเซลล์ไม่มีค่าความผิดพลาด คุณสามารถลดความซับซ้อนของสูตรได้มากขึ้น ดังนี้

=SUM(IF(ISERROR(Data)*1))

เวอร์ชันนี้ใช้งานได้ เนื่องจาก TRUE*1=1 และ FALSE*1=0

รวมค่าตามเงื่อนไข

คุณอาจต้องรวมค่าตามเงื่อนไข ตัวอย่างเช่น สูตรอาร์เรย์นี้จะรวมเฉพาะจํานวนเต็มบวกในช่วงที่ชื่อ Sales ดังนี้

=SUM(IF(Sales>0,Sales))

ฟังก์ชัน IF จะสร้างอาร์เรย์ของค่าบวกและค่าเท็จ ฟังก์ชัน SUM จะละเว้นค่าเท็จเนื่องจาก 0+0=0 ช่วงเซลล์ที่คุณใช้ในสูตรนี้อาจประกอบด้วยจํานวนแถวและคอลัมน์เท่าใดก็ได้

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

=SUM((Sales>0)*(Sales<=5)*(Sales))

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

คุณยังสามารถสร้างสูตรอาร์เรย์ที่ใช้ชนิดของเงื่อนไข OR ได้ ตัวอย่างเช่น คุณสามารถรวมค่าที่น้อยกว่า 5 และมากกว่า 15:

=SUM(IF((Sales<5)+(Sales>15),Sales))

ฟังก์ชัน IF จะหาค่าทั้งหมดที่น้อยกว่า 5 และมากกว่า 15 และส่งต่อค่าดังกล่าวไปยังฟังก์ชัน SUM

คุณไม่สามารถใช้ฟังก์ชัน AND และ OR ในสูตรอาร์เรย์ได้โดยตรง เนื่องจากฟังก์ชันเหล่านั้นส่งกลับผลลัพธ์เดียว ฟังก์ชัน TRUE หรือ FALSE และฟังก์ชันอาร์เรย์ต้องใช้อาร์เรย์ของผลลัพธ์ คุณสามารถแก้ไขปัญหาชั่วคราวได้โดยใช้ตรรกะที่แสดงในสูตรก่อนหน้า กล่าวคือ คุณดําเนินการทางคณิตศาสตร์ เช่น บวกหรือคูณกับค่าที่ตรงตามเงื่อนไข OR หรือ AND

คำนวณค่าเฉลี่ยที่ไม่รวมศูนย์

ตัวอย่างนี้จะแสดงวิธีการเอาค่าศูนย์ออกจากช่วงเมื่อคุณต้องการหาค่าเฉลี่ยของค่าในช่วงนั้น สูตรจะใช้ช่วงข้อมูลที่ชื่อ Sales ดังนี้

=AVERAGE(IF(Sales<>0,Sales))

ฟังก์ชัน IF จะสร้างอาร์เรย์ของค่าที่ไม่ใช่ 0 และส่งต่อค่าดังกล่าวไปยังฟังก์ชัน AVERAGE

นับจำนวนความแตกต่างระหว่างช่วงของเซลล์สองช่วง

สูตรอาร์เรย์นี้จะเปรียบเทียบค่าในช่วงของเซลล์สองช่วงที่ชื่อ MyData และ YourData และส่งกลับจํานวนความแตกต่างระหว่างเซลล์ทั้งสอง ถ้าเนื้อหาของสองช่วงเหมือนกัน สูตรจะส่งกลับค่า 0 เมื่อต้องการใช้สูตรนี้ ช่วงเซลล์ต้องมีขนาดเท่ากันและมีขนาดเดียวกัน (ตัวอย่างเช่น ถ้า MyData เป็นช่วง 3 แถวคูณ 5 คอลัมน์ YourData ต้องมี 3 แถวคูณ 5 คอลัมน์ด้วย):

=SUM(IF(MyData=YourData,0,1))

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

คุณสามารถลดความซับซ้อนของสูตรได้ดังนี้

=SUM(1*(MyData<>YourData))

เช่นเดียวกับสูตรที่นับค่าความผิดพลาดในช่วง สูตรนี้ใช้ได้เนื่องจาก TRUE*1=1 และ FALSE*1=0

ค้นหาตำแหน่งของค่าสูงสุดในช่วง

สูตรอาร์เรย์นี้จะแสดงหมายเลขแถวของค่าสูงสุดในช่วงคอลัมน์เดียวที่มีชื่อว่า Data

=MIN(IF(Data=MAX(Data),ROW(Data),""))

ฟังก์ชัน IF จะสร้างอาร์เรย์ใหม่ที่สอดคล้องกับช่วงที่ชื่อ Data ถ้าเซลล์ที่สอดคล้องกันมีค่ามากที่สุดในช่วง อาร์เรย์จะมีหมายเลขแถว มิฉะนั้น อาร์เรย์จะมีสตริงว่าง ("") ฟังก์ชัน MIN จะใช้อาร์เรย์ใหม่เป็นอาร์กิวเมนต์ที่สองและส่งกลับค่าที่น้อยที่สุด ซึ่งสอดคล้องกับหมายเลขแถวของค่าสูงสุดใน Data ถ้าช่วงที่ชื่อ Data มีค่ามากที่สุดที่เหมือนกัน สูตรจะส่งกลับแถวของค่าแรก

ถ้าคุณต้องการให้แสดงที่อยู่เซลล์ตามจริงของค่าสูงสุด ให้ใช้สูตรต่อไปนี้

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))