تابع OFFSET اکسل و کاربرد آن

تابع OFFSET اکسل و کاربرد آن

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

قاعده کلی تابع OFFSET اکسل به صورت زیر هست:

(عرض[اختیاری]،ارتفاع [اختیاری]، تعداد ستون، تعداد سطر، سلول یا محدوده مرجع)OFFSET

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

برای اینکه درک کارکرد این تابع کمی ساده تر بشه، تابع OFFSET اکسل رو در قالب دو مثال ساده بررسی میکنیم. جدول زیر رو در نظر بگیرید:

A B C D
1 9 19 1 16
2 13 1 8 20
3 4 15 14 16
4 3 19 6 18

فرض کنید ما میخوایم از سلول A1 به سلول D2 برسیم. تابع OFFSET ما به شکل زیر میشه:

(OFFSET(A1,1,3

در اینحالت نتیجه عدد 20 خواهد بود.

حالا اگر بخوایم از سلول A1 به محدوده C2:D4 برسیم، باید فرمولمون رو به صورت زیر بنویسیم:

(OFFSET(A1,1,2,3,2

در اینجا چون نتیجه تابع یک سلول نیست و یک محدوده شامل شش سلول هست درون سلول خطای !VALUE# نمایش داده میشه. این حالت زمانی استفاده میشه که ما میخوایم با برای نتیجه تابع OFFSET از توابعی مثل SUM یا AVERAGE استفاده کنیم. مثلا نتیجه فرمول ((SUM(OFFSET(A1,1,2,3,2 برابر عدد 82 هست که جمع شش سلول C2 تا D4 هست.

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

  • سلول یا محدوده مرجع: این پارامتر سلول یا محدوده ای رو مشخص میکنه که ما میخوایم از اون شروع کنیم و با حرکت کردن به تعداد سطر و ستون مشخص به سلول یا محدوده جدید برسیم. این پارامتر میتونه یک سلول مثل A1 یا یک محدوده مثل A1:B3 باشه.
  • تعداد سطر: این پارامتر مشخص میکنه که ما از سلول یا محدوده مرجع میخوایم چند سطر و در چه جهتی جابجا بشیم. در صورتی که این عدد مثبت باشه محدوده مرجع به سمت پایین و در صورتی که این عدد منفی باشه به سمت بالا جابجا میشه. در صورتی که محدوده مرجع شما بیش از یک سلول باشه (مثلا A1:B6)، جابجایی از اولین سلول موجود در محدوده یعنی A1 محاسبه میشه. به عنوان مثال با تعیین عدد 1، محدوده A2:B7 به عنوان نتیجه برگردونده میشه.
  • تعداد ستون: این پارامتر مشخص میکنه که ما از سلول یا محدوده مرجع میخوایم چند ستون و در چه جهتی جابجا بشیم. در در حالت راست به چپ در صورتی که این عدد مثبت باشه محدوده مرجع به سمت چپ و در صورتی که این عدد منفی باشه به سمت راست جابجا میشه.در صورتی که محدوده مرجع شما بیش از یک سلول باشه (مثلا A1:B6)، جابجایی از اولین سلول موجود در محدوده (A1) محاسبه میشه. به عنوان مثال با تعیین عدد 1، محدوده B1:C6 به عنوان نتیجه برگردونده میشه.
  • ارتفاع [اختیاری]: این پارامتر مشخص میکنه که بعد از جابجا شدن از محدوده مرجع به تعداد سطر و ستون مشخص، ارتفاع محدوده ای که به عنوان نتیجه تابع برگردونده میشه باید چند سطر باشه. تعیین این پارامتر اختیاری هست و در صورتی که ما پارامترهای ارتفاع و عرض رو تعیین نکنیم، تابع OFFSET اکسل به صورت خودکار پارامترهای ارتفاع و عرض رو مساوی با ارتفاع و عرض محدوه مرجع ما قرار میده. به عنوان مثال اگر ما ارتفاع و عرض رو تعیین نکنیم و سلول A1 رو به عنوان پارامتر اول تابع OFFSET اکسل انتخاب کنیم ارتفاع و عرض هر دو برابر 1 و اگر  A1:B3 رو به عنوان محدوده مرجع انتخاب کنیم، ارتفاع برابر  3 و عرض برابر 2 خواهد بود.
  • عرض[اختیاری]: این پارامتر مشخص میکنه که بعد از جابجا شدن از محدوده مرجع به تعداد سطر و ستون مشخص، عرض محدوده ای که به عنوان نتیجه تابع برگردونده میشه باید چند ستون باشه. تعیین این پارامتر هم مثل پارامتر ارتفاع اختیاری هست.

نکته: تابع OFFSET اکسل یک تابع VOLATILE هست بدین معنی که با هر تغییر در ورکبوک شما، فارغ از اینکه تغییر موردنظر تاثیری رو نتیجه این تابع  داشته باشه یا نه، این تابع مجدداً محاسبه میشه. بنابراین در استفاده از این تابع دقت کنید چون استفاده نابجا و زیاد از این تابع میتونه فایل اکسل شما رو خیلی کند و سنگین کنه.

مثال: استفاده از تابع OFFSET برای ایجاد نمودار دینامیک با تغییر اطلاعات سلول ها

تابع OFFSET اکسل و کاربرد آن

مخصوص کاربران ویژه سایت
کاربر گرامی دسترسی کامل به مطالب مربوط به این بخش با اشتراک در بخش کاربران ویژه امکانپذیر میباشد. لطفا برای اشتراک در بخش کاربران ویژه از بخش عضویت ویژه استفاده نمایید

موفق باشید.

34 Comments on “تابع OFFSET اکسل و کاربرد آن”

    1. یاسین اسماعیل پور

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

  1. پویان

    سلام به نظرم یه چیزو اشتباه نوشتی. ستون در حالت مثبت میره به سمت راست نه چپ! درست میگم؟

    1. یاسین اسماعیل پور

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

  2. ehsaneconomy

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

    1. یاسین اسماعیل پور

      سلام احسان جان
      بزودی مطلبی در مورد نمدار های داینامیک در اکسل منتشر میکنم که اونجا بیشتر در این خصوص توضیح خواهم داد.

    1. یاسین اسماعیل پور

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

  3. وحید

    سلام مهندس جان فایل تصویری آموزش هارو هم دارید؟ در صورت عضویت ویژه این امکان برای اعضا وجود دارد؟

    1. یاسین اسماعیل پور

      سلام وحید جان
      در حال حاضر فایل تصویری برای آموزش ها وجود نداره. انشالله در برنامه دارم که از ابتدای سال دوره آموزشی به همراه ویدئو تصویری رو هم برای دوستانی که تمایل دارند آماده کنم. در صورت تمایل میتونی در اون دوره شرکت کنی وحید جان

  4. aaj

    سلام
    می خواستم بدونم، برای این که در جداول یا نمودارهای محوری (pivot table)، نمودار روی شیت کلا نمایش داده بشه، یا دیده نشه، میشه از این تابع کمک گرفت؟
    مثلا (البته pivot نیست) شما یک تقویم داری که فقط وقتی روی یک تاریخ خاص کلیک می کنی، قرارهای اون روز شبیه یک پنجره pop-up نمایش داده بشه و وقتی چیزی نباشه، اصلا پنجره ای دیده نشه؟

    1. انجام پروژه های اکسل در اینجا

      سلام ، جناب aaj
      سوالتون رو خیلی کلی ، مبهم و متناقض بیان کردید ، هیچ کاری غیرممکن نیست! فقط ما باید راهش رو پیدا کنیم ، میشه دقیق تر بفرمایید که چی بایستی پنهان و پیدا بشه ؟ که بتونم راهنمایی کنم!!
      خانیکی
      0919-302-6452

  5. سید حسن مذنب

    سلام
    من می خوام با مشخص کردن یک تعداد عدد ، ستون مربوطه به اونها رو انتخاب کنم…مثلا با اعداد 2و5و7 میخوام ستونهای 2و5و7 (B,E,G) انتخاب بشه (SELECT بشه)
    بعد میخوام با عوض شدن این اعداد ستون های انتخابی من هم عوض شه…یعنی میخوام SELECTION ستونها داینامیک باشه و بتونم با تغییر شماره ها ستون های مختلفی رو انتخاب کنم…راهش چیه آقای مهندس؟

  6. آموزش ماکرو نویسی در اینجا

    سلام ، آقا سیدحسن
    با ماکرونویسی میشه این کار رو انجام داد
    بر روی نام شیت ، کلیک راست کنید و گزینه View Code را انتخاب کنید
    کدهای نوشته شده رو پاک کنید و کدهای زیر را اونجا کپی کنید
    Private Sub Worksheet_Change(ByVal Target As Range)
    r = Target.Row
    c = Target.Column
    On Error Resume Next
    If r = 1 And c = 1 Then Columns(Cells(r, c).Value).Select
    End Sub
    حالا اگه به شیت برگردید و عددی را در خانه A1 تایپ کنید ، ستون متناظر با آن انتخاب میشود !!
    برای انتخاب چند ستون پشت سرهم بایستی نام لاتین ستونها نوشته بشه ، مثلاً C:G که ستون C تا G رو برای شما انتخاب میکنه !
    برای انتخاب چندین ستون ناپیوسته ، با تایپ اعداد ، آنطورکه شما میخواهید ، نیاز به کدنویسی بیشتری است که در اینجا نمی گنجد
    اما نقطه شروع ، از دستور فوق است و باید تا رسیدن به جواب مطلوب روی آن کار شود
    موفق باشید – خانیکی
    0919-302-6452

  7. مریم

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

  8. انجام پروژه های اکسل در اینجا

    سلام ، مریم خانم
    اگه تعداد شیتهایی که میخوان باهم جمع بشن خیلی زیاد نباشه ، میتونید مراحل زیر رو انجام بدین :
    شیت جدیدتون رو دقیقاً مثل شیتهای دیگه ایجاد کنید ، با این تفاوت که در این شیت جمع شیتهای دیگه قرار میگیره
    فرمول زیر رو که فقط برای خانه K18 وفقط برای سه شیت نوشته شده به تعداد همه شیتهاتون کامل کنید
    =Sheet1!K18+Sheet2!K18+Sheet3!K18
    و این فرمول رو در خانه K18 شیت جدیدتون قرار دید
    دیگه لازم نیست برای محاسبه جمع بقیه خانه ها ، این فرمول رو دوباره بنویسید !! میتونید به راحتی ، همین فرمول رو در دیگر خونه ها کپی کنید
    موفق باشید – خانیکی
    0919-302-6452

  9. Masoud

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

  10. Rahim

    سلام آقای اسماعیل پور.
    من یه فایلی دارم خب.. مثلا ردیف A2 تا A100 یه سری اعداد متفاوت داره. خب خیلی ساده برای جمع سام میزنم جمع میشه اعدادم. اما آیا راهی هم هست که اگر بین این همه اعداد اگر حرفی بزارم مثلا در خانه A21 یه حرف حالا هرچی باشه، بزارم.. بعد طوری باشه که فقط جمع اعدادی رو برام بزنه ک قبل خانه A21 باشه. نمیدونم کامل تونستم برسونم یا نه.. یخورده بیشتر توضیح میدم .. ببینید من اعدادی دارم مثلا 5 و 6 و 7 و 21 و 45 و (A1:A5) حالا توی خونه A3 جای عدد 7 مثلا حرفی بزارم تکست .. آیا فرمولی هست که بشه خونه ای رو که حرف توش باشه رو پیدا کنه و اعداد قبلشو برام سام بزنه.. موضوعم بسیار حیاتیه.. ممنون میشم پاسخگو باشین

  11. Rahim

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

  12. علی

    سلام
    من فایل مشخصات خانواری رو دارم که می خوام از بین 10000 کد خانوار، کد 1000 خانوار مشخص رو جدا کنم. یعنی 1000 سلول مشخص از 10000 سلول یک ستون. ممکنه راهنماییم کنین

  13. Parsa

    سلام
    وقتتون بخیر
    من توی یه شیتم یه جدول دارم که حاوی حدود 20 ستون هست (20*2) . ولی از این 20 ستون معمولا 5 تاش عدد داره و بقیه ستونها معمولا صفرن. (تولیدات روزانه یه کارگاه از محصولات مختلف هست که توی روز معمولا 5 نوع تصادفی از محصولات مختلف رو تولید میکنه). میخوام توی یه شیت جدید یه کپی از این جدول داشته باشم ولی فقط ستونها (هدر ستون و مقدارش) که غیر صفر هست رو نشون بده (5*2).(یعنی فقط تعداد همون 5 محصولی که امروز تولید داشته رو) میشه راهنماییم کنید چطور این کار رو انجام بدم؟
    تشکر

    1. یاسین اسماعیل پور

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

  14. بیک زاده

    سلام
    خسته نباشین .یه سوالی داشتم در مورد تاریخ و زمان در اکسل
    من تعدادی نمونه (بتن) در یک روز تهیه کردم مثلا در روز 1395/09/10 وتعدادی از این نمونه ها رو 7 روز بعد و تعدادی رو 28 روز بعد مورد آزمایش قرار بدم .
    الان روز نمونه گیری رو تو یه سلول مشخص می کنم . چطور باید تو سلولی دیگه اون 7 روز آیده یعنی 17/09/1395 و در سول دیگه 28 روز بعدی رو از تاریخ نمونه گیری مشخص کنم ؟
    ممنون می شم کمکم کنین.

  15. ساناز

    سلام من مثال اول و امتحان کردم با فرمول offset اما خروجی نمیده خوب نباید یه ربطی به برنامه که مینویسیم داشته باشه یکم توزیح بذهید

  16. فهیمه

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

  17. mona

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

پاسخ دهید

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