Formules et fonctions Excel

• Excel formulas are expressions that perform calculations on values within a spreadsheet.
• Excel functions are pre-defined formulas that perform specific calculations, simplifying complex operations.

Formula Basics

• Formulas always begin with an equals sign (=).
• After the equals sign, you enter constants, cell references, operators, and functions to create expressions.
• Constants are numbers or text values entered directly into a formula.
• Cell references refer to the value in a specific cell (e.g., A1, B2:B10).
• Operators specify the type of calculation to perform (+, -, *, /, ^).

Cell References

• Relative references adjust when a formula is copied to another cell (e.g., if you copy a formula with A1, it will change to A2, A3, etc.).
• Absolute references remain constant, regardless of where the formula is copied – use the dollar sign ($) to fix a row or column (e.g., $A$1 remains A1).
• Mixed references have one relative and one absolute part (e.g., $A1 or A$1).

Operators

• Arithmetic operators: + (addition), - (subtraction), * (multiplication), / (division), ^ (exponentiation).
• Comparison operators: = (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), <> (not equal to).
• Text concatenation operator: & (joins text strings together).
• Reference operators: : range Operator, , union Operator.

Order of Operations

• Excel follows the standard order of operations: Parentheses, Exponents, Multiplication and Division, Addition and Subtraction (PEMDAS).
• Use parentheses to control the order of calculations.

Common Functions

• SUM: Adds values in a range of cells (e.g., =SUM(A1:A10)).
• AVERAGE: Calculates the average of values in a range of cells (e.g., =AVERAGE(A1:A10)).
• COUNT: Counts the number of cells in a range that contain numbers (e.g., =COUNT(A1:A10)).
• COUNTA: Counts the number of cells in a range that are not empty (e.g., =COUNTA(A1:A10)).
• MAX: Returns the largest value in a range of cells (e.g., =MAX(A1:A10)).
• MIN: Returns the smallest value in a range of cells (e.g., =MIN(A1:A10)).
• IF: Performs a logical test and returns one value if true, and another value if false (e.g., =IF(A1>0, "Positive", "Negative")).
• VLOOKUP: Searches for a value in the first column of a table and returns a value in the same row from a specified column (e.g., =VLOOKUP(A1, B1:D10, 3, FALSE)).
• HLOOKUP: Searches for a value in the first row of a table and returns a value in the same column from a specified row.
• INDEX: Returns a value or the reference to a value from within a table or range.
• MATCH: Searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
• COUNTIF: Counts the number of cells within a range that meet a given condition (e.g., =COUNTIF(A1:A10, ">5")).
• SUMIF: Sums the values in a range that meet a given criterion (e.g., =SUMIF(A1:A10, ">5", B1:B10)).
• AVERAGEIF: Finds the average of the values in a range that meet a given criterion.
• AND: Returns TRUE if all its arguments are TRUE.
• OR: Returns TRUE if any argument is TRUE.
• NOT: Reverses the logic of its argument.

Date and Time Functions

• TODAY: Returns the current date.
• NOW: Returns the current date and time.
• DATE: Returns the serial number of a particular date.
• TIME: Returns the serial number of a particular time.
• YEAR: Returns the year of a date.
• MONTH: Returns the month of a date.
• DAY: Returns the day of a date.

Text Functions

• LEFT: Extracts a specified number of characters from the beginning of a text string.
• RIGHT: Extracts a specified number of characters from the end of a text string.
• MID: Extracts a specified number of characters from a text string, starting at a specified position.
• LEN: Returns the number of characters in a text string.
• UPPER: Converts a text string to uppercase.
• LOWER: Converts a text string to lowercase.
• PROPER: Converts a text string to proper case (first letter of each word capitalized).
• TRIM: Removes extra spaces from a text string.
• CONCATENATE: Joins several text strings into one text string.
• TEXT: Formats a value as text in a specific format.
• FIND: Finds one text string within another (case-sensitive).
• SEARCH: Finds one text string within another (not case-sensitive).
• REPLACE: Replaces part of a text string with a different text string.
• SUBSTITUTE: Substitutes new text for old text in a text string.

Logical Functions

• Used to perform logical tests and make decisions based on the results.
• IF function: Checks a condition and returns one value if the condition is true, and another value if the condition is false.
• AND function: Checks if all conditions are true.
• OR function: Checks if at least one condition is true.
• NOT function: Reverses the value of a condition.
• TRUE function: Returns the logical value TRUE.
• FALSE function: Returns the logical value FALSE.
• IFS function: Checks multiple conditions.

Lookup and Reference Functions

• Used to find and retrieve data from tables or ranges.
• VLOOKUP function: Searches for a value in the first column of a table and returns a value in the same row from a specified column.
• HLOOKUP function: Searches for a value in the first row of a table and returns a value in the same column from a specified row.
• INDEX function: Returns a value or the reference to a value from within a table or range.
• MATCH function: Searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
• CHOOSE function: Chooses a value from a list of values based on an index number.
• INDIRECT function: Returns the reference specified by a text string.

Math and Trigonometry Functions

• ABS: Returns the absolute value of a number.
• INT: Returns the integer part of a number.
• ROUND: Rounds a number to a specified number of digits.
• ROUNDUP: Rounds a number up to the nearest integer or specified number of digits.
• ROUNDDOWN: Rounds a number down to the nearest integer or specified number of digits.
• MOD: Returns the remainder after a number is divided by a divisor.
• SQRT: Returns the square root of a number.
• RAND: Returns a random number between 0 and 1.
• RANDBETWEEN: Returns a random integer between two specified numbers.
• SUMPRODUCT: Multiplies corresponding components in the given arrays, and returns the sum of those products.
• AGGREGATE: Applies different aggregate functions to a list or database.
• SUBTOTAL: Returns a subtotal in a list or database.

Statistical functions

• AVERAGE: Returns the average of its arguments.
• MEDIAN: Returns the median of the given numbers.
• MODE: Returns the most frequently occurring, or repetitive, value in an array or range of data.
• STDEV: Estimates standard deviation based on a sample. The standard deviation is a measure of how widely dispersed the values are from the average value (the mean).
• VAR: Estimates variance based on a sample.

Financial Functions

• PV: Returns the present value of an investment.
• FV: Returns the future value of an investment.
• PMT: Returns the periodic payment for an annuity.
• RATE: Returns the interest rate per period of an annuity.
• NPER: Returns the number of periods for an investment.

Array Formulas

• Perform calculations on multiple values at once.
• To enter an array formula, press Ctrl + Shift + Enter.
• Array formulas are enclosed in curly braces {}.
• Can perform complex calculations that are difficult or impossible with standard formulas.

Formula Auditing

• Excel provides tools for auditing formulas to identify errors and understand formula dependencies.
• Trace precedents: Shows which cells are used in a formula.
• Trace dependents: Shows which formulas use a specific cell.
• Error checking: Identifies common formula errors.
• Evaluate formula: Steps through a formula calculation to show intermediate results.

Formula Errors

• #DIV/0!: Occurs when dividing by zero.
• #NAME?: Occurs when Excel doesn't recognize text in a formula.
• #N/A: Occurs when a value is not available.
• #REF!: Occurs when a cell reference is invalid.
• #VALUE!: Occurs when the wrong type of argument is used in a function.
• #####: Occurs when a cell is not wide enough to display the value.