El truco que quiero mostrar es para Excel. Está directamente relacionado con la formula "DESREF". ¿De qué se trata? La formula tiene la siguiente estructura:
=desref(Referencia;Indicador de fila;Indicador de columna;Alto de la tabla(opcional); Ancho de la tabla (opcional))
con esta estructura, el uso que se le puede dar es el siguiente: link
o también puede verlo en office
Más que describir la formula lo que quiero mostrar ahora, son cuatro funciones más a partir de esta fórmula:
- Llamar datos de una tabla sin tener que especificar la fila (ó columna)
Para esto tenemos que anidar la formula "columna()" y "Fila()". Estas formulas funcionan igual. Devuelven el numero de columna o fila de una celda de referencia, respectivamente. Si se maneja vacía (es decir "columna()") devuelve el numero de la columna donde se escribe la formula.
Por lo tanto, ocupamos la formula de la siguiente forma:
desref(anclafija;fila(ancla)-fila(anclafija);columna(ancla)-columna(anclafija))
donde ancla, es el argumento donde se inicia la tabla. Cuando se habla de ancla fija significa que le agregamos "$" a la columna y a la fila, de tal manera que no se mueva cuando corramos la formula para el resto de la tabla que estamos construyendo.
la expresión "columna(ancla)-columna(anclafija)" o "expresion columna" de ahora en adelante, es lo que permite a la formula ir cambiando la columna de extracción a medida que aplicamos la formula en las demás celdas. En el fondo siempre va a tomar la columna donde esté y le va a restar a la celda especifica donde estemos aplicando la formula, la cantidad de columnas desde el ancla teniendo finalmente la referencia requerida. Para las filas es la misma idea.
- Saltar columnas en base a un patron (par, impar, cada n columnas)
considerando que la expresion columna (al igual que la expresion fila) devuelve los numeros naturales enteros desde 0 en adelante, si colocamos un ponderador entero, entonces le decimos que vaya de dos en dos o de tres en tres o el intervalo que quieran. tambien podemos asumir que cuando no hay ponderador de esta expresión entonces, por defecto es 1. La formula quedaría del siguiente modo.
desref(anclafija;Ponderador(fila(ancla)-fila(anclafija));Ponderador(columna(ancla)-columna(anclafija)))
desref($C$12;2*(fila(C12)-fila($C$12));3*(columna(c12)-columna($c$12)))
En este ejemplo los valores devueltos seran los valores que estén cada 3 columnas y cada 2 filas.
Dado que la expresion (columna(c12)-columna($c$12) son los naturales enteros, tambien podemos devolver los impares sumando 1 o podemos tener millones de combinaciones segun queramos como avance y desde donde comience la extraccion de datos.
es importante tener en cuenta que toda la expresion "(columna(ancla)-columna(anclafija)" debe ir entre parentesis, porque de lo contrario no resultaría.
- Trasponer la tabla
Quizas esta es la más dificil de explicar pero funciona de la misma manera, solo que en vez de colocar las formulas de columna (relativa y fija) en la columna, se colocan en la fila y viceversa. Recordemos que la formula es:
=desref(Referencia;Indicador de fila;Indicador de columna)
Citando el articulo de referencia está formula funciona "verbalmente" de la siguiente forma:
"comience desde el ancla, muevase n celdas hacia abajo y m celdas hacia la derecha"
Por lo tanto, del siguiente modo la tabla nos vendrá traspuesta:
desref(anclafija;columna(ancla)-columna(anclafija);fila(ancla)-fila(anclafija))
Verbalmente estamos diciendo: Desde el ancla extraiga el dato que está en la fila "p" columnas hacia abajo y "q" filas hacia la derecha.
Mejor dicho con esta formula, cuando, por ejemplo la ejecutamos una celda más abajo del ancla, la expresion "columna(ancla)-columna(anclafija)"es igual a 0, porque no avanzamos columnas, sino una fila hacia abajo. Por lo tanto el argumento filas de la formula desref queda en 0. Por su parte, la expresion "fila(ancla)-fila(anclafija)" es igual a 1 y en la formula desref el llamado de la columna es igual a 1. En resumen, el dato extraido será el dato que esté a 0 filas abajo y 1 columna a la derecha del ancla. Tal vez ya lo entendieron, pero como a mi me costó entenderlo lo explico de esta forma.
- Trasponer y saltar columnas y/o filas en una sola formula
Y ahora la última complicación es cuando además de la trasposición de la tabla quieren sacar ciertas columnas o filas de la tabla, se debe ponderar NO mirando las expresiones construidas en base a la formulas fila o columna, SINO al argumento que está solicitando la función desref. Por ejemplo si quiero que extraiga los datos cada tres columnas entonces tengo que hacer lo siguiente:
desref(anclafija;columna(ancla)-columna(anclafija);3*(fila(ancla)-fila(anclafija)))
porque ese es la referencia de COLUMNA tiene la función desref.
En el fondo, tal vez es mas facil entender este procedimiento si consideramos que las expresiones construidas en base a las formulas fila y columna, NO son lo mismo que los argumentos de la función desref.
- Trasponer solo una variable y dejar el resto igual.
Supongamos que tenemos una base excel con esta estructura:
| sujeto | pregunta | porcentaje logro |
| a | 1 | 75,0% |
| a | 2 | 16,6% |
| a | 3 | 58,9% |
| a | 4 | 24,2% |
| b | 1 | 14,4% |
| b | 2 | 1,8% |
| b | 3 | 49,5% |
| b | 4 | 15,0% |
| c | 1 | 76,5% |
| c | 2 | 1,3% |
| c | 3 | 68,1% |
| c | 4 | 34,3% |
| d | 1 | 85,9% |
| d | 2 | 44,8% |
| d | 3 | 93,0% |
| d | 4 | 17,4% |
y se necesita que la estrutura sea la siguiente
| sujeto | Preg1 | Preg2 | Preg3 | Preg4 |
| a | 75,0% | 16,6% | 58,9% | 24,2% |
| b | 14,4% | 1,8% | 49,5% | 15,0% |
| c | 76,5% | 1,3% | 68,1% | 34,3% |
| d | 85,9% | 44,8% | 93,0% | 17,4% |
utilice la siguiente formula:
DESREF(Anclafija;(COLUMNA(ancla)-COLUMNA(anclafija))+intervalodelsalto*(FILA(ancla)-FILA(anclafija));Ncolumnas(partiendo de 0))
en el ejemplo esta formula quedó de la siguiente forma:
=DESREF($B$3;(COLUMNA(B3)-COLUMNA($B$3))+4*(FILA(B3)-FILA($B$3));2)
el unico requisito es que numero de preguntas tiene que ser el mismo para cada sujeto (cuatro en el caso del ejemplo) si no son esas las condiciones, se recomienda que primero se genere la base con todas las preguntas posibles de tal manera de dejar constante ese intervalo y luego aplicar la formula.
Eso es todo por ahora, se vienen muchos post mas. Espero que este sea de utilidad.
