توابع بانک اطلاعاتی توابع شرطی در اکسل
توابع سریع بانک اطلاعاتی
توابع بانک اطلاعاتی در اکسل توابعی هستن که برای انجام یک سری محاسبات در یک پایگاه داده بکار برده میشن. توابع بانک اطلاعاتی یک سری محاسبات پایه ای مثل جمع، شمارش، میانگین و … رو حساب میکنه با این تفاوت که میتونه شرطهای مختلفی رو بپذیره. مثلا جمع رکورد های خاصی در دیتابیس رو محاسبه کنه. لیست توابع بانک اطلاعاتی رو در جدول زیر میبینیم:
تابع | عملکرد تابع |
Daverage | میانگین داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه |
Dcount | تعداد سلول های حاوی عدد رو در محدوده مطابق با شرط ها محاسبه میکنه |
Dcounta | تعداد سلول های پر، رو در محدوده مطابق با شرط ها محاسبه میکنه |
Dget | تنها داده ای که مطابق با شرط ها هست رو نمایش میده |
Dmax | ماکزیمم داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه |
Dmin | مینیمم داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه |
Dproduct | ضرب داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه |
Dstdev | انحراف معیار استاندارد (نمونه) داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه |
Dstdevp | انحراف معیار استاندارد (جامعه) داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه |
Dsum | جمع داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه |
Dvar | واریانس (نمونه) داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه |
Dvarp | واریانس (جامعه) داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه |
آرگومان های توابع بانک اطلاعاتی
همه این توابع ساختار یکسان دارند. در واقع سه آرگومان اجباری داریم که با یک مثال در ادامه تشریح میکنیم:
فرض کنید میخواهیم مجموع تعداد اشتغال در بخش صنعت رو محاسبه کنیم. برای این کار از تابع Dsum استفاده میکنیم و بصورت زیر:
Database: آرگومان اول که محدوده بانک اطلاعاتی رو تعیین میکنه. این محدوده میتونه Table باشه. در این مثال، Table1 به عنوان آرگومان اول تابع تعیین میشه.
Field: آرگومان فیلد، ستونی رو که قراره محاسبات روی اون انجام بشه رو تعیین میکنه. در اینجا چون میخوایم مجموع اشتغال رو محاسبه کنیم، باید فیلد تعداد اشتغال رو مشخص کنیم. نحوه مشخص کردن فیلد تعداد اشتغال به سه صورت امکان پذیر هست:
- اسم فیلد یعنی: “تعداد اشتغال”
- شماره فیلد یعنی: عدد 4 (ستون تعداد اشتغال چهارمین ستون از Table1 هست)
- آدرس سلولی که داخل اون نوشته شده تعداد اشتغال، یعنی: J1 (با فرض اینکه در سلول J1 نوشته شده تعداد اشتغال)
Criteria: محدوده شرط های مورد نظر در این آرگومان مشخص میشه. در این مثال محدوده J3:J4 محدوده شرط مورد نظر هست. محدوده شرط حتما با اسم سرستون ها مشخص میشه.
شکل 1- توابع بانک اطلاعاتی-ساختار تابع
همین فرمول رو به شکل های زیر هم میتونیم بنویسیم:
=DSUM (Table1[#All] , 4 , J3:J4)
=DSUM (Table1[#All] , “تعداد اشتغال” , J3:J4)
شرط های قابل استفاده
شرط هایی که در این توابع بکار برده میشن، در جدول زیر آمده:
مفهوم | شرط |
سلول هایی که نوشته شده “قرمز” رو در نظر میگیره | قرمز |
سلول هایی که با “قر” شروع میشن رو در نظر میگیره | قر* |
سلول های مساوی با 10 رو در نظر میگیره | 10 |
سلول های بزرگتر از 10 رو در نظر میگیره | >10 |
سلول های پر رو در نظر میگیره | <> |
سلول هایی که با 100 مخالف هستن رو در نظر میگیره | <>100 |
تاریخ های بعد از 19 دسامبر 2017 رو در نظر میگیره | >12/19/2017 |
علامت های Wildcards به گستردگی کاربرد در توابع sumifs و … نیستند. در واقع بعضی الگوها در توابع بانک اطلاعاتی شناخته نمیشن. پس هنگام استفاده از علامت های Wildcards در توابع بانک اطلاعاتی خیلی باید دقت کنیم و نتیجه رو حتما یکبار چک کنیم.
ممکنه این سوال برامون پیش بیاد که فرق بین تابع sumif و Dsum چی هست؟
یکی از تفاوت های اصلی این دو تابع، سرعت عملکرد هست. تابع sumifs از دسته توابع volatile به شمار میاد و در موارد زیاد، باعث سنگین و کند شدن فایل میشه. اما تابع Dsum سرعات عملکرد بالاتری داره.
د رادامه یکی دیگه از ویژگی های مهم توابع بانک اطلاعاتی رو خواهیم دید:
یکی از ویژگی های خیلی مهم این توابع، امکان برقراری شرط های متنوع با منطق Or/And هست.
در محدوده Criteria، اگر شرط بصورت افقی قرار بگیره، منطق AND و اگر زیر هم قرار بگیره، منطق OR خواهد داشت.
در شکل 2، در جدولی که به معنی OR هست، شرط به اینصورت تفسیر میشه، رکوردهایی در نظر گرفته میشه که مربوط به بانک سپه در بخش صنعت یا بانک ملی در بخش صنعت باشند.
شکل 2- توابع بانک اطلاعاتی-منطق یا
در شکل 3، در جدولی که به معنی AND هست، شرط به اینصورت تفسیر میشه، رکوردهایی در نظر گرفته میشه که مربوط به بخش صنعت و با اشتغال بین 10 تا 15 باشند.
شکل 3- توابع بانک اطلاعاتی-منطق و
پس با این منطق میتونیم شرط های متنوعی رو با مفهوم AND/Or در توابع بانک اطلاعاتی داشته باشیم و محاسبات دلخواه مثل جمع، میانگین، شمارش و … رو محاسبه کنیم.
محدوده Criteria ، حتما باید عین سر ستون مربوطه در دیتابیس رو داشته باشه. حتی یک اسپیس کم و زیاد در نتیجه اثرگذار خواهد بود. پس جهت اطمینان، سر ستون های دلخواه رو کپی میکنیم و در محدوده شرط پیست میکنیم.
از بین توابع بانک اطلاعاتی، همه، مشابه تابع Dsum عمل میکنند و نکته خاصی ندارند به جز تابع Dget. اینن تابع میتونه جستجوی شرطی انجام بده، به شرط اینکه نتیجه جستجو، یک رکورد یونیک و منحصر بفرد باشه. اگر نتیجه جستجو یک مقدار منحصر بفرد نباشه تابع با خطای #NUM! مواجه میشه و اگر کلا جستجو، نتیجه ای نداشته باشه، خطای #Value! نمایش داده خواهد شد.
مثلا میخواهیم میزان وام پرداختی به کد شماره a00846 رو فراخوانی کنیم. (شکل 4)
شکل 4- توابع بانک اطلاعاتی – تابع Get
چون میدونیم کد طرح یک مقدار یونیک هست، و نتیجه جستجو منحصر بفرد خواهد بود، پس تابع نتیجه داره و مقدار مبلغ پرداختی به طرح a00846 رو یعنی 313 رو نشون میده.
اما اگر مورد جستجو، مثلا بانک سپه باشه، چون چندین بانک سپه وجود داره و یکی نیست، نتیجه تابع #NUM! خواهد بود. مطابق شکل 5.
شکل 5- توابع بانک اطلاعاتی-تابع Dget و خطای NUM
دانلود فایل نمونه این آموزش
حالا که همه نکات مربوط به این توابع رو شرح دادیم، کافیه، فایل نمونه رو دانلود کنید و چند تمرین حل کنید. نمونه هایی از توابع Dcount, Dcounta و …
با عضویت در سایت به صورت مستقیم دانلود کنید
-
ارسال به ایمیل
Des
2 نظر
سلام سوالی داشتم مهندس جان چطور میشه مثلا در یک ستون که تعدادی عدد است و تعدادی از سلولها خالی یا صفر هستند در ستون دیگر فقط سلولهای که عدد دارند اورده شوند و سلولهی دیگر نمایش داده نش.د ممنونم اگر راهنمایی کنید
درود
منظور از آورده شوند چیه؟
با فرمول؟فیلتر؟کد؟
اگر فقط بحث عدم نمایش صفر در دیتابیس هست این کد رو در فرمت سل بزنید:
0;-0;;@@