miércoles, 24 de septiembre de 2014

Formación y Consultoría Excelforo.

Microsoft Excel, por que sin duda una buena base te permitirá avanzar en tu trabajo.
Aprende con los mejores: Edición de Cursos de Excel y Macros online con tutor personal de Octubre de 2014.


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

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)


Curso Excel Avanzado para versiones 2007/2010

(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 Octubre de 2014; 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.

lunes, 22 de septiembre de 2014

Otros usos de la función INDICE.

Estamos muy acostumbrados a emplear la función INDICE siguiendo su primera sintaxis para, principalmente, localizar un valor dentro de una matriz de valores, determinado por su número de fila y columna:
=INDICE(matriz-rango, número_fila, [número_columna])
Sin duda una forma muy cómoda y potente de búsqueda.. en especial al combinar/anidar la función COINCIDIR.


Sin embargo, la función INDICE tiene más recorrido y sorpresas que darnos... y es que esta función no sólo devuelve el dato de la matriz buscada, si no que también el resultado es una referencia y será interpretado como tal por otras fórmulas!!.
Por tanto, el valor devuelto por la función INDICE se puede usar como una referencia o como un valor, dependiendo de la fórmula.

Podemos comprobarlo en el siguiente ejemplo:


Observamos un uso habitual de INDICE para conseguir el dato dentro de un rango, en la celda 11 hemos introducido:
=INDICE($C$3:$N$8;3;4)
lo que encuentra el valor correspondiente a la fila 3 y columna 4, esto es, el resultado será: 472.
Sin embargo, haciendo uso, de por ejemplo la función CELDA con su argumento 'dirección', en nuestra celda C12:
=CELDA("dirección";INDICE($C$3:$N$8;3;4))
conseguimos fácilmente la referencia donde se encuentra el valor buscado ($F$5)!!.

Un gran avance para nuestras formulaciones, sin duda.


Pero tenemos alguna sorpresa más...
Si definimos los argumentos de INDICE número_fila o número_columna como 0 (cero) o vacío, nuestra función INDICE devuelve la matriz de valores de toda la columna o fila, respectivamente. Algo fantástico para trabajar matricialmente, si fuera el caso.
Sobre nuestro ejemplo de la imagen anterior.


Vemos las funciones insertadas en C14 y C15, que nos devuelven las sumas de la fila 3 y columna 4 completas (del rango o matriz C3:N8).
En C14 la suma de toda la columna 4:
=SUMA(INDICE($C$3:$N$8;;4))
y en C15 la suma de toda la fila 3:
=SUMA(INDICE($C$3:$N$8;3;))


Potencia y flexibilidad.
Pero aún queda algo más, y es que la función INDICE tiene una segunda sintaxis, en forma de referencia:
=INDICE(ref, número_fila, [número_columna], [número_área])
En esencia tiene la misma finalidad, buscar o localizar valores o referencias dentro de un rango o matriz, pero en este caso si la referencia se compone de selecciones no adyacentes, podremos elegir la selección donde buscar. Esto es, podemos buscar información en rangos no contiguos!!.

Lo vemos en el siguiente ejemplo, con tres rangos donde buscar...

jueves, 18 de septiembre de 2014

Unidades de medida para Excel.

Un problema muy recurrente en Excel es determinar las propiedades alto y ancho (.Width y .Height) de una celda o de cualquier otro objeto (gráficos, imágenes, autoformas, etc.). sobre todo si queremos ser precisos; más aún cuando el proceso de definición viene dado desde algún procedimiento de VBA.
En definitiva, Excel NO nos proporciona ninguna regla horizontal ni vertical (al estilo de Word) y no hay ninguna manera rápida de medir el ancho o el alto de una hoja de cálculo en centímetros. Excel utiliza caracteres, puntos y píxeles como unidades de medida.


Si sobre una columna desplegamos y visualizamos su Ancho, de manera estándar veremos la etiqueta que nos informa que: Ancho: 10,71 (80 píxeles):

Unidades de medida para Excel.


De igual forma con el alto de la fila, nos parece, por defecto, la etiqueta: Alto: 12,75 (17 píxeles)

Unidades de medida para Excel.


Si alguien ha intentado descubrir la correlación, seguramente se haya vuelto loco...
12,75 es a 17 px
igual que
10,71 es a 80 px ????

martes, 16 de septiembre de 2014

La función COMBINAT: Calculando el número de combinaciones.

Veremos hoy una sencilla función que nos permite calcular el número de combinaciones, en definitiva que nos permite determinar el número total de grupos posibles para un número determinado de elementos.
OJO, hablamos sólo de calcular el número de grupos posible y no de listarlos!!... para lo cual te recomiendo leas estas entradas del blog:
ver 1
ver 2


Si pretendiéramos calcular el número de combinaciones posibles para un determinado número de elementos en grupos de diferentes tamaños, por ejemplo, de 10 elementos para grupos de tamaños de 1 a 10... podríamos plantear el siguiente ejercicio:

La función COMBINAT: Calculando el número de combinaciones.


Observamos que hemos empleado la función
=COMBINAT(número; tamaño)
con los siguientes argumentos:
Número:= El número de elementos.
Tamaño:= El número de elementos de cada combinación.

En el ejemplo, en la celda B4 insertamos
=COMBINAT($B$1;B3)
para luego arrastar hacia la derecha...
El resultado para cada agrupación de elementos obtenemos el número de combinaciones.


Me gusta recordar siempre la diferencia entre Permutaciones y Combinaciones: Una combinación es cualquier conjunto o subconjunto de objetos, independientemente de su orden interno. Las combinaciones son distintas de las permutaciones, en las que el orden interno es importante.

OJO!!, no confundir con la función COMBINA(número; tamaño), que nos devuelve la cantidad de combinaciones (con repeticiones) de una cantidad determinada de elementos.

jueves, 11 de septiembre de 2014

Usar el Tabulador para desplazarse por la hoja de cálculo.

Al hilo de la entrada anterior, veremos cómo emplear el Tabulador para desplazarse en horizontal.. y cómo cambiar de fila a la izquierda.

El asunto es sencillo pero práctico, queremos desplazarnos sobre una hoja de cálculo introduciendo ciertos valores (si fuera el caso), pero también queremos que al cambiar de fila retrocedamos al inicio o izquierda...
Por ejemplo, queremos movernos en horizontal entre las columnas A:F, y que al llegar al límite derecho (columna F) el carro nos retorno a la columna A, y la fila siguiente (una fila más abajo).


La acción es bien simple, no situaremos en la celda A1 y tras introducir el dato NO presionaremos Enter sino Tabulador, y al llegar al final de la fila (celda F1) y sólo en ese caso, presionaremos Enter, lo que nos llevará a la celda B2.
Veámoslo en el siguiente video:

Usar el Tabulador para desplazarse por la hoja de cálculo.


Una combinación recomendada (Tabulador + Enter) cuando debemos introducir manualmente muchos datos, y queremos despreocuparnos de los saltos de fila...

martes, 9 de septiembre de 2014

Mover selección después de presionar Enter.

Un usuario, a través de un comentario, preguntaba la forma de moverse dentro de un rango y saltar de fila al llegar a una celda presionando sólo el Enter.
La idea es poder introducir datos directamente en un rango de celdas (por ejemplo B3:G7) y que presionando Enter, al llegar a la última columna (columna G), salte tras validar con Enter a la fila siguiente y la columna B.
Por ejemplo, tras introducir un valor en G3, y presionar Enter salte a la celda B4...


Debemos tomar dos acciones. La primera consiste desde la Ficha Archivo, entrar en las Opciones de Excel > Avanzadas > Sección Opciones de Edición > Después de presionar Enter, mover selección Dirección:

Mover selección después de presionar Enter.


Seleccionaremos la opción de Dirección: Derecha

Segunda acción a tomar. Seleccionar el rango continuo donde queremos introducir información (rango B3:G7). Comenzaremos situándonos en B3 y cada vez presionemos Enter, nos desplazaremos hacia la derecha en horizontal!!, hasta llegar al límite derecho de nuestra selección (columna G), en cuyo momento, saltaremos a la primera columna (columna B) y fila siguiente...
Lo vemos en el vídeo siguiente:

Mover selección después de presionar Enter.


Se observa el movimiento de desplazamiento buscado...

jueves, 4 de septiembre de 2014

VBA: Cómo crear un tipo de dato definido por el usuario.

Aprenderemos a crear nuestros propios tipos de datos, que podremos usar como variables en nuestros procedimientos.
Comenzaremos repasando brevemente cuáles son los tipos de datos estándar en VBA (más habituales!):
Tipo Dato: Boolean
Memoria: En función de la plataforma de implementación
Valores: True o False

Tipo Dato: Date
Memoria: 8 bytes
Valores: 0:00:00 (medianoche) del 1 de enero de 0001 a 11:59:59 p.m. del 31 de diciembre de 9999.

Tipo Dato: Integer
Memoria: 4 bytes
Valores: -2.147.483.648 a 2.147.483.647 (con signo)

Tipo Dato: Long (entero largo)
Memoria: 8 bytes
Valores: -9.223.372.036.854.775.808 a 9.223.372.036.854.775.807 (9,2...E+18 †) (con signo)

Tipo Dato: String (longitud variable)
Memoria: En función de la plataforma de implementación
Valores: 0 a 2.000 millones de caracteres Unicode aprox.

Tipo Dato: Double (punto flotante de precisión doble)
Memoria: 8 bytes
Valores: -1,79769313486231570E+308 a -4,94065645841246544E-324 † para los valores negativos; 4,94065645841246544E-324 a 1,79769313486231570E+308 † para los valores positivos

y otros como: Byte, Decimal, Double (punto flotante de precisión doble), Object, Variant, Currency...

Pero hoy nos interesan en especial:
Tipo Dato: User-Defined(estructura)
Memoria: En función de la plataforma de implementación
Valores: Cada miembro de la estructura tiene un intervalo de valores determinado por su tipo de datos y es independiente de los intervalos de valores correspondientes a los demás miembros.


Remarcar lo fundamental de definir el tipo de datos de nuestras variables, y que hacerlo mal puede suponer un error en nuestros procedimientos.

Centrándonos en el asunto que nos ocupa, debemos saber que cualquier tipo de datos se define mediante la instrucción Type, y que los Tipos de datos definidos por el usuario podrán contener uno o más elementos de un tipo de datos, una matriz o incluso otro tipo de dato definido por el usuario previamente.
Diría que el uso de Tipo de datos definidos por el usuario son especialmente útiles cuando tenemos que pasar información entre diferentes procedimientos. En lugar de tener, digamos, cuatro parámetros en un mismo procedimiento, podríamos emplear un Tipo de datos definido por nosotros, con cuatro elementos.
Recomendable para hacer nuestro código más legible y mejor documentado.


Comencemos entonces poniendo los antecedentes del ejemplo a explicar.
Supongamos que trabajamos con un registro de Ventas de nuestros comerciales, con información del Comercial, Zona de trabajo, su Antigüedad en la empresa y el número de Ventas. En este caso, es posible que tengamos que trabajar con los valores de los diferentes valores de cada Comercial.
Una forma de hacer esto sería configurar las variables para cada elemento de los datos:

Dim Comercial As String
Dim Zona As String
Dim Antig As Integer
Dim Ventas As Currency


Por supuesto esta forma de trabajar funciona, pero ¿y si necesitamos trabajar con los datos de varios Comerciales a la vez?... en este caso podríamos crear una nueva variable para cada Comercial, pero claro, esto parece demasiado ineficiente.

La mejor mejor manera, entonces, es crear un Tipo de dato compuesto de cuatro elementos (Nombre, Zona, Antigüedad, Ventas) que contenga toda la información requerida:

Type TipoDatoPropio
    comercial As String
    zona As String
    antig As Integer
    ventas As Currency
End Type


OJO, debemos incluirlo dentro de nuestro proyecto VBA en un módulo, fuera de nuestros procedimientos!!

Ya tenemos creado nuestro propio tipo de datos. Ahora podremos usarlo en nuestros procedimentos.

Estos son los datos en nuestra hoja de cálculo:

VBA: Cómo crear un tipo de dato definido por el usuario.



En un módulo del proyecto VBA incluimos el siguiente procedimiento, que carga de nuestra hoja de datos los valores para cada comercial:

Public Sub ProbandoTipoDatoPropio()
'comienzo definiendo una Matriz con un tipo de datos 'TipoDatoPropio'
Dim MiMatriz(8) As TipoDatoPropio
Dim i As Integer, x As Integer

'asignamos valores a nuestra matriz
For x = 0 To 7
    MiMatriz(x).comercial = Cells(x + 2, 1).Value
    MiMatriz(x).zona = Cells(x + 2, 2).Value
    MiMatriz(x).antig = Cells(x + 2, 3).Value
    MiMatriz(x).ventas = Cells(x + 2, 4).Value
Next x

'mostramos el resultado, recorriendo la matriz-Array creada...
For i = LBound(MiMatriz) To UBound(MiMatriz) - 1
    Debug.Print "Dato: " & MiMatriz(i).comercial & " " & MiMatriz(i).zona & " " & _
        MiMatriz(i).antig & " " & MiMatriz(i).ventas
Next
End Sub



Tras ejecutar el procedimiento veremos en nuestra ventana de inmediato:



Observemos de qué forma tan sencilla hemos cargado los datos de una matriz con varias columnas, y de qué forma podemos recuperar los valores.. incluso de manera segmentada por columnas/campos si así quisiéramos...

Otro ejemplo más sencillo:

Type diario
    NumReg As Integer
    Descripcion As String
    Debe As Currency
    Haber As Currency
    Saldo As Currency
End Type
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub PruebaTipoDatosdiario()
Dim regdiario As diario

'aisgnamos valores a nuestra variable
regdiario.NumReg = 1001
regdiario.Descripcion = "Suministros"
regdiario.Debe = 750
regdiario.Haber = 125
regdiario.Saldo = regdiario.Debe - regdiario.Haber

'mostramos un dato...
MsgBox regdiario.Saldo

End Sub

martes, 2 de septiembre de 2014

Importando - Exportando datos en XML con Excel.

Aprenderemos cómo construir nuestro fichero XML con datos volcados en nuestra hoja de cálculo Excel.
Comenzar diciendo que XML es un Lenguaje estandarizado de Exportación e Importación de información, esto es el intercambio de datos; y su significado concreto viene del inglés: eXtensible Markup Language.
El fin principal de este lenguaje es almacenar datos en forma legible.


En el ejemplo que desarrollaré partiremos de una información contenida en una hoja de cálculo:

Importando - Exportando datos en XML con Excel.


El objetivo del día es generar en un fichero XML la información de nuestro origen, para a continuación realizar en otro Libro de Excel o base de datos de Access la importación de dichos datos...

Existen multitud de códigos VBA para Excel para generar nuestro fichero XML, y mucha información en la Web. Pero yo os propongo un método algo más sencillo: Descargarnos un complemento de Microsoft, que podemos encontrar en
http://www.microsoft.com/en-us/download/details.aspx?id=3108
Es un complemento gratuito y con la garantía Microsoft... y a pesar de estar definido para la versión 2003, sigue siendo 100% operativo en versiones superiores.


Tras instalar este complemento tendremos su propia Ficha:

Importando - Exportando datos en XML con Excel.



Con la instalación anterior, estamos en disposición de comenzar y finalizar en pocos pasos el proceso de Exportación-Importación XML.
Primer paso: Generamos el mapa XML con el complemento.
Para ello, presionamos Convert a Range in a XML List... desde el desplegable del complemento.
Esto abrirá la ventana para seleccionar dónde se encuentra el rango de datos:

Importando - Exportando datos en XML con Excel.


Tras el Ok se habrá generado el Mapa XML que podemos visualizar desde la Ficha Programador/Desarrollador > grupo XML > botón Origen: