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

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

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

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

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

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

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

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

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

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

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

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

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

فایل پیوست

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

شماره ردیف خودکار در اکسل

سلام دوستان

اگر می خواهید در یک ستون اکسل از یک شماره تا یک شماره به صورت اتوماتیک با درگ کردن وارد شود از این فرمول استفاده کنید :

{=ROW(شماره پایانی :شماره آغازی)}

مثال :

{=ROW(1:12)} ==> 1,2,3,4,....,12

و برای اینکه شماره ردیف ها براساس یک شرط مثلا خالی یا پر بودن یک سلول وارد بشه می تونید فرمول رو به این شکل وارد کنید :

{=IF(B1="";"";ROW(1:12))}

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

موفق باشید.

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

http://excelhouse.blog.ir/

جدا کردن داده های عددی از متن در اکسل

با سلام

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

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

دریافت فایل جدا کردن متن از عدد در اکسل

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

http://excelhouse.blog.ir/

نمونه گزارش بین دو تاریخ در اکسل

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

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

این فایل نمونه ای از کاربرد SUMIFS در اکسل رو نشون میده فقط تاریخ ابتدا و انتها را صحیح وارد کنید(مطابق لیست)

امیدوارم ازش لذت ببرید.

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

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

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

http://excelhouse.blog.ir/

جستجوی دوشرطی با استفاده از VLOOKUP

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

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

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

توضیح فرمول :

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

در مثال زیر مقدار مورد جستجو  f2 یعنی "محمد اکبری" می باشد اما به دلیل اینکه فرمول vlookup به صورت ستونی به جستجو می پردازد در هیچکدام از ستون های ما "محمد اکبری" به صورت دو کاراکتر کنار هم موجود نمی باشد بلکه محمد در ستون A و اکبری در ستون B است به همین دلیل فرمول ارور می دهد و شماره دانشجویی بازگردانده نمی شود.

CHOOSE({1,2};A2:A4&" "&B2:B4;C2:C4) : برای جلوگیری از ارور ایجاد شده ما می توانیم با استفاده از فرمول CHOOSE ستون A و Bرا با هم تلفیق کنیم و محدوده سه ستونی را به عنوان یک محدوده دو ستونی به VLOOKUP معرفی کنیم تا در جستجو مشکلی به وجود نیاید .

برای اینکار در پارامتر اول CHOOSE ما {1,2} را وارد می کنیم تا فرمول دو پارمتر بعدی را که مقدار هستند با هم انتخاب کند یعنی هم پارمتر اول و هم پارامتر دوم فراخوانی شود سپس در پارامتر بعدی فرمول CHOOSE ستون A وB را با & با یکدیگر تلفیق می کنیم و چون بین کاراکتر ستون A و B یک فاصله وجود دارد (مثال : "محمد"&" یک فاصله"&"اکبری") با استفاده از & یک فاصله به این شکل " " بین دو محدوده ایجاد میکنیم تا برای تمام سلول ها این فاصله اعمال شود. پارامتر سوم فرمول CHOOSE هم ستون C است تا فرمول A,B تلفیق شده و C را به صورت دو ستون فرخوانی کند.

در نهایت پس از انتخاب مورد جستجو و تعیین محدوده جستجو ، شماره ستون مورد نظر ما که در اینجا طبق توضیحات بالا ستون C یعنی ستون دوم است(A و B یک ستون در نظر گرفته می شود) در پارامتر سوم فرمول VLOOKUP نوشته می شود و FALSE هم در آخر فرمول تا دقیقا مقدار مورد نظر جستجو شود و با زدن CTRL+SHIFT+ENTER به صورت همزمان پس از کامل کردن فرمول درون سلول ، فرمول را به صورت آرایه ای استفاده می کنیم.

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

مقدار اولین سلول در یک لیست

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

برای به دست آوردن اولین سلول دارای مقدار در یک لیست که از سلول های خالی و پر تشکیل شده به صورت زیر عمل میکنیم :

* این روش اولین سلول دارای مقدار عددی یا متنی رو نشون میده در صورتی که بعضی از روش ها ممکن فقط مختص داده های متنی باشه *

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

http://excelhouse.blog.ir/

محاسبه حداکثر تغییرات در اکسل

سلام خدمت شما دوستان گرامی

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

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

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

http://excelhouse.blog.ir/

جستجوی ستون دلخواه در اکسل

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

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

اما چطور میشه که ما از هر طرف که بخوایم داده مورد نظرمون رو جستجو کنیم ؟

برای اینکار از ترکیب دو فرمول INDEX و MATCH از هر طرف که می خوایم داده هامون رو استخراج می کنیم.

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

برای توضیحات بیشتر روی تصویر زیر کلیک کنید :

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

http://excelhouse.blog.ir/

پیدا کردن نزدیک ترین عدد

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

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

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

{=INDEX(A1:A10;MATCH(MIN(ABS(A1:A10-D1));ABS(A1:A10-D1);0))}

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

در این فرمول از ترکیب INDEX و MATCH استفاده شده به این صورت که MATCH شماره سطری که اختلاف کمتری با مورد جستجو داره رو برمی گردونه و INDEX هم با استفاده از شماره سطر مقدار رو جستجو می کنه :

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

http://excelhouse.blog.ir/