Sử dụng Flash Fill
Đây là một câu hỏi thường được đặt ra, cụ thể là làm thế nào để trích xuất dữ liệu từ một cột sang một định dạng khác hoặc nhiều hơn một cột.
Trước tiên, chúng ta sẽ xem cách thực hiện việc này bằng cách sử dụng tính năng Flash Fill. Tô flash là một tính năng mới đã được thêm vào Microsoft Excel 2013 và các phiên bản mới hơn.
Trong ví dụ dưới đây, chúng ta có một danh sách tên và họ trong Cột A, Flash Fill luôn cần một cột nguồn, để hoạt động, trong trường hợp này là Cột A, chứa tên nhân viên. Flash Fill thường được sử dụng trong các ví dụ để tạo địa chỉ email hoặc các loại dữ liệu lặp lại theo công thức khác. Công thức văn bản cũng được sử dụng rộng rãi cho câu hỏi tạo email. Có rất nhiều công thức văn bản mà người ta có thể sử dụng, từ phức tạp hơn đến đơn giản hơn. Trong bài viết này, chúng tôi có một ví dụ tạo email điền vào flash, một ví dụ tạo email công thức văn bản và chúng tôi đã cập nhật nó với các đề xuất công thức văn bản của người nhận xét.
1) Chúng tôi muốn điền vào Cột B, với địa chỉ email của các nhân viên. Cách địa chỉ email được tạo bởi CNTT nội bộ của công ty, đó là tên và họ của nhân viên được kết hợp với tên miền của công ty để tạo ra địa chỉ email.
2) Trong Ô B5, chúng ta phải cung cấp Flash Fill, một ví dụ về những gì chúng ta muốn đầu tiên và Flash Fill sau đó sẽ sử dụng nó làm mẫu để điền phần còn lại của cột. Vì vậy, chúng tôi nhập văn bản:
Sau đó, chúng tôi nhấn CTRL-ENTER.
3) Sau khi nhấn CTRL-ENTER, Excel sẽ tự động thay đổi văn bản thành siêu kết nối.
4) Sau đó, chúng tôi phải đánh dấu toàn bộ phạm vi B5:B15 mà chúng tôi muốn điền địa chỉ email, sử dụng Flash Fill và nhấn CTRL-E, là phím tắt cho Flash Fill hoặc với phạm vi được đánh dấu, hãy chuyển đến Dữ liệu và nhấp vào Flash Fill như được hiển thị.
5) Phần còn lại của dải ô chứa các địa chỉ email cần thiết.
Đọc thêm: Tách văn bản trong Excel bằng cách sử dụng Flash Fill
Sử dụng công thức TEXT
Tất cả sẽ không bị mất nếu bạn có phiên bản Microsoft Excel cũ hơn, vì chúng ta có thể làm điều tương tự với các công thức TEXT. Tuy nhiên, sử dụng công thức TEXT hơi phức tạp hơn trong trường hợp cụ thể này.
1) Đầu tiên, chúng ta phải xem văn bản trong Cột A, để tìm ra hàm TEXT nào sẽ sử dụng trong công thức phức tạp của chúng ta.
2) Chúng tôi thấy rằng chúng tôi có tên, tiếp theo là dấu cách và sau đó là họ.
3) Chúng tôi sẽ xây dựng công thức này từ đầu.
Điều đầu tiên, chúng tôi làm là sử dụng hàm SEARCH như được hiển thị để tìm không gian và trong Ô B5, chúng tôi nhập:
=TÌM KIẾM (”“, A5, 1)
Nhấn CTRL-ENTER.
4) Sau khi nhấn CTRL-ENTER, chúng ta nhận được 5, nói cách khác trong trường hợp của Emma Smith, khoảng trắng là ký tự thứ năm. Nhấp đúp và gửi công thức xuống cho chúng tôi biết vị trí không gian xảy ra cho tất cả các nhân viên khác. Đối với Nicole Roberts, khoảng trắng là ký tự thứ bảy trong chuỗi văn bản như được hiển thị.
5) Bây giờ chúng ta có thể sử dụng hàm LEFT để trích xuất tên, vì dựa vào vị trí của khoảng trắng, chúng ta có thể trích xuất tất cả các ký tự, bên trái của khoảng trắng để lấy tên.
Vì vậy, chúng ta cần cung cấp cho hàm LEFT chuỗi văn bản đầu vào, sau đó là số ký tự trong chuỗi văn bản từ bên trái mà chúng ta muốn trích xuất.
Vì vậy, chúng tôi chọn Ô A5, làm chuỗi văn bản, sau đó để tìm số ký tự cần trích xuất từ bên trái để chỉ lấy tên, chúng tôi sử dụng hàm SEARCH và trừ đi 1 để được:
=LEFT (A5, TÌM KIẾM (”“, A5, 1) -1)
Chúng tôi trừ đi 1 vì chúng tôi không muốn bao gồm khoảng trắng.
6) Sau khi chúng tôi nhấn CTRL-ENTER và nhấp đúp vào công thức và gửi xuống. Chúng tôi nhận được tất cả các tên đầu tiên được trích xuất như được hiển thị.
7) Bây giờ chúng ta có thể sao chép công thức bằng cách nhấn CTRL-C hai lần, để mở Bảng tạm.
8) Bây giờ chúng tôi có công thức được lưu trữ để sử dụng sau này. Vì vậy, chúng tôi có thể xóa Cột B và làm việc để trích xuất họ.
9) Chúng tôi biết không gian ở đâu và vì chúng tôi đã sử dụng hàm LEFT để trích xuất tên, chúng tôi có thể sử dụng hàm RIGHT để trích xuất họ. Vì vậy, hãy bắt đầu xây dựng công thức cho quá trình trích xuất họ này.
10) Chúng ta cần cho hàm RIGHT biết số ký tự cần trích xuất từ bên phải. Chúng tôi biết vị trí của không gian. Do đó, chúng ta có thể sử dụng hàm LEN để cho chúng ta biết tổng số ký tự trong chuỗi văn bản và sau đó trừ đi phần còn lại từ hàm SEARCH, được sử dụng để cho chúng ta biết ban đầu không gian ở đâu.
Chúng tôi sử dụng công thức sau:
=LEN (A5) - TÌM KIẾM (”“, A5, 1))
Điều này cung cấp cho chúng tôi các ký tự còn lại sau dấu cách.
11) Sau đó, chúng ta có thể sử dụng hàm RIGHT kết hợp với công thức trên để trích xuất họ, vì bây giờ chúng ta đã biết số ký tự từ bên phải cần trích xuất để đến khoảng trắng.
Vì vậy, chúng tôi nhập công thức sau:
=RIGHT (A5, LEN (A5) - TÌM KIẾM (”“, A5, 1))
13) Sao chép công thức và sau đó xóa nội dung của ô.
14) Giờ đây, chúng tôi có thể tập hợp mọi thứ lại với nhau trong một công thức lớn, vì chúng tôi đã chia nhỏ từng bước để hiển thị logic và sao chép từng phần vào Clipboard.
Vì vậy, sau khi xóa cột, trong Ô B5, chúng tôi nhập công thức:
=LEFT (A5, TÌM KIẾM (”“, A5, 1) - 1) &PHẢI (A5, LEN (A5) - TÌM KIẾM (”“, A5,1)) &”@ mycompany.com”
15) Chúng tôi nhấp đúp và gửi xuống, để điền địa chỉ email vào phần còn lại của cột.
Đề xuất của nhà bình luận xuất sắc để đạt được hiệu quả tương tự:
Rahul Singh đã đóng góp công thức sau để đạt được hiệu quả tương tự, sử dụng một công thức ít phức tạp hơn nhiều:
Vì vậy, trong ô B5 nhập công thức sau:
=CONCATENATE (SUBSTITUTE (A5, ”“, “”), ”@ mycompany.com”)
Karthik đã đóng góp công thức sau để đạt được hiệu quả tương tự như công thức văn bản ban đầu trong bài đăng, nó cũng ít phức tạp hơn nhiều:
=SUBSTITUTE (A5, ”“, ””) &”@ mycompany.com”
Đọc thêm: Cách sử dụng Flash Fill trong Excel để chia nhỏ dữ liệu (4 cách nhanh)
Tệp làm việc
Kết luận
Flash Fill là một tính năng mới tiện dụng trong Microsoft Excel 2013 trở lên. Đây là tính năng Tự động điền nâng cao và có thể khá tiết kiệm thời gian. Tuy nhiên, đối với những người sử dụng phiên bản Excel cũ hơn và trong một số trường hợp nhất định khi Flash Fill không hoạt động, các công thức TEXT sẽ giúp bạn. Mặc dù chúng phức tạp hơn để tạo ra, nhưng không có ví dụ nào quá phức tạp để chúng giải quyết. Vui lòng bình luận và cho chúng tôi biết bạn thích sử dụng công thức Flash Fill hay TEXT để trích xuất / định dạng dữ liệu và tại sao.
Các bài viết liên quan
- Cách Tắt Flash Fill trong Excel (2 Phương pháp Dễ dàng)
- [Đã giải quyết!] Flash Fill không hoạt động trong Excel (5 lý do có giải pháp)
- Flash Fill không nhận dạng được mẫu trong Excel (4 nguyên nhân với các bản sửa lỗi)