Snasui Excel Forum

สวัสดีครับอาจาร์ผมเป็นสมาชิกใหม่ แต่เข้ามาดูใน web นี้นานแล้วครับและเป็นประโยชน์กำบผมมาเลยครับตอนนี้ผมเจอปัญหาเรื่องดึงข้อมูลข้าม Sheet แบบมีหลายเงื้อนไขครับ ผมลองแก้เองและหาในกระทู้ก็ยังทำไม่ได้ครับผมจึงอยากรบกวนอาจารย์และพี่ในนี้ช่วยชี้ทางให้ผมหน่อยครับ สิ่งที่ผมต้องการ
1.นำข้อมูลใน Sheet 1 ไปไว้ Sheet 2 โดยมีเงื้อนไขว่า ข้อมูลที่เหมือนกันตั้งแต่ A ถึง E ส่งไป Sheet 2 ให้เหลือแค่ข้อมูลเดียวและนำจำนวนในช่อง F มารวมกัน
2.ถ้ามีช่องใดช่องหนึ่งที่ไม่เหมือนกันให้เรียงออกมาในลำดับต่อไป
3.ถ้าเพิ่มข้อมูลต่อใน sheet 1 เมื่อไหร่ให้ย้ายไปอัตโนมัติเลยครับ
พอจะมีสูตรที่ทำได้หรือเปล่าครับ
ผมแนบ File มาพร้อมด้วยครับ
ขอบคุณครับ

ลองดูครับ

Sheet1

G6 =A6&B6&C6&D6&E6

คัดลอกสูตรลงด้านล่าง

Sheet2

A2 =IFERROR(INDEX(Sheet1!A$6:A$12,SMALL(IF(FREQUENCY(MATCH(Sheet1!$G$6:$G$12,Sheet1!$G$6:$G$12,0),ROW(Sheet1!$A$6:$A$12)-ROW(Sheet1!$A$6)+1),ROW(Sheet1!$A$6:$A$12)-ROW(Sheet1!$A$6)+1),ROWS(A$2:A2))),"")

Ctrl+Shift+Enter คัดลอกสูตรไปด้านขวาถึง E2 แล้วคัดลอกลงด้านล่าง

F2 =SUMIFS(Sheet1!$F$6:$F$12,Sheet1!$A$6:$A$12,A2,Sheet1!$B$6:$B$12,B2,Sheet1!$C$6:$C$12,C2,Sheet1!$D$6:$D$12,D2,Sheet1!$E$6:$E$12,E2)

Enter คัดลอกสูตรลงด้านล่าง

ได้แล้วครับแต่ติดนิดหน่อยครับพอเพิ่มไปใน Sheet 1 แถวที่ 13,14 ข้อมูลก็ไม่รันขึ้นมาเพิ่มครับ เพราะว่างานตอนนี้งานของผมรันกันทุกชั่วโมงครับพอผมไปปรับสูตรใน Sheet 2 ให้เพื่อรอไว้เลยกลับไม่ขึ้นอะไรเลยครับ
อันนี้คือสูตรที่แนะนำมา

Sheet 2

A2 =IFERROR(INDEX(Sheet1!A$6:A$12,SMALL(IF(FREQUENCY(MATCH(Sheet1!$G$6:$G$12,Sheet1!$G$6:$G$12,0),ROW(Sheet1!$A$6:$A$12)-ROW(Sheet1!$A$6)+1),ROW(Sheet1!$A$6:$A$12)-ROW(Sheet1!$A$6)+1),ROWS(A$2:A2))),"")

ส่วนอันนี้ผมเอาไปแก้ให้เป็นล่วงหน้า

Sheet 2

A2 =IFERROR(INDEX(Sheet1!A$6:A$30,SMALL(IF(FREQUENCY(MATCH(Sheet1!$G$6:$G$30,Sheet1!$G$6:$G$30,0),ROW(Sheet1!$A$6:$A$30)-ROW(Sheet1!$A$6)+1),ROW(Sheet1!$A$6:$A$30)-ROW(Sheet1!$A$6)+1),ROWS(A$2:A2))),"")

ตัวสีแดงคือที่ผมแก้ไขพอเสร็จแล้วกับไม่มีข้อความอะไรขึ้นเลยครับผมคิดว่าน่าจะไม่ตรงเงือนไข
พอจะมีวิธีแก้ไขเปล่าครับผมคิดจนปวดหัวแล้วครับ
ขอบคุณล่วงหน้าครับ

ปรับตามสูตรเดิมเป็น A2=IFERROR(INDEX(Sheet1!A$6:A$30,SMALL(IF(FREQUENCY(IF(Sheet1!$G$6:$G$30<>"",MATCH(Sheet1!$G$6:$G$30,Sheet1!$G$6:$G$30,0)),ROW(Sheet1!$A$6:$A$30)-ROW(Sheet1!$A$6)+1),ROW(Sheet1!$A$6:$A$30)-ROW(Sheet1!$A$6)+1),ROWS(A$2:A2))),"")
ที่ F2=SUMIFS(Sheet1!$F$6:$F$30,Sheet1!$G$6:$G$30,A2&B2&C2&D2&E2)

ได้แล้วครับผมสงสัยนิดนึงครับผมนั่งดูสูตรแล้วมีการปรับในสูตรตรงที่ผมทำตัวสีแดงใช่ไหมครับ

=IFERROR(INDEX(Sheet1!A$6:A$30,SMALL(IF(FREQUENCY(IF(Sheet1!$G$6:$G$30<>"",MATCH(Sheet1!$G$6:$G$30,Sheet1!$G$6:$G$30,0)),ROW(Sheet1!$A$6:$A$30)-ROW(Sheet1!$A$6)+1),ROW(Sheet1!$A$6:$A$30)-ROW(Sheet1!$A$6)+1),ROWS(A$2:A2))),"")

แล้วถ้ามีการต่อเกินจาก A30 ผมต้องแก้อะไรเพิ่มหรือเปล่าครับ
ขอบคุณครับ

ใช้ได้เลย ไม่ต้องแก้ครับ

ขอเพิ่มอีกนิดน่ะครับ คือผมอาจอธิบายผิดไปสูตรที่แก้ให้ผมนั้นตรงตามเป้าหมายเลยครับแต่พอเกินจาก A30 ไปข้อมูลไม่ถูกส่งไป sheet 2 ผมเลยมานั่งดูอีกทีเลยลองเปลี่ยนตัวเลขแถวใหม่ให้เป็น A100 เลยเพื่อล่วงหน้าตามที่ผมใส่ตัวสีแดงไว้

=IFERROR(INDEX(Sheet1!A$6:A$100,SMALL(IF(FREQUENCY(IF(Sheet1!$G$6:$G$100<>"",MATCH(Sheet1!$G$6:$G$100,Sheet1!$G$6:$G$100,0)),ROW(Sheet1!$A$6:$A$100)-ROW(Sheet1!$A$6)+1),ROW(Sheet1!$A$6:$A$100)-ROW(Sheet1!$A$6)+1),ROWS(A$2:A2))),"")

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

ก่อนอื่นต้องขอบอกว่าพิมพ์ยาวได้ตามต้องการครับ

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

การทำให้สูตรยืดหยุ่นตามปริมาณข้อมูลสามารถทำได้ง่าย ๆ ด้วยการทำ Database ให้เป็น Table วิธีการคือคลิกลงไปในฐานข้อมูลของเรา > เข้าเมนู Insert > Table > ทำเครื่องหมายที่ My data has header > OK สูตรจะขยายหรือยุบตามตารางนี้ตลอดไป

อีกวิธีใช้ Range Name เข้ามาช่วย แต่อยากให้ใช้ Table เข้ามาช่วยก่อนดีกว่าครับ

ขอบคุณมากครับอาจารย์ ผมจะลองปรับแก้ดูครับ

ผมลองทำแล้วครับใช้ยากเหมื่อนกันครับผมไม่เคยใช้เลย รบกวนทุกท่านช่วยดูให้หน่อยครับว่าผมทำถูกต้องหรือเปล่า
1.ผมทำฐานข้อมูลเป็น Table
2.ผมทำข้อมูลดิบที่มีการเพิ่มทุกวันเป็น excel
แต่เวลาผมจะเพิ่มข้อมูลไปที่ฐานข้อมูลผมต้องไปที่เมนู "เปลี่ยนแหล่งข้อมูล" ถูกต้องหรือเปล่าครับ
เพราะผมมีโครงการประมาณนี้ครับ
1.จะมีเครื่องที่ลงข้อมูลวัตถุดิบ 2-3 เครื่อง
2.จะมีเครื่องที่ทำเป็นฐานข้อมูล 1 เครื่อง
3.และจะเอาข้อมูลดิบมารวมในฐานข้อมูลเพื่อนำข้อมูลมาประเมินสถาณะการ
ประมาณนี้ครับ แต่ผมอยากจะศึกษาแบบข้าม Sheet ไปก่อน รบกวนทุกท่าช่วยชี้แนะด้วยครับ
ผมมีแนบไฟล์มาให้ช่วยดูด้วยครับ
ปล.ผมเข้าไปค้นในกระทู้เจอกระทู้ที่มีการแนะนำหนังสือไว้ชื่อ " จัดฐานข้อมูลด้วย Execl " ตอนนี้ของหมดครับไม่ทราบว่ามีเล่นอ่นหรือหาซื้อได้ที่ไหนครับ
ขอบคุณทุท่านล่วงหน้าครับ

Wara wrote: Sat Feb 03, 2018 9:55 am ผมลองทำแล้วครับใช้ยากเหมื่อนกันครับผมไม่เคยใช้เลย รบกวนทุกท่านช่วยดูให้หน่อยครับว่าผมทำถูกต้องหรือเปล่า
1.ผมทำฐานข้อมูลเป็น Table
2.ผมทำข้อมูลดิบที่มีการเพิ่มทุกวันเป็น excel
แต่เวลาผมจะเพิ่มข้อมูลไปที่ฐานข้อมูลผมต้องไปที่เมนู "เปลี่ยนแหล่งข้อมูล" ถูกต้องหรือเปล่าครับ
เพราะผมมีโครงการประมาณนี้ครับ
1.จะมีเครื่องที่ลงข้อมูลวัตถุดิบ 2-3 เครื่อง
2.จะมีเครื่องที่ทำเป็นฐานข้อมูล 1 เครื่อง
3.และจะเอาข้อมูลดิบมารวมในฐานข้อมูลเพื่อนำข้อมูลมาประเมินสถาณะการ
ประมาณนี้ครับ แต่ผมอยากจะศึกษาแบบข้าม Sheet ไปก่อน รบกวนทุกท่าช่วยชี้แนะด้วยครับ
ผมมีแนบไฟล์มาให้ช่วยดูด้วยครับ
ปล.ผมเข้าไปค้นในกระทู้เจอกระทู้ที่มีการแนะนำหนังสือไว้ชื่อ " จัดฐานข้อมูลด้วย Execl " ตอนนี้ของหมดครับไม่ทราบว่ามีเล่นอ่นหรือหาซื้อได้ที่ไหนครับ
ขอบคุณทุท่านล่วงหน้าครับ

การสร้าง Table ให้ไปที่ตารางข้อมูล แล้วกด Ctrl+T
แล้วไปที่ Pivot table เลือกแหล่งข้อมูลใหม่ แล้วเลือกไปที่ตารางที่สร้างครับ

Wara wrote: Sat Feb 03, 2018 9:55 am ผมลองทำแล้วครับใช้ยากเหมื่อนกันครับผมไม่เคยใช้เลย รบกวนทุกท่านช่วยดูให้หน่อยครับว่าผมทำถูกต้องหรือเปล่า
1.ผมทำฐานข้อมูลเป็น Table
2.ผมทำข้อมูลดิบที่มีการเพิ่มทุกวันเป็น excel
แต่เวลาผมจะเพิ่มข้อมูลไปที่ฐานข้อมูลผมต้องไปที่เมนู "เปลี่ยนแหล่งข้อมูล" ถูกต้องหรือเปล่าครับ
เพราะผมมีโครงการประมาณนี้ครับ
1.จะมีเครื่องที่ลงข้อมูลวัตถุดิบ 2-3 เครื่อง
2.จะมีเครื่องที่ทำเป็นฐานข้อมูล 1 เครื่อง
3.และจะเอาข้อมูลดิบมารวมในฐานข้อมูลเพื่อนำข้อมูลมาประเมินสถาณะการ
ประมาณนี้ครับ แต่ผมอยากจะศึกษาแบบข้าม Sheet ไปก่อน รบกวนทุกท่าช่วยชี้แนะด้วยครับ
ผมมีแนบไฟล์มาให้ช่วยดูด้วยครับ
ปล.ผมเข้าไปค้นในกระทู้เจอกระทู้ที่มีการแนะนำหนังสือไว้ชื่อ " จัดฐานข้อมูลด้วย Execl " ตอนนี้ของหมดครับไม่ทราบว่ามีเล่นอ่นหรือหาซื้อได้ที่ไหนครับ
ขอบคุณทุท่านล่วงหน้าครับ

PivotTable กับ Table เป็นคนละ Object เป็นคนละความสามารถ ที่ผมพูดถึงคือ Table เป็นการตอบคำถามเรื่องสูตรที่ยืดหยุ่น ไม่ใช่ PivotTable ที่เป็น Object สำหรับการทำรายงานครับ

แต่หากต้องการจะทำรายงานด้วย PivotTable และต้องการให้ฐานข้อมูลยืดหยุ่นก็ต้องสร้างข้อมูลให้เป็น Table เช่นกัน ผมแจ้งไปแล้วว่าสร้าง Table ด้วยวิธีใด การตอบในฟอรัมนี้ผมจะใช้เมนูภาษาอังกฤษ หากใช้ภาษาไทยหรือภาษาอื่นจำเป็นต้องเทียบเคียงเองครับ

กรณีที่ทำ PivotTable แล้วไม่ได้ข้อมูลตามที่ต้องการ ซึ่งคุณ Wara ต้องทราบได้เองว่าถูกต้องหรือไม่ อย่างไร หากไม่ถูกต้องให้ทำตัวอย่างคำตอบที่ต้องการมาด้วย จะได้ช่วยดูให้ได้ครับ

ครับอาจาราย์ผมกำลังทำอยู่ครับเดี๋ยวติดข้อส่งสัยตรงไหนจะรีบถามเลยครับ ส่วนเรื่อง PivotTable กับ Table ผมเข้าใจผิดเองครับของผมเป็นภาษาไทยผมกำลังหาข้อมูลมาเทียบเคียงตามที่อาจารย์บอกครับ
ขอบคุณครับ

อาจารย์ครับผมทำตามที่อาจารย์แนะนำเรื่อง Table ผมแนบตัวอย่างมาให้อาจารย์ดูที่ผมทำถูกต้องหรือเปล่าครับ
และผมกำลังใส่ข้อมูลและปรับตารางนิดหน่อยเพื่อให้เหมาะกับการทำงานของผมยังงัยแล้วผมจะมาขอคำแนะนำเรื่อยๆในกระทู้นี้น่ะครับ
ขอบคุณอาจารย์ทุกท่านที่เข้ามาให้ความรู้ผมน่ะครับ
ขอบคุณครับ

ทำถูกวิธีแต่ไม่ถูกชีตครับ

ชีตที่จะต้องทำเป็น Table คือ ข้อมูลดิบ ส่วนชีต ฐานข้อมูล เป็นการดึงข้อมูลดิบมาแสดงผล

การที่เราใส่ Table ให้กับข้อมูลดิบ เราไม่ต้องแก้ไขสูตรหากว่ามีข้อมูลเพิ่มหรือลดในชีตข้อมูลดิบครับ

Wara wrote: Sat Feb 03, 2018 2:07 pm อาจารย์ครับผมทำตามที่อาจารย์แนะนำเรื่อง Table ผมแนบตัวอย่างมาให้อาจารย์ดูที่ผมทำถูกต้องหรือเปล่าครับ
และผมกำลังใส่ข้อมูลและปรับตารางนิดหน่อยเพื่อให้เหมาะกับการทำงานของผมยังงัยแล้วผมจะมาขอคำแนะนำเรื่อยๆในกระทู้นี้น่ะครับ
ขอบคุณอาจารย์ทุกท่านที่เข้ามาให้ความรู้ผมน่ะครับ
ขอบคุณครับ

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

ขอบคุณครับ อาจารย์ทั้งสองท่านผมตาสว่างเลยครับผมงมอยู่ตั้งนาน
ยังงัยผมจะรบกวนปรึกษาอีกเรื่อยๆน่ะครับ
ขอบคุณอีกครังครับ

สวัสดีครับอาจารย์ทุกท่าน วันนี้ผมติดปัญหาเรื่องสูตร VLOOKUP แบบมีเงื่อนไข
ความต้องการ
1.เมื่อผมกำหนดหมายเลขเครื่องในช่อง A1 ที่ Sheet ฐานข้อมูล ข้องมูลตั้งแต่ B:G ที่กำหนดจะถูงส่งไปอยู่ใน Sheet เครื่อง 5
ผมลองแกะสูตรที่อาจารย์ทุท่านเคยตอบไว้และลองแก้สูตรเองทำไปทำมา ข้อมูลไปแต่ไปไม่หมดที่แถวที่กำหนดเป็นเครื่องที่ 5
ผมลองแก้แล้วแต่แก้ไม่ได้จริงๆจึงอยากถามอาจารร์ทุกท่านว่า ผมควรใช้สูตรแบบไหน
สูตรนี้ที่ผมลองปรับเอง
=(IFERROR(INDEX(ฐานข้อมูล!B$2:B$11,SMALL(IF(ฐานข้อมูล!$A$2:$A$11=$B$1,ROW(ฐานข้อมูล!$B$2:$B$11)-ROW(ฐานข้อมูล!$B$2)+1),ROWS($B$3:B3))),"")

ดึงข้อมูลได้แต่ไปไม่ครบ
ผมแนบ file มาให้อาจารย์ทุกท่านช่วยดูด้วยครับ ความต้องการผมอธิบายไว้ใน File ด้วยครับ
ขอบคุณครับ

ปรับใหม่ครับ=IFERROR(INDEX(ฐานข้อมูล!B$2:B$11,SMALL(IF(ฐานข้อมูล!$A$2:$A$11=$B$1,ROW(ฐานข้อมูล!$B$2:$B$11)-ROW(ฐานข้อมูล!$B$2)+1),ROWS($B$3:B3)​)),"")

ไม่ได้ครับสูตรนี้ใส่ไปแล้ววงเล็บไม่ครบครับ

puriwutpokin wrote: Mon Feb 05, 2018 7:27 pm ปรับใหม่ครับ=IFERROR(INDEX(ฐานข้อมูล!B$2:B$11,SMALL(IF(ฐานข้อมูล!$A$2:$A$11=$B$1,ROW(ฐานข้อมูล!$B$2:$B$11)-ROW(ฐานข้อมูล!$B$2)+1),ROWS($B$3:B3)),"")

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