رتبه بندی در اکسل با استفاده از VLOOKUP

سلام

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

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

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

دریافت فایل رتبه بندی در اکسل
عنوان: رتبه بندی در اکسل
حجم: 11.1 کیلوبایت

شما می توانید با کلیلک روی " نظر " در قسمت پایین این مطلب هرگونه سوال و نظری را با ما در میان بگذارید.

آموزش تصویری ایجاد لیست های تو در تو در اکسل

با سلام

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

دریافت فایل آموزش تصویری دیتا ولیدیشن پیشرفته
عنوان: data validation
حجم: 6.95 مگابایت

شما می توانید با کلیلک روی " نظر " در قسمت پایین این مطلب هرگونه سوال و نظری را با ما در میان بگذارید.

ایجاد یک auto-fill سفارشی در اکسل

سلام یه همه ی دوستان عزیز

همانطور که می دانید در اکسل قابلیت پر کردن سلول ها به صورت اتوماتیک وجود در دارد.به این صورت که اگر نشانگر موس را روی گوشه پایین سمت چپ سلول فعال ببریم می توانیم با درگ کردن سلول ها زیری را به صورت خودکار پر کنیم . مثلا اگر سلول a1 برابر 1 و سلول a2 برابر 2 باشد در این صورت ابتدا سلول a1:a2 را select می کنیم سپس به گوشه سمت چپ سلول a2 رفته و نشانگر موس را درگ میکنیم تا سلول ها ی زیرین به صورت خودکار پر شوند . مانند تصویر زیر :

در اکسل لیست هایی به صورت پیش فرض موجود می باشد که از آن ها در پر کردن خودکار سلول ها می شود استفاده کرد اما برای اینکه ما لیست سفارش خود را داشته باشیم باید به مسیر FILE>OPTION>ADVANCED>GENERAL>EDIT CUSTOM LIST   برویم و محدوده مورد نظر را ابتدا import و بعد آن را add کنیم مانند تصویر زیر :

شما می توانید با کلیلک روی " نظر " در قسمت پایین این مطلب هرگونه سوال و نظری را با ما در میان بگذارید.

دانلود دفتر معین تحت اکسل

سلام به همه ی دوستان

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

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

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

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

2. برای وارد کردن تاریخ مثلا 1394/01/01 می بایست به این صورت 940101 این تاریخ را وارد کرد.

استفاده از این فایل با ذکر منبع بلامانع است.

دریافت فایل دفتر معین تحت اکسل
عنوان: دفتر معین
حجم: 47.4 کیلوبایت

شما می توانید با کلیلک روی " نظر " در قسمت پایین این مطلب هرگونه سوال و نظری را با ما در میان بگذارید.

نظر سنجی شماره دو

سلام به همه ی همراهان **خانه اکسل**

نظر شما در مورد نحوه ارائه مطالب چگونه است ؟

1. بیشتر مطالب ابتدایی اکسل توضیح داده شود.

2.به همین منوال خوب است.

3. بیشتر مطالب پیشرفته اکسل توضیح داده شود.

خوشحال میشیم ما رو در هرچه بهتر شدن این وبلاگ همراهی کنید .  :)

رسم نمودار جعبه ای در اکسل

با سلام خدمت دوستان

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

دریافت ترسیم نمودار جعبه ای در اکسل
عنوان: نمودار جعبه ای
حجم: 44 کیلوبایت

پس از اینکه اطلاعات را به شکل فایل ضمیمه تغییر دادیم به ترتیب مراحل زیر را انجام می دهیم :

1. ابتدا محدوده مربوط به ( Q3-Q2, Q2-Q1 , Q1  ) را از قسمت اطلاعات نمودار انتخاب و از قسمت ترسیم نمودار ستونی "STAKED COLUMN" را انتخاب می کنیم.

2. از روی نمودار قسمتی که مربوط به "Q1" می باشد رو کلیک راست کنید و از format data series و از fill گزینه no fill رو انتخاب کنید تا قسمت مربوط به چارک اول در نمودار محو بشه.

3. از روی نمودار محدوده "Q1" رو انتخاب و از layout و از error bars گزینه more error bar option رو انتخاب کنید . قسمت direction رو روی minus و error amount رو روی custom قرار بدید . از custom روی specify value کلیک کنید و negative error value رو محدوده ای " چارک اول - داده پرت از پایین " که برای داد ها در چند ستون کنار هم ایجاد کردید قرار بدید.

4. از روی نمودار محدوده "Q3-Q2" رو انتخاب کنید و از layout و از error bars گزینه more error bar option رو انتخاب کنید . قسمت direction رو روی plus و error amount رو روی custom قرار بدید . از custom روی specify value کلیک کنید و positive error value رو محدوده ای " داده پرت از بالا - چارک سوم " که برای داد ها در چند ستون کنار هم ایجاد کردید قرار بدید.

ترسیم نمودار پویا با استفاده از HLOOKUP و DATA VALIDATION

سلام

در فایل زیر یک لیست کشویی از هفته های فروش کالا با استفاده از DATA VALIDATION در سلول K2 ایجاد شده که با انتخاب هفته مورد نظر اطلاعات مربوط به فروش آن هفته با استفاده از فرمول HLOOKUP فراخوانی شده و نمودار مربوط به آن ترسیم می شود.

موفق باشید.

دریافت فایل ترسیم نمودار پویا در اکسل
عنوان: نمودار داینامیک در اکسل
حجم: 13.6 کیلوبایت

شما می توانید با کلیلک روی " نظر " در قسمت پایین این مطلب هرگونه سوال و نظری را با ما در میان بگذارید.

ایجاد لیست های تو در تو و مرتبط به هم در اکسل

با سلام

امروز قصد داریم یک نمونه از جالب ترین کاربرد های data validation  در اکسل رو به شما نشون بدیم.

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

در اکسل هم این قابلیت وجود داره که ما هم توضیحات رو به شما ارائه می دهیم هم یک فایل در آخر ضمیمه میشه که بهتر این مطلب انتقال داده بشه :

1. ابتدا در یک ستون نام گروه ها را وارد می کنیم و با توجه به توضیحات data validation  که در همین وبلاگ موجوده در سلول مورد نظر یک لیست کشویی از گرو ه ها ایجاد می کنیم.

2.در ستون های دیگر نام هر گروه رو وارد می کنیم و زیر مجموعه های مربوط به هر گروه رو زیر نام گروه تایپ می کنیم.

3. به مسیرformulas> name manager   می رویم و برای هر گروه ، با توجه به اسم اون گروه یک محدوده ایجاد می کنیم.

4. سلولی که می خواهیم زیر مجموعه هر گروه رو داشته باشیم رو انتخاب و برای ایجاد یک لیست کشویی اقدام می کنیم allow را روی لیست انتخاب می کنیم و برای source این فرمول را وارد می کنیم :

=indirect(آدرس سلولی که نام گروه ها به صورت کشویی در آن موجود است)           

دریافت فایل ایجاد لیست تو در تو در اکسل
عنوان: لیست تو در تو در اکسل
حجم: 11.9 کیلوبایت

دریافت فایل آموزش تصویری ایجاد لیست تو در تو در اکسل
عنوان: data validation
حجم: 6.95 مگابایت

شما می توانید با کلیلک روی " نظر " در قسمت پایین این مطلب هرگونه سوال و نظری را با ما در میان بگذارید.

جستجوی موارد تکراری در اکسل

سلام خدمت همه ی دوستان عزیز

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

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

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

پایدار باشید.

دریافت فایل جستجوی موارد تکراری در اکسل
عنوان: موارد تکراری
حجم: 11.4 کیلوبایت

جستجو موارد تکراری با ترکیب INDEX ، SMALL، IF  و بدون ستون کمکی توسط جناب آقای عاشوریان :

فایل پیوست

شما می توانید با کلیلک روی " نظر " در قسمت پایین این مطلب هرگونه سوال و نظری را با ما در میان بگذارید.

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

سلام به همه ی دوستان

برای جلوگیری از ورود اطلاعات تکراری در یک ستون ابتدا ستون را انتخاب میکنیم سپس از مسیر DATA>DATA VALIDATION وارد پنجره DATA VALIDATION می شویم.

در این پنجره Allow را روی custom قرار می دهیم و در کادر formulla این فرمول را وارد می کنیم و بر روی ok کلیک می کنیم :

=COUNTIF(اولین سلول محدوده ، محدوده مورد نظر) =number

1 مثال  ) =COUNTIF(A:A;A1)=1

2 مثال) =COUNTIF($A$1:$A$20;A1)=1

* به جای number شماره تکرار را وارد کنید یعنی برای جلوگیری از یک بار تکرار داده عدد 1 را به جای number  وارد کنید.

* برای آشنایی با فرمول countif کلیک کنید .

* توجه داشته باشید اگر قصد انجام این کار برای یک محدوده مشخص از یک ستون را دارید می بایست از آدرس مطلق استفاده کنید . مثال 2

همانطور که مشاهده می شود در صورت ورود اطلاعات تکراری در ستون با یک پیغام خطا مواجه می شویم که با مراجعه به data>data validation و انتخاب تب Error Alert و تغییر title و error message متن ظاهر شده در هنگام بروز خطا را می توانیم تغییر دهیم.

موفق باشید.

نظرات و سوالات خودتون رو با ما در میان بزارید.

http://excelhouse.blog.ir/