دانلود مقاله نرم افزار ریاضی و آمار Excel( اکسل)

فایلهای اکسل خود را در گوگل ایجاد کنید
معرفی Google Docs :
یکی از خدمات شرکت گوگل Google Docs است که توسط آن می توان فایلها Documents(word) ، SpreadSheets (Excel) ، Presentation(PowerPoint) را ایجاد کرد.
از جمله امکانات دیگر این تکنولوژی رایگان شرکت گوگل می توان به موارد :
ایجاد فایلها :
به سادگی می توان کارهای اولیه مانند بالت، مرتب سازی، ساخت جدول، فرمول نویسی و عوض کردن فونت را انجام داد البته به صورت مجانی .
• آپلود فایها:
Google Docs انواع فایهای متداول و معمول مانند :
Doc , XLS , RTF , CSV , PPT , ODT , ODS و ...
را قبول می کند ، می توانید این فایلها را آپلود کنید.
• سادگی کار: مانند سایر نرم افزارها با Toolbar ها کار می کنید .
• Share کردن فایلها و کار با آنها:
• می توانید به صورت همزمان روی فایلهایتان با سایر همکارانتان کار کنید، آنها را دعوت کنید تا فایلتان را مشاهده کنند.

• می توانید تنظیم کنید که چه کسانی اجازه دیدن داردند و چه کسانی اجازه ویرایش
• می توانید همزمان روی فایهایتان کار کنید، و در ضمن نسخه های قدیمی فایل نیز موجود است و از Chat آنلاین گوگل نیز استفاده کنید.
روی لینک زیر برای دیدن نمونه فایلی که به اشتراک گذاشته شده است و برای عموم قابل دیدن است کلیک کنید
Published File (اگر من که بوجود آورنده این فایل هستم به شما اجازه بدهم می توانید این فایل را ویرایش کنید)
نمونه فایل آموزش به صورت اکسل (این فایل بر روی سرور گوگل است و یک فایل داینامیک است یعنی اگر من فایل ویرایش کنم شما با ویرایش جدید فایل را دانلود می کنید بدون اینکه مجبور باشم لینک را آپدیت کنم)
دسترسی و امینت:
فایلهای شما بر روی سرور گوگل هستند و از هر نقطه دنیا به صورت Web base قابل دسترس و هر زمان از طریق اینترنت و بدون نیاز به هیچ نرم افزاری قابل استفاده می باشند.
فایها را به سادگی کپی و Export می کنید.
به سادگی آنها را بر روی وب قرار می دهید
شروع کنید:
• اگر در Google شما Account ندارید ، ایجاد کنید .

(منظور همان ایمیل است J)
• Login کنید .

(منظور این است که وارد ایمیل خود شوید)
• روی لینک Documents کلیک کنید.

(این لینک در بالای صفحه ایمیل شما نشان داده شده است.)
• خوب ادامه دهید ، با کمی دقت و حوصله خودتان متوجه خواهید شد که تا چه اندازه کار اینجا ساده است .

به عکس زیر هم دقت کنید.)
3 ترفند بسیار کاربردی در نرم افزار Excel
مطمئنأ شما نیز پس از نصب بسته نرم افزاری آفیس ، با نرم افزار Microsoft Office Excel برخورد داشته اید.

اکسل نرم افزاری قدرتمند برای محاسبه ، مدیریت و تحلیل داده ها می باشد.

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

اکنون قصد داریم به معرفی 3 ترفند بسیار کاربردی در اکسل بپردازیم.

این ترفندها عبارتند از کشیدن چارت در چند ثانیه ، کپی کردن Chart Formats و لینک کردن Autoshape و Textbox با اطلاعات داخل سلول.

یقینأ استفاده از این ترفند میتواند کار با اکسل را برای شما آسان تر و بهینه تر نماید.

کشیدن چارت در اکسل در چند ثانیه
در واقع در دو مرحله بسیار ساده شما میتوانید به سرعت یک چارت در اکسل بکشید:
1- یک سلول را در میان جدولی که میخواهید اطلاعات آن به شکل چارت رسم شود انتخاب کنید.

2- کلید F11 را فشار دهید.

چارت شما آماده است !

اکسل چارت شما را در یک Chart Sheet جدید ایجاد میکند.

شما میتوانید سایر تنظیمات را طبق روش معمول انجام دهید.

کپی کردن Chart Formats در اکسل
همانگونه که میدانید تنظیمات مربوط به فرمت چارتها در اکسل بسیار وقت گیر و گاه کسل کننده است.

آیا تا به حال چارتی را در اکسل ایجاد کرده اید که به نظرتان فرمت مناسب و ایده آلی داشته باشد و دوست داشته باشید سایر چارتهایی که ایجاد میکنید یا قبلا کشیده اید همین فرمت را داشته باشند؟

در این ترفند روش بسیار ساده ای را برای کپی کردن Chart Formats حتی بین فایلهای مختلف معرفی خواهیم کرد.

1- چارتی که فرمت دلخواهتان را دارد انتخاب (select) کنید.

2- با فشردن دو کلید ctrl+C چارت را کپی کنید.

3- چارت مقصد که میخواهید فرمت آن را تغییر دهید را انتخاب (select) کنید.

4- از منوی edit بالای صفحه گزینه Paste Special را انتخاب کنید.

5- در پنجره ظاهر شده گزینه Formats را انتخاب کرده و Ok کنید.

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

لینک کردن Autoshape و Textbox با اطلاعات داخل سلول در اکسل
آیا تا به حال به مرتبط کردن یک text box یا یک AutoShape به یک سلول نیاز پیدا کرده اید؟

با این ترفند شما می توانید اطلاعات یک سلول را به یک text box یا یک AutoShape بصورت لینک منتقل کنید.

1- اولا یک text box یا یک AutoShape رسم کنید.

2- حالا text box یا AutoShape ایجاد شده را انتخاب (select) کنید.

3- روی formula bar (نوار بالای صفحه که فرمولها در آن نمایش داده میشوند) کلیک کنید و علامت = را تایپ کنید.

4- حال بر روی سلول مورد نظرتان کلیک کنید و کلید enter را فشار دهید.

همانطور که مشاهده می کنید اطلاعات سلول مور نظرتان به text box یا AutoShape بصورت لینک منتقل شده است.

حالا میتوانید از روشهای معمول format دلخواهتان را به text box یا AutoShape بدهید.
مطمئنأ شما نیز پس از نصب بسته نرم افزاری آفیس ، با نرم افزار Microsoft Office Excel برخورد داشته اید.

این ترفندها عبارتند از "کشیدن چارت در چند ثانیه" ، "کپی کردن Chart Formats" و "لینک کردن Autoshape و Textbox با اطلاعات داخل سلول".

کشیدن چارت در اکسل در چند ثانیه در واقع در دو مرحله بسیار ساده شما میتوانید به سرعت یک چارت در اکسل بکشید: 1- یک سلول را در میان جدولی که میخواهید اطلاعات آن به شکل چارت رسم شود انتخاب کنید.

2- کلید F11 را فشار دهید.

چارت شما آماده است !

کپی کردن Chart Formats در اکسل همانگونه که میدانید تنظیمات مربوط به فرمت چارتها در اکسل بسیار وقت گیر و گاه کسل کننده است.

1- چارتی که فرمت دلخواهتان را دارد انتخاب (select) کنید.

2- با فشردن دو کلید ctrl+C چارت را کپی کنید.

3- چارت مقصد که میخواهید فرمت آن را تغییر دهید را انتخاب (select) کنید.

4- از منوی edit بالای صفحه گزینه Paste Special را انتخاب کنید.

5- در پنجره ظاهر شده گزینه Formats را انتخاب کرده و Ok کنید.

لینک کردن Autoshape و Textbox با اطلاعات داخل سلول در اکسل آیا تا به حال به مرتبط کردن یک text box یا یک AutoShape به یک سلول نیاز پیدا کرده اید؟

1- اولا یک text box یا یک AutoShape رسم کنید.

2- حالا text box یا AutoShape ایجاد شده را انتخاب (select) کنید.

3- روی formula bar (نوار بالای صفحه که فرمولها در آن نمایش داده میشوند) کلیک کنید و علامت = را تایپ کنید.

4- حال بر روی سلول مورد نظرتان کلیک کنید و کلید enter را فشار دهید.

حالا میتوانید از روشهای معمول format دلخواهتان را به text box یا AutoShape بدهید.

باگ اکسل 2007 و hotfix آن در واقع این اشتباه در نحوه نمایش این محاسبه است نه خود محاسبه، مثلا اگر شما محاسبه زیر را وارد نمایید : 77.1*850+2 که اکسل عدد 65537 را نشان می دهد.

این باگ از آنجا نشات می گیرد که اکسل کلیه محاسباتش را به صورت باینری انجام می دهد مثلا عدد 77.1 برای اکسل یعنی : 0100 0000 0101 0011 0100 0110 0110 0110 0110 0110 0110 0110 0110 0110 0110 0110 و در واقع بعد از اینکه محاسبه ای به صورت باینری انجام شد، اکسل آنرا به صورت یک رشته ی متنی نشان می دهد ، و باگ فوق در نحوه نمایش است .

به 0101 هایی که در بالا پشت سر هم تکرار شده است دقت کردید، این موضوع مانند وقتی است که شما 1/3 را محاسبه می کنید، و عدد .3333333 بدست می آید و حال تصور کنید که 1/3*3 را محاسبه می کنید، یعنی .9999999 می شود ، نه عدد 1 !!

دلیل این موضوع خارج از بحث این مقاله است و شما می توانید برای اطلاع بیشتر به لینکهای زیر مراجعه کنید.

IEEE 754-1985 Floating-point arithmetic may give inaccurate results in Excel مایکروسافت در لینک زیر HOTFIX این باگ را قرار داده است که آنرا دانلود و نصب می کنیدو با نصب آن این مشکل نشان دادن بر طرف می شود.

Description of the Excel 2007 hotfix package: October 9, 2007 نتیجه: مایکروسافت فقط ظاهر اکسل ۲۰۰۷ را تغییر نداده بلکه مجدد آنرا تولید کرده است و ساختار نرم افزار تغییر کرده و بهبود پیدا شده است.

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

/ (فرشید میدانی) این فرمول در واقع یک فرمول تو درتو می باشد که برای محاسبه مالیات حقوق و دستمزد یک شخص در ماه مورد استفاده قرار می گیرد .لازم میدانم که بگویم اولا این فرمول نیاز مختصری به دانستن حسابداری دارد وثانیا اینکه اعداد مندرج در فرمول براساس جدول مالیات حقوق ودستمزد می باشد که همه ساله بر اساس یک فرمول خاص محاسبه شده واز طریق وزارت اقتصاد و دارائی به اطلاع تمامی شرکتها می رسد که شرکتها نیز بایستی مالیات حقوق را بر این اساس محاسبه و از حقوق کسر نمایند .ناگفته نماند که اعدادمورد محاسبه درفرمول در سال 1385 مورد استفاده قرارمی گرفته است.

بنابر این به محض منتشر شدن جدول مالیاتی سال 1386 فرمول را به روز خواهم کرد و در اختیار دوستان قرار می دهم تا بتوانید مالیات حقوقتان را به راحتی محاسبه نمایید.(به غیر از شر کتهای دولتی ) چگونگی محاسبه فرمول : در محاسبه مالیات حقوق .

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

مثال 1 : کل حقوق در یافتی شخصی در پایان ماه معادل 25,500,000 ریال می باشد مالیات این شخص به ترتیب زیر خواهد بود .

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

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

پس طبق جدول مالیاتی نرخ ها از 10 و 20و 25و 30 و35 درصد تشکیل شده است .

به مبالغی که در فاصله درصد های مذکور قرار می گیرد طبقه مالیاتی می گوییم .یعنی اینکه اگر حقوق شما را از معافیتی که قبلا ذکر شد کسر کنیم عددی باقی خواهد ماند که این عدد تا مبلغ 5,660,000 ریال با نرخ 10 درصد محاسبه میکنیم بعد اگر باز هم مبلغی باقی ماند آن مبلغ تا میزان 10,493,333 ریال با نرخ 20 درصد و باز هم اگر عددی باقی ماند تا میزان 22,993,333 ریال با نرخ 25 درصد محاسبه میکنیم که اگر باز هم عددی باقی ماند تا میزان 85,493,333 ریال با نرخ 30 درصد محاسبه می کنیم در نهایت اگر باز هم عددی باقی ماند تا میزان 100,000,000 ریال با نرخ 35 درصد محاسبه می کنیم .

مثال 2 : فرض کنید حقوق در یافتی شخصی در پایان ماه معادل 5,660,000 ریال می باشد مالیات این شخص به ترتیب زیر محاسبه می شود .

برای راحتتر شدن موضوع اینگونه میگوییم که طبقه اول مالیات تا 5,660,000 ریال می باشد پس مالیات این طبقه اینگونه خواهد بود که 3500000= 2160000 -5660000 ریال که ده درصد آن مساوی 350000ریال میباشد .

اگر5660000 ریال را در سلول a1 بنویسیم فرمول اکسل مساوی خواهد بود با =if(and(a1>2160000;a1 در حقیقت این بخش از فرمول برای محاسبه طبقه اول جدول می باشد .

نرم افزار ریاضی و آمار ( اکسل) تاریخ و زمان در اکسل آموزش این مطلب را من در کلاسها به این صورت شروع می کنم ، در خانه ای از اکسل یک تاریخ (مثلا 8/5) بنویسد و سپس در همان خانه یک عدد تایپ کنید ، خواهید دید که به جای عدد تاریخ نوشته می شود .

خوب حالا می توانید همین کار را برای یک زمان نیز انجام دهید (مثلا 1:20) و بعد بروید و در همین خانه عدد تایپ کنید مثلا 5 یا 10 و یا عدد اعشاری 1.5 خواهید دید که این اعداد به زمان تبدیل می شوند.

سوال) آیا می توانید پیش بینی کنید که اگر در خانه ای عدد 100.5 تایپ شود نتیجه زمان یا تاریخ آن چند می شود.

این تغییرات از چه الگویی پیروی می کنند.

یادآوری Format Cell وقتی که در یک خانه ای مقداری تایپ می شود ، اکسل این مقدار را بررسی می کند و بنابر این مقدار format این خانه را تعیین می کند مثلا اگر درخانه ای شما مقادیر زمان و تاریخ را تایپ کنید اکسل فرمت آن خانه را به یکی از انواع Date/Time تغییر می دهد ( می دانیم که خانه ها در حالت عادی فرمت General دارند ) و از این به بعد هر مقداری که در این خانه تایپ شود نیز به همین فرمت تبدیل خواهد شد بنابراین اگر در خانه ای یک تاریخ مثلا 8/5 درج شود (format cell را نگاه کنید) از این به بعد هر عددی که در این خانه تایپ شود توسط اکسل به تاریخ متناظرش تبدیل خواهد شد.

منطق تاریخ و زمان در اکسل برای ما انسانها تاریخ و زمان یک مفهوم است و برای کامپیوتر فقط عدد قابل فهم ، بنابراین در اکسل شما هر تاریخ یا زمان را باید به یک عدد تبدیل کنید و برای اینکه این مفهوم داشته باشد، بایستی یک مبدا زمان (لحظه ای که زمان برابر صفر است داشته باشیم) و سپس تمامی زمانها را با این صفر مقایسه کنیم ، مثلا بگوییم که X روز از صفر گذشته است.

مبدا زمان (صفر) در اکسل برابر 1/0/1900 12:00:00 AM است .

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

مثلا عدد 367 را پیش بینی کنید که خواهدشد : 1/1/1901 12:00:00 AM تمرین : بگویید که از لحظه صفر اکسل چند روز سپری شده است؟

حل : تاریخ ویندوز را صحیح تنظیم کنید و سپس در یک خانه ای از اکسل کلید Ctrl+; را بزنید (یعنی کنترل را نگه دارید و کلید ; که همان "ک" است را فشار دهید ) با این کار تاریخ امروز در این خانه درج خواهد شد.

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

تذکر: شما شاید 1904 را هم در جاهایی ببینید که این مختص به کامپیوترهای Macintosh است .

تذکر : جای ماه و سال به صورت پیش فرض اینگونه است که M/d/yyyy و برای تنظیم آن به مسیر زیر بروید:.

Control panel regional and language options customize … تا اینجا منطق تاریخ بیان شد اما برای زمان اطلاعات ما کافی نیست .

مثلا باز هم یک زمان در یک خانه بنویسید 10:25 حال در همین خانه بروید و عدد تایپ کنید خواهید دید که زمان مرتبا 0:00 نشان داده می شود ، دو حالت وجود دارد یا اکسل نمی فهمد و یا ما نمی فهمیم که اکسل چی فهمیده ، البته احتمال دیگر هم وجود دارد که قل مراد اصلا نمی فهمد.

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

می دانیم که عدد صحیح تعداد روزهای سپری شده از لحظه صفر اکسل را نشان می دهد واگر به formula bar نگاهی بیاندازید اینرا خودتان خواهید دید.

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

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

اما معیار و منطق تبدیل قسمت اعشار به ساعت چیست ؟

لحظه صفر یعنی 12 شب اول ژانویه 1900 و لحظه 1 یعنی 12 شب یکم ژانویه 1900 .

در واقع اکسل ساعت 12 شب را لحظه 0 و ساعت 12 شب روز بعد را لحظه 1 در نظر می گیرد و 24 ساعت را اینگونه محاسبه می کند با توجه به جدول زیر می توانید این مطلب را بهتر درک کنید: حال می توانید با یک تناسب ساده معین کنید که اگر یک عدد اعشاری (مثلا 0.3125) را وارد کنید و سپس فرمت خانه به به time تغییر دهید چه اتفاقی خواهد افتاد.

=0.3125*24=7.5 7:30 توضیح اینکه شما با ضرب عدد در 24 در واقع عددی که بین 0 تا 1 تعریف شده بوده را بردید و در 0 تا 24 تعریف کرده اید.

استفاده از این منطق در حل مسائل : مثال اول: فرض کنید که قرار است حقوق کارگرهای روز مزد یک کارخانه محاسبه شود قرار است که به هر کارگر ساعتی 1000 تومان دستمزد داده شود، شیتهای لازم را برای اینکار را به ترتیب زیر در نظر خواهیم گرفت : نکته 1: اکسل زمانها را از هم به سادگی کم می کند و نتیجه حاصل به طور اتوماتیک از نوع TIME خواهد بود.

حال می بایستی دستمزد هر کارگر را حساب کنیم ، فرض کنید برای آرش اینکار را به فرمول : =D2*1000 انجام دهیم ، خوب نتیجه را اگر بررسی کنید خواهید دید که یک عدد منطقی نمی شود چون آرش 8 ساعت کارکرده و باید 8000 تومان حقوق بگیرد ، اما عددی که ما داریم می شود : 333.33 (در اکسل 2007 می شود 8:00 و اگر فرمت خانه را GENERAL کنید همین عدد را خواهید دید) .

منطق آن را می دانیم در واقع اکسل آمده و عدد منناظر 8:00 را در نظر گرفته و سپس آنرا در 1000 ضرب کرده که نتیجه غیر منطقی به نظر می رسد ، به زبان دیگر اکسل عدد 8:00 را به صورت 0.333333 در نظر می گیرد.

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

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

راهنمایی : به FORMAT CELLS CUSTOME m/d/yyyy h:mm یا یکی از اینها را بعنوان فرمت این خانه زرد رنگ انتخاب کنید (که هم تاریخ و هم زمان را نشان دهد) خوب ، می بینید که اکسل دارد درست عمل می کند در واقع این زمانها را باهم جمع زده و هر 24 ساعت را یک روز در نظر گرفته بنابراین داوود 9 روز و 3 ساعت کامل کارکرده است .

اما اگر بخواهید ساعت را نشان دهد : باید درFORMAT CELLS شما گزینه [h]:mm را انتخاب کنید (به علامت براکت دقت کنید) بنابراین در مثال فوق داوود 219:00:00 ساعت کار کرده است.

چند نکته در خصوص تاریخ و زمان : شما توسط توابع =now() می توانید تاریخ و ساعت جاری را در اکسل به کار ببرید.

تذکر : این تابع در هر زمان مجدد محاسبه می شود و مقدار جاری (یعنی آنچه که الان ساعت کامپیوترتان نشان می دهد) را بر می گرداند .

تذکر :اگر کلید F9 بزنید یا فایل را مجدد باز کنید و یا هر مقداری تایپ کنید و Enter را بزنید ، همه اینها باعث مجدد محاسبه شدن (refresh) این تابع خواهند شد.

تذکر : کلید Ctrl + ; و Ctrl + Shift + ; به ترتیب تاریخ و زمان جاری سیستم را بر می گردانند و با refresh شدن عوض نمی شوند.

وقتی که شما یک زمان (مثلا 12:25:33) و یا یک تاریخ دارید (مثلا 8/5/2007) با یک سری از توابع می توانید اطلاعاتی را از داخل این داده ها بیرون بکشید که البته چون تاریخها میلادی هستند برای ما کاربرد زیادی ندارند) برای کار با تاریخ جلالی (هجری شمسی) می توانید به مقاله ایکه به همین منظور در وبلاگم قرار داده ام مراجعه کنید: برای درک format cells می توانید به مقاله ایکه به همین منظور در وبلاگم قرار داده ام مراجعه کنید.

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

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

خوب اگر کسی فرمول SUMIF یا COUNTIF را بلد باشد می‌داند که در این فرمولها فقط شما می‌توانید یک شرط داشته باشید .

اما در مثالهای بالا ما دو شرط داریم پس از این فرمولها نمی‌توانیم استفاده کنیم.

از طرف دیگر در Query ها ما می‌توانیم چندین شرط را به صورت توابع OR یا AND بکار ببریم !

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

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

مثالهای ذکر شده بر اساس دادههای موجود در این جدول است.

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

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

(با رنگ آبی در شکل مشخص شده است).

معرفی توابع : در اینجا قست داریم از توابعی که با کلمه D شروع می‌شوند مانند DSUM و DCOUNT و DAVERAGE استفاده کنیم.

صورت کلی این توابع به صورت زیر است : =DSUM(database,field,criteria) =نام تابع(محدوده تابع‌ها,فیلد,محدوده شرط) (برای آشنایی با سایر این توابع به راهنمای اکسل مراجعه کنید.) مثالهایی از شرط ها و کاربر آنها همراه با توابع چند ضابطه در یک ستون (or) : برای اینکه در یک فیلد (ستون) بخواهیم در آن واحد چند شرط را داشته باشیم ، شروط را در سطرهای زیر هم وارد می‌کنیم و آنرا "یا" در نظر می‌گیریم.

سوال : مجموع مبلغ فروش مینا و رضا چقدر است ؟

پاسخ : در محدوده شرط عینا عبارت زیر را تایپ می‌کنیم.

سپس در یک خانه مانند A5 فرمول زیر را تایپ می‌کنیم.

** " شما در این مقاله جواب حاصل از فرمول را در A5 مشاهده می‌کنید و فرمول خانه A5 در زیر هر شکل تایپ شده است.

" فرمول : =DSUM(A6:D14,"sales",A1:D3) یک شرط که در دو یا چند ستون صدق کند.

(and) در این حالت یک رکورد (سطر) باید دارای چند شرط باشد که آنرا "و" در نظر می‌گیریم.

سوال : رضا کلا چه تعداد تلویزیون فروخته است ؟

پاسخ : فرمول : =DSUM(A6:D14,"Quantity",A1:D2) سوال : می‌خواهیم جمع کل مبلغ فروش را طوری حساب کنیم که حداقل یکی از سه شرط زیر را داشته باشد: الف) یا کالا تلویزیون باشد.

ب) یا فروشنده آن مینا باشد.

ج) یا تعداد فروش آن بیشتر از 5 تا باشد.

پاسخ : فرمول : =DSUM(A6:D14,"Sales",A1:D4) سوال : بگویید مینا و رضا چند بار بیشتر یا مساوی 100 فروش کرده‌اند.

پاسخ : یعنی تعداد فروش میناهایی که بیشتر یا مساوی 100است یا رضاهایی که بیشتر یا مساوی 100 است.

فرمول : =DCOUNT(A6:D14,"Sales",A1:D3) چند شرط در یک ستون : ترکیبی از چند شرط در یک فیلد (ستون) ، منظور ترکیبی از And و Or بر روی یک فیلد است.

سوال : مجموع فروشهایی که مبلغ آنها کمتر از 30 است یا مبلغ آنها بین 80 تا 120 است را بدست آورد ؟

پاسخ : تمامی شرطها روی فیلد مبلغ فروش است.

دقت داشته باشید که حتما سرستونها به اگر لازم باشد تکراری خواهند شد.

مانند زیر فرمول : =DSUM(A6:D14,"sales",A1:B3) جمع بندی نحوه نگارش شرطها : وقتی که شرط هایی در یک سطر جلوی همدیگر می آیند ، حالت AND پیش می‌آید ، یعنی باید همه این شرطها که در این سطر قرار دارند در یک رکورد (سطر) صادق باشند تا تابع کارش را انجام دهد ، مثلا رضا چند تلویزیون فروخته است ، یعنی اینکه فروشنده رضا باشد AND کالا تلویزیون باشد .

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

نکته : شما می‌توانید به جای نام فیلد در تابع از شماره ستون استفاده کنید مثلا با توجه به جدول ارائه شده در این مقاله داریم : =DSUM(A6:D14,"sales",A1:B2) =DSUM(A6:D14,4,A1:B2) خطر اخراج از شرکت : اگر اشتباها محدوده شرط شما مانند مثال زیر باشد ، احتمالا بعد از محاسبه از شرکت اخراج می‌شوید ، فرمول : =DSUM(A6:D14,"sales",A1:B3) در نگاه اول به نظر می‌رسد شما قصد داشتید که مجموع فروشهای بیشتر از 80 را محاسبه کنید ، اما در واقع اگر به محدوده شرط دقت کنید متوجه می‌شوید که این محدوده دارای یک شرط خالی (یعنی سطر 3 است که با رنگ نارنجی مشخص شده است) در واقع به اکسل گفته‌اید که مجموع فروشهایی را محاسبه کن که مبلغ آن بیشتر از 80 یا هرچه (چون در این سطر شرطی قرار ندارد) را حساب کن .

بنابراین اکسل کل فروشها را محاسبه خواهد کرد.

مثال 3 : فرض کنید حقوق دریافتی شخصی در پایان ماه معادل 10,493,333 ریال ریال باشد مالیات حقوق این شخص به ترتیب زیر خواهد بود .

ابتدا می گوییم چون این عدد بزگتر از 5660000 ریال است پس ما دیگر نیازی به محاسبه مالیات تا 5660000 ریال نداریم چون در فرمول قبلا محاسبه شده است .

تنها کاری که می کنیم بایستی مابقی عدد بزرگتر از 5660000ریال را محاسبه کنیم ومالیات طبقه اول را به آن اضافه کنیم .

بنابر این : If(and(a1>2160000;a15660000;a1 در فرمول بالا وقتی بعد از if and می آوریم در واقع شما دامنه شرط خود را افزایش داده و به نوعی با ایجاد علامت کوچکتر و بزرگتر دامنه شرط خود را با and محدود می کنید .

می گویید اگر a1 بزرگتر از 2160000 و A1 کوچکترومساوی با 5660000 باشد آنگاه جواب شرط ما بایستی معافیت را کسر کرده سپس در درصد طبقه اول ضرب کند ادامه فرمول نیز به همین ترتیب چون پرانتز if اول بسته نشده است بنابر این می توانیم if دیگر ایجاد کرده و ادامه می دهیم .

اگر a1 بزرگتر از 5660000 و کوچکتر و مساوی با 10493333باشد آنگاه جواب شرط دوم ما بایستی منهای 5660000 سپس در درصد طبقه دوم (20 درصد ) ضرب شود ودر نهایت با حاصل مالیات طبقه اول جمع شود.

که جواب فرمول شما مسای 1316667 خواهد شد و نکته قابل توجه اینکه با دادن شرط and در حقیقت شما تمام اعداد بین هر طبقه را مد نظر قرار خواهید داد .

فرمت تابع (جواب درصورتی که شرط تابع برقرار نباشد ; جواب درصورتی که شرط تابع برقرار باشد; شرط تابع ) if نکته : ما در فرمول وقتی علامت بزرگتر از به طور ساده تر : اگر سلول عدد وارده را a1 فرض کنیم می توان گفت : اگر a1 بزرگتر باشد از 2160000 (شرط تابع ) و a1 کوچکترو مساوی با5660000 باشد آنگاه جواب شرط هر عددی شد منهای معافیت مالیاتی سپس ضربدر نرخ طبقه اول (10% ) در قسمت جواب در صورتی که شرط تابع برقرارنباشد تابع if باید دقت شود که پرانتز اول بسته نشود چون در این صورت قادر به ادامه فرمول نخواهید بود پس ادامه میدهیم اگرa1 ( عدد 350000 در حقیقت مالیات طبقه اول در حالتی که ما عدد 5660000 را وارد کرده باشیم می باشد .می دانید چرا این کار را انجام دادیم ؟

چون ما وقتی در فرمول با علامت کوچکتر وبزرگتر فاصله بین اعداد را محدود می کنیم و طبقه به طبقه پیش می رویم پس دیگر نیاز نیست تا فرمول طبقه اول را در طبقه دوم بگنجانیم از طرفی برای درست بودن جواب بایستی مالیات طبقه اول را در جواب طبقه دوم بنگنجانیم پس می توانیم بگوییم جواب طبقه دوم هر چه شد با نهایت مالیات طبقه اول جمع شود )مجددا در بخش جواب نادرست تابع if طبقه دوم با یک if دیگر شروع می کنیم تا تابع تودر تو ادامه یابد که : اگر a1 آنگاه a1 منهای 22993333 که جواب هر چه شد در نرخ طبقه چهارم (30%) ضرب می کنیم و با مالیات طبقه های قبلی جمع می کنیم یعنی (350000+966667+ 3125000) = 4441667 ودر نهایت ادامه می دهیم اگر a1 بزرگتر باشد از 85493333 و کوچکتر مساوی 100000000 آنگاه a1 بایستی منهای 85493333 شود سپس در نرخ مالیاتی طبقه آخر (35%)‌ضرب شود و در نهایت با مالیات محاسبه شده طبقه های قبلی جمع شود .

یعنی با عدد 23191667 جمع شود (اگر عدد 85493333 را در فرمول قرار دهید مالیات تا این طبقه مساوی این عدد خواهد شد ) دقت کنید که در تابع if وقتی پرانتز اول باز شد نباید تا آخر فرمول بسته شود چون فرمول شما تا همان جا که پرانتز بسته می شود خواهد بود .

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

با عرض پوزش از اینکه توضیح فرمول کمی با فرهنگ لغت حسابداری توضیح داده شد چرا که این فرمول نیازمند چنین توضیحی بود .

امید وارم مورد قبول دوستان واقع شود .

2,500,000کل حقوق در یافتی(2,160,000)میزان معافیت340,000مانده ضربدر 10 درصد34,000میزان ما لیات از مبلغ (ریال )تا مبلغ (ریال )نرخ مالیاتی (درصد )طبقه مالیاتیمیزان مالیات محاسبه شده12,160,0000%معافیت مالیاتی-2,160,0005,660,00010%اول350,0005,660,00010,493,33320%دوم966,66710,493,33322,993,33325%سوم1,316,66722,993,33385,493,33330%چهارم4,441,66785,493,333100,000,00035%پنجم23,993,333 12 شب06 صبح0.2512 ظهر0.56 بعد از ظهر0.7512 شب فردا1 ABCDE1نامساعت ورودساعت خروجکارکرددستمزد2آرش6:0014:00=C2-B23علی6:3016:009:304رضا7:0012:005:005کاوه7:3017:259:55 ABCD1نامساعت ورودساعت خروجکارکرددستمزد2آرش6:0014:008:00=D2*24*10003علی6:3016:009:3095004رضا7:0012:005:0050005کاوه7:3017:259:559916.6667 ABCDE1روز هفتههفته 1هفته 2هفته 3هفته 42SATURDAY8:0011:008:008:003SUNDAY9:0011:009:008:004MONDAY10:0011:0010:008:005TUESDAY8:008:008:008:006WEDNESDAY12:0012:0012:008:007THURSDAY8:008:008:008:00=SUM(B2:E7) ABCDE1روز هفتههفته 1هفته 2هفته 3هفته 42SATURDAY8:0011:008:008:003SUNDAY9:0011:009:008:004MONDAY10:0011:0010:008:005TUESDAY8:008:008:008:006WEDNESDAY12:0012:0012:008:007THURSDAY8:008:008:008:001/9/1900 3:00 ABC112:25:3312=HOUR(B1)225=MINUTE(B1)333=SECOND(B1)458/5/20072007.00=YEAR(B5)68=MONTH(B5)75=DAY(B5)1=WEEKDAY(B5) ABCD1ProductSalespersonQuantitySales23456ProductSalespersonQuantitySales7TVMina23008TVReza41009VCDReza41010TVMina330011TVMina51012VCDReza610013TVReza31014VCDReza4100 ABCD1ProductSalespersonQuantitySales2Mina3Reza45930 ABCD1ProductSalespersonQuantitySales2TVReza3457 ABCD1ProductSalespersonQuantitySales2TV3Mina4>55820 ABCD1ProductSalespersonQuantitySales2Mina>=1003Reza>=100455 ABCD1SalesSales2>80345330 ABCD1Sales2>80345930