อยากแยก ข้อมูลเป็น sheet

[pattarwut]

ลงทะเบียนอบรม(ขับขี่) (การตอบกลับ).xlsx

ได้ใช้ google form ทำการเก็บข้อมูล พอแปลงค่ามาเป็น EXcel แล้วตัวข้อมูล column มันแสดงผลยาวไปทางขวา
โดยเรามีหัวข้อหลักอยู่คอลัมแรก
อยากทราบว่าจะใส่สูตรยังไงเพื่อกรอกข้อมูลไปแสดงอีก sheet
เช่น
หัวเรื่องที่ต้องการแยก คือ A และ B ตัวเลขคือที่มันแสดงผลแต่ละ คอลัม

A 5 6 7
B 1 2 3
A 8 8 9
A 9 5 7

อยากให้ ข้อมูลใน Sheet ถัดไป ดึงแต่ข้อมูลที่เป็นเฉพาะ Roll A เท่านั้น
ขอบคุณครับ

[snasui]

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

[pattarwut]

ตามไฟล์ที่แนบไปครับ ย้ายจากฟอร์มการตอบรับ โดยใช้หัวข้อประเภทที่ต้องการอบรมเป็นหลัก แล้วแยกเป้น sheet ตามไฟล์ครับ ให้ข้อมูลอยู่ในลักษณ์เดียวกับตัวอย่างคับ ขอบคุณมากครับ

[snasui]

ตัวอย่างสูตรครับ

ชีต ขับขี่

1. ที่ A2 คีย์ ผู้ขับขี่ยานพาหนะในเขตการบิน
2. ที่ B2 คีย์
   =IFERROR(INDEX(INDEX('การตอบแบบฟอร์ม 1'!$A$2:$Z$100,0,MATCH(B$1,'การตอบแบบฟอร์ม 1'!$A$1:$Z$1,0)),SMALL(IF('การตอบแบบฟอร์ม 1'!$B$2:$B$100=LOOKUP(CHAR(255),$A$2:$A2),ROW('การตอบแบบฟอร์ม 1'!B$2:B$100)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1),ROWS(B$2:B2))),"")
   Ctrl+Shift+Enter > Copy ไปทางขวาและลงด้านล่าง

ชีต ไม่ขับขี่

1. ที่ A2 คีย์ ผู้ปฏิบัติงานในเขตการบิน (ไม่มีหน้าที่ขับขี่ยานพาพนะ)
2. ที่ B2 คีย์
   =IFERROR(INDEX(INDEX('การตอบแบบฟอร์ม 1'!$L$2:$P$100,0,MATCH(B$1,'การตอบแบบฟอร์ม 1'!$L$1:$P$1,0)),SMALL(IF('การตอบแบบฟอร์ม 1'!$B$2:$B$100=LOOKUP(CHAR(255),$A$2:$A2),ROW('การตอบแบบฟอร์ม 1'!B$2:B$100)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1),ROWS(B$2:B2))),"")
   Ctrl+Shift+Enter > Copy ไปด้านขวาและลงด้านล่าง

ชีต โดนยึดบัตร

1. ที่ A2 คีย์ ผู้โดนยึดบัตรอนุญาต รปภ.บุคคล
2. ที่ B2 คีย์
   =IFERROR(INDEX(INDEX('การตอบแบบฟอร์ม 1'!$Q$2:$Z$100,0,MATCH(B$1,'การตอบแบบฟอร์ม 1'!$Q$1:$Z$1,0)),SMALL(IF('การตอบแบบฟอร์ม 1'!$B$2:$B$100=LOOKUP(CHAR(255),$A$2:$A2),ROW('การตอบแบบฟอร์ม 1'!B$2:B$100)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1),ROWS(B$2:B2))),"")
   Ctrl+Shift+Enter > Copy ไปด้านขวาลและด้านล่าง

อ่าน Ctrl+Shift+Enter โดยละเอียดทุกอักขระได้ที่นี่ viewtopic.php?f=6&t=13228 เพื่อจะได้กดแป้นให้รับสูตรได้อย่างถูกต้อง ไม่เช่นนั้นจะไม่ได้คำตอบครับ

[pattarwut]

ขอบคุณครับ ได้แบบ งงๆ

[pattarwut]

รูป 1.png

รูป 2.png

ขออนุญาตสอบถามครับ จากสูตรที่คุณ snasui เคยให้ไปสามารถใช้งานได้ตามปกติ แต่มีการเพิ่่มช่องในการเก็บข้อมูลขึ้นมา
ตัวอย่างสูตรที่ให้มา copy แล้วลากไม่สามารถใช้งานได้ อยากสอบถามวิธีการแก้ไข ต้องทำอย่างไรครับ (ตามภาพที่ส่งครับ)
ขอความกรุณาด้วยครับ

[snasui]

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

[pattarwut]

ตามไฟล์ที่ส่งไป sheet การตอบแบบฟอร์ม 1 ข้อความลำดับที่ 527-528 ใน column ที่ AB และ AC มันไม่ไปแสดงผลใน sheet ถูกยึดบัตร ครับ

ปล.ในตอนแรก column AB และ AC ยังไม่มี ได้ทำการเพิ่มทีหลัง แล้วทำตามสูตรเดิมมันไม่แสดงผล ครับ
ขอบคุณครับ

[snasui]

ปรับสูตรที่ B2 เป็นด้านล่างครับ

=IFERROR(INDEX(INDEX('การตอบแบบฟอร์ม 1'!$R$2:$AC$690,0,MATCH(B$1,'การตอบแบบฟอร์ม 1'!$R$1:$AC$1,0)),SMALL(IF('การตอบแบบฟอร์ม 1'!$B$2:$B$690=LOOKUP(CHAR(255),$A$2:$A2),ROW('การตอบแบบฟอร์ม 1'!B$2:B$690)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1),ROWS(B$2:B2))),"")

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

[pattarwut]

อยากทราบว่าพอจะอธิบาย โค้ดให้ฟังได้ไหมครับว่ามันมีความหมายว่าอะไรบ้าง เพื่อเป็นความรู้เพิ่มเติมครับ

[snasui]

ตัวอย่างคำอธิบายครับ

จากสูตร =IFERROR(INDEX(INDEX('การตอบแบบฟอร์ม 1'!$R$2:$AC$690,0,MATCH(B$1,'การตอบแบบฟอร์ม 1'!$R$1:$AC$1,0)),SMALL(IF('การตอบแบบฟอร์ม 1'!$B$2:$B$690=LOOKUP(CHAR(255),$A$2:$A2),ROW('การตอบแบบฟอร์ม 1'!B$2:B$690)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1),ROWS(B$2:B2))),"")

แปลว่า หาก INDEX(INDEX('การตอบแบบฟอร์ม 1'!$R$2:$AC$690,0,MATCH(B$1,'การตอบแบบฟอร์ม 1'!$R$1:$AC$1,0)),SMALL(IF('การตอบแบบฟอร์ม 1'!$B$2:$B$690=LOOKUP(CHAR(255),$A$2:$A2),ROW('การตอบแบบฟอร์ม 1'!B$2:B$690)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1),ROWS(B$2:B2))) ได้ผลลัพธ์เป็นค่าผิดพลาด ให้แสดงผลลัพธ์เป็นค่าว่าง

จากสูตร INDEX(INDEX('การตอบแบบฟอร์ม 1'!$R$2:$AC$690,0,MATCH(B$1,'การตอบแบบฟอร์ม 1'!$R$1:$AC$1,0)),SMALL(IF('การตอบแบบฟอร์ม 1'!$B$2:$B$690=LOOKUP(CHAR(255),$A$2:$A2),ROW('การตอบแบบฟอร์ม 1'!B$2:B$690)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1),ROWS(B$2:B2)))

แปลว่า จากช่วง INDEX('การตอบแบบฟอร์ม 1'!$R$2:$AC$690,0,MATCH(B$1,'การตอบแบบฟอร์ม 1'!$R$1:$AC$1,0) ให้นำผลลัพธ์ในลำดับที่ที่เป็นผลลัพธ์ของ SMALL(IF('การตอบแบบฟอร์ม 1'!$B$2:$B$690=LOOKUP(CHAR(255),$A$2:$A2),ROW('การตอบแบบฟอร์ม 1'!B$2:B$690)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1),ROWS(B$2:B2)) มาแสดง ขยายความได้ว่า หากผลลัพธ์ของ Small(...) ได้ค่าเป็น 3 ก็ให้นำลำดับที่ 3 มาแสดง หากได้ผลลัพธ์เป็น 8 ก็ให้นำลำดับที่ 8 มาแสดง

จากสูตร SMALL(IF('การตอบแบบฟอร์ม 1'!$B$2:$B$690=LOOKUP(CHAR(255),$A$2:$A2),ROW('การตอบแบบฟอร์ม 1'!B$2:B$690)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1),ROWS(B$2:B2))

แปลว่า ให้นำค่าที่น้อยที่สุดในลำดับที่ที่เป็นผลลัพธ์ของ ROWS(B$2:B2) จากช่วง IF('การตอบแบบฟอร์ม 1'!$B$2:$B$690=LOOKUP(CHAR(255),$A$2:$A2),ROW('การตอบแบบฟอร์ม 1'!B$2:B$690)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1) มาแสดง ขยายความได้ว่า หาก ROWS(B$2:B2) มีค่าเป็น 1 ให้นำค่าที่น้อยที่สุดในลำดับที่ 1 มาแสดง ถ้าได้ค่าเป็น 100 ให้นำค่าที่น้อยที่สุดในลำดับที่ 100 มาแสดง

จากสูตร IF('การตอบแบบฟอร์ม 1'!$B$2:$B$690=LOOKUP(CHAR(255),$A$2:$A2),ROW('การตอบแบบฟอร์ม 1'!B$2:B$690)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1)

แปลว่า ถ้า 'การตอบแบบฟอร์ม 1'!$B$2:$B$690=LOOKUP(CHAR(255),$A$2:$A2 เป็นจริงแล้วให้แสดงผลลัพธ์ของ ROW('การตอบแบบฟอร์ม 1'!B$2:B$690)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1

จากสูตร LOOKUP(CHAR(255),$A$2:$A2) แปลว่าให้หาอักขระสุดท้ายในช่วง $A$2:$A2 สังเกตว่าเลข 2 ของ A ตัวหลังไม่ได้ Lock ด้วย $ หากคัดลอกลงไปด้านล่างจะเป็น A2:A3, A2:A4 ซึ่งจะเป็นการหาค่าอักขระสุดท้ายที่พบในช่วงที่ขยายไปเรื่อย ๆ

จากสูตร ROW('การตอบแบบฟอร์ม 1'!B$2:B$690)-ROW('การตอบแบบฟอร์ม 1'!B$2)+1 แปลว่าให้แสดงค่าลำดับเริ่มจากเลข 1 สิ้นสุดที่ค่าใด ๆ ที่เป็นจำนวนบรรทัดในช่วง ROW('การตอบแบบฟอร์ม 1'!B$2:B$690) หากจำนวนบรรทัดเป็น 2000 ก็จะแสดงเลขลำดับจาก 1-2000 เช่นนี้เป็นต้นครับ

[pattarwut]

ขอบพระคุณมากครับที่กรุณาสอน และอธิบายให้ฟัง