توابع اکسل 4

Engineering functions	توابع مهندسی
Function	Description
BESSELI	Returns the modified Bessel function In(x)
BESSELJ	Returns the Bessel function Jn(x)
BESSELK	Returns the modified Bessel function Kn(x)
BESSELY	Returns the Bessel function Yn(x)
BIN2DEC	Converts a binary number to decimal
BIN2HEX	Converts a binary number to hexadecimal
BIN2OCT	Converts a binary number to octal
COMPLEX	Converts real and imaginary coefficients into a complex number
CONVERT	Converts a number from one measurement system to another
DEC2BIN	Converts a decimal number to binary
DEC2HEX	Converts a decimal number to hexadecimal
DEC2OCT	Converts a decimal number to octal
DELTA	Tests whether two values are equal
ERF	Returns the error function
ERFC	Returns the complementary error function
GESTEP	Tests whether a number is greater than a threshold value
HEX2BIN	Converts a hexadecimal number to binary
HEX2DEC	Converts a hexadecimal number to decimal
HEX2OCT	Converts a hexadecimal number to octal
IMABS	Returns the absolute value (modulus) of a complex number
IMAGINARY	Returns the imaginary coefficient of a complex number
IMARGUMENT	Returns the argument theta, an angle expressed in radians
IMCONJUGATE	Returns the complex conjugate of a complex number
IMCOS	Returns the cosine of a complex number
IMDIV	Returns the quotient of two complex numbers
IMEXP	Returns the exponential of a complex number
IMLN	Returns the natural logarithm of a complex number
IMLOG10	Returns the base-10 logarithm of a complex number
IMLOG2	Returns the base-2 logarithm of a complex number
IMPOWER	Returns a complex number raised to an integer power
IMPRODUCT	Returns the product of from 2 to 29 complex numbers
IMREAL	Returns the real coefficient of a complex number
IMSIN	Returns the sine of a complex number
IMSQRT	Returns the square root of a complex number
IMSUB	Returns the difference between two complex numbers
IMSUM	Returns the sum of complex numbers
OCT2BIN	Converts an octal number to binary
OCT2DEC	Converts an octal number to decimal
OCT2HEX	Converts an octal number to hexadecimal

excel help

توابع اکسل 3

Date and time functions	توابع تاریخ و زمان
Function	Description
DATE	Returns the serial number of a particular date
DATEVALUE	Converts a date in the form of text to a serial number
DAY	Converts a serial number to a day of the month
DAYS360	Calculates the number of days between two dates based on a 360-day year
EDATE	Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH	Returns the serial number of the last day of the month before or after a specified number of months
HOUR	Converts a serial number to an hour
MINUTE	Converts a serial number to a minute
MONTH	Converts a serial number to a month
NETWORKDAYS	Returns the number of whole workdays between two dates
NOW	Returns the serial number of the current date and time
SECOND	Converts a serial number to a second
TIME	Returns the serial number of a particular time
TIMEVALUE	Converts a time in the form of text to a serial number
TODAY	Returns the serial number of today's date
WEEKDAY	Converts a serial number to a day of the week
WEEKNUM	Converts a serial number to a number representing where the week falls numerically with a year
WORKDAY	Returns the serial number of the date before or after a specified number of workdays
YEAR	Converts a serial number to a year
YEARFRAC	Returns the year fraction representing the number of whole days between start_date and end_date

توابع اکسل 2

Database functions
Function	Description
DAVERAGE	Returns the average of selected database entries
DCOUNT	Counts the cells that contain numbers in a database
DCOUNTA	Counts nonblank cells in a database
DGET	Extracts from a database a single record that matches the specified criteria
DMAX	Returns the maximum value from selected database entries
DMIN	Returns the minimum value from selected database entries
DPRODUCT	Multiplies the values in a particular field of records that match the criteria in a database
DSTDEV	Estimates the standard deviation based on a sample of selected database entries
DSTDEVP	Calculates the standard deviation based on the entire population of selected database entries
DSUM	Adds the numbers in the field column of records in the database that match the criteria
DVAR	Estimates variance based on a sample from selected database entries
DVARP	Calculates variance based on the entire population of selected database entries

 

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

توابع اکسل 1

	فهرست تمام توابع اکسل مقدماتی و پیشرفته و آماری و ...  دسته بندی شده
Database functions	توابع پایگاه داده
Date and time functions	توابع تاریخ و زمان
Engineering functions	توابع مهندسی
Financial functions	توابع مالی
Information functions	توابع اطلاعاتی
Logical functions	توابع منطقی
Lookup and reference functions	توابع جستجو و آدرس
Math and trigonometry functions	توابع ریاضیات و مثلثات
Statistical functions	توابع آماری
Text functions	توابع متن
External functions	توابع خارجی

ادامه نوشته

توابع جستجو 3

تابع  VLOOKUP

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

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

پارامترها به ترتیب عبارتند از :

1. مقدار جستجو
2. آدرس کل جدول
3. شماره ستونی که قصد داریم مقدار آنرا بیابیم .
4. عدد یک برای لیست صعودی ، عدد -1 برای لیست نزولی ، عدد صفر برای لیست نامنظم

 

شرح کامل این ساختار به زبان اصلی از راهنمای اکسل :

 EXCEL HELP VLOOKUP:

Lookup_value    is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array   is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

• If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

• You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.

• The values in the first column of table_array can be text, numbers, or logical values.

• Uppercase and lowercase text are equivalent.

Col_index_num    is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup    is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

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

 

 

 

 

 

 

 

 

ترکیب دو تابع MATCH , INDEX

مثال : در جدول داده شده مثال قبل ، فرمولی طراحی نمایید که هرگاه نام شخص نوشته شود ، شماره پرسنلی نمایش داده شود . ( ترکیب دو تابع MATCH , INDEX  )

 

توابع آدرس و جستجو 2

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

۱- تابع   index

شکل این تابع به این صورت است :            

=index(  شماره ردیف داده در آرایه  ; آدرس آرایه مورد جستجو ) 

این تابع در آرایه ( یک محدوده تک ستونی)جستجو ، به شماره ردیف داده شده وارد می شود و سپس داده را که در آن ردیف قرار گرفته نمایش می دهد .مثال در فرمول زیر  b6 تا b11 محدوده ای از نام هاست که نام شماره 4 در لیست ( توضیح علی ) مورد نظر ماست .

=INDEX(B6:B11;4 )

۲- تابع  match

شکل این تابع به این صورت است :            

=match(  آرایه جستجو  ;  داده مورد جستجو; match type ) 

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

match type : برای حالتی که آرایه نزولی است عدد -1 برای حالتی که صعودی است عدد یک و برای حالتی که آرایه نامنظم پر شده است عدد صفر است .

 

مثال : در مثال زیر اگر نام نوشته شود - شماره ردیف آن نمایش داده شود و اگر شماره ردیف نوشته شود نام نمایش داده شود .

فرم سازی 3

 

نحوه ساخت فرم ها

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

ابزار گزارش گیری 2

 

مطالبی درباره  

شیوه استفاده از توابع sumif  , countif

در گزارش گیری از انبوه داده ها

 

 

تمرین تابع subtotal

این تابع دارای ۱۱ تابع درونی است و هر کدام ازاین توابع با یک عدد از یک تا یازده مشخص شده و سپس در محدوده داده شده عمل می کنند .

این یازده تابع عبارتند از :

 

Function_num (includes hidden values)	Function_num     (ignores hidden values)	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

 

 

 

 

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

 به طور مثال در جدول زیر جمع دریافتی های علی در ماه مهر چقدر است ؟

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

 

 

هرگاه بخواهیم سطرهای پنهان شده در محاسبات دخیل نشوند از اعداد با پیش شماره 100 استفاده می کنیم .  ( XP 2003 )

 

در فایل زیر مطلوبست:

1.  جمع کل دریافتی های علی ؟
2. میانگین کل دریافتی های رضا؟
3. جمع کل دریافتی های علی در ماه مهر؟
4. جمع کل دریافتی های علی در مها مهر از بانک ملی؟
5. جمع کل درافتی های علی و رضا با هم ؟
6. جمع کل درافتی هایی که از بانک سپه انجام شده ؟

 

دانلود تمرین تابع subtotal