Macro para intercambiar valores de un rango unidimensional

Excel permite transponer con facilidad los valores de un rango. Por ejemplo, si tenemos un rango vertical de valores como éste

y queremos convertirlo en un rango horizontal, usamos Copiar Pegado Especial-Transponer



¿Pero cómo hacemos para intercambiar los valores? Una posibilidad es arrastrando las celdas mientras mantenemos apretada la tecla Mayúsculas. Pero esto puede volverse muy tedioso y hasta irritante si se trata de un número de celdas considerable. Así que la solución que propondremos será con una macro.
Lo que queremos es que dado el rango A1:A5, obtener el rango C1:C5



La macro es la siguiente (nota: el uso de esta macro es sólo para rangos unidimensionales)

Sub swap_range()
Dim rngToSwap As Range, rngTarget As Range, cell As Range
Dim iX As Long, Counter As Long, HorVert As Integer
Dim arrval()

On Error GoTo err

Set rngToSwap = Application.InputBox _
(prompt:="seleccionar el rango", Type:=8)
Set rngTarget = Application.InputBox _
(prompt:="seleccione la primer celda para pegar", Type:=8)

HorVert = Application.InputBox( _
prompt:="Horizontal = 1;Vertical = 2", Type:=1)


Counter = rngToSwap.Count

ReDim arrval(Counter)

For iX = Counter - 1 To 0 Step -1
arrval(iX) = rngToSwap(Abs(iX - Counter)).Value
Next iX

Select Case HorVert
Case 1
Range(rngTarget, rngTarget.Offset(0, Counter)).Value = arrval
Case Else
Range(rngTarget, rngTarget.Offset(Counter, 0)).Value = _
WorksheetFunction.Transpose(arrval)
End Select

err:
Exit Sub

End Sub

Definimos tres variables que el usuario debe introducir usando Application.InputBox: el rango a intercambiar, la primer celda del rango objetivo y el sentido del rango a obtener (horizontal o vertical)



No sé hasta qué punto encontrarán útil esta macro. Sencillamente, me topé con este problema en una de mis tareas lo que me llevó a escribir la macro.

Technorati Tags: MS Excel

Ocultar #N/A en rótulos de datos en gráficos Excel

Supongamos que hemos representado los datos de la tabla en un gráfico, donde uno de los datos de la serie es un error #N/A

En nuestro gráfico hemos agregado rótulos para mostrar los valores de los puntos de la serie (las columnas). El problema es que no queremos que el resultado #N/A aparezca en el gráfico.

Si se trata de un gráfico “estático”, es decir que los datos no cambiarán, sería suficiente con seleccionar el rótulo del punto de la serie y borrarlo. Pero si se trata de un gráfico “dinámico”, es decir que los datos irán cambiando, tendremos que encontrar una solución también dinámica.

El contenido del rótulo está ligado a los datos de la tabla, por lo cual deberemos actuar sobres éstos. Una posibilidad es agregar una columna auxiliar para hacer una pequeña transformación de los datos: cuando un dato de #N/A lo transformamos en vacío (“”). Para eso usamos la fórmula

=SI(ESNUMERO(B2),B2,"")

Ahora cambiamos la referencia de la serie de datos al rango C2:C6

Si bien evitamos el #N/A, aparece en su lugar un 0, cuando nuestro objetivo es que no aparezca nada




Para lograrlo tendremos que cambiar el formato de número en el rango C2:C6. Usamos el formato personalizado

#;-#;;




Los formatos de números tienen la siguiente estructura:

Formato de números positivos; formato de números negativos; formato de ceros; formato de texto.

No hemos definido ningún formato para los ceros (no hay ningún parámetro entre el segundo y tercer “;”), por lo que los valores cero (y los vacíos) no son representados. Los valores en el gráfico “heredan” esta característica.

Technorati Tags: MS Excel

Ocultar filas con valor cero en tablas dinámicas.

Al trabajar con tablas dinámicas puede suceder que queramos ocultar filas cuyo valor es 0 (cero). La forma de hacerlo depende de la estructura de los datos que alimentan la tabla dinámica.

Si no hay valores repetidos en el área de las filas, como en este caso



Seguimos los siguientes pasos:

1 – Creamos la tabla dinámica y agregamos el campo “Valor” en el área de página



2 – Seleccionamos el campo de página y abrimos el menú de configuración de campo



3 – En la ventanilla “ocultar elementos” marcamos el 0 y apretamos “Aceptar”. Las filas de los elementos con valor 0 no aparecen en la tabla.



Esta técnica no funciona si hay elementos repetidos en el área de las filas, como en este caso



En este caso agregamos una columna auxiliar con esta fórmula

=--SI(SUMAR.SI($A$24:$A$35,A24,$B$24:$B$35)<>0,1)



Esta fórmula evalúa el total de cada grupo de valores para cada línea del grupo que totalice 0 da 0; en caso contrario 1.

Arrastramos el campo Auxiliar al área de página y seleccionamos 1



Las filas que totalicen 0 desaparecen.

Technorati Tags: MS Excel