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

021-47625755
info@excelpedia.net

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

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

      0

توابع اکسل

  • خانه
  • بلاگ
  • توابع اکسل
  • فرمول نویسی آرایه ای در اکسل

فرمول نویسی آرایه ای در اکسل

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

همونطور که قبلا گفته شد، فرمول نویسی حرفه ای اصولی داره که حتما باید با این اصول آشنا و به اونها مسلط باشید. بعد از تسلط به این اصول و شناخت توابع مهم و کاربردی، نوبت میرسه به فرمول نویسی آرایه ای در اکسل. این نوع فرمول نویسی منطق و اصول خاص خودشو داره. با تسلط به این مفهوم و منطق، میشه گفت شما تبدیل به یک فرمول نویسی حرفه ای در اکسل شدید و میتونید درصد بیشتری از مسائل رو حل کنید.

در این مقاله به تشریح مفهوم فرمول نویسی آرایه ای در اکسل می پردازیم.

منطق فرمول نویسی آرایه ای در اکسل به این صورت هست که یک سری محاسبات رو در درون خودش انجام میشه و نیاز به سلول های کمکی رو از بین میبره. نکته مهم این نوع فرمول نویسی این هست که بعد از نوشتن فرمول، بجای Enter، باید Ctrl+Shift+Enter زده بشه در غیر اینصورت با خطا مواجه میشه.

فرمول نویسی آرایه ای به دو بخش تقسیم میشه:

بخش اول: توابعی که ذاتا آرایه ای هستن

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

MMult: تابع ضرب ماتریس (تعداد سطر ماتریس اول باید با تعداد ستون ماتریس دوم برابر باشه)

Minverse: تابع معکوس کردن ماتریس (ماتریس حتما باید مربعی باشه)

Transpose: تابع ترانهاده کردن

Frequency: تابع محاسبه فراوانی

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

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

 

حتما بخوانید:  Skip Blank | تکنیکی کاربردی در Paste special

بخش دوم: ترکیب توابع معمولی با منطق آرایه ای

این بخش تابع خاص و ویژه ای نداره. استفاده از همون توابع معمولی اکسل مثل Sum, Index,small و … اما با منطق آرایه ای هست. تسلط به این بخش قطعا شما رو به سمت درک بهتر توابع، فرمول نویسی در اکسل و حرفه ای شدن پیش می بره.

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

همونطور که میدونید تابع Sumproduct از ورژن 2007 به بعد اضافه شده و قبل از اون همچین تابعی وجود نداشته. تابع Sumproduct اول داده ها رو نظیر به نظیر در هم ضرب میکنه و بعد با هم جمع میکنه. حالا فرض کنید تعداد فروش و مبلغ مربوط به هر کدوم رو داریم. حالا میخوایم مجموع فروش رو محاسبه کنیم. با 3 روش این کار رو انجام میدیم:

روش اول: تابع Sumproduct

محاسبه مجموع فروش با تابع Sumproduct

شکل 1- محاسبه مجموع فروش با تابع Sumproduct

روش دوم: فرمول نویسی معمولی

اگر بخوایم بدون استفاده از تابع Sumproduct مجموع فروش رو حساب کنیم، باید یک ستون کمکی داشته باشیم که اول داده ها رو در هم ضرب کنه و بعد با هم جمع بزنه.

محاسبه مجموع فروش با ستون کمکی

شکل 2- محاسبه مجموع فروش با ستون کمکی

روش سوم: فرمول نویسی آرایه ای

حالا میخوایم بدون ستون کمکی و بدون تابع استفاده از تابع Sumproduct این مجموع رو حساب کنیم.

محاسبه مجموع فروش بصورت آرایه ای

شکل 3- محاسبه مجموع فروش بصورت آرایه ای

همونطور که در شکل 3 می بینید، مجموع فروش رو با استفاده از تابع Sum و بدون ستون کمکی و بصورت آرایه ای محاسبه کردیم. نحوه عملکرد این تابع رو در تصویر زیر می بینید:

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

همونطور که میبینید، عملیات ضرب نظیر به نظیر داده ها در یک آرایه و در دل خود فرمول انجام میشه. بعد عملیات جمع روی داده های ضرب شده انجام میشه. در واقع تابع Sumproduct  منطق آرایه ای داره و طی دو مرحله محاسبات رو انجام میده و معادل فرمول (Sum(A2:A14*B2:B14 عمل میکنه.

نکته:
نشانه ظاهری فرمول های آرایه ای علامت { } هست که نباید تایپ بشه. بلکه به محض زدن کلید ترکیبی Ctrl+Shift+Enter این علامت ابتدا و انتهای فرمول اضافه میشه.

 

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

 

دانلود فایل اکسل این آموزش

برای دانلود فایل اکسل این آموزش روی لینک زیر کلیک کنید.

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

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

Des

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

 

 

برچسب:پیشرفته, تابع Frequency, تابع Minverse, تابع MMult, تابع Sum, تابع Sumproduct, تابع Transpose

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

مطلب قبلی

جدول (Table) ابزاری بسیار مهم در اکسل
۳ آذر ۱۳۹۶

مطلب بعدی

رسم نمودار درون سلول های اکسل
۳ آذر ۱۳۹۶

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

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

    9 نظر

  1. profile avatar
    عبدالله
    ۶ اسفند ۱۳۹۸
    پاسخ

    سلام خسته نباشید
    من یک لیست از نفرات و نمره ها رو دارم و میخوام رتبه بندی نفراتی که بالاترین نمره رو میگیرند همیشه بصورت خودکار انجام بشه که مشکلی خوردم که ظاهرا با آرایه کردن فرمول حل میشه. مثلا این جدول رو دارم:
    نام نمره
    علی 40
    حسن 30
    محمد 40
    محسن 52
    با فرمول Large کلیه نمرات به ترتیب مرتب میشه
    با ترکیب فرمول MATCH+INDEX هم نفری که نمره متعلق به اون هست پیدا میشه
    فقط مشکل اینجاست که نفراتی که نمره مشابه دارند، نام اولین نفری که پیدامیکنه رو برای نفر بعدی و بعدی ها هم تکرار میکنه!
    یعنی بعد از استفاده از فرمول، نتیجه اینطوری میاد:
    محسن 52
    علی 40
    علی 40
    حسن 30
    یعنی اسم محمد رو که نمره 40 داره نمیاره. اگر امکان داره یکی از اساتید فرمول رو اصلاح کنند:
    =INDEX($A$2:B13,MATCH(F3,$B$2:B15,0),1)
    باتشکر

    • profile avatar
      حسنا خاکزاد
      ۶ اسفند ۱۳۹۸
      پاسخ

      درود
      بله تکرار میکنه چون ذات تابع match همینه
      برای این کار باید یک مقدار خیلی کم، در حد 0.000001 به نمره هاتون اضافه کنید که منحصر بفرد باشه. مثلا row()*0.000001
      هم اثری روی مقدار نمره نداره هم منحصر بفرده و میتونید جستجو کنید

      • profile avatar
        عبدالله
        ۷ اسفند ۱۳۹۸
        پاسخ

        اما این نمره ها معدل نفرات هست و من در تعیین شون نقش ندارم و ممکنه واقعا دو سه نفر دقیقا نمره یکسانی بگیرند!! با آرایه یا IF چطور میشه درستش کرد؟

        • profile avatar
          حسنا خاکزاد
          ۷ اسفند ۱۳۹۸
          پاسخ

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

          یا اینکه در یک ستون کمکی رتبه بندی انجام بدید با rank و برای رتبه های یکسان با countif داده ها رو اضافه کنید به رتبه که تفکیک بشن از هم. بعد رتبه ها رو شروع کنید vlookup کردن. اینطوری رتبه ها 1 تا n شما جستجو میشن و مقادیر بصورت مرتب چیده میشن.

          =rank(A1,$A$1:$A$30)+countif($A$1:A1,A1)

  2. profile avatar
    محمد
    ۴ دی ۱۳۹۸
    پاسخ

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

    • profile avatar
      سامان چراغی
      ۱۳ دی ۱۳۹۸
      پاسخ

      سلام
      اگر منظورتون از ایجاد ماتریس:
      اگر ایجاد Table با ابعادی که انتخاب شده هست، باید از VBA استفاده بشه.
      اگر تعریف محدوده ای نامگذاری شده با ابعاد انتخابی هست، هم با VBA میشه و هم با استفاده از فرمول نویسی در Name

  3. profile avatar
    علی
    ۲۲ فروردین ۱۳۹۸
    پاسخ

    سلام
    خسته نباشید
    یک فایل اکسل دارم با 2 شیت که برای هر دو شیت ستون اول شماره درخواست،ستون دوم کد کالا و ستون سوم تعداد درخواست می باشد که ممکن است برای یک شماره درخواست یکسان چندین کد مختلف با تعداد متفاوت ثبت شده باشد
    سوال:
    اگر بخوام از شیت a ردیفی که شماره درخواست 1 با کد 100 ثبت شده مقدار درخواست را از شماره درخواست 1 با کد 100 از شیت b بردارم چیکار کنم
    این را مدنظر داشته باشید احتمال این که برای مثال درخواست 1 شامل چند کد در ردیفهای مختلف باشد هست
    ممنون

    • profile avatar
      سامان چراغی
      ۲۳ فروردین ۱۳۹۸
      پاسخ

      سلام
      اگر فقط قصد شمردن تعداد ردیف های مورد نظر در شیت B هست از تابع Countifs استفاده کنید.
      اگر قصد دارید کل ردیف های شیت B رو ببینید (یکجوری فیلتر حساب میشه) بهتره از Pivot Table استفاده کنید.

  4. profile avatar
    مصطفی
    ۱۴ آذر ۱۳۹۶
    پاسخ

    عالی بود. مرسی

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

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

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

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

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

آخرین مطالب

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

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

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

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

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

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

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

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

021-47625755

info@excelpedia.net

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

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

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

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