Soluciones de Excel explicadas para diferentes versiones

Excel ha evolucionado enormemente durante la última década y cada versión contiene diferentes soluciones para una serie de problemas.

Recientemente, estaba resolviendo un problema de fecha en una tabla. Necesita una fórmula para encontrar condicionalmente la fecha más cercana que coincida con otros dos criterios de una tabla. La solución depende de la versión de Excel que esté usando.

Durante los últimos diez años, Excel ha cambiado drásticamente. Ahora más que nunca, la versión de su software determina qué puede hacer y qué tan fácil es la solución.

El horario con el que estoy trabajando se muestra en Figura 01.

Necesito saber la próxima fecha de revisión para cada proyecto. Esto significa que necesito la fecha más cercana en la columna D, ya que hay una celda en blanco en la columna E.

Mi informe se muestra en Figura 02. Las columnas tienen cuatro soluciones diferentes. La fila 1 contiene las versiones con las que funciona. El grado 2 describe la solución.

Figura 02.

Todas las fórmulas son del grado 4 en Figura 02 aparece en Figura 03.

Figura 03.

Todas las fórmulas de la fila 4 se han copiado. Deberá consultar Figura 03 También explico estas fórmulas. Las fórmulas son razonablemente complejas y proporciono 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 estará finalizada.

Para definir o devolver una celda en blanco, puede usar comillas dobles juntas 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 lo hacen las fórmulas estándar para celdas individuales. Debe usar un conjunto de teclado especial para ingresar matrices.

Mantenga presionadas las teclas Ctrl y Shift y luego presione la tecla Enter. Esto coloca los corchetes (llaves) alrededor de la fórmula. Puede verlo en la primera versión que aparece en Figura 03.

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

READ  Los 3 mejores servidores de construcción libre de Minecraft

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

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

Las fechas restantes se comparan para determinar la fecha más temprana (la más baja). Este tipo de cálculo que utiliza fórmulas normales requiere que se realicen varias celdas. La fórmula de matriz puede realizar todos los cálculos en una celda.

Excel 2010 y versiones posteriores – agrupación – primera columna

La función AGREGAR tiene la capacidad inusual de ignorar las celdas de error. 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 la misma que la función SUBTOTAL. Usted especifica el cálculo a realizar. El número «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 el número de posición: 1 encuentra el más bajo, 2 segundos el más bajo.

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

En Excel, las fechas son números. Cada fecha tiene un número de serie principal. En la función AGREGAR, dividimos el rango de fechas por el resultado del cálculo condicional. Esta cuenta analiza dos condiciones distintas. Así es como funciona este cálculo.

Cada celda del rango puede devolver VERDADERO o FALSO. Cuando multiplicamos las dos condiciones juntas, convierten VERDADERO en 1 y FALSO en 0. Y el resultado de A2 se multiplicará por el resultado de E2 y así sucesivamente por debajo de los rangos.

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

READ  Lifeweaver está haciendo que los entusiastas trabajen con su nuevo sistema de control

Figura 04.

Cuando multiplica cualquier cosa por cero (FALSO), devuelve cero, como puede ver en Figura 04. La única vez que 1 regresa es cuando ambas condiciones son VERDADERAS.

Dividimos las fechas de los resultados para este caso. Cualquier condición que no coincida será cero. La división por cero devuelve un error.

Dirigimos la función AGREGAR para ignorar los errores. Cuando ambas condiciones coinciden, la fecha se dividirá por 1, dejándola sin cambios. Las fechas restantes coincidirán con ambos criterios. Es analizado por la función PEQUEÑO para determinar la fecha mínima.

El número 1 al final de la función AGREGAR indica a la función PEQUEÑA que devuelva el valor más bajo (más antiguo) de las fechas.

Se ha agregado la función IFERROR para manejar el error generado cuando no se cumplen las condiciones.

Esto sucede cuando no hay una celda en blanco para un código de proyecto específico.

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

Así como SUMIFS permite la agrupación condicional, MINIFS permite el cálculo mínimo condicional.

El primer rango, D2: D13, es el rango en el que se encuentra el límite inferior.

Luego, asocia el rango de condiciones, A2: A13, con la condición G4. Puede agregar condiciones agregando otro rango de condiciones, E2: E13, y otra condición, «». Todos estos están separados por comas.

Dado que esta funcionalidad fue creada para manejar nuestros requisitos, esta es la 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.

Edición de solo 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 recibe actualizaciones periódicas. En 2020, recibió la actualización Dynamic Arrays, 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. Para las fórmulas de matriz, ya no necesita usar Ctrl + Shift + Enter para ingresarlas. Las fórmulas en las celdas H4 y K4 son idénticas excepto por los paréntesis en ambos extremos.

El cálculo en la versión de suscripción funciona de la misma manera que la fórmula de matriz que describí anteriormente. No recomiendo esta solución de matriz dinámica, porque tenemos la funcionalidad MINIFS especializada disponible en la versión de suscripción.

READ  La actualización de Oculus Quest v37 agrega soporte inesperado de Apple

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 pueda ver cómo podemos adaptarlo para manejar requisitos más desafiantes.

Suponga que en lugar de encontrar la primera fecha según el código del proyecto, queremos encontrar la primera fecha según la primera letra del código de cuenta en la columna C. Se pueden asociar diferentes prefijos con diferentes categorías de cuenta. Figura 05 Tiene el informe. La fórmula está incluida en el K9 en Figura 03. MINIFS no puede manejar este tipo de situaciones.

Figura 05.

Las matrices dinámicas le permiten manipular rangos con otras funciones. IZQUIERDA extrae caracteres de la izquierda de la celda. Podemos usarlo para extraer la primera letra del rango de la columna de cálculo.

Podemos reemplazar la condición del código del proyecto con una condición que extraiga la primera letra 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 progresado a lo largo de los años. Pasamos de fórmulas de matriz complejas que requerían una entrada de teclado especial a una función que funciona como una matriz y luego a una función personalizada creada para manejar cálculos condicionales mínimos.

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

Recuerde ver el video que acompaña a este artículo, que explicará con más detalle cómo funcionan estas fórmulas.

El video adjunto y el archivo de Excel entrarán en más detalles para ilustrar estas técnicas.

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

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *