• مطالب آموزشی
    • تمام مطالب آموزشی
    • Excel
    • Power BI
    • DAX
  • دوره های آموزشی
    • کلاس اکسل پیشرفته
    • کلاس داشبورد با Power BI
    • کلاس DAX و مدلسازی داده‎
    • Power BI Report Server
    • گالری تصاویر
  • پکیج های آموزشی
    • اکسل
    • Power BI
    • SQL
    • سبد خرید
  • معرفی
  • تماس
  • اینستاگرام
  ورود
گروه تحلیلگری
  • مطالب آموزشی
    • تمام مطالب آموزشی
    • Excel
    • Power BI
    • DAX
  • دوره های آموزشی
    • کلاس اکسل پیشرفته
    • کلاس داشبورد با Power BI
    • کلاس DAX و مدلسازی داده‎
    • Power BI Report Server
    • گالری تصاویر
  • پکیج های آموزشی
    • اکسل
    • Power BI
    • SQL
    • سبد خرید
  • معرفی
  • تماس
  • اینستاگرام
0
ثبت نام / ورود

وبلاگ

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

در طی این سال ها که اکسل تدریس می کنم (از سال ۱۳۹۰) بارها و بارها در کلاس ها و گروه ها و شبکه های اجتماعی این سوال از من پرسیده شده (بخصوص همان ابتدای درس 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» ذخیره کنید یا از آن یک «افزونه» بسازید.

برچسب ها: اکسل پیشرفتهماکرونویسی
درباره پوریا بغدادی

من مباحث BI را در دانشگاه تهران آموخته ام. مجری و مشاور سیستم های هوش تجاری (BI) هستم. آموزش را برای علاقه شخصی پی میگیرم.

نوشته های بیشتر از پوریا بغدادی
در تلگرام
کانال ما را دنبال کنید!
در اینستاگرام
ما را دنبال کنید!
مطالب زیر را حتما بخوانید
  • ایجاد فرمت سفارشی در اکسل

    2.08k بازدید

  • کتاب مرجع اکسل پیشرفته ۲۰۱۹

    2.05k بازدید

  • جستجوی چند شرطی در اکسل

    5.2k بازدید

  • مقایسه دو ستون در اکسل

    3.89k بازدید

  • کاربرد تابع offset در اکسل

    1.42k بازدید

  • کاربرد علامت دلار در اکسل

    3.95k بازدید

guest
تعداد ماههای سال به عدد
guest
21 نظر
قدیمی ها
جدید ها بیشترین رای
Inline Feedbacks
View all comments
مهدی عسکری
مهدی عسکری

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

2
پاسخ
پوریا بغدادی
پوریا بغدادی
نویسنده
در پاسخ به  مهدی عسکری

خواهش می کنم.

0
پاسخ
سعید آذری
سعید آذری

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

1
پاسخ
پوریا بغدادی
پوریا بغدادی
نویسنده
در پاسخ به  سعید آذری

ممنونم از لطف شما.

0
پاسخ
کیان
کیان

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

0
پاسخ
پوریا بغدادی
پوریا بغدادی
نویسنده
در پاسخ به  کیان

از فرمول هایی مثل Match باید در ترکیب با روش های فوق استفاده کرد. البته با ماکرو هم به راحتی میشه به همین ماکرو یک شرط اضافه کرد.

0
پاسخ
محمد
محمد
در پاسخ به  کیان

توی همون فرمول اولی که نوشته اند، توی پرانتز مربوط به تابع row، خودتون یک سلول متناظر دیگر اضافه کنید. مثلاٌ، *(e4:e14=”بد”)

0
پاسخ
مینا
مینا

سلام ممنون از اموزش خوبتون
سوالی داشتم، به فرض من یک شیت دارم که بی نهایت سطر داره و هر سطر یه امتیازی رو در یکی از سلول هاش داره، من میتونم کدی داشته باشم که به طور مثال وقتی امتیاز سطری به عدد مشخصی رسید، اون سطر به صورت خودکار در شیت دیگه ای در همون فایل کپی بشه؟ چون تا به حال کد نویسی انجام ندادم نتونستم سمپلی برای این مورد پیدا کنم

0
پاسخ
پوریا بغدادی
پوریا بغدادی
نویسنده
در پاسخ به  مینا

سلام و درود خدمت شما
البته شیت ها نمی توانند بی نهایت سطر داشته باشند، احتمالا منظور شما تعداد زیادی سطر هست. بله می توانید با ماکرو نویسی اینکار را انجام دهید.
با کمک رویدادهایی نظیر Selection_change یا نظایر آن می توانید تغییر کردن داده ها یا سلول فعال را برنامه نویسی کنید.

0
پاسخ
رضا
رضا

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

0
پاسخ
پوریا بغدادی
پوریا بغدادی
نویسنده
در پاسخ به  رضا

سلام
یعنی در کدام ستون غیر صفر؟

0
پاسخ
مهدی
مهدی

دمت گرم این همون چیزی بود که دنبالش بودم. فکر نمی کردم هرگز بتونم این مشکل رو حل کنم ولی شما کمک کردی دمت خیلی گرم

1
پاسخ
پوریا بغدادی
پوریا بغدادی
نویسنده
در پاسخ به  مهدی

خواهش می کنم. سلامت باشید.

0
پاسخ
مسعود
مسعود

سلام ، من از تابع LOOKUP در ورژن ۲۰۰۷ استفاده کردم ولی ارور میده ، علت چیه ؟

1
پاسخ
پوریا بغدادی
پوریا بغدادی
نویسنده
در پاسخ به  مسعود

به احتمال زیاد مشکل جای دیگری هست. تابع lookup قدیمی تر از این حرفهاست.

0
پاسخ
نجفی
نجفی

جناب بغدادی سلام
من به شدت به این موضوعی که شما اموزش دادید احتیاج دارم ولی نتونستم موضوع رو درست بفهمم
ایا امکانش هست با شما تماس گرفت و راهنمایی کنید

0
پاسخ
پوریا بغدادی
پوریا بغدادی
نویسنده
در پاسخ به  نجفی

سلام در ساعات اداری به شماره پشتیبانی در واتساپ پیام بفرستید.

0
پاسخ
محمد
محمد

خیلی خیلی ممنونم. مشکلم رو حل کردید.

0
پاسخ
ستاره
ستاره

سلام وقت بخیر
ممنونم خیلی عالی بود، خیلی دنبال راهش بودم، بالاخره حل شد، سپاس

0
پاسخ
صحرائی
صحرائی

سلام. مثل همیشه مالبتون پرمحتوی و مفید. خدا خیرتون بده

0
پاسخ
ساسان
ساسان

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

0
پاسخ
جستجو
جستجو برای:
دسته بندی ها
  • Charts
  • DAX
  • Excel
  • Power BI
  • Power Query
  • هوش تجاری
بسته های آموزشی
  • Excel
  • Power BI
  • SQL
درباره گروه تحلیلگری

معتقدیم آموزش شروع یک تعهد بلند مدت است. این را از مشتریان ما سوال کنید.

اطلاعات تماس
  • تهران، خیابان مطهری، ابتدای خیابان سنایی، کوچه بیست و یکم
  • 543 30 910 - 021
پیوندها
  • آموزش Power BI
  • آموزش SQL
  • آموزش اکسل
دسته بندی محصولات
Excel Power BI SQL
نماد الکترونیک
wpDiscuz

ورود

رمز عبور را فراموش کرده اید؟

هنوز عضو نشده اید؟ عضویت در سایت