Excel - Formeln, Funktionen und Produktivität
** Excel-Referenz für Formeln, Funktionen, Verknüpfungen und Produktivitätsmerkmale* *
Excel ist die weltweit beliebteste Tabellenkalkulation, die von Millionen für Datenanalyse, Finanzmodellierung und Business Intelligence verwendet wird. Dieses umfassende Betrugsblatt umfasst wesentliche Formeln, Funktionen, Verknüpfungen und erweiterte Funktionen, um Ihre Excel-Produktivität zu steigern.
Grundlegende Formeln und Funktionen
Wesentliche mathematische Funktionen
# 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
```_
### Textfunktionen
```text
# 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&B;1 # 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
```_
### Datums- und Zeitfunktionen
```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
```_
## Erweiterte Funktionen
### Logische Funktionen
```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 Funktionen
```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
```_
### Statistische Funktionen
```text
# 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
```_
## Datenanalyse Werkzeuge
### Pivot Tische
```text
# 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
```_
### Datenvalidierung
```text
# 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
```_
### Bedingte Formatierung
```text
# 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
### Navigation 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
### Verknüpfungen bearbeiten
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
### Formel Shortcuts
Formula creation
= # Start formula F4 # Toggle absolute/relative references Ctrl+Shift+Enter # Array formula Alt+= # AutoSum Ctrl+` # Show/hide formulas F9 # Calculate formulas ```_
Erweiterte Funktionen
Array Formeln
```text
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 (Daten > Daten erhalten)
```text
Common transformations
- Remove duplicates
- Filter rows
- Add custom columns
- Group by columns
- Merge/append tables
- Change data types
- Split columns
- Pivot/unpivot columns ```_
Macros und VBA Basics
```vb ' 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 ```_
Tipps und Best Practices
Formel Best Practices
```text
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 ```_
Leistungsoptimierung
```text
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 ```_
Fehlerbehebung
```text
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 ```_
Allgemeine Anwendungsfälle
Finanzrechnungen
```text
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 ```_
Beispiele für Datenanalysen
```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) # 3rd largest value =SMALL(A:A,3) # 3rd smallest value ```_
Dieses umfassende Excel-Cheatsheet umfasst die wesentlichen Formeln, Funktionen und Funktionen, die für effektive Tabellenkalkulation benötigt werden. Bearbeiten Sie diese Konzepte, um Ihre Excel Produktivität und Datenanalysefähigkeiten deutlich zu steigern.