lunes, 20 de junio de 2011

La función SI dentro de un campo calculado en una tabla dinámica.

Días atrás llegó a mi correo el problema planteado por un lector, preguntando por la forma de emplear en la configuración de los campos calculados de las tablas dinámicas la función SI condicional.
No es frecuente ver el uso de los campos calculados, y menos emplear fórmulas complicadas para su configuración, sin embargo, esta herramienta admite muchas más funciones y fórmulas que las simples aritméticas... pero con cuidado.
Nos centraremos con el ejemplo planteado por el lector. Partimos de un libro mayor de contabilidad, con un resumen de movimientos de distintas cuentas contables y sus cargos y abonos al debe y haber:

La función SI dentro de un campo calculado en una tabla dinámica


En primer lugar construiremos nuestra tabla dinámica, llevándonos los campos 'CUENTA', 'NOMBRE' y 'NOMBRE RUT' al área de filas, y los campos 'DEBE' y 'HABER' al área de datos:

La función SI dentro de un campo calculado en una tabla dinámica


El primer paso ha sido sencillo y nada fuera de lo normal, aunque algo lejos de lo que queremos lograr en esta entrada. Nuestro objetivo es conseguir que para cada agrupación que recoja la tabla dinámica por los tres campos del área de filas ( 'CUENTA', 'NOMBRE' y 'NOMBRE RUT' ) exista un único valor que aglutine neteado los movimientos del 'DEBE' y del 'HABER', esto es, que para todas las agrupaciones obtengamos el saldo DEUDOR o ACREEDOR de dicho elemento.
Para ello crearemos dos campos calculados (ver cómo). Desde Herramientas de tabla dinámica > Opciones > Herramientas > Fórmulas > Campo calculado:

La función SI dentro de un campo calculado en una tabla dinámica


Para el campo calculado DEUDOR hemos añadido la fórmula =SI(DEBE >HABER;DEBE -HABER;0); de igual forma añadiremos un segundo campo calculado ACREEDOR con la fórmula =SI(HABER >DEBE;HABER -DEBE;0).
Veamos el efecto sobre nuestra tabla dinámica:

La función SI dentro de un campo calculado en una tabla dinámica


Fijémonos en las diferencias con los campos 'DEBE' y 'HABER', mientras éstos reflejan todos los movimientos habidos en ambos campos, la fórmula SI condiciona y muestra únicamente los movimientos netos 'DEUDORES' o 'ACREEDORES'.
Ya podemos quitar los campos 'DEBE' y 'HABER' de la tabla dinámica, que sólo habíamos mostrado para explicar las diferencias entre éstos y nuestros campos calculados. Logrando visualizar en nuestra tabla dinámica los saldos de cada cuenta.

La función SI dentro de un campo calculado en una tabla dinámica

36 comentarios:

  1. Hola !
    • Podrian decirme como son las funciones de exel para sacar el saldo deudor ( SD ) y saldo acreedor ( SA )
    Gracias !!! ♥

    ResponderEliminar
  2. Hola,
    no existe como tal una función Saldo deudor o Saldo acreedor, como puedes ver en una de las imágenes de este post, la componemos con un condicional
    para el Sd
    =SI(debe>haber;debe-haber;0)
    para el Sa
    =SI(debe<haber;haber-debe;0)
    Slds

    ResponderEliminar
  3. Tengo un problema con una condicional, sobre el mismo caso, tomando la tabla del ejemplo supongamos que la tabla dinamica se le agrega un campo llamado tipo_de_cuenta el cual contendra dos valores posibles que pueden ser Balance o Resultados, el objetivo es obtener los saldos DEUDOR o ACREEDOR, pero solamente de aquellas cuentas que sean igual a Resultados, es decir, obtener los saldos de aquellas cuentas que son de gastos y de ingresos separados por columnas, la condicional que probe quedo de la siguiente manera, =si(TIPO_DE_CUENTA="Resultados",DEUDOR,0) y para la contra-parte =SI(TIPO_DE_CUENTA="Resultados",ACREEDOR,0).

    Realize la condicional en un campo calculado pero no arrojo resultado, solamente obtuve 0.
    Cualquier respuesta por favor al mail user.test.2@hotmail.com

    ResponderEliminar
    Respuestas
    1. Hola Jesús.
      la cuestión es que la fórmulas dentro de los campos calculados no funcionan igual que en la hoja de cálculo, ni tampoco admite todas las formas, como demuestra tu ejemplo.
      Te propondría que realizaras una agrupación de cuentas de RESULTADOS y otra de BALANCE (manual o automática), y sobre esa agrupación aplicarás el campo calculado de la entrada.

      Un cordial saludo

      Eliminar
  4. Tengo esta formula en un campo calculado,no me marca ningun error, pero no realiza la comparacion del mayorista = "C" y para "C" de todas maneras hace la 2a formula del IF

    =IF((MAYORISTA="C"), (EXIST/'DESPLAZ MENSUAL'*25.5), (EXIST/'DESPLAZ MENSUAL'*24))

    que le cambio a la formula para que funcione??????

    ResponderEliminar
    Respuestas
    1. Acabo de encontrar sin responder esta cuestión. Siento el retraso.
      Muy probablemente el fallo está en que el campo calculado no funciona sobre elementos individuales de campos.
      Tu estás condicionando que si el elemento individual del campo MAYORISTA es C entonces realice operaciones sobre acumulados de otros campos.
      La solución pasaría por incorporar un campo auxiliar en el origen de datos con la operación que quieres hacer.
      Slds

      Eliminar
  5. hola. quetal exelente tu blog ayer justamente estaba
    haciendo lo mismo pero tengo un problema al obtener los saldo
    deudoras y acreedoras no me suma los totales de ambas columnas
    aperece 0. y en la imagen e visto que te suman ambas
    columnas. me podrias de decir como hiciste por favor.

    ResponderEliminar
    Respuestas
    1. Hola, muchas gracias!!
      bueno, realmente en el ejemplo no aparece una suma de total de ambass columnas (???), pero podrías incorporarlo como un nuevo campo calculado como suma de DEUDOR y ACREEDOR
      (TOTAL=DEUDOR+ACREEDOR)
      o de la diferencia si prefieres obtener el signo contable adecuado.
      Slds

      Eliminar
  6. el campo calculado me acepta varias funciones si dentro de la misma formula?

    ResponderEliminar
    Respuestas
    1. Hola, que tal?
      espero te encuentres bien...
      el campo calculado no funciona igual que una fórmula en la hoja de cálculo, por lo que aunque te admitiera varias funciones SI anidadas, el resultado no sería el normal que esperaras...
      Mejor, si tienes opción, construye tu fórmula en una columna anexa al origen de datos.
      Slds cordiales

      Eliminar
    2. muchas Gracias¡¡

      Eliminar
  7. hola
    nesecito una macro que me inserte la informacion en una serie de celdas por ejemplo en todas las celdas de A el nombre del empleado y en todas las celdas de B su identificacion siguiendo el orden de las celdas mediante un formulario ya realizado
    de antemano muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      sería necesario identificaras dónde están los datos de Empleado e Identifiación, si se introducen mediante un Userform o desde algún rango de la hojade cálculo.
      Si es mediante un Userform (supongo que sí), entonces en el Userform tendrás al menos dos TextBox, y un botón para pasar los datos a la hoja de cálculo, más o menos:
      Range("a1").value = TextBox1.value
      Range("b1").value = TextBox2.Value

      en lugar de Range("A1") y Range("B1") deberás usar un metodo que te permita ir pegando valores uno debajo de otro...
      Por ejemplo, Range("A"&Rows.Count).End(xlup).offset(1,0).value=TextBox1.Value

      Slds

      Eliminar
  8. Hola muchas gracias
    ahora tengo otras dudas:
    lo que pasa es que cuando inserto un registro y dejo uno de los campos vacidos me los llena automicamente en otro registro por ejemplo
    digito esto
    juan 123
    pedro
    juan 456
    y me aparece asi
    juan 123
    pedro 456
    juan
    me rellena los campos solo, como haria para que esto siguiera el orden sin importar si no se llena un campo y tambien como haria para condicionar una informacion a una celda por ejemplo que en la celda A50 solo se pueda meter el nombre de juan claro que esta duda seria secundaria la mas importante seria la primera pido disculpas si me extendi mucho
    de antemano muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      tendrías que completar por filas, de acuerdo al primer campo (los nombres), esto es, al campo que sí se completen todis los registros.
      Range("A"&Rows.Count).End(xlup).offset(1,0).value=TextBox1.Value
      Range("A"&Rows.Count).End(xlup).offset(0,1).value=TextBox2.Value

      así podría funcionar.

      Respecto a condicionar informción en celdas, probablemente te refieras a usar la Validación de datos en esas celdas, para permitir sólo ciertos valores de una lista.

      Slds

      Eliminar
  9. Hola, he tenido un problema que no he podido resolver, como puedo actualizar una formula cuando ya le he dado formato a una tabla, me explico, en el excel 2007 si tu le das formato a una planilla en excel (formato de tabla), si agregas un dato mas a la fila siguiente, los formatos y formulas se aplican automaticamente a esas celdas, ahora bien, si necesito hacer una modificacion a alguna formula, no logro que esa se corrija automáticamente, seria genial si me puedes ayudar con eso
    Gracias¡¡¡¡

    ResponderEliminar
    Respuestas
    1. Hola María,
      por defecto en una Tabla de Excel los campos con fórmulas se autocompletan (fórmulas, formatos, etc), y cualquier modificación que se realice en alguna de las celdas de ese campo calculado se autoaplica al resto...
      Te refieres a cómo evitar que eso ocurra???

      Eliminar
  10. Hola
    como haria para que una combobox me aparezcan en forma de lista o una especie de campo autorellenable y me deje digitar solo los datos dados por ejm
    carro,mama,papa etc...
    De antemano muchas gracias
    Un Saludo

    ResponderEliminar
  11. ya resolvi la duda de arriba ahora :p ahora no se es como odernar de la A la Z ya que la lista viene desde excel y no se busca desorganizar esta ya que hay otros datos en esta lista solo la del combobox

    ResponderEliminar
    Respuestas
    1. Hola,
      el asunto es bastante complejo.
      En primer lugar tendrías que componer un listado con valores únicos (lo puedes hacer empleando una Collection), para luego intentar ordenarlos en un Array, con algún método de ordenación, puedes ver una típico en
      http://excelforo.blogspot.com.es/2012/12/vba-algoritmo-de-ordenacion-tipo.html
      Ya te digo que podría ser bastante complicado...
      Slds

      Eliminar
  12. Hola,
    Yo tengo tres columnas en el excel: "Previsión/Ejecución", "Base imponible" y "Fecha". En la tabla dinámica pongo en columnas la fecha agrupada por años y en las filas Previsión/Ejecución y la Base imponible como Valores. Quiero tener un elemento calculado en el que por años me diga la diferencia entre la Ejecución y la Desviación. Creo que debería hacerlo como condicional pero no sé como... adjunto enlace del archivo.
    Gracias.
    https://docs.google.com/file/d/0B7L-ZnNx5qo3MllONVUxWk1Tems/edit?usp=sharing

    ResponderEliminar
    Respuestas
    1. Hola Sergio,
      no sería necesario ningún condicional, bastaría un elemento calculado como diferencia de los anteriores (Previsión y Ejecución).
      Sigue los pasos expuestos en:
      http://excelforo.blogspot.com.es/2010/02/elementos-calculados-en-tablas.html

      Slds

      Eliminar
  13. hola, tengo una consulta.... resulta que en una tabla he colocado un campo calculado que acumula los valores en trimestres verticalmente y cuando coloco un elemento calculado de forma horizontal para encontrar variaciones (%) entre trimestres. El porcentaje que me arroja de los campos calculados no es el correcto mientras que cuando se trata de un campo normal el elemento lo calcula correctamente.

    Hay forma de hacer que el elemento calculado funcione correctamente con el campo calculado.

    ResponderEliminar
    Respuestas
    1. Hola Ana María,
      el problema es que los Elementos y Campos calculados en Tablas dinámicas no siguen las mismas normas de cálculo que una operación en la hoja de cálculo... por lo que te recomendaría contruyeses en el origen de datos un campo auxiliar donde incorporar tu cálculo.
      Mi experiencia me dirije a emplear sólo Campo/elementos calculados cuando no es posible hacerlo en el origen.

      Slds y si tuvieras algún problema más no dudes en plantearmelo.

      Eliminar
  14. Hola,

    Tengo un campo calculado llamado Calificación que se basa en otro campo llamado Indicador y este último se resume como promedio en la tabla dinámica. La fórmula del campo calculado es:

    = SI(Indicador >= 0.95, 3, SI(Indicador >= 0.93, 2, SI(Indicador >=0.9, 1, 0)))

    El problema es que el resumen del campo calculado no está siendo evaluado con el resumen del campo Indicador.

    A continuación datos para construir la tabla dinámica:

    Área,Proyecto,Indicador
    Área 1,Proyecto A,0.9
    Área 1,Proyecto B,0.95
    Área 2,Proyecto C,0.93
    Área 2,Proyecto D,0.7
    Área 3,Proyecto E,0.99

    En estos datos, para el Área 1 hay dos proyectos, A y B, los cuales individualmente con el condicional califican respectivamente como 1 y 3. El problema es que la tabla dinámica para el Área 1 muestra como calificación un 3 y debería ser un 1 dado que el promedio del indicador para los proyectos del área es 0.925.

    Por favor su ayuda.

    ResponderEliminar
    Respuestas
    1. Hola andrés,
      ten presente que los campos calculados en una tabla dinámica operan a partir del dato acumulado como suma!.
      Yo añadiría un nuevo campo calculado que sumara el número de registros de cada área, y luego en el campo calculado 'Calificación' haría la operación:
      =SI(Indicador/ConteoX<0,9;0;SI(Indicador/ConteoX <0,93;1;SI(Indicador/ConteoX <0,92;2;3)))

      siendo el campo calculado ConteoX:
      =SUMA(conteo)

      y conteo sería una columna auxiliar en tu origen de datos con valores únicos 1

      Espero te sirva.
      Saludos

      Eliminar
    2. Funcionó perfecto!!! Muchas gracias no solo por la solución sino también por la rapidez de la respuesta. Saludos

      Eliminar
    3. ;-)
      me alegra funcionara como esperabas.
      un saludo

      Eliminar
  15. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  16. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  17. Estimado, cómo hago para q el campo calculado q has incorporado en tu ejemplo muestre los totales al final tal cual fuera un campo más.

    Saludos,


    Miguel

    ResponderEliminar
    Respuestas
    1. Hola Miguel,
      las tablas dinámicas son muy potentes, pero en ocasiones nos encontramos con situaciones o casos no posibles o limitaciones.. el que planteas parece uno de ellos.
      Siempre podrías probar a construir una columna auxiliar en el origen de datos para intentar replicar ese campo calculado de la TD.
      un cordial saludo

      Eliminar
  18. Hola tengo una TD (Excel 2013) hice un campo calculado llamado MARGEN, la formula es:
    =SI(AREA="PERFUMES";'PRECIO VENTA' - 'PRECIO COMPRA';0)

    AREA, es Sring. contiene los valores: "PERFUMES","BOLSAS","CARTERAS"...(y otros 5 mas) la situación es que necesito que en la TD solo se muestre el resultado (precio venta-precio compra) de aquellos productos que corresponden a AREA = perfumes

    Mi TD está estructurada en titulos de fila los campos Producto y Area (ambos Srting), en valores Precio Compra, precio venta y margen (Numericos) y en columna (ninguno).

    ¿me falta algo en la formula? al poner la función Si todo el Margen resulta con Cero, pero si únicamente aplico la resta (precio venta - precio compra) sí me da un resultado. reitero, lo que requiero es ver un resultado en todos aquellos que pertenecen a Perfumes. (espero haberme explicado)
    Gracias por su atencion

    ResponderEliminar
    Respuestas
    1. Hola,
      has pensado (no se si será posible) añadir al origen de datos un campo auxiliar con ese cálculo para luego llevarlo a la TD???

      A veces el comportamiento de los campos calculados en TD no es como esperamos...

      Si quieres puedes enviarme el fichero a excelforo@gmail.com
      Saludos

      Eliminar
    2. Muchas gracias por tu respuesta.
      esa es justamente la situación.... ya lo había agregado a la BD pero la TD suma todos los márgenes, resultando un margen enorme y la realidad es distinta.

      mi salida actual fue hacer dos TD's en una hice el cálculo y muestro exclusivamente esa Area (la estoy filtrando por Perfumes) y otra TD sin el calculo y con todas las Areas (excluyendo perfumes).

      pero ya sabes.... jejeje quieren ver todo en una sola TD....
      No compraron mi idea de que era mas claro asi... en dos =P

      Eliminar
    3. Si llevas el campo AREA a la zona de filas, y aplicas Subtotales... el campo calculado (añadido a la BD) incorporado al área de valores te resumiría el dato por sus diferentes elementos: "PERFUMES","BOLSAS","CARTERAS"...(y otros 5 mas)
      como decías...

      Eliminar