Excel Forum

สวัสดี อาจารย์ และ ท่านผู้รู้ทุกท่าน ครับ
คือผมมีเรื่องมารบกวนให้ช่วยหน่อยครับ
คือหัวหน้าให้ผมทำ Munthly Machinces Fault Report
โดยให้จัดขัอมูลให้รวมกันเป็นกลุ่มเรียงกันครับ
แต่ผมทำยังไงก็ไม่ได้ดั่งไฟล์แนบข้างล่างครับ
รบกวนช่วยหน่อยว่าจะต้องใช้สูตรยังไงเข้าไปครับ

ขอบคุณ ครับ

จากรูปข้อมูลคือผมต้องการให้ผลออกมาแบบดั่งรูปข้างล่างนี้ครับ

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

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

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

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

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

จากข้อมูลที่ได้ป้อนเข้าใน 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) = ขาดการตรวจเช็ค

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

ที่ชีต 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 ลงด้านล่าง

=IFERROR(LOOKUP(2,1/((B4=B$3:B3)*(H4=H$3:H3)),R$3:R3)&", "&J4,J4)

ผมเห็นสูตรตัวอย่างตัวนี้ของอาจารย์แล้วผมแปลกใจมากเลยเพราะว่าผมพึงเคยเห็นเป็นครั้งแรก ไม่คิดเลยว่ามันจะสามารถทำแบบนี้ได้ด้วย สูตรสุดยอดมากครับอาจารย์ แต่ผมยังไม่เข้าใจเลยว่าทำไมต้องให้
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 หยุดทางด้านผลิต

การเรียงลักษณะที่กล่าวมานั้นไม่ได้เรียงตามตัวอักษร เป็นการกำหนดการเรียงขึ้นมาเอง การเรียงด้วยสูตรจะมีข้อจำกัดอย่างหนึ่งคือจะต้องเรียงจากน้อยไปหามากหรือจากมากไปหาน้อยเท่านั้น

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

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

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

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

หาวิธีเรียงข้อมูลต้นทางให้กลุ่มเดียวกันอยู่ด้วยกันครับ

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

ขอบคุณ มากครับ เดียวจะลองทำตามที่แนะนำให้ก่อน
ถ้าติดปัญหาจะมาขอคำแนะนำเพิ่มครับ

Xengsue wrote: ↑Tue Apr 17, 2018 3:33 pm Lookup_value = 2 และ
Lookup_Vector = 1/((B4=B$3:B3)*(H4=H$3:H3)) ด้วยครับแล้วความหมายของมันคืออะไรกัน?

ขออภัยที่ลืมตอบคำถามนี้ครับ

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

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)) ด้วยครับแล้วความหมายของมันคืออะไรกัน?

ขออภัยที่ลืมตอบคำถามนี้ครับ

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

ขอบคุณมากสำหรับความรู้ และ การแนะนำครับ
ตอนนี้ผมก็ทำการแก้ปัญหาที่ยกมาถามนี้ได้แล้วครับ