UniversoExcelForo

Respuesta a consultas.
Fecha de creación: 09.Abril.2012

Fundadores: Wibly,Tyno,Servando,Sakkar,Rosendo2,RMaximo,Railar,Mcoronel,Ioyama,Igtelo,Hugotron,Drochar,Dedos,Alberto,Adolfo

No se pueden hacer consultas de Excel o VBA por mensaje privado.
Utiliza el botón buscar del foro y te ahorras tiempo para obtener "ayuda".

No estás conectado. Conéctate o registrate

Ver el tema anterior Ver el tema siguiente Ir abajo  Mensaje [Página 1 de 1.]

rubito1


buen dia gente:explico lo que preciso y adjunto link al archivo en el que me ayudo tyno
al apretar pasar producidas,pasa lo que esta en la columna "Q" marcado como "L" o "T" y me lo copia y pega en archivo externo(el cual lo modifique para que se abra directamente solo);al hacer esto me anota en la columna "AN" "pasado" con lo cual no lo vuelve a pasar en otra oportunidad;lo que yo preciso es que si cambio la letra de la columna q en vez de "L" le pongo la letra "T" por mas que diga "pasado" pueda cambiarle la letra en el archivo externo "resumen produccion anual";yo lo intente pero no me lo cambia sino que me lo anota de nuevo(con la letra ya cambiada)subo aca el link  (cambiar en el codigo el link a la direccion del archivo

https://www.dropbox.com/s/gr8dzuv4qmtcrah/archivos%20en%20uso%202014.zip

[url=https://www.dropbox.com/s/gr8dzuv4qmtcrah/archivos en uso 2014.zip]enlace[/url]

tyno


Hola Rubito.
En base a tu pedido estaba pensando que quizás lo que te puede servir es establecer una conexión de datos externos por medio de Microsoft Query (MQ) en el archivo Resumen producción Anual. Esto te va a permitir ver los datos del archivo Producción en  archivo Resumen de forma actualizada, es decir cada cambio que realices en Producción se ve en Resumen y para esto no es necesario tener los libros abiertos, luego la información que requieras ver por mes se administra por medio de una tabla dinámica. Y si vamos al caso, también pienso que si lo que te interesa es tener un Resumen (por eso has creado un archivo Resumen) lo que se puede hacer es crear directamente una tabla dinámica en el mismo archivo producción, con esto se evitan hacer macros complejas y archivos extras para consultar la información.

Para que veas de lo que te hablo te paso el archivo de Producción con una tabla dinámica en la cual vas a gestionar la información que hoy estas queriendo ver en el libro Resumen. A esta tabla dinámica le he agregado la opción de segmentación de datos la cual esta disponible a partir de Excel 2010, por lo que si tenes una versión anterior esto no va a funcionar. Esta opción de segmentación de datos sirve para para seleccionar de manera rápida los criterios de información requerido, en tu caso creo que es Año/Mes y Estado de los pedidos.
Si no tenes Excel 2010 esta información se puede filtrar de otra forma diferente a la segmentación de datos pero que cumple con el mismo propósito, sea como sea, la idea es que veas como se gestiona y actualiza la información de manera rápida y sencilla a través de un informe de tabla dinámica. Esto también se puede aplicar a archivo compartidos por medio de MQ como te expliqué al principio.
Para hacer una prueba modifica manualmente algún dato en la Hoja PLANILLA y luego andá a la Hoja RESUMEN donde esta la tabla dinámica y vas a ver la actualización correspondiente.
Esta tabla dinámica se actualiza por medio de una macro que hice y las columnas que ves yo las he seleccionado pero se pueden visualizar o quitar las columnas que se deseen.

https://www.dropbox.com/s/0aqm7ffjupggvpt/produccion%202014%20CASI%20casi.zip

Saludos

3 excelente el 13/04/14, 12:21 pm

rubito1


tyno,gracias de nuevo;lo que hicistes esta barbaro!me encanto esto de las tablas dinamicas las cuales aun no las manejo(justo el otro dia empece a investigar algo de ello)
1)si bien lo que hicistes esta espectacular (y ya estuve modificando algunas cosas en la tabla dinamica(me jugue!!)y realmente me encanta el potencial que tiene!!,pero hasta que lo practique y entienda como usar todo esto,podra ser que hagas lo que te pedi de i cambio la letra de la columna q en vez de "L" le pongo la letra "T"  asi puedo ir usando mientras tanto eso? ya que esto nuevo que me pasas y que esta barbaro no lo quiero instrumentar hasta tanto no lo sepa usar bien yo;te parece bien?(y tu idea seria que este "informe" luiego este en un archivo externo "resumenn....)es asi no?
2)fijate que hay una faya ya que al agregar nuevos items en planilla o carga diaria "desaparecen los 2 filtros que estan arriba de todo al lado del de estado
3)x otro lado,habras visto que tengo un monton de macros en este libro y quisiera "limpiar"las que no uso(que son las que o tienen los botones que se ven en las hojas;existe algo para eso?si te parece que si decime como solicitarlo en un nuevo tema (ya que se que eso no correponde pedirlo aca)(yo habia pedido eso pero no tuve resultado antes)
gracias y saludos

tyno


Hola Rubito.
Justamente la propuesta de utilizar una tabla dinámica o Microsoft Query con lo cual se actualicen las modificaciones de la Hoja PLANILLA  sería la única solución que veo a tu planteo ya que tus registros no tienen un ID o sea un valor que  identifique cada fila de registro respecto de otra. Por ejemplo si haces una modificación en alguna fila de datos del pedido 2500 el cual a su vez tiene 10 filas de registros no hay forma de establecer cual fila modificar en el archivo externo.

Con respecto a la falla, en realidad no es una falla sino que los nuevos datos ingresados a la Hoja PLANILLA se ingresan sin formato fecha en la columna C "Fecha Pedido" con lo cual en la tabla dinámica estos valores no son reconocidos como fechas por lo que desaparecen esos grupos de segmentación. Esto se soluciona dándole formato fecha a toda la columna C "Fecha Pedido" de la Hoja PLANILLA antes de ingresarles los nuevos datos.

Volviendo al principio entonces creo que no  te queda otra que considerar el tema de la tabla dinámica y el uso de Microsoft Query si es que vas a usar dicha tabla en un archivo externo.

Saludos

5 ok el 13/04/14, 07:04 pm

rubito1


ok,comprendo(y ya corregi lo del formato fecha y queda ok!!)
1)como hago si quiero "agregar"arriba algun otro elemento(columna)de filtrado?
2)ahora la tabla toma hasta la columna AU de planilla;si quiero que tome alguna columna posterior?(ya que no veo la opcion de donde toma los datos
3)la idea seria hacer una tabla dinamica desde "resumen..." y que tome los datos de un "archivo externo",es asi?
4)no se pueden usar los filtros en la tabla dinamoca para ordenar(quiero ordenar alfabetico ,aparece como que "esta" el filtro aplicado pero no lo ordena
5)vistes el punto 3 que puse antes?(lo de las macros?)
gracias

tyno


1)como hago si quiero "agregar"arriba algún otro elemento(columna)de filtrado?

Para la segmentación de datos, seleccionas alguna celda de la tabla dinámica de manera que aparezca en la barra de herramientas la herramienta de tabla dinámica y en  la pestaña Opciones seleccionas "Insertar Segmentación de datos"  luego "Insertar Segmentación de datos"  con esto aparece un cuadro de lista para seleccionar el campo por el cual queremos hacer los filtros, en este caso ya estaban seleccionado ESTADO, Mes y Año.

2)ahora la tabla toma hasta la columna AU de planilla;si quiero que tome alguna columna posterior?(ya que no veo la opcion de donde toma los datos

Si agregas más columnas la tabla dinámica debería tomarlas de forma automática ya que tiene establecido un rango dinámico, o se que cualquier fila o columna que se agregue luego aparece en la tabla dinámica.
Por otro lado si en la tabla dinámica querés ver o quitar columnas haces click derecho sobre cualquier celda de la tabla y se selecciona "Mostrar lista de  campos", con esto aparece un cuadro de lista a la derecha de la pantalla donde seleccionamos las columnas que deseamos ver o quitar y a su vez, en la parte inferior de este cuadro de lista podemos elegir donde disponer las columnas o campos. Luego este cuadro se puede ocultar para tener una mejor visión de la tabla. 

3)la idea seria hacer una tabla dinámica desde "resumen..." y que tome los datos de un "archivo externo",es asi?
No se como es el tema, por lo que entiendo tenes un archivo RESUMEN en una carpeta de dropbox. Si es así, entonces esa tabla dinámica que te mostre de ejemplo se puede hacer en el archivo RESUMEN de dropbox directamente, para esto hay que usar Microsoft Query lo cual es bastante sencillo.

4)no se pueden usar los filtros en la tabla dinámica para ordenar(quiero ordenar alfabetico ,aparece como que "esta" el filtro aplicado pero no lo ordena
Ordenar es una cosa diferente a  filtrar, aunque si te fijas en cada columna hay un botoncito para filtrar, si desplegás ese botoncito esta también la opción de ordenar y en " Mas opciones de ordenación " seleccionas Orden por ascendente o descendente, por defecto esta en ordenar manualmente.


5)vistes el punto 3 que puse antes?(lo de las macros?)
Me olvide de responderte eso, lo único que te queda es seleccionar el botón el cual querés quitarle la macros y haciendo click derecho en el menú seleccionas Asignar macro y en el cuadro que aparece vas a ver que esta seleccionada la macro de dicho botón, luego le das al botón   derecho Modificar y esto te lleva a la macro entonces la borras manualmente y luego eliminas el botón. Esto hasta donde se el único método para borrar las macros en el editor, si bien es algo que te puede llevar un tiempo también es algo que haces un vez. 

Entonces si esto te interesa vemos como crear la tabla dinámica mediante Microsoft Query en el archivo dropbox si esto es así.

Saludos

7 respuestas el 14/04/14, 10:19 am

rubito1


1)justo anoche estuve investigando y vi como insertar seg.de datos asi que lo practicare a continuacion
2)si inserto una columna la toma pero si inserto algunas mas,es como que elimina alguna de las que estaban antes???y si la "inserto " despues de la col ap o aq ni siquiera la toma(x mas que le pongo actualizar)probalo y veras(pareceria como que acepta hasta un limite y solo hasta cierta columna)(igual esto de insertar es solo xque estoy practicando para aprender,no es que lo necesito ahora)
3)la idea del archivo"resumen"donde figuran todos los meses,es poder ir almacenando alli los datos de lo que se ha ido procesando(ya sea x cambio de estado o x producido o x entregado(x eso las 3 planillas de resumen);si esto se maneja con la tabla dinamica iria cambiando automaticamente,lo cual no serviria(o me equivoco?)
4)justo a eso me refiero;lo que intente es "ordenar" alfabeticamente,pero no lo hace,x eso mi consulta
5)gracias x esa respuesta,pero lo que yo quiero es que las macros"que no tienen boton asignado"esa son las que quiero sacar (ya que no las estoy usando(igual esto no es tan importante asi que no te quiero molestar mucho con esto)
6)x ultimo,me encanta esto de la tabla dinamica y el query,reitero que no lo conozco ,pero creo que si me gustaria hacerlo de esta manera y poder aprender a usarla

8 parcial el 14/04/14, 10:59 am

rubito1


ya encontre como "modificar" de donde toma los datos y le "amplie" las columnas(ahorea si toma(lo que decia en el punto 2)

tyno


3)la idea del archivo"resumen"donde figuran todos los meses,es poder ir almacenando alli los datos de lo que se ha ido procesando(ya sea x cambio de estado o x producido o x entregado(x eso las 3 planillas de resumen);si esto se maneja con la tabla dinamica iria cambiando automaticamente,lo cual no serviria(o me equivoco?)
 
Efectivamente una tabla dinámica actualiza cualquier cambio que se realice en su origen de datos, que seria la Hoja PLANILLA.
No se si no me doy cuenta de algún aspecto de lo que querés lograr, pero en base a lo que entiendo mi idea es que en la Hoja PLANILLA dejes registrados todos los movimientos, cambios, modificaciones, etc  que hagas, es decir, que esa Hoja PLANILLA quede como una gran base de datos o historial de los procesos, y luego por medio de la tabla dinámica realices la consulta que quieras.
Me parece que no tiene sentido y es algo redundante estar pasando los datos de la Hoja PLANILLA a otro archivo para crear una nueva base de datos para que después al final hacer una consulta , si dicha consulta se puede hacer directamente con la Hoja PLANILLA.
Como habrás visto con la tabla dinámica hacés rápidamente el filtrado y consulta de información que quieras ¿para que complicarse con otras cosas? ¿para que agregar varias Hojas de los meses con los procesos si de última vas a ver una solo Hoja a la vez? o sea, con la tabla dinámica seleccionas el mes que quieras consultar lo mismo que si seleccionaras una Hoja de un mes, con la ventaja que con la tabla dinámica tenes todo en una sola Hoja y no en 12.
Pero bueno, esto que te sugiero como te lo mencioné es en base a lo que entiendo  de tus archivos y en base a lo que he aprendido sobre base de datos y consultas. Siempre hay que buscar la economía y  simplicidad en los informes: por un lado para  tener una vista clara y fácil de  analizar de los datos consultados y por otro para evitar posibles errores en el proceso de consultar información y una tabla dinámica en este sentido no falla.
 
4)justo a eso me refiero;lo que intente es "ordenar" alfabeticamente,pero no lo hace,x eso mi consulta
Puede darse que hay ciertos datos que no se pueden ordenar porque están supeditados a otros datos, aunque en esto no estoy seguro del todo. En todo caso hay que buscar ordenar por otro campo.
 
5)gracias x esa respuesta,pero lo que yo quiero es que las macros"que no tienen boton asignado"esa son las que quiero sacar (ya que no las estoy usando(igual esto no es tan importante asi que no te quiero molestar mucho con esto)
 Como te decía, no queda otra que ir al editor y eliminar una a una de forma manual las macros que no utilizamos.
 
6)x ultimo,me encanta esto de la tabla dinamica y el query,reitero que no lo conozco ,pero creo que si me gustaria hacerlo de esta manera y poder aprender a usarla.
Después te paso un par de archivos de ejemplo para que lo practiques esto de Microsoft Query.
 

Saludos

10 espera UN POCO el 14/04/14, 02:18 pm

rubito1


tyno,dejame masticarlo un poco ya que me parece que tenes razon;mientras ,ya que estoy practicando con esto,te hago una pregunta,
1)vos pusistes en  segmentac de datos(en adelante dire sd) years y months los cuales toman los datos de la fecha pedido(año y mes);como puedo hacer eso mismo pero para poder usarlo en otros campos(que tome datos de fecha entrega/fecha disponible,etc)(ya que me interesaria para el informe futuro ,filtrar el mes y como esta ahora me daria x dia)
el punto 4 (ordenar x el filtro,no se podra?,probe con otro campo y tampoco)(SOLO ORDENANDOLO X NUMERO DE PEDIDO AHI SI ME DEJA)
5)ok(finish
6)espero "ansioso"

tyno


1)vos pusistes en  segmentac de datos(en adelante dire sd) years y months los cuales toman los datos de la fecha pedido(año y mes);como puedo hacer eso mismo pero para poder usarlo en otros campos(que tome datos de fecha entrega/fecha disponible,etc)(ya que me interesaria para el informe futuro ,filtrar el mes y como esta ahora me daria x dia)
Lo de year/month quedo en ingles porque el Excel 2010 que tengo está en ingles y como te pase el archivo ya quedo así.
Para hacer esto mismo en otro campo con fechas los primero que tenes que hacer es agrupar las fechas:  seleccionas una celda de la columna con fechas que quieras agrupar y click derecho, seleccionar “Agrupar”, con esto va a aparecer un pequeño cuadro de lista donde vas a seleccionar agrupar por “Año” y “Mes”,  lo cual va a insertar estos dos nuevos campos en la tabla dinámica, posiblemente te ponga como nombres Año y Mes o Año2 y Mes2, fijate que en este cuadro de lista para agrupar, en la parte superior la fecha “Hasta” sea superior a la “Fecha inicial”. En todo caso ponele 31/12/2999 en fecha “Hasta”.
Luego vas a la lista de campos (la que aparece a la derecha de la Hoja Excel-si no la ves das botón derecho sobre la tabla y seleccionas mostrar lista de campos-) y ocultas estos nuevos campos Año y Mes que acabas de crear, esto porque vas a utilizarlos en la segmentación de datos según tu consulta.
 
el punto 4 (ordenar x el filtro,no se podra?,probe con otro campo y tampoco)(SOLO ORDENANDOLO X NUMERO DE PEDIDO AHI SI ME DEJA)

Indicame que campo estas queriendo ordenar que no podes y veo si se puede hacer algo

12 probe pero.. el 14/04/14, 03:36 pm

rubito1


1)ok,lo hice pero me pone en donde antes figuraba la fecha exacta el mes,y ademas en la lista no me sale como decis month2 ,solo sale en la lista lo que hicistes vos (month+years)sera que no admite 2?

tyno


¿cual es el campo que estas queriendo agrupar?

rubito1


1 ES EL DE "FECHA DISPONIBLE" (COL L)Y EL OTRO ES EL DE "FECHA DE ENTREGA"(COL O)

tyno


Hola Rubito.
Recién probé de agrupar las columnas que mencionas y no hay drama. Para cada caso me creó una columna Año2/Mes2 y Año3/Mes3.
O bien no me doy cuenta de lo que te esta pasando, sino subí el archivo a dropbox con los datos que tenes y los cuales te generan el conflicto e indicame cual es el problema. Saludos

16 SUBI EJEMPLO el 15/04/14, 12:32 am

rubito1


ENLACE (MARQUE CON AMARILLO POR EJEMPLO UNA COLUMNA QUE QUIERO


https://www.dropbox.com/s/qenezyc3y7l76bu/produccion%202014%20bisbis.rar

BAJAR

tyno


Hola Rubito.
Te paso el archivo al cual le agregado las dos columnas de Año y Mes para Fecha Disponible. No se bien que problemas tenias pero pude agrupar y crear la segmentación para esta columna sin complicaciones.
Si querés cambiarle el nombre a cada cuadro de segmentación, haces click derecho sobre el mismo y en Propiedades de segmentación (abajo) en Mostrar Encabezado le asignas el nombre que quieras.

También agregué un par de instrucciones en el editor de macros de la Hoja RESUMEN(donde esta la tabla dinámica) para que al seleccionar dicha Hoja la veas en pantalla completa, si querés ver pantalla normal solo hace Escape y podes ver las Herramientas. Cuando salgas de la Hoja RESUMEN se ve en pantalla normal de nuevo. Si no te interesa esta opción vas a tener que borrar las lineas que te indico abajo:

Private Sub Worksheet_Activate()
On Error Resume Next
Application.ScreenUpdating = False'<<<<<<<<<<BORRAR
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Application.DisplayFullScreen = True '<<<<<<<<<<BORRAR
Application.ScreenUpdating = True'<<<<<<<<<<BORRAR
End Sub


Private Sub Worksheet_Deactivate()'<<<<<<<<<<BORRAR
On Error Resume Next'<<<<<<<<<<BORRAR
Application.ScreenUpdating = False'<<<<<<<<<<BORRAR
Application.DisplayFullScreen = False'<<<<<<<<<<BORRAR
Application.ScreenUpdating = True'<<<<<<<<<<BORRAR
End Sub'<<<<<<<<<<BORRAR



Otra  cosa que quizás te pueda servir, vas a ver que inserté una fila para el total por numero de pedido, probá de hacer click dos veces seguidas en alguno de estos totales en la celdas de la columna Total (Q), por ejemplo la celda Q40 donde esta el total del pedido nº 28161, lo cual te va a insertar una Hoja con el detalle completo del registro seleccionado, luego podes eliminar esta Hoja.
Esto es una propiedad de la tablas dinámicas.

https://www.dropbox.com/s/d8r19rvft6yinc8/produccion%202014%20bisbis.zip




Saludos



Última edición por tyno el 15/04/14, 11:11 am, editado 1 vez

tyno


Aquí te paso para que descargues un video que hice de un ejemplo sobre Microsoft Query.

La idea es que descargues dicho video y en base a la explicación de abajo hagas la prueba usando Microsoft Query (que dicho sea de paso lo voy a poner como tutorial en el foro)

https://www.dropbox.com/s/m644ocivbwcbmr8/datos%20externos.rar

Explico el ejemplo:
He creado dos archivos Excel uno llamado ORIGEN que sería el archivo principal con los datos que deseamos se vean en otro archivo, en este caso es una tabla de datos en la Hoja DATOS, también esta el archivo DESTINO, que sería el archivo donde vamos a establecer la conexión con los datos del archivo ORIGEN.

1-Entonces en el archivo, en la Hoja donde queremos insertar los datos externos, en la pestaña Datos, seleccionamos el botón De otras fuentes, opción Desde Microsoft Query, 
2-Aparece el asistente para crear la consulta, seleccionamos Excel Files*, Aceptar.
3-En la ventana Seleccionar Libro en la lista de la izquierda seleccionamos la ruta donde se encuentra el archivo principal. Cuando demos con la ubicación de archivo en la lista de la derecha veremos el archivo Excel que contiene los datos de origen, en este caso es el archivo llamado ORIGEN.xlsx.
4-Seleccionamos el archivo, en esta instancia puede aparecer un mensaje que diga "EL origen de datos no contiene tablas visibles", si es así, damos Aceptar y aparece un nuevo asistente con dos listados en blanco, seleccionamos el botón inferior Opciones... y verificamos que todas las Opciones de tabla estén tildadas.
5- Ahora deben verse las Hojas del archivo que hemos seleccionado en el cuadro de lista izquierda. 
6 Entonces seleccionamos la Hoja DATOS ya que es la que contiene los datos que vamos a vincular. Podemos darle a la + y ver las columnas que de la tabla y de esta manera pasar al cuadro de lista  de la derecha solo las columnas que deseamos que otros vean o bien seleccionamos todas la Hoja y así pasamos todas las columnas.

7-Por ultimo seleccionamos la celda de destino, es decir, a partir de donde se insertara la tabla vinculada en el archivo DESTINO, damos aceptar y ya tenemos todos los datos del archivo ORIGEN. 
En este punto podemos elegir insertar los datos como una tabla común o una tabla dinámica. Si seleccionamos esta opción de tabla dinámica una vez que esta se inserte en la Hoja damos click derecho sobre la misma y en opciones de tabla dinámica  en la pestaña Mostrar tildamos la opción tabla dinámica clásica (la de Excel 2003) y quitamos el tilde en el primer casillero Mostrar botones expandir/contraer, luego pasamos a disponer los campos como deseamos. O sea, se puede elegir el diseño de tabla dinámica que se quiera, yo prefiero la de 2003 a mi gusto es la más practica.


8-Luego es cuestión de probar de modificar los datos en el archivo ORIGEN y en el archivo DESTINO con Alt+F5 o en la pestaña Diseño damos a la opción actualizar y veremos como las modificaciones se actualizan en el archivo DESTINO, esto se puede hacer con el archivo de origen tanto cerrado como abierto.

19 esperar el 16/04/14, 08:02 am

rubito1


que tal tyno?te pido un poco de paciencia por unos dias ya que me surgieron unos temitas x resolver asi luego puedo retomar esto;empece a ver tu video y parece espectacular!!!(es sin sonido,no?)no quiero contestar rapido sino que quiero estudiar bien todo el material que me distes que es superinteresante y novedoso para mi,por eso (al margen de mis temas x resolver)quiero estudiarlo detenidamente y luego continuarlo;queria decirte esto ya que sos demasiado atento para dejarte colgado unos dias sin responderte despues de todo este material que me enviastes;saludos(y felices pascuas)y nos contactamos en unos dias

tyno


El video es sin sonido, pero para eso lo explique al procedimiento. Saludos

21 excelente el 16/04/14, 09:19 pm

rubito1


no pude esperar mas a terminar mis cosas y decidi ver tu video;excelenteeee! esta barbaro,gracias

22 practica el 20/04/14, 11:09 pm

rubito1


tyno,estoy practicando y aprendiendo despues de ver tu video;te contacto en breve nuevamente;gracias

Ver el tema anterior Ver el tema siguiente Volver arriba  Mensaje [Página 1 de 1.]

Permisos de este foro:
No puedes responder a temas en este foro.