Formare avansată excel. Lucrul în Excel pentru utilizatori avansați

Colegi!

Necesitatea automatizării calculelor, ținând cont sumă uriașă sursă de date asociate cu my activitate profesională, a manifestat un mare interes pentru învăţarea Excel, ca programe cu capabilități enorme.

Există o mulțime de informații pe Internet, traininguri online, webinarii și mijloace didactice. Voi fi sincer: cel mai eficient pentru mine a fost să studiez cursul „Advanced MS Excel”, care s-a dovedit a fi de înțeles chiar și pentru un „amator”, concis, care conținea o cantitate imensă de material bine organizat pentru studiu. Separat, aș dori să spun despre predarea lecțiilor de către autor: aceasta este o prezentare atentă, inteligibilă, ușor de auzit a materialului de curs.

Este dificil să precizez exact cum a ajutat cursul, deoarece fiecare dintre cele 6 module a avut o contribuție pozitivă la munca mea: în efectuarea de calcule complexe, consolidarea fișierelor, tabele pivot, formatare etc.

Vă mulțumim pentru o excursie minunată în spațiile și posibilitățile nesfârșite programe Excel. În plus, vă mulțumesc pentru livrarea constantă, utilă și discretă de exemple, lecții și trucuri pentru studierea și repetarea materialului pe care l-ați învățat deja. Repetiția este mama învățării!

Îți doresc succes, Dmitry, din suflet!

Șef departament POS al unei companii de logistică

În lecțiile video autorul limbaj accesibil prezintă materiale de curs folosind specifice exemple ilustrative din practica de zi cu zi. Un avantaj incontestabil Cursul este interactiv - autorul verifică personal temele finalizate; dacă este necesar, îl puteți contacta cu întrebări clarificatoare sau puteți lăsa un mesaj pe forum. Chiar dacă sunt suficient utilizator experimentat Excel, în materialele de curs le-am descoperit cu surprindere nedisimulata întreaga linie tehnici practice pe care le folosesc acum în munca mea. Unele tehnici sunt atât de eficiente încât pot economisi ore și chiar zile de muncă! În plus, pe parcursul cursului am învățat noi diagrame de care nici măcar nu auzisem până acum (cascada, pâlnia de vânzări, Pareto etc.).

Analist

Advanced MS Excel este un curs util pentru cei care doresc să-și simplifice munca și să automatizeze multe procese într-un timp scurt. Foarte format convenabil antrenament: video + atasat fisier Excel, în care poți repeta noile cunoștințe acumulate în practică. Videoclipurile sunt mici (le puteți viziona în timpul prânzului la serviciu), dar prezintă informațiile concis și clar.

Am găsit modulul tabel pivotant deosebit de util. În ciuda faptului că aveam deja experiență de lucru cu ei, am descoperit multe Informatii utile: folosind slice, crearea de tabele rezumative pentru clienți foi separate, aplicarea formatării condiționate unui tabel pivot, grupare, sortare, tabele pivot etc.

Separat, aș dori să notez lecția „Model de date (pivot de putere redusă): înainte de a studia această lecție, am petrecut mult timp trăgând date dintr-un tabel în altul folosind funcțiile CĂUTARE V sau SUMIF. Acum pot să construiesc masă rotativă, care este construit din două sau mai multe tabele sursă.

Lecțiile despre formatarea condiționată au fost de asemenea utile: înainte de a viziona aceste lecții, am folosit doar propria mea formulă pentru a determina ce celule să formatez. Acest lucru nu a fost întotdeauna convenabil, deoarece, după cum s-a dovedit, de multe ori a trebuit să reinventăm roata. Acum, înainte de a-mi scrie propria formulă, mă uit soluții gata făcute formatare condiționată și adesea găsesc acolo ceea ce am nevoie.

Un curs excelent pentru cei care doresc să-și îmbunătățească cunoștințele despre MS Excel și să le reîmprospăteze pe cele existente.

Specialist lider în achiziții

Microsoft Excel, poate cea mai comună program de birou. Este folosit de toată lumea, de la președinte până la școlar, așa că stăpânind cu măiestrie acest instrument, veți putea să vă remarcați printre angajații de birou sau să vă ușurați munca de zi cu zi.

Pe lângă multe funcții simple, pe care probabil îl cunoașteți și îl folosiți deja, este util să învățați câteva trucuri „pentru cool” care vă vor face cu un ordin de mărime mai mare decât toți ceilalți. Așadar, dacă doriți să vă prezentați în fața șefului dvs. și să vă zdrobiți concurenții, aceste 8 funcții Excel complicate vă vor fi utile.

1. Suma 3D

Acum pe fila TOTAL trebuie să vedem cât și în ce zi ai petrecut în această perioadă. Introducem =SUM(‘Săptămâna1:Săptămâna7’!B2) și formula însumează toate valorile din celula B2 din toate file-urile. Acum, după ce am completat toate celulele, am aflat în ce zi a săptămânii am petrecut cel mai mult și, în cele din urmă, ne-am adunat toate cheltuielile pentru aceste 7 săptămâni.

1. Funcția MATCH

Funcția MATCH caută un element specificat dintr-un interval (Intervalul este două sau mai multe celule din foaia de lucru. Celulele dintr-un interval pot fi fie adiacente, fie neadiacente) celule și reflectă poziția relativă a acelui element în interval.

Separat, INDEX și MATCH nu sunt deosebit de utile. Dar împreună pot înlocui funcția VLOOKUP.

De exemplu, pentru a masă mare pentru a afla cine este șeful Wells Fargo, scrieți =INDEX(A3:A11,MATCH("Wells Fargo",B3:B11,0).
Prin utilizarea Funcțiile CĂUTARE V acest lucru nu se poate face deoarece caută doar de la stânga la dreapta. Iar combinația ultimelor două vă permite să faceți acest lucru cu ușurință.

Una dintre cele mai funcții convenabileîn arsenalul Excel și, de asemenea, unul dintre cele mai simple este semnul $. Îi spune programului ce să nu facă reglare automată formule atunci când sunt copiate în celulă nouă, așa cum face de obicei Excel.

În acest caz, semnul $ în fața lui „A” împiedică programul să schimbe formula pe orizontală, iar în fața lui „1” – pe verticală. Dacă scrieți „$A$1”, atunci valoarea celulei copiate va fi aceeași în orice direcție. Foarte recepție convenabilă când trebuie să lucrezi cu baze de date mari date.

1. Selectarea parametrilor

Fără asta Funcții Excel legiuni de analiști, consultanți și prognozatori ar avea dificultăți. Întrebați pe oricine din consultanță sau vânzări și vă vor spune cât de utilă poate fi această funcție Excel.

De exemplu, vindeți un nou joc video și trebuie să aflați câte copii trebuie să vândă managerii dvs. în a treia lună pentru a câștiga 100 de milioane de dolari. Pentru a face acest lucru, în meniul „Instrumente”, selectați funcția „Selectare parametri”. Dorim ca celula Venituri totale să arate o valoare de 100 de milioane USD. În câmpul set cell, indicați celula în care valoare totală, în câmpul to value – suma dorită, iar în celula Modificare, selectați celula în care va fi afișat numărul de mărfuri vândute în a treia lună. Și - voila! – programul a făcut față. Parametrii și valorile din celule pot fi ușor manipulate pentru a obține rezultatul dorit.

1. Funcția VLOOKUP

Această funcție vă permite să găsiți rapid valoarea de care aveți nevoie în tabel. De exemplu, trebuie să știm scorul final al lui Bette, scriem: =VLOOKUP(„Beth”,A2:E6,5,0), unde Beth este numele elevului, A2:E6 este intervalul tabelului, 5 este numărul coloanei , iar 0 înseamnă că nu căutăm o potrivire exactă cu sensul.

Funcția este foarte convenabilă, dar trebuie să cunoașteți câteva caracteristici ale utilizării sale. În primul rând, VLOOKUP caută doar de la stânga la dreapta, așa că dacă trebuie să căutați într-o altă ordine, va trebui să modificați setările de sortare ale întregii foi. De asemenea, dacă alegi și tu masa mare, căutarea poate dura mult timp.

Dacă doriți să colectați toate valorile de la celule diferiteîntr-unul singur, puteți utiliza funcția CONCATENARE. Dar de ce să tastați atât de multe litere când le puteți înlocui cu „&”.

1. Matrice

Pentru a crea o matrice sau o matrice, veți avea nevoie de mai multe operații, dar acestea sunt mai complicate decât cu formulele convenționale, deoarece afișarea rezultatului necesită nu una, ci mai multe celule.

De exemplu, să înmulțim două matrici. Pentru a face acest lucru, utilizați funcția MULTIPLE (Matrice 1, Matrice 2). Principalul lucru este că nu uitați să închideți formula paranteze. Acum apăsați combinația de taste Ctrl+Shift+Enter, iar Excel va afișa rezultatul înmulțirii sub forma unei matrice. Același lucru este valabil și pentru alte funcții care funcționează cu matrice - în loc să apăsați Enter pentru a obține rezultatul, utilizați Ctrl+Shift+Enter.

1. Funcția INDEX

Reflectă valoarea sau referința la celulă la intersecția unui anumit rând și coloană dintr-un interval selectat de celule. De exemplu, pentru a vedea cine este al patrulea pe lista celor mai bine plătiți manageri de top de pe Wall Street, introducem: =INDEX(A3:A11, 4).

Birou incorporat produs Microsoft Excel este convenabil pentru crearea de baze de date și menținerea înregistrărilor operaționale. Suplimentele de program oferă utilizatorului posibilitatea ca utilizatorii avansați să automatizeze munca și să elimine erorile folosind macrocomenzi.

ÎN această recenzie considera caracteristici utile Programe Excel pe care utilizatorii avansați le folosesc pentru a le rezolva diverse sarcini. Vom învăța cum să lucrăm cu o bază de date în Excel. Să învățăm cum să folosim macrocomenzi în practică. Vom lua în considerare, de asemenea, utilizarea partajării documentelor pentru lucru în comun (cu mai mulți utilizatori).

Cum se lucrează cu o bază de date în Excel

O bază de date (DB) este un tabel cu un anumit set informatii (baza de date clienti, inventarul depozitului, contabilitatea veniturilor si cheltuielilor etc.). Această formă de reprezentare este convenabilă pentru sortarea după parametru, cautare rapida, calcularea valorilor după anumite criterii etc.

De exemplu, să creăm o bază de date în Excel.

Informații introduse manual. Apoi am evidențiat intervalul de date și l-am formatat „ca un tabel”. A fost posibil să setați mai întâi un interval pentru baza de date („Inserare” - „Tabel”). Și apoi introduceți datele.

Găsiți informațiile de care aveți nevoie în baza de date

Selectați Meniul principal – fila „Editare” – „Găsiți” (binoclu). Sau apăsați combinația de taste rapide Tastele Shift+ F5 sau Ctrl + F. În bara de căutare, introduceți valoarea dorită. Prin utilizarea a acestui instrument Puteți înlocui un nume de valoare din întreaga bază de date cu altul.

Să sortăm valori similare în baza de date

Baza noastră de date este compilată pe principiul unui „tabel inteligent” - există o săgeată în colțul din dreapta jos al fiecărui element de antet. Poate fi folosit pentru a sorta valori.

Sa afisam marfa care se afla in depozitul nr.3. Faceți clic pe săgeata din colțul numelui „Depozit”. Selectați valoarea dorită din lista verticală. După ce facem clic pe OK, avem acces la informațiile din depozitul nr. 3. Doar daca.

Să aflăm ce produse costă mai puțin de 100 de ruble. Faceți clic pe săgeata de lângă „Preț”. Selectați „Filtre numerice” - „Mai puțin sau egal cu”.

Setați parametrii de sortare.

După ce faceți clic pe OK:

Notă. Folosind un filtru automat personalizat, puteți seta simultan mai multe condiții pentru sortarea datelor în baza de date.

Să găsim rezultate intermediare

Să calculăm costul total al mărfurilor în depozitul nr. 3.

Folosind un filtru automat, vom afișa informații despre la acest depozit(Vezi deasupra).

Sub coloana „Cost”, introduceți formula: = TOTAL INTERMEDIAR(9;E4:E41), unde 9 este numărul funcției (în exemplul nostru, SUM), E4:E41 este intervalul de valori.

Observați săgeata de lângă rezultatul formulei:

Poate fi folosit pentru a schimba funcția SUM.

Care este frumusetea aceasta metoda: daca schimbam depozitul vom obtine o noua valoare totala (in functie de noua gama). Formula rămâne aceeași - tocmai am schimbat parametrii filtrului automat.

Cum se lucrează cu macrocomenzi în Excel

Macro-urile sunt concepute pentru automatizare muncă de rutină. Acestea sunt instrucțiuni care comunică cursul de acțiune pentru atingerea unui obiectiv specific.

Există multe macrocomenzi în acces deschis. Acestea pot fi copiate și lipite în registrul de lucru (atâta timp cât instrucțiunile își îndeplinesc scopul propus). Să ne uităm la exemplu simplu Cum să înregistrați singur o macrocomandă.

Să creăm o instrucțiune care va calcula automat costul unei comenzi de client:

1. Să copiem tabelul într-o foaie nouă.
2. Să eliminăm datele de cantitate. Dar să ne asigurăm că aceste celule au un format numeric fără zecimale (deoarece este posibil să comandați mărfuri individual, nu în unități de masă).
3. Valoarea „Preț” trebuie să fie într-un format valutar.
4. Să eliminăm datele de cost. Sa introducem formula in coloana: pret * cantitate. Și ne vom înmulți.
5. În partea de jos a tabelului este „Total” (cate unități de bunuri au fost comandate și la ce cost). Chiar mai mic - „Total”.

Tabelul a luat următoarea formă:

Acum să învățăm Microsoft Excel să efectueze un anumit algoritm.

Cum să lucrezi în Excel în același timp pentru mai multe persoane

Pentru ca mai mulți utilizatori să aibă acces la o bază de date în Excel, aceasta trebuie să fie deschisă. Pentru versiunile 2007-2010: „Recenzie” - „Acces la carte”.

Notă. Pentru versiune veche 2003: „Serviciul” - „Accesul la carte”.

Dar! Dacă 2 sau mai mulți utilizatori au modificat valorile aceleiași celule în timpul accesării documentului, va apărea un conflict de acces.

Sau informațiile introduse anterior de un utilizator vor fi șterse atunci când următorul utilizator introduce o nouă valoare în aceeași celulă.

Programul nu numai că mărește productivitatea utilizatorilor, dar le urmărește și erorile!

Din păcate, există unele limitări în modul multiplayer. De exemplu:

• foile nu pot fi șterse;
• celulele nu pot fi combinate sau separate;
• creați și editați macrocomenzi;
• lucru limitat cu date XML(import, șterge hărți, converti celule în elemente etc.).

Dar în general acest mod foarte atractiv pentru munca mare și de rutină, care este mai ușor și mai rapid de finalizat împreună. Accesul partajat crește semnificativ productivitatea angajaților de la birou.

În exemplu: distribuire a unui fisier- procesul de conectare și lucru în modul multi-utilizator este descris mai detaliat.