jueves, 21 de julio de 2011

Excel - Cuadro Resumen de Personal utilizando la Función SI anidada

Hola, después de algunos días estoy de vuelta y en esta ocasión les explicaré como elaborar un Cuadro Resumen de Personal utilizando la función "SI" de manera anidada y lo complementaremos con tablas dinámicas para una mejor presentación final.

Tomaremos como ejemplo una relación de trabajadores con sus fechas de inicio y fin de contrato.  Con dicha información elaboraremos un cuadro resumen por meses en el cual se pueda visualizar la cantidad de trabajadores que hubo en cada mes.

Pasos:

1. Debemos tener nuestra información en una tabla con los siguientes datos:



2.  Para poder calcular el tiempo de trabajo por meses aplicaremos el siguiente truco: debemos de tener columnas con los meses ya separados, algo similar a esto:


3.  Nos colocamos en la celda D2 e ingresamos la siguiente fórmula:

=SI($B2=$D$1,1,SI($C2=$D$1,1,SI(Y($B2<$D$1,$C2>$D$1),1,0)))

Donde:
$B2 --> es la celda donde se encuentra la FECHA INICIAL.
$C$2 --> es la celda donde se encuentra la FECHA FINAL.
$D$1 --> es el mes correspondiente al mes de trabajo.
1 --> si la condición se cumple.
0 --> si la condición no se cumple.
Y --> es el conector entre ambas condiciones.

La interpretación de la fórmula podría ser de esta manera:
"Si la fecha inicial es igual al mes de trabajo indicar 1, sino, si la fecha final es igual al mes de trabajo indicar 1, sino, si la fecha inicial es menor que la del mes de trabajo y además, la fecha final es mayor a la del mes de trabajo entonces indicar 1, caso contrario indicar 0”.

3. La misma fórmula debemos de copiarla en cada celda de la fila 2, variando únicamente lo que corresponde al mes de trabajo $D$1, en donde por ejemplo la fórmula de la celda E2 sería la siguiente:

=SI($B2=$E$1,1,SI($C2=$E$1,1,SI(Y($B2<$E$1,$C2>$E$1),1,0)))

3. Una vez que hemos copiado y modificado la fórmula en todas las celdas de la fila 2, tendremos un cuadro como éste:


4. Ahora seleccionamos todo el rango D2:O2, lo copiamos y lo pegamos en todas las celdas restantes de nuestro cuadro, quedándonos un cuadro igual al siguiente:


5. Es el momento de generar nuestra tabla dinámica para lo cual seleccionamos todo el rango A1:O11, que es donde se encuentra toda la información con la cual vamos a trabajar.

6.  Del menú INSERTAR seleccionamos la opción TABLA DINÁMICA y debe de aparecer una ventana similar a ésta:


6. De preferencia elegimos colocar la tabla dinámica en una NUEVA HOJA DE CÁLCULO, para lo cual hacemos clic en dicha opción y luego en ACEPTAR.  Nos debe de aparecer una nueva hoja de cálculo similar a la que mostramos a continuación:


7. En la Lista de Campos de la tabla dinámica que se encuentra ubicada en la columna de la derecha arrastramos el campo APELLIDOS Y NOMBRES al área de RÓTULOS DE FILA y los campos de cada mes (Ene-09, Feb-09, Mar-09,…) al área de ∑ VALORES, quedando finalmente de la siguiente manera:


Espero que no les haya parecido complicado, pero de todas maneras cualquier duda no dejen de escribirme...

Hasta la próxima,

BrAiNhA.

jueves, 14 de julio de 2011

Excel - Cuadro Resumen de Personal con Tablas Dinámicas

Hola nuevamente, como recordarán mi primer post fue sobre como aprender a elaborar un Cuadro Resumen de Personal utilizando la función "Contar.SI", pues esta vez lo realizaremos mediante tablas dinámicas, no es necesario el tener muchos conocimientos sobre tablas dinámicas, luego de este ejemplo verán que estoy en lo cierto.

Pasos:

1. Debemos tener nuestra información en una tabla de personal con los datos básicos para nuestro cuadro resumen similar a ésta:




2. Seleccionamos todo el rango B4:E24, que es donde se encuentra toda la información con la cual vamos a trabajar.

3. Del menú INSERTAR seleccionamos la opción TABLA DINÁMICA y debe de aparecer una ventana similar a ésta:


4. De preferencia elegimos colocar la tabla dinámica en una NUEVA HOJA DE CÁLCULO, para lo cual hacemos clic en dicha opción y luego en ACEPTAR.  Nos debe de aparecer una nueva hoja de cálculo similar a la que mostramos a continuación:


5. En la Lista de Campos de la tabla dinámica que se encuentra ubicada en la columna de la derecha arrastramos el campo OFICINA al área de RÓTULOS DE FILA y el campo CARGO al área de RÓTULOS DE COLUMNA, quedando de la siguiente manera:


6. Hacemos algo similar con el campo APELLIDOS Y NOMBRES el cual arrastramos al área de ∑ VALORES, con lo cual ya tenemos todas las celdas con la información correspondiente.

7. Pero como podemos observar, luego de estos pasos nos han quedado muchas columnas, lo cual no es recomendable para la presentación de un cuadro de este tipo.  Para solucionarlo debemos agrupar los cargos de una manera más ordenada, por eso nos colocamos sobre cualquiera de los cargos de la tabla dinámica y luego iremos al MENÚ DE TABLA DINÁMICA ubicado en la parte superior, luego OPCIONES y del menú FÓRMULAS elegimos la opción ELEMENTO CALCULADO.


8. En este momento debemos de tener una ventana similar a ésta:


8. Ingresamos el primer nombre del cargo: ASESOR, luego nos situamos en el campo de la FÓRMULA donde iremos agregando todos los elementos necesarios para este cargo, una vez agregados todos le hacemos clic en SUMAR, con lo cual debe de quedar igual a esto:


9. Hacemos clic en ACEPTAR para visualizar nuestro nuevo campo ASESOR.  De la misma manera procederemos con los cargos DIRECTIVO, PROFESIONAL, SECRETARIA, TÉCNICO y AUXILIAR, no olvidar que al final de cada SUMA debemos de hacer clic en ACEPTAR, quedándonos una tabla dinámica similar a la que mostramos a continuación:


10. Finalmente nos quedaremos solo con los cargos que necesitamos, para lo cual hacemos clic en la pestaña de RÓTULOS DE COLUMNA y le quitamos el check a todos los cargos que ya no son necesarios.


Luego de estos 10 pasos debemos de tener un Cuadro Resumen similar al que mostramos abajo y al cual le podemos hacer los retoques que creamos necesarios para una mejor presentación final. 


Espero que ahora ya le hayan perdido ese temor a las tablas dinámicas.  Cualquier duda no dejen de escribirme...

Hasta la próxima,

BrAiNhA.

martes, 12 de julio de 2011

Excel - Cálculo de la Edad

Hola, ésta vez les traigo un ejemplo práctico para aprender a calcular la edad de una o varias personas.

Pasos:

1. Como podrán observar, tenemos un hoja de cálculo con los campos APELLIDOS Y NOMBRES, NACIMIENTO, AÑOS, MESES Y DÍAS, estos tres últimos se encuentran vacíos porque es donde ingresaremos las fórmulas para calcular las edades de cada persona.


Muy importante el no olvidar que para el cálculo de las edades debemos de contar con la fecha actual, la cual obtenemos ingresando en la celda B1 la siguiente fórmula:

=HOY()

2. Ahora nos colocamos en la celda C5 para ingresar la fórmula con la cual calcularemos los AÑOS de Eduardo Javier Portal Matías, la cual sería:

=SIFECHA(B5,$B$1,"y") & " años"

Donde:
B5 --> es la celda donde se encuentra la FECHA INICIAL.
$B$1 --> es la celda donde se encuentra la FECHA FINAL.
"y" --> para mostrar los años.
& " años" --> para agregar automáticamete la palabra años al final del texto.

3. Nos colocamos en la celda D5 para calcular los MESES de la misma persona, para lo cual ingresaremos la siguiente fórmula:

=SIFECHA(B5,$B$1,"ym") & " meses"

Donde:
B5 --> es la celda donde se encuentra la FECHA INICIAL.
$B$1 --> es la celda donde se encuentra la FECHA FINAL.
"ym" --> para mostrar los meses.
& " meses" --> para agregar automáticamete la palabra meses al final del texto.

4. Luego nos colocamos en la celda E5 para calcular los DÍAS de la misma persona, para lo cual ingresaremos la siguiente fórmula:

=SIFECHA(B5,$B$1,"md") & " días"

Donde:
B5 --> es la celda donde se encuentra la FECHA INICIAL.
$B$1 --> es la celda donde se encuentra la FECHA FINAL.
"md" --> para mostrar los días.
& " días" --> para agregar automáticamete la palabra días al final del texto.

5. Finalmente copiamos las tres fórmulas en el resto de celdas de nuestra hoja de cálculo y de esta manera tendremos las edades de las seis personas restantes, quedando nuestra hoja de cálculo de la siguiente manera:


Adicionalmente, podemos obtener la edad de una persona en una sola celda, como podemos apreciar en el siguiente cuadro:


Para obtener dicho resultado, deberemos de colocarnos en la celda C4 e ingresar la siguiente fórmula:

=SIFECHA(B4,$B$1,"y") & " años, " & SIFECHA(B4,$B$1,"ym") & " meses y " & SIFECHA(B4,$B$1,"md") & " días"

Como podemos apreciar, con esta última fórmula hemos concatenado todas nuestras tres fórmulas anteriores en una sola y de esta manera obtenemos otra forma de presentación.
Finalmente, copiamos la fórmula en todas las celdas restantes y de esta manera tendremos toda nuestra información completa.

Espero que este nuevo post les haya sido de ayuda, he tratado de ser lo más claro posible.  Cualquier duda no dejen de escribirme...

Hasta la próxima,

BrAiNhA.

Excel - Cuadro Resumen de Personal utilizando la Función Contar.SI

Hola, aquí les traigo un ejemplo práctico con el cual podemos aprender a presentar un Cuadro Resumen de Personal utilizando la función "Contar.SI", el cual realmente espero les sirva de ayuda.  Si bien es cierto que se puede elaborar con "Tablas Dinámicas", el día de hoy lo haremos con la función antes mencionada para todos aquellos que no están familiarizados con las tablas dinámicas.

Pasos:

1. Debemos tener nuestra información en una tabla de personal con los datos básicos para nuestro cuadro resumen similar a esta:


2. Elaborar un cuadro resumen como el que se muestra abajo:


3. Debemos de ubicarnos en la celda B6 para ingresar nuestra primera función, la cual sería de la siguiente manera:

=SI(CONTAR.SI(BD!$C$7:$C$13,"DIRECTIVO I")+CONTAR.SI(BD!$C$7:$C$13,"DIRECTIVO II")=0,"",CONTAR.SI(BD!$C$7:$C$13,"DIRECTIVO I")+CONTAR.SI(BD!$C$7:$C$13,"DIRECTIVO II"))

Para entender un poco esta fórmula diríamos algo así:
"Si el conteo de DIRECTIVOS I y DIRECTIVOS II da cero, entonces dejar en blanco, caso contrario contar DIRECTIVOS I y DIRECTIVOS II".

4. De igual manera, debemos de ingresar la función en el resto de celdas, variando el criterio: "DIRECTIVO I" y "DIRECTIVO II" por el que corresponda, por ejemplo en la celda C6 se tendrá que cambiar por " ASESOR SENIOR" y "ASESOR JUNIO".  Este paso lo debemos de realizar en todas las celdas, no olvidar que en las que corresponden a las de la Oficina de Contabilidad y de Logística además deberemos de cambiar el rango de origen: BD!$C$7:$C$13, el cual por ejemplo en el caso de la Oficina de Contabilidad será de BD!$C$16:$C$24.

5. No hay que olvidarnos de ingresar las fórmulas de suma correspondientes a los "Totales x Oficina" y " Totales x Cargos".

Por ejemplo:
=SUMA(B6:G6) <--- Para el "Total x Oficina" de la Oficina de Recursos Humanos.
=SUMA(B6:B8) <--- Para el "Total x Cargos" de Directivo.

6. Al final debemos de tener un Cuadro Resumen similar a este: 


Espero que esta breve explicación les haya sido totalmente clara y por ende de ayuda.  Cualquier duda no dejen de escribirme...

Hasta la próxima,

BrAiNhA.