ขอปรึกษาเกี่ยวกับการใช้ Excel สร้าง report อัตโนมัติ

[asawin77]

ผมอยากจะสร้าง Daily Report แบบอัติโนมัติ ขั้นตอนปกติที่ทำอยู่ คือ ดึงข้อมูลดิบออกมาแล้ว Pivot Copy ข้อมูลที่ต้องการไปวางในตาราง Daily Report ที่เราสร้างไว้แล้วส่งครับ แต่อยากจะ link ข้อมูลแบบอัตโนมัติพอมีวิธีแนะนำไมครับ ผมลองใช้ pivot แล้ว link สูตรไป ตาราง Daily เดือนแรกนะได้ พอเดือนต่อมาดึงข้อมูลดิบออกมา Copy ไปวางของเดือนแรก format pivot เปลี่ยนไปจากเดิมใช้ไม่ได้เลย พอมีวิธีแนะนำไมครับ ข้อมูลมีไม่เยอะครับ ขนาดเฉลี่ยน 50 KB

[snasui]

ลองแนบไฟล์ตัวอย่างมาด้วยพร้อมอธิบายขั้นตอนการทำงานเผื่อเพื่อนสมาชิกจะพอแนะนำได้ครับ

[asawin77]

เนื่องจากข้อมูลมีไม่เยอะเพราะดึงข้อมูลแบบรายวัน จึงอยากให้มีขั้นตอนสั้นและเร็วที่สุด ดึงข้อมูลดิบออกมาจากระบบแล้ว copy วางแล้วผูกสูตรไว้ให้ Link ข้อมูลไปที่ Sheet ตาราง Report ในไฟล์ตัวอย่างมีรายละเอียดมีดังนี้ ทั้งหมด มี 3 sheet sheet แรกคือตาราง Report อีก 2 sheet เป็นข้อมูลดิบแบบรายวันที่ในแต่ละวันจะดึงมา report เมื่อเราดึงข้อมูลดิบออกมาวาง sheet ข้อมูลดิบให้สูตร link แสดงใน sheet ตาราง Report ตามวันที่ , ชื่อ Agent ตามช่องที่ระบายสีไว้ รายละเอียดตามไฟล์แนบครับ

[snasui]

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

ที่ชีต ข้อมูลดิบ1 ข้อมูลวันที่ในคอลัมน์ B จะต้องคัดลอกไปจนถึงบรรทัดสุดท้ายที่เป็นข้อมูลของวันนั้น ๆ

1. ที่ E5 คีย์
   =SUMPRODUCT(--(INT(ข้อมูลดิบ1!$B$4:$B$26)=LOOKUP(2,1/($B$5:B5),$B$5:B5)),--(ISNUMBER(SEARCH(C5,ข้อมูลดิบ1!$D$4:$D$26))),ข้อมูลดิบ1!$E$4:$E$26)
   Enter > Copy ลงด้านล่างยังเซลล์ที่เกี่ยวข้อง
2. ที่ J5 คีย์
   =SUMPRODUCT(--(INT(ข้อมูลดิบ1!$B$4:$B$26)=LOOKUP(2,1/($B$5:$B5),$B$5:$B5)),--(ISNUMBER(SEARCH($C5,ข้อมูลดิบ1!$D$4:$D$26))),--(ข้อมูลดิบ1!$E$4:$E$26=TRIM(J$4)),ข้อมูลดิบ1!$F$4:$F$26)
   Enter > Copy ไป K5 แล้ว Copy ลงด้านล่างยังเซลล์ที่เกี่ยวข้อง
3. ที่ M5 คีย์
   =SUMPRODUCT(--(INT(ข้อมูลดิบ2!$A$3:$A$194)=LOOKUP(2,1/($B$5:$B5<>""),$B$5:$B5)),--(ข้อมูลดิบ2!$B$3:$B$194=INDEX(ข้อมูลดิบ2!$H$3:$H$6,MATCH("*"&$C5,ข้อมูลดิบ2!$I$3:$I$6,0))),--(ข้อมูลดิบ2!$E$3:$E$194=LOOKUP(COLUMNS($J5:J5),{1;2;3},{"ANSWERED";"NO ANSWER";"BUSY"})))
   Enter > Copy ไปด้านขวาถึง O5 แล้ว Copy ลงด้านล่างยังเซลล์ที่เกี่ยวข้อง

[asawin77]

sheet Report
- จำนวนยอดขายได้แล้ว
- ตารางถังส่วนตัวและถังกลางข้อมูลไม่มาครับ ให้ยึด Sheet ข้อมูลดิบ1 ตรงcolumn C เป็นหลัก column D ไว้ดูชื่อของ agent เพราะใน C ชื่อ Agent ไม่ครบครับ Column C ตรงนี้เราต้องการนับแยกข้อมูลของAgent ถังส่วนตัว /ถังกลาง ( ถังส่วนตัว = ประจำส่วนตัว - Agent XX ,ถังกลาง = จะเป็นชื่ออื่นๆ โดยให้ดูชื่อ Agent เต็ม ที่อยู่ column D )
- ตาราง status การโทร ขาด "รับสาย" ครับ ยังไม่มาแสดงใน Report แสดงเพียง 2 status

**** sheet ข้อมูลดิบเราจะใช้วิธีการ Copy ของวันถัดไปมาวางทับข้อมูลเดิมนะครับ เช่น วันนี้ วันที่ 1 พรุ่งนี้ผมจะนำวันที่ 2 มาวางทับแทนแล้วให้สูตรไปแสดงใน sheet report ในช่องของวันถัดไปครับ

[snasui]

ทำตามที่ผมแจ้งไปตามข้อความนี้ด้วยครับ

ที่ชีต ข้อมูลดิบ1 ข้อมูลวันที่ในคอลัมน์ B จะต้องคัดลอกไปจนถึงบรรทัดสุดท้ายที่เป็นข้อมูลของวันนั้น ๆ

หากมีการคัดลอกวันที่ถัดไปมาวางทับแสดงว่าจะมีแค่วันที่เดียวในแต่ละครั้ง ตัวอย่างการปรับสูตรเป็นด้านล่างครับ

J5 คีย์

=IF(--LEFT(ข้อมูลดิบ1!$B$1,10)=LOOKUP(2,1/($B$5:$B5),$B$5:$B5),SUMPRODUCT(--(ISNUMBER(SEARCH($C5,ข้อมูลดิบ1!$D$4:$D$26))),--(ข้อมูลดิบ1!$E$4:$E$26=TRIM(J$4)),ข้อมูลดิบ1!$F$4:$F$26),0)

Enter > Copy ไป K5 และ Copy ลงด้านล่างไปยังเซลล์ที่เกี่ยวข้อง

ตาราง status การโทร ขาด "รับสาย" ครับ ยังไม่มาแสดงใน Report แสดงเพียง 2 status

เปลี่ยนคำว่า ANSWERD ในสูตรเป็นคำว่า ANSWERED ครับ

[asawin77]

- ถังส่วนตัวได้แล้วครับ
- เหลือถังกลางครับ ถังกลางก็อยู่ใน column C เหมือนกันครับ ที่ชื่อจะไม่ใช่ (ประจำส่วนตัว - Agent XX ) เป็นชื่ออื่น เช่น ในตัวอย่าง NEW CUST ,NEW อาหารเสริม renew จะนับเป็นถังกลางตัวครับ

*** ที่ชีต ข้อมูลดิบ1 ข้อมูลวันที่ในคอลัมน์ B จะต้องคัดลอกไปจนถึงบรรทัดสุดท้ายที่เป็นข้อมูลของวันนั้น ๆ
หากมีการคัดลอกวันที่ถัดไปมาวางทับแสดงว่าจะมีแค่วันที่เดียวในแต่ละครั้ง (ผมยังไม่เข้าใจในตรงนี้ครับ แต่ก็แอบคิดถ้าเอาวันที่ถัดไปมาวางทับ วันที่ผ่านมาข้อมูลก็หายถูกไมครับ)

[asawin77]

ต่อนะครับ หรือถ้าเปลี่ยนเป็นการนำข้อมูลมาวางเรียงกันลงด้านล่าง (Row) วันที่ 1 - 2 -3 ไปเรื่อยๆ ดีไมครับ

[snasui]

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

1. ที่ E5 คีย์
   =SUMPRODUCT(--(INT(ข้อมูลดิบ1!$B$4:$B$14)=LOOKUP(9.99999999999999e307,$B$5:B5)),--(ISNUMBER(SEARCH(C5,ข้อมูลดิบ1!$D$4:$D$14))),ข้อมูลดิบ1!$E$4:$E$14)
   Enter > Copy ลงด้านล่างยังเซลล์ที่เกี่ยวข้อง
2. ที่ J5 คีย์
   =SUMPRODUCT(--(LOOKUP(9.99999999999999e307,$B$5:$B5)=INT(ข้อมูลดิบ1!$B$4:$B$14)),--(ISNUMBER(SEARCH($C5,ข้อมูลดิบ1!$D$4:$D$14))),--(ISNUMBER(SEARCH("Agent",ข้อมูลดิบ1!$C$4:$C$14))))
   Enter > Copy ลงด้านล่างยังเซลล์ที่เกี่ยวข้อง
3. ที่ K5 คีย์
   =SUMPRODUCT(--(LOOKUP(9.99999999999999e307,$B$5:$B5)=INT(ข้อมูลดิบ1!$B$4:$B$14)),--(ISNUMBER(SEARCH($C5,ข้อมูลดิบ1!$D$4:$D$14))),--(ISNUMBER(SEARCH("NEW",ข้อมูลดิบ1!$C$4:$C$14))))
   Enter > Copy ลงด้านล่างยังเซลล์ที่เกี่ยวข้อง
4. ที่ M5 คีย์
   =SUMPRODUCT(--(INT(ข้อมูลดิบ2!$A$3:$A$194)=LOOKUP(9.99999999999999e307,$B$5:$B5)),--(ข้อมูลดิบ2!$B$3:$B$194=INDEX(ข้อมูลดิบ2!$H$3:$H$6,MATCH("*"&$C5,ข้อมูลดิบ2!$I$3:$I$6,0))),--(ข้อมูลดิบ2!$E$3:$E$194=LOOKUP(COLUMNS($J5:J5),{1;2;3},{"ANSWERED";"NO ANSWER";"BUSY"})))
   Enter > Copy ไปด้านขวาถึง O5 แล้ว Copy ลงด้านล่างยังเซลล์ที่เกี่ยวข้อง

*** ที่ชีต ข้อมูลดิบ1 ข้อมูลวันที่ในคอลัมน์ B จะต้องคัดลอกไปจนถึงบรรทัดสุดท้ายที่เป็นข้อมูลของวันนั้น ๆ
หากมีการคัดลอกวันที่ถัดไปมาวางทับแสดงว่าจะมีแค่วันที่เดียวในแต่ละครั้ง (ผมยังไม่เข้าใจในตรงนี้ครับ แต่ก็แอบคิดถ้าเอาวันที่ถัดไปมาวางทับ วันที่ผ่านมาข้อมูลก็หายถูกไมครับ)

ผมเข้าใจว่ามีบรรทัดที่ 16 - 26 ด้วยจึงคิดว่าต้องเติมวันที่ให้เต็มเพราะจะได้สะดวกในการหาค่าที่ต้องการเช่นค่าใน F23:F26

ต่อนะครับ หรือถ้าเปลี่ยนเป็นการนำข้อมูลมาวางเรียงกันลงด้านล่าง (Row) วันที่ 1 - 2 -3 ไปเรื่อยๆ ดีไมครับ

ถ้าอ้างอิงการสรุปผลในชีต Report ก็ควรจะเป็นเช่นนั้นครับ

[asawin77]

- สูตรคำนวณส่งมา Report ได้ตามตัวอย่างแล้วครับ ส่วนการนำข้อมูลดิบมาวางต้องวางแบบเรียงตาม Row ลงด้านล่างใช่ไมครับไม่ควรวางทับ สูตรถึงจะคำนวณส่งไปยังตาราง ตรงตามวันใน Report ซึ่งมีวันที่ 1 ถึง 30 ในตารางตัวอย่างผมใส่แค่ 1-4 เท่านั้นครับ
- เมื่อ copy สูตร มาใส่ตารางจริง ปรากฎว่าสูตรยังไปดึงข้อมูลของตารางตัวอย่างมา มา report ครับ

[snasui]

สูตรคำนวณส่งมา Report ได้ตามตัวอย่างแล้วครับ ส่วนการนำข้อมูลดิบมาวางต้องวางแบบเรียงตาม Row ลงด้านล่างใช่ไมครับไม่ควรวางทับ

ข้อมูลควรวางเรียงกันลงไปด้านล่างเรื่อย ๆ ไม่ใช่วางทับที่เดิม วันที่ถัดไปก็วางเรียงกันไปและจะต้องแก้ช่วงเซลล์ในสูตรให้รองรับข้อมูลที่อาจจะมีได้ครับ

เมื่อ copy สูตร มาใส่ตารางจริง ปรากฎว่าสูตรยังไปดึงข้อมูลของตารางตัวอย่างมา มา report ครับ

ต้องแก้สูตรให้อ้างอิงแหล่งข้อมูลให้ถูกต้องครับ

[asawin77]

ขอบคุณครับ ดูแล้ว Pivot แล้ว copy มาวางทีละวันจะเร็วกว่า ที่ทำแบบนี้เพราะป้าท่านหนึ่งขอมาให้ง่ายเข้าไว้ 5555++ เพราะแกไม่รู้เรื่อง Excel ครับ เดี๋ยวจะทำอธิบายให้แกเข้าใจมากกว่านี้ ขอบคุณมากๆครับ

[asawin77]

ข้อมูลควรวางเรียงกันลงไปด้านล่างเรื่อย ๆ ไม่ใช่วางทับที่เดิม วันที่ถัดไปก็วางเรียงกันไปและจะต้องแก้ช่วงเซลล์ในสูตรให้รองรับข้อมูลที่อาจจะมีได้ครับ
- ตอนนี้สูตรรองรับแบบไหนครับ แบบเรียงลงด้านล่างหรือแบบวางทับ

ต้องแก้สูตรให้อ้างอิงแหล่งข้อมูลให้ถูกต้องครับ
- ต้องทำแบบไหนครับ

[snasui]

ข้อมูลควรวางเรียงกันลงไปด้านล่างเรื่อย ๆ ไม่ใช่วางทับที่เดิม วันที่ถัดไปก็วางเรียงกันไปและจะต้องแก้ช่วงเซลล์ในสูตรให้รองรับข้อมูลที่อาจจะมีได้ครับ
- ตอนนี้สูตรรองรับแบบไหนครับ แบบเรียงลงด้านล่างหรือแบบวางทับ

สูตรที่ผมเขียนไปสอดคล้องกับข้อความที่ระบายสีเอาไว้เรียบร้อยแล้วครับ

ต้องแก้สูตรให้อ้างอิงแหล่งข้อมูลให้ถูกต้องครับ
- ต้องทำแบบไหนครับ

ขึ้นอยู่กับว่าปัจจุบันสูตรนั้นเป็นแบบไหน ลองแนบไฟล์ล่าสุดแล้วแจ้งปัญหาที่เป็นจึงจะตอบได้ชัดเจนครับ

[asawin77]

ตอนนี้ผมเอาข้อมูลของวันที่ 2 มาวางเรียงต่อกันแล้ว copy สูตร ลงไป จากนั้นลองแก้ไขสูตรปรากฎว่า Error ครับ รบกวนแนะนำด้วยครับ

[snasui]

ปรับช่วงเซลล์ในสูตรให้ขยายตามจำนวนบรรทัดของข้อมูลด้วยครับ

ข้อมูลถึงบรรทัดที่ 21 ดังภาพ

Data.png

แต่สูตรครอบคลุมแค่ 14 บรรทัด ดังภาพ เปลี่ยนเลข 14 ทุกตำแหน่งในสูตรนี้และสูตรอื่นให้เป็นเลขที่มากกว่าหรือเท่ากับ 21 สามารถใส่ตัวเลขเผื่อไว้เท่าที่คิดว่าข้อมูลจะขยายไปถึงครับ

Formula.png

[asawin77]

ขอบคุณครับ

[asawin77]

สวัสดีครับ มีข้อมูล Update ครับ ตอนนี้ผมนำมาใส่ตารางแบบจริงจึงมี Update ครับ

- Sheet Report : Column E => Outbound => PO ให้นับจำนวน Po ตาม agent (ใน Sheet ยอดขาย Column A) ครับ ส่วนยอดขายได้ตรงแล้ว
- Sheet Report : หัวข้อ ยอดขาย ถังกลาง- ถังส่วนตัว => Column M นับจำนวน PO ยังถูกไม่ทั้งหมดเนื่องจากเมื่อเพิ่มข้อมูลของวันอื่นๆเข้ามาแล้วจะมีช่องว่างสูตรจะไม่นับ อยากให้นับรวมเป็นถังกลางครับ ถังส่วนตัวนับถูกแล้ว และรวมยอดขาย Column N และ P ซึ่งจะเป็นยอดขายแยกถังครับ ตัวอย่างตามไฟล์แนบครับ

[Bo_ry]

F9
=SUMIFS(ยอดขาย!$E$2:$E$999,ยอดขาย!$D$2:$D$999,"*"&IF($C9="Total",,$C9),ยอดขาย!$B$2:$B$999,">="&LOOKUP(9^9,$B$9:$B9),ยอดขาย!$B$2:$B$999,"<"&LOOKUP(9^9,$B$9:$B9)+1)

M9
=COUNTIFS(ยอดขาย!$D$2:$D$999,"*"&IF($C9="Total",,$C9),ยอดขาย!$B$2:$B$999,">="&LOOKUP(9^9,$B$9:$B9),ยอดขาย!$B$2:$B$999,"<"&LOOKUP(9^9,$B$9:$B9)+1,ยอดขาย!$C$2:$C$999,"*agent*")

O9
=COUNTIFS(ยอดขาย!$D$2:$D$999,"*"&IF($C9="Total",,$C9),ยอดขาย!$B$2:$B$999,">="&LOOKUP(9^9,$B$9:$B9),ยอดขาย!$B$2:$B$999,"<"&LOOKUP(9^9,$B$9:$B9)+1,ยอดขาย!$C$2:$C$999,"<>*agent*")

S9:U9
=COUNTIFS(Call!$B$2:$B$9999,IF($C9="Total","<>",INDEX(Call!$H$2:$H$6,MATCH("*"&$C9,Call!$I$2:$I$6,0))),Call!$A$2:$A$9999,TEXT(LOOKUP(9^9,$B$9:$B9),"yyyy-mm-dd\*"),Call!$E$2:$E$9999,CHOOSE(COLUMNS($S9:S9),"A*","N*","B*"))

[asawin77]

- Sheet Report : Column E => Outbound => PO ให้นับจำนวน Po ตาม agent (ใน Sheet ยอดขาย Column A) ครับ ส่วนยอดขายได้ตรงแล้ว
- Sheet Report : หัวข้อ ยอดขาย ถังกลาง- ถังส่วนตัว => Column M นับจำนวน PO ยังถูกไม่ทั้งหมดเนื่องจากเมื่อเพิ่มข้อมูลของวันอื่นๆเข้ามาแล้วจะมีช่องว่างสูตรจะไม่นับ อยากให้สูตรนับรวมเป็นถังกลางครับ ถังส่วนตัวนับถูกแล้ว และยังเหลือรวมยอดขาย Column N และ P ตัวอย่างตามไฟล์แนบเดิมด้านบนครับ