ALGUNOS RECURSOS CON EXCEL

Algunos de estos recursos sólo están disponibles para versiones superiores a Excel 2003. El camino para llegar hasta ellos es a través de los Menús (en unos), a travez de la Cinta de opciones (en otros), a travez del Editor VBA para las macros, directamente en las celdas para el caso de las fórmulas y en Nombres definidos para el caso de Excel 4.0 .

CONTENIDO:

1. Asignar un   Nombre definido   a un rango de datos. 2. Asignar automáticamente,   Nombres definidos   a varios campos de datos. 3. Visualizar   Barras de datos   en las celdas (para valores numéricos). 4. Texto largo   en varias filas de la misma celda. 5. Trabajar con   Nombres en 2D . 6. Generar un   Nombre de rango variable . 7. Atajos de teclado . 8. Instrucciones   en el editor de VBA. 9. Fórmulas   e   Instrucciones Excel 4.0 .

1.- ASIGNAR UN NOMBRE DEFINIDO A UN RANGO DE DATOS.

1. Selecciona el rango de datos.2. Fórmulas > Nombres definidos > Administrador de nombres.3. Nuevo:

a. Nombre:  miRango , o el nombre que prefieras (sin espacios), siempre que no esté reservado. Por ejemplo; no se pueden utilizar como nombres AB o UVA ya que estos son nombres de columnas.

b. Ámbito:  Libro, o el que prefieras de la lista desplegable.c. Comentario:  El que creas oportuno.d. Hace referencia a:  El rango de celdas al que quieres aplicar el

nombre, p.ej. A2:C14

Ejemplo de trabajo con nombres: =BUSCARV("La Palma";miRango;3;0) , el resultado, según la Imagen del próximo tema, sería: 769.

Volver a Contenido

2.- ASIGNAR AUTOMÁTICAMENTE,NOMBRES   DEFINIDOS, A VARIOS CAMPOS DE DATOS.

1. Selecciona el rango de datos, incluso los títulos. Observa la Imagen a la derecha.

2. fórmulas > Nombres definidos > Crear desde la selección.3. Marca Fila superior, desmarca el resto y pulsa ACEPTAR.

Ejemplo de trabajo con nombres: Si ahora escribimos p.ej. en J6 =”En “&Mes&” el valor fue “&Valor, el resultado es En Mayo el valor fue 413, y si esa misma fórmula la escribimos p.ej. en E12 el resultado es En Noviembre el valor fue 635.

Volver a Contenido

3.- VISUALIZAR BARRAS DE DATOS EN LAS CELDAS   (para valores numéricos).

1. Selecciona el rango de datos (en la imagen de la derecha: B2:B13).2. Inicio > Estilos > Formato condicional > Barras de datos > Elige el color

que prefieras.

Las barras de datos tendrán un progreso acorde al valor de la celda, respecto al resto de valores del rango formateado.

Este efecto lo puedes combinar con Escalas de color y/o con Conjuntos de

iconos, que puedes encontrar en el mismo sitio.

Volver a Contenido

4.- TEXTO LARGO EN VARIAS FILAS DE LA MISMA CELDA.

Un texto como el

representado en B2 (ocupa más de una celda), podría verse como en B3, en el caso de que la celda de la derecha está ocupada con un valor.

Para que se vea como en B5 (o E5), puedes actuar como se indica a continuación:

1. Haz clic en la celda que contiene el texto largo.2. Inicio > Alineación > Ajustar texto (o también: Botón secundario sobre la

celda > Formato de celdas > Alineación > Ajustar texto).

Otra forma de conseguir este efecto es; donde quieras un salto de línea, pulsa: Alt + Intro.

Volver a Contenido

5.- TRABAJAR CON NOMBRES EN 2D.

1.

Selecciona el rango de datos, incluso los títulos (B2:H14). Observa la Imagen de la derecha.

2. Fórmulas > Nombres definidos > Crear desde la selección.3. Marca Fila superior y Columna izquierda.4. Pulsa ACEPTAR.

Si ahora escribes en una celda =SUMA(abril:junio manzana:mango), obtienes 2.484, que es la suma del rango sombreado en amarillo.Si escribes en otra celda =SUMA(mango), obtienes 2.412, que es la suma de los mangos en todo el año.

Aplicando el mismo procedimiento; con =SUMA(julio:septiembre), tendrás la suma del tercer trimestre y con=SUMA(enero:diciembre), obtienes la suma de toda la fruta durante todo el año.

Volver a Contenido

6.- GENERAR UN NOMBRE DE RANGO VARIABLE.

Puedes crear un nombre que abarque el rango ocupado con los datos, y en caso de que en el futuro se añada(n) nuevo(s) registro(s), o se elimine(n) alguno(s) ya existente(s), se ajustará automáticamente al nuevo rango. 

1. Fórmulas > Nombres definidos > Asignar nombre a un rango.2. Definir nombre.3. Nuevo:

a. Nombre:  miRango , o el nombre que prefieras (sin espacios), siempre que no esté reservado. Por ejemplo; no se pueden utilizar como nombres AB o UVA ya que estos son nombres de columnas.

b. Ámbito:  Libro, o el que prefieras de la lista desplegable.c. Comentario:  El que creas oportuno.d. Hace referencia a:  =DESREF(Hoja1!$A$1;1;0;CONTAR(Hoja1!

$A:$A);4) .

Con esta fórmula, miRango abarca A2:D10 en el caso de la figura de la izquierda (CONTAR(A:A) = 9), y A2:D13 en el caso de la figura de la derecha (CONTAR(A:A) = 12).

Volver a Contenido

7.- ATAJOS DE TECLADO.

Por cuestiones de funcionalidad y diseño, algunos atajos funcionan sólo en versiones anteriores a E2007, y otros sólo a partir de E2007. 

FUNCIÓN O TAREA TECLADO FUNCIÓN O TAREA TECLADO

Abrir. Cont. AFormato Porcentaje sin decimales.

Cont. Mayú. %

Administrador de nombres.

Cont. F3 Guardar. Cont. G

Administrador de tareas de Windows.

Cont. Mayú. esc.

Hora actual. Cont. Mayú. :

Ayuda. F1 Insertar celdas. Cont. +Barras de herramientas. Cont. 7 Insertar hipervínculo… Cont. Alt KBorrar selección (incluso formato).

Cont. D (Delete)

Interupción en VBA (marca/desmarca).

F9

Buscar y reemplazar (1). Cont. B Ir a … F5Buscar y reemplazar (2). Cont. L Libro nuevo. Cont. UCambiar Excel < > VBA.

Alt F11Mostrar/Ocultar objetos ( o marcadores ).

Cont. 6

Cerrar aplicación activa. Alt F4Muestra las columnas ocultas de la selección.

Cont. )

Cerrar, Restaurar, Minimizar,…

Alt esp.Muestra las filas ocultas de la selección.

Cont. (

Contorno a las celdas seleccionadas.

Cont. Mayú. &

Muestra/Oculta fórmulas/resultados.

Alt º

Copia/Pega la celda superior (1).

Cont. J Negrita(1). Cont. N

Copia/Pega la celda superior (2).

Cont. ' Negrita(2). Cont. 2

Copia/Pega la celda superior (3).

Cont. Mayú. "

Oculta columna. Cont. 9

Copiar. Cont. C Oculta fila. Cont. 0Cortar. Cont. X Ortografía. F7Crear tabla (lista). Cont. Q Pegar. Cont. VCursiva (itálica) (1). Cont. 3 Pegar nombre. F3

Cursiva (itálica) (2). Cont. KQuita el contorno de las celdas seleccionadas.

Cont. Mayú. _

Deshacer. Cont. Z Referencias: Abs... F4Editar celda. F2 Rehacer. Cont. Y

Eliminar celdas. Cont. -Rellena la selección con la entrada actual.

INTRO

Esquema. Cont. 8 Selecciona columna. Cont. esp.Evalua fórmula seleccionada (o porción).

F9Selecciona el rango con datos actual.

Cont. Mayú. * (no núm.)

Expande/Contrae barra de fórmulas.

Cont. Mayú. U

Selecciona fila. Mayú. esp.

Fecha actual. Cont. ,Selecciona toda hoja o el rango actual (1).

Cont. E

Fija para selecciónar celdas.

F8Selecciona toda hoja o el rango actual (2).

Cont. Mayú. esp.

Formato de celdas. Cont. 1 Subrallado (1). Cont. 4Formato Fecha con el día, mes y año.

Cont. Mayú. #

Subrallado (2). Cont. S

Formato Moneda con dos decimales.

Cont. Mayú. $

Tachado. Cont. 5

Formato numérico Exponencial (2 decimales).

Cont. Mayú. ^

Tamaño de fuente (o Tabla).

Cont. T

Volver a Contenido

1.- INSTRUCCIONES EN EL EDITOR DE VBA.

Funciones Excel: Application.WorksheetFunction. ????? Abre un libro con contraseña: Workbooks.Open "libro1.xls", , , , "Contraseña" Abre una página Web desde VBA: ActiveWorkbook.FollowHyperlink Address:="http://www.nombrepagina.com" Activa el botón COPIAR FORMATO: application.commandbars.findcontrol(Id:=108).enabled=true Activa/desactiva la actualización de pantalla: Application.ScreenUpdating = True ó FalseActiva/Desactiva los eventos: Application.EnableEvents = True ó False (luego guardar y ponerlo a True)Activa/desactiva los mensajes de alerta: Application.DisplayAlerts = True ó FalseActiva/Desactiva un botón en el cuadro de controles: ActiveSheet.OLEObjects("boton1").Enabled = True/False Actualiza la hoja al rango ocupado con datos (barra de Despl. con arrastre pequeño): ActiveSheet.UsedRange Asigna un nombre a un rango: ActiveWorkbook.Names.Add Name:="lista", RefersTo:=Range(Cells(1, 1), Cells(f, c)) Asigna una macro a una autoforma: ActiveSheet.Shapes("miAutoforma").OnAction = "miMacro" (= "" la desactiva)Borrar hipervínculos de un rango: Range("B1:B120").Hyperlinks.Delete Botón derecho no muestra menú contextual: Application.CommandBars("Cell").Enabled = False ' True para activarCambia el color del indicador de error: Application.ErrorCheckingOptions.IndicatorColorIndex = 2 Carga/cambia una imágen en un control de imágen ActiveX (desde disco duro): Image1.Picture = LoadPicture("C: \Mis documentos\Imagen.jpg") Cede el control al sistema operativo: OpenForms = DoEvents Celda en el encabezado (o pie de pág.): Sheets("Hoja1").PageSetup.LeftHeader = Sheets("Hoja1").Range("A1").Value Celda sobre la que se encuentra un botón (u otra fig): MsgBox ActiveSheet.Shapes("boton").TopLeftCell.Address Colección de controles por el nombre: Me.Controls("TextBox" & n).Value = Cells(fila, n) ' n=un númeroColoca botones, en columna de celda activa: ActiveSheet.Shapes.Range(Array("OptionButton1", "OptionButton2")).Left = ActiveCell.Left Color de etiqueta de hoja (amarillo): ActiveWorkbook.Sheets(ActiveSheet.Name).Tab.Color = 65535 Comprueba si existe una carpeta (si no; la crea): If Dir("D:\Copias\", vbDirectory) = "" Then MkDir "D:\Copias\" Comprueba si hay un filtro presente (con filtrado): If ActiveSheet.FilterMode = False Then Exit Sub Comprueba si hay un filtro presente (con o sin filtrado): If Not ActiveSheet.AutoFilterMode Then Exit Sub Comprueba si una celda tiene una fórmula: ActiveCell.HasFormula ( en Excel 4.0: =indicar.celda(48+0*hoy(),!a1) [nombre definido] )Concatena un texto (fijo) a cada celda de un rango: (en ventana inmediato de VBA) [b2:b12] = [transpose(transpose("SC "&b2:b12))]

Concatena/añade a valores existentes, un valor dado: [a1:a1000] = evaluate("a1:a1000&""(1)""") concatena con "(1)" (sin comillas)Convierte número tipo 19440724 a fecha 1944-07-24 (Inmediato): [a1:a5] = [transpose(transpose(text(a1:a5,"0000\/00\/00")))] Convierte una fórmula de inglés a español: Activecell.Formulaarray = "=left(b7)&mid(b7,match(1, --isnumber(match(mid(b7, Row(indirect(""2:15"")),1),{""a"";""e"";""i"";""o"";""u""},0)),0)+1,1)" Copia un rango en otra ubicación: Worksheets("Hoja1").Range("B2:C5").Copy Worksheets("Hoja2").Range("D15") Copia/Pega sin seleccionar rangos: Range(ActiveCell, ActiveCell.Offset(5, 3)).Copy Destination:= Worksheets("Hoja1").Range("E5") Copia/Pega, un rango en otro: (incluso formatos) [A1:A3].Copy [H2] (sólo valores) [F8:F10].Value = [A1:A3].Value Cuadro de diálogo de las fuentes: application.dialogs(xlDialogFont).show Cuenta las celdas con datos en un rango: conteo = Application.Count(Range("A:A")) [ sólo números ].Cuenta las celdas con datos en un rango: conteo = Application.CountA(Range("A:A")) [celdas no vacias]Cuenta las celdas con datos en un rango: conteo = Application.CountIf(Range("A:A"), "Marzo") [ condicional ].Cuenta datos en otro libro: conteo = Application.CountA(Workbooks("Libro2.xls").Sheets("Hoja1").Range("C:C")) Cuenta número de hojas: Worksheets.Count Declara una constante. Declarada a nivel global funciona para varios módulos: Const miConstante = 1944 Delimita el rango en que se pueden seleccionar celdas: ActiveSheet.ScrollArea="$B$1:$C$20" Desactiva el boton cerrar (X) de Excel: ActiveWorkbook.Protect ,,True Desactiva la barra de menús: Application.CommandBars("Worksheet Menu Bar").Enabled = True Descarga/Cierra un formulario: Unload Me ( Me o el nombre del formulario)Deshace una entrada en una celda (condicional): If "condición" Then Application.Undo Desmarca todos los botones de opción de una hoja: Activesheet.OptionButtons.Value = false Devuelve si un fichero fue abierto como de sólo lectura: ((GetAttr(ActiveWorkbook.FullName) And vbReadOnly) = vbReadOnly) Devuelve un carácter Unicode: ChrW(código) código abarca hasta 65000.Devuelve un valor; "negativo", "cero" o "positivo": =ELEGIR(SIGNO(número)+2;"negativo";"cero";"positivo") Dia de la semana: dia = Choose(Weekday(date,vbMonday), "Lun", "Mar", "Mié", "Jue", "Vie", "Sab","Dom") Ejecuta la Calculadora: RetVal = Shell("C:\WINDOWS\CALC.EXE", 1) Ejecuta un programa ejecutable: Shell("C:\WINDOWS\SYSTEM32\CALC.EXE", 1) (Ayuda > estilos)Ejecuta una macro de otro libro: application.run "'C:\ruta y sub\carpeta al\libro2.xls'!nombre_de_la_macro" Elige una constante según un número de índice: miVariable = Choose(numInd, "const1", "const2", 50, 70, "otra") Elimina celdas vacias: [a1:f10].specialcells(xlcelltypeblanks).delete shift:=xltoleft

Equivalencia con rango: Range(Cells(1, 1), Cells(10, 10)) = "A" equivale a Range("A1:J10")="A"Escribe en la ventana Inmediato: Debug.Print . . . Escribe en varias celdas al mismo tiempo: Range("A2:E2") = Array("Ruta", "Nombre", "Tamaño", "Modificado", "Tipo") Escribe en hoja protegida: Worksheets("miHoja").Protect UserInterfaceOnly:=True Evita que la selección cambie en una celda al pulsar Intro: Application.MoveAfterReturn = Target.Address <> "$A$1" Fecha y hora de creación de un fichero (cerrado): FileDateTime("Clientes.xls") Fila en que se encuentra un dato: fila = Worksheets(1).Range("A3:A23").Find(miDato).Row Fórmulas/Resultados. Muestra/Oculta… : ActiveWindow.DisplayFormulas = True /FalseIF para VBA: iif(expresión;verdadero;falso) (verdadero y falso, siempre son evaluados)Imagen en comentario de celda: ActiveSheet.Shapes("Comment 1").Fill.UserPicture "C:\Imágenes\foto.jpg" Impide Copiar/Pegar : Application.CutCopyMode = False Impide mover/arrastrar celdas/rangos: Application.CellDragAndDrop = False Imprime todas las hojas del libro: Sheets.PrintOut Incrementa 5% (Ventana INMEDIATO): For Each Celda In Selection: Celda.Value = 1.05 * Celda: Next Intersección o cruce de 2 rangos: If Application.Intersect(Target, Range("B2:B11")) is Nothing Then Invierte un texto: miTexto = StrReverse(Texto) Mayúsculas (1), minúsculas (2) y Nombre própio (3) : Target.Value = StrConv(Target.Value, 3) Tambien: vbUpperCase, vbLowerCase y vbProperCase Mensaje en la barra de estado: Application.StatusBar="Estamos en la linea: " & F al final del proceso: Application.StatusBar=False Mensaje temporal (de n seg. + 5 aprox.): CreateObject("wscript.shell").popup "Una prueba", n, "AnSanVal" Minimiza la cinta de opciones (1): If ExecuteExcel4Macro("get.toolbar(5,""ribbon"")") > 80 Then SendKeys "^{f1}" Minimiza la cinta de opciones (2): If Application.CommandBars("ribbon").Height > 80 Then SendKeys "^{f1}" Nombre de fichero mediante C. diálogo ABRIR: nombrefich = Application.GetOpenFilename Nombre de la unidad: nombre = Dir("d:\", vbVolume) Nombre definido (asigna/actualiza) al rango actual: ActiveWorkbook.Names.Add Name:="datos", RefersToR1C1:=ActiveCell.CurrentRegion Nombre del autor de un fichero: MsgBox ActiveWorkbook.BuiltinDocumentProperties("Author") Nombre del último que modificó un fichero: MsgBox ActiveWorkbook.BuiltinDocumentProperties("Last Author") Nombres ocultos. Visualiza los… (Ventana Inmediato): For Each n In Names: n.Visible = True: Next Núm. de páginas que se imprimirán en la hoja activa: MsgBox ExecuteExcel4Macro("Get.Document(50)") & " pág." Oculta / Muestra un libro: Windows("Libro1.xls").Visible = False / True

Oculta ciertas filas según una condición: Rows("10:20").EntireRow.Hidden = Range("A5").Value = "pagado" Oculta la barra de fórmulas: Application.DisplayFormulaBar = True vOculta un libro (Explorador): SetAttr "C:\Temp\Prueba.xls", vbHidden Pantalla completa: Application.DisplayFullScreen = True Pega en hoja activa sin seleccionar destino: ActiveSheet.Paste Destination:=Worksheets("Hoja1"). Range("A5:A7) ' rango destino completo.Pega en otra hoja solo valores, sin cambiar de hoja: Worksheets("Hoja2").Cells(65536,1).End(xlup).Offset(1).PasteSpecial xlPasteValues Posición de una cadena dentro de otra, buscando desde el final.: InStrRev("Constantino", "t") devuelve 8.Primera fila visible en la zona móvil de una hoja con paneles inmovilizados: fila = ActiveWindow.ScrollRow Pulsaciones de teclado. Para enviar INTRO (teclado numérico): Application.SendKeys "{ENTER}" (truco, VBA) Rango de datos en serie de gráfico: MsgBox Worksheets("Hoja1").ChartObjects(1).Chart.SeriesCollection(1).Formula Rango variable: Names.Add Name:="BaseProveedores", RefersTo:="= offset(Proveedores!$A$2,0,0,counta(Proveedores!$A:$A)-1,counta(Proveedores!$2:$2))" Rangos filtrados (sólo celdas visibles): Range("A1:A14").SpecialCells(xlCellTypeVisible).Copy Destination:=Range("D25") Recalcular funciones personalizadas, junto con el libro: Application.Volatile Recorre los valores de una matriz: For Clave = 0 To UBound(Claves) … Next clave Reemplaza un texto por otro dentro de una cadena de texto: celda = Replace(celda.Value, ",", "") Repite un texto en todo un rango: Range("A100:A150").FormulaArray = "Texto a repetir" Ruta completa de un archivo (en el ejemplo del libro personal): Workbooks("Personal.xls").FullName Salto de página -(Insertar) … ActiveSheet.HPageBreaks.Add ActiveCell (horizontal) … ActiveSheet.VPageBreaks.Add ActiveCell (vertical)Salto de página manual: Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakManual Selecciona las celdas que devuelven un error: Cells.SpecialCells(xlCellTypeFormulas, 16).Select Selecciona un rango con celda sup. Izq. en esquina sup. Izq. De pantalla: Application.Goto Reference:= Range("B12"), Scroll:=True Selecciona unas figuras SI y otras NO: ActiveSheet.Shapes.Range(Array(1, 2, 4)).Select Selecciona y visualiza a partir de una celda: Application.Goto Reference:=Sheets(1).Range("Z127"), Scroll:=True Seleccionar un rango, a partir de una celda dada (redimensionar): ActiveCell.Resize(3, 4).Select Situar figura según celda activa: ActiveSheet.Shapes("miFlecha").Left = Target.Left y ActiveSheet.Shapes("miFlecha").Top = Target.Top + Target.Height Sumar un rango: Range("resultado").Value = Application.WorksheetFunction.Sum(Range( "B2,E2,B6,B9,D6:E6,D10:E10,E14"))

Temporizador (utilizar DoEvents para que el libro quede activo): Application.OnTime Tipo de error (número) existente en una celda: MsgBox Evaluate("error.type($C$4)") 2= #¡DIV/0! 3= #¡VALOR! 7= #N/A Tipo de objeto seleccionado: TypeName(Selection) "Range", "TextBox", "Oval", etc.Traduce la fórmula de la celda activa a inglés (Inmediaiato en el editor VBA): ? activecell.formula Última celda ocupada de una columna: Cells(Rows.Count, "A").End(xlup).row Última columna ocupada de una fila: Cells(1,Columns.Count).Select Trabajar con una variable opcional: If IsMissing(Fecha) Then Fecha = Date Variables del entorno del sistema: variable = Environ(num) (num = desde 1 hasta 40)

Volver a Contenido

1.- FÓRMULAS EN LA HOJA DE CÁLCULO.

Extrae de una cadena el texto comprendido entre el 4º espacio y el 6º espacio. =EXTRAE(A1;HALLAR(""$"";SUSTITUIR (A1;"" "";""$"";4))+1;HALLAR(""$"";SUSTITUIR(A1;"" "";""$"";6))-HALLAR(""$"";SUSTITUIR(A1;"" "";""$"";4))-1)" Años meses o días, entre dos fechas: =SIFECHA(A1;B1;"Y")&" años "&SIFECHA(A1;B1;"YM")&" meses y "&SIFECHA(A1;B1;"MD")&" días." Aplica distintos porcentajes a distintos valores: =ELEGIR(COINCIDIR(f2;{0\0,5\1,5\2,5\3\5\6}*1000);5;7;10;12;15;20;22)% Aplicar distintos porcentajes a distintos valores: =A2*COINCIDIR(A2;{0\16\31})% Autonumérico para datos filtrados (o no filtrados): =Subtotales(3,b$2:b2) Busca en C y devuelve K : =BUSCAR("Pedro";C:K) o también =SI(ESERROR(COINCIDIR("Pedro";C:C));"";BUSCAR("Pedro";C:K)) Busca en rangos de distintas hojas: =BUSCARV(I2;ELEGIR(COINCIDIR(A6;{211\220\228};0);Hoja2!A3:G2642; Hoja3!A3:H338;Hoja4!A2:G673);2;FALSO) Cantidad de domingos en el mes de la fecha que está en A1 (matricial) [ en A1 p.e.: 1944 ]: {=SUMA(( DIASEM(FILA(INDIRECTO(FECHA(AÑO(A1);MES(A1);1)&"":""&(FECHA(AÑO(A1);MES(A1)+1;1))-1)))=1)*1)} Cantidad de meses entre dos fechas: =SIFECHA(fechaInicio;fechaFin;"m") Comprueba si A11 está entre los valores 5 y 12: =ELEGIR(COINCIDIR(A11;{0;5;12});"Fuera de rango"; "Dentro de rango";"Fuera de rango") Contar con varias condicones: =SUMA(CONTAR.SI($C$1:$C$20;{"A";"B";">D"})) Contar registros únicos (A) condicionales (B): =SUMAPRODUCTO((B2:B15="pan")*(COINCIDIR(A2:A15&"|"; A2:A15&"|";0)=FILA(A2:A15)-FILA(A1))) CONTAR.SI con múltiples condiciones: =SUMAPRODUCTO((CONTAR.SI(A1:F10;{1\3\5\7}))) Controla mediante Validación Texto en mayúsculas: =IGUAL(A1;MAYUSC(A1))

CONVERTIR entre unidades de medida: =CONVERTIR(3000;"W";"HP") =CONVERTIR(2;"m";"cm") Convierte un literal (invertido) a fecha (19440724 >> 16277 >> 24/07/1944): =--TEXTO("19440724";"00-00-00") Crear nombres Def. en selección ( truco / atajo ): Control + Mayúscula + F3 . . . Cuenta coincidencias ( z ) en un rango: =SUMAPRODUCTO(LARGO(A1:G5)-LARGO(SUSTITUIR(A1:G5;"z";""))) Cuenta los caracteres que se repiten a la izquierda: =hallar(izquierda(sustituir(A1;izquierda(A1);""));A1)-1 Cuenta los NO repetidos: =SUMAPRODUCTO((A1:A35<>"")/CONTAR.SI(A1:A35;A1:A35&"")) Cuenta los registros únicos en un rango: =SUMAPRODUCTO(--(FRECUENCIA(A:A;A:A)>0)) Cuenta los valores únicos en un rango: =SUMAPRODUCTO(1/CONTAR.SI(A2:A11;A2:A11)) Cuenta mayúsculas en un texto: =SUMAPRODUCTO(LARGO(A1)-LARGO(SUSTITUIR(A1;CARACTER(FILA(INDIRECTO("65:90")));""))) Cuenta según condiciópn exepto 4, 6 y 12: =CONTAR.SI(C2:C30;"<20")-SUMAPRODUCTO(--ESNUMERO(COINCIDIR(C2:C30;{4\6\12};0))) Devuelve los cabeceros (a,b, c) que coincidan con los valores máximos de A2:C2: =ELEGIR(SUMAPRODUCTO({1;2;4}*(A2:C2=MAX(A2:C2)));"a";"b";"ab";"c";"ac";"bc";"abc") Distancia entre 2 puntos: =RAIZ(((X2-X1)*(X2-X1))+((Y2-Y1)*(Y2-Y1))) <> =RAIZ((X2-X1)^2+(Y2-Y1)^2) <> = raiz ( x2 + y2 ) Domingos entre dos fechas: =SUMAPRODUCTO(--(DIASEM(FILA(INDIRECTO(A1&":"&A2));2)=7)) A1=fechaInicio A2=fechaFinEdad en años, meses y días: =SIFECHA(E2;HOY();"y")&" año(s), "&SIFECHA(E2;HOY();"ym")& " mes(es) y "&SIFECHA(E2;HOY();"md")&" día(s)." Elimina el carácter de la derecha (de un número): =SUSTITUIR(A2;CARACTER(CODIGO(DERECHA(A2;1)));"")+0 En ASD345 dejar sólo 345: =REEMPLAZAR(A1;1;MIN(HALLAR({0;1;2;3;4;5;6;7;8;9};A1&"0123456789")-1);"") Encuentra la primera celda vacia después de un rango (1): {=COINCIDIR(VERDADERO;B12:$B$65536="";)} Encuentra la primera celda vacia después de un rango (2): =INDICE(COINCIDIR(VERDADERO;B12:$B$65536="";);1) Extrae el mínimo, excluyendo el cero: {=MIN(SI($B$3:$B$24<>0;$B$3:$B$24;FALSO))} Extrae el promedio, excluyendo los ceros: {=SI(SUMA(B4:B15)=0;0;PROMEDIO(SI(B4:B15<>0;B4:B15;"")))} Extrae Núm. de un texto: {=1*EXTRAE(A1;COINCIDIR(VERDADERO;ESNUMERO(1*EXTRAE(A1;FILA($1:$9);1));0); CONTAR(1*EXTRAE(A1;FILA($1:$9);1)))} Extrae un dígito de un número COMO NÚMERO: =--IZQUIERDA(A1;1) (también =--IZQUIERDA(A1) ] Fila en que se encuentra un error #¡VALOR! : {=K.ESIMO.MAYOR((FILA($A$2:$A$6))* (ESNUMERO(--(TIPO.DE.ERROR($A$2:$A$6)=3)));1)} Fila o Columna de la celda activa (es necesario Calcular [F9 o VBA]): CELDA("fila") o

CELDA("columna") [ p.e.: SI(CELDA("fila")=1;… ]Filas impares (p.e.: para formato condicional): =residuo(fila(),2) Formato condicional para numeros que contengan cierta cifra: =NO(ESERROR(ENCONTRAR(5;A2))) Formato condicional. Resalta fila (o columna, o rango): =FILA()=CELDA("fila") Formato de celda personalizada, para positivos (azul) y negativos (rojo): [Azul]#.##0,00 ? ;[Rojo]-#.##0,00 ?_;# Formato para horas, minutos, segundos y milisegundos: [h]:mm:ss,000 Formato personalizado con condiciones: [Azul][>=100]0;[verde][>=10]0,0;[Rojo]0,00 (3 condiciones)Fórmula para celdas validadas que impide la entrada de registros duplicados: =CONTAR.SI(A:A;A1)<2 Fórmulas/Resultados. Muestra/oculta… (atajo de teclado): Alt + º Fracciones impropias (numerador>denominador): =TEXTO("3 5/4";"???/???") ó =TEXTO(3,25;"???/???") Imita Mround(n;m) : =REDONDEAR(n/m;0)*m n = número m = múltiploImpide escribir en un rango validado, mientras en E6 diga "algo": =$E$6<>"algo" Incrementa Precio 1,5% cada mes: =B1*(1,015)^SIFECHA(A1;HOY();"M") [A1]=fecha [B1]= Precio [C1]=fórmulaIncrementa/decrementa una fecha determinado número de meses: FECHA.MES(fecha_inicial:meses) (necesita Herramientas para análisis).Indica si una celda tiene fórmula con F. condicional: =INDICAR.CELDA(48;INDIRECTO("fc";FALSO)) Jerarquía en registros únicos: {=SUMA(SI($F$3:$F$17<F3;1/CONTAR.SI( $F$3:$F$17;$F$3:$F$17);0))+1}Jerarqía (incluso con repetidos) 1: {=SUMA(SI($F$3:$F$17>F3;1/CONTAR.SI($F$3:$F$17;$F$3:$F$17);0))+1} Jerarquia (incluso con repetidos)2: =SUMAPRODUCTO(($F$3:$F$17>=F3)*(1/CONTAR.SI($F$3:$F$17;$F$3:$F$17))) K.ESIMO de varias hojas: =K.ESIMO.MAYOR('Hoja1:Hoja3'!E$2:E$25;1) Letra del NIF : =EXTRAE("TRWAGMYFPDXBNJZSQVHLCKE";RESIDUO(A1;23)+1;1) Letra que más se repite: { =caracter(moda(si(largo(b8:b23);codigo(b8:b23)))) } Listado de Nombres definidos: F3 > Pegar lista Matriz "manualmente". Selecciona 4 columnas por 3 filas: { ={1;2;3;4\5;6;7;8\9;10;11;12} }Máximo Común Divisor (MCD): =gcd(A15:A20) Máximo entre dos límites: {=MAX(($A$2:$A$13>=D2)*($A$2:$A$13<=E2)*$B$2:$B$13)} Máximo valor (B) para un mes dado (A), según D2: {=SUMAPRODUCTO(MAX((MES(A2:A10)=D2)*(B2:B10)))}Mensaje en la propia celda: [>900000]"Excelente";[<300000] "Estudiar";"" (formato personalizado)Mensaje según valor de otra celda: =SI(A2="";"";ELEGIR(COINCIDIR(A2;{0\300000\900000}); "Estudiar";"";"Excelente")) Mínimo entre dos límites: {=MIN(SI($A$2:$A$13>=LimInf;1;5000)*SI($A$2:$A$13<= LimSup;1;5000)*($B$2:$B$13))} Mínimo que sea mayor que "número" : =K.ESIMO.MENOR(datos;CONTAR.SI(datos;"<="& numero)+1) Moda inversa (número o texto menos repetido): {=INDICE(valores;COINCIDIR(MIN(CONTAR.SI(

valores;valores));CONTAR.SI(valores;valores);0))}MODA para texto (matricial): {=INDICE(A2:A20;COINCIDIR(MAX(CONTAR.SI(A2:A20;A2:A20)); CONTAR.SI(A2:A20;A2:A20);0))} Modo de poner un condicional en SUMAR.SI: =SUMAR.SI(A2:A10;">"&J1;C2:C10) No grafica valores cero: =SI(E2>0;E2;NOD()) (o directamente #N/A)Nombre de la hoja: =DERECHA(CELDA("nombrearchivo");LARGO(CELDA("nombrearchivo"))- ENCONTRAR("]";CELDA("nombrearchivo"))) Nombre del libro: =EXTRAE(CELDA("nombrearchivo";A1);ENCONTRAR("[";CELDA("nombrearchivo";A1)) +1;ENCONTRAR("]";CELDA("nombrearchivo";A1))-ENCONTRAR("[";CELDA("nombrearchivo";A1))-1) Nombre/Fórmula (Insertar>Nombre>Definir). Esta devuelve la formula que está en A2: =indicar.celda(6+0*hoy();!$a2) (49 indica si es matricial)Número más cercano a cero: =INDICE(A1:A20;COINCIDIR(MIN(ABS(A1:A20));ABS(A1:A20);-1)) Números pares/impares menores/mayores que "num": =SUMAPRODUCTO((A1:E1<50)*(RESIDUO(A1:E1;2)=0)) Para graficar y no ver fórmulas que devuelven cero(0): =si(formula>0,formula,nod()) Para NO visualizar un dato en un gráfico: =SI(A1="";NOD();A1) Periodo de días, meses, etc. entre dos fechas: =SIFECHA(fecha1;fecha2;"d") Proporcional Inversa: =INDICE((B$1/SUMA(A$2:A$4))*A$2:A$4;JERARQUIA(A2;A$2:A$4)) B1=cantidad a repartir.Raiz cúbica de un número (núm. en A1): =POTENCIA(A1;1/3) Redondea a la centena más próxima: =REDONDEAR(núm;-2) (;-1 ;-3 etc. para: decenas, millares, etc.)Redondea un número, por grupos de valor: =MRound(38;5) devuelve 40. Precisa herramientas para Análisis.Redondea una hora en módulos de 30 minutos: =MULTIPLO.SUPERIOR(B2;1/48)Redondea… (… a la décima más próxima) =REDONDEAR(A1;1) (… hacia arriba) =REDONDEAR.MAS(A1;1) Redondeo en fracciones de 0,05 (si>2 redondeo hacia arriba, resto al más próximo): =ENTERO(A2*20+SI(A2>2;0,9;0,5))/20 Rellena con puntos la celda despues del texto: ;;;@*. (Formato personalizado)Resultado de varios posibles: =BUSCAR(G2;{0\5\7\9};{"Insuficiente"\"Suficiente"\"Notable"\"Sobresaliente"}) Ruta completa del libro y hoja (debe estar guardado): =CELDA("nombrearchivo") Seleccionar el rango actual: Ctrl* Seleccionar rango con datos (incluso celdas vacias intermedias): Ctrl+Mayúsc+Fin Separa núm. de texto AAAA AA AAA 271,00: =IZQUIERDA(A2;HALLAR("|";SUSTITUIR(A2;" ";"|";LARGO(A2)-LARGO(SUSTITUIR(A2;" ";""))))-1) Suma de importes correspondientes a un mes determinado: =SUMAPRODUCTO((MES(A2:A2000)=3)*(B2:B2000)) Suma los 4 valores más altos de un rango: =SUMAPRODUCTO(K.ESIMO.MAYOR(A1:A12;{1\2\3\4}))

Suma una cantidad dígito a dígito, y devuelve el resultado en un sólo dígito: =residuo(cantidad,9)+9*(residuo(cantidad,9)=0) p.e. 47=2 (4+7=11, 1+1=2)Sumar columnas alternas: =SUMAPRODUCTO((A1:I3)*(RESIDUO(COLUMNA(A1:K100);2)=1)) (1= impares, 0= pares) (cambiar el 2= cada 3, cada 4, etc.)Sumar con varias condicones: =SUMA(SUMAR.SI($E$1:$E$25;{"<7";9;">120"})) Sumar desde 1 hasta n : = n*(n+1)/2 (Julian - Valencia)Sumar mismo rango de varias hojas: =SUMA(Hoja1:Hoja15!A1) ó =SUMA(Primera:ultima!A1:D15) Título de campo del dato encontrado: {=INDICE(A1:L1;1;MIN(SI(A1:L32=B35; COLUMNA(A1:L1))))}Trimestre de una fecha: ="Trimestre " & COINCIDIR(MES(A1);{1\4\7\10}) & "º" >>> ( {1\3\5\7\9\11} para bimestres y {1\7} para semestres)Turno de trabajo: ="Turno de "&INDICE({"noche"\"mañana"\"tarde"\"noche"};COINCIDIR(A1; --{"0:00"\"6:30"\"14:30"\"22:30"})) A1=horaÚltima coincidencia: =BUSCAR(2;1/($B$1:$B$30="La Gomera");$C$1:$C$30) Última fecha de un registro: {=SI(MAX($A$2:$A$9*($B$2:$B$9=$G2))<1; "No hay datos";MAX($A$2:$A$9*($B$2:$B$9=$G2)))} Último dato en un rango: =BUSCAR(2;1/(D2:O2<>"");D2:O2) (en fila) = BUSCAR(2;1/(A2:A30<>"");A2:A30) (en columna)Último dato escrito en columna A. Para números: =BUSCAR(9e307;a:a) Último dato escrito en columna A. Para textos: =COINCIDIR("zzzzzz";a:a) Último valor escrito en un rango: =BUSCAR(2;1/(B1:B10<>"");B1:B10) Validación a múltiples columnas: =SUMAPRODUCTO(--(($C$1:$C1&"@"&$D$1:$D1)=($C1&"@"&$D1)))<2 impide parejas.Veces que se encuentra un texto en un rango: =SUMAPRODUCTO(1 - ESERROR(HALLAR("Acero"; A$2:A$7)))

Nota: Las funciones entre llaves, por ejemplo {=Formula}, son matriciales y deben ser entradas con: Control + Mayúscula + Intro. No escribir las llaves, estas entran solas.

Volver a Contenido

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A2")) Is Nothing Then

If Target <> Empty Then ActiveSheet.Name = Range("a2").Value

End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$J$11" Then ActiveSheet.Name = Target.Value

End Sub

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error GoTo NombreDuplicado

If Range("J11") <> Empty Then Sh.Name = Range("J11")

Exit Sub

NombreDuplicado:

MsgBox "Ya existe una hoja llamada '" & Range("J11") & "'" & _

vbNewLine & vbNewLine & "No se pueden tener dos hojas con el mismo nombre"

Range("J11").Activate

End Sub