24. Documentacion LibreOffice Calc para un trámite. Validación de datos

1. Introducción

Cuando aportamos una hoja de cálculo cal para llenar un trámite debemos:

Proteger las celdas que no permitan entrada de datos y también las fòrmulas
Establecer el área de impresión
Ocultar fórmulas y valores intermedios
Mostrar mensajes de error
Redistribuir la información para que sea mas facil de recoger
Mostrar los campos que tengan algún error.

Una cosa que debemos tener en cuenta es el idioma con el que se van a mostrar las fórmulas. A veces puede fallar cuando las diferentes instalaciones de Calc son de diferente idioma o versión. Por tanto se recomienda utizar el idioma inglés.

Hay que agradecer la ayuda de la Junta de Andalucía (Validación de Datos, Formato condicional)

2. Fórmulas en inglés

Herramientas ->Opciones Libre Office Calc-> Formula->Utiliza nombre de las funciones en inglés.

3. Detectar errores en un campo

Para detectar errores en un campo, se podría hacer de varias maneras:

Utilizar el formato condicional.(Pero da incompatibiliades cuando se protege la hoja. Para acceder a esta opción Se entra por el menú Formato -> Formato condicional). Hay una explicación detallada en Formato condicional.
Utilizar validación (menú Datos -> Validez. Ver Validación de Datos)

4. Fórmulas para detectar errores.

NOTA: Cuando las fórmulas se hacen tremendamente complicadas, se tiene dos opciones para que el desarrollo sea más simple:

CREAR funciones propias con VBA. Es la mejor solución PERO... hay que activar las MACROS, cosa que és peligrosa.
DIVIDIR las fórmulas complicadas y situarlas en varia celdas. El problema es que hay que repetir las fórmulas cada vez.

A continuación se exponen algunos ejemplos prácticos. Pero como vemos se complica mucho.

Fórmulas relacionadas con números:

=ISNUMBER(A5) # Si la celda A5 es número

=ISTEXT(A5) # Si la celda A5 es texto

=ISODD(A5) # Si la celda A5 es un número impar

=ISEVEN(A5) # Si la celda A5 es un número par.

=MOD(A5;13)=5 # Si el resto de dividir la celda A5 por 13 es 5.

Fórmulas relacionadas con texto

=ISBLANK(A5) # Si la celda A5 está vacía

=LEN(TRIM(A5))<3 # Si la celda A5 quitando los blancos iniciales y finales tiene menos de 3 caracteres

=AND(ISNUMBER(A5);LEN(TRIM(A5))=9) # Telefono de España (número de 9 dígitos)

=AND(ISNUMBER(A5);LEN(TRIM(A5))=9)

Fórmulas relacionadas con fechas:

=AND(ISNUMBER(A5);LEFT(CELL("format";A5);1)="D") # Si la celda A5 es un a fecha (si es número y tiene formato de fecha)

=YEAR(A5)=YEAR(TODAY()) # El año de la celda A5 coincide con el actual

=MONTH(A5)=MONTH(TODAY()) # El més de la celda A5 coincide con el actual

Fórmulas más específicas (Combinación texto-dígitos, DNI, NIE):

=AND(LEN(A5)=10;NUMBERVALUE(LEFT(A5;4)=YEAR(TODAY());ISNUMBER(RIGHT(A5;6)))) # La celda A5 tiene 10 dígitos, siendo los 4 primeros el año actual.

Que se separando términios para que se vea mas claro.

=AND(LEN(A5)=10;

NUMBERVALUE(LEFT(A5;4)=YEAR(TODAY());

ISNUMBER(RIGHT(A5;6))))

=AND(LEN(A5)=16;OR(LEFT(A5;6)="CONTE-";LEFT(A5;6)="CONTI-");ISNUMBER(NUMBERVALUE(LEFT(A5;10)))) # si la celda A5 tiene 16 caracteres y comienza por "CONTE-" o "CONTI-" y el resto es un número

Que se separando términios para que se vea mas claro.

=AND(LEN(A5)=16;

OR(LEFT(A5;6)="CONTE-";

LEFT(A5;6)="CONTI-");

ISNUMBER(NUMBERVALUE(LEFT(A5;10))))

=RIGHT(A5)=MID("TRWAGMYFPDXBNJZSQVHLCKE";MOD(NUMBERVALUE(LEFT(A5;LEN(A5)-1));23)+1;1) # Verifica la letra del DNI de la celda A5

Que se separando términios para que se vea mas claro.

=RIGHT(A5)=

MID("TRWAGMYFPDXBNJZSQVHLCKE";

MOD(NUMBERVALUE(LEFT(A5;LEN(A5)-1));

23)+1;

=RIGHT(A5)=MID("TRWAGMYFPDXBNJZSQVHLCKE";MOD(NUMBERVALUE(CONCAT(CHOOSE((LEFT(A5;1)="X")*0 +(LEFT(A5;1)="Y")*1+(LEFT(A5;1)="Z")*2 +1; "0"; "1"; "2" );MID(A5;2;LEN(A5)-2)));23)+1;1) # Verifica la letra del NIE de la celda A5

Que se separando términios para que se vea mas claro.

=RIGHT(A5)=

MID("TRWAGMYFPDXBNJZSQVHLCKE";

MOD(NUMBERVALUE

(CONCAT

(CHOOSE((LEFT(A5;1)="X")*0 +

(LEFT(A5;1)="Y")*1 +

(LEFT(A5;1)="Z")*2 +1; "0"; "1"; "2");

MID(A5;2;LEN(A5)-2)

)

);

)+1;

)

=AND(LEN(A5)=20;ISNUMBER(NUMBERVALUE(MID(A5;1;7))); ISTEXT(MID(A5;8;2));ISNUMBER(NUMBERVALUE(MID(A5;10;4))); ISTEXT(MID(A5;14;1));ISNUMBER(NUMBERVALUE(MID(A5;15;4))); ISTEXT(RIGHT(A5;2))) # Verifica que la celda A5 tiene formato de referencia catastral.

Que se separando términios para que se vea mas claro.

=AND(LEN(A5)=20;

ISNUMBER(NUMBERVALUE(MID(A5;1;7)));

ISTEXT(MID(A5;8;2));

ISNUMBER(NUMBERVALUE(MID(A5;10;4)));

ISTEXT(MID(A5;14;1));

ISNUMBER(NUMBERVALUE(MID(A5;15;4)));

ISTEXT(RIGHT(A5;2))

)

=AND(NOT(ISERROR(FIND(LEFT(A5); "ABCDEFGHPQSKLM"))); ISNUMBER(NUMBERVALUE(MID(A5;2;7))); LEN(A5)=9) # Verifica que la celda A5 tiene formato de CIF (pero no verifica dígitos de control!!!!

Si queremos combinar todos los resultados para verificar que se incluya DNI, CIF o NIE

=OR(RIGHT(A5)=MID("TRWAGMYFPDXBNJZSQVHLCKE"; MOD(NUMBERVALUE(LEFT(A5;LEN(A5)-1)); 23)+1;1); RIGHT(A5)=MID("TRWAGMYFPDXBNJZSQVHLCKE"; MOD(NUMBERVALUE(CONCAT(CHOOSE((LEFT(A5;1)="X")*0 +(LEFT(A5;1)="Y")*1+(LEFT(A5;1)="Z")*2 +1; "0"; "1"; "2"); MID(A5;2;LEN(A5)-2))); 23)+1 ; 1) ; AND(NOT(ISERROR(FIND(LEFT(A5); "ABCDEFGHPQSKLM"))); ISNUMBER(NUMBERVALUE(MID(A5;2;7))); LEN(A5)=9))

Que se separando términios para que se vea mas claro:

=OR(

RIGHT(A5)=

MID("TRWAGMYFPDXBNJZSQVHLCKE";

MOD(NUMBERVALUE(LEFT(A5;LEN(A5)-1));

23)+1;

);

RIGHT(A5)=

MID("TRWAGMYFPDXBNJZSQVHLCKE";

MOD(

NUMBERVALUE(

CONCAT(

CHOOSE((LEFT(A5;1)="X")*0 +

(LEFT(A5;1)="Y")*1+

(LEFT(A5;1)="Z")*2 +1;

"0";

"1";

"2");

MID(A5;2;LEN(A5)-2)));

23)+1 ;

1) ;

AND(NOT(ISERROR(FIND(LEFT(A5); "ABCDEFGHPQSKLM"))); ISNUMBER(NUMBERVALUE(MID(A5;2;7)));

LEN(A5)=9)

)

5. Fórmulas definidas por el usuario (VBA)

Vamos al menú Herramientas-> Macros -> Organizar Macros -> Basic, le indicameos el fichero donde estamos trabajando, damos al botón crea y nos pide el nombre del modulo a crear (le digo"XimoModule")

Se han creado estas funciones para verificar DNIs, CIFs i NIEs, referncias catastrales, códigos postales

Option explicit
REM  *****  BASIC  *****

'1. ValidaDNI:  Valida DNI
'2. ValidaNIE:  Valida NIE
'3. ValidaCIF:  Valida CIF

'4. SumaDigitos: Suma los dígitos de un número
'5. ValidaDOC: Valida un documento (CIF,NIF,DNI)
'6. ValidaCP: Valida someramente codigo postal 

'7. ValidaFMT: Valida un formato 
'8. ValidaREF_CAT: Valida el formato de la referencia catastral rústica y urbana

Sub Main
  dim a_, b_, c_, d_, e_, f_, g_  as Boolean
  'a_= ValidaDNI("73912286S")
  'b_= ValidaNIE("X1234567L")
  'c_= ValidaCIF("P4624000H") 
  'd_= ValidaCIF("A58818501") 
  
  a_= ValidaDOC("73912286S")
  b_= ValidaDOC("X1234567L")
  c_= ValidaDOC("P4624000H") 
  d_= ValidaDOC("A58818501") 
  
  e_=ValidaCP("46760")
  f_=ValidaCP("00123")
  g_=ValidaCP("53123")
    
  call msgbox(a_ & "  " & "  " & b_ & "   "&  c_ & "  " & d_ & "     " & e_ & " " & f_ & " " & g_)
End Sub

'**************************************************************************
'1.  Valida un DNI
'**************************************************************************
Function ValidaDNI(ByVal dni as String) as Boolean 
  dim dni_, digit, control, controles as String
  dim i, resto as integer
  dim valor as long
  
  controles="TRWAGMYFPDXBNJZSQVHLCKE"
  dni_=Trim(dni)
  ValidaDNI= True
  
  '1. Longitud=9
  If ValidaDNI and Len(dni_)<>9 Then 
     ValidaDNI= False
  End If
  
  '2. Los 8 primeros caracteres son dígitos  
  if ValidaDNI then
    For i=1 to 8
      digit=Mid(dni_,i,1)
      if digit<"0" or digit >"9" then
         ValidaDNI= False
      End If
    Next i 
  end if  
  
  '3. Calculamos el resto del número por 23
  If ValidaDNI then
    valor = CLng(mid(dni_,1,8))
    resto=(valor mod 23) +1
    digit= mid(dni_,9,1)
  
    '4. Si no cuadra
    control=Mid(controles, resto, 1)
    if control<>digit then
      ValidaDNI= False
    End If
  End if  
  
End Function

'**************************************************************************
'2. Valida un NIE
'**************************************************************************
Function ValidaNIE(ByVal nie as String) as Boolean 
  dim nie_, digit, digit1, control, controles as String
  dim i, resto as integer
  dim valor as long
  
  controles="TRWAGMYFPDXBNJZSQVHLCKE"
  nie_=Trim(nie)
  ValidaNIE= True
  
  '1. Longitud=9
  If ValidaNIE and Len(nie_)<>9 Then 
     ValidaNIE= False
  End If
  
  '2. El primer caracter tiene que se X,Y,Z
  If ValidaNIE then
    digit=Mid(nie_,1,1)
    if digit<"X" or digit>"Z" then
      ValidaNIE= False
    end if
  end if
     
  '3. Obtenim els dígits
  If ValidaNIE then
    Select case digit
      case Is="X" 
        digit1=0
      case Is="Y" 
        digit1=1
      case Is="Z" 
        digit1=2
    End Select 
  
    '4. Los 2-8 primeros caracteres son dígitos  
    For i=2 to 8
      digit=Mid(nie_,i,1)
      if digit<"0" or digit >"9" then
         ValidaNIE= False
      End If
    Next i 
  end if
 
        
  
  '3. Calculamos el resto del número por 23
  If ValidaNIE then
    valor = CLng(digit1 & mid(nie_,2,8))
    resto=(valor mod 23) +1
    digit= mid(nie_,9,1)
  
    '4. Si no cuadra
    control=Mid(controles, resto, 1)
    if control<>digit then
      ValidaNIE= False
    End if  
  End If
  
End Function


'**************************************************************************
'3. Valida un CIF
'     A - Sociedades Anónimas
'     B - Sociedades de responsabilidad limitada
'     C - Sociedades colectivas
'     D - Sociedades comanditarias
'     E - Comunidades de bienes
'     F - Sociedades cooperativas
'     G - Asociaciones y otros tipos no definidos
'     H - Comunidades de propietarios
'     P - Corporaciones locales
'     Q - Organismos autónomos
'     S - Organos de la administración
'     K, L y M - seguramente para compatibilidad con formatos antiguos
'     X - Extranjeros, que en lugar del D.N.I. tienen el N.I.E.
'**************************************************************************
Function ValidaCIF(ByVal cif as String) as Boolean 
  dim cif_, digita, digitb, digit, prefijo, prefijos as String
  dim i, resto as integer
  dim valor, sumaPar, sumaImpar, suma as long
  
  prefijos="ABCDEFGHPQSKLM"
  cif_=Trim(cif)
  ValidaCIF= True
  
  '1. Longitud=9
  If ValidaCIF and Len(cif_)<>9 Then 
     ValidaCIF= False
  End If
  
  '2. El primer caracter tiene que ser uno de estos "ABCDEFGHKLMNPQS"
  If ValidaCIF then
    prefijo=Mid(cif,1,1)
    if Instr(1, prefijos , prefijo, 1) <1 then  
      ValidaCIF= False
    end if
  end if
     
  '3.Los 2-8 primeros caracteres son dígitos 
  If ValidaCIF then
    For i=2 to 8
      digit=Mid(cif_,i,1)
      if digit<"0" or digit >"9" then
         ValidaCIF= False
      End If
    Next i 
  end if
       
  
  '4. Sumamos los dígitos de las posiciones pares 
  If ValidaCIF then
    sumaPar=0
    for i=3 to 7 step 2
      sumaPar = SumaPar + CInt(mid(cif_,i,1))
    next i
    
    '5. Sumamos los dígitos impares multiplicados por 2 
    SumaImpar=0
    for i=2 to 8 step 2
      SumaImpar=SumaImpar+SumaDigitos( 2* CLng(mid(cif_,i,1)))
    next i
    
    '6. sumamos todo y nlos quedamos con la cifra de las unidades 
    Suma=SumaPar+ + SumaImpar 
    Suma=Suma mod 10
    
    '7. Restamos a 10 
    Suma=10 - Suma
    
    '8. Vemos varios tipos de dígitos
    digita=CStr(Suma)
    digitb=Chr(64+Suma)
    digit= mid(cif_,9,1)
  
  end if    
  
  '9 Para las sociedades (A,B,C,D)
  if ValidaCIF and Instr(1, "ABCD", prefijo, 1) >0 then  
      if digit<>digita then
        ValidaCIF= False
      End If
  end if
    
    
  '10 Para las sociedades P)
  if ValidaCIF and Instr(1, "P", prefijo, 1) >0 then  
     if digit<>digitb then
        ValidaCIF= False
      End If     
  end if  
  
  '11 para el resto
  if ValidaCIF and Instr(1, "ABCDP", prefijo, 1) <1 then  
     if digit<>digita and digit<>digitb then
        ValidaCIF= False
     End If     
  end if  
End Function


'**************************************************************************
'4. Suma los dígitos de un número
'**************************************************************************
Function SumaDigitos (ByVal num_ as Long) as Integer
  Dim a_ as String
  Dim i as Integer
  
  SumaDigitos=0
  a_= CStr(num_)
  for i=1 to Len(a_)
    SumaDigitos=SumaDigitos + CInt(Mid(a_,i,1))
  next i
End function


'**************************************************************************
'5. Valida tanto NIF, DNI.NIE
'**************************************************************************
Function ValidaDOC (ByVal doc_ as String) as Boolean
  Dim doc as String
   
  doc = Trim(doc_)
  If Len(doc)<>9 then
    ValidaDOC= False
  Else  
    doc = Mid(doc,1,1)
 
    Select case doc
      Case "0" to "9"
         ValidaDOC= ValidaDNI(doc_) 
      Case "X" to "Z" 
        ValidaDOC=ValidaNIE(doc_) 
      Case "A" to "H", "K" to "M","P","Q","S"
        ValidaDOC= ValidaCIF(doc_)
      Case Else
        ValidaDOC= False
    End Select 
  
  End if
end Function

'**************************************************************************
'6. Valida someramente el codio postal
'**************************************************************************
Function ValidaCP (ByVal doc_ as String) as Boolean
  Dim doc, s as String
  Dim i as Integer
    
  ValidaCP= True
  doc = Trim(doc_)
  
  ' Longitud 5
  If Len(doc)<>5 then
    ValidaCP= False
  end if
  
  'Numerico
  If ValidaCP then  
    for i=1 to 5
      s= Mid(doc,i,1)
      if s<"0" and s>"9" then
        ValidaCP= False
      end if  
    next i
  End if  
  
  'La provincia entre 01 Araba y 52 Melilla
  If ValidaCP then
    s=mid(doc,1,2) 
    if s<"01" or s>"52" then
      ValidaCP= False
    end if  
  End if  
 End function
 
 '**************************************************************************
'7. Valida formato
'        Ejemplos de formato
'        'AANNAA' -> 2 letras + 2 Dígitos + 2 Letras
'        'XXX'    -> 3 caracteres cualquier
'        'NN-L'   -> 2 digito +  guión + Letra
'        'NNA.L'  -> 2 digito +  1 letra + punto + Letra
'**************************************************************************
Function ValidaFMT (ByVal doc_ as String, Byval formato) as Boolean
  Dim doc, d, f as String
  Dim i as Integer
  
  ValidaFMT= True
  
  'La misma longitud que el formato
  if Len(doc_)<>Len(formato) then
     ValidaFMT= False
  end if
  
  if ValidaFMT then 
    for i=1 to len(doc_)
      d=mid(doc_,i,1)
      f=mid(formato,i,1)
    
      Select case f
        'Letra mayúscula'
        Case "A"
          if d<"A" or d>"Z" then
            ValidaFMT=false
          end if
        'Dígito  
        Case "N" 
          if d<"0" or d>"9" then
            ValidaFMT=false
          end if
        'Igual al caracter del formato
        Case "X"
           ValidaFMT=ValidaFMT 'No hacemos nada
        
        Case Else   
          if d<>f then
            ValidaFMT=false
          end if
        
      End Select 
    
    next i
  end if
End function

    
 
 '**************************************************************************
'8. Valida Referencia catastral
'   
'**************************************************************************
Function ValidaREF_CAT (ByVal doc_ as String) as Boolean
  Dim fmt_urb, fmt_rus,s as String
  
  fmt_urb="NNNNNNNAANNNNANNNNAA" 'formato de urbana
  fmt_rus="NNNNNANNNNNNNNNNNNAA" 'formato de rústica Las2 primeras la provincia
  ValidaREF_CAT=ValidaFMT(doc_,fmt_urb)
  
  if not ValidaREF_CAT then
    ValidaREF_CAT=ValidaFMT(doc_,fmt_rus)
      if ValidaREF_CAT then
        s=mid(doc,1,2)
        if s<"01" or s>"52" then 'Validar provincia
           ValidaREF_CAT=false
        end if
      end if
  end if  
end function

Buscar este blog

Dantesque Sedipualba