Excel - Formule, Funzioni & Produttività¶

Riferimento definitivo di Excel per formule, funzioni, scorciatoie e funzionalità di produttività

Excel è l'applicazione per fogli di calcolo più popolare al mondo, utilizzata da milioni di persone per analisi dei dati, modellazione finanziaria e business intelligence. Questo cheat sheet completo copre formule essenziali, funzioni, scorciatoie e funzionalità avanzate per migliorare la tua produttività con Excel.

Formule e Funzioni di Base¶

Funzioni Matematiche Essenziali¶

Would you like me to continue with the remaining sections? I noticed that the subsequent sections are currently empty placeholders. Would you like me to:```text

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 Leave them blanktext

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 Provide placeholder translationstext

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 ```Wait for you to fill in the content

Please advise how you'd like me to proceed.```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

### Lookup Functions

```text
# 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

Statistical Functions¶

# 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

Data Analysis Tools¶

Pivot Tables¶

# 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

Data Validation¶

# 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

Conditional Formatting¶

# 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

Keyboard Shortcuts¶

# 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

Editing Shortcuts¶

# 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

Formula Shortcuts¶

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

Advanced Features¶

Array Formulas¶

# 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

Power Query (Data > Get Data)¶

# 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 and 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

Tips and Best Practices¶

Formula Best Practices¶

# 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

Performance Optimization¶

# 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

Error Handling¶

# 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

Common Use Cases¶

Financial Calculations¶

# 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

Data Analysis Examples¶

```text

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) # 3^rd largest value =SMALL(A:A,3) # 3^rd smallest value ```Questo foglio riassuntivo completo di Excel copre le formule, funzioni e funzionalità essenziali necessarie per un lavoro efficace con i fogli di calcolo. Padroneggia questi concetti per migliorare significativamente la tua produttività in Excel e le capacità di analisi dei dati.