สูตรอาร์เรย์เป็นสูตรที่มีประสิทธิภาพสูง ซึ่งทําให้คุณสามารถทําการคํานวณที่ซับซ้อนซึ่งมักจะไม่สามารถทําได้ด้วยฟังก์ชันเวิร์กชีตมาตรฐาน ซึ่งเรียกอีกอย่างว่าสูตร "Ctrl-Shift-Enter" หรือ "CSE" เนื่องจากคุณต้องกด Ctrl+Shift+Enter เพื่อใส่ลงไป คุณสามารถใช้สูตรอาร์เรย์เพื่อทําให้ดูเหมือนว่าเป็นไปไม่ได้ เช่น
- นับจํานวนอักขระในช่วงของเซลล์
- รวมตัวเลขที่ตรงกับเงื่อนไขบางอย่าง เช่น ค่าต่ําสุดในช่วงหรือตัวเลขที่อยู่ระหว่างขอบเขตบนและขอบเขตล่าง
- รวมค่าอันดับที่ N ทุกตัวที่อยู่ในช่วงของค่า
Excel มีสูตรอาร์เรย์สองชนิด: สูตรอาร์เรย์ที่ทําการคํานวณหลายแบบเพื่อสร้างผลลัพธ์เดียวและสูตรอาร์เรย์ที่คํานวณผลลัพธ์หลายผลลัพธ์ ฟังก์ชันเวิร์กชีตบางฟังก์ชันจะส่งกลับอาร์เรย์ของค่า หรือต้องการอาร์เรย์ของค่าเป็นอาร์กิวเมนต์ สําหรับข้อมูลเพิ่มเติม ให้ดู คําแนะนําและตัวอย่างของสูตรอาร์เรย์
หมายเหตุ
ถ้าคุณมี Microsoft 365 เวอร์ชันปัจจุบัน คุณสามารถใส่สูตรในเซลล์ด้านบนซ้ายของช่วงผลลัพธ์ แล้วกด ENTER เพื่อยืนยันสูตรเป็นสูตรอาร์เรย์แบบไดนามิก มิฉะนั้น ต้องใส่สูตรเป็นสูตรอาร์เรย์ดั้งเดิม โดยเลือกช่วงผลลัพธ์ก่อน ใส่สูตรในเซลล์ด้านซ้ายบนของช่วงผลลัพธ์ แล้วกด CTRL+SHIFT+ENTER เพื่อยืนยัน Excel จะแทรกวงเล็บปีกกาที่จุดเริ่มต้นและจุดสิ้นสุดของสูตรให้คุณ สำหรับข้อมูลเพิ่มเติมเกี่ยวกับสูตรอาร์เรย์ ให้ดูที่ คำแนะนำและตัวอย่างของสูตรอาร์เรย์
สร้างสูตรอาร์เรย์ที่คํานวณผลลัพธ์เดียว
สูตรอาร์เรย์ชนิดนี้ทำให้รูปแบบของเวิร์กชีตง่ายขึ้นได้โดยการแทนที่หลายๆ สูตรที่ต่างกันด้วยสูตรอาร์เรย์สูตรเดียว
คลิกเซลล์ที่คุณต้องการใส่สูตรอาร์เรย์
ใส่สูตรที่คุณต้องการใช้
สูตรอาร์เรย์จะใช้ไวยากรณ์ของสูตรมาตรฐาน โดยทั้งหมดจะเริ่มต้นด้วยเครื่องหมายเท่ากับ (=) และคุณสามารถใช้ฟังก์ชัน Excel ที่มีอยู่แล้วภายในสูตรอาร์เรย์ของคุณได้
ตัวอย่างเช่น สูตรนี้จะคํานวณค่าผลรวมของอาร์เรย์ของราคาหุ้นและหุ้น และวางผลลัพธ์ในเซลล์ถัดจาก "ค่าทั้งหมด"
สูตรจะคูณการใช้ร่วมกัน (เซลล์ B2 – F2) ด้วยราคา (เซลล์ B3 – F3) แล้วบวกผลลัพธ์เหล่านั้นเพื่อสร้างผลรวมทั้งหมดของ 35,525 นี่คือตัวอย่างของสูตรอาร์เรย์เซลล์เดียวเนื่องจากสูตรอยู่ในเซลล์เดียว
กด Enter (ถ้าคุณมีการสมัครใช้งาน Microsoft 365 ปัจจุบัน) หรือกด Ctrl+Shift+Enter
เมื่อคุณกด Ctrl+Shift+Enter Excel จะแทรกสูตรระหว่าง { } โดยอัตโนมัติ (วงเล็บปีกกาเปิดและวงเล็บปีกกาปิด)หมายเหตุ
ถ้าคุณมี Microsoft 365 เวอร์ชันปัจจุบัน คุณสามารถใส่สูตรในเซลล์ด้านบนซ้ายของช่วงผลลัพธ์ แล้วกด ENTER เพื่อยืนยันสูตรเป็นสูตรอาร์เรย์แบบไดนามิก มิฉะนั้น ต้องใส่สูตรเป็นสูตรอาร์เรย์ดั้งเดิม โดยเลือกช่วงผลลัพธ์ก่อน ใส่สูตรในเซลล์ด้านซ้ายบนของช่วงผลลัพธ์ แล้วกด CTRL+SHIFT+ENTER เพื่อยืนยัน Excel จะแทรกวงเล็บปีกกาที่จุดเริ่มต้นและจุดสิ้นสุดของสูตรให้คุณ สำหรับข้อมูลเพิ่มเติมเกี่ยวกับสูตรอาร์เรย์ ให้ดูที่ คำแนะนำและตัวอย่างของสูตรอาร์เรย์
สร้างสูตรอาร์เรย์ที่คํานวณหลายผลลัพธ์
เมื่อต้องการคํานวณหลายผลลัพธ์โดยใช้สูตรอาร์เรย์ ให้ใส่อาร์เรย์ในช่วงของเซลล์ที่มีจํานวนแถวและคอลัมน์เท่ากันที่คุณจะใช้ในอาร์กิวเมนต์อาร์เรย์
เลือกช่วงของเซลล์ที่คุณต้องการใส่สูตรอาร์เรย์
ใส่สูตรที่คุณต้องการใช้
สูตรอาร์เรย์จะใช้ไวยากรณ์ของสูตรมาตรฐาน โดยทั้งหมดจะเริ่มต้นด้วยเครื่องหมายเท่ากับ (=) และคุณสามารถใช้ฟังก์ชัน Excel ที่มีอยู่แล้วภายในสูตรอาร์เรย์ของคุณได้
ในตัวอย่างต่อไปนี้ สูตรคูณการใช้ร่วมกันตามราคาในแต่ละคอลัมน์ และสูตรจะอยู่ในเซลล์ที่เลือกในแถวที่ 5
กด Enter (ถ้าคุณมีการสมัครใช้งาน Microsoft 365 ปัจจุบัน) หรือกด Ctrl+Shift+Enter
เมื่อคุณกด Ctrl+Shift+Enter Excel จะแทรกสูตรระหว่าง { } โดยอัตโนมัติ (วงเล็บปีกกาเปิดและวงเล็บปีกกาปิด)หมายเหตุ
ถ้าคุณมี Microsoft 365 เวอร์ชันปัจจุบัน คุณสามารถใส่สูตรในเซลล์ด้านบนซ้ายของช่วงผลลัพธ์ แล้วกด ENTER เพื่อยืนยันสูตรเป็นสูตรอาร์เรย์แบบไดนามิก มิฉะนั้น ต้องใส่สูตรเป็นสูตรอาร์เรย์ดั้งเดิม โดยเลือกช่วงผลลัพธ์ก่อน ใส่สูตรในเซลล์ด้านซ้ายบนของช่วงผลลัพธ์ แล้วกด CTRL+SHIFT+ENTER เพื่อยืนยัน Excel จะแทรกวงเล็บปีกกาที่จุดเริ่มต้นและจุดสิ้นสุดของสูตรให้คุณ สำหรับข้อมูลเพิ่มเติมเกี่ยวกับสูตรอาร์เรย์ ให้ดูที่ คำแนะนำและตัวอย่างของสูตรอาร์เรย์
ถ้าคุณต้องการรวมข้อมูลใหม่ในสูตรอาร์เรย์ของคุณ ให้ดู ขยายสูตรอาร์เรย์ คุณยังสามารถลองทําสิ่งต่อไปนี้ได้:
- กฎสําหรับการเปลี่ยนสูตรอาร์เรย์ (สามารถมีได้จํากัด)
- ลบสูตรอาร์เรย์ (คุณกด Ctrl+Shift+Enter ที่นั่นด้วย)
- ใช้ค่าคงที่อาร์เรย์ในสูตรอาร์เรย์ (อาจมีประโยชน์)
- ตั้งชื่อค่าคงที่อาร์เรย์ (ทําให้ใช้ค่าคงที่ได้ง่ายขึ้น)
คุณควรลองทำดู
ถ้าคุณต้องการลองใช้ค่าคงที่อาร์เรย์ก่อนที่จะลองใช้กับข้อมูลของคุณเอง คุณสามารถใช้ข้อมูลตัวอย่างได้ที่นี่
เวิร์กบุ๊กด้านล่างแสดงตัวอย่างของสูตรอาร์เรย์ เมื่อต้องการทํางานกับตัวอย่างที่ดีที่สุด คุณควรดาวน์โหลดเวิร์กบุ๊กลงในคอมพิวเตอร์ของคุณโดยการคลิกไอคอน Excel ที่มุมล่างขวา แล้วเปิดในโปรแกรม Excel บนเดสก์ท็อป
คัดลอกตารางด้านล่างและวางลงใน Excel ในเซลล์ A1 ตรวจสอบให้แน่ใจว่าเลือกเซลล์ E2:E11 ใส่สูตร =C2:C11*D2:D11 แล้วกด Ctrl+Shift+Enter เพื่อทําให้เป็นสูตรอาร์เรย์
| พนักงานขาย | ประเภทรถ | จํานวนที่ขายได้ | ราคาต่อหน่วย | ยอดขายรวม |
|---|---|---|---|---|
| ชาติ | ซีดาน | 5 | 2200 | =C2:C11*D2:D11 |
| คูเป้ | 4 | 1800 | ||
| สัญญา | ซีดาน | 6 | 2300 | |
| คูเป้ | 8 | 1700 | ||
| มโน | ซีดาน | 3 | 2000 | |
| คูเป้ | 1 | 1600 | ||
| พัชระ | ซีดาน | 9 | 2150 | |
| คูเป้ | 5 | 1950 | ||
| สุเชาว์ | ซีดาน | 6 | 2250 | |
| คูเป้ | 8 | 2000 |
สร้างสูตรอาร์เรย์หลายเซลล์
- ในเวิร์กบุ๊กตัวอย่าง ให้เลือกเซลล์ E2 ถึง E11 เซลล์เหล่านี้จะมีผลลัพธ์ของคุณ
คุณจะเลือกเซลล์ที่มีผลลัพธ์ของคุณเสมอก่อนที่คุณจะใส่สูตร
และโดยเสมอเราหมายถึง 100 เปอร์เซ็นต์ของเวลา
- ใส่สูตรนี้ เมื่อต้องการใส่ในเซลล์ เพียงเริ่มพิมพ์ (กดเครื่องหมายเท่ากับ) และสูตรจะปรากฏในเซลล์สุดท้ายที่คุณเลือก คุณยังสามารถใส่สูตรในแถบสูตร:
=C2:C11*D2:D11 - กด Ctrl+Shift+Enter
สร้างสูตรอาร์เรย์เซลล์เดียว
- ในเวิร์กบุ๊กตัวอย่าง ให้คลิกเซลล์ B13
- ใส่สูตรนี้โดยใช้วิธีใดวิธีหนึ่งจากขั้นตอนที่ 2 ด้านบน:
=SUM(C2:C11*D2:D11) - กด Ctrl+Shift+Enter
สูตรจะคูณค่าในช่วงเซลล์ C2:C11 และ D2:D11 แล้วเพิ่มผลลัพธ์เพื่อคํานวณผลรวมทั้งหมด
ต้องการความช่วยเหลือเพิ่มเติมไหม
คุณสามารถสอบถามผู้เชี่ยวชาญใน ชุมชนด้านเทคนิคของ Excel หรือรับการสนับสนุนใน ชุมชนได้เสมอ