اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
info@excelpedia.net
ورود/ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • مسائل کاربردی
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
    • Cart

      0

توابع اکسل

  • خانه
  • بلاگ
  • توابع اکسل
  • توابع بانک اطلاعاتی توابع شرطی در اکسل

توابع بانک اطلاعاتی توابع شرطی در اکسل

  • ارسال شده توسط سامان چراغی
  • دسته بندی توابع اکسل
  • تاریخ 29 نوامبر 2019
  • نظرات ۲ دیدگاه ها
توابع بانک اطلاعاتی
نظر شما در مورد این آموزش

توابع سریع بانک اطلاعاتی

توابع بانک اطلاعاتی در اکسل توابعی هستن که برای انجام یک سری محاسبات در یک پایگاه داده بکار برده میشن. توابع بانک اطلاعاتی یک سری محاسبات پایه ای مثل جمع، شمارش، میانگین و … رو حساب میکنه با این تفاوت که میتونه شرطهای مختلفی رو بپذیره. مثلا جمع رکورد های خاصی در دیتابیس رو محاسبه کنه. لیست توابع بانک اطلاعاتی رو در جدول زیر میبینیم:

تابععملکرد تابع
Daverageمیانگین داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه
Dcountتعداد سلول های حاوی عدد رو در محدوده مطابق با شرط ها محاسبه میکنه
Dcountaتعداد سلول های پر، رو در محدوده مطابق با شرط ها محاسبه میکنه
Dgetتنها داده ای که مطابق با شرط ها هست رو نمایش میده
Dmaxماکزیمم داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه
Dminمینیمم داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه
Dproductضرب داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه
Dstdevانحراف معیار استاندارد (نمونه) داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه
Dstdevpانحراف معیار استاندارد (جامعه) داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه
Dsumجمع داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه
Dvarواریانس (نمونه) داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه
Dvarpواریانس (جامعه) داده هایی که مطابق با شرط ها هستن رو محاسبه میکنه

آرگومان های توابع بانک اطلاعاتی

همه این توابع ساختار یکسان دارند. در واقع سه آرگومان اجباری داریم که با یک مثال در ادامه تشریح میکنیم:

فرض کنید میخواهیم مجموع تعداد اشتغال در بخش صنعت رو محاسبه کنیم. برای این کار از تابع Dsum استفاده میکنیم و بصورت زیر:

حتما بخوانید:  ویژگی بسیار جالب تابع Index

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
نکته:
در مورد امکان استفاده از تاریخ شمسی، میتونیم از آفیس 2016 و نمایش تاریخ شمسی استفاده کنیم.

 

نکته:
علامت های Wildcards به گستردگی کاربرد در توابع sumifs و … نیستند. در واقع بعضی الگوها در توابع بانک اطلاعاتی شناخته نمیشن. پس هنگام استفاده از علامت های Wildcards در توابع بانک اطلاعاتی خیلی باید دقت کنیم و نتیجه رو حتما یکبار چک کنیم.

 

حتما بخوانید:  مدیریت خطا در اکسل با استفاده از توابع

ممکنه این سوال برامون پیش بیاد که فرق بین تابع sumif و Dsum چی هست؟

یکی از تفاوت های اصلی این دو تابع، سرعت عملکرد هست. تابع sumifs از دسته توابع volatile به شمار میاد و در موارد زیاد، باعث سنگین و کند شدن فایل میشه. اما تابع Dsum سرعات عملکرد بالاتری داره.

د رادامه یکی دیگه از ویژگی های مهم توابع بانک اطلاعاتی رو خواهیم دید:

یکی از ویژگی های خیلی مهم این توابع، امکان برقراری شرط های متنوع با منطق Or/And هست.

در محدوده Criteria، اگر شرط بصورت افقی قرار بگیره، منطق AND و اگر زیر هم قرار بگیره، منطق OR خواهد داشت.

در شکل 2، در جدولی که به معنی OR هست، شرط به اینصورت تفسیر میشه، رکوردهایی در نظر گرفته میشه که مربوط به بانک سپه در بخش صنعت یا بانک ملی در بخش صنعت باشند.

منطق یا OR

شکل 2- توابع بانک اطلاعاتی-منطق یا

در شکل 3، در جدولی که به معنی AND هست، شرط به اینصورت تفسیر میشه، رکوردهایی در نظر گرفته میشه که مربوط به بخش صنعت و با اشتغال بین 10 تا 15 باشند.

منطق و AND

شکل 3- توابع بانک اطلاعاتی-منطق و

پس با این منطق میتونیم شرط های متنوعی رو با مفهوم AND/Or در توابع بانک اطلاعاتی داشته باشیم و محاسبات دلخواه مثل جمع، میانگین، شمارش و … رو محاسبه کنیم.

نکته:
محدوده Criteria ، حتما باید عین سر ستون مربوطه در دیتابیس رو داشته باشه. حتی یک اسپیس کم و زیاد در نتیجه اثرگذار خواهد بود. پس جهت اطمینان، سر ستون های دلخواه رو کپی میکنیم و در محدوده شرط پیست میکنیم.

 

حتما بخوانید:  تابع MOD در اکسل یک تابع کاربردی

از بین توابع بانک اطلاعاتی، همه، مشابه تابع Dsum عمل میکنند و نکته خاصی ندارند به جز تابع Dget. اینن تابع میتونه جستجوی شرطی انجام بده، به شرط اینکه نتیجه جستجو، یک رکورد یونیک و منحصر بفرد باشه. اگر نتیجه جستجو یک مقدار منحصر بفرد نباشه تابع با خطای #NUM! مواجه میشه و اگر کلا جستجو، نتیجه ای نداشته باشه، خطای #Value! نمایش داده خواهد شد.

مثلا میخواهیم میزان وام پرداختی به کد شماره a00846 رو فراخوانی کنیم. (شکل 4)

توابع بانک اطلاعاتی – تابع Get

شکل 4- توابع بانک اطلاعاتی – تابع Get

 چون میدونیم کد طرح یک مقدار یونیک هست، و نتیجه جستجو منحصر بفرد خواهد بود، پس تابع نتیجه داره و مقدار مبلغ پرداختی به طرح a00846 رو یعنی 313 رو نشون میده.

اما اگر مورد جستجو، مثلا بانک سپه باشه، چون چندین بانک سپه وجود داره و یکی نیست، نتیجه تابع #NUM! خواهد بود. مطابق شکل 5.

توابع بانک اطلاعاتی-تابع Dget و خطای NUM

شکل 5- توابع بانک اطلاعاتی-تابع Dget و خطای NUM

دانلود فایل نمونه این آموزش

حالا که همه نکات مربوط به این توابع رو شرح دادیم، کافیه، فایل نمونه رو دانلود کنید و چند تمرین حل کنید. نمونه هایی از توابع Dcount, Dcounta  و …

با عضویت در سایت به صورت مستقیم دانلود کنید

  • ارسال به ایمیل

Des

دریافت فایل توابع دیتابیسبرای دانلود فایل کلیک کنید

برچسب:تابع Daverage, تابع Dcount, تابع Dget, تابع Dmax, تابع Dmin, تابع Dproduct, تابع Dstdev, تابع Dstdevp, تابع Dsum, تابع Dvar, تابع Dvarp

  • اشتراک گذاری:
سامان چراغی
    دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع هستم. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم. پروژه های نرم افزاری بسیاری تحت برنامه اکسل و VBA طراحی کرده ام. علاقه مند به فرمول نویسی و برنامه نویسی اکسل و تهیه داشبوردهای مدیریتی ...

    مطلب قبلی

    نمودار موزائیکی و یک مسئله پیچیده
    29 نوامبر 2019

    مطلب بعدی

    کاربردهای حرفه ای توابع اطلاعاتی
    3 دسامبر 2019

    ممکن است همچنین دوست داشته باشید

    Information Functions Conditional Formatting Cover
    کاربردهای حرفه ای توابع اطلاعاتی
    3 دسامبر, 2019
    GetPivotData-Cover
    تابع GetPivotData در اکسل و نحوه کار با آن
    26 سپتامبر, 2019
    Excel 2019 new functions-cover-min
    توابع جدید در اکسل 2019
    20 آگوست, 2019

      2 نظر

    1. افشین ابدی
      1 دسامبر 2019
      پاسخ

      سلام سوالی داشتم مهندس جان چطور میشه مثلا در یک ستون که تعدادی عدد است و تعدادی از سلولها خالی یا صفر هستند در ستون دیگر فقط سلولهای که عدد دارند اورده شوند و سلولهی دیگر نمایش داده نش.د ممنونم اگر راهنمایی کنید

      • حسنا خاکزاد
        2 دسامبر 2019
        پاسخ

        درود
        منظور از آورده شوند چیه؟
        با فرمول؟فیلتر؟کد؟
        اگر فقط بحث عدم نمایش صفر در دیتابیس هست این کد رو در فرمت سل بزنید:
        0;-0;;@@

    نظر بدهید لغو پاسخ

    نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

    -- بارگیری کد امنیتی --

    ارسال دیدگاه به معنی این است که شما با قوانین ارسال دیدگاه موافق هستید.

    زودتر از دیگران با خبر بشید

    از طریق ایمیل، مطالب آموزشی درباره اکسل و برنامه نویسی وی بی و سایر رویدادهای مهم را دریافت خواهید کرد.
    • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

    آخرین مطالب

    فرمول نویسی در Conditional Formatting
    کاربردهای حرفه ای توابع اطلاعاتی
    03دسامبر2019
    توابع بانک اطلاعاتی
    توابع بانک اطلاعاتی توابع شرطی در اکسل
    29نوامبر2019
    Marimekko Chart
    نمودار موزائیکی و یک مسئله پیچیده
    10نوامبر2019
    Google Sheets
    گوگل شیت اکسل آنلاین | قسمت دوم
    15اکتبر2019

    وجود یک منبع جامع و به روز که بدونی همیشه پشتیبانته برای یادگیری ضروریه. اکسل پدیا رو برای همین به وجود آوردیم.

    پاسخ سوالات شما

    اگر سوالات اکسلی دارید تو تالار گفتمان اکسل پدیا میتونید مطرح کنید. اساتید بسیاری هستند که به شما کمک میکنند.

    کانال و تالار گفتمان اکسل پدیا

    ورود به تالار گفتگو تلگرام

    عضویت در خبرنامه
    با عضویت در خبرنامه، ایمیل‌های آموزشی درباره اکسل و برنامه نویسی وی بی را دریافت خواهید کرد.

    • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

    021-47625755

    info@excelpedia.net

    آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

    دانلود اپ اکسل پدیادانلود از کافه بازار

    تمامی حقوق برای اکسل پدیا محفوظ است.

    • اکسل از راه دور
    • شرایط و قوانین
    • درباره ما
    • تماس با ما

    logo-samandehi

    به اکسل پدیا اعتماد کنید