Excel es la herramienta que esta en el 100% de las empresas, sin importar su tamaño
Aprenda Excel como los expertos y prepárate para ser mas competitivo
Por muchas aplicaciones que existan para planificar tareas, proyectos, citas o eventos, siempre terminamos recurriendo a los calendarios de siempre… ¿es tu caso? Otro año más te presento mis plantillas de calendario Excel 2020, para ayudarte a gestionar tu trabajo diario o tus reuniones y citas. En 2016 creé este pack de plantillas con calendarios […]
Cuando copiamos y pegamos datos como valores y esta acción es repetitiva, podemos trabajar más rápido con el teclado, sin usar el ratón. Recuerda que cuando una celda contiene una formula al pegarla con Crtl + V o con Pegar se pega ésta, pero a veces lo que queremos es pegar el resultado de esa fórmula, es decir los valores.
Selecciona los datos con Ctrl + Shift + Flecha Abajo, los copiamos con Ctrl + C
Para pegarlos pulsa la tecla del menú contextual de Windows y pulsamos la tecla V e Intro, de esta forma pega como valores.
Mostrar lista de Validación en Excel dependiendo del valor de una celda
El objetivo de este tutorial es mostrar de manera dinámica una Lista de validación en una celda, y se mostrará dependiendo del valor de otra celda. Usaremos Macros VBA y eventos de hoja para detectar el momento en que cambia el valor de la primera celda. Las condiciones son las siguientes:
Si ingresamos la palabra “Libre” en la celda B3, la celda D3 aceptará cualquier valor.
Si ingresamos la palabra “Lista” en la celda B3, la celda D3 tendrá una lista de validación.
En la celda B3 de nuestro ejemplo deberemos aplicar una Lista de validación que muestre las palabras Libre y Lista. Seguimos los siguientes pasos:
Elegimos la celda B3.
En la pestaña Datos elegimos Validación de datos.
En la sección Permitir elegimos Lista.
En la sección origen ingresamos “Libre,Lista”.
Aceptar.
Figura 1. Lista de validación en Excel para mostrar dos opciones.
Detectar cambios en la celdas usando Eventos para aplicar Lista de validación
Usaremos el evento Change o Cambiar para detectar el cambio de valores de la celda B3 y dependiendo lo elegido se aplicará una Lista de Validación en la celda D3 o se quitará si elegimos Libre.
La lista de validación que se aplicará si elegimos Lista en la celda B3 serán los valores que se encuentran en una tabla llamada TablaValores que se encuentra en la hoja Lista.
Figura 2. Valores para aplicar en la Lista de validación.
Haremos uso de código VBA para indicarle a Excel mediante macros que detecte los cambios en la celdas y apliquen o quiten la lista de validación. Usaremos el evento Change de la hoja Hoja1. Haremos lo siguiente:
Presionamos la combinación de teclas Alt + F11.
Damos doble clic en Hoja1 (Hoja1) que se encuentra a la izquierda.
Damos clic en el Combo que dice Worksheet.
Ahora elegimos Change en la lista de la derecha donde tendremos los eventos de la hoja.
Ingresamos la macro que te proporciono más adelante.
Probamos.
Figura 3. Uso el evento Change en VBA para detectar cambios en la hoja de cálculo.
Si en la celda B3 elegimos Lista, se mostrará una Lista de validación. Si elegimos Libre, se borrará la Lista de validación.
Figura 4. Mostrar Lista de validación en Excel dependiendo del valor de una celda.
Código VBA de la macro
'EXCELeINFO
'MVP Sergio Alejandro Campos
'http://www.exceleinfo.com
'https://www.youtube.com/user/sergioacamposh
'http://blogs.itpro.es/exceleinfo
Private Sub Worksheet_Change(ByVal Target As Range)
Dim HojaLista As Worksheet
Dim RangoLista As Range
Dim MiRango As Range
Set HojaLista = ThisWorkbook.Worksheets("Lista")
Set RangoLista = HojaLista.Range("TablaValores[ENCABEZADO]")
Set MiRango = Application.Worksheets("Hoja1").Range("D3")
If Range("B3").Value = "Libre" Then
MiRango.Validation.Delete
ElseIf Range("B3").Value = "Lista" Then
With MiRango.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="='" & HojaLista.Name & "'!" & RangoLista.Address
End With
End If
End Sub
MATRIZALEAT – Funciones de Matrices Dinámicas en Excel
En un tutorial anterior vimos los conceptos básicos de lo que se viene en Excel a raíz del cambio en el motor de cálculos en Excel. Vimos el tema de las Matrices dinámicas en Excel (Dynamic Array Functions) y los conceptos de Rango de desbordamiento (Spill range) y cómo hacer referencia a dicho rango.
Recordemos que Microsoft ha liberado 7 nuevas funciones de Matrices dinámicas, las cuales actualmente en versión previa a los usuarios registrados en el programa Office Insider:
ORDENAR. La función ORDER ordena el contenido de una matriz o rango.
ORDENARPOR. La función SORTBY ordena el contenido de un rango o una matriz basándose en los valores de una matriz o rango correspondientes.
FILTRAR. La función FILTER le permite filtrar un rango de datos basándose en los criterios que defina.
UNICOS. La función UNIQUE devuelve una lista de valores únicos en una lista o un rango.
SECUENCIA. La función SEQUENCE le permite generar una lista de números secuenciales en una matriz, como 1, 2, 3, 4.
MATRIZALEAT. La función RANDARRAY devuelve una matriz de números aleatorios entre 0 y 1.
SIMPLE. La función SINGLE, devuelve un valor único mediante lógica conocida como intersección implícita. SOLO puede devolver un valor, el rango de celda o un error.
En este tutorial veremos el uso de la función MATRIZALEAT.
La función MATRIZALEAT nos permitirá devolver una matriz de números aleatorios. Esta función trabaja de manera similar a la función ALEATORIO.ENTRE, pero con la ventaja que tiene más poder ya que podemos elegir cuántas y cuántas columnas deseamos mostrar en el rango de desbordamiento. Además de que también podremos indicar un número mínimo y uno máximo para mostrar los números aleatorios, y también podemos elegir entre mostrar números con decimales o enteros.
Sintaxis de la función MATRIZALEAT
La función MATRIZALEAT tiene 5 parámetros los cuales se reflejan de la siguiente manera:
Los parámetros que se encuentran entre corchetes son opcionales.
Filas. El número de filas que devolverá la matriz.
Columnas. El número de columnas que devolverá la matriz.
Min. El número mínimo que se desea que se devuelva.
Max. El número máximo que se desea que se devuelva.
Entero. Devuelve un entero o un valor decimal. Se usa VERDADERO para un entero y FALSO para un valor decimal.
Devolver números aleatorios
En un ejemplo sencillo deseamos devolver 10 números aleatorios, que serán mostrados en 5 filas y 2 columnas. El número mínimo a mostrar será el 1 y el número máximo a mostrar será el 16.
=MATRIZALEAT(E3,2,1,16,VERDADERO)
Y se devolverán los siguientes números aleatorios.
13
6
9
4
6
6
11
15
11
5
Mostrar 5 nombres de empresas de manera aleatoria
Tenemos un rango de dos columnas donde tenemos el Ranking de las mejores empresas de tecnología con su nombre y correspondiente puesto. Deseamos devolver 5 nombres aleatorio combinando las funciones BUSCARV y MATRIZALEAT.
La función BUSCARV nos servirá para encontrar el nombre de cada empresa y el resultado de la función MATRIZALEAT será el argumento valor_buscado de la función BUSCARV.
Figura 1. Devolver nombres de manera aleatoria usando BUSCARV y MATRIZALEAT.
Es importante anotar que este cambio en el Motor de cálculos en Excel y las nuevas funciones de Matrices dinámicas están en versión previa para un conjunto de usuarios anotados en el Programa Office Insider. El programa de Office Insider es gratis para los usuarios de Office 365que desean tener acceso a características previas de la aplicaciones de Office.
¿Cómo sumarías los valores de una columna en la que existen errores?
Si tu respuesta pasa por eliminar los errores para que la función SUMA pueda hacer su trabajo… mal vamos. Tardarías mucho tiempo en caso de tener muchos datos.
Si tratas de evitar estos errores con la función SI.ERROR… ya me va gustando más.
Pero si en tu solución utilizas la función AGREGAR… ¡enhorabuena!¡eres un Exceler!
El artículo de hoy está dedicado a una función tan potente como olvidada. Es la función AGREGAR. Una superfunción tan potente… ¡que vale por 19!
A simple vista, AGREGAR se parece mucho a SUBTOTALES. Ambas sirven para hacer diferentes cálculos con datos ignorando las filas ocultas. Sin embargo, AGREGAR ofrece mucha más potencia como te mostraré en los ejemplos.
AGREGAR tiene dos sintaxis diferentes, determinadas por la función que quieras utilizar en cada momento.
Sintaxis de referencias
=AGREGAR(núm_función;opciones; ref1;[ref2];…)
Núm_función: Es un número del 1 al 13 que determina la función que se va a utilizar. Puedes ver más abajo la tabla con las funciones correspondientes.
Opciones: Es un número que indica qué datos se deben incluir o excluir del cálculo. También puedes ver la correspondencia más abajo.
Ref1;[ref2]…: son las referencias a las celdas o rangos de celdas que contienen los valores a calcular.
Sintaxis matricial
=AGREGAR(núm_función;opciones; matriz;k)
Núm_función: Es un número del 14 al 19 que determina la función que se va a utilizar. Puedes ver más abajo la correspondencia.
Opciones: Es un número que indica qué datos se deben incluir o excluir del cálculo. También puedes ver la correspondencia más abajo.
Matriz: Es una matriz o rango de datos o una fórmula matricial que da como resultado una matriz y cuyos datos se van a utilizar en el cálculo.
k: Es un número que, dependiendo del núm_función elegida, determina el resultado a devolver.
Función
Significado de k
K.ESIMO.MAYOR
El enésimo valor más alto que contenga la matriz.
K.ESIMO.MENOR
El enésimo valor más bajo que contenga la matriz.
PERCENTIL.INC
El porcentaje, que debe ser un valor entre 0 y 1.
PERCENTIL.EXC
El porcentaje, que debe ser un valor entre 0 y 1.
CUARTIL.INC
El cuartil, que debe ser un valor entre 0 y 4.
CUARTIL.EXC
El cuartil, que debe ser un valor entre 0 y 4.
Correspondencia de funciones y opciones
Las funciones que se pueden utilizar con AGREGAR están numeradas del 1 al 19:
Núm_función
Función
Descripción
Sintaxis
1
PROMEDIO
Devuelve la media aritmética (promedio) de los argumentos.
Referencias
2
CONTAR
Cuenta el número de celdas de un rango que contienen números.
Referencias
3
CONTARA
Cuenta el número de celdas no vacías de un rango.
Referencias
4
MAX
Devuelve el valor máximo de una lista.
Referencias
5
MIN
Devuelve el valor mínimo de una lista.
Referencias
6
PRODUCTO
Devuelve el producto de todos los números introducidos como argumentos.
Referencias
7
DESVEST.M
Calcula la desviación estándar en función de una muestra.
Referencias
8
DESVEST.P
Calcula la desviación estándar de un total de población.
Referencias
9
SUMA
Suma todos los valores de los rangos introducidos como argumentos.
Referencias
10
VAR.S
Calcula la varianza en función de una muestra.
Referencias
11
VAR.P
Calcula la varianza en función de la población total.
Referencias
12
MEDIANA
Devuelve la mediana o el número central de un rango de datos.
Referencias
13
MODA.UNO
Devuelve el valor que más veces se repite en un rango.
Referencias
14
K.ESIMO.MAYOR
Devuelve el enésimo valor más alto de un rango.
Matricial
15
K.ESIMO.MENOR
Devuelve el enésimo valor más bajo de un rango.
Matricial
16
PERCENTIL.INC
Devuelve el enésimo percentil de un rango donde n está en el rango 0 y 1 incluidos.
Matricial
17
CUARTIL.INC
Devuelve el cuartil en función del percentil entre 0 y 1 incluidos.
Matricial
18
PERCENTIL.EXC
Devuelve el enésimo percentil de un rango donde n está en el rango 0 y 1 excluidos.
Matricial
19
CUARTIL.EXC
Devuelve el cuartil en función del percentil entre 0 y 1 excluidos.
Matricial
Las opciones o reglas de inclusión o exclusión de datos también vienen dadas por un número:
Opción
Descripción
0 o en blanco
Ignora las funciones SUBTOTALES y AGREGAR anidadas.
1
Ignora las filas ocultas y las funciones SUBTOTALES y AGREGAR anidadas.
2
Ignora los errores y las funciones SUBTOTALES y AGREGAR anidadas.
3
Ignora las filas ocultas, los errores y las funciones SUBTOTALES y AGREGAR anidadas.
4
No ignora nada.
5
Ignora las filas ocultas
6
Ignora los valores de error.
7
Ignora las filas ocultas y los errores.
No te preocupes por todos estos datos. A medida que se van introduciendo los argumentos, aparecen todas las opciones disponibles para cada uno.
Los números de función
Las opciones
Si te es más fácil también puedes utilizar el botón Insertar función, con lo que aparecerá el cuadro de diálogo de AGREGAR. En este caso no tendrás la misma ayuda visual para seleccionar la función ni la opción.
Ejemplos con la función AGREGAR
Venga, vamos a ver algunos ejemplos prácticos.
Observa los ejemplos de arriba. Te los voy detallando uno por uno:
Ejemplo 1: Varias referencias
La fórmula de la celda I4
=AGREGAR(1;7;C2:C17;D2:D17;E2:E17;F2:F17)
calcula el promedio (núm_función=1) de los rangos de celdas C2:C17;D2:D17;E2:E17;F2:F17 omitiendo las filas ocultas y los errores (opción=7).
En este ejemplo también es posible incluir un solo rango (C2:F17), pero este ejemplo muestra cómo también funciona con varios rangos.
Ejemplo 2: K.ESIMO.MAYOR
La fórmula de la celda I5
=AGREGAR(14;5;C2:C17;1)
devuelve el primer valor mayor (núm_función=14) del rango C2:C29, omitiendo las filas ocultas.
Ejemplo 3: K.ESIMO.MAYOR en fórmula matricial
Este ejemplo es algo más complicado que los anteriores. La fórmula de la celda I6
=AGREGAR(14;5;C2:C17*(B2:B17=”Herra. Manual”);2)
La fórmula devuelve el segundo valor más alto del rango C2:C29 y cuya categoría es “Tornillería”.
Sería posible conseguir el mismo resultado utilizando únicamente K.ESIMO.MAYOR, pero para introducirla habría que pulsar Ctrl + Mayús + Intro, ya que se trata de una fórmula matricial. Al usar AGREGAR, ya está implícito que se trata de una matricial.
Ejemplo 4: Sin omitir errores
El último ejemplo de la imagen de arriba corresponde a una suma en la que ni se ignoran las filas ocultas ni los errores:
=AGREGAR(9;5;D2:D17)
Al no omitir nada, la fórmula da un error ya que no se pueden sumar valores de error.
… pero no todo es tan bueno
Sí, parece que agregar es la navaja suiza de las funciones de Excel, pero no siempre es así. ¿Por qué?
Porque es más complicado utilizarla que sus equivalentes. Por ejemplo, es más fácil utilizar SUMA que AGREGAR.
Porque tienes que introducir más argumentos.
Porque tiene muchas opciones para recordar.
Porque muchas personas no conocen la función y, en caso de querer modificarla, resultaría más tedioso.
Sólo funciona en versiones a partir de 2007 (aunque no sé de nadie que utilice 2003 y antiguas).
Ventajas de utilizar AGREGAR
Si te he convencido para utilizar AGREGAR, seguro que después de leer la siguiente lista, la utilizarás sí o sí:
Con AGREGAR puedes hacer 512 cálculos diferentes (19 funciones X 8 opciones).
No tienes que preocuparte de los posibles errores que se encuentren en los rangos a utilizar.
Puedes hacer cálculos con las celdas visibles de listas con filtro.
No necesitas entender cómo funcionan las fórmulas matriciales.
Resumen
Como has visto, AGREGAR es junto con SUBTOTALES y SUMAPRODUCTO, una de las funciones más versátiles y útiles con las que cuenta Excel.
