آموزش جامع و تصویری کار با توابع و فرمول نویسی در اکسل (Functions)

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

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

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

   دانلود این ویدیو 

بخش هایی از یک تابع

به منظور درست کار کردن، یک عمل باید به شیوه ی بخصوصی نوشته شود که نحوه ی (syntax) نامیده می شود. علامتی که برای هر عملیاتی ضروری می باشد علامت (=) یا سام میباشد (sum) و بعد یکی یا بیشتر شناسه. شناسه ها شامل اطلاعاتی می باشد که شما می خواهید محاسبه کنید در مثال زیر ، مقادیر از سلول A1 تا A20 محاسبه می شوند. شناسه نام عملیات علامت مساوی

کار با شناسه ها

شناسه ها هم به سلول های تکی و هم به ردیفی از سلول ها می توانند اشاره کنند و حتماً باید درون پرانتز باشند. شما بسته به این که این که چه نوع علامت یا نحوی برای عملیات فراهم کرده باشید، می توانید یک شناسه یا چند شناسه در پرانتز بگذارید. به عنوان مثال، عملیات میانگین گیری از سلول B1 تا B9 = Average (b1:b9) میانگین مقادیر را از سلول B1 تا سلول B9 محاسبه خواهد کرد.

چندین تابع باید با یک کاما (،) جدا شوند. به عنوان مثال ، عملیات =sum (A1:A3,C1:C2,E1) مقادیر همه ی سلول ها را در سه شناسه جمع می کند.

ساخت یک تابع جدید (ایجاد تابع)

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

• SUM(سام): این عمل، مقادیر را در سلول های شناسه جمع می بندد.
• Average (میانگین): میانگین مقادیری را که در شناسه هستند، مشخص می کند آن عملیات جمع سلول ها را محاسبه کرده و سپس جمع به تعداد تقسیم شده و نتیجه بدست می آید.
• Count(شمارش): این عمل سلول ها را به صورت عددی ، در شناسه ، می شمارد این عمل برای شمارش سریع کالاها در یک ردیف سلولی، مفید می باشد.
• Max(ماکسیمم): این عمل ، بیشترین مقدار سلول را که در یک شناسه هست معین می کند.
• Min(مینیمم): این عمل، کمترین مقدار را در یک شناسه ، معین می کند.

ایجاد تابع اصلی

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

۱- سلول مورد نظر برای انجام عملیات را انتخاب کنید. در مثال ما سلول c11 را انتخاب می کنیم.

۲- علامت (=) را تایپ کرده و نام عملیات مورد نظر را وارد کنید. شما همچنین می توانید عملیات مرد نظر را از لیست عملیات پیشنهادی که زیر سلولی که شما تایپ کرده اید، ظاهر خواهد شد. در مثال ما، میانگین = (=Average) را تایپ می کنیم.

نام تابع را تایپ کنید یا از لیست پیشنهادی یک تابع را انتخاب کنید

۳- ردیفی از سلول ها را برای شناسه درون پرانتز وارد کنید. در مثال ما، (C3:C10) این فرمول، مقادیر را از سلول C10 محاسبه گرده، جمع می بندد و سپس به تعداد کل سلول ها تقسیم کرده و میانگین را معین می کند.

۴- کلید enter صفحه کلید را فشار دهید. عملیات، محاسبه خواهد کرد و نتیجه در سلول، ظاهر خواهد شد. در مثال ما، قیمت میانگین هر واحد از کالا های سفارش داده شده ۱۵٫۹۳$ بود.

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

ایجاد یک عملیات با کاربرد دستور(auto sum) (جمع کردن اتوماتیک)

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

۱- سلولی را که میخواهید عملیات در آن باشد، انتخاب کنید. در مثال ما سلول D12 را انتخاب خواهیم کرد.

۲- در گروه ویرایش (Editing)روی نوار home ، فلش را پیدا کرده ، سپس دستور autosum و بعد عملیات مورد نظر را از منو انتخاب کنید. در مثل ما ، sum را انتخاب می کنیم.

۳- عمل انتخاب شده در آن سلول ظاهر خواهد شد. اگر به طور منطقی قرار داده شوند، دستور اتو سام یا جمع اتوماتیک، به صورت اتوماتیک یک ردیف سلولی برای شناسه انتخاب خواهد کرد. در مثال ما سلول های D3 تا D11 به صورت اتوماتیک انتخاب شده اند و مقادیرشان برای محاسبه قیمت کل با یکدیگر جمع خواهد شد. شما همجنین می توانید به صورت دستی ردیفی از سلول های مورد نظر را درون شناسه وارد کنید.

ابعاد سلول بصورت خودکار براساس مکان تابع انتخاب خواهد شد

۴- کلید ENTER روی صفحه کلید را فشار دهید. عمل محاسبه خواهد کرد و نتیجه در آن سلول، ظاهر خواهد شد. در مثال ما ، جمع سلول D3 تا D11 606.05$ دلار است.

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

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

کتابخانه ی عملیات

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

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

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

• وارد کردن یک عملیات از کتابخانه ی عملیات
• در مثالی که زیر آورده ایم، از یک عمل به منظور محاسبه ی تعداد روز های کاری که طول می کشد که کالا بعد از اینکه سفارش داده شدند دریافت گردند، استفاده خواهیم کرد. در مثال ما، از داده های ستون های B و C برای محاسبه زمان تحویل در ستون D استفاده خواهیم کرد.

۱- سلولی را انتخاب کنید که می خواهید عملیات در آن باشد. در مثال ما ، سلول D3 را انتخاب می کنیم.

۲- نوار فرمول روی ریبون را برای دسترسی به عملیات کلیک کنید.

۳- از گروه عملیات ، گروه عملیات مورد نظر خود را انتخاب کنید. در مثال ما تاریخ و زمان را انتخاب کرده ایم.

۴- از این گزینه، عملیات مورد نظر خود را انتخاب کنید. در مثال ما ، عمل network days یا شبانه روزی را برای شمارش تعداد روزهای کاری از تاریخ سفارش تا تاریخ تحویل را انتخاب کرده ایم.

۵- وقتی آن گزینه انتخاب شد، جدول شناسه عملیات ظاهر می شود. از اینجا شما می توانید سلول هایی را که در عملیات، شناسه ها را ساختار می بخشند، وارد یا انتخاب کنید. در مثال ما، B3 را در قسمت تاریخ شروع وارد کرده ایم و C3 را در قسمت تاریخ پایان …

۶- وقتی از شناسه ها رضایت پیدا کردید OK کنید.

۷- آن عمل محاسبه شده و نتیجه در سلول ظاهر خواهد شد. در مثال ما، نتیجه نشان می دهد که ۴ روز کاری تا دریافت آن سفارش، طول می کشد.

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

کلیک کنید، نگه دارید و بکشید تا یک تابع را به سلولهای دیگر کپی کنید

دستور عملیاتی Insert (وارد کردن)

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

نکته مهم! آموزش تصویری مراجع سلولی (رفرنس دهی سلولی) در نرم افزار اکسل

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

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

۱- سلولی را که می خواهید عملیات انجام دهید، انتخاب کنید. در مثال ما سلول B16 را انتخاب کرده ایم.

۲- نوار فرمول روی ریبون را انتخاب و سپس دستور عملیاتی Insert function (وارد کردن عملیات) را انتخاب کنید.

۳- جدول Insert function ظاهر خواهد شد.

۴- تعداد کمی کلمات کلیدی برای توضیح محاسبه ای که شما می خواهید آن عمل انجام دهد، تایپ کنید. سپس go را کلیک کنید. در مثال ماcount cells (شمارش سلول ها) را تایپ کرده ایم، اما شما می توانید از زیر گروه آن ، کشو را باز کرده و جستجوی بیشتر بزنید.

کلیدواژه را سرچ کنید و سپس Ok را کلیک کنید

۵- نتایج را به منظور یافتن عملیات مورد نظر، بازنگری کنید. سپس ok را بزنید. در مثال ما، count A را انتخاب کرده ایم. چون تعداد سلول ها را از میان ردیف سلول ها، شمارش خواهد کرد.

یک تابع را انتخاب کنید و سپس ok کنید

۶- جدول شناسه ی عملیاتی ظاهر خواهد شد. قسمت value 1 را انتخاب کنید. سپس سلول های مورد نظر را وارد و انتخاب کنید. در مثال ما ، ردیف سلول های A3:A10،(A3تا A10) را انتخاب کرده ایم. شما ممکن است به افزودن شناسه های دیگر در قسمت Value 2 ادامه دهید اما در اینجا ما فقط می خواهیم تعداد سلول ها A3 تا A10 را برای ما شمارش کند.سپس ok را بزنید.

مقدار دلخواه را برای برد سلول وارد کنید و سپس OK کنید

۷- عملیات ، محاسبه خواهد کرد و در نتیجه در سلول، ظاهر خواهد شد. در مثال ما، نتیجه نشان می دهد که کلاً ۸ کالا سفارش داده ایم شده است.

تابع درج شده و ارزش محاسبه شده

نکته مهم! حتما این بسته آموزشی فوق العاده آموزش اکسل را استفاده کنید تا در کمتر از ۱ ماه در این نرم افزار کاربردی حرفه ای شوید!

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