فهرست
این آموزش کوچک نحوه ساخت یک تابع را توضیح می دهد VPR (VLOOKUP) به حروف بزرگ و کوچک، چندین فرمول دیگر را نشان می دهد که اکسل می تواند به روشی حساس به حروف بزرگ و کوچک جستجو کند، و به نقاط قوت و ضعف هر تابع اشاره می کند.
حدس می زنم هر کاربر اکسل می داند که چه عملکردی جستجوی عمودی را انجام می دهد. درست است، این یک تابع است VPR. با این حال، افراد کمی این را می دانند VPR به حروف بزرگ و کوچک حساس نیست، یعنی حروف کوچک و بزرگ برای آن یکسان است.
در اینجا یک مثال سریع نشان دهنده ناتوانی است VPR شناسایی ثبت نام فرض کنید در یک سلول A1 حاوی مقدار "بیل" و سلول است A2 - "بیل"، فرمول:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... جستجوی خود را در "بیل" متوقف می کند زیرا این مقدار در لیست اول است و مقدار را از سلول استخراج می کند B1.
بعداً در این مقاله، من به شما نشان خواهم داد که چگونه این کار را انجام دهید VPR حساس به حروف کوچک و بزرگ علاوه بر این، ما چند عملکرد دیگر را یاد خواهیم گرفت که می توانند جستجوهای حساس به حروف بزرگ را در اکسل انجام دهند.
ما با ساده ترین شروع می کنیم - VIEW (جستجو) و SUMPRODUCT (SUMPRODUCT) که متأسفانه دارای چندین محدودیت قابل توجه است. در ادامه، نگاهی دقیق‌تر به فرمول کمی پیچیده‌تر خواهیم داشت INDEX + MATCH (INDEX+MATCH) که در هر شرایطی و با هر مجموعه داده ای بی عیب و نقص کار می کند.
تابع VLOOKUP به حروف بزرگ و کوچک حساس است
همانطور که می دانید، عملکرد معمولی است VPR به حروف بزرگ و کوچک حساس است با این حال، راهی برای حساس کردن آن به حروف بزرگ و کوچک وجود دارد. برای این کار باید یک ستون کمکی به جدول اضافه کنید، همانطور که در مثال زیر نشان داده شده است.
فرض کنید در یک ستون B شناسه های محصول (Item) وجود دارد و می خواهید قیمت محصول و نظر مربوطه را از ستون ها استخراج کنید C и D. مشکل اینجاست که شناسه ها دارای حروف کوچک و بزرگ هستند. به عنوان مثال، مقادیر سلول B4 (001Tvci3u) و B5 (001Tvci3U) فقط در مورد آخرین کاراکتر متفاوت است، u и U بود.
همانطور که می توانید تصور کنید، فرمول جستجوی معمولی است
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
بر خواهد گشت $ 90، از آنجایی که ارزش 001Tvci3u در محدوده جستجو زودتر از 001Tvci3U. اما این چیزی نیست که ما نیاز داریم، درست است؟
برای جستجو با یک تابع VPR در اکسل حساس به حروف بزرگ، باید یک ستون کمکی اضافه کنید و سلول های آن را با فرمول زیر پر کنید (که B ستون جستجو است):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
این فرمول مقدار مورد نظر را به کاراکترهای جداگانه تقسیم می کند، کد خود را جایگزین هر کاراکتر می کند (به عنوان مثال، به جای A در عوض در 65 سالگی a کد 97) و سپس این کدها را در یک رشته منحصر به فرد از اعداد ترکیب می کند.
پس از آن، از یک تابع ساده استفاده می کنیم VPR برای جستجوی حساس به حروف کوچک و بزرگ:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
عملکرد صحیح عملکرد VPR حساس بودن به حروف بزرگ و کوچک به دو عامل بستگی دارد:
- ستون کمکی باید سمت چپ ترین ستون در محدوده قابل مشاهده باشد.
- مقداری که به دنبال آن هستید باید به جای مقدار واقعی یک کد کاراکتری داشته باشد.
نحوه استفاده صحیح از تابع CODE
فرمول درج شده در سلول های ستون کمکی فرض می کند که همه مقادیر جستجوی شما دارای تعداد کاراکترهای یکسانی هستند. اگر نه، پس باید کوچکترین و بزرگترین اعداد را بدانید و به همان تعداد ویژگی اضافه کنید IFERROR (IFERROR) تفاوت بین کوتاه ترین و طولانی ترین مقدار جستجو چند کاراکتر است.
به عنوان مثال، اگر کوتاه ترین مقدار جستجو 3 کاراکتر و طولانی ترین آن 5 کاراکتر است، از این فرمول استفاده کنید:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
برای عملکرد PSTR (MID) شما آرگومان های زیر را ارائه می دهید:
- استدلال اول - متن (متن) مرجع متن یا سلول حاوی کاراکترهایی است که باید استخراج شوند (در مورد ما B2 است)
- استدلال اول - start_num (start_position) موقعیت اولین شخصیتی است که باید استخراج شود. شما وارد شوید 1 در اولین تابع PSTR, 2 – در تابع دوم PSTR و غیره.
- استدلال اول - num_chars (تعداد_شخصیت ها) - تعداد کاراکترهایی را که باید از متن استخراج شوند را مشخص می کند. از آنجایی که ما همیشه فقط به 1 کاراکتر نیاز داریم، در همه توابع می نویسیم 1.
محدودیت ها: عملکرد VPR بهترین راه حل برای جستجوهای حساس به حروف بزرگ در اکسل نیست. ابتدا، اضافه کردن یک ستون کمکی مورد نیاز است. ثانیاً، فرمول فقط در صورتی کار خوبی انجام می دهد که داده ها همگن باشند یا تعداد دقیق کاراکترها در مقادیر جستجو شده مشخص باشد. اگر این مورد شما نیست، بهتر است از یکی از راه حل هایی که در زیر نشان می دهیم استفاده کنید.
عملکرد LOOKUP برای جستجوی حساس به حروف بزرگ و کوچک
عملکرد VIEW (جستجو) مرتبط VPR، با این حال نحو آن امکان جستجوهای حساس به حروف بزرگ را بدون اضافه کردن ستون کمکی فراهم می کند. برای این کار استفاده کنید VIEW همراه با تابع دقیق (دقیق).
اگر داده ها را از مثال قبلی (بدون ستون کمکی) بگیریم، فرمول زیر با این کار مقابله می کند:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
جستجوی فرمول در محدوده A2: A7 مطابقت دقیق با مقدار سلول F2 به حروف بزرگ و کوچک حساس است و مقدار را از ستون B همان سطر برمی گرداند.
پسندیدن VPRتابع VIEW همانطور که در تصویر زیر می بینید، با مقادیر متن و عددی به طور مساوی کار می کند:
مهم! به منظور عملکرد VIEW به درستی کار می کند، مقادیر در ستون جستجو باید به ترتیب صعودی، یعنی از کوچکترین به بزرگترین مرتب شوند.
اجازه دهید به طور خلاصه نحوه عملکرد تابع را توضیح دهم دقیق در فرمول نشان داده شده در بالا، زیرا این نکته کلیدی است.
عملکرد دقیق دو مقدار متن را در آرگومان های 1 و 2 مقایسه می کند و اگر دقیقاً یکسان باشند TRUE یا اگر نیستند FALSE را برمی گرداند. برای ما مهم است که عملکرد دقیق حساس به حروف کوچک و بزرگ
بیایید ببینیم فرمول ما چگونه کار می کند VIEW+EXACT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- عملکرد دقیق مقدار سلول را مقایسه می کند F2 با تمام عناصر در یک ستون A (A2:A7). اگر مطابقت دقیق پیدا شود، TRUE را برمی‌گرداند، در غیر این صورت FALSE.
- از آنجایی که شما اولین آرگومان تابع را می دهید VIEW مقدار TRUE، مقدار مربوطه را از ستون مشخص شده (در مورد ما، ستون B) تنها در صورتی استخراج می کند که مطابقت دقیق و حساس به حروف بزرگ و کوچک پیدا شود.
امیدوارم این توضیح واضح بوده باشد و اکنون ایده اصلی را درک کرده باشید. اگر چنین است، پس با سایر عملکردها که ما بیشتر تجزیه و تحلیل خواهیم کرد، مشکلی نخواهید داشت، زیرا. همه آنها بر اساس یک اصل کار می کنند.
محدودیت ها: داده های ستون جستجو باید به ترتیب صعودی مرتب شوند.
SUMPRODUCT - مقادیر متن را پیدا می کند، به حروف بزرگ و کوچک حساس است، اما فقط اعداد را برمی گرداند
همانطور که قبلا از عنوان متوجه شدید، SUMPRODUCT (SUMPRODUCT) یکی دیگر از تابع های اکسل است که به شما کمک می کند جستجوی حساس به حروف کوچک و بزرگ انجام دهید، اما فقط مقادیر عددی را برمی گرداند. اگر این گزینه برای شما مناسب نیست، می توانید بلافاصله به بسته نرم افزاری بروید INDEX + MATCH، که برای هر مورد و برای هر نوع داده راه حلی ارائه می دهد.
ابتدا، اجازه دهید نحو این تابع را به اختصار توضیح دهم، این به شما کمک می کند فرمول حساس به حروف بزرگ را که در ادامه می آید بهتر درک کنید.
عملکرد SUMPRODUCT عناصر آرایه های داده شده را ضرب می کند و مجموع نتایج را برمی گرداند. نحو به این صورت است:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
از آنجایی که ما به جستجوی حساس به حروف بزرگ نیاز داریم، از تابع استفاده می کنیم دقیق (EXACT) از مثال قبلی به عنوان یکی از ضرب کننده ها:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
همانطور که به یاد می آورید دقیق مقدار سلول را مقایسه می کند F2 با تمام عناصر در یک ستون A. اگر مطابقت دقیق پیدا شود، TRUE را برمی‌گرداند، در غیر این صورت FALSE. در عملیات ریاضی، اکسل TRUE را به عنوان TRUE در نظر می گیرد 1و FALSE برای 0بیشتر SUMPRODUCT این اعداد را ضرب می کند و نتایج را جمع می کند.
صفرها شمرده نمی شوند زیرا وقتی ضرب می شوند همیشه می دهند 0. بیایید نگاهی دقیق‌تر به آنچه اتفاق می‌افتد، با تطابق دقیق در یک ستون بیندازیم A پیدا کرد و برگشت 1… عملکرد SUMPRODUCT عدد موجود در ستون را ضرب می کند B on 1 و نتیجه را برمی گرداند - دقیقاً همان عدد! این به این دلیل است که نتایج سایر محصولات صفر است و بر جمع حاصل تأثیری ندارند.
متاسفانه عملکرد SUMPRODUCT نمی توان با مقادیر متن و تاریخ کار کرد زیرا نمی توان آنها را ضرب کرد. در این صورت یک پیغام خطا دریافت خواهید کرد #ارزش! (#VALUE!) مانند یک سلول F4 در تصویر زیر:
محدودیت ها: فقط مقادیر عددی را برمی گرداند.
INDEX + MATCH - جستجوی حساس به حروف بزرگ برای هر نوع داده
در نهایت، ما به فرمول جستجوی نامحدود و حساس به حروف کوچک و بزرگ نزدیک هستیم که با هر مجموعه داده ای کار می کند.
این مثال آخر است، نه به این دلیل که بهترین ها برای دسر باقی می مانند، بلکه به این دلیل که دانش به دست آمده از مثال های قبلی به شما کمک می کند فرمول حساس به حروف کوچک و بزرگ را بهتر و سریع تر درک کنید. INDEX + MATCH (INDEX+MATCH).
همانطور که احتمالا حدس زده اید، ترکیبی از توابع بیشتر در معرض и شاخص در اکسل به عنوان جایگزینی انعطاف پذیرتر و قدرتمندتر برای VPR. مقاله استفاده از INDEX و MATCH به جای VLOOKUP به خوبی توضیح می دهد که چگونه این توابع با هم کار می کنند.
من فقط نکات کلیدی را مرور می کنم:
- عملکرد بیشتر در معرض (MATCH) یک مقدار را در یک محدوده معین جستجو می کند و موقعیت نسبی آن، یعنی شماره ردیف و/یا ستون را برمی گرداند.
- بعد، تابع شاخص (INDEX) مقداری را از یک ستون و/یا ردیف مشخص برمی گرداند.
به فرمول INDEX + MATCH می تواند به صورت حساس به حروف بزرگ و کوچک جستجو کند، فقط باید یک تابع به آن اضافه کنید. حدس زدن دوباره چیست سخت نیست دقیق (دقیق):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
در این فرمول دقیق به همان روشی که در ارتباط با تابع کار می کند VIEW، و همین نتیجه را می دهد:
توجه داشته باشید که فرمول INDEX + MATCH محصور در بریس های فرفری یک فرمول آرایه است و باید با فشار دادن آن را کامل کنید Ctrl + Shift + Enter.
چرا INDEX+MATCH بهترین راه حل برای جستجوی حساس به حروف بزرگ است؟
مزایای اصلی بسته نرم افزاری شاخص и بیشتر در معرض:
- نیازی به افزودن ستون کمکی ندارد، برخلاف VPR.
- نیازی به مرتب سازی ستون جستجو ندارد، برخلاف VIEW.
- با انواع داده ها - اعداد، متن و تاریخ کار می کند.
این فرمول کامل به نظر می رسد، اینطور نیست؟ در واقع اینطور نیست. و به همین دلیل.
فرض کنید سلول در ستون مقدار بازگشتی مرتبط با مقدار جستجو خالی است. فرمول چه نتیجه ای خواهد داشت؟ نه؟ بیایید ببینیم فرمول در واقع چه چیزی را برمی گرداند:
اوه، فرمول صفر را برمی گرداند! اگر با مقادیر متن خالص کار می کنید، این ممکن است مشکل بزرگی نباشد. با این حال، اگر جدول حاوی اعداد، از جمله صفرهای "واقعی" باشد، این یک مشکل می شود.
در واقع، تمام فرمول‌های جستجوی دیگر (VLOOKUP، LOOKUP و SUMPRODUCT) که قبلاً در مورد آنها صحبت کردیم، به همین ترتیب عمل می‌کنند. اما شما فرمول کامل را می خواهید، درست است؟
برای اینکه یک فرمول به حروف کوچک و بزرگ حساس شود INDEX + MATCH کامل، آن را در یک تابع قرار دهید IF (IF) که سلولی را با مقدار بازگشتی آزمایش می کند و در صورت خالی بودن، نتیجه خالی را برمی گرداند:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
در این فرمول:
- B یک ستون با مقادیر بازگشتی است
- 1+ عددی است که موقعیت نسبی سلول برگردانده شده توسط تابع را تغییر می دهد بیشتر در معرض، به آدرس واقعی سلول. به عنوان مثال، در عملکرد ما بیشتر در معرض آرایه جستجو داده شده است A2: A7، یعنی موقعیت نسبی سلول A2 اراده 1، زیرا اولین مورد در آرایه است. اما موقعیت واقعی سلول A2 در ستون است 2، بنابراین اضافه می کنیم 1برای جبران تفاوت و داشتن عملکرد غیر مستقیم (INDIRECT) مقدار را از سلول مورد نظر بازیابی کرد.
تصاویر زیر فرمول اصلاح شده حساس به حروف بزرگ را نشان می دهد INDEX + MATCH در عمل. اگر سلول برگشتی خالی باشد، نتیجه خالی برمی گرداند.
من فرمول را در ستون ها بازنویسی کردم ب:دیبرای جا دادن نوار فرمول روی اسکرین شات.
فرمول برمی گردد 0اگر سلول برگشتی حاوی صفر باشد.
اگه لینکشو میخوای شاخص и بیشتر در معرض زمانی که مقدار بازگشتی خالی است، پیامی نمایش داده می شود، می توانید آن را در آخرین نقل قول (“”) فرمول بنویسید، به عنوان مثال، مانند این:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
