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

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

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

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

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

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

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

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

http://excelhouse.blog.ir/

Sum

کاربرد  Sum  :

جمع زدن مجموعه ای از اعداد.

نحوه استفاده از فرمول :

sum(add this, [and this ..])

مثال :

sum(1,2,3,4) = 10
sum(5.6,2.3) = 7.9

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/

Large

کاربرد  Large  :

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

نحوه استفاده از فرمول :

large(list, شماره اولین یا دومین یا ... عدد بزرگ)

مثال :

large(list, 2) = دومین عدد بزرگ را در لیست پیدا می کند

http://excelhouse.blog.ir/

Lower

کاربرد  Lower  :

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

نحوه استفاده از فرمول :

lower(this text)

مثال :

lower("Hello") = hello
lower("hEELo") = hello

http://excelhouse.blog.ir/

Round

کاربرد  Round  :

رند کردن اعداد اعشاری به نزدیک ترین تعداد اعشاری که مشخص می شود .

نحوه استفاده از فرمول :

round(this number, تعداد رقم بعد از اعشار)

مثال :

round(1.2365,0) = 1
round(1.2365,2) = 1.24

http://excelhouse.blog.ir/

ایجاد محدوده پویا برای نمودارها

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

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

برای این کار ما ابتدا باید دو محدوده متغیر ، یکی برای محور X و یکی برای محور  Y تعریف کنیم. پس از تب FORMULAS گزینه NAME MANAGER رو انتخاب می کنیم و یک محدوده جدید ایجاد می کنیم. بعد از اینکه یک اسم در نظر گرفتیم برای محدوده توی قسمت انتخاب محدوده مورد نظر این فرمول رو وارد می کنیم :

=OFFSET(Sheet1!$A$4;0;0;COUNTA(Sheet1!$A$4:$A$100);1)

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

مشابه همین کار رو برای محور Y انجام میدیم تا محدوده های پویا رو ایجاد کرده باشیم.

حالا ما دو محدوده پویا داریم یکی برای محور X و یکی برای Y کافیه حالا نمودار ستونی که داریم رو براساس این دو محدوده ایجاد کنیم.

برای اینکار بعد از اینکه یک نمودار ستونی ایجاد کردید نمودار رو انتخاب و از تب DESIGN که مربوط به نمودار هست گزینه SELECT DATA رو انتخاب کنید.

برای محور X ها گزینه EDIT رو انتخاب کنید و توی قسمت SERIS VALUES به جای محدوده SELECT شده اسم محدوده ای که ایجاد کردید رو وارد کنید فقط قبل از اسم محدوده حتما اسم شیت رو هم وارد کنید یعنی به این شکل : 

='1.xlsx'!X

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

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

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

http://excelhouse.blog.ir/

If

کاربرد If  :

برگرداندن یکی از دو مقداری که شرط مورد نظر در آن صادق است  .

نحوه استفاده از فرمول :

if(is-this-true?, do this, or this)

مثال :

if(5<10,"hello","world") = hello
if(5>10,"hello","world") = world

http://excelhouse.blog.ir/

Len

کاربرد  Len  :

تعداد کاراکترهای یک داده متنی را مشخص می کند .

نحوه استفاده از فرمول :

len(of this text)

مثال :

len("hello") = 5
len(A1) = تعداد کاراکترهای سلول مورد نظر را مشخص می کند

http://excelhouse.blog.ir/

Rand

کاربرد Rand :

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

نحوه استفاده از فرمول :

rand()

مثال :

rand() = یک عدد تصادفی

http://excelhouse.blog.ir/