سبد خرید 0

وبلاگ

باکس جستجو در اکسل بصورت پیشرفته با توابع جستجو

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

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

  • ابزار Find
  • ابزار Advanced Filter که امکان جستجوی پیشرفته رو هم فراهم می کنه.
  • تابع VLOOKUP
  • تابع HLOOKUP
  • تابع FIND
  • تابع SEARCH
  • تابع MATCH
  • تابع INDEX
  • و…

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

ادغام اطلاعات چند سلول در یک سلول با تابع CONCATENATE :

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

فرم اطلاعات تماس مشتری در اکسل
شکل شماره 1 – شیت Data برای ذخیره اطلاعات تماس مشتری در اکسل

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

 ایجاد ستون کمکی برای نوشتن تابع CONCATENATE
شکل شماره 2 – شیت Data – ایجاد ستون کمکی برای نوشتن تابع CONCATENATE

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

حتما بخوانید:   add-ins و همه چیز درمورد افزونه ها در اکسل

استفاده از تابع FIND برای جستجو بین عبارات سلول ها:

خب تا اینجا ما تونستیم اطلاعات رو در یک سلول ترکیب کنیم.حالا یک شیت جدید به نام Search ایجاد کنید و جدولی دقیقا مثل شیت Data ایجاد کنید با این تفاوت که ستون های کمکی و اطلاعات مربوط به مشتری رو حذف کنید و و فقط عنوان ستون ها باقی بمونه.حالا فرمول سلول G3 در شیت Data رو به شکل زیر تغییر بدین و به سلول های پایینی درگ کنید:

شیت Search برای نمایش نتایج جستجو
شکل شماره 3 – شیت Search برای نمایش نتایج جستجو

الان اگر شما به شیت Search برید و در سلول A1 عبارتی رو وارد کنید، در شیت Data و در ستون G اون ردیف هایی که عبارت موجود در سلول A1 هست براتون شماره ردیفش رو میندازه.ما اینجوی متوجه میشیم که کدام ردیف ها جزء نتیجه جستجوی ماست.

جستجو و گزارش مخاطبین در اکسل
شکل شماره 4 – نتیجه جستجو در شیت Data

همانطور که در شکل بالا می بینید، ما در شیت Search و در سلول A1 عبارت “ان” را تایپ کردیم و در شیت Data اون ردیف هایی که شامل عبارت “ان” هستند با شماره مشخص شد.اما مشکلی که وجود داره این هست که در ستون کمکی1 بین شماره گذاری ها سلول خالی وجود دارد و ما باید کاری کنیم که این شماره گذاره از اولین سلول شروع بشه.پس در ستون کمکی2 و در سلول H3 فرمول زیر رو تایپ کرده و به سلول های پایینی درگ کنید:

حتما بخوانید:   ساخت لیست سفارشی در اکسل با ابزار Custom List
استفاده از تابع SMALL برای ترتیب شماره گذاری
شکل شماره 5 – استفاده از تابع SMALL برای ترتیب شماره گذاری

خب کار ما درشیت Data تموم شد.حالا میریم به شیت Search تا اطلاعات سرچ شده را لیست کنیم.برای اینکار به شیت Search رفته و در سلول A3 فرمول زیر را نوشته و در تمام سلول ها درگ کنید:

نتایج جستجو در سلول اکسل
شکل شماره 6 – شیت Search – نتایج جستجو در سلول اکسل

همانطور که در فرم جستجو شماره 6 مشاهده می کنید زمانی که در سلول A1 یک عبارت را تایپ می کنیم و Enter را می زنیم میره در شیت Data و در بین تمام سلول های محدوده، ردیف هایی که عبارت “ان” داخلش موجود هست رو برامون لیست میکنه.

ایجاد باکس جستجو در اکسل بصورت لحظه ای:

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

ایجاد باکس جستجو از تب Developer
شکل شماره 7 – ایجاد باکس جستجو از تب Developer

روی TextBox کلیک راست کرده و گزینه Properties را بزنید تا پنجره Properties باز شود.مقابل گزینه LinkedCell عبارت A1 را بنویسید تا به سلول A1 لینک شود.

لینک کردن TextBox به سلول شیت
شکل شماره 8 – لینک کردن TextBox به سلول شیت

اکنون در تب Developer با یکبار کلیک روی گزینه DesignMode، حالت طراحی را غیرفعال کنید.

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

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

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

اشتراک گذاری:

مطالب زیر را حتما مطالعه کنید

39 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

  • سلام خدا قوت
    چطور میشه این 5 فیلد رو گسترش داد برای 1000 سطر دیگه؟
    ممنون از راهنمایتون

        • سلام. وقت بخیر

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

  • درود و وقت به خیر
    شما در شیت دیتا بیس ایمیل ها رو هایپر لینک کردید، چطور میشه در شیت سرچ هم وقتی نتایج رو میاره هایپر لینک ها رو هم فراخوانی کنه؟
    ممنونم

    • سلام. وقت بخیر.

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

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

  • سلام
    من ی table درام تو ی شیت دیگه که محتواش مربوط به پذیرش خودرو هست و تو هر ثبت پذیرش ی سریال ساده هم براش تعلق میگیره حالا میخوام تو ی شیت دیگه با توجه به تاریخ پذیرش خودرو تعدا پذیرش و همینطور شماره سریال های مربوط به ی تاریخ خاص رو داشته باشم مثلا با وارد کردن تاریخ تو ی سلول تعدا پذیرش خودرو تو اون روز و همینطور شماره سریال ها رو بهم بده مشکل دوم اینکه پذیرش خودرو تو روزهای مختلف ثابت نیست و باید با توجه به تعداد پذیرش تو تاریخ مورد نظر سطر ایجاد بشه اگر براتون مقدور هست راهنمائی بکنید ممنون میشم

  • سلام
    آقا دمت گرم خیلی حال داد خیلی به من کمک کرد هم تو استفاده از فایل و هم استفاده برای فایل های دیگر
    خدا خیرت بده جوون

  • سلام یک سوال دارم. لطفا در صورت امکان راهنمایی کنید:
    در یک ردیف از A5 تا A15 نام شرکت های مختلف نوشته شده و از B5 تا B15 امتیاز انها بصورت عدد نوشته شده است. من نیاز دارم که با تابع LARGE(B1:B15;1) مقدار بالاترین امتیاز را استخراج کنم و همراه با ان نام شرکت که بالاترین امتیاز را گرفته استخراج شود. این دو یعنی امتیاز و نام شرکت در شیت دیگر نوشته شوند. تابع مناسب برای استخراج نام شرکت متناظر با بزرگترین امتیاز چیست؟
    با تشکر

    • سلام.وقت بخیر.

      اگر هدفتان فقط پیدا کردن بزرگترین امتیاز (غیرتکراری) هست.میتونید با ترکیب این تابع با توابعی مثل VlookUp,Match,Index نام شرکت رو هم پیدا کنید.

      مثال :

      (INDEX(A5:B15,MATCH(LARGE(B5:B15,1),B5:B15),1=

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

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

    • سلام. وقت بخیر

      از تب Formulas و گزینه Calculation بررسی کنید که محاسبه گر اکسل روی حالت دستی تنظیم نشده باشه.

  • این فرمول رو نوشتم ولی تو جستجو پیدا نمی کنه و باید فایل رو ببندو و باز کنم که در هنگام بازکردن پیغام آپدیت میاد و باید مسر فایل رو بدم تا جستجو انجام بشه
    IFERROR(INDEX(‘[Data Base]Data’!$A$3:$H$7,'[Data Base]Data’!$H3,COLUMN()),””)

    • احتمالا شما فرمول رو از فایل ما کپی کردین.چون آدرس فایل هم منتقل شده.

      فرمول رو مانند فرمول زیر اصلاح کنید:

      =IFERROR(INDEX(Data!$A$3:$H$7,Data!$H3,COLUMN()),"")

  • سلام.جناب مختاری من میخوام در شیتی که تکس فارسی نوشتم ،سه [حرف] مثل: ن،ت،خ باهم جستجو کنم،وقتی بین حروف فاصله میدم جستجو انجام نمیشه،خواهشا راهنمایی بفرمایید

    • سلام. وقت بخیر

      3 حرف همزمان نمیشه سرچ کرد. چون وقتی مقدار ن ت خ رو جستجو می کنید اون سه حرف رو یک کلمه درنظر میگره.

  • سلام من استفاده کردم خیلی ممنون فقط عیبش اینه که باید عین عبارت را جستجو کنیم مثلا من میخوام <> را جستجو کنم ولی اسم محصولم سینک ظرفشویی اخوان هست چطوری میشه عبارت <> را جستجو کنم و نمایش صحیح اتفاق بیوفته

    • سلام. وقت بخیر

      عین عبارت لازم نیست تایپ بشه.حتی اگر قسمتی از کلمه هم تایپ بشه سرچ انجام میشه.

      برای جستجوی مخالف هم میتونید از فرمول زیر الگو بگیرید :


      IFERROR(IF(ISERROR(SEARCH(Search!$A$1,CONCATENATE(A3,B3,C3,D3,E3,F3))),ROW()-2,""),"")=

      • سلام روز خوش
        درسته عین عبارت لازم نیست ولی اگر بینش یه کلمه جا بیوفته خروجی نمیده
        مثال: اگر اسم محصول سینک ظرفشویی اخوان باشه
        1.سینک ظرفشویی را سرچ کنیم: خروجی درسته
        2.سینک اخوان را سرچ کنیم: خروجی نمیده و پیدا نمیکنه
        من find را با search عوض کردم و جواب گرفتم میشه این:
        =IFERROR(IF(Search(Search!$A$1,CONCATENATE(A3,B3,C3,D3,E3,F3))>0,ROW()-2,””),””)
        اینطوری دیگه به حروف بزرگ و کوچیک هم حساس نیست فقط نکتش اینه که اگه کلمات پشت سر هم نباشن باید بین * قرار بگیرند برای سرچ به عنوان مثال *سینک*اخوان*
        بازم ممنون از همگی من کارم حل شد

  • =VLOOKUP(A12,’lf(and(q2f4<|3<b3,|,l3'!A:AO,9,0)
    سلام مجدد
    فرمولی که میخواهمبدانم دارد چه دستور یا فرمانی صادر میکند؟

    • سلام. وقت بخیر.

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

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

  • سلام مهندس مختاری
    من دوران قرنطینه نوروز رو با فایلهای آموزشی شما گذروندم و بسیار لذت بردم و یاد گرفتم
    از صبوری ، کیفیت و دقت آموزش شما بسیار سپاس گذارم
    سوالی برام پیش اومده ممنون میشم اگه راهنمایی کنید
    برای ترکیب if و find زمانی که شماره فاکتور رو پیدا نمیکنه چطور باید در vba عمل کرد که خطا نده
    من به صورت زیر نوشتم ولی درست نیست
    Sub search_factor2()

    ‘ search_factor2 Macro


    Dim factor_no2 As String
    factor_no2 = InputBox(“ÔãÇÑå ÝǘÊæÑ ÑÇ æÇÑÏ ˜äíÏ”, “ÌÓÊ æ Ìæí ÝǘÊæÑ”)
    Sheets(“Data_factor”).Select
    Columns(“K:K”).Select

    If Selection.Find(What:=factor_no2, After:=ActiveCell).Activate “” Then
    ActiveCell.Offset(-1, -10).Range(“A1:L35”).Select
    Selection.Copy
    Sheets(“ÝǘÊæÑ”).Select
    Range(“A1:L35”).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Sheets(“Data_factor”).Select
    Selection.Delete
    Sheets(“ÝǘÊæÑ”).Select
    Else
    MsgBox “ÚÈÇÑÊ ãæÑÏ äÙÑ íÇÝÊ äÔÏ”, “äÊíÌå ÌÓÊÌæ”
    End If

    End Sub

    • سلام. وقت بخیر

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

      on error resume next

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

    =IFERROR(IF(FIND(Search!$A$1,CONCATENATE(A3,B3,C3,D3,E3,F3))>,ROW()-2,””),””)

    • سلام.وقت بخیر

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

دیدگاهتان را بنویسید