نحوه پرکردن اتوماتیک خانه ها در اکسل به زبان ساده

ویژگی های autofill (پر کردن خودکار) اکسل کارآمدترین راه ها برای صرفه جویی در زمان را به کاربران ارائه می دهند. خیلی از کاربران نمی دانند که بیشتر کارهایی که به صورت دستی انجام می دهند می تواند خودکارسازی شود. در این مقاله، پنج راه کار بسیار کارآمد برای خودکارسازی autofill ستون ها را آموزش می دهیم. به عنوان مثال، شاید تصمیم داشته باشید یک فرمول را برای تمام خانه های سطرهای دوم و سوم لیستی که درست کرده اید اعمال کنید. شاید تصمیم دارید تا تمام خانه های خالی یک صفحه اکسل را پر کنید. در این جا، درباره پنج راه کار بسیار کارآمد برای خودکارسازی autofill ستون های اکسل صحبت می کنیم.

Flash Fill در اکسل

پرکردن سریع سلول Flash Fill یکی از امکانات بسیار پرکاربرد اکسل محسوب می شود. ابزار Flash Fill در اکسل برای انجام چندین کار، برنامه ریزی شده است بطوری که انجام کارهای طولانی را برایتان سرعت می بخشد و اجرای کارهای روزمره را با کاربرگ های اکسل، لذت بخش می کند. از طرفی کار کردن با ابزار Flash Fill در اکسل، بسیار ساده و آسان است و اکسل فقط از شما می خواهد الگویی که داده ها دارند را با چند عضو از دنباله مقادیر، معرفی کنید تا خودش بقیه اعضای دنباله را تشخیص داده و ایجاد کند.

کاربرد Flash Fill در اکسل چیست؟

پرکردن سریع یا Flash Fill در اکسل ابزاری است که از نسخه ۲۰۱۳ به بعد در اکسل گنجانده شده است. این ابزار، اطلاعاتی که در سلول ها وارد کرده اید، تجزیه و تحلیل کرده و سعی می کند الگویی برای دسته ای از داده ها پیدا کند. به این ترتیب می توانید از Flash Fill در اکسل بخواهید که بقیه مقادیر یک دنباله را برایتان پیدا کرده یا حدس بزند.

البته در اکسل ۲۰۱۶ و ۲۰۱۹ امکانات بیشتری نیز به ابزار پرکردن سریع سلول فلش فیل اضافه شده است. بطوری که امروزه به کمک تکنیک های یادگیری ماشین (Machine Learning) دقت پیش بینی کارهایی که Flash Fill انجام می دهد بیشتر شده و تقریبا با نظر کاربر مطابقت دارد. در هر بار استفاده از فلش فیل در اکسل، میلیون ها برنامه کوچک با یکدیگر ترکیب شده و داده هایی را برای هم ارسال می کنند تا بتوانند بهترین بازده را داشته باشند. همه این فعالیت ها در زمان بسیار کوتاهی (در حدود میلی ثانیه) صورت پذیرفته، بطوری که کاربر از وجود آن ها اطلاع پیدا نمی کند. اما چگونه از پرکردن سریع سلول ها با ابزار Flash Fill در اکسل استفاده کنیم؟

برای دسترسی به این ابزار، می توانید از برگه Data، قسمت Data Tools را انتخاب کنید. نماد Flash Fill در این قسمت دیده می شود. در اکسل برای اجرای دستور Flash Fill ، کلید میانبری هم وجود دارد. اگر از کلیدهای ترکیبی Ctrl+E استفاده کنید، دستور Flash Fill اجرا خواهد شد.

نکته: البته برای اجرای این دستور می توانید از برگه Home و در قسمت Editing، گزینه Fill را انتخاب کرده و دستور Flash Fill را اجرا کنید.

چگونه از Flash Fill استفاده کنیم؟

در اغلب موارد، با استفاه از الگویی که در سلول ها ایجاد کرده اید، ابزار پرکردن سریع، عمل کرده و منظور شما را درک می کند. برای شناخت بهتر موضوع به بررسی چند مثال می پردازیم که بطور خودکار و حتی بدون استفاده از دستور Flash Fill، اکسل قادر به ساختن الگوی مورد نظر شما می شود.

مثال ۱: تفکیک مقادیر متنی

فرض کنید در یک کاربرگ اکسل، در ستون A، اسامی شهرستان ها و استانشان نوشته شده. می خواهیم نام استان را از نام شهرستان ها جدا کرده و آن را در یک ستون جداگانه مثلا B، قرار دهیم. به تصویر زیر که چنین وضعیتی را نشان داده، دقت کنید.

نکته: همانطور که دیده شد، احتیاجی به اجرای دستور پرکردن سریع نیز نبود و این کار به طور خودکار صورت گرفت.

برای انجام چنین کارهایی مراحل زیر را باید طی کرد:

1. در مجاورت ستون داده های اصلی یک ستون ایجاد کنید.
2. در سلول اول ستون جدید، مقداری که براساس ستون اصلی باید استخراج شود را بنویسید.
3. در سطر دوم ستون ایجاد شده، شروع به ورود مقدار متناظر با سطر دوم ستون اول کنید.
4. در صورتی که لیست مقادیر استخراج شده از فهرست ستون اول، ظاهر و نیاز شما به درستی تشخیص داده شد، کلید Enter را بزنید.
5. در غیر اینصورت، به وارد کردن ادامه متن مورد نظرتان ادامه دهید تا اکسل متوجه الگو شود.
6. اگر باز هم فلش فیل در اکسل، به دلخواه شما رفتار نکرد، می توان نتیجه گرفت که الگوی درخواستی شما پیچیده بوده و نرم افزار قادر به درک آن نشده است.

نکته: اگر می خواهید نتیجه اجرای عملیات پرکردن سریع را لغو کنید، از دستور Undo یا کلید میانبر Ctrl+Z استفاده کنید.

مثال ۲: استخراج مقادیر عددی از درون متن

این بار می خواهیم با اجرای دستور Flash Fill، عمل پرکردن سریع را انجام دهیم. فرض کنید کاربرگی تشکیل شده که در آن اسامی افراد و شماره کارمندی آن ها با هم به شکل نامرتبی ترکیب شده است. می خواهیم از میان این رشته ترکیب شده از متن و عدد، شماره کارمندی را استخراج کنیم.

باز هم به مانند مثال ۱، اقدام می کنیم و این بار بعد از وارد کردن شماره کارمندی نفر اول در ستون دوم (سلول B2)، به سلول B3 رفته و دکمه Flash Fill را از برگه Data کلیک می کنیم.

باز هم اکسل به شکل فوق العاده ای، هدف ما را تشخیص داد و بقیه سلول ها را با سرعت پر می کند.

نکته: به کمک علامت Flash Fill Options که در تصویر دیده می شود، می توانید اکسل را از نتیجه کار Flash Fill آگاه کنید. در صورتی که می خواهید نتیجه را برگردانید، دستور Undo Flash Fill را انتخاب کنید و در صورتی که از نتیجه کار رضایت دارید، گزینه Accept suggestions را کلیک کنید. این کار به افزایش دقت پرکردن سریع اکسل در مراحل بعدی کارتان، کمک می کند. به این ترتیب اکسل برای پر کردن سریع Flash Fill هوشمندتر خواهد شد.

مثال ۳: ترکیب و ادغام سلول های متنی

در این مثال می خواهیم عکس عملیات قبلی را انجام دهیم. به این معنی که با استفاده از فلش فیل در اکسل، می خواهیم محتویات چند سلول را یکدیگر ترکیب کنیم. با توجه به کاربرگ زیر، فرض کنید که باید ترکیب محتویات ستون اول تا سوم را در ستون چهارم، ظاهر کنیم. این بار هم به کمک معرفی مقدار اول در ستون چهار (D)، اکسل را با نحوه کار آشنا می کنیم. به این ترتیب اکسل قادر است بقیه مقادیر مربوط به ستون D را تشخیص داده و آن ها را ایجاد کند.

همانطور که دیده می شود، هر بخش از مشخصات کالا، با علامت نقطه در سلول اول (D2) از یکدیگر جدا شده اند. اکسل نیز همین الگو را درک کرده و سلول های مرتبط را با یکدیگر ادغام کرده و در کنار هم چیده است.

مثال ۴: پاک سازی داده ها

فاصله های اضافه در ابتدای عبارت های متنی، یکی از مشکلاتی است که بخصوص هنگام مرتب سازی (Sort) یا جستجوی مقادیر (Vlookup) در کاربرگ های اکسل بروز می کند. روش های مختلف و البته شاید پیچیده ای برای حذف این فواصل وجود داشته باشد، ولی استفاده از پرکردن سریع در اکسل، این کار را با سرعت و سادگی انجام می دهد.

همانطور که دیده می شود، اسامی شهرهایی که در ابتدای آن ها فاصله قرار گرفته است، در ستون «نام اصلاح شده»، بدون فاصله ظاهر شده اند. برای انجام این کار فقط کافی است که در سلول B2 مقدار صحیح «تهران» را بدون هیچ فاصله ای در ابتدا وارد کنید و سپس دکمه Flash Fill را بزنید. به این ترتیب بقیه سطرهای این ستون نیز شامل اسامی شهرها بدون هیچ فاصله ای در ابتدای سلول خواهند بود.

مثال ۵: قالب بندی سلول ها

گاهی، داده های درون کاربرگ دارای قالب نوشتاری یا ساختار مشخصی نیستند. برای مثال ممکن است بین نام و نام خانوادگی افراد هیچ علامت متمایز کننده ای وجود نداشته باشد. یا مثلا شماره تلفن ها را در نظر بگیرید. اگر بخواهیم کدهای شهری از شماره ها بوسیله علامتی، جدا شده و متمایز شوند، باز هم از ابزار کاربردی Flash Fill در اکسل می توان استفاده کرد. کاربرگی را در نظر بگیرید که شامل اسامی افراد است که بدون علامت جداکننده، ثبت شده اند. می خواهیم در ستون مجاور این اسامی به صورت «نام، نام خانوادگی» نوشته شوند.

همانطور که دیده می شود، به سرعت و راحتی، بین قسمت های نام و نام خانوادگی در ستون اسامی تفکیک شده، از علامت کاما «،» برای جداسازی استفاده شده است. این کار را بر اساس مثالی که برای شماره تلفن نیز گفته شد، در تصویر زیر انجام داده ایم.

نکته: توجه داشته باشید که برای نمایش صفر در ابتدای هر یک از این سلول های عددی، نوع قالب (Format) را به صورت متن (Text) در آورده ایم. در صورتی که این کار انجام نشود، صفر ابتدای اعداد توسط اکسل حذف می شود ولی با توجه به اینکه آن ها با صورت متنی، قالب بندی کرده ایم، صفر اول اعداد، نشان داده شده است.

از پرکردن سریع برای تغییر قالب بندی سلول های با محتویات تاریخ نیز می توان استفاده کرد. در کاربرگ زیر، تاریخ هایی با قالب عددی نشان داده شده که می خواهیم آن ها را با قالبی به صورت اسامی ماه ها درآوریم. کافی است که برای تاریخ اول، تاریخ با اسامی ماه را وارد کنیم تا بقیه موارد توسط Flash Fill در اکسل صورت گیرد.

مثال ۶: جایگزین کردن متن

جایگزین کردن قسمتی از یک متن، یکی از کارهایی است اغلب در اکسل انجام می دهیم. برای مثال در نظر بگیرید که کد ملی افراد در یک ستون از کاربرگ اکسل ثبت شده و می خواهیم برای حفظ حریم خصوصی، قسمتی از این کد را با علامت ضربدر (X) جایگزین کنیم. در اینجا هم باز می توانیم از فلش فیل در اکسل استفاده کنیم. با معرفی الگو برای مقدار اول از کد ملی، بقیه سطرها نیز مطابق با آن تکمیل خواهند شد.

مثال ۷: عملیات پیچیده

فرض کنید به واسطه اسامی افراد و کارکنان یک شرکت می خواهید، آدرس پست الکترونیک تولید کنید. به این منظور نام فرد را به همراه نام خانوادگی با یک علامت نقطه جدا کرده و سپس علامت @ به همراه نام حوزه که نام شرکت است به کار گرفته و آدرس پست الکترونیک ایجاد می کنیم. گامی های مربوط به استفاده از پرکردن سریع Flash Fill را نیز در اینجا به کار می بریم.

به این ترتیب بدون آنکه از فرمول نویسی استفاده کرده باشید، آدرس پست الکترونیک کارکنان را به صورت استاندارد تولید کرده اید. همانطور که توجه دارید، اکسل با معرفی یک مثال برای نفر اول، توانست شیوه ایجاد آدرس های پست الکترونیک را حدس بزند و برای بقیه کارکنان نیز آن را ایجاد کند.

حتی می توانید به جای استفاده از نام کامل افراد فقط حرف اول به همراه نام خانوادگی برای تولید آدرس پست الکترونیک استفاده کنید. به کاربرگ زیر توجه کنید.

=LOWER(LEFT(B2,1))&”.”&LOWER(A2)&”@”fardars.org”

محدودیت های استفاده از Flash Fill

همانطور که دیدید، ابزار Flash Fill در اکسل بسیار ارزشمند و پرقدرت است. ولی متاسفانه ضعف ها و محدودیت هایی نیز هنگام استفاده از آن دیده می شود. در ادامه بعضی از این محدودیت ها معرفی شده اند.

• به روزرسانی محتویاتی که توسط Flash Fill ایجاد شده اند وجود ندارد. به این معنی که اگر سلول یا سلول هایی که در ستون اول از مثال هایی قبلی بودند، عوض شود، محتویات سلول های ستون های حاصل از پرکردن سریع، تغییر نخواهد کرد. در نتیجه از این دیدگاه، Flash Fill ، مانند فرمول ها و محاسبات درون اکسل، براساس سلول های پیش نیاز شکل گرفته ولی تغییر نخواهد کرد.
• گاهی با توجه به تنوع دنباله ها یا درخواست های شما، ممکن است ابزار Flash Fill به درستی، الگوی داده ها را درک نکرده باشد. برای اینکه به اکسل در این امر کمک کنید، بهتر است تعداد مثال ها را افزایش دهید. نتیجه ای که انتظار دارید اکسل برایتان ایجاد کند را برای دو یا سه سطر وارد کنید تا اکسل به توالی و الگوی ذهنی شما پی برده و آن را برای سطرهای بعدی تکرار کند.

همانطور که مشخص است، سلول هایی که با رنگ زمینه نارنجی دیده می شوند، محتویات مناسبی ندارند و به درستی توسط Flash Fill تشکیل نشده اند.

• ممکن است تبدیل بوسیله Flash Fill ، ماهیت و قالب بندی سلول را تغییر دهد. برای مثال ممکن است معرفی یک قالب بندی خاص روی اعداد توسط Flash Fill در اکسل، آن ها را به متن تبدیل کند که در این صورت ماهیت اعداد از بین خواهد رفت.
• ممکن است بنابه دلایلی، اکسل نتواند الگوی مناسبی تشخیص دهد که در این صورت پیغام خطایی به مانند تصویر زیر بر روی صفحه کاربرگ ظاهر خواهد شد.

شاید با معرفی کردن مثال های بیشتر در دنباله اطلاعات، اکسل بتواند به نیت شما پی برده و بقیه مقادیر دنباله را ایجاد کند.

فعال یا غیرفعال کردن Flash Fill در اکسل

بطور پیش فرض بعد از نصب نرم افزار کاربردی اکسل، پرکردن سریع (Flash Fill) فعال است. ولی اگر می خواهید این ابزار اکسل هنگام کار و پرکردن سلول ها، مقادیری را به شما پیشنهاد ندهد، می توانید آن را غیرفعال سازید. به این منظور مراحل زیر را طی کنید.

1. از فهرست File وارد گزینه Options شوید.
2. در پنجره ظاهر شده، در بخش سمت چپ، گزینه Advanced را انتخاب کنید.
3. در قسمت Editing options گزینه Automatically Flash Fill را خاموش کنید.
4. دکمه OK را بزنید.

به این ترتیب دیگر پیشنهادهای هیجان انگیز Flash Fill را نخواهید دید. اگر از این امر منصرف شدید، کافی است همین روند را طی کرده و گزینه مربوطه را فعال کنید.

نکته: گاهی با توجه به فعال بودن گزینه Flash Fill، پیشنهاد برای ایجاد دنباله ها ظاهر نمی شود. در این مواقع بهتر است خودتان با فشردن دکمه Flash Fill از برگه Data، این کار را به عهده بگیرید. همچنین به محلی که برای ایجاد ادامه دنباله انتخاب کرده اید، دقت کنید تا مکان درست و سلول مناسب، مشخص شده باشد.

نحوه مقایسه دو ستون در اکسل و قرمز شدن تفاوت ها