Excel - Formules, fonctions et productivité

*Ultime référence Excel pour les formules, fonctions, raccourcis et fonctions de productivité *

Excel est l'application de tableur la plus populaire au monde, utilisée par des millions de personnes pour l'analyse des données, la modélisation financière et l'intelligence des affaires. Cette feuille de triage complète couvre les formules essentielles, les fonctions, les raccourcis et les fonctionnalités avancées pour augmenter votre productivité Excel.

Formules et fonctions de base

Fonctions mathématiques essentielles

# Basic arithmetic
=A1+B1          # Addition
=A1-B1          # Subtraction
=A1*B1          # Multiplication
=A1/B1          # Division
=A1^B1          # Exponentiation

# Mathematical functions
=SUM(A1:A10)    # Sum range of cells
=AVERAGE(A1:A10) # Average of range
=MIN(A1:A10)    # Minimum value
=MAX(A1:A10)    # Maximum value
=COUNT(A1:A10)  # Count numeric values
=COUNTA(A1:A10) # Count non-empty cells

# Rounding functions
=ROUND(A1,2)    # Round to 2 decimal places
=ROUNDUP(A1,0)  # Round up to nearest integer
=ROUNDDOWN(A1,0) # Round down to nearest integer
=INT(A1)        # Remove decimal portion
=ABS(A1)        # Absolute value

Fonctions textuelles

# Text manipulation
=LEN(A1)        # Length of text
=LEFT(A1,5)     # First 5 characters
=RIGHT(A1,3)    # Last 3 characters
=MID(A1,2,4)    # 4 characters starting at position 2
=UPPER(A1)      # Convert to uppercase
=LOWER(A1)      # Convert to lowercase
=PROPER(A1)     # Proper case (title case)

# Text combination and splitting
=CONCATENATE(A1,B1) # Combine text (legacy)
=A1&B1          # Combine text (modern)
=CONCAT(A1:C1)  # Combine range of cells
=TEXTJOIN(",",TRUE,A1:C1) # Join with delimiter

# Text searching and replacing
=FIND("text",A1) # Find position of text (case sensitive)
=SEARCH("text",A1) # Find position of text (case insensitive)
=SUBSTITUTE(A1,"old","new") # Replace text
=TRIM(A1)       # Remove extra spaces
```_

### Fonctions de la date et de l'heure

```text
# Current date and time
=TODAY()        # Current date
=NOW()          # Current date and time
=DATE(2024,12,25) # Create specific date
=TIME(14,30,0)  # Create specific time (2:30 PM)

# Date calculations
=DATEDIF(A1,B1,"D") # Days between dates
=DATEDIF(A1,B1,"M") # Months between dates
=DATEDIF(A1,B1,"Y") # Years between dates
=WEEKDAY(A1)    # Day of week (1=Sunday)
=MONTH(A1)      # Extract month number
=YEAR(A1)       # Extract year
=DAY(A1)        # Extract day

# Date formatting
=TEXT(A1,"mm/dd/yyyy") # Format date
=TEXT(A1,"dddd")       # Full day name
=TEXT(A1,"mmmm")       # Full month name
```_

## Fonctions avancées

### Fonctions logiques

```text
# Conditional logic
=IF(A1>10,"High","Low") # Basic IF statement
=IF(A1>20,"High",IF(A1>10,"Medium","Low")) # Nested IF

# Multiple conditions
=AND(A1>10,B1<5)    # Both conditions true
=OR(A1>10,B1<5)     # Either condition true
=NOT(A1>10)         # Opposite of condition

# Advanced conditional functions
=IFS(A1>20,"High",A1>10,"Medium",TRUE,"Low") # Multiple IF conditions
=SWITCH(A1,1,"One",2,"Two",3,"Three","Other") # Switch statement

Fonctions de recherche

# VLOOKUP (Vertical lookup)
=VLOOKUP(A1,Table1,2,FALSE) # Exact match lookup
=VLOOKUP(A1,Table1,2,TRUE)  # Approximate match lookup

# HLOOKUP (Horizontal lookup)
=HLOOKUP(A1,Table1,2,FALSE) # Horizontal table lookup

# INDEX and MATCH (more flexible)
=INDEX(B:B,MATCH(A1,A:A,0)) # Find value using INDEX/MATCH
=INDEX(Table1,MATCH(A1,Table1[Name],0),2) # Table lookup

# XLOOKUP (modern replacement for VLOOKUP)
=XLOOKUP(A1,Table1[Name],Table1[Value]) # Modern lookup function

# Multiple criteria lookup
=INDEX(C:C,MATCH(1,(A:A=A1)*(B:B=B1),0)) # Array formula lookup

Fonctions statistiques

# Descriptive statistics
=MEDIAN(A1:A10)     # Middle value
=MODE(A1:A10)       # Most frequent value
=STDEV(A1:A10)      # Standard deviation
=VAR(A1:A10)        # Variance
=PERCENTILE(A1:A10,0.9) # 90th percentile

# Conditional statistics
=SUMIF(A:A,">10",B:B)      # Sum if condition met
=COUNTIF(A:A,"Text")       # Count if condition met
=AVERAGEIF(A:A,">10",B:B)  # Average if condition met

# Multiple criteria
=SUMIFS(C:C,A:A,">10",B:B,"<5")     # Sum with multiple criteria
=COUNTIFS(A:A,">10",B:B,"Text")     # Count with multiple criteria
=AVERAGEIFS(C:C,A:A,">10",B:B,"<5") # Average with multiple criteria

Analyse des données Outils

Tableaux pivotants

# Pivot table creation (Alt+N+V)
1. Select data range
2. Insert > PivotTable
3. Drag fields to areas:
   - Rows: Categories to group by
   - Columns: Additional grouping
   - Values: Data to summarize
   - Filters: Data to filter

# Pivot table functions
=GETPIVOTDATA("Sales",A3,"Region","East") # Extract pivot data

Validation des données

# Create dropdown lists
1. Select cells
2. Data > Data Validation
3. Allow: List
4. Source: =Sheet1!A1:A10 or "Option1,Option2,Option3"

# Validation formulas
=AND(A1>=0,A1<=100)     # Number between 0-100
=LEN(A1)<=10            # Text length limit
=ISNUMBER(A1)           # Must be number

Formatage conditionnel

# Formula-based formatting
=MOD(ROW(),2)=0         # Highlight every other row
=A1>AVERAGE($A$1:$A$10) # Highlight above average
=COUNTIF($A$1:A1,A1)>1  # Highlight duplicates
=AND(A1<>"",$B1="")     # Highlight missing data

Raccourcis clavier

Raccourcis de navigation

# Basic navigation
Ctrl+Home          # Go to cell A1
Ctrl+End           # Go to last used cell
Ctrl+Arrow Keys    # Jump to edge of data
Ctrl+Page Up/Down  # Switch between worksheets
F5                 # Go to specific cell

# Selection shortcuts
Ctrl+Shift+End     # Select to last used cell
Ctrl+Shift+Arrow  # Select to edge of data
Ctrl+Space         # Select entire column
Shift+Space        # Select entire row
Ctrl+A             # Select all data

Édition des raccourcis

# Cell editing
F2                 # Edit active cell
Ctrl+D             # Fill down
Ctrl+R             # Fill right
Ctrl+Shift+Plus    # Insert cells/rows/columns
Ctrl+Minus         # Delete cells/rows/columns

# Formatting shortcuts
Ctrl+B             # Bold
Ctrl+I             # Italic
Ctrl+U             # Underline
Ctrl+Shift+$       # Currency format
Ctrl+Shift+%       # Percentage format
Ctrl+Shift+#       # Date format

Raccourcis de la formule

# Formula creation
=                  # Start formula
F4                 # Toggle absolute/relative references
Ctrl+Shift+Enter  # Array formula
Alt+=              # AutoSum
Ctrl+`             # Show/hide formulas
F9                 # Calculate formulas

Caractéristiques avancées

Formules de répartition

# Legacy array formulas (Ctrl+Shift+Enter)
\\{=SUM(A1:A10*B1:B10)\\}           # Multiply arrays and sum
\\{=MAX(IF(A1:A10="Text",B1:B10))\\} # Conditional max

# Dynamic arrays (Excel 365)
=FILTER(A1:C10,B1:B10>10)       # Filter data
=SORT(A1:C10,2,-1)              # Sort by column 2 descending
=UNIQUE(A1:A10)                 # Remove duplicates
=SEQUENCE(10,1,1,1)             # Generate sequence 1-10

Demande de puissance (Données > Obtenir des données)

# Common transformations
1. Remove duplicates
2. Filter rows
3. Add custom columns
4. Group by columns
5. Merge/append tables
6. Change data types
7. Split columns
8. Pivot/unpivot columns

Macros et VBA Basics

' Basic VBA syntax
Sub MyMacro()
    Range("A1").Value = "Hello World"
    Range("A1").Font.Bold = True
    Range("A1:A10").Select
End Sub

' Common VBA objects
Workbooks("filename.xlsx")     ' Reference workbook
Worksheets("Sheet1")           ' Reference worksheet
Range("A1:B10")               ' Reference range
Cells(1,1)                    ' Reference cell by row/column

Conseils et pratiques exemplaires

Formule Meilleures pratiques

# Use named ranges for clarity
=SUM(SalesData)    # Instead of =SUM(A1:A100)

# Use table references
=SUM(Table1[Sales]) # Reference table columns

# Avoid volatile functions in large sheets
=NOW()             # Recalculates constantly
=INDIRECT()        # Can slow performance
=OFFSET()          # Can slow performance

# Use structured references
=[@Sales]*[@Quantity] # Reference current row in table

Optimisation des performances

# Efficient formulas
=SUMPRODUCT()      # Often faster than array formulas
=INDEX/MATCH       # Faster than VLOOKUP
=XLOOKUP()         # Modern, efficient lookup

# Avoid entire column references when possible
=SUM(A:A)          # Slower
=SUM(A1:A1000)     # Faster with known range

Gestion des erreurs

# Common error functions
=IFERROR(A1/B1,"Division Error")    # Handle any error
=IFNA(VLOOKUP(A1,Table1,2,0),"Not Found") # Handle #N/A
=ISERROR(A1)       # Check if cell contains error
=ISBLANK(A1)       # Check if cell is empty
=ISNUMBER(A1)      # Check if cell contains number

Cas d'utilisation courante

Calculs financiers

# Loan calculations
=PMT(5%/12,60,10000)    # Monthly payment for loan
=PV(5%/12,60,200)       # Present value of annuity
=FV(5%/12,60,200)       # Future value of annuity
=RATE(60,200,10000)     # Interest rate calculation

# Investment calculations
=NPV(10%,B1:B5)         # Net present value
=IRR(B1:B5)             # Internal rate of return
=XIRR(B1:B5,A1:A5)      # IRR with specific dates

Exemples d'analyse de données

# Sales analysis
=SUMIFS(Sales,Region,"East",Month,"January") # Regional sales
=AVERAGEIFS(Sales,Product,"Widget",Year,2024) # Product average
=COUNTIFS(Status,"Complete",Priority,"High")   # Count criteria

# Ranking and percentiles
=RANK(A1,A:A,0)         # Rank value (descending)
=PERCENTRANK(A:A,A1)    # Percentile rank
=LARGE(A:A,3)           # 3rd largest value
=SMALL(A:A,3)           # 3rd smallest value

Cette feuille de tricherie Excel complète couvre les formules, fonctions et fonctionnalités essentielles pour un travail efficace de tableur. Maîtrisez ces concepts pour augmenter considérablement votre productivité Excel et vos capacités d'analyse de données.