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

شرح

کارکرد جستجو کردنو جستجو کردنیک رشته متن را در دیگری پیدا کنید و موقعیت شروع اولین رشته متن را برگردانید (شمارش از کاراکتر اول رشته متن دوم). به عنوان مثال، برای پیدا کردن موقعیت حرف "n" در کلمه "printer" می توانید از تابع زیر استفاده کنید:

SEARCH("n"،"چاپگر")

این تابع برمی گردد 4 ، از آنجایی که "n" چهارمین کاراکتر در کلمه "چاپگر" است.

شما همچنین می توانید کلمات را در کلمات دیگر پیدا کنید. به عنوان مثال، تابع

SEARCH ("پایه"، "پایگاه داده")

برمی گرداند 5 ، از آنجایی که کلمه "پایه" با کاراکتر پنجم کلمه "پایگاه داده" شروع می شود. می توان از توابع استفاده کرد جستجو کردنو جستجو کردنبرای تعیین موقعیت یک کاراکتر یا رشته متن در یک رشته متنی دیگر و سپس برگرداندن متن با استفاده از توابع PSTRو PSTRBیا آن را با توابع جایگزین کنید جایگزین کردنو جایگزین کردن. این توابع در این مقاله نشان داده شده است.

مهم:

این ویژگی‌ها ممکن است به همه زبان‌ها در دسترس نباشند.

تابع SEARCH فقط در صورتی دو بایت در هر کاراکتر می‌شمارد که زبان پیش‌فرض یک زبان دارای BDC باشد. در غیر این صورت، تابع SEARCH مانند تابع SEARCH عمل می کند و هر کاراکتر یک بایت می شمارد.

زبان هایی که از BDCS پشتیبانی می کنند عبارتند از ژاپنی، چینی ساده، چینی سنتی و کره ای.

نحو

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

SEARCHB(search_text، viewed_text، [start_position])

آرگومان های توابع SEARCH و SEARCHB در زیر توضیح داده شده است.

Search_textضروری. متنی که می خواهید پیدا کنید.

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

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

اظهار نظر

کارکرد جستجو کردنو جستجو کردنبه حروف کوچک و بزرگ حساس نیستند اگر باید به حروف بزرگ و کوچک حساس باشید، از توابع استفاده کنید پیدا کردنو شب.

در استدلال جستجو_متن می توانید از حروف عام استفاده کنید: علامت سوال ( ? ) و یک ستاره ( * ). یک علامت سوال با هر کاراکتری مطابقت دارد، یک ستاره با هر دنباله ای از کاراکترها مطابقت دارد. اگر می خواهید علامت سوال یا ستاره پیدا کنید، یک علامت تایپ کنید ( ~ ).

اگر مقدار آرگومان جستجو_متن پیدا نشد، #VALUE! مقدار خطا برگردانده شد

اگر استدلال نقطه شروع حذف می شود، سپس روی 1 تنظیم می شود.

اگر نقطه شروع بیش از 0 یا بیشتر از طول آرگومان نباشد viewed_text ، #ارزش! مقدار خطا برگردانده شد

بحث و جدل نقطه شروع می توان از آن برای رد کردن تعداد معینی از کاراکترها استفاده کرد. اجازه دهید فرض کنیم که تابع جستجو کردنباید برای کار با رشته متن "MDS0093.Men's Clothing" استفاده شود. برای پیدا کردن اولین رخداد "M" در بخش توصیفی یک رشته متن، آرگومان را روی نقطه شروع مقدار 8 به طوری که جستجو در آن قسمت از متن انجام نشود شماره سریال(در این مورد - "MDS0093"). تابع جستجو کردنجستجو را از کاراکتر هشتم شروع می کند، کاراکتر مشخص شده در آرگومان را پیدا می کند جستجو_متن ، در موقعیت بعدی، و عدد 9 را برمی گرداند جستجو کردنهمیشه شماره کاراکتر را از ابتدا شمارش می کند از متن در حال مشاهده ، از جمله کاراکترهایی که در صورت وجود مقدار آرگومان نادیده گرفته می شوند نقطه شروع بیش از 1.

مثال ها

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

داده ها
درآمد: حاشیه
"رئیس" اینجا.
فرمول	شرح	نتیجه
SEARCH("and";A2;6)	موقعیت اولین "و" در ردیف سلول A2 که از کاراکتر ششم شروع می شود.
SEARCH(A4;A3)	موقعیت شروع ردیف "حاشیه" (ردیف جستجو شده در سلول A4 است) در ردیف "درآمد: حاشیه" (سلول جستجو شده A3 است).
REPLACE(A3,SEARCH(A4,A3),6,Volume)	کلمه "margin" را با کلمه "volume" با قرار دادن کلمه "margin" در سلول A3 و جایگزینی آن کاراکتر و پنج کاراکتر زیر با رشته متن "volume" جایگزین می کند.	درآمد: حجم
PSTR(A3;SEARCH(" ";A3)+1,4)	چهار کاراکتر اول را که از اولین فاصله در ردیف Revenue: Margin (سلول A3) پیروی می کنند، برمی گرداند.
SEARCH("""";A5)	اول موقعیت نقل قول های دوگانه(") در سلول A5.
PSTR(A5;SEARCH("""";A5)+1;SEARCH("""";A5;SEARCH("""";A5)+1)-SEARCH(""""";A5)-1)	فقط متن محصور شده در دو نقل قول از سلول A5 را برمی‌گرداند.

کار با پروژه VB (12)
قالب بندی شرطی (5)
لیست ها و محدوده ها (5)
ماکروها (روش های VBA) (63)
متفرقه (39)
اشکالات و اشکالات اکسل (3)

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

بیایید فرض کنیم شما از یک تامین کننده/مشتری/مشتری یک جدول تکمیل شده با لیستی از محصولات دریافت می کنید:

و این جدول باید با شماره مقاله/کد محصول در کاتالوگ محصولات موجود شما مقایسه شود:

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


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

(49.5 کیلوبایت، 13249 بارگیری)

در برگه «سفارش» در این فایل، جدول دریافتی از مشتری و در برگه «کاتالوگ» مقالات ما وجود دارد.
خود فرمول با استفاده از فایل مثال به شکل زیر خواهد بود:

BROWSE(2,1/SEARCH(Directory!$A$2:$A$11, A2);Directory!$A$2:$A$11)
=LOOKUP(2,1/SEARCH(دایرکتوری!$A$2:$A$11,A2)،دایرکتوری!$A$2:$A$11)
اگر متن حداقل یک مقاله از کاتالوگ داشته باشد، این فرمول نام مقاله را برمی گرداند #عدم_الف (#بدون_الف)اگر مقاله در کاتالوگ یافت نشد.
قبل از اینکه این فرمول را با انواع اضافات (مانند حذف #N/A غیر ضروری) اصلاح کنیم، بیایید بفهمیم که چگونه کار می کند.
تابع LOOKUP مقدار مشخص شده (2) را در محدوده مشخص شده (آرایه - آرگومان دوم) جستجو می کند. محدوده معمولاً آرایه ای از سلول ها است، اما تابع VIEW اولین ویژگی مورد نیاز ما را دارد - سعی می کند هر عبارتی که به عنوان آرگومان دوم نوشته شده است را مستقیماً به یک آرایه تبدیل کند. به عبارت دیگر، عبارت موجود در این آرگومان را ارزیابی می‌کند، که ما از آن با جایگزین کردن عبارت به عنوان آرگومان دوم استفاده می‌کنیم: 1/SEARCH(Directory!$A$2:$A$11;A2). بخش SEARCH (کاتالوگ!$A$2:$A$11;A2) به نوبه خود هر مقدار را از فهرست کاتالوگ در سلول A2 جستجو می کند (نام از جدول مشتری). اگر مقداری پیدا شد، شماره موقعیت اولین کاراکتر مقدار یافت شده برگردانده می شود. اگر مقدار پیدا نشد، مقدار خطای #VALUE! (#VALUE!) برگردانده می شود. حالا ویژگی دوم: تابع نیاز دارد که داده ها در آرایه به ترتیب صعودی مرتب شوند. اگر داده ها متفاوت قرار گیرند، تابع آرایه را اسکن می کند تا زمانی که مقداری بزرگتر از مقدار جستجو شده، اما تا حد امکان نزدیک به آن پیدا کند (البته اگر داده ها اجازه می دهند، برای جستجوی دقیق تر باز هم بهتر است. برای مرتب کردن لیست به ترتیب صعودی). بنابراین، ابتدا عدد 1 را با عبارت SEARCH(Directory!$A$2:$A$11,A2) تقسیم می کنیم تا آرایه ای به شکل: (0.0181818181818182:#VALUE!:#VALUE!:#VALUE!:#VALUE!: #VALUE !:#VALUE!:#VALUE!:#VALUE!:#VALUE !}
خوب، به عنوان مقدار مورد نظر به تابع یک عدد می دهیم 2 - یک عدد آشکارا بزرگتر از آنچه که معمولاً در آرایه یافت می شود (زیرا یک تقسیم بر هر عددی کمتر از دو خواهد بود). و در نتیجه، موقعیتی را در آرایه ای به دست می آوریم که آخرین تطابق از دایرکتوری در آن رخ می دهد. پس از آن تابع VIEW این موقعیت را به خاطر می آورد و مقدار را از آرایه Directory برمی گرداند!$A$2:$A$11 (آگومان سوم) که در این آرایه برای این موقعیت نوشته شده است.
شما می توانید مراحل محاسبه تابع را خودتان برای هر سلول مشاهده کنید؛ در اینجا من به سادگی مراحل را به شکل کمی گسترش یافته برای درک ارائه می کنم:

1. =BROWSE(2,1/SEARCH(دایرکتوری!$A$2:$A$11, A2);دایرکتوری!$A$2:$A$11)
2. =VIEW(2;
   1/(55:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE;!}
   کاتالوگ!$A$2:$A$11)
3. =VIEW(2;(0.0181818181818182:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE;Каталог!$A$2:$A$11)!}
4. =VIEW(2;
   1;
   ("FM2-3320":"CV455689":"Q5949X":"CE321A":"CE322A":"CE323A":"00064073":"CX292708":"CX292709":"CX2927": "CX2927")
5. ="FM2-3320"

حالا بیایید تابع را کمی اصلاح کنیم و چند پیاده سازی دیگر انجام دهیم
پیاده سازی 1:
به‌جای شماره‌های مقاله و #N/A، برای موقعیت‌های یافت شده «بله» و برای موقعیت‌های گمشده «در کاتالوگ یافت نشد» را نمایش می‌دهیم:
=IF(END(VIEW(2,1/SEARCH(کاتالوگ!$A$2:$A$11,A2)));"در کاتالوگ یافت نشد"،"بله")
=IF(ISNA(LOOKUP(2,1/SEARCH(Directory!$A$2:$A$11,A2)))،"در فهرست یافت نشد"،"بله")
عملکرد تابع ساده است - ما LOOKUP را مرتب کرده ایم، بنابراین همه چیزهایی که باقی می ماند UND و IF هستند.
سازمان ملل (ایسنا) بازمی گردد درست است، واقعیاگر عبارت داخل آن مقدار خطا برگرداند #عدم_الف (#بدون_الف)و نادرستاگر عبارت داخل این مقدار خطا را برنگرداند.
IF (IF) آنچه را که توسط آرگومان دوم مشخص شده است برمی‌گرداند اگر عبارت اولی برابر باشد درست است، واقعیو آنچه را که برهان سوم مشخص می کند، اگر بیان استدلال اول باشد نادرست.

پیاده سازی 2:
به جای #N/A، "در کاتالوگ یافت نشد" را نمایش می دهیم، اما در صورت یافتن مقالات، نام این مقالات را نمایش می دهیم:
=IFERROR(VIEW(2,1/SEARCH(کاتالوگ!$A$2:$A$11,A2),کاتالوگ!$A$2:$A$11);"در کاتالوگ نیست")
=IFERROR(LOOKUP(2,1/SEARCH(کاتالوگ!$A$2:$A$11,A2)،کاتالوگ!$A$2:$A$11)،"در کاتالوگ نیست")
من در این مقاله به طور مفصل در مورد تابع IFERROR صحبت کردم: چگونه 0 را به جای خطا در یک سلول با فرمول نشان دهیم.
به طور خلاصه، اگر عبارت مشخص شده توسط آرگومان اول تابع مقدار هر خطا را برمی گرداند، آنگاه تابع آنچه را که توسط آرگومان دوم نوشته شده است (در مورد ما، متن "در فهرست یافت نشد") برمی گرداند. اگر عبارت خطایی را برنگرداند، تابع IFERROR مقداری را که با عبارت در آرگومان اول به دست آمده است می نویسد (در مورد ما، این نام مقاله خواهد بود).

پیاده سازی 3
لازم است نه تنها مشخص شود که به کدام مقاله مربوط است، بلکه باید قیمت کالای این مقاله را نیز نمایش دهید (خود قیمت ها باید در ستون B صفحه کاتالوگ قرار گیرند):
=IFERROR(نمایش(2,1/SEARCH(دایرکتوری!$A$2:$A$11,A2)،دایرکتوری!$B$2:$B$11)؛"")
=IFERROR(LOOKUP(2,1/SEARCH(دایرکتوری!$A$2:$A$11,A2)،دایرکتوری!$B$2:$B$11)،"")

چند نکته مهم:

• داده های صفحه با شماره مقاله نباید دارای سلول های خالی باشد. در غیر این صورت، با درجه احتمال بالا، فرمول مقدار یک سلول خالی را برمی گرداند و نه سلولی که با شرایط جستجو مطابقت دارد.
• فرمول به گونه‌ای جستجو می‌کند که هر تطابقی پیدا می‌شود. مثلا یک عدد به صورت مقاله نوشته می شود 1 و در خط نام ممکن است علاوه بر کل 1 نیز وجود داشته باشد، 123 , 651123 , FG1412NMو غیره برای همه این موارد می توان مقاله شماره 1 را انتخاب کرد، زیرا در هر عنوان موجود است. معمولاً اگر ماده 1 در انتهای لیست قرار گرفته باشد، ممکن است این اتفاق بیفتد

بنابراین توصیه می شود لیست را قبل از استفاده از فرمول مرتب کنیدصعودی (از کوچکترین به بزرگترین، از A تا Z).

در مثال پیوست شده در ابتدای مقاله، تمامی گزینه های مورد تجزیه و تحلیل را خواهید دید.

اگر نیاز به نمایش همه نام‌ها دارید، می‌توانید از تابع CONTAIN_ONE_OF از افزونه MulTEx من استفاده کنید.

آیا مقاله کمکی کرد؟ لینک را با دوستان خود به اشتراک بگذارید دروس تصویری

("نوار پایین":("textstyle":"ایستا"،"textpositionstatic":"پایین"،"textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"پایین سمت چپ"،"textpositionmarginleft":24," textpositionmarginright":24"textpositionmargintop":24"textpositionmarginbottom":24"texteffect":"slide"، "texteffecteasing":"easeOutCubic"، "texteffectduration":600، "texteffectslidedirection": "leftlid"، "texteffects" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"اسلاید"،"texteffectslidedirection1":"راست"،"texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic:601"ext "texteffectdelay1":1000,"texteffect2":"slide", "texteffectslidedirection2":"راست"،"texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic"،"texteffectduration2":600""ext textcss":"display:block; padding:12px; text-align:left;""textbgcss":"display:block; position:absolute; top:0px; left:0px; عرض:100%؛ ارتفاع:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);""titlecss":"display:block; موقعیت:نسبی; فونت:پررنگ 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; موقعیت:نسبی; فونت: 12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; موقعیت:نسبی; margin-top:8px;""texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;,""descriptioncssresponsive":"display:none !important;""buttoncssresponsive" """"addgooglefonts":false,""googlefonts":""، "textleftrightpercentforstatic":40))

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

دستور را اجرا کنید ویرایش صفحه اصلی Find and highlight را پیدا کنید(یا کلیک کنید Ctrl+F) برای باز کردن کادر محاوره ای پیدا کنید و جایگزین کنید. اگر نیاز به جایگزینی داده دارید، دستور را انتخاب کنید ویرایش صفحه اصلی Replace را پیدا و برجسته کنید(یا کلیک کنید 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 را بدون توجه به نحوه قالب‌بندی آنها پیدا می‌کند.

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

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

من هر روز روش های مشابهی را انجام می دهم و بدون عملکردهایی که در زیر توضیح داده شده است، واقعاً کار سختی خواهم داشت. توجه داشته باشید و آنها را در کار خود به کار ببرید!

در جدول اکسل، توابع VLOOKUP و GLOOKUP جستجو کنید

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

نحو تابع VLOOKUP این است: =VLOOKUP( lookup_value; جدول جست و جو؛ خروجی_شماره_ستون; [نوع_نقشه]). بیایید به استدلال ها نگاه کنیم:

• مقدار جستجو- ارزشی که ما به دنبال آن خواهیم بود. این یک استدلال لازم است.
• جدول جستجو- آرایه سلول هایی که جستجو در آنها انجام می شود. ستونی با مقادیر جستجو شده باید اولین ستون در این آرایه باشد. این نیز یک استدلال لازم است;
• شماره ستون برای نمایش- شماره سریال ستون (از اولین آرایه شروع می شود) که در صورت مطابقت مقادیر مورد نیاز، تابع داده ها را نشان می دهد. استدلال لازم؛
• نوع تطبیق– برای تطابق ضعیف، «1» (یا «درست»)، برای تطابق کامل «0» («FALSE») را انتخاب کنید. آرگومان اختیاری است، اگر حذف شود، جستجو انجام خواهد شد تطابق غیر دقیق.

پیدا کردن تطابق دقیق با استفاده از VLOOKUP

بیایید به مثالی از نحوه عملکرد عملکرد VLOOKUP در زمانی که نوع تطبیق FALSE است، نگاه کنیم، و یک مطابقت دقیق را جستجو می کنیم. آرایه B5:E10 دارایی های ثابت یک شرکت خاص، ارزش دفتری، تعداد موجودی و مکان آنها را نشان می دهد. سلول B2 حاوی نامی است که باید شماره موجودی آن را در جدول پیدا کنید و آن را در سلول C2 قرار دهید.

تابع VLOOKUP در اکسل

بیایید فرمول را بنویسیم: =VLOOKUP(B2,B5:E10,3,FALSE).

در اینجا اولین آرگومان نشان می دهد که در جدول باید به دنبال مقدار سلول B2 بگردید. کلمه "فکس". آرگومان دوم می گوید که جدول مورد جستجو در محدوده B5:E10 قرار دارد و باید در ستون اول به دنبال کلمه "Fax" بگردید. در آرایه B5:B10. آرگومان سوم به برنامه می گوید که نتیجه محاسبه در ستون سوم آرایه قرار دارد، یعنی. D5: D10. استدلال چهارم FALSE است، یعنی. مطابقت کامل مورد نیاز است.

و بنابراین، تابع رشته "Fax" را از سلول B2 دریافت می کند و آن را در آرایه B5:B10 از بالا به پایین جستجو می کند. هنگامی که مطابقت پیدا شد (خط 8)، تابع مقدار مربوطه را از ستون D برمی‌گرداند، یعنی. محتویات D8 این دقیقاً همان چیزی است که ما نیاز داشتیم، مشکل حل شده است.

اگر مقدار جستجو شده یافت نشد، تابع برمی گردد.

پیدا کردن یک تطابق غیر دقیق با استفاده از VLOOKUP

به لطف این گزینه در VLOOKUP، می توانیم از فرمول های پیچیده برای یافتن نتیجه دلخواه خودداری کنیم.

آرایه B5:C12 نرخ بهره وام ها را بسته به مبلغ وام نشان می دهد. در سلول B2 مبلغ وام را نشان می دهیم و می خواهیم نرخ چنین معامله ای را در C2 دریافت کنیم. این کار از این جهت دشوار است که مقدار آن می تواند هر چیزی باشد و بعید است که با مقادیر مشخص شده در آرایه مطابقت داشته باشد؛ جستجوی یک تطابق دقیق مناسب نیست:

سپس فرمول جستجوی غیر دقیق را می نویسیم: =VLOOKUP(B2,B5:C12,2,TRUE). اکنون، از بین تمام داده های ارائه شده در ستون B، برنامه به دنبال نزدیکترین کوچکتر خواهد بود. یعنی برای مبلغ 8000 مقدار 5000 انتخاب می شود و درصد مربوطه نمایش داده می شود.

جستجوی غیر دقیق VLOOKUP در اکسل

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

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

جستجوی داده ها با استفاده از تابع VIEW

تابع LOOKUP مشابه VLOOKUP کار می کند، اما نحو متفاوتی دارد. من زمانی از آن استفاده می‌کنم که جدول داده‌ها شامل چندین ستون باشد و برای استفاده از VLOOKUP باید تعداد ستون خروجی را نیز محاسبه کنید. در چنین مواردی، تابع VIEW کار را آسان تر می کند. و بنابراین، نحو: =نمایش( lookup_value; array_to_search; آرایه_برای_نمایش) :

• مقدار جستجو- داده ها یا پیوندی به داده های مورد جستجو؛
• آرایه برای جستجو- یک سطر یا ستون که در آن به دنبال مقدار مشابه هستیم. ما باید این آرایه را به ترتیب صعودی مرتب کنیم.
• آرایه برای نمایش- محدوده ای حاوی داده برای نمایش نتایج. طبیعتاً باید به اندازه آرایه جستجو باشد.

هنگام نوشتن به این روش، شما یک ارجاع غیر نسبی به آرایه نتایج می دهید. و شما مستقیماً به آن اشاره می کنید، یعنی. نیازی نیست ابتدا تعداد ستون خروجی محاسبه شود. ما از تابع VIEW در مثال اول برای تابع VLOOKUP (دارایی های ثابت، شماره موجودی) استفاده می کنیم: =VIEW(B2,B5:B10,D5:D10). مشکل با موفقیت حل شد!

عملکرد VIEW در مایکروسافت اکسل

جستجو بر اساس مختصات نسبی توابع MATCH و INDEX

راه دیگر برای جستجوی داده، ترکیب توابع MATCH و INDEX است.

اولین مورد برای جستجوی یک مقدار در یک آرایه و به دست آوردن شماره سریال آن استفاده می شود: SEARCH( lookup_value; viewed_array; [نوع تطبیق] ). آرگومان های تابع:

• مقدار جستجو- استدلال مورد نیاز
• آرایه برای مشاهده- یک سطر یا ستون که در آن به دنبال مطابقت هستیم. استدلال لازم
• نوع تطبیق- برای جستجوی یک تطابق دقیق، "0"، برای نزدیکترین تطابق کوچکتر "1"، برای نزدیکترین تطابق بزرگتر "-1" را مشخص کنید. از آنجایی که تابع از ابتدای لیست تا انتها جستجو می کند، هنگام جستجوی نزدیکترین کوچکتر، ستون جستجو را به ترتیب نزولی مرتب کنید. و هنگام جستجوی موارد بیشتر، آن را به ترتیب صعودی مرتب کنید.

موقعیت مقدار مورد نیاز پیدا شده است، اکنون می توانید با استفاده از عملکرد آن را روی صفحه نمایش دهید فهرست مطالب( آرایه؛ شماره خط؛ [شماره_ستون]) :

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

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

توابع MATCH و INDEX در اکسل

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

آیا احساس می کنید که چگونه دانش و مهارت های شما در حال رشد و تقویت است؟ سپس متوقف نشوید، به خواندن ادامه دهید! در پست بعدی در نظر خواهیم گرفت: دشوار و جالب خواهد بود!

این سوال اغلب مطرح می شود « چگونه در اکسل پیدا کنیم»? در اکسل می توانید هر اطلاعاتی را بیابید: متن، بخشی از متن، شماره، شماره تلفن، ایمیل. نشانی, نام خانوادگی، فرمول، یادداشت، قالب سلول و غیره
یک سلول در محل تقاطع یک سطر و یک ستون در اکسل پیدا کنید – « چگونه یک سلول در اکسل را در تقاطع یک سطر و یک ستون پیدا کنیم» ( تابع INDEX در اکسل).
مکان دیگری را در اکسل پیدا کرده و به آن منتقل کنید(مثلا روی یک فرم)چندین داده به طور همزمان - به مقاله "یافتن چندین داده در یک زمان در اکسل" مراجعه کنید. (تابع VLOOKUP در اکسل).
یا سلول را با پیوند در پیدا کنید فرمول اکسل, برای جایگزینی لینک مراجعه کنید « پیوندها را به برگه های دیگر در فرمول های اکسل تغییر دهید».
پیدا کنید سلول های اکسلبا یادداشت - مقاله "درج یادداشت در اکسل" .
برای جستجوی سریعیک میانبر صفحه کلید وجود دارد - Ctrl+F. کلیک کلید Ctrlو با نگه داشتن آن، کلید F را فشار دهید.یک پنجره جستجو ظاهر می شود.
همچنین می توانید پنجره جستجو را به این صورت باز کنید: در تب "Home"، روی دکمه "Find and Select" کلیک کنید.
در تب "یافتن"، در سلول "یافتن"، کلمه مورد نظر خود را بنویسید (بخشی از یک کلمه امکان پذیر است) و روی "یافتن بعدی" کلیک کنید. اولین چنین کلمه ای پیدا خواهد شد. سپس روی "یافتن بعدی" کلیک کنید و جستجو به دومین کلمه مشابه منتقل می شود.

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

اگر جستجو چیزی پیدا نکرد و می دانید که این داده ها قطعا وجود دارد، سعی کنید تورفتگی را از سلول های جدول حذف کنید. نحوه حذف تورفتگی در سلول ها، به مقاله مراجعه کنید " متن اکسل. قالب".
پیدا کردن عدد در اکسلنیاز به تنظیم جزئی شرایط جستجو - قابل اجرا است جستجوی پیشرفته در اکسل.
مشاوره.
اگر برای مدت طولانی با جدول کار می کنید و اغلب نیاز دارید که از یک کلمه به کلمه دیگر جستجو کنید.سپس راحت تر است که پنجره جستجو را هر بار نبندید، بلکه آن را به قسمتی از جدول منتقل کنید که در آن تداخل نداشته باشد. می توانید آن را به زیر صفحه منتقل کنید و تنها سلول ورودی کلمه جستجو ("یافتن") را باقی بگذارید و سپس Enter را فشار دهید.
این گفتگوی جستجو همیشه روی صفحه باقی می ماند، حتی اگر به صفحه دیگری بروید. با استفاده از این پنجره می توانید در هر صفحه ای جستجو کنید، فقط باید آن را در آن فعال کنید صفحه باز شود. برای انجام این کار، روی مکان نما در خط "یافتن" کلیک کنید.
برای جستجوی پیشرفته تر، روی دکمه Options کلیک کرده و گزینه جستجوی مورد نظر را انتخاب کنید.
به عنوان مثال، "ارزش" را انتخاب کنید. سپس شماره، شماره تلفن و غیره را جستجو می کند.
اگر نیاز دارید همه کلمات را با وزن یکسان، اما در موارد (شیر، شیر، شیر و غیره) پیدا کنید، سپس فرمولی را با حروف عام می نویسیم. در این مورد به "Waldcards in Excel" مراجعه کنید.
تابع اکسل "یافتن و برجسته کردن"نه تنها داده ها را پیدا می کند، بلکه آنها را جایگزین می کند. به مقاله “نحوه کپی فرمول در اکسل بدون تغییر لینک” مراجعه کنید.
نحوه حذف فضاهای اضافی که در پردازش داده ها در یک جدول اختلال ایجاد می کنند، مقاله "نحوه حذف فضاهای اضافی در اکسل" را مطالعه کنید.
در اکسل، می توانید هر اطلاعاتی را نه تنها با تابع جستجو یا فرمول ها، بلکه با تابع قالب بندی شرطی پیدا کنید. مقاله “قالب بندی مشروط در اکسل” را در این مورد بخوانید.
همچنین می توانید در مورد عملکرد "یافتن و انتخاب" در مقاله " بخوانیدفیلتر در اکسل".