ขอคำแนะนำสูตรดึงข้อมูลต่าง Sheet เพื่อจัดเรียงบรรทัดใหม่

[wimon]

เรียนอ.คนควนที่เคารพ
อ้างถึงหัวข้อกระทู้: การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียวเมื่อ: 27 มิ.ย. 2011 21:43 ของคุณ anny happy ทำให้ผมคิดอยากจะต่อยอดความรู้ และนำสูตรของอ.มาทดลองทำดู คือสูตร =IF($I2="","",IF(INDIRECT("'"&$I2&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN()),1,"")&COUNTIF($I$2:$I2,$I2)+1)="","",INDIRECT("'"&$I2&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN()),1,"")&COUNTIF($I$2:$I2,$I2)+1)))
กรณี File ที่ผมได้แนบมาหากจะดัดแปลงสูตรให้ดึงข้อมูลจากSheet1 เพื่อมาจัดเรียงบรรทัดใหม่ต่อเนื่องกันและตัดบรรทัดว่าง และข้อความบางบรรทัดออกตามต้องการ รายละเอียดตามเอกสารแนบ ขอความกรุณาอ.ช่วยต่อยอดความรู้ให้ด้วยครับ
ขอแสดงความนับถือ
Wimon

[snasui]

ดึงออกมาให้เรียงกันตามปกติในชีทใดชีทหนึ่งก่อน แล้วค่อยใช้อีกชีทจัดการอีกทอดจะง่ายกว่ากันมากครับ การเขียนสูตรทีเดียวให้ดึงมาเฉพาะเซลล์ที่มีข้อมูลต้องใช้สูตร Array เข้ามาช่วย กรณีข้อมูลจำนวนมากจะทำให้คำนวณนานโดยไม่จำเป็น อีกประการฟังก์ชัน Address และ Indirect เป็น Volatile Function ทั้งคู่ คือถูกสามารถถูกกระทบเปลี่ยนแปลงได้โดยง่าย ควรจะใช้งานเท่าที่จำเป็นเท่านั้นครับ

[wimon]

เรียนอาจารย์คนควน
ดึงออกมาให้เรียงกันตามปกติในชีทใดชีทหนึ่งก่อน แล้วค่อยใช้อีกชีทจัดการอีกทอดจะง่ายกว่ากันมากครับ การเขียนสูตรทีเดียวให้ดึงมาเฉพาะเซลล์ที่มีข้อมูลต้องใช้สูตร Array เข้ามาช่วย

รับทราบครับอ.ควน จากข้อความที่อ.แนะมาข้างต้นเพื่อเป็นการต่อยอดความรู้ผมเรียนถามดังนี้
1.หากมีความต้องการดึงข้อมูลจาก Sheet1 ดึงออกมาให้เรียงกันตามปกติในชีดใดชีทหนึ่งก่อน อ.พอจะแนะนำวิธีดึงตามปกติเพื่อให้เรียงตามSheet ที่ต้องการ ช่วยแนะนำวิธีทำของอาจารย์เพื่อประกอบความรู้ด้วยครับ และมีกี่วิธี หรือมี แหล่ง link ใดที่จะศึกษาเพิ่มเติมบ้าง กรุณาแนะนำด้วย
2.หากดึงออกตามปกติที่อาจารย์แนะนำแล้ว ขั้นตอนอีก Sheet อ.จะใช้ผูกสูตรอะไรอีกทอด ครับ
3.ผมอยากได้รับความรู้เป็นอย่างมากและSheet 1 เพราะไม่ค่อยให้เจอบ่อยนัก หากจะขอความกรุณาอาจารย์ช่วยเขียนสูตรทีเดียวให้ดึงมาเฉพาะเซลล์ที่มีข้อมูลต้องใช้สูตร Array เข้ามาช่วย ขอความกรุณาอาจารย์ด้วยครับอยากรู้มากๆ
ขอแสดงความนับถือ
wimon

[snasui]

คิดว่าน่าจะเข้าใจไปคนละทางครับ

สิ่งที่คุณ wimon ต้องการเป็นแบบใดระหว่าง 2 แบบด้านล่าง (เนื่องจากไปอ้างอิง Link การนำข้อมูลมาต่อกันจากหลาย ๆ ชีท แต่ที่ถามมาล่าสุด เป็นการดึงมาจาก Sheet1 เท่านั้น)

1. ดึงข้อมูลหลายชีทมาต่อกันโดยนำเฉพาะบรรทัดที่มีข้อมูลมาเท่านั้น
2. ต้องการเขียนที่ชีทเดียวไม่ต้องมีชีทสำหรับต่อข้อมูลก่อน

หรือ

1. ดึงข้อมูลจากชีทใด ๆ ที่มีบรรทัดว่าง โดยดึงมาเฉพาะเซลล์ที่มีข้อมูล

[wimon]

เรียนอ.คนควน
ต้องการดึงข้อมูลจาก Sheet1 ออกมา โดยนำไปไว้อีก Sheet (เหมือนกับตัวอย่างใน Sheetชื่อ แบบที่ต้องการ) ครับ ตาม File แนบ
ขอแสดงความนับถือ

Wimon

หมายเหตุ เหตุที่ต้องอ้างอิง หัวข้อกระทู้: การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียวเมื่อ: 27 มิ.ย. 2011 21:43 ของคุณ anny happy เพราะเหตุว่ากระทู้ดังกล่าวใกล้เคียง คล้ายๆ กับ File ที่ผมแนบไปครับ และได้ทดลองนำสูตรที่อ.ตอบของกระทู้คุณ anny happy ลองทำดูแต่ก็ไม่สำเร็จครับ

[snasui]

ลองตามไฟล์แนบครับ

1. ที่ แบบที่ต้องการดึงมาจากSheet1 ซลล์ A4 คีย์

=COUNTIF(Sheet1!B5:B65536,"<9.99999999999999e307")

Enter

2. ที่ แบบที่ต้องการดึงมาจากSheet1 ซลล์ B6 คีย์

=IF(ROWS(B$6:B6)>$A$4,"",INDEX(Sheet1!B$6:B$100,SMALL(IF(ISNUMBER(Sheet1!$B$6:$B$100),ROW(Sheet1!$B$6:$B$100)-ROW(Sheet1!$B$6)+1),ROWS(B$6:B6))))

Ctrl+Shift+Enter > Copy ไปด้านขวาและลงด้านล่าง

[wimon]

เรียนอ.คนควน

สูตรที่อาจารย์ให้มาเป็นความรู้อย่างมากหลังจากการแกะสูตรโดยการกด F9 ดู ผมขออนุญาตอาจารย์กรุณาช่วยให้คำแนะนำเพิ่มเติม กรณีหากต้องการให้ Cell A2:K(N) ของSheetชื่อแบบที่ต้องการดึงมาจากSheet1 เขียนเป็นสูตรดึงข้อมูลจากShee1 โดยตรงทั้งหมดโดยไม่ต้องพิมพ์ manual รายละเอียดผมได้อธิบายไว้เพิ่มเติมตาม File แนบครับ

ขอแสดงความนับถือ
Wimon

[snasui]

สำหรับหัวคอลัมน์ที่ไม่ได้มีความหลากหลาย ผมไม่เห็นความจำเป็นว่าต้องเขียนสูตรดึงมาครับ ข้อมูลที่ดึงมานี้ควรเป็น Database ก่อนเพื่อที่จะนำไปใช้เป็นรายงานหรือทำอย่างอื่นต่อไป หัว Field จึงควรมีแค่ 1 บรรทัด จึงเห็นว่าไม่มีความจำเป็นต้องเขียนสูตรครับ นอกจากนี้บรรทัดที่ 5 ก็ไม่ควรเป็นบรรทัดว่างด้วยครับ

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

[wimon]

เรียนอ.คนควน
ขอบคุณอ.มากๆครับสำหรับคำแนะนำ
-สำหรับไม่แสดงค่าผมใช้แบบวิธีBasic โดยการ Conditional ในการจัด Format โดยไม่ได้แก้ไขสูตรครับ
-สำหรับเรื่องหัว Columns ผมเห็นด้วยกับคำแนะนำของอ.ทุกประการ ครับเพราะการทำงานของสูตรควรเน้นหลักง่ายๆไว้ก่อนข้อมูลในการทำงานคำนวนจะได้ไม่ช้าตามที่อ.เคยแนะนำเสมอ แต่สำหรับ Case เป็นด้วยความอยากรู้ อยากศึกษาสูตร เหมือนกับทำเรื่องง่ายๆให้เป็นเรื่องยาก จึงขอความกรุณาอ.ช่วยแนะนำอีกสักครั้งครับ ขอเป็นสูตรอ.ช่วยกรุณาด้วยครับ

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

[snasui]

ลองตามนี้ครับ

ที่ชีท แบบที่ต้องการดึงมาจากSheet1 เซลล์ A2 คีย์

=INDEX(Sheet1!A:A,MATCH(1,INDEX(ISTEXT(Sheet1!A:A)+0,0),0))

Enter > Copy ไปใช้กับเซลล์อื่น ๆ ที่ต้องการแสดงหัวคอลัมน์

กรณีมีค่าผิดพลาดสามารถใช้ IF เข้าไปดักได้ครับ ซึ่งสูตรก็จะยาวกว่าเดิม การหาค่าแบบนี้ทำได้หลายสูตร และสูตรนี้ได้ปรับให้ไม่ต้องกดแป้น Ctrl+Shift+Enter

[wimon]

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