خرید حضوری و مطمئن ارز دیجیتال
تبلیغات افزایش بازدید و افزایش فروش در ارم بلاگ
مشاوره آنلاین روانشناسی به فارسی زبانان خارج از ایران
دیگه نگو شام چی بپزم !! ○مجله آشپزی شکمو○
این مقاله راهنمایی دقیق در مورد نحوه استفاده از مجموعه نمادهای قالب بندی در اکسل ارائه می دهد. این به شما یاد می دهد که چگونه یک مجموعه آیکون سفارشی ایجاد کنید که بر بسیاری از محدودیت های گزینه های داخلی غلبه کند و آیکون ها را بر اساس مقدار اعمال کنید.
چندی پیش شروع به بررسی ویژگی ها و قابلیت های مختلف Conditional Formatting در اکسل کردیم. اگر از قبل اصول اولیه را میدانید، بیایید ادامه دهیم و ببینیم چه گزینههایی با توجه به مجموعه نمادهای اکسل دارید و چگونه میتوانید از آنها در پروژههای خود استفاده کنید.
مجموعه آیکونها در اکسل گزینههای قالببندی آمادهای هستند که آیکونهای مختلفی را به سلولها اضافه میکنند، مانند فلشها، شکلها، علامتهای چک، پرچمها، شروع رتبهبندی و غیره تا به صورت بصری نشان دهند که مقادیر سلولها در یک محدوده چگونه با یکدیگر مقایسه میشوند.
به طور معمول، یک مجموعه آیکون شامل سه تا پنج نماد است، در نتیجه مقادیر سلول ها در یک محدوده قالب بندی شده به سه تا پنج گروه از بالا به پایین تقسیم می شوند. به عنوان مثال، یک مجموعه 3 آیکون از یک نماد برای مقادیر بزرگتر یا مساوی 67٪، از نماد دیگری برای مقادیر بین 67٪ تا 33٪ و نماد دیگری برای مقادیر کمتر از 33٪ استفاده می کند. با این حال، شما آزاد هستید که این رفتار پیش فرض را تغییر دهید و معیارهای خود را تعریف کنید.
برای اعمال یک مجموعه آیکون روی داده های خود، این کاری است که باید انجام دهید:
محدوده سلول هایی را که می خواهید قالب بندی کنید انتخاب کنید.
در تب Home ، در گروه Styles ، روی Conditional Formatting کلیک کنید .
به نماد مجموعهها اشاره کنید و سپس روی نوع نماد مورد نظر کلیک کنید.
همین! نمادها بلافاصله در داخل سلول های انتخاب شده ظاهر می شوند.
اگر از نحوه تفسیر و برجسته سازی داده های اکسل راضی نیستید، می توانید به راحتی مجموعه آیکون های اعمال شده را سفارشی کنید. برای ویرایش، مراحل زیر را دنبال کنید:
هر سلولی را که به صورت مشروط با مجموعه آیکون قالب بندی شده است، انتخاب کنید.
در برگه صفحه اصلی ، روی Conditional Formatting > Manage Rules کلیک کنید .
قانون مورد علاقه را انتخاب کنید و روی Edit Rule کلیک کنید .
در کادر محاورهای Edit Formatting Rule ، میتوانید آیکونهای دیگری را انتخاب کرده و آنها را به مقادیر مختلف اختصاص دهید. برای انتخاب نماد دیگری، روی دکمه کشویی کلیک کنید و لیستی از تمام نمادهای موجود برای قالب بندی شرطی را مشاهده خواهید کرد.
پس از اتمام ویرایش، برای ذخیره تغییرات و بازگشت به اکسل، دوبار روی OK کلیک کنید.
برای مثال، ما صلیب قرمز را برای برجسته کردن مقادیر بیشتر یا مساوی 50٪ و علامت تیک سبز را برای برجسته کردن مقادیر کمتر از 20٪ انتخاب کرده ایم. برای مقادیر بین، از علامت تعجب زرد استفاده می شود.
نکات:
برای معکوس کردن تنظیمات نماد ، روی دکمه Reverse Icon Order کلیک کنید .
برای مخفی کردن مقادیر سلول و نمایش فقط نمادها ، کادر نمایش فقط نماد را انتخاب کنید .
برای تعریف معیارها بر اساس مقدار سلول دیگر ، آدرس سلول را در کادر Value وارد کنید .
می توانید از مجموعه آیکون ها همراه با سایر قالب های شرطی استفاده کنید ، به عنوان مثال برای تغییر رنگ پس زمینه سلول های حاوی نمادها.
در مایکروسافت اکسل، 4 نوع مجموعه آیکون مختلف وجود دارد: جهت، اشکال، نشانگرها و رتبه بندی. هنگام ایجاد قانون خود، می توانید از هر نمادی از هر مجموعه ای استفاده کنید و هر مقداری را به آن اختصاص دهید.
برای ایجاد مجموعه آیکون های سفارشی خود، این مراحل را دنبال کنید:
محدوده سلول هایی را که می خواهید نمادها را در آن اعمال کنید انتخاب کنید.
روی Conditional Formatting > Icon Sets > More Rules کلیک کنید .
در کادر محاوره ای New Formatting Rule ، نمادهای مورد نظر را انتخاب کنید. از کادر کشویی Type ، درصد ، تعداد فرمول را انتخاب کنید و مقادیر مربوطه را در کادرهای Value تایپ کنید
در نهایت روی OK کلیک کنید .
برای این مثال، ما یک مجموعه آیکون سه پرچم سفارشی ایجاد کردهایم که در آن:
پرچم سبز هزینههای خانوار را بیشتر یا مساوی 100 دلار نشان میدهد.
پرچم زرد به اعداد کمتر از 100 دلار و بزرگتر یا مساوی 30 دلار اختصاص داده می شود.
پرچم سبز برای مقادیر کمتر از 30 دلار استفاده می شود.
به جای "هاردکد کردن" معیارها در یک قانون، می توانید هر شرط را در یک سلول جداگانه وارد کنید و سپس به آن سلول ها مراجعه کنید. مزیت کلیدی این روش این است که می توانید به راحتی شرایط را با تغییر مقادیر در سلول های ارجاع شده بدون ویرایش قانون تغییر دهید.
به عنوان مثال، ما دو شرط اصلی را در سلول های G2 و G3 وارد کرده ایم و قانون را به این صورت پیکربندی کرده ایم:
برای نوع ، فرمول را انتخاب کنید .
برای کادر Value ، آدرس سلول را که قبل از علامت برابری قرار داده شده است وارد کنید. برای انجام خودکار آن توسط اکسل، فقط مکان نما را در کادر قرار دهید و روی سلول روی برگه کلیک کنید.
برای اینکه شرایط به طور خودکار توسط اکسل محاسبه شود، می توانید آنها را با استفاده از یک فرمول بیان کنید.
برای اعمال قالب بندی شرطی با نمادهای فرمول محور، شروع به ایجاد یک مجموعه آیکون سفارشی همانطور که در بالا توضیح داده شد، کنید. در کادر محاورهای New Formatting Rule ، از کادر کشویی Type ، Formula را انتخاب کنید و فرمول خود را در کادر Value وارد کنید .
برای این مثال از فرمول های زیر استفاده می شود:
پرچم سبز به اعداد بزرگتر یا مساوی با میانگین + 10 اختصاص داده می شود:
=AVERAGE($B$2:$B$13)+10
پرچم زرد به اعداد کمتر از میانگین + 10 و بزرگتر یا مساوی میانگین - 20 اختصاص داده می شود.
=AVERAGE($B$2:$B$13)-20
پرچم سبز برای مقادیر کمتر از میانگین - 20 استفاده می شود.
توجه داشته باشید. استفاده از ارجاعات نسبی در فرمول های مجموعه آیکون ممکن نیست .
نماد قالب شرطی اکسل برای مقایسه 2 ستون تنظیم شده است
هنگام مقایسه دو ستون، مجموعه آیکونهای قالببندی شرطی، مانند فلشهای رنگی، میتوانند یک نمایش بصری عالی از مقایسه را به شما ارائه دهند. این را می توان با استفاده از یک مجموعه آیکون در ترکیب با فرمولی انجام داد که تفاوت بین مقادیر را در دو ستون محاسبه می کند - فرمول درصد تغییر به خوبی برای این منظور کار می کند.
فرض کنید هزینه های ژوئن و جولای را به ترتیب در ستون های B و C دارید . برای محاسبه میزان تغییر مقدار بین دو ماه، فرمول در D2 کپی شده است:
=C2/B2 - 1
فلش رو به بالا اگر درصد تغییر یک عدد مثبت باشد (مقدار در ستون C بیشتر از ستون B است).
فلش رو به پایین اگر تفاوت یک عدد منفی باشد (مقدار ستون C کمتر از ستون B است).
یک فلش افقی اگر درصد تغییر صفر باشد (ستون های B و C برابر هستند).
برای انجام این کار، یک قانون تنظیم آیکون سفارشی با این تنظیمات ایجاد می کنید:
وقتی مقدار > 0 باشد، یک پیکان سبز به سمت بالا .
فلش سمت راست زرد وقتی مقدار <=0 و >=0 است، که انتخاب را به صفر محدود می کند.
یک فلش رو به پایین قرمز وقتی مقدار < 0 باشد.
برای همه نمادها، Type روی Number تنظیم شده است .
در این مرحله، نتیجه چیزی شبیه به این خواهد بود:
برای نشان دادن فقط نمادها بدون درصد، تیک Show Icon Only را بزنید.
یک عقیده رایج این است که مجموعه نمادهای قالب بندی شرطی اکسل را فقط می توان برای قالب بندی سلول ها بر اساس مقادیر خود استفاده کرد. از نظر فنی، این درست است. با این حال، میتوانید مجموعه نمادهای قالب شرطی را بر اساس مقداری در سلول دیگر شبیهسازی کنید.
فرض کنید در ستون D تاریخ های پرداخت دارید. هدف شما این است که وقتی صورتحساب خاصی پرداخت می شود، یک پرچم سبز در ستون A قرار دهید، یعنی در سلول مربوطه در ستون D یک تاریخ وجود دارد. اگر سلولی در ستون D خالی است، باید یک پرچم قرمز درج شود.
برای انجام کار، مراحل زیر انجام می شود:
با افزودن فرمول زیر به A2 شروع کنید و سپس آن را در ستون کپی کنید :
=IF($D2<>"", 3, 1)
فرمول می گوید اگر D2 خالی نیست 3 برگردانید و در غیر این صورت 1.
سلول های داده در ستون A را بدون سرصفحه ستون (A2:A13) انتخاب کنید و یک قانون تنظیم نماد سفارشی ایجاد کنید .
تنظیمات زیر را پیکربندی کنید:
زمانی که عدد >=3 باشد، پرچم سبز را نشان دهید.
زمانی که عدد > 2 باشد، پرچم زرد نشان دهید. همانطور که به یاد دارید، ما واقعاً هیچ جا پرچم زرد نمی خواهیم، بنابراین شرطی را تعیین می کنیم که هرگز برآورده نمی شود، یعنی مقدار کمتر از 3 و بزرگتر از 2.
در کادر کشویی Type ، شماره را برای هر دو نماد انتخاب کنید.
برای پنهان کردن اعداد و نمایش فقط نمادها، کادر علامت فقط تنظیم را انتخاب کنید .
نتیجه دقیقاً همان چیزی است که ما به دنبال آن بودیم: پرچم سبز اگر سلولی در ستون D حاوی چیزی در آن باشد و پرچم قرمز اگر سلول خالی باشد.
به طور پیش فرض، مجموعه آیکون های اکسل برای قالب بندی اعداد طراحی شده اند، نه متن. اما تنها با کمی خلاقیت، میتوانید آیکونهای مختلفی را به مقادیر متنی خاص اختصاص دهید، بنابراین میتوانید با یک نگاه ببینید که در این یا آن سلول چه متنی وجود دارد.
فرض کنید ستون یادداشت را به جدول هزینه های خانوار خود اضافه کرده اید و می خواهید نمادهای خاصی را بر اساس برچسب های متنی در آن ستون اعمال کنید. این کار مستلزم کارهای مقدماتی است مانند:
یک جدول خلاصه (F2:G4) با شماره گذاری هر یادداشت تهیه کنید. ایده این است که در اینجا از یک عدد مثبت، منفی و صفر استفاده کنید.
یک ستون دیگر به جدول اصلی به نام Icon اضافه کنید (این جایی است که نمادها قرار است قرار گیرند).
ستون جدید را با فرمول VLOOKUP پر می کند که یادداشت ها را جستجو می کند و اعداد مطابق را از جدول خلاصه برمی گرداند:
=VLOOKUP(C2, $F$2:$G$4, 2, FALSE)
اکنون زمان آن است که آیکون هایی را به یادداشت های متنی خود اضافه کنیم:
محدوده D2:D13 را انتخاب کنید و روی Conditional Formatting > Icon Sets > More Rules کلیک کنید .
سبک آیکون مورد نظر خود را انتخاب کنید و قانون را مانند تصویر زیر پیکربندی کنید:
مرحله بعدی جایگزینی اعداد با یادداشت های متنی است. این را می توان با اعمال یک قالب شماره سفارشی انجام داد . بنابراین، دوباره محدوده D2:D13 را انتخاب کنید و دکمه را فشار دهیدCTRL + 1میانبر
در کادر محاورهای Format Cells ، در تب Number ، دسته سفارشی را انتخاب کنید ، فرمت زیر را در کادر Type وارد کنید و روی OK کلیک کنید :
"خوب"؛ گزاف"؛ "قابل قبول"
جایی که " خوب " مقدار نمایش برای اعداد مثبت، " گزاف " برای اعداد منفی و " قابل قبول " برای 0 است. لطفاً مطمئن شوید که آن مقادیر را به درستی با متن خود جایگزین کنید.
این به نتیجه مطلوب بسیار نزدیک است، اینطور نیست؟
برای خلاص شدن از شر ستون Note که زائد شده است، محتویات ستون Icon را کپی کنید و سپس از ویژگی Paste Special برای چسباندن به عنوان مقادیر در همان مکان استفاده کنید. با این حال، لطفاً به خاطر داشته باشید که این کار آیکونهای شما را ثابت میکند، بنابراین به تغییرات دادههای اصلی پاسخ نمیدهند. اگر با یک مجموعه داده قابل بهروزرسانی کار میکنید، از این مرحله رد شوید.
اکنون، میتوانید با خیال راحت ستون Note را پنهان یا حذف کنید (اگر فرمولها را با مقادیر محاسبهشده جایگزین کرده باشید) بدون اینکه روی برچسبها و نمادهای متنی در ستون Icon تأثیر بگذارد . انجام شد!
توجه داشته باشید. در این مثال، ما از مجموعه 3 آیکون استفاده کرده ایم. اعمال مجموعه های 5 آیکون بر اساس متن نیز امکان پذیر است اما به دستکاری های بیشتری نیاز دارد.
چگونه فقط برخی از موارد مجموعه آیکون را نشان دهیم
مجموعههای 3 آیکون و 5 نماد داخلی اکسل زیبا به نظر میرسند، اما گاهی اوقات ممکن است آنها را کمی غرق در گرافیک ببینید. راه حل این است که فقط آن نمادهایی را نگه دارید که توجه را به مهم ترین موارد جلب می کند، مثلاً بهترین عملکرد یا بدترین عملکرد.
برای مثال، هنگام برجسته کردن هزینهها با نمادهای مختلف، ممکن است بخواهید فقط مواردی را نشان دهید که مقادیر بالاتر از میانگین را نشان میدهند. بیایید ببینیم چگونه می توانید این کار را انجام دهید:
با کلیک کردن روی قالب بندی مشروط > قانون جدید > قالب بندی فقط سلول هایی که حاوی هستند، یک قانون قالب بندی مشروط جدید ایجاد کنید . قالب بندی سلول هایی با مقادیر کمتر از میانگین را انتخاب کنید که با فرمول زیر برگردانده می شود. بدون تنظیم هیچ فرمتی روی OK کلیک کنید .
=AVERAGE($B$2:$B$13)
روی Conditional Formatting > Manage Rules… کلیک کنید ، قانون کمتر از میانگین را به سمت بالا حرکت دهید ، و یک تیک در گزینه Stop if True در کنار آن قرار دهید.
در نتیجه، نمادها فقط برای مقادیری نشان داده می شوند که در محدوده اعمال شده بیشتر از میانگین هستند:
مجموعه های داخلی اکسل دارای مجموعه محدودی از آیکون ها هستند و متأسفانه هیچ راهی برای افزودن آیکون های سفارشی به مجموعه وجود ندارد. خوشبختانه، راهحلی وجود دارد که به شما امکان میدهد قالببندی شرطی را با نمادهای سفارشی تقلید کنید.
روش 1. با استفاده از منوی نماد، آیکون های سفارشی را اضافه کنید
برای شبیه سازی قالب بندی شرطی اکسل با مجموعه آیکون های سفارشی، مراحل زیر را باید دنبال کنید:
همانطور که در تصویر زیر نشان داده شده است، یک جدول مرجع ایجاد کنید که شرایط شما را مشخص کند.
در جدول مرجع، آیکون های مورد نظر را وارد کنید. برای این کار، روی دکمه Insert > Symbols group > Symbol کلیک کنید . در کادر محاورهای Symbol ، فونت Windings را انتخاب کنید ، نماد مورد نظر خود را انتخاب کنید و روی Insert کلیک کنید .
در کنار هر نماد، کد کاراکتر آن را تایپ کنید، که در نزدیکی پایین کادر محاوره ای نماد نمایش داده می شود .
برای ستونی که نمادها باید ظاهر شوند، فونت Wingdings را تنظیم کنید و سپس فرمول IF تودرتو را مانند این وارد کنید:
=IF(B2>=90, CHAR(76), IF(B2>=30, CHAR(75), CHAR(74)))
با مراجع سلولی، این شکل را به خود می گیرد:
=IF(B2>=$H$2, CHAR($F$2), IF(B2>=$H$3, CHAR($F$3), CHAR($F$4)))
فرمول را در پایین ستون کپی کنید، این نتیجه را دریافت خواهید کرد:
نمادهای سیاه و سفید نسبتاً کسل کننده به نظر می رسند، اما می توانید با رنگ آمیزی سلول ها جلوه بهتری به آنها بدهید. برای این کار، میتوانید قانون داخلی ( فرمبندی شرطی > قوانین سلولهای برجسته > برابر با ) را بر اساس فرمول CHAR اعمال کنید، مانند:
=CHAR(76)
اکنون، قالب بندی نمادهای سفارشی ما زیباتر به نظر می رسد، درست است؟
افزودن آیکون های سفارشی با کمک صفحه کلید مجازی حتی ساده تر است. مراحل عبارتند از:
با باز کردن صفحه کلید مجازی در نوار وظیفه شروع کنید. اگر نماد صفحه کلید وجود ندارد، روی نوار کلیک راست کرده و سپس روی Show Touch Keyboard Button کلیک کنید .
در جدول خلاصه خود، سلولی را که می خواهید نماد را در آن درج کنید، انتخاب کنید و سپس روی نمادی که دوست دارید کلیک کنید.
همچنین، میتوانید صفحهکلید ایموجی را با فشار دادن باز کنیدبرد + .میانبر (کلید لوگوی ویندوز و کلید نقطه با هم) و آیکون های آنجا را انتخاب کنید.
در ستون Custom Icon ، این فرمول را وارد کنید:
=IF(B2>=$G$2, $E$2, IF(B2>=$G$3, $E$3, $E$4))
در این مورد، شما نه به کدهای کاراکتر نیاز دارید و نه در مورد نوع قلم.
هنگامی که به دسکتاپ اکسل اضافه می شود، نمادها سیاه و سفید هستند:
در اکسل آنلاین، آیکون های رنگی بسیار زیباتر به نظر می رسند:
این نحوه استفاده از مجموعه آیکون ها در اکسل است. با نگاهی دقیق تر، آنها قادر به کارهای بسیار بیشتر از چند فرمت از پیش تعیین شده هستند، درست است؟ اگر کنجکاو هستید که سایر انواع قالب بندی شرطی را بیاموزید، آموزش های لینک شده در زیر ممکن است مفید باشند.
تبلیغات افزایش بازدید و افزایش فروش در ارم بلاگ
مشاهده
وبلاگ سرگرمی و دانستنی ها
مطلب مرتبط
آموزش Communication در هوانوردی
