ممکن است مواقعی پیش بیاید که در حال جمع آوری یک ورک بوک هستید و بخواهید خلاصه ای از یک کاربرگ و اطلاعات جزئی را در کاربرگ دیگر نگه دارید. به عنوان مثال، فرض کنید Sheet1 کاربرگ خلاصه شما است و شما اطلاعات دقیقی برای حساب های بانکی در Sheet2 دارید. در نگاه کردن به اطلاعات جزئی، شما در ستون A تاریخ دارید و در ستون های B، C و D موجودی حساب های مختلف دارید. بنابراین، اطلاعات تفصیلی جدولی است که پیشرفت جاری مانده های بانکی را در تاریخ های مختلف نشان می دهد.
در کنار هم قرار دادن اطلاعات خلاصه خود در Sheet1، متوجه می شوید که باید به آخرین ارقام در ستون های B، C و D اشاره کنید. این ارقام نشان دهنده آخرین موجودی ها هستند و بنابراین برای خلاصه شما عالی هستند. چگونه آن را انجام دهید؟ به خصوص هنگامی که به مرور زمان به افزودن اطلاعات به کاربرگ جزئیات خود ادامه می دهید؟
در واقع، چندین راه برای نزدیک شدن به مشکل وجود دارد. (معمولا چندین راه برای حل هر مشکل اکسل وجود دارد.) یکی از راه ها استفاده از تابع VLOOKUP است. در نقطه ای از خلاصه که آخرین موجودی ستون B جزئیات را می خواهید (Sheet2)، فرمول زیر را قرار دهید:
=VLOOKUP(MAX(Sheet2!$A:$A),Sheet2!$A:$D,2)
برای تغییر ارجاعات دو مانده حساب دیگر، کافی است آخرین عدد (2) را به 3 (برای حساب در ستون C) یا 4 (برای حساب در ستون D) تغییر دهید. این تابع به این دلیل کار می کند که حداکثر مقدار را در ستون A که حاوی تاریخ است، جستجو می کند. سپس در جدول داده ها (Sheet2!$A:$D) نگاه می کند و افست مناسب برای ستون مورد نظر را پیدا می کند.
این روش به خوبی کار می کند، مشروط بر اینکه هیچ تاریخی در ستون A وجود نداشته باشد که از آخرین موجودی وارد شده گذشته باشد. اگر وجود داشته باشد، مقادیر بازگشتی همیشه نادرست خواهند بود.
راه دیگر برای نزدیک شدن به مشکل استفاده از تابع INDEX در ارتباط با COUNT یا COUNTA است. اگر ستون های جزئیات حاوی هیچ متنی نیستند (حتی در سرفصل های ستون)، از تابع COUNT استفاده می کنید. اگر متنی وجود دارد، COUNTA ترجیح داده می شود. در نقطه ای که می خواهید آخرین موجودی را از ستون B جزئیات وارد کنید، از فرمول زیر استفاده می کنید:
=INDEX(Sheet2!B:B,COUNTA(Sheet2!B:B))
به جدول نگاه می کند، تعداد سلول های غیر خالی در ستون B را تعیین می کند و سپس شکل را از آخرین سلول غیر خالی می کشد. برای تطبیق فرمول برای ستون های C و D، به سادگی مراجع B را به C یا D مناسب تغییر دهید.
راه دیگر برای مقابله با مشکل استفاده از تابع OFFSET است، مانند شکل زیر:
=OFFSET(Sheet2!B1,COUNTA(Sheet2!B:B)-1,0)
این تابع مقدار یک سلول را از یک سلول مرجع پایه برمی گرداند. در این مورد، سلول پایه Sheet2!B1 است. تابع COUNTA برای تعیین تعداد ردیف هایی که باید از پایه خارج شوند استفاده می شود و 0 مشخص می کند که افست باید در همان ستون مرجع پایه باشد. برای تغییر فرمول ستون های C و D، به سادگی تمام مراجع را به B به C یا D تغییر دهید.
