Com establir un rang de valors a Excel

Quan s'utilitza Microsoft Excel, molt sovint l'usuari no sap per endavant quanta informació hi haurà a la taula com a resultat. Per tant, no entenem en totes les situacions quin rang s'ha de cobrir. Després de tot, un conjunt de cel·les és un concepte variable. Per desfer-se d'aquest problema, cal que la generació de rang sigui automàtica perquè es basi únicament en la quantitat de dades introduïdes per l'usuari.

Canvi automàtic dels intervals de cel·les a Excel

L'avantatge dels rangs automàtics a Excel és que faciliten molt l'ús de fórmules. A més, permeten simplificar significativament l'anàlisi de dades complexes que contenen un gran nombre de fórmules, que inclouen moltes funcions. Podeu donar un nom a aquest interval i, a continuació, s'actualitzarà automàticament en funció de les dades que contingui.

Com fer un canvi automàtic d'interval a Excel

Suposem que sou un inversor que necessita invertir en algun objecte. Com a resultat, volem obtenir informació sobre quant podeu guanyar en total durant tot el temps que els diners funcionaran per a aquest projecte. No obstant això, per obtenir aquesta informació, hem de controlar regularment quants beneficis totals ens aporta aquest objecte. Feu el mateix informe que en aquesta captura de pantalla.

A primera vista, la solució és òbvia: només cal sumar tota la columna. Si hi apareixen entrades, l'import s'actualitzarà de manera independent. Però aquest mètode té molts desavantatges:

1. Si el problema es resol d'aquesta manera, no es podran utilitzar les cel·les incloses a la columna B per a altres finalitats.
2. Aquesta taula consumirà molta memòria RAM, cosa que farà que sigui impossible utilitzar el document en ordinadors febles.

Per tant, cal resoldre aquest problema mitjançant noms dinàmics. Per crear-los, heu de realitzar la següent seqüència d'accions:

1. Aneu a la pestanya "Fórmules", que es troba al menú principal. Hi haurà una secció “Noms definits”, on hi ha un botó “Assigna un nom”, sobre el qual hem de fer clic.
2. A continuació, apareixerà un quadre de diàleg en el qual haureu d'omplir els camps tal com es mostra a la captura de pantalla. És important tenir en compte que hem d'aplicar la funció = DESPLAÇAMENT juntament amb la funció VERIFICACIÓper crear un interval d'actualització automàtica. 
3. Després d'això, hem d'utilitzar la funció SUM, que utilitza el nostre rang dinàmic com a argument. 

Després de completar aquests passos, podem veure com s'actualitza la cobertura de les cel·les pertanyents a la franja “ingressos” a mesura que hi anem afegint nous elements.

Funció OFFSET a Excel

Vegem les funcions que vam registrar anteriorment al camp "interval". Utilitzant la funció DISPOSICIÓ podem determinar la quantitat d'interval tenint en compte quantes cel·les s'omplen a la columna B. Els arguments de la funció són els següents:

1. Inicia la cel·la. Amb aquest argument, l'usuari pot indicar quina cel·la de l'interval es considerarà a la part superior esquerra. Informarà cap avall i cap a la dreta.
2. Interval compensat per files. Utilitzant aquest interval, establim el nombre de cel·les en què s'ha de produir un desplaçament des de la cel·la superior esquerra de l'interval. Podeu utilitzar no només valors positius, sinó zero i menys. En aquest cas, és possible que el desplaçament no es produeixi en absolut, o bé es durà a terme en sentit contrari.
3. Interval compensat per columnes. Aquest paràmetre és similar a l'anterior, només permet configurar el grau de desplaçament horitzontal del rang. Aquí també podeu utilitzar valors zero i negatius.
4. La quantitat de rang en alçada. De fet, el títol d'aquest argument ens deixa clar què vol dir. Aquest és el nombre de cel·les en què l'interval hauria d'augmentar.
5. El valor de l'interval en amplada. L'argument és semblant a l'anterior, només es refereix a les columnes.

No cal que especifiqueu els dos últims arguments si no cal. En aquest cas, el valor de l'interval serà només una cel·la. Per exemple, si especifiqueu la fórmula =OFFSET(A1;0;0), aquesta fórmula farà referència a la mateixa cel·la que la del primer argument. Si el desplaçament vertical s'estableix en 2 unitats, en aquest cas la cel·la es referirà a la cel·la A3. Ara descriurem amb detall què significa la funció VERIFICACIÓ.

Funció COUNT a Excel

Utilitzant la funció VERIFICACIÓ determinem quantes cel·les de la columna B hem omplert en total. És a dir, mitjançant dues funcions, determinem quantes cel·les s'omplen de l'interval i, en funció de la informació rebuda, determinem la mida de l'interval. Per tant, la fórmula final serà la següent: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Fem una ullada a com entendre correctament el principi d'aquesta fórmula. El primer argument apunta a on comença el nostre rang dinàmic. En el nostre cas, aquesta és la cel·la B2. Altres paràmetres tenen coordenades zero. Això suggereix que no necessitem desplaçaments en relació a la cel·la superior esquerra. Tot el que estem omplint és la mida vertical de l'interval, amb la qual hem utilitzat la funció VERIFICACIÓ, que determina el nombre de cel·les que contenen algunes dades. El quart paràmetre que hem omplert és la unitat. Així, mostrem que l'amplada total de l'interval hauria de ser d'una columna.

Per tant, utilitzant la funció VERIFICACIÓ l'usuari pot utilitzar la memòria de la manera més eficient possible carregant només aquelles cel·les que contenen alguns valors. En conseqüència, no hi haurà errors addicionals en el treball associats al mal rendiment de l'ordinador en què funcionarà el full de càlcul.

En conseqüència, per determinar la mida de l'interval en funció del nombre de columnes, heu de realitzar una seqüència similar d'accions, només en aquest cas heu d'especificar la unitat al tercer paràmetre i la fórmula al quart. VERIFICACIÓ.

Veiem que amb l'ajuda de fórmules d'Excel, no només podeu automatitzar els càlculs matemàtics. Això és només una gota a l'oceà, però de fet us permeten automatitzar gairebé qualsevol operació que us vingui al cap.

Gràfics dinàmics en Excel

Així, en l'últim pas, vam poder crear un rang dinàmic, la mida del qual depèn completament de quantes cel·les plenes conté. Ara, a partir d'aquestes dades, podeu crear gràfics dinàmics que canviaran automàticament tan bon punt l'usuari faci algun canvi o afegeixi una columna o fila addicional. La seqüència d'accions en aquest cas és la següent:

1. Seleccionem el nostre rang, després del qual inserim un gràfic del tipus "Histograma amb agrupació". Podeu trobar aquest element a la secció "Insereix" a la secció "Gràfics - Histograma".
2. Fem un clic esquerre del ratolí sobre una columna aleatòria de l'histograma, després del qual es mostrarà la funció =SERIES() a la línia de funció. A la captura de pantalla podeu veure la fórmula detallada. 
3. Després d'això, cal fer alguns canvis a la fórmula. Heu de substituir l'interval després de "Full1!" al nom de la gamma. Això donarà lloc a la funció següent: =FILA(Full1!$B$1;;Full1!ingrés;1)
4. Ara queda afegir un nou registre a l'informe per comprovar si el gràfic s'actualitza automàticament o no.

Fem una ullada al nostre diagrama.

Resumim com ho hem fet. En el pas anterior, vam crear un rang dinàmic, la mida del qual depèn de quants elements conté. Per fer-ho, hem utilitzat una combinació de funcions VERIFICACIÓ и DISPOSICIÓ. Hem creat aquest interval amb un nom i després hem utilitzat la referència a aquest nom com a interval del nostre histograma. Quin interval específic triar com a font de dades en la primera etapa no és tan important. El més important és substituir-lo més endavant pel nom de la gamma. D'aquesta manera podeu estalviar molta memòria RAM.

Intervals anomenats i els seus usos

Parlem ara amb més detall sobre com crear correctament intervals amb nom i utilitzar-los per realitzar les tasques que s'estableixen per a l'usuari d'Excel.

Per defecte, utilitzem adreces de cel·les normals per estalviar temps. Això és útil quan necessiteu escriure un interval una o més vegades. Si s'ha d'utilitzar tot el temps o ha de ser adaptable, s'han d'utilitzar intervals amb nom. Faciliten molt la creació de fórmules i no serà tan difícil per a l'usuari analitzar fórmules complexes que inclouen un gran nombre de funcions. Descriurem alguns dels passos necessaris per crear rangs dinàmics.

Tot comença amb el nom de la cèl·lula. Per fer-ho, només cal seleccionar-lo i, a continuació, escriure el nom que necessitem al camp del seu nom. És important que sigui fàcil de recordar. Hi ha algunes restriccions a tenir en compte a l'hora de nomenar:

1. La longitud màxima és de 255 caràcters. Això és suficient per assignar un nom que el vostre cor desitja.
2. El nom no ha de contenir espais. Per tant, si conté diverses paraules, és possible separar-les mitjançant el caràcter de subratllat.

Si més endavant en altres fulls d'aquest fitxer hem de mostrar aquest valor o aplicar-lo per fer més càlculs, no cal canviar al primer full. Només podeu escriure el nom d'aquesta cel·la d'interval.

El següent pas és crear un rang amb nom. El procediment és bàsicament el mateix. Primer heu de seleccionar un interval i, a continuació, especificar-ne el nom. Després d'això, aquest nom es pot utilitzar en totes les altres operacions de dades a Excel. Per exemple, els intervals amb nom s'utilitzen sovint per definir la suma de valors.

A més, és possible crear un rang amb nom mitjançant la pestanya Fórmules mitjançant l'eina Establir nom. Després de seleccionar-lo, apareixerà una finestra on hem d'escollir un nom per al nostre rang, així com especificar l'àrea a la qual s'estendrà manualment. També podeu especificar on funcionarà aquest rang: dins d'un sol full o al llarg del llibre.

Si ja s'ha creat un rang de noms, per utilitzar-lo, hi ha un servei especial anomenat gestor de noms. Permet no només editar o afegir nous noms, sinó també esborrar-los si ja no són necessaris.

Cal tenir en compte que quan s'utilitzen intervals amb nom a les fórmules, després d'haver-los suprimit, les fórmules no es sobreescriuran automàticament amb els valors correctes. Per tant, es poden produir errors. Per tant, abans de suprimir un rang amb nom, heu d'assegurar-vos que no s'utilitza en cap de les fórmules.

Una altra manera de crear un rang amb nom és obtenir-lo d'una taula. Per fer-ho, hi ha una eina especial anomenada "Crea des de la selecció". Tal com entenem, per utilitzar-lo, primer cal seleccionar l'interval que editarem, i després establir el lloc on tenim els encapçalaments. Com a resultat, a partir d'aquestes dades, Excel processarà automàticament totes les dades i s'assignaran títols automàticament.

Si el títol conté diverses paraules, Excel les separarà automàticament amb un guió baix.

Així, vam descobrir com crear intervals de nom dinàmics i com us permeten automatitzar el treball amb grans quantitats de dades. Com podeu veure, n'hi ha prou amb utilitzar diverses funcions i les eines del programa incorporades a la funcionalitat. No hi ha res de complicat, encara que a primera vista ho pugui semblar a un principiant.