Snasui Excel Forum

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

เซลล์ D1:O1 คีย์หัวคอลัมน์เป็นดังนี้ เงื่อนไขที่ 1, เงื่อนไขที่ 1.1, เงื่อนไขที่ 1.2, เงือนไข 2, เงื่อนไขที่ 2.1, เงื่อนไขที่ 2.2, เงื่อนไขที่ 2.3, เงื่อนไขที่ 2.4, เงื่อนไขที่ 2.5, เงื่อนไขที่ 3, เงื่อนไขที่ 4, เงื่อนไขที่ 5 ตามลำดับ

เซลล์ D16:O16 คีย์ข้อความแสดงความหมายของสูตรที่ใช้ด้านบน เขียนเพื่อความเข้าใจ สามารถย้ายไปยังตำแหน่งที่เหมาะสม ดังนี้ Cond 1, ค่าว่าง, - มากกว่า 1, Cond 2, =นาย, >นาง, <>น.ส., พบเต็ม, พบย่อ, วรรคก่อนชื่อ, ห่าง<> 2 วรรค, ไม่ใช่ - ตามลำดับ

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

1. ที่ D2 คีย์
   =OR(E2,F2)
   Enter > Copy ลงด้านล่าง
2. ที่ E2 คีย์
   =TRIM(A2)=""
   Enter > Copy ลงด้านล่าง
3. ที่ F2 คีย์
   =LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))>1
   Enter > Copy ลงด้านล่าง
4. ที่ G2 คีย์
   =OR(AND(H2,I2,J2),AND(I2,J2,K2),AND(I2,J2,L2))
   Enter > Copy ลงด้านล่าง
5. ที่ H2 คีย์
   =LEFT(TRIM(A2),3)="นาย"
   Enter > Copy ลงด้านล่าง
6. ที่ I2 คีย์
   =LEFT(TRIM(A2),3)<>"นาง"
   Enter > Copy ลงด้านล่าง
7. ที่ J2 คีย์
   =LEFT(TRIM(A2),4)<>"น.ส."
   Enter > Copy ลงด้านล่าง
8. ที่ K2 คีย์
   =SUMPRODUCT(COUNTIF(A2,คำนำหน้าชื่อเต็ม!$A$1:$A$78&"*"))>0
   Enter > Copy ลงด้านล่าง
9. ที่ L2 คีย์
   =SUMPRODUCT(COUNTIF(A2,'ยศ-ย่อ'!$A$1:$A$64&"*"))>0
   Enter > Copy ลงด้านล่าง
10. ที่ M2 คีย์
    =IFERROR(MID(TRIM(A2),FIND(" ",TRIM(A2))+1,255)<>TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",255)),255)),FALSE)
    Enter > Copy ลงด้านล่าง
11. ที่ N2 คีย์
    =IFERROR(MID(SUBSTITUTE(A2," ","@"),FIND("@",SUBSTITUTE(A2," ","@")),2)<>"@@",TRUE)
    Enter > Copy ลงด้านล่าง
12. ที่ O2 คีย์
    =A2<>"-"
    Enter > Copy ลงด้านล่าง
13. ที่ B2 คีย์
    =IF(OR(D2,G2,M2,N2,O2),FALSE,TRUE)
    Enter > Copy ลงด้านล่าง

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

จากภาพยังไม่ได้ทำข้อ 13 ซึ่งเป็นการคีย์สูตรที่ B2 ที่ผมแนะนำไป กรุณาทำให้ครบถ้วน คอลัมน์ B นี้คือคำตอบที่จะนำไปใช้ต่อ ส่วนคอลัมน์ D เป็นต้นไปเป็นแค่คอลัมน์ช่วยเท่านั้น

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

ที่ A15 ข้อมูลอยู่ในรูปแบบที่ถูกต้อง แต่ F15 แสดง FALSE
ที่ต้องการคือ F15 ต้องแสดง TRUE ครับ

เงื่อนไขที่จะแสดงค่าเป็น TRUE ได้คือ
1. คำนำหน้าชื่อบิดาต้องเป็นคำว่า "นาย" หรือ "ชื่อยศ" ทีี่เป็นคำเต็ม เท่านั้นครับ
2. คำนำหน้าชื่อบิดากับชื่อต้องพิมพ์ติดกันครับ
3. ระยะห่างจากชื่อและนามสกุลต้องมีแค่ 2 เคาะวรรคเท่านั้นครับ
4. หากไม่เป็นไปตามเงื่อนไขในข้อ 1 ถึง 3 (ทั้ง 3 ข้อ) ให้แสดงค่าเป็น FALSE ครับ

F15 คืออะไรและทำไมต้องเป็น True อธิบายมาด้วยครับ

การพิจารณาคำตอบดูเฉพาะที่ F15 อย่างเดียวไม่ได้ เพราะต้องดูรวมกันทุกเงื่อนไข

การเขียนเงื่อนไข ควรเขียนให้สอดคล้องกับสิ่งที่จะค้นหา ผลลัพธ์ของเงื่อนไขควรจะให้กลายเป็น True เสมอ อย่าไปเทียบกับ False เพราะจะทำให้สับสน ตัวอย่างเงื่อนไข เช่น

มี - เกินกว่า 1 ตัว ให้เป็น True
ค่าวรรค ไม่เท่ากับ 2 ตัว ให้เป็น True
ไม่มีคำนำหน้านาม ให้เป็น True
...
ฯลฯ

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

ต้องกราบขออภัยจริง ๆ ครับ
ไม่ใช่ F15 ครับ
คือ B15 ครับ
คำอธิบายอยู่ที่ C15 ครับ

เงื่อนไขที่จะแสดงค่าเป็น TRUE ได้คือ
1. คำนำหน้าชื่อบิดาต้องเป็นคำว่า "นาย" หรือ "ชื่อยศ" ทีี่เป็นคำเต็ม เท่านั้นครับ
2. คำนำหน้าชื่อบิดากับชื่อต้องพิมพ์ติดกันครับ
3. ระยะห่างจากชื่อและนามสกุลต้องมีแค่ 2 เคาะวรรคเท่านั้นครับ
4. คอลัมน์ A เป็นเครื่องหมาย -

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

ผมปรับสูตรมาให้ใหม่โดยสิ่งที่ต้องทำก่อนคือ ให้นำคำนำหน้าตามมาต่อกันในชีตเดียว ตัด นาง, น.ส., นางสาว ทิ้งไป เพื่อสูตรจะได้ไม่ซับซ้อน

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

=IFERROR(IF(TRIM(A2)="-",TRUE,IF(AND(OR(LEFT(TRIM(A2),3)="นาย",SUMPRODUCT(COUNTIF(A2,คำนำหน้าชื่อเต็ม!$A$1:$A$142&"*"))>0),MID(SUBSTITUTE(A2," ","@"),FIND("@",SUBSTITUTE(A2," ","@")),2)="@@",MID(TRIM(A2),FIND(" ",TRIM(A2))+1,255)=TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",255)),255))),TRUE,FALSE)),FALSE)

สูตรยังไม่เป็นไปตามเงื่อนไขใน 3 แถบเหลือง ตามภาพและไฟล์แนบครับ

ตัดคำนำหน้าย่อทิ้งให้หมดแล้วปรับช่วงเซลล์ในสูตรใหม่ให้ตรงกับข้อมูลที่มีจริงในชีตคำนำหน้านามครับ

ที่ B2 คีย์
=IFERROR(IF(TRIM(A2)="-",TRUE,IF(AND(OR(LEFT(TRIM(A2),3)="นาย",SUMPRODUCT(COUNTIF(A2,คำนำหน้าชื่อเต็ม!$A$1:$A$76&"*"))>0),MID(SUBSTITUTE(A2," ","@"),FIND("@",SUBSTITUTE(A2," ","@")),2)="@@",MID(TRIM(A2),FIND(" ",TRIM(A2))+1,255)=TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",255)),255))),TRUE,FALSE)),FALSE)
Enter > Copy ลงด้านล่าง

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

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

ลองแบบนี้ดูครับ
D2 =IF(IFERROR(FIND("-",A2,FIND("-",A2)+1),0)>=1,FALSE,IF(OR(A2="",IFERROR(FIND("-",A2),0)=1),TRUE,IF(IFERROR(FIND("นาย ",A2),0)=1,FALSE,IF(AND(IFERROR(FIND("นาย",A2),0)=1,IFERROR(FIND(" ",A2,FIND(" ",A2)+1),0)-IFERROR(FIND(" ",A2),0)=1),TRUE,IF(AND(IFERROR(FIND("นาย",A2),0)=1,IFERROR(FIND(" ",A2),0)>=1),FALSE,IF(IFERROR(FIND("นาย",A2),0)=1,TRUE,IF(SUMPRODUCT(COUNTIFS(A2,'ยศ-ย่อ'!$A$1:$A$64&"*"))>0,FALSE,IF(SUMPRODUCT(COUNTIFS(A2,คำนำหน้าชื่อเต็ม!$A$1:$A$78&" "&"*"))>0,FALSE,IF(SUMPRODUCT(COUNTIFS(A2,คำนำหน้าชื่อเต็ม!$A$1:$A$78&"*"))>0,TRUE,FALSE))))))))) Enter+Copy ลงด้านล่าง

ajsudkedt wrote:ขอความอนุเคราะห์เพิ่มครับ
กรณีมีชื่อต้น ชื่อกลาง และชื่อท้าย
ต้องการกำหนดให้ห่างกันอย่างละ 2 เคาะวรรค
ต้องกำหนดสูตรอย่างไรให้แสดงค่าเป็น TRUE ครับ

ลองแนบไฟล์มาใหม่ ให้ครอบคลุมเงื่อนไขทั้งหมดที่มี และ ระบุผลลัพท์ที่ต้องการทั้งหมด
เพื่อนๆจะได้เข้าถึงปัญหาทั้งหมดได้ง่ายครับ

parakorn wrote:

ajsudkedt wrote:ขอความอนุเคราะห์เพิ่มครับ
กรณีมีชื่อต้น ชื่อกลาง และชื่อท้าย
ต้องการกำหนดให้ห่างกันอย่างละ 2 เคาะวรรค
ต้องกำหนดสูตรอย่างไรให้แสดงค่าเป็น TRUE ครับ

ลองแนบไฟล์มาใหม่ ให้ครอบคลุมเงื่อนไขทั้งหมดที่มี และ ระบุผลลัพท์ที่ต้องการทั้งหมด
เพื่อนๆจะได้เข้าถึงปัญหาทั้งหมดได้ง่ายครับ

นอกจากด้านบนนี้ ไฟล์ที่แนบมายังไม่ใช่ไฟล์ล่าสุดตามที่ได้ถามตอบกันไปครับ

เป็นไฟล์ที่ใส่สูตรอาจารย์ล่าสุดแล้วครับ

=IFERROR(IF(TRIM(A2)="-",TRUE,IF(AND(OR(LEFT(TRIM(A2),3)="นาย",SUMPRODUCT(COUNTIF(A2,คำนำหน้าชื่อเต็ม!$A$1:$A$142&"*"))>0),MID(SUBSTITUTE(A2," ","@"),FIND("@",SUBSTITUTE(A2," ","@")),2)="@@",MID(TRIM(A2),FIND(" ",TRIM(A2))+1,255)=TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",255)),255))),TRUE,FALSE)),FALSE)

ชีตคำนำหน้านามเป็นข้อมูลล่าสุดแล้วหรือไม่ ได้ลบคำหน้านามแบบย่อออกแล้วหรือไม่ครับ

ผมเปิดพบว่ายังมีคำนำหน้านามแบบย่อ สูตรก็ยังคลุมไปถึงตำแหน่งนั้นครับ

สูตรที่จะคงไว้ตามที่ผมตอบไปล่าสุดคือลัมน์ B ที่เหลือลบทิ้งให้หมดครับ

ต้องขออภัยด้วยครับ
ปรับไฟล์แนบใหม่แล้วครับ
เพิ่มเงื่อนไขดังนี้ครับ
คำนำหน้าชื่อ (เป็นคำเต็ม) ต้องห่างจากชื่อชื่อกลาง 2 เคาะวรรค
ชื่อกลางกับชื่อท้ายที่เป็น ณ ต้องห่างกัน 1 เคาะวรรค
ปรับสูุตรอย่างไรให้แสดง TRUE ครับ
ตอนนี้มันแสดง FALSE อยู่ครับ

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

1. นำคำว่า "นาย" ไปเพิ่มไว้ที่ชีต คำนำหน้าชื่อเต็ม
2. ที่ B2 คีย์
   =IFERROR(IF(TRIM(A2)="-",TRUE,IF(AND(LEFT(A2,FIND(" ",A2))<>LOOKUP(2,1/(ISNUMBER(SEARCH(คำนำหน้าชื่อเต็ม!$A$1:$A$78&"*",A2))),คำนำหน้าชื่อเต็ม!$A$1:$A$78)&" ",MOD(LEN(A2)-LEN(SUBSTITUTE(A2," ","")),2)=0),TRUE,FALSE)),FALSE)
   Enter > Copy ลงด้านล่าง

กรุณาอ่านโพสต์ #38 อีกครั้ง สูตรในคอลัมน์ D เป็นต้นไปไม่จำเป็นต้องเก็บไว้ให้ลบทิ้งไปเพราะจะเป็นภาระการคำนวณครับ