امروز : دوشنبه , ۴ بهمن , ۱۳۹۵

فرض کنید ستونی از اعداد داشته باشیم و بعضی از مقادیر این ستون تکراری باشند و بخواهیم با یک فرمو ل این اعداد را طوری جمع کنیم که هر عدد فقط یک بار در محاسبه در نظر گرفته شود. مثلاً اگر عددی دوبار یا سه بار در لیست تکرار شده باشد فقط یک بار در جمع منظور گردد. برای این منظور از ترکیب سه تابع sum، IF،Frequncey استفاده می کنیم. فرض کنید در ناحیه A2:A12 تعدادی عدد وارد کرده ایم. فرمول را به شکل زیر می نویسیم: =SUM(IF(FREQUENCY(A2:A12,A2:A120)>0,A2:A12,0)) این فرمول اعداد ستون داده شده را جمع می کند به طوری که هر عدد بدون در نظر گرفتن اینکه چند بار تکرار شده باشد فقط یک بار در نتیجه جمع ظاهر می شود. برای درک بهتر این فرمول ابتدا باید نتیجه تابع FREQUENCY را بررسی کنیم. شکل کلی تابع FREQUENCY به این صورت است. FREQUENCY(data_array,bins_array) همانطور که در فرمول می بینید به جای data_array و bins_array ناحیه A2:A12 قرار گرفته است. توجه داشته باشید اگر از  تابع FREQUENCY به تنهایی (بدون ترکیب با فرمول دیگری) استفاده می کنید باید آن را به صورت آرایه ای بنویسید. در این مثال ما تعداد ۱۱ عدد را در ناحیه A2:A12 وارد کرده ایم. اگر بخواهیم با استفاده از تابع FREQUENCY تعداد تکرار هر عدد را حساب کنیم باید ابتدا تعداد ۱۱ سلول را انتخاب نموده و سپس فرمول  (FREQUENCY(A2:A12,A2:A120 را در اولین سلول نوشته و کلیدهای ترکیبی ctrl+shift+enter را فشار دهیم تا فرمول به صورت آرایه ای ثبت شود. تذکر: در فرمول های آرایه ای علامت های {    } در ابتدا و انتهای فرمول به صورت اتوماتیک نوشته می شوند. در فرمول =SUM(IF(FREQUENCY(A2:A12,A2:A120)>0,A2:A12,0)) اگر تعداد تکرار در تابع FREQUENCY بیشتر از عدد صفر باشد آن عدد یک باردر محاسبه منظور می شود و اگر صفر بود یعنی تکرار آن عدد بیش از یک بار …

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : یکشنبه , ۲۴ آبان , ۱۳۹۴

در این پست از نرم افزار اکسل برای تسهیل گزارش گیری از پرداخت به پرسنل استفاده شده است. فرض کنید به افرادی که با شما کار می کنند در تاریخ های مختلف مبالغی را به عنوان مساعده حقوق پرداخت کرده اید. در یک شیت اسامی و مشخصات نفرات همراه با کد پرسنلی هر شخص ثبت شده است. در شیت دیگر مبلغ مساعده های پرداختی را فقط با ذکر شماره پرسنلی هر فرد یاداشت کرده اید. اکنون اگر بخواهید بدانید که جمع دریافتی هر نفر و تعداد دریافت های او چقدر است در شیتی به نام “جمع پرداختی” فرمول های لازم جهت این کار نوشته شده است. در شیت “پرسنل”  اسامی و مشخصات پرسنل ثبت شده است. در شیت “پرداختها” مبالغ پرداختی به پرسنل و در شیت “جمع پرداختی” جمع مبالغ پرداختی به هر شخص مشخص میگردد. در این مثال از فرمولهای برداری استفاده شده است. برای نوشتن فرمول های برداری پس از نوشتن فرمول باید به جای فشار کلید Enter از ترکیب Ctrl+Shift+Enter استفاده کنیم و علامت {  } ابتدا و انتهای  فرمول به صورت اتوماتیک ایجاد میگردند و نباید آنها را تایپ کنیم. فرمولهای استفاده شده در هر سلول در تصویر مشخص شده اند. ضمناً در شیت “پرداختها” محدوده A1:C100 به نام “پرداختها” نامگذاری شده است. و ستون B1:B100 از همین صفحه به نام “شماره_پرسنلی” نامگذاری شده است. در فرمولهای نیز از این نام ها استفاده شده است. برای نامگذاری یک ناحیه یا باید اسم مورد نظر را در کادر نام و یا از طریق سربرگ فرمول ها و سپس از گزینه Define name استفاده نمایید. در آینده روش نامگذاری و استفاده از نام  در فرمول ها در مقاله جداگانه ای تشریح خواهد شد. دانلود فایل کاربرد اکسل در حسابداری         دانلود فایل کاربرد اکسل در حسابداری

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : شنبه , ۱۶ آبان , ۱۳۹۴

با سلام در این پست قصد دارم یکی از ترفندهای بسیار ساده و کاربردی اکسل که شاید کمتر به آن توجه شود را تقدیم شما عزیزان نمایم. در سربرگ Home ازگزینه های Select & Find و انتخاب گزینه replcae  می توان برای پیدا کردن اطلاعات مورد نظر و یا جایگزینی قسمتی از متن با متن دیگر استفاده نمود. کلید میانبر Ctrl+H می باشد. فرض کنید که یک متن ۲۰۰ صفحه ای در اختیار دارید و قرار است که در آن هر جا کلمه “اکسل” ذکر شده باشد کلمه جدید “میکروسافت اکسل ۲۰۱۰” جایگزین آن شود. مسلماً پیدا کردن تمام کلمات و جایگزینی آنها به صورت دستی کار بسیار وقت گیر و طاقت فرسایی است. برای این منظور پس از انتخاب کل متن مورد نظر از سربرگ Home گزینه  find & select  و سپس replace را انتخاب نموده و در کادر مقابل  Find what: کلمه “اکسل” و در مقابل عبارت Replace with: کلمه “میکروسافت اکسل ۲۰۱۰” را تایپ کرده و بر روی دکمه Replace all کلیک نمایید. پیامی مبنی بر اینکه عملیات جستجو خاتمه یافته و تعداد کلمات جایگزین شده ظاهر می شود.  برای اتمام کار بر روی دکمه Ok کلیک نمایید. به عنوان مثالی  دیگر فرض کنید در یک شیت اکسل قرار است اطلاعات ساعت ورود و خروج پرسنل شرکت را وارد نمایید. برای وارد کردن ساعت در اکسل باید فرمت ورود اطلاعات به این شکل باشد: hh:mm:ss که در آن hh معرف ساعت، mm معرف دقیقه و ss معرف ثانیه می باشد. همانطور که می بینید وارد کردن تعداد زیادی ساعت ورود و خروج به شکل بالا بسیار وقت گیر می باشد. راه حل بسیار ساده است. برای این منظور می توانید ساعت را به شکل hh.mm.ss وارد نمایید. یعنی از نقطه معمولی برای جدا کردن ساعت و دقیقه استفاده نموده و پس از اتمام کار نقطه معمولی را با علامت : جایگزین نمایید. برای …

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : دوشنبه , ۱۱ آبان , ۱۳۹۴

روزی یکی از دوستان سئوالی را مطرح کرده بودند که با وجود آنکه راه حل بسیار ساده ای داشت اما این دوست عزیز را به قول خودشان بسیار کلافه کرده بود. متن سئوال این بود: مسئله‌ای بسیار ساده، اما بدون راه حل و کلافه کننده مشکل Copy وPaste  در ردیف‌ های فیلتر شده فایل حاوی کامل سئوال را می توانید از اینجا دانلود نمایید  از آنجا که این سئوال ممکن است برای بسیاری از دیگر دوستان نیز پیش آمده باشد لذا آن را با یک مثال ساده توضیح می دهم. فرض کنید دریک کاربرگ اکسل و در sheet1 اعداد از ۱۰۰ تا ۲۰۰۰ را بخواهیم در یک ستون به ترتیب وارد کنیم. و همچنین فرض کنیم که اعداد ۲۰۰ – 400 – 700 -1100 – 1600 را که در sheet2 قرار دارند، بخواهیم بعداً در این لیست و در جاهای خالی کپی کنیم. اگر تعداد داده هایی که قصد کپی آنها در جاهای خالی را داریم محدود باشند، به راحتی می توان هر کدام را در جای خودش کپی نمود. اما اگر تعداد این داده ها بسیارزیاد باشند، کپی تک تک آنها کار تقریباً غیر ممکنی خواهد بود. توجه: در این مثال ترتیب قرارگیری اطلاعات مهم است. برای این منظور به ترتیب زیر عمل می کنیم. ابتدا یک ستون کمکی را در نظر میگیریم و در آن شماره ردیف ستون اطلاعات را می نویسیم. در مقابل جاهای خالی نیز باید شماره ردیف قرار داشته باشد. ستون اطلاعات که حاوی جاهای خالی می باشند را انتخاب می کنیم. سربرگ Home و گزینه Find&select را انتخاب کرده و گزینه Goto Special… را برمی گزینیم. پنجره ای ظاهر می شود که در آن گزینه Blanks را انتخاب نموده و بر روی دکمه Ok کلیک می کنیم. در این حالت کلیه سلول های خالی لیست به حالت انتخاب در می آیند. پس از انتخاب …

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : سه شنبه , ۲۱ مهر , ۱۳۹۴

در این مقاله می خواهیم اطلاعات دو لیست در اکسل را با هم مقایسه نموده و ورودی هایی که منحصر به فرد هستند (غیر تکراری) را از هر دو لیست پیدا کنیم. بارها اتفاق افتاده که در هنگام کار با صدها ردیف از اطلاعات در اکسل خواسته ایم داده های خود را از دو لیست به صورت دستی مقایسه نماییم. برای آنکه مجبور به مقایسه دستی نباشیم، از ابزارformatting  Conditional و COUNTIF استفاده می کنیم. سئوال: من می خواهم دو ستون از اطلاعات را با هم مقایسه نموده و در هر ستون داده های منحصر به فرد را با یک رنگ خاص مشخص نمایم. در این مثال توضیح داده می شود که چگونه با استفاده از conditional formatting دو ستون از داده ها را با هم مقایسه کنیم. برای مثال دو لیت مطابق شکل زیر را می خواهیم با هم مقایسه نماییم.   برای این منظور به روش زیر عمل می کنیم. ابتدا ناحیه A1:A6 را انتخاب نموده و نام FirstList را برای آن انتخاب می کنیم. (برای نام گذاری در کادر اسم نام مورد نظر را بدون فاصله تایپ کرده و کلید Enter را فشار می دهیم) به همین ترتیب ناحیه B1:B6 را انتخاب نموده و برای آن نام SecondList را انتخاب می کنیم. ناحیه A1:A6 را انتخاب می کنیم . (می توانیم از لیست کادر نام عبارت FirstList را انتخاب نماییم) از سربرگ Home ابزار Conditional formatting را انتخاب نمایید و سپس بر روی گزینه New rule کلیک کنید. از پنجره ظاهر شده عبارت “Use a formula to determine which cells to format” را انتخاب نمایید. فرمول را به صورت زیر وارد نمایید. =COUNTIF(SecondList,A1)=0  بر روی دکمه Format کلید کنید و از پنجره ظاهر شده یک رنگ دلخواه انتخاب نمایید. بر روی دکمه ok کلیک کنید.   چک باکس stop if true را در حالت انتخاب قرار داده و بر …

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : چهارشنبه , ۲۵ شهریور , ۱۳۹۴

در این پست می خواهم روش محاسبه فروش و میانگین هفت روز آخر را بدون توجه به اینکه جدول اطلاعات دارای چند ردیف باشد خدمت شما دوستان عزیز تقدیم کنم. فرض کنید دارای جدولی از فروش در تاریخ های مختلف هستید (شکل زیر) و هر روزه نیز اطلاعات جدیدی به این جدول افزوده می شود. می خواهیم با استفاده از چند فرمول و ترکیب کردن این فرمول ها به صورت پویا همیشه جمع و میانگین فروش آخرین هفت روز را محاسبه نماییم.     برای جمع فروش از فرمول SUM(OFFSET(B2,COUNTA(B:B)-7,0,7,1)) و برای محاسبه میانگین فروش از فرمول AVERAGE(OFFSET(B2,COUNTA(B:B)-7,0,7,1)) استفاده می کنیم. برای آشنایی بیشتر با اجزاء فرمول توابع به کار رفته به صورت مختصر توضیح داده می شوند. تابع AOUNTA(value1,[value2],…)   این تابع تعداد سلول های غیر خالی یک ناحیه را می شمارد. (محتوای عددی و غیر عددی) تابع COUNT(value1,[value2],…) فقط مقادیر عددی را می شمارد تابع OFFSET(reference,rows,cols,[height],[width]) انتقال از محل تعیین شده به تعداد معین سطر و ستونی که برای آن مشخص می کنیم. مثال OFFSET(C3,2,3) یعنی از سلول C3 دو ردیف به سمت پایین و سه ستون به سمت چپ منتقل میشود که در این حالت محتوای سلول F5 نمایش داده خواهد شد. تابع SUM جمع اعداد یک ناحیه و تابع AVERAGE نیز میانگین اعداد یک ناحیه را محاسبه می کند. فایل این پست را از لینک زیر دانلود نمایید. دانلود فایل    

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : سه شنبه , ۱۷ شهریور , ۱۳۹۴

در اینکه تابع VLOOKUP یکی از قوی ترین و مفیدترین فرمول هاست شکی نیست. اما این تابع دارای یک اشکال اساسی است، شما نمی توانید توسط این تابع عملیات جستجو به سمت راست را انجام دهید. (اگر جهت صفحه فارسی باشد جستجو به سمت راست و اگر انگلیسی باشد جستجو به سمت چپ امکان پذیر نیست) فرض کنید در جدول زیر برای آنکه بدانیم مقدار فروش چه کسی ۲۱۳۳ تومان می باشد تابع VLOOKUP نمی تواند این کار را انجام دهد. چون تابع VLOOKUP فقط  پاسخ را در ستون سمت راست مقدار مورد جستجو برمیگرداند. یکی از راههای ساده حل مشکل انتقال ستون فروش به سمت راست اسامی فروشندگان است! اما این راه حل منطقی نیست. همانطور که می دانید ممکن است در همین جدول بخواهیم جستجو را بر اساس مقدار سود انجام دهیم و یا موارد دیگر. پس برای هر جستجو مجبوریم ستون مورد جستجو را به سمت راست انتقال دهیم. راه حل این موضوع استفاده از ترکیب توابع INDEX+MATCH است (OFFSET+MATCH) را نیز می توانید استفاده کنید. شکل کلی ترکیب این توابع به صورت زیر است. =INDEX(ستون اطلاعات مورد جستجو,MATCH(صفر,ستونی که شامل داده مورد نظر است, مقداری که به دنبال آن می گردید )) برای مثال ((INDEX($B$2:$B$14,MATCH(1088,$D$2:$D$14,0 = موقعیت عدد ۱۰۸۸ در ناحیه D2:D14 را پیداکرده و پاسخ مناسب را از ناحیه B2:B14 برمیگرداند (مقدار سمت چپ مورد جستجو) مثال های بیشتر را در جدول زیر ببینید.  

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : چهارشنبه , ۱۱ شهریور , ۱۳۹۴

گاهی لازم است در اکسل یک یا مجموعه ای از اعداد را بدون نوشتن فرمول در عدد دیگری ضرب کنیم، یا عددی ثابت را به تعدادی عدد دیگر بیفزائیم بدون آنکه فرمولی نوشته شود. همچنین تبدیل مجموعه ای از اعداد مثبت به منفی و یا برعکس و یا تقسیم مجموعه ای از اعداد بر یک عدد ثابت را انجام دهیم. برای این کار در اکسل راهی ساده وجود دارد. فرض کنید در سلول های A1:C5 تعدادی عدد وارد کرده ایم. می خواهیم بدون استفاده از فرمول های معمول اکسل چهار عمل اصلی را بر روی این مجموعه از اعداد اعمال کنیم. ابتدا در یک سلول دلخواه عددی را وارد می کنیم. در مرحله بعد این عدد را در حافظه کپی می کنیم (ctrl+c) سپس کل ناحیه اعداد وارد شده را انتخاب نموده و بعد از راست کلیک، از منوی ظاهر شده عبارت Paste special را انتخاب می کنیم. پنجره ای ظاهر می شود. در قسمت پایین این پنجره گزینه های Add، Subtract، Multiply،Divide به ترتیب برای جمع، تفریق،ضرب و تقسیم عدد کپی شده در حافظه بر روی کل اعداد ناحیه انتخابی مورد استفاده قرار می گیرد. نکته: برای تبدیل اعداد مثبت به منفی و یا برعکس، عددی که در حافظه کپی می کنیم باید ۱- باشد. در ویدیوی زیر مثالی برای ضرب یک مجموعه از اعداد در یک عدد ثابت نشان داده شده است.   

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : سه شنبه , ۱۰ شهریور , ۱۳۹۴

در بسیاری از اوقات اطلاعاتی به صورت جدول در اکسل وارد می کنیم. فرض کنید که این اطلاعات را به صورت ستونی وارد کرده ایم و اکنون بنا به دلایلی تصمیم گرفته ایم که این اطلاعات را به صورت سطری وارد کنیم. برای انجام این کار نیازی به حذف اطلاعات و وارد کردن دوباره آنها نداریم. کافی است کل اطلات مورد نظر را انتخاب کرده و در حافظه کپی نماییم، سپس در یک مکان مناسب راست کلیک نموده و گزینه Paste Special …  را انتخاب کرده و تیک گزینه Transpose را فعال نمود و بر روی کلیک Ok کلیک کنیم. خواهید دید که اگر اطلات به صورت ستونی باشند به سطری و اگر اطلاعات به صورت سطری باشند به ستونی تبدیل خواهند شد. در ویدیوی زیر روش انجام این کار را می بینید.  

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : سه شنبه , ۲۷ مرداد , ۱۳۹۴

همانطور که می دانید اطلاعات در اکسل اغلب به صورت جدولی وارد می شوند. در بسیاری از اوقات لازم است پس از تکمیل اطلاعات، خطوط اطراف جدول را رسم کنیم. برای این منظور از کادر … Format Cells و از قسمت Border خطوط اطراف جدول را ایجاد می کنیم. (لازم به یادآوری است که در این ویدیو مفهوم جدول در اکسل مورد نظر نیست و فقط کادر کشی اطراف اطلاعات مد نظر است. چون خود جدول و خواص آن در اکسل بحث کاملاً جداگانه ای است که در در یک فرصت مناسب به آن پرداخته خواهد شد.) اما ما در این ویدیو نشان می دهیم که با استفاده از ابزار Conditional formatting می توان در صورت وارد کردن اطلاعات یک سلول خطوط اطراف اطلات به صورت اتوماتیک ایجاد شده و در صورت حذف هر یک از این اطلاعات، خطوط ایجاد شده نیز پاک شوند.  

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : جمعه , ۲۳ مرداد , ۱۳۹۴
Page 3 of 712345...Last »