excel n' cia

Xcl NT Words

2012-02-07T05:26:00.000-08:00

Hoy os presento Xcl NT Words, un pequeño programa parapracticar nuestro vocabulario inglés que podéis descargaros de mi sitio Xcl NT docs .Hasta ahora he creado aplicaciones con un componente fundamentalmente lúdicopara mostrar que con Excel y VBA se puede hacer incluso lo que no te imaginas.Sin embargo en esta ocasión la aplicación es fundamentalmente práctica.

Lo primero que quiero recordar es que al estar construidacon Excel 2010, la aplicación funciona tanto en éste como en Excel 2007. Sinembargo deben ser versiones originales, por lo visto (y en esto hablo de oídasya que no he tenido ocasión de comprobarlo personalmente porque las versionesque yo tengo instaladas son originales), en versiones “piratas” no se puedeejecutar… Otra cosa es que al ser necesario ejecutar el código VBA, debéisaceptar las macros, aunque esta nota a estas alturas parece innecesaria.

Todos los que hemos estudiado inglés nos hemosauto-examinado de vocabulario. Existen muchas aplicaciones en el mercado quepermiten evaluar nuestro conocimiento de las palabras contenidas en laaplicación, yo tengo varias con las que he aprendido mucho.

En la herramienta que propongo he querido incluir algo másde valor añadido (al menos no se de ninguna aplicación que contenga estautilidad extra), puedes alimentar base de datos de la aplicación con laspalabras que te interesan. Así podemos incluir vocabulario específico para darleun enfoque científico, legal, médico, técnico,… Por otro lado tenemos la opciónde seleccionar la dificultad de las palabras que se incluirán en el examen demodo que pueda hacerse una selección aleatoria o examinarnos de las palabrasque más fallamos. A mi juicio, esta última es la que hace que aprendamos másrápido las palabras en las que nos encallamos.

Como suele suceder en los exámenes de vocabulario, podemosencontrarnos con palabras que tengan varios significados, pero sólo uno seráconsiderado correcto. Si tenéis dudas podéis comprobar las palabras existentesy su significado en la pestaña Info Palabras.

Como en los casos anteriores, la aplicación te proporcionagráficos e informes que se actualizan en tiempo real a medida que pasa eltiempo. No sólo con históricos de tus notas, si no también con la situación encada momento del propio test, información detallada sobre las palabrasindividuales… Este como de costumbre, es el guiño que realizo al trabajo deoficina, que como sabéis es donde Excel y VBA ofrecen su verdadera utilidad.

Personalmente estoy utilizando Xcl NT Words para estudiar el vocabulario nuevo que aparece en las películas inglesas. Como hace mucha gente, para mantener mi inglés en forma, veo películas en versión original inglesa con los subtítulos en inglés. Creo un documento nuevo para cada película y sinceramente me parece una forma fácil y entretenida de memorizar el vocabulario.

Pienso a medida que vaya acumulando documentos los iré colgando en Xcl NT docs, os invito a haceros con ellos y ahorraros el trabajo de buscarlas en el diccionario. Espero que os guste la aplicación, si tenéis algún comentario o sugerencia como siempre me encantará oírlas.

Encuesta sobre uso: Excel 2010, Excel 2007 y anteriores

2012-02-03T01:48:00.000-08:00

Los que sois habituales en el blog estáis acostumbrados aver en el lateral derecho la pequeña encuesta que voy haciendo de uso de lasdistintas versiones de Excel.

Si no recuerdo mal, Excel 2010 salió a la venta allá porjunio de 2010, y aunque la encuesta que realicé durante el año pasado tiene unamuestra pequeña y técnicamente no muy representativa de toda la población, noparece que a día de hoy haya desbancado en uso a Excel 2007.

Como podéis ver en el lateral, podéis informar durante el 2012 de laversión de Excel que utilizáis en la nueva encuesta de la derecha. Según ésta a díade hoy aún Excel 2007 es la versión mayoritaria, sin embargo el número de votosde momento es pírrico...

Como por Google Analytics veo que el porcentaje departicipación en la encuesta en relación al número de visitas es bajísimo ospediría un click. Me resulta interesante ver cómo va evolucionando con eltiempo el uso de las distintas versiones y cuando se produce el “sorpasso” porlo que os animo a que votéis para hacer la encuesta más representativa.

Personalmente compre Excel 2010 y lo instalé en uno de misordenadores. La verdad es que estoy muy contento con él y a nivel privado es laúnica versión que utilizo. Sin embargo, en cuanto al uso en la empresa, en micaso y por los comentarios que he recibido, Excel 2007 aún es mayoritario. Enparte es lógico ya que a mi juicio, para un usuario medio, Excel 2007 es unaversión equivalente a Excel 2010.

Por lo que me ha comentado algún lector del blog, losarchivos creados en Excel 2010 no se abren en copias “piratas” de Excel. Bueno,entiendo que es una medida razonable desde el punto de vista de la empresa, quela verdad me extraña que no hubieran aplicado antes. Los archivos que voycolgando en Xcl NT Tools están construidos con Excel 2010, así que me temo quesólo podréis examinarlos si habéis pasado por caja…

Comenté en su día en la entrada Excel 2010 vs Excel 2007 lasdiferencias que había entre ambas versiones en base a la versión beta que en sudía estuvo disponible. Aunque he recibido alguna sugerencia en cuanto a ampliarel tema, creo que hay mucha información disponible sobre las características ymejoras que aporta Excel 2010, tan sólo hay que ir a la página oficial deMicrosoft o al sitio de Excel en Facebook donde hay disponible información deprimera mano sobre sus mejoras, las nuevas tablas dinámicas, los minigráficos osparklines, nuevas funciones y mejoras de cálculo, etc, etc,…

No sé si en esas páginas dedican espacio al cambio denomenclatura de algunas fórmulas, que algunos en este blog habéis comentado quees molesto y negativo, pero que personalmente pienso que no tiene mayortrascendencia que la de escribir la fórmula de otro modo.

Recordad pasaros por el lado derecho y dejar vuestro votito en la encuesta...

Gracias!!

Basics 11, TRUNCAR y ENTERO

2012-02-01T01:23:00.001-08:00

La fórmula TRUNCAR se diferencia de la que vimosrecientemente =REDONDEAR, en que elimina la parte a partir delnúmero decimal especificicado. Tanto si truncamos el número 9,99 como sitruncamos 9,08 obtendremos 9 en el caso de haber elegido 0 números decimales.Si hubiésemos elegido uno obtendríamos 9,9 en el primer caso y 9,0 en elsegundo.

Sintaxis:

=TRUNCAR(Número; Núm_decimales)

Número. Es el número que desea truncar designado como tal o por referencia a una celda.

Núm_decimales. Es un número que especifica la precisión al truncar. El valor predeterminadodel argumento núm_decimales es 0.

Para el caso de que nos interese truncar a 0 decimales, laformula truncar es equivalente a la fórmula Entero. La sintaxis de esta fórmulaes muy sencilla, simplemente requiere identificar la celda cuyo contenidoqueremos convertir en entero.

Sintaxis:

=ENTERO(número). Siendo este como en los demás casosdesignado por sí mismo o en referencia a una celda)

Es importante recordar que la fórmula =ENTERO redondeaal entero inferior más próximo, es decir no redondea de la misma forma que lafórmula =REDONDEAR que lo hará al inferior o superior más próximo atendiendo alestándar de redondeo de decimales, superior o inferior a 5.

Basics 10; REDONDEAR

2012-01-30T04:27:00.000-08:00

Ocurre en Excel, que aunque en una celda hayamos puesto un formato con sólo 2 decimales(y por tanto veamos por ejemplo 2,47), no quiere decir que el número almacenadoen dicha celda esté redondeado del mismo modo. De hecho, Excel almacena esenúmero con los decimales necesarios (por ejemplo 2,46654748).

Este en apariencia insignificante detalle hace que nos encontremos condescuadres cuando estamos trabajando con una cantidad considerable de datosnuméricos. Hay que tener en cuenta que generalmente, en los informesfinancieros un decimal no tiene porqué significar una pequeña cantidad. Podemosestar hablando de miles…

De este modo cuando queramos comparar varias fuentes de datos distintas, se pueden llegar aproducir descuadres de una o incluso varias unidades al sumar varias celdas. Es posible que podamos obviar dichas diferencias, aunque si nuestros usuarios son mínimamente exigentes no les agradará descubrirlas, pudiendo incluso generar algo de desconfianza en nuestro trabajo.

Por otro lado, si los cálculos influyen en una construcción más compleja, que realice comprobaciones y checkings intermedios, o tenga macros dependientes de ciertas verificaciones, nos puede complicar la vida y dificultar la automatización.

La forma de solucionar este inconveniente es utilizando la fórmulaREDONDEAR. Como su propio nombre indica, esta fórmula redondea un número condecimales a un número entero o al número de decimales especificado.

Sintaxis:
=REDONDEAR(Número;Núm_Decimales)

=Redondear; Redondea.

Número; El número que desea redondear (puede ser el número en sí o la celdaque lo contenga).

Núm_Decimales; Especifica el número de decimales quedeseamos contenga el número anterior.

En la imagen siguiente podemos ver los efectos que tiene la FórmulaRedondear sobre distintos números.

Hay otras opciones para redondear que trataremos másadelante ya que pueden ser de utilidad para la construcción de determinadasaplicaciones; REDONDEAR.MAS/MENOS y REDONDEAR PAR/IMPAR,…

No hay que confundir la fórmula REDONDEAR con TRUNCAR sobre la que también trataré en breve.

Utilización de Símbolos

2012-01-23T11:18:00.001-08:00

A la hora de presentarinformes, solemos echar de menos caracteres que no están presentes en elteclado tradicional. En esos casos utilizamos los que se encuentran disponiblesal ejecutar el comando Símbolo.

Por poner un ejemplo, alhacer el típico informe de resultados mensual que compara los datos de un plancon el real, o del real con el año anterior, lógicamente es necesario incluiruna columna con la variación. Al presentar la tabla con los resultados, esconveniente utilizar todos los recursos a nuestro alcance para que los títulosde las columnas encajen con cierta armonía.

Si tenemos que poner encada encabezado la descripción exacta de la información que contiene puede serdifícil conciliar la literalidad con la estética. Esta es la razón de utilizarabreviaturas, como LYR, BUD, ACT,… Para el encabezado de la variación del año real comparado con el presupuesto o con el año anterior, podemos utilizar elsímbolo “∆“ (Delta), que como esconocido, es el símbolo matemático utilizado para la Variación.

Este símbolo, y multitudde otros se encuentran accesibles en el comando Símbolo, representado por laletra griega Omega y presente en la cinta de opciones dentro de la pestañaInsertar, grupo Texto.

Al ejecutar este comando,aparece la ventana Símbolo, en la que tenemos una gran variedad de iconos paraelegir. No sólo eso, podemos elegir grupos distintos de iconos en función de lafuente elegida en el desplegable Fuente.

Para el caso concreto deDelta personalmente suelo utilizar la de la Fuente Arial que se ve enla imagen.

Para insertarlo como sifuera un carácter más del teclado basta con seleccionarlo (momento en el queaparece resaltado en azul) y hacer doble click sobre el mismo o pulsar sobre elbotón insertar. Aparecerá en la celda en la que estábamos situados. Es posibleque tengamos que modificar el tamaño específico del símbolo para hacerlo encajarcon el del resto del encabezado, para ello basta con seleccionarespecíficamente el símbolo y modificar el tamaño de la fuente.

Delta es sólo un ejemplo,hay multitud de iconos a elegir perfectamente interpretables en una tabla comopara sustituir completamente un encabezado.

Ni que decir tiene, quedel mismo modo que hemos cambiado el tamaño podremos cambiar el resto depropiedades de la fuente exactamente igual que si fuera un carácter de tecladocualquiera.

Por supuesto podemos insertar otro tipo de elementos como imágenes prediseñandas, fotografías,... pero en este caso se trata de elementos tipográficos al mismo nivel que los caracteres del teclado.

Combinar colores en la celda

2012-01-16T12:05:00.000-08:00

Hoy hablaré de una opción estética que es tremendamente fácil, pero poco conocida y utilizada. Se trata de ir un poco más allá a la hora de utilizar la ventana del formato de celdas.

Antes de poner un ejemplo de su utilidad, que como en todos los elementos estéticos dependerá de la creatividad del usuario, veremos la mecánica. Supongamos una celda o grupo de celdas al que le vamos a aplicar este formato especial.

Con la celda seleccionada, acudimos a la herramienta de Formato de celdas, bien utilizando el botón derecho del ratón o desde la pestaña inicio de la cinta de opciones.

En la pestaña Relleno de esta ventana, ejecutamos el comando Efectos de relleno.

Aparece una ventana en la que podremos seleccionar dos colores que compondrán la celda. En nuestro caso rojo para indicar situación insatisfactoria y verde para indicar la óptima. Por otro lado seleccionamos la alineación vertical.

Esto puede utilizarse con fines meramente estéticos, pero también se puede utilizar de forma más creativa para dar algo más de sofisticación por ejemplo a nuestros informes.

Con Excel 2007 y Excel 2010 tenemos múltiples posibilidades como el Formato condicional y los Minigráficos. Voy a aportar una solución adicional que podréis utilizar por ejemplo para reflejar la situación de los KPI’s (Key Performance Indicators) de nuestro Cuadro de Mando Integral o Balanced Scorecard.

Para ello en nuestra celda utilizamos la fórmula Repetir tal y como muestra la imagen, esto hará que se repita un carácter o caracteres tantas veces como nos indique una celda determinada. En nuestro caso la celda D9, que puede tomar valores entre 1 y 10.

Una opción similar a la que estamos tratando ahora es la del formato condicional con Escalas de color. Sin embargo, se diferencia de ella en que en esta opción aparecen todos los colores de forma simultánea en la celda.

Con un poco de creatividad podéis construir la imagen siguiente, similar a los Velocímetros que incluyen los colores de un semáforo, pero que en este caso no se representan en una semicircunferencia sino en una franja.

Rastrear precedentes y dependientes.

2012-01-09T11:11:00.000-08:00

Hoy hablamos de una deesas utilidades que generalmente no se suele utilizar por los creadores de las hojas, ya que si el tamaño no es desmesurado solemosrecordar de donde vienen los cálculos. Sin embargo es muy práctica cuando estásrevisando una hoja construida por otra persona para determinar cómo estáconstruida y qué se pretende con ella.

Se trata de lasutilidades Rastrear precedentes y Rastrear dependientes. Ambas están situadasen la ficha Fórmulas de la Cinta de Opciones y dentro de ella, en el grupo decomandos Auditoría de Fórmulas.

El comando Rastrear precedentes nos ayuda a ver de manera inmediata las celdas que intervienen enel cálculo de una celda.

Es cierto que estainformación está disponible en la barra de fórmulas o simplemente haciendodoble click sobre la celda, sin embargo es más cómodo acudir con la vistadirectamente a la celda/s implicadas siguiendo las flechas que buscarlas en la fórmula o en la cuadrícula, sobre todo si están distantes entre sí.

La utilidad de rastreardependientes permite visualizar de forma inmediata todas las celdas que seven afectadas por una celda en concreto.

No hace falta resaltar laimportancia de saber dónde van a influir los cambios que hagamos en unadeterminada celda. De hecho es muy frecuente cuando se trabaja con hojas decálculo formuladas, deteriorarlas al eliminar o modificar celdas por no tenerpresente las implicaciones que tienen.

Tanto para indagar sobrePrecedentes o Dependientes, pulsando sucesivamente los iconos vamos avanzandoen el desarrollo de los cálculos; así por ejemplo, si pulsamos una vez sobrerastrear precedentes, nos indicará las celdas que influyen sobre nuestra celda,pero si pulsamos nuevamente, veremos a su vez, las celdas que influyen sobreestas celdas y así sucesivamente.

Una vez conocidos losprecedentes y dependientes de una celda pierde su encanto tener esas flechas ennuestra cuadrícula, para deshacernos de ellas utilizamos el comando QuitarFlechas, para borrarlas todas o quitarlas por niveles.

Control de cambios

2011-11-14T06:58:00.001-08:00

En aquellos casos en los que una hoja sea utilizada y modificada por varios usuarios, suele ser interesante tener controlados los cambios que se realizan y por quien. Esto incrementa la confianza y seguridad del responsable de la misma ya que puede revisar los cambios efectuados.

En ocasiones ni siquiera hace falta que utilicen la hoja varias personas para que nos interese disponer de una herramienta que nos proporcione la trazabilidad de nuestros propios cambios.

Excel ofrece una herramienta para identificar los cambios realizados tanto mostrando el valor inicial y final de la celda cambiada, como identificando el usuario que ha realizado el cambio, momento en que lo ha realizado,… Pero no se queda ahí, además de permitirnos realizar esta revisión nos permite aceptar o rechazar dichos cambios.

El comando Control de cambios se sitúa en la Cinta de Opciones dentro de la ficha Revisar.

Ejecutando este comando, acedemos a una primera elección en cuanto a la acción a realizar. En primer lugar, debemos indicarle a Excel que queremos resaltar los cambios (ya que la segunda opción es la de Aceptar o Rechazar los cambios existentes, que en un libro nuevo serán ninguno).

Ejecutando resaltar cambios, se abre la ventana Resaltar Cambios. En la que podremos identificar qué cambios queremos monitorizar.

El control de cambios sólo se encuentra disponible para libros compartidos. Por tanto es necesario activar el primer check box con el que el libro se comparte automáticamente. En cuanto al resto tenemos la posibilidad de configurar la información a controlar.

Cuando, Todos o a partir de una fecha determinada.

Quién, Todos o solamente determinados usuarios.

Dónde, Se puede limitar el área de nuestro libro en la que se monitorizarán los cambios. En caso de no indicar nada se entenderá todo el libro.

A la hora de mostrar los cambios tendremos dos opciones:

Resaltar cambios en pantalla; con esta opción aparecerán resaltadas en azul las celdas en las que ha habido cambios con un comentario en cada una de ellas mostrando los detalles.

Mostrar cambios en una hoja nueva; Personalmente prefiero esta opción. Aparece una nueva pestaña en nuestro libro llamada Historial en la que aparecen resumidos todos los cambios con su información.

Aceptar o rechazar cambios.

Una vez que hemos trabajado con un libro y hemos guardado los cambios, tendremos la opción de Aceptar o Rechazar los cambios.

Ejecutando este comando Aceptar o rechazar cambios, nos aparece una ventana similar a la anterior, en la que podremos delimitar los cambios que queremos aceptar o rechazar por razón de tiempo, usuario o área de trabajo.

Tras delimitar los cambios a tratar mediante estos parámetros, aparecerán uno tras otro los cambios a revisar, hay que aceptar o rechazar cada cambio para pasar al siguiente. O en caso de que lo tengamos claro podemos aceptar o rechazar todos los cambios en bloque.

Trucos y Consejos 6; Duplicar hojas.

2011-11-07T12:24:00.000-08:00

Hace pocos días me sorprendió ver como duplicaba hojas un compañero de trabajo. Pensaba que la forma de hacerlo sobre la que hablaré a continuación era universalmente conocida, pero viendo que no es así, la incluyo bajo el grupo Trucos y Consejos.

Trataré en esta entrada de la forma más eficiente de duplicar una hoja de cálculo. En ocasiones, tenemos la necesidad de mostrar solamente una hoja de un libro. Pensemos por ejemplo en el caso de mostrar un informe presente en una hoja de un libro que contiene en las demás los cálculos efectuados, o el caso de enviar por separado a cada responsable hojas de un libro que contiene en cada una el informe para cada división, país,...

Para no entrar en este momento en consideraciones referentes a los vínculos y su posible ruptura, imaginemos que la hoja que queremos mostrar está en valor (por tanto no formulada).

Mi compañero abrió un nuevo libro, copió la hoja haciendo click en el recuadro superior izquierdo de la cuadrícula (lo que selecciona toda la hoja) y pegó en el libro nuevo la hoja que acaban de copiar.

Otra forma de hacerlo sería la de grabar el libro original con otro nombre y eliminar las hojas que no queremos mostrar.

Son opciones aceptables, pero existe una más rápida que nos permitirá hacer lo mismo, o incluso con varias hojas de forma inmediata. Para ello basta hacer click con el botón derecho sobre la pestaña que queremos mover o duplicar. Y ejecutar el comando Mover o copiar…

Este comando nos permite:

Cambiar de lugar una hoja en nuestro libro (esto tiene realmente poco interés ya que se puede hacer arrastrando directamente la pestaña).
Duplicar una hoja y seleccionar al tiempo la posición que ocupará en el mismo libro.
Cortar una hoja de nuestro libro y crear con ella un nuevo libro o pegarla en otro ya existente que tengamos abierto (con lo que desaparece del original).
Hacer una copia de esa hoja que es la que enviamos al nuevo libro o a otro existente ya abierto.

Para ello jugaremos con el desplegable Al libro, y con el check box Crear una copia, que seleccionado o no duplicará la hoja u hojas seleccionadas o simplemente trasladará la original.

Si tenemos seleccionadas varias hojas de nuestro libro, podremos hacer las operaciones que hemos comentado para todas ellas al mismo tiempo.

Si hemos decidido duplicar una de nuestras hojas para pegarla en el mismo libro en el que está la original, esta nueva hoja aparecerá con el nombre de la original seguido de (2). Si la enviamos a un libro nuevo no tendrá esa distinción.

Como podéis ver, se trata de una acción tremendamente fácil y realmente útil en comparación con otros procedimientos más laboriosos. Por ello me parece necesario conocerla.

Tablas Dinámicas 4, Campos Calculados

2011-06-18T12:45:00.000-07:00

Hemos visto varias entradas sobre Tablas Dinámicas en Excel 2003/2007 para presentar y resumir de forma flexible la información de grandes bases de datos.

A la hora de trabajar con tablas dinámicas, podemos necesitar campos adicionales obtenidos combinando datos de la tabla original. Un ejemplo típico de esta situación es el margen de un producto, pedido, proyecto,...

Imaginemos la siguiente base de datos que sirve de origen a nuestra tabla dinámica.

Se trata de información cualitativa y cuantitativa de una serie de pedidos. Uno de esos campos es el margen del pedido. Por su propia naturaleza el margen es un % que no se podrá combinar a la hora de hacer agrupaciones con las posibles opciones que ofrece la herramienta Configuración de campo de valor, presente en la lista de campos.

Cualquier opción que elijamos (suma, promedio, cuenta, máximo, producto,...) no es valida, ya que el margen de cada posible agrupación en la tabla dinámica debe calcularse como combinación de las sumas de beneficio y ventas y eso no se puede construir con esta opción. Imaginemos que configurásemos un promedio para el margen, tendríamos el siguiente resultado:

Esto obviamente es erróneo ya que el margen de una agrupación no es el promedio de los márgenes individuales.

Para construir campos adicionales que combinen la información de la tabla de origen y la presenten de manera adecuada a cualquier combinación que elijamos utilizamos los Campos Calculados.

Para crear el campo Margen (MG) en nuestra tabla dinámica, hacemos click en cualquier celda perteneciente a la tabla dinámica y acudimos a la ficha Herramientas de Tabla Dinámica. Pestaña Opciones y seleccionamos el comando Cálculos (en Excel 2010) o Fórmulas (en Excel 2007).

En la ventana que nos aparece, podremos nombrar el nuevo campo e introducir la fórmula que lo calcula utilizando los datos presentes en la tabla de datos original.

De este modo tendremos el margen correcto para cualquier agrupación que presentemos en la tabla.

Como de costumbre, mi consejo es la experimentación para terminar de sacarle todo el partido a esta utilidad. Eso si, tened en cuenta que podéis crear cualquier campo calculado con una condición, los datos de partida deben estar presentes en la base de datos de origen.

Xcl NT Puzzles

2011-06-12T10:06:00.000-07:00

Os presento la tercera entrada de Xcl NT Tools, una aplicación cuya construcción ha sido todo un reto realmente divertido. Se trata de Xcl NT Puzzles, un juego inspirado en aquellos rompecabezas de plástico con los que jugábamos de pequeños deslizando sus piezas.

Al empezar el juego, aparece la ventana del menú, en ella nos encontramos con los 8 puzzles disponibles. Para seleccionarlos haremos click sobre ellos. Podemos ver qué puzzle está seleccionado en cada momento en la esquina inferior izquierda del menú.

Con el puzzle que queremos resolver seleccionado, pulsamos sobre Jugar y empezaremos el juego. Primero aparecerá el puzzle resuelto que irá desapareciendo para volver a aparecer completamente desordenado. Tras aceptar el mensaje de inicio podemos empezar a desplazar las fichas. Para ello basta con hacer un click sobre la ficha a mover, si está junto al hueco cambiará su posición con él. En caso de pulsar sobre una ficha que no se encuentre junto al hueco no pasará nada.

La puntuación se compone de la combinación de tiempo y desplazamientos. Tras un periodo de cortesía, estas magnitudes comienzan a restar puntos a los 999 puntos iniciales. Una vez terminado el puzzle pulsaremos sobre DONE!. En el caso de que el puzzle no esté resuelto la aplicación nos lo indicará. Si efectivamente hemos terminado el puzzle nos dará la posibilidad de guardar el resultado y/o volver al menú.

De vuelta al menú, tendremos la posibilidad de obtener un informe con las estadísticas guardadas en nuestro Xcl NT Puzzles. Obviamente, es aconsejable tener varias partidas guardadas para que el informe pueda aportar alguna información de interés.

Este informe tiene varios indicadores; en la parte de la izquierda, tenemos la pastilla de popularidad que indica las veces que se ha completado cada puzzle. El promedio de puntuación en cada ronda y una comparativa en un gráfico con doble escala entre el tiempo y los desplazamientos promedios para cada ronda.

Los gráficos de la derecha nos ofrecen la posibilidad de comparar distintos indicadores de las tres magnitudes influyentes en el juego. De forma puntual en el superior y continuada o evolutiva en el inferior.

Como ya sabéis, Xcl NT Puzzles está disponible para descargarse en Xcl NT docs, el sitio que he creado para compartir con quien esté interesado estas pequeñas aplicaciones. Espero que os guste y que compartáis con migo cualquier impresión que queráis transmitir.

Xcl NT Memory

2011-05-09T11:39:00.000-07:00

Hoy pongo a vuestra disposición Xcl NT Memory, la segunda entrega de Xcl NT Tools. Una pequeña aplicación con la que podréis poner a prueba vuestra memoria encontrando parejas de iconos ocultas en un panel. Como en el caso de Xcl NT Places, está disponible para descargarlo en Xcl NT Docs.

La inspiración para esta aplicación viene de un juego que regalé hace algún tiempo a mi sobrina. La puntuación obtenida depende del número de veces que destapas una casilla y del tiempo que tardas en descubrir todas las parejas. Ambas magnitudes vienen representadas por unos velocímetros que indican el tiempo consumido y numero de destapes en su parte inferior.

En principio tenía tres niveles de dificultad, pero cuando ví la facilidad con la que una niña de 6 años terminaba el nivel más difícil, comprendí que debía incluir un nuevo nivel (profesional), más desafiante para las memorias menos deterioradas...

Como en el caso de Xcl NT Places, tras recibir la puntuación obtenida, podrás realizar una serie de acciones como registrar tu puntuación para poder verla en el cuadro de records (que es enunciado de forma sonora), o en el gráfico de evolución de los 25 últimos resultados. También podrás enviarnos un mail directamente desde la aplicación con tu resultado para incluirlo en un futuro ranking.

La idea fundamental es mostrar la capacidad de excel para hacer cosas que la mayoría de usuarios no conoce. Imagina lo que podría ayudar en tu trabajo diario a la hora de automatizar informes si estos pudiesen alimentarse automáticamente, si pudieses mostrar los gráficos previamente seleccionados en pantallas flotantes, hacerlos realmente interactivos de modo que cambiasen en tiempo real o si pudieses hacer notificaciones sin tener que redactar los mails...

Esta es la finalidad fundamental de Xcl NT Memory, obviamente no competir con la PlayStation de Sony o la Xbox de Nintendo... Espero que os genere inquietudes e ideas. Si queréis compartirlas en este blog, serán bienvenidas.

Gráficos con doble escala.

2011-04-03T10:03:00.000-07:00

Una de las primeras cosas que se aprenden a la hora de introducirse en Excel es a hacer gráficos. Es relativamente sencillo y sobre todo desde la aparición de Excel 2007, que incluye nuevos tipos de gráfico y acabados, produce un efecto muy profesional que no hay que desaprovechar.

En la entrada de hoy no vamos a hablar de cómo hacer gráficos, sino de un "truco" que nos permitirá incluir más información en nuestros gráficos y hacerlos más fáciles de interpretar. Se trata de utilizar lo que se conoce con el nombre de Eje secundario.

Si pensamos en un gráfico normal, tendremos dos ejes graduados, el vertical y el horizontal. Es posible añadir un segundo eje vertical para representar en el mismo gráfico una o varias series de datos con una escala diferente a las demás series. Así, para buscar los valores de una parte del gráfico habrá que acudir al eje vertical de la izquierda y para los otros nos fijaremos en el eje vertical de la derecha.

Supongamos la típica tabla de ventas de unidades de un producto, que incluye distintas regiones junto con el total de todas ellas.

Si quisiéramos representar toda esa información en un gráfico, tendríamos el problema de la diferencia de magnitudes entre los valores de las series individuales y el total (suma de todas ellas). Quedando un gráfico como el siguiente:

Resulta evidente que representando todas las series en el mismo eje, se pierde claridad quedando las series individuales muy constreñidas y dificultando la identificación de sus valores concretos en el gráfico.

Utilizando el eje secundario para la representación de la serie Total, podemos mejorar la claridad del gráfico. Para ello, seleccionaremos la serie que queremos representar sobre el eje secundario (en nuestro caso el total de las ventas). Con ella seleccionada nos aparece el complemento de la Cinta de Opciones: Herramientas de gráficos. Acudiremos a la pestaña Presentación y dentro del grupo Selección actual hacemos click en Aplicar formato a la selección tal y como se ve en la siguiente imágen:

Tras esta acción, aparece la ventana Formato de la serie de datos. En Opciones de serie seleccionamos el checkbox Trazar serie en: Eje secundario:

Obtenemos la nueva serie representada en el nuevo eje secundario. En realidad su escala no ha cambiado, sin embargo, la escala de las series individuales se ha visto liberada de la serie Total y por tanto la representación de estas series individuales se puede expandir ocupando toda la superficie del Área de trazado.

Aún no hemos acabado. La serie Total ahora está representada superponiéndose a las individuales, lo que no supone ninguna ventaja sobre la situación anterior. Para solucionarlo volveremos a seleccionar la serie y pulsando sobre el botón derecho acudimos a Dar formato a serie de datos... Manipulando el formato de la serie total la adecuaremos a nuestras necesidades. Aquí la solución está en manos de la creatividad de cada cual, sin embargo propongo estas dos opciones:

Representar la serie total mediante barras (igual que las series individuales), pero con un grosor que coincida con el de todas las series y a una transparencia del 50% de modo que se posible ver la composicion de cada total.

La segunda opción es cambiar el tipo de gráfico para esta serie y representarlo mediante una línea. También le ponemos una transparencia del 50% de modo que no nos oculte información.

Posiblemente incluya más entradas referentes a gráficos: Un basic para su creación, una entrada para gráficos dinámicos, otra para representaciones más elaboradas,... De momento espero que ésta os haya parecido interesante. Personalmente pienso que usar el Eje secundario es de gran utilidad.

Xcl NT Places

2011-02-13T10:03:00.000-08:00

Hoy presento una aplicación en Excel con la que me gustaría mostraros alguna de las posibilidades que ofrecen Excel y VBA para la creación de herramientas ofimáticas. Podéis descargarla en: Xcl NT docs - Xcl NT Places. Se trata de un juego tipo Quiz con el que deberéis acertar 5 poblaciones europeas. Para ello se ofrece un dato inicial que podrá ser completado con 5 pistas y un comodín del 50%.

Cuando terminéis las cuatro rondas de las que consta, recibiréis vuestra puntuación. Podréis guardarla para ver gráficamente la evolución de vuestros resultados y tener presente la máxima puntuación que habéis alcanzado. También podréis enviarnos vuestra puntuación para incluirla en el Ranking que incluiremos a continuación en esta misma entrada. Podréis hacerlo directamente desde Xcl NT Places si disponéis de Outlook o enviándonos un pantallazo a excel.n.cia@gmail.com

El juego utiliza macros en su ejecución así que será necesario habilitarlas (en caso de no estarlo por defecto en vuestro equipo). Para ello, bastará con aceptar la proposición que aparecerá al abrir el libro.

No creo que haga falta decirlo, pero no he incluido ningún virus ni elemento malicioso en documento. Está testeado para que no se produzcan comportamientos anómalos, por lo que he llegado a la conclusión que es una aplicación estable. No obstante, si detectáis alguna incidencia con ella os agradecería enormemente que me la hiciéseis llegar. Lo mismo digo para cualquier comentario, solicitud, queja, corrección de la información que contiene...

El libro está protegido para evitar su modificación, pero como ya he comentado en el blog excel no es una aplicación 100% segura. Cualquier modificación del contenido o su código no tiene mi consentimiento. Podéis estar seguros de haceros con el documento original en Xcl NT docs, el sitio que he creado para compartir con vosotros mis archivos.

Mejoras VS ¿Mejoras?

2010-11-28T13:49:00.000-08:00

Hace unos meses comenté las novedades que aportaba en nuevo Excel 2010 en la entrada Excel 2010 vs Excel 2007. En aquellos momentos circulaba la versión Beta, ahora llevo unos meses utilizando la definitiva versión profesional.

Comenté entonces que la evolución era menor que la que se había producido en el paso de Excel 2003 a Excel 2007. Hay mejoras significativas que ya se expusieron en aquella entrada. Sin embargo en esta ocasión me gustaría mostrar mi perplejidad por unas novedades que sinceramente no sé qué es lo que aportan.

=CONSULTAV(...;...;...;...)

Al poco de estrenar mi flamante Excel 2010, tuve que utilizar la archiconocida fórmula =BUSCARV(...;...;...;...). Como comentaba en la entrada Basics 1; BUSCARV es una de las primeras fórmulas en aprenderse por su gran utilidad. En caso de desconocer su funcionamiento, os remito a aquella entrada.

El nuevo Excel 2010 me tuvo un buen rato perplejo intentando averiguar por qué una fórmula tan elemental me daba error. Revisé una y otra vez que era lo que podía ocurrir hasta que descubrí que simplemente había dejado de existir.

=BUSCARV(...;...;...;...) ahora es CONSULTAV(...;...;...;...).

=MED(...;...;...)

Otro caso igual que el anterior es la desaparición de =EXTRAER(...;...;...), esta fórmula también ha desaparecido para pasar a denominarse MED(...;...;...), y del mismo modo, la única diferencia es el nombre.

Ambas sirven para extraer un conjunto de caracteres de una cadena de texto o número a partir de un lugar predeterminado. Si ahora utilizamos la antigua EXTRAER(...;...;...) el nuevo Excel 2010 no nos la reconocerá dando error.

Aún no se cuál era la necesidad de estos cambios, ni la mejora que proporcionan. De todos modos, todo hay que decirlo, una vez que te das cuenta el trastorno es nulo.

Xcl NT; Mailings Personalizados

2010-09-08T12:17:00.001-07:00

Con Excel y VBA es posible construir herramientas para el envío de mailings a un gran número de destinatarios en segundos, simplemente con la ejecución de una macro.

No sólo podemos construir una carta tipo para todos; También podemos personalizarlas en función de los criterios que deseemos. Podemos adjuntar uno o varios archivos comunes o individualizados para cada receptor...

Envíe campañas promocionales a cientos de destinatarios en segundos. Reclame sus facturas pendientes de cobro a sus clientes adjuntándolas en formato .pdf. Reparta de forma inmediata a múltiples personas invitaciones, documentos, notificaciones,...

Con Excel y VBA podrá además crear aplicaciones que realicen seguimientos y reenvíos en función de las circunstancias específicas que usted establezca. Plantéenos su idea sin compromiso, se la construimos a medida.

Xcl NT; Herramientas de Reporting

2010-09-01T09:20:00.000-07:00

La distinta localización física de las filiales o sucursales de una empresa, o la existencia de diferentes empresas pertenecientes al mismo grupo con un cierto grado de autonomía administrativa, requieren de una herramienta para la centralización y consolidación de la información. Esta actividad es lo que comúnmente conocemos como Reporting.

La información objeto de Reporting suele ser información agregada presente en el Sistema de Gestión / Enterprise Resource Planning (ERP) o también puede ser información elaborada expresamente por fuera de los sistemas con el objeto de ser consolidada en la central y así tener la información relevante de todas las unidades.

Es habitual y según los casos muy recomendable la adquisición de una herramienta de reporting. Sin embargo esta necesidad puede ser del mismo modo satisfecha utilizando las herramientas de las que disponemos.

Se puede construir con Excel y VBA una herramienta para la introducción de la información según el formato determinado por los usuarios finales. Extraer con dicha herramienta un archivo cifrado que se envía a la central donde se invertirá el proceso para consolidar la información. Del mismo modo, se podrá almacenar el histórico tanto individual como agregado para su utilización en informes y análisis.

Si la disponibilidad presupuestaria o las necesidades de la empresa no aconsejan la adquisición de un paquete de reporting. Excel y VBA son buenas herramientas para construirlo.

Xcl NT; Cálculo y Presentación del Cuadro de Mando Integral / Balanced Scorecard

2010-08-31T09:46:00.000-07:00

Existen en el mercado numerosas aplicaciones y plantillas para la representación de un Cuadro de Mando Integral / Balanced Scorecard. Permiten visualizar los Key Performance Indicators (KPI's) y conservar el histórico de los mismos. Sin embargo, esta es la última fase de lo que representa la herramienta del CMI.

El CMI por su propia definición es único para cada empresa ya que expresa y monitoriza la estrategia de la misma, influida por sus características y condicionantes internos y externos. Es por tanto imposible establecer un CMI aplicable a dos empresas.

Añadida a la exclusividad de su concepción, el proceso de construcción de un CMI también es único para cada empresa; depende de la Organización de la propia empresa, sus Herramientas de Gestión, su Forma de Trabajo,...

Una vez hecho el ejercicio de diseño del CMI coherente con la estrategia de la empresa, la aportación de Valor Añadido realmente significativa en su construcción y utilización continuada es la obtención y tratamiento periódico de la información. Esta actividad fundamental, en la inmensa mayoría de los casos, inevitablemente va ligada a Excel.

Se puede homogeneizar la información, tratarla, completarla y analizarla utilizando Excel y VBA de forma automática. Esta es la parte más costos en términos de tiempo de trabajo, en la elaboración y utilización en el tiempo de un CMI. Y es precisamente la parte que no se puede estandarizar al ser un proceso único para cada empresa.

Posteriormente con Excel y VBA podemos proporcionar cualquier representación gráfica (semáforos, velocímetros, termómetros,...) y su almacenaje para su utilización sobre demanda libre o limitada por criterios de privilegio en la confidencialidad.

Ponemos a su disposición nuestro conocimiento técnico en Excel y VBA, para la construcción de la herramienta tanto en lo referente a su representación como a la automatización de su cálculo. A esto añadimos nuestra experiencia en la implementación de este tipo de herramientas en distintas empresas.

Xcl NT; Control de Hitos y Coordinación de Equipos

2010-08-30T09:00:00.000-07:00

Una necesidad recurrente en la empresa consiste en la coordinación de actividades y seguimiento de su cumplimiento.

Como ejemplos de esta actividad podemos citar entre otros:

Proyectos en los que hay que cumplir con determinados hitos o milestones en su ejecución, y cuya omisión implica penalización económica.

Envío de documentación referente a Riesgos Laborales para hacer trabajos en otras empresas, y cuya falta implica la imposibilidad de realizar dichos trabajos hasta que se subsane.

Solicitud de determinada documentación a nuestros clientes para poder facturar y cobrar como Actas de Recepción o Aceptación, Documentos de Homologación, etc. Cuya omisión implica retrasos en el cobro.

Imaginemos un coordinador con alguna de estas necesidades. Este Coordinador o Project Leader tendrá controlados en su agenda las fechas clave e intentará asegurar con los Responsables que cada hito se cumple según lo previsto. Ahora imaginemos que ese coordinador es responsable de 40 proyectos distintos. Su vida puede resultar realmente complicada.

Nuestra propuesta ante esta necesidad es una aplicación que ejerza de Coordinador. Dicha aplicación nos envía un mail personalizado, tanto a nosotros como a cada uno de los responsables, alertando de las actuaciones requeridas.

La capacidad de Excel y VBA para automatizar tareas y ejecutarlas de forma precisa hace que sea indiferente llevar 2 proyectos que 200. Con la antelación que deseemos, se generarán los avisos necesarios para que ningún hito se nos pase por alto.

Xcl NT; Reconstrucción de Modelos en Excel

2010-08-25T08:45:00.000-07:00

Prácticamente todos los que hemos pasado por distintos departamentos de administración hemos tenido la vivencia de tener que trabajar con hojas de Excel heredadas de usuarios que a su vez heredaron de otros sin que haya una visión clara de su funcionamiento, partes o fiabilidad.

Dichas hojas, que seguramente fueron una buena solución en un principio, al pasar por varios usuarios han sido ampliadas y retocadas perdiendo el enfoque. Posiblemente han quedado obsoletas en parte, lo que se suple con parches y correcciones añadidas. El paso del tiempo tiende a empeorar la situación. La acumulación de parches, se convierten en un auténtico suplicio para el encargado de lidiar con ellas. Ciertamente lo más conveniente sería construirlas desde cero, pero no hay nadie en la empresa que comprenda la hoja, disponga del tiempo o los conocimientos técnicos necesarios para hacerlo.

Partiendo de la información disponible, con el objetivo en mente de lo que se pretende conseguir y disponiendo de los conocimientos necesarios de Excel y VBA, es relativamente sencillo analizar los parches intermedios para reconstruir el modelo, o incluso hacer un nuevo análisis de la necesidad y elaborar una nueva aplicación desde cero. En ambos casos ganaremos en fiabilidad y rapidez.

Xcl NT; Cálculo Automático y Presentación de Informes

2010-08-24T10:02:00.000-07:00

La necesidad de elaboración de informes es universal en toda la empresa y de manera especial en los departamentos de Administración y Finanzas.

Para los decisores, disponer de la información precisa en tiempo y forma es fundamental para poder tomar las decisiones acertadas. Esto hace que haya periodos de tiempo (aproximadamente una semana completa con periodicidad mensual) en los que la carga de trabajo aumenta de forma considerable. Varias personas dedican la mayor parte de su tiempo a la extracción y elaboración de la información.

Dependiendo del grado de automatización de estas tareas, la elaboración de estos informes generalmente sujetos a plazos de entrega ajustados suponen no sólo un coste de oportunidad en lo referente al tiempo, sino también un coste de credibilidad y bienestar debido a la incertidumbre en cuanto a la presencia de errores y sobrestress asociado.

La utilización de macros, creación de formulaciones específicas mediante VBA y en general el aprovechamiento de las capacidades en cuanto a automatización y cálculo de Excel permiten la obtención de cualquier informe de manera automática. Informes de Ventas, P&L's desagregadas, Informes de Stock, de Deuda Vencida, Working Capital, Cash Flow,... Cualquier informe se puede automatizar ganando en tiempo y fiabilidad.

El trabajo de horas, e incluso días, dedicado a la elaboración de informes se puede sustituir por la inmediatez y exactitud de un proceso automático que trata y presenta la información según las necesidades de la empresa.

Por último, las posibilidades de presentación son prácticamente ilimitadas en función de la voluntad del usuario final. Existiendo la posibilidad de crear informes interactivos que muestren diferente información según las necesidades o privilegios en cuanto a confidencialidad del usuario que los utiliza.

Xcl NT; Homogeneización de Bases de Datos

2010-08-23T08:10:00.000-07:00

Todos nos hemos encontrado en alguna ocasión con la necesidad de homogeneizar una Base de Datos que al haber sido generada a lo largo del tiempo por distintos usuarios y con criterios distintos, no presenta un formato homogéneo.

En un momento dado, con la creación de un nuevo desarrollo o aplicación, es necesaria la introducción de la información presente en nuestro histórico en un formato único del que no se dispone. Presencia indistinta de mayúsculas y minúsculas, abreviaturas, espacios, guiones, barras,...

No disponer de las herramientas necesarias para homogeneizar la información, implica horas de tedioso trabajo manual y lo que es peor, riesgo de error que puede llegar a comprometer la efectividad del nuevo proceso.

En Xcl NT disponemos de herramientas que facilitan la homogeneización de estas Bases de Datos ganando en tiempo y seguridad.

Xcl NT; Automatización y Tratamiento de Información

2010-08-19T09:03:00.000-07:00

Evite horas de monótono trabajo manual con su correspondiente riesgo de error por fatiga. Sustitúyalo por la ejecución de una macro en Excel, que en segundos le permitirá dedicar ese tiempo a lo realmente importante.

Es prácticamente generalizada la necesidad de tratar datos con el objeto de obtener un formato determinado o para obtener una información agregada que no proporciona nuestro sistema de gestión o ERP. Bien porque la necesidad apareció después de configurarse o porque el propio sistema no dispone de esa funcionalidad.

Es muy probable que nos encontremos ante un proceso tedioso en el que hay que bajar a Excel los datos, ya sea directamente o mediante una interface. Una vez bajados, esos datos están dispuestos de modo en que hay que trabajar con ellos para que tengan el formato adecuado. Posteriormente, es posible que haya que tratar los datos añadiendo información no presente según distintos criterios o cruzando distintas fuentes.

Cualquier necesidad de formateo y elaboración de información puede ser resuelta utilizando Excel y VBA. Las ganancias en tiempo y precisión superan con creces su pequeño coste.

Xcl NT; Aplicaciones Específicas.

2010-08-18T07:19:00.000-07:00

Las aplicaciones genéricas ofrecen soluciones genéricas basadas en un cliente tipo o promedio. En la mayoría de los casos, estas soluciones genéricas no se adaptan a las necesidades específicas de cada empresa.

Para Xcl NT el cliente tipo no existe. De hecho, no existen dos clientes iguales por lo que no existen dos necesidades iguales. Queremos conocerle, saber qué necesita y proporcionárselo de manera precisa, rápida y al menor coste.

Gracias a las Nuevas Tecnologías es posible satisfacer micro-mercados, el objetivo es vender más de menos. Cada cliente obtiene exactamente lo que necesita a un coste razonable al utilizarse las herramientas de las que ya dispone. Los conceptos tradicionales de coste de producción y distribución pierden su significado. Los costes de aprendizaje por parte del usuario prácticamente se eliminan al utilizar herramientas a las que está habitualdo.

Imagine lo que podría hacer si dispusiese de una herramienta asequible que le permitiese sortear sus dificultades. En Xcl NT podemos proporcionársela.

Xcl NT, Optimización de Procesos Ofimáticos

2010-08-16T08:43:00.000-07:00

Potencie la capacidad de su administración añadiendo funcionalidades o automatizando procesos, con las herramientas de las que dispone y adaptándose de forma absoluta a las necesidades de su empresa.

Excel y su lenguaje de programación incorporado VBA (Visual Basic para Aplicaciones) permiten obtener de manera rápida y al menor coste la solución a cualquier necesidad en cuanto a:

Automatización de procesos y tareas rutinarias.
Control de Actividades, hitos y envío de avisos.
Cálculo automático y preciso de cualquier tipo de informe.
Filtrado, tratamiento, análisis y presentación de datos.
Reformulación de modelos obsoletos o deteriorados.
Creación de aplicaciones específicas,...

Una amplia experiencia en departamentos Financieros y de Administración de diferentes multinacionales cimentan una convicción: El aprovechamiento de las herramientas informáticas y en concreto de Excel y VBA es lo que marca la diferencia en términos de eficiencia y productividad.

Informes periódicos que requieren de múltiples operaciones tediosas y repetitivas. Corrección manual de gran cantidad de registros con un elevado riesgo de error asociado. Formateo y preparación de la información importada de nuestro ERP. Necesidades de tratamiento, análisis y uso de la información para las que el mercado no dispone de soluciones y cuya carencia supone horas de trabajo. Todo esto se puede solventar de manera rápida y económica utilizando el software presente de manera universal: Microsoft Office.

Según nuestra experiencia profesional, muchos cambios de ERP o adquisiciones de CRM se han realizado con la única motivación de obtener algunas funcionalidades que perfectamente se podían conseguir utilizando aquellas herramientas de las que ya se dispone.

En este sentido, Microsoft Office y la programación en VBA ofrecen inagotables oportunidades para multiplicar la productividad de nuestros sistemas. En el mercado encontramos todo tipo de herramientas, para gestión de tesorería, gestión de stocks, facturación, planificación de actividades, elaboración de Cuadros de Mando,... Es posible que su adquisición sea una decisión acertada. Sin embargo, antes de embarcarse en ellas, compruebe que lo que necesita no está en sus manos.

Le presentamos una pequeña muestra de soluciones representativa de lo que se puede hacer aprovechando el potencial de Microsoft Office. No son productos cerrados ya que no pretendemos decirle lo que le queremos vender. Esperamos que usted nos cuente lo que nos quiere comprar.

Imágenes Integradas en la Cuadrícula

2010-08-10T09:30:00.000-07:00

En determinados casos puede interesarnos mostrar una imagen como fondo de la cuadrícula en la que trabajamos. Es poco habitual para el trabajo diario en una hoja de cálculo, pero puede ser interesante cuando hacemos una aplicación para terceras personas, o si vamos a realizar una presentación con nuestra hoja de cálculo.

Este procedimiento en Excel 2007 es muy sencillo. Basta con ejecutar el icono Fondo que se sitúa en la cinta de opciones dentro de la pestaña Diseño de página. El icono en cuestión está dentro del grupo de comandos Configurar Página. El procedimiento en Excel 2003 es igualmente sencillo. Basta con ir al menú Formato / Hoja / Fondo y continuar con el resto del mismo modo.

Al ejecutar este comando nos aparece inmediatamente la ventana Fondo de Hoja en la que podremos explorar en nuestras carpetas hasta localizar y seleccionar la imagen deseada.

Con un doble click sobre la misma o con un solo click y presionando el botón insertar, aparecerá en la superficie cuadriculada de nuestra hoja de cálculo.

Es aconsejable tratar las imágenes de modo que aumente la transparencia de las mismas. De este modo facilitará la lectura del contenido de las celdas.

No hay que confundir esta acción con la de insertar una imagen, en este caso no podremos escribir en la superficie ocupada por la propia imagen.

Trucos y Consejos 5; Atajos Adicionales

2010-08-09T09:27:00.000-07:00

En la entrada Trucos y Consejos I, hicimos referencia a los shortcuts o atajos más habituales. Obviamente no son los únicos. Hay múltiples, tantos que es poco práctico tratar de aprenderlos todos. No obstante, para aquellas personas habituadas a trabajar con el teclado es muy útil aprender los más utilizados.

Para descubrir los shortcuts o atajos de Excel en la versión 2007 basta con situar el cursor en cada uno de los iconos de la Cinta de Opciones. Inmediatamente aparecerá una nota con la función del icono y la manera de ejecutarlo a través del teclado si está disponible.

Existen otro tipo de atajos para trabajar con la cinta de opciones. Se muestran manteniendo pulsada la tecla Alt.

Pulsando Alt mas la tecla correspondiente a la Ficha de la Cinta de Opciones con la que queremos trabajar aparecen las claves a pulsar para cada comando.

Personalmente este es un método que no me resulta cómodo, da la sensación de estar jugando a la Play Station, infinitos botones que pulsar para cada opción. Creo más bien que se trata de un método de emergencia para poder trabajar sin ratón.

Generalmente suelo utilizar atajos para las funciones principales, y al resto acudo con el ratón. Unos shortcuts adicionales que creo conveniente conocer son:

Pegado especial: Es un atajo que me produjo gran desconsuelo cuando empezé a trabajar con Excel 2007; había desaparecido!!!. En Excel 2003 el atajo era Ctrl+Alt+E, combinación que desapareció y que con gran alivio descubrí que sólo había cambiado a Ctrl+Alt+V. Este atajo es muy útil seleccionar rápidamente si queremos pegar sólo fórmulas, valores, formatos,... Lógicamente, si queremos pegar todo basta con utilizar Ctrl+V.

Lo realmente práctico de este atajo es que no sólo ahorras hacer varios clicks para llegar a mostrar la ventana de pegado especial, sino que además te puedes mover por ella con el cursor y la tecla TAB. Con ello se sigue ganando tiempo de apuntar y clickar con el ratón.

Alt+º Muestra las fórmulas presentes en una hoja. Esto es conveniente si queremos hacer una revisión de las fórmulas de un libro al evitar tener que visualizar una por una.

Volviendo a pulsar sobre el mismo shortcut volveremos a ver los valores.

Mostrar lista de Macros; Alt+F8. En el caso de no tener iconos asignados a nuestras macros en la barra de acceso rápido, nos muestra el cuadro con las macros disponibles para ese libro.

Por último de esta serie, Buscar y Reemplazar: Ctrl+L. Con el podemos encontrar textos determinados y reemplazarlos por otros. Por ejemplo para reformular hojas con libros vinculados que se van actualizando cada mes.

Como comentaba, el número de shortcuts o atajos es ingente, lo mejor es buscar las acciones que más utilizamos y comprobar si hay atajo asociado a las mismas. Al principio puede costar un poco, pero en poco tiempo compensa.

Mostrar y Ocultar Imágenes

2010-05-14T20:55:00.000-07:00

En principio no es .para lo que está .pensado Excel, pero es posible que deseemos mostrar imágenes en una de nuestras hojas. Ante esto tenemos varias opciones.

La opción más sencilla podría ser copiar la imagen y pegarla tal cual, el principal inconveniente es que en el caso de necesitar varias imágenes nos encontraríamos con dificultades ya que probablemente no tuviésemos suficiente espacio visible como para organizar todas las imágenes de manera ordenada. Obligando así a desplazarnos por la hoja, lo que podría llegar a ser bastante tedioso.

El caso anterior se podría solucionar con un hipervínculo que nos llevase a otra pestaña tal y como vimos en la entrada Hipervínculos. Una vez visualizada la imagen, incluiríamos otro hipervínculo a la pestaña original.

De estos casos más sencillos podríamos pasar a otros quizá demasiado complejos (aunque probablemente óptimos), utilizando Visual Basic para mostrar las imágenes en un User Form y almacenando las imágenes fuera de Excel para evitar hacerlo demasiado pesado. Esto sería tema para otra futura entrada.

Pero una manera relativamente sencilla y bastante espectacular es hacerlo a través de los Comentarios. Si disponemos de Excel 2007 (o incluso Excel 2010) podemos utilizar estos elementos para poder visualizar la imagen cada vez que pongamos el cursor sobre la celda que contiene el comentario (aparecerá automáticamente), y una vez que alejamos el cursor de la celda la imagen desaparecerá por sí sola.

El proceso a seguir es el siguiente:

Sabiendo la celda en la que queremos que aparézcala imagen al pasar el cursor por ella. Vamos a la cinta de opciones, en la pestaña Revisar y dentro del área Comentarios pulsamos sobre el icono Nuevo comentario.

Hecho esto nos aparece el típico cuadro de comentario en el que estará presente el nombre del usuario. Nosotros borraremos todo el contenido del comentario y haremos click en el borde del comentario con el botón derecho para acceder al menú contextual.

Elegiremos la opción Formato del comentario.

Esto abrirá una ventana con distintas pestañas que nos permitirían modificar el aspecto por defecto del comentario.

En nuestro caso, la opción que nos interesa es la opción Relleno dentro de la pestaña Colores y líneas.

Expandiendo el desplegable del campo color, elegiremos la opción Efectos de relleno…

Esto nos abre una nueva ventana con cuatro pestañas. En la pestaña Imagen, podremos seleccionar la imagen que vamos a importar.

No creo que haga falta detenerse en el proceso de buscar la imagen en su ubicación, es una acción muy usual.

Una vez seleccionada la imagen a incluir en nuestro comentario, la pestaña imagen de nuestra ventana queda con el aspecto siguiente.

Aceptando cuando corresponde, obtenemos la imagen en nuestro comentario. Será necesario modificar el tamaño del comentario para hacerlo coincidir con las proporciones adecuadas para la imagen.

Para ello colocamos el cursor en los círculos presentes en los bordes del comentario y arrastramos según nos convenga. En este caso basta con agrandar la foto en altura para lo que arrastramos el punto central del borde inferior del comentario.

El resultado final, repitiendo el proceso tantas veces como sea necesario, podemos apreciarlo en el video siguiente. Obviamente es una simplificación, pensemos en las utilidades que tendría para un profesor a la hora de pasar lista en clase, un maestro de artículos en el que podemos ver la imagen del producto, un directorio de empleados,…

Seguridad en Excel; Proteger Celdas

2010-03-10T09:50:00.000-08:00

En una. de- las- empresas -para- las -que he- trabajado, era -el encargado -de coordinar el reporting de una unidad de negocio de una multinacional. Ocasionalmente tenía que recopilar información de los distintos países para lo que creaba un formato en Excel que debían cumplimentar y devolver.

Por supuesto, pensaba que sesudos controllers no tendrían dificultades en rellenar unos simples formatos y devolverlos de modo que pudiese automatizar la consolidación de datos de manera sencilla.

Fue entonces cuando descubrí que la capacidad creativa de un controller no tiene límites, incluir columnas o filas variando la estructura del formato o introducir información relevante en celdas fuera del mismo fueron cosas de cuya insospechada existencia tuve constancia.

Para evitar estas situaciones es para lo que utilizamos el bloqueo de celdas. Podemos construir un formato en Excel bloqueando todas aquellas celdas en las que no debe introducirse información, dejando exclusivamente las apropiadas. No nos garantiza nada, después de eso a mí me han enviado las cifras redactándolas por email… pero ciertamente limita las posibilidades de los saboteadores y si además gozamos de la autoridad suficiente para imponer el envío de los datos de una determinada forma, habremos conseguido tener una materia prima homogénea para nuestra consolidación.

Supongamos que tenemos una simple tabla de datos en la que queremos que distintas personas rellenen una serie de celdas con fondo amarillo. En la misma tabla habrá otro tipo de celdas con fondo gris que serán totales o datos dados que no se deben modificar. Por otro lado, también debemos evitar la escritura del resto de superficie de la hoja, evitando así que datos que deben entrar en los cálculos se sitúen fuera del formato construido.

El primer paso a la hora de bloquear celdas para escritura es seleccionar todas aquellas celdas que deben poderse editar. Con ellas seleccionadas, acudiremos a la ventana Formato de celda, para ello abrimos el menú Contextual con el botón derecho y seleccionamos la opción Formato de celda.

Acudimos a la pestaña Proteger. Vemos que hay dos chek boxes disponibles, uno de ellos seleccionado por defecto (Bloqueada). Debemos des-seleccionar ese check box tal y como muestra la imagen.

Una vez hecho esto acudimos a la ficha revisar, y dentro del grupo de comandos Cambios de la cinta de opciones ejecutamos Proteger hoja. No nos entretendremos con ello ya que podéis consultar el procedimiento detallado en la entrada Seguridad en Excel; Proteger Libros y Hojas.

El resultado es que podremos introducir datos en las hojas habilitadas a tal efecto y si intentamos introducir información en las celdas bloqueadas nos aparecerá un mensaje impidiéndolo tal y como podemos observar en el vídeo.

Resumiendo el mensaje nos alerta de que la celda está protegida y no se puede escribir salvo que se introduzca la contraseña.

Del mismo modo, es aconsejable proteger la estructura del libro para evitar sorpresas. El procedimiento para proteger el libro lo podéis encontrar también en la entrada Seguridad en Excel; Proteger Libros y Hojas.

Seguridad en Excel; Cifrar un libro

2010-03-09T09:54:00.000-08:00

Es .muy- habitual, -sobre- todo- a- aquellos- de nosotros. que .trabajamos. con. información financiera sensible de una empresa, que necesitemos proteger un libro de Excel mediante una clave que sea necesaria introducir para poder abrir el libro.

A este respecto podemos hacer muchas disquisiciones sobre la seguridad de Excel. La verdad es que por lo que sé y por lo que he podido leer, Excel no es una aplicación infalible en cuanto a seguridad. Existen herramientas en el mercado que podrían sobrepasar sus medidas de seguridad y ciertos trucos (que no desvelare...) que pueden burlar su Excel.

Hay empresas .como. por .ejemplo .Bancos y. Grandes .Multinacionales que disponen de un dispositivo parecido a un reloj que proporciona una clave de acceso a sus sistemas con una vigencia fugaz. Esto quiere decir que tras un lapso de tiempo reducido (pongamos 30 segundos) cambia la clave, lo que prácticamente elimina la posibilidad de actuación de los software generadores de contraseñas.

No obstante, podemos considerar como aceptable la seguridad standard frente a un posible elemento malintencionado con los conocimientos y herramientas disponibles para la inmensa mayoría de usuarios.

Una medida de seguridad que podemos aplicar (entre otras que incluimos en este conjunto de entradas sobre seguridad) es la de Cifrar el documento de modo que nos solicite una contraseña.

Supongamos un libro en las que tenemos claves de acceso de distintos usuarios.

Con el libro abierto desplegamos el botón de Office y sobre el conjunto de opciones correspondiente a Preparar, seleccionamos la herramienta Cifrar libro, representada por un candado y una llave.

Tras esto nos aparece un cuadro en el que se pide la introducción de una contraseña. Es conveniente que la contraseña sea lo suficientemente larga, disponga de números y combinación de Mayúsculas y Minúsculas (las normas habituales para contraseñas seguras).

Tras introducir la contraseña y pulsar Enter o Aceptar, nos vuelve a salir otro cuadro pidiendo la repetición de la contraseña. Esto sirve para evitar errores en la introducción de la contraseña la primera vez ya que no visualizaremos lo que escribimos si no puntos y si por error pulsamos una tecla imprevista, abrir el documento sería casi tan difícil como si no conociésemos la contraseña.

En este momento podemos cerrar el libro guardando los cambios y la próxima vez que pretendamos acceder a ese documento nos pedirá la contraseña antes de mostrar nada del mismo como se aprecia en la imagen inferior.

Si no introducimos correctamente la clave, aparecerá un mensaje en el que se nos informa de que la clave no es correcta y amablemente presuponiendo que ha habido un error y no mala intención…

Seguridad Excel; Ocultar Datos

2010-03-08T10:13:00.000-08:00

Otra .medida. para. incrementar la seguridad y confidencialidad .en nuestras. hojas es la de hacer invisible cierta información.

Supongamos que tenemos un área de nuestra tabla de datos que participa en la hoja que hemos construido. Podrían ser las opciones de una lista de validación, un desplegable o formulario, o datos utilizados en fórmulas. De forma inmediata podemos arrastrar dicha área lejos de la información que va a centrar la atención y hacerla desaparecer poniéndole un color de fuente blanco (suponiendo claro, que el fondo de esas celdas sea blanco).

Otra forma de de esconder datos se consigue ocultando las filas o columnas en las que se encuentran esos datos. Tal y como comentábamos en la entrada Desde 0; 5 Editar un libro en Excel basta con seleccionar las filas o columnas a ocultar y desplegar el menú contextual con el botón derecho para ejecutar el comando Ocultar.

Hay que tener en cuenta que será visible el salto en los encabezados de fila y columna, por lo que si no protegemos la estructura del libro será muy fácil volver a mostrar esas filas o columnas. Por otro lado, tenemos que tener cuidado de no tener gráficos referenciados a estas celdas, ya que si es así, el gráfico desaparecerá.

Del mismo modo que ocultamos filas o columnas podemos ocultar hojas. Esto también lo vimos en la entrada Desde 0; 5 Editar un libro en Excel. El procedimiento es idéntico, salvo que hay que desplegar el menú contextual sobre la pestaña de la hoja. Sin embargo, la información oculta de este modo también se puede rastrear. Ya que desplegando el menú contextual en las pestañas restantes nos aparecerá la opción de Mostrar y si la ejecutamos veremos la lista de pestañas ocultas.

Bastaría con ejecutar Mostrar y elegir la hoja a mostrar en la siguiente ventana.

Una forma de evitar que nuestro usuario indiscreto pudiese mostrar la hoja sería protegiendo el libro tal y como vimos en la entrada Proteger Libros y hojas… de este modo sabría que hay información oculta pero no podría acceder a ella.

Por último, existe una forma más sofisticada de evitar que la hoja que acabamos de ocultar aparezca en dicha lista y por lo tanto no sea posible seguirle el rastro. Para ello tendremos que acudir al editor de Visual Basic.

Ya explicamos en la entrada Tips & Tricks IV cómo incluir la ficha programación en nuestra cinta de opciones. Otra forma de acceder al Editor de VBA es a través de la barra de herramientas de acceso rápido, tal y como vimos en la entrada Tips & Tricks IV.

No empezaremos (aún) a adentrarnos en VBA pero sí que podemos acceder sin requerir mayores conocimientos a la ventana de propiedades de la hoja que queremos ocultar y cambiar la última propiedad Visible que por defecto viene como - 1 – xlSheetVisible y cambiarla por -2- xlSheetVeriHidden.

De este modo, la hoja desaparecerá de la zona de pestañas y no tendremos pistas sobre su existencia en el menú contextual.

La única manera de mostrarla de nuevo será acudiendo al editor de VBA y volviendo a cambiar la propiedad visible de la hoja.

Seguridad Excel; Ocultar fórmulas

2010-03-03T10:39:00.000-08:00

En esta .entrada sigo comentando unos pocos trucos que nos permitirán .ocultar y proteger la información si nos encontramos con la necesidad de que terceras personas no tengan acceso a ciertos datos o a la forma en que están construidas nuestras hojas.

La seguridad de Excel utilizando medidas estándar no se puede decir que sea infalible, si bien es aceptable cuando se plantea para proteger nuestras hojas del 99,9% de los usuarios ofimáticos.

Adicionalmente, existen formas de protección en Excel que requieren de conocimientos a nivel de Visual Basic para Aplicaciones, con ellas se puede impedir duplicar un libro, hacer que sólo sea posible abrir un libro en determinados equipos, insertar fechas a partir de las cuales no sea posible abrir el libro,… Pero para llegar a ellas primero hay que conocer el código VBA. Tengo previsto incluir más entradas sobre VBA en el blog, pero todo requiere un orden, y aún hay mucho de lo que hablar en cuanto a la propia interface de Excel.

En esta entrada mostraremos cómo ocultar una fórmula. De este modo lo que aparecerá en la celda será el resultado pero no la fórmula que lo origina. En la imágen de abajo vemos como al situarnos sobre una celda vemos la fórmula que proporciona el resultado en la barra de fórmulas.

Para ello tenemos que acudir a la pestaña Proteger de la ventana Formato de celda.

En la pestaña Proteger nos encontramos con dos check box, Bloqueada y Oculta. La opción Bloqueada es precisa para impedir que ciertas celdas o toda una hoja sean editables. Hablamos sobre ello en la entrada Seguridad Excel; Proteger hojas y libros. El check box Oculta nos permitirá impedir que aparezca la fórmula que produce el resultado visible en la barra de fórmulas. Para ello debemos activar dicho check box. Tras esto acudimos a la ficha Revisar y ejecutamos el comando Proteger hojas.

Volviendo a colocarnos sobre la celda en la que activamos el check box Oculta, comprobamos que la fórmula ha desaparecido de la barra de fórmulas siendo visible únicamente el resultado.

Si intentamos cambiar la información de dicha celda nos aparecerá un mensaje indicando que no es posible. Esto se debe a que a la vez que el check box Oculta, activamos el Bloqueada. Si quisiéramos que esas celdas fueran editables debemos desactivar ese check box al inicio del procedimiento. En este caso podríamos cambiar el contenido de la celda pero seguiríamos sin ver la fórmula.

Seguridad Excel; Proteger hojas y libros

2010-03-01T23:26:00.000-08:00

Voy a dedicar unas pocas entradas al tema de la seguridad en Excel. Hay que decir que en principio Excel es aplicación infalible en cuanto a seguidad. Sin embargo, posee unas herramientas incorporadas en la interface y podemos construir otras a nivel de VBA que pueden hacer que consigamos un nivel de tranquilidad bastante aceptable.

Empezaré con la manera más simple proteger nuestros libros, si bien no de mantenerlos secretos. Para este modo de protección utilizaremos los comandos de la cinta de opciones proteger hoja y proteger libro.

Con el comando proteger hoja impediremos que otros usuarios accidental o intencionadamente cambien el contenido de nuestra hoja. Hay que tener en cuenta que esta opción no proporciona confidencialidad de los datos ya que los usuarios pueden ver la información que contiene el libro. Solamente no pueden hacer modificaciones en el mismo.

Pulsando el icono proteger hoja en la pestaña que queremos proteger nos aparecerá una ventana en la que podemos seleccionar que elementos de la hoja queremos proteger. Por defecto aparecen las dos primeras que son las que abarcan todo. Además opcionalmente podemos introducir una contraseña. Es aconsejable hacerlo ya que si no, un usuario podría desproteger el libro por casualidad. Bastaría con pulsar el icono Desproteger hoja (el mismo que utilizamos para protegerla pero cambiado de nombre) y pulsar Enter o Aceptar.

Tras pulsar Enter o Aceptar nos pedirá introducir de nuevo la contraseña por los motivos que ya indicamos en la entrada Cifrar Libro. Aquí son aplicables también las sugerencias en cuanto al establecimiento de contraseñas.

Una vez aceptada esta contraseña y grabada la modificación, cualquier intento de modificar la hoja protegida hará que aparezca el siguiente mensaje.

Es importante proteger el libro, no solo sus hojas. Esto es debido a que si no protegemos el libro (al menos su estructura), cualquier usuario podría eliminar la hoja protegida, lo que sería tan perjudicial como si cambiaran los datos.

Para proteger el libro ejecutaremos el comando Proteger libro. Situado como veíamos al principio en la cinta de opciones de la pestaña Revisar, y el grupo de acciones Cambios.

Al ejecutar el comando nos aparece una ventana en la que nos da la opción de proteger la estructura y las ventanas. Estructura impedirá añadir o eliminar hojas mientras que activando la casilla de ventanas, permanecerán con el mismo tamaño que tenían al bloquearlo. Opcionalmente podremos igual que en el caso anterior introducir una contraseña. Si decidimos introducir una contraseña, cosa que volvemos a aconsejar, también nos solicitará verificarla. Por último, si intentásemos modificar la estructura del libro nos aparecería el siguiente mensaje.

La lección de mi amigo Fede

2010-03-01T09:52:00.000-08:00

El otro día recibí en mi correo del trabajo una consulta de mi amigo Fede. Ocasionalmente me ha preguntado alguna cosilla sobre Excel, como casi todos mis amigos o excompañeros. En caso de estar en el trabajo, si tengo una respuesta inmediata se la doy, pero si requiere algo más de análisis les remito a otro momento en el que pueda pensar en ello. No sólo porque suelo estar liado en mis horas de trabajo, sino lógicamente porque no me parece muy ético hacerlo de otro modo.

Como desgraciadamente es habitual en los tiempos de crisis que corren, la empresa para la que trabaja mi amigo Fede ha cerrado y despedido a todos sus trabajadores. Fede es el responsable de la administración de personal por lo que le ha correspondido cargar con todo el papeleo y los trámites burocráticos.

El mail que me envió, tenía una hoja de excel con algunos datos de los empleados que obviamente se ocupó de sustituir para no desvelar, pero reflejando la estructura de la hoja; nombre, dirección, fecha de entrada, antigüedad, código de empleado,… y quería utilizar Word vinculado a Excel para construir las etiquetas con la dirección que utilizaría para enviar la documentación a todos ellos.

La solución era muy fácil, utilizar la opción de Combinar Correspondencia de Word (para la que próximamente dedicaré una entrada), y que básicamente trae la información de una tabla de excel para incluirla en un documento de word. De este modo, puedes construir miles de documentos con datos personalizados (por ejemplo un mailing), de forma automática e inmediata.

Había un pequeño problema, la hoja de excel donde estaban los datos no tenía formato tabla. Utilizaba distintas líneas para poner toda la información, dejaba espacios en blanco en los saltos de página, al principio del informe ponía un encabezado con el nombre del informe de la aplicación, fecha,… En definitiva, la manera habitual en la que suelen aparecer los datos al bajarlos de un ERP o una aplicación de gestión a Excel.

En concreto, la disposición de la información era la que se puede apreciar en el pantallazo a vista de pájaro y en las imágenes siguientes en mayor detalle.

En este caso la disposición de la información suponía el problema ya que necesitaba tener la información en formato tabla. Una vez solucionado esto, Combinar Correspondencia no suponía ningún problema.
.

No bastaba con ordenar la tabla, ya que haciendo una ordenación simple por un campo como Código de Empleado se perderían los datos de la dirección, que eran precisamente los que le interesaban. Después de un vistazo rápido, le dije que podía hacerse una macro para ordenar la tabla según convenía, pero que si no iba a ser una necesidad recurrente, para un sólo uso no tenía sentido ponerse con ello. Así que como andaba algo liado, dí por supuesto que era inevitable el arrastre a mano uno por uno para luego ordenar los campos. Teniendo en cuenta que eran unos 400 podéis suponer el trabajo que significaba.

Al día siguiente F. tenía la información en formato tabla. Supuse que el pobre había estado arrastrando los campos a mano, y la verdad es que pensé que quizá podía haber hecho algo más para facilitarle las cosas… pero me dio una lección cuando me explicó cómo lo había hecho. Como se suele decir, a veces los árboles no te dejan ver el bosque. Tendemos a evaluar automáticamente una situación de forma compleja por costumbre cuando la solución elemental está en frente. No sé si dedicándole un par de minutos más, hubiera encontrado ésta o una solución similar, pero la verdad es que cuando me contó su truco no pude evitar quedarme perplejo por no haber visto una solución tan sencilla y tan buena de forma inmediata. ¡¡Bravo Fede!!.

El proceso de Fede fué el siguiente:

Insertó dos columnas en las que incluir los campos Dirección y Código postal que estaban bajo el nombre.

Copio un rango con todos los datos de la columna que contenía Nombre, Dirección y Código Postal. Y pegó esa misma columna en las columnas insertadas pero una celda mas arriba para la columna dirección y dos para la columna Código postal.

Así en la línea que nos interesa está toda la información, habiendo “residuos” en el resto. Esto no nos importa, porque son columnas que desechamos despues de ordenar.

Los campos no sólo están bien situados al principio de la tabla, sino también en el resto ya que los espacios se repiten siguiendo el mismo modelo durante todo el informe.

Seleccionamos todo el área que nos interesa ordenar y ordenamos por cualquiera de los campos que presentan celdas en blanco, nosotros elegimos el primero (Código de Empleado).

Quedando la tabla despues de limpiar los residuos en perfectas condiciones para utilizar Combinar Correspondencia.

A la hora de enfrentarse a un problema, es conveniente hacerlo sin prejuicios. No es fácil, pero mi amigo Fede me ha recordado que es muy conveniente. A veces es dificil decidir en que momento debemos dejar de pensar que existe una solución sencilla para pasar a análisis más complejos. Pero debo recordar que hay que desechar primero las opciones elementales.

A Combinar Correspondencia le dedicaré una próxima entrada. Con esta solución cree hace poco en mi empresa un sistema para que partiendo de la información presente en Navision sobre entrada de pedidos de nuestros proveedores, enviásemos de forma automática cartas a todos ellos preguntando por los pedidos que según la fecha de entrega que nos habían dado estaban aún pendientes de recibir en nuestro almacén habiendo pasado dicha fecha. Es una utilidad muy interesante si nuestro sistema no dispone de opciones para crear mailings, cartas de reclamación de deuda a clientes,…

Desde 0; 10 Formato del Texto

2010-02-23T10:15:00.000-08:00

Siguiendo. con. la. .introducción. .al .formato .en. Excel ..abordaremos .la. pestaña. llamada Alineación.

El primer grupo de opciones; Alineación del texto nos permite variar la situación del texto dentro de la celda. Con sus comandos podemos llevar el contenido de la celda al centro, la parte superior o inferior, izquierda, derecha,...

Si utilizamos las opciones derecha y superior obtendríamos el resultado que se aprecia en la imágen.
.

El siguiente grupo de opciones Control de texto nos permitirá adaptar el contenido a la celda, distribuyéndolos en varias líneas, disminuyendo su tamaño,… Aunque realmente lo más utilizado es alargar la longitud de la celda en función de su contenido. Una opción de uso frecuente es la de Combinar celdas, con ella convertimos dos o más celdas en una.
.

La opción Ajustar texto hace que se modifique la altura de una celda para adaptarse al contenido de la celda. El segundo caso, Reducir hasta ajustar, modifica el tamaño de la letra para que se ajuste al tamaño de la celda. La última opción Combinar celdas, hace que las celdas seleccionadas se fusionen convirtiéndose en una sola.

En cuanto a la Orientación poco hay que decir, nos permite introducir ángulos en la disposición del contenido de la celda, bien de manera gráfica o por introducción de los grados.

Vemos como la celda B2 en la que teníamos la palabra Alineación en la esquina superior derecha puede orientarse con un ángulo determinado gráfica o numéricamente.

El grupo de opciones Dirección del texto, tengo que reconocer que para mí es un misterio. Según la ayuda de Excel Se selecciona una opción en el cuadro Texto para especificar el orden de lectura y la alineación. La configuración predeterminada es Contexto pero puede cambiarla a De izquierda a derecha o De derecha a izquierda. (Nunca lo he utilizado y no se si puede servir para idiomas con esta dirección de escritura.

La siguiente pestaña es la pestaña Fuente:

Esta pestaña proporciona múltiples herramientas para modificar la caligrafía con la que escribimos en Excel. Podemos elegir entre múltiples tipos de letra, múltiples tamaños, múltiples colores,...

Realmente lo mejor es explorar las posibilidades ya que no presenta dificultad ni existe riesgo al tratarse de variaciones meramente estéticas.

Son destacables las opciones de Superíndices y Subíndices, poco utilizadas pero muy útiles en algunos casos. No es lo mismo utilizar superíndices que poner m2.

Excel 2010 vs Excel 2007

2010-02-17T10:11:00.000-08:00

Parece ser que las .diferencias. entre. Excel 2007 y Excel 2010 no .serán tan. significativas como las que se produjeron con el cambio entre la versiónde Excel 2003 y Excel 2007. Así es los usuarios de Excel 2007 tendrán una adaptación prácticamente inmediata, y los de versiones anteriores no deberán angustiarse sobre la versión a la que adaptarse.

Desde hace tiempo es posible descargarse y utilizar una versión proporcionada por Microsoft para que sean los propios usuarios los que aporten mejoras.

Desde el punto de vista formal se pueden apreciar diferencias a nivel de la interface. Aunque la cinta de opciones permanece prácticamente igual que en la versión anterior, se sustituye el botón de office redondo por una especie de pestaña de color verde como se aprecia en la siguiente imagen.

En la cinta de opciones dispondremos de la nueva pestaña Addins. Según parece, habrá más posibilidades de personalización en la interface de Excel 2010. Siendo posible recuperar las opciones predeterminadas de manera sencilla.

Desplegando esta pestaña que sustituye al anterior Botón de Office se observa cómo parece que funcionará de una manera similar si bien con diferencias en cuanto a la forma y con mayor información disponible.

Otra de las mejoras curiosas es la presencia de unos gráficos situados en una celda llamados Sparklines. Con Excel 2003 utilizaba una solución más rudimentaria pero conceptualmente idéntica que os comentaba en la entrada Gráficos sobre celdas. Esta utilidad nos permitirá ver tendencias y picos en series de datos de forma inmediata. Como digo era una necesidad que muchos reclamábamos.

Hay una variación a la hora de trabajar con tablas dinámicas “Slicer” de forma que la selección y filtrado de datos funciona de manera diferente. Según la página de Microsoft supondrá un ahorro de tiempo y mayor eficiencia en el filtrado.

.
Una nueva vista llamada Microsoft Office Backstage cambia la manera de imprimir, guardar, compartir y publicar las hojas de cálculo simplificando los pasos a seguir respecto a versiones anteriores. Por otro lado, aparecen opciones de pegado en el menú contextual.

Incremento de las capacidades del formato condicional y los gráficos dinámicos.

Guarda automáticamente los documentos no guardados durante 4 días. Siempre que hayamos estado trabajando con ellos al menos 10 minutos. Esto es útil para subsanar las catástrofes que se producen con los pequeños descuidos.

Opción de imprimir pantallazos lo que es útil para evitar tener que abrir nuevos programas tipo snagit o la opción Imprimir Pantalla y tratar la imagen de la que como opción. Otra mejora es la posibilidad de trabajar con formas en las macros.

Una mejora que parece que se va a producir estriba en la mejora de la compatibilidad de los ficheros xls. Al parecer, los documentos creados con Excel 2010 se podrán abrir con versiones anteriores. Si esta mejora es cierta, será un gran avance ya que no todas las empresas utilizan la misma versión de Excel. De hecho me he encontrado con grupos de usuarios dentro de una misma empresa con diferentes versiones, lo que originaba problemas no solo de compatibilidad sino también en cuanto a formatos y utilidades, fórmulas...

Posibilidad de trabajar en equipo en una misma hoja de cálculo. Se puede ver quién está trabajando con la misma hoja de cálculo al mismo tiempo. Todas las modificaciones se controlan y marcan inmediatamente para mantenerlo al tanto de cuándo y dónde se realiza cada cambio. Personalmente tengo curiosidad por probar esta propiedad, entiendo que tendrá utilidad para cuestiones como rellenado de datos o trabajo con aplicaciones creadas, pero en cuanto a programación o creación de aplicaciones tengo mis dudas.

La versión de 64bits aumenta el límite que anteriormente se situaba en 2 GB. Realmente una capacidad más que suficiente para las necesidades habituales. Mis libros con más peso no llegan a las 100 Megas.

Posibilidad de almacenar cualquier hoja de cálculo en la web para que esté disponible en cualquier momento y lugar del mundo, inclusive desde equipos de telefonía móvil que posean Windows Mobile como sistema operativo y la herramienta Excel Web App.

Desde 0; 9 Formato de Número

2010-02-15T11:08:00.000-08:00

Para. seleccionar .la .forma de .presentar el contenido de una celda. utilizamos. la pestaña Número dentro de la ventana Formato de Celda.

Lo primero que vemos es un cuadro llamado Categoría, en el podremos seleccionar entre 12 categorías de formato. Algunos no son de uso generalizado pero otras las manejaremos continuamente.

General: cuando no tienen un formato específico (es el formato por defecto). Excel interpretará la información que introducimos asignándole el formato adecuado.
.

Número: Para presentar valores numéricos. Es recomendable activar la casilla de separador de miles e indicar el número de decimales que queremos que se presenten (por defecto presenta 2).

Hay que tener en cuenta que el hecho de que seleccionemos presentar 0 decimales no significa que se eliminen, siguen presentes en los cálculos. Cuando hagamos operaciones con varias celdas podemos encontrarnos con descuadres por los decimales que no vemos. Para los decimales de nuestros cálculos deberemos utilizar las funciones redondear o truncar, sobre las que hablaremos en una próxima entrada Basics.

Moneda / Contabilidad: Con ellos podemos mostrar la moneda a la que se refiere el dato y el número de decimales que queremos que aparezcan.

.

La diferencia entre moneda y contabilidad, es que la opcion de moneda nos da la opción de poner en rojo los números negativos, mientras que la de Contabilidad alinea los símbolos de moneda y las comas decimales en una columna.

Fecha y hora: Bloque con múltiples posibilidades que sirven para mostrar información temporal en su formato.

Porcentaje: Poca explicación necesita salvo que podemos elegir el número de decimales. .

Hay que tener en cuenta que cuando una celda tiene formato Porcentaje Excel entenderá el número que introduzcamos como tal, pero en caso de que tengamos una serie de datos y cambiemos el formato a porcentaje, Excel traducirá a % en escala de uno. Es decir, en una celda con un 15 no entenderá 15% sino 1500%. Por tanto si hay un 1 lo traducirá por un 100%. Para que aparezca un 20% debe haber un 0,2 etc.

Fracción: Generalmente para indicar ½ pondremos 0,5. Esta opción permite mostrar fracciones ½, 1/3, 1/5,... .

Científica: Permite trabajar con nomenclatura exponencial. .

Texto: Permite presentar la celda exactamente igual que fue introducida.

Especial: Para introducir datos como códigos postales, números de teléfono,…

Personalizada: Para crear nuestros propios formatos.

Desde 0; 8 Formatos

2010-02-11T10:25:00.000-08:00

No voy a decir .que. tan .importante .como. la .información .sea la forma de .presentarla. Lo esencial es que la información sea correcta y calculada de la forma más eficiente posible. Pero partiendo de esa base, existe una gran diferencia a la hora de presentar los datos.

La utilización de formatos distintos para distintos tipos de información. Evitar el abuso en los colores estridentes. La selección del mejor tipo de gráfico en función de los datos a presentar,… Son detalles que pueden facilitar la labor del destinatario de la información y no sólo potenciar la percepción que de nosotros tengan como profesional si no también la credibilidad y confianza en nuestros informes.

En estas entradas dedicadas a los aspectos más elementales del trabajo con Excel, dedicaré varias de ellas al aspecto formal de la hoja de trabajo en la que se presenta nuestra información.

Para cambiar la apariencia de nuestro trabajo, tenemos distintas herramientas. En Excel 2007 la mayoría de ellas están accesibles desde la cinta de opciones. Mi sugerencia es experimentar con ellas.

No obstante, para explicar las opciones de formato de una manera ordenada utilizaremos la ventana Formato de Celdas. Por otro lado dicha ventana funciona de forma exactamente igual en Excel 2003 que en Excel 2007 por lo que creo que podrá ser útil así para más gente.

Para llegar a la ventana de formato en Excel 2003, tenemos la opción del botón derecho del ratón sobre la celda o rango al que queremos aplicar el formato. Aparecerá el menú contextual sobre el que seleccionaremos la opción Formato de celdas…

También podemos llegar a dicha ventana a partir del Menú, seleccionando Formato y Celdas…

En cuanto a Excel 2007, accederemos a la ventana de formato celdas a través del Menú Contextual (exactamente igual que con Excel 2003), o también haciendo click en la flecha que indica desplegable en la cinta de opciones. Este desplegable está en los bloques Fuente, Alineación y Número.

Mi sugerencia en cuanto al uso de los formatos es practicar con ellos, su utilización es fácil e intuitiva. Por otro lado, con cuatro recursos de formato se puede mejorar considerablemente la apariencia de nuestro trabajo. En las próximas entradas entraremos en detalle en cuanto a las posibilidades del Formato.

Desde 0; 7 Copiar y pegar

2010-02-10T10:22:00.000-08:00

La. manera. natural .de introducir .datos y .fórmulas .en .excel es a .través .del .teclado, sin embargo, podemos copiar la información presente en otra ubicación y pegarla en el lugar que nos interese para trabajar con ella.

Supongamos un conjunto de datos presente en un libro que queremos utilizar en otro. Teniendo ambos libros abiertos seleccionaremos los datos, y con ellos seleccionados pulsaremos el botón derecho del ratón. Aparecerá así el menú contextual el que se aprecian distintas opciones.

De las opciones posibles, seleccionaremos Copiar.

Situándonos en el libro de destino, seleccionamos la celda a partir de la cual queremos colocar la información y volviendo a hacer aparecer el menú contextual con el botón derecho del ratón, elegimos la opción Pegar.

Existe una opción Cortar, este comando funciona igual que la opción Copiar con la diferencia de que hace desaparecer la información de la ubicación original. (Existen implicaciones distintas en cuanto a la utilización de estos dos comandos para fórmulas, según se utilicen referencias absolutas o relativas, dedicaré una entrada futura a esto).

Otra opción que podemos ver en el menú contextual es el comando Pegado especial… Cuando elegimos este comando en el menú contextual, aparece la siguiente ventana con distintas opciones.

El primer conjunto de alternativas nos permitirá pegar la información tal cual está en el origen, o sólo distintas características como puede ser el formato, los valores (ignorando las fórmulas), las fórmulas de modo que utilizarán los valores presentes en las nuevas celdas, los comentarios,…
.

El segundo grupo de opciones, no es muy utilizado, pero sirve para realizar operaciones usando los datos que tenemos y los valores que traemos. Supongamos una celda en la que tenemos el valor 5. Copiamos la celda, y en otra celda en la que hay un 2 elegimos la opción pegado especial, y dentro de este Operación / Sumar… el valor que aparecerá en la celda será el 7 ya que habrá sumado al valor que teníamos el que traemos.

Como digo, este grupo de operaciones no es de uso habitual, entre otras cosas porque es difícil su seguimiento y control.

Por último, tenemos un par de opciones en el tercer grupo, estas son muy interesantes:
.

En primer lugar Saltar blancos. Esta opción se utiliza cuando copiamos un rango y lo pegamos encima de otro que contienen información. Si seleccionamos el check box Saltar blancos, el resultado de la acción habrá reemplazado el contenido de las celdas en las que había algo que pegar, y aquellas que no tenían información (estaban en blanco) mantendrán los datos que tenían antes de hacer el pegado especial.

Copiamos los datos de la columna A y al pegarlos en la columna C, seleccionamos la opción Saltar blancos dentro de Pegado especial...

En segundo lugar tenemos la opción de Transponer, con ella podemos cambiar la disposición de ciertos datos a la horade pegarlos en el destino.

Imaginemos que tenemos un rango de una columna por 10 filas.

Después de copiarlo, para pegarlo trasponiendo filas por columnas rellenamos el checkbox Transponer. Así el nuevo rango será de una fila por 10 columnas.

Desde 0; 6 Rangos

2010-02-09T10:15:00.000-08:00

Ya hemos hablado sobre cómo se selecciona y trabaja sobre una celda y cómo se localiza. Recordamos que a la izquierda de la barra de fórmulas, se sitúa un área llamada Cuadro de Nombres donde, de forma predeterminada aparece la localización de la celda seleccionada en el área de trabajo. Así mismo, podíamos Asignarle un nombre a dicha celda simplemente escribiendo el nombre en el cuadro.

Del mismo modo, podemos seleccionar un conjunto de celdas adyacentes. Es lo que se conoce con el nombre de Rango. Los rangos pueden contener celdas adyacentes de una fila, de una columna o de varias filas y columnas.

En el Cuadro de Nombres veremos su localización. La nomenclatura de la localización de un Rango funciona utilizando la celda de la esquina superior izquierda del rango, seguida por dos puntos y la esquina inferior derecha; es decir ColumnaFila(SI):ColumnaFila(ID).

Existen varias maneras de seleccionar un rango, una de ellas es escribiendo su localización en el cuadro de nombre, al presionar enter, inmediatamente aparecerá seleccionado el rango.

Otra manera de seleccionar un rango (la más habitual) es mediante el ratón. Bastará con pulsar una vez el botón derecho del ratón sobre una celda y arrastrar el puntero hasta el límite opuesto del rango. Esta manera de seleccionar el rango no requiere un orden específico, simplemente hay que seleccionar una extremo o esquina cualquiera y finalizar con la opuesta.

Seleccionar rangos es una operación necesaria entre otras operaciones a la hora de copiar grandes cantidades de información para pegarlas en otro lugar de la hoja. También se utiliza para cambiar formatos de determinadas áreas de la cuadrícula. O a la hora de utilizar determinadas utilidades de la hoja como la construcción de subtotales, la elaboración de tablas dinámicas, la aplicación de filtros,...

Existe la posibilidad de desplazar rangos o dicho de otro modo cambiarlos de lugar. Para ello, teniendo seleccionado el rango, colocamos el cursor sobre uno de sus bordes. En ese momento podremos hacer click con el botón izquierdo del ratón y sin soltarlo, desplazar el rango a otro lugar. El nuevo rango como es lógico habrá cambiado de localización.

Es interesante resaltar que esta acción no rompe las referencias de las fórmulas que pudieran utilizar celdas contenidas en el rango, ya estén vinculadas con referencias absolutas o relativas (explicaré este concepto en una próxima entrada).

Por último hablaremos de la posibilidad de seleccionar absolutamente toda la superficie de trabajo. Esto puede ser de utilidad para asegurarnos de borrar todo el contenido de una hoja, o bien para asegurarnos de copiar todo el contenido de una hoja (aunque para duplicar hojas hay otra fórmula que veremos más adelante). Como decía seleccionaremos toda la superficie de una hoja haciendo click en el cuadro situado en la confluencia de los títulos de fila y columna. Esquina superior izquierda del área de trabajo.

Desde 0; 5 Editar un libro en Excel

2010-01-26T10:01:00.000-08:00

Ya- hemos- hablado- sobre el -tipo de -datos -que se- pueden- introducir en una celda en la entrada Desde 0; 2 Estructura de la Hoja. Para modificar el contenido de una celda podemos:

1. Hacer click con el botón derecho sobre la celda (seleccionarla) y escribir sobre ella. Con esto se sustituye el contenido en caso de haber algo, o se introduce lo que pongamos si no hay nada. También podemos borrar su contenido pulsando Supr o Delete con la celda seleccionada.

2. Hacer doble click sobre la celda para hacerla editable. Con esto podemos cambiar todo o parte de su contenido.

Si lo que queremos editar es una fórmula, podemos hacerlo con el procedimiento anterior, o bien utilizando la barra de fórmulas. Se presenta encima de la cuadrícula identificada con el símbolo fx. Haciendo click sobre ella con el botón derecho la haremos editable.

Tanto si trabajamos sobre la barra de fórmulas como si hacemos editable una celda con una fórmula, aparecerán resaltadas con distintos colores los componentes de la misma, lo que es muy útil para identificar lo que queremos cambiar.

En cualquier momento podemos insertar filas o columnas en nuestra hoja de cálculo. Para ello, seleccionamos la fila o columna por delante de la que queremos insertar otra fila o columna y presionamos en el icono Insertar situado en la cinta de opciones de la ficha Inicio, grupo Celdas.

En la imágen insertaríamos una columna a la izquierda de la actual columna K. En la siguiente imágen insertaríamos una fila por encima de la que ahora es la segunda fila.

Otro modo de insertar filas, columnas o celdas es situándonos en una celda y desplegando las opciones del icono Insertar y ejecutar el comando que requieran nuestras necesidades.

Hay que decir que insertar filas no altera las fórmulas. Lo contrario ocurre si utilizamos la opción de eliminar filas o columnas. En ese caso, todas las fórmulas referenciadas a celdas que se eliminan dejarán de funcionar dando un mensaje de error.

Para eliminar una fila o columna la seleccionamos haciendo click sobre el encabezado que la identifica, y pulsamos el botón derecho sobre la selección de modo que nos aparecerá el menú contextual con las opciones disponibles. En este caso la opción que nos interesa es Eliminar.

Como se ve en el menú contextual otra opción a utilizar es la de Ocultar, en este caso no perderemos la información contenida en las celdas afectadas, simplemente dejaremos de verlas. Por tanto la opción ocultar no afectan a las fórmulas.

Para volver a ver una fila o columna oculta, tendremos que seleccionar las dos filas o columnas adyacentes. Mostrando el menú contextual en este caso ejecutaríamos la opción Mostrar.

Por último, podemos Insertar, Eliminar, Ocultar y Mostrar hojas en nuestro libro.

Para añadir una nueva pulsamos en la pestaña situada a la derecha de las pestañas existentes, automáticamente aparecerá una hoja nueva.

Haciendo click con el botón derecho sobre una pestaña, también aparece un menú contextual con las mismas opciones disponibles. Con las opciones Eliminar, Mostrar y Ocultar haremos con las pestañas lo que su nombre indica.

Desde 0; 4 Abrir, Crear, Guardar y Cerrar

2010-01-25T10:45:00.000-08:00

Para empezar a trabajar con un libro de Excel tenemos dos opciones:

1. Abrir un libro existente, para ello iríamos al icono de office presente en la esquina superior izquierda.

Pulsando sobre el icono abrir, aparecerá una ventana en la que podremos encontrar el documento que queremos recuperar.

Para abrir el documento haremos doble click sobre su nombre en la ventana que aparece tras pulsar en la opción Abrir (representada por la carpeta), o teniéndolo seleccionado presionamos sobre el botón Abrir.

2. Para crear un libro nuevo hacemos click en el mismo botón de office, ejecutaremos el icono representado por una hoja "Nuevo".

En la ventana siguiente, bien hacemos doble click en el icono Libro en blanco o teniéndolo seleccionado pulsamos sobre el comando Crear.

Una vez que hemos trabajado con el documento deberemos guardar los cambios para que queden registradas las modificaciones realizadas. En este caso tenemos dos posibilidades:

Comando Guardar, esta opción la solemos utilizar cuando hemos trabajado con un libro ya creado sobre el que hemos continuado trabajando. Si no tenemos el icono guardar situado en la barra de herramientas de acceso rápido, pulsamos el botón de office y ejecutamos el comando Guardar.

Para el caso de que hayamos creado un libro nuevo (en cuyo caso Excel lo habrá nombrado por defecto Libro1,2,3,… dependiendo del número de libros nuevos abiertos en la sesión), o queramos cambiar la denominación existente en el que estamos trabajando (lo que se conoce como guardar una copia), tras pulsar sobre el botón de office ejecutaremos el comando Guardar como:

Como se ve en la imágen, al Guardar como tenemos distintas opciones de las que hablaremos más adelante. Por el momento seleccionaremos Libro de Excel o Libro de Excel 97-2003 en el caso de que lo puedan tener que abrir usuarios con esta versión del Excel.

Tras ejecutar el comando guardar como, nos aparece una ventana en la que tendremos la opción de elegir el sitio donde lo guardamos y su nombre.

Por último veremos los iconos situados en la esquina superior derecha de la aplicación, es de suponer que ya conozcáis estos iconos, por lo que simplemente pasaremos por encima de ellos.

El icono representado por una línea nos servirá para minimizar la ventana de Excel, ejecutándolo ésta, se sitúa una representación de la aplicación en la parte inferior de la pantalla en la barra junto con el resto de aplicaciones abiertas. Volveremos a hacer aparecer la aplicación haciendo click sobre la representación o con el botón derecho sobre ella, eligiendo Restaurar o Maximizar.

El icono representado por dos cuadros nos sirve para cambiar el tamaño de la ventana que contiene la aplicación. Si hemos ejecutado ese comando, aparecerá en su lugar un cuadrado. Ejecutándolo maximizaremos de nuevo el tamaño de la ventana.

Para cerrar la aplicación pulsaremos sobre el aspa.

Podéis observar que hay dos conjuntos de iconos. El inferior es para cerrar un libro sin cerrar la aplicación. El superior es para cerrar Excel.

Por último, si hemos hecho cambios en un libro y pulsamos sobre el aspa para cerrarlo, nos saldrá el siguiente mensaje:

Este mensaje es de gran utilidad para evitar cerrar por descuido el libro sin guardar los cambios.

Desde 0; 3 Navegar por Excel

2010-01-20T10:17:00.000-08:00

Como decía en la entrada anterior, podemos desplazarnos por la hoja de Excel e interactuar con ella mediante el ratón y el teclado.

Para ir a celdas relativamente cercanas a la que estamos, puede ser más rápido utilizar las flechas del cursor que el ratón.

Otros movimientos posibles son:
Ctrl Inicio: Nos lleva al principio de la hoja (Celda A1).
Ctrl Fin: Nos lleva a la última fila que tiene datos en nuestra hoja.
Estas dos opciones desplazan la celda activa.

Avpg: Desplaza hacia abajo un pantallazo la superficie visible de la cuadrícula.
Alt + Avpg: Desplaza hacia la derecha un pantallazo la superficie visible de la cuadrícula.
Repág: Desplaza hacia arriba un pantallazo la superficie visible de la cuadrícula.
Alt + Repg: Desplaza hacia la izquierda un pantallazo la superficie visible de la cuadrícula.
Estas dos opciones no desplazan la celda activa, sólo desplazan el área visible de la hoja.

Para cuando queramos desplazarnos a una celda concreta fuera del área visible de la cuadrícula (generalmente bastante alejada), podemos utilizar el comando Ir a. Accedemos a él a través de la sucesión de teclas Ctrl + I.

La tecla Ctrl se mantiene apretada hasta pulsar la segunda tecla. Estas combinaciones forman lo que denominamos shortcuts o atajos. Podéis encontrar los atajos más habituales en la entrada Tips & Tricks I.

En la imagen siguiente, vemos unos elementos llamados barras de desplazamiento. Permiten desplazar el área visible de la hoja. Hay varias formas de utilizarlas; haciendo click sobre la barra y sin soltar el botón desplazando la barra o haciendo click en la parte anterior o posterior de la barra, lo que nos desplaza un pantallazo el área visible de la cuadrícula.

Generalmente en Excel utilizamos la primera Fila y Columna para Identificar el tipo de datos que hay en ellas, es lo que se conoce como una tabla de datos.

Cuando la tabla de datos es muy grande, puede que nos interese movernos por la cuadrícula sin dejar de ver cuales son los datos con los que estamos trabajando. Para ello utilizaremos la opción de Inmovilizar paneles. La opción inmovilizar paneles se encuentra en la ficha Vista y dentro de la Cinta de opciones, en el grupo de comandos Ventana.

Como se ve en la imagen siguiente tenemos las opciones inmovilizar el panel que contenga las filas que se encuentren por encima de la celda activa, las columnas que se encuentran a la izquierda de la celda activa o las filas y columnas a la vez.

En el siguiente vídeo vemos cómo podemos desplazarnos por la hoja utilizando las barras de desplazamiento sin perder la noción de que contiene cada fila/columna.

Una opción parecida a la de Inmovilizar paneles es la de utilizar los cuadros de división, con ella podemos dividir la parte visible del área de trabajo hasta en cuatro partes para desplazarnos por ella de forma simultánea. Para ello situamos el puntero sobre el cuadro de división que se encuentra encima de la barra de desplazamiento vertical o a la derecha de la horizontal. Cuando el puntero se convierta en una flecha de dos puntas, arrastraríamos la barra de división hasta el punto de la hoja de cálculo en el que desea dividir la ventana.

Para quitar la barra de división basta con hacer doble click sobre ella.

Desde 0; 2 Estructura de la Hoja

2010-01-19T10:34:00.000-08:00

En. esta. entrada .daremos .unas .pinceladas .sobre la. interface .de .usuario. de. Excel; Lo primero que vemos al abrir la aplicación es una superficie cuadriculada sobre un fondo blanco (es el área de trabajo), cada unidad de esa cuadrícula se llama celda.

La celda se identifica y localiza de manera similar a si estuviéramos jugando a los barquitos (por su orden de fila y columna). Las filas se identifican por números en orden ascendente de arriba abajo. Las columnas se identifican a través de las letras del abecedario, según su orden en el mismo, de izquierda a derecha.

Así, la celda C6 se encuentra en la confluencia de la sexta fila (6), con la tercera columna (C). Cuando la celda se encuentra seleccionada, aparece resaltada con el borde negro. Además, el encabezado de la fila y columna que la determinan, aparecen también resaltados con un color distinto. Existe un área de la interface llamada Cuadro de Nombres, en la que aparece bien la localización de la celda seleccionada o su nombre en el caso de que la hayamos nombrado (en la última imagen podemos ver el Cuadro de Nombres presente encima de la celda A1).

Podemos interactuar con la aplicación a través del ratón y del teclado (bien sea mediante las flechas cursor o combinaciones del teclado).

Para seleccionar una celda podemos desplazar el cursor del ratón hacia ella, y hacer click con el botón izquierdo en ella, también podríamos llegar a ella con las flechas del cursor, el tabulador (movimiento lateral de izquierda a derecha) o la tecla Enter (movimiento horizontal de arriba abajo).
.

Haciendo doble click sobre la celda la convertimos en editable, aparece el cursor en ella lo que facilita localizar la posición en la que introduciremos el siguiente carácter. Hacer una celda editable nos permitirá introducir información (números, texto o fórmulas).

Los números serán la materia prima para realizar los cálculos. El texto servirá para identificar la información de modo que cualquier usuario y no sólo su creador pueda interpretarla. Por último, con las fórmulas haremos los cálculos según nuestras necesidades. Podemos hacer cálculos simples (operaciones aritméticas), pero existen numerosas fórmulas más complejas disponibles en Excel. Existe una barra de fórmulas en la parte superior de la cuadrícula en la que se nos muestra la fórmula (formulas (2)), ya que en la celda en la que incluimos la fórmula veremos el resultado numérico (formulas (1)).

La cuadrícula constituye una hoja de cálculo, cada hoja se identifica por la pestaña (situadas en la parte inferior de la ventana, el nombre y algunas propiedades de las pestañas son editables). En un libro puede haber un número variable de hojas, identificadas a su vez por sus pestañas.

Desde 0; 1 ¿Qué es Excel?

2010-01-18T10:14:00.000-08:00

Excel es la .aplicación de Hoja de Cálculo. líder en el mercado, hasta el punto de convertirse en indispensable para cualquier trabajo de oficina. Son múltiples las aplicaciones de Excel en todas las áreas de la empresa; Administración, Producción, Ventas, Marketing, I+D,…

En todos ellas se presentan necesidades de transformación y presentación de información, y es en esto donde un buen conocimiento de Excel marca la diferencia. La solución a cualquier necesidad de un proceso de gestión se puede construir a partir de Excel.

La evolución de Excel con el incremento continuo de sus capacidades, permite aventurar que muchas aplicaciones que ahora mismo se distribuyen por separado, acaben desapareciendo por redundantes ante Excel.

La interfaz de usuario de Excel puede parecer compleja en un principio, pero con la práctica resulta muy intuitiva y fácil de usar. Eso sí, requiere de paciencia y constancia ya que son muchísimos los comandos disponibles. Esto nos da una idea de la amplitud de sus posibilidades.

Una primera aproximación a la interface de usuario de Excel, nos lleva a identificar el área de trabajo (que es donde introduciremos la información), y el área de las fichas con sus respectivas cintas de opciones e iconos de comando agrupados por categorías funcionalmente homogéneas.

Estos comandos realizan una acción al pulsarlos, por ejemplo, el comando “Negrita”, representado con una N resalta con un trazo más grueso el contenido de la celda seleccionada en el momento de ejecutarlo.

Existen en el mercado numerosos manuales sobre Excel, por supuesto son de utilidad. Sin embargo, según mi experiencia es la práctica y el trabajo continuado con Excel lo más efectivo a la hora de aprender. Podemos ir a academias y comprarnos libros, pero si no vemos un ejemplo concreto de la necesidad que tal o cual fórmula o comando puede solucionar, acabaremos olvidando lo que hemos aprendido .

Lo habitual es empezar a utilizarlo en el trabajo. Encontrar nuestras necesidades en cuanto a transformación y presentación de información y pensar: “estaría bien poder hacer esto…”, plantearnos: ¿Cómo podría conseguir que Excel lo hiciera?... entonces, acudir a los manuales, a los foros en internet, a los conocidos con más experiencia,… y sobre todo, al botón más importante de Excel: El botón de ayuda.

Es un proceso largo, pero sin darte cuenta, de la nada, en no demasiado tiempo puedes pasar a ser un experto. Y lo que es mejor, el proceso es muy divertido y satisfactorio.

Hipervínculos sobre Imágenes 2

2010-01-10T23:43:00.000-08:00

Como .anunciaba en la entrada. anterior Hipervínculos sobre imágenes 1, veremos ahora un trabajo con hipervínculos que consigue un efecto más interesante a los vistos hasta ahora. Se trata de insertar en una imagen distintos hipervínculos asociados a distintas áreas de la misma.

En este caso, construiremos sobre la base de un mapa de Europa, hipervínculos a las pestañas referentes a cada país. De este modo, al situar el cursor sobre un país nos aparece la mano señalando con el índice (que es como excel representa el hipervínculo), y al pulsar el botón izquierdo del ratón accederemos a la información de ese país.

En la imagen vemos el mapa que utilizaremos como "índice", directamente copiado y pegado en la cuadrícula de excel.

Vamos a la ficha Insertar y seleccionamos el comando Formas del grupo Ilustraciones. Como vemos en la explicación, con este comando podríamos insertar formas prediseñadas a las que tendríamos la capacidad de cambiar algunas características como tamaño, color,... pero también podemos crear nuestras propias formas.

Para ello utilizamos la opción A mano alzada, esto hace que podamos dibujar una forma libre.

El puntero se transformará en una cruz, que actuará como un lápiz mientras mantengamos presionado el botón izquierdo del ratón.

Como se ve en la imagen anterior, una vez terminada la forma, se convierte en un objeto al que podremos cambiar sus características. Para ello basta con tenerlo activado y pulsar el botón derecho del ratón, apareciendo entonces el menú contextual.

Como podéis suponer el comando que nos interesa ejecutar es Formato de forma... Tras pulsarlo, aparece la siguiente ventana.

Lo primero que hacemos es quitar el relleno de la forma. Para ello, ponemos el 100% de Transparencia en la opción Relleno sólido del conjunto de opciones para Relleno.

Luego, haremos desaparecer el contorno de la forma, para lo que elegimos la opción Sin línea dentro del conjunto de opciones de Color de línea.

De este modo, tendremos una forma invisible a la que le podremos asignar el hipervínculo. Para ello volvemos a acceder al menú contextual pulsando el botón derecho del ratón sobre la forma seleccionada, y elegimos el comando Hipervínculo...

Sin detenernos en explicar el funcionamiento, creamos el hipervínculo a un Lugar de este documento, y la celda A1 de la pestaña de Datos España. Para profundizar sobre la creación de hipervínculos podéis echarle un vistazo a la entrada Hipervínculos.

Repetiremos las operaciones anteriores con todos los países para los que queramos crear hipervínculos. Podéis observar que dejo un pequeño margen entre país y país, esto lo hago para que al pasar de un vínculo a otro, el cursor cambie de forma por la superficie donde no hay hipervínculo. Personalmente pienso que ayuda a ver más claramente cuando hemos pasado de un país a otro.

.
Por último, podéis apreciar el resultado final en el vídeo siguiente.

Excel 2007 vs Excel 2003

2010-01-09T03:38:00.000-08:00

Estaba .pensando en hacer una entrada sobre el inminente .Excel 2010. Ya hace bastantes meses que se puede conseguir una versión que Microsoft permite descargar para que sean los propios usuarios los que lo testeen. Sin embargo, no se puede decir que haya diferencias radicales con respecto a Excel 2007.

Eso me hizo pensar que no había dedicado ningún comentario al mucho más significativo paso de Excel 2003 a Excel 2007. Es ahí donde se produjo un cambio realmente importante. Por eso, y teniendo en cuenta que con estos tiempos de crisis, muchísimos usuarios no se habrán hecho aún con Excel 2007 y harán el cambio directamente a Excel 2010 desde Excel 2003, he creído conveniente dedicarle una entrada. Posteriormente hablaremos sobre lo más significativo del Excel 2010.

Sin ánimo de ser exhaustivo (para ello sugiero la parte que señala las novedades de Excel 2007 dentro del propio Excel 2007), podemos enunciar como mejoras de Excel:

Aspecto totalmente diferente de la interfaz de usuario, esto hace que mejore la forma de interactuar con la aplicación de modo que los comandos más utilizados están situados de forma más accesible. Esto, claro está, una vez que te habitúas. Al principio te pasas una semana bastante perdido (y con ataques de ira difícilmente contenida). Personalmente pienso que al poco tiempo te acostumbras y luego no volverías al sistema antiguo. En la imágen adjunta se puede ver la nueva interfaz, para cada opción o pestaña del menú aparece una cinta de opciones con los comandos más representativos agrupados por categorías.

Otra gran mejora se podría resumir en el aumento de la potencia, lógicamente esto va asociado a un aumento de la memoria, que junto con el aumento de la capacidad de procesado de los nuevos ordenadores soporta todas las mejoras entre las que podemos destacar:

La superficie de trabajo de las hojas de cálculo pasa a ser prácticamente infinita. Más de 1.000.000 de filas y más de 16.000 columnas hacen que el espacio en Excel deje de ser problema. Con las versiones anteriores, ocurría en numerosas ocasiones que para bajar gran cantidad de datos, hubiera que utilizar Access, o dividir las bajadas en varias partes. Así una vez tratada la información y eliminado lo innecesario se podía trabajar en una hoja. Ahora hay que tener realmente muchos, pero que muchos datos para que Excel no resulte autónomo.

Aumento de las capacidades de interacción con la aplicación, como el número de veces que es posible deshacer (100). Incremento de la longitud máxima de las celdas (hasta 32.000 caracteres), del número de anidamientos en una fórmula a 64 (por ejemplo de los condicionales anidados).

Mejora significativa en cuanto a gráficos. Realmente las capacidades gráficas de Excel 2007 marcan la diferencia, por ello les dedicaré una entrada en el futuro. No sólo aparecen nuevos tipos, sino que se mejoran significativamente los que había y el número de colores disponibles que ahora pasa a 16.000.000... Como ejemplo ilustramos las diferencias apreciables en cuanto a gráficos de tarta. Vemos cómo el aspecto que proporcionaba comparado con el que proporciona Excel 2007:

Mejoras en cuanto al formato condicional. Sobre esto ya dediqué la entrada Formato Condicional Excel 2007. Ahora no solamente puedes incluir múltiples formatos condicionales, si no que incorpora complementos gráficos realmente útiles. Esto es una importante aportación a la hora de elaborar todo tipo de informes, especialmente para la representación de los KPI's o Indicadores Claves del Éxito de un Cuadro de Mando Integral o Balanced Scorecard.

Nuevas fórmulas y mejora de las existentes como por ejemplo =SUMAR.SI.CONJUNTO.

Mejoras gráficas e incorporación de elementos nuevos en WordArt y SmartArt

A nivel de usuario pueden ser las diferencias más destacables, pero no son las únicas, hay diferencias como plantillas nuevas, configuración de la barra de estado (de esto hablamos en la entrada Tips & Tricks IV), posibilidad de publicar en .pdf. Diferencias en cuanto a la elaboración de tablas dinámicas como veíamos en Tablas Dinámicas 3 (Excel 2007), mejoras y novedades en cuanto a conectividad de datos...

En definitiva, aunque al principio puede resultar un tanto molesto, el paso de 2003 a 2007 fue sin duda un paso a mejor. Teniendo en cuenta que Excel 2010 seguirá avanzando por el mismo camino, tarde o temprano habrá que hacer el esfuerzo de adaptarse (esfuerzo que compensa).

Hipervínculos sobre Imágenes 1

2010-01-06T01:17:00.000-08:00

Los. hipervínculos .son. un recurso .muy. interesante .del. que. ya .hablamos en la .entrada Hipervínculos. En la presente entrada, aprenderemos a crear hipervínculos sobre imágenes. Hasta ahora los habíamos creado en palabras situadas en una celda de Excel. Sin embargo, obtendremos una impresión mucho más profesional y atractiva, si utilizamos imágenes.

En este primer ejemplo vamos a crear una serie de hipervínculos asociados a imágenes propias del clip-art presente en Excel, pero el procedimiento es exactamente el mismo con cualquier otro tipo de imagen que podamos copiar/pegar en Excel. Posteriormente, incluiré una nueva entrada: Hipervínculos sobre Imágenes 2 con un efecto bastante mas espectacular.

El primer paso es insertar la imagen, para ello acudimos a la cinta de opciones de la pestaña Insertar, ejecutando el comando Imágenes Prediseñadas. Podemos ver que hay más opciones; en Imágenes podemos insertar por ejemplo fotos, y con Formas, tendremos una serie de formas para utilizar.

Una vez aquí se puede operar de distintas maneras en función de si ya tenemos creadas nuestras propias colecciones o no. Supondremos que es la primera vez que utilizamos esta opción por lo que en la pestaña que nos aparece hacemos click sobre la opción Organizar Clips situada en la parte inferior de la ventana.

La siguiente ventana permite elegir entre la lista de colecciones. Para obtener símbolos de las monedas vamos a la carpeta Colecciones de Office y dentro de ésta, la carpeta Negocios.

Arrastrando el icono deseado a la hoja de cálculo, aparecerá con el contorno resaltado, lo que nos permitirá modificar su tamaño.

Supongamos que tenemos un libro de Excel con los símbolos de distintas monedas presentes en la primera pestaña (Índice). En las siguientes páginas tendremos el detalle de la información para cada una de las monedas; Euros, Dólares, Libras y Yenes.

Haciendo clik con el botón derecho sobre la imagen a la que queremos asignar un hipervínculo, aparecerá resaltada junto con el menú contextual, dentro del cual aparecerá la opción Hipervínculo.

Ejecutando el comando hipervínculo, nos aparece la ventana para la creación de hipervínculos que ya conocemos de la entrada Hipervínculos. En este caso, al estar todas las direcciones de interés dentro del mismo documento de Excel, elegimos Lugar de este documento y la celda y pestaña oportunos en cada caso.

Repitiendo la operación en todos los símbolos tendremos hipervinculadas todas las monedas de la pestaña Índice a sus respectivas pestañas con información individualizada.

Por otro lado, para poder volver a la pestaña Índice de modo directo, creamos otro hipervínculo que nos lleve de la pestaña de cada moneda al Índice. Para ello utilizaremos en este caso la opción Formas de la pestaña Insertar. Dentro de las opciones disponibles, elegimos una flecha que indica el retroceso.

Seleccionando la flecha y colocando el puntero del ratón (convertido momentáneamente en una cruz) sobre la posición en la que queremos insertar la forma, arrastramos para obtener el tamaño deseado. Hecho esto, la forma estará seleccionada. Con botón derecho accedemos al menú contextual con el que igual que antes, creamos el hipervínculo a la pestaña Índice.

Hecho esto con todas las pestañas relativas a las monedas, podremos navegar por nuestra hoja de cálculo de manera rápida y fácil.

Orígenes de las Hojas de Cálculo

2010-01-04T10:15:00.000-08:00

Hoy .en día es .inconcebible. el .trabajo .en la. empresa .sin una .aplicación .como la hoja de cálculo. En mi humilde opinión, Excel se está convirtiendo en la herramienta ofimática por excelencia. Con los nuevos desarrollos y capacidades de Excel 2007 y el próximo Excel 2010 (tamaños de hoja gigantescos, capacidad de creación de hojas de hasta 4 GB, elementos gráficos cada vez más sorprendentes, colores infinitos…), no sería de extrañar que el futuro depare que Excel fagocite a sus compañeros del paquete Office. Ahora mismo, estoy pensando en Access, pero con Excel puedes apañartelas para hacer lo que haces con los demás, dejemos tiempo al tiempo y veremos.

Como mera curiosidad (reconozco que un tanto freaky), introduzco esta entrada que habla sobre cómo ha evolucionado esta herramienta. Es una pincelada, que no pretende ser una clase magistral (tengo claro que el papel de historiador no es el mío).

Según he podido leer en Wikipedia y en algunos libros (que sinceramente no reseño porque no recuerdo el nombre/autor…), ya había embriones de lo que podía considararse conceptualmente una hoja de cálculo por 1961, año en el que apareció el artículo Budgeting Models and System Simulation de Richard Mattessich.

Si os interesa la evolución de los ordenadores, echad un vistazo a Historia de la Computación en Wikipedia, abarca desde la invención del algoritmo en 1617, hasta los próximos lanzamientos previstos para el 2010…

Dan Bricklin y Bob Frankston en 1978 son los inventores generalmente aceptados de las hojas de cálculo. La hoja en cuestión era VisiCalc y se programó con el ordenador Apple II. Según Bricklin, la idea surgió de ver cómo un profesor de la universidad que hacía una tabla de cálculos en un tablero encontró un error, teniendo que borrar y reescribir el proceso en dicho tablero. Bricklin se dió cuenta de que podría hacerse el proceso en un ordenador, usando el paradigma tablero/hoja de cálculo para ver los resultados de las fórmulas que intervenían en el proceso.

Como curiosidad para los amantes de las “reliquias”, comentar que es posible obtener VisiCalc desde la pagina web de Dan Bricklin, www.bricklin.com siendo ejecutable en los ordenadores de hoy día.

Como suele pasar con las grandes ideas, irían apareciendo aplicaciones que harían evolucionar a las hojas de cálculo. SuperCalc (1980), Multiplan (1982), Lotus 1-2-3 (1983) que ha sido uno de los líderes durante mucho tiempo y Quattro (1987) que pretendía quitarle el liderato. No obstante, con Microsoft Excel (introducido para el Mac OS en 1985 y para Windows 2.0 en 1987) se acabó la discusión.

Microsoft Excel es el líder indiscutido en cuestión de hojas de cálculo a pesar de los productos que se pueden conseguir de forma gratuíta como OpenOffice y StarOffice o las hojas de cálculo web de Google. Veremos que nos depara el futuro, pero de momento no parece que peligre el liderato de Excel, por tanto si hay que emplear tiempo en aprender o especializarse en una, Excel es la opción.

Tablas Dinámicas 3 (Excel 2007)

2009-12-28T08:43:00.000-08:00

Como -comentaba -en la- entrada -Tablas -Dinámicas 1 (Excel- 2003), la -forma de construir tablas dinámicas es muy similar en Excel 2007, si bien tiene bastantes mejoras.

Partiendo de la misma tabla de la que disponíamos en las dos entradas anteriores, abrimos lo que se asemeja al antiguo Asistente para Tablas y Datos Dinámicos. Para ello ejecutamos el icono Tabla Dinámica presente en el bloque Tablas de la pestaña Insertar.

Esto abre una ventana en la que seleccionamos el origen de los datos, (que será el rango que ocupa toda la tabla) y la posición en la que se situará la tabla. En este caso colocaremos la tabla dinámica junto con la tabla origen de datos.

Esto nos lleva a la misma representación de la tabla dinámica que aparecía en Excel 2003, pero con una ventana llamada lista de campos de Tabla Dinámica.

La construcción de la tabla dinámica puede hacerse igual que hacíamos en Excel 2003 (por el método de arrastre a la tabla) o también arrastrándolos a los cuadros que se sitúan debajo de la frase Arrastrar Campos entre las Áreas Siguientes.

Así llevamos el campo deporte de la lista al cuadro de Rotulos de fila que se situa en la parte inferior de la Lista de campos de tabla dinámica.

Luego el campo Ventas al área Sumatorio de Valores..

El campo ciudad al filtro del informe:

Llevamos los tipos de tienda a los Rótulos de Columna.

Igual que comentábamos en Excel 2003, podemos utilizar los filtros presentes en los campos de tabla para afinar más en la presentación de los datos. Con una importante novedad; en Excel 2007 tenemos la opción de seleccionar varios valores a la hora de filtrar.

Si quisiéramos mostrar el Coste y Beneficio independientemente del tipo de Tienda:

La información presente en las tablas puede elegirse en el campo configuración de campo de valor, en él como ya pasaba en versiones anteriores de Excel, podemos elegir entre suma, cuenta, promedio… Para hacer estas selecciones, podemos acceder al campo configuración del campo de la cinta de opciones de la pestaña opciones, o como se ve en la imagen siguiente, a través de activar el desplegable en el campo, dentro de la ventana lista de campos de tabla dinámica.

En la primera pestaña tenemos los tipos de cálculo posibles para resumir los datos del campo seleccionado.

Cada vez que seleccionamos una celda dentro de la tabla dinámica, aparece la pestaña de Herramientas de Tablas Dinámicas. Esta pestaña se compone de dos subpestañas, Opciones y Diseño.

En la pestaña opciones, tendremos ordenados a lo largo de distintos compartimentos de la cinta de opciones, herramientas para configurar la tabla. Algunas de ellas son muy útiles y les dedicaremos futuras entradas en más detalle. Por ejemplo la opción de creación de Campos Calculados, o los Gráficos Dinámicos.

La pestaña de diseño como su propio nombre indica afecta a temas fundamentalmente estéticos.

Las entradas sobre tablas dinámicas que he expuesto en esta serie, tienen una intención introductoria. El tema de las Tablas dinámicas, es muy amplio y requeriría de muchas más indicaciones. Como he dicho anteriormente, dedicaré futuras entradas a profundizar en este tema. Hasta entonces mi consejo es la práctica y la investigación de forma autodidacta. No obstante, como siempre, podéis hacerme llegar las dudas que os hayan podido quedar tras en este tema.

Tablas Dinámicas 2 (Excel 2003)

2009-12-23T01:11:00.001-08:00

En esta- entrada- continuamos -con -el mismo -ejemplo -visto en Tablas Dinámicas 1 (Excel 2003). Continuaremos explorando las opciones en la presentación y resumen de la información en las Tablas Dinámicas de Excel 2003. La siguiente entrada tratará de las modificaciones y mejoras en Excel 2007.

En la entrada anterior, Tablas Dinámicas 1 (Excel 2003), habíamos ignorado la barra de herramientas que aparecía utilizando el asistente para tablas y datos dinámicos.

Nos fijaremos en el comando Dar formato al informe, que aparece resaltado en la imagen al lado del desplegable Tabla dinámica.

Ejecutando ese comando, aparece una ventana en la que tenemos una gran variedad de formatos predefinidos para presentar la información. Seleccionamos por ejemplo uno de los situados al final de la lista.

La pega de estos diseños predefinidos es que hay que completar el formato de los números para que aparezcan los separadores de miles y los decimales, alineándo los números a la derecha. (Como se ve en la imágen siguiente, resultado de la acción anterior, los números aparecen centrados y sin puntos ni comas). Para mejorar esta presentación, vamos a la opción de menú Formato y seleccionamos la opción Celdas.

Esto nos abre la típica ventana de formato de celdas, en la que elegimos la opción deseada. (Considero que no es necesario profundizar en ello).

Una vez arreglado el tema del formato de los números, el aspecto de la tabla es el siguiente:

Hay otra manera de manipular los resultados de la tabla que no habíamos explorado hasta el momento es utilizando el comando diseño presente en el asistente para tablas dinámicas.

Al comando Diseño, llegamos situando la celda activa dentro de la tabla dinámica. Esto hace que aparezca el asistente para tablas dinámicas. Expandiendo la lista desplegable Tabla dinámica, podremos seleccionar el asistente.

Y una vez en el asistente, podremos ejecutar el comando Diseño...

Obtenemos la ventana diseño, en la que aparece la representación de la tabla dinámica con el diseño actual, y la lista de campos disponibles que podremos manipular arrastrándolos a las distintas áreas de la tabla.

En nuestro caso, vamos a modificar la tabla de modo que además de la suma de ventas, aparezca la suma de costes y la suma de beneficios.

Esto dará como resultado la tabla con el diseño de la información deseado.

Recordemos que disponemos de distintos filtros presentes en los campos página, filas y columnas que hacen que podamos ir al minimo detalle en la presentación de los datos. En la imagen vemos los datos para los deportes de Fútbol, en la ciudad de Barcelona, tienda Tipo B.

Por último, una función muy útil de las tablas dinámicas es que podemos ver el máximo detalle para un conjunto de información seleccionado. Si hacemos doble click sobre la celda que contiene el dato nos aparece una nueva pestaña con el desglose de toda las líneas que componen ese dato.

Es aconsejable eliminar dicha pestaña una vez analizada la información, así no saturamos el libro con hojas que no vamos a utilizar.

Tablas Dinámicas 1 (Excel 2003)

2009-12-08T01:37:00.000-08:00

Las tablas dinámicas son informes modificables que pueden resumir grandes cantidades de información. Se pueden alterar sus filas y columnas para ver diferentes resúmenes de los datos. Aplicar filtros, centrarse en determinada información…

Pueden crearse también gráficos dinámicos para representar toda esa información gráficamente, e incluso se puede colocar un informe de tabla dinámica en una web, de modo que los visitantes puedan ver los datos e interactuar con ellos desde la propia web. Pero sobre estos temas hablaré en futuras entradas.

Creación de una tabla dinámica:

La creación de una tabla dinámica es un proceso de gran utilidad pero que requiere de cierta práctica. El mecanismo es muy similar tanto en Excel 2003 como Excel 2007. Comenzaré con la creación en Excel 2003 para la que dedicaré dos entradas una para tablas simples y la siguiente para las tablas con un grado de complejidad mayor. Posteriormente, dedicaré una entrada para el Excel 2007. Para ello utilizaré siempre el mismo ejemplo de Tabla Dinámica con los mismos datos.

Habitualmente utilizaremos tablas de considerables dimensiones, pero para este ejemplo, como es habitual, utilizaremos una simplificación con intención didáctica.

Supongamos que tenemos una cadena de tiendas de deportes, presente en varias ciudades, en algunas de ellas con varias localizaciones. Hemos dividido las ventas según los distintos deportes, y disponemos también de los costes y el beneficio resultante en un día determinado. Como se puede intuir, hay muchas maneras de presentar y resumir toda la información disponible.

Partimos de un libro de excel en el que en la primera pestaña (Datos de Origen), tenemos la tabla con la información, los requisitos de esa tabla son elementales. Una serie de columnas con encabezado en la primera fila. Para la tabla dinámica, creamos la pestaña Tabla Dinámica.

Lo mejor para crear una tabla dinámica en Excel 2003 es utilizar el asistente para tablas y gráficos dinámicos. Este asistente se encuentra en Datos / Informes de Tablas y Gráficos Dinámicos.

Esto hará que aparezca la ventana con el primer paso del asistente. Por el momento seleccionaremos las opciones de Lista o base de datos de Microsoft Office Excel (que en este caso está en la pestaña Origen de Datos), y Tabla dinámica.
.

Hecho esto pulsamos Siguiente, lo que nos lleva al paso 2 del asistente. En él, introducimos el rango donde están los datos a tratar.
.

Pulsando sobre siguiente, accedemos al sguiente paso en el que establecemos el lugar en el que se situará la tabla.

En este caso, la esquina superior izquierda se situará en la celda B2 de la pestaña Tabla Dinámica.

Pulsando sobre Finalizar, habremos terminado de definir el marco que rige la tabla dinámica. En este punto, podemos definir cómo queremos que se nos presente la información. Para ello aparece la representación de la tabla dinámica con la lista de campos disponibles (columnas de la tabla Origen de Datos).

Estos campos se pueden llevar a la tabla haciendo click sobre el campo y sin soltar, arrastrarlo hasta el área de la tabla donde queremos que aparezcan. En este caso arrastramos el campo Deporte y lo llevamos a los campos de fila.
.

Así, tenemos en nuestra tabla la lista con todos los deportes posibles (apareciendo una sola vez).

Del mismo modo podemos llevar el campo Ventas a el área de datos, con lo que obtendremos el resumen de ventas por deporte.

Podemos incluir más criterios por los que mostrar la información arrastrando otro campo a la zóna de los campos de página, esa zona se encuentra colocando el campo en la celda superior izquierda de la tabla dinámica.

En nuestro caso hemos arrastrado el campo ciudad, podremos elegir la ciudad para la que queremos ver el resumen anterior, o si lo queremos ver para todas. Para ello, sólo debemos utilizar el filtro practicable del campo Ciudad.
.

Así podríamos ver la información anterior individualizada por ciudad, pero también podríamos añadir más campos en el caso de que estuviéramos interesados en una información más detallada. Por ejemplo, llevando el campo Tienda a la zona Campos en Columna obtenemos el desglose de las ventas por deporte y tienda, para la ciudad de Madrid.

Observe que tanto en los campos fila, columna y página podemos utilizar los filtros disponibles y acotar la información de la tabla. Por ejemplo, la información anterior para el tipo de tiendas A de todas las ciudades.

Hasta ahora hemos jugado con las posibilidades de la tabla dinámica sin utilizar la opción de diseño, ni los formatos preestablecidos. De estos temas me ocuparé en la siguiente entrada. De momento considero que para aquellos que no han trabajado aún con tablas dinámicas, experimentar con las posibilidades ofrecidas hasta ahora da suficiente juego como para estar ocupados durante un tiempo.

Conciliación Bancaria

2009-11-17T03:20:00.000-08:00

En esta entrada .expongo una de las soluciones que .comentaba en la entrada Optimizar es sacar el máximo partido a lo que se tiene. Consiste en la realización de una Conciliación Bancaria.

Para los que no estén habituados a este proceso, la conciliación bancaria consiste básicamente en comparar de forma periódica (por lo general mensualmente), la información que nos ofrece el extracto de nuestro banco con la que reflejan los cobros y pagos de nuestra contabilidad. El objetivo es asegurarnos que no hay ningún cobro o pago que se queda fuera de nuestra contabilidad y evitar duplicidades o errores en la misma.

La opción más elemental para hacerla consistiría en imprimirse mes a mes los extractos bancarios que nos proporciona la web de nuestro banco, y compararlos con la impresión del mayor de la cuenta de bancos, tickeando (a mano) importe por importe ambos listados.

Esta operación no es difícil pero es muy laboriosa y agotadora, lo que es del todo injustificable por el tiempo que requiere y por la posibilidad de error que genera no solo las operaciones manuales sino también el cansancio que provoca la tarea. Esperando no ofender a los que aún hacen así esta operación (que sorprendentemente según mi experiencia aún son muchos), es como si el resto de la contabilidad se registrase en la pared con pinturas.

La solución que propongo es muy elemental, y manifiestamente mejorable, pero que en la empresa en la que trabajo ya supuso una diferencia significativa respecto a la alternativa que empleaban. Con la base de los cálculos propuestos y unas pequeñas macros, podemos construir una aplicación que nos diga automáticamente los descuadres y omisiones a investigar, siendo la única operación manual necesaria la acción de copiar-pegar en nuestro Excel la información del banco y nuestra contabilidad, pero sería bastante más largo de explicar por lo que detallo la solución más simple.

Para empezar, podemos indicar que tanto de nuestro banco (la mayoría tienen la opción de descargar a Excel la información que muestra el extracto) como nuestra contabilidad (los ERP’s tienen la opción de descarga en Excel de la información) podemos transformarla en pocas y simples operaciones a algo parecido a lo que muestra la siguiente imagen.

Este sería un ejemplo de la información tratada de nuestra contabilidad.

Mientras que esta sería la información del banco una vez preparada.

En la información del banco nos quedaremos con la fecha valor, la descripción del movimiento (generalmente contiene el emisor o receptor del cobro/pago) y el importe, mientras que para la información de nuestra contabilidad nos quedaremos con la fecha de registro, el número de cobro o pago, la descripción (si contiene igualmente emisor o receptor del cobro/pago) y el importe. Como vemos en ambas imágenes, hemos ordenado ambas tablas en función de la columna importe de menor a mayor.

Podemos dejar mas columnas en función de la información disponible y la utilidad en cuanto a la investigación de descuadres, pero en principio, esta herramienta está pensada para indicarnos dónde están las diferencias a investigar, no para determinar las causas. Para eso tendremos que investigar en la contabilidad, los extractos bancarios detallados, hablando con los clientes o el banco,…

Una vez que tenemos la materia prima sobre la que vamos a comparar, utilizaremos solamente dos fórmulas que ya hemos visto en este blog; el condicional SI visto en Basics 5; Condicional SI y la fórmula COINCIDIR, visto en BASICS 9; COINCIDIR.

Primero determinaremos de la lista de importes contenidos en la tabla cuántos se repiten; esto es importante ya que como vimos en su momento, la fórmula coincidir nos da la posición en la que se encuentra el primer valor coincidente que encuentra. Debemos estar al tanto de si determinado valor se repite más de una vez, ya que en ese caso podría aparecer 3 veces en la tabla del banco y una sola en nuestra contabilidad y no nos percataríamos de que faltan dos en nuestra contabilidad.

Básicamente lo que hace este condicional es avisarnos de si el valor de la celda perteneciente a la línea en la que está la fórmula es igual al siguiente, con lo que nos avisará poniendo en la celda OJO, o es diferente con lo que dejará la celda en blanco.

El siguiente paso es averiguar si los valores de cada una de las tablas se encuentran presentes en la otra tabla, y viceversa. Es decir, comprobar que todos los apuntes que refleja el extracto del banco se encuentran presentes en nuestra contabilidad (para cerciorarnos de que no nos hemos dejado ninguno) y viceversa, que todos los valores que hay en nuestra contabilidad tienen su justificación en el extracto del banco (para evitar que por error hayamos incluido alguno inexistente). Para ello utilizamos la fórmula coincidir según se puede apreciar en las imágenes:

Análisis de los resultados:

Una vez hecho todo lo anterior, tenemos información suficiente como para verificar la conciliación bancaria con total seguridad. Para ello nos fijamos en:

Columna de los Repetidos:
Vemos cuantos importes se repiten y el número de veces que lo hacen, obviamente los mismos importes se deben repetir el mismo número de veces en ambas tablas. En el caso del ejemplo se puede ver como hay dos importes que se repiten 330,6 y 1698 pero observamos que el 330,6 aparece 3 veces en el banco mientras que en el mayor aparece solamente 2 veces. Esto significa que hemos dejado uno por contabilizar. Suponiendo que tanto la descripción del banco como de nuestros asientos contables proporcionen la información que tenemos en el ejemplo, es fácil de determinar que el cobro que falta por contabilizar es el cobro del 23/10 del CLIENTE 65455. En cuanto al segundo valor repetido, vemos que se repite dos veces tanto en el banco como en nuestra contabilidad por lo que es correcto.

Columna de los Presentes en la otra Tabla:
Como ya dijimos esta columna nos indica si cada uno de esos importes se encuentra presente en la otra tabla. Una vez tenida en cuenta la consideración sobre los repetidos, vemos que en nuestro ejemplo hay un cobro que refleja el extracto del banco que falta por contabilizar en nuestro sistema, el pago del cliente CLIENTE 66855 que se produjo el 17/08.

Esta es una solución básica para la conciliación, su bondad reside en que su construcción no requiere de conocimientos avanzados de Excel, simplemente las dos fórmulas indicadas que son realmente sencillas. Por supuesto, es posible construir soluciones similares con otras fórmulas también vistas como por ejemplo =BUSCARV(…;…;…;…). Incluso se pueden construir aplicaciones mucho más elegantes y automáticas utilizando macros, pero como solución primaria considero que es bastante eficaz y aceptable.

Basics 9; COINCIDIR

2009-11-16T01:10:00.000-08:00

Esta .fórmula .nos busca .un determinado .valor o texto en una .columna y nos. devuelve la posición que ocupa en dicha columna, así por ejemplo, si tuviéramos la clasificación de una maratón con el orden en el que han llegado los distintos dorsales, para determinar la posición en la que han llegado un determinado dorsal podríamos utilizar la fórmula coincidir. En este punto hay que decir que al igual que pasa con otras fórmulas como puede ser BUSCARV, en el caso de que existan dos valores o textos iguales en la lista y sea precisamente ese el que estemos buscando, nos dará la posición que ocupa el primero que encuentre.

Sintáxis:

= Coincidir ( Valor Buscado ; Rango de Búsqueda ; Tipo de Coincidencia )

Donde:
Valor Buscado: Es el elemento cuya existencia y posición queremos verificar en el rango de búsqueda.
Rango de búsqueda: Es la columna en la que buscaremos el valor objetivo.
Tipo de Coincidencia: Existen tres tipos:
0: Coincidencia Exacta; es el tipo óptimo y más aconsejable.
1: Da el mayor valor que es menor o igual que el objetivo, es decir que si no hay una coincidencia exacta, nos da el que más se le aproxima por abajo. Requiere que el orden del rango sea ascendente.
-1: Da el menor valor que es mayor o igual al objetivo. Por tanto, si no hay una coincidencia exacta nos dará el que más se le aproxima por encima. Requiere que el orden del rango sea descendente.

En la siguiente imagen vemos un ejemplo sobre la utilización de ésta fórmula: Tenemos el orden de llegada de una competición, identificando la posición con el número de dorsal y proporcionando el tiempo de llegada.

Para averiguar si el dorsal buscado está entre los 10 primeros y su posición utilizamos las celdas de la columna E para poner los dorsales a buscar y las adyacentes de la columna F para la fórmula que nos dará el resultado. Como se ve en la imagen, cuando la fórmula encuentra el dorsal nos da su posición y cuando no lo encuentra, nos da un mensaje de error #N/A.

Esta fórmula por sí sola y a puede aportar valor a nuestras aplicaciones, como por ejemplo para verificar la existencia de una entrada en una columna con muchas líneas, determinar la posición exacta de una entrada,… pero combinados sus resultados con otras se pueden construir soluciones muy simples pero de gran utilidad y valor añadido, como la que comentaba en la entrada Optimizar es sacar el máximo partido a lo que se tiene una Conciliación Bancaria a la que dedicaré una próxima entrada.

Funciones BD

2009-11-11T10:07:00.000-08:00

En esta. entrada .continuamos con el. tratamiento de .Bases de Datos, una de las utilidades más destacadas de Excel. En la entrada Basics 8; Tablas y Filtros se explicaba cómo partiendo de una tabla de datos, se podían mostrar sólamente los datos que nos interesaban aplicándoles filtros. En la entrada Basics 7; Subtotales poníamos ejemplos sobre cómo agrupar las tablas por diferentes criterios y obtener los subtotales de cada agrupación. Próximamente incluiré una entrada sobre tablas dinámicas que es una de las herramientas más potentes de Excel y que según mi experiencia, presenta cierta dificultad y aún diría pereza o rechazo para la mayoría de la gente a la hora de iniciarse y dominar su utilización.

No obstante, las tablas dinámicas no son la única opción para trabajar con Bases de Datos. Es posible que nos interesen solamente ciertos datos, o datos que son imposibles de obtener en una sola tabla dinámica sin cambiar su configuración. Para esos casos además de las soluciones vistas en las entradas que comentábamos con anterioridad, podemos construir fórmulas de la familia de las funciones condicionales como SUMAR.SI, o emplear las Funciones BD que nos ocupan en esta entrada, y que permiten trabajar con bases de datos, realizando distintas operaciones que de otro modo requerirían de acciones intermedias. En este caso, vamos a utilizar el ejemplo que aparece en la ayuda del propio Excel, con ligeras modificaciones e intentándolo explicar de una manera más clara y con más ejemplos para facilitar su comprensión.

Sintáxis de las fórmulas (todas las fórmulas BDfunción siguen la misma estructura):

BDfunción ( Rango de Datos ; Identificación de los datos a utilizar ; Restricciones )

Donde:

Rango de Datos; Tabla con los datos a utilizar (las celdas en amarillo).

Identificación de los datos a utilizar; Encabezado de la columna objetivo entre “”.

Restricciones; criterios para discriminar los datos (las celdas en verde).

BDCONTAR(…;…;…)

Esta fórmula nos sirve para determinar cuantos elementos que cumplen con las restricciones, contienen números en las celdas pertenecientes a la columna objetivo.

En el primer resultado, calculamos de toda la tabla, cuantos casos de manzanos entre 10 y 16 metros (restricciones), contienen números en el campo “Edad”.

En el segundo resultado, calculamos de toda la tabla, cuantos casos de manzanos >10 metros y perales >8 metros (restricciones), contienen números en el campo "Edad".

BDCONTARA(…;…;…)

Esta fórmula nos sirve para. determinar cuantos elementos que cumplen con las restricciones, contienen información en las celdas pertenecientes a la columna objetivo, es decir, no están en blanco.

En el primer resultado, calculamos de toda la tabla, cuantos casos de manzanos entre 10 y 16 metros (restricciones) , no tienen el campo “Beneficio” en blanco. Vemos que el resultado vuelve a ser 1. (En realidad en la tabla no hay ningún caso con el campo beneficio en blanco, pero como sólo hay un manzano entre 10 y 16 metros, sólo hay un caso de manzano que no tenga el beneficio en blanco).

En el segundo resultado calculamos de toda la tabla, cuantos casos de manzanos entre 10 y 16 metros, y perales entre 8 y 12 metros (restricciones), no tienen el campo “Beneficio” en blanco. Como en el caso anterior no hay ningún campo beneficio en blanco, pero como sólo hay un manzano y un peral que cumplen las restricciones, el resultado es 2.

BDMAX(…;…;…)

Utilizamos esta fórmula para determinar el valor máximo de la columna objetivo, entre los casos que cumplen las restricciones.

Para el primer resultado, buscamos el máximo beneficio que se obtiene tanto en los manzanos como en los perales (restricciones), al no haber restricciones de altura y considerarse tipo de árbol, el beneficio más alto de los cuatro posibles es el del primer manzano con 105.

Para el segundo resultado, buscamos el máximo beneficio de entre los casos que cumplen todas las restricciones, que como vimos en un caso anterior sólo son dos, por tanto el manzano de 14 metros y el peral de 9 metros. El resultado es el del peral con 76,8.

BDMIN(…;…;…)

Este es el caso contrario al anterior, en lugar de buscar el máximo buscamos el mínimo.

Para el primer resultado, de los manzanos cuya altura es superior a 10 metros (sólo hay dos), el beneficio mínimo es el del manzano de 14 metros, por tanto 75.

Para el segundo resultado, el rendimiento mínimo de los manzanos con altura superior a 10 metros y perales con altura superior a 8 metros es de 8.

BDSUMA(…;…;…)

Con esta fórmula sumamos los datos de la columna objetivo de todos los elementos que cumplan las restricciones.

En el primer resultado, sumamos los beneficios de todos los manzanos, por tanto el resultado es 225.

En el segundo resultado, sumamos la edad de los árboles que cumplen todas las restricciones, por tanto el resultado es 23.

BDPRODUCTO(…;…;…)

Con esta fórmula multiplicamos los datos de la columna objetivo de todos los elementos que cumplen las restricciones.

Para el primer resultado, calculamos el producto de los rendimientos de todos los manzanos con altura superior a 10 metros. Obtenemos un resultado de 140.
.

Para el segundo resultado, calculamos el producto de los rendimientos de los árboles que cumplen todas las restricciones. Como ya sabemos, son el manzano de 14 metros y el peral de 9, por tanto el resultado es 80.

BDPROMEDIO(…;…;…)

Con esta fórmula hayamos el promedio de los datos de la columna objetivo de todos los elementos que cumplen las restricciones.

En el primer resultado calculamos el promedio de edad de los manzanos, tenemos 3 manzanos, de 20, 15 y 9 años, por tanto el promedio da 14,67.

En el segundo resultado calculamos el promedio de la edad de nuestros dos arboles favoritos que cumplen todas las restricciones, por tanto 11,5.

Existen más fórmulas BD, =BDDESVEST(...;...;...) para la desviación standard considerando una muesta, =BDDESVESTP(...;...;...) para la desviación standard considerando toda la población, =BDVAR(...;...;...) y =BDVARP(...;...;...) para la varianza considerando una muestra o toda la población respectivamente... sin embargo, su sintáxis y mecánica no difiere de las que hemos visto, y no suele ser tan frecuente su necesidad, por lo que no me detengo en ellas.

Consideraciones adicionales.

Puede identificarse los datos a utilizar mediante el número de columna dentro de la tabla en lugar de su encabezado, en este caso hemos utilizado el encabezado por considerarlo didácticamente más fácil de entender.

Varias de las opciones de cálculo de las funciones BDfunción están disponibles de forma automática en la barra de estado, como vimos en la entrada Tips & Tricks IV, sin embargo suele ser habitual que necesitemos no sólo saber por ejemplo no sólo cual es el máximo o la suma, sino que quede reflejada en una celda para su utilización en cálculos posteriores.