Page 1 of 1

ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Mon Apr 16, 2018 4:58 pm
by Xengsue
สวัสดี อาจารย์ และ ท่านผู้รู้ทุกท่าน ครับ
คือผมมีเรื่องมารบกวนให้ช่วยหน่อยครับ
คือหัวหน้าให้ผมทำ Munthly Machinces Fault Report
โดยให้จัดขัอมูลให้รวมกันเป็นกลุ่มเรียงกันครับ
แต่ผมทำยังไงก็ไม่ได้ดั่งไฟล์แนบข้างล่างครับ
รบกวนช่วยหน่อยว่าจะต้องใช้สูตรยังไงเข้าไปครับ

ขอบคุณ ครับ

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Mon Apr 16, 2018 5:01 pm
by Xengsue
จากรูปข้อมูลคือผมต้องการให้ผลออกมาแบบดั่งรูปข้างล่างนี้ครับ

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Mon Apr 16, 2018 5:56 pm
by snasui
:D ช่วยคีย์คำตอบที่ต้องการลงมาในไฟล์ตัวอย่างแล้วอธิบายมาอย่างละเอียดว่านำมาแสดงด้วยเงื่อนไขใดจะได้สะดวกในการทำความเข้าใจ

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

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Mon Apr 16, 2018 11:53 pm
by Xengsue
snasui wrote:
Mon Apr 16, 2018 5:56 pm
:D ช่วยคีย์คำตอบที่ต้องการลงมาในไฟล์ตัวอย่างแล้วอธิบายมาอย่างละเอียดว่านำมาแสดงด้วยเงื่อนไขใดจะได้สะดวกในการทำความเข้าใจ

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

ขอโทษที่ไม่รู้จะอธิบายยังไงให้อาจารย์เข้าใจครับ
คือสูตรที่ผมทำในไฟล์มันไม่ถูกตามต้องการครับ
แล้วผมอยากได้ดั่งรูปข้างล่างนี้ครับ

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Tue Apr 17, 2018 5:11 am
by Xengsue
EX.xlsx
จากข้อมูลที่ได้ป้อนเข้าใน sheet "Fault" ผมอยากเอามาจัดเรียงแสดงใน sheet "Fault report" โดยมีเงื่อนไขดั่งนี้ ครับ

1. ในเซลล์ (c4:c65) มีเงื่อนไขคือ
  • ถ้ารหัสเครื่องจักจาก sheet "Fault" Cell (b4:b65) เป็นรหัสที่ช้ำกันเช่น M07FA07 และ มัน Fault code cell (h4:h65) ช้ำกันเช่น ME หยุดทางด้านเครื่องกล เหมือนกันก็ให้รวมเป็นตัวเดียวกันลงใน sheet "Fault report" cell (c4)
- รหัสเครื่องจัก cell (c4) = M07FA07
  • แต่กรณีถ้ารหัสเครื่องจักช้ำกันเช่น M07FA07 แต่ว่ามัน Fault code ต่างกันเช่น ME หยุดทางด้านเครื่องกล และ EE หยุดทางด้านไฟฟ้า ก็ให้แสดงเป็นตัวถัดไปเช่น
- รหัสเครื่องจัก cell (c4) = M07FA07
- รหัสเครื่องจัก cell (c5) = M07FA07
  • แต่เราจะจัดเรียงกันให้มันเป็นกลุ่มกันตาม code ใน sheet "code" cell (c3:c19) ฉะนั้นจากข้อมูลใน sheet "Fault" จึ่งเอามาจัดเรียงแสดงใน sheet "Fault report" เซลล์ (c4:c65)ได้ดั่งนี้
- รหัสเครื่องจัก cell (c4) = M07FA07
- รหัสเครื่องจัก cell (c5) = M13RM01
- รหัสเครื่องจัก cell (c6) = M07RK01
- รหัสเครื่องจัก cell (c7) = M13WF02AC
- รหัสเครื่องจัก cell (c8) = M07FA07
- รหัสเครื่องจัก cell (c9) = 12Mn03
2. ในเซลล์ (d4:d65) มีเงื่อนไขคือ
  • รหัสเครื่องจักจาก sheet "Fault report" Cell (c4:c65) มัน Fault code ทางด้านไหนจากข้อมูลที่มีใน sheet "fault" cell (h4:h65) ก็ให้มันแสดงเป็น Code คัวนั้นเช่น
- รหัสเครื่องจัก cell (c4) = M07FA07 fault code ME หยุดทางด้านเครื่องกล
- รหัสเครื่องจัก cell (c5) = M13RM01 fault code EE หยุดทางด้านไฟฟ้า
- รหัสเครื่องจัก cell (c6) = M07RK01 fault code EE หยุดทางด้านไฟฟ้า
- รหัสเครื่องจัก cell (c7) = M13WF02AC fault code EE หยุดทางด้านไฟฟ้า
- รหัสเครื่องจัก cell (c8) = M07FA07 fault code EE หยุดทางด้านไฟฟ้า
- รหัสเครื่องจัก cell (c9) = 12Mn03 fault code PR หยุดทางด้านผลิต
3. ในเซลล์ (e4:e65) มีเงื่อนไขคือ
  • ความถี่ของเครื่องจักที่มีรหัส cell (c4:c65) และ Fault code cell (d4:d65) ตามลำดับว่ามีกี่ครั้งตามข้อมูลที่มีใน sheet "fault" เช่น
- รหัสเครื่องจัก cell (c4) = M07FA07 fault code ME หยุดทางด้านเครื่องกล มีึความถี่เท่า 2 ครั้ง
- รหัสเครื่องจัก cell (c5) = M13RM01 fault code EE หยุดทางด้านไฟฟ้า มีึความถี่เท่า 1 ครั้ง
- รหัสเครื่องจัก cell (c6) = M07RK01 fault code EE หยุดทางด้านไฟฟ้า มีึความถี่เท่า 1 ครั้ง
- รหัสเครื่องจัก cell (c7) = M13WF02AC fault code EE หยุดทางด้านไฟฟ้า มีึความถี่เท่า 1 ครั้ง
- รหัสเครื่องจัก cell (c8) = M07FA07 fault code EE หยุดทางด้านไฟฟ้า มีึความถี่เท่า 1 ครั้ง
- รหัสเครื่องจัก cell (c9) = 12Mn03 fault code PR หยุดทางด้านผลิต มีึความถี่เท่า 1 ครั้ง
4. ในเซลล์ (f4:f65) มีเงื่อนไขคือ
  • เครื่องจักที่มีรหัสเครื่องจัก cell (c4:c65) และ Fault code cell (d4:d65) มีความถี่กี่ครั้งจากข้อมูลที่มีใน sheet "fault" เช่นตัวอย่างใน cell (f4) เท่ากับบดความของสาเหตุที่แท้จริงของเครื่องจักที่มีรหัสเครื่อง cell (c4) และ Fault code cell (d4) ที่ Fault ไป 2 ครั้งใน sheet "Fault" Cell (j4) กับ Cell (j8) รวมกันแล้วได้ Bearing ของพัดลมมีเสียงดังและสั่นมาก, ใบพัดลมบิดงอ ฉะนั้นผลที่ได้ในเซลล์ (f4:f65) จึ่งได้ดั่งนี้
-cell (f4) = Bearing ของพัดลมมีเสียงดังและสั่นมาก, ใบพัดลมบิดงอ
- cell (f5) = ช่างไฟฟ้าสะลับเฟสไฟกับด้าน
- cell (f6) = ชุดเครื่องชาร์จแบตเตอรี่สำรองของชุดควบคุมทั้งระบบไหม้
- cell (f7) = ยังไม่ทราบสาเหตุที่แท้จริง
- cell (f8) = Over Voltage
- cell (f9) = ขาดการตรวจเช็ค

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Tue Apr 17, 2018 6:06 am
by snasui
:D ตัวอย่างสูตรครับ

ที่ชีต Fault
  1. เซลล์ R3 คีย์คำว่า "สาเหตุ" เพื่อใช้เป็นหัวคอลัมน์
  2. เซลล์ R4 คีย์สูตร
    =IFERROR(LOOKUP(2,1/((B4=B$3:B3)*(H4=H$3:H3)),R$3:R3)&", "&J4,J4)
    Enter > Copy ลงด้านล่างจนสุดข้อมูลที่มี
ชีต Report Fault
  1. ที่ C4 คีย์
    =IFERROR(INDEX(Fault!B$4:B$10,SMALL(IF(FREQUENCY(MATCH(Fault!$B$4:$B$10&Fault!$H$4:$H$10,Fault!$B$4:$B$10&Fault!$H$4:$H$10,0),ROW(Fault!$B$4:$B$10)-ROW(Fault!$B$4)+1),ROW(Fault!$B$4:$B$10)-ROW(Fault!$B$4)+1),ROWS(C$4:C4))),"")
    Ctrl+Shift+Enter > Copy ลงด้านล่าง
  2. ที่ D4 คีย์
    =IFERROR(INDEX(Fault!H$4:$H10,SMALL(IF(FREQUENCY(MATCH(Fault!$B$4:$B$10&Fault!$H$4:$H$10,Fault!$B$4:$B$10&Fault!$H$4:$H$10,0),ROW(Fault!$B$4:$B$10)-ROW(Fault!$B$4)+1),ROW(Fault!$B$4:$B$10)-ROW(Fault!$B$4)+1),ROWS(D$4:D4))),"")
    Ctrl+Shift+Enter > Copy ลงด้านล่าง
  3. ที่ E4 คีย์
    =IF(D4="","",COUNTIFS(Fault!$B$4:$B$10,C4,Fault!$H$4:$H$10,D4))
    Enter > Copy ลงด้านล่าง
  4. ที่ F4 คีย์
    =IF(D4="","",LOOKUP(2,1/((C4=Fault!$B$4:$B$10)*(D4=Fault!$H$4:$H$10)),Fault!$R$4:$R$10))
    Enter > Copy ลงด้านล่าง

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Tue Apr 17, 2018 3:33 pm
by Xengsue
=IFERROR(LOOKUP(2,1/((B4=B$3:B3)*(H4=H$3:H3)),R$3:R3)&", "&J4,J4)
ผมเห็นสูตรตัวอย่างตัวนี้ของอาจารย์แล้วผมแปลกใจมากเลยเพราะว่าผมพึงเคยเห็นเป็นครั้งแรก ไม่คิดเลยว่ามันจะสามารถทำแบบนี้ได้ด้วย :thup: สูตรสุดยอดมากครับอาจารย์ แต่ผมยังไม่เข้าใจเลยว่าทำไมต้องให้
Lookup_value = 2 และ
Lookup_Vector = 1/((B4=B$3:B3)*(H4=H$3:H3)) ด้วยครับแล้วความหมายของมันคืออะไรกัน?


และก็ขอบคุณ อาจารย์ มากครับที่ให้สูตรมาแต่ลองดูแล้วยังติดอีกข้อครับที่อยากขอเพิ่ม คือ
ชีต Report Fault
ที่ C4 คีย์
=IFERROR(INDEX(Fault!B$4:B$10,SMALL(IF(FREQUENCY(MATCH(Fault!$B$4:$B$10&Fault!$H$4:$H$10,Fault!$B$4:$B$10&Fault!$H$4:$H$10,0),ROW(Fault!$B$4:$B$10)-ROW(Fault!$B$4)+1),ROW(Fault!$B$4:$B$10)-ROW(Fault!$B$4)+1),ROWS(C$4:C4))),"")
Ctrl+Shift+Enter > Copy ลงด้านล่าง

ที่ D4 คีย์
=IFERROR(INDEX(Fault!H$4:$H10,SMALL(IF(FREQUENCY(MATCH(Fault!$B$4:$B$10&Fault!$H$4:$H$10,Fault!$B$4:$B$10&Fault!$H$4:$H$10,0),ROW(Fault!$B$4:$B$10)-ROW(Fault!$B$4)+1),ROW(Fault!$B$4:$B$10)-ROW(Fault!$B$4)+1),ROWS(D$4:D4))),"")
Ctrl+Shift+Enter > Copy ลงด้านล่าง
มันทำได้แต่มันไม่ได้จัดเรียงข้อมูลให้เป็นกลุ่มให้ผมครับ คือผมต้องการให้มันจัดเรียงเป็นแบบนี้ครับ

ที่ชีต Fault
  1. เซลล์ B11 และ H11 ผมได้เพิ่มรหัส 23ss02 และ fault code เป็น ME หยุดทางด้านเครื่องกล
    ฉะนั้นข้อมูลเลยกลายเป็น
- cell (b4) = M07FA07 cell (h4) = ME หยุดทางด้านเครื่องกล
- cell (b5) = M13RM01 cell (h5) = EE หยุดทางด้านไฟฟ้า
- cell (b6) = M07RK01 cell (h6) = EE หยุดทางด้านไฟฟ้า
- cell (b7) = M13WF02AC cell (h7) = EE หยุดทางด้านไฟฟ้า
- cell (b8) = M07FA07 cell (h8) = ME หยุดทางด้านเครื่องกล
- cell (b9) = M07FA07 cell (h9) = EE หยุดทางด้านไฟฟ้า
- cell (b10) = 12Mn03 cell (h10) = PR หยุดทางด้านผลิต
- cell (b11) = 23ss02 cell (h11) = ME หยุดทางด้านเครื่องกล
ที่ชีต Report Fault
จากสูตรที่อาจารย์ให้มาผมได้ทำการคีย์ ลงไปแล้วมันได้มาแบบนี้
- cell (c4) = M07FA07 cell (d4) = ME หยุดทางด้านเครื่องกล
- cell (c5) = M13RM01 cell (d5) = EE หยุดทางด้านไฟฟ้า
- cell (c6) = M07RK01 cell (d6) = EE หยุดทางด้านไฟฟ้า
- cell (c7) = M13WF02AC cell (d7) = EE หยุดทางด้านไฟฟ้า
- cell (c8) = M07FA07 cell (d8) = EE หยุดทางด้านไฟฟ้า
- cell (c9) = 12Mn03 cell (d9) = PR หยุดทางด้านผลิต
- cell (c10) = 23ss02 cell (d10) = ME หยุดทางด้านเครื่องกล
แต่ต้องการให้มันจัดเรียงกันเป็นกลุ่มตามลำดับ fault code ของชีต code เซลล์ (C3:C19) ไปด้วยเลยครับ
  1. กลุ่ม 1. ME หยุดทางด้านเครื่องกล (ถ้ากลุ่มนี้ไม่มีให้ผ่านเป็นกลุ่มถัดไป)
    .
  2. กลุ่ม 2. EE หยุดทางด้านไฟฟ้า (ถ้ากลุ่มนี้ไม่มีให้ผ่านเป็นกลุ่มถัดไป)
    .
  3. กลุ่ม 3. CE หยุดทางด้านโยธา (ถ้ากลุ่มนี้ไม่มีให้ผ่านเป็นกลุ่มถัดไป)
    .
  4. กลุ่ม 4. PR หยุดทางด้านผลิต (ถ้ากลุ่มนี้ไม่มีให้ผ่านเป็นกลุ่มถัดไป)
    .
ฉะนั้นที่ที่ชีต Report Fault จะได้ประมารนี้ครับ
แบบการจัดเรียงเป็นกลุ่มที่ต้องการ
- cell (c4) = M07FA07 cell (d4) = ME หยุดทางด้านเครื่องกล
- cell (c5) = 23ss02 cell (d5) = ME หยุดทางด้านเครื่องกล
- cell (c6) = M13RM01 cell (d6) = EE หยุดทางด้านไฟฟ้า
- cell (c7) = M07RK01 cell (d7) = EE หยุดทางด้านไฟฟ้า
- cell (c8) = M13WF02AC cell (d8) = EE หยุดทางด้านไฟฟ้า
- cell (c9) = M07FA07 cell (d9) = EE หยุดทางด้านไฟฟ้า
- cell (c10) = 12Mn03 cell (d10) = PR หยุดทางด้านผลิต

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Tue Apr 17, 2018 8:03 pm
by snasui
:D การเรียงลักษณะที่กล่าวมานั้นไม่ได้เรียงตามตัวอักษร เป็นการกำหนดการเรียงขึ้นมาเอง การเรียงด้วยสูตรจะมีข้อจำกัดอย่างหนึ่งคือจะต้องเรียงจากน้อยไปหามากหรือจากมากไปหาน้อยเท่านั้น

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

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Tue Apr 17, 2018 10:13 pm
by Xengsue
snasui wrote:
Tue Apr 17, 2018 8:03 pm
:D การเรียงลักษณะที่กล่าวมานั้นไม่ได้เรียงตามตัวอักษร เป็นการกำหนดการเรียงขึ้นมาเอง การเรียงด้วยสูตรจะมีข้อจำกัดอย่างหนึ่งคือจะต้องเรียงจากน้อยไปหามากหรือจากมากไปหาน้อยเท่านั้น

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

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Tue Apr 17, 2018 10:21 pm
by snasui
:D หาวิธีเรียงข้อมูลต้นทางให้กลุ่มเดียวกันอยู่ด้วยกันครับ

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Tue Apr 17, 2018 10:31 pm
by Xengsue
snasui wrote:
Tue Apr 17, 2018 10:21 pm
:D หาวิธีเรียงข้อมูลต้นทางให้กลุ่มเดียวกันอยู่ด้วยกันครับ
ขอบคุณ มากครับ เดียวจะลองทำตามที่แนะนำให้ก่อน
ถ้าติดปัญหาจะมาขอคำแนะนำเพิ่มครับ

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Wed Apr 18, 2018 6:30 am
by snasui
Xengsue wrote:
Tue Apr 17, 2018 3:33 pm
Lookup_value = 2 และ
Lookup_Vector = 1/((B4=B$3:B3)*(H4=H$3:H3)) ด้วยครับแล้วความหมายของมันคืออะไรกัน?
:o ขออภัยที่ลืมตอบคำถามนี้ครับ

สามารถศึกษา Lookup ลักษณะนี้ได้จาก Link นี้ครับ wordpress/last-cell-that-not-blank/

Re: ดึงข้อมูลจากอีก sheet มาจัดเรียงให้เป็นกลุ่ม

Posted: Thu Apr 19, 2018 4:08 am
by Xengsue
snasui wrote:
Wed Apr 18, 2018 6:30 am
Xengsue wrote:
Tue Apr 17, 2018 3:33 pm
Lookup_value = 2 และ
Lookup_Vector = 1/((B4=B$3:B3)*(H4=H$3:H3)) ด้วยครับแล้วความหมายของมันคืออะไรกัน?
:o ขออภัยที่ลืมตอบคำถามนี้ครับ

สามารถศึกษา Lookup ลักษณะนี้ได้จาก Link นี้ครับ wordpress/last-cell-that-not-blank/
ขอบคุณมากสำหรับความรู้ และ การแนะนำครับ
ตอนนี้ผมก็ทำการแก้ปัญหาที่ยกมาถามนี้ได้แล้วครับ