آغاز ثبت نام دوره آنلاین شروع حرفه ای اکسل (ظرفیت محدود)
سبد خرید
0

سبد خرید شما خالی است.

جستجو
Generic filters

نحوه کار با Autofill و ایجاد لیست خودکار در اکسل

Autofill در اکسل
۴.۳/۵ - (۲۳ امتیاز)

ایجاد سری با استفاده از Autofill در اکسل

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

درگ کردن اعداد

  1. عددی رو در یک سل تایپ کنید.
  2. روی سلول کلیک کنید، گوشه سمت راست، پایین سلول یک مربع مشکی کوچیک وجود داره. موس رو روی اون نگه دارید.
  3. به محض اینکه علامت موس بصورت + دراومد، Drag کنید. وقتی داده رو درگ میکنید اکسل یا داده موجود در سلول رو کپی میکنه یا اینکه به ترتیب سلول ها رو پر میکنه. یک علامتی در کنار آخرین سلول قرار میگیره که از ما میپرسه چه چیزی رو و با چه شرایطی در سلول ها قرار بده؟

AutoFill در اکسل - درگ کردن عدد در اکسل

شکل ۱- AutoFill در اکسل – درگ کردن عدد در اکسل

در ادامه توضیح داده شده است که هر کدام از این موارد چه کارکردی دارند:

Copy Cells محتوای سلول را عینا در سلول های دیگه کپی میکنه
Fill Series سری عددی ایجاد میکنه با قدر نسبت ۱ (سلول مرجع، ابتدای سری خواهد بود)
Fill Formatting Only فقط فرمت سلول مرجع رو منتقل میکنه
Fill Without Formatting بدون فرمت سلول مرجع، داده رو کپی میکنه
Flash Fill مطابق با الگوی سل مرجع عمل خواهد کرد

درگ کردن با انتخاب بیش از یک سلول

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

اگر بخوایم همین داده ها پشت سر هم تکرار بشن، یعنی ۲ ,۴ ,۲, ۴ ,۲ ,۴ و ….. بعد از درگ کردن، از زبانه ایجاد شده، Copy Cells رو بزنید.

درگ کردن تاریخ و زمان

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

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

AutoFill در اکسل - درگ کردن تاریخ

شکل ۲- درگ کردن تاریخ

Fill Days ایجاد سری تاریخی با فواصل یک روز
Fill Weekdays ایجاد سری تاریخی با فواصل یک هفته
Fill Months ایجاد سری تاریخی با فواصل یک ماه
Fill Years ایجاد سری تاریخی با فواصل یک سال

توجه داشته باشید که این موضوع فقط در مورد تاریخ میلادی صادق است.

درگ کردن عبارت های متنی

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

درگ کردن عبارات متنی خاص  شکل۳- AutoFill در اکسل – درگ کردن عبارات متنی خاص

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

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

 File/ Options/ Advance/ General/ Edit Custom List

بعد از باز شدن پنجره Edit Custom List در قسمت import محدوده ای که لیست فارسی تایپ شده است رو وارد میکنیم بعد Add را زده و Ok میکنیم.

اضافه کردن لیست های فارسی در اکسل

شکل۴- AutoFill در اکسل – اضافه کردن لیست های فارسی در اکسل

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

AutoFill در اکسل با Double Click

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

استفاده از لیست ذخیره شده در اکسل

درگ کردن فرمول ها با استفاده از AutoFill در اکسل

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

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

کلیدواژه : متوسط
126

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

دیدگاه کاربران
  • گ ۲۵ بهمن ۱۴۰۰ / ۱۱:۲۲ ق٫ظ

    سلام حالتون خوبه؟ یه سوالی داشتم خدمتتون..من یه اکسلی دارم با ۵۰۰۰ ردیف..الان باید از هر ۱۲ ردیف یه بار یه ردیف جدید بینشون اضافه کنم..روشی هست که بتونم این ردیف ها رو یه جا وارد کنم یا باید یکی یکی دستی وارد کنم؟

    • حسنا خاکزاد ۲۵ بهمن ۱۴۰۰ / ۱۲:۵۰ ب٫ظ

      درود بر شما
      باید از ابزار سورت استفاده کنید
      به این ترتیب که:
      یک ستون ایجاد کنید
      باید یک سری ایجاد کنید که اعداد یک تا (هرجا که لازمه) دوازده بار تولید بشه. یعنی توی این ستون همچین چیزی میخوایم:
      ۱
      ۱
      ۱
      ۱
      ۱
      ۱
      ۱
      ۱
      ۱
      ۱
      ۱
      ۱
      ۲
      ۲
      ۲
      ۲
      ۲
      ۲
      ۲
      ۲
      ۲
      ۲
      ۲
      ۲
      و الی اخر
      (این کار با روش های مختلف قابل انجامه)
      با این کار یک ستون داریم که اعداد ۱و ۲ و … ۱۲ بار پشت سر هم تکرار شدن. و مثلا اخرین عددی که ثبت شده ۴۰۰ هست
      حالا در انتهای همون ستون عدد ۱تا ۴۰۰ رو یکبار پشت سر هم ثبت میکنید
      و در نهایت روی ستون ایجا دشده، سورت میکنید داده ها رو.
      این کار باعث میشه سیزدهمین یک که جلوش خالیه بیاد در انتهای ۱۲ عدد ۱ که اول نوشته بودیم قرار بگیره و در واقع سطر خالی بعد از ۱۲ ردیف ایجا دشده

      امیدوارم بتونید اجرا کنید

      راه دیگه هم وی بی

  • فربد ۱۹ دی ۱۴۰۰ / ۲:۰۲ ب٫ظ

    سلام وقت بخیر
    من در یک فایل اکسل دو نوع لیست ماهیانه و سالیانه دارم میخواهم با وارد کردن اطلاعات در هر سلول لیست ماهانه اتوماتیک همه اطلاعات در لیست سالیانه که در همان اکسل هست منتقل شود ممنون میشم راهنمایم کنید

    • حسنا خاکزاد ۱۹ دی ۱۴۰۰ / ۷:۳۶ ب٫ظ

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

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

  • جواد ۲۳ آبان ۱۴۰۰ / ۹:۳۴ ق٫ظ

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

    • حسنا خاکزاد ۲۳ آبان ۱۴۰۰ / ۱:۰۰ ب٫ظ

      درود
      اگر table جواب نیاز رو نده، نه فعلا امکانی بصورت پیشفرض وجود نداره

  • ZAR ۳۱ مرداد ۱۴۰۰ / ۱:۳۰ ق٫ظ

    سلام وقت بخیر من میخام این فرمول رو =LOOKUP(F$1;’ضایعات (پرسنل)’!B:B;’ضایعات (پرسنل)’!FE:FE) در قسمت آخرش که FE:FE رو در سلول های بعد با حرف بعدش بنویسه مثلا FF:FF , و همینطور به ترتیب حروف الفبا پیش بره اینکه داخل هرسلول بخام حرف بعد رو خودم تایپ کنم زمان بره ممنون میشم راهنمایی کنید.

    • حسنا خاکزاد ۳۱ مرداد ۱۴۰۰ / ۱۱:۲۰ ق٫ظ

      درود
      وقتی درگ کنید خودش تغییر میکنه نیاز به تغییر دستی نیست
      اگر منظور این نیست توضیح بیشتر بدید

  • مجید ۱ دی ۱۳۹۹ / ۱۱:۱۵ ب٫ظ

    سلام در قسمت auto fill وقتی او بعلاوه مشکی میاد گزینه fill series نداره اکسلمم ۲۰۱۹ نمیتونم فرمول هارا به بقیه اعداد تعمیم بدهم

    • حسنا خاکزاد ۲ دی ۱۳۹۹ / ۸:۰۸ ب٫ظ

      درود
      fil series مال انتقال فرمول نیست
      معمولی درگ کنید فرمول ها انتقال داده میشه

  • هومن احمدیان ۲۱ مهر ۱۳۹۹ / ۹:۵۳ ق٫ظ

    سلام روزتون بخیر
    من سلول هارو انتخاب میکنم و با استفاده از علامت جمع اونهارو به پایین میکشم ولی همون عدد تکرار میشه و در کادر پایینی گزینه COPY CELL یا fill series وجود نداره به جای اونها یه کادری باز میشه که formating chart total table و اینها هستند لطفا راهنمایی بفرمایید چگونه همون گزینه fill series رو فعال کنم

    • سامان چراغی ۲۴ اسفند ۱۳۹۹ / ۱:۵۲ ب٫ظ

      سلام
      اگر درون Table این کار رو انجام میدید به خاطر همینه. همین کار رو خارج از جدول انجام بدید.

  • سید ۱۶ تیر ۱۳۹۹ / ۳:۳۱ ب٫ظ

    سلام
    یه فرمول دارم به این شکل

    IF(ISBLANK($C$1),0,VLOOKUP($C$1,’21’!$B$5:$Q$104,9,0))
    این فرمول تو خونه های زیادی باید تکرار بشه.
    ولی به این شکل که تو هر سطر عدد ۹ از فرمول vlookup تغییر میکنه و در هر ستون، شماره شیت (در اینجا، ۲۱) تغییر میکنه
    جوری هست بشه این فرمول رو با این تغییرات بسط داد؟ یا باید دستی تغییر کنن

    • حسنا خاکزاد ۱۶ تیر ۱۳۹۹ / ۱۱:۱۷ ب٫ظ

      درود
      بستگی به جهت درگ کردن داره
      بصورت کلی توابع row/ column کارشون ایجاد عدد تناوبی در فرمول هست
      میتونید مقاله مقاله رو ببینید

  • جواد ۱۸ دی ۱۳۹۸ / ۶:۰۰ ب٫ظ

    با سلام
    سوالی داشتم
    من میخواستم که فورمول زیر رو وقتی میکشم پایین
    Sheet2!C7
    با کشیدن پایین عدد شیت تا ۹۵ بره.
    یعنی نخوام دونه دونه واسه هر سلول فرمول بدم عدد شیت رو عوض کنم.
    چون وقتی میکشم پایین عدد شیت ثابت میمونه و فقط عدد c7 بالا میره.

    • سامان چراغی ۱۸ دی ۱۳۹۸ / ۷:۱۴ ب٫ظ

      سلام
      برای این کار اول کل محدوده ای که میخواید فرمول درونشون قرار بگیره انتخاب کنید سپس تو یک سلول فرمولتون رو بنویسید و نهایتا به جای Enter ترکیب Ctrl + Enter بزنید.

      • جواد ۱۹ دی ۱۳۹۸ / ۷:۵۶ ق٫ظ

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

    • حسنا خاکزاد ۱۹ دی ۱۳۹۸ / ۱۰:۰۶ ق٫ظ

      درود بر شما
      یک راه استفاده از ترکیب indirect و address هست. این مقاله رو بخونید
      https://excelpedia.net/address-function/

      و اسم شیت رو اینطوری بنویسید که با درگ کردن شماره بندازه:

  • سارا ۱۳ دی ۱۳۹۸ / ۱:۲۹ ب٫ظ

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

    • سامان چراغی ۱۵ دی ۱۳۹۸ / ۷:۴۶ ق٫ظ

      سلام،
      باید سلول های هر شیت رو که میخواید در لیست بیاد نامگذاری کنید (به نام همون شیت) سپس در سلولی که میخواید لیست رو نمایش بده یک List از Data Validation تعریف کنید که در قسمت Custom آن نوشته باشید:

      با فرض اینکه نام شیت رو در سلول B1 تعیین میکنید.

  • الهه ۶ مهر ۱۳۹۸ / ۶:۲۷ ب٫ظ

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

  • فریبا ۳۱ خرداد ۱۳۹۸ / ۴:۵۹ ب٫ظ

    سلام خسته نباشید
    من یه آدرس دهی از طریق لینک گذاری در یک فهرستی داشتم اخیرا آدرسها قابل خوندن نیست و پیغام خطا can not open the specified میده چون تعداد لیست زیاده امکان اصلاح به صورت تک تک ندارم امکان داره راهنمایی کنید چون احتمالا مسیر آدرس م دچار مشکل شده .
    در edite hyp مسیر آدرس text to dis با address متفاوت هست .
    ممنونم

    • سامان چراغی ۳۱ خرداد ۱۳۹۸ / ۹:۴۶ ب٫ظ

      سلام
      برای ویرایش تعداد زیادی هایپرلینک از کد وی بی باید استفاده کنید

      در کد بالا آدرس جدید رو به جای New Address بذارید و در قسمت File Name هم اسم فایل رو با استفاده از توابع بدست بیارید.

  • وکیلی ۱۴ اردیبهشت ۱۳۹۸ / ۶:۲۹ ب٫ظ

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

    • حسنا خاکزاد ۱۴ اردیبهشت ۱۳۹۸ / ۸:۰۷ ب٫ظ

      درود بر شما
      از table استفاده کنید
      ویژگی های سطر رو انتفال میده

  • مهدي ۱۷ اسفند ۱۳۹۷ / ۱:۱۳ ق٫ظ

    سلام و خسته نباشید
    من میخواهم اعداد به صورتی ۷تایی با این فرمت تکرار شوند.
    ۱-۱ , ۱-۲ ,۱-۳ ۱-۴, ۱-۵ ,۱-۶, ۱-۷ , ۲-۱ ,۲-۲ ,۲-۳, ۲-۴, ۲-۵ ,۲-۶ ,۲-۷ ,۳-۱,….
    البته به صورت عمودی
    لطفا راهنمایی کنید.
    تشکر

    • حسنا خاکزاد ۱۸ اسفند ۱۳۹۷ / ۱۰:۲۷ ق٫ظ

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

  • منیره ۷ بهمن ۱۳۹۷ / ۱۱:۰۹ ق٫ظ

    سلام.
    عالیه ممنونم

  • فرنوش ۱۳ آبان ۱۳۹۷ / ۷:۲۶ ب٫ظ

    سلام ممنون از سایت خوبتون
    چجور میتونم دوتا سلول را درگ کنم؟تا سری بسازم
    هرکاری میکنم نمیشه😭

    • حسنا خاکزاد ۱۵ آبان ۱۳۹۷ / ۹:۱۷ ق٫ظ

      درود بر شما
      هر دو رو انتخاب کنید
      دستگیره سلول دومی رو بگیرید و به سمت پایین درگ کنید

  • علی ۷ مهر ۱۳۹۷ / ۳:۳۵ ب٫ظ

    با سلام و وقت به خیر میخواستم در یک جدول فقط ردیفهای مربوط به دامنه ای خاص را داشته باشم و بقه حذف شوند.مثلا ردیفهای مربوط به اعدادی که در ستون بین ۵ تا ۱۰ هستندلطفا راهنمایی کنید متشکرم

    • حسنا خاکزاد ۷ مهر ۱۳۹۷ / ۴:۰۲ ب٫ظ

      درود بر شما
      یک راه ساده ایمه که محدوده دلخواه رو فیلت رکنید و به یک شیت دیگه منتقل کنید و شیت قبلی رو پاک کنید

      یا اینکه برعکس خواستتون رو فیلترکنید و داده هایی که نمایش داده میشن رو حذف کنید

  • فریبا ۶ مهر ۱۳۹۷ / ۱۱:۵۱ ق٫ظ

    سلام
    من میخوام لیست دانش آموزان مدرسه رو براساس پایه (دهم، یازدهم، دوازدهم)مرتب کنم، همین مراحلی که شما توضیح دادید انجام دادم یک custom list تعریف کردم ولی مرتب نمیشه و این پیغام را نشان میدهد:
    This operation requires the merged celles to be identically sized
    چکار باید بکنم؟!

    • حسنا خاکزاد ۷ مهر ۱۳۹۷ / ۸:۵۷ ق٫ظ

      درود بر شما
      سلول مرج شده نباید داشته باشید
      Unmerge کنید

  • مرتضی ۱۰ فروردین ۱۳۹۷ / ۲:۴۸ ب٫ظ

    سلام من گزارش مالی از سیستم بیمه گرفتم که مشخص میکنه چه بیمه ای در چه تاریخی ثبت شده.
    مشکل اینجاست تاریخ بصورت منظم نمیاد مثلا ۹۳/۰۲/۰۵ دنبالش۹۳/۰۸/۰۹
    لذا خواهش من از شما اینه اگه راه حلی داره برام بگین چون مال ۷ سال هست که بالغ بر ۹۰۰۰ سطر میشه
    خواهشن اگه راه حلی داره برام ایمیل کنین. وقتم کمه.
    پیشاپیش از زحمات شما نهایت تشکر را دارم .

    • حسنا خاکزاد ۱۲ فروردین ۱۳۹۷ / ۸:۳۰ ب٫ظ

      درود بر شما
      / ها رو بردارید. از طریق ابزار Find & Replace. بعد براحتی مثل عدد باهاشون رفتار کنید و مرتب کنید.
      در نهایت از قسمت فرمت سل، ۰۰″/”۰۰″/”۰۰ این کد رو بزنید تا / ها نمایش داده بشن.

      موفق باشید

  • محسن ۲۰ بهمن ۱۳۹۶ / ۰:۳۹ ق٫ظ

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

  • ایوب ۴ مهر ۱۳۹۶ / ۶:۲۹ ب٫ظ

    عالی

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

5 + 13 =

توسط
تومان