فرمولهای پایگاه دادهها در اکسل
مقدمه :
از اکسل می توان بعنوان یک پایگاه داده (دیتا بیس) نیز استفاده کرد دوستانی که با پایگاه دادهایی از جمله اکسس و SQL کار کردهاند میدانند که از جمله امکانات این نرم افزارها گرفتن پرسشها (Query) است .
معنی Query این است که در اساس ضابطه خاصی یک سری گزارش تهیه شود ، بعنوان مثال در یک سیستم فروش میخواهیم بدانیم که کلا از یک ماه پیش تا به حال چقدر از کالای A فروختهایم یا مجموع این فروش در روزهای جمعه یک ماه گذشته چقدر میشود.
خوب اگر کسی فرمول SUMIF یا COUNTIF را بلد باشد میداند که در این فرمولها فقط شما میتوانید یک شرط داشته باشید .
اما در مثالهای بالا ما دو شرط داریم پس از این فرمولها نمیتوانیم استفاده کنیم.
از طرف دیگر در Query ها ما میتوانیم چندین شرط را به صورت توابع OR یا AND بکار ببریم !
خوب در اکسل اگر شما خیلی حرفهای باشید با یک سری تکنیکهای پیچیده میتوانید شرطهای AND و OR را به سختی بکار ببرید ، اما توابع دیتابیسی اکسل این امکان را به سادگی در اختیار شما قرار میدهند.
شروع کار :
برای شروع یک سری داده به شرح زیر در نظر میگیریم ، هر فروشنده تعدادی کالا را با مبلغی به فروش رسانده است که اطلاعات آن در جدولی مانند زیر وارد شده است.
مثالهای ذکر شده بر اساس دادههای موجود در این جدول است.
|
|
A |
B |
C |
D |
|
1 |
Product |
Salesperson |
Quantity |
Sales |
|
2 |
|
|
|
|
|
3 |
|
|
|
|
|
4 |
|
|
|
|
|
5 |
|
|
|
|
|
6 |
Product |
Salesperson |
Quantity |
Sales |
|
7 |
TV |
Mina |
2 |
300 |
|
8 |
TV |
Reza |
4 |
100 |
|
9 |
VCD |
Reza |
4 |
10 |
|
10 |
TV |
Mina |
3 |
300 |
|
11 |
TV |
Mina |
5 |
10 |
|
12 |
VCD |
Reza |
6 |
100 |
|
13 |
TV |
Reza |
3 |
10 |
|
14 |
VCD |
Reza |
4 |
100 |
حال قرار است از این اطلاعات ما گزارشهایی با شرطهایی خاص تهیه کنیم ، برای اینکار ناحیه ای را برای اینکه بتوانیم شرطهایمان را تایپ کنیم در نظر میگیریم ، توصیه میشود که این ناحیه بالای محدوده دادهایمان باشد تا به سادگی در دسترس قرار گیرند.
تذکر مهم : همانطور که در شکل میبینید حتما ستونهای ما سر ستون دارند (در شکل با رنگ صورتی مشخص شده است) و عینا همین سر ستونها در محدوده ای که قرار است شرط ها وارد شوند نیز بایستی تایپ شوند. (با رنگ آبی در شکل مشخص شده است).
معرفی توابع :
در اینجا قست داریم از توابعی که با کلمه D شروع میشوند مانند DSUM و DCOUNT و DAVERAGE استفاده کنیم.
صورت کلی این توابع به صورت زیر است :
=DSUM(database,field,criteria)
=نام تابع(محدوده تابعها,فیلد,محدوده شرط)
(برای آشنایی با سایر این توابع به راهنمای اکسل مراجعه کنید.)
مثالهایی از شرط ها و کاربر آنها همراه با توابع
چند ضابطه در یک ستون (or) :
برای اینکه در یک فیلد (ستون) بخواهیم در آن واحد چند شرط را داشته باشیم ، شروط را در سطرهای زیر هم وارد میکنیم و آنرا "یا" در نظر میگیریم.
سوال : مجموع مبلغ فروش مینا و رضا چقدر است ؟
پاسخ : در محدوده شرط عینا عبارت زیر را تایپ میکنیم.
سپس در یک خانه مانند A5 فرمول زیر را تایپ میکنیم.
*** " شما در این مقاله جواب حاصل از فرمول را در A5 مشاهده میکنید و فرمول خانه A5 در زیر هر شکل تایپ شده است. "
|
|
A |
B |
C |
D |
|
1 |
Product |
Salesperson |
Quantity |
Sales |
|
2 |
|
Mina |
|
|
|
3 |
|
Reza |
|
|
|
4 |
|
|
|
|
|
5 |
930 |
|
|
|
فرمول : =DSUM(A6:D14,"sales",A1:D3)
یک شرط که در دو یا چند ستون صدق کند. (and)
در این حالت یک رکورد (سطر) باید دارای چند شرط باشد که آنرا "و" در نظر میگیریم.
سوال : رضا کلا چه تعداد تلویزیون فروخته است ؟
پاسخ :
|
|
A |
B |
C |
D |
|
1 |
Product |
Salesperson |
Quantity |
Sales |
|
2 |
TV |
Reza |
|
|
|
3 |
|
|
|
|
|
4 |
|
|
|
|
|
5 |
7 |
|
|
|
فرمول : =DSUM(A6:D14,"Quantity",A1:D2)
سوال : میخواهیم جمع کل مبلغ فروش را طوری حساب کنیم که حداقل یکی از سه شرط زیر را داشته باشد:
الف) یا کالا تلویزیون باشد.
ب) یا فروشنده آن مینا باشد.
ج) یا تعداد فروش آن بیشتر از 5 تا باشد.
پاسخ :
|
|
A |
B |
C |
D |
|
1 |
Product |
Salesperson |
Quantity |
Sales |
|
2 |
TV |
|
|
|
|
3 |
|
Mina |
|
|
|
4 |
|
|
>5 |
|
|
5 |
820 |
|
|
|
فرمول : =DSUM(A6:D14,"Sales",A1:D4)
سوال : بگویید مینا و رضا چند بار بیشتر یا مساوی 100 فروش کردهاند.
پاسخ : یعنی تعداد فروش میناهایی که بیشتر یا مساوی 100است یا رضاهایی که بیشتر یا مساوی 100 است.
|
|
A |
B |
C |
D |
|
1 |
Product |
Salesperson |
Quantity |
Sales |
|
2 |
|
Mina |
|
>=100 |
|
3 |
|
Reza |
|
>=100 |
|
4 |
|
|
|
|
|
5 |
5 |
|
|
|
فرمول : =DCOUNT(A6:D14,"Sales",A1:D3)
چند شرط در یک ستون :
ترکیبی از چند شرط در یک فیلد (ستون) ، منظور ترکیبی از And و Or بر روی یک فیلد است.
سوال : مجموع فروشهایی که مبلغ آنها کمتر از 30 است یا مبلغ آنها بین 80 تا 120 است را بدست آورد ؟
پاسخ : تمامی شرطها روی فیلد مبلغ فروش است. دقت داشته باشید که حتما سرستونها به اگر لازم باشد تکراری خواهند شد. مانند زیر
|
|
A |
B |
C |
D |
|
1 |
Sales |
Sales |
|
|
|
2 |
>80 |
<120 |
|
|
|
3 |
<30 |
|
|
|
|
4 |
|
|
|
|
|
5 |
330 |
|
|
|
فرمول : =DSUM(A6:D14,"sales",A1:B3)
جمع بندی نحوه نگارش شرطها :
وقتی که شرط هایی در یک سطر جلوی همدیگر می آیند ، حالت AND پیش میآید ، یعنی باید همه این شرطها که در این سطر قرار دارند در یک رکورد (سطر) صادق باشند تا تابع کارش را انجام دهد ، مثلا رضا چند تلویزیون فروخته است ، یعنی اینکه فروشنده رضا باشد AND کالا تلویزیون باشد .
وقتی شرطهایی در زیر هم یعنی در سطرهای جداگانه می آیند ، مفهوم آنها OR است یعنی اگر یکی از آن شرطها در یک رکورد (سطر) صدق کند، تابع کارش را انجام میدهد ، مثلا مجموع مبلغ فروش مینا و رضا را حساب کنید یعنی اینکه مبلغ فروشهایی که فروشنده آنها مینا بوده یا فروشند آنها رضا بوده را با هم جمع کنید.
نکته : شما میتوانید به جای نام فیلد در تابع از شماره ستون استفاده کنید مثلا با توجه به جدول ارائه شده در این مقاله داریم :
=DSUM(A6:D14,"sales",A1:B2)
=DSUM(A6:D14,4,A1:B2)
I خطر اخراج از شرکت :
اگر اشتباها محدوده شرط شما مانند مثال زیر باشد ، احتمالا بعد از محاسبه از شرکت اخراج میشوید ،
|
|
A |
B |
C |
D |
|
1 |
Sales |
|
|
|
|
2 |
>80 |
|
|
|
|
3 |
|
|
|
|
|
4 |
|
|
|
|
|
5 |
930 |
|
|
|
فرمول : =DSUM(A6:D14,"sales",A1:B3)
در نگاه اول به نظر میرسد شما قصد داشتید که مجموع فروشهای بیشتر از 80 را محاسبه کنید ، اما در واقع اگر به محدوده شرط دقت کنید متوجه میشوید که این محدوده دارای یک شرط خالی (یعنی سطر 3 است که با رنگ نارنجی مشخص شده است) در واقع به اکسل گفتهاید که مجموع فروشهایی را محاسبه کن که مبلغ آن بیشتر از 80 یا هرچه (چون در این سطر شرطی قرار ندارد) را حساب کن .
بنابراین اکسل کل فروشها را محاسبه خواهد کرد.
کلمات کلید مقاله:
database and list management functions – dsum – dcount – excel database formula
examples of criteria in excel formula – excel query – query in exce – query with excel
توابع اکسل – جمع با چند شرط در اکسل – گزارش گیری در اکسل – خلاصه داده ها در اکسل -
