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

اكسل ، آموزش رايگان اكسل ، كتاب آموزش اكسل ، آموزش اكسل پيشرفته ، آموزش نرم افزار اكسل ، اكسل ۲۰۱۶ ، آموزش جديد اكسل

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

۲,۱۰۰ بازديد

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

 مقدمه :

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

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

تا كنون نظري ثبت نشده است
ارسال نظر آزاد است، اما اگر قبلا در رویا بلاگ ثبت نام کرده اید می توانید ابتدا وارد شوید.