این بسیار ساده و در عین حال بسیار قدرتمند است.
هنگام پردازش دسته های بزرگ داده، تعداد کمی از توابع اکسل به اندازه تابع SUBSTITUTE همه کاره هستند. متأسفانه، اغلب به طرز غم انگیزی به نفع راه حل های بسیار پیچیده تر برای مسائلی که اغلب ساده هستند، نادیده گرفته می شود.
عملکرد SUBSTITUTE راهی سریع و تمیز برای پاکسازی داده ها، افزودن جزئیات به گزارش ها و ایجاد ارتباطات ساده است. تسلط بر این تابع و درک استفاده از آن، آن را به ابزاری غیرقابل جایگزین و آسان برای تجزیه و تحلیل مجموعه داده های بزرگ تبدیل می کند.
1. ایجاد ارتباطات فرم با تابع SUBSTITUTE اکسل
عملکرد SUBSTITUTE اکسل تعویض سریع و آسان نشانگرهای از پیش تعیین شده را در تکه ای از متن از پیش فرمت شده آسان می کند. این به کاربران اجازه می دهد تا ارتباطات فرم را به راحتی ایجاد کنند و اطلاعاتی را که با هر تکرار تغییر می کند، مبادله کنند.
می توانید یک الگو را در سلولی در صفحه گسترده قرار دهید، سپس لیستی از گیرندگان را در صفحه گسترده دیگری وارد کنید. سپس، توابع SUBSTITUTE را در یک سلول دیگر زنجیره بزنید تا جزئیات هر گیرنده را اضافه کنید.
توجه داشته باشید که می توانید بیش از یک SUBSTITUTE در داخل یکدیگر قرار دهید:
=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)
اگر تعداد زیادی فیلد در حال تغییر است، می توانید کل حرف را به پاراگراف تقسیم کنید و آنها را یکی یکی تغییر دهید. در سلول آخر سطر، تمام این پاراگراف ها را در یک حرف به هم متصل کنید.
ایمیل‌های تولید شده به‌طور خودکار روشی فوق‌العاده برای دسترسی سریع و آسان به فهرستی از مشتریان، ایجاد مکاتبات بازاریابی شخصی، یا تولید نامه‌های پوششی سفارشی‌شده هستند.
می‌توانید پیام‌های طولانی‌تر را به پاراگراف‌ها یا جملات تکی تقسیم کنید تا از فرمول‌های بسیار پیچیده SUBSTITUTE اجتناب کنید. حتی می‌توانید از شرط‌ها برای سفارشی‌سازی بیشتر استفاده کنید، مانند جابه‌جایی در فهرست‌های مختلف مهارت‌ها بر اساس نوع شغلی که برای آن درخواست می‌دهید.
2. استفاده از تابع SUBSTITUTE اکسل برای تولید گزارش
تابع SUBSTITUTE نه تنها برای درج اطلاعات اولیه در فیلدهای از پیش فرمت شده مفید است، بلکه می تواند محاسبات پیچیده را در گزارش های از پیش ساخته شده نیز تزریق کند. محاسبات تزریقی به شما این امکان را می دهد که از Excel برای ساخت داشبوردی استفاده کنید که اطلاعات بصری را ارائه می دهد و مشاهدات سفارشی را بر اساس داده ها ارائه می دهد.
ترکیب SUBSTITUTE برای از پیش تولید بلوک های متن و شرطی مانند تابع IF به شما امکان می دهد گزارش های دقیق تری ایجاد کنید.
در مثال بالا، می بینید که دو پاراگراف مجزا با استفاده از برخی اطلاعات از یک گزارش در حال ایجاد هستند. سپس یک تابع شرطی می تواند تعیین کند که کدام یک از دو پاراگراف برای نمایش به خوانندگان گزارش مناسب است.
پاراگراف اول با استفاده از این فرمول ایجاد می شود:
=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 & "%"))
جفت کردن چندین تابع SUBSTITUTE زنجیره ای با شرطی ها می تواند به شما در طراحی داشبوردها و گزارش های دقیق تر کمک کند. محاسبات حتی می توانند مستقیماً در توابع SUBSTITUTE انجام شوند تا اطمینان حاصل شود که شما فقط مرتبط ترین اطلاعات را به خوانندگان خود ارائه می دهید.
3. پاکسازی داده های اکسل برای ذخیره سازی با استفاده از تابع SUBSTITUTE اکسل
هنگامی که صحبت از ذخیره سازی طولانی مدت داده می شود، ملاحظات کمی به اندازه پاکسازی ضروری است. حفظ داده های زباله از آلودگی یا آسیب رساندن به مجموعه داده ها برای جلوگیری از خطاهای آینده که نمی توانید درک کنید ضروری است.
این می تواند به معنای هر چیزی باشد، از پاک کردن کاراکترهای غیرقانونی از یک مجموعه داده تا اطمینان از اینکه کلمات کلیدی رزرو شده به جای دستور، به عنوان متن در نظر گرفته می شوند. مهم نیست که چه کاری باید انجام دهید تا پایگاه داده خود را در برابر تهدیداتی مانند تزریق SQL و داده های قابل استفاده خود ایمن نگه دارید، تابع SUBSTITUTE اکسل می تواند کمک کند.
برای مثال، می‌توانیم فهرستی از پاسخ‌های کاربر به یک نظرسنجی را برای درج در پایگاه داده SQL آماده کنیم. داده‌های تایید نشده می‌توانند شامل انواع مختلفی از نویسه‌های غیرقانونی باشند که در صورت حذف نشدن، ممکن است مشکلاتی را ایجاد کنند:
=SUBSTITUTE(A2:A4, "'", '\'")
تابع فوق یک مرحله ساده و در عین حال ضروری در آماده سازی داده ها برای ذخیره سازی را نشان می دهد. این تابع هر پاسخ در ستون B را برای یک نقل قول بررسی می کند. در صورت وجود، شخصیت قبلاً دارای یک علامت بک اسلش است. اضافه کردن بک اسلش کمک می کند تا اطمینان حاصل شود که کاراکترهای رزرو شده با پرس و جو مورد استفاده برای درج داده ها در DB تداخل نخواهند کرد.
اگر کاراکتر یا رشته ای که در جستجوی آن هستید باید به طور کلی حذف شود، تابع SUBSTITUTE همچنان مفید است:
=SUBSTITUTE(B2:B20, "$", "")
استفاده از یک رشته خالی برای آرگومان سوم، کاراکترهای مشخص شده را به طور کامل از سلول حذف می کند. با عملکرد SUBSTITUTE، می توانید متن خاصی را حذف کنید تا نویسه های بالقوه مضر را به طور کامل از داده های خود پاک کنید.
4. اطمینان از قالب بندی مناسب داده ها با عملکرد SUBSTITUTE اکسل
یکی از مشکلات احتمالی هنگام جمع آوری داده ها، امکان دریافت اطلاعات نادرست است. هنگامی که کاربران اطلاعات را در یک فرم بدون پاسخ وارد می کنند، می تواند منجر به اطلاعات گزارش نادرست شود. داده‌های نادرست می‌توانند به سادگی یک غلط املایی معمولی یا به پیچیدگی معادله‌ای باشند که فرمت اشتباهی دارند.
تابع SUBSTITUTE اکسل راهی عالی برای جلوگیری از خطاهای ساده از ایجاد گزارش نادرست داده است. هنگام تجزیه و تحلیل نتایج تولید شده توسط یک منبع خاص، حذف نادرستی های شناخته شده می تواند به تولید نتایج دقیق تر کمک کند:
=SUBSTITUTE(A2, "CompanyName Inc.", "Company Name Inc.")
مثال بالا نشان می دهد که چگونه یک تابع SUBSTITUTE می تواند یک اشتباه تایپی ساده را از لیست بررسی های محصول حذف کند. جایگزینی یک غلط املایی رایج می تواند تضمین کند که هنگام بررسی داده ها در تاریخ بعدی، ذکر نام تجاری یا شرکت نادیده گرفته نمی شود.
استفاده از تابع SUBSTITUTE برای جایگزینی خطاهای بالقوه شناخته شده با اطلاعات صحیح می تواند مشکلاتی را در مجموعه داده شما قبل از تبدیل شدن به مشکل ایجاد کند و در صورت تمایل به استفاده از صفحه گسترده خود برای نمایه سازی داده ها در آینده به شما کمک می کند از اشتباهات جلوگیری کنید.
عملکرد اکسل SUBSTITUTE: ساده و در عین حال کاربردی
در حالی که تابع SUBSTITUTE ممکن است نسبتا ساده باشد، یک فرمول بسیار مفید است. جایگزینی متن در بلوک‌های از پیش فرمت‌شده، کمک به تولید گزارش‌ها، پاکسازی داده‌ها، و حذف اطلاعات مشکل احتمالی از مجموعه داده‌ها، سطح را خراش می‌دهد.
استفاده از محاسبات در SUBSTITUTE و جفت کردن آنها با فرمول های شرطی به گسترش قابلیت های آن کمک می کند. می‌تواند داشبوردها و گزارش‌ها را از ارائه داده‌های بصری تا ارائه تجزیه و تحلیل‌های پیچیده به‌طور خودکار گسترش دهد.
ترکیب این تابع با فرمول‌ها و تکنیک‌های دیگر می‌تواند داشبوردی را در مقابل ابزارهای پایگاه داده اختصاصی ایجاد کند. بنابراین، اگر شما یک تحلیلگر داده، مهندس یا کار با مجموعه داده های عظیم هستید، دانستن و درک تابع SUBSTITUTE اکسل ضروری است.