پث‌وایز

آموزش اکسل و گوگل شیتدرس ۶ از ۱۲

توابع جستجو در اکسل: کشیدن داده با XLOOKUP

کشیدن قیمت، اسم یا شماره تلفن از یه جدول دیگه با کدش؛ با XLOOKUP یا توی اکسل‌های قدیمی با VLOOKUP.

چیزی که از این درس می‌بری

  • تابع جستجو یه مقدار رو توی یه جدول پیدا می‌کنه و مقدار معادلش رو از یه ستون دیگه برمی‌گردونه.
  • فرمول =XLOOKUP(find, where, return, "Not found") توی اکسل ۲۰۲۱، مایکروسافت ۳۶۵ و گوگل شیت کار می‌کنه.
  • توی اکسل‌های قدیمی‌تر از =VLOOKUP(find, range, column number, FALSE) استفاده کن؛ هیچ‌وقت بدون FALSE ننویس.
فهرست این درس۵
  1. اینو پیدا کن، اونو بیار
  2. XLOOKUP یا VLOOKUP؟
  3. وقتی جستجو چیزی پیدا نمی‌کنه
  4. چک‌لیست توابع جستجو
  5. مرور درس

۰۱ایده‌ی اصلی

اینو پیدا کن، اونو بیار

هر جستجو سه تا چیز می‌خواد: دنبال چی بگرده (کد این ردیف)، کجا بگرده (ستون کدها توی اون یکی جدول)، و چی رو برگردونه (ستون قیمت کنار همون کد).

توی ردیف سفارش نوشته P-104. توی لیست قیمت، P-104 روی ردیفیه که قیمتش ۲۳۰٬۰۰۰ هست. تابع جستجو ۲۳۰٬۰۰۰ رو برمی‌گردونه.

مثال

لیست قیمت: کدها توی G2:G50، قیمت‌ها توی H2:H50
سفارش ردیف ۲: کد کالا توی B2

=XLOOKUP(B2, G2:G50, H2:H50, "Not found")

خروجی

230000

این‌طوری بخونش: B2 رو توی کدها پیدا کن، قیمت معادلش رو بیار، وگرنه بگو Not found. قبل از کشیدن به پایین، محدوده‌ها رو قفل کن ($G$2:$G$50، $H$2:$H$50).

خودت رو امتحان کنسؤال ۱ از ۴

کد پرسنلی کارمندها توی ستون A شیت Staff و شماره تلفن‌ها توی ستون D هستن. شیرین یه کد پرسنلی توی سلول B2 گزارشش داره. کدوم فرمول شماره تلفن رو میاره؟

  1. =XLOOKUP(B2, Staff!D:D, Staff!A:A)
  2. =XLOOKUP(Staff!A:A, B2, Staff!D:D)
  3. =XLOOKUP(B2, Staff!A:A, Staff!D:D)
  4. =SUMIF(Staff!A:A, B2, Staff!D:D)
جواب رو ببین

=XLOOKUP(B2, Staff!A:A, Staff!D:D)

درسته: B2 رو پیدا کن، توی ستون کد پرسنلی (Staff!A:A)، و ستون تلفن (Staff!D:D) رو برگردون. عبارت Staff! خیلی ساده یعنی «توی شیت Staff».

XLOOKUP یا VLOOKUP؟

XLOOKUP

توی اکسل ۲۰۲۱، مایکروسافت ۳۶۵ و گوگل شیت هست. پیش‌فرضش جستجوی دقیقه، ستون سمت چپ یا راست رو می‌تونه بخونه، و می‌ذاره پیام «پیدا نشد» دلخواه خودت رو بذاری.

VLOOKUP

توی همه‌ی نسخه‌ها، از جمله اکسل‌های قدیمی مثل ۲۰۱۶ و ۲۰۱۹ هست. فقط ستون‌های سمت راستِ ستون کد رو می‌بینه و برای جستجوی دقیق حتماً باید FALSE آخرش بذاری.

مثال

همین کار با VLOOKUP (کد توی G، قیمت توی H):

=VLOOKUP(B2, $G$2:$H$50, 2, FALSE)

خروجی

230000

عدد ۲ یعنی «ستون دوم محدوده» (H). مقدار FALSE یعنی جستجوی دقیق؛ اگه ننویسیش، ممکنه VLOOKUP بی‌صدا ردیف اشتباهی رو برگردونه.

خودت رو امتحان کنسؤال ۲ از ۴

فرمول =VLOOKUP(B2, $G$2:$H$50, 2) بدون FALSE آخرش، برای جستجوی کد کالا امنه و مشکلی نداره.

  1. درست
  2. نادرست
جواب رو ببین

نادرست

غلطه. بدون FALSE، تابع VLOOKUP جستجوی تقریبی انجام می‌ده و ممکنه بدون هیچ خطایی قیمت ردیف بغلی رو برگردونه. برای کدها، اسم‌ها و شناسه‌ها همیشه فرمول رو با FALSE تموم کن.

۰۳خطای #N/A

وقتی جستجو چیزی پیدا نمی‌کنه

خطای #N/A یعنی «تطابق دقیقی پیدا نشد». معمولاً یا واقعاً اون کد توی لیست نیست، یا دو تا کد دقیقاً مثل هم نیستن: یه فاصله‌ی اضافی، «P-104» در برابر «P104»، یا عددی که توی یه جدول به صورت متن ذخیره شده و توی جدول دیگه به صورت عدد.

خودت رو امتحان کنسؤال ۳ از ۴

تابع جستجو پیداش می‌کنه یا خطای #N/A می‌ده؟

پیدا می‌کنهخطای #N/A

  • دنبال P-104 می‌گرده، توی لیست هم P-104 هست
  • دنبال P-104 می‌گرده، ولی توی لیست P104 هست
  • دنبال «P-104 » با یه فاصله‌ی اضافی آخرش می‌گرده
  • دنبال p-104 با حروف کوچیک می‌گرده، توی لیست P-104 هست
  • دنبال 1050 به عنوان عدد می‌گرده، ولی توی لیست «1050» متنی ذخیره شده
جواب رو ببین

پیدا می‌کنه: دنبال P-104 می‌گرده، توی لیست هم P-104 هست, دنبال p-104 با حروف کوچیک می‌گرده، توی لیست P-104 هست

خطای #N/A: دنبال P-104 می‌گرده، ولی توی لیست P104 هست, دنبال «P-104 » با یه فاصله‌ی اضافی آخرش می‌گرده, دنبال 1050 به عنوان عدد می‌گرده، ولی توی لیست «1050» متنی ذخیره شده

چک‌لیست توابع جستجو

  • با یه کد یکتا (کد پرسنلی، کد محصول) جستجو کن، نه با اسمی که ممکنه تکراری باشه.
  • قبل از کشیدن فرمول به پایین، محدوده‌ها رو با $ قفل کن.
  • اگه اکسل جدید داری از XLOOKUP استفاده کن؛ توی VLOOKUP همیشه فرمول رو با FALSE تموم کن.
  • موقع خطای #N/A، دو تا کد رو کاراکتر به کاراکتر مقایسه کن: فاصله‌ها، خط تیره‌ها و عدد در برابر متن.

خودت رو امتحان کنسؤال ۴ از ۴

فرمول VLOOKUP کاوه توی ردیف ۲ درست کار می‌کنه، ولی بعد از کشیدن به پایین، توی بیشتر ردیف‌ها خطای #N/A می‌ده؛ با این‌که کدها واقعاً توی لیست قیمت هستن. محتمل‌ترین دلیل چیه؟

  1. محدوده‌ی لیست قیمت قفل نشده بود، برای همین موقع کشیدن به پایین سُر خورده و کدهای بالا از محدوده بیرون افتادن
  2. تابع VLOOKUP نمی‌تونه بیشتر از ۲۰ ردیف رو جواب بده
  3. به جای FALSE باید TRUE می‌ذاشت
  4. قیمت‌ها به صورت متنی ذخیره شدن
جواب رو ببین

محدوده‌ی لیست قیمت قفل نشده بود، برای همین موقع کشیدن به پایین سُر خورده و کدهای بالا از محدوده بیرون افتادن

دقیقاً. وقتی قفل نباشه، G2:H50 می‌شه G3:H51، G4:H52 … و کدهای بالای جدول از محدوده بیرون می‌افتن. نوشتن $G$2:$H$50 درستش می‌کنه.

۰۵جمع‌بندی

مرور درس

  • تابع جستجو یه مقدار رو توی یه جدول پیدا می‌کنه و مقدار معادلش رو از یه ستون دیگه برمی‌گردونه.
  • فرمول =XLOOKUP(find, where, return, "Not found") توی اکسل ۲۰۲۱، مایکروسافت ۳۶۵ و گوگل شیت کار می‌کنه.
  • توی اکسل‌های قدیمی‌تر از =VLOOKUP(find, range, column number, FALSE) استفاده کن؛ هیچ‌وقت بدون FALSE ننویس.
  • خطای #N/A یعنی تطابق دقیقی پیدا نشد: فاصله‌ها، خط تیره‌ها و عدد در برابر متن رو چک کن.

به سبک خودت یادش بگیر.

توی اپ از یادآی بخواه این درس رو ساده‌تر توضیح بده، از زندگی خودت مثال بزنه یا عمیق‌تر بره. بعد یه مسیر کامل دور هدف خودت بساز.

Cafe Bazaar Myket توی اپ شخصی‌سازیش کن