چگونه از تابع OFFSET در اكسل استفاده كنیم؟

چگونه از تابع OFFSET در اكسل استفاده كنیم؟آموزش اكسلآموزشجمعه ۷ دی ۱۴۰۳ - ۱۵:۳۰مطالعه 7 دقیقهنوید عموزادهتبلیغاتمشاهده همه ویدئو‌هاتبلیغاتتبلیغاتبا تابع OFFSET در اكسل، یاد می گیرید كه چگونه با جا‌به‌جایی پویا در سلول‌‌ها، گزارش‌‌های حرفه‌‌ای و دقیق‌تری ایجاد كنید .

تبلیغاتتابع آفست در اكسل یكی از توابع پیشرفته و كاربردی است كه برای ایجاد مرجع پویا و تغییرپذیر استفاده می‌شود. این تابع به كاربر اجازه می‌دهد با مشخص كردن تعداد ردیف‌ها و ستون‌ها، به سلولی خاص یا محدوده‌ای از سلول‌ها ارجاع دهد .

فهرست مطالبچطور از تابع OFFSET در اكسل استفاده كنیم؟استفاده از OFFSET در دنیای واقعیبرای درك بهتر، شكل عمومی تابع OFFSET را در نظر بگیرید كه شامل پنج آرگومان است:=OFFSET(reference,rows,cols,height,width)reference یا A: مرجع پایه (سلول یا محدوده‌ای از سلول‌ها) كه از آن‌جا جابه‌جایی انجام می‌شود .

rows یا B: آرگومان دوم تابع offset، تعداد ردیف‌هایی است كه باید از مرجع پایه جابه‌جا شود و می‌تواند مثبت یا منفی باشد.cols یا C: تعداد ستون‌هایی كه باید از مرجع پایه جابه‌جا شود (می‌تواند مثبت یا منفی باشد) .

height (اختیاری) یا D: ارتفاع محدوده‌ی مورد نظر (تعداد ردیف‌ها).width (اختیاری) یا E: عرض محدوده‌ی مورد نظر (تعداد ستون‌ها).نكته‌ی اول: اگر مقادیر B و C و D و E به‌جای یك عدد ثابت، مرجع سلولی باشند، این مقادیر به‌طور پویا تغییر می‌كنند و می‌توانند از سلول‌های دیگر تأثیر بپذیرند .

نكته‌ی دوم: اگر مقدار B منفی باشد، جابه‌جایی به سمت بالا صورت می‌گیرد، و اگر مقدار C منفی باشد، جابه‌جایی به سمت چپ انجام می‌شود.كپی لینكچطور از تابع OFFSET در اكسل استفاده كنیم؟شاید توضیحات بالا كمی پیچیده به‌نظر برسند؛ اما با دیدن چند مثال، متوجه می‌شوید كه این تابع به‌سادگی قابل استفاده است .

برای نشان دادن نحوه‌ی عملكرد تابع آفست، از مثال بسیار ساده‌ای استفاده می‌كنیم. اگر فرمول زیر را در سلول A6 وارد كنید:=OFFSET(A1,2,3,2,3)فرمول به این شكل تفسیر می‌شود و عملیات زیر را انجام می‌دهد:نقطه‌ی شروع، سلول A1 است .

از سلول A1 دو ردیف به پایین می‌رویم.سپس سه‌ ستون به سمت راست حركت می‌كنیم.ناحیه‌ای به‌اندازه‌ی دو ردیف (ارتفاع) و سه ستون (عرض) از D3 انتخاب می‌شود.صفحه گسترده اكسل حاوی فرمول افستذخیره خودكار فایل اكسل هنگام قطعی برقراهكار كلیدی برای ذخیره خودكار فایل اكسل در هنگام قطعی برقاگر مقادیر D و E را در فرمول خود وارد نمی‌كردید، نتیجه به‌‌طور پیش‌فرض فقط به سلول D3 (هومن) ارجاع می‌داد .

دلیلش این است كه در اكسل، اگر ارتفاع و عرض را مشخص نكنید، فرض بر این است كه نتیجه همان اندازه‌ی مرجع (Reference) خواهد بود.كپی لینكاستفاده از OFFSET در دنیای واقعیبرای درك بهتر موضوع، از مثال دیگری استفاده می‌كنیم .

توجه داشته باشید كه تابع offset در excel به‌تنهایی كاربرد چندانی ندارد و معمولاً در كنار توابع دیگر استفاده می‌شود تا به نتایج كاربردی‌تر برسیم.در این مثال، جدولی داریم كه تعداد واحدهای فروخته‌شده توسط ۱۰ كارمند را در سه هفته نشان می‌دهد .

حالا با استفاده از تابع OFFSET، سه نوع گزارش ایجاد می‌كنیم.جدول داده‌ها و ایجاد سه گزارشگزارش اول (جدول بنفش): تعداد فروش یك كارمند مشخص در یك هفته‌ی مشخصهدف: زمانی كه شناسه‌ی كارمند و شماره‌ی هفته را وارد می‌كنیم، تعداد فروش آن كارمند در هفته‌ی مشخص نمایش داده شود .

در سلول H4 فرمول زیر را وارد كنید:=OFFSET(B1,H2,H3)توجه: از سلول B1 به عنوان اولین ورودی شروع می‌كنیم؛ زیرا می‌خواهیم هفته ۱ در اولین سلول سمت راست سلول B1 باشد؛ بنابراین، هر زمان كه از تابع OFFSET استفاده می‌كنید، با دقت درباره‌ی محل نقطه‌ی شروع خود فكر كنید .

B1: نقطه‌ی شروع جدول.H2: شماره‌ی كارمند (تعداد ردیف‌هایی كه باید جابه‌جا شود).H3: شماره‌ی هفته (تعداد ستون‌هایی كه باید جابه‌جا شود).حالا، اگر عدد ۶ را در سلول H2 (شناسه كارمند) و عدد ۲ را در سلول H3 (شماره هفته) وارد كنیم .

این فرمول تعداد فروش كارمند شماره‌ی ۶ (آیلار) را در هفته‌ی ۲ نمایش می‌دهد.استفاده از تابع offset برای استخراج داده‌ هابا تغییر مقادیر سلول‌های H2 و H3، می‌توانید اطلاعات هر كارمند و هفته‌ی دلخواه را دریافت كنید .

گزارش دوم: گزارش كلی كاركناندر ادامه، می‌خواهیم گزارشی از كاركنان در جدول آبی ایجاد كنیم؛ به‌طوری كه با واردكردن شناسه‌ی یك كارمند، اكسل به‌طور خودكار نام كارمند را (با استفاده از تابع VLOOKUP)، تعداد كل هفته‌های داده‌های موجود را (با استفاده از تابع COUNTIF) و مجموع و میانگین واحدهای فروخته‌شده توسط آن كارمند را با استفاده از توابع SUM و AVERAGE نشان دهد .

برای شروع، شناسه‌ی كارمند را در سلول H7 تایپ می‌كنیم. به‌عنوان مثال، شناسه‌ی كارمند ۳ را وارد می‌كنیم كه به جاوید مربوط است. سپس از تابع VLOOKUP برای بازگرداندن نام جاوید در سلول H8 استفاده می‌كنیم:=VLOOKUP(H7,A2:B11,2)استفاده از تابع VLOOKUP برای برگرداندن نام كارمند از شناسهحال از تابع COUNTIF با استفاده از كاراكتر جایگزین (*) برای شمارش تعداد سلول‌ها در ردیف اول كه شامل كلمه‌ی هفته به‌اضافه‌ی یك عدد باشد، استفاده می‌كنیم:=COUNTIF(1:1,"هفته*")استفاده تابع COUNTIF در اكسل برای شمارش تعداد سلول‌های ردیف ۱ حاوی كلمه‌ی هفتهاكنون می‌توانیم با استفاده از این اطلاعات، مجموع و میانگین واحدهای فروخته‌شده توسط جاوید را محاسبه كنیم .

ابتدا در سلول H10 این فرمول را وارد می‌كنیم:=SUM(OFFSET(B1,H7,1,1,H9))كه در آن B1 نقطه شروع است، H7 تعداد ردیف‌ها برای جابه‌جایی به پایین است (شناسه كاركنان)، عدد ۱ تعداد ستون‌ها برای جابه‌جایی به سمت راست است، عدد ۱ ارتفاع نتیجه است و H9 عرض نتیجه (تعداد كل هفته‌ها) است .

استفاده از توابع SUM و OFFSET برای استخراج داده‌ها از جدولافزون‌براین می‌توانیم فرمول مشابهی را در سلول H11 كپی كنیم و فقط SUM را به AVERAGE تغییر بدهیم؛ زیرا از همان ارجاعات استفاده می‌‌شود:=AVERAGE(OFFSET(B1,H7,1,1,H9))استفاده توابع AVERAGE و OFFSET برای استخراج داده‌ها از جدولحالا می‌توانیم مقدار موجود در سلول H7 را تغییر دهیم تا گزارش كاركنان دیگری را از جدول داده‌ها دریافت كنیم .

افزون‌براین، اگر هفته‌ای جدید به داده‌ها اضافه شود، سلول H9 به‌طور خودكار به هفته ۴ تغییر می‌كند و محاسبات بعدی در سلول‌های H10 و H11 نیز به‌روزرسانی می‌شوند.گزارش سوم: گزارش هفتگی می‌خواهیم با واردكردن شماره‌ی هفته، مجموع و میانگین فروش كل كارمندان در آن هفته نمایش داده شود:۱- در سلول H14 شماره هفته را وارد كنید (مثلاً: ۱ برای هفته اول) .

۲- در سلول H15 مجموع فروش را با فرمول زیر محاسبه كنید:=SUM(OFFSET(B1,1,H14,10,1))B1: نقطه‌ی شروع.۱: تعداد ردیف‌های جابه‌جایی.H14: شماره‌ی هفته (ستون مربوطه).۱۰: ارتفاع (تعداد كارمندان).۱: عرض (یك ستون) .

استفاده از توابع SUM و OFFSET برای استخراج داده ها از جدول۳- برای میانگین فروش، فرمول زیر را در سلول H16 وارد كنید:=AVERAGE(OFFSET(B1,1,H14,10,1))۴- با تغییر مقدار سلول H14، داده‌های هفته‌های دیگر نمایش داده می‌شوند .

استفاده از توابع میانگین و افست برای استخراج داده‌‌ها از جدولترفند اكسل برای صرفه‌‌جویی در زمان انجام پروژهبیش‌از ۱۰ ترفند اكسل برای صرفه‌‌جویی در زمان انجام پروژهتابع offset در excel، در تركیب با توابعی نظیر SUM و AVERAGE و VLOOKUP، یكی از ابزارهای قدرتمند برای كار با داده‌های پویا محسوب می‌شود .

نكته‌ی حائز اهمیت، انتخاب صحیح نقطه‌ی شروع و تنظیم مقادیر ردیف و ستون برای رسیدن به داده‌های مطلوب است.به‌نظر شما این راهكارها چه‌قدر كاربردی هستند؟ شما هم تجربه‌ای مشابه دارید؟ نظرتان را با ما و دیگر خوانندگان در میان بگذارید .