آشنایی با متداول ترین و پرکاربردترین توابع معروف اکسل
اکسل شامل کتابخانه گسترده‌ای از بهترین توابع مالی و اقتصادی و ریاضی است که هر کاربر مستر و استادکار اکسلی در طول کار خود بارها از آنها استفاده میکند و همین موضوع میتواند وجه تمایز بین کاربران مبتدی و استادکار این نرم افزار ورتیکال بحساب آید. در این درس نحوه کار با پرکاربردترین توابع اکسل آموزش داده شده است.
فهرست مطالب آموزش
Functions (توابع) اکسل
ساختمان اکسل شامل یک کتابخانه عظیمی با بیش از 450 تابع است. ما الان در شیتی که اسم آن Functions است و در فایلی به نام 03 – Creating Formulas and Functions قرار دارد میخواهیم مطالب این درس را شروع کنیم و فرمولهای دلخواه را ایجاد کنیم.
توابع ریاضی Excel
اگر شما به تب Formulas در ریبون بروید و روی یکی از نوشته‌های اینجا کلیک کنید، اسلایدی باز میشود که انواع مختلفی از تابعها در آن وجود دارد و اگر روی هر کدام مثلاً این یکی، یا این یکی قرار بگیریم پاپ آپی باز میشود که به ما یادآوری میکند و توضیح میدهد که آن تابع چه کاری انجام میدهد، و خیلی از آنها اینجا وجود دارند، مثلاً من Math & Trig را پیشنهاد میدهم. الان ممکن است این ذهنیت اشتباه را اینجا داشته باشید که به راحتی میتوانید غرق اینها شوید و در کار با اکسل بخواهید همه اینها را پوشش بدهید و یاد بگیرید.
اینجا یک تابع کسینوسی هذلولی معکوس یا hyperbolic است. به نظرتان ممکن است شما به آن نیاز پیدا کنید؟ من شک دارم اینطور باشد، مگر اینکه در عرصه علمی باشید، البته اینجا بعضی از ابزارها و تابعهای با ارزشی وجود دارد.
توابع مالی Excel
اگر شما در زمینه مالی کار میکنید، قطعاً یکسری توابع مالی اینجا است. اینجا شاید 50 تابع یا بیشتر، وجود داشته باشد. اصلاً تعریف یک تابع چیست؟ تابع یک میانبری برای یک فرمول است. در حال حاضر هر کدام از آنها برای کاری مناسب است و شما میتوانید تعداد زیادی از گزینه‌ها را برای استفاده آنها اینجا ببینید. زیر More Functions، یک دسته‌ای به اسم Statistical یا آماری را مشاهده میکنید، و آن خیلی عظیم است.
طریقه نوشتن توابع در اکسل مثلاً تابع RANK
در همین شیت، یکسری اطلاعات در ستون jام وجود دارد که شاید اینها یک توصیفی از کارهایی باشد که ممکن است بخواهیم با این داده‌هایی که اینجا داریم انجام بدهیم. در حال حاضر ما داده‌های خیلی زیادی نداریم، اما شما خیلی راحت تصور کنید این لیستی خیلی بزرگ است. اینجا یک کلمه Rank هم وجود دارد، اجازه بدهید بگویم که ایده این چیست، اگر ما Rank یا رتبه بندی این حقوقها را بخواهیم، به عبارت دیگر بخواهیم این یکی را با بقیه مقایسه کنیم و بگوییم این حقوق نسبت به بقیه در چه رتبه‌ای قرار دارد، ممکن است شما حدس بزنید که تابعی به اسم Rank وجود دارد. الان ممکن است این سؤال برای شما پیش آید که چطور بفهمیم تابعی در اکسل وجود دارد یا نه؟ خب، گاهی اوقات شما این را نمیدانید.
الان یک نفر از شما ممکن است این کلمه یا عبارت را استفاده کرده باشد، یا فقط ممکن است یک روزی اتفاق افتاده باشد که آنرا بین توابع مختلف دیده باشید. خب؛ ما الان اول یک علامت مساوی میگذاریم، بعد حروف آنرا تایپ میکنیم، من حرف R را تایپ میکنم، میبینید که تمام توابعی که با این حرف شروع میشوند را به ما نشان میدهد، و گاهی اوقات شما فقط با نگاه کردن به آنها متوجه میشوید که چه توابع دیگری وجود دارند. اینجا یکی به اسم Rank وجود دارد. در حال حاضر اگر خوب توجه کنید متوجه میشوید که اینجا اسم این مورد، Rank Average است و توضیح آن هم است، این یکی اسمش Rank EQ است، و یک دانه هم از باقیمانده‌های نسخه‌های قبلی وجود دارد، بصورت ساده، یعنی r a n k، که شما نمیتوانید در این لیست آن را ببینید، تا وقتی که خودتان بیایید آنرا تایپ کنید، یک علامت توجه کوچک هم اینجا دیده میشود (کنار کلمه rank در لیست)، ما الان نمیخواهیم به آن کاری داشته باشیم. پس بعد از اینکه بطور کامل rank را تایپ کردم، علامت پرانتز باز هم میگذارم، اما این تابع میگوید، برای نتیجه، یک مقدار، مثلاً درست، اینجا را انتخاب کنید، علامت کاما بگذارید و حالا من میخواهم آنرا با بقیه مقادیر این لیست مقایسه کنم.
بعضی مواقع برای فرمول، بصورت دستی کل ستون را انتخاب میکنیم با این کار تمامی ستون به راحتی انتخاب میشود، گویی که از سلول F2 تا F13 را هایلایت کرده باشیم. کافی است الان من بیایم روی ستون F کلیک کنم و بعد هم اینتر را از صفحه کلید بزنم. بنابراین مشخص میشود که این هشتمین ورودی مقدار بزرگ در این لیست است که تابع Rank، آنرا بدست آورد. خب، ما میتوانیم برای این لیست، بیاییم همین سلول را به پایین درگ کنیم تا فرمول آن کپی شود، خیلی راحت الان میتوانیم رتبه بقیه را هم ببینیم، الان متوجه میشویم که بالاترین مقدار اینجا وجود دارد، این دومی است. اگر لیست ما دو مقدار شبیه به هم داشته باشد، و ما از تابع Rank ااستفاده کنیم، رتبه آنها مشترک میشود. بنابراین من میخواهم برای لحظه‌ای مقدار این دو تا حقوق را مثل هم بگذارم، خب این کار را انجام میدهم، خب الان هر دو آنها در موقعیت یا رتبه 5 قرار گرفتند، اگر به لیست مربوط به رتبه‌ها نگاه کنید ما رتبه 6 را نمیبینیم، بنابراین به خاطر این تشابه، در واقع موقعیتهای پنجم و ششم هر دو اشغال شدند.
دو تغییر دیگری که در تابع Rank وجود دارد و ممکن است شما بخواهید آنها را ببینید.
طریقه کار با چند نمونه از توابع اکسل
اینجا سمت راست ورکشیت ما، بعضی ایده‌های دیگر هم وجود دارد، count data cells. خب؛ خیلی از افراد میگویند چطوری ما اینرا انجام بدهیم؟ خیلی راحت است، اکسل تابعی به اسم COUNTA دارد، من در این سلول علامت مساوی را میگذارم، حالا تابع counta را تایپ میکنم، پرانتز باز را میگذارم و این لیست را انتخاب میکنم، حالا اینتر میزنم، تعداد آنها 72 شد. خب؛ این تابع نه تنها تعداد داده‌های عددی بلکه تعداد همه داده‌ها را به ما میدهد. احتمال دارد این زیاد به درد ما نخورد. الان این یکیcounts numerical cells است. چطوری ما میتوانیم تعداد فقط سلولهای عددی را بدست بیاوریم؟ الان اگر مساوی count بگذارم، پرانتز باز؛ این سلولها را دوباره انتخاب میکنم، دقت کنید که خیلی از آنها عددی نیستند، اما بعضی از آنها هستند.
اینتر میزنم، میبینیم که از این لیست فقط 48 تا از آنها عددی است. الان شما میتوانید بگویید که این مورد یک چیز بی‌اهمیت است و من هم تقریباً با شما موافق هستم، اما گاهی اوقات دانستن اینها مهم است. باز هم اینجا سمت راست نگاه کنید Medianیا میانه خود ما، از طرفی، ارزش آن مثل Average است، اما قطعاً نه دقیقاً مثل آن، Median میگوید اجازه بدهید میانه محدوده یک لیست و یا حتی اعداد ورودی را نشان بدهم، در ضمن اگر تعداد ارقام لیست ما فرد باشد، این تابع مقدار وسطی لیست را به عنوان Median در نظر میگیرد و اگر تعداد مقادیر لیست زوج باشد، قطعاً با تقسیم آن به دو قسمت مساوی مقدار وسطی ندارد، پس می‌آید دو مقدار وسط را انتخاب میکند و میانگین آنها را به عنوان Median به ما نشان میدهد. بنابراین مساوی median. الان، یکبار دیگر میپرسم، چطوری شما متوجه میشوید که تابعی به اسم Median وجود دارد؟ خب، این یک حدس خوب است که اجازه میدهد بگویم، اکسل قابلیتهای آماری زیادی دارد، اما اگر شما به منو فرمول بروید، روی More Functions کلیک کنید، بروید به Statistical، شما میتوانید Median را اینجا ببینید.
و اگر شما آنرا پیدا کنید، چه حدس دیگری دارید؟ آن را اینجا پیدا کردیم، توضیح آن هم وجود دارد. البته گاهی اوقات، شما حس میکنید که باید اینکار را انجام بدهید، خب؛ Median اینجا وجود دارد. پس در سلول مساوی Median را مینویسم و پرانتز باز میگذارم، به نظر شما حد وسط حقوقهای ما چقدر است؟ پس ستون F را انتخاب میکنم یا فقط همین سلولها را انتخاب میکنم. اینتر میزنم، و این هم مقدار میانه یا وسط لیست ما.
طریقه پیدا کردن توابع در نرم افزار اکسل (Insert Function)
خب الان، second largest یا دومین بزرگترین را میخواهیم بدست بیاوریم، من فکر نمیکنم این خیلی مورد نیاز ما باشد، ولی به هر حال ممکن است چند باری در طول سال به آن احتیاج پیدا کنید. برای من در طول 15 سال گذشته اتفاق افتاده، و چند باری هم برای من خیلی مفید بوده.
آیا تابعی برای دومین بزرگترین وجود دارد؟ من میتوانم به شما بگویم آن اینجا است. اما چطوری آنرا کشف کنیم؟ آیا در دسته Math & Trig است؟ خب این مسیر را امتحان میکنیم، اینجا زیر Math & Trig نگاه میکنیم، اینجا که نیست. شاید زیر Statistical باشد. گاهی اوقات شما نمیدانید اصلاً کجا دنبال آن بگردید. خب، در حال حاضر گزینه‌ای در اکسل برای این جور مواقع وجود دارد، شما میتوانید اینجا روی Insert Function کلیک کنید، و در واقع اینجا برای دومین بالاترین، تایپ کنید second largest، و روی Go کلیک کنید و ببینید چه اتفاقی می‌افتد.
در حال حاضر آن به شما هشدار میدهد که در اینجا یکسری توابعی در این مورد وجود دارد، این تابع اسمش Large است، بنابراین من الان فقط این پنجره را از اینجا cancel میکنم. و اینجا مینویسم مساوی Large، دوباره میگویم که من ادعا نمیکنم که این لزوماً چیزی است که به آن احتیاج داریم، فقط به این موضوع اشاره میکنم که اگر یک تابع خاص با ارزشی را نیاز داشته باشیم، دیگر برای همیشه میدانیم آنرا کجا پیدا کنیم. در مثال اینجا، پرانتز باز را میگذارم، حالا کل ستون F را انتخاب میکنم، حالا کاما میگذارم و عدد 2 را مینویسم و اینتر میزنم، خب جواب را بدست آوردیم. ما میتوانستیم در فرمول به جای عدد 2، 3 را برای سومین بزرگترین و 4 را برای چهارمین بزرگترین و به همین ترتیب بگذاریم. همچنین شما ممکن است حدس بزنید که یک تابع برعکس این هم، به اسم Small وجود دارد.
در این مورد ما فقط میخواهیم بدانیم دومین بزرگترین مقدار حقوق چند است و نمیخواهیم به ما بگوید کجا قرار دارد. اما به راحتی میتوانیم آنرا در این لیست کوتاه ببینیم. آن مال ماریا در سطر یازده است.
طریقه انتخاب تابع مناسب اکسل
دوباره اینجا سمت راست STD، را میبینیم انحراف استاندارد یا لاتین آن standard deviation میشود، اما شما لزوماً اینرا نمیدانید چیست، و یکبار دیگر میپرسم، چطوری این رو بفمیم که کجا است؟ چطور میتوانیم در مورد آن حدسی بزنیم و مسیر انتخاب تابع آنرا در اکسل پیدا کنیم؟ شما احتمالاً تصور میکنید که اگر اکسل median را دارد، اگر averages را دارد، خب احتمالاً انحراف استاندارد را هم دارد. اگر شما دنبال آن بگردید، شما آنرا زیر توابع مربوط به Statistical میتوانید پیدا کنید، اما اگر شما شروع به تایپ کردن کنید، بدون شک میدانید آن با ST شروع میشود، درست به این لیست اینجا دقت کنید، ممکن است انتظار داشته باشید که stdev pیا stdev sو یا ممکن است این یکی مربوط به تابع STD باشد و این ابزار را برای ما فراخوانی کند. احتمالاً حس میکنید یکی از اینها همان موردی است که شما به آن احتیاج دارید.
و با این روش، بعضی وقتها برای رسیدن به تابع مد نظرتان با توابع شبیه دیگری هم آشنا میشوید و لیستی از توابع یکسان را خواهید دید و میفهمید که اکسل چقدر کامل است. کافی است روی این کلیک کنیم، شبیه این. حالا اینجا میرویم سراغ حقوقهایمان، و اگر شما چیزی در مورد انحراف استاندارد بدانید خب یعنی یک جوابی بدست می‌آورید که ممکن است آنرا قبلاً حس کرده باشید. آن یک میانگین وزنی از تفاوت بین حقوقها و میانگین آنها بدست می‌آورد. خب؛ من روی اینتر میزنم تا جواب را بدست بیاورم.
جمع بندی توابع اکسل
بنابراین تعداد زیادی از توابع وجود دارند، یک عالمه از آنها است، دوباره، نمیخواهیم زمان زیادی برای پیدا کردن یک تابع صرف کنیم، اما بیشتر شما که با اکسل کار میکنید یک حسی دارید به بعضی چیزهایی که اینجا هستند و در ذهن بسپارید که بعضی از شما هرگز آن را در Math & Trig پیدا نخواهید کرد، بعضی از شما خیلی از آنها را دیدید، تابعهای زیره دسته‌ها مثل Math & Trig. بستگی به این داره که شما کجا دارید کار میکنید و اینکه چه کاری انجام میدهید.
خیلی از توابع دیگر هم است، برای موقعی که شما چند دقیقه‌ای هم بخواهید بیشتر از یکی از آنها را بررسی کنید، مثلاً بخواهید این یکی را چک کنید. بیشتر از 450 تابع در اکسل وجود دارد و آنها برای خیلی از کسانی که کاربر اکسل هستند کاملاً ضروری هستند.