راهنمای مبتدیان برای VLOOKUP در مقابل جداول محوری

برای همه موارد Excel در کانال Slack ما سؤال بپرسید یا به گفتگو بپیوندید.

به عنوان یک کاربر اکسل، احتمالاً در مورد دو مورد از شناخته شده ترین ویژگی های اکسل شنیده اید: تابع VLOOKUP و Pivot Tables.

اما اگر قبلاً زیاد از آنها استفاده نکرده اید، ممکن است تعجب کنید: چه زمانی از VLOOKUP در مقابل Pivot Tables استفاده می کنید؟و برای چه باید از آنها استفاده کرد؟

در این مقاله به بررسی VLOOKUP، Pivot Tables و همچنین Power Pivot می پردازیم و اینکه چه زمانی باید از هر یک از آنها برای بهترین نتیجه استفاده کنید.

فایل تمرین رایگان خود را دانلود کنید

با دانلود این فایل تمرینی مراحل مقاله را دنبال کنید.

VLOOKUP چیست؟

VLOOKUP یک تابع جستجو و مرجع در اکسل است.

معمولاً در یک کاربرگ برای جستجو و استخراج داده ها از جدول یا کاربرگ اکسل دیگر استفاده می شود.

به عنوان مثال، ما یک جدول اکسل به نام "فروش" داریم که شامل جزئیات فروش محصولات برای تمام ماه های سال است.

و جدول دیگری به نام "محصولات" با جزئیات محصول.

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

با این حال، جدول "فروش" جزئیات مربوط به دسته بندی محصولات را ندارد. این اطلاعات در جدول "محصولات" ذخیره می شود.

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

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

تابع VLOOKUP چهار آرگومان دارد (اطلاعاتی که نیاز دارد).

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

Lookup Value همان ارزشی است که شما به دنبال آن هستید. این شناسه دسته در مثال ما است.

جدول آرایه جدولی است که برای جستجوی این اطلاعات به آن نیاز داریم. این جدول "محصولات" خواهد بود.

Col Index Num شماره ستون جدول حاوی اطلاعاتی است که باید برگردانده شود. این ستون حاوی دسته است که ستون دوم است. بنابراین ما از 2 استفاده خواهیم کرد.

Range Lookup نوع جستجویی است که شما انجام می دهید. آیا به دنبال محدوده ها هستید؟در مثال ما اینطور نیستیم، زیرا به دنبال شناسه دسته بندی خاصی هستیم.

فرمول زیر به جدول "فروش" در ستون F اضافه می شود.

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

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

Pivot Table چیست؟

Pivot Table یک ابزار گزارش دهی در اکسل است که داده ها را خلاصه می کند و یک تجمیع مقادیر را انجام می دهد.

به عنوان مثال ، برای نشان دادن کل فروش در ماه یا تعداد سفارشات برای هر محصول.

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

نحوه ایجاد یک جدول محوری

با استفاده از ستون دسته بندی در جدول "فروش" ، می توانیم جدول محوری را ایجاد کنیم تا کل فروش برای هر دسته از محصولات را نشان دهیم.

Click in the “Sales” table, then click Insert>جدول محوری .

جدول "فروش" به عنوان منبع داده مورد استفاده انتخاب می شود. و گزینه پیش فرض وارد کردن جدول محوری در یک برگه جدید است.

برگه جدید درج شده و Pivottable روی آن قرار می گیرد. یک لیست فیلد در سمت راست با تمام ستون های جدول "فروش" نشان داده شده است.

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

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

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

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

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

جداول محوری ابزاری قدرتمند اکسل است. برخی از تکنیک های جدول Advanced Pivot را بررسی کنید.

Power Pivot چیست؟

Power Pivot همچنین به عنوان مدل داده در اکسل گفته می شود.

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

Power Pivot یک ویژگی پیشرفته از جمله زبان فرمول خاص خود به نام DAX است. می توانید در این راهنمای جامع برای Power Pivot اطلاعات بیشتری در مورد آن کسب کنید.

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

به جای استفاده از فرمول جستجو برای ادغام داده ها از چندین جدول به یک ، می توانید آنها را در جداول خود نگه دارید و از Power Pivot برای ارتباط آنها استفاده کنید.

سپس می توانید یک جدول محوری از تمام جداول مرتبط (مدل داده) ایجاد کنید.

جداول را در محوری برق بارگذاری کنید

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

Click in the “Sales” table and click Data>از جدول/محدوده.

این پنجره ویرایشگر Power Query را باز می کند.

Power Query ابزاری برای ایجاد تحولات قدرتمند به داده های شما است تا آن را برای تجزیه و تحلیل آماده کند. برای تهیه داده ها برای Power Pivot بسیار عالی است.

این داده ها تمیز است و نیازی به تحول ندارد ، بنابراین می توان مستقیماً در مدل داده بارگذاری کرد.

Click Home> Close & Load>بستن و بارگیری به.

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

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

این مراحل را برای جدول "محصولات" تکرار کنید.

هر دو جدول در مدل داده بارگذاری شده و در صفحه نمایش داده شد و اتصالات قابل مشاهده هستند.

در مدل روابط بین جداول ایجاد کنید

اکنون برای ایجاد رابطه بین دو جدول ، باید پنجره Power Pivot را باز کنیم.

Click Data>مدل داده را مدیریت کنید.

پنجره Power Pivot for Excel باز می شود و شما را به نمای داده می برد.

به نظر می رسد یک کتاب کار اکسل (اما اینطور نیست). دو برگه در پایین صفحه نمایش دو جدول هستند که در مدل داده بارگذاری شده اند.

روی دکمه نمودار نمایش کلیک کنید.

از قسمت "شناسه دسته" در "فروش" به قسمت "ID" در "محصولات" کلیک کرده و بکشید

وقتی موش خود را آزاد می کنید ، رابطه ایجاد می شود.

یک خط بین دو میز با 1 در سمت "محصولات" و نماد بی نهایت در سمت "فروش" ترسیم شده است.

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

پنجره Power Pivot را ببندید.

یک جدول محوری از یک مدل داده Pivot Power ایجاد کنید

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

Click Insert>جدول محوری .

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

جدول Pivot ایجاد شده و لیست فیلد ظاهر می شود.

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

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

قسمت "دسته" را از جدول "محصولات" به ردیف بکشید. و سپس قسمت "کل" را از جدول "فروش" به "ارزش" بکشید

مقادیر را قالب بندی کنید و ما همان نتایج جدول محوری را مانند گذشته داریم.

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

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

vlookup برای کشیدن داده ها از جدول محوری

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

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

این پاسخ صحیح را برمی گرداند.

با این حال ، VLOOKUP از مرجع محدوده سلول A4: B6 استفاده می کند. و اگرچه این کار می کند ، اگر Pivottable تغییر یابد ، VLOOKUP خراب می شود.

این واقعاً داده ها را از یک جدول محوری بیرون می کشد ، بلکه آن را از محدوده سلول بیرون می کشد.

Vlookup vs getPivotData

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

جداول محوری ابزاری پویا است ، اما Vlookup چنین نبود.

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

برای استفاده از این عملکرد ، تایپ کرده و سپس روی یک سلول در جدول محوری کلیک کنید.

عملکرد GetPivotData هر زمان که روی یک سلول در جدول Pivot از یک فرمول کلیک کنید ، به طور خودکار ایجاد می شود.

این مثال با استفاده از جدول Pivot ایجاد شده از مدل داده است.

عملکرد GetPivotData به دنبال مقدار در ستون "جمع کل" و برای دسته غذا است.

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

بسته شدن

جداول Vlookup و Pivot دو ویژگی هستند که یکدیگر را تکمیل می کنند. بنابراین این یک مورد بیشتر از نحوه استفاده از آنها در کنار هم است ، نه اینکه جدول Vlookup vs Pivot را بچسبانید.

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

Power Pivot با ارتباط با جداول مختلف ، یک روش جایگزین برای این کار ارائه می دهد تا سپس جداول محوری از آن ایجاد شود.

فایل تمرین رایگان خود را دانلود کنید

با دانلود این فایل تمرینی مراحل مقاله را دنبال کنید.

با Goskills بیشتر بدانید

آیا می خواهید در مورد اکسل اطلاعات بیشتری کسب کنید؟دوره های جامع آنلاین اکسل Goskills را بررسی کنید.

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

امروز برای یک آزمایش رایگان 7 روزه ثبت نام کنید تا تمام دوره های Goskills را امتحان کنید ، از جمله دوره های برنده جایزه ما.