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

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

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

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

เริ่มต้นด้วยการอัปเดตประจำเดือนกันยายนสำหรับOffice ๓๖๕สูตรใดก็ตามที่สามารถส่งกลับผลลัพธ์ได้หลายผลลัพธ์จะถูกคำนวณโดยอัตโนมัติหรือข้ามไปยังเซลล์ใกล้เคียง การเปลี่ยนแปลงในลักษณะการทำงานนี้ยังมาพร้อมกับฟังก์ชันอาร์เรย์แบบไดนามิกใหม่หลายฟังก์ชัน สูตรอาร์เรย์แบบไดนามิกไม่ว่าพวกเขาจะใช้ฟังก์ชันที่มีอยู่หรือฟังก์ชันอาร์เรย์แบบไดนามิกจะต้องมีการใส่ข้อมูลลงในเซลล์เดียวเท่านั้นและได้รับการยืนยันโดยการกดEnter ก่อนหน้านี้สูตรอาร์เรย์ดั้งเดิมจำเป็นต้องเลือกช่วงผลลัพธ์ทั้งหมดก่อนจากนั้นจึงยืนยันสูตรด้วยการกดCtrl + Shift + Enter โดยทั่วไปจะเรียกว่าสูตรCSE

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

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

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

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

  • รวมค่าที่ n ทุกค่าในช่วงของค่า

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    • ขนาดไฟล์ที่เล็กลง    คุณสามารถใช้สูตรอาร์เรย์เดียวแทนสูตรขั้นกลางหลายๆ สูตรได้ ตัวอย่างเช่น เวิร์กบุ๊กใช้สูตรอาร์เรย์เดียวในการคำนวณผลลัพธ์ในคอลัมน์ 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 *, F14 * G14, F15, G15, G16, F17, G17, G18, F19, G19,,,,,,,,,, *)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    สร้างแถวที่3ตามค่าคงที่อาร์เรย์4คอลัมน์ = ลำดับ (3, 4)

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

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

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

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

ในเซลล์ E11 ให้ใส่= SUM (d9: h1-h9 * ลำดับ (1, 5))หรือ= SUM (D9: h1-h9 * {1, 2, 3, 4, 5}) สูตรจะส่งกลับ๘๕

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

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

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

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

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

= sum (ลำดับ (1, 5, 3, 1) * ลำดับ (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)} นอกจากนี้ ค่าที่เป็นตัวเลขก็ไม่สามารถมีเครื่องหมายเปอร์เซ็นต์ เครื่องหมายดอลลาร์ เครื่องหมายจุลภาค หรือวงเล็บได้

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

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

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

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

เพิ่มค่าคงที่อาร์เรย์ที่มีชื่อจากสูตร > ชื่อที่กำหนดชื่อ > Manager > ใหม่

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

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

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

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

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

= TEXT (DATE (YEAR (วันนี้ ()), ลำดับ (1, 12), 1), "mmm")

ใช้การรวมกันของฟังก์ชันข้อความวันที่ปีวันที่และลำดับเพื่อสร้างรายการแบบไดนามิกของ12เดือน

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

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

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

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

    Enter = ลำดับ (1, 12) * 2หรือ= {1, 2, 3, 4; 5, 6, 7, 8, 10, 11, 12, 12} * 2

    นอกจากนี้คุณยังสามารถหารด้วย (/) เพิ่มด้วย (+) และลบด้วย (-)

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

    Enter = ลำดับ (1, 12) ^ 2หรือ= {1, 2, 3, 4; 5, 6, 7, 8, 10, 11, 12, 12} ^ 2

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

    Enter =SQRT(ลำดับ (1, 12) ^ 2)หรือ= SQRT ({1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 12} ^ 2)

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

    Enter = สลับ (ลำดับ (1, 5))หรือ= สลับ ({1, 2, 3, 4, 5})

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

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

    Enter = สลับ (ลำดับ (5, 1))หรือ= สลับ ({1; 2; 3; 4; 5})

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

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

    Enter = สลับ (ลำดับ (3, 4))หรือ= สลับ ({1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 12})

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

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

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

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

    Enter = ลำดับ (3, 6, 10, 10)หรือ= {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

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

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

    ใช้ตัวดำเนินการช่วงที่หกตัวดำเนินการ (#) เพื่ออ้างอิงอาร์เรย์ที่มีอยู่

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

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

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

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

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

    = SUM (เลน (C9: C13))

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

    = ค่าเฉลี่ย (เลน (C9: C13))

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

    = INDEX (C9: C13, MATCH (MAX (เลน (C9: C13)), เลน (C9: C13), 0), 1)

    สูตรนี้จะทำงานเฉพาะเมื่อช่วงข้อมูลประกอบด้วยคอลัมน์เดียวของเซลล์

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

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

    แม็กซ์ (เลน (C9: C13)

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

    เลน (C9: C13)

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

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

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

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

    • ถ้าคุณไม่ใช้ชนิดการตรงกัน Excel จะถือว่า1

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

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

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

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

    สูตรอาร์เรย์ของ Excel เพื่อค้นหาค่าที่น้อยที่สุด: = ขนาดเล็ก (B9 # ลำดับ (D9))

    Enter = ขนาดเล็ก (B9 # ลำดับ (D9)= ขนาดเล็ก (B9: B18, {1; 2; 3})

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

    = SUM (ขนาดเล็ก (B9 # ลำดับ (D9))

    = ค่าเฉลี่ย (ขนาดเล็ก (B9 # ลำดับ (D9))

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

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

    Enter = ขนาดใหญ่ (B9 #, ROW ("1: 3"))) หรือ= ขนาดใหญ่ (B9: B18, ROW ("1: 3" )))

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

    =ROW(1:10)

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

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

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

    = ลำดับ (10)

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

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

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

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

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

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

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

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

    =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ในช่วงที่เรียกว่ายอดขาย

ตัวอย่างเช่นสูตรอาร์เรย์นี้จะบวกเพียงจำนวนเต็มบวกในช่วงที่ชื่อว่าขายซึ่งแสดงถึงเซลล์ E9: E24 ในตัวอย่างด้านบน:

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

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

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

= SUM ((Sales>0) * (Sales<2500) * (ยอดขาย))

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

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

= SUM (IF ((Sales>0) + (Sales<2500), ยอดขาย))

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

ตัวอย่างนี้จะแสดงวิธีการลบศูนย์ออกจากช่วงเมื่อคุณต้องการหาค่าเฉลี่ยของค่าต่างๆ ที่อยู่ในช่วงนั้น สูตรจะใช้ช่วงข้อมูลที่ชื่อ 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 จะสร้างอาร์เรย์ใหม่ที่สอดคล้องกับช่วงข้อมูลที่ชื่อ ถ้าเซลล์ที่สอดคล้องกันมีค่ามากที่สุดในช่วงอาร์เรย์จะมีหมายเลขแถว มิฉะนั้นอาร์เรย์มีสตริงว่าง ("") ฟังก์ชัน MIN จะใช้อาร์เรย์ใหม่เป็นอาร์กิวเมนต์ที่สองและจะส่งกลับค่าที่น้อยที่สุดซึ่งสอดคล้องกับหมายเลขแถวของค่ามากที่สุดในข้อมูล ถ้าช่วงที่ชื่อข้อมูลมีค่าสูงสุดที่เหมือนกันสูตรจะส่งกลับแถวของค่าแรก

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

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

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

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

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

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

ยอดขาย บุคคล

รถยนต์ พิมพ์

หมายเลข ขายแล้ว

หน่วย ราคา

ผลรวมทั้งหมด ยอดขาย

ชาติ

ซีดาน

5

๓๓๐๐๐

คูเป้

4

๓๗๐๐๐

สัญญา

ซีดาน

6

๒๔๐๐๐

คูเป้

8

๒๑๐๐๐

มโน

ซีดาน

3

๒๙๐๐๐

คูเป้

1

๓๑๐๐๐

พัชระ

ซีดาน

9

๒๔๐๐๐

คูเป้

5

๓๗๐๐๐

สุเชาว์

ซีดาน

6

๓๓๐๐๐

คูเป้

8

๓๑๐๐๐

สูตร (ผลรวมทั้งหมด)

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

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

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

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

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

=SUM(C2:C11*D2:D11)

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

นอกจากนี้ให้สังเกตว่าสูตรเซลล์เดียวในเซลล์ 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 *,C9, 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 จะละเว้นค่าที่ไม่มีเซลล์ที่สอดคล้องกัน

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

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

  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

    ๔๐

    ๕๐

    ๖๐

    ๗๐

    ๘๐

    ๙๐

  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 (๖๖)

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

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

=SUM(LEN(A2:A6))

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

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

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

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

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

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

    = ขนาดเล็ก (A1: A11, {1; 2; 3})

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

= SUM (ขนาดเล็ก (A1: A10, {1, 2, 3})

= ค่าเฉลี่ย (เล็ก (A1: A10, {1, 2, 3})

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

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

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

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

    = ขนาดใหญ่ (A1: A10 แถว (ทางอ้อม ("1: 3")))

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

=ROW(1:10)

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

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

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

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

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

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

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

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

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

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

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

(MAX (จำนวน (A2: A6)

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

เลน (A2: A6)

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

สุดท้ายฟังก์ชันINDEXจะใช้อาร์กิวเมนต์เหล่านี้: อาร์เรย์และหมายเลขแถวและคอลัมน์ภายในอาร์เรย์นั้น ช่วงของเซลล์ A2: A6 มีอาร์เรย์ฟังก์ชัน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))

รับ

ส่วนของบทความนี้ยึดตามชุดของคอลัมน์ Power User ของ Excel ที่เขียนโดยโคลิน Wilcox และดัดแปลงจากบทที่14และ15จากสูตร Excel ๒๐๐๒, หนังสือที่เขียนโดยจอห์น Walkenbach, อดีต MVP ของ excel

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

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

ดูเพิ่มเติม

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

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

ฟังก์ชัน FILTER

ฟังก์ชัน RANDARRAY

ฟังก์ชัน SEQUENCE

ฟังก์ชัน SINGLE

ฟังก์ชัน SORT

ฟังก์ชัน SORTBY

ฟังก์ชัน UNIQUE

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

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

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

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

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

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

×