Excel - Dividir el texto de una celda en varias lineas

Hay situaciones en las cuales queremos dividir el texto en una celda de Excel en dos o más líneas.
Excel nos ofrece dos posibilidades de lograr esto:
1 – generar una nueva línea dentro de la celda
2 – dar un formato que exhiba el texto en líneas separadas para adaptarlo al ancho de la celda.

Con el primer método generamos efectivamente una nueva línea dentro de la celda. La ventaja de este método es que nos permite establecer dónde empezar la nueva línea.
Por ejemplo si queremos empezar la nueva línea con la palabra "Ventas"



seleccionamos la celda, ponemos la señal del mouse a la izquierda de la palabra "Ventas" y pulsamos ALT y Enter




Como vemos también el texto en la barra de fórmulas aparece en tres líneas, lo que nos impide ver el encabezamiento de las columnas.

El segundo método consiste en utilizar Formato de Celdas---Alineación, señalando la opción "ajustar texto"




En este caso, a diferencia del anterior, Excel "decide" dónde abrir la nueva línea de acuerdo al ancho de la celda. En la barra de fórmulas, el texto sigue apareciendo en una sola línea. De esta manera evitamos que la barra de fórmulas oculte el encabezamiento de las columnas.




Categorías: Varios_

Technorati Tags: Excel

Valores unicos en una lista de Excel

En la entrada de ayer mostrábamos como generar una lista de valores únicos con validación de datos. La lista de validación de datos se actualizaba a medida que se agregaban nuevos datos en el rango seleccionado. Para lograrlo usamos fórmulas bastante complicadas y tablas auxiliares.

En realidad hay una solución mucho más sencilla para esta tarea. Excel produce este tipo de listas en forma automática. Mostraré esto con un ejemplo.

Supongamos la lista de frutas del ejemplo de ayer




Si después de seleccionar la celda A11 pulsamos simultáneamente las teclas ALT y flecha hacia abajo, se abrirá una lista desplegable que contiene valores únicos de la lista



Utilizando las flechas o moviendo mouse podemos elegir el valor deseado. Para aceptar el valor podemos pulsar el botón derecho del mouse o apretar Enter.




Categorías: Varios_

Technorati Tags: NAMES, Nombres en Excel

Validación de Datos en Excel - Agregar valores únicos a la lista desplegable

Todo usuario que lleve algún tiempo trabajando con Excel, conoce la funcionalidad Validación de Datos.
Esta funcionalidad permite controlar el tipo de datos que son introducidos en un rango determinado. Existen varias posibilidades






Las más interesantes, en cuanto a posibilidades y flexibilidad, son Lista y Personalizada.
Lista permite generar una lista desplegable de la cual puede el usuario elegir valores válidos.
Ya hemos mostrado diferentes técnicas para poblar la lista, en especial haciendo uso de nombres, para referirnos a rangos que se encuentran en otra hoja y para establecer referencias dinámicas.
En una entrada anterior mostraba como crear listas desplegables dependientes. Es decir, que los valores de una lista de validación de datos dependan de la elección de un determinado valor en otra lista.

Hoy veremos como agregar valores únicos a una lista desplegable de validación de datos.
El archivo con el ejemplo se puede descargar aquí.
La técnica consiste en generar una lista de valores únicos en un rango de alguna hoja, que será la referencia de la lista de validación de datos.
Para lograrlo usamos una tabla auxiliar con dos rangos.


En el rango "Valores únicos" usamos la fórmula =SI(CONTAR.SI($A$5:A6,A6)=1,A6,"")
Esta fórmula nos permite establecer si un valor aparece más de una vez. Es importante prestar atención a la definición del rango $A$5:A6, donde la primer celda tiene una referencia absoluta y la segunda una referencia relativa.

En el rango "No. De Orden" generamos un número que nos servirá de argumento en la fórmula que generará la lista de valores únicos.
=SI(CELDA("contents",C5)="","",FILA(C5))
El número es el número de fila sólo par los valores que aparecen por primera vez. Esta fórmula usa como argumento el resultado de la fórmula del rango "Valores únicos".

En el rango "Lista" usamos la fórmula

INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))

para hacer aparecer los valores que tiene número de orden.
Para evitar resultados #¡NUM!, agregamos una condición (en color verde) para evaluar resultados de error

=SI(ESERROR(INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))),"",INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25)))

Una explicación detallada de las fórmulas se puede leer aquí.


Categorías: Funciones&Formulas_, Varios_

Technorati Tags: Validacion de Datos

Ocultar hojas de calculo en un cuaderno Excel

A veces queremos ocultar una de las hojas de un cuaderno Excel. La forma corriente de hacerlo es mediante el menú Formato---Hoja---Ocultar.

Si queremos prevenir que algún usuario vuelva a hacer visible la hoja, podemos proteger el cuaderno (libro) con el menú de protección y usar una contraseña.



Es sabido que las contraseñas de Excel son muy fáciles de romper. Por lo tanto, este método de ocultar hojas no es muy seguro.

De hecho no existe ningún método seguro, pero si podemos aprovechar una característica que la mayoría de los usuarios de Excel desconocen.

La Hoja de Excel cuenta con una serie de propiedades. Una de ella es la propiedad "Visible". Esta propiedad puede tener uno de tres valores: "xlSheetVisible", "xlSheetHidden" o "xlSheetVeryHidden".

Hojas ocultas con la propiedad xlSheetHidden (la que aplica Excel cuando usamos el menú Formato) pueden hacerse visible con el menú Formato---Hojas---Mostrar. Hojas ocultas con la propiedad xlSheetVeryHidden no aparecen en este menú y por lo tanto serán invisibles para la mayoría de los usuarios regulares de Excel.

Para cambiar la propiedad de la hoja debemos acceder al cuadro de propiedades de esta.
Ya hemos visto que uno de los caminos de hacer visible el cuadro de propiedades es hacer visible la barra de herramientas del Cuadro de Controles y allí pulsar el botón Propiedades

Otra variante es abrir el editor de Visual Basic (Herramientas---Macros---Editor de Visual Basic) y allí cliquear la Hoja1 (o la que queramos ocultar) para activar la ventana de Propiedades



si la ventana no aparece usamos el menú View---Properties Window en el menú del editor.

En ambos casos procedemos de la misma manera. Abrimos la lista desplegable en la ventanilla de la propiedad Visible, y señalamos el valor "2 – xlSheetVeryHidden"




Para mostrar nuevamente la hoja, sólo podemos utilizar la ventanilla de propiedades del editor de Visual Basic y devolver el valor de la propiedad Visible a xlSheetVisible nuevamente.


Categorías: Varios_

Technorati Tags: Excel

Listas desplegables dependientes en Excel con Validación de Datos.

Ya hemos visto que la función Validación de Datos de Excel (Datos---Validación de Datos) permite controlar los datos que son introducidos en una celda. Una de las posibilidades es crear una lista desplegable de la cual el usuario puede elegir el valor a introducir en la celda.
En una nota anterior hemos tratado como evitar duplicados con validación con validación de datos. En otra hemos mostrado un modelo para asignar operarios a máquinas, de manera que cada operario asignado "desaparezca" de la lista desplegable.

En esta nota mostraremos otras posibilidades de crear listas desplegables dependientes.

Supongamos por ejemplo, que queremos de acuerdo al país elegido en una celda, la lista desplegable en la celda contigua muestre solamente ciudades de ese país.

Los pasos a seguir son:

1 – creamos nombres que contengan la lista de los países y las listas de las ciudades de cada uno de los países. Es importante que el nombre del rango que contiene las ciudades sea idéntico al nombre del país.



2 – en la celda de los países aplicamos validación de datos, con la opción "lista" usando el nombre "países"



3 – en la celda de las ciudades aplicamos validación de datos, también con la opción "lista", pero en este caso aplicamos la función INDIRECTO para crear una referencia dinámica al nombre que contiene la lista:

=INDIRECTO($A$5)

Este es el motivo que el nombre del rango de las ciudades coincida con el nombre del país



A partir de este momento, al elegir un nombre de país en la celda de países, la lista de validación de datos se ajusta al nombre que contiene los nombre de las ciudades de ese país

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Insertar un calendario permanente en hojas de calculo Excel con Controles ActiveX

Ayer vimos como usar controles ActiveX en hojas de Excel. Para insertarlos debemos previamente activar la barra de herramientas del Cuadro de Controles. En la esquina inferior derecha de la barra del Cuadro de Controles, está el icono "más controles".

Al activarlo tendremos acceso a muchos más controles (no todos disponibles para las hojas de cálculos). Uno de ellos es el Calendar Control, que nos permite insertar un calendario permanente en la hoja de cálculos.

Para insertar el calendario en una hoja Excel, procedemos como con los otros controles ActiveX, como mostramos en la entrada anterior.

Una vez insertado en la hoja, abrimos el menú de propiedades, para adpatar el calendario a nuestras necesidades

En este ejemplo de calendario en Excel, tenemos una tabla con ventas diarias en la Hoja2. En la Hoja1 insertamos el calendario, y lo ligamos (LinkedCell) a la celda B7 (que no será visible al estar cubierta por el calendario).

En la celda B14 introducimos esta fórmula, que usa la celda ligada B7 como argumento

=FECHANUMERO(B7)

Esta formula convierte el valor textual del calendario a una fecha. En la celda C14 introducimos una función BUSCARV, para obtener el valor de las ventas correspondientes a la fecha elegida en el calendario

=SI(B14>MAX(Hoja2!A:A);"fecha sin datos";BUSCARV(B14;Hoja2!A:B;2;0))

la condición lógica con la función MAX, nos permite evitar valores erróneos cuando se elige una fecha que no exste en la tabla de ventas.

Ahora, podemos elegir la fecha en el calendario y ver la suma de ventas correspondientes en la celda C14.

Además del menú de propiedades en la barra de herramientas, también se puede cliquear con el boton izquierda el control y abrir este diálogo

Categorías: Varios_

Technorati Tags: Calendario en Excel, Controles ActiveX en Excel

Controles ActiveX en hojas de calculo Excel

Como ya hemos visto en una entrada anterior, Excel permite agregar controles en las hojas de cálculos. En las versiones más recientes de Excel existen dos tipos de controles: controles Excel y controles ActiveX.
En las entradas anteriores hablamos sólo sobre los controles Excel, que aparecen en la barra Formularios. Estos controles son menos flexibles y tienen menos posibilidades que los controles ActiveX, pero por lo general su funcionalidad es suficiente para cualquier usuario Excel promedio.
Sobre las diferencias entre los controles de la barra Formularios y los controles ActiveX en Excel, pueden leer el resumen de John Walkenbach (en inglés).

Los controles ActiveX son mucho más flexibles y ofrecen muchas más posibilidades, pero por lo general, requieren el uso de Vba.
Para usar los controles ActiveX en la hoja de cálculo tenemos que activar la barra Cuadro de Controles (clic en la zona de barras de herramientas y selección o Ver---Barra de Herramientas---Cuadro de Controles)

En la parte superior de la barra aparecen tres iconos: modo de diseño, propiedades y ver código.

Estos tres iconos nos permiten cambiar el aspecto y las propiedades del control y también agregarle funcionalidad con Vba (macros). Estas tareas se realizan estando en "modo de diseño". Una vez terminada la adaptación del control a nuestras necesidades, debemos pulsar el icono para salir del modo de diseño y poder utilizar el control.
Para insertar un control ActiveX procedemos como con los controles Excel, cliqueando con el mouse el control y ubicándolo en la hoja de cálculo.
Una de las diferencias con los controles Excel, es que los controles ActiveX tienen muchas más propiedades que pueden ser adaptadas. Para acceder al menú de las propiedades activamos el control y cliqueamos el icono de propiedades en la barra de Cuadro de Controles.
Por ejemplo, insertamos una casilla de verificación y abrimos el menú de propiedades

Como podemos ver, existen un gran número de propiedades, como el color, la fuente y otras. La propiedad "LinkedCell" nos permite ligar el "estado" del control a una celda en la hoja. Esta celda puede ser utilizada luego como argumento en una función. Por ejemplo, he cambiado el texto de la casilla a prueba de "prueba de casilla", el color del fondo y la he ligado a la celda C1. Al final del proceso apretamos el icono "modo de diseño" para activar el control. A partir de ahora, de acuerdo a la situación de la casilla, en la celda C1 aparecerá el valor VERDADERO (casilla señalada) o FALSO.

Como ya hemos dicho, podemos utilizar el valor de la celda C1 como argumento en una función. Por ejemplo, en la celda A4 podemos escribir esta fórmula
=SI(C1;"casilla señalada";"casilla sin señalar")
y de acuerdo a la situación del control, aparecerá uno de los dos textos.

En la esquina inferior derecha de la barra del Cuadro de Controles, está el icono "más controles". Al activarlo tendremos acceso a muchos más controles (no todos disponibles para las hojas de cálculos).

En la próxima entrada veremos como insertar un calendario permanente en una hoja de cálculo de Excel.



Categorías: Varios_

Technorati Tags: Controles en Excel

Gráficos en Excel – Actualización automática de datos.

Supongamos un gráfico basado en una tabla de datos como esta:




Excel nos permite generar con facilidad un gráfico que represente el desarrollo de las ventas:




Ahora supongamos que queremos que el gráfico se actualice cada vez que agregamos datos a nuestra tabla.
Una posibilidad es hacerlo manualmente, para lo cual tendremos que activar el gráfico y en el menú Gráfico---Datos de origen y actualizar al rango de los datos



Pero existe la posibilidad de forzar a Excel a actualizar el gráfico en forma automática cada vez que agreguemos datos a nuestra tabla.
Para lograr esto definimos nombres que contienen fórmulas con la función DESREF para crear rangos automáticos, como ya vimos en la entrada sobre referencias dinámicas.
Para crear un gráfico que se actualice automáticamente seguimos estos pasos:
1 – creamos un nombre llamado Mes (Insertar---Nombres---Definir) con esta fórmula: =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)
2 – creamos un segundo nombre que contendrá esta fórmula: =DESREF(Hoja1!$B$2,0,0,CONTARA(Hoja1!$B:$B)-1,1)
3 – Contrariamente a lo que parecería lógico, no aplicamos el nombre con el rango dinámico en lugar de el rango definido en la ventana Rango de Datos (ver arriba), sino en una fórmula que define el gráfico, llamada SERIES. Activamos el gráfico y seleccionamos una de las barras. Al hacer esto veremos la fórmula "SERIES" en la barra de fórmulas



En esta fórmula reemplazamos las referencias a los rangos por los nombres que acabamos de definir:

=SERIES(Hoja1!$B$1,grafico_aut_sp.xls!mes,grafico_aut_sp.xls!ventas,1)

Como se puede ver, Excel agrega automáticamente el nombre del cuaderno donde están definidos los nombres.

A partir de este momento, el gráfico se actualizará automáticamente cada vez que agreguemos los datos de un mes de ventas.

Y antes de finalizar la entrada, unas palabras sobre la fórmula SERIES. Esta fórmula es distinta de las que conocemos en Excel. No podemos utilizarla en celdas de una hoja y tampoco podemos combinar en ella funciones que usamos en las hojas de cálculo. La tarea de esta fórmula es definir los datos que se utilizan en un gráfico. Su sintaxis es la siguiente:
=SERIES(título, rango de la categoría, valores, orden)

Como hemos dicho, no podemos usar funciones regulares de Excel en esta fórmula, pero como hemos visto, si podemos usar nombres que contienen fórmulas comunes.


Categorías: Graficos_

Technorati Tags: Gráficos en Excel

Excel - Controlando el orden de entrada de datos en una hoja

En una entrada anterior mostré como limitar el área de trabajo en una hoja de Excel. Para hacer esto usamos la propiedad Scroll Area de la hoja, como explico en esa entrada.
Pero ahora uno de mis lectores me pregunta como controlar la secuencia de entrada de datos en una hoja de Excel. Esta situación se da en especial en formularios, donde queremos facilitar la entrada de datos. Por ejemplo, que cada vez que apretamos "enter" Excel sepa elegir la celda adecuada en la secuencia.
Esto se puede hacer con macros especiales llamadas "eventos". Estas macros están ligadas a una hoja específica y entran en acción cuando sucede algún evento determinado, por ejemplo, cambia el valor de una celda o elegimos una celda en una rango determinado. De ahí su nombre.
Daré aquí un ejemplo sencillo. Un formulario para recuento de inventario donde en la columna A se introduce el número de catálogo, en la columna B la cantidad, en la columna C el precio y el la columna D calculamos el valor total (cantidad X precio).




Con la ayuda de una macro del tipo eventos, queremos que después que el usuario ingrese el número de catálogo y apriete "enter", Excel pase automáticamente a la celda de la columna B de la misma fila (cantidad); después de ingresar la cantidad Excel pase directamente a la celda del precio y calcule automáticamente el total en la columna D.

Los pasos a seguir son los siguientes:

1 – apuntamos con el mouse a la pestaña de la hoja y pulsamos el botón derecho; en el diálogo que se abre seleccionamos "ver código"




2 – en el editor del Vba (prestar atención que la Hoja1 aparece seleccionada) copiamos este código

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngA As Range, rngB As Range, rngC As Range

Set rngA = [A:A]
Set rngB = [B:B]
Set rngC = [C:C]

If Union(Target, rngA).Address = rngA.Address Then _
Target.Offset(0, 1).Select

If Union(Target, rngB).Address = rngB.Address Then _
Target.Offset(0, 1).Select

If Union(Target, rngC).Address = rngC.Address Then
Target.Offset(0, 1).Value = Target * Target.Offset(0, -1)
Target.Offset(1, -2).Select
End If
End Sub




3 – A partir de este momento cuando se produce un cambio en la hoja, la macro evalúa si el cambio ocurrió en alguno de los rangos definidos (rngA, rngB, rngC). De ser así procede de acuerdo al código.


Esta macro supone que los datos son introducidos siguiendo el orden establecido (número de catálogo, cantidad, precio). Si variamos el orden, el total calculado (columna D) puede ser incorrecto.


Categorías:Varios_

Technorati Tags: Vba, Eventos en Excel

Excel - Como importar archivos texto a con más de 65536 filas.

Ya hemos visto algunas técnicas para importar archivos texto a Excel.
Si usamos alguna de las versiones a partir de Excel 97 y hasta Excel 2003, el máximo de filas que podemos importar es 65536.
En versiones anteriores a Excel 97 el máximo era 16384. A partir de Excel 2007 dispondremos de más de 1 millón de filas.
Pero como la mayoría de los usuarios seguimos trabajando con alguna de las versiones entre Excel 97 y Excel 2003, veremos como solucionar el problema de importar archivos de mas de 65536 filas.
La base de datos de conocimientos de Microsoft propone una macro para solucionar el problema. La solución consiste en dividir automáticamente el archivo a importar, si excede el máximos de filas permitido, en varias hojas.
En esta entrada reproduzco la macro propuesta por Microsoft, con los mensajes traducidos al español.
Además he reemplazado el método Inputbox del original por GetOpenFilename, que permite elegir el archivo desde un diálogo, en lugar de tener que escribir el nombre y la ubicación del archivo que queremos importar.

El archivo con la macro se puede descargar aquí



Esta macro no divide las líneas del archivo importado en columnas. Esto debe hacerse usando el comando Datos---Texto en Columnas.


Categorías: Varios_





Technorati Tags: Vba, Importar archivos a Excel

Traduccion de funciones Excel

En la entrada sobre los nombres de las funciones Excel en inglés, proponía una macro para traducir las funciones del idioma local al inglés.

Me llaman la atención sobre la existencia de dos complementos existentes para esta tarea.

Este complemento desarrollado por Eric Desart, se puede descargar del sitio de Ron de Bruin, que entre otras, tiene una función de traducción bi-direccional en 11 idiomas.

Otro complemento es el TranslateIt, con traducción bi-direccional en 12 idiomas.

Este complemento instala una barra de herramientas donde muestra las traducciones, ademas de un menú desplegable con las distintas opciones.

Categorías: Funciones&Formulas_

Technorati Tags: Funciones Excel

Nombre de la funciones Excel en inglés.

En las versiones locales de Excel, las funciones aparecen traducidas al idioma local. Así, por ejemplo, el usuario de la versión española usará SUMA en lugar de SUM, o PROMEDIO en lugar de AVERAGE.
No todos los casos son tan evidentes como estos que he mencionado. Por ejemplo, cómo se llama la función LARGE en español? Sería de esperar MAYOR, pero no, en la lengua de Cervantes la función se llama K.ESIMO.MAYOR.
Uno de los motivos para querer traducir el nombre de una función Excel al inglés podría ser realizar una búsqueda en la red, cuando usando el nombre en español no nos da los resultados deseados. Otra podría ser intercambiar información con un colega que usa una versión en inglés.
Yo personalmente me encuentro en esta situación cuando escribo entradas para este blog.
La solución que propongo se basa en el hecho que cuando grabamos una macro, Excel traduce las funciones al inglés. Esta macro permite traducir cualquier función de cualquier idioma local al inglés

Sub trad_func()
'escrita el 30/06/2006 por JLD

'probar si la seleccion incluye solo una celda
If Selection.Rows.Count <> 1 Then
MsgBox "por favor, seleccionar solo una celda"
Exit Sub
End If

If ActiveCell.HasFormula Then 'probar si la celda contiene o no formula
MsgBox Mid(ActiveCell.Formula, 2)
Else
MsgBox "la celda no contiene funciones"
End If

End Sub

La idea fue sugerida por Gali en el foro Exceluciones.
Esta macro se puede copiar al cuaderno Personal.xls, adjuntarle una combinación de teclas (por ejemplo, Ctrl+Mayúscula+T) y así tenerla a disposición en todo momento.



Categorías: Funciones&Formulas_

Technorati Tags: Funciones Excel