Excel - Fórmulas, Funciones & Productividad

*Referencia exclusiva de Excel para fórmulas, funciones, atajos y características de productividad *

Excel es la aplicación de hoja de cálculo más popular del mundo, utilizada por millones para el análisis de datos, el modelado financiero y la inteligencia empresarial. Esta hoja de trampa completa cubre fórmulas, funciones, atajos y funciones avanzadas esenciales para aumentar su productividad de Excel.

Fórmulas y Funciones básicas

Funciones matemáticas esenciales

# 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

Funciones de texto

# 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

Funciones de fecha y hora

# 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

Funciones avanzadas

Funciones lógicas

# 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

Funciones de búsqueda

# 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

Funciones estadísticas

# 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

Análisis de datos Herramientas

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

Validación de datos

# 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

Formato condicional

# 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

Atajos de teclado

Atajos de navegación

# 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

Edición de atajos

# 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

Atajos de Fórmula

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

Características avanzadas

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

Consultas de alimentación (Data ± obtener datos)

# 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 y Básicos VBA

' 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

Consejos y mejores prácticas

Las mejores prácticas de Fórmula

# 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

Optimización del rendimiento

# 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

Manejo de errores

# 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

Casos de uso común

Cálculos financieros

# 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

Ejemplos de análisis de datos

# 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

Esta completa hoja de trampa Excel cubre las fórmulas, funciones y características esenciales necesarias para un trabajo eficaz de hoja de cálculo. Domine estos conceptos para aumentar significativamente su productividad de Excel y sus capacidades de análisis de datos.