Ordenar rango de celdas de texto con una matricial.

Hace ya bastante tiempo conté la manera de ordenar valores numéricos en un rango de celdas (ver). Hoy vamos a aplicar una técnica similar para ordenar un conjunto de celdas con texto.
En particular aprenderemos la particularidad de la función CONTAR.SI respecto a esta ordenación.
Nuestro objetivo será ordenar en sentido ascendente, esto es de A a Z, este conjunto de celdas:

Para esto es fundamental comprobar los valores resultantes de aplicar CONTAR.SI sobre un rango de celdas con texto, veamos la imagen siguiente donde he colocado las cinco vocales...

Observamos que la función:
=CONTAR.SI($A$2:$A$6;">="&A2)
con el operador mayor o igual que nos devuelve el número de elementos ordenados posteriores a la celda condicionada...
De igual forma
=CONTAR.SI($A$2:$A$6;"<="&A2)
nos dice el número de elementos en el rango a evaluar anteriores a la celda condicionada...

Dicho de otro modo el operador mayor o igual a me da una ordenación alfabética en sentido descendente (de Z a A), y el operador menor o igual a en sentido ascendente (de A a Z).
Así que ya tenemos la primera parte de nuestra fórmula matricial.

Si volvemos sobre nuestro listado original a ordenar en sentido ascendente, y para trabajar de una manera más sencilla, le asignaremos un nombre definido 'listado':
listado =Hoja1!$A$2:$A$20

A continuación seleccionaremos el rango contiguo en B2:B20 y en la celda activa B2 escribiremos nuestra matricial:
=INDICE(listado;COINCIDIR(K.ESIMO.MENOR(CONTAR.SI(listado;"<"&listado);FILA()-1);CONTAR.SI(listado;"<"&listado);0))
tras lo cual presionaremos Ctrl+Mayusc+Enter.
El resultado será nuestro 'listado' ordenado de A a Z:

Seguir leyendo...

Contando celdas: la combinación FILAS x COLUMNAS.

Hoy explicaré una forma de contar celdas sin macros, aplicado a un problema concreto planteado por un lector:

Requiero hacer una macro que busque un nombre (Hoja 1)en la columna A por ejemplo (Carlos) y que al mismo tiempo en la columna B tome el valor que tiene al lado, es decir: Columna A Columna B Carlos 8 Roberto 10 Carlos 4 y que sume el valor del numero las veces que se repita el nombre de Carlos. Lo interesante es que la suma del valor se distribuya en unos como una matriz en una Hoja 2 en un rango de 5 filas a lo largo de todas las columnas es decir si tengo una suma de 12 Columna A B C D E F 1 1 1 1 2 1 1 1 3 1 1 4 1 1 5 1 1

Veremos la manera de emplear las funciones estándar de hoja de cálculo a nuestro alcance para conseguir nuestro objetivo (distribuir una cantidad por los elementos de una matriz) sin necesidad de implementar una macro.
Para ello veamos de qué origen de datos partimos:

Hemos insertado en la celda D3 la función:
=SUMAR.SI(A1:A11;D2;B1:B11)
con el fin de tener el primer resultado solicitado, la suma de los valores de la columna B que coinciden con el valor 'carlos'.

El siguiente paso para conseguir nuestra matriz de cinco filas con 'unos' distribuidos será trabajar una 'matriz auxiliar'. Asi que no situaremos en la celda H2 y escribiremos:
=FILAS(H$2:H2)*COLUMNAS(H$2:H2)+MAX(G$2:G$6)
también podríamos haber optado por esta algo más sencilla:
=FILAS(H$2:H2)+MAX(G$2:G$6)
luego copiaremos cinco filas hacia abajo y tantas columnas a la derecha como necesitemos... esto resultará una numeración de arriba hacia abajo y de izquierda hacia derecha consecutiva:

Fijémonos en que no haya datos en la columna G.
El funcionamiento de esa operación =FILAS(H$2:H2)*COLUMNAS(H$2:H2) devuelve una numeración de filas por cada columna (1,2,3,4 y 5 para nuestro ejemplo); y a continuación sumamos el valor máximo de la columna anterior con +MAX(G$2:G$6); asegurándonos entonces ese secuencia correlativa con saltos de 5 en 5 por cada columna.

El siguiente y último paso consite en emplear nuestros valores calculados en la matriz auxilar y aplicarlos con el fin de obtener la distribución pedida.
Como ya conocemos cuál es el valor a distribuir, en la celda D3, bastará aplicar un condicional del tipo y escribir en H9:
=SI(H2<=$D$3;1;"")
arrastrando al resto de elementos de la matriz final:

Así en dos pasos tenemos lo que se solicitaba, y sin macros.

En el ejemplo he expuesto dos maneras de conseguir el mismo resultado, la primera consistía en multiplicar la función FILAS por la función COLUMNAS (aunque realmente para el ejemplo no era necesario); pero me parece interesante recordar cómo podemos contar celdas de un rango o matriz con funciones de Excel; y es que sería tan sencillo como calcular el producto del número total de filas por el número de columnas de dicha matriz:
=FILAS(H2:M6)*COLUMNAS(H2:M6)
por ejemplo, la matriz H2:M6 tiene 30 celdas (5 filas x 6 columnas).

Seguir leyendo...

Últimos cursos Excel y macros online con tutor del año!!!

Acabamos este año 2013, con tantas y tantas novedades personales y profesionales, y muchas ellas relacionadas con nuestra hoja de cálculo favorita: Microsoft Office Excel; sin duda una de las mejores herramientas que tenemos a nuestra disposición.
Por esto, cuanto mejor conozcamos las posibilidades de Excel, más y mejor rendimiento seremos capaces de extraer, y esto sólo se logra con formación estructurada y apoyado en todo momento por un tutor personal, con conocimientos claramente probados.. y que además ya conoces ;-)
Si necesitas aprender con los mejores te interesa la edición de Cursos de Excel y Macros online con tutor personal de Diciembre de 2013.

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

Curso Excel Avanzado para versiones 2007/2010

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Excel Financiero

(ver más)

Curso Macros Iniciación

(ver más)

Curso Macros 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)

Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de diciembre de 2013; 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...

Reestablecer Indicador de errores cometidos.

Hace un año expliqué cómo configurar el color del indicador que señalaba una celda con algún tipo de error (ver).
En el día de hoy aprenderemos la forma en que podemos reestablecer estos indicadores si por algún motivo los hubieramos omitido.
Veamos una serie de errores marcados en algunas celdas (fijémosnos en la esquina superior izquierda de las celdas):

Es sabido que seleccionado el desplegable automático de estas celdas podemos omitir la aparición del indicador:

Obviamente tras presionar esa opción para cada tipo de error, las marcas desaparecerán...

Seguir leyendo...

Versiones de Excel.

Hace algunas semanas tuve que recordar un poco de historia de Excel, ya que tenía que controlar algunas rutinas en función a la versión de Excel con la que trabajaban diferentes usuarios. Asi que decidí explicar algunas formas de obtener el número de versión de Excel (y alguna otra información) y mostrar un ejemplo sencillo de como dirigir y controlar un proceso en VBA según la versión de trabajo.
Me centraré en las versiones para Windows, ya que las versiones MAC van por otro lado...

En primer lugar veremos la forma manual de conocer la versión de tu Excel.
Para ello accederemos, en Excel 2007, al botón de Office > Opciones de Excel > Recursos > Acerca de..., para más detalle pulsaremos el botón de Acerca de:
>p align="center">

Si trabajas en Excel 2010 busca esta información en el menú Archivo > Ayuda > Acerca de:

Recordaré ahora el historial de versiones de Excel para Windows:
Versión 2.0 = Excel 2 (1987)
Versión 3.0 = Excel 3 (1990)
Versión 4.0 = Excel 4 (1992)
Versión 5.0 = Excel 5 (1993)
Versión 7.0 = Excel 95
Versión 8.0 = Excel 97
Versión 9.0 = Excel 2000 (Office XP)
Versión 10.0 = Excel 2002
Versión 11.0 = Excel 2003
Versión 12.0 = Excel 2007
Versión 14.0 = Excel 2010
Versión 15.0 = Excel 2013

Para conocer nuestra versión y otros detalles, incluso del sistema, podemos aplicar una sencilla macro:

Sub Conocer_Version()
Dim Vers As String
'controlamos qué versión es la de nuestra aplicación Excel abierta..
Select Case Application.version
    Case "2.0": Vers = "Excel 2 (1987)"
    Case "3.0": Vers = "Excel 3 (1990)"
    Case "4.0": Vers = "Excel 4 (1992)"
    Case "5.0": Vers = "Excel 5 (1993)"
    Case "7.0": Vers = "Excel 95"
    Case "8.0": Vers = "Excel 97"
    Case "9.0": Vers = "Excel 2000 (Office XP)"
    Case "10.0": Vers = "Excel 2002"
    Case "11.0": Vers = "Excel 2003"
    Case "12.0": Vers = "Excel 2007"
    Case "14.0": Vers = "Excel 2010"
    Case "15.0": Vers = "Excel 2013"
    Case Else: Vers = "Otras versiones de Excel...."
End Select
'mostramos información
With Application
    MsgBox Vers & vbCr & "Versión:= " & .version & " Número de versión:= " & .Build & vbCr & _
                "Nombre y número de versión del sistema operativo actual:= " & .OperatingSystem
End With

End Sub

En mi caso este es el mensaje mostrado:

Para emplear y poder dirigir procedimientos en nuestras macros según la versión de trabajo del usuario podríamos emplear esa propiedad .version, pero como el valor devuelto es una cadena de texto, no olvidemos anidarlo dentro de una función VAL de VBA:

Sub SegunVersion()
'en este ejemplo controlo si la versión es anterior a Excel 2007
If Val(Application.version) < 12 Then
    MsgBox ".xls"
    Else
    MsgBox ".xlsx"
End If

End Sub

Seguir leyendo...