ا ل پیشرفته

پست های وبلاگ ا ل پیشرفته از سایتهای وبلاگی با ذکر منبع به صورت خودکار بازنشر شده و در این صفحه نمایش داده شده است. در صورتیکه این اطلاعات دارای محتوای نامناسب بوده و یا دارای هر گونه تخلف میباشد بر روی گزینه ‘درخواست حذف’ کلیک نمائید

انواع داده در ا ل

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

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

اعداد در ا ل

اعداد در ا ل از اهمیت بسیاری برخوردارند، در مراتب حرفه ای تر کار با ا ل و مخصوصاً در یادگیری و استفاده از برنامه نویسی ویژوال بیسیک در ا ل، به اهمیت شناخت و تفکیک انواع داده در ا ل و کار با اعداد پی خواهیم برد. حتی اعداد نیز خود به چند دسته تعریف میشوند که در توصیف نوع متغیرها در ماکرو نویسی در ا ل بسیار مهم میباشد. داده های عددی در ا ل ممکن است حتی به فرمت متنی نیز ذخیره شوند که انجام اینکار گاهی لازم میباشد.  توابع ریاضی و مثلثاتی در ا ل (math & trig functions) از جمله توابعی هستند که بر روی داده های عددی عملیات انجام میدهند.

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

شما می توانید در ا ل نحوه نمایش اعداد خود را نیز تعیین نمایید، مانند تعداد اعشار، نمایش عدد به صورت درصد یا به صورت عدد علمی، در مباحث بعدی بیشتر به این مورد اشاره خواهیم نمود.

مالی و حسابداری در ا ل

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

برای کار با توابع حسابداری و انجام امور مالی در ا ل لازم است کاربران با داده های نوع currency آشنا باشند، لذا این نکته اهمیت این نوع داده ها را آشکارتر مینماید.

تاریخ و زمان در ا ل

از جمله پر کاربردترین ابزارهای ا ل در حوزه های امور اداری، حسابداری و مستند سازی، توابع تاریخ و زمان (date & time functions) می باشند، برای یادگیری کار با توابع تاریخ و زمان و البته انجام عملیات بر روی سلول های حاوی تاریخ و زمان، لازم است کاربران درک درستی از داده های نوع تاریخ و زمان داشته باشند.

 متن در ا ل

استفاده از توابع و داده های متنی چه در محیط ا ل و چه در زبان برنامه نویسی ویژوال بیسیک در ا ل همواره در سطوح بالاتر و حرفه ای تر قرار گرفته و این موضوع برای فایل های ا ل با زبان فارسی یادگیری مهارت های بیشتری را می طلبد. توابع متنی در ا ل (text functions in excel) از جمله توابع بسیار مهم و عمومی در ا ل به شمار میروند که یت آنها انجام عملیات بر روی داده های متنی میباشد. لذا شناخت داده های متنی و روش تعریف آنها از اهمیت بسیاری برخوردار است.

داده های boolean در ا ل

داده های boolean در ا ل داده هایی هستند که صرفاً میتوانند یکی از دو ح true و false باشند. لازم به ذکر است در ا ل و ویژوال بیسیک، همواره عبارت false برابر با عدد صفر و عبارت true برابر با عدد یک میباشد. پس داده های boolean داده هایی با دو مقدار صفر یا یک میباشند. البته داده ی نوع null هم وجود دارد که به معنی not true و not false است. در آینده در مورد این نوع داده ها بیشتر اطلاعات ب خواهیم کرد.

توابع منطقی در ا ل (logical functions) که از ارکان ا ل به شمار میروند و توابع اطلاعات در ل (information functions) از جمله توابعی هستند که با داده های نوع boolean سر و کار دارند.

 

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : انواع داده در ا ل - ا ل ,داده ,توابع ,کاربران ,متنی ,انجام ,انواع داده ,فرمول نویسی ,خواهیم نمود ,شناخت انواع ,ا ل برای ,جمله توابعی هستند ,نویسی ویژوال بیسیک ,برن
انواع داده در ا ل ا ل ,داده ,توابع ,کاربران ,متنی ,انجام ,انواع داده ,فرمول نویسی ,خواهیم نمود ,شناخت انواع ,ا ل برای ,جمله توابعی هستند ,نویسی ویژوال بیسیک ,برن
مفاهیم ماکرو نویسی در ا ل

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

ماکرو چیست؟

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

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

 

procedure

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

برای آشنایی بیشتر با پروسیژرها و انواع آنها مقاله ی پروسیژ ها در ویژوال بیسیک را مطالعه نمایید.

در ماکرو نویسی procedure ها میتوانند sub، function یا property باشند. در ادامه به معرفی آن ها میپردازیم.

sub

ساب یا همان sub routine یک نوع procedure میباشد که با عبارت sub شروع شده و با عبارت end sub تمام میشود، کد های قرار گرفته در بین این دوعبارت عملیات خاصی را با هم انجام میدهند اما قرار نیست که وجی مشخصی ارایه کنند، یعنی نمیتوان نوشت وجی sub برابر است با یک مقدار یا عبارت مشخص. sub ها پرکاربردترین procedure ها در ماکرو نویسی در ا ل میباشند. به این نوع پروسیژر ها به صورت مختصر روتین (routine) هم میگویند، در مقاله ی procedure ها در ماکرو نویسی این نوع پروسیژر مورد بررسی قرار گرفته است.

property

پروپرتی یا همان property procedure هم مانند sub یک نوع procedure میباشد که با property set یا property get یا property let شروع شده و با end property تمام میشود. کد های قرار گرفته در بین این دو عبارت برای cl module ها خواص (properties) تعریف و توصیف میکنند، به عبارت ساده تر کدها قرار است برخی خواص یک شیء خاص را تعریف و مورد بررسی قرار دهند، با مفهوم شیء آشنا خواهیم شد.

 

function

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

نمیتوان procedure ها را درون یکدیگر تعریف کرد به عبارت دیگر، خط اول و آ هر procedure یک module level code (کدی که در سطح یک ماژول تعریف شده و شناخته شده است) میباشد.

ماژول

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

انواع ماژول ها

انواع ماژول ها عبارتند از:

  • ماژول های نوع standard module یا همان code module که ماژول هایی هستند که میتوانند توسط سایر ماژول ها در یک پروژه مورد استفاده قرار گیرند.
  • ماژول های نوع cl module که برای تعریف یک cl خاص و مشخص تعریف و مورد استفاده قرار گرفته اند. این نوع ماژول ها یک کلاس (cl ) را توصیف (define) کرده و خواص (properties) و روش های (method definitions) آن را تعریف میکنند.
  • ماژول های نوع form module که حاوی کد های مربوط به یک فرم (form) هستند. با فرم ها به تفصیل آشنا خواهیم شد. فرم ها خود دارای اشیا متعدد میباشند که کدهای مربوی به properties هریک از آنها در این فضا قرار میگیرد.
  • ماژول های نوع object modul که حاوی کد های مشخص برای یک شیء (object) میباشند، ماژول ها نوع cl module، form module و document module همگی جز این دسته میباشند به این علت که خود form ها یا document ها یا cl ها در ویژوال بیسیک اشیا محسوب میشوند.

 declaration

کد های غیر اجرایی (یا غیر قابل اجرا non executable) میباشند که برای نام گذاری procedure ها، ثابت ها (constants)، متغیر ها (variables) و مشخصات (characteristics) آنها- مانند نوع داده ها- میباشند. هر ماژول در ابتدا دارای سطرهایی تحت عنوان declarations میباشد که منظور چند سطر ابت ماژول است که برخی تنظیمات مربوط به سراسر آن ماژول در قالب کد نوشته میشود. در آینده با این بخش اشنا خواهیم شد. یکی از دلایل نیاز به تعریف چند ماژول برای یک پروژه، متفاوت بودن declaration ها ی آنها با توجه به کاربردشان میباشد.

میتوان ماژول را به صورت یک سری declarations که به دنبال آن ها procedure ها قرار دارند هم تعریف نمود. به کد ها و متغیرهایی که به صورت decorations تعریف میشوند عبارت module level codes و module level variable اطلاق میگردد. متغیر های نوع module level در تمام procedure های قرار گرفته در آن ماژول معتبر میباشند.

فرم

فرم ها یا form ها اشیا تبادل اطلاعات با کاربر (dialog box) یا پنجره هایی میباشند که در برگیرنده ی کنترل ها (controls) هستند. نوع تخصصی تر فرم ها multiple document interface ها یا فرم های mdi میباشند که خود حاوی فرم های کوچکتر (child forms) در درون خود هستند. فرم ها از دو قسمت تشکیل شده اند: یکی خود شیء فرم و کنترل های قرار گرفته در آن و دیگری ماژول در برگیرنده ی کدهای اجرایی آن فرم و اشیا درون آن (که به آن form module میگوییم). با انتخاب گزینه های view object و view code (کد های فرم ها در فضای form module قرار دارند) که با کلیک راست بر روی فرم نمایش داده میشوند، میتوانیم هر قسمت را ببینیم یا ویرایش نماییم.

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

object box و procedure box

در فضای کد نویسی form ها (form modules و سایر module ها) در بالا و سمت چپ کادر object box وجود دارد که لیستی از تمام اشیا قرار گرفته در form در آن قرار دارد، هر کدام از اشیا را که میخواهید انتخاب نمایید، حال در سمت راست کادر فوق کادری تحت عنوان procedure box قرار دارد، با باز این کادر متوجه میشویم که مثلاً برای یک کمبوبا چندین procedure وجود دارد (برای سایر module ها با باز این قسمت لیستی از procedure های موجود در آن ماژول نمایش داده میشود)، مثلاٌ click، با انتخاب کلیک یک private sub combobox1_click ایجاد میشود که در واقع یک procedure میباشد که فقط در سطح همین ماژول اعتبار دارد (به دلیل وجود کلمه ی privet) و حاوی کد هایی است که در صورت کلیک بر روی این کمبوبا اجرا میشوند، در واقع در اینجا میخواهید مشخص کنید که کلیک بر روی کمبو با چه نتایجی ایجاد کند.

برای درک این قسمت حتماً باید مفهوم اشیا (object) و پروپرتی (properties) را درک کرده باشید.

project

یک یا یک سری ماژول را یک project مینامیم، مثلاً شما ممکن است برای انجام کاری ناچار به ایجاد چند ماژول متفاوت شوید (به علت وجود declaration های مختلف) به این مجموعه یک project میگوییم. وقتی ما با ا ل سر و کار داریم، project به معنی فایل ا لی هست که ماژول ها در آن قرار گرفته اند، در یک فایل ا ل میتوانید به تعداد بیشما ماژول بوجود بیاورید.

اشیاء

اشیا همان object ها هستند که به صورت خلاصه میتوان آن ها را ترکیبی از کدها و داده ها فرض نمود که میتوان بر روی آنها به صورت یکجا عملیات انجام داد و یا آن ها را به صورت یکجا و یک واحد مورد استفاده قرار داد. به عنوان مثال application یک شیء است، یا فرم ها یا کنترل ها همگی شیء میباشند. اشیا خواص (properties) مربوط به خود را دارند که هرکدام روش(method) استفاده ی مختص خود را دارا میباشند.

مفهوم شی (object) در ماکرو نویسی در ا ل یکی از مهمترین مفاهیم است که برنامه نویسان باید درک درستی از آن بدست بیاورند، در واقع نرم افزار ا ل که در آن ماژول های خود را بوجود می آورید application مورد خطاب ویژوال بیسیک میباشد، اگر دقت کنید vba مخفف عبارت visual basic for applications میباشد که در این سایت application ا ل مورد بررسی قرار گرفته است، پس در هر جایی که عبارت application به کار برده شده منظور ا ل میباشد.

زبان ویژوال بیسیک برای application های دیگر هم مورد استفاده قرار میگیرد که فعلاً موضوع بحث این سایت نیست، خود application ا ل یک شی (object) است، اشیا در ویژوال بیسیک میتوانند در برگیرنده اشیا دیگر باشند، مثلاً شی application در برگیرنده ی شی workbook میباشد که به معنی فایل ا لی که در آن کد نویسی میکنید میباشد که خود در برگیرنده ی شی (object) شیت (worksheet) میباشد و شیت در برگیرنده اشیا دیگر میباشد، این بحث در مقاله ای جداگانه به تفصیل بررسی خواهد شد.

هر شیء توسط یک کلاس توصیف و تعریف (define) شده است.

properties

پروپرتی یا properties در مورد اشیا همانند تنظیمات آنها میباشند، مثلاً یک شی فونت (font) دارای تنظیماتی مانند bold بودن میباشد که یک پروپرتی boolean (دو ح دارد یا true و یا false) است، شی فونت زیر مجموعه شی range است.

کلاس

کلاس ها یا cl ها در واقع توصیف هایی (definition) برای object ها میباشند، کلاس به عنوان یک template که یک شیء را در زمان اجرای کد ها بوجود می آورند عمل میکنند. کلاس ها خواص (properties) اشیا و روش های استفاده شده برای کنترل رفتار شی را توصیف میکنند. کلاس ها و ماژول ها خود جز اشیا هستند و خود آنها جزیی از شیء vba میباشند.

 

پنجره کد نویسی

فضایی که در آن کد نوشته میشود را پنجره کد نویسی یا code window مینامیم، پنجره کد نویسی میتواند انواع ماژول و یا پنجره کد نویسی مربوط به هر sheet باشد.

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

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

container

یک شیء که در بر گیرنده ی اشیاء دیگر میباشد را container میگوییم.

method

متد ها (methods) یا روش ها، procedure هایی هستند که بر روی اشیا عمل میکنند. هر متد یک procedure است که بر روی یک شیء عمل میکند.

range

محدوده یا range به یک یا چند سلول ا ل گفته میشود، هر range یا از یک سلول تشکیل شده و یا از تعدادی سلول که در کنار یکدیگر قرار دارند. به عنوان مثال a1 یک range است که دارای یک سلول میباشد و a1:b3 هم یک range با تعداد شش سلول میباشد.

cells

cells هم مانند range برای اشاره به سلول های ا ل در کدهای ویژوال بیسیک استفاده میشود، با این تفاوت که cells تنها به یک سلول اشاره میکند.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : مفاهیم ماکرو نویسی در ا ل - ماژول ,نویسی ,ا ل ,procedure ,میباشد ,بیسیک ,ویژوال بیسیک ,قرار گرفته ,ماکرو نویسی ,مورد استفاده ,استفاده قرار ,زبان ویژوال بیسیک ,زبان برنامه نویس
مفاهیم ماکرو نویسی در ا ل ماژول ,نویسی ,ا ل ,procedure ,میباشد ,بیسیک ,ویژوال بیسیک ,قرار گرفته ,ماکرو نویسی ,مورد استفاده ,استفاده قرار ,زبان ویژوال بیسیک ,زبان برنامه نویس
راهایی برای سرعت بخشی به کار بردن توابع در ا ل

   سرعت بخشیدن کاربا توابع در ا ل                                                                                                                                         فرمولهای excel در دسته های مختلفی دسته بندی شده اند که برای کارهای ریاضی، مالی ، آماری و کار با رشته های متنی و کار با داده های زمان و …. به کار می روند.
توابع ریاضی مشتمل است بر انواع و اقسام توابع مثلثاتی، قدر مطلق، تقسیم باقیمانده ، توان، مجموع مربعات و ….
توابع متنی شامل جستجو در یک متن، حذف و جایگزینی بخشی از یک متن و …. می باشد. توابع آماری برای محاسبه واریانس، انحراف از معیار و کلی چیزهای دیگر که آرزویشان را داریم هم وجود دارد برای داشتن پیش زمینه ذهنی سعی کنید حتماً سری به لیست این فرمولهای بزنید تا در مواقع وم از امکانات تعبیه شده در خود excel استفاده کنید. تابع نویسی از یک علامت = شروع می شود به محض وارد یک علامت مساوی در یک خانه خالی excel می فهمد مقدار این خانه یک داده تنها نیست بلکه باید آنرا حساب کند.
وقتی نیاز به یک تابع را احسای می کنید به عنوان نخستین سؤال این پرسش مطرح می شود که از کجا تابع مورد نظر خود را بیاییم؟
ممکن است شما دقیقاً بدانید می خواهید چه کاری انجام دهید ولی اطلاعی نداشته باشید که آیا در ا ل توابع مورد نیاز شما پیش بینی شده است یا خیر؟ از کجا بدانیم تابعی که مورد نیاز ما است کجاست؟ شاید هم تابعی را چند ماه پیش به کار برده اید ولی هم اکنون آنرا فراموش کرده اید. فرض کنید می خواهید میزان بازپرداخت ماهانه برای یک وام 180 هزار دلاری را که ظرف ده سال با بهره 6 درصد باید بازپرداخت شود محاسبه کنید. کافی است در خط فرمول قرار بگیرید و دکمه function insert را بفشارید و در آنجا عبارت مورد نظر خود را در بخش جستجو وارد کنید. یا حتی می توانید نوع تابع مورد نظر خود را از بخش category انتخاب نموده و در لیست مربوطه تک تک روی اسامی هر یک از توابع بایستید و تعریف آنرا بخوانید. مثلاً تابع pmt همان تابعی است که ما بدنبال آن هستیم و در تعریف آن آورده شده است، محاسبه میزان باز پرداخت ماهانه برای وام با اقساط ثابت و بهره ثابت با انتخاب این تابع کادری باز می شود که آرگومانهای تابع را از ما دریافت می نماید.
قبل از این کار با کلیک روی گزینه this topic help on می توانید نکات راهنما در مورد این تابع را بخوانید. در این راهنما syntax یا به عبارتی فرم دقیق تابع و مشخصات آرگومانهای ورودی آن آورده شده است و همچنین یک مثال کامل از یک نمونه کاربرد تابع ذکر شده است. که می توانید این مثال را کپی کرده و در فایل خود بچسبانید و نحوه کار آن را ببینید.
پنجره ای که آرگومانهای تابع را دریافت می کند به تفکیک مقادیر ورودی تابع ما را نشان داده است و منتظر می ماند شما مقدار هر یک از وارد نمایید. در اینجا می توانید به هر یک از این آرگومانها یک مقدار عددی یا آدرس یک خانه تخصیص دهید. آرگومانهایی که ارائه مقدار آنها ضروری است با رنگ مشکی تیره مشخص شده اند و حتماً باید مقدارشان را مشخص کنید. بقیه آرگومانها مقدارشان اختیاری است و اگر معلوم نکنید به صورت پیش فرض به آنها مقدار داده می شود.
در کادر مقابل هر آرگومان که قرار بگیرید در پایین ترین بخش صفحه تعریفی از ویژگیها و مشخصات این آرگومان آورده می شود.
تابع 5 pmt(pate. noper.pv.type)‌آرگومان دریافت می دارد که سه تای اول آن اجباری هستند و دو تای آ ی اختیاری.
برای محاسبه اقساط ماهانه وام ده ساله 180 هزاردلاری با بهره 6 درصد در بخش rate که نشانگر بهره است مقدار 12/6% را باید وارد کنید. بهره 6 درصد سالیانه برای هر ماه معادل نیم درصد است.
توجه داشته باشید که نرخ سود را باید ماهانه وارد نمود نه سالانه. آرگومان noper مبین تعداد کل اقساط وام است که در اینا معادل 12*10=120 خواهد بود. pv هم نشانگر کل میزان وام دریافتی بدون در نظر گرفتن بهره و معادل 180000 است. با فشردن دکمه ok نتیجه کار در کاربرگ شما پدیدار می شود ($.79 . 19) یعنی شما هر ماه تقریباً هزار و هفتاد و نه دلار باید بپردازید. چون این مقدار را باید بپردازید و به اصطلاح حسابداری پرداختنی است در داخل پرانتز به عنوان یک عدد منفی – و با رنگ قرمز نشان داده می شود.
مراحلی که در بالا توضیح دادیم برای وقتی است که شما خود تابع و یا آرگومانهای آنرا به درستی نمی شناسید. ولی در دفعات بعدی که نام تابع را به خاطر می آورید کافی است در داخل خود کاربرگ نام تابع را وارد نمایید. بدیهی است باید محتوای خانه ای که قرار است از طریق یک تابع محاسبه شود با یک علامت مساوی شروع شود. ا ل با نشان دادن نوشته هایی با زمینه زرد رنگ به نام tips به شما راهنمایی های لازم را در خصوص ترتیب و مشخصات آرگومانها می نماید. آرگومان جاری که در حال وارد نمودن آن هستید پررنگ تر دیده می شود. پارامترهایی که مقدار دادن به آنها اختیاری است در داخل کروشه نشان داده می شوند.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : راهایی برای سرعت بخشی به کار بردن توابع در ا ل - تابع ,کنید ,توابع ,داده ,بهره ,مورد ,نشان داده ,آرگومانهای تابع ,وارد نمایید ,باید بپردازید ,وارد کنید
راهایی برای سرعت بخشی به کار بردن توابع در ا ل تابع ,کنید ,توابع ,داده ,بهره ,مورد ,نشان داده ,آرگومانهای تابع ,وارد نمایید ,باید بپردازید ,وارد کنید
رسم نمودار دایره ای و doughnut در ا ل

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

نمودار doughnut در ا ل

نمودار doughnut در ا ل مانند نمودار دایره ای میباشد وتنها در ظاهر با آن تفاوت دارد، ضمن اینکه در ادامه با قابلیتی از نمودار doughnut (دونات) آشنا میشویم که در نمودار دایره ای وجود ندارد،                           

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

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

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

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

برای جلوگیری از دوباره کاری در ترسیم نمودار در ا ل، هنگام insert نمودار در ا ل و طی مراحل فوق یک سلول خالی را در ح انتخاب قرار دهید، در اینجا اگر دقت کنید میبینید سلول a1 درح انتخاب قرار گرفته، بدین ترتیب پس از انتخاب نمودار مورد نظر یک صفحه ی خالی برای شما نمایش داده میشود که میتوانید رسم نمودار در ا ل را از آن آغاز نمایید.                                                                                                                    کادر خالی ایجاد شده در صفحه ی ا ل شما آماده ی معرفی داده ها برای رسم نمودار دایره ای در ا ل میباشد، بر روی صفحه کلیک کرده و گزینه ی select data را انتخاب کنید، با انتخاب این گزینه پنجره ی انتخاب داده برای نمودار ا ل فعال میگردد. در اینجا شما میتوانید با کلیک بر روی add نمودار دایره ای جدید خود را در ا ل ایجاد نمایید.                           

شکل ظاهری نمودار و نحوه ی نمایش دادن نام آیتم ها و سهم آنها را به راحتی میتوانید در تب design و تب های دیگر تغییر دهید                                                                          در مورد نمودار doughnut شما میتوانید چند سری داده را همزمان نشان دهید، برای این کار مراحل رسم نمودار را دنبال نمایید و یک سری جدید را اضافه نمایید،                                     حلقه داخلی روش اندازه گیری اول و حلقه بیرونی، روش اندازه گیری دوم را نشان میدهد. شکل ظاهری و نحوه نمایش داده ها را میتوانید به دلخواه خود ویرایش نمایید.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : رسم نمودار دایره ای و doughnut در ا ل - نمودار ,ا ل ,دایره ,میتوانید ,داده ,نمایید ,نمودار دایره ,نمودار doughnut ,اندازه گیری ,انتخاب قرار ,نمایش داده
رسم نمودار دایره ای و doughnut در ا ل نمودار ,ا ل ,دایره ,میتوانید ,داده ,نمایید ,نمودار دایره ,نمودار doughnut ,اندازه گیری ,انتخاب قرار ,نمایش داده
فرمول‌نویسی آرایه‌ای در ا ل
    فرمول نوسی آرا یه ای در ا ل                                                                                                                                        بااستفاده از فرمول‌نویسی آرایه‌ای (array formulas) می‏‌توانید فرمول‏‌ها را به صورت کلی بنویسید و روی چندین سلول اعمال کنید. ساده‌ترین کاربرد فرمول نویسی آرایه‌ای، فرمول نویسی روی ستون‏‌ها است. به این صورت که دیگر به جای استفاده از نشانی یک سلول برای نگارش فرمول، شما می‏‌توانید محدوده‌ی یک ستون را در فرمول مورد استفاده قرار دهید. در ادامه مطلب باما همراه باشید با روش استفاده از فرمول نویسی آرایه‌ای آشنا شوید.
 
برای مثال مطابق شکل زیر، برای به دست آوردن حاصل‌ضرب اعداد ستون d در ستون e، در روش سنتی باید فرمول e1*d1= را در سلول f1 بنویسید و سپس فرمول را در سلول‏‌های متناظر مربوط به سطرهای دیگر کپی نمایید.
 
با استفاده از فرمول نویسی آرایه‌ای درا ل شما می‌‏توانید به صورت یکجا محدوده‌ی (f1:f3) در ستون f، را انتخاب نموده و سپس عبارت d1:d3*e1:e3= را در آن بنویسید. برای راحتی کار می‌توانید به وسیله یروش نام‌گذاری محدوده‌، یک نام برای محدوده‌‌های d1:d3 و e1:e3 اختصاص دهید. 
  

البته تنها تفاوت فرمول نویسی آرایه‌ای با فرمول نویسی معمولی نحوه‌ی تائید فرمول می‌باشد. در واقع در فرمول نویسی آرایه‌ای بجای استفاده از کلید enter، باید سه کلید ctrl + shift + enter را همزمان فشار داد. به همین خاطر به فرمول نویسی آرایه‌ای، فرمول‌های سی.اس.ای (cse formulas) هم گفته می‏‌شود. پس از تائید مشاهده می‌کنید که در اطراف فرمول d1:d3*e1:e3= دو علامت کروشه {} ایجاد شده است. (دقت کنید که این دو کروشه بصورت خ ر پس از استفاده از سه کلید ctrl + shift + enter ایجاد می‌شود بنابراین نباید بصورت دستی در ابتدا و انتهای فرمول کروشه بگذارید.)

 

حال اگر تصمیم داشته باشید که مجموع سلول های f1:f3 را محاسبه کنید می‌توانید با انتخاب سلول f4، فرمول (sum(f1:f3= را بنویسید و تائید کنید. اگرچه اگر هدف اصلی محاسبه مجموع ضرب ستون d و e بود می‌توانستید همان ابتدا از فرمول نویسی آرایه‌ای استفاده کنید و در سلول f4 فرمول (sum(d1:d3*e1:e3= را نوشته و برای تائید سه کلید ctrl + shift + enter را همزمان فشار دهید.

* برای محاسبه مجموع ضرب ستون d و e، می‌توانید از تابع sumproduct نیز استفاده نمائید.

یک نمونه پرکاربرد از فرمول نویسی آرایه‌ای محاسبه خطا در محاسبات ی است. بطور مثال برای مقایسه اعداد پیش‌بینی شده با اعداد تجربی از عبارت متوسط ​​درصد خطای مطلق ((mean absolute percentage error (mape) یا (mean absolute percentage deviation (mapd استفاده می‌شود. mape را می‌توان با کمترین میزان محاسبات با استفاده از فرمول نویسی آرایه‌ای تعیین نمود.
به این صورت که با استفاده از روش نام‌گذاری محدوده‌، برای اعداد پیش‌بینی شده نام forecasted_values و برای اعداد تجربی نام  actual_values را انتخاب نمائید. سپس در سلولی که می‌خواهید خطا را محاسبه کنید فرمول زیر را وارد نموده و سپس برای تائید سه کلید ctrl + shift + enter را همزمان فشار دهید.

=average(abs((actual_values-forecasted_values)/actual_values)))


عنوان وبلاگ : ا ل پیشرفته
برچسب ها : فرمول‌نویسی آرایه‌ای در ا ل - فرمول ,نویسی ,استفاده ,آرایه‌ای ,سلول ,ستون ,فرمول نویسی ,نویسی آرایه‌ای ,shift enter ,کلید ctrl ,ctrl shift ,فرمول نویسی آرایه‌ای ,ctrl shift enter ,mean absolute percentage
فرمول‌نویسی آرایه‌ای در ا ل فرمول ,نویسی ,استفاده ,آرایه‌ای ,سلول ,ستون ,فرمول نویسی ,نویسی آرایه‌ای ,shift enter ,کلید ctrl ,ctrl shift ,فرمول نویسی آرایه‌ای ,ctrl shift enter ,mean absolute percentage
کابرد علامت $ در ا ل

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

زمانی که فرمول a1*b1 را به سمت ردیفهای پایین تر بسط دهید نتیجه بصورت a2*b2، a3*b3،.... تغییر خواهد کرد، همچنین اگر فرمول a1*a2 را به سمت راست (پیش فرض) بسط دهید نتیجه بصورت b1*b2, c1*c2, .... خواهد بود.

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

چند مثال:

a$1 (down): a$1,a$1,a$1,a$1,....

$a1 (down): $a2,$a3,$a4,....

a$1 (right): b$1,c$1,d$1,....

$a1 (right): $a1,$a1,$a1,$a1,...

$a$1 (down or right): $a$1,$a$1,$a$1,....

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

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

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : کابرد علامت $ در ا ل - فرمول ,علامت ,ثابت ,right ,down ,استفاده ,نتیجه بصورت ,دهید نتیجه ,دهید نتیجه بصورت
کابرد علامت $ در ا ل فرمول ,علامت ,ثابت ,right ,down ,استفاده ,نتیجه بصورت ,دهید نتیجه ,دهید نتیجه بصورت
نمودار پویا در ا ل

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

 
عنوان وبلاگ : ا ل پیشرفته
برچسب ها : نمودار پویا در ا ل - ا ل ,پویا ,نمودار ,نمودار پویا
نمودار پویا در ا ل ا ل ,پویا ,نمودار ,نمودار پویا
نمایش ا ل به روش های مختلف

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

دیدن دو sheet همزمان در ا ل

ممکن است کاربر نیاز داشته باشد دو sheet از workbook خود را در کنار هم ببیند، برای این کار می توان فایل ا ل موجود را دوباره باز کرد و دو فایل ا ل (که در واقع ی) را در کنار هم دید. به منظور انجام این کار وارد تب view شده و در گروه window گزینه ی new window را انتخاب می کنیم، حال برای تعیین نحوه نمایش دو فایل به صورت همزمان از گزینه ی arrange all استفاده می کنیم.

freeze در ا ل

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

در دستور freeze سه ح وجود دارد، می توانید انتخاب کنید یک سطر و یا یک ستون را همیشه ببیند و یا اینکه با انتخاب گزینه ی اول می توانید چند سطر و ستون را freeze کنید. در فایل ویدئوی قرار گرفته در انتهای این مقاله، روش استفاده از ابزار freeze در ا ل توضیح داده شده است.

split در ا ل

اگر بخواهید sheet موجود را به دوبخش تقسیم کنید می توانید از split استفاده نمایید. استفاده از split زمانی کاربرد دارد که در حال کار بر روی یک فایل ا ل طولانی میباشید، در این ح ممکن است برای فرمول نویسی ها مجبور باشید قسمت های بالایی و پایینی شیت را در کنار یکدیگر ببینید، split شیت کار شما را بسیار راحت مینماید. ابزار split هم در تب view و در گروه window قرار گرفته است.

costume view در ا ل

فرض کنید احتیاج دارید یک نمای خاص (view) از ا ل را دائماً کنترل کنید، به عنوان مثال شما در حال تکمیل بانک اطلاعات هستید و اطلاعاتی در sheet اول وجود دارند که دائم باید داده های ورودی را با آنها چک کنید. در این ح به جای اینکه هر دفعه به sheet  اول رفته و دنبال آن اطلاعات باشید نمای مورد نظر در custom view ذخیره نمایید. ابزار custom view در تب view و در گروه workbook view قرار گرفته است.

 

watch window در ا ل

حال اگر بخواهید بدون اینکه view خود را عوض کنید به یک سری اطلاعات در فایل ا ل خود دسترسی سریع داشته باشید می توانید از دستور watch window استفاده نمایید. با ایجاد پنجره نظارت می توانید محتوا و مقدار یک سلول خاص را ذخیره نمایید و اگر به آن احتیاج داشته باشید به جای گشتن به دنبال آن می توانید از طریق watch window به آن دسترسی داشته باشید. این ابزار در نسخه های ا ل ۲۰۱۰ به بعد از منو حذف شده است. در ا ل نسخه ۲۰۰۷ از مسیر زیر میتوانید به ابزار watch window دسترسی پیدا کنید:

صفحه نمایش ا ل

صفحه نمایش ا ل می تواند به صورت صفحه گسترده، که در ا ل به آن normal گفته می شود و یا به صورت های page break یا page layout نمایش داده شود. در حالات page layout، ا ل مانند نرم افزار word به صورت صفحه ای نمایش داده می شود و می توان برای صفحات header و footer هم تعیین نمود. در ح page break تنها سلول های دارای اطلاعات صفحه بندی می شوند و فضاهای خالی صفحه بندی نمی شوند.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : نمایش ا ل به روش های مختلف - ا ل ,view ,freeze ,window ,فایل ,صفحه ,فایل ا ل ,watch window ,داشته باشید ,استفاده نمایید ,نمایش ا ل
نمایش ا ل به روش های مختلف ا ل ,view ,freeze ,window ,فایل ,صفحه ,فایل ا ل ,watch window ,داشته باشید ,استفاده نمایید ,نمایش ا ل
کابرد علامت $ در ا ل

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

زمانی که فرمول a1*b1 را به سمت ردیفهای پایین تر بسط دهید نتیجه بصورت a2*b2، a3*b3،.... تغییر خواهد کرد، همچنین اگر فرمول a1*a2 را به سمت راست (پیش فرض) بسط دهید نتیجه بصورت b1*b2, c1*c2, .... خواهد بود.

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

چند مثال:

a$1 (down): a$1,a$1,a$1,a$1,....

$a1 (down): $a2,$a3,$a4,....

a$1 (right): b$1,c$1,d$1,....

$a1 (right): $a1,$a1,$a1,$a1,...

$a$1 (down or right): $a$1,$a$1,$a$1,....

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

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

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : کابرد علامت $ در ا ل - فرمول ,علامت ,ثابت ,right ,down ,استفاده ,نتیجه بصورت ,دهید نتیجه ,دهید نتیجه بصورت
کابرد علامت $ در ا ل فرمول ,علامت ,ثابت ,right ,down ,استفاده ,نتیجه بصورت ,دهید نتیجه ,دهید نتیجه بصورت
فرمول‌نویسی آرایه‌ای در ا ل
    فرمول نوسی آرا یه ای در ا ل                                                                                                                                        بااستفاده از فرمول‌نویسی آرایه‌ای (array formulas) می‏‌توانید فرمول‏‌ها را به صورت کلی بنویسید و روی چندین سلول اعمال کنید. ساده‌ترین کاربرد فرمول نویسی آرایه‌ای، فرمول نویسی روی ستون‏‌ها است. به این صورت که دیگر به جای استفاده از نشانی یک سلول برای نگارش فرمول، شما می‏‌توانید محدوده‌ی یک ستون را در فرمول مورد استفاده قرار دهید. در ادامه مطلب باما همراه باشید با روش استفاده از فرمول نویسی آرایه‌ای آشنا شوید.

 

برای مثال مطابق شکل زیر، برای به دست آوردن حاصل‌ضرب اعداد ستون d در ستون e، در روش سنتی باید فرمول e1*d1= را در سلول f1 بنویسید و سپس فرمول را در سلول‏‌های متناظر مربوط به سطرهای دیگر کپی نمایید.
 


با استفاده از فرمول نویسی آرایه‌ای درا ل شما می‌‏توانید به صورت یکجا محدوده‌ی (f1:f3) در ستون f، را انتخاب نموده و سپس عبارت d1:d3*e1:e3= را در آن بنویسید. برای راحتی کار می‌توانید به وسیله یروش نام‌گذاری محدوده‌، یک نام برای محدوده‌‌های d1:d3 و e1:e3 اختصاص دهید. 
 
 

البته تنها تفاوت فرمول نویسی آرایه‌ای با فرمول نویسی معمولی نحوه‌ی تائید فرمول می‌باشد. در واقع در فرمول نویسی آرایه‌ای بجای استفاده از کلید enter، باید سه کلید ctrl + shift + enter را همزمان فشار داد. به همین خاطر به فرمول نویسی آرایه‌ای، فرمول‌های سی.اس.ای (cse formulas) هم گفته می‏‌شود. پس از تائید مشاهده می‌کنید که در اطراف فرمول d1:d3*e1:e3= دو علامت کروشه {} ایجاد شده است. (دقت کنید که این دو کروشه بصورت خ ر پس از استفاده از سه کلید ctrl + shift + enter ایجاد می‌شود بنابراین نباید بصورت دستی در ابتدا و انتهای فرمول کروشه بگذارید.)

 

حال اگر تصمیم داشته باشید که مجموع سلول های f1:f3 را محاسبه کنید می‌توانید با انتخاب سلول f4، فرمول (sum(f1:f3= را بنویسید و تائید کنید. اگرچه اگر هدف اصلی محاسبه مجموع ضرب ستون d و e بود می‌توانستید همان ابتدا از فرمول نویسی آرایه‌ای استفاده کنید و در سلول f4 فرمول (sum(d1:d3*e1:e3= را نوشته و برای تائید سه کلید ctrl + shift + enter را همزمان فشار دهید.

* برای محاسبه مجموع ضرب ستون d و e، می‌توانید از تابع sumproduct نیز استفاده نمائید.

یک نمونه پرکاربرد از فرمول نویسی آرایه‌ای محاسبه خطا در محاسبات ی است. بطور مثال برای مقایسه اعداد پیش‌بینی شده با اعداد تجربی از عبارت متوسط ​​درصد خطای مطلق ((mean absolute percentage error (mape) یا (mean absolute percentage deviation (mapd استفاده می‌شود. mape را می‌توان با کمترین میزان محاسبات با استفاده از فرمول نویسی آرایه‌ای تعیین نمود.
به این صورت که با استفاده از روش نام‌گذاری محدوده‌، برای اعداد پیش‌بینی شده نام forecasted_values و برای اعداد تجربی نام  actual_values را انتخاب نمائید. سپس در سلولی که می‌خواهید خطا را محاسبه کنید فرمول زیر را وارد نموده و سپس برای تائید سه کلید ctrl + shift + enter را همزمان فشار دهید.

=average(abs((actual_values-forecasted_values)/actual_values)))


عنوان وبلاگ : ا ل پیشرفته
برچسب ها : فرمول‌نویسی آرایه‌ای در ا ل - فرمول ,نویسی ,استفاده ,آرایه‌ای ,سلول ,ستون ,فرمول نویسی ,نویسی آرایه‌ای ,shift enter ,کلید ctrl ,ctrl shift ,فرمول نویسی آرایه‌ای ,ctrl shift enter ,mean absolute percentage
فرمول‌نویسی آرایه‌ای در ا ل فرمول ,نویسی ,استفاده ,آرایه‌ای ,سلول ,ستون ,فرمول نویسی ,نویسی آرایه‌ای ,shift enter ,کلید ctrl ,ctrl shift ,فرمول نویسی آرایه‌ای ,ctrl shift enter ,mean absolute percentage
نمایش ا ل به روش های مختلف
 نمایش ا ل به روش های مختلف                                                                                                                                           در ایجاد فایل های ا ل و فرمول نویسی ها، برای بسیاری از کاربران پیش آمده که نیاز پیدا می کنند صفحه ی ا ل خود را به شکلی متفاوت ببینند. به عنوان مثال، گاهی کاربران نیاز دارند دو شیت از یک فایل ا ل را در کنار یکدیگر به صورت همزمان ببینند و یا اینکه سلول هایی از یک شیت را فریز (freeze) نمایند.

دیدن دو sheet همزمان در ا ل

ممکن است کاربر نیاز داشته باشد دو sheet از workbook خود را در کنار هم ببیند، برای این کار می توان فایل ا ل موجود را دوباره باز کرد و دو فایل ا ل (که در واقع ی) را در کنار هم دید. به منظور انجام این کار وارد تب view شده و در گروه window گزینه ی new window را انتخاب می کنیم، حال برای تعیین نحوه نمایش دو فایل به صورت همزمان از گزینه ی arrange all استفاده می کنیم.

freeze در ا ل

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

در دستور freeze سه ح وجود دارد، می توانید انتخاب کنید یک سطر و یا یک ستون را همیشه ببیند و یا اینکه با انتخاب گزینه ی اول می توانید چند سطر و ستون را freeze کنید. در فایل ویدئوی قرار گرفته در انتهای این مقاله، روش استفاده از ابزار freeze در ا ل توضیح داده شده است.

split در ا ل

اگر بخواهید sheet موجود را به دوبخش تقسیم کنید می توانید از split استفاده نمایید. استفاده از split زمانی کاربرد دارد که در حال کار بر روی یک فایل ا ل طولانی میباشید، در این ح ممکن است برای فرمول نویسی ها مجبور باشید قسمت های بالایی و پایینی شیت را در کنار یکدیگر ببینید، split شیت کار شما را بسیار راحت مینماید. ابزار split هم در تب view و در گروه window قرار گرفته است.

costume view در ا ل

فرض کنید احتیاج دارید یک نمای خاص (view) از ا ل را دائماً کنترل کنید، به عنوان مثال شما در حال تکمیل بانک اطلاعات هستید و اطلاعاتی در sheet اول وجود دارند که دائم باید داده های ورودی را با آنها چک کنید. در این ح به جای اینکه هر دفعه به sheet  اول رفته و دنبال آن اطلاعات باشید نمای مورد نظر در custom view ذخیره نمایید. ابزار custom view در تب view و در گروه workbook view قرار گرفته است.

 

watch window در ا ل

حال اگر بخواهید بدون اینکه view خود را عوض کنید به یک سری اطلاعات در فایل ا ل خود دسترسی سریع داشته باشید می توانید از دستور watch window استفاده نمایید. با ایجاد پنجره نظارت می توانید محتوا و مقدار یک سلول خاص را ذخیره نمایید و اگر به آن احتیاج داشته باشید به جای گشتن به دنبال آن می توانید از طریق watch window به آن دسترسی داشته باشید. این ابزار در نسخه های ا ل ۲۰۱۰ به بعد از منو حذف شده است. در ا ل نسخه ۲۰۰۷ از مسیر زیر میتوانید به ابزار watch window دسترسی پیدا کنید:

صفحه نمایش ا ل

صفحه نمایش ا ل می تواند به صورت صفحه گسترده، که در ا ل به آن normal گفته می شود و یا به صورت های page break یا page layout نمایش داده شود. در حالات page layout، ا ل مانند نرم افزار word به صورت صفحه ای نمایش داده می شود و می توان برای صفحات header و footer هم تعیین نمود. در ح page break تنها سلول های دارای اطلاعات صفحه بندی می شوند و فضاهای خالی صفحه بندی نمی شوند.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : نمایش ا ل به روش های مختلف - ا ل ,view ,freeze ,window ,فایل ,صفحه ,فایل ا ل ,watch window ,داشته باشید ,استفاده نمایید ,نمایش ا ل
نمایش ا ل به روش های مختلف ا ل ,view ,freeze ,window ,فایل ,صفحه ,فایل ا ل ,watch window ,داشته باشید ,استفاده نمایید ,نمایش ا ل
نمودار پویا در ا ل
نمودار پویا در ا ل                                                                                                                                        یکی از کاربردهای نرم افزار ا ل تجزیه و تحیلی داده ها و ساخت نمودار پویا در ا ل به منظور تصمیم گیر های درست میباشد . یعنی شما می توانید اطلاعات زیادی که در رکوردهای ا ل وارد کرده اید را بر روی یک نمودار پویا نمایش دهید که این کار سبب سهولت تصمیم گیری برای مدیر یک سازمان خواهد شد . 

اضافه کنترل ها

مهمترین قدم در این آموزش اضافه کنترل مورد نظر برای ساخت یک نمودار پویا میباشد . کنترل مورد نظر خود را از تب developer از گروه controls گزینه insert و از گزینه های form control کنترل مورد نظر خود را وارد محیط ا ل نمایید . در این آموزش از combobox برای کنترل و نمایش داده بر روی نمودار استفاده شده است بنابراین کنترل combobox را وارد محیط ا ل خواهم کرد .بر روی کنترل راست کلیک کرده و تب control را انتخاب نمایید .

در قسمت input range مقادیر موجود در ستون a2 تا a5 را وارد کرده و در قسمت cell link سلول a6 را انتخاب کرده و بر روی ok کلیک نمایید . همانطور که ملاحظه خواهید کرد مقادیر به combobox اضافه خواهند شد . اگر کمی دقت نمایید خواهد دید که با انتخاب هر یک از مقادیر از combobox مقدار عددی در سلول a6 هم تغییر خواهد کرد . به عبارت دیگر برای هر یک از گزینه های combobox یک عدد اختصاص داده خواهد شد . مثلا اگر این عدد برابر ۲ باشد کل اطلاعات ردیف ۸ تغییر خواهد کرد و اطلاعات مربوط به در آمد۶ ماهه و اگر این عدد را به ۴ تغییر دهید کل اطلاعات مربوط به در آمد خالص ۶ ماهه در ردیف ۸ نمایش داده خواهد شد . سلول a6 کلید حل ساخت یک نمودار پویا میباشد .

تنها کاری که شما باید انجام دهید نمایش اطلاعات وارد شده در محدوده a7 تا g8 بر روی یک نمودار میباشد . خواهید دید که با انتخاب گزینه مورد نظر از combobox اطلاعات مربوط به آن گزینه بر روی نمودار نمایش داده خواهد شد و بدین ترتیب شما یک نمودار پویا در ا ل ایجاد کرده اید .

 

 

 
عنوان وبلاگ : ا ل پیشرفته
برچسب ها : نمودار پویا در ا ل - نمودار ,کنترل ,ا ل ,اطلاعات ,پویا ,combobox ,نمودار پویا ,کنترل مورد ,داده خواهد ,نمایش داده ,اطلاعات مربوط
نمودار پویا در ا ل نمودار ,کنترل ,ا ل ,اطلاعات ,پویا ,combobox ,نمودار پویا ,کنترل مورد ,داده خواهد ,نمایش داده ,اطلاعات مربوط
انواع داده در ا ل
   انواع داده ها در ا ل                                                                                                                                    به دلیل ماهیت صفحه گسترده بودن ا ل و امکان فرمول نویسی در سلول های ا ل، کاربران در حوضه های متفاوت از ا ل برای رفع نیازهای خود استفاده می نمایند. در این میان ا ل با داده های متفاوتی سر و کار دارد که ابزارهای گوناگونی به منظور کار با این داده ها در اختیار کاربران قرار داده شده است. اما شاید این سوال پیش بیاید که دلیل اهمیت شناخت انواع داده در ا ل چیست، در آینده با فرمول نویسی ها و توابع در ا ل آشنا میشویم، هر تابعی در ا ل ارگومان های مخصوص به خود را دارد که بنا به نوع آن تابع و یت آن، نوع آرگومان های آنها متفاوت میباشد. برخی توابع دارای آرگومان های عددی میباشند و برخی تنها آرگومان های متنی را میپذیرند. شناخت انواع داده در ا ل به ما کمک میکند درک درستی از فرمول نویسی ها و کاربرد توابع داشته باشیم.                                                                                                                        

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

اعداد در ا ل

اعداد در ا ل از اهمیت بسیاری برخوردارند، در مراتب حرفه ای تر کار با ا ل و مخصوصاً در یادگیری و استفاده از برنامه نویسی ویژوال بیسیک در ا ل، به اهمیت شناخت و تفکیک انواع داده در ا ل و کار با اعداد پی خواهیم برد. حتی اعداد نیز خود به چند دسته تعریف میشوند که در توصیف نوع متغیرها در ماکرو نویسی در ا ل بسیار مهم میباشد. داده های عددی در ا ل ممکن است حتی به فرمت متنی نیز ذخیره شوند که انجام اینکار گاهی لازم میباشد.  توابع ریاضی و مثلثاتی در ا ل (math & trig functions) از جمله توابعی هستند که بر روی داده های عددی عملیات انجام میدهند.

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

شما می توانید در ا ل نحوه نمایش اعداد خود را نیز تعیین نمایید، مانند تعداد اعشار، نمایش عدد به صورت درصد یا به صورت عدد علمی، در مباحث بعدی بیشتر به این مورد اشاره خواهیم نمود.

مالی و حسابداری در ا ل

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

برای کار با توابع حسابداری و انجام امور مالی در ا ل لازم است کاربران با داده های نوع currency آشنا باشند، لذا این نکته اهمیت این نوع داده ها را آشکارتر مینماید.

تاریخ و زمان در ا ل

از جمله پر کاربردترین ابزارهای ا ل در حوزه های امور اداری، حسابداری و مستند سازی، توابع تاریخ و زمان (date & time functions) می باشند، برای یادگیری کار با توابع تاریخ و زمان و البته انجام عملیات بر روی سلول های حاوی تاریخ و زمان، لازم است کاربران درک درستی از داده های نوع تاریخ و زمان داشته باشند.

 متن در ا ل

استفاده از توابع و داده های متنی چه در محیط ا ل و چه در زبان برنامه نویسی ویژوال بیسیک در ا ل همواره در سطوح بالاتر و حرفه ای تر قرار گرفته و این موضوع برای فایل های ا ل با زبان فارسی یادگیری مهارت های بیشتری را می طلبد. توابع متنی در ا ل (text functions in excel) از جمله توابع بسیار مهم و عمومی در ا ل به شمار میروند که یت آنها انجام عملیات بر روی داده های متنی میباشد. لذا شناخت داده های متنی و روش تعریف آنها از اهمیت بسیاری برخوردار است.

داده های boolean در ا ل

داده های boolean در ا ل داده هایی هستند که صرفاً میتوانند یکی از دو ح true و false باشند. لازم به ذکر است در ا ل و ویژوال بیسیک، همواره عبارت false برابر با عدد صفر و عبارت true برابر با عدد یک میباشد. پس داده های boolean داده هایی با دو مقدار صفر یا یک میباشند. البته داده ی نوع null هم وجود دارد که به معنی not true و not false است. در آینده در مورد این نوع داده ها بیشتر اطلاعات ب خواهیم کرد.

توابع منطقی در ا ل (logical functions) که از ارکان ا ل به شمار میروند و توابع اطلاعات در ل (information functions) از جمله توابعی هستند که با داده های نوع boolean سر و کار دارند.

 

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

ماکرو چیست؟

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

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

 

procedure

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

برای آشنایی بیشتر با پروسیژرها و انواع آنها مقاله ی پروسیژ ها در ویژوال بیسیک را مطالعه نمایید.

در ماکرو نویسی procedure ها میتوانند sub، function یا property باشند. در ادامه به معرفی آن ها میپردازیم.

sub

ساب یا همان sub routine یک نوع procedure میباشد که با عبارت sub شروع شده و با عبارت end sub تمام میشود، کد های قرار گرفته در بین این دوعبارت عملیات خاصی را با هم انجام میدهند اما قرار نیست که وجی مشخصی ارایه کنند، یعنی نمیتوان نوشت وجی sub برابر است با یک مقدار یا عبارت مشخص. sub ها پرکاربردترین procedure ها در ماکرو نویسی در ا ل میباشند. به این نوع پروسیژر ها به صورت مختصر روتین (routine) هم میگویند، در مقاله ی procedure ها در ماکرو نویسی این نوع پروسیژر مورد بررسی قرار گرفته است.

property

پروپرتی یا همان property procedure هم مانند sub یک نوع procedure میباشد که با property set یا property get یا property let شروع شده و با end property تمام میشود. کد های قرار گرفته در بین این دو عبارت برای cl module ها خواص (properties) تعریف و توصیف میکنند، به عبارت ساده تر کدها قرار است برخی خواص یک شیء خاص را تعریف و مورد بررسی قرار دهند، با مفهوم شیء آشنا خواهیم شد.

 

function

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

نمیتوان procedure ها را درون یکدیگر تعریف کرد به عبارت دیگر، خط اول و آ هر procedure یک module level code (کدی که در سطح یک ماژول تعریف شده و شناخته شده است) میباشد.

 

ماژول

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

انواع ماژول ها

انواع ماژول ها عبارتند از:

  • ماژول های نوع standard module یا همان code module که ماژول هایی هستند که میتوانند توسط سایر ماژول ها در یک پروژه مورد استفاده قرار گیرند.
  • ماژول های نوع cl module که برای تعریف یک cl خاص و مشخص تعریف و مورد استفاده قرار گرفته اند. این نوع ماژول ها یک کلاس (cl ) را توصیف (define) کرده و خواص (properties) و روش های (method definitions) آن را تعریف میکنند.
  • ماژول های نوع form module که حاوی کد های مربوط به یک فرم (form) هستند. با فرم ها به تفصیل آشنا خواهیم شد. فرم ها خود دارای اشیا متعدد میباشند که کدهای مربوی به properties هریک از آنها در این فضا قرار میگیرد.
  • ماژول های نوع object modul که حاوی کد های مشخص برای یک شیء (object) میباشند، ماژول ها نوع cl module، form module و document module همگی جز این دسته میباشند به این علت که خود form ها یا document ها یا cl ها در ویژوال بیسیک اشیا محسوب میشوند.

 declaration

کد های غیر اجرایی (یا غیر قابل اجرا non executable) میباشند که برای نام گذاری procedure ها، ثابت ها (constants)، متغیر ها (variables) و مشخصات (characteristics) آنها- مانند نوع داده ها- میباشند. هر ماژول در ابتدا دارای سطرهایی تحت عنوان declarations میباشد که منظور چند سطر ابت ماژول است که برخی تنظیمات مربوط به سراسر آن ماژول در قالب کد نوشته میشود. در آینده با این بخش اشنا خواهیم شد. یکی از دلایل نیاز به تعریف چند ماژول برای یک پروژه، متفاوت بودن declaration ها ی آنها با توجه به کاربردشان میباشد.

میتوان ماژول را به صورت یک سری declarations که به دنبال آن ها procedure ها قرار دارند هم تعریف نمود. به کد ها و متغیرهایی که به صورت decorations تعریف میشوند عبارت module level codes و module level variable اطلاق میگردد. متغیر های نوع module level در تمام procedure های قرار گرفته در آن ماژول معتبر میباشند.

فرم

فرم ها یا form ها اشیا تبادل اطلاعات با کاربر (dialog box) یا پنجره هایی میباشند که در برگیرنده ی کنترل ها (controls) هستند. نوع تخصصی تر فرم ها multiple document interface ها یا فرم های mdi میباشند که خود حاوی فرم های کوچکتر (child forms) در درون خود هستند. فرم ها از دو قسمت تشکیل شده اند: یکی خود شیء فرم و کنترل های قرار گرفته در آن و دیگری ماژول در برگیرنده ی کدهای اجرایی آن فرم و اشیا درون آن (که به آن form module میگوییم). با انتخاب گزینه های view object و view code (کد های فرم ها در فضای form module قرار دارند) که با کلیک راست بر روی فرم نمایش داده میشوند، میتوانیم هر قسمت را ببینیم یا ویرایش نماییم.

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

object box و procedure box

در فضای کد نویسی form ها (form modules و سایر module ها) در بالا و سمت چپ کادر object box وجود دارد که لیستی از تمام اشیا قرار گرفته در form در آن قرار دارد، هر کدام از اشیا را که میخواهید انتخاب نمایید، حال در سمت راست کادر فوق کادری تحت عنوان procedure box قرار دارد، با باز این کادر متوجه میشویم که مثلاً برای یک کمبوبا چندین procedure وجود دارد (برای سایر module ها با باز این قسمت لیستی از procedure های موجود در آن ماژول نمایش داده میشود)، مثلاٌ click، با انتخاب کلیک یک private sub combobox1_click ایجاد میشود که در واقع یک procedure میباشد که فقط در سطح همین ماژول اعتبار دارد (به دلیل وجود کلمه ی privet) و حاوی کد هایی است که در صورت کلیک بر روی این کمبوبا اجرا میشوند، در واقع در اینجا میخواهید مشخص کنید که کلیک بر روی کمبو با چه نتایجی ایجاد کند.

برای درک این قسمت حتماً باید مفهوم اشیا (object) و پروپرتی (properties) را درک کرده باشید.

project

یک یا یک سری ماژول را یک project مینامیم، مثلاً شما ممکن است برای انجام کاری ناچار به ایجاد چند ماژول متفاوت شوید (به علت وجود declaration های مختلف) به این مجموعه یک project میگوییم. وقتی ما با ا ل سر و کار داریم، project به معنی فایل ا لی هست که ماژول ها در آن قرار گرفته اند، در یک فایل ا ل میتوانید به تعداد بیشما ماژول بوجود بیاورید.

 

اشیاء

اشیا همان object ها هستند که به صورت خلاصه میتوان آن ها را ترکیبی از کدها و داده ها فرض نمود که میتوان بر روی آنها به صورت یکجا عملیات انجام داد و یا آن ها را به صورت یکجا و یک واحد مورد استفاده قرار داد. به عنوان مثال application یک شیء است، یا فرم ها یا کنترل ها همگی شیء میباشند. اشیا خواص (properties) مربوط به خود را دارند که هرکدام روش(method) استفاده ی مختص خود را دارا میباشند.

مفهوم شی (object) در ماکرو نویسی در ا ل یکی از مهمترین مفاهیم است که برنامه نویسان باید درک درستی از آن بدست بیاورند، در واقع نرم افزار ا ل که در آن ماژول های خود را بوجود می آورید application مورد خطاب ویژوال بیسیک میباشد، اگر دقت کنید vba مخفف عبارت visual basic for applications میباشد که در این سایت application ا ل مورد بررسی قرار گرفته است، پس در هر جایی که عبارت application به کار برده شده منظور ا ل میباشد.

زبان ویژوال بیسیک برای application های دیگر هم مورد استفاده قرار میگیرد که فعلاً موضوع بحث این سایت نیست، خود application ا ل یک شی (object) است، اشیا در ویژوال بیسیک میتوانند در برگیرنده اشیا دیگر باشند، مثلاً شی application در برگیرنده ی شی workbook میباشد که به معنی فایل ا لی که در آن کد نویسی میکنید میباشد که خود در برگیرنده ی شی (object) شیت (worksheet) میباشد و شیت در برگیرنده اشیا دیگر میباشد، این بحث در مقاله ای جداگانه به تفصیل بررسی خواهد شد.

هر شیء توسط یک کلاس توصیف و تعریف (define) شده است.

properties

پروپرتی یا properties در مورد اشیا همانند تنظیمات آنها میباشند، مثلاً یک شی فونت (font) دارای تنظیماتی مانند bold بودن میباشد که یک پروپرتی boolean (دو ح دارد یا true و یا false) است، شی فونت زیر مجموعه شی range است.

کلاس

کلاس ها یا cl ها در واقع توصیف هایی (definition) برای object ها میباشند، کلاس به عنوان یک template که یک شیء را در زمان اجرای کد ها بوجود می آورند عمل میکنند. کلاس ها خواص (properties) اشیا و روش های استفاده شده برای کنترل رفتار شی را توصیف میکنند. کلاس ها و ماژول ها خود جز اشیا هستند و خود آنها جزیی از شیء vba میباشند.

 

پنجره کد نویسی

فضایی که در آن کد نوشته میشود را پنجره کد نویسی یا code window مینامیم، پنجره کد نویسی میتواند انواع ماژول و یا پنجره کد نویسی مربوط به هر sheet باشد.

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

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

container

یک شیء که در بر گیرنده ی اشیاء دیگر میباشد را container میگوییم.

method

متد ها (methods) یا روش ها، procedure هایی هستند که بر روی اشیا عمل میکنند. هر متد یک procedure است که بر روی یک شیء عمل میکند.

range

محدوده یا range به یک یا چند سلول ا ل گفته میشود، هر range یا از یک سلول تشکیل شده و یا از تعدادی سلول که در کنار یکدیگر قرار دارند. به عنوان مثال a1 یک range است که دارای یک سلول میباشد و a1:b3 هم یک range با تعداد شش سلول میباشد.

cells

cells هم مانند range برای اشاره به سلول های ا ل در کدهای ویژوال بیسیک استفاده میشود، با این تفاوت که cells تنها به یک سلول اشاره میکند.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : مفاهیم ماکرو نویسی در ا ل - ماژول ,نویسی ,ا ل ,procedure ,میباشد ,بیسیک ,ویژوال بیسیک ,قرار گرفته ,ماکرو نویسی ,مورد استفاده ,استفاده قرار ,زبان ویژوال بیسیک ,زبان برنامه نویس
مفاهیم ماکرو نویسی در ا ل ماژول ,نویسی ,ا ل ,procedure ,میباشد ,بیسیک ,ویژوال بیسیک ,قرار گرفته ,ماکرو نویسی ,مورد استفاده ,استفاده قرار ,زبان ویژوال بیسیک ,زبان برنامه نویس
راهایی برای سرعت بخشی به کار بردن توابع در ا ل
   سرعت بخشیدن کاربا توابع در ا ل                                                                                                                                         فرمولهای excel در دسته های مختلفی دسته بندی شده اند که برای کارهای ریاضی، مالی ، آماری و کار با رشته های متنی و کار با داده های زمان و …. به کار می روند.
توابع ریاضی مشتمل است بر انواع و اقسام توابع مثلثاتی، قدر مطلق، تقسیم باقیمانده ، توان، مجموع مربعات و ….
توابع متنی شامل جستجو در یک متن، حذف و جایگزینی بخشی از یک متن و …. می باشد. توابع آماری برای محاسبه واریانس، انحراف از معیار و کلی چیزهای دیگر که آرزویشان را داریم هم وجود دارد برای داشتن پیش زمینه ذهنی سعی کنید حتماً سری به لیست این فرمولهای بزنید تا در مواقع وم از امکانات تعبیه شده در خود excel استفاده کنید. تابع نویسی از یک علامت = شروع می شود به محض وارد یک علامت مساوی در یک خانه خالی excel می فهمد مقدار این خانه یک داده تنها نیست بلکه باید آنرا حساب کند.
وقتی نیاز به یک تابع را احسای می کنید به عنوان نخستین سؤال این پرسش مطرح می شود که از کجا تابع مورد نظر خود را بیاییم؟
ممکن است شما دقیقاً بدانید می خواهید چه کاری انجام دهید ولی اطلاعی نداشته باشید که آیا در ا ل توابع مورد نیاز شما پیش بینی شده است یا خیر؟ از کجا بدانیم تابعی که مورد نیاز ما است کجاست؟ شاید هم تابعی را چند ماه پیش به کار برده اید ولی هم اکنون آنرا فراموش کرده اید. فرض کنید می خواهید میزان بازپرداخت ماهانه برای یک وام 180 هزار دلاری را که ظرف ده سال با بهره 6 درصد باید بازپرداخت شود محاسبه کنید. کافی است در خط فرمول قرار بگیرید و دکمه function insert را بفشارید و در آنجا عبارت مورد نظر خود را در بخش جستجو وارد کنید. یا حتی می توانید نوع تابع مورد نظر خود را از بخش category انتخاب نموده و در لیست مربوطه تک تک روی اسامی هر یک از توابع بایستید و تعریف آنرا بخوانید. مثلاً تابع pmt همان تابعی است که ما بدنبال آن هستیم و در تعریف آن آورده شده است، محاسبه میزان باز پرداخت ماهانه برای وام با اقساط ثابت و بهره ثابت با انتخاب این تابع کادری باز می شود که آرگومانهای تابع را از ما دریافت می نماید.
قبل از این کار با کلیک روی گزینه this topic help on می توانید نکات راهنما در مورد این تابع را بخوانید. در این راهنما syntax یا به عبارتی فرم دقیق تابع و مشخصات آرگومانهای ورودی آن آورده شده است و همچنین یک مثال کامل از یک نمونه کاربرد تابع ذکر شده است. که می توانید این مثال را کپی کرده و در فایل خود بچسبانید و نحوه کار آن را ببینید.
پنجره ای که آرگومانهای تابع را دریافت می کند به تفکیک مقادیر ورودی تابع ما را نشان داده است و منتظر می ماند شما مقدار هر یک از وارد نمایید. در اینجا می توانید به هر یک از این آرگومانها یک مقدار عددی یا آدرس یک خانه تخصیص دهید. آرگومانهایی که ارائه مقدار آنها ضروری است با رنگ مشکی تیره مشخص شده اند و حتماً باید مقدارشان را مشخص کنید. بقیه آرگومانها مقدارشان اختیاری است و اگر معلوم نکنید به صورت پیش فرض به آنها مقدار داده می شود.
در کادر مقابل هر آرگومان که قرار بگیرید در پایین ترین بخش صفحه تعریفی از ویژگیها و مشخصات این آرگومان آورده می شود.
تابع 5 pmt(pate. noper.pv.type)‌آرگومان دریافت می دارد که سه تای اول آن اجباری هستند و دو تای آ ی اختیاری.
برای محاسبه اقساط ماهانه وام ده ساله 180 هزاردلاری با بهره 6 درصد در بخش rate که نشانگر بهره است مقدار 12/6% را باید وارد کنید. بهره 6 درصد سالیانه برای هر ماه معادل نیم درصد است.
توجه داشته باشید که نرخ سود را باید ماهانه وارد نمود نه سالانه. آرگومان noper مبین تعداد کل اقساط وام است که در اینا معادل 12*10=120 خواهد بود. pv هم نشانگر کل میزان وام دریافتی بدون در نظر گرفتن بهره و معادل 180000 است. با فشردن دکمه ok نتیجه کار در کاربرگ شما پدیدار می شود ($.79 . 19) یعنی شما هر ماه تقریباً هزار و هفتاد و نه دلار باید بپردازید. چون این مقدار را باید بپردازید و به اصطلاح حسابداری پرداختنی است در داخل پرانتز به عنوان یک عدد منفی – و با رنگ قرمز نشان داده می شود.
مراحلی که در بالا توضیح دادیم برای وقتی است که شما خود تابع و یا آرگومانهای آنرا به درستی نمی شناسید. ولی در دفعات بعدی که نام تابع را به خاطر می آورید کافی است در داخل خود کاربرگ نام تابع را وارد نمایید. بدیهی است باید محتوای خانه ای که قرار است از طریق یک تابع محاسبه شود با یک علامت مساوی شروع شود. ا ل با نشان دادن نوشته هایی با زمینه زرد رنگ به نام tips به شما راهنمایی های لازم را در خصوص ترتیب و مشخصات آرگومانها می نماید. آرگومان جاری که در حال وارد نمودن آن هستید پررنگ تر دیده می شود. پارامترهایی که مقدار دادن به آنها اختیاری است در داخل کروشه نشان داده می شوند.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : راهایی برای سرعت بخشی به کار بردن توابع در ا ل - تابع ,کنید ,توابع ,داده ,بهره ,مورد ,نشان داده ,آرگومانهای تابع ,وارد نمایید ,باید بپردازید ,وارد کنید
راهایی برای سرعت بخشی به کار بردن توابع در ا ل تابع ,کنید ,توابع ,داده ,بهره ,مورد ,نشان داده ,آرگومانهای تابع ,وارد نمایید ,باید بپردازید ,وارد کنید
رسم نمودار دایره ای و doughnut در ا ل
                                                                                                                                     رسم نمودار دایره ای و doughnut در ا لنمودار pie یا به بیان گویاتر، نمودار دایراه ای در ا ل به صورت معمول برای نشان دادن سهم پارامترهای مختلف از یک آیتم مورد بررسی به کار برده میشود. به عنوان مثال شما میتوانید سهم هریک از کالاها در درآمد شرکت خود را در یک نمودار دایره ای به راحتی مشاهد نموده و با هم مقایسه نمایید.                                                     

نمودار doughnut در ا ل

نمودار doughnut در ا ل مانند نمودار دایره ای میباشد وتنها در ظاهر با آن تفاوت دارد، ضمن اینکه در ادامه با قابلیتی از نمودار doughnut (دونات) آشنا میشویم که در نمودار دایره ای وجود ندارد،                           

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

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

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

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

برای جلوگیری از دوباره کاری در ترسیم نمودار در ا ل، هنگام insert نمودار در ا ل و طی مراحل فوق یک سلول خالی را در ح انتخاب قرار دهید، در اینجا اگر دقت کنید میبینید سلول a1 درح انتخاب قرار گرفته، بدین ترتیب پس از انتخاب نمودار مورد نظر یک صفحه ی خالی برای شما نمایش داده میشود که میتوانید رسم نمودار در ا ل را از آن آغاز نمایید.                                                                                                                    کادر خالی ایجاد شده در صفحه ی ا ل شما آماده ی معرفی داده ها برای رسم نمودار دایره ای در ا ل میباشد، بر روی صفحه کلیک کرده و گزینه ی select data را انتخاب کنید، با انتخاب این گزینه پنجره ی انتخاب داده برای نمودار ا ل فعال میگردد. در اینجا شما میتوانید با کلیک بر روی add نمودار دایره ای جدید خود را در ا ل ایجاد نمایید.                            شکل ظاهری نمودار و نحوه ی نمایش دادن نام آیتم ها و سهم آنها را به راحتی میتوانید در تب design و تب های دیگر تغییر دهید                                                                          در مورد نمودار doughnut شما میتوانید چند سری داده را همزمان نشان دهید، برای این کار مراحل رسم نمودار را دنبال نمایید و یک سری جدید را اضافه نمایید،                                     حلقه داخلی روش اندازه گیری اول و حلقه بیرونی، روش اندازه گیری دوم را نشان میدهد. شکل ظاهری و نحوه نمایش داده ها را میتوانید به دلخواه خود ویرایش نمایید.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : رسم نمودار دایره ای و doughnut در ا ل - نمودار ,ا ل ,دایره ,میتوانید ,داده ,نمایید ,نمودار دایره ,نمودار doughnut ,اندازه گیری ,انتخاب قرار ,نمایش داده
رسم نمودار دایره ای و doughnut در ا ل نمودار ,ا ل ,دایره ,میتوانید ,داده ,نمایید ,نمودار دایره ,نمودار doughnut ,اندازه گیری ,انتخاب قرار ,نمایش داده
تصاویر متصل به سلول‌ها در ا ل
   تصاویر متصل به سلول ا ل                                                                                                                               اضافه ع به صفحات ا ل همچون انجام این کار در دیگر برنامه‌های مجموعه آفیس کاری ساده است. از منوی insert گزینه pictures را انتخاب کرده و پس از مراجعه به محل ذخیره‌سازی تصویر موردنظر روی هارد‌دیسک، آن را در بخش دلخواه از صفحه قرار می‌دهید.

 

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

فرض کنید مجموعه‌ای از 20 ردیف اطلاعات را در ستون a و مجموعه‌ای از 20 ردیف اطلاعات را نیز در ستون h از نرم‌افزار ا ل وارد کرده‌اید. در این میان تصویری را نیز در صفحه ا ل فراخوانی کرده و ردیف‌های 5 تا 10 از ستون‌های b تا g را نیز به آن اختصاص داده‌اید. درح عادی همه چیز درست است و هیچ مشکلی وجود ندارد؛ اما فرض کنید برای انجام محاسباتی روی اطلاعات وارد شده در سلول‌ها، ستون‌های c تا f را پنهان (hide) می‌کنید.

 

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

 

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

 

2ـ روی گزینه size and properties کلیک کرده و در پنجره به‌نمایش درآمده بخش properties را انتخاب کنید.

 

3ـ در نهایت گزینه move and size with cells را فعال کرده و با کلیک روی close تغییرات را ذخیره کنید.

 

4ـ از حالا به بعد هر تغییری که روی سطرها و ستون‌ها ایجاد شود نیز مشکلی در ترکیب ظاهری فایل ا ل شما ایجاد نمی‌کند و تصویر/تصاویر فراخوانی شده باتوجه به تغییرات اعمال شده، تغییر اندازه داده و به‌ عنوان مثال چنانچه باید ردیف‌های 5 تا 10 و ستون‌های b تا g را به خود اختصاص دهند همواره در همین فضا جای می‌گیرند.

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

 

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

فرض کنید مجموعه‌ای از 20 ردیف اطلاعات را در ستون a و مجموعه‌ای از 20 ردیف اطلاعات را نیز در ستون h از نرم‌افزار ا ل وارد کرده‌اید. در این میان تصویری را نیز در صفحه ا ل فراخوانی کرده و ردیف‌های 5 تا 10 از ستون‌های b تا g را نیز به آن اختصاص داده‌اید. درح عادی همه چیز درست است و هیچ مشکلی وجود ندارد؛ اما فرض کنید برای انجام محاسباتی روی اطلاعات وارد شده در سلول‌ها، ستون‌های c تا f را پنهان (hide) می‌کنید.

 

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

 

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

 

2ـ روی گزینه size and properties کلیک کرده و در پنجره به‌نمایش درآمده بخش properties را انتخاب کنید.

 

3ـ در نهایت گزینه move and size with cells را فعال کرده و با کلیک روی close تغییرات را ذخیره کنید.

 

4ـ از حالا به بعد هر تغییری که روی سطرها و ستون‌ها ایجاد شود نیز مشکلی در ترکیب ظاهری فایل ا ل شما ایجاد نمی‌کند و تصویر/تصاویر فراخوانی شده باتوجه به تغییرات اعمال شده، تغییر اندازه داده و به‌ عنوان مثال چنانچه باید ردیف‌های 5 تا 10 و ستون‌های b تا g را به خود اختصاص دهند همواره در همین فضا جای می‌گیرند.

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

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

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

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

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

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

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

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

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

اولویت انجام محاسبات ریاضی: اگر با ریاضی آشنایی داشته باشید، عملگرها از اولویتی برای انجام محاسبات برخوردارند. این اولویت ها در نرم افزار ا ل نیز اجرا می شوند و در فرمول نویسی از اهمیت بالایی برخوردار هستند. اولویت ها در ریاضی به ترتیب پرانتز، درصد، توان، ضرب و تقسیم، جمع و تفریق و الحاق متن است که با علامت های زیر نشان داده می شوند:
()، ٪، ^، * /، + -، &
مثال: فرض کنید می خواهیم عبارت ۴*۲+۱۰ را به دو ح مختلف اجرا کنیم و نتیجه را بر اساس اولویت عملگرها مشاهده کنیم. در ح اول حاصل عبارت ۴*۲+۱۰ عدد ۱۸ خواهد شد. چرا که عملگر ضرب، از اولویت بالاتری نسبت به عملگر تقسیم برخوردار است. اما اگر این عبارت را به صورت (۱۰+۲)*۴ بنویسیم، حاصل ۴۸ خواهد شد. چرا که عملگر پرانتز، اولویت بالاتری نسبت به عملگر ضرب دارد.

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

ساختار توابع: هر تابع دارای یک نام و معمولا تعدادی ورودی است و ساختاری به صورت زیر دارد: (.... ; ورودی ۳; ورودی ۲; ورودی ۱) نام تابع
مثال از یک تابع: برای اینکه با عملکرد یک تابع به صورت عملی آشنا شوید، بهتر است یک مثال را در ا ل اجرا کنید. برای اینکار از تابع sum یا جمع استفاده کنید. این تابع، سلول های انتخاب شده را با یکدیگر جمع می کند.

پس از درج تابع، اگر بر روی کلید 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 آن را انتخاب کنید.
پس از انتخاب تابع sum بر روی دکمه ok کلیک می کنیم. در پنجره جدید که function arguments نام دارد، باید ورودی های تابع را وارد کنیم.

 

اکنون با کلیک بر روی ok نتیجه این تابع که عدد ۳۰ است در سلول انتخاب شده نمایش داده می شود.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : نکاتی در مورد درج فرمول در سلول ها - توابع ,سلول ,استفاده ,تابع ,فرمول ,عملگرها ,آدرس سلول ,نشان داده ,عملگرها عبارتند ,افزار ا ل ,توابع ا ل ,کادر insert function ,برای معرفی محدوده
نکاتی در مورد درج فرمول در سلول ها توابع ,سلول ,استفاده ,تابع ,فرمول ,عملگرها ,آدرس سلول ,نشان داده ,عملگرها عبارتند ,افزار ا ل ,توابع ا ل ,کادر insert function ,برای معرفی محدوده
دو نکته برای پیمایش آسان تر فایل ا ل
دونکته بای پیمایش آسان تر ا ل                                                                                                                                       وقتی تعداد worksheet های یک فایل ا ل زیاد می شود مراجعه به صفحه مورد نظر می تواند وقت شما را بگیرد.

نکته ۱ :

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

نکته ۲ :

برای جابه جایی بین صفحه ها می توانید از ویژگی hyperlink استفاده کنید و یک ج فهرست به صورت دستی ایجاد کنید.

برای انجام این کار خانه ای که می خواهید نقش لینک را پیدا کند انتخاب کرده و از روبان insert گزینه hyperlink را انتخاب کنید. سپس در پنجره باز شده عبارت place in this document را انتخاب کرده و روی صفحه مقصد لینک کلیک کنید تا انتخاب شود.                                                                                 با استفاده از این روش و درج لینک ها در صفحهات یک فایل، پیمایش آن برای شما آسان تر خواهد بود.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : دو نکته برای پیمایش آسان تر فایل ا ل - صفحه ,لینک ,نکته ,انتخاب کرده ,انتخاب کنید ,کلیک کنید ,صفحه مورد ,فایل ا ل
دو نکته برای پیمایش آسان تر فایل ا ل صفحه ,لینک ,نکته ,انتخاب کرده ,انتخاب کنید ,کلیک کنید ,صفحه مورد ,فایل ا ل
تغییر ظاهر ا ل
                                                                                                                                             تغییر ظاهر ا لشاید در زمره ی آن دسته کاربرانی باشید که ظاهر تکراری ا ل را نمی پسندند و نمی خواهد که در ابتدای ایجاد فایل های ا ل خود تنظیمات رنگ ها، فونت و … را  از ابتدا شروع نمایند. برای حل این مشکل و تغییر ظاهر ا ل به آنچه مورد پسند شماست می توانید از ا ین موارد استفاده کنید                                                   

theme ها در ا ل

برای انتخاب تم (theme) دلخواه برای ا ل خود و یا انتخاب یک ترکیب رنگ، فونت و یا effect خاص برای ا ل خود می توانید از مسیر زیر اقدام نمایید. تغییر تم تنها بر روی ترکیب رنگ ها و فونت های پیش فرض ا ل تاثیر می گذارد و کاربر می تواند در صورت نیار تغییرات ظاهری دیگر را لحاظ نماید.

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

ایجاد template ا ل

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

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : تغییر ظاهر ا ل - ا ل ,فایل ,توانید ,تغییر ,فونت ,ایجاد ,برای ا ل ,نمایید تغییر ,ظاهر ا ل ,تغییر ظاهر ,ایجاد فایل
تغییر ظاهر ا ل ا ل ,فایل ,توانید ,تغییر ,فونت ,ایجاد ,برای ا ل ,نمایید تغییر ,ظاهر ا ل ,تغییر ظاهر ,ایجاد فایل
سه تابعی که شما را در ا ل حرفه ای می کند

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

توابع شگفت انگیز

می توان وجه تمایز یک کاربر عادی excel و یک کاربر حرفه ای excel بدون اغراق در میزان توانایی وی در استفاده از سه تابع دانست ، توابعی که می توانند مشکلاتی که به آن اشاره شد مرتفع کنند ، سه تابع vlookup ، match ، offset .

ترکیب توابع vlookup ، offset و match

کاربرد vlookup ,match


این توابع این امکان را فراهم می آورد که توسط excel یک سوزن را در انبار کاه پیدا کنید ، فرض کنید در یک شیت اطلاعاتی از مشتری ها شامل شماره تماس ، نام ، شهر و سن را در محدوده a1:.b5000 فراهم آورده اید ، می خواهید بدانید شماره تماس 09355061941 متعلق به چه ی است .


 تابع vlookup شماره تلفن مورد نظر را ستون tel جستجو می کند و با توجه به محدوده جستجو یعنی a1:b16 نام متناظر با شماره را یافته بر می گرداند ، اما تابع match شماره را در ستون tel جستجو کرده و شماره سل متناظر با آن یعنی 9 را بر میگرداند .
نکته: شایان ذکر است اگر در قسمت lookup array فرمول match ارایه افقی وارد کنیم تابع محل ستون آیتم مورد جستجو را بر می گرداند ، این خصوصیت باعث می شود باترکیب توابع match و vlookup فرمول های پویاتر و کارآمد تری را طراحی کنیم .

کاربرد offset چیست


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


=offset(a1;5;5;2;3)

با توجه به اینکه فرمول match موقعیت مکانی یه داده را بازمیگرداند ، می تواند به عنوانی یکی از آرگومان ها فرمول offset بکار رود ، نکته دیگر اینکه فرمول های vlookup و match در صورت عدم وجود داده مورد نظر خطاری #value را باز می گردانند همچنین فرمول offset بدون بکار بردن و ترکیب با سایر توابع مانند sum این خطا را باز خواهد گرداند .

در پایان

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

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : سه تابعی که شما را در ا ل حرفه ای می کند - offset ,شماره ,match ,فرمول ,vlookup ,excel ,فرمول offset ,اینکه فرمول ,فرمول match ,شماره تماس
سه تابعی که شما را در ا ل حرفه ای می کند offset ,شماره ,match ,فرمول ,vlookup ,excel ,فرمول offset ,اینکه فرمول ,فرمول match ,شماره تماس
نحوه رسم نمودار گانت در ا ل
     نحوه رسم نمودار گانت در ا ل                                                                                                                                          در ا ل میتوانید مهارت خود را در استفاده از ا ل در فلید کنترل پروژه افزایش دهید.                                                                                                                                    مرحله اول را با ایجاد چند ستون  محتوای فعالیت ها ، تاریخ شروع ، مدت زمان انجام فعالیت و زمان پایان را اتخاب کنید.                                                                                                                                     

مرحله دوم 

ازسر برگ insert > charts > barراانتخاب می کنیم


  در کادر خالی باز شده کلیک راست کرده و گزینه select data را انتخاب می کنیم  .                          درکادر باز شده روی گزینه addزده و در کادر باز شده در قسمت series name روی سلولb1 یعنی start کلیک کرده و در کادر دوم series values  ستون تاریخ شروع را انتخاب می کنیم و بعد ok

همین مرحله را برای ستون duration انجام میدهی

در سمت راست کارد select data گزینه edit را زده و نام فعالیت ها را انتخاب میکنیم

 select dataگزینهedit را زده ونام فعالیتها را انتخاب میکنیم                                                        یرای مرحله بعد روی بار آبی رنگ که مربوط به زمان شروع کلیک راست کرده و روی گزینه format data series را انتخاب  می کنیم                                                                        

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

 

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

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

از commentها برای اضافه توضیحات (یادداشتها) در یک خانه استفاده می‌شود .

برای درج comment ، ابتدا خانه مورد نظر را انتخاب می‌کنیم و سپس از منوی inset گزینه comment را انتخاب کنید.

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

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

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

برای ویرایش یا حذف یک comment از خانه‌ای می‌توانید روی آن خانه r-click (کلیک راست) کنید و از منوی باز شده (context menu) گزینه‌های edit، delete را انتخاب کنید.

وقتی موس روی سلول حاوی comment قرار می‌گیرد، comment نمایش داده می‌شود  اگر بخواهیم به صورت ثابت نمایش داده شود گزینه show/hide comments را می‌زنیم.

برای عوض background یک comment به ترتیب مراحل زیر را انجام می‌دهیم:

1- روی خانه حاوی comment، r-click کرده و گزینه edit comment را انتخاب می‌کنیم.

2- روی کادر comment ، r-click کرده و گزینه format comment را کلیک می‌کنیم.

توجه مهم : باید دقیقا بر روی کادر و یا همان border کامنت ها right click نمایید.  در غیر اینصورت فقط پنجره تنظیمات font را مشاهده خواهید کرد.

3- به سربرگ picture بروید و با استفاده از دکمه select picture ع مورد نظر خود را انتخاب کنید.

در نهایت کاربرگ (sheet) ما با اضافه چند شکل  در خواهد آمد.

دقت داشته باشید که در هنگام cut/copy یک سلول، comment آن نیز کپی می‌شود.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : ع در سلول‌های ا ل - comment ,خانه ,ا ل ,click ,گزینه ,حاوی ,انتخاب کنید ,نمایش داده ,click کرده ,حاوی comment ,برای اینکار
ع در سلول‌های ا ل comment ,خانه ,ا ل ,click ,گزینه ,حاوی ,انتخاب کنید ,نمایش داده ,click کرده ,حاوی comment ,برای اینکار
ج در ا ل
                                                                                                                                                                                                                                                                                                              ج درا ل                                                                                                                                 ا ل یک ج بسیار گسترده است که کاربران میتوانند سلول های آن را مورد استفاده قرار دهند، در هر صورت به خودی خود هر فایل ا ل یک ج است ولی در ا ل قابلیت دیگری قرار داده شده است به این ترتیب که میتوان در ا ل ج تعریف کرد، در واقع با استفاده از این قابلیت بخشی از داده های خود که میخواهید به صورت ج باشد را به عنوان ج به ا ل معرفی میکنید. استفاده از ج در ا ل که خود یک ج بسیار بزرگ میباشد ابتدا شاید کمی به نظر برسد ولی با خواندن این مقاله پی به مزایای استفاده از ج در ا ل میبریم.

 

روش رسم ج در ا ل

داده های شکل زیر را در نظر بگیرید که آنها را برای انجام تحلیل ها در یک فایل ا ل وارد کرده ایم،

 

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

 

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

پس از تبدیل داده های بالا به ج ، ا ل به صورت اتوماتیک ج شما را نامگذاری میکند، به هر ستون قابلیت اضافه میکند و ج شما با یک فرمت پیشفرض تغییر ظاهر میدهد، استفاده از ج در ا ل دارای مزایای زیر است که با کمی تمرین میتوانید آنها را درک کنید:

  • عنوان ستون ها همواره در بالای صفحه ثابت میماند و با درگ به سلول های پایین صفحه عنوان ستون ها همچنان قابل دیدن است، به این ترتیب با زیاد شدن تعداد سطرها لازم نیست برای خواند عنوان ستون ها به بالای صفحه درگ کنید.
  • استفاده از ابزارهای sort و filter و همچنین a nced filter بر روی جداول بسیار راحت تر خواهد بود و همینطور شکل ظاهری جداول به راحتی و با استفاده از قالب های موجود قابل تغییر است.
  • با افزودن داده ی جدید، ج به صورت اتوماتیک تغییر سایز میدهد و سطر جدید را میپذیرد و فرمت و فرمول های قبلی برای سطر جدید تکرار میشود.
  • اگر داده ها را به صورت ج تعریف کنیم میتوانیم از ابزار form یا فرم برای اضافه داده ی جدید و یا ویرایش داده های قبلی استفاده نماییم، برای فعال ابزار فرم به قسمت customize quick access toolbar بروید و این ابزار را در قسمت all commands پیدا کرده و به quick access toolbar خود اضافه نمایید. با استفاده از این ابزار افزودن و ویرایش داده ها برای جداول بسیار راحت میگردد.
  • برای تعریف جداول محوری یا نمودارهای محوری (pivot table & pivot charts) بهتر است از ج ها به عنوان داده های ورودی استفاده کنیم، در اینصورت نتیجه ی بهتری از جداول و نمودارهای محوری میگیریم و پس از به روز رسانی داده های ج با افزودن و یا کاستن یک رکورد (یا سطر ج )، نمودار و ج محوری نیز به صورت خ ر به روز رسانی میشود. پس اگر قصد استفاده از جداول و نمودارهای محوری را دارید حتماً از ج استفاده کنید.
  • سهولت در نوشتن فرمول در سلول ها از دیگر مزایای ج در ا ل میباشد، به صورتی که نیاز به درگ فرمول برای تمام اعضای یک ستون نداریم و بلافاصله بعد از ویرایش و یا نوشتن فرمول برای یک سلول از یک ستون داده، فرمول نوشته شده در تمام سطرهای ج درج میگردد.

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

 

 

اصول رسم ج در ا ل

در رسم ج در ا ل با توجه به اینکه جداول ا ل هسته ی اصلی بانک های اطلاعاتی (یک بانک اطلاعاتی یک ج بزرگ است) و خوراک اصلی ترسیم نمودارها و جداول محوری میباشند حتماً باید اصول زیر را رعایت نماییم:

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

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

آشنایی با عبارات کاربردی ج در ا ل

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

  • list یا لیست در واقع لیست داده ها یا همان ج میباشد.
  • field یا فیلد که به یک ستون از داده ها میگویند که عنوان مختص خود را دارد، سطر اول ج همواره حاوی نام ستون ها میباشد که به این سطر header row میگوییم.
  • record یا رکورد به یک سطر از داده ها میگویند که بهتر از هر سطر با یک عنوان مشخص شود، مانند شماره ردیف یا شماره آیتم یا شماره سطر، که به این شماره unique key میگوییم.
  • item یا آیتم، به هریک از داده های یک بانک اطلاعاتی میگوییم (به یک record یک item هم میگوییم).

یک ج در ا ل یک flat-file database است یعنی فقط یک ج در database وجود دارد و هر record  دربردارنده تمام اطلاعات مورد نیاز درباره یک آیتم است، از طرفی نرم افزار access یک relational database است که اطلاعات را با کارایی بیشتر ذخیره میکند.

طراحی ج در ا ل

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

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

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : ج در ا ل - ج ,ا ل ,استفاده ,داده ,جداول ,ستون ,عنوان ستون ,نمودارهای محوری ,نوشتن فرمول ,برای جداول ,ج استفاده ,quick access toolbar
ج در ا ل ج ,ا ل ,استفاده ,داده ,جداول ,ستون ,عنوان ستون ,نمودارهای محوری ,نوشتن فرمول ,برای جداول ,ج استفاده ,quick access toolbar
بهینه سازی با فایلهای xlsb در excel 2007 , 2010
بهینه سازی با فایلهای xlsb در excel 2007 , 2010                                                                                                                                           تکنولوژی فایلهای excel از نسخه 2007 به open xml تغییر پیدا کرده است که در این فایلها اطلاعات به صورت مجزا و به صورت فشرده ذخیره می شوند برای مثال یک فایل خالی به نام test.xlsx بسازید و سپس آنرا به test.xlsx.zip تغیر نام بدهید و آنرا باز نمایید . خواهید دید که ساختاری شبیه شکل زیر خواهد داشت. 
این فرمت فایل مزایای بسیار دارد از جمله آنکه اگر قسمتی از فایل اب شود ، سایر قسمت ها سالم و قابل بازی هستند.
نکته حائر اهمیت آن است که در excel 2007 -2010 در هنگام save فایل گزینه ای به نام xlsb و یا excel_binary_workbook داریم .
فایلهای xlsb دارای ساختاری باینری هستند یعنی چیزی نزدیک به زبان 0 و 1 ترجمه شده اند و برخلاف فایلهای xlsx که ساختاری xml دارند و باید ابتدا ترجمه شوند تا بتوان با آنها کار کرد بنابراین :
ساختار باینری در فایلهای excel این امکان را فراهم می کند که بتوان فایلها را با سرعت بیشتری save / open کرد در ضمن اینکه از نظر امکانات فایل هیچ تفاوتی بین xlsx , xlsb نیست .
اگر شما فایل بسیار بزرگی که شامل سطرو ستونهای زیادی است و در عمل با کندی سرعت در هنگام باز و ذخیره مواجه هستید می توانید این فرمت را تست نمایید .
توجه داشته باشید که همانطور که گفته شد فایلها xlsx با دلیل قابلیت بالای بازی هنگام ارجحیت خود را همواره نسبت به xlsb خواهند داشت و به همین دلیل فرمت پیش فرض excel می باشد.


عنوان وبلاگ : ا ل پیشرفته
برچسب ها : بهینه سازی با فایلهای xlsb در excel 2007 , 2010 - فایل ,excel ,فایلهای ,xlsb ,xlsx ,فایلها ,فایلهای xlsb ,2007 2010 ,excel 2007 ,test xlsx ,فایلهای excel
بهینه سازی با فایلهای xlsb در excel 2007 , 2010 فایل ,excel ,فایلهای ,xlsb ,xlsx ,فایلها ,فایلهای xlsb ,2007 2010 ,excel 2007 ,test xlsx ,فایلهای excel
ساخت combo box با مقادیر متغیر در ا ل 2007
کنترل combo box ورودی کاربران را در هنگام وارد داده ها کنترل می کند و مقادیر خارج از لیست combo box را قبول نمی‌کند.                                                                                 ساخت combo box با مقادیر متغیر در ا ل 2007

drop down list در excel توسط data validation برای سلولها انجام می‌شود اما موضوع این مقاله ساخت combo box ای است که کاربر بتواند به راحتی مقادیر آنرا کم یا زیاد کند و لازم نباشد که به صورت دستی این کار انجام شود به عبارت دیگر بتواند مقادیر لیست combo box را به راحتی کنترل نماید.
قبل از هر کار ابتدا باید شما با ابزار بسیار مفیدی در excel 2007 آشنا شوید به نام table و بدانید که table یک محدوده پویا ست یعنی dynamic range است.
منظور از محدوده پویا در excel ، آدرس هایی است که با یک نام شناخته می شوند و شما به جای نوشتن آدرس سلول excel از آن نام استفاده می نمایید.
برای ساخت یک محدوده پویا در excel 2007 – 2010 از ابزار table استفاده می‌شود. فرض کنید که در یک شرکت شما 5 انبار دارید که کاربر قرار است در لیست ورود کالا، یکی از انبارهای را انتخاب نماید.
مطابق شکل اسامی انبارها را در یک sheet جداگانه از  sheet لیست ورود انبار وارد نمایید و سپس یکی از سلولهای را کلیک کنید و کلید ctrl+t را بزنید تا این لیست به table تبدیل شود.                                     بعد از اینکه table شما در excel آماده شد باید یک name تعریف کنید و منبع name به ستون "نام انبارها" اشاره کند.
برای اینکار از سربرگ formula گزینه define name را بزنید.
در قسمت name یک اسم دلخواه تعیین کنید مثلا mystock  (اسم فارسی نگذارید و از space هم استفاده نکنید).
در قسمت refer to با دقت بسیار سلولهایی که نام انبارها در آن نوشته شده است را با drag موس انتخاب کنید . (سر ستون انتخاب نشود)
و در نهایت کار شما باید نتیجه ای مانند شکل را بدهد و ok را بزنید.                                                    به sheet "لیست کالا" بروید و در صورت تمایل می توانید این لیست را نیز به صورت یک table در آورد و از مزایای table استفاده کنید .
سلول یا سلولهای ستون "نام انبار" را انتخاب نمایید.
از سربرگ data گزینه validation را بزنید .
در پنجره data validation گزینه list را انتخاب نمایید و سپس در قسمت source این پنجره name تعریف شده در مرحله قبل را همراه با علامت  = وارد نمایید و کلید ok را بزنید:                                                    حال در سلولهای نام یک combo box دارید که اسامی انبارها را نشان می دهد، اگر شما در table "نام انبارها" گزینه ای را اضافه کنید و یا سطری را delete نمایید متوجه می شوید که این combo box خ ر به روز می شود .
عنوان وبلاگ : ا ل پیشرفته
برچسب ها : ساخت combo box با مقادیر متغیر در ا ل 2007 - table ,لیست ,combo ,نمایید ,excel ,انبارها ,محدوده پویا ,sheet لیست ,وارد نمایید ,name تعریف ,انتخاب نمایید
ساخت combo box با مقادیر متغیر در ا ل 2007 table ,لیست ,combo ,نمایید ,excel ,انبارها ,محدوده پویا ,sheet لیست ,وارد نمایید ,name تعریف ,انتخاب نمایید
کاراکترهای wildcard در ا ل
تا اینجا با ابزارهای بسیاری در ا ل آشنا شده ایم.میتوان گفت که یکی از پر استفاده ترین کارها در ا ل جستجو و جایگزینی در رشته های متنی، جداول، بانک های اطلاعاتی، سلول های ا ل و… میباشد.

کاراکترهای wildcard در ا ل

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

با مفهوم ضمیر در دستور زبان آشنا هستیم، به عنوان مثال ضمیر ” او ” در زبان فارسی به معنی یک نفر است و ضمیر “آنها” به معنی چند نفر، میتوان بجای اسم ها از ضمیرهای معادل با آنها استفاده کرد. کاراکترهای wildcard هم مانند ضمیر عمل میکنند. کاراکتر ” ؟ ” معادل با یک کاراکتر است که میتواند هر کاراکتری باشد و کاراکتر ” * ”   معادل با هر تعداد کاراکتر است که میتوانند هر کاراکتری باشند. کاراکترهای wildcard در ا ل کاراکترهایی هستند که میتوانند جایگزین کاراکترهای دیگر شوند. از سه کاراکتر * (asterisk)، ؟ (question mark) و ~ (tilde) به عنوان کاراکترهای wildcard در ا ل یاد میکنیم.

کاراکترهای wildcard در ا ل کاربرد گسترده ای در توابع و ابزار ها دارند.

کاراکتر ” ؟ ” در ا ل

یکی از کاراکترهای wildcard در ا ل کاراکتر ” ؟ ” یا question mark میباشد. این کاراکتر میتواند جایگزین یک کاراکتر دیگر (هریک از کاراکترهای استاندار ascii) باشد، فرض کنید یک فایل ا ل حاوی فهرستی طولانی از اسامی داشته باشیم، با استفاده از ابزار find & replace به راحتی میتوانیم هر اسمی را در این فهرست جستجو نماییم. در این فهرست به دنبال نام hamed ghadimi میگردیم، اما در این فهرست این نام ممکن است به هریک از ح های زیر وارد شده باشد:

hamed-ghadimi

hamed_ghadimi

hamed ghadimi

hamed/ghadimi

مشخص است که اگر ما عبارت hamed ghadimi را جستجو نماییم ممکن است نتیجه مورد نظر را پیدا نکنیم و از طرفی در مورد کاراکتر جدا کننده نام از نام خانوادگی مطمئن نیستیم، یک راه انجام این جستجو استفاده از کاراکترهای wildcard در ا ل و بالاخص کاراکتر ” ؟ ” میباشد. اگر در پنجره find & replace به دنبال عبارت hamed?ghadimi بگردیم به این معنی است که به دنبال hamed و ghadimi میگردیم که با یک کاراکتر (فقط یک کاراکتر-هر کاراکتری) از هم جدا شده باشند و کاراکتر جدا کننده هر کاراکتری میتواند باشد (space یا فاصله هم یک کاراکتر میباشد).

کاراکتر ” * ” در ا ل

یکی از کاراکترهای wildcard در ا ل کاراکتر ” * ” یا asterisk میباشد. این کاراکتر میتواند جایگزین هر تعداد کاراکتر دیگر (هریک از کاراکترهای استاندار ascii) باشد، کاراکتر ” * ” میتواند صفر یا چندین کاراکتر باشد، پس طبق مثال بالا اگر ما در فهرستی به دنبال hamed*ghadimi نتیجه ی جستجو تمام موارد زیر را شامل میشود:

hamed-ghadimi, hamed_ghadimi, hamed ghadimi, hamed/ghadimi,….

hamedghadimi

[email protected]@@@@@@ghadimi, hamed.+_/#@ghadimi,….

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

کاراکتر ” ~ ” در ا ل

یکی از کاراکترهای wildcard در ا ل کاراکتر ” ~ ” یا tilde میباشد. این کاراکتر دو کاربرد دارد اول اینکه در ا ل کاراکتر tilde همانند کاراکتر ستاره ” * ” (asterisk) میتواند جایگزین هر تعداد کاراکتر دیگر (هریک از کاراکترهای استاندار ascii) باشد – کاراکتر ” ~ ” میتواند صفر یا چندین کاراکتر باشد – کاربرد دوم کاراکتر  ~ ”“ اینست که از این کاراکتر برای جستجوی کاراکترهای” * و ? و خود ~ “میتوان استفاده نمود.

در عبارت مورد جستجو در فایل ا ل اگر قبل از کاراکتر asterisk یک کاراکتر tilde قرار دهیم به این معنی است که منظور ما از کاراکتر ” * ” خود کاراکتر asterisk میباشد و نه یک کاراکتر wildcard. به عنوان مثال اگر شما بخواهید در یک فایل ا ل به دنبال ” * ” بگردید باید در پنجره جستجو عبارت” ~* ” را جستجو نمایید و اگر بخواهید” ؟ ” را پیدا کنید باید عبارت” ~? ” را جستجو نمایید و به همین ترتیب اگر به دنبال” ~ “هستید باید عبارت” ~~ “را جستجو نمایید.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : کاراکترهای wildcard در ا ل - کاراکتر ,ا ل ,hamed ,کاراکترهای ,جستجو ,ghadimi ,hamed ghadimi ,کاراکترهای wildcard ,ghadimi hamed ,جستجو نمایید ,ا ل کاراکتر ,ghadimi hamed ghadimi ,hamed ghadimi hamed ,کاراکترهای ا
کاراکترهای wildcard در ا ل کاراکتر ,ا ل ,hamed ,کاراکترهای ,جستجو ,ghadimi ,hamed ghadimi ,کاراکترهای wildcard ,ghadimi hamed ,جستجو نمایید ,ا ل کاراکتر ,ghadimi hamed ghadimi ,hamed ghadimi hamed ,کاراکترهای ا
چگونه خطای فرمول ها را اصلاح کنیم
 چگونه خطای فرمول ها را اصلاح کنیم                                                                                                                                         تمام فرمول ها بدون نقص نمی باشند و وقتی که خطا داشته باشند، ا ل با ظاهر یک مثلث سبز رنگ در گوشه سمت چپ و بالای سلول فرمول و یک پیغام خطا، به شما اطلاع می دهد. برای مثال، ممکن است مقدار ، آدرس سلول یا تابع را غلط وارد کرده باشید. اولین کاری که در هنگام مشاهده مقدار خطا باید انجام دهید این است که فرمول و آدرس های بکار رفته را بررسی کنید ولی در برگه های کاری پیچیده، همیشه پیدا فرمول یا آدرس نادرست، آسان نیست. از ابزارهای بررسی excel برای پیدا اشتباهات خود استفاده کنید. می توانید خط های ردی را نمایش دهید که آدرس های مرجوعی (آدرس هایی که در فرمول به آنها اشاره شده است) و وابسته ها (آدرس هایی که در یک سلول دیگر به آنها ارجاع می شود مانند آدرس هایی که در فرمول بکار می روند) را پیدا می کنند.

 

نوار ابزار formula auditing را باز کنید  

 

 وقتی که خطاها رخ می دهند، یکی از قدرتمند ترین ابزارها برای اصلاح آنها، نوار ابزار formula auditing است. برای باز آن، از منوی tools ، گزینه formula auditing و بعد گزینه show formula auditing toolbar را انتخاب کنید.

خطاها را مرور کنید 

 برای بررسی هر سلولی که با مثلث خطا مشخص شده است، روی دکمه error checking در نوار ابزار formula auditing کلیک کنید،excel اولین سلول خطا را متمایز می کند و گزینه هایی برای اصلاح آنها در اختیار شما قرار می دهد.

خطا را اصلاح کنید 

 در اینجا، ا ل متوجه شده است که یک سلول از فرمولی استفاده می کند که با فرمول‌های سلول‌های پیرامون، متفاوت است. این ممکن است یک خطا باشد یا نباشد. اگر بخواهید می توانید به ا ل بگویید که این خطا را نادیده بگیرد. برای این کار، روی دکمه ignore error کلیک کنید. ولی، این یک خطاست. بنابراین روی copy formula from above کلیک کنید. با کلیک روی edit in formula bar خودتان می توانید فرمول را تغییر دهید

 خطاهای بعدی را نیز اصلاح کنید 

ا ل ، سلول خطای بعدی را متمایز می کند. گزینه مورد نظر را از کادر محاوره ای error checking انتخاب کنید. آن قدر ادامه دهید تا تمام خطاها را مشاهده کنید. وقتی که ا ل خطاها را بررسی می کند، یک پیغام نشان می دهد. روی ok کلیک کنید.

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

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

ردی را مشاهده کنید

 فلش‌های ردی را نمایش می دهد که به منبع های فرمول اشاره می کنند و به شما کمک می کنند تا خطا را دنبال کنید. در اینجا، من اشتباهاً جمع کل در ستون g را در محدوده هایی که در تابع average بکار بردم وارد ! بعد از اینکه خطا را پیدا کردید، اصلاحات لازم را انجام دهید و با کلیک روی remove all arrows ، فلش های ردی را حذف کنید.
*توجه*

 استفاده از trace error

اگر یک مثلث سبزرنگ، وجود خطا در فرمولی که تایپ کرده اید را نشان دهد، با بردن اشاره گر ماوس به سمت چپ سلول و متمایز دکمه trace error ، کلیک روی فلشی که ظاهر می شود و انتخاب یکی از گزینه ها می توانید فوراً آن را بررسی کنید.
ردی وابسته ها

با کلیک روی یک سلول و کلیک روی دکمه trace dependents درنوار ابزار formula auditing می توانید بررسی کنید که آیا مقدار آن سلول در یکی از فرمول ها استفاده شده است یا خیر و چگونه.
ارزی فرمول

برای ارزی یک فرمول، روی دکمه evaluate formula در نوار ابزار formula auditing کلیک کنید تا یک کادر محاوره ای باز شود که شما را در تک تک قسمت های فرمول جلو می برد. برای محاسبه قسمت خط کشی شده فرمول، روی evaluate کلیک کنید. هر اندازه که لازم است روی evaluate کلیک کنید.

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

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

ابتدا روی شیت مدنظر راست کلیک میکنیم و از منویی که باز میشه گزینه view code را انتخاب میکنیم

 

با اینکار وارد پنجره visual basic editor میشویم. در قسمت چپ گزینه enablecalculation رو پیدا میکنیم و مقدار آن را برابر با false قرار میدهیم:

 

با اینکار فقط شیت مدنظر ما بصورت محاسبه دستی تبدیل میشود و بقیه شیت ها بصورت محاسبه خ ر باقی میمانند. حالا کافیست که هر بار که نیاز به آپدیت شدن محاسبات شیت داشتیم از تب formulas گزینه calculate sheet را انتخاب کنیم. برای برگرداندن نحوه محاسبات شیت به ح اتوماتیک هم کافیست دوباره گزینه enablecalculation را به مقدار true تغییر دهیم.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : تغییر نحوه محاسبه از خ ر به دستی فقط برای شیت مشخص - محاسبات ,دستی ,محاسبه ,فایل ,گزینه ,ا ل ,نحوه محاسبه ,گزینه enablecalculation ,بصورت محاسبه ,تغییر دهیم ,محاسبه دستی
تغییر نحوه محاسبه از خ ر به دستی فقط برای شیت مشخص محاسبات ,دستی ,محاسبه ,فایل ,گزینه ,ا ل ,نحوه محاسبه ,گزینه enablecalculation ,بصورت محاسبه ,تغییر دهیم ,محاسبه دستی
روش رمز گذاری برای پروژه های ا ل

روش رمز گذاری برای پروژه های ا ل                                                                                                                                           اگر شما از کامپیوتر استفاده می کنید احتمال این نیز وجود دارد که چند پرونده ا ل در pc  یا مک خود داشته باشید. همانطور که میدانید میلیون ها نفر در سراسر جهان از ا ل استفاده می کنند که بیش تر بدرد تجارت و نهاد های تی استفاده می شود. خوشبخاته با وجود ا ل ۲۰۱۴ امکان بالا بردن امنیت پرونده ها با رمز گذاری وجود دارد.

بریا این کار ابتدا باید پرونده ای را که می خواهید رمز گذاری کنید باز کرده و گزینه info را انتخاب کنید سپس روی protect workbook کلیک نمایید.

از نوار باز شده روی گزینه encrypt with p word کلیک کنید.

excel-p word-2-640×427بلافاصله ا ل پنجره ای برای وارد رمز عبور باز می کند شما می توانید رمز عبور خود را به سادگی در ت ت بار باز شده وارد کنید اما به یاد داشته باشید که اگر رمز عبور خود را گم کنید دیگر امکان دسترسی به پرونده شما نیست. ا ل هیچ گزینه ای برای بازی رمز عبور وارد شده توسط شما ندارد.

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

 

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : روش رمز گذاری برای پروژه های ا ل - عبور ,پرونده ,ا ل ,گذاری ,وجود ,گزینه ,داشته باشید ,وجود دارد
روش رمز گذاری برای پروژه های ا ل عبور ,پرونده ,ا ل ,گذاری ,وجود ,گزینه ,داشته باشید ,وجود دارد
روش های کار با خطوط مرزی و الگوها درا ل
   روش هاتی کار باخطوط مرزی والگوها در ا ل                                                                                                                                    خطوط شبکه ای که در برگه های کاری 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 کلیک کنید و یک رنگ انتخاب کنید.

 

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : روش های کار با خطوط مرزی و الگوها درا ل - کنید ,مرزی ,کلیک ,سلول ,خطوط ,استفاده ,کلیک کنید ,انتخاب کنید ,کنید برای ,خطوط شبکه ,خطوط مرزی ,مرزی داشته باشند
روش های کار با خطوط مرزی و الگوها درا ل کنید ,مرزی ,کلیک ,سلول ,خطوط ,استفاده ,کلیک کنید ,انتخاب کنید ,کنید برای ,خطوط شبکه ,خطوط مرزی ,مرزی داشته باشند
تابع forecast
 تابع forecast                                                                                                                                                            تابع forecast ا ل برای پیش بینی خطی یک متغیر بر اساس مقادیر قبلی اون متغیر استفاده میشود. با داشتن مقادیر گذشته یک متغیر میتوانیم با استفاده از تابع forecast ا ل مقدار آن متغیر را برای دوره‌های آینده بر اساس رگرسیون خطی بدست بیاوریم.

قاعده کلی استفاده از تابع forecast ا ل به صورت زیراست:

(محدوده مقادیر متغیر در دوره‌های گذشته، محدوده مقدار تابع برای متغیرهای گذشته، مقدار متغیر که برای آن قصد پیش بینی  تابع را داریم)forecast

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

برای اینکار ابتدا باید پارامترهای مختلف تابع forecast رادر ج مشخص کنیم.

محدوده مقادیر متغیر در دوره‌های گذشته:                                                                             از آنجایی  که مقادیر یازده ماه گذشته را داریم پس این پارامتر شامل محدوده a2:a12 یعنی عددهای 1 تا 11 میشود.

محدوده مقدار تابع برای متغیرهای گذشته:                                                                           این محدوده باید مقدار تابع یعنی تعداد فروش رو به ازای عددهای یک تا یازده به ما نشون بده. پس این محدوده رنج b2:b12 را شامل میشود.

مقدار متغیر که برای آن قصد پیش بینی  تابع را دار یم :                                                           میخواهیم مقدار تعداد فروش رو برای ماه 12 حساب کنیم پس مقدار متغیری که ما میخواهیم  پیش بینی کنیم سلول a13 است.

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

forecast(a13;b2:b12;a2:a12) 

                                            

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : تابع forecast - تابع ,forecast ,متغیر ,محدوده ,استفاده ,گذشته ,تابع forecast ,تعداد فروش ,مقدار تابع ,forecast ا ل ,مقدار متغیر ,محدوده مقادیر متغیر
تابع forecast تابع ,forecast ,متغیر ,محدوده ,استفاده ,گذشته ,تابع forecast ,تعداد فروش ,مقدار تابع ,forecast ا ل ,مقدار متغیر ,محدوده مقادیر متغیر
مفاهیم کلیدی تاریخ و زمان در ا ل
 مفاهیم کلیدی تاریخ وزمان درا ل                                                                                                                                           تاریخ و زمان در excel یکی از آن مواردی است که اگر مفهوم آنرا ندانیم قطعا با مشکلات زیادی در excel روبروخواهیم شد ، محاسبات ما غلط از آب در می آیند . محاسبه زمان ورود و وج کارکنان شرکت ، محاسبات زمان کار یک ماشین در یک واحد صنعتی و محاسبه اضافه کاری که در یک ماه انجام داده اید کاملا وابسته به مفهوم زمان و تاریخ در excel هستند.

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

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

  • منطق تاریخ و زمان در ا ل
  • کلیدهای میانبر format cells
  • مفهوم ساعت در ا ل
  • مثال: محاسبه دستمزد بر اساس ساعت کارکرد
  • مثال: جمع ساعات کارکرد در یک ماه
  • نکاتی در مورد ساعت و زمان ا ل
  • نحوه تایپ صحیح تاریخ میلادی در ا ل
  • تنظیمات control panel
عنوان وبلاگ : ا ل پیشرفته
برچسب ها : مفاهیم کلیدی تاریخ و زمان در ا ل - زمان ,ا ل ,تاریخ ,محاسبه ,excel ,کارکرد ,محاسبه زمان ,زمان کارکرد ,زمان ورود
مفاهیم کلیدی تاریخ و زمان در ا ل زمان ,ا ل ,تاریخ ,محاسبه ,excel ,کارکرد ,محاسبه زمان ,زمان کارکرد ,زمان ورود
شناخت اتفاقات عجیب در ا ل
   شناخت اتفاقات عجیب درا ل                                                                                                                                          یک سری اتفاقات عجیب در ا ل برای شما می افتد که حتی نمی دانید که آن اتفاق را چطوری باید توصیف کنید و یا اینکه در اینترنت جستجو نمایید.

1- فلش آبی رنگی در شیت ا ل که پاک نمی شود!

 

یک فلش به یکباره در ا ل ظاهر می شود که بر روی آن نمی توانید کلیک کنید و آنرا پاک کنید.

دلیل این امر اصلاحا circular references است. یعنی فرمولی که دچار یک حلقه یا loop شده است.

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

مثلادر سلول b3 ما فرمولی نوشته ایم که به سلول c7 ربط دارد و البته !!!! در c7 فرمولی نوشته ایم که به سلول b3 ربط دارد و ا ل سردرگم می شود . بدیهی است که شما باید فرمول ها را اصلاح نمایید.

2- آدرس عجیب و غریب سلول ها در فرمول نویسی!

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

تعجب نکنید این یک روش آدرس دهی است . مثلا سلول a1 که در سطر 1 و ستون 1 ا ل واقع شده است، آدرس آن می شود r1c1 و همینطور الی آ .

دلیل این موضوع آن است که تنظیمات ا ل را به اشتباه تغییر داده اید. پس excel options بروید وr1c1 reference style را از ح انتخاب خارج کنید.

 

3- هنگام کپی فرمول ، اعداد تکرار می شوند!!!

این ح وقتی پیش می آید که شما محاسبات ا ل را از ح automatic خارج کرده اید و به ح manual در آورده اید.

در این ح ا ل فرمولها را آپدیت نمی کند و منتظر می ماند که شما به صورت دستی آنها را آپدیت کنید که البته کلید میانبر آن f9 است. در status bar نرم افزار ا ل شما می توانید کلمه calculate را مشاهده نمایید (تصویر قبل را ببینید) که یعنی این شیت احتیاج به محاسبه دارد و اعدادش آپدیت نیست.

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

برای تنظیم این ح (یعنی محاسبات اتوماتیک انجام شود و یا دستی) می توانید از دو مسیر زیر اقدام کنید :

formual --> calculation options

یا

excel options --> formulas --> calculation options

 

4- نمودار ا ل خالی (سفید) است!

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

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

5- صفحات سفید زیادی print می شود

در ا ل به صورت پیش فرض کل منطقه ای که استفاده شده است چاپ می شود. برای آنکه بدانید این منطقه کجاست کلید ctrl+end را بزنید تا به آ ین سلول بروید.

 

 

 

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : شناخت اتفاقات عجیب در ا ل - ا ل ,سلول ,ح ,فرمول ,آپدیت ,عجیب ,calculation options ,excel options ,فرمول نویسی ,فرمولی نوشته ,اتفاقات عجیب
شناخت اتفاقات عجیب در ا ل ا ل ,سلول ,ح ,فرمول ,آپدیت ,عجیب ,calculation options ,excel options ,فرمول نویسی ,فرمولی نوشته ,اتفاقات عجیب
رمزگذاری روی فایل‌های ا ل
رمزگذاری روی فایل های ا ل                                                                                                                                   زمانی است که میخواهیم برای محافظت از فایل های ا ل و اینکه  فایل ها توسط هر ی خوانده نشود لازم است  که آنها رارمزگذاری کنیم تاافراد مشخصی با داشتن کلمه رمز بتوانند فایل را باز کنند

برای اینکار کافیست روی دکمه file در گوشه بالا سمت راست کلیک کنید. پس از باز شدن بخش backstage ، روی دکمه protect workbook کلیک کنید و از منوی باز شده گزینه encrypt with p word را کلیک کنید.

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

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

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : رمزگذاری روی فایل‌های ا ل - فایل ,کلیک ,کلیک کنید ,تایپ کنید
رمزگذاری روی فایل‌های ا ل فایل ,کلیک ,کلیک کنید ,تایپ کنید
ابزار paste picture link در ا ل

ابزار paste picture link در ا ل ابزاری بسیار جالب و پر استفاده جهت حفاظت از محتوای فایل ا ل می باشد.ابزار paste picture link در ا ل

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

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

سپس در روبان home دکمه paste را باز کرده و در زیر منوی as picture گزینه paste as picture را انتخاب کنید.

 

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

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

 

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : ابزار paste picture link در ا ل - paste ,محتوای ,picture ,تصویر ,مخاطب ,ا ل ,قرار دهید ,انتخاب کنید ,فایل ا ل ,picture link ,paste picture ,ابزار paste picture
ابزار paste picture link در ا ل paste ,محتوای ,picture ,تصویر ,مخاطب ,ا ل ,قرار دهید ,انتخاب کنید ,فایل ا ل ,picture link ,paste picture ,ابزار paste picture
find & replace در ا ل

 find & replace                                                                                                                                            شاید بارها از پنجره find & replace در ا ل برای جستجو و جایگزینی استفاده کرده باشید. ابزار find & replace در ا ل بسیار کاربردی میباشد. علاوه بر جستجو و جایگزینی متن ها و اعداد در ا ل، میتوان فرمت ها را نیز جستجو و جایگزین کرد و علاوه بر این میتوان جستجو را به کامنت ها، فرمول ها و مقادیر ثابت محدود کرد. همچنین برای جستجو در ا ل میتوان از کاراکترهای wildcards استفاده کرد 

برای باز پنجره find & replace در ا ل میتوان از کلید میانبر ctrl + f استفاده کرد و یا در تب home و در گروه editing بر روی منوی find & select کلیک نمود.

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

پنجره find & replace در ا ل

 

د. با کلیک بر روی option جزییات پنجره find & replace در ا ل نمایش داده میشود.

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

  • برای جستجوی یک فرمت خاص (شامل رنگ، فونت، حاشیه و…) سلول، بر روی منوی فرمت (format) در سمت راست و بالای پنجره find & replace کلیک نمایید و از منوی باز شده با انتخاب گزینه ی format و باز شدن پنجره find format فرمت مورد نظر را انتخاب کنید. و یا در همین منو با انتخاب گزینه ی choose format from cell… فرمت دلخواه خود را از یک سلول در ا ل انتخاب نمایید و آن را جستجو کنید.
  • اگر گزینه ی match case تیک زده شود، ابزار find & replace در ا ل به صورت case sensitive عمل مینماید.
  • در صورت تیک زدن گزینه ی match entire cell contents تمام محتوای سلول با عبارت مورد جستجو مقایسه میگردد و تمام کاراکترهای نوشته داخل سلول باید با کاراکترهای عبارت مورد جستجو ی ان باشد.

کاراکترهای wildcard شامل ? و * تنها در صورتی میتوانند توسط ابزار find & replace در ا ل مورد استفاده قرار بگیرند که گزینه ی match entire cell contents تیک خورده باشد.

  • کادر within در پنجره find & replace در ا ل محل جستجو را نشان میدهد، جستجو میتواند در شیت فعال و یا در تمام workbook صورت پذیرد.
  • منوی search صرفاً الگوی جستجو را تعیین میکند که سطری باشد یا ستونی
  • همانطور که قبلاً گفته شد، توسط ابزار find & replace در ا ل میتوان در فرمول ها، مقادیر و یا کامنت ها جستجو کرد. منوی look in تعیین میکند جستجو در میان values (سلول هایی که در آنها مقادیر ثابتی نوشته شده است) یا formulas (سلول هایی که در آنها فرمول نوشته شده است) و یا کامنت ها (comments) صورت گیرد.

replace در ا ل

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

 

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : find & replace در ا ل - ا ل ,find ,replace ,جستجو ,سلول ,پنجره ,پنجره find ,ا ل میتوان ,ابزار find ,سلول هایی ,عبارت مورد
find & replace در ا ل ا ل ,find ,replace ,جستجو ,سلول ,پنجره ,پنجره find ,ا ل میتوان ,ابزار find ,سلول هایی ,عبارت مورد
سری و لیست در ا ل
بسیاری از کاربران در فایل های ا ل خود نیاز به تعریف سری ها پیدا می کنند. همانطور که در ریاضیات خو م سری ها در واقع رشته ای از اعداد هستند که هر عدد با الگویی خاص با استفاده از عدد قبلی خود بوجود میاید، به عنوان مثال یک سری از اعداد را در نظر بگیرید که هر عدد حاصلضرب عدد قبلی در یک مقدار ثابت است. استفاده از قابلیت های ا ل برای تعریف سری و لیست می تواند بخشی از وقت شما را در تهیه ی فایل های ا ل صرفه جویی نماید. از طرفی گاهی نیاز به وارد لیست دلخواه در ا ل داریم، ابزاری در ا ل وجود دارد که به کمک آن میتوان لیست های دلخواه را در قسمتی از ا ل ذخیره نمود و هنگام احتیاج از آنها استفاده کرد.                                                                                                         سری ولیست در ا ل                  

معرفی سری و لیست در ا ل

برای ایجاد سری ها در ا ل میتوان از فرمول نویسی استفاده نمود ، سری های عددی (برای داده های نوع عددی) در ا ل به دو دسته ی کلی linear و growth تقسیم میشوند، از طرفی سری نوع date (برای داده های نوع تاریخ و زمان) با توجه به کاربرد ان در ا ل تعریف شده است. کاربرد و روش ایجاد هر سری در ادامه توضیح داده خواهد شد.

سری ها در ا ل

دو نوع سری عددی در ا ل میتوان تعریف کرد، نوع اول سری linear میباشد که در آن هر جمله از سری (series) برابر است با جمله ی قبلی بعلاوه یک عدد ثابت و نوع دوم سری growth است که در آن هر جمله برابر است با جمله قبلی ضرب در یک عدد ثابت، این دو سری برای داده های عددی تعریف شده اند و برای کارهایی مانند ایجاد شماره سطر و یا ایجاد سری های ریاضی به کار میروند. نوع دیگری سری در ا ل وجود دارد به نام سری date، هر عددی در ا ل (در یک بازه مشخص) در واقع میتواند سریال نامبر یک تاریخ و زمان در ا ل باشد. سری نوع date در واقع این تاریخ ها را ایجاد مینماید، مثلاً اگر  بخواهید تمام روزهای هفته از یک تاریخ خاص تا تاریخ خاصی دیگر را پشت سر هم در یک ستون درج کنید میتوانید از سری نوع date استفاده کنید.

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

برای تعریف سری ها در ا ل از تب home و در گروه editing منوی fill را انتخاب کنید:

 

برای ایجاد سری ها، ابتدا جمله اول سری را در یک سلول بنویسید، آن سلول را انتخاب نموده و مسیر توضیح داده شده در بالا را بپیمایید. برای سری های نوع date دقت داشته باشید که این سری را میتوانید برای days یا روزها، weekday یا روزهای هفته (که روزهای شنبه و یکشنبه در این سری وارد نمیشوند)، month یا ماه ها , years یا سالها نوشته شود و هر ماه و سال دقیقاً منطبق با تعداد روزهای آن در تقویم حساب میگردد.   

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

برای تعریف سری ها در ا ل از تب home و در گروه editing منوی fill را انتخاب کنید:

 

برای ایجاد سری ها، ابتدا جمله اول سری را در یک سلول بنویسید، آن سلول را انتخاب نموده و مسیر توضیح داده شده در بالا را بپیمایید. برای سری های نوع date دقت داشته باشید که این سری را میتوانید برای days یا روزها، weekday یا روزهای هفته (که روزهای شنبه و یکشنبه در این سری وارد نمیشوند)، month یا ماه ها , years یا سالها نوشته شود و هر ماه و سال دقیقاً منطبق با تعداد روزهای آن در تقویم حساب میگردد.

 

لیست در ا ل

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

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

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

در ا ل به صورت پیش فرض لیست هایی مانند فهرست ماه های میلادی یا لیست روزهای هفته وجود دارد که با نوشتن یک نام از هریک لیست های موجود ا ل می تواند بقیه سلول ها را پر نماید. در یکی از سلول های فایل خود “ urday” را بنوسید و با درگ سلول مشاهده نمایید که ا ل نام بقیه روزها را اضافه می نماید.

برای وارد لیست داخواه خود در ا ل ۲۰۰۷ ،  وارد پنجره excel option شده و در تب popular بر روی دکمه ی edit custom list کلیک نمایید. در نسخه های جدیدتر ا ل این دکمه در قسمت general و تب a nced قرار گرفته است.

 

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

 

 

 

 

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

معرفی ابزار sort در ا ل

ابزار sort در ا ل (custom sort) در تب data و در گروه sort & filter قرار گرفته است

 

کار با ابزار sort در ا ل

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

میخواهیم ج را بر مبنای ستون دوم (item name) و به ترتیب حروف الفبا مرتب کنیم، برای انجام اینکار ابتدا سلولی از ج مورد نظر را انتخاب کنید و سپس بر روی آی sort در تب data کلیک نمایید، پنجره sort باز میشود.

در sort level موجود در پنجره sort، در کادر sort by نام ستونی (فیلد) از ج خود که میخواهید مرتب سازی بر اساس آن صورت گیرد (ستون item name) را انتخاب نمایید و در قسمت های sort on و order با توجه به نوع داده های ستون مورد نظر، روش sort را انتخاب کنید. در اینجا میخواهیم ستون item name را به ترتیب حروف الفبا مرتب کنیم.

پنجره sort نشان داده شده در بالا تنها دارای یک sort level میباشد، یعنی ج تنها بر مبنای ستون دوم و به صورت صعودی مرتب شده است ، در پنجره sort در ا ل با استفاده از دکمه ی add level میتوان یک sort level جدید ایجاد نمود.

همانطور که گفته شد، در پنجره sort در ا ل میتوان با استفاده از add level یک ج را در چند سطح (level) مرتب سازی (sort) کرد. به وجی ج   توجه کنید، ستون دوم طبق خواست ما به صورت صعودی مرتب شده است، اما اگر بخواهیم برای یک کالا، نام فروشنده ها نیز مرتب شود باید چه کنیم! یعنی اگر بخواهیم مثلاً برای کالای eraser اسم فروشنده های این کالا در ستون سوم به ترتیب حروف الفبا باشد. برای این کار باید یک level به پنجره sort اضافه نماییم.

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

برای اضافه level های بعدی میتوان همچنان از add level یا copy level استفاده کرد، برای پاک یک level از گزینه delete level و برای جابجا ترتیب level ها میتوان از دکمه های move up و move down استفاده کرد.

با کلیک روی دکمه option وارد پنجره تنظیمات sort در ا ل میشویم، در اینجا میتوانیم در مورد case sensitive بودن sort ها و orientation آنها تصمیم بگیریم.

انواع sort در ا ل

ستون های جداول در ا ل را میتوان بر اساس value (یعنی محتوای سلول ها)، cell color (رنگ سلول ها)، font color (رنگ فونت سلول ها) و cell icon (آی سلول ها) مرتب سازی کرد. اگر ستون داده ها بر اساس value مرتب شود برای داده های عددی گزینه های smallest to largest و largest to smallest و برای داده های متنی گزینه های a to z و z to a وجود دارد.

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

          

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : sort در ا ل - sort ,ستون ,level ,ا ل ,مرتب ,داده ,پنجره sort ,ابزار sort ,حروف الفبا ,مرتب سازی ,item name ,برای انجام اینکار
sort در ا ل sort ,ستون ,level ,ا ل ,مرتب ,داده ,پنجره sort ,ابزار sort ,حروف الفبا ,مرتب سازی ,item name ,برای انجام اینکار
go to در ا ل

    go toدر ا ل                                                                                                                                   به صورت خلاصه، ابزار go to در ا ل برای انتخاب محدوده ها (range) کار برد دارد. فرض کنید شما مشغول فرمول نویسی یا رفع اشکال فرمول ها در یک spreadsheet حرفه ای هستید یا اینکه میخواهید فرمول های نوشته شده در یک فرمول شیت را بررسی کنید. برای اینکار، در وهله ی اول نیاز به شناخت سلول های حاوی فرمول دارید. یا فرض کنید در یک فایل ا ل طولانی میخواهید به آ ین سلول را فعال نمایید و یا یک محدوده پیوسته از ا ل (area) را انتخاب کنید. ابزار go to در ا ل از جمله tools های کاربردی میباشد که در ادامه با آن آشنا خواهیم شد.

معرفی go to در ا ل

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

لازم به ذکر است پنجره go to special هم به عنوان قسمتی از ابزار go to به شمار می آید که در این مقاله مورد بررسی قرار میگیرد.

کاربرد ابزار go to در ا ل به اینجا ختم نمیشود.

 

کار با ابزار go to در ا ل

در تب home و در گروه editing، بر روی منوی find & select کلیک نمایید. با کلیک بر روی گزینه ی go to، پنجره go to باز میشود،(با فشردن کلید ctrl + g میتوان پنجره go to را باز کرد):

 در پنجره go to، تمام نام های تعریف شده در فایل ا ل فعال نشان داده میشوند که کاربر میتواند با کلیک بر روی هریک، سلول های مربوط به آنها را انتخاب نماید.  در قسمت reference میتوان نام هر محدوده ی دلخواه در فایل ا ل را وارد نموده با فشردن کلید ok آن محدوده را انتخاب کرد. از طرفی کلیک بر روی دکمه special در پنجره go to در ا ل و یا انتخاب گزینه go to special از منوی find & select پنجره go to special را باز میکند .

پنجره go to special در ا ل

پنجره go to special در ا ل برای انتخاب سلول های ا ل به کار میرود.

 

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

گزینه constant: سلول های حاوی مقدار ثابت را انتخاب میکند، این مقدار ثابت میتواند هریک از انواع داده ها باشد.

گزینه formulas: سلول های حاوی فرمول را انتخاب میکند، اگر تنها گزینه ی number انتخاب شده باشد، سلول های حاوی فرمول که وجی آنها عدد باشد انتخاب میگردد. گزینه ی text، سلول های حاوی فرمول که وجی آنها داده متنی باشد را انتخاب میکند. گزینه logical سلول های حاوی فرمول که وجی آنها داده منطقی باشد را انتخاب میکند و در نهایت گزینه error سلول ها حاوی فرمول که وجی آنها خطا باشد را انتخاب مینماید. از این گزینه میتوان برای مشخص سلول های حاوی فرمول در بحث رفع اشکال فرمول ها در ا ل استفاده نمود.

گزینه blanks: سلول های خالی را انتخاب میکند. از این گزینه، پس از انتخاب سلول های خالی، میتوان با کلیک روی دکمه های delete row یا delete column در منوی delete ، گروه cells و در تب home برای delete سطر ها و ستون ها استفاده کرد.

گزینه current region: سلول های محدوده (area) مربوط به سلول انتخاب شده را انتخاب میکند، برای انتخاب تمام سلول های یک ج یا یک بانک داده از این گزینه میتوان استفاده نمود.

گزینه objects: تمام اشیا را انتخاب میکند، مانند شکل ها، نمودار ها، smart art ها و غیره.

 

گزینه row differences: فرض کنید تعدادی سطر داده داریم که در هریک از آنها فرضاً سه ستون قرار دارد، فرض کنیم داده های این سه ستون قرار است ی ان باشند (یعنی هر سطر داده های ی ان داشته باشد)، ، شما میخواهید آنها را با هم مقایسه کنید و داده های متفاوت را مشخص نمایید. برای پیدا و انتخاب داده های غیر ی ان در هر سطر، کافیست از گزینه row difference در پنجره go to special استفاده کنید. قبل باز پنجره go to special محدوده مورد نظر را انتخاب کنید.

گزینه column differences: فرض کنید تعدادی ستون داده داریم که در هریک از آنها فرضاً سه سطر قرار دارد، فرض کنیم داده های این سه سطر قرار است ی ان باشند (یعنی هر ستون داده های ی ان داشته باشد)، شما میخواهید آنها را با هم مقایسه کنید و داده های متفاوت را مشخص نمایید. برای پیدا و انتخاب داده های غیر ی ان در هر ستون، کافیست از گزینه column difference در پنجره go to special استفاده کنید. قبل باز پنجره go to special محدوده مورد نظر را انتخاب کنید.

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

گزینه dependents: تمام سلول هایی که در آنها از سلول فعال استفاده شده است را انتخاب میکند، با انتخاب گزینه direct only، تنها سلول هایی که سلول فعال مستقیماً در آنها استفاده شده است انتخاب میگردند و با انتخاب گزینه all levels تمام سلول هایی که در فرمول نوشته شده در آنها (مستقیم و غیر مستقیم) سلول فعال استفاده گردیده انتخاب میگردند.

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

گزینه visible cells only: سلول های visible را انتخاب میکند، یعنی سطر ها و ستون های hide شده را انتخاب نمیکند. این گزینه زمانی کار میکند که سطرها و ستون های hide شده وجود داشته باشد.

گزینه conditional formats: سلول هایی که ابزار conditional formatting در مورد آنها به کار رفته انتخاب میکند.

گزینه data validation: سلول هایی که ابزار data validation برای آنها استفاده شده است را انتخاب میکند. انتخاب گزینه same به ما این امکان را میدهد که سلول هایی را که data validation در آنها با محدودیتی مشابه محدودیت به کار رفته برای سلول فعال استفاده شده است را انتخاب کنیم و گزینه all تمام سلول هایی که این ابزار در آنها استفاده شده است را انتخاب میکند.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : go to در ا ل - سلول ,گزینه ,فرمول ,ا ل ,داده ,استفاده ,سلول هایی ,انتخاب میکند ,حاوی فرمول ,تمام سلول ,انتخاب میکند، ,انتخاب میکند گزینه ,برای انتخاب محدوده
go to در ا ل سلول ,گزینه ,فرمول ,ا ل ,داده ,استفاده ,سلول هایی ,انتخاب میکند ,حاوی فرمول ,تمام سلول ,انتخاب میکند، ,انتخاب میکند گزینه ,برای انتخاب محدوده
تکنیک های اشکال ز فرمول ها
کاربران ممکن است در فایل های ا ل خود از فرمول های زیادی استفاده کرده باشند، طبیعی است که برخی از فرمول ها آن طور که مد نظر کاربر میباشد عمل نکند یا اینکه وجی فرمول خطا باشد. در مواقع بسیاری و در مواردی که فرمول ها پیچیده و یا تعداد پارامترهای دخیل در مساله زیاد باشد پیدا مشکل ممکن است زمانبر باشد. به همین دلیل در این مقاله به موضوع بسیار مهم تکنیک های اشکال ز فرمول ها در ا ل میپردازیم.

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

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

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

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

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

     

    توابع is و توابع ترکیبی if در اشکال ز فرمول ها

     توابع بر اساس محتوا در ا ل شامل سه تابع isodd، iseven و isformula و توابع is هستند. توابع is در ا ل شامل توابع isblank، iserr، iserror، islogical، isna، isnontext، isnumber، isref و istext میباشند. تمامی این توابع ساختار ی انی دارند و وجی آنها boolean میباشد، توابع is در ا ل تنها یک ورودی میگیرند و آن را برای شرط خاصی بررسی میکنند و وجی تمام این توابع تنها true یا false میباشد.

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

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

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

    فرض کنید قرار است کاربر در سلول a1 تعداد کالای مورد درخواست را وارد کند و این عدد در فرمول های دیگر برای محاسبات پارامترهای مورد نیاز استفاده شود، حال اگر کاربر a1 را خالی بگذارد و یا سهواً داده ای غیر عددی در آن وارد نماید مسلماً فرمول های ما با خطا مواجه خواهند شد، برای این کار میتوانیم در سلول a2 فرمول مشابه فرمول زیر بنویسیم:

    =if(or(isblank(a1);type(a1)<>1);”wrong data, number only please”; “let’s do next step”)

    در ساختار فرمول بالا از تابع or (توابع منطقی در ا ل) و توابع type و isblank استفاده شده، به این ترتیب در صورتی که سلول a1 خالی باشد و یا داده ای غیر عددی در آن وارد گردد پیامی مبنی بر خطا بودن نوع داده ی ورودی به کاربر نشان داده میشود. اگر قرار بود عدد وارد شده در a1 به عنوان ورودی تابع power (توابع ریاضی) باشد، مسلماً غیر عددی بودن a1 باعث بروز خطا در فرمول میگردید، در چنین موردی ساختاری مانند ساختار زیر را میتوان استفاده کرد:

    =if(or(isblank(a1);type(a1)<>1);”wrong data in a1″;power(a1;2))

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

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

    =if(iserror(a1);”this formula contains error”; “acceptable outcome”)

    حتی در فرمول نویسی حرفه ای تر میتوان به کاربر با توجه به نوع خطای اتفاق افتاده پیام متناسب داد، مثلاً در صورت بروز خطای n/a# در وجی تابع vlookupحتماً میتوان این نتیجه را گرفت که عبارت مورد نظر در ج مورد جستجو وجود نداشته، این پیام را میتوان از ساختاری مشابه ساختار زیر به کاربر داد:

    =if(error.type(a1)=7;”the required item is not in table”;””)

    در فرمول بالا در صورت بروز خطای خطای n/a# پیامی برای کاربر نمایش داده میشود و او را در جریان قرار میدهد، حتی میتوان ساختار زیر را مستقیماً در سلول a1 استفاده کرد:

    =if(error.type(vlookup(“excelpro”;a2:c13;2;false))=7;”the required item is not in table”;vlookup(“excelpro”;a2:c13;2;false))

    در ساختار بالا عبارت excelpro در محدوده a2:c13 جستجو میشود و در صورت پیدا نشدن پیامی متناسب به کاربر داده میشود.

     

    استفاده از گروه formula auditing در اشکال ز فرمول ها

    گروه formula auditing در تب formulas برای رصد وضعیت فرمول ها در ا ل مورد استفاده قرار میگیرد.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : تکنیک های اشکال ز فرمول ها - فرمول ,ا ل ,داده ,توابع ,استفاده ,تابع ,اشکال ز ,ز فرمول ,فرمول نویسی ,داده میشود ,انواع خطاها ,اشکال ز فرمول ,گروه formula auditing
تکنیک های اشکال ز فرمول ها فرمول ,ا ل ,داده ,توابع ,استفاده ,تابع ,اشکال ز ,ز فرمول ,فرمول نویسی ,داده میشود ,انواع خطاها ,اشکال ز فرمول ,گروه formula auditing
گروه formula auditing در ا ل
در فرمول نویسی ا ل مواقعی وجود دارد که فرمول ها به واسطه ماهیت محاسبات و توابع استفاده شده پیچیده میگردند، در برخی از این موارد دنبال روند حل یک مساله و بدست آمدن وجی توسط فرمول نوشته شده امری دشوار میگردد. به عنوان مثال اگر فرمولی از چند تابع که هریک دارای آرگومان های متفاوت هستند تشکیل شده باشد بدیهی است که درک روند بدست آوردن وجی توسط آن فرمول میتواند زمان بر باشد .                                                                                                                                        گروه formula auditing در ا ل

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

ابزار های formula auditing در ا ل

  • در گروه formula auditing در ا ل با انتخاب یک سلول و کلیک بر روی گزینه ی trace dependents ا ل از سلول انتخاب شده فلش هایی به سمت سلول های دیگر ا ل رسم میکند به شرطی که سلول انتخاب شده در فرمول نوشته شده در آن سلول ها استفاده شده باشد، به این ترتیب به راحتی میتوان فهمید که سلول انتخاب شده در کدام فرمول ها مورد استفاده قرار گرفته است.
  • برع ح فوق، برای اینکه بفهمیم در فرمول نوشته شده در یک سلول کدام سلول ها مورد استفاده قرار گرفته اند، میتوانیم بر روی سلول حاوی فرمول کلیک کرده و گزینه ی trace precedents را انتخاب کنیم.
  • برای حذف فلش های رسم شده  میتوانیم بر روی گزینه ی remove arrows کلیک نماییم.
  • در ا ل به صورت پیشفرض نتیجه ی فرمول های نوشته شده در یک سلول به کاربر نشان داده میشود و برای مشاهده ی فرمول ها باید از formula bar استفاده کرد. با انتخاب گزینه ی show formulas در گروه formula auditing در ا ل، فرمول های ا ل در سلول ها نمایش داده میشوند.

ارزی و بررسی فرمول ها در ا ل

برای بررسی روند اجرای یک فرمول در ا ل میتوان از گزینه ی evaluate formula در گروه formula auditing در ا ل استفاده کرد. ابتدا سلولی را که قصد بررسی فرمول نوشته شده در آن را دارید انتخاب کنید و سپس بر روی گزینه ی evaluate formula کلیک کنید. با کلیک بر روی گزینه ی evaluate میتوانید گام به گام روند اجرای فرمول را بررسی نمایید.

به عنوان مثال، در سلول a2 دمای هوا را درج نمایید، با نوشتن فرمول زیر در سلول b2 با توجه به دمای هوای ذکر شده در شده در سلول a2 دمای هوا را توصیف کنید:

=if(a2<=0;”freezing”;if(and(a2>0;a2<=8);”cold”;if(and(a2>8;a2<=15);”cool”;if(and(a2>15;a2<=25);”warm”;if(a2>25;” ”)))))

حال دمای هوا را در سلول a2 وارد نمایید و با انتخاب سلول b2 و کلیک بر روی گزینه ی evaluate روند تصمیم گیری و بدست آمدن وجی توسط این فرمول را بررسی نمایید.

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

با کلیک بر روی گزینه ی step in در مراحل evaluate فرمول، مقدار های جایگزین شده برای متغیرها و آرگومان های فرمول (که آدرس سلول میباشند) نشان داده میشود، مثلاً اگر زیر a1 خط کشیده شده باشد با کلیک بر روی step in مقدار سلول a1 نشان داده میشود و با کلیک بر روی گزینه ی step out میتوان به evaluate فرمول ادامه داد.

بررسی خطاها در ا ل

با انواع خطاها در ا ل قبلاً آشنا شدیم، اگر سلولی دارای خطا باشد، با انتخاب آن سلول و کلیک بر روی دکمه ی error checking در گروه formula auditing در ا ل میتوان خطای رخ داده را بررسی کرد.

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

از طرفی در صورتی که در هریک از سلول های ا ل فرمولی نوشته شده باشد که از خود آن سلول در فرمول استفاده شده باشد (مثلاً در a1 بنویسیم a1*5=) خطای circular اتفاق میفتد و وجی فرمول صفر نشان داده میشود، برای اطلاع از خطاهای circular رخ داده در شیت ا ل میتوان بر روی گزینه ی circular reference کلیک نمود.                                            ا ل

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : گروه formula auditing در ا ل - فرمول ,سلول ,ا ل ,گزینه ,کلیک ,formula ,formula auditing ,گروه formula ,نشان داده ,فرمول نوشته ,ا ل میتوان ,گروه formula auditing
گروه formula auditing در ا ل فرمول ,سلول ,ا ل ,گزینه ,کلیک ,formula ,formula auditing ,گروه formula ,نشان داده ,فرمول نوشته ,ا ل میتوان ,گروه formula auditing
data validation در ا ل
   data validation در ا ل                                                                                                                                          ابزار data validation در ا ل یکی از پر کاربرد ترین ابزار ها در فرمول نویسی ها، formula sheet ها، نرم افزار های محاسباتی نوشته شده توسط ا ل و… میباشد. توسط ابزار data validation در ا ل میتوانید کاربر را طوری محدود کنید که فقط داده های مورد نظر شما را در سلول های تعیین شده وارد نماید، به عنوان مثال میتوانید کاربر را محدود کنید در سلولی فقط بتواند عددی زوج وارد کند، یا اینکه فقط اعداد بزرگتر از ۱۳ قابل وارد شدن در سلول باشند یا اینکه کاربر فقط یکی از گزینه های مورد نظر شما را بتواند در سلول بنویسد و بسیاری محدودیت های دیگر که در ادامه با آنها و روش اعمال آنها آشنا میشویم.                                                     

محدودیت ها در data validation در ا ل

برای استفاده از ابزار data validation ابتدا سلول مورد نظر را انتخاب نمایید و سپس از تب data و در گروه data tools بر روی دکمه data validation کلیک کنید،

گزینه ی اول (data validation…) پنجره data validation را باز میکند که برای ایجاد محدودیت ها توسط این ابزار به کار برده میشود. با کلیک بر روی گزینه ی دوم (circle invalid data) سلول هایی از صفحه ا ل که ابزار data validation در مورد آنها به کار رفته و محتوی داده ای نا معتبر میباشد مشخص میشوند. برای مشخص سلول های دارای داده ی نا معتبر،  دور آن ها خطی قرمز ترسیم میگردد که با کلیک بر روی گزینه ی سوم (clear validation circle) خطوط رسم شده پاک میشوند.

در تب اول پنجره data validation در ا ل میتوان تنظیمات مربوط به validation criteria را انجام داد، در کادر allow ابتدا نوع داده یا نوع محدودیت ورود داده به سلول (داده معتبر) را تعیین مینماییم، داده های معتبر را به سه دسته تقسیم کرده ایم، دسته اول شامل گزینه های whole number (عدد)، decimal (عدد علمی)، date و  time (تاریخ و زمان) و text length (طول رشته متنی) میباشد. دسته دوم گزینه list و دسته سوم custom است.

به عنوان مثال، اگر گزینه ی whole number را انتخاب کنیم، به این معنا است که تنها داده های عددی (اعداد) به عنوان محتوای سلول مورد نظر معتبر میباشند.

در کادر دوم (data) جزییات مربوط به گزینه ی انتخاب شده در کادر allow تعیین میگردد، جزییات مربوط به گزینه های قرار گرفته در دسته اول (شامل whole number، decimal، date، time و text length) ی ان میباشد زیرا همگی ماهیتی عددی دارند. در مثال بالا با فرض تعیین whole number یا هریک از گزینه های دسته اول در کادر allow، در این قسمت اعداد و محدوده های مجاز برای ورود به سلول را به ترتیب زیر تعیین میکنیم:

  • اگر میخواهیم اعداد بین دو عدد خاص معتبر باشند گزینه ی between،
  • برای معتبر بودن اعداد خارج از بازه دو عدد خاص گزینه ی not between،
  • برای معتبر بودن تنها یک عدد خاص گزینه ی equal to،
  • برای معتبر بودن اعدادی غیر از یک عدد خاص گزینه ی not equal to،
  • برای معتبر بودن اعداد بزرگتر از یک عدد خاص گزینه ی greater than،
  • برای معتبر بودن اعداد کوچکتر از یک عدد خاص گزینه ی less than،
  • برای معتبر بودن اعداد بزرگتر مساوی یک عدد خاص گزینه ی greater than or equal to،
  • و در نهایت برای معتبر بودن اعداد کوچکتر مساوی یک عدد خاص گزینه ی less than or equal to.

اگر میخواهید سلول مورد نظر در صورت خالی بودن هم مورد بررسی قرار بگیرد تیک گزینه ی ignore blank را بردارید.

        گزینه ی لیست (list) در کادر allow، در صورتی که بخواهید کاربر تنها داده هایی خاص که شما تعیین میکنید را بتواند در سلول وارد کند کاربرد دارد، با انتخاب گزینه ی list، کادری به عنوان source نشان داده میشود که میتوانید لیست داده های دلخواه که در سلول های ا ل نوشته شده است را انتخاب کنید همچنین با تیک زدن گزینه in-cell dropdown با انتخاب سلول مورد نظر توسط کاربر، لیست تعیین شده نشان داده میشود که کاربر میتواند گزینه ی دلخواه را از میان آنها انتخاب کند.                                                 

تعیین محدودیت دلخواه در data validation

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

برای اینکار پس از انتخاب سلول مورد نظر از پنجره data validation و در قسمت allow گزینه ی custom را انتخاب نمایید. همانند آنچه در conditional formatting و ایجاد قوانین دلخواه دیدیم، در اینجا نیز فرمول نوشته شده حتماً باید وجی boolean داشته باشد یعنی وجی فرمول حتماً باید true یا false باشد. بنابراین توابع is که جز توابع اطلاعات (information functions) میباشند در این مقوله بسیار کاربرد دارند از طرفی توابع منطقی (logical functions) مانند توابع if، and و or بسیار مهم و کاربردی میباشند.

                             

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : data validation در ا ل - گزینه ,سلول ,data ,داده ,معتبر ,validation ,data validation ,برای معتبر ,معتبر بودن ,بودن اعداد ,سلول مورد ,بودن اعداد کوچکتر ,بودن اعداد بزرگتر
data validation در ا ل گزینه ,سلول ,data ,داده ,معتبر ,validation ,data validation ,برای معتبر ,معتبر بودن ,بودن اعداد ,سلول مورد ,بودن اعداد کوچکتر ,بودن اعداد بزرگتر
consolidate در ا ل

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

معرفی ابزار consolidate در ا ل

ابزار consolidate در ا ل در تب data و در گروه data tools قرار گرفته است.                  

کار با ابزار consolidate در ا ل

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

برای تهیه گزارش فروش سالیانه شرکت بالا توسط ابزار consolidate در ا ل، ابتدا یک شیت خالی ایجاد میکنیم و یک سلول را انتخاب مینماییم، سپس بر روی ابزار consolidate کلیک میکنیم. پنجره consolidate در ا ل در زیر نشان داده شده است، در این پنجره و در منوی function تصمیم میگیریم که چه نوع عملیاتی باید بر روی داده های عددی انجام گیرد، میتوان هر یک از عملیات های sum، count، average، max، min، product، count numbers، stddev، stddevp، var، varp را بر روی داده های عددی انجام داد.                                                                                                                                   برای اضافه جداول گزارشات فصلی، بر روی دکمه reference در قسمت reference  کلیک نمایید و محدوده مورد نظر را انتخاب کنید، پس از انتخاب محدوده ی مورد نظر بر روی دکمه add کلیک کنید تا آن محدوده در قسمت all references اضافه گردد. (برای این مثال ابتدا محدوده ی ج در شیت یک را انتخاب کنید، آن را add نمایید و سپس محدوده ی ج در شیت دو را انتخاب و add کنید و به همین ترتیب چهار ج مربوط به فصول را از چهار شیت متفاوت انتخاب و add نمایید.)

 

 

  • برای حذف یک محدوده از قسمت all references آن محدوده را انتخاب و بر روی دکمه ی delete کلیک نمایید

 

 

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

 

اگر گزینه ی create link to source data را تیک بزنیم، با تغییر داده ها در هریک از جداول مرجع (references)، اطلاعات وجی ابزار consolidate نیز تغییر خواهند کرد و ج وجی به صورت خ ر به روز رسانی خواهد شد.

 

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

 

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : consolidate در ا ل - ا ل ,consolidate ,ابزار ,ج ,محدوده ,داده ,ابزار consolidate ,فایل ا ل ,کلیک نمایید ,انتخاب کنید، ,عددی انجام
consolidate در ا ل ا ل ,consolidate ,ابزار ,ج ,محدوده ,داده ,ابزار consolidate ,فایل ا ل ,کلیک نمایید ,انتخاب کنید، ,عددی انجام
توابع اطلاعات در ا ل
توابع اطلاعات در ا لتوابع اطلاعات یا گزارش گیری در ا ل (information functions) از جمله توابع عمومی ا ل محسوب میشوند که تمام کاربران ا ل به آنها احتیاج دارند، رس اصلی این نوع توابع، اطلاع از وضعیت سلول ها و فایل ا ل میباشد که در ادامه به آن پرداخته میشود. با توجه به کاربرد وسیع این توابع در رفع اشکال فایل های ا ل یادگیری آنها به افراد در تمامی تخصص ها توصیه میشود.                                                                            

تعریف و دسته بندی توابع اطلاعات در ا ل

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

توابع بدست آوردن اطلاعات در ا ل

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

 

تابع cell در ا ل

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

cell (info_type, [reference])

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

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

تابع error.type در ا ل

تابع error.type یک آرگومان دارد و syntax آن به صورت زیر است:

error.type (error_val)

وجی این تابع عددی متناظر با نوع خطا آرگومان این تابع میباشد، در صورتی که آرگومان این تابع خطا نباشد وجی تابع error.type خطای n/a# خواهد بود. از این تابع میتوان برای تشخیص نوع خطاها و تصمیم گیری بر اساس آن استفاده نمود و یا اینکه مثلاً با استفاده از یک ساختار if برای هر نوع خطا یک رشته متنی در نظر بگیرید که به کاربر توضیح دهد چه چیزی را باید اصلاح کند.                                                                 تابع type در ا ل

از این تابع برای بدست آوردن نوع داده استفاده میشود، سینت تابع type به صورت زیر است:

type (value)

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

 

تابع sheet در ا ل

تابع sheet در ا ل شماره شیت سلول مرجع را ارائه میدهد، “شماره شیت” یعنی اینکه شیت مورد نظر چندمین شیت در مجموعه شیت های موجود میباشد. سینت این تابع به صورت زیر است:

sheet (value)

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

  • شیت شامل تمام انواع شیت های ا ل مانند chart sheet، macro sheet، dialog sheet و worksheet میشود و شیت های visible، hidden و very hidden را در بر میگیرد.
  • اگر آرگومان تابع sheet یک آرگومان معتبر نباشد، وجی تابع خطای !ref# خواهد بود.
  • اگر آرگومان تابع نام یک شیت نا معتبر (مثلاً نوشتن نام اشتباه یا نوشتن نام شیتی که وجود ندارد) باشد، وجی تابع خطای n/a# خواهد بود.

تابع sheets در ا ل

از این تابع برای بدست آوردن تعداد شیت ها استفاده میگردد. سینت تابع sheets به صورت زیر است:

sheets (reference)

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

  • شیت شامل تمام انواع شیت های ا ل مانند chart sheet، macro sheet، dialog sheet و worksheet میشود و شیت های visible، hidden و very hidden را در بر میگیرد.
  • اگر آرگومان تابع sheets یک آرگومان معتبر نباشد، وجی تابع خطای !ref# خواهد بود.

تابع info در ا ل

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

info (type_text)

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

 

توابع دیگر اطلاعات در ا ل

تابع na در ا ل

این تابع یک تابع بدون آرگومان میباشد و خطای n/a# را نمایش میدهد. نمایش این خطا برای کاربر به معنی “هیچ داده ای موجود نیست (no value is available)” میباشد. در فرمول نویسی ها گاهی میتوان بجای خالی گذاشتن سلول ها این خطا را استفاده کرد تا کاربر گمان نکند که سلول اشتباهاً خالی مانده. سینت این تابع به صورت زیر است:

na ( )

تابع n در ا ل

این تابع یک آرگومان دریافت میکند و عدد معادل آن آرگومان را به عنوان وجی میدهد:

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

 

 

 
عنوان وبلاگ : ا ل پیشرفته
برچسب ها : توابع اطلاعات در ا ل - تابع ,آرگومان ,ا ل ,توابع , وجی ,اطلاعات , وجی تابع ,توابع اطلاعات ,بدست آوردن ,برای بدست ,باشد، وجی ,macro sheet، dialog ,sheet، dialog sheet ,آرگومان معتب
توابع اطلاعات در ا ل تابع ,آرگومان ,ا ل ,توابع , وجی ,اطلاعات , وجی تابع ,توابع اطلاعات ,بدست آوردن ,برای بدست ,باشد، وجی ,macro sheet، dialog ,sheet، dialog sheet ,آرگومان معتب
توابع جستجو ومرجع در ا ل
توابع جستجو و مرجع در ا ل یا lookup & reference functions مانند توابع ریاضی و توابع متنی که پیشتر با آنها آشنا شدیم، از جمله توابع عمومی ا ل محسوب میشوند که تمام کاربران ا ل به آنها احتیاج دارند، توابع جستجو و مرجع در فایل های ا لی که محتوی بانک های اطلاعاتی میباشند بسیار کاربرد دارد، برخی از توابع جستجو و مرجع در ا ل مانند توابع vlookup و hlookup بسیار معروف و پرکاربرد هستند. همواره در فرمول ها و توابع ا ل میتوان از سلول های دیگر به عنوان آرگومان استفاده نمود.                                      توابع جستجو و مرجع در ا ل                      

معرفی توابع جستجو و مرجع در ا ل

توابع جستجو و مرجع در ا ل یا lookup & reference functions توابعی هستند که در بانک های اطلاعات در ا ل به صورت گسترده مورد استفاده قرار میگیرند، کارهایی مانند جستجو در بانک های اطلاعاتی، ارجاع دادن و بسیاری موارد دیگر از جمله کاربردهای توابع جستجو و مرجع در ا ل میباشند. در این مقاله برخی از توابع “مرجع” مورد بررسی قرار میگیرند .

 

توابع مرجع در ا ل

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

 

توابع  column و  columns در ا ل

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

column (reference)

  

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

اگر مجموعه ای از سلول ها (range) را بجای reference  قرار دهیم وجی تابع شماره سمت چپ ترین ستون خواهد بود. و اگر جای reference را خالی بگذاریم وجی تابع برابر شماره ستون سلولی که تابع در آن نوشته شده است میباشد.

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

توابع  row و  rows در ا ل

دقیقاً مانند تابع column عمل میکند ولی این تابع اطلاعات ردیف (row) را به ما میدهد. از تابع row در ا ل میتوان شماره سطر یک سلول را به دست آورد، سینت این تابع به صورت زیر است:                      

row (reference)

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

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

توابع  row و  rows در ا ل

دقیقاً مانند تابع column عمل میکند ولی این تابع اطلاعات ردیف (row) را به ما میدهد. از تابع row در ا ل میتوان شماره سطر یک سلول را به دست آورد، سینت این تابع به صورت زیر است:

row (reference)

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

 

اگر مجموعه ای از سلول ها (range) را بجای reference  قرار دهیم وجی تابع شماره بالا ترین سطر خواهد بود. و اگر جای reference را خالی بگذاریم وجی تابع برابر شماره سطر سلولی که تابع در آن نوشته شده است میباشد.

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

تابع address در ا ل

از تابع address در ا ل برای بدست آورد آدرس یک سلول در worksheet استفاده میشود، syntax این تابع به صورت زیر است:

address(row_num, column_num, [abs_num], [a1], [sheet_text])

  • در ساختار این تابع، آرگومان اول و دوم آرگومان های اجباری و عدد هستند و به ترتیب شماره سطر و ستون سلولی هستند که میخواهیم آدرس آن را بدست بیاوریم، آرگومان سوم، چهارم و پنجم آرگومان های اختیاری هستند.
  • آرگومان سوم در مورد نسبی یا مطلق بودن آدرس سلول تعیین تکلیف میکند. این آرگومان میتواند خالی بماند و یا یکی از اعداد ۱، ۲، ۳ یا ۴ باشد، اگر خالی یا عدد یک باشد آدرس دهی به صورت مطلق خواهد بود و اگر عدد ۴ باشد آدرس دهی به صورت نسبی میباشد، اگر میخواهیم آدرس سلول به نحوی بیان شود که سطر به صورت مطلق و ستون نسبی باشد، عدد ۲ و برای برع این ح عدد ۳ را بجای آرگومان سوم قرار دهید.
  • آرگومان چهارم هم یک آرگومان اختیاری و یک مقدار منطقی (logical value) میباشد، اگر این آرگومان خالی بماند و یا true باشد، آدرس سلول به فرمت آشنای a1 یعنی شماره سطر عدد و شماره ستون حرف بیان میشود و اگر این آرگومان false باشد فرمت بیان آدرس سلول به صورت r1c1 میشود که سطر و ستون هر دو عدد هستند.
  • و نهایتاً، آرگومان پنجم نام شیت در برگیرنده ی سلول را در صورتی که تمایل داشته باشیم در آدرس سلول ذکر شود را مشخص میکند.

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

address(8;13;3;false;”sheet1″)= sheet1!r[8]c13

address(8;13;3;true;”sheet1″)= sheet1!$m8

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

تابع areas در ا ل

در ا ل، area به یک محدوده ی پیوسته از سلول ها شامل یک یا چندین سلول که در ح انتخاب قرار دارد گفته میشود، مثلاً سلول a1 به تنهایی میتواند یک area باشد، همینطور محدوده ی a1:c5 هم اگر توسط کاربر انتخاب شود یک area است. تابع areas تعداد area های یک محدوده (reference) را مشخص میکند. سینت این تابع به صورت زیر است:

areas (reference)

در عبارت بالا reference تنها میتواند یک area باشد، برای انتخاب چند area باید از پرانتز استفاده کنیم، به مثال های زیر توجه نمایید:

areas(b2:d4) =1

areas((b2:d4,e5,f6:i9)) = 3

areas(b2:d4b2) = 1

در مثال سوم دو area وجود دارند، اولی b2:d4 و دومی b2 است، در واقع منظور از این نوع نوشتن area ها بدست آوردن area فصل مشترک دو area اصلی میباشد که یک area است و وجی این تابع عدد یک میباشد، اگر دو area قرار گرفته به عنوان reference با هم فصل مشترک نداشته باشند، وجی تابع خطای !null# خواهد بود.

 

تابع choose در ا ل

سینت تابع choose به صورت زیر است:

choose(index_num,value1,value2,…)

در عبارت بالا، آرگومان اول عددی است بین ۱ و ۲۵۴ که در واقع شماره مشخصه (index number) مربوط به یکی از ۲۵۴ مقدار داده شده به تابع در آرگومان های دوم تا دویست و پنجاه و پنجم میباشد.

آرگومان های value1، value2،… تا value254 میتواند یک فهرست دلخواه کاربر باشد (مانند نام پرسنل، شماره اقلام، آدرس سلول و…) که به ترتیب شماره مشخصه (index number) یک تا ۲۵۴ را به خود اختصاص میدهند، وجی تابع choose آیتم مربوط به index_num مورد نظر کاربر میباشد.

اگر عدد قرار گرفته در آرگومان اول کمتر از یک یا بیشتر از تعداد value ها باشد، وجی تابع خطای !value# خواهد بود. و اگر عدد index_num یک عدد اعشاری باشد تنها ارقام صحیح آن عدد در نظر گرفته میشود.

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

sum (choose (2,a1:a10,b1:b10,c1:c10)) = sum(b1:b10)

choose (2,a1:a10,”excelpro”,۱۳)) = excelpro

تابع index در ا ل

این تابع دو ح آرایه ای (array) و reference دارد، ح اول مربوط به آرایه ها میباشد که در مورد آن بعداً بحث خواهیم نمود، سینت تابع index به صورت زیر است:

index(reference,row_num,column_num,area_num)

  • این تابع چهار آرگومان دارد، reference که آرگومان اول میباشد آدرس یک یا چند محدوده یا area میباشد، توجه داشته باشید مانند آنچه در تابع areas یاد گرفتیم در اینجا هم اگر بخواهیم به چند محدوده یا area به صورت همزمان اشاره کنیم نیاز به استفاده از پرانتز داریم.
  • آرگومان دوم و سوم به ترتیب یک شماره سطر و ستون مربوط به reference میباشد که کاربر میخواهد به آن اشاره کند، مثلاً فرض کنید شما محدوده ای b2:e8 را به عنوان reference به تابع معرفی کرده اید، حال میخواهید مقدار سلول c5 را بدانید، بجای آرگومان دوم عدد ۴ و بجای آرگومان سوم عدد ۲ را قرار دهید (زیرا در محدوده ی انتخاب شده b2:e8 سلول c5 در سطر چهارم و در ستون دوم محدوده قرار دارد)، در ضمن اگر reference معرفی شده تنها دارای یک سطر یا ستون باشد میتوانید آرگومان دوم یا سوم را خالی بگذارید.
  • همانطور که گفته شد، میتوانید چند area را در محل reference به تابع معرفی کنید، در این صورت آرگومان چهارم تعیین میکند کدام area مورد نظر شما است، به عنوان مثال اگر سه area به تابع معرفی کنید و آرگومان چهارم عدد دو باشد، تابع در area دوم به دنبال سطر و ستون مورد نظر شما میگردد

اگر شماره سطر و سلول قرار گرفته به عنوان آرگومان دوم و سوم در محدوده ی area مورد نظر نباشد وجی تابع برابر خطای !ref# خواهد بود.

عنوان وبلاگ : ا ل پیشرفته
برچسب ها : توابع جستجو ومرجع در ا ل - تابع ,آرگومان ,سلول ,reference ,ا ل ,شماره , وجی تابع ,توابع جستجو ,آدرس سلول ,بجای reference ,عنوان مثال ,ا ل میتوان شماره ,تابع میتوان تعداد ,خالی بگ
توابع جستجو ومرجع در ا ل تابع ,آرگومان ,سلول ,reference ,ا ل ,شماره , وجی تابع ,توابع جستجو ,آدرس سلول ,بجای reference ,عنوان مثال ,ا ل میتوان شماره ,تابع میتوان تعداد ,خالی بگ
آخرین وبلاگهای به روز شده
وبلاگهای اتفاقی
اخرین جستجو ها
ساحت های پنج‌گانه اخلاق در نهج البلاغه ثبت نام و لیست رشته های فراگیر پیام نور کردستان تحلیل تجزیه کلاس معرفی کلاس تجزیه آزاد شهریار تحلیل سیگنال سیگنال آزاد بیوگرافی آزاده خورشیددوست شهرستان عالیات عتبات ستاد جلسه عتبات عالیات شهرستان کلاردشت اولین جلسه giraffe gives birth but it s not april بسته کامل نمونه سوالات استخدامی قوه قضاییه شغل تقریر نویس انشا جانشین سازی درباره موبایل افکار اقبال لاهوری سکوت سرد روند مد کیف دستی بهار و تابستان حسابداری و حسابرس تراز پرداز آتیه سنجش و مقایسه در مورد دیوار و قفس درباره درس ششم نگارش کلاس دهم سلوم می‌باشند عمومی احشایی پاو وینت شاخه خا وستان سیستم گردش سلوم احشایی وجود دارد اسلاید آهنگ شروین به نام عاشقت بودم گورخواب ها مرز فهمیدن دوربین گل ی نوت 8 ، دریچهای نوین را به دنیای عکاسی موبایل باز م جزوه و نمونه سوالات ادواری فنی و حرفه ای متصدی کافی شاپ کد شغل پاو وینت آشنایی با معماری ی کاخ گلستان هشدار آنلاین درباره اجاره ملک ورثه ای رایگان کتاب اسرار نشانه ها رام رسمی هواوی g630 u10 بیلد b161 اندروید اعزام نمایندگان ایران برای شرکت در مسابقات جام جهانی بازی‌های را آیا فردا ۹بهمن مدارس لواسان تعطیل است؟ ultra private email provider lavabit is back online فروشندگان موفق چه ویژگی‌هایی دارند؟ بررسی جبران خسارات ناشی از نقض قرارداد سوالات تستی صحیفه سجادیه پایه یازدهم hands on raspberry pi 7 inch touch display and case سنگ پینیت طرح جابر با موضوع دستگاه گردش خون مقایسه دیوار با تنهای دوربین ابجکت وجود بررسی بررسی وجود فروش در و پنجره دوجداره استاندارد کرج انشا درباره پلاسکو از زبان پنجره خواص درمانی قارچ ترافل black truffle mushrooms برای چشم و chris distefano pilot from u2018how i met your motheru2019 duo gets cbs order جواب سوال کتاب هدیه آسمان صفحه83 پاو وینت ماشین آلات ساختمانی و عمرانی روستای مشگل مردم روستا خداوند جغری روستای جغری قرار دارد مردم مجبورند آهنگ شستم تو چشم یار متروی فرودگاه اردیبهشت ۹۶ افتتاح می‌شود انشا درمورد خورشید به صورت ذهنی اللهم ارزقنی شفاعه الحسین یوم الورود سیمپسون فنسی جسیکا simpson ادکلن fancy جسیکا سیمپسون jessica simpson ادکلن جسیکا ادکلن جسیکا سیمپسون انشای مقایسه بهشت و جهنم طرح کرامت پایه دوم ابت واحد کار وفای به عهد حرام پرچم سرخی آهنگ محسن چاوشی نداشتی دوست میریزم محسن چاوشی دوست نداشتی آهنگ نداشتی رنجوندنمو جدید محسن دوست نداشتی رنجوندنمو تجربه کار با فریمورک vue js turn your bug finding skills into more cash as google microsoft hike bounties گوشی مراحل اموزش درصد شارژ برنامه هایتان ø¢ù…ø§ø¯ú¯ûŒ ø§ùˆø±ú˜ø§ù†ø³ ú©ø±ù…ø§ù†ø´ø§ù‡ ø¨ø±ø§ûŒ ø§ø±ø§ûŒù‡ ø®ø¯ù…ø§øª ø¨ù‡ ø²ø§ø¦ø±ø§ù† ú©ø±ø¨ù„ø§ûŒ ù…ø¹ù„ø§ انشا درمورد خورشید به روش جانشین سازی samsung investing in 3840x1080 and 3840x1200 curved displays at 144 hz ب مز فات فکر نکنین ارایشگر که اموزش پیش منیر دیده باشه لبنان بازی فکری پازلک اندروید آموزش و نسخه مود شده پول بی نهایت wikimedia incremental dump files for the french wikipedia on march پایان نامه بررسی رابطه عزت نفس، ابراز وجود و متغیرهای جمعیت‌شناختی با مصرف سیگار در بین دانشجویان text message sounds عضویت در کانال تلگرام کرموبله تفاوت تاریکی و روشنایی انشا تحقیق در رابطه با تاریخچه و مبدأ پیدایش هلو on the banks انشایی درباره ی مقایسه وسنجش انسان با سنگ متن ذهنی یا تخیلی درباره مومیایی دانش آموزان دانش آموزان دانش آموز خوشآمدگویی یک زن به انقلاب خوزستان جلسه زیارت برگزار زیارت خوزستان استان خوزستان خانواده اعضای خانواده خالص نیست قاطی داره حضرت زهرا س الگوی دفاع از حقانیت است روش محاسبه جذر اعداد با فرجه دلخواه حسین برای سلامتی جملات ناب انگیزشی جشن یلدا در کانون فرهنگی شهید بهشتی تنهایی ، بهترینم است تغییر زمان برگزاری مجمع بهمن ماه معاونین فناوری زمان عج به تشییع جنازه کدام زن رفتند؟ تشییع پیکر آیت ا شیخ حبیب ا محمدیان ترامپ 45 رئیس جمهوری تدوین قانون جدید انشا درس تضاد مفاهیم وناسازی معنایی نگارش دهم تحقیق در مورد آزمایشاتالکترونیک25ص تا هفته آینده مشکلات نساجی برطرف نشود دیگر روی نیمکت نمی نشینم برنجی فلکه شیرفلکه قیمت ایتالیا فلکه برنجی شیرفلکه برنجی قیمت شیرفلکه لیست قیمت فروشگاه حسین بررسی کف کاذب بررسی تاثیر گروه درمانی شناختی مبتنی بر ذهن آگاهی mbct بر کاهش خستگی روانی ن مبتلا به مولت بد گمانی بخار تولید بخار فایر تیوب بخار فایر نییه نردیوان بالاخانیم قاپی‌نین خانیم اوستاد بالاخانیمدان سوروش قاپی‌نین دالیندا حاجی خانیم ایشینی گؤرندن نردیوان گره‌ک با یاد تو دلشادم تحقیق چگونه میتوانیم جوامع منطقه مان وجهان رااز خشونت برهانیم بارم بندی زیست شناسی دهم بارش شدید 72 ساعته باران در کهگیلویه و بویراحمد گردشگران مراقب باشند ای دیریافته با تو سخن می گویم مسلمانان جهان شادی خود را در روز بعثت چگونه نشان می دهند انتظارم رو بهوصال مبدل کن حضرت عشق جان
Facebook Twitter Google Plus Digg Share This RSS
کلیه فعالیتهای وبلاگ724 تابع قوانین جمهوری اسلامی ایران میباشد. تمامی اطلاعات، خبرها و مقالات بصورت خودکار از سایت ها و وبلاگهای فارسی دریافت و با ذکر منبع نمایش داده می شوند و وبلاگ 724 هیچگونه مسئولیتی در قبال محتوای آنها ندارد. در صورت مشاهده هر نوع تخلف یا محتوای نا مناسب بر روی دکمه “درخواست حذف وبلاگ” در آن صفحه کلیک نمائید.
All rights reserved. © weblog724 2012-2017 Run in 1.425 seconds
RSS