Validación de datos entre varias hojas Excel – otra variante

El método que propongo en mi nota anterior para validación de datos entre varias hojas de Excel no funciona en algunos casos, como le ha sucedido a uno de mis lectores. No he logrado encontrar una explicación por qué en mi ejemplo el método funciona pero en el archivo de mi lector no.


18/02/2012
Esta entrada ha sido actualizada con esta nota.

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.

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