TUTORIALES BDNS
Tutorial 1
Cruzar datos
de
inhabilita-ciones con
propuestas de
concesiones
mediante
Excel
11/10/2016
11/10/2016 1
Contenido
Introducción ... 2
Supuesto ... 3
Solución ... 4
Primer paso: Obtención de datos ... 4
Segundo paso: Preparación de datos ... 5
Tercer paso: Cruce de datos ... 7
Cuarto paso: Mejora del modelo - Detección a una fecha relativa ... 10
11/10/2016 2
Introducción
La Base de Datos Nacional de Subvenciones tiene varios objetivos, según se establece en la Ley General de Subvenciones, y entre ellos están la mejora en la gestión pública y la lucha contra el fraude en materia de subvenciones.
Para el cumplimiento de estas dos finalidades, un mecanismo de ayuda es el empleo inteligente de la propia información de la BDNS. Porque la BDNS no es sólo una obligación para los órganos gestores: es también una oportunidad de mejorar su gestión mediante la consulta y análisis de la información en ella existente.
En ocasiones, esa mejora en la gestión se puede lograr con una consulta puntual a la información contenida, y con la elaboración de un informe y una extracción de datos. En otras ocasiones, se necesitará construir modelos de análisis y decisión, empleando información externa a la BDNS. Estos modelos, estos marcos específicos de análisis, se pueden construir con herramientas ofimáticas, hojas de cálculo, etc.
Sin duda muchos de los usuarios de la BDNS son expertos en el diseño y manejo de tales instrumentos; pero es posible que haya otros muchos que no han pasado de un uso elemental. Mediante esta serie de tutoriales pretendemos presentar cómo con herramientas simples y sencillas se puede generar valor añadido a la información de la BDNS para su uso por los órganos gestores u otros con acceso a la BDNS. El objetivo de estos tutoriales no es mostrar cómo funciona la BDNS, sino mostrar qué puede hacerse con la información de la BDNS.
Este primer tutorial presenta un caso frecuente en muchos órganos gestores: La detección de inhabilitaciones antes del momento de la concesión. En él se presenta un caso, fácilmente escalable a miles de candidatos a beneficiarios, de detección automática de la condición de “inhabilitado”, describiendo los pasos individuales que se deben dar en una hoja de cálculo.
Por último, rogamos a los usuarios y lectores de este manual que cualquier errata o problema, tanto con este manual como con la BDNS, sea puestos en conocimiento del Equipo de administración en [email protected]
11/10/2016 3
Supuesto
El Instituto de Deportes de Syldavia, organismo autónomo adscrito a la Consejería de Presidencia de la Comunidad Autónoma de Syldavia, convocó por Resolución de su Director General de 01/03/2016 las Subvenciones Anuales para Deportistas de Élite, que se registró en la BDNS con el código 705412, y fue publicado su extracto en el DO correspondiente en día 07/03/2016.
Examinadas las solicitudes presentadas por los candidatos1, la Comisión Técnica ha formulado la
siguiente propuesta de adjudicación:
Antes de presentar a la firma del Director General la Resolución de Concesión, se pretende
examinar la situación de posible inhabilitación judicial de los candidatos.
1 Los datos personales que se muestran en este tutorial son datos inventados. Cualquier parecido con la realidad
es mera coincidencia. Las pantallas reales que contienen datos personales reales han sido convenientemente anonimizadas.
11/10/2016 4
Solución
En vez de consultar individualmente en la BDNS la situación, a efectos de posible inhabilitación, de los candidatos propuestos, vamos a construir un sistema semiautomático de detección y decisión, que nos pueda servir para esta ocasión y futuras.
Para ello, cruzaremos los datos de la propuesta de concesión, con los datos de inhabilitados extraídos mediante un informe de la BDNS.
Primer paso: Obtención de datos
La obtención de los datos se efectúa de una manera muy sencilla, tal como se explica en los Manuales BDNS correspondientes.
A través del menú Informes -> Informes generales, se selecciona la doble flecha de la derecha correspondiente a Informe Básico de Inhabilitaciones:
De los distintos parámetro con los que es posible elaborar el informe, solo se establece en este ejemplo como fecha de fin desde 01/07/2016, para considerar solo las inhabilitaciones vigentes a partir de esa fecha, y no obtener aquellas penas ya cumplidas:
11/10/2016 5
Se obtiene el informe, que se presenta en pantalla, y sobre el botón de exportación se selecciona
Excel:
Se procede a
Abrir,
Guardaro ambas cosas la hoja resultante:
Segundo paso: Preparación de datos
Es preciso efectuar una breve preparación de datos, que solo nos llevará unos minutos, para poder efectuar nuestros análisis de manera más eficiente.
Al abrir el libro Excel generado, lo primero que debemos hacer es habilitar la edición, pulsando el botón correspondiente:
11/10/2016 6
Ahora vamos a eliminar las filas y las columnas que no nos son relevantes para nuestro trabajo, dejando una hoja de datos “más limpia”.
Marcamos las filas 1 a 5, y procedemos a su eliminación, activando el menú contextual pulsando el botón derecho del ratón:
De
la
misma
manera,
eliminamos
las
columnas desde A
hasta H, pues no
son necesarias:
Con esto ya tenemos una “hoja limpia”, que contiene a las personas inhabilitadas cuya inhabilitación está vigente a fecha 01/07/2016:
11/10/2016 7
Tercer paso: Cruce de datos
Lo más conveniente es que tengamos en un mismo libro dos hojas, una con la propuesta de concesión, y otra con el cuadro “limpio” de inhabilitaciones obtenido en el epígrafe anterior.
Preparamos en nuestra hoja Propuesta concesión, dos columnas con formato fecha, que denominamos Inicio Inhabilitación y Fin Inhabilitación:
Vamos a utilizar la función BuscarV (Vlookup en inglés)2 para cruzar los NIF de ambas hojas de
cálculo. Para ello voy a establecer una función que haga lo siguiente: Buscar si el contenido de la celda A2 de esta hoja Propuesta concesión coincide con el contenido existente en alguna celda de la matriz B2:G284 de la hoja Inhabilitaciones vigentes, y si es así recuperas el contenido de la columna 5 de la matriz considerada (ojo, columna 5 de la matriz, no de la hoja donde está la matriz).
Es decir, que si aparece el NIF 00012358P (valor en Propuesta concesión A2) en la hoja Inhabilitaciones vigentes, recupere el valor existente en la columna F (quinta columna de la matriz) de la hoja
Inhabilitaciones vigentes.
2 Además de la ayuda de Excel, puede ser interesante buscar información sobre el uso y sintaxis de esta función en www.todoexcel.com, www.exceltotal.com, y en el sitio oficial de Microsoft.
11/10/2016 8
Que busca un valor en la primera columna de la izquierda de una tabla y devuelve un valor en la misma fila desde una columna especificada. La sintaxis será =BUSCARV(A2;'Inhabiltaciones vigentes'!$B$2:$G$284;5;FALSO) .
Como vamos a hacer una copia al resto de filas de la columna F, bloqueamos los límites de la matriz de búsqueda en la hoja Propuesta concesión definiéndola como $B$2:$G$284.
11/10/2016 9
Si copio el contenido de la celda F2 al rango F3:F17, el resultado:
Estamos detectando ya cuatro posibles inhabilitaciones vigentes en nuestra lista de candidatos a concesión.
Hacemos lo mismo en la columna G, para recuperar los fines del periodo. En este caso la
sintaxis es =BUSCARV(A2;'Inhabiltaciones vigentes'!$B$2:$G$284;6;FALSO) . Nótese que ahora recuperamos el valor de la columna sexta de la hoja Inhabilitaciones vigentes.
11/10/2016 10
Extendemos la fórmula de G2 a todas las celdas necesarias, y manipulamos un poco el aspecto de la hoja para ver mejor a los inhabilitados que está propuestos para concesiones (en rojo):
Tenemos una bonita hoja, que nos ha detectado 4 presuntos inhabilitados de 16 candidatos a beneficiarios. Igual haríamos si fuera 1.600, o 16.000, u otra cifra cualquiera.
Cuarto paso: Mejora del modelo - Detección a una fecha relativa
Vamos a introducir algún mecanismo automático que pueda comparar las fechas de inicio y fin de inhabilitación con una determinada fecha de control. Esta fecha de control puede ser absoluta, como en este primer caso. Escribimos en la celda G22 la fecha 13/09/2016.
O bien empleamos fecha relativa. Esto se hace con una función. Escribimos en la celda G22 =hoy(). Al pulsar entre, esa función devuelve el día de la fecha. Cada día que abra esa hoja, variará ese valor con el día.
También podríamos poner una fecha relativa futura, dentro de una semana =hoy()+7:
Vamos a introducir una nueva columna que compare nuestra fecha de
control, con los límites de la inhabilitación. Para ello vamos a emplear las funciones “SI” e “Y”, estableciendo una fórmula que diga si la fecha de inicio es menor o igual que la fecha de control y la fecha de control es menor o igual que la fecha de fin, entonces nos avise.
11/10/2016 11
En la celda I2 escribo =Y(SI(F2<=$G$22;1;0); SI($G$22<=G2;1;0)) , y lo copio a todas las filas de la columna I. Nótese que con el “$” bloqueamos absolutamente la celda de comparación de la fecha de control. El resultado:
Vamos a hacerlo un poco más vistoso, que facilite la visualización. Para ello, vamos a dar formato condicional a las celdas donde se muestra el resultado la inhabilitación. Para ello a las celdas de resultado les vamos a aplicar Formato
condicional – Nueva regla:
Vamos a utilizar la misma regla de decisión para el formato condicional que empleado para detectar la inhabilitación vigente a la fecha de control. Seleccionamos Utilice una
fórmula que determine las celdas a aplicar formato:
Y escribimos la misma fórmula:
=Y(SI(F2<=$G$22;1;0); SI($G$22<=G2;1;0)) Pulsamos Formato.
11/10/2016 12
Definimos las distintas características que deseamos de formato para las celdas que cumplan la regla:
Vemos el resultado en la vista previa y pulsamos Aceptar:
Además le añadimos un filtro automático a la columna del resultado, marcando previamente la columna I:
11/10/2016 13
Nuestra hoja habrá quedado de la siguiente manera:
11/10/2016 14
Podemos ya seleccionar solo a aquellos candidatos a beneficiarios que se encuentran en periodo de pena de inhabilitación:
Esto que hemos construido para unos pocos candidatos propuestos para la concesión se puede efectuar igualmente, en unos minutos para varios miles, decenas de miles, etc., facilitando enormemente el control en esa fase crítica de la gestión de subvenciones.
