Como prueba de buena voluntad voy a mostrar un pequeño truco con formato personalizado de números. En uno de los muchos foros de Excel apareció esta pregunta:
Hay alguna manera de ajustar el texto de una fecha con formato “dd/mm/aaaa hh:mm” de manera que la fecha aparezca en la primera línea de la celda y la hora en la segunda”
Una forma de hacerlo es editando la celda, poniendo el cursor delante de la hora y apretando Alt+Enter.
Pero la idea es definir un formato personalizado, de manera que no tengamos que editar cada una de las celdas.
El truco es el siguiente:
Seleccionamos el rango de las celdas y definimos el formato personalizado
En la ventanilla “Tipo” ponemos el cursor del mouse en el lugar del formato donde queremos dividir en dos líneas y pulsamos simultáneamente las teclas Ctrl+J y apretamos “Aceptar”
Con el rango seleccionado definimos “Ajustar texto”
o con el menú Formato de celdas
Finalmente ajustamos el alto de la fila
También podemos ir más adelante y dividir la celda en tres líneas, usando dos veces Ctrl+J
Para lograr este formato en la ventanilla “Tipo” del formulario de formato personalizado de números ponemos
“día:” dd (nótese las comilla) y un espacio
Apretamos Ctrl+J
“mes:” mmmm y un espacio
Apretamos Ctrl+J
“año:” aaaa
Apretamos “Aceptar” y definimos “Ajustar texto”. Si vemos esta situación
formFecha08
tenemos primero que cambiar el ancho de las columnas y luego el alto de la fila.
El truco fue mencionado por Rick Rothstein (si, el mismo de la prueba del nueve).
Calcular promedio ponderado en tablas dinámicas
Si queremos calcular un promedio ponderado en una tabla dinámica de Excel, lo más probable es que intentemos hacerlo creando un campo calculado. Esto es lo que intentó uno de mis lectores sólo para descubrir que SUMAPRODUCTO no da los resultados esperados cuando se usa en un campo calculado.
Vamos a describir el problema (y luego mostrar la solución). Supongamos esta tabla de datos
Queremos calcular la venta promedio por operación de cada vendedor, es decir, el promedio de las ventas ponderado por la cantidad de operaciones que cada uno efectuó. La idea es obtener los resultados que aparecen en la columna “ponderado” (nótese la diferencia en relación al promedio simple)
Para realizar el cálculo en una tabla dinámica (lo que es muy conveniente si el tamaño y los componentes de nuestros datos cambian con frecuencia) podríamos estar tentados a crear un campo calculado con esta fórmula
=SUMAPRODUCTO('Total de Ventas','Nro. De Ventas' )/SUMA('Nro. De Ventas' )
Pero, como señalo más arriba, esta fórmula no funciona en campos calculados de tablas dinámicas.
La solución es crear un campo auxiliar (promedio ponderado) con esta fórmula
Validación de datos - Tamaño de la fuente de la lista desplegable
En la última semana tres lectores me han consultado sobre el mismo tema: el tamaño de la fuente en las listas desplegables creadas con validación de datos.
El tamaño de la fuente en estas listas está definido por defecto y no puede ser cambiado. Esto genera dos problemas:
1 – Algunos de los valores de la lista desplegable aparecen “cortados”
2 – Si el zoom de la hoja está por debajo del 100%, los valores puede llegar a ser casi ilegibles
En esta nota mostraré dos estrategias para solucionar estos problemas:
1 – usar eventos para cambiar el ancho de la columna que contiene la celda con la validación de datos y el nivel de zoom de la hoja;
2 – usar una combobox de la colección de controles ActiveX.
Partimos de esta situación donde tenemos una lista de departamentos de una empresa, que hemos incluido en el nombre definido “lstDepartamentos” y una lista desplegable en la celda E2. Las columnas A, B y C nos sirven como columnas auxiliares y en aplicaciones prácticas estarán ocultas. EL nivel de zoom es 80%
Solución con un evento simple.
Hay alguna manera de ajustar el texto de una fecha con formato “dd/mm/aaaa hh:mm” de manera que la fecha aparezca en la primera línea de la celda y la hora en la segunda”
Una forma de hacerlo es editando la celda, poniendo el cursor delante de la hora y apretando Alt+Enter.
Pero la idea es definir un formato personalizado, de manera que no tengamos que editar cada una de las celdas.
El truco es el siguiente:
Seleccionamos el rango de las celdas y definimos el formato personalizado
En la ventanilla “Tipo” ponemos el cursor del mouse en el lugar del formato donde queremos dividir en dos líneas y pulsamos simultáneamente las teclas Ctrl+J y apretamos “Aceptar”
Con el rango seleccionado definimos “Ajustar texto”
o con el menú Formato de celdas
Finalmente ajustamos el alto de la fila
También podemos ir más adelante y dividir la celda en tres líneas, usando dos veces Ctrl+J
Para lograr este formato en la ventanilla “Tipo” del formulario de formato personalizado de números ponemos
“día:” dd (nótese las comilla) y un espacio
Apretamos Ctrl+J
“mes:” mmmm y un espacio
Apretamos Ctrl+J
“año:” aaaa
Apretamos “Aceptar” y definimos “Ajustar texto”. Si vemos esta situación
formFecha08
tenemos primero que cambiar el ancho de las columnas y luego el alto de la fila.
El truco fue mencionado por Rick Rothstein (si, el mismo de la prueba del nueve).
Calcular promedio ponderado en tablas dinámicas
Si queremos calcular un promedio ponderado en una tabla dinámica de Excel, lo más probable es que intentemos hacerlo creando un campo calculado. Esto es lo que intentó uno de mis lectores sólo para descubrir que SUMAPRODUCTO no da los resultados esperados cuando se usa en un campo calculado.
Vamos a describir el problema (y luego mostrar la solución). Supongamos esta tabla de datos
Queremos calcular la venta promedio por operación de cada vendedor, es decir, el promedio de las ventas ponderado por la cantidad de operaciones que cada uno efectuó. La idea es obtener los resultados que aparecen en la columna “ponderado” (nótese la diferencia en relación al promedio simple)
Para realizar el cálculo en una tabla dinámica (lo que es muy conveniente si el tamaño y los componentes de nuestros datos cambian con frecuencia) podríamos estar tentados a crear un campo calculado con esta fórmula
=SUMAPRODUCTO('Total de Ventas','Nro. De Ventas' )/SUMA('Nro. De Ventas' )
Pero, como señalo más arriba, esta fórmula no funciona en campos calculados de tablas dinámicas.
La solución es crear un campo auxiliar (promedio ponderado) con esta fórmula
Validación de datos - Tamaño de la fuente de la lista desplegable
En la última semana tres lectores me han consultado sobre el mismo tema: el tamaño de la fuente en las listas desplegables creadas con validación de datos.
El tamaño de la fuente en estas listas está definido por defecto y no puede ser cambiado. Esto genera dos problemas:
1 – Algunos de los valores de la lista desplegable aparecen “cortados”
2 – Si el zoom de la hoja está por debajo del 100%, los valores puede llegar a ser casi ilegibles
En esta nota mostraré dos estrategias para solucionar estos problemas:
1 – usar eventos para cambiar el ancho de la columna que contiene la celda con la validación de datos y el nivel de zoom de la hoja;
2 – usar una combobox de la colección de controles ActiveX.
Partimos de esta situación donde tenemos una lista de departamentos de una empresa, que hemos incluido en el nombre definido “lstDepartamentos” y una lista desplegable en la celda E2. Las columnas A, B y C nos sirven como columnas auxiliares y en aplicaciones prácticas estarán ocultas. EL nivel de zoom es 80%
Solución con un evento simple.