viernes, 10 de diciembre de 2010

% sobre Totales en Tablas Dinámicas

"He leído tu post titulado Campos Calculados en Tablas Dinámicas y me gustaría saber que debo hacer para calcular el peso de cada columna sobre el total general"

El ejemplo de partida de dicho post es la siguiente tabla dinámica:


Como se puede ver se trata de una tabla dinámica con el detalle por zonas de los ingresos, los costes directos y el margen bruto. Lo que queremos conseguir es que esta información nos la muestre como porcentajes sobre los totales de cada columna para ver "el peso" de cada zona sobre el total.

Si lo que queremos es simplemente sustituir las cifras por porcentajes la operación es muy sencilla. A saber:

Nos situamos en la celda B4 y pulsamos el botón derecho del ratón. Aparecerá el siguiente menú emergente, en el que seleccionamos la opción Configuración de campo:


En la ventana que se abre pulsamos el botón Opciones:


Abrimos Mostrar datos como y seleccionamos la opción % del total:


Pulsamos Aceptar y objetivo conseguido (obviamente tendremos que repetir la misma operación para las otras dos columnas):



Si lo que queremos es que aparezcan ambos datos (la cifra y el porcentaje que representa sobre el total) sólo tendremos que duplicar las tres columnas que tenemos (arrastrando los campos a la zona de datos de la tabla dinámica) y posteriormente seguir los pasos que acabamos de explicar. Vamos a verlo con la columna de Ingresos Zona:

Tabla inicial:


Campos de la tabla dinámica:


"Arrastramos" el campo Ingresos dentro de la tabla dinámica en la zona de Datos y soltamos. Aparecerá, al final de la tabla, nuevamente la suma de ingresos por zona:


Aplicamos en esta columna los pasos explicados para calcular el porcentaje sobre el total (podemos aprovechar para cambiar el nombre de este campo dentro del menú Configuración de campo/Nombre) :


Ya sólo nos queda colocar esta columna a la derecha de Ingresos Zona. Para ello nos situamos dentro de la columna que hemos denominado % Ingr/Total y pulsamos el botón derecho del ratón. Utilizamos la opción Ordenar:


El resultado será el esperado:

jueves, 2 de diciembre de 2010

Mostrar/Ocultar Información con Formato Condicional


"Me gustaría tener una casilla de verificación que al marcarla apareciera una tabla con un análisis de sensibilidad de una cuenta de resultados y que al desactivar dicha casilla de verificación desapareciera la tabla (incluidos los bordes)".

Vamos a utilizar el siguiente ejemplo:


Tenemos una entrada de datos para el precio de un producto y otra entrada para la cantidad. Debajo hemos colocado una casilla de verificación con el texto "ver análisis de sensibilidad". Para crear esta casilla de verificación vamos al menú Ver/barras de herramientas/Formulario y, en la nueva barra que aparece, hacemos clic encima de la casilla de verificación:


La dibujamos en la hoja, nos ponemos encima de la casilla dibujada y hacemos clic en el botón derecho del ratón. Se abrirá un menú emergente del que seleccionamos la opción Formato de control. Se abrirá una ventana y en la pestaña Control en Valor seleccionamos Sin Activar y en Vincular con la celda hacemos clic en la celda G2. Pulsamos Aceptar. A partir de este momento cada clic en la casilla de verificación se convertirá en un VERDADERO o FALSO en la celda G2.

Lo que queremos conseguir es lo que se muestra en las dos siguientes imágenes:



1. Seleccionamos el rango E3:F11 y dibujamos los bordes de la tabla.
2. En el rango E3:E11 introducimos los precios que queremos evaluar en el análisis de sensibilidad.
3. En la celda F2 introducimos la fórmula =C3*C4
4. Seleccionamos el rango E2:F11 y vamos al menú Datos/Tabla.
5. En la ventana que se abre en Celda de entrada columna seleccionamos la celda C3 y pulsamos Aceptar.


El resultado obtenido será el siguiente:


6. Ocultamos la fila 2 para que no se vea la fórmula de enlace ni el resultado de la casilla de verificación (el verdadero o falso).
7. Seleccionamos el rango E3:F11 y vamos al menú Formato/Formato condicional.
8. En Condición 1 seleccionamos Fórmula y en el cuadro de la derecha escribimos lo siguiente =$G$2=FALSO
9. Pulsamos el botón Formato y en la pestaña Fuente optamos por el color de fuente blanco. En la pestaña Bordes pulsamos la opción Ninguno:


10. Pulsamos Aceptar y objetivo conseguido.

miércoles, 1 de diciembre de 2010

Resaltar un Dato Concreto de una Tabla

"Aprovechando el ejemplo utilizado en el anterior artículo ( Destacar Toda una Fila de una Tabla de Datos) me gustaría saber cómo destacar un dato concreto de dicha tabla, es decir, el cruce entre una fila y una columna (un año y un mes concreto en el ejemplo)".

La solución es muy parecida a la mostrada en el anterior post. Tendremos que incluir una nueva entrada de datos que haga referencia al mes y utilizar la función lógica Y dentro del Formato Condicional. La tabla de la que partimos es la misma que ya utilizamos en el anterior artículo, a saber:


Para destacar un sólo dato necesitamos incluir en el modelo una nueva entrada de datos que será el mes. Para ello introducimos el rótulo en E2 como se ve en la imagen y nos situamos en la celda F2 y vamos al menú Datos/Validación/Permitir/Lista y en Origen seleccionamos el rango C10:N10 y pulsamos Aceptar.


Ahora ya podemos indicar el año y el mes que queremos destacar. A continuación seleccionamos el rango C11:N19 y vamos al menú Formato/Formato condicional. En Condición 1seleccionamos Fórmula y en el cuadro de la derecha escribimos la siguiente:

=Y($B11=$C$2;C$10=$F$2)

Pulsamos el botón Formato y elegimos, por ejemplo, trama amarilla y número en negrita y pulsamos Aceptar. El resultado será el siguiente:


En realidad sólo se destacará el dato dentro de la tabla. Para que también destaque el año y el mes debemos hacer lo siguiente:

Para el mes:
1. Seleccionamos el rango C10:N10
2. Vamos al menú Formato/Formato condicional y seleccionamos Fórmula.
3. En el cuadro de la derecha escribimos la fórmula: =C$10=$F$2
4. Pulsamos el botón Formato y elegimos los colores de trama y de fuente
5. Pulsamos Aceptar.

Para el año:
1. Seleccionamos el rango B11:B19
2. Vamos al menú Formato/Formato condicional y seleccionamos Fórmula.
3. En el cuadro de la derecha escribimos la fórmula: =$B11=$C$2
4. Pulsamos el botón Formato y elegimos los colores de trama y de fuente
5. Pulsamos Aceptar.

martes, 30 de noviembre de 2010

Destacar toda una Fila de una Tabla de Datos


"Tengo una tabla con datos históricos de ventas por meses. La utilizo habitualmente en presentaciones y me ayudaría mucho el poder seleccionar un año y que se destacara la información de dicho ejercicio".

No problema. La solución es muy sencilla (cosa que se agradece a estas alturas del año) haciendo uso del Formato Condicional. Partimos del siguiente ejemplo:


Introducimos la entrada de datos como sigue:


Nos situamos en la celda C2 y vamos al menú Datos/Validación y seleccionamos Permitir/Lista. En el apartado Origen seleccionamos el rango B11:B19 y pulsamos Aceptar. De esta manera ya tenemos nuestra lista desplegable con los años representados en la tabla:


A continuación seleccionamos todos los datos de la tabla, es decir, el rango C11:N19 (los rótulos no). Vamos al menú Formato/Formato condicional y lo configuramos de la siguiente manera:

En la Condición 1 seleccionamos la opción Fórmula y en el cuadro de la derecha escribimos la fórmula:

=$B11=$C$2

Pulsamos el botón Formato y seleccionamos el formato concreto que queremos que adopte toda la fila a destacar (por ejemplo trama amarilla y números en negrita) y pulsamos Aceptar:


¡Objetivo conseguido! Cuando seleccionemos un año en la celda C2 toda la fila de la tabla correspondiente a dicho año se formateará como le hayamos indicado:


domingo, 21 de noviembre de 2010

Búsqueda con Columna Variable (3 soluciones)


"Tengo una tabla con información de empleados y me gustaría tener como variable una celda donde indicarle la información requerida para que me devuelva la información existente en la intersección entre el empleado que aparece en la primera columna de la tabla y una columna variable".

Aunque una posible solución ya la tenéis en el post "Búsqueda con Tablas e Indicador de Columna variable", y dado que me han llegado varias consultas muy similares, vamos a ampliar dicho artículo planteando tres soluciones distintas con las siguientes funciones: doble BUSCARV, BUSCARV e INDIRECTO y, finalmente, INDICE y COINCIDIR. Partimos del siguiente ejemplo:


Planteamos la siguiente entrada de datos:


En la celda C2 seleccionaremos el empleado del que queremos la información y en la celda C3 la información concreta que necesitamos. Empezamos creando con la herramienta de Validación de Datos las listas de entrada de datos. Para ello nos situamos en la celda C2 y vamos a l menú Datos/Validación y lo formulamos como se muestra en la imagen:


Pulsamos Aceptar y ya tendremos nuestra lista desplegable en C2. Para hacer lo mismo en C3 debemos dar algún paso intermedio. El motivo es que el contenido de la lista de C3 serán los rótulos de los campos de la tabla. Dicho rótulos se encuentran en disposición horizontal y la herramienta de validación no nos permite que el origen de la lista tenga dicha disposición. Lo único que tenemos que hacer es seleccionar el rango B10:F10 y pulsar Copiar. A continuación nos situamos en la celda H3 y vamos a Edición/Pegado Especial y seleccionamos Transponer, y pulsamos Aceptar. En la columna I añadimos la numeración que se muestra en la imagen y que después veremos su uso:


Ahora sí podemos situarnos en C3 y proceder a genera la lista desplegable (Datos/Validación):



1. Solución con Doble BUSCARV
Seleccionamos el rango B11:F20 y vamos al cuadro de nombres (a la izquierda de la barra de fórmulas). Hacemos un clic y escribimos el nombre Datos y pulsamos Enter. A continuación hacemos lo mismo con el rango H3:I7 y le damos el nombre, por ejemplo, ncol. Esta tabla no es otra cosa que el nombre de los distintos campos de la tabla y el número de columna que representa dentro de dicha tabla (que hemos denominado Datos). Nos situamos en la celda B5 y escribimos la siguiente fórmula:

=BUSCARV(C2;datos;BUSCARV(C3;ncol;2;FALSO);FALSO)

Lo que estamos haciendo es anidar un BUSCARV en el argumento número de columna de dicha función. Como se puede ver en la siguiente imagen, seleccionamos en C2 el empleado que queremos consultar (empleado7) y después en C3 seleccionamos de la lista la información que necesitamos:



2. Solución con BUSCARV e INDIRECTO
Podemos obtener el mismo resultado anidando la función INDIRECTO en el argumento número de columna de la función BUSCARV. Para ello seleccionamos el rango H3:I7 y vamos al menú Insertar/Nombre/Crear. Aparecerá la ventana que se muestra en la siguiente imagen y en donde debemos seleccionar (ya aparece seleccionada por defecto) la opción columna izquierda:


Pulsamos Aceptar. A partir de este momento en el cuadro de nombres aparecerán todos los nombres de los rótulos de la tabla de datos. Al pulsar cualquiera de estos nombres nos llevará al número de columna que representan. Y esto es lo que precisamente nos ayudará con la función INDIRECTO. Nos situamos en la celda D5 y escribimos la siguiente fórmula:

=BUSCARV(C2;datos;INDIRECTO(C3);FALSO)



3. Solución con INDICE y COINCIDIR
La función INDICE nos devuelve la intersección de una fila y columna de una matriz. Nuestra matriz es la tabla que hemos denominado Datos. El número de fila que queremos dependerá del empleado que seleccionemos en C2. Para calcular a qué fila se refiere introducimos la función COINCIDIR. El número de columna dependerá de la información solicitada en C3. Utilizamos también la función COINCIDIR para obtener el número de columna. La fórmula que debemos escribir en F5 es:

=INDICE(datos;COINCIDIR(C2;B11:B20;0);COINCIDIR(C3;B10:F10;0))


miércoles, 27 de octubre de 2010

Problemas en el Cálculo del Valor Actual Neto


"Estoy intentando realizar el cálculo del Valor Actual Neto (VAN) de una inversión y obtengo un resultado incorrecto".

El ejemplo que nos envía es el siguiente:


La función VAN en excel presenta diversas peculiaridades:

a) El nombre de la función en excel es VNA, y no VAN.
b) El desembolso inicial o inversión no debe introducirse dentro de la función, sino fuera de ésta sumando.
c) El rango de flujos de caja considerado en la función no debe contener ninguna celda en blanco. Si, como es el caso, esperamos un flujo igual a cero en algún periodo del proyecto debemos escribir cero como valor en dicho periodo. Este es precisamente el error que nos encontramos en este caso.

Nos situamos en la celda C13 y escribimos la fórmula:

=VNA(C3;C6:C11)+C5

El primer argumento hace referencia a la tasa de descuento. En nuestro ejemplo se trata del Tipo de Interés Nominal Anual (TIN) que se encuentra en la celda C3.
El segundo argumento es el rango donde se encuentran los flujos de caja (sin incluir el desembolso inicial, como ya he indicado). En nuestro caso el rango C6:C11.
Finalmente, fuera de la función VNA sumamos (ya que se encuentra con signo negativo) el desembolso inicial.

La fórmula está perfectamente planteada. El problema en este caso es que en los años 3 y 4, en los que esperamos un flujo de caja igual a cero, hemos dejado las celdas en blanco. Con este planteamiento excel interpreta que el flujo del año 3 (que se encuentra en blanco) es el de la siguiente celda del rango que contenga un valor, es decir, el del año 5 en nuestro ejemplo. En el año 4 interpreta que su valor es el del año 6. Evidentemente esto genera un error a la hora de actualizar los valores. La solución es tan sencilla como introducir los valores cero en las celdas que hemos dejado en blanco, tal y como se muestra a continuación:


sábado, 16 de octubre de 2010

Cálculo de la Frecuencia de la Moda


"En un post de tu blog , explicas cómo hallar el valor más repetido en un rango (la moda). Lo que yo necesito es, además de saber dicho valor, saber cuántas veces se repite".

El post al que te refieres es Cálculo del Valor más Frecuente (MODA) de un Rango. Para dar solución a tu pregunta utilizaremos las funciones CONTAR.SI y MODA. Partimos del siguiente ejemplo que muestra las valoraciones obtenidas en las encuestas realizadas a cuatro tiendas:


Nos situamos en la celda B17 y escribimos la siguiente fórmula:

=CONTAR.SI(B4:B15;MODA(B4:B15))

De esta manera lo que estamos haciendo es utilizar como criterio para contar el valor de la moda, es decir, que cuente cuántas veces se repite el valor de la moda en el rango B4:B15 . En nuestro ejemplo sólo nos quedaría copiar la fórmula introducida en B17 en el rango C17:E17 .

Si además queremos tener disponible el valor en si de la Moda podemos escribir en B19 la fórmula:

=MODA(B4:B15) y copiar esta fórmula en el rango C19:E19


Como se puede comprobar, el valor de la Moda de las encuestas de la Tienda 1 es 2 y dicho valor se repite 4 veces.

jueves, 9 de septiembre de 2010

Cálculo de Número de Días


"Necesito calcular, cada día, cuántos días llevan transcurridos desde el 1 de enero del año en curso y los días que faltan para concluir el año ¿hay alguna función que realice estos cálculos?"

No existe ninguna función directa que realice estos cálculos, pero la solución es bien sencilla utilizando las funciones FECHA y AÑO. Supongamos que deseamos contar con la siguiente información actualizada diariamente:


1. la fórmula de B2 es muy sencilla:

=HOY()

2. La fórmula de B3 es igualmente sencilla utilizando la función NUM.DE.SEMANA Esta función tiene tan sólo dos argumentos NUM.DE.SEMANA(Num_de_serie;Tipo). El primer argumento se refiere a la fecha de la que queremos saber el número de semana del año que le corresponde. El segundo argumento sirve para determinar el tipo de semana, esto es, si la semana comienza el domingo, en cuyo caso escribiremos el valor 1, o si queremos considerar semanas cuyo primer día sea el lunes, en cuyo caso el valor de este argumento será 2. Así las cosas, la fórmula resultante será:

=NUM.DE.SEMANA(B2;2)

3. Para calcular los días transcurridos desde el 1 de enero del año en curso hasta la fecha actual, utilizamos la siguiente fórmula:

=B2-FECHA(AÑO(B2);1;1) Si queremos añadir un texto descriptivo al resultado podemos utilizar el operador & (CONCATENAR):
=B2-FECHA(AÑO(B2);1;1)&" días desde principio del año"

4. Finalmente para saber cuántos días restan para concluir el año en curso:
=FECHA(AÑO(B2);12;31)-B2 Añadiendo un texto descriptivo sería:
=FECHA(AÑO(B2);12;31)-B2&" días para terminar el año"



Si queremos mantener esta información siempre visible en la hoja, podemos utilizar la opción de Inmovilizar Paneles. Para ello nos situamos en la celda A7 y vamos al menú Ventana/Inmovilizar Paneles. Aparecerá un borde superior en toda la fila 7 quedando inmovilizadas las celdas que se encuentren por encima de dicha linea.

miércoles, 8 de septiembre de 2010

Repetición de Caracteres Concretos en una Celda


Pues parece que fue ayer pero ya ha pasado más de un mes desde mi última entrada. Prometí a mis hijos no acercarme a una hoja de cálculo durante las vacaciones y... ¡casi lo cumplo!

Me habéis mandado numerosas preguntas (supongo que debo daros las gracias...) que intentaré contestar lo antes posible. Una de las más repetidas ha sido la siguiente:

"Necesito saber el número de espacios en blanco que contiene un grupo de celdas".

Vamos a utilizar una fórmula bastante sencilla, que sé que leí hace tiempo en algún lado pero no recuerdo donde, con las funciones LARGO y SUSTITUIR. Partimos del siguiente ejemplo:


Queremos saber cuántos espacios en blanco contiene cada celda (cuestión que aunque no lo parezca puede resultar muy útil para, por ejemplo, realizar fórmulas que separen en distintas celdas los nombres y los apellidos). Lo que vamos a hacer en esencia es:

1. Contar el número total de caracteres que contiene la celda B3
2. Borrar todos los espacios en blanco del texto de B3 y escribir el resultado en C3.
3. Contar el número total de caracteres de C3.
4. Hallar la diferencia entre ambos totales. Dicha diferencia será, obviamente, el número de espacios en blancos que tiene la celda B3.

Vamos a empezar, con el objetivo de que se entienda mejor, por el paso 2. Para ello utilizaremos la función SUSTITUIR. Esta función sustituye dentro de una cadena de texto un texto original por otro nuevo específico. La sintaxis de esta función es:

SUSTITUIR(texto;texto_original;texto_nuevo; núm_de_ocurrencia)

Texto:es el texto o la referencia a una celda que contiene texto en el que desea sustituir caracteres.

Texto_original: es el texto que desea reemplazar.

Texto_nuevo: es el texto por el que desea reemplazar texto_original.

Núm_de_ocurrencia: especifica la instancia de texto_original que desea reemplazar por texto_nuevo. Si especifica el argumento núm_de_ocurrencia, sólo se remplazará esa instancia de texto_original. De lo contrario, todas las instancias de texto_original en texto se sustituirán con texto_nuevo.

Así las cosas, nos situamos en la celda C3 y escribimos la siguiente fórmula:

=SUSTITUIR(B3;" ";"") Fíjese que el segundo argumento es "espacio" y que el tercero es "" sin ningún espacio. Le estamos pidiendo a excel que sustituya los espacios en blanco que se encuentre por nada. El resultado de esta fórmula será este:


Como puede comprobar, el contenido de la celda C3 es el mismo que el de la celda B3 pero sin espacios en blanco.

Una vez hecho esto el resto es muy sencillo. Sólo nos queda "medir" el largo de ambas celdas y hallar la diferencia. Para ello hacemos lo siguiente:

en D3 escribimos: =LARGO(B3)
en E3 escribimos: =LARGO(C3)
en F3 escribimos: =B3-C3

¡Problema resuelto! Evidentemente si sólo necesitamos el número de espacios en blanco podemos resumir todas estas fórmulas en la siguiente (que escribo en la celda C3):

=LARGO(B3)-LARGO(SUSTITUIR(B3;" ";""))


En este caso hemos contado el número de espacios en blanco pero puede utilizar esta fórmula con otros caracteres simplemente sustituyendo la expresión " " por "a" ,por ejemplo.

sábado, 24 de julio de 2010

Buscar Dentro de Fórmulas


"Tengo una hoja con muchos datos y con cálculos de sumatorios parciales dispersos por dicha hoja. Además de dichos sumatorios tengo otras fórmulas. Necesito localizar todas las celdas en donde existe una fórmula que esté utilizando la función SUMA, ya que debo revisar que los rangos aplicados en esta función sean correctos".


La solución a este problema es muy sencilla utilizando la herramienta Buscar. Para ello vamos a utilizar el siguiente ejemplo:


Se trata de una tabla en la que tenemos diversos datos y diversas fórmulas y lo que pretendemos es localizar aquellas que contengan la función SUMA. Para ello seguimos los siguientes pasos:

1. Vamos a l menú Edición/Buscar. Se abrirá la siguiente ventana:


2. Dentro del campo Buscar: escribimos la palabra SUMA y comprobamos que dentro de del campo Buscar dentro de: tenemos seleccionado Fórmulas. Pulsamos el botón Buscar todo. El resultado será el siguiente:


Una vez resuelto el problema planteado en esta consulta, tenemos muchas opciones para trabajar. Lo primero que cabe destacar es que esta herramienta nos presenta un listado de todas las celdas que contienen la función buscada con su fórmula correspondiente. De esta manera ya podemos comprobar desde la propia herramienta si el rango de SUMA es el que nos interesa. Además podemos hacer clic en cada uno de los resultados de la búsqueda y excel selecciona automáticamente la celda a la que hace referencia:


En esta imagen puede comprobar que al seleccionar el primer resultado de la búsqueda excel selecciona la celda en la hoja a la que hace referencia (celda B11).

También podemos realizar una selección múltiple marcando, por ejemplo, el primer resultado de la búsqueda y haciendo clic con la tecla Shift pulsada en el último resultado de la búsqueda:


Como puede comprobar en la imagen excel selecciona las tres celdas implicadas, a saber: B11, C11 y E11.

Finalmente también puede realizar una selección discontinua pulsando la tecla Ctrl y haciendo clic en los resultados de la búsqueda que le interese (por ejemplo el primero y el último):


Evidentemente, las opciones de esta herramienta son mucho más amplias y, por lo tanto, serán objeto de futuros posts.