Rangos de celdas: el 99% de los usuarios no lo saben

Nunca me enseñaron cómo usar rangos de celdas en Excel; Dudo que tengas cualquiera de las dos cosas. Son demasiado básicos para que cualquiera pueda enseñarlos, ¿verdad? Selecciona algunas celdas y verás un símbolo : (dos puntos) colocado entre las celdas inicial y final. Tal vez podríamos agregar algunos $ (signos de dólar ). ) para fijar la posición de una referencia de celda. No es ninguna ciencia espacial.

Para la mayoría de los usuarios de Excel, eso es todo lo que saben sobre rangos y todo lo que creen que necesitan saber. Pero ese es sólo el punto de partida; hay mucho más por descubrir.

Los rangos son los componentes básicos de Excel, así que asegurémonos de entender cómo usarlos correctamente.

Esta publicación descubre los métodos para trabajar con rangos que todo usuario de Excel debería conocer.

Tabla de contenido

• Operadores de rango
• Operador de rango
• Operador sindical
• Operador de intersección
• Orden de precedencia
• Funciones que devuelven rangos de celdas
• ELEGIR
• SI
• IFS
• ÍNDICE
• INDIRECTO
• COMPENSAR
• CAMBIAR
• BUSCARX
• Otros operadores y sintaxis
• Marcador absoluto ($)
• Operador de derrame
• Operador de intersección implícito
• Conclusión

Descargue el archivo de ejemplo: haga clic en el botón a continuación para unirse al programa Insiders y obtener acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo:

• 0179 Conceptos básicos del rango de celdas.xlsx
• 0179 Hoja de referencia básica del rango de celdas.pdf

Tener acceso

Operadores de rango

Los operadores de rango son los caracteres especiales que colocamos entre referencias de celdas individuales para crear un rango.

Hay 3 operadores:

• Operador de rango : crea un área de rango única de 4 lados.
• Operador de unión : Crea un rango uniendo dos o más rangos.
• Operador de intersección : el rango compartido por dos o más rangos.

Comencemos por comprender estos operadores.

Operador de rango

El operador de rango es el símbolo : (dos puntos). Vemos este símbolo al seleccionar un rango usando el mouse o los atajos de teclado.

Dado que este es el operador que la mayoría de los usuarios de Excel conocen, no dedicaremos mucho tiempo a este.

A continuación se muestra un ejemplo del operador de rango en uso.

=SUM(B2:D4)

Resultado: 42

El operador de rango combina todas las celdas (incluidas las dos celdas de la referencia) en un único rango de 4 lados.

Nada demasiado complejo aquí.

Una vez unido al operador sindical o al operador de intersección, el operador de rango se vuelve aún más poderoso.

Operador sindical

El operador de unión es el símbolo ( coma).

Es posible que haya visto la coma utilizada en la siguiente situación:

=SUM(B2,D3,C4)

Resultado: 14

Sin embargo, en este escenario, la coma no es el operador de unión sino el separador de argumentos.

Dentro de una función, los argumentos están separados por una coma. Por ejemplo, la función SUMA puede incluir 255 elementos para agregar, cada uno separado por una coma. En este ejemplo, proporcionamos a SUM 3 argumentos; No hemos utilizado el operador sindical.

Debemos incluir corchetes adicionales para usar el operador de unión dentro de una función, para que Excel sepa que no debe tratar la coma como un separador de argumentos.

=SUM((B2,D3,C4))

Resultado: 14

En el ejemplo, el cálculo arroja el mismo resultado, pero no siempre será así.

Puede parecer que simplemente estamos enumerando celdas. Si bien así es como enumeramos elementos en una oración o en Power Query, no es así como funcionan las referencias de celda.

El cálculo combina los rangos individuales en un solo rango antes de ejecutarse.

El poder del operador de unión se ve cuando se combina con el operador de rango. Mire la imagen a continuación; Estamos sumando los valores de los dos rangos como si hubiera un solo rango.

=SUM(B2:D2,C4:E4)

Resultado: 29

Recordar:

La coma puede ser el separador de argumentos o el operador de unión. Utilice corchetes dentro de una fórmula para asegurarse de que Excel trate la coma como operador de unión.

Operador de intersección

El operador de intersección es el carácter (espacio).

El operador de intersección devuelve el rango compartido por dos o más rangos.

Mira el ejemplo de abajo:

=SUM(B3:E4 C2:D5)

Resultado: 19

La fórmula en la celda G2 calcula el rango donde los otros dos rangos se cruzan (es decir, el área donde se cruzan los rangos) en las celdas C3:D4 .

Al utilizar el operador de intersección, podemos incluir múltiples rangos. Por ejemplo, B3:E4 C2:D5 D2:E4 sería una sintaxis aceptable para 3 rangos y devolvería D3:D4 .

Orden de precedencia

Ahora bien, ¿qué pasa si mezclamos operadores en un único rango? ¿Cómo afecta eso al cálculo?

Dentro de las reglas de cálculo de Excel, ya existe un orden de cálculo establecido.

1. Operador de rango
2. Operador de intersección
3. Operador sindical

Echemos un vistazo a 3 ejemplos para ver esto en funcionamiento.

Ejemplo 1

Mira lo siguiente, ¿cuál será el resultado?

=SUM((B3:E3 C2:C5,D4))

1. B3:E3 y C2:C5 calculan primero
2. A continuación, se calcula la intersección entre los dos rangos, dejando solo la celda C3 .
3. El operador sindical evalúa y da un rango de C3,D4 .
4. Por lo tanto, el cálculo final es =SUM((C3,D4))

Resultado: 10

Ejemplo #2

Si queremos forzar el orden de cálculo, podemos usar corchetes (como con cualquier función anidada en Excel).

=SUM((B3:E3 (C2:C5,D4)))

1. Los corchetes evalúan primero:
   • Se calcula el operador de rango C2:C5 .
   • A continuación, como no hay intersección entre paréntesis, el operador de unión evalúa, dejando el rango C2:C5,D4 .
2. A continuación, se evalúa la intersección. La única celda que se cruza entre B3:E3 y C2:C5,D4 es C3 .
3. Por lo tanto, el cálculo final es =SUM((C3)) .

Resultado: 2

Ejemplo #3

Eche un vistazo al siguiente ejemplo; ¿Qué calculará eso? Éste es complicado.

=SUM(B2:(D3,C4))

1. Los corchetes calculan primero:
   • Solo hay un operador de unión entre paréntesis, por lo que se evalúa como D3,C4 .
2. El único operador que queda es el operador de rango. El propósito del operador de rango es crear un solo rango de 4 lados. Entonces, Excel crea un rango que abarca los 3 rangos. Esto sólo puede ser B2:D4 .
3. Esto lleva al cálculo final de =SUM(B2:D4) .

Resultado: 42

Aunque D4 no era una de las celdas de la fórmula, es necesario incluir D3 y C4 .

Si me dices que esto no te sorprendió, no te creeré.

Funciones que devuelven rangos de celdas

Uno de los desafíos de trabajar con rangos es cuando no sabemos la dirección de las celdas a incluir en la fórmula. Esto ocurre cuando necesitamos un cálculo para determinar el rango a utilizar; conocido como rango dinámico.

Afortunadamente, existen varias funciones que podemos utilizar para calcular una dirección de rango.

En lugar de hacer referencia a una celda, podemos incluir una fórmula que calcule el rango.

Usemos un ejemplo simple:

=SUM(B2:IF(A1=1,D4,D10))

• Cuando el valor en la celda A1 es 1 , IF devuelve una referencia de celda de D4 . Esto crea un rango de =SUM(B2:D4) .
• Cuando el valor de la celda A1 no es 1 , IF devuelve una referencia de celda de D10 . Esto crea una fórmula de =SUM(B2:D10) .

Entonces, dependiendo del valor en la celda A1 , el rango cambia de tamaño.

La sintaxis puede parecer confusa al principio, ya que hay una fórmula directamente después de los dos puntos. Excel calcula la fórmula primero y luego devuelve la dirección de la celda para crear el rango para la fórmula siguiente.

Esto crea una enorme flexibilidad porque podemos crear rangos sobre la marcha dentro de las fórmulas.

Las funciones que devuelven rangos son: ELEGIR, SI, IFS, ÍNDICE, INDIRECTO, COMPENSACIÓN, CAMBIAR y BUSCAR XL. Echemos un vistazo breve a cada una de estas funciones.

Para saber más sobre estas funciones, consulte el libro de Alan Murray, Fórmulas avanzadas de Excel .

ELEGIR

La función ELEGIR puede seleccionar diferentes rangos de celdas según un número de índice.

ELEGIR

Descripción

Utiliza index_num para devolver un valor de la lista de argumentos de valor. Utilice ELEGIR para seleccionar uno de hasta 254 valores según el número de índice.

Sintaxis

ELEGIR(núm_índice, valor1, [valor2],…)

• Index_num : especifica qué argumento de valor se selecciona. Index_num debe ser un número entre 1 y 254, o una fórmula o referencia a una celda que contenga un número entre 1 y 254.
  • Si index_num es 1, ELEGIR devuelve valor1; si es 2, ELEGIR devuelve valor2; etcétera.
  • Si index_num es menor que 1 o mayor que el número del último valor de la lista, ELEGIR devuelve el #VALOR. valor de error.
  • Si index_num es una fracción, se trunca al número entero más bajo antes de usarse.
• Valor1, valor2,… El valor 1 es obligatorio, los valores posteriores son opcionales. De 1 a 254 argumentos de valor entre los cuales ELEGIR selecciona un valor o una acción para realizar en función de index_num. Los argumentos pueden ser números, referencias de celdas, nombres definidos, fórmulas, funciones o texto.

Veamos un ejemplo del uso de ELEGIR para seleccionar un rango a devolver.

=SUM(CHOOSE(G4,B2:B5,C2:C5,D2:D5,E2:E5))

El primer argumento de la función ELEGIR es el número de índice. Los argumentos restantes son una lista de posibles resultados.

Usando el ejemplo anterior:

• El número de índice en la celda G4 es 2 .
• Por lo tanto, se devuelve el segundo elemento de los argumentos restantes, que son las celdas C2:C5 .
• La fórmula anterior se convierte en =SUM(C2:C5) .

Resultado: 16

SI

La función SI puede devolver un rango basado en lógica condicional.

SI

Descripción

Comprueba si se cumple una condición y devuelve un valor si es VERDADERO y otro valor si es FALSO

Sintaxis

SI(prueba_lógica, valor_si_verdadero, [valor_si_falso])

• prueba_lógica : la condición a probar.
• value_if_true : el valor que se devolverá si el resultado de la prueba lógica es VERDADERO.
• value_if_false (opcional): el valor que se devolverá si el resultado de la prueba_lógica es FALSO.

El siguiente es un ejemplo del uso de IF como un lado del operador de rango.

=SUM(C2:IF(G4=2,C5,D5))

Usando el ejemplo anterior:

• El IF se evalúa como VERDADERO porque la celda G4 es igual a 2 .
• El rango creado es C2:C5 .
• La fórmula anterior se convierte en =SUM(C2:C5) .

Resultado: 16

IFS

La función IFS puede calcular un rango basándose en una serie de pruebas lógicas y devuelve el primer valor que se evalúa como VERDADERO.

IFS (Excel 2019 y posteriores)

Descripción

La función IFS comprueba si se cumplen una o más condiciones y devuelve un valor que corresponde a la primera condición VERDADERA.

Sintaxis

=IFS([Algo es Verdadero1, Valor si es Verdadero1,Algo es Verdadero2,Valor si es Verdadero2,Algo es Verdadero3,Valor si es Verdadero3…)

Lo siguiente utiliza IFS para devolver un rango completo a la función SUMA.

=SUM(IFS(G4=1,B2:B5,G4=2,C2:C5,G4=3,D2:D5,G4=4,E2:E5))

• La celda G4 es igual a 2 ; por lo tanto, se devuelve el rango C2:C5 .
• La fórmula anterior se convierte en =SUM(C2:C5) .

Resultado: 16

ÍNDICE

La función ÍNDICE puede devolver la enésima fila y la enésima columna de un rango como referencia de celda.

ÍNDICE (forma de matriz)

Descripción

Devuelve el valor de un elemento en una tabla o matriz, seleccionado por los índices de número de fila y columna.

Sintaxis

ÍNDICE (matriz, núm_fila, [núm_columna])

• matriz : un rango de celdas o una constante de matriz.
  • Si la matriz contiene solo una fila o columna, el argumento correspondiente núm_fila o núm_columna es opcional.
  • Si la matriz tiene más de una fila y más de una columna, y solo se usa núm_fila o núm_columna, ÍNDICE devuelve una matriz de toda la fila o columna de la matriz.
• row_num : selecciona la fila de la matriz desde la cual devolver un valor. Si se omite núm_fila, se requiere núm_columna.
• column_num (opcional): selecciona la columna de la matriz desde la cual devolver un valor. Si se omite num_columna, se requiere num_fila.

El siguiente ejemplo utiliza ÍNDICE como un lado del operador de rango.

=SUM(C2:INDEX(B2:E5,G4,G5))

• Según el rango B2:E5 , ÍNDICE devuelve la cuarta fila (celda G4 ) y la segunda columna (celda G5 ). Que se calcula como C5 .
• El operador de rango crea un rango de C2:C5 .
• La fórmula anterior se convierte en =SUM(C2:C5) .

Resultado: 16

Con la función ÍNDICE, si el valor de la fila o columna es cero o está en blanco, el valor devuelto es la fila o columna completa.

=SUM(INDEX(B2:E5,G4,G5))

• Según el rango B2:E5 , ÍNDICE devuelve todas las filas (0 en la celda G4 ) de la segunda columna (celda G5 ). Que se calcula como C2:C5
• La fórmula anterior se convierte en =SUM(C2:C5)

Resultado: 16

INDIRECTO

INDIRECTO convierte una cadena de texto en un rango o referencia de celda.

INDIRECTO

Descripción

Devuelve la referencia especificada por una cadena de texto.

Sintaxis

INDIRECTO(texto_ref, [a1])

• Ref_text : una referencia a una celda que contiene una referencia de estilo A1, una referencia de estilo R1C1, un nombre definido como referencia o una referencia a una celda como una cadena de texto. Si ref_text no es una referencia de celda válida, INDIRECTO devuelve #REF. valor de error.
  • Si ref_text hace referencia a otro libro (una referencia externa), el otro libro debe estar abierto. Si el libro de origen no está abierto, INDIRECTO devuelve el mensaje #REF. valor de error.
• a1 (opcional). Un valor lógico que especifica qué tipo de referencia está contenida en la celda ref_text.
  • Si a1 es VERDADERO o se omite, ref_text se interpreta como una referencia de estilo A1.
  • Si a1 es FALSO, ref_text se interpreta como una referencia de estilo R1C1.

Echemos un vistazo a un ejemplo usando INDIRECTO.

=SUM(C2:INDIRECT(G4))

• INDIRECT(G4) convierte el valor de texto C5 en un rango.
• El rango creado por el operador sindical es C2:C5 .
• La fórmula anterior se convierte en =SUM(C2:C5) .

Resultado: 16

Advertencia

INDIRECT es una función volátil, por lo que debe usarse con cuidado.

COMPENSAR

La función DESPLAZAMIENTO puede crear un rango basado en la posición de una celda combinada con argumentos de ancho y alto.

COMPENSAR

Descripción

Devuelve una referencia a un rango que es un número específico de filas y columnas de una celda o rango de celdas. La referencia que se devuelve puede ser una sola celda o un rango de celdas. Podemos especificar el número de filas y el número de columnas que se devolverán.

Sintaxis

DESPLAZAMIENTO(referencia, filas, columnas, [alto], [ancho])

• Referencia : La referencia a partir de la cual basar el desplazamiento.
• Filas : el número de filas, arriba o abajo, a las que queremos que haga referencia la celda superior izquierda. Las filas pueden ser positivas (lo que significa debajo de la referencia inicial) o negativas (lo que significa encima de la referencia inicial).
• Cols : el número de columnas, a la izquierda o a la derecha, a las que queremos que haga referencia la celda superior izquierda del resultado. Las columnas pueden ser positivas (lo que significa a la derecha de la referencia inicial) o negativas (lo que significa a la izquierda de la referencia inicial).
• Alto (opcional): el alto es el número de filas que queremos que tenga la referencia devuelta. La altura debe ser un número positivo.
• Ancho (opcional): El ancho es el número de columnas que queremos que sea la referencia devuelta. El ancho debe ser un número positivo.

Echemos un vistazo a un ejemplo.

=SUM(OFFSET(B2,G4,G5,G6,G7)

• La función DESPLAZAMIENTO comienza en la celda B2 y luego crea un rango que tiene 0 filas hacia abajo ( G4 ), 1 columna de ancho ( G5 ), 4 filas de alto ( G6 ), 1 columna de ancho ( G7 ). Esto crea el rango C2:C5 .
• La fórmula anterior se convierte en =SUM(C2:C5) .

Resultado: 16

Advertencia:

OFFSET es una función volátil, por lo que debe usarse con cuidado.

CAMBIAR

La función CAMBIAR es una mezcla entre las funciones ELEGIR e IFS. Puede devolver un rango basado en la coincidencia del valor en el argumento de la expresión.

CAMBIAR (Excel 2019 en adelante)

Descripción

La función SWITCH evalúa un valor (llamado expresión) frente a una lista de valores y devuelve el resultado correspondiente al primer valor coincidente. Si no hay coincidencia, se puede devolver un valor predeterminado opcional.

Sintaxis

SWITCH(expresión, valor1, resultado1, [predeterminado o valor2, resultado2],…[predeterminado o valor3, resultado3])

• expresión : La expresión es el valor (como un número, fecha o algún texto) que se comparará con el valor1…valor126.
• valor1…valor126 : ValorN es un valor que se comparará con la expresión.
• resultado1…resultado126 : ResultadoN es el valor que se devolverá cuando el argumento valorN correspondiente coincide con la expresión. ResultN y debe proporcionarse para cada argumento de valorN correspondiente.
• predeterminado (opcional): el valor predeterminado es el valor que se devolverá en caso de que no se encuentren coincidencias en las expresiones del valor N. El argumento predeterminado se identifica por no tener una expresión de resultadoN correspondiente. El valor predeterminado debe ser el argumento final de la función.

En el siguiente ejemplo, SWITCH devuelve un rango completo a la función SUMA.

=SUMA(CAMBIAR(G4,1,B2:B5,2,C2:C5,3,D2:D5,4,E2:E5))

• El valor en G4 es 2 . El rango que sigue al valor 2 en la lista de argumentos es C2:C5 .
• La fórmula anterior se convierte en =SUM(C2:C5) .

Resultado: 16

BUSCARX

BUSCAR XL busca un valor de un rango y devuelve las celdas correspondientes de otro rango.

BUSCAR XL (Excel 2021 en adelante)

Descripción

La función BUSCAR XL busca un rango o una matriz y devuelve el elemento correspondiente a la primera coincidencia que encuentra en otra fila o matriz. Si no existe ninguna coincidencia, BUSCARX puede devolver la coincidencia más cercana (aproximada).

Sintaxis

BUSCARXL(valor_de_búsqueda, matriz_de_búsqueda, matriz_devolución, [si_no_encontrado], [modo_coincidencia], [modo_búsqueda])

• valor_buscado : El valor a buscar
• lookup_array : la matriz o rango a buscar
• return_array : la matriz o rango a devolver
• i f_not_found (opcional): cuando no se encuentra una coincidencia válida, devuelve el texto [if_not_found]. Si no se encuentra una coincidencia válida y falta [if_not_found], se devuelve #N/A.
• match_mode (opcional): especifique el tipo de coincidencia:
  • 0 : Coincidencia exacta. Si no se encuentra ninguno, devuelva #N/A. Este es el valor predeterminado.
  • -1 : Coincidencia exacta. Si no encuentra ninguno, devuelva el siguiente artículo más pequeño.
  • 1 : Coincidencia exacta. Si no encuentra ninguno, devuelva el siguiente artículo más grande.
  • 2 : Una coincidencia con comodín donde *, ? y ~ tienen un significado especial.
• search_mode (opcional): especifique el modo de búsqueda a utilizar:
  • 1 : Realice una búsqueda comenzando por el primer elemento. Este es el valor predeterminado.
  • – 1 : realiza una búsqueda inversa comenzando en el último elemento.
  • 2: Realice una búsqueda binaria que se base en que lookup_array esté ordenado en orden ascendente. Si no se ordenan, se devolverán resultados no válidos.
  • -2 : realiza una búsqueda binaria que se basa en que lookup_array se ordene en orden descendente. Si no se ordenan, se devolverán resultados no válidos.

En el siguiente ejemplo, lo mantendremos simple usando solo los argumentos requeridos para la función BUSCAR XL.

=SUM(C2:XLOOKUP(G4,B2:B5,C2:C5))

• BUSCARX encuentra el valor 8 (celda G4 ) del rango B2:B5 y devuelve el rango correspondiente de C2:C5 . Como 8 es la cuarta celda en B2:B5 , devuelve la cuarta celda en C2:C5 , que es C5
• El rango creado por el operador sindical es C2:C5
• La fórmula anterior se convierte en =SUM(C2:C5)

Resultado: 16

Otros operadores y sintaxis

Los operadores de rango en la primera sección se ocupan de la creación de rangos básicos a partir de referencias de celda estándar. Pero hay otros operadores que también debemos conocer.

• Marcador absoluto : Para congelar referencias de celdas.
• Operador de derrame : para trabajar con matrices dinámicas.
• Operador de intersección : para trabajar con referencias de celdas en la misma fila o columna.

Echemos un vistazo a cada uno de estos.

Marcador absoluto ($)

El marcador absoluto es el símbolo $ (dólar). Este es un símbolo que la mayoría de los usuarios entienden y con el que están familiarizados.

Cuando existe un símbolo $ antes de una letra de columna o un número de fila, esa fila o columna se congela al copiar/pegar la fórmula en otra ubicación.

Cuando la columna o la fila están congeladas, se conoce como referencia mixta. Cuando tanto la fila como la columna están congeladas, se conoce como referencia absoluta.

• Referencia relativa: A1
• Referencias mixtas: $A1 o A$1
• Referencia absoluta: $A$1

Después de seleccionar una referencia de celda, presionar la F4tecla recorre las 4 opciones: $A$1, A$1, $A1, A1 .

Excel ignora este símbolo $ cuando evalúa fórmulas; solo se usa al copiar o arrastrar fórmulas a otras celdas.

Se permite mezclar referencias absolutas, relativas y mixtas en un solo rango. Por ejemplo, A1:$D$10, $E4 tiene una sintaxis válida y contiene referencias relativas, absolutas y mixtas.

Operador de derrame

El operador de derrame es el símbolo # (almohadilla/libra). Este símbolo obtiene el rango completo ocupado para un cálculo de matriz dinámica.

En el siguiente ejemplo, la función ÚNICA genera una lista de valores únicos de la fuente original. Los datos podrían tener un número desconocido de valores únicos; por lo tanto, el rango ocupado por UNIQUE es de tamaño desconocido.

La fórmula en la celda G2 es:

=UNIQUE(E2:E5)

La fórmula en la celda G7 es:

=SUMA(G2#)

G2# proporciona una forma de hacer referencia a un rango devuelto por la fórmula en G2 , sin importar el número de filas o columnas producidas.

Referencia:

Para obtener más información sobre fórmulas de matrices dinámicas, consulte nuestra publicación: Matrices dinámicas en Excel: todo lo que NECESITA saber

Operador de intersección implícito

El operador de intersección implícito es el símbolo @ (arroba). Este símbolo se utiliza para devolver solo la celda que existe en la misma fila o columna que la fórmula que hace referencia al rango.

En el siguiente ejemplo, el rango C2:C5 se utiliza en cada fórmula. El símbolo @ devuelve sólo la celda que existe en la misma fila.

• La celda G2 devuelve el valor de la celda C2
• La celda G4 devuelve el valor de la celda C4
• ¡ La celda G6 devuelve el #VALOR! error ya que no hay valores en C2:C5 que existan en la misma fila que la fórmula.

Este enfoque funciona tanto para filas como para columnas.

Conclusión

Ahora eres un ninja de Excel Range. Tienes todas las herramientas que necesitas para crear modelos de Excel muy potentes (pero simples). El único límite ahora es tu creatividad.

---