Khi xử lý các lô dữ liệu lớn, một số hàm Excel linh hoạt như hàm SUBSTITUTE. Thật không may, nó thường bị bỏ qua một cách bi thảm để ủng hộ các giải pháp phức tạp hơn nhiều cho những vấn đề thường là đơn giản.
Hàm SUBSTITUTE là một cách nhanh chóng và rõ ràng để làm sạch dữ liệu, thêm chi tiết vào báo cáo và tạo các giao tiếp biểu mẫu đơn giản. Nắm vững chức năng này và hiểu cách sử dụng nó làm cho nó trở thành một công cụ dễ sử dụng và không thể thay thế để phân tích các tập dữ liệu lớn.
1. Tạo liên lạc biểu mẫu với chức năng SUBSTITUTE của Excel
Hàm SUBSTITUTE của Excel giúp bạn dễ dàng hoán đổi các điểm đánh dấu được chỉ định trước trên một đoạn văn bản được định dạng sẵn một cách dễ dàng và nhanh chóng. Điều này cho phép người dùng tạo các giao tiếp biểu mẫu một cách dễ dàng, hoán đổi thông tin thay đổi sau mỗi lần lặp lại.
Bạn có thể đặt mẫu vào một ô trên bảng tính, sau đó nhập danh sách người nhận trên một bảng tính khác. Tiếp theo, xâu chuỗi các hàm SUBSTITUTE trên một ô khác để thêm chi tiết cho từng người nhận.
Lưu ý rằng bạn có thể lồng nhiều hơn một SUBSTITUTE vào nhau:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Template!$A$1, "{ cardlastfour }", F2), "{ billingtotal }", E2), "{ cardtype }", D2), "{ enddate }", TEXT(C2, "mmm dd, yyyy")), "{ lname }", B2), "{ fname }", A2)
Nếu bạn có quá nhiều trường thay đổi, bạn có thể chia toàn bộ bức thư thành các đoạn văn và thay đổi từng đoạn một. Trong ô cuối cùng của hàng, nối tất cả các đoạn này thành một chữ cái.
Email được tạo tự động là một cách tuyệt vời để nhanh chóng và dễ dàng tiếp cận danh sách khách hàng, tạo thư tiếp thị được cá nhân hóa hoặc tạo thư xin việc tùy chỉnh.
Bạn có thể chia các thư dài hơn thành các đoạn văn hoặc các câu đơn lẻ để tránh các công thức SUBSTITUTE quá phức tạp. Bạn thậm chí có thể sử dụng điều kiện để tùy chỉnh bổ sung, chẳng hạn như hoán đổi các danh sách kỹ năng khác nhau trên thư xin việc dựa trên loại công việc bạn đang ứng tuyển.
2. Sử dụng hàm SUBSTITUTE của Excel để tạo báo cáo
Hàm SUBSTITUTE không chỉ tiện dụng để chèn thông tin cơ bản vào các trường được định dạng trước mà còn có thể đưa các phép tính phức tạp vào các báo cáo được tạo sẵn. Tính toán tiêm cho phép bạn sử dụng Excel để xây dựng bảng điều khiển cung cấp thông tin trực quan và cung cấp các quan sát tùy chỉnh dựa trên dữ liệu.
Kết hợp SUBSTITUTE để tạo trước các khối văn bản và điều kiện như hàm IF cho phép bạn tạo các báo cáo chi tiết hơn.
Trong ví dụ trên, bạn có thể thấy rằng hai đoạn riêng biệt đang được tạo bằng cách sử dụng một số thông tin từ một báo cáo. Sau đó, một hàm điều kiện có thể xác định đoạn nào trong hai đoạn phù hợp để hiển thị cho người đọc báo cáo.
Đoạn đầu tiên được tạo bằng công thức này:
=IF(A2>B2, SUBSTITUTE(Templates!$A$3, "{ growthpercent }", (ROUND(B2 / A2, 2) * 100) - 100 & "%"), SUBSTITUTE(Templates!$A$4, "{ decreasepercent }", 100 - (ROUND(A2 / B2, 2)) * 100 & "%"))
Việc ghép nối nhiều hàm SUBSTITUTE được xâu chuỗi với các điều kiện có thể giúp bạn thiết kế các báo cáo và trang tổng quan chuyên sâu hơn. Tính toán thậm chí có thể được thực hiện trực tiếp trong hàm SUBSTITUTE để đảm bảo bạn chỉ trình bày thông tin phù hợp nhất cho độc giả của mình.
3. Vệ sinh dữ liệu Excel để lưu trữ bằng hàm SUBSTITUTE của Excel
Khi nói đến việc lưu trữ dữ liệu dài hạn, một vài cân nhắc cũng quan trọng như vệ sinh. Giữ cho dữ liệu rác không gây ô nhiễm hoặc làm hỏng tập dữ liệu là điều cần thiết để ngăn ngừa các lỗi trong tương lai mà bạn không thể hiểu được.
Điều này có thể có nghĩa là bất cứ điều gì từ việc xóa các ký tự không hợp lệ khỏi tập dữ liệu để đảm bảo các từ khóa dành riêng được coi là văn bản chứ không phải lệnh. Bất kể bạn cần làm gì để giữ an toàn cho cơ sở dữ liệu của mình khỏi các mối đe dọa như SQL injection và dữ liệu của bạn có thể sử dụng được, thì hàm SUBSTITUTE của Excel có thể trợ giúp.
Ví dụ: chúng ta có thể chuẩn bị danh sách phản hồi của người dùng đối với khảo sát để chèn vào cơ sở dữ liệu SQL. Dữ liệu không hợp lệ có thể chứa nhiều ký tự không hợp lệ có thể gây ra sự cố về sau nếu không bị xóa:
=SUBSTITUTE(A2:A4, "'", ''")
Hàm trên thể hiện một bước đơn giản nhưng cần thiết trong việc chuẩn bị dữ liệu để lưu trữ. Hàm này kiểm tra từng phản hồi trong cột B để tìm một trích dẫn. Nếu có, ký tự có dấu gạch chéo ngược được thêm vào trước đó. Thêm dấu gạch chéo ngược giúp đảm bảo rằng các ký tự dành riêng sẽ không can thiệp vào truy vấn được sử dụng để chèn dữ liệu vào DB.
Nếu ký tự hoặc chuỗi bạn đang tìm kiếm cần phải xóa hoàn toàn, hàm SUBSTITUTE vẫn hữu ích:
=SUBSTITUTE(B2:B20, "$", "")
Sử dụng một chuỗi rỗng cho đối số thứ ba sẽ loại bỏ hoàn toàn các ký tự được chỉ định khỏi ô. Với chức năng SUBSTITUTE, bạn có thể xóa văn bản cụ thể để xóa hoàn toàn các ký tự có khả năng gây hại khỏi dữ liệu của mình.
4. Đảm bảo định dạng dữ liệu phù hợp với chức năng SUBSTITUTE của Excel
Một vấn đề tiềm ẩn khi thu thập dữ liệu là khả năng nhận được thông tin không đúng định dạng. Khi người dùng nhập thông tin vào biểu mẫu phản hồi miễn phí, điều đó có thể dẫn đến thông tin báo cáo không chính xác. Dữ liệu không chính xác có thể đơn giản như một lỗi chính tả phổ biến hoặc phức tạp như một phương trình có định dạng sai.
Hàm SUBSTITUTE của Excel là cách hoàn hảo để ngăn các lỗi đơn giản gây ra báo cáo dữ liệu không chính xác. Khi phân tích kết quả do một nguồn cụ thể tạo ra, việc loại bỏ những điểm không chính xác đã biết có thể giúp tạo ra kết quả chính xác hơn:
=SUBSTITUTE(A2, "CompanyName Inc.", "Company Name Inc.")
Ví dụ trên cho thấy cách hàm SUBSTITUTE có thể xóa một lỗi đánh máy đơn giản khỏi danh sách đánh giá sản phẩm. Việc thay thế một lỗi chính tả phổ biến có thể đảm bảo rằng việc đề cập đến thương hiệu hoặc công ty không bị bỏ qua khi kiểm tra dữ liệu sau này.
Việc sử dụng hàm SUBSTITUTE để thay thế các lỗi tiềm ẩn đã biết bằng thông tin chính xác có thể phát hiện các vấn đề trong tập dữ liệu của bạn trước khi chúng trở thành vấn đề, giúp bạn tránh mắc lỗi nếu muốn sử dụng bảng tính của mình để lập hồ sơ dữ liệu trong tương lai.
Hàm SUBSTITUTE của Excel: Đơn giản nhưng đầy đủ chức năng
Mặc dù hàm SUBSTITUTE có thể tương đối đơn giản, nhưng đây là một công thức cực kỳ hữu ích. Việc thay thế văn bản trong các khối được định dạng sẵn, giúp tạo báo cáo, làm sạch dữ liệu và xóa thông tin sự cố tiềm ẩn khỏi tập dữ liệu chỉ là bề nổi.
Sử dụng các phép tính trong SUBSTITUTE và ghép nối chúng với các công thức có điều kiện giúp mở rộng khả năng của nó. Nó có thể mở rộng bảng điều khiển và báo cáo từ việc trình bày dữ liệu trực quan đến cung cấp các phân tích phức tạp được tạo tự động.
Kết hợp chức năng này với các công thức và kỹ thuật khác có thể tạo ra một bảng điều khiển cạnh tranh với các công cụ cơ sở dữ liệu chuyên dụng. Vì vậy, biết và hiểu hàm SUBSTITUTE của Excel là bắt buộc nếu bạn là nhà phân tích dữ liệu, kỹ sư hoặc làm việc với các tập dữ liệu lớn.
