Kitabı oku: «Aprender Excel financiero y para MBA», sayfa 2
005 Referencias circulares y cálculo manual
LAS REFERENCIAS CIRCULARES SE DAN cuando una fórmula utiliza la celda que la contiene como uno de sus parámetros, ya sea de forma directa o indirecta. Normalmente, una referencia circular produce un error en Excel; sin embargo, es posible establecer que las referencias circulares sean permitidas.
IMPORTANTE
Si inserta, antes de permitir las iteraciones, una referencia circular indirecta, unas flechas azules indicarán cuáles son las celdas en conflicto.
1. En la hoja Ventas mensuales del libro Ejemplo1, seleccione la celda B28, introduzca la fórmula =Descuento+40% 
y confirme la entrada pulsando sobre el botón Introducir.
2. Un mensaje de advertencia le indica que está utilizando una referencia circular, es decir, una fórmula que toma su contenido como parte del cálculo. Pulse el botón Aceptar. 
En la ficha Fórmulas del cuadro de Opciones de Excel se encuentran las opciones de cálculo de la aplicación.
3. Aparece el término Referencias circulares seguido del nombre de la celda que la contiene en la Barra de estado. 
El resultado de la celda es 0 porque Excel realmente no es capaz de utilizar referencias circulares, si no activa la opción apropiada. Haga clic en la pestaña Archivo, pulse sobre el comando Opciones y, en el cuadro de diálogo, seleccione la categoría Fórmulas. 
4. Marque la casilla Habilitar cálculo iterativo del apartado Opciones de cálculo, cambie las Iteraciones máximas a 1 y pulse el botón Aceptar. 
5. Al permitir las iteraciones, el resultado de B28 cambia a 40% de nuevo, pues a su último valor, que era 0, se le ha sumado 5. Seleccione la celda C4, cambie su valor por 8.000 y pulse Retorno. 
6. Al efectuarse el cambio en la celda C4, cambia el resultado de B28 a 80% 
y, por lo tanto, cambia el valor de B24 ya que éste es calculado a partir de aquél. Aunque el valor de B28 no está relacionado con valor de C4, sucede que al realizar una operación en una celda Excel siempre recalcula todas las celdas del libro al que pertenece. Por lo tanto, al valor anterior de la celda Descuento (40% al momento del cálculo) se le ha sumado, según indica la fórmula, un 40% más. Como verá, si trabaja con referencias, circulares puede obtener resultados imprevistos al modificar el contenido de cualquier celda.
Cada vez que se recalcula la hoja, vuelve a calcularse la fórmula de la celda con referencia circular a partir del valor actual de la misma.
7. En el grupo Calculo de la ficha Fórmulas de la Cinta de Opciones, puse el botón Opciones para el cálculo y seleccione la opción Manual. 
8. Ahora Excel no realizará ningún cálculo hasta que usted le indique que debe hacerlo. Para ello deberá pulsar la tecla F9. Hágalo y compruebe cómo el contenido de la celda Descuento cambia a 120%. 
Al activar el cálculo manual, la aplicación sólo recalcula las fórmulas del libro cuando el usuario pulsa la tecla F9 o pulsa los botones Calcular ahora o Calcular hoja ahora, ubicados a la derecha del botón Opciones para el cálculo de la Cinta de opciones.
9. Cambie el valor de B28 por 40% nuevamente.
10. Abra el cuadro Opciones de Excel, reactive el cálculo Auto-mático, deshabilite el cálculo iterativo y acepte el cuadro.
11. Para terminar el ejercicio, guarde los cambios realizados.
IMPORTANTE
El grupo Cálculo de la ficha Fórmulas de la Cinta de opciones contiene, además del botón Opciones para el cálculo, que le permite establecer si este debe ser Manual, Automático o Automático excepto en las tablas de datos, dos botones que le permiten calcular toda la hoja o calcular todo el libro cuando la opción seleccionada es Manual.
006 El relleno automático y el relleno flash
EXCEL 2013 RELLENA AUTOMÁTICAMENTE celdas con series de datos (días, meses, números, etc.). La nueva función de relleno rápido, por otra parte, reconoce, si lo hubiera, un patrón en la hoja y lo utiliza para rellenar series.
1. En la esquina inferior derecha de la celda seleccionada aparece un pequeño cuadro negro, el controlador de relleno. Al situar el puntero del ratón sobre ese cuadro, éste cambia a una cruz negra. Seleccione la celda B3, haga clic en su controlador de relleno y, sin soltar el botón, arrastre hasta la celda E3.
2. Ahora todas las celdas tienen el mismo formato y se añade el mes de abril. Además, aparece la etiqueta inteligente Opciones de autorrelleno. Pulse sobre ella.
3. Puede copiar las celdas, rellenarlas con una serie (aplicada por defecto), rellenarlas con formato o sin formato, o rellenarlas con los meses. Escoja Copiar celdas y vea el resultado.
Las opciones de esta etiqueta varían en función del tipo de serie creada.
4. Ahora la nueva celda muestra el contenido de la primera, enero. Si continuara arrastrando el mismo controlador de auto-relleno, se repetirían los mismos tres meses con los que partimos. Seleccione la celda E3, pulse el botón Borrar del grupo Modificar de la ficha Inicio y escoja la opción Borrar todo.
5. Active la hoja Clientes VIP, seleccione la columna A y añada una nueva columna desde la ficha Inicio, grupo Celdas, comando Insertar, opción Insertar celdas.
6. Introduzca un número 1 en la celda A4 y arrastre su controlador de relleno hasta la celda A14.
7. Al partir de sólo un número, Excel lo repite en todas las celdas. Pulse en la etiqueta inteligente Opciones de autorrelleno.
Si arrastra mientras mantiene la tecla Control pulsada, la serie numérica se rellena automáticamente con números consecutivos.
8. En este caso, el programa nos permite copiar las celdas, rellenar con una serie de relleno, rellenar sólo con los formatos de celda o rellenar sin formato. Pulse en la opción Serie de relleno y vea cómo se autorrellenan las celdas con una serie.
9. Seleccione el rango E4:E14, introduzca la palabra Barcelona y pulse Ctrl.+Retorno.
10. Las celdas seleccionadas se rellenan automáticamente con esta palabra. Sitúese en la celda B2, escriba el nombre del primer elemento de la lista, María, y pulse la tecla Retorno.
11. Una vez en la celda inferior, empiece a escribir el nombre del segundo elemento y vea cómo, tan pronto introduce la inicial, Excel muestra una lista de las opciones que considera adecuadas usando como patrón los datos de la primera columna.
12. Es gracias a la herramienta de relleno flash. Pulse la tecla Retorno para confirmar la entrada de ese segundo elemento.
13. La columna B queda rellenada con los nombres de pila correspondientes y aparece la etiqueta Opciones de relleno de Flash. Pulse sobre ella.
14. Puede deshacer el relleno rápido, aceptar las sugerencias y seleccionar las celdas en blanco, si las hubiera, y las celdas modificadas. Elija la opción Aceptar sugerencias y repita el proceso para rellenar la columna de apellidos. 
IMPORTANTE
La nueva herramienta de relleno rápido funciona únicamente cuando el programa reconoce un patrón de datos con alguna coherencia, pero estos datos no tienen que ser siempre nombres. Además, el relleno rápido distingue entre mayúsculas y minúsculas, por lo que puede emplearse, por ejemplo, para convertir fácilmente una lista de términos en minúsculas en una lista en mayúsculas. También es capaz de crear una lista de iniciales a partir de nombres propios.
007 Convertir datos en tabla
LAS TABLAS DAN UN ASPECTO PROFESIONAL a sus datos y permiten el uso de poderosas herramientas de administración de información como los filtros automáticos de las cabeceras de columnas.
IMPORTANTE
También puede convertir un rango en Tabla usando el botón Dar formato como tabla, del grupo Estilos de la ficha Inicio que, además, le permite escoger al momento el diseño que desea aplicar. Por otra parte, puede convertir la tabla de nuevo en rango usando la opción Convertir en rango del grupo Herramientas de la subficha contextual Diseño, de Herramientas de tabla.
1. Descargue de nuestra web el archivo InventarioMarcombo. xlsx, ábralo y compruebe, desplazándose por él, la gran cantidad de datos que contiene.
2. Se trata de casi 800 referencias de libros de nuestra editorial. Vamos a crear una tabla con estos datos. Seleccione A1 y pulse Ctrl.+Mayúsculas+desplazamiento hacia la derecha para seleccionar todas las columnas con datos y luego Ctrl.+Mayúsculas+desplazamiento hacia la abajo para seleccionar todas las filas
3. Pulse el botón Tabla del grupo Tablas, en la ficha Insertar.
El comando Tabla se encuentra en el grupo Tablas de la ficha Insertar y le dirige al cuadro de diálogo Crear Tabla.
4. Como hizo la selección de los datos previamente, el rango que muestra la aplicación en el campo ¿Dónde están los datos de la tabla? es correcto. Mantenga activa la opción Tiene encabezados y pulse en botón Aceptar.
Si selecciona previamente los datos con los cuales desea crear una tabla, al abrirse el cuadro Crear tablas encontrará su referencia en el lugar adecuado.
5. El rango se convierte en una tabla con un estilo predeterminado y se activa la subficha contextual Diseño, de Herramientas de tabla. Pulse en su botón Estilos rápidos, el último.
6. Se despliega una galería de muestras. Seleccione la que prefiera para cambiar el aspecto de la tabla.
7. Haga clic en cualquier celda para ver el efecto obtenido. Las puntas de flecha que están en la esquina derecha de cada cabecera de columna despliegan los filtros de la tabla. Pulse el que corresponde a la columna Título y seleccione la opción Ordenar de A a Z.
8. La columna se organiza alfabéticamente y el resto de la tabla se reorganiza en función de este orden. Despliegue el cuadro de filtros de la columna Id. marca.
9. Pulse en la casilla de selección de la opción Seleccionar todo para desactivarla, seleccione la opción Alfaomega y pulse Aceptar.
10. Compruebe cómo ahora sólo se ven las filas de títulos publicados por esta editorial. Muestre ahora las opciones de filtro de la columna Páginas.
11. Seleccione ahora la opción Filtros de número.
12. Puede establecer una gran cantidad de filtros a partir de un valor establecido. Seleccione la opción Entre.
13. Como verá, con esta opción debe establecer un límite superior y uno inferior para el rango mostrado. Mantenga seleccionada la opción es mayor o igual a e introduzca el valor 100 y, en el campo siguiente, es menor o igual a, introduzca el valor 200 y pulse Aceptar.
14. Sólo se muestran los libros que tienen entre 100 y 200 páginas, ambas inclusive, que han sido publicadas por Editorial Alfaomega. Para eliminar el filtro de páginas, despliegue de nuevo sus opciones y pulse sobre Borrar filtro de “Páginas”.
IMPORTANTE
Puede convertir un conjunto de datos en una tabla de excelente presentación visual con filtros incorporados con sólo pulsar en uno de los estilos del menú Dar formato como tabla.
008 Usar la función subtotales
LA FUNCIÓN SUBTOTALES SE CREA al utilizar la función Auto-suma sobre una tabla. Lo interesante es que esta función permite al usuario aplicar en una misma celda cualquier función de auto-suma a partir de un cómodo menú.
1. En este ejercicio continuaremos trabajando con el libro InventarioMarcombo. Haga clic en la cabecera PC sin IVA y luego pulse la combinación de teclas Ctrl.+Desplazamiento hacia abajo.
2. Una vez se encuentre al final de la columna, seleccione la celda H795, que es la inmediatamente inferior. En la ficha Fórmulas de la Cinta, pulse en el botón Autosuma del grupo Biblioteca de fórmulas.
El argumento 109 en la función SUBTOTALES indica que para el cálculo serán consideradas sólo las celdas visibles (10) y que se efectuará una suma (9).
3. Se inserta en la celda indicada la sumatoria de todos los PVP. de la columna, pero observe la fórmula: se trata de la función SUBTOTALES. En ella, el primer argumento establece el código de la función aplicada, que en este caso es SUMA y, como viene precedido por el número 10, indica que se ignoran las celdas ocultas. El siguiente dato simplemente indica el nombre de la columna analizada. Pulse el botón Introducir.
4. Se muestra el resultado de sumar todos los datos visibles. Despliegue el menú de filtros de la columna Editorial, seleccione la editorial 2001 Editors Choice sin desactivar Alfao- mega y pulse en botón Aceptar.
Tenga en cuenta que también puede combinar filtros de distintas columnas.
5. Se añaden las referencias con la identidad indicada y la suma se actualiza automáticamente. Sin embargo, el dato que nos proporciona la celda H795 es bastante inútil. Vamos a cambiarlo por otro que nos arroje una información más valiosa. ¿Se ha fijado en el botón de punta de flecha de esta celda? Pulse sobre él.
6. Puede en estos momentos cambiar la fórmula aplicada por cualquiera de las que se indican en este menú, o por cualquier otra si pulsa en la opción Más funciones, que le llevaría al cuadro Insertar funciones. Escoja en este caso la función Promedio.
7. La celda nos muestra ahora, efectivamente, el promedio de todos los PV. Con la celda aún seleccionada, coloque el cursor en su esquina inferior derecha y arrastre el ratón hacia la izquierda, para copiar su contenido en la celda G153.
8. La función se pega en la celda indicada y se adapta correctamente para mostrarnos el promedio de la columna I, PVP. Aplique el formato de moneda de dos decimales a todos los datos de las columnas H e I.
La etiqueta inteligente que aparece a la derecha de la celda al añadir una nueva columna a una tabla, se llama Opciones de autocorrección y le permite eliminar el autoformato establecido.
9. Cambie la función de ambos subtotales por Máx.
10. Ahora ya sabe cuál es el coste máximo de las referencias visibles. Guarde los cambios para terminar y pasar al próximo ejercicio.
009 Crear esquema por subtotales de la tabla
EL COMANDO SUBTOTAL, INCLUIDO EN EL grupo de herramientas Esquema de la ficha Datos de Excel 2013, puede calcular subtotales de forma automática en celdas concretas de las tabla, siempre que haya algún dato común y que éste se encuentre en la primera fila de datos.
1. Seleccione la columna D, Editorial, active la ficha Datos y pulse sobre el botón Borrar del grupo Ordenar y filtrar para eliminar el filtro aplicado.
2. Con la columna aún seleccionada, coloque el puntero en borde del ratón en su cabecera. Cuando se transforme en una cruz con puntas en flecha, pulse sobre el mismo y, manteniendo el botón pulsado, presione también la tecla Mayúsculas sin soltarla y arrastre la columna hacia la izquierda, hasta situarla sobre la columna A.
3. De este modo la columna movida se inserta a la derecha de la columna en la que ha sido soltada. Seleccione la cabecera y pulse sobre el botón Ordenar de la A a la Z, también en el grupo Ordenar y filtrar.
4. Seleccione la celda A1 y pulse a continuación sobre el botón Subtotal del grupo Esquema, también en la ficha Datos.
5. En el cuadro Subtotales se indica que se calcularán los subtotales para cada cambio en la columna Editorial (es decir, para cada nuevo nombre de la empresa). Despliegue el campo Usar función y escoja la opción Promedio.
6. Calcularemos los promedios de las columnas PV sin IVA y PVP. Seleccione estas opciones en el siguiente campo, active las opciones Reemplazar subtotales actuales y Resumen debajo de los datos y pulse el botón Aceptar.
Las vistas esquemas le permiten presentar grandes cantidades de datos de forma más cómoda y manejable.
7. Se muestran del lazo izquierdo de la tabla los símbolos del esquema. Para visualizar mejor lo que ha sucedido, pulse sobre el número 2 que está en la cabecera, de modo que se muestren sólo los datos que están en este nivel.
8. Bajo los datos de cada editorial se ha insertado una fila con la función Subtotal, que nos muestra el promedio de cada editorial. Además, al final de la tabla se ha insertado otra fila que calcula el promedio general de los precios de todos los títulos. Pulse sobre el símbolo Mas (+) que se encuentra a la derecha de la fila 8, que contiene el promedio de 2001 Editors Choice.
9. De este modo puede expandir cualquiera de los grupos creados para el esquema y mostrar los detalles del mismo. Seleccione ahora las columnas E, F y G desde sus cabeceras y despliegue el comando Agrupar.
10. Pulse sobre la opción Agrupar de este comando.
11. También podría haber pulsado directamente sobre el icono del comando Agrupar. Como podrá ver, se ha creado un nuevo grupo que une las tres columnas seleccionadas. Contráigalo haciendo clic en el símbolo Menos (-) del grupo.
12. Guarde los cambios para terminar.
IMPORTANTE
La opción Autoesquema crea grupos automáticamente en conjunto de datos siempre que éstos sean analizados por funciones que proporcionen totales parciales o subtotales. Además, deberá haber una primera fila o columna que proporcione los rótulos (por ejemplo, la columna Editorial en este caso), y no puede haber filas en blanco.
010 Crear un esquema de forma automática
EL GRUPO DE HERRAMIENTAS ESQUEMA DE la ficha Datos de Excel 2013 incluye la opción Autoesquema, dentro del comando Agrupar, que permite crear complejos esquemas de forma auto-mática, siempre que los datos estén presentados de la forma correcta. Los datos deben estar agrupados por fórmulas o funciones que calculen totales parciales, no puede haber filas o columnas en blanco en medio de los datos a agrupar, y las celdas que actúan como rótulos de grupo deberán estar en alguno de los extremos de los datos, nunca en medio de éstos.
1. Antes de comenzar este ejercicio descargue de nuestra web el archivo Ejemplo2 y guárdelo en su equipo.
2. Vamos a crear un esquema que organizará toda la información de la hoja de cálculo con un solo clic, pero antes vamos a analizar sus contenidos. En primer lugar, puede ver que los datos están divididos en Ingresos y Gastos. Al pie de cada categoría se presenta el total de cada una. Seleccione la celda B7.
3. En ella se suman los valores de la misma columna para las filas 5 y 6, y así en todas las columnas. Del mismo modo, las celdas de la fila 19 suman los valores de cada columna para las filas 10 a 18.
4. Luego son sumados los totales trimestrales para cada concepto en las filas E, I, M y Q, de nuevo con una sencilla función SUMAR. Compruébelo, por ejemplo, en la celda E5.
5. Una vez analizados los datos, y sin importar cuál es la celda seleccionada, despliegue el comando Agrupar del grupo Esquema, que se encuentra en la ficha Datos, y seleccione la opción Autoesquema.
En este ejemplo, la presencia de la función SUMA en diversas celdas de la hoja es fundamental para el correcto funcionamiento de la función Autoesquema.
6. Utilizando como referencia las celdas con la función SUMA, se ha creado de forma automática un conjunto de grupos, tanto para las filas como para las columnas de los datos. Pulse sobre el símbolo 1 que se encuentra en la parte superior del conjunto de datos.
7. De este modo puede ver los totales por trimestre para cada concepto. Tal como vimos en el ejercicio anterior, puede expandir el campo que quiera con sólo pulsar sobre el signo Más (+) del trimestre que prefiera. Pruébelo.
Si los datos no son apropiados para la función Autoesquema, al intentar usar este comando se abre una advertencia de la aplicación que informa que no se puede crear un esquema. En ese caso deberá crearlo de forma manual.
8. Haga clic ahora en el símbolo 1 de los controles laterales del esquema.
9. Se ocultan los detallas de Ingresos y Gastos. Seleccione las celdas A5:A7, despliegue a continuación el comando Desagrupar y seleccione la opción Desagrupar.
10. Se abre el cuadro del mismo nombre. Mantenga seleccionada la opción Filas y pulse el botón Aceptar.
11. Se desagrupa el conjunto de filas que correspondía a los datos seleccionados. Pulse sobre cualquier celda de datos, despliegue una vez más el comando Desagrupar y seleccione esta vez el comando Borrar esquema.
12. Así de fácil es borrar un esquema de una hoja de cálculo. Guarde los cambios para terminar.
