การใช้ Flash Fill
นี่คือคำถามที่ถามบ่อย คือ วิธีดึงข้อมูลจากคอลัมน์หนึ่งไปเป็นรูปแบบอื่น หรือหลายคอลัมน์
เราจะมาดูวิธีทำด้วยฟีเจอร์ Flash Fill ก่อน Flash Fill เป็นฟีเจอร์ใหม่เพิ่มใน Microsoft Excel 2013 และเวอร์ชันใหม่กว่า
ในตัวอย่างด้านล่าง มีรายการชื่อและนามสกุลในคอลัมน์ A Flash Fill ต้องการคอลัมน์ต้นทางเสมอเพื่อทำงาน ซึ่งในกรณีนี้คือคอลัมน์ A ที่มีชื่อพนักงาน Flash Fill มักใช้ในตัวอย่างสร้างอีเมลหรือข้อมูลซ้ำที่มีรูปแบบ สูตรข้อความ (Text formulas) ก็ใช้แพร่หลายสำหรับการสร้างอีเมล มีสูตรข้อความหลากหลาย จากซับซ้อนไปจนถึงง่าย ในบทความนี้มีตัวอย่าง Flash Fill สร้างอีเมล ตัวอย่างสูตรข้อความสร้างอีเมล และอัปเดตด้วยข้อเสนอสูตรข้อความจากผู้อ่าน
1) เราต้องการเติมคอลัมน์ B ด้วยที่อยู่อีเมลของพนักงาน วิธีที่ IT ของบริษัทสร้างอีเมลคือ รวมชื่อและนามสกุลของพนักพร้อมโดเมนบริษัท เพื่อผลิตที่อยู่อีเมล
2) ในเซลล์ B5 ต้องให้ Flash Fill ตัวอย่างของสิ่งที่ต้องการก่อน Flash Fill จะใช้เป็นเทมเพลตเติมส่วนที่เหลือของคอลัมน์ ใส่ข้อความ:
admin@wsxdn.com
กด CTRL-ENTER
3) หลังกด CTRL-ENTER Excel จะเปลี่ยนข้อความเป็นไฮเปอร์ลิงก์อัตโนมัติ
4) ต้องไฮไลท์ช่วง B5:B15 ที่ต้องการเติมอีเมลด้วย Flash Fill และกด CTRL-E (ปุ่มลัด Flash Fill) หรือกับช่วงที่ไฮไลท์ ไปที่แท็บ Data คลิก Flash Fill
5) ส่วนที่เหลือของช่วงจะถูกเติมด้วยที่อยู่อีเมลที่ต้องการ
อ่านเพิ่มเติม: การแยกข้อความใน Excel ด้วย Flash Fill
การใช้สูตร TEXT
ไม่เสียดายหากคุณใช้ Excel เวอร์ชันเก่ากว่า เราสามารถทำอย่างเดียวกันได้ด้วยสูตร TEXT การใช้สูตร TEXT ซับซ้อนกว่าเล็กน้อยในกรณีนี้
1) ก่อนอื่น ต้องดูข้อความในคอลัมน์ A เพื่อตัดสินใจว่าจะใช้ฟังก์ชัน TEXT ไหนในสูตรซับซ้อนของเรา
2) เราเห็นว่ามีชื่อแรก ตามด้วยช่องว่าง แล้วตามด้วยนามสกุล
3) เราจะสร้างสูตรนี้จากศูนย์
สิ่งแรก ใช้ฟังก์ชัน SEARCH เพื่อหาช่องว่าง ในเซลล์ B5 ใส่:
=SEARCH(” “, A5, 1)
กด CTRL-ENTER
4) หลังกด CTRL-ENTER ได้ 5 หมายถึงในกรณีของ Emma Smith ช่องว่างคือตัวอักษรที่ 5 คลิกสองครั้งและลากสูตรลง จะเห็นตำแหน่งช่องว่างของพนักงานคนอื่น เช่น Nicole Roberts ช่องว่างเป็นตัวอักษรที่ 7
5) ตอนนี้ใช้ฟังก์ชัน LEFT ดึงชื่อแรก เนื่องจากตำแหน่งช่องว่างรู้แล้ว สามารถดึงตัวอักษรทั้งหมดทางซ้ายของช่องว่างเพื่อได้ชื่อแรก
ต้องให้ LEFT ฟังก์ชันรับสตริงข้อความนำเข้า และจำนวนตัวอักษรจากซ้ายที่ต้องการดึง
เลือกเซลล์ A5 เป็นสตริงข้อความ แล้วเพื่อหาจำนวนตัวอักษรที่จะดึงจากซ้ายเพื่อได้ชื่อแรกอย่างเดียว ใช้ SEARCH และลบ 1:
=LEFT(A5, SEARCH(” “, A5, 1) -1)
ลบ 1 เพราะไม่อยากรวมช่องว่าง
6) กด CTRL-ENTER คลิกสองครั้งลากสูตรลง ได้ชื่อแรกทั้งหมดตามภาพ
7) คัดลอกสูตรด้วยการกด CTRL-C สองครั้ง เพื่อเปิด Clipboard
8) ตอนนี้มีสูตรเก็บไว้ใช้ภายหลัง ล้างคอลัมน์ B และทำงานดึงนามสกุล
9) เรารู้ตำแหน่งช่องว่าง และใช้ LEFT ดึงชื่อแรกแล้ว สามารถใช้ RIGHT ดึงนามสกุล เริ่มสร้างสูตรดึงนามสกุล
10) ต้องบอก RIGHT ฟังก์ชันจำนวนตัวอักษรที่จะดึงจากขวา รู้ตำแหน่งช่องว่าง ใช้ LEN หาจำนวนตัวอักษรทั้งหมดในสตริง แล้วลบเศษจาก SEARCH ที่บอกตำแหน่งช่องว่าง
สูตร:
=LEN(A5) – SEARCH(” “, A5, 1))
ได้ตัวอักษรที่เหลือหลังช่องว่าง
11) ใช้ RIGHT ร่วมกับสูตรข้างต้น เพื่อดึงนามสกุล เนื่องจากรู้จำนวนตัวอักษรจากขวาที่จะดึงจนถึงช่องว่าง
พิมพ์สูตร:
=RIGHT(A5, LEN(A5) – SEARCH(” “, A5, 1))
13) คัดลอกสูตร แล้วล้างเนื้อหาเซลล์
14) ตอนนี้รวมทุกอย่างในสูตรใหญ่เดียว เนื่องจากแยกขั้นตอนเพื่อแสดงตรรกะ และคัดลอกแต่ละส่วนไป Clipboard แล้ว
หลังล้างคอลัมน์ ในเซลล์ B5 พิมพ์สูตร:
= LEFT(A5, SEARCH(” “, A5, 1) – 1) & RIGHT(A5, LEN(A5) – SEARCH(” “, A5, 1)) & “@mycompany.com”
15) คลิกสองครั้งลากลง เพื่อเติมคอลัมน์ที่เหลือด้วยที่อยู่อีเมล
ข้อเสนอจากผู้อ่านที่ดีเยี่ยม เพื่อให้ผลลัพธ์เดียวกัน:
Rahul Singh เสนอสูตรต่อไปนี้ให้ผลลัพธ์เดียวกัน ด้วยสูตรที่เรียบง่ายกว่ามาก:
ในเซลล์ B5 พิมพ์สูตร:
=CONCATENATE(SUBSTITUTE(A5,” “, “”),”@mycompany.com”)
Karthik เสนอสูตรต่อไปนี้ให้ผลลัพธ์เดียวกับสูตรข้อความต้นฉบับในโพสต์ ก็เรียบง่ายกว่ามาก:
=SUBSTITUTE(A5,” “,””)&”@mycompany.com”
อ่านเพิ่มเติม: วิธีใช้ Flash Fill ใน Excel แยกข้อมูล (4 วิธีเร็ว)
ไฟล์ทำงาน
สรุป
Flash Fill เป็นฟีเจอร์ใหม่ที่น่าสนใจใน Microsoft Excel 2013 และใหม่กว่า เป็น AutoFill ที่เพิ่มประสิทธิภาพ และประหยัดเวลามาก อย่างไรก็ตาม สำหรับผู้ใช้ Excel เวอร์ชันเก่า และในบางกรณีที่ Flash Fill ไม่ทำงาน สูตร TEXT มาช่วยได้ แม้จะซับซ้อนกว่าในการสร้าง แต่ไม่มีตัวอย่างซับซ้อนเกินกว่าที่จะแก้ไม่ได้ โปรดแสดงความคิดเห็นว่าคุณชอบใช้ Flash Fill หรือสูตร TEXT ในการดึง/จัดรูปแบบข้อมูล และเหตุผล
บทความที่เกี่ยวข้อง
- วิธีปิด Flash Fill ใน Excel (2 วิธีง่าย)
- [Solved!] Flash Fill Not Working in Excel (5 Reasons with Solutions)
- Flash Fill Not Recognizing Pattern in Excel (4 Causes with Fixes)
