แยกข้อมูลในเซลล์โดยให้ค่าที่ซ้ำแสดงค่าเดียว

ปกติแล้วการแยกค่าในเซลล์เดียวเป็นหลายเซลล์สามารถใช้ Data > Text to columns เข้ามาช่วยได้ และถ้าหากในเซลล์นั้นคั่นแต่ละส่วนด้วยอักขระใด ๆ แล้วจะยิ่งแยกได้โดยง่าย แต่หากว่าเซลล์นั้นมีค่าซ้ำกันหลายค่าแต่เราต้องการนำค่าที่ซ้ำกันนั้นมาแสดงเพียงค่าเดียวย่อมจะมีความยุ่งยากลำบากกว่าปกติ

ยกตัวอย่างตามภาพด้านล่างจะมีอักขระในเซลล์ A1 คั่นแต่ละส่วนด้วยเครื่องหมายคอมม่า ซึ่งจะพบว่ามีหลายส่วนเป็นค่าที่ซ้ำกัน หากเราต้องการแยกค่าเหล่านั้นออกมาแสดงในแต่ละเซลล์โดยผลลัพธ์เริ่มตั้งแต่ B2 เรียงลงไปด้านล่างโดยต้องการให้ค่าที่ซ้ำกันแสดงเพียงค่าเดียว เราจำเป็นจะต้องใช้สูตร Array เข้ามาช่วย

ภาพแสดงแยกข้อมูลในเซลล์โดยให้ค่าที่ซ้ำแสดงค่าเดียว

ที่เซลล์ B2 เราสามารถเขียนสูตรได้ดังนี้

=IFERROR(INDEX(TRIM(MID(SUBSTITUTE( $A$2,",",REPT(" ",50)),50(ROW(INDIRECT("1:50"))-1)+1,50)),SMALL( IF(FREQUENCY(MATCH(TRIM(MID(SUBSTITUTE($A$2,",",REPT(" ",50)),50(ROW( INDIRECT("1:50"))-1)+1,50)),TRIM(MID(SUBSTITUTE($A$2,",",REPT(" ",50)), 50*(ROW(INDIRECT("1:50"))-)+1,50)),0),ROW(INDIRECT("1:"&LEN($A$2)))),
ROW(INDIRECT("1:"&LEN($A$2)))),ROWS(B$2:B2))),"")

Ctrl+Shift+Enter > คัดลอกสูตรลงด้านล่าง สามารถดาวน์โหลดไฟล์ตัวอย่างไปศึกษาได้ตามสะดวก

กรณีมีปัญหาเกี่ยวกับการใช้งาน Excel และ VBA สามารถสอบถามได้ที่ Excel Forum

สูตรหาลำดับข้อความภายในอีกข้อความ

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

สำหรับการแยกข้อความออกมาเป็นข้อความละเซลล์เพื่อง่ายต่อการจัดการหากว่าเราทราบแน่ชัดว่าข้อความนั้น ๆ แบ่งด้วยอักขระใด เราสามารถแบ่งด้วย Text to columns ได้อย่างง่าย ๆ และใช้ฟังก์ชั่นที่ไม่ซับซ้อนนักในการหาลำดับที่ของแต่ละข้อความออกมาได้

จากภาพด้านล่าง เซลล์ A2 เป็นตัวอย่างข้อความต้นทางหลายข้อความคั่นแต่ละข้อความด้วยเครื่องหมายคอมม่า ส่วนเซลล์ C2 เป็นข้อความเพียงบางส่วนที่เราจะนำไปหาจากข้อความใน A2 ว่าแต่ละข้อความใน C2 อยู่ในลำดับที่เท่าไรของ A2 โดยให้แสดงลำดับที่พบเรียงลงไปด้านล่าง เริ่มลำดับแรกที่เซลล์ D2

เมื่อนำแต่ละข้อความในเซลล์ C2 ไปหาจากข้อความในเซลล์ A2 จะพบว่า

1. A อยู่ในลำดับที่ 1
2. B อยู่ในลำดับที่ 2
3. AB อยู่ในลำดับที่ 6
4. K อยู่่ในลำดับที่ 9
5. CD อยู่ในลำดับที่ 3

ภาพแสดงการนำแต่ละข้อความในเซลล์ C2 ไปหาว่าอยู่ลำดับที่เท่าไรจากข้อความใน A2

[caption id="attachment_2767" align="alignnone" width="600"] String order in other string[/caption]

เราสามารถเขียนสูตรที่ D2 เพื่อหาคำตอบดังนี้

=IFERROR(INDEX(MATCH(MID($C$2,SMALL(IF(MID(","&$C$2&",",ROW(
INDIRECT("1:"&LEN($C$2))),1)=",",ROW(INDIRECT("1:"&LEN($C$2)))),
ROW(INDIRECT("1:"&LEN($C$2)))),SMALL(IF(MID($C$2&",",ROW(
INDIRECT("1:"&LEN($C$2)+1)),1)=",",ROW(INDIRECT("1:"&LEN($C$2)+1))),
ROW(INDIRECT("1:"&LEN($C$2))))-SMALL(IF(MID(","&$C$2&",",ROW(
INDIRECT("1:"&LEN($C$2))),1)=",",ROW(INDIRECT("1:"&LEN($C$2)))),
ROW(INDIRECT("1:"&LEN($C$2))))),MID($A$2,SMALL(IF(MID(","&$A$2&",",
ROW(INDIRECT("1:"&LEN($A$2))),1)=",",ROW(INDIRECT("1:"&LEN($A$2)))),
ROW(INDIRECT("1:"&LEN($A$2)))),SMALL(IF(MID($A$2&",",ROW(
INDIRECT("1:"&LEN($A$2)+1)),1)=",",ROW(INDIRECT("1:"&LEN($A$2)+1))),
ROW(INDIRECT("1:"&LEN($A$2))))-SMALL(IF(MID(","&$A$2&",",
ROW(INDIRECT("1:"&LEN($A$2))),1)=",",ROW(INDIRECT("1:"&LEN($A$2)))),
ROW(INDIRECT("1:"&LEN($A$2))))),0),ROWS(D$2:D2)),"")

Ctrl+Shift+Enter > Copy ลงด้านล่าง

สูตรนี้เป็นสูตร Array ที่มีความซับซ้อนสูง ซ้อนกันหลายชั้นเพื่อที่จะตัดแต่ละข้อความออกมาเสียก่อน แล้วค่อยหาลำดับที่พบข้อความใน C2 จากข้อความใน A2 ผู้ที่จะศึกษาสูตรลักษณะนี้จะต้องเข้าใจแต่ละฟังก์ชั่นเป็นอย่างดีมาก่อน สามารถดาวน์โหลดไฟล์แนบด้านล่างไปศึกษาได้ตามสะดวกครับครับ

การนำข้อมูลมาแสดงตามจำนวนรายการที่กำหนดโดยสามารถระบุหน้าที่ต้องการ

กรณีฐานข้อมูลมีรายการจำนวนมากและเราต้องการนำมาแสดงเฉพาะจำนวนบรรทัดที่ต้องการ เฉพาะหน้าที่ต้องการเพื่อที่ว่าปริมาณข้อมูลจะได้ไม่มากเกินไป เหมาะกับการประยุกต์ใช้ในการนำข้อมูลมาลงแบบฟอร์มต่าง ๆ เหมาะแก่การดูหรือเพื่อการพิมพ์ เรา สามารถประยุกต์ใช้ Scroll Bar มาช่วยในการทำงานลักษณะนี้ได้

การทำรายงานลักษณะดังกล่าวนั้น จะมีส่วนประกอบหลัก ๆ ตามด้านล่าง

1. ฐานข้อมูล
2. รายงานตามจำนวนบรรทัดที่ต้องการ
3. Scroll Bar กำหนดจำนวนบรรทัดที่ต้องการนำมาแสดงผล
4. Scroll Bar กำหนดหน้าที่ต้องการนำมาแสดงผล

ภาพประกอบการนำข้อมูลจากฐานข้อมูลมาแสดงตามจำนวนบรรทัดที่กำหนดโดยสามารถระบุหน้าที่ต้องการนำมาแสดง

จากภาพด้านบนมีขั้นตอนการทำงานดังนี้

A. แสดงเมนู Developer เพื่อสะดวกต่อการเรียกใช้ Scroll Bar

1. คลิกขวาที่ตำแหน่งใด ๆ บน Ribbon
2. เลือก Customize Ribbon
3. เลือก Developer
4. คลิก OK

การทำเช่นนี้จะมีเมนู Developer ขึ้นมาให้ใช้งาน ดูขั้นตอนตามภาพด้านล่างประกอบ

B. สร้างปุ่ม Scroll Bar ขึ้นมา 2 ปุ่ม เพื่อใช้คลิกเลือกจำนวนบรรทัดและระบุหน้าที่ต้องการแสดงผล โดยมีขั้นตอนคือ

1. เข้าเมนู Developer
2. คลิกปุ่ม Insert
3. คลิกปุ่ม Scroll Bar
4. นำไปวาดในตำแหน่งที่ต้องการ

ภาพประกอบการสร้างปุ่ม Scroll Bar

C. กำหนดค่าให้กับ Scroll Bar แต่ละปุ่มโดยคลิกขวาที่ Scroll Bar แล้วเลือก Format Control

1. กำหนดค่าให้กับปุ่มที่ใช้แสดง "จำนวนรายการ" ตามภาพ
   
2. กำหนดค่าให้กับปุ่มที่ใช้แสดง "หน้าที่" ตามภาพ
   

D. เขียนสูตรเพื่อนำข้อมูลมาแสดงตามเงื่อนที่เลือกจาก Scroll Bar

1. เซลล์ M2 คีย์สูตร
   =K2*L2
   Enter
2. เซลล์ F2 คีย์สูตร
   =IF(ROWS(F$2:F2)>$K$2,"",$M$2-$K$2+ROWS(F$2:F2))
   Enter > Copy ลงด้านล่าง
3. เซลล์ G2 คีย์สูตร
   =IFERROR(INDEX(B$2:B$41,$F2),"")
   Enter > Copy ไปด้านขวาถึง I2 แล้ว Copy ลงด้านล่าง
4. เซลล์ L3 คีย์สูตร
   ="หน้าละ "&K2&" รายการ"
   Enter
5. เซลล์ L4 คีย์สูตร
   ="หน้าที่ "&L2
   Enter

หลังจากการกำหนดค่าให้กับ Scroll Bar และเขียนสูตรต่าง ๆ เรียบร้อยแล้ว สามารถคลิกที่ Scroll Bar เพื่อเลือกดูข้อมูลได้ตามต้องการ

Revised: January 29, 2017 at 07:00

การนำข้อมูลที่อยู่ในคอลัมน์เดียวไปเรียงหลายคอลัมน์

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

ตัวอย่างเช่นข้อมูลอยู่ที่ A2:A100 และต้องการเรียงใหม่ที่คอลัมน์ B เริ่มจาก B2 เป็นต้นไปโดยสามารถเลือกได้ว่าแต่ละคอลัมน์จะให้มีข้อมูลเพียงกี่บรรทัด

สามารถทำได้ดังนี้

1. ที่เซลล์ B1 ทำ Validation เพื่อเลือกจำนวนบรรทัดที่ต้องการ
2. ที่เซลล์ B2 คีย์สูตรเพื่อจัดเรียงข้อมูลใหม่
   =IF(ROWS(B$2:B2)>$B$1,"",IFERROR(INDEX($A$2:$A$100,ROWS(B$2:B2)+($B$1*(COLUMNS($B2:B2)-1))),"")
   Enter > Copy ไปด้านขวาและลงด้านล่างตามต้องการ
   
   สูตรข้างต้นใช้กับ Excel Version 2007 เป็นต้นไป ส่วน Excel 2003 สามารถใช้สูตรตามด้านล่างครับ
   =IF(OR(ROWS(B$2:B2)>$B$1,ROWS(B$2:B2)+($B$1*(COLUMNS($B2:B2)-1))>COUNTA($A$2:$A$100)),"",INDEX($A$2:$A$100,ROWS(B$2:B2)+($B$1*(COLUMNS($B2:B2)-1))))
   Enter > Copy ไปทางด้านขวาและลงด้านล่าง

ภาพประกอบการนำข้อมูลที่อยู่ในคอลัมน์เดียวไปเรียงหลายคอลัมน์