Mostrando entradas con la etiqueta OpenOffice.org Calc. Mostrar todas las entradas
Mostrando entradas con la etiqueta OpenOffice.org Calc. Mostrar todas las entradas

Validación con Listas desplegables: información dinámica, en otras hojas y más con Excel o similar

Cuando se requiere que ingresar información en una planilla de cálculo los datos sean seleccionados de una lista desplegable se debe de realizar una validación del tipo lista, esta actúa tanto como ayuda para el ingreso datos como para validar la calidad de los mismos.

En la web hay muchas preguntas sobre como realizar estas listas con valores dinámicos dependiendo del contenido de otra celda (ej. si se están ingresando países y ciudades, al mostrar la lista de ciudades muestre solo las del país antes ingresado), como hacer cuando la información a validar no se encuentra en la propia hoja o libro, evitar valores repetidos, y otras más; muchas de estas no están respondidas.

Este artículo pretende ser una guía de como realizar estas listas intentando abarcar todas las posibilidades.

Está basado en Microsoft Excel, también se mencionará como hacerlo en OpenOffice.org Calc donde la operativa es más permisible. No se incluye información sobre Google Docs ya que este tipo de validación no está disponible.

El contenido es el siguiente:

1. Listas de validación básicas en la misma hoja
2. Listas de validación con origen de datos en otra hoja del mismo libro.
3. Listas de validación con origen de datos en otra hoja de otro libro.

Como se está publicando a medida en que se desarrolla cada capítulo, proximamente estarán disponible la continuación que incluye:

4. Quitar valores repetidos.
5. No incluir espacios en blanco.
6. Listas de validación con información variable según valor de otra celda.
7. Listas de validación con información variable según valor de otra columna.

Si hay algún otra parte que consideras que se deba de incluir, se agradecerá hacerlo llegar mediante comentarios o correo electrónico.

Categorias: , , , , 1 comentarios

Desglosar dinero en billetes y monedas de diferentes denominaciones con Excel o similar

Cuando se tiene, por ejemplo, pagar nómina de contado es necesario conocer cuantos billetes y monedas de diferentes denominaciones se requieren para solicitarlos en el banco de manera de poder pagar con el monto exacto a cada uno.

El proceso es el siguiente:

- En una hoja de cálculo se deberá de poner en una columna el importe que se quiere desglosar y tantas columnas como diferentes denominaciones de la moneda corriente existan, desde el billete de mayor valor hasta la moneda de menor valor. Como título de estas columnas se pondrá el propio valor y debe de ser en formato numérico ya que se va a utilizar para realizar cálculos.

- El propio cálculo se separará en dos condiciones: uno para el billete de mayor denominación y otro para el resto.

- Para el de mayor denominación simplemente el importe se divide por el valor del billete, y se trunca el resultado de manera de tener la cantidad de billetes necesarios. A nivel de fórmulas, queda de la siguiente manera:


Esta fórmula se encuentra en C2, en B2 el importe y en C1 el valor del billete. Se debe de copiar para todos los importes a distribuir.

Para el resto de denominaciones la formula cambia ya que antes de hacer la operación arriba mostrada se debe de restar al importe original lo ya distribuido.
La formula para el segundo billete de mayor dominación es la siguiente:


Donde:

B2 es el importe a procesar, nótese que tiene referencia mixta, se fija la columna importe y se deja libre la fila.
En D1 se encuentra el valor del billete, también tiene referencia mixta pero esta vez queda libre la columna para poder cambiarse de nominación al copiar la celda y siempre fija la fila 1.
El rango $C$1:C$1 contiene los valores de las nominaciones anteriores al que se está procesando, para este caso solo la columna C. El inicio del rango siempre será fijo, y en para el final se irá variando la columna de manera de que al copiar se extienda el rango a todas las nominaciones anteriores. Para la siguiente será $C$1:D$1.
El rango $C2:C2 contiene las cantidades ya distribuidas.
Con SUMAPRODUCTO se irá acumulando el producto de ambos rango celda a celda: C1*C2 + D1*D2, de esta manera se obtiene lo ya distribuido.
Hay una función REDONDEAR que parece estar de más, pero es para evitar problemas de precisión de Excel (cuando el resultado esperado es 30 puede que realmente retorne 29,9999999), se redondea a 5 decimales de manera de no interferir con la operativa.

Esta función se debe de copiar para el resto de las nominaciones y todos los importes a distribuir.

Al final se suman todas las cantidades de cada denominación de manera de saber el total requerido.

Desglosar dinero en billetes y monedas de diferentes denominaciones con Microsoft Excel

Las formulas son las utilizadas en la muestra del proceso.

En C2 introducir esta fórmula para el cálculo de la cantidad de billetes con denominación mas alta:


En D2 introducir esta fórmula para el cálculo de la cantidad de billetes con la segunda denominación más alta:


Copiarla para el resto de billetes y monedas.

Aquí se encuentra disponible un archivo de ejemplo utilizando Euros que contiene dos hojas: en la primera un detalle donde se desglosan los importes y en la segunda un resumen con la información total.

Desglosar dinero en billetes y monedas de diferentes denominaciones con OpenOffice.org Calc

Las formulas a utilizar son las siguientes:

En C2 introducir esta fórmula para el cálculo de la cantidad de billetes con denominación mas alta:


En D2 introducir esta fórmula para el cálculo de la cantidad de billetes con la segunda denominación más alta


Copiarla para el resto de billetes y monedas.

Al igual que en Excel, aquí se encuentra disponible un archivo de ejemplo en OpenOffice.org Calc utilizando euros que contiene dos hojas: en la primera un detalle donde se desglosan los importes y en la segunda un resumen con la información total.

Categorias: , , , 7 comentarios

Escribir o convertir números a letras

Una necesidad frecuente es escribir un número con letras (convertir número en letras), las planillas de cálculo al igual que los manejadores de base de datos no disponen de una herramienta para dicha tarea, pero es factible realizarla.

En la web hay disponibles diversos complementos y macros en Visual Basic para el caso de Microsoft Excel, y ejemplos con funciones, también para Microsoft Excel que utilizando columnas intermedias. El código de estas funciones se puede convertir al lenguaje que se requiera.
Independientemente del lenguaje con que se implemente en grandes rasgos el criterio para escribir un número con letras es el siguiente:

  • Tomar la parte entera del número.

  • Considerar la casuística especial para el cero.

  • Separar las cifras en miles (grupos de a 3 pociones a partir de la derecha), se tendrán tantos grupos como se deseen implementar, para números normales pueden tomarse: miles de millones, millones, miles, y unidades. La cifra de cada grupo se debe de tratar de la misma manera por lo que la misma funcionalidad utilizada en una es válida para el resto.
    • Ver casuísticas especiales como ser el cien.
    • Tomar el valor de la centena de una lista predefinida (ciento, doscientos, ...).
    • Si la decena es corresponde a 1 (10 al 19), tomarlo de una lista exclusiva de para estos (diez, once, doce ...).
    • Tomar el valor de las unidades de una lista predefinida (un, dos, tres, ...).
    • Tener consideraciones con los valores como el 1 = uno / un, 20 (veinte, veinti).

  • Luego de convertir cada cifra de miles se debe de poner su denominación: mil, millones, etc.

  • Por último si se desean incluir los decimales se deberán de poner en forma fraccionaria, o utilizar la misma función para escribirlos con letra (normalmente se utiliza la primer opción).

Aquí se pretende publicar una función que sea relativamente fácil de comprender, sin el uso de macros, ni columnas adicionales de manera que sea fácil de insertar en una hoja de cálculo.

A diferencia de las funciones disponibles la que se presenta aquí parte de una lista pre convertida de los números del 0 al 999, como se trata de solo 1000 números no ocupa espacio y tendrá un mayor rendimiento.

Función para escribir números con letras con Microsoft Excel en español:


Asumiendo que en las columnas A y B de otra hoja llamada N se encuentran los nombres de los números del 0 al 999, y en celda A2 de la misma se encuentra el número a convertir. El separador de listas es el símbolo ; (punto y coma).

La lista de los números junto el ejemlpo completo para Microsoft Excel está disponible aquí.

Función para escribir números con letras con OpenOffice.org Calc:


Asumiendo que en las columnas A y B de otra hoja llamada N se encuentran los nombres de los números del 0 al 999, y en celda A2 de la misma se encuentra el número a convertir. El separador de listas es el símbolo ; (punto y coma).

La lista de los números junto con el ejemplo completo para OpenOffice.org Calc está disponible aquí. Función para escribir números con letras con Microsoft Excel en ingles, Google Docs, Numbers:

Con respecto al anterior aquí cambia la dirección de la lista de números.


Asumiendo que en las columnas A y B de otra hoja llamada N se encuentran los nombres de los números del 0 al 999, y en celda A2 de la misma se encuentra el número a convertir. El separador de listas es el símbolo ; (punto y coma).

Como usar este ejemplo en todas las planillas de cálculo:

Para el uso de esta operación en planillas de cálculo se deberá utilizar el propio archivo que se encuentra disponible, o en el caso de desarrollarlo de cero se deben de realizar los siguientes pasos:

  • Crear o utilizar una hoja en blanco con el nombre N, y copiar los datos con los nombres del 0 al 999 (información disponibles en los archivos de ejemplo).
  • En otra hoja introducir un número de prueba en la celda A2.
  • Poner la función presentada en la celda B2, al hacerlo deberá de mostrar en frase el número introducido en A2.
  • Si se quiere mostrar el número en otra celda que no sea A2 y B2 se debe de mover (cortar y pegar) la celda A2 a la primer o única fila donde estará el número a convertir, y mover la celda B2 donde se quiere el primer resultado (otra opción es cambiar A2 por la celda correspondiente en la propia formula).

Una vez familiarizado con el uso de la misma se pueden omitir pasos, incluso dejar la lista en un archivo separado.

Función para escribir números con letras con Oracle:

Para simplificar este artículo este desarrollo se realizó por separado, para verlo acceder aquí.

Categorias: , , , , , , , , 0 comentarios

Realizar promedios sin incluir ceros

Varios usuarios han preguntado como hacer para contar o promediar valores de un rango sin incluir el 0 (cero), aquí se mostrará una forma de hacerlo.

Las funciones que cuentan, por ejemplo CONTAR de Microsoft Excel, consideran las celdas que contengan datos (no vacías), no sirve cuando se desean ignorar los ceros (o cualquier otro valor), las funciones que permiten contemplan condiciones normales, por ejemplo CONTAR.SI, cuentan según una única condición, y si consideran las celdas vacías, por lo que se requieren dos condiciones: que no sean vacías y diferentes de cero.

Básicamente la idea es contar y/o sumar en forma condicionada.

Es recomendable mirar lo publicado hace un tiempo atrás que muestra como contar o sumar filas con condiciones múltiples.

Contar registros sin tomar en cuenta los valores 0 con Microsoft Excel:


Esta opción utiliza dos condiciones: que el valor sea diferente de cero y diferente de nulo (celda vacía). Otra forma de hacerlo es mediante restas:


Se cuentan todos las celdas con contendido (no nulas) y se restan las que contienen cero.

Sumar registros sin tomar en cuenta los valores 0 con Microsoft Excel:


En este caso la suma se puede hacer directamente ya que sumar 0 o nulos no cambia el resultado (solo en caso de planillas de cálculo). Si por ejemplo se desean solo los valores positivos la formula sería así:


Promedio sin tomar en cuenta los valores 0 con Microsoft Excel:

Para realizar el promedio se juntan ambas operaciones mostradas, con sumas condicionadas:


ó con restas:


Para el caso del promedio de valores no nulos positivos (> 0 / mayor que cero):


Evitar errores al realizar Promedio con Microsoft Excel:

Cuando se intenta realizar un promedio, ya sea mediante la función PROMEDIO o las indicadas aquí, y todos los valores son nulos el resultado es #¡DIV/0! (o cero para estos casos).

El error se evita preguntando antes si la cantidad (valor del denominador) es cero, en caso positivo se muestra nulo (o cero), y de lo contrario se muestra el resultado de la operación.

Utilizando la función normal que no considera nulos pero si los ceros:


Utilizando formulas para no considerar los ceros ni los nulos:


ó


Promediando valores positivos:


Promedio sin tomar en cuenta los valores 0 (cero) con OpenOffice.org Calc:

Función promedio normal:


Función de promedio con condiciones múltiples:


Función de promedio con restas:


Función de promedio tomando en cuenta solamente los valores positivos:


Próximamente se completará este artículo con las demás planillas de cálculo y base de datos.

Categorias: , , , 0 comentarios

Referencias relativas, absolutas y mixtas en Excel o similar

Para poder trabajar sin problemas con las planillas de cálculo es necesario tener muy claro como funcionan las referencias a celdas y/o rangos, aquí se plantea un artículo que pretende explicar su funcionamiento.

Parte de la información que se presenta fue extraída de la ayuda de Microsoft Excel, si bien fue escrita para los productos de Microsoft es válida para todas las plantillas de cálculo como ser: OpenOffice.org Calc, Google Docs, Numbers, etc.

Según la gente de Microsoft: “Una referencia identifica una celda o un rango de celdas en una hoja de cálculo e indica a Microsoft Excel en qué celdas debe buscar los valores o los datos que desea utilizar en una fórmula. En las referencias se puede utilizar datos de distintas partes de una hoja de cálculo en una fórmula, o bien utilizar el valor de una celda en varias fórmulas. También puede hacerse referencia a las celdas de otras hojas en el mismo libro y a otros libros. Las referencias a celdas de otros libros se denominan vínculos.".

Existen tres tipos de referencias: relativas, absolutas y mixtas.

Referencias relativas Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la celda que contiene la fórmula y de la celda a la que hace referencia. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia. Si se copia la fórmula en filas o columnas, la referencia se ajusta automáticamente. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas. Por ejemplo, si copia una referencia relativa de la celda B2 a la celda B3, se ajusta automáticamente de =A1 a =A2."
La figura muestra el copiado de la celda B2 a B3, como se puede observar se ha desplazado una fila, y de haberse copiado a la celda C3, el contenido de la misma estaría apuntando a B2.

Referencias absolutas Una referencia de celda absoluta en una fórmula, como $A$1, siempre hace referencia a una celda en una ubicación específica. Si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas y es necesario cambiarlas a referencias absolutas. Por ejemplo, si copia una referencia absoluta de la celda B2 a la celda B3, permanece invariable en ambas celdas =$A$1."
La figura muestra el copiado de la celda B2 a B3, como se puede observar que ahora no se ha desplazado ninguna fila, y de haberse copiado a la celda C3, el contenido de la misma estaría también apuntando a B1.

Referencias mixtas Una referencia mixta tiene una columna absoluta y una fila relativa, o una fila absoluta y una columna relativa. Una referencia de columna absoluta adopta la forma $A1, $B1, etc. Una referencia de fila absoluta adopta la forma A$1, B$1, etc. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia relativa y la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia relativa se ajusta automáticamente y la referencia absoluta no se ajusta. Por ejemplo, si se copia una referencia mixta de la celda A2 a B3, se ajusta de =A$1 a =B$1"
La figura muestra el copiado de la celda B2 a C3, como se puede observar no se ha desplazado la fila, pero si la columna. Si el contenido de B2 hubiese sido =$A1 al copiarla a C3 tendría =$A2.

Si se edita la formula, por ejemplo presionado F2 sobre la celda, y se sitúa el cursor sobre la referencia, al presionar F4 se van alternando las combinaciones mencionadas. Esto es para evitar digitar los símbolos de $.

A continuación se mostrarán un par de ejercicios o ejemplos:

Ejemplo 1:


En una hoja de cálculos se realizaron 5 cuadros, en el cuadro número 1 se ingresaron números del 1 al 9 ordenados.
En el centro del cuadro 2, se ingresó una referencia relativa al centro del cuadro 1: =C5.
En el centro del cuadro 3, se ingresó una referencia absoluta al centro del cuadro 1: =$C$5.
En el centro del cuadro 4, se ingresó una referencia mixta al centro del cuadro 1 fijando la fila: =C$5.
En el centro del cuadro 5, se ingresó una referencia mixta al centro del cuadro 1 fijando la columna: =$C5.
En los cuadros 2 al 5 se copia la celda del centro a las 8 celdas vecinas de los bordes.

Los resultados muestran que:

  • Al utilizar referencias relativas en el cuadro 2 se logró reproducir exactamente la muestra.
  • Al utilizar referencias absolutas en el cuadro 3 se llenó con el valor del centro de la muestra.
  • Al utilizar referencias mixtas fijando la fila en el cuadro 4 solo varía las celdas de las columnas.
  • Al utilizar referencias mixtas fijando la columna en el cuadro 5 se ve lo contrario al cuadro 4.

Ejemplo 2:


En la siguiente figura las formulas introducidas en las celdas con colores se visualizan a la izquierda de cada una.

Aquí se ingresó información desde la celda C2 hasta G6 alterando con celdas sin datos.

En las celdas E14 a E17 se definieron referencias a la celda E4 que contiene la palabra Centro con los tres tipos de referencia relativas, absolutas, y mixtas (una para fila y otra para columna).

Por ultimo se copió el rango E14 hacia las celdas C12, G12, C16 y G16 (defasaje de 2 celdas en diagonal para todas las direcciones).

Luego de realizar el copiado se puede ver los diferentes resultados según las referencias introducidas.

Errores frecuentes con referencias:

Uno de los errores mas frecuentes cuando se trabaja con diversas funciones se debe a no utilizar referencias absolutas.

Por ejemplo cuando se utiliza la famosa función BUSCARV o VLOOKUP, si a la matriz a buscar se la define con referencias relativas en lugar de absolutas, y cuando la función final se copia a las filas de abajo, la matriz también se desplazará hacia abajo provocando que apunte a un área donde no se encuentre el dato a buscar.

Uso erróneo de la función:

=BUSCARV(A1;F1:H100;2;FALSO)

NOTA: No siempre está mal utilizar referencias relativas en esta función, hay veces -generalmente muy pocas- en que se necesita este desplazamiento.

Uso correcto de la función:

=BUSCARV($A1;$F$1:$H$100;2;FALSO)

Normalmente se fija la columna done se encuentra el dato a buscar ($A1), que es utilizado cuando se quiera copiar la formula a la derecha para buscar una segunda columna de datos.

La matriz a buscar generalmente ($F$1:$H$100) es fija.

Categorias: , , , , , 0 comentarios

Separar los componentes de nombres de personas

Extraer los componentes de nombres de personas no es una tarea sencilla, aquí se mostrará una manera muy básica de separar los nombres propios en sus dos apellidos y nombres.

Para este caso se parte de la premisa que los componentes del nombre están separados por espacios, e ingresados siempre en el mismo orden: apellido uno, apellido dos, y nombres.

La idea del proceso es extraer el primer apellido a partir de la primer posición del nombre completo hasta antes de la primer ocurrencia del espacio.

Para extraer el segundo apellido se parte del texto que se encuentra a partir del primer espacio hasta la ubicación del siguiente espacio.

Y por ultimo para extraer los nombres se parte desde la posición siguiente al segundo espacio (largo del primer apellido + largo del segundo apellido + 2 espacios + 1) hasta el final de la cadena de texto.

Este método tiene grandes limitantes tales como:

  • No extrae apellidos o nombres compuestos (ej: DEL CAMPO).
  • El orden de los componentes es fijo para todos los casos.
  • Genera anomalías cuando no existen algunos de los apellidos o nombres.

Separar nombres con Microsoft Excel en español:


Partiendo de que el nombre completo se encuentra en la columna A, se dejará el primer apellido en la columna B, el segundo apellido en la columna C, y los nombres en la columna D.

Primer Apellido (B2)_


Segundo Apellido (C2):


Nombres (D2):


Separar nombres con Microsoft Excel en inglés, Google Docs, OpenOffice.org Calc, Numbres:

Se parten de las mismas premisas que en ejemplo anterior.

Primer Apellido (B2):


Segundo Apellido (C2):


Nombres (D2):


Separar nombres con Oracle:

Se realizará una consulta con una lógica similar a la de las hojas de cálculo, la diferencia más importante radica en que no se dispone del valor de la celda anterior para conocer su largo.


Esta sentencia es para prueba, cuando se disponga de una tabla cambiar el contenido del from por la tabla donde se encuentre el nombre a procesar, y modificar los nombres de campos.

Separar Nombres con Microsoft Access:

Se realizará una consulta similar a la planteada en Oracle.


Tomando los datos de una tabla llamada tabla_nombres que tiene un campo nombre_completo con contiene la información a dividir.

Otras alternativas:

Vale la pena comentar que otra forma muy básica de hacer este proceso es utilizar la herramienta de Microsoft Excel de separar texto en columnas. Aparte de ser muy básica esta operación se debe de hacer por procesos y no definida en formulas como la que aquí se presenta.

Próximamente se publicarán maneras más completas de realizar estas operaciones. Soluciones con mayor inteligencia son realizadas con algoritmos más complejos basados en árboles de decisión y redes neuronales.

Aprovecho el tema para ofrecer mis servicios profesionales para la normalización de nombres propios en bases de datos a gran escala, utilizando algoritmos que interpretan los componentes sin importar el orden, formato y cantidad de componentes del mismo. Lo complementan operaciones adicionales como la identificación del sexo, e identificación nombre repetidos aun cuando estén definidos en formas diferentes (nombres completos vs iniciales, con faltas de ortografía, por sonido, etc.).

Por consultas realizarlas por este correo

Categorias: , , , , , , , , , , , 0 comentarios

Teclas para moverse en Microsoft Exel o similar

Una manera de optimizar considerablemente el tiempo de trabajo con planillas de cálculo es utilizando teclas para moverse y desplazarse dentro de la planilla.

Cuando el uso de estas teclas se realice en forma natural o inconsciente, la optimización del tiempo de trabajo será considerable.

Las teclas que más importantes para la reducción del tiempo de trabajo con Microsoft Excel en español e inglés y Google Docs son:

    CTRL + teclas de dirección (flechas): Ir hasta el extremo de la región de datos actual. El cursor queda en la celda con información anterior a la primer celda vacía encontrada según la dirección seleccionada.

    CTRL + INICIO: Ir hasta el comienzo de una hoja de cálculo. Se para en la celda A1.

    CTRL+ FIN: Ir a la última celda de la hoja de cálculo, que es la celda ubicada en la intersección de la columna situada más a la derecha y la fila ubicada más abajo (en la esquina inferior derecha) o la celda opuesta a la celda inicial, que es normalmente la celda A1.

Si a la combinación de estas teclas se le agrega la tecla MAYUSCULAS (o SHIFT) el cursor se desplazará y seleccionará el área cubierta desde la celda original a la final del desplazamiento. Esta es la mejor manera de seleccionar celdas ya sea para copiar, pegar o dar formato.

Desplazarse solamente con las telas de dirección o avance de página dentro de listas de decenas de miles de filas es una manera muy ineficiente de hacerlo. Haciendo un simple cálculo: teniendo buena resolución de pantalla entran> un poco mas de 40 filas por página, por lo que llegar a la fila 20 mil mediante avance de páginas requiere presionar dicha tecla unas 500 veces.

Otra tecla importante, pero solo de Microsoft Excel y OpenOffice.org Calc es:

    F5: Mostrar el cuadro de diálogo Ir a. Al ingresar una referencia de dirección en este cuadro el cursor se desplaza hacia la misma. Por ejemplo al ingresar F4500 va a dicha celda. En OpenOffice.org Calc la ventana de diálogo es un poco mas completa.

Ejemplo:

Para seleccionar todas las celdas que tienen datos de una columna,se debe de parar al comienzo de la misma, y presionar CTRL + MAYUSC + ABAJO, en caso de no existir ninguna celda vacía el cursor llegará hasta la fila final.

Si existen celdas vacías intermedias, se deberá presionar esta combinación de teclas tantas veces como huecos se encuentren hasta llegar al final. Como alternativa cuando existen muchas celdas vacías es ir hasta el fin de la planilla (CTRL + MAYSC + FIN), de esta manera el cursor habrá quedado en la fila final pero también en la columna final, luego retroceder columnas hacia la izquierda hasta dejar seleccionada la columna que se desea.

Otra alternativa es "bajar" por una columna que no tenga datos (CTRL + MAYUSC + ABAJO) que llegará hasta la fila 64 mil y pico, posicionarse el la columna deseada y regresar para arriba con CTRL + MAYUSC + ARRIBA.

Este ejemplo aplica también sin el uso de la tecla MAYUSC lo cual solo servirá para desplazarse.

Se recomienda tomar un archivo que tenga datos ingresados y jugar para familiarizarse con el uso de las mismas. "Jugar" con estas teclas sin miedo ya que no se rompe nada. Primero probar solo el desplazamiento y luego la selección de áreas.

Otras teclas de desplazamiento:

    Teclas de dirección: Moverse una celda hacia arriba, hacia abajo, hacia la izquierda o hacia la derecha.

    INICIO: Ir hasta el comienzo de una fila. (*)

    AV PÁG: Desplazarse una pantalla hacia abajo.

    RE PÁG: Desplazarse una pantalla hacia arriba.

    ALT + AV PÁG: Desplazarse una pantalla hacia la derecha. (*)

    ALT + RE PÁG: Desplazarse una pantalla hacia la izquierda. (*)

    CTRL + AV PÁG: Ir a la siguiente hoja del libro.

    CTRL + RE PÁG: Ir a la hoja anterior del libro.

    CTRL+ F6 o CTRL + TAB: Ir al siguiente libro o a la siguiente ventana. (*) (**)

    CTRL + MAYÚS + F6 ó CTRL + MAYÚS + TAB: Ir al libro o a la ventana anterior. (*) (**)

    F6: Mover al siguiente panel de un libro que se ha dividido. (*) (**)

    MAYÚS + F6: Mover al anterior panel de un libro que se ha dividido. (*) (**)

    CTRL + RETROCESO: Desplazarse para ver la celda activa. (*) (**)

    MAYÚS + F5: Mostrar el cuadro de diálogo Buscar. (*) (**)

    MAYÚS + F4: Repetir la última acción de Buscar (igual a Buscar siguiente). (*)

    TAB: Desplazarse entre celdas desbloqueadas en una hoja de cálculo protegida.

Notas:

  • Texto tomado de la ayuda de Microsoft Excel al que se le incluyeron algunos comentarios.
  • (*) No disponibles en Google Docs.
  • (**) No disponibles en OpenOffice.org Calc.
Categorias: , , , , , 2 comentarios

Identificar registros repetidos Excel o similar

Identificar registros repetidos en una tabla de datos es una operación que se realiza con alta frecuencia, aquí se mostrará una manera muy fácil de hacerlo.

La idea es la siguiente:

  1. Identificar la clave de registro único, definir que columnas o combinación de estas la componen.
  2. Ordenar los datos por la clave de registro único.
  3. Comparar los componentes de la clave de una fila con la fila siguiente, si son iguales se trata de un registro duplicado, si lo son se marcan como tales.

Se mostrarán ejemplos partiendo de los siguientes datos:


Ejemplo 1: Eliminar registros repetidos dejando los valores con fechas mas recientes.


Los pasos son los siguientes:

  • Ordenar la lista, en este caso por Código y luego por Fecha. De esta manera quedan todos los registros con el mismo código juntos, y el que tiene la fecha mayor en el último lugar.
  • Se crea una nueva columna "EsRepetido" en la cual simplemente se comparará el contendido de la columna "Código" para la fila actual con la siguiente columna, obteniendo dos valores posibles: Verdadero o Falso.

    =A2 = A3

  • A la lista de datos se le aplica un Autofiltro, y se seleccionan de la nueva columna "EsRepetido" todas aquellas filas que tienen Verdadero. Estas se deben de eliminar.

Si el criterio para determinar valores repetidos fuese las columnas "Nombre" y "Apellido" la consulta se haría por ambas columnas de la siguiente manera:

Formula en Microsoft Excel en Español:

=Y(C2 = C3; B2 = B3)

Formula en Google Docs, Microsoft Excel en Inglés, OpenOffice.org Calc, Numbers:

=AND(C2 = C3; B2 = B3)

Para este caso los datos se debieron de ordenar por "Apellido" y luego por "Nombre" (o viceversa), y luego por la fecha.

La fecha se incluye en el orden para tener un criterio al momento de eliminar el registro. En caso de necesitar eliminar los registros con fechas mas antiguas el orden de la fecha será descendente.

Resultados diferentes se obtienen si al momento de comparar la fila actual se lo hace respecto a la anterior en lugar de la siguiente (eliminaría registros diferentes).

Ejemplo 2: Sumar la deuda actualizada de los clientes, contar cantidad de registros únicos.

En este caso, deuda actualizada se entiende como el registro con la fecha mayor para cada cliente.

Los pasos son los siguientes:

  • Al igual que en el caso anterior la lista se debe de ordenar por código (o nombre y apellido) y luego por fecha, también se crea la columna EsRepetido utilizando los mismos criterios.
  • Para contar los registros únicos se creará una columna Cantidad en la que en cada fila se pondrá 1 si el valor de la columna EsRepetido de su correspondiente fila es Falso, de lo contrario se pone 0. Al sumar todos los datos de esta nueva columna se obtiene la cantidad de registros únicos.

Formula en Microsoft Excel en Español:

=SI(F2 = FALSO; 1; 0)

Formula en Google Docs, Microsoft Excel en Inglés, OpenOffice.org Calc, Numbers:

=IF(F2 = FALSE; 1; 0)

Ahora para sumar el valor de la deuda tomando en cuenta solo el registro que contiene la fecha mayor se debe de verificar la columna EsRepetido, si no se trata de un repetido se toma el valor de la deuda, de lo contrario cero.


Formula en Microsoft Excel en Español:

=SI(F2 = FALSO; E2; 0)

Formula en Google Docs, Microsoft Excel en Inglés, OpenOffice.org Calc, Numbers:

=IF(F2 = FALSE; E2; 0)

El registro de la fecha mayor se obtiene gracias al orden de los datos, no requiere realizar nada a nivel de formulas.

Estas formulas se pueden realizar sin tener la columna auxiliar EsRepetido:

Cuenta sin repetidos en Microsoft Excel en Español:

=SI(NO(A2 = A3); 1; 0)

Cuenta sin repetidos en Google Docs, Microsoft Excel en Inglés, OpenOffice.org Calc, Numbers:

=IF(NOT(A2 = A3); 1; 0)

Suma de deuda actualizada en Microsoft Excel en Español:

=SI(NO(A2 = A3); E2; 0)

Suma de deuda actualizada en Google Docs, Microsoft Excel en Inglés, OpenOffice.org Calc, Numbers:

=IF(NOT(A2 = A3); E2; 0)

Categorias: , , , , 3 comentarios

Como utilizar la función BUSCARV o VLOOKUP y errores más frecuentes

Una de las herramientas mas utilizadas de las planillas de cálculo es la función BUSCARV o VLOOKUP, una vez que te familiarizas con ella le “tomarás cariño” ya que permite optimizar el procesamiento de datos. Aquí se muestra como se utiliza y cuales son los errores más comunes que se dan al utilizarla.

Según la ayuda de Microsoft Excel la función Busca un valor en la primera columna de la izquierda de una tabla y luego devuelve un valor en esa misma fila desde una columna especificada. De forma predeterminada, la tabla se ordena de forma ascendente.

Su sintaxis es la siguiente:

BUSCARV(
valor_buscado;
matriz_a_buscar_en;
indicador_de_columnas;
ordenado
)

Donde:

  • Valor_buscado: es el valor buscado en la primera columna de la tabla y puede ser un valor, referencia o una cadena de texto.
  • Matriz_a_buscar_en: Es una tabla de texto, números o valores lógicos en los cuales se recuperan datos. Puede ser una referencia a un rango o un nombre de rango.
  • Indicador_de_columnas: Es el número de columna de la matriz matriz_a_buscar_en desde la cual se debe de devolverse el valor que coincida. La primera columna de valores en la tabla es la columna 1.
  • Ordenado:es un valor lógico para encontrar la coincidencia más cercana en la primera columna (ordenada de forma ascendente) = VERDADERO u omitido; para encontrar la coincidencia exacta = FALSO.

La función VLOOKUP de Microsft Excel en Inglés, Google Docs, OpenOffice.org Calc se comporta de la misma manera que BUSCARV, solo difiere en su nombre.

Los errores mas comunes que se presentan con esta función son los siguientes:

  1. Al escribir la función en la primer fila funciona bien, pero al copiarlo a las filas de abajo comienzan a salir #N/A cuando el valor buscado se encuentra en la matriz a buscar.
    Las referencias de la matriz a buscar están en forma variable. Por ejemplo cuando se escribe un rango A1:B100 en la fila 2 y se copia a la fila 3 este pasa a ser A2:B101, y así sucesivamente.
    La solución es siempre poner el rango de la matriz a buscar en forma fija de la manera $A$1:$B$100 (se escribe el símbolo $ antes de de cada elemento de la dirección, es mas fácil hacerlo con la tecla F4 cuando se está editando la formula y el cursor está sobre las direcciones).
    Otra solución es definirle un nombre al rango y utilizar este nombre en la función.
  2. El dato que recupera es incorrecto, no pertenece a el valor buscado.
    La mayoría de las veces se requiere que la función retorne los valores exactos, y esto se hace ingresando Falso (ó 0) en el cuarto parámetro. En el caso que se omita o se ingresa Verdadero y el valor no se encuentra, retorna el primer dato mayor al valor buscado que encuentre.
  3. La función retorna #N/A aun cuando el dato a buscado se encuentra en la matriz a buscar.
    La función solo busca en la primer columna, por lo que si el dato se encuentra en otra ésta no lo encontrará.
    Se deben de reordenar las columnas de manera que donde se encuentra el valor buscado quede de primera, en caso de que no se pueda se deberá de crear una nueva columna al inicio de la matriz con los mismos datos que la original (por ejemplo si no se puede mover la columna D, debo tener en la columna a una copia de D: en A2: =D2).
  4. La función retorna #N/A sin importar si el dato esté o no en la matriz a buscar.
    Se está tomando una columna que no pertenece al rango. El rango debe de contener desde la columna que contiene los datos a buscar hasta como mínimo la columna donde se encuentran los valores a devolver.
Ver como quitar los resultados #N/A que se obtienen cuando no se encuentra el valor.

Lo expuesto aquí también aplica para las funciones de búsquedas horizontales BUSCARH o HLOOKUP considerando que las búsquedas se hacen por filas en lugar de columnas.

Categorias: , , , , , , 1 comentarios

Quitar los valores #N/A en Excel o similar

Cuando se utiliza la función BUSCARV o VLOOKUP y el dato a buscar no se encuentra retorna el valor #N/A el cual no es posible utilizarlo en sumas, cuentas, ni demás operaciones que se requieran aplicarle a los datos que si fueron identificados.

Se deben de quitar estos valores para continuar con las operaciones. La primer opción que se viene a la mente es borrar las celdas que contienen #N/A, creando un nuevo posible problema ya que se pierden las fórmulas.

La manera mas limpia de quitarlos es preguntar por el resultado de la función, si es #N/A se asigna un “valor nulo”, que puede ser el propio nulo (“”), cero (0), las palabras “No se encuentra”, o el valor que se requiera. En caso de no serlo se ejecuta nuevamente la función para mostrar el resultado obtenido.

La función ESNOD (o ISNA para versiones en inglés) retorna verdadero si el valor que se le pasa es #N/A, de lo contrario falso.

La forma de evitar el valor #N/A en Microsoft Excel es:


La forma de evitar el valor #N/A en Google Docs, OpenOffice.org Calc, Microsoft Excel en inglés:


Esta solución aplica para todas las funciones que puedan retornar el valor #N/A.

Categorias: , , , , , , 7 comentarios

Calcular primer y ultimo día hábil del mes

En este artículo se mostrará como calcular el primer y/o último día hábil del mes, en principio sin contemplar los días festivos.

Para hallar el primer día hábil del mes, la idea es la siguiente:

  1. Calcular la fecha del primer día del mes.
  2. Ver el día de la semana que "cae" primer día del mes.
  3. Si se trata de un sábado hay que sumarle dos días, de ser un domingo se suma uno, y para el resto de los días es el propio primero.

Para hallar el último día hábil del mes, la idea es la siguiente:

  1. Calcular la fecha del último día del mes.
  2. Ver el día de la semana que "cae" el último día del mes.
  3. Si se trata de un sábado hay que restarle un día, de ser un domingo se restan 2, y para el resto de los días el mismo fin de mes.

Calcular primer y último día laborable en Microsoft Excel en español:

Primer día laborable del mes:


Último día laborable del mes:


Partiendo del supuesto que en A1 se encuentra la fecha perteneciente al mes en cuestión. Si se cambia por HOY() se obtiene el dato respecto al día actual.

Variante al Cálculo en Microsoft Excel en español:

En Excel hay una alternativa en la que si se pueden tener en cuenta dos días festivos.

Calcular el primer día hábil considerando los festivos se identifica el último día calendario del mes anterior y a este se le suma 1 día laborable.

Otra fórmula para calcular el primer día hábil del mes en Microsoft Excel sin considerar festivos.


Otra fórmula para calcular el primer día hábil del mes en Microsoft Excel considerando festivos.


Donde RANGO_DIAS_FESTIVOS corresponde a un rango donde se encuentra la lista de días festivos.

Calcular el primer día hábil considerando los festivos se identifica el último día calendario del mes corriente, a este se le suma 1 día calendario, y se le resta un día laborable.

Otra fórmula para calcular el ultimo día hábil del mes en Microsoft Excel sin considerar festivos.


Otra fórmula para calcular el ultimo día hábil del mes en Microsoft Excel considerando festivos.


Calcular primer y último día laborable del mes en Google Docs, OpenOffice.org Calc, Microsoft Excel en inglés:

Calcular primer día laborable del mes:


Calcular ultimo día laborable del mes:


Al no tener la función que identifica el último día del mes la formula queda un poco mas compleja.

Calcular primer y último día laborable del mes en Oracle:

Cálculo para el primer día laborable del mes:


Cálculo para el ultimo día laborable del mes:


Calcular primer y último día laborable del mes en MySQL:

Cálculo para el primer día laborable del mes:


Cálculo para el ultimo día laborable del mes:


Calcular primer y último día laborable del mes en Microsoft Access:

Cálculo para el primer día laborable del mes:


Consulta que toma datos de una supuesta tabla "datos".

Cálculo para el ultimo día laborable del mes:


Consulta que toma datos de una supuesta tabla "datos". Al no disponer de una función que calcule el ultimo día del mes calendario, se calcula el primer día del mes siguiente y se le resta uno (funciona aun para diciembre: 12 + 1 = enero).

Categorias: , , , , , , , , , , 5 comentarios

Contar cantidad de domingos entre dos fechas o cualquier otro dia de la semana

La formula para calcular la cantidad de un determinado día de la semana (lunes, martes, miércoles, etc.) entre de dos fechas es la siguiente:

Truncar(( Fecha_Final – Fecha_Inicial – DiaDeLaSemana(Fecha_Final-X) + 8) / 7)

Donde X es el número del día de la semana: 0 = Domingo, 1 = Lunes, …, 6 = Sábado.

Esta fórmula puede utilizarse para calcular la cantidad de días laborales entre dos fechas tomando en cuenta los sábados (cosa que las funciones tradicionales no contemplan).

Cálculo en Microsoft Excel en español:

Contar la cantidad de domingos:


Contar la cantidad de lunes:


En B3 se encuentra la fecha final y B2 la fecha inicial.

Cálculo en Google Docs, OpenOffice.org, Calc, Microsoft Excel en inglés:

Traduciendo la fórmula al inglés queda de la siguiente manera:

Contar la cantidad de domingos:


Contar la cantidad de lunes:


Aquí encontrarán un ejemplo al respecto realizado en Google Docs.

Cálculo en Oracle:

Calcular la cantidad de martes que hay entre dos fechas:


Cantidad de martes entre el 2 de enero de 2009 y hoy:


Cálculo en MySQL:

Calcular la cantidad de martes que hay entre dos fechas:


Aquí los cálculos cambian debido a que Weekday retorna 1 para lunes hasta 6 domingo. Para variar el día de la semana se deberá sustituir el 2 que se encuentra en "interval (-2 + 1)" por el número correspondiente con mismo criterio que antes 0: domingo, 1: lunes, etc.

Cálculo en Microsoft Access:

Cantidad de sábados entre ambas fechas.


Cantidad de sábados entre el 2 de enero de 2009 y hoy


NOTA: Tomada de una supuesta tabla "datos".

Categorias: , , , , , , , , , , , 2 comentarios

Contar valores diferentes en Excel o similar

A continuación se presentan un par de formas de contar la cantidad de datos diferentes que existan en un rango. La idea es la siguiente:

Crear una nueva columna donde cada dato diferente tendrá un peso de 1, de manera que la sumatoria nos de la cantidad de elementos diferentes.
Para hacer que cada dato diferente sume 1, para los casos en que hay repetidos tendremos que dividir a 1 entre la cantidad de ocurrencias del dato con mismo valor (matemáticamente hablando el inverso de la cantidad de ocurrencias). Esta operación también aplica cuando solo existe una única ocurrencia (1/1=1).

Cálculo en Microsoft Excel en español:

Supongamos que queremos saber la cantidad de elementos diferentes no nulos que existen en el rango A2:A10, para lograrlo tenemos que hacer lo siguiente:

1. Crear una columna por ejemplo en D, donde la celda D2 tendrá el inverso de la cantidad de ocurrencias del valor que se encuentra en A2:


Utilizamos la función CONTAR.SI en todo el rango que nos interesa y lo comparamos con el dato de A2, previamente verificamos que la celda A2 no esté vacía.
La parte que contiene '&””' al final, es para cuando la celda A2 está vacía (para que busque una celda vacía hay que ponerle ""). Esta función nunca la necesitará esta parte porque pusimos una condición al inicio que solo tome celdas con contenido, se deja por si alguien quiere quitarla par que incluya celdas vacías

2. Copiar la función ingresada en la fila 2 para el resto de las filas del rango.

3. Al final debemos sumar los datos de la columna calculada:


El dato resultante es la cantidad de valores diferentes que se encuentran en el rango que estamos buscando (para este caso A2:A10).

Segunda forma:

Hay otra forma para hacer esto sin utilizar una columna auxiliar, entendí necesario mostrar la primer solución ya que se trata de un paso previo para entender la siguiente.

Al manejar funciones matriciales podemos calcular el valor ponderado de cada elemento el la propia función de manera de realizar la sumar al final:


El valor resultante de la condición "diferentes de nulos" (“”) es verdadero, que es lo mismo que 1, para los que la cumplen y cero para los que no.

La función CONTAR.SI se comporta de la misma manera que en el caso anterior.

El rango se puede extender a tantas filas y columnas como se requiera, por ejemplo en un rango de 3 columnas:


Cálculo en Microsoft Excel en inglés, Google Docs, OpenOffice.org, Calc:

Traduciendo al inglés, las formulas de la primer forma queda de la siguiente manera:

Paso 1:


Paso 2:


La segunda forma no me ha funcionado en Google Docs, por ahora lo lamento.

Categorias: , , , , , , 2 comentarios

Validar caracteres contenidos en un texto con Excel o similares

Cuando se necesita validar el contendido de un texto, por ejemplo que solo se contenga letras (con o sin tildes), determinadas letras, número, etc; no hay una operación definida, y menos si no se quiere recurrir a macros.

Aquí se presenta una formula que valida el contenido de un texto en Microsoft Excel sin macros, retorna verdadero si todos los caracteres del texto son válidos, de lo contrario falso.

Para lograrlo se utilizan funciones matriciales, puede que sean algo complejas de entender, si no se comprenden solo es copiar y pegar.

Cálculo en Microsoft Excel en español:


Esta función validará el contenido del texto encontrado en la celda A1, los caracteres válidos son los que se encuentran en la lista incluida en la función.

Se puede cambiar la lista dependiendo de las necesidades del momento, simplemente se deben de escribir los caracteres válidos.

En el caso de que la celda a validar no tenga información la misma dará falso, si se desea admitir nulos la formula sería de la siguiente manera:


Cálculo en Microsoft Excel en inglés, Google Docs, OpenOffice.org Calc:

Traduciendo al inglés, la primer función queda de la siguiente manera:


Traduciendo al inglés, la segunda función queda de la siguiente manera:


Categorias: , , , , , , 0 comentarios

Ordenar datos por criterios externos arbitrarios

A continuación se mostrará una forma sencilla de ordenar una serie de datos por un criterio totalmente arbitrario, y que éste se pueda modificar según las necesidades del momento.

La idea del proceso es la siguiente:

  • Crear una tabla que contenga al menos una columna los datos que se deben de ordenar, y en una segunda valores numéricos en la donde se definirá el orden.
  • Cruzar la información a ordenar con la nueva tabla de manera de agregar una columna adicional con la información del orden.
  • Ordenar los datos por la nueva columna.
  • En caso que en el futuro se requiera modificar el orden, simplemente se cambia la numeración de la tabla donde está definido.
Solución para hojas de cálculo:

A modo de ejemplo se mostrará una lista de productos que cada uno de ellos tiene una categoría, se ordenarán los productos según un orden cualquiera que se le asignará a cada categoría. : El mismo está disponible en la siguiente dirección de Google Docs.
Para que se puedan ver las fórmulas ha quedado de libre edición, por lo que si ves algo extraño accede a la versión original (Archivo / Historial de Revisiones).
Para verlo en Microsoft Excel (español) se debe de exportar el documento como xls (Archivo / Exportar).

Los pasos son los siguientes:


  1. Crear la lista donde se definirá el orden a aplicar. En el ejemplo se trata de la lista que se encuentra en la hoja Categorías. Definir el orden.

  2. Crear una nueva columna en la lista de datos originales para posteriormente cruzar la información (columna Orden Externo).

  3. Cruzar la información entre las dos tablas de manera de obtener el valor del orden de la segunda hoja. Esto se realizará utilizando la función BUSCARV (VLOOKUP en ingles):

    La formula para la busqueda es la siguiente:

    Versiones en español:


    Versiones en inglés:


    Siendo C la columna donde se encuentra la clave de búsqueda. En las columnas B y D de la hoja Categorias estarían la clave a buscar y dato resultante respectivamente.

  4. Ordenar la tabla por la nueva columna calculada, adicional a este orden se le puede incluir otros. Para el caso del ejemplo el orden está definido por la columna Orden Externo y posteriormente Nombre Producto.
Solución para base de datos (SQL):

A nivel de base de datos la solución es más fácil, una vez creada la tabla (supongamos con los mismos nombres que la planilla de cálculo mostrada).
Simplemente se debe de hacer un Join entre ambas tablas y ordenar el resultado por el dato del orden.


Categorias: , , , , , , , , , , , 1 comentarios

Identificar repetidos, unir información o validar consistencia de dos o mas reportes

Muchas veces se requiere "cruzar" dos o mas listas de datos, ya sea para unir la información, identificar registros repetidos, o validar la consistencia de los datos (ver si hay diferencias en algún dato).

Se puede hacer esta operación de manera muy sencilla mediante hojas de cálculo utilizando la función BUSCARV para versiones en español, y LOOOKUP para versiones en inglés.

Según ayudas de los productos, la sintaxis es la siguiente:

BUSCARV(
Valor buscado;
Matriz de comparación;
Indicador de columnas;
Ordenado
)

Los argumentos van separados por el carácter que se tenga definido como separador de lista de la configuración del computador, los mas comunes son el ";" (punto y coma) y "," (coma).

La función busca un valor específico en la columna más a izquierda de una matriz y devuelve el valor en la misma fila de una columna especificada en la tabla.

Los argumentos son:

"Valor buscado": es el valor que se busca en la primera columna de la "Matriz de Comparación" que puede ser un valor, una referencia o una cadena de texto.

"Matriz de comparación": es el conjunto de información donde se buscan los datos.

"Indicador de columnas": es el número de columna de "Matriz de Comparación" desde la cual debe devolverse el valor coincidente.

El error más común en esta operación es que los datos se van desplazando una vez que se copian las formulas a las siguientes filas, para evitar esto se deben de fijar la matriz de comparación con los caracteres $ (delante del a letra que indica la columna y el número que indica la fila), o utilizar una matriz con un nombre de referencia que siempre es fijo. Y para el valor buscado es recomendable fijar la columna.

Cuando las calves de la información que se tiene que cruzar son compuestas por mas de una columna se deberá de definir una nueva columna concatenando una única clave (esto se puede hacer en la propia formula o con una columna auxiliar).

Para evitar errores cuando los datos no son del mismo tamaño, antes se deben de uniformar con un formato de texto único.

Las celdas que traigan como resultado #N/A indican que no existen en la segunda lista, estas se deben de cambiar por un valor 0, espacios o nulo dependiendo de sus necesidades. La forma mas elegante de hacerlo es agregarle a la propia función una validación de los datos cuando son nulos, aquí se muestra como hacerlo.

Como siempre si tienes dudas no dejes de consultar.

Categorias: , , , , , , 0 comentarios

Identificar siguiente día de la semana, siguiente lunes, siguiente martes, .. siguiente jueves, …, etc.

Hay veces que se requiere poder identificar la fecha correspondiente al, por ejemplo, siguiente jueves de la semana. Puede que nos interese compara la fecha de un pago con la del cierre de una remesa, o lo que sea.

Para el cálculo, la idea es la siguiente:

  1. Identificar el día de la semana que "estamos parados" (ya sea la fecha actual o determinada fecha).
  2. Sabiendo que una de las numeraciones para identificar los días de las semanas es 1: Domingo, 2: Lunes, 3: Martes, ..., 6:Viernes, 7: Sábado.
  3. Contar la cantidad de días que distan, por ejemplo, del jueves de la semana en curso.
  4. El valor resultante se lo sumamos a la fecha en cuestión. Hasta aquí tenemos la fecha correspondiente al jueves de la semana en curso.
  5. Como se requiere el siguiente jueves, debemos ver si ya nos pasamos de ese día para ir a la semana siguiente sumando 7 días.
En el presente ejemplo está realizado para hallar el próximo jueves, si se requiere otro día se debe de cambiar las dos ocurrencias del número 5 correspondiente al jueves por el número correspondiente al día deseado según información del punto 2.

Cálculo en Microsoft Excel en español:


Este ejemplo se calcula según una fecha que se encuentra en la celda A1. Si se desea calcular sobre el día actual se cambia la celda por la función HOY.


Cálculo en Google Docs, OpenOffice.org Calc, Microsoft Excel en inglés:


Este ejemplo se calcula según una fecha que se encuentra en la celda A1. Si se desea calcular sobre el día actual se cambia la celda por la función TODAY.


Cálculo en Oracle:


Este ejemplo se calcula según una fecha fija. Si se desea calcular sobre el día actual se cambia la celda por la función SYSDATE.


Tener en cuenta que muchos de los lenguajes, ej C/C++, de programación no tienen implementada la función de sumar y restar días, en estos casos hay que desarrollarla.

Categorias: , , , , , , , , , 0 comentarios

Comparar o Restar horas de diferentes fechas

En varias ocasiones he visto problemas al realizar operaciones con fechas y horas, más precisamente cuando las horas son de diferentes fechas.

Por ejemplo, al restar dos horas cuando las fechas son diferentes, si intentas comparar las fechas y luego las horas restando unas y otras lo puedes hacer pero lleva un poco de lógica algo compleja.

La solución es extremadamente simple: la fecha y hora se deben de sumar generando un único dato del tipo fecha hora.

Esto se debe a que la mayoría de los sistemas para el almacenar un dato que contiene una fecha o una hora utilizan un mismo tipo de dato que contiene la fecha y hora integrada, y de manera visual por formatos se diferencian uno de otro.

Facilitará operaciones tales como:

- Calcular el tiempo transcurrido entre dos horas de igual o distintas fechas.

- Comparar dos horas de igual o distintas fechas.

- Obtener la fecha y hora máxima de un rango.

- Obtener la fecha y hora mínima de un rango.

- Obtener la fecha y hora media de un rango.

Cálculo en Microsoft Excel, Google Docs, OpenOffice.org Calc, Base de Datos:

Diferencia entre fechas (tiempo transcurrido desde la Hora Inicio de la Fecha Inicio hasta la Hora Fin de la Fecha Fin).


Comparar fechas, por ejemplo saber si la Hora de Fin es mayor a la Hora de Inicio.


En programa que tanto las fechas como las horas se almacenan en cadenas de caracteres, a la fecha se le debe de concatenar la hora para poder compararlas.

Categorias: , , , , , , , , , , , , , 1 comentarios

Generar reportes por rangos variables

Una operación muy común al momento de recuperar datos es clasificar el resultado por rangos de valores dependiendo de un determinado criterio, ya sea para posteriormente agruparla o simplemente para saber en que categorías se encuentra.

Dentro de la Minería de Datos (datamining), podemos decir que estas operaciones están incluidas en las técnicas de Limpieza y Transformación de datos.

El proceso para identificar y agrupar los rangos es el siguiente:

1. Identificar el rango al que pertenece cada dato.

2. Agrupar por los criterios necesarios más la información del rango (paso opcional).

3. Realizar los cálculos de agregación de información necesarios (Suma, cuentas, promedios, máximos, mínimos, etc).

La forma "tradicional" de identificar el rango es hacer tantas condiciones como rangos existan, lo que lleva a un reporte complicado que depende de la cantidad de rangos, y ni hablar cuando hay que hacer cambios.

Como alternativa podemos realizar dicha operación utilizando una tabla auxiliar (lista) donde se definen los rangos, y luego mediante una misma consulta preguntar a que rango de dicha tabla pertenece cada valor.

Para cada rango se debe de realizar la siguiente pregunta:

valor_a_buscar "mayor o igual" rango_minimo Y valor_a_buscar "menor" rango_minimo

Cuando el resultado es verdadero se debe a que lo encontramos. Es muy importante no dejar huecos entre los rangos ni superponerlos para evitar perder información ni duplicarla respectivamente.

Solución para hojas de cálculo:

1. Se crea una nueva hoja, por ejemplo llamada RANGOS, donde tendremos tres columnas: A tendrá el rango mínimo, B el rango máximo, y C el nombre del rango.

2. Para buscar los rangos se debe de utilizar la BUSCARV (VLOOKUP en ingles), utilizando la característica del cuarto parámetro la cual habilita la búsqueda en rangos. Buscará el dato en la lista, de no encontrarlo nos dará el dato correspondiente al menor valor superior al que estamos buscando, aquí es muy importante tener ordenada la lista ya que de lo contrario no nos funcionará.

En la hoja donde tenemos nuestra información, se crea una nueva columna llamada Rango donde se ingresará la siguiente formula:

2.a. Formula en Microsoft Excel en Español:


2.b. Fórmula en Google Docs, OpenOffice.org Calc, Microsoft Excel en inglés:


3. En caso de necesitar agrupar la información, una vez identificado el rango, se crea una tabla dinámica que incluya esta nueva columna y se realiza el reporte. Las operaciones de agregación son provistas por la tabla dinámica (en caso de utilizar Microsoft Excel).

Hice un ejemplo con una lista de países agrupando según la información de su población y superficie, aquí queda disponible un documento de Google Docs

Para que se puedan ver las fórmulas ha quedado de libre edición, por lo que si ven algo extraño accede a la versión original (Archivo / Historial de Revisiones).

Solución para manejadores de base de datos:

1. Se crea la tabla para que contenga los rangos como mínimo con los campos que de la tabla de rangos.

2. Definir la información de los rangos ingresando los datos en la nueva tabla.

3. Al reporte (consulta) que se tenga simplemente se le debe de agregar la tabla de rangos y unirla (join) mediante:


O como un colaborador mencionó en sus comentarios:


Ejemplo de reporte detallado:


4. En caso de requerir las agrupaciones, se deben de agrupar los datos y utilizar las operaciones de agregación necesarias.


Como siempre si desean mayor información o aclarar algún tema no duden en comunicarse.

Categorias: , , , , , , , , , , , , 3 comentarios

Rellenar números con ceros a la izquierda

Cuando se requiere, por ejemplo, rellenar con ceros a la izquierda de un número es fácil hacerlo en sistemas que tienen una función definida, como el LPAD de Oracle, y no tan fácil en planillas de cálculo y muchos lenguajes de programación ya que esta operación no está definida. Hacerlo por suerte la cosa no es tan complicado.

El proceso para rellenar un número con ceros u otro carácter a la izquierda es el siguiente:

Calcular la cantidad de caracteres a insertar de acuerdo al largo definido y el largo que posea el dato al que se le desea dar formato.

Crear una cadena de caracteres repitiendo el carácter de relleno (en nuestro caso 0) tantas veces como el resultado del punto anterior.

Unir la cadena de caracteres creada con el dato original, en este caso los caracteres de relleno van a la izquierda. En el caso de rellenar por la derecha, solo se debe de cambiar el orden en el cual se concatenan las dos cadenas.

A nivel de fórmulas queda de la siguiente manera:

Rellenar con ceros u otro carácter a la izquierda en Microsoft Excel en español:


que es igual a:


Para estos casos se rellena hasta con 15 ceros a la izquierda del dato que se encuentra en A2.

Para rellenar por la derecha se invierten los datos a concatenar:


Rellenar con ceros u otro carácter a la izquierda en en Google Docs, OpenOffice.org, Calc, Microsoft Excel en inglés:


que es igual a:


Rellenar con ceros u otro carácter a la izquierda o derecha en Oracle:


Utilizando la operación ya implementada por Oracle, se rellena con ceros a la izquierda del número 123456. La función para rellenar a la derecha es RPAD.

Rellenar con ceros u otro caracter a la izquierda o derecha en MySQL:


Al igual que Oralcle MySQL dispone de las funciones LPAD y RPAD.

Rellenar con ceros u otro carácter a la izquierda o derecha en Microsoft Access:


Ejemplo de rellenar con hasta 15 ceros por la izquierda y la derecha a un dato_numerico perteneciente a la tabla datos.

Para el caso de Microsoft Access se propone una variación a los casos anteriores. Para rellenar por la izquierda se le concatenan muchos ceros a la izquierda, y se toman se extrae los N caracteres que se desean por la derecha de la cadena resultante. Para rellenar por la derecha se utilizan las operaciones inversas.

Categorias: , , , , , , , , 7 comentarios
Suscribirse a: Entradas (Atom)