دوازده نکته که حرفه ای ها در اکسل می دانند
نکته شماره 8: بهبود ارائه مطالب با روش قالب‌بندی سفارشی اعداد
قالب‌بندی سفارشی، برای تغییر داده‌ها در ارائه‌های شما بدون “دست زدن” به داده‌های منبع، ایده‌ال است. استفاده از قالب‌بندی‌های سفارشی (در Format Cells) به جلوگیری از مراحل واسطه‌ای که برای تغییر ظاهری داده‌ها ضروری است، کمک می‌کند.
به عنوان مثال، ممکن است؛ لازم باشد داده‌ها را با واحد “میلیون” ارائه دهید. یکی از راه حل‌ها، اضافه‌کردن یک مرحله محاسبه اضافی (یا سلول) با تقسیم مقادیر داده‌ها به 1،000،000 است. سپس، علاوه بر اضافه‌کردن واحد پولی مناسب به آن عدد، در انتهای مقدار “M” را اضافه می‌کنید یا به آن بچسبانید.
یک جایگزین بهتر این است؛ که سلول‌ها را برای نشان‌دادن میلیون قالب‌بندی کنیم. این کار به جلوگیری از کارهای اضافی کمک می‌کند؛ و گزارش شما را اندک و فرم شما را به عنوان مقادیر نگه می‌دارد.
توجه: اضافه‌کردن متن به قالب‌بندی اعداد، مقدار زیربنایی سلول را تغییر نمی‌دهد (یعنی محتوا رو تغییر نمیده!). اکسل هنوز داده را به عنوان عدد و نه متن تشخیص می‌دهد.
تغییر قالب‌بندی با استفاده از روش اول مقادیر سلول را به فرمت متن تبدیل می‌کند (روشی که همینطوری M را کنار سلول بچسبانیم)؛ زیرا متن اضافه شده به یک مقدار (مانند “300M”) مقدار را به یک متن تبدیل می‌کند. اکسل عملیات ریاضی را روی رشته‌های متن انجام نمی‌دهد.
قالب‌بندی داده‌ها در ستون B بر حسب میلیون روش کارآمدتر و کاربردی‌تر است. بدون نگاه کردن به مقدار موجود در سلول، می‌توان مقدار سلول را 300 میلی‌متر تصور کرد. به یاد داشته باشید: قالب‌بندی یک ظاهر است؛ و پشت صحنه سلول را در نوار فرمول می‌توان ملاحظه کرد. قالب‌بندی سفارشی (استفاده از ابزارهای Format Cells) ظاهر نمایان سلول را تغییر می‌دهد و نه مقدار واقعی سلول را.
برای یادگیری ادامه مطالب کاربردی با ما همراه باشید…
قالب‌بندی سفارشی، نیز به ویژه هنگامی که داده‌ها را روی نمودار می‌بریم؛ بسیار مفید است. به جای این نمودار،
شما می‌توانید این نمودار را نمایش دهید:
تصویر دوم، دقیق‌تر و منظم‌تر به نظر می‌رسد.
انواع فرمت‌های اصلی اکسل عبارتند از:
General: انتخاب General اجازه می‌دهد؛ اکسل حدس بزند قالب‌بندی مناسب برای محتوای شما چیست. استفاده از General معمولاً اشکالی ندارد؛ زیرا Excel ورودی‌های تاریخ و زمان را به عنوان Date & Time درک می‌کند؛ و همچنین متن‌ها و مقادیر وارد شده را به عنوان متن یا مقدار می‌شناسد.
Specific : (عدد، پول رایج، حسابداری، تاریخ، زمان، درصد، کسر و غیره): این‌ها عموماً بدیهی و بی‌نیاز به توضیح هستند.
Special: به طور کلی برای ورود شماره تلفن و کدپستی استفاده می‌شود.
Custom: گزینه Custom به شما این امکان را می‌دهد؛ تا قالب‌های دلخواه خود را بسازید. می‌توانید متن را به قالب‌بندی شماره یا عدد اضافه کنید. انتخاب کنید که چگونه می‌خواهید؛ اعداد مثبت و منفی را نمایش دهید؛ و چگونه می‌خواهید مقادیر صفر و متن‌های وارد شده قالب‌بندی شوند. استفاده صحیح از قالب‌بندی سفارشی شما را ملزم می‌کند؛ که مطابق شکل زیر، نحو (چیدمان) مناسب را دنبال کنید.
1-8- نحوه قالب‌بندی سفارشی
نحوه قالب‌بندی “عدد مشتری” از 4 بخش زیر تشکیل شده است: 1- عدد مثبت 2- عدد منفی 3- صفر 4- متن
هر بخش با نماد “;” از هم جدا می‌شود. اکسل به هر 4 قسمت احتیاج ندارد.
اگر قالب‌بندی شما، تنها شامل دو قسمت باشد؛ اکسل فرض می‌کند؛ که قسمت اول قالب‌بندی مقادیر مثبت و صفر می‌باشد، در حالی که بخش دوم برای قالب بندی مقادیر منفی استفاده خواهد شد. اگر شما فقط یک بخش قالب را مشخص کنید، یعنیsemi colon (نقطه ویرگول) در ترکیب کاملاً نادیده انگاشته شود – سپس اکسل مثبت و منفی را نادیده می‌گیرد؛ اما قالب‌بندی یکسان را برای هر چهار قسمت اعمال می‌کند.
مقادیر متن به صورت تایپی نمایش داده می‌شوند؛ بنابراین قالب‌بندی خاصی روی آنها اعمال نمی‌شود. شما می‌توانید قالب‌ها را از ابتدا سفارشی نمایید؛ و به راحتی بسازید؛ این یک کار تجاری خوب است؛ که از یک قالب سفارشی موجود استفاده کنید؛ و آن را برای رسیدن به نتیجه مورد نظر خود تنظیم کنید.
مراحل زیر را دنبال نمایید تا قالب‌بندی سفارسی را ایجاد نمایید:
1- Cell/Column/Row را برای قالب‌بندی انتخاب نمایید.
2- کلیک راست کرده و Format Cells را انتخاب کرده یا از کلید میانبر “CTRL” + 1 استفاده نمایید.
3- در قسمت category بر روی Custom کلیک کرده و در زیر “Type”، فرمت دلخواه را بنویسید( تا شکل موردنظر شما نمایان شود).
4- Ok را فشار داده و تأیید کنید که قالب صحیح است.
در اینجا نمونه‌ای از یک قالب سفارشی، وجود دارد.
و دیگری که مقدار صفر و رفتار متن را نیز کنترل می‌کند.
ما این موارد را تفکیک (تجزیه) می‌کنیم؛ و کمی توضیح خواهیم داد. قبل از آن، اجازه دهید نگاهی به قوانین بیندازیم:
کاراکتر مورد نظر | توصیف |
$ or € | کاراکتر ویژه‌ای |
+ , – , = , < , > , /, ( , | بسته به مکانی که تایپ کرده‌اید؛ در سلول ظاهر می‌شوند. نیازی به علامت نقل قول (quotation ) نیست. |
“ ” | متنی را که بین علامت‌های نقل قول تایپ شده است نشان می‌دهد |
_( , _) | یک کاراکتر فضای خالی را نمایش می‌دهد |
0 | محل نگهدارنده مقادیر ( نه جایگزین اعشار) |
# | برای مثال: اگر #.## را به عنوان قالب دارید؛1.2 نمایش داده می‌شود و 1.20 نمایش داده نمی‌شود |
. | نقطه برای نمایش رقم اعشار |
, | ویرگول، جداکننده هزار را نشان می‌دهد |
#,##0 | سه رقم سه رقم، از سمت راست اعداد جدا می‌شوند |
#,##0.00 | بعد از نقطه دوصفر ملاحظه می‌گردد، یعنی علاوه بر جدا کردن سه رقم سه رقم، تا دو رقم اعشار نیز نمایش می‌دهد. |
0, and 0,, | اگر ویرگول از محل نگهدارنده پیروی کند؛ سپس اعداد بر حسب هزار نمایش داده می‌شود؛ و اگر دو ویرگول بعد از صفر بیاید؛ آنها بر حسب میلیون نمایش داده می‌شوند. |
?
| این علامت نیز از همان قاعده 0 پیروی می‌کند. یک تفاوت این است؛ که درون سلول، صفر وارد نماییم؛ درون سلول را خالی نشان می‌دهد. همچنین مکان‌های رقم اعشار را در جایگاهشان تراز می‌نماید. |
* | علامت ستاره، به اکسل می‌گوید؛ تا زمان پر شدن سلول، کاراکتر بعدی را تکرار کند، به عنوان مثال: اگر می‌خواهید؛ علامت دلار در سمت چپ سلول باشد؛ و سپس فضای خالی و تعداد آن را در سمت راست داخل سلول داشته باشید. شما باید با یک فضای خالی از * پیروی کنید. به عنوان مثال: $ * #، ## . |
[Black], [Green], [Red], | برای تغییر رنگ مقادیر، کد رنگ را به عنوان اولین مورد از هر بخش تایپ کنید. اگر از رنگ راضی نیستید؛ یا رنگ دیگری را دوست دارید، می‌توانید برای رنگ‌های بیشتر از پالت، کد رنگی را انتخاب کنید. به عنوان مثال: نحوه استفاده از فهرست رنگ [Color10] است؛ که سبز تیره‌تر از رنگ [Green] است. |
@ | به عنوان نماینده متن درون سلول قرار می‌گیرد. |
در زیر چند مثال وجود دارد که نحوه استفاده از این نمادها را نشان می دهد:
مثال 1: نمایش داده‌ها به میلیون
قالب زیر برای نمایش داده‌ها بر حسب میلیون استفاده می‌شود:
” 0,,”، به اکسل می گوید “این اعداد را بر حسب میلیون نمایش دهد.”0,” نماینده نشان دادن اعداد بر حسب واحد هزار است. “M” دوم، متن را به عدد فرمت شده اضافه می‌کند؛ اما مقدار سلول اصلی همان است (مقدار یا محتوای سلول را می‌توان در نوار فرمول ملاحظه کرد). اولین بخش بعد از اولین “;” با قالب اعداد مثبت یکسان است (یعنی بخش اول)؛ اما مقادیر منفی با استفاده از علامت منفی نمایش می‌یابد. “-” بعد از “;” دوم صفرها را با “-” جایگزین می‌کند (یعنی اگر درون سلول صفر تایپ کنیم، خط تیره نمایش می‌یابد). قالب‌بندی متن در این مثال نادیده گرفته شده است – فقط مقادیر مثبت، منفی و صفر تعریف شده‌اند.
مثال 2:
“#,##0-);” اعداد موجود را به صورت سه‌رقم سه‌رقم جدا کرده و یک space بین عدد و مرز سلول می‌اندازد.
“[Red] (###0)” از رنگ قرمز برای اعداد منفی استفاده می‌کند؛ و پرانتز را در اطراف عدد منفی قرار می‌دهد. در حالی که در قالب‌بندی متن، مجددا نادیده گرفته می‌شود؛ صفر نمایش داده می‌شود. (حتما فرمت‌های مورد نظر را در اکسل با مثال انجام دهید؛ تا نکات به صورت مفهومی برای شما جا بیفتد)
مثال 3:
“_($*#,##0_) ;” شامل یک فاصله قبل و بعد از اعداد است، بنابراین مقادیر به حاشیه متصل نیستند. اکسل، نماد علامت دلار را تشخیص می‌دهد. نیازی به قرار دادن آن در براکت‌ها نیست. بخش “*” فاصله‌ای را به نسبت به طول سلول اضافه می‌کند. به عنوان مثال، اگر سلول کوچک باشد، نتیجه آن می‌شود: $ 370.00 و اگر سلول عریض‌تر باشد نتیجه: $ 370.00 خواهد شد. “($*(#,##0_));” نیز مشابه است؛ اما با براکت برای اعداد منفی است. برای صفرها “($* “-”_));”یک خط تیره “-“ به جای صفر نشان می‌دهد. برای متن، بخش “_(@_)” یک space پیش و پس از متن اضافه می‌نماید – دوباره برای اطمینان از اینکه “متن” به حاشیه سلول متصل نگردد.
مثال 4:
“?” رقم‌های اعداد اعشاری را به صورت درست تعیین می‌نماید. به علامت سوال‌های بعد از نقطه دقت نمایید؛ به تعداد این علامت سوال‌ها، رقم اعشار در عدد شما نمایش می‌دهد. همچنین به تعداد علامت سوال‌های قبل از نقطه از دیواره سلول فاصله می‌گیرد. کافی است؛ این روش‌ها را در اکسل امتحان نمایید، تا بر لذت کار با این نرم‌افزار قدرتمند بیفزایید. در اینجا بعضی از مثال‌های قالب‌بندی و نتایج آنها بیان شده‌اند.
سوال‌های قبل از نقطه از دیواره سلول فاصله می‌گیرد. کافی است؛ این روش‌ها را در اکسل امتحان نمایید، تا بر لذت کار با این نرم‌افزار قدرتمند بیفزایید. در اینجا بعضی از مثال‌های قالب‌بندی و نتایج آنها بیان شده‌اند.
2-8- قالب‌بندی شرطی با استفاده از گزینه قالب‌بندی سفارشی
رنگ‌ها، با استفاده از “[ ]” به سلول تخصیص داده می‌شود؛ اما آنها می‌توانند؛ برای انجام عملیات قالب‌بندی شرطی نیز استفاده شوند. آن محدودیت‌های بیشتری نسبت به ابزار قالب‌بندی شرطی اکسل (Home < Conditional Formatting) دارد؛ اما می‌توان از آنها برای ایجاد یک سناریوی موردی “بالا” ، “کم” و “دیگر” استفاده کرد. به طور مثال:
پیش از قالب‌بندی:
پس از قالب‌بندی:
اولین شرط، “[>100]” ، مرز (حد) بالا را تنظیم می‌نماید – اگر مقادیر بیشتر از 100 باشد؛ سپس “A Group” با یک فاصله همراه مقادیر قرار می‌گیرد. دومین شرط، C Group “[<50]”، مرز (حد) پایین را تنظیم می‌نماید. در نهایت، “B Group” ، برای هرچیزی بین این مرزها (حدود) است.
این ترکیب (خصوصیات) می‌تواند برای ترکیب‌ها (خصوصیات) قالب‌بندی ساده مفید باشد؛ اما برای انعطاف‌پذیری و کارآمدی بیشتر از قالب‌بندی شرطی (Home<Conditional Formatting ) استفاده کنید.
3-8- آزمایش قالب‌های اعداد (Number Format)
گاهی اوقات زمان زیادی را صرف تلاش؛ برای بدست آوردن قالب سفارشیِ “مورد نظر” می‌نمایید. شما می‌بینید؛ که بین شیت اکسل و پنچره FormatCells قالب‌بندی سفارشی در حال رفت و آمد هستین؛ تا بتوانید قالب مورد نظر خود را پیاده کنید. راه حل جایگزین برای آزمایش، قالب دلخواه با تابع () TEXT است. شما ابتدا با تابع TEXT (value, format text) قالب فرمول قابل اجرا در شیت اکسل خود ایجاد می‌کنید؛ (شکل زیر را ببینید) و سپس فرمول را در قسمت“Type” قالب‌بندی سفارشی کپی کنید.
توجه – رنگ قلم (به عنوان مثال ” [قرمز “] و” [سبز]”) با استفاده از تابع (TEXT (value, format text قابل آزمایش نیستند – این نحوه فقط در قالب‌بندی سفارشی کار می‌کند. فونت‌ها تنها زمانی رنگی می‌شوند؛ که فرمول در جعبه”Type” قالب‌بندی کپی شده و قرار داده شده باشد.
و نتیجه، این می‌شود:
