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

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

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

Vlookup از چند شیت یا فایل

vlookup در چند شیت
۵/۵ - (۲ امتیاز)

vlookup در چند شیت و فراخوانی داده ها

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

جستجو بین دو شیت

وقتی میخوایم جستجوی داده رو بین دو شیت انجام بدیم، روش کار خیلی مشابه Vlookup معمولی هست. تنها تفاوت این هست که نام شیت رو در قسمت Table_Array باید اضافه کنیم. یعنی ساختار تابع بصورت زیر خواهد بود:

=VLOOKUP(lookup_value, Sheet!range, col_index_num, [range_lookup])

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

=VLOOKUP (A2 ,’محصول ۱′!A1:B13 , 2,0)

در واقع کافیه در حین نوشتن فرمول و انتخاب آرگومان دوم، روی شیت محصول ۱ کلیک کنیم و Table_Array رو محدوده A1:B13 انتخاب کنیم.

فراخوانی داده از چندین شیت

آرگومان های تابع به شرح زیر است:

Lookup_Value: مقداری که میخواهیم جستجو کنیم. در اینجا یعنی “اردیبهشت” یا سلول A2 که در اون کلمه اردیبهشت نوشته شده.

Table_Array: جدولی که جستجو در اون انجام میشه. در اینجا داده های فروش مربوط به محصول ۱ در شیت به نام “محصول ۱” قرار گرفته. محدوده A1:B13

Col_indx_num: شماره ستونی از محدوده جستجو که میخواهیم نمایش داده بشه. مقدار فروش ستون دوم از جدول هست پس این آرگومان عدد ۲ تعیین میشه.

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

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

نکته:
اگر اسم شیت Space یا کاراکترهای غیرالفبایی وجود داشته باشه، اسم شیت در یک سینگل کوتیشن قرار میگیره. برای مشاهده جزئیات و نحوه کار با رفرنس ها در شیت های دیگه، مقاله ارجاع به سایر منابع رو مطالعه کنید.

 

جستجو بین دو فایل (Workbook)

در جستجو بین دو فایل، مثل حالت بین دو شیت عمل میکنیم. در این حالت نام فایل هم به ادامه اسم شیت اضافه میشه. اسم  فایل داخل براکت و بعد اسم شیت و بعد محدوده مورد نظر. یعنی:

=VLOOKUP(A2, [فروش.xlsx]اردیبهشت!$A$2:$B$6, 2, FALSE)

Vlookup بین چند شیت با Iferror

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

=IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, “پیدا نشد“))

مثلا محصول ۱ در شش ماه اول و محصول ۲ در شش ماه دوم به فروش رسیده. حالا میخوایم در یک شیت همه اطلاعات رو تجمیع کنیم (شکل ۱).

vlookup در چند شیت

شکل ۱- جستجو بین چند شیت با Vlookup و Iferror

 برای این کار فرمول رو به شرح زیر می نویسیم:

=IFERROR (VLOOKUP (A2,’محصول ۱′!$A$2:$B$7,2,0) , IFERROR (VLOOKUP (A2,’محصول ۲′!$A$2:$B$7,2,0) ,”پیدا نشد”) )

با این فرمول اگر داده مورد نظر در شیت محصول ۱ پیدا نشه، جستجو در شیت محصول ۲ انجام میشه و اگر در شیت محصول ۲ هم مورد جستجو پیدا نشه و با خطا مواجه بشیم، عبارت “پیدا نشد” نمایش داده میشه.

جستجو بین شیت ها با استفاده از Indirect

وقتی تعداد شیت ها بیشتر میشه اینکه برای هر شیت یک Vlookup جداگانه نوشته بشه و به شیت مربوطه ارجاع داده بشه کار سختی هست و انجام این کار با استفاده از vlookup در چند شیت کار خوبی نیست. در این قسمت میخوایم با استفاده از تابع Indirect جستجو رو بین همه شیت ها انجام بدیم. برای این کار باید از فرمول نویسی آرایه ای استفاده کنیم. قبل از نوشتن این فرمول، باید به چند نکته دقت کنیم:

  • در یک محدوده اسم شیت های مورد نظر رو وارد کنیم:

نامگذاری محدوده اسم شیت ها

شکل ۲- جستجو بین شیت ها – نامگذاری محدوده اسم شیت ها

  • فرمول نویسی آرایه ای با کلید ترکیبی Ctrl+Shift+Enter ثبت میشه.
  • ساختار داده ها (ترتیب ستون ها) در شیت ها باید مشابه باشند.
  • چون از یک Table_Array در کل فرمول استفاده خواهیم کرد، بهتره بزرگترین محدوده بین شیت ها رو در نظر بگیریم که مطمئن باشیم همه داده ها پوشش داده شده.

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

=VLOOKUP(lookup_value, INDIRECT(“‘”&INDEX(Sheets_name, MATCH(1, –(COUNTIF(INDIRECT(“‘” & Sheets_name & “‘!lookup_range“), lookup_value)>0), 0)) & “‘!table_array“), col_index_num, FALSE)

Sheets_name: اسم شیت هایی که در یک محدوده قرار گرفته

Lookup_value: مقدار مورد جستجو. مثلا در اینجا نام ماه

Lookup_range: ستونی که مورد جستجو قرار میگیره و داده Lookup value در اون قرار داره

Table_array: محدوده مورد جستجو (جدول مورد نظر)

Col_index_num: شماره ستون داده مورد نظر در این قسمت تعیین میشه

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

=VLOOKUP (A2, INDIRECT(“‘”&INDEX(Sheets_name, MATCH (1, –COUNTIF(INDIRECT(“‘”&Sheets_name& “‘!A2:A7”),A2) >0), 0) ) & “‘!A2:B7”),2, FALSE)

این فرمول چطور کار میکنه؟

برای اینکه بتونیم درک کنیم که این فرمول چطور کار میکنه، باید به اجزا کوچکتر تجزیه کنیم.

از داخلی ترین توابع یعنی Countif (Indirect(….)) شروع میکنیم:

با استفاده از تابع Indirect اسم شیت ها رو به محدوده A2:A7 که محدوده جستجو هست میچسبونیم.

INDIRECT({“‘محصول ۱’!A2:A7″;”‘محصول ۲’!A2:A7”})

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

COUNTIF ({“‘محصول ۱’!A2:A7″;”‘محصول ۲’!A2:A7”} , A2)

این فرمول تعداد A2 (داده مورد جستجو) رو در دو شیت محصول ۱ و محصول ۲ در محدوده A2:A7 محاسبه میکنه.

نتیجه فرمول Countif بصورت زیر خواهد بود:

{۱;۰}

این یعنی مقدار A2 در شیت محصول ۱ یکبار تکرار شده و در شیت محصول ۲ اصلا وجود نداره.

حالا برای اینکه نام شیتی که رکورد مورد نظر داخلش وجود داره رو پیدا کنیم، اول باید نتایج Countif که بزرگتر از ۰ هست رو پیدا کنیم. پس برای این کار نتیجه Countif رو با ۰ مقایسه میکنیم. و نتیجه بصورت False / True نمایش داده میشه.

COUNTIF(INDIRECT(“‘” &Sheets_name& “‘!A2:A7”),A2)>0

حالا برای تبدیل مقادیر logical به مقدار عددی – –  رو قبل از تابع countif قرار میدیم.

–(COUNTIF(INDIRECT(“‘” &Sheets_name& “‘!A2:A7”),A2)>0)

نتیجه این فرمول مقدار ۰ و ۱ خواهد بود. در واقع مقدار ۱ تعیین میکنه داده مورد نظر در کدوم شیت وجود داره. در نتیجه زیر نشون میده که داده مورد نظر در شیت اول یعنی شیت محصول ۱ وجود داره و در شیت محصول ۲ (شیت دوم) وجود نداره.

{۱;۰}

حالا باید مکان این عدد ۱ رو تعیین کنیم. برای این کار از Match استفاده میکنیم. این فرمول مکان اولین عدد ۱ رو تعیین میکنه.

MATCH (1, –(COUNTIF(INDIRECT(“‘” &Sheets_name& “‘!A2:A7”),A2)>0), 0)

حالا باید با توجه به این نتیجه، اسم شیت رو فراخوانی کنیم. برای این کار خروجی تابع Match رو در تابع Index قرار میدیم. تابع Index بین اسم شیت ها، داده متناسب با خروجی Match رو به عنوان خروجی میده.

INDEX (Sheets_name, MATCH(1, –(COUNTIF(INDIRECT(“‘” &Sheets_name& “‘!A2:A7”),A2)>0), 0))

نتیجه فرمول زیر بصورت زیر خواهد بود:

INDEX {“محصول ۲″;”محصول ۱”}),۱)

خروجی این فرمول عبارت “محصول ۱” خواهد بود که نام شیت داده مورد جستجو هست.

حالا برای اینکه این اسم بدست آمده را به یک آدرس تبدیل کنیم. باید طبق الگوی آدرس دهی عمل کنیم و الگوی مورد نظر رو بسازیم. برای این کار آدرس رو میسازیم و در Indirect قرار میدیم.

INDIRECT(“‘”&INDEX(Sheets_name, MATCH(1, –(COUNTIF(INDIRECT(“‘” &Sheets_name& “‘!A2:A7”),A2)>0), 0)) & “‘!A2:B7”)

خروجی این فرمول بورت زیر خواهد بود: (نام شیت به همراه آدرس محدوده)

“‘محصول ۱’!A2:B7”

نکته:
به ساختار آدرس محدوده با ! دقت داشته باشید. این الگو باید برای همه آدرس دهی ها رعایت بشه. اول نام شیت، بعد علامت تعجب و بعد محدوده مورد نظر.

 

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

=VLOOKUP (A2, INDIRECT (“‘محصول ۱’!A2:B7”),2, FALSE)

فراموش نکنید که این فرمول آرایه ای هست و باید با Ctrl+shift+Enter ثبت بشه.

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

دانلود فایل آموزش نحوه انجام Vlookup در چند شیت

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

آواتار
127

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

دیدگاه کاربران
  • امیر ۲۷ خرداد ۱۴۰۱ / ۵:۱۸ ب٫ظ

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

    • سامان چراغی ۲۸ خرداد ۱۴۰۱ / ۹:۵۲ ق٫ظ

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

  • amir ۲۷ خرداد ۱۴۰۱ / ۵:۱۵ ب٫ظ

    با سلام و احترام
    در تابع vlookup یکی از آرگومان های آن این است که columun index number را مشخص کرد یعنی بگوییم که در ستون چندم جستجو شود اگر که جدول ما خیلی طولانی بود و ستون های زیادی داشت آیا راهی وجود دارد که مثلا شمارش کردن ستون ۲۰ بود خوب با شمارش دست در ستون ۲۰ خیلی سخت است آیا می شود کاری کرد که در جدول که حاوی ستون های زیادی است در این آرگامون که ستون چندم است کاری کرد که شماره ستون بدون شمارش دست مشخص شود؟ شماره ستون بدون شمارش مشخص شود
    با تشکـــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــر

    • آواتار
      حسنا خاکزاد ۲۸ خرداد ۱۴۰۱ / ۱۰:۴۷ ق٫ظ

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

  • امیر ۲۷ خرداد ۱۴۰۱ / ۵:۱۰ ب٫ظ

    با سلام و احترام
    چطور می شود که سلول های حاوی error را شمارش کرد؟

    • سامان چراغی ۲۸ خرداد ۱۴۰۱ / ۹:۵۱ ق٫ظ

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

  • امیر ۲۷ خرداد ۱۴۰۱ / ۵:۰۸ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۲۸ خرداد ۱۴۰۱ / ۱۰:۴۲ ق٫ظ

      درود
      در قسمت فرمول بنویسید :
      len(a1)=5

  • امیرحسین ۲۷ خرداد ۱۴۰۱ / ۵:۰۵ ب٫ظ

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

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

      درود
      فرمول بنویسید در قسمت custom:
      isnumber(find(“@”,A1))

  • امیرحسین ۲۷ خرداد ۱۴۰۱ / ۴:۵۴ ب٫ظ

    با سلام
    چطور می شود در کاندیشنال فرمتینگ اگه text بود و مابین مثلا ۳ تا ۶ حرف بود آن وقت به رنگ خاصی نمایش داده شود.
    با تشــــــــــــــــکر

    • آواتار
      حسنا خاکزاد ۲۸ خرداد ۱۴۰۱ / ۱۰:۴۶ ق٫ظ

      درود
      دو شرط دارید:
      And(and(len(A1)>=۳,Len(a1)<=6),istext(A1)) اینو در قسمت فرمول نویسی بنویسید

  • محسن ۲۳ اردیبهشت ۱۴۰۱ / ۳:۱۵ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۲۳ اردیبهشت ۱۴۰۱ / ۶:۱۱ ب٫ظ

      درود
      داخل یک شیت بزنید = وروی سلول مورد نظر کلیک کنید بعد بزنید + و برید سراغ شیت دوم و روی سلول بعدی کلیک کنید و Enter رو بزنید

  • علی ۲۱ اردیبهشت ۱۴۰۱ / ۷:۴۷ ق٫ظ

    سلام. چند تا شیت تو اکسل دارم و هر شیت بر حسب وقایعی است که در بازه های زمانی مختلف اتفاق افتاده. میخوام یک شیت تجمیعی درست کنم که بره تو تمام شیتها جستجو کنه و عبارت متناظر با کوچکترین تا بزرگترین تاریخ را به ترتیب زمانی نشون بده. مثلا ممکنه کوچکترین تاریخ تو شیت ۳ باشه، بعد مثلا تو شیت ۷ و بعد مثلا تو شیت ۱ و … . آیا امکان پذیر هست؟

    • آواتار
      حسنا خاکزاد ۲۱ اردیبهشت ۱۴۰۱ / ۱۰:۰۳ ق٫ظ

      درود با وی بی بهتر میتونید انجام بدید (عین چیزی که گفتید)
      اما میتونید با پاور کوئری تجمیع کنید و بعد با استفاده از سورت و …. ترتیب تاریخ ها رو درست کنید

  • حسن صادقی ۲۰ بهمن ۱۴۰۰ / ۹:۱۳ ب٫ظ

    سلام
    یه شیت داریم ستون اول اون محصولات ۱ تا ۱۰ هستند و بعد از آن وزن و نرخ و مبلغ
    می خواهیم طی ۱۰ شیت بعدی با زدن عنوان محصول، مقادیر،نرخ ها و مبالغ مربوط به هر محصول در شیت مربوط به آن درج شود از طریق تابع ایندکس و ستون کمکی رفتم فقط یک شیت تهیه شد . اگه راهنمایی کنید ممنون میشم.

    • آواتار
      حسنا خاکزاد ۲۱ بهمن ۱۴۰۰ / ۱۱:۲۷ ق٫ظ

      درود
      باید در تک تک اون ده شیت فرمول مربوطه رو بنویسید(میتونید شیت ها رو انتخب کنید، یکبار vlookup بنویسید و ctrl+enter) که از دیتابیس بخونه و اطلاعات رو برداره…. اما اگر داده تکراری وجود داره، با ویلوکاپ نمیشه
      یا اینکه کدنویسی VBA انجام بدید

  • حسن عباسی ۹ بهمن ۱۴۰۰ / ۶:۳۷ ب٫ظ

    سلام من یک شیت دارم که اطلاعات پایه توش هست بدین صورت که برای هر سلول با محتوای متنی خاص یک کد مربدط به اون تعریف کردم حالا تو یک شیت دیگه میخوام با ثبت کد در یک سلول محتویات مربوط به اون کد در سلول بعدی خودش ظاخر بشه چیکار کنم..ممنون

    • آواتار
      حسنا خاکزاد ۱۰ بهمن ۱۴۰۰ / ۲:۳۰ ب٫ظ

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

  • لوکا ۲۰ بهمن ۱۳۹۹ / ۴:۵۵ ق٫ظ

    سلام دوستان:
    والا من فایلی دارم که از سرور اطلاعات نماد های بورسی بروز میشه و اما مشکلی که داره نمادها همیشه نیستند یعنی امروز نمادی که معامله نشده تو لیست نیست ولی فردا ممکنه تو لیست باشه و تو آپدیت پس فردا لیست ممکنه باشه. و همچنین هربار که فایل اکسل آپدیت میشه ستونها هم جاش متفاوت میشه در یک ستون ایجاد میشه یعنی گاهی ستون aمیشه ستون نام نماد و گاهی ستون b میشه ستون نام نماد (من فک میکنم سرورهای مختلف دارن و به روشهای مختلف کویری میگیرن میزارن رو سرور.)
    حالا من میخوام یه فایل ایجاد کنم که اکر تو ستون a و سطر یک اون نام نماد رو تایپ میکنم سطر اطلاعات اون نماد رو جلوی اون پیست کنه و همین طور تا آخر حدودا در هر روز ۱۰ نفر رو سرچ میکنم اسم نمادها یونیک هستند ولی کد ندارن … در واقع میخوام تو کل اون شیت یا فایل اکسلی که میاد اسم نماد رو سرچ کنه و اطلاعات رو وارد کنه ….چون اطلاعات شیت اول از لحاظ قیمت و حجم معامله تغییر میکنه میخوام هی نام ۷ یا هشت ماد رو تو اون شیت سرچ کنه و اطلعلت جدید رو مرتب تو شیت جدید بیاره که من بتونم محاسباتم رو روش انجام بدم.
    ممنون میشم راهنماییم کنید

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

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

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

    سلام و عرض ادب
    خانم مهندس ممنون میشم در خصوص موضوع زیر بهم کمک کنید:
    دو تا شیت دارم. در شیت اول اطلاعات صورت وضعیت های دریافتی رو به شرح ذیل وارد میکنم:
    ردیف کد ICM شماره صورت وضعیت تاریخ صدور صورت وضعیت نام شرکت شماره قرارداد مبلغ قرارداد و …

    بعد در شیت دوم یک فرم چرخش صورت وضعیت بنام ICM طراحی کردم که هر وقت میخوام صورت وضعیت دریافتی رو برای بررسی بین تیم پروژه به چرخش بندازم اطلاعات رو از شیت اول وارد این فرم میکنم و سپس پرینت میگیرم.
    الان میخوام فرمولی بدم که کد ICM رو از شیت اول کپی کنم و بعد از اینکه در سل ICM NUMBER در شیت دوم در فرم چرخش صورت وضعیت Paste کردم همه اطلاعات مربوط به اون صورت وضعیت که در شیت اول وارد شده رو انتقال بده به سل های تعبیه شده در فرم چرخش صورت وضعیت.

    با تشکر

    • آواتار
      حسنا خاکزاد ۱۵ بهمن ۱۳۹۹ / ۱۲:۰۴ ب٫ظ

      درود
      تابع vlookup میتونه انجام بده

  • مقداد ۱۸ آبان ۱۳۹۹ / ۵:۴۳ ب٫ظ

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

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

      درود
      بین شیت و فایل هیچ فرقی نداره
      حتمما چک کنید که عین اون داده وجود داشته باشه با فرمت یکسان
      و الا جستجو بین شیت و فایل هیچ فرقی به لحاظ اصولی نداره

  • Khosravi ۱۵ آبان ۱۳۹۹ / ۹:۵۱ ق٫ظ

    سلام
    سوال من اینه که چطور فرمول vlookupی سلول رو بتونم‌توی یک ردیف برای سلولها ی دیگر drag کن

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

      درود
      باید اصول درگ کردن رو یاد بگیرید
      فرقی نمیکنه چه تابعی

      ای مقاله رو مطالعه کنید
      https://excelpedia.net/cell-address/

  • رضا ۱۵ مهر ۱۳۹۹ / ۳:۲۱ ب٫ظ

    سلام چجوری میتونم اطلاعات وارد شده در یک شیت یک رو در شیت دو داشته باشم و هر تغییری در شیت یک انجام شد در شیت دو هم انجام شود

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

      درود
      اگه عینا همونه که مساوی بذارید همه سلول ها رو
      اما اگه چیزی شبیه گزارشگیری هست که باید از توابع مربوطه استفاده کنید و اطلاعات رو از شیت ۱ فراخوانی کنید

  • شایان ۲۰ شهریور ۱۳۹۹ / ۱۱:۱۵ ب٫ظ

    با عرض سلام و خسته نباشید
    چگونه میتوانم با کمک فرمول، محتوای موجود در ستونهای ۲ و ۳ را در مقابل محتوای موجود در ستون ۱ بگذارم؟؟
    لازم به ذکر است با کمک vlookup وقتی محتوا ستون ۲ و۳ را در مقابل ستون ۱ میگذارم، فقط اولین اسم (در ستون سوم) را میشناسد!! یعنی در مثال زیر فقط علی و شایان را شناخته و آنها را تکرار کرده و در مقابل ستون ۱ قرار میدهد!
    ستون ۳ ستون ۲ ستون ۱
    علی ۲۰ ۲۰
    شایان ۵۰ ۲۰
    رضا ۲۰ ۵۰
    اکبر ۵۰ ۵۰

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

      درود بر شما
      برای جستجوی موارد تکراری با فرمول مقاله زیر رو مطالعه کنید
      https://excelpedia.net/search-duplicates/

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

    با سلام و تشکر از سایت بسیار خوب و مفیدتون
    ببخشید لیستی دارم که اطلاعات کتابها رو (مثل نام کتاب، مولف و …) به صورت عمودی نوشته و اطلاعات کتابها یکسان نیستند، مثلا یک کتاب ۱۰ سطر اطلاعات داره و کتاب دیگه ۳ سطر، ولی پایان اطلاعات هر کتاب یک سطر با علامت خاص دارم که وجه تمایز بین کتابهاست.
    اگر بخوام اینها رو به صورت افقی در بیارم که تمام نام کتابها تو یه ستون بیاد و تمام نام مؤلفین تو یه ستون چه راهکاری وجود داره؟
    لطفاً کمکم کنید، ممنونم ازتون بی‌نهایت

    • آواتار
      حسنا خاکزاد ۱۳ مرداد ۱۳۹۹ / ۰:۰۵ ق٫ظ

      درود بر شما
      با توجه به متغیر بودن موارد، به نظر میرسه کدنویسی باید انجام بدید

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

        ببخشید میشه یه راهنمایی بفرمایید چطور باید کد بنویسم؟
        با تشکر

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

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

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

        بازم عذر میخوام اگه موارد متغیر نبود و تعداد سطرهای هر کتاب مساوی بود چه راهکاری وجود داشت؟
        بازم ممنون از پاسخگویی تون

        • آواتار
          حسنا خاکزاد ۱۳ مرداد ۱۳۹۹ / ۱۰:۳۶ ق٫ظ

          اگر ثابت باشه و مثلا ده تا ده تا باشه، و بخواید هر ده تا رو بیارید کنار هم
          میتونید از Index یا Offset استفاده کنید به این صورت که با درگ کردن به سمت راست، ده تا ده تا جابجا بشه. (یک رابطه ریاضی باید براش پیدا کنید و از column و row برای تولید عدد در فرمو لنویسی استفاده کنید
          مشابه این فرمول:
          با فرض اینکه داده ها در ستون A هستن و هر ده تا ردیف میخواد بیاد کنار هم:

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

    سلام وقت بخیر
    من یه جدول دارم پرسنل با افراد تحت تکفل (به صورت ستونی) که میخوام گزارش بگیرم که نفرات تحت تکفل هر نفر در یک سطر و جلوی نفر اصلی باشه.
    مثال
    —–نام———کد پرسنلی—-سمت
    بهزاد مرادی——–۱۰۰——–اصلی
    زهرا حسینی——————-همسر
    به این صورت بشه
    کد پرسنلی——–نام————–همسر
    ۱۰۰———-بهزاد مرادی——زهرا حسینی
    با این توضیح که شاید بعضی از پرسنل دارای چند فرزند باشند مثلا ۲یا۳دختر یا پسر

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

      سلام
      از پیوت تیبل استفاده کنید و فیلد های نام، کد پرسنلی و سمت رو در قسمت Row قرار بدید.

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

    سلام من با این فرمول توی یک ردیف اسامی را فراخان میکنم چرا بعضی اسامی را اشتباه میده
    HLOOKUP(S3,A3:R3,1,1) ارفع
    اپرداز اتکام اتکای اخابر ارفع اعتلا افرا افق البرز امید آرمان آریا آریان آسیا آینده بالاس بالبر بایکا آسیا
    به جای آسیا ارفع میاد

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

      درود
      ارگومان اخر رو صفر بذارید بجای یک

  • سمیرا ۸ تیر ۱۳۹۹ / ۱۲:۱۲ ب٫ظ

    اینطوری اگر کد رو بدم فقط نام رو میاره من میخام با یک کد همه اطلاعات رو بیار.بازهم معذرت

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

      خب باید با col_index این مسئله رو درست کنید
      اینکه هر بار کدوم ستون از دیتابیس رو نشون بده دست خودتونه. شما باید تعیین کنید
      بسته به جهت درگ کردن و ساختار دیتابیس میتونید اتومات هم بکنید

    • اسکندری ۵ مرداد ۱۳۹۹ / ۸:۰۹ ب٫ظ

      متشکرم بله دقیقا
      لطف کردید بسیار ممنون

  • سمیرا ۸ تیر ۱۳۹۹ / ۱۱:۴۱ ق٫ظ

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

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

      درود بر شما
      با vlookup میتونید کد پرسنلی رو هر جا سرچ کنید و داده های مرتبط رو بیارید

  • aria moqaddam ۵ تیر ۱۳۹۹ / ۱:۳۴ ب٫ظ

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

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

      درود
      رتبه بندی که با تابع rank انجام میشه
      اگر هم میخواید افراد رو پیدا کنید توابع small/ large
      اما شرط ها رو باید از طریق فرمول نویسی ارایه ای تعیین کنید
      یا ستون کمکی استفاده کنید

ارسال دیدگاه

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

توسط
تومان