photosdematures.com

Com es poden crear referències estructurades a Excel?

Les referències estructurades comencen per taules Excel. Tan aviat com les taules creades a Excel creen automàticament referències estructurades.

Ara mireu la imatge següent.

• Pas 1: Havia donat un enllaç a la cel·la B3, en lloc de mostrar l'enllaç com a B2 que es mostra com a Taula1 [@Venda]. Aquí Taula 1 és el nom de la taula i @Venda és la columna a què ens referim. Totes les cel·les d’aquesta columna es refereixen amb un nom de taula i seguides del nom de l’encapçalament de la columna.
• Pas 2: Ara canviaré el nom de la taula a Taula_dades i canvieu el títol de la columna a Import.
• Pas 3: Per canviar el nom de la taula, col·loqueu un cursor dins de la taula> aneu a Disseny> Nom de la taula.

• Pas 4: Esmenta el nom de la taula com a Taula_dades.

• Pas 5: Ara canvieu i doneu una referència a la cel·la B3.

Per tant, hem entès que la referència estructurada té dues parts Nom de la taula i nom de la columna.

Exemples

Podeu descarregar aquesta plantilla Excel de referències estructurades aquí - Plantilla Excel de referències estructurades

Exemple 1

Mitjançant referències estructurades podeu dinamitzar la vostra fórmula. A diferència de les referències de cel·les normals, permet que la fórmula sigui activa en cas d'afegir-se i suprimir-se a l'interval de dades.

Permeteu-me aplicar la fórmula SUM per a la taula d’abast normal i excel.

Fórmula SUM per al rang normal.

Taula SUM de la fórmula Excel.

Permeteu-me afegir algunes línies a les dades de la taula normal i excel. He afegit 2 línies de comanda a les dades; ara veig la diferència.

La referència estructurada a la taula Excel mostra el valor actualitzat, però l'interval de dades normal no mostra els valors actualitzats tret que feu alguns canvis a la fórmula manualment.

Exemple 2

Ara, mireu un exemple més. Tinc informació sobre el nom, la quantitat i el preu del producte. Amb aquesta informació he d’arribar al valor de venda.

Per obtenir el valor de les vendes, la fórmula és Quantitat * Preu. Apliquem aquesta fórmula a la taula.

Diu la Fórmula [@QTY] * [@PRICE]. Això és més comprensible que la referència normal de B2 * C2. No obtenim el nom de la taula si posem la fórmula dins de la taula.

Problemes amb les referències estructurades d'Excel

Mentre fem servir referències estructurades, ens enfrontem a alguns problemes que es detallen a continuació.

Problema núm. 1

Les referències estructurades també tenen els seus propis problemes. Tots coneixem l’aplicació de la fórmula Excel i la seva còpia o arrossegament a la resta de cel·les restants. Aquest no és el mateix procés a les referències estructurades, funciona de manera diferent.

Ara mireu l’exemple següent. He aplicat la fórmula SUM a excel per al rang normal.

Si vull sumar el preu i el valor de venda, simplement ho faré copiar i enganxar o arrossegar la fórmula actual a les altres dues cel·les i em donarà el valor SUMA del preu i el valor de venda.

Ara apliqueu la mateixa fórmula per a la taula Excel per a la columna Quantitat.

Ara obtenim la suma de la columna Qty. Igual que l'interval normal, la fórmula copia la fórmula actual i enganxa-la a la columna Preu per obtenir el preu total.

Oh Déu meu!!! No mostra el total de la columna Preu, sinó que continua mostrant només el total de la columna Qty. Per tant, no podem copiar i enganxar aquesta fórmula a la cel·la adjacent o a cap altra cel·la per fer referència a la columna o fila relativa.

Arrossegueu la fórmula per canviar la referència

Ara ja sabem la seva limitació, ja no podem fer la feina de copiar-enganxar amb referències estructurades. Llavors, com superar aquesta limitació?

La solució és molt senzilla, només cal arrossegar la fórmula en lloc de copiar-la. Seleccioneu la cel·la de fórmula i utilitzeu el controlador de farciment i arrossegueu-la a les dues cel·les restants per canviar la referència de la columna a Preu i valor de venda.

Ara hem actualitzat fórmules per obtenir els totals respectius.

Problema núm. 2

Hem vist un problema amb les referències d’estructura i també hem trobat la solució, però tenim un problema més aquí, no podem fer la trucada com a referència absoluta si arrosseguem la fórmula a altres cel·les.

Vegem ara l’exemple següent. Tinc una taula de vendes amb diverses entrades i vull consolidar les dades mitjançant la funció SUMIF a excel.

Ara aplicaré la funció SUMIF per obtenir els valors de vendes consolidats de cada producte.

He aplicat la fórmula per al mes de gener, ja que és una referència estructurada que no podem copiar i enganxar la fórmula a les dues columnes restants, no canviarà la referència a febrer i març, així que arrossegaré la fórmula.

Ah !! No he obtingut cap valor a la columna de febrer i març. Quin seria el problema ??? Mireu de prop la fórmula.

Hem arrossegat la fórmula a partir del mes de gener. A la funció SUMIF, el primer argument és el rang de criteris Taula_venda [producte] ja que hem arrossegat la fórmula, ha canviat a Vendes_Taula [gener].

Llavors, com ens hi plantem ?? Hem de fer el primer argument, és a dir, la columna Producte com a absoluta i altres columnes com a referència relativa. A diferència de la referència normal, no tenim el luxe d’utilitzar la tecla F4 per alterar el tipus de referència.

La solució és que hem de duplicar la columna de referència tal com es mostra a la imatge següent.

Ara podem arrossegar la fórmula a altres escornades de dues columnes. El rang de criteris serà constant i les altres referències de columnes canviaran en conseqüència.

Consell professional: Per fer la fila com a referència absoluta, hem de fer una entrada doble de fila, però hem de posar el símbol @ abans del nom de la fila.

= Taula_venda [@ [Producte]: [Producte]]

Com es desactiva la referència estructurada a Excel?

Si no sou un fan de les referències estructurades, podeu desactivar-lo seguint els passos següents.

• Pas 1: Aneu a FITXER> Opcions.
• Pas 2: Fórmules> Desmarqueu Utilitzeu noms de taules a les fórmules.

Coses que cal recordar

• Per fer la referència absoluta en referència estructurada, hem de duplicar el nom de la columna.
• No podem copiar la fórmula de referència estructurada, sinó que hem d’arrossegar la fórmula.
• No podem veure exactament a quina cel·la ens referim en referències estructurades.
• Si no us interessen les referències estructurades, podeu desactivar-les.