Snasui Excel Forum

ปรับสูตรที่ชีท Summary M เซลล์ F5 เป็นตามด้านล่างครับ

=INDEX(INDEX(Summary!$F$5:$S$38,MATCH(1,IF(LOOKUP(9.99999999999999E+307,$B$5:$B5)=Summary!$B$5:$B$38,IF(LOOKUP(2,1/($E$5:$E5<>""),$E$5:$E5)=Summary!$E$5:$E$38,1)),0),0),SMALL(IF(ISNUMBER(SEARCH(LEFT(F$4,4),Summary!$F$4:$S$4)),COLUMN(Summary!$F$4:$S$4)-COLUMN(Summary!$F$4)+1),MOD((ROWS(F$5:F5)-1),2)+1))

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

สำหรับผลลัพธ์ที่เป็น #N/A เนื่องจากที่ชีท Summary เซลล์ B18:B24 มี Format เป็น Text ให้แก้โดย คลุม B18:B24 เข้าเมนู Data > Text to columns > Finish

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

ต่อไปคือที่มีการเออร์เรอร์ตั้งแต่คนที่ชื่อ คุณวีรวัฒน์ นั้น เนื่องจาก ข้อมูลวันที่ในคอลัมน์ B ชีท Summary ตั้งแต่เซลล์ B18:B24 ข้อมูลจะเป็น Text เลยทำให้สูตรไม่ทำงานเช่นกัน
อย่างไรก็ดีข้อมูลที่คุณบันทึกไว้ เป็นตัวการหลักที่ทำให้สูตรไม่แสดงผล หรือ ผลเออร์เรอร์ ดังนั้น เราต้องแก้ที่ต้นเหตุ วิธีการดังนี้ครับ

ในเมื่อเราอ้างอิงจาก รหัสไม่ได้แล้ว เราก็อ้างอิงจากชื่อคนไปเลย เพราะในรหัสคุณมีการเพิ่มตัวอักษรลงไปในรหัส ทำให้รหัสกลายเป็น Text เช่น "344 (ยกเลิกบิล)" ทำให้ในคอลัมน์ D
มีข้อมูลที่เป็นทั้ง Number และ Text สูตร Lookup999... จึงใช้งานไม่ได้

เราจึงต้องเปลี่ยนใช้ Lookup(Char(255)) แทน โดยอ้างจาก "วันที่" และ "ชื่อ-นามสกุล"
ลองตามนี้ครับ
ที่ชีทSummary Mเซลล์ F5 คีย์
=IF($B5<>"",INDEX(Summary!F$5:F$200,MATCH(1,IF($B5=Summary!$B$5:$B$200,IF($E5=Summary!$E$5:$E$200,1)),0)),INDEX(Summary!M$5:M$200,MATCH(1,IF(LOOKUP(9.99999999999999E+307,$B$5:$B5)=Summary!$B$5:$B$200,IF(IF(LOOKUP(CHAR(255),$E$5:$E5)="",LOOKUP(CHAR(255),$E$4:$E4),LOOKUP(CHAR(255),$E$5:$E5))=Summary!$E$5:$E$200,1)),0)))
กด Ctrl+Shift+Enter คัดลอกไปทางขวา แล้วลงล่างพร้อมกัน

จะเห็นได้ว่า จะมีการเออร์เรอร์ในเซลล์ F31:F44 เหตุผลเพราะว่า ข้อมูลวันที่ในชีท Summary คอลัมน์ B ตั้งแต่เซลล์ B18:B24 มีข้อมูลเป็น Text ทำให้สูตร Lookup999 ไม่ทำงาน
ดังนั้นจึงต้องไปคีย์ข้อมูลวันที่ในชีท Summary คอลัมน์ B ตั้งแต่เซลล์ B18:B24 ใหม่อีกครั้ง ให้ข้อมูลเป็นวันที่ (Number)
วิธีการทดสอบว่ามันเป็นข้อมูลวันที่แล้วหรือไม่ ลองทดสอบคีย์ข้อมูลวันที่ในเซลล์ B2 ก็ให้คีย์ =Count(B2) ในเซลล์ใดๆ หากได้ค่าออกมาเท่ากับ 1 แสดงว่าข้อมูลถูกต้อง ถ้าได้ 0 แสดงว่าข้อมูลวันที่นั้น
ผิดครับ

ใช้ได้เเล้วครับ ขอบคุณอาจารย์ snasui และ คุณ bank9597 ที่ให้ความกระจ่างเเละชัดเจนมากเลยครับ
ต้องเเก้ที่ต้นเหตุจริงๆด้วยครับ

พบปัญหาครับอาจารย์หลังจากใช้งานมาพักหนึ่ง
ในช่อง เซลล์ F164,F165 Copy ลงด้านล่างจนถึง F174,F175 เเล้วเเสดงเเต่ค่า F164,F165 ครับ
สูตรตั้งเเต่ F5 Copy ลงด้านล่างจนถึงช่วงเฉพาะช่วงเซลล์ดังกล่าวที่มีปัญหาครับ
รบกวนด้วยครับ

คุณต้องแจ้งด้วยครับว่า ตอนนี้ใช้สูตรใดอยู่ เช่น ใช้สูตรของอาจารย์ หรือตามของผม เพราะว่ามันไม่ได้ใช้เหมือนกัน

อีกประการคือควรแนบไฟล์มาด้วยเสมอเพื่อสะดวกในการทดสอบ เพราะสูตรนั้นค่อนข้างยากและยาวควรแนบไฟล์มาด้วยเป็นอย่างยิ่งครับ

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

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

เช่น ชื่อ นาย ก. ลงวันที่ 16/6/2012 ซึ่งซ้ำกัน 3 บรรทัด สูตรมันก็จะดึงมาเฉพาะค่าในบรรทัดแรกมาแสดงเท่านั้น

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


ลองตามนี้ครับ ที่ F4 คีย์
=IF($B4<>"",INDEX(Summary!F$5:F$200,MATCH(1,IF($B4=Summary!$B$5:$B$200,IF($D4=Summary!$D$5:$D$200,1)),0)),INDEX(Summary!M$5:M$200,MATCH(1,IF(LOOKUP(9.99999999999999E+307,$B$4:$B4)=Summary!$B$5:$B$200,IF(IF(LOOKUP(CHAR(255),$D$4:$D4)="",LOOKUP(CHAR(255),$D$3:$D3),LOOKUP(CHAR(255),$D$4:$D4))=Summary!$D$5:$D$200,1)),0)))
กด Ctrl+Shift+Enter คัดลอกไปทางขวามือ แล้วลงล่างพร้อมกัน

ปล. รบกวนเรียกชื่อผมด้วยชื่อ bank9597 ดีกว่าครับ เพราะคำว่าอาจารย์ มันไม่เหมาะกับผมเลยครับ ควรจะใช้เรียกอาจารย์ snasui ดีกว่าครับ

ต้องขอบคุณ คุณbank9597 ด้วยครับ เเต่เนื่องจากฟอร์มนี้จะเเสดงทั้ง Text เเละ ตัวเลข ครับ
ตัวอย่างนี้ตัดมาเฉพาะ Text เท่านั้นนะครับ พอจะเเนะนำเพิ่มเติมอีกได้มั้ยครับ

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

อนึ่งถ้าหากผมต้องแก้ไขปัญหานี้ ผมก็แค่เพิ่มคอลัมน์อีก 1 คอลัมน์ เพื่อเรียงลำดับข้อมูล แล้วก็ปรับสูตรเดิม แค่นี้ก็ไม่มีปัญหาอะไรแล้วครับ ไม่ว่ารหัสคุณจะเป็น Text หรือ Number ก็ไม่ใช่ปัญหาอีกต่อไปครับ

ลองแนบสูตรที่ผมเขียนให้ไปแล้วดูว่าได้ผลเป็นอย่างไร ยกไฟล์นั้นมาถามกันต่อครับ

เนื่องจากผมใส่สูตรของอาจารย์ Snasui ลงไปในฟอร์มตัวอย่างนี้เเล้วผลปรากฎว่า สูตรไม่ดึงค่าใดๆมาเลยครับ
รบกวนอาจารย์ด้วยครับ
ขอบคุณครับ

ลองปรับสูตรเป็นตามด้านล่างครับ

1. ที่ K1 คีย์สูตรเพื่อหาว่าจะต้อง List รายการมาทั้งหมดกี่บรรทัด
   =(MATCH(9.99999999999999E+307,Summary!B:B)-ROW(Summary!B4))*2
   Enter > Copy ลงด้านล่าง
2. ที่ G3 เปลี่ยนค่าเดิมเป็นคำว่า Model เนื่องจากใช้สูตรในการหาค่านี้จากหัวบรรทัดในชีท Summary
3. ที่ F4 คีย์สูตรเพื่อ List รายการ
   =IF(ROWS(F$4:F4)>$K$1,"",INDEX(INDEX(Summary!$F$5:$S$200,MATCH(1,IF(LOOKUP(2,1/($D$4:$D4<>""),$D$4:$D4)=Summary!$D$5:$D$200,IF(LOOKUP(2,1/($E$4:$E4<>""),$E$4:$E4)=Summary!$E$5:$E$200,1)),0),0),SMALL(IF(ISNUMBER(SEARCH(LEFT(F$3,4),Summary!$F$4:$S$4)),COLUMN(Summary!$F$4:$S$4)-COLUMN(Summary!$F$4)+1),MOD((ROWS(F$4:F4)-1),2)+1)))
   Ctrl+Shift+Enter > Copy ไปด้านขวาและลงด้านล่าง

เท่าที่ลองใช้งานได้ตามต้องการครับอาจารย์
ขอบคุณมากๆครับ

จากที่ลองสูตรใช้งานได้ตามที่ต้องการมากๆครับ
จะสอบถามอาจารย์เพิมเติมครับ กรณีเราจะเพิ่มช่องต่อจาก SPEC คือ REMARK เข้าไปอีก
จะต้องเพิ่มเติมสูตรอย่างไรได้บ้างครับ รบกวนอาจารย์ด้วยครับ
ขอบคุณมากครับ

ต้องปรับช่วงข้อมูลในสูตรให้เป็นช่วงข้อมูลที่จะใช้จริงครับ โดย

1. ที่ชีท Summary เซลล์ T4:U4 ให้คีย์คำว่า Remark ไม่ใช่คีย์ที่ T3:U3 แล้ว Merge เซลล์เหมือนที่ทำมาครับ
2. ปรับสูตรเดิมที่ F4 เป็น
   =IF(ROWS(F$4:F4)>$K$1,"",INDEX(INDEX(Summary!$F$5:$U$200,MATCH(1,IF(LOOKUP(2,1/($D$4:$D4<>""),$D$4:$D4)=Summary!$D$5:$D$200,IF(LOOKUP(2,1/($E$4:$E4<>""),$E$4:$E4)=Summary!$E$5:$E$200,1)),0),0),SMALL(IF(ISNUMBER(SEARCH(LEFT(F$3,4),Summary!$F$4:$U$4)),COLUMN(Summary!$F$4:$U$4)-COLUMN(Summary!$F$4)+1),MOD((ROWS(F$4:F4)-1),2)+1)))
   Ctrl+Shift+Enter

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

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