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

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

สิ่งสำคัญ:  บทความนี้เป็นการแปลด้วยเครื่อง โปรดดู ข้อจำกัดความรับผิดชอบ โปรดดูบทความฉบับภาษาอังกฤษ ที่นี่ เพื่อใช้อ้างอิง

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

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

สูตรอาร์เรย์มักถูกเรียกว่าสูตร CSE (Ctrl+Shift+Enter) เพราะว่าแทนที่จะกดแค่ Enter คุณจะต้องกด Ctrl+Shift+Enter เพื่อทำให้สูตรสมบูรณ์

ทำไมจึงต้องใช้สูตรอาร์เรย์

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

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

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

  • รวมค่าอันดับที่ N ทุกตัวที่อยู่ในช่วงของค่า

บทนำเบื้องต้นเกี่ยวกับอาร์เรย์และสูตรอาร์เรย์

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

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

ลองทำดู!

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

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

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

สร้างสูตรอาร์เรย์หลายเซลล์
  1. คัดลอกตารางด้านล่างทั้งตาราง แล้ววางลงในเซลล์ A1 ในเวิร์กชีตเปล่าใน Excel จากนั้นให้ทำตามคำแนะนำด้านล่างของตาราง

    พนักงาน
    ขาย

    ประเภท
    รถยนต์

    จำนวน
    ที่ขายได้

    ราคา
    ต่อหน่วย

    ยอดขาย
    รวม

    ชาติ

    ซีดาน

    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)

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

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

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

ถ้าคุณกำลังทำงานใน Excel ดูให้แน่ใจว่า Sheet1 กำลังใช้งานอยู่ แล้วเลือกเซลล์ E2:E11 กด F2 และพิมพ์สูตร =C2:C11*D2:D11 ในเซลล์ปัจจุบัน E2 ถ้าคุณกด Enter คุณจะเห็นว่าสูตรถูกใส่ลงในเซลล์ E2 เท่านั้น และจะแสดงผลเป็น 165000 และหลังจากที่คุณพิมพ์สูตรแล้ว ให้กด Ctrl+Shift+Enter แทนการกด Enter โดยตรง ในตอนนี้ คุณจะเห็นผลลัพธ์ในเซลล์ E2:E11 และในแถบสูตรจะปรากฏเป็น {=C2:C11*D2:D11} ซึ่งบอกให้คุณรู้ว่านี่คือสูตรอาร์เรย์ ตามที่แสดงไว้ในตารางด้านล่าง

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

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

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

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

=SUM(C2:C11*D2:D11)

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

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

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

  • ความสอดคล้องกัน    ถ้าคุณคลิกเซลล์ใดๆ ตั้งแต่ 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 จะแสดงข้อความบอกว่าคุณไม่สามารถเปลี่ยนแปลงเพียงบางส่วนของอาร์เรย์ได้

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

  • เมื่อต้องการลบสูตรอาร์เรย์ ให้เลือกสูตรทั้งหมด (เช่น =C2:C11*D2:D11) กด Delete แล้วกด Ctrl+Shift+Enter

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

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

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

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

ต้องแน่ใจว่าคุณได้ลองทำขั้นตอนนี้ในโปรแกรม Excel สำหรับเดสก์ท็อป (หลังจากที่คุณได้ดาวน์โหลดเวิร์กบุ๊กลงในคอมพิวเตอร์ของคุณ)

ขยายสูตรอาร์เรย์
  1. คัดลอกทั้งตารางลงในเซลล์ A1 ในเวิร์กชีต Excel

    พนักงาน
    ขาย

    ประเภท
    รถยนต์

    จำนวน
    ที่ขายได้

    ราคา
    ต่อหน่วย

    ยอดขาย
    รวม

    ชาติ

    ซีดาน

    5

    33000

    165000

    คูเป้

    4

    37000

    148000

    สัญญา

    ซีดาน

    6

    24000

    144000

    คูเป้

    8

    21000

    168000

    มโน

    ซีดาน

    3

    29000

    87000

    คูเป้

    1

    31000

    31000

    พัชระ

    ซีดาน

    9

    24000

    216000

    คูเป้

    5

    37000

    185000

    สุเชาว์

    ซีดาน

    6

    33000

    198000

    คูเป้

    8

    31000

    248000

    กิตติกร

    ซีดาน

    2

    27000

    คูเป้

    3

    30000

    ขจี

    ซีดาน

    4

    22000

    คูเป้

    1

    41000

    แสงระวี

    ซีดาน

    5

    32000

    คูเป้

    3

    36000

    ผลรวมทั้งหมด

  2. เลือกเซลล์ E18 ใส่สูตรผลรวมทั้งหมด =SUM(C2:C17*D2:D17) ในเซลล์ A20 แล้วกด Ctrl+Shift+Enter
    ผลลัพธ์ควรเป็น 2,131,000

  3. เลือกช่วงของเซลล์ที่มีสูตรอาร์เรย์ปัจจุบัน (E2:E11) รวมทั้งเซลล์เปล่า (E12:E17) ที่อยู่ติดกับข้อมูลใหม่ กล่าวคือ ให้เลือกเซลล์ E2:E17

  4. กด F2 เพื่อเปลี่ยนเป็นโหมดแก้ไข

  5. ในแถบสูตร ให้เปลี่ยน C11 เป็น C17, เปลี่ยน D11 เป็น D17 แล้วกด Ctrl+Shift+Enter
    จากนั้น Excel จะอัปเดตสูตรในเซลล์ E2 ถึง E11 และใส่อินสแตนซ์ของสูตรลงในเซลล์ใหม่ นั่นคือตั้งแต่เซลล์ E12 ถึง E17

  6. พิมพ์สูตรอาร์เรย์ = SUM(C2:C17*D2*D17) ลงในเซลล์ F17 เพื่อให้อ้างอิงเซลล์จากแถว 2 ถึงแถว 17 แล้วกด Ctrl+Shift+Enter เพื่อใส่สูตรอาร์เรย์ลงไป
    ผลรวมทั้งหมดที่คำนวณใหม่ควรเป็น 2,131,000

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

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

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

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

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

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

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

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

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

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

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

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

และด้วยสูตรอาร์เรย์นี้เอง คุณสามารถใช้ค่าคงที่อาร์เรย์กับฟังก์ชันที่มีอยู่ภายในส่วนใหญ่ที่ 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 ใน 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 เพื่อเปลี่ยนเป็นโหมดแก้ไข
    สูตรอาร์เรย์ควรเป็น =C8:E10 ตามเดิม

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

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

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

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

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

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

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

ข้อมูล

นี่คือกลุ่มเซลล์ที่มาพร้อมกัน

เพื่อรวมเป็นเพื่อรวมกันเป็น

<!--_blank-->

<!--_blank-->

<!--_blank-->

อักขระทั้งหมดใน A2:A6

=SUM(LEN(A2:A6))

เนื้อหาของเซลล์ที่ยาวที่สุด (A3)

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

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

=SUM(LEN(A2:A6))

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

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

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

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

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

    =SMALL(A5:A14,{1;2;3})

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

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

=SUM(SMALL(A 5 :A1 4 ,{1;2;3}))

=AVERAGE(SMALL(A 5 :A1 4 ,{1;2;3}))

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

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

  1. เลือกเซลล์ A1 ถึง A3

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

    =LARGE(A5:A14,ROW(INDIRECT("1:3")))

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

จากจุดนี้ คุณจะได้ทราบเกี่ยวกับฟังก์ชัน ROW และ INDIRECT เล็กน้อย คุณสามารถใช้ฟังก์ชัน ROW สร้างอาร์เรย์ของจำนวนเต็มที่ต่อเนื่องกันได้ ตัวอย่างเช่น เลือกคอลัมน์เปล่าที่มีเซลล์ 10 เซลล์ในเวิร์กบุ๊กฝึกหัดของคุณ ใส่สูตรอาร์เรย์นี้ในเซลล์ A5:A14 แล้วกด 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 เป็นต้น

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

สูตรนี้จะใช้งานได้ก็ต่อเมื่อช่วงข้อมูลมีเซลล์คอลัมน์เดียวเท่านั้น บน แผ่นงาน3 ให้ใส่สูตรต่อไปนี้ลงในเซลล์ A16 แล้วกด Ctrl+Shift+Enter

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

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

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

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

(MAX(LEN( A6 : A9 ))

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

LEN( A6:A9 )

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

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

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

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

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

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

ฟังก์ชัน 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 เมื่อต้องการใช้สูตรนี้ ช่วงเซลล์ต้องมีขนาดเท่ากันและเป็นช่วงเซลล์ที่มีมิติเดียวกัน (ตัวอย่างเช่น ถ้า 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 Tech Community ขอความช่วยเหลือใน Answers community หรือแนะนำฟีเจอร์ใหม่หรือการปรับปรุงบน Excel User Voiceได้เสมอ

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

ดูเพิ่มเติม

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

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

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

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

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

×