از نرم افزار اکسل همانند access و Sql می توان به عنوان پایگاه داده استفاده کرد. نرم افزارهای پایگاه داده دارای امکانات ساخت Query (پرسش) هستند. به این معنی که بر اساس معیارهای مشخصی می توان گزارش هایی را تهیه کرد.
فرض کنید که در یک سیستم فروش می خواهیم گزارشی از فروش کالای خاصی را طی یک ماه گذشته بدست آوریم و یا اینکه بدانیم در یک روز مشخص در یک ماه گذشته مجموع فروش این کالا چقدر بوده است.
در این موارد استفاده از فرمول های شرطی SUMIF و COUNTIF به دلیل اینکه دارای یک شرط هستند نمی توانیم استفاده کنیم.
پرس جوها (Query) گاهی دارای چندین شرط OR و AND هستند و اگر بخواهیم از توابع شرطی اکسل استفاده کنیم توابعی بسیار پیچیده را باید بنویسیم.
اما با استفاده از توابع دیتابیس اکسل، حل این مسائل بسیار ساده خواهند شد.
فرض کنید دارای جدولی از اطلاعات به شرح ذیل هستیم
می خواهیم از جدول اطلاعات داده شده گزارشهایی با شرایطی خاص تهیه کنیم.
همانطور که در شکل می بینید تعدادی از ردیف های بالای جدول خالی در نظر گرفته شده اند . می خواهیم در این ناحیه شرط های مسئله را تایپ کنیم. و نکته مهم دیگر اینکه ستون های اطلاعات دارای سرستون هستند (رنگ پرتقالی) و همین سر ستون ها برای محل هایی که باید شرط را در آنها بنویسیم نیز تکرار شده اند (رنگ سبز)
پایگاه داده
شکل 1
همانطور که می دانید در اکسل دارای توابع SUM، COUNT، AVERAGE و … هستیم و همبن توابع را نیز داریم که با حرف D شروع می شوند. این های توابع Database اکسل هستند مانند DSUM،DCOUNT، DAVERAGE
شکل کلی تابع DSUM به صورت زیر است.
=DSUM(database,field,criteria)
مثال هایی از شروط مختلف را در زیر می بینید.
چند شرط در یک ستون (معادل تابع or)
برای اینکه در یک فیلد (ستون) بخواهیم در آن واحد چند شرط را داشته باشیم ، شروط را در سطرهای زیر هم وارد می‌کنیم و آنرا “یا” در نظر می‌گیریم.
سئوال: مجموع فروش رضا و سعید چقدر است؟
ابتدا در جدول در زیر عنوان نام فروشنده رضا و سعید را تایپ کرده و در سلول E5 فرمول را به شکل زیر تایپ می کنیم.
فرمول:
=DSUM(A6:E16,D6,B1:C2(
پایگاه داده
شکل 2
یک شرط در دو یا چند ستون (معادل تابع and)
در این حالت یک رکورد (سطر) باید دارای چند شرط باشد که آنرا “و” در نظر می‌گیریم.
سئوال: تعداد کل دوربین های فروخته شده توسط سعید چند دستگاه می باشد؟
پاسخ:
=DSUM(A6:E16,D6,B1:C2(
سوال : می‌خواهیم جمع کل مبلغ فروش را طوری حساب کنیم که حداقل یکی از سه شرط زیر را داشته باشد:
الف) یا کالا گوشی سامسونگ باشد.
ب) یا فروشنده آن سعید باشد.
ج) یا تعداد فروش آن بیشتر از 5 تا باشد.
پاسخ :
=DSUM(A6:E16,E1,B1:D4(
پایگاه داده
شکل 3
سوال : سعید و رضا چند بار بیشتر یا مساوی 4 فروش کرده‌اند.
پاسخ : یعنی تعداد فروش های سعید که بیشتر یا مساوی 4 است یا تعداد فروش های رضاه که بیشتر یا مساوی 4 است.
=DSUM(A6:E16,D6,C1:D3(
پایگاه داده
شکل 4
چند شرط در یک ستون :
ترکیبی از چند شرط در یک فیلد (ستون) ، منظور ترکیبی از And و Or بر روی یک فیلد است.
سوال : مجموع فروشهایی که مبلغ آنها کمتر از 100 است یا مبلغ آنها بین 200 تا 400 است را بدست آورد ؟
پاسخ : تمامی شرطها روی فیلد مبلغ فروش است. دقت داشته باشید که حتما سرستونها اگر لازم باشد تکراری خواهند شد. مانند زیر
=DSUM(A6:E16,E1,D1:E2(
پایگاه داده
شکل 5
جمع بندی نحوه نگارش شرطها :
وقتی که شرط هایی در یک سطر جلوی همدیگر می آیند ، حالت AND پیش می‌آید ، یعنی باید همه این شرطها که در این سطر قرار دارند در یک رکورد (سطر) صادق باشند تا تابع کارش را انجام دهد ، مثلا رضا چند گوشی سامسونگ فروخته است ، یعنی اینکه فروشنده رضا باشد AND کالا گوشی سامسونگ باشد .
وقتی شرطهایی در زیر هم یعنی در سطرهای جداگانه می آیند ، مفهوم آنها OR است یعنی اگر یکی از آن شرطها در یک رکورد (سطر) صدق کند، تابع کارش را انجام می‌دهد ، مثلا مجموع مبلغ فروش سعید و رضا را حساب کنید یعنی اینکه مبلغ فروشهایی که فروشنده آنها سعید بوده OR فروشند آنها رضا بوده را با هم جمع کنید.
مثال:
برای محاسبه جمع امتیاز چند نفر در یک مسابقه از فرمول DSUM استفاده شده است. شکل زیر را ببینید
پایگاه داده
دانلود فایل این مقاله
ثبت ديدگاه