Excel - Herramientas: Solver - Tablas - Ordenar y Filtrar Datos - Formatos de Texto - Macros.

0


Microsoft Excel 

HERRAMIENTAS: Solver - Tablas - Ordenar y Filtrar Datos - Formatos de Texto - Macros.


En esta sección de Excel nos enfocaremos en las funcionalidades de las herramientas mas relevantes que excel cuenta. Asì también recomiendo la aplicación de los ejemplos prácticos para una mejor comprensión de estas herramientas.

1.-ANÁLISIS DE DATOS.

1.1.-Solver.

Es una herramienta de Análisis de Datos que nos permite dar como resultado un valor optimo máximo o mínimo evaluando una serie de datos y restricciones. Solver adecua el valor objetivo que esta relacionada por formulas a restricciones de manera que cumplan con los parámetros. Si cuentas con Microsoft Excel con versiones superiores a 2010 puedes activar esta herramienta siguiendo los siguientes pasos.
  • Seleccionamos en la pestaña Archivo.
  • Sección Opciones.
  • Complementos.
  • Complementos de Excel.
  • En el formulario, buscaremos y activaremos la Casilla Solver.
La herramienta solver tiene que ser programada de tal manera que las celdas se relacionen por formulas o restricciones. Para poder entender mejor esta herramienta vamos a conocer sus parámetros.
  • Establecer Objetivo: El valor objetivo es la variable o indicador que solver adecuara para cumplir con el objetivo y restricciones.
  • Restricciones: Las restricciones son aquellas variables pueden tomar valores que esta comprendidos dentro de los parámetros.
  • Método de Resolución: Solver cuenta con tres métodos de solución entre ellas: 
    • GRG Nonlinear, Este método se usa para problemas no Lineales Suavizados;
    • Simplex LP; Se usa para problemas de modelos lineales.
    • Evolutionary; Se usa para problemas de modelos no suavizados.

Ejercicio de Solver
En una empresa de Metalmecánica se desea vender dos productos: Sillas Metálicas y Puertas Metálicas. Para ello se cuenta con los precios y costo de producción. Ademas las ganancias las podemos determinar restando el precio menos el costo de producción. Así también por motivos de almacenamiento y demanda se cuentan con algunas restricciones para la producción.

Planteamiento del Problema

Solución: 
Podemos establecer que nuestra función Objetivo es: Maximizar las ganancias (Max) y nuestra Variables Objetivos son: las Unidades de Sillas y Puertas Metálicas a fabricar. Para poder ejecutar solver debemos relacionar nuestra variable objetivo y restricciones de la siguiente manera: 





Como resultado obtenemos que las Ganancias Máximas Totales es igual a = S/. 15 000. Para ello se deben fabricar 0 unidades de Sillas Metalicas y 150 unidades de Puertas Metálicas.


2.1.- Formato de Tabla.

Para aplicar un formato a la tabla, seleccionaremos los datos que conformarán la tabla, luego a Inicio>Tablas>Dar Formato como Tablas>Seleccionaremos el Formato de Tabla; Luego aparecerá un formulario emergente solicitando si el rango seleccionado contiene los encabezados de la tabla; de ser así activaremos la casilla.

La importancia de Aplicar formato de Tabla; Si nuestra tabla esta conformado con filas , columnas y encabezados para cada columna, podemos aplicar Formato, esto permitirá identificar e interactuar los valores a excel y aplicar diferentes formatos, cálculos y funcionalidades. Al aplicar formato de Tabla nos permitirá realizar las siguientes funciones:
  • Ordenar y Aplicar Filtros.
  • Aplicar Tablas Dinámicas.
  • Aplicar Fila de Totales.
  • Seleccionar celdas o rangos dinámicos.

2.2.-Ordenar y Filtrar.

Filtros. Para activar los filtros haremos clic dentro de la tabla deseada y luego nos vamos a la pestaña de datos y en la sección de Ordenar y filtrar haremos clic en filtros. Al activar esta opción permite desplegar por columna una serie de criterios de búsqueda como: orden, fecha o caracteres definidos. Los resultados que se muestran solo cumplen con los criterios definidos. 

Ordena Múltiples Columna en función a Criterios.

2.3.-Copiar y Pegar.

A simple vista parecen funciones comunes. Sin embargo podemos copiar el contenido de las celdas seleccionados y pegarlos en: valores, formulas, posiciones y formatos de diferentes maneras. Es importante saber que la función simple de copiar y pegar; tienen por defecto pegar las formulas y valores pero sin el formato. A continuación presentare algunas de las funcionalidades mas detalladas de esta herramienta. Recuerda que también podemos copiar con las teclas rápidas Ctrl+C y pegar con las teclas Ctrl+V.
  • Pegar Formulas: Copiar el contenido de una celda y pegar las fórmulas.
  • Pegar Valores: Copiar el contenido de una celda y pegarlas solo los valores.
  • Pegar Formato: Copiar el contenido de una celda y pegarlas incluyendo su formato.
  • Transponer: Copiar lo valores de las filas seleccionadas y pegar los valores en columnas.

2.4.-Asignar Nombres.

Asigna un nombre a un rango de celdas, una vez que hallamos definido el nombre podemos utilizarlos en las formulas. Esta asignación nos permite llamar a una celda o rango de manera mas dinámica.
  1. Seleccionamos las celdas o el rango para asignarle un nombre.
  2. Nos vamos a Formulas.
  3. Asignar Nombre.
  4. En el formulario emergente escribiremos el nombre.
Ejemplo:
En el siguiente ejemplo mostraremos como asignar un nombre a una celda y llamarlo desde una formula.
Asignar Nombres a Celdas

2.5.-Texto en Columnas.

Esta herramienta nos permitirá separar una cadena de texto en columnas independientes, la herramienta utilizara caracteres como: espacios, guiones, comas, punto y comas, entre otros; para separar las palabras. 

Ejemplo
En el siguiente ejemplo se muestra datos como: Nombres, Apellidos Paterno, Apellido Materno, Celular, Edad en una sola celda. A continuación vamos a separar estos datos por celdas independientes.



3.- GRÁFICOS

3.1.-Minigràficos.

A comparación de los gráficos, los minigraficos son pequeños gráficos ubicados en las celdas; cada minigráfico representa los datos de una fila. Para poder aplicar un minigráfico seleccionaremos la celda ubicada en la misma fila que deseamos representar los datos, luego nos iremos a insertar - minigráficos y seleccionaremos una entre las tres opciones, ya sea lineas, columnas o perdida y ganancias. 

3.2.-Proteger Hoja o Libro

Estas funciones evitan o permite realizar funciones, para proteger la hoja o libro de nuestro documento de excel. Para ello debemos ir.
  1. A la pestaña Revisar - Proteger - Proteger Hoja o Proteger Libro .
  2. En el caso de Proteger Hoja aparecerá un formulario en la que deberemos ingresar la Contraseña y en la parte inferior una lista de funciones que permitiremos realizar en caso de que se encuentren activas.
  3. En el caso de Proteger Libro solo ingresaremos la Contraseña.
Esta herramienta permite o evita realizar cambios como: Seleccionar, aplicar formatos, Insertar, eliminar, Ordenar, Filtro, Tabla dinámica y Modificar para las celdas filas y columnas de un libro. 

4.- PROGRAMADOR

4.1.-Macros

Las macros son grabaciones de procedimientos o comandos, utilizando la interfaces de la herramienta de Microsoft Excel y son almacenados en código Vba.

Es importante tener en cuenta que debemos guardar nuestro archivo con la siguiente extensión: Libro de Excel habilitado para macro (* ) de lo contrario se perdera el codigo Vba. Para guardar nuestro archivo debemos seguir los siguientes pasos:

  1. Nos ubicaremos en la pestaña Archivo y haremos clic en Guardar como
  2. Luego seleccionaremos la carpeta donde guardaremos nuestro archivo de excel.
  3. Aparecerá un formulario y nos ubicaremos en Tipo y desplegaremos en Libro de Excel (*. xlsx) y seleccionaremos la opción Libro de Excel habilitado para macros (*. xlsm) y finalmente en Guardar.

4.2.-Fecha Actual mediante Macro.

En el siguiente ejemplo asignaremos una macro a un botón, la cual nos mostrara la fecha actual en la celda activa. Si no contamos con los conocimientos de programación en Vba descuidemos ya que vamos a utilizar la grabación de macro para facilitarnos en escribir el código luego lo editaremos y le asignaremos al botón. 

4.3.- Grabar una Macro en Vba.

Para grabar una macro seguiremos los siguientes pasos:
  1. Nos ubicaremos en la pestaña Programador - Código - Grabar Macro.
  2. Aparecerá un formulario y colocaremos como nombre de la macro MacroFechaHoraActual y aceptar
  3. Seleccionaremos una celda e ingresaremos la siguiente formula: =hoy() y enter.
  4. Después seleccionaremos nuevamente la celda previa y copiaremos la celda con Ctrl+C.
  5. Luego nos ubicaremos en Inicio y desplegaremos en Pegar - Pegar Valores.
  6. Ya para finalizar detendremos la Grabación , para ello nos iremos a la pestaña de Programador - Código - Detener Grabación.

4.4.- Asignar una Macro a un botón.

Para asignar una macro a un boton seguiremos los siguientes pasos:
  1. Nos ubicaremos en la pestaña Programador - Insertar - Controles de formulario y daremos clic en botón (Control de Formulario)
  2. Después haremos clic y manteniendo presionado lo arrastraremos para definir las dimensiones del botón, después de definir el tamaño soltaremos para finalizar.
  3. Al soltar aparecerá un formulario en donde seleccionaremos la macro MacroFechaHoraActual en la lista de Macros y clic en Aceptar.
  4. Para ejecutar la macro seleccionaremos en una celda y daremos clic sobre el botón.

4.5.- Edición de Macro.

Para editar nuestra Macro seleccionaremos la pestaña Programador - Código - Macros seleccionaremos la macro que deseemos editar y haremos clic en Modificar. nos mostrara el siguiente código Vba.

Sub MacroFechaHoraActual()' MacroFechaHoraActual Macro
'La celda Activa
 ActiveCell.FormulaR1C1 = "=TODAY()"
'Eliminaremos esta selección ya que solo necesitamos la celda activa.
 Range("J9").Select
'Copiaremos el valor de la celda Activa
 Selection.Copy
'Se pegara solo el valor de la fecha actual onitiendo la formula
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
End Sub

Ademas agregaremos el siguiente comando Application.ScreenUpdating = False desactivando nuestra imagen evitando que nuestra macro parpadee al ejecutarse y al finalizar la activaremos Application.ScreenUpdating = True. Finalmente de editar nuestra macro podemos concluir con el siguiente código:
Sub MacroFechaHoraActual()
' MacroFechaHoraActual Macro
'Desactivaremos la imagen de nuestro excel
 Application.ScreenUpdating = False
'La celda Activa
 ActiveCell.FormulaR1C1 = "=TODAY()"
'Copiaremos el valor de la celda Activa
 Selection.Copy
'Se pegara solo el valor de la fecha actual omitiendo la formula
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
'Activaremos la imagen de nuestro excel
 Application.ScreenUpdating = True
End Sub










Entradas que pueden interesarte










Entradas más recientes


Entradas antiguas









Sin comentarios