Trucos excel

28
Desarrollo de Soluciones con Microsoft Excel 97/2000 Trucos Excel Desarrollo De Soluciones Con Microsoft Excel 97/2000 Trucos EXCEL Derechos Reservados Desarrollo de Soluciones con Excel 97/2000 1

description

 

Transcript of Trucos excel

Page 1: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

Desarrollo De Soluciones Con Microsoft Excel 97/2000

Trucos EXCEL

Derechos Reservados

Desarrollo de Soluciones con Excel 97/2000

1

Page 2: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

Trucos Excel

1.- Hacer que el texto encaje A veces se desea que cada columna tenga el ancho justo para el texto más ancho de esa columna. Una manera de hacer coincidir la anchura con el texto es seleccionar las columnas y escoger Formato/Columna/Ajustar a la selección. Pero hay una manera más rápida. Después de seleccionar las columnas que quiera ajustar, colocar el puntero sobre las letras de las columnas en la división entre dos de ellas. Cuando el puntero cambie de aspecto y parezca un signo más con flechas que apuntan a derecha e izquierda, hacer doble clic con el botón izquierdo del ratón para ajustar toda la selección. Para ajustar sólo una columna, hay que seguir el mismo método, pero sin seleccionar nada primero. Cuando se haga doble clic cerca de la división entre dos columnas, Excel ajusta la columna a la izquierda de la división.

2.- Evitar el libro de trabajo inicial Si se abre Excel para trabajar con un archivo existente, tener que cerrar el libro de trabajo vacío inicial es un engorro. Una forma de evitar esta pérdida de tiempo es cambiar la línea de comando que arranca Excel. Para cambiar el comando del acceso directo del escritorio, hacer clic con el botón derecho en el acceso directo y elegir Propiedades. Ir a la ficha Acceso directo, añadir el parámetro /E a la línea de comandos, o sea, después de EXCEL.EXE en el campo de texto Destino y pulsar Aceptar.

3.- Cambiar los parámetros por defecto Cuando se abre un libro en blanco de Excel o se crea un archivo nuevo, se tienen unos parámetros por defecto que pueden no responder a sus necesidades. Para cambiarlos, abrir un nuevo libro de trabajo y formatearlo en la forma que se desee, incluyendo el número de hojas, cabeceras, pies de páginas, márgenes, y si se muestra o no la cuadrícula. Luego, guardar el archivo como una plantilla llamada BOOK.XLT en la carpeta Iniciar que debe haber por debajo de \MSOFFICE\EXCEL (usual en Office 95) o bien \MSOFFICE\OFFICE (usual en Office 97); si no existiera, habría que crearla. Excel utilizará este archivo como el modelo por defecto en lo sucesivo. Puede crear archivos por defecto en lo sucesivo. Puede crear archivos por defecto similares para hojas de trabajo individuales, utilizando el nombre de archivo SHEET.XLT.

2

Page 3: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

4.- Cambiar el texto de los botones personalizados Cuando se añade un botón personalizado a una barra de herramientas, en el texto explicativo de la función del botón se puede leer Personalizado. Esto se puede cambiar (o el texto de cualquier otro botón) con una macro. Si el botón está en la barra de herramientas estándar, digamos, en la segunda posición por la izquierda, y quiere que el texto que aparezca sea Hola, la macro sería como sigue: Sub DoIt Toolbars ("Standard"). ToolbarButtons(2).Name="Hola" End Sub Para crear una macro, elegir Insertar/Macro, y seleccionar Módulo para insertar una hoja de módulo de Visual Basic, e introducir la macro en la hoja. Una vez que se ejecute la macro (con el nombre adecuado de barra de herramientas, posición del botón y texto explicativo), el cambio permanece hasta que se altere de nuevo. También puede cambiar el texto de la línea de estado, reemplazando el argumento .Name con el argumento .StatusBar, que toma la forma .StatusBar="Text".

5.- Ver encabezados de columnas Con Excel configurado para mostrar los textos explicativos, una ventana se abre e indica la columna más a la izquierda (por ejemplo, columna A) cuando se arrastra la barra de desplazamiento horizontal. Si se tiene una tabla con encabezados en la primera fila, se pueden ver los encabezados de la columna en su lugar. Seleccionar una celda en la fila y elegir Datos/Filtros/Filtro Automático.

6.- Ver nombres de hojas de trabajo Si hay muchas hojas en un libro de trabajo, no se podrán ver todas las solapas simultáneamente, pero si hace clic con el botón derecho sobre cualquiera de ellas, se verá una lista de nombres de hojas. Para saltar a cualquiera de ellas, hacer clic sobre su nombre o seleccionarla con las flechas de cursor y pulsar [Intro].

7.- Contar elementos en una base de datos Si se utiliza Excel para mantener una base de datos, se pueden utilizar funciones para extraer datos. Una función muy útil es CONTAR.SI(), cuya sintaxis es CONTAR.SI(rango, criterio). Supongamos, por ejemplo, que hay una lista de clientes en las

3

Page 4: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

celdas A1:G200, con España en la columna D. Para obtener el número total de clientes de Madrid, use la fórmula CONTAR.SI (D1:D200, "NY"). Esto le indica a Excel que busque en las celdas del rango D1 al D200 y cuente sólo aquellos cuyo valor sea NY (Nueva York). El criterio en una fórmula CONTAR.SI() puede ser texto, un número o una expresión. Si las ventas totales de cada cliente están en la columna E, por ejemplo, la fórmula CONTAR.SI (E1:E200), ">=100000" contará cuantos clientes tienen unas ventas iguales o superiores a 100.000 pts.

8.- Calcular totales de valores numéricos en una base de datos Otra útil función para extender datos es la función SUMA.SI(), que da la suma de los elementos seleccionados en la base de datos. La sintaxis de la función es SUMA.SI(rango, criterio, rango_suma). Supongamos que hay una lista de clientes en las celdas A1:G200 con los estados en la columna D y las ventas totales por cliente en la columna E. Si quiere conocer el total de todas las ventas en California, puede usar la fórmula SUMA.SI (D1:D200, "CA", E1:E200). Esta fórmula mirará en las celdas del rango D1 al D200 para todas aquellas que coincidan con el texto CA, y sumará las cantidades correspondientes en el rango E1 a E200. Si se omite rango_suma, Excel suma las celdas en el primer rango.

9.- Sumar celdas sin ratón El botón AutoSuma calcula el total de la suma de las celdas que tiene arriba o a la izquierda. Si se prefiere el teclado, pulsar [Alt]-[=].

10.- Formato condicional de celdasCuando se trabaja con muchos datos, una buena forma de llamar la atención es hacer que se resalten automáticamente aquellas celdas que cumplan una determinada condición. Excel 97 dispone de una opción al efecto muy sencilla de utilizar.Lo primero es seleccionar el rango de celdas al que se desea aplicar un formato diferente al resto en función del valor que tomen en cada momento. Luego, elegir Formato/Formato condicional. En el cuadro de diálogo nos encontramos con 4 cuadros, de los cuales el primero permite seleccionar con qué se va a hacer la condición (por defecto, el valor de la celda), el segundo marca qué tipo de condición se va a efectuar (entre 2 valor, mayor que, menor que, etc.) y el resto son los valores que deberemos determinar, aunque si se elige una condición del tipo "mayor que" o similar, el cuarto cuadro desaparecerá obviamente. El botón Formato permite

4

Page 5: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

definir en qué consistirá el formato condicional, pudiendo decidir entre fuentes, estilos, tamaño, color, bordes, etc.

11.- Controlar la tecla INTRO Por defecto, cuando se pulsa [Intro], la celda activa pasa a ser la de abajo, o la de arriba si pulsa [Mayús]-[Intro]. Para cambiar esto, elegir Herramientas/Opciones, y después la ficha Editar. En la lista desplegable que está debajo de la casilla de comprobación Mover selección después de ENTRAR, seleccionar la dirección que desee para el cursor. Cualquiera que sea la dirección elegida, [Mayús]-[Intro] se moverá el cursor en la dirección contraria. Si no se desea que el cursor se mueva en absoluto, desactivar la opción.

12.- Copiar la celda de arriba en la celda activa Para copiar un número, fórmula o texto de una celda a la que está justo debajo, colocarse en la celda en la que se quiere copiar y pulsar [Ctrl]-['] (apóstrofo). Esto es particularmente útil si ha insertado una columna en blanco en medio de una base de datos y necesita copiar una fórmula o dos de la fila de arriba. Y ya que estamos con fórmulas, la combinación de teclas [Ctrl]-["] (abrir comilla simple) conmuta entre mostrar los valores y las fórmulas correspondientes.

13.- Duplicar una fórmula Para introducir la misma fórmula en más de una celda al mismo tiempo, seleccionar el rango de celdas, teclear la fórmula como de costumbre y pulsar [Ctrl]-[Intro].

14.- Calcular rápidamente Para realizar rápidos cálculos para un rango de celdas en versiones antiguas de Excel, había que introducir la fórmula que diera el resultado y después borrarla. Excel 7.0 añade una ventana de cálculo automático mucho más útil, pero pobremente documentada. La ventana de AutoCálculo está a la derecha de la barra de estado que normalmente dice preparado. Contiene una entrada, como Suma=0 (aunque esto depende de lo último que hizo con dicha ventana). Para un cálculo rápido, seleccionar las celdas que quiere calcular, hacer clic con el botón derecho en la ventana de Cálculo automático y seleccionar el proceso del menú: Media, Contar, Contar Números (el número de celdas con números), Max (el número más largo en la selección), Min (el número más pequeño de la selección) o Suma. Aún mejor, se pueden seleccionar celdas no contiguas manteniendo pulsada la tecla Ctrl mientras se apunta y

5

Page 6: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

hace clic con el ratón.

15.- Mezclar tipos de gráficos A veces, es útil mezclar tipos de gráficos, como poner un gráfico de tarta para el total de ventas de un año detrás de un gráfico de columnas para los trimestres individuales. Para hacer esto, crear el gráfico de columnas, hacer doble clic sobre el gráfico para seleccionarlo, después hacer clic en una serie de datos (un conjunto de columnas). A continuación, elegir Formato/Tipo de gráfico (o hacer clic con el botón derecho y elegir Tipo de gráfico en el menú contextual), y finalizar escogiendo un tipo de gráfico, como un gráfico de tarta, y después hacer clic en Aceptar. Para volver al gráfico original, seleccionar otra vez las series de datos que se quiere cambiar, elegir Formato/Tipo de gráfico, y redefinirlo para que coincida con el tipo de gráfico original.

16.- Introducir la fecha y la hora Para introducir la fecha actual en una celda, pulsar [Ctrl]-[;] (punto y coma). Para introducir la hora actual en una celda, pulsar [Ctrl]-[:] (dos puntos). Para introducir tanto la fecha como la hora en la misma celda, pulsar [Ctrl]-[;] (punto y coma) y después un carácter separador, como ; (punto y coma) o / (barra), y pulsar [Ctrl]-[:] (dos puntos).

17.- Añadir líneas de tendencia Para añadir una línea de tendencia o de análisis de regresión a un gráfico, seleccionar las series de datos para los que quiere la tendencia o análisis, elegir Insertar/Línea de tendencia (o hacer doble clic y elegir Insertar/Líneas de tendencia del menú contextual), ajustar el tipo de tendencia o regresión y las opciones, y seleccionar Aceptar. Para borrar una línea de tendencia o regresión de un gráfico, seleccionar la línea y pulsar [Supr].

18.- Cambiar fórmulas a valores Si alguna vez ha tenido un puñado de fórmulas en una hoja de cálculo que necesitaba reemplazar con los valores calculados, apreciará esta macro, que hace el trabajo por usted: Sub Form2Val() Fot Each c In Selection.Cells c.Formula= c.Value Next c End Sub

6

Page 7: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

Para usar esta macro, colóquese en la celda o seleccionar las celdas que quiere cambiar y ejecútela. Lo demás es automático.

19.- Controlar mayúsculas/minúsculas en las celdas He aquí un par de macros para cambiar las entradas de celdas entre mayúsculas y minúsculas. Para convertir el texto en mayúsculas, usar la siguiente macro: Sub MakeUpperCase() For Each c In Selection.Cells c.Value= UCase$(c.Value) Next c End Sub Para cambiar el texto a minúsculas utilizar esta macro similar: Sub MakeLowerCase() For Each c In Selection.Cells c.Value=LCase$(c.Value) Next c End Sub Para utilizar cualquiera de estas macros, simplemente colocarse en la celda o seleccionar las celdas que se quieran cambiar, y ejecutar la macro.

20.- Eliminar espacios en celdas Los espacios extra que están antes de alguna entrada de una celda pueden crear problemas cuando intentan ordenar y filtrar tablas. Para eliminar estos espacios, seleccionar las celdas y ejecute esta macro: Sub TrimIt() For Each c In Selection.Cells c.Value=Application.Trim(c.Value) Next c End Sub

21.- Documentar una formula mediante notas El método más rápido consiste en pulsar la combinación Mayúsculas-F2. Desde el menú se consigue el mismo efecto con el comando Insertar/Notas. Con cualquiera de las dos acciones abriremos el cuadro de diálogo Notas, desde donde se podrán introducir tantos comentarios como se deseen, incluso grabando notas sonoras con el micrófono (si el equipo es multimedia). Si se ha insertado una nota y no es posible verla y/o oírla, ir al menú Herramientas/Opciones y en la ficha Ver, en el recuadro Mostrar marcar la opción Indicador de notas.

7

Page 8: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

23.- Llenar directamente un rango con un mismo dato o fórmula Suponiendo que las columnas A, B y C contienen 12 filas de números que deben ser sumadas en la columna D, marcamos un rango que comience en D2 (D1 contiene un rótulo) y llegue hasta D13, y tecleamos la fórmula =SUMA(A2:C2), terminando con Ctrl-Intro, en lugar del habitual Intro. La fórmula aparecerá en las 12 celdas marcadas. Eso sí, en referencias relativas (como =SUMA(A2:C2) en la celda D2, =SUMA(A3:C3) en la celda D3, y así sucesivamente. Si se trata de un dato, se procede de igual forma.

24.- Ensanchar rápidamente una columna Con el puntero del ratón situado en el borde derecho del encabezado de la columna (la fila gris inicial donde figuran las letras que las identifican), hacer doble clic. Con ello conseguiremos que la columna se adapte exactamente a la necesidad de espacio de la celda más ancha contenida en la columna. En Lotus 1-2-3 este procedimiento es válido; sin embargo, en Quattro Pro la opción es Formato/Ajuste rápido.

25.- Pegar datos desde una tabla de Access Si se marcan en Access unos registros de una tabla de la base de datos con el comando Edición/Copiar, y se pegan en Excel con el comando Edición/Pegar, nos podremos encontrar con algunas dificultades con respecto a los campos (celdas ahora) que no contenían valores. Si, por ejemplo, utilizamos una función CONTARA (cuenta las celdas que tienen algún contenido dentro del rango especificado) que tenga como argumentos las celdas de la columna correspondiente a un campo de la base de datos original, siempre nos encontraremos con el sorprendente resultado de que cuenta todas las celdas del rango, aunque algunas estén aparentemente vacías. Peor aún, puede dar problemas al ordenar la información. Esto se debe a que durante la copia, se pasa a dichas celdas el carácter oculto Alt+O de la tabla ASCII, en lugar de dejarlas en blanco. Se trata de una situación complicada. Si marcamos el rango, al elegir Edición/Ir a/Especial/Constantes/Texto, veremos cómo quedan seleccionadas no sólo las celdas que contiene un texto visible, sino también aquellas que estaban teóricamente en blanco. Para nuestra desgracia, arreglar la situación no es tan sencillo como elegir Edición/Reemplazar dejando en blanco la línea Reemplazar con, pues no lograremos nada. Habrá que hacerlo en dos pasos. El

8

Page 9: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

primer paso es teclear Alt+O en la línea Reemplazar y $$$$ (o cadena absurda por el estilo que no pueda nunca confundirse con el contenido real de cualquier registro) en la línea Reemplazar con. Y el segundo paso es reemplazar las cadenas absurdas que habrán quedando en las celdas aparentemente vacías con nada (dejando vacía la línea Reemplazar con).

26.- Incluir tipos de letra variados en una sola celda Si se desean introducir varios tipos, tamaños y colores de letra en un rótulo que se encuentra en una sola celda, bastará con ir marcando los bloques y formateándolos dentro de la barra de fórmulas. Lamentablemente, no es posible aplicar esto a las fórmulas ni a las entradas numéricas.

27.- Ocultar fórmulas en una hoja protegida Cuando el diseñador de una hoja desea protegerla de modo que los futuros usuarios no sean capaces de ver el contenido de las fórmulas que introdujo, sino sólo el resultado de las operaciones que las fórmulas efectúan, tiene la posibilidad de, una vez marcadas las celdas que desee ocultar, activar el atributo Oculta de la ficha Protección, que encontrará en el menú Formato/Celda. Cuando finalmente se active la protección con el comando Herramientas/Proteger/Proteger Hoja, el maravilloso "saber cómo" sobre hoja de cálculo quedará protegido, incluso con una contraseña, si así se desea.

28.- Crear listas rápidas Muchas veces en una empresa se utilizan listas de nombres repetidamente, (nombres de los vendedores, filiales, listas de artículos, etc.). Para evitar estar introduciendo una y otra vez la lista podemos seleccionar los datos y el comando Edición/Copiar, marcar la celda de destino y Edición/Pegar. Este procedimiento, aunque perfectamente válido, puede ser sustituido por el más sofisticado método de crear Listas personalizadas. Para crear una lista personalizada, marcar en bloque las celdas que contengan valores que quiera que formen parte de la lista. En el menú Herramientas/Opciones elegir la ficha Listas y pulsar el botón Importar. Ahora la lista de vendedores está incluida, y cada vez que se necesite introducirla en una hoja, solo se necesitará introducir en una celda el primer valor de la serie, y pinchando en la esquina inferior derecha de la celda arrastrar hacia abajo (arriba, izquierda o derecha) para construir la lista.

9

Page 10: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

29.- No imprimir determinadas áreas Muchas veces al imprimir una hoja de cálculo no se desea que determinadas filas o columnas del área de impresión aparezcan en la misma. Para ello el método más fácil es ocultar las filas/columnas deseadas. Esto se puede hacer de dos formas. Primero, seleccionar las filas/columnas y en el menú Formato seleccionar Fila o Columna y Ocultar. Para que las columnas/filas sean visibles de nuevo hay que seleccionar las columnas/filas contiguas y activar Formato/Fila o Columna/Mostrar. Otro método de llevar el puntero del ratón hasta la línea divisoria del encabezado de dos filas/columnas, el puntero cambiará y tendrá la forma de una barra con dos flechas, y mover el ratón hasta ocultar la fila columna. El método para volver a mostrarlas es el mismo que antes. Y por último utilizando atajos de teclado, una vez seleccionada la columna pulsar Ctrl-9 para ocultar y Ctrl-Mayúsculas-9 para mostrarla. Si se trata de filas, utilizar Ctrl-8 y Ctrl-Mayúculas-8.

30.- Crear filtros automáticos Los filtros automáticos son una característica avanzada de Excel que permiten seleccionar los datos relevantes de una tabla o lista de una forma muy intuitiva. Si tenemos una tabla o lista de datos, ponemos el cursor sobre una celda de la tabla y pulsamos Datos/Filtro/Filtro Automático. Aparecerá en la cabecera de cada columna de datos una tecla indicándonos los criterios de selección para filtrar dichos datos. Una vez escogido uno de los filtros Excel sólo mostrará los datos que cumplan los criterios utilizados en dicho filtro. Aparte de los filtros más utilizados se pueden incluir filtros personalizados por el usuario.

31.- Juego de laberinto en Excel 7 En Excel 7, seleccionar Archivo/Nuevo y bajar hasta la fila 95. Hacer clic sobre el indicador 95 para seleccionar toda al fila, luego pulsar TAB una vez, elegir Ayuda/Acerca de y, con las teclas CONTROL+MAYUSCULAS pulsadas, hacer clic en Soporte Técnico. Una nueva ventana se abrirá con unos gráficos en 3D. Ahora se puede recorrer libremente un laberinto tridimensional llamado Hall of Tortured Souls usando las teclas de flecha. Ir directamente al muro y teclear excelkfa. Comenzará el juego.

32.- Combinación de celdas

10

Page 11: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

Para combinar dos o más celdas hay que usar el carácter &. Así, por ejemplo, si en A2 hubiera un nombre propio, en B2 el primer apellido y en C2 el segundo apellido, se puede formar una combinación de las 3 celdas en otra nueva para hacer aparecer el nombre completo de la persona con un espacio entre cada uno de la siguiente forma:

=A2&" "&B2&" "&C2. También sería posible utilizar la función Concatenar. La fórmula anterior quedaría así: =CONCATENAR(A2;" ";B2;" ";C2).

33.- Separación de cadenas Podemos basarnos en el ejemplo que supone trabajar con un NIF, el cual consta siempre de un número y una letra. Lo que se pretende es separar el número de la letra para usarlos a la hora de hacer cálculos y, mediante la fórmula de cálculo de la letra NIF, verificar si está correctamente introducida. Supongamos que tenemos en la columna A los números del NIF en el formato 1234567-L, en la columna B queremos extraer los números para el cálculo y en la columna C la comprobación. Para extraer los números de la celda que contiene el NIF podemos utilizar: En B2: =IZQUIERDA(A2;HALLAR("-";A2)-1) (estando en A2 el NIF 1234567-L, el resultado será 1235467) En C2: =DERECHA(A2;LARGO(A2)-HALLAR("-";A2)) (esto extraerá la letra L) Estas 2 fórmulas funcionan cualquiera que sea la longitud del NIF. Ahora podríamos comprobar si el NIF es correcto con un condicional del tipo:

=SI(C2=AlgoritmoNIF(B2);"";"NIF no válido")siendo AlgoritmoNIF la fórmula de cálculo del NIF.

34.- Tratamiento de fechas y el año 2000 en Excel 97 Cuando se teclean sólo dos dígitos del año, Excel 97 asume lo siguiente:

• Si la fecha tecleada está en el rango 1/1/00 - 31/12/29, Microsoft Excel asume que la fecha es del año 2000.

• Si la fecha tecleada está en el rango 1/1/30- 31/12/99, Microsoft Excel asume que la fecha pertenece a los años 1900. Ejemplos:

11

Page 12: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

• Si quiere introducir en una celda la fecha 17/5/23, debería teclear los 4 dígitos del año, por ejemplo 17/5/1923, si no lo hace así Excel asumirá que es 17/5/2023.

• Si quiere introducir la fecha 6/9/57, pero refiriéndose al año 2057, deberá introducir los cuatro dígitos del año, esto es 6/9/2057 en otro caso Microsoft Excel asumirá 6/9/1957. Nota: Esta información difiere del comportamiento de versiones anteriores.

35.- Combinaciones de teclas rápidas que más comúnmente se utilizan en Excel 97 En Excel 97, se pueden actualizar rápidamente los datos que son devueltos por Microsoft Query. Pulsando F9 refresca la consulta en la ventana actual. Se puede utilizar esta tecla en lugar de hacer clic en la opción refrescar datos del menú. Excel 97 permite utilizar una combinación de teclas para insertar rápidamente la fecha y hora actual en una celda. Para hacerlo, utilice la siguiente combinación de teclas: Fecha actual: CTRL+; Hora Actual: CTRL+MAYUSCULAS+: La fecha y la hora se basan en el reloj interno del ordenador. Excel 97 permite copiar y pegar texto en un solo paso. Para hacer esto, utilice la siguiente combinación de teclas: CTRL+SHIFT+, (coma) copia los datos de la celda superior a la celda activa y lo pega en la celda activa. CTRL+SHIFT+" (comilla doble) copia los datos de la celda superior a la celda activa y utilice el comando Pegado Especial para pegar sólo valores en la celda activa. Para obtener una lista con todas las combinaciones de teclas disponibles, debe hacer lo siguiente:

• Clic en el asistente de Office. • Teclear "teclas rápidas" sin comillas y clic en Buscar. • Elegir el tema que usted desee.

36.- Combinación de teclas que rápidamente permita moverse entre las hojas de un libro de trabajo en Excel 97 Se puede utilizar CTRL+AVPág para mover las hojas a la derecha y CTRL+ RePág para moverse a las hojas a la izquierda.

37.- Cambiar el salto de página mientras se previsualiza un archivo en Excel 97 Excel 97 permite hacerlo con una nueva característica: Vista Previa de Salto de Página. Con esta característica puede ver el documento y mover el salto de página con un simple clic sobre el

12

Page 13: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

salto de página y arrastrándolo hasta la posición deseada. Para utilizar esta característica seguira los pasos detallados a continuación: 1. En el menú Ver, hacer clic en Vista previa del salto de

página. 2. Hacer clic en el salto de página para seleccionar y arrastrar a

la nueva posición.

38.- Niveles de deshacer en Excel 97 Excel 97 tiene hasta 16 niveles de deshacer. Para deshacer una acción, pulsar en el botón Deshacer. Para deshacer varias acciones a la vez, pulsar en la flecha al lado del botón deshacer, seleccionar más de una acción desde la lista y para realizar la operación de deshacer, hacer clic en el último elemento seleccionado de la lista.

39.- Vincular los cuadros de texto se pueden a celdas en una hoja de trabajo en Excel 97

• Dibujar un cuadro de texto en su hoja de cálculo. • Comprobar que el cuadro de texto es el objeto seleccionado

en su hoja de cálculo. Para hacer esto hacer clic sobre el cuadro de texto una vez.

• Pulsar F2. Esto situará el cursor en la barra de fórmulas. • Teclear =<referencia a la celda>, donde la <referencia a la

celda> es la celda a la que quiere vincular el cuadro de texto.

40.- Cambiar de mayúsculas a minúsculas una cadena de texto en Excel 97

• Para cambiar una cadena entera a minúsculas utilizar la función MINUSC.

• Para cambiar una cadena entera a mayúsculas utilizar la función MAYUSC.

• Para cambiar la primera letra de cada palabra a mayúsculas utilizar la función NOMPROPIO.

Ejemplo: * En la celda A1, teclee su nombre y apellido todo en minúsculas. * En la celda B1, teclee =NOMPROPIO(A1).

41.- Insertar una marca de agua o filigrana en el fondo de una hoja de cálculo en Excel 97

• En la barra de herramientas de Dibujo, hacer clic en Insertar WordArt.

• Seleccionar el diseño y hacer clic en Aceptar.

13

Page 14: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

• Introducir el texto y hacer clic en Aceptar. • Cambie el tamaño del objeto al tamaño que desee. • En la barra de herramientas de WordArt, hacer clic en

Formato de WordArt. • Establezca el color de relleno a Sin Relleno. • Poner el color de la línea a Gris claro y clic en Aceptar. De

esta manera ya puede introducir datos en las celdas y tener un objeto como una marca de agua por debajo de sus datos.

42.- Copiar el formato de unas celdas a otras en Excel 97 Una manera rápida y fácil de copiar y pegar formatos de celdas es:

• Seleccionar una celda que contenga los formatos que usted desee seleccionar.

• Hacer clic en el botón Copiar formato, con lo que el cursor se convierte en una brocha.

• Seleccionar las celdas a las que quiere pegar el formato. Al soltar el botón del ratón, se aplicará el formato a la selección.

43.- La función Autocompletar en Excel 97 Si los primeros caracteres que teclea en una celda coinciden con una entrada existente en esa columna, Excel rellena los caracteres que faltan. Excel sólo completa las entradas que están compuestas de texto, o combinaciones de texto y números, las entradas que contienen sólo números, fechas u horas no se completan. Para aceptar las entradas propuestas, pulsar Aceptar. La entrada completa coincide exactamente con el modelo en mayúsculas y minúsculas de la entrada existente. Para reemplazar los caracteres introducidos, continuar tecleando. Para borrar los caracteres introducidos, pulsar Retroceso.

44.- Grado de compatibilidad soporta Excel 97 con respecto a las otras versiones Si se comparten archivos con personas que trabajan con otras versiones de Excel, en Excel 97 se puede guardar los libros de trabajo en un formato que permiten ser utilizados en una versión anterior sin restringir al usuario las nuevas características. Cuando se esté preparado para guardar el trabajo, seguir los siguientes pasos:

• En el menú Archivo, elija Guardar Como. • En la lista Guardar Como Tipo, clic en Libros de Excel 97 y

5.0/95 (*.xls). Además se puede poner este tipo de formato por defecto de la siguiente forma:

14

Page 15: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

• En el menú Herramientas, elegir Opciones. • En la etiqueta de transición, en el cuadro desplegable de

Guardar Como elegir Libros de Microsoft Excel 97 y 5.0/95 (*.xls)

45.- Tratamiento de las tablas dinámicas en Excel 97 En Excel 97, ya se pueden dar formato a las celdas de una tabla dinámica. Este formato se guardará cuando se actualice dicha tabla. Antes de comenzar a dar formato, asegurarse de que la opción Habilitar Selección del menú Seleccionar del menú de Tablas dinámicas está activada. En caso de no estarlo, activarla. Nota: Si se aplican formatos condicionales a las celdas de tablas dinámicas, este formato no se mantendrá después de actualizar la tabla dinámica.

46.- Publicar datos en una página Web en Excel 97 Es un proceso muy sencillo. Para hacerlo se deben seguir los siguientes pasos:

• Seleccionar el rango de celdas que contiene los datos que desee publicar.

• Desde el menú Archivo, hacer clic sobre Guardar como HTML.

• Seguir las instrucciones del asistente de publicación en Internet.

47.- Tratar los documentos HTML directamente en Excel 97 Excel 97 permite analizar los datos de sitios Web. Se pueden abrir los documentos HTML directamente. Para ello hay que realizar los siguientes pasos:

• Desde el menú Archivo , hacer clic en Abrir. • Teclear la URL en el cuadro de texto Nombre de archivo.

48.- Simulador de vuelo en Excel 97 Pulsar F5 (se abre el cuadro Ir a) y teclear el rango X97:L97. Luego pulsar el tabulador para situarse en la celda M97, pulsar CONTROL+MAYUSCULAS mientras se pulsa sobre el icono del Asistente para gráficos. Tras unos momentos, aparecerá una pantalla en la que se puede volar sobre unos paisajes fractales. En un momento determinado del vuelo se verá un sitio con inscripciones que muestra los nombres de los creadores de Excel 97. Los controles son extremadamente sensibles; se usa el ratón para controlar la dirección: el botón izquierdo avanza y el derecho retrocede. Para salir basta con pulsar ESC.

15

Page 16: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

49.- Insertar nuevas celdas y pegar datos en un solo paso Cuando se vayan a copiar o mover celdas, seleccionar el rango de celdas que se desea desplazar, apuntar con el ratón al borde de la selección y mantener pulsada la tecla CONTROL mientras se arrastra el rango a la nueva ubicación. Al liberar el botón del ratón, Excel copia las celdas al destino. Para insertar los datos, mantener pulsadas las teclas CONTROL y MAYUSCULAS durante el arrastre y las celdas existentes serán desplazadas.

50.- Convertir una tabla de Excel en un archivo de Access Elegir Datos/Convertir para activar un asistente que lleva el proceso paso a paso. Si esta opción falta del menú, seleccionar Herramientas/Complementos y asegurarse de que está seleccionada la opción Enlaces de Access. Si la opción no aparece, habrá que instalarla desde el programa de instalación. Access necesita estar instalado para que el asistente funcione.

51.- Guardar formatos personalizados para diagramas para aplicarlos rápidamente a otros Para crear un autoformato definido por el usuario, abrir o crear un diagrama con las opciones deseadas de formato, luego elegir Formato/Autoformato y pulsar la opción Definido por el usuario. Pulsar a continuación el botón Personalizar para activar el cuadro de diálogo correspondiente, en el que hay que pulsar el botón Añadir, escribir un nombre y una descripción para el formato y guardarlo para posteriores usos.

52.- Fórmulas y funciones rápidas Teclear un signo igual dentro de la celda y luego pulsar el botón en barra de fórmulas para activar el asistente de funciones. Este proporcionará una lista de las funciones más usadas recientemente y define cada argumento que se necesita introducir.

53.- Números bien redondeados Crear un formato de número personalizado que muestre valores de moneda en miles en lugar de en millones. Elegir Formato/Celda, ir a la pestaña Número y seleccionar Personalizado dentro de Categoría. En el campo Tipo introducir lo siguiente: #,;(#,). Una vez creado, Excel recordará este formato y lo mostrará en las opciones de formato personalizado.

54.- Descartar entradas duplicadas

16

Page 17: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

Para eliminar valores duplicados de una columna de texto, seleccionar la columna de texto a filtrar y elegir Datos/Filtro/Filtro avanzado. En el cuadro de diálogo, marcar la casilla Sólo registros únicos. Elegir Filtrar la lista para ocultar las filas que contengan información duplicada, o bien elegir Copiar a otra ubicación para crear una lista duplicada con sólamente valores únicos. Excel pone la fila en azul para indicar que está filtrada.

55.- Seguir la pista a los datos con plantillas Para ello probar la característica de seguir la pista a los datos en el asistente de plantillas. Crear una hoja de cálculo como el formulario de entrada de datos. Elegir el asistente de plantillas desde el menú Datos. El asistente lleva paso a paso por el proceso de enlazar campos desde el formulario a una base de datos. Una vez finalizado, se tendrá una plantilla personalizada que cualquiera puede abrir y rellenar, con entradas introducidas automáticamente en la base de datos.

56.- Concatenar texto y números Utilizando el carácter & se pueden unir en una sola celda datos numéricos y de texto. Por ejemplo, si en la celda D10 hay un valor 500 y se quiere mostrar en otra celda el texto 500 pesetas, elegir la nueva celda y entrar la fórmula +D10&" pesetas" y pulsar INTRO.

57.- Copiar configuraciones de página entre hojas diferentes Activar la hoja que contiene las propiedades de la página que se desean copiar. Elegir Archivo/Configurar página y pulsar OK en el cuadro de diálogo. Activar luego la hoja en la que se desea aplicar esa configuración, elegir Edición/Repetir configuración de página o bien pulsar CONTROL+Y.

58.- Centrar en un solo paso Si se centra texto frecuentemente a través de columnas utilizando el cuadro de diálogo de formato de celda, colocar un botón en la barra de herramientas para hacer esto en un solo paso. Para ello, hacer clic con el botón derecho en cualquier barra de herramientas y elegir Personalizar. Elegir la categoría Formato de texto y arrastrar el botón de las columnas centradas a través hacia la barra de herramientas.

59.- Programas complementarios de Excel Algunos complementos para Excel (como View Manager, Reports Manager, y Analisys ToolPak) o bien no funcionan adecuadamente

17

Page 18: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

o bien no funcionan en absoluto en un Cuaderno. La solución en la mayoría de los casos es ver la sección Excel fuera del Cuaderno escogiendo Sección/Ver Fuera. Después volver al Cuaderno eligiendo Archivo/Cerrar.

60.- Añadir archivos de Excel 4.0 a un cuaderno Si se intenta, utilizando comandos del menú, añadir archivos Excel 4.0 (o anteriores) a un Cuaderno, éste no permitirá añadir más de uno. Para solventar esto, se puede arrastrar cada archivo Excel desde el Explorador (o el escritorio) al panel izquierdo del Cuaderno, o abrirlo en la versión más reciente de Excel y guardarlo con el formato de cualquiera de las versiones 5.0 ó 7.0.

61.- Optimizar macros de Excel El código del generador de macros de Excel graba todas las acciones del teclado y del ratón y está usualmente lleno de instrucciones Select, que ralentizan la macro como la que se muestra a continuación: Sheets("Q1 Estimates").Select Range("C9").Select ActiveCell. FormulaR1C1="6000" Range("C11").Select ActiveCell.FormulaR1C1="5400"

Se pueden mejorar las prestaciones de la macro reemplazando todas las sentencias Select con referencias directas a las celdas o rangos. Por ejemplo, se podría reemplazar todo el código anterior con sólo dos líneas: Sheets("Q1 Estimates").Cells(9,3).Value=6000 Sheets("Q1 Estimates"). Cells(11,3).Value=5400

62.- Ejecutar macros autoejecutables Las macros AutoOpen y AutoClose de un libro de trabajo de Excel se ejecutan automáticamente cuando un usuario abre o cierra el libro de trabajo. Cuando abrir o cierre un libro de trabajo utilizando código de macro VBA, por el contrario, Excel no ejecutará estas macros automáticamente. Si se desea ejecutarlas hay que llamar al método Run AutoMacros del objeto ExcelWorkbook; por ejemplo, se puede usar el siguiente código para ejecutar una macro AutoOpen del libro de trabajo: Dim myWB As Workbook Set myWB=Workbooks.Open ("Financial.xls") myWB.RunAutoMacros x1AutoOpen

63.- Cómo crear plantillas

18

Page 19: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

Se puede convertir cualquier documento de Excel en una plantilla guardándolo en la carpeta MSOFFICE\PLANTILL (o cualquiera de las carpetas identificadas como localizaciones de plantillas de usuario). No es necesario dotar al archivo de una extensión especial (como .DOC, para las plantillas Word); simplemente guárdelo en el directorio \PLANTILL, el archivo aparecerá con otras plantillas en el cuadro de diálogo cuando se haga clic sobre Nuevo en el menú Archivo o sobre el botón Iniciar documento nuevo de la barra de acceso directo de Office.

64.- Añadir fragmentos a un cuaderno Para añadir parte de un documento, en lugar del archivo completo, a un Cuaderno, seleccionar la porción a añadir, arrastrarlo hasta el Cuaderno, y soltarlo en la lista de Secciones y aparecerá una nueva sección Cuaderno con esta información. También se puede arrastrar un fragmento de documento creado arrastrando el texto o datos seleccionados desde un documento hasta el escritorio, hasta el Cuaderno. Cuando se hace esto, el fragmento aparece como una nueva sección en el Cuaderno, como si se hubieran arrastrado los datos directamente desde la aplicación Office. Para avanzar una sección de una vez, pulsar CONTROL-ALT-MAYUSCULAS-AvPág simultáneamente. Para retroceder una sección pulsar CONTROL-ALT-MAYUSCULAS-RePág.

65.- Mover datos entre aplicaciones El soporte OLE permite arrastrar datos entre dos aplicaciones que se estén ejecutando sin más que seleccionarlos en una y arrastrarlos hasta la otra. Pero preparar las ventanas de cada aplicación para que aparezcan en la pantalla ha quedado anticuado. En su lugar, seleccionar los datos en la aplicación origen; entonces, manteniendo pulsado ya sea el botón izquierdo del ratón (para mover) o el derecho (para copiar), arrastrar los datos hasta el icono de la aplicación de destino en la barra de tareas de Windows 95 y mantenerlo allí uno o dos segundos hasta que la aplicación de destino se abra. Se puede concluir la operación seleccionando la zona en la que se quieren depositar los datos.

66.- Deshacer arrastrar y soltar Imaginemos que se selecciona un texto en Word y se mueve a una hoja de Excel pulsando el botón izquierdo del ratón durante la operación de arrastrado. Pero después de soltar el texto en Excel, cambiamos de opinión y seleccionamos Edición/Deshacer Arrastrar y soltar en Excel. ¿Pone esta operación cada cosa, justo

19

Page 20: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

en la forma en que estaba? Pues no. El texto desaparece de la hoja de Excel pero no reaparece automáticamente en Word. Para deshacer completamente la operación hay que volver a Word y seleccionar Edición/Deshacer Mover. Sólo entonces reaparecerá el texto que se movió a Excel.

67.- Mover plantillas con cuadernos Macros, autotexto y otras opciones personalizables se guardan a menudo en plantillas. Si se piensa en mover un Cuaderno a otro ordenador pero se desea conservar el acceso a las macros, se deben mover las copias de las plantillas que contienen dichas características a la nueva localización del Cuaderno. Se pueden poner los archivos de plantilla en el Cuaderno, si se desea, para simplificar la operación de mover, pero las macros y demás opciones no serán accesibles desde la nueva ubicación hasta que se guarden las plantillas como archivos.

68.- Proteger celdas La protección de celdas se emplea para que el cursor se salte las protegidas, muy útil al rellenar formularios. Para ello hay que seguir estos pasos:

• Seleccionar las celdas que han de rellenarse • Elegir Formato/Celdas • Hacer clic sobre la ficha Proteger • Desmarcar la casilla Bloqueada • Pulsar Aceptar • Elegir Herramientas/Proteger/Proteger hoja • Marcar las 3 casillas que hay en el cuadro que aparece y

escribir, si se desea, una contraseña • Pulsar Aceptar

Ahora la tecla TAB sólo pasará el cursor por las celdas desprotegidas.

69.- Seleccionar varias hojas a la vez Para realizar acciones en más de una hoja a la vez se pueden seleccionar varias manteniendo pulsada la tecla CONTROL mientras se hace clic sobre los nombres de las etiquetas de cada hoja.

70.- Determinar una función como el contenido de una celda Excel tiene una gran variedad funciones como ISBLANK() y ISERROR(), para comprobar el tipo de entrada que hay en una celda. Sin embargo no tiene como función predeterminada una

20

Page 21: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

entrada del tipo fórmula. Para crear una función, definida por el usuario, se debe presionar [Alt][F11] para que aparezca el editor de Visual Basic y luego el comando Insertar/ Módulo. Aparecerá una ventana con el código para crear la acción: Function IsFormula(c) IsFormula = c.HasFormula End Function Finalmente volveremos a Excel presionando de nuevo ALT-F11. Ahora, haremos una entrada en la celda A1. Seguidamente entraremos la función definida por el usuario ISFORMULA(A1) en cualquier otra celda para determinar que la celda A1 contiene una fórmula. Si hacemos esto la función devolverá TRUE, en otro caso devolverá FALSE. Debemos notar, pero, que cuando se realiza una entrada de una función definida por el usuario en un libro de trabajo puede ser que otra celda la contenga, por lo que deberemos incluir el nombre del libro de trabajo. Por lo tanto si, se ha creado una función definada por el usuario en LIBRO 1.XLS, se debe entrar la función como sigue: ='Libro 1.xls'!IsFormula(A1)

71.- Formato específico para el contenido de una celda o rango A veces conviene asegurarse de que se introducen los datos correctos en una hoja de cálculo. Para ello se puede especificar qué datos son válidos para una celda o rango. Por ejemplo, para que sólo se puedan introducir fechas posteriores al 1 de enero de 1998 en un rango de celdas hay que seguir estos pasos:

• Seleccionar las celdas a restringir. • Elegir Datos/Validación. Se abrirá el cuadro de diálogo

Validación de datos con varias fichas. • Seleccionar la ficha Configuración si no fuese la que

apareciera desplegada. • Desplegar la lista Permitir y seleccionar Fecha. Se puede

apreciar que el cuadro cambia de aspecto para ofrecer diversos campos de entrada de información.

• En la lista desplegable Datos seleccionar el operador Mayor que y en la casilla Fecha inicial escribir 1/1/98 (como hemos supuesto en el ejemplo).

• Como último detalle se puede personalizar el mensaje de error que Excel mostrará en caso de que la fecha introducida en alguna de las celdas restringidas no sea correcta. Para ello

21

Page 22: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

hay que seleccionar la ficha Mensaje de error, seleccionar el tipo de mesnaje y escribir un texto para el caso.

72.- Anidar más de 7 funciones SI Excel 97 no permite anidar más de 7 funciones SI en una misma celda. Supongamos que se tienen ciertos valores en el rango comprendido entre B5 y M5 y se desea que en N5 se calcule el primer valor no nulo comprobando desde B5. En otras palabras, si B5 es cero, entonces se comprueba C5; si éste es cero, entonces se comprueba D5; y así sucesivamente hasta que haya un valor distinto de cero, en cuyo caso sería el valor a tomar en N5. Para ello, hay que uso de una celda auxiliar que no se vaya a utilizar (por ejemplo, R5). En ella consignar esta fórmula: =SI(B5<>0;B5;SI(C5<>0;C5;SI(D5<>0;D5;SI(E5<>0;E5;SI(F5<>0;F5;SI(G5<>0;G5;SI(H5<>0;H5;SI(I5<>0;I5;SI(J5<>0;J5;SI(K5<>0;K5;SI(L5<>0;L5;0)))))))))) Finalmente, en N5, consignar esta otra fórmula: =SI(R5<>0;R5;SI(J5<>0;J5;SI(K5<>0;K5;SI(L5<>0;L5;SI(M5<>0;M5;0))))) Con esto se habrá conseguido dividir en dos el número de anidamientos y el cálculo es correcto.

73.- Los problemas del siglo XXI En Excel, en sus versiones 4, 5 y 7, los dígitos 00 a 19 son automáticamente interpretados como los años 2000 a 2019, mientras que el 20 es capturado como 1920. En la versión 97 de los productos Microsoft el año de referencia se incrementa a 29, por lo que este número se almacena como 2029 y el 30 como 1930.

74.- Evitar la ejecución de macros en documentos protegidos por contraseña A causa de los posibles virus que pueden albergarse en las macros de Excel, lo que hay que hacer es mantener pulsada la tecla MAYUSCULAS mientras se pulsa Aceptar en el cuadro de diálogo de la contraseña.

75.- Documentos fiables Cualquier documento guardado en el subdirectorio InicioXL (que cuelga del subdirectorio Office) será considerado fiable y también la Protección antivirus en macros será ignorada. Por lo tanto, no se debe permitir guardar nada en ese subdirectorio.

76.- Cálculo automático sobre toda una hoja entera

22

Page 23: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

Supongamos que se quieren dividir todas las celdas numéricas de una hoja de cálculo por el valor en pesetas del Euro, es decir, dividir por 166,386. Vamos a su poner que la hoja de cálculo tiene el nombre genérico por defecto Hoja1. Para volcar su contenido ya convertido a euros, abrir una nueva hoja en blanco del mismo documento (por ejemplo en la Hoja 2 si es que no se utiliza) y en la celda A1 consignar esta fórmula: =SI(ESNUMERO(Hoja1!A1);Hoja1!A1/166,386;Hoja1!A1) Luego arrastrar el controlador de relleno (el pequeño cuadradito negro de la esquina inferior derecha de la celda activa) hacia la derecha hasta que ocupe el mismo núumero de columnas de la hoja original, y después arrastrar hacia abajo hasta ocupar el mismo número de filas de la hoja original. Automáticamente se mostrarán todos los valores convertidos.

77.- Suma acumulativa basada en fechas Hay trabajos propios de hoja de cálculo que requieren introducir una fecha en cada columna de datos para luego ir acumulando una serie de valores en otra columna y sumándose fila a fila. Esto, que parece sencillo, se complica si lo que se pretende es tener una columna con los valores acumulados por fecha, es decir, para varias fechas iguales, el valor se acumula hasta que se inicia una fila con fecha posterior. Para hacerlo más gráfico, pongamos por caso el control de ventas de productos en un establecimiento. Lo primero que hay que hacer es crear una columna auxiliar en una columna que no se utilice (por ejemplo, la columna Z) basada en interruptores (flags). Este tipo de columnas auxiliares son muy útiles pero hay que alejarlas de la vista principal para que no estorben. Por otra parte, vamos a suponer que los datos deseados van a ir en la columna H. Esta es la vista parcial de la hoja:

A B C D E F G H I ... Z123 FECH

APAG.

BASE

IVA LIQUIDO

ACUMULADO

ACUMULADO POR DIA

IDEM

4 02/01/98

P 1 819 131 950 950 950 X

5 02/01/98

P 2 1.293

2071.500 2.450 2.450 Y

23

Page 24: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

6 02/01/98

P 3 1.379

2211.600 4.050 4.0504.050

Y

7 03/01/98

P 4 1.897

3032.200 6.250 2.200 X

8 03/01/98

P 5 5.000

8005.800 12.050 8.000 Y

9 03/01/98

P 6 1.595

2551.850 13.900 9.850 Y

10

03/01/98

P 7 2.259

3612.620 16.520 12.47012.470

Y

11

04/01/98

P 8 1.897

3032.200 18.720 2.200 X

12

04/01/98

P 9 1.121

1791.300 20.020 3.500 Y

13

04/01/98

P 10

1.595

2551.850 21.870 5.3505.350

Y

14

05/01/98

P 11

2.586

4143.000 24.870 3.000 X

15

05/01/98

P 12

3.276

5243.800 28.670 6.800 Y

16

05/01/98

P 13

1.121

1791.300 29.970 8.100 Y

17

05/01/98

P 14

4.655

7455.400 35.370 13.500 Y

18

05/01/98

P 15

560 90 650 36.020 14.150 Y

19

05/01/98

P 16

38.966

6.234

45.200

81.220 59.350 Y

20

05/01/98

P 17

13.190

2.110

15.300

96.520 74.65074.650

Y

Para este trabajo se utilizarán 2 fórmulas, una es la que va en la columna Z y otra la que va en H. En la columna Z, el primer valor es (debe ser) siempre fijo. Aquí lo vamos a llamar X, siendo Y el otro interruptor. Pero se pueden llamar A y B, o bien PEPE y JUAN. El funcionamiento será idéntico. La fórmula empleada desde el 2º valor de Z en adelante es la siguiente: =SI(($A4<$A5);"X";"Y") Esta fórmula permite saber en qué momento se ha producido un cambio de fecha en la columna A.

24

Page 25: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

En la columna H van los valores a mostrar ya calculados basándose en el contenido de la columna Z. La fórmula del primer elemento (H4) es:=SI($Z4="X";$F4;SI(Y($Z3="Y";$Z4="Y");$H3+$F4;$F4+$F3))Esta fórmula comprueba el valor de su fila en Z y acumula el valor siempre que no haya cambio de fecha. En el momento que cambie la fecha, el valor se reinicializa a cero y comienza una nueva acumulación. No hay que confundir el operador lógico Y con la letra "Y" entrecomillada.En algunos casos, es posible que se prefieran dejar en blanco aquellas celdas de H que no totalizan. Para este propósito se incluyen en la columna I los mismos valores que en H pero dejando en blanco las celdas que no suman el total general. La fórmula en este caso es:=SI($A4=$A5;"";SI($Z4="X";"";SI(Y($Z3="Y";$Z4="Y");$H3+$F4;$F4+$F3)))Ahora sólo hay que decidir si trabajará con la columna H o con la I. Si se prefiere la H, basta con borrar la I. Si se prefiere la I, basta borrar la H y mover los valores de I hacia H.

78.- Combinar datos de celdas de texto Para combinar dos o más celdas se utiliza el carácter & (ampersand) así si en la celda A2 estuviese el nombre, en la celda B2 estuviese el primer apellido y en C2 el segundo apellido, la siguiente fórmula combinaría las tres celdas con un espacio entre cada una: =A2 & " " & B2 & " " & C2 También es posible utilizar la función CONCATENAR. La fórmula anterior quedaría: =CONCATENAR(A2;" ";B2;" ";C2)

79.- Separar la letra del número en un NIF Supongamos que tenemos en la columna A los números de NIF en el formato 1234567-L, en la columna B se desean extraer los números para el cálculo y en la columna C la letra para la comprobación. Para extraer los números de la celda que contiene el NIF podemos utilizar en B2: =IZQUIERDA(A2;HALLAR("- ";A2)-1) (estando en A2 el NIF 1234567-L, el resultado será 1234567. Para extraer la letra del NIF podemos utilizar en C2: =DERECHA(A2;LARGO(A2)- HALLAR("-";A2)) de tal forma que extraerá la letra L.

25

Page 26: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

Estas dos fórmulas funcionan cualquiera que sea la longitud del NIF. Se podría comprobar si el NIF es correcto con un condicional del tipo =SI(C2=AlgoritmoNIF(B2);"";"NIF no válido") siendo AlgoritmoNIF la formula de cálculo del NIF. Este sistema de extracción es útil cuando se dispone de entradas alfanuméricas con un formato definido, por ejemplo, referencias de productos que se componen de varios dígitos y números o letras de control separados por un guión.

80.- Retornos de línea en el texto de una celda Cuando se teclea texto en una celda, es posible cortarlo en cualquier momento para pasar lo siguiente debajo de lo anterior pulsando ALT-INTRO.

81.- Recuento rápido de valores Se puede conseguir un recuento rápido de cuántos elementos se tienen en una hoja utilizando la función CONTAR.SI que cuenta las celdas, dentro de un rango, que no están en blanco y que cumplen con un criterio concreto. Su sintaxis es: CONTAR.SI(rango;criterio) Rango es aquél dentro del cual desea contar el número de celdas que no están en blanco. Criterio es la condición (en forma de número, expresión o texto) que define las celdas a contar. Por ejemplo, puede expresarse como 32; "32"; ">32" o "manzanas". Ejemplo 1:

Supongamos que el rango A3:A6 contiene "manzanas", "naranjas", "melocotones" y "manzanas" respectivamente. CONTAR.SI(A3:A6;"manzanas") sería igual a 2. Ejemplo 1: Supongamos que el rango B3:B6 contiene 32; 54; 75 y 86 respectivamente. CONTAR.SI(B3:B6;">55") sería igual a 2.

82.- Crear una configuración por defecto Cuando se comienza un nuevo archivo, se toman por defecto una serie de valores que podrían no ajustarse a las necesidades de un usuario en concreto. Para estos casos se puede generar una configuración personalizada. Para ello, se abre un libro nuevo (o una hoja nueva), se le da el formato requerido y se guarda el archivo con el nombre LIBRO.XLT (en el caso de libro) o HOJA.XLT

26

Page 27: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

(en el caso de hoja) dentro de la carpeta INICIO, que cuelga de la carpeta OFFICE. Excel lo utilizará como plantilla en los nuevos documentos. También se pueden guardar en la carpeta de inicio alternativo, la cual hay que configurar en la ficha General cuando se elige Herramientas/Opciones.

83.- Colocar los cortes de página antes de imprimir Se pueden establecer manualmente los cortes de páginas eligiendo Archivo/Vista preliminar y pulsando el botón Saltos de página. De esta forma aparecerá un mensaje que indicará que se pueden arrastrar con el ratón una serie de marcas azules que separan claramente una página de otra al imprimir. Para volver a la vista normal, elegir de nuevo Archivo/Vista preliminar y pulsar el botón Vista normal.

84.- Adaptar celdas para textos largos Si se va a incluir un texto largo, se pueden combinar varias celdas adyacentes y hacer que el texto se trunque entre ellas. Pare ello, seleccionar varias celdas adyacentes de una misma fila y elegir Formato/Celdas. Del cuadro que aparece, entrar en la ficha Alineación y marcar las casillas de verificación Ajustar texto y Combinar celdas. Una vez escrito todo el texto, colocar el puntero del ratón sobre la marca de división de la fila actual y la siguiente y arrastrar hacia abajo para que el texto se visualice.

85.- Suma ultrarrápida de celdas adyacentes Esto es válido tanto para filas como para columnas. Supongamos que tecleamos una serie de cifras numéricas que después se van a sumar. Al pasar de la última celda, mantener pulsadas las teclas ALT y MAYUSCULAS mientras se pulsa la tecla del signo =. Se quedará visualizada la fórmula de la suma automáticamente y basta pulsar INTRO para que la suma quede reflejada.

86.- Adaptar el tamaño de las celdas a su contenido Cuando se teclean más caracteres de los que se pueden visualizar en una celda, Excel no muestra todo el contenido. Para forzar a que lo haga, seleccionar las celdas en las que se desee aplicar este efecto y elegir Formato/Celdas. Del cuadro que aparece, entrar en el ficha Alineación y marcar la casilla Reducir hasta ajustar. El contenido de la celda se reducirá o aumentará según sea la medida de la columna.

27

Page 28: Trucos excel

Desarrollo de Soluciones con Microsoft Excel 97/2000Trucos Excel

87.- Introducir un mismo dato en varias celdas simultáneamente Seleccionar las celdas en las que se vaya a introducir el mismo dato, teclearlo y pulsar CONTROL-INTRO.

88.- Uso de botones complementarios Desde la versión 5.0, al pulsar la tecla MAYUSCULAS y pulsar sobre ciertos botones, se puede apreciar que éstos cambian y muestran un botón diferente. Si se libera la tecla MAYUSCULAS, vuelve a aparecer el botón original. La siguiente es una muestra de botones con complementario aunque hay algunos más: Abrir/Guardar Imprimir/Vista preliminar Orden ascendente/Orden descendente Subrayado/Doble subrayado Alineación izquierda/Alineación derecha Alineación central/Combinar y centrar Aumento decimal/Reducción decimal Reducir indentación/Aumentar indentación Insertar filas/Eliminar filas Insertar columnas/Eliminar columnas Pegar formato/Pegar valores Eliminar contenido/Eliminar formato Debido a estos complementarios, algunos botones pueden ser reemplazados por otros dentro de la misma barra de botones y ganar así más espacio.

28