Snasui Excel Forum

เรียน ทุกท่านที่เคารพครับ มีเรื่องมาปรึกษาครับ สอบถามการใช้สูตรในกรณีที่ข้อมูลเป็นชุดเข้ามา และมีปริมาณบรรทัดที่แตกต่างกัน(และแต่ละบรรทัดเกี่ยวข้องกัน) ซึ่งหากชุดข้อมูลมีเท่าๆกันผมก็สามารถใช้สูตรคงเดิมปกติได้ครับ แต่พอชุดข้อมูลที่เป็นแถบสีเขียว กับแถบสีส้ม มีบรรทัดไม่เท่ากัน ทำให้ผมไม่สามารถเริ่มต้นการใช้สูตรได้ครับ
ชุดข้อมูล สีเขียว คือ ping ได้ มีข้อมูลดังนี้ (และชุดนี้อาจจจะอยู่บรรทัดไหนก็ได้ครับหรืออาจจะไม่ใช่ชุดเริ่มต้นครับ)
Pinging 111.193.199.199 with 32 bytes of data:
Reply from 111.193.199.199: bytes=32 time=51ms TTL=49

Ping statistics for 111.193.199.199:
Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 51ms, Maximum = 51ms, Average = 51ms

ชุดสีส้ม(ในไฟล์) คือ ping ไม่ได้ มี Timeout มีข้อมูลดังนี้ (และชุดนี้อาจจจะอยู่บรรทัดแรก หรือบรรทัดไหนก็ได้ครับหรืออาจจะไม่ใช่ชุดเริ่มต้นครับ)
Pinging 111.193.199.200 with 32 bytes of data:
Request timed out.

Ping statistics for 111.193.199.200:
Packets: Sent = 1, Received = 0, Lost = 1 (100% loss),

รายละเอียด และผลลัพธ์ตามไฟล์แนบครับ (ผลลัพธ์จะเป็นไปตาม Column สีม่วงตามไฟล์แนบครับ) ซึ่งผมยังหาวิธีไม่ได้
Result1(IP) Result2(Loss) Result3(Time)
111.193.199.199 0% loss 51ms
111.193.199.200 100% loss Request timed out.
911.193.119.299 0% loss 58ms
299.103.110.999 0% loss 54ms
399.103.110.39 100% loss Request timed out.
71.13.13.30 wit 0% loss 75ms
191.193.113.42 0% loss 52ms
919.76.180.946 0% loss 56ms
971.103.194.922 0% loss 67ms
771.703.113.70 0% loss 43ms
471.103.113.418 0% loss 53ms

แต่ได้พยายามลองและกรองโดยใช้สูตรเพื่อดึงค่าที่เกี่ยวข้องออกมา (ดังเช่น comlumn สีฟ้าตามไฟล์ครับ)
ขอบคุณมากๆครับ

กรุณา Update Profile โดยระบุ Version ของ Excel ที่ใช้จริงตาม link นี้ด้วยครับ https://snasui.com/viewtopic.php?f=6&p=103177#p103177

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

1. ที่ G2 คีย์
   =IFERROR(IF(FIND("Pinging",A2,1)=1,TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",50)),51,50))),LOOKUP(CHAR(255),G$1:G1))
   Enter > Copy ลงด้านล่าง
2. ที่ K2 คีย์
   =IFERROR(INDEX(G:G,AGGREGATE(15,6,ROW($G$2:$G$84)/(FREQUENCY(MATCH($G$2:$G$84,$G$2:$G$84,0),ROW($G$2:$G$84)-ROW($G$2)+1)>0),ROWS(K$2:K2))),"")
   Enter > Copy ลงด้านล่าง
3. ที่ L2 คีย์
   =IF($K2="","",IFERROR(LOOKUP(2,1/($G$2:$G$84=$K2)/($H$2:$H$84<>""),$H$2:$H$84),"0% loss"))
   Enter > Copy ลงด้านล่าง
4. ที่ M2 คีย์
   =IF(L2="","",IF(L2="(100% loss),","Request timed out.",LOOKUP(2,1/($G$2:$G$84=$K2)/($I$2:$I$84<>""),$I$2:$I$84)))
   Enter > Copy ลงด้านล่าง

ขอบคุณมากๆครับ เดี๋ยวทดสอบดูครับ (ผมใส่ version excel ให้แล้วครับ ในการตั้งค่าครับ)

ไม่ต้องใช้ Colum สีฟ้า

K2
=IFERROR(TRIM(MID(SUBSTITUTE(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$99)/(RIGHT($A$2:$A$99,2)="a:"),ROWS(K$2:K2)))," ",REPT(" ",30)),30,30)),"")

L2
=IF(K2="","",TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$99)/(RIGHT($A$2:$A$99,2)="),"),ROWS(L$2:L2))),"(",REPT(" ",30)),"),",),30)))

M2
=IF(K2="","",TRIM(RIGHT(SUBSTITUTE(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$99)/ISNUMBER(MATCH(RIGHT($A$2:$A$99,2),{"t.","ms"},)),ROWS(K$2:K2))),"=",REPT(" ",30)),30)))

ขอบคุณ คุณBo_ry มากๆครับ เดี๋ยวผมนำไปทดสอบครับ

Bo_ry wrote: Wed Jan 20, 2021 4:14 pm ไม่ต้องใช้ Colum สีฟ้า

K2
=IFERROR(TRIM(MID(SUBSTITUTE(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$99)/(RIGHT($A$2:$A$99,2)="a:"),ROWS(K$2:K2)))," ",REPT(" ",30)),30,30)),"")

L2
=IF(K2="","",TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$99)/(RIGHT($A$2:$A$99,2)="),"),ROWS(L$2:L2))),"(",REPT(" ",30)),"),",),30)))

M2
=IF(K2="","",TRIM(RIGHT(SUBSTITUTE(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$99)/ISNUMBER(MATCH(RIGHT($A$2:$A$99,2),{"t.","ms"},)),ROWS(K$2:K2))),"=",REPT(" ",30)),30)))

เรียน คุณ Bo_ry ผมนำไปทดสอบแล้วครับ สุดยอดมากๆครับ โดยทดสอบจากค่า input ที่เข้ามา ประมาณ 1200 บรรทัด สามารถใช้งาน
ได้ดีและรวดเร็วมากๆครับ (ก่อนหน้านี้ผมติดปัญหา สูตรสีฟ้าเยอะมากๆ จึงส่งผลให้ excel run ค้าง และสูตรค้างครับ)
ขอบคุณทั้ง2 ท่านมากๆครับ คุณ snasui และ คุณ Bo_ry