การใช้แฟลชเติม
นี่เป็นคำถามที่มักถูกถามถึงวิธีการดึงข้อมูลจากคอลัมน์หนึ่งไปยังอีกรูปแบบหนึ่งหรือมากกว่าหนึ่งคอลัมน์
เราจะไปดูวิธีการทำสิ่งนี้โดยใช้คุณสมบัติการเติมแฟลชก่อน เติมแฟลช เป็นคุณลักษณะใหม่ที่เพิ่มลงใน Microsoft Excel 2013 และเวอร์ชันที่ใหม่กว่า
ในตัวอย่างด้านล่าง เรามีรายชื่อและนามสกุลในคอลัมน์ A การเติมแบบรวดเร็วจำเป็นต้องมีคอลัมน์ต้นทางเสมอ เพื่อให้ทำงานได้ ซึ่งในกรณีนี้คือคอลัมน์ A ซึ่งมีชื่อพนักงาน มักใช้ Flash Fill ในตัวอย่างเพื่อสร้างที่อยู่อีเมลหรือข้อมูลสูตรซ้ำประเภทอื่นๆ สูตรข้อความยังใช้กันอย่างแพร่หลายสำหรับคำถามเกี่ยวกับการสร้างอีเมล มีสูตรข้อความหลากหลายรูปแบบที่ใครๆ ก็ใช้ได้ ตั้งแต่แบบซับซ้อนไปจนถึงแบบเรียบง่าย ในบทความนี้ เรามีตัวอย่างการสร้างอีเมลแบบเติมแฟลช ตัวอย่างการสร้างอีเมลสูตรข้อความ และเราได้อัปเดตด้วยคำแนะนำสูตรข้อความของผู้วิจารณ์
1) เราต้องการเติมคอลัมน์ B ด้วยที่อยู่อีเมลของพนักงาน วิธีสร้างที่อยู่อีเมลโดยฝ่ายไอทีภายในของบริษัท คือ ชื่อและนามสกุลของพนักงานจะถูกรวมเข้ากับชื่อโดเมนของบริษัท เพื่อสร้างที่อยู่อีเมล
2) ในเซลล์ B5 เราต้องให้ Flash Fill ซึ่งเป็นตัวอย่างของสิ่งที่เราต้องการก่อน จากนั้น Flash Fill จะใช้สิ่งนี้เป็นเทมเพลตเพื่อเติมข้อมูลในคอลัมน์ที่เหลือ ดังนั้นเราจึงป้อนข้อความ:
จากนั้นให้กด CTRL-ENTER
3) หลังจากกด CTRL-ENTER หนึ่งครั้ง Excel จะเปลี่ยนข้อความเป็นไฮเปอร์ลิงก์โดยอัตโนมัติ
4) จากนั้นเราต้องไฮไลต์ช่วงทั้งหมด B5:B15 ที่เราต้องการเติมด้วยที่อยู่อีเมลโดยใช้ Flash Fill แล้วกด CTRL-E ซึ่งเป็นปุ่มลัดสำหรับ Flash Fill หรือช่วงที่ไฮไลต์ให้ไปที่ Data จากนั้นคลิกที่ Flash Fill ตามที่แสดง
5) ส่วนที่เหลือของช่วงจะเต็มไปด้วยที่อยู่อีเมลที่จำเป็น
อ่านเพิ่มเติม: การแยกข้อความใน Excel โดยใช้ Flash Fill
การใช้สูตร TEXT
ทั้งหมดจะไม่สูญหายหากคุณมี Microsoft Excel เวอร์ชันก่อนหน้า เนื่องจากเราสามารถทำสิ่งเดียวกันกับสูตร TEXT ได้ การใช้สูตร TEXT นั้นซับซ้อนกว่าเล็กน้อย อย่างไรก็ตาม ในกรณีนี้
1) ประการแรก เราต้องดูที่ข้อความในคอลัมน์ A เพื่อหาว่าจะใช้ฟังก์ชัน TEXT ใดในสูตรที่ซับซ้อนของเรา
2) เราเห็นว่าเรามีชื่อ ตามด้วยเว้นวรรค แล้วก็นามสกุล
3) เราจะสร้างสูตรนี้ตั้งแต่เริ่มต้น
อย่างแรก เราใช้ฟังก์ชัน SEARCH เพื่อค้นหาช่องว่าง และในเซลล์ B5 เราป้อน:
=ค้นหา (” “, A5, 1)
กด CTRL-ENTER
4) หลังจากกด CTRL-ENTER เราจะได้ 5 หรืออีกนัยหนึ่งในกรณีของ Emma Smith ช่องว่างคืออักขระที่ห้า การดับเบิลคลิกแล้วส่งสูตรลงไปจะแสดงให้เราเห็นว่าพื้นที่ว่างสำหรับพนักงานคนอื่นๆ ทั้งหมดอยู่ที่ใด สำหรับนิโคล โรเบิร์ตส์ ช่องว่างคืออักขระตัวที่เจ็ดในสตริงข้อความตามที่แสดง
5) ตอนนี้เราสามารถใช้ฟังก์ชัน LEFT เพื่อแยกชื่อได้ เนื่องจากเราสามารถแยกอักขระทั้งหมด ทางซ้ายของช่องว่างเพื่อให้ได้ชื่อตามพื้นที่ที่ว่างได้
ดังนั้น เราต้องกำหนดให้ฟังก์ชัน LEFT เป็นสตริงข้อความป้อนเข้า จากนั้นจึงระบุตัวเลขของอักขระในสตริงข้อความจากด้านซ้ายที่เราต้องการแยก
ดังนั้นเราจึงเลือกเซลล์ A5 เป็นสตริงข้อความ จากนั้นเพื่อค้นหาจำนวนอักขระที่จะแยกจากด้านซ้ายเพื่อให้ได้ชื่อเท่านั้น เราใช้ฟังก์ชัน SEARCH และลบ 1 เพื่อรับ:
=ซ้าย (A5, ค้นหา (” “, A5, 1) -1)
เราลบ 1 เพราะเราไม่ต้องการเว้นวรรค
6) เมื่อเรากด CTRL-ENTER แล้วดับเบิลคลิกที่สูตรแล้วส่งลงไป เราดึงชื่อทั้งหมดออกมาตามที่แสดง
7) ขณะนี้เราสามารถคัดลอกสูตรโดยกด CTRL-C สองครั้ง เพื่อเปิดคลิปบอร์ด
8) ตอนนี้เรามีสูตรที่เก็บไว้เพื่อใช้ในภายหลัง ดังนั้นเราจึงสามารถเคลียร์คอลัมน์ B และดำเนินการแยกนามสกุลได้
9) เรารู้ว่าช่องว่างอยู่ที่ไหน และเนื่องจากเราใช้ฟังก์ชัน LEFT เพื่อแยกชื่อ เราจึงสามารถใช้ฟังก์ชัน RIGHT เพื่อแยกนามสกุลได้ เรามาเริ่มสร้างสูตรสำหรับขั้นตอนการแยกนามสกุลกัน
10) เราจำเป็นต้องบอกฟังก์ชัน RIGHT ถึงจำนวนอักขระที่จะแยกจากด้านขวา เรารู้ตำแหน่งของพื้นที่ ดังนั้น เราจึงสามารถใช้ฟังก์ชัน LEN เพื่อบอกจำนวนอักขระทั้งหมดในสตริงข้อความ จากนั้นลบส่วนที่เหลือออกจากฟังก์ชัน SEARCH ซึ่งใช้เพื่อบอกเราในขั้นต้นว่าช่องว่างอยู่ที่ไหน
เราใช้สูตรต่อไปนี้:
=LEN (A5) – ค้นหา (” “, A5, 1))
สิ่งนี้ทำให้เรามีอักขระที่เหลือหลังจากเว้นวรรค
11) จากนั้นเราสามารถใช้ฟังก์ชัน RIGHT ร่วมกับสูตรด้านบนเพื่อแยกนามสกุล เนื่องจากตอนนี้เราทราบจำนวนอักขระจากด้านขวาเพื่อแยกเพื่อไปยังช่องว่าง
ดังนั้นเราจึงพิมพ์สูตรต่อไปนี้:
=ขวา (A5, LEN (A5) – ค้นหา (” “, A5, 1))
13) คัดลอกสูตรแล้วล้างเนื้อหาของเซลล์
14) ตอนนี้เราสามารถรวมทุกอย่างไว้ในสูตรเดียวได้ เนื่องจากเราได้แยกย่อยแต่ละขั้นตอนเพื่อแสดงตรรกะ และคัดลอกแต่ละส่วนไปยังคลิปบอร์ด
ดังนั้น หลังจากล้างคอลัมน์แล้ว ในเซลล์ B5 เราจะพิมพ์สูตร:
=ซ้าย (A5, ค้นหา (” “, A5, 1) – 1)&ขวา (A5, LEN(A5) – SEARCH(” “,A5,1))&”@mycompany.com”
15) เราดับเบิลคลิกแล้วส่งลงไป เพื่อเติมส่วนที่เหลือของคอลัมน์ด้วยที่อยู่อีเมล
คำแนะนำของผู้วิจารณ์ที่ยอดเยี่ยมเพื่อให้ได้ผลเช่นเดียวกัน:
ราหุล ซิงห์ สนับสนุนสูตรต่อไปนี้เพื่อให้ได้ผลเช่นเดียวกัน โดยใช้สูตรที่ซับซ้อนน้อยกว่ามาก:
ดังนั้นในเซลล์ B5 ให้พิมพ์สูตรต่อไปนี้:
=CONCATENATE(SUBSTITUTE(A5,” “, “”),”@mycompany.com”)
กาฬสินธุ์ สนับสนุนสูตรต่อไปนี้เพื่อให้ได้ผลเช่นเดียวกับสูตรข้อความเริ่มต้นในโพสต์ ซึ่งมีความซับซ้อนน้อยกว่ามาก:
=SUBSTITUTE(A5,” “,””)&”@mycompany.com”
อ่านเพิ่มเติม: วิธีใช้ Flash Fill ใน Excel เพื่อแยกข้อมูล (4 วิธีด่วน)
แฟ้มงาน
บทสรุป
Flash Fill เป็นคุณลักษณะใหม่ที่มีประโยชน์ใน Microsoft Excel 2013 และใหม่กว่า เป็นการป้อนอัตโนมัติที่ได้รับการปรับปรุงและสามารถประหยัดเวลาได้มาก อย่างไรก็ตาม สำหรับผู้ที่ใช้ Excel เวอร์ชันก่อนหน้าและในบางกรณีที่ Flash Fill ไม่ทำงาน สูตร TEXT จะช่วยได้ แม้ว่าการสร้างจะซับซ้อนกว่า แต่ก็ไม่มีตัวอย่างใดที่ซับซ้อนเกินกว่าจะรับมือไหว โปรดแสดงความคิดเห็นและแจ้งให้เราทราบหากคุณต้องการใช้ Flash Fill หรือสูตร TEXT เพื่อแยก/จัดรูปแบบข้อมูล พร้อมเหตุผล
บทความที่เกี่ยวข้อง
- วิธีปิด Flash Fill ใน Excel (2 วิธีง่ายๆ)
- [แก้ไขแล้ว!] เติมแฟลชไม่ทำงานใน Excel (5 เหตุผลพร้อมวิธีแก้ไข)
- Flash Fill ไม่รู้จักรูปแบบใน Excel (4 สาเหตุพร้อมการแก้ไข)
