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

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

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

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

با اینکار وارد پنجره Visual Basic Editor می شویم. حالا در قسمت چپ گزینه EnableCalculation را پیدا می کنیم و مقدار آن را برابر با False قرار میدهیم

با اینکار فقط شیت مدنظر ما بصورت محاسبه دستی می شود و بقیه شیت ها بصورت محاسبه خودکار باقی می میمانند. حالا کافیست که هر بار که نیاز به آپدیت شدن محاسبات شیت داشتیم از تب Formulas گزینه Calculate Sheet راانتخاب کنیم. برای برگرداندن نحوه محاسبات شیت به حالت اتوماتیک هم کافیست دوباره گزینه EnableCalculation رابه مقدار True تغییردهیم.

هدف از آموزش اکسل امروز این است که چگونه می توانیم  در نرم افزار اکسل ماکرو یا کد VBA خود را ینویسیم چطور باید فایل راذخیره کنیم.  اگر بخواهیم ماکرو رابه کامپیوتر دگری  انتقال دهیم  فرمت  اکسل برای ذخیره ورکبوک ها در نسخه های قدیمی تر XLS و در نسخه های جدیدتر XLSX است. این فرمت ها از ذخیره سازی ماکرو پشتیبانی نمی کنند به این معنی که اگر کد VBA یا ماکرویی را در فایل اکسل ن بنویسید و بعد با فرمت XLSX ذخیره کنید تمام کدهای VBA که ایجاد کردید از بین میرود و پس از اینکه فایل ر امجددا باز کنید اثری از کدهایی که نوشتید دیده نخواهد شد.

در اکسل برای ذخیره فایل هایی که دارای کد VBA و ماکرو هستنددر اکسل میتونیم از دو فرمت XLSM و XLSB استفاده کنیم. برای اینکار  در پنجره SAVE AS اکسل یکی از این دو فرمت رو برای ذخیره ماکرو انتخاب کنیم.

زمانی که فایل اکسل را با فرمت XSM یا XLSB ذخیره می کنیم ماکروها و کدهای VBA ما هم در فایل ذخیره می شود و زمانی که این فایل رو به سیستم دیگری منتقل کنیم می توانیم از کدهایی که نوشتیم استفاده کنیم.

 فرض کنید بخواهیم کدی رابنویسیم که بتوانیم آن کد را به سیستم دیگه ای انتقال دهیم و در تمام فایل های اکسلی که ایجاد می کنیم (نه فقط فایل XLSM که ذخیره کردیم) از آن کد استفاده کنیم. به عنوان مثال اگر بخواهیم تابعی را وارد کنیم  که بتوانیم ازآن در  فرمول نویسی های در تمام فایل های اکسل استفاده کنیم. برای اینکار میتاونیم از فرمت XLAM اکسل استفاده کنیم که برای تهیه Add-In ها در اکسل استفاده می شود. این فرمت در واقع بسیار شبیه فرمت XLSM هست با تفاوت های جزئی. برای استفاده از این فرمت ابتدا باید فایل خودرا در پنجره SAVE AS اکسل با فرمت XLAM ذخیره می کنیم و به سیستم موردنظرانتقال بدهید. اکنون کافیست این فایل رادر نرم افزار اکسل به صورت یک Add-In اضافه کنید. در پنجره تنظیمات اکسل وارد بخش Add-Ins می شویم و در قسمت پایین پس از انتخاب Excel Add-Ins در بخش Manage رو دکمه Go کلیک می کنیم.

با انتخاب دکمه GO پنجره Add-Ins باز می شود.

حالا روی دکمه Browse کلیک می کنیم و فایل XLAM را انتخاب می کنیم. با اینکار فایل ما به عنوان یک Add-In به اکسل اضافه می شود و می توانیم از توابع و کدهای آن استفاده کنیم.

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

برای اینکار کافیست روی دکمه File در گوشه بالا سمت راست کلیک کنید. پس از باز شدن بخش BackStage  روی دکمه Protect Workbook کلیک کنید و از منوی باز شده گزینه Encrypt With Password راکلیک کنید.

در پنجره باز شده پسورد مورد نظررا تایپ کنید و پس از تایید رمز در پنجره ای که مجددا باز می شود رمز رادوباره تایپ کنید و  ok را کلیک کنید.

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

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

پنهان سازی سلول های اکسل

در اکسل نمی توان یک سلول را بصورت کامل پنهان ساخت اما می توان آن را بصورت ظاهری جایگزین نمود. بدین منظور سلول یا سلول های (با نگه داشتن کلید Ctrl یا Shift) خود را انتخاب کنید. بعد از آن کلیک راست کرده و بر روی گزینه ی Format Cells کلیک کنید.

در پنجره ای که نمایش داده می شود مطمئن شوید که تب Number انتخاب شده است. در پنل Category، قسمت Custom را انتخاب کرده و سپس در فیلد Type عبارت ;;; را تایپ نمایید. توجه داشته باشید که شما قادر خواهید بود بعداً جهت نمایش دوباره محتوا، این نوع فیلد را به حالت اولیه باز گردانید. سرانجام بر روی دکمه ی OK کلیک کنید.

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

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

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

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

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

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

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

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

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

نکته مهم قبل از شروع آموزش:

کاربران Excel 2016 در صورت مواجه شدن با خطاهای مختلف در هنگام وارد کردن فرمولها، توصیه می شود قبل از باز کردن اکسل فرمت فعلی نوشتار ویندوز را به English تغییر دهید. (با سرچ Region در ویندوز 10 و 8 به این بخش دسترسی خواهید داشت):

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

انتخاب دسته‌ای از سلولها در اکسل کاری ندارد، روی سلولهای مورد نظر کلیک و درگ می کنید. اما اگر بخواهید همه سلولها را انتخاب کنید چه؟ دو روش وجود دارد، یا از کلیدهای ترکیبی Ctrl A در ویندوز و یا Command A در مک استفاده کنید یا بر روی سلول کوچکی که در گوشه بالا سمت چپ یا راست وجود دارد کلیک کنید.

رنگی کردن ردیفها در EXCEL

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

برای شروع، منطقه موردنظر را هایلایت کنید (یعنی با درگ کردن، محدوده جدول را انتخاب کنید، می توانید با ترفند قبلی کل صفحه گسترده را انتخاب کنید)، اکنون همانطور که در تب home هستید همانند تصویر روی دکمه Formatting Conditional کلیک کرده و سپس New Rule را انتخاب کنید

بر روی منوی کشویی style در پنجره ظاهر شده کلیک کرده و گزینه Classic را برگزینید.

بعد از این کار، پنجره دیگری ظاهر می شود که بایستی Use a formula to determine which cells to format را انتخاب کنید. با استفاده از این گزینه می توانید با تعریف فرمول به صورت خودکار آن سلولهایی که بایستی استایل داده شوند را تعیین کنید.

برای مثال این فرمول را وارد و رنگ مورد نظر را انتخاب کنید تا سطرها یک درمیان رنگ شوند:

=MOD(ROW(),2)

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

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

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

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

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

نحوه استفاده از VLOOKUP  در اکسل

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

 مشاهده می کنید که هر محصول در ستون Item یک کد منحصر به فرد دارد و قیمت معادل آن نیز در ستون price بیان شده، ما میخواهیم تابعی تعریف کنیم تا کاربر بتواند با وارد کردن کد محصول (در سلول جلوی Enter item)، قیمت متناظر آن را در سلول پایین (جلوی Price) نمایش دهد. برای این کار در سلول جلوی Price جایی که میخواهید نتیجه نمایش داده شود این فرمول را وارد کنید:

=vlookup(I3,B2 11,3)

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

روش ساختن لیستهای کشویی

اگر میخواهید مقادیر ورودی را برای کاربران محدود کنید، منوهای کشویی راه حل خوبی برای این کار هستند. یک سلول خالی را انتخاب کنید، از تب Data و مطابق تصویر Data Validation را برگزینید. در تب Settings پنجره ظاهر شده گزینه List را انتخاب کنید، اکنون با انتخاب سلولهایی که مقادیر مورد نظر شما در آن است آیتمهای این لیست را تامین کنید. مراحل کار به صورت تصویری:  

نمایش صفرهای ابتدایی اعداد

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

ادغام کردن مقادیر

بعضی اوقات لازم دارید تا مقادیر چند سلول مختلف را با یکدیگر ادغام و به صورت واحد نمایش دهید. به عنوان مثال در تصویر ذیل اگر بخواهیم مقادیر سه فیلد Last name، FirstName و position را کنار هم بیاوریم از این فرمول استفاده می کنیم:

=concat(C6,B6,D6)

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

=concat(C6,” “,B6,” “,D6)

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

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

در حالت پیش‌فرض وقتی متنی طولانی وارد یک سلول می کنید، اگر محتوای مورد نظر بیشتر از اندازه سلول باشد روی سلولهای دیگر کشیده می شود که ظاهر خوشایندی ندارد. برای حل این مشکل روی سلول مورد نظر کلیک راست کرده و Format Cells را بزنید، در تب Alignment گزینه Wrap Text را انتخاب و ok کنید. طول و عرض سطر مورد نظر را نیز می توانید به دلخواه خود تغییر دهید. مراحل کار به صورت تصویری:

نمایش تب Developer

این تب به صورت پیش فرض در اکسل مخفی است، اگر نیاز داشته باشید تا کارهایی مثل نوشتن ماکروها را تجربه کنید به وجود این تب نیاز خواهید داشت، برای نمایش آن اگر اپلی هستید مطابق تصویر پنجره Preference را باز کنید و Ribbon & Toolbar را کلیک کنید و اگر ویندوزی هستید از منوی فایل و انتخاب Options و انتخاب Customize Ribbon اقدام کنید.

در پنجره ظاهر شده کافی است تیک Developer را بزنید تا تب نمایش داده شود.

در هنگام کار با اکسل شاید برای شما هم زیاد پیش آمده باشد که بخواهید یک worksheet را به یک workbook دیگر کپی کنید اما نخواهید فرمول ها قابل مشاهده شود.

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

برای این کار فایل اصلی را باز کنید، و در sheet که داده ها به همراه فرمول ها در آن موجود هستند بروید. روی sheet راست کلیک کرده و “Move Or Copy” را بزنید.

در صفحه ای که باز می شود، از منوی انتخاب “To book”، “(new book)” را انتخاب کنید و بعد تیک  “Create a copy” را هم بزنید. در پایان هم روی دکمه OK کلیک کنید.

بدین ترتیب یک  workbook جدید باز خواهد شد و داده های که در فایل قبلی داشتید، اینجا paste خواهند شد.

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

حالا در زبانه ی Home، روی Paste کلیک کنید، و بعد مطابق تصویر “Values” را انتخاب کنید.

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

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

این مسئله را با یک مثال کاربردی بیان خواهیم کرد. فرض کنید نتایج طراحی ارماتورهای تیرهای بتنی را استخراج کرده و میخواهید ان را کنترل کنید یا مساحت ان ها را تبدیل به تعداد میلگرد برای رسم در نقشه های اجرایی کنید (توجه داشته باشید که از این تکنیک برای پیدا کردن لنگرها و نیروها و غیره … هم می توانید استفاده کنید)

شرح مسئله:

هدف ما این است که مساحت ارماتور فوقانی انتهایی تیر را در دیتابیس استخراج کنیم. اما کار به این سادگی نیست برای مثال تیر شماره b7 در طبقات مختلف به همین نام تکرار می شود، ضمن ان که همین تیر می تواند ابعاد متفاوتی داشته باشد. در نتیجه تابع ما برای استخراج ایتم صحیح مود نظر باید 4 شرط داشته باشد:

شرط اول: کدام طبقه

بازه  E5:E1000 ستونی است که طبقات ما در ان مشخص است و i5 سلولی است که شرط ما به ان حساس است (در اینجا STORY5 شرط مورد نظر ماست)

شرط دوم: نام تیر

بازه  B5:B1000 ستونی است که نام تیر ما در ان مشخص است و j6 سلولی است که شرط ما به ان حساس است (در اینجا b7 شرط مورد نظر ماست)

شرط سوم: ابعاد تیر

بازه  C5:C1000 ستونی است که ابعاد تیرهای ما در ان مشخص است و k6 سلولی است که شرط ما به ان حساس است (در اینجا B45xB45 شرط مورد نظر ماست)

شرط چهارم: محل قرارگیری ارماتور

بازه  D5 1000 ستونی است که محل قرارگیری ارماتور در ان مشخص است (چپ – میانه – راست) و m5 سلولی است که شرط ما به ان حساس است (در اینجا End-i شرط مورد نظر ماست)

توجه: ممکن است بگویید تابع SUMIFS در اکسل مقدار را جمع می کند ولی بر نمی گرداند جواب من این است که با توجه به تعداد شروطی که برای ان گذاشته ایم امکان ندارد دو مقدار باشد که هر چهار شرط بالا را با هم ارضا کند البته روش دیگری است که با استفاده از ترکیب دو تابع Match و index می توانید چندین شرط ایجاد کنید.

همانطور که می دانیم اکثر کاربران نرم افزار اکسل (excel) برای انجام کارهای محاسباتی ریاضی نیازمند استفاده از فرمول نویسی در سلول ها هستند. در این مقاله قصد داریم تا حدودی نحوه کار کردن با امکانات محاسباتی اکسل و کار کردن با فرمول های آن آموزش داده شود.

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

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

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

نحوه درج فرمول در سلول ها

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

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

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

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

مقادیر ثابت

مقادیر ثابت مقادیری هستند که ثابت بوده و تغییر نمی کنند. این مقادیر می توانند عددی یا متنی باشند. در فرمول نویسی باید مقادیر ثابت متنی را بین دو علامت ”  قرار دهیم. در این صورت محاسبه ای روی این مقادیر انجام نمی شود.

آدرس سلول ها

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

به طور مثال فرض کنید مقدار عددی 2000 را در سلول B1 قرار می دهیم. سپس به سلول A1 رفته و در آنجا کاراکتر مساوی را درج و مقابل آن سلول B1 را وارد می کنیم. در این حالت سلول A1 نیز دارای مقدار 2000 خواهد شد و هر تغییری که در سلول B1 انجام دهیم، در سلول A1 نیز مشاهده خواهیم کرد.

عملگرها

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

عملگرهای محاسباتی: عملگرهای محاسباتی، عملگرهایی هستند که از آنها برای محاسبات عددی استفاده می شود. این عملگرها عبارتند از و – و * و / و ٪ و ^ که به ترتیب و از راست به چپ برای محاسبه جمع، تفریق، ضرب، تقسیم، درصد و توان به کار می روند.

عملگرهای مقایسه ای: از عملگرهای مقایسه ای برای مقایسه مقادیر استفاده می شود.

این عملگرها عبارتند از =،، => و. این عملگرها به ترتیب و از راست به چپ مساوی، بزرگ تر، بزرگ تر مساوی، کوچک تر، کوچک تر مساوی و نامساوی بودن دو عدد را مقایسه می کند.

نتیجه حاصل از عملیات این عملگرها می تواند مثبت (درست) یا منفی (نادرست) باشد.

به طور مثال نتیجه حاصل از عملیات 5=4 یک نتیجه �نادرست� است چرا که عدد 5 مساوی با عدد 4 نیست.

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

به طور مثال اگر مقدار سلول B3  =”سلام” باشد و سلول C3 =”جهان”، می توانیم در سلول D3 عبارت B3&C3 را به عنوان فرمول یعنی پس از تساوی وارد کنیم تا در این سلول عبارت “سلام جهان” درج شود.

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

به طور مثال نتیجه عبارت A1:A10 سلول های A1 تا A10 است اما برای اشاره به سلول های خاص از عبارت A1;A5;A7 استفاده می شود.

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

()، ٪، ^، * /، -، &

مثال: فرض کنید می خواهیم عبارت 4*2 10 را به دو حالت مختلف اجرا کنیم و نتیجه را بر اساس اولویت عملگرها مشاهده کنیم.

در حالت اول حاصل عبارت 4*2 10 عدد 18 خواهد شد. چرا که عملگر ضرب، از اولویت بالاتری نسبت به عملگر تقسیم برخوردار است.

اما اگر این عبارت را به صورت (10 2)*4 بنویسیم، حاصل 48 خواهد شد. چرا که عملگر پرانتز، اولویت بالاتری نسبت به عملگر ضرب دارد.

توابع

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

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

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

• توابع مالی

• توابع تاریخ و ساعت

• توابع ریاضی و مثلثاتی

• توابع آماری

• توابع جستجو و مرجع

• توابع پایگاه داده

• توابع متنی

• توابع منطقی

• توابع اطلاعاتی

• توابع مهندسی

ساختار توابع: هر تابع دارای یک نام و معمولا تعدادی ورودی است و ساختاری به صورت زیر دارد: (…. ; ورودی 3; ورودی 2; ورودی 1) نام تابع

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

در تصویر زیر، نام تابع SUM است و ورودی های تابع نیز سلول های A1 و A2 هستند.

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

درج فرمول با استفاده از Function Wizard در اکسل

اکسل فرمول های زیادی دارد و همانطور که پیشتر اشاره شد، هر تابع نیاز به �ورودی هایی� برای محاسبه دارد. با استفاده از امکان Function Wizard می توانید از تمام توابع اکسل استفاده کنید و نتیجه محاسبات را در سلول مورد نظر مشاهده کنید.

برای این کار از روی نوار فرمول، بر روی fx کلیک کنید تا کادر Insert Function باز شود.

توضیح کادر Insert Function: در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید.

در بخش Or select a category می توانید یکی از بخش های موجود را انتخاب کنید.

این بخش ها توابع اکسل را دسته بندی کرده است. مثلا اگر بخش Financial را انتخاب کنید، توابع مالی در کادر Select a function نشان داده خواهند شد.

با انتخاب گزینه All همه توابع اکسل و با انتخاب گزینه Most Recently used نیز توابعی که اخیرا استفاده کرده اید نشان داده می شوند.

اکنون در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید و از بخش Select a function آن را انتخاب کنید.

حال فرض کنید می خواهیم در کادر Insert Function تابع جمع را درج کنیم. همانطور که در مثال درج فرمول از طریق وارد کردن علامت = نیز گفتیم، این تابع با نام SUM شناخته می شود.

پس از انتخاب تابع SUM بر روی دکمه OK کلیک می کنیم. در پنجره جدید که Function Arguments نام دارد، باید ورودی های تابع را وارد کنیم.

در این مثال سه عدد  12 و 10 و 8 را وارد می کنیم.

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

در اکسل می توانیم لیست  را بر اساس هر یک از ستونهای آن به صورت صعودی یا نزولی مرتب کنیم. برای این کار کافی است با ما همراه باشید تا با روش های آن آشنا شویم:
روش اول:
1- یک سلول از ستونی را که میخواهیم لیست بر اساس آن مرتب شود انتخاب می کنیم.
2- برای مرتب سازی صعودی  در اکسل از آیکون Sort Ascending  و برای مرتب سازی نزولی  در اکسل از آیکون Sort Descending در نوار ابزار Standard استفاده می کنیم.
نکته
باید توجه داشته باشیم که در این حالت نباید یک ستون انتخاب شود بلکه فقط یک سلول از ستونی که میخواهیم بر اساس آن لیست مرتب شود را انتخاب می کنیم.
روش دوم:
1- کل جدول یا یک سلول از جدول را انتخاب می کنیم.
2- منوی Data را انتخاب می کنیم.
3- گزینه Sort را انتخاب می کنیم.
4- پنجره ای باز می شود که باید قسمتهای زیر را با توجه به نیاز در آن پر کنیم:
الف- Sort By: در این کادر ستونی را که میخواهیم لیست بر اساس آن مرتب شود، انتخاب می کنیم.
ب- Then By: در این کادر ستونی را تعیین می کنیم که اگر اطلاعات ستونی که در کادر اول مشخص شده برای مرتب سازی مانند هم بود لیست بر اساس این ستون مرتب شود.
ج- Ascending: با انتخاب این گزینه لیست به صورت صعودی مرتب می شود.
د- Descending: با انتخاب این گزینه لیست به صورت نزولی مرتب می شود.
ه- My List Has: در این قسمت دو گزینه Header Row، و No Header Row وجود دارد. اگر Header Row انتخاب شده باشد هنگام باز شدن پنجره Sort کل لیست به غیر از سطر اول که سطر عنوان است انتخاب می شود و در کادر های Sort By , Then By عناوین قرار می گیرند. ولی اگر No Header Row را انتخاب کنیم هنگام باز شدن پنجره Sort کل جدول حتی سطر اول که سطر عنوان است انتخاب می شود و در کادر های Sort by , Then By بجای عناوین, نام ستونها قرار می گیرند. در حقیقت در این حالت عناوین جزء اطلاعات در نظر گرفته شده و در مرتب سازی  در اکسل شرکت داده می شوند.

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

قالب بندی شرطی به شما این امکان را می دهد تا بصورت اتوماتیک یکسری قالب بندی مانند رنگ ها، آیکان ها و... را بر اساس مقادیر سلولها، به یک یا چندین سلول اعمال کنید. برای انجام این کار ابتدا باید یک قانون برای آن بسازید. به این قوانین conditional formatting rule (قوانین قالب بندی شرطی) گفته می شود. بعنوان مثال، یک قانون قالب بندی شرطی می تواند این باشد که اگر مقدار سلولی کمتر از $2000 بود رنگ آن سلول قرمز شود. با اعمال این قانون به شکل قالب بندی شرطی شما با یک نگاه از روی رنگهای قرمز می توانید به سرعت تشخیص دهید مقادیر کدام سلولها کمتر از $2000 بوده است.                                                            

روش های  ایجاد یک قانون برای قالب بندی شرطی در اکسل

در این مثال ما یک برگه اکسل داریم که داده های فروش در آن می باشد، و می خواهیم بدانیم که کدامیک از فروشندگان ما به هدف فروش ماهیانه اش رسیده است. هدف فروش ماهیانه $4000 می باشد. پس ما یک قانون قالب بندی شرطی می سازیم تا هر سلولی که مقدارش بیشتر از 4000 دلار باشد مشخص گردد.                                                                                                                                         ➊ ابتدا سلولها مورد نظر که می خواهید قالب بندی شرطی روی آنها اعمال گردد را انتخاب کنید.

➋ در تب Home بر روی دستور Conditional Formatting کلیک کنید. یک کادر پایین افتادنی باز می شود.

➌ نشانگر ماوس را بر روی قالب بندی شرطی مورد نظرتان ببرید، سپس از منوی باز شده قانون مورد نظرتان را انتخاب کنید. در این مثال، ما گزینه greater than (بزرگتر از) را انتخاب می کنیم.                                                                                                                               
➍ یک پنجره ظاهر می شود. مقدار مورد نظرتان را وارد کنید. در این مثال ما مقدار 4000 را وارد می کنیم.

➎ سبک قالب بندی را از کادر باز شدنی آن انتخاب کنید، در این مثال ما سبک قالب بندی Green Fill with Dark Green Text را انتخاب می کنیم. سپس بر روی OK کلیک کنید.                  
➏ قالب بندی شرطی بر روی سلولهای انتخاب شده اعمال می گردد.                            

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

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

• Data Bars: نوارهای داده، نوارهایی بصورت افقی هستند که به هر سلول اضافه می شوند. عملکرد این نوارها مانند نمودارها می باشد. در هر سلول بر اساس مقدار آن سلول این نوارها بصورت اتوماتیک تنظیم می شوند و جلوه بصری خیلی خوبی به اطلاعات شما می دهند.
• Color Scales: مقیاس رنگها، بر اساس مقدار هر سلول رنگ آن را تغییر می دهند. هر مقیاس رنگ از دو یا سه درجه بندی رنگ استفاده می کند. برای مثال، در مقیاس رنگ Green-Yellow-Red، بزرگترین مقدار به رنگ سبز در می اید، میانگین ها به رنگ زرد، و کمترین مقدار به رنگ قرمز در می آید.
• Icon Sets: مجموعه آیکان ها (شکلک ها)، با توجه به مقدار هر سلول آیکانی را به سلول اضافه می کند.

روش استفاده از قالب های شرطی از پیش طراحی شده اکسل

➊ ابتدا دامنه سلولی مورد نظرتان را جهت اعمال قالب بندی شرطی انتخاب کنید.



➋ بر روی دستور Conditional Formatting کلیک کنید. یک کادر پایین افتادنی باز می شود.

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



➍ قالب بندی شرطی انتخاب شده بر روی سلولها اعمال می گردد.

 حذف قالب بندی شرطی در اکسل

➊ بر روی دستور Conditional Formatting کلیک کنید. یک کادر باز می شود.

➋ نشانگر ماوس را روی گزینه Clear Rules ببرید، و قوانینی را که می خواهید حذف کنید را انتخاب کنید. در این مثال ما گزینه Clear Rules from Entire Sheet را انتخاب می کنیم، این گزینه تمامی قالب بندی های شرطی موجود در برگه اکسل را حذف خواهد کرد.



➌ قالب بندی های شرطی حذف می گردند.


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