เปิดโปง! เทคนิคการเชื่อม Text จากหลายๆ Cell (มากๆ) เข้าด้วยกัน

ผมเชื่อว่าหลายคนต้องเคยเจอปัญหาในการเชื่อม Text จากหลายๆช่องเข้าด้วยกันมาก่อนแน่ๆ เพราะมันไม่มีสูตรสำเร็จรูปเช่นเดียวกับปัญหาอื่นๆ เช่น การบวกหลายๆช่องเข้าด้วยกัน (ฟังก์ชั่น  SUM ทำได้) หรือการ คูณหลายๆช่องเข้าด้วยกัน (ฟังก์ชั่น PRODUCT ทำได้) แต่ทำไม พอจะเชื่อม Text เข้าด้วยกันดันทำไม่ได้!! แม้ว่า Excel จะมีฟังก์ชั่น CONCATENATE มาให้ แต่มันก็ไม่สามารถใช้แบบ =CONCATENATE(A1:H1) ได้ แต่กลับต้องใส่เป็น =CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1) หรือ =A1&B1&C1&D1&E1&F1&G1&H1 แทน ซึ่งเหนื่อยมากๆ โดยเฉพาะหากต้องเชื่อมมากกว่า 10 Cell ขึ้นไป!! (และอาจทำผิดโดยไม่รู้ตัวด้วย) วันนี้ผมมีเทคนิคแก้ปัญหาดังกล่าวมานำเสนอ โดยไม่จำเป็นต้องใช้ VBA เข้าช่วยด้วย ทำยังไงมาดูกันครับ ลองทำตามดูนะครับ ดูเหมือนจะเยอะ แต่ ทำจริงๆไม่กี่วินาทีเอง ก่อนอื่นสมมติว่าผมต้องการเชื่อม Text ในช่อง A1:H1  (มีคำว่า Text1, Text2...Text8) วางอยู่…

การ Lookup ข้อมูล แบบ Case-Sensitive (สนตัวพิมพ์เล็กพิมพ์ใหญ่)

Excel Lookup ไม่ลืมหูลืมตา ไม่ดูตัวพิมพ์เล็กพิมพ์ใหญ่!? ผมเดาว่าหลายๆ คนอาจยังไม่รู้ว่า ปกติ Excel จะ Lookup ข้อมูลแบบไม่สนตัวพิมพ์เล็กพิมพ์ใหญ่ นั่นคือ หากเราจะหาคำว่า B1 จากตารางที่มีทั้งคำว่า b1 และคำว่า B1 Excel จะเอาผลลัพท์จากคำที่เจอก่อน (วิ่งจากบนลงล่าง) โดยไม่สนว่ามันจะเป็นตัวพิมพ์เดียวกับคำที่เราต้องการหรือไม่ เพราะ Excel มันทำงาน Lookup โดยมองทั้งสองคำนั้นเหมือนๆ กันนั่นเอง ทางแก้ไข วิธีที่จะ Lookup ข้อมูล แบบ Case-Sensitive (สนตัวพิมพ์เล็กพิมพ์ใหญ่) ได้ จะต้องใช้ ฟังก์ชั่น EXACT และการทำงานของ Array Formula มาช่วย Concept การใส่สูตร {=INDEX(B2:B7,MATCH(1,--EXACT(A2:A7,A10),0))} หาว่าคำที่ต้องการอยู่แถวที่เท่าไหร่ก่อน ด้วย MATCH กับ EXACT…

เล่นแร่แปรสูตร : การแปลงวันที่ Text ให้เป็นวันที่ Date

ปกติแล้ว Excel จะมีฟังก์ชั่นที่ชื่อว่า DATEVALUE ในการเปลี่ยนวันที่ในรูปแบบ Text ให้กลายเป็นรูปแบบ Date จริงๆ ที่เป็นตัวเลขอยู่แล้ว... แต่ฟังก์ชั่นนี้มีข้อจำกัดอยู่มาก คือ มันจะ Convert Text ได้แค่ในรูปแบบที่มันรู้จักเท่านั้น (ซึ่งมีไม่กี่แบบ คล้ายๆตอนที่เราพิมพ์ลงไปใน cell ปกติ แหละครับ ว่า 31/1/2014 หรือ 31-Jan-2014 หรือ 31-01-2014 แล้ว excel มันจะฉลาดแปลงเป็นวันที่ได้เอง) ดังนั้น ถ้าหากเรามี Date ในรูปแบบแปลกไปจากที่มันรู้จัก เช่น 31012014 หรือ 20140131 อะไรแบบนี้ ฟังก์ชั่นนี้ก็จะเอ๋อไปเลย วันนี้ผมมีวิธีแก้มาแนะนำหลากหลายวิธีด้วยกันครับ ลองติดตามดูได้ วิธี 1 ตัด Text ออกเป็นส่วนๆ แล้วเชื่อม (ยาก) Concept : ใช้พวกฟังก์ชั่น LEFT…

เล่นแร่แปรสูตร: หาว่าใน cell นั้นๆ มีตัวอักษรที่เรากำหนดอยู่กี่ตัว

สถานการณ์ ในช่อง A1 มีคำว่า THE*DARK*KNIGHT*IS*COOL ผมต้องการหาว่ามีตัว * กี่ตัว จะทำยังไง?? ส่วนประกอบ LEN เพื่อนับจำนวนตัวอักษรทั้งหมด SUBSTITUTE(text,old_text,new_text,[instance_num]) เพื่อแทนที่ตัวอักษร Concept การผสมสูตร ใช้ LEN เพื่อนับตัวอักษรทั้งหมดว่ามีกี่ตัว ใช้ SUBSTITUTE แทนที่ตัวอักษรที่เราต้องการหาด้วย "" (เหมือนลบตัวอักษรนั้นทิ้ง) ใช้ LEN นับคำที่ SUBSTITUTE แล้ว จากนั้นเอาไปหักออกจากที่นับไว้ตอนแรก ขั้นตอนการผสมสูตร ในช่อง A1 เขียนว่า THE*DARK*KNIGHT*IS*COOL ในช่องอื่น ช่องไหนก็ได้ =LEN(A1)  ได้ 23 =SUBSTITUTE(A1,"*","")  ได้ THEDARKKNIGHTISCOOL เราตัดตัว * ทิ้งทั้งหมด ด้วยการไม่กำหนด [instance_num] ว่าจะแทนที่ตัวไหนเป็นพิเศษ นับ THEDARKKNIGHTISCOOL =LEN(SUBSTITUTE(A1,"*","")) ได้ 19…
Excel Quick Tips by inwexcel

การแปลงค่าจาก ตัวหนังสือ ตัวเลข

ทำไมต้องแปลงค่า? หากเราใช้ข้อมูลที่มีรูปแบบไม่ตรงตามที่ต้องการ อาจเปิดปัญหาในการใช้สูตร เช่น Lookup ค่าไม่เจอ ก็เป็นไปได้ครับ วิธีแปลงค่า จากตัวหนังสือ => ตัวเลข ใน Excel วิธีที่ง่าย และได้ผลที่สุด คือ เอาไปคูณ 1 ครับ เช่น ในช่อง A1 มีตัวหนังสือว่า 000540 หากจะแปลงไปเป็นตัวเลข และแสดงผลในช่อง B1 ในช่อง B1 ให้เขียนว่า =A1*1 ซึ่งจะได้ค่าเป็น 540 แบบเป็น Number นั่นเอง วิธีแปลงค่า จากตัวเลข => ตัวหนังสือ ใน Excel วิธีที่ง่าย และได้ผลที่สุด คือ เอาไป & ช่องว่าง ครับ เช่น ในช่อง A1…