آیا می‌دانستید که تابع OFFSET در اکسل می‌تواند بیشتر از ارجاع دادن به سلول‌ها انجام دهد؟ این روش های منحصر به فرد برای استفاده از آن را بررسی کنید!
تابع OFFSET مایکروسافت اکسل یک ابزار بسیار قدرتمند و همه کاره است. با این حال، به دلیل پیچیدگی و تعداد عملکردهای مشابه، متأسفانه اغلب کمتر مورد استفاده قرار می گیرد.
عملکرد نسبتاً ساده می تواند باعث صرفه جویی در وقت و ناامیدی نویسندگان اکسل شود. استخراج نقاط دقیق داده از یک مجموعه داده اصلی به خوبی سازماندهی شده را ساده می کند. همچنین امکان ایجاد ساده گزارش های بسیار دقیق، تجسم های تعاملی و داشبوردهای پیچیده را فراهم می کند.
1. ایجاد مجموعه داده با اندازه متغیر
یکی از رایج‌ترین کاربردهای تابع OFFSET اکسل، گرفتن مجموعه‌های داده با اندازه متغیر است. با توجه به مجموعه بزرگی از داده ها، ممکن است فرد بخواهد در هر زمان به بخش کوچکی از داده ها نگاه کند.
به عنوان مثال، با توجه به یک گزارش خودکار از مجموع فروش برای کارکنان روزانه، ممکن است لازم باشد که مجموع فروش را برای یک هفته یا چند هفته مشخص کنید.
نوشتن یک تابع SUM متفاوت برای هر مجموع می تواند به راحتی انجام شود. با این حال، یک تابع OFFSET ممکن است برای سهولت استفاده و قابلیت استفاده مجدد بسیار سریعتر باشد.
ابتدا روزی از ماه را که می خواهیم با آن شروع کنیم را در سلول B6 وارد کنید. سپس وارد می کنیم که چند روز می خواهیم گزارش را در B7 اجرا کنیم. سپس، می‌توانیم از یک تابع SUM ساده و تابع AVERAGE برای دریافت اطلاعات بیشتر در مورد هر کارمند در بازه زمانی انتخاب شده استفاده کنیم:
=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))
=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)
تنظیم تاریخ شروع یا طول زمان به سادگی تنظیم یک سلول واحد است که می‌تواند کشیدن مجموعه داده‌های با اندازه متغیر را بسیار آسان‌تر کند.
2. اجازه دادن به تولید گزارش سفارشی
استفاده از روش های مشابه با روش های بالا، تهیه یک گزارش کامل با تنظیم متغیر را نسبتاً آسان می کند. می توانید اولین آرگومان را در تابع OFFSET به برگه دیگری تبدیل کنید. انجام این کار به شما این امکان را می دهد که ورود و گزارش داده های خود را جدا نگه دارید.
این امکان کشیدن برش های داده منتخب از یک صفحه به صفحه دیگر را فراهم می کند. علاوه بر این، به شما امکان می دهد فقط اطلاعات مربوط به گزارش فعلی خود را نمایش دهید. تنظیم گزارشی که مجموع روزانه را با استفاده از همان داده های قبلی نشان می دهد بسیار ساده است. شما می توانید این کار را بدون کشیدن داده ها برای روزهای نامربوط انجام دهید.
تابع افست زیر که در سلول B5 قرار داده شده است، تعداد روزهای مشخص شده در B2 را نشان می دهد. در هفته مشخص شده در B1 شروع می شود و فقط داده های دوره مربوطه را نمایش می دهد:
=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)
می‌توان ورودی‌های B1 و B2 را محدود کرد و با استفاده از اعتبارسنجی داده‌ها، فهرست‌های کشویی در Excel با تعداد هفته‌های موجود ایجاد کرد.
3. صفحه گسترده صفحه بندی
تابع OFFSET همچنین می‌تواند به صفحه‌بندی ساده صفحه‌گسترده دست یابد، وظیفه‌ای که معمولاً با استفاده از تب توسعه‌دهنده اکسل مرتبط است. این می تواند کار با مجموعه داده های بزرگ را ساده تر و کارآمدتر کند.
هنگام کار با صفحه‌گسترده‌ای با 30000 ردیف داده، نگاه کردن به کل مجموعه داده‌ها در یک زمان دشوار خواهد بود. ایجاد یک کشویی که محدوده ای از اعداد از 1 تا 300 را می پذیرد به ما امکان می دهد تا به سرعت داده ها را به صفحات 100 نتیجه تقسیم کنیم.
تابع OFFSET همیشه لنگر خود را در سلول سمت چپ بالای داده تنظیم می کند. اما آرگومان دوم، افست عمودی است. این برابر با شماره صفحه انتخابی منهای یک برابر 100 خواهد بود. افست افست، آرگومان سوم، را می توان روی 0 تنظیم کرد.
آرگومان چهارم روی 100 و آرگومان پنجم برای تعداد ستون های مجموعه داده تنظیم شده است. این روش باعث می‌شود که تکه‌های بسیار بزرگ داده به قطعات بسیار قابل مدیریت‌تری تقسیم شوند.
در اینجا نمونه ای از مجموعه داده صفحه بندی شده است. این داده ها شامل 500 نمونه از اطلاعات ثبت شده از مجموعه ای از حسگرها در یک صفحه و یک گزارش صفحه بندی شده در برگه دیگر است.
این گزارش از اعتبارسنجی داده ها در سلول B1 برای ایجاد یک کشویی با صفحات موجود در آن استفاده می کند. عبارت زیر در سلول A3 بدنه گزارش را ایجاد می کند:
=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)
4. برش داده ها برای نمودارها
ما می توانیم یک نمودار به گزارش فروش که قبل از استفاده از تابع OFFSET ایجاد کرده ایم اضافه کنیم. نمودار می تواند اطلاعات متغیر را بر اساس داده های انتخاب شده در دو منوی کشویی بالای برگه بکشد.
هنگام تغییر مقادیر بالای صفحه، نمودار به طور خودکار تغییر می‌کند تا هفته‌های انتخابی را منعکس کند و به هر کسی که گزارش را مشاهده می‌کند، یک مرجع بصری سریع ارائه می‌کند.
ترکیب نمودارها و نمودارها با تابع افست، تجسم داده های پیچیده و تغییر را بسیار آسان تر می کند. همچنین امکان تولید داشبوردهای ساده و درون اکسل را فراهم می کند.
5. کشیدن اطلاعات بر اساس تغییر تاریخ
هنگامی که در مورد داده های گزارش یا داده های استخراج شده از فرآیندهای زمانی خودکار صحبت می شود، جستجو برای اطلاعات خاص بر اساس مُهر زمانی غیرعادی نیست. هنگام در نظر گرفتن یک دوره واحد، استفاده از توابع شرطی مانند IF یا IFS برای پایین کشیدن فقط داده های مورد نیاز دشوار نیست. اگر نیاز دارید که داده‌ها را بر اساس محدوده‌ای از روزها یا زمان‌ها جمع‌آوری کنید، خیلی آسان است که در یک شرط تودرتوی عظیم قرار بگیرید.
با استفاده از ترکیبی از COUNTIF و OFFSET، استخراج داده ها از محدوده زمان یا روز بسیار ساده است. از یک سلول برای نمایش یک روز شروع استفاده کنید. سپس، از یک سلول دیگر برای نشان دادن مدت زمانی که می‌خواهید از آن بیرون بکشید، استفاده کنید.
در تابع OFFSET، بسته به طرح داده های شما، ارتفاع یا عرض را می توان با استفاده از تابع COUNTIF در اکسل روی یک مقدار تنظیم کرد. این به شما امکان می دهد تعیین کنید که چه تعداد ورودی در محدوده درخواستی قرار دارند و فقط داده های مرتبط را بکشید.
در این مثال، ما یک مجموعه داده داریم که شامل ورودی های چندین سال است. می‌توانیم از تاریخ وارد شده در سلول B1 و تابع زیر استفاده کنیم تا فقط ورودی‌هایی را که پس از آن تاریخ رخ داده‌اند، بکشیم:
=OFFSET(Sheet1!U2, COUNTIF(Sheet1!V2:V501, "<" & B1), 0, 500 - COUNTIF(Sheet1!V2:V501, "<" & B1), 1)
این ابزار کاربردی قابل توجهی را به گزارش اضافه می کند و به شما امکان می دهد مقادیری را که قبل یا بعد از یک تاریخ خاص آمده اند فیلتر کنید. اگر به جای آن از تابع COUNTIFS استفاده می کنید، حتی می توانید تاریخ پایان را اضافه کنید و به کاربر نهایی تنوع بیشتری بدهید.
6. ترکیب با COUNTIF
علاوه بر افزایش بازه زمانی، تابع COUNTIF برای کشیدن تکه های خاصی از انواع داده های مرتب شده با عملکرد OFFSET اکسل به خوبی عمل می کند. تنظیم آرگومان های ارتفاع و عرض تابع OFFSET با استفاده از COUNTIF امکان پذیر است. علاوه بر این، می توانید به راحتی هر داده عددی را با مقدار قابل تنظیم از جایی در برگه خود تغییر دهید.
اگر فهرستی از تراکنش های یک روز داشتید که بر اساس مقدار تراکنش ها مرتب شده بودند، می توانید از این تکنیک برای کشیدن هر تراکنش به مقدار مشخصی استفاده کنید. این را می توان با استفاده از یک تابع OFFSET ساده مانند زیر انجام داد:
=OFFSET(A2, COUNTIF(B2:B22, "<" & H3), 0, COUNTIF(B2:B22, "<" & H3), 4)
یکی از مهمترین مواردی که در این تابع باید به آن توجه داشت، آرگومان دوم تا آخر، COUNTIF (B2:B22، “<" و H3) است. هدف از این آرگومان این است که مشخص کند چند ردیف باید کشیده شود. برای جلوگیری از خطاهای احتمالی در داده‌های استخراج‌شده، این تعداد ردیف‌ها را فقط به تعداد منطبق با نیاز محدود می‌کند.
یکی دیگر از ملاحظات مهم برای کسانی که به دنبال استفاده از OFFSET به این روش هستند این است که فقط در صورتی کار می کند که داده هایی که با آنها کار می کنید به خوبی مرتب شده باشند. داده های نامرتب نتایجی را که به دنبال آن هستید بر نمی گرداند. خوشبختانه، اکسل مجموعه‌ای از ابزارهای مرتب‌سازی خودکار را نیز برای مرتب کردن داده‌های شما دارد.
می‌توانید از این برای انجام اقدامات از برش متغیر محصولات بر اساس قیمت گرفته تا عملیات‌هایی که فقط به تعداد معینی انجام می‌شوند، استفاده کنید. توانایی کنترل مقدار دقیق داده‌های استخراج‌شده، کنترل دقیقی بر نمونه دقیق داده‌هایی که از صفحه‌گسترده خود می‌کشید، می‌دهد. این امکان ایجاد انواع گزارش های مفصل و تعاملی را فراهم می کند.
این همان چیزی است که عملکرد OFFSET در واقع برای آن مفید است
در نهایت، عملکرد OFFSET به کاربران اجازه می دهد تا اطلاعات را از صفحه گسترده خود با دقت بالا استخراج کنند. این باعث صرفه جویی در زمان می شود و به کاربران اجازه می دهد تا عملیات بیشتری را با عملکردهای کمتر انجام دهند.
این کار کشیدن داده های دقیق مورد نیاز خود را بدون توسل به شرط های تودرتو ساده می کند. همچنین در زمان و تلاش کاربران در ایجاد گزارش های سفارشی، داشبورد و موارد دیگر در اکسل صرفه جویی می کند.