Snasui Excel Forum

สอบถามวิธีการดึงข้อมูลใน Excel ที่ขึ้นบรรทัดใหม่ ให้แยกอยู่ในรูปแบบ Row ครับ

จากที่วงๆ อยู่ ทำได้มั้ยครับ

คิดว่าพอมีทางทำได้ ลองแนบไฟล์ Excel ที่ทำเป็นตัวอย่างมาด้วยเพื่อน ๆ จะได้ช่วยหาวิธีให้ได้ครับ

ตัวอย่างประมาณนี้ครับ จริงข้อมูลเยอะมากครับ ทำมือไม่ไหว ขอบคุณครับ

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

1. ที่ D3 คีย์สูตรเพื่อเป็นคอลัมน์ช่วย
   =SUMPRODUCT(LEN($C$3:C3)-LEN(SUBSTITUTE(C$3:C3,CHAR(10),"")))-(LEN(C3)-LEN(SUBSTITUTE(C3,CHAR(10),"")))+1
   Enter > Copy ลงด้านล่าง
2. ที่ C9 คีย์สูตรเพื่อแสดงรายการอาหาร
   =TRIM(LEFT(SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",TRUE,SUBSTITUTE($C$3:$C$4,CHAR(10),",")),",","</s><s>"),CHAR(9),"")&"</s></t>","//s"),"฿",REPT(" ",100)),100))
   Enter > Copy ลงด้านล่าง
3. ที่ A9 คีย์สูตรเพื่อแสดงวันที่
   =LOOKUP(ROWS(A$9:A9),$D$3:$D$4,A$3:A$4)
   Enter > Copy ไป B9 แล้ว Copy ลงด้านล่าง

กรุณาระบุ Version ของ Excel ที่ใช้อยู่จริง ดูวิธีการที่นี่ viewtopic.php?p=103177#p103177 เพื่อเพื่อนสมาชิกจะได้ตอบให้ตรงตาม Version ครับ

snasui wrote: Tue May 24, 2022 1:04 pm ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ D3 คีย์สูตรเพื่อเป็นคอลัมน์ช่วย
   =SUMPRODUCT(LEN($C$3:C3)-LEN(SUBSTITUTE(C$3:C3,CHAR(10),"")))-(LEN(C3)-LEN(SUBSTITUTE(C3,CHAR(10),"")))+1
   Enter > Copy ลงด้านล่าง
2. ที่ C9 คีย์สูตรเพื่อแสดงรายการอาหาร
   =TRIM(LEFT(SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",TRUE,SUBSTITUTE($C$3:$C$4,CHAR(10),",")),",","</s><s>"),CHAR(9),"")&"</s></t>","//s"),"฿",REPT(" ",100)),100))
   Enter > Copy ลงด้านล่าง
3. ที่ A9 คีย์สูตรเพื่อแสดงวันที่
   =LOOKUP(ROWS(A$9:A9),$D$3:$D$4,A$3:A$4)
   Enter > Copy ไป B9 แล้ว Copy ลงด้านล่าง

กรุณาระบุ Version ของ Excel ที่ใช้อยู่จริง ดูวิธีการที่นี่ viewtopic.php?p=103177#p103177 เพื่อเพื่อนสมาชิกจะได้ตอบให้ตรงตาม Version ครับ

ผมลองนำไปปรับใช้ ยังคงติดการใช้งานอยู่ครับ เนื่องจากพอใส่เพิ่มบรรทัดไป มันจะขึ้น Error และไม่ข้อมูลไม่ตรง ครับ

ไฟล์ตัวอย่างครับ
https://docs.google.com/spreadsheets/d/ ... ue&sd=true

กรุณาแนบเป็นไฟล์ Excel มาที่ฟอรัมนี้โดยตรงตามกฎการใช้บอร์ดข้อ 4 ด้านบน ครับ

snasui wrote: Tue May 24, 2022 3:29 pm กรุณาแนบเป็นไฟล์ Excel มาที่ฟอรัมนี้โดยตรงตามกฎการใช้บอร์ดข้อ 4 ด้านบน ครับ

ขออภัยด้วยครับ

จากสูตรผมลองใช้แล้ว Copy สูตรลงด้านล่างมี Error 2 จุดครับ
1.พอ Copy ไป Cell ประมาณ 160 แล้วจะ Error ครับ
2.ช่วงของ Vlookup มันไม่ตรงครับ

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

1. ที่ D2 คีย์สูตรเพื่อเป็นคอลัมน์ช่วย
   =SUMPRODUCT(LEN($C$2:C2)-LEN(SUBSTITUTE(C$2:C2,CHAR(10),""))+1)-(LEN(C2)-LEN(SUBSTITUTE(C2,CHAR(10),"")))
   Enter > Copy ลงด้านล่าง
2. ที่ D1 คีย์สูตรเพื่อเป็นตัวนับ
   =SUMPRODUCT(LEN(C2:C182)-LEN(SUBSTITUTE(C2:C182,CHAR(10),""))+1)
   Enter
3. ที่ A185 คีย์สูตรเพื่อแสดงวัน เวลา
   =IF(ROWS(A$185:A185)>$D$1,"",LOOKUP(ROWS(B$185:B185),$D$2:$D$182,A$2:A$182))
   Enter > Copy ไป B185 > Copy ลงด้านล่าง
4. ที่ C185 คีย์สูตรเพื่อแสดงรายการ
   =IF(B185="","",TRIM(LEFT(SUBSTITUTE(INDEX(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(INDEX($C$2:$C$182,MATCH(B185,$B$2:$B$182,0)),CHAR(10),"</s><s>"),CHAR(9),"")&"</s></t>","//s"),COUNTIFS(B$185:B185,B185)),"฿",REPT(" ",100)),100)))
   Enter > Copy ลงด้านล่าง

snasui wrote: Tue May 24, 2022 4:57 pm ตัวอย่างการปรับสูตรตามด้านล่างครับ

1. ที่ D2 คีย์สูตรเพื่อเป็นคอลัมน์ช่วย
   =SUMPRODUCT(LEN($C$2:C2)-LEN(SUBSTITUTE(C$2:C2,CHAR(10),""))+1)-(LEN(C2)-LEN(SUBSTITUTE(C2,CHAR(10),"")))
   Enter > Copy ลงด้านล่าง
2. ที่ D1 คีย์สูตรเพื่อเป็นตัวนับ
   =SUMPRODUCT(LEN(C2:C182)-LEN(SUBSTITUTE(C2:C182,CHAR(10),""))+1)
   Enter
3. ที่ A185 คีย์สูตรเพื่อแสดงวัน เวลา
   =IF(ROWS(A$185:A185)>$D$1,"",LOOKUP(ROWS(B$185:B185),$D$2:$D$182,A$2:A$182))
   Enter > Copy ไป B185 > Copy ลงด้านล่าง
4. ที่ C185 คีย์สูตรเพื่อแสดงรายการ
   =IF(B185="","",TRIM(LEFT(SUBSTITUTE(INDEX(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(INDEX($C$2:$C$182,MATCH(B185,$B$2:$B$182,0)),CHAR(10),"</s><s>"),CHAR(9),"")&"</s></t>","//s"),COUNTIFS(B$185:B185,B185)),"฿",REPT(" ",100)),100)))
   Enter > Copy ลงด้านล่าง

ได้แล้วครับ ขอบคุณมากครับ

ผมอยากเข้าใจหลักการครับ จะรบกวนอธิบายได้มั้ยครับ ขอบคุณครับ

จากไฟล์ที่แนบมาต้องการแยกข้อความที่มีการคั่นข้อความด้วยอักขระลำดับที่ 10 หรือ Char(10) ซึ่งอักขระนี้ใช้เพื่อขึ้นบรรทัดใหม่ในเซลล์เดียวกัน

โดยมีหลักการทำงานดังนี้

ลำดับแรก ตรวจสอบว่าในแต่ละเซลล์มีกี่ข้อความ โดยใช้ฟังก์ชัน =SUMPRODUCT(LEN($C$2:C2)-LEN(SUBSTITUTE(C$2:C2,CHAR(10),""))+1) ซึ่งหมายถึงให้นำอักขระทั้งหมดในเซลล์นั้นเป็นตัวตั้ง หักออกด้วยจำนวนอักขระหลังจากเปลี่ยน Char(10) ในเซลล์นั้นให้เป็นค่าว่างแล้ว ผลลัพธ์คือจำนวนข้อความที่ต้องการในเซลล์นั้น ๆ

เมื่อได้จำนวนข้อความในแต่ละเซลล์แล้วก็ประยุกต์เพิ่มด้วยการผสานสูตรที่ให้ผลลัพธ์เป็นจำนวนข้อความสะสมไปในแต่ละบรรทัดเป็น =SUMPRODUCT(LEN($C$2:C2)-LEN(SUBSTITUTE(C$2:C2,CHAR(10),""))+1)-(LEN(C2)-LEN(SUBSTITUTE(C2,CHAR(10),"")))

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

ลำดับถัดมาใช้สูตร =SUMPRODUCT(LEN(C2:C182)-LEN(SUBSTITUTE(C2:C182,CHAR(10),""))+1) เพื่อนับว่ามีจำนวนข้อความทั้งหมดกี่ข้อความ เพื่อจะใช้แสดงรายการทั้งหมดที่เกี่ยวข้องออกมาเป็นรายบรรทัดโดยจำนวนบรรทัดทั้งหมดต้องไม่เกินค่านี้ หากเกินจะให้แสดงผลลัพธ์เป็นค่าว่าง

เมื่อได้ค่าข้างต้นแล้วสามารถเขียนสูตรเพื่อแสดงรายการที่เกี่ยวข้องในคอลัมน์ A และ B ด้วยฟังก์ชั่น =IF(ROWS(A$185:A185)>$D$1,"",LOOKUP(ROWS(B$185:B185),$D$2:$D$182,A$2:A$182)) ซึ่งจะมีการตรวจสอบก่อนว่าจำนวนบรรทัดทั้งหมดเกินค่าใน D1 แล้วหรือไม่ หากเกินจะให้แสดงค่าว่าง

ส่วนคอลัมน์ C จะเป็นการแยกข้อความที่คั่นด้วย Char(10) ออกมาเป็นแต่ละข้อความด้วยฟังก์ชัน Filterxml โดยข้อความที่จะนำมาใช้นั้นต้องเป็นค่าในคอลัมน์ C ที่เป็นลำดับเดียวกันกับค่าในคอลัมน์ B เมื่อแยกออกมาแล้วจะแสดงค่าทีละลำดับโดยใช้จำนวนสะสมของค่าในคอลัมน์ B เป็นตัวกำหนด

ในการแสดงผลได้เปลี่ยน Char(9) ให้เป็นค่าว่างและตัดเอามาใช้เฉพาะค่าด้านหน้าก่อนอักขระ ฿ เท่านั้นครับ