WWW.YOUTUBE.COM/c/excelhechofacil

Encabezado y pie de página en Excel con números romanos

noreply@blogger.com (Andres Rojas Moncada) — Tue, 13 Aug 2019 18:53:00 +0000

Quizás uno de los principales inconvenientes para la mayoría de los usuarios principiantes de Excel es el tema de las impresiones dentro de la aplicación; aún más cuando lo que desean es trabajar con los encabezados o pies de página al momento de imprimir un documento desarrollado en Microsoft Excel.

Bajo esta premisa, aquí es necesario recordar algunos aspectos esenciales a la hora de imprimir hojas de cálculo. Por ejemplo, a diferencia de Microsoft Word que posee un solo cuerpo para el encabezado y uno para el pie de página; Excel posee tres de manera correspondiente para cada uno de ellos. Es decir: encabezado izquierdo, encabezado centro, encabezado derecho, pie de página izquierdo, pie de página centro, pie de página derecho.

Por otro lado, Excel de manera nativa limita las numeraciones de las páginas para las impresiones de los encabezados y/o pies de página a un formato que sólo acepta números naturales, es decir: {1, 2, 3, 4, 5, 6, Etc. Etc.}.

Entonces aquí la pregunta: ¿Qué debemos hacer para imprimir en los encabezados o pies de página una numeración que incluya números romanos? Muy simple: Utilizar una Macro que desarrolle esta tarea.

Es por ello por lo que me he tomado un tiempo para escribir esta Macro y compartirla a todo el conjunto de personas interesadas que buscan una solución a este problema.

La Macro en cuestión es la siguiente:

Sub Encabezado_PieDePagina_EnNumerosRomanos()

Dim HojaActiva As Worksheet

Dim NumeroPaginasHoja As Long

Dim Indice As Long

Dim NumeroReinicio As Long

Set HojaActiva = ActiveSheet

NumeroPaginasHoja = HojaActiva.PageSetup.Pages.Count

If NumeroPaginasHoja = 0 Then

MsgBox "No hay nada para imprimir.", vbInformation + vbOKOnly, "Alerta"

Else

NumeroReinicio = Application.InputBox _

(Prompt:="• Un número diferente de uno reinicia la numeración en ese número." & _

vbNewLine & "• Un número igual a uno inicia la numeración en uno." & vbNewLine & _

vbNewLine & "Ingrese un valor numérico... (" & NumeroPaginasHoja & _

" páginas detectadas)", Title:="Reiniciar la numeración en...", Default:=1, Type:=1)

If NumeroReinicio = 0 Then

Exit Sub

Else

NumeroReinicio = VBA.Abs(NumeroReinicio)

Application.ScreenUpdating = False

For Indice = 1 To NumeroPaginasHoja

Application.StatusBar = "Imprimiendo página " & Indice & " de " & NumeroPaginasHoja

With HojaActiva

'//** CABECERA IZQUIERDA **// Use el apostrofe (') para no ejecutar la línea siguiente

.PageSetup.LeftHeader = "Página " & Application.WorksheetFunction.Roman(NumeroReinicio, 0)

'//** CABECERA CENTRO **// Use el apostrofe (') para no ejecutar la línea siguiente

.PageSetup.CenterHeader = "Página " & Application.WorksheetFunction.Roman(NumeroReinicio, 0)

'//** CABECERA DERECHA **// Use el apostrofe (') para no ejecutar la línea siguiente

.PageSetup.RightHeader = "Página " & Application.WorksheetFunction.Roman(NumeroReinicio, 0)

'//** PIE DE PÁGINA IZQUIERDO **// Use el apostrofe (') para no ejecutar la línea siguiente

.PageSetup.LeftFooter = "Página " & Application.WorksheetFunction.Roman(NumeroReinicio, 0)

'//** PIE DE PÁGINA CENTRO **// Use el apostrofe (') para no ejecutar la línea siguiente

.PageSetup.CenterFooter = "Página " & Application.WorksheetFunction.Roman(NumeroReinicio, 0)

'//** PIE DE PÁGINA DERECHO **// Use el apostrofe (') para no ejecutar la línea siguiente

.PageSetup.RightFooter = "Página " & Application.WorksheetFunction.Roman(NumeroReinicio, 0)

.ExportAsFixedFormat Type:=xlTypePDF, _

Filename:=ActiveWorkbook.Path & Application.PathSeparator & "Pagina " & Indice & ".pdf", _

IgnorePrintAreas:=True, _

From:=Indice, _

To:=Indice, _

OpenAfterPublish:=False

End With

NumeroReinicio = NumeroReinicio + 1

Next Indice

Application.StatusBar = False

HojaActiva.PageSetup.CenterHeader = ""

HojaActiva.PageSetup.CenterFooter = ""

Application.ScreenUpdating = True

End If

End Sub

¿Qué hace la Macro?

• La Macro calcula el número de páginas que se encuentran en la hoja activa de un libro de Excel al momento de ejecutarla. Nota: un libro de Excel puede contener múltiples hojas, y una hoja puede contener múltiples páginas.

• Seguidamente en la interfaz de usuario se muestra un cuadro de diálogo que le permitirá introducir el comienzo de la numeración; en caso de que se requiera comenzar esta última con un número diferente de uno debe especificarlo explícitamente en el diálogo. Nota: por default la numeración arranca en uno, si quiere comenzar con un valor diferente de uno especifíquelo en el diálogo con un valor numérico natural; por ejemplo: {1, 10, 20, 25, Etc.}.

• Al dar clic en el comando Aceptar del cuadro de diálogo la Macro comenzará a exportar cada página de la hoja activa a ficheros PDF con la numeración en romano (ya sea en cualquiera de los encabezados o pies de página) dentro de la ruta actual donde se encuentre almacenado el libro de Excel.

Nota 1: en la barra de estado de la aplicación se muestra el progreso de cada página exportada.

Nota 2: es recomendable antes de exportar, guardar el libro de Excel en una subcarpeta para prevenir que las páginas en PDF queden desperdigadas en una ubicación no deseada.

Nota 3: utilice una herramienta online o desktop para unir los PDF como desee.

¿Y donde aparecen los números romanos?

Por default la macro imprime los números romanos en todas las áreas correspondientes al encabezado y/o pie de página de las páginas que se exporten de la hoja activa del libro de Excel.

Si usted quiere imprimir los números romanos en una parte específica del encabezado y/o pie de página solo debe comentar las líneas de código donde NO quiere que aparezcan dichos números.

Por ejemplo, en el código siguiente se imprime únicamente los números romanos del encabezado centro y el pie de página centro de cada página que se exporte a PDF.

Descargue una copia del libro de Excel con el código y úselo a su conveniencia. Referencia del archivo: Workbook294.

¿Microsoft cambiará el paradigma en las nuevas versiones de Excel?

noreply@blogger.com (Andres Rojas Moncada) — Fri, 16 Nov 2018 22:11:00 +0000

Apreciado lector, antes de comenzar a informarse con lo que he escrito en este artículo, quiero que tengas en cuenta que lo que quiero expresar es mi humilde opinión y punto de vista acerca de los cambios que sin temor a equivocarme van a ocurrir con los nuevos lanzamientos de esta aplicación que muchos usuarios utilizamos y denominamos Excel.

Si quisiera responder en una sola palabra a la pregunta que planteo en el título de este post, mi respuesta contundente y definitiva sería un: SI.

Recuerdo que hace muchos años cuando utilizaba esa vieja versión de Excel 2003 “que en ese entonces era lo mas actualizado de la aplicación para la época” me pregunté algo interesante: ¿qué tanto podrá cambiar está aplicación en el futuro? Si ya con lo que tengo puedo dar solución a muchos problemas que se me presentan.

La respuesta a la pregunta de ese entonces la pude observar cuando Microsoft en años posteriores lanzó la versión de Excel 2007. Esta nueva versión (2007) llegó cargada de muchos cambios, pero quizás los más relevantes fueron: 1) Reemplazar los tradicionales menús por la cinta de opciones, 2) Adicionar más filas y columnas al lienzo de una hoja de cálculo y, 3) Añadir nuevas funciones de hoja, entre muchas otras cosas más que probablemente pasaron desapercibidas.

En fin... Todo un cambio en el paradigma de lo que hasta ahora se conocía acerca de esta aplicación.

El Internet de antes no es como lo conocemos ahora (ha evolucionado y lo seguirá haciendo), sin embargo, al llevar la idea de los párrafos anteriores al Internet de la época, puedo decir con certeza que al buscar información sobre estos nuevos cambios siempre observé una constante en muchos usuarios, el descontento sobre las nuevas características.

Siempre he afirmado (y pienso que lo seguiré haciendo) que todo cambio involucra dos tipos de esfuerzo. El primero se relaciona con aprender y estar abierto a las nuevas cosas, es decir, un esfuerzo intelectual al intentar absorber lo nuevo, lo mejorado. El segundo, consiste en invertir tiempo y recursos económicos para facilitar el primer esfuerzo, en otras palabras, si no tenemos disposición de tiempo y/o un mínimo de dinero para invertir en nuestra formación, será más difícil acortar la curva de aprendizaje que ayude a las ganas de querer aprender nuevas cosas.

Actualmente medir la aceptación que tuvieron los usuarios del mercado en cuanto al cambio de paradigma entre la versión de Excel 2003 a la versión de Excel 2007 es algo que parece inútil e innecesario. La realidad es que las personas ya no usan las viejas versiones con menú iguales o anteriores a Excel 2003. El tiempo, las nuevas características y mejoras, y la manera más sencilla en como se pueden resolver los problemas, se encargaron por si solas de migrar a los usuarios a las nuevas versiones de Excel que actualmente conocemos.

Por lo anterior diré algo que quizás suene atrevido y hasta injusto, no obstante, es necesario que alguien lo diga para detener de una vez por todas ese tipo de quejas generalizadas que impiden la resiliencia a lo nuevo y a mejorar cada día. “Si no tienes la capacidad para subirte al tren del cambio el único que experimentará las consecuencias del atraso serás definitivamente tú”.

Probablemente en un punto del futuro de tu vida, al igual que yo, otra persona te mencione que el año 2018 fue el año en el que se originó por parte de Microsoft el segundo nuevo gran cambio de paradigma en las versiones de Excel como la conoces. Quizás te mencione dentro de sus palabras que ese segundo gran cambio no fue estético o de apariencia, sino por el contrario, fue un cambio interno o de funcionamiento que facilitó las operaciones con datos dentro de la hoja de cálculo.

Este nuevo cambio de paradigma del cual quiero hablarte un poco en este artículo supone el reemplazo del viejo motor de cálculo de Excel que procesaba set de datos dentro de la memoria del PC para devolver un único valor como resultado de la operación informática, por el nuevo motor de cálculo que al igual que el anterior motor toma sets de datos, los procesa, y ahora tiene la capacidad de emitir sets de datos como resultado de la operación informática.

En otras palabras, las nuevas versiones de Excel que se liberarán durante y después del año 2019 tendrán la capacidad de recibir set de datos (los cuales denominaremos Arrays o Matrices) dentro de una sola fórmula que use o no funciones de hoja, procesadas dentro de una sola celda, para emitir tras su cálculo o evaluación set de datos o matrices dentro de las celdas contiguas desde donde evaluemos la fórmula.

Esta característica que a primera vista puede no ser muy relevante y que Microsoft llamó: (Dynamic Arrays o Matrices Dinámicas) redefine por completo el concepto de cálculo dentro de Excel, el cual, y sin temor a equivocarme facilitará la resolución de muchos problemas, con fórmulas más pequeñas, menos anidamiento de funciones, y menos consumo de memoria del PC para entregar resultados de manera más rápida que en versiones anteriores.

Este nuevo paradigma sumado de momento con siete nuevas funciones que incorporarán próximamente en todas las versiones de Excel con suscripción a Office 365 y algunas características adicionales, demuestran una vez más que Excel ha evolucionado por segunda vez para ofrecernos mejoras que facilitarán el procesamiento de la información en casos que van desde lo sencillo hasta lo complejo con fórmulas más simples y comprensibles.

Para ir finalizando con este artículo solo me queda mencionar que la manera en como se procesan las matrices en las versiones tradicionales de Excel (método heredado con Ctrl + Shift + Enter) se mantendrán en las versiones de Excel con el nuevo motor de cálculo por razones de compatibilidad. Esto quiere decir, que usted indiscutiblemente en un futuro no muy lejano deberá utilizar fórmulas de matrices dinámicas en lugar de las tradicionales fórmulas matriciales evaluadas con Ctrl + Shift + Enter, debido a que poco a poco irán desapareciendo las versiones de Excel que las soporten.

Por eso apreciado lector, así usted se queje con el mismo Bill Gates dueño de Microsoft sobre el descontento que tiene acerca de los nuevos cambios que surgirán en Excel debido al avance tecnológico y a las nuevas, y nuevas, y nuevas ideas que permitirán mejorar el rendimiento de las aplicaciones de procesamiento de datos, si no existe una actitud positiva de cambio de su parte, el único que experimentará las consecuencias del atraso serás definitivamente tú.

Con mucho cariño, Andrés Rojas Moncada.

Llevar hojas de un libro de Excel a hojas en libros separados

noreply@blogger.com (Andres Rojas Moncada) — Wed, 14 Feb 2018 03:12:00 +0000

Una actividad muy inusual que un usuario básico, medio, o quizás avanzado puede llegar a realizar desde la interfaz de usuario con un libro de trabajo de Excel, es trasladar las múltiples hojas de dicho libro, a libros independientes o separados que contengan una hoja por libro de Excel.

Está tarea, aunque se observe un poco descabellada y en muchas ocasiones hasta innecesaria, constituye una actividad rutinaria en ambientes de trabajo administrativos donde las fuentes de datos se mezclan en un libro único con múltiples hojas debido a la poca estandarización de los procesos en las labores cotidianas.

Es por ello que coloco a tu disposición un sencillo Procedimiento Sub desarrollado con VBA (Visual Basic para Aplicaciones) con el único fin de que sólo copies y pegues el código en un módulo estándar del IDE (Entorno de Desarrollo Integrado) del Editor de Visual Basic de tu libro de Excel, lo ejecutes y obtengas el resultado en muy poco tiempo y con casi nada de esfuerzo.

Procedimiento Sub

Sub CopiarHojasEnNuevosLibros()

If ThisWorkbook.Worksheets.Count > 1 Then

Dim sRuta As String

Dim sNombreCarpeta As String

Application.ScreenUpdating = False

sNombreCarpeta = Format(Date, "dd-mm-yyyy") & "-" & _

Format(Time, "hh-mm-ss")

sRuta = Application.GetSaveAsFilename(InitialFileName:=sNombreCarpeta, _

Title:="Establezca una ruta de guardado")

sRuta = Replace(sRuta, ".", "")

If sRuta <> "Falso" Then

MkDir (sRuta)

Dim HojaAux As Worksheet

For Each HojaAux In ThisWorkbook.Worksheets

HojaAux.Copy

ActiveWorkbook.SaveAs Filename:=sRuta & Application.PathSeparator & _

HojaAux.Name

ActiveWorkbook.Close SaveChanges:=True

Next HojaAux

MsgBox "Listo."

End If

Application.ScreenUpdating = True

Else

MsgBox "No hay suficientes hojas.", vbInformation, "Atención"

End If

End Sub

En caso de estar interesado en aprender a construir este sencillo procedimiento desde cero para realizar algún cambio o modificación en el código, con gusto puedes remitirte a un video de apoyo que construí para ese fin. Además, puedes descargar el libro de Excel que usamos en ese video.

Clic aquí para ir al video de apoyo.

Descargar Libro de Excel.

Macro para dividir cadenas con delimitador

noreply@blogger.com (Andres Rojas Moncada) — Mon, 27 Nov 2017 17:00:00 +0000

Voy a iniciar este post en mi blog comentando sobre una herramienta muy utilizada por aquellos usuarios de Excel que importan información desde archivos planos hacia el interior de las hojas de Excel.

Si, me refiero indiscutiblemente a la herramienta: Texto en columnas. (asistente de varios cuadros de diálogo) que podemos encontrar en el grupo Herramientas de datos de la pestaña Datos.

Esta herramienta la cual bajo mi punto de vista me parece una herramienta formidable para tratar información dentro de archivos planos separada en muchos casos por caracteres especiales, es en todo su contexto una utilidad bastante practica que puede ser utilizada de manera similar con la información que podamos almacenar dentro de las celdas en una hoja de Excel.

En este caso afirmo enfáticamente que esta herramienta (Texto en columnas) no sólo puede usarse cuando queremos importar información a Excel, por el contrario, es muy factible poderla utilizar cuando queremos tratar información dentro de alguna(s) celda(s) que contengan delimitadores como caracteres inmersos dentro de cadenas de texto.

Es decir, cuando en una o quizás varias celdas queramos separar de manera individual (celdas independientes) porciones de texto de una cadena de texto mucho más grande que contenga algún patrón de separación (carácter separador).

Bajo este esquema dicha herramienta es bastante funcional. Sin embargo, esta herramienta tiene una limitante muy poco conocida, la cual se esconde de usuarios básicos por no estudiarla en su totalidad o de manera detenida.

Esta limitante que tiene la herramienta y que a simple vista puede ser interpretada como un error, es que no separa correctamente cadenas de texto cuando dichas cadenas son devueltas dentro de las celdas por funciones o fórmulas.

Por ejemplo, imagine que en la celda A1 usted tiene un nombre completo que usted mismo introdujo con el teclado dentro de la celda; Y en la celda A2 tiene ese mismo nombre, pero como fórmula de retorno desde la celda A1. Es decir, A1: Pedro Mora; A2: =A1.

Pues bien, si aplica el proceso que ejerce la herramienta (Texto en columnas) sobre ambas celdas, usted podrá apreciar que la herramienta sólo actúa sobre la celda A1 y no sobre la celda A2 (porque el nombre es devuelto mediante una fórmula).

Aquí quiero comentarle que esto no es un error de la herramienta, la verdad es que es, una limitante; una limitante que puede ser cubierta con programación mediante el entorno de desarrollo de VBA del Excel.

Por ejemplo, esta macro hace que el contenido de una celda (devuelta o no mediante fórmula) se divida si existe un carácter separador de coma (",") dentro de las palabras escritas o devueltas dentro de la celda.

Macro para dividir texto delimitado con comas

Sub DividirPorComa(MiRango As Range)

Dim Celda As Range

Dim MatrizResultado() As String

Dim i As Long

For Each Celda In MiRango

Range(Celda.Offset(0, 1), Celda.Offset(0, 6)).ClearContents

MatrizResultado = Split(Celda.Value, ",")

For i = 0 To UBound(MatrizResultado)

Celda.Offset(0, i + 1).Value = Trim(MatrizResultado(i))

Next i

Next Celda

End Sub

Para implementarlo, sólo tienes que invocarlo desde otro procedimiento y pasarle la celda de interés que quiere que analice desde su argumento en la cabecera.

Si quieres más detalles por favor visualiza este video.

Si quieres conservar la macro baja el libro de Excel.

Formato de número personalizado - valores negativos

noreply@blogger.com (Andres Rojas Moncada) — Wed, 15 Nov 2017 04:22:00 +0000

Al comenzar a leer estas líneas lo primero que quiero que hagas es que comprendas que un Formato de número personalizado no es algo mágico ni tampoco complicado ni mucho menos difícil de aprender.

Son simplemente un conjunto de reglas que se aplican siguiendo una lógica estructurada que se detalla con precisión en varios capítulos de la ayuda de Excel. En ellos (en los capítulos de la ayuda) se describen con gran detalle el uso de cada uno de los caracteres especiales que la caja de texto interpreta y traslada hacia el interior de cada una de las celdas. (Formato de celdas – Número – Categoría – Personalizada - Tipo).

Para crear, o mejor dicho para personalizar un nuevo tipo de Formato de número que pueda aplicarse a una o varias celdas, lo esencial es identificar inicialmente las cuatro partes de las que se compone un formato de número; en donde el total de las partes que se conoce como (código del formato) definen en su orden el formato de números positivos, números negativos, los valores de cero, y por último el texto.

<POSITIVO>;<NEGATIVO>;<CERO>;<TEXTO>

Esta regla que por lo regular no es estricta en todos los casos permite definir y trabajar con las secciones de código que necesitemos en un momento dado. Sin embargo, si se desea omitir una sección dentro del código del formato, siempre deberá tenerse cuidado de especificar y separar dicha sección con un punto y coma (;) dentro de los límites de la expresión.

Por otro lado, y algo que debe quedar claro, es que, si solo se especifican dos secciones de código para un formato de número personalizado, la primera sección de código siempre se usará para los números positivos y los ceros, y la segunda sección se utilizará para los números negativos.

Así mismo, si un formato de número sólo requiere únicamente de una sección de código, esta se usará para todos los números dentro del código del formato. Es decir, si solo se especifica una sola sección de código, está sección aplicará únicamente para los números dentro de las celdas de interés donde se aplique dicho formato.

Convenciones de algunos caracteres especiales de uso frecuente

• Comillas dobles (“ ”): se utilizan para especificar cadenas de texto dentro de todas las secciones de código de un formato de número.

• Símbolo de arroba (@): se utiliza únicamente en la sección de código de texto. (La sección de código de texto en el código del formato siempre será la última). Si omite la arroba en la última sección de código, el texto que escriba en la(s) celda(s) no se mostrará.

• Símbolo de subrayado (_): Se utiliza en cualquier sección del código del formato. Subrayado crea un espacio que tenga el ancho de un carácter que se especifique de manera subsiguiente a este último.

• Símbolo de asterisco (*): Se utiliza para repetir un determinado carácter dentro de un formato de número. Se usa en todas las secciones del código del formato. Al utilizar el asterisco con un carácter subsiguiente, dicho carácter rellena la celda hasta el ancho de toda la columna.

• Corchetes para colores ([Color]): Se utilizan para especificar un tipo de color dentro de cada sección del código del formato. Los colores disponibles y aceptados para un formato de numero son: [Negro], [Verde], [Blanco], [Azul], [Magenta], [Amarillo], [Cian], [Rojo].

• El cero (0): Es un marcador de posición de dígitos. Muestra los ceros no significativos si un número tiene menos dígitos que los ceros especificados en el formato. Ejemplo: si escribe 10.5 en una celda y desea mostrar 10.50 en dicha celda, use el formato de numero #.00 para agregar un cero adicional.

• El numeral (#): Es un marcador de posición de dígitos. (#) sigue las mismas reglas que el (0), sin embargo, (#) no muestra ceros adicionales cuando el número que escribe tiene menos dígitos a ambos lados de la coma decimal que el número de símbolos # especificados en el formato. Ejemplo, si en una celda escribe 10.5 y especifica el formato de número #.### el número mostrado siempre será 10.5.

• La interrogación (?): Es un marcador de posición de dígitos y sigue las mismas reglas que el (0). El detalle con este marcador de posición es que Excel lo interpreta con espacios de alineación para números con decimales de diferentes tamaños. Ejemplo, si escribe 50.5, 10.58, 852.45, 5487.365 y especifica el siguiente formato de número #,##0.00? Excel alinea todos los números por el separador decimal.

Así, al tener en cuenta estos parámetros básicos usted podrá emplearlos para dar una mejor presentación a los datos que escriba dentro de las celdas. No obstante, mi única recomendación es que lea la ayuda completa y verifique cada uno de los aspectos y demás convenciones que allí se estipulan.

Por ejemplo, con el siguiente video te enseño en menos de un minuto a crear un formato de numero personalizado para denotar los valores negativos de moneda contenidos en una o varias celdas de Excel.

Clic aquí para ver el video.

Clic aquí para descargar el libro de Excel.

Gráfico de barras internas

noreply@blogger.com (Andres Rojas Moncada) — Tue, 07 Nov 2017 05:03:00 +0000

Diseñar un gráfico en Excel no es una tarea complicada, sólo es necesario ir a la pestaña Insertar y elegir una de las variadas opciones que tiene el grupo Gráficos.

No obstante, los gráficos por defecto que construye Excel, muchas veces no se adecuan al cien por ciento a nuestras necesidades para representar los datos como información útil que sirva para tomar alguna decisión.

Es por ello que muchas veces debemos realizar algunas modificaciones extras que hagan que los gráficos cambien y queden más pulidos para el público de nuestros datos. (clic en la imagen para ampliar).

Uno de los gráficos más populares que tiene Excel sin duda son los que constituyen el subgrupo de las barras o columnas, siendo el más representativo el gráfico de Columnas agrupadas. Este tipo de gráfico agrupa en columnas los datos que se conforman por series; donde una serie es un conjunto de datos que pueden o no estar asociados a una variable específica del entorno que queremos representar.

Sin embargo, este tipo de gráfico no es muy recomendable cuando queremos asociar a cada una de las series el total de los datos acumulados que componen dicha serie; Esto se debe a la aparición de una barra extremadamente larga que hace que la representación se vea desproporcionada respecto del resto de datos que se incluyen en el gráfico.

Una alternativa práctica y quizás muy elegante para representar los totales de cada una de las series dentro de un gráfico de barras o Columnas agrupadas, desde luego es y seguirá siendo al utilizar el eje secundario del gráfico. Donde en esencia se usará dicho eje para desplazar la(s) barra(s) de los totales que componen cada una de las series.

En este caso como la(s) barra(s) que representan los totales quedan aisladas en un eje independiente, es mucho más fácil su manipulación y/o modificación dentro del gráfico. Así, al estar en un eje independiente a la del resto de la serie pueden modificarse su ancho y superposición respecto de los otros componentes que se quieren mostrar como información relativa de un posible total general. (clic en la imagen para ampliar).

Al construir este tipo de gráficos deben tenerse en cuenta varios aspectos que yo considero concejos útiles que hacen que los gráficos hablen por si solos. El primero es no utilizar colores diferentes para representar las barras dentro de la serie (puedes usar colores degradados de un único color solido); El segundo es no saturar a tu espectador con proporciones que definan las medidas de las barras (puedes eliminar los ejes verticales [Principal y Secundario] y tu gráfico se leerá mejor).

Este tutorial te muestra un tipo de gráfico como el que acabo de describir.

Descarga aquí el libro de Excel.

Buscar el valor máximo y devolver registro(s)

noreply@blogger.com (Andres Rojas Moncada) — Tue, 31 Oct 2017 23:22:00 +0000

Los métodos de búsqueda en Excel suelen ser muy variados y no necesariamente constituyen aquellos que únicamente utilizan a las funciones nativas de Excel para buscar y devolver valores.

Funciones nativas como: BUSCAR, BUSCARV, BUSCARH, DESREF, ELEGIR, etc. etc. Que muchas veces son limitadas para realizar operaciones complejas de búsqueda bajo condiciones que se delimitan con ciertos criterios que se presuponen y no se disponen dentro de otras celdas en una hoja de Excel. (clic en la imagen para ampliar).

Imagine por un momento que usted tiene una tabla de datos que contiene alguna información de interés de la cual, una columna dentro de esa tabla posee valores numéricos y usted quiere extraer registros completos que correspondan a los valores máximos contenidos en ese campo que posee números.

En otras palabras, identificar implícitamente dentro de la columna que posee valores numéricos, el valor o los valores máximo(s) que contenga dicha columna y posteriormente extraer esos registro(s) completos asociados a esos valores.

Este caso que parece algo trivial y que quizás usted piense puede resolverse con una mera y simple función de Búsqueda y referencia, es en todo sentido un ejemplo complejo que constituye un método de búsqueda que puede emplear otras cuantas funciones que nada tienen que ver con las que buscan valores.

Por ejemplo, un método que use la aritmética básica para detectar errores y posiciones de registros que contienen valores de interés, de los cuales tras una simple división por cero pueden mostrar cuales registros podemos y no podemos extraer.

En este caso los errores pueden ser fácilmente tratables con otras funciones, no obstante, siempre debe tenerse presente cuales funciones son las más eficientes a la hora de usarlas para detectar estos valores.

Una función que en particular yo uso con frecuencia es AGREGAR; esta función tiene la particularidad de llamar otras funciones dentro de sí misma y dotarlas de ciertas características que de manera nativa estas últimas no poseen.

Por ejemplo, pueden llamarse funciones básicas como PROMEDIO, SUMA, MAX, MIN, K.ESIMO.MENOR, etc. etc. De la cual, tras ser aplicada dentro de AGREGAR, usted puede omitir los errores que contenga el argumento de la función nativa de manera correspondiente.

No solo se pueden omitir errores con AGREGAR, también tiene un sinnúmero de otras características útiles que pueden usarse bajo ciertos parámetros de operación con esta función.

Si quieres ver un tutorial interesante que trata este tema con gran detalle y resuelve un caso del entorno real, por favor sigue el enlace de más abajo y aprende algo nuevo sobre métodos de búsqueda en Excel.

Clic aquí para ir al video.

Clic aquí para descargar el libro de Excel.

Indicador porcentual de tiempo transcurrido por mes

noreply@blogger.com (Andres Rojas Moncada) — Fri, 27 Oct 2017 14:40:00 +0000

En esta publicación te voy a realizar una pregunta directa para entrar en contexto. ¿Es posible determinar el porcentaje de tiempo transcurrido de un mes particular teniendo como parámetro una fecha? La respuesta es Si.

Antes de explicarte en que consiste este ejercicio primero voy a detallarte con un ejemplo un caso concreto para que lo comprendas rápidamente. Clic en la imagen para ampliar.

Suponga que tiene una fecha cualquiera escrita dentro de una celda en una hoja de un libro de Excel. Esta fecha desde luego está constituida por un día, un mes, y un año, delimitados con un formato de fecha estándar (dd/mm/yyyy).

Como es de suponer, un mes de un año está constituido por días. Dependiendo del mes estipulado, este último puede tener 28, 30, ó 31 días (o 29 si el año es bisiesto).

Imagine que la totalidad de días de un mes que usted elija representa el 100% de días para ese mes. Entonces sin aplicar mucha lógica comprenderá que cada día de ese mes representa una fracción porcentual de ese 100% que representa todos los días para ese mes.

Por ejemplo, si un mes tiene 30 días, un solo día equivale a una fracción de 0.033 ó 3.33%. Si toma el mes de septiembre que tiene 30 días y agrupa mentalmente lo primeros 15 días, lógicamente usted tendrá agrupados el 50% de los días de ese mes.

Imagine ahora que tiene esa fecha dentro de la celda en una hoja de un libro de Excel; Pues bien, ¿Cómo haría para que una fórmula le diga cuanto porcentaje de días han transcurrido desde el inicio del mes que contiene esa fecha, hasta el día que se estipula en esa misma fecha? Clic en la imagen para ampliar.

Es decir, calcular inicialmente el primer día de ese mes y luego calcular cuántos días han transcurrido hasta la fecha que se estipuló en la celda. Esa cantidad de días que se obtienen como resultado de la operación inicial se dividen entre la cantidad de días totales de ese mes y así se obtiene un indicador porcentual de tiempo transcurrido por mes.

El método descrito anteriormente quizás no sea el más eficiente por la cantidad de cálculos que hacemos dentro de la celda, no obstante, para llegar a un término conceptual y explicarlo con simplicidad, este caso es bastante útil.

Si quieres observar un método más eficiente y rápido que nos lleva al mismo resultado, visualiza esta serie de 3 videos que he preparado cuidadosamente para explicarte con mucho detalle como calcular un indicador de este tipo.

Ir al video uno | Ir al video dos | Ir al video tres

Si quieres descargar el libro de Excel usado en los videos, haz clic aquí.

Formato condicional complejo - Construcción por partes

noreply@blogger.com (Andres Rojas Moncada) — Thu, 12 Oct 2017 16:00:00 +0000

Una actividad muy frecuente que muchos usuarios medios y/o avanzados de Excel desarrollan desde la interfaz de usuario de esta aplicación, es la implementación de formatos condicionales sobre sus trabajos.

Sin duda, un Formato condicional en Excel es bastante útil cuando queremos marcar una o varias celdas con un color o quizás una fuente específica para hacer énfasis sobre determinados datos.

En este punto usted debe comprender que, cuando se habla de Formato condicional en Excel, siempre se están refiriendo a reglas de validación que se aplican sobre una o varias celdas y que, de cumplirse, dichas celdas cambiaran su formato al especificado por el usuario de manera personalizada mediante la herramienta que Excel tiene desde la interfaz de usuario.

Por defecto Excel tiene una gama de reglas para aplicar formatos condicionales (prestablecidos) que el usuario puede utilizar con tan sólo seleccionar las celdas y hacer unos cuantos clics. Por ejemplo: Escalas de color, Barras de datos, Conjuntos de iconos, Reglas superiores e inferiores, etc.

Sin embargo, cuando se trata de resaltar celdas que cumplan alguna condición lógica algo loca (compleja), Excel no cuenta con esas reglas para establecer la validación adecuada que cumpla con nuestros requisitos.

En esos casos la aplicación nos pone a disposición un elemento importante dentro del cuadro de dialogo Nueva regla de formato. Este sencillo cajón que muchas personas no utilizan por desconocimiento, no es más que un validador de reglas personalizadas que se implementan a partir de fórmulas que devuelven valores booleanos (falso o verdadero). Clic en la imagen para ampliar.

Aquí hay que comprender que una fórmula tradicional por sí sola no sirve para implementar un formato condicional, puesto que muchas de esas fórmulas no devuelven valores lógicos booleanos de (falso o verdadero) que la herramienta Formato condicional entienda correctamente.

Sin embargo, sí que es válido construir con esas fórmulas estructuras que devuelvan dichos valores (falso o verdadero). Para ello, sólo debemos realizar comparaciones directas que involucren los resultados de las fórmulas con los valores de interés que deseamos utilizar como criterios para devolver las reglas que deseamos construir.

Por regla general, una comparación que devuelve un valor booleano sin duda utiliza los operadores de comparación. Esto que a usted le puede sonar algo tonto y hasta trivial, es la clave para crear las reglas de validación que pueden usarse dentro de un Formato condicional. Los operadores de comparación desde luego serán: el mayor, el menor, el igual, el mayor igual, el menor igual, y el diferente.

Si quieres observar un ejemplo práctico que detalle estos conceptos, haz clic aquí y aprende algo nuevo con este video.

El libro de Excel lo puedes descargar haciendo clic aquí.

Participación porcentual por mes con tabla dinámica

noreply@blogger.com (Andres Rojas Moncada) — Sat, 30 Sep 2017 14:36:00 +0000

Sin lugar a dudas, cuando se trata de presentar información de manera resumida para tomar alguna decisión, en lo primero que pensamos es, en una Tabla Dinámica.

Las Tablas Dinámicas en Excel son una herramienta poderosa a la hora de construir informes con los (datos crudos) que de manera estructurada debemos tener para pasarle como insumo a esta herramienta.

Podemos mediante sus opciones resumir los campos que contengan valores con operaciones como: suma, recuento, promedio, máximo, mínimo, producto, contar, etc. etc. (clic en la imagen para ampliar)

Además, esta herramienta nos permite darle (formato de número) a la información que queremos presentar. Estos formatos incluyen todos los que pueden aplicarse desde una celda cualquiera que de manera tradicional tengamos en una hoja de cálculo.

Sin embargo, algo que es realmente atractivo de esta herramienta de Excel, es que nos permite hacer algunos cálculos adicionales para encontrar de un modo muy rápido, relaciones porcentuales entre los campos que componen la tabla y la fuente de datos.

Estos campos pueden relacionarse con las filas o columnas ya procesadas en un informe de Tabla Dinámica; igualmente se procesan también con los totales generales calculados en cualquier otro campo; No obstante, la opción más poderosa es la de relacionar un campo de interés con un campo base para obtener indirectamente la relación porcentual de dos campos que no están ligados de manera natural en la fuente de datos.

Por ejemplo, imagine una fuente de datos estructurada que relacione las ventas de los comérciales de una organización. Una fuente de datos que contenga como mínimo: un campo fecha (día de la venta), un campo que describa el nombre del comercial, y un campo que describa el monto o valor de la venta realizada. (clic en la imagen para ampliar)

Usted puede obtener la relación porcentual mensual de las ventas de cada comercial de dos formas bien definidas, una que se tabule como relación del total general global de las ventas realizadas, y otra que se calcule respecto del total general parcial (mensual) para cada uno de los comérciales.

En este caso, para calcular la segunda relación porcentual usted puede pensar que la fecha es más que suficiente para llevar a cabo la operación, no obstante, como se quiere obtener respecto de cada mes (mensual), es vital agrupar el campo fecha por meses para que sirva de campo base para calcular la relación porcentual parcial de manera correcta para cada comercial.

Detalla este ejemplo con el siguiente video.

Descargar aquí el libro de Excel.

Asignar descripción a Función Definida por el Usuario

noreply@blogger.com (Andres Rojas Moncada) — Thu, 13 Jul 2017 22:24:00 +0000

Excel no sólo es famoso por poseer un gran número de funciones integradas que pueden servir literalmente para resolver muchos problemas con los datos; por el contrario, también lo es porque nos permite escribir nuestras propias funciones mediante el entorno Visual Basic para Aplicaciones o VBA.

Cuando un programador de esta aplicación decide escribir funciones que pueden usarse desde la interfaz de usuario estamos hablando de UDFs o Funciones Definidas por el Usuario.

Las UDFs al igual que las funciones integradas pueden procesar los datos y devolver un resultado; sin embargo, una diferencia con las UDFs respecto de las funciones integradas, es que las primeras no muestran por default una ayuda al usuario cuando este quiere utilizarlas mediante el cuadro de dialogo Insertar función.

Por ejemplo, suponga que implementamos una función (UDF) para el tratamiento de cadenas alfanuméricas con VBA en Excel; Una función con dos argumentos que extrae de cadenas de texto que posiblemente tengan números, únicamente sus números y los separa con un carácter separador de manera opcional.

UDF para extraer los números de una cadena alfanumérica - Ver resolución

Public Function NumerosSeparador(Cadena As String, Optional Separador As String = "") As Variant

Dim LargoCadena As Long

Dim i As Long

Dim Caracter As Variant

Dim Resultado As Variant

Dim Marca As Boolean

If Cadena = "" Then

NumerosSeparador = VBA.CVErr(xlErrNull)

Exit Function

End If

LargoCadena = VBA.Len(Cadena)

Marca = False

For i = 1 To LargoCadena

Caracter = VBA.Mid(Cadena, i, 1)

If VBA.IsNumeric(Caracter) And Separador = "" Then

Resultado = Resultado & Caracter

Else

If VBA.IsNumeric(Caracter) Then

Resultado = Resultado & Caracter

Marca = True

ElseIf Marca Then

Resultado = Resultado & Separador

Marca = False

End If

Next i

If VBA.IsEmpty(Resultado) Then

NumerosSeparador = VBA.CVErr(xlErrNA)

ElseIf Separador = "" Or Marca Then

NumerosSeparador = Resultado

Else

NumerosSeparador = VBA.Mid(Resultado, 1, VBA.Len(Resultado) - VBA.Len(Separador))

End If

End Function

Si únicamente se implementara esta función dentro de un módulo en VBA del Excel, con total certeza no obtendremos la ayuda correspondiente en los cuadros de dialogo Insertar función y Argumentos de función que las funciones tradicionales si muestran (clic para ampliar).

Una manera efectiva para solucionar este inconveniente es crear un Procedimiento Sub que cargue mediante el método MacroOptions del objeto Application, todos los componentes descriptivos de la UDF.

Procedimiento Sub para cargar el componente descriptivo de la UDF

Sub EjecutarAlAbrir()

Dim NombreDeLaFuncion As String

Dim DescripcionDeLaFuncion As String

Dim CategoriaDeLaFuncion As Variant

Dim DescripcionDeLosArgumentos(1 To 2) As String

NombreDeLaFuncion = "NumerosSeparador"

DescripcionDeLaFuncion = "Devuelve los números de una cadena " & _

"alfanumérica con un carácter separador opcional."

CategoriaDeLaFuncion = "Tratamiento de cadenas"

DescripcionDeLosArgumentos(1) = "Referencia de celda que " & _

"contiene una cadena alfanumérica."

DescripcionDeLosArgumentos(2) = "Opcional. Carácter separador " & _

"por el cual se dividirá el número resultante."

Application.MacroOptions Macro:=NombreDeLaFuncion, _

Description:=DescripcionDeLaFuncion, Category:=CategoriaDeLaFuncion, _

ArgumentDescriptions:=DescripcionDeLosArgumentos

End Sub

Cada componente descriptivo de la UDF puede cargarse en una variable independiente dentro del método MacroOptions; Incluso puede anclarse la función dentro de una categoría personalizada o una categoría ya existente. Si se trata de una categoría personalizada sólo deberá especificarse el nombre de la nueva categoría; en cambio si se trata de una categoría existente deberá usarse un valor numérico de la siguiente tabla (clic para ampliar).

Ejecutar el Procedimiento Sub hará que los componentes descriptivos se carguen en los cuadros de dialogo correspondientes al momento de usar a la función en la interfaz de usuario, sin embargo, si cierra y abre el libro de Excel nuevamente estos componentes se perderán. La solución más recomendable es ejecutar el Procedimiento Sub que carga a los componentes descriptivos cada que se abra el libro de Excel; esto puedes lograrlo si llamas al procedimiento desde el evento Open del objeto ThisWorkbook.

Ejecutar el Procedimiento Sub al abrir el libro de Excel

Private Sub Workbook_Open()

Call Apertura.EjecutarAlAbrir

End Sub

Así, todo quedará más entendible para un usuario final cuando quiera usar las UDFs que usted haya programado para hacerle la vida más fácil.

• El método que implementa la carga de los componentes descriptivos de una función (UDF) se implementó por Sergio Alejandro Campos – EXCELeINFO.

• Descargar el libro de Excel.

• Ir al video y observar la resolución.

Buscar registros únicos o duplicados con fecha más reciente

noreply@blogger.com (Andres Rojas Moncada) — Sat, 08 Jul 2017 15:00:00 +0000

Quiero plantearles a todos ustedes un caso muy peculiar que me gustaría resolvieran en su tiempo libre y así entrenen un poco con Excel para que adquieran más habilidades con esta aplicación.

El tema que quiero tratar se basa principalmente en la extracción de registros de una base de datos que contienen registros únicos y duplicados. Para ello quiero que observes detenidamente la imagen que presento a continuación (clic para ampliar).

Como pudiste analizar en la imagen, se trata de una base de datos que relaciona en su primera y segunda columna, el código y el nombre de personas (respectivamente) que pueden encontrarse de manera única o duplicada dentro de los registros de la base de datos.

En la última columna se encuentran registrados valores de fecha; estas fechas son únicas para cada persona. Es decir, si una persona se encuentra duplicada en la base de datos, los valores de fecha correspondientes para cada registro duplicado de esa persona siempre serán diferentes uno del otro.

Por ejemplo, el código 2826 (remarcado con rojo) que relaciona a María Quevedo se encuentra dos veces dentro de la base de datos. Ambos registros poseen fechas diferentes (15/06/2016 y 21/02/2017).

¿Cuál es el problema?

Se requiere extraer de la base de datos todos los registros de manera única (uno sólo por persona cuando existan duplicados) del cual su fecha asociada corresponda a la más reciente o la más actual.

Por ejemplo, para el código 2826 (remarcado con rojo) que relaciona a María Quevedo se debería extraer el registro con fecha 21/02/2017 (relleno amarillo).

Tenga en cuenta que los registros de la base de datos pueden estar ordenados en cualquier posición. Por ejemplo, para el código 2826 (remarcado con rojo) debe extraerse el segundo registro (relleno amarillo); en cambio para el código 22201 (remarcado con azul) que relaciona a Manuel Ubillus debe extraerse el sexto registro (relleno amarillo).

Para identificar un registro que corresponda a una persona que esté duplicada sólo ten en cuenta las columnas: Código y Nombre. Al finalizar el ejercicio sólo deberás extraer únicamente los registros con relleno amarillo. Imagina una base de datos con más de 500 registros.

Déjame tu solución en los comentarios de más abajo. Recuerda, puedes utilizar cualquier método que desees, lo único es que los extraigas de la base de datos a otro lugar de una hoja de Excel sin hacerlo manualmente (usa fórmulas, funciones, código VBA, lo que quieras). Reto personal - No para el premio.

Posdata: mientras escribo esto yo ya he imaginado tres posibles soluciones.

Como premio para la primera persona que me entregue una solución válida (utilizando sólo las herramientas de la interfaz de usuario de Excel - ~~No VBA~~), le entregaré una suscripción por un año a la Suite de Office 365. La solución que implementes debes describirla muy bien en los comentarios del post. Válido hasta el 30 de julio de 2017 - inclusive.

• Ver la resolución en video.

• Descargar el libro de Excel.

Milisegundos en fechas y horas de Excel

noreply@blogger.com (Andres Rojas Moncada) — Wed, 05 Jul 2017 02:30:00 +0000

Al trabajar con tiempos en Excel no es muy frecuente utilizar la cantidad exacta de milisegundos que devuelven las funciones que insertan la fecha y la hora del sistema en las celdas de una hoja de algún libro de Excel.

Sin embargo, en algunas ocasiones muy particulares si que podemos necesitar de estas pequeñas cifras para tratar algún problema que estemos resolviendo con esta hoja de cálculo.

Pues bien, en este artículo te voy a describir como obtener de una hora (insertada desde función) la cantidad de milisegundos exacta con que fue cargada en una celda. Además, te mostraré como recuperar e insertar en otras celdas esos milisegundos como números enteros mediante dos fórmulas muy sencillas.

Antes de comenzar a explicarte cada una de las fórmulas quiero que comprendas que cuando se trabajan con horas y/o fechas dentro de esta aplicación, Excel internamente siempre representa una fecha como un número entero secuencial (por ejemplo, el 04 de jul de 2017 equivale al número 42920), y una hora del día como un valor decimal mayor que cero y menor que uno (por ejemplo, 0.45 denota las 10:48:00 a.m.).

El camino más sencillo para obtener de una hora insertada desde función el número exacto de milisegundos con que se introdujo desde el sistema hacia una celda de Excel, es cambiando el Formato de numero personalizado de la celda de la siguiente manera.

Formato de número personalizado para leer los milisegundos de una hora

hh:mm:ss.000

Nota: utilice el carácter separador decimal de su sistema para especificar los ceros.

Recuperar los milisegundos de una hora como valores enteros no es una tarea complicada. Lo primero que debes entender es que si una hora la representa Excel como un valor decimal (una fracción de la unidad), probablemente no podrás recuperarla haciendo uso de cálculos que extraigan residuos de dicha hora. Debe usarse alguna técnica intermedia que trate a la hora como una cadena de texto para extraer algunos de sus caracteres y posteriormente volver a convertirla a un valor numérico.

Fórmula para recuperar milisegundos de una hora como valores enteros

=VALOR(DERECHA(TEXTO(A1,"dd/mm/yyyy hh:mm:ss.000"),3))

Nota: suponga que en la celda A1 usted ha introducido la fecha y la hora de su sistema mediante la función AHORA.

Insertar una cantidad de milisegundos (como valores enteros) dentro de una hora que se ha escrito manualmente desde el teclado tiene el mismo inconveniente que se presenta a la hora de recuperar estos milisegundos. El problema radica en que no se pueden agregar como una simple suma aritmética debido a que una hora es una fracción decimal y los milisegundos son valores enteros. La solución está en convertir primero los milisegundos en la fracción decimal de tiempo que representan y posteriormente si sumarlos a la hora que se requiera.

Fórmula para insertar milisegundos como valores enteros a una hora

=A1+(A2/8.64E7)

Nota: suponga que la celda A1 contiene una hora escrita desde el teclado (por ejemplo, 15:25:30) y la celda A2 contiene un número entero de 450 para representar esa cantidad de milisegundos.

Si formatea la celda que utilice para realizar el cálculo con el formato de número personalizado (hh:mm:ss.000) obtendrá la adición de los 450 milisegundos a la hora escrita en la celda A1.

La expresión (8.64E7) relaciona en notación científica los 86 millones 400 mil milisegundos que tiene un día cualquiera.

• Observaeste video para comprender a fondo las fórmulas.

• Descarga el libro de Excel.

Rango dinámico de celdas para crear tabla dinámica

noreply@blogger.com (Andres Rojas Moncada) — Fri, 30 Jun 2017 15:19:00 +0000

Crear y/o construir una Tabla dinámica es una tarea casi que habitual en muchos usuarios de Excel. Las tablas dinámicas más que hacer la vida fácil son una herramienta poderosa que nos permite resumir la información para tomar decisiones.

Construir una tabla dinámica en Excel es muy sencillo; sólo debe tenerse una base de datos (tabla de datos) correctamente estructurada para lograrlo. Para crearla sólo debes ejecutar el comando Tabla dinámica ubicado en el grupo Tablas de la pestaña Insertar.

El cuadro de dialogo Crear tabla dinámica acepta de muchas formas a las bases de datos; puede introducirse directamente un rango de celdas dispuesto en una hoja de Excel, o bien una tabla propiamente hablando.

Sin embargo, una manera poco convencional por no decir que practica a la hora de crear tablas dinámicas dentro de Excel es utilizando nombres que referencien a fórmulas que construyen rangos de celdas dinámicos. En este contexto resulta muy obvio decir que también es válido dicho método.

Por ejemplo, suponga que se dispone de una hoja de Excel exclusiva para almacenar únicamente datos sobre las comisiones de las ventas de los comerciales de una organización. Esta hoja que sólo contendrá dicha información es una candidata perfecta para implementar un nombre formulado que genere un rango dinámico de celdas que nos sirva como insumo para crear una o más tablas dinámicas en otras hojas del libro.

Este nombre que referenciará una formula sólo debe cumplir un único requisito, debe apuntar a un rango de celdas (tabla de datos) que contenga al menos una columna o campo cuyos valores no contengan celdas vacías. Esto es necesario ya que la fórmula que se implementará usará a dicha columna para estimar el alto del rango de celdas que creará dinámicamente a la base de datos que se pasará a la tabla dinámica.

La fórmula que utiliza a la función de Excel DESREF es la que básicamente creará un rango dinámico de celdas que se actualiza cada vez que ingresemos valores dentro de la hoja. Esta fórmula que puede ser asignada a un nombre mediante el Administrador de nombres (pestaña Fórmulas • grupo Nombres definidos), puede ser incorporada directamente mediante dicho nombre a la hora de crear la tabla dinámica.

Ejemplo de fórmula que genera un rango dinámico de celdas

=DESREF($A$1,0,0,CONTARA($A:$A),5)

En consecuencia, cada vez que quieras crear una tabla dinámica en una hoja diferente del libro de Excel que contiene la base de datos y el nombre formulado, sólo deberás crear la tabla dinámica y especificar dicho nombre como fuente de datos.

• Observa este video tutorial para analizar el método.

• Descargar el libro de Excel.

3 métodos que retornan la fecha final de un mes, dada una fecha inicial

noreply@blogger.com (Andres Rojas Moncada) — Tue, 13 Jun 2017 03:00:00 +0000

Cualquier usuario de Excel (sea principiante, medio, o avanzado) en alguna ocasión ha trabajado con fechas dentro de una base de datos dispuesta en esta aplicación.

Quizás muchos de nosotros en muy pocas ocasiones ha necesitado realizar operaciones con fechas dentro de las celdas de Excel; del mismo modo, y aún menos frecuente es tener que devolver mediante cálculo el día final de un mes dada una fecha inicial. Sin embargo, en algún momento esto tendrá que pasar por tus manos y deberás saber cómo resolver este inconveniente.

Pues bien, en este post te voy a explicar tres métodos muy sencillos para devolver o retornar la fecha final de un mes (día final de un mes) teniendo como punto de partida una fecha inicial que puede corresponder a cualquier día particular de un mes específico (incluyendo hasta el último día del mes).

Lo primero que debes comprender, es que una fecha en Excel está representada por un número de serie secuencial (por ejemplo, el 15 de junio de 2017 “15/06/2017” equivale al número de serie 42901). Si a este número de serie le sumamos una unidad (42902), lo lógico será que devuelva el (16/06/2017).

Esto te puede llevar a pensar que si una fecha global (15/06/2017) opera como un entero, pues sus agregados de año, mes, y día también operan como números enteros. Es decir, si tenemos el año: 2017, el mes: 06, y el día: 15, en esencia podemos armar una fecha con estos valores.

¿Cómo opera la función FECHA?

Una función no muy usada con frecuencia en Excel es FECHA. Esta función que tiene la capacidad de armar una fecha con tres valores enteros que representan valores para el año, mes, y día respectivamente, es sin duda la más versátil para convertir números enteros desagregados, en valores secuenciales que Excel entiende como fechas.

La sintaxis básica de la función FECHA(año,mes,día) es muy sencilla de comprender. Un número de cuatro dígitos para el año (2017), un numero de dos dígitos para el mes (06), y un número de dos dígitos para el día (15).

---

En la interfaz de usuario se debe tener en cuenta que Excel inicia el conteo de fechas desde el (01 de enero de 1900), es decir, desde el año 1900; Por tanto, especificar un número menor para el argumento año (<1900) de la función FECHA hará que dicho valor se sume al valor de 1900. Por ejemplo, FECHA(1,06,15) devolverá el (15 de junio de 1901).

Así mismo, algo similar ocurre con los argumentos mes y día de la citada función, en donde si sobrepasamos (mes: >12, día: >31) o retrocedemos (mes: <1, día: <1) el número máximo o mínimo en los límites de los meses de un año, o los días de un mes específico, estos se agregan como números respectivos de serie secuencial a la fecha generada por la función FECHA.

Este concepto no muy fácil de entender es la clave para poder devolver cualquier día que usted requiera con el uso de la función que le mencioné anteriormente, inclusive el día final de un mes cualquiera.

Primer método - Utiliza a la función FECHA

=FECHA(AÑO(A1),MES(A1)+1,0)

Suponga en la celda A1 una fecha como el: 15/06/2017, y en la celda B1 una fórmula como la anterior.

La función AÑO extrae el año de la fecha y lo pasa como primer argumento de la función FECHA (hasta aquí podemos decir que el año es 2017); La función MES extrae el mes correspondiente a la fecha y lo pasa como segundo argumento de la función FECHA (si sumamos una unidad a la expresión MES, el resultado es 07); Por último, si especificamos un cero como el número inmediatamente anterior al límite inferior de los días de un mes, esa unidad se restará al valor generado por el argumento mes de la función FECHA (como consecuencia el número de serie secuencial se reducirá en una unidad para el día del mes actual “7” y dará como resultado el día final del mes 06).

---

Segundo método - Utiliza a la función FECHA

=FECHA(AÑO(A1),MES(A1)+1,1)-1

Suponga la fecha inicial como el 15/06/2017 en la celda A1

Esta fórmula es bastante similar a la anterior, sin embargo, en esta devolvemos el primer día del siguiente mes (01/07/2017), del mes que se específica en la fecha inicial. Por ende, si restamos una unidad a toda la expresión, el resultado será un número secuencial que equivale al último día del mes de la fecha inicial.

---

Tercer método - Utiliza a la función FIN.MES

=FIN.MES(A1,0)

Suponga la fecha inicial como el 15/06/2017 en la celda A1

Esta es una de esas funciones de la cual su nombre la describe a la perfección; En esta simplemente pasamos en su primer argumento la fecha inicial, y en su segundo argumento (que realiza un desplazamiento de meses hacia adelante o atrás del mes de la fecha inicial) especificamos un movimiento de cero meses para indicar el día final del mes contenido en la fecha inicial.

---

• Observa la demostración en video.

• Descarga el Libro de Excel.

Contar y sumar por rangos de fecha (mes) con SUMAPRODUCTO

noreply@blogger.com (Andres Rojas Moncada) — Fri, 09 Jun 2017 13:00:00 +0000

Las hojas de cálculo en la actualidad suelen utilizarse para realizar labores muy complejas, o al menos, para resolver escenarios que de manera manual resultarían muy dispendiosos de trabajar.

Sin embargo, algo que muchos usuarios básicos, medios, o avanzados hacen con mucha frecuencia en las hojas de cálculo (en especial las de Excel), son operaciones matemáticas que permiten contar y/o sumar cantidades dentro de uno o varios rangos de valores dispuestos en las celdas de dichas hojas.

Más aún, se da el caso en que algunos usuarios requieren implementar algunas restricciones con otros valores contiguos diferentes a los que son susceptibles de ser operados para contar o sumar.

Por ejemplo, una tarea muy rutinaria en los puestos de trabajo que administran los inventarios (de un sinnúmero de organizaciones) es conocer la cantidad de transacciones y/o la suma de los elementos de algún producto que se movió dentro del inventario en un periodo de tiempo determinado (por ejemplo, en un mes específico).

En Excel hay varias maneras para realizar estas operaciones aritméticas (con restricciones); una de ellas es utilizando a las funciones CONTAR.SI.CONJUNTO y/o SUMAR.SI.CONJUNTO.

Sin embargo, cuando se trata de realizar una operación rápida que permita conocer los resultados de algunos movimientos dentro de una base de datos para contar y/o sumar valores por periodos de tiempo, indiscutiblemente la función SUMAPRODUCTO se lleva el primer premio por su versatilidad y sencillez a la hora de ponerla en marcha dentro de las celdas de Excel.

Esta función que prácticamente opera multiplicando los elementos equivalentes (en la misma posición) de las matrices de sus múltiples argumentos para posteriormente sumarlos, se convierte en una función versátil si de manera explícita suponemos el producto de las diferentes matrices dentro de un único argumento (el primer argumento de la función SUMAPRODUCTO).

Al implementar esta función en una celda con este método logramos simplificar el cálculo y obtener un resultado mucho más rápido.

Así, si suponemos a cada una de las matrices que se deben operar con esta función como expresiones únicas separadas por el símbolo multiplicación (*) dentro de un solo y único argumento con SUMAPRODUCTO, el conteo o la suma se obtienen de manera natural sin convertir los valores lógicos a valores numéricos cuando se emplean los operadores de comparación.

• Para que lo comprendas mejor observa este video tutorial.

• Si quieres profundizar en el concepto utiliza este Libro de Excel.

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

noreply@blogger.com (Andres Rojas Moncada) — Sun, 28 May 2017 06:52:00 +0000

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

noreply@blogger.com (Andres Rojas Moncada) — Wed, 24 May 2017 14:35:00 +0000

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

noreply@blogger.com (Andres Rojas Moncada) — Thu, 18 May 2017 05:15:00 +0000

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

noreply@blogger.com (Andres Rojas Moncada) — Mon, 15 May 2017 16:19:00 +0000

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

<tabs>

<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

noreply@blogger.com (Andres Rojas Moncada) — Fri, 05 May 2017 23:58:00 +0000

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.

Principales controles en la cinta de opciones de Excel - XML y VBA

noreply@blogger.com (Andres Rojas Moncada) — Tue, 25 Apr 2017 06:23:00 +0000

En la actualidad existen muchas personas que se consideran expertas en Excel o al menos con conocimientos avanzados sobre esta herramienta, sin embargo, al cuestionarlos con temas tan simples como la identificación de los diversos controles con que operan a diario dentro del Excel, sus respuestas son casi desconcertantes.

Esta y otras cuestiones más, han hecho que me detenga por un momento y escriba este artículo para tratar de exponer los diversos y más frecuentes controles con los que un usuario básico, o inclusive experto, puede llegar a encontrarse cuando opera con este aplicativo.

Aunque usted no quiera conocer de historia sobre Excel, déjeme actualizarlo un poco diciéndole que desde la Versión 2007 en adelante, Microsoft apostó por una interfaz de usuario nueva para su Suite de Office. Una suite que innovó en aquellos años principalmente por cambiar su viejo esquema de menús para trabajar dentro de los aplicativos, por una Cinta de opciones compuesta por múltiples Pestañas rellena de Controles a la que denominó: Ribbon.

Para describir desde un punto de vista técnico los principales controles que usted puede encontrar dentro de Excel, es necesario categorizarlos en dos grandes esquemas, los Controles globales (que funcionan como contenedores), y los Controles de acción (que funcionan como disparadores o desencadenadores de las herramientas y rutinas del aplicativo).

Controles globales

• Ribbon o Cinta de opciones

Aunque usted no lo crea, la cinta de opciones o Ribbon es un control dentro de Excel. Un control que no puede dilucidarse a simple vista debido a que actúa como recipiente de otros controles que están contenidos dentro de dicha cinta.

Para comprenderlo mejor imagine que la cinta de opciones es todo el bloque superior de la aplicación cuando usted abre un libro de Excel en blanco.

• Pestañas

Estos controles son más sencillos de identificar, puesto que son la primera impresión que el usuario final tiene al desplazarse por las diversas funcionalidades del aplicativo.

Las pestañas tienen un rol importante dentro de Excel, ya que son ellas las que permiten ordenar y clasificar las innumerables funcionalidades que tiene esta herramienta. Por ejemplo, la Pestaña Datos contiene todo el conjunto de funcionalidades para operar con los datos como si se tratase de una sola entidad.

Funcionalidades como: obtención de datos desde fuentes externas, trasformación, conexiones entre diversas fuentes y Excel, ordenamiento, esquemas, etc. etc.

• Grupos

Aunque son sencillamente identificables dentro de una pestaña en la cinta de opciones, un grupo no tiene una funcionalidad concreta que realice algo para el usuario final.

La única tarea que cumple un grupo dentro de una pestaña es la de sub-categorizar las funcionalidades que especifica la pestaña que lo contiene. De esta manera no encontraremos los múltiples controles que lanzan esas funcionalidades desperdigados por toda la pestaña.

Por ejemplo, en la Pestaña Inicio podemos ver el Grupo Alineación, el cual cuenta con funcionalidades para ajustar, orientar, etc. Los caracteres dentro de una o varias celdas.

Controles de acción

• Botón de comando

Este es el control más sencillo que usted podrá encontrar en toda la cinta de opciones. De hecho, su característica sólo radica en recibir un clic por parte del usuario para ejecutar su acción. Esta acción puede desplegar un formulario, una ventana, o ejecutar una funcionalidad sobre algún objeto seleccionado.

Puede encontrar los botones de comando de dos formas: grandes y chicos. Un ejemplo claro de este control lo ubicará en la Pestaña Insertar en los grupos Tablas y Complementos (Tabla y Tienda) de la versión 2016 de Excel.

• Casilla de verificación

Este tipo de control es bastante simple. Como su nombre lo indica es una pequeña casilla con una etiqueta que puede ser activada o desactivada por parte del usuario de la aplicación. Si la casilla está activa mostrará una marca dentro del recuadro blanco, si no lo está, el recuadro permanecerá blanco.

Un ejemplo de este control puede ubicarlo en el Grupo Mostrar de la Pestaña Vista. Observe que este grupo está conformado únicamente por este tipo de control.

• Caja de texto

Este control por lo regular no se encuentra en las pestañas convencionales de la cinta de opciones de Excel. Sin embargo, si crea una tabla con un rango de celdas podrá apreciar este control dentro del Grupo Propiedades ubicado en la Pestaña Diseño de las Herramientas de tabla que aparece de manera emergente al ubicar la celda activa dentro de la tabla recién creada.

Básicamente esta caja de texto se utiliza para pedir información al usuario mediante la escritura manual desde teclado. Esta información puede ser utilizada para muchos fines dentro del aplicativo.

• Botón de activación

Los controles de activación, conocidos por muchos programadores como botones de activación, son muy similares al Botón de comando. La diferencia de este último con el botón de activación radica principalmente en que este queda (activo o encendido) una vez el usuario final hace clic sobre él.

Esta característica que está muy bien marcada en este tipo de controles lo hace ideal para utilizarlo al realizar ciertas tareas con restricciones mientras el control está activo. Una vez el usuario desactiva el control la tarea vuelve a realizarse con normalidad.

Por ejemplo, el clásico botón de activación lo podemos encontrar en la Pestaña Inicio del Grupo Alineación. Ajustar texto desde luego es un botón de activación que configura el texto de una o varias celdas que por lo regular es extenso, en varias líneas para poder verlo todo.

Usted puede encontrar varias formas diferentes para el botón de activación; chico con icono y etiqueta, chico únicamente con icono, grande con icono y etiqueta, grande únicamente con icono. Las dos últimas se personalizan con XML.

• Listas (permite escribir - no permite escribir)

En Excel se permiten configurar dos tipos diferentes de listas. Las que aceptan la inserción de texto por parte del usuario final, y las que no lo aceptan.

Por alguna razón Excel no utiliza las listas que no permiten la inserción de texto por parte del usuario. Los desarrolladores de Microsoft no dan una explicación clara sobre este aspecto. Sin embargo, mediante XML es posible insertar este tipo de controles a la cinta de opciones.

El control lista es muy parecido en forma a la Caja de texto, salvo que tiene en su parte derecha un pequeño triangulo apuntando hacia abajo, e indica que dicho control contiene elementos seleccionables dentro de él.

Un ejemplo de este control es el que contiene las fuentes que se pueden aplicar al texto dentro de una celda. Puedes ubicarlo en la Pestaña Inicio, Grupo Fuente.

• Separador

Sin muchos detalles que decir, el control separador no es nada más que una simple línea vertical que divide unos controles de otros dentro de un grupo. Un ejemplo de este tipo de control puede observarse en los Grupos Fuente, Alineación, y Número de la Pestaña Inicio.

• Menú

La apariencia de este control es muy similar a la del Botón de comando. Puede presentarse de dos tamaños en la cinta de opciones (grande y chico); y se identifica fácilmente debido a que posee un pequeño triangulo apuntando hacia abajo en la parte derecha del control.

Un menú puede contener otros tipos de controles, por ejemplo, pueden incorporarse botones de comando, casillas de verificación, e inclusive otros menús (sub-menú).

Un ejemplo de este tipo de control puede observarse en el Grupo Edición de la Pestaña Inicio. Efectivamente el control Rellenar es un claro ejemplo de un menú.

• Menú con botón

La descripción más simple que puede usarse para comprender este control es imaginar un Botón de comando y un Menú unidos en un solo control. En esencia el botón de comando se posiciona en la parte superior y el menú en la inferior.

Pueden apreciarse dentro de la cinta de opciones de dos formas diferentes (grande y chico). Si es grande, ambos controles unidos se muestran grandes, si es chico, ambos se muestran chicos.

Este control es bastante versátil debido a que puede ejecutar una funcionalidad directamente con su botón superior. Si se necesitan ejecutar otras funcionalidades se pueden desplegar dentro del menú en la parte inferior.

Un ejemplo puede observarse en el Grupo Alineación de la Pestaña Inicio. El control Combinar y centrar es efectivamente un Menú con botón.

• Menú separador

Este control tiene la misma función que el Control Separador, sin embargo, presenta dos cambios bien definidos. El primero, es que sólo se puede incorporar dentro de controles menú y listas; El segundo, es que puede añadírsele una etiqueta a modo de título.

Su posición es horizontal dentro del menú. Un ejemplo concreto de este control puede apreciarse en el Grupo Fuente, Pestaña Inicio, en el control que define el tipo de fuente para una celda.

• Etiqueta

Funciona como un pequeño cajón que contiene texto que no se puede seleccionar ni cliquear. Este tipo de control opera principalmente para añadir títulos descriptivos que sirven de indicación al usuario final sobre la funcionalidad de otros controles.

El único grupo que contiene este tipo de control se ubica en la Pestaña Diseño de página, Grupo Opciones de la hoja.

• Lanzadera de grupo

Este tipo de control no es propiamente hablando un control en el sentido estricto de la palabra; Es más bien una configuración que se hace sobre un grupo y tiene la finalidad de añadir un Botón de comando en la esquina inferior derecha de este último.

Su forma es algo particular, ya que se corresponde con un pequeño cuadrado que tiene dentro de sí una diminuta flecha ubicada diagonalmente. Este control por lo general despliega cuadros de dialogo que llevan a cabo tareas adicionales que complementan la funcionalidad del grupo y la pestaña.

Puede observar un control de este tipo en la Pestaña Diseño de página, en el Grupo Configurar página.

---

Para finalizar sólo quiero comentar que estos no son todos los controles existentes que incorpora Excel dentro de su entorno de trabajo en la cinta de opciones. No obstante, quiero que quede claro que los controles mencionados en este artículo presuponen la mayoría que se pueden encontrar en los grupos que están dentro de las diversas pestañas.

Si quieres ver un ejemplo de cada control mencionado en este artículo implementado desde XML y VBA dentro de Excel, observa este video.

Si piensas que olvide mencionar algún control dentro de esta lista por favor déjamelo en los comentarios, con gusto lo añado y lo pongo a disposición de toda la comunidad.

Filtro avanzado para extraer registros por el largo de una cadena

noreply@blogger.com (Andres Rojas Moncada) — Tue, 28 Mar 2017 20:56:00 +0000

La definición más sencilla que puede describir a un Filtro avanzado en Excel, es sin duda, aquella acción que se realiza con el uso de la herramienta Filtro avanzado que se despliega desde el comando Avanzadas del grupo Ordenar y filtrar de la pestaña Datos.

Este pequeño cuadro de dialogo que se despliega tras ejecutar dicho comando, es en esencia un formulario muy potente que nos permite especificar Opciones avanzadas de filtrado con criterios complejos.

Estos criterios que por lo general son escritos en celdas independientes a la base de datos que queremos filtrar, constituyen el cien por ciento de las veces resultados booleanos que el cuadro de dialogo Filtro avanzado interpreta para extraer los registros de la base de datos y llevarlos hacia el área de extracción.

El área de extracción que es un área especial donde se disponen los resultados de la acción del filtro, es una opción muy acertada con la que cuenta el formulario mencionado anteriormente. Donde con tan solo activar una opción desde el formulario y luego especificar el rango de celdas de extracción, el Filtro avanzado lleva los datos a otro lugar diferente de la base de datos.

Por ejemplo, imagine un caso hipotético donde se cuenta con una base de datos de cinco columnas y en una de ellas existen valores numéricos que representan códigos de productos.

Estos códigos de productos que pueden oscilar en su tamaño, pueden contener desde un carácter de largo, hasta los ocho caracteres.

Entonces mi pregunta para usted seria...

¿Cómo extrae las filas que tienen 2, 4, y 6 dígitos de largo?

Pues muy simple, le doy una idea, con un Filtro avanzado que configure su Rango de criterios con una fórmula que utilice a la Función O y a la Función Largo de Excel en una sola celda de manera muy parecida a la siguiente:

=O(LARGO(C2)=2,LARGO(C2)=4,LARGO(C2)=6)

Esta función parece no tener mucho sentido, pero si la aplicas teniendo en cuenta todos los conceptos explicados en este Curso de Filtros avanzados en Excel, de seguro comprenderás que la fórmula tiene mucha relevancia.

• Si quieres comprender este caso de manera más detallada observa este video.

• El libro de trabajo de Excel lo puedes descargar haciendo clic aquí.

Convertir tabla de datos en Excel a un archivo XML

noreply@blogger.com (Andres Rojas Moncada) — Mon, 27 Mar 2017 03:51:00 +0000

Podría hablar largo y tendido sobre los múltiples usos que tiene el lenguaje XML en la actualidad sobre los sistemas de información. Sin embargo, hacerlo no tendría mucho sentido puesto que Excel sólo lo utiliza en unos cuantos contextos que se podrían enumerar con los dedos de una mano.

Por ejemplo, el principal uso que puede ofrecer el lenguaje XML sobre Excel radica principalmente en crear y gestionar los múltiples controles que pueden cargarse sobre la personalización de la cinta de opciones del aplicativo. No obstante, eso es un tema que no viene al caso sobre el propósito de esta publicación. Pero tranquilo, si eso es lo que te trae hasta aquí, te diré que muy pronto iniciaremos con esos conceptos...

Otro contexto del lenguaje XML sobre Excel, es básicamente el transporte de información para recibir y generar datos en archivos con estructuras XML dentro del aplicativo.

En este punto debo decirte que XML no es un formato como muchas personas suelen pensar, por el contrario, es más que eso; es un lenguaje de marcado muy completo que nos permite crear estructuras muy flexibles y sólidas que se utilizan para disponer la información de forma segura y estructurada, cuyo objetivo es transportarla entre los diversos sistemas de información con costes de peso y tamaño relativamente pequeños.

Así, si tratas de imaginar que es eso de XML y con que se come ese lenguaje, pues prácticamente te diré que XML no es nada más que un estándar creado hace varias décadas para escribir estructuras que definen etiquetas que se conforman como un patrón específico para disponer datos (lenguaje meta).

Por ejemplo, suponga que se necesita alimentar una base de datos relacional como SQL Server que opera dentro de un sistema de información dispuesto dentro de un servicio web que recibe información en archivos XML para llenar una tabla de datos con cuatro campos o columnas.

Estos cuatro campos o columnas que pueden denominarse en este ejemplo como: Línea de negocio, Tipo de moneda, Activos, y Pasivos; pueden disponerse transitoriamente en forma de tabla en un libro de Excel y luego comenzar a escribir los datos dentro de la tabla.

Una vez que toda la información esté registrada dentro de la tabla y estemos conformes con su contenido, podemos fácilmente crear una estructura base en forma de etiquetas con extensión .xml siguiendo este estándar y las reglas con las que se creó el sistema de información para generar un archivo XML y transmitir los datos.

Por ejemplo, una estructura XML como la siguiente:

<?xml version="1.0" encoding="UTF-8"?>

<FT009>

</RegistroFT009>

</FT009>

Donde cada etiqueta dispuesta dentro de la capa base, es un contenedor que llevará cada uno de los datos de cada registro o fila de la tabla de Excel hacia la tabla de datos de la Base de datos relacional.

• Si quieres detallar esto más claramente observa este video.

• Quieres activar la pestaña Desarrollador o Programador en Excel, sigue este enlace.

• Observa este instructivo creado por la Superintendencia de salud de Colombia que refleja un caso real en la creación de un archivo XML.

• Si observó el video del caso de estudio de esta publicación y quieres practicar con los mismos datos de ejemplo, bájate los archivos haciendo clic aquí.

Sumar celdas de un rango con criterio sensible a mayúsculas y minúsculas

noreply@blogger.com (Andres Rojas Moncada) — Wed, 08 Mar 2017 15:25:00 +0000

¿De qué sirve sumar un rango de celdas si no se puede seleccionar un criterio para refinar la operación?

Cuando se trata de operar con matrices dentro de Excel, una de las funciones predilectas por muchos usuarios es la bien conocida función SUMAPRODUCTO.

Esta función que no sólo opera con literales directos dentro de sus argumentos (referencias a rangos de celda), puede ser utilizada de manera eficiente cuando se devuelven las matrices desde otras funciones del aplicativo.

Para comprender el funcionamiento de esta función, sólo se debe saber que en cada argumento de SUMAPRODUCTO puede ir especificado una matriz de valores; así pues, al existir varias matrices de valores dentro de los argumentos, la función resuelve multiplicando cada elemento respectivo en cada una de las matrices para posteriormente sumar dichos productos y generar un resultado.

Es necesario tener en cuenta que las matrices pasadas dentro de SUMAPRODUCTO deben siempre ser del mismo tamaño para que no se generen incongruencias en la operación.

Por ejemplo, suponga que usted necesita saber la suma de los valores de una columna que está asociada a un campo que tiene cadenas de texto con las mismas palabras, pero algunas de esas palabras están escritas con mayúsculas y otras con minúsculas.

Donde su criterio de suma, es una palabra que debe coincidir exactamente con el valor introducido en una celda.

Pues bien, para resolver esto de manera rápida y muy profesional, usted puede utilizar a la función SUMAPRODUCTO para especificar dentro de su primer argumento una matriz de valores de unos y ceros que referencie cuando se produce la coincidencia exacta con el criterio estipulado.

Esta matriz que puede generarse fácilmente mediante la función de Excel IGUAL, arroja tras su comparación una serie de valores que especifican coincidencias exactas entre dos cadenas de texto.

Al existir coincidencia la matriz devuelve un (verdadero o 1) y al no existir se devuelve un (falso o 0).

Por tanto, si se sabe que un número multiplicado por cero da como resultado un cero, pues los valores falsos de la comparación lógica nunca se tendrán en cuenta por la función SUMAPRODUCTO a la hora de emitir el resultado.

• Si quieres ver un tutorial que detalle más este caso clica aquí.

• Para observar un ejemplo en un archivo de Excel clica aquí.