การค้นหาบางอักขระในข้อความ

[xxporxx]

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

[snasui]

เนื่องจากไม่ถูกต้องอยู่ 2 เรื่องใหญ่ ๆ ครับ

1. จากสูตรในไฟล์แนบ
   =IFERROR(INDEX($A$2:$A$11,SMALL(IF(MMULT(ISNUMBER(SEARCH(MID($C$2,TRANSPOSE(ROW(INDIRECT(“1:”&LEN($C$2)))),1),$A$2:$A$11))+0,LEN(MID($C$2,ROW(INDIRECT(“1:”&LEN($C$2))),1)))=LEN($C$2),ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(D$2:D2))),"")
   อักขระทีผมทำสีแดงไว้นั้นไม่ได้เป็นเครื่องหมาย "" เหมือนที่ผมระบายตัวอักษรเป็นสีน้ำเงิน ซึ่งต้องแก้ใหม่ อันนี้เนื่องจาก Web แสดงเป็นเครื่องหมายเช่นนั้น หากพบเหตุการณ์นี้จะต้องคีย์แก้ไขเองเสมอ
2. การกดแป้นให้รับสูตร ต้องกด 3 แป้นคือ Ctrl+Shift+Enter แทนการกดแป้น Enter ธรรมดา หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาครอบสูตร ปีกกานี้จะคีย์เข้าไปเองไม่ได้ หากยังไม่เห็นเครื่องหมายปีกกาครอบสูตร แสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง

[xxporxx]

ขอบคุณครับ ผมขอลองทำก่อนนะครับ ได้ผลยังไงแล้วผมจะมาแจ้งให้ทราบครับ

[xxporxx]

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

[snasui]

จะให้ List รายการได้จะต้อง Copy สูตรลงด้านล่างครับ

[xxporxx]

ขอบคุณครับ คุณ Snasui ทำได้ตามความต้องการแล้วครับ ขอบคุณมากครับ

[jinora]

รบกวนด้วยครับ
1. ผมลองแล้วอยากสอบถามเพิ่มเติมครับ ว่าถ้าเราอยากค้นหาข้อความที่เรียงตามตัวอักษรที่เราต้องการทราบจะทำได้มั้ย
เช่น หาคำว่า อง ในโปรแกรมคุณSnasui ก็จะหาทุกข้อความและแสดงผลทุกบรรทัดที่มีอักษร อ และ ง แต่ผมอยากเฉพาะที่มีคำว่า อง เป็นส่วนประกอบ พอจะทำได้มั้ยครับ
2. และก้เพิ่มเติม ผลสงสัย excel ครับ คือลองพยายามแกะสูตรและทำความเข้าใจก็งงไปไม่ถูกเลยครับ เช่น =INDIRECT("1:"&LEN($C$2)) ผลลัพธ์ คือ รายการ อยากถามว่าทำไมได้ผลลัพธ์นี้ แล้วเอาไปทำไรต่อ (แนวคิด) ครับ ขอบคุณครับ

[DhitiBank]

หากต้องการหาทั้ง keyword ที่คีย์ ลองปรับสูตรที่ D2 เป็น

Code: Select all

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISNUMBER(SEARCH($C$2,$A$2:$A$10)),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(D$2:D2))),"")

จากนั้นกด Ctrl+Shift ค้าง แล้วกด Enter --> คัดลอกลงล่างครับ

สำหรับสูตรของอาจารย์ตรง Indirect ก็ต้องดูสูตร Mid ทั้งสูตรครับ
MID($C$2,ROW(INDIRECT("1:"&LEN($C$2))),1)
- กรณีนี้คือต้องการหาคำทั้งหมดในช่วงข้อมูล ที่พบอักขระตัวใดก็ตามใน keyword (C2) อาจารย์เลยแยกอักขระแต่ละตัวใน keyword ออกมาด้วยสูตร Mid
- รูปแบบคร่าวๆ คือ Mid(คำที่ต้องการตัด,ตำแหน่งที่ต้องการตัด,จำนวนอักขระที่จะตัด)
- เมื่อต้องการแยกแต่ละตัวเพื่อเอามาตรวจ "จำนวนอักขระที่จะตัด" ต้องเป็น 1 (ตัดทีละตัว) และ "ตำแหน่งที่ต้องการตัด" ก็ต้องเริ่มตั้งแต่ อักขระตัวที่ 1 จนถึงตัวสุดท้าย สูตร Row(Indirect("1:"&Len(C2))) จะทำหน้าที่นี้ครับ

สมมติว่า อักขระใน C2 มี 2 ตัว จะได้
=Row(Indirect("1:"&2)) สูตร Len เอาไว้นับอักขระ
=Row(Indirect("1:2"))
={1;2} การเอาตำแหน่งมาใช้กับสูตร Row, Column และอื่นๆ อีกบางสูตร หากเราคีย์เองเช่น "1:"&2 หรือ "A1:A"&4 หรืออื่นๆ จะเอามาใส่ตรงๆ ไม่ได้ครับ ต้องผ่าน Indirect

ก็จะได้สูตรที่ตัดอักขระแต่ละตัวออกมาจาก C2 ครับ แล้วก็เอามาจัดการหาต่อไปในสูตร Mmult ซึ่งยากมากครับ หากจะทำความเข้าใจควรดูเรื่องการคูณเมทริกซ์ (คณิตศาสตร์) มาก่อนครับ ค่อยๆ ดูไปครับ

[jinora]

ขอบคุณมากครับอาจารย์DhitiBank
ถ้ายังไงผมขอรับไปทดลองและศึกษาเพิ่มเติมก่อนนะครับ

[DhitiBank]

ยินดีครับ แต่ผมก็เป็นเพื่อนสมาชิกเหมือนกันครับ แอบมาเรียนเป็นระยะๆ

[DhitiBank]

เห็น PM มาถามเรื่องสูตร ขออธิบายตรงนี้นะครับ เผื่อมีเพื่อนๆ ท่านอื่นสนใจจะได้อ่านด้วยกัน

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISNUMBER(SEARCH($C$2,$A$2:$A$10)),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(D$2:D2))),"")

ผมขออธิบายเป็นแนวคิดนะครับ ดูจากข้างในออกมาก่อนเพราะผมค่อยๆ คิดจากข้างในออกมา

1. IF([b]ISNUMBER(SEARCH($C$2,$A$2:$A$10))[/b],[i]ROW($A$2:$A$10)-ROW($A$2)+1[/i])
--สูตร IF ด้านในจะมี 2 ส่วน ส่วนเงื่อนไข (คือตัวหนา) และส่วนผลลัพธ์เมื่อเงื่อนไขเป็นจริง (ตัวเอียง) [แต่ส่วนผลลัพธ์เมื่อเงื่อนไขเป็นเท็จได้ละเอาไว้ หากเงื่อนไขเป็นเท็จจะให้ค่า False]
--ส่วนเงื่อนไข: ก็ใช้ Search เพื่อมองหาว่าช่วงข้อมูล A2:A10 มี keyword ที่ใส่ไปหรือไม่ ถ้ามีก็จะคืนค่าเป็นตัวแหน่งที่พบ keyword ในคำนั้นๆ ถ้าไม่มีก็จะคืนค่า error สามารถตรวจสอบได้พร้อมๆ กันหลายเซลล์ครับ โดยระบุเป็นช่วงข้อมูลลงไปดังในสูตร จากนั้นก็เอาสูตร Isnumber มาตรวจอีกว่าผลลัพธ์ไหนเป็นตัวเลข หากเป็นตัวเลขก็ True หากเป็นค่า error ก็ให้ False
--ส่วนผลลัพธ์เมื่อเงื่อนไขเป็นจริง: ใช้สูตร Row(A2:A10)-Row(A2)+1 เป็นการรันลำดับครับ ซึ่งหากคลุมช่วงนี้ทั้งหมดแล้วกด F9 ดูก็จะได้ว่ามันคือ {1;2;3;4;5;6;7;8;9} ที่เป็นเช่นนี้เพื่อให้แสดงตำแหน่งในช่วงข้อมูลที่พบ keyword เมื่อส่วนเงื่อนไขเป็นจริง
--สมมติว่าในช่วง A2:A10 มีข้อความใน A5 และ A7 เท่านั้นที่พบ keyword ก็จะได้ว่า

=IF({False;False;False;True;False;True;False;False;False},{1;2;3;4;5;6;7;8;9})
={False;False;False;4;False;6;Fasle;False;False}

นี่คือผลลัพธ์ที่ได้จากสูตร If ซึ่งจะเอาไปใช้ต่อในสูตร Small

2. SMALL(.....,ROWS(D$2:D2))
--สูตรนี้จะเลือกตัวเลขที่น้อยที่สุดเป็นลำดับที่ Row(D$2:D2) มาใช้ครับ ซึ่ง Row(D$2:D2) มีค่าเท่ากับ 1 อะไรที่น้อยที่สุดเป็นลำดับ 1 คือเลข 4 (จากตัวอย่างบน) (ลองนึกดูว่าหากคัดลอกสูตรลง Row(D$2:D2) จะเป็น Row(D$2:D3) ซึ่งมีค่าเท่ากับ 2 สูตร small ก็จะดึงค่าที่น้อยที่สุดเป็นอันดับ 2) ผลลัพธ์จากสูตร Small ก็จะไปใช้ในสูตร Index เพื่อบอกว่าให้เอาข้อมูลตำแหน่งไหนที่ Index อ้างอิงอยู่มาแสดงครับ

3. ส่วน Iferror ก็ใช้เพื่อดักค่า error เพราะจากสูตร Small ในข้อ 2 เมื่อคัดลอกสูตรลงมาเรื่อยๆ ถึงบรรทัดที่ 3 สูตรจะมองหาค่าที่น้อยที่สุดเป็นลำดับ 3 แต่ว่าไม่มีแล้ว ก็จะเกิดค่า error ครับ สูตร Iferror ก็จะบังคับให้แสดงค่าว่างแทน

[jinora]

ขอบคุณมากครับ ท่าน DhitiBank
จากสูตรด้านบน งั้นถ้าผมเปลี่ยนนิดหน่อยแบบนี้แทนก็ได้ใช่มั้ยครับ
=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISNUMBER(SEARCH($C$2,$A$2:$A$10)),ROW($A$1:$A$9)),1)),"")
***ในกรณีที่ต้องหาค้นหาแค่เจอคำที่เหมือนคำแรกแล้วพอ

[XDoll]

เห็น PM มาถามเรื่องสูตร ขออธิบายตรงนี้นะครับ เผื่อมีเพื่อนๆ ท่านอื่นสนใจจะได้อ่านด้วยกัน

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

[DhitiBank]

จากสูตรด้านบน งั้นถ้าผมเปลี่ยนนิดหน่อยแบบนี้แทนก็ได้ใช่มั้ยครับ
=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISNUMBER(SEARCH($C$2,$A$2:$A$10)),ROW($A$1:$A$9)),1)),"")
***ในกรณีที่ต้องหาค้นหาแค่เจอคำที่เหมือนคำแรกแล้วพอ

ขอชมที่พยายามทำความเข้าใจและมีความคิดสร้างสรรค์ครับ

--สำหรับสูตร small สามารถใช้แบบนั้นก็ได้ หากต้องการหาแค่คำแรกที่พบ keyword หรือ ใช้สูตร Min แทน small ไปเลยก็ได้ครับ

--ส่วนการรันลำดับ ผมก็เคยสงสัยเหมือนกันว่าทำไมต้อง row($a$2:$a$10)-row($a$2)+1 [คือสงสัยว่าทำไมต้องลบด้วยแถวที่น้อยที่สุดในช่วงอ้างอิง แล้วเอา 1 มาบวกกลับเข้าไป] ทำไมไม่แทนด้วยอะไรที่รวบรัดกว่าเช่น row($a$1:$a$9) อาจารย์คนควนเคยแนะผมว่า ลองแทรกแถวระหว่างแถวที่ 1 และ 2 แล้วสังเกตผลว่าสูตรยังให้ผลลัพธ์ถูกต้องหรือไม่ครับ

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

[DhitiBank]

อ่านแล้วเข้าใจง่ายครับ

ขอบคุณครับ ผมก็เรียบเรียงอยู่นานกว่าจะกด submit ได้ พยายามเอาเรื่องที่รู้ออกมาแบ่งกันครับ