Excel archivos

Mar, Vie, 2021

Excel

CAMPOS CALCULADOS en tabla dinámica

¿Cómo creamos campos calculados a partir de datos externos?

Las tablas dinámicas nos permiten calcular, resumir y analizar datos, de manera que podemos hacer comparaciones o ver patrones y tendencias en ellos.

Sin embargo, puede ocurrir que las funciones de resumen no nos proporcionen los resultados que necesitamos. Entonces podemos crear nuestras propias fórmulas en los campos calculados. Los campos calculados son una columna más de la tabla dinámica. En ellos podemos trabajar con fórmulas simples o bien recibir resultados de cálculos de funciones de la hoja de cálculo.

En nuestro ejemplo, vamos a acceder a una base de datos externa con cerca de 21.000 registros de ventas de 100 productos distintos. Cada venta supone una comisión a la empresa del 5%. Queremos saber el importe del pago de comisiones de venta por producto. Vamos a usar los valores de uno de los campos de la base de datos. Por tanto, una tabla dinámica con un campo calculado es una buena solución pues usamos los datos de un campo en la fórmula a calcular.

En primer lugar, conectamos el libro de trabajo con la base de datos. A continuación, creamos la tabla dinámica y añadimos el campo calculado. Por último, damos formato a la tabla dinámica.

Tabla dinámica y datos externos

Queremos un resumen de las ventas de cien productos que incluya el importe de la comisión de las ventas (un 5%). Contamos con una base de datos de veinte y un mil registros.

Conectamos el libro de trabajo con la base datos para construir la tabla dinámica.

Puede parecer que la base de datos no contiene demasiados registros y que podrían estar en una hoja del libro de trabajo. Hay dos buenas razones para que no sea así: primera, la velocidad de procesado es más rápida, y segunda, la integridad de los datos.

Si en lugar de veinte y un mil registros la base datos contuviera un millón, las ventajas serían evidentes.

¿Cómo lo hacemos?

Una vez conectados el libro de trabajo y la base de datos construimos la tabla dinámica y, dentro de ella, creamos el campo calculado.

Paso 1: conectamos con la base de datos

Abrimos el menú >> Tabla dinámica desde la barra de menús (menú >> Insertar)

Nos aparece la ventana >>> Crear tabla dinámica

Elegimos la opción >>> Utilizar una fuente de datos externa:

Seleccionamos la fuente de datos externa:

Abrimos la conexión y elegimos la celda inicial para crear la tabla dinámica:

conexión creada y celda inicial tabla dinámica

Ya hemos conseguido la conexión con la base de datos. Aparece la ventana para crear la tabla dinámica con los campos que podemos usar.

Paso 2: creamos la tabla dinámica

Para construir la tabla dinámica, arrastramos el campo PRODUCTOS a la sección Filas:

A continuación arrastramos el campo VENTAS a la sección Valores:

Podemos configurar el campo de valor con otras operaciones (Promedio, Recuento, Máximo, Mínimo…), pero para este caso nos interesa la suma.

Una vez configurados los valores, la tabla dinámica está lista:

Paso 3: creamos el campo calculado

Abrimos el menú >>> Campos, elementos y conjuntos desde la barra de menús (menú >>> Analizar)

Elegimos la opción >>> Campo calculado:

Aparece la ventana >>> Insertar campo calculado:

Damos un nombre al campo calculado. En este caso, COMISION VENTAS.

Creamos la fórmula para el campo calculado. Como queremos el importe de las comiones por producto, la fórmula es el campo VENTAS por el 5%.

Y conseguimos el campo calculado con las comisiones por ventas.

Paso 4: damos formato a la tabla

Para presentar un informe de las comisones pagadas fácil de interpretar, vamos a dar formato a los valores.

Con el botón derecho del ratón conseguimos el menú auxiliar de Excel:

Elegimos el menú >> Configuración de campo de valor:

Al hacer clic en la opción, nos aparece la ventana >>> Configuración de campo de valor:

Damos un nombre personalizado a la columna de VENTAS:

También damos nombre a la columna COMISION:

Finalmente hacemos lo mismo con la columna PRODUCTOS:

Aplicamos formato moneda a los valores:

Y por último, rotulamos los encabezados para que destaquen:

Descarga el libro de trabajo

Libro de trabajo con datos y fórmulas Este es un archivo con extensión .xlsx. Comprueba que el navegador no cambia la extensión al archivo.

Base de datos Este es un archivo con extensión .accdb. Comprueba que el navegador no cambia la extensión al archivo.

Feb, Vie, 2021

Excel

SUMA entre fechas

¿Cómo sumamos valores entre fechas?

Sumar fechas es muy sencillo cuando comprendemos cómo Excel maneja la información relacionada con el tiempo. Para Excel, una fecha es simplemente un número entero.

Siendo más preciso, una fecha es un número de serie que representa el número de días transcurridos desde enero de 1900. De esta manera, el número de serie 1 corresponde al 1 de enero de 1900. A ese número de serie le damos un formato especial. Por lo tanto, puede aparecer como 01/01/2020, o como miércoles, 01 de enero de 2020, o como 1-ene… pero el número de serie sigue siendo el mismo: el 43831.

Puesto que se trata de un entero, las fechas en Excel son muy manejables para nuestros cálculos. Así podemos sumar los valores de un mes concreto, o también podemos sumar valores según ciertos criterios, o quitar días con respecto a una fecha.

Tenemos los datos de los ingresos de una empresa durante el año 2020. La tabla que usamos lista algo más de 4.000 registros para que así podamos ver toda la potencia de cálculo de Excel con las fechas.

En primer lugar vamos a explorar cómo Excel suma entre fechas. A continuación cómo suma los valores por mes completo, y por último cómo suma fechas según períodos de tiempo.

Suma entre dos fechas

Queremos saber las ventas entre dos fechas. Para eso contamos con la tabla auxiliar donde escribimos la fechas que acotan la duración del período de tiempo.

En la celda TOTAL vamos a escribir la fórmula que calcula el total de ventas del período elegido.

Las dos fechas del intervalo de tiempo son dos condiciones para la suma entre fechas. Por lo tanto, en primer lugar la función atiende a esos dos criterios y criba los valores que los cumplen, y en segundo lugar los suma.

VIDEO

La función SUMAR.SI.CONJUNTO suma todos los argumentos que cumplen varios criterios. Podemos emplear hasta 127 criterios.

SUMAR.SI.CONJUNTO(rango_suma; rango_criterios1; criterios1; [rango_criterios2; criterios2];…)

¿Cómo lo hacemos?

Este caso hacemos un uso sencillo de la función SUMAR.SI.CONJUNTO. Solo tenemos que fijar los dos criterios que delimitan el rango de fechas.

Como tenemos muchos datos para manejar, previamente creamos referencias de nombre para los rangos que usamos en la función.

*creamos nombres para los rangos que usamos*

Los nombres fechas, productos y ventas incluyen, cada uno, los valores de las columnas correspondientes. De esta manera hacemos más manejable la función.

Paso 1: escribimos la función

Podemos hacerlo indistintamente en la celda o en la barra de fórmulas.

Paso 2: usamos nombres en la función

Como rango_suma empleamos el nombre ventas y como rango_criterios1 el nombre fechas.

Paso 3: creamos los criterios

Vamos a construir el primer criterio. Primero, tenemos que tener en cuenta que dentro de una fórmula los operadores de comparación son cadenas de texto y por tanto se escriben entre comillas.

=SUMAR.SI.CONJUNTO(ventas;fechas;">="

En caso de no hacerlo, Excel lanzará el aviso de error en la fórmula:

Incluimos la celda con la fecha de inicio en el criterio (la celda F4), y usamos la y comercial (&) para unir y generar un solo elemento:

Hacemos lo mismo para el segundo criterio. Como rango usamos fechas, y como criterio el operador menor o igual y la celda G4, que es la fecha final.

Ya tenemos acotadas las fechas del intervalo que necesitamos, y ahora conseguimos el resultado:

*resultado suma entre dos fechas elegidas*

Suma por mes completo

Queremos el resumen anual de las ventas mes a mes. En una tabla auxiliar tenemos los meses en formato <<nombre de mes-año>>, y en la celda TOTAL MES obtenemos las ventas de cada mes.

Para poder usar la función SUMAR.SI.CONJUNTO, necesitamos acotar el rango de fechas. Sin embargo, ese formato de fecha de la tabla auxiliar, en una única celda, no nos proporciona la última fecha de cada mes.

La función FIN.MES soluciona nuestro problema, y con ella podremos tener los dos criterios que fijan el rango de fechas.

La función FIN.MES nos devuelve el número de serie del último día del mes. A partir de una fecha inicial, buscamos el último día según el número de meses que le indiquemos.

FIN.MES(fecha_inicial, meses)

VIDEO FUNCION FIN.MES

¿Cómo lo hacemos?

Para solucionar este caso, vamos a combinar la función SUMAR.SI.CONJUNTO como ya hemos aplicado en el caso anterior, y la función FIN.MES.

También empleamos nombres para los rangos de ventas y fechas, además de los operadores lógicos mayor o igual que («>=») y menor o igual que («<=»).

Paso 1: escribimos la función

Podemos hacerlo indistintamente en la celda o en la barra de fórmulas.

Paso 2: ventas como rango para la suma

Como rango_suma usamos el nombre ventas:

A continuación escribimos el rango_criterios1: el nombre fechas:

rango_criterios1: los meses — *rango_criterios2: las fechas*

El criterio1 lo construimos con el operador lógico mayor o igual que… Como vemos, se escribe entre comillas.

La fecha inicial es el propio mes, es decir, la celda correspondiente al mes. En este caso F4:

Paso 3: la fecha final de cada mes

Para el segundo criterio, volvemos a emplear el nombre fechas:

El criterio2 lo construimos con el operador lógico menor o igual que… y también lo escribimos entre comillas.

Por último como fecha final, usamos la función FIN.MES. El primer argumento para esta función es la celda F4, es decir, el mes en cuestión, y como segundo argumento escribimos 0, puesto que queremos el último día de dicho mes.

Con esto, tenemos completa la función, y este es el resultado para el primer mes:

Extendemos la fórmula hacia abajo y conseguimos el resultado del infome mes a mes:

Suma de fechas según períodos de tiempo discontinuos

Ahora vemos cómo sumar períodos de tiempo discontinuos, es decir, de lunes a jueves, o los fines de semana, o todos los miércoles de un semestre…

Por ejemplo, queremos el resumen, mes a mes, primero de los ingresos de lunes a jueves y segundo de los fines de semana.

*tabla de ventas según períodos de tiempo*

Este resumen necesita la función DIASEM, que nos devuelve el día de la semana como un número entero entre 1 y 7.

Podemos aplicar la función DIASEM cuando tenemos una celda con un valor en cualquier formato de fecha, o bien cuando recibe el valor de una función FECHA o cuando es el resultado de otras fórmulas o funciones.

DIASEM(núm_de_serie,[tipo])

Añadimos una columna a la tabla principal para conseguir el día de la semana de las fechas.

Feb, Vie, 2021

Excel

FILTRAR para encontrar palabras clave

¿Cómo encontramos palabras clave a partir de nombres largos?

Tenemos una tabla principal con una lista de productos. Como vemos, los nombres son cadenas de texto largas que pueden incluir alguna palabra clave de la tabla auxiliar.

*lista de productos con algunos nombres largos*

En la tabla auxiliar, las palabras clave presentan información relativa a los productos: el grupo al que pertenecen.

*tabla auxiliar con información relacionada*

Pretendemos clasificar los productos de la tabla principal según las palbaras clave de la tabla auxiliar.

*lista de productos y sus palabras clave*

Por lo tanto, primero buscamos la palabra clave dentro del nombre del producto, y después usamos esa palabra clave como criterio de búsqueda para obtener todos los registros que lo cumplan.

La función HALLAR busca una cadena de texto dentro de una segunda cadena de texto y no discrimina en la búsqueda de textos entre mayúsculas y minúsculas. Por tanto es una función mucho más versátil que ENCONTRAR.

Con la función HALLAR conseguimos la posición inicial de la primera cadena de texto, a partir del primer carácter de la segunda cadena de texto.

HALLAR(texto_buscado;dentro_del_texto;[núm_inicial])

¿Cómo lo hacemos?

Lo que queremos conseguir tiene una sencilla lógica excel. En primer lugar, comprobamos si las palabras clave están en el nombre de los productos de la lista. De esto se encarga la función HALLAR. En segundo lugar, ese resultado lo usamos como criterio de filtrado para obtener la categoría a la que pertenece el producto. De esto se encarga la función FILTRAR.

Ambas partes trabajan juntas porque el resultado de HALLAR lo convertimos en un valor booleano (VERDADERO o FALSO) con la función ESNUMERO y ya nos sirve como criterio para el filtrado.

Paso 1: escribimos la función HALLAR

Empezamos a construir la fórmula escribiendo la función HALLAR en la barra de fórmulas.

Normalmente el argumento texto_buscado es un texto simple para la búsqueda dentro de un texto más largo. Por ejemplo, hallar «m» en «Madrid».

En nuestro caso empleamos como argumento un array de celdas: F5:F7, esto es, el rango donde están las palabras clave. De esa manera, si encuentra uno de ellos, nos da la posición inicial en el nombre del producto.

La referencia es absoluta porque evaluamos el nombre de cada producto de la lista con las palabras clave de este rango.

función HALLAR argumento texto_buscado array — *función HALLAR, argumento texto_buscado array*

Con esto, ya incluimos la celda del producto como el segundo argumento de la función (dentro_del_texto).

función HALLAR argumento dentro_del_texto — *función HALLAR, argumento dentro_del_texto*

Por último, la función HALLAR nos devuelve el número de la posición inicial del texto buscado (desde el primer carácter de la segunda cadena de texto), o bien nos devuelve un error porque no lo ha encontrado.

La evaluación que hace la función HALLAR encuentra «cocina» en la primera posición de «Cocina portátil» y por eso nos devuelve 1. Sin embargo, no encuentra «Casa» ni «Cabina» y devuelve el error !#VALOR.

Por tanto, nos interesan los valores numéricos que consigue la función HALLAR porque significan que la palabra clave existe dentro del nombre del producto.

Paso 2: necesitamos un valor lógico

Como acabamos de ver, la evaluación del array nos devuelve un valor numérico o un mensaje de error. Para utilizarlo como criterio de la función FILTRAR lo convertimos en un valor booelano con la función ESNUMERO.

La función ESNUMERO es una función de información y la usamos para comprobar si un valor es numérico. Si la celda contiene un valor numérico, devuelve VERDADERO; en otro caso, devuelve FALSO.

ESNUMERO(valor)

valor representa el valor que queremos verificar, entonces aplicado a nuestro caso:

y conseguimos este resultado:

Es decir, para el primer producto existe coincidencia en la lista de las palabras clave.

Paso 3: filtramos la lista de productos

Ahora ya tenemos listo el criterio para filtrar, así que empelamos la función FILTRAR.

La función FILTRAR extrae de un rango de datos los valores coincidentes con los criterios que definamos. Es una función disponible para versiones Microsoft 365.

FILTRAR(array;include;[if_empty])

En el primer argumento de la función, definimos el array de datos que filtramos. En nuestro caso, los valores de la categoría en la tabla auxiliar .

Como criterio, el resultado de la función ESNUMERO que nos devuelve un valor booelano.

y finalmente, si el criterio anterior nos devuelve FALSO, una alternativa. En este caso, optamos por el espacio en blanco con las comillas, aunque podríamos usar un texto de advertencia.

Validamos la fórmula y obtenemos, para el primer producto, el grupo que le corresponde.

Por último, extendemos al selección hacia abajo y conseguimos las categorías del resto de productos que contengan palabras clave:

filtrar para encontrar palabras clave — *resultado final*

Feb, Vie, 2021

Excel

BUSCARV para búsquedas parciales

¿Cómo buscar cuando solo tenemos parte del criterio de búsqueda?

Producimos piezas y tenemos un histórico del porcentaje de las piezas producidas con defectos. Queremos saber cuántas piezas con defecto fabricaremos de algunas piezas en la próxima serie.

En una tabla auxiliar tenemos el histórico de defectos con el código de la pieza.

Sin embargo, en la tabla principal solo tenemos parte del código de la pieza.

Para hacer el cálculo de piezas defectuosas en la tabla principal, primero tenemos que hacer la búsqueda y después conseguir el porcentaje en la tabla auxiliar.

BUSCARV es una función de búsqueda y referencia. La usamos para buscar elementos en una tabla o en un rango por fila.

BUSCARV(valor_buscado;matriz_tabla;indicador_columnas;[rango])

¿Cómo lo hacemos?

Aunque nuestros criterios son parciales, podemos hacer las búsquedas sin mayor problema con solo añadir el carácter * al criterio parcial de búsqueda.

Paso 1: escribimos la función de búsqueda

Empezamos a construir la fórmula escribiendo la función en la barra de fórmulas. Como valor buscado, el código del primer producto:

A continuación, selecionamos la matriz en donde queremos buscar (la tabla HISTÓRICO) y fijamos con F4, puesto que es una referencia absoluta: cualquier código de la tabla principal se consulta en la tabla auxiliar.

La columna que nos interesa de la tabla auxiliar es la segunda (% defectuosas) así que escribimos 2 como índice.

Por último, como queremos una coincidencia exacta, escribimos 0 ó FALSO en la función.

Validamos la fórmula y extendemos la selección hacia abajo, pero conseguimos el error #N/D (No Disponible) en las celdas, porque no encuentra ninguno de los valores parciales en la tabla auxiliar,

Paso 2: adaptamos el criterio parcial

Acabamos de comprobar que un argumento de búsqueda directo (el valor de la celda sin más) no nos sirve. Tenemos que mejorarlo para conseguir resultados.

El carácter *, usado con una cadena de texto en una búsqueda, reemplaza un grupo cualquiera de caracteres. Es un comodín que podemos usar según nos convenga. El otro carácter comodín (?) reemplaza un único carácter.

Por tanto, vamos a emplear el carácter * para mejorar el argumento de búsqueda. De esta manera, al añadir * a una cadena de texto, obtenemos nuevas cadenas de texto. Por ejemplo, tenemos la cadena ABC. Le añadimos delante * y entonces conseguimos cadenas como: 1ABC, XABC, D4fABC…

Si además añadimos el carácter * al final de la cadena, *ABC* dará lugar a cadenas como 14fABC2Nm, O23ABCtUU…

Para nuestro caso, escribimos la función en la celda como:

Observa que son necesarias las comillas junto al carácter * y que necesitamos el operador & para unir las tres subcadenas. El resto de la función no cambia:

Validamos la fórmula y extendemos la selección hacia abajo.

Ahora solo aparece el error #N/D (No Disponible) en las celdas cuyos valores no están en la tabla auxiliar.

Paso 3: controla el error #N/D

Controlamos el error #N/D con la función SI.ERROR. Con esto conseguimos sustituir la indicación del error por un mensaje propio o por un valor adecuado.

La función SI.ERROR tiene como primer argumento el resultado esperado, y como segundo argumento una alternativa en caso de error: un mensaje personalizado, un valor, una fórmula, otra función…

SI.ERROR(valor; valor_si_error)

Escribimos la función SI.ERROR al principio de la barra de fórmulas. Con esto usamos como primer argumento la evaluación realizada por la función BUSCARV.

Al final de la fórmula escribimos la alternativa en caso de error. Por ejemplo, la alternativa es «No hay datos».

Con esto validamos la fórmula y extendemos la selección hacia abajo.

A partir de la versión 2013, Excel incorpora la función SI.ND para controlar el error #N/D. Si dispones de esta versión o de una superior, puedes emplearla del mismo modo que SI.ERROR.

Paso 4: calculamos las piezas defectuosas

Las piezas defectuosas que obtendremos según los datos históricos son el resultado de la siguiente fórmula:

= unidades * (% piezas defectuosas/100)

En nuestro caso:

Ahora validamos la fórmula y extendemos la selección hacia abajo.

Ahora nos da un error de valor (#¡VALOR!) en las celdas con el valor No hay datos, es decir, las celdas donde no es posible hacer el cálculo.

Al igual que con el primer error, controlamos este segundo con la función SI.ERROR. Ahora, cuando se produce el error, dejamos la celda en blanco como valor (comillas vacías).

Validamos la fórmula y extendemos la selección hacia abajo.

Descarga el libro de trabajo

Libro de trabajo con datos y fórmulas Este es un archivo con extensión .xlsx. Comprueba que el navegador no cambia la extensión al archivo.

Ene, Vie, 2021

Excel

De datos en una columna a tabla

¿Cómo organizar los datos de una columna en una tabla?

Tenemos los datos de una serie de productos apilados en una columna y queremos organizar esos datos en una tabla.

Observamos que hay cuatro tipos de datos que se corresponden con varios campos distintos y que se van repitiendo hacia abajo.

cuatro tipos de datos en columna — *los cuatro tipos de datos*

Queremos una tabla que presente los datos en campos.

La tabla tendrá una fila de encabezados para los campos.

Cada celda de la tabla tiene que albergar el valor que le corresponda de la columna de datos. Para conseguirlo vamos a utilizar una técnica que usa la función INDICE.

La función INDICE nos devuelve el valor de una celda dentro de una matriz o tabla, según el número de fila que la celda ocupa dentro de la matriz. En la versión matricial de esta función, son argumentos obligatorios la matriz (o tabla) y el número de fila.

INDICE(matriz; núm_fila; [núm_columna])

Esta técnica completará las celdas de la tabla con los valores de las celdas de la columna de datos. La técnica consiste en emparejar las celdas de la columna de datos con las celdas que les correspondan en la tabla mediante indicadores. Estos indicadores los conseguimos combinando las funciones FILAS y COLUMNAS, que usaremos como argumento para el número de fila de la función INDICE.

La técnica

La función INDICE devuelve el valor de una celda dentro de una matriz, siempre que sepamos en qué fila está dicha celda.

De los dos argumentos obligatorios para usar la función INDICE, ahora mismo tenemos la matriz, que es el rango de celdas que contiene los valores en la columna de datos. Nos hace falta saber en qué fila está cada celda de la columna de datos. Pero la columna de datos puede estar colocada en cualquier lugar de la hoja de cálculo así que tenemos que asegurarnos una manera exacta para saber en qué fila está cada celda de la columna de datos.

Paso 1: indicadores numéricos para la matriz

Supongamos que cada celda de la columna de datos tuviese un identificador numérico; la primera celda sería el 1, la siguiente celda sería el número 2 y así sucesivamente hasta el final de la columna de datos.

*indicadores numéricos para la columna de datos*

Paso 2: indicadores numéricos para la tabla

Consideraremos la tabla como una cuadrícula.

Cada celda de la cuadrícula tendrá su indicador numérico. Este nos permitirá emparejar las celdas de la columna de datos con su correspondiente lugar en la cuadrícula. Así, el valor de la celda número 1 de la columna de datos irá a la celda número 1 de la tabla y después el siguiente valor hasta llegar al final de la columna.

*indicadores numéricos para la cuadrícula*

El indicador numérico para cada celda de la cuadrícula lo obtendremos mediante una fórmula que combina las funciones COLUMNAS y FILAS.

Paso 3: conseguimos el número de columna en la tabla

La función COLUMNAS nos devuelve el número de columnas de una matriz o de una referencia.

COLUMNAS(matriz)

El número de columna nos servirá para recorrer la tabla en sentido vertical. Nos situamos en la celda D3 (primera celda de la tabla a donde queremos llevar el primer valor de la columna de datos) y escribimos esta función:

Como queremos saber el número de columna de la primera celda de la cuadrícula (D3), la matriz que necesitamos para usar la función COLUMNAS será exclusivamente la propia celda D3. Cuando extendamos la selección al resto de columnas y filas, Excel tomará como referencia la columna D. Será la columna número 1 de la cuadrícula. Lo conseguimos usando una referencia mixta a la columna D (el símbolo dólar delante de la primera D).

Aplicamos y extendemos la selección al resto de columnas y conseguimos este resultado:

Ya tenemos un número para cada columna la tabla. Al extender la selección hacia abajo, aparece el valor de la columna a la que pertenece cada celda:

Paso 4: conseguimos el número de fila en la tabla

La función FILAS nos devuelve el número de filas de una matriz o de una referencia.

FILAS(matriz)

El número de fila nos servirá para recorrer la tabla en sentido horizontal. Igual que para las columnas, nos situamos en la celda D3 y escribimos esat función:

Queremos saber el número de fila de la primera celda de la cuadrícula (D3), por lo tanto la matriz que necesitamos para usar la función FILAS será exclusivamente la propia celda D3. Cuando extendamos la selección al resto de columnas y filas, Excel tomará como referencia la fila 3. Será lafila número 1 de la cuadrícula. Lo conseguimos usando una referencia mixta a la fila 3 (el símbolo dólar delante del primer 3).

Aplicamos y extendemos la selección al resto de columnas y conseguimos:

Ya tenemos un número para cada fila de la tabla. Cuando extendemos la selección hacia abajo, aparece el valor de cada fila en las celdas:

Paso 5: conseguimos un único número para cada celda de la tabla

Los resultados de las funciones COLUMNAS y FILAS utilizados por separado son inútiles. Son un par de valores que no identifican de manera única cada celda. Sin embargo, si los sumamos conseguimos un valor más cercano a lo que pretendemos.

*fórmula que suma los indicadores de columna y fila*

La fórmula proporciona estos resultados en las celdas:

*resultados de la fórmula para la primera fila*

Es decir, cada celda es el resultado de esta operación:

Extendemos la selección a toda la cuadrícula y conseguimos:

Fijándonos en los resultados, comprobamos que si restamos uno al valor de la fila, conseguimos la sucesión numérica que queremos para la primera fila: 1, 2, 3, 4. Ajustamos la fórmula restando 1 a la función FILAS.

y conseguimos la sucesión numérica que queremos para la primera fila:

Para el resto de celdas de la tabla conseguimos este resultado:

Tras este ajuste, para el valor de la función FILAS hemos conseguido esta serie numérica: 0, 1, 2 … 13 y 14. Como son cuatro los campos, la serie numérica tiene que ser múltiplo de 4, es decir, tenemos que ajustar de nuevo la fórmula mulitplicando el valor de (FILAS – 1) por 4.

Como el valor de la función COLUMNAS no ha cambiado (1, 2, 3, 4), la fórmula va a operar con estos valores en cada celda:

*origen de los valores ajustados de las celdas*

El resultado es este:

Acabamos de conseguir el valor de fila que necesitamos en cada celda para la función INDICE.

Paso 6: usamos la función INDICE

El primer argumento de la función INDICE (matriz) es la columna de datos como referencia absoluta:

El segundo argumento, número de fila, es el resultado de la fórmula que hemos construido en el paso anterior:

El resultado para la primera fila:

Extendemos la selección a toda la tabla:

*tabla con los valores de la columna de datos*

Hemos transferido correctamente los datos apilados de una columna a una tabla, ordenándolos por campos.

Categoría Excel

¿Cómo creamos campos calculados a partir de datos externos?

Tabla dinámica y datos externos

¿Cómo lo hacemos?

Paso 1: conectamos con la base de datos

Paso 2: creamos la tabla dinámica

Paso 3: creamos el campo calculado

Paso 4: damos formato a la tabla

Descarga el libro de trabajo

¿Cómo sumamos valores entre fechas?

Suma entre dos fechas

¿Cómo lo hacemos?

Paso 1: escribimos la función

Paso 2: usamos nombres en la función

Paso 3: creamos los criterios

Suma por mes completo

¿Cómo lo hacemos?

Paso 1: escribimos la función

Paso 2: ventas como rango para la suma

Paso 3: la fecha final de cada mes

Suma de fechas según períodos de tiempo discontinuos

¿Cómo encontramos palabras clave a partir de nombres largos?

¿Cómo lo hacemos?

Paso 1: escribimos la función HALLAR

Paso 2: necesitamos un valor lógico

Paso 3: filtramos la lista de productos

¿Cómo buscar cuando solo tenemos parte del criterio de búsqueda?

¿Cómo lo hacemos?

Paso 1: escribimos la función de búsqueda

Paso 2: adaptamos el criterio parcial

Paso 3: controla el error #N/D

Paso 4: calculamos las piezas defectuosas

Descarga el libro de trabajo

¿Cómo organizar los datos de una columna en una tabla?

La técnica

Paso 1: indicadores numéricos para la matriz

Paso 2: indicadores numéricos para la tabla

Paso 3: conseguimos el número de columna en la tabla

Paso 4: conseguimos el número de fila en la tabla

Paso 5: conseguimos un único número para cada celda de la tabla

Paso 6: usamos la función INDICE