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:
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:
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:
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:
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.
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:
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:
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:
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:
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.
Hola !
ResponderEliminar• Podrian decirme como son las funciones de exel para sacar el saldo deudor ( SD ) y saldo acreedor ( SA )
Gracias !!! ♥
Hola,
ResponderEliminarno 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
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).
ResponderEliminarRealize la condicional en un campo calculado pero no arrojo resultado, solamente obtuve 0.
Cualquier respuesta por favor al mail user.test.2@hotmail.com
Hola Jesús.
Eliminarla 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
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
ResponderEliminar=IF((MAYORISTA="C"), (EXIST/'DESPLAZ MENSUAL'*25.5), (EXIST/'DESPLAZ MENSUAL'*24))
que le cambio a la formula para que funcione??????
Acabo de encontrar sin responder esta cuestión. Siento el retraso.
EliminarMuy 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
hola. quetal exelente tu blog ayer justamente estaba
ResponderEliminarhaciendo 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.
Hola, muchas gracias!!
Eliminarbueno, 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
el campo calculado me acepta varias funciones si dentro de la misma formula?
ResponderEliminarHola, que tal?
Eliminarespero 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
muchas Gracias¡¡
Eliminarhola
ResponderEliminarnesecito 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
Hola,
Eliminarserí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
Hola muchas gracias
ResponderEliminarahora 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
Hola,
Eliminartendrí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
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
ResponderEliminarGracias¡¡¡¡
Hola María,
Eliminarpor 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???
Hola
ResponderEliminarcomo 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
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
ResponderEliminarHola,
Eliminarel 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
Hola,
ResponderEliminarYo 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
Hola Sergio,
Eliminarno 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
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.
ResponderEliminarHay forma de hacer que el elemento calculado funcione correctamente con el campo calculado.
Hola Ana María,
Eliminarel 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.
Hola,
ResponderEliminarTengo 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.
Hola andrés,
Eliminarten 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
Funcionó perfecto!!! Muchas gracias no solo por la solución sino también por la rapidez de la respuesta. Saludos
Eliminar;-)
Eliminarme alegra funcionara como esperabas.
un saludo
Este comentario ha sido eliminado por el autor.
ResponderEliminarEste comentario ha sido eliminado por el autor.
ResponderEliminarEstimado, 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.
ResponderEliminarSaludos,
Miguel
Hola Miguel,
Eliminarlas 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
Hola tengo una TD (Excel 2013) hice un campo calculado llamado MARGEN, la formula es:
ResponderEliminar=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
Hola,
Eliminarhas 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
Muchas gracias por tu respuesta.
Eliminaresa 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
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)
Eliminarcomo decías...
Hola Ismael,
ResponderEliminarGusto volver en saludarte. Puedo utilizar la funcion ESTEXTO()dentro de un campo calculado de una tabla dinamica. Gracias de antemano.
Hola Ismael
ResponderEliminarPor favor necesito agregar un campo calculado que me permita dividir para los datos de Ene-17, la columna Feb-17/Saldo Pendiente y en la misma columna del campo calculado para los datos de Feb-17, la columna Mar-17/Saldo Pendiente.
Período Saldo Pendiente Feb-17 Mar-17 Abr-17 (Campo Calculado)
Ene-17 3000 4000 5000 6000 4000/3000
CxCVigente 1000 1000 1000 2000 1000/1000
1-30 1000 2000 3000 2000 2000/1000
31-60 1000 1000 1000 2000 1000/1000
Feb-17 4000 4000 2000 1500 2000/4000
CxCVigente 2000 2000 1000 500 1000/2000
1-30 1000 1500 500 500 500/1000
31-60 1000 500 500 500 500/1000
Hola
Eliminarentiendo hablamos de campo calculado dentro de una tabla dinámica...
No me queda claro qué cálculo quieres realizar y sobre todo cómo está la fuente/origen de datos distribuido..
Si puedes, enviame el fichero a
excelforo@gmail.com
Saludos
Hola Ismael
ResponderEliminarEs posible que un campo calculado sume los valores de un campo numerico (si es > 0,1,0) y que al final me de la suma del total?
Hola,
Eliminares posible crear un campo calculado con una función condicional:
=SI(campo>0;1;0)
pero OJO.. lo primero que hará el campo calculado es acumular/sumar ese 'campo', que será el resultado que comparará si es mayor que cero... para cada item acumulado obtendrás un 1 o 0 que obviamente sumará en su total general por columna
Slds
Hola Buenas Noches como están ???
ResponderEliminarNecesito una ayuda con un campo calculado que estoy intentando realizar desde hace varios días pero no me da el resultado esperado.
Tengo una tabla dinámica que me indica cliente - Fechas de Compra y cantidad, ahora bien quiere insertar el campo calculado que me reste la fecha de la ultima compra menos la inmediatamente anterior.
Me podrían ayudar a resolverlo
Así esta la tabla dinámica diseñada y quiero es restar como campo calculado las fechas.
Nombre Cliente Fecha Cantidad
Casa 02-06-2018 2
09-06-2018 2
16-06-2018 1
18-06-2018 1
21-06-2018 1
25-06-2018 1
Saludos espero me puedan ayudar
Hola
Eliminarno parece posible ese cálculo dentro de la tabla dinámica...
pero si puedes conseguirlo si aplicas cálculos en el origen de datos.
Aplica una nueva columna donde obtengas la última fecha y otra la inmediata anterior (cumpliendo los criterios que luego vayas a emplear en tu tabla dinámica), cuando lo tengas lleva ese cálculo a ésta
Saludos
Hola, mira yo estoy trabajando en una tabla dinámica para el control de ingreso y egreso del personal. Todos los días me llega un reporte con el horario de entrada o salida en forma de listado. Logré acomodar los datos para que al filtrar por el nombre y apellido o identificación me devuelva el listado de ingreso y egreso por día, ahora tengo el problema de que no logo hacer que calculé cuántas horas trabajo el empleado por día, como podría solucionar este problema un campo calculado? Gracias
ResponderEliminarHola juanjo
Eliminarbastaría un campo calculado que reste 'hora salida' menos 'hora entrada'.. quizá lo veas en decimal, pero si cambias el formato lo tendrás en sexagesimal (hh:mm:ss)..
Solo ten claro que los campos calculados primero suman datos y luego operan... en tu caso te servirá
Espero haber entendido bien el tema...
Saludos
Perdon por no haber sido mas especifico con el problema, la formula que yo uso para calcular las horas, debido a que tengo 3 horarios basicos de 7 a 15 de 15 a 23 y de 23 a 7, para este ultimo tenia el problema de que EXCEL no lograba registrar que eran horarios de dias distintos, y temo que en el campo calculado me pase lo mismo
ResponderEliminarESTA ES LA FORMULA QUE USO PARA CONTAR LAS HORAS
=SI(HORA DE SALIDA> HORA DE ENTRADA;HORA DE SALIDA-HORA DE ENTRADA;HORA DE SALIDA+1-HORA DE ENTRADA)
Hola
Eliminarlo mejor para trabajar con diferencias de horas 'entre días' es añadir a la hora el día que toque...
Esto es, si quieres saber cuantas horas hay entre las 23:00 y las 02:00 trabaja mejor 15/02/2019 23:00 y 16/02/2019 02:00....
así tendrás horas correctas
Saludos