เมื่อความรู้ดั้งเดิมมีข้อจำกัด

ในสมัยก่อน…วิชาวิทยาศาสตร์อย่าง ฟิสิกส์ เคมี ที่หลายๆ คนเคยเรียน สามารถอธิบายปัญหาและปรากฏการณ์ต่างๆ ที่เกิดขึ้นบนโลกนี้ได้เป็นอย่างดี นักวิทยาศาสตร์ยุคแรกๆ เรียนรู้… ทำความเข้าใจกฎเกณฑ์ที่เกี่ยวกับมัน จนคิดว่ารู้จักมันดีในระดับหนึ่งแล้ว

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

spacetime_strip

ฟิสิกส์แบบนิวตัน ใช้อธิบายหลายๆเรื่องในอวกาศไม่ได้ ต้องใช้ทฤษฎีไอน์สไตน์แทน นี่แหละคือตัวอย่างของความรู้เดิมใช้ได้จำกัด

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

หากเพื่อนๆ เริ่มพบว่า การเขียนสูตรแบบเดิมเริ่มตอบโจทย์บางอย่างไม่ได้ เช่น

  • อยากจะเขียนสูตรเพื่อให้แสดงค่า Percentile ที่ 70% ของข้อมูล โดยมีเงื่อนไขว่าต้องเป็นค่าเฉพาะของกลุ่มคนที่กำหนดด้วย (ใช้ Pivot ยังไม่ได้เลยครับ)
  • อยากจะให้ excel List ข้อมูลทุกบรรทัด ที่มีเงื่อนไขตรงกับที่กำหนดออกมา โดยใช้สูตร (ปกติ VLOOKUP จะเจอแค่บรรทัดแรก)
  • หากเราต้องการจะประหยัดเนื้อที่ชีทให้ได้มากที่สุด ไม่ต้องการเสียพื้นที่ในการทดข้อมูล จะทำยังไง

ปัญหาทุกอย่างนี้ สามารถถูกแก้ไขได้ด้วย การเขียนสูตรแบบใหม่ที่เรียกว่า Array Formula ซึ่งนอกจากจะช่วยแก้ปัญหาแล้ว ยังทำให้ศักยภาพในการเขียนสูตรเราเพิ่มขึ้นอีกมหาศาลเลยทีเดียว

Array Formula ต่างจากสูตรธรรมดายังไง?

ก่อนที่จะอธิบายว่ามันต่างกันยังไง ผมว่าควรจะเริ่มจากการทำความเข้าใจก่อนว่า Array คืออะไร?

Array ก็คือ ชุดของข้อมูล ซึ่งคำว่า ชุด (collection) ปกติก็จะเป็นการรวมกันของข้อมูลมากกว่า 1 ตัวนั่นเอง

array-component

 

ตัวอย่างเช่น {1,2,5} คือ เป็น Array ที่มีข้อมูล 3 item รวมกันอยู่เป็นชุด Array อันนึง เป็นต้น

โดยที่ข้อมูลจะเขียนอยู่ในเครื่องหมายปีกกา (คล้ายๆเครื่องหมายเซ็ตในวิชาคณิตศาสตร์) โดยที่มีตัวคั่นข้อมูลคือ เครื่องหมาย comma และ เครื่องหมาย semicolon ซึ่งต่างกันดังนี้

  • Comma (,)= คั่นระหว่างคอลัมน์ (วิธีจำ => คอมม่า คอลัมน์ มีตัว ค. นำหน้าเหมือนกัน)
  • Semicolon (;) = คั่นระหว่างแถว

เช่นในตัวอย่าง ={1,2,5;10,3,7} แปลว่า ข้อมูลจะเรียงกันจากซ้ายไปขวา คือ 1 2 5  และขึ้นแถวใหม่ จากนั้นเป็นเลข 10 3 7 ( 1 และ 10 อยู่คอลัมน์เดียวกัน) เป็นต้น

ข้อมูล Array 2 แบบ

การใส่ข้อมูล Array มีอยู่ 2 แบบด้วยกัน คือ

  1. Array Constant : เป็นการใส่ค่าคงที่ ไม่ได้มีการอ้างอิงโดย Cell Reference
    • แบบนี้ต้องใส่ปีกกาครอบด้วย Excel ถึงจะรู้ว่าเป็น Array
    • เช่น ={1,2,5;10,3,7} แบบเดียวกับตัวอย่างข้างบน
  2. Array Cell Reference : เป็นการอ้างอิงค่าเป็นช่วงโดยใช้ Cell Reference
    • แบบนี้ไม่ต้องใส่ปีกกาครอบ
    • เช่น =A1:C2 จะเท่ากับ ={A1,B1,C1;A2,B2,C2} เอง

สิ่งที่สำคัญ คือ หากเราจะใช้ Array Formula เราจะต้องสั่งการด้วยการ กดปุ่ม Ctrl+Shift+Enter เพื่อใช้งาน
(กดแล้วมันจะมีเครื่องหมายปีกกา { } มาครอบสูตรเราให้อีกทีโดยอัตโนมัติ ไม่ใช่ว่าเราไปใส่ปีกกาเอง ยกเว้นจะใส่ครอบตัว Array Constant)

เอาล่ะครับ ทีนี้หลังจากเรารู้จัก Array แล้ว เรามาดูกันว่าการเขียนสูตรแบบ Array Formula ต่างจากสูตรปกติยังไง

array-cal-matchหรือ array-cal-match2

  สูตรปกติ Array Formula
การใส่สูตร ใส่ข้อมูลที่ละตัวเช่น = 1*10 ใส่ข้อมูลเป็นชุดเช่น ={1,2,3}*{10,20,30}
การคำนวณ คำนวณปกติ ไม่มีการจับคู่คำนวณเช่น = 1*10 จับคู่อันดับในการคำนวณ
(ตัวแรกชุดนึงคู่กับตัวแรกของอีกชุด)={1*10,2*20,3*30}={10,40,90}
การ Confirm สูตร กด Enter หรือ เลื่อนไป Cell อื่น กด Ctrl+Shift+Enter
ผลลัพธ์ ได้ข้อมูลออกมาค่าเดียวเสมอ= 10 เลยตัวเดียว ข้อมูลสามารถออกมาได้หลายตัวจากสูตรเดียวอาจกระจายอยู่หลายๆ ช่องได้ เช่น
ได้ผลลัพธ์เป็น 3 ค่า คือ
= {10,40,90}แต่ Excel ไม่สามารถแสดงข้อมูล 3 ตัวในช่องเดียวกันได้ เลยขึ้นแค่คู่แรก คือ 10

จัดการอย่างไรดีกับการที่ Array ออกมาหลายค่า?

เรามีทางเลือก 2 ทางหลักๆ ครับ คือ

  1. เปลี่ยนการเขียนสูตร เป็นให้ลากครอบคลุมหลายช่องก่อน แล้วค่อยใส่สูตร Array Formula
      • หากเราลากพื้นที่หลายช่องไว้ก่อน (ครอบ A4:C4 แล้วค่อยใส่สูตร)  ผลลัพธ์จะแสดงออกมาทีเดียวหลายช่อง ช่องละ item
      • และถ้าสังเกตดู คือ สูตรในแต่ละช่องจะเห็นเป็นสูตรเดียวกัน แม้ค่าจะออกมาต่างกันเป็นคนละ item
      • Array-Display-1
    • ในตัวอย่าง คือ จะเห็น 10 40 90 อยู่แยกกันคนละ cell
    • การทำแบบนี้ จะมีลักษณะพิเศษอย่างนึง คือ หลังจากทำแบบนี้แล้ว เราไม่สามารถลบข้อมูลช่องใดช่องหนึ่งทิ้งได้ ถ้าจะลบต้องลบทั้งหมด
  2. หาฟังก์ชั่นที่ทำหน้าที่สรุปข้อมูลมาครอบอีกทีเพื่อให้รวมค่าหลายๆ ให้อยู่ในช่องเดียว เช่น SUM
    • จะได้ว่า =SUM({1,2,3}*{10,20,30}) = 140
    • Array-Display-2
    • แบบนี้จะทำให้ข้อมูลทั้ง Array ออกมาที่ช่องเดียวได้

ขนาดมิติการจับคู่คำนวณของ Array Formula

จำง่ายๆ แบบนี้ครับ

  • มิติของแถวของผลลัพธ์ = มิติของแถวที่มากที่สุดของตัวตั้งต้น
  • มิติของคอลัมน์ของผลลัพธ์ = มิติของคอลัมน์ที่มากที่สุดของตัวตั้งต้น

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

ตัวอย่าง

  • แต่ละชุดมีมิติเหมือนกัน
    • Array-Dimension-1
    • เช่น ={1,2,3}*{10,20,30}
    • หากลองกด F9 จะได้ค่า ว่า = {10,40,90}
    • หรือเขียนได้ว่า {1 แถว 3 คอลัมน์} * {1 แถว 3 คอลัมน์} = {1 แถว 3 คอลัมน์} (ขนาดมิติเหมือนเดิม)
  • แต่ละชุดมีมิติไม่เหมือนกัน
    • Array-Dimension-2
    • หากลองเปลี่ยนสูตร ={1,2,3}*{10;20;30;40}
    • หากลองกด F9 จะได้ค่า ว่า = {10,20,30 20,40,60 30,60,90 40,80,120}
    • หรือเขียนได้ว่า {1 แถว 3 คอลัมน์} * {4 แถว 1 คอลัมน์} = { 4 แถว 3 คอลัมน์ } (ขนาดมิติเปลี่ยนไป)

ตัวอย่างการใช้งานจริง

อ่านทฤษฎีมาระดับนึงแล้ว มาดูตัวอย่างจริงกันดีกว่าครับ

ตัวอย่างที่ 1 : ประหยัดพื้นที่

Array-Formula-Ex-01

จากรูป หากเราต้องการหาค่าผลต่างที่มากที่สุด ปกติจะต้องทำการคำนวณหลายช่องเลย คือ ต้องคำนวณผลต่างให้ครบทุกช่องก่อน (ช่อง D2 ถึง D6) จากนั้นค่อยใช้ฟังก์ชั่น Max อีกทีหนึ่ง เช่นเดียวกับช่อง D7

แต่ถ้าเราใช้ Array Formula เราจะสามารถเขียนทุกอย่างอยู่ในช่องเดียวได้เลย คือ =MAX(B2:B6-C2:C6) แล้วกด Ctrl+Shift+Enter มันก็จะมีปีกกามาครอบเอง (ห้ามใช้วิธีใส่ลงไปเอง)

สิ่งที่มันทำคือ จับคู่ เอา ={B2-C2;B3-C3;B4-C4;B5-C5;B6-C6} ซึ่งจะได้ ={15;8;10;2;3} จากนั้นก็เอา MAX มาครอบ เป็น =MAX({15;8;10;2;3})
ซึ่งจะได้ค่า 15 นั่นเอง

ตัวอย่างที่ 2 : หาค่า Median ของคะแนนห้องเรียน A จากข้อมูลที่มีหลายห้องปนกัน

Array-Formula-Ex-02

 

จากรูป ในช่อง E2 ใส่สูตรดังนี้ =MEDIAN(IF($A$2:$A$10=$D2,$B$2:$B$10,””)) แล้วกด Ctrl+Shift+Enter จะได้ 7

สิ่งที่ Excel คิดก็คือ จับคู่เปรียบเทียบ A2 กับ D2; A3 กับ D2 … A10 กับ D2 ว่าเท่ากันหรือไม่ ถ้าเท่า ได้ TRUE ถ้าไม่เท่าได้ FALSE จะได้ว่า
=MEDIAN(IF({TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE},$B$2:$B$10,””))

ต่อไป ด้วยเงื่อนไข IF ถ้าคู่ไหนจริง (เป็นห้อง A) จะใส่ค่า $B$2:$B$10 ถ้าคู่ไหนไม่จริงใส่ค่าว่าง “” จะได้ว่า
=MEDIAN({10;””;””;5;””;3;9;””;””})

ต่อไปสูตร MEDIAN ก็เริ่มทำงาน โดยมันจะไม่สนใจค่าว่าง ก็เปรียบเสมือนเหลือค่า แค่ =MEDIAN({10;5;3;9}) จึงได้ค่าเหลือ 7 ครับ

สำหรับตัวอย่างถัดไป ซึ่งคือ การให้ excel List ข้อมูลทุกบรรทัด ที่มีเงื่อนไขตรงกับที่กำหนดออกมา โดยใช้สูตร Array Formula นั้น จะขอยกไปบทความถัดไป เพราะมีความซับซ้อนค่อนข้างมากครับ อย่าลืมติดตามให้ได้นะครับ