Diga que quiere poder contar elementos únicos de una lista, pero con un giro. Y digamos que está trabajando con esta hoja de trabajo:
La columna D cuenta el número de filas en cada una de las secciones de la columna B, y la columna C cuenta el número de secciones únicas según los primeros cinco caracteres de la columna A para esa sección. Las celdas B2: B11 contienen ARG y puede contar ocho elementos únicos en los primeros cinco caracteres de A2: A11 porque A7: A9 contienen 11158 cada uno, por lo que los dos duplicados no se cuentan. De manera similar, el 5 en D12 le dice que hay cinco filas para BRD, pero dentro de las filas 12:16, hay tres elementos únicos de los primeros cinco caracteres, ya que 11145 se repite y 11173 se repite.
Pero, ¿cómo le dices a Excel que haga esto? ¿Y qué fórmula podría usar en C2 que se pueda copiar a C12 y C17?
La fórmula de conteo simple en D2`` =COUNTIF(B:B,B2)cuenta el número de veces que B2 (ARG) existe en la columna B.
Utiliza una columna auxiliar para aislar los primeros cinco caracteres de la columna A, como en esta figura:
A continuación, debe indicar de alguna manera que para ARG, solo está interesado en las celdas F2: F11 para encontrar la cantidad de elementos únicos. En general, encontrará este valor utilizando la fórmula de matriz que se muestra en esta figura:
Utiliza la celda C3 temporalmente solo para mostrar la fórmula; puede ver que no está presente en C3 en figuras anteriores. (En breve aprenderá cómo funciona esta fórmula).
Entonces, ¿cuál es la fórmula en C2, C12 y C17? La respuesta sorprendente (y genial) se muestra en esta figura:
¡Guau! ¿Como funciona esto?
Eche un vistazo a Respuesta en los nombres definidos en esta figura:
Es la misma fórmula de una figura anterior, pero en lugar de usar el rango F2: F11, usa un rango llamado Rg. Además, la fórmula era una fórmula de matriz, ¡pero las fórmulas con nombre se tratan como si fueran fórmulas de matriz! Es decir, =Answerno se ingresa con Ctrl + Shift + Enter, sino que simplemente se ingresa como de costumbre.
Entonces, ¿cómo se define Rg? Si se selecciona la celda C1 (que es un paso importante para comprender este truco), entonces se define como en esta figura:
Eso es =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details es el nombre de la hoja, pero puede ver esta fórmula sin el nombre largo de la hoja. Una manera fácil de hacer esto es nombrar temporalmente la hoja con algo simple, como x, y luego mirar nuevamente el nombre definido:
¡Esta fórmula es más fácil de leer!
Puede ver que esta fórmula coincide con $ B1 (observe la referencia relativa a la fila actual) con toda la columna B y resta 1. Resta 1 porque está usando OFFSET de F1. Ahora que conoce la fórmula de C, eche un vistazo a la de C2:
La MATCH($B2,$B:$B,0)parte de la fórmula es 2, por lo que la fórmula (sin la referencia al nombre de la hoja) es:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
o:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
o:
=OFFSET($F$1,1,0,10,1)
Como COUNTIF($B:$B,$B2)es 10, hay 10 ARG. Este es el rango F2: F11. De hecho, si se selecciona la celda C2 y presiona F5 para ir a Rg, verá esto:
Si la celda inicial fuera C12, presionar F5 para ir a Rg produce esto:
Así que ahora, con Respuesta definida como =SUM(1/COUNTIF(rg,rg)), ¡ya está!
Veamos más de cerca cómo funciona esta fórmula, usando un ejemplo mucho más simple. Normalmente, la sintaxis de COUNTIF es =COUNTIF(range,criteria), como =COUNTIF(C1:C10, "b")en esta figura:
Esto daría 2 como el número de b en el rango. Pero al pasar el rango en sí como criterio, se utiliza cada elemento del rango como criterio. Si resalta esta parte de la fórmula:
y presione F9, verá:
Se evalúa cada elemento del rango y esta serie de números significa que hay una a y dos b, tres c y cuatro d. Estos números se dividen en 1, dando 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, como puede ver aquí:
Así que tienes 2 mitades, 3 tercios, 4 cuartos y 1 entero, y sumarlos da 4. Si un elemento se repitiera 7 veces, entonces tendrías 7 séptimos y así sucesivamente. ¡Muy genial! (Felicitaciones a David Hager por descubrir / inventar esta fórmula).
Pero espera un minuto. Tal como está, solo debe ingresar esta fórmula en C2, C12 y C17. ¿No sería mejor si pudiera ingresarlo en C2 y completarlo y mostrarlo solo en las celdas correctas? De hecho, puedes hacer esto. Puede modificar la fórmula en C2 para que sea =IF(B1B2,Answer,""), y cuando la completa, hace el trabajo:
Pero, ¿por qué detenerse aquí? ¿Por qué no convertir la fórmula en una fórmula con nombre, como se muestra aquí?
Para que esto funcione, la celda C2 debe ser la celda activa (o la fórmula debería ser diferente). Ahora puede reemplazar las fórmulas de la columna C con =Answer2:
Puede ver que C3 tiene =Answer2, al igual que todas las celdas en la columna C. ¿Por qué no continuar con esto en la columna D? La fórmula en D2, después de aplicar también la comparación a B1 y B2, se muestra aquí:
Entonces, si mantiene la celda D2 seleccionada y define otra fórmula, diga Respuesta3:
luego puede ingresar =Answer3en la celda D2 y completar:
Aquí está la parte superior de la hoja de trabajo, con fórmulas que se muestran, seguida de la misma captura de pantalla con valores que muestran:
Cuando otras personas intentan resolver esto, ¡pueden rascarse la cabeza al principio!
Este artículo invitado es de Bob Umlas, MVP de Excel. Es del libro, Más Excel fuera de la caja. Para ver los otros temas del libro, haga clic aquí.
