Algunos trucos con Autosuma en Excel

Todos conocemos el icono de Autosuma en Excel. Éste nos permite introducir la función SUMA con solo hacer un clic sobre él. Muchos de nosotros sabemos que este icono nos permite agregar muchas otras funciones

Si tenemos un rango de números contiguos (vertical u horizontalmente) y nos ubicamos en la primer celda libre por debajo (o a la derecha) del rango, al apretar Autosuma Excel reconoce automáticamente el rango y pone la fórmula. El rango automático siempre corre desde la última celda del rango (contigua a la celda libre donde queremos que aparezca la fórmula) y hasta la primer celda libre o la primera ocupada por una fórmula.

Si tenemos un rango con más de una columna, podemos totalizar las filas y las columnas con un solo clic a Autosuma. Seleccionamos el rango rectangular asegurándonos de incluir una fila y una columna en blanco

Al apretar Autosuma Excel pone las fórmulas correspondientes para las filas y las columnas



Otro truco interesante es el siguiente. Supongamos que tenemos esta serie de rangos de números, cada uno separado del otro por una fila libra



Con un solo clic podemos agregar un total para cada uno de los grupos. Empezamos por seleccionar los grupos dejando las celdas vacías fuera de la selección. Esto no lo haremos manualmente (supongamos que tenemos una gran cantidad de grupos) sino aplicando “Ir a” (Ctrl+G o F5). Seleccionamos toda la columna (o el rango adecuado) y usamos Ir A—Especial—Constantes—Números



Esto hará que sólo lo números sean seleccionados. Ahora apretamos el icono de Autosuma y veremos que Excel pone la fórmula en forma automática



Pero eso se pone más interesante cuando nos ponemos en la primer celda libre, en nuestro caso A15, y apretamos nuevamente Autosuma.

Excel selecciona automática las celdas que contienen la función SUMA y crea de hecho un suma de sumas



Si hay más de una fila libre entre cada grupo, este truco funciona parcialmente



Volvemos a los pasos anteriores (Ir A—Especial—Constantes—Números y apretar Autosuma) y veremos que Excel pone correctamente la función suma en la primer fila libre por debajo de cada grupo



Pero no podemos volver a aplicar Autosuma como en el caso anterior.

Technorati Tags: MS Excel

Búsqueda de archivos en Office

Si trabajan, como yo, intensamente con Office de Microsoft (Excel, Word, Access, PowerPoint, etc.) posiblemente sufran del problema como encontrar dónde diablos guardamos ese archivo…

Los programas del Office tienen una herramienta, poco conocida en mi opinión, que nos permite ubicar archivos por texto y por palabras claves.

Por ejemplo, tengo varios archivos de Excel con tablas para campeonatos de fútbol. Tienen en común que el texto “tabla de posiciones” forma parte del nombre del archivo. Para ubicarlos apretamos el botón de “Abrir” de la barra de herramientas (o Archivo-Abrir)



En el extremo superior derecho pulsamos Herramientas y Buscar. También podemos acceder a través del menú Archivo



En Excel 2003 existen ciertas diferencias funcionales entre ambas posibilidades.
Usando el primer método se abre este formulario



En “buscar texto” ponemos el texto de búsqueda y Tipo de archivo seleccionamos Excel. Al pulsar “Ir” comenzará la búsqueda de los archivos. En mi máquina la búsqueda dio estos resultados



Una vez seleccionado el archivo, apretamos Aceptar para abrirlo.
Una posibilidad más avanzada es usar palabras claves. El requisito es incluir las palabras claves en el formulario Propiedades del archivo. Por ejemplo, en los archivos cuyo tema es facturación con Excel, he puesto la palabra clave “factura”



Abrimos Herramientas-Buscar y apretamos la pestaña Avanzada. En Propiedad elegimos Palabras clave, en Condición elegimos “incluye” y apretamos el botón “Agregar”. Como estamos usando una sola propiedad no hay necesidad de elegir entre las posibilidades “Y” u “O”.



Apretamos “Ir” y podremos ver el resultado de la búsqueda



Como puede apreciarse este método es muy flexible y permite búsquedas de acuerdo a muchos criterios.

El método Archivo-Buscar tiene una interfaz levemente distinta

Technorati Tags: MS Excel

Ocultar valores cero en gráficos de Excel

En uno de los comentarios a mi nota sobre valores cero y vacíos en gráficos de Excel, me consulta una lectora cómo hacer para que estos valores no aparezcan en el gráfico.

Veamos esto con un ejemplo. Supongamos que llevamos una estadística sobre la ciudad de procedencia de turista y la queremos representar con un gráfico de columnas

De Murcia no han llegado turistas, pero de todas maneras aparece en el eje de las categorías (X). Lo que quiere mi lectora es una técnica para que las ciudades de las cuales no llegan turistas no aparezcan en el gráfico.

La solución que propongo en esta nota es usar tablas y gráficos dinámicos. Personalmente no me gustan los gráficos de las tablas dinámicas, pero en este caso nos resultan muy prácticos.

A partir de la tabla de turistas que mostramos más arriba creamos en una nueva hoja una tabla dinámica y su correspondiente gráfico

Por ahora este gráfico se ve tal como el anterior. Arrastramos el campo Turistas (el mismo que hemos puesto en el área de datos) al área de página




Seleccionamos con un clic el campo de página Turistas (en la celda A1) y abrimos el menú de configuración de campo. En la ventanilla Ocultar elementos marcamos el 0 y apretamos Aceptar




A partir de este momento, todas las ciudades con 0 turistas no aparecen en el gráfico




Finalmente podemos ocultar los botones de campo para que el gráfico parezca como un gráfico corriente de Excel

Technorati Tags: MS Excel

Filtro Avanzado en hojas protegidas – una rareza de Excel

Aplicamos Proteger a una hoja de Excel para impedir que el usuario haga cambios o modificaciones en la hoja. Excel nos permite definir excepciones. Por ejemplo, permitir que el usuario pueda usar Autofiltro (a condición que el autofiltro haya sido aplicado antes de proteger la hoja) u Ordenar

Si aplicamos Proteger Hoja sin excepciones, la opción Filtro y Mostrar Todo del menú Datos aparecen deshabilitadas



Si nos fijamos bien veremos que la opción Filtro Avanzado sigue activa. Los programadores de Excel se han olvidado de bloquear esta opción.

Así podemos aplicar Filtro avanzado para filtrar todas las filas donde el valor de la celda en la columna A sea mayor de 2500 a pesar de que la hoja está protegida



Uno de los problemas que surgen es que si bien la opción Filtro Avanzado sigue activa, la opción Mostrar Todo está bloqueada. Es decir, podemos filtrar, pero no podemos quitar el filtro.

Para mostrar las filas ocultas sin tener que cancelar la protección basta con abril el diálogo del Filtro Avanzado y borrar el rango de los criterios.

Microsoft conoce este problema desde la versión 2000, y en la última nota publicado en la base de conocimientos de Excel , Advanced Filter May Function on Protected Worksheet, mencionan que por el momento (noviembre del 2003!) no hay forma de impedir la aplicación de Filtro Avanzado en una hoja protegida.

Una solución posible es proteger todas las celdas y no permitir seleccionar todas las celdas donde pudieran definirse criterios para filtro avanzado. De esta manera, si bien el diálogo del filtro avanzado se abrirá, no habrá posibilidad de definir un rango de criterios, lo que hace que todas las filas queden visibles.

Technorati Tags: MS Excel

Valores vacíos y cero en gráficas de Excel

A pesar que las series de valores de estos gráficos parecen idénticas, los gráficos son distintos

En la primer serie de datos (Valores 1), las celdas A5 y A8 están vacías, es decir, no contienen ni valores ni fórmulas. En cambio la celdas A17 y A20 (serie Valores 2) si bien se las ve vacías, no lo están. La celda A17 contiene la fórmula =SI(A5<>0,A5,"").

Por ese motivo, en el primer gráfico la línea aparece discontinua y en el segundo la línea es continua y quebrada. Esto significa que Excel interpreta el resultado de la fórmula, (“”) como si fuera cero a pesar que en pantalla la celda aparece vacía.

Consideremos la serie Valores 1 y supongamos que queremos obtener una línea continua. Para lograrlo seleccionamos el gráfico y abrimos el menú Herramientas-Opciones. En la pestaña Gráfico seleccionamos la opción “Trazar Celdas vacías como…” --> Interpolar

La línea aparecerá ahora continua



En el caso de Valores 2 dado que las celdas A17 y A20 no están vacías, esta técnica no nos sirve. La solución consiste en cambiar la fórmula que genera los valores de la serie de

=SI(A5<>0,A5,"")

a

=SI(A5<>0,A5,NOD())

La función NOD() todo lo que hace es poner un valor de error #N/A en la celda. El resultado es

Si queremos que los valores #N/A no sean visibles podemos aplicar formato condicional dándole a la fuente el mismo color como el fondo de la celda

Technorati Tags: MS Excel

Extraer palabras de una frase en Excel

Me llegó esta consulta: ¿cómo puedo extraer de una frase la cuarta palabra sin importar la cantidad de palabras en la frase? Por ejemplo, si en una celda tenemos la frase: “Cuántas palabras hay en esta frase” , cómo podemos extraer la tercer palabra, “hay”.

Chip Pearson trae una solución basada en funciones matriciales que desarrolló Laurent Longre.

Por ejemplo, si ponemos la frase mencionada en la celda A2 y en B2 el número de orden de la palabra a extraer, esta fórmula dará el resultado esperado

=EXTRAE(A2,K.ESIMO.MENOR(SI(EXTRAE(" "&A2,FILA(INDIRECTO("1:"&LARGO(A2)+1)),1)=" ",FILA(INDIRECTO("1:"&LARGO(A2)+1))),B2),SUMA(K.ESIMO.MENOR(SI(EXTRAE(" "&A2&" ",FILA(INDIRECTO("1:"&LARGO(A2)+2)),1)=" ",FILA(INDIRECTO("1:"&LARGO(A2)+2))),B2+{0;1})*{-1;1})-1)

Podemos extender la cuestión a cómo extraer de una frase una cadena de palabras a partir de una posición determinada.

Por ejemplo , en la celda A3 ponemos la frase “ En esta frase hay muchas más palabras que en la anterior”; si queremos extraer la cadena formado por las tres palabras a partir de la cuarta palabra en la frase (“hay”), usamos esta fórmula donde en B3 ponemos el número de orden la palabra del comienzo y en C3 la cantidad de palabras a extraer

=EXTRAE(A3,K.ESIMO.MENOR(SI(EXTRAE(" "&A3,FILA(INDIRECTO("1:"&LARGO(A3)+1)),1)=" ",FILA(INDIRECTO("1:"&LARGO(A3)+1))),B3),SUMA(K.ESIMO.MENOR(SI(EXTRAE(" "&A3&" ",FILA(INDIRECTO("1:"&LARGO(A3)+2)),1)=" ",FILA(INDIRECTO("1:"&LARGO(A3)+2))),B3+C3*{0;1})*{-1;1})-1)

Recordemos que éstas son funciones matriciales, es decir que deben ser introducidas apretando simultáneamente Ctrl+Mayúsculas+Enter

Con todo el respeto debido a Chip Pearson y a Laurent Longre (que lo tienen bien merecido), estas soluciones tienen dos problemas. El primero y más importante es que las funciones matriciales son muy “pesadas” y si se usan con cierta intensidad en una hoja el cálculo de la hoja (cada vez que cambia un dato) toma mucho tiempo. El segundo problema es que si hay más de un espacio entre las palabras de la frase o hay espacios al principio de la frase se obtienen resultados erróneos.

Mi propuesta es usar funciones definidas por el usuario (UDF). En mi primer intento escribí esta función que extrae una palabra determinada de la frase. La función tiene dos argumentos: la celda que contiene la frase y la posición de la palabra a extraer.

El código de la función es:


Function extraer_palabra(frase As Range, palabra As Integer)

Dim arrFrase As Variant

arrFrase = Split(WorksheetFunction.Trim(frase), " ")

extraer_palabra = arrFrase(palabra - 1)


End Function

Como ven, estoy usando la función TRIM de Excel y no la de Vba. Esto se debe a que ambas funciones no se comportan de la misma manera y sólo la de Excel quita todos los espacios dejando sólo uno entre cada palabra.

Como en el caso anterior, vamos a ampliar nuestra función de manera que podamos extraer una cadena de palabras de una frase en una celda. La función, “ extraer_palabra2”, tiene tres argumentos: la celda que contiene la frase, la posición de la primer palabra de la cadena a extraer y la cantidad de palabras a extraer.

El código de la función es:

Function extraer_palabra2(frase As Range, _
palabra1 As Integer, cuantas As Integer)

Dim arrFrase As Variant, iX As Long, temp



arrFrase = Split(WorksheetFunction.Trim(frase), " ")

extraer_palabra2 = arrFrase(palabra1 - 1)

For iX = palabra1 + 1 To palabra1 + cuantas - 1

extraer_palabra2 = extraer_palabra2 & " " & arrFrase(iX - 1)

Next iX

End Function



Por ejemplo, queremos extraer una cadena de cuatro palabras a partir de la tercera: “hay en esta frase”

Obtenemos



Estas funciones podemos guardarlas en el cuaderno Personal.xls para que estén disponibles en todo momento.

Technorati Tags: MS Excel

Como definir un icono para agregar una hoja en Excel

Me considero una persona modesta. Es decir, alguien que se conforma con poco, con lo que hay. Tal vez por eso me sorprende encontrar de tanto en tanto, en particular en ciertos foros, gente que se queja de Excel.
Es verdad, hay ciertos aspectos de Excel que pueden volverse molestos, ciertas funcionalidades que faltan o cosas que no funcionan como quisiéramos. Pero por otra parte Excel nos ofrece suficientes herramientas para corregir, aunque se parcialmente, estas situaciones. Un ejemplo mostramos ayer en la nota sobre cómo agregar un icono para cancelar el autofiltro.

Otra funcionalidad que encuentro un tanto molesta en Excel es cuando agregamos una hoja a un cuaderno (en particular por mi mala costumbre de no planear el diseño del cuaderno de antemano y verme obligado a agregar hojas a medida que el proyecto avanza).

En Excel 2003 (y versiones anteriores hasta Excel 97, si no me equivoco) podemos agregar una hoja con uno de estos métodos:

# - abriendo el menú insertar en la pestaña de la hoja

# - con el icono del comando Insertar hoja de cálculo (que podemos arrastrar y ubicar en alguna de las barras de herramientas)



El problema con estos métodos es que no tenemos control de donde será ubicada la hoja. Lo natural sería que a la derecha de la última, pero no es esto lo que sucede. Por algún motivo Excel pone la nueva hoja a la izquierda de la hoja activa. Además, Excel pone un nombre por defecto. Mi idea es que Excel inserte la nueva hoja a la izquierda de la última del cuaderno y que nos invite a poner el nombre antes de crearla.

Para hacer esto usaremos una macro que luego ligaremos al icono de insertar hoja de cálculo. La macro la guardamos en el cuaderno Personal.xls, para que esté disponible para todo cuaderno de Excel con el cual estemos trabajando.

El código de nuestra macro es:

Sub agregar_hoja_con_nombre()
'02/03/2009 by Jorge Dunkelman

Dim shName As String

If ActiveWorkbook Is Nothing Then Exit Sub

shName = Application.InputBox(prompt:="Nombre de la hoja?", Title:="Nombre", _
Type:=2)

Select Case shName
Case Is = "False"
MsgBox "No se ha insertado una hoja"
Exit Sub
Case Is = ""
Sheets.Add after:=Sheets(Sheets.Count)
Case Else
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = shName
End Select

End Sub


Al correr la macro comprueba en primer lugar si hay un cuaderno abierto (de lo contrario no hay donde agregar la hoja!). Luego se abre una ventanilla donde se nos pide definir el nombre de la hoja. En caso de dejarlo en blanco se insertará una hoja con el nombre por defecto (Hoja4, Hoja5, etc.); en caso de apretar Cancelar aparecerá un mensaje diciendo que no se ha insertado la hoja y en caso de poner un valor éste aparecerá en la pestaña de la hoja.

La hoja siempre se inserta a la derecha de la última hoja del cuaderno sin tomar en cuenta cuál era la hoja activa en ese momento.

Estamos usando el método InputBox (y no la función InputBox de Vba), para poder distinguir cuando el usuario aprieta Cancelar de cuando deja el nombre en blanco.

Ahora sólo nos queda por crear el icono y agregarlo a alguna de las barras. Podemos crear un icono de la categoría macro (como vimos en la nota anterior) y luego cambiar la imagen copiándola del icono de insertar hoja que existe en Excel.

Para hacer esto abrimos el menú Herramientas-Personalizar y usamos Copiar imagen

Luego elegimos el icono que acabamos de crear y pegamos la imagen (y por supuesto, lo ligamos a la macro que guardamos en el cuaderno Personal.xls)

La técnica para crear el icono en Excel 2007 está explicada en esta nota.

Technorati Tags: MS Excel