Buscar valores duplicados

Una de las situaciones más comunes que se nos presentan cuando ingresamos valores, es necesitar saber qué números se repiten en un determinado conjunto de celdas, ya sea para eliminarlos (a eso lo veremos en un próximo post), o bien para saber cuáles son y determina la MODA; es decir, cuál o cuales valores son los que tienen mayor peso (ocurrencia de apariciones).

Para esto, Excel nos provee de funciones como MODA.UNO y MODA.VARIOS. Sus nombres son muy descriptivos, ya que la primera función nos muestra el número que se repite más veces, y la segunda función nos mostrará una serie de números que son los que más se repiten.

Antes de comenzar a utilizar estas funciones, es necesario hacer algunas aclaraciones:

1 - Si nuestro conjunto de valores contiene más de un número que se repita la misma cantidad de veces que el otro, la función MODA.UNO nos devolverá siempre el primero que encuentre. Por esto, no podremos fiarnos de que sea el devuelto el valor que tenga predominancia sobre los otros. Por lo que es recomendable utilizar en este caso la función MODA.VARIOS.

2 - Si en nuestro conjunto de valores no hay repetición de ninguno de ellos, tanto MODA.UNO como MODA.VARIOS nos devolverán el error: #N/A.


En la siguiente imagen tenemos una serie de valores, y usaremos la función MODA.VARIOS para obtener los 2, los 3 y los 5 números que más se repiten:

Ahora, buscaremos los dos números que más se repiten, como en la siguiente imagen:

¿Cómo debemos hacerlo?, primero debemos seleccionar las 2 celdas en donde vamos a dejar nuestros dos valores que más se repitan, luego, en la barra de fórmula, escribimos "=MODA.VARIOS(" y como argumento seleccionamos nuestra matriz de datos, y cerramos el paréntesis, quedando nuestra fórmula de la siguiente manera "=MODA.VARIOS(A2:A13)".

Atención, acá viene la parte interesante: no daremos ENTER luego de culminar de escribir nuestra fórmula, debemos presionar las teclas CTRL + SHIFT + ENTER para ingresarla.
Ustedes se preguntarán ¿por qué debo ingresar la fórmula de esa manera?, y esto es así porque como hemos seleccionado 2 celdas (una matriz) en donde dejaremos nuestros dos valores más repetidos, debemos ingresar la fórmula como una fórmula de matriz, y es precisamente con CTRL + SHIFT + ENTER como se le "dice" a Excel que será una fórmula de matriz.
Nuestra hoja quedará como en la siguiente imagen:

Fíjense que la fórmula quedó de la siguiente manera: "{=MODA.VARIOS(A2:A13)}", de esta manera Excel almacena las fórmulas de matriz.

Si se han fijado, en la serie de valores, tanto el número 1, como el 2 y el 3 se repiten tres veces cada uno, por lo tanto, para buscar los tres valores más repetidos deberemos seleccionar 3 celdas en donde dejar nuestros valores, y no lo haremos. Sí buscaremos los 5 valores más repetidos, como en la siguiente imagen:

¿Por qué aparecieron en el 4° y 5° lugar los valores "#N/A"?, porque solamente tenemos 3 valores que se repiten, por lo tanto Excel no puede encontrar 5 números repetidos, y una vez mostrados los tres que se repiten, muestra el error indicando que no hay más números repetidos.

Espero que les sirva.

Buscar datos en una matriz

Hola mis amigos!

Como verán, me estoy involucrando más en el blog, y empiezo a subir más cosas que espero les sirvan.

En este post les voy a mostrar cómo buscar un determinado dato en una matriz de celdas.

Si nos alejamos de las premisas que deben cumplir las grandes bases de datos relacionales, podemos considerar a Excel como una "base de datos". Un libro sería la base de datos propiamente dicha, y sus hojas podrían ser las "tablas" de esa base; inclusive podríamos interactuar con datos de varios libros o bases de datos, y sus respectivas tablas.

Invocando a las reglas de normalización, los datos no deben estar duplicados en diferentes tablas, porque esto lleva a problemas de mantenimiento, ya que si un dato cambia en una tabla, hay que cambiarlo en el resto, lo cual es muy trabajoso. Y por si fuera poco, se crearía una gran propensión a la inconsistencia de los datos.

En el ejemplo que voy a desarrollar, tengo un libro en cuya primera hoja se almacenan datos de clientes de un supuesto negocio, que consisten en:
Número del Cliente (único para cada cliente).
Nombre del Cliente (puede estar duplicado).
Saldo del Cliente (el saldo de dinero que le queda a favor o en contra a cada cliente, y éste es el dato que vamos a buscar).
Acá les adjunto una captura de la primera hoja:

La segunda hoja del libro contiene los siguientes datos:
Número de Cliente (relaciona al cliente de la primera hoja).
Saldo del Cliente (dinero que tiene a favor o en contra el cliente, y éste será el dato buscado).
Acá está la captura de la segunda hoja:

Ahora vamos a ver cómo desde la primera hoja buscamos el saldo de cada cliente, el cual se encuentra en la segunda hoja.

La función que utilizaremos será BUSCARV o CONSULTAV. Ustedes se preguntarán el por qué de los dos nombres, y esto es así ya que a partir de Excel 2010 decidieron cambiar el nombre de la función BUSCARV por un nombre más descriptivo como CONSULTAV, pero si tienen instalado el Service Pack 1 de Office, la verán como BUSCARV (su viejo nombre), no se preocupen por esto.

Apreciemos cómo se utiliza la función en las siguientes capturas;

En la celda D2 ingresamos la función, la cual tiene cuatro parámetros; el valor que vamos a buscar (en este caso el número del cliente), la matriz en donde vamos a buscar (que se encuentra en la segunda hoja, el indicador de columnas (que es en realidad la columna que contiene el dato deseado), y por último un valor booleano (verdadero o falso) para indicar si el valor buscado deber respetar una coincidencia estricta o por el contrario, se puede buscar por un valor parecido.
En nuestro caso, vamos a buscar por el número de cliente, que se encuentra en la celda B2, luego nos desplazamos a la segunda hoja y seleccionamos toda la matriz, tal como lo muestra la siguiente imagen:

Noten que la función ya se va armando, ya que tiene el dato a buscar (el número de cliente, y la matriz). Hasta ahora va quedando de la siguiente forma: =BUSCARV(B2;Saldos!B1:C7
Ahora sólo resta indicar qué columna de la matriz debe devolver, que en nuestro caso es la número 2 (saldo), y siempre recomiendo usar "FALSO" como último argumento para que las búsquedas sean exactas.
Entonces, la función final quedaría así:
=BUSCARV(B2;Saldos!B1:C7;2;FALSO)
Una vez presionado ENTER, veremos en nuestra pantalla lo siguiente;

El saldo se corresponde perfectamente con el número de cliente entre ambas hojas, por lo que ahora deberíamos arrastrar hacia abajo, ¿no es cierto?, bueno lo hagamos y veamos qué sucede;

Algo anduvo mal, ¿estamos de acuerdo?.
Bueno, pero no fue culpa de Excel, sino exclusivamente nuestra, porque debimos decirle a Excel que trabajara con referencias absolutas a las celdas de la matriz, para que la referencia a la misma no variara cuando arrastramos la fórmula hacia abajo.

La función debería haber sido escrita así: =BUSCARV(B2;Saldos!$B$1:$C$7;2;FALSO)

El signo de moneda que le agregamos a las celdas B1 y C7 hacen que Excel "ancle" ese conjunto de celdas y no se corran cuando arrastramos hacia cualquier dirección.
Si no tienen claro este tema de referencias relativas y absolutas, los remito a otro de mis posts:
Variables Estáticas en Excel

Una vez indicado que vamos a utilizar una referencia absoluta o estática, veamos cómo queda:

Ahora quedó bien.

Los invito a practicar esto pero que el dato a buscar esté en otro libro.

La idea es comenzar a imponer las "buenas prácticas" en el manejo de la información, ya se trate de un estudiante, un contador, el propietario de un negocio, etc. para que puedan organizar mejor sus datos y accedan a ellos de una manera ágil, segura y ordenada, evitando el "copiar y pegar" libros enteros o parte de ellos en otros.

Espero les haya servido.

Rellenar Celdas Numéricas con Caracteres en Excel - 2 de 2

Hola Amigos!

En el post anterior hemos visto cómo obtener la cantidad de caracteres de una celda, y cómo hacer que se repita un determinado caracter.

Ahora vamos a ver el resto para poder hacer que una celda se rellene con "0" (ceros) a la izquierda.

A la celda "E2" le agregué las siguientes funciones: "=CONCATENAR(REPETIR("0";10);B2)".
Como su nombre lo indica, la función CONCATENAR(), es decir, UNE en una celda varios valores. En este caso le decimos a Excel que una la repetición de 10 ceros y el valor de la celda B2, y luego copiamos el contenido de la celda hacia abajo.

¿Excel hizo lo que le decíamos?, definitivamente sí.
¿Es lo que buscábamos?, está claro que no, porque si se fijan, en la celda E2 hay 11 caracteres, en la celda E3 hay 12, en la celda E4 hay 13, en la celda E5 hay 14, y así podríamos seguir hasta ocupar los 255 caracteres por celda.

¿Y por qué sucedió esto?, porque no utilizamos la función LARGO() para determinar cuántos caracteres había en la celda B2 y en las subsiguientes, y en función de eso, agregar los ceros, 

¿Cómo lo corregimos?, así:

Al agregar la función LARGO() a la fórmula de E2 de esta manera: "=CONCATENAR(REPETIR("0";10-largo(B2));B2)", le dijimos a Excel que evaluara cuántos caracteres contenía la celda B2 (en este caso 1 caracter), y se los restara a los 10 que yo había fijado arbitrariamente. Así, en vez de 10 veces, la función REPETIR() ahora lo hace 9 veces.

Si se fijan en las celdas subsiguientes a E2, verán que cada una de ellas guarda el mismo número de caracteres (10), pero no el mismo número de "0" (ceros), y esto es lo que habíamos buscado.

Una aclaración para los puristas de Excel:
Ustedes argumentarán, y con toda razón, que tratándose de valores numéricos bastaría con acceder al menú contextual y en la opción "Formato de Celda" --> Número, determinar cuántos ceros se quieren implementar, incluidos formatos más complejos.
Pero esto es una explicación más completa que eso, ya que esta fórmula que abarca las tres funciones, podría servir sobre todo para los datos que son de texto, y en vez de repetir el caracter "0" se podría poner un espacio vacío entre comillas " ", invertir el orden (poner REPETIR() al final, y el valor de la celda al  comienzo, y estaría justificado a la derecha.
Espero que entiendan que elegí un formato numérico solamente por propósitos didácticos.

Rellenar Celdas Numéricas con Caracteres en Excel - 1 de 2

Hola de nuevo mis amigos!

Ahora les voy a enseñar cómo rellenar una celda numérica con ceros a la izquierda (en realidad puede ser cualquier caracter).

A varios de ustedes seguramente les ha pasado de necesitar dar un largo específico a una columna numérica para exportar a un archivo delimitado por comas (*.csv), o de texto (*.txt), o simplemente por pura estética visual, y se han encontrado con la limitación de no saber cómo hacerlo.

Como yo quiero enseñar a pescar y no entregar el pescado listo, vamos a ir paso por paso con la construcción de una sencilla fórmula para lograr este cometido.

Comencemos:

En este libro de trabajo he agregado 5 valores numéricos aleatorios, incrementando los mismos en una cifra por cada columna.
Fíjense que en la celda B2 tengo el valor "5", y en la celda D2 tengo la función "=LARGO(B2)", la cual he copiado hacia abajo hasta el último valor.
¿Qué hace esta función?, como su nombre lo dice, me devuelve la cantidad de caracteres de la celda indicada.
¿Y para qué nos sirve?, a eso ya lo veremos un poco más adelante.

Ahora veamos en acción la función REPETIR:

Fíjense que en la celda D2 ingresé la función "=REPETIR("0";10)".
¿Para qué hice esto?, para que en este caso el caracter "0" (cero) se repita 10 veces. las 10 veces es una arbitrariedad de mi parte, bien podría ser que se repita 2 veces o 200.

¿Con esto ya podemos hacer que se rellene con "0" (ceros) a la izquierda?, no, todavía no, porque ya sabemos qué largo en caracteres tiene una celda, y sabemos cómo hacer que se repita un determinado caracter, pero no sabemos todavía cómo unir los ceros a la izquierda con los datos.

A esto lo vamos a hacer en el próximo post.

Variables Estáticas en Excel

Hola mis amigos!

Si bien no había publicado nada desde hace muchísimo tiempo, ahora he decidido que me voy a abocar un poco más a mi blog, y en este caso, voy a tratar sobre las variables estáticas y semi-estáticas en Excel.

En la pantalla tenemos en la celda A2 un valor de un porcentaje, el cual equivaldría al 21%; mientras que en la celda B2 guardamos un importe al que se le aplicará el porcentaje y ese valor se dejará en la celda D2 tal como lo explicita la fórmula ingresada en esa celda( =C2*A2).

Ahora bien, en la columna "B" tenemos una serie de importes a los que debemos calcular el porcentaje, por lo tanto nos sentimos tentados de arrastrar hacia abajo la fórmula introducida en D2, no sería mala idea, ¿no es cierto?.

Pero ocurre lo siguiente:

Excel nos calcula bien el porcentaje de la celda D2, pero en el resto de las celdas nos aparecen ceros ("0").
Algo no anda bien, ¿estamos de acuerdo?.

Ahora, ¿por qué pasa esto?.
Por la forma que tiene Excel de interpretar la fórmula ingresada en la celda D2, Cuando le indicamos que es igual a C2 multiplicado por A2, Excel entiende lo siguiente:
"En esta celda (D2), hay que almacenar el valor de la celda que está inmediatamente a mi lado por la izquierda (C2) multiplicado por el valor de la celda que está desplazada 2 lugares hacia la izquierda (A2)".

Entonces, ¿qué fue lo que ocurrió cuando arrastramos la fórmula hacia abajo?, simple, que Excel para la celda D3, y las subsiguientes de la misma columna, "razonó" igual que para D2:
"En esta celda (D3), hay que almacenar el valor de la celda que está inmediatamente a mi lado por la izquierda (C3) multiplicado por el valor de la celda que está desplazada 2 lugares hacia la izquierda (A3)".
¿Qué valor tiene C3?, 243; ¿y A3?, A3 no tiene valor, por lo que podríamos considerarlo igual a cero (0). Por lo tanto, en D3 se almacena 243 multiplicado por 0 (cero). Excel lo hizo de manera impecable, pero no es lo que queríamos.

¿Cómo lo resolvemos?, por suerte es en extremo fácil:
Debemos decirle a Excel que no importa cuántos valores tengamos en la columna "B", a éstos siempre los multiplique por el valor de A2, y los almacene en la columna "D", en la fila que corresponda.
Lo hacemos así:

Cuando agregamos el signo $ (pesos) delante de la letra de la columna, en este caso "A", y antes de la fila, en este caso "2", le estamos diciendo que queremos "anclar" esa celda.
De esta forma Excel interpreta la fórmula para cada fila de la columna "D", de la siguiente manera:
"En esta celda (DX), hay que almacenar el valor de la celda que está inmediatamente a mi lado por la izquierda (CX) multiplicado exclusivamente por el valor de la celda A2".

Lo que hicimos fue establecer una referencia absoluta en vez de una referencia relativa, que es como Excel interpreta las fórmulas por defecto.

Ahora nuestra hoja de trabajo quedaría de la siguiente manera:

Espero que les haya sido de utilidad, y recuerden que esta forma de explicar las referencias absolutas y relativas la vieron acá por primera vez. :D

También pueden hacer referencias semi-absolutas, por ejemplo: $A2 (en este caso le decimos que "ancle" la columna "A", pero que varíe la fila); o también A$2 (en cuyo caso de decimos que la fila debe quedar "anclada" pero puede variar la columna. 

Ya seguiré posteando más temas de Excel.

Modificar código auto generado por NetBeans

Hola mis amigos:

Si han llegado hasta mi blog, es porque se les presentó el problema de necesitar, o desear; modificar, o eliminar el código auto generado por NetBeans.

Les tengo una buena noticia, hay una solución para eso, simple y efectiva al 100% sin necesidad de tener que modificar los archivos XML, los archivos FORM, o hacer malabarismos para obtener resultados que no son los satisfactorios.

Como todos sabemos, los IDE's son de una gran ayuda para los programadores, y en el caso de Java, Eclipse y NetBeans deben ser de los mejores.

Yo programo en NetBeans porque me siento más cómodo con el diseñador de pantallas que es nativo de NetBeans, y no como Eclipse que utiliza el WindowBuilder en la mayoría de los casos, u otros que son de pago. El WindowBuilder tiene una flexibilidad fantástica, pero... muchas veces uno toca algo de código, y todo nuestro formulario se descompagina, obligándonos a tener que revisar el código generado hasta encontrar el error y corregirlo. Por el contrario, el editor gráfico de NetBeans es mucho más robusto, pero nos genera código que no se puede modificar fácilmente cuando el componente es un JPanel por ejemplo.

Muchas soluciones que se ven en Internet recomiendan borrar los archivos .FORM de la clase gráfica, pero cuando NetBeans nos dice que el proyecto ha cambiado y aceptamos los cambios, el editor gráfico no reconoce a la clase como gráfica, y desaparece el botón de edición gráfica, dejándonos con una clase corregida, pero a la que no podemos ver. A mi criterio, una pésima solución.

Solución:
1 - Para evitar este problema, abran su proyecto en NetBeans, y editen la clase en la que quieran realizar modificaciones.
2- Busquen el archivo Java en el directorio que contiene los paquetes de archivos. Ejemplo: C:\Users\gustavo\Documents\NetBeansProjects\Cubiertas\src\cubiertas\ConexionInformix.java
3 - Editen el archivo con NotePad++ o TextPad (yo uso éste último).
4 - Graben el archivo en el editor con que lo modificaron.
5 - Vuelvan a NetBeans y disfruten de ver sus cambios

Espero les haya servido.

Sobre mí

Mi nombre es Gustavo Echenique, vivo en Villa Dolores, provincia de Córdoba, Argentina.

Soy Administrador de Sistemas a tiempo completo.

La intención de mi blog es publicar información de referencia sobre Java e Informix. Ese tipo de información que uno busca por Internet y no se encuentra fácilmente, o directamente no se encuentra.

También me gusta pensar en miles de cosas, y me gusta la divulgación científica, por lo que postearé artículos de mi autor preferido: Isaac Asimov.

También encontrarán posts sobre Forex (Foreign Exchange), que es el intercambio de divisas extranjeras, porque es una actividad que me moviliza mucho.

En definitiva, el blog se podría llamar "Cambalache", por lo de "La Biblia junto al calefón".

Espero les guste y les sea de utilidad.