Excel Total

Evitar resultados #¡DIV/0!

leonelquezada2007@gmail.com (Morpheus) — Tue, 27 Oct 2009 00:03:00 +0000

Me pregunta una usuaria, a quien llamaremos Claudia Acosta, cómo hacer que Excel muestre un cero en lugar del código de error #¡DIV/0!

Siempre que intentamos realizar una división por cero, algo matemáticamente imposible, Excel nos alerta de esta situación con el resultado de error #¡DIV/0!

Claro que esto no se ve muy bien que digamos. Una forma de evitar este resultado y hacer que Excel nos devuelva otro resultado más "estético", (un cero), es valiéndonos de la función SI:

=SI(B2=0, 0, A2/B2)

Es decir, que si el divisor (B2 en este caso) es igual a cero, Excel debe devolver un cero. En caso contrario, devuelve la división A2/B2.

Existe otra manera, más corta, de evitar los resultados #¡DIV/0!. Es con la fórmula:

=--SI(B2,A2/B2)

¿Cómo funciona? Debemos tomar en cuenta que para Excel, un cero equivale al valor lógico FALSO; mientras que cualquier otro valor equivale a un VERDADERO. Entonces, suponiendo que en A2 tengamos un 4 y en B2 un 2, la fórmula se convierte en:

=--SI(2, 4/2), lo que resulta en:
=--SI(VERDADERO,4/2)
=--4/2
=--2
=2

Pero, si en B2 tenemos un cero, entonces la fórmula resulta:

=--SI(0, 4/0)
=--SI(FALSO,4/0)

Cuando no establecemos el segundo argumento de SI (valor_si_falso), SI devuelve FALSO:

=--FALSO

Al aplicar un signo menos a este resultado, Excel lo convierte a su equivalente numérico, es decir, cero:

=0

Y para no cambiar de signo el resultado (en el primer caso), aplicamos un doble signo menos.

Slicers - Novedad en Excel 2010

leonelquezada2007@gmail.com (Morpheus) — Thu, 24 Sep 2009 18:15:00 +0000

En la nota Primeras imágenes de Excel 2010, comentaba sobre una nueva característica en la barra Insertar, llamada Slicers, sobre la cual no tenía idea de su funcionamiento.

El día de hoy, el blog oficial de Microsoft Excel nos revela cual es la funcionalidad de estos slicers (¿cómo les llamaríamos en español?). Esta es mi traducción a la nota en cuestión, escrita por el Excel MVP Joseph Chirilov (las imágenes están en inglés):

Los slicers son una novedad para Excel 2010, que son controles visuales que permiten al usuario rápida y fácilmente filtrar sus datos de forma interactiva. "Flotan" sobre la hoja, como las gráficas y autoformas.

Este es un ejemplo del mismo slicer en tres situaciones distintas. El usuario puede seleccionar uno, todos, o algunos de los países. Un slicer actúa como un filtro de reporte, así que se puede vincular a una tabla dinámica, una gráfica dinámica o a un cubo de datos para crear un reporte interactivo.

¿Por qué los slicers?

En esta primera versión, el equipo Excel quiso permitir al usuario:

Ver lo que está haciendo en todo momento
Interactuar fácilmente con los datos
Filtrar múltiples tablas o graficos dinámicos o consultas juntos
Crear mejores tableros de datos

Detallemos un poco más cada uno de estos objetivos:

Ver lo que se está haciendo en todo momento

En este ejemplo, quiero analizar la utilidad generada por clientes con uno o más hijos y con una escolaridad de bachillerato o superior. En Excel 2007, puedo agregar los campos de página "Numero de hijos" y "Educación" a mi tabla dinámica y seleccionar los elementos correspondientes. Sin embargo, el campo de página mostrará "múltiples elementos", lo que no es de gran ayuda.

En Excel 2010, hemos agregado slicers a la caja de herramientas de forma que el usuario pueda ver lo que está haciendo en todo momento. Ahora queda completamente claro qué datos está mostrando el reporte en cada momento.

Interactuar fácilmente con los datos

Filtrar datos en Excel 2007 era bastante tedioso. Primero, había que dar clic en la lista desplegable o en el icono Filtro, luego expandir los elementos necesarios, seleccionar / deseleccionar los elementos que queramos y finalmente dar clic en Aceptar.

En contraste, seleccionar un elemento de un slicer es fácil. Sólo requiere un clic. Para seleccionar múltiples elementos, se pueden usar combinaciones de Ctrl + clic, Shift + clic o clic + arrastrar. Esta forma de seleccionar elementos es la misma con la que el usuario está familiarizado al seleccionar celdas, elementos de lista o iconos en Windows. Los slicers lucen como controles en lugar de celdas, induciendo al usuario a utilizarlos. Utilizar slicers para crear reportes maximiza la interactividad, permitiendo al usuario y a sus colaboradores pasar menos tiempo escudriñando y más tiempo analizando.

Filtrar múltiples tablas dinámicas, gráficas dinámicas o consultas juntos

Una de las limitaciones de los filtros de los reportes es solo permiten una relación 1:1 con la tabla dinámica que están filtrando. Si el usuario necesitaba aplicar un filtro a mútiples tablas dinámicas, tenía que recrear el filtro en cada tabla. Ahora, el usuario puede vincular un slicer a varias tablas, gráficos dinámicos y/o consultas de datos. Todo lo que se haga en el slicer se aplicará convenientemente a todo lo que este tenga conectado. Más sobre esto en la siguiente nota.

Crear mejores tableros de datos (dashboards)

Los slicers pueden ser personalizados de varias maneras, y pueden crearse ahora tableros que antes no eran posibles. En una futura nota, profundizaré más en las formas en que se puede mejorar el aspecto visual de los slicers. Por ahora, aquí está un ejemplo del tipo de reportes que se pueden lograr con los slicers:

Y una cosa más: los slicers son compatibles con Excel Services 2010 y Excel Web App, así que podemos utilizarlos y compartirlos en la web.

Excel 2010 - Logo

leonelquezada2007@gmail.com (Morpheus) — Wed, 09 Sep 2009 17:02:00 +0000

Microsoft acaba de dar a conocer los nuevos iconos de los programas de la suite Office 2010. Este es el de Excel 2010:

Otros iconos, el de Word:

Power Point:

Outlook:

y Access:

Fuente.

Insertar 'bullets' rápidamente

leonelquezada2007@gmail.com (Morpheus) — Fri, 04 Sep 2009 21:25:00 +0000

Hay ocasiones en las que deseamos preceder las entradas de una lista con viñetas (o bullets, en inglés), como en este ejemplo:

Normalmente insertaríamos estas viñetas yendo a Insertar - Símbolo...

Localizamos el símbolo correspondiente y clic en Insertar.

Una forma más rápida, es (para esta viñeta en específico) escribiendo la combinación de teclas Alt + 7, pero del teclado numérico, ya que con el 7 del teclado alfabético no funciona. Con esto aparecerá la viñeta en la celda faltando solamente escribir el valor de la celda.

Esta es la tabla completa de símbolos que se pueden utilizar con Alt + [núm. de teclado numérico]:

Observemos que tanto Alt + 0 como Alt + 47 no devuelven ningún símbolo, en tanto Alt + 32 devuelve un espacio en blanco. A partir de Alt + 48 comienzan los números y letras "normales".

Aparentemente, esta no es una forma muy práctica que digamos para obtener este formato, ya que requiere que cada entrada de celda esté precedida, manualmente, de Alt + 7; además de que esto convertirá en texto nuestros valores numéricos. Sin embargo, tenemos la gran ventaja de que también podemos utilizar esta combinación de teclas a la hora de crear un formato de celda personalizado. En efecto, si vamos al cuadro de diálogo Formato de celdas (Formato - Celdas... ó Ctrl + 1), categoría Personalizada, podemos teclear Alt + 7 (o cualquier otro número de la tabla anterior), en el cuadro de texto Tipo, seguido del resto del formato deseado. Por ejemplo: [Alt + 7]#,##0 (hay que soltar la tecla Alt antes de escribir "#,##0"). Si lo hacemos correctamente, Excel mostrará el resultado previo en la ventana Muestra:

Por último, clic en Aceptar y copiamos el formato al resto de las celdas de interés. De esta manera, ya no es necesario escribir Alt + [número] al ingresar cada valor, sino solamente el valor (o la fórmula) dejando a Excel el resto del trabajo.

Trabajar con usuarios avanzados

leonelquezada2007@gmail.com (Morpheus) — Tue, 11 Aug 2009 22:20:00 +0000

Como dice Jorge Camoes en su blog, "perder estúpidamente el tiempo en el trabajo me molesta realmente".

Cuando se trabaja en una compañía en la que un alto porcentaje de gente usa Excel como su herramienta principal, inevitablemente surgen historias de terror de gente gastando días en tareas que debieron durar minutos. Por ejemplo:

Miles de sumas, calculadas una a una, cuando una tabla dinámica pudo dar los mismos resultados en segundos.
Dos personas comparando dos listas, cuando pueden utilizar una simple función de búsqueda para hacerlo.
Miles de copias de exactamente la misma gráfica, pero con diferentes mercados, territorios de venta o medidas (una sola gráfica con algo de interactividad básica serviría igual).
Cientos de celdas coloreadas ¡manualmente!, simulando un espantoso confetti electrónico.

Todo esto es, además, propenso a una alta cantidad de errores. Es frustrante ver a la gente perdiendo tiempo de esta forma, cuando fácilmente podrían salir temprano de su trabajo y jugar con sus hijos (o las amigas de sus hijos...). Y es tan fácil... todo lo que hay que hacer es decirse a uno mismo: "esto es estúpido; debe haber alguna forma más rápida de hacerlo". Y generalmente, la hay.

Si el lector es gerente, os compartiré un secreto administrativo: No te imaginas lo ineficiente que puede llegar a ser un usuario principiante de Excel, y lo más probable es que tengas a varios eternos principiantes en tu equipo. No es cuestión de capacitación. La gente olvida la teoría y los ejemplos que no se aplican a sus propios problemas. Mejor contrata a un usuario avanzado. Conviértelo en la referencia del equipo (o de la empresa) para las cuestiones de Excel. Haz que se integre con los usuarios, encuentre ineficiencias y que las resuelva.

Aunque no lo parezca, Excel por sí solo puede incrementar la eficiencia de un equipo de trabajo -y hasta de toda un área.

Día especial

leonelquezada2007@gmail.com (Morpheus) — Mon, 06 Jul 2009 17:59:00 +0000

El día de hoy es el día 40,000 Excel. Y no porque Excel tenga 40,000 días en el mercado, sino porque este es el número serial que corresponde al 6 de julio de 2009. Recordemos que el sistema de fechas en Excel comienza el 1 de enero de 1900. Han pasado 40,000 días desde esa fecha.

Para comprobarlo, escribimos la fecha de hoy (Ctrl + Shift + ;) en una celda y posteriormente, aplicamos formato de número a la celda (Ctrl + Shift + 1).

Resultado inesperado - resolviendo el misterio

leonelquezada2007@gmail.com (Morpheus) — Mon, 22 Jun 2009 14:22:00 +0000

En relación a la nota Resultado inesperado, en la que comentaba algunos resultados aparentemente incomprensibles que Excel me devolvía, ckarlanga me hizo llegar su comentario y análisis de la situación, al que decidí dedicarle su nota propia dada su relevancia. Comenta ckarlanga:

"Hola, me hago llamar ckarlanga, y navegando por los foros di con esta info que me pareció bastante curiosa y sobre todo que alguien la mencionara, siendo un curioso del excel, estuve tratando de abrir la funcion de distribución normal, para ello intenté dar con el complemento que en excel lo contenía y me di la lata de abrir uno por uno los complementos, y por ahí di con el paquete que contiene atpvbes.xla atpvben.xla analisys32.xll funcres.xla y procdb.xla, les saque las claves a estos archivos y en los ATP tanto español como inglés, se encuentra la función networkdays y dias.lab, éste es el código:

Function Networkdays(start_date As Variant, end_date As Variant, Optional holidays As Variant) As Variant
Networkdays = Application.Run(GetMacroRegId("NETWORKDAYS"), start_date, end_date, holidays)

End Function

Cuando uno ejecuta esa function desde el excel va a esta función que ejecuta una función base para todas las creadas dentro de este paquete llamada GetMacroRegId. Esta a su vez, si logras convertir el archivo xla a xls, tiene una descripción de cada una de las funciones hechas dentro del xla:

Private Function GetMacroRegId(FuncText As String) As String
Debug.Print ("[GetMacroRegId] '" & FuncText & "' <") For i = LBound(FunctionIDs) To UBound(FunctionIDs)
If (LCase(FunctionIDs(i, 0)) = LCase(FuncText)) Then
If (Not (IsError(FunctionIDs(i, 1)))) Then
GetMacroRegId = FunctionIDs(i, 1)
Debug.Print ("[GetMacroRegId] '" & FuncText & "' -> '" & GetMacroRegId & "' >")
Exit Function
End If End If Next i
Debug.Print ("[GetMacroRegId] Error while finding '" & FuncText & "' >")
End Function

dentro del xla covertido a xls tienes esto ...

Function table
Labels VBAname Module Procedure Type text Function text Argument text Macro type Category Shortcut text Help topic Help text Arg1 Arg2 Arg3
HEX2DEC Hex2Dec hex2dec PP# HEX.A.DEC número 1 Ingeniería xlmain10.chm!1929

Convierte un número hexadecimal en decimal es el número hexadecimal que desea convertir

(encabezados)

y estos son los datos

NETWORKDAYS Networkdays networkdays PPPP# DIAS.LAB fecha_inicial;fecha_final;festivos 1 Fecha y hora xlmain10.chm!1963 Devuelve el número total de días laborables entre dos fechas es un número de serie que representa la fecha inicial es un número de serie que representa la fecha final.

El código al que haces referencia, 840368184, es el id de la macro que tiene adentro la fórmula de cálculo de los días hábiles, apenas tenga una respuesta específica dentro de que archivo se encuentra ( lo mas probable un dll) te lo informo...

un saludo...

perdona lo enredado de la respuestas... pero a esta hora no pienso más..."

Con esto comenzamos a resolver el misterio. Intentaré reproducir estos pasos en mi equipo para comparar resultados. Te agradezco esta colaboración, ckarlanga. Así mismo, te extiendo una cordial invitación a postear notas en este blog, las cuales, desde luego, llevarían tu firma. Si te gusta la idea, házmelo saber para otorgarte permisos de escritura.

Formatear el eje y

leonelquezada2007@gmail.com (Morpheus) — Wed, 27 May 2009 22:44:00 +0000

Supongamos el siguiente gráfico:

Queremos que para facilitar la lectura del mismo, tengamos tres colores en la escala del eje y: el verde para los valores mayores o iguales a 30, negro para los resultados entre -10 y 30 y rojo para los menores de -10, quedando de esta manera:

Para lograrlo seguimos los siguientes pasos:

Clic derecho en el eje y - Formato de ejes..., activamos la ficha Número. Seleccionamos la Categoría Personalizado. En Tipo: escribimos el siguiente formato personalizado:

[verde][>=30]#,##0;[Rojo][<-10]-#,##0;Estándar

Aceptar. Recordemos, sin embargo, lo que vimos en la nota pasada: nuestro principal objetivo es contar una historia con la gráfica, más que impactar visualmente.

Una buena gráfica

leonelquezada2007@gmail.com (Morpheus) — Mon, 25 May 2009 17:34:00 +0000

Como lo dice Chandoo en su blog, "una gráfica bién hecha nos cuenta una historia. Tan simple como eso."

Este es un ejemplo de lo que es una buena gráfica, "Tiempo empleado para comer por día vs tasa nacional de obesidad" (clic en la imagen para agrandarla):

x: minutos utilizados para comer por día
y: porcentaje de población con IMC > 30

Se toma unos cinco segundos en entender de qué es la gráfica. Y luego, conoces la historia. Lo más importante, estimula al lector a hacerse preguntas y entender los datos.

En sí misma, la gráfica es sencilla. Nada del otro mundo. Pero yuxtapone brillantemente dos piezas de datos: la tasa de obesidad en los países y el tiempo utilizado para comer, para contar una historia.

El artículo demuestra cabalmente uno de los fundamentos de Excel: el principal objetivo de una gráfica no es impactar visualmente, sino transmitir información clara rápidamente.

La gráfica de nuestro ejemplo dice más que esta otra, que aunque más visual, es más confusa:

Si una gráfica Excel no comunica información más rápidamente que una tabla de datos, podemos decir, con toda justicia, que es una mala gráfica. Sería mejor prescindir de ella y mostrar en su lugar solo los datos fuente.

Controlar el ingreso de datos en un formulario

leonelquezada2007@gmail.com (Morpheus) — Thu, 21 May 2009 21:05:00 +0000

Cuando trabajamos en un formulario Excel, a menudo sucede que las celdas de ingreso de datos están dispersas por toda la hoja, o no están en celdas adyacentes. Esto nos hace perder tiempo al tener que situar el cursor en la celda correspondiente cada vez:

Supongamos que necesitamos ingresar los datos en el orden mostrado en la imagen cada vez. Una forma de solucionar esto es creando un nombre que contenga las celdas de ingreso de datos. Procedemos como sigue:

Seleccionamos las celdas de entrada de datos en el orden que queramos llenarlas, pero comenzando por la número 2. Lo hacemos así para que la última celda seleccionada (que es la primera en el orden) sea la celda activa del nombre. Entonces, según el ejemplo, presionamos Ctrl y, en este orden, las celdas 2, 3, 4, 5, 6, 7, 8, 1.

Finalmente, le damos un nombre a este rango, digamos input_data.

De esta manera, cada vez que vayamos a ingresar datos en el formulario, simplemente vamos al nombre input_data (con F5 o seleccionándolo de la lista de nombres)

y comenzamos a escribir los datos presionando Enter para avanzar a la siguiente celda cada vez.

Primeras imágenes de Excel 2010

leonelquezada2007@gmail.com (Morpheus) — Wed, 20 May 2009 16:53:00 +0000

Microsoft ha decidido filtrar algunas imágenes de Office 2010. Curiosamente, le ha pedido a algunos sitios que las publicaron, que las retiraran inmediatamente. Ars Technica es uno de ellos. Pero mientras a mí no me digan nada, comparto con ustedes las correspondientes a Excel 2010.

En esta primera imagen podemos ver que el enorme botón de Office ha sido reemplazado por un rectángulo situado a la izquierda de la Pestaña Home (Inicio):

Otra de las imagenes filtradas muestra que todas las pestañas permanecen idénticas a las de 2007, a excepción de la de Insertar.

Los cambios observables son:

Hay un nuevo comando, Screenshot. Supongo que será similar a la cámara fotográfica actual. O bien, para salvar todo o parte de la hoja como imagen.

Un nuevo grupo de gráficas, Sparklines, o minigráficas. Excel 2010 soportará de forma predeterminada este tipo de gráfica.

Un nuevo comando, al final de la barra llamado Slicer. No tengo idea de su función.

El filtro avanzado - Introducción

leonelquezada2007@gmail.com (Morpheus) — Mon, 18 May 2009 05:03:00 +0000

La mayor parte de las veces el autofiltro satisface nuestras necesidades de filtrado. No obstante, tiene sus limitaciones. Una usauria me pregunta como filtrar una lista de forma que el campo filtrado muestre cuatro valores (criterios).

Este un ejemplo sencillo de filtrado que el autofiltro no puede realizar. Recordemos que en el cuadro Autofiltro Personalizado solo podemos establecer dos valores como criterio:

En estos casos, tenemos la opción de usar el filtro avanzado de Excel, mucho más flexible que aquel, aunque requiere de un poco más de trabajo. Antes de comenzar a utilizarlo, necesitamos elaborar una tabla de criterios. Esta tabla contiene la información que Excel utilizará para filtrar la lista. Consiste en al menos dos filas, la primera de las cuales contiene uno o más nombres de campo de la lista (excepto cuando utilizamos criterios calculados, tema de otra nota). Las filas restantes contienen los criterios a utilizar. Puede estar en cualquier parte de la hoja, siendo recomendable situarla en la parte superior de la lista, para que esté visible en todo momento (si la ponemos al lado de la lista, al filtrar, posiblemente se ocultarán algunas de sus filas).

La siguiente lista muestra una lista de los edificios conectados de una importantísima empresa de telecomunicaciones (clic en la imagen para agrandarla).

La columna E contiene la clave asociada a cada edificio. El requerimiento es mostrar únicamente aquéllos edificios que tengan clave A, B, C o D. Insertamos unas 6 filas para escribir nuestro rango de criterios, como sigue:

En este ejemplo, simplemente escribimos los valores buscados uno a continuación del otro, en el rango A1:A5. Estos deben tener exactamente el mismo encabezado que tienen en la lista original. Tengamos en cuenta que los criterios escritos en una misma fila Excel los une con el operador Y, mientras que los que están en otra fila los une con el operador O.

Nos situamos en cualquier celda de la lista y vamos a Datos - Filtro - Filtro avanzado... nos aseguramos que el valor de Rango de lista: sea el correcto. En Rango de criterios, escribimos el rango de la tabla de criterios, en nuestro caso, A1:A5. Si queremos que los registros filtrados se muestren en la misma ubicación original, damos Aceptar. Por el contrario, si queremos que la lista resultante esté en otro rango independiente, activamos la opción Copiar a otro lugar, y en Copiar a: escribimos la celda donde queramos que comience la lista. Aceptamos el cuadro.

Hemos visto un ejemplo muy sencillo que ejemplifica el uso del filtro avanzado. Sin embargo, la flexibilidad y potencia del mismo va mucho más allá. Podemos, por poner un ejemplo, filtrar la lista mostrando aquellos campos cuyos valores son superiores al promedio de los mismos, utilizando criterios formulados. Este será tema de otra nota.

Biohazard

leonelquezada2007@gmail.com (Morpheus) — Sat, 02 May 2009 07:12:00 +0000

Tipos de gráficos personalizados

leonelquezada2007@gmail.com (Morpheus) — Mon, 20 Apr 2009 13:17:00 +0000

Prácticamente todo mundo está de acuerdo en que el formato que los gráficos de Excel tienen de manera predeterminada, es punto menos que horrible.

Luce mal, los colores no combinan, el fondo del gráfico "cansa" la vista, las líneas de división sobrecargan el gráfico... en fin, siempre será necesario (si queremos comunicar información gráfica efectivamente) que mejoremos el formato predeterminado de estas gráficas. Excel nos brinda la oportunidad de hacerlo, así como de elaborar nuestra propia galería de tipos de gráfico, de manera que no tengamos que pasar por todo el proceso de "maquillaje" una y otra vez. Simplemente, seleccionaremos nuestros datos y les aplicamos alguno de nuestros tipos de gráfico personalizados.

Para lograrlo, procedemos como sigue:

Creamos una gráfica estándar, aplicándole los formatos y personalizaciones que más nos gusten. Recomiendo utilizar una muestra típica de los datos que estaremos graficando.

Una vez que el gráfico luzca como deseamos, lo agregamos a la galería de gráficos personalizada, siguiendo los siguientes pasos:

Teniendo seleccionado el gráfico, vamos a Gráfico - Tipo de gráfico..., pestaña Tipos personalizados.
En la sección Seleccionar desde, activamos la opción Definidos por el usuario.
Clic en Agregar...

En el cuadro Agregar tipo de gráfico personalizado, escribimos un nombre y, opcionalmente, una descripción de nuestro gráfico.

Clic en Aceptar. Con esto agregamos el gráfico a la galería. Aceptar nuevamente para cerrar el cuadro de diálogo.

Finalmente, probamos el gráfico. Seleccionamos una nueva muestra de datos, e iniciamos el Asistente para gráficos. En el paso 2, en Seleccionar el tipo de gráfico, escogemos el nuevo tipo de gráfico, bajo la pestaña Tipos personalizados, opción Definidos por el usuario (¿personalizados definidos por el usuario? Redundante pero cierto. Gracias, Bill). Clic en finalizar.

Sobre los comentarios

leonelquezada2007@gmail.com (Morpheus) — Thu, 16 Apr 2009 13:21:00 +0000

Esta vez, solo para informarles que desde hace un par de semanas he eliminado el paso de verificación de palabra al enviar comentarios. En otras palabras, cuando envíen sus comentarios, ya no verán cuadros como estos:

Sino que directamente llegarán sus comentarios a mi buzón, haciéndoles, así lo espero, más fácil el envío de los mismos.

Reglas de ordenación

leonelquezada2007@gmail.com (Morpheus) — Wed, 15 Apr 2009 13:32:00 +0000

Las celdas pueden contener diferentes tipos de contenido. Pueden contener texto, números, valores lógicos, resultados de error, o bien, pueden estar vacías o contener espacios en blanco (que no es lo mismo).

Si tenemos solamente valores numéricos, Excel los ordenará (Edición - Ordenar... ) del menor negativo al mayor positivo (para un orden ascendente). Pero, ¿qué pasa si queremos ordenar texto, o valores lógicos? ¿que pasa si tenemos varios tipos de información en las celdas?

En una ordenación ascendente, Excel presenta la información en el siguiente orden:

Valores numéricos, siguiendo el orden indicado anteriormente. Las horas y fechas son tratadas como números. Se toma en cuenta el valor intrínseco de la celda, no su valor formateado.
Texto, en orden alfabético, como sigue: 0 1 2 3 4 5 6 7 8 9 (espacio) ' - ! # $ % ( ) * , . / : ; ? @ [ \ ] ^ _ { | } ~ ¡ ´ ¿ + < = > ¬ ° A B C D E F G H I J K L M Ñ O P Q R S T U V W X Y Z.
Valores lógicos: primero FALSO, luego VERDADERO.
Valores de error (#¡NUM!, #N/A, etc.), apareciendo en su orden original, sin ordenarse por tipo de error.
Celdas vacías, siempre al final.

De manera predeterminada, la ordenación no es sensible a mayúsculas / minúsculas. Podemos cambiar esto dando clic en Opciones... del cuadro Ordenar:

Siguiendo un orden descendente, la secuencia queda a la inversa, excepto por las celdas vacías, que volverán a quedar al final de la lista. En la version en inglés, el texto sigue un orden ligeramente diferente.

Este blog cada día está más nerd.

Link.

Ingreso manual de referencias - Tip

leonelquezada2007@gmail.com (Morpheus) — Mon, 13 Apr 2009 14:08:00 +0000

En el blog Daily Dose of Excel, en la nota Illegal Range Names (nombres de rango inválidos), encontramos lo siguiente:

Podemos utilizar puntos al definir nombres, por ejemplo, mi.rango. Sin embargo, si el nombre que utilizamos, además de incluir un punto, parece una referencia de celda en la notación F1C1, por ejemplo, cu50.s1, Excel la evaluará como si fuera una referencia verdadera (en este caso, S1:CU50), y por lo tanto, nos avisará que es un nombre inválido.

En la misma nota, Dick Kusleika nos provee este tip: al introducir manualmente una referencia de celda, podemos utilizar el punto (.) en lugar de los dos puntos (:). Después, al presionar Enter, Excel lo convertirá a ":".

Si escribimos "=SUMA(A2.A120)", al presionar Enter veremos en la barra de fórmulas:

=SUMA(A2:A120)

Seleccionar todos los objetos de una hoja

leonelquezada2007@gmail.com (Morpheus) — Wed, 08 Apr 2009 14:00:00 +0000

En Yahoo respuestas, encontré la siguiente pregunta:

¿Como selecciono en Excel miles de lineas "auto-formas" de una sola?

Necesito modificarlas a todas. Conozco una manera de seleccionar varias con la tecla Ctrl y clickeando en cada una, pero aun así es muy laborioso si son miles de ellas.
Agradezco muchísimo de antemano a quien pueda ayudarme.

La solución es:

Edición - Ir a... - Especial... - Objetos.

Con el teclado:

F5, Alt+e, j, Aceptar.

Cabe aclarar que con esto seleccionaremos todos los objetos, es decir, incluyendo gráficos e imagenes, no solamente las autoformas. Tengámoslo en cuenta.

Habría que revisar también porqué el usuario tiene estos miles de líneas en su hoja. Aunque es posible realizar dibujos de aceptable calidad en Excel, no es la mejor herramienta para hacerlo.

Contar subcadenas en cadena

leonelquezada2007@gmail.com (Morpheus) — Mon, 06 Apr 2009 15:00:00 +0000

En mundoexcel.com, uno de los foros que frecuento, un usuario envió esta consulta:

Hola tengo una gran pequeña duda y por mas que leo y busco no encuentro la solucion: quiero contar cuantas veces esta escrita una letra en una celda.

Ejemplo:

Palabra " Antonio"

Mi pregunta es cuantas letras "O" tiene esa palabra.

La solución que propuse fue utilizar una fórmula matricial, como sigue:

Supongamos que en A2 está la palabra "Antonio", en B2 la letra a contar "o".

En C2 escribe la siguiente fórmula matricial:

=SUMA(--(EXTRAE(A2,FILA(INDIRECTO("1:"&LARGO(A2))),1)=B2))

Recuerda aceptarla con Ctrl + Shift + Enter.

LARGO(A2) devuelve el número de caracteres de Antonio, 7.

INDIRECTO devuelve el string "1:7", que sirve de argumento a FILA.

FILA(1:7) genera una lista de consecutivos de 1 a 7. En otras palabras, genera la constante matricial {1; 2; 3; 4; 5; 6; 7}.

EXTRAE devuelve cada uno de los caracteres 1o. a 7o. La constante matricial cambia a: {"A"; "n"; "t"; "o"; "n"; "i"; "o;"}.

Se compara cada uno con B2, "o". Si son iguales, se genera un VERDADERO. Si no son iguales, se genera un FALSO: {FALSO; FALSO; FALSO; VERDADERO; FALSO; FALSO; VERDADERO}.

Al agregar -- Excel convierte los VERDADEROS en unos, y los FALSOS en ceros: {0; 0; 0; 1; 0; 0; 1}.

Finalmente se suman estos unos y ceros, resultando 2.

PHD entrevista a John Walkenbach

leonelquezada2007@gmail.com (Morpheus) — Mon, 30 Mar 2009 20:01:00 +0000

Chandoo ha publicado la entrevista que realizó a John Walkenbach. Para quienes deseen leerla (en inglés), clic aquí.

Debo decir que quedé un poco decepcionado. Esperaba más de esta entrevista, viniendo de mr. Spreadsheet. Al leerla me pareció que Mr. Walkenbach solo intentaba vendernos alguno de sus libros, más que enseñarnos algo. Indudablemente, la de Charley Kyd fue una entrevista mucho más didáctica y amena.

Advanced Excel Conference

leonelquezada2007@gmail.com (Morpheus) — Fri, 27 Mar 2009 19:43:00 +0000

Jon Peltier ha anunciado en su sitio, la primera edición de la Excel User Conference, que se llevará a cabo los días 17 y 18 de junio, en Atlanta, Georgia (EU). Contará con la participación del mismo Jon:

y de Bob Umlas, otro Excel MVP (algunos llaman a sus fórmulas forUmlas):

Cada uno de ellos dará un par de clases acerca de elaboración de gráficos y programación en Excel. Esta conferencia entra en sustitución de la Excel User Conference, que se estuvo realizando en años previos. Lamentablemente, los organizadores no pudieron organizar la edición de este año. Afortunadamente, Jon tomó la iniciativa y organizó su propia conferencia. Así pues, a ahorrar se ha dicho. La oportunidad de escuchar y dialogar con dos Excel MVP's simultáneamente no se da todos los días.

La función SIFECHA

leonelquezada2007@gmail.com (Morpheus) — Tue, 24 Mar 2009 00:00:00 +0000

La función SIFECHA es un caso aparte. No por su simpleza o complejidad. Simplemente, porque desde Excel 5.0 no está documentada. Es una función secreta, por decirlo de algún modo. En efecto, si buscamos la función en el listado de funciones de Excel, veremos que no aparece, aún si como categoría seleccionamos Todas. Tampoco aparece en la ayuda on-line de Excel. Supuestamente (y esto es solo un mito urbano) Microsoft mantiene oculta esta función porque planea desaparecerla, ya que es una función originaria de Lotus. Por si fuera poco, está mal traducida, ya que su nombre original, en inglés, es DATEDIF (date diference), no DATEIF.

SIFECHA devuelve la diferencia entre dos fechas, expresada en determinado intervalo. La sintaxis:

=SIFECHA(fecha_1, fecha_2, intervalo)

fecha_1 y fecha_2 deben ser fechas válidas, de otra forma, obtendremos un error #¡VALOR!. fecha_1 debe ser menor (más antigua) que fecha_2. Si no es así, se obtiene #¡NUM! El argumento intervalo especifica la unidad de medida en la que Excel devolverá el resultado. Puede ser uno de los siguientes valores:

"m" - meses. Número de meses completos entre fecha_1 y fecha_2.

"d" - días. Número de días entre fecha_1 y fecha_2.

"y" - años. Número de años completos entre fecha_1 y fecha_2.

"ym" - meses excluyendo años. Número de meses entre fecha_1 y fecha_2, suponiendo que fecha_1 y fecha_2 son del mismo año.

"yd" - días excluyendo años. Número de días entre fecha_2 y fecha_2, suponiendo que fecha_1 y fecha_2 son del mismo año.

"md" - días excluyendo meses y años. Número de días entre fecha_2 y fecha_2, suponiendo que fecha_1 y fecha_2 son del mismo mes y del mismo año.

Supongamos que queremos calcular la diferencia entre las fechas 01/03/2007 (uno de marzo de 2007) y la actual 24/03/2009. El resultado de SIFECHA variará según el intervalo especificado, como sigue:

Nótese que si el argumento intervalo es una referencia, no debe estar escrito entre comillas.

Para calcular la edad de una persona podemos usar una fórmula como la siguiente:

=SIFECHA(A1,HOY(), "y") & " años, " & SIFECHA(A1,HOY(),"ym") & " meses, " & SIFECHA(A1, HOY(), "md") & " días."

Suponiendo que la fecha de nacimiento del individuo estudiado está en A1.

Utilizando VBA, SIFECHA no puede ser llamada utilizando WorksheetFunction. Es preciso elaborar manualmente el código que haga los cálculos, por ejemplo:

Function Age(fecha1 As Date, fecha2 As Date) As String

Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date

Temp1 = DateSerial(Year(fecha2), Month(fecha1), Day(fecha1))
Y = Year(fecha2) - Year(fecha1) + (Temp1 > fecha2)
M = Month(fecha2) - Month(fecha1) - (12 * (Temp1 > fecha2))
D = Day(fecha2) - Day(fecha1)

If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(fecha2), Month(fecha2) + 1, 0)) + D + 1
End If

Age = Y & " años " & M & " meses " & D & " dias"

End Function

It´s friday

leonelquezada2007@gmail.com (Morpheus) — Fri, 20 Mar 2009 15:00:00 +0000

Del sitio Comics I don't understand:

El jefe no dejará que nadie abandone está reunión hasta que alguien explique cómo es que se rompió la ventana.

En el blog de Jon Peltier:

-Nuestra relación comenzó a declinar en este punto.
-Fue ahí cuando comenzaste a graficarlo todo.
-¡Coincidencia!

Ocultar hojas efectivamente

leonelquezada2007@gmail.com (Morpheus) — Fri, 13 Mar 2009 16:00:00 +0000

Algunos de nuestros libros contienen hojas que preferimos ocultar, ya sea porque son hojas con cálculos parciales, o porque contienen información confidencial. O porque simple y sencillamente, no queremos que otros las vean. Normalmente, las ocultamos con Formato - Hoja - Ocultar.

Desde luego, el ocultar hojas de este modo no evita de ninguna manera que el usuario husmée en ellas e incluso modifique nuestras fórmulas, llegando a descomponer completamente el libro. Mucho menos sirve para ocultar información de carácter confidencial. Todo lo que se tiene que hacer es ir a Formato - Hoja - Mostrar... y seleccionar la hoja que se quiera ver.

Para estos casos, tenemos la posibilidad de ocultar una hoja de forma que ni siquiera aparezca en el cuadro Mostrar hoja... utilizando el editor de Visual Basic (VBE).

Procedemos como sigue:

Ingresamos al Editor de VB (Alt + F11) y localizamos nuestro libro en el Explorador de proyectos (precedido con la palabra "VBAProject").

Las hojas del libro están dentro de la subcarpeta Microsoft Excel Objetos. En caso de que no se vean, las mostramos dando clic en el botón + de dicha subcarpeta. Seleccionamos la hoja que queremos ocultar.

En la ventana Propiedades buscamos la propiedad Visible y establecemos su correspondiente valor a 2 - xlSheetVeryHidden (si no se muestra la referida ventana, la mostramos con Ver- Ventana propiedades).

Con este valor en la propiedad Visible, la hoja estará oculta y no aparecerá en el cuadro Mostrar hoja...

Ahora bién, existe la posibilidad de que el usuario conozca este "truco" y reestablezca la propiedad Visible a -1 - xlSheetVisible (hoja visible) siguiendo los mismos pasos. Para evitar esto es necesario proteger el proyecto con una contraseña. Vamos a Herramientas - Propiedades de VBAProject..., ficha Protección:

Activamos la casilla Bloquear proyecto para visualización, escribimos alguna contraseña y aceptamos el cuadro. Con esto, si el usuario intenta mostrar el editor de Visual Basic, Excel le requerirá escribir la contraseña para abrirlo.

Finalmente cerramos el editor VB y guardamos el archivo. De regreso en Excel, damos Formato - Hoja - Mostrar...:

y comprobamos que la hoja que recién ocultamos no aparece en este cuadro. Más aún, si solo hemos ocultado una hoja, ni siquiera estará habilitado el comando Mostrar...

Pegado especial valores - Revisited

leonelquezada2007@gmail.com (Morpheus) — Tue, 10 Mar 2009 23:00:00 +0000

Copiar un rango de celdas y pegar únicamente los valores de las mismas es una operación un tanto frecuente.

Normalmente, lo hacemos copiando el rango de celdas, seleccionando la celda de destino y dando clic derecho - Pegado especial... Valores:

Otra opción es copiar el rango, clic en la lista desplegable del botón pegar, Valores:

La forma más rápida, para mi gusto, es la siguiente:

Seleccionamos el rango a copiar, y luego lo arrastramos a su nueva ubicación con el ratón, pero utilizando el botón derecho del mismo. Al soltar este botón, aparecerá un menú desplegable. Damos clic en la opción Pegar aquí solo como valores.

Si queremos convertir un rango a valores (es decir, pegar como valores pero en la misma ubicación), arrastramos el rango (con clic derecho) una columna a la derecha, regresamos a la posición original, soltamos y seleccionamos Pegar aquí solo como valores.

Para los que saben inglés, pueden ver la explicación de esta técnica en el siguiente video:

La función utilizada en el video, RAND, es la función ALEATORIO en español.