Importar datos de texto a Excel (4 de 5)

En esta ocasión, y siguiendo con la serie de artículos que trata de la importación de datos, vamos a ver cómo podemos tratar un archivo de texto para que lo tengamos disponible en Excel.

Usualmente, nos vamos a encontrar archivos de texto en formato .csv  y   .txt. En cuanto al primero (CSV: Comma Separated Values) su propio acrónimo nos indica que es un formato en el que el texto está separado por comas, de forma que quedan definidas y ordenadas las columnas que se pueden extraer de dicho texto. Podemos aprender más sobre archivos CSV en Wikipedia: CSV . En relación al formato TXT, la separación entre columnas la delimita normalmente el caracter de tabulación o el espacio.

Excel puede convertir estos formatos de archivo en tablas de datos, basándose precisamente en los tipos de separación anteriormente comentados. Donde hay un indicador de separación (ya sea, coma, punto y coma, tabulación o espacio), Excel detecta que el siguiente texto debe quedar encuadrado en la siguiente celda de la misma fila. Para cambiar de fila, se detectan los saltos de línea.

Vamos a practicar con un ejemplo y para ello nos vamos a aprovechar de una característica que tiene la red social Linkedin: la exportación de los contactos a un archivo en formato CSV. De esta forma, podremos importarlos a Excel y gestionarlos de la forma que necesitemos.

En primer lugar, accedemos a Linkedin y concretamente a "Contactos", donde en dicha página podremos ver al final un enlace que nos proporciona todos nuestros contactos en CSV:

Después de hacer click en "Exportar contactos", debemos elegir "Microsoft Outlook (archivo .CSV)" y guardar el archivo en un lugar identificado de nuestro disco duro.

Abrimos Excel y vamos a la ficha "Datos". Una vez activa esta ficha, en el grupo "Obtener datos externos", hacemos click en el botón "Desde texto" y obtendremos el siguiente cuadro que pertenece al paso 1 de 3 de un asistente:

Por defecto, está seleccionada la opción "Delimitados" y es la que debemos dejar. Si nuestro archivo fuese de texto y la separación fuese por espacios, deberíamos elegir la segunda opción "De ancho fijo".

Al hacer click en "Siguiente", obtenemos:

..pero observamos que en la vista previa de los datos, la opción de "Tabulación" que nos sale por defecto, no funciona, porque no se "forman" columnas (al fin y al cabo, la "Tabulación" no era la opción más indicada, puesto que hay comas como separación en el archivo CSV de Linkedin). Debemos ir probando con el resto de opciones, pero nos damos cuenta de que la opción lógica ("Coma") tampoco funciona. Finalmente, probamos que la opción "Otro" con unas comillas introducidas en el campo, es la ideal (mejora si activamos la casilla del campo "Considerar separadores consecutivos como uno sólo"):

Hacemos click en "Siguiente" y tenemos ante nosotros el 3er paso:

En el que, lo aconsejable es dejar la opción por defecto "General" y hacer click en "Finalizar". Al hacerlo, nos sale un cuadro de diálogo donde podemos elegir el lugar donde aparecerá la tabla importada:

Sólo tenemos que hacer click en "Aceptar" y ya tenemos nuestros contactos de Linkedin en Excel:

A partir de aquí, podemos tener todo el control que Excel nos permite: dar formato, eliminar/reordenar las columnas que consideremos oportuno, etc. 

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Aplicación práctica de la herramienta SOLVER de Excel

Vamos a abordar en este post una herramienta de Excel bastante conocida en algunos ámbitos (matemáticas, ingeniería, economía, química, etc.) en los que el análisis de datos y la programación lineal es parte importante.

SOLVER (definida de forma coloquial y simple), es una herramienta que sirve para saber en qué cantidad se tienen que combinar varias cosas (variables) para formar un todo perfecto u óptimo (objetivo) ateniéndonos a ciertas condiciones dadas y a algunas restricciones también dadas.

Para complementar la definición anterior, pongamos un ejemplo típico susceptible de solucionar con la herramienta SOLVER de Excel:

Como en todos los problemas, una de las cosas más importantes, es esquematizarlo de forma gráfica para entenderlo y estar en disposición de resolverlo más fácilmente. A continuación hacemos esto, y además asignamos unos valores de ejemplo.

También, para trabajar posteriormente en SOLVER es muy conveniente que la plantilla que hemos configurado para nuestro planteamiento, la copiemos, la peguemos y la dejemos sin datos más abajo para que ahí puedan introducirse las formulas que definirán las relaciones que habrá entre las restricciones y los datos que nos tiene que dar SOLVER:

Una vez llegados aquí, ya hemos hecho lo más laborioso y difícil. Es la hora de SOLVER y para ello, debemos asegurarnos de que Excel lo tiene entre sus complementos. Para ello, hacemos click en el botón "Complementos" del grupo "Complementos" de La ficha "Programador" y activamos la casilla correspondiente:  

Después, para ejecutar el complemento y empezar a utilizarlo, hacemos click en el botón "Solver" que estará en el grupo "Análisis" de la ficha "Datos":


Aparecerá la ventana de SOLVER, en al que debemos seleccionar en primer lugar:

• La celda objetivo. Es decir, la celda donde aparecerán los beneficios (en €) que se podrán obtener con la combinación óptima (H43).
• El tipo de optimización que hará SOLVER. En este caso, queremos Maximizar los beneficios.
• El rango de celdas donde aparecerán los resultados que aportará SOLVER, en este caso H39:J40.

Una vez hecho esto, debemos decirle por último a SOLVER cuáles son las restricciones. Para ello, comenzamos con las restricciones de crédito a los clientes, haciendo click en el botón "Agregar" y seleccionando el rango donde pusimos las fórmulas de las restricciones (Referencia de celda) y seleccionando la propia Restricción... de esta forma:

Hacemos click en "Agregar" y seleccionamos los rangos de las siguientes restricciones (las de fabricación), de tal forma que al final, debe quedar así:

Una de las restricciones que podemos ver en la imagen anterior y cuyo objetivo es que los resultados sean exclusivamente enteros se agrega así:

 
Pues ya lo tenemos todo. Sólo nos queda hacer click en "Resolver" y obtendremos un cuadro similar a este:

... que al "Aceptar", vemos cómo aparecen los resultados en Excel:

Con las condiciones de rentabilidad o beneficio por cada producto y cliente y las restricciones de fabricación y crédito a los mismos, los máximos beneficios que se pueden obtener son 49.000 € si la política de ventas de la empresa incentiva a sus vendedores a cumplir con las cantidades y proporciones que SOLVER ha proporcionado en el cuadro anterior.

Podemos apreciar a continuación la utilidad de esta aplicación si hacemos un análisis de los resultados:

Pueden descargar, si lo desean, el archivo de Excel que ha servido para las explicaciones en este artículo:

  José Manuel Pomares Medrano

¿Qué tipo de gráfico utilizar en nuestros informes? (2/4)

En este 2º artículo de la serie ¿Qué tipo de gráfico utilizar en nuestros informes? vamos a abordar los gráficos que representan cuántas veces se repite algo que tenemos categorizado o segmentado. Es decir, gráficos de distribución de frecuencias o "Histogramas".

Por ejemplo, un director comercial puede necesitar un gráfico que exprese cómo se distribuye la cantidad de camisas vendida en un período determinado, según la talla (categoría). Otro ejemplo puede ser la necesidad de tener un gráfico que represente la distribución de las unidades de coches vendidos, en función del modelo.

Una vez llegados aquí, nos damos cuenta de que los gráficos de distribución de frecuencias se utilizan para mostrar como se distribuyen entre varias categorías, las unidades vendidas, pero NO el valor monetario de esas unidades.


2. Distribución de datos

En Excel, un "Histograma" es un gráfico de columnas para el cual hemos cambiado "filas" por "columnas", desde la ficha "Diseño" de las Herramientas de gráficos:

El resultado es que las columnas quedan sin separación, además de que Excel asigna un color distinto a cada categoría:

... y en la leyenda del histograma aparecen los nombres y colores de cada categoría.

Este tipo de gráficos es una opción recomendable cuando las categorías son pocas. En caso contrario, el tipo de gráfico más adecuado es uno de línea suavizada:

... aunque también podríamos combinarlo si no son demasiadas categorías:

Para suavizar la línea, debemos activar la casilla:

.... que está dentro del cuadro "Formato de serie de datos" (al que podemos acceder seleccionando la opción "Dar formato a la serie de datos..." del menú contextual que aparece al pinchar con el botón derecho encima de la línea.

Tablas de datos en Excel (2 variables)

Como decíamos en el post "Análisis de datos en Excel", llamaremos "Tablas de datos" a una utilidad mediante la cual podemos obtener los resultados que arrojaría una operación en caso de que una o dos de las variables implicadas en dicha operación tomaran distintos valores. En este artículo se estudiarán las Tablas de datos con 2 variables.

Conozcamos pues, cómo utilizar las Tablas de datos en Excel:

A continuación, vemos una tabla de datos completada:

Vemos una tabla en la que para distintos valores de ventas (encabezados de columnas) que pudieran conseguir los comerciales de una empresa, se pagarían distintos importes (en rojo), según se aplique un porcentaje de comisión u otro (encabezados de filas). Excel rellena automáticamente todos los importes en rojo gracias a la aplicación de esta forma de análisis dinámico sobre:

• Los encabezados de columna, en los que ponemos a nuestra elección los valores que podrían tomar las ventas de los comerciales
• Los encabezados de fila, en los que ponemos a nuestra elección los porcentajes de comisión que la empresa podría pagar
• 2 celdas (sobreadas en verde), cuyos valores no son relevantes para el cálculo de la "Tabla de datos", de hecho, podrían estar vacías o tener cualquier valor. Lo que si es importante es su situación, pues estas celdas deben estar localizadas y fuera del ámbito de la Tabla de datos que queremos conseguir. El cometido de estas celdas es servir únicamente de referencia a Excel.
• 1 celda (sombreada para la ocasión en naranja), que debe tener una formula mediante la cual, Excel pueda saber qué relación tienen las celdas verdes.

¿Cómo se hace?

La forma de conseguir que Excel rellene automáticamente todos los importes que habría que pagar a los comerciales según porcentaje de comisión y según nivel de ventas, es la siguiente:

1) Introducimos en la celda naranja (DEBE ESTAR EN LA POSICIÓN QUE VEMOS EN LA IMAGEN, ES DECIR, EN LA ESQUINA SUPERIOR IZQUIERDA DE LA TABLA QUE DESEAMOS FORMAR) una fórmula que relacione de la forma que queramos a las celdas de color verde (en la imagen de abajo, podemos ver que la relación es promedio de ventas x % de comisión). Como ya se ha dicho antes, es indistinto qué valores contienen las celdas verdes, puesto que lo importante es tenerlas identificadas, fuera del rango de la Tabla de datos y que la celda naranja las relacione.

2) Seleccionamos todo el rango de la tabla que se formará:

3) Seleccionamos la "Ficha Datos", el botón "Análisis Y si" que hay dentro del grupo "Herramientas de datos", y después seleccionamos la opción "Tabla de datos...".

4) Una vez hecho, aparecerá un cuadro de diálogo en el que debemos hacer la siguiente referencia a las filas:

5) Después, hacemos la referencia a las columnas:

6) A aceptamos.... con el consiguiente resultado que veíamos al principio del artículo:

¿Otras opciones similares?

El mismo resultado que obtenemos con el análisis "Y si" de Excel, lo podemos conseguir introduciendo una fórmula en una de las celdas y haciendo uso de "Autorrellenar" y con la precaución de utilizar las "Referencias relativas" necesarias en las celdas. Por ejemplo, en la celda que une la columna 50.000 € y la fila 1 %, podríamos poner:  =$B9*C$8  y arrastrar hacia abajo y hacia la derecha para conseguir un "autorrellenado", teniendo al final el mismo resultado:

Artículos relacionados:

Análisis de datos en Excel
Administrar escenarios en Excel
La utilidad "Buscar objetivo" en Excel

  José Manuel Pomares Medrano