Snasui Excel Forum

Posted: **Thu Oct 17, 2019 8:12 pm**

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

Posted: **Thu Oct 17, 2019 8:41 pm**

ที่ ชีทที่ต้องการแสดงค่า ที่ 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)),"") คัดลอกไปทางขวาและลงล่างครับ

Posted: **Thu Oct 17, 2019 9:40 pm**

puriwutpokin wrote: Thu Oct 17, 2019 8:41 pm ที่ ชีทที่ต้องการแสดงค่า ที่ 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 ไ่ม่ให้แสดงผล) และถ้าเป็นวัสดุตัวเดียวกันก็ให้แสดงเป็นผลรวมอยู่ในรายการเดียวกันต้องปรับแก้ไขสูตรแบบไหนครับผม

Posted: **Thu Oct 17, 2019 11:49 pm**

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) จะได้ไม่ต้องคำนวณใหม่

Posted: **Fri Oct 18, 2019 1:31 am**

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

Posted: **Sat Oct 19, 2019 9:43 pm**

Bo_ry wrote: Thu Oct 17, 2019 11:49 pm 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 ก็จะค้างตลอดเลยครับ

Posted: **Sat Oct 19, 2019 10:52 pm**

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

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

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

Posted: **Sun Oct 20, 2019 10:20 am**

snasui wrote: Sat Oct 19, 2019 10:52 pm ไฟล์ตัวอย่างการสร้างรายงานด้วย Power Pivot โดยมีขั้นตอนการทำงานคร่าว ๆ ดังนี้

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

Add ชีต BOM และ Order เข้า Data Model

เข้าหน้าต่าง Power Pivot เพื่อสร้างความสัมพันธ์ระหว่าง BOM และ Order โดยใช้ Model เป็นคีย์ในการเชื่อมความสัมพันธ์

สร้างรายงาน 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 เข้าตรงจุดไหนครับอาจารย์
อาจารย์ช่วยสอนขั้นตอนใช้งานผมหน่อยครับ

Posted: **Sun Oct 20, 2019 10:40 am**

pro602 wrote: Sun Oct 20, 2019 10:20 am จุดเป็น 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

pro602 wrote: Sun Oct 20, 2019 10:20 am ปรับสูตรให้แสดงค่า 0 แทน - และลบสูตรทิ้ง

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

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

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

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

Posted: **Sun Oct 20, 2019 11:23 am**

snasui wrote: Sun Oct 20, 2019 10:40 am
pro602 wrote: Sun Oct 20, 2019 10:20 am จุดเป็น 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

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

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

pro602 wrote: Sun Oct 20, 2019 10:20 am Add ชีต BOM และ Order เข้า Data Model....ข้อนี้ผมไม่เข้าใจครับอาจารย์ว่าขั้นตอนการ Add ทำยังงัยครับ
เข้าหน้าต่าง Power Pivot เพื่อสร้างความสัมพันธ์ระหว่าง BOM และ Order โดยใช้ Model เป็นคีย์ในการเชื่อมความสัมพันธ์....หน้าต่าง Power Pivot เข้าตรงจุดไหนครับอาจารย์
ต้องมีการติดตั้ง Power Pivot เสียก่อน หากยังไม่มีก็ไม่สามารถอธิบายให้เข้าใจได้ครับ

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

Posted: **Sun Oct 20, 2019 4:02 pm**

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

Posted: **Sun Oct 20, 2019 4:42 pm**

snasui wrote: Sun Oct 20, 2019 4:02 pm ประเด็นไม่ได้อยู่ที่ Excel 2010 แต่อยู่ที่ต้องเป็น Excel 2010 Professional และต้องมี Add-ins ที่ชื่อว่า Power Pivot ครับ หากมีครบแล้วสามารถสอบถามมาได้ใหม่ จะได้แนะนำกันต่อไปได้ครับ

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

Posted: **Sun Oct 20, 2019 6:53 pm**

pro602 wrote: Sun Oct 20, 2019 4:42 pm
snasui wrote: Sun Oct 20, 2019 4:02 pm ประเด็นไม่ได้อยู่ที่ 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 ไม่โชว์ครับ

Posted: **Sun Oct 20, 2019 7:00 pm**

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

Posted: **Sun Oct 20, 2019 8:47 pm**

snasui wrote: Sun Oct 20, 2019 7:00 pm ลองดูที่เมนู File > Option > Add-ins > ที่ด้านล่างตรง Manage เลือก Com Add-ins > Go > ตรวจสอบดูว่า Power Pivot for Excel ได้ Tick ไว้หรือไม่ หากไม่ก็ให้ Tick แล้ว > OK ครับ

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

Posted: **Mon Oct 21, 2019 9:09 am**

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

Posted: **Tue Oct 22, 2019 8:45 pm**

logic wrote: Mon Oct 21, 2019 9:09 am บรรทัดสุดท้ายมันฟ้องยังไม่ได้โหลด ผมว่าน่าจะใช้ไม่ได้ ลองปิดเอ็กเซลแล้วเปิดใหม่ อีกอันก็รีสตาร์ทวินโดว์ครับ

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

Posted: **Tue Oct 22, 2019 9:04 pm**

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

Posted: **Wed Oct 23, 2019 8:51 pm**

snasui wrote: Tue Oct 22, 2019 9:04 pm หากเป็นเช่นนั้น อีกทางเลือกคือ แจ้งหน่วยงาน IT ให้ติดตั้ง Excel 2010 หรือสูงกว่า ในรุ่น 64 bit Professional หากไม่มีก็ไม่สามารถใช้ Power Pivot มาช่วยได้ครับ

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

Posted: **Tue Oct 29, 2019 11:57 am**

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

Snasui Excel Forum

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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