اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
[email protected]

ورود یا ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • رسم نمودارهای حرفه ای
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • افزونه های کاربردی
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
  • درباره ما
    • Cart

      0

مسائل کاربردی

  • خانه
  • بلاگ
  • مسائل کاربردی
  • لیست قابل جستجو در اکسل

لیست قابل جستجو در اکسل

  • ارسال شده توسط حسنا خاکزاد
  • دسته بندی مسائل کاربردی
  • تاریخ ۲۰ خرداد ۱۳۹۸
  • نظرات ۲۶ دیدگاه ها
لیست فروریز قابل جست و جو
4.3 / 5 ( 7 امتیاز )

ایجاد لیست فرو ریز قابل جستجو در اکسل

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

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

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

گام اول: پیدا کردن عبارت مورد نظر

تابعی که میتونه جستجو کنه ببینه یک عبارت در یک سلول وجود داره یا نه تابع Find/Search هست. این دو تابع در مورد سرچ فارسی عینا مشابه عمل میکنن. در صورت پیدا کردن عبارت مورد نظر، خروجی عدد و در غیر اینصورت خطای #Value! خواهد بود.

پس با فرض اینکه لیست مورد نظر قراره در سلول D1 ایجاد بشه، فرمول زیر رو مینویسیم. (شکل 1)

=FIND ($D$1 , A2)

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

شکل 1- پیدا کردن عبارت مورد نظر در سلول های لیست

همونطور که میبینیم، در صورتی که عبارت مورد نظر پیدا بشه، خروجی عدد خواهد بود و در غیر اینصورت خطا.

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

گام دوم: شماره گذاری موارد پیدا شده

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

=IF ( ISNUMBER ( FIND ($D$1,A2) ) ,MAX ($B$1:B1) +1 ,”” )

در واقع تابع Isnumber چک میکنه که آیا خروجی تابع Find عدد هست یا نه. اگه عدد بود، ماکزیمم محدوده بالای سرشو باضافه 1 میکنه، اگر هم عدد نبود (خطا بود) خالی میذاره. همونطور که در شکل 2 میبینیم، مقابل دو استان که شامل عبارت “رد” هستن، یعنی اردبیل و کردستان، به ترتیب شماره 1 و 2 نمایش داده می شه.

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

شکل 2- ایجاد شماره پشت سر هم برای سلول هایی که شامل عبارت مورد نظر هستند

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

حالا کافیه که سلول های مشخص شده رو پشت سر هم لیست کنیم. برای این کار میتونیم از Backward Vlookup استفاده کنیم. یا اینکه از ترکیب Match و Index استفاده کنیم:

=IFERROR ( INDEX ($A$2:$A$32, MATCH ( ROW(A1) , $B$2:$B$32, 0)) , “”)

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

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

این فرمول شماره های ایجاد شده (که به ترتیب هستن) رو فراخوانی میکنه و محتوای موجود در سلول روبروی اونها رو نمایش میده. که در واقع خواسته ما هم همینه و میخوایم لیست استانهایی که شامل عبارت مورد جستججو هستن رو پشت سر هم داشته باشیم.

گام چهارم: نام گذاری پویا برای محدوده لیست

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

حتما بخوانید:  مقایسه نحوه عملکرد واقعی و برنامه ای با نمودار در اکسل

از تب Formula  گزینه Name Manager  رو کلیک میکنیم و یک نام به عنوان List ایجاد میکنیم و فرمول زیر رو در اون مینویسیم. مطابق شکل 4.

=OFFSET ( Sheet2!$C$2,0,0, COUNTIF ( Sheet2!$C$2:$C$32 , “?*” ) ,1)

برای درک بهتر محدوده های نامگذاری داینامیک، مقاله مربوط به Offset رو مطالعه کنید.

نکته:
تابع COUNTIF( Sheet2!$C$2:$C$32 , “?*” ) تعداد سلول های پر که داده نمایش میدن رو شمارش میکنه و کاری به سلول هایی که با فرمول پر شدن ولی خالی نمایش داده میشن نداره.

 

نامگذاری محدوده بصورت داینامیک

شکل 4- لیست فروریز قابل جست و جو – نامگذاری محدوده بصورت داینامیک

گام پنجم: ایجاد لیست کشویی

کافیه که نام ایجاد شده رو به Data Validation اختصاص بدیم. برای این کار روی سلول D1 کلیک کرده و از تب Data گزینه Data Validation رو انتخاب میکنیم. از تب Settings گزینه List رو انتخاب میکنیم و نام تعیین شده که در گام چهارم فرمول نویسی کردیم رو تخصیص میدیم. مطابق شکل 5

تخصیص محدوده نامگذاری شده به دیتا ولیدیشن

شکل 5- تخصیص محدوده نامگذاری شده به دیتا ولیدیشن

قبل از اینکه Ok رو بزنیم، به تب Error Alert رفته و تیک گزینه اخطار رو برمیداریم و بعد Ok رو میزنیم. مطابق شکل 6

برداشتن تیک خطا دهی

شکل 6- لیست فروریز قابل جست و جو – برداشتن تیک خطا دهی

حالا کافیه عبارت مورد نظر رو در سلول D1 تایپ کنیم و بعد لیست فروریز رو باز کنیم. می بینیم که  فقط سلول هایی که شامل اون عبارت هستن در لیست نمایش داده میشه. به تصویر زیر دقت کنید:

لیست فرو ریز قابل جستجو در اکسل

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

حتما بخوانید:  تابع جالب Cell در اکسل

دانلود فایل اکسل لیست قابل جستجو در اکسل

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

با عضویت در سایت به صورت مستقیم دانلود کنید

  • ارسال به ایمیل

Des

دریافت فایل اکسل لیست قابل جست و جوبرای دانلود فایل کلیک کنید

برچسب:تابع Find, تابع IF, تابع Iferror, تابع Index, تابع Match, تابع Offset

  • اشتراک گذاری:
profile avatar
حسنا خاکزاد
فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

مطلب قبلی

فرم ورود داده در اکسل
۲۰ خرداد ۱۳۹۸

مطلب بعدی

ترکیب جداول در اکسل با استفاده از Power Query
۲۰ خرداد ۱۳۹۸

ممکن است همچنین دوست داشته باشید

External-Links-Cover
مدیریت لینک ها در اکسل
۲۷ بهمن, ۱۳۹۹
WildCards-Cover
جستجو پیشرفته در اکسل با استفاده از Wildcard
۲۲ بهمن, ۱۳۹۹
Conditional-Formatting-Nth-Large-Small-Cover
تفکیک اعداد با استفاده فرمت دهی شرطی
۴ بهمن, ۱۳۹۹

    26 نظر

  1. profile avatar
    علی
    ۱۱ بهمن ۱۳۹۹
    پاسخ

    سلام
    من یک فایل اکسل تردد و حقوق و دستمزد طراحی کردم که فقط ورود و خروج پرسنل رو میزنم و گزارش کارکرد روزانه و ماهانه و حقوق و دستمزد (با توجه به حکمش) و بیمه و مالیات وغیره رو خودش خودکار محاسبه میکنه
    دستگاه تردد هم گرفتیم برا شرکت ولی برنامه نداره
    میخواستم بدونم ممکن هست که این برنامه تحت اکسلی که من نوشتم اطلاعات ورود و خروج رو به صورت اتومات از دستگاه تردد بگیره و ثبت کنه؟

    • profile avatar
      حسنا خاکزاد
      ۲۹ بهمن ۱۳۹۹
      پاسخ

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

  2. profile avatar
    بهمن
    ۲۰ دی ۱۳۹۹
    پاسخ

    سلام
    وقت بخیر
    بسیار عالی و کاربردی بود ممنونم

  3. profile avatar
    جواد محمدی
    ۱۱ آذر ۱۳۹۹
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۱۱ آذر ۱۳۹۹
      پاسخ

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

      پیوت تیبل هم میتونه کمک کنه بهتون

  4. profile avatar
    araste
    ۱۲ مرداد ۱۳۹۹
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۱۳ مرداد ۱۳۹۹
      پاسخ

      درود بر شما
      برای جستجوی موارد مرتبط باید از تابع Vlookup استفاده کنید

  5. profile avatar
    Ts.Scania
    ۶ اردیبهشت ۱۳۹۹
    پاسخ

    ممنون از زحمات شما ، فایل دانلود شده کار نمیکنه!

    • profile avatar
      حسنا خاکزاد
      ۷ اردیبهشت ۱۳۹۹
      پاسخ

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

  6. profile avatar
    طهماسبی
    ۲ اردیبهشت ۱۳۹۹
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۲ اردیبهشت ۱۳۹۹
      پاسخ

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

      • profile avatar
        طهماسبی
        ۲ اردیبهشت ۱۳۹۹
        پاسخ

        با تشکر از دقت نظرتان
        دقیقا همینطور هست . یه جدول مرجع از 800 نماد در بورس هست . و در جدول دیگر من نمادهای انتخابی خودم رو وارد میکنم بطوریکه سلول نماد بصورت لیست کشویی هست که با توجه به تعداد زیاد فرصت زیادی گرفته میشود و زیاد خوب نیست . به همین منظور اگر این لیست قابلیت سرچ داشته باشه ورود اطلاعات با سهولت انجام میپذیرد.
        و می خواهم در همون سلول سرچ کنم نه اینکه سرچ را یه جای دیگه انجام داد ( فکر کنم اینکار رو در vba انجام داده شود بهتر باشد)
        با تشکر از شما لطفا راهنمایی بفرمایید.
        درصورت امکان قابیلت ارسال فایل رو هم دارم

        • profile avatar
          حسنا خاکزاد
          ۳ اردیبهشت ۱۳۹۹
          پاسخ

          خواهش مکینم. عرض کردم راه حل رو… میتونید از تابع cell برای پیدا کردن سلول فعال استفاده کنید و این اموزش رو تکمیل کنید برای خودتون
          حالا یا فرمول، یا کدنویسی، هر کدوم مسلط تر هستید انتخاب کنید.

          • profile avatar
            طهماسبی
            ۳ اردیبهشت ۱۳۹۹

            از راهنمایی تون بسیار سپاسگزارم

  7. profile avatar
    فاطمه
    ۹ دی ۱۳۹۸
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۹ دی ۱۳۹۸
      پاسخ

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

  8. profile avatar
    ایمان قربانی
    ۷ مهر ۱۳۹۸
    پاسخ

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

  9. profile avatar
    احسان
    ۲۴ مرداد ۱۳۹۸
    پاسخ

    سلام وقت بخیر.
    یک صفحه داریم که دو ستون دارد.ستون اول تاریخ روز و ستون دوم قیمت روز.
    می خواهیم بیشترین مقدار قیمت هر ماه با تاریخ متعلق به آن روز در دو ستون دیگه نمایش داده بشه.ولی داده ها برای بیست سال می باشد و همچنین تعدادزیادی فایل.با استفاده از از فرمول =INDEX(…,MATCH(MAX(…..),…..,0)) ( روزهای پنجشنبه و جمعه و تعطیلات داده نداریم ) بیشترین قیمت هر ماه را بدست میارم که بسیار وقت گیر هست. این داده ها برای موارد زیادی هم هست که کار رو خیلی سخت تر میکنه.اگر امکان دارد راهنمایی بفرمایید.متشکرم.

    • profile avatar
      حسنا خاکزاد
      ۵ شهریور ۱۳۹۸
      پاسخ

      درود بر شما
      بستگی داره که تاریخ میلادی باشه یا شمسیو اگه شمسی هست با چه فرمتی…
      بهتره سوال رو در مقیاس کوچک مطرح کنید و مثال بزنید که خواستتون واضح بشه

  10. profile avatar
    a
    ۲۴ تیر ۱۳۹۸
    پاسخ

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

  11. profile avatar
    Fahime4127
    ۴ تیر ۱۳۹۸
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۴ تیر ۱۳۹۸
      پاسخ

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

      https://excelpedia.net/product/start-excel/

      الان هم جشنواره هست و تخفیف خورده محصولات

  12. profile avatar
    ملیحه
    ۲۳ خرداد ۱۳۹۸
    پاسخ

    با تشکر از شما بابت پاسخگویی سریعتون
    از تابع IF استفاده کردم و همچنین ترکیبش با توابع SUM , COUNTIF
    مساله ای که دارم این هست که تابع خیلی طولانی میشه
    به اینصورت که اگر به طور مثال عدد سلول 3 باشه، با تابع کانت ایف باید بررسی بشه که تعداد خونه های غیر صفر از از ابتدای ردیف 3 هست یا خیر که اگه نباشه باید یک سلول دیگه به رنج کانت ایف اضافه شه و باز بررسی صورت بگیره و همین طور تا آخر
    و این خیلی طولانی هست و امکان اشتباه زیاده.

    • profile avatar
      حسنا خاکزاد
      ۲۳ خرداد ۱۳۹۸
      پاسخ

      جزئیات سوال مشخص نیست
      باید ساختار و خواسته دقیق مشخص باشه
      چیزی که برداشت کردم بدون یاز به if و با offset شدنی هست
      باز هم برای ارائه جزئیات بیشتر به گروه تلگرامی برید و اونجا توضیحات به همراه عکس یا فایل بذارید تا دوستان راهنمایی کنن

  13. profile avatar
    ملیحه
    ۲۳ خرداد ۱۳۹۸
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۲۳ خرداد ۱۳۹۸
      پاسخ

      درود بر شما
      از تابع Offset کمک بگیرید و برای صفر بودن مقادیر، تعدادشو با countif حساب کنید و به مقدار مورد نظر اضافه کنید

      https://excelpedia.net/offset-function/

نظر بدهید لغو پاسخ

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

-- بارگیری کد امنیتی --

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

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

آخرین مطالب

مقادیر متمایز در اکسل
تفاوت داده Distinct و Unique چیست؟ چطور میشه مشخص کرد
۱۰فروردین۱۴۰۰
استفاده از چندین تابع IF اکسل در هم
توابع IF تو در تو و نکات مهمی که باید بدانید
۲۷اسفند۱۳۹۹
تابع Trim
تابع Trim در اکسل
۲۱اسفند۱۳۹۹
کاربرد Power Query
چند مثال کاربردی از ابزار Power Query
۱۵اسفند۱۳۹۹

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

پاسخ سوالات شما

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

کانال و تالار گفتمان اکسل پدیا

ورود به تالار گفتگو تلگرام

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

  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

021-47625755

[email protected]

آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

دانلود اپ اکسل پدیادانلود از کافه بازار

تمامی حقوق برای اکسل پدیا محفوظ است.

  • اکسل از راه دور
  • شرایط و قوانین
  • درباره ما
  • تماس با ما