تابع Offset و آغاز ایجاد داشبورد
تابع Offset و کاربردهای آن
تابع Offset از دسته توابع جستجو یا Lookup & Reference هست. این تابع کاربرد خیلی زیادی در ایجاد نمودارهای پویا، محدوده های داینامیک داره که همه این موارد در تهیه نرم افزار، گزارشگیری حرفه ای و تهیه داشبوردهای مدیریتی کاربرد خیلی زیادی دارن. از این تابع برای ایجاد یک محدوده استفاده میشه. برای اینکه خوب درک کنیم اول باید آرگومانهاش رو بشناسیم:
Reference: این آرگومان آدرس یک سلول هست. سلول مرجعی که آدرس دادن از این سلول شروع میشه.
Row: این آرگومان عددی است. این عدد به این معنی هست که میگه از سلول مرجع، این تعداد سلول بیا پایین/بالا. اگر عدد مثبت باشه، به سمت پایین و اگر منفی باشه به سمت بالا شیفت میشه.
Column: این آرگومان عددی است. این عدد به این معنی هست که میگه از سلول مرجع، این تعداد سلول برو سمت راست/چپ. اگر عدد مثبت باشه، به سمت راست و اگر منفی باشه به سمت چپ شیفت میشه.
[height]: این آرگومان عددی و اختیاری است. ارتفاع (تعداد سلول) محدوده مورد نظر رو تعیین میکنه. در صورتی که تعیین نشه، ۱ در نظر گرفته میشه.
[width]: این آرگومان عددی و اختیاری است. پهنای (تعداد سلول) محدوده مورد نظر رو تعیین میکنه.در صورتی که تعیین نشه، ۱ در نظر گرفته میشه.
در واقع این تابع به ۲ صورت مورد استفاده قرار میگیره:
- فراخوانی داده مورد نظر از بین یک سری داده از طریق تعیین آدرس سلول مورد نظر. که داده موجود در آدرس تعیین شده رو بر میگردونه.
مثال: میخوایم داده مشخصی رو از بین داده های یک محدوده فراخوانی کنیم.
=OFFSET(E3,۳,۲)
شکل ۱- فراخوانی داده خاصی از از یک سری داده با استفاده از تابع Offset
از سلول E3 به تعداد ۳ سلول میره پایین و به تعداد ۲ سلول میره راست. تا اینجا رسیدیم به سلول G6. از اونجا محدوده ای به ارتفاع ۱ و پهنای ۱ سلول رو در نظر میگیره (آرگومان های ۴ و ۵ تابع Offset در صورتیکه مشخص نشه، ۱ در نظر گرفته میشه). چون خروجی این تابع یک سلول است، محتویات همون سلول G6 نمایش داده میشه.
- ارجاع به یک محدوده در اکسل. چون خروجی این تابع یک محدوده هست، به تنهایی کاربرد نداره و حتما باید با ابزار یا تابع دیگه ای ترکیب بشه. در غیر اینصورت خروجی تابع خطا خواهد بود.
مثال: میخوایم داده های موجود در ردیف دوم رو با هم جمع بزنیم.
=Offset(E3,1,o,1,4)
شکل ۲- جمع محدوده خاصی از از یک سری داده با استفاده از تابع Offset
از سلول E3 به تعداد ۱ سلول میره پایین و به تعداد ۰ سلول میره راست. تا اینجا رسیدیم به سلول E4. از اونجا محدوده ای به ارتفاع ۱ و پهنای ۴ سلول رو در نظر میگیره. یعنی E4:H4. چون خروجی این تابع یک محدوده است، به تنهایی خطا میده. پس با تابع دیگه ای مثل Sum ترکیب میکنیم. خروجی جمع محدوده تعیین شده خواهد بود.
دقت کنید که برای مثال های حل شده راههای دیگه ای با توابع دیگه ای هم میشه ارائه داد. اما هدف صرفا مشاهده نحوه عملکرد این تابع هست. مثال های کاربردی استفاده از این تابع رو در تهیه محدوده های پویا، گزارشگیری و داشبوردهای مدیریتی در آینده ارائه خواهیم داد.
به تصویر زیر نگاه کنید. با تغییر آرگومان های مختلف، خروجی محدوده ای که تعیین میشه رنگی میشه. میتونید این فایل رو دانلود کنید و با تغییر اجزای تابع، نتیجه رو ببینید تا بهتر بتونید هر دو مفهوم این تابع رو درک کنید. سل مرجع با خط کشی قرمز و محدوده در نظر گرفته شده به رنگ زرد نمایش داده میشه.
این فایل تمرین بسیار خوبی از فرمول نویسی در فرمت دهی شرطی هم میتونه باشه. سعی کنید روش کار این فایل رو برای خودتون تحلیل کنید که چطور با تغییر در سلول ها، محدوده های رنگی (متناسب با مفهوم تابع Offset) تغییر میکنه. محدوده سلول A1:B3 سلولهای کمکی هستند که در فرمول نویسی فرمت دهی شرطی استفاده شدن.
دانلود فایل این آموزش
فایلی که تو این آموزش ایجاد شده رو میتونید از لینک زیر دانلود کنید:
سلام
وقت بخیر
من یک فایل دارم که بایستی تو یک شیت بر اساس جستجوی محتوای یک سلول تو شیت اول بره تو شیت دوم جستجو کنه و در صورت وجود همون محتوا مقداری که سه سطر پایینتر از سلول شیت دوم هست رو برای فرمول فراخوان کنه. با Lookup و تلفیقش با offset موفق نشدم. اگر ممکنه راهنمایی بفرمایید.
سپاسگزارم
درود
با توابع جستجو وقتی پیدا کردید سلول مورد نظر رو، ادرسشو پیدا کنید با ترکیب تابع adress با row , column
بعد با Indirect بذارید داخل تابع offset و بعد بگید ۳ تا پایین تر
سلام ممنون از سایت خوب تون و توضیحات کاربردی تون
من یه سوال داشتم در تایع sumifs در قسمت شرط ها میشه بیش از یکبار از offset استفاده کرد؟ یعنی چند تا شرط بصورت پویا (با بیش از یک مقدار ) داشته باشیم؟
سپاس
سلام
حتما باید از قبل برای هر تعداد شرطی که میتونه لحاظ بشه در تابع Sumifs گزینه تعریف کرده باشید. این گزینه ها میتونن شرط رو از سلول بخونن.
با سلام و عرض ادب
آیا امگان استفاده از یک تابع مانند (N:N)min در آرگومان Reference به جای معرفی یک سلول مشخص می باشد
پیشاپیش از توجهتان متشکرم
درود بر شما
جنس ارگومان رفرنسه یعنی باید ادرس باشه
با استفاده از min مکان سلول رو پیدا کنید و ادرسش رو بسازید و بذارید توی فرمول
سلام ببخشید چطور میتونم از یک شیت دیگه آدرس بدم که هشت تا هشت تا جلو بره شماره ردیف؟ مثلا من میخوام در شیت یک اطلاعات ستون دوم از شیت دو هشت تا هشت تا جلو بره
در.د بر شما
باید الگو رو با row یا column بسازید
این مقاله رو بخونید ایده بگیرید
https://excelpedia.net/address-function/
هم تابع offset هم address اینکار رو میکنن
سلام ممنونم از پاسخگوییتون…الان من با تابع offset اینو نوشتم OFFSET(Sheet2!AG6;0;0;1;1)= و کدملی که میخواستم رو نشون داد… اما نمیدونم باید چی بنویسم که بعدی بشه ag 14 , ag 22 ,… به همین ترتیب ۸تایی جلو بره…میشه لطفا راهنماییم کنید
درود
اون تابع address که گفتم و بخونید با مثال هاش
میتونید ۸ تا ۸ تا ببرید جلو
نشد خواهر نشد…:))) احتمالا که نه ۱۰۰% یه جا رو اشتباه میزنم ولی بازم مرسی مرسی :*
سلام میشه کدنویسی vba مربع جادویی ۳*۳ رو آموزش بدید؟
سلام وقت بخیر بنده یه جدولی دارم میخام مثلا اگر یه حرف ازنام قطعه رو نوشتم خودت مثل گوگل حدس بزنه بگه اینه یا…..مثل گوگل که یه کلمه میخای سرچ کنی حرف اولو مینویسی گوگل چندتا کلمه پایین میاره میگه کدوم .. مممنون و شپاس اززحمات بی دریغ شما
درود
در حدی که autocomplete خود اکسل داره، میتونه اونکار و انجام بده که اونم بر اساس داده های از قبل وارد شده هست
اما اگر میخواید قسمتی از کلمه رو بنویسید و لیستی از اون کلمات بهتون بده مقاله زیر رو ببینید و ازش ایده بگیرید
https://excelpedia.net/searchable-list/
سلام
من یه کاربرگی دارم که توش تولیدات روزانه ثبت میشه و محصولات تولیدی هر روز متفاوته
آیا فرمولی هست که بشه گزارشی از این لیست استخراج کرد که مثلا در فلان تاریخ فهمید که چه کالایی تولید شده؟ بطور مثال تو یه سلول تاریخ رو که نوشتیم در سلولهای بعدی کالاهای تولید شده رو برامون بیاره.
درود بر شما
با ابزار pivot میتونید انجام بدید
در نهایت فرمول هم میتونه این کار و انجام بده منتها باید فرمول نویسی آرایه رو بدونید تا بتونید با توجه به ساختار داده جستجو های متنوعی رو انجام بدید
با سلام
ممنون بابت مطالب مفید سایت. من تعداد زیادی متغیرهای کیفی در اکسل وارد می کنم و با کمک تابعcount if خروجی میگیرم که به طور مثال هر داده چند بار تکرار شده.هر کدام از متغیرهای کیفی به چند دسته تقسیم میشن اما نمی دونم از چه فرمولی استفاده کنم تا بتونم بفهمم هر دسته از متغیر کیفی چند بار تکرا شدند؟ممنون میشم راهنماییم کنید چون واقعا برای گرفتن این خروجی به مشکل برخوردم.میتونم نمونه اکسل رو براتون بفرستم
درود بر شما
دسته ها رو هم باید توی داده ها داشته باشید یا منطق دسته بندی
که بر اساس اون بتونید شمارش کنید
منطق همون countif هست
با عرض سلام و خسته نباشید از آموزشهای مفید و حرفه ایتون. از تلاشهای بی دریغ و مستمری که بابت تهیه مطالب و آموزش انجام دادید صمیمانه سپاسگزارم. آرزوی توفیق و سلامتی برایتان را از خداوند مسئلت دارم.
سلام و خسته ناشید
دستتون درد نکنه بسیار جالب بود . اگه میشه کمی هم درباره vbتوضیح بدهید
باسپاس
درود بر شما
در این لینک مقالات VB رو مشاهده میکنید:
https://excelpedia.net/category/vba/
این هم کتاب پیشنهادی:
https://excelpedia.net/excel-2013-power-programming-with-vba/
با سلام و عرض ادب
یک نمودار تحت اکسل طراحی کردم که از یک سری داده بدست میاد که این داده ها شامل عدد، متن و سلول های blank میشه.
وقتی نمودار رو رسم میکنم و رنج رو مشخص میکنم تمام سلول ها رو در نظر میگیره. میخوام در محدوده انتخاب ده متون و سلول های خالی رو در نظر نگیره و فقط نمودار رو برای داده های عددی( که شامل صفر ها هم بشه ولی سلول های blank نه) رسم کنه.
سعی کردم با تابع offset این کار رو انجام بدم ولی نمیدونم چطور میشه چند محدوده رو انتخاب کنم.
با توجه به اینکه تعداد داده هام زیاد هست پیشنهادتون برای این کار چیه؟
با تشکر فراوان از مطالب آموزنده و کاربردی شما.
سلام
کافیه یک ستون تعریف کنید که به جای سلول های خالی و متنی که نمیخواید تو نمودار نمایش داده بشه مقدار =NA() رو بذارید و اعداد هم خودشون رو بذارید و این ستون رو در رنج نمودار انتخاب کنید.
عرض سلام ادب و احترام
“من علمنی حرفا فقد صیرنی عبدا ” (حضرت علی علیه السلام)
بنده علاقمند به یادگیری اکسل بوده که یکی از دوستان وب سایت اکسل پدیا را معرفی نمود از مطالعه شیوه آموزش و توضیحات ارائه شده بسیار تشکر میکنم.
امیدوارم در سایر بخشها نیز شاهد ان باشم .
سلام بر شما
خوشحالیم که مفید بوده
انشاله در همه زمینه های فعالیت خواهیم داشت
موفق و پیروز باشید
با سلام مجدد
احتراما سوالم درخصوص روش جابجایی رنگ سلول، برام یک ایمیل آمد که نوشته بود روی لینک زیر کلیک کنید تا تایید شود ولی لینکه بصورت تکس هست و نمیشه روی آن کلیک کرد لطفا راهنمایی کنید
ممنون
درود
چه لینکی؟ از طرف کجا؟
منظورتون واضح نیس