آموزش تابع TRANSPOSE و تابع VLOOKUP در اکسل

تابع TRANSPOSE(ترنسپوز) در اکسل چیست؟

 

 

تابع TRANSPOSE و تابع VLOOKUP در اکسل

 

آموزش تابع Transpose و تابع Vlookup : این تابع داده های افقی را به عمودی و عمودی را به افقی تبدیل میکند. فرض کنید اطلاعات زیر در اکسل در دسترس است.

 

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

 

سپس در زبانه FORMULAS از بخش توابع LOOKUP & REFERENCE تابع TRANSPOSE را فرامیخوانیم.

 

 

در کادر ظاهر شده این تابع همانند تصویر زیر محدوده جدول اولیه (در این مثال A1 تا E2) را وارد کنید و در پایان روی OK کلیک کنید.

 

 

پس از انتخاب گزینه OK جدول حاصل بصورت زیر است.

حال با کلیک بر کلید F2 فرمول این سلول باز میشود.

 

 

حالا ترکیب سه کلید CTRL+SHIFT+ENTER را بفشارید. خواهید دید جدول اولیه به حالت معکوس تشکیل شده است.

 

 

مظلب مرتبط برای شما :  آموزش تابع MAX و IF در نرم افزار اکسل(Excel) + [مثال واقعی]

تابع VLOOKUP در نرم افزار Excel

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

مثال برای تابع Vlookup در نرم افزار اکسل

اطلاعات کاری کارکنان یک شرکت در یک ماه در چند جدول بصورت زیر در اکسل در دسترس است.

 

 

میخواهیم در سلول E8 فرمولی بنویسیم که حقوق فردی را که نام او در سلول D8 نوشته میشود را محاسبه کند .فرمول محاسبه حقوق بصورت زیر است:

حق اولاد + (نرخ هر ساعت کار × ساعت کار)

برای این کار در سلول E8 تابع VLOOKUP را فرامیخوانیم. کادر این تابع بصورت زیر است.

 

 

در کادر LOOKUP_VALUE باید مورد جستوجو وارد شود. در این مثال بجای نوشتن مورد جستوجو ،آدرس سلول آن نوشته میشود. چون قرار است نام فردی که در سلول D8 نوشته شده است جستوجو شود ،بنابراین در این کادر آدرس سلول D8 را مینویسیم یا روی آن کلیک میکنیم.

 

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

 

 

درکادر COL_INDEX_NUM شماره ستونی را که میخواهیم نمایش داده شود را باید بنویسیم. در این مثال و در جدول تعداد ساعات کاری افراد، میخواهیم ساعت کاری نام فردی که جستوجو شده، نمایش داده شود. ستونی که تعداد ساعت کار افراد در جدول نوشته شده، ستون ۲ است. بنابراین در این کادر عدد ۲ را مینویسیم.

در کادر RANGE_LOOKUP یک مقدار دلخواه باید وارد شود. اگر این کادر خالی بماند یا عدد ۱ قرار گیرد، در صورت موجود نبودن عین عبارت جستوجو در ستون اول جدول، نزدیکترین یا شبیهترین عدد یا عبارت با آن انتخاب میشود. اگر در این کادر عدد صفر قرار گیرد، در صورت موجود نبودن مورد جستوجو در محدوده انتخاب شده ،خطای عدم وجود (#N/A) نمایش داده میشود. در این مثال عدد صفر را قرار میدهیم.

پس از کلیک بر OK تصویر زیر را شاهد خواهیم بود.

 

 

چون در کادر D8 مقداری وجود ندارد، تا اینجای کار خطای #N/A نمایش داده میشود. تا اینجا فقط ساعت کار فرد را جستوجو کردهایم. اما ساعت کار باید در نرخ هر ساعت کار ضرب شود. برای ادامه نوشته فرمول دوباره روی سلول E8 کلیک کرده و درپایان فرمول علامت ضرب “*” را قرار میدهیم.

 

برای ادامه فرمول دوباره در ادامه همین فرمول، تابع VLOOKUP را با کلیک بر نام فرمول فرامیخوانیم.

 

 

 

مطلب مرتبط : تابع ضرب دو ماتریس و معرفی ماتریس واحد در اکسل

در ادامه آموزش تابع Transpose و تابع Vlookup در پنجره جدید تابع VLOOKUP همانند تصویر زیر ادامه فرمول را مینویسیم. در کادر مورد جستوجو (LOOKUP_VALUE) همان سلول ۸D را قرار داده و در جدول جستوجو، جدول نرخ ساعت کاری را بدون سربرگ آن (محدوده D2 تا E5) را وارد میکنیم. درکادر COL_INDEX_NUM شماره ستونی که نرخ ساعت کار در آن نوشته شده یعنی ستون دوم جدول را قرار میدهیم .در کادر RANGE_LOOKUP هم عدد صفر را وارد میکنیم.

 

 

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

=VLOOKUP(D8,A2:B5,2,0)*VLOOKUP(D8,D2:E5,2,2)

برای جمع مبلغ حق اولاد فرد جستوجو شده با فرمول بالا ،در ادامه فرمول فوق در نوار فرمول سلول E8 کلیک کرده و علامت “+” قرار میدهیم و دوباره تابع VLOOKUP را فرامیخوانیم.

 

در کادر تابع VLOOKUP جدید در قسمت مورد جستوجو، آدرس سلول D8، در کادر جدول جستوجو محدوده جدول حق اولاد(G2 تا H5) را قرار میدهیم. در کادر ستون نمایش عدد ۲ و در کادر آخر عدد صفر را قرار میدهیم.

 

حالا با کلیک بر گزینه OK فرمول محاسبه حقوق کامل شده. این فرمول در سلول E8 بصورت زیر است.

=VLOOKUP(D8,A2:B5,2,0)*VLOOKUP(D8,D2:E5,2,2)+VLOOKUP(D8,G2:H5,2,0)

حال اگر در سلول D8 نام هر فردی را بنویسید، در سلول E8 حقوق وی نمایش داده میشود. برای مثال اگر در این سلول نام “محمد” را بنویسیم ،در سلول E8 حقوق وی که از فرمول زیر محاسبه شده است، نمایش داده میشود.

=(۴۰×۴۵۰۰)+۳۵۰۰۰=۲۱۵۰۰۰

حال اگر در سلول D8 نامی نوشته شود که در جداول موجود نباشد، خطای عدم وجود (#N/A) نمایش داده میشود.

همچنین تابع HLOOKUP برای جستوجو در سطرها بکار میرود.

 

امیدواریم از این مطلب در سایت صنایع سافت که درباره آموزش تابع Transpose و تابع Vlookup بود، لذت برده باشید. نظرات خودتون رو برای ما کامنت کنید تا ما بتونیم هر چه بیشتر از اونها واسه بهتر شدنه تیممون استفاده کنیم.

 

 

 

1

 

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

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

0 پاسخ

دیدگاه خود را ثبت کنید

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

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد.