[لیست آموزش توابع]
وقتی بین دو تا جدول گیر میافتی! (تکنیک جستجوی زنجیرهای در اکسل)
خیلی وقتا توی گزارشهای مالی، فروش یا اداری با وضعیتی مواجه میشیم که دادهها توی یک جدول جمع نشدن و به صورت سلسلهمراتبی پخش هستن.
صورت مسئله و چالش واقعی:
فرض کنید دو تا جدول داریم:۱. جدول اول (ستون A و B): نام «شعبه» رو به «منطقه» وصل کرده.
۲. جدول دوم (ستون D و E): «منطقه» رو به «نرخ تخفیف» وصل کرده.
حالا توی سلول G۲ نام یک شعبه (مثلاً اهواز) رو داریم و میخوایم توی سلول H۲ مستقیماً نرخ تخفیف اون شعبه رو استخراج کنیم!
مشکل کجاست؟توی جدول اول «نرخ تخفیف» نداریم؛ توی جدول دوم هم اسمی از «شعبه» نیست!
حلقه اتصال این دو تا جدول چیه؟ «نام منطقه»
🟢 راهحل اول: استفاده از VLOOKUP تو در تو (Nested VLOOKUP)
برای حل این مسئله باید جستجوی دو مرحلهای انجام بدیم:۱. اول براساس «*نام شعبه*»، «*نام منطقه*» رو از جدول اول پیدا کنیم:
=VLOOKUP(G۲, A:B, ۲, ۰)
خروجی این فرمول میشه: منطقه ۳
۲. حالا دقیقاً همین فرمول بالا رو میذاریم جای آرگومان اول (Lookup_value) یک VLOOKUP دیگه تا بره از جدول دوم نرخ تخفیف رو بیاره:
=VLOOKUP(VLOOKUP(G۲, A:B, ۲, ۰), D:E, ۲, ۰)
تفسیر فرمول:اول شعبه رو بده به VLOOKUP داخلی تا منطقه رو پیدا کنه، بعد منطقه بهدستاومده رو بده به VLOOKUP بیرونی تا از جدول دوم نرخ تخفیف رو بکشه بیرون!
راهحلهای حرفهایتر و مدرنتر
اگر از آفیس ۲۰۲۱ یا ۳۶۵ استفاده میکنید یا میخواید فرمولنویسی منعطفتری داشته باشید:
۱. با XLOOKUP تو در تو (سریعتر و امنتر):
=XLOOKUP(XLOOKUP(G۲, A:A, B:B), D:D, E:E)
۲. با ترکیب INDEX و MATCH (بدون محدودیت چپ و راست):
=INDEX(E۲:E۴, MATCH(INDEX(B۲:B۱۰, MATCH(G۲, A۲:A۱۰, ۰)), D۲:D۴, ۰))
نکته کلیدی: در کارهای واقعی حتماً محدودههای جدول رو با کلید F۴ قفل (مطلق $) کنید تا موقع درگ کردن فرمول به هم نریزه.
"پیشنهاد به مجله" رو بزنین خستگیامونو بشوره ببره! بازم براتون آموزش رایگان خوب بزارم
اکسلدان شو | آموزش ویژه بازار کار @ExcelDanSho
وقتی بین دو تا جدول گیر میافتی! (تکنیک جستجوی زنجیرهای در اکسل)خیلی وقتا توی گزارشهای مالی، فروش یا اداری با وضعیتی مواجه میشیم که دادهها توی یک جدول جمع نشدن و به صورت سلسلهمراتبی پخش هستن.
صورت مسئله و چالش واقعی:فرض کنید دو تا جدول داریم:۱. جدول اول (ستون A و B): نام «شعبه» رو به «منطقه» وصل کرده.
۲. جدول دوم (ستون D و E): «منطقه» رو به «نرخ تخفیف» وصل کرده.
حالا توی سلول G۲ نام یک شعبه (مثلاً اهواز) رو داریم و میخوایم توی سلول H۲ مستقیماً نرخ تخفیف اون شعبه رو استخراج کنیم!
مشکل کجاست؟توی جدول اول «نرخ تخفیف» نداریم؛ توی جدول دوم هم اسمی از «شعبه» نیست!
حلقه اتصال این دو تا جدول چیه؟ «نام منطقه»🟢 راهحل اول: استفاده از VLOOKUP تو در تو (Nested VLOOKUP)
برای حل این مسئله باید جستجوی دو مرحلهای انجام بدیم:۱. اول براساس «*نام شعبه*»، «*نام منطقه*» رو از جدول اول پیدا کنیم:
=VLOOKUP(G۲, A:B, ۲, ۰)
خروجی این فرمول میشه: منطقه ۳
۲. حالا دقیقاً همین فرمول بالا رو میذاریم جای آرگومان اول (Lookup_value) یک VLOOKUP دیگه تا بره از جدول دوم نرخ تخفیف رو بیاره:
=VLOOKUP(VLOOKUP(G۲, A:B, ۲, ۰), D:E, ۲, ۰)
تفسیر فرمول:اول شعبه رو بده به VLOOKUP داخلی تا منطقه رو پیدا کنه، بعد منطقه بهدستاومده رو بده به VLOOKUP بیرونی تا از جدول دوم نرخ تخفیف رو بکشه بیرون!
راهحلهای حرفهایتر و مدرنتراگر از آفیس ۲۰۲۱ یا ۳۶۵ استفاده میکنید یا میخواید فرمولنویسی منعطفتری داشته باشید:
۱. با XLOOKUP تو در تو (سریعتر و امنتر):
=XLOOKUP(XLOOKUP(G۲, A:A, B:B), D:D, E:E)
۲. با ترکیب INDEX و MATCH (بدون محدودیت چپ و راست):
=INDEX(E۲:E۴, MATCH(INDEX(B۲:B۱۰, MATCH(G۲, A۲:A۱۰, ۰)), D۲:D۴, ۰))
نکته کلیدی: در کارهای واقعی حتماً محدودههای جدول رو با کلید F۴ قفل (مطلق $) کنید تا موقع درگ کردن فرمول به هم نریزه. "پیشنهاد به مجله" رو بزنین خستگیامونو بشوره ببره! بازم براتون آموزش رایگان خوب بزارماکسلدان شو | آموزش ویژه بازار کار @ExcelDanSho۷