نحوه پیدا کردن عدد در جدول اکسل راهنمای حل مسائل تایپ شده در مایکروسافت اکسل

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

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

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

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

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

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

Case_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(مقدار_ جستجو، آرایه_جستجو، [مطابق_نوع])

• 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]،...)

• آرایه 1 اولین آرایه ای است که اجزای آن باید ضرب شوند و سپس نتایج اضافه شوند.
• 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<>"با مربا")

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

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

با تشکر از وقت شما!

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

در این مقاله

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

برای انجام این کار می توانید از تابع 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 در محدوده مشخص شده برمی گرداند.

برای اطلاعات بیشتر، به توابع GLOOKUP مراجعه کنید.

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

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

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

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

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

برای حل این مشکل، مثالی را بر روی یک جدول شماتیک نشان می‌دهیم که با شرایط توضیح داده شده در بالا مطابقت دارد.

ورق با جدولی برای جستجوی مقادیر به صورت عمودی و افقی:

بالای خود جدول یک خط با نتایج وجود دارد. در سلول B1 معیار جستجو را وارد می کنیم، یعنی عنوان ستون یا نام ردیف. و در سلول D1 فرمول جستجو باید نتیجه محاسبه مقدار مربوطه را برگرداند. پس از آن، فرمول دوم در سلول F1 کار می کند، که قبلاً از مقادیر سلول های B1 و D1 به عنوان معیاری برای جستجوی ماه مربوطه استفاده می کند.

یافتن یک مقدار در رشته اکسل

اکنون متوجه می شویم که حداکثر فروش محصول 4 در چه حجمی و در چه ماهی بوده است.

برای جستجو بر اساس ستون:

بیشترین فروش محصول 4 طی دو سه ماهه در چه ماه و چه چیزی بوده است.

اصل فرمول برای یافتن مقدار در یک ردیف اکسل:

اولین آرگومان تابع VLOOKUP (نمای عمودی) پیوندی را به سلولی که معیار جستجو در آن قرار دارد، مشخص می کند. آرگومان دوم محدوده سلول هایی را برای مشاهده در طول جستجو مشخص می کند. آرگومان سوم تابع VLOOKUP باید شماره ستونی را نشان دهد که از آن مقدار باید در مقابل ردیفی به نام Product 4 گرفته شود. اما از آنجایی که ما این عدد را از قبل نمی دانیم، از تابع COLUMN برای ایجاد آرایه ای از اعداد ستون استفاده می کنیم. محدوده B4:G15.

این به تابع VLOOKUP اجازه می دهد تا یک آرایه کامل از مقادیر را جمع آوری کند. در نتیجه، تمام مقادیر مربوط به هر ستون در ردیف محصول 4 در حافظه ذخیره می شود (یعنی: 360؛ 958؛ 201؛ 605؛ 462؛ 832). پس از آن، تابع MAX تنها می تواند حداکثر عدد را از این آرایه بگیرد و در نتیجه محاسبه فرمول، آن را به عنوان مقدار سلول D1 برگرداند.

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

چگونه سرصفحه های ستون را بر اساس ابتدای یک سلول بدست آوریم؟

به عنوان مثال، چقدر موثر ماه را با استفاده از فرمول دوم نمایش دادیم که در آن حداکثر فروش وجود داشت. توجه به این که در فرمول دوم از اسکلت فرمول اول بدون تابع MAX استفاده کردیم دشوار نیست. ساختار اصلی فرمول: VLOOKUP(B1,A5:G14,COLUMN(B5:G14),0). ما تابع MAX را با MATCH جایگزین کرده ایم که از مقدار بدست آمده توسط فرمول قبلی به عنوان اولین آرگومان استفاده می کند. اکنون به عنوان معیاری برای جستجوی ماه عمل می کند. و در نتیجه تابع SEARCH عدد ستون 2 را به ما برمی گرداند که در آن حداکثر مقدار فروش برای محصول 4 قرار دارد که پس از آن تابع INDEX در کار گنجانده شده است که مقدار را بر اساس تاریخ و شماره ستون برمی گرداند از محدوده مشخص شده در آرگومان های آن. از آنجایی که ما یک ستون 2 داریم و شماره ردیف در محدوده ای که نام ماه ها در آن ذخیره می شود، در هر صورت 1 خواهد بود، پس فقط باید از تابع INDEX برای بدست آوردن مقدار مربوطه از محدوده B4 استفاده کنیم. G4 - فوریه (ماه دوم).



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

گزینه دوم برای کار این است که جدول را با استفاده از نام ماه به عنوان معیار جستجو کنید. در چنین مواردی، ما باید اسکلت فرمول خود را تغییر دهیم: تابع VLOOKUP را با HLOOKUP و تابع COLUMN را با ROW جایگزین کنیم.

این به ما امکان می دهد دریابیم حداکثر فروش در یک ماه خاص چه حجمی و چه محصولی بوده است.

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

اصل فرمول برای یافتن مقدار در ستون اکسل:

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

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

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

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

در این مقاله

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

برای انجام این کار می توانید از تابع 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 در محدوده مشخص شده برمی گرداند.

برای اطلاعات بیشتر، به توابع GLOOKUP مراجعه کنید.

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

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

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

کشف اطلاعات مورد نیاز در یک کاربرگ با داده های زیاد بسیار دشوار است. با این حال، کادر محاوره‌ای Find and Replace یافتن اطلاعات را بسیار آسان‌تر می‌کند. علاوه بر این، دارای ویژگی های مفیدی است که بسیاری از کاربران از آن بی اطلاع هستند.

دستور را اجرا کنید ویرایش صفحه اصلی Find and highlight را پیدا کنید(یا کلیک کنید Ctrl+F) برای باز کردن کادر محاوره ای پیدا کنید و جایگزین کنید. اگر نیاز به جایگزینی داده دارید، دستور را انتخاب کنید صفحه اصلی ویرایش را پیدا کنید و جایگزین را انتخاب کنید(یا کلیک کنید Ctrl+H). دستور دقیقی که اجرا می کنید تعیین می کند که کادر محاوره ای در کدام یک از دو تب باز شود.

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

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

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

هنگام جستجو با استفاده از کادر محاوره ای پیدا کنید و جایگزین کنیدمی توان از دو علامت عام استفاده کرد:

• ? - با هر شخصیتی مطابقت دارد.
• * - با هر تعداد کاراکتر مطابقت دارد.

علاوه بر این، این کاراکترهای عام همچنین می توانند هنگام جستجوی مقادیر عددی استفاده شوند. به عنوان مثال، اگر در نوار جستجو مشخص کنید 3* ، نتیجه تمام سلول هایی را که دارای مقداری هستند که با 3 شروع می شود نشان می دهد و اگر 1?9 را وارد کنید، تمام ورودی های سه رقمی را که با 1 شروع و با 9 ختم می شوند دریافت خواهید کرد.

برای جستجوی علامت سوال یا ستاره، قبل از آن علامت (~) قرار دهید.
به عنوان مثال، رشته جستجوی زیر متن *NONE* را پیدا می کند: -*N0NE~*
برای پیدا کردن کاراکتر tilde، دو تایلد را در نوار جستجو قرار دهید.

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

• چک باکس مورد کبریت- آن را طوری تنظیم کنید که حروف متن جستجو شده با حروف متن مشخص شده مطابقت داشته باشد. برای مثال اگر کلمه Ivan را در جستجو وارد کنید و کادر مشخص شده را علامت بزنید، کلمه Ivan در نتایج جستجو ظاهر نمی شود.
• چک باکس کل سلول- آن را برای یافتن سلولی تنظیم کنید که دقیقاً حاوی متنی است که در نوار جستجو مشخص شده است. به عنوان مثال، اگر Excel را در نوار جستجو تایپ کنید و کادر را علامت بزنید، سلولی که حاوی عبارت Microsoft Excel باشد را پیدا نمی کنید.
• لیست کشویی منطقه جستجو- لیست شامل سه مورد است: مقادیر، فرمول ها و یادداشت ها. به عنوان مثال، اگر عدد 900 را در نوار جستجو و در لیست کشویی وارد کنید منطقه جستجواگر آیتم مقدار را انتخاب کنید، اگر با استفاده از فرمول به دست آمده باشد، سلول حاوی مقدار 900 را در نتایج جستجو نخواهید دید.

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

همچنین توجه داشته باشید که با استفاده از پنجره پیدا کنید و جایگزین کنیدمقادیر عددی قالب بندی شده یافت نمی شوند. برای مثال، اگر وارد نوار جستجو شوید $5* مقداری که قالب بندی ارزی آن اعمال شده و به نظر می رسد 54.00 دلار باشد، یافت نمی شود.

کار با تاریخ ها می تواند چالش برانگیز باشد زیرا اکسل از بسیاری از فرمت های تاریخ پشتیبانی می کند. اگر به دنبال تاریخی هستید که قالب‌بندی پیش‌فرض اعمال شده باشد، اکسل تاریخ‌ها را حتی اگر به روش‌های مختلف قالب‌بندی شده باشد، پیدا می‌کند. برای مثال، اگر سیستم شما از قالب تاریخ m/d/y استفاده می‌کند، رشته جستجوی 10/*/2010 تمام تاریخ‌های اکتبر 2010 را بدون در نظر گرفتن نحوه قالب‌بندی آنها پیدا می‌کند.

از یک فیلد خالی استفاده کنید جایگزین کنید بابرای حذف سریع برخی از اطلاعات از یک کاربرگ. به عنوان مثال، - * را در فیلد وارد کنید پیدا کنیدو میدان را ترک کنند جایگزین کنیدروی خالی سپس روی دکمه کلیک کنید همه را جایگزین کنیدتا اکسل تمام ستاره های کاربرگ را پیدا کرده و حذف کند.