Determinacion de Saldo en Cuenta Corriente

En base al movimiento de cuentas corrientes de un banco, podemos determinar el saldo de un cliente en particular:

En la celda B15 ingresamos el código del cliente manualmente
Celda C15 =BUSCARV(B15;G5:H8;2)
Celda D15 =BDSUMA(A4:E12;4;B14:B15)-BDSUMA(A4:E12;3;B14:B15)

De esta forma detallamos el saldo de Cuenta Corriente por cliente

Auditoria de Fórmulas

La opción de Validación en formulas es una buena opción cuando se utilizan muchas fórmulas, números, etc. ya que una de ellas puede estar mal diseñado o no cumple algunas condiciones para mostrar resultados.
Veremos un ejemplo y localizaremos algunos datos que no cumplen una condición.
Supongamos esta lista de números en el rango A1:A10, para el cual hemos definido como valores permitidos sólo números enteros en 50 y 100

Si intentamos poner, por ejemplo, 21 Excel genera un mensaje de error

¿Cómo es entonces que en la lista aparecen valores "ilegales"? Esto se debe a que Excel controla la validez de los datos en el momento de ser introducidos manualmente. Si los datos existen en la hoja antes de haber definido la regla de validación de datos o si copiamos los datos de otra fuente y los pegamos en la hoja, Validación de datos no funciona.
Una forma rápida de controlar la validez de valores en situaciones como las descritas en el párrafo anterior es usar la opción de Validación de datos

En nuestro caso, al apretar el botón vemos este resultado

Al reemplazar el valor inválido por uno permitido, el círculo desaparece. Esto hace que este método sea muy cómodo.

Contabilidad con EXCEL

Auditoría de fórmulas en Excel – señalar precedentes y dependientes

En esta nota mostraremos el rastreo de celdas precedentes y dependientes en un cuaderno de Excel.
Precedentes son celdas que afectan el valor de la celda inspeccionada. Dependientes son las celdas afectadas por la celda seleccionada. Veamos esto con un ejemplo


En Hoja1 tenemos una tabla de bonos que corresponden a intervalos de edades. En la hoja Nombres tenemos la lista de los nombres con sus respectivas edades.
En la celda E1 ponemos una lista desplegable con Validación de Datos, basada en la lista de nombres.

En la celda E2 ponemos la fórmula
=BUSCARV(E1,nombres!A2:B6,2,0)
que nos da la edad del nombre que aparece en la celda E1. Finalmente en la celda E3 ponemos
=BUSCARV(E2,A2:B7,2)
para determinar el bono de acuerdo a la edad.
Seleccionamos A7 (o cualquier celda en el rango A2:A7) y accionamos la opción Rastrear dependientes

Excel traza una flecha que indica que la celda E3 es afectada por el valor de la celda A7.

Ahora seleccionamos la celda E3 y activamos Rastrear Precedentes

Vemos que E2 es precedente de E3 (afecta su valor) y también todo el rango A2:B7 que además de la flecha aparece enmarcado con un borde de color azul.
¿Qué pasa cuando las celdas precedentes están en otra hoja? Ese es el caso de la fórmula en la celda E2 que es afectada por los valores de la tabla nombres en la hoja Nombres.
En ese caso Excel señala que se trata de una referencia remota poniendo una flecha y en su extremo el símbolo de una tabla.

Para ver las referencias a las celdas precedentes tenemos que apuntar con el mouse a la flecha (la figura del mouse cambia de una cruz a una flecha) y hacer un doble clic

Esto abrirá el menú de Ir A, donde podemos ver la lista de las celdas remotas que afectan a la fórmula

Podemos elegir una de las celdas de la lista y apretar aceptar para ir a la celda en cuestión.

Graficos en Excel con la funcion REPETIR

Graficos en Excel con la funcion REPETIR
Si los números a representar son todos positivos (o negativos), la fórmula a usar se simplifica a =REPETIR(CARACTER(124);B2) en esta tabla de temperaturas promedio podemos agregar el valor representado al lado de la barra, con esta fórmula
En la celda C2 =REPETIR(CARACTER(124);B2)&" "&B2

Contar caracteres en excel

Función LARGO
Nos retornará la cantidad de caracteres que tiene la cadena de texto.
Estructura: LARGO(Texto)
Ejemplo: en la celda A1 escribe el texto: "Funciones Excel". Para saber la cantidad de caracteres que forman este texto deberás introducir en la celda B1 la función =LARGO(A1). Como resultado nos devolverá un 15.
Hacer notar que incluye en el recuento el espacio entre las dos palabras. Para sacar el espacio en el recuento, simplemente colocar -1 al final de la formula tal cual se muestra en la celda B2

Insertar un calendario permanente en Excel 2007.

Podemos crear un calendario permanente en una hoja de Excel sin usar controles. Esto puede hacerse con fórmulas matriciales, como en este modelo.
Este modelo usa fórmulas matriciales de rango, es decir una fórmula que da el resultado en varias celdas simultáneamente. Para crear este modelo empezamos por introducir en la celda B2 el mes y el año del calendario. Podemos hacer esto poniendo en B2 la fórmula =HOY() y dando un formato “mmmm,aaaa” a la celda. Luego seleccionamos el rango B4:H8 asegurándonos que la celda activa sea B4, introducimos esta fórmula en la celda activa
=FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-2)+{0\7\14\21\28\35}+{0;1;2;3;4;5;6}
Pero que mostrará las fechas en todas las casillas del rectángulo

Formato instantáneo de celdas Excel 2007

Podemos dar formato de forma visual a las celdas desde una galería de formatos predefinidas
Simplemente seleccionamos un grupo de celdas y usamos la funcionalidad del botón “Estilos de Celda” del “Grupo Estilo” de la “Ficha Inicio”. Comprobaremos que gracias a la funcionalidad “Vista Inmediata” los cambios son inmediatos y puede seleccionar de forma cómoda el aspecto final de los documentos.
Simplemente seleccionamos un grupo de celdas y utilizamos el botón “Estilos de Celda” que muestra un desplegable con diferentes tipos de formato.

Convertir Filas a Columnas

Muchas personas me consultaron sobre como poder convertir columnas a filas en excel.
ejemplo: de A1:F1 convertirlos a A2:A7

Primero seleccionar las celdas (A1:F1) y copiar

Posteriormente seleccionar la celda A2 y presionar el botón derecho del Ratón (mouse), seleccionar pegado especial.
Ahora en la ventana siguiente seleccionar transponer, tal como se muestra en la imagen siguiente.

y el resultado sera:

Lo ultimo que se deberia realizar es eliminar la fila 1.

Convertir una formula a caracter (letras o numeros)

Uno de los participantes me consulta si se puede convertir las formulas que son el resultado de algun proceso de actualizacion de información a Número fijo, sin que este cambien, dia que pasa.
Pues existe esa posibilidad de poder realizar ese trabajo, y detallo a continuación:

La celda C2 tiene una formula que con el transcurso de los años (puede ser meses o dias), se actualiza automáticamente, pero si una vez que tengamos un resultado a un tiempo determinado, se desea parar el cálculo a esa fecha. entonces lo que se debe hacer es convertir esa formula de C2 a numero natural de la siguiente forma:

Copiar la celda C2, y en la misma celda seleccionar la opcion de Pegar - Pegar valores
y el resultado será:

Nombrar rango de celdas

En algunas ocasiones realizamos formulas y/o funciones que no precisamente contienen Celdas como referencias.
Por ejemplo:

Este tipo de sumas son las más conocidas
Pero se puede dar nombre al rango de celdas (B2:B5) para no sumar como tradicionalmente se lo realiza,
Ahora realizaremos la misma suma pero nombrando al rango de celdas (B2:B5)
1.- Demarcar las celdas
2.- Formulas – Asignar nombre a un rango
3.- Darle un nombre
4.-Verificar el rango al que asignamos

Ahora simplemente aceptamos.
Lo interesante es…

La suma tiene como rango el texto (números) que es el nombre que le dimos a ese rango de celdas.
En algunas oportunidades se realizan muchas operaciones con nombres como en el gráfico siguiente, pero eso ya es habilidad del usuario.

Donde se encuentran las opciones de Excel 2003 en el nuevo Excel 2007?

¿No encuentra los comandos de Excel 2003 que más le gustan en la nueva interfaz de Excel 2007?
Ahora puedes ver donde quedaron las opciones anteriores. sigue este enlace:
http://office.microsoft.com/es-es/excel/HA101491513082.aspx

Apariencia de EXCEL 2007

Los colores que puede cambiar en cuanto a la apariencia de excel 2007 son 3,
Azul

plata

y negro

Este cambio se la realiza en:
Boton de Office - opciones de excel

Formato condicional Talento Humano

Una buena práctica para el formato condicional es el uso de la misma en Planillas de Sueldos o remuneraciones.
La dificultad se da cuando el personal que se debe registrar en una planilla es mucha. Por ejemplo: Verificar que el Liquido Pagable (lo que realmente recibirá el personal como retribución por el mes trabajado) es mayor a 0, teniendo encuenta que en el mes, el trabajador pueda ser sujeto de descuentos, anticipos, etc. y que no sobrepase su salario.
Para un caso práctico pondremos un ejemplo:
Se tiene una planilla de sueldos

Pero la dificultad esta en que a veces no podemos controlar a simple vista que personas tienen sueldos en negativo, ya sea por distintos motivos como anticipos, descuentos etc.

Controlaremos los montos negativos de una planilla de sueldos utilizando la opcion de Formato Condicional
Primero demarcar la columna que se desea evaluar, desde el primer monto hasta el último (AB12:AB950) para este ejemplo. Por espacio, simplemente se muestran algunos datos. Ficha Inicio - Formato Condicional - Resaltar reglas de celdas - Es menor que...

Ahora colocamos el valor Cero (numeral) para que todos aquellos valores menores a cero (negativos) puedan tener un formato como en el ejemplo

El resultado final sera:

De la columa AB que se demarco, al encontrar uno de los montos negativos lo resaltara con el formato preestablecido.
De esta forma podemos controlar mejor los numeros negativos que podamos tener en una Planilla de Sueldos o Remuneraciones

Filas y columnas vacías

Se insertan las filas vacías a "hacer una hoja de aspecto agradable". Sin embargo, por ejemplo, dividiendo los datos de enero y febrero de datos de esta forma, Excel asumirá que se trata de dos listas, no con uno. Los totales por debajo de ellos se utilizan autosum trabajo sólo en uno de estos conjuntos. Si usted desea tener un poco más de espacio, sólo tienes que arrastrar la altura de la fila manejar, o usar Formato> Fila> Altura de un conjunto específico de altura.

Nombrar celdas

¿No te odio, como las fórmulas =C1*B1-H1+F1 son horrible para trabajar. ¿No sería más fácil ver los nombres de los números que serán sujetos de operaciones?
por ejemplo

A partir de entonces, C1*B1-H1+F1 tiene un alias y se puede usar en las fórmulas. por ejemplo podemos cambiar el nombre a una celda como se muestra en la siguiente imagen

Para cambiar un nombre a una celda, simplemente seleccionar la celda y en la parde superior izquierda hacer clic en la referencia que tiene la celda seleccionada y colocar un nombre específico, en nuestro caso CANTIDAD.
Ahora simplemente seleccionar las demas celdas y colocar un nombre a las demas celdas y asi se podra realizar la misma operacion pero con la diferencia que ahora si se sabe que se esta realizando: =precio_unitario*cantidad-Descuento+Utilidad

Función Min y Max

Una de las operaciones mas sencilas es el de sacar de un rango de celdas, el numero máximo y el número mínimo.
Realizaremos un ejemplo sencillo en primera instancia.
Realizamos la tabla siguiente:

Ahora simplemente escribir en las celdas B8 y B9 las formulas siguientes:
B8 =min(B2:B6)
B9 =max(B2:B6)

Como podra apreciar, en ambas celdas (B8 y B9) estan los números mínimos y máximos de esa lista de notas

Modificar formato de comentarios

El formato preestablecido de los comentarios se puede cambiar con autoformas.
Para realizar ese trabajo, previamente se debe tener un comentario realizado.
A partir del comentario se realiza lo siguiente:
Activar opción desde Botón Office - Opciones de Excel

Categoría Avanzadas - Mostrar
Seleccionamos Comentarios e indicadores o Solo indicadores y comentarios al activar
Hacemos clic en Aceptar

Una vez insertado el comentario, para poder modificar la forma, hemos de añadir la opción a la barra rápida
Hacemos clic en la flecha de la derecha
Seleccionamos Más comandos

Bajo la categoría Comandos disponibles en: seleccionamos Todos los comandos
Seleccionamos la opción Cambiar forma
hacemos clic en Agregar

Nuestro comando aparecerá en la lista de la derecha

Seleccionamos el comentario y luego la barra de acceso rápido. Seleccionamos el autoformato que deseamos utilizar

Y el resultado final sera este