ساخت لیست کشویی در اکسل

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

در این ویدئو درباره ساخت لیست کشویی در اکسل با قابلیت جستجو در آن لیست صحبت می کنیم.

ساخت لیست کشویی در اکسل

این امکان که اصطلاحاً Google Search می گویند؛وقتی شما در گوگل جستجو را انجام می دهید، ابتدای حروف را که در گوگل وارد می کنید بقیه اش را برای تان می آورد. یا مثلاً لیست تان را فیلتر می کنید و آیتم های پیشنهادی را به آن هایی که آن اسم داخلش هست محدود می شود. در این ترفند می خواهیم این کار را انجام دهیم.

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

ساخت لیست های کشویی
ساخت لیست کشویی در اکسل

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

  1. تابع Srearch
  2. تابع max
  3. تابع Isnumber
  4. تابع Rows
  5. تابع vlookup
  6. تابع Countif
  7. تابع Offset
  8. تابع Iferror

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

1- قدم اول: لیست کشویی در اکسل

در مرحله اول لیست کشویی با data Validation می سازیم. برای اینکه با وارد نمودن مقدار با خطا مواجه نشویم تیک خطای ….Show Error Alert را مطابق شکل زیر بر می داریم:

لیست کشویی در اکسل
ساخت لیست کشویی در اکسل

2- قدم دوم : تابع Search

تابع search این موضوع را بررسی می کند که آیا رشته ورودی در سلول مدنظر وجود دارد یا نه. اگر وجود داشت مکان کاراکتر مدنظر را به عنوان خروجی نمایش می دهد در غیر این ضورت به صورت خطا نمایش خواهد داد.

SEARCH(find_text,within_text,[start_num])

آرگومان find_text: این آرگومان و جزء از تابع Search آنچه که ما به دنبال آن هستیم را دریافت می کند.

آرگومان within_text : سلولی که آرگومان اول در آن قرار است جستجو شود.

آرگومان [start_num] : این آرگومان اختیاری می باشد. این آرگومان کاراگتر شروع جستجو در سلول مرجع را مشخص می کند. مثلا اگر این آرگومان را 3 وارد کنیم به معنای این هست که 2 کاراکتر اول را در نظر نگیرد.

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

تابع Search
تابع Search در اکسل

3- قدم سوم: تابع IsNumber

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

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

ISNUMBER(value)

حالا ما آنچه تابع Search به عنوان خروجی نمایش داده است را بررسی می کنیم که آیا خروجی تابع Search عدد هست یا خطا. برای اعداد مقدار True و برای مقادیر غیرعددی False را نمایش می دهد.

4- قدم چهارم : تابع If

تابع If را به تفصیل در مقاله تابع if مورد بررسی قرار دادیم. اجزای آرگومان های تابع if به شکل زیر می باشد:

IF(logical_test, value_if_true, [value_if_false])


تابع If در اکسل

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

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

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

5- قدم پنجم : تابع max

این تابع بیشترین مقدار را نمایش می دهد. آرگومان های تابع max مقادیر عددی یا سلول حاوی عدد می باشد.

MAX(number1, [number2], …)

برای اینکه بتوانیم آن سلول هایی که حاوی مقدار مدنظر ما می باشد را از لیست اولیه خارج نماییم با استفاده از تابع max این چنین می نویسیم:

Max($B$1:B1)+1

تابع Max
تابع Max

همان گونه که در فرمول مشاهده می کنید B1 اولی به صورت مطلق و B1 دومی به صورت نسبی می باشد. دلیل این کار را در ویدئو به تفصیل توضیح داده ایم.


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

6- قدم ششم : تابع Vlookup

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

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

این تابع شامل 4 آرگومان می باشد.

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])


تابع vlookup

برای آرگومان اول تابع Vlookup باید ورودی بسازیم. باید اعداد 1 ، 2 و… که در مرحله قبل ساختیم را ایجاد نماییم لذا برای این کار از تابع Rows استفاده می کنیم.

برای آرگومان دوم تابع Vlookup لیست داده های اولیه مان را انتخاب می کنیم. برای شماره ستون هم عدد 2 و مقدار آرگومان آخر هم مقدار False یا صفر را وارد می کنیم.

7- قدم هفتم : تابع Rows

تابع Rows تعداد ردیف هایی که انتخاب نموده ایم را برای ما مشخص می کند. همان گونه که در ویدئو هم مشاهده کردید و در قدم قبلی گفتیم برای اینکه ورودی تابع Vlookup را بسازیم که عددی از 1 تا آخرین مقدار می باشد از تابع Rows استفاده می کنیم.

ROWS(array)

لیست کشویی حرفه ای  تابع Rows
تابع Rows

تابع Rows شامل ناحیه ای می باشد که از سلول F2 شروع می شود. تابع Rows تعداد سلول را شمارش می کند. مثلا وقتی به سلول f4 می رسیم فرمول می شود :

Rows ($F$2:F4 ) = 3

این مقدار می شود ورودی تابع Vlookup که در قسمت قبلی صحبت کردیم. اما همان گونه که می بینید بعد از اینکه موارد موجود تمام می شود خطای N/A# را نمایش می دهد برای این موضوع از تابع iferror استفاده می کنیم.

8- قدم هشتم : تابع iferror

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

IFERROR(value, value_if_error)

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

=IFERROR(VLOOKUP(ROWS($F$2:F2),$B$2:$C$12,2,0),””)


ترکیب تابع IFERROR و vLOOKUP

اما مرحله آخربا استفاده از تابع offsset هست.

9-قدم نهم : تابع offset

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

بیشتر بخوانید:   ترکیب تابع index و match در اکسل

اجزای تابع offset به شرح زیر هست:

OFFSET(reference, rows, cols, [height], [width])

به زبان ساده با این تابع از یک سلول (یا محدوده) به یک سلول (یا محدوده ) دیگر می رسید.

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

آرگومان های rows, cols : این دو آرگومان تعداد ردیف و ستونی که باید طی شود تا به نقطه ثانویه برسیم.

آرگومان های [height], [width] : بعد از اینکه با استفاده از آرگومان های دوم و سوم به نقطه ثانویه رسیدیم با این دو آرگومان اختیاری می توانیم حجم ناحیه یعنی اینکه طول و عرض ناحیه را مشخص کنیم.

اما در این مثال:

لیست کشویی با تابع offset
تابع Offset

آرگومان اول را سلول F2 قرار می دهم، مقادیر ردیف و ستون را خالی می گذاریم ولی برای محاسبه ارتفاع محدوده از تابع Countif استفاده می کنیم.

10- قدم دهم : تابع Countif

برای محاسبه آرگومان [height] در تابع Offset از تابع Countif و wildcard  ها استفاده کنیم.

COUNTIF(range, criteria)

آرگومان اول تابع countif ناحیه شمارش و آرگومان دوم شرطش می باشد. یعنی اینکه آنهایی از ناحیه مدنظر که دارای شرط هستند را می شمارد.

حالا برای این مثالی که داریم بررسی می کنیم شرطی که تعریف کردیم با استفاده از  wildcard ها ( کاراکترهای مخصوصی در اکسل هستند که می توانند به جای سایر کاراکترها در هنگام جستجو، فیلتر کردن و … استفاده شوند )

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

COUNTIF($F$2:$F$12,”?*”)

در آخرین مرحله ساخت لیست کشویی در اکسل هم فرمول زیر را باید کپی کنیم و در تنظیمات لیست کشویی که با data VALIDATION ساختیم قرار دهیم:

=OFFSET(F2,,,COUNTIF($F$2:$F$12,”?*”))

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

حالادیگر لیست تان آماده است.

بیشتر بخوانید:

راهنمای جامع فرمت سل

راهنمای جامع pIVOT tABLE

8 دیدگاه

  1. با سلام .چطور میشه لیست کشویی را در تعداد سطر بیشتری قرار داد بطوریکه محدوده جستجو ثابت وفقط ردیف های قرارگیری لیست کشویی افزایش یابد. با تشکر

  2. سلام
    وقتتون بخیر
    اگر منظورتون اینه که سلولهای بیشتری دارای لیست کشویی باشند ، شما میتونید محدوده مورد نظری که میخاید سلولهاش بصورت لیست کشویی باشد رو انتخاب و بعد از اون data validation را انتخاب کنید

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

  4. سلام. وقت تون بخیر.
    اگر منظورتون این هست که برای سلول اولی تنظیمات و فرمول نویسی را انجام بدیم بعد با کشیدن برای بقیه سلول ها امکان پذیر باشد که این امکان وجود دارد.

    تنظیمات بیشتری که مدنظرتان هست را بفرمایید تا راهنمایی نماییم.

  5. سلام.خیلی ممنون از مطالب مفیدتون
    من یک مشکل دارم ممنون میشم کمکم کنید
    ببینید وقتی یک لیست کشویی درست کردم میخوام وقتی روی هر کدومش که کلیک میکنم داخل سلول دیگه قیمت رو نمایش بده مثلا ستونی از آب نوشابه دوغ دارم وقتی که آب رو انتخاب کردم داخل سلول انتخابی قیمتش رو نمایش بده خیلی ممنون

    1. سلام. این مشکلی نداره.

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

      بعدش که لیست کشویی رو ساختید می تونید با توابع جستجو مثل vlookup یا ترکیب indexو Match خروجی رو به بهترین شکل داشته باشید.

      https://exceltip.ir/%d8%aa%d8%a7%d8%a8%d8%b9-vlookup-%d8%a7%da%a9%d8%b3%d9%84-%d9%88-%d8%b7%d8%b1%db%8c%d9%82%d9%87-%d8%a7%d8%b3%d8%aa%d9%81%d8%a7%d8%af%d9%87-%d8%a7%d8%b2-%d8%a2%d9%86/

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

پاسخی بگذارید

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