5 ترفند تکمیل خودکار مایکروسافت اکسل برای ساخت سریعتر صفحات گسترده

از این ترفندهای تکمیل خودکار صفحه‌گسترده برای تکمیل سریع‌تر (و هوشمندانه‌تر کارها) و صرف زمان کمتر در Microsoft Excel استفاده کنید.

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

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

1. تکمیل خودکار هر سلول دیگر در اکسل

هرکسی که مدتی از اکسل استفاده کرده باشد می داند که چگونه از ویژگی تکمیل خودکار برای تکمیل خودکار یک سلول اکسل بر اساس سلول دیگر استفاده کند.

شما به سادگی ماوس خود را در گوشه سمت راست پایین سلول کلیک کنید و نگه دارید، و آن را به پایین بکشید تا فرمول آن سلول در هر سلول زیر آن اعمال شود (شبیه به کپی کردن فرمول ها در اکسل).

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

نحوه پر کردن خودکار هر سلول دیگر در اکسل

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

1. دو خانه اول را در ستون مورد نظر برجسته کنید.
2. علاوه بر این، ماوس را در گوشه سمت راست پایین دو سلول قرار دهید تا مکان نما به ‘+’ تغییر کند.
3. حالا آن را نگه دارید و همانطور که به طور معمول انجام می دهید پایین بکشید.

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

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

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

اگر آن سلول های دوم خالی نباشند چه؟ خوب، در آن صورت، اکسل همان قوانین را در سلول دوم بلوک اول که هایلایت کرده بودید، برای هر سلول دیگر نیز اعمال می کند.

به عنوان مثال، اگر سلول دوم یک “1” در خود داشته باشد، اکسل تمام سلول های دیگر را با شمارش 1 به صورت خودکار پر می کند.

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

2. تکمیل خودکار به پایان داده در مایکروسافت اکسل

کشیدن نشانگر ماوس از بالا به پایین مجموعه ای از 100 تا 200 ردیف برای پر کردن خودکار آن ستون بسیار آسان است. اما، اگر واقعاً 10،000 یا 20،000 ردیف در صفحه گسترده وجود داشته باشد، چه؟ کشیدن نشانگر ماوس به سمت پایین در 20000 ردیف زمان زیادی می برد.

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

1. کاربرگ اکسل را باز کنید و هر دو سلول را در یک ستون انتخاب کنید.
2. به جای کشیدن تمام طول ستون به پایین، فقط کلید shift را روی صفحه کلید نگه دارید.
3. اکنون متوجه خواهید شد که وقتی ماوس خود را در گوشه سمت راست پایین سلول قرار می دهید، به جای نماد مثبت، یک نماد با دو خط افقی و موازی است.
4. اکنون، تنها کاری که باید انجام دهید این است که روی آن نماد دوبار کلیک کنید و اکسل به طور خودکار کل ستون را پر می کند، اما فقط تا جایی که ستون مجاور واقعاً داده دارد.

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

3. جاهای خالی را پر کنید

تصور کنید که وظیفه تمیز کردن صفحه گسترده اکسل را بر عهده دارید و رئیس شما از شما می خواهد که یک فرمول خاص را برای هر سلول خالی در یک ستون اعمال کنید.

شما نمی‌توانید هیچ الگوی قابل پیش‌بینی را ببینید، بنابراین نمی‌توانید از ترفند تکمیل خودکار «Every x» در بالا استفاده کنید. بعلاوه، این رویکرد هرگونه داده موجود در ستون را از بین می برد. چه کاری می توانی انجام بدهی؟ خوب، ترفند دیگری وجود دارد که می توانید از آن استفاده کنید تا فقط سلول های خالی را با هر چیزی که دوست دارید پر کنید.

در برگه بالا، رئیس شما از شما می خواهد که هر سلول خالی را با رشته “N/A” پر کنید. در یک برگه با چند ردیف، این یک فرآیند دستی آسان خواهد بود. اما در یک برگه با هزاران ردیف، یک روز کامل از شما زمان می برد.

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

1. کاربرگ اکسل خود را باز کنید و تمام داده های موجود در ستون را انتخاب کنید.
2. سپس به منوی Home بروید، نماد Find & Select را انتخاب کنید و Go To Special را انتخاب کنید.
3. در پنجره بعدی، Blanks را انتخاب کنید.
4. حالا در پنجره بعدی می توانید فرمول را در اولین سلول خالی وارد کنید. در این مورد، شما فقط N/A را تایپ می‌کنید و سپس CTRL + Enter را فشار می‌دهید تا همین مورد برای هر سلول خالی یافت شده اعمال شود.

اگر می‌خواهید، به جای «N/A»، می‌توانید فرمولی را در اولین سلول خالی تایپ کنید (یا روی مقدار قبلی کلیک کنید تا از فرمول موجود در سلول درست بالای سلول خالی استفاده کنید).

اگر بخواهید تمام فضاهای خالی کاربرگ خود را با یک رشته پر کنید، این روش به خوبی جواب می دهد، اما اگر اصلاً فضاهای خالی را نخواهید چه می شود؟ برای این، ما قبلاً چند روش مفید برای حذف فضاهای اضافی در اکسل را پوشش داده ایم.

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

این آخرین ترفند در واقع چند مرحله طول می کشد. شما باید روی دسته ای از آیتم های منو کلیک کنید—و کاهش کلیک ها همان چیزی است که کارآمدتر شدن است، درست است؟

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

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

نام ماکرو را بگذارید و سپس روی دکمه Create Macro کلیک کنید. با این کار یک پنجره ویرایشگر کد باز می شود. کد زیر را در تابع جدید قرار دهید:

FirstColumn = InputBox (“لطفاً حرف ستون را وارد کنید.”)

FirstRow = InputBox (“لطفاً شماره ردیف را وارد کنید.”)

LastRow = Range (FirstColumn & “65536”). End(xlUp). Row

برای i = FirstRow To LastRow

If Range(FirstColumn & i).Value = “” سپس

محدوده (FirstColumn & (i – 1)). محدوده کپی (FirstColumn & i)

پایان اگر

بعدی منم

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

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

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

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

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

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

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

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

FirstColumn = InputBox (“لطفاً حرف ستون را وارد کنید.”)

FirstRow = InputBox (“لطفاً شماره ردیف اول را وارد کنید.”)

LastRow = InputBox (“لطفاً شماره ردیف آخر را وارد کنید.”)

برای i = FirstRow To LastRow

محدوده (FirstColumn & i).Value = 5000 + (Range(FirstColumn & (i – 1)).Value * 0.1)

بعدی منم

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

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

پر کردن خودکار ستون‌های اکسل بسیار آسان است

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

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

منبع مقاله