آغاز ثبت نام دوره آنلاین شروع حرفه ای اکسل (ظرفیت محدود)
سبد خرید
0

سبد خرید شما خالی است.

جستجو
Generic filters

ترکیب توابع با تابع Vlookup در اکسل

ترکیب توابع با تابع Vlookup
۵/۵ - (۱۱ امتیاز)

یکی از راه های حرفه ای شدن در اکسل، شناخت توابع به تنهایی و توانایی در ترکیب اونها با هم هست. تا حالا بصورت جداگانه با توابع IF و Vlookup آشنا شدید. این دو تابع به تنهایی هم خیلی پرکاربرد هستن و جزو توابعی هستن که حتما باید بهشون مسلط باشیم. وقتی که این دو تابع رو با هم ترکیب میکنیم، نتایج خیلی مفید و کاربردی به ما میده. در این مقاله میخوایم نمونه هایی از ترکیب توابع با تابع Vlookup در اکسل رو ببینیم:

  1. مقایسه خروجی تابع Vlookup با یک عبارت مشخص

یکی از بیشترین مسائلی که از ترکیب توابع IF و Vlookup داریم، این هست که خروجی Vlookup رو با یک عبارت مشخص مقایسه میکنیم و خروجی بله/خیر یا غلط/درست  و … رو نمایش میدیم. ساختار کلی این مسئله بصورت زیر هست:

IF(VLOOKUP(…) = sample_value, TRUE, FALSE) 

مثال اول-مقایسه خروجی Vlookup با یک مقدار مشخص

 فرض کنید لیستی از محصولات به همراه موجودی اونها رو داریم. میخوایم محصولی رو انتخاب کنیم و اگر موجودی اون محصول صفر بود، بگه در انبار نیست.

=IF(VLOOKUP (E1; A1:B10; 2; 0) =۰; بله” ;  “خیر )

 مقایسه خروجی Vlookup با یک مقدار مشخص

شکل ۱- مقایسه خروجی Vlookup با یک مقدار مشخص

مثال دوم- مقایسه خروجی  Vlookup با یک سلول دیگه

فرض کنید میخوایم میزان فروش محصول مورد نظر رو با بیشترین فروش (که در یک سلول دیگه هست) مقایسه کنیم:

=IF ( VLOOKUP(E1;A1:B10;2;0 ) = E9 ;بله” ;  “خیر)

 مقایسه خروجی Vlookup با یک سلول دیگه

شکل ۲- مقایسه خروجی Vlookup با یک سلول دیگه

 مثال سوم- جستجو در یک لیست کوچکتر-مقایسه دو لیست

فرض کنید میخوایم دو لیست رو با هم مقایسه کنیم. اگر داده های جدول در لیست دوم وجود داشت، کلمه “وجود دارد” رو نمایش بده. اگر هم وجود نداشت، کلمه “وجود ندارد” رو نشون بده.

=IF ( ISNA ( VLOOKUP(A2;$D$2:$D$4;1;0) ) ; “وجود دارد” ; “وجود ندارد” )

تابع Vlookup در صورتی که داده مورد نظر رو پیدا نکنه،#N/A هست. پس با تابع ISNA چک میکنیم که آیا خروجی تابع Vlookup خطا هست یا نه. اگر خطا بود، یعنی وجود نداشته پس عبارت “وجود ندارد” را نمایش خواهد داد.

  مقایسه دو لیست

شکل ۳- مقایسه دو لیست با استفاده از ترکیب توابع با تابع Vlookup

  1.  ترکیب تابع Vlookup با IF برای اعمال محاسبات مختلف

میتونیم خروجی تابع Vlookup رو بررسی کنیم و یک سری محاسبات روش انجام بدیم.

فرض کنید میخوایم پورسانت فروشنده ها رو بر اساس میزان فروششون حساب کنیم. شرایط به این صورت است که هر نفر، فروش بیش از ۳۰۰ داشته باشد، بیست درصد و در غیر اینصورت ده درصد پورسانت دریافت خواهد کرد.

=IF(VLOOKUP(D2,A2:B9,2,0)>=300,۰.۲*VLOOKUP(D2,A2:B9,2,0),۰.۱*VLOOKUP(D2,A2:B9,2,0))

انجام عملیات مختلف روی خروجی تابع Vlookup

شکل ۴- انجام عملیات مختلف روی خروجی تابع Vlookup

  1. ترکیب توابع با تابع Vlookup برای کنترل خطای #N/A

IF(ISNA(VLOOKUP(…)), “عبارت مورد نظر”, VLOOKUP(…)) 

اگر تابع Vlookup داده مورد جستجو رو پیدا نکنه، با خطای #N/A مواجه میشیم. یکی از راه های کنترل این خطا استفاده از تابع منطقی ISNA با IF هست.

=IF(ISNA(VLOOKUP(D2,A2:B9,2,0)),“موجود نیست”,VLOOKUP(D2,A2:B9,2,0))

 کنترل خطای تابع Vlookup

شکل ۵- کنترل خطای تابع Vlookup

این تابع چطور کار میکنه؟

خروجی تابع ISNA، True یا False هست. اگر تابع Vlookup با خطا مواجه بشه، خروجی تابع IFNA(Vlookup(….) مقدار True خواهد بود و عبارت “موجود نیست” رو نمایش میده. در غیر اینصورت خروجی ISNA(Vlookup(…) مقدار False خواهد بود که نتیجه خود تابع Vlookup رو بر میگردونه. در اینجا چون “خرمی” در لیست وجود نداره، با خطا مواجه میشه و نتیجه “موجود نیست” نمایش داده میشه. به تصویر زیر دقت کنید.

این کار رو توابع دیگه هم انجام میدن. توابعی مثل IFNA، Iferror، Iserror و If(Iserror). جهت مطالعه بیشتر نحوه مدیریت خطا، مقاله مدیریت خطا در اکسل رو مطالعه کنید.

  1. استفاده از Match و Index بجای Vlookup

خیلی از کاربران اکسل معتقدند که تابع Vlookup تنها راه جستجوی عمودی در اکسل نیست و میتونیم این کار رو با توابع index و Match هم انجام بدیم. به مثال زیر توجه کنید:

=IF (ISERROR (INDEX ( B2:B9, MATCH (D2,A2:A9,0),1)) ,“”, INDEX (B2:B9, MATCH(D2,A2:A9,0),1))

استفاده از Index و Match بجای vlookup

شکل ۶- استفاده از Index و Match بجای Vlookup (ترکیب توابع با تابع Vlookup)

همونطور که در مثال بالا می بینید، بجای استفاده از Vlookup از ترکیب Match و Index برای پیدا کردن فروشنده مورد نظر استفاده کردیم. این فرمول چطور کار میکنه؟

تابع Match به ما میگه که موردی که جستجو میکنیم چندمین سلول از محدوده مورد نظر هست. خروجی تابع Match (که عدد هست) به عنوان آرگومان شماره ردیف در تابع index استفاده میشه. اگر هم داده مورد نظر رو پیدا نکنه خروجی Match بصورت #N/A خواهد بود و ما برای مدیریت خطا از تابع Iserror استفاده کردیم.

به تصویر زیر دقت کنید و نحوه عملکرد ترکیب این دو تابع رو ببینید.

 

در این مقاله ترکیب توابع با تابع Vlookup رو دیدیم. اینکه چطور میتونیم این تابع رو با if ترکیب کنیم و نتایج متنوعی رو داشته باشیم. همونطور که قبلا گفتیم، یکی از راه های حرفه ای شدن در اکسل توانایی در ترکیب توابع و گرفتن خروجی های متنوع هست. برای آشنایی بیشتر با نحوه مدیریت خطا در اکسل، دیباگ کردن فرمول، آشنایی با تابع Index و Match مقالات مرتبط با این موضوعات رو در سایت مطالعه کنید.

126

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • Atashi ۲۰ تیر ۱۴۰۰ / ۶:۵۵ ق٫ظ

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

  • علی فلاح ۴ آذر ۱۳۹۹ / ۸:۱۹ ق٫ظ

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

    • حسنا خاکزاد ۵ آذر ۱۳۹۹ / ۷:۴۱ ب٫ظ

      درود بر شما
      خیلی بستگی به این داره که چه نوع تاریخ یاستفاده میشه
      تاریخ میلادی؟ شمسی؟ چه نوع شمسی؟
      اگر میلادی باشه و با ظاهر شمسی، کافیه از MAXIF یا Dmax استفاده کنید

  • مقصودی ۳۰ آبان ۱۳۹۹ / ۱۱:۵۰ ق٫ظ

    سلام و وقت بخیر اکسلی تهیه کردم برای انبار نزدیک به ۳۰۰۰کالا توش تعریف شده.حتی شیت بندی ها هم کاملا جداست.همه چی خیلی خوب کار میکنه اما تو بعضی از کالاها مجبور به استفاده – هستیم که در این موقع اخطار!value# میده ممنون میشم اگه کمک کنید.

    • حسنا خاکزاد ۲ آذر ۱۳۹۹ / ۲:۱۹ ب٫ظ

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

      • مقصودی ۷ آذر ۱۳۹۹ / ۵:۴۱ ب٫ظ

        ممنون از پاسخگویتون این راه جواب نداد اگه امکان داره اکسل رو بفرستم براتون برسی کنید

  • سبحان ۲۹ آبان ۱۳۹۹ / ۸:۱۶ ب٫ظ

    سلام. تشکر از گروه عالیتون. من سوال اکسل داشتم توی جدول زیر که یه توضیح در موردش بدم: (خیلی دنبال جواب گشتم پیدا نکردم)
    – ستون A گروه های سنی هست از ۵ تا ۱۹ سال ادامه داره
    – ستون B تا E نمایه توده بدنی هست (BMI)
    – حالا با توجه به سن علی که در کتگوری ۵.۷۵ قرار میگیره (ردیف ۶)
    – و BMI علی که ۱۷ هست از ۱۶.۷۵ بیشتر و از ۱۸.۴۵ کمتر هست (نسبت به ردیف ۶)
    من نیاز دارم که در سلول زرد رنگ علی واژه “اضافه وزن” یا همون سلول B2 رو ببینم.
    و این روند برای ۷۰۰ نفر قراره بررسی بشه که هرکدوم ممکنه لاغری شدید، لاغری و… ادامه پیدا کنه. همچنین اگر بالاتر از محدوده اضافه وزن بود واژه “چاق” دیده بشه
    * خیلی سوال شد ببخشید *
    لینک فایل اکسل:
    https://s17.picofile.com/file/8414605776/1.xlsx.html
    لینک عکس جدول:
    https://s16.picofile.com/file/8414605884/1.JPG

  • محسن ۲۰ آبان ۱۳۹۹ / ۴:۴۷ ب٫ظ

    سلام و عرض ادب
    میشه توی تابع vlookup از تابع sum استفاده کرد؟؟
    اگه میشه چطوری

    • حسنا خاکزاد ۲۰ آبان ۱۳۹۹ / ۵:۳۰ ب٫ظ

      درود
      ترکیب توابع کلا امکان پذیره
      سوالتون و بفرماییدتا مشخص بشه منظورتون چیه

  • Rinatad ۲ مهر ۱۳۹۹ / ۱۲:۱۱ ب٫ظ

    با سلام
    وقتی از تابع vlookup استفاده می کنیم سلول مورد نظر که پیدا می شود دیگر دنبال همان محتوای قبلی نمی گردد که اگر چند بار کد ملی مورد نظر تکرار شده باشد فقط اولی را پیدا می کند و ادامه به سرچ نمی دهد
    سوال من اینکه چه فرمولی با vlookup باید ترکیب کرد تا چند دفعه یه ستون را سرچ کند برای یک کدملی ؟؟؟؟؟؟؟؟

    • سامان چراغی ۲۲ دی ۱۳۹۹ / ۳:۴۳ ب٫ظ

      سلام
      تابع Vlookup فقط اولین موردی که پیدا کنه به شما نمایش میده و دیگه متوقف میشه. برای انجام این کار دو راه دارید:
      ۱- استفاده از Pivot Table
      ۲- ترکیب توابع Index و Match
      میتونید مقاله جستجو موارد تکراری رو هم مشاهده کنید.

  • محمدرضا ۱۰ مرداد ۱۳۹۹ / ۴:۱۸ ب٫ظ

    سلام
    من یه جدول دارم، متشکل از کد پرسنلی اسم نسبت(اصلی خود شخص، همسر، فرزند دختر یا پسر، پدر، مادر)
    میخوام گزارشی بگیرم از این جدول که وقتی کد پرسنلی رو وارد میکنم نام تمام افراد تحت تکفل شخص بیاد

    • سامان چراغی ۱۰ مرداد ۱۳۹۹ / ۶:۴۸ ب٫ظ

      کامنت شما در آدرس Vlookup از چند شیت یا فایل پاسخ داده شده.

  • محمد ۱۰ اسفند ۱۳۹۸ / ۱۱:۴۴ ب٫ظ

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

    • حسنا خاکزاد ۱۱ اسفند ۱۳۹۸ / ۱۰:۳۳ ق٫ظ

      درود
      بستگی به ساختار و شرایط داده ها داره
      ولی اولین تابع رو vlookup امتحان کنید
      ببینید با خواسته شما هماهنگی داره یا نه

    • سامان چراغی ۱۱ اسفند ۱۳۹۸ / ۱۰:۳۳ ق٫ظ

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

    • نسترن الهی ۲۴ فروردین ۱۳۹۹ / ۳:۲۹ ب٫ظ

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

      • حسنا خاکزاد ۲۴ فروردین ۱۳۹۹ / ۶:۰۶ ب٫ظ

        درود
        امکان که وجود داره ولی خیلی بستگی به شرایط و جزئیات داره. بهترین روشش استفاده از VBA هست
        اما پیشنهاد میکنم روش کار و ذخیره داده رو تغییر بدید که بهتر بتونید با ابزارها و فمرول نویسی این مدیریت رو انجام بدید.

  • پریا ترابی ۲۰ بهمن ۱۳۹۸ / ۷:۵۹ ب٫ظ

    با عرض سلام و ادب ممنون از پاسخ شما به خاطر سوال اولم
    و اما سوالی دیگری از خدممتون دارم و اون اینه که فرض کنید یه ستون مربوط به آیدی خانوار دارم و یه ستون اطلاعات مرگ و میر کودکان است می خوام محرومیت خانوار رو از نظر مرگ و میر کودکان بررسی کنم. امکان داره خانواری ۳ بچه داشته باشه که هیچ کدوم نمرده باشند و در کل پس خانوار محروم نیست ولی در حالت دوم امکان داره خانواری دارای ۳ فرزند باشه که مثلا یکیشون مرده و دوتای دیگه فوت نکردن. در خانوار حالت اول با remove duplicates مشکل حل میشه و رکورد تکراری از بین میره و به راحتی میبینیم که خانوار محروم نیس چون اطلاعات تکراری برای اون خانوار از بین رفته ولی برای حالت دوم چطور می تونم رکوردی را بگیرم که اگه خانوار حتی ۱ فرزند فوت شده داشته باشه اونو به صورت محروم بهم نشون بده. ممنون میشم اگه راهنماییم کنید.

    • سامان چراغی ۲۴ بهمن ۱۳۹۸ / ۶:۵۹ ب٫ظ

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

  • siavash ۱۸ بهمن ۱۳۹۸ / ۵:۵۷ ب٫ظ

    سلام وقتتون بخیر

    من یک فایل خروجی دارم از تمام کالای های موجود در بازار که بر اساس تاریخِ هر روز، در یک شیت اکسل جمع آوری میشه و شامل قیمت تمام شده، ارزش بنیادی، درصد تغییر قیمت، نسبت خرید به فروش و ….. میباشد.
    (یعنی از هر اسم کالا چندین و چند سطر وجود داره که اطلاعات مربوط به اون کالا در تاریخِ بخصوص رو نشون میده)

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

    • حسنا خاکزاد ۱۹ بهمن ۱۳۹۸ / ۸:۳۱ ب٫ظ

      درود بر شما
      اینکه هر روز یک شیت داره و شما گزارش از همه رو زها میخواید، بهترین کار استفاده اس پاور کوئری برای تجمیع شیت ها و بعد گزارش گیری هست

  • سیاوش ۱۸ بهمن ۱۳۹۸ / ۵:۵۳ ب٫ظ

    سلام وقتتون بخیر

    من یک فایل خروجی دارم از تمام کالای های موجود در بازار که بر اساس تاریخِ هر روز، در یک شیت اکسل جمع آوری میشه
    (یعنی از هر اسم چندین و چند سطر وجود داره که اطلاعات مربوط به اون کالا در تاریخ بخصوص رو نشون میده)

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

  • پریا ترابی ۱۸ بهمن ۱۳۹۸ / ۱:۵۰ ق٫ظ

    با عرض سلام من یه حجم زیادی داده در یک جدول دارم که ستون اول نمایانگر آیدی خانوارهاست و ۶ ستون دیگر نشان دهنده معلولیت های مختلف خانوار است. چه طور می تونم برای هر خانوار اگر معلولیت داره تعداد معلولیت ها رو بشمرم؟

    • حسنا خاکزاد ۱۹ بهمن ۱۳۹۸ / ۷:۱۸ ب٫ظ

      درود بر شما
      توابع شمارش counta و countif هست
      اما ظاهرا ساختار دیتابیس مناسبی ندارید برای این کار
      باز هم شاید لازم باشه بیشتر راجع به داده ها توضیح بدید

  • مینا ۱۰ آبان ۱۳۹۸ / ۱:۵۲ ب٫ظ

    سلام
    من با ترکیبه vlookupوcolumnsفرمولیرو نوشتم و به چن سلول درگش کردم تا فقط با زدنه کد همه ی اطلاعات پرسنل رو برام نمایش بده اما استادمون گفته باید فرمولی بنویسین که اگه به جای نام و نام خانوادگی از لیست چیزه دیگ ای نوشتیم اونوخت همون اطلاعات مرتبط رو بهمون نشون بده

    • سامان چراغی ۱۰ آبان ۱۳۹۸ / ۷:۴۹ ب٫ظ

      سلام
      بله در این حالت به جای استفاده از تابع Column بهتره از تابع Match استفاده کنید که مشخص کنید موردی که از لیست انتخاب میکنید در چندمین ستون جدولی که در Vlookup تعیین شده قرار داره.

  • فرشید شیرکوند ۲۵ مهر ۱۳۹۸ / ۱۱:۳۰ ق٫ظ

    سلام.وقت بخیر
    من یک جدولی دارم که آماد تولیدی اپراتورها رو میخوام ثبت کنم.
    یک اپراتور ممکنه در طول روز سایزهای مختلفی رو تولید کنه. مثلا
    حمید ۲ اینچ ۱۵ عدد
    بهروز ۱/۵ اینچ ۳۰ عدد
    رضا ۳ اینچ ۲۰ عدد
    حمید ۴ اینچ ۱۷ عدد
    احمد ۰٫۵ اینچ ۳۵ عدد
    حمید ۱ اینچ ۴۰ عدد
    بهروز ۱ اینچ ۳۶ عدد
    حالا اگه بخوام یه فرمولی بنویسم که حمید رو پیدا کن و تولید هر سایز رو در زمان مصوب تولیدی اش ضرب کن چه فرمولی باید استفاده کنم؟
    پرکردن و ثبت اطلاعات هم به گونه ای هست که امکان این وجود نداره آمار نفرات مرتب کنار هم ثبت بشه.
    چون در طول روز و در زمانهای مختلف آمار نوشته می شود

    • حسنا خاکزاد ۱۴ آذر ۱۳۹۸ / ۱۲:۳۲ ب٫ظ

      درود بر شما
      توی داده هاتوون زمان رو نشون ندادید
      اما اگر کنارش میاد، مشکلی نیست
      کافیه داده ها رو در هم ضرب کنید
      بعد هر گزارشی خواستید از روش بگیرید

  • محسن ۱ مهر ۱۳۹۸ / ۳:۲۷ ب٫ظ

    با سلام و تشکر از شما
    آیا میتوان با ترکیب دستور IF و vlookup اگر سلولی دارای ۲ شرط بود نتیجه را برگرداند ؟ به عنوان مثال:اگر نام شخص علی و نام خانوادگی او اکبری بوده امتیاز اور را برای ما پیدا کند
    نام نام خانوادگی امتیاز
    علی اکبری ۲۰
    علی احمدی ۳۰

    • حسنا خاکزاد ۳ مهر ۱۳۹۸ / ۱۱:۴۳ ق٫ظ

      درود بر شما
      اگر نخواید پشت سر هم بیاد میتونید با if و and انجام بدید بدون نیاز به vlookup
      اما اگر مهمه که پشت سر هم لیست بهش باید از فرمول نویسی آرایه ای استفاده کنید که بتونه چند شرط رو بررسی کنه

  • حسام ۳۱ شهریور ۱۳۹۸ / ۱۱:۴۷ ق٫ظ

    با سلام و وقت به خیر
    دو تا شیت داریم در یک شیتA نام محصول و مقدار ریالی آن هست و در شیت دیگر Dچندین ستون داریم که هر ستون مربوط به افرادی است مثلا ستون b تا ستون FF ک مربوط به ۱۰۰ نفر می باشدهمچنی هر سلول نشان دهنده یک روز است و در هر روز فرد مربوطه مثلا b یک خرید دارد و در آخر ماه ما می خواهیم با یک فرمول جمع تمام خریدهای مرتبط با شیت A (که در شیت A قیمت ریالی آن موجود هست )با هم جمع بسته شود و زیر ستون b نوشته شود
    لطفا فرمول آن راهنمایی فرمایید
    با تشکر

    • حسنا خاکزاد ۱۴ آذر ۱۳۹۸ / ۶:۲۶ ب٫ظ

      درود بر شما
      سوال خیلی گنگه
      ولی اگه میخواید داده های مربوط به یک شرط رو جمع بزنید، از تابع sumif استفاده کنید

  • رضائي ۱۵ تیر ۱۳۹۸ / ۱۱:۴۲ ق٫ظ

    من دریک فایل اکسل ۲ شیت دارم. در شیت اول اسامی ۱۵۰۰ نفر پرسنل چند شرکت معرفی شده برای یک سمینار هستش که بر اساس کدپرسنلی نوشته شده و در شیت دوم اسامی حاضرین واقعی در سمینار بر اساس کد پرسنلی است . الان من میخواهم با فرمولی در مقابل لیست اولم ( مدعوین ) بر اساس لیست دوم ( حاضرین واقعی ) حاضر و غایب زده شود تا بدانم کدام نفرات معرفی شده از سمینار غایب بودند . از چه فرمول یا فرمولهایی استفاده کنم و نحوه نوشتن آن فرمولها چگونه است ؟

    • حسنا خاکزاد ۲۴ تیر ۱۳۹۸ / ۲:۵۵ ب٫ظ

      درود بر شما
      لینک زیر رو مطالعه کنید

  • صابر ۱۵ تیر ۱۳۹۸ / ۷:۵۱ ق٫ظ

    با سلام سپاس از شما بابت مطاب کاربردی
    یه سوال داشتم. من از تابع vlookup استفاده کردم و فایل رفرنس در پوشه ای هست که برای سایر همکاران Share شده. اما وقتی همکاران از اون فایل استفاده می کنن اطلاعات رو بر نمی گردونه. من چجوری میتونم از این فرمول برای اکسلی که تو پوشه اشتراکی هست استفاده کنم

  • محمد ۲۹ خرداد ۱۳۹۸ / ۹:۳۴ ق٫ظ

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

    • حسنا خاکزاد ۲۹ خرداد ۱۳۹۸ / ۱۰:۰۹ ق٫ظ

      درود بر شما
      سوالتون واضح نیست
      ظاهرا راه حل رو میدونید. مشکل کجاست؟

  • سما ۲ خرداد ۱۳۹۸ / ۱۰:۲۹ ق٫ظ

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

    • حسنا خاکزاد ۴ خرداد ۱۳۹۸ / ۱۰:۳۴ ق٫ظ

      درود بر شما
      از تابع Vlookup استفاده کنید

  • احمد پسندیده ۱۰ اردیبهشت ۱۳۹۸ / ۸:۴۷ ق٫ظ

    سلام چطور میتونم در ورک شیتی که ستونهای اون تاریخ و سطرهای اون اسم کالا هست داده متناظر با آخرین روز مربوط به یک کالای خاص رو با تابع vlookup استخراج کنم.

    • حسنا خاکزاد ۱۰ اردیبهشت ۱۳۹۸ / ۱۲:۲۰ ب٫ظ

      درود بر شما
      کاملا بستگی به جنس تاریخ و نحوه چینش داده ها داره

  • علی ۲۱ فروردین ۱۳۹۸ / ۱۱:۰۶ ق٫ظ

    سلام
    خسته نباشید
    یک فایل اکسل دارم با ۲ شیت که برای هر دو شیت ستون اول شماره درخواست،ستون دوم کد کالا و ستون سوم تعداد درخواست می باشد که ممکن است برای یک شماره درخواست یکسان چندین کد مختلف با تعداد متفاوت ثبت شده باشد
    سوال:
    اگر بخوام از شیت a ردیفی که شماره درخواست ۱ با کد ۱۰۰ ثبت شده مقدار درخواست را از شماره درخواست ۱ با کد ۱۰۰ از شیت b بردارم چیکار کنم
    این را مدنظر داشته باشید احتمال این که برای مثال درخواست ۱ شامل چند کد در ردیفهای مختلف باشد هست
    ممنون

    • حسنا خاکزاد ۲۱ فروردین ۱۳۹۸ / ۶:۴۰ ب٫ظ

      درود بر شما
      اگر درست متوجه شده باشم، نیاز به فمرول نویسی آرایه ای دارید که هم دو شرطی بودن رو حل کنه و هم تکراری بودن.
      اگر فایل سنگین نمیشه و میتونید، منطق آرایه ای رو سرچ کنید و فرمول نویسی آرایه ای رو یاد بگیرید.

  • فرزانه ۱۷ فروردین ۱۳۹۸ / ۱۲:۰۷ ب٫ظ

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

  • فرزانه ۱۷ فروردین ۱۳۹۸ / ۱۰:۵۴ ق٫ظ

    سلام. میخوام فاکتورفروش چاپ کنم که بازدن شماره فاکتور، همه فیلدهای اون مثل شرح کالا، کدکالا، تعداد، قیمت و… را از توشیت قبلیش بخونه وبیاره اینجابشونه چطوری میتونم با vlook up انجام بدم

    • حسنا خاکزاد ۱۷ فروردین ۱۳۹۸ / ۱۱:۴۴ ق٫ظ

      درود بر شما
      کافیه در یک شیت همه اطلاعات رو با شماره فاکتور ثبت کرده باشید
      بعد با vlookup مقادیر مرتبط رو در فاکتور فراخوانی کنید.
      lookup value شما میشه شماره فاکتور

      مقاله زیر رو بخونید
      https://excelpedia.net/vlookup-function/

  • مرتضی ۲۹ بهمن ۱۳۹۷ / ۱:۵۶ ب٫ظ

    سلام و خسته نباشید
    میخوام یک کد رو جستجو کند و تمام کد های تکرار آن را چاپ کند ؟

    • حسنا خاکزاد ۳۰ بهمن ۱۳۹۷ / ۸:۴۹ ب٫ظ

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

      فرمول نویسی آرایه ای:
      https://excelpedia.net/array-formula/

  • mehrdad ۱۵ بهمن ۱۳۹۷ / ۲:۱۵ ب٫ظ

    درو بر شما ادمین های گرامی
    یه سوال داشتم در مورد ترکیب vlookup و if. من میخوام از یک جدول عبارتی (کد یا نام شخصی) رو سرچ کنم و کلیه اطلاعات تو ردیف اون کد یا شخص رو برام تو جدول دیگه بیاره

  • محمد علی ۲۷ آبان ۱۳۹۷ / ۱۱:۴۷ ق٫ظ

    با عرض سلام وادب من می خوام از vlookup استفاده کنم ودوشرط و جود داشته باشه یعنی در فرمول گفته میشه من دنبال محتویات سلولA1 هستم بشرطی که سلول نظیر b1 هم باهم برابر باشه. می خوام در مرحله اول جستجو ۲ گزینه رو بررسی کند. ممکنه بنده رو راهنمایی کنید. این در مورد چند سری فاکتور هست که کالاهای مشترک دارند و میخوام با اطلاعات مشابه اون مقایسه بشود.

    • حسنا خاکزاد ۲۷ آبان ۱۳۹۷ / ۱:۳۶ ب٫ظ

      درود بر شما
      یک راه استفاده از ستون کمکی و چسباندن این دو شرط به هم هست.
      راه دیگه استفاده از فرمول نویسی آرایه ای که باید از Choose استفاده کنید به صورت زیر.
      Ctrl+Shift+Enter فراموش نشه

      • حمیدرضا ۱۸ خرداد ۱۳۹۸ / ۴:۵۷ ب٫ظ

        سلام وقت بخیر
        دقیقا من همین مشکل رو دارم و از همین فرمول استفاده کردم ولی بازم N/A میده

        • حسنا خاکزاد ۱۸ خرداد ۱۳۹۸ / ۹:۰۴ ب٫ظ

          درود بر شما
          ctrl+shift+enter حتما باید بزنید
          فرمول آرایه ای هست
          جداکننده ها رو هم دقت کنید

          • حمیدرضا ۱۹ خرداد ۱۳۹۸ / ۲:۱۴ ب٫ظ

            اوکی ممنون
            فقط فرمول آرایه ای چی هست؟

    • محمد ۱۶ آذر ۱۳۹۷ / ۸:۵۵ ب٫ظ

      ممنون از
      خانم خاکزاد برای پاسختون

  • میلاد ۲۶ آبان ۱۳۹۷ / ۵:۲۳ ب٫ظ

    سلام.من یه راهنمایی می خواستم.من تو اکسل با استفاده از ,Vlook و یا Match ,Index یه داده را فراخوانی می کنم.چطوری میتونم چند تا جواب داشته باشم؟ خیلی جستجو کردم.همیشه اولین جوابو بهم میده این فرمول ها.من لیست جواب های Vlook را میخوام.چند جواب داشته باشه Vlook من.

    • حسنا خاکزاد ۲۷ آبان ۱۳۹۷ / ۹:۰۰ ق٫ظ

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

      در این فرمول، D1 مقداری هست که جستجو میکنید
      ستون A ستونی هست که جستجو در اون انجام میشه و ستون B ستونی هست که مقدار مورد نظر فراخوانی میشه

      برای ثبت فرمول هم از ctrl+shift+enter استفاده کنید

  • rishsefid ۹ آبان ۱۳۹۷ / ۰:۴۴ ق٫ظ

    سلام خدمت خانم خاکزاد.خسته نباشید.لطفا منو راهنمایی کنید.اگه ما در یک ستون یک سری از اعداد داشته باشیم و در ستون دیگر مجموع چند عدد با هر یک از ان اعداد برابر باشد چگونه انها را پیدا کنیم و رنگی کنیم و مشخص کنیم.(راستش دیگه از راهنمایی دیگران نتونستم راهی پیدا کنم)(مغایرت بانکی) واز چه توابعی استفاده کنم.
    و سوال دیگر ایا در اکسل با توابع میشه حلقه ایجاد کرد؟
    ممنونم برای پاسخگویی که وقت میگذارید و امیدوارم همیش موفق باشید.

    • حسنا خاکزاد ۱۰ آبان ۱۳۹۷ / ۱۱:۴۶ ق٫ظ

      درود بر شما
      بحث مغایرت گیری خیلی متنوع هست و بستگی به ساختار ها و … داره. یک راه استفاده از solver add ins هست که البته یک سری محدویدت ها داره.
      یعضی ها هم چند مرحله این کار رو انجام میدن که خب روش های مختلفی هم استفاده میکنن.

      حلقه رو هم با کدنویسی میتونید ایجاد کنید.

      • rishsefid ۱۰ آبان ۱۳۹۷ / ۲:۰۵ ب٫ظ

        ممنون از پاسختون.در مورد تابع solver add ins مقاله و راهنمایی بفرمایید.با تشکر

        • حسنا خاکزاد ۱۰ آبان ۱۳۹۷ / ۱۰:۱۰ ب٫ظ

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

  • mak ۱۱ مهر ۱۳۹۷ / ۱۰:۴۲ ق٫ظ

    با سلام
    این فرمول

    با فرمول یکسانه؟

    • حسنا خاکزاد ۱۱ مهر ۱۳۹۷ / ۱۲:۰۹ ب٫ظ

      درود بر شما

      بله

      البته اگه بجای موجود نیست، بذارید ” “

    • مصطفی اسدی ۱۲ مهر ۱۳۹۷ / ۱۱:۳۷ ب٫ظ

      سلام ممنون از آموزش هاای خوبتون
      میشه مثال از ااستفاده از vlookup در کدنویسی بگذارید یا index ممنون

      • حسنا خاکزاد ۱۴ مهر ۱۳۹۷ / ۹:۰۲ ق٫ظ

        درود بر شما
        در کدنویسی لزوما توابع vlookup و … میتونه استفاده نشه
        یک سری دستورات هستن برای انجام جستجو های متنوع
        بستگی به سوال داره.
        اما بصورت کلی در محیط VBA به برخی توابع میتونید با دستور زیر دسترسی داشته باشید
        worksheetfunction

  • mak ۱۱ مهر ۱۳۹۷ / ۱۰:۴۱ ق٫ظ

    با سلام و احترام
    ممنون از آموزش‌های کامل، زیبا و کاربردی‌ شما

  • امیرحسین مظاهری ۸ مهر ۱۳۹۷ / ۱۱:۳۵ ق٫ظ

    ممنون از وقتتون . بله احتمالا بنده بد مطرح کردم .
    در حالت کلی میخوام مدارکdcc transmittal و comment sheet …. های یک پروژه ی مهندسی رو مرتب کنم . با توجه به coordination procedure موجود .
    من میخوام در ستون اول ، عبارتی رو وارد کنم . عبارت میتونه شماره سند باشه.
    بعنوان مثال شماره سند واصله به من : EX-FH-UH-L-97-001
    در coordination procedure ترتیب و تعاریفی برای هر قسمت داریم . مثلا قسمت اول شماره سند بالا ، مشخص کننده نام پروژه باشد . در این رشته EX را مخفف excelpedia در نظر گرفته ایم . من میخواهم excelpedia در ستون دوم ( ستون نام پروژه ) درج شود .

    قسمت بعدی شماره (در اینجا FH ) بعنوان مثال ارسال کننده ی نامه است . FH را قبلا factory headquarter تعریف کردیم . حال میخواهم در ستون سوم ( ارسال کننده ) عبارت factory headquarter رو وارد کنه .
    هدف بنده اینه که در ستون اول شماره سند رو وارد کنم ، و در بقیه ستون ها ، به ترتیب ، اطلاعات رو وارد کنه .

    باز هم ممنون از وقت و شکیبایی تون .

    • حسنا خاکزاد ۸ مهر ۱۳۹۷ / ۱:۳۸ ب٫ظ

      برای اینکار باید اول جداول استاندارد رو تعریف کنید برای اجزای مختلف کد
      مثلا یک جدول اسم پروژه ها با کدهاشون
      یک جدول اسم ارسال کننده ها و کدهای مخففشون
      و ….

      بعد مرحله بعدی تفکیک این کد هست. اگر اجزای کد و تعداد حروف ثابته، راحت تره و الا باید دنبال الگو بگردین.
      برای تفکیک باید از فرمول های متنی استفاده کنید (داخل سایت مقالات هست).

      بعد نتیجه تفکیک رو بذارید داخل تابع vlookup
      مثلا اگر قسمت اول کد ختما ۳ رقمه، نمونه فرمول این میشه:

      توضیح:
      کد در سلول A1 نوشته شده
      Table1 جدول تهیه شده کدهای قسمت اول هست

      • امیرحسین مظاهری ۹ مهر ۱۳۹۷ / ۹:۱۹ ق٫ظ

        ممنون !
        تفکیک فرمول های متنی رو داخل سایت جستجو کنم ؟ الگو منظور چیست ؟

        و آیا اصلا این کار ، با اکسل متداول هست ؟
        بازم ممنون !

  • امیرحسین مظاهری ۷ مهر ۱۳۹۷ / ۴:۵۷ ب٫ظ

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

    abc-123-dfg
    مثلا حروف قبل از خط فاصله اول ، مشخص کننده کشور سازنده محصولی باشد ( مثلا Abc را ایران تعریف کردیم ، grm را آلمان تعریف کرده بودیم و aus را استرالیا ) کاراکتر های بعدی مشخص کننده مثلا سال ساخت محصول باشند ( مثلا ۱۲۳ را ساخت امسال ، ۴۵۶ را ساخت سال قبل و …. تعریف کرده باشیم )
    همینطور الی آخر.

    • حسنا خاکزاد ۸ مهر ۱۳۹۷ / ۹:۲۱ ق٫ظ

      اگر منظورتون اینه که مثلا سه ستون داشته باشید و در هر ستون یک قسمت از کد رو بنویسید و بخواید در ستون چهارم به هم بچسبه.( یعنی در یک سلول بنویسید ایران و در ستون کد تبدیل بشه به ABC و الی آخر)
      بله
      میشه با Vlookup این کار و کرد.
      جداول استاندارد رو تشکیل بدید و فراخوانی کنید

      کلا سوال رو کامل مطرح نمیکنید و ما مجبوریم حدس بزنیم!!!!
      تا اینجای کار فرضیات رو گفتید. ولی نگفتید که چکار میخواید بکنید

  • امیرحسین مظاهری ۷ مهر ۱۳۹۷ / ۱۱:۵۳ ق٫ظ

    سلام . ممنون از مطلب خوبتون . من اگر بخواهم یک کد ، بطور مثال شماره نامه ای در فرمت خاص وارد یک سلول کنم ، و در ستون های دیگر این کد را بخواند و از حالت فشرده خارج کند ، باید از همین دستور استفاده کنم ؟
    مثال : m-k-1397 را وارد میکنم . میخواهم در ستون اول معنای m را ( مثلا به معنی مهم ) وارد کند و در ستون دوم معنای حرف k ( مثلا ، تاخیر ) را وارد کند …

    ممنون از مطالب مفیدتون.

    • حسنا خاکزاد ۷ مهر ۱۳۹۷ / ۳:۰۹ ب٫ظ

      درود بر شما
      منظورتون از حالت فشرده خارج کردن چی هست؟!

      سوال رو واضح تر بفرمایید

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

سه + نوزده =

توسط
تومان