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

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

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

  

• ต่อไปนี้คือการคํานวณ Total Sales of coupes และ sedans สําหรับพนักงานขายแต่ละคนโดยการใส่ =F10:F19*G10:G19 ในเซลล์ H10

  เมื่อคุณกด Enter คุณจะเห็นผลลัพธ์หกลงในเซลล์ H10:H19 โปรดสังเกตว่าช่วงสปิลล์จะถูกเน้นด้วยเส้นขอบเมื่อคุณเลือกเซลล์ใดๆ ภายในช่วงสปิลล์ คุณอาจสังเกตเห็นว่าสูตรในเซลล์ H10:H19 เป็นสีเทา สูตรเหล่านั้นมีไว้สําหรับการอ้างอิง เท่านั้น ดังนั้นถ้าคุณต้องการปรับสูตร คุณจะต้องเลือกเซลล์ H10 ที่สูตรหลักอาศัยอยู่

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

  

  ในเซลล์ H20 ของเวิร์กบุ๊กตัวอย่าง ให้พิมพ์หรือคัดลอกและวาง =SUM(F10:F19*G10:G19) แล้วกด Enter

  ในกรณีนี้ Excel จะคูณค่าในอาร์เรย์ (ช่วงเซลล์ F10 ถึง G19) แล้วใช้ฟังก์ชัน SUM เพื่อเพิ่มผลรวมเข้าด้วยกัน ผลลัพธ์คือยอดขายรวมทั้งหมด $1,590,000

  ตัวอย่างนี้แสดงให้เห็นว่าสูตรชนิดนี้มีประสิทธิภาพมากเพียงใด ตัวอย่างเช่น สมมติว่าคุณมีแถวข้อมูล 1,000 แถว คุณสามารถหาผลรวมของข้อมูลบางส่วนหรือทั้งหมดได้โดยการสร้างสูตรอาร์เรย์ในเซลล์เดียวแทนที่จะลากสูตรลงไปตามแถว 1,000 แถว นอกจากนี้ โปรดสังเกตว่าสูตรเซลล์เดียวในเซลล์ H20 จะเป็นอิสระจากสูตรหลายเซลล์ (สูตรในเซลล์ H10 ถึง H19) อย่างสิ้นเชิง นี่คือข้อดีอีกอย่างหนึ่งของการใช้สูตรอาร์เรย์ ซึ่งก็คือความยืดหยุ่น คุณสามารถเปลี่ยนสูตรอื่นๆ ในคอลัมน์ H ได้โดยไม่มีผลกระทบต่อสูตรใน H20 นอกจากนี้ยังอาจเป็นแนวทางปฏิบัติที่ดีที่จะมีผลรวมอิสระเช่นนี้เนื่องจากจะช่วยในการตรวจสอบความถูกต้องของผลลัพธ์ของคุณ

• สูตรอาร์เรย์แบบไดนามิกยังมีข้อดีเหล่านี้:

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

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

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

  • ประสิทธิภาพ    ฟังก์ชันอาร์เรย์เป็นวิธีที่มีประสิทธิภาพในการสร้างสูตรที่ซับซ้อน สูตรอาร์เรย์ =SUM(F10:F19*G10:G19) เหมือนกับสูตรนี้: =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19)

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

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

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

={1,2,3,4,5} หรือ ={"January","กุมภาพันธ์","March"}

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

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

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

  ใช้เวิร์กบุ๊กจากตัวอย่างก่อนหน้านี้ หรือสร้างเวิร์กบุ๊กใหม่ เลือกเซลล์ว่างแล้วใส่ =SEQUENCE(1,5) ฟังก์ชัน SEQUENCE สร้างอาร์เรย์ 1 แถวคูณ 5 คอลัมน์เหมือนกับ ={1,2,3,4,5} ผลลัพธ์ต่อไปนี้จะแสดงขึ้น:

  

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

  เลือกเซลล์ว่างที่มีห้องอยู่ข้างใต้ แล้วใส่ =SEQUENCE(5) หรือ ={1; 2; 3; 4; 5} ผลลัพธ์ต่อไปนี้จะแสดงขึ้น:

  

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

  เลือกเซลล์ว่างที่มีห้องอยู่ทางขวาและข้างใต้ แล้วใส่ =SEQUENCE(3,4) คุณจะเห็นผลลัพธ์ที่ได้ดังนี้

  

  คุณยังสามารถใส่: หรือ ={1,2,3,4; 5,6,7,8; 9,10,11,12} แต่คุณจะต้องใส่ใจกับตําแหน่งที่คุณใส่เครื่องหมายอัฒภาคกับเครื่องหมายจุลภาค

  อย่างที่คุณเห็นตัวเลือก SEQUENCE มีข้อดีอย่างมากในการใส่ค่าคงที่อาร์เรย์ของคุณด้วยตนเอง ส่วนใหญ่จะช่วยประหยัดเวลาของคุณ แต่ก็สามารถช่วยลดข้อผิดพลาดจากการป้อนด้วยตนเอง นอกจากนี้ยังง่ายต่อการอ่าน โดยเฉพาะอย่างยิ่งเนื่องจากเครื่องหมายอัฒภาคสามารถแยกความแตกต่างจากตัวคั่นเครื่องหมายจุลภาคได้ยาก

ต่อไปนี้เป็นตัวอย่างที่ใช้ค่าคงที่อาร์เรย์เป็นส่วนหนึ่งของสูตรที่ใหญ่ขึ้น ในเวิร์กบุ๊กตัวอย่าง ให้ไปที่ ค่าคงที่ ในเวิร์กชีตสูตร หรือสร้างเวิร์กชีตใหม่

ในเซลล์ D9 เราได้ใส่ =SEQUENCE(1,5,3,1) แต่คุณยังสามารถใส่ 3, 4, 5, 6 และ 7 ในเซลล์ A9:H9 ไม่มีอะไรพิเศษเกี่ยวกับการเลือกหมายเลขนั้นเราเพียงแค่เลือกอย่างอื่นนอกเหนือจาก 1-5 สําหรับความแตกต่าง

ในเซลล์ E11 ให้ใส่ =SUM(D9:H9*SEQUENCE(1,5)) หรือ =SUM(D9:H9*{1,2,3,4,5}) สูตรจะส่งกลับค่า 85

ฟังก์ชัน SEQUENCE สร้างค่าคงที่อาร์เรย์ที่เทียบเท่ากัน {1,2,3,4,5} เนื่องจาก Excel จะดําเนินการกับนิพจน์ที่อยู่ในวงเล็บก่อน องค์ประกอบสององค์ประกอบถัดไปที่เข้ามาเล่นคือค่าของเซลล์ใน D9:H9 และตัวดําเนินการคูณ (*) ในขั้นตอนนี้ สูตรจะคูณค่าต่างๆ ในอาร์เรย์ที่เก็บไว้ด้วยค่าที่สอดคล้องกันในค่าคงที่ ซึ่งเทียบเท่ากับ:

=SUM(D9*1,E9*2,F9*3,G9*4,H9*5) หรือ =SUM(3*1,4*2,5*3,6*4,7*5)

สุดท้าย ฟังก์ชัน SUM จะบวกค่าและส่งกลับค่า 85

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

=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)) หรือ =SUM({3,4,5,6,7}*{1,2,3,4,5})

องค์ประกอบที่คุณสามารถใช้ในค่าคงที่อาร์เรย์

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

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

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

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

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

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

คลิก ตกลง แล้วเลือกแถวใดก็ได้ที่มีเซลล์ว่างสามเซลล์ แล้วใส่ =Quarter1

ผลลัพธ์ต่อไปนี้จะแสดงขึ้น:

ถ้าคุณต้องการให้ผลลัพธ์กระจายในแนวตั้งแทนแนวนอน คุณสามารถใช้ =TRANSPOSE(Quarter1)

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

=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"mmm")

ฟังก์ชัน DATE จะใช้ฟังก์ชัน DATE เพื่อสร้างวันที่โดยยึดตามปีปัจจุบัน SEQUENCE จะสร้างค่าคงที่อาร์เรย์ตั้งแต่ 1 ถึง 12 สําหรับเดือนมกราคมถึงธันวาคม จากนั้น ฟังก์ชัน TEXT จะแปลงรูปแบบการแสดงผลเป็น "mmm" (ม.ค., ก.พ., มี.ค.เป็นต้น) ถ้าคุณต้องการแสดงชื่อเดือนแบบเต็ม เช่น มกราคม คุณต้องใช้ "mmmm"

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

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

• หลายรายการในอาร์เรย์

  ใส่ =SEQUENCE(1,12)*2, หรือ ={1,2,3,4; 5,6,7,8; 9,10,11,12}*2

  คุณยังสามารถหารด้วย (/) บวกด้วย (+) และลบด้วย (-)

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

  ใส่ =SEQUENCE(1,12)^2 หรือ ={1,2,3,4; 5,6,7,8; 9,10,11,12}^2

• ค้นหารากที่สองของรายการยกกําลังสองในอาร์เรย์

  ป้อน =SQRT(SEQUENCE(1,12)^2) หรือ =SQRT({1,2,3,4; 5,6,7,8; 9,10,11,12}^2)

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

  Enter =TRANSPOSE(SEQUENCE(1,5)) หรือ =TRANSPOSE({1,2,3,4,5})

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

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

  Enter =TRANSPOSE(SEQUENCE(5,1)) หรือ =TRANSPOSE({1; 2; 3; 4; 5})

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

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

  Enter =TRANSPOSE(SEQUENCE(3,4)) หรือ =TRANSPOSE({1,2,3,4; 5,6,7,8; 9,10,11,12})

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

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

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

  ตัวอย่างต่อไปนี้อธิบายวิธีการใช้สูตรอาร์เรย์เพื่อสร้างอาร์เรย์ใหม่จากอาร์เรย์ที่มีอยู่

  ใส่ =SEQUENCE(3,6,10,10) หรือ ={10,20,30,40,50,60; 70,80,90,100,110,120; 130,140,150,160,170,180}

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

  ถัดไป ให้ใส่ =D9# หรือ =D9:I11 ในเซลล์ว่าง อาร์เรย์ของเซลล์ขนาด 3 x 6 จะปรากฏพร้อมค่าเดียวกันกับที่คุณเห็นใน D9:D11 เครื่องหมาย # เรียกว่า ตัวดําเนินการช่วงที่สปิลล์ และเป็นวิธีการอ้างอิงช่วงอาร์เรย์ทั้งหมดของ Excel แทนที่จะพิมพ์ออกมา

  

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

  คุณสามารถใช้ผลลัพธ์ของสูตรอาร์เรย์ที่หกและแปลงเป็นส่วนต่างๆ ของคอมโพเนนต์ได้ เลือกเซลล์ D9 แล้วกด F2 เพื่อสลับไปยังโหมดแก้ไข ถัดไป ให้กด F9 เพื่อแปลงการอ้างอิงเซลล์เป็นค่าซึ่ง Excel จะแปลงเป็นค่าคงที่อาร์เรย์ เมื่อคุณกด Enter สูตร =D9# ควรเป็น ={10,20,30; 40,50,60; 70,80,90}.

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

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

  

  =SUM(LEN(C9:C13))

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

  =AVERAGE(LEN(C9:C13))

• เนื้อหาของเซลล์ที่ยาวที่สุดในช่วง C9:C13

  =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

  สูตรนี้จะทํางานเมื่อช่วงข้อมูลมีคอลัมน์เดียวของเซลล์เท่านั้น

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

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

  MAX(LEN(C9:C13)

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

  LEN(C9:C13)

  อาร์กิวเมนต์ชนิดตรงกันในกรณีนี้คือ 0 ชนิดการจับคู่สามารถเป็นค่า 1, 0 หรือ -1

  • 1 - ส่งกลับค่าที่มากที่สุดที่น้อยกว่าหรือเท่ากับค่า val ของการค้นหา

  • 0 - ส่งกลับค่าแรกเท่ากับค่าการค้นหาทุกประการ

  • -1 - ส่งกลับค่าที่น้อยที่สุดที่มากกว่าหรือเท่ากับค่าการค้นหาที่ระบุ

  • ถ้าคุณละชนิดที่ตรงกัน Excel จะถือว่าเป็น 1

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

  ถ้าคุณต้องการรับเนื้อหาของสตริงข้อความที่เล็กที่สุด คุณจะต้องแทนที่ MAX ในตัวอย่างด้านบนด้วย MIN

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

  ตัวอย่างนี้แสดงวิธีการค้นหาค่าที่น้อยที่สุดสามค่าในช่วงของเซลล์ ที่อาร์เรย์ของข้อมูลตัวอย่างในเซลล์ B9:B18has ถูกสร้างขึ้นด้วย: =INT(RANDARRAY(10,1)*100) โปรดทราบว่า RANDARRAY เป็นฟังก์ชันที่เปลี่ยนแปลงได้ ดังนั้นคุณจะได้รับชุดตัวเลขแบบสุ่มใหม่ทุกครั้งที่ Excel คํานวณ

  

  Enter =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1; 2; 3})

  สูตรนี้ใช้ค่าคงที่อาร์เรย์เพื่อประเมิน ฟังก์ชัน SMALL สามครั้ง และส่งกลับสมาชิกที่น้อยที่สุด 3 รายในอาร์เรย์ที่มีอยู่ในเซลล์ B9:B18 โดยที่ 3 คือค่าตัวแปรในเซลล์ D9 เมื่อต้องการค้นหาค่าเพิ่มเติม คุณสามารถเพิ่มค่าในฟังก์ชัน SEQUENCE หรือเพิ่มอาร์กิวเมนต์เพิ่มเติมไปยังค่าคงที่ คุณยังสามารถใช้ฟังก์ชันเพิ่มเติมกับสูตรนี้ เช่น SUM หรือ AVERAGE ตัวอย่างเช่น

  =SUM(SMALL(B9#,SEQUENCE(D9))

  =AVERAGE(SMALL(B9#,SEQUENCE(D9))

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

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

  ใส่ =LARGE(B9#,ROW(INDIRECT("1:3"))) หรือ =LARGE(B9:B18,ROW(INDIRECT("1:3")))

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

  =ROW(1:10)

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

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

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

  =SEQUENCE(10)

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

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

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

  

• =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 ซึ่งแทนเซลล์ E9:E24 ในตัวอย่างด้านบน

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

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

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

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

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

คุณยังสามารถสร้างสูตรอาร์เรย์ที่ใช้ชนิดของเงื่อนไข OR ได้ ตัวอย่างเช่น คุณสามารถรวมค่าที่มากกว่า 0 หรือ น้อยกว่า 2500 ได้

=SUM(IF((Sales>0)+(Sales<2500),Sales))

คุณไม่สามารถใช้ฟังก์ชัน 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))

คุณจะพบตัวอย่างที่คล้ายกันในเวิร์กบุ๊กตัวอย่างบนเวิร์กชีต ความแตกต่างระหว่างชุดข้อมูล

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

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

คัดลอกทั้งตารางด้านล่างและวางลงในเซลล์ 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))