ثبت نام دوره آنلاین VBA به زبان ساده
سبد خرید
0

سبد خرید شما خالی است.

جستجو
Generic filters

تابع Offset و آغاز ایجاد داشبورد

تابع Offset
۳.۸/۵ - (۲۰ امتیاز)

تابع Offset و کاربردهای آن

تابع Offset از دسته توابع جستجو یا Lookup & Reference هست. این تابع کاربرد خیلی زیادی در ایجاد نمودارهای پویا، محدوده های داینامیک داره که همه این موارد در تهیه نرم افزار، گزارشگیری حرفه ای و تهیه داشبوردهای مدیریتی کاربرد خیلی زیادی دارن. از این تابع برای ایجاد یک محدوده استفاده میشه. برای اینکه خوب درک کنیم اول باید آرگومانهاش رو بشناسیم:

Reference: این آرگومان آدرس یک سلول هست. سلول مرجعی که آدرس دادن از این سلول شروع میشه.

Row: این آرگومان عددی است. این عدد به این معنی هست که میگه از سلول مرجع، این تعداد سلول بیا پایین/بالا. اگر عدد مثبت باشه، به سمت پایین و اگر منفی باشه به سمت بالا شیفت میشه.

Column: این آرگومان عددی است. این عدد به این معنی هست که میگه از سلول مرجع، این تعداد سلول برو سمت راست/چپ. اگر عدد مثبت باشه، به سمت راست و اگر منفی باشه به سمت چپ شیفت میشه.

[height]: این آرگومان عددی و اختیاری است. ارتفاع (تعداد سلول) محدوده مورد نظر رو تعیین میکنه. در صورتی که تعیین نشه، ۱ در نظر گرفته میشه.

[width]: این آرگومان عددی و اختیاری است. پهنای (تعداد سلول) محدوده مورد نظر رو تعیین میکنه.در صورتی که تعیین نشه، ۱ در نظر گرفته میشه.

در واقع این تابع به ۲ صورت مورد استفاده قرار میگیره:

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

مثال: میخوایم داده مشخصی رو از بین داده های یک محدوده فراخوانی کنیم.

=OFFSET(E3,۳,۲)

فراخوانی داده خاصی از از یک سری داده با استفاده از تابع Offset

شکل ۱- فراخوانی داده خاصی از از یک سری داده با استفاده از تابع Offset

از سلول E3 به تعداد ۳ سلول میره پایین و به تعداد ۲ سلول میره راست. تا اینجا رسیدیم به سلول G6. از اونجا محدوده ای به ارتفاع ۱ و پهنای ۱ سلول رو در نظر میگیره (آرگومان های ۴ و ۵ تابع Offset در صورتیکه مشخص نشه، ۱ در نظر گرفته میشه). چون خروجی این تابع یک سلول است، محتویات همون سلول G6 نمایش داده میشه.

  1. ارجاع به یک محدوده در اکسل. چون خروجی این تابع یک محدوده هست، به تنهایی کاربرد نداره و حتما باید با ابزار یا تابع دیگه ای ترکیب بشه. در غیر اینصورت خروجی تابع خطا خواهد بود.

مثال: میخوایم داده های موجود در ردیف دوم رو با هم جمع بزنیم.

=Offset(E3,1,o,1,4)

جمع محدوده خاصی از از یک سری داده با استفاده از تابع Offset

شکل ۲- جمع محدوده خاصی از از یک سری داده با استفاده از تابع Offset

از سلول E3 به تعداد ۱ سلول میره پایین و به تعداد ۰ سلول میره راست. تا اینجا رسیدیم به سلول E4. از اونجا محدوده ای به ارتفاع ۱ و پهنای ۴ سلول رو در نظر میگیره. یعنی E4:H4. چون خروجی این تابع یک محدوده است، به تنهایی خطا میده. پس با تابع دیگه ای مثل Sum ترکیب میکنیم. خروجی جمع محدوده تعیین شده خواهد بود.

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

 

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

این فایل تمرین بسیار خوبی از فرمول نویسی در فرمت دهی شرطی هم میتونه باشه. سعی کنید روش کار این فایل رو برای خودتون تحلیل کنید که چطور با تغییر در سلول ها، محدوده های رنگی (متناسب با مفهوم تابع Offset) تغییر میکنه. محدوده سلول A1:B3 سلولهای کمکی هستند که در فرمول نویسی فرمت دهی شرطی استفاده شدن.

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

فایلی که تو این آموزش ایجاد شده رو میتونید از لینک زیر دانلود کنید:

[esd id=”42″ link=”https://dl.dropboxusercontent.com/s/nayauppe193lhna/Offset.xlsx?dl=0″ subject=”لینک دانلود فایل آموزش Offset” btntext=”دریافت فایل” type=”mail_type” theme=”theme_d” mailer_group=”default” position=”right” icon=”fa fa-download” description=”برای دانلود فایل کلیک کنید”]

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

دیدگاه کاربران
  • aida ۲۵ آبان ۱۴۰۰ / ۱۲:۱۶ ب٫ظ

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

    • حسنا خاکزاد ۲۵ آبان ۱۴۰۰ / ۲:۳۲ ب٫ظ

      در.د بر شما
      باید الگو رو با row یا column بسازید
      این مقاله رو بخونید ایده بگیرید
      https://excelpedia.net/address-function/

      هم تابع offset هم address اینکار رو میکنن

      • aida ۲۶ آبان ۱۴۰۰ / ۹:۵۴ ق٫ظ

        سلام ممنونم از پاسخگوییتون…الان من با تابع offset اینو نوشتم OFFSET(Sheet2!AG6;0;0;1;1)= و کدملی که میخواستم رو نشون داد… اما نمیدونم باید چی بنویسم که بعدی بشه ag 14 , ag 22 ,… به همین ترتیب ۸تایی جلو بره…میشه لطفا راهنماییم کنید

        • حسنا خاکزاد ۲۶ آبان ۱۴۰۰ / ۱۱:۴۰ ق٫ظ

          درود
          اون تابع address که گفتم و بخونید با مثال هاش
          میتونید ۸ تا ۸ تا ببرید جلو

          • aida ۲۶ آبان ۱۴۰۰ / ۲:۴۳ ب٫ظ

            نشد خواهر نشد…:))) احتمالا که نه ۱۰۰% یه جا رو اشتباه میزنم ولی بازم مرسی مرسی :*

  • سارا ۲۵ دی ۱۳۹۹ / ۱۰:۴۰ ق٫ظ

    سلام میشه کدنویسی vba مربع جادویی ۳*۳ رو آموزش بدید؟

  • ابوالفضل ۴ اسفند ۱۳۹۸ / ۱۱:۰۴ ب٫ظ

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

    • حسنا خاکزاد ۷ اسفند ۱۳۹۸ / ۱۰:۳۷ ق٫ظ

      درود
      در حدی که autocomplete خود اکسل داره، میتونه اونکار و انجام بده که اونم بر اساس داده های از قبل وارد شده هست
      اما اگر میخواید قسمتی از کلمه رو بنویسید و لیستی از اون کلمات بهتون بده مقاله زیر رو ببینید و ازش ایده بگیرید

      https://excelpedia.net/searchable-list/

  • عیسی ۲۹ اردیبهشت ۱۳۹۸ / ۸:۳۹ ق٫ظ

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

    • حسنا خاکزاد ۲۹ اردیبهشت ۱۳۹۸ / ۲:۱۲ ب٫ظ

      درود بر شما
      با ابزار pivot میتونید انجام بدید
      در نهایت فرمول هم میتونه این کار و انجام بده منتها باید فرمول نویسی آرایه رو بدونید تا بتونید با توجه به ساختار داده جستجو های متنوعی رو انجام بدید

  • مقیمی ۱۵ اردیبهشت ۱۳۹۸ / ۱۰:۱۴ ق٫ظ

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

    • حسنا خاکزاد ۱۵ اردیبهشت ۱۳۹۸ / ۱۱:۲۱ ق٫ظ

      درود بر شما
      دسته ها رو هم باید توی داده ها داشته باشید یا منطق دسته بندی
      که بر اساس اون بتونید شمارش کنید
      منطق همون countif هست

  • Balal Khani ۱۵ فروردین ۱۳۹۸ / ۲:۰۸ ق٫ظ

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

  • gholamreza1169 ۱۱ شهریور ۱۳۹۷ / ۹:۲۵ ق٫ظ

    سلام و خسته ناشید
    دستتون درد نکنه بسیار جالب بود . اگه میشه کمی هم درباره vbتوضیح بدهید
    باسپاس

  • Mohammad reza Nikbakht ۳۰ مرداد ۱۳۹۷ / ۳:۴۶ ب٫ظ

    با سلام و عرض ادب

    یک نمودار تحت اکسل طراحی کردم که از یک سری داده بدست میاد که این داده ها شامل عدد، متن و سلول های blank میشه.
    وقتی نمودار رو رسم میکنم و رنج رو مشخص میکنم تمام سلول ها رو در نظر میگیره. میخوام در محدوده انتخاب ده متون و سلول های خالی رو در نظر نگیره و فقط نمودار رو برای داده های عددی( که شامل صفر ها هم بشه ولی سلول های blank نه) رسم کنه.
    سعی کردم با تابع offset این کار رو انجام بدم ولی نمیدونم چطور میشه چند محدوده رو انتخاب کنم.
    با توجه به اینکه تعداد داده هام زیاد هست پیشنهادتون برای این کار چیه؟

    با تشکر فراوان از مطالب آموزنده و کاربردی شما.

    • سامان چراغی ۳۱ مرداد ۱۳۹۷ / ۱۰:۲۹ ق٫ظ

      سلام
      کافیه یک ستون تعریف کنید که به جای سلول های خالی و متنی که نمیخواید تو نمودار نمایش داده بشه مقدار =NA() رو بذارید و اعداد هم خودشون رو بذارید و این ستون رو در رنج نمودار انتخاب کنید.

  • yzn ۲۴ آبان ۱۳۹۶ / ۶:۰۴ ب٫ظ

    عرض سلام ادب و احترام
    “من علمنی حرفا فقد صیرنی عبدا ” (حضرت علی علیه السلام)
    بنده علاقمند به یادگیری اکسل بوده که یکی از دوستان وب سایت اکسل پدیا را معرفی نمود از مطالعه شیوه آموزش و توضیحات ارائه شده بسیار تشکر میکنم.
    امیدوارم در سایر بخشها نیز شاهد ان باشم .

    • حسنا خاکزاد ۲۵ آبان ۱۳۹۶ / ۱۰:۰۶ ق٫ظ

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

      • ssrezaei ۲۵ اردیبهشت ۱۳۹۷ / ۱۱:۰۹ ق٫ظ

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

        • حسنا خاکزاد ۲۹ اردیبهشت ۱۳۹۷ / ۱۲:۵۱ ب٫ظ

          درود
          چه لینکی؟ از طرف کجا؟
          منظورتون واضح نیس

ارسال دیدگاه

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

توسط
تومان