Snasui Excel Forum

สวัสดีค่ะ ขอสอบถามวิธีการเขียนสูตรคำนวณ สำหรับการ allocate ค่าใช้จ่ายจาก Cost Center ไปแต่ละ Business Unit โดยมีการกำหนดอัตราส่วน% ไว้ค่ะ ตัวอย่างเช่น
- Cost Center : Logistic ให้ allocate ไปที่ Maintenance BU 50% / Tech Assist 50%
- Cost Center : Office ให้ allocate ไปที่ Maintenance BU 35% / Tech Assist 35% / Front Office 30%
- Cost Center : MRO ให้ allocate ไปที่ Maintenance BU 100%
- Cost Center : Front Office ให้ allocate ไปที่ Maintenance BU 10% / Tech Assist 10% / Front Office 80%

ได้แนบไฟล์ไว้ สำหรับตัวอย่างข้อมูลค่ะ ทางบริษัทใช้ Excel 2013 ค่ะ

ขอบคุณค่ะ

ช่วยอธิบายเพิ่มเติมได้ไหมครับ ว่า Maintenance BU อยู่ในส่วนไหน และใส่ผลลัพธ์ที่ต้องการมาสัก 1 ตัวอย่างครับ เพื่อจะได้เข้าใจตรงกันครับ

บริษัทมีการกำหนด Cost center ไว้ 9 แผนกค่ะ ( MRO / Tech Assist / Hosting / Planning /QA / Logistic / Front Office / Office / ACR) โดยต้องการ allocate ค่าใช้จ่ายแต่ละแผนก เข้าแต่ละ Business Unit ตาม % ที่กำหนดไว้ในไฟล์แนบ เพื่อทำงบกำไรขาดทุนแยกกันค่ะ โดยมีทั้งหมด 5 BU (MRO / Tech Assist / Front Office / ACR / Hosting)

*ขอแก้ชื่อ cost center ให้ตรงกับในไฟล์ค่ะ

ตัวอย่างเช่น ;
เงินเดือน (Logistic) = 50,000 ให้ allocate ไปที่ Business Unit ดังนี้
- MRO (50%) = 25,000 / Tech Assist (50%) = 25,000

เงินเดือน (Office) = 30,000 ให้ allocate ไปที่ Business Unit ดังนี้
- MRO (35%) = 10,500 / Tech Assist (35%) = 10,500 / Front Office (30%) = 9,000

เงินเดือน (MRO) = 100,000 ให้ allocate ไปที่ Business Unit ดังนี้
- MRO (100%) = 100,000

เงินเดือน (Front Office) = 70,000 ให้ allocate ไปที่ Business Unit ดังนี้
- MRO (10%) = 7,000 / Tech Assist (10%) = 7,000 / Front Office (80%) = 56,000

ขอบคุณค่ะ

...

ไม่ค่อยเข้าใจรูปแบบ ที่สอบถามมา

T3
=SUM(INDEX(INDEX($B$3:$J$10,0,MATCH($L3,$B$2:$J$2,0)),0))*M3

**เพื่อนสมาชิกกรุณาช่วยกด โฆษณาในเพจ เพื่อเป็นการสนับสนุน ฟอรั่ม**

Norkaz

ขอโทษนะคะ ที่ทำไฟล์ไม่ชัดเจนว่าอยากให้ผลลัพท์แสดงอย่างไร
ช่วยรบกวนดูให้อีกครั้งได้ไหมคะ ในไฟล์แนบได้ทำตารางผลลัพท์ แสดงค่าใช้จ่ายรวมของทุกแผนก โดยแสดงแยกแต่ละ Business Unit แล้วค่ะ ไม่ทราบว่าพอจะมีสูตรที่ช่วยให้คำนวณได้เร็วขึ้นไหมคะ

ขอบคุณค่ะ

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ A26 คีย์สูตรเพื่อเป็นเซลล์ช่วย
   =IFERROR(INDEX($A$3:$A$10,INT((ROWS(A$26:A26)-1)/9)+1),"")
   Enter > Copy ลงด้านล่าง
2. ที่ B26 คีย์สูตรเพื่อเป็นเซลล์ช่วย
   =IF(A26="","",INDEX($B$3:$J$10,MATCH(A26,$A$3:$A$10,0),COUNTIFS(A$26:A26,A26)))
   Enter > Copy ลงด้านล่าง
3. ที่ C26 คีย์สูตรเพื่อเป็นเซลล์ช่วย
   =IF(A26="","",INDEX($B$2:$J$2,COUNTIFS(A$26:A26,A26)))
   Enter > Copy ลงด้านล่าง
4. ที่ D26 คีย์สูตรเพื่อเป็นเซลล์ช่วย
   =IF($A26="","",$B26*INDEX($N$3:$R$11,MATCH($C26,$M$3:$M$11,0),COLUMNS($D26:D26)))
   Enter > Copy ไปด้านขวาถึง H26 > Copy ลงด้านล่าง
5. ที่ N16 คีย์สูตรเพื่อหาคำตอบ
   =SUMIFS(D$26:D$97,$A$26:$A$97,$M16)
   Enter > Copy ไปด้านขวาและลงด้านล่างให้เต็มตาราง

ขอบคุณมากค่ะอาจารย์ จะศึกษาสูตร และนำไปใช้ค่ะ