Formato Condicional

Excel entre unas de las opciones mejoradas en esta versión es sin duda los formatos condicionales.
Un ejemplo sencillo se presenta a continuación:
Se tiene una lista con nombres y edades

El objetivo es que se pinte de un color que elijamos las edades que son mayores o iguales a 30.

Primero, demarcamos la columna que contiene todas las edades de la lista. 

Ahora ingresamos a las opciones que se muestra en la siguiente imagen:

En la ficha INICIO - formato condicional - Resaltar reglas de celdas - Es mayor que..

Aplicaremos formato a las celdas que son MAYORES QUE: 29

y el resultado fina sera que las edades mayores o iguales a 30 estaran con los colores seleccionados, pero si las edades por ejemplo de la primera persona lo cambiamos a 25, entonces el formato desaparecera por no cumplir la condicion asignada.

Nombre de celdas

Veremos un caso muy sencillo de la utilización de Nombres a celdas o rango de celdas y la practicidad que se le da a esta opción.

Vemos en el ejemplo siguiente  la suma normal que se realiza en la columna A.

Ahora en la columna C realizaremos la suma al igula que se hizo en la columna A pero con la diferencia que en la columna C utilizaremos Nombres como referencia de celdas.

Seleccionar o demarcar las celdas C2;C7, luego en la Ficha FORMULAS seleccionar Aisgnar Nombre.

En la ventana auxiliar que se tiene escribir el nombre a las celdas seleccionadas, en nuestro ejemplo escribimos Bolivianos y aceptar

Posteriormente escribir la funcion suma en la celda C8 de la siguiente manera =SUMA(Bolivianos)

En siguientes notas veremos la utilidad que se da a los NOMBRES a celda o rango de celdas en excel

EXCEL Y PAQUETES CONTABLES II

Ahora veremos como transferir los datos de ORIÓN a Excel.

Puede en primera instancia sacarle una copia de respaldo a las operaciones que realiza. (En su totalidad ) y seleccionar el Directorio donde la información será guardada.

Pero existe otra forma de tener la información total de lo que se procesa en el ORIÓN o MÓNICA.
Alguna vez se preguntó… ¿Dónde está la información que se genera en los paquetes contables?.
La respuesta es sencilla
Para el MÓNICA

Para ORIÓN en la barra de estado se tiene la dirección donde se almacena los datos.

Ahora simplemente debemos ingresar a esas direcciones para poder sacar la información más directa.

Una de las cosas más interesantes es que debe fijarse en que formato está almacenado la información. En el caso de ORIÓN está en ACCESS, por lo que los archivos pueden ser llevados a EXCEL directamente del Access y no de la forma tradicional como ingresar a ORIÓN.
La información llevada a EXCEL ya sea de MÓNICA o de ORIÓN, es vital para realizar gráficos por fechas, verificación de saldos, etc.

Podemos utilizar Tablas dinámicas para realizar algunas operaciones como en el ejemplo siguiente: DESCARGAR ARCHIVO XLS

EXCEL Y PAQUETES CONTABLES

Una de las operaciones comunes que se realiza en un paquete contable es el de sacar copias de seguridad (Backup).
Añadiendo a la copia de seguridad o respaldo, se hace muy necesario el de extraer los datos a EXCEL, pero no siempre los programas contables tienen esa opción que es muy importante a la hora de realizar análisis contable por cuenta, por fechas por clientes, etc.
Veamos un ejemplo de dos paquetes contables que son conocidos en Bolivia (PAQUETE CONTABLE MÓNICA y SISTEMA CONTABLE ORIÓN.
MONICA tiene entre sus opciones:

Como puede observar en la última ventana, MÓNICA tiene una opción que es: TRANSFERIR DATOS A…
Esta operación hace que los distintos archivos (Limitados) como Clientes, Proveedores, Inventarios, Lista de Preciosa y Asientos contables sean transferidos a EXCEL.
La información transferida se encontrará en el disco E: en la carpeta MONICA8 y dentro la carpeta EMP 1012.

Una vez aceptada se tiene como resultado la confirmación de la transferencia y los nombres de los archivos en formato XLS

Ahora lo que resta es abrir el archivo.

La información ahora se la puede ver en formato EXCEL

Uso de Tablas

Las tablas en excel son una herramienta muy eficiente cuando se utilizan formularios en las que constantemente se incrementan los registros en una planilla.
En el siguiente ejemplo tenemos en la Columna A nombres y demarcamos las celdas A1:A6 y en la Ficha INSERTAR seleccionar la opción TABLA.

Verificamos si la tabla tiene encabezados, en nuestro caso si lo tiene y seleccionamos la casilla La tabla tiene encabezados

Ahora simplemente creamos un formulario (cuadro combinado) y en el formato de control seleccionar las celdas que muestra la siguiente imagen: Rango de entrada $A$2:$A$6, vincular con celdas $C$1

Como se podrá apreciar, los nombres se encuentran ya en el formulario.
El problema inicial radica en que si adicionamos un nombre mas a la columna A, ese nombre no aparecera en el formulario.

Para solucionar este problema debemos nombrar las celdas demarcando A2:A6 y en el espacio de los nombres de celdas colocamos NOMBRES

Ahora en el formulario (cuadro combinado) en la opcíón Rango de entrada cambiamos el nombre que le dimos a la columna A.

Ahora cada vez que agreguemos un nuevo nombre, el formulario (Cuadro combinado) se actualizara.

Repetir filas en extremo superior

Si posee un listado en Excel con encabezado y ocupa más de 2 hojas, a continuación podrá ver cómo configurar la hoja para no tener que copiar y pegar el encabezado en cada una de las hojas del informe.

En la ficha “Diseño de página”, en el grupo “Opciones de la hoja”, haga clic en la flecha para abrir la ventana de Configuración


Se desplegará la siguiente ventana:


Haga clic en el recuadro “Repetir filas en extremo superior” y seleccione en su hoja de cálculo, la fila que pertenece a los títulos de su listado.


Termine oprimiendo la tecla Enter y se devolverá automáticamente a la ventana anterior. Si desea configurar otras opciones, como el tamaño del papel, las márgenes, etc, puede hacerlo en esta misma ventana.

Haga clic en Aceptar.

Diríjase a Vista preliminar (Ctrl + F2) y verifique que los encabezados aparecerán para cada hoja y no tendrá necesidad de copiarlos para cada una

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.