یافتن آخرین تکرار یک مقدار با فرمول و با ماکرو در اکسل
در طی این سال ها که اکسل تدریس می کنم (از سال ۱۳۹۰) بارها و بارها در کلاس ها و گروه ها و شبکه های اجتماعی این سوال از من پرسیده شده (بخصوص همان ابتدای درس VLookup) که آیا میتوان آخرین مقدار یافت شده مورد جستجو را مشخص کرد؟ یا میتوان تکرارهای بعدی مقدار جستجو را مشخص کرد؟ یا سوالاتی شبیه به همین ها. در این آموزش یاد خواهید گرفت که چگونه آخرین رخداد یک آیتم در یک لیستی از فرمول های مورد استفاده در اکسل را پیدا کنید.
اخیرا، من در حال تنظیم برنامه کاری برای یک جلسه بودم. من جدولی در اکسل داشتم که در آن، لیست افراد و تاریخ هایی که آنها به عنوان مدیر جلسه بودند ثبت شده بود. از آنجایی که تکرار در لیست وجود داشت (که به این معنی است که فرد چندین بار مدیر جلسه بوده است)، لازم بود تا من آخرین باری که فرد به عنوان « مدیر جلسه » عمل کرده را بدانم. به این دلیل من مجبور بودم اطمینان یابم که کسی که به تازگی ارائه داشته، مجددا تعیین نشود.
بنابراین تصمیم گرفتم از توابع اکسل استفاده کنم تا این کار انجام شود. در زیر نتیجه نهایی دیده می شود که در آن می توانیم یک نام از drop-down (لیست کشویی) انتخاب کنیم و تاریخ آخرین رخداد آن نام در لیست نمایش داده شود:
اگر درک خوبی از توابع اکسل داشته باشید، می دانید که هیچ تابع اکسلی وجود ندارد که بتواند این کار را انجام دهد. اما شما در اکسل پیشرفته قرار دارید و در این قسمت ما اتفاقات جادویی را ایجاد می‌کنیم. در این آموزش، سه راه برای انجام این کار به شما نشان داده خواهد شد:
- پیدا کردن آخرین تکرار – با استفاده از تابع MAX
- پیدا کردن آخرین تکرار – با استفاده از تابع LOOKUP
- پیدا کردن آخرین تکرار – با استفاده از ماکرونویسی (Custom VBA)
پیدا کردن آخرین تکرار – با استفاده از تابع MAX
این فرمول اکسلی است که آخرین مقدار یک لیست را بازمی گرداند:
چگونگی عملکرد این فرمول به شرح زیر است:
- تابع MAX برای پیدا کردن شماره سطر آخرین نام تطبیقی استفاده شده است. به عنوان مثال، اگر نام «بیگی» باشد، از آنجا که «بیگی» در ردیف ۴ است، ۴ برگردانده می شود. به دلیل اینکه لیست ما از سطر چهارم شروع می شود، ۳ محاسبه شده است. بنابراین موقعیت آخرین رخداد «بیگی» در لیست ما ۷ است.
- برای اینکه مجبور نباشید از Control + Shift + Enter استفاده کنید، SUMPRODUCT استفاده شده است زیرا SUMPRODUCT می تواند فرمول های آرایه را مدیریت کند.
- تابع INDEX در اینجا برای پیدا کردن تاریخ آخرین نام تطبیقی استفاده شده است.
یافتن آخرین رخداد – با استفاده از تابع 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 را به شما یاد خواهم داد، اما ابتدا به شما چگونگی عملکرد این تابع را نشان می‌دهم.
با توجه به فرمول زیر نتیجه اینچنین است:
این فرمول سه آرگومان می گیرد:
- مقدار 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 بروید.
۲- روی Visual Basic کلیک کنید. ویرایشگر VB در backend باز خواهد شد.
۳- در قسمت Project Explorer در ویرایشگر VB، بر روی هر شی برای فایلی که در آن می خواهید کد را وارد کنید، راست کلیک کنید. اگر Project Explorer را مشاهده نمی کنید، به تب View بروید و روی Project Explorer کلیک کنید.
۴- به Insert بروید و بر روی Module کلیک کنید. این کار یک شیء Module را برای workbook شما وارد می‌کند.
۵- کد را در پنجره Module کپی و paste کنید.
در حال حاضر این فرمول در تمام کاربرگ های workbook در دسترس خواهد بود.
توجه داشته باشید که شما باید Workbook را در فرمت XLSM ذخیره کنید، زیرا یک ماکرو در آن وجود دارد. همچنین اگر میخواهید این فرمول در تمام Workbook هایی که شما استفاده می کنید در دسترس باشد، میتوانید آن را بصورت «Macro Personal Workbook» ذخیره کنید یا از آن یک «افزونه» بسازید.
درود بر شما جناب بغدادی عزیز ، ممنون از آموزش کاربردی و بسیار عالیتون
خواهش می کنم.
سلام من بسیار از آموزش های شما استفاده کردم و واقعا لذت بردم لحن زیبا و مختصر ودر عین حال جامع شما قابل تحسین است بشما حسودی میکنم چون سالهاست که با اکسل کار میکنم هنوز تبحر شما را ندارم بسیار سپاسگزارم و آروزی توفیق هرچه بیشتر برای شما را آرزومندم
ممنونم از لطف شما.
سلام
لطفا بفرمایید در صورتی که شرطی دیگرید داشته باشیم به عنوان مثال جلسه خوب بود وبد بود .. میتوان از این فرمول استفاده کرد
با تشکر از آموزش خیلی خوب شما
از فرمول هایی مثل Match باید در ترکیب با روش های فوق استفاده کرد. البته با ماکرو هم به راحتی میشه به همین ماکرو یک شرط اضافه کرد.
توی همون فرمول اولی که نوشته اند، توی پرانتز مربوط به تابع row، خودتون یک سلول متناظر دیگر اضافه کنید. مثلاٌ، *(e4:e14=”بد”)
سلام ممنون از اموزش خوبتون
سوالی داشتم، به فرض من یک شیت دارم که بی نهایت سطر داره و هر سطر یه امتیازی رو در یکی از سلول هاش داره، من میتونم کدی داشته باشم که به طور مثال وقتی امتیاز سطری به عدد مشخصی رسید، اون سطر به صورت خودکار در شیت دیگه ای در همون فایل کپی بشه؟ چون تا به حال کد نویسی انجام ندادم نتونستم سمپلی برای این مورد پیدا کنم
سلام و درود خدمت شما
البته شیت ها نمی توانند بی نهایت سطر داشته باشند، احتمالا منظور شما تعداد زیادی سطر هست. بله می توانید با ماکرو نویسی اینکار را انجام دهید.
با کمک رویدادهایی نظیر Selection_change یا نظایر آن می توانید تغییر کردن داده ها یا سلول فعال را برنامه نویسی کنید.
مرسی از آموزش, ولی آیا امکانش هست کد نویسی تابع فوق الذکر را به نحوی تغییر داد که آخرین مقدار غیر صفر را برگرداند
سلام
یعنی در کدام ستون غیر صفر؟
دمت گرم این همون چیزی بود که دنبالش بودم. فکر نمی کردم هرگز بتونم این مشکل رو حل کنم ولی شما کمک کردی دمت خیلی گرم
خواهش می کنم. سلامت باشید.
سلام ، من از تابع LOOKUP در ورژن ۲۰۰۷ استفاده کردم ولی ارور میده ، علت چیه ؟
به احتمال زیاد مشکل جای دیگری هست. تابع lookup قدیمی تر از این حرفهاست.
جناب بغدادی سلام
من به شدت به این موضوعی که شما اموزش دادید احتیاج دارم ولی نتونستم موضوع رو درست بفهمم
ایا امکانش هست با شما تماس گرفت و راهنمایی کنید
سلام در ساعات اداری به شماره پشتیبانی در واتساپ پیام بفرستید.
خیلی خیلی ممنونم. مشکلم رو حل کردید.
سلام وقت بخیر
ممنونم خیلی عالی بود، خیلی دنبال راهش بودم، بالاخره حل شد، سپاس
سلام. مثل همیشه مالبتون پرمحتوی و مفید. خدا خیرتون بده
کمترین کار گذاشتن کامنت هستش در برابر این پست کاربردی واقعا ممنون بابت معرفی این روش چون مدت زیادی درگیر پیدا کردن آخرین داده تکراری بودم و موفق نمیشدم خودم حل کنم