Snasui Excel Forum

สวัสดีครับอาจารย์และพี่ๆสมาชิก
รบกวนขอคำปรึกษาและแนะนำการทำบันทึกข้อมูลการขายและการทำสรุปรายงานยอดการขายของพนักงานขายแต่ล่ะคน โดยมีรายล่ะเอียด
ที่ผมได้ทำตัวอย่างแนบมาดังนี้ครับ
-Sheet "Product&Price" เป็นข้อมูลผลิตภัณฑ์และราคาแต่ล่ะเกรด
-Sheet "Sales code" เป็นข้อมูลของพนักงานขาย
-Sheet "Record" จะเป็น Sheet สำหรับคีย์เก็บข้อมูลการขายของพนักงานขายแต่ล่ะคนในแต่ล่ะวันโดยคีย์เก็บข้อมูลไปเรื่อยๆ
-ผลลัพท์หรือข้อมูลที่ต้องการคือเมื่อทำการป้อนข้อมูลที่ Sheet "Record" แล้ว ที่ Sheet "S001-S007" อยากให้ข้อมูลมาแสดงผลโดยอัตโนมัติตามตัวอย่างใน Sheet S001,S002,S003 ครับ *ผมยกตัวอย่างการขายมา 2 วันครับ*
โดยจะแสดงผลจำนวนการขายและยอดการขายโดยเอาจำนวนการขายไปคูณกับราคาสินค้าใน Sheet "Product & Price' ครับ
และในแต่ล่ะ Sheet ให้แสดงผลรวมยอดขายทั้งหมดตามตัวอย่างครับ
-Sheet "Summarize" เป็นการนำยอดขายของ Sale แต่ล่ะคนมาสรุปยอดขายในแต่ล่ะวันครับ

*ใช้ Excel V.2007 ครับ*
หรือมีรูปแบบการออกแบบการบันทึกหรือทำรายงานในลักษณะไหนที่จะได้ผลประมาณนี้อาจารยืและพี่ๆสมาชิกช่วยแนะนำและชี้แนะผมด้วยครับ*
รายการ Product และ Sales จะมีจำนวนมากกว่าตัวอย่างที่ผมยกมาครับ

ขอบคุณครับ

ที่ A5=IFERROR(INDEX(Record!A$3:A$30,SMALL(IF(Record!$B$3:$B$30=$A$2,ROW(Record!$B$3:$B$30)-ROW(Record!$B$3)+1),ROWS($A$5:$A5))),"") แล้ว เลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Ctrl+Shift+Enter แล้วคัดลอกไปถึง C5 และคัดลอกลงครับ
ที่ C5=SUMIFS(INDEX(Record!$D$3:$E$30,,MATCH(D$4,Record!$D$2:$E$2,0)),Record!$A$3:$A$30,$A5,Record!$B$3:$B$30,$B5,Record!$C$3:$C$30,$C5) ทำเหมือน อันแรก แล้วคัดลอกไปวางที่ F5 แล้วคัดลอกลงครับ
ืที่
E5=D5*IFERROR(VLOOKUP($C5,'Product&Price'!$A$3:$C$11,MATCH(E$4,'Product&Price'!$A$2:$C$2,0),0),0) ว่างแล้วคัดลอกไปที่ G5 ทำแบบเดียวกับด้านบน
ืที่ ชีท Summarize
ที่ B3=SUMPRODUCT(INDIRECT($A3&"!E5:G8"),(INDIRECT($A3&"!A5:A8")=B$2)*(INDIRECT($A3&"!B5:B8")=$A3)*{1,0,1}) คัดลอกไปทั่วตาราง

puriwutpokin wrote: Sat Mar 07, 2020 3:50 pm ที่ A5=IFERROR(INDEX(Record!A$3:A$30,SMALL(IF(Record!$B$3:$B$30=$A$2,ROW(Record!$B$3:$B$30)-ROW(Record!$B$3)+1),ROWS($A$5:$A5))),"") แล้ว เลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Ctrl+Shift+Enter แล้วคัดลอกไปถึง C5 และคัดลอกลงครับ
ที่ C5=SUMIFS(INDEX(Record!$D$3:$E$30,,MATCH(D$4,Record!$D$2:$E$2,0)),Record!$A$3:$A$30,$A5,Record!$B$3:$B$30,$B5,Record!$C$3:$C$30,$C5) ทำเหมือน อันแรก แล้วคัดลอกไปวางที่ F5 แล้วคัดลอกลงครับ
ืที่
E5=D5*IFERROR(VLOOKUP($C5,'Product&Price'!$A$3:$C$11,MATCH(E$4,'Product&Price'!$A$2:$C$2,0),0),0) ว่างแล้วคัดลอกไปที่ G5 ทำแบบเดียวกับด้านบน
ืที่ ชีท Summarize
ที่ B3=SUMPRODUCT(INDIRECT($A3&"!E5:G8"),(INDIRECT($A3&"!A5:A8")=B$2)*(INDIRECT($A3&"!B5:B8")=$A3)*{1,0,1}) คัดลอกไปทั่วตาราง

แก้ไขเพิ่มเติมครับ
ที่ A5=IFERROR(INDEX(Record!A$3:A$30,SMALL(IF(Record!$B$3:$B$30=$A$2,ROW(Record!$B$3:$B$30)-ROW(Record!$B$3)+1),ROWS($A$5:$A5))),"") แล้ว เลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Ctrl+Shift+Enter แล้วคัดลอกไปถึง C5 และคัดลอกลงครับ
ที่ C5=SUMIFS(INDEX(Record!$D$3:$E$30,,MATCH(D$4,Record!$D$2:$E$2,0)),Record!$A$3:$A$30,$A5,Record!$B$3:$B$30,$B5,Record!$C$3:$C$30,$C5) กดเลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Enter แล้วคัดลอกไปวางที่ F5 แล้วคัดลอกลงครับ
ืที่
E5=D5*IFERROR(VLOOKUP($C5,'Product&Price'!$A$3:$C$11,MATCH(E$4,'Product&Price'!$A$2:$C$2,0),0),0) ว่างแล้วคัดลอกไปที่ G5 กดเลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Enter
ที่ A2=MID(CELL("filename",$A$1),FIND("]",CELL("filename"))+1,4) กดเลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Enter
ที่ B2=IFERROR(VLOOKUP(A2,'Seles code'!A2:B10,2,0),"") กดเลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Enter
ืที่ ชีท Summarize
ที่ B3=SUMPRODUCT(INDIRECT($A3&"!E5:G8"),(INDIRECT($A3&"!A5:A8")=B$2)*(INDIRECT($A3&"!B5:B8")=$A3)*{1,0,1}) คัดลอกไปทั่วตาราง

puriwutpokin wrote: Sat Mar 07, 2020 4:54 pm

puriwutpokin wrote: Sat Mar 07, 2020 3:50 pm ที่ A5=IFERROR(INDEX(Record!A$3:A$30,SMALL(IF(Record!$B$3:$B$30=$A$2,ROW(Record!$B$3:$B$30)-ROW(Record!$B$3)+1),ROWS($A$5:$A5))),"") แล้ว เลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Ctrl+Shift+Enter แล้วคัดลอกไปถึง C5 และคัดลอกลงครับ
ที่ C5=SUMIFS(INDEX(Record!$D$3:$E$30,,MATCH(D$4,Record!$D$2:$E$2,0)),Record!$A$3:$A$30,$A5,Record!$B$3:$B$30,$B5,Record!$C$3:$C$30,$C5) ทำเหมือน อันแรก แล้วคัดลอกไปวางที่ F5 แล้วคัดลอกลงครับ
ืที่
E5=D5*IFERROR(VLOOKUP($C5,'Product&Price'!$A$3:$C$11,MATCH(E$4,'Product&Price'!$A$2:$C$2,0),0),0) ว่างแล้วคัดลอกไปที่ G5 ทำแบบเดียวกับด้านบน
ืที่ ชีท Summarize
ที่ B3=SUMPRODUCT(INDIRECT($A3&"!E5:G8"),(INDIRECT($A3&"!A5:A8")=B$2)*(INDIRECT($A3&"!B5:B8")=$A3)*{1,0,1}) คัดลอกไปทั่วตาราง

แก้ไขเพิ่มเติมครับ
ที่ A5=IFERROR(INDEX(Record!A$3:A$30,SMALL(IF(Record!$B$3:$B$30=$A$2,ROW(Record!$B$3:$B$30)-ROW(Record!$B$3)+1),ROWS($A$5:$A5))),"") แล้ว เลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Ctrl+Shift+Enter แล้วคัดลอกไปถึง C5 และคัดลอกลงครับ
ที่ C5=SUMIFS(INDEX(Record!$D$3:$E$30,,MATCH(D$4,Record!$D$2:$E$2,0)),Record!$A$3:$A$30,$A5,Record!$B$3:$B$30,$B5,Record!$C$3:$C$30,$C5) กดเลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Enter แล้วคัดลอกไปวางที่ F5 แล้วคัดลอกลงครับ
ืที่
E5=D5*IFERROR(VLOOKUP($C5,'Product&Price'!$A$3:$C$11,MATCH(E$4,'Product&Price'!$A$2:$C$2,0),0),0) ว่างแล้วคัดลอกไปที่ G5 กดเลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Enter
ที่ A2=MID(CELL("filename",$A$1),FIND("]",CELL("filename"))+1,4) กดเลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Enter
ที่ B2=IFERROR(VLOOKUP(A2,'Seles code'!A2:B10,2,0),"") กดเลือกชีทที่ต้องการทุกชีท แล้วกด F2 + Enter
ืที่ ชีท Summarize
ที่ B3=SUMPRODUCT(INDIRECT($A3&"!E5:G8"),(INDIRECT($A3&"!A5:A8")=B$2)*(INDIRECT($A3&"!B5:B8")=$A3)*{1,0,1}) คัดลอกไปทั่วตาราง

ขอบคุณครับ สูตรแรกผมทดลองนำไปใช้งานกับข้อมูลSale ประมาณ 70 Sheet ผลลัพธ์ที่ได้มาก้ได้ตามความต้องการครับ แต่การทำงานประมวลผลจะช้าพอสมควรเลยครับพอป้อนข้อมูลที่ Sheet Recod ต้องรอสักพักใหญ่ๆ
สำหรับสูตรแก้ไขเดี๋ยวผมจะนำไปทดลองใช้งานอีกครังครับ

ที่ Sheet Record
F2:G2 พิมพ์ Price A และ Price B

F3:G24
=VLOOKUP($C3,'Product&Price'!$A$3:$C$11,COLUMNS($F3:F3)+1,0)*D3

เลือก A2:G24 แปลงเป็น Table กด Ctrl+T


Insert Pivot Table
Date, Product ไว้ที่ Rows
Grade A,B Price A,b ไว้ที่ Values
Sale name ไว้ที่ Filter

Tab Design > Show in Tabular และ Repeat all items

Tab Analyze > Options > Show report Filter pages > OK

Bo_ry wrote: Sun Mar 08, 2020 12:18 am ที่ Sheet Record
F2:G2 พิมพ์ Price A และ Price B

F3:G24
=VLOOKUP($C3,'Product&Price'!$A$3:$C$11,COLUMNS($F3:F3)+1,0)*D3

เลือก A2:G24 แปลงเป็น Table กด Ctrl+T


Insert Pivot Table
Date, Product ไว้ที่ Rows
Grade A,B Price A,b ไว้ที่ Values
Sale name ไว้ที่ Filter

Tab Design > Show in Tabular และ Repeat all items

Tab Analyze > Options > Show report Filter pages > OK

-ขอบคุณครับสำหรับคำแนะนำและแนวทางการทำงาน
ในส่นของ Sheet summarize ต้องใช้สูตรแบบไหนครับ

ใช้ Pivot เหมือนเดิม แต่เพิ่ม Calculated field
='Price A'+'Price B'

Bo_ry wrote: Sun Mar 08, 2020 8:48 pm ใช้ Pivot เหมือนเดิม แต่เพิ่ม Calculated field
='Price A'+'Price B'

Calculate Field.jpg

ขอบคุณครับสำหรับคำแนะนำ