The life
جمعه 1393/06/28 :: نویسنده : Mohammad H

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

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

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

این موضوع را با مثالی دنبال می کنیم:

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

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

در جدول اصلی که همه اطلاعات در آن قرار دارد ، ستونی که شما بر اساس آن می خواهید جستجو اولیه انجام پذیرد همیشه باید اولین ستون جدول باشد در این مثال چون می خواهیم جدول را بر اساس شماره پرسنلی جستجو کنیم و بعد رشته تحصیلی را پیدا کنیم ، ستون مربوط به شماره پرسنلی را در ابتدای جدول قرار می دهیم . (تصویر شماره 1) این نکته بسیار بسیار مهم است


فرض میکنیم جدول اصلی در sheet1 قرار دارد و می خواهیم VLOOKUP را در sheet2 اجرا کنیم.

در sheet2 جدول جدید و ساده ای ایجاد میکنیم و شماره پرسنلی هایی که میخواهیم رشته تحصیلی آنها را مشخص کنیم در آن می نویسیم و فرمول VLOOKUP را در ستون رشته تحصیلی اجرا میکنیم (تصویر شماره 2  - به جدول جدید و پایینsheet  نیز توجه کنید)

فرمول VLOOKUP دارای چهار قسمت است:

قسمت اول (Lookup_value): سلول مربوط به مبنای جستجو(شماره پرسنلی) را که در جدول جدید نوشته ایم در این قسمت وارد می کنیم ، چون می خواهیم بر اساس شماره پرسنلی خاصی ، رشته تحصیلی را مشخص کنیم پس مبنای ما جهت جستجو شماره پرسنلی است. ( تصویر شماره 2 )


قسمت دوم ( table_array) :

a) با کلیک کردن بروی کوچک نمایی ، اندازه محل وارد کردن فرمول را کوچک میکنیم

b) به sheet2 که جدول اصلی و اطلاعات مبنای ما آنجا هستند می رویم

c) کل محدوده جدول اصلی خود را جهت جستجو شماره پرسنلی انتخاب می کنیم .

d) پس از انتخاب محدوده جستجو در جدول اصلی ، در ادرس وارد شده در محل فرمول ، قبل از حروف انگلیسی علامت $ می گذاریم . یعنی آدرس  sheet1!A2:G12   را با قرار دادن $ قبل از حروف انگلیسی تبدیل به  آدرس مطلق    sheet1!$A$2:$G$12       می کنیم.  همانند تصویر شماره 3 که هر چهار مرحله a – b – c – d  در آن مشخص شده است .


قسمت سوم  (  col_index_num) : به جدول اصلی در  sheet1 مراجعه کرده و ستون های آن را میشماریم تا ببینیم جواب مورد نظر ما که رشته تحصیلی می باشد چندمین ستون است و عدد ستون مورد نظر را که 4 است را در این قسمت وارد میکنیم. (تصویر شماره 4)


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

 قسمت1 فرمول) شماره پرسنلی مورد نظر ما

قسمت2 فرمول ) در جدول اصلی جستجو کن

قسمت3 فرمول) اگر شماره پرسنلی را پیدا کردی سلول مربوط به رشته تحصیلی آن را نمایش بده

قسمت چهارم ( range_lookup) : در این قسمت عدد صفر را قرار می دهیم

و در آخر بروی ok کلیک می کنیم .

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





نوع مطلب : آموزش اکسل Excel، 
برچسب ها : آموزش اکسل، VLOOKUP،
لینک های مرتبط :

پرینت عناوین و سر تیترها در تمامی صفحات اکسل ( Print Title ) :

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


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

قدم اول : درقسمتPage Layout گزینهPrint Titles  را انتخاب میکنیم. (شکل شماره 2)

قدم دوم : درون جعبه مربوط به  Rowe to repeat at top   کلیک کرده و سرتیترهای مورد نظر را انتخاب میکنیم و در نهایت بروی  ok  کلیک میکنیم ( شکل شماره 2 ).


درنهایت پرینتها به شکل زیر میشوند( همانند شکل شمار 3 و 4 در ادامه مطلب ).



ادامه مطلب


نوع مطلب : آموزش اکسل Excel، 
برچسب ها : آموزش اکسل، Print titles،
لینک های مرتبط :
جمعه 1393/03/9 :: نویسنده : Mohammad H


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

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

مطلب زیر از وبلاگ http://hazrate-mah.blogfa.com   گرفته شده است

فعلا تمام سعی تونو بکنید که هرچی میتونید عکس آپلود کنید تو اینترنت ...

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

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

دوستان ..: امام زمان ، نوه ی عزیز امام هادیه(ع)  ، مَهدی دلش خونه از این قضیه ...

دوسال گذشته ها حواسمون هست؟؟ منتظران مَهدی دست بجنبونید...

وبلاگی دیگر جهت حمایت از امام هادی النقی (ع

http://naghi-aftab10.blogfa.com

http://hazrate-mah.blogfa.com

http://discernment-23.mihanblog.com
لطفا اطلاع رسانی کنید

        



ادامه مطلب


نوع مطلب : آموزش اکسل Excel، حسابداری، امام زمان (عج)، داستان و حکایت، شعر وجملات نغز، 
برچسب ها : امام هادی، هادی النقی، علی النقی، نقی، هادی، علی،
لینک های مرتبط :

مشکل مرتب کردن ستون در اکسل( SORT کردن ) :

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

مهمترین نکته در مرتب کردن (sort) ، آشنایی با مفهوم داده های پیوسته و ناپیوسته در اکسل می باشد.

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



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



نکته : دقت داشته باشید که یک ستون خالی از اطلاعات باعث ناپیوسته شدن می شود نه یک سلول خالی

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

به تصاویر زیر توجه کنید . دو جدول با اطلاعات پیوسته و ناپیوسته را بر اساس معدل مرتب  می کنیم تا نحوهSORT شدن آنها را مورد بررسی قرار دهیم برای چک کردن جداول از نام کشتکاران با معدل 17 استفاده میکنیم .



تصویر شماره 3 : با توجه به پیوسته بودن، جدول صحیح SORT شده و ردیفهای آن به صورت هماهنگ جابجا شده اند ( نام کشتکاران به صورت هماهنگ با معدل 17 جابجا شده است ).




در تصویر 4 با توجه به ستون خالی ، برنامه اکسل دو جدول را در نظر می گیرد و هنگامی که بر اساس معدل ، ستون را مرتب می کند اسامی را بدون تغییر باقی می گذارد (نام کشتکاران در جای خود ثابت مانده و فقط معدل 17 جابجا شده است   و به اشتباه 16.5 روبروی نام مورد نظر ما قرار گرفته است)





نوع مطلب : آموزش اکسل Excel، 
برچسب ها : آموزش اکسل، مرتب کردت ستون در اکسل، SORT کردن در اکسل،
لینک های مرتبط :

سلام -  این بار می خوام برای آشنایی بیشتر دوستان با وبلاگ نویسی و نحوه ارسال مطالب به محیط وبلاگ ، یه سری آموزش ها رو به صورت عکس توی وب قرار بدم ، امیدوارم این کار به علاقه مندی شما به وبلاگ نویسی منتهی بشه


                    






نوع مطلب : آموزش اکسل Excel، 
برچسب ها :
لینک های مرتبط :
شنبه 1392/10/21 :: نویسنده : Mohammad H

پرینت قسمتی از یک صفحه در اکسل ( Print Area ):

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



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

حرکت اول ) ابتدا قسمتی از لیست را که می خواهیم پرینت گرفته شود را انتخاب می کنیم که در این مثال قسمت مورد نظر حقوق سه نفر اول در سه ماهه اول می باشد . ( شکل 2 )

حرکت دوم ) به قسمت Page Layout می رویم . ( همانند شکل 2 )

حرکت سوم ) بروی آیکون Print Area کلیک کرده و از سه گزینه ارائه شده گزینه Set Print Area را انتخاب می کنیم . ( شکل 2 )


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





نوع مطلب : آموزش اکسل Excel، 
برچسب ها : آموزش اکسل، پرینت، پرینت قسمتی از مطلب، Print Area،
لینک های مرتبط :

ثابت کردن سطرها و ستونها در اکسل ( : (Freeze panes

برخی موا قع که بر روی لیست بزرگی شامل چندین سطر و ستون کار می کنیم ، وقتی که به قسمت های پایین لیست مراجعه می نمائیم ، دیگر سر ستونهای لیست خود ( موضوع ستون ) را نمی بینیم و نمی توا نیم تشخیص بدهیم که هر ستون مربوط به چه موضوعی است .

برای مثال فرض کنید لیست حقوقی را دارید که ستونهای آن مربوط به 12 ماه سال است و ردیف های آن را بیش از 30 نام و نام خانوادگی تشکیل می دهد ( شکل 1 ) . حال اگر بخواهیم به قسمت پایین لیست برویم و حقوق اسفند ماه در ردیف 11 را مشاهده کنیم با مشکلی که در شکل 2 وجود دارد مواجه خواهیم شد یعنی نه سرستونها مشخص هستند و نه نام در قسمت ردیفها ( شکل 2 ) .


برای رفع این مشکل می بایست موضوع سطرها و ستونها را ثابت کنیم ( Freeze ) برای این کار به ترتیب زیر عمل می کنیم باید توجه داشته باشیم مهمترین نکته در Freeze کردن انتخاب سلول در حرکت اول است.

حرکت اول ) ابتدا یک خط فرضی زیر ردیفی که میخواهیم ثابت باشد ، به صورت ذهنی میکشیم و این کار را برای ستون نیز انجام میدهیم( مطابق خط چین مشخص شده در شکل شماره 3 ) و سپس بروی سلولی که در زیر محل برخورد دو خط چین قرار دارد کلیک کرده و آن را انتخاب میکنیم ( سلول مورد نظر در شکل3 با دایره سبز مشخص شده )

حرکت دوم ) در قسمت View بروی آیکن Freeze panes کلیک کرده و گزینه اول را انتخاب میکنیم همانند شکل 3 در بالا که با رنگ آبی مشخص شده .

 اینک میتوانیم حقوق اسفند ماه در ردیف 11 را با در نظر گرفتن ماه و نام مشاهده کنیم.

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





نوع مطلب : آموزش اکسل Excel، 
برچسب ها : آموزش اکسل، آموزش ثابت کردن سطر و ستون، FREEZE کردنن در اکسل،
لینک های مرتبط :


( کل صفحات : 3 )    1   2   3   
آمار وبلاگ
  • کل بازدید :
  • بازدید امروز :
  • بازدید دیروز :
  • بازدید این ماه :
  • بازدید ماه قبل :
  • تعداد نویسندگان :
  • تعداد کل پست ها :
  • آخرین بازدید :
  • آخرین بروز رسانی :
تماس با ما
اللهم عجل لولیک الفرج
کسی نیامده جز او سر قرار خودش نشسته غرق تماشای شیعیان خودش چه انتظار عجیبی ست اینکه شب تا صبح کسی قنوت بگیرید به انتظار خودش
 
 
 
شبکه اجتماعی فارسی کلوب | Buy Mobile Traffic | سایت سوالات