کاربرد یادداشت‌های توضیحی در اکسل

 

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

 

اضافه کردن یادداشت توضیحی در اکسل

با دو روش می‌توان وارد یادداشت‌ توضیحی در اکسل اضافه کرد.

از طریق منوی راست کلیک و با کلید میانبر Shift+F2

سلول مورد نظر را انتخاب کنید. کلید میانبر Shift+F2 را بزنید یا راست کلیک کرده و گزینه Insert Comment را انتخاب کنید. متن مورد نظر در گوشه سمت راست را تایپ کنید. در خارج مستطیل کلیک کنید.

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

در قسمت FILE، بر روی گزینه Options کلیک کنید و به قسمت General منتقل شوید. در این قسمت به بخش Personalize your copy of Microsoft Office  رفته و نام کاربری خود را تغییر دهید.

 

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

برای اضافه کردن تاریخ یادداشت، پس از نوشتن آن، کلید های Ctrl و + را بفشارید. ساعت یادداشت نیز با فشردن کلیدهای Ctrl,Shift و + اضافه می‌شود.

ویرایش و حذف یادداشت توضیحی در اکسل

برای ویرایش یادداشت‌ توضیحی در اکسل، سلول مورد نظر‌ را انتخاب کنید. راست کلیک کرده و گزینه Edit Comment را انتخاب کنید. کادر محتوی یادداشت قابل ویرایش خواهد شد. متن را تغییر دهید.

برای کپی کردن یک یادداشت به دیگر سلول ها، ابتدا سلول حاوی یادداشت را انتخاب کرده و آن را کپی نمایید. به سلولی مقصد رفته و از گزینه‌های موجود در منوی Paste گزینه Paste Special را انتخاب کنید. در پنجره باز شده دکمه رادیویی Comment را بزنید.

نشان دادن یا پنهان کردن یادداشت‌ توضیحیدر excel

سلول مورد نظر‌ را انتخاب کنید. به منوی REVIEW بروید. در زیر منوی Comments اگر بر روی Show/Hide Comment یکبار کلیک نمایید، یادداشت نوضیحی نمایش داده می‌شود، اگر بار دوم کلیک نمایید یادداشت پنهان خواهد شد. در صورتیکه بر روی Show All Comments کلیک کنید، تمامی یادداشت ها نشان داده خواهد شد.

جابجا کردن و تغیر اندازه یادداشت توضیحی در اکسل

با توجه به حجم متن یادداشت‌ توضیحی در اکسل، بعضاً متن در مقابل داداه‌ها قرار می‌گیرد (متن هایی که همواره نشان داده می‌شوند). برای جابجا کردن یادداشت های توضیحی در اکسل، سلول مورد نظر‌ را انتخاب کنید. راست کلیک کرده و گزینه Edit Comment را انتخاب کنید. نشانگر ماووس را بر روی حاشیه کادر محتوی یادداشت نگه دارید، علامت چهار جهته حرکت نمایان خواهد شد. به مکان دلخواهتان درگ کنید.

 

جستجو در بین یادداشت های توضیحی در اکسل

بعضاً تعداد یادداشت های توضیحی به حدی زیاد است که پیدا کردن یک یادداشت خاص بسیار زمانبر می‌شود. برای جستجو در میان یادداشت ها به منوی HOME رفته در زیر منوی Editing کلیلک کنید. در زیر منوی Editing به بخش Find & Select  بروید. در این بخش Find را انتخاب کنید (یا کلید میانبر Ctrl+F را بزنید). در منوی باز شده دکمه Options را بزنید تا پنجره گسترده تر شود. در این پنجره در قسمت Look in گزینه Comment را انتخاب کنید. در قسمت Find what کلمه مورد نظر را تایپ کرده با زدن دکمه Find all تمامی یادداشت هایی که این کلمه را دارند، بیابید. با زدن دکمه Find next دربین سلول هایی که یادداشت توضیحی آنها این کلمه را دارند، حرکت خواهید کرد.

 

تغییر قالب یادداشت توضیحی در اکسل

برای تغییر قالب یک یادداشت توضیحی در اکسل، سلول حاوی یادداشت را انتخاب کنید. راست کلیک کرده و گزینه Edit Comment را انتخاب کنید. بر روی حاشیه یادداشت توضیحی راست کلیک کرده و گزینه Format Comment را انتخاب کنید. در پنجره بازشده قادر به تغییر دادن فرمت نوشته یادداشت خواهید بود. در تب های مختلف این پنجره می‌توانید عملیات مختلفی انجام دهید.

 

اگر در وسط کادر یادداشت راست کلیک کتید و Format Comment را انتخاب کنید، فقط می‌توانید فونت نوشته را تغییر دهید.

 

کدام سلول ها یادداشت توضیحی دارند

زمانی که یک یادداشت‌ توضیحی در اکسل اضافه می‌کنید، یک مثلث قرمز رنگ (نشانگر یادداشت توضیحی) در بالا گوشه سمت راست سلول ظاهر می‌شود. گزینه‌های مختلف چگونه نشان دادن یادداشت توضیحی یا نشانگر آن، در بخش Options قرار دارد. در قسمت FILE، بر روی گزینه Options کلیک کنید و به قسمت Advanced منتقل شوید. در این قسمت به بخش Display  رفته و یکی از گزینه های مربوط به نحوه نمایش یادداشت توضیحی را انتخاب کنید.

No comments or indicators: با انتخاب این گزینه نه نشانگرها نمایش داده می‌شود و نه خود یادداشت ها.

Indicators only, and comments on hover: در این حالت فقط نشانگر یادداشت نمایش داده می شود. همچنین یادداشت زمانی ظاهر می‌شود که ماوس را روی سلول ببرید.

Comments & indicators: هم نشانگرها و هم یادداشت ها نمایش داده می‌شوند.

 

در صورتیکه نه نشانگر و نه یادداشت نمایش داده نمی‌شوند، با استفاده از Go To Special تمامی سلول هایی که یادداشت توضیحی دارند را انتخاب و در صورت نیاز رنگ آنها را تغییر دهید. برای این کار به منوی HOME رفته در زیر منوی Editing کلیلک کنید. در زیر منوی Editing به بخش Go To Special بروید. در منوی باز شده دکمه رادیویی  Comment را انتخاب کرده و OK کنید. تمامی سلول هایی که یادداشت توضیحی دارند انتخاب خواهند شد. در زیر منوی Font  از منوی HOME رنگ مورد نظر برای سلول ها را انتخاب کنید. سلول ها رنگی شده‌اند.

 

پرینت کردن یادداشت‌ توضیحی در اکسل

دو مدل برای پرینت کردن یادداشت‌ توضیحی در اکسل وجود دارد. یک اینکه یادداشت ها را در همان مکانی که دیده می‌شوند پرینت کنید. دو اینکه تمامی یادداشت ها را در انتهای برگه و به صورت جداگانه پرینت کنید. برای پرینت یادداشت در مکان دیده شدن، از منوی  REVIEW بر روی Show All Comments کلیک کنید، تمامی یادداشت ها نشان داده خواهد شد. به منوی PAGE LAYOUT بروید. در زیر منوی Print Titles به تب Sheet منتقل شوید. در این تب به قسمت Comment  رفته و در منوی کرکره‌ای باز شونده گزینه As displayed in sheet را انتخاب کنید.

 

برای پرینت تمامی یادداشت ها در انتهای برگه، دستوررات فوق را دوباره اجرا کنید. ولی در منوی کرکره‌ای باز شونده گزینه At end of sheet را انتخاب کنید.

اضافه کردن یادداشت توضیحی به فرمول

برای اضافه کردن یادداشت توضیحی به یک فرمول از تابع N استفاده می‌شود. تابع N دارای خصوصیاتی است که اگر متنی در آرگومان آن اضافه شود، در نتیجه حاصله فرمول تاثیری نخواهد داشت. برای این منظور، پس از نوشتن فرمول یک علامت + در انتهای آن گذاشته و حرف N را تایپ کنید. اکسل تابع N را به شما پیشنهاد خواهد کرد. این تابع را انتخاب کنید. در آرگومان آن متن توضیحتان را در داخل دو علامت  ” ”  قرار داده و اینتر را بزنید.

اضافه کردن یادداشت توضیحی به کد ماکرودر EXCEL

اضافه کردن یادداشت در کد نویسی، به فهم بهتر آن کمک می‌کند. در مراجعات بعدی نیز اصلاح یا ویرایش کد را آسان تر خواهد کرد. بعضاً نیز قصد دارید بخشی از کد به صورت موقت اجرا نشود، در این موارد نیز تبدیل کردن آن به یادداشت توضیحی، بهتر از پاک کردن آن قسمت است! برای این منظور در خطی که می‌خواهید یادداشت اضافی را بنویسید، قبل از یادداشت، یک آپاستروف (‘) بگذارید. یا اینکه یادداشت را نوشته و از ابزار ویرایشی ویژوال بیسیک استفاده کنید.

 

 
P: بیان کننده این است که فرمول به صورت برداری عمل می‌کند.
N: بیان کننده این است که فرمول با محاسبه مجدد مقداری متفاوت خواهد داشت.(F9)
@: بیان کننده این است که برای استفاده از فرمول باید Analysis toolPak باید نصب شود.
 
تابع قدر مطلق در اکسل
شرح تابع
مثال
ABS
قدر مطلق یک عدد را می دهد.
=ABS(-2)
ACOS
Arcos یک عدد را می دهد. (رادیان)
=ACOS(-0.5)*180/PI()
ACOSH
آرک کوسینوس هیپربولیک درexcel
=ACOSH(10)
ATAN2
Arctan نقطه ای به مختصات X,Y را  روی دایره مثلثاتی  می دهد.(تبدیل به درجه *180/pi()
=ATAN2(-1, -1)
CEILING
عددی را به مقداری بالاتر،نسبت به صف روند می کند، که این مقدار ضریب عددی است که پارامتر دوم تابع است.
=CEILING(2.4;2)
FLOOR
عددی را به مقداری پایین تر از خودش، نسبت به صف روند می کند، که این مقدار ضریب عددی است که پارامتر دوم تابع است.
 
COMBIN
احتمال ترکیب ریاضی دو عدد در اکسل
=COMBIN(4;2)
COUNTIF
تعداد خانه هایی که دارای شرط خاصی هستند را می دهد.
=COUNTIF(B2:B5,">55")
DEGREES
رادیان را به درجه تبدیل می کند.
توجه: تمام فرمولها برحسب رادیان هستند.
=DEGREES(PI())
EVEN
عددی را به نزدیکترین عدد زوج بعداز خودش گرد می کند.
=EVEN(2.5)
EXP
عدد e  را به توان x  می رساند.
=EXP(1)
FACT
فاکتوریل یک عدد را محاسبه می کند. (N!)
=FACT(3)
FACTDOUBLE
اگر n زوج: n!=n(n-2)(n-4)…(4)(2)
اگر n فرد: n!=n(n-2)(n-4)…(3)(1)
=FACTDOUBLE(6)
GCD
بزرگترین مقسوم علیه مشترک
Greatest Common Divisor
=GCD(24, 36) à12
INR
جزء صحیح یک عدد را می‌دهد.
=INT(5.4) à 5
LCM
کوچکترین مضرب مشترک
Least Common Multiple
=LCM(36;24) à 72
LN
لگاریتم در مبنای e
=LN(2.7182818) à 1
LOG
لگاریتم در مبنای 10 یا دلخواه
=LOG(8;2) à 3
LOG10
لگاریتم در مبنای 10
=LOG10(10^5) à 5
MDETERM
دترمینال یک ماتریس (بردار) را می‌دهد.
=MDETERM({3,6;1,1})à -3
MINVERSE P
ماتریس معکوس را می‌دهد
=MINVERSE(A2:C4) àP
MMULT P
حاصلضرب دو ماتریس را می‌دهد.
=MMULT(array1;aray2) àP
MOD د
باقی مانده تقسیم دو عدد برهم در EXCEL
MOD(n, d) = n - d*INT(n/d)
=MOD(10;3) à1
MROUND
عددی را به مضربی دلخواه از عدد دیگر، گرد می‌کند.
=MROUND(10;3) à9
MULTINOMIA
نسبت فاکتوریل جمع به فاکتوریل حاصلضرب
= (a+b+c)! / a! b! c!
=MULTINOMIAL(2,3,4)à1260
ODD
عددی را به نزدیکترین عدد فرد بعد از خودش گرد می‌کند.
=ODD(1.5) à 3
PI()
عدد پی را می‌دهد.
=PI() à3.14159265358979
POWER
عدد را به توان داده شده می‌رساند.
=POWER(2;10) à1024
PRODUCT
حاصلضرب اعداد
=PRODUCT(2,3,5) à 30
QUOTIENT
خارج قسمت یک تقسیم را می‌‌دهد.
=QUOTIENT(15;3) à 5
RADIANS
درجه را به رادیان تبدیل می‌کند.
=RADIANS(90) à1.570796
RAND() N
عددی تصادفی بین 0 تا 1 تولید می‌شود.
=RAND()*(b-a)+a
=TRUNC(RAND()*100(
RANDBETWEENN@
عددی تصادفی بین محدوده ورودی داده شده می‌دهد.
=RANDBETWEEN(1,100)
ROMAN
اعداد را به اعداد یونانی تبدیل می‌کند.
=ROMAN(8) à VIII
ROUND
برای گرد کردن اعداد با دقت در تعداد رقم اعشار
=ROUND(2.15;1) à 2.1
ROUNDDOWN
گرد کردن اعداد به سمت صفر
=ROUNDDOWN(2.578;2)à2.57
ROUNDUP
گرد کردن اعداد دور از صفر
=ROUNDUP(2.578;2)à2.58
SERIESSUM در  اکسل
تابع SERIES را می‌دهد. ر.ک به راهنمای اکسل
 
SIGN
علامت یک تابع را می‌دهد. (مثبت 1 منفی 0)
=SING(-5) à 0
SIN
مقدار سینوس یک زاویه (رادیان) را می‌دهد.
=SIN(30*PI()/180) à 0.5
SINH
مقدار سینوس هیپربولیک را  می‌دهد.
 
SQRT در اکسل
محاسبه جذر یک عدد
=SQRT(36) à 6
SQRTPI
جذر مضارب عدد پی را می‌دهد.
=SQRTPI(2) à2.50
SUBTOTAL
تابعی که مجموعه‌ای از عملیاتها را برای لیست فراهم می‌کند. (نتیجه تابع بنابر سطرهای مخفی می‌تواند کنترل شود...)
ر.ک به راهنمای اکسل
SUM
محاسبه  حاصل جمع (مقدار TRUE برابر 1 است)
 
SUMIF
حاصل جمع با توجه به شرط خاصی محاسبه می‌شود.
=SUMIF(A2:A5,">160000",B2:B5)
SUMPRODUCT
عناصر نظیر به نظیر دو آرایه را درهم ضرب و سپس مجموع آنها را محاسبه می‌کند..
=SUMPRODUCT(A2:B4, C2 4)
=SUM(A2:B4*C2 4) àP =SUM(A2:B4^2) àP
SUMSQ
محاسبه مجموع توان 2 ورودی‌ها
=SUMSQ(3;5) à 34
SUMX2MY2 P  
 
SUMX2PY2 P  
 
SUMXMY2 P  
 
 TAN
محاسبه تانژانت یک زاویه
=TAN(RADIANS(45))
TANH
محاسبه تانژانت هیپربولیک یک زاویه
=TANH(-2)
TRUNC
قسمت اعشاری را حذف می‌کند.
=TRUNC(8.9) à 8

 با استفاده از ترکیب تابع INDEX و ابزار CONDITIONAL FORMATTING موقعیت یک مقدار را در یک جدول از داده ها پیدا نموده و آن را با رنگ متمایز مشخص نماییم.

در این آموزش اکسل پیشرفته از تابع INDEX و ابزارهای CONDITIONAL FORMATTING و DATA VALIDATION استفاده می کنیم.

فرض کنید جدولی داریم  که از 6 ردیف و 5 ستون تشکیل شده است. تعداد 30 عدد مختلف در این جدول قرار دارد (داده ها می توانند تکراری هم باشند). می خواهیم سطر و ستون دلخواه را وارد نموده و با استفاده از تابع INDEX مقدار واقع در تقاطع این سطر و ستون را بدست آوریم. ضمناً عدد پیدا شده نیز باید با یک رنگ دلخواه در جدول مشخص گردد.

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

 

در کنار جدول در دو سلول عبارت های "سطر و ستون " را وارد کرده و در زیر هر کدام یک عدد که بیانگر شماره سطر و ستون باشد را وارد می کنیم. با استفاده از ابزار DATA VALIDATION مقادیری که توسط کاربر وارد می شود را محدود می کنیم تا کاربر نتواند عددی بیش از تعداد سطر و ستون های جدول را وارد نماید و اگر چنین اتفاقی بیفتد، با یک پیام مناسب به کاربر هشدار داده و مقدار وردی غلط را نپذیر

برای سلول حاوی شماره ستون نیز دقیقاً مراحل تصویری بالا را تکرار می کنیم با این تفاوت که به جای عدد 6 باید عدد 5 را قرار دهیم، چون تعداد ستون های جدول ما 5 عدد می باشد.

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

(INDE I5:M10,F5,G5=

در این فرمول I5:M10 محدوده جدول و F5 سلول حاوی شماره سطر و G5 سلول حاوی شماره ستون می باشد.

 

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

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

 

 

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

(AND(ROW()=$F$10+4,COLUMN()=$G$10+8=

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

ضمناً $F$10  و $G$10 مقادیر سلول سطر و ستونی هستند که قبلاً وارد کرده ایم. (برای جلوگیری از تغییر سلول حاوی شماره سطر و ستون این آدر سها به صورت مطلق وارد شده اند)

اما چرا عدد 4 به مقدار سطر (ردیف)  و عدد 8 به مقدار ستون اضافه شده اند؟

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

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

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

ایجاد لیست پایین افتادنی هوشمند در EXCEL

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

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

در تب (سربرگ) developer  و در بخش ActiveX Control ابزار ComboBox را انتخاب می کنیم (اگر سربرگ Developer در نوار ریبون دیده نمی شود مسیر زیر را برای فعال کردن آن طی کنید.

File/Options/Customize Ribbon و چک باکس کنار گزینه Developer را فعال کنید)

در یک سلول دلخواه کلیک کرده تا کامبوباکس در آنجا قرا گیرد.

بر روی کامبوباکس راست کلیک کرده و Properties را انتخاب می کنیم

در پنجره ای که ظاهر می شود تغییرات را به صورت زیر اعمال می کنیم.

AutoWordSelect: False

LinkedCell: B3

ListFillRange: DropDownList  ((در گام دوم یک یک نام برای این مرحله ایجاد خواهیم کرد

MatchEntry: 2 – fmMatchEntryNon

 

سلول B3 به کامبوباکس لینک می شود به این معنی که هر مقداری که در کامبوباکس وارد شود در سلول B3 نیز ظاهر می شود.

به سربرگ Developer رفته و بر روی Design mode کلیک می کنیم تا بتوانیم متن خود را در کامبوباکس وارد کنیم.

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

مرحله دوم: تنظیم اطلاعات در excel

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

برای انجام این کار از سه ستون کمکی و یک دامنه اسم پویا (dynamic name range) استفاده می کنیم.

ستون کمکی 1

فرمول زیر را در سلول F3 تایپ کرده و آن را تا F22 کپی می کنیم.

=,,ISNUMBER(IFERROR(SEARCH($B$3,E3,1),””))

 

این فرمول در صورتی که متن وارد شده در کامبوباکس در ستون نام کشورها وجود داشته باشد عدد 1 را نمایش می دهد. مثلاً اگر شما حرف UNI را تایپ نمایید فقط در مقابل نام های United stats و United kingdom عدد 1 و در مقابل نام سایر کشورها عدد 0 قرار خواهد گرفت

 

ستون کمکی 2

فرمول زیر را در سلول G3 وارد کرده و تا سلول G22 کپی می کنیم.

=IF(F3=1,COUNTIF($F$3:F3,1),””)

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

برای مثال اگر شما عبارت UNI را در کامبوباکس وارد کنید در سلول G3 عدد 1 که مطابق با United States و سلول G9  عدد 2 را که مطابق با نام United kingdom و دومین مورد یافته شده است نمایش می دهد.در این حالت اگر هیچکدام از کلمات نام کشوری در باکس جستجو واردنشده باشد در مقابل نام آن چیزی قرار نمی گیرد و سلول مقابل آن نام خالی خواهد بود.

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

در سلول H3 فرمول زیر را قرار داده و تا  H22  آن را کپی نمایید.

=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),””)

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

مثلاً اگر شما UNI را در کامبوباکس (جعبه جستجو) وارد نمایید نام های United States و United Kingdom لیست شده و نام  بقیه کشورها نمایش داده نمی شوند.

 

ایجاد نام دامنه پویا (Dynamic range Name)

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

ما از این نام دامنه پویا برای نشان دادن مقادیر کامبوباکس استفاده می کنیم.

تذکر: همانطور که در گام اول  نام DropDownList در مقابل فیلد ListFillRange در خواص کامبوباکس وارد کردیم در اینجا نام دامنه پویا را مشابه همان نام ایجاد می کنیم.

برای ایجاد این نام دامنه مطابق مراحل زیر عمل نمایید.

به سربرگ Formulas و سپس Name Manager بروید

در کادر Name Manager بر روی New کلیک کنید تا پنجره نام جدید ظاهر شود

در فیلد نام عبارت DropDownList را وارد نمایید

در کادر Refer to فرمول زیر را وارد کنید.

=$H$3:INDE $H$3:$H$22,MAX($G$3:$G$22),1)

مرحله سوم

استفاده از کد VBA برای تکمیل گام آخر

برای تکمیل مرحله نهایی کد های زیر را به کامبوباکس اضافه نمایید. برای این منظور مطابق مراحل زیر عمل کنید.

در سربرگ Developer بر روی Design کلیک کنید

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

در پنجره ظاهر شده کد های نوشته شده را پاک کرده کدهای زیر را قرار دهید.

Private Sub ComboBox1_GotFocus()

ComboBox1.ListFillRange = “DropDownList“

Me.ComboBox1.DropDown

End Sub

برای نتیجه و ظاهر بهتر کار می توانید سلول B3 را با کامبوباکس بپوشانید و ستونهای کمکی را نیز Hide نمایید.

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

 

 

 

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

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

تابع ضرب در اکسل | Product

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

 

 

نکته:
چون عملیات تقسیم، معکوس ضرب هست، تابعی برای تقسیم نداریم و در صورت نیاز از همان / استفاده می کنیم. اما توابعی داریم که اجزای تقسیم (باقیمانده، خارج قسمت) را محاسبه می کنند.

تابع جمع در اکسل | Sum

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

 

خروجی این تابع صفر خواهد بود.

نکته:
با توجه به اینکه تفریق، همان جمع اعداد منفی است، تابعی برای تفریق نیز اختصاص داده نشده است. یا از – و یا اینکه اعداد مورد نظر رو در -1 ضرب می کنیم و در تابع Sum قرار می دهیم.

تابع توان در اکسل | Power

این تابع عملیات به توان رساندن  انجام می دهد. آرگومان های این تابع به شرح زیر است:

Number: پایه توان. عددی که قرار است به توان عددی برسد.

Power: توان. عددی که پایه به توان آن میرسد.

 

 

خروجی این تابع 64 خواهد بود. عدد 4 به توان 3 رسیده است.

تابع جذر | Sqrt

این تابع عملیات جذر گرفتن یا همان ریشه دوم عدد رو بر می گردونه.

 

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

=Power(64,1/3)

 

توابع محاسبات اصلی در اکسل که عمدتا عملگرهای معادل هم دارند رو تشریح کردیم. مزیت استفاده از این توابع بجای عملگرها، این است که مدیریت این توابع و دیباگ کردن آن ها راحت تراست. ولی به لحاظ عملکرد هیچ تفاوتی بین آنها نیست و اینکه فکر کنیم مثلا * با Product در نتیجه تفاوت خواهند داشت، اصلا درست نیست.

 

ممکن است شما می خواهید  یک ردیف یا ستون از یک صفحه گسترده (spreadsheet) وجود دارد،را حذف کنید. اما نمی خواهید به طور دائم آن را از فایل کاربرگ حذف کنید. Excel  یکی از ویژگی های است که به شما اجازه می دهد به طور موقت یک ردیف یا ستون  را پنهان کنید.

 

توجه: سلولهای ردیف و ستون پنهان هنوز هم می تواند در محاسبات در سایر سلول های قابل رویت و همچنین انجام محاسبات خود گنجانده شده است.

پنهان کردن یک یا چند ردیف در اکسل                                                                                                                                                                                                                                         برای پنهان کردن یک یا چند ردیف، سطر (s) را انتخاب کنید تا پنهان شوند.

 

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

 

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

 آشکار ساختن سطرها وستون هادر EXCEL

برای رویت یک ردیف، برای بار اول شما باید ردیف های هر دو بالا و پایین ردیف پنهان (s) را انتخاب کنید. سپس،  بر روی هدر ردیف انتخاب شده راست کلیک کرده و گزینه Unhide اتخاب کنید.

 

ردیف های مخفی دوباره  نمایش داده شد و همراه با ردیف اطراف آن برجسته است.

 

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

 

ستون انتخاب شده همراه با ستون پنهان شده و یک خط ضخیم تر را نشان می دهد که در آن ستون بود.       

 

برای رویت ستون پنهان، درست مثل با ردیف پنهان، وانتخاب ستون ها هم به سمت چپ و راست از ستون پنهان،  بر روی یکی از ستون راست کلیک، و گزینه Unhide را انتخاب کنید.

 

ستون پنهان دوباره نمایش داده شده وهمراه با ستون در دو طرف برجسته شده است.

 

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

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

در اکسل، ذخیره سازی اطلاعات در بین کاربرگ ها (یا worksheets) به صورت نسبی انجام می گیرد و همین مساله سبب می شود تا از تکرار اطلاعات و داده ها در پروژه های بزرگ جلوگیری شود. مزیت نسبی بودن پایگاه داده (Database) در این است که می توانید اطلاعات یک Sheet یا برگه را در جدول و یا شیت دیگر مورد استفاده قرار دهید. اکسل این کار را با استفاده از لینک کردن سلول به سایر کاربرگ ها انجام می دهد.

به این ترتیب اگر در یک سلول از کاربرگ Sheet1 اطلاعاتی داشته باشید، می توانید براحتی مقدار آن را در سلول دیگری از کاربرگ Sheet2 نمایش دهید. برای انجام اینکار کافی است مراحل زیر را طی کنید:

1# گام نخست

نرم افزار اکسل را فراخوانی کنید و یک کاربرگ (Worksheet) جدید با نام “دی” ایجاد کنید. سپس در سلول A1 عبارت “میزان فروش” و در سلول B1 مقدار “1000” را وارد نمایید.

 

2# گام دوم

کاربرگ دیگری ایجاد کرده و نام آن را “بهمن” بگذارید. سپس در سلول A1 عبارت “میزان فروش” و در سلول B1 مقدار “2000” را وارد نمایید.

به این ترتیب 2 کاربرگ ساده با نام های “دی” و “بهمن” دارید. حال می توانید از اطلاعات موجود در این دو کاربرگ (worksheet) در کاربرگ سوم استفاده کنید.

3# گام سوم

کاربرگ جدیدی با نام “مجموع” ایجاد کنید و در سلول A1 عبارت “جمع کل” را وارد نمایید و در سلول B1 فرمول زیر را تایپ کنید:

=دی!B1

فرمول را با علامت مساوی شروع کنید، سپس نام کاربرگ، علامت تعجب و سپس آدرس سلول مرجع را تایپ نمایید. در واقع شکل عمومی فرمول بالا به این شکل خواهد بود:

=worksheet_name!cell_name

که worksheet_name همان نام کاربرگ و cell_name همان آدرس سلول مورد نظر است.

با این توضیحات اکنون در کاربرگ “مجموع” بر روی سلول B1 کلیک کنید تا انتخاب شود و سپس در بخش وارد کردن فرمول، فرمول مذکور را وارد نمایید. با فشردن کلید Enter مشاهده می کنید که در خانه ی B1 از کاربرگ “مجموع” مقدار عددی 1000 نمایش داده می شود (اطلاعات مربوط به سلول B1 از کاربرگ “دی”).

حالا می توانید فرمول را بر اساس نیاز خود بسط دهید و مورد استفاده قرار دهید مثلا برای نمایش مجموع سلولهای B1 از دو کاربرگ “دی” و “بهمن” کافی است در سلول B1 کاربرگ “مجموع” فرمول زیر را تایپ کنید:

=دی!B1+بهمن!B1

 

در فرمول ترکیبی و ساده ی بالا، مقادیر دو سلول B1 از کاربرگهای “دی” و “بهمن” با استفاده از علامت “+” (به اضافه) با همدیگر جمع شده اند. بدین ترتیب هر تغییری که در یکی از دو سلول موجود در کاربرگ های دی یا بهمن اعمال کنید، نتیجه اش در سلول B1 کاربرگ “مجموع” نمایش داده می شود.

بصورت پیش فرض هنگامیکه یک سند اکسل جدید باز می کنید، عرض تمام ردیف ها و طول تمام ستون ها با یک مقدار مشخص نمایش داده می شود. برای مثال در نسخه های جدید اکسل طول ستون ها 15، عرض ردیف ها 8٫38 و فونت پیش فرض نیز Calibri و با شماره 15 می باشد. با تغییر نوع و اندازه فونت، طول و عرض سلول ها نیز متناسب با آن تغییر خواهد کرد. با این حال راه های مختلفی نیز برای تنظیم این طول و عرض بصورت دلخواه وجود دارد که در ادامه ی مطلب به آنها اشاره خواهیم کرد. پس با مودمها همراه باشید.

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

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

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

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

هم اکنون بر روی ناحیه انتخاب شده کلیک راست کرده و گزینه ی Row Height را انتخاب نمایید. به جای کلیک راست همچنین می توانید از کلیدهای میانبر Shift+F10 نیز استفاده نمایید.

پنجره ای نمایش داده می شود که بصورت پیش فرض شامل مقدار قبلی عرض ردیف ها است. مقدار موردنظر خود را وارد کرده و سرانجام بر روی دکمه OK کلیک کنید.

شما می توانید همین کار را برای تغییر طول دسته ای از ستون هادر اکسل نیز انجام دهید. بدین منظور بر روی عنوان ابتدایی ترین ستون موردنظر خود کلیک کرده و نشانه گر موس را به سمت عنوان ستون نهایی خود ببرید. بعد از کلیک راست در ناحیه ی انتخاب شده، بر روی گزینه Column Width کلیک نمایید.

مقدار طول موردنظر خود را وارد کرده و سرانجام بر روی دکمه ی OK کلیک کنید.

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

همچنین یک گزینه ی دیگر نیز وجود دارد که می توانید طول ها را بصورت خودکار تعیین نماید. این اندازه ها با توجه به نوع و اندازه فرمتی که تعیین کرده اید تنظیم می شوند و اهمیتی هم ندارد که سلول های سند شما خالی باشند یا خیر.
بدین منظور می توانید پس از انتخاب ستون های موردنظر بر روی دکمه ی Format کلیک کرده و در داخل منوی Cell Size گزینه ی AutoFit Column Width را انتخاب نمایید. اگر یکی از سلول های ستون انتخاب شده نیز دارای داده ای باشد، طول بر اساس آن تغییر خواهد کرد در غیر اینصورت اگر تمامی سلول ها خالی باشند هیچ تغییری را احساس نخواهید کرد.

همین کار را می توانید اینبار با انتخاب گزینه AutoFit Row Height برای عرض ردیف ها نیز انجام دهید.

شما همچنین می توانید طول کل سلول های موجود در اکسل را به اندازه مورد نظر خود تغییر دهید. عرض سلول ها هم مطمئناً به نوع فونت استفاده شده و سایز آنها بستگی خواهد داشت. بدین منظور ابتدا بر روی دکمه Format کلیک کرده و سپس گزینه ی Default Width را انتخاب کنید.

هم اکنون مقدار دلخواه خود را برای طول سلول ها وارد کرده و سرانجام بر روی دکمه ی OK کلیک کنید.

این هم از هرآنچه که نیاز است درباره نحوه تغییر سایز سلول های اکسل بدانید. امیدوارم لذت برده باشید.

در بین فرمول های موجود در اکسل فرمول index به واقع یکی از 5 فرمول برتر آن است، این فرمول همه کاره، قوی و هوشمند است گرچه در ظاهر ساده به نظر می رسد. این فرمول قادر است تغییر عمده ای در روش آنالیز داده ها و محاسبه اعداد به وجود آورد.

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

چند کابرد ساده از فرمول در اکسل:
فرض کنید شما لیستی 8 تایی از اسامی دارید حال میخواهید بدانید هشتمین آیتم از این لیست چه نامی است، کافی است فرمول زیر را بنویسید


=INDE list, 8)
فرض کنید در ستون سوم از این لیست شماره تلفن وارد شده باشد می خواهید شماره تلفن هشتمین نفر از لیست را بدانید


=INDE list, 8,3)

ترکیب های مختلف فرمول indexدر  اکسل
فرمول index با دو ترکیب بکار می رود


INDE range or table, row number, column number)


در این ترکیب با دادن شماره سطر و ستون یک ناحیه می توان به رفرنس یا مقدار دست یافت


INDE range, row number, column number, area number)


در این ترکیب شما به دادن شماره سطر و ستون به یک مقدار یا رفرنس در یک محدوده خاص دست خواهید یافت



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


=index(list;n)

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


=index(list;n;m)


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


=average(age column)

همچنین می توانید از فرمول زیر نیز استفاده کنید سن افراد در ستون 5 جدول قرار دارد می توان نوشت:


=average(index(list; ;5))


نکته: می توان بجای ردیف از جای خالی یا 0 استفاده کنید همچنین در مورد ستون هم می توان بکار برد.

دلیل چهارم: جستجو به سمت چپ
می دانیم که استفاده از فرمول vlookup امکان جستجو در خلاف جهت را به ما نمی دهد اما با ترکیب فرمول های index&match می توان به این مشکل فائق آمد، در مثال فوق می خواهیم بدانیم کدام فرد سنگین ترین وزن را دارد.

=INDE List[F-Name];MATCH(MA List[weight]);List[weight];0);1)


دلیل پنجم: ایجاد محدوده داینامیک در Excel

تا کنون با برخی از توانایی های تابع index آشنا شده اید که به نظر ساده بوده است، حال به برخی کاربرد ها پیچیده تر این تابع می پردازیم، توانایی واقعی index در ماهیت آن نهفته است شما در ظاهر می بینید که index مقداری را نمایش می دهد اما در واقع این تابع شما را به سلولی که حاوی آن مقدار است ارجاع می دهد به عنوان مثال وقتی می نویسد index(list;8) شما 8 امین مقدار از لیست را می بینید اما در واقع شما به سلول حاوی هشتمین مقدار ارجاع داده شده اید پس حاصل این تابع یک ارجاع است و هرگاه شما نیاز به ارجاعی داشته باشید می توانید از تابع استفاده کنید.
کمی گیج کننده است، با مثال هایی به شرح موضوع می پردازیم:
شما وقتی می خواهید ناحیه ای مانند A1:A10 را جمع بزنید از sum(A1:A10) استفاده می کنید در این فرمول a1 , a10 رفرنس هستند حال به این فرمول توجه کنید:
=sum(A1:index(A1:A50;10))


جواب ها در هر دو یکسان است اما در دومی index از محدوده a1 تا a50 شما را به 10 خانه اول ارجاع می دهد.

مثال1: متوسط قد X نفر اول:
فرض کنید می خواهید متوسط قد x نفر اول افراد لیست را بیابید، x عددی متغیر است در این صورت فرمول زیر را خواهیم داشت:


=AVERAGEA(G4:INDE List[length];K7))


مثال 2: متوسط قد افراد در یک لیست داینامیک
گاهی شما میخواهید متوسط قد همه افراد لیست را داشته باشید اما نمیدانید این افراد چند نفر هستند در این صورت هر بار که داده ای اضافه می شود باید فرمول ها را بروز کنید اما چگونه می توان از فرمول های خودکار استفاده کرد، یکی از راه ها استفاده از فرمول offset است:


=offset(A1;0;0;counta(A:A);1)


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


=A1:index(A:A;counta(A:A))


دلیل ششم: ارجاع به محدوده خاصی از چندین محدوده
سه لیست یا محدوده مجزا دارید list1,list2,list3 میخواهیم متوسط داده های این سه لیست را بدست آوریم لذا از ترکیب دوم تایع index استفاده مکنیم



=AVERAGE(INDE (list1;list2;list3);;;D2))



(list1;list2;list3) نشان دهنده کل محدوه ها و d2 نشانگر شماره محدوده مورد نظر است.


دلیل هفتم: تابع index می تواند آرایه ها را پردازش کند

ماهیت تابع index به گونه ای است که بدون استفاده از CTRL+SHIFT+ENTER داده های آرایه ای را پردازش کند، به عنوان مثال شما می توانید متوسط سنی افرادی که نام آنها با حرف F شروع شده بیایبد


=SUM(INDE ((LEFT(List[F-Name];1))="F")*(List[Age]);0))

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

 

1- تابع  LOOKUP  در اکسل

این تابع دو فرم آرایه‌ای و برداری (Vector) دارد که فرم برداری آن مدنظر ما است. در اکسل به یک محدوده از سلول‌ها که تنها یک سطر یا یک ستون داشته باشد، Vector می‌گویند مثلاً محدوده‌های A1:A88 یا A1:M1 هر دو Vector هستند.

تابع LOOKUP، یک عبارت را در یک Vector جستجو می‌کند و محتوای سلول هم موقعیت با سلول پیدا شده در Vector دیگر را به عنوان خروجی به کاربر می‌دهد.

ساختار تابع LOOKUP به شکل زیر می‌باشد:

=LOOKUP (lookup_value, lookup_vector, result_vector)

آرگومان اول: وارد کردن این آرگومان اجباری است چون بیانگر عبارت مورد نظر برای جستجو می‌باشد. این آرگومان می‌تواند عدد، رشته متنی، Logical Values (شامل صفر و یک یا True و False) یا آدرس یک سلول حاوی عبارت مورد نظر باشد.
آرگومان دوم: این آرگومان بیانگر Vector محل جستجو می‌باشد که وارد کردن آن نیز اجباری است. این آرگومان یک محدوده از اکسل شامل یک سطر یا یک ستون می‌باشد که قرار است آرگومان اول در آن جستجو شود. سلول‌های محدوده‌ی Vector هم می‌توانند حاوی اعداد یا رشته‌های متنی یا Logical Values (شامل صفر و یک یا True و False) باشند.

آرگومان سوم: یک Vector مانند آرگومان دوم و به همان اندازه می‌باشد، مثلاً اگر آرگومان دوم یک بردار افقی با 10 سلول باشد، آرگومان سوم هم باید یک بردار افقی با 10 سلول باشد. در واقع پس از یافتن عبارت مورد جستجو در lookup_vector، تابع LOOKUP محتوای سلول هم تراز با سلول حاوی عبارت جستجو در result_vector را به عنوان خروجی نمایش می‌دهد.

اگر تابع LOOKUP نتواند عبارت آرگومان اول را در vector مورد جستجو پیدا کند، آخرین (بزرگترین) مقدار در lookup_vector را که برابر یا کوچکتر از عبارت مورد جستجو می‌باشد را به عنوان نتیجه‌ی جستجو می‌پذیرد. بنابراین برای اینکه خروجی تابع، صحیح باشد، Vector باید به صورت صعودی مرتب شده باشد. در غیر اینصورت ممکن است تابع LOOKUP جواب صحیح را به ما ندهد. پس به صعودی بودن lookup_vector دقت کنید.

اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو lookup_vector باشد در خروجی تابع خطای N/A# ظاهر می‌شود.

به مثال زیر دقت کنید:

 

دقت داشته باشید که لزومی ندارد دو محدوده lookup_vector و  result_vector مانند مثال بالا، حتماً مجاور هم باشند، بلکه صرفاً هم اندازه بودن این دو محدوده (Vector) کافیست.

---

2- تابع VLOOKUP:

تابع VLOOKUP یا Vertical LOOKUP (جستجوی عمودی) در excel مانند تابع LOOKUP عمل می‌کند. درواقع اگر با تابع LOOKUP آشنا باشید درک VLOOKUP برای شما آسان‌تر خواهد بود، لذا توصیه می‌شود قبل از مشاهده توضیحات تابع VLOOKUP، بخش تابع LOOKUP که در بالا به آن اشاره شده است را  مطالعه کنید.

تابع VLOOKUP می‌تواند یک عبارت را در اولین ستون یک محدوده جستجو کند و در هر یک از ستون‌های موجود در محدوده‌ مورد جستجو، محتوای سلول هم تراز (هم ردیف) با سلول پیدا شده را به عنوان خروجی اعلام نماید. در واقع کلمه Vertical در نام این تابع به جستجو در ستون اشاره می‌کند.

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

 

ساختار این تابع به صورت زیر است:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

تابع VLOOKUP چهار آرگومان دارد، سه آرگومان اول اجباری و آرگومان آخر اختیاری می‌باشد.

آرگومان اول: این آرگومان عبارتی است که کاربر می‌خواهد جستجو کند، در مثال بالا این آرگومان شماره پرسنلی شخص می‌باشد، این آرگومان همانند آنچه در تابع LOOKUP وجود داشت، می‌تواند عدد، رشته متنی، آدرس سلول و یا یک مقدار منطقی (Logical Value) باشد.

اگر محتویات ستون اول محدوده مورد جستجو از نوع متن باشد، می‌توانید از کاراکترهای جایگزین شونده استاندارد در آرگومان اول استفاده کنید. علامت? را می‌توان جایگزین یک کاراکتر و علامت * را می‌توان جایگزین چندین کاراکتر دانست.

آرگومان دوم: این آرگومان یک محدوده از اکسل می‌باشد، تمام جدول داده‌ها به عنوان این آرگومان به تابع معرفی می‌گردد، در مثال بالا محدوده‌ی A2:C10 نشانگر آرگومان دوم می‌باشد، همینطور می‌توان نام محدوده را به عنوان آرگومان دوم درج کرد

نکته 1) عملیات جستجوی آرگومان اول تنها در ستون اول محدوده‌ی معرفی شده به عنوان آرگومان دوم انجام می‌شود. بنابراین مهم نیست که محدوده‌ی وارد شده دارای چند ستون باشد.

آرگومان سوم: این آرگومانیک عدد می‌باشد و شماره ستون داده‌ی مورد نظر برای استخراج از جدول است، ستون شماره 1 همان ستون یا Vector جستجو شده و ستون شماره 2 ستون مجاور می‌باشد و به همین ترتیب. در مثال بالا، این آرگومان عدد 3 می‌باشد، زیرا ستون حاوی نام شخص ستون سوم از جدول است.

نکته 2) اگر آرگومان سوم تابع VLOOKUP کمتر از یک باشد خروجی تابع خطای!VALUE# و اگر این عدد بزرگتر از تعداد کل ستون‌ها باشد خروجی تابع خطای!REF# خواهد بود.

آرگومان چهارم: اگرچه وارد کردن این آرگومان، اختیاری است اما بسیار مهم می‌باشد. این آرگومان می‌تواند True یا False باشد.

اگر این آرگومان True باشد یا نادیده گرفته شود، در اینصورت تابع VLOOKUP رفتار زیر را انجام می‌دهد:

• اولاً، داده‌های محدوده مورد جستجو (ستون اول) باید همانند آنچه در تابع LOOKUP وجود داشت به صورت صعودی مرتب شده باشند تا مطمئن باشیم که خروجی تابع قابل اعتماد است.
• دوماً، در صورت نیافتن عبارت مورد جستجو در ستون اول، دقیقاً مشابه تابع LOOKUP، تابع VLOOKUP نیز بزرگترین مقدار کوچکتر از عبارت مورد جستجو را به عنوان پاسخ می‌پذیرد.
• سوماً، مجدداً مشابه تابع LOOKUP، اگر تابع VLOOKUP نتواند عبارت مورد جستجو را در ستون اول پیدا کند، آخرین (بزرگترین) مقدار در آن Vector که برابر یا کوچکتر از عبارت مورد جستجو می‌باشد را به عنوان نتیجه‌ی جستجو می‌پذیرد (به صعودی بودن داده‌ها دقت کنید).

حال اگر آرگومان چهارم False باشد، False برای تابع به معنی Exact Match است یعنی در این حالت تابع تنها داده‌ای را به عنوان پاسخ می‌پذیرد که دقیقاً مانند عبارت مورد جستجو باشد و اگر آن را پیدا نکرد خروجی تابع برابر خطای N/A# خواهد بود.

در صورتی که آرگومان چهارم False باشد نیازی به چینش صعودی داده‌های ستون مورد جستجو نیست در صورت وجود داشتن چند جواب، اولین مورد پیدا شده به عنوان جواب پذیرفته می‌شود.

آرگومان چهارم هرچه که باشد (True یا False)، مانند تابع LOOKUP، اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو ستون مورد جستجو (Vector) باشد خروجی تابع خطای N/A# است.

---

3- تابع HLOOKUP:

تابع HLOOKUP یا Horizontal LOOKUPn (جستجوی افقی) در اکسل، در ساختار و طریقه عملکرد هیچ تفاوتی با تابع VLOOKUP ندارد، تنها تفاوت این دو تابع در افقی و عمودی بودن داده‌ها است، تابع HLOOKUP برای جدول‌های افقی کاربرد دارد و سطر اول داده‌ها را جستجو می‌کند. با یادگیری و فهم توابع LOOKUP و VLOOKUP مشکلی در کار با تابع HLOOKUP نخواهید داشت.