Un lector me consulta cómo aplicar validación de datos para evitar registros duplicados, teniendo en cuenta los valores que aparecen en varias hojas del cuaderno.
Esta entrada ha sido actualizada en esta nota.
miércoles, agosto 15, 2007
Validación de Datos entre varias hojas de Excel.
Ya hemos visto que cuando queremos aplicar validación de datos refiriéndonos a una lista remota (es decir, que no se encuentra en la hoja a la que queremos aplicar validación de datos), tenemos que usar nombres para crear la referencia a la lista.
Un lector me consulta cómo aplicar validación de datos para evitar registros duplicados, teniendo en cuenta los valores que aparecen en varias hojas del cuaderno.
En una breve búsqueda en la Internet encontré esta respuesta de Bob Umlas (un Excel MVP):
Data validation across sheets isn't supported. You may need an event macro with code to do the checking for you as the worksheet changes (no se puede aplicar validación de datos a través de varias hojas. Habría que usar una macro de tipo evento para hacer la comprobación a medida que se van introduciendo cambios en la hoja).
Estaba a punto de ponerme a escribir la macro, cuando decidí intentar hacerlo de todas maneras sin macros.
El resultado es que si se puede. El truco parece residir en no crear ninguna referencia explícita a las hojas del cuaderno. Paso a explicar.
Esta entrada ha sido actualizada en esta nota.
Un lector me consulta cómo aplicar validación de datos para evitar registros duplicados, teniendo en cuenta los valores que aparecen en varias hojas del cuaderno.
Esta entrada ha sido actualizada en esta nota.
sábado, agosto 11, 2007
Tabla de amortización con Excel
Varias veces en el pasado he recibido consultas sobre cómo construir una tabla de amortización de préstamos con Excel.
Aclaremos que hay varios sistemas de amortización, y aquí mostraré como construir la tabla de amortización con cuota fija. A quien quiera ver otras posibilidades le recomiendo la página Excel Avanzadode Adolfo Aparicio. Allí podrán descargar un archivo Excel (Prestamos.xls) con muchas variantes de tablas de amortización.
En esta nota veremos como construir un cuadro de amortización de prestamos que sea flexible en cuanto al tipo de plazo (mensual, trimestral, anual, etc.) y la cantidad de plazos. Nuestro modelo se divide en dos partes: el cuadro de datos
y la tabla de amortización
El cuadro de datos contiene los parámetros necesarios para el cálculo: monto/importe del préstamo, tasa de interés anual, período del pago en meses (1: mensual, 3: trimestral, 12: anual) y número de períodos. Así por ejemplo, en un préstamo a tres años con pagos trimestrales el período de pago será 3 y el número de períodos 12.
Las fórmulas en la tabla de amortización son las siguientes:
Pago (columna B): =PAGO($C$4*($C$5/12);$C$6;-$C$3).
La expresión ($C$4*($C$5/12) nos permite expresar la tasa de interés anual en términos del plazo de pago del préstamo.
Cuota del interés (columna C): =PAGOINT($C$4*($C$5/12);A11;$C$6;-$C$3)
Cuota del capital (columna D): =PAGOPRIN($C$4*($C$5/12);A11;$C$6;-$C$3)
Saldo (columna E): =E10-D11, es decir, resta el pago a cuenta del capital del saldo anterior.
Capital amortizado: =D11+F10, el complementario de la la columna anterior.
La tabla contiene 120 filas con fórmulas, es decir sirve para préstamos de hasta 10 años. Para hacer invisibles las filas que están fuera del rango del préstamo, usamos Formato Condicional a lo largo de todas las columnas del cuadro de amortización:
En la fórmula del formato condicional, =$A12>$C$6, hay que prestar atención a la dirección semi-absoluta del primer miembro.
Technorati Tags: MS Excel
Aclaremos que hay varios sistemas de amortización, y aquí mostraré como construir la tabla de amortización con cuota fija. A quien quiera ver otras posibilidades le recomiendo la página Excel Avanzadode Adolfo Aparicio. Allí podrán descargar un archivo Excel (Prestamos.xls) con muchas variantes de tablas de amortización.
En esta nota veremos como construir un cuadro de amortización de prestamos que sea flexible en cuanto al tipo de plazo (mensual, trimestral, anual, etc.) y la cantidad de plazos. Nuestro modelo se divide en dos partes: el cuadro de datos
y la tabla de amortización
El cuadro de datos contiene los parámetros necesarios para el cálculo: monto/importe del préstamo, tasa de interés anual, período del pago en meses (1: mensual, 3: trimestral, 12: anual) y número de períodos. Así por ejemplo, en un préstamo a tres años con pagos trimestrales el período de pago será 3 y el número de períodos 12.
Las fórmulas en la tabla de amortización son las siguientes:
Pago (columna B): =PAGO($C$4*($C$5/12);$C$6;-$C$3).
La expresión ($C$4*($C$5/12) nos permite expresar la tasa de interés anual en términos del plazo de pago del préstamo.
Cuota del interés (columna C): =PAGOINT($C$4*($C$5/12);A11;$C$6;-$C$3)
Cuota del capital (columna D): =PAGOPRIN($C$4*($C$5/12);A11;$C$6;-$C$3)
Saldo (columna E): =E10-D11, es decir, resta el pago a cuenta del capital del saldo anterior.
Capital amortizado: =D11+F10, el complementario de la la columna anterior.
La tabla contiene 120 filas con fórmulas, es decir sirve para préstamos de hasta 10 años. Para hacer invisibles las filas que están fuera del rango del préstamo, usamos Formato Condicional a lo largo de todas las columnas del cuadro de amortización:
En la fórmula del formato condicional, =$A12>$C$6, hay que prestar atención a la dirección semi-absoluta del primer miembro.
Technorati Tags: MS Excel
martes, agosto 07, 2007
Tablas dinámicas en lugar de Autofiltro.
Uno de mis lectores me pregunta si se puede aplicar autofiltro simultáneamente a varias hojas. En sus propias palabras:
La respuesta es no, no se puede. Pero podemos usar tablas dinámicas para lograr efectos parecidos a los de Autofiltro. Para esto usaremos tablas dinámicas con la opción "rangos de consolidación múltiples".
Supongamos un cuaderno Excel (que se puede
descargar aquí) con listas de datos compuestas de fechas e importes, en tres hojas. Nuestro objetivo en consolidar los datos en una tabla y mostrar los datos de determinadas fechas.
Construimos la tabla dinámica con rangos de consolidación múltiples
Agregamos las listas de las distintas hojas
Y ponemos la tablas en una hoja aparte (Reporte)
Obtenemos
Ahora empezamos a mejorar el resultado. Eliminamos el elemento Columna arrastrándolo fuera de la tabla y movemos el elemento Página1 a la zona de campos de columna
Reemplazamos los rótulos de la tabla por otros más significativos
Para filtrar los datos según fechas abrimos el menú de ítems del campo
Y seleccionamos los elementos deseados
Este método es muy efectivo si queremos seleccionar sólo una fecha o un rango reducido. Si tenemos muchos elementos (fechas) en nuestras listas el método puede ser muy trabajoso.
¿se puede aplicar autofiltro a varias hojas de una sola vez? Tengo 25 hojas y
quiero filtrar en todas desde una fecha determinada. ej: desde ej 01/06/2007.
La respuesta es no, no se puede. Pero podemos usar tablas dinámicas para lograr efectos parecidos a los de Autofiltro. Para esto usaremos tablas dinámicas con la opción "rangos de consolidación múltiples".
Supongamos un cuaderno Excel (que se puede
descargar aquí) con listas de datos compuestas de fechas e importes, en tres hojas. Nuestro objetivo en consolidar los datos en una tabla y mostrar los datos de determinadas fechas.Construimos la tabla dinámica con rangos de consolidación múltiples
Agregamos las listas de las distintas hojas
Y ponemos la tablas en una hoja aparte (Reporte)
Obtenemos
Ahora empezamos a mejorar el resultado. Eliminamos el elemento Columna arrastrándolo fuera de la tabla y movemos el elemento Página1 a la zona de campos de columna
Reemplazamos los rótulos de la tabla por otros más significativos
Para filtrar los datos según fechas abrimos el menú de ítems del campo
Y seleccionamos los elementos deseados
Este método es muy efectivo si queremos seleccionar sólo una fecha o un rango reducido. Si tenemos muchos elementos (fechas) en nuestras listas el método puede ser muy trabajoso.
Technorati Tags: MS Excel
Suscribirse a:
Entradas (Atom)