En Julio aprende Excel y macros para Excel.

Por todas aquellas veces que pensaste que necesitabas aprender Excel...

Cursos de Excel y Macros en modalidad elearning, para permitir el acceso a ellos a cualquier persona desde cualquier parte del mundo...
No lo dudes haz de Excel tu mejor aliado!
Aprende con los mejores y adquiere una buena base: Edición de Cursos de Excel y Macros online con tutor personal de Julio de 2015.
Nunca estudiar fue tan fácil.

Los cursos de Excel y Macros abiertos para este mes de Julio son:

Curso Excel Presencial en Madrid nivel Avanzado - 20 horas -

(ver más)

Curso Excel Avanzado

(ver más)

Curso Macros Medio

(ver más)

Curso Macros Iniciación

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Curso Excel Financiero

(ver más)


Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de Julio de 2015; y la matrícula estará abierta hasta el día 10.

Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...

Personalizar el formato del Libro nuevo por defecto

Seguro que mil veces has abierto tus nuevos libros de trabajo y siempre has visto la misma configuración, formato, etc... y alguna vez has deseado que por defecto te aparecieran algunos formatos ya aplicados sobre tus nuevos libros...
La solución es personalizarlo, para lo cual crearemos una Plantilla de Excel con nuestros configuraciones y formatos preferidos.

Lo primero que haremos será abrir un Nuevo Libro y aplicarle todos aquellos formatos que queramos.
Por ejemplo:
1- yo desmarcaré las Líneas de cuadrícula de las celdas
2- aplicaré un formato de número con separador de miles y con dos decimales
3- tipo de fuente Tahoma
4- color de fuente Rojo
5- etc...
También podremos cambiar la configuración desde las Opciones de Excel.

Cuando ya tengamos claro nuestro aspecto final, grabaremos este Libro, asegurándonos que lo guardamos con UNA SOLA hoja!!!, con la extensión .xltx (Plantilla de Excel), como Libro.xltx (es importante tenga este nombre!!)
Y en la ruta:
Windows XP:C:\Documents and Settings\NombreUsuario\Application Data\Microsoft\Excel\XLSTART
Windows Vista: C:\Users\NombreUsuario\AppData\Local\Microsoft\Excel\XLSTART
Windows 7 or 8: C:\Users\NombreUsuario\AppData\Roaming\Microsoft\Excel\XLSTART
No olvides reemplazar NombreUsuario por tu usuario en tu PC

haz clic en la imagen

Un último paso, ahora nos iremos a las Opciones de Excel y deshabilitaremos, desde el menú General > Sección Opciones de Inicio, la opción Mostrar la pantalla de Inicio cuando se inicie esta aplicación:

haz clic en la imagen

Esta acción es importante para que la próxima vez que abramos Excel, directamente tome la nueva configuración... y no nos pregunte qué queremos abrir.

Cerramos la aplicación, y listos... la siguiente vez que abramos Excel se abrirá con la plantilla generada, con nuestra configuración y formato personalizados.

Seguir leyendo...

Limitaciones para los nombres del archivo y de las hojas de Excel.

Hoy trataré algo sencillo.. pero importante: cuál es el máximo de caracteres que admite Excel para los nombres de sus ficheros y de sus hojas.
Es algo que nunca me había planteado, ya que de manera natural evito nombres excesivamente largos (para ámbos), y desconocía el número exacto.. sin embargo en una formación me preguntó un alumno cuál era el número mágico.

Listaré primero cuáles son los caracteres especiales reservados que no debemos/podemos emplear en los nombres de archivo y hojas:
< (menor que) > (mayor que)
: (dos puntos)
" (comillas)
/ (barra división)
\ (barra)
| (barra vertical)
? (cierre interrogación)
* (asterisco)
y algunas otras menos comunes...

Comenzaremos las hojas de nuestro Libro de trabajo, donde el máximo de caracteres permitido es 31 caracteres.

Respecto al nombre del fichero la limitación es algo distinta, ya que se contabiliza la ruta de acceso al archivo completa (incluyendo el nombre del archivo), y no puede exceder de los 218 caracteres (por curiosidad, 242 para Word).
Esta limitación incluye tres caracteres que representan: la unidad, los caracteres de los nombres de la carpeta, la barra diagonal inversa (\) entre carpetas y los caracteres del nombre de archivo (y por supuestos los corchetes [])

Este número se basa en una limitación de 256 caracteres en Excel para crear vínculos a otros archivos, y en concreto el límite de 218 caracteres para el nombre de la ruta de acceso se basa en:
-Hasta 31 caracteres en un nombre de hoja.
-Apóstrofos y corchetes usados para denotar el nombre del libro.
-Signo de exclamación.
-Una referencia a la celda.
Por ejemplo:
E:\excelforo\documentos\...\[MiLibro.xlsx]Hasta_31_carac_NombreHoja!$A$1

OJO con la limitación, podría parecer que en la vida llegaremos a estos datos, pero cada vez que creamos un nivel en nuestro árbol de carpetas, estamos añadiendo más y más caracteres innecesarios, que en un momento nos podrían un error de Windows, y no permitirnos abrir nuestros ficheros...

Otras características y límites de las hojas de cálculo y de los libros de Excel relevantes son:
Característica - Límite máximo
Número total de caracteres que puede contener una celda: 32.767 caracteres
Celdas cambiantes en un escenario: 32
Celdas ajustables en Solver: 200
Criterios de ordenación: 64 combinados en una única operación; ilimitado en operaciones de ordenación secuenciales
Niveles de deshacer: 100
Campos en un formulario: 32
Listas desplegables de filtros: 10.000

Otras igualmente útiles de conocer:
Característica - Límite máximo
Libros abiertos: En función de la memoria disponible y los recursos del sistema
Tamaño de hoja: 1.048.576 filas por 16.384 columnas
Ancho de columna: 255 caracteres
Alto de fila: 409 puntos
Saltos de página: 1.026 horizontal y vertical
Caracteres en un encabezado o un pie de página: 255
Hojas en un libro: En función de la memoria disponible (el número predeterminado es 3)
Colores en un libro: 16 millones de colores (32 bits con acceso completo al espectro de colores de 24 bits)
Vista con nombre en un libro: En función de la memoria disponible
Formatos o estilos de celdas distintos: 64.000
Estilos de relleno: 32
Grosor y estilos de línea: 16
Tipos de fuentes distintas: 1.024 fuentes globales disponibles; 512 para cada libro
Formatos de número en un libro: Entre 200 y 250, según el idioma de la versión de Excel instalada
Nombres en un libro: En función de la memoria disponible
Ventanas en un libro: En función de la memoria disponible
Paneles en una ventana: 4
Hojas vinculadas: En función de la memoria disponible
Vista Escenario: En función de la memoria disponible, un informe de resumen sólo muestra los primeros 251 escenarios
Funciones personalizadas: En función de la memoria disponible
Escala de zoom: del 10 % al 400 %
Informes: En función de la memoria disponible
Parámetros del libro: 255 parámetros por libro

Para otras limitaciones entrar aquí.

Seguir leyendo...

Tablas dinámicas de Excel y cómo permitir diferentes filtros sobre un mismo campo

Leyendo un comentario de un articulo publicado en un grupo de LinkedIn al que pertenezco me acordé de algo poco habitual, y que hacía tiempo no empleaba.
Por ese motivo me decidí a compartirlo.

La idea es que en una tabla dinámica es posible habilitar la opción para que se nos permita aplicar sobre un mismo campo diferentes criterios de filtros (sobre etiquetas, sobre valor...).

Partiremos de un simple ejemplo, con una tabla de dos campos, a partir de la cual hemos construido una tabla dinámica:

Si con esta configuración por defecto intentáramos aplicar dos filtros sobre el campo 'Zona', por ejemplo: Zonas 'Centro' y 'Norte' y a continuación un filtro por valores mayores de 25 comprobaríamos que la segunda 'fagocita' a la primera... es decir, sólo estaría permitido un único filtro por campo...

Pero esto puede cambiar...
Accederemos a las Opciones de tabla dinámica (por ejemplo botón derecho del ratón sobre la Tabla dinámica), y luego navegaremos hasta la pestaña Totales y filtros y nos aseguraremos de dejar marcada la opción: Permitir varios filtros por campo:

Tras Aceptar, estaremos en disposición de realizar los filtros comentados:
1-Zonas 'Centro' y 'Norte' y
2-filtro por valores mayores de 25

haz clic en la imagen

Permitidme recomendar este libro sobre Tablas dinámicas escrito por Miguel Caballero & Fabian Torres (excelfree.weebly).
El libro trata temáticas avanzadas sobre tablas dinámicas, además, proporciona información completa y detallada acerca de la serie de vídeos tablas dinámicas a profundidad donde se puede aprender esta funcionalidad desde lo más básico a lo más profesional. :

Puedes comprarlo accediendo desde aquí o haciendo clic en la imagen...
Acerca del Libro:
Las tablas dinámicas son una de las funcionalidades más poderosas de la familia de software ofimáticos de Microsoft, además, la llegada de Power Pivot supuso un gigantesco salto para llevar acabo análisis de datos de forma eficaz y crear modelos de datos sofisticados.
Power Pivot trabaja en armonía con las tabla dinámicas, por lo que son los pilares de Excel con mayor trascendencia.

Este libro tiene como objetivo brindar un conjunto de trucos y macros enfocados a Tablas Dinámicas, trucos que servirán tanto para la estética del reporte, solución de tareas y automatización mediante macros, así como una primera guía a Power Pivot. La finalidad del manual es tomar un paso más avanzado si se quiere ir más allá de los cursos convencionales. Los trucos, tips y macros presentados aquí son una colección que los autores (Miguel Caballero & Fabian Torres) han aprendido en los últimos dos años de diversos libros, blogs y por experiencia.

Especificaciones Técnicas del manual:
Peso del Archivo: 12,6 MB
Tamaño de Hojas: Carta, 8 1/2 " x 11"
Número de Páginas: 361 Páginas
Formato: PDF
Tinta Interior: Full Color
Idioma: Español
Publicación: Self-Publishing
Fecha de Publicación: 24 de Abril del 2015
ASIN: B00TVM3CRG disponible en Kindle
DRM: Libre
Autores: Miguel Caballero & Fabian Torres
Tabla de Contenido en: http://issuu.com/miguelcaballerosierra/docs/tablas_din__micas__look_inside

Seguir leyendo...

No mostrar el texto (en blanco) en nuestras tablas dinámicas.

La idea de hoy consiste en lograr que no aparezca el poco vistoso texto (en blanco) en nuestras áreas de filtro (área de filas o columnas) de nuestras tablas dinámicas de Excel.

haz clic en la imagen

Observemos el origen de nuestro problema.. en el origen o fuente de datos, en algunos de nuestros campos existen elementos vacíos, sin información.

Una posible solución es simple, seleccionamos nuestra Tabla origen, de la cual se alimente la Tabla dinámica, y realizaremos un reemplazamiento de Nada por un Apóstrofe:

haz clic en la imagen

Después de este reemplazamiento y la correspondiente actualización o refresco de la tabla dinámica conseguimos nuestro objetivo...

haz clic en la imagen

Una segunda manera es aplicar sobre la tabla dinámica un formato condicional, con tipo fórmula(en nuestro ejemplo):
=$G4="(en blanco)"

para acabar indicándole un formato personalizado del tipo: ";;;":

Seguir leyendo...

VBA: Recuperar un dato de un elemento seleccionado de un ListBox.

Es muy frecuente me llegue un pregunta parecida a esta:
Si tengo un Listbox en un Userform cargado desde una tabla de Excel con varias columnas, ¿cómo capturo el dato de una celda específica de la fila seleccionada?

El asunto se puede resolver de diferentes maneras.. pero hoy contaré una muy sencilla empleando la propiedad .Column del control ListBox.

Veámoslo con un ejemplo. Partiremos de la siguiente Tabla en nuestra hoja de cálculo (llamada 'Tabla1'):

A continuación construiremos nuestro UserForm (formulario de usuario) desde nuestro Editor de VB.
Constará de:
1- un Cuadro de lista/ListBox:= ListBox1
2- tres cajas de texto/TextBox:= TxtCodigo, TxtUnidades y TxtImporte
3- tres etiquetas/labels:= label1, label2 y label3:

Terminamos la parte del diseño definiendo las propiedades del ListBox1:
.RowSource = Tabla1
.ColumnCount = 3
.ColumnHeads = True
.Column.Widths40 = 40 pt;49,95 pt;49,95 pt

Ya estamos en disposición de incorporar el código necesario...
Para ello insertamos el código asociándolo a nuestro UserForm de nuestro proyecto de VBA desde el editor de VB.
Es un sencillo evento Click vinculado al ListBox

Private Sub ListBox1_Click()
 
'si hemos seleccionado algún item...
If ListBox1.ListIndex <> -1 Then
    Me.TxtCodigo.Value = ListBox1.Column(0)     'tomamos el dato del primer campo
    Me.TxtUnidades.Value = ListBox1.Column(1)   'tomamos el dato del segundo campo
    Me.TxtImporte.Value = ListBox1.Column(2)    'tomamos el dato del tercer campo
End If

End Sub

Lo que hemos conseguido es mostrar en los TextBox el valor de cada columna/campo del elemento seleccionado mediante el empleo de la propiedad .Column...

Seguir leyendo...

VBA: Una función personalizada para devolver datos ordenados.

La idea del trabajo de hoy es obtener un listado ordenado (en sentido descendente-de mayor a menor-) mediante una UDF, o función personalizada en VBA.
Supongamos el siguiente origen de información, del cual queremos obtener un listado a parte con aquellos registros cuyo número de unidades sea mayor, por ejemplo, los cinco primeros.. aunque, con esta personalización podrías obtener aquellas posiciones que deseáramos.

haz clic en la imagen

Construiremos una función que realice por nostros la búsqueda de los valores que necesitemos, la posterior ordenación interna, y acabe devolviendo el valor ya ordenado...
Lo interesante es que apoyándonos en nuestro propio Tipo de datos (ver cómo) desplegaremos no solo los valores numéricos ('unidades') si no también el resto de datos de esos registros...

El proceso completo de la función es:
1- carga de datos en nuestra Array (matriz)
2- ordenación de esos datos siguiendo el modelo burbuja (ver)
3- la función acaba devolviendo un dato según el argumento 'tipo' seleccionado.. quien retornará una de las propiedades del Type.

Insertamos el código de la función en un módulo estándar de nuestro proyecto de VBA desde el editor de VB:

Type Referencias
'definimos un tipo de dato personalizado con tres propiedades....
    Uds As Integer
    NumRef As String
    DSArt As String
End Type
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function ordenado(rng As Range, orden As Integer, tipo As String)
'el argumento tipo nos dirigirá hacia una o otra propiedad de nuestro tipo de dato
'tipo puede ser: 'producto', 'descripcion' o 'unidades'

Application.Volatile
'contamos celdas con datos, no vacías...
Num = Application.WorksheetFunction.CountA(rng)
Dim matriz() As Referencias
ReDim matriz(1 To Num) As Referencias

'rellenamos nuestra matriz con los valores con datos
x = 1
For Each celda In rng
    If celda.Value <> 0 Then
        matriz(x).Uds = Val(celda.Value)
        matriz(x).NumRef = celda.Offset(0, -2).Value
        matriz(x).DSArt = celda.Offset(0, -1).Value
        x = x + 1
    End If
Next celda

'reordenarmos empleando el método de burbuja
Dim strTemp As Referencias
Dim i As Long, j As Long
Dim lngMin As Long, lngMax As Long
lngMin = 1
lngMax = x - 1
'ordenado por Burbuja
For i = lngMin To lngMax - 1
  For j = i + 1 To lngMax
    If Val(matriz(i).Uds) < Val(matriz(j).Uds) Then
      'para unidades
      strTemp.Uds = Val(matriz(i).Uds)
      matriz(i).Uds = Val(matriz(j).Uds)
      matriz(j).Uds = Val(strTemp.Uds)
      'para Cod Artículo
      strTemp.NumRef = matriz(i).NumRef
      matriz(i).NumRef = matriz(j).NumRef
      matriz(j).NumRef = strTemp.NumRef
      'para Descripción
      strTemp.DSArt = matriz(i).DSArt
      matriz(i).DSArt = matriz(j).DSArt
      matriz(j).DSArt = strTemp.DSArt
    End If
  Next j
Next i

'devolvemos un dato u otro según el argumento 'tipo'
If tipo = "unidades" Then
    ordenado = matriz(orden).Uds
ElseIf tipo = "descripcion" Then
    ordenado = matriz(orden).DSArt
ElseIf tipo = "producto" Then
    ordenado = matriz(orden).NumRef
Else
    ordenado = "error"
End If
End Function

En la celda F3 introduciremos la función: =ordenado($C$3:$C$21;$E3;F$2) donde alimentamos los argumentos creados en nuestra UDF. primer argumento: rango de los valores (campo unidades). segundo argumento: posición deseada; recuerda el orden es descendente. Posición 1 el mayor valor, etc.. tercer argumento: dato deseado (unidades, producto o descripción) Como veíamos en la imagen de más arriba, el orden obtenido responde a lo que pretendíamos...

Seguir leyendo...

Consiguiendo un objetivo con fórmulas.

Trataré hoy un caso sencillo: cómo conseguir un margen objetivo sin emplear las herramientas Buscar Objetivo ni Solver, sólo empleando fórmulas y funciones.

Esta es nuestra información de partida:

Haz clic en la imagen

Vemos cómo con la función de Excel SUMAPRODUCTO en nuestra celda F4 hemos conseguido el margen actual global:
=SUMAPRODUCTO($C$7:$C$15;($E$7:$E$15-$D$7:$D$15))/SUMAPRODUCTO($C$7:$C$15;$D$7:$D$15)

es un margen ponderado tomando en cuenta los precios de venta, los costes y las unidades!!!.
Notemos como la función SUMAPRODUCTO al admitir operaciones matriciales, de manera interna, me permite directamente obtener la diferencia entre Precio Venta y Coste Unitario:
SUMAPRODUCTO($C$7:$C$15;($E$7:$E$15-$D$7:$D$15))

En la celda G1 vemos cuál es el margen objetivo global a alcanzar, en nuestro ejemplo 30%.

El trabajo, entonces, consiste en determinar cuáles deben ser los precios de venta para, según nuestras condiciones (costes y unidades), llegar al margen del 30%.

De igual forma, para verificar el éxito de nuestro cálculo, en la celda G4 hemos añadido una fórmula similar a la anterior, pero tomando el cuenta el nuevo Precio de Venta a calcular:
=SUMAPRODUCTO($C$7:$C$15;($G$7:$G$15-$D$7:$D$15))/SUMAPRODUCTO($C$7:$C$15;$D$7:$D$15)

El truco de la fórmula para llegar a nuestros nuevos precios de venta es muy sencillo, incrementar cada margen individual de cada artículo en el porcentaje existente entre margen actual global y margen objetivo.
En nuestro ejemplo, en las celdas G7:G15:
=(D7*(1+F7+$G$1-$F$4))

Haz clic en la imagen

Observamos en el ejemplo como nuestro nuevo margen global coincide exactamente con el objetivo marcado.
Igualmente podríamos verificar cual es el margen individual nuevo, para el nuevo precio.. y cómo se ha comportado de forma ponderada para cada caso...

Seguir leyendo...