تابع OFFSET بخش 2

آخرین به‌روزرسانی: 27 دی 1401

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

ویژگی Evaluate Formula (ارزیابی فرمول) در اکسل چگونه کار می‌کند؟

چنانچه هر بخش از فرمول درون‌سلولی را انتخاب کرده و بعد کلید F9 را بزنید، اکسل مقداری را که توسط آن بخش به دست می‌آید را به شما نشان می‌دهد. پس از فشاردادن کلید F9 می‌بایست کلید Esc یا خروج را بزنید وگرنه فرمول پاک می‌شود. این روش، رفع اشکال و درک فرمول‌های پیچیده را بسیار آسان می‌کند. مثلاً  بیایید دوباره به سراغ فایلی به نام Offsetcost.xlsx برویم، سلول E4 با فرمول =SUM(OFFSET(E10,0,0,E1,1))  هزینه کل فاز 1  را محاسبه می‌کند. اگر نشانگر ماوس را روی این بخش از فرمول OFFSET(E10,0,0,E1,1) برده و آن را انتخاب کرده و سپس کلیدF9 را فشار دهید عبارت: =SUM({135,120,58}) را خواهید دید که مشخص می‌کند بخش OFFSET این فرمول در سلول D4 از سلول‌های صحیح  یعنی (D10:D12) استفاده می‌کند.

روش دیگر برای دیدن اینکه فرمولی پیچیده چگونه عمل می‌کند آن است که از دستور Evaluate Formula (ارزیابی فرمول) استفاده کنیم. برای انجام این کاربر سلول E4‌کلیک کرده و بعد بر سربرگFormulas در روی ریبون نوارابزار کلیک کنید. در گروه Formula Auditing روی گزینه Evaluate Formula کلیک کنید (تصویر 7-22) روی دکمه Evaluate سه بار کلیک کنید و اکسل فرمول را قدم‌به‌قدم برای شما ساده می‌کند تا نتیجه نهایی آن را ببینید. پس از دو بار کلیک کردن برEvaluate فرمول به شکل =SUM({135,120,58}) ظاهر می‌شود بنابراین می‌فهمید که در سلول E4 سلول‌های فاز یک برای داروی شماره 2 را انتخاب نموده‌اید که درواقع همان چیزی است که در نظر داشته‌اید. اگر برای بار سوم روی Evaluate کلیک کنید  نتیجه نهایی را می‌بینید که 313 است.

تصویر 7-22 کادر محاوره‌ای Evaluate Formula

چگونه می‌توان فرمولی نوشت که همیشه آخرین عدد یک ستون را نشان دهد؟

اغلب داده‌های جدید را در کاربرگی دانلود می‌کنید. آیا می‌توان فرمولی نوشت که همیشه فروش آخرین ماه را نشان دهد؟ (فایلی بنام Mostrecent.xlsx و تصویر 8-22 را ببینید)

تصویر 7-22 کادر محاوره‌ای Evaluate Formula

چگونه می‌توان فرمولی نوشت که همیشه آخرین عدد یک ستون را نشان دهد؟

اغلب داده‌های جدید را در کاربرگی دانلود می‌کنید. آیا می‌توان فرمولی نوشت که همیشه فروش آخرین ماه را نشان دهد؟ (فایلی بنام Mostrecent.xlsx و تصویر 8-22 را ببینید)

تصویر 9-22 مثالی از محدوده‌های پویا (dynamic range)

در اینجا محدوده ارقام پویایی ایجاد کرده‌ایم که با اضافه‌کردن ردیف یا ستون داده‌ها به محدوده A1:D10 گسترش پیدا می‌کند. داده‌ها اکنون حاوی 9 ردیف و چهار ستون از داده‌ها می‌باشند. چه خوب می‌شد اگر امکان داشت که محدوده‌ای از نام ایجاد کنیم که به هنگام اضافه‌کردن نام افراد یا اطلاعات جدید به بانک داده به شکل خودکار دارای ردیف‌ها و یا ستون‌های بیشتری شود؟

برای ایجاد یک محدوده پویا بر سربرگ Formulas کلیک کنید و بعد در گروه Defind Names روی گزینه Name Manager کلیک کنید. سپس بر گزینه New Option کلیک کنید تا محدوده‌ای را همان‌طور که در تصویر 10-22 نشان‌داده‌شده مشخص کنید.

تصویر 10-22 ایجاد یک محدوده پویا

می‌توانید نام محدوده ِData  را که من ایجاد کرده‌ام را انتخاب کنید. این محدوده فرمول =OFFSET(data!$A$1,0,0, COUNTA(data!$A:$A),COUNTA(data!$1:$1)) را نشان می‌دهد. محدوده از گوشه سمت چپ بالایی یعنی سلول A1 آغاز می‌شود. پس از آن، صفر ردیف و ستون از سلول A1 دورتر می‌شویم. محدوده انتخاب شده دارای فرمول Number of rows=number of nanblank entries (تعداد ردیف‌ها برابر با تعداد ورودی‌های غیر خالی) در ستون A و فرمول Number of columns=number of entries (تعداد ستون‌ها برابر تعداد ورودی‌های غیر خالی)در ردیف 1 می‌باشد؛ بنابراین چنانچه نام اشخاص یا فیلدهای داده را وارد نمایید، فرمول به شکل خودکار جا باز می‌کند تا آنها را نیز شامل شود. در اینجا به علامت دلار ($) نیاز داریم تا اطمینان حاصل کنیم که چنانچه در کاربرگ پیمایش کنید، محدوده مشخص شده جای خود را تغییر ندهد.

برای امتحان کردن این مسئله فرمول=SUM(data) را در سلول G11 وارد می‌کنیم. در حال حاضر این فرمول تمامی عددهای محدوده A1:D9 را جمع زده و عدد 448.278دلار را به ما می‌دهد.

حالا به ردیف 11 نام Meredith را اضافه می‌کنیم، در سلول B11 حقوق 10000 دلار را اضافه کرد و در سلول E1 متغیری برای اشتباهات (کلمه Mistakes را اضافه کرده) می‌افزاییم و بالاخره در سلول E11 عدد 1000 را وارد می‌کنیم. حالا فرمول =SUM(data) عددهای 10000 و 1000 را به شکل خودکار اضافه کرده و عدد قبلی را به مقدار 459.278 دلار به‌روز می‌کند.

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

کاربرگی بنام Chartdynamicrange.xlsx (تصویر 11-22)  حاوی کالاهای فروخته شده از محصولات شرکت می‌باشد. همان‌طور که می‌بینید واحدهای فروخته شده با استفاده از نمودار تجمعی نمایش‌داده‌شده‌اند.

تصویر 11-22 استفاده از تابع OFFSET برای به‌روز کردن خودکار نمودار

کار را در ردیف 19 شروع می‌کنیم، داده‌های جدید فروش را دانلود و اضافه کرده‌ایم. آیا راه ساده‌ای برای اطمینان حاصل پیداکردن از اینکه نمودار به شکل خودکار با داده‌های جدید به‌روز می‌شود وجود دارد؟

کلید به‌روز کردن نمودار استفاده از تابع OFFSET‌ برای ایجاد یک محدوده پویای اسامی برای ستون ماه (Month) و ستون واحدهای فروخته (Units Sold) شده است. همان‌طور که داده‌های جدید را وارد می‌کنیم، محدوده پویای واحدهای فروخته شده به طرز خودکار تمامی داده‌های مربوط به فروش را مشمول می‌کند و محدوده پویای ماه‌ها نیز عدد هر ماه را وارد می‌نماید. بعد از ایجاد این محدوده‌ها می‌توانید نمودار را تغییر داده و محدوده داده‌های استفاده شده در هر نمودار را  با محدوده‌های پویا جایگزین نمایید. نمودار شما این‌گونه هر بار داده‌ها را وارد کنید به‌روز می‌شود.

برای آغاز کار در سربرگ Formulas در گروه Defined Names بر روی گزینه Define Name‌ کلیک کنید تا کادر محاوره‌ای New Name ظاهر شود. حالا با پرکردن کادر محاوره‌ای به شکلی که در تصویر 12-22 نشان‌داده‌ شده، محدوده‌ای بنام Units ایجاد کنید.

تصویر 12-22 ایجاد یک محدوده پویا برای کالاهای فروخته شده

واردکردن =OFFSET(‘dynamic range’!$C$3,0,0,COUNT(!$C:$C),1) در بخش Refers To از کادر محاوره‌ای محدوده‌ای یک ستون ایجاد می‌کند که از سلول C3 آغاز می‌شود و حاوی داده‌های اولین واحد فروخته شده است. این محدوده هر تعداد عدد که در ستون C  باشد و توسط این بخش از فرمول COUNT(‘dynamic range’!$C:$C) ‌به‌دست‌آمده است در خود جای می‌دهد. همان‌طور که داده‌ها در ستون C وارد می‌شود، داده‌های وارد شده به شکل خودکار در محدوده نامیده شده به Units اضافه می‌شود.

حالا یک محدوده پویا به نام Month برای ماه‌های وارد شده در ستون B ایجاد کنید. فرمول این محدوده در تصویر 13-22 نشان‌داده‌شده است.

تصویر 13-22 ‌استفاده از فرمول برای تعیین یک محدوده پویا به نام Month

حالا به نمودار رفته و روی هر نقطه‌ای از آن کلیک کنید. در قسمت formula فرمول

=SERIES(‘dynamic range’!$C$2,’dynamic range’ !$B$3:$B$18, ‘dynamic range’!$C$3:$C$18,1)

را می‌بینید. این فرمول  نسخه اکسل از داده‌هایی است که به شکل اولیه‌ای برای تنظیم نمودار مورداستفاده قرار می‌گیرد. محدوده‌های $B$3:$B$18 and $C$3:$C18 را با نام‌های محدوده پویا به شرح زیر تغییر دهید:

SERIES(‘dynamicrange’!$C$2,Chartdynamicrange.xlsx!Month,Chartdynamicrange.xlsx!Units,1)

البته اگر فضای خالی در بالای هریک از داده‌های جدید لیست شده باشد این روش کارایی نخواهد داشت. داده‌های جدیدی در سلول‌های B19:C19‌ و سلول‌های زیر آن وارد کنید و می‌بینید که آنها به نمودار اضافه شده‌اند.

یادآوری

ویژگی جدول (Table) اکسل راه را برای همکاری خودکار فرمول‌ها و نمودارها با داده‌های جدید بسیار آسان کرده است. فصل 26 به نام جداول برای بحث‌های بیشتر در این مورد را مشاهده کنید.

مسئله‌های این فصل:

فایلی به نام Ch21p1.xlsx حاوی داده‌هایی درباره فروش واحدهایی از 11 محصول در طول سال‌های 1999 الی 2003 می‌باشد. فرمولی بنویسید که با استفاده از توابع Match ‌و Offset  میزان فروش محصولی خاص را در طول سال مشخصی تعیین کند. آیا راه دیگری برای حل این مسئله بدون استفاده از توابع ذکر شده به ذهنتان می‌رسد؟

قانون عام میانگین متحرک تجارت که همواره توصیه می‌شود آن است که سهام را وقتی قیمتش بالاتر از میانگین ماه D گذشته می‌رود خریداری کرده و آن را وقتی‌که قیمتش از متوسط قیمت ماه D گذشته پایین‌تر رفته به فروش رسانیم. در فصل 12 به نام توابع IF، IFERROR،IFS، CHOOSE‌وSWITCH به شما نشان دادیدم که در صورت D=15، این قانون تجاری به میزان قابل‌توجهی در شاخص اس‌اندپی 500 عملکرد بهتری پیدا خواهد کرد. با ترکیب کردن جدولی یک‌سویه با تابع OFFSET مقداری از D را که حداکثر سود تجاری (با حذف هزینه‌های مبادلات)  را به دست می‌آورد پیدا کنید. می‌توانید داده‌های مرتبط را در فایلی بنام Matradingrule.xlsx مشاهده کنید.

یک قانون عام میانگین متحرک تجارت که همواره توصیه می‌شود آن است که سهام را وقتی قیمتش بالاتر از میانگین ماه B گذشته می‌رود خریداری کرده و آن را وقتی‌که قیمتش از متوسط قیمت ماه S گذشته پایین‌تر رفته به فروش رسانیم.  در فصل 12 نشان دادیم که چنانچه BبرابرS و هر دو آنها برابر 15 باشند (B=S=15)، این قانون تجاری به میزان قابل‌توجهی از شاخص اس‌اندپی 500 عملکرد بهتری پیدا خواهد کرد. با ترکیب جدول یک وجهی داده‌ها با تابع OFFSET مقادیری از B و S را به دست آورید که سود تجاری حداکثر (با حذف هزینه‌های مبادله) را به دست خواهند آورد. داده‌های مرتبط با این مسئله را در فایلی بنام Matradingrule.xlsx خواهید یافت.

فایلی به نام Lagged.xlsx حاوی داده‌هایی درباره تعداد تبلیغات مجلات است که هر شصت ماه توسط ارتش آمریکا برای جلب نیروی نظامی منتشر شده است. هر ماه، تعدادK تبلیغات عقب‌افتاده با تعداد تبلیغات K منتشر شده ماه پیش تعیین می‌شود. قرار است برای ماه‌های 7 الی 60  مقادیر تبلیغات عقب‌افتاده 1 ماه، 2 ماه  تا 6 ماه را مشخص نمایید. از تابع OFFSET برای محاسبه مؤثر مقادیر عقب‌افتاده استفاده کنید.

فایلی به نام Verizondata.xlsx فروش چهار مدل تلفن ورایزن در پنج منطقه را به ما می‌دهد. روش  بسیار مؤثری برای درج هریک از این ترکیب20 گانه منطقه/مدل، نام مناطق، نوع تلفن و فروش هر تلفن در یک ردیف را پیدا کنید.

این مسئله کمی مشکل است. فایلی به نام Agingdata.xlsx تعداد درخواست‌های بیمه که هر روز دریافت می‌شود و تعداد کارمندان شرکت بیمه در دسترس را نشان می‌دهد. یک کارمند روزانه می‌تواند 50 درخواست را مورد بررسی قرار دهد. کارمندان ابتدا درخواست‌های قدیمی‌تر را بررسی و به جریان می‌اندازند. در فایلی بنام s22_6.xlsx، سلول‌های H6:AL6 حاوی تعداد درخواست‌هایی هستند که از اول ژانویه در سیستم بوده‌اند، پیش از آنکه درخواست‌های جدیدی وارد شود، کاربرگی تنظیم کنید که میزان قدمت درخواست‌ها را بررسی و دنبال نماید. این یعنی در هر روز چند درخواست یک‌روزه، دوروزه …  30 روزه و بیش از سی روز قدمت در سیستم خواهند بود؟

هر ردیف از فایلی به نام DVDsales.xlsx حاوی فروش ماهیانه دی‌وی‌دی  است. فرمولی بنویسید که فروش هر دی‌وی‌دی در طول اولین ماه ورودش به بازار را مشخص نماید.

برای به‌دست‌آوردن امتیاز طرف ضعیف در بازی گلف، میانگین ده تا از ضعیف‌ترین امتیازهای بازیکن در 20 دور بازی را حساب می‌کنید، آنگاه آن را از عدد هشتاد کم کرده و عدد حاصله را به نزدیک‌ترین عدد صحیح گرد می‌کنید؛ بنابراین اگر میانگین ده تا از ضعیف‌ترین امتیازهای 20 دور بازی 86.4 باشد، امتیاز طرف ضعیف 6 است. فایلی به نام Golfdata.xlsx حاوی امتیازات بازی گلف است. از ردیف 24 شروع کرده و امتیاز طرف ضعیف هر دور بازی را محاسبه کنید. فرض کنید اگر دهمین امتیاز عالی هر بیست دور بیشتر از یکی باشد همه دورهای شامل آن امتیاز در محاسبه امتیاز طرف ضعیف اعمال خواهند شد. توجه کنید در اکسل تابع =ROUND(x.0)  x را به نزدیک‌ترین عدد صحیحی گرد می‌کند.

هر ردیف از فایلی به نام Carsumdata.xlsx حاوی داده‌های فروش یک وسیله نقلیه (اتومبیل، قطار یا هواپیما) از ژانویه تا ژوئیه است. فرض کنید ماه و محصول را در کاربرگ وارد کرده‌اید. فرمولی بنویسد که به شما فروش کل آن محصول در ماه خاصی را بدهد.

فایلی بنام Verizon.xlsx حاوی بازده ماهیانه سهام شرکت ورایزن می‌باشد. از تابع OFFSET استفاده کنید تا تمامی بازده ماه ژانویه، بازده ماه فوریه و به همین شکل را در ستون‌های جداگانه‌ای استخراج کنید.

فایلی به نام Casesensitive.xlsx حاوی کدها و قیمت‌های محصولاتی می‌باشد. توجه کنید که کدهای محصولات به بزرگی و کوچکی حروف حساس هستند. مثلاً DAG32 با محصولی به نام dag32 فرق دارد. فرمولی بنویسید که با واردکردن کد شناسه هر محصول قیمت آن را ارائه دهد. اشاره: احتمالاً نیاز به استفاده از تابع EXACT پیدا می‌کنید. فرمول EXACT(cell1,cell2) چنانچه Cell1  و Cell2 دارای محتویات یکسانی باشند عبارت True را برمی‌گرداند. تابع EXACT حروف کوچک و بزرگ را متمایز می‌کند.

فایلی به نامReversed.xlsx حاوی ستونی از عددها است. از تابع OFFSET استفاده کنید تا ترتیب عددها را برعکس کنید تا عددهای ته ستون در بالا ظاهر شوند.

فایلی به نام Yeartodate.xlsx حاوی فروش ماهیانه شرکتی از ژانویه سال 2008 تا آگوست سال 2013 است. فرمولی بنویسید که برای سال و ماهی خاص از آن سال فروش تجمعی سالیانه را تا آن تاریخ محاسبه کند. مثلاً چنانچه رقم 6 را برای ماه و 2010 را برای سال وارد کنید، فرمول می‌بایست کل فروش  دوره ژانویه تا ژوئن سال 2010 را محاسبه کند.

نشان دهید چگونه می‌توانید نموداری از فروش ماهیانه رسم کنید که همیشه فروش آخرین شش ماه را نشان دهد.

فایلی به نام Transactiondata.xlsx حاوی مبادلات فروش در شعبه‌های A،B،C، D و E از یک شرکت داروسازی می‌باشد. از تابع OFFSET‌استفاده کنید تا این داده‌ها را به شکلی تغییر دهید که فروش هر شعبه در یک ردیف جداگانه نمایش داده شود.