ช่วยแนะนำการทำสรุปรายงานวัสดุที่ต้องใช้ในแต่ล่ะแผนกแบบอัตโนมัติให้หน่อยครับ

[pro602]

รบกวนอาจารย์และพี่ๆสมาชิกช่วยแนะนำการทำสรุปรายงานวัสดุที่ต้องใช้ในแต่ล่ะแผนกแบบอัตโนมัติให้ผมหน่อยครับ
-Sheet order คือรายการ Model ที่สั่งซื้อ ป้อนข้อมูลรายการสั่งซื้อ
-Sheet BOM คือ รายละเอียดวัสดุที่ต้องใช้ในแต่Section ของแต่ล่ะ Model ที่ช่อง Order คือยอดสั่งซื้อจาก Sheet Order
ทำรายงานผลรวมวัสดุแต่ละประเภทของแต่ล่ะแผนกที่ต้องใช้ทั้งหมดให้แสดงผลอัตโนมัติไปยัง Sheet ต่างๆตามแผนก

[puriwutpokin]

ที่ ชีทที่ต้องการแสดงค่า ที่ A2=IFERROR(INDEX(BOM!$B$2:$G$4000,AGGREGATE(15,6,(ROW(BOM!$B$2:$B$4000)-ROW(BOM!$B$2)+1)/(BOM!$B$2:$B$4000=MID(CELL("filename"),FIND("]",CELL("filename"))+1,31)),ROWS($A$2:$A2)),MATCH(A$1,BOM!$B$1:$G$1,0)),"") คัดลอกไปทางขวาและลงล่างครับ

[pro602]

ที่ ชีทที่ต้องการแสดงค่า ที่ A2=IFERROR(INDEX(BOM!$B$2:$G$4000,AGGREGATE(15,6,(ROW(BOM!$B$2:$B$4000)-ROW(BOM!$B$2)+1)/(BOM!$B$2:$B$4000=MID(CELL("filename"),FIND("]",CELL("filename"))+1,31)),ROWS($A$2:$A2)),MATCH(A$1,BOM!$B$1:$G$1,0)),"") คัดลอกไปทางขวาและลงล่างครับ

-ขอบคุณครับคุณ"puriwutpokin"สำหรับแนวทางที่แนะนำ ถ้าต้องการจะให้แสดงผลเฉพาะตัวที่ต้องใช้งาน(Total Usage มีค่า=0 ไ่ม่ให้แสดงผล) และถ้าเป็นวัสดุตัวเดียวกันก็ให้แสดงเป็นผลรวมอยู่ในรายการเดียวกันต้องปรับแก้ไขสูตรแบบไหนครับผม

[Bo_ry]

F1
=MID(CELL("filename",F1),FIND("]",CELL("filename",F1))+1,20)

A2
=IF(B2="","",$F$1)

B2
=INDEX(BOM!$C$2:$C$4036,SMALL(IF(FREQUENCY(IF(BOM!$B$2:$B$4036=$F$1,IF(ISNUMBER(MATCH(BOM!$A$2:$A$4036,Order!$A$2:$A$29,)),MATCH(BOM!$C$2:$C$4036,BOM!$C$2:$C$4036,))),ROW(BOM!$C$1:$C$3555)),ROW(BOM!$C$1:$C$3556)),ROWS(B$2:B2)))
Ctrl+shift+Enter

C2
=IF(B2="","",VLOOKUP(B2,BOM!$C:$D,2,0))

D2
=IF(B2="","",SUMIFS(BOM!F:F,BOM!C:C,B2))

ลาก A2:D2 ลง
Copy A2:Dxx ไป sheet อื่นให้ครบ

มีสูตร Array คำนวณช้าอยู่แล้ว
และมี function Cell เป็น volatile function คำนวณใหม่ทุกครั้งที่คลิก
ให้ Copy Paste Value ทับ F1 (ชื่อ sheet) จะได้ไม่ต้องคำนวณใหม่

[Bo_ry]

แก้
D2
=IF(B2="","",SUMIFS(BOM!G:G,BOM!C:C,B2))

[pro602]

F1
=MID(CELL("filename",F1),FIND("]",CELL("filename",F1))+1,20)

A2
=IF(B2="","",$F$1)

B2
=INDEX(BOM!$C$2:$C$4036,SMALL(IF(FREQUENCY(IF(BOM!$B$2:$B$4036=$F$1,IF(ISNUMBER(MATCH(BOM!$A$2:$A$4036,Order!$A$2:$A$29,)),MATCH(BOM!$C$2:$C$4036,BOM!$C$2:$C$4036,))),ROW(BOM!$C$1:$C$3555)),ROW(BOM!$C$1:$C$3556)),ROWS(B$2:B2)))
Ctrl+shift+Enter

C2
=IF(B2="","",VLOOKUP(B2,BOM!$C:$D,2,0))

D2
=IF(B2="","",SUMIFS(BOM!F:F,BOM!C:C,B2))

ลาก A2:D2 ลง
Copy A2:Dxx ไป sheet อื่นให้ครบ

มีสูตร Array คำนวณช้าอยู่แล้ว
และมี function Cell เป็น volatile function คำนวณใหม่ทุกครั้งที่คลิก
ให้ Copy Paste Value ทับ F1 (ชื่อ sheet) จะได้ไม่ต้องคำนวณใหม่

ผมลองทำตามแล้วยังไม่ได้ครับและExcel ก็จะค้างตลอดเลยครับ

[snasui]

ไฟล์ตัวอย่างการสร้างรายงานด้วย Power Pivot โดยมีขั้นตอนการทำงานคร่าว ๆ ดังนี้

1. ปรับสูตรให้แสดงค่า 0 แทน - และลบสูตรทิ้ง
2. Add ชีต BOM และ Order เข้า Data Model
3. เข้าหน้าต่าง Power Pivot เพื่อสร้างความสัมพันธ์ระหว่าง BOM และ Order โดยใช้ Model เป็นคีย์ในการเชื่อมความสัมพันธ์
4. สร้างรายงาน Pivot Table

ดู Sheet1 ในไฟล์แนบครับ

[pro602]

ไฟล์ตัวอย่างการสร้างรายงานด้วย Power Pivot โดยมีขั้นตอนการทำงานคร่าว ๆ ดังนี้

1. ปรับสูตรให้แสดงค่า 0 แทน - และลบสูตรทิ้ง
2. Add ชีต BOM และ Order เข้า Data Model
3. เข้าหน้าต่าง Power Pivot เพื่อสร้างความสัมพันธ์ระหว่าง BOM และ Order โดยใช้ Model เป็นคีย์ในการเชื่อมความสัมพันธ์
4. สร้างรายงาน Pivot Table

ดู Sheet1 ในไฟล์แนบครับ

-ขอบคุณสำหรับคำแนะนำและแนวทางการใช้งานที่อาจารย์ช่วยชี้แนะให้ครับแต่ผมลองLoad File ที่อาจารย์ทำแนบมาให้แล้วเข้า Sheet 1 ผมลองกดดูในแต่ล่ะส่วนมันขึ้นฟ้อง 2 แบบนี้ครับ
- จุดที่เป็นหน้าต่าง Section จะฟ้อง การเตรียมใช้งานแหล่งข้อมูลล้มเหลว

-จุดเป็น Pivot มันฟ้องว่า Pivottable ถูกสร้างขึ้นในรุ่นที่ใหม่กว่า (ผมใช้ Excel 2010ครับ)
และในส่วนที่อาจารย์แนะนำขั้นตอนการทำคร่าวๆมาให้ผม
*]ปรับสูตรให้แสดงค่า 0 แทน - และลบสูตรทิ้ง....ปรับสูตรตรงไหนครับ ใช้ตรง Total usage (sheet BOM ใช่ไหมครับ ถ้าปรับแล้วมันจะคำนวนยังงัยครับอาจารย์
[*]Add ชีต BOM และ Order เข้า Data Model....ข้อนี้ผมไม่เข้าใจครับอาจารย์ว่าขั้นตอนการ Add ทำยังงัยครับ
[*]เข้าหน้าต่าง Power Pivot เพื่อสร้างความสัมพันธ์ระหว่าง BOM และ Order โดยใช้ Model เป็นคีย์ในการเชื่อมความสัมพันธ์....หน้าต่าง Power Pivot เข้าตรงจุดไหนครับอาจารย์
อาจารย์ช่วยสอนขั้นตอนใช้งานผมหน่อยครับ

[snasui]

จุดเป็น Pivot มันฟ้องว่า Pivottable ถูกสร้างขึ้นในรุ่นที่ใหม่กว่า

ผมสร้างด้วย Excel ใน Office 365 ครับ

กรณีเป็น Excel 2010 จะต้องเป็น Version Professional ไม่ใช่ Version Standard นอกจากนี้จะต้องมี Add-ins ที่ชื่อว่า Power Pivot ซึ่งต้องดาวน์โหลดมาต่างหากจาก Web Microsoft https://www.microsoft.com/en-us/downloa ... spx?id=102

ปรับสูตรให้แสดงค่า 0 แทน - และลบสูตรทิ้ง

สูตรเดิมที่ให้ผลลัพธ์แสดงเป็นเครื่องหมาย - จะต้องเปลี่ยนเป็นให้แสดงผลลัพธ์เป็น 0 แทน โดยจะต้องคำนึงถึงกรณีเช่นนี้ทุก ๆ งานไม่ใช่เฉพาะงานนี้หากคอลัมน์นั้น ๆ ต้องการให้แสดงเป็นตัวเลขและต้องการนำค่านั้น ๆ ไปใช้ต่อครับ

ชีตที่นำมาทำรายงานนี้คือ BOM กับ Order สูตรอยู่ทีไหนบ้างก็ไปแก้ที่นั่น แก้แล้ว Copy และวางสูตรเป็น Value ไม่เก็บสูตรเอาไว้จะเป็นภาระในการคำนวณของไฟล์ จะทำให้เครื่องทำงานหนัก ยกเว้นมีสูตรไม่กี่บรรทัดครับ

Add ชีต BOM และ Order เข้า Data Model....ข้อนี้ผมไม่เข้าใจครับอาจารย์ว่าขั้นตอนการ Add ทำยังงัยครับ
เข้าหน้าต่าง Power Pivot เพื่อสร้างความสัมพันธ์ระหว่าง BOM และ Order โดยใช้ Model เป็นคีย์ในการเชื่อมความสัมพันธ์....หน้าต่าง Power Pivot เข้าตรงจุดไหนครับอาจารย์

ต้องมีการติดตั้ง Power Pivot เสียก่อน หากยังไม่มีก็ไม่สามารถอธิบายให้เข้าใจได้ครับ

[pro602]

จุดเป็น Pivot มันฟ้องว่า Pivottable ถูกสร้างขึ้นในรุ่นที่ใหม่กว่า

ผมสร้างด้วย Excel ใน Office 365 ครับ

กรณีเป็น Excel 2010 จะต้องเป็น Version Professional ไม่ใช่ Version Standard นอกจากนี้จะต้องมี Add-ins ที่ชื่อว่า Power Pivot ซึ่งต้องดาวน์โหลดมาต่างหากจาก Web Microsoft https://www.microsoft.com/en-us/downloa ... spx?id=102

ปรับสูตรให้แสดงค่า 0 แทน - และลบสูตรทิ้ง

สูตรเดิมที่ให้ผลลัพธ์แสดงเป็นเครื่องหมาย - จะต้องเปลี่ยนเป็นให้แสดงผลลัพธ์เป็น 0 แทน โดยจะต้องคำนึงถึงกรณีเช่นนี้ทุก ๆ งานไม่ใช่เฉพาะงานนี้หากคอลัมน์นั้น ๆ ต้องการให้แสดงเป็นตัวเลขและต้องการนำค่านั้น ๆ ไปใช้ต่อครับ

ชีตที่นำมาทำรายงานนี้คือ BOM กับ Order สูตรอยู่ทีไหนบ้างก็ไปแก้ที่นั่น แก้แล้ว Copy และวางสูตรเป็น Value ไม่เก็บสูตรเอาไว้จะเป็นภาระในการคำนวณของไฟล์ จะทำให้เครื่องทำงานหนัก ยกเว้นมีสูตรไม่กี่บรรทัดครับ

Add ชีต BOM และ Order เข้า Data Model....ข้อนี้ผมไม่เข้าใจครับอาจารย์ว่าขั้นตอนการ Add ทำยังงัยครับ
เข้าหน้าต่าง Power Pivot เพื่อสร้างความสัมพันธ์ระหว่าง BOM และ Order โดยใช้ Model เป็นคีย์ในการเชื่อมความสัมพันธ์....หน้าต่าง Power Pivot เข้าตรงจุดไหนครับอาจารย์

ต้องมีการติดตั้ง Power Pivot เสียก่อน หากยังไม่มีก็ไม่สามารถอธิบายให้เข้าใจได้ครับ

-ขอบคุณสำหรับคำแนะนำดีๆจากอาจารย์ครับสงสัยคงต้องหาแนวทางการทำรายงานแบบใหม่ครับ เพราะมีข้อจำกัดที่ Office ใช้ Excel 2010 อยู่เลยครับ

[snasui]

ประเด็นไม่ได้อยู่ที่ Excel 2010 แต่อยู่ที่ต้องเป็น Excel 2010 Professional และต้องมี Add-ins ที่ชื่อว่า Power Pivot ครับ หากมีครบแล้วสามารถสอบถามมาได้ใหม่ จะได้แนะนำกันต่อไปได้ครับ

[pro602]

ประเด็นไม่ได้อยู่ที่ Excel 2010 แต่อยู่ที่ต้องเป็น Excel 2010 Professional และต้องมี Add-ins ที่ชื่อว่า Power Pivot ครับ หากมีครบแล้วสามารถสอบถามมาได้ใหม่ จะได้แนะนำกันต่อไปได้ครับ

ครับอาจารย์ เดี๋ยวพรุ่งนี้ผมจะเช็คกับ IT ที่บริษัทดูอีกทีครับ (ผมหาที่ Download Add-ins Power Pivot สำหรับ Microsoft Excel 2010 32bits ไม่เจอเลยครับอาจารย์)

[pro602]

ประเด็นไม่ได้อยู่ที่ Excel 2010 แต่อยู่ที่ต้องเป็น Excel 2010 Professional และต้องมี Add-ins ที่ชื่อว่า Power Pivot ครับ หากมีครบแล้วสามารถสอบถามมาได้ใหม่ จะได้แนะนำกันต่อไปได้ครับ

ครับอาจารย์ เดี๋ยวพรุ่งนี้ผมจะเช็คกับ IT ที่บริษัทดูอีกทีครับ (ผมหาที่ Download Add-ins Power Pivot สำหรับ Microsoft Excel 2010 32bits ไม่เจอเลยครับอาจารย์)

ผมลองDownload file (ใช้ตัวx86)จากเว็ปที่อาจารย์ส่งLink ให้แล้วลองติดตั้ง เปิดExcel ขึ้นมาแล้วทำการตั้งค่าAdd-ins ตามที่เขาแนะนำแล้วRibbon ชุดPower Pivot ก็ไม่เห็นครับ แต่ผมลองเปิดExcel file ตัวอย่างที่Download มาจากเว็ปที่โหลดAdd-ins มาเปิดดูได้ครับ...แต่ทำไม่Power Pivot Ribbon ไม่โชว์ครับ

[snasui]

ลองดูที่เมนู File > Option > Add-ins > ที่ด้านล่างตรง Manage เลือก Com Add-ins > Go > ตรวจสอบดูว่า Power Pivot for Excel ได้ Tick ไว้หรือไม่ หากไม่ก็ให้ Tick แล้ว > OK ครับ

[pro602]

ลองดูที่เมนู File > Option > Add-ins > ที่ด้านล่างตรง Manage เลือก Com Add-ins > Go > ตรวจสอบดูว่า Power Pivot for Excel ได้ Tick ไว้หรือไม่ หากไม่ก็ให้ Tick แล้ว > OK ครับ

ผมเช็คตามอาจารย์แนะนำแล้วครับ พบว่าTick แล้ว>OK ...แต่ไม่จำครับเปิดเข้าไปเช็ดมาดูใหม่ก็ยังไม่ถูก Tick ครับ

[logic]

บรรทัดสุดท้ายมันฟ้องยังไม่ได้โหลด ผมว่าน่าจะใช้ไม่ได้ ลองปิดเอ็กเซลแล้วเปิดใหม่ อีกอันก็รีสตาร์ทวินโดว์ครับ

[pro602]

บรรทัดสุดท้ายมันฟ้องยังไม่ได้โหลด ผมว่าน่าจะใช้ไม่ได้ ลองปิดเอ็กเซลแล้วเปิดใหม่ อีกอันก็รีสตาร์ทวินโดว์ครับ

สงสัยจะใช้ไม่ได้จริงๆครับผมลองทำตามคำแนะนำหมดแล้วครับ

[snasui]

หากเป็นเช่นนั้น อีกทางเลือกคือ แจ้งหน่วยงาน IT ให้ติดตั้ง Excel 2010 หรือสูงกว่า ในรุ่น 64 bit Professional หากไม่มีก็ไม่สามารถใช้ Power Pivot มาช่วยได้ครับ

[pro602]

หากเป็นเช่นนั้น อีกทางเลือกคือ แจ้งหน่วยงาน IT ให้ติดตั้ง Excel 2010 หรือสูงกว่า ในรุ่น 64 bit Professional หากไม่มีก็ไม่สามารถใช้ Power Pivot มาช่วยได้ครับ

ขอบคุณอีกครั้งครับสำหรับคำแนะนำมาตั้งแต่ต้นครับอาจารย์

[Bo_ry]

ใช้ Pivot Tabel ธรรมดาได้ ต้อง Clean Data, 0 ให้เป็น 0 ไม่ใส่ -