کمک مجانی کمپیوتر  
Array Formulas Many of the formulas described here are Array Formulas, which are a special type of formula in Excel. If you are not familiar with Array Formulas, click here. Array To Column Averaging Values In A Range You can use Excel's built in
=AVERAGE
function to average a range of values. By using it Averaging Values Between Two Numbers Use the array formula =AVERAGE(IF((A1:A60>=Low)*(A1:A60<=High),A1:A60)) Where Low and High are the values between which you want to average. Averaging The Highest N Numbers In A Range To average the N largest numbers in a range, use the array formula =AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10")))) Change "1:10" to "1:N" where N is the number of values to average. Averaging The Lowest N Numbers In A Range To average the N smallest numbers in a range, use the array formula =AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:10")))) Change "1:10" to "1:N" where N is the number of values to average. In all of the formulas above, you can
use =SUM
instead of
=AVERAGE to sum, rather Counting Values
Between Two Numbers =SUM((A1:A10>=5)*(A1:A10<=10)) To sum the same numbers, use the following array formula: =SUM((A1:A10>=5)*(A1:A10<=10)*A1:A10) Counting Characters In A String The following formula will count the number of "B"s, both upper and lower case, in the string in B1. =LEN(B1)LEN(SUBSTITUTE(SUBSTITUTE(B1,"B",""),"b","")) Date And Time
Formulas Other Date Related Procedures are described on the following pages. Duplicate And
Unique Values In A Range Dynamic Ranges You can define a name to refer to a range whose size varies depending on its contents. For example, you may want a range name that refers only to the portion of a list of numbers that are not blank. such as only the first N nonblank cells in A2:A20. Define a name called MyRange, and set the Refers To property to: =OFFSET(Sheet1!$A$2,0,0,COUNTA($A$2:$A$20),1) Be sure to use absolute cell references in the formula. Also see then Named Ranges page for more information about dynamic ranges. Finding The Used
Part Of A Range To find the range that contains data, use the following array formula:
=ADDRESS(ROW(DataRange2),COLUMN(DataRange2),4)&":"& To find the number of rows that contain data, use the following array formula: =(MAX((DataRange2<>"")*ROW(DataRange2)))ROW(DataRange2)+1 This will return the number 11, indicating that the first 11 rows of DataRange2 contain data. To find the last entry in the first column of DataRange2, use the following array formula:
=INDIRECT(ADDRESS(MAX((DataRange2<>"")*ROW(DataRange2)), To find the last entry in the second
column of
DataRange2, use the following
array formula: First And Last Names Suppose cell A2 contains the name "John A Smith". To return the last name, use
=RIGHT(A2,LEN(A2)FIND("*",SUBSTITUTE(A2," ","*",LEN(A2) To return the first name, including the middle name (if present), use
=LEFT(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2) To return the first name, without the middle name (if present), use =LEFT(B2,FIND(" ",B2,1)) We can extend these ideas to the
following. Suppose
A1
contains the Returning
First Word In A String Returning
Last Word In A String Returning
All But First Word In A String Returning
Any Word Or Words In A String
=MID(A10,SMALL(IF(MID(" "&A10,ROW(INDIRECT This formula can be extended to get any set of words in the string. To get the words from M for N words (e.g., the 5th word for 3, or the 5th, 6th, and 7th words), use the following array formula:
=MID(A10,SMALL(IF(MID(" "&A10,ROW(INDIRECT Note that in the above array
formulas, the
{0,1} and
{1,1}
are enclosed in array braces (curly brackets {} ) not parentheses. Grades High And Low
Values Another method to do this, without using circular references, is provided by Laurent Longre, and uses the CALL function to access the Excel4 macro function library. Click here for details. Left Lookups
=INDIRECT(ADDRESS(ROW(Rng)+MATCH(C1,Rng,0)1,COLUMN(Rng)ColsToLeft))
Or
Where
Rng
is the range containing the lookup values, and
ColsToLeft is the number of columns See the Lookups page for many more examples of lookup formulas.
Minimum And
Maximum Values In A Range To return the address of the cell
containing the first (or only) instance of the minimum of a list,
=ADDRESS(MIN(IF(NumRange=MIN(NumRange),ROW(NumRange))),COLUMN(NumRange),4) This function returns B2, the address of the first '1' in the range. Address Of The Last Minimum In A Range To return the address of the cell
containing the last (or only) instance of the minimum of a list,
=ADDRESS(MAX(IF(NumRange=MIN(NumRange),ROW(NumRange)*(NumRange<>""))), This function returns B4, the address of the last '1' in the range. Address Of First Maximum In A Range To return the address of the cell
containing the first instance of the maximum of a list, =ADDRESS(MIN(IF(NumRange=MAX(NumRange),ROW(NumRange))),COLUMN(NumRange),4) This function returns B1, the address of the first '5' in the range. Address Of The Last Maximum In A Range To return the address of the cell
containing the last instance of the maximum of a list, =ADDRESS(MAX(IF(NumRange=MAX(NumRange),ROW(NumRange)*(NumRange<>""))), This function returns B5, the address of the last '5' in the range. Download a workbook illustrating these formulas. Most Common String In A Range =INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0)) Where Rng is the range containing the data. Ranking Numbers =IF(ROW()ROW(TopRng)+1>TopN,"",LARGE(RankRng,ROW()ROW(TopRng)+1)) To return the TopN smallest values of RankRng, use =IF(ROW()ROW(TopRng)+1>TopN,"",SMALL(RankRng,ROW()ROW(TopRng)+1)) The list of numbers returned by these
functions will automatically change as you change the Download a workbook illustrating these formulas. See the Ranking page for much more information about ranking numbers in Excel. Removing Blank
Cells In A Range Summing Every
Nth Value Suppose your data is in
A1:A20,
and N = 3
is in D1.
The following
array formula will sum the values in
A3, A6, A9,
etc. If you want to sum the values in
A1, A4, A7, etc., use the following array formula: If your data ranges does not begin in
row 1, the formulas are slightly more complicated. Suppose our data is
in B3:B22,
and N = 3
is in D1.
To sum the values in rows
5, 8, 11,
etc, use the following
array formula: If you want to sum the values in rows
3, 6, 9,
etc, use the following
array formula: Download a workbook illustrating these formulas. Miscellaneous Suppose our active sheet is named "MySheet" in the file C:\Files\MyBook.Xls. To return the full sheet name (including the file path) to a cell, use =CELL("filename",A1) Note that the argument to the
=CELL
function is the word
"filename"
in quotes, not your This will return "C:\Files\[MyBook.xls]MySheet" To return the sheet name, without the path, use =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1, This will return "MySheet" File Name Suppose our active sheet is named "MySheet" in the file C:\Files\MyBook.Xls. To return the file name without the path, use =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", This will return "MyBook.xls" To return the file name with the path, use either =LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))) Or =SUBSTITUTE(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]", The first syntax will return "C:\Files\[MyBook.xls]" The second syntax will return "C:\Files\MyBook.xls" In all of the examples above, the
A1
argument to the
=CELL
function forces Excel to get the sheet name from the sheet containing
the formula. Without it, and Excel calculates the
=CELL
function when another sheet is active, the cell would contain the name
of the active sheet, not the sheet actually containing the formula.

