• INICIO
• FUNCIONES
• E-BOOK
• CONTACTO
• ACERCA

Convertir número a fecha en Excel

Al trabajar con datos provenientes de otros sistemas será común obtener las fechas en un formato que no es válido para Excel. En ocasiones los datos de fechas están formados por la concatenación de los dígitos del día, mes y año pero debido a su formato son reconocidos por Excel como números. En esta ocasión te mostraré una fórmula que te permitirá hacer la conversión de números a fechas en Excel.
Pero antes de construir nuestra fórmula debo recordarte que todas las fechas en Excel están formadas sin excepción por día, mes y año. Si por alguna razón intentas formar una fecha utilizando solamente el día y el mes, Excel utilizará de manera predeterminada el año actual, lo cual podría cambiar el significado de las fechas.

Cómo convertir números a fechas

Para nuestro primer ejemplo supondremos que al importar los datos a Excel obtenemos una columna con números de 8 dígitos donde los primeros dos dígitos indican el día, los siguientes dos dígitos indican el mes y finalmente los últimos cuatro dígitos son el año.

Considera que cuando el día es menor a 10 se debería mostrar un cero al principio pero al ser un valor numérico, Excel omite dicho cero a la izquierda. Tal es el caso de la celda A4 que muestra el número 5121992 que consta de solo 7 dígitos y que representa el día 05 de diciembre de 1992.
El primer paso para convertir el número a fecha es utilizar la función TEXTO que nos ayudará a dar un formato adecuado al número. Si estás familiarizado con esta función, sabrás que solo tiene dos argumentos y que el segundo de ellos es donde indicamos el formato que deseamos aplicar al valor. Ya que en nuestro ejemplo tenemos números de 8 dígitos, me interesa agruparlos como 2-2-4 utilizando el guion medio de manera que queden divididos con un formato muy parecido al de las fechas en Excel.  Ese formato lo lograremos con la siguiente fórmula:
=TEXTO(A2,"##-##-####")
Recuerda que el símbolo # representa cualquier dígito y en este caso le indicamos a la función TEXTO que aplique un formato considerando 8 posibles dígitos. Observa el resultado de aplicar esta fórmula a los datos de ejemplo:

Pareciera que con esta función hemos terminado nuestra tarea, pero si observas con detenimiento podrás darte cuenta que los valores de las celdas están alineados a la izquierda, lo que quiere decir que en este momento tenemos cadenas de texto, que se ven como fechas pero en realidad aún no son fechas válidas para Excel. Para convertir dichas cadenas de texto en fechas auténticas podemos utilizar la función FECHANUMERO de la siguiente manera:
=FECHANUMERO(TEXTO(A2,"##-##-####"))
Al modificar nuestra fórmula y aplicarla a los datos, terminaremos con una serie de números tal como se muestra en la siguiente imagen.

Este resultado no deberá sorprenderte porque las fechas en Excel son valores numéricos y lo único que debemos hacer será cambiar el formato de las celdas para asegurarnos de que tengan un formato de fecha.

El objetivo de la función FECHANUMERO es convertir una fecha en texto a su valor numérico en Excel de manera que dicho valor pueda ser tratado efectivamente como una fecha. Otro método para hacer la conversión es multiplicar el resultado de la función TEXTO por un valor numérico y que puede ser logrado con la siguiente fórmula:
=--TEXTO(D2,"##-##-####")
Observa los dos símbolos negativos al inicio de la fórmula donde cada uno de ellos indica una multiplicación por el número uno negativo (-1). La primera multiplicación es necesaria para convertir el resultado de la función TEXTO en un valor numérico y que será negativo por lo que la segunda multiplicación tendrá el objetivo de convertir dicho valor en positivo. Observa el resultado de aplicar esta fórmula a nuestros datos de ejemplo.

Cualquiera de los dos métodos que utilices, ya sea la función FECHANUMERO o el doble signo negativo, te permitirán convertir un número en fecha en Excel.

Fechas con años de dos dígitos

Los ejemplos anteriores los hemos realizado suponiendo que el año está indicado por cuatro dígitos, pero si tus datos indican los años solo por dos dígitos entonces deberás modificar el formato de las fórmulas anteriores de la siguiente manera:
=FECHANUMERO(TEXTO(G2,"##-##-##"))
Observa que dentro de la función TEXTO he indicado un formato de la forma 2-2-2 de manera que los años quedan indicados por los últimos dos dígitos. Al momento de aplicar la fórmula a un grupo de números de 6 dígitos obtendremos el siguiente resultado.

Por supuesto que también podrás utilizar la fórmula de los símbolos negativos y solamente deberás modificar el formato indicado dentro de la función TEXTO.

La regla 2029 en años de dos dígitos

Al trabajar con fechas que utilizan años de dos dígitos es necesario recordar que existe una restricción para el rango de fechas que se pueden representar con dicho formato. De manera predeterminada podrás representar fechas desde el 01 de enero de 1930 y hasta el 31 de diciembre de 2029.
Esto quiere decir que, al momento de ingresar el valor 29 como el año de una fecha, Excel lo interpretará como el año 2029. Si ingresas el año 30, se interpretará automáticamente como el año 1930. Esta no es una regla de Excel sino del sistema operativo Windows, así que si deseas modificar la interpretación de este rango de años podrás hacerlo desde el Panel de Control > Configuración regional y de idioma > Configuración adicional > Fecha.

Hasta aquí con los ejemplos para convertir números a fechas en Excel. Si en lugar de tener números tienes cadenas de texto en diferentes formatos que deseas convertir a fechas en Excel, entonces te recomiendo consultar el artículo Convertir texto a formato fecha en Excel.

• INICIO
• FUNCIONES
• E-BOOK
• CONTACTO
• ACERCA

Macro para contar celdas con datos

Cuando trabajamos con macros en Excel y dependemos de que el usuario ingrese valores en algunas celdas de nuestra hoja, es común vernos en la necesidad de contar las celdas con datos de manera que podamos hacer las validaciones pertinentes. En esta ocasión mostraré un par de  alternativas que tenemos para realizar ese tipo de cuenta desde VBA.
Para realizar el primer ejemplo de este artículo utilizaré los datos capturados en el rango A1:C10 y además necesitaré algunos botones de comando ActiveX que insertaré desde la ficha Programador y que serán los encargados de ejecutar el código VBA desde su evento Click.

La propiedad Count

Para nuestro primer ejemplo utilizaré el objeto Range y una de sus propiedades conocida como Count que nos indicará la cantidad exacta de celdas que conforman el rango indicado. Considera el siguiente código VBA:

1 2 3

Private Sub CommandButton21_Click()     MsgBox Range("A1:C10").Count End Sub

El código ha sido insertado en el evento Click del botón de comando y mostrará un cuadro de diálogo con la cantidad de celdas del rango. Así que, al pulsar el botón obtendré el siguiente resultado:

Es importante recordar que la propiedad Count funcionará correctamente mientras el rango no exceda de 2,147,483,647 de celdas. Si necesitas contar una cantidad mayor de celdas, entonces será necesario utilizar la propiedad CountLarge.
Con este código hemos logrado contar la totalidad de celdas dentro de un rango específico, pero para poder contar las celdas con datos tenemos que restar aquellas celdas que están vacías y eso será precisamente lo que haremos en la siguiente sección.

El método SpecialCells

Una manera de conocer las celdas que están vacías es utilizar el objeto Range y su método SpecialCells el cual recibe un parámetro para indicar el tipo de celdas que deseamos obtener. Un posible valor para dicho parámetro es xlCellTypeBlanks que simboliza las celdas en blanco, así que podemos utilizar el siguiente código VBA:

1 2 3

Private Sub CommandButton22_Click()     MsgBox Range("A1:C10").SpecialCells(xlCellTypeBlanks).Count End Sub

Al ejecutar este código obtendremos un resultado como el siguiente:

Ahora ya conoces el funcionamiento de la propiedad Count y del método SpecialCells así que podremos crear fácilmente una macro para contar las celdas con datos.

Macro para contar celdas con datos

Para poder contar las celdas que contienen datos dentro de un rango específico podemos restar las celdas en blanco del total de las celdas del rango. Eso lo podemos lograr con el siguiente código:

1 2 3

Private Sub CommandButton23_Click()     MsgBox Range("A1:C10").Count - Range("A1:C10").SpecialCells(xlCellTypeBlanks).Count End Sub

Observa que no he introducido ninguna instrucción nueva sino que solamente he utilizado la propiedad Count para obtener el total de celdas y posteriormente he restado las celdas vacías con el método SpecialCells. Al ejecutar este código obtenemos el siguiente resultado:

El rango A1:C10 está formado por 30 celdas, de las cuales 5 están vacías, así que solo existen 25 celdas con datos por lo que el resultado de nuestra macro es correcto.

Más sobre el método SpecialCells

En este primer ejemplo he utilizado el método SpecialCells para contar las celdas en blanco de un rango pero es posible utilizar dicho método para contar celdas con ciertos tipos de dato especiales, como las celdas que contienen fórmulas o las celdas que tienen comentarios. Lo único que debes hacer es indicar el valor adecuado como su parámetro:

• xlCellTypeAllFormatConditions: Celdas de cualquier formato
• xlCellTypeAllValidation: Celdas que contienen criterios de validación
• xlCellTypeBlanks: Celdas vacías
• xlCellTypeComments: Celdas que contienen comentarios
• xlCellTypeConstants: Celdas que contienen constantes
• xlCellTypeFormulas: Celdas que contienen fórmulas
• xlCellTypeLastCell: La última celda del rango usado
• xlCellTypeSameFormatConditions: Celdas con el mismo formato
• xlCellTypeSameValidation: Celdas con los mismos criterios de validación
• xlCellTypeVisible: Todas las celdas visibles

En nuestro ejemplo utilizamos el valor xlCellTypeBlanks para contar las celdas en blanco de un rango, pero puedes hacer la prueba con los diferentes valores disponibles para el método SpecialCells.

La función CONTARA

Otra alternativa que tenemos para contar celdas con datos desde VBA es utilizar la función CONTARA que nos devolverá la cantidad de celdas que no están vacías. Sin embargo, debes recordar que para utilizar una función de Excel desde VBA es indispensable utilizar su nombre en inglés. El nombre en inglés de la función CONTARA es COUNTA.

1 2 3

Private Sub CommandButton21_Click()     MsgBox WorksheetFunction.CountA(Range("A1:C10")) End Sub

Como argumento de la función COUNTA he indicado el mismo rango de celdas A1:C10 y como resultado obtenemos la cuenta de las celdas que contienen datos.

Este resultado concuerda con el del primer ejemplo, así que ahora ya conoces dos alternativas para contar las celdas con datos desde VBA. Finalmente dejo el vínculo de descarga del libro de trabajo utilizado en la creación de los ejemplos de este artículo.

• INICIO
• FUNCIONES
• E-BOOK
• CONTACTO
• ACERCA

Cómo calcular porcentajes en Excel

Los porcentajes son una manera de expresar los números como una fracción del número 100 y son de gran utilidad para indicar que tan grande o pequeña es una cantidad respecto de otra. Generalmente denotamos los porcentajes utilizando el símbolo de porcentaje como 75% pero dicho porcentaje es lo mismo que 75/100 y lo mismo que 0.75.

Porcentajes en Excel

Al ser un valor numérico, Excel nos permite trabajar fácilmente con los porcentajes dentro de nuestras hojas de cálculo. Por ejemplo, para ingresar el porcentaje 75%, basta con seleccionar la celda deseada e ingresar los números 7 y 5 seguidos del símbolo de porcentaje. Al pulsar la tecla Entrar dicho valor será reconocido como un porcentaje.

Observa que Excel ha aplicado automáticamente a la celda el formato Porcentaje indicándonos que el valor ha sido reconocido adecuadamente como un porcentaje. Si cambiamos el formato de la celda a General, entonces podrás ver que el valor numérico de la celda es 0.75 que es el resultado de dividir 75 entre 100.

De esta manera hemos comprobado que es totalmente válido ingresar en las celdas un porcentaje utilizando el símbolo de porcentaje (%) y Excel lo identificará correctamente como un valor numérico.

Calcular porcentajes en Excel

Para obtener el porcentaje de cierta cantidad, será suficiente con hacer la multiplicación por el porcentaje que deseamos obtener. Por ejemplo, si en la celda A2 tenemos el valor 200 y deseamos obtener el 75% de dicho valor, entonces será suficiente con hacer la multiplicación de ambos de la siguiente manera.

La celda B2 tiene el porcentaje que deseamos obtener y la celda C2 hace la multiplicación de ambos valores para obtener como resultado el valor 150 que es precisamente el 75% de 200. De hecho, no es necesario colocar el porcentaje en una celda individual ya que podríamos hacer el cálculo directamente en una fórmula. En la siguiente imagen puedes observar tres fórmulas que nos ayudan a obtener el 75% de 200.

Tal como lo mencioné al principio del artículo, 75% es lo mismo que 75/100 y lo mismo que 0.75 así que Excel nos devuelve el mismo resultado para cualquiera de estas opciones.

Sumar porcentajes en Excel

En ocasiones necesitamos aumentar una cantidad en un determinado porcentaje. Por ejemplo, si tengo un listado de precios de productos y necesito obtener el precio final después de impuestos, entonces necesitaré sumar dicho porcentaje al valor original.

Considera la fórmula de la celda C2 que entre paréntesis hace el cálculo del 15% del precio original que es la misma operación que hicimos en la sección anterior. A dicho resultado le sumamos el precio original para obtener el precio total después de haber sumado el porcentaje del impuesto.
Otra alternativa que tenemos para sumar un porcentaje es hacer una simple multiplicación. Si queremos agregar el 15% a un precio, debemos multiplicarlo por 115%.

Esta nueva fórmula obtiene el mismo resultado que el ejemplo anterior y nos muestra el precio de cada producto después de haber agregado el 15% de impuesto. Con este método, el porcentaje por el cual debemos multiplicar siempre será 100% más el porcentaje de queremos agregar.

Restar porcentajes en Excel

Si en lugar de sumar necesitamos restar un porcentaje, la operación será muy similar a la sección anterior. En la siguiente imagen puedes observar una lista de precios y además una columna que indica un porcentaje de descuento a aplicar a cada uno de ellos. La fórmula para realizar dicho cálculo se observa en la siguiente imagen:

De igual manera obtenemos el porcentaje en la operación contenida entre paréntesis pero en esta ocasión la sustraemos del precio original para obtener el precio con el descuento indicado. Al igual que en la sección anterior también podemos utilizar una multiplicación para obtener el precio con descuento.

En este caso tenemos un 15% de descuento, es decir, queremos restar dicho porcentaje al precio original por lo que haremos la multiplicación por 85% que es el resultado de restar 100% – 15%.
Los porcentajes son un tipo dato ampliamente utilizado en Excel y es importante que te familiarices con este tipo de cálculos de manera que puedas sacar el máximo provecho de la herramienta.

• INICIO
• FUNCIONES
• E-BOOK
• CONTACTO
• ACERCA

Macro para listar archivos de carpetas y subcarpetas

En un artículo pasado creamos una macro para listar los archivos de una carpeta y colocar sus  nombres en las celdas de una columna de Excel. Sin embargo es posible mejorar dicha macro para incluir todos los archivos contenidos en las subcarpetas y eso será precisamente lo que haremos en esta ocasión.

Archivos de carpeta y subcarpetas

Como ejemplo utilizaremos una estructura de archivos como la siguiente:

He colocado la carpeta principal directamente en la unidad C: de mi equipo de manera que la ruta para dicha carpeta será C:\Carpeta.
La carpeta principal contiene 5 archivos y 2 subcarpetas. Cada una de las subcarpetas contiene 5 archivos a la vez por lo que en total tenemos 15 archivos entre la carpeta principal y las  subcarpetas. Nuestro objetivo será crear una macro para listar todos los archivos tanto de la carpeta principal como de sus subcarpetas.

El objeto FileSystemObject

El objeto FilsSystemObject es de gran utilidad para tener acceso al sistema de archivos de cualquier equipo. Este objeto nos permite manipular archivos, carpetas, sus rutas de acceso e inclusive podemos obtener información del sistema de archivos. Para crear este tipo de objeto en VBA utilizamos la siguiente instrucción:
Set fs = CreateObject("Scripting.FileSystemObject")
Con esta instrucción habremos creado un objeto FileSystemObject el cual podremos acceder a  través de la variable fs. A lo largo del código de nuestra macro utilizaré algunos métodos del objeto FileSystemObject y para cada uno daré una breve explicación que te permita comprender su funcionamiento.

Macro para listar archivos de carpetas y subcarpetas

A continuación se encuentra el código de la macro que crea un listado de los archivos contenidos en la carpeta y subcarpetas.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

Sub Mostrar_Archivos(ruta)       'Sección 1: Declaración de variables y objetos     Dim fs, carpeta, archivo, subcarpeta As Object     Set fs = CreateObject("Scripting.FileSystemObject")           'Sección 2: Ajustes necesarios a ruta     If ruta = "" Then         Exit Sub     ElseIf Right(ruta, 1) <> "" Then         ruta = ruta & ""     End If           'Sección 3: Objeto Folder de la ruta indicada     On Error GoTo ErrHandler     Set carpeta = fs.GetFolder(ruta)           'Sección 4: Obtener archivos del objeto Folder     For Each archivo In carpeta.Files         ActiveCell.Value = ruta & archivo.Name         ActiveCell.Offset(1, 0).Select     Next           'Sección 5: Obtener subcarpetas del objeto Folder     For Each subcarpeta In carpeta.SubFolders         Mostrar_Archivos (subcarpeta)     Next           'Sección 6: Auto-ajustar columnas y salir     ActiveCell.EntireColumn.AutoFit     Exit Sub       ErrHandler:     ActiveCell.Value = "Ruta inexistente"   End Sub

En la sección 1 de código observarás la declaración de las variables utilizadas en la macro así como la creación del objeto FilsSystemObject y su asignación a la variable fs. La sección 2 analiza el parámetro ruta de la subrutina para saber si dicha cadena de texto está vacía y en tal caso salir del procedimiento. De lo contrario se revisa que dicha ruta termine con una diagonal invertida y de no ser así se hace una concatenación de dicho carácter.
El objetivo del código de la sección 3 es el de crear un objeto de tipo Folder que nos permita analizar el contenido de la carpeta. Esto se logra utilizando el método GetFolder del objeto FileSystemObject y el resultado es guardado en la variable carpeta. Es con el uso del método GetFolder que podemos obtener un error en VBA en caso de que la ruta indicada sea inválida por lo que he agregado la instrucción On Error GoTo para que en caso de obtener un error, la macro ejecute el código de la etiqueta ErrHandler que se encuentra al final del código y que imprimirá el mensaje “Ruta inexistente”.
En la sección 4 utilizamos la instrucción For Each para recorrer todos los archivos devueltos por el método Files y que están contenidos en la carpeta principal. Para cada archivo encontrado se concatena su ruta y su nombre y se asigna al valor de la celda activa. Posteriormente activamos la celda inferior a la celda activa de manera que la selección quede lista para el siguiente archivo.
La sección 5 obtiene las subcarpetas con el método SubFolders y para cada una de ellas se vuelve a ejecutar la macro Mostrar_Archivos lo cual ocasionará que se repita todo el proceso hasta imprimir todos los archivos contenidos en cada una de ellas.
Finalmente la sección 6 se encarga de auto ajustar el ancho de la columna de la celda activa de manera que se puedan visualizar correctamente todos los nombres de archivos. Finalmente se ejecuta la instrucción Exit Sub para salir del procedimiento.

Probando la macro

Para probar esta macro agregaré un botón ActiveX a la hoja y colocaré el siguiente código en su evento Click:

Utilizamos la función InputBox para permitir al usuario que ingrese la ruta de la carpeta de la cual se desean mostrar los archivos.
Para iniciar la prueba debo seleccionar la celda donde deseo que se listen los archivos ya que la macro comenzará a insertar los nombres a partir de la celda activa. Posteriormente hago clic en el botón y se mostrará el cuadro de diálogo solicitando la ruta de la carpeta.

Ingreso la ruta de la carpeta que contiene los archivos y al hacer clic en el botón Aceptar se ejecutará la macro y obtendremos el siguiente resultado.

Si comparas el contenido de las celdas de la columna A con los archivos mostrados en la imagen al inicio de este artículo podrás comprobar que se han listado todos los archivos de la carpeta principal y de sus subcarpetas.

• INICIO
• FUNCIONES
• E-BOOK
• CONTACTO
• ACERCA

Números grandes en Excel

Excel es una excelente herramienta para trabajar y realizar operaciones con números. Sin embargo, esta hoja de cálculo tiene algunas limitantes para trabajar con números muy grandes además de aplicar un formato especial a las celdas donde ingresamos este tipo de números.
Cuando hablo de números grandes me refiero a números enteros que contienen 12 dígitos o más. Estos números son tratados de manera especial en Excel ya que de manera predeterminada se les aplicará un formato de notación científica que no necesariamente deseamos tener. Por otro lado, Excel tiene un límite de 15 dígitos significativos para cualquier tipo de número por lo que se dificultará trabajar con cantidades de gran tamaño.

Excel convierte números a notación científica

Siempre que ingresamos un número de 12 o más dígitos en una hoja de Excel, se le aplicará un formato de notación científica. Para comprobarlo ingresaré el número de doce dígitos “123456789123” en la celda A1 y obtendré el siguiente resultado:

El valor de la celda es realmente el número ingresado y lo podemos comprobar al observar que la barra de fórmulas contiene dicho número así que esto se trata solamente de un formato de la celda. Este comportamiento se debe a que, de manera predeterminada, las celdas de una hoja tienen un formato “General” y por lo tanto Excel aplicará el formato que mejor crea conveniente para el dato recién ingresado.
Para prevenir este comportamiento te recomiendo aplicar el formato “Número” a las celdas antes de ingresar el número y con eso evitarás que Excel aplique el formato de notación científica. Por ejemplo, en la siguiente imagen puedes observar que he aplicado el formato de celda “Número” sin posiciones decimales y como resultado la celda mostrará el número de 12 dígitos tal como fue ingresado inicialmente.

Excel cambia los últimos dígitos a ceros

Cuando intentamos ingresar números muy largos en una celda, específicamente números con más de 15 dígitos, Excel reemplazará con un cero todos los dígitos a partir de la posición dieciséis. Para comprobar este comportamiento, ingresaré el número “12345123451234512345” en la celda A1 y dejaré el formato de celda “General”.

El número que he ingresado es la secuencia “12345” repetida cuatro veces para un total de 20 dígitos y como era de esperarse, Excel ha aplicado el formato de notación científica tal como lo describí en la sección anterior pero ahora existe una diferencia de mayor preocupación porque el valor mostrado en la barra de fórmulas no es el mismo que el ingresado en la celda. Observa que la barra de fórmulas muestra un número donde los últimos 5 dígitos han sido cambiados por ceros. Sin importar si cambiamos el formato de la celda a “Número”, Excel seguirá mostrando los últimos dígitos como ceros.

Este comportamiento se debe a que Excel utiliza la especificación IEEE 754 que indica que solamente se almacenarán los 15 dígitos significativos de cualquier número y eso se cumple inclusive para números con decimales. Por ejemplo, en la siguiente imagen puedes observar que Excel tiene el mismo comportamiento con el número de la celda A1, que es la misma secuencia de números del ejemplo anterior pero con un punto decimal después del décimo dígito. Aunque he aplicado un formato de “Número” con 10 posiciones decimales, los últimos5  dígitos se muestran como ceros:

En este ejemplo, la parte entera tiene 10 dígitos significativos, lo cual deja espacio solamente para 5 dígitos significativos después del punto decimal. Podrás agregar tantos números adicionales como lo desees pero siempre se mostrarán como máximo los 15 dígitos significativos de cualquier número. Por ejemplo, si agrego tres números más en la parte entera (777), Excel disminuirá la cantidad de dígitos mostrados en la parte decimal para cumplir con el máximo de 15 dígitos.

¿Existe alguna manera de sobrepasar el límite de 15 dígitos significativos en Excel? La respuesta corta es NO. La respuesta larga es que podemos implementar algunas alternativas las cuales analizaremos a continuación.

Ejemplo: Número de tarjeta de crédito en Excel

Existen usuarios de Excel que intentan almacenar los números de tarjetas de crédito en una celda pero pronto descubren los problemas de los que hemos hablado anteriormente. Como sabemos, las tarjetas de crédito están identificadas por 16 dígitos por lo que exceden por un solo dígito el máximo permitido por Excel.
Suponiendo que se ha colocado el formato de la celda como “Número” y que en la celda A1 se han capturado los 16 dígitos de la tarjeta de crédito identificada como 1234-5678-1234-5678, obtendremos el siguiente resultado:

Como era de esperarse, el último dígito es convertido en cero y no hay manera de recuperarlo. La alternativa que tenemos es la de cambiar el formato de la celda a “Texto” y posteriormente hacer la captura. Esto no funciona al revés, es decir, es obligatorio colocar primero el formato de la celda como “Texto” y posteriormente ingresar el número o de lo contrario se perderá información.

Después de haber ingresado el número, Excel mostrará la advertencia de que lo hemos ingresado como texto pero debemos ignorar dicho mensaje ya que, si hiciéramos la conversión a número, volveríamos a perder el último dígito. Otra manera de ingresar el número como texto es anteponer un apóstrofo (‘) al momento de ingresar el dato de la siguiente manera:

En este caso no importa si la celda tiene formato “General” ya que el apóstrofo obligará a Excel a considerar el número como texto. Observa cómo la barra de fórmulas muestra el apóstrofo al inicio del texto, pero la celda A1 no muestra dicho carácter.
Así que, una manera de introducir aquellos números que tengan más de 15 dígitos es hacerlo como cadenas de texto pero habremos perdido los beneficios de las operaciones aritméticas ya que no podrás sumarlos, restarlos, multiplicarlos ni dividirlos ya que serán simplemente cadenas de texto.

Números grandes con VBA

Al convertir los números largos en texto habremos perdido la posibilidad de hacer operaciones aritméticas con ellos. Una alternativa es crear nuestras propias funciones UDF que tomen dichas cadenas de texto, las conviertan en número, hagan las operaciones dentro de VBA y finalmente devuelvan el resultado de dicha operación como una cadena de texto para insertarlo en una celda. Esto es posible porque dicho lenguaje de programación no tiene la limitante de los 15 dígitos significativos.
Esta solución implica programación en VBA, por lo que no entraremos en explicaciones y detalles en este momento sino que será necesario un artículo especial para abordar el tema de la creación de ese tipo de funciones que nos ayuden a realizar operaciones aritméticas con números ingresados como texto.
Aunque Excel es la hoja de cálculo más utilizada del mundo, sigue siendo un programa de software que tiene sus limitaciones, pero es importante que todo usuario avanzado de Excel conozca dichos inconvenientes y que sea capaz de aplicar las posibles soluciones.

• INICIO
• FUNCIONES
• E-BOOK
• CONTACTO
• ACERCA

Rangos dinámicos en Excel

Un rango de Excel es un conjunto de celdas adyacentes al cual podemos hacer referencia en nuestras fórmulas. Sin embargo, cuando nuestros datos aumentan o disminuyen, es necesario editar las fórmulas para introducir el nuevo rango. La solución a este inconveniente son los rangos dinámicos ya que hacen que nuestras fórmulas se adapten automáticamente a la cantidad de datos en la hoja.

¿Qué es un rango dinámico en Excel?

Cuando hablamos de rangos dinámicos en Excel nos referimos a una técnica especial que nos permitirá hacer referencia a un conjunto de celdas que se ajustará automáticamente al insertar o borrar datos por lo que siempre tendremos el cálculo adecuado sin necesidad de editar nuestras fórmulas.
Esta técnica implica el uso de dos funciones de Excel: DESREF y CONTARA. La función DESREF nos permite crear una referencia a un rango de celdas indicando la cantidad de filas y columnas a partir de una celda específica. La función CONTARA cuenta el número de celdas no vacías de un rango. Para entender mejor estas funciones haremos algunos ejemplos.

La función CONTARA

La función CONTARA nos devuelve la cantidad de celdas no vacías contenidas en un rango. Por ejemplo, en la siguiente imagen puedes observar que he introducido la función CONTARA con el rango A1:C5 como su argumento.

El rango A1:C5 está formado por 15 celdas, sin embargo la función CONTARA nos ayuda a saber que de todas esas celdas solamente 12 contienen un valor. Esta funcionalidad nos será de gran ayuda al momento de crear rangos dinámicos en Excel ya que utilizaremos esta función para contar las celdas que tienen valores dentro de una fila o de una columna. Por ejemplo, la siguiente fórmula nos ayuda a saber la cantidad de filas con datos dentro de la columna A:
=CONTARA($A:$A)
Observa el resultado de aplicar esta fórmula sobre los mismos datos del ejemplo anterior:

De esta manera podemos saber que nuestro rango tiene 4 filas con datos. Por otro lado, si queremos conocer las columnas de nuestro rango podemos utilizar la siguiente fórmula:
=CONTARA($1:$1)
Esta fórmula contará las celdas no vacías dentro de la fila 1 de nuestra hoja y al utilizarla con los datos de ejemplo tendremos el siguiente resultado.

En seguida combinaremos la función CONTARA con la función DESREF para crear rangos dinámicos en Excel.

Rangos dinámicos con DESREF

La función DESREF nos permite crear una referencia con tan solo indicar la celda donde comenzará el rango y posteriormente el número de filas y columnas que conforman dicho rango. Para nuestros datos de ejemplo, la celda inicial del rango se encuentra en la celda A1 por lo que comenzamos nuestra fórmula de la siguiente manera:
=DESREF($A$1,
El segundo y tercer argumento de la función DESREF nos sirven para indicar algún movimiento a partir de la celda inicial pero no son necesarios en la creación de rangos dinámicos así que los podemos indicar siempre como cero.
=DESREF($A$1, 0, 0,
Los argumentos importantes en la creación de rangos dinámicos son el cuarto y el quinto de la función DESREF ya que nos permiten indicar la cantidad de filas y columnas que deseamos incluir a partir de la celda inicial. El número de filas lo podemos obtener con la función CONTARA de la siguiente manera:
=DESREF($A$1, 0, 0, CONTARA($A:$A),
Y para obtener el número de columnas volvemos a utilizar la función CONTARA de la siguiente manera:
=DESREF($A$1, 0, 0, CONTARA($A:$A), CONTARA($1:$1))
Si introducimos esta fórmula por sí sola en una celda obtendrás un error #¡VALOR! ya que nuestra fórmula no devuelve un valor sino una referencia. Pero si colocamos la fórmula anterior dentro de la función SUMA le estaremos pidiendo a Excel que sume todos los valores del rango devuelto por  la función DESREF.
=SUMA(DESREF($A$1, 0, 0, CONTARA($A:$A), CONTARA($1:$1)))
Al momento de ingresar esta fórmula obtendremos la suma de los valores en todo el rango:

Pero lo mejor de esta fórmula es que sumará automáticamente los datos adicionales que introduzcamos. Por ejemplo, en la fila 5 colocaré los valores 2, 4 y 6 y la suma se actualizará automáticamente:

Podrás introducir más datos hacia abajo y la suma siempre se actualizará automáticamente. De igual manera podrás insertar nuevas columnas de datos y de igual manera serán consideradas en el cálculo sin tener que modificar la fórmula.

Rango dinámico nombrado

Una manera de mejorar la administración de los rangos dinámicos en Excel es asignando un nombre a la referencia devuelta por la función DESREF. Para crear un nombre debemos ir a la ficha Fórmulas y pulsar el botón Asignar nombre  que se encuentra dentro del grupo Nombres definidos.

Como resultado se mostrará un cuadro de diálogo que nos permitirá crear un nuevo Nombre y como referencia colocaremos la misma función DESREF creada en nuestro ejemplo anterior.

Al pulsar el botón Aceptar se creará el nombre MiRangoDinamico que hará referencia al mismo rango dinámico del ejemplo anterior. La ventaja de utilizar este método será que ya no tendremos que ingresar la función DESREF cada vez que queramos utilizar nuestro rango dinámico sino que será suficiente con introducir el nombre recién creado.

Para futuras modificaciones del rango dinámico recién nombrado podrás utilizar el Administrador de nombres de Excel que te permitirá modificar o eliminar dicho nombre.

Otro ejemplo de rangos dinámicos

El rango dinámico creado en el ejemplo anterior considera la adición de nuevas filas y de nuevas columnas pero hay ocasiones en las que será suficiente con vigilar solamente la adición de nuevas filas. En ese caso nuestra fórmula se simplifica ya que solo tendremos que utilizar la función CONTARA una sola vez.
En el siguiente ejemplo tenemos una lista de personas con sus respectivas edades y nos interesa tener una fórmula que obtenga el promedio de dichas edades conforme se agregan nuevos datos.

Ya que las edades que deseamos considerar en el cálculo se encuentran en la columna B, podemos utilizar la función DESREF de la siguiente manera:
=DESREF($B$1, 0, 0, CONTARA($B:$B))
Observa que en este caso he omitido el quinto argumento de la función DESREF porque no me interesa considerar nuevas columnas sino solamente nuevas filas y eso es precisamente lo que hace el cuarto argumento donde he colocado la función CONTARA. Ahora podemos crear un nuevo nombre utilizando la fórmula anterior.

Ya con el nombre creado podremos utilizarlo en la fórmula de la celda E1 para obtener el promedio de las edades de la siguiente manera:

Podemos aumentar la lista de edades y la fórmula calculará automáticamente el promedio considerando los nuevos datos ingresados.

Rangos dinámicos con encabezados

Hasta ahora nuestros ejemplos han sido con rangos de celdas que no tienen encabezados, pero es muy común que los datos en Excel tengan títulos en la primera fila por lo que tendremos que modificar un poco la manera de crear nuestro rango dinámico. En la siguiente imagen puedes ver los mismos datos del ejemplo anterior pero en esta ocasión la fila 1 tiene encabezados para cada columna.

La fórmula que utilizaremos para definir nuestro rango dinámico tendrá algunas modificaciones y será la siguiente:
=DESREF($B$2, 0, 0, CONTARA($B:$B)-1)
En primer lugar nuestro rango dinámico comenzará en la celda B2 y además haremos una resta del valor 1 al cuarto argumento de la función DESREF. Esto se debe a que la función CONTARA devolverá el valor 9 como la cantidad de celdas con datos en la columna B, y eso incluirá a la celda B1, pero necesitamos descartar el encabezado de la columna y por eso hacemos la resta. Si definimos el nombre Edades_2 utilizando esta fórmula, entonces obtendré el resultado adecuado al calcular el promedio.

Agregar línea de promedio a gráfico de Excel

Al comparar un conjunto de valores dentro de un gráfico de Excel, será de gran ayuda desplegar una línea que muestre el promedio de los datos. Desafortunadamente no existe un comando que con un solo clic agregue una línea de promedio al gráfico así que tendremos que dar algunos pasos adicionales para lograr tal efecto.
Para nuestro ejemplo utilizaremos una tabla de datos con la cantidad de llamadas realizadas a un centro de soporte telefónico durante todo un año. El gráfico nos permite ver el comportamiento de dichas llamadas mes a mes para cada uno de los países.

Una pregunta que podría surgir al analizar la información de este tipo de gráfico sería ¿Cuál de los dos países hizo más llamadas en promedio durante todo el año? Tal vez podrías adivinar la respuesta con solo mirar el gráfico, pero en más de una ocasión los datos serán tan similares que lo mejor será agregar una línea de promedio al gráfico de Excel lo cual podremos logar en tan solo tres pasos.

Paso 1: Datos para líneas de promedio

Para nuestro ejemplo agregaremos dos líneas de promedio, una línea para cada país en el gráfico. Dichas líneas de promedio deben tener su origen en una columna de datos de nuestra hoja así que comenzaré por insertar la siguiente fórmula en la celda D2:
=PROMEDIO($B$2:$B$13)
Esta fórmula calculará el promedio de todo el rango de datos para México el cual se encuentra en la columna B. Posteriormente copiaré la fórmula hacia abajo y terminaré con una tabla de datos como la siguiente:

En seguida insertaré una nueva fórmula en la celda E2 la cual estará encargada de obtener el valor promedio de Colombia. La fórmula que utilizaré será la siguiente:
=PROMEDIO($C$2:$C$13)
Después de ingresar esta fórmula debemos copiarla hacia abajo para tener todas las filas con el promedio para Colombia.

Paso 2: Agregar línea de promedio al gráfico

Ahora que nuestros datos están listos debemos agregar las nuevas columnas al rango de datos del gráfico. Una manera de hacerlo es haciendo clic derecho sobre el gráfico y eligiendo la opción Seleccionar datos.

Esto abrirá el cuadro de diálogo Seleccionar origen de datos y solamente tendremos que indicar el nuevo rango de datos, que para nuestro ejemplo se encontraba inicialmente en A1:C13 y ahora lo extenderé hasta la celda E13 de la siguiente manera:

Al hacer clic en el botón Aceptar veremos reflejadas dos líneas horizontales en nuestro gráfico las cuales representarán el promedio de cada una de las series de datos.

Paso 3: Cambiar el tipo de gráfico a las líneas

En la imagen anterior puedes ver que las líneas de promedio del gráfico tienen marcadores para cada uno de los meses pero lo que queremos hacer es dejar una línea recta sin marcadores. Para lograr ese cambio debes hacer clic derecho sobre una de las líneas horizontales y elegir la opción Cambiar tipo de gráfico de series.

Una vez abierto el cuadro de diálogo deberás elegir la opción Línea del panel izquierdo y posteriormente elegir la primera opción de la sección Línea del panel derecho la cual representa al gráfico de Líneas.

Esto hará que la línea de promedio de nuestro gráfico quede sin marcadores. Si repites esta misma acción para la otra línea tendrás un gráfico como el siguiente:

Con estos pasos habremos agregado nuestras líneas de promedio al gráfico de Excel.

Líneas de promedio en gráficos de barra

En este ejemplo inicié con un gráfico de líneas con marcadores, pero es muy común tener gráficos de barras a los que queremos agregar las líneas de promedio. En ese caso debemos seguir los mismos pasos descritos anteriormente, pero debemos poner especial atención al paso 3 ya que al agregar las series de datos del promedio tendremos un gráfico con muchas barras:

En este caso es forzoso hacer el cambio de tipo de gráfico para las columnas que representan el promedio de manera que al transformarlas en gráficos de líneas obtengamos el siguiente resultado: