متن فیلم

بعد از آشنایی با توابع IF، IFS، COUNTIF و SUMIF در این ویدیو به نحوه استفاده از توابع مرجع VLOOKUP و HLOOKUP می‌پردازیم. VLOOKUP یکی از رایج‌ترین توابع نوع مرجع در اکسل است. این تابع به شما امکان می‌دهد داده‌های ارجاع شده در یک جدول جستجو را پیدا کنید. این تابع مخفف جستجوی عمودی است و بنابراین، ابزاری مفید برای زمانی است که می‌خواهید چیزی را در یک جدول با یک محدوده بر اساس ستون پیدا کنید. به زودی، به HLOOKUP که مخفف جستجوی افقی است، نگاهی خواهیم انداخت که داده‌ها را بر اساس سطر جستجو می‌کند. VLOOKUP با استفاده از یک کلید مشترک بین داده‌های منبع و داده‌های جستجو در جدول جستجو کار می‌کند. یک فرمول جستجوی معمولی به شکل علامت مساوی =VLOOKUP(B3,A2:B12,2,FALSE) خواهد بود، که در آن B3 مقدار جستجو است. که مقدار یا کلمه‌ای است که به دنبال آن هستید. A2:B12 جدول یا محدوده جستجو است. این آرایه جدول یا محدوده سلول‌هایی است که حاوی مقدار جستجو هستند. در یک فرمول، اکسل این را به عنوان table_array ارجاع می‌دهد. جدول جستجو می‌تواند در همان برگه یا در یک برگه جداگانه دیگر باشد. عدد دوم شماره ستون جستجو است. این شماره ستونی در جدول جستجو است که حاوی مقدار مورد نظر شماست. در یک فرمول، اکسل این را به عنوان Col_index_num ارجاع می‌دهد. FALSE یک پارامتر اختیاری است که تعیین می‌کند آیا تطابق یافت شده باید دقیق باشد که با false نشان داده می‌شود یا می‌تواند تقریبی باشد که با true نشان داده می‌شود. در یک فرمول، اکسل این را به عنوان [range_lookup] ارجاع می‌دهد. براکت‌های دور این آرگومان در فرمول، نشان می‌دهند که این یک آرگومان اختیاری است، در حالی که بقیه آرگومان‌های اجباری فرمول VLOOKUP هستند. اگر پارامتر اختیاری false یا true را در فرمول خود مشخص نکنید، به طور پیش‌فرض روی true قرار می‌گیرد. این یک تطابق تقریبی الزامی است. همچنین می‌توانید از عدد صفر به جای false و عدد یک به جای true استفاده کنید. حالا بیایید تابع VLOOKUP را در عمل ببینیم. در کاربرگ فروش خودرو، فرض کنید می‌خواهیم یک لیست قیمت سریع از خودروهای مورد علاقه‌مان داشته باشیم. اولین کاری که باید انجام دهیم این است که ستون حاوی مقداری که می‌خواهیم جستجو کنیم را در ستون سمت چپ قرار دهیم، زیرا VLOOKUP این را الزامی می‌کند. سپس می‌توانیم ستون اصلی را حذف کنیم. سپس فرمولی را که در سلول V16 مشاهده می‌کنیم وارد می‌کنیم که به دنبال کلمه Corvette در آرایه جدول از سلول A2 تا G156 است. سپس به دنبال مقداری در ستون پنجم می‌گردیم، در این مورد، ستون قیمتی که با ردیف حاوی Corvette مطابقت دارد و مقدار دقیق 45705 دلار را برمی‌گرداند. توجه داشته باشید که در این مثال، ما از بخشی از جدول داده‌های موجود خود به عنوان جدول جستجو یا آرایه جدول استفاده می‌کنیم. بیایید آن را به صورت ارز ایالات متحده قالب‌بندی کنیم، سپس آن را به صفر رقم اعشار قالب‌بندی خواهیم کرد. در واقع، به جای استفاده از مرجع A25 در فرمول، استفاده از مرجع کلمه Corvette در جدول کوچک این برگه کاری که لیست ماشین‌های مورد علاقه ما در آن قرار دارد، آسان‌تر خواهد بود. این V5 است و فرمول هنوز هم کار می‌کند. حال، بیایید آن فرمول را تا جدول ماشین‌های مورد علاقه در بالای آن در برگه کاری کپی کنیم. اما مشکلی وجود دارد زیرا وقتی فرمول را کپی می‌کنیم، ارجاعات سلول تغییر می‌کند. این اتفاق افتاد زیرا همانطور که قبلاً در این دوره یاد گرفتیم، حالت پیش‌فرض ارجاعات سلول نسبی است و ما می‌خواهیم در این مورد آنها مطلق باشند. بیایید عملیات کپی را لغو کنیم. برای اینکه ارجاعات سلول‌ها مطلق شوند، باید به تمام ارجاعات سلول‌ها در فرمول، علامت دلار اضافه کنیم. این کار را می‌توان به صورت دستی انجام داد یا می‌توانید مکان‌نما را به نوبت در هر ارجاع سلول در فرمول قرار دهید و هر بار F4 را فشار دهید تا نمادهای دلار به طور خودکار اضافه شوند. بیایید دوباره فرمول را کپی کنیم. این بار، کار می‌کند. اگر از دسته پر کردن در سلول W5 برای کپی کردن آن به بقیه ماشین‌ها استفاده کنیم، کار نمی‌کند. در واقع، در هر سلول به نتیجه یکسانی می‌رسیم. چرا؟ زیرا هر کدام از آنها به سلول‌های یکسانی در مقدار جستجو ارجاع می‌دهند، زیرا ما از یک ارجاع مطلق استفاده کرده‌ایم. تنها کاری که اکنون باید انجام دهیم این است که فرمول را اصلاح کنیم تا با حذف نماد دلار، ارجاع مطلق را فقط برای پارامتر ستون در بخش مقدار جستجو در فرمول حذف کنیم. در سلول W5، مقدار $V$5 را به $V5 تغییر می‌دهیم. سپس وقتی دستگیره پر کردن را به پایین می‌کشیم، فرمول به درستی کپی می‌شود و تمام قیمت‌ها برای نشان دادن قیمت خرده‌فروشی صحیح خود تغییر می‌کنند. در نهایت، برای نشان دادن اینکه دو جدول اکنون توسط این تابع VLOOKUP به هم متصل شده‌اند، اگر قیمت خرده‌فروشی شورولت کوروت را در جدول داده‌های اصلی و سلول E25 تغییر دهیم، قیمت در لیست قیمت خودروهای مورد علاقه نیز تغییر خواهد کرد. حال بیایید نگاهی به تابع HLOOKUP بیندازیم، که همانطور که قبلاً اشاره کردیم، همان کار را انجام می‌دهد و تقریباً به همان روش تابع VLOOKUP کار می‌کند، اما به جای ستون‌ها، داده‌ها را در ردیف‌ها جستجو می‌کند. HLOOKUP به دنبال یک کلمه یا مقدار در ردیف بالای جدول می‌گردد و سپس مقداری را در همان ستون از ردیف مشخص شده در آرایه جدول برمی‌گرداند. بنابراین، اگر مقادیر مقایسه‌ای شما در یک ردیف در امتداد بالای جدول داده‌ها قرار داشته باشند، از HLOOKUP استفاده خواهید کرد. در مقابل، اگر مقادیر مقایسه شما در ستونی در سمت چپ داده‌هایی که می‌خواهید پیدا کنید قرار داشته باشند، همانطور که در وظیفه قبلی بودند، از VLOOKUP استفاده خواهید کرد. از بین این دو تابع، VLOOKUP به دلیل ماهیت اکثر جداول داده، بسیار بیشتر از HLOOKUP استفاده می‌شود. نحو HLOOKUP مشابه VLOOKUP است، با این تفاوت که شما یک شماره شاخص ردیف را مشخص می‌کنید که در فرمول اکسل به عنوان row_index_num به آن ارجاع داده می‌شود. این نشان دهنده شماره ردیف در جدول جستجو است که حاوی مقدار مورد نظر شماست. بیایید یک جدول جستجوی کوچک در سمت راست جدول داده اصلی خود ایجاد کنیم. چند ستون در این برگه کاری پنهان شده‌اند تا مشاهده کمی آسان‌تر شود. اکنون در ردیف بالای جدول جستجوی خود HP کم، HP متوسط و HP زیاد داریم. در مرحله بعد، نمادهای Wingdings را به عنوان رتبه‌بندی برای سه سطح قدرت اسب بخار اضافه خواهیم کرد. یک چهره غمگین برای رتبه اسب بخار پایین، دو چهره خنثی برای رتبه اسب بخار متوسط و سه چهره شاد برای رتبه اسب بخار بالا. حالا، بیایید یک ستون جدید در سمت راست ستون سطح اسب بخار اضافه کنیم و آن را رتبه اسب بخار بنامیم. سپس در سلول L2، تابع HLOOKUP را وارد می‌کنیم. این تابع به دنبال مقدار در سلول K2 می‌گردد که در این مورد HP متوسط است و آن را در محدوده سلولی از Y21 تا A22، که جدول جستجوی کوچک ما است، جستجو می‌کند. پاسخی را که در ردیف 2 جدول زیر HP متوسط پیدا می‌کند، برمی‌گرداند و از یک مقدار دقیق استفاده می‌کند. توجه داشته باشید که ما در این فرمول از برخی ارجاعات مطلق نیز استفاده کرده‌ایم. توجه داشته باشید که آنچه برگردانده می‌شود متن KK است، بنابراین باید سلول را با استفاده از فونت Wingdings قالب‌بندی کنیم. اکنون، وقتی روی دسته پر کردن دوبار کلیک می‌کنیم، کل ستون نمادهای رتبه اسب بخار مربوط به هر ردیف مقدار سطح اسب بخار را نشان می‌دهد و کار ما تمام است. اگرچه VLOOKUP و HLOOKUP هنوز هم به عنوان توابع بالفعل برای ارجاعات جستجو در اکسل استفاده می‌شوند، اما تابع جدیدتری به نام XLOOKUP وجود دارد. این نسخه فقط در نسخه‌های دسکتاپ اکسل از اکسل برای مایکروسافت ۳۶۵ و اکسل برای وب و همچنین در اکسل برای آیپد و آیفون و اکسل برای تبلت‌ها و تلفن‌های اندروید پشتیبانی می‌شود. XLOOKUP یک نسخه بهبود یافته و ترکیبی از VLOOKUP و HLOOKUP با هم است. این تابع می‌تواند در هر جهتی به صورت عمودی یا افقی کار کند. همچنین به جای یک آرایه جدول و یک شماره شاخص ستون یا ردیف، از آرایه جستجوی جداگانه و مقادیر آرایه بازگشتی استفاده می‌کند. در این ویدیو، نحوه استفاده از توابع VLOOKUP و HLOOKUP در اکسل را برای یافتن و اتصال به داده‌های ارجاع شده در جداول جستجوی عمودی و افقی یاد گرفتیم. در ویدیوهایی که در درس بعدی ارائه می‌شوند، به بررسی استفاده از جداول محوری در اکسل خواهیم پرداخت.