การใช้สูตร & แบบไม่ให้สนใจในช่องที่ไม่มีค่าอยู่

[ตาโต]

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

[snasui]

ลองตามนี้ครับ

ที่ E2 คีย์

=LEFT(REPLACE(REPLACE(REPLACE(A2&B2&C2&D2,4,0,","),3,0,","),2,0,","),IF(COUNTA(A2:D2)=1,1,COUNTA(A2:D2)+COUNTA(A2:D2)-1))

Enter > Copy ลงด้านล่าง

[kmb]

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

ข้อแตกต่างของกรณีที่ผมใช้คือข้อมูลในแต่ละเซลล์ไม่ใช่ 1 ตัวอักษร

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

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

[snasui]

ถ้าข้อมูลในแต่ละเซลล์มากกว่า 1 ตัวอักษรและต้องการให้มีคอมม่าคั่นระหว่างตัวอักษรแต่ละตัว หันไปใช้ Morefunc หรือเขียน VBA แทนครับ

ยกตัวอย่างเช่น A1:D1 มีข้อความดังนี้ตามลำดับ

abcd|e|f|ghijk <== เครื่องหมาย | คือเส้นแบ่งเซลล์

ที่ E1 คีย์

=Mconcat(Mid(Mconcat(A1:D1),Row(Indirect("1:"&Sum(Len(A1:D1)))),1),",")

Ctrl+Shift+Enter

[kmb]

ได้แล้วครับ ขอบคุณสำหรับคำชี้แนะครับ

ถ้าเป็นกรณีที่ในเซลล์มีตัวอักษรมากกว่า 1 ตัว จากสูตรด้านบนเปลี่ยนเป็นตามนี้เลยครับ
=IF(RIGHT(MCONCAT(A2:D2,","),1)=",",LEFT(MCONCAT(A2:D2,","),LEN(MCONCAT(A2:D2,","))-1),MCONCAT(A2:D2,","))

แต่ต้องดาวน์โหลดและติดตั้ง Morefunc ก่อนตามลิงค์นี้ครับ
viewtopic.php?f=3&t=9

[snasui]

ได้แล้วครับ ขอบคุณสำหรับคำชี้แนะครับ

ถ้าเป็นกรณีที่ในเซลล์มีตัวอักษรมากกว่า 1 ตัว จากสูตรด้านบนเปลี่ยนเป็นตามนี้เลยครับ
=IF(RIGHT(MCONCAT(A2:D2,","),1)=",",LEFT(MCONCAT(A2:D2,","),LEN(MCONCAT(A2:D2,","))-1),MCONCAT(A2:D2,","))

แต่ต้องดาวน์โหลดและติดตั้ง Morefunc ก่อนตามลิงค์นี้ครับ
viewtopic.php?f=3&t=9

กรณีแต่ละเซลล์มีหลายอักขระและต้องการคั่นค่าในแต่ละเซลล์ด้วยคอมม่า สามารถเขียน Mconcat ที่ E2 ได้อีกวิธีตามด้านล่างครับ

=Left(Mconcat(A2:D2,","),Len(Mconcat(A2:D2,","))-(D2=""))

Enter > Copy ลงด้านล่าง

[kmb]

ได้แล้วครับ ขอบคุณสำหรับคำชี้แนะครับ

ถ้าเป็นกรณีที่ในเซลล์มีตัวอักษรมากกว่า 1 ตัว จากสูตรด้านบนเปลี่ยนเป็นตามนี้เลยครับ
=IF(RIGHT(MCONCAT(A2:D2,","),1)=",",LEFT(MCONCAT(A2:D2,","),LEN(MCONCAT(A2:D2,","))-1),MCONCAT(A2:D2,","))

แต่ต้องดาวน์โหลดและติดตั้ง Morefunc ก่อนตามลิงค์นี้ครับ
viewtopic.php?f=3&t=9

กรณีแต่ละเซลล์มีหลายอักขระและต้องการคั่นค่าในแต่ละเซลล์ด้วยคอมม่า สามารถเขียน Mconcat ที่ E2 ได้อีกวิธีตามด้านล่างครับ

=Left(Mconcat(A2:D2,","),Len(Mconcat(A2:D2,","))-(D2=""))

Enter > Copy ลงด้านล่าง

ขอบคุณอีกครั้งสำหรับเทคนิคนี้ครับ จะนำไปประยุกต์ใช้ต่อไปครับ

[ตาโต]

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

[ตาโต]

เพิ่มเติมจากเงื่อนไขเดิมค่ะ คือต้องการให้มีการแสดงคำนำหน้าว่า IT และข้อมูลมีคอลัมภ์อื่นคั่นค่ะ

[snasui]

ลองตามนี้ครับ

เซลล์ O2 คีย์

="IT,"&LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A2&C2&E2&G2&I2&K2&M2,7,0,","),6,0,","),5,0,","),4,0,","),3,0,","),2,0,","),IF(COUNTA(A2:M2)=1,1,COUNTA(A2:M2)+COUNTA(A2:M2)-1))

Enter > Copy ลงด้านล่าง

[ตาโต]

เพิ่มเติมค่ะ ตัวอักษรในแต่ละคอลัมภ์มีมากกว่า 1 ตัวโดยมีได้ไม่จำกัด และเป็นภาษาไทยหรือภาษาอังกฤษหรือตัวเลยก็ได้ค่ะ

ddd.jpg

[ตาโต]

ทำได้มั้ยคะ T-T

[snasui]

ในคราวต่อไปควรยกตัวอย่างข้อมูลที่แทนข้อมูลจริงด้วยครับ

กรณีที่มีจำนวนคอลัมน์เท่าที่ยกตัวอย่างมา ผมปรับสูตรให้ใหม่โดย

ที่ O2 คีย์

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("IT,"&A2&","&B2&","&C2&","&D2&","&E2&","&F2&","&G2&","&H2&","&I2&","&J2&","&K2&","&L2&","&M2&",",",,,,,",","),",,,,",","),",,,",","),",,",","),SUMPRODUCT(LEN(A2:M2))+COUNTA(A2:M2)+2)

Enter > Copy ลงด้านล่าง

[ตาโต]

ได้แล้วค่ะ ขอบคุณมากๆค่ะ คราวหน้าจะปรับปรุงเรื่องตัวอย่างค่ะ

[ตาโต]

ตกหล่นไปนิดนึงค่ะ ไม่เอาคอลัมภ์ที่คั่นอยู่ค่ะ ส่งตัวอย่างมาให้แล้วค่ะ

[snasui]

ลองตามนี้ครับ

ที่ O2 คีย์

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("IT,"&A2&","&C2&","&E2&","&G2&","&I2&","&K2&","&M2&",",",,,,,",","),",,,,",","),",,,",","),",,",","),LEN(A2&C2&E2&G2&I2&K2&M2)+COUNTA(A2,C2,E2,G2,I2,K2,M2)+2)

Enter > Copy ลงด้านล่าง

[ตาโต]

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

[snasui]

การตัดค่าด้ายซ้ายมาตามจำนวนที่ต้องการคือคำสั่ง Left(x,y) ครับ x คือข้อความที่ต้องการตัดค่าด้านซ้ายออกมา y คือจำนวนข้อความที่ตัด ถ้าเขียน =Left("ABCD",3) จะหมายถึงตัดด้านซ้ายข้อความ ABCD มา 3 อักขระ ผลลัพธ์จะได้ ABC

เมื่อแทนค่าในสูตรด้านบน

x คือ SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("IT,"&A2&","&C2&","&E2&","&G2&","&I2&","&K2&","&M2&",",",,,,,",","),",,,,",","),",,,",","),",,",",") และ y คือ LEN(A2&C2&E2&G2&I2&K2&M2)+COUNTA(A2,C2,E2,G2,I2,K2,M2)+2

สำหรับ SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("IT,"&A2&","&C2&","&E2&","&G2&","&I2&","&K2&","&M2&",",",,,,,",","),",,,,",","),",,,",","),",,",",")
หลักการคือให้นำข้อความ IT, มาเชื่อมกับ A2, C2, E2, G2, I2, K2, M2 และ , รวมทั้งหมด 9 ชุด จากนั้น เปลี่ยนเครื่องหมาย , ที่อยู่ติด ๆ กันทิ้งไป โดยเปลี่ยน ,,,,, (5 ตัว) ให้เป็น , คือเหลือเพียงตัวเดียว และเปลี่ยนคอมม่า 4 ตัวให้เหลือเพียงตัวเดียว, เปลี่ยนคอมม่า 3 ตัวให้เหลือเพียงตัวเดียว และเปลี่ยนคอมม่า 2 ตัวให้เหลือเพียงตัวเดียว

ส่วน LEN(A2&C2&E2&G2&I2&K2&M2)+COUNTA(A2,C2,E2,G2,I2,K2,M2)+2 ความหมายของ LEN(A2&C2&E2&G2&I2&K2&M2) คือให้นับว่า A2&C2&E2&G2&I2&K2&M2 มีกี่อักขระ บวกกับผลลัพธ์ของ COUNTA(A2,C2,E2,G2,I2,K2,M2) ซึ่งเป็นการนับว่า A2,C2,E2,G2,I2,K2,M2 มีค่าใด ๆ อยู่จำนวนกี่เซลล์

สุดท้ายบวกด้วย 2 ซึ่งเป็นตัวแปรสำคัญ สาเหตุหลักที่บวกด้วย 2 เนื่องจากว่าในขั้นตอนแรกเอา IT, เข้ามาเชื่อมเข้าไปด้วย ซึ่งมีถึง 3 อักขระ ประกอบกับตัวที่สำคัญอีกตัวหนึ่งคือ , ที่เราเชื่อมเข้าไปเป็นตัวสุดท้าย

สมมุติตัวอย่างให้ง่าย หากไม่มีข้อมูลใด ๆ ในช่อง A2,C2,E2,G2,I2,K2,M2 ผลลัพธ์จะได้

=LEFT("IT,",0+0+2) (ที่เหลือคอมม่าเพียงตัวเดียวหลัง IT เป็น IT, เป็นผลมาจากฟังก์ชั่น Substitute) นั่นหมายความว่าตัดค่าด้านซ้ายของ IT, มาเพียง 2 อักขระ จะได้คำตอบเป็น IT

[ตาโต]

สุดยอดมากค่ะ ขอบคุณค่ะ ^^

[ตาโต]

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

=LEFT(SUBSTITUTE(SUBSTITUTE("IT,"&F4&","&H4&","&J4&","&L4&","&N4&"",",,,",","),",,",","),SUMPRODUCT(LEN(F4:N4))-COUNT(F4:N4)+COUNTIF(F4:N4,"?*")+2)