Explicación de las soluciones de Excel para diferentes versiones

Excel ha recorrido un largo camino durante la última década y cada versión contiene diferentes soluciones para una serie de problemas.

Recientemente resolví un problema de fechas en una hoja de cálculo. Necesitaba una fórmula para encontrar condicionalmente la fecha más temprana que coincidiera con otros dos criterios de una tabla. La solución depende de la versión de Excel que esté usando.

Excel ha cambiado drásticamente en los últimos 10 años. Más que nunca, la versión de su software determina lo que puede hacer y lo fácil que es resolverlo.

La tabla con la que estoy trabajando se muestra en Figura 01.

Necesito saber la próxima fecha de revisión de cada proyecto. Dicho esto, necesito la fecha más temprana en la columna D donde hay una celda en blanco en la columna E.

Mi informe aparecerá en Figura 02. Las columnas contienen cuatro soluciones diferentes. La línea 1 contiene las versiones en las que funcionan. La línea 2 describe la solución.

Todas las fórmulas de la línea 4 en Figura 02 se muestran en Figura 03.

Se han copiado todas las fórmulas de la línea 4. Tienes que recurrir a ello Figura 03 cómo explico estas fórmulas. Las fórmulas son bastante complejas, y doy más detalles en el video que acompaña a este artículo.

La fórmula de la celda K9 aún no se ha mostrado y se discutirá en último lugar.

Para identificar o devolver una celda en blanco, use dos comillas como esta: “”.

Todas las versiones – matriz – columna H.

Las fórmulas de matriz son más complejas que la mayoría de las fórmulas. Las fórmulas de matriz pueden funcionar con rangos de la misma manera que las fórmulas estándar funcionan con celdas individuales. Debe usar una combinación de teclado especial para ingresar matrices.

Mantenga presionadas las teclas Ctrl + Shift y presione Entrar. Esto envuelve las llaves (llaves) alrededor de la fórmula. Puede verlos en la primera fórmula enumerada en Figura 03.

Esta fórmula de matriz calcula la primera función SI primero. Esto compara cada celda en el área A2: A13 con el código del proyecto en la celda G4. Si una celda coincide con el código del proyecto, se mueve a la siguiente función SI y busca una celda vacía en la fila correspondiente en el área E2: 13.

Si se encuentra una celda vacía, se devuelve la fila correspondiente de D2: D13. Si alguna de las pruebas es FALSA, se devuelve una celda vacía.

Juntas, las dos funciones SI proporcionan un conjunto de fechas y celdas en blanco para que la función MIN (que devuelve el valor mínimo) las verifique. La función MIN ignora las celdas vacías.

Las fechas restantes se comparan para determinar la fecha más temprana (mínima). Este tipo de cálculo con fórmulas normales requeriría varias celdas. Una fórmula de matriz puede hacer todos los cálculos en una sola celda.

Excel 2010 y posterior – Agregado – Columna I.

La función AGREGAR tiene la capacidad inusual de ignorar las celdas de falla. Esta capacidad nos permite crear una fórmula única que funciona como una fórmula de matriz pero no es una matriz.

La función AGREGAR es similar a la función SUBTOTAL. Indican qué cálculo se debe realizar. El “15” al principio define la función PEQUEÑO. La función PEQUEÑO es una versión flexible de la función MIN.

La función MIN devuelve el valor mínimo en un rango. La función PEQUEÑO puede encontrar el más bajo o el segundo más bajo especificando un número de posición: 1 encuentra el más bajo, 2 encuentra el segundo más bajo.

El segundo argumento de AGGREGATE tiene 6, y esto le dice que ignore los errores.

En Excel, las fechas son números. Cada fecha se basa en un número secuencial. En la función AGREGAR, dividimos el rango de fechas por el resultado de un cálculo condicional. Este cálculo considera dos condiciones separadas. Así es como funciona este cálculo.

Cualquier celda del rango puede devolver VERDADERO o FALSO. Si multiplicamos las dos condiciones juntas, convierten VERDADERO en 1 y FALSO en 0. El resultado de A2 se multiplica por el resultado de E2 y así sucesivamente en los rangos.

Con dos condiciones, hay cuatro posibilidades y dos posibles resultados, como se muestra en Figura 04.

Si multiplica algo por cero (FALSO) devolverá cero como puede ver en Figura 04. La única vez que se devuelve 1 es cuando ambas condiciones son VERDADERAS.

Dividimos los datos por estos resultados de condición. Cualquier condición que no coincida es cero. Dividir por cero devuelve un error.

Le dijimos a la función AGREGAR que ignorara los errores. Si ambas condiciones coinciden, la fecha se divide por 1, dejándola sin cambios. Los datos restantes cumplen ambos criterios. Son analizados por la función PEQUEÑA para determinar la fecha más baja.

El 1 al final de la función AGREGAR le dice a la función PEQUEÑA que devuelva el valor más bajo (más temprano) de los datos.

Se agregó la función IFERROR para manejar el error que se genera cuando no se cumple ninguna condición.

Esto ocurre cuando no hay una celda vacía para un código de proyecto en particular.

Excel 2019 y superior – MINIFS – Solución preferida – Columna J.

Así como la función SUMIFS habilita la suma condicional, la función MINIFS habilita un cálculo condicional mínimo.

La primera área, D2: D13, es el área donde se puede encontrar el mínimo.

Luego acopla un área de condición A2: A13 con una condición G4. Usted agrega condiciones agregando otro rango de condiciones, E2: E13, y otra condición, “”. Todos estos están separados por comas.

Dado que esta función se creó para satisfacer nuestras necesidades, es mi solución recomendada. Desafortunadamente, solo está disponible en Excel 2019 y la versión de suscripción. Tenga en cuenta que también hay una función MAXIFS.

Solo versión de suscripción – matrices dinámicas – columna K.

La versión de suscripción de Excel está disponible a través de Microsoft 365 (anteriormente Office 365). Esta versión se actualiza periódicamente. En 2020 recibió la actualización de matrices dinámicas que cubrí en tres artículos separados en mayo, junio y julio de 2020.

Las matrices dinámicas cambian la forma en que se calculan las fórmulas. Con respecto a las fórmulas de matriz, ya no necesita escribirlas usando Ctrl + Shift + Enter. Las fórmulas en las celdas H4 y K4 son idénticas excepto por los corchetes en ambos extremos.

El cálculo en la versión de suscripción funciona igual que la fórmula de matriz descrita anteriormente. No recomiendo usar esta solución de matriz dinámica ya que la función especial MINIFS está disponible en la versión de suscripción.

Esta fórmula muestra que el nuevo motor de cálculo de Excel puede manejar una fórmula de celda única que funciona con condiciones basadas en rangos, no solo celdas individuales. Lo he incluido para que veas cómo podemos adaptarlo a necesidades más exigentes.

Por ejemplo, en lugar de buscar la primera fecha de un código de proyecto, queremos buscar la primera fecha de la primera letra del código de cuenta en la columna C. Diferentes prefijos pueden referirse a diferentes categorías de cuentas. Figura 05 consiguió el informe. La fórmula en K9 está contenida en Figura 03. La función MINIFS no puede manejar este tipo de condición.

Las matrices dinámicas le permiten manipular áreas con otras funciones. La función IZQUIERDA extrae caracteres del lado izquierdo de una celda. Podemos usarlo para extraer el primer carácter del área de la columna Cuenta.

Podemos reemplazar la condición del código del proyecto con una condición que extraiga el primer carácter de cada celda en el rango C2: C13. Luego, esto se compara con la letra de la celda J9 para devolver VERDADERO o FALSO para cada celda del rango.

Excel ha evolucionado a lo largo de los años. Pasamos de fórmulas de matriz, que son complejas y requieren una entrada de teclado especial, a una función que funciona como una matriz, y luego a una función dedicada que está diseñada para realizar cálculos condicionales mínimos.

Finalmente, Excel tiene fórmulas que pueden manejar condiciones flexibles para rangos que usan matrices dinámicas.

Asegúrese de ver el video que acompaña a este artículo, que explica con más detalle cómo funcionan estas fórmulas.

El video adjunto y el archivo de Excel se describen con más detalle para demostrar estas técnicas.

Neale Blackwood CPA realiza contabilidad A4 y ofrece capacitación en Excel, seminarios web y servicios de consultoría. Las preguntas se pueden enviar a [email protected]