آموزش اکسل رایگان از صفر + فیلم آموزشی رایگان

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

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

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

اکسل چیست؟

بسیار خب، بیایید ابتدا به بنیادین‌ترین سوال ممکن پاسخ دهیم. نرم‌افزار اکسل مایکروسافت (Microsoft Excel) یک اپلیکیشن «صفحه گسترده» (یا Spreadsheet) است که اجازه می‌دهد داده‌های خود را در ردیف‌ها (یا سطرها) و ستون‌های گوناگون قرار دهید، به آن‌ها نظم ببخشید و یا در صورت نیاز، محاسبات لازم را روی آن‌ها اعمال کنید. ضمنا در این نرم‌افزار می‌توان به روش‌های بسیار متنوع و گوناگون به سراغ جداول محوری، نمودارها، توابع و فرمول‌هایی رفت که محاسبات پیچیده یا تصویرسازی داده‌های کلان را آسان می‌کنند.

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

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

از جمله کاربردهای بنیادین اکسل می‌توان به موارد زیر اشاره کرد:

• مدیریت بودجه برای رویه‌های شخصی یا کاری
• مدیریت فاکتورها و رسیدها
• مدیریت پروژه‌ها، مشتریان، کارفرمایان و داده‌های سلامتی‌محور
• ساخت تقویم و برنامه‌ریزی
• ساخت چک لیست و لیست وظایف
• محاسبات امور مالی گوناگون از قبیل وام‌ها، بدهی‌ها و اجاره‌خانه
• انبارداری

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

آموزش اکسل: اصطلاحات پایه

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

• «دفتر کار» (Workbook) و «صفحه گسترده» (Spreadsheet): دفتر کار اکسل در واقع همان چیزی است که به محض باز کردن این نرم‌افزار مشاهده خواهید کرد. دفتر کار خود شامل صفحه گسترده می‌شود که آن نیز یک جدول بزرگ برای درج اطلاعات است. به این ترتیب، صفحه گسترده زیرمجموعه‌ای برای دفتر کار به حساب می‌آید و می‌توانید چندین صفحه گسترده در دفتر خود داشته باشید و به کمک زبانه‌‌هایی که در پایین رابط کاربری قرار گرفته‌اند، میان آن ها جابه‌جا شوید. ناگفته نماند که صفحات گسترده با نام ثانویه «کاربرگ» (Worksheet) نیز شناخته می‌شوند.
•  «سلول» (Cell): با باز کردن یک صفحه گسترده در نرم‌افزار اکسل شاهد انبوهی از بلوک‌های مستطیلی شکلی خواهید بود که سلول یا Cell نامیده می‌شوند. هر سلول می‌تواند میزبان داده‌هایی باشد که در آن وارد می‌کنید و این داده می‌تواند به اشکال مختلف (اعداد، کلمات، تصاویر، فرمول‌ها) به کار گرفته شود. برای وارد کردن داده‌های مورد نظر خود هم می‌توانید آن‌ها را مستقیما درون سلول تایپ کنید و هم به سراغ نوار فرمول در بالای صفحه گسترده و زیر نوار زبانه‌ها بروید.
• در سمت چپ نوار فرمول‌ها، یک کادر به خصوص به چشم‌تان خواهد خورد. این کادر به صورت پیش‌فرض آدرس ستون و ردیفی را نمایش می‌دهد که سلول منتخب شما در آن قرار گرفته. برای مثال سلولی که در بالاترین نقطه و سمت چپ صفحه گسترده شما قرار گرفته، A1 نام دارد، چرا که در ستون A و در ردیف 1 قرار گرفته است.
• «فرمول‌ها» (Formulas) و «توابع» (Functions): فرمول‌های اکسل در واقع مجموعه‌ای از محاسبات و معادلات به حساب می‌آیند. اکسل به شما این امکان را داده که هم فرمول‌های خودتان را بسازید و هم به سراغ توابعی بروید که در واقع فرمول‌هایی از پیش آماده هستند. این توابع می‌توانند محاسباتی مانند ضرب و جمع را به صورت خودکار برای شما انجام دهند. ناگفته نماند که علاوه بر محاسبات ریاضیاتی، می‌توان از توابع برای رسیدگی به امور دیگری مانند شمارش تعداد آیتم‌های موجود در یک ستون یا نمایش کمینه یا بیشینه‌ یک گروه از سلول‌ها استفاده کرد.

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

آموزش اکسل: زبانه‌ها و نوار ابزار

ریبان (Ribbon) یا همان نوار اکسل یکی از مهم‌ترین بخش‌های این نرم‌افزار است که باید اندکی وقت گذاشته و خودتان را با آن آشنا کنید. درست مانند آنچه در دیگر نرم‌افزارهای خانواده آفیس مایکروسافت یافت می‌شود، ریبان حاوی دکمه‌هایی است که آن‌ها را درون زبانه‌های (Tabs) گوناگون می‌یابید. این نوار کاملا قابل شخصی‌سازی است و برای مثال می‌توانید هم دکمه‌ها و زبانه‌های جدیدی به آن اضافه کنید و هم موارد قبلی را حذف کنید.

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

• File: از این دکمه می‌توان برای ساخت کاربرگ جدید، باز کردن کاربرگ‌های از پیش ساخته شده، ذخیره‌سازی، پرینت گرفتن و اشتراک‌گذاری برگه‌ها و صفحات استفاده کرد.
• Home: این گزینه اجازه می‌دهد موارد گوناگونی مانند فونت‌ها، چیدمان، اعداد، حالات (Styles)، سلول‌ها و موارد مرتبط با ویرایش را مدیریت کنید.
• Insert: برای تعبیه جداول، نمودارها، تصاویر، فیلترها و لینک‌ها استفاده می‌شود.
• Draw: امکان استفاده از ابزارهای طراحی و نقاشی گوناگون مانند Lasso Selection (برای برش زدن بخش‌های یک تصویر)، پاک‌کن، قلم و هایلایتر را مهیا می‌سازد.
• Page Layout: تم، ساختار صفحات، ابعاد و گزینه‌های برگه در صفحه گسترده شما را مدیریت می‌کند.
• Formulas: اجازه می‌دهد یک فرمول یا تابع را انتخاب کنید و به ویرایش فرمول‌ها مشغول شوید.
• Data: برای کسب و دگرگون‌سازی داده، مشاهده جستارها و ارتباطات، دسته‌بندی، فیلتر کردن و استفاده از ابزارهای مدیریت داده به کار می‌رود.
• Review: با استفاده از ابزارهای گوناگون می‌توانید به غلط‌گیری، بهبود دسترسی‌پذیری، مدیریت زبان و کامنت گذاشتن مشغول شوید.
• View: گزینه‌هایی برای تغییر نمای کاربرگ دارد، امکان زوم کردن به داخل یا بیرون را مهیا می‌سازد و اجازه می‌دهد پنجره‌ها را مدیریت کنید.

نوار ابزار Quick Access

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

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

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

در نهایت دکمه Save نیز اجازه می‌دهد فایلی که همین لحظه در حال کار روی آن هستید را ذخیره‌سازی کنید.

• مطالب مرتبط پیشنهادی برای مطالعه:
  • تنظیم بوردر در اکسل و تغییر رنگ بردر — راهنمای تصویری (+ فیلم رایگان)
  • شمارش سلول‌ ها در اکسل — روش ها و فرمول های کاربردی
  • نحوه شماره گذاری خودکار ردیف در اکسل — آموزش تصویری
  • راست به چپ کردن کاربرگ اکسل — به زبان ساده

آموزش اکسل: گزینه‌های صفحه گسترده

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

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

• افزودن صفحه گسترده: روی دکمه علامت مثبت (+) در پایین پنجره و کنار آخرین صفحه خود بزنید تا قادر به ساخت صفحه گسترده تازه‌ای باشید.
• تغییر چیدمان صفحات گسترده: زبانه مربوط به صفحه خود را نگه داشته و آن را به جایی تازه در کاربرگ خود بکشید.
• تعیین نام یک صفحه گسترده: روی زبانه مربوط به صفحه گسترده دو بار کلیک کرده و نامی تازه برای آن بنویسید. اکسل به صورت پیش‌فرض از نام‌های «Sheet 1» و «Sheet 2» و باقی موارد استفاده می‌کند.
• رنگ زدن زبانه یک صفحه گسترده: روی زبانه صفحه کلیک راست کرده و زیر Tab Color، به سراغ یک رنگ جدید بروید.
• محافظت از صفحه گسترده: روی زبانه صفحه راست کلیک کرده و زیر بخش Protect Sheet، یک رمز عبور را همراه با گزینه‌های دلخواه خود تنظیم کنید.
• جابه‌جایی یا کپی کردن صفحه گسترده: روی زبانه صفحه راست کلیک کرده و گزینه Move or Copy را انتخاب کنید. سپس می‌توانید صفحه را به جایی دیگر در کاربرگ خود انتقال دهید، آن را به کاربرگی کاملا متفاوت ببرید یا یک کپی از آن بگیرید.
• حذف صفحه گسترده: روی زبانه صفحه راست کلیک کرده و به سراغ گزینه Delete بروید. بعد از این باید درون پنجره‌ای تازه تایید کنید که از کار خود اطمینان دارید.

آموزش اکسل: رسیدگی به امور ساده و بنیادین

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

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

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

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

انتخاب مجموعه‌ای از ستون‌ها، ردیف‌ها و سلول‌ها

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

انتخاب ستون‌ها، ردیف‌ها و سلول‌های مجاور یکدیگر

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

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

انتخاب ستون‌ها، ردیف‌ها و سلول‌های پراکنده

اگر می‌خواهید ستون‌ها، ردیف‌ها و سلول‌هایی را انتخاب کنید که در مجاورت یکدیگر قرار ندارند، لازم است ابتدا به کلیک روی مورد نخست مشغول شوید. سپس دکمه Ctrl را روی کیبورد خود نگه دارید و به صورت همزمان، روی سایر موارد کلیک کنید. هر زمان کار به پایان رسید، دکمه Ctrl را رها کنید.

تعبیه یا حذف یک ستون، ردیف یا سلول

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

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

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

• مطلب پیشنهادی برای مطالعه: آموزش مخفی کردن ستون در اکسل — راهنمای کامل و کاربردی + فیلم آموزشی

جابه‌جا کردن یک ستون، ردیف یا سلول

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

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

تنظیم ابعاد یک ستون یا ردیف

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

ابتدا لازم است ستون یا ردیف مورد نظرتان را انتخاب و روی آن راست کلیک کنید. در منوی تازه‌ای که باز می‌شود، یکی از دو گزینه «عرض ستون» (Column Width) یا «ارتفاع ردیف» (Row Height) را بسته به نیاز خود انتخاب کنید. سپس یک پنجره تازه باز می‌شود و قادر به مشاهده عرض یا ارتفاع کنونی خواهید بود. عدد دلخواه خود را جای مقادیر پیش‌فرض وارد کرده و روی گزینه OK بزنید.

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

متناسب کردن ابعاد با داده شما

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

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

پر کردن خودکار سلول‌ها (Autofill)

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

چند راه برای استفاده از این قابلیت وجود دارد، اما ساده‌ترین روش استفاده از «ابزار پر کردن» (Fill Handle) است. سلولی که می‌خواهید مرجع داده باشد را انتخاب کنید، ابزار پر کردن را در گوشه پایین سمت راست سلول بیابید و یا آن را روی سلول‌هایی که می‌خواهید پر شوند بکشید یا خیلی ساده روی آن دو بار کلیک کنید.

فیلترها (Filters)

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

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

بیایید نگاهی به مثال پایین بیندازیم. با کلیک روی زبانه Data و انتخاب گزینه «Filter»، یک فیلتر را اضافه کنید. سپس روی فلشی که کنار سربرگ‌های ستون قرار گرفته کلیک کنید و می‌توانید انتخاب کنید که آیا می‌خواهید داده به صورت صعودی دسته‌بندی شود یا نزولی. از سوی دیگر می‌توانید ردیف‌هایی که باید به نمایش درآیند را برگزینید.

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

مرتب‌سازی (Sort)

گاهی از اوقات ممکن است فهرست داده‌ای در اختیار داشته باشید که حتی یک ذره هم مرتب نشده است. مثلا شاید فهرست مخاطبان بازاریابی یا پست‌های یک وبلاگ را مستقیم درون اکسل کپی کرده باشید. شرایط هرچه که باشد، قابلیت «مرتب‌سازی» (Sort) به شما کمک می‌کند تا هر فهرستی را براساس حروف الفبا مرتب کنید.

روی داده در ستونی که می‌خواهید مرتب شود کلیک کنید. سپس روی زبانه «Data» در نوار ابزارهای بالایی بزنید و در سمت چپ به دنبال گزینه Sort بگردید. اگر حرف A در بالای حرف Z قرار گرفته، لازم است تنها یک بار روی آن دکمه کلیک کنید. اگر حرف Z در بالای حرف A است، لازم است دو بار روی آن کلیک کنید. وقتی A در بالای Z قرار داشته باشد، فهرست شما به ترتیب حروف الفبا مرتب می‌شود. اما اگر Z در بالای حرف A باشد، همین اتفاق به صورت معکوس رخ خواهد داد.

حذف داده‌های تکراری

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

برای حذف موارد تکراری، ستون یا ردیفی که می‌خواهید عاری از موارد همسان باشد را انتخاب کنید. سپس به زبانه Data بروید و گزینه «Remove Duplicates» را (زیر Tools) انتخاب کنید. یک پنجره تازه باز می‌شود که از شما می‌خواهد اعمال تغییرات در داده را تایید کنید. روی گزینه «Remove Duplicates» بزنید و کار به پایان می‌رسد.

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

چسباندن ویژه (Paste Special)

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

در عوض می‌توانید بگذارید اکسل تمام این کارها را انجام دهد. برای شروع کار، ستون یا ردیفی که می‌خواهید روند انتقال داده را پشت سر بگذارد انتخاب کنید. روی آن راست کلیک کرده و گزینه Copy را انتخاب کنید. سپس به انتخاب سلول‌هایی در صفحه گسترده خود بپردازید که می‌خواهید نقطه شروع ردیف یا ستون شما باشد. روی سلول راست کلیک کنید و سپس گزینه Paste Special را بزنید. وقتی پنجره جدید باز شد، تیک گزینه «ترا نهادن» (Transpose) را بزنید.

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

تقسیم داده

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

به لطف اکسل، هر دو حالت امکان‌پذیر است. پیش از هر چیز، ستونی که می‌خواهید تقسیم داده در آن اتفاق بیفتد را انتخاب کنید. سپس به زبانه Data رفته و گزینه «Text to Columns» را بزنید. یک پنجره جدید به نمایش در می‌آید که حاوی اطلاعات اضافه است. قبل از هر کار دیگری لازم است یکی از دو گزینه «Delimited» یا «Fixed Width» را انتخاب کنید:

• Delimited یعنی می‌خواهید ستون خود را براساس کاراکترهایی نظیر کاما (,)، فواصل (Spaces) یا زبانه‌ها (Tabs) تقسیم کنید.
• Fixed Width یعنی می‌خواهید به انتخاب نقطه‌ای مشخص در تمام ستون‌هایی که فرایند تقسیم شدن را پشت سر می‌گذارند مشغول شوید.

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

سپس نوبت به تعیین «حائل‌ها» یا همان Delimiters می‌رسد. چنین حائلی می‌تواند یک زبانه، یک نقطه ویرگول (؛)، یک کاما (,)، یک فاصله (اسپیس)، یک علامت @ در آدرس ایمیل و یا هرچیز دیگری باشد. در مثال ما، به سراغ فاصله یا اسپیس رفته‌ایم. اکسل سپس یک پیش‌نمایش از شمایلی که ستون‌های جدید خواهند یافت مقابل چشم شما می‌گذارد. زمانی که از پیش‌نمایش راضی بودید، گزینه Next را بزنید. این صفحه به شما اجازه می‌دهد که به انتخاب «قالب‌بندی‌های پیشرفته» (Advanced Formats) نیز مشغول شوید. زمانی که کارتان به پایان رسید، روی Finish کلیک کنید.

آموزش اکسل: میانبرهای کاربردی کیبورد

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

ساخت یک کاربرگ جدید

روی ویندوز: Ctrl + N - روی مک: Command + N

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

روی ویندوز: Shift + Space - روی مک: Shift + Space

انتخاب یک ستون کامل

روی ویندوز: Ctrl + Space - روی مک: Control + Space

انتخاب باقی ستون

روی ویندوز: Ctrl + Shift + Down/Up + روی مک: Command + Shift + Down/Up

انتخاب باقی ردیف

روی ویندوز: Ctrl + Shift + Right/Left - روی مک: Command + Shift + Right/Left

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

روی ویندوز: Ctrl + K - روی مک: Command + K

باز کردن پنجره قالب‌بندی سلول‌ها

روی ویندوز: Ctrl + 1 - روی مک: Command + 1

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

روی ویندوز: = + Alt - روی مک: Command + Shift + T

آموزش اکسل: فرمول‌ها

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

• برای جمع در اکسل از علامت + استفاده کنید
• برای تفریق در اکسل از علاکت - استفاده کنید
• برای ضرب در اکسل از علامت * استفاده کنید
• برای تقسیم در اکسل از علامت / استفاده کنید
• برای افزودن توان از علامت ^ استفاده کنید

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

• مطلب پیشنهادی برای مطالعه: محاسبات آماری با اکسل — راهنمای کاربردی

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

• میانگین گرفتن (Average) از یک بازه سلولی: AVERAGE=
• جمع زدن (Sum) یک بازه سلولی: SUM=
• شمارش (Count) یک بازه سلولی: COUNT=

از سوی دیگر به این دقت کنید که مجموعه‌ای از سلول‌های خاص با علامت کاما (,) از یکدیگر جدا و با علامت دو نقطه (:) نیز نشانه‌گذاری می‌شوند. به عنوان مثال می‌توانید یکی از سه حالت زیر را داشته باشید:

(SUM(4,4= یا (SUM(A4,B4= یا (SUM(A4:B4=

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

آموزش اکسل: قالب‌بندی اولیه

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

فونت‌ها، سایه‌‌زنی و رنگ‌ها

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

برای اینکه ردیف بالایی به شکلی بهتر از انبوه داده‌های زیرین متمایز شود، می‌توانید خیلی ساده آن را قالب‌بندی کنید . ردیف مورد نظر را انتخاب کنید و سپس با استفاده از گزینه‌های موجود در زبانه Home به قالب‌بندی آن بپردازید. در اینجا می‌توانید فونت را برجسته (Bold) کنید، به سراغ یک رنگ پس‌زمینه متفاوت بروید یا رنگ فونت را تغییر دهید:

۱. ردیف بالایی را انتخاب کنید.

۲. روی گزینه Bold کلیک کنید.

۳. روی فلش کناری Fill Color زده و یک رنگ را انتخاب کنید.

۴. روی فلش کناری Font Color زده و یک رنگ را انتخاب کنید.

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

• مطالب پیشنهادی برای مطالعه: 
  • ایجاد واترمارک در اکسل + فیلم آموزش رایگان
  • تنظیم بوردر در اکسل و تغییر رنگ بردر — راهنمای تصویری (+ فیلم رایگان)
  • ساخت فرم ورود اطلاعات در اکسل — راهنمای تصویری (+ فیلم آموزش رایگان)

تاریخ‌ها، واحدهای پولی و اعداد اعشاری

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

تاریخ‌ها

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

۱. ستون یا ردیفی که قرار است تاریخ را در آن وارد کنید، انتخاب کنید.

۲. زیر بخش Number در نوار ابزارهای اکسل، روی فلشی که درون General قرار گرفته بزنید.

۳. از منوی کشویی یکی از گزینه‌های «Long Date» یا «Short Date» را انتخاب کنید.

در نظر داشته باشید که اگر به سراغ Long Date بروید (که کلمات و اعداد نمایش داده شده در پایین را تعبیه می‌کنید) نیازی به تایپ دستی نیست. برای مثال اگر Long Date را انتخاب و سپس تاریخ ۲/۱۴/۱۸ را وارد کنید، به صورت خودکار به شکل «چهارشنبه، ۱۴ فوریه، ۲۰۱۸» نمایش داده می‌شود.

واحد پولی

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

روش اول

۱. ستون، ردیف یا سلولی که می‌خواهید مبلغ در آن وارد شود را برگزینید.

۲. زیر بخش Number در نوار ابزارهای بالایی، روی فلشی که درون General قرار گرفته بزنید.

۳. از منوی کشویی، گزینه Currency را انتخاب کنید.

روش دوم

روش دوم به شما اجازه می‌دهد که واحد پولی مورد نظر خودتان را انتخاب کنید.

۱. ستون، ردیف یا سلولی که می‌خواهید مبلغ در آن وارد شود را برگزینید.

۲. زیر بخش Number در نوار ابزارهای بالایی، روی فلشی که کنار Currency قرار گرفته بزنید.

۳. واحد پولی مورد نظر خود را انتخاب کنید.

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

اعداد اعشاری

برای تنظیم واحدهای پولی یا وارد کردن اعداد ساده می‌توان به سراغ قالب‌بندی اعشاری رفت. برای مثال ممکن است بخواهید نمرات دانشجویان یا حتی خودتان را پایش کنید و برای نیازمند اعداد اعشاری باشید. با دو گام ساده می‌توان از این قالب‌بندی استفاده کرد:

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

۲. زیر بخش Number در نوار ابزارها، روی یکی دکمه‌های «افزایش عدد اعشاری» (Increase Decimal) یا «کاهش عدد اعشاری» (Decrease Decimal) بزنید.

قالب‌بندی شرطی

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

برای شروع کار، مجموعه سلول‌هایی که می‌خواهید از قالب‌بندی شرطی در آن‌ها استفاده شود را انتخاب کنید. سپس در منوی Home به سراغ گزینه «Conditional Formatting» بروید و سپس منطق مورد نظر خود را از منوی کشویی انتخاب کنید. ناگفته نماند که اگر خواستار گزینه‌ای متفاوت از گزینه‌های پیش‌فرض باشید، امکان ساخت شرط‌های اضافه نیز وجود دارد. پس از این یک پنجره جدید باز می‌شود که اطلاعات بیشتری راجع به شرط قالب‌بندی شما می‌خواهد. زمانی که کارتان به پایان رسید روی گزینه OK بزنید و سپس خواهید داد که نتایج به صورت خودکار ظاهر می‌شوند.

ترکیب کردن سلول‌ها با علامت «&»

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

در مثال ما که درون تصویر پایین قابل مشاهده است، از فرمول A2&" "&B2= استفاده شده.

بیایید با یک مثال، نگاهی کامل به این فرمول بیندازیم. فرض می‌کنیم می‌خواهیم نام‌های کوچک و نام‌های خانوادگی را درون ستونی واحد به نام‌هایی کامل تبدیل کنیم. برای این کار ابتدا نشانگر ماوس را روی سلولی خالی می‌بریم که قرار است میزبان نام کامل افراد باشد. سپس یکی از سلول‌هایی که حاوی نام کوچک است را انتخاب می‌کنیم، علامت «&» را در آن می‌نویسیم و سپس به انتخاب کردن سلولی مشغول می‌شویم که حاوی نام خانوادگی همان فرد است.

کارمان هنوز به پایان نرسیده. اگر فرمول شما A2&B2= باشد، در آن صورت هیچ فاصله‌ای میان نام کوچک و نام خانوادگی فرد گذاشته نمی‌شود. برای افزودن این فاصله ضروری، از تابع A2&" "&B2= استفاده می‌کنیم. علامت نقل قول «"» پیرامون فاصله به اکسل می‌گوید که یک فاصله میان نام کوچک و نام خانوادگی بگذارد.

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

اک

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

کلیک کنید

آموزش اکسل: جداول Pivot

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

بیایید یک مثال را از نظر رد کنیم. فرض می‌کنیم می‌خواهید ببینید چند نفر در هر گروه از مدرسه هاگوارتز حضور دارند.

برای ساخت جدول پاشته‌ای به زبانه Data رفته و سپس Pivot Table را برگزینید. اکسل به صورت خودکار جدول پاشنه‌ای شما را پر می‌کند، اما همیشه امکان تغییر دادن ترتیب داده وجود دارد. سپس چهار گزینه پیش روی شما قرار خواهد گرفت:

• Report Filter: این گزینه به شما اجازه می‌دهد که تنها ردیف‌های مشخصی را در مجموعه داده خود ببینید. برای مثال اگر بخواهیم یک فیلتر براساس گروه‌های حاضر در مدرسه هاگوارتز بسازیم، می‌توانیم انتخاب کنیم که از میان تمام دانش‌آموزان، تنها دانش‌آموزان گریفیندور به نمایش درآیند.
• Column Labels: این برچسب‌های ستون می‌توانند سربرگ‌های شما در مجموعه داده باشند.
• Row Labels: این موارد می‌توانند ردیف‌های شما در مجموعه داده باشند. هم لیبل‌های ستون و هم لیبل‌های ردیف می‌توانند حاوی داده ستون‌های شما باشند. (برای مثال نام نخست را می‌توان هم به درون لیبل ستون و هم لیبل ردیف کشید و همه‌چیز بستگی به این دارد که می‌خواهید چطور داده‌های خود را تماشا کنید).
• Value: این بخش به شما اجازه می‌دهد که به شکلی متفاوت داده‌ را مشاهده کنید. به جای وارد کردن صرف مقادیر عددی، می‌توانید به جمع زدن، شمردن یا میانگین گرفتن از اعداد مشغول شوید یا به روش‌های دیگر، داده‌ را دستکاری کنید.

از آن‌جایی که می‌خواهیم تعداد دانش‌آموزان حاضر در هر گروه از مدرسه هاگوارتز را بشماریم، به سراغ جدول پاشنه‌ای خود رفته و ستون House (در اینجا به معنای گروه) را هم به Row Labels و هم Values می‌کشیم. به این ترتیب تعداد دانش‌آموزان مرتبط با هر گروه شمارش خواهند شد.

آموزش اکسل: تابع IF

• از تابع IF برای پیاده‌سازی یک آزمون منطقی استفاده می‌شود، به عبارت دیگر برای بررسی اینکه اینکه چیزی حقیقت دارد یا خیر.
•  ساختار و استدلال‌های تابع IF با این ساختار نوشته می‌شوند: IF(logical_test, value_if_true, [value_if_false])=
• برای مثال: IF(A2>A3,"Bigger","Smaller")=

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

تابع IF چیست؟

تابع IF در اکسل اجازه می‌دهد یک آزمون منطقی بسازید. فرمولی که برای این تابع استفاده می‌شود نیز «گزاره اگر» (IF Statement) نام دارد.

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

تابع IF یکی از چندین توابع منطقی قابل استفاده در اکسل به حساب می‌آید و از دیگر موارد می‌توان به توابع AND و IFERROR و IFS و NOT اشاره کرد.

ساختار و استدلال تابع IF

هر فرمولی که از تابع IF استفاده می‌کند چند بخش مشخص دارد:

1=IF(logical_test, value_if_true, [value_if_false])

• logical_test: شرایطی که در حال آزمودن آن هستید. این بخش حتما باید پر شود.
• value_if_true: اگر logical_test با پاسخ مثبت همراه باشد باید چه اتفاقی بیفتد. این بخش حتما باید پر شود.
• value_if_false: اگر logical_test با پاسخ منفی همراه باشد باید چه اتفاقی بیفتد. این بخش را می‌توان به دلخواه پر کرد.

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

این قوانین را مد نظر نگه دارید:

• اگر logical_test غلط باشد، اکسل پاسخ FALSE می‌دهد و value_if_false نیز حذف می‌شود.
• اگر می‌خواهید نتیجه value_if_true یا value_if_false متنی باشد، باید از علامت نقل قول (") استفاده کنید و تنها موارد استثنا، کلمات TRUE و FALSE هستند.
• تابع IF به بزرگی یا کوچکی حروف در زبان انگلیسی حساس نیست.
• اکسل ۲۰۱۰ به بعد اجازه می‌دهد تا سقف ۶۴ دستور IF درون فرمولی واحد داشته باشد. نسخه‌های قدیمی‌تر اکسل اما به تنها ۷ دستور محدود شده‌اند.

مثال‌های تابع IF

در ادامه آموزش اکسل به روش‌های گوناگونی می‌پردازیم که می‌توانید برای استفاده از فرمول‌های IF به سراغ آن‌ها بروید:

نوشتن متن در صورت صحت دستور

1=IF(A2>A3,"Bigger","Smaller")

این مثالی بسیار ساده از دستور IF در اکسل است و می‌خواهیم بررسی کنیم که آیا مقدار موجود در سلول A2 از مقدار موجود در A3 بیشتر است یا خیر. اگر بیشتر باشد عبارت Bigger به نمایش درمی‌آید و اگر نه، عبارت Smaller.

محاسبه ریاضیاتی در صورت صحت دستور

1=IF(A2>A3,A2-A3)

این دستور IF اندکی متفاوت نوشته شده است. به جای اینکه نتیجه value_if_true یک کلمه باشد، فرمول تفریق یک مقدار از مقدار دیگر است. بنابراین اگر A2 از A3 بزرگ‌تر باشد، اختلاف میان این دو نتیجه فرمول ما خواهد بود. اگر بزرگ‌تر نباشد نیز بخش value_if_false حذف شده و عبارت FALSE را به عنوان نتیجه دریافت خواهید کرد.

آزمودن دستور با ریاضی

1=IF(A2/A3=5,A2/A3,"")

یک راه دیگر برای نوشتن دستور IF، اجرای یک محاسبه درون بخش logical_test است. شرط IF در اینجا، A2/A3=5 است. اگر این فرمول صحیح باشد، در آن صورت محاسبه A2 تقسیم بر A3 انجام می‌شود. اگر نتیجه مساوی با ۵ نباشد، می‌خواهیم هیچ نتیجه‌ای به دست نیاید و بنابراین دو بار از علامت نقل قول (") استفاده می‌کنیم.

• مطالب پیشنهادی برای مطالعه:
  • فرق فرمول و تابع در اکسل — به زبان ساده + فیلم آموزشی رایگان
  • فرمول نویسی در اکسل — به زبان ساده (+ فیلم آموزش رایگان)
  • آدرس نسبی و مطلق سلول در فرمول اکسل — راهنمای کاربردی (+ دانلود فیلم آموزش رایگان)

مثال‌های دستور Nested IF

دستورهای Nested IF به حالتی گفته می‌شود که بیش از یک دستور IF درون فرمول خود دارید. نحوه پیاده‌سازی فرمول تقریبا یکسان است، اما به جای اینکه پرانتز را در بخش پایانی قسمت نخست ببندیم، یک علامت کاما (,) گذاشته و دستوری دیگر را می‌نویسیم.

دو دستور IF در یک فرمول

1=IF(B2="F","Class A",IF(B2="M","Class B"))

از نخستین مثال برای دسته‌بندی دانشجویان براساس جنسیت استفاده کرده‌ایم. دانشجویان زن به کلاس A فرستاده می‌شوند و دانشجویان مرد به کلاس B. فرمول به بررسی F (مخفف Female) و M (مخفف Male) در B2 می‌پردازد و سپس بسته به صحت دستور، کلاس A یا کلاس B را به عنوان نتیجه در نظر می‌گیرد.

تعداد پرانتزهایی که در بخش پایانی فرمول Nested IF نیاز دارید، با تعداد دفعاتی که IF نوشته شده یکسان است. در مثال ما دو IF به چشم می‌خورد و بنابراین به دو پرانتز در انتها نیاز داریم.

سه دستور IF در یک فرمول

1=IF(A2=TODAY(),"This is today",IF(A2<TODAY(),"Old date",IF(A2>TODAY(),"Future date")))

این مثالی از فرمولی است که چندین دستور IF در خود دارد:

• بخش ابتدایی بررسی می‌کند که در سلول A2 تاریخ امروز (Today) نوشته شده یا خیر و اگر این‌طور باشد، عبارت «This is today» را به عنوان نتیجه نشان می‌دهد.
• بخش دوم بررسی می‌کند که آیا تاریخ امروز از A2 بزرگ‌تر است تا مشخص شود که A2 تاریخی قدیمی‌تر است یا خیر. اگر پاسخ مثبت باشد، عبارت «Old Date» به عنوان نتیجه نشان داده می‌شود.
• در نهایت یک آزمون داریم که بررسی می‌کند آیا تاریخ امروز کوچک‌تر از تاریخ A2 است یا خیر. اگر پاسخ مثبت باشد، عبارت «Future» به معنای آینده نشان داده می‌شود.

کپی کردن قیمت در صورت منفی بودن پاسخ

1=IF(C2="Bill","",IF(C2="Food","",B2))

در این مثال پایانی، می‌خواهیم خیلی سریع شمار کامل خریدهایی که درون دسته‌بندی خاصی جای نمی‌گیرند را بشماریم. ما در حال جمع زدن تمام خریدهای غیر ضروری هستیم و اگر لیستی بلند در اختیار دارید، بهترین روش همین است. به نظر ما هر موردی که توضیحاتی حاوی «قبض» (Bill) یا «خوراک» (Food) دارد ضروری است. بنابراین قیمتی که در B2 نشان داده می‌شود باید برای تمام موارد دیگر باشد.
در فرمول ما چنین اتفاقی افتاده:

• "","C2="Bill: اگر در C2 عبارت «Bill» وجود دارد، سلول را خالی بگذار.
• "","C2="Food: اگر در C2 عبارت «Food» وجود دارد، سلول را خالی بگذار.
• B2: اگر هر یک از دستورها منفی بودند، آنچه در B2 است را بنویس.

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

آموزش اکسل: VLOOKUP

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

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

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

• فرمول خام:

1=VLOOKUP(lookup value, table array, column number, [range lookup])

• فرمول مثال ما:

1=VLOOKUP(C2,Sheet2!A:B,2,FALSE)

در این فرمول، چندین متغیر داریم. مثالی که در این مقاله آموزش اکسل آورده‌ایم، زمانی به کار می‌آید که بخواهید اطلاعات Sheet 1 و Sheet 2 را درون Sheet 1 ادغام کنید.

• Lookup Value: این بخش از فرمول به معنای مقدار یکسانی است که در هر دو صفحه گسترده خود دارید. نخستین مقدار در نخستین صفحه گسترده خود را انتخاب کنید. در مثالی که پایین‌تر آمده، باید به سراغ نخستین ایمیل آدرس در فهرست می‌رفتیم که درون سلول C2 قرار گرفته.
• Table Array: این بخش به معنای بازه ستون‌هایی در Sheet 2 است که می‌خواهید داده را از آن‌ها بیرون بکشید، از جمله داده یکسانی که در Lookup Value (در مثال ما می‌شود ایمیل آدرس‌ها) در Sheet 1 داشته‌ایم و همینطور ستون داده‌ای که می‌خواهید به Sheet 1 کپی شود. در مثال ما، ساختار به شکل ".Sheet2!A:B" است. "A" به معنای ستون A در Sheet 2 است که می‌شود ستون‌هایی در Sheet 2 که داده‌ای یکسان با Lookup Value ما در Sheet 1 دارد. "B" به معنای ستون B است که حاوی داده‌ای می‌شود که فقط در Sheet 2 وجود دارد و می‌خواهیم آن را به Sheet 1 بیاوریم.
• Column Number: در آرایه جدول (یعنی بازه ستون‌هایی که همین حالا تعیین کردیم)، این مورد به اکسل می‌گوید که داده جدیدی که می‌خواهید به Sheet 1 کپی کنید، در کدام ستون قرار گرفته. در مثال، می‌شود ستونی که عبارت «House» را در آن می‌یابیم. عبارت «House» متعلق به دومین ستون در بازه ستون‌ها (یا آرایه جدول) است و بنابراین شماره ستون نیز ۲ خواهد بود. توجه داشته باشید که بازه شما می‌تواند حاوی بیش از ۲ ستون باشد. برای مثال اگر سه ستون در Sheet 2 داشته باشیم - حاوی ایمیل، سن و همان House - و هم‌چنان می‌خواهید ستون House را به Sheet 1 بیاورید، کماکان قادر به استفاده از VLOOKUP خواهید بود. برای این کار فقط کافی است عدد «۲» را به «۳» تغییر دهید تا مقادیر ستون سوم بیرون کشیده شوند.
• Range Lookup: از عبارت FALSE استفاده کنید تا اطمینان حاصل شود که فقط مقادیر دقیق و سازگار بیرون کشیده می‌شوند.

در مثال پایین، Sheet 1 و Sheet 2 حاوی فهرست‌هایی هستند که اطلاعاتی متفاوت را راجع به افرادی یکسان به اشتراک می‌گذارند و وجه اشتراک اصلی نیز، ایمیل آدرس‌های آن‌ها است. بیایید فرض کنیم که می‌خواهیم دو مجموعه داده را به گونه‌ای ادغام کنیم که اطلاعات تمام گروه‌ها یا همان Houses از Sheet 2 به Sheet 1 منتقل شود.

بنابراین وقتی فرمول بالا را تایپ می‌کنیم، تمام اطلاعات House به Sheet 1 می‌آید.

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

آموزش اکسل: INDEX MATCH

درست مثل VLOOKUP، توابع INDEX و MATCH در اکسل نیز داده را از یک مجموعه داده دیگر بیرون کشیده و در محلی واحد قرار می‌دهند. تفاوت‌های اصلی به شرح زیر است:

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

بنابراین اگر بخواهیم داده موجود در Sheet 1 و Sheet 2 را به Sheet 1 بیاوریم، اما مقادیر ستون در این برگه یکسان نباشد، برای انجام VLOOKUP باید ابتدا جای ستون‌ها را تغییر دهیم. اما در این مثال ترجیح می‌دهیم در عوض به سراغ یک فرمول INDEX MATCH برویم.

یک مثال را از نظر رد می‌کنیم. فرض می‌کنیم Sheet 1 حاوی فهرستی از نام‌ها و ایمیل آدرس افراد در مدرسه هاگوارتز است و Sheet 2 هم ایمیل آدرس همان افراد را همراه با «پاترونوس» (حیوان محافظ هر دانش‌آموز در مدرسه هاگوارتز) آن‌ها در خود جای داده. اطلاعاتی که هر دو فهرست مشترک است، ایمیل آدرس دانش‌آموزان است. اما ستون ایمیل آدرس در نقطه‌ای متفاوت از هر برگه قرار گرفته. بنابراین به جای VLOOKUP به سراغ فرمول INDEX MATCH می‌رویم تا نیازی به جابه‌جا کردن ستون‌ها نباشد.

اما این فرمول چیست؟ فرمول INDEX MATCH در واقع یک فرمول MATCH است که درون فرمول جداگانه INDEX جای گرفته:

• فرمول خام:

1=INDEX(table array, MATCH formula)

• این فرمول تبدیل می‌شود به:

1=INDEX(table array, MATCH (lookup_value, lookup_array))

• فرمول نهایی همراه با متغیرهای مثال ما:

1=INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

متغیرها به شرح زیر هستند:

• Table Array: بازه ستون‌هایی در Sheet 2 که حاوی داده جدید شده و می‌خواهید آن‌ها را به Sheet 1 بیاورید. در مثال ما، "A" به معنی ستون A است که اطلاعات «پاترونوس» هر فرد در آن جای گرفته.
• Lookup Value: این ستونی در Sheet 1 است که اطلاعات یکسان در هر دو صفحه گسترده را شامل می‌شود. در مثالی که آورده‌ایم، این یعنی ستون «email» در Sheet 1 که می‌شود همان ستون C. بنابراین: Sheet1!C:C
• Lookup Array: این ستونی در Sheet 2 است که اطلاعات یکسان در هر دو صفحه گسترده را شامل می‌شود. در مثالی که آورده‌ایم، این یعنی ستون «email» در Sheet ۲ که می‌شود ستون C: بنابراین: Sheet2!C:C

به محض اینکه متغیرهای صحیح را پیدا کردید، فرمول INDEX MATCH را در بالاترین سلول از ستون خالی Patronus در Sheet 1 بنویسید. اینجا همان جایی است که می‌خواهید اطلاعات سر از آن درآورند.

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

کلیک کنید

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

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

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

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

برای شروع، داده‌ای که می‌خواهید تبدیل به نمودار شود را برگزینید و سپس از منوی بالایی به سراغ گزینه Charts بروید. اگر هم نسخه‌ای قدیمی‌تر از اکسل را در اختیار دارید، لازم است به زبانه Insert رفته و سپس گزینه Chart را انتخاب کنید. سپس قادر به انتخاب نمودار خواهید بود که بیشترین سازگاری را با داده شما دارد.

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