امروز : پنج شنبه , ۱۰ فروردین , ۱۳۹۶

شاید به این نکته توجه کرده باشید هنگامی که در یک سلول اکسل یکی از ماههای میلادی را نوشته (مثلاً بنویسیم Jan) و آن را به سلول های پایین درگ کنیم تمام ماههای میلادی به ترتیب نوشته می شوند. همچنین با تایپ هر کدام از روزها و درگ کردن آنها، بقیه روزها به صورت اتوماتیک نوشته می شوند. اما اگر ایام هفته فارسی یا ماههای شمسی را بخواهیم با این روش تکمیل کنیم، چنین اتفاقی نمی افتد. علت آن است که قبل از این کار ابتدا باید آنها را به لیست های اکسل اضافه کنیم. برای انجام این کار باید از مسیر زیر اقدام کنیم. File/Options/Advanced/General/Edit custom lists… پس از باز شدن کادر لیست به دو روش می توان لیست دلخواه را ایجاد کرد. روش اول: در کادر خالی سمت راست  و زیر عنوان List Entries کلیک کرده و اسامی دلخواه (برای مثال روزهای هفته) را تایپ می کنیم. دقت داشته باشید اگر بخواهید کل روزهای هفته را پست سر هم تایپ کنید باید بین هر کدام از آنها علامت کاما قرار دهید. اما بهتر است پس از تایپ اسم هر روز کلید Enter را فشار داده و اسم روز بعد را در خط بعدی تایپ کنید. پس از اتمام کار بر روی دکمه add کلیک نمایید. اسامی تایپ شده به لیست اضافه می شوند. روش دوم: ابتدا اسامی مورد نظر را در یک ستون اکسل تایپ کرده و آدرس سلول ها ی حاوی اسامی را در کادرImport list from cells: نوشته و سپس بر روی دکمه Import کلیک نمایید. اکنون اگر در یک سلول اکسل مثلاً کلمه (شنبه) را نوشته و آن را به سلول های پایین درگ کنیم، اسامی هفته به صورت اتوماتیک تکمیل می شوند. شکلهای زیر روش کار را نشان می دهند.    

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

کاربرد تابع VLOOKUP تابع VLOOKUP در سری توابع LOOKUP & REFERENCE اکسل قرار دارد. شکل کلی این تابع به صورت: =VLOOKUP(LOOKUP_Value,table_array,col_index_mum, [range_Lookup])) می باشد. به زبان ساده معنی عبارت بالا این است:   Lookup_Valueبگرد به دنبال مقداری که من در سلول … وارد کرده ام. (آدرس سلول به جای Lookup نوشته شود) table_array جدولی که مقدار وارد شده در بالا در آنجا قرار دارد. col_index_num شماره ستونی که مقدار آن باید مقابل Lookup_Value نوشته شود. Range_Lookup (مقدار ۰ و ۱) دقت جستجو را مشخص می کند. مقدار صفر جستجوی دقیق و مقدار یک جستجوی تقریبی را فراهم می آورد. در این پست کاربرد این تابع را با ذکر یک مثال توضیح میدهم. دوستان عزیزی که مسئولیت تهیه صورت وضعیت یک شرکت را به عهده دارند به خوبی می دانند که بدون استفاده از تابع VLOOKUP، وارد کردن شرح، واحد و قیمت آیتمهای فهرست بهاء کاری طاقت فرسا و همراه با اشتباهات زیاد خواهد بود. اما با استفاده از این تابع و فقط با وارد کردن ردیف آیتم مورد نظر، سایر پارامترهای دیگر از قبیل شرح آیتم، واحد، قیمت واحد به صورت اتوماتیک درج خواهند شد. در این مثال فرض ما بر این است که کاربرگهایی که ما روی آنها کار می کنیم (مانند برگ ریز متره، خلاصه متره و …) دارای جهت راست به چپ هستند  (از سربرگ PAGE LAYOUT) گزینه Sheet Right – to- Left را فعال می کنیم.) در این مثال من یک کاربرگ را به عنوان DATA BASE که نمونه ای از فهرست بهای رشته برق می باشد در قالب اکسل تهیه کرده ام. همانطور که در شکل می بینید این نمونه در محدوده A2:D10 قرار گرفته است.  ابتدا بهتر است که این محدوده را نامگذاری کنیم. برای این کار از سربرگ FORMULAS آیتم Name Manager را انتخاب کرده و از پنجره باز شده New را کلیک می …

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

در بسیاری از اوقات لازم است  داده های زیادی را در اکسل وارد نماییم. معمولاٌ این اطلاعات دارای یک عنوان می باشند که در اولین ردیف این عنوان ها قرار می گیرند. عناوینی از قبیل: شماره ردیف، شرح، تعداد و … حال اگر مقدار اطلاعات بیش از یک صفحه شود، در هنگام اسکرول کردن عناوین اطلاعات به سمت بالا حرکت کرده و از دید پنهان می شوند  اگر تعداد ستون های اطلاعات زیاد باشند در این حالت تشخیص اینکه داده های هر ستون چه چیزی را مشخص می کنند دشوار خواهد شد. برای رفع این مشکل باید بتوانیم ردیفی را که شامل عنوان مطالب هستند ثابت نگه داریم تا در حین اسکرول صفحات، این عناوین بدون حرکت مانده و جا به جا نشوند. برای این منظور از سربرگ VIEW مثلث کوچک کنار Freeze Panes را کلیک می کنیم. سه گزینه به نام های Freeze Panes، Freeze Top Row و Freeze First Column قرار دارند. گزینه اول یعنی Freeze Pane سلول های واقع در بالا و سمت راست سلول فعال (سلول که هم اکنون در حالت انتخاب قرار دارد) را در حالت قفل شده نگه می دارد. یعنی با اسکرول کردن به پایین و یا سمت چپ (اگر صفحه در حالت راست به چپ باشد) سلول های بالا و سمت راست سلول فعال بدون حرکت کردن در جای خود ثابت می مانند. کلیک بر روی گزینه Freeze Top Row بدون توجه به سلول فعال، همیشه اولین ردیف کاربرگ را به حالت ثابت در می آورد. کلیک بر روی گزینه Freeze First Column بدون توجه به سلول فعال، همیشه اولین ستون کاربرگ را به حالت ثابت در می آورد. پس از اجرای هر کدام از دستورات بالا، گزینه اول یعنی Freeze Panes به حالت Unfreeze Pane تبدیل می شود که برای لغو دستورات می توان از آن استفاده کرد. تذکر: اگر ناحیه ورود اطلاعات را به جدول تبدیل کنیم، به Freeze کردن نیازی نخواهد بود. در این حالت با انتخاب هر …

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

یکی از توابع بسیار مفید و کاربردی اکسل تابع IF می باشد. با استفاده از این تابع می توان با توجه به شرایط دلخواه، جواب مورد نظر را به دست آورد. به عنوان مثال در لیست حقوق و دستمزد افراد (شکل زیر) در ستون مالیات می توان با قرار دادن تابع IF به شرط برقرار بودن رابطه خاصی مالیات را محاسبه نمود. شکل کلی تابع IF به صورت  (مقدار اگر شرط برقرار نبود,مقدار اگر شرط برقرار بود,  شرط)if= در این مثال فرض شده است افرادی که حقوق روزانه آنها مساوی یا بیشتر از ۳۵۰۰۰۰ ریال باشد ۵% حقوق آنها به عنوان مالیات در نظر گرفته شود. پس فرمول مورد نظر به صورت زیر نوشته می شود.    (IF(M>=350000,0.05*M2,0=  در این فرمول M2 آدرس سلول مبلغ حقوق روزانه بوده و علامت ۳۵۰۰۰۰<= شرط تابع برای محاسبه مالیات می باشد. در صورتیکه  حقوق روزانه بزرگتر یا مساوی ۳۵۰۰۰۰ ریال باشد شرط اول یعنی: ۰٫۰۵*M2 اجرا می شود و در صورت کوچکتر بودن مبلغ حقوق روزانه از ۳۵۰۰۰۰ ریال شرط دوم تابع یعنی مقدار صفر در ستون مالیات نمایش داده می شود. همانطور که در شکل مشاهده می شود مبلغ مالیات در ردیفهای ۴،۵،۸ و ۱۰ صفر منظور شده است. علامت , و یا ; (بستگی به تنظیمات سیستم عامل کاربر دارد) برای جدا کردن شرطها مورد استفاده قرار میگیرد.  تابع IF به شکل تو در تو نیز استفاده زیادی دارد و برای چک نمودن بیش از دو شرط به کار میرود. در جدول مثال بالا فرض می کنیم برای افرادی که حقوق روزانه آنها کمتر از ۳۰۰۰۰۰ ریال بوده ولی کارکرد روزانه آنها  بیشتر از ۲۲ روز باشد پاداشی معادل ۲% حقوق روزانه تعلق بگیرد. برای این منظور از IF تو در تو استفاده می کنیم. در این تابع به جای شرط دوم یک تابع جدید IF قرار میدهیم    (IF(M2<300000,if(L2>22,0.02*M2,0= شرح تابع …

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

تابع sum علاوه بر جمع زدن اعداد به صورت معمول، توانایی جمع زدن سلول های دارای داده  طبق شرایط دلخواه را نیز به ما می دهد. فرض کنیم که دارای جدولی از اسامی دانشجویان به همراه نمره های آنان در یک درس باشیم میخواهیم با استفاده از تابع Sum به سئوالات زیر پاسخ دهیم. مثال ۱: اگر اسم دانشجو “رضا” و حداقل نمره ۷ باشد چند مورد وجود دارد؟ مثال ۲: اگر اسم دانشجو سعید یا حداقل نمره ۱۶ باشد چند مورد وجود دارد؟ ابتدا جدول اطلاعات را تشکیل میدهیم. در ستون P1 تا P10 اسامی افراد و در ستون Q1 تا Q10 نمرات را وارد می کنیم. اکنون به هریک از دو ستون اطلاعات نامی را اختصاص می دهیم. (به جای آدرس محدوده اسامی و نمرات، از نام آنها استفاده می کنیم.) برای این کار محدوده P1 تا P10 را انتخاب کرده و از سربرگ Formulas عبارت Create from selection را کلیک می کنیم. پنجره ای ظاهر می شود که دارای چند گزینه می باشد. در اینجا میخواهیم که عنوان بالای اسم دانشجویان به عنوان اسم محدوده در نظر گرفته شود برای این منظور گزینه Top row را تیک می زنیم و بر روی OK کلیک می کنیم. به روشی مشابه محدوده Q1 تا Q10 را اسم گذاری می کنیم. شکل زیر نحوه کار را نمایش می دهد.                                           فرمول را به صورت زیر می نویسیم.   {(( ۷=<نمرات)*(“رضا” =اسامی))Sum=} {(( ۱۶=<نمرات)+(“سعید” =اسامی))Sum=} توجه: علامت های {} در ابتدا و انتهای فرمول را وارد نکنید. پس از نوشتن فرمول کلیدهای ctrl  و  Shift را با هم نگهداشته و سپس کلید Enter را فشار دهید. که در این حالت علائم {} به صورت اتوماتیک  به فرمول اضافه می شوند . (روش ورود فرمولهای آرایه …

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

هنگام قراردادن توضیحات در سلول های اکسل (insert comment) همیشه یک مستطیل ساده ظاهر می شود و ما توضیحات مربوط به آن سلول را در این کادر می نویسیم. آیا راهی برای تغییر شکل و زمینه این مستطیل ساده وجود دارد؟ پاسخ مثبت است. با تغییر شکل این کادر میتوانیم ظاهر حرفه ای تری به اسناد و فایل های خود بدهیم و آنها را از حالت یک نواخت خارج نماییم. برای انجام این کار مراحل زیر را به ترتیب انجام می دهیم. (اکسل ۲۰۱۰و۲۰۱۳) پس از باز کردن اکسل بر روی File در گوشه بالا و سمت چپ صفحه کلیک می کنیم و از پنجره ظاهر شده آخرین گزینه یعنی Options را انتخاب می کنیم. پنجره Excel Option ظاهر می شود. در قسمت سمت چپ این پنجره عنوان Customize Ribbon را انتخاب می کنیم. با انتخاب این گزینه، دو کادر ظاهر می شوند. در کادر سمت چپ تعدادی از دستورات و ابزارهای اکسل لیست شده اند و در بالای این کادر عبارت Popular Commands دیده می شود. بر روی مثلث کوچک سمت راست این کادر کلیک نموده و عبارت All Commands را انتخاب می کنیم. در این حالت کلیه ابزارها و دستورات اکسل لیست می شوند. نوار لغزنده این کادر را درگ می کنیم تا به دستوری با نام Change Shape برسیم. آن را انتخاب می کنیم. اکنون باید این دستور را به کادر سمت راست با استفاده از دکمه Add اضافه نماییم. کادر سمت راست شامل عنوان های منو و ابزارهایی است که هم اکنون در بالای صفحه اکسل وجود دارند. قبل از اینکه بتوانیم هر دستور یا ابزاری را به کادر سمت راست اضافه نماییم باید در یکی از زیرگروههای منوهای فعلی (مثل Home-Insert-Design) یک گروه جدید اضافه کنیم. یا اینکه یک tab جدید ایجاد کنیم. برای انجام این کادر در قسمت پایین کادر سمت راست چند دکمه وجود دارد. ما در این قسمت میخواهیم دستور Change Shape را در گروه Home قرار دهیم. برای این کار ابتدا از کادر سمت راست …

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

لیست ها در اکسل کار ورود داده ها را ساده تر کرده و از اشتباهات احتمالی در هنگام ورود اطلاعات جلوگیری می کنند. در این روش به جای ورود اطلاعات از صفحه کلید، اطلاعات مورد نیاز از یک لیست انتخاب می شود. این لیست باید قبلاً توسط کاربر ایجاد شده باشد. یک روش ایجاد لیست با ذکر یک مثال توضیح داده میشود. فرض کنید تعدادی از استانها با میزان بارندگی سالیانه آنها را بخواهیم به یک لیست تبدیل کنیم. ابتدا نام استانها را در یک ستون، مثلاً ستون A و بدون فاصله وارد می کنیم. از سربرگ Data گزینه Data validation را انتخاب می کنیم. از پنجره ظاهر شده و از قسمت Allow از کادر پایین افتادنی گزینه List را انتخاب می کنیم. از قسمت پایین همین پنجره و در قسمت Source آدرس محدوده ای را که اسامی استانها در آن قرار گرفته را وارد می کنیم. (به جای آدرس محدوده، اگر این محدوده دارای اسم باشد می توان از اسم به جای آدرس محدوده استفاده نمود). می توان در قسمت Input Massage پیام دلخواه را تایپ کنیم تا هنگام انتخاب سلول پیام مناسب نمایش داده شود. در قسمت Error Alert نیز می توان پیامی درج کرد تا در صورت انتخاب نادرست، (مثلاً تایپ نامی که در لیست وجود ندارد) این پیام ظاهر شود مراحل اجرای کار در تصویرهای زیر توضیح داده شده اند. تذکر: در شکلهای زیر یک بار محدوده نام استان به لیست تبدیل شده و یک بار نیز محدوده میزان بارندگی به لیست تبدیل شده است (دو لیست مجزا). هر دو محدوده را نمی توان همزمان به یک لیست تبدیل کرد. تذکر: اطلاعات برای تبدیل به لیست باید در یک محدوده پیوسته (بدون سلول خالی در بین داده ها) وارد شوند. تذکر: اطلاعاتی که به لیست تبدیل می شوند، می توانند در کاربرگ جاری یا یک کاربرگ …

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

در اکسل می توان محتویات دو یا چند سلول را با یکدیگر ادغام کرده و در یک سلول نشان داد. همانطور که علامت + اعداد را با همدیگر جمع می کند علامت & (امپرساند) رشته های متنی را با یکدیگر ادغام می نماید. همچنین از تابع CONCATENATE نیز می توان برای ادغام محتویات چند سلول و قرار دادن آنها در یک سلول واحد استفاده نمود. به این نکته توجه داشته باشید که رشته های متنی حتماً باید داخل علامت ” ” قرار داده شوند. مثال۱: فرض کنید در سلول A1 کلمه “حمید” و در سلول B1 کلمه “رضا” را وارد کرده اید. با دستور A1&B1= که در سلول C1 نوشته می شود کلمه ترکیبی “حمیدرضا” حاصل می شود. مثال بالا با استفاده از دستور CONCATENATE به این صورت خواهد بود. =CONCATENATE(A1,B1) نتیجه هر دو دستور یکسان است. توجه داشته باشید که در این دستور از کاما برای جدا کردن پارامترهای داخل پرانتز استفاده می شود. مثال ۲: در سلول A1 عبارت “جمع دریافتی شما تا تاریخ” و در سلول B1 یک تاریخ دلخواه (در این مثال ۲۵/۱۱/۱۳۹۲) و در ستون C1 یک مقدار عددی (مثلاً ۲۵۰۰۰۰۰۰) را وارد کرده ایم. فرمول ترکیبی را به صورت زیر در سلول A4 وارد کرده ایم. =A1&” “&B1&” “&”مبلغ”&” “& C1&” “&”ریال می باشد” عبارت  &” “& جهت ایجاد فواصل خالی برای خوانایی بیشتر متن در فرمول استفاده شده. نتیجه فرمول نوشته شده عبارت “جمع دریافتی شما تا تاریخ ۱۳۹۲/۱۱/۲۵ مبلغ ۲۵۰۰۰۰۰۰ ریال می باشد” خواهد بود. اگر در سلول های A1 و B1 مقادیر را عوض کنید مشاهده می کنید که این مقادیر در جمله نیز عوض می شوند. به عبارت دیگر قسمتی از جمله حاصل از فرمول، تابع مقادیر درون سلول های A1 و B1 بوده و مقدار ثابتی نیست. ضمناً به تناسب نیاز رشته های ثابتی مانند “مبلغ” و “ریال می باشد” را توسط علامت & به جمله اضافه کرده ایم.                                

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

در بسیاری از اوقات هنگام پر کردن فرمهای کامپیوتری مشاهده می کنیم که با انتخاب نام استان از یک لیست، شهرستانهای همان استان برای انتخاب در دسترس قرار می گیرند. در این پست هدف ما ایجاد چنین لیستی در اکسل می باشد. برای شروع نام استانها را در یک ستون (مثلاً ستون A) و در مقابل نام استان نام شهرستانهای تابعه را می نویسیم. حال می خواهیم با انتخاب نام استان در ستون K نام شهرهای مربوط به همان استان در ستون L به صورت لیست ظاهر شود. ابتدا ستون نام استان را (ستون K ) انتخاب نموده و سپس ازسربرگ Data  گزینه Data validation را انتخاب می کنیم. از پنجره ظاهر شده و از سربرگ Setting و از قسمت Allow گزینه لیست و در قسمت Source که در پایین صفحه ظاهر می شود آدرس محدوده استانها (در این مثال سلول های A2 تا  A7 ) را انتخاب نموده و سپس بر روی دکمه OK کلیک نمایید. شکل زیر مراحل کار را نشان می دهد. شکل های  ۱ و ۲                                   کار ما با ستون K به پایان رسید. اکنون برای ستون L یعنی ستونی که باید نام شهرستانها در آن نمایش درآید باید به محدوده اسامی شهرستانها یک نام اختصاص دهیم. برای این منظور ابتدا محدوده اسامی استانها و شهرستانها را با هم انتخاب نموده و از قسمت نوار ریبون عنوان Formulas را برگزیده و سپس از قسمت Name Manager عنوان Create from selection را انتخاب می کنیم. از پنجره ظاهر شده تیک تمام گزینه ها بجز Right Column را پاک می کنیم و سپس بر روی OK کلیک می کنیم. شکل  ۳                    اکنون همانند مرحله اول باید Data validation را بر روی ستونی …

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

اگر تا به حال از بانک ها و موسسات مالی وام دریافت کرده باشید، حتماً مایلید بدانید که چگونه سود وام ها و سپرده ها را بر اساس نرخ اعلام شده محاسبه می کنند. اکسل برای محاسبات مالی دارای توابعی می باشد که در این زمینه بسیار مفید هستند. برای استفاده از این توابع ابتدا لازم است با چند پارامتر که در این توابع استفاده می شوند آشنا شویم. Loan principle اصل پول Interest Rate نرخ بهره Pay-Period دوره پرداخت Duration of the loan طول دوره پرداخت Payment پرداخت در هر دوره تذکر: ۱ اکسل نرخ بهره را به ازاء دوره پرداخت می گیرد (مثلاً پرداخت ماهانه) اما پاسخها را بر اساس سالانه برمیگرداند و برای تبدیل به ماهانه باید آن را بر ۱۲ تقسیم کنیم. تذکر ۲: مقدار پرداختهایی که در فرمول وارد می شوند بایستی به صورت عدد منفی وارد شوند. تابع PMT مثال۱: فرض کنیم از یک موسسه مالی یک وام به مبلغ پنج میلیون تومان با نرخ بهره ۱۴% و دوره بازپرداخت ۳ ساله گرفته ایم. مبلغ قسط ماهانه آن چقدر است؟ از فرمول به شرح زیر برای مشخص کردن میزان اقساط ماهانه استفاده می کنیم. =PMT(نرخ بهره ماهانه,تعداد دوره پرداخت,اصل وام دریافتی) همانطور که در شکل می بینید نرخ بهره سالانه یعنی ۱۴% را بر عدد ۱۲ تقسیم کرده ایم تا  نرخ بهره مانه را بدست آوریم.                             تذکر: قسط پرداختی هر ماه شامل دو قسمت است. قسمتی از آن مربوط به اصل پول گرفته شده و قسمتی نیز مربوط به پرداخت سود است. مثال ۲: فرض کنیم در مثال ۱ تعداد ۱۵ قسط را تا کنون پرداخت کرده باشید. چه مقدار از قسط آخر (قسط ۱۵) مربوط به اصل پول و چه مقدار آن مربوط به سود می باشد؟ برای این …

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : چهارشنبه , ۳ تیر , ۱۳۹۴
Page 5 of 7« First...34567