Sabemos que desde hace pocos años Microsoft está poniendo empeño y un gran esfuerzo en implementar mejoras constantes en el paquete Office, y en Excel en particular... haciéndose difícil estar constantemente actualizado.
Parte de este esfuerzo por Microsoft fue sacar al mercado la versión de Office 365, en competencia con las versiones perpetuas (o permanentes).
Una breve nota a este respecto para los que se pierdan en este punto.
Una versión permanente/perpetua supone un único pago por el paquete, teniendo acceso a actualizaciones de seguridad, pero no mejoras de la aplicación en general (nuevas funciones, herramientas, etc...).
Estas pueden ser las versiones de Excel 2019, Excel 2016 o cualquier otra anterior.
De otro lado tenemos la versión 365 que supone un pago mensual/anual y se tiene acceso continuo a todo tipo de actualizaciones y mejoras.
Para estas actualizaciones se emplean dos canales:
1- canal mensual
2- canal semianual
configurable e intercambiable por el usuario desde su perfil de cuenta.
Para saber cuál es tu canal de actualización basta ir a la ficha Archivo > Cuenta y buscar la info de 'Acerca de Excel'
En mi caso tengo el canal mensual...
Otro aspecto relevante es el número de versión y de compilación que indica que mejoras, actualizaciones y 'problemas' podríamos tener instaladas en nuestro equipo.
Existe una página de Microsoft donde se informa de todas estas actualizaciones... es esta https://docs.microsoft.com/es-es/officeupdates/update-history-office365-proplus-by-date En este link podemos encontrar las actualizaciones más recientes de todas las versiones y canales.... muy práctico, sin duda, para saber si estamos a la última.
Como curiosidad, seguro que lo has notado, en la última actualización (me refiero a la de 29 de abril de 2019 - versión 1904 compilación 11601.20144) se cambió el aspecto de los iconos de las aplicaciones de Office (Excel, Word, Outlook, etc).
A día de hoy solo nos queda, como de especial relevancia, la implementación de las funciones de matriz dinámicas (dynamic arrays)... al menos para la versión 365.
Si eres un fanático de las actualizaciones y quieres disponer en todo momento de lo último, antes qeu nadie... quizá te interese formar parte del programa Office Insider (lee aquí).
OJO, el 'precio' de estar al día son posibles fallos y errores no depurados!!, es decir, eres el 'conejillo de indias' de las actualizaciones y mejoras, previa a la salida comercial para el resto de versiones y canales estándar.
Espero veas más claro el mundo de las actualizaciones :D
Todos esperamos como 'agua de mayo' las funciones de matrices dinámicas (dynamic arrays formulas)... y esperemos lleguen pronto ;-)
Pero a pesar de su potencial no podremos olvidar nunca las potentes fórmulas matriciales (fórmulas CSE).
Hoy aplicaremos, basándonos en un post anterior del blog, un uso matricial que nos permitirá contar registros en una base de datos.
Supongamos tenemos la siguiente base de datos con info de movimientos por país... dándose el caso que en una misma celda nos podemos encontrar varios registros!!
Por suerte la estructura de los registros es siempre igual: PAÍS (#)
La fórmula matricial buscada en B2 sería =SUMA(LARGO(TblReg[ventas])-LARGO(SUSTITUIR(TblReg[ventas];"(";"")))
ejecutada matricialmente, i.e., presionado Control + Mayúscula + Enter.
Lo que conseguimos con la parte interna de la fórmula:
LARGO(TblReg[ventas])-LARGO(SUSTITUIR(TblReg[ventas];"(";""))
es obtener para cada celda el número de veces que aparece el '(', esto es, un vector del tipo: {0;1;2;0;1}
Vector de valores que acabamos sumando consiguiendo el dato buscado.
Conoceremos en esta ocasión un par de funciones con algún tiempo a sus espaldas poco conocidas, sobre todo por que de momento solo están disponibles para Excel 2019 o la versión 365: CAMBIAR y SI.CONJUNTO
Son funciones que hasta su aparición se sustituían con la función SI anidando condiciones, o bien con la función ELEGIR.
A modo de ejemplo usaremos estas funciones para componer una validación de datos anidada, doned según el país elegido en la primera celda validada, ofrezca las ciudades del país correspondiente en la segunda celda.
Tenemos los datos dispuestos en el rango F3:I6 con nombres definidos asignados: España =Hoja1!$F$4:$F$6
Francia =Hoja1!$H$4:$H$6
Italia =Hoja1!$G$4:$G$6
paises =Hoja1!$F$3:$I$3
Portugal =Hoja1!$I$4:$I$6
Por otro lado en la celda B3 hemos incorporado una Validación de datos tipo lista con origen el nombre definido= paises.
Así pues en la celda C3 he incorporado otra validación de datos tipo lista con origen la fórmula: =CAMBIAR($B$3;"ES";España;"IT";Italia;"FR";Francia;"PT";Portugal)
De igual forma en C4 =SI.CONJUNTO($B$3="ES";España;$B$3="IT";Italia;$B$3="FR";Francia;$B$3="PT";Portugal)
En C5 =SI($B$3="ES";España;SI($B$3="IT";Italia;SI($B$3="FR";Francia;SI($B$3="PT";Portugal))))
o en C6 =ELEGIR(COINCIDIR($B$3;paises;0);España;Italia;Francia;Portugal)
Que serían cuatro posibilidades para este ejemplo...
Notemos que los valores desplegados en B3 no coinciden con los nombres asignados a cada conjunto de ciudades
ES para España
FR para Francia
IT para Italia
o PT para Portugal
lo que hace inútil el método de uso de la función INDIRECTO.
La función SI.CONJUNTO permite gestionar hasta 127 condiciones =SI.CONJUNTO(Prueba_lógica1;ValorVerdadero1;Prueba_lógica2;ValorVerdadero2;Prueba_lógica3;ValorVerdadero3; ...)
es decir, controla 127 posibles 'salidas' o respuestas en caso que se cumplan las pruebas lógicas añadidas.
Por otra parte la función CAMBIAR gestiona hasta 126 'intercambios' de valor =CAMBIAR(dato;valor1;resultado1;valor2;resultado2;....)
por tanto, intercambia el dato existente en el primer argumento según los 'valores' indicados en argumentos siguientes, cambiando el dato por el resultado marcado.
Funciones potentes que nos habilitan y abren mucho más los posibles tratamientos de condiciones.
Hace un os días un conocido me enseño la documentación de un Master de reconocido prestigio que está realizando, y en concreto una práctica donde se enseñaba cómo obtener el PayBack de una inversión.
Me llamó mucho la atención el sistema empleado y me permito reproducirlo aunque ya escribí al respecto en este post sobre el PayBack.
El sistema empleado en este master (usando la función INTERSECCION.EJE) es muy simple pero a la vez muy limitado, dependiendo de la linealidad de la inversión.
Veamos dos ejemplos aplicados.
Primera inversión:
Como se observa en el modelo de la imagen (es el mismo que el descrito en este ejemplo), el gráfico muestra una linealidad muy clara en el flujo acumulado de tesorería entre los periodos de la inversión..
por lo que al aplicar en C20 la función: =INTERSECCION.EJE(B3:G3;B9:G9)
es decir, =INTERSECCION.EJE(periodos; flujos acumulados)
obtenemos un valor muy próximo (y válido) respecto al método de cálculo de ejemplo 'linkado'.
Al fin y al cabo, como indica la documentación, esta función devuelve el punto de intersección, que se basa en el mejor ajuste de la línea de regresión trazado con los valores x y los valores y conocidos.
Por lo que emplearemos la función INTERSECCION.EJE para determinar el valor de la variable dependiente cuando la variable independiente es igual a 0 (cero), es decir cuando los flujos acumulados se hagan cero...
En el gráfico se ve muy claro el momento en el que los flujos cortan el eje.
Segunda inversión:
Sin embargo, en el siguiente modelo de inversión, no se dan las condiciones de linealidad, y comprobaremos cómo el uso de esta función INTERSECCION.EJE distorsiona bastante la realidad.
Se observa en este segundo modelo que el gráfico NO muestra una linealidad muy clara en el flujo acumulado de tesorería entre los periodos de la inversión... representa más bien una curva
por lo que al aplicar en C21 la función: =INTERSECCION.EJE(B3:G3;B9:G9)
es decir, =INTERSECCION.EJE(periodos; flujos acumulados)
obtenemos un valor muy distinto respecto al método de cálculo de la celda C19.
En definitiva, ojo con qué método de cálculo aplicamos... ya que si optamos por el uso sencillo de INTERSECCION.EJE deberemos asegurarnos de esa linealidad.
Veremos hoy una función interesante, con algunos años a sus espaldas pero poco conocida (yo mismo la descubrí hace pocos meses) - la función TIPO =TIPO(valor)
sabiendo que la función devolverá alguno de los siguientes resultados
Número/Fecha:= 1
Texto:= 2
Un valor lógico:= 4
Un valor de error:= 16
Matriz:= 64
Nota: curiosamente no es capaz de discriminar un número de una fecha, es decir, toma el valor real de la celda (recuerda que en realidad las fechas son números!!)
Veamos algún ejemplo sencillo.
Se observa en los ejemplos cómo la función identifica perfectamente los tipos de datos escritos en cada celda, en base a la equivalencia anterior...
Esta función TIPO complementa perfectamente, y en algunos casos mejora, las funciones ES (ESTEXTO, ESNUMERO, ESNOTEXTO, ESERROR, ESFORMULA, etc)... y es que la función TIPO permitirá con una simple prueba lógica evaluar el tipo de dato a analizar.
Notemos cómo la ambigüedad en algunos casos dados por las funciones ES desaparece con TIPO
Notemos el caso del valor lógico VERDADERO (o FALSO) en la celda B7, donde TIPO devuelve el valor 4 (i.e., valor lógico), mientras que la función ESNOTEXTO la identifica, efectivamente como 'no texto'...
Precisamente por esta claridad a la hora de identificar qué tipo de dato es, parece más correcto construir nuestras pruebas lógicas con la función TIPO; por ejemplo,
=SI(TIPO(A1)=4;"-";BUSCARV(A1;C1:D10;2;0))
identificamos si A1 contiene un error y en su caso retorna '-' y en cualquier otro caso realizamos una búsqueda.
Hoy veremos un ejemplo del uso de los comodines de Excel (* y ?) en las funciones con el objetivo de obtener la suma parcial de unos importes correspondientes a un esquema clásico por apartados tipo
1
1.1
1.1.1
2
2.1
2.1.1
etc.
Veamos nuestro listado:
En este caso emplearemos la clásica función SUMAR.SI para acumular las cantidades pero basándonos en el uso del comodín individual de Excel: ? (cierre de interrogación), que representa un único caracter.
La fórmula buscada y que añadimos en cualquiera de las celdas amarillas del ejercicio, por ejemplo en D4, sería: =SUMAR.SI($A$2:$A$31;A4&".?";$B$2:$B$31)+SUMAR.SI($A$2:$A$31;A4&".??";$B$2:$B$31)
Lo importante es el uso del comodín al construir el argumento del criterio: A4&".?"
que condiciona a que además del valor de la celda A4 (1.3 en el ejemplo) acumule cualquier importe que coincida con el patrón
1.3.X, esto es, 1.3.1, 1.3.2, 1.3.3, 1.3.4, 1.3.5, 1.3.6, 1.3.7, 1.3.8 y 1.3.9 quedan fuera 1.3.10 y sucesivos...
por eso en la fórmula hemos incorporado la segunda suma con el criterio A4&".??".
Si bien este primer intento exige que los parciales estén dentro de los datos distribuidos...
Para combatir este problema tenemos otra opción con el comodín general * (representa un número de caracteres indeterminado).
Así, en D4 podríamos insertar: =SUMAR.SI($A$2:$A$31;A4&".*";$B$2:$B$31)
siendo el criterio A4&".*"
que acumulará cualquier importe que corresponda a un apartado tipo 1.3.XXXXXX.XXXX.XXXXX
Todo dependerá de la distribución de información en el listado o esquema original...
Un aspecto interesante cuando tenemos que mostrar ecuaciones personalizadas en nuestra hoja de cálculo y no queremos emplear la barra de herramientas de ecuaciones.
Además de todas las posibilidades a nuestra disposición la opción de Entrada de lápiz de ecuación... a la que podemos acceder desde la barra de herramientas anterior, o bien directamente desde la ficha Insertar > desplegable Símbolos > Ecuación > Entrada de lápiz de ecuación (abajo de la ventana desplegada).
Esta opción abrirá una ventana tipo pizarra (Control de entrada matemática) donde podemos, incluso con el ratón, pintar o escribir las variables, operaciones o fórmulas deseadas, como si del papel se tratara...
Se nos ofrecen algunas herramientas muy simples:
1 - Escribir
2 - Borrar
3 - Seleccionar y corregir
4 - Eliminar.
De especial relevancia la opción de Seleccionar y corregir que permite ajustar partes de lo
escrito que no se haya autodetectado correctamente.
Por ejemplo, si escribimos la famosa ecuación desarrollado por Einstein
E = m x c 2
Comprobamos como mi operador de multiplicación lo ha entendido como una sub x, y que la c la detectó como un signo de incluido.
Por tanto actuaremos con la opción de Seleccionar y corregir sobre ambos 'errores'. Tras rodear y seleccionar la 'x' aparecen posibilidades a nuestra disposición... elegiremos la que nos convenga.
De igual forma repetimos los pasos para el otro error...
Una vez finalizadas las correcciones, y vemos lo que queríamos en la Vista previa simplemente presionamos Insertar, lo que lleva nuestra ecuación a la hoja de cálculo como un objeto 'ecuación'... con todas las opciones de la barra de herramientas disponibles.
