martes, 31 de julio de 2018
MICROSOFT AVANZADO Y EXPERTO
MICROSOFT EXCEL AVANZADO Y EXPERTO
Módulo 1: Macros
- Introducción al Curso de Excel Avanzado
- ¿Qué son las Macros? ¿Para qué sirven?
- Creando tu primera macro en Excel
- Eliminar, modificar e insertar datos con Macros
- Filtros Avanzados con Macros
- Gráficos Dinámicos con Macros
- Creación de Formularios con Macros y VBA
- Bonus: Cómo crear un atajo para el botón de Macros
Módulo 2: Qué es Power Pivot
- Introducción a PowerPivot – ¿Qué es?
- Conociendo la interfaz y menús de PowerPivot
- Crear tablas relacionadas con PowerPivot
- Importar bases de datos desde Access con PowerPivot
- Importar datos desde archivos .CSV
- Gráficos Dinámicos con PowerPivot
- Segmentación de datos con PowerPivot
- Ordenar, filtrar y tipos de datos en Power Pivot
- Máximo, mínimo, autosuma y promedio con PowerPivot
- Función SI (Y-O) con varias condiciones en Power Pivot
- Cómo crear un KPI con PowerPivot
Módulo 3: Qué es Power Query
- Curso de Power Query: ¿Qué es Power Query?
- Importar datos desde archivos TXT, CSV, XML, Access y Excel con Power Query
- Importar datos desde un sitio web
- Dividir, reemplazar y cambiar el formato de columnas
- Administrar, combinar y anexar datos con Power Query
- Agrupar por, transponer e invertir con Power Query
- Extraer datos de Columna fecha y hora con Power Query
- Columna condicional y personalizada con Power Query
- Vista previa de datos con Power Query
sábado, 28 de julio de 2018
viernes, 20 de julio de 2018
domingo, 1 de julio de 2018
VBA Macros Práctica 2
Sub ventas()
' saludo Macro ' Mi primera macro
' Autor: Patricia Acosta
' Acceso directo: CTRL+s
Range("A1").Select
ActiveCell.FormulaR1C1 = "Mes"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Producto"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Ventas"
Range("A2").Select
ActiveCell.FormulaR1C1 = "enero"
Range("B2").Select
ActiveCell.FormulaR1C1 = "Zapatos"
Range("C2").Select
ActiveCell.FormulaR1C1 = "20000"
Range("A3").Select
ActiveCell.FormulaR1C1 = "febrero"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Tenis"
Range("C3").Select
ActiveCell.FormulaR1C1 = "25000"
Range("A4").Select
ActiveCell.FormulaR1C1 = "marzo"
Range("B4").Select
ActiveCell.FormulaR1C1 = "Carteras"
Range("C4").Select
ActiveCell.FormulaR1C1 = "30000"
Range("A5").Select
ActiveCell.FormulaR1C1 = "abril"
Range("B5").Select
ActiveCell.FormulaR1C1 = "Cinturones"
Range("C5").Select
ActiveCell.FormulaR1C1 = "32000"
Range("A6").Select
ActiveCell.FormulaR1C1 = "mayo"
Range("B6").Select
ActiveCell.FormulaR1C1 = "Zapatillas"
Range("C6").Select
ActiveCell.FormulaR1C1 = "35000"
Range("A7").Select
ActiveCell.FormulaR1C1 = "junio"
Range("B7").Select
ActiveCell.FormulaR1C1 = "Monederos"
Range("C7").Select
ActiveCell.FormulaR1C1 = "35000"
End Sub
VBA Macros Práctica 3
Sub Colores()
'
' Colores Macro
'
'
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "2"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "2"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "2"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "4"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "3"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "3"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "3"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "4"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "4"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "4"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "4"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "5"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "5"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "6"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "6"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "6"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "7"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "7"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "7"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "8"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "8"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "8"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "9"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "9"
ActiveCell.Offset(1, 0).Range("A1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "9"
ActiveCell.Offset(-27, 0).Range("A1:A4").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 192
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Offset(4, 0).Range("A1:B3").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Offset(3, 0).Range("A1:C3").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Offset(3, 0).Range("A1:D4").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 6299648
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Offset(4, 0).Range("A1:E2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=3
ActiveCell.Offset(2, 0).Range("A1:F3").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
ActiveCell.Offset(3, 0).Range("A1:G3").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.499984740745262
.PatternTintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=6
ActiveCell.Offset(3, 0).Range("A1:H3").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10498160
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Offset(3, 0).Range("A1:I3").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=-18
ActiveCell.Offset(-25, 0).Range("A1").Select
End Sub
Sub borrar()
'
' borrar Macro
'
'
ActiveCell.Range("A1:A28").Select
Selection.ClearContents
ActiveCell.Range("A1:J28").Select
ActiveWindow.SmallScroll Down:=12
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Select
End Sub
Suscribirse a:
Entradas (Atom)