สูตรอาร์เรย์เป็นสูตรที่สามารถทําการคํานวณได้หลายอย่างกับรายการอย่างน้อยหนึ่งรายการในอาร์เรย์ คุณสามารถมองได้ว่าอาร์เรย์เป็นแถวหรือคอลัมน์ของค่า หรือการผสมผสานระหว่างแถวและคอลัมน์ของค่า สูตรอาร์เรย์สามารถส่งกลับผลลัพธ์หลายรายการหรือส่งกลับผลลัพธ์เดียวก็ได้
เริ่มต้นด้วยการอัปเดตประจําเดือนกันยายน 2018 สําหรับ Microsoft 365 สูตรใดๆ ที่สามารถส่งกลับผลลัพธ์ได้หลายรายการจะสปิลล์ลงหรือข้ามไปยังเซลล์ข้างเคียงโดยอัตโนมัติ การเปลี่ยนแปลงลักษณะการทํางานนี้ยังมาพร้อมกับ ฟังก์ชันอาร์เรย์แบบไดนามิกใหม่หลายอย่าง สูตรอาร์เรย์แบบไดนามิก ไม่ว่าจะใช้ฟังก์ชันที่มีอยู่หรือฟังก์ชันอาร์เรย์แบบไดนามิก จะต้องป้อนลงในเซลล์เดียวเท่านั้น จากนั้นยืนยันด้วยการกด Enter ก่อนหน้านี้ สูตรอาร์เรย์ดั้งเดิมจําเป็นต้องเลือกช่วงผลลัพธ์ทั้งหมดก่อน จากนั้นยืนยันสูตรด้วย Ctrl+Shift+Enter โดยทั่วไปเรียกว่าสูตร CSE
คุณสามารถใช้สูตรอาร์เรย์ในการทํางานที่ซับซ้อนต่างๆ ได้ เช่น:
- สร้างชุดข้อมูลตัวอย่างอย่างรวดเร็ว
- นับจํานวนอักขระที่มีอยู่ในช่วงของเซลล์
- รวมเฉพาะตัวเลขที่ตรงตามเงื่อนไขบางอย่าง เช่น ค่าต่ําสุดในช่วง หรือตัวเลขที่อยู่ระหว่างขอบเขตบนและขอบเขตล่าง
- รวมค่าอันดับที่ N ทุกตัวที่อยู่ในช่วงของค่า
ตัวอย่างต่อไปนี้จะแสดงวิธีการสร้างสูตรอาร์เรย์หลายเซลล์และสูตรอาร์เรย์เซลล์เดียว ในกรณีที่เป็นไปได้ เราได้รวมตัวอย่างที่มีฟังก์ชันอาร์เรย์แบบไดนามิกบางฟังก์ชัน รวมถึงสูตรอาร์เรย์ที่มีอยู่ซึ่งใส่เป็นอาร์เรย์แบบไดนามิกและอาร์เรย์ดั้งเดิม
ดาวน์โหลดตัวอย่างของเรา
ดาวน์โหลดตัวอย่างเวิร์กบุ๊กที่มีตัวอย่างสูตรอาร์เรย์ทั้งหมดในบทความนี้
อาร์เรย์แบบหลายเซลล์และแบบเซลล์เดียว
แบบฝึกหัดนี้จะแสดงให้คุณเห็นวิธีการใช้สูตรอาร์เรย์หลายเซลล์และสูตรอาร์เรย์เซลล์เดียวในการคํานวณชุดของตัวเลขยอดขาย ขั้นตอนชุดแรกจะใช้สูตรหลายเซลล์ในการคํานวณชุดผลรวมย่อย ชุดที่สองจะใช้สูตรเซลล์เดียวในการคํานวณผลรวมทั้งหมด
สูตรอาร์เรย์หลายเซลล์
ที่นี่เรากําลังคํานวณยอดขายรวมของคูเป้และซีดานสําหรับพนักงานขายแต่ละคนด้วยการใส่ =F10:F19*G10:G19 ในเซลล์ H10
เมื่อคุณกด Enter คุณจะเห็นผลลัพธ์ที่สปิลล์ลงในเซลล์ H10:H19 โปรดสังเกตว่าช่วงสปิลล์จะถูกเน้นด้วยเส้นขอบเมื่อคุณเลือกเซลล์ใดๆ ภายในช่วงสปิลล์ คุณอาจสังเกตเห็นว่าสูตรในเซลล์ H10:H19 เป็นสีเทา สูตรเหล่านี้มีไว้สําหรับใช้อ้างอิงเท่านั้น ดังนั้นถ้าคุณต้องการปรับสูตร คุณจะต้องเลือกเซลล์ H10 ซึ่งมีสูตรหลักอยู่สูตรอาร์เรย์เซลล์เดียว
ในเซลล์ H20 ของเวิร์กบุ๊กตัวอย่าง ให้พิมพ์หรือคัดลอกแล้ววาง =SUM(F10:F19*G10:G19) จากนั้นกด Enter
ในกรณีนี้ Excel จะคูณค่าต่างๆ ในอาร์เรย์ (ช่วงเซลล์ตั้งแต่ F10 ถึง G19) แล้วใช้ฟังก์ชัน SUM เพื่อบวกผลรวมเข้าด้วยกัน ผลลัพธ์ที่ได้คือยอดขายรวมทั้งหมด $1,590,000
ตัวอย่างนี้แสดงให้เห็นถึงประสิทธิภาพการทํางานของสูตรชนิดนี้ ตัวอย่างเช่น สมมติว่าคุณมีแถวข้อมูล 1,000 แถว คุณสามารถรวมบางส่วนหรือทั้งหมดของข้อมูลได้ด้วยการสร้างสูตรอาร์เรย์ในเซลล์เดียวแทนที่จะลากสูตรลงมาคลุมทั้ง 1,000 แถว นอกจากนั้น คุณจะสังเกตเห็นว่าสูตรเซลล์เดียวในเซลล์ H20 จะไม่ขึ้นอยู่กับสูตรหลายเซลล์ (สูตรในเซลล์ H10 ถึง H19) แต่อย่างใด นี่คือข้อดีอีกประการหนึ่งของการใช้สูตรอาร์เรย์ ซึ่งก็คือความยืดหยุ่นนั่นเอง คุณสามารถเปลี่ยนสูตรอื่นในคอลัมน์ H ได้โดยไม่ส่งผลต่อสูตรใน H20 นอกจากนี้ยังควรมีค่าผลรวมที่เป็นอิสระเช่นนี้ เนื่องจากจะช่วยตรวจสอบความถูกต้องของผลลัพธ์ของคุณนอกจากนี้ สูตรอาร์เรย์แบบไดนามิกยังมีข้อดีต่างๆ ดังต่อไปนี้
- ความสอดคล้องกัน ถ้าคุณคลิกเซลล์ใดๆ ตั้งแต่ H10 ลงมา คุณจะเห็นสูตรเดียวกัน ความสอดคล้องกันนั้นสามารถช่วยให้มั่นใจในความถูกต้องแม่นยํายิ่งขึ้น
- ความปลอดภัย คุณไม่สามารถเขียนทับคอมโพเนนต์ของสูตรอาร์เรย์แบบหลายเซลล์ได้ ตัวอย่างเช่น คลิกเซลล์ H11 แล้วกด Delete Excel จะไม่เปลี่ยนแปลงผลลัพธ์ของอาร์เรย์ เมื่อต้องการเปลี่ยน คุณต้องเลือกเซลล์ด้านบนซ้ายในอาร์เรย์ หรือเซลล์ H10
- ขนาดไฟล์ที่เล็กลง คุณสามารถใช้สูตรอาร์เรย์เดียวแทนสูตรขั้นกลางหลายๆ สูตรได้ ตัวอย่างเช่น ตัวอย่างยอดขายรถยนต์ ใช้สูตรอาร์เรย์เดียวในการคํานวณผลลัพธ์ในคอลัมน์ E ถ้าคุณใช้สูตรมาตรฐาน เช่น =F10*G10, F11*G11, F12*G12 เป็นต้น คุณจะใช้สูตรที่แตกต่างกัน 11 สูตรในการคํานวณผลลัพธ์เดียวกัน นั่นไม่ใช่เรื่องใหญ่ แต่จะเกิดอะไรขึ้นถ้าคุณมีแถวทั้งหมดเป็นพันแถวล่ะ จากนั้นมันสามารถสร้างความแตกต่างที่ยิ่งใหญ่ได้
- ประสิทธิภาพ ฟังก์ชันอาร์เรย์อาจเป็นวิธีที่มีประสิทธิภาพในการสร้างสูตรที่ซับซ้อน สูตรอาร์เรย์ =SUM(F10:F19*G10:G19) เหมือนกันกับ =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19)
- สปิลล์ สูตรอาร์เรย์แบบไดนามิกจะสปิลล์ลงในช่วงผลลัพธ์โดยอัตโนมัติ ถ้าข้อมูลต้นฉบับของคุณอยู่ในตาราง Excel สูตรอาร์เรย์แบบไดนามิกของคุณจะปรับขนาดโดยอัตโนมัติตามที่คุณเพิ่มหรือเอาข้อมูลออก
- ข้อผิดพลาด #SPILL! ข้อผิดพลาด อาร์เรย์แบบไดนามิกทําให้เกิดข้อผิดพลาด #SPILL! ซึ่งระบุว่าช่วงสปิลล์ที่กําหนดไว้ถูกบล็อกด้วยเหตุผลบางประการ เมื่อคุณแก้ไขการบล็อก สูตรจะสปิลล์โดยอัตโนมัติ
สร้างค่าคงที่อาร์เรย์หนึ่งและสองมิติ
ค่าคงที่อาร์เรย์คือคอมโพเนนต์ของสูตรอาร์เรย์ คุณสร้างค่าคงที่อาร์เรย์โดยการใส่รายการข้อมูล แล้วใส่วงเล็บปีกกา ({ }) คร่อมรายการนั้นด้วยตนเอง เช่น
={1,2,3,4,5} หรือ ={"มกราคม","กุมภาพันธ์","มีนาคม"}
ถ้าคุณแยกข้อมูลโดยใช้เครื่องหมายจุลภาค คุณจะสร้างอาร์เรย์แนวนอน (แถว) ถ้าคุณแยกข้อมูลโดยใช้เครื่องหมายอัฒภาค นั่นคือ คุณจะสร้างอาร์เรย์แนวตั้ง (คอลัมน์) ในการสร้างอาร์เรย์สองมิติ ให้คุณคั่นข้อมูลในแต่ละแถวด้วยเครื่องหมายจุลภาค และคั่นแถวแต่ละแถวด้วยเครื่องหมายอัฒภาค
ขั้นตอนต่อไปนี้จะให้คุณฝึกสร้างค่าคงที่แนวนอน ค่าคงที่แนวตั้ง และค่าคงที่สองมิติ เราจะแสดงตัวอย่างโดยใช้ ฟังก์ชัน SEQUENCE เพื่อสร้างค่าคงที่อาร์เรย์โดยอัตโนมัติ เช่นเดียวกับค่าคงที่อาร์เรย์ที่ป้อนด้วยตนเอง
-
สร้างค่าคงที่แนวนอน
ใช้เวิร์กบุ๊กจากตัวอย่างก่อนหน้านี้ หรือสร้างเวิร์กบุ๊กใหม่ เลือกเซลล์ว่างใดๆ แล้วใส่ =SEQUENCE(1,5) ฟังก์ชัน SEQUENCE จะสร้างอาร์เรย์ขนาด 1 แถวคูณ 5 คอลัมน์เหมือนกับ ={1,2,3,4,5} ผลลัพธ์ต่อไปนี้จะแสดงขึ้น:
-
สร้างค่าคงที่แนวตั้ง
เลือกเซลล์ว่างใดๆ ที่มีที่ว่างอยู่ข้างใต้ แล้วใส่ =SEQUENCE(5) หรือ ={1; 2; 3; 4; 5}. ผลลัพธ์ต่อไปนี้จะแสดงขึ้น:
-
สร้างค่าคงที่สองมิติ
เลือกเซลล์เปล่าใดๆ ที่มีที่ว่างทางด้านขวาและด้านล่าง แล้วใส่ =SEQUENCE(3,4) คุณจะเห็นผลลัพธ์ที่ได้ดังนี้
คุณยังสามารถใส่: หรือ ={1,2,3,4; 5,6,7,8; 9,10,11,12} แต่คุณจะต้องใส่ใจกับตําแหน่งที่คุณใส่เครื่องหมายอัฒภาคกับจุลภาค
ตามที่คุณเห็น ตัวเลือก SEQUENCE มีข้อได้เปรียบที่สําคัญกว่าการใส่ค่าคงที่อาร์เรย์ของคุณด้วยตนเอง โดยหลักๆ แล้ว จะช่วยให้คุณประหยัดเวลา แต่ยังช่วยลดข้อผิดพลาดจากการใส่ข้อมูลด้วยตนเองได้อีกด้วย นอกจากนี้ ยังง่ายต่อการอ่าน โดยเฉพาะอย่างยิ่งเมื่อเครื่องหมายอัฒภาคอาจแยกความแตกต่างได้ยากจากตัวคั่นด้วยเครื่องหมายจุลภาค
ไวยากรณ์ของค่าคงที่อาร์เรย์
ต่อไปนี้คือตัวอย่างที่ใช้ค่าคงที่อาร์เรย์เป็นส่วนหนึ่งของสูตรที่ใหญ่กว่า ในเวิร์กบุ๊กตัวอย่าง ให้ไปที่ ค่าคงที่ใน เวิร์กชีตสูตร หรือสร้างเวิร์กชีตใหม่
ในเซลล์ D9 เราใส่ =SEQUENCE(1,5,3,1) แต่คุณยังสามารถใส่ 3, 4, 5, 6 และ 7 ในเซลล์ A9:H9 ได้ ไม่มีอะไรพิเศษเกี่ยวกับการเลือกหมายเลขนั้น เราแค่เลือกอย่างอื่นที่ไม่ใช่ 1-5 เพื่อแยกความแตกต่าง
ในเซลล์ E11 ให้ใส่ =SUM(D9:H9*SEQUENCE(1,5)), or =SUM(D9:H9*{1,2,3,4,5}) สูตรจะส่งกลับค่า 85
ฟังก์ชัน SEQUENCE จะสร้างค่าที่เทียบเท่ากับค่าคงที่ {1,2,3,4,5}อาร์เรย์ เนื่องจาก Excel จะดําเนินการกับนิพจน์ที่อยู่ในวงเล็บก่อน องค์ประกอบอีกสององค์ประกอบถัดไปที่จะดําเนินการจึงคือค่าของเซลล์ใน D9:H9 และตัวดําเนินการคูณ (*) สูตรจะคูณค่าที่อยู่ในอาร์เรย์ที่เก็บไว้กับค่าที่สอดคล้องกันในค่าคงที่ ซึ่งเทียบเท่ากับ:
=SUM(D9*1,E9*2,F9*3,G9*4,H9*5), or =SUM(3*1,4*2,5*3,6*4,7*5)
สุดท้าย ฟังก์ชัน SUM จะรวมค่าต่างๆ และแสดงผลเป็น 85
ถ้าต้องการหลีกเลี่ยงการใช้อาร์เรย์ที่เก็บไว้ และเก็บการดําเนินการไว้ในหน่วยความจําทั้งหมด คุณสามารถแทนที่ด้วยค่าคงที่อาร์เรย์อื่นได้
=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)), or =SUM({3,4,5,6,7}*{1,2,3,4,5})
องค์ประกอบที่คุณสามารถใช้ได้ในค่าคงที่อาร์เรย์
- ค่าคงที่อาร์เรย์อาจประกอบด้วยตัวเลข ข้อความ ค่าตรรกะ (เช่น TRUE และ FALSE) หรือค่าความผิดพลาด เช่น #N/A คุณสามารถใช้ตัวเลขในรูปแบบจํานวนเต็ม ทศนิยม และรูปแบบเชิงวิทยาศาสตร์ได้ ถ้าคุณใส่ข้อความเข้าไป คุณต้องกํากับข้อความด้วยเครื่องหมายอัญประกาศ ("ข้อความ")
- ค่าคงที่อาร์เรย์ไม่สามารถมีอาร์เรย์ สูตร หรือฟังก์ชันเพิ่มเติมได้ กล่าวคือ พวกเขาสามารถประกอบด้วยข้อความหรือตัวเลขเท่านั้น ซึ่งจะคั่นด้วยเครื่องหมายจุลภาคหรืออัฒภาค Excel จะแสดงข้อความเตือนเมื่อคุณใส่สูตร เช่น {1,2,A1:D4} หรือ {1,2,SUM(Q2:Z8)} นอกจากนี้ ค่าที่เป็นตัวเลขไม่สามารถมีเครื่องหมายเปอร์เซ็นต์ เครื่องหมายดอลลาร์ เครื่องหมายจุลภาค หรือวงเล็บได้
การตั้งชื่อค่าคงที่อาร์เรย์
วิธีที่ดีที่สุดในการใช้ค่าคงที่อาร์เรย์ คือการตั้งชื่อค่าคงที่อาร์เรย์ ค่าคงที่ที่ตั้งชื่อสามารถนํามาใช้งานได้ง่ายขึ้น อีกทั้งยังสามารถซ่อนความซับซ้อนบางอย่างของสูตรอาร์เรย์ของคุณไม่ให้ผู้ใช้งานคนอื่นๆ เห็นได้ด้วย เมื่อต้องการตั้งชื่อค่าคงที่อาร์เรย์และการใช้ค่าคงที่อาร์เรย์ในสูตร ให้ทําดังนี้
ไปที่สูตร กําหนด>ชื่อ>กําหนดชื่อ ในกล่อง ชื่อ ให้พิมพ์ ไตรมาส 1 ในกล่อง อ้างอิงไปยัง ให้ใส่ค่าคงที่ต่อไปนี้ (โปรดอย่าลืมพิมพ์วงเล็บปีกกาเข้าไปด้วยตัวเอง)
={"มกราคม","กุมภาพันธ์","มีนาคม"}
กล่องโต้ตอบควรจะมีลักษณะในขณะนี้
คลิก ตกลง จากนั้นเลือกแถวใดก็ได้ที่มีเซลล์เปล่าสามเซลล์ แล้วใส่ =Quarter1
ผลลัพธ์ต่อไปนี้จะแสดงขึ้น:
หากคุณต้องการให้ผลลัพธ์สปิลล์ในแนวตั้งแทนแนวนอน คุณสามารถใช้ =TRANSPOSE(Quarter1) ได้
ถ้าคุณต้องการแสดงรายการของ 12 เดือน เหมือนกับที่คุณอาจใช้ในการสร้างงบการเงิน คุณสามารถยึดตามปีปัจจุบันได้ด้วยฟังก์ชัน SEQUENCE สิ่งที่น่าประทับใจเกี่ยวกับฟังก์ชันนี้คือแม้ว่าจะแสดงเฉพาะเดือนเท่านั้น แต่ก็มีวันที่ที่ถูกต้องอยู่เบื้องหลังซึ่งคุณสามารถใช้ในการคํานวณอื่นๆ ได้ คุณจะพบตัวอย่างเหล่านี้ในเวิร์กชีตชุดข้อมูลค่า คงที่อาร์เรย์ที่มีชื่อและ ตัวอย่าง อย่างรวดเร็ว ในเวิร์กบุ๊กตัวอย่าง
=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"MMM")
ซึ่งใช้ ฟังก์ชัน DATE เพื่อสร้างวันที่ตามปีปัจจุบัน SEQUENCE จะสร้างค่าคงที่อาร์เรย์ตั้งแต่ 1 ถึง 12 สําหรับเดือนมกราคมถึงธันวาคม จากนั้น ฟังก์ชัน TEXT จะแปลงรูปแบบการแสดงผลเป็น "mmm" (ม.ค. ก.พ. มี.ค. มี.ค. เป็นต้น) ถ้าคุณต้องการแสดงชื่อเดือนแบบเต็ม เช่น มกราคม คุณควรใช้ "mmmm"
เมื่อคุณใช้ค่าคงที่ที่ตั้งชื่อเป็นสูตรอาร์เรย์ โปรดอย่าลืมใส่เครื่องหมายเท่ากับ เช่นใน =Quarter1 ไม่ใช่แค่ Quarter1 ถ้าคุณไม่ทําเช่นนั้น Excel จะตีความอาร์เรย์เป็นสตริงของข้อความและสูตรของคุณจะไม่ทํางานตามที่คาดไว้ สุดท้าย อย่าลืมว่าคุณสามารถใช้ฟังก์ชัน ข้อความ และตัวเลขรวมกันได้ ทุกอย่างขึ้นอยู่กับว่าคุณต้องการสร้างสรรค์ผลงานเพียงใด
ค่าคงที่อาร์เรย์กับการใช้งาน
ตัวอย่างต่อไปนี้จะแสดงวิธีการสองสามวิธีที่คุณสามารถใส่ค่าคงที่อาร์เรย์ที่จะใช้ในสูตรอาร์เรย์ได้ บางตัวอย่างใช้ ฟังก์ชัน TRANSPOSE ในการแปลงแถวเป็นคอลัมน์และแปลงคอลัมน์เป็นแถว
-
หลายรายการแต่ละรายการในอาร์เรย์
ใส่ =SEQUENCE(1,12)*2, or ={1,2,3,4; 5,6,7,8; 9,10,11,12}*2
คุณยังสามารถหารด้วย (/) บวกด้วย (+) และลบด้วย (-) -
การยกกำลังสองข้อมูลในอาร์เรย์
ใส่ =SEQUENCE(1,12)^2, or ={1,2,3,4; 5,6,7,8; 9,10,11,12}^2 -
ค้นหารากที่สองของรายการยกกําลังสองในอาร์เรย์
ใส่ =SQRT(SEQUENCE(1,12)^2), or =SQRT({1,2,3,4; 5,6,7,8; 9,10,11,12}^2) -
สับเปลี่ยนแถวมิติเดียว
ใส่ =TRANSPOSE(SEQUENCE(1,5)) หรือ =TRANSPOSE({1,2,3,4,5})
ถึงแม้ว่าคุณจะใส่ค่าคงที่อาร์เรย์แนวนอนเข้าไป ฟังก์ชัน TRANSPOSE ก็จะแปลงค่าคงที่อาร์เรย์เป็นคอลัมน์ -
สับเปลี่ยนคอลัมน์มิติเดียว
ป้อน =TRANSPOSE(SEQUENCE(5,1)) หรือ =TRANSPOSE({1; 2; 3; 4; 5})
ถึงแม้ว่าคุณจะใส่ค่าคงที่อาร์เรย์แนวตั้งเข้าไป ฟังก์ชัน TRANSPOSE ก็จะแปลงค่าคงที่อาร์เรย์เป็นแถว -
สับเปลี่ยนค่าคงที่สองมิติ
ป้อน =TRANSPOSE(SEQUENCE(3,4)), or =TRANSPOSE({1,2,3,4; 5,6,7,8; 9,10,11,12})
ฟังก์ชัน TRANSPOSE จะแปลงแถวแต่ละแถวให้เป็นชุดคอลัมน์
ใส่สูตรอาร์เรย์พื้นฐานสำหรับการทำงาน
ส่วนนี้จะมีตัวอย่างสูตรอาร์เรย์พื้นฐาน
สร้างอาร์เรย์จากค่าที่มีอยู่
ตัวอย่างต่อไปนี้จะอธิบายวิธีการใช้สูตรอาร์เรย์เพื่อสร้างอาร์เรย์ใหม่จากอาร์เรย์ที่มีอยู่
ใส่ =SEQUENCE(3,6,10,10) หรือ ={10,20,30,40,50,60; 70,80,90,100,110,120; 130,140,150,160,170,180}
ตรวจสอบให้แน่ใจว่าได้พิมพ์ { (วงเล็บปีกกาเปิด) ก่อนที่คุณจะพิมพ์ 10 และ } (วงเล็บปีกกาปิด) หลังจากที่คุณพิมพ์ 180 เนื่องจากคุณกําลังสร้างอาร์เรย์ของตัวเลข
ถัดไป ให้ใส่ =D9# หรือ =D9:I11 ในเซลล์ว่าง อาร์เรย์ของเซลล์ขนาด 3 x 6 จะปรากฏพร้อมกับค่าเดียวกันกับที่คุณเห็นใน D9:D11 เครื่องหมาย # เรียกว่า ตัวดําเนินการช่วงสปิลล์ และเป็นวิธีการของ Excel ในการอ้างอิงทั้งช่วงอาร์เรย์แทนที่จะต้องพิมพ์ออกมา
สร้างค่าคงที่อาร์เรย์จากค่าที่มีอยู่
คุณสามารถนําผลลัพธ์ของสูตรอาร์เรย์ที่สปิลล์มาแปลงเป็นส่วนประกอบได้ เลือกเซลล์ D9 แล้วกด F2 เพื่อเปลี่ยนเป็นโหมดแก้ไข ถัดไป ให้กด F9 เพื่อแปลงการอ้างอิงเซลล์ให้เป็นค่า ซึ่ง Excel จะแปลงเป็นค่าคงที่อาร์เรย์ เมื่อคุณกด Enter สูตร =D9# ในขณะนี้ควรเป็น ={10,20,30; 40,50,60; 70,80,90}.นับอักขระในช่วงของเซลล์
ตัวอย่างต่อไปนี้จะแสดงให้เห็นถึงวิธีการนับจํานวนอักขระในช่วงของเซลล์ ซึ่งรวมถึงช่องว่าง
=SUM(LEN(C9:C13))
ในกรณีนี้ ฟังก์ชัน LEN จะแสดงความยาวของสตริงข้อความแต่ละสตริงของแต่ละเซลล์ที่อยู่ในช่วง จากนั้นฟังก์ชัน SUM จะบวกค่าดังกล่าวเข้าด้วยกัน และแสดงผลลัพธ์ (66) ถ้าคุณต้องการทราบจํานวนอักขระโดยเฉลี่ย คุณสามารถใช้:
=AVERAGE(LEN(C9:C13))เนื้อหาของเซลล์ที่ยาวที่สุดในช่วง C9:C13
=INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)
สูตรนี้ใช้ได้เฉพาะเมื่อช่วงข้อมูลประกอบด้วยคอลัมน์ของเซลล์เดียว
มาดูสูตรกันให้ใกล้ชิดโดยเริ่มจากองค์ประกอบภายในและทํางานออกไปด้านนอก ฟังก์ชัน LEN ส่งกลับความยาวของแต่ละรายการในช่วงเซลล์ D2:D6 ฟังก์ชัน MAX จะคํานวณค่าที่มากที่สุดในบรรดารายการเหล่านั้น ซึ่งจะสอดคล้องกับสตริงข้อความที่ยาวที่สุด ซึ่งอยู่ในเซลล์ D3
นี่คือจุดที่สิ่งต่าง ๆ ซับซ้อนเล็กน้อย ฟังก์ชัน MATCH จะคํานวณออฟเซต (ตําแหน่งสัมพันธ์) ของเซลล์ที่มีสตริงข้อความที่ยาวที่สุด เมื่อต้องการทําเช่นนั้น ต้องมีสามอาร์กิวเมนต์: ค่าการค้นหา อาร์เรย์การค้นหา และชนิดที่ตรงกัน ฟังก์ชัน MATCH จะค้นหาอาร์เรย์การค้นหาสําหรับค่าการค้นหาที่ระบุ ในกรณีนี้ ค่าการค้นหาคือสตริงข้อความที่ยาวที่สุด:
MAX(LEN(C9:C13)
และสตริงดังกล่าวจะอยู่ในอาร์เรย์นี้
LEN(C9:C13)
อาร์กิวเมนต์ชนิดที่ตรงกันในกรณีนี้คือ 0 ชนิดที่ตรงกันอาจเป็นค่า 1, 0 หรือ -1- 1 - ส่งกลับค่าที่มากที่สุดซึ่งน้อยกว่าหรือเท่ากับค่าการค้นหา
- 0 - ส่งกลับค่าแรกที่เท่ากับค่าการค้นหา
- -1 - ส่งกลับค่าที่น้อยที่สุดซึ่งมากกว่าหรือเท่ากับค่าการค้นหาที่ระบุ
- ถ้าคุณละชนิดที่ตรงกัน Excel จะถือว่าเป็น 1
สุดท้าย ฟังก์ชัน INDEX จะใช้อาร์กิวเมนต์เหล่านี้: อาร์เรย์ หมายเลขแถวและหมายเลขคอลัมน์ที่อยู่ในอาร์เรย์นั้น ช่วงเซลล์ C9:C13 มีอาร์เรย์ ฟังก์ชัน MATCH มีที่อยู่เซลล์ และอาร์กิวเมนต์สุดท้าย (1) จะระบุว่าค่ามาจากคอลัมน์แรกในอาร์เรย์
ถ้าคุณต้องการรับเนื้อหาของสตริงข้อความที่เล็กที่สุด คุณจะต้องแทนที่ MAX ในตัวอย่างข้างต้นด้วย MINค้นหาค่าที่น้อยที่สุด n ในช่วง
ตัวอย่างนี้แสดงวิธีการค้นหาค่าที่น้อยที่สุดสามค่าในช่วงของเซลล์ ที่มีการสร้างอาร์เรย์ของข้อมูลตัวอย่างในเซลล์ B9:B18 ด้วย: =INT(RANDARRAY(10,1)*100) โปรดทราบว่า RANDARRAY เป็นฟังก์ชันที่เปลี่ยนแปลงได้ ดังนั้นคุณจะได้รับชุดตัวเลขสุ่มใหม่ทุกครั้งที่ Excel คํานวณ
ใส่ =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1; 2; 3})
สูตรนี้จะใช้ค่าคงที่อาร์เรย์ในการหาค่าฟังก์ชัน SMALL สามครั้ง และส่งกลับสมาชิกที่น้อยที่สุด 3 รายการในอาร์เรย์ซึ่งอยู่ในเซลล์ B9:B18 โดยที่ 3 เป็นค่าตัวแปรในเซลล์ D9 เมื่อต้องการหาค่าเพิ่มเติม คุณสามารถเพิ่มค่าในฟังก์ชัน SEQUENCE หรือเพิ่มอาร์กิวเมนต์เพิ่มเติมให้กับค่าคงที่ คุณยังสามารถใช้ฟังก์ชันอื่นกับสูตรนี้ได้ เช่น SUM หรือ AVERAGE ตัวอย่างเช่น
=SUM(SMALL(B9#,SEQUENCE(D9))
=AVERAGE(SMALL(B9#,SEQUENCE(D9))ค้นหาค่าที่มากที่สุด n ในช่วง
ในการค้นหาค่าที่มากที่สุดในช่วง คุณสามารถแทนที่ฟังก์ชัน SMALL ด้วยฟังก์ชัน LARGE นอกจากนี้ ตัวอย่างต่อไปนี้จะใช้ฟังก์ชัน ROW และ INDIRECT
ใส่ =LARGE(B9#,ROW(INDIRECT("1:3"))) หรือ =LARGE(B9:B18,ROW(INDIRECT("1:3")))
ในจุดนี้ การทราบข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชัน ROW และ INDIRECT อาจช่วยได้บ้าง คุณสามารถใช้ฟังก์ชัน ROW เพื่อสร้างอาร์เรย์ของจํานวนเต็มที่ต่อเนื่องกันได้ ตัวอย่างเช่น เลือกค่าว่างและใส่:
=ROW(1:10)
สูตรจะสร้างคอลัมน์ที่มีจํานวนเต็มต่อเนื่องกัน 10 ตัวขึ้นมา เมื่อต้องการดูปัญหาที่อาจเกิดขึ้น ให้แทรกแถวบนช่วงที่มีสูตรอาร์เรย์ (ในที่นี้คือ แทรกแถวบนแถวที่ 1) Excel จะปรับเปลี่ยนการอ้างอิงแถว และขณะนี้สูตรจะสร้างจํานวนเต็มตั้งแต่ 2 ถึง 11 เมื่อต้องการแก้ปัญหาดังกล่าว ให้คุณเพิ่มฟังก์ชัน INDIRECT ลงในสูตรดังนี้
=ROW(INDIRECT("1:10"))
ฟังก์ชัน INDIRECT จะใช้สตริงข้อความเป็นอาร์กิวเมนต์ (ซึ่งเป็นเหตุผลที่ช่วง 1:10 มีเครื่องหมายอัญประกาศคร่อมอยู่) Excel จะไม่ปรับเปลี่ยนค่าข้อความเมื่อคุณแทรกแถวหรือย้ายสูตรอาร์เรย์ ด้วยเหตุนี้ ฟังก์ชัน ROW จึงสร้างอาร์เรย์ของจํานวนเต็มที่คุณต้องการขึ้นมาเสมอ คุณสามารถใช้ SEQUENCE ได้อย่างง่ายดาย:
=SEQUENCE(10)
ลองตรวจสอบสูตรที่คุณใช้ก่อนหน้านี้ — =LARGE(B9#,ROW(INDIRECT("1:3"))) — เริ่มต้นจากวงเล็บด้านในและดําเนินการออกมาด้านนอก: ฟังก์ชัน INDIRECT จะส่งกลับชุดของค่าข้อความ ในกรณีนี้ ค่า 1 ถึง 3 ฟังก์ชัน ROW จะสร้างอาร์เรย์คอลัมน์สามเซลล์ ฟังก์ชัน LARGE ใช้ค่าในช่วงเซลล์ B9:B18 และจะถูกประเมินสามครั้ง หนึ่งครั้งสําหรับการอ้างอิงแต่ละครั้งที่ส่งกลับโดยฟังก์ชัน ROW ถ้าคุณต้องการค้นหาค่าเพิ่มเติม ให้คุณเพิ่มช่วงเซลล์ที่มากขึ้นลงในฟังก์ชัน INDIRECT สุดท้าย คุณสามารถใช้สูตรนี้กับฟังก์ชันอื่นๆ เช่น SUM และ AVERAGE เช่นเดียวกับตัวอย่าง SMALL
การจัดการกับข้อผิดพลาด
-
รวมช่วงที่มีค่าความผิดพลาด
ฟังก์ชัน SUM ใน Excel จะไม่ทํางานเมื่อคุณพยายามที่จะรวมช่วงที่มีค่าความผิดพลาด เช่น #VALUE! หรือ #N/A ตัวอย่างนี้จะแสดงให้คุณเห็นถึงวิธีการรวมค่าต่างๆ ในช่วงที่ชื่อ Data ซึ่งมีความผิดพลาดอยู่
-
=SUM(IF(ISERROR(Data),"",Data))
สูตรจะสร้างอาร์เรย์ใหม่ที่มีค่าเดิมลบด้วยค่าความผิดพลาดใดๆ เริ่มจากฟังก์ชันภายในและทํางานภายนอก ฟังก์ชัน ISERROR จะค้นหาข้อผิดพลาดในช่วงเซลล์ (ข้อมูล) ฟังก์ชัน IF จะส่งกลับค่าเฉพาะถ้าเงื่อนไขที่คุณระบุประเมินเป็น TRUE และส่งกลับค่าอื่นถ้าเงื่อนไขประเมินเป็น FALSE ในกรณีนี้ จะส่งกลับสตริงว่าง ("") สําหรับค่าความผิดพลาดทั้งหมดเนื่องจากประเมินเป็น TRUE และส่งคืนค่าที่เหลือจากช่วง (ข้อมูล) เนื่องจากประเมินเป็น FALSE ซึ่งหมายความว่าไม่มีค่าความผิดพลาด จากนั้นฟังก์ชัน SUM จะคํานวณผลรวมสําหรับอาร์เรย์ที่กรองแล้ว -
นับจำนวนค่าความผิดพลาดในช่วง
ตัวอย่างนี้จะเหมือนกับสูตรก่อนหน้านี้ แต่จะแสดงจํานวนค่าความผิดพลาดในช่วงที่ชื่อ Data แทนที่จะกรองออกไป
=SUM(IF(ISERROR(Data),1,0))
สูตรนี้จะสร้างอาร์เรย์ที่มีค่า 1 สําหรับเซลล์ที่มีความผิดพลาด และสร้างค่า 0 สําหรับเซลล์ที่ไม่มีความผิดพลาด คุณสามารถลดความซับซ้อนของสูตรลงและจะได้รับผลลัพธ์แบบเดียวกันได้ด้วยการย้ายอาร์กิวเมนต์ที่สามสําหรับฟังก์ชัน IF ดังนี้
=SUM(IF(ISERROR(Data),1))
ถ้าคุณไม่ระบุอาร์กิวเมนต์ ฟังก์ชัน IF จะแสดงค่า FALSE ในกรณีที่เซลล์ไม่มีค่าความผิดพลาด คุณสามารถลดความซับซ้อนของสูตรได้อีก ดังนี้
=SUM(IF(ISERROR(Data)*1))
เวอร์ชันนี้ใช้งานได้ เนื่องจาก TRUE*1=1 และ FALSE*1=0
รวมค่าตามเงื่อนไข
คุณอาจต้องการรวมค่าต่างๆ ตามเงื่อนไข
ตัวอย่างเช่น สูตรอาร์เรย์นี้จะรวมเฉพาะจํานวนเต็มบวกในช่วงที่ชื่อ Sales ซึ่งแทนเซลล์ E9:E24 ในตัวอย่างด้านบน
=SUM(IF(Sales>0,Sales))
ฟังก์ชัน IF จะสร้างอาร์เรย์ของค่าบวกและค่าเท็จ ฟังก์ชัน SUM จะละเว้นค่า False เนื่องจาก 0+0=0 ช่วงเซลล์ที่คุณใช้ในสูตรนี้อาจประกอบด้วยจํานวนแถวและคอลัมน์ใดๆ
นอกจากนี้ คุณยังสามารถรวมค่าต่างๆ ที่ตรงกับเงื่อนไขมากกว่าหนึ่งเงื่อนไขได้ด้วย ตัวอย่างเช่น สูตรอาร์เรย์นี้จะคํานวณค่าที่มากกว่า 0 และ น้อยกว่า 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 ประกอบด้วยค่าสูงสุดที่เหมือนกัน สูตรจะแสดงแถวของค่าแรก
ถ้าคุณต้องการให้แสดงที่อยู่เซลล์ตามจริงของค่าสูงสุด ให้ใช้สูตรต่อไปนี้
=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))
คุณจะพบตัวอย่างที่คล้ายกันในเวิร์กบุ๊กตัวอย่างบนเวิร์กชีตความแตกต่างระหว่างชุดข้อมูล
การตอบรับ
บางส่วนของบทความนี้อ้างอิงจากชุดคอลัมน์ Excel Power User ที่เขียนโดย Colin Wilcox และดัดแปลงมาจากบทที่ 14 และ 15 ของ Excel 2002 Formulas ซึ่งเป็นหนังสือที่เขียนโดย John Walkenbach อดีต Excel MVP
ต้องการความช่วยเหลือเพิ่มเติมไหม
คุณสามารถสอบถามผู้เชี่ยวชาญใน ชุมชนด้านเทคนิคของ Excel หรือรับการสนับสนุนใน ชุมชนได้เสมอ
ดูเพิ่มเติม
ลักษณะการทำงานของอาร์เรย์แบบไดนามิกและอาร์เรย์ที่กระจายตัว
สูตรอาร์เรย์แบบไดนามิกเปรียบเทียบกับสูตรอาร์เรย์ CSE แบบดั้งเดิม