5 ترفند عالی برای پر کردن خودکار سلول‌های اکسل

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

1- تمام سلول‌های دیگر را پر کنید

اگر مدتی است که با اکسل کار می‌کنید حتما می‌دانید که چگونه از ویژگی "پر کردن خودکار" استفاده کنید. روی گوشه پایین سمت راست یک سلول کلیک می‌کنید و ماوس را روی آن نگه می‌دارید و به‌سمت پایین می‌کشید تا فرمول شما روی تمام سلول‌های زیرین آن ستون اعمال شود.

اگر اولین سلول تنها یک عدد است و در آن فرمولی به‌کار نرفته، اکسل به‌طور خودکار عدد آن سلول را با یک جمع می‌کند و در سلول زیرین قرار می‌دهد و این روند برای سلول‌های بعدی به‌همین منوال تکرار می‌شود.

اما اگر نمی‌خواهید که فرمول autofill روی تمام سلول‌های زیرین اعمال شود باید چه کنیم؟ به‌عنوان مثال، فرض کنید می‌خواهید نام و نام خانوادگی به‌یکدیگر بپیوندند اما آدرس‌ها هیچ تغییری نکند.

فرمول را روی سلول‌های دیگر اعمال کنید

این کار با یک تغییر کوچک در روند autofill امکان‌پذیر است. به‌جای کلیک کردن روی اولین سلول و کلیک کردن بر روی گوشه پایین سمت راست، دو سلول اول را هایلایت کنید. بعد از آن، ماوس را روی لبه پایین سمت راست دو سلول نگه دارید تا نشانگر ماوس به‌علامت "+" تغییر کند.

حالا ماوس را به‌پایین بکشید.

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

سلول‌های دیگر چگونه اداره می‌شوند

حالا اگر سلول‌های دوم خالی نباشند تکلیف چیست؟ در این حالت، اکسل همان قوانین سلول دوم مربوط به اولین بلوک هایلایت شده را به‌سایر سلول‌ها انتقال می‌دهد. بطور مثال، اگر داخل سلول دوم عدد "1" وجود داشته باشد، اکسل به‌طور خودکار سایر سلول‌ها را با اضافه کردن مقدار یک به‌هر کدام پر می‌کند.

2- پر کردن خودکار تا پایان داده

یکی از مواردی که کاربران در هنگام کار با شیت‌های کاری اکسل در شرکت‌ها و محیط‌های کاری بزرگ با آن روبرو می‌شوند سروکار داشتن با حجم عظیمی از شیت‌ها است.

می‌توان با گرفتن و پایین بردن ماوس بین 100 تا 200 سطر و پر کردن خودکار سلول‌های یک ستون به‌هدف رسید. اما فکر کنید اگر 10 یا 20 هزار سطر در یک شیت گسترده روبروی‌تان باشد چه اتفاقی می‌افتد؟ انجام همین روند برای 20 هزار سطر زمان بسیار زیادی از شما می‌گیرد.

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

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

این ترفند به‌اندازه چندین ساعت باعث صرفه‌جویی در زمان‌تان می‌شود.

3- پر کردن خانه‌های خالی

تصور کنید که می‌خواهید یک صفه گسترده اکسل را پاک کنید و از شما خواسته شده تا یک فرمول خاص و ویژه را برای هر خانه خالی موجود در یک ستون اعمال کنید. نمی‌توانید هیچ الگوی قابل پیش‌بینی پیدا کنید. ترفندی که در بالا اشاره شد هم جواب‌گو نیست. در ضمن، با روش فوق، هر داده‌ای که داخل این ستون وجود داشته باشد پاک می‌شود. چاره چیست؟

برای این منظور هم یک ترفند دیگر وجود دارد که به‌کمک آن می‌توانید تنها خانه‌های خالی یک ستون را با هر چیزی که خواستید پر کنید.

در شیت بالا، قرار است تمام خانه‌های خالی با رشته "N/A" پر شود. اگر شیت شما فقط چند سطر داشته باشد، پس می‌توانید این کار را به‌راحتی و با دست انجام دهید. اما وقتی شیت شما هزاران سطر دارد، باید یک روز کامل وقت بگذارید.

پس، نمی‌توانید این کار را دستی انجام دهید. تمام داده‌های یک ستون را انتخاب کنید. به منوی Home بروید، آیکون Find & Select را انتخاب کنید. حالا، Go To Special را انتخاب کنید.

در پنجره بعدی، Blanks را انتخاب کنید.

در پنجره بعدی، فرمول را در اولین خانه خالی وارد کنید. در این‌جا، تنها کافی است عبارت N/A را تایپ کنید و سپس Ctrl + Enter را بزنید تا همین اتفاق برای تمام خانه‌های خالی که پیدا می‌شود رخ دهد.

در صورت نیاز، به‌جای "N/A"، یک فرمول را داخل اولین سلول خالی وارد کنید (یا روی مقدار قبلی کلیک کنید تا از فرمول سلولی که درست بالای سر سلول خالی وجود دارد استفاده کند)، وقتی Ctrl + Enter را بگیرید، همان فرمول برای تمام خانه‌های خالی به‌کار می‌رود.

با این کار، پاک کردن یک شیت خیلی بزرگ بسیار سریع و آسان انجام می‌شود.

4- با مقدار ماکروی قبلی پر کنید

روش قبلی کار زیادی می‌برد. باید روی چند آیتم در منوهای مختلف کلیک کنید. اما کم کردن کلیک‌ها موردی است که همه دنبال آن هستیم.

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

برای ساختن ماکرو، روی آیتم Developer در منو کلیک کنید. بعد از آن، روی آیکون Macros کلیک کنید.

یک نام برای ماکرو انتخاب کنید و روی دکمه Create Macro کلیک کنید. در این‌جا، یک پنجره ویرایشگر کد باز می‌شود. کد زیر را داخل فانکشن جدید پیست کنید:

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

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

انجام این کار با همان روش نگه داشتن ماوس و کشیدن آن به‌پایین امکان‌پذیر نیست، اما با ترفندی که در بالا به‌آن اشاره شد یا استفاده از ماکرویی که در این‌جا توضیح دادیم کار را بسیار ساده می‌کند.

5- ماکروی محاسبات تکراری

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

بیایید ماکروی قبلی را اصلاح کنیم تا محاسبه جدیدی را بر اساس نتایج سلول قبلی انجام دهیم.

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

توجه داشته باشید که این روش یک راه‌کار جایگزین برای محاسبات تکراری است. شما می‌توانید یک فرمول را مستقیما در سلول خالی بعدی وارد کنید و سلول قبلی را هم در این فرمول منظور کنید. زمانی‌که آن ستون را autofill کنید، مقادیر قبلی به‌همان ترتیب در محاسبات منظور می‌شوند.