Autor: Oscar Cronquist Artículo actualizado en noviembre 08, 2019

Este artículo muestra una fórmula que le permite extraer valores no vacíos en las columnas según una condición. La imagen de arriba muestra la condición en la celda B9 y la fórmula en el rango de celdas B10: B14.

El conjunto de datos está en el rango de celdas A2: E7 y la columna de búsqueda es la columna A. La fórmula devuelve valores de varias filas si el valor correspondiente en la columna de búsqueda coincide, un valor en cada celda.

Jim pregunta:

Descargué el archivo lookup-vba3. Creo que puedo usar esto para ayudarme a completar un calendario. Sustituí las fechas por Bolígrafo, Papel y Borrador. Luego tuve ubicaciones sustituidas por valores de $. Donde tengo una fecha de, por ejemplo, 11 / 27 / 12, tengo ubicaciones de 10 entregando ese día.

Usando la plantilla como se muestra en la captura de pantalla en "Devolver múltiples valores horizontal o verticalmente (VBA)".

No puedo expandir más allá de la columna "C" para devolver varios valores. Creo que está en el código de la matriz, pero no puedo entender cómo devolver valores más allá de la columna C.

Si puedes ayudar, lo aprecio mucho!

Gracias,
Jim

Respuesta

Fórmula de matriz en la celda B10:

=IFERROR(INDEX($B$2:$E$7, SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1)), 1/(SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), ""), ROW(A1))-SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1)))), "")

Cómo crear una fórmula matricial

  1. Seleccione la celda B10.
  2. Haga clic en la barra de fórmulas.
  3. Pegue la fórmula de la matriz por encima.
  4. Mantenga presionado CTL + SHIFT simultáneamente.
  5. Pulse Intro.

Cómo copiar la fórmula

  1. Seleccione la celda B10.
  2. Copiar celda (Ctrl + c).
  3. Seleccionar rango de celdas B11: B15.
  4. Pegar (Ctrl + v).

La siguiente fórmula de matriz concatena los valores devueltos, la función TEXTJOIN puede hacer que la fórmula sea mucho más pequeña.

Fórmula de matriz en la celda B10:

=TEXTJOIN(" ,",TRUE,IF((A2:A7=B9)*(B2:E7<>""),B2:E7,""))

Explicando la fórmula matricial en la celda B10.

La Función ÍNDICE devuelve un valor o una referencia de la celda en la intersección de una columna y fila en particular, en un rango dado.

ÍNDICE ($ B $ 2: $ E $ 7, row_num, column_num)

Los primeros tres pasos siguientes calculan el row_nums y los pasos restantes calculan column_nums.

Paso 1 - Encuentra fechas coincidentes y no espacios en blanco

El signo igual es un operador lógico. le permite comparar el valor en la celda B9 con el rango de celdas A2: A7, la expresión lógica devuelve VERDADERO si es igual y FALSO si no.

$ A $ 2: $ A $ 7 = $ B $ 9

se convierte en

{40909; 40910; 40911; 40912; 40913; 40909} = 40909

se convierte en

{CIERTO; FALSO; FALSO; FALSO; FALSO; CIERTO}

Los caracteres menor y mayor que también son operadores lógicos, comprueban si los valores en el rango de celdas B2: E7 no están en blanco.

$ B $ 2: $ E $ 7 <> ""

se convierte en

{"Nueva York", "Los Ángeles", 0, "Chicago"; 0, "Houston", 0,0; "Filadelfia", "Phoenix", "San Antonio", 0; "San Diego", 0, "Dallas", "San José"; "Jacksonville", 0,0, "Indianápolis"; 0, "Austin", 0, "San Francisco"} <> ""

se convierte en

{VERDADERO, VERDADERO, VERDADERO, VERDADERO; VERDADERO, VERDADERO, VERDADERO, VERDADERO; VERDADERO, VERDADERO, VERDADERO, VERDADERO; VERDADERO, VERDADERO, VERDADERO, VERDADERO; VERDADERO, VERDADERO, VERDADERO, VERDADERO; VERDADERO, VERDADERO, VERDADERO}

Los paréntesis le permiten manipular el orden de cálculo que es realmente importante en este paso. El asterisco es un carácter que multiplica las dos matrices, VERDADERO * VERDADERO = VERDADERO (1), VERDADERO * FALSO = FALSO (0) y FALSO * FALSO = FALSO (0). Esto significa que la lógica AND se aplica a las dos matrices.

Puede multiplicar matrices con diferentes tamaños siempre que siga ciertas reglas, en este caso, estoy multiplicando una matriz que tiene el mismo número de filas que la otra matriz.

($A$2:$A$7=$B$9)*($B$2:$E$7<>"")

devoluciones

{1, 1, 0, 1; 0, 0, 0, 0; 0, 0, 0, 0; 0, 0, 0, 0; 0, 0, XNX, XNX, XN, XN, XN, XN, XN, XN, XN, XNXX

1 es lo mismo que VERDADERO y 0 (cero) es FALSO. Excel convierte los valores booleanos a sus equivalentes numéricos cuando realiza cálculos aritméticos entre dos o más matrices.

Paso 2 - Devuelve los números de fila correspondientes

IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), "")

se convierte en

IF ({1, 1, 0, 1; 0, 0, 0, 0; 0, 0, 0, 0; 0, 0, 0, 0; 0, 0, XNX, XNX, XNX, XNX, XNUM, XNXX, 0 }, {0; 0; 1; 0; 1; 1}, "")

y devoluciones

{1, 1, "", 1; "", "", "", "", "", "", "", "", "", "", "," "," "," "", 6, "", 6}

Cómo utilizar la función IF

Comprueba si se cumple una expresión lógica. Devuelve un valor específico si es VERDADERO y otro valor específico si es FALSO.

Cómo utilizar la función IF

Paso 3 - Devuelve el k-ésimo valor más pequeño

PEQUEÑO (matriz, k)

SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1))

se convierte en

PEQUEÑO ({1, 1, "", 1; "", "", "", ""; "", "", "", ""; "", "", "", ""; "" , "", "", ""; "", 6, "", 6}, 1)

y devuelve 1.

Cómo usar la función PEQUEÑA

La función PEQUEÑA le permite extraer un número en un rango de celdas en función de cuán pequeño es en comparación con los otros números del grupo.

Cómo usar la función PEQUEÑA

Paso 1 - Encuentra fechas coincidentes y no espacios en blanco

($A$2:$A$7=$B$9)*($B$2:$E$7<>"")

se convierte en

({40909; 40910; 40911; 40912; 40913; 40909} = 40909) * ({"New York", "Los Angeles", 0, "Chicago"; 0, "Houston", 0,0; "Philadelphia", "Phoenix "," San Antonio ", 0;" San Diego ", 0," Dallas "," San José ";" Jacksonville ", 0,0," Indianápolis ", 0," Austin ", 0," San Francisco "} <> "")

y devoluciones

{1, 1, 0; 1, 0, 0, jCJ.J.J.J.J.J.J.JPGGGJPGPJPGGGGJPGGGGGGGGGGG XXUMX, 0; 0;

Paso 2: calcula los números de fila y columna

IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), "")

se convierte en

IF ({1, 1, 0, 1; 0, 0, 0, 0; 0, 0, 0, 0; 0, 0, 0, 0; 0, 0, XNX, XNX, XNX, XNX, XNUM, XNXX, 0 }, {0; 0; 1; 0; 1; 1} + {2}, "")

y devoluciones

{2,1.5, "", 1.25; "", "", "", "", "", "", "", "", "", "", "", ", "", "" "", 6.5, "", 6.25}

Paso 3 - Devuelve el k-ésimo valor más pequeño

PEQUEÑO (matriz, k)

SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), ""), ROW(A1))

se convierte en

SMALL({2,1.5,"",1.25;"","","","";"","","","";"","","","";"","","","";"",6.5,"",6.25}, 1)

y devuelve 1.25.

Paso 4 - Restar números de fila

SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), ""), ROW(A1))-SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1))

se convierte en

PEQUEÑO ({2,1.5, "", 1.25; "", "", "", ""; "", "", "", ""; "", "", "", ""; "", " "," "," ";" ", 6.5," ", 6.25}, 1) -SMALL ({1, 1," ", 1;" "," "," "," ";" "," "," "," ";" "," "," "," ";" "," "," "," ";" ", 6," ", 6}, ROW (A1))

se convierte en

1.25-1

y devuelve 0.25

Paso 5 - Calcular el número de columna

1/(SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), ""), ROW(A1))-SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1)))

se convierte en

1 / 0.25 y devuelve el número de columna 4.

Cálculo final en la celda B10

La función ÍNDICE usa el número de fila y columna para determinar qué valor devolver.

IFERROR(INDEX($B$2:$E$7, SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1)), 1/(SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), ""), ROW(A1))-SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1)))), "")

se convierte en

IFERROR (ÍNDICE ($ B $ 2: $ E $ 7, 1, 4), "")

se convierte en

IFERROR ("Chicago", "")

y devuelve "Chicago" en la celda B10.

Si la función ÍNDICE devuelve un valor de error, el Función de IFERROR detecta el error y devuelve un "" en blanco.

Tenga en cuenta que use el IFERROR con precaución. Maneja todos los errores que pueden hacer que sea muy difícil detectar errores en sus fórmulas.