Excel dzīves hakeru tiem, kas iesaistīti ziņošanas un datu apstrādes

Renāts nav pirmā reize, kad viesu autors runā par Layfhakere. Iepriekš mēs publicējusi lielisku materiālu, no viņa par to, kā izveidot mācību plānu: galvenā grāmata un tiešsaistes resursi, kā arī papildu algoritms izveidot mācību plānu.

Šajā rakstā ir dažus vienkāršus paņēmienus, kas palīdz vienkāršot darbu Excel. tie ir īpaši noderīga tiem, kas iesaistīti vadības ziņojumu, sagatavo dažādas analīzes pārskatus, pamatojoties uz izkrāvumu 1C ziņojumiem un citiem šo prezentāciju un diagrammas vadība. Vai nav izlikties būt absolūts jaunums - vienā vai otrā veidā, šīs metodes noteikti apspriests forumos vai pantā minēto.

Vienkārša alternatīva VLOOKUP un HLOOKUP, ja nepieciešamās vērtības nav pirmajā ailē tabulas: View, INDEX + MATCH

VLOOKUP (VLOOKUP) Un AAR (HLOOKUP) tikai darbs, ja mērķa vērtības ir pirmajā kolonnā vai rindā tabulas, no kuras jūs vēlaties iegūt datus.

Citos gadījumos, ir divas iespējas:

1. Izmantot VIEW funkcija (LOOKUP).
   Viņa ir šādu sintaksi: skatoties (uzmeklējamā_vērtība Meklēt vektors; vektor_rezultata
   instagram viewer
   ). Bet, lai tā darbotos pareizi, jums ir nepieciešams, lai diapazona vērtībām Meklēt vektors Tie tika sakārtoti augošā secībā:
2. MATCH izmantot kombināciju funkcijas (MATCH) un indeksu (indekss).
   MATCH funkcija atgriež sērijas numuru elementu masīvā (ar tās palīdzību jūs varat atrast, pēc kārtas tabulas vēlamais elements), un funkcija INDEX atgriež masīva elements ar iepriekš noteiktu skaitu (kas mēs zina funkcionēt MATCH).Sintakse iespējas:
   • MATCH (uzmeklējamā_vērtība massiv_poiska; MATCH_TYPE) - mūsu gadījumā mums ir nepieciešama sava veida salīdzināšanas "precīzo atbilstību", tā atbilst skaitam 0.
   • INDEX (masīvs; rindas_numurs; [COLUMN_NUM]). Šajā gadījumā tas nav nepieciešams norādīt kolonnas numuru, kā masīvs sastāv no vienas līnijas.

Cik ātri aizpildīt tukšās šūnas sarakstā

Uzdevums - aizpildīt šūnas ar vērtībām kolonnā iepriekš (ievērojot stāv katrā rindā tabulas, ne tikai pirmajā bloka rindā grāmatas par šo tēmu):

Izvēlieties kolonnu "Tēma", nospiediet to kasetē grupas "Home" pogas "Atrast un izvēlieties» → «Izcelt šūnu grupu» → «Blank šūnas "un sākt ievadīt formulu (ti, likt vienādības zīmi), un attiecas uz šūnu no augšas, vienkārši nospiežot augšupvērstās bultiņas tastatūra. Tad nospiediet Ctrl + Enter. Pēc tam, dati var tikt saglabāti kā vērtību, jo formulas ir vairs nav vajadzīgi:

Kā atrast kļūdas formulu

Aprēķināšana atsevišķa daļa formula

Lai saprastu sarežģītu formulu (kurā kā funkciju argumentus, izmantojot citas funkcija, tas ir, dažas funkcijas ir iestrādāta citu), vai arī, lai atrastu tajā avots kļūdas, bieži vien ir nepieciešams, lai aprēķinātu daļa no tā. Ir divi vienkārši veidi:

1. Lai aprēķinātu labo daļu formulu formulu joslā, izvēlieties daļu, un nospiediet F9:
   
   Šajā piemērā, tur bija problēma ar funkciju SEARCH (meklēšana) - tur bija sajaukti argumenti. Ir svarīgi atcerēties, ka, ja jums nav atcelt aprēķinu par funkciju un nospiediet Enter, tad aprēķinātais daļa paliks numurs.
2. Nospiediet "Aprēķināt formulu" grupas "formula", uz lentes:
   
   
   Logā, kas parādās, jūs varat aprēķināt formulas soli pa solim, un, lai noteiktu, kādā stadijā un kādā funkcija kļūda (ja tāds ir):
   

Kā noteikt, kas nosaka, ka formula vai atsauce

Lai noteiktu, kuri no šūnām atkarīgs formulu grupā ar "formula", ar lenti, noklikšķiniet uz pogas "Trace precedentus":

Ir bultas, kas norāda, no kuras rezultāts ir atkarīgs.

Ja simbols parādās uz izvēlēto attēlu sarkanā, formula ir atkarīga no šūnām, kas ir citos sarakstos vai citas grāmatas:

Uzklikšķinot uz tā, mēs varam redzēt, kur tieši ietekme uz šūnu vai diapazonu:

Blakus "Trace precedentiem" ir poga "apgādājamie", kas strādā tāpat: tas parāda bultiņām no aktīvās šūnas ar formulu, lai šūnas, kas ir atkarīgi no tā.

Poga "Noņemt bultas", kas atrodas tajā pašā ēkā, ļauj, lai novērstu precedentus, apgādājamo, vai abu veidu uzreiz bultām:

Kā atrast summu (skaitlis, vidējais) vērtības šūnas ar vairākām lapām

Pieņemsim, ka jums ir vairākas līdzīgas lapas ar datiem, ko vēlaties pievienot, atrast vai pārstrādāti kādā citā veidā:

Lai to izdarītu, šūnu, kurā vēlaties redzēt rezultātu, ievadiet standarta formulu, piemēram, SUM (summa), un ievadiet argumentu seko kols un uzvārda pirmo lapu no saraksta lapu jums nepieciešams process:

Jūs saņemsiet summu, šūnu adreses B3 ar lapas "data1", "dati 2", "data3":

Šādi risinot darbi lokšņu sakārtots kārtas. Sintakse ir: feature = (pervyy_list: posledniy_list atsauce uz diapazonu!).

Kā automātiski veidot šablonisks frāzes

Izmantojot pamatprincipus, strādājot ar tekstu Excel un dažiem vienkāršiem funkcijas, var pagatavot šablonisks frāzes ziņojumiem. Vairāki principi darbam ar tekstu:

• Apvienot tekstu ar preču zīmi & (var aizstāt savu funkciju join (join), bet tas nav daudz).
• Teksts ir vienmēr rakstīts pēdiņās, šūnu atsauces ar tekstu - ne vienmēr.
• Lai iegūtu īpašo apzīmējumu "pēdiņas", izmantojot raksturs funkcija (CHAR) argumentu 32.

PIEMĒRS veidojot veidņu frāzes, izmantojot formulas:

rezultāts:

Šajā gadījumā papildus funkcijas simbola (CHAR), (lai parādītu citātus), izmantojot funkciju IF (IF), kas ļauj mainīt tekstu Atkarībā no tā, vai ir pozitīva tendence pārdošanas un funkcija teksta (teksts), kas ļauj parādīt vairākus jebkurā formātā. Tās sintakse ir aprakstīts zemāk:

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

Formāts ir norādīts pēdiņās tādā pašā veidā kā tad, ja esat ievadījis pielāgotu formātu "Format Cells".

Jūs varat automatizēt sarežģītākus tekstus. Manā praksē bija automatizēt garš, bet ikdienas piezīmes par vadības kontiem formātā "skaitlis samazinājās / roze pie XX par plānu, galvenokārt sakarā ar pieaugumu / samazinājumu FAKTORA1 XX, pieaugums / samazinājums FAKTORA2 YY... »ar mainīgo sarakstu faktori. Ja jūs rakstīt šādus komentārus bieži un process rakstiski var algoritmizācija - vērts reiz neizpratnē izveidotu formulu vai makro, kas ietaupīs jums no vismaz daļu no darba.

Kā saglabāt datus katrā šūnā pēc apvienošanās

Kombinējot šūnas saglabātas tikai vienu vērtību. Excel brīdina, ja jūs mēģināt apvienot šūnas:

Attiecīgi, ja jums bija formula, kas ir atkarīgs no katrā šūnā, tas vairs darbā pēc to Savienības (bug # N / A līnijas 3-4 piemēri):

Apvienot šūnas, saglabājot datus katrā no tiem (varbūt jums ir formula, kā šajā piemērā, abstrakti; jūs varētu vēlēties apvienot šūnas, bet saglabāt visus datus nākotnē, vai slēpt savas ieceres), apvienot jebkuru šūnu uz loksne, atlasiet tos, un pēc tam izmantot komandu "Format Painter" pārsūtīšanas formātu uz šūnām, kas jums ir nepieciešams, un apvienot:

Kā veidot kopsavilkumu vairākiem datu avotiem

Ja jums ir nepieciešams, lai izveidotu konsolidēti no vairākiem datu avotiem, ir nepieciešams pievienot lentes vai ātrās piekļuves rīkjoslai "PivotTable Wizard diagrammas", kurā pastāv šāda iespēja.

To var izdarīt šādi: "File» → «Uzstādījumi» → «Ātrās piekļuves rīkjosla» → «Visa komanda» → «PivotTable Wizard un diagrammas» → «Add»:

Pēc tam, lentas būs atbilstošas ​​ikona, noklikšķinot uz kuru izraisa pievienošanu kapteiņa:

Kad jūs noklikšķiniet uz tā, dialoglodziņu:

Tajā jums būs nepieciešams, lai izvēlētos "A maz joslas konsolidāciju" un noklikšķiniet uz "Next". Nākamajā sadaļā jūs varat izvēlēties "Izveidot vienotu lapas lauku" vai "Izveidot lapu lauku." Ja vēlaties sevi nākt klajā ar nosaukumu, par katru datu avotu - lai izvēlētos otro vienību:

Nākamajā lodziņā pievienot visus diapazonus, pamatojoties uz kuriem, lai izveidotu kopsavilkumu, un lūgt tos vārdus:

Pēc šī punkta, uzņems PivotTable ziņojumu pēdējo dialoglodziņā - uz esošu vai jaunu darblapu:

Ziņot PivotTable gatavs. Filtrs "Page 1", jūs varat izvēlēties tikai vienu no datu avotu, ja tas nepieciešams:

Kā aprēķināt atkārtojumu skaitu tekstu tekstā A B ( «MTS Tarifu SuperMTS" - divi gadījumi; saīsinājumu MTS)

Šajā piemērā A slejā, ir dažas rindiņas teksta, un mūsu uzdevums - noskaidrot, cik reižu katrs no tiem atbilst nepieciešamo tekstu šūnā E1:

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

1. LEN (LEN) - aprēķina garumu teksta, viens arguments - tekstu. Piemērs: LEN ( "mašīna") = 6.
2. SUBSTITUTE (SUBSTITUTE) - aizstāj teksta virkne zināmu tekstu ar citu. Syntax: SUBSTITUTE (tekstu; star_tekst; nov_tekst). Piemērs: SUBSTITUTE ( "automašīna", "auto", "") = "mobilais".
3. UPPER (UPPER) - aizstāj visas rakstzīmes virknē ar lielajiem burtiem. Vienīgais arguments - teksts. Piemērs: UPPER ( "mašīna") = "MACHINE". būs nepieciešams šo funkciju, lai padarītu meklēšanu reģistrjutīgi. Galu galā, UPPER ( "Machine") = UPPER ( "Machine")

Lai atrastu noteiktu ierakstu no teksta virknes uz citu, jums ir noņemt visas tās atgadījumiem oriģinālu un salīdzināt garumu virknes no oriģinālā:

LEN ( "Tarifu Super MTS MTS") - LEN ( "Super Rate") = 6

Un tad sadalīt starpību pēc garuma līniju, mēs meklējām:

6 / len ( "MTS") = 2

Tas ir divreiz line "MTS" ir oriģināla.

Tā joprojām ir jāraksta valodā algoritmu formulām (apzīmē "teksts" teksts, kuru mēs meklējam, ierakstu un "vēlamo" - skaits, atkārtojumu, ka mēs esam ieinteresēti):

= (LEN (teksts) -DLSTR (SUBSTITUTE (UPPER (teksts); UPPER (pieprasīts, lai); ""))) / Len (pieprasīts, lai)

Mūsu Piemēram, formula ir šāda:

= (LEN (A2) -DLSTR (SUBSTITUTE (UPPER (A2); UPPER ($ E $ 1); ""))) / len ($ E $ 1)