Excel - dodatne funkcije u Excelu koje MS nije ugradio

TUTORIALI, tips and tricks u Office 2007 paketu ( Access, Word, Excel, FrontPage, Powerpoint, Publisher itd....)
Locked
User avatar
IvanC
Administrator
Posts: 576
Joined: Tue Sep 20, 2005 9:54 am
Contact:

Excel - dodatne funkcije u Excelu koje MS nije ugradio

Post by IvanC »

MOREFUNC za Excel je besplatna dodatna aplikacija koja se integrira u Excelov izbornik, a djelo je Laurent Longre.
Ove funkcije kompatibilne su s programom Excel 95 – 2007 Aplikacija podržava dva jezika, engleski i francuski.

Popis funkcija koje sadrži aplikacija

Information functions

EVAL => Evaluates a formula or expression
FILENAME => Name of the workbook
FORMULATEXT => Returns the formula of a cell
INDIRECT.EXT => same as INDIRECT working also with closed workbooks
MRFVERSION => Morefunc's version number
PAGENUM => Page number of a cell
SHEETNAME => Name of a sheet in the workbook
XLM.GET.CELL => Informations about the formatting, location or contents of a cell or a range
XLM.GET.DOCUMENT => Informations about the workbook (number of sheets, author, summary...)
XLM.GET.WORKBOOK => Informations about the current sheet or the workbook (protection, type of sheet...)
XLM.GET.WORKSPACE => Informations about the workspace (country version of Excel, startup path...)

Math functions

CHBASE => Converts a value from a base into another base
GEOMEAN.EXT => Same as GEOMEAN, higher capacity
HEX.AND => Bitwise AND between hexadecimal numbers
HEX.FORMAT => Formats and checks the validity of an hexadecimal number
HEX.NEG => Inverts the sign of an hexadecimal number (2's complement)
HEX.NOT => Negates (NOT) an hexadecimal number
HEX.OR => Bitwise OR between hexadecimal numbers
HEX.SUM => Adds hexadecimal numbers
MATRIXROUND => Adjusts an array of rounded numbers (=>sum of percentages = always 100%)
MDETERM.EXT => Same as MDETERM, faster and supporting larger arrays
MINVERSE.EXT => Same as MINVERSE, faster and supporting larger arrays
MMULT.EXT => Same as MMULT, faster and supporting larger arrays
MRAND => Series of random integers without repetitions
PN.ISPRIME => Returns TRUE if a number is a prime number (up to 255 digits)
PN.NEXT => Returns the next prime number (up to 255 digits)
WEIGHTED.AVERAGE => Returns a weighted average

Statistical functions

COUNTDIFF => Number of unique values in a range or array
COUNTIF.3D => Same as COUNTIF working with 3D references
MMAX => Returns the N highest numbers of a range or an array
MMIN => Returns the N lowest numbers of a range or an array
STDEV.GROUPED => Standard deviation based on a sample (data grouped in classes)
STDEVP.GROUPED => Standard deviation based on the entire population (grouped data)
VAR.GROUPED => Variance based on a sample (data grouped in classes)
VARP.GROUPED => Variance based on the entire population (grouped data)

Text functions

ANSI128 => Strips the accents from a string
MCONCAT => Concatenates all values in a range or an array
NBTEXT => Converts a positive number into spelled-out text (supports 13 languages)
REGEX.COMP => Returns TRUE if a text matches a regular expression
REGEX.COUNT => Counts the matches of a regular expression in a string
REGEX.FIND => Location of a substring matching a regular expression
REGEX.LEN => Counts the matches of a regular expression in a string
REGEX.MID => Returns a substring matching a regular expression
REGEX.SUBSTITUTE => Replaces a substring matching a regular expression with a new string
TEXTREVERSE => Reverses the characters of a string
WMID => Extracts a word or a group of words from a text
WORDCOUNT => Number of words in a text

Sorting
HSORT => Sorts a range or an array (supports up to 14 sort keys) - vertical
HSORT.IDX => Sort index of a range or array (supports up to 14 sort keys) - vertical
UNIQUEVALUES => Returns the unique items of a range or an array
VSORT => Sorts a range or an array (supports up to 14 sort keys) - horizontal
VSORT.IDX => Sort index of a range or array (supports up to 14 sort keys) - horizontal

Miscellaneous functions
ARRAY.FILTER => Returns only the visible cells of a range (in an array)
ARRAY.JOIN => Joins various items (ranges, unions, strings, numbers...) in a single array
GETV => Returns a temporary variable value stored by SETVAR
INTVECTOR => Returns a vector of consecutive integers in the ascending order
LASTROW => Last filled cell in a column
RECALL => Returns the previous value of the calling cell before the last calculation
REGION => Returns the region of a cell (like the VBA CurrentRegion method)
SETV => Stores a value in a temporary variable
SHEETOFFSET => Returns a range that is a specified number of sheets from a range
THREED => Coerces a 3D range into a single array - enables array formulae with 3D references
UNION.OFFSET => OFFSET-like function working with unions (discontinguous ranges)

Za download i više detalja Morefunc na Excelu 2007 i 2013 MOREFUNC dodatne funkcije u Excelu
Administrator foruma
IvanC
Locked