Fórmula para cambiar el área de impresión dinámicamente

En Excel no es muy frecuente Establecer un área de impresión; De hecho, son muy pocos los usuarios de esta aplicación que imprimen sus reportes o alguna información contenida en las hojas de un libro de Excel. Esto ha originado que la mayoría de usuarios desconozcan una herramienta que puede ser muy útil a la hora de llevar los datos al papel.

Bajo esta premisa debo decir que en ocasiones a algunos usuarios se les dificulta imprimir desde Excel debido a que lo primero que se procesa para la impresión: son las celdas de las primeras filas y columnas de una hoja sin importar si estas últimas no contienen datos o están en blanco.

Es decir, si alguna información existe por fuera de esta área, Excel siempre imprimirá las primeras celdas en blanco (si no existen datos) antes de llegar a las que realmente si los contienen.

Esto que muchos usuarios desconocen, es el principal factor por el que imprimir desde Excel se vuelve casi tedioso y en pocas ocasiones una tarea titánica. No obstante, un comando muy útil a la hora de llevar los datos al papel es el que lleva el nombre de: Establecer área de impresión (ubicado en la pestaña Diseño de página, en el grupo Configurar página, desplegando el menú Área de impresión).

Al ejecutar este comando sobre un área determinada (rango de celdas) usted no verá que suceda algo en el libro de Excel, sin embargo, si clica en el ítem Imprimir de la pestaña Archivo, sí que verá el rango de celdas estipulado listo para imprimirse.

Algo que debes saber

Una vez se ha establecido un área de impresión, Excel la almacena dentro del Administrador de nombres (ubicado en la pestaña Fórmulas, del grupo Nombres definidos) con la denominación de: Área_de_impresión.

---

Por lo general una vez se establece un área de impresión, esta queda configurada de manera estática con el rango de celdas asignado o seleccionado. No obstante, algo que quizás usted no sabe hasta este momento es que dicho rango de celdas al que hace referencia un área de impresión, puede ser modificado con fórmulas que contienen funciones para que se genere de manera dinámica al establecer algún parámetro que lo controle.

Existen muchos modos para configurar el área de impresión de una hoja de Excel para que se genere de manera dinámica. Por ejemplo: uno de ellos es escribiendo una fórmula que utilice a la función DESREF que permita la devolución de rangos de celdas mediante su primer y sus dos últimos argumentos. ¿Quieres ver cómo? Observa este video ilustrativo.

• Si al ver el material quieres practicar, descarga el Libro de Excel que usamos en el video.

Filtro avanzado para extraer registros con enteros dentro de enteros

El comando Avanzadas (del grupo Ordenar y filtrar de la pestaña Datos) que despliega el cuadro de diálogo Filtro avanzado, es sin lugar a dudas la herramienta perfecta para filtrar tablas de datos dentro de Excel con opciones que utilizan criterios complejos.

Cuando se habla de criterio(s) complejo(s), se refiere a toda la gama de formulaciones que establecen reglas de filtrado diferentes a las ya existentes en los filtros tradicionales de Excel. Por ejemplo, los filtros de fecha, los filtros de texto, y los filtros de número, que obtenemos de manera natural al aplicar un filtro rápido sobre los encabezados de una tabla de datos.

Algo que usted debe saber

Un criterio complejo no se estipula dentro del cuadro de diálogo Filtro avanzado, ¡NO! En vez de eso, esta herramienta tiene la capacidad de leer el contenido de algunas celdas que, si los contienen, para interpretarlos dentro de la ejecución del propio filtro.

---

Los criterios complejos se pueden llegar a detectar a simple vista debido a que sus formulaciones por lo regular utilizan funciones de hoja de Excel, y muy pocas veces usan los tan afamados comodines.

Dentro de los comodines podemos encontrar dos principales

El asterisco (*), que se utiliza para representar cualquier cantidad de caracteres en una posición determinada dentro de un criterio de búsqueda; por ejemplo, usted puede usar el siguiente criterio (*ez) en el campo apellido de una tabla que referencie personas dentro de una base de datos para devolver apellidos como: González, Rodríguez, Gómez, etc.

El símbolo interrogación (?), que se utiliza para representar un sólo carácter en una posición determinada dentro de un criterio de búsqueda; por ejemplo, usted puede utilizar el siguiente criterio (P?rra) en el campo apellido de una tabla que referencie personas dentro de una base de datos para devolver apellidos como: Porra, y Parra.

Sin embargo, estos dos comodines y los demás que se describen en la ayuda de Excel desde la interfaz de usuario, siempre deberán usarse para configurar búsquedas que se apliquen a cadenas de texto o String. Si se aplican sobre valores numéricos o cualquier valor que los represente, las búsquedas no tendrán efecto o no producirán ningún resultado.

---

• Observa este video tutorial para que aclares las dudas.

Al igual que los comodines, las fórmulas que utilizan funciones también son candidatas perfectas para establecer criterios de búsqueda. Bajo este contexto son innumerables las opciones de implementación de criterios dentro de un Filtro avanzado.

La clave principal para que un Criterio formulado funcione correctamente al ejecutar un Filtro avanzado, es establecer una respuesta booleana de (Falso o Verdadero) que se modifique dinámicamente a medida que la herramienta barre cada registro de la base de datos. Esto se logra utilizando adecuadamente las referencias absolutas y relativas sobre las celdas dentro de la fórmula que establece al criterio.

Pregunta

Una vez que hayas observado este video tutorial, ¿Cuál piensas tú, podría ser la solución más indicada para establecer más de un número de búsqueda que se ejecute en una sola oportunidad del Filtro avanzado? Déjame tu respuesta en los comentarios del blog.

---

• Si quieres descarga el Libro de Excel e implementa tu solución.

• Aprende más viendo este Curso rápido de Filtros avanzados en Excel.

Separar números en grupos con espacios - Formato de número personalizado

Si es la primera vez que tocas el tema de los Formatos de número personalizados, no te preocupes, es una característica de Excel muy fácil de entender que consiste en aplicar una máscara a casi cualquier valor contenido dentro de una celda.

Para que lo comprendas de una manera sencilla, sólo imagina que un Formato de número es una máscara que cambia la apariencia (respecto de la visualización) que hace el usuario final sobre el valor contenido dentro de una celda, sin en realidad cambiar el propio valor dentro de la celda.

Por ejemplo, cuando aplicas a una celda el formato de (Moneda), (Contabilidad), (Porcentaje), o quizás el de (Fecha corta), estás aplicando indistintamente y sin saberlo, un Formato de número dentro de la celda seleccionada.

¿Qué es un Formato de número?

Como te describí en líneas anteriores, un Formato de número es tan sólo una máscara que se aplica a un valor dentro de una celda; sin embargo, usted debe entender que un Formato de número es todo aquel que Excel ya ha predefinido y ha puesto a su disposición para aplicarlo sobre cualquier celda según su valor.

En esencia se llama Formato de número a todo tipo de formato predefinido que puedes localizar desde la pestaña Número, del cuadro de dialogo Formato de celdas, que obtienes desde el comando Formato de celdas... ubicado en el menú contextual de cualquier celda en una hoja de Excel.

Además de los formatos de número predefinidos por la misma aplicación, Excel también nos permite definir formatos de número personalizados para abarcar aquellas máscaras que por default no encontramos dentro de la aplicación.

¿Qué es un Formato de número personalizado?

Un Formato de numero personalizado es aquel que el usuario define sobre una celda teniendo en cuenta las reglas que expone Excel desde su ayuda. Este tipo de formatos se estipulan desde el campo Tipo, que ubicamos en la opción Personalizada, de la pestaña Número, del cuadro de dialogo Formato de celdas.

---

Por ejemplo, suponga una cadena continua de valores numéricos de un largo de 10 dígitos contenida en una celda de Excel (2573698425). Esta cadena la cual es continua debe separarse con espacios en cuatro subgrupos de manera tal que el primero y el ultimo subgrupo contengan los tres primeros y los tres últimos dígitos respectivamente de dicha cadena. A su vez, dos subgrupos de dos dígitos que contengan los valores numéricos intermedios restantes de la cadena numérica (257  36  98  425).

Si buscas un poco te darás cuenta que no existe un Formato de número por defecto dentro de Excel para resolver este problema; sin lugar a dudas aquí debemos especificar un Formato de número personalizado mediante esta cadena que lo resuelve:

###” “##” “##” “###

Una vez analizado el formato, lograrás intuir que el marcador de posición de dígitos (#) actúa como delimitador de cada valor numérico dentro de la cadena. Así, si delimitamos del mismo modo el correspondiente espacio con un par de comillas dobles (“ ”), Excel asumirá que queremos separar la cadena numérica en subgrupos con espacios intermedios.

• Para que te quede claro puedes observar este video tutorial y sacar tus propias conclusiones.

• O mejor aún, descarga el Libro de Excel que usamos en el video y observa la aplicación del Formato de numero personalizado por ti mismo.

Aplicación Custom UI Editor - XML y VBA

Cuando pensé en crear mi primera aplicación sobre Excel, lo primero que se me vino a la cabeza, fue: como realizaría el diseño para la interfaz de usuario de mi cliente.

En ese instante se me ocurrieron varias ideas, ideas como: colocar en una hoja del libro de Excel todo el conjunto de controles necesarios para que el usuario final desplegara lo que había programado; por otro lado, también imaginé diseñar un Formulario general que contuviera todo ese conjunto de controles que se lanzaran dentro del UserForm cada vez que mi cliente abriera el libro.

Sin embargo, allí estaba yo, pensando y pensando sobre la manera más profesional de hacerlo. En ese punto observé la interfaz de usuario que tiene el mismo Excel y deduje: ¡porque no implementarla como lo hace la misma aplicación!, es decir, con una pestaña que despliegue todos los controles necesarios que el usuario requiere para operar con la aplicación.

Pues bien, allí empezó mi tarea investigativa sobre como colocar controles en la cinta de opciones del Excel. Muy poco tiempo pasó y deduje que la mejor manera de hacerlo era con XML (eXtensible Markup Language).

Este lenguaje que a primera vista me pareció sorprendente, llamó mi atención por su sencillez y robustez a la hora de implementarlo sobre esquemas que ya estaban creados dentro de Excel. No obstante, me faltaba algo. Me faltaba un editor que me permitiera de manera sencilla insertar esos esquemas XML dentro de la estructura interna de cualquier libro de Excel.

Algo que usted debe saber

Un archivo de Excel cualquiera está compuesto por diversos y diferentes esquemas nativos que hacen que la aplicación general lo interprete de manera correcta al momento de cargar su cinta de opciones, menús contextuales y hasta la misma barra de herramientas de acceso rápido.

Para comprobarlo, haz que tu sistema operativo muestre las extensiones de todos los archivos en tu PC. Luego cambia la extensión de un archivo de Excel, por ejemplo: de (.xlsm a .zip), por último, descomprime el archivo de Excel con la extensión modificada y observa todos los elementos que lleva inmerso un libro de Excel. Básicamente muchos de esos elementos son esquemas XML que están dentro del libro para que este funcione correctamente.

---

Un editor que tras probarlo me pareció bastante confiable, es el poco conocido Custom UI Editor para Microsoft Office. Este pequeño y sencillo programa que se distribuye de manera gratuita en: www.openxmldeveloper.org, es una herramienta imprescindible que nos permite insertar todos los esquemas XML dentro de la estructura interna de cualquier libro de Excel y otras aplicaciones de la Suite de Office de un modo muy limpio y seguro sin necesidad de realizar maniobras dispendiosas sobre los archivos.

La instalación del programa es como de costumbre muy similar a la de cualquier otro programa que ya hemos instalado en un sistema operativo Windows. Sólo debes ejecutar su archivo de instalación, aceptar algunos términos y condiciones, y por último dar clic en unos cuantos botones.

Que debo tener en cuenta al instalar el Custom UI Editor

Casi nada, su instalación es muy fácil y sencilla, sin embargo, debes asegurarte primero que tu sistema operativo tenga una versión igual o superior del marco de trabajo de .Net Framework 3.5 que encuentras en www.microsoft.com; ya que este programa se desarrolló con el IDE de .Net.

• Clic aquí para ver un video que explica cómo hacerlo.

---

El Custom UI Editor como ya dije, es un programa muy potente que nos permite insertar los diversos esquemas XML que nos permite Excel desde su entorno gráfico. Por ejemplo, en la cinta de opciones o (Ribbon), en la barra de herramientas de acceso rápido o (Quick Access Toolbar), en menús contextuales, en la vista previa, etc.

A continuación, un ejemplo de un botón en una pestaña en la Ribbon

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

  <ribbon startFromScratch="false">

    <tabs>

      <tab id="customTab" label="Custom Tab">

        <group id="customGroup" label="Custom Group">

          <button id="customButton"

          label="Custom Button"

          imageMso="HappyFace"

          size="large"

          onAction="Callback" />

        </group>

      </tab>

    </tabs>

  </ribbon>

</customUI>

• Este video te muestra como insertar el esquema.

• Descargue el Libro de Excel con el anterior esquema XML.

---

Si en algún momento te interesa desarrollar una aplicación profesional con Excel, te recomiendo primero pensar en cómo realizarás el diseño de la interfaz antes de comenzar a construir la aplicación.

Desde luego si me preguntas a mi como hacerla, lo más probable es que te responda: lo más parecida a Excel; es decir: usa XML para crear esquemas con controles, y el Custom UI Editor para incorporarlos a cualquier Libro de Excel.

Impedir registros duplicados en una tabla de Excel

Esta vez voy a salirme un poco del esquema tradicional con el que suelo comenzar mis artículos y voy a iniciar con la explicación de un concepto clave en Excel que quizás muchos usuarios principiantes o intermedios desconocen cuando les hablan de tablas dentro de esta aplicación.

Si bien es cierto que en el argot popular un rango de celdas que contienen valores puede calificársele como una tabla de datos en una hoja de Excel, es muy válido decir también que dicho concepto está mal utilizado cuando hablamos de tablas dentro de esta aplicación.

Es decir, una cosa es tener un rango de celdas con valores, y otra muy diferente es tener una tabla de datos con valores.

Entonces: ¿Qué es un Rango de celdas con valores y qué es una Tabla de datos?

Para que lo entiendas claramente, imagine que un Rango de celdas con valores no es nada más que un conjunto contiguo de celdas con datos que relacionan información de algún modelo particular que queremos representar en una hoja de Excel. Y una Tabla de datos es una estructura que esta aplicación reconoce mediante referencias dinámicas de celdas que Excel organiza con un nombre único dentro de un Libro para calificar la tabla, y nombres agregados para calificar a las columnas o los campos de dicha tabla.

En consecuencia, si un usuario cualquiera distribuye información en algunas celdas de una hoja para representar un modelo de interés, en esencia estamos hablando de un rango de celdas con valores. Sin embargo, si el usuario aplica el comando Tabla que se encuentra en el grupo Tablas de la pestaña Insertar o (Ctrl + T) sobre el rango de celdas con valores, estamos hablando de tabla de datos.

Por tanto, una tabla de datos tiene la capacidad inherente de referenciar sus columnas y sus filas de modo dinámico a medida que se ingresan nuevos campos o registros de manera respectiva dentro de la tabla. Lo que hace que cualquier fórmula, formato, o inclusive una validación, sean implementadas en los nuevos registros recién ingresados.

Ahora bien... Esta potencia que tienen las tablas como ya dije puede aprovecharse muy bien sobre las fórmulas y los formatos implementados; Pero más aún sobre las validaciones que se realicen sobre uno o varios campos de interés dentro de la tabla.

Por ejemplo, suponga una tabla de datos como la que le muestro arriba; una tabla de datos que referencia las calificaciones por periodo y asignatura de algunos estudiantes de un aula de clase. De la cual queremos implementar una restricción para que la persona que diligencia la información de la tabla, no duplique la calificación en un nuevo registro de una materia para un periodo que ya fue ingresado con anterioridad.

Para resolver este inconveniente fácilmente podemos implementar una validación de datos personalizada que se formule con el uso de una función de Excel que cuente los registros en busca de valores duplicados dentro de la tabla. Validación que se aplicará sobre las celdas de interés, pero que obtiene su conteo de una columna auxiliar que nos sirve de base para implementar la validación.

Esta validación personalizada prácticamente le impedirá que un nuevo registro (y no uno anterior) se cree duplicado dentro de la tabla. Así, si usted utiliza el método que voy a describir a continuación en un video tutorial, podrá sencillamente implementar integridad referencial sobre cualquier tabla de datos que necesite de esta acción.

• Clic aquí para ir al video tutorial.

• Descargue aquí el Libro de trabajo de Excel.