Contar las Ocurrencias de una Cadena de Texto Dentro del Contenido de un Rango en Excel - CONTAR.SI()

Hola a todos, hoy vamos a hacer una pausa en la serie de posts dedicados a la construcción de portafolios de inversión, para resolver este problema que me planteó el día de hoy un amigo.

En su trabajo, tiene un archivo que incluye una columna como la que aparece a continuación y que puede llegar a tener un poco más de 100.000 filas:

Entre la minería de datos que debe realizar con el archivo, en esta columna en particular, debe encontrar la cuenta de las veces que se repite la cadena de texto "NITO".

Como pueden observar, para estas 7 filas, a simple vista la cuenta es muy sencilla y la respuesta correcta es que la cadena aparece 4 veces. Para el archivo que se encuentra mi amigo en la vida real, el conteo a simple vista no es una opción.

Entonces, cuando este me preguntó cómo podía hacer para calcular esta cuenta con una fórmula, le hablé de la función CONTAR.SI( ).

Y cómo debemos aplicarla entonces? A continuación podemos ver la construcción de la fórmula:

Formato de Celdas Personalizado para Puntos Básicos en Excel - Basic Points

Hola a todos, hoy nos vamos a alejar un poco del TSQL, pero muy pronto estaremos regresando. Esta semana, alguien me preguntó si sabía cual era la opción que debía seleccionar en Excel, para aplicar el formato de puntos básicos el cual es muy común en economía. 

Los puntos básicos se suelen utilizar para expresar la variación que existe para una variable al medirla en dos periodos de tiempo diferentes. Un ejemplo claro es cuando el banco central de un país, varía la tasa de intervención (o de referencia). Supongamos que dicha tasa se encuentra en el mes inicial en 5% y que para el siguiente mes, el banco decide subirla, pasándola a 5,25%. En dicho caso, se dice que la tasa de intervención, presenta un incremento de 25pb (puntos básicos o basic points en ingles).

Este es un cuadro de ejemplo, como el que quería trabajar quién me pregunto:

La idea es que en la columna D, podamos tener la diferencia entre el Año 2017 y el 2016, expresada en puntos básicos.

Entonces, le dije que dicha opción no existía y que lo que debía hacer era utilizar una opción de formato personalizado, combinada con la multiplicación de un factor (10.000) que le permita expresar los puntos básicos.

Entonces, para empezar nos vamos a ubicar en la celda D4 y vamos a escribir la siguiente fórmula:

Extracción de Datos desde Archivos de Texto Usando Power Query de Excel (ETL - BI)

Hola, hoy vamos a continuar con los posts sobre extracción de datos (ETL) utilizando Power Query. Hoy realizaremos la extracción, desde un archivo de texto CSV (separado por comas o punto y comas).

Iniciemos por crear un nuevo archivo de Excel y vayamos al menú de Power Query:

El archivo que vamos a extraer tiene la siguiente estructura:

Ejecutar una macro al momento en que abrimos nuestro archivo de excel - vba

Hola, hoy vamos a escribir un poco de código en vba. Lo que vamos a hacer es construir una macro que se ejecute en el mismo instante en que damos doble clic en un archivo de excel y este se abre. Esta funcionalidad puede ser muy útil para limpiar información de nuestro archivo, para iniciar algunas variables en el mismo, para guardar una copia del archivo etc. 

Todo depende del problema que estemos abordando. En mi caso, la voy a utilizar para que el usuario vea un msgbox.

Iniciemos entonces, llamando nuestro editor de vba con Alt + F11 e insertemos un nuevo módulo:

Vamos a dar doble clic en ThisWorkbook:

Trabajando con los argumentos del método GetOpenFilename en vba

En el post anterior tuvimos nuestro primer contacto con el método GetOpenFilename. Hoy vamos a revisar los argumentos opcionales que podemos incluir cuando nuestra macro realice la llamada al método.

La siguiente es la estructura completa del método con sus argumentos:

Application.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

Donde:

FileFilter: Es una cadena de texto que nos sirve para aplicar un criterio de filtrado.

FilterIndex: Es un número que funciona como indice para indicar cuál es el criterio de filtrado a usar.

Title: Sirve para personalizar el titulo del cuadro de diálogo. Si se deja en blanco, por defecto aparecera: Abrir.

ButtonText: Solo aplica para Mac.

Multiselect: Acepta el valor True (verdadero) o False (falso). True nos permite seleccionar más de un archivo a la vez. False solo nos permite seleccionar un archivo.

Recordemos que cuando llamamos el método sin ninguno de sus argumentos lo que tenemos es el siguiente cuadro:

Vamos a hacer el llamado pasando un valor para el argumento FileFilter: 

Usando el método GetOpenFilename de vba en nuestras Macros

Hoy vamos a trabajar con el método GetOpenFilename del objeto Application en Excel. 

Dicho método, lo podemos utilizar en las macros que escribamos en vba, cuando necesitemos que el usuario tenga que interactuar con la aplicación y deba suministrar un nombre de archivo y una ruta para que la macro realice alguna operación con el mismo.

Ademas, al hacerlo de esta forma le vamos a dar un toque mucho más profesional a nuestra macro.

Cuando invocamos el método GetOpenFilename abre el siguiente cuadro de diálogo:

Este cuadro es familiar para todos los que trabajamos con Excel o con cualquier aplicación de Office. Basta con navegar en las distintas carpetas hasta encontrar el archivo que queremos, seleccionarlo y dar clic en el botón Abrir.

 Lo que vamos a hacer es escribir una sencilla macro como ven a continuación:

La primera linea, llama el cuadro de diálogo y asigna el valor de retorno del mismo a la variable arch. Este método nos devuelve una cadena de texto que contiene el nombre del archivo y la ruta en la que este se encuentra.

Invertir cadenas de texto en excel creando una función personalizada con vba

Dentro de las muchas funciones para el tratamiento de textos que trae excel incluidas no hay una que nos permita invertir cadenas. Es cierto, no la hay. Si tenemos en una celda: "Hola" y queremos invertirla para tener: "aloH" no lo podemos hacer con las funciones disponibles.

Sin embargo, la librería de funciones que incluye vba si tiene una para cumplir con dicho propósito. Se llama StrReverse( ) y lo que nos pide como argumento, es una cadena de texto para poder invertirla.

Como la función esta disponible en vba, tendremos que escribir un poco de código y crear una función personalizada. Así es, en excel también podemos crear nuestras propias funciones y llamarlas en nuestras hojas de cálculo tal y como lo hacemos con BUSCARV, BUSCARH, IZQUIERDA, DERECHA o cualquier otra.

Vamos entonces a construir una función con la que podamos invertir la cadena de texto que tenemos en la celda B2:

Digitamos la combinación de teclas Alt + F11 para llamar el editor de vba y creamos un nuevo módulo: