Buscar un valor y devolver todas sus apariciones

 

CarpetaCrea una carpeta con el nombre Actividades Asistidas donde  guardarás diferentes libros:

Buscar un valor y devolver todas sus apariciones 

 

Rango con los datos siguientes:

Nº teléfono

Fecha avería

Fecha reparación

Días reparación

655555555

10/02/2013

12/02/2013

2

611111111

17/02/2013

19/02/2013

2

611111111

19/02/2013

25/02/2013

6

611111111

03/03/2013

10/03/2013

7

622222222

12/03/2013

17/03/2013

5

655555555

18/03/2013

27/03/2013

9

655555555

29/03/2013

31/03/2013

2

652211111

14/04/2013

14/04/2013

0

622222222

16/04/2013

22/04/2013

6

633333333

21/04/2013

26/04/2013

5

652211111

28/04/2013

02/05/2013

4

645454545

29/04/2013

03/05/2013

4

655555555

02/05/2013

08/05/2013

6

622222222

05/05/2013

09/05/2013

4

645454545

09/05/2013

12/05/2013

3

 En las distintas filas quedan  registrados cronológicamente los avisos de averías de los clientes, la columna Días reparación  contiene una fórmula  para calculas la diferencia entre la Fecha reparación y la Fecha avería.

Necesitas extraer los datos de averías por número de teléfono, con indicación de los intervalos de tiempo que ese mismo abonado  reclama la misma avería o inicia un parte nuevo de averías, destacando si  se produce en un plazo inferior a 5 días o en un plazo inferior a 30 días.

En un libro denominado Teléfonos, en  la primera hoja con el nombre Registros, incluirás todos los avisos, tal y como se relacionan en el rango anterior; en la segunda hoja denominada  Listado por teléfono extraerás de forma automática todos los registros del mismo número, para ello realiza los siguientes pasos:

  1. Para conseguir una relación de los números de teléfonos, utiliza filtro avanzado. Selecciona el rango que contiene los números de teléfonos, activa la ficha Datos, y en el grupo Ordenar y filtrar, selecciona  Avanzadas, indica los valores  del cuadro de diálogo Filtro avanzado que se muestran en la siguiente imagen, observa que no tienes que poner un criterio, por lo que es innecesario el encabezado.  Es fundamental que  actives  la casilla Sólo registros únicos.

 

Aparece la siguiente lista:

2. En la hoja Listado por teléfono  en una celda (en el ejemplo la celda B2) utilizado el comando Validación de datos, del grupo Herramientas de datos en la ficha Datos  crea una lista con los teléfonos filtrados en la hoja Registros


 

 

Copia el  encabezado del rango de la hoja Registros y añade el campo Días desde el último aviso

Nº Teléfono

Fecha avería

Fecha reparación

Días reparación

Días desde último aviso

3. Para que la función BUSCARV devuelva todos los registros de un mismo número de teléfono y no solo el primero que encuentre, vas a realizar las siguientes operaciones:

En la Hoja Registros añade una nueva columna, con el encabezado Averías. A través de una función vas a introducir un número de orden de avería por número de teléfono.

4. Para calcular los Días desde último aviso en la primera celda tras el encabezado introduce la siguiente función   El uso de la función SI.ERROR, simplemente evita el llenado de celdas con #N/A, ·#VALOR, ….se trata de calcular los días transcurridos entre la ultima fecha y la inmediatamente anterior, por lo que para que la información sea correcta es necesario que los datos de la columna Fecha de reparación en la hoja Registros estén ordenados de forma ascendente.

5. En la hoja Listado por teléfono sitúate en una fila anterior al encabezado y asigna los números de las columnas donde están los datos que vas a filtrar; después en la columna A  desde el primer registro,  añade números consecutivos desde el número 1, es el truco que te permitirá encontrar en la Hoja Registros los número de teléfonos repetidos y en el mismo orden.

6. Inserta la siguiente función en la primera celda del rango  y  autollena el resto, excluyendo, lógicamente, la columna Días desde último aviso.


Por estética debes ocultar el contenido de las celdas del libro las filas de  los números correspondientes a las columnas a devolver y los números consecutivos de la columna de la hoja Listado por teléfono, para ello selecciona las celdas y activa el cuadro de diálogo Formato de celdas que configurarás como la imagen siguiente:

Para destacar  si los avisos desde un mismo número se produce en un plazo inferior a 5 días o en un plazo inferior a 30 días, utiliza formato condicional, como viene configurado en las siguientes imágenes:

El resultado final sería similar al siguiente:

Licencia Creative Commons