5incoaprendices

Como llamar a una función BUSCARV simplificada con VBA en Excel 2007

noreply@blogger.com (Aladino) — Tue, 05 Apr 2011 19:31:00 +0000

Buenas, me gustaría saber cómo puedo crear la función BUSCARV de tal manera que sólo me pida el dato a buscar y al ejecutarla me haga todo el cálculo. Gracias.

Mucha gente cree que ha de crear una UDF o Función Definida por el Usuario, pero lo cierto es que las funciones primitivas de Excel están creadas en C++ que es un lenguaje mucho más potente que Visual Basic, por lo que no merece la pena intentar recrear la función en VBA ya que será mucho más lenta.

La mejor solución es llamar a la función BUSCARV desde VBA.

Imaginemos que tenemos esta tabla de datos como origen de datos

Si queremos hallar el apellido de Segismundo, utilizaríamos la función BUSCARV de la siguiente manera:

=BUSCARV(A4;$A$2:$B$5;2). Recuerda que los datos de la tabla han de estar ordenados para que la función muestre los datos correctamente y no dé un error. Así, en este caso, al indicar como valor buscado A4 (Segismundo) nos devuelve el valor correspondiente (García)

Si lo que queremos es utilizar la función BUSCARV de manera más simple, teniendo solo que señalar el valor buscado hemos de crear una macro.

Activamos el Editor VBA con ALT + F11

Insertamos un módulo desde Insertar - Módulo

Nuestro módulo aparecerá bajo la carpeta Módulos del explorador de VISUAL bASIC

En la ventana de la derecha pegamos el siguiente código

Sub Buscarvsimplificado() Creamos el nombre de la macro

Dim valorb As String Definimos el tipo de dato

valorb = Application.InputBox("Valor Buscado", "Función BUSCARV Simplificada") Creamos el cuadro de introducción de datos

ActiveCell = Application.VLookup(valorb, Range("$A$2:$B$5"), 2)

Indicamos que devuelva el resultado de la función BUSCARV en la celda seleccionada

End Sub

Ficha programador - Código - Macros

Seleccionamos la macro Buscarvsimplificado

Hacemos clic en Opciones...

Asignamos las teclas de método abreviado y hacemos clic en Aceptar

Al pulsar en el atajo de teclas, en este caso, Ctrl + N se ejecutará la macro mostrándose el cuadro de diálogo que nos solicitará que introduzcamos el valor que deseemos. Puede ser texto o podemos seleccionar la celda en la que se encuentra el valor.

Hacemos clic en Aceptar y observamos cómo nos muestra el resultado correctamente

Por último, para que funcione la macro si estamos en Excel 2007, hemos de guardar el libro de trabajo como Libro de Excel habilitado para macros, de lo contrario, el código no se ejecutará.

Nota: Cuidado al asignar las teclas de método abreviado, ya que si las que seleccionamos ya tiene asociadas una acción, dejará de funcionar para que nuestra macro funcione. En nuestro ejemplo, CTRL + N sirve para aplicar negrita. Si asignamos la macro a dicha combinación de teclas, no podremos aplicar negrita.

Cómo mostrar dos series en un mismo gráfico

noreply@blogger.com (Aladino) — Mon, 04 Apr 2011 09:56:00 +0000

Hola, ¿Quería saber cómo puedo tener dos ejes en un mismo gráfico? Vuestro blog es estupendo.

Es muy sencillo. Para ello, has de tener dos series de datos. Tomemos como ejemplo esta simple tabla.

A continuación, creamos un gráfico simple, desde Insertar - Gráficos - Columna

Aquí tenemos nuestro gráfico creado a partir de la tabla

Para mostrar la segunda serie de datos, hacemos clic con botón derecho sobre cualquiera de las columnas de datos del gráfico y seleccionamos Dar formato a serie de datos

En el cuadro de diálogo Formato de serie de datos, hemos de seleccionar la opción Eje secundario

Al cerrar el cuadro de diálogo, nuestra segunda serie de datos se mostrará en el lado derecho

NOTA: Si no aparece la opción eje secundario, has de cambiar el tipo de gráfico. Por ejemplo, si es columna apilada, no mostrará la segunda serie de datos, sin embargo, la agrupada sí lo mostrará.

Cómo convertir el resultado de la función AHORA en fijo

noreply@blogger.com (Aladino) — Fri, 01 Apr 2011 18:50:00 +0000

¿Alguien sabe cómo se puede convertir el resultado de una función volátil como, por ejemplo, AHORA() en un valor fijo?

Cuando utilizamos funciones volátiles como HOY() o AHORA(), nos encontramos con el problema de que cada vez que actualizamos la hoja de cálculo, esta función se actualiza con la fecha y hora actual en vez de la fecha y hora de la primera vez que realizamos el primer cálculo.

Como podemos observar, cuando realizamos el primer cálculo, nos muestra una fecha y una hora

Pero si volvemos a actualizar la hoja, o abrimos el libro posteriormente, los datos se vuelven a calcular. En el ejemplo, al actualizar la hoja un minuto más tarde, la hora muestra dicho cambio.

SOLUCIÓN:

Haz clic con el botón derecho del ratón sobre el lateral de la celda que contiene la función volátil, en este caso AHORA()

Arrastra el ratón sin soltar el botón a una celda adyacente

Y sin soltar el ratón vuelve a la celda actual.

En nuestro ejemplo arrastramos con botón derecho hasta la celda B2 y sin soltar el botón derecho del ratón, volvemos a la celda A1, en donde ya soltamos el botón.

Al hacerlo, aparecerá un menú contextual con una serie de opciones.

Elegimos Copiar aquí sólo como valores

Como podemos observar en la barra de fórmulas, se nos indica que el valor ha quedado fijo.

noreply@blogger.com (Aprendiz) — Tue, 15 Mar 2011 14:40:00 +0000

Muchas gracias por suscribirte de nuevo,

...pero ya estás suscrito. Recuerda confirmar el correo que te he enviado haciendo clic sobre el enlace para evitar el SPAM y finalizar tu suscripción. Si no lo has recibido, revisa tu carpeta antispam, puede haberse colado. Y asegúrate de asignar cmo contacto mi dirección de correo para que no te pierdas ni una sola lección.

Odio que recibas mis lecciones si no las has solicitado. Es contraproducente tanto para ti como para mí.

Puedes darte de baja en cualquier momento haciendo clic en el enlace Unsuscribe parecido a este:

To unsubscribe or change subscriber options visit:

http://www.aweber.com/z/r/?jByMbOwMTLSsDKyczCwsrLRmtJyMnJyszAw=

Un abrazo

Claudia Castellano

¡¿A qué esperas para empezar ya?!

Ya has confirmado tu suscripción

noreply@blogger.com (Aprendiz) — Tue, 15 Mar 2011 13:03:00 +0000

¡Genial!

Ya estás dentro. Revisa tu correo. En unos minutos recibirás la Lección 1 sobre Tablas Dinámicas ;-)

¡Espero verte pronto y recibir tus dudas!

Claudia

Quitar salto de página Excel 2007

noreply@blogger.com (Aladino) — Wed, 16 Feb 2011 12:46:00 +0000

He aplicado la Vista previa de salto de página y cuando he vuelto a la vista normal me aparecen puntos discontinuos y no puedo quitarlos

Eso tiene una solución sencilla. Suponemos que te ha ocurrido algo así:

Tienes datos en una hoja

Has hecho clic en la zona inferior derecha Vista previa de salto de página o bien desde Vista - Vistas de libro - Vista previa de salto de página

Excel 2007 ha creado las divisiones de salto de página y has pulsado Aceptar

Cuando has vuelto a la vista Normal

Te has encontrado con una serie de lineas discontinuas que no podías quitar.

Esto tiene una sencilla solución visual.

Botón Office

Hacemos clic en Opciones de Excel

Seleccionamos Avanzadas en el menú de la izquierda
Nos desplazamos hasta la sección Mostrar opciones para esta hoja

Desactivamos la casilla Mostrar saltos de página
Hacemos clic en Aceptar

Y Voilà, la línea de salto de página ya no se muestra.

Otro ejemplo de la función CONTAR.SI en Excel 2007

noreply@blogger.com (Aprendiz) — Fri, 14 May 2010 15:26:00 +0000

Hola,

Tengo la siguiente fórmula:

=SI(O(C3="SUSPENSO"; C4="SUSPENSO";C5="SUSPENSO";C6="SUSPENSO");"SUSPENSO";"APROBADO")

Me indica que está un alumno está SUSPENSO si alguno de los valores tiene un SUSPENSO. La fórmula funciona muy bien. El problema es que a partir de ahora he de tener en cuenta 20 valores más, para lo cual tendría que escribir uno por uno. Quería saber si hay alguna manera de realizar esto de forma más práctica.

Muchas gracias.

La respuesta es sencilla. Hemos de utilizar otra función más potente, combinada con el SI.

Nuestro lector tiene la siguiente tabla,

En la cual al final ha utilizado la siguiente fórmula para calcular el Total.

El problema que se le plantea a nuestro lector con esta fórmula es que al aumentar el número de celdas a evaluar, nuestra fórmula será poco práctica. Por ejemplo, si tuviésemos que evaluar todas las celdas de la columna, la fórmula sería:

Esta fórmula se antoja poco práctica. La mejor opción es utilizar otra función como CONTAR.SI en lugar de la función O.

Con esta fórmula estamos diciendo que nos cuente cuantos suspensos hay. Si hay algún suspenso, entonces se cumple la condición de la función SI y mostrará SUSPENSO. Si no se cumple la condición, entonces mostrará APROBADO.

Como podemos observar, el resultado que obtenemos es el mismo que con la anterior fórmula.

Nota: Podemos utilizar la fórmula inicial de nuestro lector, pero de forma matricial

=SI(O(C3:C6;"SUSPENSO");"SUSPENSO";"APROBADO")

Para confirmar pulsamos ctrl+Intro.

Como hemos indicado en otras ocasiones, las fórmulas matriciales tienen el inconveniente de ser más lentas cuando han de realizar el cálculo con muchos datos.

Si quieres recibir nuestro mini-curso de Excel, suscríbete a nuestra Newsletter arriba a la derecha. Recibirás información de calidad, así como dos archivos PDF con todos nuestros artículos publicados sobre Excel.

Contar celdas con formato condicional

noreply@blogger.com (Aladino) — Mon, 10 May 2010 13:59:00 +0000

Hola! Oye Aladino, a ver si me puedes echar un cable. Yo precisamente quiero contar el número de celdas que han sido coloreadas por un formato condicional. En este caso el formato condicional ha sido marcar las celdas con el número más grande, es decir, en reglas de formato "Aplicar formato a los valores que están en el rango en - Superior - 1".

Esto funcina sin problemas, y de cada columna me va marcando qué valor es el más elevado. Ahora quiero contar por filas cuál es la incidencia que más veces ha obtenido el valor más elevado, y no hay manera. No sé qué parámetro he de poner en el campo criterio de la función contar.si

En teoría, por lo ejemplos del excel y por lo que he visto por ahí, debería ser "1 superior", que es como se llama la condición del formato, pero nada... A ver si alguien me puede dar una idea.

Un saludo y muchas gracias ^_^

Miguel

En este caso, nuestro lector no establece un buen planteamiento. La norma general es que cuando aplicamos color de relleno a nuestras celdas, para contar hay que seguir el método indicado en este artículo. Cuando el formato se ha aplicado mediante formato condicional, a la hora de contar celdas por color o formato, tendremos que utilizar la fórmula utilizada en formato condicional. No obstante, en este caso concreto, no podemos utilizar ninguna de ambas soluciones, ya que el formato condicional se ha aplicado por columnas y nuestro lector desea obtener resultados por filas. Es decir, contar el número de celdas de color pero por filas.

Ponemos un ejemplo muy sencillo. Veamos la siguiente tabla a la cual hemos aplicado la regla Superior 1 del formato condicional.

Lo hemos hecho por columnas, seleccionando una columna

Inicio – Formato Condicional – Administrar reglas o directamente desde Nueva regla

Y seleccionamos la regla Aplicar formato únicamente a los valores con rango inferior o superior

Marcamos como porcentaje 1 en vez de 10

Hacemos clic en Aceptar

Las soluciones a este problema son múltiples ya que podemos utilizar fórmulas de distintos tipos.

=CONTAR.SI(B3:C3;MAX(B:B)+ CONTAR.SI(B3:C3;MAX(C:C)+CONTAR.SI(B3:C3;MAX(D:D)

=SI(B3=MAX($B$3:$B$5);1;0)+SI(C3=MAX($C$3:$C$5);1;0)+SI(D3=MAX($D$3:$D$5);1;0)

Creo que la más sencilla es la siguiente:

=(MAX($B$3:$B$5)=B3)+(MAX($C$3:$C$5)=C3)+(MAX($D$3:$D$5)=D3)

Escribimos esta formula en E3

Y la aplicamos hacia abajo al resto de filas

Si queremos escribir menos, podemos acortar creando previamente los nombres por cada columna

Pulsamos Ctrl+F3

En nuestro caso le damos el nombre maxim1
Establecemos que debe ser el máximo de la columna Valor 1. =MAX($B$3:$B$5)

Aceptamos y creamos otro nombre para la columna Valor 2 que llamamos maxim2

Aceptamos y creamos otro nombre para la columna Valor 3 denominado maxim3 siguiendo los pasos anteriores.

Y cerramos

Y escribimos nuestra fórmula usando dichos nombres:

=(maxim1=B3)+(maxim2=C3)+(maxim3=D3)

Podemos observar como ambas fórmulas dan resultados correctos.

Como crear un gráfico en Excel 2007 a partir de una tabla en Word 2007

noreply@blogger.com (Aprendiz) — Mon, 03 May 2010 07:01:00 +0000

Pregunta un lector si es posible crear un gráfico basado en una tabla en Word.

Si la tabla está en Word, hemos de pasarla primero a Excel.

Seleccionamos la tabla en Word

Copiamos y pegamos mediante pegado especial en Excel.

Una vez con nuestra tabla pegada en Excel creamos el gráfico desde la ficha Insertar

Seleccionamos el tipo de gráfico que queremos

Y lo modificamos un poco para que quede mejor. Por ejemplo, reduciendo el formato del eje izquierdo (valores Y)
Establecemos que nos muestre las cantidades en millones, pues en este caso son cantidades altas

Desde la categoría Número establecemos los decimales a 0.

Haz clic en Cerrar

Agranda un poco el gráfico

Suscríbete a nuestra Newsletter y disfruta de todas sus ventajas.

Como crear una autoforma con bordes con líneas en Word 2007

noreply@blogger.com (Aladino) — Fri, 30 Apr 2010 07:01:00 +0000

Hola, tengo una autoforma cuyo borde no sé como realizar. A ver si me podéis ayudar. Gracias.

La autoforma a la que se refiere nuestro lector es la que vemos en la imagen siguiente.

Pasos para realizarla:

Ficha Insertar - Formas
Seleccionamos la autoforma elíptica
La dibujamos

Ficha opcional Herramientas de dibujo - Formato - Efectos 3D
Aplicamos el estilo 3D 4

Y esto es todo. Así de simple.

Obtener la suma del producto de dos columnas

noreply@blogger.com (Aladino) — Mon, 26 Apr 2010 07:01:00 +0000

Buenos días,

Os agradecería me ayudarais a realizar una formula dónde me diera la suma de el producto de la columna A por la columna B. Una sóla formula sin arrastrar las filas una a una.

Adjunto ejemplo.

Muchas gracias,

Un saludo.

Este es el ejemplo que adjunta nuestra lectora.

Lo que ella necesita es calcular en una sola celda la suma de los valores obtenidos al multiplicar las columnas A y B pero sin tener que pasar por un primer paso de multiplicar los valores en la columna auxiliar C y posteriormente sumar los valores.

Podemos hacerlo de dos maneras:

Utilizando la función SUMA matricial: =SUMA((A1:A4)*(A1:B4)).

Nota: Recuerda hacer pulsar Ctrl+Intro en vez de Intro para que haga el cálculo matricial. Podemos observar que las funciones matriciales están rodeadas por llaves.

O utilizando la función SUMAPRODUCTO:

=SUMAPRODUCTO((A1:A4)*(A1:B4))

Nota: Si nuestra hoja de cálculo tiene muchos datos es mejor evitar las funciones matriciales puesto que son más lentas.

Descarga el archivo de ejemplo aquí.

Suscríbete a nuestra Newsletter

Ejemplo de tabla dinámica

noreply@blogger.com (Aladino) — Fri, 23 Apr 2010 07:01:00 +0000

Aquí mostramos otro ejemplo de tabla dinámica. La imagen inferior muestra los datos de ventas de algunos vendedores por Zonas en el primer cuatrimestre.

Creamos la tabla dinámica desde Insertar - Tabla dinámica

Si hemos situado previamente el cursor en alguna de las celdas de la tabla origen, Excel asignará los datos de la tabla directamente. De no ser así, tendremos que indicárselo.

En este ejemplo, crearemos la tabla en la misma hoja, concretamente en la celda A18.

Marcamos todos los valores.

Recuerda que los valores numéricos se asignarán al recuadro valores y los alfabéticos a los rótulos de fila

Se creará nuestra tabla dinámica

Aplicamos un filtro para seleccionar los vendedores de Madrid

Desde la opción Diseño

desactivamos los subtotales

Para cambiar la estructura de datos, asignamos los valores por filas.

Los campos quedarán de la siguiente manera

De esta manera, se mostrarán los vendedores que venden en Madrid y le asignaremos una estructura por filas

Añadimos un poco de estilo y aquí tenemos nuestra tabla dinámica.
Puedes descargarte el ejemplo aquí.

Si deseas recibir lecciones, trucos o dar clases por e-mail, suscríbete a nuestra Newsletter.

Texto que se extienda a lo largo de toda la línea de manera exacta en Word 2007

noreply@blogger.com (Aprendiz) — Tue, 20 Apr 2010 09:39:00 +0000

Hola. Mi consulta es la siguiente. Tengo un ejercicio de Word en el que el texto aparece distribuido pero por más que lo hago desde el párrafo, no consigo hallar la medida exacta para que cuadre con los márgenes. He conseguido solucionarlo introduciendo el texto en una tabla y aplicando un ajuste de texto a la celda, pero me gustaría saber si existe una manera más rápida de hacer esto.

Lo que nuestra lectora quiere es aplicar una distribución del texto exacta tal y como aparece en la imagen inferior.

Para ello, ha solucionado el problema introduciendo en una tabla. En 2007 lo logramos desde la ficha Insertar - Tabla. Creamos una tabla de 1 fila por 1 columna.

Una vez creada la tabla, escribimos el texto Manual de seguimiento dentro.

Hacemos clic con botón derecho sobre la tabla y seleccionamos la opción Propiedades de tabla
Seleccionamos la opción Celda

Hacemos clic en Opciones

Marcamos la casilla Reducir texto hasta ajustarlo

Hacemos clic en Aceptar

Y volvemos hacer clic en Aceptar en la siguiente pantalla.

Observamos como nuestro texto aparece distribuido a lo largo del párrafo de manera exacta sin tener que acudir a la opción de párrafo y distribuir
Por último, convertimos la tabla en texto

Al crear una tabla, aparece la opción Herramientas de tabla y justo debajo Diseño y Presentación.

Bajo el grupo Datos seleccionamos la opción Convertir texto a

Dejamos las opciones por defecto que vemos en la imagen que se muestra a continuación.

Hacemos clic en Aceptar

He aquí nuestro texto distribuido a la perfección

Posteriormente, aplicamos un color de fondo a nuestro párrafo.

Aunque esta opción es viable, sin embargo, es demasiado compleja en relación con el resultado que pretendemos obtener.

Este mismo resultado podemos obtenerlo mediante el atajo de teclado Ctrl+Mayusc+J

Si deseas recibir trucos, lecciones, clases por e-mail o realizar consultas sobre temas que te interesen, no dudes en suscribirte a nuestra Newsletter.

Insertar formulario de comentarios bajo el post o artículo en Blogger

noreply@blogger.com (Aprendiz) — Tue, 06 Apr 2010 11:18:00 +0000

Hola, cómo puedo poner en mi blog los comentarios como los tenéis vosotros, justo debajo del post para que el usuario no tenga que hacer un clic de más?

Suponemos que deseas tener el formulario de comentarios tal y como aparece en la imagen inferior.

Es muy sencillo. Esta opción ya está implementada en Blogger sin necesidad de escribir código como ocurría antaño.

Accede a tu cuenta de Blogger y haz clic en Configuración - Comentarios
Asegúrate en el apartado Comentarios tener activada la opción Mostrar

En el apartado Ubicación del formulario de comentarios marca la opción Entrada incrustada a continuación

Guarda los cambios.

Como podemos observar, Blogger nos indica que para que esta opción funcione hemos de tener habilitada la opción Publicar páginas. Esta opción se encuentra ubica en el apartado Archivos.

Hacemos clic en Sí y guardamos los cambios

En caso de que esta opción esté inhabilitada, hemos de volver a Comentarios y seleccionar cualquier otra ubicación del formulario comentarios. como por ejemplo, Página completa

No hemos de olvidarnos de guardar los cambios, ya que si no, no surtirá efecto.

Observamos como en Archivos se ha habilitado la opción ¿Habilitar páginas de entrada?. Seleccionamos Sí. Guardamos la configuración.

Volvemos a seleccionar en Comentarios la opción Entrada incrustada a continuación.

Cómo acceder al código de seguimiento de mi Google Analytics

noreply@blogger.com (Aladino) — Sat, 03 Apr 2010 18:26:00 +0000

Hola, tengo un problema. Hace tiempo me hice una cuenta en google analytics y al cambiar de plantilla en blogger, perdí el código de seguimiento. ¿Cómo puedo encontrarlo?

Es muy sencillo. Los pasos son los siguientes:

1. Accede a tu cuenta de Google Analytics. Cuando aparezcan los perfiles del sitio web, haz clic en la opción Editar situada a la derecha del Porcentaje de cambios.

2. Una vez que hayas accedido, haz clic en la opción Comprobar estado situada al lado de la opción Recepción de estado, en la parte superior derecha del cuadro Información del perfil del sitio web principal.

3. Por último, bajo el apartado Pegue este código en su sitio, podrás observar como aparece el código que necesitas pegar en tu blog para poder seguir obteniendo los resultados de visitas.

Nota: Recuerda que el tener un código incorrecto o inexistente es una de las principales razones por las que puede que no obtengas datos de ningún tipo.

La papelera de mi correo Gmail ha desaparecido

noreply@blogger.com (Aprendiz) — Thu, 01 Apr 2010 20:11:00 +0000

Hola,

He entrado en mi correo de Gmail y he eliminado por equivocación un correo. Como sé que una vez que se elimina va a la papelera, he ido a la misma, pero cual ha sido mi sorpresa al ver que la papelera había desaparecido del menú de la izquierda. ¿Cómo puedo arreglar esto?

Solución rápida: Escribir en el cuadro de búsqueda de nuestro correo label: trash o también in:trash, pero esta solución es temporal.

Solución permanente: Gmail permite ocultar y mostrar las etiquetas que queramos. Partimos de la siguiente imagen en la que no tenemos a la vista la Papelera en nuestro correo de Gmail.

Para solucionar esto, hemos de ir a Configuración

Hacemos click en Etiquetas

En la etiqueta Papelera hacemos clic en mostrar

Una vez activada la opción, quedará sin subrayar

Como podemos observar, ya aparece en nuestro menú de la izquierda la etiqueta de papelera

Curso de Tablas Dinámicas en Excel 2007

noreply@blogger.com (Aprendiz) — Mon, 15 Mar 2010 10:03:00 +0000

Muchas gracias por suscribirte. En unos minutos recibirás la Lección 1 GRATIS sobre tablas dinámicas. Pero antes...

...para poder recibir esta primera lección, no olvides entrar en tu correo y confirmar la suscripción haciendo clic en un link parecido a este:

-----------------------------------------------------------
CONFIRM BY VISITING THE LINK BELOW:

http://www.aweber.com/z/c/?jybwy035oamq5ralj0xva23c7q8301

Click the link above to give us permission to send you
information. It's fast and easy! If you cannot click the
full URL above, please copy and paste it into your web
browser.

De lo contrario, no podrás recibir la lección. Si no te has suscrito, ignora este mensaje.

Un abrazo

Claudia Castellano

Tabla simple de horas trabajadas

noreply@blogger.com (Aladino) — Mon, 15 Feb 2010 19:39:00 +0000

Hola, quisiera crear una tabla para calcular las horas realizadas, pero no soy capaz de configurarlo ... me explico ...
Tengo varios horarios de inicio o fin, 8/14, 6/14, 14/22 y 22/6
En los siguientes horarios, los resultados deberían salir 6, 8, 8 y 8 respectivamente.
la idea seria ponerlo así:
b2:8 c2:14
b3:6
B2 y c2 serían las horas de inicio y fin y b3 sería la suma de horas
en este caso, el horario seria de 8 a 14, lo mismo para cuando entre de 22 a 6, gracias.

En este caso, lo mejor es que creemos una columna para las horas de entrada y otra para las de salida. Por otro lado el cálculo de la suma de horas es más práctico situarlo en una tercera columna. Hemos reproducido el siguiente ejemplo:

La columna A muestra Horas de Inicio y la columna B las Horas de Fin. La columna C mostrará el cálculo de la diferencia de hojas trabajadas o al suma de las mismas. Para realizar esta tabla:

Seleccionamos todas las celdas en las que escribiremos las horas. En nuestro ejemplo A1:B5.

Aplicamos un formato de hora personalizado ya que deseamos que al escribir la hora no se muestren ni minutos, ni segundos. Para ello, desde la Ficha Inicio - Número Activamos el Iniciador de cuadro de diálogo que es esa pequeña flecha situada en la esquina inferior derecha de este bloque.

Bajo la pestaña Número, seleccionamos la Categoría Personalizada y en Tipo escribimos h en minúscula.

Hacemos clic en Aceptar

Escribimos las horas con formato 8:00, 14:00 etc
Para obtener la suma de horas trabajadas o la diferencia de las mismas en la columna C aplicamos la función RESIDUO de la siguiente manera:

=RESIDUO(B2-A2;1)

Y aplicamos la fórmula al resto de la columna.

La función RESIDUO evitará que Excel muestre un error en el cálculo cuando la fecha de Fin es menor que la de Inicio.

Suscríbete a nuestra lección semanal de nuestro curso de de Excel

Como mostrar comentarios de celdas en otra celda mediante vba en Excel 2007

noreply@blogger.com (Aladino) — Sat, 16 Jan 2010 18:23:00 +0000

Otro consultante nos pregunta cómo puede imprimir en celdas todos los comentarios de una hoja.

Hemos creado este sencillo ejemplo para ilustrar el uso de nuestra macro. En la columna A están las Notas de los alumnos y la persona, al no tener conocimientos de las funciones de Excel, ha optado por crear comentarios estableciendo las calificaciones en función de cada nota. Ahora necesita que se muestren las calificaciones en la columna D. Para ello, utilizaremos una macro que nos permitirá imprimir los comentarios donde queramos.

Hacemos clic con botón derecho sobre la hoja en la que tengamos los comentarios, e este caso, Hoja1 y seleccionamos la opción Ver código
Nos aseguramos de que esté seleccionada la hoja en cuestión en el explorador de proyectos

Escribimos en el editor el siguiente código:

Sub Imprcom() ' Iniciamos la macro con el nombre Imprcom

Dim CelIni As Range ' Indicamos la celda de inicio CelIni como Rango
Dim Cmt As Comment ' Establecemos Cmt como Comentario

With ActiveSheet 'Indicamos que en la hoja activa
Set CelIni = .Range("D2") ' La celda de inicio es la D2
For Each Cmt In .Comments ' Por cada Comentario de la hoja
CelIni.Value = Cmt.Text ' Se muestre el texto del mismo en D2
Set CelIni = CelIni.Offset(1, 0) ' A continuación se mueva una celda hacia abajo
Next Cmt ' Repetir lo mismo por cada comentario

End With 'Finalizamos la instrucción
End Sub 'Finalizamos la macro

Al ejecutar la macro, podemos observar como cada uno de los comentarios aparecen escrito en la columna D a partir de la celda D2.

Si quieres recibir lecciones semanales de nuestro curso de Excel, suscríbete en el menú de la derecha.

Como imprimir comentarios de celdas en Excel 2007

noreply@blogger.com (Aladino) — Sat, 16 Jan 2010 17:36:00 +0000

Nos pregunta un consultante cómo se pueden imprimir los comentarios de una hoja en Excel.

Esto es bien sencillo. Tenemos estos dos comentarios en la Hoja1.

Nos vamos a la ficha Revisar y nos aseguramos de tener marcada la opción Mostrar todos los comentarios.
Seleccionamos la ficha Diseño de página

Hacemos clic en el Iniciador de cuadro de diálogo para acceder a más opciones

Hacemos clic en la pestaña Hoja

En el apartado Comentarios seleccionamos Como en la hoja

Hacemos clic en Vista Preliminar

Observamos como se muestran los comentarios de la hoja que se imprimirán.

Nota: Si estamos utilizando una versión anterior a Excel 2007 podemos acceder desde menú Archivo - Configurar Página - Pestaña Hoja

Si deseas acceder a lecciones de nuestro curso de Excel, no dudes en suscribirte en el menú de la derecha y recibirás lecciones semanales junto con un pdf con los artículos publicados en 2009.

Utilizar la función BUSCARV para obtener datos de otra hoja

noreply@blogger.com (Aladino) — Wed, 06 Jan 2010 22:35:00 +0000

Hola 5inco, Tengo un archivo excel el cual consta de 2 hojas de calculo y mi problema es el siguiente.
La hoja A como la B tienen una columna en comun llamada \"nombre\", estos nombres (alrededor de mil registros) estan en distintos orden, pero son en si los mismos.
En la hoja B al lado de la la columna \"nombre\" tengo una columna llamada \"RUT\" la cual no existe en la hoja A
Me gustaría poner los RUT de la hoja B en la hoja A, ordenados por su respectivo nombre. (en el ejemplo adjunto una hoja con la solución esperada)
Yo pienso que la mejor solución es mediante macro (el cual toy muy perdido), espero que me puedas ayudar y que me guies.

Muchas Gracias

La solución es sencilla. Si utilizamos la función BUSCARV, tendremos el problema resuelto.

Nuestro consultante quiere mostrar los datos de la columna Rut...

...de la hoja B en función del Nombre
La función a utilizar es la siguiente:

=BUSCARV(B3;B!$B$3:$C$13;2;0)

B3 busca el nombre en función del cual queremos obtener el código de ruta
B!$B$3:$C$13 busca el nombre en la tabla de la hoja B
2 indica que dentro de la tabla de la hoja B ha de devolver los resultados de la segunda columna empezando por la izquierda
0 indica que queremos obtener la coincidencia exacta y no la aproximada

Como podemos observar, la función BUSCARV nos devuelve perfectamente los códigos de ruta.

Puedes descargarte el archivo de ejemplo aquí.

Nota: Si te ha gustado esta explicación y quieres recibir más, suscríbete a nuestra explicación semanal mediante el formulario de la derecha.

Como mostrar mensaje de bloqueo de actualización de vínculos en Excel 2007 cuando no aparece.

noreply@blogger.com (Aladino) — Wed, 06 Jan 2010 21:53:00 +0000

Cordial saludo experto:
Sucede que tengo una hoja de Excel que tiene vinculos con otra del mismo libro; yo quiero que no actualice cuando el libro, porque la hoja donde estaban los datos se borró. Pero no me sale la pregunta de que si quiero actualizar datos o no, si no que trata de actualizar automaticamente y como no encuentra los datos me sale un error.
Que puedo hacer para que no actualice de forma automatica?; ya estuve en opciones de Excel avanzadas pero no he conseguido nada.

Por favor ayudeme......

Cuando abrimos un libro con vínculos, Excel suele mostrar un mensaje dándonos la opción de habilitar la actualización automática de los vínculos.

Al hacer clic en Opciones se abrirá el cuadro de diálogo Opciones de seguridad de Microsoft Office.

Desde este cuadro podremos decidir si queremos Habilitar el contenido o por si el contrario deseamos Protegerlo contra contenido desconocido.

Ahora bien, si al abrir el libro ni tan siquiera aparece el mensaje que bloquea la actualización de vínculos, entonces tendremos que habilitarlo de la siguiente manera:

Hacemos clic en el Botón Office
Seleccionamos Opciones de Excel

Seleccionamos la categoria Centro de Confianza
Hacemos clic en Configuración del Centro de Confianza

Si al abrir el libro no aparecía el mensaje de bloqueo de actualización de vínculo puede ser que en el Centro de confianza esté marcada la opción No mostrar nunca información sobre contenido bloqueado.

En ese caso, marcamos la opción Mostrar la barra de mensajes en todas las aplicaciones cuando se haya bloqueado el contenido.

Y hacemos clic en Aceptar

Tendremos que cerrar el libro y volverlo a abrir para que se apliquen los cambios y aparezca el mensaje de bloqueo de actualización de vínculos.

Si deseas recibir más explicaciones sobre Excel como esta, suscríbete a nuestra Explicación semanal en el formulario de la derecha.

Buscar datos que cumplan dos criterios en Excel 2007

noreply@blogger.com (Aladino) — Wed, 06 Jan 2010 20:09:00 +0000

Hola,

Soy incapaz de conseguir la formula que deseo. Tengo que mostrar en la hoja HORARIO en la columna B, la situación que esta, segun la hoja CONTROL donde me indica la fecha y el estado segun el oficial.

He probado con varias de COINCIDIR E INDICE, pero como tiene que obtener el resultado segun la fecha y el nombre del oficial, y en la hoja CONTROL, no existen todas las fechas solo algunas, me da errores y ya no se por donde ir. Desearia que alguien me echara una mano para conseguir volcar la situacion de cada oficial si existe y sino existe dejar el campo en blanco.

Gracias

En la tabla Control tenemos la Situación de cada Oficial en cada Fecha

Lo que queremos obtener es en qué situación estaba cada oficial en una fecha determinada. Nos enfrentamos a una búsqueda de datos que cumpla con dos criterios. Puesto que BUSCARV por sí misma no permite buscar por más de un criterio, utilizaremos una fórmula matricial que combine las funciones INDICE y COINCIDIR.

En este caso, utilizamos la función Indice para que nos indique en qué fila y columna se encuentra la situación de cada Oficial. Por ello, primero indicamos que queremos buscar en la columna Situación de la hoja CONTROL.

En el segundo argumento, que es el que nos debe indicar la fila, utilizaremos la función COINCIDIR para que nos devuelva la fila que coincida con los criterios del Oficial y de la Fecha utilizando una matriz de las columnas Oficial y Fecha.

{=INDICE(CONTROL!$C$2:$C$14;COINCIDIR(1;(CONTROL!$A$2:$A$14=HORARIO!B$1)*(CONTROL!$B$2:$B$14=HORARIO!$A2);0));"")}

Aplicamos la fórmula matricial pulsando CTRL+MAYUSC+INTRO y obtenemos los resultados que necesitábamos.

El problema es que cuando una fecha no existe, la celda muestra un error de valor no disponible.

Para evitar esto, podemos utilizar la función SI.ERROR al inicio de la fórmula de la siguiente manera:

{=SI.ERROR(INDICE(CONTROL!$C$2:$C$14;COINCIDIR(1;(CONTROL!$A$2:$A$14=HORARIO!B$1)*(CONTROL!$B$2:$B$14=HORARIO!$A2);0));"")}

Al aplicar la fórmula de nuevo, podemos observar como el error #N/A desaparece.

Puedes descargarte el archivo de ejemplo aquí.

Si deseas recibir nuestra explicación semanal sobre Excel, no dudes en rellenar el formulario de suscripción que hay arriba a la derecha con tu nombre y mail.

Mostrar mensaje de información de un control commandbutton en una hoja de Excel

noreply@blogger.com (Aladino) — Wed, 06 Jan 2010 17:13:00 +0000

Nuestro consultante nos envía la siguiente consulta. Tengo una hoja de excel en la cual cree un commandbutton, lo que quiero saber, es el codigo en vba que me permita que al pasar el mouse por encima de boton me muestre la informacion de guia referente al botón.

Lo que quiere nuestro consultante en este caso es que al pasar el ratón por encima del botón muestre como texto "Formulario". Si el botón estuviese dentro de un formulario, la solución sería más sencilla, pero al tratarse de un botón creado en una hoja de manera independiente, la tarea se complica ya que hay que utilizar código que llame a la librería de sistema.

Resultado a obtener:

Pasos para crear la solución:

Creamos un botón mediante un control ActiveX desde la Ficha Programador - Insertar

Dibujamos nuestro botón en la hoja
Seleccionamos el botón

En el grupo Controles, seleccionamos la opción Propiedades

Nota: Recuerda tener el Modo Diseño activado

Establecemos el nombre interno del botón como CommandButton1

Aprovechamos a cambiar en Caption el texto del botón. Escribimos Aceptar

Ya tenemos nuestro botón creado.
Accedemos al Editor VBA mediante Alt+F11

Insertamos un módulo desde menú Insertar - Módulo

Hacemos doble clic en Módulo1 y escribimos el siguiente código:

'Declaramos dos funciones de sistema que llaman a la librería del usuario de Windows

Option Explicit

Declare Function GetSystemMetrics Lib "user32" ( _

ByVal nIndex As Long) As Long

Declare Function GetSysColor Lib "user32" ( _

ByVal nIndex As Long) As Long

'Declaramos una segunda función que nos permite crear la etiqueta

Public Function Info(objHostOLE As Object, _

sTTLText As String) As Boolean

Dim objToolTipLbl As OLEObject

Dim objOLE As OLEObject

Const SM_CXSCREEN = 0

Const COLOR_INFOTEXT = 23

Const COLOR_INFOBK = 24

Const COLOR_WINDOWFRAME = 6

Application.ScreenUpdating = False 'la pantalla no se actualiza solo mientras se crea la etiqueta y se le da formato

For Each objOLE In ActiveSheet.OLEObjects

If objOLE.Name = "TTL" Then objOLE.Delete 'Solo puede existir una cada vez

Next objOLE

'creamos el control de la etiqueta...

Set objToolTipLbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1")

'...y le damos formato para que parezca una ventana de información

With objToolTipLbl

.Top = objHostOLE.Top + objHostOLE.Height - 10

.Left = objHostOLE.Left + objHostOLE.Width - 10

.Object.Caption = sTTLText

.Object.Font.Size = 8

.Object.BackColor = GetSysColor(COLOR_INFOBK)

.Object.BackStyle = 1

.Object.BorderColor = GetSysColor(COLOR_WINDOWFRAME)

.Object.BorderStyle = 1

.Object.ForeColor = GetSysColor(COLOR_INFOTEXT)

.Object.TextAlign = 1

.Object.AutoSize = False

.Width = GetSystemMetrics(SM_CXSCREEN)

.Object.AutoSize = True

.Width = .Width + 2

.Height = .Height + 2

.Name = "TTL"

End With

DoEvents

Application.ScreenUpdating = True

'Establecemos que la etiqueta desaparezca después de 5 segundos de haber retirado el ratón...

' ...del botón. Puedes establecer menos segundos

Application.OnTime Now() + TimeValue("00:00:05"), "DeleteToolTipLabels"

End Function

'Por último, creamos otro procedimiento que nos permite borrar el texto de la etiqueta

Public Sub DeleteToolTipLabels()

Dim objToolTipLbl As OLEObject

For Each objToolTipLbl In ActiveSheet.OLEObjects

If objToolTipLbl.Name = "TTL" Then objToolTipLbl.Delete

Next objToolTipLbl

End Sub

Hacemos doble clic en la hoja en la que hemos creado el botón. En este caso es la Hoja1.

Escribimos el siguiente código.

Sub CommandButton1_MouseMove(ByVal Button As Integer, _

ByVal Shift As Integer, _

ByVal X As Single, _

ByVal Y As Single)

Dim objTTL As OLEObject

Dim fTTL As Boolean

For Each objTTL In ActiveSheet.OLEObjects

fTTL = objTTL.Name = "TTL"

Next objTTL

If Not fTTL Then

Info CommandButton1, "Formulario"

End If

End Sub

Nota1: Si nombras el botón de otra manera tendrás que sustituir el nombre CommanButton1 delante de _MouseMove

Nota2: En las últimas líneas de código recuerda que Info es el nombre de la función que creamos en el módulo, CommandButton1 el nombre del botón que hemos creado y el texto entre comillas es el que queremos que aparezca cuando posicionemos el ratón sobre el botón.

Para que funcione hay que desactivar el Modo Diseño

Si quieres recibir una lección semanal, no dudes en suscribirte a nuestra explicación semanal en el formulario de la derecha.

Descargar archivo de ejemplo aquí. Recuerda que la versión del archivo es 2007.

Seleccionar aleatoriamente registros

noreply@blogger.com (Aprendiz) — Tue, 05 Jan 2010 20:43:00 +0000

Nuestra consultante tiene una serie de registros introducidos en un orden determinado y necesita escoger un número aleatorio de los mismos. En este caso, vamos a escoger aleatoriamente 11 registros.

Insertamos una columna auxiliar a la derecha de la columna A.

Para ello, hacemos clic con botón derecho sobre la columna B.

Elegimos Insertar
En la primera celda que no sea encabezado introducimos la función =aleatorio()

Y arrastramos al resto de la columna a los 11 primeros registros

Seleccionamos la columna y ordenamos de manera personalizada

Marcamos la opción Ampliar la selección para que mantenga el Código asignado a cada cliente.

Hacemos clic en Ordenar.

En Ordenar por seleccionamos Columna B, que es la columna auxiliar en la que hemos aplicado la función Aleatorio
Ordenar según Valores
Criterio de ordenación De menor a mayor
Puesto que nuestra tabla tiene encabezados hemos de marcar la casilla Mis datos tienen encabezados

Hacemos clic en Aceptar

Eliminamos la columna B

Observamos que se han escogido los primeros 11 registros aleatoriamente

Si deseas recibir una explicación semanal gratuita sobre Excel, suscríbete en el menú de la derecha.