Transcripciones
1. Introducción: Hola, mi nombre es George, y estoy aquí para enseñarte las habilidades para ser mucho más proficiente en el uso de Excel. En este curso, te estaré mostrando cómo crear tu propio rastreador de negocios, hoja de cálculo de
ganancias y pérdidas para elevar tu negocio y realizar un seguimiento de tu crecimiento. C o ingresos, gastos y ganancias y pérdidas. Fácilmente. Realiza un seguimiento de tu inventario de productos y haz que se actualice automáticamente. Por último, personalizado para adaptarse a su marca y negocio. Pero lo que es más importante son las habilidades aprendidas el cabello
se puede transferir a tantas tareas diferentes dentro de Excel. El curso se divide en cinco secciones y tendrás un cuaderno proporcionado para permitirte seguirlo fácilmente. Te estaré mostrando cómo crear dashboards que sean dinámicos y cambien automáticamente dependiendo de los días que ingreses. Mejorarás algunas habilidades fundamentales de Excel y aprenderás funciones como el uso de rangos con nombre , Formato
condicional, y toneladas de fórmulas diferentes. Lo que han querido mostrar el cabello fue difícil de usar muchas de las funciones de Excel que tienes disponibles en un producto verdaderamente útil. Después de este curso, ¿no sólo contarás con una herramienta fácil de usar y
sencilla para rastrear tu negocio? Pero también habrás aprendido habilidades que muchos usos avanzados de XR desconocen. Y ahora empecemos.
2. Lección 1: categorías: En este primer video, estaremos creando nuestras categorías para usar dentro del resto del cuaderno de trabajo. Lo construiremos para que puedas sumar hasta 20 tipos de ingresos diferentes, tipos gastos, tipos de transacciones, y productos. Lo realmente genial que aprenderás hoy es cómo crear rangos con nombre
dinámico para todas tus categorías. El motivo por el que estaremos utilizando estas categorías como listas desplegables en otras pestañas. Y nadie quiere ver montones de espacios en blanco en la lista desplegable. Las habilidades aprendidas heck, sean transferibles a muchos escenarios diferentes. Entonces primero, abre tu libro de trabajo de rastreador de negocios y ve a la pestaña de categorías. Entonces, empecemos. En primer lugar. Vamos a crear nuestros tipos de ingresos. Para hacer las cosas más fáciles. He destacado celdas en naranja donde debes ingresar datos para los tipos de ingresos lo iniciarán antes. Se sumará a los tipos de ingresos, y los llamaremos ventas de productos y marketing de afiliados. A continuación, agreguemos algunos tipos de gastos. A partir de S4 se introducirán las tarifas de transacción, fase de
publicidad, los costos del producto, y las tarifas de envío. Pasar a tipos de transacción ingresará a algunos comunes, como ingresos, gastos, saldo y transferencia. Ahora pasando a la sección de productos, agreguemos algunos nombres y categorías de productos ficticios. Al entrar una startup, no aplicable tanto en el nombre del producto como en la categoría del producto. No tiene todas las transacciones estarán relacionadas con un producto y queremos esa flexibilidad. A continuación, entraremos sofá, mesa, cama, armarios, y escritorio como los nombres de productos en la columna F. Y finalmente crearemos las siguientes categorías en la columna G. Ahora, como se mencionó antes, al
final de este curso, podrás entrar más o me refería a las categorías que hemos ingresado para que esto te resulte útil. No obstante, para el curso asegurate que te guardes con los mismos que he entrado. Ahora viene el bit interesante, cómo crear rangos con nombre dinámico comenzará por crear un rango con nombre normal. Destacemos así antes a B203 y nombrando a este rango de células ingreso yendo al recuadro de direcciones en la esquina superior izquierda e ingresando ingresos. Ahora la razón por la que estamos creando estos rangos nombrados es demasiado fácil. Refiéralos en otras hojas de trabajo en listas desplegables. Te mostraré un ejemplo de lista desplegable y los beneficios de un rango con nombre dinámico. Para aquellos de ustedes que quieran una introducción a rangos nombrados, por favor echa un vistazo a mi Curso de Principiantes. Tengo una sección sobre ella, la celda K4, y la resaltaré en un color diferente rápidamente solo para hacerla más visible. Después irá a la validación de datos y datos en Permitir, seleccione lista. Y luego el tipo fuente. Y diez es igual a ingresos. Notarás que el cuadro desplegable tiene muchas celdas en blanco, lo cual no es genial y puede causar problemas. Por lo tanto, ahora vamos a crear un rango con nombre dinámico para que sólo las celdas no en blanco se afilen usando la función de desplazamiento. Déjame mostrarte cómo funciona primero esta función, la función offset devuelve una celda o rango de celdas que es un número especificado de filas y columnas de una celda o rango de celdas. Empezar a escribir igual offset. Abrir paréntesis. El primer argumento es la celda inicial a la que queremos hacer referencia let Center antes como ejemplo. Entonces el siguiente argumento es el número de filas arriba o abajo desde la primera celda. Entraremos 0 como queremos empezar desde muy, para. El siguiente argumento es el número de columnas. Nuevamente, ingresaremos 0 como queremos hacer referencia a la columna b Seguidamente
ingresaremos la altura, que haremos dinámica mediante el uso del conteo una fórmula en la columna B. Contar a contará todas las celdas que contengan datos. Y de ahí, a medida que sumamos nuevas categorías, esto cambiará. Entonces ingrese contar a, abra paréntesis, y resalte la Columna B, y luego cierre el paréntesis. Y como tenemos un título que se contará, por lo que entraremos minús1 después de esto. Y por último, por el ancho, ingresarás una ya que sólo queremos que se devuelva una columna. Después cierra paréntesis. Como puede ver, se muestran las únicas categorías a las que ingresamos. A medida que agregamos o eliminamos una categoría, verás que lo que se muestra se actualiza automáticamente. Ahora actualicemos nuestro rango nombrado con esta fórmula. En primer lugar, vaya a fórmulas y luego nombre manager. Seleccione el rango de ingresos con nombre, y vaya al cuadro de fórmulas en la parte inferior. Aquí, utilizará el comando Desfase para crear un rango con nombre dinámico. Entonces escriba igual offset, paréntesis
abierto, categorías, signo de exclamación. Antes, como queremos hacer referencia a la hoja de trabajo. Y pondremos una referencia absoluta alrededor esto en 0 para los argumentos de fila y columna. Y para contar a, paréntesis
abierto, categorías signo de exclamación, columna B con una referencia absoluta, luego cerrar paréntesis y uno menos para el argumento altura. Y por último, uno para el argumento width. Como atajo, podrías haber copiado la fórmula que
creamos un m3 y simplemente pegarlo eso también. Y finalmente haga clic en la garrapata verde. Ahora mirando hacia atrás a la lista, podemos ver que sólo contiene las categorías que definimos. Y si agregamos más, la lista se actualiza automáticamente. Ahora vamos a crear los rangos nombrados restantes empezando por el gasto. Vuelve al encargado nombrado de fórmula. Y vamos a copiar la fórmula que utilizamos para el rango de ingresos para acelerar esto. Entonces seleccióname. Tipo gasto en la fórmula de ingresos. Cambiar la referencia a C4. Y cuentan un alcance para ver y golpear OK. Haremos lo mismo para los tipos de transacción. Dará nombre a este tipo T. Y vamos a cambiar la referencia a D4 y el recuento a rango a D. Lo mismo para nombre del producto,
nombrará a este nombre orgulloso. Y cambiaremos la referencia a F4. Y cuentan una gama a F. Y por último, la categoría de producto nombrará a este orgulloso gato y cambiará la referencia a G4. Y cuentan un alcance a G. Y ahí lo tienes. Hemos creado nuestras categorías para ser utilizadas en todo el libro utilizando rangos con nombre dinámico y la función de desplazamiento en Excel. También te he presentado otras funciones como listas de validación de datos. Estas son solo unas pocas que estarás utilizando a lo largo de este curso. Seguir avanzando. Y para el final de esto, tendrás una hoja de cálculo de seguimiento de negocios,
ganancias y pérdidas realmente genial .
3. Lección 2: objetivos: En este siguiente video, estaremos creando los objetivos que enlazarán a nuestras categorías que hemos definido en el video anterior y nos permitirán establecer un objetivo específico de ingresos o gastos por cada categoría. Estaremos usando si las declaraciones bastante extensamente aquí. Por lo que introduce a quienes no lo han utilizado a una nueva función. En primer lugar, ¿puedes abrir el negocio, realizar un seguimiento de un libro de trabajo e ir a la pestaña de objetivos? Antes de empezar, he prepoblado algunas fórmulas como se muestra aquí. Estos simplemente sumarán cualquier dato que ingresemos para resumir los objetivos de ingresos y gastos para cada periodo. Entonces, comencemos sumando nuestras categorías. Queremos que esto sea dinámico y se actualice desde la pestaña de categorías. Pero ya hemos hecho el duro trabajo y podemos referirnos a uno de nuestros rangos nombrados que hemos creado. Entonces empecemos por ir a vender POR 13 y escribir ingresos iguales. Se puede ver que ahora se están mostrando las categorías de ingresos que hemos definido. Al igual que en los videos anteriores. He resaltado cualquier celda en naranja como una celda donde el usuario ingresa texto libre o números. Digamos para las ventas de productos tenemos una meta de 1000 al mes. Por lo que escribiremos un 1000 en d 13. Y para el marketing de afiliados, tenemos un objetivo de un 100 por mes. Entonces ingrese un 100 en d 14 luego usará esto para calcular los objetivos semanales, trimestrales, anuales, y de historial completo. En primer lugar, usemos una declaración if para calcular el objetivo semanal, que es la cifra mensual, dividida por 4.3. Al ser 4.3 semanas en un mes. Vaya a c 13 e introduzca iguales, si se abren paréntesis. Si d 13 es igual a blanco, definido por estas comillas, entonces en blanco para encontrar de nuevo por las comillas. De lo contrario d 13 dividido por 4.3. Y arrastra eso hacia abajo. A continuación, el trimestral. Vaya a E 13 e introduzca igual si se abre paréntesis, el 13 es igual a blanco. Entonces en blanco. De lo contrario d 13 veces tres. Y el anual van a F 13 e ingresan igual f, paréntesis
abierta, d 13 es igual en blanco, luego en blanco. De lo contrario d 13 veces 12. Y por último, toda la historia. Ahora esta será una fórmula más complicada. Ya que tu historia completa no es una constante. Podrían ser tres meses o tres años. Por lo que necesitamos ser un cabello un poco inteligente para calcular el objetivo para el mismo periodo de tiempo. Tendremos que encontrar el número de días que has estado operando. Divida eso por 365, y luego multiplica eso por tu objetivo de ingresos anuales. Te pasaré por eso ahora. Comienza igual que antes. Vaya a g 13 e ingrese igual f, abra paréntesis, d 13 es igual a blanco, luego en blanco. Ahora con el fin de encontrar el número de días que has estado operando, podemos usar la función min y max en tu historial de transacciones. Escriba, max, abra paréntesis, y luego haga clic en las transacciones que realmente estamos y la columna de referencia B. Y luego presione F4 una vez para poner una referencia absoluta alrededor de esto, que cuando copiemos esta fórmula, siga siendo columna de referencia B. A continuación, escriba restar, y luego MIN abrir paréntesis. Y nuevamente referencia columna B y presione F4 una vez para poner una referencia absoluta. Ponga estas fórmulas entre paréntesis. Después divide por 365, y vuelve a poner estas fórmulas entre paréntesis. Y finalmente multiplicar por F 13 en la hoja de trabajo de objetivos y cerrar paréntesis. Y solo arrastra hasta ahí lo tienes. Una vez que rellena los datos de las transacciones, tus objetivos de historial completo se actualizarán automáticamente. Hagamos ahora lo mismo para las categorías de gastos. Escriba gasto igual en IEEE 13 para
hacer referencia al gasto llamado rango que hemos creado en el video anterior. A continuación, ingrese los objetivos de gastos mensuales. Pondremos algunos números ficticios y nos aseguraremos de que sean negativos. Así que poner menos 50 por mes para la fase de transacciones, menos 200 por mes para publicidad, fase, menos 500 por mes para producto, fase menos 100 por mes para gastos de envío. Ahora para entrar a las fórmulas, sólo
podemos copiar las fórmulas que hicimos para los ingresos. Seleccione C 13 TC 30 para copiar las celdas y pegarlas en J 13. Ahora haz lo mismo para las fórmulas trimestrales, anuales y de historia completa. Se puede ver para el historial completo, seguimos haciendo referencia a la columna B. Si no pusiéramos una referencia absoluta y copiáramos esto a través de las columnas habría cambiado. Mi curso de principiante también cubre referencias absolutas y relativas. Por lo que por favor echa un vistazo a eso si necesitas una introducción. Y por último, poblemos el objetivo de ingresos anuales y el objetivo de beneficio anual. Para objetivo de ingresos anuales vaya a F4 y tipo igual a F 12. Para el objetivo de beneficio anual, vaya a L4, tipo igual F2 más m2. Y esos fueron los objetivos realmente cumplidos. En este video, has visto cuán efectivas si las declaraciones pueden ser para completar diversas tareas. También han utilizado algunas fórmulas relativamente complejas, sobre todo cuando se intenta calcular los objetivos de historia completa. En la siguiente sesión, estaremos pasando por las transacciones fueron en realidad las cuales brevemente tocamos hoy.
4. Lección 3: transacciones: Entrar. Si el paréntesis abierto E5 es igual a blanco con las comillas, entonces tablón. De lo contrario, algunos paréntesis abiertos. Seleccione K4, luego I5, luego menos J5. Y cerrar paréntesis. Copia esto hacia abajo para remar un 100. Esto resumirá todas sus transacciones entrantes y salientes para proporcionar un monto total de saldo. Ahora en lugar de que tengas que introducir transacciones manualmente, he creado algunas transacciones ficticias que puedes copiar y pegar valores en esta hoja de trabajo. Para las transacciones archivos guardados en el proyecto, y copiar y pegar valores. Todas las transacciones como psi. Sólo recuerda, no pegues sobre la categoría de producto ni fórmulas de saldo total. Y ahí lo tienes. En este video, cubrimos algunos usos más complejos de la validación de datos. Si las declaraciones y V búsquedas. También ahora tienes una transacción de trabajo C, que será el principal conductor de las próximas hojas de trabajo. En el siguiente video, crearemos nuestro panel de control de inventario de productos.
5. Lección 4: productos: En este video, crearemos tu panel de control de inventario de productos, lo
construiremos para que la hoja de trabajo se complete automáticamente a medida que añadas transacciones. No necesitas preocuparte por hacer un seguimiento manual de tu lista de productos. Será impulsado directamente desde sus transacciones. Primero abrió el negocio rastrear un libro de trabajo, y vaya a la pestaña de productos. En esta hoja de trabajo se estará utilizando rangos con nombre, SUMIFS, y es funciones de error para asegurar que las fórmulas atienden a cualquier escenario. Y es posible que hayas notado que las células están resaltadas en gris. Esto sigue el mismo formato que los videos anteriores. Las celdas grises indican que ingresarás aquí una fórmula. Entonces, empecemos con el nombre del producto. Al igual que en videos anteriores, podemos sacar a través de todos los nombres únicos de productos haciendo referencia al rango nombrado. Ve a B5 y entra igual nombre orgulloso. Para las categorías de productos, ingresaremos si vLookup fórmula combinación k2, C5, e ingresaremos igual f, paréntesis abierto. B5 está en blanco. Entonces en blanco. De lo contrario vLookup, abra el paréntesis B5 para el valor de búsqueda, que es el nombre del producto, y las categorías, FTG para la matriz de tablas. Y tirar de tres columna dos, y luego ir por una coincidencia exacta. Y luego cerrar paréntesis. Ahora, arrastra esta fórmula hasta el fondo. Para las siguientes columnas, crearemos inicialmente una versión más simple cual calculará cifras en función del historial completo de transacciones. Pero en el siguiente video, nos aseguraremos de que sea aún más dinámico y que
estas cifras se actualicen en función del periodo de tiempo que seleccionemos. Para. Ahora, primero calculemos el total comprado. Vaya a D5 y escriba igual f, abra paréntesis. B5 está en blanco. Entonces en blanco. De lo contrario usamos alguna fórmula que nos permite resumir datos en base a criterios específicos. Entonces ingresemos el rango de suma como la columna Cantidad en la hoja de trabajo de transacciones. Y el primer rango de criterios es la columna del nombre del producto. Y el criterio será el nombre del producto en el producto. En realidad somos el siguiente criterio es la columna de tipo de transacción en la hoja de trabajo de transacciones. Y los criterios vamos a codificar en la fórmula como gasto. Recuerda poner entre comillas dobles. Ahora pongamos algunas referencias absolutas y mezclamos en las celdas para
asegurarnos de que estén bloqueadas en el lugar correcto cuando rastreemos o copiemos las fórmulas. Por favor, refiérase a mi lección sobre referencias celulares. Si esto va demasiado rápido. Y vamos a arrastrar esto hasta el fondo. Para el total de productos vendidos se copiará la fórmula a
través Ya que sólo necesitamos hacer un pequeño cambio. Cambiemos el gasto por el ingreso ya que cualquier célula entrará como ingresos. Y vamos a arrastrar eso hacia abajo. Para el inventario realizado es simplemente la diferencia entre tu tortuga comprada y tu tortuga vendida. Entonces CO2, F5, y entrar igual f, abrir paréntesis. B5 está en blanco. Entonces en blanco. De lo contrario, D5, compras
totales menos E5, tu tortuga vendida. Y por último, para los productos vendidos rango, podemos usar la función de rango en Excel aquí. Pero un problema con la función de rango es que no atiende escenarios con los números son los mismos. Por lo que hay duplicados. Permítanme mostrarles primero cómo funciona la función de rango. Iremos a la celda G5 y en rango igual, paréntesis abierto. El primer argumento es el número que estamos tratando de clasificar, que es el total vendido para el primer producto. Por lo tanto seleccione E5. Se arregla el segundo argumento. Por lo tanto, seleccione E5 a
E3, E4, y ponga en referencia absoluta golpeando a F4. El tercer argumento es el orden. Por lo que queremos que el número más grande tenga rango uno. Así que entra 0 aquí y arrastra esto hacia abajo a G ten. Se puede ver que el sofá y el armario tenían el mismo número de celdas y el mismo rango. Y también nos falta un rango cinco de la lista. Esto resalta el tema donde estamos tratando de clasificar los números duplicados. Podemos evitar esto haciendo una pequeña adición a la fórmula. Ingresa más conteo si abre paréntesis para tu rango en E5 a E5, pero pon en referencia absoluta alrededor del primer E5. Para los criterios, seleccione nuevamente E5, cierre paréntesis. Y luego menos un paréntesis cercano. Arrastra eso hasta g diez. Y como puedes ver, el sofá y el armario tienen un rango diferente a pesar de que tienen el mismo número de ventas. Esta es una forma muy útil de clasificar datos con duplicados. Tenemos que modificar esta fórmula sólo un poco más para dar cuenta de cualquier espacio en blanco. Entonces ve al inicio y crea una declaración if
que indique cuando el nombre del producto está en blanco, muestra un espacio en blanco. De lo contrario, calcula el rango como decir, y arrastra eso hacia abajo. Y así es como se crea un panel de control de inventario de productos se impulsa a partir de sus transacciones. Puede ir más allá y agregar cálculos para el costo promedio del producto, ventas, beneficios, valores de inventario. Para que esto sea aún más útil. En el siguiente video, estaremos creando el panel principal. Este es el video final del curso y
empatará todas las hojas de trabajo que has creado hasta ahora juntas. Este panel de control contendrá gráficos de sus ingresos, gastos y productos. También será totalmente dinámico, lo que te permitirá elegir tus periodos de tiempo. Y todos los gráficos y cifras se actualizarán en consecuencia.
6. Lección 5: tablero principal: Este es el video final que reunirá todo lo que has hecho en los videos anteriores. Todos los gráficos y datos serán dinámicos. Y tendrás la opción de cambiar
los periodos de tiempo y que todas las cifras y gráficos se actualicen automáticamente. Entonces, empecemos. En primer lugar, ¿puedes abrir el cuaderno de trabajo de Business Tracker e ir a la pestaña principal del panel? Esta hoja está prepoblada con algunos datos simples para empezar. Pero haremos la mayor parte del trabajo juntos para poner en marcha este panel. En primer lugar, empecemos con la parte superior izquierda. Como siempre, las células anaranjadas son células. Ingresamos texto o números. Las celdas azules contienen cuadros desplegables donde seleccionará datos. Y las celdas grises estarán donde ingreses fórmulas. Vayamos a la celda D7 e introduzcamos la fecha del primero de enero de 2019. Entonces vamos a crear la lista desplegable de periodo de tiempo. Seleccionar D8, K2, Validación de datos, seleccionar lista. Y en la fuente ingresaremos manualmente a nuestras opciones de lista. Para la fuente, ingresaremos semanalmente, comas, mensuales, comas, comas
anuales e historial completo, y seleccionaremos Aceptar. Como puede ver, estos ya están disponibles en la lista desplegable. Para la fecha de inicio, tendremos que introducir un extracto if para atender la opción de historial completo. Entonces si el periodo de tiempo seleccionado es igual al historial completo, queremos que la fecha de inicio sea el día de transacción más temprano en la página de transacciones. De lo contrario, queremos que la fecha de inicio sea la ingresada en D7. Esto se puede hacer de la siguiente manera. Introduzca iguales, si se abren paréntesis, D es igual al historial completo dentro de las comillas. Entonces entra a los hombres. Abrir paréntesis. Vaya a la hoja de trabajo de transacciones y seleccione la columna B. De lo contrario, seleccione D7, que es el día de inicio. También llamemos a esta celda como comienzan los guiones bajos de fecha, ya que es mucho más fácil hacer referencia a un rango con nombre. Para la fecha de finalización, necesitaremos múltiples declaraciones si para atender cada periodo de tiempo. Entonces, empecemos. Si periodo de tiempo es igual semanalmente. Y la fecha de fin es la fecha de inicio más seis. Periodo de tiempo igual mensualmente. Y vamos a querer utilizar la función de fecha. Y el año será el mismo que comienza la fecha. El mes sería la fecha de inicio más uno. Y el día será la fecha comienza minús1. Si el periodo de tiempo. Podemos copiar la misma fórmula que usamos para mensualmente, pero solo cambia el mes a 12 meses. Y por último, para la historia completa, tomaremos el estado máximo en la hoja de trabajo de transacciones. Y ahí lo tienes. Era una fórmula bastante larga, pero permite atender a todos los periodos de tiempo. Y como paso final, llamemos a esta celda como subrayado de fecha. Y me gusta hacer una pequeña actualización más que te introducirá al formato condicional. Dado que el rango de fechas del historial completo no requerirá que ingreses un día de inicio, sería mejor dejar en blanco lo que es factible en la celda D7 para evitar cualquier confusión, esto se puede hacer fácilmente con el formato condicional. Simplemente resalta la celda D7. Vaya al formato condicional en la pestaña Inicio. Seleccionar nuevo rol. Ir a usar fórmula para determinar qué celdas dar formato. Donde establece los valores de formato donde esta fórmula es verdadera. Introduzca D8 igual para el historial entre las comillas. A continuación seleccione Formato e ingresó el relleno y el color de fuente tiene el mismo color. Iremos con azul oscuro y luego golpearemos OK. Ahora se puede ver que cuando se selecciona el historial completo, la celda de fecha de inicio de los informes cambia de color y ya no es visible. A continuación, poblemos algunos de los datos en la parte inferior, empezando por los ingresos. Acude a la celda C42 T1. Y usemos nuestro rango de ingresos llamado para poblar las corrientes de ingresos. Para los objetivos, vamos a necesitar jalar a través de los objetivos para el tipo de ingresos correcto en función de los periodos de tiempo seleccionados. Por lo que vamos a utilizar una combinación de la función de índice y la función de coincidencia para hacer eso. Seleccione el índice 4.2.1 tipo igual, abierto paréntesis. Ir a la hoja de trabajo de objetivos. Y la matriz será b 11 a G, 32 en la Hoja objetivo. Y pongamos en absoluta referencia en torno a esto. Para el número de fila, tendremos que buscar el tipo de ingresos. Entonces para ello, escriba, coincida con paréntesis abiertos para el valor de búsqueda de vuelta al panel principal y seleccione el tipo de ingreso en C41. Y estamos buscando este valor arriba en la columna B del objetivo xi. Por lo tanto, seleccione b 11 a B32 como la matriz de búsqueda y ponga una referencia absoluta alrededor de eso. Para el tipo de partido, queremos una coincidencia exacta. Entonces ponga 0 y cierre paréntesis. Y para el número de columna, fuimos a hacer referencia a la columna que coincide con el periodo de tiempo seleccionado en el panel principal. Entonces entrar partido, abrir paréntesis. A medida que el Valor de búsqueda, seleccione, desvíe del tablero principal y ponga en referencia absoluta alrededor de eso. Y para la matriz de búsqueda, seleccione B11 a G11 y ponga una referencia absoluta alrededor de eso. Para el partido, queremos uno exacto. Así que pon 0 y cierra el paréntesis dos veces, y luego arrastra esa fórmula hacia abajo. Ahora te darás cuenta de que tenemos un montón de NAs de los que queremos deshacernos. Esto simplemente se puede hacer con una declaración if. Entonces actualicemos la fórmula. Seleccione la celda D 41, y al inicio, a la derecha, si se abre paréntesis, seleccione C 41, el tipo de ingreso es igual a blanco, luego en blanco y pone otro paréntesis de cierre al final. Ahora si arrastras esta fórmula hacia abajo, nos deshacemos de los NA. Para los ingresos reales necesitaremos utilizar la suma es fórmula para buscar las transacciones trabajo ella y resumir todas las transacciones en
función del tipo de ingresos para el periodo de tiempo específico. Esto es un poco más complicado, pero te acompañaré a través de él. Seleccione y4, z1, y escriba igual a sumifs, paréntesis abierta. Para el rango de verano, vaya a la hoja de trabajo de transacciones y seleccione la columna Ingresos, columna i, y ponga una referencia absoluta alrededor de esto. Para los criterios un rango, seleccione el subtipo de transacciones en la hoja de trabajo de transacciones, columna D, y luego ponga una referencia absoluta. Cuatro criterios. Una, volver al panel principal y seleccionar el tipo de ingresos en el rango de criterios C
414 para ir a la hoja de trabajo de transacciones y seleccionar la columna de fecha, columna B y F Para, para una referencia absoluta. Para nuestros criterios de fecha, sólo
queremos sumar las transacciones que se encuentran entre una fecha de inicio y de fin. Por lo que necesitamos dividir esto en dos criterios separados. Para los criterios, queremos sólo algunos CUALQUIER días que sean más o iguales a una fecha de inicio. Por lo que tendremos que introducir comillas. El mayor que signo. Igual a comillas, el símbolo n. Y luego queremos referir R star a. Así que tipo date underscore start ya este era el nombre del día de inicio que definimos en el panel principal. Para el rango de criterios tres, seleccione la columna de fecha, columna b. nuevamente, queremos sólo algunos, CUALQUIER días que sean menores o iguales a la fecha. Tendremos que introducir comillas. El símbolo menor que es igual a comillas y el símbolo n. Y queremos referirnos a nuestro fin de día. Entonces escribe date underscore end, ya que este era el nombre de la fecha de finalización que definimos en el tablero principal. Ahora sé que eso fue mucho, pero casi estamos ahí. Para atender escenarios en los que pueda tener tipos de transacción negativos para ingresos, IE, devoluciones o reembolsos. Queremos asegurarnos de que estos también se contabilizen. Entonces introduce un signo menos y luego copia toda la fórmula y pega al signo menos. Y todo lo que necesitas para cambiar es el rango de suma desde la columna entrante, columna i a la columna de salidas, columna j Al
igual que hicimos en la fórmula de objetivos, haremos una actualización rápida para que no
se calculen datos cuando no tenemos un tipo de ingreso. Por lo tanto, seleccione la celda E4, t1, y en la estrella, a la derecha, si se abre paréntesis, seleccione el tipo de ingreso en C41 es igual en
blanco, en blanco, y ponga otro paréntesis de cierre al final. Ahora, arrastra esta fórmula hacia abajo. La fórmula de diferencias es mucho más simple. Es solo tu ingreso real menos tu ingreso objetivo. Por lo tanto, seleccione F4, t1 tipo igual, si se abren paréntesis. Tu tipo de ingreso es igual a blanco. En blanco. De lo contrario sus ingresos reales menos su objetivo. Entonces E4, t1 menos t0 41. Y arrastra eso hacia abajo. Haremos lo mismo por gastos ahora. Ve así H 41 y escribe gasto igual para poblar todos tus flujos de gastos. Para los objetivos de gastos, es similar a la fórmula que hicimos para los objetivos de ingresos. Vaya a la celda I4, T1, y escriba igual f, abra paréntesis, que 41 está en blanco, luego en blanco. De lo contrario vamos a querer tirar en los objetivos de gastos. Entonces ingrese índice, abra paréntesis para la matriz. Vaya a la hoja de trabajo de objetivos y seleccione IL-1 a N32. Golpea F4 para una referencia absoluta. Para el num de fila en Partido. Abrir paréntesis. Para el Valor de búsqueda, seleccione la edad 41 en el tablero principal. El array de búsqueda es IL-1 a cualquiera 32 en la hoja de trabajo de objetivos. Vuelve a golpear F4 para una referencia absoluta y un 0 para el tipo de partido. Para la columna NUM. Entrar partido, abrir paréntesis. Para el Valor de búsqueda, seleccione D8 en el tablero principal. El array de búsqueda es 1111 en la hoja de trabajo de objetivos. Y entra ceros para el tipo de partido. Arrastre esta fórmula hacia abajo. Por el gasto real. Simplemente podemos copiar la fórmula de la tabla de ingresos así. Y arrastra eso hacia abajo. Y la diferencia se puede copiar y arrastrar a Dan también. Pasemos ahora a las gráficas. Empezará en la parte superior derecha con uno simple. Haga clic con el botón derecho en la casilla, haga clic en Seleccionar datos ,
luego seleccione hey, H6 a j nueve, y el gráfico se actualizará. Y haga clic en Aceptar. A continuación, crearemos la gráfica para el flujo de ingresos. Y te mostraré cómo hacer dinámicos estos gráficos para que ignore cualquier celda en blanco. Entonces primero mostraré el problema. Si no hacemos que el gráfico sea dinámico, haga clic con el botón derecho en el recuadro encima de la tabla de ingresos y haga clic en Seleccionar datos estaba sosteniendo Control. Seleccione las celdas C4, T1 a E6
y, a continuación, seleccione C 39, t 39. Y mostró las entradas de la leyenda estado Target y real. Y el producto estatal eje horizontal, ventas, marketing de afiliados. Si se invierten, basta con pulsar el botón de fila de interruptores y columnas. Entonces HIT bien. Notarás que la gráfica no es tan útil por todos los espacios en blanco. Y no quieres tener que actualizar los rangos cada vez que añadas categorías. Entonces te mostraré cómo hacerlo dinámico. Necesitamos usar la función de desplazamiento para definir la serie junto a rangos nombrados. Entonces permítanme mostrarles la fórmula para los valores objetivo de ingreso. Escoge cualquier celda libre. Entonces vamos a escoger D6, D2 y escribir igual offset paréntesis abierto. Para la referencia, seleccione D 41 y golpee F4 para una referencia absoluta. Para las filas en 00 para las columnas, para la altura en cuenta a. Y seleccione por defecto t1, t2, d 60, y pulse F4 para una referencia absoluta. Y cerrar paréntesis. A continuación, menos contar en blanco, abrir paréntesis, y seleccionar el 41 a D6 de nuevo y golpear F4 y cerrar paréntesis. Por lo que estamos contando el número de celdas que tienen datos en ella, incluyendo fórmulas menos el número de espacios en blanco, que resultan en el número de celdas no en blanco. Para el ancho N21. Entonces por fin cerrar paréntesis. Y como se puede ver, sólo se muestran los valores no en blanco para los objetivos de ingreso. Y esta es la fórmula que usaremos para el objetivo de tinta llamado rango. Hagamos rápidamente la fórmula para el rango nombrado real de ingresos. Copia la fórmula que acabamos de hacer para los objetivos de ingresos y cambia las referencias de D a E psi. Ahora que tienes las fórmulas, definamos un rangos con nombre. Copia la fórmula objetivo de ingresos, ve a fórmulas, y luego nombra administrador. Seleccione nuevo para el nombre tipo tinta. El objetivo cuatro se refiere a pegar la fórmula. Calor. Está bien. Ahora copia la fórmula real de ingresos que hicimos y crea el rango de nombres reales de tinta, como digamos, se crean los rangos de nombres, podemos actualizar nuestras gráficas. Haga clic derecho y vaya a seleccionar datos, seleccione la serie de destino y haga clic en editar. Y tenemos que hacer es reemplazar las referencias de celda al final con blanco de tinta justo después del signo de exclamación. lo mismo para la serie real y haga clic en Editar, reemplazó las referencias de celda al final con tinta real justo después del signo de exclamación. Una vez que golpeas bien, la carga ahora se actualiza. Tendremos que hacer lo mismo para el objetivo de gastos y el gasto real. Copiar la fórmula real de tinta y cambiar la referencia de E a Me gusta así calcular un rango dinámico para el objetivo de gastos y repetir para el gasto real y cambiar la referencia de i a j. A continuación, crearemos los rangos de nombres tanto para el objetivo de gastos como para el gasto real. Copiar la fórmula para el objetivo de gastos. A continuación, vaya a Administrador de nombres, seleccione nuevo tipo exp target y pegue la fórmula. Y hacer lo mismo para real como psi. Lo vamos a nombrar. Y por último, vamos a hacer las gráficas. En primer lugar, haga clic derecho en el cuadro y seleccione los datos. Seleccionemos algunos datos para que
se complete algo de información y que sea mucho más fácil mientras mantenemos Control. Seleccione las celdas H 41 a j 60, y luego seleccione H 39 a J 39 aseguró el estado de entrada de leyenda Objetivo y real y el eje horizontal indique los tipos de gastos. Si se invierten. Basta con pulsar el botón de fila de interruptores y columnas. Seleccione la serie objetivo y haga clic en Editar. Y todo lo que necesitas hacer es reemplazar las referencias de celda al final
con el objetivo XP justo después del signo de exclamación. Haga lo mismo para la serie real y haga clic en Editar. Sustituir las referencias de celda al final por x Actual justo después del signo de exclamación. Y ahora tanto tus gráficos de ingresos como de gastos son dinámicos. Por lo que tengo menos la sección de productos hasta la última ya que necesitamos hacer una actualización a nuestras fórmulas sobre el producto C. Para que las actualizaciones en base al periodo de tiempo establecido. Acude a la hoja de trabajo del producto y necesitaremos actualizar las fórmulas para la compra total y total vendido. Vamos primero a la celda D5 en la columna de compra total y sumamos condiciones para el periodo de tiempo. Para el tercer rango de criterios. Acude a las transacciones que estamos en realidad y selecciona la columna B y luego pulsa F4 para una referencia absoluta. El tercer criterio, escriba comillas mayores que iguales a comillas. Y luego añadir el símbolo n. Y luego hacer referencia a la fecha de inicio, que es la fecha de inicio. Para el cuarto rango de criterios, seleccione nuevamente la columna B y luego presione F4 para obtener una referencia absoluta. Y para el cuarto criterio, escriba comillas, menos que iguales, comillas. Y luego el símbolo n. Y luego hacer referencia a la fecha de fin, que se llama fecha. Y el final. Esto entonces sólo algunas de las transacciones dentro
del periodo de tiempo seleccionado en la página principal del tablero. Arrastremos esa fórmula hacia abajo. Ahora para acelerar, actualizando el total vendido, podemos copiar esta parte de la fórmula y pegar la fórmula al final, así. Y luego arrastra hacia abajo. Está bien, genial. Ahora que se actualiza el calor de los productos, ya
podemos completar los datos del producto en el panel principal. Vaya a la hoja de trabajo del panel principal y seleccione celda y 41. Y aquí queremos sacar a través de los productos más vendidos. Podemos utilizar las funciones de índice y coincidencia para ello. Por lo que el tipo es igual a índice, abrir paréntesis para la matriz ir a la hoja de producto y dormía columna B, que es la columna del nombre del producto, y golpear F4 para una referencia absoluta. Por lo tanto, el número de fila, vamos a buscar uno en el producto, por lo que clasifica columna usando la función de coincidencia. Por lo que entra partido, abre paréntesis para el Valor de búsqueda, selecciona m para T1 en el tablero principal. También pondrá una referencia mixta donde queremos buscar las columnas para que sea fácil de copiar presionando F4 tres veces. Para el array de búsqueda,
vaya a la hoja de producto y seleccione la columna G y presione F4 una vez para obtener una referencia absoluta. Para el cotejo escriba en 0 y luego cierre paréntesis. Y para la columna NUM, puede ingresar 0 y cerrar paréntesis. Y luego puedes arrastrar esta fórmula hacia abajo. Notarás que obtenemos NAs ya que tenemos menos de diez productos. Por lo que podemos solucionar esto mediante el uso de una combinación de la sentencia if y es la función de error. Vuelve a la n 41 y ve al inicio de la fórmula. Y tipo f, paréntesis abierto es error, paréntesis abierto. Y luego ir al final y poner un paréntesis cercano como argumento lógico para el valor si es verdadero, introducir comillas como say y four, el valor es falso, copiar la fórmula del índice y pegarlo aquí. Lo que esto significa es si la fórmula del índice genera un error porque el valor de búsqueda no existe, entonces mostrar un espacio en blanco, lo contrario, mostrar el resultado del índice. Menos. Arrastra eso hacia abajo. Se puede ver desaparecer a los NAs. Ahora, para la columna total comprada, podemos copiar la fórmula para los nombres de los productos. Y solo necesitamos cambiar la matriz de la columna B y la hoja de producto a la columna D. Recuerda cambiarla dos veces. Cambiamos la matriz a la columna D ya que esta era la columna total comprada en la hoja de producto. A continuación, arrastre hacia abajo. Y luego simplemente rápidamente la línea central. Para la columna total de suelo. Podemos copiar la fórmula y cambiar la matriz a la columna E. Y luego arrastrar hacia abajo. Para el inventario realizado. Podemos copiar la fórmula y cambiar la matriz a la columna F, luego arrastrar hacia abajo. Y por último, actualizaremos el gráfico para mostrar los principales productos
vendidos los hará dinámicos de la misma manera que lo hicimos para el pasto de ingresos y gastos. Para agilizar esto, copiaremos las fórmulas offset que hicimos anteriormente. Y vamos a cambiar los rangos como psi para hacer referencia a los productos vendidos datos en la columna P. Después
copiaremos la fórmula, goto fórmulas, luego nombra manager, y agregaremos un nuevo rango de nombres para el nombre, lo
llamaremos producto vendido, y copiar la fórmula en el recuadro refiere a. Después cierra los libros de Name Manager. Entonces crearemos las gráficas. Entonces haga clic con el botón derecho en el cuadro, haga clic en Seleccionar Primero seleccionemos algunos datos, autocontrol. Seleccione n 41 a 50
y, a continuación, seleccione P 4150. Asegúrese de que los nombres de los productos estén en la caja horizontal del lado derecho. Para la serie, haga clic en editar para el nombre de la serie, a la derecha. Productos vendidos. Para los valores de serie. Eliminar las referencias celulares y reemplazar con producto vendido, que es el rango de nombres. Justo después del signo de exclamación. Simplemente eliminaremos todas esas fórmulas offset que creamos ya que ya no son necesarias. Y es decir, ya has construido tu pista de negocios. Una hoja de cálculo, los datos son totalmente dinámicos. Se pueden cambiar los periodos de tiempo y se puede cambiar la fecha de inicio y se actualizarán las gráficas y los datos. Como decir. De verdad espero que te haya parecido útil este curso. Y las habilidades aprendidas aquí te permitirán crear todo tipo de hojas de cálculo diferentes. Y así diversos problemas dentro de Excel. Si tienes alguna pregunta que quieras que haga, cualquier otro curso, por favor avísame.
Learn To Excel - Josh, Excel Trainer, CFA, Analyst
