01 / 12Tres Planos Llegaron a Obra Desfasados

Ordenar y FiltrarFunciones de TextoMatrices Dinámicas

Ordenar códigos alfanuméricos en Excel: el 10 tras el 9

El Acero de los Ejes A–D se Cortó con la Revisión 9 de un Plano que Iba por la 11, Porque el Registro Ordenó R10 y R11 Antes que R2

29 oct 202610 min de lectura

UsaORDENARTEXTOANTES, TEXTODESPUESVALORMIN, MAX

La hoja de emisiones de Harbrook Construction le decía a la obra con qué revisión de cada plano trabajar. En septiembre mandó tres planos desfasados. El registro estaba completo y era correcto; la ordenación que lo leía puso R10, R11 y R12 delante de R2.

La hoja tomaba la última fila de cada plano en un registro ordenado de la A a la Z. En cualquier plano con diez revisiones o más, la última fila era R9.

Emisión desfasada, septiembreCifra
Planos en el registro8
Revisiones emitidas67
Planos enviados con la revisión equivocada3
Revisiones que la obra nunca vio6
Armadura de vigas re-atada (S-101)8.450 £
Estructura rehecha, ejes A–D (S-103)27.600 £

Alcance. ORDENARPOR y FILTRAR requieren Excel 2021 o posterior, y TEXTODESPUES requiere Excel 2024 o Microsoft 365.

Cada sección da al lado una versión para Excel anterior, hecha con EXTRAE, VALOR y MAX.SI.CONJUNTO, que requiere Excel 2019.

En todo el artículo, la hoja Registro tiene una fila por revisión emitida en A2:B68: el plano en la columna A y el código de revisión, como R12, en la columna B.

01Tres Planos Llegaron a Obra Desfasados

El responsable de documentación anota cada emisión como una fila: plano, revisión y fecha. La hoja de emisiones busca en ese registro la revisión vigente de cada plano y la imprime en la remisión que va a obra.

El 11 de septiembre el taller empezó a cortar el acero de los ejes A–D con la revisión 9 del S-103. Las revisiones 10 y 11 ya se habían emitido, y entre las dos cambiaban las placas de anclaje y dos perfiles de pilar.

Ocho Planos, Sesenta y Siete Revisiones y Tres Enviados a Obra en R9

El resumen de emisiones de Harbrook Construction, en A1:E9: una fila por plano. La columna B cuenta las revisiones del registro, la C es lo que la hoja envió a obra tomando la última fila de un registro ordenado de la A a la Z, la D es la revisión más alta por número y la E cuántas revisiones iba atrasada la obra.

ABCDE
1
Drawing
Revisions issued
Sent to site (A→Z)
Current revision
Revisions behind
2
S-101 Ground beams
12
R9
R12
3
3
S-102 Pile caps
6
R6
R6
0
4
S-103 Steel frame A–D
11
R9
R11
2
5
S-104 Steel frame E–H
8
R8
R8
0
6
S-105 Roof steel
10
R9
R10
1
7
S-106 Stair cores
4
R4
R4
0
8
S-107 Connections
7
R7
R7
0
9
S-108 Retaining wall
9
R9
R9
0

fxLas celdas con fórmulas están resaltadas en verde

Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas

Otros dos planos también estaban desfasados. El S-101 se detectó en la inspección previa al hormigonado, con la armadura atada según la R9. El S-105 se detectó antes de que nadie fabricara con él.

02El Texto Se Ordena Carácter a Carácter

R12 es texto, no un número, así que Excel lo ordena como un diccionario. Compara el primer carácter, luego el segundo, y se detiene en la primera diferencia.

R10 y R2 comparten la R. Después el 1 se encuentra con el 2, y el 1 va antes, así que R10 se ordena delante de R2 siga lo que siga. Escribe ="R10"<"R2" en cualquier celda y devuelve VERDADERO.

Orden de la A a la ZQué comparó Excel
R1R1 es el más corto
R101 antes que 2
R111 antes que 2
R121 antes que 2
R2primer 2
R9el último, porque 9 es la primera cifra más alta

En un plano con nueve revisiones o menos el orden es correcto. La décima revisión es donde se rompe, y por eso el registro pareció estar bien durante casi toda la obra.

Escenario: En una columna vacía escribe de R1 a R12, uno por fila, y ordénala de la A a la Z con Datos ▸ Ordenar. Comprueba qué código queda en la última fila y escribe ="R10"<"R2" a su lado.

Pruébalo en la cuadrícula

03La Última Fila Era la Fila Equivocada

La fórmula de la hoja de emisiones era correcta en sus propios términos. Con el registro ordenado por plano y después por revisión, traía la última coincidencia de cada plano.

=BUSCARX(A2;Registro!$A$2:$A$68;Registro!$B$2:$B$68;;0;-1)

El -1 hace que BUSCARX busque de abajo arriba. Devuelve la última fila, no la revisión más alta, y confía en que la ordenación las haya puesto en el mismo sitio.

PlanoRevisiones emitidasEnviada a obraVigente
S-101 Vigas riostras12R9R12
S-103 Estructura A–D11R9R11
S-105 Cubierta10R9R10
S-108 Muro de contención9R9R9

El S-108 es el que hay que mirar. Muestra R9, y R9 es correcto. La misma respuesta en cuatro filas acertó una vez y falló tres.

Escenario: Ordena de la A a la Z un registro con las doce revisiones del S-101 y escribe =BUSCARX("S-101";A2:A13;B2:B13;;0;-1). Confirma que devuelve R9; después ordena de la Z a la A y mira cómo devuelve R1.

Pruébalo en la cuadrícula

04MAX No Sabe Leer R12

El arreglo evidente es pedir la revisión más alta en lugar de la última. =MAX.SI.CONJUNTO(Registro!$B$2:$B$68;Registro!$A$2:$A$68;A2) parece correcta y devuelve 0 en todos los planos.

MAX y MAX.SI.CONJUNTO ignoran el texto por completo. No encuentran números en la columna B, así que devuelven 0 en lugar de un error.

Un cero en una remisión se lee como "todavía sin revisión", que es otra forma de equivocarse. =MAX(Registro!B2:B68) hace lo mismo. La solución es darle a Excel el número que se le pide comparar, y eso hace la sección siguiente.

05Saca el Número a Su Propia Columna

Todo lo que va después de la R es el número de revisión. Ponlo en una columna auxiliar como número de verdad, y cada ordenación, búsqueda y máximo vuelve a funcionar.

=--TEXTODESPUES(B2;"R")

TEXTODESPUES devuelve el texto que sigue a la primera R, es decir "12", y los dos signos menos lo convierten en el número 12.

En Excel anterior, =VALOR(EXTRAE(B2;2;9)) hace lo mismo: toma hasta nueve caracteres a partir del segundo. Rellénala en la columna C del registro. Ahora C2:C68 contiene del 1 al 12, y una columna de números se ordena en orden numérico.

Escenario: Añade =--TEXTODESPUES(B2;"R") en C2 del registro y rellénala hasta C68. Después ordena por la columna A y luego por la C, y comprueba que la última fila del S-101 ya es R12.

Pruébalo en la cuadrícula

06Ordena por el Número y Muestra el Código

Con una columna auxiliar, Datos ▸ Ordenar admite dos niveles: Plano de la A a la Z y después la columna numérica de menor a mayor. En Microsoft 365 puedes saltarte la auxiliar y ordenar con una fórmula.

=ORDENARPOR(Registro!A2:B68;Registro!A2:A68;1;--TEXTODESPUES(Registro!B2:B68;"R");1)

ORDENARPOR ordena las dos columnas según claves que tú calculas. La segunda clave es el número, nunca el código, así que R10 queda después de R9. Lo que muestra la lista ordenada sigue siendo el código.

Una ordenación hecha con una fórmula no puede quedarse anticuada. Quien añada la R13 el mes que viene no tiene que acordarse de volver a ordenar el registro.

Escenario: Escribe la fórmula de ORDENARPOR en una hoja vacía y busca el S-103 en su resultado. Comprueba que sus filas van de R1 a R11; después añade una R12 al registro y mira cómo aparece al final.

Pruébalo en la cuadrícula

07Encuentra la Revisión Vigente Sin Ordenar

La hoja de emisiones nunca necesitó ordenar nada. Necesita la revisión más alta de cada plano, y eso es un máximo. A un máximo no le importa en qué orden estén las filas.

Con la columna auxiliar de la sección 5:

="R"&MAX.SI.CONJUNTO(Registro!$C$2:$C$68;Registro!$A$2:$A$68;A2)

Sin ella, en Microsoft 365:

="R"&MAX(FILTRAR(--TEXTODESPUES(Registro!$B$2:$B$68;"R");Registro!$A$2:$A$68=A2))

Las dos devuelven R12, R11 y R10 para los tres planos desfasados. Desordena el registro, ordénalo al revés o añade filas al final, y las respuestas no se mueven.

Escenario: Sustituye el BUSCARX de la hoja de emisiones por la versión con MAX.SI.CONJUNTO y rellénala hacia abajo. Comprueba que la columna C coincide ahora con la D en los ocho planos.

Pruébalo en la cuadrícula

08Rellena los Códigos con Ceros

La otra solución está en el origen. Los códigos con un número fijo de cifras se ordenan bien como texto, porque cada carácter queda alineado: R02 antes que R10, como debe ser.

="R"&TEXTO(C2;"00")

Eso convierte el 2 en R02 y el 12 en R12. Los códigos de revisión de dos cifras, como P01 o C02, son habituales en los proyectos británicos justo por esto.

El relleno solo funciona hasta el ancho elegido: R100 volvería a ir antes que R99. El relleno ayuda también a las personas y a los filtros. Quien ordene el registro a mano obtiene la respuesta correcta, sin columna auxiliar que recordar.

La oferta de Excel no sirve aquí. Al ordenar una columna de números guardados como texto, como "10" y "2", la Advertencia de ordenación ofrece ordenar como número todo lo que parezca un número.

R10 no parece un número, así que esa oferta no aparece nunca.

09Compara el Recuento con la Revisión

Las revisiones se emiten en secuencia, así que el número de revisiones de un plano y su número de revisión más alto deben coincidir. Si no coinciden, la emisión está desfasada, y se ve antes de que nada salga hacia la obra.

=CONTAR.SI.CONJUNTO(Registro!$A$2:$A$68;A2)=--TEXTODESPUES(C2;"R")

Con C2 conteniendo la revisión enviada a obra, devuelve FALSO para S-101, S-103 y S-105. El S-101 tiene 12 emisiones y se envió la R9. La comprobación no necesita ordenar ni columna auxiliar.

Si un proyecto se salta o retira revisiones, compara con el máximo de la columna auxiliar. En cualquier caso, pon la comprobación junto a la remisión, no en una pestaña que nadie abre.

Escenario: Añade la comprobación con CONTAR.SI.CONJUNTO en la columna F de la hoja de emisiones y pon =CONTAR.SI(F2:F9;FALSO) encima. La hoja de Harbrook devuelve 3, uno por cada plano que salió desfasado.

Pruébalo en la cuadrícula

10Seis Trampas

  1. Ordenar códigos como texto. R10 va antes que R2, y el error solo aparece a partir de la décima revisión.
  2. La última fila como la vigente. BUSCARX con -1 devuelve lo que la ordenación dejó abajo.
  3. MAX sobre códigos. MAX y MAX.SI.CONJUNTO ignoran el texto y devuelven 0, no un error.
  4. Una respuesta correcta en una lista corta. El S-108 en R9 es correcto, así que una revisión puntual sale bien.
  5. Un relleno demasiado estrecho. De R01 a R99 se ordena bien; R100 lo vuelve a romper.
  6. Esperar a la Advertencia de ordenación. Solo ofrece ordenar como número las celdas que son todo cifras.

11Cuatro Ejercicios

  1. Reprodúcelo. Escribe de R1 a R12, ordena de la A a la Z y mira dónde cae R10.
  2. Arregla la ordenación. Añade =--TEXTODESPUES(B2;"R") junto a los códigos y ordena por esa columna.
  3. Sáltate la ordenación. Usa MAX.SI.CONJUNTO sobre la columna auxiliar para devolver la revisión vigente de cada plano.
  4. Demuéstralo. Añade la comprobación con CONTAR.SI.CONJUNTO y confirma que marca S-101, S-103 y S-105, y nada más.

Qué Llevarse

El registro de Harbrook tenía todas las revisiones. La hoja lo leía en orden de diccionario, y ese orden pone el 10 antes que el 2. Nada parecía ir mal hasta que un plano llegó a su décima revisión.

Un código con un número dentro es texto. Ordena y compara por el número, no por el código, o rellena el código para que los dos órdenes coincidan.

Después comprueba el recuento, porque eso es lo que habría salvado los ejes A–D.

Comparte este artículo:
Volver al Blog
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy