فرمولهاي پايگاه داده‌ها در اكسل

 مقدمه :

از اكسل مي توان بعنوان يك پايگاه داده (ديتا بيس) نيز استفاده كرد دوستاني كه با پايگاه دادهايي از جمله اكسس و 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)

فرمول  :   =DSUM(A6:D14,"sales",A1:B3)

 در نگاه اول به نظر مي‌رسد شما قصد داشتيد كه مجموع فروشهاي بيشتر از 80 را محاسبه كنيد ، اما در  واقع اگر به محدوده شرط دقت كنيد متوجه مي‌شويد كه اين محدوده داراي يك شرط خالي (يعني سطر 3 است كه با رنگ نارنجي مشخص شده است) در واقع به اكسل گفته‌ايد كه مجموع فروشهايي را محاسبه كن كه مبلغ آن بيشتر از 80 يا هرچه  (چون در اين سطر شرطي قرار ندارد) را حساب كن .

بنابراين اكسل كل فروشها را محاسبه خواهد كرد.