ต้องการแจกแจงจำนวนเงินโดยไม่ให้เกินวงเงินที่มีอยู่

[niwat2811]

รบกวนสอบถามท่านผู้รู้ มีข้อมูลทั้งหมดอยู่ที่ชีท หน่วย
แต่ว่าข้อมูลที่ต้องการให้เปรียบเทียบอยู่ที่ชีท บริษัท
โดยมีความต้องการคือ ใช้ข้อมูลจำนวนเงินที่ชีท บริษัท คอลัมภน์ D เป็นหลัก
เช่น บริษัท 1 จำนวนเงินที่คอลัมภน์ D = 201,498,745.30 บาท เป็นหลัก
และให้ทำการ Sum ที่ชีทหน่วยและให้แสดงผลที่คอลัมภน์ D เป็นชื่อบริษัท โดยไม่ให้เกินวงเงินดังกล่าวข้างต้น
ตามไฟล์ที่แนบมาพร้อมนี้

[menem]

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

ช่อง D1 ให้พิมพ์ บริษัท
ช่อง D2 ใส่สูตร
=IF(D1<>"",IF(SUMIF(D$1:D1,D1,C$1:C1)+C2>SUMIF(บริษัท!$A:$A,D1,บริษัท!$D:$D),IF(MATCH(D1,บริษัท!$A:$A,0)+1<COUNTA(บริษัท!A:A),INDEX(บริษัท!$A:$A,MATCH(D1,บริษัท!$A:$A,0)+1),""),D1),"")

[niwat2811]

ขอบคุณครับสำหรับคำตอบ แต่ยังได้ไม่ตรงตามต้องการครับ
อยากให้เกลี่ยแต่ละบริษัทตามจำนวนเงินให้ครบทุกหน่วย
จากสูตรที่ให้มาหน่วยที่ 66-80 ยังไม่มีบริษัทที่รับผิดชอบ
การเกลี่ยแต่ละบริษัท จำนวนเงินอาจจะขาดหรือเกินจากวงเงินก็ได้ครับ
แต่ขอให้เกลี่ยได้ครบทุกหน่วยและใกล้เคียงกับวงเงินของบริษัท ไม่ต้องเรียงลำดับบริษัทก็ได้ครับ
ขอบคุณครับ

[menem]

ถ้าเป็นแบบนั้นอาจจะต้องใช้ สูตรแบบ Array ซึ่งผมเขียนไม่เป็นครับ, รบกวนรอท่านอื่นนะครับ

[snasui]

ตัวอย่างสูตรครับ

ที่ชีตหน่วยเซลล์ D2 คีย์

=INDEX(บริษัท!$A$2:$A$16,MATCH(1,1/(SUM(C$2:C2)<=SUBTOTAL(9,OFFSET(บริษัท!$D$2,0,0,ROW(บริษัท!$D$2:$D$16)-ROW(บริษัท!$D$2)+1))),0))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

[niwat2811]

ขอบคุณอาจารย์สำหรับสูตรครับ ได้ทดลองนำสูตรไปใช้ที่ชีทหน่วยแล้ว
ปรากฎว่าเมื่อ Sum จำนวนเงินแยกรายบริษัทที่ชีทบริษัทแล้ว ผลต่างที่ได้เป็นเงินที่ต่างกันมาก
เลยได้ทดลองทำ Manual ที่ชีทหน่วยคอลัมภน์ E แล้วนำไป Sum จำนวนเงินแยกรายบริษัทที่ชีทบริษัท
ผลต่างที่ได้จะไม่ต่างกันมาก เป็นไปได้ไหมที่สามารถปรับสูตรให้ได้ใกล้เคียงกับวิธี Manual ครับ
(อยากได้ผลต่าง บวก ลบ ประมาณ ไม่เกิน 1 แสนบาท) ข้อมูลตามไฟล์แนบครับ

[snasui]

ลักษณะตามตัวอย่างคำตอบที่หาด้วยมือคงจะต้องพึ่ง VBA เข้ามาช่วย ลองทำมาดูก่อน ติดแล้วค่อยถามกันต่อครับ

[niwat2811]

ได้ลองค้นหาข้อมูลจากอินเตอร์เน็ตแล้วได้นำ Code มาทดสอบให้งานที่ Sheet1 สามารถใช้งานได้
แต่พอนำข้อมูลจริงมาวางที่ Sheet2 พอรันมาโครแล้ว ขึ้น Error ตรงหน่วยที่ 31 ว่า Subscript Out of Range 9 ครับ
ไม่ทราบว่าควรปรับ Code อย่างไรถึงจะรันผ่านครับ และต้องการ Loop จำนวนเงินของแต่ละบริษัทมาใส่
ที่เซล D1 และทำการเลือกหน่วยที่ยังไม่ได้ถูกเลือกต่อไป ควรปรับ Code เพิ่มอย่างไรครับ ขอบคุณครับ

[snasui]

แก้ตัวแปร Array ที่กำหนดสมาชิก 30 ตัวให้เป็น 80 ตัวแล้วลอง Run ใหม่ครับ

[niwat2811]

ได้ลองแก้ตัวแปรและลอง Run ใหม่แล้ว จนถึงตอนนี้ประมาณ 3 ชม.แล้วก็ยัง Run ไม่เสร็จ
ไม่ทราบว่าพอจะมีวิธี Manual อย่างไรให้เร็วไหมครับ

[menem]

งั้นลองแบบนี้ดูครับ (ผมปรับเพิ่มวงเงิน ตามที่แจ้งไว้ว่าสามารถเพิ่มได้อีกนิดหน่อย
แต่ต้อง Allocate ให้ครบ)

[Bo_ry]

ลองดูก่อน
Column C ต้องเรียงจากมากไปน้อย

[niwat2811]

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

[snasui]

ลองปรับสูตรเป็นด้านล่างครับ

=INDEX(MOD(INT((ROW($B$2:INDEX($B:$B,2^16))-1)/2^(TRANSPOSE(MATCH(ROW($B$2:$B$81),ROW($B$2:$B$81)))-1)),2)*TRANSPOSE($B$2:$B$81),MATCH(MIN(ABS(MMULT(MOD(INT((ROW($B$2:INDEX($B:$B,2^16))-1)/2^(TRANSPOSE(MATCH(ROW($B$2:$B$81),ROW($B$2:$B$81)))-1)),2),$B$2:$B$81)-$E$1)),ABS(MMULT(MOD(INT((ROW($B$2:INDEX($B:$B,2^16))-1)/2^(TRANSPOSE(MATCH(ROW($B$2:$B$81),ROW($B$2:$B$81)))-1)),2),$B$2:$B$81)-$E$1),0),0)

[niwat2811]

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

[snasui]

เข้าใจว่าเป็นเช่นนั้นครับ

เครื่องผม CPU Core i5, RAM 8G สามารถ Run ได้ครับ