El uso del Excel en el trabajo diario lleva a encontrar problemas que se repiten constantemente, para no tener que hacer el mismo proceso una y otra vez, es necesario grabar una macro; ésta es un conjunto de comandos que el Excel ejecuta automáticamente para realizar tareas repetitivas.
Entendiendo las Funciones Personalizadas en Excel
Procedimientos y Funciones: Una Distinción Clave
- PROCEDIMIENTOS: Realizan una acción. Por ejemplo, insertar filas, crear un gráfico, etc.
- FUNCIONES: Devuelven un valor. Por ejemplo calcular el valor presente de un rango, etc.
Como se aprecia los procedimientos son lo que comúnmente se conoce como macros que se pueden escribir o grabar, y cuya utilidad principal es cambiar el aspecto de la hoja de cálculo, mientras que las funciones calculan valores y los colocan como respuesta en la celda donde se ha pegado la función.
A su vez, dentro de las funciones existen dos clases: las que vienen con la aplicación Excel y las que puede definir el usuario para resolver problemas particulares.
Creación de Funciones Definidas por el Usuario en Excel VBA
Una función definida por el usuario se debe escribir en un módulo de Visual Basic para Aplicaciones (VBA) del Excel y sirve para calcular un nuevo valor realizando cálculos con valores (argumentos) que previamente se le deben proporcionar; es decir que su funcionamiento es exactamente igual al de las funciones que vienen incorporadas en el Excel, por lo tanto se utilizan para resolver problemas muy particulares del usuario.
Escribir el código de la función en el módulo que se acaba de insertar.
Lea también: Fiscal General y su impacto en las elecciones mexicanas
Siempre, al terminar el código de una función personalizada, se debe escribir el nombre la función, el signo igual y una expresión que la defina.
Componentes de una Función Personalizada
- El nombre de la función, que en este caso se ha llamado LIQ.
- Los argumentos, que son los nombres escritos entre paréntesis después del nombre de la función. Estos son los valores que el usuario debe proporcionar cuando va ha utilizar la función y a su vez son los valores que utiliza el Visual Basic para efectuar los cálculos necesarios.
- El valor devuelto, este es el valor que la función definida por el usuario colocará en la celda donde se haya utilizado, después de haber efectuado los cálculos.
Para ilustrar el último punto se presenta el siguiente ejemplo: es muy común que en los almacenes el precio de los productos se anuncie incluyendo el IVA, pero al momento de facturar se debe desagregar el monto de la venta entre ingreso para el almacén e IVA, lo cual requiere operaciones aritméticas sencillas que se complican un poco cuando en la venta se otorga un descuento.
Las funciones definidas por el usuario quedan disponibles en el libro en que fueron creadas, pero como lo importante es que puedan ser utilizadas en cualquier momento que se esté trabajando con Excel, pueden grabarse en el libro de macros personal o hacer que el libro en el cual están grabadas se cargue automáticamente cuando se activa el Excel.
Funciones Personalizadas en Power Query
Si se encuentra en una situación en la que necesita aplicar el mismo conjunto de transformaciones a consultas o valores diferentes, crear una función personalizada de Power Query que se pueda reutilizar tantas veces como necesite podría ser beneficiosa.
Más adelante, esta función personalizada se puede invocar en las consultas o valores que prefiera.
Lea también: La Función del Derecho Fiscal
Principios del Lenguaje M en Power Query
M es un lenguaje funcional, sensitivo a mayúsculas y minúsculas por lo que hay que ser cuidadoso con la sintaxis.
Incluye una gran cantidad de funciones integradas que podemos utilizar cuando realizamos transformaciones.
Si abrimos el editor avanzado de cualquiera de las consultas veremos un conjunto de fórmulas separadas por coma unas de otras y encerradas entre las instrucciones let e in.
Cada fórmula constituye un paso de transformación y están formadas por dos partes separadas por el signo “=”. A la izquierda del signo se declara una variable a la que se le asigna el resultado de evaluar la expresión de la derecha.
La expresión puede ser el llamado a una función, ya sea de la biblioteca estándar o nuestra.
Lea también: Contabilidad en la Administración
Una función es una asignación de un conjunto de valores de entrada a un solo valor de salida. son los valores que requiere la función para poder ejecutarse, pueden ser opcionales.
En un paso de fórmula podemos usar, además de las funciones de la biblioteca estándar de Power Query, una función personalizada.
Podemos crear funciones personalizadas en Power Query dentro de una consulta o como una consulta separada. Si la definimos dentro de una consulta solo podremos usarla en esa consulta.
La información del tipo de dato se puede incluir de manera explicita en la declaración tanto de los parámetros como del valor de retorno de la función. Si no la incluimos el tipo de datos será implícito. Los valores implícitos son de tipo any, es similar al tipo objeto en otros lenguajes. Los parámetros de una función pueden ser opcionales.
Proceso de Creación de Funciones Personalizadas en Power Query
Para empezar, debe tener un parámetro que tenga un valor que actúa como ejemplo.
- Use la experiencia del conector para conectarse a los datos donde se hospeda.
- Cuando hayas seleccionado los datos, selecciona el botón Transformar Datos o el botón Editar.
- Empiece por usar la experiencia del conector de carpetas para ir a la carpeta donde se encuentran los archivos y seleccione Transformar datos o Editar. Estos pasos le llevan a la experiencia de Power Query.
- Cree un nuevo parámetro con el nombre parámetro de archivo y el tipo de Binario. Las funciones personalizadas se pueden crear mediante cualquier tipo de parámetros.
- Haga clic con el botón derecho en el valor Binario de su elección en el campo Contenido y seleccione la opción Agregar como nueva consulta. Esta opción crea eficazmente una nueva consulta con un paso de navegación directamente a ese archivo como binario y el nombre de esta nueva consulta es la ruta de acceso de archivo del archivo seleccionado.
- Haga clic con el botón derecho en Parámetro de archivo en el panel Consultas. Esta operación crea eficazmente una nueva función que se vincula con la consultaTransformar archivo de ejemplo. Los cambios que realice en la consulta Transformar archivo de ejemplo se replican automáticamente en la función personalizada.
- Con la nueva función creada, seleccione la consulta con el nombre Transformar archivo de ejemplo. Esta consulta ahora está vinculada al archivo de transformación y a la función, así que los cambios realizados en esta consulta se reflejan en la función.
- La primera transformación que debe producirse en esta consulta es una que interpreta el binario. El formato de todos los archivos CSV de la carpeta es el mismo. Todos tienen un encabezado que abarca las cuatro primeras filas.
- Promover encabezados: los encabezados de la tabla final se encuentran ahora en la primera fila de la tabla. Después de promocionar los encabezados de columna, Power Query de forma predeterminada agrega automáticamente un nuevo Changed Type paso que detecta automáticamente los tipos de datos de cada columna.
La función de Transformar archivo se basa en los pasos realizados en la consulta Transformar archivo de ejemplo. Sin embargo, si intenta modificar manualmente el código de la función de Transformar archivo, recibirá una advertencia que lee The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated.
Invocación de una Función Personalizada en Power Query
Con la función personalizada creada y todos los pasos de transformación incorporados, puede volver a la consulta original donde tiene la lista de archivos de la carpeta (archivos CSV en este ejemplo).
- En la pestaña Agregar columna de la cinta de opciones, seleccione Invocar función personalizada en el grupo General.
- Dentro de la ventana Invocar función personalizada, escriba Tabla de salida como Nombre de columna nuevo.
- Seleccione el nombre de la función, Transformar archivo, en la lista desplegable Consulta de funciones.
- Después de seleccionar la función en el menú desplegable, se muestra el parámetro de la función y puede seleccionar qué columna de la tabla se va a usar como argumento para esta función.
- Después de seleccionar Aceptar, se crea una nueva columna con el nombre Tabla de salida. Esta columna tiene valores de Tabla en sus celdas, como se muestra en la siguiente imagen.
- La función se aplicó a cada fila de la tabla utilizando los valores de la columna Contenido como argumento de la función.
- Ahora que los datos se han transformado en la forma que busca, puede expandir la columna de la tabla de salida seleccionando el icono de expansión.
- Puede verificar que tiene datos de todos los archivos de la carpeta comprobando los valores en la columna Nombre o Fecha. En este caso, puede comprobar los valores de la columna Fecha, ya que cada archivo solo contiene datos de un solo mes a partir de un año determinado.
Este método en específico que vimos puede serte muy útil para obtener datos de sitios públicos, financieros, de acciones y demás, podemos tomarlos sin estar creando una nueva consulta.
Ejemplo Práctico: Datos de Tipo de Cambio
Antes de entrar de lleno, este caso lo vimos originalmente en nuestra dinámica de Power BI Studio.
En el ejemplo anterior tomamos unas tablas de la web con cambios de divisas desde Dólares Americanos a Pesos Colombianos, día por día.
En la URL puedes cambiar el año, por ejemplo, el 2016 cambiarlo al 2017. Estos datos, a pesar que no estén tan bien estructurados, en Power Query los vamos a revisar fácilmente.
En Power BI me voy al Power Query. Aquí estamos viendo la página web tal cual como la veíamos y quiero traerme por medio de ejemplos que me tome cada uno de los días y el monto.
Así como cuando agregas una columna desde ejemplos, también nos podemos traer columnas desde sitios web. Sigues escribiendo más ejemplos para que lo copie acá en Power Query, como "2 de enero 3 mil 149". Luego el tercer valor. De hecho, aquí parece que ya me lo tomó, solo hay que revisar rápidamente. Por ejemplo, 5 de enero que es 3 mil 213, es correcto. Parece ser que ya está tomando el valor correcto para todos los meses y todos los días.
Ahora voy a dividir la columna para que tengamos en una sola columna la fecha y en otra el monto. Asegúrate de tener la columna seleccionada. Así nos separa los valores en dos columnas.
Te vas a la primera posición del texto en el Editor Avanzado. Vamos a definir la variable como texto agregando un "as text". Si fuera un número, por ejemplo, le pondríamos "as number".
Agregamos comillas donde vamos a utilizar nuestra variable. Usamos el símbolo "&" dos veces y ponemos el nombre de la variable entre esos símbolos.
Aquí quiero hacer un listado de cada año que quiero que me tome de ese sitio. Dentro de los corchetes, vamos a poner un listado de número, escribiendo el número inicial, luego punto y punto "..", y el número final del listado.
Para nuestro ejemplo vamos a hacer un listado del 2016 hasta el año presente.
Ahora ya tendremos la tabla de tipo de cambio para cada año, desde el 2016 hasta el 2022, en este caso.
Ahora tenemos que expandir los valores de cada tabla. Ya básicamente solo tenemos que hacer un par de transformaciones más. Por ejemplo, yo lo que haré para este caso en específico es combinar el día con el año.
Listo, ahora ya tenemos los cambios que necesitamos, con los datos de todos los años en una misma tabla. Se cargan los datos. Voy a poner un gráfico de línea. Con todo esto solo quiero dejarte la enseñanza cómo trabajar con funciones personalizadas.
Funciones Personalizadas en ONLYOFFICE Docs
Es un placer introducir las funciones personalizadas en ONLYOFFICE Docs 8.1. Es una característica que te permite insertar funciones personalizadas para calcular diversos parámetros y fórmulas.
Esperamos que esta característica recién añadida se convierta en un activo valioso en tu kit de herramientas, agilizando tu flujo de trabajo con la capacidad de crear funciones personalizadas adaptadas a tus necesidades. En ONLYOFFICE, nuestro principal objetivo es ayudarte a conseguir la excelencia mediante la implementación de herramientas y funciones innovadoras.
Ejemplos de Funciones
- La media geométrica es una medida de tendencia central que resulta útil para conjuntos de datos que implican multiplicaciones o abarcan varios órdenes de magnitud.
- La media armónica es una medida de tendencia central que resulta útil para conjuntos de datos que implican tasas o proporciones.
- La media cuadrática es una medida estadística de la magnitud de una cantidad variable.
- Recupera el valor absoluto de un número. Puedes utilizar esta función para asegurarte de que un número es positivo.
- Combina varios valores de cadena.
- Escribe en mayúsculas la primera letra de un valor de cadena. El primer carácter irá en mayúsculas, mientras que el resto de letras irán en minúsculas.
- Convierte una marca de tiempo en un formato amigable.
- Divide un número. Esta función es una alternativa al operador /.
- Comprueba si un número es divisible por pares. Cuando no hay resto, esta función se evaluará a verdadero. Cuando hay un resto, esta función se evaluará a falso.
- Convierte todos los caracteres de una cadena a minúsculas.
- Multiplica un valor por un número. Esta función es una alternativa al operador *.
- Convierte una cadena en un número.
- Genera un número aleatorio dentro de un rango. Con esta función, los números generados son completamente aleatorios y pueden repetirse.
- Calcula la raíz cuadrada de un número. Opcionalmente, especifique la raíz enésima a calcular.
- Redondea un número a un decimal especificado.
- Elimina las etiquetas SGML/XML y sustituye los espacios en blanco adyacentes por un espacio. Este filtro puede utilizarse para eliminar cualquier etiqueta HTML de una variable.
- Cambiar el texto a la carcasa del título. Cada palabra de la cadena resultante empezará con mayúsculas, mientras que el resto de caracteres se escribirán en minúsculas.
- Eliminar los espacios en blanco al inicio y al final de una cadena.
- Cambiar todos los caracteres a mayúscula.