ข้ามไปที่เนื้อหาหลัก
การสนับสนุน
ลงชื่อเข้าใช้
แนวทางและตัวอย่างของสูตรอาร์เรย์

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

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

ตั้งแต่การอัปเดตของเดือนกันยายน 2018 Microsoft 365สูตรใดๆ ที่สามารถส่งกลับผลลัพธ์หลายผลลัพธ์จะสไปด้านล่างหรือข้ามไปยังเซลล์ข้างเคียงโดยอัตโนมัติ การเปลี่ยนแปลงนี้พฤติกรรมจะยังมาพร้อมกับฟังก์ชันอาร์เรย์ แบบไดนามิกใหม่หลายฟังก์ชัน สูตรอาร์เรย์แบบไดนามิก ไม่ว่าสูตรเหล่านั้นจะใช้ฟังก์ชันที่มีอยู่หรือฟังก์ชันอาร์เรย์แบบไดนามิก จะต้องป้อนข้อมูลลงในเซลล์เดียวเท่านั้น และได้รับการยืนยันโดยการกด Enter สูตรอาร์เรย์ดั้งเดิมก่อนหน้านี้ต้องเลือกช่วงผลลัพธ์ทั้งหมดก่อน จากนั้นยืนยันสูตรด้วยCtrl+Shift+Enter ซึ่งโดยทั่วไปจะเรียกว่าสูตรCSS

คุณสามารถใช้สูตรอาร์เรย์เพื่อจัดการงานที่ซับซ้อนเช่น:

  • สร้างชุดข้อมูลตัวอย่างอย่างรวดเร็ว

  • นับจํานวนอักขระที่มีอยู่ในช่วงของเซลล์

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

  • รวมค่า N ทุกตัวในช่วงของค่า

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

ดาวน์โหลดตัวอย่างของเรา

ดาวน์โหลดเวิร์กบุ๊กตัวอย่างที่มีตัวอย่างสูตรอาร์เรย์ทั้งหมดในบทความนี้

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

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

    ฟังก์ชันอาร์เรย์หลายเซลล์ในเซลล์ H10 =F10:F19*G10:G19 เพื่อคํานวณจํานวนรถยนต์ที่ขายโดยราคาต่อหน่วย

  • ที่นี่เราคํานวณยอดขายรวมของคูปและซีดานของพนักงานขายแต่ละคนโดยการใส่ =F10:F19*G10:G19 ในเซลล์ H10

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

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

    สูตรอาร์เรย์เซลล์เดียวเพื่อคํานวณผลรวมทั้งหมดด้วย =SUM(F10:F19*G10:G19)

    ในเซลล์ 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…) ตัวอย่างเช่น ตัวอย่างยอดขายรถยนต์จะใช้สูตรอาร์เรย์หนึ่งสูตรเพื่อคํานวณผลลัพธ์ในคอลัมน์ E ถ้าคุณใช้สูตรมาตรฐาน เช่น =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! #A0!-    อาร์เรย์แบบไดนามิกแนะ #SPILL! ข้อผิดพลาดซึ่งระบุว่าช่วงสปีลที่ตั้งใจไว้ถูกบล็อกด้วยเหตุผลบางอย่าง เมื่อคุณแก้ไขการบล็อก สูตรจะสหกใส่โดยอัตโนมัติ

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

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

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

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

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

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

    สร้างค่าคงที่อาร์เรย์แนวนอนด้วย =SEQUENCE(1,5) หรือ ={1,2,3,4,5}

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

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

    สร้างค่าคงที่อาร์เรย์แนวตั้งด้วย =SEQUENCE(5) หรือ ={1;2;3;4;5}

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

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

    สร้างค่าคงที่อาร์เรย์ 3 แถว 4 คอลัมน์ด้วย =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

ใช้ค่าคงที่อาร์เรย์ในสูตร ในตัวอย่างนี้ เราใช้ =SUM(D9:H(*SEQUENCE(1,5))

ฟังก์ชัน 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

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

ใช้ค่าคงที่อาร์เรย์ที่มีชื่อในสูตร เช่น =Quarter1 ที่ซึ่ง Quarter1 ถูกกําหนดเป็น ={"มกราคม","กุมภาพันธ์","มีนาคม"}

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

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

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

ใช้ฟังก์ชัน TEXT, DATE, YEAR, TODAY และ SEQUENCE ร่วมกันเพื่อสร้างรายการแบบไดนามิกเป็นเวลา 12 เดือน

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

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

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

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

    ใส่ =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)

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

    ใส่ =TRANSPOS1(SEQUENCE(1,5))หรือ =TRANSPOSSS({1,2,3,4,5})

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

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

    ใส่ =TRANSPOS1(SEQUENCE(5,1))หรือ =TRANSPOSSS({1;2;3;4;5})

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

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

    ใส่ =TRANSPOS1(SEQUENCE(3,4))หรือ =TRANSPOS1({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 จะคํานวณออฟเซต (ตําแหน่งสัมพันธ์) ของเซลล์ที่มีสตริงข้อความที่ยาวที่สุด To do that, it requires three arguments: alookup value, a lookup array, and a match type. ฟังก์ชัน MATCH จะค้นหาอาร์เรย์การค้นหาค่าการค้นหาที่ระบุ ในกรณีนี้ ค่าการค้นหาคือสตริงข้อความที่ยาวที่สุด:

    MAX(LEN(C9:C13)

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

    LEN(C9:C13)

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

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

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

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

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

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

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

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

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

    Excelอาร์เรย์เพื่อหาค่าที่น้อยที่สุดในอันดับ N: =SMALL(B9#,SEQUENCE(D9))

    ใส่ =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 สุดท้าย เหมือนกับตัวอย่าง SMALL คุณสามารถใช้สูตรนี้กับฟังก์ชันอื่น เช่น SUM และ AVERAGE

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

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

    ใช้อาร์เรย์เพื่อจัดการกับข้อผิดพลาด ตัวอย่างเช่น =SUM(IF(ISERROR(Data),"",Data) จะรวมช่วงที่ชื่อ Data แม้ว่าจะมีข้อผิดพลาด เช่น #VALUE! หรือ #NA!

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

คุณอาจต้องรวมค่าตามเงื่อนไข

คุณสามารถใช้อาร์เรย์เพื่อคํานวณตามเงื่อนไขบางอย่างได้ =SUM(IF(Sales>0,Sales)) จะรวมค่าทั้งหมดที่มากกว่า 0 ในช่วงที่เรียกว่า Sales

ตัวอย่างเช่น สูตรอาร์เรย์นี้จะรวมเฉพาะจํานวนเต็มบวกในช่วงที่ชื่อ 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+EnterExcelจะล้อมรอบสูตรด้วยวงเล็บปีกกา ( {}) และแทรกอินสแตนซ์ของสูตรในแต่ละเซลล์ของช่วงที่เลือก ซึ่งเกิดขึ้นเร็วมาก ดังนั้นสิ่งที่คุณเห็นในคอลัมน์ E คือยอดขายรวมของพนักงานขายแต่ละรถยนต์แต่ละประเภท ถ้าคุณเลือก E2 จากนั้นเลือก E3, E4 และอื่นๆ คุณจะเห็นว่าสูตรเดียวกันนี้แสดงอยู่: {=C2:C11*D2:D11} 

ผลรวมในคอลัมน์ E จะได้จากการคำนวณโดยสูตรอาร์เรย์

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

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

=SUM(C2:C11*D2:D11)

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

นอกจากนี้ โปรดสังเกตว่า สูตรเซลล์เดียวในเซลล์ D13 จะแยกจากสูตรหลายเซลล์ (สูตรในเซลล์ E2 ถึง E11) อย่างสิ้นเชิง นี่คือประโยชน์อีกอย่างหนึ่งของการใช้สูตรอาร์เรย์ ความยืดหยุ่น คุณสามารถเปลี่ยนสูตรในคอลัมน์ E หรือลบคอลัมน์นั้นทั้งหมดได้ โดยไม่มีผลต่อสูตรใน 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)

สิ่งสำคัญ: กด Ctrl+Shift+Enter เมื่อใดก็ตามที่คุณต้องการใส่สูตรอาร์เรย์ ซึ่งใช้ได้กับทั้งสูตรเซลล์เดียวและสูตรหลายเซลล์

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

  • เลือกช่วงของเซลล์เพื่อเก็บผลลัพธ์ ของคุณ ไว้ก่อนที่คุณจะใส่สูตร คุณได้สร้างสูตรอาร์เรย์หลายเซลล์เมื่อคุณเลือกเซลล์ 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ละเว้นค่าที่ไม่มีเซลล์ที่สอดคล้องกัน

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

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

  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 จะคํานวณออฟเซต (ตําแหน่งสัมพันธ์) ของเซลล์ที่มีสตริงข้อความที่ยาวที่สุด To do that, it requires three arguments: alookup value, a lookup array, and a match type. ฟังก์ชัน 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))

การตอบรับ

ส่วนต่างๆ ของบทความนี้ยึดตามชุดของคอลัมน์ Excel Power User ที่เขียนโดย Colin Wilcox และปรับจากบท 14 และ 15 ของ Excel 2002 Formulas หนังสือที่เขียนโดย John Walken Excel MVP เดิม

ต้องการความช่วยเหลือเพิ่มเติมใช่ไหม

คุณสามารถสอบถามผู้เชี่ยวชาญใน Excel Tech Community ขอความช่วยเหลือใน Answers community หรือแนะนำฟีเจอร์ใหม่หรือการปรับปรุงบน Excel User Voice

ดูเพิ่มเติม

ลักษณะการทำงานของอาร์เรย์แบบไดนามิกและอาร์เรย์ที่กระจายตัว

สูตรอาร์เรย์แบบไดนามิกกับสูตรอาร์เรย์ CSS แบบดั้งเดิม

ฟังก์ชัน FILTER

ฟังก์ชัน RANDARRAY

ฟังก์ชัน SEQUENCE

ฟังก์ชัน SORT

ฟังก์ชัน SORTBY

ฟังก์ชัน UNIQUE

ข้อผิดพลาด #SPILL! ใน Excel

ตัวคูณจุดตัดโดยนัย: @

ภาพรวมของสูตร

ต้องการความช่วยเหลือเพิ่มเติมหรือไม่

ขยายทักษะ Office ของคุณ
สำรวจการฝึกอบรม
รับฟีเจอร์ใหม่ก่อนใคร
เข้าร่วม Office Insider

ข้อมูลนี้เป็นประโยชน์หรือไม่

ขอบคุณสำหรับคำติชมของคุณ!

ขอขอบคุณสำหรับคำติชมของคุณ! เราคิดว่าอาจเป็นประโยชน์ที่จะให้คุณได้ติดต่อกับหนึ่งในตัวแทนฝ่ายสนับสนุน Office ของเรา

×