ทราบหรือไม่ว่าถ้าเลือกพื้นที่จากเซลล์บนซ้ายลงมาทางขวาตามภาพซ้ายบนเท่านั้นที่จะทำให้ใช้กับคำสั่ง Data Validation กับ Conditional Formatting แล้วจึงจะทำงานร่วมกับเครื่องหมาย $ ในสูตรเงื่อนไขที่ทำไว้ได้ถูกต้อง

วิธีการแบบนี้จะช่วยทำให้ใช้คำสั่งดังกล่าวได้ทั้งตารางโดยทำเพียงครั้งเดียว ไม่ต้องทะยอยทำทีละแถวตั้ง

จากภาพที่สอง ใช้ Conditional Formatting ช่วยแสดงสีรายการที่ต้องการว่าอยู่ตรงไหน

เริ่มจากเลือกพื้นที่ตารางเฉพาะส่วนของรายการทั้งหมด ตั้งแต่ B3:E20 แล้วสั่ง Home > Conditional Formatting > New Rule โดยเลือกใช้เงื่อนไขแบบ Use a formula to determine which cells to format แล้วใช้สูตร =$C3=$G$3

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

จากเดิมที่มีเงื่อนไขว่ามิติต้องเท่ากัน เพียงแค่ขอให้มีด้านใดด้านหนึ่งของมิติเท่ากันก็เพียงพอแล้ว

BranchNum
=Sheet1!$B$4:$B$14
พื้นที่แนวตั้ง

Date
=Sheet1!$C$3:$Q$3
พื้นที่แนวนอน

Data
=Sheet1!$C$4:$Q$14
พื้นที่สี่เหลี่ยมผืนผ้าขนาดเท่ากับแนวตั้งxแนวนอน

จากตารางในภาพนี้เป็นยอดสินค้า รายวันของแต่ละสาขา
1. หายอดรวมของทุกสาขาในวันที่ต้องการ
2. หายอดรวมของทุกวันของสาขาที่ต้องการ
3. หายอดรวมของสาขาหนึ่งๆในช่วง 7 วัน
4. หายอดรวมทีละ 5 สาขาในช่วงระหว่างวันที่เท่าใดก็ได้

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=1p1Sl4Eg7obm4T3emYgNv_QppQrGQ7KxL

หมายเหตุ

ตามปกติโครงสร้าง (Syntax) ของสูตรนี้

=SUMPRODUCT(array1, [array2], [array3], …)

หากใช้เครื่องหมายคอมมา ,,, จะมีข้อแม้สำคัญว่า The array arguments must have the same dimensions. หรือขนาดของพื้นที่อ้างอิงต้องมีมิติหรือขนาดเท่ากัน

Sum แปลว่าบวก Product แปลว่าอะไร

คำว่า Product ไม่ได้แปลว่าผลิตภัณฑ์ แต่เป็นภาษาคณิตศาสตร์แปลว่า คูณ

ดังนั้น SumProduct แปลว่า ผลรวมของผลคูณ

โครงสร้าง (Syntax) ของสูตรนี้
=SUMPRODUCT(array1, [array2], [array3], …)

คำว่า array หมายถึง อะไรก็ตามที่มีตั้งแต่ 2 เซลล์หรือ 2 ค่าขึ้นไป

ส่วนที่อยู่ในวงเล็บ [ ] เช่น [array2], [array3], นั้นแสดงว่า ไม่จำเป็นต้องใส่ลงไปในสูตรก็ได้ถือว่าเป็น Optional โดยอย่างน้อยสูตรนี้ต้องมี array1 ใส่ไว้ แต่อย่างไรก็ตามหากต้องการหาผลรวมของผลคูณ ต้องมี array1 กับ array2 เช่น ในภาพแรก

=SUMPRODUCT(C2:C10,D2:D5)

ขั้นแรกสูตรนี้จะนำแต่ละเซลล์ใน C2:C10 มาคูณกับแต่ละเซลล์ใน D2:D5 ตามลำดับ
C2*D2
C3*D3
C4*D4
C5*D5

จากนั้นจึงนำผลคูณที่ได้ทั้งหมดมารวมกันเป็นคำตอบ

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

ข้อแม้สำคัญที่ทำให้สูตรนี้ทำงานได้ ถ้าไม่สังเกตให้ดีจะถูกมองข้ามไปตลอดอยู่ตรงเครื่องหมาย comma ซึ่ง Excel มีข้อแม้สำคัญว่า The array arguments must have the same dimensions.

ถ้าใส่เครื่องหมาย comma ,,, เมื่อไหร่ เมื่อนั้นพื้นที่ตารางที่ใส่ไว้ในสูตรนี้(หรือสูตรอื่นๆที่ทำงานคล้ายกัน) ต้องมีขนาดหรือมิติเท่ากันเสมอ โดยหารู้ไม่ว่าสามารถใช้เครื่องหมายคูณ * ใส่แทนได้ด้วย

จากเดิม
=SUMPRODUCT(Cost,Quantity)
แก้เป็น
=SUMPRODUCT(Cost*Quantity)

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

… โปรดติดตามตอนต่อไป

หากต้องการบันทึกข้อมูลลงไปในเซลล์ตามลำดับตัวเลขที่เห็น … Range Name เป็นสิ่งที่ช่วยได้

ให้เริ่มจากคลิกเลือกเซลล์ B7 จากนั้นกดปุ่ม Ctrl แล้วคลิกเลือกเซลล์ D2, D7, B2 แล้วตั้งชื่อ Range Name ให้กับเซลล์เหล่านี้ เช่นตั้งชื่อว่า MyInput

เคล็ดลับนี้เป็นเรื่องที่ผมค้นพบ ไม่เคยเห็นใครที่ไหนทำได้มาก่อน โดยมีประเด็นสำคัญอยู่ที่ตอนที่คลิกเลือกเซลล์ที่ต้องการจะตั้งชื่อให้นั้น ให้เลือกเซลล์ตำแหน่งที่ 2, 3, 4, 1 โดยเลือกเซลล์ตำแหน่งแรก B2 เป็นเซลล์สุดท้ายแล้วจึงตั้งชื่อ Range Name

เริ่มบันทึกข้อมูลโดยกดปุ่ม F5 หรือเลือกชื่อ Range Name ที่ตั้งชื่อว่า MyInput เพื่อให้ Excel เลือกพื้นที่ตารางหลายเซลล์นั้นให้ก่อน จากนั้นให้พิมพ์ค่าที่ต้องการแล้วกด Enter ไปเรื่อยๆ Excel จะบันทึกข้อมูลลงไปในเซลล์ B2 > B7 > D2 > D7

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=1vHqy4MYQb6Gr7ZKIjppMgOviPSdIVFae

Range Name สามารถตั้งชื่อให้กับเซลล์หลายเซลล์ที่ไม่ต้องเป็นพื้นที่เซลล์ที่ต่อเนื่องกันได้ด้วย โดยในตัวอย่างนี้เมื่อตรวจสอบดูที่เมนู Formulas > Name Manager จะพบชื่อ MyInput โดยมี Refers to
=Sheet1!$B$7,Sheet1!$D$2,Sheet1!$D$7,Sheet1!$B$2

ที่แปลกกว่านั้นเมื่อกดปุ่ม F5 ไปที่ MyInput แล้ว Excel จะไม่แสดงชื่อ MyInput ให้เห็นในช่อง Name Box