نحوه انجام VLOOKUP در یک صفحه گسترده اکسل

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

خلاصه عناوین

• VLOOKUP در اکسل چیست؟

• نحوه انجام VLOOKUP در اکسل

• نحوه اتصال صفحات اکسل با VLOOKUP

تعاریف کلیدی

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

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

VLOOKUP در اکسل چیست؟

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

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

سینتکس VLOOKUP به صورت زیر است:

=VLOOKUP(lookup_value, table_array, col_index_num[, exact])

این صفحه‌گسترده حاوی داده‌های نمونه برای نشان دادن نحوه اعمال VLOOKUP به مجموعه‌ای از سطرها و ستون‌ها است:

در این مثال، فرمول VLOOKUP به صورت زیر است:

=VLOOKUP(I3, A2:E26, 2)

بیایید ببینیم هر یک از این پارامترها به چه معناست:

• lookup_value مقداری است که می خواهید در جدول جستجو کنید. در این مثال، این مقدار در I3 است که 4 است.
• table_array محدوده ای است که شامل جدول است. در مثال، این A2:E26 است.
• col_index_num شماره ستون مقدار دلخواه برگشتی است. رنگ در ستون دوم table_array است، بنابراین این 2 است.
• exact یک پارامتر اختیاری است که تعیین می کند آیا مطابقت جستجو باید دقیق (FALSE) یا تقریبی (TRUE، پیش فرض) باشد.

می توانید از VLOOKUP برای نمایش مقدار برگشتی استفاده کنید یا آن را با سایر توابع ترکیب کنید تا از مقدار برگشتی در محاسبات بعدی استفاده کنید.

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

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

نحوه انجام VLOOKUP در اکسل

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

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

شما می توانید به راحتی با عملکرد VLOOKUP به این امر دست یابید. فقط به یاد داشته باشید که هر استدلال چه خواهد بود. در این مثال، از آنجایی که می‌خواهیم اطلاعات مربوط به مقدار را در سلول E6 جستجو کنیم، E6 همان lookup_value است. آرایه_table محدوده حاوی جدول است که A2:C9 است. شما باید فقط خود داده ها را بدون سرصفحه در ردیف اول قرار دهید.

در نهایت، col_index_num ستونی در جدول است که بررسی ها در آن قرار دارند، که ستون دوم است. با وجود این موارد، بیایید به نوشتن آن فرمول بپردازیم.

1. سلولی را که می خواهید نتایج را در آن نمایش دهید انتخاب کنید. در این مثال به سراغ F6 می رویم.
2. در نوار فرمول =VLOOKUP(.
3. سلول حاوی مقدار جستجو را برجسته کنید. این E6 در این مثال است که حاوی پیتزا است.
4. یک کاما (،) و یک فاصله تایپ کنید و سپس آرایه جدول را برجسته کنید. که در این مثال A2:C9 است.
5. سپس شماره ستون مقدار مورد نظر را تایپ کنید. بررسی ها در ستون دوم هستند، بنابراین برای این مثال 2 است.
6. برای آخرین آرگومان، اگر می‌خواهید مطابقت دقیق موردی که وارد کرده‌اید، FALSE را تایپ کنید. در غیر این صورت، TRUE را تایپ کنید تا نزدیکترین تطابق آن را ببینید.
7. فرمول را با یک پرانتز بسته پایان دهید. به یاد داشته باشید که بین آرگومان ها کاما قرار دهید. فرمول نهایی شما باید به شکل زیر باشد:=VLOOKUP(E6,A2:C9,2,FALSE)
8. Enter را بزنید تا نتیجه خود را دریافت کنید.

=VLOOKUP(E6,A2:C9,2,FALSE)

اکسل اکنون بررسی های پیتزا را در سلول F6 برمی گرداند.

نحوه انجام VLOOKUP برای چندین آیتم در اکسل

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

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

1. سلول کنار اولین مورد را انتخاب کنید. در این مثال F6 است.
2. فرمول VLOOKUP را برای اولین مورد تایپ کنید.
3. مکان نما خود را به آرگومان آرایه جدول در فرمول ببرید و F4 را روی صفحه کلید خود فشار دهید تا به یک مرجع مطلق تبدیل شود.
4. فرمول نهایی شما باید به شکل زیر باشد:=VLOOKUP(E6,$A$2:$C$9,2,FALSE)
5. برای دریافت نتایج کلید Enter را فشار دهید.
6. هنگامی که نتیجه ظاهر شد، سلول نتایج را به پایین بکشید تا فرمول برای همه موارد دیگر پر شود.

=VLOOKUP(E6,$A$2:$C$9,2,FALSE)

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

نحوه اتصال صفحات اکسل با VLOOKUP

همچنین می توانید با استفاده از VLOOKUP اطلاعات را از یک صفحه اکسل به صفحه دیگر بکشید. این به ویژه در صورتی مفید است که می‌خواهید جدول اصلی را بی‌تأثیر بگذارید، مانند زمانی که داده‌ها را از یک وب‌سایت به اکسل وارد کرده‌اید.

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

1. سلولی را که می خواهید قیمت کالای اول را در آن نمایش دهید انتخاب کنید. در این مثال B3 است.
2. برای شروع فرمول =VLOOKUP( را در نوار فرمول تایپ کنید.
3. روی سلول حاوی نام اولین مورد کلیک کنید تا به عنوان مقدار جستجو اضافه شود. در این مثال A3 (شکلات) است.
4. مانند قبل، یک کاما (،) و یک فاصله تایپ کنید تا به آرگومان بعدی بروید.
5. به صفحه اصلی بروید و آرایه جدول را انتخاب کنید.

1. F4 را بزنید تا مرجع مطلق شود. این باعث می شود که فرمول برای بقیه موارد قابل اجرا باشد.
2. در حالی که هنوز در صفحه اصلی هستید، به نوار فرمول نگاه کنید و شماره ستون را برای ستون قیمت تایپ کنید. در این مثال 3 است.
3. FALSE را تایپ کنید زیرا در این مورد می خواهید مطابقت دقیق هر محصول داشته باشید.
4. پرانتز را ببندید و Enter را بزنید. فرمول نهایی باید مانند زیر باشد:=VLOOKUP(A3, Sheet1!$A$2:$C$9, 3, FALSE)
5. نتیجه را برای اولین محصول به پایین بکشید تا نتایج را برای سایر محصولات در برگه زیر مجموعه مشاهده کنید.

=VLOOKUP(A3, Sheet1!$A$2:$C$9, 3, FALSE)

اکنون می توانید داده های اکسل خود را بدون تأثیر بر جدول اصلی مرتب کنید. اشتباهات تایپی یکی از دلایل رایج خطاهای VLOOKUP در اکسل است، بنابراین مطمئن شوید که از آنها در لیست جدید اجتناب کنید.

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

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

منبع مقاله