Este es el problema del presupuesto de los bomberos. La gente de una estación de bomberos ha estado haciendo mal sus presupuestos en Excel. Una sorprendente transformación de Power Query proporciona la solución.
Ver video
- Steve tiene que sumar los números que se han ingresado en una columna de texto
- Hay varias líneas en cada celda, separadas por alt = "" + Enter
- Necesita dividir esas líneas en filas, luego analizar el monto en dólares del medio de cada celda
- Resumir por centro de costos
- Construye una tabla de búsqueda
- Obtenga totales de la tabla de búsqueda, usando IFNA para ignorar los errores en la fila en blanco
- Bonificación: agregue una macro de evento para actualizar la hoja de trabajo cuando cambien una celda.
Transcripción del video
Aprenda Excel de, Podcast Episodio 2160: Datos SUM que se han introducido Alt +.
Oye. Bienvenido de nuevo al netcast. Soy Bill Jelen. No me lo estoy inventando. Recibí una pregunta de alguien que tiene datos, datos presupuestarios, que se ve así. Ahora, pongo palabras falsas aquí para que no tengamos su información presupuestaria, pero la persona es nueva en el departamento de contabilidad, fue a una empresa y esta empresa durante años ha estado haciendo sus presupuestos de esta manera. No son contadores que hacen el presupuesto, son gente de línea, pero así es como lo han estado haciendo y él no puede hacer que cambien. Entonces, este es nuestro objetivo. Dice que esto es tan malo como escribir el presupuesto en Word.
Bueno, casi, pero afortunadamente, gracias a Power query, va a salvar nuestro problema. Este es nuestro objetivo. Por cada CENTRO DE COSTOS aquí, queremos informar el total de todos esos números. Entonces, hay un nombre de gasto, un -, habitualmente un -, luego un signo $, y luego, solo para hacer la vida interesante, de vez en cuando, una nota aleatoria después; no todas las veces, solo algunas veces. Fila en blanco entre cada uno. Toneladas y toneladas de datos.
Entonces, esto es lo que voy a hacer. Voy a ir al final, a la última celda, voy a seleccionar todas estas cosas, incluidos los títulos. Voy a crear un NOMBRE. Lo voy a llamar MyData. MyData, así, ¿de acuerdo? Bien. Ahora vamos a utilizar Power query, que es gratuito en 2010 o 2013, integrado en 2016 y 2016 Office 365. Eso vendrá de una TABLA O RANGO. Bien. En primer lugar, siempre que tengamos esos espacios en blanco en la COLUMNA A, todos los NULL de los que queremos deshacernos. Así que voy a desmarcar NULL. Increíble. Bueno. Realmente, en estos datos, en esta versión de los datos, porque voy a construir una VLOOKUP, no necesitamos esta columna. Entonces, voy a hacer clic derecho y deshacerme de esa columna, así que QUITAR columna.
Bien. Ahora, aquí es donde sucederá la maldita magia. Elija esta columna, DIVIDIR COLUMNA POR UN DELIMITADOR, y definitivamente vamos a entrar en AVANZADO. El delimitador será un carácter especial y vamos a dividir cada aparición del delimitador. Entonces, aquí, creo que ya lo han descubierto porque lo expandí, pero te lo voy a mostrar. INSERTAR CARÁCTER ESPECIAL. Voy a decir que es un AVANCE DE LÍNEA, está bien, entonces, en cada aparición del AVANCE DE LÍNEA, y voy a DIVIDIR EN FILAS. Muy bien, y lo que va a pasar aquí es, 1, 2, 3, 4, 5, voy a obtener 5 filas o voy a decir 1001, pero, en cada fila, va a tener una diferente línea de esta celda. Esto es increíble. Hay 1, 2, 3, 4, 5, 1001. Muy bien. Ahora solo tenemos que analizar a este chico malo. Bien,entonces, elija esa columna, DIVIDIR COLUMNA POR UN DELIMITADOR. Esta vez, un delimitador será un signo $. Eso es perfecto, una vez, en el primer signo $ que encontramos, en caso de que haya un signo $ en la parte futura. Nos vamos a DIVIDIR EN COLUMNAS. Haga clic en Aceptar. Bien. Entonces, hay detalles. Aquí está nuestro dinero.
Ahora, voy a dividir esto en el ESPACIO. Entonces, elija esta columna, DIVIDIR COLUMNA POR UN DELIMITADOR, y el delimitador será un ESPACIO, sí, una vez en el DELIMITADOR MÁS IZQUIERDO, haga clic en Aceptar, y no necesito esos comentarios por ahí, así que esos comentarios serán ' vas a QUITAR. En realidad, tampoco necesito esto porque solo estoy tratando de obtener un total de todas esas cosas, así que voy a ELIMINAR.
Ahora, transfórmate. GRUPO POR CENTRO DE COSTOS, EL NUEVO NOMBRE DE LA COLUMNA se llamará TOTAL, la OPERACIÓN será la SUMA, y ¿a qué columna vamos a SUMA? Los DETALLES 2.1. Hermosa. Haga clic en Aceptar, de acuerdo, y lo que obtenemos es una línea por CENTRO DE COSTOS con el TOTAL de todas esas líneas. CASA, CERRAR Y CARGAR. Probablemente va a insertar una nueva hoja de trabajo. Espero que inserte una nueva hoja de trabajo, y lo hace, y esa hoja de trabajo se llama MYDATA_1. MYDATA_1.
Bien. Ahora volveremos aquí a los datos originales y seguiremos estos pasos. En el primero, = BUSCARV de 1001 en nuestros resultados. Es como configurar una referencia circular, pero no nos dará una referencia circular. , 2, FALSO. Quiero la coincidencia exacta. Muy bien, pero no vamos a querer hacer eso con las celdas en blanco. Entonces, voy a decir, bueno, en realidad, vamos a copiarlo por completo. CONTROL + C, baja hasta el final solo para ver lo que obtenemos. Quizás obtengamos N / A y pueda deshacerme de él con IFNA. Sí, hermosa, está bien. Entonces, eliminemos los N / A. Si es N / A, entonces solo queremos “”. No queremos nada ahí. CONTROL + ENTER. Bien. Ahora, ese debería ser el TOTAL. Veamos si podemos encontrar uno corto y hacer los cálculos. = 627,37 + 7264,25 + 6066.01 + 4010.66 + 9773.94, y el TOTAL, 27742.23 es eso. Increible. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), “”))
Ahora, este es el trato. Entonces, tenemos a esas personas de línea que están aquí cambiando cosas, está bien, y digamos que pasan y cambian el presupuesto, 40294.48, y vienen aquí y cambian este a 6000, así, y agregan uno nuevo, ALT + ENTRAR, ALGO - $ signo, $ 1000 recién agregado. Bien. Ahora, por supuesto, cuando presiono ENTER, este número, 40294.48, no se va a actualizar, está bien, pero lo que tenemos que hacer es ir a la pestaña de DATOS y queremos ACTUALIZAR TODO. Entonces, 40294.48. Mira, mira, mira, mira. REFRESCAR TODO. Súper alucinante.
Me encanta la consulta de energía. Power query es lo más sorprendente. Estos datos, que esencialmente son como datos de palabras en una celda, ahora los tenemos actualizados. Probablemente incluso podría hacer algún tipo de macro que diga que cada vez que alguien cambia algo en la COLUMNA C, seguimos adelante y hacemos clic en ACTUALIZAR TODO usando la macro y solo tenemos esos resultados constantemente, constantemente actualizados.
Qué pregunta tan horrible me envió. Me siento mal por Steve que tiene que lidiar con esto, pero ahora, usando Power query en Office 365 o descargado para 2010 o 2013, tiene una manera muy, muy fácil de resolver esto.
Espere. Bien, un apéndice: hagámoslo aún mejor. Esta hoja se llama DATOS y he guardado el libro como habilitado para macros, por lo que xlsm. Si es xlsx, no omita guardar como xlsm. ALT + F11. Busque el libro de trabajo llamado DATOS, haga doble clic, arriba a la izquierda, HOJA DE TRABAJO, y luego CAMBIAR cada vez que cambiemos la hoja de trabajo, y vamos a decir ACTIVEWORKBOOK.REFRESHALL, y luego cierre, está bien, y ahora probémoslo. Editemos algo. Entonces, tomaremos esas frambuesas que actualmente son 8,000 y las cambiaremos a 1000, así que reduciremos en 7000. Cuando presiono ENTER, quiero ver que 42,000 bajen a 35,000. ¡Ah! Increíble.
Este hola. Aquí es donde normalmente les suplico que compren mi libro pero, hoy, les voy a pedir que compren el libro de mis amigos - Ken Puls y Miguel Escobar - M es para (DATA) MONKEY. Todo lo que aprendí sobre Power query, lo aprendí de este libro. Es un libro asombroso. Mira eso.
Resumen del episodio: Steve tiene números para sumar que se han ingresado en una columna de texto; múltiples líneas en cada celda, separadas por ALT + ENTER; necesita dividir esas líneas en filas, luego analizar el monto en dólares del medio de cada celda; resumir por COST CENTER; construir una tabla de búsqueda; obtenga totales de la tabla de búsqueda, usando IFNA para ignorar los errores en la fila en blanco; y luego, una macro adicional, al final, una macro de eventos para actualizar la hoja de trabajo cuando cambian una celda.
Quiero agradecer a Steve por enviar esa pregunta y estoy muy contento de tener una respuesta; antes de la consulta de energía, hubiera sido muy, muy difícil, y quiero agradecerles por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2160.xlsm
