نکاتی پیرامون جدا کردن قسمتی از متن سلول در اکسلl

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

 

 

 

 

فرمول LEFT:

به وسیله این فرمول می توان از سمت چپ یک سلول ، قسمتی از متن آن را جدا کرد ساختار فرمول به شکل زیر است .

 

در قسمت اول فرمول : سلولی که می خواهیم قسمتی از متن آن را جدا کنیم ، را وارد نمائیم .

در قسمت دوم فرمول : تعداد کاراکترهایی که می خواهیم از متن جدا کنیم را وارد می کنیم .

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

فرمول RIGHT: به وسیله این فرمول می توان از سمت راست یک سلول ، قسمتی از متن آن را جدا کرد.

ساختار فرمول کاملا همانند فرمول LEFTاست و دارای دو قسمت می باشد در قسمت اول ، متن مورد نظر انتخاب می شود و در قسمت دوم ، تعداد کاراکترهایی که می خواهیم جدا کنیم را وارد می نمائیم .

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

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

در قسمت اول فرمول : سلولی که می خواهیم قسمتی از متن آن را جدا کنیم را وارد می نمائیم

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

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

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

فرمول SEARCH در اکسل :

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

 

در قسمت اول فرمول : کلمه مورد نظر را که می خواهیم در سلول و متن ، جستجو و یافت شود را مینویسیم

در قسمت دوم فرمول : سلول مورد نظر که متن در آن قرار دارد و می خواهیم جستجو در آن سلول صورت گیرد را وارد می نمائیم .

در قسمت سوم فرمول :  در این قسمت عدد 1 یک را مینویسیم .

به زبان ساده ، این فرمول می گوید ، کلمه ای مورد نظر شما ، چندمین کاراکتر یک متن خاص است.

یک مثال کلی برای فرمول های فوق  : اگر یک ستون از جدولی خاص شامل تاریخ های مختلف باشد ( مثل 1394/05/01و 1393/03/06و 1392/04/23و ... ) و شما بخواهید از هر سلول فقط قسمت سال آن را جدا کنید به شکل زیر عمل می کنید .

با این فرمول از سلول تاریخ فقط 4 عدد سمت چپ که مربوط به سال است جدا میشود   =LEFT(A2;4)

از هر سلول فقط قسمت روز آن را جدا کنید:

با این فرمول از سلول تاریخ فقط2عدد سمت راست که مربوط به روز است جدا میشود =RIGHT(A2;4)

از هر سلول فقط قسمت ماه آن را جدا کنید:

با این فرمول با توجه به قرار گرفتن عدد ماه در ساختار تاریخ شمسی (1394/01/05)  از ششمین کاراکتر تاریخ شروع و دو عدد ماه جدا میشود  0      =MID(A2;6;2

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

 

یکی از امکانات بسیار پرکاربرد در نرم افزار اکسل امکان درج سری‌های عددی به صورت خودکار و تنها با مشخص کردن دوجمله از سری می‌یاشد. این امکان به ما اجازه می‌دهد در کمترین زمان ممکن تعداد زیادی از اعداد در یک سری را درج و برای استفاده آماده نماییم. برای انجام عمل مذکور در نرم افزار اکسل به روش زیر عمل می‌کنیم

در ادامه با ما همراه باشید

ابتدا دو جمله از دنباله مورد نظر را درج می کنیم (سطری یا ستونی).

 

 

---

 دنباله ای از خانه‌های که می‌خاهیم سری مورد نظر در آن‌ها درج شود را با شروع از دو خانه اول انتخاب می‌کنیم.

 

---

از منوی "home" گزینه "Fill" و از منوی باز شده گزینه "Series" را انتخاب می‌کنیم.

 

---

در پنجره "series" بسته به نحوه انتخاب (سطری یا ستونی) "Rows "یا "Columns" را انتخاب می‌کنیم.

 

---

در پنجره "series" از قسمت "type" نوع سری را نیز "linear" انتخاب می کنیم.  

 

---

در صورت تایید گام (step) دکمه "ok" را می زنیم

 

---

همان طور که مشاهده می کنید اعداد به صورت 5 تا 5 زیاد شد.

 

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

لحظه هایتان به زیبایی رسیدن به آرزو های بهاری تان

 

 

 

 

 

نکاتی پیرامون کنترل اندازه سلول هادر اکسل

 

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

مثلا فرض کنید در یک جدول  ستون D و سطر 9 رنگشان با بقیه فرق دارد. آن را انتخاب کرده وقتی که موس را روی ستون D می گذاریم شبیه به یک فلش می شود و اگر روی آن کلیک کنیم ، تمام ستون انتخاب می شود.

حالا یک مقدار موس را حرکت می دهیم و آن را روی مرز بین ستون E و ستون D می آوریم.

شکل موس به یک فلش دوجهته تغییر می یابد و به راحتی با کلیک و درگ می توانیم اندازه آن را کوچک و یا بزرگ کنیم.

 

الان ستون D بسیار کوچک شد و محتویات داخل آن کاملا مشخص نیست. وقتی  روی آن کلیک می کنیم اطلاعات آن سلول را در بالا می بینیم. حال به سراغ ستون E می آییم و طول آن را هم کم می کنیم.

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

 

حالا روی یکی از این سلول ها قرار می گیریم که یک Tooltip ظاهر می شود که محتویات آن سلول را نمایش می دهد.

 

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

حالا به سراغ ستون D می آییم و اندازه آن را تغییر می دهیم.

زمانی که روی D کلیک کردیم و درگ (Drag) می کنیم ، اندازه طول ستون D را با واحد پیکسل می بینیم.

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

راه بهتر: روی ستون   را انتخاب کنید. حال در ریبون FormatCells E کلیک کرده 

 

 گزینه Column Width را می زنیم. یک پنجره محاوره ای باز می شود طبق شکل زیر:

 

طول ستونمان را تغییر می دهیم و بعد Ok را می زنیم.

روش دیگر: به سراغ ریبون  Format cells  رفته ، و گزینه Autofit Column Width را انتخاب می کنیم.

 

اکنون اطلاعات به صورت اتوماتیک به اندازه محتویات آن تغییر می کند.

همین تنظیمات را می توان در مورد ارتفاع سطرها یا همان ارتفاع سلول ها انجام داد.

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

به سراغ Format آمده و این بار گزینه row height را انتخاب کرده و ارتفاع سطر را زیاد می کنیم.

یا می توان به صورت اتوماتیک از ریبون Format گزینه Autofit row height را انتخاب کرد.

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

 وقتی طول ستون E را کم کردیم، به جای عدد در آن # نمایش داده شد.

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

همان طور که می بینیم یک مقدار عددی با واحد ریال داخل سلول قرار دارد.

 

 مثلا می توانیم با کلیک روی Increase تعداد صفرهای عدد را زیاد کنیم.

 

یا با کمک decrease صفرهایی را که اضافه کرده بودیم حذف کنیم و یا محتویات سلولی این عدد را به درصد تغییر دهیم و نیز می توان اطلاعات عددی را به عنوان یک مبلغ تعیین کرد.

 

در حقیقت در این قسمت واحد پول را مشخص می کنیم. مثلا دلار ، یورو ، پوند و واحدهای پولی دیگر. قسمت Custom را باز می کنیم.

 

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

حالا روی More Number Formats کلیک می کنیم. یک پنجره باز می شود.

می بینیم که در قسمت Category  گزینه Custom انتخاب شده است و در قسمت Type واحد پولی ریال انتخاب شده است.

نحوه قرارگیری اطلاعات داخل یک سلول در excel

روی عنوان جدول کلیک کرده ، با دقت به ریبون Alignment نگاه کنید. دوتا از آن ها فعال شدند.

 

 

نوشته شما در مرکز سلول است و این گزینه انتخاب شده هم Center است.

و گزینه بالایی Middle Align می باشد.

Middle Align : در سلول نوشته را در وسط قرار می دهد.

Top Align : در سلول نوشته را بالا قرار می دهد.

Down Align : در سلول نوشته را پایین قرار می دهد.

حال به سراغ Orientation می رویم.

 می توانید به متن شکل خاصی دهید و متن را به هر سمتی که می خواهید بچرخانید.

 

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

دستور Merge & Center : این دستور خطوط بین سلول ها را از بین می برد. و متن را به حالت اول بر می گرداند.

 

برای یکی شدن سلول ها روی گزینه Wrap Text کلیک می کنیم.

 

 

فونت در اکسل

حالا به سراغ کنترل سلول جدولمان خواهیم رفت. به ریبون فونت می آییم و با باز شدن گزینه Font color می توانیم موس را روی هرکدام از رنگ هایی که می خواهیم برده و رنگ آن را انتخاب کنیم.

مثلا رنگ قرمز را انتخاب می کنیم.

 

حال به سراغ Fill Color می آییم. می بینیم که رنگ زمینه (Background) سلول انتخابی عوض می شود. نارنجی را انتخاب می کنیم و ظاهر این متن را هم می توانیم کنترل کنیم.

 

 

به عنوان مثال U ، B ، I

 

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

فیلتر کردن اطلاعات در اکسل

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

بدین صورت عناوین ستون هایمان را انتخاب می کنیم. پس به سراغ ریبون editing رفته و Sort & Filter را انتخاب میکنم.

 

 بلافاصله سلول هایی که انتخاب شده بودند به صورت شکل زیر تغییر وضعیت می دهند.

و در کنار آن ها یک مثلث کوچک قرار می گیرد.

حال به سراغ یکی از مثلث سلول ها می رویم مانند سلول سال.

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

که در کنار آنها یک Checkbox است که در حالت انتخاب می باشد.

تیک کنار 1385 را برمی داریم و حالا Ok می کنیم.

اکنون فقط اطلاعات سال 1386 در اختیار ما قرار می گیرد.

یعنی فعلا اطلاعات سال 1385 را نمی بینیم.

این بار به سراغ سلول غرفه ها می رویم و یک لیست از اطلاعات درون غرفه ها را می آورد. تیک Select All را برداشته و برای کیف و کفش تیک می گذاریم و آن را کلیک می کنیم.

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

حالا می خواهیم فیلتر را حذف کنیم. به ریبون editing رفته . گزینه Sort & Filter را انتخاب کرده و Clear را می زنیم.

کنترل ظاهری سلول ها در اکسل

یکی از ریبون های دیگر ریبون Style است. 3 گزینه دارد:

با استفاده از Format as table شکل ظاهری جدول را تغییر می دهیم. با کلیک و درگ کردن، تمام سلول های جدول را انتخاب می کنیم. حالا به سراغ Format as table می رویم و وقتی که روی آن کلیک کنیم یک لیست از Style های مختلف آماده در اکسل را برای ما باز می کند.

 

هرکدام را که انتخاب کنیم یک پنجره به نام Format as table برای ما باز می شود که محدوده انتخاب شده در آن مشخص است.

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

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

حل م عدم نمایش بیش از 15 رقم در یک سلول و کپی کردن سلول ها به عنوان عکس در اکسل

 

در مورد حل مشکل عدم نمایش بیش از 15 رقم در یک سلول در اکسل ارائه شده است.

: حل مشکل عدم نمایش بیش از 15 رقم در یک سلول در اکسل 

 

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

این موضوع به دلیل فرمت یا ویژگی آن سلول است.
برای حل این مشکل کافی است:
ابتدا  سلول یا سلول‌های مورد نظر که چنین مشکلی را دارند را انتخاب کنید.
بر روی یکی از این سلول‌های انتخاب شده راست کلیک کرده و Format Cells را انتخاب کنید.
اکنون در تب Number و قسمت Category، گزینه Text را انتخاب نموده و OK کنید.
خواهید دید که کل ارقام به طور کامل نمایش داده می‌شوند.

برای انجام سریع‌تر این کار می‌توانید در همان تب Home از قسمت Number حالت سلول را تغییر دهید.

 کپی کردن سلول ها به عنوان عکس در اکسل در اکسل

 

: کپی کردن سلول ها به عنوان عکس در اکسل کپی کردن سلول ها به عنوان عکس در اکسل 

: کپی کردن سلول ها به عنوان عکس در اکسل

همان‌طور که می‌دانید در نرم‌افزار Microsoft Excel امکان Copy و Paste کردن سلول‌ها در محیط نرم‌افزار به آسانی وجود دارد. اما فرض کنید قصد دارید سلول‌ها را در محیط یک‌ نرم‌افزار دیگر نمایش دهید. Excel امکان کپی کردن سلول‌ها در قالب یک تصویر را فراهم می‌کند. در این ترفند به معرفی نحوه انجام این کار می‌پردازیم. این ترفند بر روی نسخه‌ی 2007 به بالای اکسل امکان‌پذیر است.
بدین منظور:
ابتدا  فایل مورد نظر خود را در Microsoft Excel فراخوانی کنید.
سپس سلول‌های مورد نظر خود را به حالت انتخاب دربیاورید.
اکنون در تب Home، بر روی فلش کنار دکمه‌ی Copy کلیک کنید.
حالا در منوی باز شده بر روی Copy as Picture را انتخاب کنید.
در پنجره‌ی باز شده از فعال بودن گزینه‌های As show on screen و Bitmap اطمینان حاصل کرده و سپس بر روی دکمه‌ی OK کلیک کنید.
با این کار تصویر سلول‌های انتخابی به Clipboard کپی شده است.
اکنون می‌توانید درون نرم‌افزار مورد نظر خود (نظیر Paint یا Microsoft Word)، این تصویر را Paste کنید.

Data table و Simulation در اکسل

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

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

 

حالا میخواهیم بدانیم اگر قیمت را دست کاری کنیم چه اتفاقی برای سود نهایی محصول می افتد؟ قیمت را تا چه پایین بیاوریم که سودمان منفی نشود و تا چه حد بالا ببریم که سود مورد نظر مدیریت حاصل شود؟

برای اینکار ابتدا لیست قیمتهای مورد نظرتان را زیر هم بنویسید. مانند تصویر زیر:

 

سپس کل محدوده F3:G24 را انتخاب نموده به تب Data بروید در گزینه What-if Analysis روی گزینه Data table کلیک نمایید.

 

سپس پنجره ای باز میشود که دارای دو قسمت است. چون جدولتان ستونی است قسمت Row را خالی بگذارید و در دومین قسمت یعنی Column Input Cell سلول مربوط به قیمت یعنی B3 را مشخص کنید:

 

حالا کافیست OK کنید و در نتیجه مقادیر مختلف سود به ازای هر قیمت مشخص میشود:

 

 

 چنانچه قیمت محصول چیزی بین ۱۱۰۰۰ و ۱۱۵۰۰ باشد به نقطه سر به سر قیمت و سود خواهید رسید. یعنی چنانچه بخواهید با فروش ۱۰۰۰ تن به سود برسید حداقل باید قیمت فراتر از ۱۱۰۰۰ تومان برای محصول در نظر بگیرید. برای اینکه بتوانید قیمت دقیق را محاسبه کنید باید مقادیر بین این دو عدد را در جدول بگذارید، اعداد خودبخود تغییر میکنند و نتیجه مورد نظرتان را خواهید دید.

حالا اگر بخواهید بدانید با همین قیمت ۱۶۸۰۰ تومان باید چه تعداد محصول به فروش برسانید تا به سود برسید می توانید مانند روش بالا محاسبات را انجام دهید.

 

همانطور که مشاهده می کنید حداقل مقدارفروش برای این محصول باید  ۶۷۰ عدد باشد تا زیان نکنیم. می توانید با تغییر اعداد بصورت دستی در همین جدول نتیجه اعداد مورد نظرتان را مشاهده کنید.

نکته: اگر داده هایتان را بصورت افقی می نویسید در پنجره Data table باید Row Input Cell را تکمیل کنید و دومین گزینه را خالی بگذارید.

 

حالا فرض کنید میخواهید دیتاهایتان بصورت درصد باشد.

یعنی میخواهید بدانید چند درصد باید در مقدار یا قیمت تغییر ایجاد کنید تا سودتان تغییر کند. کاری ندارد:

فرض کنید میخواهیم اینکار را برای سلول مقدار یعنی B2 انجام دهیم. در یک سلول خالی مثلا D2 میزان درصد پیش فرض را صفر میگذاریم و سلول مربوط به عدد مقدار را اصلاح میکنیم و مینویسیم ۱۰۰۰*(۱+D2)

 

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

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

 

حالا کافیست به تی Data بروید روی گزینه what-If Analysis کلیک نمایید و گزینه Data table را انتخاب نموده و این بار هر دو فیلد Row input cell و Column input cell را تکمیل نمایید. برای قسمت اول عدد مرتبط با قیمت یعنی B3 و برای فیلد دوم سلول B2 را انتخاب نمایید و Ok  کنید. آنگاه تصویر زیر را خواهید دید.

 

همانطور که مشخص است با نغییرات مختلف در دو متغیر مقدار و قیمت، سود نهایی حاصل شده متفاوت است. مثلا در قیمت ۱۵۰۰۰ تومان و مقدار فروش ۷۵۰ عدد سودمان برابر صفر خواهد بود و ۷۵۱ امین فروش برایمان سود ایجاد خواهد کرد.

نکاتی پیرامون نحوه استفاده از ابزار Goal Seek در اکسل

فرض کنید در   Excel پس از اعمال محاسبات بر روی اعداد، به نتیجه‌ای نهایی دست پیدا کردید.اما عدد نهایی آنچه که ما انتظار داریم نیست (یا به عبارت دیگر آنچه که ما دوست داریم باشد، نیست).حال برای رسیدن به عدد مورد نظر بایستی در میان انبوهی از اعداد و محاسبات، تغییر کوچکی […]

فرض کنیدپس از اعمال محاسبات بر روی اعداد، به نتیجه‌ای نهایی دست پیدا کردید.
اما عدد نهایی آنچه که ما انتظار داریم نیست (یا به عبارت دیگر آنچه که ما دوست داریم باشد، نیست).
حال برای رسیدن به عدد مورد نظر بایستی در میان انبوهی از اعداد و محاسبات، تغییر کوچکی دهیم.
چنین کاری طبعاً آسان نیست.
به عنوان مثال فرض کنید ما در امتحانات پایان ترم، نمرات ۱۷، ۱۸، ۱۹، ۱۵، ۱۸ و ۱۷ را کسب
کرده‏ایم و آخرین امتحان هنوز باقی مانده است.
اکنون می‏خواهیم بدانیم در امتحان آخر باید چه نمره‏ای بگیریم تا معدل ما ۱۸ شود؟ در این زمان است که
ابزار Goal Seek در اکسل به داد ما می‏رسد و این کار را انجام می‏دهد.به طور کلی این ابزار جهت
تنظیم یک مقدار به گونه‏ای که نتیجه انتخابی ما حاصل شود مورد استفاده قرار می‏گیرد.
بدین منظور: ابتدا Microsoft Excel 2007 را اجرا نمایید.
حال فرض می‏کنیم قصد داریم همانند مثال بالا، بفهمیم چه نمره‏ای باید در امتحان کسب کنیم تا معدل‏مان ۱۸ شود.
ابتدا اعداد (در اینجا نمرات) را در سلول‏های یک ستون وارد می‏کنیم (مثلاً ۶ نمره‏ای که در بالا داریم را
در سلول‏های A1 تا A6 وارد می‏کنیم).
سپس یک سلول خالی را انتخاب می‏کنیم (مثلاً B7) و فرمول (AVERAGE(A1:A7= را در این سلول وارد می‏کنیم (دقت کنید
ما اطلاع داریم که سلول A7 در این مثال خالی است و آن را عمداً خالی نگه داشتیم و در
معدل گیری نیز لحاظش کردیم.
با مطالعه ادامه ترفند دلیل انجام این کار را خواهید فهمید).
اکنون در نوار بالای صفحه به تب Data بروید.
از قسمت Data Tools بر روی What-If Analysis کلیک کرده و سپس Goal Seek در اکسل را انتخاب نمایید.
در قسمت Set cell بایستی ستونی که نتیجه نهایی در آن درج شده است را وارد نمایید (در اینجا B7).
همچنین در قسمت To value بایستی مقدار نتیجه مطلوب خود که در این مثال عدد ۱۸ است را وارد نمایید.
در قسمت By changing cell نیز سلولی که برای قرار دادن آخرین عدد از ابتدا خالی نگه داشته بودیم را
را وارد می‏کنیم (در این مثال سلول A7).
با فشردن دکمه OK محاسبه آغاز می‏شود و نتیجه نهایی در درون سلول خالی قرار می‏گیرد.
به تبع آن معدل نیز تغییر میکند و به آنچه که مورد رضایت ما بوده است تبدیل می‏شود.

نکاتی در مورد تابع ADDRESS و تابع TRANSEPOSEدر اکسل

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

 

Row- num: شماره سطر سلول را وارد کنید.

Cohum-num: شماره ستون سلول را وارد کنید.

[abs_num]: این فیلد اختیاری است و در صورت خالی بودن و یا وارد کردن عدد ۱ سلول مورد نظر مطلق می شود، با وارد کردن عدد ۲ ردیف مورد نظر مطلق و ستون مورد نظر نسبی، با وارد کردن عدد ۳ ردیف مورد نظر نسبی و ستون مورد نظر مطلق و با وارد کردن عدد ۴ سلول مورد نظر به صورت نسبی نمایش داده می شود.

[a1]: یک آرگومان اختیاری و یک مقدار منطقی (Logical Value) میباشد، اگر این آرگومان خالی بماند و یا True باشد، آدرس سلول به فرمت آشنای A1 یعنی شماره سطر عدد و شماره ستون حرف بیان می شود و اگر این آرگومان False باشد فرمت بیان آدرس سلول به صورت

[sheet_text]: اختیاری و متنی است که نام کاربرگ مورد نظر را مشخص می کند.

نکته: آرگومان اول و دوم آرگومان های اجباری و عدد هستند، آرگومان سوم، چهارم و پنجم آرگومان های اختیاری هستند.

به مثال های زیر توجه کنید:

ADDRESS (8,13,3,FALSE,”Sheet1″)= Sheet1!R[8]C13=

ADDRESS (8,13,3,TRUE,”Sheet1″)= Sheet1!$M8=

تابع TRANSEPOSEدر اکسل

 

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

 

Array: تنها آرگومان این تابع آدرس محدوده یا آرایه ای است که می خواهیم Transpose شود.

مثال: برای تبدیل سطر به ستون ابتدا ناحیه سلول های A1 تا A5 را انتخاب کنید. سپس در ناحیه ای که می خواهید فرمول را بنویسید به اندازه معکوس ماتریس مورد نظر، سلول انتخاب کنید و فرمول( Transpose(A1:A5 بنویسید در نهایت به جای Enter از ترکیب Ctrl+Shift+Enter استفاده کنید.

 

نکاتی در مورد پیدا کردن سطرهای تکراری در اکسل

 

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

 

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

پیدا کردن سطرهای تکراری در اکسل و حذف آنها با تابع داده DATA FUNCTION

از این روش زمانی استفاده کنید که:

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

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

در این پنجره اگر تیک My data has headers را بزنید، اکسل از عنوان ستون های شما در کادر مربوط به Columns استفاده خواهد کرد. در کادر Columns تمامی ستون هایی که باید تکراری بودن مقادیر آنها بررسی شود، لیست شده است. پس از اتمام عملیات، اکسل تعداد سطرهای تکراری و غیر تکراری را اعلام خواهد کرد.

پیدا کردن سطرهای تکراری در اکسل با قالب بندی شرطی

از این روش زمانی استفاده کنید که:

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

با استفاده از قالب بندی شرطی می‌توانید سطرهایی با داده های تکراری را به راحتی پیدا کرده و قالب آن را به دلخواه خود تغییر دهید. برای این منظور محدودهای که در آن داده‌های سطرها تکراری هستند را انتخاب کنید. از منوی HOME به زیر منوی Styles رفته در بخش Conditional Formatting کلیک کنید. در منوی باز شده قسمت Highlight Cells Rules را انتخاب کنید و در این قسمت به Duplicate Values بروید. در پنجره باز شده اگر مقدار Duplicate را انتخاب کنید، داده‌های تکرای قالب بندی خواهند شد و اگر Unique را انتخاب کنید، داده‌های بدون تکرار قالب بندی می‌شوند. قالب موردن نظرتان را از سمت راست و از منوی کره‌کره‌ای انتخاب کنید یا در Custom Format قالب دلخواه خود را تعریف کنید.

پیدا کردن سطرهای تکراری در اکسل با استفاده از جدول های پاشنه‌ای

از این روش زمانی استفاده کنید که:

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

اگر می‌خواهید از داده‌های موجود به سرعت ستونی از داده‌های غیر تکراری تولید کنید، استفاده از جدول پاشنه‌ای مناسب است. برای ایجاد یک جدول پاشنه‌ای، از منوی INSERT به زیر منوی Tables رفته و روی Pivot Table کلیک کنید. در پنجره باز شده محدوده داده‌ها را انتخاب کنید. OK نمایید تا جدولتان ایجاد شود. به طور پیش فرض جدول در برگه‌ای جدید ایجاد خواهد شد، اگر می‌خواهید جدولتان در همان برگه داده‌ها ایجاد شود، دکمه رادیویی Existing Worksheet را انتخاب کرده و سلول مورد نظر، برای درج جدول را انتخاب کنید. اگر داده‌های شما در جای دیگری به غیر از برگه فعلی قرار دارد، دکمه رادیویی Use an external data source  را انتخاب نموده و آنها را فراخوانی کنید.

 پس از ایجاد جدول پاشنه‌ای، از قسمت Choose filed to add report عنوان ستونی که اضافه کرده‌اید را به قسمت ROWS درگ کنید. روش درگ کردن را از محدوده در اکسل بخوانید. با تنظیم Value Filed Settings بر روی شمارنده، تعداد داده‌های تکراری را می‌توانید مشاهده کنید.

 

تنظیمات بیشتر بر روی جداول پاشنه‌ای

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

 

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

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

پیدا کردن سطرهای تکراری در اکسل با استفاده از مرتب کردن داده ها

از این روش زمانی استفاده کنید که:

• تعداد داده‌هایتان کم است و می‌توانید با کنترل چشمی آنها را حذف کنید.
• می‌خواهید سطرهای تکراری را ببنید، آنالیز کنید و قبل از حذف، تکراری بودن آنها را تایید کنید.
• نوع داده‌هایتان ساده است و می‌توانید تکراری بودن آنها را تشخیص بدهید. (داده هایتان ۱۵ رقمی و ترکیبی از عدد و حرف نیست!)

مرتب کردن داده‌ها از سریع ترین روش های پیدا کردن و حذف سطرهای تکراری در اکسل است. فرض کنید داده‌هایتان ساده و تعدادشان کم است، به سرعت آنها را مرتب کنید و تکراری ها را حذف نمایید. اگر داده‌هایتان پیچیده است از این روش استفاده نکنید و به سراغ قالب بندی شرطی بروید.

برای مرتب کردن داده‌ها، کل داده‌هایتان را انتخاب کرده و از منوی DATA به زیر منوی Sort&Filter رفته و بر روی Sort کلیک کنید. در پنجره باز شده در قسمت Column ستونی را که می‌خواهید بر اساس آن مرتب سازی کنید، انتخاب نماید. OK کنید داده‌هایتان مرتب خواهند شد.

 

پیدا کردن سطرهای تکراری در اکسل با استفاده از فیلتر پیشرفته Advanced Filter

از این روش زمانی استفاده کنید که:

• فقط می‌خواهید داده‌های بدون تکرار را مشاهده کنید.
• قصد ندارید داده‌های تکراری را حذف کنید و پنهان کردن آنها کافی است.

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

برای اعمال فیلتر پیشرفته بر روی داده‌ها، از منوی DATA به زیر منوی Sort&Filter رفته و بر روی Advanced کلیک کنید. در پنجره باز شده محدوده داده‌ها را انتخاب کرده و دکمه رادیویی Unique records only را بزنید. OK کنید. اگر می‌خواهید داده‌های اصلی بدون تغییر باقی بماند، دکمه رادیویی Copy to another location را بزنید تا اکسل داده‌هایتان را به جای دیگری کپی کرده و سپس فیلتر نماید.

 

معیار انتخاب ستون های بررسی سطرهای تکراری در اکسل

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

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

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

 

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

 

سطرهایی که داده‌های تکراری دارند را می‌توان در هم ترکیب کرد. بدین معنی که اگر شماره و طول آرماتور ها یکسان است، می‌توان تعداد آرماتورهای این دو سطر را با هم جمع کرده و داده‌های دو سطر را تنها در یک سطر نوشت. در ردیف ۱ آرماتور با شماره ۱۰ و طول ۲۴۸ سانتیمتر با ردیف ۹ تکراری است. تعداد آرماتور ردیف ۱ و ردیف ۹ را جمع کرده (۱۳۵=۲۳+۱۱۲) و کلاً در یک سطر بنویسید.

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

برای انتقال و کپی کردن اطلاعات 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 عدد می باشد.

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

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

 

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

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



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

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

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

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

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

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

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

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

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



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



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



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

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



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

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

 

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

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

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

 

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

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

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



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

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



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

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



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

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

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

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

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

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

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

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



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



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



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



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

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

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

 

نکاتی پیرامون حذف سلول در اکسل

در Excel دو مفهوم براي حذف داريم ، يكي حذف محتويات يا به اصطلاح درست تر پاك كردن محتويات سلول و يكي حذف سلول ، در پاك كردن محتويات فقط محتويات سلول پاك شده و خود سلول برجاي ماند ولي در حذف سلول در حقيقت تكه انتخاب شده به طور كامل حذف مي شود. در اين حالت خود سلول و محتويات آن از بين مي روند حال به توضيح اين در مورد مي پردازيم .

پاك كردن محتويات خانه ها در اکسل :



روش اول :

1- خانه هاي مورد نظر را انتخاب مي كنيم .

2- از منوي Edit ، گزينه Clear را انتخاب مي كنيم .

3- زير منويي باز مي شود كه از آن Content را انتخاب مي كنيم .
 


روش دوم :

1- سلول را انتخاب كرده

2- دكمه del صفحه كليد را مي زنيم .

روش سوم :

1- بر روي سلول كليك راست كرده

2- Clear Content را انتخاب مي كنيم .

حذف سلول  در اکسل :

1- انتخاب سلول هاي مورد نظر

2- انتخاب Delete به يكي از روشهاي زير :

الف – Edit - delete

ب – R.C - delete

3- بايد دقت داشته باشيم در اين مرحله سلول ها حذف مي شوند و در نتيجه به جاي آنها حفره ايجاد مي شود كه بايد سلولهاي اطراف جايگزين اين فضاي خالي شوند. پس براي پر كردن فضاي خالي در اين مرحله متوني ظاهر شده كه شامل گزينه هاي زير است.

الف – Shift Cell Left :

باعث انتقال خانه سمت راست خانه پاك شده به جاي آن مي شود .

ب- Shift Cells Up :

خانه زيرين خانه پاك شده را بجاي آن منتقل مي كند .

ج- Entire Row :

سطر زيرين خانه پاك شده را به جاي سطري كه خانه پاك شده در آن قرار دارد منتقل مي كند.

د- Entire Column :

ستون سمت راست خانه پاك شده را به جاي ستوني كه خانه پاك شده در آن قرار دارد منتقل مي كند .

نكته :

اگر محيط فارسي بجاي ستون سمت راست ، ستون سمت چپ جايگزين مي شود .

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

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

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

1. میانبرهای کیبورد

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

1. |Ctrl + A|: تمام داده های شما را انتخاب می کند؛
2. |Ctrl + C|: تمام داده ها یا بخش مورد نظر شما را کپی می کند؛
3. |Ctrl + V|: داده ها یا بخش مورد نظری که کپی کردید را پیست میکند؛
4. |Ctrl + End|: شما را به آخرین سلول داده ها منتقل می کند؛
5. |Ctrl + Home|: شما را به اولین سلول داده ها منتقل می کند؛
6. |Ctrl + Up Arrow|: شما را به اولین سلول ستون فعال خود منتقل می کند؛
7. |Ctrl + Down Arrow|: شما را به آخرین سلول ستون فعال خود منتقل می کند؛
8. |Shift + Space|: ردیف سلول های فعال شما را انتخاب می کند؛
9. |Ctrl + Space|: ستون سلول های فعال شما را انتخاب می کند؛
10. |Ctrl + minus sign|: به شما گزینه های حذف را ارائه می دهد؛

2. کپی کردن فرمول به سمت پایین در اکسل 

برای سرعت در کپی کردن فرمول کافی است:

1. اشاره گر موس را بر روی دکمه ی پایین گوشه ی سمت راست سلول بیاورید، یک علامت + به رنگ مشکی ایجاد می شود؛
2. دو مرتبه بر روی علامت "+" کلیک کنید؛

 

 

3. کپی کردن ارزش ها یا فرمول هادر EXCEL

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

1. به سلول اولی بروید که قصد کپی کردن یا پر کردن در سلول های دیگر را دارید؛
2. در کادر نام، آدرس آخرین سلول محدوده را تایپ کنید، جایی که قصد دارید اطلاعات یا فرمول خود را در آن وارد کنید و |Shift + Enter| را بزنید؛
3. حال F2 را برای ویرایش فرمول در اولین سلول خود بزنید؛
4. سپس |Ctr + Enter| را بزنید؛

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

 

4.جمع یک ستون یا یک ردیفبرای محاسبه ی سریع اعداد یک ستون یا یک ردیف، کافی است به آخرین سلول مراجعه کنید و |Alt + =| را بزنید.

 

5.حذف ردیف های تکراریبرای حذف ردیف هایی با داده های تکراری، موارد زیر را دنبال کنید؛

1. داده هایی که قصد حذف آن ها را دارید را انتخاب نمایید، می توانید از |Ctrl + A| نیز استفاده نمایید؛
2. بر روی Data menu از نوار منو کلیک نمایید؛
3. بر رویRemove Duplicates کلیک نمایید؛
4. ردیف هایی که قصد حذف آن ها را دارید انتخاب نمایید؛
5. و سپس OK را بزنید؛

 

6. اضافه کردن صفرهای پیش فرض در اکسل

قطعا تا به حال تجربه ی وارد کردن اعداد و ارقام درشت را داشته اید که این کار زمان بر و در عین حال کاری با دقت است. به عنوان مثال ممکن است شما بخواهید عدد 7893 را به صورت 0000007893 وارد نمایید. برای انجام این کار کافی است که از یک فرمول کامل ساده استفاده نمایید. اگر که عدد شما در سلول A1 وجود دارد و شما قصد دارید تا آن را به شماره ای با پیش رقم 0 با حداکثر 10 کاراکتر تبدیل نمایید، این فرمول را درB1 وارد نمایید:=TEXT(A1,REPT("0",10))

 

نکلتی در مورد مقایسه دو لیست از اطلاعات در اکسل

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

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

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

 

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

ابتدا ناحیه 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 کلیک نمایید.

 

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

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

در اکسل برای ایجاد یك فایل جدید یا یك Bookجدید به یكی از روشهای زیر عمل می كنیم .

روش اول : كلیك بر روی آیكون New

روش دوم: Ctrl + N (یك Book خالی ) Blank Work Book – Task Pam ظاهر می شود – New – File

بستن یك Book باز :

روش اول : كلیك بر روی آیكون Close موجود در انتهای نوار منو

روش دوم : File – Close

ممكن است با بستن یك فایل ظاهر شود كه می پرسد می خواهید فایل را ذخیره كنید یا نه اگر No را بزنید فایل را دخیره نمی كند . آن را می بندد. اگر Yes را بزنیم فایل را ذخیره می كند و آن را می بندد كه در درسهای بعد با ذخیره كردن فایل آشنا می شویم .

خروج از محیط Excel :

1- كلیك بر روی آیكون Close موجود ردر نوار عنوان

2- File – Exit

3- Alt+f4
ذخیره كردن Book ایجاد شده
1- به یكی از سه روش زیر Save را انتخاب می كنیم :
الف) Icon Save

ب) File – Save

ج) Ctrl+ S
2- پنجره ای باز شده كه شامل قسمتهای زیر است :

الف ) در سمت چپ صفحه تابلوی دیده می شود كه برای دسترسی سریع به قستهای اصلی Win ، مثل My Computer , My Document, Desktop است. كافی است بر روی گزینه مورد نظر كلیك كرده تا در صفحه سفید رنگ مقابل محتویات آن پوشه را ببینیم .

ب) Save in : در این قسمت مسیر ذخیره مكردن فایل را مشخص می كنیم ، به طور پیش فرض فایلها در My Document ذخیره می شوند .

ج) File Name : نام فایل را تایپ می كنیم . به طور پیش فرض فایلها با اسامی Book1 ، Book2 و … ذخیره می شوند .

د) Create New Folder : ممكن است بخواهیم فایل ها را در پوشه جدیدی قرار دهیم برای این كار ابتدا یا Save in مسیر مورد نظر را انتخاب كرده سپس برروی این آیكون كلیك می كنیم . پنجره ای باز می شود و نام پوشه را می خواهد . نام پوشه را تایپ كرده و Ok می كنیم . پس از انجام تنظیمات بالا بر روی دكمه Save پنجره كلیك می كنیم .

 

ذخیره كردن Book ذخیره شده با نام دیگر  در excel :

در منوی File علاوه بر Save گزینه save as هم وجود دارد. تفاوت این دو گزینه چیست ؟

برای بار اول ذخیره سازی یك Book این در هیچ تفاوتی با هم ندارند. بر روی هر كدام كه كلیك كنیم پنجره Save as باز می شود. ولی برای دفعات بعد (بعد از اینكه Book را ذخیره كردیم) اگر Save را انتخاب كنیم، دیگر پنجره ای باز نمی شود، و تغییرات بر روی فایل با همان نامی كه انتخاب كرده ایم ذخیره می شود ولی اگر Save as را انتخاب كنیم مجدداً پنجره Save as باز شده و می توانیم فایل را با نام دیگر یا در مسیر دیگر یا با نام دیگر و در مسیر دیگر ذخیره كنیم.

تذكر : وقتی فایلی را با نامی ذخیره می كنیم این فایل در نوار عنوان ظاهر می شود.

ذخیره كردن Book روی دیسكت:

برای ذخیره كردن یك فایل بر روی دیكست باید در پنجره Save as ، در قسمت Save in ، :3.5 floppy A را انتخاب كنیم .

ذخیره كردن Book با فرمت مناسب برای صفحات وب :

اگر بخواهیم فایل Excel را با فرمتی ذخیره كنیم كه مانند یك صفحه وب باشد و بتوان آن را در Browser دید، كافی است مراحل زیر را انجام دهیم:

1- انتخاب منوی File

2- انتخاب گزینه as a web page save

نكته :

در این حالت فایل با پسوند .htm ذخیره می شود .
بازسازی  Recovery Documentدر اکسل
اگر Excel با مشكلی مواجه شود، در صدد ذخیره كردن كارپوشه های باز در هنگام برخورد با مشكل بر می آید در این حالت اگر دوباره اكسل را شروع كنیم پنجره Document Recovery باز شده و كلیه كارپوشه هایی كه باز بوده اند را نمایش می دهد. كافی است بر روی كارپوشه مورد نظر كلیك كرده تا باز شود. در مقابل نام كار پوشه دو عبارت ممكن است دیده شود :
1- [Recover] : اگر این عبارت نشان داده شده باشد به این معناست كه Excel موفق به ذخیره كردن فایل شده است .

2- [original] اگر این عبارت نشان داده شده باشد به این معناست كه این فایل ، فایل با آخرین نسخه ذخیره شده است. و Excel در هنگام بروز مشكل آن را ذخیره نكرده است زیرا یا بعد از آخرین ذخیره ، تغییری نكرده است و یا تغییرات انجام شده ولی نتوانسته ذخیره كند.

تذكر : ممكن است با هر دو عنوان فایل دیده شود می توانیم حالت Recovery را باز كرده و با فایل اصلی مقایسه كنیم و سپس ذخیره كنیم .
باز كردن صفحه ذخیره شده  در اکسل
می خواهیم فایلی را كه قبلا ذخیره كرده ایم دوباره بیاوریم برای این كار :
1- پنجره Open را به یكی ار روشهای زیر باز می كنیم :

الف : Icon Open

ب: File – Open

ج: Ctrl + o

2- پنجره ای باز می شود كه پنجره Openبوده و مشابه پنجره save بوده و شامل قسمتهای زیر است:

الف : Look in در این قسمت مسیری كه فایل را در آن ذخیره كرده ایم انتخاب می كنیم .

ب: Icon View : كلیك بر روی این Icon منویی ظاهر می شود كه حالتهای مختلف نمایش فایلها و پوشه های موجود در مسیر انتخاب شده را نمایش می دهد و شامل حالت های زیر است :

1- Thumbnails : این حالت مناسب فایلهای تصویری بوده و تصویر را نمایش می دهد .

2- Tiles : پوشه ها و فایلهای موجود را به صورت كاشی وار در دو ستون نمایش می دهد .

3- Icons : پوشه ها و فایلهای موجود را در 6 ستون نمایش می دهد.

4- List : پوشه ها و فایلهای موجود را در 15 سطر نمایش می دهد .

5- Details : پوشه ها و فایلهای موجود را با جزئیاتشان نمایش می دهد . در حالتهای قبل فقط نام فایلها نمایش داده می شود ولی در این حالت علاوه بر نام فایل ، سایز آن ، نوع و تاریخ ویرایش آن نمایش داده می شود .

6- Properties : با انتخاب این گزینه پنجره ی Open به دو تابلو تقسیم شده در تابلو سمت چپ نام فایل و پوشه ها و در تابلو سمت راست خصوصیات فایل مثل شخص ایجاد كننده آن ، تاریخ تغییرات ، نام كمپانی و … نمایش داده می شود .

7- Preview : پیش نمایش از فایل ، نمایش داده می شود .

نكته :

برای بعضی از فایلها با انتخاب این گزینه در تابلو سمت راست پیام Preview not available نمایش داده می شود . برای رفع این مشكل باید بر روی فایل باز عملیات زیر را انجام دهیم :

File – Properties – انتخاب Tab: Summary – Save preview picture – ok

پس از انجام عملیات بالا ، مجددا برروی آیكن Save ، كلیك كنید .

تذكر : در صورتی كه بخواهیم هر روز از كار پوشه ای استفاده كنیم و بخواهیم به طور خودكار با باز كردن Excel این كار پوشه باز شود و كافی است آن را در پوشه XL start قرار دهیم. این پوشه در هنگام نصب Excel ساخته می شود و مسیر آن یكی از دو مسیر زیر است .

C:/document and setting\ئی كه در آن هستیم User \application data \ یا

Microsoft \excel\ XL start

C:\program file\ Microsoft office\office ll \ XL Start

نکاتی در مورد تابع OFFSET در اکسل

 

اگر بخواهیم با عملکردتابع Offset بیشتر آشنا شویم باید بیان کردکه  آدرس یک سلول رابه تعدادی که مشخص میکنید محدوده داده اید را به شما نشان مید هد  .

برای آشنایی بیشتر با ما همراه باشید .

آرگومانهای فرمول   Offset در اکسل  به شرح زیر است :

 

Reference

آدرس سلول مبدأ را میخواهد.

Rows

سوال می کنداز چند تا سطر بالاتر یا پائینتر از آدرسی که در آرگومان اول داده اید  محاسبه کند .مثلا اگر آرگومان اول B5 را انتخاب کنبد و در آرگومان دوم عدد ۲ را وارد کنید نقطه شروع  را B7 در نظر میگیرد و اگر -۲ قرار دهید دو سطر به عقب بر می گرده و B3 راملاک قرار میدهد  .

Cols

اگر آرگومان اول B5 را انتخاب کنبد و در آرگومان دوم عدد ۰ را وارد کنید و آرگومان سوم را ۱ قرار دهید نقطه شروع شما به C5 تغییر میکند

یعنی اکسل از C5 شروع به محاسبه میکند اگر هم عدد منفی قرار دهید به  ستون عقبتر  و از A5 محاسبه میکند 

نکته:3 آرگومان اول اجباری است و حتما باید مفدار را وارد  کنید و میتوانید آرگومان های بعدی را براساس  قرار دهید.

 Height در اکسل

 ارتفاع داده ای را مشخص میکند   یعنی اگر ما آرگومان اول را A1 انتخاب کنیم و آرگومان دوم و سوم را ۰ قرار دهییم و آرگومان چهارم که ارتفاع است را عدد ۳ در نظر بگیریم .  اکسل رنج A1:A3 را در نظر میگیرد و مقدارهایش رو برمی گرداند .

Width

مشابه آرگومان چهارماست ولی در عرض محاسبه میکند
OFFSET(A1,0,0,0,3)

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

اکسل پیرامونمشخص کردن سطرهای تکراری در اکسل با یک ترفند ئ ساده و جالب به کار میبرد .

با ما همراه باشید

ویژگی Conditional Formatting در اکسل این امکان را به شما می دهد که بتوانید مقادیر تکراری را در یک محدوده با رنگ دلخواه مشخص کنید.

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

 

برای انجام این کار باید کمی فرمول نویسی کنید !

ابتدا محدوده لیست را انتخاب کنید و سپس در روبان Home گزینه Conditionam Formatting را باز کرده و گزینه New Rule را انتخاب کنید.

سپس در پنجره باز شده گزینه آخر (Use a formula to ) را انتخاب کرده و فرمول زیر را در آن بنویسید :

=COUNTIFS($B$3:$B$19;$B3;$C$3:$C$19;$C3;$D$3:$D$19;$D3)>1

 

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

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

 

نکاتی در مورد جابه جایی ، صفحه بندی و چاپ در اکسل

 

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

1-سلول یا منطقه ی مورد نظررا انتخاب می کنید .

2-در نوار ابزار اکسل ،روی نشانه برش (cut)،کلیک می کنید در این هنگام به دور منطقه ی انتخاب شده مرز متحرک ظاهر می شود که نشان میدهد ،منطقه ی مورد نظر بریده شده است.  

 

 

بریدن و چسباندن در اکسل   

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

1-سلول یا منطقه ی مورد نظررا انتخاب می کنید .

2-در نوار ابزار اکسل ،روی نشانه برش (cut)،کلیک می کنید در این هنگام به دور منطقه ی انتخاب شده مرز متحرک ظاهر می شود که نشان میدهد ،منطقه ی مورد نظر بریده شده است.  

 

 

 

3-در سلول مورد نظر برای انتقال کلیک کنید تا انتخاب شود.

4-روی نشانه چسباندن (paste) در نوار ابزار اکسل ،کلیک کنید.منطقه ی سلول مورد نظر،در مکان جدید چسبانده واز جای قبلی ناپدید می شود. 

 

کپی کردن در اکسل   

 

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

برای این کار، به روش زیر عمل کنید:

1-سلول یا منطقه مورد نظر را انتخاب کنید.

2- در نوار ابزار اکسل، روی نشانه برش (copy)، کلیک کنید. در این هنگام، به دور منطقه انتخاب شده مرز متحرکی ظاهر میشود که نشان میدهد منطقه مورد نظر، در تخته برش(clipboard) ویندوز ، قرار داده شده است. 

  

 

  

 

3- دور سلول مورد نظر برای انتقال ، کلیک می کنید تا انتخاب شود.

4- روی نشانه چسباندن در نوار ابزار کلیک کنید تا یک نسخه از محتویات منطقه انتخاب شده در مکان جدید قرار داده شود. داده های اصلی دست نمی خورند و سر جای خود می مانند.  

 

مرجع مطلق و مرجع نسبی در اکسل   

    

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

      در اکسل دو نوع مرجع سلول وجود دارد: مرجع نسبی(relative reference) و مرجع مطلق (absolute reference). وقتی فرمولی را از یک سلول به سلول دیگر کپی کنید، متوجه تفاوت این دو مرجع خواهید شد. برای مشاهده این تفاوت تمرین زیر را انجام دهید.

 

   

 

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

برای اینکه مرجع مطلق رابه اکسل بشناسانیم بایداز علامت (($))استفاده کنیم.

علامت دلاررا درنشانی سلول یامنطقه،می توان پیش ازحرف نشانی،پیش از

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

نتیجه فرق خواهدکرد.جدول زیرتفاوت هارا نشان می دهد.

 

 

 

اگرفرمول مثلابه این شکل واردشود	وقتی فرمول به جای دیگری کپی شوداتفاق زیرمی افتد.
C1	هم ستون وهم سطرعوض می شود.
C1$	ستون عوض نمی شودولی عرض عوض می شود.
C$1	ستون عوض می شودولی سطرعوض نمی شود.
C$1$	ستون وسطر،هیچ یک عوض نمی شوند.

 

 

 

 

 

شمامی توانید درحین وارد کردن نشانی سلول یامنطقه،مستقیماّ علامت $راتایپ کنیدویا می توانید پس ازتایپ آن،با زدن کلیدf4نشانی رابه صورت مطلق درآورید.

 

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

 

 

 

در این جدول سطر هشتم جمع سطرهای بالا و ستون E جمع ستون های سمت چپ آن است.

 

بنابراین،کافی است فروش های فروردین،اردیبهشت و خرداد را وارد کنیدو از  طریق تابع SUM،سطر 8 و ستون E،را پر نمایید.

 

2-سلول B8 را در سلول B9،سلول C8 را در سلول C9 و سلول D8 را در سلول D9 کپی کنید.چه اتفاقی افتاد؟

 

3-روی سلول B9 دو بار کلیک کنید.آیا منطقه ی تابع جمع آن درست است؟روی سلول C9 و D9 هم دو بار کلیک کنید.آیا منطقه ی تابع جمع آن ها درست است؟چرا غلط است؟

 

سلول های B9،C9،D9 را پاک کنید.

 

4-اکنون روی سلول B8 کلیک کنید و در نوار فرمول، کل فرمول آن را انتخاب کنید.کلید F4 را فشار دهید.در نوار فرمول چه تغییری در فرمول مشاهده می کنید.چند بار کلید F4 را بزنید و تغییرات فرمول را مشاهده کنید.اکنون به قدری کلید F4 را بزنید تا فرمول به شکل کاملاً مطلق =SUM($B$5:$B$7) درآید.حالا کلید ENTER را بزنید.

 

5-اکنون سلول B8 را در سلول B9 کپی کنید.محتوای سلول B9 را با B8 مقایسه نمایید،نتیجه را شرح دهید.

 

6-روی سلول B9 دو بار کلیک کنید.منطقه یاب کدام منطقه را نشان می دهد؟حال روی سلول B8 دو بار کلیک کنید.منطقه یاب کدام منطقه را نشان می دهد؟علت چیست؟

 

7-فرمول های موجود در سلول های C8 و D8 را هم نسبی و در سلول های C9 و D9 کپی کنید.اگر این عمل را درست انجام دهید،محتوای سلول های بالا،با سلول های پایین باهم برابر خواهند بود.فایل را برای استفاده ی بعدی ذخیره کنید.اگر تمام فرمول را انتخاب کنید،F4 روی تمام آن عمل می کند اما اگر بخشی از فرمول را برگزینید،F4 روی همان بخش انتخاب شده عمل می کند.  

 

 

 

 

 

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

 

 دستگیره ی پر کردن در اکسل    

 

 

 

یک راه دیگر برای کپی کردن داده های یک سلول در سلول های دیگر این است که دستگیره ی پر کردن آن را با ماوس بگیرید و به هر تعداد سلول که می خواهید،بسط دهید.طرز استفاده از دستگیره ی پر کردن را قبلاً دیده ایم.با تمرینی،مطلب را خواهیم آموخت:

 

1 – در تمرین قبل ، در سلول B3 ، کلمه فروش را تایپ کنید که با سلول پایین آن فروش فروردین خوانده شود .

 

2 – اکنون دستگیره ی پرکردن سلول B3 را بکشید و تکمه ی ماوس را رها کنید . چه اتفاقی افتاد ؟ شرح دهید .   

 

 

 

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

 

 ( Drag & Drop)در ویرایش داده ها  

 

 

 

یکی از راههای دیگر انتقال داده ها در کاربرگ،به کار گرفتن عمل «کشیدن و رها کردن»داده ها با ماوس است.در این عمل،نقش اصلی را با ماوس بر عهده دارد.

 

برای جابه جا کردن داده ها (مشابه بریدن و چسباندن)باید به طریق زیر عمل کنید:

 

1-منطقه مورد نظر را با ماوس یا صفحه کلید،انتخاب کنید.

 

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

 

3-مکان نما را حرکت دهید و به جایی که می خواهید داده ها به آن منتقل سشوند بیاورید و کلید ماوس را رها کنید.

 

برای کپی کردن همان منطقه به جای جدید،فقط کافی است که در مرحله 3،به هنگام حرکت مکان نمای ماوس،کلید Ctrl را نیز پایین نگه دارید.

 

سلول هایی که با شیوه کشیدن و رها کردن منتقل یا کپی می کنید، باید به هم چسبیده باشند،یعنی مناطق گسسته را با این روش نمی توانید حرکت دهید. 

 

 

 

 

 

  

 

 

 

 

 

 

 

 

 

«لغو کردن» [Undo] و «انجام مجدّد 

 

 عمل [Redo] در اکسل  

 

 

 

اکسل ، هر کاری را که شما انجام می دهید، یادداشت می کند. بنابراین همیشه می داند که در اثر کدام عمل شما ،چه تغییری در کار برگ حاصل شده است.پس،همیشه می توانید آخرین عمل خود را لغو کنید و به محض انجام کار، همه چیز به یک مرحله قبل بر میگردد.به این خصوصیّت اکسل «لغو عمل»[Undo] می گویند.

 

اما اکسل،هر عملی را که شما لغو کنید دور نمی اندازد و فراموش نمی کند ؛ بلکه آن را در جای دیگری یادداشت می کند.

 

بنابراین ، اگر شما پشیمان شدید و خواستید عملی را که لغو کرده بوده اید دوباره انجام دهید،می توانید این کار را از طریق خصوصیّت«انجام مجدّد عمل»[Redo] صورت دهید. نشانه های «لغو عمل» و«انجام مجدّد عمل» ،در نوار ابزار استاندارد اکسل قرار گرفته است. 

 

 

 

 

 

  

 

    

 

در منوی Edit هم فرمان UndoوRedo وجود دارد. 

 

    

 

 

 

  

 

 

 

چاپ   

 

اکسل می تواند به سادگی کاربرگ شما را چاپ کند . اما همیشه توصیه می شودذ پیش از چاپ چیزی را که میس خواهید چاپ شود ، پیش نگری (Preview) کنید .

 

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

 

برای پیش نگری چاپ  با ماوس روی کلید « پیش نگری » یا همان Preview print در نوار ابزار استاندارد کلیک کنید . بالافاصله کاربرگ به صورتی که چاپ خواهد شد در   صفحه ، نمودار خواهد شد .     

 

 

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

 

 

 

  

 

 

 

در بالای پنجره پیش نگری چند نشانه ی دیگر هست که بر نحوه ی چاپ یا پیش نگری ، تاثیر می گذارد . و بدین شرح اند .  

 

  نشانه ی Next ( صفحه بعدی ) : با کلیک کردن روی آن ، به صفحه ی قابل چاپ بعدی خواهیم رفت .

 

  نشانه ی Previous    ( صفحه ی قبلی ) :  با کلیک کردن روی این نشانه ، به صفحه ی قابل چاپ قبلی خواهیم رفت .

 

  نشانه ی  Margins  ( حاشیه ها ) : با کلیک کردن روی این نشانه ، خطوط حاشیه ی صفحه چاپ و نیز فاصله های جدول چاپی را می توانید تنظیم کنید . خطوط و علایمی کهذ پس از زدن این کلید ، در صفحه چاپی ظاهر می شوند ، همگی با استفاده از ماوس جابه جا می گردند در این روش می توانید تنظیم های گوناگونی به عمل آورید .

 

  نشانه ی Page break preview ( پیش نگری مجزا شدن صفحات ) : با کلید زدن روی این نشانه ، نمای کوچک شده ای از کاربرگ ، ظاهر می شود که خطوط برش  چاپ در آن ترسیم شده است . و صفحات از هم مجزا شده اند . در زمینه ی صفحه هم شماره صفحه گذاشته شده است. 

 

  نشانه   Close  ( بستن ) : این نشانه پنجره ی پیش نگری چاپ را می بندد .

 

  نشانه ی Help   ( کمک ) : با زدن این نشانه ، امکسل در زمینه ی چاپ به شما کمک می کند .  

 

 

 

 

 

مشخص کردن نحوه ی چاپ در اکسل

 

 

 

یکی از نشانه های منوی پیش نگری preview، که Page set up   است ، منویی با همین نام را بر صفحه ظاهر می کند که از طریق آن نیز برخی از پارامترهای چاپ ، تعیین می شود . در اینجا به آن منو پرداخته می شود .   

 

 

 

تعیین جهت صفحه:

 

با کلیک کردن روی عنوان portrait، صفحه چاپ به شکل ایستاده خواهد بود. اگر روی عنوان landscape کلید بزنید ، صفحه ، در جهت خوابیده چاپ خواهد شد. 

 

 

 

 

 

 

 

تعیین دقیق حاشیه های چاپ:

 

دیدیم که میتوان خطوط تعیین حاشیه ها را با ماوس حرکت داد و حاشیه های چهارگانه صفحه را مشخص کرد ، اما این کار زیاد دقیق نیست. با این همه اگر میخواهید این حاشیه ها را به دقت تعیین کنید ، زبانه دوم منویpage set up یعنی زبانه margins(حاشیه ها) را فعال کنید تا منو به شکل زیر در آید.  

 

 

 

  

 

 

 

اکنون میتوانید، اندازه حاشیه ها را در منوهای با عنوان top(بالا)،bottom (پایین)،left(چپ)،right(راست) تعیین کنید. اندازه حاشیه سر صفحه و پا صفحه را نیز میتوانید با تعیین عدد دقیق در زیر عنوان header(سر صفحه) وfooter (پا صفحه) تعیین کنید. 

 

 

 

 

 

  

 

وسط در وسط کردن منطقه چاپی:  

 

 

 

وقتی چیزی در صفحه چاپ میشود از سمت راست حاشیه چپ تراز میشود. اگر میخواهید فاصله راست و چپ چیزی که چاپ میشود از گوشه راست و چپ صفحه به یک فاصله باشد، در قسمت center on pageاز زبانه margins ، گزینه horizotally(به طور افقی) را انتخاب کنید. اگر میخواهید فاصله بالا و پایین صفحه با آنچه در آن چاپ میشود مساوی باشد، در همین زبانه ، از منو گزینه vertically (به طور عمودی) را انتخاب کنید. بلافاصله در شکل کوچک موجود در منو ، تاثیر این انتخاب ها دیده خواهد شد و در صفحه چاپی نیز ، به هنگام چاپ اعمال خواهد شد.  

 

 

 

 

 

 

 

تعیین سر صفحه و پا صفحه چاپی در اکسل: 

 

 برای تعیین سر صفحه و پا صفحه چاپی در منوی page set up ، زبانه header/footer را فعال میکنیم .  

 

 

 

  

 

 

 

همان طور که ملاحظه می کنید دو خانه سفید خالی در بالا و پایین منو داریم که نماینده سر صفحه و پا صفحه چاپی هستند و باز، همان طور که مشاهدا میکنید ، در خانه زیر عنوان header(سر صفحه) و در خانه زیر عنوانfooter (پا صفحه) کلمهnone (هیچ چیز) به چشم میخورد. حالا اگر منوی footer یاheader را ، از طریق کلید زدن روی کلید پیکان دار سمت راست آن، فعال کنید، گزینه هایی را میبینید که خود اکسل، برای سر صفحه  یا پا صفحه پیشنهاد می کند و با انتخاب هر کدام،آنها بلافاصله مورد استفاده قرار می گیرند .از آن جملهد اند:شماره ی صفحه ،شماره ی صفحه وتعداد کل صفحات نام فایل و شماره ی صفحه وغیره.

 

شما می توانید یکی از این گزینه ها را انتخاب کنید ویا،ااگر هیچ یک را نپذیرفتید،خودتان سر صفحه وپا صفحه تیین کنید.برای این کار،دو کلید در این منو موجود است که یکی CUSTOM HEADER (سر صفحه ی سفارشی)ودیگریCUSTOM FOOTER (پا صفحه سفارشی)ست.هر یک از این دو کلید را بزنید،منوی مشابهی ظاهر خواهد شد که فقط در نام متفاوت است

 

  

 

 

 

اکسل ،هر یک از سر صفحه و پا صفحه را به سه منطقه ی مجزا تقسیم می کند،که با نام ها ی LEFT SECTION (بخش وسط)و RIGHT SECTION (بخش راست)در منو، مشخص می شوند.در خانه های سفید پایین هریک ازاین عنوان ها میتوان چیز های متفاوتی گذاشت.

 

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

 

 

از چپ به راست ،این نشانه ها به شرح زیر عمل می کنند:

 

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

 

 کلیک کردن روی این نشانه ،باعث می شود که کد &(PAGE) به سر صفحه یا پا صفحه ی شما افزوده شود. اکسل،به جای این کد شماره ی صفحهد چاپی می گذارد.

 

 کلیک کردن روی این نشانه ،باعث می شود،کد &(PAGE) به سر صفحه یا پا صفحه ی شما افزوده شود.اکسل به جای این کد،تعداد کل صفحات چاپی را می گذارد.

 

 کلیک کردن روی این نشانه ،به سر صفحه یا پا صفحه ی شما ،کد &(DATE) را می افزاید و در چاپ باعث می شود که تاریخ روز به جای آن چاپ شود.

 

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

 

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

 

با زدن این نشانه ،کد &(TAB) به سر صفحه یا پا صفحه افزوده می شود ودرهنگام چاپ ،نام کاربرگ به جای آن چاپ می شود.  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  تکرار سطر ها و ستون ها در نسخه  های  چاپی  در اکسل

 

 

 

بیشتر وقت ها، کاربرگ بزرگ است ودر چند صفحه کنار هم چاپ می شود.در این مواقع،ممکن است بخواهید برخی سطر ها و ستونها در تمام نسخه های چاپی از کلیه ی مناطق کاربرگ چاپ شود. برای این کار از زبانه ی چهارم منوی page setup که زبانه ی sheet(صفحه ) است استفاده می کنید .  

 

 

 

 

 

 

 

در این منو بخشی بنام print titles وجود دارد که در شکل بالا مشخص است .دراین بخش دو گزینه داریم که یکی rowz to repeat at top(سطرهایی که در بالا تکرار می شوند) و دیگریcolumns to repeat at edge(ستونهایی که در لبه تکرار می شوند )است.

 

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

 

1-پس از فعال کردن این بخش از منو در خانه ی مقابل عنوان rowz to repeat at top[ کلیک میکنید تا فعال شود .

 

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

 

3-در این منو روی سطر هایی که میخواهید در بالای هر صفحه ی چاپی تکرار شود کلیک می کنید.میتوانید یک یا چند سطر را انتخاب کنید.

 

4-اکنون روی تکمه کناری این منو که در شکل بالا نشان داده شده است کلید می زنید تا کل منو دوباره ظاهر شود

 

5-برای گزینه ی columns to repeat at edge نیز به همین ترتیب کار میکنید .

 

6-با استفاده از print preview حاصل کار را می بینید و اگر مناسب تشخیص دادید چاپ میکنید . 

 

 

 

 

 

 

 

مشخص کردن ترتیب چاپ

 

 

 

وقتی یک کاربرگ بزرگ داشته باشید میتوانید دستور بدهید که اکسل این کاربرگ بزرگ را به چه ترتیبی قطعه قطعه و چاپ کند . برای این کار:

 

1-      در منوی page set up زبانه ی sheetرا فعال میکنید .

 

2- در بخشpage order دو انتخاب است:اگر مقابل گزینه یdown then over(اول ستون ،بعد سطر )را بزنید ، ابتدا کاربرگ به شکل عمودی می برد و بعد ،هر ستون را تکه تکه چاپ میکند و به سراغ ستون بعد می رود .حال ،اگر گزینه ی،over then down(اول سطر ،بعد ستون ) را انتخاب کنید ، کاربرگ را به شکل افقی تکه تکه میکند و بعد از چاپ هر تکه ،ان را جداگانه چاپ می کند. 

 

 

 

 

 

 

 

چاپ منطقه ی خاصی از یک کاربرگ در اکسل     

 

 

 

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

 

1 – در منوی page set up زبانه ی sheetرا فعال کنید .

 

2 – حالا در خانه ی مقابل عنوان print areae ( محدوده ی چاپ ) را کلیک کنید تا فعال شود . 

 

3 – روی نشانه ی پنهان کردن موقت منو ، در کگوشه ی راست ، خانه ی کذکور ، کلیک کنید تا منو موقتا پنهتان شود  .

 

4 – حالا منطقه ی مورد نظر را با ماوس یا صفحه کلید ، انتخاب کنید و مجدداً  روی همان نشانه کلیک کنید .

 

5 – با زدن نشانه ی   Preview print نتیجه را ببینید .

 

وارد کردن لیست های فارسی (ایام هفته و نام ماه ها) در نرم افزار اکسل(Excel)

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

وارد کردن لیست های فارسی (ایام هفته و نام ماه ها) در نرم افزار اکسل(Excel)

برای وارد کردن لیست های مورد نظر ابتدا آنها را در یک ستون وارد نموده و آنها را انتخاب می کنیم و از قسمت File گزینه Option و سپس گزینه Advance را انتخاب می نماییم و در پنجره ظاهر شده در سمت راست روی گزینه Edit custom list کلیلک می نماییم.

درپنجره ظاهر شده روی گزینه Import کلیلک کرده ، گزینه های وارد شده در کاربرگ درقسمت List entries ظاهر می شود و سپس روی گزینه OK کلیلک می نماییم.

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

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

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

نکاتی ارزنده پیرامون نحوه کار با سلولها در اکسل

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

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

ویرایش سطر و ستون در اکسل

اضافه كردن سطر :

روش اول :

۱- بر روی سطری كه می خواهیم سطر جدید قبل از آن اضافه شود ، كلیك می كنیم .

۲- منوی Insert را انتخاب می كنیم .

۳- گزینه Row را انتخاب می كنیم .

روش دوم:

۱- بر روی سطری كه می خواهیم سطر جدی قبل از آن اضافه شود كلیك راست می كنیم .

۲- از منوی باز شده Insert را انتخاب می كنیم .

نكته :

سطرهای زیرین به پایین رانده می شوند .

اضافه كردن ستون :

روش اول:

۱- بر روی ستونی كه می خواهیم ستون جدید قبل از آن اضافه شود ، كلیك می كنیم .

۲- منوی Insert را انتخاب می كنیم .

۳- گزینه Column را انتخاب می كنیم .

روش دوم :

۱- بر روی ستونی كه می خواهیم ستون جدید قبل از آن اضافه شود راست كلیك می كنیم .

۲- گزینه Insert را انتخاب می كنیم .

نكته : ستون قبلی به جلو رانده می شود.

اصطلاح : از Insert Row برای اضافه كردن سطر و از Insert Column برای اضافه كردن ستون استفاده می شود .

تغییر ارتفاع سطر:

برای تغییر ارتفاع سطر به دو روش می توان عمل كرد :

روش اول:

كافی است به مرز بین سطر بعدی رفته تا مكان نما به صورت یك فلش دو سر درآید . سپس مرز سطر را به محل مورد نظر Drag می كنیم . در حین Drag كادر كوچكی ظاهر می شود كه اندازه سطر را نشان می دهد .

روش دوم:

در این روش ارتفاع به اندازه دقیق تنظیم می شود . برای این كار مراحل زیر را انجام می دهیم :

۱- انتخاب منوی Format
۲- انتخاب زیر منوی Row

۳- انتخاب گزینه Height

۴- در كادر باز شده اندازه دلخواه را وارد می كنیم .

نكته : به طور پیش فرض ارتفاع سطر ۱۲.۷۵ می باشد .

تغییر پهنای ستون :

برای تغییر پهنای ستون به یكی از دو روش زیر عمل می كنیم :

روش اول :

كافی است به مرز بین ستون مورد نظر و ستون بعدی رفته تا مكان نما به صورت فلش دو سر درآید سپس مرز ستون را در جهت مورد نظر Drag می كنیم .

روش دوم:

در این روش پهنا را به صورت دقیق تنظیم می كنیم . برای این كار مراحل زیر را طی می كنیم :

۱- انتخاب متوی Format

۲- انتخاب زیر منوی Column

۳- انتخاب گزینه Width

۴- در كادر باز شده مقدار دلخواه را وارد می كنیم .

نكته :

به طور پیش فرض پهنای ستون ۸.۴۳ می باشد .

تغییر پهنای استاندارد ستون :

می دانیم كه پهنای استاندارد ستونها ۸.۴۳ می باشد اگر بخواهیم این پهنای استاندارد را كم یا زیاد كنیم به ترتیب زیر عمل می كنیم :

(فقط به یاد داشته باشید این تغییر بر روی ستونهایی اعمال می شود كه پهنای آنها را تغییر نداده باشیم .)

۱- انتخاب منوی Format

۲- انتخاب زیر منوی Column

۳- انتخاب گزینه standard Width

۴- در كادر باز شده پهنای باند را وارد می كنیم .

مخفی كردن ستون یا سطر:

می توانیم یك سطر یا ستون را مخفی كنیم تا محتویات آن و كلا خود سطر مشخص نباشد. برای این كار مراحل زیر را طی می كنیم :

۱- انتخاب منوی Format

۲- انتخاب زیر منوی Row (برای سطر) یا Column (برای ستون)

۳- انتخاب گزینه Hide

نمایان ساختن سطر یا ستون مخفی شده :

اگر بخواهیم سطر یا ستون مخفی شده را از حالت مخفی خارج كنیم مراحل زیر را انجام می دهیم :

۱- انتخاب سطر (ستون) قبل و بعد از سطر (ستون) مخفی شده

۲- انتخاب منوی Format

۳- انتخاب زیر منوی Row (برای سطر) یا Column (برای ستون)

۴- انتخاب گزینه Unhide

تغییر اندازه عرض ستون به اندازه اطلاعات داخل آن در اکسل :

ممكن است اطلاعاتی كه در سلول تایپ می كنیم از عرض سلول بیشتر باشد و در هنگام تایپ ظاهرا سلول بعدی را می كیرد . برای رفع این مشكل مراحل زیر را اانجام می دهیم .

۱- كلیك بر روی سلولی كه می خواهیم عرض ستون به اندازه اطلاعات داخل آن شود.

۲- Format

۳- Column

۴- Auto fit selection

قالب بندی سلولها :

در Excel این توانایی را داریم كه قالب بندی یا فرمت یك سلول را تغییر داده و فرمت آن سلول را مثلاً تاریخ یا درصد یا زمان یا متن و یا … كنیم . همچنین می توانیم دور سلول كادر بیندازیم یا رنگ زمینه آن را عوض كنیم . برای قالب بندی خانه ها در Excel مراحل زیر را طی می كنیم .

روش اول :

۱- انتخاب متوی Format

۲- انتخاب گزینه Cell

روش دوم:

۱- انتخاب سلولهای مورد نظر

۲- راست كلیك روی سلولهای انتخابی

۳- انتخاب گزینه format cells

با اجرای یكی از دو روش بالا پنجره ای باز می شود كه دارای tab های زیر است :

الف – Number :

توسط این tab می توانیم اطلاعات ورودی را تعیین كنیم . این Tab شامل گزینه های زیر می باشد :

– Sample : هر فرمتی را كه انتخاب كنیم ، بر روی محتوای سلول انتخابی نمایش می دهد . ( حالت پیش نمایش را دارد)

– Category : در این قسمت نوع داده را مشخص می كنیم كه شامل انواع زیر می باشد :

۱- Genera: این گزینه عددها را به صورت رشته ای از رقمهای متوالی و بدون هرگونه قالب بندی نشان می دهد. و اگر عددی در سلول جا نشود آن را به صورت نمایی نشان می دهد .

۲- Number : مقادیر را بصورت رشته ای از رقمهای متوالی نشان می دهد . در این حالت اگر عدد در سلول نگنجد سلول بزرگترمی شود . همچنین در این قسمت می توانیم تنظیمات زیر را انجام دهیم :

– Decimal Places : در این قسمت می توانیم تعداد ارقام بعد از اعشار را تعیین كنیم . (به طور پیش فرض تا ۲ رقم اعشار نمایش می دهد)

– Negative Number : در این حالت می توانیم مشخص كنیم كه عدد منفی به همان صورت نشان داده شود یا به رنگ قرمز یا سیاه با علامت منفی نشان داده شود . یا به رنگ قرمز بدون علامت منفی نشان داده شود . (توجه داشته باشید كه این گزینه فقط روی اعداد منفی عمل می كند)

– Use 1000 separator : اگر در كنار این گزینه تیك بگذاریم اعداد را سه رقم ، سه رقم از سمت راست جدا كرده و علامت (،) می گذارد.

۳- Currency : مقادیر را همراه با سمبل پول رایج نشان می دهد. در این حالت می توان تنظیمات زیر را انجام داد :

– Symbol : نوع واحد پول را مشخص می كنید . در این قسمت واحد پول كشورهای مختلف نمایش داده شده و می توانیم واحد دلخواه خود را انتخاب كنیم .

– Negative Number : اعداد منفی چطور نمایش داده شوند .

Accounting: مقادیر صفر به صورت خط تیره (-) نمایش داده می شوند .

در Currency قالب بندی اعداد منفی را داریم

۴- Accounting : مانند حالت Currency است . این قالب بندی حسابداری می باشد و تفاوتهایی با حالت Currency دارد كه عبارتند از :

* در حالت Accounting علامت پول رایج در منتها علیه سمت چپ سلول و عدد در سمت راست سلول نمایش داده می شود . در نتیجه پول همیشه در یك ستون عمودی و در هم قرار می گیرد كه در مقایسه با Currency ، اعداد خواناتر و ظاهر آنها مرتب تر است . Accounting مقادیر صفر به صورت خط تیره (-) نمایش داده می شوند.

۵- Date : تاریخ را با قالب بندی خاص تاریخ نشان می دهد و ما می توانیم انواع قالب بندی های تاریخ را دیده و انتخاب كنیم . مثلا مدلی را انتخاب كنیم كه فقط روز و ماه را نشان دهد و یا مدلی را انتخاب كنیم كه روز را به عدد و ماه را به حروف نشان دهد .

۶- Time : زمان را با قالب بندی های خاص زمان نشان می دهد . انواع قالب زمانی در این قسمت وجود دارند . ما می توانیم مدلی را انتخاب كنیم كه ساعت را از ۱ تا ۱۲ با برچسب صبح و بعداز ظهر نمایش دهد و یا مدلی را انتخاب كنیم كه ساعت را از ۱ تا ۲۴ نمایش دهد و یا …

۷- Percentage : عددها را همراه با علامت درصد نشان می دهد .

نكته : در تمام قالب بندی ها بجز Percentage فرقی نمی كند كه ابتدا اطلاعات را در سلول وارد كنیم یا اول قالب بندی را تنظیم كنیم . ولی در حالت percentage اگر ابتدا عدد را نوشته و سپس فرمت را Percentage كنیم ، عدد را در ۱۰۰ ضرب می كند. ولی اگر ابتدا فرمت را Percentage كنیم ، و بعد عدد را بنویسیم ، تغییری در عدد نمی دهد .

۸- Fraction : مقادیر را به صورت عدد صحیح نشان می دهد كه بدنبال آن نزدیك ترین كسر به مقدار واقعی ظاهر می شود . برای نوشتن یك عدد مخلوط كافی است ابتدا قسمت صحیح را نوشته سپس یك فاصله بدهیم و بعد كسر رات نوشته و بعد علامت (/) را بگذاریم و بعد مخرج كسر را بنویسیم . اگر عدد را بنویسیم ، پس از enter كردن مقدار سلول می شود . اگر بخواهیم همان مقدار اولیه باقی بماند ، كافی است در قسمت Fraction مقدار را انتخاب كنیم . در این حالت نگاه می كند ببیند مخرج اصلی باید در چند ضرب شود تا مخرج انتخابی شود سپس صورت را هم در همان عدد ضرب می كند .

۹- Scientific : مقادیر را با قالب بندی علمی نشان می دهد . ( e به … Enponent و به معنای ۱۰ به توان n می باشد . )

۱۰- Text : مقادیر به همان صورتی كه وارد شده اند نشان می دهد. اگر فرمولی را به صورت متن قالب بندی كرده باشیم ، Excel آن را به صورت متن نمایش می دهد و مقادیر آن را محاسبه نمی كند . در درسهای قبلی گفتیم كه برای تبدیل سلولهای عددی به متنی می توانیم از علائم ” ، ‘ و ^ و = استفاده كنیم . همچنین می توانیم قالب آن سلول را به Textتبدیل كنیم .

۱۱- Special : مقادیر را با استفاده از قواعد قالب بندی خاص نشان می دهد كه عبارتند از :

* Zip code : (كدپستی) برای قالب بندی كدپستی است و صفرهای قبل از عددرا نشان می دهد .

* zip code 4 : (كدپستی ۴ رقم) : كدپستی یا درقالب معمولی (قالب بالا) است یا در قالب ۴ رقم در این قالب دو عدد ثابت شده قبل از چهارم اول علامت خط تیره (-) می گذارد.

* phone number : اعداد را در قالب شماره تلفن ده رقمی نمایش می دهد . یعنی به صورت (nnn) nnn – nnnn نشان می دهد . قسمت داخل پرانتز كد شهرستان ، سه رقم بعد ، كد محله و چهار رقم آخر تلفن محل مورد نظر است .

* Social security number : (تامین اجتماعی ) اعداد را در قالب كد تامین اجتماعی ۱۰ رقمی نمایش می دهد . با انتخاب این قالب بعد از رقم سوم و پنجم خط تیره می گذارد .

۱۲-Custom : برای ایجاد یك قالب بندی جدید از این گزینه استفاده می كنیم كه خارج از بحث ما می باشد .

ب- Alignment :

توسط این Tab می توانیم جهت قرار گرفتن اطلاعات در سلول را تعیین كنیم . این Tab شامل قسمتهای زیر است :

۱- Horizontal : محل قرار گرفتن افقی متن را تعیین می كند . این قسمت شامل گزینه های زیر است :

* general : هم ترازی پیش فرض

* Left : هم ترازی داده ها در سمت چپ (استفاده برای ارقام ) (چپ چین )

* Center : هم ترازی داده ها در مركز

* Right : هم ترازی داده ها در سمت راست (برای متون) (راست چین)

* Fill : تمام سلول را با متنی كه در آن نوشته شده است ، پر می كند .

* Justify : یك تراز مناسب برای سلول در نظر می كیرد . معمولا برای زمانی است كه اطلاعات در سلول نگنجد.

*Center Across Selection : وسط متن را در وسط سلولهای انتخابی می گذارد.

۲- Vertical : محل قرار گرفتن اطلاعات را بطور عمودی تنظیم می كند . این گزینه شامل قسمتهای زیر است :

* Bottom : هم ترازی داده ها در پایین سلول

* Top:هم ترازی داده ها در بالای سلول

* Center: هم ترازی داده ها در وسط یا مركز سلول

*Justify : داده ها در داخل سلول هم تراز می شوند . (بدین معنی كه داده ها در داخل سلول بصورت مساوی جاسازی می شوند . مانند متون موجود در روزنامه ها )

۳- Orientation : شامل قسمتهای زیر است : * زاویه متن را نسبت به افق بوسیله ماوس می توان تعیین كرد.

* Degrees : زاوبه متن را نسبت به افق بوسیله تایپ زاویه یا با كلیدهای Increase و Decrease تعیین كرد.

۴- Text Control : شامل قسمتهای زیر است :

* Wrap Text : اگر اندازه متن بیشتر از سلول باشد توسط این گزینه می توان آن را شكست . در نتیجه ارتفاع سطر افزایش می یابد .

* Shrink to fit : اگر اندازه متن از سلول بیشتر باشد با انتخاب این گزینه متن به اندازه ای كوچك می شود كه داخل سلول بگنجد.

* Merge Cells : اگر اندازه متن از سلول بیشتر باشد می توان سلولهایی را كه متن اشغال كرده را انتخاب نمود و سپس در كنار این گزینه تیك زد . این عمل باعث می شود كه این سلول بهم پیوسته و یك سلول شوند .

ج- Font :

توسط این قسمت می توان نوع خط ، اندازه ، رنگ و … خطوط را تعیین نمود. این Tab شامل گزینه های زیر است :

۱- Font : در این قسمت نوع خط را تعیین می كنیم .

۲- Font Style : در این قسمت می توانیم تعیین كنیم كه نوشته ها كج (Italic ) و یا توپر (Bold) یا كج و تو پر (Bold Italic ) و یا معمولی (Regular ) باشد .

۳- Size : در این قسمت سایز قلم را تعیین می كنیم .

۴-Underline : در این قسمت می توانیم تعیین كنیم كه زیر متن خط كشیده شود یا نه و نوع خطر را تعیین می كنیم .

* None : خط نمی كشد .

* Single : خط تكی زیر متن می كشد .

* Double : دو خطی زیر متن می كشد .

* Single Accounting : تك خط زیر كشیده ترین حرف كشیده می شود . (یعنی تمام حروف بالای خط كشیده می شوند.)

* Accounting double : دو خطی زیر تمام حروف كشیده و زیر كشیده ترین حرف كشیده می شود .

۵- Color : در این قسمت رنگ متن را تعیین می كنیم .

۶-Effect : شامل قسمتهای زیر است :

* Strike Through : بر روی متن خط می كشد .

* Super Script : برای بالا نویسی (توان نویسی) استفاده می شود . اگر بخواهیم A2 بنویسیم كافی است A2 را نوشته ، سپس ۲ را انتخاب منیم و این گزینه را فعال كنیم .

* Sub Script : برای زیرنویسی (اندیس نویسی) استفاده می شود .

۷- Preview : پیش نمایشی از انتخابات را نشان می دهد.

د- Border : توسط این Tab می توانیم حاشیه بندی انجام دهیم . كه شامل قسمتهای زیر است:

۱- Style : در این قسمت نوع خط حاشیه را مشخص می كنیم .

۲- Color : در این قسمت رنگ حاشیه را مشخص می كنیم .

۳- Presets : در این قسمت محل خطوط را مشخص می كنیم .

* None : حاشیه نمی گذارد.

* Outline : دور سلولها حاشیه می گذارد.

* Inside : داخل سلولهای انتخابی را حاشیه می گذارد.

۴- Border : در این قسمت می توانیم تعیین كنیم كه كدام قسمت از سلول حاشیه داشته باشد . نیز می توانیم تعیین كنیم كه فقط سمت چپ سلول خط بیفتد و یا فقط بالا و یا پایین سلئل حاشیه داشته باشد.

نكته :برای انداختن حاشیه ابتدا رنگ و نوع خط را تعیین می كنیم و سپس بر روی Icon های مورد نیاز برای افتادن حاشیه Click می كنیم .

هـ : Patterns : در این قسمت می توانیم رنگ زمینه را تعیین كنیم . كه شامل گزینه های زیر است. :

* Color : در این قسمت رنگ مورد نظر را انتخاب می كنیم .

* Pattern : در این قسمت می توانیم از الگوها (هاشورها) استفاده كنیم . همچنین رنگ هاشورها را تعیین كنیم .

پاك كردن قالب بندی سلولها  در اکسل:

برای پاك كردن قالب بندی سلول كافی است سلول یا سلولهای مورد نظر را انتخاب كرده سپس از منوی Edit زیر منوی Clear ، گزینه Formatting را انتخاب می كنیم . با انجام این كار فقط قالب بندی سلل پاك می شود و اطلاعات داخل سلول پاك نمی شود . ولی اگر A11 را انتخاب كنیم هم قالب بندی پاك می شود و هم اطلاعات داخل سلول .

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

در نرم افزار اکسل برای وارد کردن یک لیست از نام های محدوده ابزار استفاده در فرمول (Use in Formula) در اکسل راهکار های زیادی وجدو دارد

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

لیست جاسازی شده شامل دو ستون است: یکی برای نام محدوده و یکی برای مختصات محدوده مرتبط با هر نام.

 پس از باز کردن کارپوشه ای که محدوده ای که می خواهیم اسم های آن را در یک لیست وارد نماییم در آن قرار دارد،پس از انتخاب محل قرار گرفتن لیست نام ها از گزینه وارد کردن نام ها (Paste Names) در ابزار استفاده در فرمول (Use in Formula) از گروه تعریف نام ها (Define Names) در سربرگ فرمول ها (Formulas) استفاده می نماییم و بعد از باز شدن کادر محاوره ای وارد کردن نام ها (Paste Names) می توانیم یک لیست از اسم های محدوده را داشته باشیم.

کارپوشه ای را که شامل محدوده ای است که می خواهیم نام های آن را در یک لیست وارد کنیم (از سلول B2 تا سلول E8) را باز می نماییم.

 

سلولی که می خواهیم لیست نام محدوده ها در آن نمایان شود (سلول G3) را انتخاب می نماییم.

نکته: اکسل داده های جدید را بر روی داده های موجود می نویسد، بنابراین یک مکانی را انتخاب می نماییم که در آن هیچ داده ای وجود ندارد یا اینکه اگر داده های موجود حذف شدند مشکلی نداشته باشد.

 

بر روی سربرگ فرمول ها (Formulas) کلیک می نماییم.

 

بر روی ابزار استفاده در فرمول (Use in Formula) کلیک می نماییم.

 

منوی مربوط به ابزار استفاده در فرمول (Use in Formula) برای ما باز می شود.

 

از منوی باز شده گزینه وارد کردن نام ها (Paste Names) را انتخاب می نماییم.

 

کادر محاوره ای وارد کردن نام ها (Paste Names) نمایان می شود.

 

بر روی کلید وارد کردن لیست (Paste List) کلیک می نماییم.

 

اکسل کادر محاوره ای وارد کردن نام ها (Paste Names) را می بندد و لیستی از اسم های محدوده های کارپوشه را وارد می نماید.

 

نکاتی پیرامون طرز ساخت لیست کشویی دینامیک در اکسل

یکی از امکاناتی که نرم‌افزار Excel در اختیار کابران قرار میدهد  ایجاد یک لیست کشویی پایین‌افتادنی دینامیک یا پویا است.

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

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

بدین منظور ابتدا سلولی که قصد دارید لیست کشویی در آن ایجاد شود را انتخاب نمایید.

 

ایجاد یک لیست کشویی پایین‌افتادنی دینامیک در اکسل

سپس در تب Data از منوی Data Validation بر روی Data Validation کلیک نمایید (برای این کار می‌‌توانید کلیدهای ترکیبی Alt+L را نیز نگه داشته و سپس کلید D را فشار دهید).

 

در پنجره‌ی باز شده، گزینه‌ی Allow را بر روی List تنظیم نمایید.

 

سپس بر روی دکمه‌ی مقابل فیلد Source کلیک نمایید.

 

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

حال محدوده‌ی سلول‌های انتخابی خود جهت قرارگیری در لیست کشویی را انتخاب نمایید.

 

در نهایت بر روی دکمه‌ی ضربدر کلیک کرده و پنجره‌ی باز را OK کنید.

 

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

لازم به ذکر است برای غیرفعال کردن این لیست کشویی، پس از انتخاب سلول مجدد کادر محاوره‌ای Data Validation را باز کرده و گزینه‌ی Allow را بر روی Any value تنظیم نمایید.

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

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

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

۱. استفاده از میانبر Ctrl + PgDn و Ctrl + PgUp برای رفتن از یک کاربرگ (worksheet) به کاربرگ دیگر د ر اکسل

 

۲. با استفاده از کلیدهای جهت دار + Ctrl بین سلول‌ها جابه جا شوید

 

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

۳. برای انتخاب داده‌ها از کلید Shift استفاده کنید

 

کلیدهای ترکیبی Ctrl + Shift+ و کلیدهای جهت دار کمک می‌کند محدوده‌ی داده‌ها را تا آخرین سلول دارای داده انتخاب کنید.

۴برای کپی دوبار کلیک کنید

 

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

۵.  تغییر فرمت سلول با استفاده از کلیدهای میانبر در erxcel

 

با استفاده از کلیدهای ترکیبی !+Ctrl + Shift می‌توانید فرمت عدد را به دو رقم اعشار تبدیل کنید. برای تبدیل به فرمت دلار کافی است از کلیدهای ترکیبی $+Ctrl + Shift و برای تبدیل به درصد از کلیدهای ترکیبی %+Ctrl + Shift استفاده کنید.

۶. برای قفل سلول‌ها از کلید F4 استفاده کنید

 

گاهی اوقات برای کپی داده‌ی یک سلول می‌خواهید فرمول آن هم کپی شود. گاهی هم می‌خواهید که این کپی صورت نگیرد. وقتی می‌خواهید ورودی سلول را قفل کنید می‌توانید یک علامت $ قبل از اسم سلول (مثلا B1) قرار دهید. تایپ کردن $ قبل از نام هم سلول کار وقت‌گیر و آزاردهنده‌ای است. در عوض می‌توانید به جای تایپ $ از کلید F4 استفاده کنید تا علامت $ ایجاد شود. با هر بار فشار دادن F4 حالت‌های قفل سلول، قفل ردیف، قفل ستون و حالت بدون قفل فعال می‌شود.

۷. خلاصه کردن داده‌ها با توابع CountIF و  SumIF در اکسل

 

تابع CountIF تعداد دفعات تکرار یک داده را در محدوده‌ی انتخاب شده مشخص می‌کند. اولین ورودی این تابع محدوده‌ی مورد نظر و دومین ورودی مربوط به شرط مورد نظر ما است. برای مثال در حالت زیر اسم چند نویسنده به نام‌های سانسا، اریا، راب، جان، ریکون و برن آورده شده است. هر کدام از آن‌ها چند داستان نوشته‌اند که هر داستانی تعدادی بازدیدکننده داشته است. برای مرتب کردن داده‌ها در یک سلول خالی دستور CountIF را نوشته تا از ستون اسم نویسنده‌ها اسم آریا را شمارش کند. پس اولین ورودی تابع ستون B4 تا B50 است و دومین ورودی آن سلول G4‌ است که اسم آریا در آن نوشته شده است.

 

حالا می‌خواهیم کل بازدیدکنندگان هر داستان را شمارش کنیم. در این حالت از تابع SumIF استفاده می‌کنیم. این تابع سه ورودی دارد. ورودی اول ستون B4 تا B50 است. ورودی دوم سلول مربوط به اسم نویسنده است و ورودی سوم ستون مربوط به تعداد بازدیدکنندگان است. حاصل این تابع به عنوان مجموع کل بازدیدکنندگان آثار یک نویسنده در مقابل اسم وی نمایش داده می‌شود.

۸. استخراج داده‌ها با تابع VLOOKUP در excel

 

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

تابع VLOOKUP چهار ورودی دارد. ورودی اول مربوط به داده‌ی مورد نظر است. در این مثال ما به دنبال عدد ۱ که رتبه‌ی دانشگاه است، هستیم. ورودی دوم مربوط به محدوده‌ی داده‌ها است که در این مثال کل ردیف A تا F انتخاب شده است. ورودی سوم مربوط به شماره ستون داده‌ی مورد نظر است. چون ما به دنبال اسم دانشگاه هستیم ورودی را ستون ۲ در نظر می‌گیریم. ورودی آخر مربوط به وقتی است که داده‌ی مورد نظر (مثلا عدد ۱) پیدا نشود که به جای آن عدد ۰ یا عبارت False را قرار می‌دهیم.

۹. استفاده از & برای ترکیب رشته‌های متنی

 

در اینجا دو ستون داریم که یکی شامل نام و دومی شامل نام خانوادگی است. با استفاده از علامت & می‌توان ستونی ایجاد کرد که نام و نام خانوادگی را در کنار هم داشته باشد. در نرم‌افزار اکسل با کمک & می‌توانید دو قسمت از متن را با هم ترکیب کنید. البته دقت کنید که بین نام و نام خانوادگی حتما فاصله قرار دهید. روش کار به این صورت است: [انتخاب سلول نام خانوادگی]& ” ” & [ّانتخاب سلول نام].

۱۰. پاک کردن متن با توابع LEFT، RIGHT وLEN  در اکسل

 

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

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

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

 

تابع LEN کل کاراکترهای یک رشته‌ی متنی را شمارش می‌کند و تنها یک ورودی دارد که آن هم سلول مربوط به رشته‌ی متنی است.

حالا با کمک تعداد کاراکترهای به دست آمده از تابع LEN می‌توانید از تابع RIGHT استفاده کنید.

 

از آن جا که ما می‌خواهیم حروف اختصاری و خط فاصله را حذف کنیم، وردی تابع RIGHT را عدد تابع LEN منهای ۳ قرار می‌دهیم.

۱۱. ایجاد اعداد تصادفی با تابع   RANDدر اکسل

 

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

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

نحوه ورود تاریخ در سلولهای اکسل
در اکسل برای ورود تاریخ باید بین اعداد روز، ماه و سال کاراکتر “/” و یا “-” آورده شود. برای وارد کردن ساعت هم از کاراکتر “:” استفاده می شود.  می توانید در یک سلول هم تاریخ و هم ساعت را وارد کنید.

 

توجه کنید که فرمت تاریخ اکسل بر اساس تنظیمات regional settings کنترل پنل ویندوز است. فرمت پیش فرض سیستم های ویندوزی اکثراً به شکل فرمت استاندارد آمریکایی بوده که اول ماه، بعد تاریخ روز و در نهایت سال وارد می شود.

 

تابع YEAR در اکسل

اگر بخواهیم از عدد سال یک سلول تاریخی استفاده کنیم باید از تابع YEAR کمک بگیریم. به همین ترتیب توابع DAY و month عدد روز و ماه را بر می گردانند.

تابع date در اکسل


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

تابع HOUR در excel

این تابع مقدار ساعت را بر می گرداند. توابع MINUTE عدد دقیقه و SECOND عدد ثانیه را برمی گرداند.

تابع TIME در اکسل


در صورتیکه قصد داشته باشیم به ساعت عددی را تفریق یا اضافه نماییم باید از تابع TIME استفاده کنیم.


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

نکاتی پیرامون توابع: Right و Left وMID در اکسل

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

تابع Right در اکسل :

---

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

= RIGHT(address or text, number of characters)

به عنوان مثال عبارت I Love ITPro را در سلول A1 وارد میکنیم. حالا به منظور استخراج کلمه ITPro،  از فرمول Right استفاده میکنیم، بدین معنی که از سمت راست سلول A1 ، تعداد 5 کاراکتر را استخراج می کند:

 

تابع Left در excel:

---

از این تابع به منظور استخراج تعداد خاصی از کاراکترهای سلول از سمت چپ استفاده میشود. فرمول استفاده از این تابع به صورت زیر است:

=LEFT(address or text, number of characters)

به عنوان نمونه اگر المانهای مثال فوق را در این تابع به کار ببریم؛

 

 

---

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

---

تابع  MID در EXCEL:

---

از این تابع به منظور استخراج تعداد خاصی از کاراکترهای سلول استفاده میشود که در آن، با شروع از یک کارکتر خاص، به تعداد چند کاراکتر که ما مشخص می کنیم، استخراج می شود. فرمول استفاده از این تابع به صورت زیر است:

=MID(address or text, start number, number of characters)

به عنوان نمونه، کارکتر 8 از سلول A1، حرف I از کلمه ITPro است که با مشخص نمودن تعداد 5 کاراکتر، کلمه ITPro با استفاده از تابع MID استخراج می گردد:

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

لحظه هایتان آسمانی

نکاتی در مورد قالب‌بندی شرطی در Excel

فرض كنید می‌خواهید با استفاده از كاربرگ‌های‌ ‌برنامه اكسل، نمرات دانش آموزان را كنترل كنید. به این صورت كه نمرات خوب از نمرات متوسط و ضعیف به‌راحتی قابل تفكیك باشند. باید دید برای این امر چه‌كار باید بكنید؟ می‌توانید از قالب بندی شرطی یا conditional formatting استفاده نموده و تغییرات مختلفی را در ظاهر یك سل (Cell)، بر اساس مقدار آن، ایجاد نمایید.
با قالب بندی شرطی قادر خواهید بود برای هر سل، سه شرط تعیین كنید كه با فرمت فعلی آن سل، مجموعاً چهار شرط می‌شود. با اعمال هر شرط، می توانید نوع فونت، خطوط حاشیه، و نوع سایه‌گذاری ‌‌(‌shading) هر سل را تعیین كنید كه البته این تغییرات، شامل فرمت عددی و نوع چیدمان سل نمی‌شود. برای استفاده از قالب‌بندی شرطی در اکسل ، باید شروطی را انتخاب كنید كه با مقادیر false و ‌true قابل ارزیابی باشند. مثلا جمله "آیا عدد بزرگ‌تر از ۱۰ است؟" را می‌توان با false یا true ارزیابی كرد.
اما جمله "مقدار عدد چقدر است؟" به این روش قابل ارزیابی نیست. بدین ترتیب، اگرنتیجه شرط، true باشد، تغییرات در سل انجام می شود وگرنه تغییری در آن ایجاد نخواهد شد. همچنین می توان به ترتیب تا سه شرط برای یك سل تعیین كرد كه اگر شرط اول، ارزش ‌true داشت، تغییرات انجام شده و بقیه شرط‌ها نادیده گرفته خواهند شد.
حال این معلم می خواهد رنگ هر سل را بر اساس نمره موجود در آن، تعیین كند. بدین صورت كه خانه‌های دارای نمره كمتر از ۵۰ به‌رنگ‌قرمز، بین ۵۰ و ۶۵ به‌رنگ ‌سبز، بین ۶۵ و ۷۵ به‌رنگ ‌بنفش، و نمرات بالاتر از ۷۵، به‌رنگ آبی درآیند.
برای این‌كار، ابتدا رنگ فونت كلیه سل‌های حاوی نمرات را آبی كنید. سپس آن‌ها را انتخاب نموده و در منوی Format، روی Conditional Formating كلیك كنید. در پنجره باز شده، كادر اول را در حالت Cell Value Is باقی بگذارید. از كادر دوم، گزینه ‌less than را انتخاب و در كادر روبه‌روی آن، عدد ۵۰ را تایپ كنید. سپس دكمه ‌Format واقع در سمت راست را كلیك كرده ودر زبانه Font، رنگ قرمز را برای اعداد كمتر از ۵۰ انتخاب نمایید. با زدن ‌OK، به كادر قبلی برگشته ودكمه Add در پایین صفحه را كلیك كنید.
می توانید تا سه شرط را برای هر سل تعیین كنید.
حالا برای تعیین شرط دوم، كادر مقابل Cell Value Is را به ‌less than، و در كادر روبه‌روی آن، عدد ۶۵ را بنویسید. سپس دكمه Format را زده و در كادر باز شده، رنگ سبز را انتخاب كنید. یك‌بار دیگر بر روی Add كلیك كنید و شرط آخر را نیز بدین ترتیب تعیین نمایید: در كادر اول Cell Value Is، در كادر دوم less than، و در كادر روبه‌روی آن، عدد ۷۵ را تایپ كنید. سپس دكمه ‌Format را كلیك كرده و برای این شرط، رنگ بنفش را انتخاب نمایید. اگر دقت كنید، می‌بینید كه دكمه Add بعد از اعمال سومین شرط، غیر فعال می‌شود. چرا كه شما نمی‌توانید بیش از سه شرط برای یك سل تعیین كنید. OK را بزنید و نتیجه را ملاحظه نمایید. ‌
راه ساده‌تر آن‌است كه قالب‌بندی‌های مورد نظرتان را به‌صورت یكجا بر تمامی سل‌ها اعمال كنید. اما اگر خواستید، می‌توانید بااستفاده از دكمه ‌FormatPainter، قالب‌های شرطی و فرمت اولیه یك سل را به سل‌های دیگر كپی نمایید.
در ضمن برای این‌كه در آینده بدانید قالب‌بندی‌های شرطی بر روی كدام‌یك از سل‌های كاربرگتان اعمال شده است، از منوی Edit،‌ گزینه Go To را كلیك و در كادر باز شده، دكمه ‌Special را كلیك كنید. در پنجره باز شده، دكمه رادیویی ‌Conditional Formats را علا‌مت زده و ‌OK كنید. با این‌كار كلیه سل‌های دارای قالب‌بندی شرطی، با رنگ جداگانه نمایش داده می‌شوند.‌
می‌توانید فرمتی را روی یك سطر با توجه به تعداد یك سل خاص، اعمال نمایید.
همچنین می‌توان فرمت یك ردیف را بر اساس داده های یك سل در آن ردیف تعیین كرد. فرض كنید شماره ستون‌ها را در ردیف یك می‌نویسید، نام دانش آموزان را در ستون‌‌A ، و نمره آن‌ها را نیز در ستونB. كلیه سل‌ها از خانه ‌‌۲ A تا آخرین آن‌ها در ستون B را انتخاب كنید. از مسیر Format / ConditionalFormatting در لیست اول، گزینه ‌Formula Is و در لیست دوم، شرط ‌‌۵۰>۲=‌‌$B را نوشته و رنگ متن را نیز تنظیم كنید. با استفاده از دكمه Add، شروط دوم و سوم را به ترتیب ۶۵> B۲ $= و ۷۵ >‌‌۲‌B $= تعیین نمایید. برای كنترل مجدد تغییرات هر سل، ابتدا سل ‌‌A۲ را انتخاب كنید و به مسیر Format / Conditional Formatting بروید.
این سل بر اساس مقدار موجود در سل B۲ تغییر خواهد كرد. فرمت سل ‌‌‌A۳ نیز براساس سل B۳ تغییر خواهد نمود و به همین ترتیب تا آخر. بخش‌ B$ فرمول برای مقایسه دوبه‌دو بین ستون‌ها تنظیم می‌شود تا بتواند تغییرات هر ردیف را به‌طور جداگانه انجام دهد. همچنین می‌توانید از شرط Formula Is برای دیگر ردیف‌ها نیز استفاده كنید. مثلاً بعد از انتخاب ردیف‌های موردنظر از فرمول‌های (Mod(Row(),۲= و ((Not(Mod(Row(),۲= استفاده نمایید. برای این‌كه به‌جای ردیف‌ها، در ستون‌ها تغییرات ایجاد كنید، در فرمول آن‌ها به‌جای ()‌ Row از () Column استفاده نمایید. اگر هم خواستید، می‌توانید در شروط ایجاد شده در ردیف‌های دیگر، از تغییراتی مانند رنگ و سایه گذاری نیز استفاده كنید.
استفاده از فرمول‌های پیچیده‌تر در ایجاد تغییرات شرطی، امكانات قوی‌تری را در اختیار شما قرار می‌دهد. مثلاً برای این‌كه در محدوده‌‌ B۲:B۵۰ اعدادی كه بیش از یك‌بار آمده‌اند مشخص شوند، می‌توان در جلو لیست Formula Is فرمول‌‌ ۱<(COUNTIF($B$۲:$B$۵۰;$B۲= را نوشت. برای پیدا كردن سل حاوی بزرگ‌ترین مقدار (یا ردیف حاوی سل بزرگ‌ترین مقدار) نیز می‌توان از فرمول (۵۰$B۲=MAX($B$۲:$B$=استفاده نمود. اگر هم خواستید فرمولتان محدوده وسیع‌تری را در یك ستون پوشش دهد، در این فرمول به‌جای ‌‌۵۰$B۲:$B$ از B:$B$ استفاده كنید.‌

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

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

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

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

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

 

چرا مهارت بالا در اکسل برای حسابداران مهم است؟

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

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

این یک راه امتحان شده و درست است.

اکسل همچنان ابزار مورد انتخاب در دنیای حسابداری و سرمایه‌گذاری برای فهم و تحلیل داده‌های مالی، یعنی پردازش اعداد و تدوین اطلاعات غیر عددی است. اکسل در سال ۱۹۸۵ درست شده است و با وجود تغییرات فراوان در تکنولوژی، هنوز هم در بیشتر صنایع کارها را با آن انجام می‌دهند. اکسل با استفاده‌های متنوعی که در تحلیل داده، مدیریت درآمدها و مخارج و پیش‌بینی و مدلسازی عملکرد مالی دارد بخش مهمی از صنعت امروز است.

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

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

 

 تحلیل‌های سنگین با اکسل

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

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

«در دنیای تجارت هر جا را که نگاه کنی اکسل حضور دارد. اکسل پراستفاده ترین نرم‌افزار در دنیای تجارت است.»

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

اکسل ابزار اصلی در بیشتر شرکت های حسابداری است، از آن برای پیش‌بینی و تسهیل رشد شرکت استفاده می‌شود، و همچنین برای کمک به تصمیم گیران که تعیین کننده یک سیستم به چه چیزهایی نیاز دارد و چه تغییراتی می‌باید اعمال شود. حتی اگر از اکسل متنفر هستید باید بدانید که جزء ابزاری است که در دنیای تجارت هر قدر هم موقعیت شغلیتان پایین باشد نمی‌توانید از آن اجتناب کنید. از تحلیل‌گران حسابداری، مدیران و حتی رؤسا انتظار می‌رود که بتوانند با حداکثر کارایی از آن استفاده کنند. برای ماندن در دنیای حسابداری، باید یاد بگیرید اکسل را دوست داشته باشید – یا دستکم به شکل مناسب آن را به کار بگیرید.

این مهارتها به تمرین و تجربه نیاز دارد؛ یک حسابدار تازه‌کار حقیقتاً نمی‌تواند ادای کاری که هنوز باید یاد بگیرد را در بیاورد. خبر خوب چیست؟ اگر مقداری زمان و انرژی (و احتمالاً پول) برای یاد گرفتن توابع پیچیده‌تر اکسل داشته باشید، آنگاه شما برای شرکت های دولتی و خصوصی تبدیل به یک دارایی بسیار با ارزش خواهید شد. منابع بیشماری هست – کتابها، فیلم‌های موجود در اینترنت، و مانند اینها – که می‌تواند به شما کمک کند در ریزه‌کاری های این نرم‌افزار استاد شوید. فقط دوره های آموزش حسابداری کافی نیست، اگر لازم بود ثبت نام در دوره‌های آموزش اکسل را هم در نظر داشته باشید. اینها به شما کمک خواهد کرد که هم به سطح بالاتری از تجربه دست پیدا کنید، و هم در طول زمان فرصتهای شغلی بهتری داشته باشید.

بنابراین به دروغ نگویید که در اکسل تجربهٔ زیاد دارید. به جای آن خودتان را متعهد به یاد گرفتن جزئیات ابزارهایی کنید که این نرم‌افزار در اختیار شما قرار می‌دهد، تا بتوانید یک پیشرفت حقیقی در شغل‌تان داشته باشید!

مطالبی ارزنده پیرامون ردیابی تغییرات در فایل های اکسل

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

ردیابی تغییرات در فایل های اکسل

برای انجام این کار ابتدا فایل مورد نظر را ایجاد کرده و آن را ذخیره نمایید. در سربرگ review گزینه track changes را انتخاب نمایید.

 

پس از انتخاب پنجره ای با عنوان highlight changes باز می شود. در این پنجره همه گزینه ها در حالت غیر فعال قرار دارند. تیک کنار گزینه track changes while editing. This also shares your workbook را فعال نمایید.

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

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

در زیر عنوان highlight which changes  سه گزینه با نام های when – who-where  قرار دارند. گزینه when به معنی” چه وقتی” که خود شامل تعدادی گزینه است به شما اجازه می دهد که تغییرات فایل را در زمانهای مشخصی پیگیری نمایید.

گزینه since I last saved در اکسل  تغییرات ایجاد شده بعد از آخرین ذخیره فایل را برای شما مشخص خواهد کرد. گزینه all هرگونه تغییر و در هر زمانی را مشخص می نماید. گزینه not yet reviewed  تغییراتی که تا کنون بررسی نشده اند را مشخص می کند. گزینه since date… از تاریخی که شما تعیین می کنید تغییرات را نمایش می دهد.

گزینه دیگر بخش “who”  به معنی چه کسی می باشد. در این قسمت می توانید لیست همکارانی که فایل را با آنها به اشتراک گذاشته اید ببینید و تغییرات توسط یکی و یا همه آنها را زیر نظر داشته باشد.

آخرین بخش این قسمت نیز “where” به معنی “کجا” می باشد و می توانید یک ناحیه از کاربرگ و یا کل آن را برای زیر نظر داشتن تغییرات انتخاب نمایید.

در پایین پنجره نیز دو گزینه وجود دارند. گزینه highlight changes on screen تغییرات صورت گرفته در فایل را روی خود ناحیه تغییر یافته مشخص نموده و گزینه list changes on a new sheet لیست تغییرات رادر یک صفحه  جدید برای شما مشخص می کند.

 

پس از تنظیم کادر ردیابی تغییرات، زیر عنوان track changes گزینه دیگری به نام accept/reject changes فعال می شود که با کلیک بر روی آن می توانید تغییرات را پذیرفته و یا آنها را نادیده بگیرید.

با کلیک بر روی گزینه accept/reject changes کادر دیگری ظاهر می شود که مانند کادرپنجره قبلی گزینه های when – who-where در آن قرار دارد و می توانید تغییرات دریک تاریخ مشخص، توسط  یک فرد مشخص و در یک ناحیه مشخص را پذیرفته و یا آن را نپذیرید.

 

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

 

ابتدا تغییرات دلخواهی درفایل انجام داده و آن را ذخیره کرده و آن را می بندیم . بعد از باز کردن مجدد فایل و کلیک بر روی highlight changes همانطور که در شکل می بینید سلول های B3 و E5 با حاشیه آبی رنگ و یک مثلث آبی رنگ در گوشه آنها نشان می دهد که مقادیر این دو سلول تغییر یافته اند.

 

برای رد یا قبول تغییرات بر روی  track changes و سپس accept/reject changes کلیک کرده و کلید ok را فشار می دهیم. پنجره دیگری باز شده و اولین سلول تغییر یافته و مقادیر تغییر یافته و همچنین نام کاربری که این تغییرات را ایجاد کرده نمایش داده می شود. برای قبول تغییرات دکمه accept و یا accept all و یا برای رد و عدم پذیرش دکمه reject و یا reject all را فشار می دهیم.

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

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

قالب بندی خانه ها در Excel

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

این موضوع هم به بهتر شدن ساختار کاربرگ کمک کرده و هم خطای انسانی را در جهت وارد کردن اطلاعات کم خواهد کرد .

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

مثال برای این موارد زیاد می باشد اما بهتر است به سراغ تنظیمات قالب بندی خانه ها برویم

. برای قالب بندی خانه ها در Excel مراحل زیر را طی میکنیم :

ابتدا محدوده مورد نظر خود را  که دارای یک فرمت یکسان هستند در اکسل انتخاب می  نماییم به طور مثال در یک کاربرگ فاکتور محدوده تعداد کالا را انتخاب نموده ایم .

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

 

روش اول :

1- انتخاب منوی Format از تب home
2- انتخاب گزینه Cells format

 

 

روش دوم :

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

 

روش سوم :

فشردن کلیدهای ctrl +1

با اجرای یکی ازسه روش بالا پنجره ای باز میشود که دارای Tab های مختلفی است بر روی تب number کلیک می نماییم

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

 

• General

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

• Number :

   

  یا عدد مقادیر را بصورت رشته ای از رقمهای متوالی نشان میدهد. در این حالت اگر عدد در سلول نگنجد سلول بزرگتر میشود. همچنین در این قسمت میتوانیم تنظیمات زیر را انجام دهیم :

  1. – Decimal Places : در این قسمت میتوانیم تعداد ارقام بعد از اعشار را تعیین کنیم.

  2. – Negative Number : در این حالت میتوانیم مشخص کنیم که عدد منفی به همان صورت نشان داده شود .یا به رنگ قرمز .یا سیاه با علامت منفی . یا به رنگ قرمز بدون علامت منفی نشان داده شود. ( توجه داشته باشید که این گزینه فقط روی اعداد منفی عمل میکند. )

  3. – Use 1000 Separator : با انتخاب این گزینه اعداد وارد شده از سمت راست سه رقم سه رقم با علامت کاما (,) جدا می نماید .

      

• Currency: مقادیر را همراه با سمبل پول رایج نشان میدهد. در این حالت میتوان تنظیمات زیر را انجام داد :

  1. Decimal Places: در این قسمت میتوانیم تعداد ارقام بعد از اعشار را تعیین کنیم.

  2. Symbol: نوع واحد پول را مشخص میکنید. در این قسمت واحد پول کشورهای مختلف نمایش داده شده و میتوانیم واحد دلخواه خود را انتخاب کنیم.

  3. – Negative Number : اعداد منفی چطور نمایش داده شوند.

      

• Accounting: مانند حالت Currency است . این قالب بندی ، قالب بندی حسابداری میباشد و علامت پولی در منتهاالهیه سمت چپ آن نوشته میشود. تفاوت عمده آن با currency در این است که بخش Negative Number در این فرمت وجود ندارد چون در حسابداری ماهیت اعداد هستند که باعث کاهندگی آن ها می شود نه منفی بودن آنها به طور مثال هزینه ها اعداد مثبتی هستند که بخاطر ماهیت آنها از درآمد کسر می شوند نه بخاطر منفی بودن آنها

   

• Date: تاریخ را با قالب بندی خاص تاریخ نشان میدهد. و ما میتوانیم انواع قالب بندی های تاریخ را دیده و انتخاب کنیم.

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

• انتخاب منطقه جغرافیایی یا location وجود دارد و با انتخاب منطقه جغرافیایی مورد نظر فرمتهای مختلف در بخش بعدی فعال می گردد

• در بخش TYPE با انتخاب منطقه جغرافیایی در بخش location انواع فرمت تاریخی در این بخش فعال شده و به دلخواه می توانید یکی از آنها را انتخاب نمایید .

   

6. Time : زمان را با قالب بندی های خاص زمان نشان میدهد. انواع قالب زمانی در این قسمت وجود دارند. ما میتوانیم مدلی را انتخاب کنیم که ساعت را از 1 تا 12 با برچسب صبح و بعد از ظهر نمایش دهد و یا مدلی را انتخاب کنیم که ساعت را از 1 تا 24 نمایش دهد

   در این قالب بندی نیز 2 امکان در دسترس می باشد .

• انتخاب منطقه جغرافیایی یا location  که با انتخاب منطقه جغرافیایی مورد نظر فرمتهای مختلف در بخش بعدی فعال می گردند

• در بخش TYPE با انتخاب منطقه جغرافیایی در بخش location انواع فرمت زمان  در این بخش فعال شده و به دلخواه می توانید یکی از آنها را انتخاب نمایید .

  
  7- Percentage : عددها را همراه با علامت درصد نشان میدهد.

باامکان انتخاب تعداد اعشار در بخش decimal places

نکته

در تمام قالب بندی ها بجز Percentage فرقی نمیکند که ابتدا اطلاعات را در سلول وارد کنیم یا اول قالب بندی را تنظیم کنیم. ولی در حالت Percentage اگر ابتدا عدد را نوشته و سپس فرمت را Percentage کنیم ، عدد را در 100 ضرب میکند. ولی اگر ابتدا فرمت را Percentage کنیم، و بعد عدد را بنویسیم، تغییری در عدد نمیدهد.

 

 

 

7. Fraction: مقادیر را بصورت عدد صحیح نشان میدهد که بدنبال آن نزدیک ترین کسر به مقدار واقعی ظاهر میشود. برای نوشتن یک عدد مخلوط کافی است ابتدا قسمت صحیح را نوشته سپس یک فاصله بدهیم و بعد صورت کسر را نوشته و بعد علامت (/) را بگذاریم و بعد مخرج کسر را بنویسیم. اگر عدد 6 5/10 را بنویسیم، پس از Enter کردن مقدار سلول 6 1/2 میشود. اگر بخواهیم همان مقدار اولیه باقی بماند، کافی است، در قسمت Fraction مقدار 3/10 را انتخاب کنیم. در این حالت نگاه میکند ببیند مخرج اصلی باید درچند ضرب شود تا مخرج انتخابی شود سپس صورت را هم در همان عدد ضرب میکند.

    

8. Scientific : مقادیر را با قالب بندی علمی نشان میدهد.

   باامکان انتخاب تعداد اعشار در بخش decimal places

    

8. Text : مقادیر را به همان صورتی که وارد شده اند نشان میدهد. اگر فرمولی را به صورت متن قالب بندی کرده باشیم، Excel آن را به صورت متن نشان میدهد و مقادیر آنرا محاسبه نمیکند.

    

10. Special در اکسل :

    در بعضی منااطق جغرافیایی بعضی از اعداد دارای قالب بندی خاصی می باشند  مانند کد پستی ، کد پستی به اضافه چهار رقم، یا شماره تلفن، شماره تامین اجتماعی) .

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

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

مثال در صورتی که در واحد پولی ریال قصد تغییر واخد به تومان را داشته باشیم بعد از انتخاب واحد ریال و تایید آن در سلول دوباره با فعال کردن پنجره format cells   و انتخاب گزینه custom  می توانیم کد نوشته شده برای واحد ریال را مشاهده نموده و به راحتی کلمه ریال را به تومان تغییر دهیم .

 

مطالبی ارزنده پیرامون رسم نمودار با دو محور عمودی ودو محور افقی در اکسل

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

  رسم نمودار با دو محور عمودی ودو محور افقی در اکسل                                                                                                                                        داده هاوفشردن کلید  F11 روی صفحه کلیدمیباشدکه در این روش، نمودار در sheet 

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

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

 باشد وبعضی دوباره کاریها را انجام ندهیم  نمودار را مستقیماً در sheet وبا استفاده 

از زبانه insert   رسم میکنیم .پس ابتدا برای رسم نمودار باید داده هامون را به طور 

صحیح انتخاب کنیم یعنی چارچوب انتخاب داده هامون باید صحیح باشد  جزئیتر

 میگویم برای یک نمودار معمولی دو محور x ,y داریم که برای انتخاب داده ها باید

 تعداد داده های محورهای x ,y یکسان باشند و یا عقب وجلونباشند.در محور x 

متغیر های ما قرار میگیرد ودر محور y مقدار این متغیرها قرار خواهد گرفت.حال ما 

میخواهیم دو نمودار با مقیاسهایی که بسیار با هم متفاوت هست را با هم

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

مقدارهای متفاوت رسم کنیم.پس ابتدا داده های گروه اول را با کلیک ودرگ موس

انتخابکرده وبا استفاده اززبانهinsert  یک نمودار خطی رسم میکنیم

 

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

روی قسمت chart Area  یا همان زمینه نموداردوم وانتخاب copy  آن را کپی کرده وروی نمودار

اول paste میکنیم.با کمال تعجب چون نمودارها از نظر مقیاس زیاد با هم تفاوت دارند نموداری که 

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

مسئله روی خط یا سری نموداری که میخواهیم محورعمودی آن را به راست منتقل کنیم کلیک

کردهوسپس درزبانه  Layout  با کلیک روی Format Selection  وفعال کردن  Secondary Axis

 در وسپس با بستن پنجره ، محور عمودی سری یا نمودار انتخابی ( نمودار دوم) به راست 

منتقلخواهد شد ودو نموداربا مقدارهای خیلی متفاوت در یک نمودار به طور واضح دیده خواهند

شد.که محور y نمودار اول در سمت چپ ومحورy نمودار دوم در سمت راست قرار خواهد گرفت.

  

حالا برای اینکه محور افقی دو نمودار را جدا کنیم یعنی یکی پایین ودیگری بالا باشد به زبانه

Layout رفته وسپس به مسیز زیر بروید.

Axes / Secondary Horizontal Axis / Show Left to Right Axis

 

 

نکاتی در مورد شرط گذاري در سلول هاي اكسل با گزينه Conditional Formatting

در اکسل همیشه برای کاربران این سوال پیش می آید که

چگونه بر روي يك سلول در اكسل ، تنظيمي اعمال كنيم كه با وارد كردن عدد يك در سلول علامت تيك سبز و با وارد كردن عدد صفر ، علامت ضربدر قرمز نمايش داده شود

 

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

ابتدا ستون مورد نظر خود را انتخاب مي كنيم

 

سپس بر روي Conditional Formatting  در اکسل از تب Style كليك مي كنيم

بعد از آن بر روي گزينه Manage Rules كليك مي كنيم

 

سپس در پنجره Conditional formating rules manager بر روي گزينه New Rule كليك كنيد

 

 

 

و در انتها بر روي گزينه Ok در هر دو پنجره كليك مي كنيم تا تغييرات اعمال گردد و خواهيد ديد كه با وارد كردن شماره 1 يا بزرگتر از يك در سلول جاري آيكن سبز رنگ و با واردكردن شماره 0 يا كوچكتر از صفر در سلول جاري آيكن قرمز رنگ نمايش داده مي شود