وظایفی که در آزمون سراسری روسیه خواهند بود. آنچه شما باید در مورد آزمون سراسری روسی (VPR) بدانید

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

بسیار راحت و اغلب استفاده می شود. زیرا تطبیق دستی محدوده ها با ده ها هزار مورد مشکل ساز است.

نحوه استفاده از تابع VLOOKUP در اکسل

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

هزینه مواد در لیست قیمت موجود است. این یک جدول جداگانه است.

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

الگوریتم اقدامات:

روی OK کلیک کنید. و سپس تابع را در کل ستون "ضرب" می کنیم: گوشه پایین سمت راست را با ماوس بگیرید و آن را به پایین بکشید. نتیجه لازم را می گیریم.

اکنون یافتن هزینه مواد دشوار نیست: مقدار * قیمت.

تابع VLOOKUP دو جدول را به هم مرتبط کرد. اگر قیمت تغییر کند، هزینه مواد دریافتی در انبار (امروز وارد شده) نیز تغییر خواهد کرد. برای جلوگیری از این، از Paste Special استفاده کنید.

1. ستون را با قیمت های درج شده انتخاب کنید.
2. دکمه سمت راست ماوس - "کپی".
3. بدون حذف انتخاب، دکمه سمت راست ماوس - "Paste Special".
4. کادر کنار «ارزش‌ها» را علامت بزنید. خوب.

فرمول موجود در سلول ها ناپدید می شود. فقط مقادیر باقی خواهند ماند.



به سرعت دو جدول را با استفاده از VLOOKUP مقایسه کنید

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

داده های ارائه شده به این روش را می توان با هم مقایسه کرد. تفاوت های عددی و درصدی را پیدا کنید.

تابع VLOOKUP در اکسل با شرایط متعدد

تا به حال، ما فقط یک شرط را برای تجزیه و تحلیل ارائه کرده ایم - نام ماده. در عمل، اغلب لازم است چندین محدوده با داده ها مقایسه شود و یک مقدار بر اساس 2، 3 و غیره انتخاب شود. شاخص.

جدول نمونه:

فرض کنید باید بفهمیم به چه قیمتی آورده اند مقوا راه راهاز JSC "Vostok". شما باید دو شرط جستجو را بر اساس نام مواد و بر اساس تامین کننده تنظیم کنید.

موضوع با این واقعیت پیچیده می شود که چندین کالا از یک تامین کننده می آیند.

بیایید به فرمول با جزئیات نگاه کنیم:

1. ما دنبال چی میگردیم؟
2. به کجا نگاه می کنیم؟
3. چه داده هایی را می گیریم؟

تابع VLOOKUP و لیست کشویی

بیایید بگوییم که ما برخی از داده ها را در قالب یک لیست کشویی داریم. در مثال ما - "مواد". لازم است عملکرد را طوری پیکربندی کنید که هنگام انتخاب نام، قیمت ظاهر شود.

ابتدا یک لیست کشویی ایجاد می کنیم:

اکنون باید مطمئن شوید که وقتی ماده خاصی را انتخاب می کنید، رقم مربوطه در ستون قیمت ظاهر می شود. مکان نما را در سلول E9 (جایی که قیمت باید ظاهر شود) قرار دهید.

1. "Function Wizard" را باز کرده و VLOOKUP را انتخاب کنید.
2. اولین آرگومان "مقدار جستجو" است - یک سلول با یک لیست کشویی. جدول – محدوده با نام مواد و قیمت. ستون، به ترتیب، 2. تابع به شکل زیر است: .
3. ENTER را فشار دهید و از نتیجه لذت ببرید.

ما مواد را تغییر می دهیم - قیمت تغییر می کند:

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

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

با استفاده از تابع COLUMN برای تعیین ستون استخراج

اگر جدولی که داده‌ها را با استفاده از VLOOKUP بازیابی می‌کنید، ساختاری مشابه جدول جستجو دارد، اما به سادگی دارای ردیف‌های کمتری است، VLOOKUP می‌تواند از تابع ()COLUMN برای محاسبه خودکار اعداد ستون‌ها برای بازیابی استفاده کند. در این صورت تمام فرمول های VLOOKUP یکسان خواهند بود (تنظیم شده برای پارامتر اول که به طور خودکار تغییر می کند)! لطفاً توجه داشته باشید که پارامتر اول دارای مختصات ستون مطلق است.

ایجاد یک کلید ترکیبی با استفاده از &»|»&

اگر نیاز به جستجو در چندین ستون به طور همزمان وجود داشته باشد، لازم است یک کلید ترکیبی برای جستجو ایجاد شود. اگر مقدار بازگشتی متنی نباشد (همانطور که در قسمت "کد" وجود دارد)، بلکه عددی باشد، فرمول راحت‌تر SUMIFS برای این کار مناسب است و کلید ستون مرکب اصلاً مورد نیاز نخواهد بود.

این اولین مقاله من برای Lifehacker است. اگر آن را دوست داشتید، پس از شما دعوت می کنم از وب سایت من دیدن کنید و همچنین خوشحال می شوم در نظرات در مورد اسرار شما در استفاده از عملکرد VLOOKUP و موارد مشابه بخوانم. متشکرم. :)

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

1. جداول باید در همان کتاب کار اکسل قرار گیرند.
2. شما فقط می توانید در میان داده های ثابت (نه فرمول ها) جستجو کنید.
3. عبارت جستجو باید در ستون اول داده های استفاده شده باشد.

فرمول VLOOKUP در اکسل

نحو VLOOKUP در اکسل روسی شده به صورت زیر است:

VLOOKUP (معیار جستجو، محدوده داده، شماره ستون با نتیجه، شرایط جستجو)

آرگومان های مورد نیاز برای جستجوی نتیجه نهایی در داخل پرانتز نشان داده شده است.

ضوابط جستجو

آدرس سلول صفحه اکسل، که داده های مورد جستجو در جدول را مشخص می کند.

محدوده داده ها

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

شماره ستون برای ارزش کل

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

شرط جستجو

یک مقدار بولی (true/1 یا false/0) که مشخص می کند آیا باید به دنبال تطبیق تقریبی (1) یا تطابق دقیق (0) بود.

VLOOKUP در اکسل: نمونه های تابع

اصل عملکرد تابع ساده است. آرگومان اول شامل معیارهای جستجو است. به محض یافتن یک تطابق در جدول (آگومان دوم)، اطلاعات از ستون مورد نظر (آگومان سوم) ردیف یافت شده گرفته شده و با فرمول وارد سلول می شود.
یک استفاده ساده از VLOOKUP، جستجوی مقادیر در جدول اکسل است. در حجم زیادی از داده ها معنی دارد.

بیایید تعداد محصولاتی را که در واقع به نام ماه عرضه شده اند، پیدا کنیم.
نتیجه در سمت راست جدول نمایش داده می شود. در سلول با آدرس H3 مقدار مورد نظر را وارد می کنیم. در مثال، نام ماه در اینجا نشان داده می شود.
در سلول H4 خود تابع را وارد می کنیم. این را می توان به صورت دستی انجام داد یا می توانید از یک جادوگر استفاده کنید. برای تماس، نشانگر را روی سلول H4 قرار دهید و روی نماد Fx در کنار نوار فرمول کلیک کنید.


پنجره اکسل Function Wizard باز می شود. باید یک VLOOKUP در آن پیدا کنید. "فول لیست حروف الفبا" را از لیست کشویی انتخاب کنید و شروع به تایپ VLOOKUP کنید. تابع یافت شده را هایلایت کرده و روی OK کلیک کنید.


یک پنجره VLOOKUP برای ظاهر می شود جداول اکسل.


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


به محض اتمام انتخاب و رها کردن دکمه سمت چپ ماوس، پنجره به حالت عادی خود باز می گردد و آدرس محدوده در خط دوم ظاهر می شود. از سلول بالا سمت چپ تا سلول پایین سمت راست محاسبه می شود. آدرس های آنها توسط عملگر ":" از هم جدا می شوند - همه آدرس ها بین اولین و آخرین گرفته می شوند.


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


خط آخر را خالی بگذارید. به طور پیش فرض مقدار 1 خواهد بود، بیایید ببینیم تابع ما چه مقداری را خروجی می دهد. روی OK کلیک کنید.


نتیجه دلسرد کننده است. "N/A" به معنای داده های نامعتبر برای تابع است. ما مقداری را در سلول H3 مشخص نکرده‌ایم و تابع به دنبال یک مقدار خالی است.


نام ماه را وارد کنید و مقدار تغییر خواهد کرد.


فقط با واقعیت مطابقت ندارد، زیرا تعداد واقعی محصولات تولید شده در ژانویه 2000 است.
این اثر آرگومان Search Condition است. بیایید آن را به 0 تغییر دهیم. برای این کار، نشانگر را روی سلول با فرمول قرار دهید و دوباره Fx را فشار دهید. در پنجره ای که باز می شود، در خط آخر "0" را وارد کنید.


روی "OK" کلیک کنید. همانطور که می بینید، نتیجه تغییر کرده است.


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


روی OK کلیک کنید. همانطور که می بینید، نتیجه جستجو 0 بود، اگرچه مقدار در جدول 85٪ است.


VLOOKUP در اکسل فقط مقادیر ثابت را می فهمد.

مقایسه داده های دو جدول اکسل

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

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

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

در سلول B2 تابع VLOOKUP را وارد می کنیم. به عنوان اولین آرگومان، سلول را با ماه در برگه فعلی نشان می دهیم و محدوده را از برگه "Workshop1" انتخاب می کنیم. برای جلوگیری از تغییر دامنه هنگام کپی کردن، پس از انتخاب محدوده، F4 را فشار دهید. این لینک را مطلق می کند.


فرمول را گسترش دهید تا کل ستون را پوشش دهد.

به طور مشابه، فرمول را در ستون بعدی وارد کنید، فقط محدوده را در برگه "Workshop2" انتخاب کنید.


پس از کپی، یک گزارش خلاصه از دو شیت دریافت خواهید کرد.

جایگزینی داده ها از یک جدول اکسل به جدول دیگر

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


و در سلول G3 تابع VLOOKUP را قرار دهید. باز هم محدوده را از ورق مجاور می گیریم.


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


این تمام اطلاعات مربوط به تابع نامرئی اما مفید VLOOKUP در اکسل برای dummies است. امیدواریم در حل مشکلات به شما کمک کند.

روز خوبی داشته باشی!

کار با یک جدول خلاصه شامل کشیدن مقادیر از جداول دیگر به داخل آن است. اگر جداول زیادی وجود داشته باشد، انتقال دستی زمان زیادی می برد و اگر داده ها به طور مداوم به روز شوند، این یک کار سیزیفی خواهد بود. خوشبختانه، یک تابع VLOOKUP وجود دارد که توانایی واکشی خودکار داده ها را ارائه می دهد. در نظر بگیریم نمونه های خاصعملکرد این تابع

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

مثالی از استفاده از VLOOKUP

بیایید نگاهی به نحوه عملکرد تابع VLOOKUP با استفاده از یک مثال خاص بیندازیم.

ما دو میز داریم. اولین آنها یک میز خرید است که نام محصولات غذایی در آن قرار داده شده است. ستون بعدی بعد از نام شامل مقدار محصولی است که باید خریداری شود. بعد قیمت می آید. و در ستون آخر - کل هزینه خرید یک محصول خاص که با استفاده از فرمولی که قبلاً در سلول برای ضرب مقدار در قیمت وارد شده است محاسبه می شود. اما ما فقط باید قیمت را با استفاده از تابع VLOOKUP از جدول مجاور، که یک لیست قیمت است، افزایش دهیم.

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

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

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

برگشت