สอบถาม การหาผลรวมโดยมี 3 ตัวแปร

[dolphin]

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

ขอบคุณค่ะ

[สมเกียรติ]

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

ผมทำสูตรให้ดู 2 แบบ

C4
=SUMPRODUCT((Type=$A$4)*(Source=$B4)*(Month=$D$2)*Estimated)

D4
=SUMPRODUCT((Type=$A$4)*(Source=$B4)*(Month=$D$2)*ActJan)

แบบ SumProduct นี้ยากที่จะนำไปใช้ที่เดือนอื่น

ถ้าออกแบบตารางให้เพิ่ม Column หาตำแหน่งรายการก่อน โดยใช้
{=MATCH($A4:$A28&$B4:$B28&E2,Type&Source&Month,0)}

โดยสร้างสูตรแบบ Array และสร้างพร้อมกันทุกเซลล์เพื่อทำให้คำนวณครั้งเดียว

จากนั้นใช้ Index หาค่าตามตำแหน่งที่ Match หาได้ต่อ

=IF(ISERROR(C4),0,INDEX(Estimated,C4))
=IF(ISERROR(C4),0,INDEX(ActJan,C4))

แต่สูตร Index นี้ยังไม่ดีนักเพราะยังต้องคอยแก้ชื่อ ActJan--->ActFeb--->ActMar ในเดือนถัดไปอีก ถ้าทำให้ดีต้องใช้ Indirect ช่วยต่อแต่เกรงว่าจะยากเกินเข้าใจ จึงขอทำให้ดูแค่นี้ก่อนครับ

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

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

ผมแนะนำให้ออกแบบชีทใหม่เป็นฐานข้อมูลที่ใช้เก็บข้อมูลรายเดือนต่อๆกันไป จากนั้นจึงดึงข้อมูลไปสร้างเป็น Sheet1 Sheet2 ตามที่ต้องการนี้อีกต่อหนึ่งจะง่ายกว่ามากครับ ขอให้ดูเรื่องการออกแบบฐานข้อมูลในเว็บนี้