Excel Forum

ฟอรัม Excel, VBA และอื่นๆ ของคนไทยเพื่อประโยชน์ของทุกคนในจักรวาล (Forum Excel, VBA and others of Thai people for everyone in the universe.)
https://snasui.com/

ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

https://snasui.com/viewtopic.php?t=14281

Page 1 of 1

ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Mon Oct 08, 2018 12:55 am
by n9c5
ุผมมีข้อมูลสินค้าออกทั้งหมด 7 เดือนซึ่งแยกกันอยู่คนละ sheet อยากให้มารวมอยู่ในชีทเดียวกันและ รวมจำนวนสินค้าที่มีรุ่นเดียวกันทั้งหมด 7 เดือนมีสูตรอะไรบ้างครับ

Re: ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Mon Oct 08, 2018 6:55 am
by snasui
:D ตัวอย่างสูตรตามด้านล่างครับ
  1. ที่ E2:E3 คีย์ชื่อชีตที่จะนำข้อมูลมารวมกัน ตามตัวอย่างไฟล์ที่แนบมาคือ Sheet1, Sheet2 ตามลำดับ
    Enter > Copy ลงด้านล่าง
  2. ที่ B2 คีย์
    =SUMPRODUCT(SUMIF(INDIRECT("'"&$E$2:$E$3&"'!A2:A100"),A2,INDIRECT("'"&$E$2:$E$3&"'!B2:B100")))
    Enter > Copy ลงด้านล่าง

Re: ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Mon Oct 08, 2018 8:39 pm
by n9c5
snasui wrote: Mon Oct 08, 2018 6:55 am :D ตัวอย่างสูตรตามด้านล่างครับ
  1. ที่ E2:E3 คีย์ชื่อชีตที่จะนำข้อมูลมารวมกัน ตามตัวอย่างไฟล์ที่แนบมาคือ Sheet1, Sheet2 ตามลำดับ
    Enter > Copy ลงด้านล่าง
  2. ที่ B2 คีย์
    =SUMPRODUCT(SUMIF(INDIRECT("'"&$E$2:$E$3&"'!A2:A100"),A2,INDIRECT("'"&$E$2:$E$3&"'!B2:B100")))
    Enter > Copy ลงด้านล่าง
ขอบคุณมากครับผมรบกวนอีกหน่อยครับ ตรงรุ่นสินค้าเราจะย้ายมารวมชีทได้ยังไงบ้างครับ ในแต่ละเดือนรุ่นสินค้ามีซ้ำบ้างไม่มีบ้างจะรวมยังไงให้ครบและไม่ซ้ำกันครับ

Re: ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Mon Oct 08, 2018 9:05 pm
by snasui
:D ตัวอย่างสูตรที่ Sheet3 ตามด้านล่างครับ
  1. เซลล์ E1, F1, H1, I1 คีย์ PrdList, SheetList, SheetName, Count เพื่อเป็นหัวคอลัมน์ตามลำดับ
  2. ที่ H2:H3 คีย์ชื่อชีตคือ Sheet1, Sheet2 มีกี่ชีตก็คีย์เรียงลงไปด้านล่างตามต้องการ
  3. ที่ I2 คีย์
    =COUNT(INDIRECT("'"&H2&"'!B2:B1000"))
    Enter > Copy ลงด้านล่างถึง I3
  4. ที่ F2 คีย์
    =IF(ROWS(F$2:F2)>SUM($I$2:$I$3),"",LOOKUP(ROWS(F$2:F2),SUBTOTAL(9,OFFSET($I$2,0,0,ROW($I$2:$I$3)-ROW($I$2)+1))-SUBTOTAL(9,OFFSET($I$2,ROW($I$2:$I$3)-ROW($I$2),0))+1,$H$2:$H$3))
    Enter > Copy ลงด้านล่างถึง F3
  5. ที่ E2 คีย์
    =IF(F2="","",INDIRECT("'"&F2&"'!A"&COUNTIF(F$2:F2,F2)+1))
    Enter > Copy ลงด้านล่างจนเห็นเป็นค่าว่าง
  6. ที่ A2 คีย์
    =IFERROR(INDEX(Sheet3!$E$2:$E$1000,SMALL(IF(FREQUENCY(IF(Sheet3!$E$2:$E$1000<>"",MATCH(Sheet3!$E$2:$E$1000,Sheet3!$E$3:$E$1001,0)),ROW(Sheet3!$E$2:$E$1000)-ROW(Sheet3!$F$2)+1),ROW(Sheet3!$E$2:$E$1000)-ROW(Sheet3!$F$2)+1),ROWS(A$2:A2))),"")
    Ctrl+Shift+Enter > Copy ลงด้านล่างจนเห็นเป็นค่าว่าง

!
Note: Ctrl+Shift+Enter
เป็นการสร้างสูตร Array โดยสามารถเลือกดำเนินการตามข้อ 1 หรือ 2 ด้านล่าง
  1. กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น
    Ctrl และ Shift พร้อมกันค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array
  2. กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน
    จากนั้นกดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วตามด้วย Enter เพื่อสร้างเป็นสูตร Array
  3. หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)}
     ปีกกานี้ไม่สามารถคีย์เข้าไปเอง จะต้องมาด้วยการกดแป้น Ctrl+Shift+Enter เท่านั้น
    ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง
  4. การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

Re: ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Tue Oct 09, 2018 9:25 am
by n9c5
snasui wrote: Mon Oct 08, 2018 9:05 pm :D ตัวอย่างสูตรที่ Sheet3 ตามด้านล่างครับ
  1. เซลล์ E1, F1, H1, I1 คีย์ PrdList, SheetList, SheetName, Count เพื่อเป็นหัวคอลัมน์ตามลำดับ
  2. ที่ H2:H3 คีย์ชื่อชีตคือ Sheet1, Sheet2 มีกี่ชีตก็คีย์เรียงลงไปด้านล่างตามต้องการ
  3. ที่ I2 คีย์
    =COUNT(INDIRECT("'"&H2&"'!B2:B1000"))
    Enter > Copy ลงด้านล่างถึง I3
  4. ที่ F2 คีย์
    =IF(ROWS(F$2:F2)>SUM($I$2:$I$3),"",LOOKUP(ROWS(F$2:F2),SUBTOTAL(9,OFFSET($I$2,0,0,ROW($I$2:$I$3)-ROW($I$2)+1))-SUBTOTAL(9,OFFSET($I$2,ROW($I$2:$I$3)-ROW($I$2),0))+1,$H$2:$H$3))
    Enter > Copy ลงด้านล่างถึง F3
  5. ที่ E2 คีย์
    =IF(F2="","",INDIRECT("'"&F2&"'!A"&COUNTIF(F$2:F2,F2)+1))
    Enter > Copy ลงด้านล่างจนเห็นเป็นค่าว่าง
  6. ที่ A2 คีย์
    =IFERROR(INDEX(Sheet3!$E$2:$E$1000,SMALL(IF(FREQUENCY(IF(Sheet3!$E$2:$E$1000<>"",MATCH(Sheet3!$E$2:$E$1000,Sheet3!$E$3:$E$1001,0)),ROW(Sheet3!$E$2:$E$1000)-ROW(Sheet3!$F$2)+1),ROW(Sheet3!$E$2:$E$1000)-ROW(Sheet3!$F$2)+1),ROWS(A$2:A2))),"")
    Ctrl+Shift+Enter > Copy ลงด้านล่างจนเห็นเป็นค่าว่าง

!
Note: Ctrl+Shift+Enter
เป็นการสร้างสูตร Array โดยสามารถเลือกดำเนินการตามข้อ 1 หรือ 2 ด้านล่าง
  1. กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น
    Ctrl และ Shift พร้อมกันค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array
  2. กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน
    จากนั้นกดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วตามด้วย Enter เพื่อสร้างเป็นสูตร Array
  3. หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)}
     ปีกกานี้ไม่สามารถคีย์เข้าไปเอง จะต้องมาด้วยการกดแป้น Ctrl+Shift+Enter เท่านั้น
    ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง
  4. การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง
ผมลองทำแล้วมันได้ค่าซ้ำ ผมผิดส่วนไหนหรือเปล่าครับ
https://img.live/image/LHuai
(ขอโทษนะครับพอดีผมใส่รูปไม่ได้)

Re: ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Tue Oct 09, 2018 9:55 am
by parakorn
ลองปรับรูปให้มีขนาดเล็กลงครับ หากไม่แนบรูปในบอร์ด จะมีผลทำให้ผู้ที่เข้ามาศึกษาในภายภาคหน้าเปิดรูปไม่ได้ครับ
A2 ถึง A8 คือผลลัพท์ตามที่ต้องการ ซ้ำตรงไหนครับ ลองแนบไฟล์ที่ได้ทดลองทำ และแจ้งปัญหามาดูครับ

Re: ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Tue Oct 09, 2018 11:23 am
by n9c5
parakorn wrote: Tue Oct 09, 2018 9:55 am ลองปรับรูปให้มีขนาดเล็กลงครับ หากไม่แนบรูปในบอร์ด จะมีผลทำให้ผู้ที่เข้ามาศึกษาในภายภาคหน้าเปิดรูปไม่ได้ครับ
A2 ถึง A8 คือผลลัพท์ตามที่ต้องการ ซ้ำตรงไหนครับ ลองแนบไฟล์ที่ได้ทดลองทำ และแจ้งปัญหามาดูครับ
ไม่ค่อยคล่องต้องขอโทษด้วยนะครับ ตามไฟล์ด้านล่างครับ

Re: ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Tue Oct 09, 2018 12:56 pm
by puriwutpokin
n9c5 wrote: Tue Oct 09, 2018 11:23 am
parakorn wrote: Tue Oct 09, 2018 9:55 am ลองปรับรูปให้มีขนาดเล็กลงครับ หากไม่แนบรูปในบอร์ด จะมีผลทำให้ผู้ที่เข้ามาศึกษาในภายภาคหน้าเปิดรูปไม่ได้ครับ
A2 ถึง A8 คือผลลัพท์ตามที่ต้องการ ซ้ำตรงไหนครับ ลองแนบไฟล์ที่ได้ทดลองทำ และแจ้งปัญหามาดูครับ
ไม่ค่อยคล่องต้องขอโทษด้วยนะครับ ตามไฟล์ด้านล่างครับ
ที่ A2 ปรับเป็น=IFERROR(INDEX($E$2:$E$1000,SMALL(IF(FREQUENCY(IF($E$2:$E$1000<>"",MATCH($E$2:$E$1000,$E$2:$E$1000,0)),ROW($E$2:$E$1000)-ROW($F$2)+1),ROW($E$2:$E$1000)-ROW($F$2)+1),ROWS(A$2:A2))),"")
แล้ว กด Ctrl+Shift+Enter ครับ ลากลงครับ ส่วนสูตร ของอาจารย์ ตัวอื่นไม่ต้องกด Ctrl+Shift+Enter

Re: ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Tue Oct 09, 2018 9:11 pm
by snasui
puriwutpokin wrote: Tue Oct 09, 2018 12:56 pm ...MATCH($E$2:$E$1000,$E$2:$E$1000,0)...
:o ขอบคุณที่ช่วยแก้ให้ครับ :thup:

Re: ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Tue Oct 09, 2018 9:36 pm
by puriwutpokin
snasui wrote: Tue Oct 09, 2018 9:11 pm
puriwutpokin wrote: Tue Oct 09, 2018 12:56 pm ...MATCH($E$2:$E$1000,$E$2:$E$1000,0)...
:o ขอบคุณที่ช่วยแก้ให้ครับ :thup:
ยินดีครับอาจารย์ ช่วงนี้ไม่ได้เข้ามานานมากเลยครับ งานเยอะมากๆ ไม่ได้มาลับสมองเลยครับ

Re: ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Wed Oct 10, 2018 11:43 am
by n9c5
puriwutpokin wrote: Tue Oct 09, 2018 12:56 pm
parakorn wrote: Tue Oct 09, 2018 9:55 am ลองปรับรูปให้มีขนาดเล็กลงครับ หากไม่แนบรูปในบอร์ด จะมีผลทำให้ผู้ที่เข้ามาศึกษาในภายภาคหน้าเปิดรูปไม่ได้ครับ
A2 ถึง A8 คือผลลัพท์ตามที่ต้องการ ซ้ำตรงไหนครับ ลองแนบไฟล์ที่ได้ทดลองทำ และแจ้งปัญหามาดูครับ

ที่ A2 ปรับเป็น=IFERROR(INDEX($E$2:$E$1000,SMALL(IF(FREQUENCY(IF($E$2:$E$1000<>"",MATCH($E$2:$E$1000,$E$2:$E$1000,0)),ROW($E$2:$E$1000)-ROW($F$2)+1),ROW($E$2:$E$1000)-ROW($F$2)+1),ROWS(A$2:A2))),"")
แล้ว กด Ctrl+Shift+Enter ครับ ลากลงครับ ส่วนสูตร ของอาจารย์ ตัวอื่นไม่ต้องกด Ctrl+Shift+Enter
ขอบคุณมากครับ ผมเจอปัญหาอีกอย่างนึงครับ พอได้รุ่นสินค้า จำนวนมาแล้ว ผม sort ข้อมูลให้จำนวนมากสุดเรียงไปน้อย แล้วมันกลับมาเหมือนเดิมครับ(เข้าใจว่าอาจจะติดจากสูตร) ไม่ราบว่าพอมีวิธีไหนในการจัดเรียงบ้างมั้ยครับ

Re: ต้องการรวมข้อมูลในแต่ละชีทอยู่ในชีทเดียว

Posted: Wed Oct 10, 2018 12:23 pm
by puriwutpokin
n9c5 wrote: Wed Oct 10, 2018 11:43 am
puriwutpokin wrote: Tue Oct 09, 2018 12:56 pm
parakorn wrote: Tue Oct 09, 2018 9:55 am ลองปรับรูปให้มีขนาดเล็กลงครับ หากไม่แนบรูปในบอร์ด จะมีผลทำให้ผู้ที่เข้ามาศึกษาในภายภาคหน้าเปิดรูปไม่ได้ครับ
A2 ถึง A8 คือผลลัพท์ตามที่ต้องการ ซ้ำตรงไหนครับ ลองแนบไฟล์ที่ได้ทดลองทำ และแจ้งปัญหามาดูครับ

ที่ A2 ปรับเป็น=IFERROR(INDEX($E$2:$E$1000,SMALL(IF(FREQUENCY(IF($E$2:$E$1000<>"",MATCH($E$2:$E$1000,$E$2:$E$1000,0)),ROW($E$2:$E$1000)-ROW($F$2)+1),ROW($E$2:$E$1000)-ROW($F$2)+1),ROWS(A$2:A2))),"")
แล้ว กด Ctrl+Shift+Enter ครับ ลากลงครับ ส่วนสูตร ของอาจารย์ ตัวอื่นไม่ต้องกด Ctrl+Shift+Enter
ขอบคุณมากครับ ผมเจอปัญหาอีกอย่างนึงครับ พอได้รุ่นสินค้า จำนวนมาแล้ว ผม sort ข้อมูลให้จำนวนมากสุดเรียงไปน้อย แล้วมันกลับมาเหมือนเดิมครับ(เข้าใจว่าอาจจะติดจากสูตร) ไม่ราบว่าพอมีวิธีไหนในการจัดเรียงบ้างมั้ยครับ
ใช่ครับ เป็นที่สูตร เนื่องจากเป็นการดึงข้อมูลมาจากชีทหลายชีท ซึ่ง ต้องคงต้อง คัดลอก ข้อมูลที่ได้แล้ว ไปว่างเป็นค่าที่ชีทอื่น เพื่อให้เรียงข้อมูลครับ
All times are UTC+07:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited