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

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

หากต้องการสร้างอาร์เรย์คงที่ ให้ป้อนองค์ประกอบและล้อมรอบอาร์เรย์ไว้ วงเล็บปีกกา- ตัวอย่างเช่น รูปภาพด้านล่างแสดงอาร์เรย์ที่ประกอบด้วยค่าคงที่ 6 ค่า:

สามารถใช้อาร์เรย์ดังกล่าวได้ สูตร Excel- ตัวอย่างเช่น สูตรต่อไปนี้จะรวมค่าของอาร์เรย์นี้:

ผลรวม((1,2,3,4,5,6))

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

สูงสุด((1,2,3,4,5,6)+(7,8,9,10,11,12))

อาร์เรย์คงที่สามารถประกอบด้วยตัวเลข ข้อความ บูลีน และค่าความผิดพลาด #ไม่มี:

=(12;"ข้อความ";TRUE;FALSE;#N/A)

คุณอาจมีคำถามที่สมเหตุสมผล: เหตุใดจึงจำเป็นต้องมีอาร์เรย์ดังกล่าว ฉันจะตอบสิ่งนี้เป็นตัวอย่าง

ตัวอย่างการใช้อาร์เรย์ของค่าคงที่ใน Excel

รูปด้านล่างแสดงรายชื่อนักเรียนที่ได้รับคะแนนตามที่กำหนด:

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

=("";"ไม่น่าพอใจ";"น่าพอใจ";"ดี";"ดีเยี่ยม")

ในกรณีนี้ องค์ประกอบแรกของอาร์เรย์ประกอบด้วยสตริงว่าง เนื่องจากจะถือว่าไม่มีคะแนนเป็น 1

จากนั้นสูตรที่ส่งคืนผลลัพธ์ที่เราต้องการจะมีลักษณะดังนี้:

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

สูตรนี้ไม่ใช่สูตรอาร์เรย์ แม้ว่าจะมีอาร์เรย์ก็ตาม ดังนั้นเมื่อเข้าไปแล้วก็แค่กดปุ่ม เข้า.

แน่นอนเราสามารถคัดลอกได้ สูตรนี้เข้าไปในเซลล์ที่เหลือและรับผลลัพธ์ที่เราต้องการ:

แต่การใช้สูตรอาร์เรย์หลายเซลล์จะฉลาดกว่า มันจะมีลักษณะเช่นนี้:

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

อย่าลืมใส่เครื่องหมายเท่ากับในช่องด้วย พิสัยมิฉะนั้น Excel จะถือว่าอาร์เรย์เป็นสตริงข้อความ

ตอนนี้สูตรดูน่ากลัวน้อยลง:

อย่างที่คุณเห็น ในบางกรณี อาร์เรย์ของค่าคงที่อาจมีประโยชน์มากด้วยซ้ำ

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

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

ค่าคงที่สามารถใช้ได้ทั้งในสูตรอาร์เรย์และแยกจากกัน

การใช้ค่าคงที่เพื่อป้อนค่าในคอลัมน์

หากต้องการป้อนค่าในคอลัมน์เดียว เช่น เซลล์สามเซลล์ในคอลัมน์ C ให้ทำดังนี้

การใช้ค่าคงที่เพื่อป้อนค่าในสตริง

หากต้องการป้อนค่าอย่างรวดเร็วในแถวเดียว เช่น เซลล์ F1, G1 และ H1 ให้ทำดังนี้

การใช้ค่าคงที่เพื่อป้อนค่าในหลายคอลัมน์และแถว

การใช้ค่าคงที่ในสูตร

ตอนนี้คุณคุ้นเคยกับค่าคงที่อาร์เรย์แล้ว มาดูตัวอย่างในทางปฏิบัติกันดีกว่า

พิมพ์หรือคัดลอกและวางสูตรต่อไปนี้ลงในเซลล์ว่าง จากนั้นกด CTRL+SHIFT+ENTER:

=SUM(A1:E1*(1,2,3,4,5))

ค่าปรากฏในเซลล์ A3 85 .

เกิดอะไรขึ้น คุณคูณค่าในเซลล์ A1 ด้วย 1, ค่าในเซลล์ B2 ด้วย 2 และอื่นๆ ไปเรื่อยๆ จากนั้นใช้ฟังก์ชัน SUM เพื่อเพิ่มผลลัพธ์ คุณสามารถป้อนสูตรเดียวกันในแบบฟอร์มได้ =ผลรวม(A1*1,B1*2,C1*3,D1*4,E1*5).

และหากต้องการคุณสามารถป้อนค่าทั้งสองชุดเป็นค่าคงที่อาร์เรย์ได้:

=ผลรวม((3,4,5,6,7)*(1,2,3,4,5))

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

หมายเหตุ:หากค่าคงที่ไม่ทำงาน

คำอธิบายตัวแปร(var) ระบุขนาดและประเภทของส่วนประกอบ

มุมมองทั่วไปของคำอธิบายของอาร์เรย์ (หนึ่งมิติ):

อาร์เรย์[<тип_индексов> ] 3เราขอเตือนคุณว่าวงเล็บเหลี่ยมตัวหนาคือ องค์ประกอบบังคับไวยากรณ์ของ<тип_компонент>;

ส่วนใหญ่มักตีความดังนี้:

อาร์เรย์[<левая_граница>..<правая_граница>] ของ<тип_компонент>;

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

var a1: อาร์เรย์ของจำนวนเต็ม;

การนับเลข

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

var a1: อาร์เรย์ [-5..4] ของจำนวนเต็ม;

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

พิมพ์ char = "a", "c".."z";

(- ไม่มีอักขระ "b") var a1: อาร์เรย์ของจำนวนเต็ม;

- 256 ส่วนประกอบ

a2: อาร์เรย์ของจำนวนเต็ม;

- 256 ส่วนประกอบทั้งหมด a3: อาร์เรย์ของจริง;- ส่วนประกอบจริง 256 ชิ้น

ขนาดรวมของอาร์เรย์ไม่ควรเกิน 65,520 ไบต์ ดังนั้นพยายามตั้งค่าอาร์เรย์เป็น a4:arrayof byte ; จะล้มเหลวเนื่องจากประเภทจำนวนเต็มครอบคลุม 65,535

องค์ประกอบต่างๆ- และในกรณีนี้ จะเป็นการดีกว่าที่จะไม่จำประเภท longint เลย

ประเภทส่วนประกอบอาเรย์สามารถเป็นอะไรก็ได้:

var a4: อาร์เรย์ของจริง;- อาร์เรย์ของส่วนประกอบประเภทง่าย ๆ

a5: อาร์เรย์ของ record1;

- อาร์เรย์ของบันทึก 4Sm การบรรยาย 7 - a6: อาร์เรย์[-10..10] ของ ^string; - อาร์เรย์ของพอยน์เตอร์ขนาด 5 ซม. การบรรยายที่ 12. บนบรรทัด

a7: อาร์เรย์ [-1..1] ของไฟล์; - อาร์เรย์ของตัวแปรไฟล์ชื่อ 6Sm การบรรยาย 6 a8: อาร์เรย์ของอาร์เรย์ของถ่าน;

อาร์เรย์สองมิติ - (อาร์เรย์ของเวกเตอร์)เพื่อความกระชับและสะดวกสบาย

อาร์เรย์หลายมิติสามารถอธิบายให้ง่ายกว่านี้:

var a9: อาร์เรย์ของจริง; อาร์เรย์สองมิติ 10x20

a10 : อาร์เรย์ของคำ;

- อาเรย์สี่มิติ 2 x 3 x 256 x 21 ข้อจำกัดทั่วไปเกี่ยวกับขนาดอาร์เรย์ - ไม่เกิน 65,520 ไบต์ - ยังใช้กับอาร์เรย์หลายมิติด้วย จำนวนส่วนประกอบอาร์เรย์หลายมิติ

ถูกคำนวณเป็นผลคูณของ "มิติ" ทั้งหมด ดังนั้นอาร์เรย์ a9 จึงมีส่วนประกอบ 200 ชิ้น และอาร์เรย์ a10 มีส่วนประกอบ 32,256 ชิ้น

คำอธิบายของมิติตัวแปร

หากโปรแกรมของคุณจำเป็นต้องประมวลผลเมทริกซ์ 7 ตัว

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

constnn=100; var a: อาร์เรย์ของจริง;

m,n: จำนวนเต็ม;

การเข้าถึงส่วนประกอบอาร์เรย์

อาร์เรย์เป็นโครงสร้างการเข้าถึงโดยตรง ซึ่งหมายความว่าเป็นไปได้ที่จะโดยตรง (โดยไม่ต้องผ่านส่วนประกอบก่อนหน้านี้ทั้งหมด) เข้าถึงส่วนประกอบอาร์เรย์ใด ๆ ที่เราสนใจ ส่วนประกอบของอาร์เรย์เชิงเส้นมีการเข้าถึงดังนี้: 8 :

<имя_массива>[<индекс_компоненты>]

วงเล็บเหลี่ยมตัวหนาเป็นองค์ประกอบไวยากรณ์ที่จำเป็น

<имя_массива>[<индекс>,_,<индекс>]

และหลายมิติ - เช่นนี้: กฎการใช้งานดัชนี

1. เมื่อเข้าถึงส่วนประกอบอาเรย์คือ:
2. ดัชนีของส่วนประกอบอาจเป็นค่าคงที่ ตัวแปร หรือนิพจน์ที่มีการดำเนินการและการเรียกฟังก์ชัน
3. ประเภทของแต่ละดัชนีจะต้องเข้ากันได้กับประเภทที่ประกาศในการประกาศอาร์เรย์โดยเฉพาะสำหรับ "มิติ" ที่เกี่ยวข้อง คุณไม่สามารถสลับดัชนีได้

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

นี่คือบทหนึ่งจากหนังสือ: Michael Girvin Ctrl+Shift+Enter การเรียนรู้สูตรอาร์เรย์ใน Excel

ก่อนหน้านี้ในหนังสือเล่มนี้ คุณเห็นอาร์เรย์ของค่าคงที่หลายครั้ง จริงๆ แล้ว ทุกครั้งที่คุณประเมินประสิทธิภาพขององค์ประกอบสูตรโดยใช้ปุ่ม F9 คุณจะได้เห็นตัวอย่างของอาร์เรย์ของค่าคงที่ ตัวอย่างเช่น ในสูตรแรกของอาร์เรย์ที่สร้างขึ้นใน (แสดงอีกครั้งในรูปที่ 7.1) คุณสามารถไฮไลต์ D2:D5-C2:C5 แล้วกด F9 เพื่อดูอาร์เรย์ผลลัพธ์ (3;–8;7;6) อย่างไรก็ตาม ก่อนหน้านี้ หลังจากประเมินอาร์เรย์แล้ว คุณกด Ctrl+Z ทันทีเพื่อไม่ให้ฮาร์ดโค้ดอยู่ในสูตร แต่เป็นลิงก์ ถ้าคุณไม่ได้ใช้ Ctrl+Z คุณจะสร้างอาร์เรย์ของค่าคงที่ที่จะไม่เปลี่ยนแปลงหากข้อมูลต้นฉบับในเวิร์กชีตมีการเปลี่ยนแปลง ในบทนี้ คุณจะได้เรียนรู้เกี่ยวกับสถานการณ์ที่การใช้อาร์เรย์คงที่มีประโยชน์

ข้าว. 7.1. เลือกอาร์เรย์ (ซ้าย) กด F9 (ขวา) และรับอาร์เรย์ของค่าคงที่

ดาวน์โหลดบันทึกในรูปแบบหรือตัวอย่างในรูปแบบ

มาดูไวยากรณ์ที่ใช้ในการสร้างอาร์เรย์คงที่และตัวอย่างที่แสดงอาร์เรย์คงที่สามประเภทที่คุณอาจพบใน Excel(รูปที่ 7.2) ในเซลล์ A6 ให้สร้างลิงก์ไปยังอาร์เรย์ =A2:A4 แล้วกด F9 คุณจะเห็นองค์ประกอบต่างๆ มากมายล้อมรอบด้วยเครื่องหมายปีกกา ข้อความจะแสดงอยู่ในเครื่องหมายคำพูดเสมอ เครื่องหมายทวิภาค – ตัวคั่นที่ระบุว่าองค์ประกอบอยู่ใต้องค์ประกอบอื่นในคอลัมน์

ข้าว. 7.2. ช่วงเซลล์แนวตั้ง: ไฮไลต์ลิงก์ (ซ้าย) แล้วกด F9 (ขวา)

อาร์เรย์แนวนอนของค่าคงที่(รูปที่ 7.3) ในเซลล์ A6 ให้สร้างลิงก์ไปยังอาร์เรย์ =A2:B2 แล้วกด F9 คุณจะเห็นองค์ประกอบต่างๆ มากมายล้อมรอบด้วยเครื่องหมายปีกกา ข้อความจะแสดงอยู่ในเครื่องหมายคำพูดเสมอ ตัวเลขจะแสดงเป็นตัวเลขง่ายๆ อัฒภาคเป็นตัวคั่นที่ระบุว่าองค์ประกอบอยู่ในแถวเดียวกันในคอลัมน์ที่อยู่ติดกัน

ข้าว. 7.3. ช่วงเซลล์แนวนอน: ไฮไลต์ลิงก์ (ซ้าย) แล้วกด F9 (ขวา)

อาร์เรย์สี่เหลี่ยมของค่าคงที่(รูปที่ 7.4) ในเซลล์ A6 ให้สร้างลิงก์ไปยังอาร์เรย์ =A2:B4 แล้วกด F9 คุณจะเห็นองค์ประกอบต่างๆ มากมายล้อมรอบด้วยเครื่องหมายปีกกา ข้อความจะแสดงอยู่ในเครื่องหมายคำพูดเสมอ ตัวเลขจะแสดงเป็นตัวเลขง่ายๆ มีการใช้ตัวคั่นสองตัว: อัฒภาคเพื่อย้ายไปยังคอลัมน์ที่อยู่ติดกันในบรรทัดเดียวกัน และเครื่องหมายโคลอนเพื่อย้ายไปยังคอลัมน์แรกของบรรทัดใหม่

ข้าว. 7.4. ช่วงเซลล์สี่เหลี่ยม: ไฮไลต์ลิงก์ (ซ้าย) แล้วกด F9 (ขวา)

คุณอาจสงสัยว่ามีสถานการณ์ที่อาร์เรย์คงที่จะมีประโยชน์หรือไม่? มันไม่ขัดแย้งกับกฎทองของ Excel หรือไม่: หากข้อมูลต้นฉบับอาจมีการเปลี่ยนแปลง ให้วางไว้บนแผ่นงานและอ้างอิงข้อมูลภายในสูตร หากข้อมูลเดิมไม่เปลี่ยนแปลง ให้ฮาร์ดโค้ดลงในสูตร ประโยชน์ของอาร์เรย์ของค่าคงที่คือสูตรไม่จำเป็นต้องกด Ctrl+Shift+Enter ดังนั้นหากข้อมูลต้นฉบับไม่เปลี่ยนแปลง ให้ใช้อาร์เรย์คงที่

ลองพิจารณาตัวอย่างจากเกมกอล์ฟ (รูปที่ 7.5) เป้าหมายคือการหาผลรวมของผลลัพธ์ที่ดีที่สุด (ขั้นต่ำ) สามรายการ โดยไม่คำนึงถึงผลลัพธ์ที่ 4 และผลลัพธ์ที่ตามมาหากพวกเขาเท่ากับผลลัพธ์ที่สาม (เห็นได้ชัดว่าในตัวอย่างของเรา นี่เป็นกรณีของหมายเลข 70 ทุกประการ ). ในการแก้ปัญหาฟังก์ชัน SMALL นั้นสมบูรณ์แบบอาร์กิวเมนต์ k ซึ่งจะช่วยให้คุณสามารถเลือกค่าที่ต้องการได้: k = 1 จะให้ค่าต่ำสุด k = 2 - ค่าที่สอง ฯลฯ ดังที่เห็นได้ในรูป 7.5 ผลรวมของค่าที่น้อยที่สุดสามค่าสำหรับ k = 1, 2, 3 ให้คำตอบที่ถูกต้อง สูตรที่ยอดเยี่ยมซึ่งอาจมีเพียงข้อเสียเปรียบที่ต้องใช้เวลาในการเขียนหากคุณต้องการเลือกค่าขั้นต่ำ 10 ค่า

ข้าว. 7.5. การโต้แย้ง เคฟังก์ชัน SMALL ช่วยให้คุณได้รับค่าที่น้อยที่สุดอันดับที่ k

หากต้องการสร้างสูตรที่มีขนาดกะทัดรัดมากขึ้น คุณสามารถวางอาร์เรย์ในอาร์กิวเมนต์ของฟังก์ชันได้ (รูปที่ 7.6) และนี่คือจุดที่อาร์เรย์ของค่าคงที่ (1,2,3) มีประโยชน์ คุณสามารถใช้ฮาร์ดโค้ดได้เนื่องจากจำนวนค่าที่เลือกจะไม่เปลี่ยนแปลง เนื่องจาก k มีค่าสามค่า ฟังก์ชัน SMALL จะส่งกลับค่าสามค่าด้วย ซึ่งจะถูกนำมารวมกัน ในการตรวจสอบสิ่งนี้ ให้ไฮไลต์อาร์กิวเมนต์หมายเลข 1 ของฟังก์ชัน SUM แล้วกด F9 (รูปที่ 7.7) คุณจะเห็นว่าแม้ว่าค่าที่น้อยที่สุดเป็นอันดับสามจะเป็นตัวเลข 70 สองตัวที่เท่ากัน แต่ฟังก์ชัน SMALL(B3:B8,(1,2,3)) จะส่งกลับตัวเลขเพียงสามตัวเท่านั้น

หมายเหตุ: ไม่สำคัญว่าคุณจะตั้งค่าอาร์เรย์คงที่เป็นแนวนอน (1;2;3) หรือแนวตั้ง (1:2:3)

ไม่จำเป็นต้อง Ctrl+Shift+Enter เพื่อป้อนสูตร อย่างไรก็ตาม หากคุณใช้การอ้างอิงช่วงแทนอาร์เรย์ของค่าคงที่ในอาร์กิวเมนต์ k (รูปที่ 7.8) การป้อนสูตรจะต้องกด Ctrl+Shift+Enter (ไม่เช่นนั้นคุณจะได้รับข้อผิดพลาด #VALUE!) โปรดทราบว่ายังคงสามารถหลีกเลี่ยง Ctrl+Shift+Enter ได้ หากคุณใช้สูตร: =SUMPRODUCT(SMALL(B3:B8,D3:D5))

หมายเหตุ: ฟังก์ชัน HELP SMALL ไม่ได้ระบุว่าอาร์กิวเมนต์ k สามารถแสดงด้วยอาร์เรย์ของค่าคงที่ได้ นี่เป็นหลักฐานเพิ่มเติมว่ามีการจัดทำเอกสารได้ไม่ดีเพียงใด ความสามารถของ Excelเกี่ยวกับการทำงานกับสูตรอาร์เรย์ ฉันเรียนรู้เกี่ยวกับสิ่งนี้จากฟอรัม MrExcel และการวิจัยการลองผิดลองถูกเป็นเวลาหลายปี

หากคุณต้องการรวมผลลัพธ์ที่แยกจากกัน (เสมอกัน) ทั้งหมด คุณสามารถใช้ได้ ฟังก์ชั่นปกติ SUMIF ไม่ใช่สูตรอาร์เรย์ (รูปที่ 7.9)

ข้าว. 7.9. สูตรจะรวมคะแนนขั้นต่ำสามคะแนน รวมทั้งคะแนนที่เท่ากันด้วย

ตัวอย่างต่อไปนี้สาธิตการใช้ฟังก์ชัน LARGE (รูปที่ 7.10) เป้าหมายคือรวมเที่ยวบินบูมเมอแรงที่ยาวที่สุดสามเที่ยวบินสำหรับผู้เข้าร่วมแต่ละคน

จะทำอย่างไรถ้าคุณต้องการให้สูตรรวม หมายเลขตัวแปรค่าสูงสุดหรือต่ำสุด? ในรูป 7.11 แสดงสองสูตรที่สรุปค่าที่ใหญ่ที่สุดสามค่า สูตรไม่รวมส่วนที่แบ่งเป็นสาม ส่วนสูตรจะรวมส่วนที่สามด้วย ความแตกต่างระหว่างสูตรเหล่านี้กับสูตรที่กล่าวถึงข้างต้นคือตัวเลข ค่าสูงสุดไม่ได้ฮาร์ดโค้ดลงในสูตร แต่ถูกกำหนดโดยค่าในเซลล์ D3 และ D6 เมื่อตัวเลขในเซลล์ D3 และ D6 เปลี่ยนแปลง ผลรวมในเซลล์ E3 และ E6 จะเปลี่ยนไป ในรูป รูปที่ 7.12 แสดงผลรวมของค่าสูงสุดสองค่า

ข้าว. 7.12. สูตร 1 และ 2 ให้ผลลัพธ์เดียวกันสำหรับค่าสูงสุดสองค่า เนื่องจากค่าที่สองไม่มีค่าที่เหมือนกัน

สูตรอาจเข้าใจง่าย แต่สูตรมีองค์ประกอบที่คุณไม่เคยพบมาก่อน การใช้ฟังก์ชัน ROW และ INDIRECT ที่คุณสามารถสร้างได้ อาร์เรย์แบบไดนามิก ความยาวตัวแปรจากตัวเลขต่อเนื่องกัน เริ่มป้อนสูตรด้วยฟังก์ชัน INDIRECT (รูปที่ 7.13) INDIRECT ถูกตั้งโปรแกรมให้ส่งคืนการอ้างอิงที่ระบุโดยสตริงข้อความ ในกรณีของเรา องค์ประกอบอาร์กิวเมนต์ เซลล์_ลิงก์" 1: " &D3 เป็นการอ้างอิงถึงบรรทัด 1:3 หากต้องการยืนยัน ให้เลือกองค์ประกอบนี้แล้วกด F9

ข้าว. 7.13. การต่อกันของ "1:" และ D3 (ทางด้านซ้าย) ให้การอ้างอิงถึงบรรทัด "1:3" (ทางด้านขวา)

หากคุณต้องการประเมินว่าสูตรใด =INDIRECT(" 1: " &D3) ส่งคืน ให้เลือกทั้งหมดแล้วกด F9 (รูปที่ 7.14) คุณจะได้รับข้อผิดพลาด “สูตรยาวเกินไป ความยาวไม่ควรเกิน 8192 ตัวอักษร"

ข้าว. 7.14. การประเมินสูตร =INDIRECT(" 1: " &D3) ส่งกลับข้อผิดพลาด

ข้อผิดพลาดเกิดจากการที่การอ้างอิงไปยังแถว 1:3 มีเซลล์ทั้งหมดในแถว 1 ถึง 3 แผ่นงาน Excel(ตั้งแต่เวอร์ชัน 2550) 16,384 คอลัมน์ ได้แก่ สามแถวมี 49,152 เซลล์ ฟังก์ชัน INDIRECT นั้นไม่มีข้อผิดพลาด ดังนั้นหากคุณใส่เป็นอาร์กิวเมนต์ของฟังก์ชัน ROW คุณจะได้ผลลัพธ์ที่ต้องการ (รูปที่ 7.15) โปรดทราบว่าอาร์กิวเมนต์ของฟังก์ชัน STRING ต้องการค่าเดียว คุณ "สลิป" 49,152 เซลล์อ้างอิงถึงเขา จริง การอ้างอิงทั้งหมดนี้อ้างอิงถึงบรรทัด 1, 2 และ 3 ดังนั้นฟังก์ชัน ROW จะส่งกลับอาร์เรย์ (1:2:3) (ตรวจสอบให้แน่ใจด้วยการเลือกฟังก์ชัน ROW ทั้งหมดแล้วกด F9) โปรดทราบว่าตั้งแต่ เรากำลังพูดถึงเกี่ยวกับสตริง ไวยากรณ์อาร์เรย์ใช้โคลอนเป็นตัวคั่น

ข้าว. 7.15. ฟังก์ชัน ROW ส่งกลับหมายเลขบรรทัดของอาร์กิวเมนต์ลิงก์ เนื่องจาก INDIRECT ส่งคืนเซลล์ที่อยู่ในแถว 1, 2 และ 3 ฟังก์ชัน ROW จึงส่งคืนอาร์เรย์ (1:2:3)

ดังนั้นการใช้โครงสร้าง STRING(INDIRECT(...)) คุณสามารถสร้างข้อโต้แย้งได้ เคฟังก์ชั่น LARGE (รูปที่ 7.16) องค์ประกอบของสูตรนี้เป็นแบบไดนามิก ถ้าคุณป้อนค่า 2 ในเซลล์ D3 อาร์เรย์สำหรับ เคจะกลับมา (1;2) และถ้า 5 แล้ว – (1;2;3;4;5)

ข้าว. 7.16. การโต้แย้ง เคฟังก์ชัน LARGE รับอาร์เรย์ที่สร้างโดยการก่อสร้าง ROW(INDIRECT(...))

คุณสามารถพิมพ์สูตรให้เสร็จสิ้นได้โดยการวางฟังก์ชัน LARGE เป็นอาร์กิวเมนต์ อาร์เรย์1ฟังก์ชัน SUMPRODUCT ฉันชอบ SUMPRODUCT มากกว่า SUM เพราะอันแรกไม่จำเป็นต้องกด Ctrl+Shift+Enter แต่อันที่สองกด

ข้าว. 7.17. เติมสูตรของคุณให้สมบูรณ์โดยการวางฟังก์ชัน LARGE เป็นอาร์กิวเมนต์ อาร์เรย์1ฟังก์ชัน SUMPRODUCT

เราได้กล่าวถึงเคล็ดลับที่สามารถเรียกได้ว่าเป็น "อาร์เรย์ความยาวผันแปรของตัวเลขลำดับ" (รูปที่ 7.18)

ก่อนที่จะไปยังสูตรอาร์เรย์ถัดไป ฉันอยากจะแนะนำให้คุณรู้จักกับเครื่องมือ Excel − โต๊ะ(รูปที่ 7.19) หากต้องการสร้างตาราง ให้ยืนบนเซลล์ใดก็ได้ในช่วง A1:B8 แล้วไปที่เมนู: บ้าน → สไตล์ → จัดรูปแบบเป็นตาราง(หรือกด Ctrl+T; T คือภาษาอังกฤษ) ต่อไปในเมนู ตัวสร้างภูมิภาค ตัวเลือกสไตล์ตารางทำเครื่องหมายที่ช่องถัดจากนั้น เส้นรวม- ใช้ตัวกรองในคอลัมน์ ปริมาณการขาย: ตัวกรองตัวเลข → 10 คนแรก, ชุด 3 องค์ประกอบที่ใหญ่ที่สุดรายการ- น่าเสียดายที่รายการที่กรองจะรวมรายการที่ซ้ำกันนับตั้งแต่รายการที่สาม มูลค่าสูงสุดแสดงเป็นสองบรรทัด

ข้าว. 7.19. การใช้เครื่องมือ โต๊ะเพื่อเลือกค่าสูงสุดสามค่า

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

ในเซลล์เดียวคุณต้องทำตามลำดับ:

เพื่อแยกตัวเลขสี่ตัวพร้อมกัน สตริงข้อความคุณสามารถใช้อาร์เรย์ของค่าคงที่ในอาร์กิวเมนต์ได้ start_positionฟังก์ชั่น PSTR (รูปที่ 7.21) ฟังก์ชัน PSTR ส่งคืน หมายเลขที่กำหนดอักขระจากบรรทัดข้อความ โดยเริ่มจากตำแหน่งที่ระบุ มักจะเป็นข้อโต้แย้ง start_positionมีองค์ประกอบเดียว ตัวอย่างเช่น หากคุณป้อนสูตร =PSTR(C3,4,2) ฟังก์ชันจะส่งกลับอักขระสองตัว โดยเริ่มจากอักขระตัวที่ 4 จากเซลล์ C3

ข้าว. 7.21. อาร์เรย์ของค่าคงที่ในการโต้แย้ง start_positionฟังก์ชัน PSTR

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

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

เติมสูตรให้สมบูรณ์โดยการคูณองค์ประกอบของอาร์เรย์ที่ได้รับในขั้นตอนก่อนหน้าโดยใช้ฟังก์ชัน PRODUCT จากนั้นคูณด้วยราคาปลีก และสุดท้ายปัดเศษผลลัพธ์ให้เป็นทศนิยมสองตำแหน่ง (รูปที่ 7.24) เนื่องจากคุณใช้อาร์เรย์ของค่าคงที่เป็นอาร์กิวเมนต์ของฟังก์ชัน PSTR การป้อนสูตรจึงไม่จำเป็นต้องใช้ Ctrl+Shift+Enter

การใช้อาร์เรย์ของค่าคงที่ในฟังก์ชัน VLOOKUP เพื่อประหยัดพื้นที่ในรูป รูปที่ 25 แสดงการใช้งานฟังก์ชัน VLOOKUP โดยทั่วไป (คอลัมน์แรกในตาราง F2:G5 จะถูกจัดเรียง ดังนั้น VLOOKUP จะค้นหาเฉพาะรายการที่ตรงกันโดยประมาณเท่านั้น) หากคุณคิดว่าข้อมูลในตารางการค้นหา (F2:G5) จะไม่เปลี่ยนแปลง และคุณไม่สบายใจที่ตารางจะใช้พื้นที่บนเวิร์กชีต คุณสามารถป้อนโค้ดตารางอย่างหนักลงในสูตรได้ ปัญหาเกี่ยวกับฮาร์ดโค้ดอาร์เรย์ของค่าคงที่ในสูตร VLOOKUP คือปริมาณอินพุตค่อนข้างมาก (ต้องวางอาร์เรย์ในวงเล็บปีกกา ข้อความต้องอยู่ในเครื่องหมายคำพูด และต้องป้อน "คลาวด์" ของตัวคั่น) คุณสามารถใช้เคล็ดลับเล็กๆ น้อยๆ ได้: หากคุณมีตารางอยู่แล้ว ให้สร้าง VLOOKUP ปกติแล้วไฮไลต์อาร์กิวเมนต์ โต๊ะและกด F9 คุณแปลงการอ้างอิงช่วงเป็นอาร์เรย์ของค่าคงที่ (รูปที่ 7.26) จากนั้นคุณสามารถคัดลอกสูตร VLOOKUP ลงในคอลัมน์และลบตารางการค้นหาได้

ข้าว. 7.25. หากข้อมูลในตารางการค้นหาอาจมีการเปลี่ยนแปลง VLOOKUP ปกติที่มีการอ้างอิงเซลล์ก็เป็นตัวเลือกที่ดีเยี่ยม

ข้าว. 7.26. เน้นข้อโต้แย้ง โต๊ะและกด F9; คุณแปลงการอ้างอิงช่วงเป็นอาร์เรย์คงที่

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

คุณสามารถใช้ชื่อนี้เป็นอาร์กิวเมนต์ของฟังก์ชัน VLOOKUP ได้ (รูปที่ 7.28) โปรดทราบว่า =VLOOKUP(A2,ProductPrice,2) ไม่ใช่สูตรอาร์เรย์

ข้าว. 7.28. ชื่อ ราคาสินค้าประกอบด้วยอาร์เรย์ของค่าคงที่

คุณเพิ่งใช้สององค์ประกอบ (อาร์เรย์ของค่าคงที่และชื่อเฉพาะ) เป็นอาร์กิวเมนต์ โต๊ะฟังก์ชัน VLOOKUP แล้วอาร์กิวเมนต์ VLOOKUP อื่นๆ ล่ะ? พวกเขาสามารถมีค่าคงที่อาร์เรย์และ/หรือการดำเนินการของอาร์เรย์ได้หรือไม่? ดังที่คุณเห็นใน ข้อโต้แย้ง lookup_valueไม่สามารถประมวลผลอาร์เรย์ได้ และการโต้แย้ง คอลัมน์_หมายเลข- มันสามารถประมวลผลอาร์เรย์ได้หรือไม่?

ในรูป รูปที่ 7.29 (ช่วง A1:H4) แสดงตารางการค้นหาที่มีชื่อผลิตภัณฑ์ในคอลัมน์แรก (เรียงลำดับตามตัวอักษร) และส่วนประกอบต้นทุนในคอลัมน์ 2–8 จุดประสงค์ของสูตรคือการบวกต้นทุนของคอลัมน์ 2, 4, 5, 7 และ 8 คุณสามารถใช้อาร์เรย์ของค่าคงที่เป็นอาร์กิวเมนต์ได้ คอลัมน์_หมายเลขฟังก์ชัน VLOOKUP เพื่อรับค่าต้นทุนห้าค่าพร้อมกันที่เอาต์พุตของฟังก์ชัน คุณสามารถดูสิ่งนี้ได้หากคุณไฮไลต์ฟังก์ชัน VLOOKUP แล้วกด F9 (รูปที่ 7.30)

ข้าว. 7.29. สถานที่ในการโต้แย้ง คอลัมน์_หมายเลขฟังก์ชัน VLOOKUP อาร์เรย์ของหมายเลขคอลัมน์คงที่

ข้าว. 7.30 น. ตั้งแต่ทะเลาะกัน คอลัมน์_หมายเลขมีห้าองค์ประกอบ ฟังก์ชัน VLOOKUP จะส่งกลับค่าห้าค่า

หากคุณป้อนสูตร =SUM(VLOOKUP(A7,A2:H4,(2,4,5,7,8))) เพียงกด Enter คุณจะได้คำตอบที่ผิด (รูปที่ 7.31) คุณจะเห็นว่าฟังก์ชัน SUM ประมวลผลเฉพาะองค์ประกอบแรกของอาร์เรย์ (1.35;2.15;3;2;4) ที่ส่งคืน ฟังก์ชัน VLOOKUP- เพื่อให้ได้ผลลัพธ์ที่ถูกต้อง ให้ป้อนสูตรลงในเซลล์โดยใช้ Ctrl+Shift+Enter (รูปที่ 7.32) หากคุณต้องการประหยัดจากการกดสามปุ่ม ให้วาง VLOOKUP ไว้ใน SUMPRODUCT (รูปที่ 7.33)

ข้าว. 7.32. ไม่ว่าข้อโต้แย้งจะมีหรือไม่ก็ตาม คอลัมน์_หมายเลขฟังก์ชัน VLOOKUP คืออาร์เรย์ของค่าคงที่หรือการอ้างอิงช่วง การป้อนสูตรจำเป็นต้องกด Ctrl+Shift+Enter

ดังนั้น ปรากฎว่าอาร์กิวเมนต์ของฟังก์ชันบางตัว ซึ่งสามารถประกอบด้วยอาร์เรย์ของค่าคงที่ ไม่จำเป็นต้องใช้ Ctrl+Shift+Enter (ตัวอย่างเช่น อาร์เรย์ในขนาดเล็กที่สุดและยิ่งใหญ่ที่สุด ข้อความใน DLST boolean_valueใน OR) และบางส่วนต้องการ (เช่น คอลัมน์_หมายเลขในวีพีอาร์)

จนถึงตอนนี้ในบทนี้ คุณได้ใช้อาร์เรย์ของค่าคงที่เป็นอาร์กิวเมนต์ของฟังก์ชันแล้ว อาร์เรย์คงที่ยังสามารถใช้ในการดำเนินการทางคณิตศาสตร์และการดำเนินการเปรียบเทียบอาร์เรย์ได้ ยิ่งไปกว่านั้น หากใช้เฉพาะอาร์เรย์ของค่าคงที่ สูตรก็ไม่จำเป็นต้องกด Ctrl+Shift+Enter (รูปที่ 7.34–7.36) หากใช้อาร์เรย์ในรูปแบบของการอ้างอิงช่วง การป้อนสูตรจะต้องกด Ctrl+Shift+Enter (รูปที่ 7.37)

ข้าว. 7.34. สูตรอาร์เรย์ (ในเซลล์ C2) ถามว่า "เนื้อหาของเซลล์ B2 เท่ากับค่าใดค่าหนึ่ง: V.P. ประธาน หรือผู้ดูแลระบบ"; การดำเนินการเปรียบเทียบเกี่ยวข้องกับการดำเนินการกับอาร์เรย์ของค่าคงที่ในอาร์กิวเมนต์ boolean_value1 OR และไม่จำเป็นต้องกด Ctrl+Shift+Enter

ข้าว. 7.35. สูตรอาร์เรย์คำนวณค่าสัมประสิทธิ์ราคา ขึ้นอยู่กับมูลค่าส่วนลดแต่ละรายการ ดำเนินการคูณทางคณิตศาสตร์โดยใช้อาร์เรย์ของค่าคงที่ในอาร์กิวเมนต์ หมายเลข 1ซึ่งไม่ต้องกด Ctrl+Shift+Enter

ข้าว. 7.36. สูตรจะคูณอาร์เรย์คงที่สองตัวแล้วรวมผลลัพธ์ของผลิตภัณฑ์ (ฉันไม่คิดว่าคุณจะใช้สูตรดังกล่าว แต่ฉันรวมไว้เพื่อแสดงให้เห็นว่าอาร์เรย์คงที่ทำงานอย่างไร) การดำเนินการทางคณิตศาสตร์โดยมีอาร์เรย์ของค่าคงที่อยู่ในอาร์กิวเมนต์ หมายเลข 1 SUM และไม่จำเป็นต้องกด Ctrl+Shift+Enter

ข้าว. 7.37. สูตรจะคูณอาร์เรย์ของการอ้างอิงและค่าคงที่ จากนั้นจึงรวมผลลัพธ์ของผลิตภัณฑ์ (ควรใช้ฟังก์ชัน SUMPRODUCT จะดีกว่า ฉันรวมสูตรนี้ไว้เพื่อแสดงให้เห็นว่าอาร์เรย์ค่าคงที่ทำงานอย่างไร) การดำเนินการทางคณิตศาสตร์ไม่เพียงเกี่ยวข้องกับอาร์เรย์ของค่าคงที่เท่านั้น แต่ยังรวมถึงการอ้างอิงถึงช่วงของเซลล์ด้วย และต้องกด Ctrl+Shift+Enter

สรุปบท

• อาร์เรย์คงที่สามารถใช้ได้ทั้งในสูตรปกติและสูตรอาร์เรย์
• ไวยากรณ์ของอาร์เรย์คงที่: วงเล็บปีกกาที่จุดเริ่มต้นและจุดสิ้นสุดของอาร์เรย์ อัฒภาคแยกองค์ประกอบบนบรรทัด เครื่องหมายโคลอนแยกบรรทัดหนึ่งจากอีกบรรทัดหนึ่ง องค์ประกอบข้อความถูกวางไว้ใน เครื่องหมายคำพูดคู่และตัวเลข ค่าตรรกะ และค่าความผิดพลาด - โดยไม่มีเครื่องหมายคำพูด
• มีการใช้อาร์เรย์คงที่สามประเภท: แนวตั้ง แนวนอน สี่เหลี่ยม
• อาร์เรย์คงที่ต้องมีความยาวไม่เกิน 8192 อักขระ
• วิธีป้อนสูตรอาร์เรย์:
  • หากใช้เฉพาะอาร์เรย์คงที่ ก็ไม่จำเป็นต้องใช้ Ctrl+Shift+Enter
  • อาร์กิวเมนต์ของฟังก์ชันบางตัว เมื่อวางอาร์เรย์ของค่าคงที่ลงไป ไม่จำเป็นต้องใช้ Ctrl+Shift+Enter (ตัวอย่างเช่น อาร์เรย์ในขนาดเล็กที่สุดและยิ่งใหญ่ที่สุด ข้อความใน DLST boolean_valueในหรือ start_positionใน PSTR หมายเลข 1เป็น PRODUCT และ SUM)
  • อาร์กิวเมนต์ของฟังก์ชันบางตัวจำเป็นต้องกด Ctrl+Shift+Enter หากมีอาร์เรย์ของค่าคงที่ (เช่น อาร์กิวเมนต์ คอลัมน์_หมายเลขในวีพีอาร์)
• กฎทองของ Excel เกี่ยวกับสูตรอาร์เรย์สามารถเสริมได้ดังต่อไปนี้:
  • หากข้อมูลไม่เปลี่ยนแปลงและคุณกำลังประหยัดพื้นที่บนชีต ให้ใช้อาร์เรย์ของค่าคงที่
  • ตั้งชื่ออาร์เรย์ของค่าคงที่และใช้ชื่ออาร์เรย์ในสูตร