Posts tagged ‘Validación de planillas excel’

Tal vez la gran mayoría de los usuarios hayamos aprendido Excel a fuerza de prueba y error, con gran empeño en incorporar herramientas nuevas para enriquecer nuestras planillas.

Sin embargo vemos que las agencias con mucha frecuencia advierten sobre el incorrecto manejo del redondeo en Excel, donde muchos usuarios están confiados que el tema está bajo control, pero no están trabajando de acuerdo a los requerimientos normativos.

En las celdas I2 e I3 realizamos la misma operación, simplemente con botón de la barra de herramientas presentamos la celda I3 con un decimal menos. Vemos que redujo un decimal, lo redondeó tal cual lo hubiésemos hecho nosotros, le agradecemos a Excel por su colaboración y terminamos de ocuparnos al respecto.

Pero qué ocurre si necesitamos seguir operando con esa celda. Lo que ocurre es que la planilla sigue calculando con todos los decimales que tiene a su disposición, y que el redondeo fue sólo visual. Vamos a multiplicar las 2 celdas por 1 y veamos que nos ofrece como resultado si los volvemos a expresar con la misma cantidad de decimales que originalmente  I2.

Nos igualó los resultados, Excel siguió operando con todos los decimales disponibles.

La forma correcta de redondear en Excel está dada por la función Redondear.

Parado en la celda K3 le pido a Excel que redondee la operación I3 por J3 a 4 decimales, vemos que me sigue mostrando los decimales que le pedí pero que redondeó a 4 decimales.

Esperamos que les haya resultado de utilidad.

1.   Prerrequisito I: las macro están siempre habilitadas

a.    Se debe trabajar en un archivo  ” Libro de Excel habilitado para macros” (extensión .xlsm)

b.    Hacer click en “Botón Office”

c.    Hacer click en “Opciones de Excel”

d.    Hacer click en “Centro de confianza”

e.    Hacer click en “Configuración del Centro de confianza”

f.    Hacer click en “Configuración de macros”

g. Marcar “Habilitar todas las macros”

h.    Hacer click en “Aceptar”

i.    Hacer click en “Aceptar”

2.   Prerrequisito II: Colocar al “Programador” en la Cinta de opciones

j.     Hacer click en “Botón Office”

k.    Hacer click en “Opciones de Excel” Click en “Más frecuentes”

l.    Tildar “Marcar ficha Programador en la cinta de opciones”

m.    Aceptar

n.    Verificar  la aparición de  “Programador” en la cinta de opciones

3.   Grabar Macro

o.    Hacer click en “Programador” en cinta de opciones

p. Hacer click en “Grabar macro”

q.    Se abre el panel de diálogo

r.   Completar “Nombre de la macro”: “DatosImpresión” (no puede llevar espacios en blanco”)

s.    Guardar macro en: “Este libro”

t.    Aceptar

u.    Hacer click en “Diseño de página” en cinta de opciones

v.    Sombrear el área de impresión en la planilla

w.     Hacer click en “Establecer área de impresión”

x.    Hacer click en Configurar página (configure de acuerdo a sus preferencias)

o   Página

o   Márgenes

o   Encabezado y pie de página

o   Hoja

y.    Hacer click en “Detener macro” (el mismo ícono de “Grabar macro”)

z.      Presionar “ALT” +” F11” (juntos)

aa.    Hacer doble click a ThisWorkbook

bb.    Copiar el siguiente texto en el panel de ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

‘Por.cGMPdoc

DatosImpresion

End Sub

4.   Proteger macro

cc.    Hacer click en “VBProject”

dd.    Hacer click en “Herramientas” de la cinta de opciones

ee.    Hacer click en “Propiedades de VBAProject”

ff.    Hacer click en solapa “Protección” del cuadro de diálogo

gg.    Tildar “Bloquear proyecto para visualización”

hh.    Completar “Contraseña”

ii.    Completar “Confirmar contraseña”

jj.    Aceptar

kk.    Cerrar  el panel de Visual Basic

ll.     Cerrar el libro Excel

mm.    Fin de la configuración

5.   Verificaciones

nn.    El primer punto a verificar es que para imprimir, si previamente se modifica la configuración, toma los cambios pero imprime lo que está configurado en la macro, y para la próxima impresión vuelven a aparecer los parámetros configurados en la macro.

oo.    El segundo punto a verificar es si vamos a Cinta de opciones,  hacemos click en Macro, sólo aparece la opción “Ejecutar “en el cuadro de diálogo con lo cual no se accede a “Modificar”, ni a “Eliminar”.

pp.    Si vamos a Cinta de opciones,  hacemos click en “Programador”

qq.    Hacer click en “Visual Basic”

rr.    Hacer click en “Herramientas “

ss.     Hacer click en “Propiedades de VBAProject”,

tt.    Solicita contraseña para continuar.

En más de una ocasión, sobre todo en los talleres de Validación de planillas Excel, suele aparecer la discusión sobre si las mismas son validables, hablamos de su inviolabilidad y generalmente llegamos a la conclusión que las planillas Excel, no son a prueba de balas, pero las necesitamos.

Diseñar e implementar una hoja de cálculo debería ser la última opción. La decisión final de encargar el desarrollo y la implementación de las hojas de cálculo debe basarse en una investigación elaborada con respecto a la disponibilidad de software “existente” y probado, que ya está destinado a ser utilizado dentro de las industrias sanitarias reguladas.
Se recomienda realizar cálculos en entornos seguros y validados con funciones para cumplir con las regulaciones aplicables, por ejemplo: sistemas de datos cromatográficos (CDS), sistemas de gestión de información de laboratorio (LIMS).
Existen en el mercado softwares que le dan a las planillas Excel la posibilidad de una mayor seguridad y funcionalidad de compliance, por ejemplo, Audit Trail, firmas electrónicas, gestión de usuarios, control de versiones.

Ahora nuestra realidad, es que tenemos muchas planillas Excel, las cuales utilizamos para calcular Atributos críticos de calidad, planillas con impacto sobre la calidad de nuestros productos y mientras esto sea así, tenemos que hacer todo lo posible para tener planillas Excel correctas y seguras, por eso tenemos que validarlas y luego implementar una excelente administración y seguimiento de las mismas.

Pero ojo, no debemos validar el software Excel (la aplicación), el cual ya esta probado por el uso y es confiable, pero si todo aquello que hemos configurado, todo lo que nosotros decidimos que la planilla hará.

He pensado en tomarme unos minutos para hablar sobre este tema, ya que es muy frecuente que discutamos sobre el tema central, la validación de las planillas Excel, pero siempre que tenemos la oportunidad, en talleres, en cursos o simplemente cuando hacemos una evaluación de riesgo para saber como estamos en este tema, surge la pregunta…

¿Cómo administramos las planillas Excel?, ¿Lo hacemos de una forma segura?

Veamos un poco el tema.

Les recuerdo que hemos hablamos de la importancia de determinar el impacto GMP de una planilla Excel (PE), considerando,  si la misma tiene impacto directo sobre la  pureza, seguridad, eficacia, identidad del producto, o la medición o monitoreo de estos atributos, o si el Excel produce datos que serán usados para aceptar o rechazar el producto, o también si los resultados obtenidos del Excel interactúan con otros sistemas que tienen efecto directo sobre la calidad del producto.

También mencionamos la construcción de las planillas y las actividades de validación para asegurar que las mismas, fórmulas, instrucciones, macros, etc., cumplan con las especificaciones y sean seguras.

Pero hoy quiero hacer foco en dos aspectos muy importantes:  la administración de las planillas y el uso apropiado de las mismas.

 

Pero que pasa luego que la planilla ya está validada y el personal del laboratorio está entrenado en su uso?

El responsable de administrar las planillas del laboratorio (usualmente QA), debe proteger las planillas, usualmente asignarles una codificación, podría ser por ejemplo:

PE-NNN vXX

Donde:

PE: acrónimo de Planilla de cálculo Excel

NNNN: número único para cada PE, comenzando por el 001

V: correspondiente a versión

XX: número de

Luego QA registra la información de la PE en un listado o inventario de planillas Excel del laboratorio conteniendo las planillas validadas vigentes.

Desprotege la PE y workbook.

Asegura que cada impresión del worksheet incluye la siguiente información en cada página:

  • Nombre del archivo / nombre del worksheet (hoja) en el encabezado
  • Número de página en el pié de página
  • Estado y fecha de vigencia en el pié de página
  • Ruta donde está la planilla de cálculo Excel en el pié de página
  • Fecha y hora de impresión

Verifica que la información agregada no altera el número de páginas impresas (ajusta de forma necesaria para mantener el número de páginas como está testeada y documentada en los test del usuario de la PE).

Respeta los códigos de color establecidos para la fuente y las celdas.

Setea la PE para ser compartida, de la siguiente manera, accede al ítem menú: Tools>share workbook y chequea el ítem “permitir cambios por más de un usuario al mismo tiempo”.

Protege la PE y el workbook con un password. Ingresa al menú Herramientas / proteger Hoja y hace click, ingresa la contraseña y luego presiona “enter” o click en el botón aceptar. Registra el password (s) en el inventario de planillas validadas vigentes.  Salva el archivo y lo cierra. Coloca la PE validada en la carpeta de la red dedicado para ese propósito y liberada para su uso.

Para efectuar la administración de las Planillas Excel, suelen crearse carpetas para ubicar a las planillas según su status, por ejemplo:

  • Planillas Validadas y vigentes
  • Planillas No vigentes
  • Planillas en modificación / validación
  • Templates o modelos (como por ej. Especificación de diseño, protocolo de validación, etc.)

Los permisos de acceso a tales carpetas son los siguientes:

Posición Validadas/vigentes No vigentes En modificación/validación
Supervisor QC Solo lectura No acceso Copia/borra/modifica
Usuario Solo lectura No acceso No acceso
Personal QA Acceso completo Acceso completo Acceso completo
Adm. IT Backup / recovery Backup / recovery Backup / recovery

Uso de las planillas Excel validadas:

Cada usuario debe verificar que la planilla está disponible en la carpeta de planillas de cálculo Excel vigentes.

Cada impresión de planilla debe incluir los nombres del archivo y planilla, la información del número de página, la fecha y la hora de impresión, el estado de la planilla y la fecha de vigencia.

En cuanto al uso de las mismas, quiero que recuerden que dado que las planillas Excel “perse” no cumplen con la parte 11, el uso esperado es abrir la planilla, completar los campos necesarios con datos, generar una impresión, revisarla y firmarla.

No está permitido que los usuarios finales salven la PE de manera de permitir que el archivo sea cargado de la misma forma cada vez que se accede a él.

Cambios a una PE existente, los mismos deben ser efectuados a través del sistema de control de cambios o a través de un log de cambios de la planilla, de manera que las modificaciones sean documentadas y aprobadas antes de ser efectuadas.

Una vez aprobado el cambio, QA efectúa una copia de la planilla existente para que sea efectuada la nueva versión, luego de creada la misma sigue el mismo camino que vimos anteriormente.

La planilla modificada reemplaza a la anterior, la cual es retirada a obsoleta.

Espero que les resulte útil.

Cuando hablamos de Sistemas Computarizados en entornos GxP, la guía  seguida internacionalmente como referencia para validar y mantener a los sistemas, es la GAMP 5 (Good Automated Manufacturing Practice versión 5) confeccionada por el ISPE.

Planilla Excel cálculo MKT

Esta guía divide a los sistemas en 4 categorías de software, siendo la 1era la más sencilla y la 5ta la más compleja (la categoría 2 desapareció en esta versión).  A medida que aumenta la categoría, se debe ir incrementando la complejidad de los ensayos durante la etapa de validación, respondiendo a softwares más complejos y con más posibilidades de fallas.

Básicamente la división obedece a:

  • Categoría 1: softwares de infraestructura no configurables
  • Categoría 3: se pueden modificar algunos parámetros pero el software tampoco es configurable
  • Categoría 4: Softwares usualmente complejos, cuyos parámetros pueden ser configurados por el usuario de acuerdo a sus necesidades.
  • Categoría 5: Softwares customizados, no se fabrican en serie, sino a medida del

En que categorías coloca la GAMP 5 a nuestras planillas de cálculo?

Sorpresa!!,  en la categoría 4ta, y en la 5ta cuando tiene funciones macro.

Definitivamente nuestras planillas de cálculo son muy vulnerables, razón por la cual deben estar sólidamente diseñadas y protegidas y el proceso de validación debe reflejar consistentemente la robustez de lo hecho.

Si te faltan recursos … te ayudamos.

 

Si vamos a copiar y pegar una formula, necesitamos definir cuando las coordenadas de las celdas deberán variar y cuando deberán permanecer fijas. Esto se define cuando escribimos la formula original, no cuando la copiamos.

Daremos 2 casos.

  1. Sumaremos celdas con las dos coordenadas variables, por ejemplo, los pesos de cada muestra más el peso de cada pesafiltro.
  2. En nuestra planilla multiplicaremos a los diferentes resultados de un ensayo (celdas variables) por un valor constante de por ejemplo la pureza(celda fija).

 1.  1er caso – Las dos celdas variables. Colocamos el cursor sobre la celda.  Siempre se comienza con un símbolo numérico para que el Excel no lo tome como texto

=A2+B2

2.

tip#11

3.  Colocamos el cursor sobre la celda a copiar (C2).  Botón Derecho: Copiar

4.  Colocamos el cursor sobre la celda/s  a la/s cual/es  vamos a trasladar la fórmula (C3 a C5). Botón Derecho: Pegar.

5.  Presionar Enter. Éste modo de escribir las dos coordenadas hace que sean variables

=A3+B3; =A4+B4; etc.

6. tip#11-1

7.  2do caso – una celda variable y otra fija. Colocamos el cursor sobre la celda en la cual se generará  la fórmula. La celda B2 permanecerá constante, cuando se copie la fórmula a otras celdas. Esto se logra colocando el signo pesos delante de cada coordenada de la celda a dejar fija.

=A2*$B$2

8.

tip#11-2

 

9. Colocamos el cursor sobre la celda a copiar. Botón Derecho: Copiar.

10. Colocamos el cursor sobre la celda/s  a la/s cual/es  vamos a trasladar la fórmula. Botón Derecho: Pegar.

11. tip#11-3

 

12.  Presionar Enter. De acuerdo al uso que se le dará, se puede dejar fija la columna y no la fila o viceversa. Por ej: =A2*$B2 o A2*B$2.

Vamos a insertar una función Lógica sencilla. Por ejemplo la función “Si”. Pero le vamos a agregar color al dictamen de la celda.

Hasta el “Paso 14” es igual al Tip 9

La función Lógica en nuestro ejemplo está en la celda “F10”, y nos informará si el promedio calculado en la celda “E10”, cumple o no cumple con el requisito de ser “< a 2,00 %”.

1-14: son los mismos pasos que en el Tip#9. Para ver los pasos haga click aquí.

15. Seleccionar Inicio en la cinta de opciones.

16. Colocar el cursor en la celda donde se encuentra la función con el dictamen (PASA / FALLA) al cual queremos colorear (F10).

17. En la barra de herramientas, hacer click en Formato condicional para desplegar el listado.

18. Hacer click en Resaltar Reglas de Celdas, para desplegar el listado.

19. Hacer click en Texto que Contiene …

20. En la ventana “Aplicar Formato a las Celdas que Contengan” escribir “FALLA”.

21. En el listado desplegable (lado derecho) hacer click para desplegar el listado y seleccionar por ejemplo “Relleno Rojo Claro con Texto Rojo Oscuro”.

22.

Tip#10

23. Presionar Botón Aceptar.

24. Colocamos datos para que el promedio de mayor de 2:

Tip#10.1

 

 

Vamos a insertar una función Lógica sencilla. Por ejemplo la función “Si”.

La función Lógica en nuestro ejemplo está en la celda “F10”, y nos informará si el promedio calculado en la celda “E10”, cumple o no cumple con el requisito de ser “< a 2,00 %”.

1. Colocar el cursor en la celda en la cual se desea insertar la función.

2. En la barra de formulas, hacer click en fx.

3. Se abre el cuadro de diálogo Insertar Función, en la ventana Seleccionar Categoría . Clickear Lógicas.

4. Seleccionar función Si.

5.

Tip#9

6. Botón Aceptar.

7. Se abre el cuadro de diálogo Argumentos de Función.

8.

Tip#9.1

9.  Posicionar el cursos en la ventana Prueba lógica =E10<2

10. Posicionar el cursos en la ventana  Valor si Verdadero.

Escribimos: PASA (lo que escribamos aparecerá en pantalla cuando se de ésta condición).

11. Posicionar el cursos en la ventana  Valor si Falso.

Escribimos: FALLA (lo que escribamos aparecerá en pantalla cuando se de ésta condición).

12.

Tip#9.2

13. Presionar Botón Aceptar.

14. Cargamos datos en las celdas a promediar a modo de ejemplo:

Tip#9.3

 

 

Cuando deseamos utilizar una función, Excel nos da la posibilidad de Insertar Función. La cantidad y tipo de funciones que ofrece Excel es muy amplia, y tienen la ventaja que las funciones de Excel, no deben ser validadas, ya que las planillas ya lo están.

Va a modo de ejemplo la inserción de una función estadística: “Desviación Standard”.

1. Colocar el cursor en la celda en la cual se desea insertar la función.

2. En la barra de formulas, hacer click en fx

3. Se abre el cuadro de diálogo Insertar Función, en la ventana Seleccionar Categoría. Hacer click en Estadística.

4. Seleccionar Desvest.

5.

Tip#7

6. Presionar  Aceptar.

7.  Se abre el cuadro de diálogo Elementos de función. Posicionar el cursor en el valor 1.

8. Definir las celdas sobre las cuales se usará la función de Desviación Standard.

9.

Tip#7.1

10. Presionar Enter.

11. Presionar Aceptar.

Es de mucha ayuda para el usuario la aparición de notas que indiquen, por ejemplo, como realizar la carga de datos.

1. Colocar el cursor en la celda a cargar un comentario

2. Clickear el botón derecho del mousse

3. Insertar Comentario

4.

Tip#5

5. Escribir el texto del comentario

6. Aceptar