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

ในการเป็นผู้ใช้ Excel ขั้นสูง คุณต้องทราบวิธีการใช้สูตรอาร์เรย์ ซึ่งจำเป็นสำหรับการคำนวณที่ต้องใช้สูตรนี้ บทความต่อไปนี้ยึดตามชุดคอลัมน์ผู้ใช้ Excel ขั้นสูง ซึ่งเขียนโดย Colin Wilcox และดัดแปลงมาจากบทที่ 14 และ 15 ของ Excel 2002 Formulas (แสดงเป็นภาษาอังกฤษ) ซึ่งเขียนขึ้นโดย John Walkenbach , Excel MVP ถ้าต้องการเรียนรู้เพิ่มเติมเกี่ยวกับหนังสือเล่มอื่นของ John โปรดดูที่หน้าหนังสือ ของเขา (แสดงเป็นภาษาอังกฤษ)

ในบทความนี้


เรียนรู้เพิ่มเติมเกี่ยวกับสูตรอาร์เรย์

ส่วนนี้จะนำเสนอสูตรอาร์เรย์ และอธิบายวิธีการป้อน แก้ไข ตลอดจนการแก้ปัญหา

เหตุใดจึงต้องใช้สูตรอาร์เรย์

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

  • นับจำนวนอักขระที่อยู่ในช่วงของเซลล์
  • รวมเฉพาะตัวเลขที่ตรงตามเงื่อนไขบางอย่าง เช่น ค่าต่ำสุดในช่วง หรือตัวเลขที่อยู่ระหว่างขีดจำกัดบนและขีดจำกัดล่าง
  • รวมค่า n ทุกตัวที่อยู่ในช่วงของค่า

 หมายเหตุ   คุณอาจเห็นสูตรอาร์เรย์ที่เขียนเป็น "สูตร CSE" ทั้งนี้เนื่องจากคุณต้องกด CTRL+SHIFT+ENTER เพื่อป้อนสูตรในสมุดงานของคุณ

ความรู้เบื้องต้นโดยสังเขปเกี่ยวกับอาร์เรย์และสูตรอาร์เรย์

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

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

ตัวอย่างในส่วนถัดไปจะแสดงให้คุณเห็นวิธีการสร้างสูตรอาร์เรย์หลายเซลล์และสูตรอาร์เรย์เซลล์เดียว

ต้องลองด้วยตัวเอง!

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

การสร้างสูตรอาร์เรย์หลายเซลล์

  1. เปิดสมุดงานใหม่ที่ว่างเปล่า
  2. คัดลอกข้อมูลของแผ่นงานตัวอย่าง แล้ววางข้อมูลที่คัดลอกมาลงในแผ่นงานใหม่โดยเริ่มที่เซลล์ A1

แสดงวิธีคัดลอกข้อมูลของแผ่นงานตัวอย่าง

  • สร้างสมุดงานเปล่าหรือแผ่นงานเปล่า
  • เลือกตัวอย่างในหัวข้อวิธีใช้

 หมายเหตุ   อย่าเลือกหัวแถวหรือหัวคอลัมน์

การเลือกตัวอย่างจากวิธีใช้

การเลือกตัวอย่างจากวิธีใช้
  • กด CTRL+C
  • ในแผ่นงาน ให้เลือกเซลล์ A1 แล้วกด CTRL+V

พนักงานขาย ประเภทรถ จำนวนที่ขายได้ ราคาต่อหน่วย ยอดขายทั้งหมด
Barnhill Sedan 5 2200  
  Coupe 4 1800  
Ingle Sedan 6 2300  
  Coupe 8 1700  
Jordan Sedan 3 2000  
  Coupe 1 1600  
Pica Sedan 9 2150  
  Coupe 5 1950  
Sanchez Sedan 6 2250  
  Coupe 8 2000  

  1. ใช้ปุ่ม ตัวเลือกวางรูปปุ่ม ที่อยู่ติดกันเพื่อให้ตรงกับการจัดรูปแบบปลายทาง
  2. ถ้าต้องการคูณค่าต่างๆ ในอาร์เรย์ (ช่วงเซลล์ C2 ถึง D11) ให้เลือกเซลล์ E2 ถึง E11 จากนั้นป้อนสูตรต่อไปนี้ลงในแถบสูตร

=C2:C11*D2:D11

  1. กด CTRL+SHIFT+ENTER

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


ข้อมูลตัวอย่าง


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

  1. ในเซลล์ A13 ของสมุดงาน ให้พิมพ์ Total Sales
  2. ในเซลล์ B13 ให้พิมพ์สูตรต่อไปนี้ แล้วกด CTRL+SHIFT+ENTER:

=SUM(C2:C11*D2:D11)

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

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

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

  • ความสม่ำเสมอสอดคล้องกัน    หากคุณคลิกเซลล์ใดๆ ตั้งแต่ E2 ลงมา คุณจะเห็นสูตรเดียวกัน ความสม่ำเสมอสอดคล้องกันนี้จะช่วยให้คุณมั่นใจในความถูกต้องแม่นยำได้
  • ความปลอดภัย    คุณจะไม่สามารถเขียนทับคอมโพเนนต์ของสูตรอาร์เรย์หลายเซลล์ได้ ตัวอย่างเช่น เมื่อคลิกเซลล์ E3 และกด DELETE คุณต้องเลือกช่วงของเซลล์ทั้งหมด (E2 ถึง E11) และเปลี่ยนสูตรสำหรับอาร์เรย์ทั้งหมด หรือปล่อยทิ้งให้อาร์เรย์เป็นแบบเดิมอย่างใดอย่างหนึ่ง จากมาตรการความปลอดภัยที่เพิ่มขึ้น คุณต้องกด CTRL+SHIFT+ENTER เพื่อยืนยันการเปลี่ยนแปลงของสูตร
  • ขนาดแฟ้มที่เล็กกว่า    คุณสามารถใช้สูตรอาร์เรย์เดียวแทนสูตรขั้นกลางต่างๆ ตัวอย่างเช่น สมุดงานที่คุณสร้างขึ้นมาในแบบฝึกหัดนี้ใช้สูตรอาร์เรย์เดียวในการคำนวณผลลัพธ์ในคอลัมน์ E หากคุณใช้สูตรมาตรฐาน (เช่น =C2*D2) คุณจะใช้สูตรที่แตกต่างกัน 11 สูตรในการคำนวณผลลัพธ์เดียวกัน

การดูไวยากรณ์ของสูตรอาร์เรย์

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

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

=C2*D2
=C3*D3

และอื่นๆ สูตรเซลล์เดียวในเซลล์ B13 เป็นรูปแบบอย่างย่อของการดำเนินการการบวก รวมกับตัวเลขคณิตศาสตร์ที่ใช้เพื่อรวมผมรวมย่อย: =E2+E3+E4 และอื่นๆ

กฎสำหรับการป้อนและการเปลี่ยนสูตรอาร์เรย์

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

เมื่อคุณทำงานกับสูตรหลายเซลล์ คุณต้องปฏิบัติตามกฎต่อไปนี้

  • คุณต้องเลือกช่วงของเซลล์เพื่อเก็บรักษาผลลัพธ์ของคุณไว้ ก่อน ที่คุณจะป้อนสูตร คุณได้ดำเนินการนี้แล้วในขั้นตอนที่ 3 จากแบบฝึกหัดสูตรอาร์เรย์หลายเซลล์เมื่อคุณเลือกเซลล์ E2 ถึง E11
  • คุณไม่สามารถเปลี่ยนแปลงเนื้อหาของเซลล์ต่างๆ ในสูตรอาร์เรย์ได้ หากคุณต้องการลองเปลี่ยนแปลงเนื้อหาดู ให้เลือกเซลล์ E3 ในสมุดงานตัวอย่าง แล้วกด DELETE
  • คุณสามารถย้ายหรือลบสูตรอาร์เรย์ทั้งหมดได้ แต่จะไม่สามารถย้ายหรือลบสูตรบางส่วนได้ กล่าวคือ ถ้าต้องการย่อสูตรอาร์เรย์ คุณต้องลบสูตรที่มีอยู่ก่อน แล้วจึงเริ่มใส่สูตรใหม่

 เคล็ดลับ   ในการลบสูตรอาร์เรย์ ให้เลือกสูตรทั้งหมด (เช่น =C2:C11*D2:D11) กด DELETE แล้วจึงกด CTRL+SHIFT+ENTER

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

การขยายสูตรอาร์เรย์

บางครั้ง คุณอาจต้องการขยายสูตรอาร์เรย์ (โปรดจำไว้ว่าคุณไม่สามารถย่อสูตรอาร์เรย์ได้) ซึ่งมีขั้นตอนไม่ซับซ้อน แต่คุณต้องจำกฎต่างๆ ที่แสดงรายการในส่วนก่อนหน้านี้ให้ขึ้นใจ

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

Toth Sedan 6 2500
  Coupe 7 1900
Wang Sedan 4 2200
  Coupe 3 2000
Young Sedan 8 2300
  Coupe 8 2100

  1. เลือกช่วงของเซลล์ที่มีสูตรอาร์เรย์ปัจจุบัน (E2:E11) และเซลล์เปล่า (E12:E17) ที่อยู่ติดกับข้อมูลใหม่ กล่าวคือ ให้เลือกเซลล์ E2:E17
  2. กด F2 เพื่อสลับไปยังโหมดแก้ไข
  3. ในแถบสูตร ให้เปลี่ยน C11 เป็น C17, เปลี่ยน D11 เป็น D17 แล้วกด CTRL+SHIFT+ENTER Excel จะปรับปรุงสูตรในเซลล์ E2 ถึง E11 และใส่อินสแตนซ์ของสูตรลงในเซลล์ใหม่ คือตั้งแต่เซลล์ E12 ถึง E17

ข้อมูลตัวอย่าง


ข้อเสียของการใช้สูตรอาร์เรย์

แม้ว่าสูตรอาร์เรย์จะดูเหมือนเป็นสูตรวิเศษ แต่ก็มีข้อเสียบางประการดังนี้

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

ด้านบนของหน้า ด้านบนของหน้า

เรียนรู้เพิ่มเติมเกี่ยวกับค่าคงที่อาร์เรย์

ส่วนนี้จะนำเสนอค่าคงที่อาร์เรย์ และอธิบายวิธีการป้อน แก้ไข ตลอดจนการแก้ปัญหา

ความรู้เบื้องต้นโดยสังเขปเกี่ยวกับค่าคงที่อาร์เรย์

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

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

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

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

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

การสร้างค่าคงที่มิติเดียวและค่าคงที่สองมิติ

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

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

  1. ใช้สมุดงานจากคอลัมน์ก่อนหน้านี้ หรือเริ่มสมุดงานใหม่
  2. เลือกเซลล์ A1 ถึง E1
  3. ป้อนสูตรต่อไปนี้ลงในแถบสูตร แล้วกด CTRL+SHIFT+ENTER:

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

 หมายเหตุ   ในกรณีนี้คุณควรพิมพ์วงเล็บเปิดและวงเล็บปิด ({ })

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


ค่าคงที่อาร์เรย์แนวนอนในสูตร


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

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

  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 5 6 7

  1. ป้อนสูตรต่อไปนี้ลงในเซลล์ 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. บนแท็บ สูตร ในกลุ่ม ชื่อที่กำหนด ให้คลิก กำหนดชื่อ

กล่องโต้ตอบ กำหนดชื่อ ปรากฏขึ้น

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

={"January","February","March"}

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

แก้ไขกล่องโต้ตอบชื่อด้วยสูตร

  1. คลิก ตกลง
  2. ในแผ่นงาน ให้เลือกแถวเซลล์เปล่าสามเซลล์
  3. พิมพ์สูตรต่อไปนี้ แล้วกด CTRL+SHIFT+ENTER

=Quarter1

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


อาร์เรย์ที่ได้รับการตั้งชื่อซึ่งป้อนเป็นสูตร


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

การแก้ไขปัญหาค่าคงที่อาร์เรย์

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

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

ค่าคงที่อาร์เรย์กับการใช้งาน

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

การคูณรายการต่างๆ ในอาร์เรย์

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

={1,2,3,4;5,6,7,8;9,10,11,12}*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. เปิดสมุดงานปัจจุบันหรือสร้างสมุดงานใหม่ และตรวจสอบให้แน่ใจว่าสมุดงานดังกล่าวมีแผ่นงานเปล่าอยู่สองแผ่นงาน
  2. คัดลอกข้อมูลในตารางต่อไปนี้ และวางลงในแผ่นงานโดยเริ่มตั้งแต่เซลล์ A1
400   the quick   1 2 3 4
1200   brown fox   5 6 7 8
3200   jumped over   9 10 11 12
475   the lazy   13 14 15 16
500   power user          
2000              
600              
1700              
800              
2700              
  1. แผ่นงานของคุณที่ดำเนินการเสร็จแล้วควรมีลักษณะดังนี้

  2. กรอกข้อมูลตัวอย่าง

  3. ตั้งชื่อแผ่นงานแรกว่า Data และตั้งชื่อแผ่นงานเปล่าอีกแผ่นงานหนึ่งว่า Arrays

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

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

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

  1. จากสมุดงานตัวอย่างของคุณ ให้เลือกแผ่นงาน Arrays
  2. เลือกเซลล์ตั้งแต่ C1 ถึง E3
  3. ป้อนสูตรต่อไปนี้ลงในแถบสูตร แล้วกด CTRL+SHIFT+ENTER:

=Data!E1:G3

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

ผลลัพธ์ที่ได้จากแผ่นงาน Arrays

สูตรจะเชื่อมโยงกับค่าที่เก็บไว้ในเซลล์ E1 ถึง G3 ในแผ่นงาน Data อีกทางเลือกหนึ่งของสูตรอาร์เรย์หลายเซลล์คือการวางสูตรที่ไม่ซ้ำกันลงในเซลล์ต่างๆ ของแผ่นงาน Arrays ดังนี้


=Data!E1 =Data!F1 =Data!G1
=Data!E2 =Data!F2 =Data!G2
=Data!E3 =Data!F3 =Data!G3

หากคุณเปลี่ยนค่าบางค่าในแผ่นงาน Data การเปลี่ยนแปลงดังกล่าวจะปรากฏในแผ่นงาน Arrays โปรดอย่าลืมว่าในการเปลี่ยนค่าใดๆ ในแผ่นงาน Data คุณต้องปฏิบัติตามกฎการแก้ไขสูตรอาร์เรย์ โปรดดูข้อมูลเพิ่มเติมเกี่ยวกับกฎดังกล่าวในส่วน เรียนรู้เพิ่มเติมเกี่ยวกับสูตรอาร์เรย์

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

  1. จากแผ่นงาน Arrays ให้เลือกเซลล์ C1 ถึง E3
  2. กด F2 เพื่อเปลี่ยนเป็นโหมดแก้ไข
  3. กด F9 เพื่อแปลงการอ้างอิงเซลล์ให้เป็นค่า Excel จะแปลงค่าต่างๆ ให้เป็นค่าคงที่อาร์เรย์
  4. กด CTRL+SHIFT+ENTER เพื่อป้อนค่าคงที่อาร์เรย์เป็นสูตรอาร์เรย์

Excel จะแทนที่สูตรอาร์เรย์ =Data!E1:G3 ด้วยค่าคงที่อาร์เรย์ต่อไปนี้

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

การเชื่อมโยงระหว่างแผ่นงาน Data และ Arrays จะใช้งานไม่ได้ และค่าคงที่อาร์เรย์จะแทนที่สูตรอาร์เรย์

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

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

  • ในแผ่นงาน Data ให้ป้อนสูตรต่อไปนี้ลงในเซลล์ C7 แล้วกด CTRL+SHIFT+ENTER:

=SUM(LEN(C1:C5))

ค่า 47 จะปรากฏในเซลล์ C7

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

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

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

  1. จากแผ่นงาน Data ให้เลือกเซลล์ตั้งแต่ A12 จนถึง A14

ชุดเซลล์นี้จะเก็บผลลัพธ์ไว้ ซึ่งจะแสดงตามสูตรอาร์เรย์

  1. ในแถบสูตร ให้ป้อนสูตรต่อไปนี้ลงไป แล้วกด CTRL+SHIFT+ENTER:

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

ค่า 400, 475 และ 500 จะปรากฏในเซลล์ A12 ถึง A14 ตามลำดับ

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

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

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

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

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

  1. จากแผ่นงาน Data ให้เลือกเซลล์ A12 ถึง A14
  2. กด DELETE เพื่อล้างสูตรที่มีอยู่โดยเหลือเซลล์ที่เลือกไว้
  3. ในแถบสูตร ให้ป้อนสูตรต่อไปนี้ลงไป แล้วกด CTRL+SHIFT+ENTER:

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

ค่า 3200, 2700 และ 2000 จะปรากฏในเซลล์ A12 ถึง A14 ตามลำดับ

จากจุดนี้ คุณจะได้ทราบเกี่ยวกับฟังก์ชัน ROW และ INDIRECT เล็กน้อย คุณสามารถใช้ฟังก์ชัน ROW สร้างอาร์เรย์ของจำนวนเต็มที่ต่อเนื่องกันได้ ตัวอย่างเช่น เลือกคอลัมน์เปล่าที่มีเซลล์ 10 เซลล์ในสมุดงานฝึกหัดของคุณ ป้อนสูตรอาร์เรย์นี้ในเซลล์ A1:A10 แล้วกด CTRL+SHIFT+ENTER:

=ROW(1:10)

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

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

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

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

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

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

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

  • ในแผ่นงาน Data ให้ล้างสูตรที่มีอยู่จากเซลล์ C7 และป้อนสูตรต่อไปนี้ลงในเซลล์ดังกล่าว แล้วกด CTRL+SHIFT+ENTER:

=INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1)

ค่าที่ข้ามไป จะปรากฏในเซลล์ C7

เราจะมาดูสูตรกัน โดยเริ่มจากอีลิเมนต์ด้านในออกมาด้านนอก ฟังก์ชัน LEN จะแสดงความยาวของรายการแต่ละรายการในช่วงเซลล์ C1:C5 ฟังก์ชัน MAX จะคำนวณค่าที่มากที่สุดของรายการ ซึ่งจะสอดคล้องกับสตริงข้อความที่ยาวที่สุด ซึ่งอยู่ในเซลล์ C3

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

(MAX(LEN(C1:C5))

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

LEN(C1:C5)

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

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

สำหรับข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชันต่างๆ ที่กล่าวถึงในที่นี้ โปรดดูวิธีใช้ใน Excel

ด้านบนของหน้า ด้านบนของหน้า

การใส่สูตรอาร์เรย์ขั้นสูงสำหรับการทำงาน

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

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

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

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

สูตรจะสร้างอาร์เรย์ใหม่ที่ประกอบด้วยค่าเดิมลบด้วยค่าความผิดพลาดใดๆ ฟังก์ชัน ISERROR จะค้นหาความผิดพลาดในช่วงเซลล์ (Data) โดยเริ่มตั้งแต่ฟังก์ชันที่อยู่ข้างในออกมาข้างนอก ฟังก์ชัน IF จะแสดงค่าที่กำหนดไว้หากเงื่อนไขที่คุณระบุมีค่าเป็น TRUE และแสดงค่าอื่น หากเงื่อนไขที่คุณระบุมีค่าเป็น FALSE สำหรับในกรณีนี้ จะแสดงสตริงเปล่า ("") สำหรับค่าความผิดพลาดทั้งหมด เนื่องจากสตริงดังกล่าวมีค่าเป็น TRUE และจะแสดงค่าที่เหลือจากช่วง (Data) เนื่องจากสตริงมีค่าเป็น 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 จะสร้างอาร์เรย์ของค่าบวกและค่า False ฟังก์ชัน SUM จะละเว้นค่า False เนื่องจาก 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 ในการใช้สูตรนี้ ช่วงเซลล์ต้องมีขนาดเท่ากันและเป็นช่วงเซลล์ที่มีมิติเดียวกัน:

=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))

ด้านบนของหน้า ด้านบนของหน้า

 
 
นำไปใช้กับ:
Excel 2007