ปัญหาง่าย ๆ ในแวบแรกพร้อมวิธีแก้ปัญหาที่ไม่ชัดเจน: แยกคำสุดท้ายออกจากบรรทัดข้อความ. หรือในกรณีทั่วไป แฟรกเมนต์สุดท้าย คั่นด้วยอักขระตัวคั่นที่กำหนด (ช่องว่าง เครื่องหมายจุลภาค ฯลฯ) กล่าวอีกนัยหนึ่ง จำเป็นต้องใช้การค้นหาแบบย้อนกลับ (ตั้งแต่ต้นจนจบ) ในสตริงของ กำหนดอักขระแล้วดึงอักขระทั้งหมดทางด้านขวาของมัน
ลองดูวิธีการเลือกหลายวิธีตามธรรมเนียม: สูตร แมโคร และผ่าน Power Query
วิธีที่ 1 สูตร
เพื่อให้เข้าใจแก่นแท้และกลไกของสูตรได้ง่ายขึ้น เรามาเริ่มกันใหม่จากระยะไกลกันก่อน ขั้นแรก ให้เพิ่มจำนวนช่องว่างระหว่างคำในข้อความต้นฉบับของเราเป็น 20 ชิ้น คุณสามารถทำได้ด้วยฟังก์ชันการแทนที่ ทดแทน (ทดแทน) และหน้าที่ของการทำซ้ำอักขระที่กำหนด N-times – ซ้ำ (ตัวแทน):
ตอนนี้เราตัดอักขระ 20 ตัวออกจากส่วนท้ายของข้อความผลลัพธ์โดยใช้ฟังก์ชัน ขวา (ขวา):
มันอุ่นขึ้นใช่มั้ย? มันยังคงลบช่องว่างเพิ่มเติมโดยใช้ฟังก์ชั่น TRIM (ทริม) และปัญหาจะได้รับการแก้ไข:
ในเวอร์ชันภาษาอังกฤษ สูตรของเราจะมีลักษณะดังนี้:
=TRIM(ขวา(แทนที่(A1;» «;REPT(» «;20));20))
ฉันหวังว่าชัดเจนว่าโดยหลักการแล้ว ไม่จำเป็นต้องใส่ช่องว่าง 20 ช่อง ตัวเลขใด ๆ ก็ได้ ตราบใดที่มีความยาวมากกว่าคำที่ยาวที่สุดในข้อความต้นฉบับ
และหากข้อความต้นฉบับไม่จำเป็นต้องแบ่งตามช่องว่าง แต่ด้วยอักขระตัวคั่นอื่น (เช่น ด้วยเครื่องหมายจุลภาค) สูตรของเราจะต้องได้รับการแก้ไขเล็กน้อย:
วิธีที่ 2. ฟังก์ชันมาโคร
งานแยกคำสุดท้ายหรือส่วนย่อยออกจากข้อความสามารถแก้ไขได้โดยใช้มาโคร กล่าวคือ การเขียนฟังก์ชันการค้นหาย้อนกลับใน Visual Basic ที่จะทำในสิ่งที่เราต้องการ - ค้นหาสตริงย่อยที่กำหนดในสตริงในทิศทางตรงกันข้าม - จาก สิ้นสุดที่จุดเริ่มต้น
กดแป้นพิมพ์ลัด อื่น ๆ+F11 หรือปุ่ม ของ Visual Basic แถบ ผู้พัฒนา (ผู้พัฒนา)เพื่อเปิดตัวแก้ไขแมโคร จากนั้นเพิ่มโมดูลใหม่ผ่านเมนู แทรก – โมดูล และคัดลอกรหัสต่อไปนี้ที่นั่น:
ฟังก์ชัน LastWord(txt As String, Optional delim As String = " ", Optional n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) End Function
ตอนนี้คุณสามารถบันทึกเวิร์กบุ๊ก (ในรูปแบบที่เปิดใช้งานแมโคร!) และใช้ฟังก์ชันที่สร้างขึ้นในไวยากรณ์ต่อไปนี้:
=คำสุดท้าย(txt ; delim ; n)
ที่ไหน
- TXT – เซลล์ที่มีข้อความต้นฉบับ
- เดลิม — ตัวคั่น (ค่าเริ่มต้น — เว้นวรรค)
- n – คำไหนควรแยกจากท้าย (โดยปริยาย – คำแรกจากท้าย)
หากมีการเปลี่ยนแปลงใดๆ ในข้อความต้นฉบับในอนาคต ฟังก์ชันมาโครของเราจะคำนวณใหม่ทันที เช่นเดียวกับฟังก์ชันแผ่นงาน Excel มาตรฐาน
วิธีที่ 3. Power Query
Power Query เป็นโปรแกรมเสริมฟรีจาก Microsoft สำหรับการนำเข้าข้อมูลไปยัง Excel จากเกือบทุกแหล่ง แล้วแปลงข้อมูลที่ดาวน์โหลดมาเป็นรูปแบบใดก็ได้ พลังและความยอดเยี่ยมของ Add-in นี้ยอดเยี่ยมมากจน Microsoft ได้สร้างคุณลักษณะทั้งหมดไว้ใน Excel 2016 โดยค่าเริ่มต้น สำหรับ Excel 2010-2013 Power Query สามารถดาวน์โหลดได้ฟรีจากที่นี่
งานของเราในการแยกคำหรือส่วนสุดท้ายผ่านตัวคั่นที่กำหนดโดยใช้ Power Query นั้นแก้ไขได้ง่ายมาก
ขั้นแรก ให้เปลี่ยนตารางข้อมูลของเราเป็นตารางอัจฉริยะโดยใช้แป้นพิมพ์ลัด Ctrl+T หรือคำสั่ง หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง):
จากนั้นเราโหลด "ตารางอัจฉริยะ" ที่สร้างขึ้นลงใน Power Query โดยใช้คำสั่ง จากตาราง/ช่วง (จากตาราง/ช่วง) แถบ ข้อมูล (ถ้าคุณมี Excel 2016) หรือบนแท็บ Power Query (ถ้าคุณมี Excel 2010-2013):
ในหน้าต่างตัวแก้ไขแบบสอบถามที่เปิดขึ้นบนแท็บ การแปลง (แปลง) เลือกทีม แยกคอลัมน์ – ตามตัวคั่น (คอลัมน์แยก — ตามตัวคั่น) จากนั้นยังคงตั้งค่าตัวคั่นและเลือกตัวเลือก ตัวคั่นขวาสุดที่จะตัดไม่ใช่ทุกคำ แต่เพียงคำสุดท้าย:
หลังจากคลิกที่ OK คำสุดท้ายจะถูกแยกออกเป็นคอลัมน์ใหม่ คอลัมน์แรกที่ไม่จำเป็นสามารถลบออกได้โดยคลิกขวาที่ส่วนหัวและเลือก ลบรายการออกจากรถเข็น (ลบ). คุณยังสามารถเปลี่ยนชื่อคอลัมน์ที่เหลือในส่วนหัวของตารางได้อีกด้วย
ผลลัพธ์สามารถอัปโหลดกลับไปที่แผ่นงานโดยใช้คำสั่ง หน้าแรก — ปิดและโหลด — ปิดและโหลดไปที่ … (หน้าแรก — ปิด & โหลด — ปิด & โหลดไปที่…):
และเป็นผลให้เราได้รับ:
แบบนี้ ถูกและดี ไม่มีสูตรและมาโคร แทบไม่ต้องแตะคีย์บอร์ดเลย 🙂
หากรายการเดิมเปลี่ยนไปในอนาคต แค่คลิกขวาหรือใช้แป้นพิมพ์ลัด Ctrl+อื่น ๆ+F5 อัปเดตคำขอของเรา
- การแยกข้อความติดหนึบออกเป็นคอลัมน์
- การแยกวิเคราะห์และแยกวิเคราะห์ข้อความด้วยนิพจน์ทั่วไป
- การแยกคำแรกออกจากข้อความด้วยฟังก์ชัน SUBSTITUTE