یافتن آخرین تکرار یک مقدار با فرمول و با ماکرو در اکسل

در طی این سال ها که اکسل تدریس می کنم (از سال ۱۳۹۰) بارها و بارها در کلاس ها و گروه ها و شبکه های اجتماعی این سوال از من پرسیده شده (بخصوص همان ابتدای درس VLookup) که آیا میتوان آخرین مقدار یافت شده مورد جستجو را مشخص کرد؟ یا میتوان تکرارهای بعدی مقدار جستجو را مشخص کرد؟ یا سوالاتی شبیه به همین ها. در این آموزش یاد خواهید گرفت که چگونه آخرین رخداد یک آیتم در یک لیستی از فرمول های مورد استفاده در اکسل را پیدا کنید.

اخیرا، من در حال تنظیم برنامه کاری برای یک جلسه بودم. من جدولی در اکسل داشتم که در آن، لیست افراد و تاریخ هایی که آنها به عنوان مدیر جلسه بودند ثبت شده بود. از آنجایی که تکرار در لیست وجود داشت (که به این معنی است که فرد چندین بار مدیر جلسه بوده است)، لازم بود تا من آخرین باری که فرد به عنوان « مدیر جلسه » عمل کرده را بدانم. به  این دلیل من مجبور بودم اطمینان یابم که کسی که به تازگی ارائه داشته، مجددا تعیین نشود.

بنابراین تصمیم گرفتم از توابع اکسل استفاده کنم تا این کار انجام شود. در زیر نتیجه نهایی دیده می شود که در آن می توانیم یک نام از drop-down (لیست کشویی) انتخاب کنیم و تاریخ آخرین رخداد آن نام در لیست نمایش داده شود:

یافتن آخرین تکرار یک مقدار با فرمول Lookup در اکسل

اگر درک خوبی از توابع اکسل داشته باشید، می دانید که هیچ تابع اکسلی وجود ندارد که بتواند این کار را انجام دهد. اما شما در اکسل پیشرفته قرار دارید و در این قسمت ما اتفاقات جادویی را ایجاد می‌کنیم. در این آموزش، سه راه برای انجام این کار به شما نشان داده خواهد شد:

  1. پیدا کردن آخرین تکرار – با استفاده از تابع MAX
  2. پیدا کردن آخرین تکرار – با استفاده از تابع LOOKUP
  3. پیدا کردن آخرین تکرار – با استفاده از ماکرونویسی (Custom VBA)

پیدا کردن آخرین تکرار – با استفاده از تابع MAX

این فرمول اکسلی است که آخرین مقدار یک لیست را بازمی گرداند:

=INDEX($G$4:$G$14,SUMPRODUCT(MAX(ROW($F$4:$F$14)*($C$3=$F$4:$F$14))-3))

چگونگی عملکرد این فرمول به شرح زیر است:

  • تابع MAX برای پیدا کردن شماره سطر آخرین نام تطبیقی استفاده شده است. به عنوان مثال، اگر نام «بیگی» باشد، از آنجا که «بیگی» در ردیف ۴ است، ۴ برگردانده می شود. به دلیل اینکه لیست ما از سطر چهارم شروع می شود، ۳ محاسبه شده است. بنابراین موقعیت آخرین رخداد «بیگی» در لیست ما ۷ است.
  • برای اینکه مجبور نباشید از Control + Shift + Enter استفاده کنید، SUMPRODUCT استفاده شده است زیرا SUMPRODUCT می تواند فرمول های آرایه را مدیریت کند.
  • تابع INDEX در اینجا برای پیدا کردن تاریخ آخرین نام تطبیقی استفاده شده است.

یافتن آخرین رخداد – با استفاده از تابع LOOKUP

فرمول دیگری برای انجام همین کار  وجود دارد:

=LOOKUP(2,1/(F4:F14=C3),G4:G14)

یافتن آخرین تکرار یک مقدار با فرمول Lookup در اکسل

چگونگی عملکرد این فرمول شامل موارد زیر است:

  • مقدار جستجو ۲ است (در ادامه مطلب علت را متوجه خواهید شد.)
  • محدوده فرمول یک تقسیم بر ($ F $ 4: $ F $ 14 = C3) است – زمانی که نام تطبیقی را پیدا کند مقدار ۱ را برمی‌گرداند. در غیر این صورت error برمی گرداند. بنابراین شما یک آرایه دریافت می‌کنید. به عنوان مثال، اگر مقدار Lookup نام «بیگی» باشد آنگاه آرایه ای به صورت زیر بدون اینکه ببینیم تولید خواهد شد:

{#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}

  • آرگومان سوم محدوده ای از مقادیری است که با توجه به تاریخ موجود در آنها نتیجه را برای ما مشخص می‌نماید.

به این دلیل  از این فرمول استفاده می‌کنیم که تابع LOOKUP از تکنیک  تطبیق تقریبی استفاده می کند. به این  معنی که اگر مقدار تطبیق دقیق را پیدا کند، آن را برمی گرداند، اما اگر نتواند، کل آرایه را تا انتها اسکن خواهد کرد و بزرگترین مقدار بعدی که کمتر از مقدار جستجو است را برمی گرداند.

در این مورد عدد ۲ مقدار جستجو است و در آرایه ما فقط مقادیر ۱ یا error  را دریافت خواهیم کرد. بنابراین ابن تابع تمام آرایه را اسکن می کند و موقعیت آخرین ۱ را برمی گرداند – که مقدار آخرین تطبیق از  یک نام است.

یافتن آخرین رخداد – با استفاده از ماکرونویسی (VBA)

اجازه دهید به شما راه دیگری برای انجام این کار نشان دهم. ما می توانیم یک تابع Custom (تابع User Defined هم نامیده می شود.) با استفاده از VBA ایجاد کنیم. مزیت ایجاد یک تابع Custom این است که استفاده از آن آسان است. شما لازم نیست نگران ایجاد یک فرمول پیچیده باشید، زیرا بیشتر کارها در backend (پشت پرده) تابع VBA انجام می‌شود. من یک فرمول ساده (که بسیار شبیه فرمول VLOOKUP است) ایجاد کرده ام.

برای ایجاد یک تابع Custom، شما باید کد VBA را در ویرایشگر VB داشته باشید. من کد و مراحل لازم برای  قرار دادن در ویرایشگر VB را به شما یاد خواهم داد، اما ابتدا به شما چگونگی عملکرد این تابع را نشان می‌دهم.

با توجه به فرمول زیر نتیجه اینچنین است:

=LastItemLookup($C$3,$F$2:$G$14,2)

این فرمول سه آرگومان می گیرد:

  • مقدار Lookup (مقدار آن نام در سلول D3 می باشد)
  • محدوده Lookup (محدوده نام و تاریخ است – A2: B14)
  • شماره ستون (شماره ستونی است که ما از آن نتیجه را می‌خواهیم)

اولین باری  که فرمول را ایجاد کردید و کد را در ویرایشگر VB قرار دادید ، می توانید آن را همانند سایر توابع معمولی کاربرگ های اکسل استفاده کنید.

این کد برای این فرمول است:


Function LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)


 Dim i As Long


  For i = LookupRange.Columns(1).Cells.Count To 1 Step -1


   If Lookupvalue = LookupRange.Cells(i, 1) Then


   LastItemLookup = LookupRange.Cells(i, ColumnNumber)


   Exit Function


  End If


 Next i


End Function

این گام ها برای قرار دادن کد در ویرایشگر VB است:

۱- به تب Developer بروید.

یافتن آخرین تکرار یک مقدار با فرمول Lookup در اکسل۲- روی Visual Basic کلیک کنید. ویرایشگر VB در backend باز خواهد شد.

۳- در قسمت Project Explorer در ویرایشگر VB، بر روی هر شی برای فایلی که در آن می خواهید کد را وارد کنید، راست کلیک کنید. اگر Project Explorer را مشاهده نمی کنید، به تب View بروید و روی Project Explorer کلیک کنید.

۴- به Insert بروید و بر روی Module کلیک کنید. این کار یک شیء Module را برای workbook شما وارد می‌کند.

یافتن آخرین تکرار یک مقدار با فرمول Lookup در اکسل

۵- کد را در پنجره Module کپی و paste کنید.

یافتن آخرین تکرار یک مقدار با فرمول Lookup در اکسل

در حال حاضر این فرمول در تمام کاربرگ های workbook در دسترس خواهد بود.

توجه داشته باشید که شما باید Workbook را در فرمت XLSM ذخیره کنید، زیرا یک ماکرو در آن وجود دارد. همچنین اگر میخواهید این فرمول در تمام Workbook هایی که شما استفاده می کنید در دسترس باشد، میتوانید آن را بصورت «Macro Personal Workbook» ذخیره کنید یا از آن یک «افزونه» بسازید.

 

 

پوریا بغدادی

مباحث هوش تجاری را در دانشگاه تهران زیر نظر اساتید خبره آموختم، تحت تاثیر تحول دیجیتال هستم و عاشق آموزش دادن. تمام وجودم را در کلاس صرف یاددادن می کنم. مشاوره و انجام پروژه را نیز در کنار آموزش بصورت جدی پی میگیرم.

مطالب مرتبط

4
دیدگاه بگذارید

avatar
2 Comment threads
2 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
3 Comment authors
سعید آذریپوریا بغدادیمهدی عسکری Recent comment authors
  عضویت  
جدید ها قدیمی ها بیشترین رای
به دوستتان خبر دهید.
مهدی عسکری
مهمان
مهدی عسکری

درود بر شما جناب بغدادی عزیز ، ممنون از آموزش کاربردی و بسیار عالیتون

سعید آذری
مهمان
سعید آذری

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