Trucs de vida d'Excel per a aquells que participen en informes i processament de dades

2024 Autora: Malcolm Clapton | [email protected]. Última modificació: 2024-01-13 00:57

En aquesta publicació, Renat Shagabutdinov, director general adjunt de Mann, Ivanov and Ferber Publishing House, comparteix alguns trucs fantàstics de la vida d'Excel. Aquests consells seran útils per a qualsevol persona implicada en diversos informes, processament de dades i creació de presentacions.

Aquest article conté tècniques senzilles per simplificar el vostre treball a Excel. Són especialment útils per a aquells que es dediquen a l'elaboració d'informes de gestió, preparen diversos informes analítics basats en descàrregues d'1C i altres informes, formen presentacions i diagrames d'ells per a la gestió. No pretenc ser una novetat absoluta: d'una forma o una altra, aquestes tècniques probablement es van parlar als fòrums o es van esmentar en articles.

Alternatives senzilles a BUSCAR V i BUSCAR HL, si els valors desitjats no es troben a la primera columna de la taula: CERCA, ÍNDEX + CERCA

Les funcions BUSCARV i BUSCAR HL només funcionen si els valors desitjats es troben a la primera columna o fila de la taula de la qual voleu obtenir dades.

En cas contrari, hi ha dues opcions:

1. Utilitzeu la funció BUSCAR.

   Té la sintaxi següent: LOOKUP (valor_cerca; vector_cerca; vector_resultat). Però perquè funcioni correctament, els valors de l'interval view_vector s'han d'ordenar en ordre ascendent:

   

2. Utilitzeu una combinació de funcions MATCH i INDEX.

   La funció MATCH retorna el número ordinal d'un element de la matriu (amb la seva ajuda podeu trobar en quina fila de la taula es troba l'element cercat), i la funció INDEX retorna un element de matriu amb un nombre donat (que descobrirem utilitzant la funció MATCH).

   

   Sintaxi de la funció:

   • CERCAR (valor_cerca; matriu_de_cerca; tipus_concordança) - per al nostre cas, necessitem un tipus de concordança "concordança exacta", que correspon al número 0.

   • INDEX (matriu; número_línia; [número_columna]). En aquest cas, no cal que especifiqueu el número de columna, ja que la matriu consta d'una fila.

Com omplir ràpidament les cel·les buides d'una llista

La tasca és omplir les cel·les de la columna amb els valors de la part superior (de manera que el tema estigui a cada fila de la taula, i no només a la primera fila del bloc de llibres sobre el tema):

Seleccioneu la columna "Assumpte", feu clic a la cinta del grup "Inici", el botó "Cerca i seleccioneu" → "Seleccioneu un grup de cel·les" → "Cèl·lules en blanc" i comenceu a introduir la fórmula (és a dir, poseu una fórmula igual). signe) i consulteu la cel·la de la part superior, simplement fent clic a la fletxa amunt del teclat. Després d'això, premeu Ctrl + Enter. Després d'això, podeu desar les dades rebudes com a valors, ja que les fórmules ja no són necessàries:

Com trobar errors en una fórmula

Càlcul d'una part separada d'una fórmula

Per entendre una fórmula complexa (en què altres funcions s'utilitzen com a arguments de funció, és a dir, algunes funcions estan imbricades en d'altres) o per trobar-hi l'origen dels errors, sovint cal calcular-ne una part. Hi ha dues maneres fàcils:

1. Per calcular part d'una fórmula just a la barra de fórmules, seleccioneu aquesta part i premeu F9:

   

   En aquest exemple, hi ha hagut un problema amb la funció SEARCH: s'hi han intercanviat arguments. És important recordar que si no cancel·leu el càlcul de la part de la funció i premeu Intro, la part calculada continuarà sent un número.

2. Feu clic al botó Calcular fórmula al grup Fórmules de la cinta:

   

   A la finestra que apareix, podeu calcular la fórmula pas a pas i determinar en quina etapa i en quina funció es produeix un error (si n'hi ha):

   

Com determinar de què depèn o es refereix una fórmula

Per determinar de quines cel·les depèn una fórmula, al grup Fórmules de la cinta, feu clic al botó Cel·les que afecten:

Les fletxes semblen indicar de què depèn el resultat del càlcul.

Si es mostra el símbol destacat a la imatge en vermell, la fórmula depèn de les cel·les d'altres fulls o d'altres llibres:

En fer-hi clic, podem veure exactament on es troben les cel·les o intervals que influeixen:

Al costat del botó "Cèl·lules d'influència" hi ha el botó "Cèl·lules dependents", que funciona de la mateixa manera: mostra fletxes des de la cel·la activa amb una fórmula fins a les cel·les que en depenen.

El botó "Elimina les fletxes", situat al mateix bloc, permet eliminar les fletxes a les cel·les d'influència, les fletxes a les cel·les dependents o els dos tipus de fletxes alhora:

Com trobar la suma (nombre, mitjana) dels valors de les cel·les de diversos fulls

Suposem que teniu diversos fulls del mateix tipus amb dades que voleu afegir, comptar o processar d'una altra manera:

Per fer-ho, a la cel·la en què voleu veure el resultat, introduïu una fórmula estàndard, per exemple SUMA (SUMA), i especifiqueu el nom del primer i l'últim full de la llista d'aquells fulls que heu de processar. l'argument, separat per dos punts:

Rebreu la suma de cel·les amb l'adreça B3 dels fulls "Data1", "Data2", "Data3":

Aquest adreçament funciona per a fulls localitzats de manera coherent … La sintaxi és la següent: = FUNCTION (first_list: last_list! Referència d'interval).

Com crear frases de plantilla automàticament

Utilitzant els principis bàsics de treballar amb text a Excel i algunes funcions senzilles, podeu preparar frases de plantilla per als informes. Diversos principis per treballar amb text:

• Concatenem el text mitjançant el signe & (el podeu substituir per la funció CONCATENAR, però això no té gaire sentit).
• El text sempre s'escriu entre cometes, les referències a cel·les amb text sempre estan sense.
• Per obtenir el caràcter de servei "cometes", utilitzeu la funció CHAR amb l'argument 32.

Un exemple de creació d'una frase de plantilla amb fórmules:

Resultat:

En aquest cas, a més de la funció CHAR (per mostrar cometes), s'utilitza la funció IF, que permet canviar el text en funció de si hi ha una tendència de vendes positiva, i la funció TEXT, que permet visualitzar la número en qualsevol format. La seva sintaxi es descriu a continuació:

TEXT (valor; format)

El format s'especifica entre cometes, com si estiguéssiu introduint un format personalitzat a la finestra Format de cel·les.

També es poden automatitzar textos més complexos. A la meva pràctica, hi havia l'automatització de comentaris llargs, però habituals, als informes de gestió en el format L'INDICATOR va baixar / augmentar XX en relació amb el pla, principalment a causa del creixement / disminució de FACTOR1 per XX, creixement / disminució de FACTOR2 per XX. YY …” amb una llista canviant de factors. Si escriviu aquests comentaris sovint i el procés d'escriure'ls es pot algoritmitzar, val la pena crear una fórmula o una macro que us estalviï almenys una part de la feina.

Com emmagatzemar dades a cada cel·la després de la concatenació

Quan fusioneu cel·les, només es conserva un valor. Excel adverteix sobre això quan s'intenta fusionar cel·les:

En conseqüència, si teníeu una fórmula en funció de cada cel·la, deixarà de funcionar després de combinar-les (error # N / A a les línies 3-4 de l'exemple):

Per combinar cel·les i encara conservar les dades de cadascuna d'elles (potser teniu una fórmula com en aquest exemple abstracte; potser voleu combinar cel·les, però conservar totes les dades per al futur o amagar-les intencionadament), fusioneu qualsevol cel·la del full., seleccioneu-los i, a continuació, utilitzeu l'ordre Format Painter per transferir el format a les cel·les que necessiteu combinar:

Com crear un pivot a partir de diverses fonts de dades

Si necessiteu crear un pivot a partir de diverses fonts de dades alhora, haureu d'afegir l'"Assistent de taula dinàmica i gràfics" a la cinta o al tauler d'accés ràpid, que té aquesta opció.

Podeu fer-ho de la següent manera: "Fitxer" → "Opcions" → "Barra d'eines d'accés ràpid" → "Totes les ordres" → "Assistent de taula dinàmica i gràfics" → "Afegeix":

Després d'això, apareixerà una icona corresponent a la cinta, fent clic a la qual crida el mateix assistent:

Quan hi feu clic, apareix un quadre de diàleg:

En ell, heu de seleccionar l'element "En diversos intervals de consolidació" i fer clic a "Següent". Al pas següent, podeu seleccionar "Crea un camp de pàgina" o "Crea camps de pàgina". Si voleu crear de manera independent un nom per a cadascuna de les fonts de dades, seleccioneu el segon element:

A la finestra següent, afegiu tots els intervals sobre la base dels quals es construirà el pivot i doneu-los noms:

Després d'això, a l'últim quadre de diàleg, especifiqueu on es col·locarà l'informe de la taula dinàmica: en un full existent o nou:

L'informe de la taula dinàmica està preparat. Al filtre "Pàgina 1", només podeu seleccionar una de les fonts de dades, si cal:

Com calcular el nombre d'ocurrències del text A en el text B ("tarifa MTS SuperMTS" - dues aparicions de l'abreviatura MTS)

En aquest exemple, la columna A conté diverses línies de text, i la nostra tasca és esbrinar quantes vegades cadascuna d'elles conté el text de cerca situat a la cel·la E1:

Per resoldre aquest problema, podeu utilitzar una fórmula complexa que consta de les funcions següents:

1. DLSTR (LEN): calcula la longitud del text, l'únic argument és el text. Exemple: DLSTR ("màquina") = 6.
2. SUBSTITUTE: substitueix un text específic d'una cadena de text per un altre. Sintaxi: SUBSTITUTE (text; text_antic; text_nou). Exemple: SUBSTITUTE (“cotxe”; “automòbil”; “”) = “mòbil”.
3. MAJÚSCULA: substitueix tots els caràcters d'una cadena per majúscules. L'únic argument és el text. Exemple: SUPERIOR (“màquina”) = “COTXE”. Necessitem aquesta funció per fer cerques que no distingeixen entre majúscules i minúscules. Després de tot, UPPER ("cotxe") = UPPER ("Màquina")

Per trobar l'ocurrència d'una cadena de text determinada en una altra, heu d'eliminar totes les seves ocurrències a l'original i comparar la longitud de la cadena resultant amb l'original:

DLSTR ("Tariff MTS Super MTS") - DLSTR ("Tariff Super") = 6

I després divideix aquesta diferència per la longitud de la cadena que estàvem buscant:

6 / DLSTR ("MTS") = 2

És exactament dues vegades que la línia "MTS" s'inclou a l'original.

Queda per escriure aquest algorisme en el llenguatge de les fórmules (anotem amb "text" el text en el qual busquem ocurrències, i amb "buscat" - aquell el nombre d'ocurrències del qual ens interessa):

= (DLSTR (text) -LSTR (SUBSTITUT (SUPER (text); UPPER (cerca), ""))) / DLSTR (cerca)

En el nostre exemple, la fórmula és així:

= (DLSTR (A2) -LSTR (SUBSTITUT (SUPER (A2), SUPERIOR ($ E $ 1), “”))) / DLSTR ($ E $ 1)