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

สำหรับการค้นหาบางอักขระในข้อความ โดยไม่คำนึงว่าว่าอักขระนั้นจะมีจำนวนเท่าใดและอยู่ส่วนใดของข้อความ เราสามารถใช้สูตรมาช่วยค้นหาได้ครับ

ยกตัวอย่างเช่น มีรายชื่อตัวยาอยู่ในช่วงเซลล์ A2:A10 ต้องการคีย์ค้นหาในเซลล์ C2 แค่บางอักขระแล้วให้แสดงรายชื่อตัวยาออกมาในเซลล์ D2 เป็นต้นไป

เราสามารถเขียนสุตรได้ตามด้านล่างครับ

1. ที่ C2 คีย์คำค้นตามต้องการ
2. คีย์สูตรที่ D2 ตามด้านล่าง
   =IFERROR(INDEX($A$2:$A$10,SMALL(IF(MMULT(ISNUMBER(SEARCH(MID($C$2,TRANSPOSE(ROW(INDIRECT("1:"&LEN($C$2)))),1),$A$2:$A$10))+0,LEN(MID($C$2,ROW(INDIRECT("1:"&LEN($C$2))),1)))=LEN($C$2),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(D$2:D2))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

ภาพตัวอย่างการค้นหาบางอักขระในข้อความ

การแสดงรายการซ้ำตามจำนวนที่กำหนด

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

กรณีแบบยากเราจะใช้สูตร Array ในการแสดงรายการซ้ำ ๆ ตามจำนวนที่กำหนด ยกตัวอย่างเช่นตามภาพด้านล่าง ข้อมูลอยู่ที่ A2:A7 จำนวนที่ระบุว่าซ้ำกันกี่ครั้งอยู่ที่ B2:B7 โดยจะนำรายการมาแสดงตั้งแต่ F2 เป็นต้นไป

ภาพแสดงการแสดงรายการซ้ำตามจำนวนที่กำหนดแบบยาก

วิธีการ

1. ที่เซลล์ B8 คีย์สูตรเพื่อหายอดรวมตัวเลขทั้งหมด เพื่อประโยชน์ในการกำหนดจำนวนบรรทัดสูงสุดที่จะแสดงรายการ
   =SUM(B2:B7)
   Enter
2. ที่เซลล์ B9 หาค่าสูงสุดของช่วง B2:B7
   =MAX(B2:B7)
   Enter
3. ที่เซลล์ F2 คีย์สูตรเพื่อแสดงรายการ
   =IF(ROWS($F$2:F2)>$B$8,"",INDEX($A$2:$A$7,SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&$B$9)))<=$B$2:$B$7,ROW($B$2:$B$7)-ROW($B$2)+1),ROWS($F$2:F2))))
   Ctrl+Shift+Enter > Copy ลงด้านล่างตามต้องการ สังเกตการกดแป้นจะต้องกด Ctrl+Shift ค้างไว้ก่อนแล้วตามด้วย Enter หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะคีย์เข้าไปเองไม่ได้ครับ การแก้ไขปรับปรุงสูตรจะต้องกด Ctrl+Shift+Enter ทุกครั้ง

กรณีแบบง่ายจะใช้สูตร Lookup และเพิ่ม C2:C7 มาช่วย โดยจะนำข้อมูลมาแสดงตั้งแต่ G2 เป็นต้นไปตามภาพด้านล่างครับ

ภาพแสดงการแสดงรายการซ้ำตามจำนวนที่กำหนดแบบง่าย

วิธีการ

1. ที่เซลล์ B8 คีย์สูตรเพื่อหายอดรวมตัวเลขทั้งหมด เพื่อประโยชน์ในการกำหนดจำนวนบรรทัดสูงสุดที่จะแสดงรายการ
   =SUM(B2:B7)
   Enter
2. ที่เซลล์ C2 คีย์สูตรเพื่อประโยชน์ในการ Lookup ข้อมูล
   =SUM(B$2:B2)-B2+1
   Enter > Copy ลงด้านล่าง
3. ที่เซลล์ G2 คีย์สูตรเพื่อแสดงรายการ
   =IF(ROWS($G$2:G2)>$B$8,"",LOOKUP(ROWS($G$2:G2),$C$2:$C$7,$A$2:$A$7))
   Enter > Copy ลงด้านล่างตามต้องการ

การ Paste Link แบบ Transpose

การคัดลอกแล้ววางแบบสลับแกน (Transpose) สำหรับข้อมูลที่เป็นการเชื่อมโยง (Link) มาจากพื้นที่อื่น จะทำให้การเชื่อมโยงนั้นแสดงผลไม่ถูกต้องตามข้อมูลเดิม หากจำเป็นจะต้องทำการวางแบบสลับแกน สามารถใช้การประยุกต์เข้ามาช่วยได้ โดยมีขั้นตอนดังนี้ครับ

1. ทำการเปลี่ยนเครื่องหมาย = ให้เป็นเครื่องหมายอื่นเช่น # เพื่อไม่ให้เป็นสูตร
2. คัดลอกแล้วไปวางยังเซลล์เป้าหมายแบบสลับแกน (Transpose)
3. เปลี่ยนเครื่องหมาย # ในเซลล์เป้าหมายให้กลับมาเป็นเครื่องหมาย = เพื่อให้เป็นสูตร

ภาพตัวอย่างแสดงการ Paste Link แบบ Transpose