86 Trucos Para Excel

TRUCOS DE EXCEL

1 Para rellenar una Base de Datos 302 Para separar un sector circular en un grfico 313 Para incluir una nueva serie de datos en un grfico 324 Para seleccionar una tabla completa 335 Para poder ver las frmulas de una hoja 346 Auto_Open() y Auto_Close() 357 Personal.xls 368 Saludo al arrancar 379 38

10 Proteccin de una hoja 3911 Propiedad EnableSelection 4012 EnableSelection = xlUnlockedCells 4113 Opciones de Ordenacin 4214 Criterios utilizados para filtrar una base de datos 4315 Varias lneas en una celda 4416 Mover celdas entre hojas 4517 Insertar filas o columnas rpidamente 4618 Copiar en Excel XP arrastrando con Ctrl 4719 Edicin, Rellenar, Series 4820 4921 Clculo automtico en la barra de estado 5022 Algunas abreviaturas de teclado 5123 Al pulsar Intro se desplaza el cursor hacia abajo, pero se puede c 5224 F11 5325 Proteger celdas 5426 Ocultar la formula en una celda 5527 Copiar una hoja 5628 Insertar Comentarios 5729 Texto con diversos formatos en una misma celda 58

Nombrar Hoja desde una celda

Algunas teclas de navegacin

TRUCOS DE EXCEL

Trabajar simultneamente en varias hojas 59Escribir en varias celdas simultneamente 60Ver todas las frmulas de una hoja 61Borrar 62La fuente por defecto en Excel es la Arial 10 pero se puede cambia 63

64Puede trabajar con Estilos 65Libros que se abren al iniciar Excel 66La funcin =CELDA 67Exportar un mdulo 68Importar un mdulo 69La clusula Private 70Inspeccin rpida de variables 71Modificar el valor de una variable en tiempo de ejecucin 72Ventana Inmediato 73Debug.Print 74Propiedad ListFillRange del ComboBox 75Propiedad LinKedCell del ComboBox 76Propiedad ListIndex del ComboBox 77La funcin =TEXTO 78Curiosidades de alguna funciones con valor propio 79Cambiar el nombre de un Mludo 80Nombre de usuario: UserName 81Extensiones de los nombres de macro 82Exit Sub, Exit Function 83Macro que pregunta si se desea salir de la aplicacin 84Llamada a un procedimiento 85Proteger con contrasea las macros 86Convertir una funcin en un Complemento

SELECCIONE EL TRUCO QUE QUIERA VISUALIZAR

Gracias a jbj99 y a KL que en Exceluciones pulieron y mejoraron el fichero

Plantillas *.xlt

TRUCOS DE EXCEL

Hacer referencia a un RANGOPara localizar la ltima celda de una listaAbrir un libro existente:Borrar un registro de una tabla que contenga un dato concretoCreacin de una Frmula personalizadaMacro que cierra ExcelMacro que elimina las barras de desplazamientoMacro que protege el libro y la hojaLa funcin =EXTRAE y la funcin =ENCONTRAR

Macro que borra los ceros de un rangoMacro que reemplaza una palabra por otraCalculo del NIFFuncin que calcula la fila que ocupa cierto valor en una tablaPara poner el nombre del fichero en una celdaPara poner el nombre de la hoja en una celdaFuncin que muestra la frmula de una celdaDeterminacin de si un nmero es primo o no es primo

FactorialInicializar una matrizEvitar los movimientos de pantalla mientras se ejecuta una macroFormula referida siempre a un mismo rangoWhile...WendContar cuantas veces se repite un mismo caracter en un textoManejo de RangosOcultar HojasDeterminar la fila hasta la que llega un rango

La funcin =RESIDUO permite calcular la parte Fraccionario

Activar o Desactivar el empleo de la funcin IMPORTARDATOSDINAMICOS

0123456789:;=30.000

15 Varias lneas en una celdaPersonalizar

16 Mover celdas entre hojasPersonalizar

17 Insertar filas o columnas rpidamente

Personalizar

En una hoja de Excel que ya tengas rellena, prueba a hacer esto:Vete al principio de lo que tengas escrito

18 Copiar en Excel XP arrastrando con CtrlPersonalizar

Si no se emplea el Ctrl la celda no se copia, se mueve.19 Edicin, Rellenar, Series

Personalizar

20

Teclado

Ctrl + Intro Ir a A1Fin - Flecha abajo ltima celda de un rango.Ctrl + Flecha abajo ltima celda de un rango.Ctrl + Fin Celda inferior derecha al rea activaAlt + AvPg Pgina a la derecha

Permite desplazamientos ms rpidosCtrl + AvPg Avanza entre las hojas de un libro

21 Clculo automtico en la barra de estado

Personalizar

Base de Datos

Un criterio est formado al menos por dos celdas. La primera es un nombre de campo y la segunda que estar debajo es el criterio propiamente dicho.

Cuando usamos varios criterios estos se relacionan entre si mediante concatenadores lgicos Y u O. Por ejemplo, supongamos que deseamos filtrar de una base de datos los registros correspondientes a trabajadores que cumplan que su salario esta comprendido entre 30.000 y 40.000 euros, o bien su edad es de 30 aos y se llaman Maria. En ese caso el criterio esta formado por mltiples celdas, de forma que los criterios que estan en la misma fila se han de cumplir ambos (operador Y), y si estn en fila distinta se han de cumplir unos u otros (operador O).

Para escribir varias lneas en la misma celda puedes escribir lo que quieras y para abrir un espacio hacia abajo en la misma celda pulsar Alt+Enter.

Seleccione el rango a mover. Llvelo hasta la pestaa de la hoja de destino, presione ALT y mantenga presionado hasta que deje el rango en la parte de la hoja de destino que desee.

Pon el ratn sobre el control de relleno (es el puntito negro que tiene el cursor abajo a la derecha). Sabes que ests encima del control de relleno, porque la cruz blanca que es normalmente el ratn se cambia a color negroAhora pulsa la tecla de maysculas (no la de bloq. mays) y sin soltarla pulsa el botn izquierdo del ratn y arrstralo hacia abajo o hacia la derecha, vers que se insertan filas o columnas.

Se puede copiar una celda arrastrando con Ctrl desde alguno de los 4 lados del borde de la celda activa.

Poner el primer nmero y arrastrar el controlador de relleno con la tecla Ctrl pulsada. Esto nos dar una serie con incrementos unitarios.Algunas teclas de navegacin

Shift + Barra de desplazamiento vertical u horizontal

Para ir al extremo superior de un bloque de datos, haga doble clic en el extremo superior de la celda seleccionada. Con los dems bloques puede ir a los dems extremos del bloque. Y si lo hace mientras presiona la tecla Shift podr seleccionar rangos.

Al seleccionar un rango de nmeros vemos su suma en la barra de estado (abajo). Pulsando sobre ella con el botn derecho del ratn podemos cambiar la suma por un promedio, el mximo, el mnimo o contar los elementos seleccionados en el rango.

www.excelavanzado.com 11/21

n TRUCO TIPO

21

Personalizar

22 Algunas abreviaturas de teclado

Teclado

23 Al pulsar Intro se desplaza el cursor hacia abajo, pero se puede cambiar TecladoPuede cambiarlo en Herramientas, Opciones, Modificar, Mover seleccin despus de ENTRAR.

24 F11GrficosPulsando F11 sobre una serie de datos se construye un grfico completo y de forma automtica.

25 Proteger celdas

Personalizar

26 Ocultar la formula en una celdaPersonalizar

27 Copiar una hoja

Personalizar

Para copiar una hoja completa dispone de dos sistemas:

28 Insertar Comentarios

Personalizar

29 Texto con diversos formatos en una misma celda

Personalizar

Se pueden dar distintos formatos a los caracteres o palabras de una misma celda.

Ctrl + C Copiar Ctrl + V Pegar Ctrl + X Cortar Ctrl + Z Deshacer

Con Formato, Celda, Protege, quitar la casilla de verificacin en Bloqueada se consigue que en estas celdas se pueda escribir despus de ejecutar Herramientas, Proteger, Proteger hoja.Con TAB se puede saltar de una a otra celda de las desbloqueadas. Y con Maysculas + TAB se salta hacia atrs.

Con Formato, Celda, Proteger, Oculta se marcan las celdas cuyas frmulas o contenido visto en la lnea de edicin se desee ocultar. Despus se ha de proteger la hoja, con Herramientas, Proteger, Proteger hoja.

El primero y ms fcil es sealar con el ratn la pestaa de la hoja a copiar, pulsar Ctrl y sin soltar arrastrar la pestaa con el ratn. Al soltar se habr duplicado completamente la hoja. Si la hoja a copiar se llamaba 'Hoja 1', la nueva hoja se llamar 'Hoja 1 (2)'Es segundo mtodo consiste en situar el ratn en el cuadradito que queda como interseccin del indicador de filas y columnas. Esto selecciona la hoja completamente. Seguidamente se copia al portapapeles, por ejemplo con Ctrl + C. Se inserta una hoja nueva y pulsando sobre ella en el citado cuadradito se pega el contenido del portapapeles, por ejemplo con Ctrl + V.

Se pueden insertar comentarios en las celdas. Para ello, pulse con el botn derecho del ratn y elija 'Insertar Comentario'. Puede verlos completamente, solo la indicacin de que la celta tiene un comentario, o no verlos en absoluto segn elija una opcin u otra en Herramientas, Opciones, Ver, Comentarios.

Por ejemplo: puede poner en una celda la siguiente expresin: Volumen (m3)


n TRUCO TIPO

29

Vase el distinto color de la fuente y el 3 como superndice. Personalizar

30 Trabajar simultneamente en varias hojas

Personalizar

31 Escribir en varias celdas simultneamentePersonalizar

32 Ver todas las frmulas de una hojaPersonalizar

Tambin se puede conseguir mediante: Herramientas, Opciones, Ver, Frmulas.33 Borrar

Personalizar

34 La fuente por defecto en Excel es la Arial 10 pero se puede cambiar

Personalizar

35

Personalizar

C:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Excel\INICIARo bien en:C:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Excel\XLSTART

36 Puede trabajar con Estilos

Personalizar

Volumen (m3)Esto se consigue sealando en la barra de frmulas el carcter o caracteres que se quieren cambiar y aplicndoles el formato deseado.

Antes de escribir texto, formulas, dar formato, etc.. en varias hojas simultneamente ha de sealarlas primero.Si son un conjunto de hojas correlativas, seale la primera haciendo clic con el ratn sobre la pestaa que indica el nombre de la primera y haga Maysculas + clic en la ltima.Si las hojas no son consecutivas selecciones la primera con un clic y las restantes con un Ctrl + clic en cada una.Cuando haya terminado de introducir la informacin y formatos comunes haga clic en cualquier pestaa y as se desactivar el modo de trabajo conjunto.

Seale las celdas donde quiere escribir una misma expresin. Incluso si son de rangos separados. Escriba dicha expresin. Y valide su introduccin con Ctrl + Intro.

Se consigue pulsando Alt + (esto es, la tecla Alt y simultneamente la tecla de 1 que est a la izquierda del nmero 1). Para volver a la situacin anterior volver a pulsar.

Con el comando Edicin, Borrar se puede elegir entre Todo, Formatos, Contenido (Supr) o Comentarios.Para borrar un rango primero seleccione el rango y luego presione Shift mientras arrastra con el ratn el indicador de relleno hacia el extremo superior izquierdo del rango. Si no presiona Shift conseguir borrar el contenido pero no los formatos.

Se puede cambiar para todos los libros nuevos mediante: Herramientas, Opciones, General y elegir la Fuente Estndar y el tamao.Pero tambin se puede cambiar la fuente slo en el libro activo de la siguiente forma. Activar el libro en el que se quiere cambiar la fuente por defecto. Ejecute Formato, Estilo y dentro del formato de estilo Normal elegir Modificar, Fuente y proceder a cambiar la fuente. Finalmente Aceptar.

Plantillas *.xltLas plantillas permiten predefinir formatos, estilos, textos, frmulas, barras de herramientas personalizadas e incluso macros, que se utilizarn en todos los nuevos libros abiertos (si la plantilla se llama libro.xlt) u hojas nuevas insertadas (si la plantilla se llama hoja.xlt). Tambin pueden existir otras plantillas para diferentes usos, por ejemplo, para predefinir una factura, cierto informe mensual, un balance, etc.

La plantilla de libro predeterminada (libro.xlt) y la plantilla de hoja predeterminada (hoja.xlt) se han de grabar como plantilla en el directorio INICIAR que para la versin XP suele estar en:

Tanto las plantillas que tenga en la carpeta de inicio predeterminada de Excel como las que haya definido en la carpeta de plantillas (C:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Plantillas) las tendr disponibles cuando haga Archivo, Nuevo y salga el Panel de Tareas y en el elija Plantillas Generales.

En Formato, Estilo puede modificar el estilo normal o crear nuevos estilos, pero slo estarn disponibles en el Libro Activo.Si se desea que los estilos estn disponibles de forma permanente sigua los siguientes pasos: Abra el libro cuyos Estilos quiere guardar permanentemente. Abra un libro nuevo y combine los Estilos en ste. Ejecute Archivo, Guardar como, elija como tipo Plantilla (*.xlt). Escriba como nombre de fichero Libro.xlt o Hoja.xlt y gurdelo en la carpeta de inicio de Excel (C:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Excel\INICIAR). De esta forma cada vez que abra un libro u hoja nuevos se dispondr de estos Estilos.


n TRUCO TIPO

36

Personalizar

37 Libros que se abren al iniciar Excel

Personalizar

Los libros que coloque en la carpeta de inicio de Excel:C:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Excel\INICIARo bien enC:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Excel\XLSTARTSe abrirn al iniciar Excel.

38 La funcin =CELDA

Funcin

La funcin CELDA es la tpica funcin que sirve para varias cosas segn que argumentos se utilicen.

Si se usa con "nombrearchivo"Err:502

Otro argumento interesante es "contenido".

39 Exportar un mdulo

Macros

1. Pase al editor de visual basic y active el mdulo a exportar.

40 Importar un mdulo

Macros

1. Active el editor Visual Basic.

41 La clusula Private

Macros

42 Inspeccin rpida de variablesMacros

43 Modificar el valor de una variable en tiempo de ejecucin

Macros

44 Ventana InmediatoMacros

45 Debug.Print

Macros

Puede convertir el estilo de una celda en un tipo de estilo personalizado. Sitese en la celda patrn y elija Formato, Estilo y cambie el nombre por uno nuevo de su eleccin.Puede transferir los estilos de un libro a otro mediante Formato, Estilo, Combinar. Para ello abra los dos ficheros (o ms) y ejecute los pasos sealados desde el fichero hacia el que quiere llevar los nuevos estilos.

Tambin puede definir una carpeta de inicio de Excel en Herramientas, Opciones, General, "Al inicio, abrir todos los libros en:"

proporciona el nombre y ruta del archivo (siempre que est grabado). En la ayuda hay un error y en lugar de poner "nombrearchivo" pone "archivo".

=CELDA("contenido";A3) Proporciona el contenido de la celda A3.

2. Seleccione Archivo/ Exportar archivo. Aparece un cuadro de dilogo.3. En cuadro de edicin Nombre de Archivo, teclee el nombre para el archivo donde se guardar el mdulo, por ejemplo "General.Bas", observe que .BAS es la extensin de estos archivos.

4. Pulse sobre el botn Guardar.

2. Seleccione Archivo/ Importar Archivo. Aparece un cuadro de dilogo.3. Seleccione en la lista Buscar en: la carpeta donde tiene ubicado el archivo a importar.4. Una vez localizada la carpeta, seleccione el archivo a importar (General.Bas en el ejemplo) y pulse sobre Abrir.

Puede anteponer la clusula private a todos los procedimientos y funciones que sean llamados slo desde el mismo mdulo, es una forma de ahorrar memoria y hacer que el programa corra un poco ms rpido. Si necesita llamar un procedimiento o funcin desde otro mdulo, nunca debe precederlo por la clusula private, recuerde que esta clusula restringe el mbito de utilizacin de un procedimiento a su propio mdulo.

Cuando ejecuta un programa paso a paso, si sita el puntero de ratn sobre una variable, se muestra el valor de la misma.

A veces resulta interesante cambiar el valor de alguna variable cuando se est ejecutando el programa, para ver que ocurre si coge determinados valores, para terminar un bucle, etc.Para ello agregue a la ventana de inspeccin la variable que desee cambiar, cambie su valor sobre la propia ventana de inspeccin y contine la ejecucin del programa.

Puedes inspeccionar variables desde esta ventana anteponiendo la palabra print antes de la variable, o el signo ?.

Esta expresin permite efectuar la depuracin del programa de forma ms cmoda. Se introduce en un programa antecediendo a una variable que se desea comprobar en la ventana Inmediato. De esta forma conseguiremos ver todos los valores que toma la variable cada vez que el programa pasa por este comando. Se ven en la Ventana Inmediato [Ctr+G].


n TRUCO TIPO

45

Ejemplo:

Macros

End SubDespus de efectuada la depuracin se quita la lnea que contiene el comando Debug.Print.

46 Propiedad ListFillRange del ComboBoxMacros

47 Propiedad LinKedCell del ComboBox

Macros

En esta propiedad debe especificar en que celda debe copiarse el elemento seleccionado de la lista.

48 Propiedad ListIndex del ComboBox

Macros

49 La funcin =TEXTO

Funcinvalor: es el nmero que queremos convertir a textoformato: es el formato que queremos darle. Es un argumento optativo

Pruebe =TEXTO(;"43")Pero si prueba =TEXTO("43") obtendr un error

50 Curiosidades de alguna funciones con valor propio

Funcin

Las funciones que he visto que tienen valor propio son las siguientes:=PRECIO #N/A=AMORTIZ.LIN #N/A=CUPON.DIAS #N/A=DURACION #N/A=VNA.NO.PER #N/A=DIAS.LAB #N/A

Sub Dos_a_la_diez() Dim i As Integer Dim t As Integer t = 1 For i = 1 To 10 t = t * 2 Debug.Print "Dos a la " & i & " = " & t Next MsgBox t

Con esta propiedad deberemos definir los elementos que debe mostrar la lista, debe especificarse el rango que contiene los elementos a mostrar, el rango debe ser una columna (o dos , o tres, etc.).

Cuidado con esta propiedad, tenga en cuenta que los elementos de la lista son tratados como datos de tipo String aunque contenga nmeros o fechas, por lo que en estos casos, a veces ser necesario aplicar funciones de conversin de datos antes que el dato se copie en la hoja. Por ejemplo, si alguna vez construye una lista con nmeros ver que el dato seleccionado se alinea a la derecha, si son fechas, no se muestra con el formato correspondiente.

Mediante esta propiedad podremos saber que elemento de la lista es el seleccionado por su nmero de tipo String aunque contenga nmeros o fechas, por lo que en estos casos, a veces ser necesario aplicar 1, etc. Si no hay ningn elemento seleccionado valdr -1. Tenga en cuenta que esta propiedad slo est disponible en tiempo de ejecucin, es decir la podremos leer mientras est funcionando el programa, no se puede establecer en modo diseo, observe que no aparece en la ventana propiedades del cuadro combinado.

Esta funcin permite convertir un valor numrico en un texto. Su funcin contraria es =VALOR.Al escribir un nmero en una celda queda alineado a la derecha, si se escribe como texto (anteponindole una comilla simple) quedar alineado a la izquierda. Ejemplo: '54La funcin TEXTO tiene dos argumentos que segn la ayuda son: =TEXTO(valor;formato)

Pero la ayuda no se corresponde con la realidad de la funcin, ya que en la prctica esta funcin tiene los argumentos cambiados de orden.

Algunos nombres de funcin tienen valor propio si se escriben un una celda y sin argumentos. O al menos eso es lo que he podido comprobar en la versin XP.Ponga en una celda cualquiera +PRECIO y al pulsar intro ver que aparece un valor. A mi me da -1965686714.

y muchas ms. Por lo que veo, son las que aparece al ampliar las funciones al instalar las complementarias, conHerramientas, Complementos, Herramientas para Anlisis.


n TRUCO TIPO51 Cambiar el nombre de un Mludo

Macros

52 Nombre de usuario: UserName

Macros

'' Macro1 Macro' Macro grabada el 06/08/2004 por Adolfo''

End SubAhora vamos a crear la funcin =Usuario siguiendo el procedimiento inverso.

End FunctionPuede probarla. La encontrar en la categora de Funciones Definidas por el usuario.

53 Extensiones de los nombres de macroMacros

54 Exit Sub, Exit Function MacrosSe usan eventualmente para salir de un procedimiento o funcin.

55 Macro que pregunta si se desea salir de la aplicacin

Macros

End Sub56 Llamada a un procedimiento

Macros

End Sub

End SubPara llamar a un procedimiento de otro mdulo

Para llamar a un procedimiento de otro libro

Al insertar mdulos en el Editor de Visual Basic, se utilizan los nombres: Mdulo 1, Mdulo 2, etc. Podemos cambiar el nombre el un mdulo en sus propiedades (F4). La nica propiedad de un Mdulo es 'name'.

Vamos a crear una funcin que proporcione el nombre de usuario. Primero utilizando la Grabadora de Macros. Haga lo siguiente. Active la Grabadora y luego seleccione Herramientas, Opciones, pestaa General. Realice un cambio en el nombre de usuario. Cerrar la ventana de Opciones pulsando en Aceptar. Y detener la Grabadora. La macro obtenida ser la siguiente:

Sub Macro1()

With Application .UserName = "Adolfo Aparicio" .StandardFont = "Arial" .StandardFontSize = "10" .DefaultFilePath = "C:\Documents and Settings\Adolfo\Mis documentos" .EnableSound = False .RollZoom = False End With

Funcition Usuario() Usuario = Application.UserName

- Mdulos de clase. Extensin .cls.- Formularios. Extensin .frm.- Mdulos estandar. Extensin .bas.

Sub SalirAplic() If MsgBox("Quiere salir de la aplicacin", vbQuestion + vbYesNo, "Ultima pregunta") = vbYes Then

Application.Quit End If

[Call] NombreProc [(lista de argumentos)]La palabra clave Call es opcional para llamar a un procedimiento, salvo que existan argumentos, en cuyo caso es obligatoria. Los argumentos deben ir entre parntesis y pueden ser valores o variables.

Sub Suma(a As Byte, b As Byte) MsgBox "La suma es " & a + b

Sub sumar() Call Suma(3, 4)

NombreDelMdulo.NombreDelProcedimientoEjemplo: ThisWorkbook.SalirAplic


n TRUCO TIPO

56

Macros

Al ejecutar este comando, el libro Informe.xls debe estar abierto.57 Proteger con contrasea las macros

MacrosAlt + F11

58 Convertir una funcin en un Complemento

Macros

Los Complementos disponibles se ven haciendo: Herramientas => Complementos.

C:\Documents and Settings\Adolfo\Datos de programa\Microsoft\AddIns\

59 Hacer referencia a un RANGO

Edicin

Sintaxis para hacer referencia a un Rango:Si el rango est en la misma Hoja:RangoSi est en otra Hoja del mismo Libro:

Si est en una Hoja de otro Libro, y el Libro se encuentra en la misma carpeta que el actual:

Si est en una Hoja de otro Libro, y el Libro se encuentra en diferente carpeta que el actual:

Si el rango est en otro ordenador conectado al nuestro por una Intranet:

Si el rango est en otro ordenador conectado al nuestro por Intrernet:

=BUSCARV(200;'http://www.fcjs.urjc.es/finan/[busquedas.xls]Hoja1'!tab1;2)Si todo va bien, y tienes acceso a Internet vers como respuesta la letra B.

60 Para localizar la ltima celda de una lista

Macros

End Sub61 Abrir un libro existente:

Macros

End Sub Activar un libro ya abierto:

End Sub Crear un libro nuevo:

End Sub 62 Borrar un registro de una tabla que contenga un dato concreto

Macros

Application.Run "NombreDelLibro!NombreDelMdulo.NombreDelProcedimiento"Ejemplo: Application.Run "Informe.xls!ThisWorkbook.SalirAplic.xls"

Herramientas => Propiedades de VBAProject => Proteccin => Bloquear proyecto para visualizacin => Contrasea

Podemos hacer que una funcin creada por el usuario este disponible en todos los Libros. Para ello, hemos de convertir el Libro que contiene la funcin en un Complemento.

Excel carga los complementos guardados en el directorio donde tengis instalado MsOffice y luego en una carpeta que vara con la versin que tengas instalada. Para XP puede ser:

Para convertir el libro que contiene nuestra Funcin en un Complemento, bastara ir al Men Archivo/Guardar como y elegir el formato Complemento de Microsoft Excel (*.xla). Si lo guardis en el directorio que tiene tu versin asignado estar siempre disponible.Un aspecto a tener en cuenta es que los Complementos no son editables, es decir no podrs modificar o aadir mas Funciones. Por eso es recomendable guardar primero el Libro en formato *.xls y luego como *.xla.

'Nombre de Hoja'!Rango

'[Nombre de Libro.xls]Nombre de Hoja'!Rango

'c:\Carpeta\SubCarpeta\[Nombre de Libro.xls]Nombre de Hoja'!Rango

'\\Servidor\Carpeta\[Nombre de Libro.xls]Nombre de Hoja'!Rango

'http://www.servidor.com/Carpeta/[Libro.xls]Hoja'!RangoLos apstrofes (') son necesarios si existen espacios.Ejemplo: Prueba a poner esto en una celda de una hoja de Excel cualquiera:

Sub Final() While ActiveCell.Value "" ActiveCell.Offset(1, 0).Select Wend

Sub AbrirLibro() Workbooks.Open ("C:\Mis documentos\Ejemplo.xls")

Sub ACtivarLibro() Workbooks("Ejemplo.xls").Activate

Sub NuevoLibro() Workbooks.Add


n TRUCO TIPO62

Macros

End Sub63 Creacin de una Frmula personalizada

Macros

End Function

End Sub64 Macro que cierra Excel

MacrosSe puede asociar a un botn o a un icono.

End Sub65 Macro que elimina las barras de desplazamiento

Macros

End Sub66 Macro que protege el libro y la hoja

Macros

End Sub67

Funcin

68

Funcin

=A1-ENTERO(A1)=RESIDUO(A1;1)

69 Macro que borra los ceros de un rango

Macros

End SubSi slo se quieren ocultar los ceros se ha de poner formato de celda personalizado de #.###

Sub BorrarFilas() While ActiveCell.Value "" If ActiveCell.Value "Cadiz" Then ActiveCell.Offset(1, 0).Range("A1").Select Else Selection.EntireRow.Delete End If Wend

Clculo del trmino amortizativo de un prstamo tipo francs (de pagos constantes). Similar a la frmula =PAGO pero en este caso usando tipo nominal y con fraccionamiento.Function Termino(Principal@, Aos As Byte, tipo_nominal!, fraccionamiento As Byte) Dim im! 'Tipo efectivo del subperiodo im = tipo_nominal / fraccionamiento / 100 Termino = Principal * im / (1 - (1 + im) ^ (-Aos * fraccionamiento))

Pongamos un ejemplo para un principal de 100.000 , 10 aos, tipo nominal anual del 6%, con fraccionamiento mensual. En este caso, al ser el fraccionamiento mensual el trmino obtenido es la mensualidad.Sub ejemplo()

Sub Auto_Close() Application.Quit

Sub Desbarra() With ActiveWindow .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False End With

Sub Blindaje() ActiveWorkbook.Protect Password:="1234" Sheets("Hoja1").Protect Password:="1234"

La funcin =EXTRAE y la funcin =ENCONTRARSi en B6 pones el nombre de una persona, por ejemplo: "Andrs Suarez Oliveira" la siguiente funcin obtiene las tres iniciales (ASO).=IZQUIERDA(B6;1)&+EXTRAE(B6;ENCONTRAR(" ";B6)+1;1)&EXTRAE(B6;+ENCONTRAR(" ";B6;ENCONTRAR(" ";B6)+1)+1;1)La funcin =RESIDUO permite calcular la parte FraccionarioEn Excel disponemos de =ENTERO que calcula la parte entera, pero no disponemos de FRAC que calcularia la parte fraccionaria. Para calcular la parte Fraccionaria deberamos usar uno de estos dos mtodos:

Sub BorrarCeros() For Each Celda In Range("C11:G24") If Celda.Value = 0 Then Celda.ClearContents Next


n TRUCO TIPO70 Macro que reemplaza una palabra por otra

Macros

End SubEn este caso reemplazamos por "", lo que supone borrar la palabra buscada.

71 Calculo del NIF

MacrosEsta funcin calcula el NIF (Nmero de Identificacin Fiscal) utilizado en Espaa.

End Function72 Funcin que calcula la fila que ocupa cierto valor en una tabla

Macros

End Function73 Para poner el nombre del fichero en una celda

Funcin

=EXTRAE(CELDA("NOMBREARCHIVO";A1);ENCONTRAR("[";CELDA("NOMBREARCHIVO";A1))+1;+(ENCONTRAR("]";CELDA("NOMBREARCHIVO";A1)))-ENCONTRAR("[";CELDA("NOMBREARCHIVO";A1))-1)Requiere grabar el fichero. Las 4 lneas anteriores son una misma frmula.

74Funcin=EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;255)

Requiere grabar el fichero.75 Funcin que muestra la frmula de una celda

Macros

Devuelve la frmula que contiene una celda en lenguaje local

End FunctionSi se quita la palabra 'Local' devuelve la frmula en ingls.

76 Determinacin de si un nmero es primo o no es primo

Macros

Sub RemplazarPalabra() Dim Palabra As String Dim Hoja As Long Palabra = Trim(InputBox("Introduzca la palabra a buscar: ")) For Hoja = 1 To Sheets.Count Sheets(Hoja).Activate Cells.Replace What:=Palabra, Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next Hoja

Function nif(dni As Long) As String nif = Mid("TRWAGMYFPDXBNJZSQVHLCKE", (dni Mod 23) + 1, 1)

Est pensado para que la matriz se ponga comenzando en la celda A1, sin encabezado y sin que se repitan los elementos.Function DimeFila(Rango As Range, Valor_a_buscar) As Integer For Each c In Rango If c.Value = Valor_a_buscar Then DimeFila = c.Row End If Next

Para poner el nombre de la hoja en una celda

Function DisplayCellFormula(InputCell As Range) As String DisplayCellFormula = InputCell.FormulaLocal

Sub primo() Dim primo As Boolean Dim n As Long Dim d As Long n = Val(InputBox("Introduce un nmero natural", "Entrada de Datos")) d = 2 primo = True Do While primo And d < n If n Mod d = 0 Then primo = False End If d = d + 1 Loop


n TRUCO TIPO

76

Macros

End Sub77

Pero podemos desactivarla.

Para llevar este botn hasta una barra de herramientas sigue estos pasos.Selecciona: Ver, Barra de Herramientas, Personalizar.

78 Factorial

Macros

End Function79 Inicializar una matriz

Macros

'En este caso se ponen todos los valores a Empty

End Sub'En este caso se pone la variable c a su estado inicial'Borrandose los valores, las dimensiones y recuperandose'la memoria usada.

If primo Then MsgBox (n & " es primo") Else MsgBox (n & " no es primo") End If

Activar o Desactivar el empleo de la funcin IMPORTARDATOSDINAMICOS

Tablas Dinmicas

Al intentar operar con formulas que hacen referencia a celdas que provienen de una tabla dinmica con la versin XP de Excel nos encontramos con que automticamente nos pone la funcin:

IMPORTARDATOSDINAMICOS, y nosotros preferiramos que esto funcionara como antes.Esto se debe a que desde Office XP la funcin IMPORTARDATOSDINAMICOS se activa por defecto cuando nos referimos a celdas que forman parte de una tabla dinmica.

Para ello primero tendremos que llevar hasta alguna de las barras de herramientas un "botn", llamado "General GetPivotData".

Ve a la pestaa "Comandos". Ahora selecciona la Categora "Datos" y en la ventana derecha (Comandos) busca el botn "General Get Pivot Data" (est cerca del final, aproximadamente el octavo empezando por el final). Lleva este botn a la barra de herramientas de Excel que prefieras (esto se hace arrastrando el icono y soltndolo en alguna de las barras de herramientas).

Ahora, todo lo que tendremos que hacer es pinchar este botn para activar / desactivar la funcin IMPORTARDATOSDINAMICOS, que viene activada por defecto.

Function facto(n) Dim i As Integer facto = 1 i = 0 Do i = i + 1 facto = facto * i Loop While i n

Se inicializa con ERASE

Sub test() Dim c(1 To 20, 1 To 10) As Variant For i = 1 To 20 For j = 1 To 10 c(i, j) = RND Next j Next i MsgBox c(1,1) Erase c MsgBox c(1,1)

Sub test1() Dim c As Variant c = ActiveSheet.Range("A1:J20").Value MsgBox c(1, 1) Erase c


n TRUCO TIPO

79

Macros

End Sub80 Evitar los movimientos de pantalla mientras se ejecuta una macro

MacrosAl principio de la macro escribe

y al final

81 Formula referida siempre a un mismo rangoFuncin=SUMA(INDIRECTO("A1:B2"))

82 While...Wend

Macros

Esta estructura permite ejecutar las instrucciones contenidas mientras la condicin sea verdadera.

La siguiente macro posiciona el cursor en la ltima celda llena de la columna A de la Hoja1.

End SubOtra variante:

End SubLa segunda macro requiere posicionar inicialmente el cursor en la primera celda llena.

83 Contar cuantas veces se repite un mismo caracter en un textoFuncin=+LARGO(F3)-LARGO(SUSTITUIR(F3;"-";""))

Esta frmula cuenta cuantos guiones (-) existen en un texto que esta en la celda F3.84 Manejo de Rangos

Macros

Para sealar una tabla se hace con CurrentRegion. Un ejemplo:

filas = R.Rows.Count85 Ocultar Hojas

Macros

End Sub86 Determinar la fila hasta la que llega un rango

Macros

'Esto dara error ya que no hay ninguna matriz MsgBox c(1, 1)

Para evitar que durante la ejecucin de una macro se vean todos los movimientos y cambios del cursor, para evitar las "chirivitas" que se ven al ejecutar una macro:

Application.ScreenUpdating = False

Application.ScreenUpdating = True

Esta expresin permite sumar siempre el rango indicado aunque dicho rango se mueva a otra zona de la hoja.

Sub final1() i = 1 While Worksheets("Hoja1").Cells(i, 1).Value "" Worksheets("Hoja1").Cells(i, 1).Select i = i + 1 Wend

Sub final2() While ActiveCell.Value "" ActiveCell.Offset(1, 0).Select Wend ActiveCell.Offset(-1, 0).Select

Dim R As RangeDim filas As LongSet R = Range("A1").CurrentRegion

Podemos ocultar o mostrar Hojas de un libro. En el ejemplo siguiente, la Hoja2 esta inicialmente oculta, y para poder trabajar con ella la macro la mustra, y al finalizar la vuelve a ocultar.

Sub oculta() Sheets("Hoja2").Visible = True ActiveWorkbook.Sheets("Hoja2").Activate '... ... Sheets("Hoja2").Visible = False

Podemos determinar facilmente la fila hasta la que llega un rango. Supongamos el rango B5:B100, del que sabemos en que fila acaba (la 100), pero no sabemos en que fila comienza. Ejecutando la macro la variable n tomar el valor 5, que es la fila de comienzo del rango.Dim n As Long


n TRUCO TIPO

86

Range("B100").End(xlUp).Select

Macros

n = Selection.Row www.excelavanzado.com

ndiceHoja1Data

86 Trucos Para Excel

Documents

Transcript of 86 Trucos Para Excel