سبد خرید
0

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

جستجو
Generic filters

ایجاد لیست کشویی در اکسل و کار با Data Validation

ایجاد لیست کشویی در اکسل
۴.۸/۵ - (۲۶ امتیاز)

Excel Data Validation

برای ایجاد لیست کشویی در اکسل از چه ابزاری باید استفاده کرد؟

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

ایجاد لیست فروریز در یک سلول از اکسل از طریق ابزار دیتا ولیدیشن (Data Validation) امکان پذیر است.

روش اول ایجاد لیست کشویی (با سلول کمکی):

  1. داده های مورد نظر رو در یک محدوده تایپ کنید. مطابق شکل ۱ روزهای هفته را تایپ میکنیم.
  2. روی سلول مورد نظر که مایلیم لیست در آن قرار گیرد کلیک میکنیم. سل C2.
  3. از مسیر Data/Data Validation/Settings/Allow گزینه List را انتخاب میکنیم.
  4. در قسمت Source آدرس محدوده داده های مورد نظر را وارد میکنیم و سپس Ok.

 ایجاد لیست کشویی در اکسل (DropDown List)

شکل ۱- ایجاد لیست کشویی در اکسل (DropDown List)

حالا سلول مورد نظر ما حاوی لیستی از روزهای هفته است. (شکل ۲)

ایجاد لیست کشویی در اکسل- لیست ایجاد شده در سلول C2

شکل ۲- لیست ایجاد شده در سلول C2

روش دوم ایجاد لیست کشویی (بدون سلول کمکی):

داده های منبع لیست رو میتونیم مستقیما داخل Data Validation تایپ کنیم.

  1. روی سلول مورد نظر که مایلیم لیست در آن قرار گیرد کلیک میکنیم. سل C2.
  2. از مسیر Data/Data Validation/Settings/Allow گزینه List را انتخاب میکنیم.
  3. داده های مورد نظر را در قسمت Source تایپ میکنیم. و با , از هم جدا میکنیم و Ok.

ایجاد لیست کشویی در اکسل- بدون سلول کمکی و تایپ مستقیم در DataValidation

شکل ۳- ایجاد لیست بدون سلول کمکی و تایپ مستقیم در DataValidation

راه انتقال لیست تهیه شده به سلول های دیگر

  1. سلول حاوی لیست فروریز را انتخاب کرده و کپی میکنیم.
  2. محدوده مورد نظر که میخواهیم لیست فروریز را به آن انتقال بدیم رو انتخاب کرده و از Paste Special گزینه Validation را انتخاب میکنیم و Ok را میزنیم. (شکل ۴)

انتقال لیست فروریز به سلول های دیگر

شکل ۴- انتقال لیست فروریز به سلول های دیگر

پیشنهاد میکنم مقالات زیر رو هم نگاه کنید:

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

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

دیدگاه کاربران
  • ميلاد ۱۳ آبان ۱۳۹۹ / ۸:۴۲ ق٫ظ

    درود . من به وسیله data.validation و indirect در ۴ ستون لیست کشویی تابع هم بوجود اوردم . به این صورت که ستون اول : پایه ، ستون دوم : زیر پایه و ستون سوم : وابسته به زیر پایه و ….. وقتی به ترتیب میخوام بعد از انتخاب ستون اول ، ستون دوم رو انتخاب کنم همه چیز درسته ، اما نیاز من اینه که اگر ستون پایه ( یعنی ستون اول ) پاک بشه ستون دوم و سوم خودشون خودکار پاک بشن ، ( در حال حاضر اگر ستون اول پاک بشه ، ستون های بعدی اطلاعات قبلی رو حفظ میکنن )

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

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

      • میلاد ۵ آبان ۱۴۰۰ / ۱۰:۰۶ ق٫ظ

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

  • rasoul ۲۶ خرداد ۱۳۹۹ / ۳:۱۴ ب٫ظ

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

  • کاوه احدی ۱۸ فروردین ۱۳۹۹ / ۲:۱۲ ب٫ظ

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

  • محمدرضا ۱۱ اسفند ۱۳۹۸ / ۲:۲۰ ب٫ظ

    یک سوال داشتم
    من یک سلول کشویی دارم شامل ۱۲ ماه که اسمشونو نوشتم. میخوام وقتی کشو باز میشه و مثلا یک ماه رو انتخاب میکنم زیر اون سلول اطلاعاتی که مربوط به اون ماه هست و در جدول دیگه ای وارد کردم که همه ماهها توش قاطی هست نماش داده بشه ….

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

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

  • مسعود نوری راد ۴ بهمن ۱۳۹۸ / ۵:۳۳ ب٫ظ

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

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

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

  • محمد ۲ بهمن ۱۳۹۸ / ۹:۱۴ ب٫ظ

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

    • سامان چراغی ۳ بهمن ۱۳۹۸ / ۱۰:۲۵ ق٫ظ

      اگر اطلاعاتی که انتهای جدول اضافه میکنید به محدوده جدول اضافه بشه باید در لیست نمایش داده بشه.
      نحوه صحیح وارد کردن آدرس ستون جدول در Data Validation:

  • نظری ۱۴ آبان ۱۳۹۸ / ۱۲:۲۰ ب٫ظ

    سلام خسته نباشید اکسل من برای ۲۰۰۶ هست درسیستم های دیگه من data validation روانجام میدم ووقتی ذخیره میکنم ودرکامپیوترم باز میکنم اصلا منو کشویی برای من نمیاره و باسیستم خودم هم نمیتونم data validaton انجام بدم درقسمت source نمیتونم تو sheetهای دیگه برم ..مشکل از سیستم هست ؟یعنی اکسلم قدیمیه ؟خیلی ممنون سایتتون عالیه

    • حسنا خاکزاد ۴ آذر ۱۳۹۸ / ۱۲:۲۳ ب٫ظ

      درود بر شما
      ۲۰۰۶ که نداریم . ۲۰۰۷ هست، اما بصورت کلی خود data validation همیشه وجود داشته. اما منبع از شیت دیگه در اون ورژن ممکن نیست.اگه اشتباه نکنم ۲۰۱۰ به بعد این امکان اضافه شد

  • آواتار
    مصطفی ۱۶ خرداد ۱۳۹۸ / ۷:۱۳ ب٫ظ

    با سلام و خسته نباشید

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

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

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

      • آواتار
        مصطفی ۲۴ خرداد ۱۳۹۸ / ۱:۴۳ ب٫ظ

        ممنونم خیلی خوب بود
        تشکر

  • حامد ۱۱ اسفند ۱۳۹۷ / ۴:۰۰ ب٫ظ

    سلام
    یه سوال دارم که نتونستم حلش کنم
    data validation را بلدم
    سوالم اینه که آیا میشه وقتی در حال تایپ هستم مثلا بعد از تایپ دو یا سه کاراکتر، جستجویی(Real Time) انجام بشه و تمام اسامی ای که داخل لیست هستند و شامل کاراکتر تایپی من میشن رو نشون بده

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

      سلام
      به نوعی با VBA ممکنه بشه. به اینصورت که به محض شروع تایپ کردن، لیست نتیجه در سلول های دیگه ایجاد بشه و در لحظه منبع سلول مورد نظر به سلول های جدید وصل بشن.

  • شورشی ۱ اسفند ۱۳۹۷ / ۱۰:۲۲ ب٫ظ

    سلام
    از روش دوم ینی بدون استفاده از ستون کمکی نمیشه
    ینی این ک تمام روزهای هفته در یک لیست قرار میگیره و جدا جدا نمیشه
    ینی تا لیست رو باز میکنی ک یکی از روزهای هفته رو انتخاب کنی فقط یک مورد هست ک اون هم تمامی روزهای هفته هستش و همش باهم توی یه دونه سل انتخاب میشه
    ب جای این ک اینجوری بشه:
    شنبه
    یکشنبه
    دوشنبه
    اینجوری میشه:
    شنبه, یکشنبه, دوشنبه
    لطفا اگه روشی هست ک بشه بدون ستون کمکی یه لیست مص روش اول ساخت بگین
    ممنون

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

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

      • شورشی ۳ اسفند ۱۳۹۷ / ۴:۳۹ ب٫ظ

        سلام
        یکم بیشتر دست کاری کردم درست شد.
        توی آموزش شما جدا کننده ها رو با علامت ; یا , نشون داده
        اما جدا کننده ها توی تنظیمات سیستم من ب صورت فارسی بود ینی این علامت ؛ و روش دوم هم خوب و کاملا درست بود
        ممنونم از سایت و آموزش های خوبتون

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

          درود بر شما
          کلا برداشتم از سوالتون چیز دیگری بود.
          بله جدا کننده مهمه. اگر بیشتر میخواید راجع به این موضوع بدونید، مقاله زیر رو مطالعه کنید
          https://excelpedia.net/excel-formula-rules-part1/

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

    سلام
    وقتتون بخیر
    من دوتا مشکل دارم
    یکی اینکه لیست کشویی سوم با پیروی کردن از این فرمول برای من کار نمیکنه و باید لیست کشویی سوم رو به دوم ارتباط بدم تا بتونم سه ستونی استفاده کنم
    مورد دوم اینه که من میخوام از لیست کشویی در سطرهای دیگه هم استفاده کنم-باید چیکار کنم
    اگه اینزرت کنم فقط لیست کشویی اول تغییر میکنه ولی لیست کشویی دوم تابع سطر اول هست
    باید با چیکار کنم

    ممنون …

    =INDIRECT(SUBSTITUTE(B2&C2,” “,””))
    
    • حسنا خاکزاد ۲۳ بهمن ۱۳۹۷ / ۹:۲۱ ق٫ظ

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

  • یاس ۳۰ دی ۱۳۹۷ / ۲:۲۵ ب٫ظ

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

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

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

  • حامد ۹ آذر ۱۳۹۷ / ۷:۰۰ ب٫ظ

    سلام، امکان داره در سلولی که به صورت LISTBOX تعریف شده هم امکان ورود اطلاعات از لیست داشته باشیم و هم به صورت دستی؟ مچکرم

    • سامان چراغی ۹ آذر ۱۳۹۷ / ۹:۵۷ ب٫ظ

      سلام، در حالت عادی به هر دو صورت میشه اطلاعات رو در سلولی که به صورت لیست تعریف شده وارد کرد.

  • جعفر ۲۱ آبان ۱۳۹۷ / ۱۰:۲۹ ق٫ظ

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

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

      درود بر شما
      سوال واضح نیست.

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

  • گودرزی ۱۷ آبان ۱۳۹۷ / ۶:۱۲ ب٫ظ

    سلام ایا فونت لیست کشویی در اکسل را می توان بزرگ کرد ؟ یا حتما فرمول نویسی میخواد ؟

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

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

  • میثم ۲۴ مرداد ۱۳۹۷ / ۶:۱۸ ب٫ظ

    سلام
    من یه مشکل دارم که لیست هر تعدادی باشه فقط ۸ تا از اونا رو برای انتخاب نشون میده
    از اکسل ۲۰۱۶ استفاده میکنم

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

      درود بر شما
      دیتا ولیدیشن پیش فرضش ۸ آیتم هست. البته اسکرول داره و میتونید به بقیه هم دسترسی داشته باشید
      اگر بخواید متغیر باشه، از کمبوباکس میتونید استفاده کنید

      • عروجی ۱۱ اسفند ۱۳۹۸ / ۰:۲۸ ق٫ظ

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

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

          درود
          کمبوباکس قابلیت چندگانه نداره
          از Listbox استفاده کنید

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

    سلام چرا مال من بازم به صورت خطی نشون میده نه لیستی؟

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

      درود بر شما
      به احتمال زیاد داده ها رو دستی وارد قسمت source کردید و جدا کننده رو به درستی وارد نکردید. حتما کاما , یا ; باید بذارید بین کلمات

      • Sadegh M ۲۳ مرداد ۱۳۹۷ / ۱۰:۴۹ ق٫ظ

        منم همین مشکل دوستمون رو دارم
        کاما میذارم هم کامای فارسی هم انگلیسی اما همچنان لیست رو خطی نشون میده
        اسپیس بین کلمه و کاما رو هم همه جوره امتحان کردم (هم با اسپیس هم بدون اسپیس) اما مشکل همچنان باقیست

    • Sadegh M ۲۳ مرداد ۱۳۹۷ / ۱۰:۵۲ ق٫ظ

      با نقطه ویرگول بجای ویرگول خالی مشکل حل میشه

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

        این موضوع کاملا بستگی به جداکننده آرگومان ها در فرمول نویسی داره (Separator) که ویندوز شما چی باشه.
        یا , هست یا ;

  • نگین مهربانی ۲۹ خرداد ۱۳۹۷ / ۱۰:۱۱ ق٫ظ

    سلام وقتتون بخیر
    گزینه data validation برای من فعال نیست؟ لطفا راهنمایی بفرمایید چه کاری باید انجام بدم؟
    ممنون

    • سامان چراغی ۲۹ خرداد ۱۳۹۷ / ۱۰:۲۲ ق٫ظ

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

      • نگین مهربانی ۲۹ خرداد ۱۳۹۷ / ۱۰:۴۰ ق٫ظ

        بسیار ممنون
        مشکل رفع شد:)

  • mojair ۱۹ اردیبهشت ۱۳۹۷ / ۱:۳۴ ب٫ظ

    با سلام و خسته نباشید یک مورد بود راهنمایی کنید ممنون میشم میخوام لیست وابسته ایجاد کنم اما طوری که مثلا وقتی نام فرد را از لیست انتخاب میکنم بصورت اتوماتیک نام پدر کد ملی و تاریخ تولد را در سایر سلول ها بیاره

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

      درود بر شما
      این سوال شما لیست وابسته نیست. فراخوان یاطلاعات هست که با Vlookup انجام میشه. در خصوص کار با Vlookup لینک زیر رو مطالعه کنید:
      https://excelpedia.net/vlookup-function/

  • امیر خلیلی ۲۵ بهمن ۱۳۹۶ / ۸:۳۹ ق٫ظ

    با سلام و تشکر از سایت خوبتون
    بنده میخوام لیست کشویی (بدون سلول کمکی) از سه رنگ قرمز ، زرد و سبز ایجاد کنم. که کاربر وقتی به ستون مد نظر میرسه یکی از سه رنگ موجود در لیست رو انتخاب کنه..ممنون میشم کمکم کنید

    • سامان چراغی ۲۵ بهمن ۱۳۹۶ / ۸:۵۹ ق٫ظ

      سلام
      روش دوم تو این آموزش همین مطلب رو توضیح داده شده لطفا دقیق مطالعه کنید

      • امیر خلیلی ۲۵ بهمن ۱۳۹۶ / ۱۰:۵۳ ق٫ظ

        مسئله من اینه که چطور در source رنگ را تعریف کنم..طبق روش دوم انجام دادم ولی رنگ ها رو نمیشناسه

        • حسنا خاکزاد ۲۵ بهمن ۱۳۹۶ / ۱۱:۲۳ ق٫ظ

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

  • هادی مهدی پور ۲۵ مهر ۱۳۹۶ / ۳:۵۸ ب٫ظ

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

    • سامان چراغی ۲۶ مهر ۱۳۹۶ / ۸:۳۶ ق٫ظ

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

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان