کیم ستونی از داده‌ها دارد که حاوی کد مکان است. این کد شامل یک حرف منفرد به دنبال دو رقم مانند A03 یا B12 است. کیم می‌خواهد ستون را به‌طور مشروط قالب‌بندی کند تا هر چیزی که وارد ستون می‌شود و از این الگو استفاده نمی‌کند، به نوعی برجسته شود.
راه های زیادی برای حل این مشکل وجود دارد. هر رویکرد به توسعه فرمولی بستگی دارد که می تواند در یک قاعده قالب بندی شرطی برای برگرداندن True یا False و راه اندازی قالب شرطی استفاده شود. (این نکته به چگونگی ایجاد یک قانون قالب‌بندی شرطی نمی‌پردازد، اما در عوض روی فرمول‌های مختلفی که می‌توان در قانون استفاده کرد تمرکز می‌کند. نحوه ایجاد قوانین قالب‌بندی شرطی در سایر ExcelTips پوشش داده شده است . )
هر فرمولی که کنار هم قرار می گیرد باید سه چیز را آزمایش کند:
- دقیقاً سه کاراکتر در رشته وجود دارد.
- کاراکتر اول یک حرف است.
- کاراکترهای دوم و سوم رقمی هستند.
پیدا کردن اینکه آیا متن در یک سلول فقط سه کاراکتر دارد بسیار آسان است. برای انجام این کار می توانید از تابع LEN استفاده کنید:
=LEN(A1)=3
فهمیدن اینکه آیا کاراکتر اول یک حرف است نیز بسیار آسان است. در واقع، چند راه وجود دارد که می توان آن را انجام داد. اگر کاراکتر اول یک حرف باشد، هر یک از موارد زیر True برمی‌گرداند:
=AND(CODE(LEFT(A1,1))>64,CODE(LEFT(A1,1))<91)
=AND(LEFT(A1,1)>="A",LEFT(A1,1)
اینها بررسی می کنند تا مطمئن شوند که فقط یک حرف بزرگ در موقعیت اول قرار دارد. اگر می‌خواهید حروف کوچک را نیز بپذیرید، می‌توانید از تغییرات آزمون دوم استفاده کنید:
=AND(UPPER(LEFT(A1,1))>="A",UPPER(LEFT(A1,1))
اگر هر دو حروف بزرگ و کوچک قابل قبول هستند (همراه با هر علامت دیگری)، ممکن است از آزمون زیر استفاده کنید:
=NOT(ISNUMBER(LEFT(A1,1)+0))
در اینجا چند روش وجود دارد که می توانید آزمایش سوم را اعمال کنید - اینکه آیا کاراکترهای دوم و سوم رقم هستند یا خیر:
=ISNUMBER(VALUE(RIGHT(A1,2)))
=ISNUMBER(--RIGHT(A1,2))
توجه داشته باشید که این رویکردها دو شخصیت آخر را با هم بررسی می کنند. این بدان معناست که «1»، «11» و «111» همگی آزمون را پشت سر می‌گذارند—آنها با موفقیت به عنوان اعداد بررسی می‌شوند. اگر فرمول شما فقط دو رقم آخر را بررسی می کرد، این می تواند یک مشکل باشد، اما این واقعیت که شما اولین بررسی را نیز وارد می کنید (برای طول کلی رشته در سلول و اینکه باید 3 باشد)، پس اینطور نیست. اصلا مشکل ایجاد کنه
ترفند، در حال حاضر، ترکیب رویکرد انتخابی خود برای هر یک از سه آزمون در یک فرمول واحد است. این را می توان با استفاده از تابع AND انجام داد. فقط کوتاه ترین را از هر یک از تست ها انتخاب کنید و آنها را به این ترتیب ترکیب کنید:
=AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)
همانطور که نوشته شده است، این فرمول در صورتی که تمام تست ها پاس شده باشند، True را برمی گرداند، به این معنی که سلول حاوی یک کد مکان با یک الگوی معتبر است. اگر کیم ستون را به عنوان رنگ (مثلاً سبز) قالب بندی کند و سپس از قالب شرطی برای حذف رنگ سبز استفاده کند، این به عنوان یک قالب شرطی عالی عمل می کند. این ممکن است عقب مانده به نظر برسد، و شما ممکن است در واقع فقط بخواهید یک قالب را در صورتی اعمال کنید که الگو رعایت نشود. اگر اینطور است، پس به سادگی فرمول را در یک تابع NOT محصور کنید تا درست/نادرستی که برگردانده شده است را معکوس کنید:
=NOT(AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)
همانطور که می توانید بگویید، استفاده از فرمولی مانند این می تواند کمی مشکل باشد. اگر ترجیح می دهید، می توانید یک UDF (عملکرد تعریف شده توسط کاربر) ایجاد کنید که قانون قالب بندی شرطی را کمی کوتاه تر کند. ماکرو زیر راه خوبی است:
Function IsBadPattern(sCell As String) As Boolean
IsBadPattern = Not(sCell Like "[A-Z][0-9][0-9]")
End Function
برای استفاده از UDF در قانون قالب بندی شرطی، تنها کاری که باید انجام دهید این است که از فرمول زیر استفاده کنید:
=IsBadPattern(A1)
اگر رشته در سلول ارجاع شده با الگوی مورد نظر شما مطابقت نداشته باشد، نتیجه UDF درست خواهد بود. همانطور که نوشته شده است، اجازه استفاده از حروف کوچک را در موقعیت کاراکتر اول نمی دهد. اگر باید حروف کوچک را مجاز کنید، نیازی به تغییر UDF ندارید. در عوض، فرمول را به شکل زیر تغییر دهید:
=IsBadPattern(UPPER(A1))
