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:
    1. 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.
    2. Ámbito: Libro, o el que prefieras de la lista desplegable.
    3. Comentario: El que creas oportuno.
    4. 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:
    1. 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.
    2. Ámbito: Libro, o el que prefieras de la lista desplegable.
    3. Comentario: El que creas oportuno.
    4. 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. A Formato 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 K
Borrar selección (incluso formato). Cont. D (Delete) Interupción en VBA (marca/desmarca). F9
Buscar y reemplazar (1). Cont. B Ir a … F5
Buscar y reemplazar (2). Cont. L Libro nuevo. Cont. U
Cambiar Excel < > VBA. Alt F11 Mostrar/Ocultar objetos ( o marcadores ). Cont. 6
Cerrar aplicación activa. Alt F4 Muestra 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. 0
Cortar. Cont. X Ortografía. F7
Crear tabla (lista). Cont. Q Pegar. Cont. V
Cursiva (itálica) (1). Cont. 3 Pegar nombre. F3
Cursiva (itálica) (2). Cont. K Quita el contorno de las celdas seleccionadas. Cont. Mayú. _
Deshacer. Cont. Z Referencias: Abs... F4
Editar 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). F9 Selecciona 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. F8 Selecciona toda hoja o el rango actual (2). Cont. Mayú. esp.
Formato de celdas. Cont. 1 Subrallado (1). Cont. 4
Formato 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   ó  False
Activa/Desactiva los eventos:  Application.EnableEvents = True  ó False (luego guardar y ponerlo a True)
Activa/desactiva los mensajes de alerta:    Application.DisplayAlerts = True   ó False
Activa/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 activar
Cambia 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úmero
Coloca 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  /False
IF 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 v
Oculta 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=fechaFin
Edad 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últiplo
Impide 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órmula
Incrementa/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