Upload
others
View
5
Download
0
Embed Size (px)
Citation preview
Supongo que ya sabes que Excel cuenta con cientos de funciones
con las que puedes analizar, auditar y hacer un sinfín de cálculos
diferentes. Muchas de estas funciones de Excel las utilizamos a
diario en nuestros empleos y otras están diseñadas solo para
pequeños grupos (financieros, estadísticos, ingenieros) que realizan
tareas muy específicas.
En esta guía no trataré todas las funciones de Excel. Dejaré a un
lado las menos utilizadas. y me ceñiré a las más usadas y más útiles.
Para más información sobre todas las funciones de hoja que tiene
Excel te invito a que visites la lista completa en la web de Microsoft.
¿Todavía no sabes qué pueden hacer por ti las funciones de Excel?
En ese caso, lee el artículo hasta el final y podrás comenzar a hacer
tus propios cálculos:
Funciones de texto
Gracias a las funciones de texto puedes manipular celdas que
contienen cadenas de texto. Estas funciones son algunas de las más
importantes:
Función TEXTO
Se utiliza para convertir un número o una fecha en una cadena de
texto en un formato específico. Uno de los usos que yo le doy con
más frecuencia es para la creación de títulos donde han de
intervenir fechas dinámicas. Al concatenar una fecha con un texto,
la fecha siempre pierde el formato mostrándose como número de
serie. Con TEXTO se puede evitar esto.
=TEXTO(valor;formato)
• valor: Es el valor numérico que quieres convertir en texto. • formato: Es la máscara de entrada que corresponde al
formato que le quieres dar al texto. Estos son los códigos para introducir:
En la imagen puedes ver unos pocos ejemplos de formatos que se
pueden crear.
Función CONCATENAR
Permite combinar el contenido de dos o más celdas (hasta 255) en
una sola. También puedes utilizar el operador de concatenación “&“.
Yo personalmente utilizo CONCATENAR porque le da a la fórmula
un aspecto más “ordenado”.
=CONCATENAR(texto1,[texto2],[…])
• Texto1, texto2…: Son las cadenas de texto que se van a unir.
Sólo es obligatorio el primer argumento, admitiéndose hasta 255.
Función ESPACIOS
Al extraer información de bases de datos externas u otros
programas, a veces se suelen agregar espacios delante o detrás de
los datos. La función ESPACIOS permite eliminar estos espacios
innecesarios, dejando únicamente los propios entre palabras.
=ESPACIOS(texto)
• texto: Es el texto del que quieres eliminar el exceso de
espacios. Normalmente se suelen introducir referencias a celdas que contienen el texto, pero también puedes utilizar directamente cadenas de texto (entre comillas).
Función SUSTITUIR
Permite sustituir un texto por otro en una cadena de caracteres.
Ten cuidado al utilizarla, ya que diferencia entre mayúsculas y
minúsculas.
En caso de que en vez de sustituir un texto por otro, quieras hacer
una sustitución según la posición del texto, debes
utilizar REEMPLAZAR.
La verdad es que apenas he utilizado esta función. Cuando tengo
que hacer alguna sustitución de texto, utilizo la herramienta Buscar
y Reemplazar (Ctrl + L).
=SUSTITUIR(texto;texto_original;texto_nuevo;num_repeticion)
• Texto: Es el texto o la referencia a la celda donde se encuentra el texto a sustituir.
• Texto_original: Es la cadena de texto que se reemplazará. • Texto_nuevo: Es el texto por el que se va a reemplazar el
texto_original. • Num_repeticion: Es el número de la repetición que se
sustituirá. Si escribes un 1, se reemplazará solamente el texto la primera vez que se encuentre. Si escribes un 2, se reemplazará la segunda, etc. En caso de dejar el argumento en blanco, se sustituirán todas las repeticiones que se encuentren en el texto.
Función VALOR
Ya has visto que TEXTO convierte números en texto. Pues VALOR
hace lo opuesto: intenta convertir texto en números. Digo “intenta”
porque no siempre se reconocen los formatos de texto.
=VALOR(texto)
• Texto: Es el texto que quieres convertir en número.
Te pongo el mismo ejemplo que con TEXTO. Cuando extraes datos
externos de otros programas, a veces los valores numéricos tienen
formato de texto, de forma que no se pueden hacer operaciones
matemáticas con ellos. La función VALOR soluciona este problema.
Función IGUAL
Permite hacer comparaciones simples de dos valores. Aunque yo
prefiero utilizar directamente el signo igual (=3=2, que devuelve
FALSO), IGUAL parece que le da un aspecto más “ordenado a la
fórmula donde la utilices.
Dos de las situaciones en las que te será realmente útil es al utilizar
formatos condicionales y validación de datos. Si no coinciden dos
valores (si no son iguales), la condición no se cumplirá o no
permitirá introducir un dato.
=IGUAL(texto1;texto2)
• Texto1: Es el primer valor a comparar. • Texto2: Es el segundo valor a comparar. Tanto éste como
texto1 pueden aceptar texto, valores lógicos y datos numéricos.
Ten cuidado: IGUAL distingue entre mayúsculas y minúsculas.
Funciones para cambiar el formato del texto
Excel contiene tres funciones que son muy útiles para cambiar la
apariencia del texto de las celdas:
• MAYUSC (texto): Transforma todos los caracteres del texto en letras mayúsculas.
• MINUSC(texto): Transforma todos los caracteres el texto en letras minúsculas.
• NOMPROPIO(texto): Convierte en mayúscula el primer carácter de cada palabra, y en minúscula el resto.
En las tres funciones, el argumento texto puede ser una cadena de
caracteres entre comillas, una referencia a una celda que contenga
texto o una fórmula que devuelva un texto.
Funciones para extraer caracteres del texto
Si necesitas fórmulas para extraer de un texto, un número
determinado de caracteres, aquí tienes las tres funciones más
utilizadas:
• IZQUIERDA(texto;[núm_caracteres]): Se utiliza para devolver un número concreto de caracteres desde el comienzo de la cadena de texto.
• DERECHA(texto;[núm_caracteres]): Devuelve un número concreto de caracteres desde el final de la cadena de texto hacia atrás.
• EXTRAE(texto;núm_inicial;núm_caracteres): Devuelve un número concreto de caracteres desde la posición indicada en núm_inicial.
En estas funciones el argumento texto indica el texto sobre el que
se va a trabajar y núm_caracteres es el número de caracteres a
devolver desde la izquierda, derecha o núm_inicial.
Funciones lógicas
Para establecer condiciones lógicas puedes utilizar estas funciones,
que proporcionan toda la flexibilidad que necesitas.
Funciones Y, O y XO
• Y(valor_lógico1;[valor_lógico2];…): Devuelve VERDADERO si todos los argumentos de la función se cumplen.
• O(valor_lógico1;[valor_lógico2];…): Devuelve VERDADERO si uno o varios argumentos de la función se cumplen.
• XO(valor_lógico1;[valor_lógico2];…): Devuelve un Exclusivo lógico de todos los argumentos.
Función NO
La función NO se utiliza para negar el resultado lógico de una
fórmula, es decir, si la fórmula de la celda devuelve VERDADERO, la
función NO hará que sea FALSO y viceversa.
Por ejemplo, estas dos fórmulas devuelven FALSO:
=NO(VERDADERO)
=NO(2*2=4)
Función SI
Es la función lógica más conocida y versátil. A veces se la conoce
como “función condicional” por su capacidad de devolver un valor
según una condición que se especifique:
=SI(condicion;valor_si_verdadero;[valor_si_falso])
La función SI comprueba los criterios expresados en el
argumento condicion, y devuelve un valor (valor_si_verdadero) si
se cumplen y otro valor (valor_si_falso) si no se cumplen.
Por ejemplo, la fórmula =SI(A1<>””;”Bueno”;”Malo”), devuelve el
valor “Bueno” si la celda A1 no está vacía. Si A1 se encuentra vacía,
devuelve “Malo”.
Un ejemplo algo más complejo: La
fórmula =SI(F4>9;”¡Perfecto”;SI(F4>6;”Bien”;SI(F4>3;”Mal”;”Muy
mal”))) devolverá “¡Perfecto!” si F4 contiene un número mayor que
9, “Bien” si el valor se encuentra entre 6 y 9, “Mal”, si se encuentra
entre 3 y 6 y “Muy mal”, si es menor de 3.
Función SI.ERROR
SI.ERROR es una función muy útil que se utiliza para manejar los
posibles errores que se pueden dar en una fórmula.
=SI.ERROR(formula;valor_si_error)
Comprueba si la fórmula da como resultado un error y, si lo hace,
devuelve un valor en su lugar. Es capaz de identificar los siete
errores que se pueden dar en la fórmula.
Funciones matemáticas
Excel también cuenta con muchísimas funciones básicas y
avanzadas para realizar cálculos matemáticos, desde simples
sumas y restas hasta operaciones logarítmicas, factoriales y
similares.
Algunas de las operaciones más básicas pueden ser:
Funciones para sumar datos
Sumar es la operación matemática que más se utiliza. Excel cuenta
con varias funciones que te ayudarán en esto:
Función SUMA
Esta función devuelve la suma de los argumentos introducidos.
Estos argumentos pueden ser números, referencias de celdas o
resultados de fórmulas que devuelven números
=SUMA(número1;[número2];…)
En esta sintaxis sólo es obligatorio el primero de los argumentos.
Los demás, hasta 255, puedes introducirlos o no.
En la fórmula =SUMA(A4;B6;3), se suman los valores de las celdas
A4 y B6 y, a continuación, se suma 3.
Esta es la forma de suma más sencilla, pero hay otras más completas
y complejas.
Funciones SUMAR.SI y SUMAR.SI.CONJUNTO
Ambas funciones suman las celdas de un rango específico, siempre
que los valores cumplan ciertas condiciones.
La diferencia entre las funciones es que SUMAR.SI sólo puede
evaluar una condición mientras que SUMAR.SI.CONJUNTO, puede
hacerlo hasta con 127. Será complicado que llegues a exceder este
número..
=SUMAR.SI(rango,criterio,[rango_suma])
=SUMAR.SI.CONJUNTO(rango_suma;rango_criterios_1;criterio_1
;[rango_criterios_2];[criterio_2];…)
Como ves, las dos funciones tienen los mismos tipos de argumentos
pero su sintaxis es ligeramente diferente:
• rango_suma: es el rango de celdas donde se encuentran los valores a sumar.
• rango y rango_criterios: es el rango de celdas que contiene el criterio a evaluar.
• criterio y criterio_1: es la condición que debe ser evaluada.
Función SUMAPRODUCTO
SUMAPRODUCTO es una función un tanto especial porque es de
las pocas que trabaja con matrices. Su misión es la de multiplicar los
componentes de dos rangos de datos y sumar su resultado (dos
acciones en una).
Uno de los usos que le suelo dar es el de multiplicar cantidades por
precios en un listado de productos, para hallar el importe total.
• Reto 04: Sumar las horas de un calendario de turnos
Funciones para generar números aleatorios
Excel tiene dos funciones para generar números aleatorios. A
simple vista parece que no tienen mucha utilidad, pero sirven para
muchas actividades: asignar personas a grupos de forma aleatoria,
ordenar datos aleatoriamente o crear estudios de probabilidades.
• ALEATORIO(): Genera un número aleatorio entre 0 y 1. • ALEATORIO.ENTRE(valor_menor;valor_mayor): Genera un
número aleatorio entre los límites dados.
Mira estos ejemplos
• Cómo generar valores aleatorios con decimales entre dos números
• Números aleatorios estáticos • Función ALEATORIO excluyendo rangos
Funciones de redondeo
¿Tienes que hacer algún tipo de redondeo a un número? Sin
problemas, Excel cuenta con un sinfín de funciones que permiten
crear cualquier redondeo imaginable:
• REDONDEAR: Redondea un número al número de decimales especificado.
• REDONDEAR.MAS: Redondea un número hacia arriba, en dirección contraria a cero.
• REDONDEAR.MENOS: Redondea el número hacia abajo, en dirección hacia cero.
• REDOND.MULT: Devuelve un número redondeado al múltiplo deseado.
• MULTIPLO.INFERIOR: Redondea un número hacia abajo, hasta el múltiplo significativo.
• MULTIPLO.SUPERIOR: Redondea un número hacia arriba, hasta el múltiplo significativo.
• ENTERO: Redondea un número hacia abajo hasta el entero más próximo.
• TRUNCAR: Trunca un número a un entero. • REDONDEA.PAR: Redondea un número hasta el entero par
más próximo, en sentido contrario a cero. • REDONDEA.IMPAR: Redondea un número hasta el entero
impar más próximo, en sentido contrario a cero.
Te muestro un ejemplo:
• 7 ejemplos con funciones REDONDEAR
Funciones estadísticas
La categoría estadística cuenta con muchas funciones para realizar
todo tipo de cálculos. Seguro que en alguna ocasión has tenido que
contar un rango de celdas o hallar el valor más alto de un grupo de
números… Con Excel es posible realizar estudios estadísticos
complejos. A una escala más sencilla, esto es lo que podrías llegar a
necesitar:
Funciones para encontrar valores más altos, más bajos y promedios
• =MIN(numero_1;numero_2;…): Devuelve el valor mínimo de una lista de valores omitiendo los valores lógicos y los de texto.
• =MAX(número1,[número2],[…]): Devuelve el valor más alto de una lista de valores omitiendo los valores lógicos y los de texto.
• =PROMEDIO(número1;[número2];[…]): Devuelve la media aritmética de todos sus argumentos (hasta 255).
• =K.ESIMO.MAYOR(matriz;k): Devuelve el número que se encuentra en la n-ava posición cuando los valores se encuentran ordenados de mayor a menor.
• =K.ESIMO.MENOR(matriz;k): Devuelve el número que se encuentra en la n-ava posición cuando los valores se encuentran ordenados de menor a mayor.
Funciones para contar celdas
Excel nos ayuda a contar celdas con estas funciones:
• =CONTAR(valor1;[valor2];[…]): Devuelve la cantidad de celdas que contienen números o fechas.
• =CONTARA(valor_1;valor_2;…): Cuenta el número de celdas no vacías de uno o varios rangos de datos.
• =CONTAR.BLANCO(rango): Cuenta las celdas que están en blanco.
• =CONTAR.SI(rango;criterios): Cuenta el número de celdas que cumplen con los criterios de una condición.
• =CONTAR.SI.CONJUNTO(rango_criterio1;criterio1;[rango_criterio2;criterio2];[…]): Cuenta el número de celdas que contienen valores según uno o varios criterios.
¿Quieres algún ejemplo más?
• Cómo contar celdas que NO contienen números • Contar el número de facturas emitidas en un año
determinado
Funciones de búsqueda y referencia
Una de las tareas que se hacen con más frecuencia es la de buscar
un dato. Las funciones de búsqueda y referencia son realmente
útiles cuando necesitas buscar información o quieres mostrar
referencias de celdas
Función BUSCARX
Desde su publicación se ha convertido sin duda en la nueva reina de
las funciones de búsqueda, destronando a BUSCARV. Es mil veces
mejor. Úsala cuando:
• No te quieras complicar la vida haciendo búsquedas de datos hacia la izquierda.
• Preveas que se van a mover o eliminar columnas en el futuro (BUSCARX mantiene las referencias).
• Prefieres hacer búsquedas de datos de abajo a arriba. • Buscas varios valores.
Si te parecen pocas ventajas con respecto a BUSCARV te diré que
incluso ¡es más fácil de usar! Mira su sintaxis:
=BUSCARX(valor_buscado;matriz_buscada;matriz_devuelta;[si_n
o_se_encuentra];[modo_de_coincidencia];[modo_de_búsqueda])
Ejemplos con BUSCARX
• Por qué la función BUSCARX está revolucionando las búsquedas
Función BUSCARV
¿Quién no ha oído hablar de BUSCARV o la utiliza todos los días? Es
la protagonista de las funciones de búsqueda y la más utilizada por
los que trabajamos con Excel. BUSCARV te permite buscar un valor
en la primera columna de un rango y devuelve el dato de la misma
fila que se encuentra n columnas hacia la derecha.
=BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas
;[ordenado])
• valor_buscado: (obligatorio) Es el valor que quieres buscar. • matriz_buscar_en: (obligatorio) El rango de datos o tabla
donde se buscará el valor_buscado.
• indicador_columnas: (obligatorio) El número de columna en la tabla en la que se encuentra el valor devuelto.
• ordenado: (opcional) El valor lógico que indica si BUSCARV debe buscar una coincidencia exacta o aproximada.
Ejemplos con BUSCARV
• 6 trucos para optimizar BUSCARV (todavía más) • ¿BUSCARV hacia la izquierda? • Cómo utilizar comodines en la función BUSCARV • Dos sustitutas para BUSCARV
Función INDICE
INDICE es una de las pocas funciones de Excel que acepta dos
sintaxis: una de referencia y otra matricial. Ambas sintaxis tienen el
mismo objetivo: encontrar un valor en una matriz o rango de celdas,
conociendo solo un número de columna y de fila.
Esta función, junto con COINCIDIR, son las sustitutas perfectas
para BUSCARV, que además tienen una ventaja: la posibilidad de
hacer búsquedas hacia la izquierda.
Función COINCIDIR
La función COINCIDIR devuelve la posición relativa de un elemento
en una matriz que coincida con un valor específico en un
determinado orden. Esto significa que, dentro de un rango de
celdas, es posible conocer la posición exacta del valor buscado. Esto
es realmente útil, por ejemplo, para construir fórmulas complejas
relacionadas con datos que pueden variar de posición (dinámicos).
Échale un vistazo a estos ejemplos de uso:
• Reto 01: ¿Cuál es el producto más vendido? • Cómo debes utilizar los nombres de rango para simplificar
tus megafórmulas
Función INDIRECTO
¿El dato que estás buscando puede encontrarse en un rango de
celdas o en otro? INDIRECTO te ayuda a construir referencias de
celdas dinámicas para que hagas la búsqueda en el sitio correcto.
Particularmente yo la suelo utilizar para crear referencias a otras
hojas del libro de Excel.
Función DESREF
DESREF es de mis funciones de referencia favoritas. Desde una
referencia de celda dada, permite desplazarte un número de filas y
un número de columnas, para devolver el valor que se encuentre en
esa celda.
Normalmente suele utilizarse en combinación contras funciones
como INDICE o INDIRECTO.
Función TRANSPONER
TRANSPONER te permite cambiar las filas por columnas y las
columnas por filas, modificando así la posición de los datos. Se trata
de una función matricial.
La sintaxis es muy sencilla, simplemente hay que aplicar la matriz
que se desea transponer. Pero previamente a introducir la fórmula,
debes seleccionar el rango de celdas de destino (ya transpuesto)
donde quieres pegar los datos.
En este artículo te hablo más a fondo de esta función:
• Las fórmulas matriciales en Excel
La verdadera potencia de las funciones de búsqueda y referencia se
encuentra en combinarlas adecuadamente.
Funciones de fecha y hora
Las operaciones con fechas y horas suelen ser también bastante
habituales (tareas como sumar o restar días o tiempos, cálculos con
días laborables o hallar fechas en función de determinados
parámetros).
Algo importante que debes saber es que el formato de las celdas
que contienen fechas y horas juega un papel muy importante. A
veces un simple cambio de formato te da la respuesta que estabas
buscando. Esto es porque Excel almacena las fechas y las horas
como números de serie. Por ejemplo, el 02/06/2018 a las 15:37,
Excel lo guarda como 43253,65069 (43253 son los días
transcurridos desde el 01/01/1900 hasta el 02/006/18 y 0,65069
es la fracción del día transcurrido desde las 0:00 hasta las 15:37).
Excel trabaja de esta manera para poder realizar cálculos con este
tipo de datos de forma más eficiente.
Funciones para crear fechas
• FECHA: Para crear fechas recopilando los diferentes componentes (día, mes y año).
• FECHA.MES: Devuelve una fecha según un número de meses antes o después.
• FECHANUMERO: Convierte una cadena de texto en una fecha.
Mira estos artículos:
• Cálculos con fechas y horas en Excel • En profundidad: Guía de uso de fechas horas en Excel
Fecha y hora actuales
En ocasiones puede que necesites conocer la hora y la fecha
actuales para hacer algún cálculo de vencimientos o de control
horario.
• HOY(): Devuelve la fecha actual • AHORA(): Devuelve la fecha y hora actuales. Modificando el
formato puedes mostrar únicamente la hora.
Otra forma de conocer la fecha actual:
• Atajo de teclado: Introducir la fecha actual
Extracción de fechas y componentes de fecha
Dada una fecha, es posible obtener mucha información de forma
individual:
• DIA: Devuelve el número del día de la fecha especificada. • MES: Devuelve el número del mes. • AÑO: Devuelve el año.
• FIN.MES: Devuelve el último día del mes, un número de meses antes o después de la fecha.
• DIASEM: Devuelve el día de la semana, según el parámetro especificado.
• NUM.DE.SEMANA: Devuelve el número de la semana del año en la que se encuentra la fecha.
Mira estos artículos:
• Cómo extraer datos de celdas que contienen fecha y hora + UDF
• Convertir los Timestamps de UNIX a fechas en un instante
Cálculo de diferencias entre fechas
• SIFECHA: Devuelve la diferencia entre dos fechas. • FRAC.AÑO: Calcula la fracción del año que hay entre dos
fechas
Mira este artículo:
• Diferencia en meses entre dos fechas
Operaciones con días laborables
Para operaciones relacionadas con días laborables tienes a tu
disposición estas funciones:
• DIA.LAB: Calcula la fecha resultante de agregar o quitar un número de días laborables a una fecha inicial.
• DIAS.LAB: Calcula el número de días laborables entre dos fechas.
• DIA.LAB.INTL: Calcula la fecha resultante de agregar o quitar un número de días laborables a una fecha inicial, utilizando parámetros de fin de semana personalizados.
• DIAS.LAB.INTL: Calcula el número de días laborables entre dos fechas, utilizando parámetros de fin de semana personalizados
Echa un vistazo a este artículo:
• Cómo calcular las horas de trabajo entre dos fechas con jornadas diferentes
Funciones definidas por el usuario
Existe un último tipo de función que es el creado por el propio
usuario. Para crear una función personalizada debes tener un
mínimo conocimiento de VBA que te permita llegar a tu objetivo de
cálculo.
Si Excel no cuenta con la función que necesitas (lo dudo…) o no eres
capaz de combinar las funciones que ofrece para llegar al resultado
que necesitas (también lo dudo…), puedes crear tu propia función y
darle tu propio nombre.
Este es un ejemplo de una función personalizada que sirve para
mostrar el código del color del fondo de la celda:
Function ColorProper(celda As Range) As String
Dim C As Long
Dim R As Long
Dim G As Long
Dim B As Long
C = celda.Interior.Color
R = C Mod 256
G = C \ 256 Mod 256
B = C \ 65536 Mod 256
ColorProper = "R=" & R & ", G=" & G & ", B=" & B
End Function
Para utilizar la función sólo tienes que introducir la fórmula
=ColorProper(A1) para obtener el color de fondo de la celda A1.
Resumen
¿Cuáles de estas tareas realizas en tu trabajo? ¿Qué funciones usas?
Supongo que más de una, ya que te he mostrado las más comunes
en cualquier trabajo en que se utilice Excel.
Como has visto, el único límite que hay para hacer cualquier tipo de
cálculo es la imaginación de tu jefe… 🙂