ztevenreal
Solía ver el doble divisa (–) en las hojas de cálculo de Excel y me preguntaba qué hacía. Es un senda esencial para exigir a Excel a tratar VERDADERO y FALSO como los números 1 y 0, lo que significa que puede sumar o contar fácilmente los resultados de una prueba razonamiento en un rango. Ahora lo sé y quiero compartir este poderoso truco contigo.
La función principal: coerción booleana a numérica
El doble-unario fuerza un valía de un tipo de datos (booleano) a otro (matemático), un proceso llamado tipo coerción. Déjame mostrarte un ejemplo sencillo.
Esta fórmula devuelve VERDADERO porque el valía de la celda A1 es maduro que 10:
=A1>10
Un solo signo menos en la fórmula convierte VERDADERO en su equivalente matemático placa (-1):
=-(A1>10)
Añadir un segundo menos (por lo tanto, el signo doble-unario) invierte el número a 1.
=--(A1>10)
Si el valía de la celda A1 no cumple con los criterios booleanos, el doble unario devuelve 0.
La mentor para principiantes de razonamiento booleana en Microsoft Excel
Aumente su dicha booleana.
El problema: por qué las matrices lógicas fallan en sumas simples
Cuando crea una matriz de resultados lógicos, la función SUMA manejo VERDADERO y FALSO como no numéricos y los ignora, lo que da como resultado 0.
En esta hoja de trabajo, suponga que está monitoreando los totales de ventas semanales y si cumplen con la meta de $500. La fórmula en la celda C2 arroja una matriz que devuelve VERDADERO y FALSO según si los títulos correspondientes en la columna B cumplen con los criterios:
=B2:B6>=F2
Ahora, en superficie de decirle si alcanzó el objetivo de cada semana individualmente, desea que Excel cuente cuántos totales de ventas alcanzaron el objetivo en normal. Sin incautación, si envuelve la fórmula susodicho interiormente de SUMA, devuelve cero porque los títulos booleanos no son numéricos:
=SUM(B2:B6>=F2)
En este caso específico, usted podría utilizar CONTAR.SI. Sin incautación, estoy usando la función SUMA sólo para demostrar cómo el cirujano doble-unario fuerza la conversión numérica de la matriz subyacente.
El doble-unario convierte los nociones de la matriz, lo que significa que la función SUMA puede procesarlos:
=SUM(--(B2:B6>=F2))
Aquí, detrás de imagen, Excel convierte la matriz de {VERDADERO, FALSO, VERDADERO, FALSO, FALSO} a {1, 0, 1, 0, 0}, razón por la cual obtenemos el recuento correcto de 2.
El caso de uso de energía: contar cuando falta CONTAR.SI
El cirujano doble-unario efectivamente brilla cuando necesita realizar una razonamiento de matriz compleja que funciones integradas como CONTAR.SI no pueden ejecutar. La principal término de COUNTIFS es que sus criterios son estáticos: no se pueden comparar dinámicamente con rangos fila por fila.
Imagine que necesita contar cuántos vendedores excedieron sus objetivos individuales en esta hoja de cálculo.
Para hacer esto correctamente, debe comparar cada celda de la columna Ventas con su vecina en la columna Objetivo. Si intenta utilizar CONTAR.SI para hacer esto, se produce un resultado inesperado:
=COUNTIFS(B2:B6,">"&C2:C6)
Esto se debe a que, en superficie de comparar B2 con C2, B3 con C3, etc., Excel manejo el rango completo (C2:C6) como una matriz de múltiples criterios que se aplicarán al mismo rango (B2:B6). Esto devuelve una serie de resultados incorrectos ({3, 1, 3, 2, 3}) porque cuenta cuántas ventas son mayores que cada valía de objetivo individual en toda la repertorio. En otras palabras, tres totales de ventas son mayores que 81, uno es maduro que 97, tres son mayores que 71, y así sucesivamente.
En su superficie, utiliza SUMPRODUCT con el cirujano doble-unario. Esto obliga a Excel a evaluar la prueba razonamiento fila por fila:
=SUMPRODUCT(--(B2:B6>C2:C6))
Así es como funciona:
- Prueba razonamiento: La comparación (B2:B6>C2:C6) devuelve la matriz correcta de títulos booleanos: {FALSO, FALSO, VERDADERO, FALSO, VERDADERO}.
- Tipo coerción: El cirujano doble-unario (–) convierte esta matriz booleana en una matriz numérica: {0, 0, 1, 0, 1}.
- Suma final: La función SUMPRODUCT agrega los nociones: 0+0+1+0+1 para devolver 2, que es el recuento correcto.
Al igual que COUNTIFS, SUMIFS asimismo se limita a comprobar criterios estáticos. Entonces, por ejemplo, si desea sumar las ventas solo cuando exceden la traspaso promedio, asimismo debe usar la función SUMAPRODUCTO con el cirujano doble-unario.
¿Por qué utilizar SUMPRODUCT y no SUM?
En este ejemplo, podría utilizar SUMA en superficie de SUMAPRODUCTO. Sin incautación, la diferencia entre los dos tiene que ver con la compatibilidad. SUMPRODUCT maneja matrices de forma nativa, tomando automáticamente la matriz numérica generada por el cirujano doble-unario y sumando los nociones. En Excel para Microsoft 365 y las versiones perpetuas lanzadas en 2021 o posteriores, SUM puede hacer esto automáticamente, aunque en versiones anteriores, debe presionar Ctrl+Shift+Enter para confirmar la fórmula, que la envuelve entre llaves:
{=SUM(--(B2:B6>C2:C6))}
En versiones anteriores de Excel, si confirma esta fórmula con un simple Enter, devolverá un error o un valía incorrecto. Por lo tanto, SUMPRODUCT es más simple, seguro y compatible con versiones anteriores.
6 funciones que cambiaron la forma de utilizar Microsoft Excel
Las funciones de matriz dinámica cambiaron las reglas del colección.
¿Qué tal métodos alternativos?
Correcto a que Excel convierte VERDADERO y FALSO en 1 o 0 si ve un símbolo matemático directamente ayer de ellos, es puntual preguntarse si el cirujano doble-unario es efectivamente necesario. Legado que cualquier operación aritmética realiza este tipo de coerción, existen fórmulas alternativas que logran el mismo resultado:
Puedes multiplicar toda la matriz razonamiento por 1:
=SUMPRODUCT((B2:B6>=F2)*1)
Asimismo puedes adicionar 0 a la matriz:
=SUMPRODUCT((B2:B6>=F2)+0)
Estos métodos convierten con éxito títulos booleanos en números, lo que significa que la matriz se vuelve contable. Sin incautación, sigo prefiriendo usar el cirujano doble-unario por estos motivos:
- Señala claramente la intención: El cirujano doble-unario es el método de coerción booleano a matemático internacionalmente aceptado. Su único propósito es la conversión de tipos de datos, y verlo inmediatamente les dice a todos los que ven la hoja de cálculo exactamente qué está haciendo la fórmula. Usar *1 o +0 es menos manifiesto y podría confundirse con un ajuste aritmético separado del resultado.
- Está más honesto y menos enredado: El cirujano doble-unario es la forma más concisa de aplicar una operación de coerción matemática a una matriz. Por ejemplo, si tiene una fórmula matricial anidada compleja, dos guiones simples ayudan a apoyar la razonamiento normal simple.
- Es marginalmente más capaz: Si proporcionadamente la diferencia es insignificante, el cirujano doble-unario puede ser más capaz en libros de trabajo extremadamente grandes. Cuando usa *1 o +0, Excel debe procesar esto como una operación separada en cada celda; sin incautación, el cirujano doble-unario simplemente convierte el tipo de datos de los títulos de la matriz.
Entonces, si proporcionadamente las alternativas son perfectamente funcionales, el cirujano doble-unario es la forma más limpia, capaz y profesional de realizar la coerción booleana a numérica.
El doble divisa no es el único símbolo de Excel que puede desbloquear el control innovador de hojas de cálculo y fórmulas. Por ejemplo, el símbolo # es vitalista en las versiones modernas del software para incluir todos los resultados de una matriz dinámica en una fórmula, y el signo @ devuelve un valía único de un rango o matriz.
- SO
-
Windows, macOS, iPhone, iPad, Android
- Prueba gratuita
-
1 mes
Microsoft 365 incluye acercamiento a aplicaciones de Office como Word, Excel y PowerPoint en hasta cinco dispositivos, 1 TB de almacenamiento OneDrive y más.
