تولید اعداد تصادفی در اکسل

در اکسل جهت ایجاد اعداد تصادفغی می توان ازتابع Randاستفاده نمود 

با آموزش اکسل امروز همراه باشید تا باهم با چگونگی تولید اعداد تصادفی  آشنا شویم  

برای تولید عددی تصادفی بین دو عدد a و b از تابع Randbetween به صورت زیر استفاده می شود:

(RANDBETWEEN(a;b=

البته می توانیداز تابع Rand  در excel به تنهایی استفاده کنید.

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

پس از ایجاد لیست اعداد تصادفی اگر عددی را تغییر دهید همه اعدادبه طور تصادفی تغییر خواهند کرد

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

برای مثال دولیست راکه  می خواهیم با هم مقایسه نماییم.

  برای این منظور به روش زیر عمل می کنیم.

ابتدا ناحیه A1:A6 را انتخاب نموده و نام FirstList را برای آن انتخاب می کنیم. (برای نام گذاری در کادر اسم نام مورد نظر را بدون فاصله تایپ کرده و کلید Enter را فشار می دهیم)

به همین ترتیب ناحیه B1:B6 را انتخاب نموده و برای آن نام SecondList را انتخاب می کنیم.

ناحیه A1:A6 را انتخاب می کنیم. (می توانیم از لیست کادر نام عبارت FirstList را انتخاب نماییم)

از سربرگ Home ابزار Conditional formatting را انتخاب نمایید و سپس بر روی گزینه New rule کلیک کنید.

از پنجره ظاهر شده عبارت “Use a formula to determine which cells to format” را انتخاب نمایید.

فرمول را به صورت زیر وارد نمایید.

=COUNTIF(SecondList,A1)=0

 بر روی دکمه Format کلید کنید و از پنجره ظاهر شده یک رنگ دلخواه انتخاب نمایید.

بر روی دکمه ok کلیک کنید. 

 چک باکس در اکسل stop if true را در حالت انتخاب قرار داده و بر روی دکمه ok کلیک کنید.

 همانطور که ملاحظه می کنید Delhi Daredevils  و  Royal Challengers Bangalore منحصر به فرد هستند (در ناحیه SecondList قرار ندارند.)

برای مشخص کردن آیتم هایی که در SecondList باشند و در FirstList نباشند از فرمول زیر استفاده می کنیم.

=COUNTIF(FirstList,B1)=0

 در اینجا فرمول (COUNTIF(SecondList,A1 تعدادآیتم های ناحیه SecondList  که برابر آیتم  سلول A1 هستند را شمارش می کند (به عبارت دیگر آیا آیتم واقع درسلول A1 در SecondList هست یا خیر).

پس اگر =0(COUNTIF(SecondList,A1آیتم واقع در سلول A1 در ناحیه SecondList موجود نیست. و در پایان Conditional formatting آیتم های منحصر به فرد را به رنگ مشخص شده نمایش می دهد.

 راه دوم برای مقایسه لیست ها

یک کپی از صفحه موجود کپی نموده و سپس تمام قوائد conditional formatting  را از آن حذف کنید (انتخاب هر دو ستون –  انتخاب conditional formatting از سربرگ Home- انتخاب clear rules و سپس انتخاب clear rules from selected cells)

ابتدا ناحیه داده ها را انتخاب نمایید.

از سربرگ home ابزار conditional formatting و سپس گزینه Duplicate values را انتخاب نمایید.

از پنجره ظاهر شده زیر عبارت Format cells that contain گزینه Unique را انتخاب نمایید و سپس بر روی دکمه ok کلیک نمایید.

نتیجه مشابه مراحل قبل می باشد.

تابع AVERAGE در اکسل
همان طور که از نام این تابع پیداست، تابع AVERAGE برای به دست آوردن مقدار میانگین چند عدد مورد استفاده قرار می‌گیرد. سینتکس این تابع به صورت زیر است:
AVERAGE (number1, [number2] …)
آرگومان‌های این تابع می‌توانند آدرس سلول ها و یا فرمول باشند که در نهایت حتماً باید عدد باشند، آرگومان ها می‌توانند حتی مقادیر منطقی هم باشند. تابع میانگین حداقل باید شامل یک عدد باشد و حداکثر تعداد اعداد (یا حداکثر تعداد آرگومان ها) می تواند 255 باشد.
اگر آرگومانی از این تابع آدرس سلولی حاوی داده ی متنی، مقدار منطقی (Logical Value) و یا سلول خالی باشد، از آن آرگومان صرف‌نظر می‌شود و آن سلول شمارش نمی‌شود ولی سلول های دارای مقدار صفر در محاسبات گنجانده می‌شود، لذا به تفاوت بین سلول های خالی و سلول های حاوی صفر دقت کنید.

تابع AVERAGEA در excel
این تابع کارکردی مشابه تابع AVERAGE دارد، سینتکس تابع AVERAGEA به صورت زیر است:
AVERAGEA (value1, [value2] …)
تعداد آرگومان ها می‌تواند تا 255 باشد، آرگومان‌ها می‌توانند اعداد، نام‌ها، آرایه‌ها و یا آدرس سلول های حاوی متن‌های قابل جایگزینی با اعداد و یا مقادیر منطقی (Logical Value) باشند. سلول‌های حاوی مقادیر منطقی در تابع AVERAGE در محاسبات منظور نمی‌شدند.
سلول‌ها و یا آرایه‌های حاوی داده های متنی برابر با صفر قرار داده می‌شوند و همین طور داده‌های متنی خالی “” نیز به عنوان صفر در نظر گرفته می‌شوند، در صورتی که در تابع AVERAGE این سلول ها از محاسبات خارج می‌شدند. در کل تفاوت دو تابع AVERAGE و AVERAGEA تنها در همین موضوع است.

-تابع AVERAGEIF در اکسل
این تابع برای گرفتن میانگین یک سری از داده‌ها که دارای شرط خاص می‌باشند مناسب است، فرض کنید یک سری عدد دارید و می‌خواهید میانگین اعداد بزرگ‌تر از ده را به دست بیاورید و یا می‌خواهید میانگین اعداد زوج یک محدوده را محاسبه نمایید. ساختار تابع AVERAGEIF به صورت زیر است:
AVERAGEIF (range, criteria, [average_range])
آرگومان اول محدوده‌ای است که می‌خواهیم میانگین اعداد را در آن به دست آوریم و آرگومان دوم شرط مورد نظر برای حضور در میانگین گیری می‌باشد، مشخص است که حاصل آرگومان دوم برای هر المان از محدوده باید True یا False باشد. در صورت True  بودن شرط، آن سلول در محاسبه میانگین گنجانده می شود.
آرگومان سوم اختیاری است، در صورتی که این آرگومان وجود داشته باشد برای گرفتن میانگین مورد استفاده قرار می‌گیرد و در صورت خالی بودن این آرگومان، آرگومان اول برای میانگین گیری استفاده می شود.
سلول های حاوی True و False از محاسبات کنار گذاشته می‌شوند.
اگر در آرگومان Average_Range آرگومان سوم سلولی خالی باشد، سلول معادل آن در آرگومان Range آرگومان اول از محاسبات خارج می‌شود.
اگر در آرگومان اول یا سوم سلولی خالی باشد یا داده متنی داشته باشد، آن سلول برابر با صفر در نظر گرفته می‌شود.
اگر تمام سلول های Range خالی یا داده متنی باشد، خروجی تابع خطای!0/Div # خواهد بود.
اگر هیچ یک از سلول های Range دارای شرط Criteria نباشد، خروجی تابع خطای!0/Div # خواهد بود.
در آرگومان دوم از کاراکتر های؟ (Question Mark) به عنوان یک کاراکتر و از * (Asterisk) به عنوان چند کاراکتر بهم پیوسته می‌توان استفاده کرد.
لزومی ندارد که Average_Range دقیقاً هم اندازه ی Range تعریف شود، در واقع بدون توجه به تعریف کاربر از    Average_Range محدوده ای شامل اولین سلول محدوده ی Average_Range انتخاب شده توسط کاربر و به اندازه Range به عنوان Average_Range توسط اکسل در نظر گرفته می‌شود.

-تابع AVERAGEIFS در اکسل
تابع AVERAGEIFS کاربردی مشابه AVERAGEIF دارد با این تفاوت که در این تابع به کاربر این امکان داده می‌شود که چند شرط را در انتخاب داده ها به منظور میانگین گیری اعمال کند، فرض کنید شما میخواهید در میان اعداد موجود، میانگین اعداد بزرگتر از ده و کوچکتر از بیست را به دست بیاورید، برای انجام این کار بهترین انتخاب استفاده از تابع AVERAGEIFS می باشد. سینتکس این تابع به صورت زیر است:
AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
در ساختار بالا، آرگومان اول محدوده ای است که می‌خواهیم میانگین اعداد آن را به دست آوریم، آرگومان دوم محدوده ای است که شرط اول بر روی آن اعمال می‌گردد و آرگومان سوم، شرط اول می‌باشد. آرگومان چهارم به بعد به ترتیب محدوده های شرط و شرط های مورد نظر می‌باشند. ساختار این تابع همانند تابع SUMIFS در اکسل در توابع ریاضی می‌باشد

امید واریم از این آموزش اکسل نیز لذت برده باشید

Excel قادر است اعتبار  (درستی) داده ها را در هنگام ورود آنها، کنترل کند. این در صورتی است که پیش از ورود داده ها، برای سلول های مربوطه، شرایط اعتبار را تعریف کرده باشید.

برای کنترل اعتبار داده های ورودی مراحل زیر را طی کنید:

1-    سلولها موردنظر را انتخاب کنید.
2-    در تب Data روی دکمه Data Validation کلیک کنید.
3-    از منوی باز شده گزینه Data Validation… را انتخاب کنید.

4-    کادر Data Validation باز می شود با استفاده از این کادر می توانید شرایط مجاز ورود داده ها، پیغام های ورودی و پیغام های خطا را تعریف کنید.

تعریف شرایط مجاز
برای تعریف شرایط مجاز، از تب Settings استفاده می شود. گزینه Allow در این قسمت، لیستی از شرایط مختلف ارائه می دهد

پس از انتخاب گزینه مناسب از لیست Allow دامنه مجاز را با استفاده از عملگرهای مقایسه ای لیست Data تعیین کنید

شناسایی اصول کار با Print Area  و کنترل Page Break
تعیین ناحیه چاپ

از آنجا که معمولاً اطلاعات زیادی در Sheet وارد می شود و در بسیاری از موارد مایل به چاپ کل این اطلاعات نیستیم، می توانیم بخش موردنظر را بعنوان ناحیه چاپ به Excel معرفی کنیم. به این ترتیب کلیه تنظیمات چاپ روی این قسمت انجام می شود. برای تعیین ناحیه چاپ، مراحل زیر را طی کنید:
1-    ناحیه موردنظر را انتخاب کنید.
2-    در تب Page Layout گروه Page Setup روی دکمه Print Area کلیک کنید. منویی با دو گزینه باز می شود.

3-    گزینه Set Print Area را انتخاب کنید.
حذف ناحیه چاپ
برای حذف ناحیه چاپ تعیین شده (لغو عملیات فوق)، از منوی Print Area گزینه Clear Print Area را انتخاب کنید.
ایجاد کنترل  Page Breakدر اکسل
همانطور که میدانید هر  Sheet قادر به نگهداری حجم وسیعی از اطلاعات است که ممکن است در قالب چندین برگه کاغذ به چاپ رسد Excel بطور خودکار اطلاعات هر Sheet را بر طبق ابعاد کاغذ تعیین شده صفحه بندی می کند. یعنی بخشی از اطلاعات که در کاغذ اول جا نگیرد، در کاغذ دوم چاپ خواهد شد. مرز بین این صفحات توسط Page Break مشخص می شود.

1-  سلولی را که بعنوان اولین سلول صفحه دوم درنظر داریم انتخاب می کنیم. در این مثال باید سلول حاوی روزهای کارکرد انتخاب شود.
2-  در تب Page Layout گروه Page Setup روی دکمه Breaks کلیک کرده و از منوی باز شده، گزینه Insert Page Break را انتخاب می کنیم به این ترتیب در این محل یک Page Break جدید ایجاد می شود.

حذف Page Break
برای حذف Page Break (های) ایجاد شده، ابتدا سلول اول صفحه دوم را انتخاب می کنیم تا مشخص شود که قصد حذف کدام Page Break را داریم. سپس از منوی Breaks گزینه Remove Page Break را انتخاب می کنیم و  گزینه Reset All Page Breaks کلیه Page Break های موجود را حذف می کند.
شناسایی اصول تنظیم حاشیه و جهت صفحات در اکسل
منظور از حاشیه صفحه، فضای خالی اطراف صفحه است که اطلاعاتی بر روی آن چاپ نمی شود. اندازه این فضا توسط کاربر قابل تعیین است. برای تعیین حاشیه صفحات یک Sheet در تب Page Layout گروه Page Setup روی دکمه Margins کلیک کنید این منو سه حاشیه از پیش تعیین شده را نشان می دهد

یکی دیگر از تنظیمات مهم صفحه، تعیین جهت صفحه است. صفحات یک Sheet هم به صورت عمودی و هم به صورت افقی قابل چاپ می باشند. برای تعیین جهت صفحه، در تب Page Layout  گروه Page Setup روی دکمه Orientation کلیک کنید گزینه Portrait کاغذ را عمودی و گزینه Landscape آن را افقی می کند.

شناسایی اصول تغییر اندازه کاغذ Print Scale در excel
یکی از عوامل تاثیرگذار در صفحه بندی Sheet اندازه کاغذ است Excel بطور پیشفرض اندازه کاغذ را با کاغذ Letter منطبق می کند. برای تغییر این پیش فرض از دکمه Size در تب Page Layout گروه Page Setup استفاده می کنیم.

مقیاس چاپ نیز قابل تغییر است. پیشفرض این مقدار 100 درصد بوده و با تغییر آن به راحتی می توانید اطلاعات را بزرگتر یا کوچکتر از اندازه واقعی چاپ کنید. برای این منظور از کادر Scale  

در تب Layout Page استفاده کنید.

شناسایی اصول استفاده از پیش نمایش چاپ
مشاهده پیشنمایش چاپ در اکسل

پیش از چاپ Sheet بهتر است پیش نمایشی از آن را مشاهده کرده و تنظیمات انجام شده را کنترل کنید. برای این منظور از قابلیت Print Preview استفاده می کنیم. روی دکمه Office کلیک کرده و از منوی باز شده، گزینه Print و از زیرمجموعه های آن، گزینه و از زیرمجموعه های آن، گزینه را انتخاب کنید.

پس از انتخاب این گزینه Excel وارد محیط پیش نمایش چاپ شده و صفحه اول چاپی را نشان می دهد در این محیط، تنها تب Print Preivew وجود دارد که امکاناتی جهت دسترسی به Page Setup تنظیم حاشیه، مشاهده سایر صفحات و... ارائه می دهد برای خارج شدن از محیط Print Preview در تب Print Preview روی دکمه Close Print Preview کلیک کنید.

چاپ  Sheet
برای چاپ فایل از گزینه Print استفاده می کنیم. روی دکمه Office کلیک کرده و از زیرمجموعه های Print گزینه Print را انتخاب کنید.

زمانی که در  اکسل فرمول هایی را درست می کنید.  می توانید سلول هایی که در قسمت های دیگر Worksheet هستند را به فرمول هایتان ارجاع دهید. اما اگر تعداد زیادی فرمول داشته باشید بعضی اوقات این ارجاع دادن ها می تواند شما را سردرگم کند.در این آموزش اکسل قصد داریم  یک روش ساده برای رهایی از این سر درگمی بیان کنیم.
برنامه اکسل شامل یک ویژگی به عنوان اسم ها یا Names است که می تواند فرمول های شما را راحتر و قابل درک تر کند و شما را از سردرگمی نجات دهد. بجای ارجاع دادن یک سلول یا محدوده ای از سلول ها شما می توانید یک اسم را به سلول یا محدوده ای از سلول ها اختصاص داده و در فرمول هایتان از اسم آن سلول ها استفاده کنید.این باعث می شود فرمول های شماراحتر و قابل درک تر باشد.
در زیر ما یک دسته از سلول ها را (قسمت پر رنگ تر) از یک Worksheet دیگر به فرمول ارجا ع داده ایم.اسم Worksheet  ما “Product Database” نام دارد.نام گذاری Worksheet ایده خوبی است و در این جا  باعث شده ما متوجه شویم محدوده سلول های “A2 7” شامل چه چیز هایی هستند.

اما ما می توانیم از اسمی کوتاه تر و ساده تر برای محدوده سلول های انتخابی در این فرمول استفاده کنیم و فرمول را کوتاه تر کنیم.

?

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

 
بر روی کادر نام (Name Box ) که در بالای شبکه سلول ها قرار دارد کلیک کنید.

اسم مورد نظر خود را که می خواهید به سلول های انتخابی شما اختصاص داده شود را تایپ وسپس کلید Enter را بزنید. برای مثال ما سلول های انتخابی را “Products”  از برگه کاری “Product Database” نام گذاری کرده ایم.(برگه کاری = Worksheet)
برای نامگذاری یکسری قواعدی هست که باید آن ها را در نظر بگیرید. شما برای نام گذاری  فقط می توانید حرف اول اسم را با حروف یا underscore (_) و یا یک backslash (\) آغاز کنید. بقیه حروف اسم می تواند شامل اعداد. حروف. نشانه ها  و.... باشد. از کلید Spaces نمی توان استفاده کرد.

 
فرمولی که در اوایل آموزش به آن اشاره کردیم را به یاد دارید که در آن محدوده ای از سلول ها Worksheet ما یعنی “Products Database” موجود در  workbook را به آن ارجاع داده بودیم.
حالا ما اسم “Products”  را که بیانگر محدوده ای از سلول های Worksheet ما یعنی “Products Database” است را ایجاد کردیم.حالا در فرمول از آن اسم استفاده می کنیم. (قسمت پر رنگ تر)

?

نحوه ویرایش اسم ها با استفاده از  Name Manager در اکسل
اکسل برای ما یک ابزار را فراهم کرده با نام Name Manager که توسط آن می توان به راحتی اسم های موجود در workbook را پیدا. ویرایش. و یا پاک کرد. شما همچنین می توانید از این قسمت نام گذاری  هم انجام بدهید. و اگر می خواهید جزئیات بیشتری را در رابطه با اسم مشخص کنید.
برای دستیابی به Name Manager بر روی زبانه “Formulas” کلیک کنید.

 
در قسمت “Defined Names”  در زبانه “Formulas” بر روی “Name Manager” کلیک کنید.

 
کادر محاوره ای Name Manager به نمایش در می آید.برای ویرایش اسم های موجود بر روی اسم مورد نظر کلیک کرده آن را انتخاب کرده سپس بر روی “Edit” کلیک کنید. برای مثال ما اسم “Products” را ویرایش می کنیم.
 

قسمت ویرایش اسم باز می شود. می توانید اسم مورد نظر را تغییر دهید و یا یک مطلبی در رابطه با اسم انتخابی بنویسیدکه جزئیات بیشتری در رابطه با اسم در اختیار ما قرار بدهد.همانند این که این اسم بیانگر چه چیزی است.
همچنین می توانید محدوده ی سلول های انتخاب شده را تغییر دهید. برای این کار بر روی قسمت  “Expand Dialog”  در  سمت راست کادر “Refers to” کلیک کنید.
 
به عنوان مثال یک محصول را به پایگاه اطلاعاتی اضافه می کنیم ومی خواهیم این محصول شامل محدوده های نام گذاری شده باشد. بر روی گزینه “Expand Dialog” کلیک می کنیم پنجره محاوره ای “Edit Name” به حالت مخفی در می آید فقط قسمت “Refers to” باقی می ماند.
ما یک محدوده ای از سلول ها را بطور مستقیم در Worksheet خودمان یعنی “Product Database” انتخاب می کنیم که شامل ردیف محصول جدیدمان نیز هست.
اسم worksheet و محدوده انتخابی سلول ها در قسمت “Refers to” به صورت اتو ماتیک وارد می شود.
برای تایید انتخاب و بازگشت به کادر محاوره ای “Edit Name”  بر روی گزینه “Collapse Dialog” button کلیک و سپس در کادر محاوره ای Edit Name گزینه ok را انتخاب کرده و تغییرات اعمال می شوند.

 
چگونه یک اسم را با استفاده از Name Manager  در اکسل پاک کنیم

اگر تصمیم گرفته اید که دیگر یک اسم را نمی خواهید به راحتی می توانید آن را پاک کنید.بدین منظور به قسمت Name Manager رفته بعد از انتخاب اسم مورد نظر بر روی گزینه Delete کلیک کنید.

در قسمت کادر تایید انجام عمل پاک کردن بر روی گزینه Ok کلیک کرده و اسم را پاک کنید.
 

 درست کردن یک اسم از طریق کادر محاوره ای “New Name” در اکسل
در روش قبلی اسم را از طریق انتخاب کردن یک سلول و یا چند سلول و وارد کردن اسم مورد نظر در کادر “Name Box” می ساختید.
به صورت پیش فرض دامنه آن اسم در کل workbook وجود دارد. حالا چه کار باید بکنیم که این دامنه اسم را به یک worksheet خاص محدود کنیم.
سلول هایی که می خواهید برای آن اسم ایجاد کنید را انتخاب کرده و از زبانه “Formulas” در قسمت “Define Name” بر روی “Define Name” کلیک کنید.
نکته: شما نیازی نیست سلول ها را اول انتخاب کنید. می توانید ان ها را از طریق قسمت “Expand Dialog”  انتخاب کنید.

 
 کادر محاوره ای “New Name” به نمایش در می آید. توجه داشته باشید که بسیار به کادر “Edit Name” شباهت دارد. فرق اصلی این کادر دراین است که در این قسمت شما می توانید دامنه اسم خود را محدود کنید.
مثلا ما می خواهیم اسم مورد نظر را فقط مختص worksheet خود با نام “Invoice” قرار دهیم. ما این کار را زمانی انجام می دهیم که بخواهیم همان اسم را در worksheet دیگری بکار ببریم.
اول اسم را انتخاب کرده که در اینجا “Products” است. برای محدود کردن دامنه اسم به worksheet مورد نظر ما. از کادر “Scope” گزینه “Invoice” را که worksheet مورد نظر هست انتخاب می کنیم.
نکته: همچنین شما می توانید از طریق کلیک بر روی گزینه New در کادر محاوره ای “Name Manager”  به این قسمت دسترسی داشته باشید.

اگر می خواهید در این قسمت جزئیاتی در رابطه با اسم خود بنویسید و یا با انتخاب “Expand Dialog”  محدوده سلول های خود را انتخاب کنید. سپس بر روی OK کلیک کنید.
 
اسمی که به محدود از سلول ها اختصاص داده شده به طور اتوماتیک به قسمت “Name Box” اضافه شده و حالا می توانیم در فرمول اول این آموزش این اسم را که بیانگر محدوده از سلول ها که در worksheet مورد نظر یعنی “Invoice”  هست را ارجاع دهیم.
 

چگونه از یک اسم برای نشان دادن یک مقدار ثابت استفاده کنیم.
 حتما نباید در زمان ایجاد یک اسم سلول ها را ارجاع دهید.شما می توانید از یک اسم که نشان دهنده یک مقدار و یا حتی یک فرمول هست نیز استفاده کنید. برای مثال در worksheet  زیر از نرخ ارز برای محاسبه یکسری قیمت های مختلف به یورو استفاده می شود. بخاطر این که نرخ ارز اغلب تغییر می کند پسندیده است که یک جای مشخصی را برای ان در نظر بگیریم که راحت بتوانیم ان را تغییر دهیم. از انجایکه اسم ها براحتی ویرایش می شوند ودر این مبحث به ان ها پرداخته ایم. ما یک اسم را که بیانگر نرخ ارز است را ایجاد کرده و به ان یک مقداری را اختصاص می دهیم.

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

برای ایجاد یک اسم که بیانگر یک مقدار ثابت باشد. کادر محاوره ای “New Name”  را از زبانه “Formulas” با انتخاب “Define Name” و کلیک کردن بر بروی “Define Name”باز می کنیم. اسم مورد نظر را که بیانگر یک مقدار ثابت هست وارد کرده همانند  “ExchangeRate” (نرخ ارز).
برای اختصاص دادن یک مقدار به این اسم در کادر “Refers to” یک علامت مساوی (=) قرار می دهیم و بعد از ان مقدار عددی را وارد می کنیم. بعد از علامت مساوی از ایجاد فاصله پرهیز کنید و عدد را دقیقا بعد از علامت مساوی وارد کنید. بر روی گزینه ok کلیک کنید.
نکته: اگر از یک فرمول در بخش های مختلفی از workbook استفاده می کنید می توانید با وارد کردن فرمول در کادر “Refers to” و وارد کردن یک اسم براحتی در بخش های مختلف از اسم مورد نظر بجای فرمول استفاده کنید.

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

سلولی را که می خواهید از این فرمول (فرمول جدید با اسم ایجاد شده) استفاده کند را به صورت Highlight در اورده و اسم مورد نظر را تایپ کنید و یا اسم مورد نظر را با تایپ حروف اول آن و انتخاب ان از بین تطابق های نشان داده شده در کادر popup بر گزینید.

اسم مورد نظر وارد فرمول می شود.کلید“Enter” را بفشارید تا تغییرات در سلول ها اعمال شود.

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

اسامی بسیار مفید هستند اگر از workbooks پیچیده با فرمول های زیاد در اکسل استفاده می کنید. زمانی که می خواهید  workbooks را برای دیگران شرح دهید اسامی کار شما را راحتر می کنند.

منجمد کردن ردیف ها و ستون ها در اکسل

ممکن است که بعضی وقتها مایل باشید تا بعضی از ستونها و یا ردیف ها را همیشه در همه قسمتهای برگه اکسل خود بتوانید ببینید، مخصوصا سلولهای هدر (عنوان). با استفاده از ویژگی انجماد سلولها یا ردیفها می توانید سلولهایی را منجمد کنید و هر وقت که در سلولهای دیگر بالا یا پایین می روید این سلولها همچنان در صفحه باقی بمانند. در تصویر زیر ما دو ردیف اول را منجمد کرده ایم، این ویژگی به ما اجازه می دهد تا علیرغم حرکت بین سایر سلولها بتوانیم این دو ردیف را همواره ببینیم. در آموزش دیگری به صورت خاص به این موضوع خواهیم پرداخت.

مرتب سازی اطلاعات در اکسل

با استفاده از ویژگی مرتب سازی اطلاعات به سرعت می توانید داده های یک برگه اکسل را از نو سازماندهی کنید. محتویات شما می توانند بصورت الفبایی، عددی، و خیلی روشهای دیگر مرتب سازی گردند. بعنوان مثال شما می توانید یک لیست از شماره تلفن های اشخاص را به سرعت بر اساس نام خانوادگی مرتب سازی کنید.

فیلتر کردن داده ها در  excel

فیلترها برای محدود کردن اطلاعات استفاده می شوند، و به شما اجازه می دهند تا فقط اطلاعاتی را که نیاز دارید ببینید. در مثال زیر ما با استفاده از ویژگی فیلتر کردن اطلاعات در اکسل، اطلاعات موجود در ستون B را فیلتر کرده ایم تا فقط داده هایی که در آن ها کلمات Laptop یا Projector وجود دارند، نمایش داده شوند.

خلاصه سازی داده ها

ویژگی subtotal در اکسل به شما اجازه می دهد تا به سرعت اطلاعات خود را خلاصه سازی کنید. در مثال زیر ما ویژگی subtotal را درمورد ستون T-shirt size استفاده کرده ایم، که به ما کمک می کند تا به سادگی متوجه شویم در هر سایزی چه تعداد تی شرت نیاز داریم.

قالب بندی اطلاعات به شکل جدولی

کاملا مشابه ویژگی قالب بندی سلولها، جداول می توانند ظاهر و احساس برگه های اکسل شما را بهبود ببخشند، علاوه بر آن ویژگی قالب بندی بصورت جداول به شما اجازه می دهد تا داده های خود را سازماندهی کنید تا استفاده از آنها راحتتر گردد. به عنوان مثال جداول یکسری ویژگیهای داخلی همچون مرتب سازی و فیلتر کردن مختص به همان جدول را دارند. اکسل چندین قالب بندی پیش ساخته شده برای جداول ارائه داده است.

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

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

افزودن قالب بندی های شرطی در EXCEL

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

استفاده از ویژگی یافتن و جایگزینی در اکسل

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

عملگرهای ریاضی در excel

اکسل برای فرمولها از عملگرهای استاندارد مانند علامت بعلاوه (+) برای عملیات جمع، علامت منها (-) برای عملیات تفریق، علامت ستاره (*) برای عملیات ضرب، علامت اسلش رو به جلو (/) برای عملیات تقسیم، و علامت هشتک (^) برای عملیات به توان رساندن، استفاده می کند.



تمام فرمولها در اکسل باید با علامت برابر بودن (=) آغاز گردد.

درک ارجاع دادن به سلولها در اکسل

اگر چه شما می توانید با استفاده مستقیم از اعداد فرمولهای ساده ای در اکسل بسازید (بعنوان مثال 2+2= یا 5*5=)، اما غالبا شما از آدرس سلولها برای ساختن فرمولها استفاده خواهید کرد. این کار را ارجاع دادن به سلولها می نامند. استفاده از ارجاع سلولها باعث می گردد تا این اطمینان حاصل گردد که فرمولهای شما همیشه صحیح می مانند، زیرا شما می توانید مقدار سلولهای ارجاع داده شده را بدون اینکه به فرمول دست بزنید یا آن را بازنویسی کنید، تغییر بدهید.

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

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

بوسیله ترکیب عملگرهای ریاضی و ارجاع به سلولها، شما می توانید فرمولهای ساده متنوعی در اکسل بسازید. فرمولها همچنین می توانند شامل ترکیبی از ارجاع به سلولها و اعداد نیز باشند. در مثال زیر این موضوع نشان داده شده است.

روش ایجاد یک فرمول در اکسل

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

➊ سلولی را که می خواهید فرمول در آنجا قرار بگیرد را انتخاب کنید، در این مثال ما سلول D12 را انتخاب می کنیم.



➋ علامت برابر است با (=) را تایپ کنید. توجه داشته باشید که این علامت چگونه در سلول و همینطور نوار فرمول نمایش داده می شود.



➌ آدرس سلول اولی را که می خواهید در فرمول به آن ارجاع شود را تایپ کنید. در این مثل ما سلول D10 را تایپ می کنیم. یک حاشیه آبی در اطراف سلولی که به آن ارجاع شده است نمایان می شود.



➍ عملگر ریاضی را که می خواهید استفاده کنید را تایپ کنیدو در این مثال ما علامت جمع (+) را تایپ می کنیم.

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



➏ دکمه اینتر در صفحه کلید را فشار دهید. فرمول محاسبه می شود، و مقدار محاسبه شده در سلول مربوط به فرمول نمایان می گردد. اگر دوباره سلول مربوط به فرمول را انتخاب کنید، متوجه خواهید شد که این سلول نتایج محاسبه را نشان می دهد، در حالیکه خود فرمول در نوار فرمول نمایش داده می شود.

اگر نتیجه فرمول بزرگتر از سلولی باشد که قرار است در آنجا نمایش داده شود، نتیجه با نشانه های پوند (#######) نمایش داده می شود. معنای این نشانه ها اینست که عرض سلول به اندازه کافی نیست و محتوا نمی تواند بصورت کامل نمایش داده شود. در این حالت به سادگی فقط عرض سلول را به اندازه کافی افزایش بدهید تا بتوانید نتایج را مشاهده کنید.

ویرایش مقادیر با ارجاع به سلولها

مزیت واقعی ارجاع به سلولها اینست که به شما اجازه می دهد تا بدون نیاز به بازنویسی فرمول، داده هایتان را در برگه ها تغییر بدهید. در مثال زیر، ما مقدار سلول D1 را از 1200 به 1800 تغییر می دهید. فرمول موجود در سلول D3 بصورت اتوماتیک دوباره نتیجه را محاسبه کرده و در سلول D3 نمایش می دهد.

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

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

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

➊ سلولی را که می خواهید در آن فرمولتان را بنویسید انتخاب کنید، در این مثال ما سلول D4 را انتخاب می کنیم.



➋ علامت برابر است با (=) را تایپ کنید.

➌ سلولی را که می خواهید در ابتدای فرمول به آن ارجاع بدهید را انتخاب کنید.



➍ عملگر ریاضی مورد نظرتان را تایپ کنید. در این مثال ما عملگر ضرب (*) را تایپ می کنیم.

➎ سلول بعدی را که می خواهید در فرمول به آن ارجاع شود را انتخاب کنید.



➎ دکمه اینتر صفحه کلید را بفشارید. فرمول محاسبه می شود و نتایج آن نمایش داده می شود.

کپی کردن فرمولها با استفاده از ویژگی fill handle

فرمولها با استفاده از ویژگی fill handle می توانند به سلولهای مجاورشان کپی شوند. این ویژگی می تواند در زمان و بهره وری شما تاثیر مثبتی بگذارد.

➊ سلولی که فرمول در آن قرار دارد و قصد کپی کردنش را دارید انتخاب کنید.

➋ بعد از اینکه ماوس را رها کردید، فرمول به سلولهای انتخاب شده کپی می شود.

ویرایش یک فرمول در اکسل

گاهی اوقات ممکن است قصد تغییر یک فرمول را داشته باشید. در مثال زیر ما یک آدرس غلط را در یک فرمول استفاده کرده ایم و حالا می خواهیم تا اصلاحش کنیم.

➊ سلولی که فرمول در آن قرار دارد را انتخاب کنید. در مثال ما D12.



➋ بر روی نوار فرمول کلیک کنید تا بتوانید فرمول را اصلاح کنید. همچنین اگر بر روی سلول با ماوس دبل کلیک کنید امکان ویرایش در همان سلول هم فراهم می گردد.



➌ حاشیه ای در اطراف تمامی سلولهایی که در فرمول به آن ارجاع شده است نمایان می گردد. در این مثال ما قسمت اول فرمول را تغییر می دهیم. مقدار D10 را جایگرین D9 می کنیم.



➍ وقتی کارتان تمام شد دکمه اینتر را بفشارید.



➎ فرمول تغییر خواهد کرد و مقدار جدید محاسبه شده و نمایش داده می شود.

اگر نظرتان عوض شده باشد می توانید با فشردن کلید Esc روی صفحه کلید و یا دکمه کنسل در نوار فرمول عملیات ویرایش را لغو کنید.

برای اینکه تمامی فرمولهای موجود در برگه اکسل را بتوانید مشاهده نمایید می توانید از کلیدهای ترکیبی Ctrl+` استفاده کنید. با فشردن مجدد این کلید ترکیبی به وضعیت معمول قبل بر می گردید.

یکی از نکات بسیار کابردی در اکسل فرمت بندی سفارشی اعداد در اکسل است

با ما همراه باشید تا با تعدای از آنها آشنا شویم

فرمت سلول ها در اکسل

3. بر روی گزینه Custom در لیست کلیک کنید تا فرمت سفارشی اعداد باز شود. اکسل فرمت تنظیم‌شده در مرحله قبل را به‌صورت دستور زبانی که موردنظر خود می‌باشد، نشان دهد.

فرمت سفارشی اعداد در اکسل

مشخص است که دستور زبان فرمت انتخاب شده قبل به‌صورت

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

این دستورالعمل نوشتاری به اکسل می‌گوید که اعداد را چگونه نمایش دهد. این دستورالعمل شامل فرمت‌های مختلفی بوده که از طریق نقطه کاما (;) از هم تفکیک می‌گردند.

در شکل مذکور دو فرمت قرارگرفته که یکی سمت چپ نقطه کاما و دیگری سمت راست نقطه کاما قرار داده‌شده است. فرمتی که سمت چپ نقطه کاما قرار دارد برای اعداد مثبت اعمال‌شده و فرمتی که در سمت راست نقطه کاما قرار دارد برای اعداد منفی اعمال می‌شود؛ بنابراین در این مثال اعداد منفی با نمایش پرانتز در اطراف آن و اعداد مثبت به‌صورت عادی نمایش داده می‌شود.

توجه کنید که علامت _) در انتهای فرمت مثبت به اکسل می‌گوید که به اندازه یک پرانتز در انتهای اعداد مثبت فاصله بیاندازد. با این کار اعداد مثبت هم‌تراز با اعداد منفی (که پرانتز در اطراف آن قرار دارد)، در زیر هم و شکیل‌تر نمایش داده می‌شود.

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

+1,200

-15,000,000

به‌راحتی می‌توان این فرمت را برای نشان دادن اعداد به‌صورت درصد اصلاح کرد. فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:

+0%;-0%

این دستورالعمل نوشتاری، اعداد را به‌صورت زیر نمایش می‌دهد:

+43%

-54%

می‌توان زیباتر عمل نمود و دستورالعمل نوشتاری را به‌صورت زیر وارد کرد:

0%_);(0%)

با این تغییرات اعداد قبلی به این صورت نمایش داده می‌شود:

 45%

(54%)

درصورتی‌که در دستورالعمل از نقطه کاما استفاده نکنید نشان‌دهنده این است که فقط یک فرمت را انتخاب کرده‌اید و این فرمت هم برای اعداد مثبت و هم اعداد منفی اعمال می‌شود.

خلاصه کردن اعداد بزرگ از طریق گرد کردن به ضرایب هزار، میلیون و … در اکسل

در قسمت قبل برای نمای اعداد بزرگ از جداکننده هزارگان استفاده شد. برای نمایش واضح‌تر اعداد بزرگ و گیج نکردن مخاطبان از نمایش سه رقم سمت راست هزارگان و شش رقم میلیون و … می‌توان صرفه نظر کرد. برای انجام این کار کافی است مراحل قبلی که برای فرمت سفارشی اعداد استفاده شد، انجام دهید. در کادر مربوطه این فرمت را وارد کنید (به کامای آخر توجه کنید):

از شکل مشخص است که دستور زبان فرمت انتخاب شده قبل به‌صورت

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

این دستورالعمل نوشتاری به اکسل می‌گوید که اعداد را چگونه نمایش دهد. این دستورالعمل شامل فرمت‌های مختلفی بوده که از طریق نقطه کاما (;) از هم تفکیک می‌گردند.

در شکل مذکور دو فرمت قرارگرفته که یکی سمت چپ نقطه کاما و دیگری سمت راست نقطه کاما قرار داده‌شده است. فرمتی که سمت چپ نقطه کاما قرار دارد برای اعداد مثبت اعمال‌شده و فرمتی که در سمت راست نقطه کاما قرار دارد برای اعداد منفی اعمال می‌شود؛ بنابراین در این مثال اعداد منفی با نمایش پرانتز در اطراف آن و اعداد مثبت به‌صورت عادی نمایش داده می‌شود.

توجه کنید که علامت _) در انتهای فرمت مثبت به اکسل می‌گوید که به اندازه یک پرانتز در انتهای اعداد مثبت فاصله بیاندازد. با این کار اعداد مثبت هم‌تراز با اعداد منفی (که پرانتز در اطراف آن قرار دارد)، در زیر هم و شکیل‌تر نمایش داده می‌شود.

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

«فرمت سفارشی اعداد» یکی از ابزارهای کاربردی اکسل برای نمایش اعداد محسوب می شود و مطمئن هستم خیلی از دوستانی که با اکسل کار می کنند حداقل یک بار سر و کارشون با شیوهی نمایش اعداد در اکسل افتاده؛ بعضی ها هم که هر روز با این موضوع روبرو هستند.

کاربرد نرم‌افزار اکسل اون قدر گسترده هست که فکر می‌کنم هیچ مدیر پروژه‌ یا کسی که در پروژه فعالیت می کند یک روز هم بدون این برنامه نتواند سر کند. بعضی مواقع فکر می‌کنم که دنیای بدون اکسل چقدر سخته و چقدر محاسبات وقت‌گیر و پیچیده خواهد بود. بااین‌حالی که بیش از 15 سال است که با اکسل کار می‌کنم اگر هرروز یک مطلب جدید از اون یاد بگیرم باز هم تعجب نخواهم کرد، چون می‌دانم دنیای این برنامه اون قدر گسترده و وسیع است که حالا حالاها باید باهاش کار کرد.

مفاهیم پایه‌ای فرمت سفارشی اعداد در اکسل

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

1. بر روی محدوده مورد نظرتون که می‌خواهید فرمت اعداد اون رو عوض کنید راست کلیک کنید و آیتم Format Cells رو انتخاب کنید تا پنجره Format Cells باز شود.
2. بر روی زبانه یا تب Number بروید و از لیستی که در پایین نشان داده‌شده گزینه Number رو انتخاب کنید (البته ناگفته نماند این روشی که ارائه می‌شود محدود به انتخاب Number نمی‌شه و هر گزینه‌ای که در لیست قرار داره رو می‌شه انتخاب کرد و سپس به مراحل بعدی رفت).

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

فرمت سلول ها در اکسل

3. بر روی گزینه Custom در لیست کلیک کنید تا فرمت سفارشی اعداد باز شود. اکسل فرمت تنظیم‌شده در مرحله قبل را به‌صورت دستور زبانی که موردنظر خود می‌باشد، نشان دهد.

فرمت سفارشی اعداد در اکسل

از شکل مشخص است که دستور زبان فرمت انتخاب شده قبل به‌صورت

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

این دستورالعمل نوشتاری به اکسل می‌گوید که اعداد را چگونه نمایش دهد. این دستورالعمل شامل فرمت‌های مختلفی بوده که از طریق نقطه کاما (;) از هم تفکیک می‌گردند.

در شکل مذکور دو فرمت قرارگرفته که یکی سمت چپ نقطه کاما و دیگری سمت راست نقطه کاما قرار داده‌شده است. فرمتی که سمت چپ نقطه کاما قرار دارد برای اعداد مثبت اعمال‌شده و فرمتی که در سمت راست نقطه کاما قرار دارد برای اعداد منفی اعمال می‌شود؛ بنابراین در این مثال اعداد منفی با نمایش پرانتز در اطراف آن و اعداد مثبت به‌صورت عادی نمایش داده می‌شود.

توجه کنید که علامت _) در انتهای فرمت مثبت به اکسل می‌گوید که به اندازه یک پرانتز در انتهای اعداد مثبت فاصله بیاندازد. با این کار اعداد مثبت هم‌تراز با اعداد منفی (که پرانتز در اطراف آن قرار دارد)، در زیر هم و شکیل‌تر نمایش داده می‌شود.

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

+1,200

-15,000,000

به‌راحتی می‌توان این فرمت را برای نشان دادن اعداد به‌صورت درصد اصلاح کرد. فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:

+0%;-0%

این دستورالعمل نوشتاری، اعداد را به‌صورت زیر نمایش می‌دهد:

+43%

-54%

می‌توان زیباتر عمل نمود و دستورالعمل نوشتاری را به‌صورت زیر وارد کرد:

0%_);(0%)

با این تغییرات اعداد قبلی به این صورت نمایش داده می‌شود:

 45%

(54%)

درصورتی‌که در دستورالعمل از نقطه کاما استفاده نکنید نشان‌دهنده این است که فقط یک فرمت را انتخاب کرده‌اید و این فرمت هم برای اعداد مثبت و هم اعداد منفی اعمال می‌شود.

خلاصه کردن اعداد بزرگ از طریق گرد کردن به ضرایب هزار، میلیون و …

در قسمت قبل برای نمای اعداد بزرگ از جداکننده هزارگان استفاده شد. برای نمایش واضح‌تر اعداد بزرگ و گیج نکردن مخاطبان از نمایش سه رقم سمت راست هزارگان و شش رقم میلیون و … می‌توان صرفه نظر کرد. برای انجام این کار کافی است مراحل قبلی که برای فرمت سفارشی اعداد استفاده شد، انجام دهید. در کادر مربوطه این فرمت را وارد کنید (به کامای آخر توجه کنید):

#,##0,

درصورتی‌که بخواهید شش رقم آن حذف شود و اعداد به صورت ضریبی از میلیون نمایش داده شود، کافی است دو کاما اضافه نمود:

#,##0,,

برای درک بهتر مطلب به شکل زیر توجه کنید. در این شکل هزینه، درآمد و سود دیسیپلین مکانیکال در یک پروژه به نمایش در آمده است:

«فرمت سفارشی اعداد» یکی از ابزارهای کاربردی اکسل برای نمایش اعداد محسوب می شود و مطمئن هستم خیلی از دوستانی که با اکسل کار می کنند حداقل یک بار سر و کارشون با شیوهی نمایش اعداد در اکسل افتاده؛ بعضی ها هم که هر روز با این موضوع روبرو هستند.

کاربرد نرم‌افزار اکسل اون قدر گسترده هست که فکر می‌کنم هیچ مدیر پروژه‌ یا کسی که در پروژه فعالیت می کند یک روز هم بدون این برنامه نتواند سر کند. بعضی مواقع فکر می‌کنم که دنیای بدون اکسل چقدر سخته و چقدر محاسبات وقت‌گیر و پیچیده خواهد بود. بااین‌حالی که بیش از 15 سال است که با اکسل کار می‌کنم اگر هرروز یک مطلب جدید از اون یاد بگیرم باز هم تعجب نخواهم کرد، چون می‌دانم دنیای این برنامه اون قدر گسترده و وسیع است که حالا حالاها باید باهاش کار کرد.

یه مدت پیش دنبال یک مطلب می‌گشتم که اتفاقی به کتاب Excel® Dashboards and Reports, 2nd Edition از نویسنده پرآوازه اکسل John Walkenbach و Mike Alexander برخورد کردم که حیفم اومد اون رو با شما در میان نگذارم.

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

 فرمت سفارشی اعداد در اکسل

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

1. بر روی محدوده مورد نظرتون که می‌خواهید فرمت اعداد اون رو عوض کنید راست کلیک کنید و آیتم Format Cells رو انتخاب کنید تا پنجره Format Cells باز شود.
2. بر روی زبانه یا تب Number بروید و از لیستی که در پایین نشان داده‌شده گزینه Number رو انتخاب کنید (البته ناگفته نماند این روشی که ارائه می‌شود محدود به انتخاب Number نمی‌شه و هر گزینه‌ای که در لیست قرار داره رو می‌شه انتخاب کرد و سپس به مراحل بعدی رفت).

 همان‌طور که ملاحظه می کنید، تعداد اعشار صفر، استفاده از کاما برای جداکننده هزارگان و همچنین استفاده از پرانتز برای نمایش اعداد منفی استفاده شده است.

فرمت سلول ها در اکسل

3. بر روی گزینه Custom در لیست کلیک کنید تا فرمت سفارشی اعداد باز شود. اکسل فرمت تنظیم‌شده در مرحله قبل را به‌صورت دستور زبانی که موردنظر خود می‌باشد، نشان دهد.

فرمت سفارشی اعداد در اکسل

 مشخص است که دستور زبان فرمت انتخاب شده قبل به‌صورت

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

این دستورالعمل نوشتاری به اکسل می‌گوید که اعداد را چگونه نمایش دهد. این دستورالعمل شامل فرمت‌های مختلفی بوده که از طریق نقطه کاما (;) از هم تفکیک می‌گردند.

در شکل مذکور دو فرمت قرارگرفته که یکی سمت چپ نقطه کاما و دیگری سمت راست نقطه کاما قرار داده‌شده است. فرمتی که سمت چپ نقطه کاما قرار دارد برای اعداد مثبت اعمال‌شده و فرمتی که در سمت راست نقطه کاما قرار دارد برای اعداد منفی اعمال می‌شود؛ بنابراین در این مثال اعداد منفی با نمایش پرانتز در اطراف آن و اعداد مثبت به‌صورت عادی نمایش داده می‌شود.

توجه کنید که علامت _) در انتهای فرمت مثبت به اکسل می‌گوید که به اندازه یک پرانتز در انتهای اعداد مثبت فاصله بیاندازد. با این کار اعداد مثبت هم‌تراز با اعداد منفی (که پرانتز در اطراف آن قرار دارد)، در زیر هم و شکیل‌تر نمایش داده می‌شود.

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

+1,200

-15,000,000

به‌راحتی می‌توان این فرمت را برای نشان دادن اعداد به‌صورت درصد اصلاح کرد. فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:

+0%;-0%

این دستورالعمل نوشتاری، اعداد را به‌صورت زیر نمایش می‌دهد:

+43%

-54%

می‌توان زیباتر عمل نمود و دستورالعمل نوشتاری را به‌صورت زیر وارد کرد:

0%_);(0%)

با این تغییرات اعداد قبلی به این صورت نمایش داده می‌شود:

 45%

(54%)

درصورتی‌که در دستورالعمل از نقطه کاما استفاده نکنید نشان‌دهنده این است که فقط یک فرمت را انتخاب کرده‌اید و این فرمت هم برای اعداد مثبت و هم اعداد منفی اعمال می‌شود.

خلاصه کردن اعداد بزرگ از طریق گرد کردن به ضرایب هزار، میلیون و … در اکسل

در قسمت قبل برای نمای اعداد بزرگ از جداکننده هزارگان استفاده شد. برای نمایش واضح‌تر اعداد بزرگ و گیج نکردن مخاطبان از نمایش سه رقم سمت راست هزارگان و شش رقم میلیون و … می‌توان صرفه نظر کرد. برای انجام این کار کافی است مراحل قبلی که برای فرمت سفارشی اعداد استفاده شد، انجام دهید. در کادر مربوطه این فرمت را وارد کنید (به کامای آخر توجه کنید):

#,##0,

درصورتی‌که بخواهید شش رقم آن حذف شود و اعداد به صورت ضریبی از میلیون نمایش داده شود، کافی است دو کاما اضافه نمود:

#,##0,,

برای درک بهتر مطلب به شکل زیر توجه کنید. در این شکل هزینه، درآمد و سود دیسیپلین مکانیکال در یک پروژه به نمایش در آمده است:

گرد کردن اعداد به صورت ضریبی از میلیون در اکسل

قبل و بعد از گرد کردن اعداد به صورت ضریبی از میلیارد

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

نکته حائز اهمیت این است که اکسل تمام ارقام حذف‌شده را در محاسباتش در نظر می‌گیرد و فقط برای نمایش خروجی، ارقام را بر اساس فرمت در نظر گرفته‌شده اصلاح می‌کند.

خیلی از مواقع دیدم که دوستان برای این کار (گرد کردن اعداد به ضرایب هزارگان) اعداد را بر 1,000 و یا 1,000,000 و … تقسیم می‌کند و این در حالی است که در هرجایی که بخواهند از این اعداد برای محاسباتشان استفاده کنند باید دوباره آن اعداد را به فرمت اولیه بازگردانند و به‌عبارت‌دیگر باید در هرجایی یکپارچگی محاسباتشان در اکسل را حفظ نمایند که می‌تواند بسیار وقت‌گیر و در بعضی مواقع باعث بروز اشتباه در محاسباتشان گردد.

در صورت نیاز می‌توان گرد کردن اعداد به ضرایب هزار، میلیون و … را با اضافه کردن یک حرف یا کلمه به انتهای فرمت مشخص‌تر نمود:

#,##0, “k”

با این کار اعداد بدین‌صورت نمایش داده می‌شود

188k

318k

از این تکنیک برای نمایش اعداد مثبت و منفی نیز می‌توان استفاده نمود:

#,##0, "k";(#,##0, "k")

بعد از اعمال این فرمت اعداد مثبت و منفی بدین‌صورت نمایش داده می‌شود:

(318k)

همین منطق را اگر بخواهید برای ضریب میلیون استفاده کنید، کافی است از دو کاما استفاده کرده و عبارت موردنظر خود را به انتهای دستورالعمل اضافه نمایید.

#,##0.00,, “m”

با ترفند قبلی و با اضافه کردن دو رقم اعشار، وقتی‌که اعداد به ضریب میلیون گرد می‌شوند، می توان با نمایش دو رقم اعشار دقت اعداد نمایش داده‌شده را بالا برد.

به‌عنوان‌مثال اگر که هزینه، درآمد و سود دیسیپلین سیویل به صورت ضریب میلیارد (نه رقم) گرد شده و برای بالا بردن دقت از دو رقم اعشار استفاده شده است:

برای انتقال و کپی کردن اطلاعات SQL Server و Excel از روش‌های مختلفی می‌توان استفاده کرد و همواره شرایط خاص خود را خواهد داشت. در مطالب قبلی منتشر شده به نحوه کپی کردن اطلاعات جدول SQL به اکسل پرداختیم و حال در این مطلب به روش معکوس آن یعنی انتقال اطلاعات از Excel به جدول SQL خواهیم پرداخت.

روش کپی کردن فایل بین اکسل و SQL بسیار ساده است و به راحتی قابل انجام می‌باشد، اما برخی نکات در این بین وجود خواهد داشت که باید به آن دقت ویژه‌ای داشت.

در ادامه با آموزش انتقال اطلاعات از Excel به جدول SQL همراه ما باشید.

انتقال اطلاعات از Excel به جدول SQL

1- نرم افزار SQL Server management را باز کرده و به دیتابیس خود متصل شوید.

2- ابتدا یک جدول در پایگاه داده SQL Server خود بسازید.

3- دقت داشته باشید در هنگام ساخت ستون‌های مورد نیاز، تعداد ستون ها و همچنین نوع آن ها بسیار حائز اهمیت می‌باشد؛ به طوری که اگر نوع ستون به درستی مشخص نگردد در هنگام کپی با خطا روبه‌رو خواهید شد.

به عنوان مثال اطلاعات Integer را در Text نمی‌توان کپی کرد و با خطا روبه‌رو خواهید شد.

4- فایل اکسل خود را باز کرده و ردیف‌های مورد نظر را انتخاب و بر روی آن کلیک راست کنید.

5- سپس گزینه Copy را کلیک کنید و یا از کلید ترکیبی Ctrl + C استفاده نمایید.

6- در پایگاه داده خود، بر روی جدول کلیک راست کرده و Edit Top 200 Rows را انتخاب کنید.

7- در پنجره باز شده بر روی نشانگر سطر کلیک راست نمایید.

8- سپس گزینه paste را بزنید و یا کلید ترکیبی Ctrl + V را فشار دهید.

در صورتی که اطلاعات وارد شده و سطرها همخوانی داشته باشند، همانند تصویر زیر اطلاعات به صورت کامل کپی خواهد شد.

در نظر داشته باشید، روش توضیح داده شده برای تعداد سطرهای کمتر از 200 عدد می باشد.