امروز شنبه 22 دی 1403
0

 

ویژگی مرتب (sort) کردن در برنامه Excel این محدودیت را دارد که اجازه مشخص کردن بیش از سه داده برای مرتب کردن (سورت کردن) را نمی دهد. در بیشتر موارد همین سه داده کافیست اما شرایطی پیش می آید که امکان مرتب کردن با بیش از سه داده بسیار مفید خواهد بود. در این قسمت راهی برای دور زدن این محدودیت به شما نشان داده می شود.
برای این مثال، فرض می کنیم داده های مرتبطی در ستون های A، B، C، D و E داریم و می خواهیم آن ها را ابتدا بر طبق ستون A، سپس B، سپس C، سپس D و پس از آن E مرتب کنیم. برای انجام این کار، لازم است امکان سورت کردن وارونه (Backward) را داشته باشیم. به عبارت دیگر، ابتدا بر طبق آخرین مورد سورت کرده و سپس به ترتیب تا اولین داده سورت می کنیم.
ستون های A تا E را انتخاب کرده و سپس مسیرData → Sortرا دنبال کنید. سورت کردن را با این ترتیب مشخص کنید که ابتدا ستون C، پس از آن D و سپس E مرتب شوند. روی sort کلیک کنید. اکنون ستون های A تا E را انتخاب کرده و مسیر Data → Sort را دنبال کنید. این بار ابتدا ستون A و سپس ستون B را مرتب کنید. روی Sort کلیک کرده و پس از آن همه چیز مرتب می شود. با این کار برنامه Excel به جای سه مورد داده، مرتب کردن را با پنج مورد داده انجام می دهد.


اگر می خواهید این کار به صورت خودکار انجام شود، می توانید از یک ماکرو استفاده کنید که هم داده های انتخابی را سورت کرده و هم بر اساس فرمت کردن ردیف اول ستون های انتخاب شده، تخمین می زند که داده های شما دارای عنوان ستون هستند یا نیستند. اگر عنوان ها Bold باشند، برنامه Excel تشخیص می دهد که آن ها عنوان ستون ها هستند و مرتبشان نمی کند. در عوض، از اولین ستون سمت چپ تا آخرین ستون سمت راست، تا 256 ستون، مرتب می شود. کُد ماکرو مورد نیاز شما باید در مدول استاندارد قرار داده شود. برای رفتن به این قسمت، مسیر Tools → Macro → Visual Basic Editorرا دنبال کرده (Alt/Options-F11)، سپس Insert → Module را انتخاب و کد زیر را وارد کنید:
 Sub SortBy )
Dim l As Long
For l = Selection.Columns.Count To 1 Step -1
    Selection.Sort Key1:=Selection.Cells(2, l), _
     Order1:=xlAscending, Header:=xlGuess, Orientation:=xlTopToBottom
Next l
End Sub
برای برگشتن به فایل، روی آیکون Excel کلیک کرده یا Alt+Q را فشار دهید. با یک بار وارد کردن این کد می توانید در هر بار استفاده از Excel، تعداد ستون بیشتری از حد استاندارد را مرتب کنید.

مرتب کردن تصادفی (Random)در اکسل
شما می توانید با استفاده از برنامه ی Excelسه برندهیکم، دوم و سوم را به طور تصادفی (random) و از یک لیست دلخواه در صفحه ی گسترده ی خودتان انتخاب کنید. آسان ترین و بهترین روش برای این کار استفاده از تابع RAND و ترکیب آن با امکانات مرتب کردن (سورت کردن) است.
فرض کنیم یک جدول سه ستونی در صفحه گسترده ی خود دارید که از ستون Bشروع شده و به ترتیب شامل نام، سن و شماره شناسنامه است. می توانید تابع RAND در اکسل را در خانهA2 قرار داده و در خانه های پایینی آن به تعداد دلخواه، تا پایان جدول، کپی کنید. به محض انجام این کار، هر خانه در ستون A دارای تابع RAND خواهد بود که به شکل خودکار (اتوماتیک) یک عدد تصادفی (رندوم) را بر می گرداند که با استفاده از آن می توان جدول را سورت کرد. به عبارت دیگر، می توان ستون های A، B، C و D را به ترتیب صعودی یا نزولی و با استفاده از ستون A، مرتب کرد که به این ترتیب سه داده اول، سه برنده ی مورد نظر خواهند بود.
تابع RAND یک تابع به اصطلاح نا پایدار است که با انجام هر تغییری در فایل، به عنوان مثال وارد کردن داده ها در هر قسمت یا تکرار یک سری محاسبات با فشار دادن F9، محاسبات را دوباره انجام می دهد. پس بهتر است قبل از هر کار دیگر، برنده ها را در جایی دیگر یادداشت کنید.
البته می توانید از این خصوصیت تابع RAND به نفع خودتان استفاده کرده یک ماکرو را ثبت کنید که داده هایی را بی درنگ بعد از هر محاسبه مجدد، مرتب کرده  و تابع RAND را مجبور کند تا به مجموعه دیگری از اعداد رندوم برگردد. پس از آن شما می توانید این ماکرو را به یک دکمه پیوست (attach) کرده و هر بار که بخواهید سه برنده را نشان دهید، فقط باید روی همان دکمه کلیک کرده و سه نام بالایی را انتخاب کنید.
برای مثال، فرض کنیم در ستون های B، C و D داده هایی داریم و در ردیف 1، عنوان های ستون ها را قرار داده ایم. در ابتدا، عنوان RAND را در خانهA1 قرار می دهیم. عبارت =RAND() را در خانهA2 وارد کرده و به تعداد نیاز در خانه های پایین آن کپی می کنیم. سپس هر خانه را انتخاب و مسیر Developer → Code → Record Macro (در نسخه های قبل از 2007، مسیر Tools → Macro → Record New Macro …) را دنبال می کنیم.
ستون های A، B، C و D را انتخاب کرده و F9 را فشار می دهیم (برای محاسبه ی مجدد در Mac از علامت Office استفاده کنید). با دنبال کردنSort & Filter options → Data → Sort، داده ها بر طبق ستون A مرتب می شوند. پس از آن باید ثبت کردن ماکرو را متوقف کنیم.
سپس، مسیر Control Options → Developer → Insert (برای نسخه های قبل از 2007، مسیر View → Toolbars → Forms) را دنبال می کنیم. یک دکمه را از نوار ابزار Forms انتخاب کرده و در یک جای دلخواه صفحه (worksheet) قرار می دهیم. ماکرویی که ثبت کرده ایم را به این دکمه اختصاص داده و روی OK کلیک می کنیم (اگر می خواهید می توانید نام دکمه را از Button 1 به هر نام دیگری تغییر دهید).
می توانید ستون A را انتخاب و آن را مخفی (Hide) کنید. لزومی ندارد که هر کاربری این ستون را ببیند. هر بار که روی این دکمه کلیک کنید، داده های شما به طور تصادفی مرتب شده و تنها کافیست مطابق شکل 6، سه داده ی بالایی (برنده ها) را بخوانید.
 تابع RAND در Excel 2003 و Excel 2007 یک ایراد بزرگ دارد. اگرچه در فایل Help این برنامه ها به روشنی بیان شده که عدد تصادفی برگشت داده شده بین 0 و 1 خواهد بود، همیشه در مواردی که این تابع برای چندینخانه استفاده می شود، چنین نیست. در برخی موارد تابع RAND عددی کمتر از 0 را بر می گرداند. در سایت http://support.microsoft.com/default.aspx?kbid=828795، دلیل Microsoft برای تغییر الگوریتم آورده شده است.
دستکاری (Manipulate) داده ها با فیلتر پیشرفته (Advanced Filter)
اگر با ابزار AutoFilter برنامه ی Excel آشنایی دارید، با محدودیت های آن نیز آشنا هستید. اگر به دستکاری داده ها در مقیاس وسیعی نیاز دارید، استفاده از ابزار فیلتر پیشرفته (Advanced Filter)، همان راهی است که باید انتخاب کنید.
با تمام محدودیت ها، AutoFilters یک راه مناسب برای نمایش داده هایی است که در یک محدوده ی مشخص قرار دارند (در معیارهای مشخصی صدق می کنند). اگرچه در برخی موارد نمی توانید با استفاده از امکانات استاندارد AutoFilters، اطلاعات مورد نظرتان را جمع آوری کنید. ابزار Advanced Filter که یکی از امکانات متنوع و چند کاره در Excel است، امکان دستکاری بیشتر در داده ها را برای شما فراهم می کند. وقتی از ابزار Advanced Filter استفاده می کنید، جدول شماباید در فرمت جدول کلاسیک تنظیم شده باشد.
برای استفاده از ابزار Advanced Filter لازم است یک کپی از عنوان های ستون های جدول خود را در جایی بالای داده ها داشته باشید. همیشه باید دست کم سه ردیف خالی بالای جدول داده ها داشته باشید. برای اطمینان از اینکه عنوان های شما به درستی یکسان بوده و بدون توجه به تغییر عنوان های ستون ها یکسان باقی می مانند، همیشه عنوان ستون ها را با یک فرمول مرجع ساده مانند =A4 که در آن A4 شامل یک عنوان ستون است، ارجاع بدهید. این فرمول را به تعداد عنوان های ستون موجود در جدول خود کپی کنید. این فرمول نویسی ساده شما را از پویا (دینامیک) بودن عنوان ها در فیلتر پیشرفته مطمئن می کند. درست زیر این عنوان ها، معیارهای استفاده از فیلتر پیشرفته را قرار دهید. برای اطلاع بیشتر به قسمت Advanced Filters Criteria در Excel Help مراجعه کنید.
در زمان کار با فیلتر پیشرفته به خاطر داشته باشید که برای دو یا چند معیاری که به طور مستقیم در زیر عنوان قرار گرفته اند باید از OR استفاده کرد. اگر می خواهید از AND استفاده کنید، عنوان ستون ها و معیار باید دو بار، پهلو به پهلو، ظاهر شوند. شکل 7 چگونگی استفاده از عملگر OR و شکل 8 چگونگی استفاده از عملگر AND برای فیلتر کردن داده ها را نشان می دهد.
استفاده ی ساده ای از فیلتر پیشرفته در هر دو مثال نشان داده شده است و در هر دو مورد می توان به جای فیلتر پیشرفته از AutoFilter استفاده کرد. در ادامه مثال هایی زده می شوند که در آن ها امکان استفاده از AutoFilter وجود ندارد و باید از فیلتر پیشرفته استفاده شود.
توجه به این نکته مهم است که در زمان استفاده از یک فرمول برای معیار، نباید در بالای معیار از یک عنوان یکسان با داده های داخل جدول استفاده کرد. برای مثال اگر لیستی از داده های عددی در ستون A دارید که از خانه A5 شروع می شود (یعنی خانهA4 عنوان ستون است) و می خواهید تمام عدد های بیشتر از متوسط را از آن خارج کنید، باید از معیاری مانند:
 =A5>AVERAGE($A$5:$A$500)
استفاده کنید. اگر معیار در خانهA2 قرار گرفته، محدوده ی داده عبارت از $A$1:$a$2 است اما $A$1 نمی تواند شامل عنوان یکسانی با آنچه برای یکی از لیست ها استفاده شده، باشد. یا باید خالی بوده یا به کلی متفاوت باشد.
همچنین توجه به این نکته نیز اهمیت دارد که تمام فرمول های مورد استفاده باید بازگشت TRUE یا FALSE داشته باشند. محدوده ی تابع متوسط با اضافه کردن علامت دلار ($) قطعی می شود در حالی که ارجاع به خانه A5، یک مرجع وابسته (نسبی) است. توجه به این نکته ضروری است چرا که در زمان استفاده از فیلتر پیشرفته (Advanced Filter)، برنامه Excel، خانهA5 را یک مرجع نسبی در نظر گرفته و لیست را در هر زمان به اندازه ی یک ورودی پایین برده و آن راTRUE یا FALSE نشان می دهد. اگر TRUE برگردانده شود، متوجه می شود که باید نشان داده شود اما اگر FALSE برگردد، در معیار مورد نظر قرار نگرفته پس نشان داده نمی شود.
فرض کنیم چند نام در محدوده ی SAS3:$A$500 و با عنوان های شروع شده از A4 تکرار شده اند. همچنین فرض کنیم تعدادی از عنوان ها نیز چند بار تکرار شده اند. برای جدا کردن نام هایی که بیشتر از یک بار تکرار شده اند، باید از Advanced Filter و فرمول زیر به عنوا معیار استفاده کنید:
 =COUNTIF($A$5:$A$500,A5)>1
با استفاده از Advanced Filterو گزینه ی Copy to Another Location، لیستی که تازه ایجاد شده، شامل تمامنام هایی خواهد بود که بیشتر از یک بار در لیست اصلی تکرار شده اند (شکل 9 را ببینید). تعداد زیادی از این نام ها چندین بار تکرار شده اند اما می توانید به سادگی این لیست جدید را با استفاده ی مجدد از Advanced Filter، فیلتر کرده و این بار ازUnique Records Onlyاستفاده کنید (شکل 10 را ببینید). این بار نام هایی به شما نشان داده خواهد شد که بیش از یک بار در لیست آمده اند.
کسانی که از Advanced Filter استفاده می کنند این سوال را مطرح می کنند که چگونه می توان Excel را مجبور کرد که داده ها را بدون هیچ کم و کاستی با معیاری که آن ها مشخص کرده اند، فیلتر کند. اگر معیار شما Dave باشد و از Advanced Filter روی یک لیست طولانی استفاده کنید، Excel در کنار اسم Dave، اسامی مانند Davey، Dave J، Dave K و غیره را نیز نشان می دهد. به بیان دیگر، هر اسمی که با Dave شروع شود در این معیار قرار می گیرد. برای اینکه Excel مجبور به پیدا کردن عبارت دقیق، در این مثال فقط نام Dave، شود باید از معیار =”=Dave” استفاده کنید.

0

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

 

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

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

به تب FORMULAS رفته و show formulas را کلیک کنید.

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

به تب Home بروید وقسمت Format در منوی کشویی Format Cells را کلیک کنید.

پنجره Format Cells باز می شود و برای مخفی کردن آن به تب Protection بروید و تیک Hidden بزنید و دکمه OKرا کلیک کنید.

برای پایان دادن به مخفی کردن سلول شما باید از ورق محافظت کنید وFormat را کلیک کنید و در منوی کشویی Protect Sheet را کلیک کنید.

پنجره Protect Sheet باز می شود و اگر می خواهید کاربران به فرمول ها دسترسی کند و می خواهید آن فرمول رمز گذاری کنید شما باید در قسمت Password to unprotect sheet رمز عبور را تعیین کنید و دکمه OK را کلیک کنید.

رمز عبور را دوباره وارد کنید و دکمه OK را کلیک کنید.

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

برای نشان دادن فرمول در نوار فرمول دیگر،قسمت Format در منوی کشویی Unprotect Sheet را کلیک کنید.

پنجره Unprotect Sheet باز می شود و رمز عبور را وارد کنید.

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

0

تابع، یک فرمول از پیش نوشته شده در برنامه است. آرگومان های (متغییرهای) مورد نیاز به تابع داده می شود و تابع، نتیجه را محاسبه می کند. برای تابعSUM، تنها چیزی که باید مشخص کنید محدوده ای است که می خواهید جمع کنید نوار ابزارStandard، امکان دستیابی سریع به SUM و نیز توابع پر استفاده دیگر مانند AVERAGE (محاسبه معدل)، MIN (کوچکترین عدد)، MAX (بزرگترین عدد) وCount (محاسبه تعداد آیتم ها) را فراهم می کند. در کار عملی16 «چگونه از توابع استفاده کنیم» یاد می‌گیرید که چگونه از توابع دیگر استفاده کنید.

 

 یک محل برای نتیجه، انتخاب کنید  

 

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

 

 روی AutoSum کلیک کنید  

 

در نوار ابزار Standard، روی دکمه AutoSum کلیک کنید.

 

 فرمول SUM درج می شود  

 

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

 

 درج داده ها را تکمیل کنید  

 

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

 

 با استفاده از AutoSum، معدل حساب کنید  

 

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

 

 ورود اطلاعات را کامل کنید  

 

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

*توجه  *

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

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

 

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

برای جمع کردن مقادیر بیش از یک محدوده، روی دکمه AutoSum کلیک کنید، محدوده اول را انتخاب کنید، کلید کاما (,) را بفشارید و بعد محدوده بعدی را انتخاب کنید. بین تمام محدوده های بعدی هم باید کاما تایپ کنید.

0

 قابلیت AutoFill با پر کردن خودکار مدخل های تکراری یا یک سری داده (مانند Apri،May، June) می تواند ورود داده ها را به میزان چشمگیری سرعت بخشد. با AutoFill به سرعت می توانید لیستی از اسم روزها یا ماه ها، یک سری اعداد یا یک لیست از متن های مشابه ایجاد کنید. اسم روزها و ماه ها و مخفف های سه حرفی آنها در Excel وجود دارند. می‌توانید لیست های سفارشی(مثلاً از اسم افراد یا محصولات) ایجاد کنید و AutoFill آنها را پر کند.

 

 یک لیست از ماه‌ها ایجاد کنید  

 

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

 

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

 

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

 

  AutoFill Options را انتخاب کنید  

 

دکمه ماوس را در انتهای سطر یا ستونی که می خواهید پر کنید رها کنید. سری مورد نظر به ترتیب لازم در سلول ها درج می شود. دکمه Auto Fill Options ظاهر می شود. روی فلش آن کلیک کنید تا لیستی از گزینه ها باز شود. از این لیست برای انتخاب چگونگی پُر شدن سلول ها استفاده کنید. برای مثال، می توانید به Excel دستور دهید که بدون کپی کردن فرمت، یک سری ایجاد کنید. برای این کار، گزینه Fill Without Formatting را انتخاب کنید.

 

 یک سری متنی ایجاد کنید  

 

برای ایجاد یک سری متنی مانند Division 1، Division 2 و، داخل یک سلول کلیک کنید و اولین مورد در این سری را تایپ کنید. سلول را انتخاب کنید و دسته پر کردن را در جهتی که می خواهید بکشید.

 

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

 

دسته را رها کنید تا سری متنی در سلول‌های انتخاب شده پر شود. اگر لازم باشد، روی فلش دکمه AutoFill Options کلیک کنید و یکی از گزینه ها مانند Copy Cells را انتخاب کنید تا داده ها را بدون ایجاد سری، کپی کند.

 

 یک سری عددی ایجاد کنید  

 

برای ایجاد یک سری عددی، دو عدد اول را در سلول های مجاور تایپ کنید، این دو سلول را انتخاب کنید و دسته را برای پر کردن سری، بکشید. AutoFill بصورت خودکار، بقیه سری را بر اساس دو عدد اول پر می کند. برای مثال، اگر در سلول اول تایپ کنید5 و در سلول دوم تایپ کنید. 15، یک سری ایجاد خواهید کرد که در هر سلول، 5 عدد به عدد قبلی اضافه می شود.

 

*توجه *

 کپی کردن داده ها

برای پر کردن یک لیست با یک مدخل کپی شده، آن مدخل را تایپ کنید، آن را انتخاب کنید و دسته را برای کپی کردن آن مدخل بکشید. اگر Excel یک سری ایجاد کند، روی دکمه AutoFill Options کلیک کنید و Copy Cells را انتخاب کنید.


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

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

برای ایجاد یک لیست سفارشی، کل لیست را در یک برگه کاری تایپ کنید. این لیست را انتخاب کنید و از منوی Tools، گزینه Options را انتخاب کنید. در برگه Custom Lists، گزینه Import را انتخاب کنید و روی Ok کلیک کنید. لیست در پنجره Custom Lists ذخیره می شود. حال با تایپ کردن یکی از مدخل های لیست و کشیدن دسته پر کردن، می‌توانید این لیست را در یک صفحه کاری پر کنید.                                                         امید واریم از آموزش اکسل پیشرفته امروز نیز لذت برده باشید.                                                              موفقیت هایتان مستدام  

0

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

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

ابتدا تیترها را انتخاب کنید.

سپس به مسیر Home → Alignment → Orientation →Angle Counterclockwise  بروید تا تیترها زاویه 45 درجه در خلاف جهت عقربه ساعت پیدا کنند.

سپس به مسیر Home → Font → Borders →All Borders  رفته تا برای تیترها Border  اختصاص داده شود(البته می توانید به تمام فضای سلول ها Border  اختصاص دهید)

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

البته می توانستیم تمام مراحل بالا را در قسمت Format cells و با زدن کلیدهای ترکیبی Ctrl + 1 انجام دهیم.                                                                       

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

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

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

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

برای پنهان شدن یک شیت، کافی است بر روی آن راست کلیک کرده و Hide را بزنید.

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

پنهان کردن یک سلول از جدول در EXCEL

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

در پایین بیشتر می آموزید اما بدانید شما می‌توانید هر تعداد ردیف یا ستون را که می‌خواهید خالی نمایش دهید در حالتی که آن‌ها واقعاً خالی نیستند.
برای این کار روی یک یا چند سلول راست کلیک کنید و Format Cells را انتخاب کنید.
در بخش Number، گزینه Custom را از پایین کلیک کنید و سه سمیکالن (,,,) بدون پرانتز در فضای Type وارد کنید.
سپس OK را بزنید و در کمال تعجب می‌بینید که همه بخش‌های انتخابی خالی شده اند! با کلیک کردن بر روی آن‌ها همچنان خالی هستند اما در نوار فرمول ها محتوای آن دیده خواهند شد.
برای دوباره نمایان کردن اطلاعات کافی است مراحل بالا را طی کرده و به جای Custom، گزینه Original را انتخاب کنید. دقت داشته باشید اگر در سلول‌ها هر متنی نوشته شده باشد، با زدن اینتر محو خواهد شد. همچنین با تایپ بر روی سلول‌های مخفی، هر چه پنهان کرده‌اند با متن جدید تایپ شده جایگزین خواهند شد.

پنهان کردن خطوط

برای پنهان شدن خطوط فاصل، کافی است زبانه VIEW را انتخاب کرده و تیک Gridlines را بردارید. همچنین می‌توانید به Page Layout رفته و تیک View زیر بخش Gridlines را نیز بردارید.

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

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

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

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

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

برای این کار ابتدا، بر روی ستون F، راست کلیک کرده و Format Cells را انتخاب کنید.
اکنون روی زبانه Protection کلیک کنید و تیک گزینه Hidden را بزنید. سپس برای محافظت از سلول، بر روی Review کلیک کرده و Protect Sheet را انتخاب کنید.
اگر مایل هستید تا همه نتوانند فرمول های شما را از پنهانی خارج کنند، می‌توانید برای آن گذرواژه ای تعیین کنید. با فشردن دکمه های Ctrl+~ (همان پ بالای تب)  با کلیک بر روی Show Formulas در تب Formulas دیگر قابل مشاهده نخواهند بود.

پنهان کردن کامنت ها 

کامنت ها یا همان اطلاعات اضافی مربوط به هر سلول، به طور خودکار با نوشته شدن،  به صورت یک نشانه قرمز در گوشه راست هر سلول قرار خواهند گرفت. برای تغییر حالت خودکار بر روی  File  و سپس Option،  و زبانه Advanced، روی No comments or indicators کلیک کنید. برای نمایش دادن کافی است Comments and indicators را مجدداً بزنید یا از Show All Comments استفاده کنید.

اگر هم در یک سلول یک متن بلند بالا نوشتید و به نوعی از اندازه خود خارج تر بود، کافی است روی آن راست کلیک کرده و Format Cells را بزنید. سپس از Alignment، در زیر Horizontal، گزینه Fill را انتخاب کنید. اکنون می بینیم که مشکل طولانی بودن برطرف شده است.

0

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

 

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

 

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

 

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

 

برای اینکه چند ستون را به یک اندازه در آورید، با کشیدن ماوس روی عنوان آنها، همه را انتخاب کنید و بعد پهنای یکی از ستون ها را تغییر دهید تا تمام ستون های انتخاب شده به همین پهنا در آیند.

 

 کادر محاوره ای Column Width را باز کنید  

 

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

 

 اندازه دقیق را مشخص کنید  

 

اندازه پهنای ستون را (برحسب تعداد کاراکترها باتوجه به پهنای فونت) تایپ کنید و روی Ok کلیک کنید.

 

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

 

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

 

 کادر محاوره ای Row Height را باز کنید  

 

اندازه دقیق طول سطر را نیز می توان مشخص کرد.سطریا سطرهای مورد نظر را انتخاب کنید و از منوی Format، گزینه Row و بعد گزینه Height را انتخاب کنید. مقدار جدید را وارد کنید (برحسب نقطه. درهر اینچ، 72 نقطه وجود دارد). و روی Ok کلیک کنید تا تغییرات اعمال شوند.


*توجه  *

 بهترین تنظیم در اکسل

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


بیش از یک ستون غیر مجاور

به همان روشی که ستون های مجاور را تنظیم می کنید (مرحله2) می توانید پهنای ستون های غیر مجاور را نیز تنظیم کنید. برای انتخاب ستون های غیر مجاور، اولین ستون را انتخاب کنید، کلیدCtrl را نگه دارید و بعد ستون‌های دیگر را انتخاب کنید.


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

وقتی که داده ای را Paste می کنید، ممکن است بخواهید پهنای ستون های اصلی را نیز علاوه بر داده کپی کنید. برای این کار، از منوی Edit، گزینه Paste و بعد گزینه PasteSpecial را انتخاب کنید. و در کادر محاوره ای که باز می شود، Column Width را انتخاب کنید و روی Ok کلیک کنید.

 

0

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

 

 کادر محاوره ای Format Cells را باز کنید  

 

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

 

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

 

روی برگه Border کلیک کنید. از لیستStyle، یک شیوه انتخاب کنید و از لیستColor برای انتخاب رنگ برای خط مرزی استفاده کنید.

 

 از Presets استفاده کنید  

 

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

 

 یک خط مرزی سفارشی ایجاد کنید  

 

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

 

 از برگه Patterns استفاده کنید  

 

برای بکار بردن الگو روی سلول های انتخاب شده، روی برگه Patterns کلیک کنید. برای پر کردن سلول های انتخاب شده با رنگ، از صفحه Color یک رنگ انتخاب کنید. برای ترکیب کردن دو رنگ در الگو، روی دکمه لیست کشویی Pattern کلیک کنید و رنگ دوم را انتخاب کنید. دوباره لیست را باز کنید تا یک الگو انتخاب کنید. برای مشاهده نتیجه انتخاب ها، به قسمتSample نگاه کنید.

 

انتخاب های فرمت دهی را اعمال کنید  

 

روی Ok کلیک کنید تا کادر محاوره‌ای Format Cells بسته شود و تنظیمات جدید در برگه کاری اعمال شوند.

 

*توجه *

چاپ کردن خطوط شبکه در اکسل

بصورت پیش فرض، خطوط شبکه Excel چاپ نمی‌شوند. برای اینکه آنها را چاپ کنید، از منوی File، گزینه Page Setup را انتخاب کنید. در کادر محاوره ای Page Setup، روی برگهSheet کلیک کنید. در قسمت Print، گزینه Gridlines را انتخاب کنید و روی Ok کلیک کنید. حال برگه کاری را چاپ کنید تا خطوط شبکه را مشاهده کنید.


استفاده از دکمه های Formatting در اکسل

برای اضافه کردن خط مرزی به یک سلول، آن را انتخاب کنید، روی فلش دکمه Borders در نوار ابزار Formatting کلیک کنید و یک شیوه برای خط مرزی انتخاب کنید. برای اضافه کردن رنگ به پس زمینه سلول، روی فلش دکمه Fill Color کلیک کنید و از صفحه رنگ، یک رنگ انتخاب کنید. اگر بخواهید رنگ فونت را تغییر دهید، روی فلش دکمه Fill Color کلیک کنید و یک رنگ انتخاب کنید.

 

0

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

 محدوده منبع را انتخاب کنید 

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

 خط مرزی را با ماوس بکشید 

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

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

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

 از گزینه Add Data استفاده کنید 

 یک روش دیگر برای اضافه کردن داده‌های جدید به نمودار، استفاده از گزینه Add Data است، از منوی Chart، گزینه Add Data را انتخاب کنید تا کادر محاوره‌ای Add Data باز شود.

 داده‌های جدید را انتخاب کنید 

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

 

 داده‌ها را اضافه کنید 

 روی Ok کلیک کنید تا کادر محاوره‌ای Add Data بسته شود و داده‌های جدید به نمودار اضافه شود.


* توجه  *

 تغییر در داده‌های منبع

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

اگر محدوده‌ای که برای نمودار انتخاب کردید اشتباه باشد، می‌توانید با دوباره انتخاب کردن، آن را تغییر دهید. از منوی Chart، گزینه Sonrce Data را انتخاب کنید تا کادر محاوره‌ای Sonrce Data باز شود. محدوده داده‌های جدید را در کادر متنی Data Range تایپ کنید و روی Ok کلیک کنید.
چاپ کردن نمودارهادر excel

برای اینکه فقط نمودار را چاپ کنید و برگه کاری چاپ شود، ابتدا نمودار را انتخاب کنید و از منوی File، گزینه Print را انتخاب کنید. در کادر محاوره‌ای Print، اطمینان حاصل کنید که گزینه Selected Chart انتخاب شده باشد و روی Ok کلیک کنید.

0

برنامه اکسل برای انجام محاسبات ریاضی، از امکاناتی نظیر «فرمول نویسی در سلول ها» استفاده می کند. در این درس با امکانات محاسباتی اکسل و کار با فرمول ها آشنا خواهیم شد.

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

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

 

روش درج فرمول در سلول ها 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 نام دارد، باید ورودی های تابع را وارد کنیم.

 

 

 

 

 

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

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

 

0

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

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

 

تحلیل تک متغیره در Data Table اکسل:

از این حالت زمانی استفاده می کنیم که بخواهیم نتیجه تغییرات صرفاً یک متغیر رادر فرمول  مشاهده کنیم.فرض کنید هزینه سوخت خودرو به ازای هر کیلومتر 240 واحد هست و ما میخواهیم تاثیرات مسافت طی شده خودرو را بر هزینه سوخت آن به ازای مسافت های یک تا ده کیلومتر تحلیل کنیم. برای اینکار ابتدا فرمول A1*240= رو در سلول A2 مینویسیم. سپس اعداد 1 تا 10 رو در سلول های B1 تا K1 قرار میدهیم. و از تب DATA و بخش Data Tools رو گزینه What-If Analysis کلیک می کنیم و در منویی که باز می شود  گزینه Data Table را انتخاب می کنیم.

 

با انتخاب این گزینه پنجره زیر باز می شوذد.

 

حالا روی فلش قرمز رنگ گزینه Row Input Cell کلیک میکینم. با کلیک روی این گزینه پنجره زیر باز می شود که ما باید در  سلول مربوط به متغیر مورد نظررا  انتخاب کنیم که این سلول همون سلول A1 هست.

 

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

 

تحلیل دو متغیره در Data Table اکسل:

در بخش قبل حالت تک متغیره ابزار Data Table اکسل رو بررسی کردیم. حالا میخوایم حالت دو متغیره Data Table رو امتحان کنیم بنابراین به یک مثال کاملتر احتیاج داریم. فرض کنید رشد یا کاهش فروش سازمان ما تابع دو متغیر مقدار افزایش تبلیغات و میزان افت کیفی محصول باشه به طوری که با هر واحد افزایش تبلیغات 17درصد به فروش سازمان افزوده بشه و با هر واحد کاهش کیفیت محصول 30درصد  از فروش سازمان کم بشه. میخوایم اثرات تغییر این دو متغیر رو روی افزایش یا کاهش فروش سازمان بررسی کنیم. دامنه بررسی ما برای متغیر کاهش کیفیت محصول بین 0.5 تا 3 واحد و برای متغیر افزایش تبلیغات بین 0 تا 5 واحد هست. برای شروع ابتدا در سلول A3 فرمول افزایش فرمول تغییرات فروش سازمان رو که A1*-0.3+A2*0.17= مینویسیم. با توجه به فرمولی که ما نوشتیم سلول A1 معرف متغییر کاهش کیفیت و سلول A2 معرف متغییر افزایش تبلیغات هست.

 

حالا مثل حالت تک متغیره از تب DATA گزینه Data Table رو انتخاب می کنیم و در بخش Row Input Cell سلول A1 و در بخش  Column Input Cell سلول A2 رو قرار میدیم و پنجره Data Table رو OK می کنیم. با اینکار درصد تغییرات فروش برای مقادیر مختلف افزایش تبلیغات و کاهش کیفیت محصول در جدول نمایش داده میشه.

 

به عنوان مثال اگر کیفیت محصول یک واحد افت کنه و ما 4 واحد تبلیغاتمون رو افزایش بدیم فروش سازمان 38درصد افزایش پیدا می کنه یا اگر کیفیت محصول ما 3 واحد افت کنه، علیرغم افزایش 5 واحدی تبلیغات هم فروش سازمان 5درصد افت خواهد داشت