Serie Ejercicios de Excel Avanzado
|
Ejercicio #1 Nivel de dificultad: 8/10
Identificar los productos que los visitantes ven en una tienda online |
(Si eres capaz de realizar este ejercicio es porque eres bueno en Excel)
A continuación te presento un problema de Excel de mi entera autoría. Lo considero de un nivel alto de dificultad para el usuario promedio de Excel. Si eres capaz de realizar este ejercicio es porque eres bueno en Excel.
Es un problema del mundo real. Hay varias formas de resolverlo, usar macros es una de ellas, pero, en esta oportunidad lo planteo para que sea resuelto SÓLO mediante fórmulas de Excel.
 Índice de contenido |
- Planteamiento del problema
- Solución del problema
PLANTEAMIENTO DEL PROBLEMA
Como analista en Excel, a ud. se le encarga formular una hoja de cálculo que sea capaz de identificar los ejercicios resueltos que se venden en una tienda online sobre ciertos temas de materias de ingeniería (Mecánica vectorial, Termodinámica, etc).
En el sistema del sitio web estos ejercicios se identifican con dos nombres diferentes. Uno es el nombre que los visitantes aprecian al navegar y visitar la tienda y otro es el nombre interno que se aprecia al consultar un reporte de visitas. El problema es que en dicho reporte no hay forma de relacionar ambos nombres.
Por ejemplo, el reporte de visitas muestra que un visitante llegó hasta el ejercicio identificado como “ejercicio 5-3“. Queremos saber cuál es el nombre de ese ejercicio dentro de la tienda, tal como lo vio el visitante.
Se requiere que ud. cree las fórmulas necesarias para que al consultar el nombre interno de un ejercicio que se halle en el reporte de visitas se devuelva como resultado lo siguiente:
-
Nombre del ejercicio tal como lo ve el visitante en la tienda.
-
Identificar la materia a la que pertenece el ejercicio (Mecánica Vectorial, Termodinámica, etc.)
-
Identificar el tema de la materia (Fuerzas en el plano, Manejo de tablas, etc.)
La formulación requerida debe poder identificar al ejercicio como se muestra en la Figura 1:
- La celda en color amarillo representa el valor de entrada.
- Las celdas en color verde son el resultado de las fórmulas que ud. debe crear.
A ud. se le entrega un libro de Excel (DESCARGAR AQUÍ) con los datos del sistema que permitirán realizar la identificación requerida.
Este libro contiene tres hojas cuya información se describe a continuación:
1. Hoja “BD Posts”
Figura 2.
Esta hoja contiene información de cada ejercicio que se ofrece en la tienda. La información que a ud. le interesa de esta hoja es la siguiente:
ID: es la cédula o número que identifica a cada ejercicio dentro de la tienda.
post_title: es el nombre del ejercicio tal como lo ve el visitante en el sitio web (tienda).
post_name: es el nombre interno del ejercicio dentro de la administración del sitio web. Este es el nombre que figura dentro de los reportes de visitantes.
2. Hoja “Materias y temas”
Figura 3.
Esta hoja contiene el código que asigna el sitio web a cada nombre de materia y a cada nombre de tema de los ejercicios en la tienda. Además, en esta hoja se proporciona una lista con los nombres de las diferentes materias.
3. Hoja “BD Taxonomía”
Figura 4.
Esta hoja contiene los códigos de cada materia y de cada tema asociados con cada ejercicio de la tienda.
El ID de cada ejercicio se encuentran en la columna A (object_id).
Los códigos de materias y los códigos de temas y sub temas se encuentran en la columna B (term_taxonomy_id) de esta hoja.
Tenga en cuenta que la propia tienda como tal también posee un código en la columna B. El valor de este código es 3.
Cada ejercicio de la tienda online estaría asociado al menos a 3 registros en la hoja “BD TAXONOMÍA”, así:
-
Un registro para el código de la tienda. -
Un registro para el código de la materia. -
Un registro para el código del tema.
El ejemplo anterior lo pudieras ver así en la hoja “BD Taxonomía“:
Para simplificar este problema de Excel, suponga que la cantidad máxima de registros asociados a un ID de ejercicio (columna A) en la hoja “BD taxonomía” es 3, es decir, estamos obviando el caso cuando un ejercicio pertenece a un sub tema. Aquí, si observas, ya puedes ver un inconveniente:
¿Cómo saber si un código de la columna B es un código de materia o un código de un tema de una materia?
SOLUCIÓN A ESTE PROBLEMA DE EXCEL
Funciones a utilizar:
-
DESREF -
INDICE -
COINCIDIR -
BUSCARV
Paso 1. Obtener el nombre del ejercicio en la tienda.
Para no tener que re ordenar ciertas columnas de la tabla “BD posts” y poder así emplear la función BUSCARV, emplearemos en su lugar las funciones INDICE y COINCIDIR.
Utilizaremos la función COINCIDIR para encontrar la fila de la hoja “BD posts” donde se encuentra el nombre interno del ejercicio en la columna “L”.
¿Difícil de entender?
Lo podemos decir de esta otra forma:
Necesitamos encontrar la fila de la columna “L” donde se encuentra el nombre interno del ejercicio en la hoja BD posts. Recuerda que el nombre interno del ejercicio es proporcionado por el software analítico.
Luego utilizaremos la función INDICE para extraer de la columna F de la hoja “BD Posts” el nombre del ejercicio en la tienda (post_title).
Esta es la fórmula de Excel que necesitamos:
Figura 6.
El resultado es una fórmula que hace uso de las funciones INDICE y COINCIDIR. La función COINCIDIR devuelve un valor que es utilizado por uno de los argumentos de la función INDICE.
Paso 2. Identificar la materia y el tema.
Este es el paso más largo y de mayor análisis. Debemos contar con una tabla resumen en la que se muestren los códigos de materias y temas, los nombres de materia y temas.
Recuerda que obtendremos 3 registros por cada ejercicio: uno para el código de la tienda, uno para el código de la materia y otro para el código del tema.
A continuación, vamos a sub dividir este paso para hacer las cosas más fáciles.
Paso 2.1 Obtener el ID del ejercicio en la hoja “BD posts”
Se utilizan las funciones INDICE y COINCIDIR.
¿Por qué necesitamos el ID del ejercicio?
Si te das cuenta, el ID del ejercicio es común entre la hoja “BD posts” y la hoja “BD Taxonomia“. El ID es el valor que nos permite enlazar esas dos hojas.
Nuevamente, la función COINCIDIR nos permite encontrar la fila de la hoja “BD posts” donde se encuentra el nombre interno del ejercicio en la columna L (post_name).
Luego, con el resultado anterior, utilizaremos la función INDICE para conseguir el ID del ejercicio en la columna A de la hoja “BD Posts“.
El resultado es la fórmula siguiente:
=INDICE(‘BD Posts’!A:A;COINCIDIR(D3;’BD Posts’!L:L;0);1)
Paso 2.2 Elaborar una tabla resumen
Vamos a elaborar una tabla como la que te muestro a continuación:
Los valores de Código, Nombre y Materia? deben ser calculados a través de fórmulas de Excel que te mostraré acá más adelante.
Paso 2.2.1 Fórmulas de Excel para los valores de “Código”
El valor de cada código se obtiene consultando 3 veces el valor de ID de ejercicio en la columna A (object_id) de la hoja “BD Taxonomia” y tomando luego el valor correspondiente de la columna B (term_taxonomy_id) de la misma hoja. Recuerda que el ID de ejercicio lo obtuvimos en el paso anterior (2.1).
Ten presente que cada vez que realices una búsqueda de un mismo ID de ejercicio en la columna A de la hoja “BD Taxonomia” obtendrás siempre el mismo resultado porque las funciones de búsqueda por sí solas siempre devuelven el primer valor que encuentran.
¿Entonces cómo puedo hacer las tres búsquedas para obtener los diferentes resultados?
Podremos hacer las 3 búsquedas apoyando a las funciones INDICE y COINCIDIR con la función DESREF, gracias a que los 3 valores de ID de ejercicio están uno detrás de otro en la columna A en la hoja “BD Taxonomia“, como lo mostramos en la Figura 5.
La fórmula para obtener el código del primer registro es:
=DESREF(‘BD Taxonomia‘!B2;(COINCIDIR((INDICE(‘BD Posts‘!A:A;COINCIDIR(D3;‘BD Posts‘!L:L;0);1));‘BD Taxonomia‘!A:A;0))-2;0)
La fórmula para obtener el código del segundo registro es:
=DESREF(‘BD Taxonomia‘!B2;(COINCIDIR((INDICE(‘BD Posts‘!A:A;COINCIDIR(D3;‘BD Posts‘!L:L;0);1));‘BD Taxonomia‘!A:A;0))-2+1;0)
La fórmula para obtener el código del tercer registro es:
=DESREF(‘BD Taxonomia‘!B2;(COINCIDIR((INDICE(‘BD Posts‘!A:A;COINCIDIR(D3;‘BD Posts‘!L:L;0);1));‘BD Taxonomia‘!A:A;0))-2+2;0)
En las tres fórmulas la celda de referencia para la función DESREF es la celda B2 de la hoja “BD Taxonomia“.
El -2 constante en las tres fórmulas es para compensar el hecho de que la celda de referencia para la función DESREF inicia en la fila 2.
El +1 en la fórmula para el segundo código es para que la referencia de celda devuelta por la función DESREF corresponda a la celda siguiente a la celda donde se halla el código del primer registro.
El +2 en la fórmula para el tercer código es para que la referencia de celda devuelta por la función DESREF corresponda a la celda que está dos celdas después de la celda donde se halla el código del primer registro.
Paso 2.2.2 Fórmulas de Excel para obtener el nombre correspondiente a cada código
Ahora que ya tenemos los valores de los códigos de nombre de materia/nombre de tema, podemos obtener el nombre de materia o nombre de tema como tal.
Esta es la fórmula con la que podemos determinar el nombre de materia o el nombre de tema correspondiente a cada código:
=SI.ERROR(BUSCARV(C20;‘Materias y temas‘!A$2:B$24;2;FALSO);“N/A“)
Con la fórmula anterior podemos consultar el valor de cada código en la hoja de materias y temas y traernos el nombre correspondiente. La función SI.ERROR nos sirve para manipular el error que puede surgir si el código no existe en la hoja de materias y temas.
Paso 2.2.3 Fórmula para saber si el nombre es de una materia o es de un tema
Como ya lo dejamos ver antes, no sabemos si un determinado código es de una materia o de un tema. Nos toca averiguarlo. Para poder saberlo debemos empezar consultando cada nombre obtenido en el paso anterior. Consultaremos si cada uno de estos nombres se halla en la lista de materias.
Ten en cuenta que si un nombre no se halla en la lista de materias es porque o se trata de un nombre de tema o es porque el código pertenece a la tienda como tal.
La fórmula en el campo Materia? es una operación de búsqueda que realizaremos en la hoja “Materias y temas“, consultando cada nombre obtenido en el paso anterior. Esta es la fórmula:
=SI.ERROR(SI(COINCIDIR(D20;‘Materias y temas‘!I$2:I$6;0)>0;“SÍ“);SI(C20<>3;“NO“;“N/A“))
En esta fórmula hacemos la consulta mediante la función COINCIDIR. En caso de que el resultado de COINCIDIR sea mayor que cero significa que el nombre se halla en la lista de materias, de lo contrario, se produciría un error el cual manejamos con la función SI.ERROR haciendo a su vez uso de la función SI para saber si se trata del código de la tienda (cuyo valor es 3). Si el código es diferente de 3 se trata definitivamente del nombre de un tema.
Ya con esto completamos la tabla mostrada en la figura 7.
Paso 2.3 Obtener el nombre de la materia y el nombre del tema
Ahora podemos completar la tabla mostrada en la figura 1 utilizando los datos de la figura 7, puesto que ya tenemos identificado completamente cuál nombre es de materia y cuál nombre es de un tema. Para ello vamos a crear dos fórmulas de Excel.
Vamos a crear entonces una fórmula de Excel que tome el nombre de la materia que aparece en la figura 7 y lo ponga como resultado en el lugar correspondiente de la figura 1:
=INDICE(D20:E22;COINCIDIR(“SÍ“;E20:E22;0);1)
La fórmula anterior utiliza la función COINCIDIR para encontrar la posición del texto “SÍ” en la columna con título Materia? en la tabla de la figura 7. Luego se utiliza la función INDICE para extraer el nombre correspondiente en la columna nombre en la misma fila donde se encontró la palabra “SÍ”.
Ahora vamos a crear una fórmula de Excel que tome el nombre del tema que aparece en la figura 7 y lo ponga como resultado en el lugar correspondiente de la figura 1:
=INDICE(D20:E22;COINCIDIR(“NO“;E20:E22;0);1)
La fórmula anterior utiliza la función COINCIDIR para encontrar la posición del texto “NO” en la columna con título Materia? en la tabla de la figura 7. Luego se utiliza la función INDICE para extraer el nombre correspondiente en la columna nombre en la misma fila donde se encontró la palabra “NO”.
Con esto finalmente hemos completado la solución del problema.
