Listado de clientes frecuentes en excel
Experto/a, tengo una hoja de excel con datos ordenados por día de todos los clientes que fueron atendidos en los últimos 6 meses, estos clientes son a la vez profesionales matriculados y sus datos son cargados por trámite realizado de la sig manera:
fecha | apellido |mat|tomo|folio| Nºde tramite|...etc
13/06/11 | fernandez | a | 125 | 17 | 125455 |...etc
13/06/11 | gomez | a | 158 | 117 | 125456 |...etc
14/06/11 | fernandez | b | 194 | 35 | 125457 |...etc
14/06/11 | perez | b | 130 | 256 | 125458 |...etc
15/06/11 | fernandez | a | 125 | 17 | 125459 |...etc
Lo que preciso es crear un listado en una nueva hoja en donde me ordene los clientes por apellido, pero que pueda diferenciar, por ejemplo, en el caso de tener un apellido común como puede ser "fernandez" un fernández de mat.por, tomo. Por, folio.por del otro fernández de mat.Y, tomo.Y, folio. Y y que ademas me cuente la cantidad de días que se presentó desde el comienzo del listado hasta el ultimo registro, para ver quien es un cliente cliente habitual y quien uno casual para de esta forma poder brindar beneficios al de mayor concurrencia. Cabe aclarar también que si el cliente realiza 23 trámites el mismo día aparecerán 23 entradas con la misma fecha y también quisiera filtrar eso tomando solo que ese día se presentó o sea cuantos días se presento en la sucursal.
Estuve intentandolo con una macro de la siguiente forma:
Sub FILTROAV()
' FILTROAV Macro
' GENERA UN LISTADO DE USUARIOS FRECUENTES'
Sheets("LEGALIZACIONES").Range("B7:E65535").AdvancedFilter Action:= _ xlFilterCopy, CopyToRange:=Sheets("USUARIOS FRECUENTES").Range("A7"), Unique:=True
'lo siguiente lo puse por que me quita el autofiltro de la hoja de origen
Sheets("LEGALIZACIONES").Range("A6:M6").Select Selection.AutoFilter Range("A7").Select
Selection.End(xlDown).Select
End Sub
Hasta acá llegué, hace el listado pero no entiendo bien por que me repite algunas entradas siendo que tienen el mismo valor las celdas así como mismo formato. No comprendo la lógica de por que hace esto. Lo puedo arreglar a mano pero la idea es que lo haga automáticamente ya que tengo varias entradas.
Desde ya muchas gracias por su tiempo y buena voluntad, cualquier duda trataré de ser más claro.
fecha | apellido |mat|tomo|folio| Nºde tramite|...etc
13/06/11 | fernandez | a | 125 | 17 | 125455 |...etc
13/06/11 | gomez | a | 158 | 117 | 125456 |...etc
14/06/11 | fernandez | b | 194 | 35 | 125457 |...etc
14/06/11 | perez | b | 130 | 256 | 125458 |...etc
15/06/11 | fernandez | a | 125 | 17 | 125459 |...etc
Lo que preciso es crear un listado en una nueva hoja en donde me ordene los clientes por apellido, pero que pueda diferenciar, por ejemplo, en el caso de tener un apellido común como puede ser "fernandez" un fernández de mat.por, tomo. Por, folio.por del otro fernández de mat.Y, tomo.Y, folio. Y y que ademas me cuente la cantidad de días que se presentó desde el comienzo del listado hasta el ultimo registro, para ver quien es un cliente cliente habitual y quien uno casual para de esta forma poder brindar beneficios al de mayor concurrencia. Cabe aclarar también que si el cliente realiza 23 trámites el mismo día aparecerán 23 entradas con la misma fecha y también quisiera filtrar eso tomando solo que ese día se presentó o sea cuantos días se presento en la sucursal.
Estuve intentandolo con una macro de la siguiente forma:
Sub FILTROAV()
' FILTROAV Macro
' GENERA UN LISTADO DE USUARIOS FRECUENTES'
Sheets("LEGALIZACIONES").Range("B7:E65535").AdvancedFilter Action:= _ xlFilterCopy, CopyToRange:=Sheets("USUARIOS FRECUENTES").Range("A7"), Unique:=True
'lo siguiente lo puse por que me quita el autofiltro de la hoja de origen
Sheets("LEGALIZACIONES").Range("A6:M6").Select Selection.AutoFilter Range("A7").Select
Selection.End(xlDown).Select
End Sub
Hasta acá llegué, hace el listado pero no entiendo bien por que me repite algunas entradas siendo que tienen el mismo valor las celdas así como mismo formato. No comprendo la lógica de por que hace esto. Lo puedo arreglar a mano pero la idea es que lo haga automáticamente ya que tengo varias entradas.
Desde ya muchas gracias por su tiempo y buena voluntad, cualquier duda trataré de ser más claro.
1 Respuesta
Respuesta de AMRomeroDiaz
1
Te recomiendo que insertes una tabla dinámica, te explico como:
1. Seleccionas todo el rango de tus datos, si no me equivoco es B7:E65535.
2. Ve a la pestaña Insertar->Tabla dinámica -> Tabla dinámica
3. En el campo "Elija dónde sea colocar el informe de tabla dinámica" -> Nueva hoja de cálculo
4. Ahora estas en otra hoja, a tu izquierda verás la Lista de campos de la tabla dinámica, ahí tienes, "Fecha", "Apellido", "Mat", "Tomo", "Folio"... etc.. etc...
5. Arrastra APELLIDO a "Etiquetas de fila", ahora verás que todos los apellidos "se repiten una sola vez", es decir, salen una sola vez.
6. Haz clic en el pequeño triangulo al revés (de la columna de apellidos) y luego -> "Más opciones de ordenación" -> Ascendente (A a Z) por: ... y seleccionas "APELLIDO". Listo, hasta acá deberías tener los apellidos en orden alfabético.
7. Vuelve a la Lista de campos de la tabla dinámica y arrastra FECHA a "Sumatoria de valores" (es la opción que tiene un símbolo que parece una E) y asegurate de que la configuración de campo de valor sea CUENTA, de esta forma la tabla contará la cantidad de veces que se presentó cada cliente... verás que se crea una nueva columna con dicha cantidad para cada apellido.
8.(Extra). También puedes arrastrar FECHA (si, de nuevo) a "Etiquetas de fila", justo antes de APELLIDO (Fecha debe estar encima de Apellido), así contarás la cantidad de veces que se presentó cada cliente para cada día... y te puedes fijar que clientes se presentaron más de 23 veces en el mismo día. (Puedes usar el formato condicional para resaltar estos valores).
Un saludo,
Si necesitas ayuda adicional no dudes en preguntar.
Suerte
1. Seleccionas todo el rango de tus datos, si no me equivoco es B7:E65535.
2. Ve a la pestaña Insertar->Tabla dinámica -> Tabla dinámica
3. En el campo "Elija dónde sea colocar el informe de tabla dinámica" -> Nueva hoja de cálculo
4. Ahora estas en otra hoja, a tu izquierda verás la Lista de campos de la tabla dinámica, ahí tienes, "Fecha", "Apellido", "Mat", "Tomo", "Folio"... etc.. etc...
5. Arrastra APELLIDO a "Etiquetas de fila", ahora verás que todos los apellidos "se repiten una sola vez", es decir, salen una sola vez.
6. Haz clic en el pequeño triangulo al revés (de la columna de apellidos) y luego -> "Más opciones de ordenación" -> Ascendente (A a Z) por: ... y seleccionas "APELLIDO". Listo, hasta acá deberías tener los apellidos en orden alfabético.
7. Vuelve a la Lista de campos de la tabla dinámica y arrastra FECHA a "Sumatoria de valores" (es la opción que tiene un símbolo que parece una E) y asegurate de que la configuración de campo de valor sea CUENTA, de esta forma la tabla contará la cantidad de veces que se presentó cada cliente... verás que se crea una nueva columna con dicha cantidad para cada apellido.
8.(Extra). También puedes arrastrar FECHA (si, de nuevo) a "Etiquetas de fila", justo antes de APELLIDO (Fecha debe estar encima de Apellido), así contarás la cantidad de veces que se presentó cada cliente para cada día... y te puedes fijar que clientes se presentaron más de 23 veces en el mismo día. (Puedes usar el formato condicional para resaltar estos valores).
Un saludo,
Si necesitas ayuda adicional no dudes en preguntar.
Suerte
