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

021-47625755
info@excelpedia.net

ورود یا ثبت نام

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

      0

توابع اکسل

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

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

  • ارسال شده توسط سامان چراغی
  • دسته بندی توابع اکسل
  • تاریخ ۸ آذر ۱۳۹۸
  • نظرات ۷ دیدگاه ها
توابع بانک اطلاعاتی
2.3 / 5 ( 3 امتیاز )

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

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

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

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

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

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

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

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 ، حتما باید عین سر ستون مربوطه در دیتابیس رو داشته باشه. حتی یک اسپیس کم و زیاد در نتیجه اثرگذار خواهد بود. پس جهت اطمینان، سر ستون های دلخواه رو کپی میکنیم و در محدوده شرط پیست میکنیم.

 

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

از بین توابع بانک اطلاعاتی، همه، مشابه تابع 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

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

مطلب قبلی

نمودار موزائیکی و یک مسئله پیچیده
۸ آذر ۱۳۹۸

مطلب بعدی

کاربردهای حرفه ای توابع اطلاعاتی
۸ آذر ۱۳۹۸

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

Text-Functions-Cover
توابع ویرایش متن انگلیسی در اکسل
۱۷ مرداد, ۱۳۹۹
Replace Function Cover-min
آموزش کار با تابع Replace
۱۲ فروردین, ۱۳۹۹
Mid Function Cover-min
تابع Mid و چند کاربرد در اکسل
۱ دی, ۱۳۹۸

    7 نظر

  1. profile avatar
    Alemi
    ۲۴ آبان ۱۳۹۹
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۵ آذر ۱۳۹۹
      پاسخ

      درود
      این مقاله رو مطالعه کنید
      https://excelpedia.net/related-list/

  2. profile avatar
    َatousa
    ۲۰ آذر ۱۳۹۸
    پاسخ

    سلام
    میخواستم بدونم داخل اکسل چطوری میتونم عددی رو که به ریال نوشتم با برداشتن صفر آخرش تبدیلش کنم به تومان ؟

    • profile avatar
      سامان چراغی
      ۲۰ آذر ۱۳۹۸
      پاسخ

      سلام
      اگر به صورت عدد نوشته شده، سلول رو بر 10 تقسیم کنید.
      اگر به صورت متن نوشته شده از تابع Left استفاده کنید.

    • profile avatar
      حسنا خاکزاد
      ۲۰ آذر ۱۳۹۸
      پاسخ

      درود
      باید داده هاتون و تقسیم بر ده کنید
      اگر از فرمت سل میخواید تنظیم کنید، میشه ظاهر عدد رو تنظیم کرد ولی اصل عدد همونه. اگر ظاهر و میخواید تنظیم کنید، این کد و بزنید:

      1
      #,"00"

  3. profile avatar
    افشین ابدی
    ۱۰ آذر ۱۳۹۸
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۱۱ آذر ۱۳۹۸
      پاسخ

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

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

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

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

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

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

آخرین مطالب

نصب Power Query
فعال کردن Power Query در ورژن های مختلف اکسل
۰۷اسفند۱۳۹۹
3 روش برای شناسایی و اصلاح هایپرلینک معیوب در اکسل
۳ روش برای شناسایی و رفع مشکل لینک های معیوب در اکسل
۰۱اسفند۱۳۹۹
مدیریت لینک
مدیریت لینک ها در اکسل
۲۷بهمن۱۳۹۹
جستجو پیشرفته با Wild Card
جستجو پیشرفته در اکسل با استفاده از Wildcard
۲۲بهمن۱۳۹۹

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

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

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

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

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

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

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

021-47625755

info@excelpedia.net

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

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

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

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