تمرین ۲۴: کد مشتری و نمایش مبلغ آخرین خرید مشتری با index و Math

این یک مسئله کاربردی و پیشرفته اکسل است که در آن از ترکیب توابع INDEX و MATCH (به صورت آرایه‌ای) برای پیدا کردن آخرین رکورد ثبت شده برای یک مشتری خاص استفاده می‌شود.

معمولاً توابع جستجو مثل VLOOKUP اولین مقدار پیدا شده را برمی‌گردانند، اما در این مسئله ما به دنبال “آخرین” خرید (بر اساس ترتیب ورود داده‌ها) هستیم.

---

📝 صورت مسئله

فرض کنید شما یک جدول از تراکنش‌های فروش دارید که به ترتیب زمان ثبت شده‌اند. مشتریان شما چندین بار خرید کرده‌اند و شما می‌خواهید با وارد کردن کد مشتری در یک سلول، مبلغ آخرین خرید او را ببینید.

جدول داده‌ها (محدوده A1 تا C6)

ردیف	A (تاریخ)	B (کد مشتری)	C (مبلغ خرید)
1	Date	Customer ID	Amount
2	2023/10/01	C-101	1,000,000
3	2023/10/02	C-102	500,000
4	2023/10/05	C-101	1,200,000
5	2023/10/10	C-103	750,000
6	2023/10/12	C-101	2,500,000

• هدف: اگر ما کد C-101 را جستجو کردیم، فرمول باید مبلغ 2,500,000 (ردیف 6) را برگرداند، نه اولین خرید (ردیف 2) را.

---

💡 راه حل و فرمول

فرض کنید:

کد مشتری مورد نظر را در سلول E2 وارد کرده‌اید (مثلاً C-101).

می‌خواهید نتیجه در سلول F2 نمایش داده شود.

از فرمول آرایه‌ای زیر استفاده می‌کنیم:

=INDEX(C2:C6, MATCH(2, 1/(B2:B6=E2)))

(نکته: در نسخه‌های قدیمی اکسل، بعد از نوشتن این فرمول باید کلیدهای Ctrl + Shift + Enter را بزنید. در اکسل 2021 و 365 این کار لازم نیست.)

---

🔍 تحلیل فرمول (چرا این کار می‌کند؟)

این یکی از تکنیک‌های حرفه‌ای اکسل است:

1. (B2:B6=E2): این قسمت چک می‌کند کدام سلول‌ها در ستون B برابر با کد مشتری ما (C-101) هستند. نتیجه یک آرایه از TRUE و FALSE است:
   {TRUE; FALSE; TRUE; FALSE; TRUE}

2. 1/(...): وقتی عدد 1 را بر این آرایه تقسیم می‌کنیم:

   • 1 تقسیم بر TRUE (که 1 است) می‌شود 1.

   • 1 تقسیم بر FALSE (که 0 است) می‌شود خطای #DIV/0!.

   • نتیجه آرایه جدید: {1; #DIV/0!; 1; #DIV/0!; 1}

3. MATCH(2, ...): حالا تابع MATCH به دنبال عدد 2 در این آرایه می‌گردد. چون عدد 2 هرگز در آرایه‌ی ما (که فقط شامل 1 و خطا است) پیدا نمی‌شود، و چون آرگومان سوم MATCH را خالی گذاشته‌ایم (حالت تطبیق تقریبی)، اکسل آخرین عدد موجود در آرایه را که کوچکتر یا مساوی 2 باشد انتخاب می‌کند.

   • این کار باعث می‌شود تابع روی آخرین عدد 1 (که نشان‌دهنده آخرین باری است که مشتری خرید کرده) قفل کند.

   • در مثال ما، این سومین “1” است که مربوط به ردیف 6 (آخرین خرید C-101) می‌باشد.

4. INDEX(C2:C6, ...): در نهایت، تابع INDEX بر اساس موقعیتی که MATCH پیدا کرده (موقعیت 5م در محدوده)، مقدار متناظر در ستون مبلغ (C) را برمی‌گرداند.