Excel life hacks tiem, kas iesaistīti pārskatu veidošanā un datu apstrādē

2024 Autors: Malcolm Clapton | [email protected]. Pēdējoreiz modificēts: 2024-01-13 02:22

Šajā ziņā Renāts Šagabutdinovs, izdevniecības Mann, Ivanov un Ferber ģenerāldirektora palīgs, dalās ar dažiem lieliskiem Excel dzīves ceļiem. Šie padomi noderēs ikvienam, kas nodarbojas ar dažādu atskaišu veidošanu, datu apstrādi un prezentāciju veidošanu.

Šajā rakstā ir sniegti vienkārši paņēmieni, kā vienkāršot darbu programmā Excel. Tie ir īpaši noderīgi tiem, kas nodarbojas ar vadības ziņošanu, sagatavo dažādus analītiskos pārskatus, pamatojoties uz lejupielādēm no 1C un citiem pārskatiem, veido no tiem prezentācijas un diagrammas vadībai. Es nepretendēju uz absolūtu jaunumu - vienā vai otrā veidā šie paņēmieni, iespējams, tika apspriesti forumos vai minēti rakstos.

Vienkāršas alternatīvas VLOOKUP un HLOOKUP, ja vēlamās vērtības nav tabulas pirmajā kolonnā: LOOKUP, INDEX + SEARCH

Funkcijas VLOOKUP un HLOOKUP darbojas tikai tad, ja vēlamās vērtības ir tabulas pirmajā kolonnā vai rindā, no kuras plānojat iegūt datus.

Pretējā gadījumā ir divas iespējas:

1. Izmantojiet funkciju LOOKUP.

   Tam ir šāda sintakse: LOOKUP (meklēšanas_vērtība; uzmeklēšanas_vektors; rezultāta_vektors). Bet, lai tas darbotos pareizi, view_vector diapazona vērtības ir jākārto augošā secībā:

   

2. Izmantojiet funkciju MATCH un INDEX kombināciju.

   Funkcija MATCH atgriež masīvā esošā elementa kārtas numuru (ar tās palīdzību var noskaidrot, kurā tabulas rindā atrodas meklētais elements), un funkcija INDEX atgriež masīva elementu ar doto skaitli (ko mēs uzzināsim izmantojot funkciju MATCH).

   

   Funkciju sintakse:

   • SEARCH (meklēšanas_vērtība; meklēšanas_masīvs; atbilstības_veids) - mūsu gadījumā mums ir nepieciešams atbilstības veids "precīza atbilstība", tas atbilst skaitlim 0.

   • INDEKSS (masīvs; rindas_numurs; [kolonnas_numurs]). Šajā gadījumā kolonnas numurs nav jānorāda, jo masīvs sastāv no vienas rindas.

Kā ātri aizpildīt tukšas šūnas sarakstā

Uzdevums ir aizpildīt šūnas kolonnā ar vērtībām augšpusē (lai tēma būtu katrā tabulas rindā, nevis tikai grāmatas bloka pirmajā rindā par tēmu):

Atlasiet kolonnu "Tēma", noklikšķiniet uz lentes grupā "Sākums", noklikšķiniet uz pogas "Atrast un atlasīt" → "Atlasīt šūnu grupu" → "Tukšas šūnas" un sāciet ievadīt formulu (tas ir, ievietojiet vienādu zīme) un skatiet augšpusē esošo šūnu, vienkārši noklikšķinot uz tastatūras uz augšupvērstās bultiņas. Pēc tam nospiediet Ctrl + Enter. Pēc tam jūs varat saglabāt saņemtos datus kā vērtības, jo formulas vairs nav vajadzīgas:

Kā atrast kļūdas formulā

Formulas atsevišķas daļas aprēķins

Lai saprastu sarežģītu formulu (kurā citas funkcijas tiek izmantotas kā funkciju argumenti, tas ir, dažas funkcijas ir ligzdotas citās) vai tajā atrastu kļūdu avotu, bieži ir jāaprēķina daļa no tās. Ir divi vienkārši veidi:

1. Lai aprēķinātu daļu formulas tieši formulas joslā, atlasiet šo daļu un nospiediet taustiņu F9:

   

   Šajā piemērā radās problēma ar funkciju SEARCH – tajā tika apmainīti argumenti. Svarīgi atcerēties, ka, neatceļot funkcijas daļas aprēķinu un nospiežot Enter, tad aprēķinātā daļa paliks kā skaitlis.

2. Lentes grupā Formulas noklikšķiniet uz pogas Aprēķināt formulu:

   

   Parādītajā logā varat soli pa solim aprēķināt formulu un noteikt, kurā posmā un kurā funkcijā rodas kļūda (ja tāda ir):

   

Kā noteikt, no kā formula ir atkarīga vai uz ko attiecas

Lai noteiktu, no kurām šūnām formula ir atkarīga, lentes grupā Formulas noklikšķiniet uz pogas Ietekmējošās šūnas:

Parādās bultiņas, kas norāda, no kā ir atkarīgs aprēķina rezultāts.

Ja tiek parādīts attēlā sarkanā krāsā iezīmētais simbols, tad formula ir atkarīga no šūnām citās lapās vai citās grāmatās:

Noklikšķinot uz tā, mēs varam redzēt, kur tieši atrodas ietekmējošās šūnas vai diapazoni:

Blakus pogai "Ietekmēt šūnas" atrodas poga "Atkarīgās šūnas", kas darbojas tāpat: tā parāda bultiņas no aktīvās šūnas ar formulu uz šūnām, kas ir atkarīgas no tās.

Poga "Noņemt bultiņas", kas atrodas tajā pašā blokā, ļauj noņemt bultiņas uz ietekmējošām šūnām, bultiņas uz atkarīgajām šūnām vai abu veidu bultiņas vienlaikus:

Kā atrast šūnu vērtību summu (skaitu, vidējo) no vairākām lapām

Pieņemsim, ka jums ir vairākas viena veida lapas ar datiem, kurus vēlaties pievienot, saskaitīt vai apstrādāt kādā citā veidā.

Lai to izdarītu, šūnā, kurā vēlaties redzēt rezultātu, ievadiet standarta formulu, piemēram, SUM (SUM), un norādiet pirmās un pēdējās lapas nosaukumu no to lapu saraksta, kuras jums jāapstrādā. arguments, atdalīts ar kolu:

Šūnu summu ar adresi B3 saņemsiet no lapām "Data1", "Data2", "Data3":

Šī adresēšana darbojas loksnēm, kas atrodas konsekventi … Sintakse ir šāda: = FUNCTION (first_list: last_list! Diapazona atsauce).

Kā automātiski izveidot veidņu frāzes

Izmantojot pamatprincipus darbam ar tekstu programmā Excel un dažas vienkāršas funkcijas, varat sagatavot pārskatu veidņu frāzes. Vairāki principi darbam ar tekstu:

• Mēs savienojam tekstu, izmantojot zīmi & (to var aizstāt ar funkciju CONCATENATE, taču tam nav lielas jēgas).
• Teksts vienmēr ir rakstīts pēdiņās, atsauces uz šūnām ar tekstu vienmēr ir bez.
• Lai iegūtu pakalpojuma rakstzīmi "pēdiņās", izmantojiet funkciju CHAR ar argumentu 32.

Veidnes frāzes izveides piemērs, izmantojot formulas:

Rezultāts:

Šajā gadījumā papildus funkcijai CHAR (pēdiņu attēlošanai) tiek izmantota funkcija IF, kas ļauj mainīt tekstu atkarībā no tā, vai ir pozitīva pārdošanas tendence, un funkcija TEXT, kas ļauj parādīt numurs jebkurā formātā. Tās sintakse ir aprakstīta tālāk:

TEKSTS (vērtība; formāts)

Formāts ir norādīts pēdiņās, tāpat kā logā Format Cells ievadāt pielāgotu formātu.

Sarežģītākus tekstus var arī automatizēt. Manā praksē bija automatizēti gari, bet ierasti komentāri vadības ziņojumiem formātā “INDIKATORS samazinājās/paaugstinājās par XX attiecībā pret plānu, galvenokārt FACTOR1 pieauguma/samazinājuma dēļ par XX, FACTOR2 pieauguma/samazinājuma dēļ par YY …” ar mainīgu faktoru sarakstu. Ja šādus komentārus rakstāt bieži un to rakstīšanas process var tikt algoritmizēts, ir vērts vienreiz mulsinoši izveidot formulu vai makro, kas ietaupīs vismaz daļu darba.

Kā saglabāt datus katrā šūnā pēc savienošanas

Apvienojot šūnas, tiek saglabāta tikai viena vērtība. Excel brīdina par to, mēģinot sapludināt šūnas:

Attiecīgi, ja jums bija formula atkarībā no katras šūnas, tā pārtrauks darboties pēc to apvienošanas (# N / A kļūda piemēra 3.–4. rindiņā):

Lai sapludinātu šūnas un joprojām saglabātu datus katrā no tām (iespējams, jums ir tāda formula kā šajā abstraktajā piemērā; iespējams, vēlaties sapludināt šūnas, bet paturēt visus datus nākotnē vai apzināti tos paslēpt), sapludiniet visas lapas šūnas., atlasiet tos un pēc tam izmantojiet komandu Format Painter, lai pārsūtītu formatējumu uz šūnām, kuras ir jāapvieno:

Kā izveidot rakursu no vairākiem datu avotiem

Ja jums ir nepieciešams izveidot rakurstaciju no vairākiem datu avotiem vienlaikus, lentei vai ātrās piekļuves panelim būs jāpievieno "Rakurstabulas un diagrammu vednis", kuram ir šāda iespēja.

To var izdarīt šādi: "Fails" → "Opcijas" → "Ātrās piekļuves rīkjosla" → "Visas komandas" → "Rakurstabulas un diagrammu vednis" → "Pievienot":

Pēc tam lentē parādīsies atbilstoša ikona, noklikšķinot uz kuras izsauc to pašu vedni:

Noklikšķinot uz tā, tiek parādīts dialoglodziņš:

Tajā jums jāizvēlas vienums "Vairākos konsolidācijas diapazonos" un noklikšķiniet uz "Tālāk". Nākamajā darbībā varat izvēlēties "Izveidot vienas lapas lauku" vai "Izveidot lapas laukus". Ja vēlaties patstāvīgi izdomāt nosaukumu katram datu avotam, atlasiet otro vienumu:

Nākamajā logā pievienojiet visus diapazonus, uz kuru pamata tiks veidots šarnīrs, un piešķiriet tiem nosaukumus:

Pēc tam pēdējā dialoglodziņā norādiet, kur tiks ievietots rakurstabulas pārskats - esošajā vai jaunā lapā:

Rakurstabulas pārskats ir gatavs. Filtrā "1. lapa" varat atlasīt tikai vienu no datu avotiem, ja nepieciešams:

Kā aprēķināt teksta A gadījumu skaitu tekstā B ("MTS SuperMTS tarifs" - divi saīsinājuma MTS gadījumi)

Šajā piemērā A kolonnā ir vairākas teksta rindiņas, un mūsu uzdevums ir noskaidrot, cik reizes katrā no tām ir šūnā E1 esošais meklēšanas teksts:

Lai atrisinātu šo problēmu, varat izmantot sarežģītu formulu, kas sastāv no šādām funkcijām:

1. DLSTR (LEN) - aprēķina teksta garumu, vienīgais arguments ir teksts. Piemērs: DLSTR ("mašīna") = 6.
2. SUBSTITUTE - aizstāj noteiktu tekstu teksta virknē ar citu. Sintakse: SUBSTITUTE (teksts; vecais_teksts; jauns_teksts). Piemērs: SUBSTITUTE (“automašīna”; “auto”; “”) = “mobilais”.
3. UPPER - visas virknes rakstzīmes aizstāj ar lielajiem burtiem. Vienīgais arguments ir teksts. Piemērs: UPPER (“mašīna”) = “CAR”. Šī funkcija ir nepieciešama, lai veiktu meklēšanu bez reģistrjutīgajiem. Galu galā, UPPER ("auto") = UPPER ("Mašīna")

Lai atrastu noteiktas teksta virknes sastopamību citā, jums ir jāizdzēš visi tās gadījumi sākotnējā virknē un jāsalīdzina iegūtās virknes garums ar sākotnējo:

DLSTR (“Tariff MTS Super MTS”) - DLSTR (“Super tarifs”) = 6

Un pēc tam sadaliet šo starpību ar meklētās virknes garumu:

6 / DLSTR (“MTS”) = 2

Tieši divas reizes līnija "MTS" ir iekļauta oriģinālajā.

Atliek uzrakstīt šo algoritmu formulu valodā (ar “tekstu” apzīmēsim tekstu, kurā meklējam gadījumus, un ar “meklēto” – to, kura atkārtojumu skaits mūs interesē):

= (DLSTR (teksts) -LSTR (AIZSTĀTĀJS (AUGŠĒJĀ (teksts); AUGŠĒJĀ) (meklēšana), ""))) / DLSTR (meklēšana)

Mūsu piemērā formula izskatās šādi:

= (DLSTR (A2) -LSTR (AIZSTĀTĀJS (AUGŠĒJĀ (A2), AUGŠĒJĀ ($ E $ 1), ""))) / DLSTR ($ E $ 1)