Trucos y Fórmulas Esenciales para Optimizar tus Hojas de Cálculo

Optimización de Hojas de Cálculo: Funciones y Trucos Esenciales

Operaciones Básicas y Formato

Pegado Especial

• Selecciona la celda, copiar.
• Selecciona el rango de destino.
• Ve a Edición y elige Pegado especial.

Orientación de Texto en Celdas

• Selecciona las celdas.
• Ve a Formato de celdas, luego a la pestaña Alineación y ajusta la Orientación.

Rellenar Líneas y Bordes

• Para rellenar líneas, utiliza las opciones de Bordes Horizontales y Verticales.

Formato Condicional: Resaltar Valores Superiores al 10%

• Selecciona el rango de celdas.
• Ve a Formato, luego a Formato condicional.
• En Condición 1, elige "El valor de la celda es" y luego "mayor que". Introduce 0,10 (o 10%).
• En Formato, elige el color rojo.

Cálculos Financieros y Lógicos

Cálculo de Descuentos e Impuestos

Descuento del 10%

=CELDA * 0,1

Base Imponible

=TOTAL_BRUTO - DESCUENTO

Cálculo de IVA (16%)

=CELDA * 0,16

Recargo de Equivalencia (4%)

=CELDA * 0,04

Referencias de Celda: Relativas y Absolutas

Referencias Relativas

Se adaptan automáticamente al copiar la fórmula.

Ejemplo: =SUMA(A1:A5)

Referencias Absolutas

Permanecen fijas al copiar la fórmula, usando el símbolo $.

Ejemplo: =CELDA * $LETRA$NUMERO

Nombrar Celdas para Fórmulas

• Selecciona la celda (ej. la que contiene el valor del IVA).
• Escribe un nombre (ej. IVA) en el cuadro de nombres (situado a la izquierda de la barra de fórmulas) y pulsa Enter.
• Usa el nombre en tus fórmulas: =CELDA * IVA

Uso del Símbolo $ para Fijar Referencias

El símbolo $ indica que la parte de la referencia (fila o columna) se mantiene fija y no se adapta al copiar la fórmula.

Ejemplo: B$1 * $A2

Cálculo del Valor Futuro (VF) de una Inversión

Si tras 6 años el capital invertido es de 1500 € y el tipo de interés es del 5% anual:

Fórmula Directa

=1500 * (1 + 0,05)^6

Usando la Función VF

• Ve a Insertar función.
• Busca VF en la categoría Financieras.
• Parámetros:
  • Tasa: 5%
  • Mper: 6
  • VA: -1500 (el capital inicial se introduce en negativo)

Cálculo de Cuota Anual de un Crédito (PAGO)

Para amortizar un crédito de 5000 € en 10 años al 8% anual:

• Busca la función PAGO en la categoría Financieras.
• Parámetros:
  • Tasa: 8%
  • Mper: 10
  • VA: 5000

Determinación del Tipo de Interés (TASA)

Si te prestan 1000 € y los devuelves en 5 pagos mensuales de 210 €, para saber el tipo de interés:

• Busca la función TASA en la categoría Financieras.
• Parámetros:
  • Mper: 5
  • Pago: -210 (el pago se introduce en negativo)
  • VA: 1000

Función Lógica SI: Decisiones Basadas en Condiciones

Si una cifra de ventas no alcanza las 4000 unidades, el precio baja un 5%; para las que sí lleguen, sube un 10%.

• Busca la función SI en la categoría Lógicas.
• Parámetros:
  • Prueba_lógica: CeldaCantidad > 4000
  • Valor_si_verdadero: CeldaPrecio * 1,1 (si supera 4000, sube 10%)
  • Valor_si_falso: CeldaPrecio * 0,95 (si no supera 4000, baja 5%)

Funciones Matemáticas y Estadísticas

Cálculo de Promedio

Para calcular el promedio de los números 10, 23, 14, 65:

• Busca la función PROMEDIO en la categoría Estadísticas.
• Introduce los números directamente o selecciona el rango de celdas: =PROMEDIO(10;23;14;65)

Resto de una División (RESIDUO)

• Busca la función RESIDUO en la categoría Matemáticas y Trigonometría.
• Introduce el número y el divisor.

Cálculo del Seno de un Ángulo

Para calcular el seno de un ángulo de 120º:

1. Primero, convierte los grados a radianes:
   • Busca la función RADIANES en la categoría Matemáticas y Trigonometría.
   • Introduce 120 en el campo Ángulo y pulsa Aceptar.
2. Luego, calcula el seno:
   • Selecciona otra celda.
   • Busca la función SENO en la categoría Matemáticas y Trigonometría.
   • Pulsa Aceptar, luego el botón para elegir celda y selecciona la celda donde calculaste los radianes. Pulsa Aceptar.

Gestión y Organización de Datos

Búsqueda de Datos con BUSCARV

Para asignar un código a un vendedor o buscar información relacionada:

• Busca la función BUSCARV en la categoría Búsqueda y referencia.
• Parámetros:
  • Valor_buscado: La celda que contiene el nombre del vendedor.
  • Matriz_buscar_en: El rango de celdas que contiene todos los códigos y nombres (ej. A1:B10). Asegúrate de fijar este rango con F4 (ej. $A$1:$B$10).
  • Indicador_columnas: El número de columna de la matriz donde se encuentra el dato que quieres devolver (ej. 2, si los nombres de los vendedores están en la segunda columna).
  • Ordenado: FALSO (para una coincidencia exacta).
• Ejemplo de fórmula: =BUSCARV(CeldaNombre; Hoja2!$A$1:$B$10; 2; FALSO)

Listas Personalizadas

• Ve a Herramientas > Opciones > Listas Personalizadas.

Ordenar Datos

• Selecciona el rango de datos.
• Ve a Datos > Ordenar.

Uso de Autofiltro

• Activa una celda dentro de tu lista de datos.
• Ve a Datos > Filtro > Autofiltro.

Filtro Avanzado para Criterios Específicos

Para mostrar cantidades superiores a 3000 € usando un filtro avanzado:

1. Prepara el rango de criterios:
   • Abre una hoja nueva o un área vacía.
   • Escribe la cabecera de la columna que quieres filtrar (ej. Importe).
   • Debajo de esa cabecera, escribe el criterio (ej. >3000).
2. Aplica el filtro avanzado:
   • Activa una celda dentro de tu lista de datos original.
   • Ve a Datos > Filtro > Filtro avanzado.
   • En Rango de la lista, asegúrate de que tu tabla de datos esté seleccionada.
   • En Rango de criterios, pulsa el botón de selección de rango y selecciona el rango que creaste en el paso 1 (ej. las celdas que contienen "Importe" y ">3000").
   • Pulsa Aceptar.

Agrupación de Datos y Subtotales

Para agrupar elementos por provincia y calcular la suma del importe:

• Activa una celda dentro de tu tabla de datos.
• Ve a Datos > Subtotales.
• En "Para cada cambio en:", selecciona Provincia.
• En "Usar función:", selecciona Suma.
• En "Agregar subtotal a:", marca la casilla de Importe.
• Asegúrate de que "Reemplazar subtotales actuales" y "Resumen debajo de los datos" estén marcados según tu preferencia.
• Pulsa Aceptar.