هدف اصلی دفتر برنامه های اکسل- انجام محاسبات سند این برنامه (کتاب) می‌تواند حاوی صفحات زیادی با جداول طولانی پر از اعداد، متن یا فرمول باشد. خودکار جستجوی سریعبه شما امکان می دهد سلول های لازم را در آنها پیدا کنید.

جستجوی ساده

برای جستجوی یک مقدار در صفحه گسترده اکسل، باید لیست کشویی ابزار "Find and Replace" را در تب "Home" باز کنید و روی مورد "Find" کلیک کنید. همین اثر را می توان با استفاده از میانبر صفحه کلید Ctrl + F به دست آورد.

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

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

جستجوی متن نیز به همین روش انجام می شود. در این حالت متن جستجو شده در نوار جستجو تایپ می شود.

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

جستجوی پیشرفته

فرض کنید می خواهید تمام مقادیر را در بازه 3000 تا 3999 بیابید. در این صورت، 3??? را در نوار جستجو تایپ کنید. حروف عام "?" جایگزین هر دیگری می شود

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

شما می توانید با نادیده گرفتن نتایج نادرست، از اکثر نتایج به دست آمده راضی باشید. اما عملکرد جستجو در اکسل 2010 می تواند بسیار دقیق تر کار کند. ابزار Options در کادر محاوره ای برای این منظور است.

با کلیک بر روی گزینه ها، کاربر امکان جستجوی پیشرفته را دارد. اول از همه، بیایید به آیتم "Search Area" توجه کنیم که به طور پیش فرض روی "Formulas" تنظیم شده است.

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

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

این نتیجه را می توان با انتخاب فوری مورد "کل سلول" (حتی ترک مقدار "فرمول" در "منطقه جستجو") به دست آورد.

حالا بیایید به مورد "جستجو" بپردازیم.

اگر به جای پیش‌فرض «On Sheet»، «In Workbook» را انتخاب کنید، دیگر نیازی به حضور در صفحه سلول‌های مورد نظر شما نیست. اسکرین شات نشان می دهد که کاربر جستجو را در حالی که در برگه خالی 2 بوده آغاز کرده است.

مورد بعدی در پنجره جستجوی پیشرفته "مشاهده" است که دو معنی دارد. پیش فرض "با ردیف" است، به این معنی که سلول ها سطر به ردیف اسکن می شوند. انتخاب یک مقدار متفاوت - "بر اساس ستون" - فقط جهت جستجو و ترتیب نتایج را تغییر می دهد.

هنگام جستجو در اسناد مایکروسافت اکسل، همچنین می توانید از علامت عام دیگری استفاده کنید - "*". اگر "?" به معنای هر کاراکتری است، سپس "*" جایگزین یک کاراکتر نمی شود، بلکه هر تعداد کاراکتر را جایگزین می کند. در زیر تصویری از جستجوی لوئیزیانا را مشاهده می کنید.

گاهی اوقات لازم است در هنگام جستجو مورد شخصیت ها را در نظر بگیرید. اگر کلمه louisiana بزرگ باشد، نتایج جستجو تغییر نمی کند. اما اگر در پنجره جستجوی پیشرفته "Match case" را انتخاب کنید، جستجو ناموفق خواهد بود. این برنامه کلمات لوئیزیانا و لوئیزیانا را متفاوت در نظر می گیرد و طبیعتاً اولین آنها را نخواهد یافت.

انواع جستجو

جستجو برای مسابقات

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

نتیجه در تصویر زیر نشان داده شده است.

در صورت لزوم، کاربر می تواند رنگ نمایش بصری سلول های همسان را تغییر دهد.

فیلتراسیون

نوع دیگر جستجو فیلترینگ است. بیایید فرض کنیم که کاربر می خواهد مقادیر عددی را در بازه 3000 تا 4000 در ستون B پیدا کند.

همانطور که می بینید، تنها ردیف هایی نمایش داده می شوند که شرایط وارد شده را برآورده کنند. بقیه به طور موقت پنهان شدند. برای بازگشت به حالت اولیه، مرحله 2 را تکرار کنید.

گزینه های مختلف جستجو با استفاده از Excel 2010 به عنوان مثال مورد بحث قرار گرفت. چگونه در اکسل نسخه های دیگر جستجو کنیم؟ در انتقال به فیلتر کردن در نسخه 2003 تفاوت وجود دارد. در منوی "داده"، باید به ترتیب دستورات "Filter"، "Auto Filter"، "Condition" و "Custom Auto Filter" را انتخاب کنید.

ویدئو: جستجو در جدول اکسل

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

جستجوی همزمان در چندین محدوده

برای مثال بصری، بیایید سه جدول مجزای ساده ایجاد کنیم که در محدوده‌های غیر مجاور یک صفحه قرار دارند:

شما باید مقدار مورد نیاز برای تولید 20 محصول را جستجو کنید. متأسفانه این داده ها در ستون ها و ردیف های مختلف قرار دارند. بنابراین، ابتدا باید بررسی کنید که تولید این محصولات چقدر طول می کشد (جدول اول).

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

کاربر عادی اکسل به دنبال راه حلی با استفاده از توابع مبتنی بر فرمول مانند VLOOKUP است. و جستجو را در 3 مرحله (به طور جداگانه برای هر جدول) انجام می داد. به نظر می رسد که می توانید با انجام جستجوی تنها در 1 مرحله با استفاده از یک فرمول خاص، بلافاصله یک نتیجه آماده دریافت کنید. برای این:

1. در سلول E6 مقدار 20 را وارد کنید که شرط جستجو است.
2. در سلول E7 فرمول زیر را وارد کنید:

هزینه تولید 20 عدد یک محصول خاص

نحوه کار فرمول با VLOOKUP در چندین جدول:

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

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

با استفاده از این اصل، می توانید از فرمول های تابع VLOOKUP از چندین برگه استفاده کنید.

بعد از ظهر بخیر، ساکنان عزیز هابرو!

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

جستجو تنها یک مقاله در مورد Habré با موضوع مشابه به من داد - «تلمود با استفاده از فرمول‌ها در صفحه گسترده Google». توضیحات خوبی از موارد اساسی برای کار در اکسل ارائه می دهد (اگرچه 100٪ در مورد خود اکسل نیست).

بنابراین، پس از جمع آوری مجموعه ای از درخواست ها / وظایف، این ایده به وجود آمد که آنها را تایپ کنید و پیشنهاد کنید. راه حل های امکان پذیر(البته همه چیز ممکن نیست، اما به سرعت نتیجه می دهد).

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

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

مورد 1: توابع منطقیو توابع تطبیق
"من مجموعه ای از مقادیر را در یک جدول دارم و لازم است که وقتی یک شرط / مجموعه شرایط خاص برآورده شد، یک مقدار مشخص نمایش داده شود" (ج) کاربر

داده ها معمولاً به صورت جدول ارائه می شوند:

وضعیت:

• اگر مقدار ستون "کمیت" بیشتر از 5 باشد،
• سپس باید مقدار "بدون سفارش" را در ستون "نتیجه" نمایش دهید،

دستور فرمول به صورت زیر است:
IF (expression_logical، [value_if_true]، [value_if_false])

• عبارت منطقی عبارتی است که به TRUE یا FALSE ارزیابی می شود.
• Value_if_true - مقداری که در صورت درست بودن عبارت منطقی چاپ می شود
• Value_if_false - مقداری که در صورت نادرست بودن عبارت منطقی چاپ می شود

=IF (C5> 5، "بدون نیاز به سفارش"، "سفارش لازم است")

این اتفاق می افتد که شرایط پیچیده تر است، به عنوان مثال، تحقق 2 یا چند شرط:

• اگر مقدار در ستون "تعداد" بزرگتر از 5 باشد و مقدار در ستون "نوع" "A" باشد.

• Boolean_value1-2 و غیره - شرایطی که باید آزمایش شود، که ارزیابی آن به مقدار TRUE یا FALSE منجر می شود

=IF(AND(C2>5،B2="A")،1،0)

بیایید سعی کنیم کار را پیچیده کنیم - یک شرط جدید:

• اگر مقدار در ستون "تعداد" 10 و مقدار در ستون "نوع" "A" باشد.
• یا مقدار در ستون Quantity بزرگتر یا مساوی 5 است و مقدار Type B است
• سپس باید مقدار "1" را در ستون "نتیجه" نمایش دهید، در غیر این صورت "0".

=IF(OR(AND(C2=10،B2="A"); AND(C2>=5،B2="B"))،1،0)

حالا بریم سراغ وضعیت بعدی:
بیایید تصور کنیم که بسته به مقدار در ستون "شرط"، یک شرط خاص باید در ستون "نتیجه" نمایش داده شود؛ در زیر مطابقت بین مقادیر و نتیجه است.
وضعیت:

• 1 = A
• 2 = B
• 3 = ب
• 4 = G

=IF(A2=1، «A»، IF(A2=2، «B»، IF(A2=3، «C»، IF(A2=4، «D»0))))

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

راه حل جایگزین_1:
با استفاده از فرمول CHOOSE
نحو توابع:
SELECT(شماره_شاخص، مقدار1، [مقدار2]، ...)

• Index_number - تعداد آرگومان مقدار انتخاب شده. عدد شاخص باید عددی بین 1 تا 254، فرمول یا مرجعی به سلولی حاوی عددی بین 1 تا 254 باشد.
• Value1, value2,... - مقداری از 1 تا 254 آرگومان مقدار، که از آن تابع "SELECT" با استفاده از شماره شاخص، مقدار یا عمل مورد نظر را انتخاب می کند. آرگومان ها می توانند اعداد، ارجاعات سلولی، نام های خاص، فرمول ها، توابع یا متن باشند.

• 1 = A
• 2 = B
• 3 = ب
• 4 = G

=CHOICE(A2، "A"، "B"، "C"، "D")

• 1 = A
• 2 = B
• 3 = ب
• 5 = G

=CHOICE(A31، «A»، «B»، «C»، «D»)

راه حل جایگزین_2:
در اینجا به یکی از محبوب ترین ها می رسیم توابع اکسل، تسلط بر آن به طور خودکار هر کارمند اداری را به "کاربر اکسل با تجربه" / طعنه / تبدیل می کند.
نحو فرمول:
VLOOKUP(مقدار_جستجو، جدول، شماره_ستون، [بازرسی_فاصله])

• Search_value - مقداری که توسط تابع جستجو می شود.
• جدول، محدوده ای از سلول های حاوی داده است. در این سلول ها است که جستجو انجام می شود. مقادیر می توانند متن، عددی یا بولی باشند.
• Column_number - شماره ستونی در آرگومان "Table" که در صورت وجود مطابقت، مقدار از آن مشتق می شود. درک این نکته مهم است که ستون ها نه در امتداد شبکه صفحه عمومی (A.B، C، D و غیره)، بلکه در داخل آرایه مشخص شده در آرگومان "Table" شمارش می شوند.
• Interval_lookup - تعیین می کند که آیا تابع باید مطابقت دقیق یا تقریبی را پیدا کند.

سپس در قسمت کار جدول، فرمولی با پیوند به کتاب مرجع که قبلاً پر شده است، یادداشت می شود. آن ها در دایرکتوری، در ستون "D" مقدار ستون "A" جستجو می شود و هنگامی که مطابقت یافت می شود، مقدار ستون "E" در ستون "B" نمایش داده می شود.
نحو فرمول:
خروجی نتیجه در سلول B2:

حال شرایطی را تصور کنید که در آن باید داده ها را از جدول دیگر به یک جدول بکشید، اما جداول یکسان نیستند. مثال زیر را ببینید

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

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

=VLOOKUP($A3،$H$3:$M$6، COLUMN()،0)

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

• اگر عدد 1 در ستون "Directory" نشان داده شده است، داده ها باید از جدول "Directory_1" و اگر عدد 2 است، سپس از جدول "Directory_2" مطابق با ماه مشخص شده خارج شوند.

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

=IF($B3=1; VLOOKUP($A3,$G$3:$I$6; COLUMN()-1,0); VLOOKUP($A3,$K$3:$M$6; COLUMN()-1;0 ))

=VLOOKUP($A3, SELECT($B3,$G$3:$I$6,$K$3:$M$6), COLUMN()-1,0)

پاداش: VLOOKUP بر اساس دو یا چند ویژگی در آرگومان "search_value".
وضعیت:

• بیایید تصور کنیم که مثل همیشه آرایه ای از داده ها را به صورت جدولی داریم (اگر نه، داده ها را به آن ارائه می دهیم)، باید مقادیری را از آرایه بر اساس ویژگی های خاص به دست آوریم و آنها را در شکل جدولی دیگری قرار دهیم. .

همانطور که می توان از فرم های جدولی، هر کالا نه تنها یک نام دارد (که منحصر به فرد نیست)، بلکه متعلق به یک کلاس خاص است و گزینه بسته بندی خاص خود را دارد.
با استفاده از ترکیبی از نام و کلاس و بسته‌بندی، می‌توانیم یک مشخصه جدید ایجاد کنیم؛ برای این کار، در جدول با داده‌ها یک ستون اضافی «ویژگی اضافی» ایجاد می‌کنیم که با استفاده از فرمول زیر آن را پر می‌کنیم:

=CONCATENATE(H3;"_";I3;"_";J3)

=IFERROR(VLOOKUP(A2&"_"&B2&"_"$G$2:$K$6,5,0)،0)

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

Case_3 جستجوی یک مقدار در یک آرایه، یا زمانی که VLOOKUP نمی تواند به ما کمک کند

بیایید وضعیتی را در نظر بگیریم که در آن باید بفهمیم که آیا آرایه سلولی حاوی مقادیر مورد نیاز ما است یا خیر.
وظیفه:

• ستون "Search Condition" حاوی یک مقدار است و شما باید تعیین کنید که آیا در ستون "Search Array" وجود دارد یا خیر.

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

به ترتیب در مورد همه، قبلاً درباره "IF" صحبت کرده ایم، بنابراین اجازه دهید به تابع "IFERROR" برویم.

IFERROR(مقدار، error_value)

• مقدار آرگومانی است که برای خطا بررسی می شود.
• Value_on_error - در صورت وجود خطا در محاسبه فرمول، مقدار برگردانده می شود. ممکن است انواع زیرخطاها: #N/A، #VALUE!، #REF!، #DIV/0!، #NUMBER!، #NAME؟ و #خالی!.

"پایین"

• متن - تبدیل متن به حروف کوچک.

حال بیایید نگاهی دقیق تر به نحو تابع FIND بیندازیم.

FIND (متن_جستجو، متن_مشاهده شده، [موقعیت_شروع])

• Search_text - متنی که باید پیدا شود.
• Search_text - متنی که می خواهید متن جستجو شده را در آن پیدا کنید.
• Start_position - علامتی که از آن جستجو شروع می شود. اولین کاراکتر در متن "view_text" با شماره 1 است. اگر عدد مشخص نشده باشد، به طور پیش فرض 1 است.

=IF(IFERROR(FIND(LINE(A2)، LINE(E2)،1)، 0)=0، "fail", "bingo!")

1. LOWER(A2) – آرگومان Search_Text در سلول A2 را به متن کوچک تبدیل می کند
2. تابع FIND شروع به جستجوی آرگومان تبدیل شده Search_Text در آرایه Search_Text می کند که توسط تابع LOWER(E2) نیز به متن کوچک تبدیل می شود.
3. اگر تابع مطابقت پیدا کند، به عنوان مثال. شماره سریال اولین کاراکتر کلمه/مقدار منطبق را برمی گرداند، شرط TRUE در فرمول "IF" فعال می شود، زیرا مقدار حاصل صفر نیست. در نتیجه، ستون "نتیجه" مقدار "Bingo!" را نمایش می دهد.
4. با این حال، اگر تابع مطابقت پیدا نکرد، به عنوان مثال. شماره سریال اولین کاراکتر کلمه/مقدار منطبق نشان داده نمی شود و به جای مقدار یک خطا برگردانده می شود، شرط موجود در فرمول "IFERROR" فعال می شود و مقداری برابر با "0" برمی گردد که مطابقت دارد. به شرط FALSE در فرمول "IF"، زیرا مقدار حاصل "0" است. در نتیجه، مقدار "fail" در ستون "نتیجه" نمایش داده می شود.

همانطور که از شکل بالا مشخص است، به لطف توابع "LOW" و "FIND"، بدون در نظر گرفتن حالت کاراکترها و مکان در سلول، مقادیر مورد نظر را پیدا می کنیم، اما باید به خط 5 توجه کنیم.
عبارت جستجو روی "111" تنظیم شده است، اما آرایه جستجو حاوی مقدار "1111111 cookies" است، اما فرمول نتیجه "Bingo!" این اتفاق می افتد زیرا مقدار "111" در مجموعه مقادیر "1111111" گنجانده شده است، در نتیجه یک مطابقت پیدا می شود. در غیر این صورت این شرایطکار نخواهد کرد

Case_4 جستجوی یک مقدار در یک آرایه بر اساس چندین شرایط، یا زمانی که VLOOKUP حتی بیشتر قادر به کمک به ما نیست

بیایید موقعیتی را تصور کنیم که در آن باید مقداری را از "جدول با نتیجه" پیدا کنید آرایه دو بعدی"دایرکتوری" برای چندین شرط، یعنی مقدار "Name" و "Month".
شکل جدولی کار به شکل زیر خواهد بود:

وضعیت:

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

نحو تابع INDEX

INDEX(آرایه، شماره_ردیف، [شماره_ستون])

• آرایه - محدوده ای از سلول ها که در صورت مطابقت با شرایط جستجو، مقادیر از آنها نشان داده می شود.
• اگر آرایه فقط یک سطر یا یک ستون داشته باشد، آرگومان row_number یا column_number به ترتیب اختیاری است.
• اگر آرایه بیش از یک سطر و یک ستون را اشغال کند و تنها یکی از آرگومان های row_number و column_number داده شود، تابع INDEX آرایه ای متشکل از کل سطر یا کل ستون آرگومان آرایه را برمی گرداند.
• Line_number - شماره خطی در آرایه ای که می خواهید مقداری را از آن برگردانید.
• column_number - شماره ستونی در آرایه ای که می خواهید مقداری را از آن برگردانید.

نحو تابع MATCH

MATCH(مقدار_ جستجو، آرایه_جستجو، [مطابق_نوع])

• Lookup_value مقداری است که با مقادیر آرگومان lookup_array مطابقت دارد. آرگومان lookup_value می تواند یک مقدار (عدد، متن یا بولی) یا مرجعی به سلول حاوی چنین مقداری باشد.
• Looked_array - محدوده سلول هایی که جستجو در آن انجام می شود.
• match_type یک آرگومان اختیاری است. عدد 1-، 0 یا 1 است.

بخشی از فرمول:

MATCH($A4,$I$4:$I$7,0)

MATCH (B$3,$J$3:$L$3,0)

بنابراین، مختصات نقطه (1؛ #N/A) را دریافت کردیم که تابع "INDEX" برای جستجو در آرگومان "Array" استفاده می کند.
تابع کاملاً نوشته شده برای سلول B4 به شکل زیر است:

=INDEX($J$4:$L$7، MATCH($A4،$I$4:$I$7.0)، MATCH(B$3،$J$3:$L$3.0))

=INDEX($J$4:$L$7,1،#N/A))

برای خنثی کردن این اثر منفی، از تابع "IFERROR" که قبلاً در مورد آن خواندیم استفاده می کنیم و در صورت بروز خطا، مقدار بازگشتی را با "0" جایگزین می کنیم، سپس فرمول به شکل زیر خواهد بود:

=IFERROR(INDEX($J$4:$L$7، MATCH($A4،$I$4:$I$7.0)، MATCH(B$3،$J$3:$L$3.0))،0)

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

Case_5 یافتن یک مقدار در محدوده ای از اعداد

بیایید تصور کنیم که باید به اعدادی که در محدوده خاصی قرار دارند علامت خاصی بدهیم.
وضعیت:
بسته به قیمت تمام شده محصول، باید دسته خاصی را به آن اختصاص داد
اگر مقدار در محدوده باشد

• از 0 تا 1000 = A
• از 1001 تا 1500 = B
• از 1501 تا 2000 = B
• از 2001 تا 2500 = G
• بیش از 2501 = D

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

LOOKUP (مقدار_جستجو، بردار_پیش، [نتیجه_بردار])

• lookup_value مقداری است که تابع LOOKUP در اولین بردار به دنبال آن است. Lookup_value می تواند یک عدد، متن، بولی، نام یا مرجع مقدار باشد.
• Watch_vector محدوده ای است که از یک سطر یا یک ستون تشکیل شده است. مقادیر در آرگومان lookup_vector می تواند متن، اعداد یا بولی باشد.
• مقادیر در آرگومان view_vector باید به ترتیب صعودی باشند: ...، -2، -1، 0، 1، 2، ...، A-Z، FALSE، TRUE. در غیر این صورت، تابع LOOKUP ممکن است نتیجه نادرستی برگرداند. متن کوچک و بزرگ معادل در نظر گرفته می شود.
• result_vector محدوده ای است که از یک سطر یا ستون تشکیل شده است. نتیجه_بردار باید به اندازه Lookup_vector باشد.

=VIEW(E3,$A$3:$A$7,$B$3:$B$7)

آرگومان های "View_vector" و "Result_vector" را می توان به صورت آرایه نوشت - در این مورد، لازم نیست آنها را در یک جدول جداگانه در یک صفحه اکسل نمایش دهید.
در این حالت تابع به شکل زیر خواهد بود:
خروجی نتیجه در سلول B3:
=VIEW(E3;(0;1001;1501;2001;2501);("A"، "B"، "C"، "D"، "D"))

Case_6 جمع اعداد بر اساس ویژگی ها

برای جمع اعداد بر اساس ویژگی های خاص، می توانید از سه تابع مختلف استفاده کنید:
SUMIF - تنها با یک ویژگی جمع می شود
SUMIFS - مجموع بیش از چندین ویژگی
SUMPRODUCT - مجموع چند ویژگی
همچنین گزینه ای با استفاده از "SUM" و تابع فرمول آرایه وجود دارد، زمانی که فرمول "SUM" به یک آرایه افزایش می یابد:
((=SUM(()*()))
اما این رویکرد کاملاً ناخوشایند است و با فرمول "SUMPRODUCT" کاملاً از نظر عملکرد پوشش داده می شود
اکنون برای جزئیات بیشتر در مورد نحو "SUMPRODUCT":

SUMPRODUCT(array1, [array2], [array3],...)

• Array1 اولین آرایه ای است که اجزای آن باید ضرب شده و سپس نتایج را اضافه کرد.
• Array2, array3… - از 2 تا 255 آرایه که اجزای آن باید ضرب شوند و سپس نتایج اضافه شوند.

• هزینه کل حمل و نقل برای هر محصول برای یک دوره معین را بیابید:

همانطور که از جدول با داده ها مشخص است، برای محاسبه بهای تمام شده، باید قیمت را در مقدار ضرب کرد و مقدار حاصل را با اعمال شرایط انتخاب به جدول حاصل انتقال داد.
با این حال، فرمول SUMPROIZ اجازه می دهد تا چنین محاسباتی در فرمول انجام شود.
خروجی نتیجه در سلول B4:

=SUMPRODUCT(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
بیایید به فرمول در بخش ها نگاه کنیم:
- شرط انتخاب را در ستون "Name" جدول با داده ها در ستون "Name" در جدول با نتیجه تنظیم کنید.

($K$3:$K$11>=B$3)*($K$3:$K$11 – بر اساس بازه زمانی یک شرط تعیین می کنیم، تاریخ بزرگتر یا مساوی با روز اول ماه جاری باشد، اما کمتر از روز اول ماه بعد باشد. به طور مشابه، یک شرط در یک جدول با نتیجه، یک آرایه در یک جدول با داده است.
- ستون های "تعداد" و "قیمت" در جدول را با داده ها ضرب کنید.
مزیت بدون شک این عملکرد، ترتیب رایگان شرایط ضبط است؛ آنها را می توان به هر ترتیبی نوشت، این بر نتیجه تأثیر نمی گذارد.
نتیجه:

حال بیایید شرایط را پیچیده کنیم و این شرط را اضافه کنیم که انتخاب نام "کوکی ها" فقط در کلاس های "small" و "large" و برای نام "rolls" همه چیز به جز کلاس "with jam" رخ می دهد:

خروجی نتیجه در سلول B4:

=SUMPRODUCT(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11
یک شرط جدید به فرمول انتخاب کوکی ها اضافه شده است:

(($I$3:$I$11="کوچک")+($I$3:$I$11="بزرگ"))

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

=SUMPRODUCT(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11 "با مربا");($L$3:$L$11)*($K$3:$K$11))

این:

($I$3:$I$11<>"با مربا")

– در واقع، در این فرمول امکان نوشتن یک شرط انتخاب به همان روشی که هنگام انتخاب توسط کوکی ها وجود داشت، وجود داشت، اما پس از آن، باید سه شرط را در فرمول لیست کنید، در این حالت، نوشتن استثنا آسان تر است. - برابر با "با جم" نیست برای این ما از مقدار " استفاده می کنیم<>».
به طور کلی، اگر گروه‌هایی از ویژگی‌ها/کلاس‌ها از قبل شناخته شده باشند، بهتر است آنها را در این گروه‌ها ترکیب کنیم و کتاب‌های مرجع ایجاد کنیم تا اینکه همه شرایط را در یک تابع بنویسیم و آن را باد کنیم.
نتیجه:

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

ممنون بخاطر وقتی که گذاشتید!

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

عملکرد جستجو در مایکروسافت اکسل امکان یافتن متن یا مقادیر عددی مورد نظر را از طریق پنجره Find and Replace ارائه می دهد. علاوه بر این، این برنامه قابلیت های پیشرفته جستجوی داده ها را ارائه می دهد.

روش 1: جستجوی ساده

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

روش 2: جستجو بر اساس محدوده سلولی مشخص شده

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

روش 3: جستجوی پیشرفته

همانطور که در بالا ذکر شد، در طول یک جستجوی معمولی، نتایج جستجو کاملاً شامل تمام سلول‌های حاوی مجموعه‌ای متوالی از کاراکترهای جستجو به هر شکلی، صرف نظر از مورد، می‌شود.

علاوه بر این، نه تنها محتویات یک سلول خاص، بلکه آدرس عنصری که به آن اشاره دارد نیز می تواند در نتایج گنجانده شود. برای مثال سلول E2 حاوی فرمولی است که مجموع سلول های A4 و C3 است. این مجموع 10 است و این عدد است که در سلول E2 ظاهر می شود. اما اگر عدد "4" را در جستجو وارد کنیم، همان سلول E2 در بین نتایج قرار می گیرد. چگونه ممکن است این اتفاق بیفتد؟ فقط سلول E2 به عنوان فرمول حاوی آدرس سلول A4 است که فقط شامل عدد 4 مورد نظر است.

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

1. بعد از باز کردن پنجره "یافتن و جایگزینی"با استفاده از هر روشی که در بالا توضیح داده شد، روی دکمه کلیک کنید "گزینه ها".



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

   

   به طور پیش فرض، توابع "مورد مسابقه"و "کل سلول ها"غیرفعال هستند، اما اگر کادرهای کنار موارد مربوطه را علامت بزنیم، در این صورت هنگام ایجاد نتیجه، مورد وارد شده و تطابق دقیق در نظر گرفته می شود. اگر کلمه ای را با یک حرف کوچک وارد کنید، سلول های حاوی املای این کلمه با حرف بزرگ دیگر در نتایج جستجو ظاهر نمی شوند، همانطور که به طور پیش فرض وجود دارد. علاوه بر این، اگر عملکرد فعال باشد "کل سلول ها"، سپس فقط عناصر حاوی نام دقیق به نتایج اضافه می شود. به عنوان مثال، اگر عبارت جستجو را "Nikolaev" تنظیم کنید، سلول های حاوی متن "Nikolaev A.D." به نتایج جستجو اضافه نمی شوند.

   

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

   

   در پارامتر "مرور کردن"می توانید جهت جستجو را تغییر دهید. به طور پیش فرض، همانطور که در بالا ذکر شد، جستجو به ترتیب خط به خط انجام می شود. انتقال سوئیچ به موقعیت "بر اساس ستون"، می توانید ترتیب تولید نتایج جستجو را از ستون اول تنظیم کنید.

   

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

   

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

   

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

   

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

   

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

   

   پس از پیکربندی فرمت جستجو، روی دکمه کلیک کنید "خوب".

   

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



3. پس از تنظیم تنظیمات جستجو، روی دکمه کلیک کنید "همه چیز را پیدا کن"یا "پیدا کردن بعدی"برای رفتن به نتایج جستجو

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

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

در این مقاله

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

برای انجام این کار می توانید از تابع VLOOKUP یا ترکیبی از توابع INDEX و MATCH استفاده کنید.

نمونه هایی از استفاده از تابع VLOOKUP

تابع VLOOKUP.

نمونه هایی از INDEX و MATCHES

چه مفهومی داره:

=INDEX(شما باید مقدار را از C2:C10 برگردانید، که با MATCH مطابقت دارد (مقدار اول "Cabbage" در آرایه B2:B10))

فرمول به دنبال اولین مقدار مربوط به سلول C2:C10 می باشد کلم(در B7)، و مقدار C7 ( 100 ) - اولین مقدار مربوطه کلم.

برای اطلاعات بیشتر، تابع INDEX و تابع MATCH را ببینید.

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

برای این کار از تابع VLOOKUP استفاده کنید.

مهم:مطمئن شوید که مقادیر در ردیف اول به ترتیب صعودی مرتب شده اند.

در مثال بالا، تابع VLOOKUP به دنبال نام دانش آموزی می گردد که دارای 6 تاخیر در محدوده A2:B7 است. هیچ ورودی در جدول برای وجود ندارد 6 با تأخیر، بنابراین تابع VLOOKUP به دنبال بالاترین تطابق بعدی زیر 6 می گردد و مقدار 5 مرتبط با نام کوچک را پیدا می کند. دیو، و بنابراین برمی گردد دیو.

برای اطلاعات بیشتر، تابع VLOOKUP را ببینید.

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

برای انجام این کار، از توابع OFFSET و MATCH استفاده کنید.

توجه داشته باشید:این رویکرد در صورتی استفاده می‌شود که داده‌ها در محدوده داده‌های خارجی باشند که هر روز به‌روزرسانی می‌کنید. می دانید که ستون B دارای Price است، اما نمی دانید سرور چند ردیف داده را برمی گرداند و ستون اول بر اساس حروف الفبا مرتب نشده است.

C1سلول سمت چپ بالای محدوده است (همچنین به آن سلول شروع می گویند).

مطابقت ("پرتقال"؛ C2: C7؛ 0)به دنبال رنگ نارنجی در محدوده C2:C7 است. شما نباید سلول شروع را در محدوده قرار دهید.

1 - تعداد ستون‌هایی که در سمت راست سلول شروع هستند که مقدار بازگشتی باید برای آنها برگردانده شود. در مثال ما، مقدار بازگشتی در ستون D است، حراجی.

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

برای انجام این کار از تابع GLOOKUP استفاده می شود. در زیر یک نمونه آورده شده است.

تابع LOOKUP یک ستون را جستجو می کند حراجیو مقدار را از خط 5 در محدوده مشخص شده برمی گرداند.

برای اطلاعات بیشتر، توابع LOOKUP را ببینید.

با استفاده از Lookup Wizard یک فرمول جستجو ایجاد کنید (فقط Excel 2007)

توجه داشته باشید:افزونه Lookup Wizard در Excel 2010 متوقف شده است. این عملکرد با Function Wizard و ویژگی های جستجو و مرجع (مرجع) موجود جایگزین شده است.

در Excel 2007، Lookup Wizard یک فرمول جستجو بر اساس داده های کاربرگ ایجاد می کند که دارای عناوین ردیف و ستون است. جادوگر جستجو به شما کمک می کند تا مقادیر دیگری را در یک ردیف پیدا کنید، زمانی که مقدار را در یک ستون می دانید و بالعکس. Lookup Wizard از index و MATCH در فرمول هایی که ایجاد می کند استفاده می کند.