Drepturi de autor JSC „CDB „BIBKOM” & LLC „Agenție Book-Service” Agenția Federală pentru Educație Instituție de învățământ de stat de învățământ profesional superior „Universitatea Tehnologică de Stat Kazan” Lucrări de laborator în informatică MS EXCEL Ghid Kazan 2006 Copyright JSC „CDB „BIBKOM” & SRL „Agency Book-Service” UDC 658.26:66.094 Întocmit de: Conf. univ. E.S. Vorobiev, conf. univ E.V. Nikolaeva, conf. univ F.I. Vorobyova Lucrări de laborator în informatică. MS Excel: Metodă. instrucțiuni / Kazan. stat tehn. Universitate; Comp.: E.S. Vorobyov, E.V. Nikolaeva, F.I. Vorobyova. – Kazan, 2006. – 58 p. Sunt prezentate tehnicile de bază de lucru în pachetul MS Excel, procedura și regulile de creare și editare a foilor de calcul și diagramelor, efectuarea calculelor de bază, operațiunile de sortare și filtrare, analizarea și rezumarea datelor, precum și utilizarea expresiilor logice, rezumat și sunt descrise funcţiile de distribuţie şi operaţiile cu matrice. O lucrare separată de laborator este dedicată găsirii unei soluții la o problemă cu unul și doi parametri. Ele pot fi utilizate în studiul disciplinelor „Informatică”, „Aplicarea calculatoarelor în tehnologie” și „Utilizarea calculatoarelor în calcule”, pot servi drept ghid pentru munca extracurriculară a studenților și pot fi utilizate și de specialiștii din orice domeniu pentru dezvoltarea independentă a tehnologiilor informatice. Destinat studenților cu normă întreagă și cu frecvență parțială ai specialităților 240802.65 „Procede de bază de producție chimică și cibernetică chimică” și 240801 „Mașini și aparate de producție chimică”, studenți la direcția 2480800 „Procede de economisire a energiei și a resurselor în tehnologie chimică, petrochimie și biotehnologie”. Il. 68, fila. 1, bibliogr. 5 titluri Pregătit la Departamentul de Tehnologie Chimică Generală. Publicat prin hotărâre a consiliului editorial și editorial al Universității Tehnologice de Stat din Kazan. Recenzători: B.K. Kurbatov, profesor asociat al KSTU numit după. PE. Tupoleva E.A. Mukhutdinov, profesor asociat KGEU Kazan State Technological University, 2006 Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Lucrări de laborator nr. 1 Exercițiul 1 Concepte de bază legate de funcționarea foilor de calcul Excel 1. Lansați Microsoft Excel: faceți clic pe butonul Start ; în meniul care apare, selectați Programe; Selectați Microsoft Excel din meniul pop-up. 2. Examinați cu atenție fereastra programului Microsoft Excel (Fig. 1). Multe elemente de meniu orizontale și butoane din bara de instrumente sunt identice cu elementele de meniu și butoanele din fereastra editorului Word. Cu toate acestea, spațiul de lucru are un aspect complet diferit, care este un tabel marcat format din celule de aceeași dimensiune. Una dintre celule este evidențiată clar (încadrată cu un cadru negru - un cursor de tabel). Cum se selectează o altă celulă? Pentru a face acest lucru, trebuie doar să faceți clic pe el cu mouse-ul, cu indicatorul mouse-ului în această Fig. Prima oară ar trebui să arate ca o cruce ușoară. Încercați să evidențiați diferite celule din tabel. Utilizați barele de defilare pentru a vă deplasa în jurul mesei. 3. Pentru a introduce text într-una dintre celulele tabelului, trebuie să o selectați și imediat (fără a aștepta cursorul de text în procesorul de text) „scrieți”. Selectați una dintre celulele tabelului și „scrieți” numele zilei de astăzi a săptămânii în ea. 4. Principala diferență dintre munca foilor de calcul și un procesor de text este că după introducerea datelor într-o celulă, acestea trebuie înregistrate, adică. informați programul că ați terminat de introdus informații -3- Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency în această celulă specifică. Puteți înregistra date în unul dintre următoarele moduri: apăsați tasta (Enter); faceți clic pe altă celulă; utilizați butoanele de control ale cursorului de pe tastatură (săgeți). Înregistrați informațiile pe care le-ați introdus. 5. Selectați celula tabelului care conține ziua săptămânii și utilizați butoanele de aliniere a paragrafelor. Cum are loc alinierea? Trage o concluzie. După toate experimentele, asigurați-vă că întoarceți alinierea inițială la stânga, acest lucru va fi important în viitor. 6. Ați observat deja că tabelul este format din coloane și rânduri, iar fiecare dintre coloane are propriul titlu (A, B, C...), iar toate rândurile sunt numerotate (1, 2, 3...) (Fig. 1). Pentru a selecta o întreagă coloană, faceți clic pe antetul acesteia; pentru a selecta un întreg rând, trebuie să faceți clic pe antetul acesteia. Selectați întreaga coloană a tabelului în care se află numele zilei săptămânii pe care ați introdus-o. Care este titlul acestei rubrici? Selectați întregul rând al tabelului care conține numele zilei săptămânii. Ce titlu are această linie? Utilizați barele de defilare pentru a determina câte rânduri are tabelul și care este numele ultimei coloane. 7. Selectați celula tabelului care se află în coloana C și rândul 4. Vă rugăm să rețineți că în „câmpul Nume” (Fig. 1), situat deasupra antetului coloanei A, apare adresa celulei selectate C4. Selectați o altă celulă și veți vedea că adresa din câmpul Nume s-a schimbat. Care este adresa celulei care conține ziua săptămânii? 8. Să ne imaginăm că în celula care conține ziua săptămânii, trebuie să adăugați și o parte a zilei. Selectați celula care conține ziua săptămânii, introduceți numele părții curente a zilei folosind tastatura, de exemplu, „dimineața” și înregistrați datele apăsând tasta Enter. Ce s-a întâmplat? O parte a zilei nu a fost „adăugat” în celulă, dar date noi au înlocuit datele originale și, în loc de ziua săptămânii, ați primit o parte din zi. Adică, dacă selectați o celulă de tabel care conține unele date și introduceți date noi de la tastatură, cele mai recente informații vor apărea în celula tabelului. Cum puteți adăuga (editați) conținutul unei celule de tabel fără a reintroduce toate datele? Selectând celula care conține partea zilei, veți vedea că conținutul acesteia este duplicat în „Bara de formule” situată deasupra antetelor coloanei (Fig. 1). În „Bara de formule” puteți face clic pe un cursor de text tradițional, puteți face toate modificările necesare și apoi puteți efectua versiunea finală a datelor. Selectați celula tabelului care conține partea zilei, plasați cursorul de text în fața textului din „Bara de formule” și reintroduceți ziua săptămânii. Înregistrați datele. Ar trebui să obțineți următoarea imagine (Fig. 2). -4- Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency 9. Se poate observa că înregistrarea a depășit granițele celulei sale și a ocupat o parte din dimineața de marți a celei vecine. Acest lucru se întâmplă numai atunci când celula adiacentă este goală. Să-i luăm Fig. 2 completați-l și verificați ce modificări. Selectați celula tabelului situată în dreapta celulei care conține datele dvs. (celula în care au „intrat”) și introduceți orice text în ea. Acum este vizibilă doar acea parte a datelor dvs. care se potrivește în celulă (Fig. Marți, Vineri 3). Cum pot vedea întreaga înregistrare? Și Fig. 3 din nou „Barul de formule” vă va veni în ajutor. În el puteți vedea întregul conținut al celulei selectate. Deci, „Bara de formule” vă permite: să faceți modificări conținutului celulei selectate; vizualizați conținutul unei celule dacă întreaga intrare nu este vizibilă. Selectați celula care conține ziua săptămânii și o parte a zilei și vizualizați conținutul complet al celulei în bara de formule. 10.Cum pot crește lățimea unei coloane astfel încât atât ziua săptămânii, cât și o parte a zilei să fie vizibile într-o celulă în același timp? Pentru a face acest lucru, mutați indicatorul mouse-ului la marginea din dreapta a antetului coloanei, „prindeți” momentul în care indicatorul mouse-ului se schimbă într-o săgeată dublă neagră și, în timp ce țineți apăsat butonul stâng al mouse-ului, mutați chenarul coloanei la dreapta. Coloana s-a extins. În mod similar, puteți modifica înălțimea liniei. În acest caz, când este mutat la marginea de jos a antetului liniei, cursorul ia forma: Modificați lățimea coloanei care conține ziua săptămânii și o parte a zilei, astfel încât tot textul introdus să fie vizibil în celula tabelului. 11.Adesea este necesar să selectați nu o celulă sau o întreagă coloană, ci un bloc de celule (mai multe celule situate în apropiere). 12. Pentru a face acest lucru, trebuie să plasați indicatorul mouse-ului în celula cea mai exterioară a selecției și, în timp ce țineți apăsată tasta din stânga, mutați mouse-ul la marginea opusă selecției (întregul bloc selectat este „acoperit” de un cadru, toate celulele cu excepția celei de la care a început selecția sunt colorate în negru) . Vă rugăm să rețineți că în timpul procesului de selecție, „Câmpul de nume” înregistrează numărul de rânduri și coloane care se încadrează în selecție. În același moment în care eliberați tasta din stânga, adresa celulei din care ați început selecția este afișată în „Câmpul de nume”. Selectați un bloc de celule, începând cu celula A1 și terminând cu celula care conține „vineri”. Pentru a selecta întregul tabel, utilizați butonul de colț „gol” situat deasupra antetului primului rând. -5- Copyright JSC Central Design Bureau BIBKOM & LLC Agenție Book-Service Selectați întregul tabel. Deselectați selecția făcând clic pe orice celulă. 13. Cum să ștergeți conținutul unei celule? Pentru a face acest lucru, trebuie doar să selectați o celulă (sau un bloc de celule) și să apăsați tasta (Ștergere) sau să utilizați comanda de meniu orizontal „Editare” ⇒ „Ștergere”. Ștergeți toate intrările dvs. Exercițiul 2 Aplicați tehnicile de bază ale foii de calcul: introducerea datelor într-o celulă. Formatarea fontului. Modificarea lățimii coloanei. Completare automată, introduceți o formulă, încadrați un tabel, aliniați textul la centrul selecției, setați indicele și indicele. Să creăm un tabel care calculează al n-lea termen și suma unei progresii aritmetice. Pentru început, să ne amintim formula pentru al n-lea termen al unei progresii aritmetice: an = a1 + d (n − 1) și formula pentru suma primilor n termeni ai unei progresii aritmetice: n S n = (a1) + an) ⋅ , 2 unde a1 este primul termen al progresiei, iar d – diferența progresiei aritmetice. În fig. Figura 4 prezintă un tabel pentru calcularea celui de-al n-lea termen și suma unei progresii aritmetice, al cărei prim termen este egal cu -2, iar diferența este egală cu 0,725. Orez. 4 Înainte de a efectua exercițiul, găsiți propria progresie aritmetică, adică setați-vă primul termen al progresiei și diferenței. Exercițiul poate fi împărțit în următorii pași: selectați celula A1 și introduceți în ea titlul tabelului „Calculul celui de-al n-lea termen și suma unei progresii aritmetice”. Titlul va fi plasat pe o singură linie și va ocupa mai multe celule în dreapta lui A1; În celula A2 introduceți „d”, în celula B3 – „n”, în C3 – „an”, în D3 – „Sn”. Pentru a seta indicele, mai întâi tastați tot textul care ar trebui să fie în celulă (de exemplu, an), apoi accesați „Bara de formule”, selectați textul care ar trebui să fie un indice (de exemplu, n), deschideți comanda „ Format” ⇒ „Celele” ...” (există o singură filă „Font” în caseta de dialog de deschidere) și activați comutatorul „subscript” în grupul „Modificare”; Calculul celui de-al n-lea termen și suma progresiei aritmetice d n an Sn 0,725 1 -2 -2 0,725 2 -1,275 -3,275 0,725 3 -0,55 -3,825 0,725 4 0,175 -3,65 -3,65 . 625 -1 .125 0,725 7 2,35 1,225 0,725 8 3,075 4,3 0,725 9 3,8 8,1 0,725 10 4,525 12,625 -6- Copyright JSC Central Design Bureau BIBKOM & LLC Agenția de servicii de carte completată. Folosind butoanele corespunzătoare din bara de instrumente, măriți dimensiunea fontului cu 1 pt, aliniați la centru și aplicați un stil de caractere aldine. Antetul tabelului este proiectat. Acum puteți începe să completați tabelul. 1. În celula A3, introduceți valoarea diferenței progresiei aritmetice (în exemplul nostru este 0,725). 2. În continuare, trebuie să completați un rând de celule inferioare cu același număr. Introducerea aceluiași număr în fiecare celulă este neinteresantă și irațională. În editorul Word, am folosit tehnica copy-paste. Excel face și mai ușor să umpleți celulele cu aceleași date. Selectați celula A3, care conține diferența de progresie aritmetică. Celula selectată este înconjurată de un cadru, în colțul din dreapta jos al căruia se află un mic pătrat negru - un marcator de umplere. Dacă mutați indicatorul mouse-ului la marcatorul de umplere și în momentul în care indicatorul mouse-ului ia forma unei cruci negre, trageți indicatorul de umplere în jos în mai multe celule (în același timp, apare un indiciu în partea dreaptă a cursorului, care valoare este introdusă în celula curentă), apoi întregul rând de celule selectate va fi umplut cu datele aflate în prima celulă. Astfel, completați încă nouă celule sub celula A3 cu valoarea diferenței progresiei aritmetice. 3. Următoarea coloană conține o succesiune de numere de la 1 la 10. Și din nou, un marcator de umplere ne va ajuta să completăm rândul. Introduceți numărul 1 în celula B3, numărul 2 în celula B4, selectați ambele celule și, apucând marcatorul de umplere, trageți-l în jos. Diferența față de completarea cu aceleași date este că, selectând două celule, ați indicat principiul după care celulele rămase trebuie umplute. Marcatorul de umplere poate fi „tras” nu numai în jos, ci și în sus, la stânga sau la dreapta, iar umplerea se va răspândi în aceleași direcții. Elementul de umplere poate fi nu numai o formulă sau un număr, ci și un text. Puteți introduce „ianuarie” în celulă și, completând rândul mai în dreapta, veți obține „februarie”, „martie”, iar prin „întinderea” marcatorului de umplere din celula „ianuarie” din stânga, veți va primi în consecință „decembrie”, „noiembrie”, etc. Încercați acest lucru în afara tabelului pe care îl creați. Cel mai important lucru este că înainte de a răspândi selecția, selectați exact celula (sau celulele) pe care este formatată umplerea. 4. A treia coloană conține termenii a n-a ai progresiei. Introduceți valoarea primului termen al progresiei aritmetice în celula C3. În celula C4 trebuie să plasați o formulă pentru calcularea celui de-al n-lea termen al progresiei, care constă în faptul că fiecare celulă a coloanei diferă de cea anterioară prin adăugarea diferenței progresiei aritmetice. Toate formulele încep cu un semn egal. Pentru a introduce o formulă într-o celulă trebuie: să activați celula; -7- Copyright JSC „CDB „BIBKOM” & LLC „Agency Kniga-Service” introduceți semnul equals „=" de la tastatură sau faceți clic pe butonul „Modificați formula” din bara de formule; introduceți (fără spații) valorile sau referințele necesare, precum și operatorii necesari; intrare de comitere. Adresa celulei este introdusă în formule cu majuscule latină. Dacă introducerea a fost făcută în registrul rus, atunci apare mesajul de eroare „#NAME?”. Selectați celula C4 și introduceți formula =C3+A4 în ea (nu uitați să comutați la alfabetul latin și, în loc să vă referiți la celula A4, puteți introduce valoarea specifică a diferenței progresiei dvs. aritmetice). Nu trebuie să introduceți adresa celulei la care se face referire de la tastatură. După ce ați tastat semnul egal, faceți clic pe celula C4 și adresa acesteia va apărea în bara de formule, apoi continuați să tastați formula. În acest caz, nu trebuie să treceți la latină. După ce ați introdus complet formula, remediați-o apăsând (Enter), rezultatul calculului va apărea în celulă, iar formula în sine va apărea în „Bara de formule”. Iată o altă funcție a „Barei de formule”: dacă într-o celulă vedeți rezultatul calculelor folosind o formulă, atunci formula în sine poate fi vizualizată în „Bara de formule” selectând celula corespunzătoare. Dacă ați introdus incorect o formulă, o puteți corecta în „Bara de formule” selectând mai întâi celula. Selectați celula C4 și, similar cu umplerea celulelor cu diferențe de progresie, completați cu formula „trăgând” marcatorul de umplere în jos pe rândul de celule de sub C4. Selectați celula C8 și căutați în „Bara de formule” pentru a vedea cum arată formula, arată ca =C7+A8. Este de observat că referințele din formulă s-au schimbat în raport cu decalajul formulei în sine. 5. În mod similar, introduceți formula =(-2+С3)*B3/2 în celula D3 pentru a calcula suma primilor n termeni ai unei progresii aritmetice, unde în loc de -2 ar trebui să existe primul termen al aritmeticii inventate. progresie. Selectați celula D3 și umpleți celulele de jos cu formule trăgând în jos mânerul de umplere. 6. Acum toate celulele sunt umplute cu date, tot ce rămâne este să le formatăm. Toate coloanele au aceeași lățime, deși conțin cantități diferite de informații. Puteți modifica manual (folosind mouse-ul) lățimea coloanelor individuale sau puteți ajusta automat lățimea. Pentru a face acest lucru, selectați toate celulele tabelului care conțin date (nu coloane întregi, ci doar un bloc de celule umplute fără titlul „Calculul al n-lea termen și suma unei progresii aritmetice”) și executați comanda „Format” ⇒ „Coloană”. ” ⇒ „Lățimea de ajustare automată”. 7. Acum să formatăm titlul tabelului „Calculul celui de-al n-lea termen și suma unei progresii aritmetice”. Selectați celula A1 și aplicați caractere aldine la conținutul celulei. Titlul, destul de inestetic, „iese” în dreapta dincolo de granițele micii noastre semne. -8- Copyright JSC „CDB „BIBKOM” & LLC „Agency Kniga-Service” Selectați patru celule de la A1 la D1 și executați comanda „Format” ⇒ „Cells...”, selectați fila „Alignment” și setați comutatoarele (Fig. 6): grupați „Aliniere” ⇒ „orizontal:” la poziția „centru de selecție”; grup „Afișaj” ⇒ „Încheierea cuvintelor”. Acest lucru va permite ca antetul să fie plasat pe mai multe linii și centrat în blocul de celule selectat. Tabelul a fost aproape redus la 8. tipul de probă. Dacă în acest moment vizualizați „Fișier” ⇒ „Previzualizare”, veți constata că tot ce rămâne este să încadrați tabelul. Pentru a face acest lucru, selectați tabelul (fără antet) și executați comanda „Format” ⇒ „Celule...”. În caseta de dialog care se deschide, selectați fila „Chenar”, definiți tipul de linie și activați comutatoarele „Sus”, „Jos”, „Stânga”, „Dreapta” (Fig. 5). Această procedură se aplică fiecărei celule din zona selectată. Apoi selectați blocul de celule aferent antetului: de la A1 la D2 și, după ce faceți aceleași operațiuni, setați comutatorul „Extern”. În acest caz, obțineți un cadru în jurul tuturor celulelor selectate, mai degrabă decât fiecare. Efectuați o previzualizare. Orez. 5 Fig. 6 -9- Copyright JSC „CDB „BIBKOM” & LLC „Agenția Kniga-Service” Lucrări de laborator Nr. 2 Exercițiul 1 Consolidarea abilităților de bază în lucrul cu foi de calcul, familiarizarea cu conceptele: sortarea datelor, tipuri de aliniere a textului într-o celulă , format de număr Expeditorul și adresa sa Destinatarul și adresa sa Numărul de înregistrare Data primirii „___”___________200__ FACTURA Nr. 123 din 15 noiembrie 2000 Furnizor Casa comercială „Coarne și copite” Adresa 243100, Klintsy, st. Pushkina, 23 Cont Nr. 45638078 în MMM Bank, MFO 985435 Adăugiri: Nr. Nume Unitate de măsură 1 2 3 4 5 6 TOTAL Şeful întreprinderii Cantitate Preţ Suma Sidorkin A.Yu. Contabil șef Ivanova A.N. Exercițiul implică crearea și completarea unui formular de factură de produs. Cel mai bine este să împărțiți exercițiul în trei etape: Etapa 1 – crearea unui tabel de formular de factură; Etapa 2 – completarea tabelului; Etapa 3 – completarea formularului. Etapa 1 constă în crearea unui tabel. Sarcina principală este să potriviți masa la lățimea foii. Pentru a face acest lucru: mai întâi setați marginile, dimensiunea hârtiei și orientarea („Fișier” ⇒ „Setări pagină...”); - 10 - Copyright JSC „CDB „BIBKOM” & LLC „Agenția Kniga-Service” prin executarea comenzii „Service” ⇒ pe „Opțiuni...”, fila „Vizualizare” în grupul de comutatoare Parametrii ferestrei, activați comutatorul „ Paginare automată" (Fig. 7) Ca urmare, veți primi marginea din dreapta a barei de apelare sub forma unei linii punctate verticale (dacă nu este vizibilă, deplasați-vă folosind bara de defilare orizontală la dreapta) și cea inferioară marginea barei de apelare sub forma unei linii punctate orizontale (pentru a o vedea derulând în jos folosind bara de defilare verticală). Paginarea automată vă permite să monitorizați în timpul procesului de colectare a datelor și formatare a tabelului ce coloane se potrivesc pe pagină și care nu. Nr. 1 2 3 4 5 6 Denumire Unitate Cantitate Pret Suma TOTAL Fig. 8 Creați un tabel conform modelului propus cu același număr de rânduri și coloane (Fig. 8). Aliniați și formatați fontul în celulele antetului, selectați lățimea coloanelor, schimbând-o cu mouse-ul. Introduceți un număr în prima coloană a tabelului folosind marcatorul de umplere. Aliniați masa folosind linii de grosimi diferite. Observați că, în ultimul rând, cele cinci celule adiacente nu au o margine internă. Cel mai simplu mod de a realiza acest lucru este în următorul mod: selectați întregul tabel și setați cadrul – „Extern” cu o linie îndrăzneață; - 11 - Copyright JSC „CDB „BIBKOM” & LLC „Agency Kniga-Service” apoi selectați toate liniile cu excepția ultimei și setați cadrul cu o linie subțire „Dreapta”, „Stânga”, „Sus”, „Jos” ; după aceea, selectați separat celula din dreapta a rândului de jos și setați cadrul „Stânga” pentru aceasta cu o linie subțire; Tot ce rămâne este să selectați primul rând al tabelului și să setați cadrul „De jos” pentru acesta cu o linie îndrăzneață. Deși poți face invers. Mai întâi, „liniați” întregul tabel, apoi eliminați liniile suplimentare de încadrare. În această etapă, este recomandabil să rulați comanda „Fișier” ⇒ „Previzualizare” pentru a vă asigura că întregul tabel se potrivește pe foaie în lățime și toate liniile de încadrare sunt la locul potrivit. Etapa 2: Completarea tabelului, sortarea datelor și utilizarea diferitelor formate de numere. Completați coloanele „Nume”, „Cantitate” și „Preț” după cum doriți. Setați formatul valutar al numărului din celulele în care vor fi plasate sumele și setați numărul necesar de zecimale, dacă există. În cazul nostru, acestea sunt celulele coloanelor „Preț” și Fig. 9 „Suma”. Trebuie să le selectați și să executați comanda „Format” ⇒ „Celule...”, selectați fila „Număr” și selectați categoria „Numerar” (Fig. 9). Acest lucru vă va oferi o împărțire în mii pentru a facilita navigarea în cantități mari. Introduceți formula pentru a calcula suma, care constă în înmulțirea prețului cu cantitatea și completați rândul de celule în jos cu formula. Introduceți o formulă în celulă pentru total. Pentru a face acest lucru, selectați blocul de celule care trebuie adăugat și o celulă goală sub acest bloc în care doriți să plasați rezultatul. După aceea, faceți clic pe butonul din bara de instrumente. Încercați să modificați datele în celule individuale și vedeți cum se modifică rezultatul calculului. Sortați intrările în ordine alfabetică. Pentru a face acest lucru, selectați toate rândurile tabelului, cu excepția primului (titlu) și ultimului ("Total"), nu puteți selecta numerotarea. Executați comanda „Date” ⇒ „Sorting...” (Fig. 10), selectați coloana după care doriți să sortați datele (în cazul nostru, aceasta este coloana B, deoarece conține lista mărfurilor de sortat ) și setați comutatorul în poziția „Crescător”. Etapa a 3-a de facturare, introduceți rânduri suplimentare înaintea tabelului. Pentru a face acest lucru, selectați primele câteva rânduri ale tabelului și executați comanda „Inserare” ⇒ „Rânduri”. Orez. 10 Va fi inserat același număr de rânduri ca și cel selectat. Introduceți textul necesar înainte și după tabel. Urmărește alinierea. Vă rugăm să rețineți că textul „Data primirii „__”_______200_.” iar numele directorilor întreprinderii sunt înscrise în aceeași coloană în care se află coloana din tabel „Suma” (coloana din dreapta tabelului nostru), se aplică doar alinierea la dreapta. Textul „CONT Nr...” este introdus în celula coloanei din stânga, iar alinierea este aplicată în centrul selecției (celulele de pe un rând sunt preselectate pe toată lățimea tabelului de facturi). Un chenar a fost aplicat acestor celule în partea de sus și de jos. Toate celelalte informații text înainte și după tabel sunt introduse în coloana din stânga, aliniate la stânga. Efectuați o previzualizare. Exercițiul 2 Introducerea conceptului de „legatură absolută”, stabilirea valorii exacte a lățimii coloanei folosind comenzile din meniul orizontal. Inserarea unei funcții folosind asistentul de funcții Noul concept de „referință absolută” poate fi examinat folosind un exemplu specific. Să pregătim un tabel tradițional de pătrate de numere din două cifre (Fig. 11), atât de familiar pentru toată lumea de la cursul de algebră. În celula A3, introduceți numărul 1, în celula A4 - numărul 2, selectați ambele celule și trageți marcatorul de selecție în jos pentru a umple coloana cu numere de la 1 la 9. În mod similar, umpleți celulele B2 - K2 cu numere de la 0 la 9 Când ați completat linia cu numere de la 0 la 9, atunci toate celulele de care aveți nevoie pentru lucru nu sunt vizibile pe ecran în același timp. Să le restrângem, dar astfel încât toate coloanele să aibă aceeași lățime (ceea ce nu se poate realiza prin modificarea lățimii coloanelor cu mouse-ul). Pentru a face acest lucru, selectați coloanele de la A la K și executați comanda „Format” ⇒ - 13 - Copyright JSC „CDB „BIBKOM” & LLC „Agency Kniga-Service” „Coloană” ⇒ „Lățime...”, în intrare câmpul „Lățimea coloanei” » introduceți o valoare, de exemplu 5. Desigur, toată lumea înțelege că în celula B3 trebuie să plasați o formulă care să pătrate numărul format din zecile indicate în coloana A și cele corespunzătoare valorii plasate în rândul 2. Astfel, numărul care trebuie pătrat în celula B3 poate fi specificat prin formula A3*10+B2 (numărul de zeci înmulțit cu zece plus numărul de unități). Tot ce rămâne este să pătrați acest număr. 1 2 3 4 5 6 7 8 9 0 100 400 900 1600 2500 3600 4900 6400 8100 1 121 441 961 1681 2601 3721 5000 4900 6400 8100 44 169 196 225 484 529 576 625 1024 1089 1156 1225 1764 1849 1936 2025 2704 2809 2916 3025 3844 3969 4096 4225 5184 5329 5476 5625 6724 6889 7056 7225 8464 8 6 8 6 6 6 6 296 2 116 3136 4356 5776 7396 9216 7 289 729 1369 2209 3249 4489 5929 7569 9409 8 324 784 144446343 6084 7744 9604 9 361 841 1521 2401 3481 4761 6241 7921 9801 Fig. 11 Să încercăm să folosim „Asistentul de funcții”. Pentru a face acest lucru, selectați celula în care trebuie plasat rezultatul calculului (VZ) și executați comanda „Inserare” ⇒ „Funcție...” (Fig. 12). Caseta de dialog „Asistent de funcții (pasul 1 din 2)” (Fig. 12) are două ferestre secundare: „Categorie” și „Funcție”. Când selectați o anumită funcție, o scurtă descriere a acesteia apare în partea de jos a casetei de dialog. Dintre cele propuse Fig. 12 categorii de funcții, selectați „Matematic”, dintre „Funcții” - „Grad”, apăsați butonul Ok. În următoarea casetă de dialog (Fig. 13), introduceți în câmpul „Număr” (bază de putere) – A3*10+B2 și în câmpul „Exponent” – 2. La fel ca atunci când introduceți o formulă direct într-o celulă de tabel, nu este nevoie să introduceți adresa fiecărei celule la care face referire formula folosind tastatura. În - 14 - Copyright JSC „CDB „BIBKOM” & LLC „Agency Kniga-Service” în caseta de dialog din a doua etapă a „Function Wizard”, trebuie doar să îndreptați mouse-ul către celula corespunzătoare a foii de calcul, iar adresa acesteia va fi apar în câmpul de introducere „Număr” al casetei de dialog. Va trebui doar să introduceți aritmetica Fig. 13 semne (*, +) și numărul 10. În cazurile în care pentru a introduce argumente trebuie să selectați celule acoperite de o fereastră, în dreapta fiecărui câmp pentru introducerea argumentelor există butoane care vă permit să restrângeți și să extindeți caseta de dialog . În plus, fereastra Function Wizard poate fi mutată în lateral „prinzând” bara de titlu cu mouse-ul. În aceeași casetă de dialog (Fig. 13) puteți vedea valoarea numărului în sine (10) și rezultatul calculării gradului (100). Tot ce rămâne este să faceți clic pe butonul Ok. Rezultatul calculelor apare în celula B3. Aș dori să extind această formulă la restul celulelor din tabel. Selectați celula B3 și umpleți celulele adiacente trăgând mânerul de umplere la dreapta. Ce sa întâmplat (Fig. 14)? Orez. 14 De ce rezultatul nu a îndeplinit așteptările noastre? Numărul nu este vizibil în celula C3, deoarece nu se încadrează în întregime în celulă.Extindeți coloana C cu mouse-ul.Numărul apare pe ecran, dar în mod clar nu corespunde pătratului numărului 11 (Fig. 15). ). Orez. 15 De ce? Cert este că atunci când am extins formula spre dreapta, Excel a schimbat automat adresele celulelor, ținând cont de offset-ul nostru, la care se referă formula, iar în celula C3 nu numărul 11 ​​este pătrat, ci număr calculat prin formula B3 * 10 + C2. În toate exercițiile anterioare, am fost destul de mulțumiți de legăturile relative către celulele tabelului (la mutarea unei formule, legăturile se deplasează și ele conform aceleiași legi), dar aici a devenit necesară remedierea anumitor legături, de ex. indicați că numărul de zeci poate fi luat doar din coloana A, iar numărul de unități doar din rândul 2 (astfel încât formula să poată fi - 15 - Copyright OJSC Central Design Bureau BIBKOM & LLC Agency Book-Service extinsă în jos). Pentru a face acest lucru, Excel are capacitatea de a seta legături absolute și mixte. O referință absolută este o referință care nu se modifică atunci când formulele sunt copiate. Pentru a face acest lucru, adăugați un semn dolar $ în fața numelui coloanei și a numărului rândului (fie introdus de la tastatură, fie după introducerea adresei celulei, apăsați tasta funcțională F4). Legăturile mixte sunt legături care sunt doar parțial absolute, adică. Fie o coloană, fie un rând este fix. În acest caz, semnul dolar $ este plasat fie înaintea literei, în cazul în care este fixă ​​o coloană, fie înaintea numărului, în cazul în care este fix un rând. Semnul dolar $ este fie introdus de la tastatură, fie după introducerea adresei celulei, tasta funcțională F4 este apăsată până când semnul $ se află în locația dorită. Când copiați o formulă care conține o referință mixtă, se modifică doar partea relativă a referinței. Readuceți lățimea coloanei C în poziția inițială și efectuați următorii pași: Selectați celula B3 și, plasând cursorul de text în „Bara de formule”, corectați formula existentă =GRADE(A3*10+B2;2) la cea corectă. =GRADĂ($A3*10+ în $2,2). Acum, folosind serviciile unui marcator de umplere, puteți umple toate celulele libere ale tabelului cu această formulă (mai întâi trageți marcatorul de umplere la dreapta, apoi, fără a elimina selecția din blocul de celule rezultat, în jos). Pentru a introduce referințe de celule pentru datele coloanei A și al rândului 2, am folosit referințe mixte. O legătură absolută din exemplul nostru ar putea fi folosită dacă am introduce în formulă nu numărul 10, cu care se înmulțesc numerele din coloana A, ci adresa celulei, de exemplu A15 (unde am introduce acest număr 10). În acest caz, formula din celula B3 ar fi scrisă ca: =POWER($A3*$A$15+B$2) și apoi a copiat-o în celulele rămase. Incearca asta. Tot ce rămâne este să proiectați tabelul: introduceți un titlu pentru celula A1, formatați-l și centrați-l în funcție de selecție, încadrați tabelul și umpleți fundalul celulelor individuale. Exercițiul 3 Introducere în conceptul de „nume celule” Imaginați-vă că aveți propria companie care vinde orice produs și în fiecare zi trebuie să tipăriți o listă de prețuri cu prețuri pentru mărfuri în funcție de cursul de schimb al dolarului. Pregătiți un tabel format din coloane: „Numele produsului”; „echivalent în dolari SUA”; „Prețul în ruble”. - 16 - Copyright JSC „CDB „BIBKOM” & LLC „Agenția Kniga-Service” Completați toate coloanele, cremă „Prețul în ruble”. Completați coloana „Nume produs” cu date text (lista de produse la discreția dvs.) și coloana „Echivalent în USD” cu numere (prețuri în USD). Este clar că în coloana „Prețul în ruble” Formula ar trebui să fie: „Echivalent cu $ US” * „Cursul de schimb al dolarului”. De ce este incomod să înmulțiți cu o anumită valoare a cursului de schimb în această formulă? Da, pentru că de fiecare dată când rata se schimbă, va trebui să-ți schimbi formula în fiecare celulă. Este mai ușor să alocați o celulă separată pentru valoarea cursului de schimb al dolarului, la care vă puteți referi în formulă. Este clar că legătura trebuie să fie absolută, adică. valoarea cursului de schimb al dolarului poate fi luată numai din această celulă specifică cu o adresă fixă. Am discutat mai sus cum să setăm legături absolute, dar există o altă modalitate convenabilă: nu faceți referire la adresa celulei, ci la numele care poate fi atribuit celulei. Când denumiți o celulă sau un interval de celule, puteți accesa acea celulă sau interval în orice moment și de oriunde în tabel, chiar dacă își schimbă locațiile sau se află pe foi diferite. Selectați celula în care va fi introdus cursul de schimb al dolarului (deasupra tabelului), introduceți în ea valoarea cursului de schimb al dolarului de astăzi și executați comanda „Inserați” ⇒ „Nume” ⇒ „Atribuiți...”. În caseta de dialog care se deschide (Fig. 16), puteți introduce orice nume și puteți selecta în câmpul „Formulă” intervalul pentru care este introdus acest nume. Numele poate avea până la 255 de caractere și poate conține litere, cifre, litere de subliniere (_), bare oblice inverse (\), puncte și semne de întrebare. Cu toate acestea, primul caracter trebuie să fie o literă, un caracter de subliniere (_) sau o bară oblică inversă (\). Numele care sunt interpretate ca numere sau referințe de celule nu sunt permise. În caseta de dialog care apare, tot ce trebuie să faceți este să introduceți numele celulei (adresa exactă a acesteia este deja dată în câmpul de introducere „Formulă”) și să apăsați butonul Ok. Vă rugăm să rețineți că în „Câmpul de nume”, în loc de adresa celulei, acum este plasat numele acesteia. În celula situată în stânga celulei „Dollar_Rate”, puteți introduce textul „Dollar Rate”. Orez. 16 Acum rămâne să introduceți formula pentru calcularea prețului în ruble. Pentru a face acest lucru, selectați celula goală cea mai de sus a coloanei „Prețul în ruble” și introduceți formula după cum urmează: introduceți semnul „=”, apoi faceți clic pe mouse - 17 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency pe celula situată în stânga (în care este plasat prețul în dolari), apoi introduceți semnul „*” și „Rata_dolarului”. Formula ar trebui să arate cam așa: =B7*Rata_dolarului. Completați formula în jos folosind marcatorul de umplere. Selectați celulele corespunzătoare și aplicați acestora formatul de număr de monedă. Stilați antetul tabelului: aliniați la centru, aplicați un stil de font aldine, extindeți linia și aplicați o aliniere centrală verticală folosind comanda „Format” ⇒ „Celule...”, selectați fila „Aliniere” și în „Verticală: ”, selectați „În centru”. În aceeași casetă de dialog, activați comutatorul „Încheiere după cuvinte” în cazul în care un titlu nu se potrivește pe o singură linie. Modificați lățimea coloanelor. Selectați tabelul și setați un chenar pentru acesta. - 18 - Copyright JSC „CDB „BIBKOM” & LLC „Agenția Kniga-Service” Lucrări de laborator nr. 3 Exercițiul 1 Schimbarea orientării textului într-o celulă, familiarizarea cu capacitățile bazelor de date Excel. Sortarea datelor după mai multe chei Imaginați-vă că sunteți proprietarul unui mic magazin. Este necesar să țineți evidența strictă a primirii și consumului de mărfuri, să aveți echilibrul real în fața ochilor în fiecare zi, să puteți tipări numele mărfurilor pe departamente etc. Chiar și într-o sarcină atât de dificilă, Excel vă poate face munca mult mai ușoară. Să împărțim acest exercițiu în mai multe sarcini într-o succesiune logică: crearea unui tabel; completarea tabelului cu date în mod tradițional și utilizarea unui formular; selectarea datelor pe baza unui criteriu specific. 1. Crearea unui tabel Introduceți titlurile tabelului conform exemplului sugerat. Vă rugăm să rețineți că titlul este situat pe două rânduri ale tabelului: în linia de sus „Chitanță”, „Cheltuială”, „Sold” și linia de sub restul articolelor din titlu (Fig. 17). Suma soldului Rămas Cantitatea soldului Cantitatea cheltuială Cheltuieli Prețul cheltuielii Nume produs Cantitatea bonului Departament Preț bonului Nr. Unitate de măsură Chitanță 1 2 3 4 5 6 Fig. 17 Este mai bine să începeți să introduceți textul titlului din a doua linie. Ați observat deja că coloana „Parohie” acoperă două celule. Cuvântul „Chitanță” este tastat în aceeași coloană cu „Preț chitanță”, apoi sunt selectate două celule adiacente, iar textul este centrat pe selecție (această operațiune a fost discutată de mai multe ori în exercițiile anterioare). Celulele „Cheltuieli” și „Rămas” sunt formatate în mod similar. Selectați a doua linie de antet și aliniați-o la centru. De asemenea, puteți vedea că, pentru ca întregul tabel să se potrivească cu lățimea foii, în unele celule textul este „rotit cu 90°”. Selectați acele celule în care doriți să „extindeți” textul și selectați comanda „Format” ⇒ „Celele...” din fila „Alignment” (Fig. 18) selectați „Text Orientation” 90o și asigurați-vă că activați Comutator „Wrap to words” (lăsați alinierea verticală pe „Bottom”). Pentru celulele rămase (neexpandate), aplicați alinierea verticală „Centru”. Setați chenarul tabelului (Format ⇒ Celule…, fila Chenar). Instalați în celule, Fig. 18 care conține prețuri, format de număr monetar („Format” ⇒ „Celule...”, fila „Număr”). Introduceți numerotarea rândurilor tabelului (nr. coloană) folosind marcatorul de umplere. Introduceți formule pentru valoarea soldului („Cantitatea de chitanță” minus „Cantitatea de cheltuială”) și valoarea soldului („Cantitatea soldului” înmulțită cu „Prețul de cheltuială”). Distribuiți aceste formule pe tabel. În timpul efectuării unei sarcini, în multe cazuri este mai convenabil să folosiți meniul contextual, apelat prin apăsarea butonului dreapta al mouse-ului. Deci, pentru a formata celule, trebuie doar să le selectați, să faceți clic dreapta în timp ce cursorul mouse-ului se află în interiorul selecției și să selectați comanda „Format” ⇒ „Celele...”. Aceasta vă va duce la aceeași casetă de dialog Format Cells (Fig. 18). Și nu este deloc necesar să editați conținutul unei celule (corectați, modificați datele) în „Bara de formule”. Dacă faceți dublu clic pe o celulă sau apăsați tasta F2, în ea va apărea un cursor text și puteți face toate corecțiile necesare. 2. Completarea tabelului Redenumiți „Sheet1” în „Availability”. Pentru a face acest lucru, faceți clic dreapta pe comanda rapidă „Sheet1” și selectați comanda Redenumire. Tastați un nume nou și apăsați Enter. Decideți ce tip de produs veți vinde și ce departamente vor fi în magazinul dvs. Introduceți datele în tabel nu în funcție de departament, ci în mod aleatoriu (în ordinea în care sunt primite mărfurile). Completați toate celulele, cu excepția celor care conțin formule („Restau”). Asigurați-vă că lăsați necompletat ultimul rând al tabelului (dar acest rând trebuie să conțină toate formulele și numerotarea). Introduceți datele în așa fel încât să existe produse diferite din același departament (dar nu pe rând) și să existe întotdeauna produse cu sold zero (toate vândute) (Fig. 19 ). De acord că modul tradițional de a completa un tabel nu este deosebit de convenabil. Să folosim capacitățile bazelor de date Excel. 1 2 3 4 5 6 Departament Cofetărie Lactate Carne Carne Vin-vodcă Nume produs Marshmallow în ciocolată Brânză Cârnați Moscova Balyk Vodcă „Absolut” Consum Restant Preț de primire Cantitate de primire Preț de consum Cantitate de consum Cantitate de rest Suma rămasă Nr. măsurare Pachetul de sosire. 20 de freci. 15 kg. 65 de frecare. 10 kg. 110 rub. 20 kg. 120 de ruble. 10 sticle 2 l. 400 de ruble. 100 25 rub. 85 de frecare. 120 de ruble. 140 de ruble. 450 de ruble. 15 8 15 5 99 0 2 5 5 1 0 0 r. 170 de ruble. 600 de ruble. 700 de ruble. 450 de ruble. 0 frecare. Orez. 19 Selectați comanda „Date” ⇒ „Form...” Veți primi un formular de date (Fig. 20) care conține text static (numele câmpurilor bazei de date) și ferestre de editare în care puteți introduce și edita text. Câmpurile calculate (în care sunt plasate formule) sunt afișate pe ecran fără ferestre de editare („Suma rămasă” și „Suma rămasă”). Acum aveți tabelul sub formă de cărți de înregistrare separate (fiecare reprezentând un rând de masă). Orez. 20 Vă puteți deplasa între înregistrări fie utilizând butoanele „Previous”, „Next” sau tastele cursorului (sus, jos), fie deplasând cursorul de pe bara de defilare a formularului de date. După ce am ajuns la ultima intrare (am lăsat-o în mod deliberat goală, dar am extins formulele și numerotarea la ea), completați-o cu date noi. Este convenabil să vă deplasați între ferestrele de editare în care sunt introduse datele folosind tasta (Tab). După ce ați completat întreaga înregistrare, apăsați tasta Enter și veți fi dus automat la o nouă carte de intrare goală. Odată ce completați o nouă înregistrare, toate informațiile pe care le-ați introdus vor fi reproduse automat în tabelul original. Completați câteva intrări noi și faceți clic pe butonul Închidere. - 21 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency După cum puteți vedea, completarea tabelului în modul formular este destul de convenabilă. 3. Completarea tabelului folosind o listă gata de date Deoarece avem un număr limitat de departamente și numele acestora sunt constante, atunci când completați tabelul este mai bine să folosiți o listă pregătită în prealabil a acestor departamente. Să ștergem numele departamentelor din coloana „Departament” și să introducem o listă scurtă care include numele tuturor departamentelor o dată, în afara tabelului, de exemplu în coloana L. Apoi selectați celulele coloanei „Departament” din tabel și selectați comanda „Date” ⇒ „Verificare”. În acest caz, va apărea Fig. 21 caseta de dialog „Verificarea valorilor introduse” (Fig. 21), unde trebuie să specificam condițiile de verificare. În exemplul nostru, trebuie să selectăm dintr-o listă (care este ceea ce introducem în câmpul „Tipul de date”). Pentru a selecta „Sursa” datelor, utilizați butonul de restrângere a ferestrei. Faceți clic pe el, evidențiați lista departamentelor noastre în coloana L și reveniți la fereastră folosind butonul de maximizare a ferestrei. După finalizarea acestor pași, faceți clic pe Ok. Acum, când mergem la celulele coloanei „Departament”, unde este setată condiția de verificare, în dreapta acestor celule va apărea un pătrat cu o săgeată, făcând clic pe care putem selecta numele departamentului de care avem nevoie (Fig. 22). Orez. 22 Pentru a ascunde tabelul cu departamente, puteți face fontul din celulele coloanei L albe sau puteți ascunde întreaga coloană. Pentru a ascunde coloana L, selectați-o și alegeți Format ⇒ Coloana ⇒ Ascundere. Pentru a returna coloana L pe ecran, - 22 - Copyright JSC „CDB „BIBKOM” & LLC „Agency Kniga-Service” trebuie să selectați coloanele din jurul coloanei ascunse (coloanele K și M) și să executați comanda „Format” ⇒ „Coloană” ⇒ „Afișare” Rețineți că comanda Ascunde poate fi aplicată și la rânduri. Pentru a face acest lucru, selectați o linie și selectați comanda „Format” ⇒ „Rând” ⇒ „Ascunde”. Pentru a returna o linie pe ecran, trebuie să selectați liniile din jurul liniei ascunse și să executați comanda „Format” ⇒ „Rând” ⇒ „Afișare”. Orez. 23 De asemenea, puteți crea o listă pe o altă foaie. Cu toate acestea, în acest caz, este imposibil să se specifice adrese care includ numele foii ca „Sursă” de informații, de exemplu. Trebuie să introduceți numele intervalului de celule ca adresare. În ultima lecție, am învățat cum să denumim o singură celulă. Pentru a denumi intervale de celule, trebuie să selectați intervalul de celule, nu doar o celulă, înainte de a executa comanda „Inserare” ⇒ „Nume” ⇒ „Atribuiți”. Să ne mutăm lista de departamente din coloana L a Sheet1 în Sheet2 din coloana A. Selectați celulele în care este plasată lista noastră și executați comanda „Insert” ⇒ „Nume” ⇒ „Assign”. În caseta de dialog care se deschide (Fig. 23), puteți introduce orice nume, de exemplu „Departament”, și selectați în câmpul „Formulă” domeniul pentru care este introdus acest nume (implicit, adresa intervalului pe care îl vom selectat este plasat aici). După aceea, faceți clic pe butonul Ok. Acum, în caseta de dialog a comenzii „Verificați...” ca sursă (Fig. 21), introduceți doar semnul „=”, apoi apăsați tasta F3 pentru a deschide lista de nume de celule disponibile, selectați „Departament” în lista care se deschide și apăsați butonul Ok pentru a închide casetele de dialog. 4. Sortarea datelor Deci, ați completat tabelul în ordinea în care au fost primite mărfurile, dar doriți să aveți o listă de mărfuri pe departamente, pentru aceasta vom folosi sortarea pe rând. Selectați tabelul cu al doilea rând de antet, dar fără prima coloană „Nu” și selectați comanda „Date” ⇒ „Sortificare...” (Fig. 24). Orez. 24 - 23 - Copyright JSC „CDB „BIBKOM” & LLC „Agency Kniga-Service” Selectați prima cheie de sortare: în lista derulantă „Sort by”, selectați „Department” și setați comutatorul în poziția „Ascendent” (toate departamentele din tabel vor fi localizate alfabetic). Dacă doriți ca toate produsele dintr-un departament să fie plasate alfabetic, atunci selectați a doua cheie de sortare: în lista derulantă „Apoi după”, selectați „Nume produs”, setați comutatorul în poziția „Ascendent”. Acum aveți o listă completă de produse pe departamente. 5. Filtrarea datelor Să continuăm cunoștințele noastre cu capabilitățile bazelor de date Excel. Să ne amintim că în fiecare zi trebuie să tipărim o listă de mărfuri rămase în magazin (având un sold diferit de zero) sau să arătăm soldurile în orice departament, dar pentru aceasta trebuie mai întâi să obținem o astfel de listă, de exemplu. filtrați datele. Selectați tabelul cu al doilea rând de antet (ca înainte de a crea formularul de date). Selectați comanda de meniu „Date” ⇒ „Filter...” ⇒ „Autofilter”. Deselectați tabelul. Fiecare celulă antet de tabel are acum un buton săgeată (nu este tipărit) care vă permite să setați criteriile de filtrare (Fig. 25). Orez. 25 Să presupunem că vrem să lăsăm toate înregistrările pentru „Departamentul de cofetărie”. Extindeți lista de celule „Departament” și selectați „Cofetărie”. În acest caz, Excel va schimba tabelul și îl va afișa într-o formă în care vor fi prezente doar datele referitoare la departamentul selectat (Fig. 26), iar săgeata din coloana în care a fost aplicat autofiltrul va deveni albastră. Orez. 26 - 24 - Copyright OJSC Biroul Central de Proiectare BIBKOM & LLC Kniga-Service Agency În același mod, puteți vizualiza datele pentru alte departamente sau puteți selecta un criteriu de filtrare într-o altă coloană. Tabelul poate fi tipărit sub formă filtrată. Liniile filtrate pot fi evidențiate cu culoarea fontului, fundal, cadre sau formatate în alt mod. Înlăturând filtrarea, obținem un aspect foarte clar al tabelului. În secțiunea filtrată a tabelului, puteți calcula sume, produse și puteți efectua alte operațiuni ca și cum nu ar exista alte rânduri ale tabelului. De exemplu, dorim să calculăm suma soldului pentru departamentul de cofetărie. Pentru a face acest lucru, selectăm datele din coloana „Suma rămasă”, luând ultima celulă liberă și facem clic pe butonul „Suma automată”. Funcția SUBTOTAL(9; F2:F8) va apărea în celula liberă (Fig. 26). În ea, primul argument este numărul operației matematice sau statistice (1 - calculul valorii medii; 2 și 3 - numărarea numărului de numere și celule nevide; 4 și 5 - calculul maximului și minimului; 6 - produs; 7 și 8 - abaterea standard; 9 - suma; 10 și 11 - dispersie), iar al doilea - intervalul de calcul. Funcția SUBTOTAL este situată în categoria matematică și diferă prin faptul că calculează valori numai din celulele vizibile și nu le ia în considerare pe cele invizibile. Când modificați filtrarea, se schimbă și subtotalurile (Fig. 25), în timp ce funcția obișnuită de sumă sau produs va rămâne neschimbată. Să schimbăm puțin starea problemei, să presupunem că vrem să ne uităm la datele soldurilor non-zero ale departamentului de cofetărie. Pentru a face acest lucru, selectați elementul „Condiție” din lista coloanei „Suma soldului”. Va apărea caseta de dialog „Custom AutoFilter” (Fig. 28). În câmpul de sus, selectați „mai mult” „0,00 rub”. Rezultatul obtinut este prezentat in Fig. 28. Fig. 27 Acum, să presupunem că vrem să ne uităm la datele privind soldurile non-zero în departamentele de cofetărie și carne. Pentru a face acest lucru, lăsăm același filtru în coloana „Sold sold” și selectăm „Condition” în coloana „Department” (Fig. 28). În câmpul de sus, selectați „egal” Fig. 28 - 25 - Drepturi de autor JSC Central Design Bureau BIBKOM & LLC Agenția de servicii de carte „Cofetărie”, sub „egal” cu „Carne”, și setați OR ca funcție logică. Rezultatul obtinut este prezentat in Fig. 29. Fig. 29 Pentru a vedea din nou întregul tabel, trebuie să faceți clic pe săgeata din coloana în care a fost aplicată filtrarea (acestea sunt afișate cu săgeți albastre), selectați „Toate” în listă sau mergeți la „Filtru” ⇒ „Filtru automat ” din meniul „Date” din nou pentru a anula filtrarea modului. - 26 - Copyright JSC Biroul Central de Proiectare BIBKOM & LLC Kniga-Service Agency Lucrare de laborator Nr. 4 Exercițiul 1 Crearea și editarea graficelor într-un document Excel Foarte des în viață trebuie să măsurați dependența unei variabile de alta și să arătați aceste dependențe în forma graficelor. Excel oferă posibilitatea unei astfel de afișari vizuale a datelor electronice numerice. Să presupunem că a fost efectuat un experiment, de exemplu, au măsurat dependența unui parametru de temperatură. Temperatura initiala a fost de 10°C. Etapa de schimbare este de 10°C. Introduceți aceste date în foaia Excel (Fig. 30). Pentru a poziționa antetul „Date sursă” deasupra tabelului cu date sursă, așa cum se arată în Fig. 30 este prezentat în figură, selectați două celule, executați comanda „Format” ⇒ „Format celulă...” și în fila „Aliniere”, selectați butonul radio din câmpurile „încheierea cuvintelor”, „combinați celulele” din grupul de afișare și aliniere „centru” și pe orizontală și pe verticală (vezi Lucrări de laborator Nr. 1). Acum să pregătim un tabel cu date experimentale (Fig. 31). Introduceți datele primei coloane - numerele sunt introduse în ordine folosind marcatorul de umplere (punct negru în colțul cursorului tabelului). Antetul coloanei „Temperatura” se introduce folosind formula: =A2 (adică în formulă există un link către adresa celulei, Fig. 31, unde numele parametrului este plasat în tabelul „Date inițiale” ( Fig. 30)). Temperatura noastră inițială este egală cu temperatura inițială, adică. =B2. În continuare, diferă de precedentul printr-un pas. Prin urmare, în a doua linie temperatura este egală cu temperatura inițială + treapta, adică. =E3+$B$3. Pentru a utiliza în continuare mânerul de umplere pentru a copia formula, iar pasul trebuie făcut în mod constant din aceeași celulă, facem referința la aceasta absolută. Informațiile introduse în acest fel ne permit să automatizăm ajustarea tabelului de date experimentale la modificările condițiilor inițiale. Încercați în loc de „temperatură” în datele inițiale, introduceți „presiune” și setați valoarea inițială, de exemplu, 20. Valorile Yexper ar trebui luate din experiment, așa că introduceți-le de la tastatură. Pentru a formata antetul „Experiment”, cel mai bine este să utilizați opțiunea de format de copiere. Pentru a face acest lucru: - 27 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Fig. 32 Fig. 33 selectați celula „Date inițiale”; Faceți clic pe butonul „Format conform modelului standard” din bara de instrumente. În acest caz, Excel copiază formatul celulei selectate, iar indicatorul mouse-ului se transformă într-o perie cu semnul plus în stânga acesteia; Trageți cursorul mouse-ului peste intervalul de celule în care doriți să copiați formatul selectat. Pentru a reprezenta un grafic dependența Yexper de temperatură, selectați aceste două coloane, inclusiv titlurile lor, și executați comanda „Insert” ⇒ „Diagram” sau faceți clic pe butonul Chart Wizard din bara de instrumente. Prima casetă de dialog „Chart Wizard (pasul 1 din 4) - tip diagramă” (Fig. 32) are două file - „Standard” și „Personalizat”. În această etapă, o variantă a diagramei în curs de construire este selectată din mostrele disponibile. Pentru a reprezenta dependența unei valori de alta, trebuie să selectați tipul de diagramă „Scatter”, apoi oricare dintre cele cinci tipuri ale sale. Să desenăm doar puncte și nu linii. Faceți clic pe butonul „Vizualizare rezultat” (Fig. 32). Excel va arăta imediat cum vor fi afișate datele noastre pe diagrama finalizată. Pentru a trece la fiecare pas ulterior al Chart Wizard, utilizați butonul „Următorul”. În a doua fereastră „Chart Wizard (pasul 2 din 4): sursa de date diagramă” din fila „Interval de date”, sunt afișate adresa intervalului de date sursă selectat și un eșantion al diagramei în curs de construire (Fig. 33) . Selectați „în coloane” și faceți clic pe butonul „Următorul”. - 28 - Copyright JSC „CDB „BIBKOM” & LLC „Kniga-Service Agency” În a treia etapă a Chart Wizard (la pasul 3) din fereastra „Chart Parameters” (Fig. 34), determinați natura diagramei proiectează - formatează-l. A treia fereastră a Chart Wizard are următoarele file: title – vă permite să introduceți textul titlului diagramei și etichetelor axelor; axa – vă permite să determinați Fig. 34 afișare și marcare a axelor de coordonate; linii de grilă – vă permite să definiți tipul de linii și natura afișajului grilei; legenda – vă permite să afișați sau să ascundeți legenda și să determinați locul acesteia în diagramă. Legendă - Fig. 35 este o mică fereastră pe diagramă, care afișează numele seriilor de date și exemple de colorare a acestora pe diagramă (sub forma unei taste Legend); etichete de date – vă permite să controlați afișarea etichetelor corespunzătoare elementelor de date individuale de pe diagramă; tabel de date - vă permite să adăugați sau să ascundeți tabelul de date folosit pentru a crea diagrama în diagramă. Ultima (a patra) fereastră a expertului diagramă (Fig. 35) este utilizată pentru a determina plasarea acesteia în registrul de lucru. Este recomandat să alegeți așezarea lui pe o foaie separată, deoarece... în acest caz, diagrama este mai ușor de introdus în alte documente, nu ascunde datele originale, este mai lizibilă etc. După ce ați definit toți parametrii necesari, faceți clic pe butonul „Terminare”. Excel ne-a construit o diagramă (Fig. 36) folosind un anumit set de parametri, precum culoarea zonei de trasare (zona în care este afișată diagrama în sine, fără titluri, legende și alte elemente), font, scale, punct dimensiune, etc., implicit. Pentru a schimba o anumită opțiune de formatare a diagramei, trebuie să faceți clic dreapta pe ea și să selectați comanda corespunzătoare în meniul contextual care se deschide. Faceți alb fundalul zonei diagramei. Pentru a face acest lucru, faceți clic dreapta pe zona de trasare a diagramei și selectați comanda „Format zona de trasare”. În caseta de dialog care se deschide (Fig. 37), selectați butonul radio din grupul de umplere „normal”. Faceți clic pe Ok. - 29 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Y=f(x) 12 10 8 Y ​​​​6 4 2 0 0 20 40 60 80 100 120 X Yexper Fig. 36 Fig. 37 Fig. 38 Măriți dimensiunea punctelor. Pentru a face acest lucru, faceți clic dreapta pe puncte și selectați comanda „Format Data Series”. În caseta de dialog care se deschide (Fig. 38), în grupul „dimensiune”, setați dimensiunea punctului, de exemplu, 8 pt. Aici puteți selecta alți parametri de date, de exemplu, schimbați marcatorul, de exemplu. tip de puncte, desenați o linie, selectând culoarea, grosimea și tipul acesteia, neteziți linia etc. Argumentul funcției noastre variază de la 10 la 100, iar scara axei X are o valoare minimă de 0 și maxim 120. În În plus, fontul semnăturii datelor este prea mic. Cum pot schimba asta? Faceți clic dreapta pe axa X și selectați Formatare axa. În caseta de dialog care se deschide (Fig. 39) în fila „Scale”, setați valoarea minimă la 10, maximă la 100 și prețul diviziilor principale la 10, deoarece datele noastre se modifică în trepte de 10. În aceeași fereastră, în fila „Font”, puteți crește dimensiunea fontului și puteți modifica stilul acestuia, de exemplu, creșteți-l la 8 pt și faceți-l italic. În pliul „Aliniere”, puteți seta scrierea verticală a semnăturilor. Într-un mod similar, puteți formata axa Y. Faceți acest lucru. Înțelegi deja principiul formatării diagramelor? Apoi măriți dimensiunea fontului titlului, ajustați legenda și titlurile axelor. Un exemplu de diagramă formatată este prezentat în figură (Fig. 40). Pentru a prezice valorile răspunsului - parametrul Y la ieșirea experimentului din factor - variabile independente X la intrarea în sistem (în cazul nostru, aceasta este temperatura), este necesar să se cunoască dependența funcțională Y = f (X). Excel are capacitatea de a selecta automat o astfel de funcție. Orez. 39 Yexper Y=f(x) 12 10 8 Y ​​​​6 4 2 100 90 80 70 60 50 40 30 20 10 0 X Fig. 40 Faceți clic dreapta pe puncte și selectați Add Trendline din meniul contextual. În caseta de dialog care se deschide (Fig. 41), în fila „Tip”, selectați tipul de linie de tendință. De obicei, o linie de tendință polinomială de ordinul doi este utilizată pentru a descrie sistemul: Y = a0 + a1 * X + a2 * X 2 , (1) unde ai sunt coeficienții ecuației. Dacă este necesar, puteți schimba gradul la 6. Atunci ecuația va lua forma: - 31 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Y = a0 + a1 * X + a2 * X 2 + a3 * X 3 + + a4 * X 4 + a5 * X 5 + a6 * X 6 . (2) Fig. 41 Fig. 42 În fila „Parametri”, setați comutatoarele la „afișați ecuația pe diagramă” și „plasați valoarea de fiabilitate a aproximării pe diagramă” (Fig. 42). Acest lucru vă va permite să vedeți ecuația și acuratețea potrivirii la datele noastre. Ecuația și precizia afișate pe ecran pot fi mutate în orice loc din diagramă (precum și alte etichete, de exemplu, titlul diagramei, titlurile axelor, legenda) prin „prinderea” cadrului cu butonul stâng al mouse-ului. O formă finală aproximativă a dependenței noastre este prezentată în Fig. 43. Salvați rezultatele exercițiului 1, vom avea nevoie de ele mai târziu (vezi Laboratorul #6 de mai jos). y = -0,0054x2 + 0,6014x - 5,9667 R2 = 0,9817 Y=f(x) Yexper Polinomul (Yexper) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X Fig. 43 - 32 - 70 80 90 100 Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Exercițiul 2 Crearea și editarea suprafețelor într-un document Excel În exercițiul anterior, am analizat posibilitățile de vizualizare a unei dependențe de un singur parametru (o funcție depinde doar de o variabilă). În realitate, astfel de dependențe simple sunt destul de rare. Mai des trebuie să vă ocupați de funcții cu mai mulți parametri. Să ne uităm la cum să le vizualizăm folosind exemplul unei probleme cu doi parametri. Să avem ecuația: Z = sin X 2 − Y 2 + 5 ⋅ X ⋅ Y , (3) unde X și Y variază de la -5 la 5 cu un pas de 1. Este necesar să se traseze suprafața rezultatului obținut. Valori Z. Pentru a face acest lucru, mai întâi trebuie să construiți o matrice de date (Fig. 44). () Orez. 44 În celula B1, introduceți prima valoare Y = -5. Apoi executați comanda „Editare” ⇒ „Umplere” ⇒ „Progresie...”. În caseta de dialog care se deschide (Fig. 45), setați: „Locație” - prin linii, „Pas:” egal cu 1 și „Valoare limită:” egală cu 5. După aceea, faceți clic pe butonul Ok. Exact în același mod Fig. 45 valorile X din coloana A sunt completate, cu excepția faptului că „Locație” trebuie să fie pe coloană. Fă-o. După ce valorile argumentului sunt introduse în tabel, completați celula B2 cu formula pentru calcularea Z (3). Funcția Sin este situată în categoria matematică „Asistente de funcții”. - 33 - Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" Nu uitați că formula trebuie să conțină referințe mixte, deoarece valorile X trebuie întotdeauna selectate din coloana A, iar valorile Y din linia 1. Pentru a completa toate tabelele, utilizați marcatorul de umplere. Datele pentru construirea suprafeței sunt gata, tot ce rămâne este să o reprezentați pe diagramă. Ca și în exercițiul anterior, vom folosi „Asistentul Diagramei” (Fig. 32 – 35). Mai întâi, selectați matricea valorilor funcției (nu este nevoie să selectați valorile X și Y în Fig. 46!), deschideți expertul pentru diagrame în orice mod cunoscut și selectați tipul de diagramă „Suprafață”. Mai mult, construirea unei suprafețe nu este diferită de construirea unui grafic. Diagrama finală va arăta ceva ca cea prezentată în Fig. 46. ​​​​Puteți roti sau ajusta diagrama în caseta de dialog „Format 3D Surface” (Fig. 47), care este prezentată în Fig. 47 se deschide făcând clic dreapta pe pereții suprafeței și selectând elementul din meniul contextual „Vizualizare volum...”. - 34 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Lucrare de laborator Nr. 5 Exercițiul 1 Expresii logice în Excel Introduceți formula =7>5 în celula A1. Va returna TRUE. Să copiem conținutul lui A1 în A2 și să corectăm formula din A2: =3>5. Această formulă va returna FALSE. Partea dreaptă a ambelor formule reprezintă declarații, de exemplu. afirmații care pot fi considerate adevărate sau false. Să ne uităm la un alt exemplu. Să introducem numărul 2 în celula A4 și formula =A4>3 în celula B4. Formula returnează FALSE. Să introducem în A4 numărul 6. Formula returnează valoarea TRUE. B4 conține un predicat, adică o instrucțiune cu variabile (în acest caz există o singură variabilă). În funcție de valoarea variabilelor, predicatul poate lua valorile TRUE și FALSE. În acest exemplu, formula pare să răspundă la întrebarea: „Este numărul (sau rezultatul calculelor folosind formula) stocat în celula A4 mai mare decât 3? » În funcție de valoarea lui A4, răspunsul va fi DA (ADEVĂRAT) sau NU (FALS). În formula =A4>3, componentele sale (A4 și 3) pot fi considerate expresii aritmetice, doar foarte simple. Un exemplu mai complex: =(A4^2-1)>(2*A4+1). Puteți omite parantezele din această expresie deoarece operațiile aritmetice au prioritate față de operațiile de comparare, dar parantezele fac formula mai clară. Rezumam operațiunile de comparație în tabel. 1. Tabelul 1 > mai mare decât >= mai mare sau egal cu< <= меньше или равно меньше = <>egal nu egal Rețineți că simbolul mai mare sau egal cu este reprezentat prin două semne: > și =. Motivul este că nu există niciun semn ≥ pe tastatură. O declarație și un predicat au un nume comun - o expresie logică. Există operații logice care vă permit să construiți expresii logice complexe. Aceste operațiuni sunt implementate în Excel ca funcții (NU, ȘI, SAU). Pentru funcțiile logice, argumentele pot lua doar două valori: TRUE și FALSE. Funcția NOT poate avea un singur argument, dar funcțiile AND și OR pot avea două sau mai multe argumente. Exemplul 1 În celula A1 (numită z), scrieți orice număr. Aflați dacă aparține segmentului. Soluţie. Să atribuim celulei A1 numele z („Inserare” ⇒ „Nume” ⇒ „Atribuiți”). Să introducem în A1 numărul 3. Pentru ca z să aparțină segmentului, două predicate trebuie să fie adevărate în același timp: z ≥ 2 și z ≤ 5. În celula B1 vom plasa - 35 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency formula =I(z>=2;z<=5). Для ввода в формулу имени ячейки нажмите F3 для открытия списка имен. В В1 получим значение ИСТИНА. Следует предостеречь от неверного решения: формулы =2<=z<=5. Введите эту формулу в С1 и убедитесь, что она возвращает ЛОЖЬ! Коварство этой, на первый взгляд, такой естественной формулы в том, что Excel ничего не сообщает о ее некорректности. Пример 2 В ячейке А1 (с именем z) записано число. Выяснить, принадлежит ли оно одному из лучей на числовой оси: (-∞,2) или (5,∞). Решение. Для того чтобы z принадлежал хотя бы одному из лучей, нужно, чтобы был истинным хотя бы один из предикатов: z < 2 или z >5. În celula D1, plasați formula =OR(z<2;z>5). A1 conține numărul 3, deci formula returnează FALS. Problema ar fi putut fi rezolvată altfel, ținând cont de faptul că foaia de lucru conține o formulă pentru a verifica dacă numărul z aparține segmentului. Cele două raze menționate constituie complementul acestui segment pe axa numerelor. Să introducem formula =NOT(B1) în celula E1. Asigurați-vă, introducând numere diferite în celula A1, că formulele din celulele D1 și E1 dau rezultate identice. În practică, expresiile logice, de regulă, nu sunt folosite „în forma lor pură”. Expresia logică servește ca prim argument al funcției IF: IF(expresie_logică, valoare_dacă_adevărată, valoare_dacă_fals) Al doilea argument este expresia care va fi evaluată dacă expresia_logică returnează TRUE, iar al treilea argument este expresia care va fi evaluată dacă expresia_logică returnează FALSE. Exemplul 3 1. Introduceți în celula A2 o formulă care returnează z+1 dacă z >1 și z în caz contrar: = IF(z>1;z+1;z). (În Function Wizard, IF se află în categoria „Logic”, la fel ca și funcțiile AND, OR, NOT.); 2. Dacă z > 60, atunci în celula B2 se afișează mesajul „Valoare prag depășită”, altfel afișați z: =IF(z>60; „Valoare prag depășită”; z) Vă rugăm să rețineți că textul din formule este introdus în citate . 3. Dacă z ∈ , atunci returnează z dacă z< 10, то возвращать 10, если z >25, apoi returnați 25. Expresia pentru această condiție va arăta cam așa (să scriem formula în C2): =IF(z<10;10;ЕСЛИ(z<=25;z;25)) Теперь попробуйте менять значение z в ячейке А1, следя за тем как меняются значения в ячейках с формулами. - 36 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Упражнение 2 Итоговые функции в Excel Некоторые функции Excel возвращают одно значение, в то время как аргументом имеют блок или несколько блоков. Такие функции будем называть итоговыми. Наиболее часто используемой из таких функций является СУММ − для ее ввода на панели «Стандартная» даже размещена специальная кнопка. Эта функция как бы подводит итог колонке чисел − отсюда и название для всей группы. Эти функции входят в категории «Статистические» и «Математические». Функция СУММ допускает до 30 аргументов. Поэтому с ее помощью можно находить сумму чисел из нескольких блоков. Допустима, например, такая формула =СУММ(В2:В9;12;-4.96;А4:С18). Если в блоке в какой-либо ячейке находится текстовое значение, то оно считается равным нулю. Кроме суммы к итоговым функциям относятся, например: МАКС и МИН − вычисление максимального и минимального значений, СРЗНАЧ − среднее арифметическое значение и т.д. Прочие итоговые функции вы можете найти в «Справке». К итоговым можно отнести функции И и ИЛИ. Пример 4 данные Имеются метеостанции – количество осадков (в мм) (Рис. 48). Необходимо получить суммарное, максимальное, минимальное и среднемесячное количество осадков. Решение. Введите формулы: в В17 =СУММ(В3:В14); в В18: =МАКС(В3:В14); в В19: =МИН(В3:В14); в В20: =СРЗНАЧ(В3:В14). Далее эти формулы скопированы в С17:D20. В блоке Е17:Е20 подведены итоги за три года. В Е17 формула =СУММ(В17:D17), в Е18: т.д. На =MAKC(B18:D18) и Рис. 48 Рис. 49 - 37 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» результаты наложен формат: одна цифра после десятичного разделителя (кнопка «Уменьшить разрядность»). Существует две полезные функции, примыкающие к итоговым: СЧЕТЕСЛИ и СУММЕСЛИ. Их названия показывают, что они считают и суммируют не все данные, а только удовлетворяющие некоторому критерию. Функция СЧЕТЕСЛИ (интервал; критерий) подсчитывает в интервале (т.е. блоке) количество значений, удовлетворяющих критерию. Пример 5 Вычислим количество засушливых месяцев, т.е. месяцев, когда выпадало менее 10 мм осадков (Рис. 49). В ячейке В22 формула =СЧЕТЕСЛИ(В3:В14;"<10"). Критерий взят в двойные кавычки, как текстовая строка. Формула скопирована в C22:D22. В Е22 подсчитана сумма. Функция СУММЕСЛИ(интервал;критерий;сумм_интервал) устроена сложнее. Значения, удовлетворяющие критерию, выбираются из блока, заданного первым аргументом, суммируются соответствующие значения из сумм_интервал, заданного третьим аргументом. Если третий аргумент опущен, то суммируются ячейки в аргументе интервал. Предположим, нужно вычислить суммарные осадки, которые выпали в незасушливые месяцы. Дополним таблицу (Рис. 49). В ячейку В23 введена формула =СУММЕСЛИ(B3:B14;">=10"), apoi copiat în C23:D23. Folosind această funcție, puteți rezolva o problemă mai dificilă: care a fost cantitatea totală de precipitații în 1993 în acele luni care au fost uscate în 1994. Soluția este dată de formula = SUMNIF(D3 :D14;"<10"; С4:С15), которая возвращает значение 128,6. Поместите ее в ячейку В24. К итоговым можно отнести еще две функции: НАИБОЛЬШИЙ(блок;k) и НАИМЕНЬШИЙ(блок;k). Первая из этих функций возвращает k-e наибольшее значение из множества данных, а вторая − наименьшее. Пример 6 В ячейки A1:D1 введем набор чисел. В блок А2:А5 введем формулы, которые показаны в соседнем столбце Рис. 50 (Рис. 50). Для понимания работы функции важно отметить, что третье наибольшее значение в блоке не 2, как можно было бы подумать, а 6, т.е. совпадает со вторым наибольшим значением. И еще: если в блоке n элементов, то функция НАИБОЛЬШИЙ(блок,n) возвращает минимальное значение, что мы и видим в примере. Обратите внимание, что в А2:А5 получен исходный массив чисел, отсортированный по убыванию. Если в исходном блоке изменить какое-либо число, - 38 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» то блок А2:А5 будет автоматически перестроен. В этом отличие от операции сортировки, которая проводится самим пользователем при необходимости. Обязательно сохраните результаты примеров 4-6, так как мы будем их использовать позднее. Упражнение 3 Табличные формулы в Excel Табличные формулы − очень мощное средство Excel, позволяющее в формулах обращаться с блоками, как с обычными ячейками, давать компактные решения сложных задач. В некоторых задачах вообще невозможно обойтись без табличных формул. Пример 7 Пусть нам известна сумма прихода и сумма расхода. Необходимо вычислить доход как разность этих величин. Введите исходные данные (Рис. 51). Рис. 51 В ячейку D2 можно набрать формулу: =В2-С2, а затем скопировать ее в D3:D5. В этих ячейках появятся формулы =В3-С3 и т.д. Однако здесь фактически из вектор-столбца В2:В5 вычитается вектор-столбец С2:С5. Поэтому можно непосредственно вычесть из вектора вектор одной формулой, а не создавать отдельные формулы для компонент вектора. Создание имен. Для наглядности дадим векторам имена. Выделите диапазон со вторым и третьим столбцами таблицы (В1:С5) и дайте команду меню «Вставка» ⇒ «Имя» ⇒ «Создать». Диапазон В2:В5 получит имя «Приход», а диапазон С2:С5 имя «Расход». Ввод табличной формулы с использованием имен диапазонов. Прежде мы вводили формулу в отдельную ячейку. А сейчас введем ее в диапазон. Подробно опишем шаги. Выделим блок D2:D5. В этом блоке активна ячейка D2. Наберем знак равенства =. Нажмем функциональную клавишу F3. Появится диалоговое окно «Вставка имени». Выберем имя «Приход» и щелкнем Оk. Формула примет вид: =Приход. Наберем знак минус -. Вновь нажмем клавишу F3. В диалоговом окне «Вставка имени» выберем имя «Расход» и щелкнем Ok. Формула примет вид: =Приход-Расход. Нажмем сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=Приход-Расход}. - 39 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Прокомментируем шаги. На третьем и пятом шаге мы выбирали имя из списка имен. Можно было ввести имя непосредственно с клавиатуры, но предложенный метод проще и нет риска ошибиться в наборе имени. На шестом шаге мы нажимаем не Enter, как ранее при вводе формулы, a Shift+Ctrl+Enter (при нажатии клавиши Enter должны быть нажаты обе клавиши Shift и Ctrl). Это очень важно. Если бы мы нажали Enter, то формула была бы введена только в активную ячейку блока D2 (Проверьте!). Фигурные скобки, окружающие формулу, говорят о том, что это табличная формула. Эти скобки нельзя набирать вручную (формула будет воспринята как текст). Ввод табличной формулы. Разумеется, табличную формулу можно вводить и без использования имен. Скопируйте блок А1:С5 в А8:С12. Повторите все шаги. Выделите блок D9:D12. В этом блоке активной ячейкой является D12. Наберите знак равенства =. Выделите блок В9:В12, наберите знак минус -, выделите блок С9:С12, нажмите сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=В9:В12-С9:С12}. Мы получили две идентичные таблицы. Выделение блока с табличной формулой. Выделите одну из ячеек блока и нажмите клавишу F5 (эквивалент пункта меню «Правка» ⇒ «Перейти»). В диалоговом окне щелкните по кнопке «Выделить», установите переключатель «Текущий массив». Изменение табличной формулы. Попытайтесь очистить одну из ячеек, занятую табличной формулой. Например, выделите ячейку D8 и нажмите клавишу Del. В этом случае должно появится сообщение «Нельзя изменять часть массива». Удалить блок можно только целиком. Отредактировать формулу можно так: выделить блок с формулой, нажать функциональную клавишу F2, внести изменения в формулу, нажать сочетание клавиш Shift+Ctrl+Enter. (Попробуйте, например, ввести формулу {=Приход-Расход-1}, потом отмените это.) Коррекция табличной формулы при увеличении блока. Добавьте в обе таблицы на рабочем листе строку с данными: Год - 1996, приход - 240, расход – 200. Необходимо посчитать прибыль за 1996. Раньше, когда формулы записывались в отдельные ячейки, мы бы поступили просто: скопировали бы формулу из ячейки D5 в D6. Проделаем это для первой таблицы. Вместо ожидаемого 40 получим результат 50, т.е. число из первой ячейки блока с табличной формулой. Та же операция для второй таблицы даст правильный результат 40, но в строке формул мы увидим {=В13:В16-С13:С16} – образовался второй блок, что вовсе не входило в наши планы. Удалим формулы в ячейках D6 и D13. Правильное решение для первой и второй таблиц разное. Для первой таблицы изменим именованные блоки (выделим В1:С6 и «Вставка» ⇒ «Имя» ⇒ «Создать», для каждого имени Excel задаст вопрос: «Заменить» существующее определение имени?» Отвечаем «Да»). Выделяем D2:D6, нажимаем клавишу F2 (редактирование) и, ничего не изменяя в формуле, нажимаем клавиши Shift+ Ctrl+Enter. Для второй таблицы выделяем D8:D13, нажимаем клавишу F2 и редактируем формулу. Выделим в формуле подстроку В8:В12 и выделим блок В8:В13, также поступим с блоком С8:С12 либо просто заменим в адресах блоков цифру 2 на цифру 3. Нажимаем сочетание клавиш Shift+Ctrl+Enter. - 40 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Коррекция табличной формулы при уменьшении блока. Теперь мы хотим удалить в каждой из таблиц строку для 1996 г. Для первой таблицы вновь изменяем поименованные блоки (в ячейке D6 результат отображается как #Н/Д - недоступно). Выделяем блок с табличной формулой, нажимаем клавишу F2 и добавляем в самое начало формулы апостроф (он расположен на клавише с буквой "Э"). Формула превращается в текст. Вводим этот текст во все ячейки (клавиши Ctrl+Enter). Табличная формула прекратила существование. Очищаем последнюю строку таблицы. Выделяем блок D2:D5, нажимаем клавишу F2, удаляем апостроф, нажимаем клавиши Shift+Ctrl+Enter. Аналогично поступаем со второй таблицей. Как видим, процедура непростая и неприятная. Для решения задачи проще было воспользоваться простыми формулами. Но применение табличных формул, как мы убедимся, дает такие дополнительные возможности, что с неудобствами, связанными с изменениями этих формул, придется смириться. Упражнение 4 Дистрибутивные функции в Excel В Excel можно к блоку применить функцию (большое множество функций Excel), с тем, чтобы она вернула новый блок, содержащий значения функции для элементов исходного блока. Пример 8 Пусть в блоке А1:А4 записаны числа 1, 4, 9, 16. Поместим в B1:В4 табличную формулу {=КОРЕНЬ(А1:А4)}. Будет выведен столбец значений: 1, 2, 3, 4. Можно считать, что функция КОРЕНЬ была применена к вектору из четырех компонент и вернула новый вектор. (Разумеется, тот же результат можно было получить, записав в В1 формулу =КОРЕНЬ(А1) и скопировав ее в блок В2:В4.) Теперь рассмотрим функцию, которую нельзя применять к блоку. Поместим в D1:D2 логические значения ИСТИНА и ЛОЖЬ. В блок Е1:Е2 запишем табличную формулу {=И(D1:D2;”ИСТИНА”)}. Эта формула вернет значение ЛОЖЬ во всех ячейках блока Е1:Е2. Получается, что функцию КОРЕНЬ можно применять к массиву, а функцию И – нет. Функции, которые можно применять к списку, называются дистрибутивными. Продолжим пример с вычислением квадратного корня от элементов блока. Мы хотим вычислить сумму корней ∑ ai . Поместим в ячейку В5 формулу =СУММ(В1:В4). Результат, разумеется, 10. А теперь вычислим эту же сумму, не используя промежуточный блок В1:В4. Поместим в ячейку А5 табличную формулу {=СУММ(КОРЕНЬ(А1:А4))}. Обратите внимание, хотя формула возвращает значение в одной ячейке, она должна вводиться как табличная, т.е. ее ввод заканчивается нажатием комбинации клавиш Shift+Ctrl+Enter. Для сравнения введите в А6 эту формулу как обычную, – она вернет сообщение об ошибке #ЗНАЧ!. - 41 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Итак, к массиву, возвращаемому дистрибутивной функцией, можно применить итоговую функцию, которая возвращает всего одно значение. Но вводить такую формулу нужно как табличную. Пример 9 Рис. 52 Функцию ИЛИ можно использовать в табличных формулах, но как итоговую, т.е. если ИЛИ имеет всего один аргумент и этот аргумент – блок. Пусть в блоке А1:В2 находятся названия планет (Рис. 52). В ячейку D2 введем табличную формулу {=ИЛИ(СОВПАД(А1:В2;D1))}. Функция СОВПАД возвращает логическое значение ИСТИНА или ЛОЖЬ в зависимости от совпадения или несовпадения своих аргументов – текстовых строк. Если бы в А4:В5 была введена формула массива {=СОВПАД(А1:В2;D1)}, то она вернула бы четыре значения (Рис. 52). Функция ИЛИ(А4:В5) возвращает значение ИСТИНА. Эти две формулы мы объединяем в одну табличную формулу, которую и ввели в D2. Пример 10 Вернемся к задаче обработки данных метеостанции (пример 4 и пример 5). Для расчета количества засушливых месяцев, т.е. месяцев, когда выпало менее 10 мм осадков, очень удобно использовать функцию СЧЕТЕСЛИ. Однако с ее помощью нельзя получить количество месяцев, на протяжении которых количество осадков лежало бы в диапазоне от 20 до 80 (назовем такие месяцы нормальными). Для этого необходимо использовать дистрибутивные функции. Скопируйте текст из ячейки А22 в ячейку А25 и откорректируйте его: «Количество нормальных месяцев». Сначала подсчитаем месяцы с нормальным количеством осадков. Будем использовать вспомогательный блок F3:H14 тех же размеров, что и блок с исходными данными. В ячейку F3 вводим формулу =ЕСЛИ(И(В3>20;B3<80);1;0) и копируем ее в остальные ячейки блока F3:H14. В блоке выводятся нули и единицы. Введенная формула является индикаторной функцией множества нормальных месяцев, т.е. 1 – выводится, когда количество осадков лежит в пределах между 20 и 80 мм и 0 – в противном случае. Остается подсчитать сумму таких месяцев. Для этого введем в ячейку F25 формулу =СУММ(F3:F14) и скопируем ее в блок G25:H25. А теперь решим эту же задачу без использования вспомогательного блока. Введем в В25 табличную формулу {=СУММ(ЕСЛИ(В4:В15>20;IF(B4:B15<80;1;0);0))} и скопируем ее в C25:D25. (Таким образом, машина, перебирая значения в указанном блоке, проверяет больше или меньше текущее число 20 и, если оно меньше, прибавляет к исходному число 0. В противном случае – проверяет выполнение второго условия (менее 80). Если и второе условие выполняется, то к исходному числу прибавляет 1. В противном - 42 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» случае – 0). Формула {=СУММ(ЕСЛИ(И(В4>20;B4<80);1;0))} не приведет к успеху, так как функция И не является дистрибутивной). Этот пример очень важен. Он Рис. 53 дает рецепт, как подсчитать в множестве количество элементов, удовлетворяющих определенному критерию. Нужно составить на основе вложенных функций ЕСЛИ индикаторную функцию подмножества и применить к ней итоговую функцию СУММ, введя формулу как табличную. Теперь вычислим суммарное количество осадков, выпавших в эти месяцы Введите в А26 текст «Осадки в нормальные месяцы», в В26 – табличную формулу {=СУММ(ЕСЛИ(В3:В14>20; IF(Q3:Q14<80;В3:В14;0);0))} и скопируйте ее в C26:D26. В E25 и Е26 введите формулы для суммирования значений в строках (выделите блок В25:Е26 и щелкните кнопку «Автосумма»). Вы получите блок, показанный на рис. 53. Пример 11 В блоке А1:А10 записана числовая последовательность. Проверьте, является ли она возрастающей. Решение. Перейдите на новый лист. Запишите любую последовательность чисел в блок А1:А10. Окончательное решение можно записать одной формулой (поместите его в ячейку A12). {=ЕСЛИ(СУММ(ЕСЛИ(А2:А10-А1:А9> 0;1;0))=NUMĂRĂ(A1:A10)1;„creștere”;„nu este în creștere”)) Să analizăm acum această formulă: A2:A10-A1:A9 (adică, A9 este scăzut din A10, A8 se scade din A9 etc.) – formează un bloc format din primele diferențe ale elementelor blocului inițial; IF(A2:A10-A1:A9>0;1;0) – alcătuiește un bloc de indicatori ai primelor diferențe pozitive; SUM(IF(A2:A10-A1:A9>0;1;0)) – numără numărul de elemente nenule din blocul indicator; COUNT(A1:A10)-1 – calculează dimensiunea blocului indicator, egală cu dimensiunea blocului original redus cu 1; dacă numărul de elemente diferite de zero din blocul indicator este egal cu dimensiunea blocului indicator, atunci secvența crește, altfel nu este. Încercați să construiți blocurile corespunzătoare și funcțiile rezultate din ele pas cu pas pentru a obține o înțelegere clară a modului în care este compusă formula finală. - 43 - Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" Exercițiul 5 Operații cu matrice în Excel Cele mai simple operații care se pot face cu matrice: adunarea (scăderea), înmulțirea cu un număr, înmulțirea, transpunerea, calculul matricea inversă. Exemplul 12 Adunarea matricelor și înmulțirea unei matrice cu un număr. Adăugați matrice M și N, unde − 1 0 4  2 − 3 7 M = și N =   2 − 3 5 .    − 1 5 6 Rezolvare. Să introducem matricele M și N în blocurile A1:C2 și E1:G2. În blocul A4:C5 introducem formula tabelară (=A1:C2+E1:G2). Vă rugăm să rețineți că a fost selectat un bloc care are aceleași dimensiuni ca matricele originale. Ce se întâmplă dacă selectați blocul A4:D6 înainte de a introduce o formulă? #N/A va apărea în celulele „extra”, adică. "Nu este disponibil." Și dacă selectați A4:B5? Doar o parte din matrice va fi scoasă, fără niciun mesaj. Verifică. Utilizarea numelor facilitează introducerea unei formule de calcul. Dați intervalelor A1:C2 și E1:G2 numele M și respectiv N (execuți comanda pentru fiecare bloc „Insert” ⇒ „Nume” ⇒ „Assign”). În blocul E4:G5, introduceți formula tabelului (=M+N). Rezultatul, desigur, ar trebui să fie același. Acum să calculăm o combinație liniară de matrice 2M-N. În blocul A7:C8 introducem formula tabelară (=2*M-N). Ar trebui să obțineți următoarele rezultate:  5 − 6 10 1 − 3 11 M +N = și 2 M − N = − 4 13 7  .    1 2 11 Exemplele luate în considerare ne conduc la ideea că operația obișnuită de înmulțire aplicată blocurilor nu este în întregime echivalentă cu înmulțirea matriceală. Într-adevăr, pentru operațiile cu matrice în Excel există funcții incluse în categoria „Matematică”: MOPRED - calculul determinantului unei matrice; MOBR – calculul matricei inverse; MUMULT – înmulțire matrice; TRANSPONERE – transpunere. Prima dintre aceste funcții returnează un număr, deci este introdus ca formulă obișnuită. Funcțiile rămase returnează un bloc de celule, așa că trebuie introduse ca formule de tabel. Prima literă „M” din numele celor trei funcții este o abreviere pentru cuvântul „Matrix”. Exemplul 13 Calculați determinantul și matricea inversă pentru matrice - 44 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency − 73 78 24 A =  92 66 25 .   − 80 37 10  Verificaţi corectitudinea calculului matricei inverse înmulţind-o cu cea iniţială. Repetați acești pași pentru aceeași matrice, dar cu elementul a33=10.01. Soluţie. Să plasăm matricea originală în blocul A1:C3. În celula B5 plasăm formula de calcul a determinantului =MOPRED(A1:C3). În blocul A7:C9 introducem o formulă de calcul a matricei inverse. Pentru a face acest lucru, selectați blocul A7:C9 (are trei rânduri și trei coloane, ca matricea originală). Să introducem formula (=MOBR(A1:C3)). Chiar dacă utilizați Function Wizard, trebuie să finalizați intrarea apăsând combinația de taste Shift+Ctrl+Enter (în loc să faceți clic pe butonul „Ok”). Dacă ați uitat să preselectați blocul A7:C9 și ați introdus formula în celula A7 ca o formulă Excel obișnuită (terminând prin apăsarea Enter), atunci nu trebuie să îl introduceți din nou: selectați A7:C9, apăsați tasta F2 ( tasta edit), dar nu modificați formula, doar apăsați tastele Fig. 54 Shift+Ctrl+Enter. Copiați blocul A1:C9 în blocul E1:G9. Schimbați ușor un element al matricei originale: în celula G3, în loc de 10, introduceți 10.01. Schimbările în determinant și în matricea inversă sunt izbitoare! Acest exemplu special selectat ilustrează instabilitatea numerică a calculului determinantului și matricei inverse: o perturbație mică la intrare produce o perturbare mare la ieșire. Pentru calcule ulterioare, vom atribui numele matricelor de pe foaia de lucru: A1:C3 - A, A7:C9 - Ainv, E1:G3 - AP, E7:G9 - APinv. Pentru ca aceste nume să apară în formulele deja introduse, selectați formulele corespunzătoare, selectați „Insert” ⇒ „Nume” ⇒ „Aplica” din meniu, selectați numele dorite în caseta de dialog și faceți clic pe „Ok”. Acum să verificăm corectitudinea calculului matricei inverse. În blocul A12:C14 introducem formula (=MUMULT(A,Ainv)), iar în blocul E12:G14 - formula (=MUMNOT(AP,APinv)). Ar trebui să obțineți un rezultat ca în fig. 54. După cum era de așteptat, matricele rezultate au fost aproape de identitate. - 45 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Rețineți că setul de operațiuni cu matrice în Excel este slab. Dacă trebuie să lucrați serios cu matrice, este mai bine să apelați la ajutorul unor pachete matematice precum MatLAB (Matrix LABoratory), Mathematica, Derive. - 46 - Copyright JSC Biroul Central de Proiectare BIBKOM & LLC Kniga-Service Agency Lucrare de laborator nr. 6 Exercițiul 1 Găsirea unei soluții În lucrarea de laborator nr. 4, am analizat un exemplu de găsire automată a dependenței funcționale Y = f(X). Să ne amintim că găsirea unei astfel de dependențe este necesară pentru a prezice valorile răspunsului - parametrul Y la ieșirea experimentului din factorul - variabile independente X la intrarea în sistem (a se vedea Lucrarea de laborator nr. 4). În unele Fig. În 55 de cazuri, funcțiile prezentate în Excel nu sunt suficiente. Prin urmare, este important să puteți selecta singur o astfel de funcție, folosind una dintre metodele de optimizare matematică, de exemplu, metoda celor mai mici pătrate. Esența sa este de a minimiza suma diferenței pătrate dintre datele experimentale (Yexper) și cele calculate (Ycalculation): n ∑ (Yexper,i − Y calcul,i) 2 , i =1 (4) unde n în problema noastră a fost egal la 10 Deschideți problema de laborator #4 și continuați să completați tabelul. Y-urile experimentale au fost deja introduse. Acum să umplem tabelul cu Y calculat. Pentru a face acest lucru, vom avea nevoie de un tabel suplimentar de coeficienți, ale cărui valori le vom echivala mai întâi cu 1 (Fig. 55). Acum introduceți formula polinomială de gradul doi (1) pentru calculul Y (Fig. 55). Următoarea sarcină este să Fig. 56 selectați coeficienții ecuației astfel încât diferența dintre Ycalculation și Yexpert să fie minimă. Pentru a face acest lucru, trebuie să introduceți formula pentru calcularea diferenței pătrate (3) și formula pentru calcularea criteriului Pearson pentru a evalua acuratețea calculului nostru (Fig. 56). Ambele formule sunt încorporate în Excel și servesc ca exemple de funcții pentru care puteți face fără a introduce formule de foi de calcul (vezi Laboratorul #4 de mai sus). Deschideți Expertul pentru funcții în orice mod pe care îl cunoașteți. În categoria „Matematică”, selectați formula SUMVARIEF și faceți clic pe Ok. În a doua fereastră a Expertului de funcții din Fig. 57 introduceți matricea Yexpert ca matrice_x și matricea Ycalculation ca matrice_y și faceți clic pe Ok. Formula de calcul a testului Pearson se află în categoria „Statistică” (funcția PEARSON). În a doua fereastră a Expertului funcții, introduceți și matricea Yexpert ca matrice_x și matricea Ycalculation ca matrice_y și faceți clic pe Ok. Pentru a găsi valorile coeficienților, Excel are un add-in Solver care vă permite să rezolvați probleme de găsire a valorilor mai mari și cele mai mici, precum și să rezolvați diverse ecuații. Selectați celula în care este introdusă formula de calcul a diferenței pătrate și executați comanda „Instrumente” ⇒ „Căutare soluție”. Dacă nu există o astfel de comandă în meniul „Service”, atunci trebuie mai întâi să executați comanda „Service” ⇒ „Suplimente” și în caseta de dialog care se deschide, selectați comutatorul din coloana „Căutare soluție” ( Fig. 57), și abia apoi executați comanda „Service” ⇒ „Găsirea unei soluții”. În caseta de dialog „Căutare soluție” (Fig. 58), introduceți următorii parametri: adresa celulei țintă cu valoarea selectată (adresa celulei cu formula pentru suma diferenței pătrate), dacă ati selectat-o ​​in prealabil, adresa este plasata automat; în câmpul „Egal cu:”, setați butonul radio la „valoare minimă”; - 48 - Copyright JSC „CDB „BIBKOM” & LLC „Agency Kniga-Service” în câmpul „Culele în schimbare”, introduceți intervalul de celule a coeficienților de modificare. Butonul „Opțiuni” este folosit pentru a modifica și configura parametrii de căutare. În fig. 59 numărul lor include: metoda de rezolvare a problemei, timpul de calcul și acuratețea rezultatelor. Cu toate acestea, în majoritatea cazurilor, utilizarea setărilor implicite este suficientă. Căutarea unei soluții se efectuează după ce faceți clic pe butonul „Run”. Dacă căutarea unei soluții este finalizată cu succes, rezultatele calculului sunt introduse în tabelul sursă, iar pe ecran apare caseta de dialog „Rezultatele căutării soluției” (Fig. 59), cu care puteți salva soluțiile găsite în sursă. tabel, restaurați valorile originale și salvați rezultatele căutării soluției sub forma unui script, generați un raport cu rezultatele operațiunii de căutare a soluției. Comparați valorile coeficienților rezultate cu coeficienții din ecuația liniei de tendință. Adăugați valorile Y calculate la grafic. Pentru a face acest lucru, accesați fereastra diagramei, faceți clic dreapta oriunde pe ea și selectați comanda „Date sursă” din meniul contextual. În caseta de dialog cu același nume care se deschide (Fig. 60), accesați fila „Rând” și faceți clic pe butonul „Adăugați”. În câmpul „Nume”, faceți clic pe butonul de minimizare a ferestrei, Fig. 60 mergeți la foaia cu datele dvs., selectați celula antet a coloanei Ycalculation și reveniți la fereastră folosind butonul de maximizare a ferestrei. În mod similar Fig. 58 - 49 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency introduceți „X Values” (gamă de celule cu X sau valori de temperatură) și „Y Values” (gamă de celule cu valori Y calculate). Când ați terminat de introdus, apăsați butonul Ok. Vă rugăm să rețineți că punctele de calcul Y se află pe linia de tendință pe care am construit-o mai devreme (Fig. 61). În cele din urmă, asigurați-vă că salvați fișierul, îl vom folosi în lecția următoare (vezi Laboratorul #7 de mai jos). y = -0,0054x2 + 0. 6014x - 5,9667 2 R = 0,9817 Y=f(x) Yexper Ycalcul Polinom (Yexper) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X Fig. 61 - 50 - 70 80 90 100 Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Lucrări de laborator Nr. 7 Exercițiul 1 Găsirea unei soluții la o problemă cu doi parametri în Excel În lecțiile anterioare, ne-am uitat la posibilitățile de prelucrare și vizualizarea unei dependențe de un parametru (funcția depinde doar de o variabilă). În realitate, astfel de dependențe simple sunt destul de rare. Mai des trebuie să vă ocupați de funcții cu mai mulți parametri. Să luăm în considerare cum să procesăm astfel de dependențe și cum să le vizualizăm folosind exemplul unei probleme cu doi parametri. Să fie efectuat un experiment, de exemplu, s-a măsurat dependența unui parametru de temperatură și presiune. Temperatura medie a fost de 100°C. Etapa de schimbare este de 50°C. Presiune medie – 2 atm. Pasul de schimbare este de 1 atm. Un astfel de sistem va fi descris prin relația: Y = f (X1, X 2), (5) care este o suprafață care este adesea prezentată într-o formă similară unei hărți de contur (Fig. 62). Orez. 62 - 51 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Pentru a găsi această dependență pentru cazul nostru, vom folosi șablonul din lecțiile 4 și 6. Pentru a face acest lucru, deschideți fișierul salvat și mergeți la foaia cu date. Faceți clic pe comanda rapidă Sheet și selectați comanda „Mutare/Copiere” (Fig. 63). În caseta de dialog care se deschide (Fig. 64), puteți alege unde vrem să mutăm (copiați) foaia noastră (în cartea curentă sau în una nouă). Selectați titlul cărții curente; în fața cărei foi dorim să plasăm foaia curentă sau o copie a acesteia. Selectați „(mutare la sfârșit)”. Nu uitați să bifați caseta de selectare „Creați o copie”, altfel foaia se va muta pur și simplu la sfârșitul cărții. Apoi faceți clic pe Ok. În mod implicit, Excel creează o copie cu numele foii de lucru curente, adăugând numărul copiei la sfârșit între paranteze. Pentru comoditate, să-l redenumim. Pentru a face acest lucru, faceți clic pe eticheta foii și selectați comanda „Redenumire” (Fig. 63); Introduceți un nume nou, de exemplu, „Experiment_2” și apăsați tasta „Enter”. Mai întâi, să reconstruim tabelul de date sursă, așa cum se arată în Fig. 65. Selectați două celule din partea de sus a vechiului tabel (cele în care a fost plasat numele parametrului „Temperatura” și valoarea acestuia) și executați comanda „Insert” ⇒ „Cells...”. Aceasta va deschide caseta de dialog „Adăugați celule”, care va sugera locația lor (Fig. 66). Setați comutatorul în poziția „celule cu o schimbare în jos” și faceți clic pe butonul Ok. - 52 - Fig. 63 Fig. 64 Fig. 65 Fig. 66 Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Selectați coloana goală C (faceți clic pe antetul acestei coloane) și executați comanda „Insert” ⇒ „Columns”. Faceți modificările necesare în tabel (Fig. 65). Într-un mod similar, aduceți tabelul experimentului la forma prezentată în Fig. 67. Să vă reamintim că titlurile coloanei „Temperatura” și „Presiune” trebuie introduse folosind formule pentru a face piesa de prelucrat mai universală. Orez. 67 Să completăm acum datele din tabelul „Experiment”. Coordonatele punctelor 1 – 9 pot fi calculate în conformitate cu Fig. 62 după următoarele formule: Nr. 1 2 3 4 5 6 7 8 9 Temp. Xsr, 1-Step Xsr, 1 Xsr, 1+Step Xsr, 1-Step Xsr, 1 Xsr, 1+Pas Xsr, 1-Pas Xsr, 1 Xsr, 1+Step Press. Xsr,2-Step Xsr,2-Step Xsr,2-Step Xsr,2 Xsr,2 Xsr,2 Xsr,2-Step Xsr,2-Step Xsr,2-Step Când introduceți formule, nu uitați să faceți legături permanente a utiliza posibilitatea de copiere. Trebuie să luăm valorile Yexpert din experiment. Fie egale: Numărul punctului Yexper 1 1 2 7 3 5 4 17 5 25 6 15 Ycalculul trebuie calculat după formula: Ycalculul = A0 + A1 X 1 + A2 X 2 + A11 X 12 + A12 X 1 X 2 + A22 X 22 . - 53 - 7 3 8 10 (6) 9 4 Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Înainte de a introduce formula (6), este necesar să se modifice tabelul de coeficienți, așa cum se arată în Fig. 68, introducând valorile inițiale ale coeficienților 1. Pentru a selecta funcția, vom folosi metoda de minimizare a sumei pătratelor diferenței dintre datele experimentale (Yexper) și calculate (Ycalculation), despre care am discutat în ultimul lecţie. Orez. 68 Pe foaia noastră avem deja formulele de calcul a diferenței pătrate și formula de calcul a criteriului Pearson. Acum tot ce trebuie să faceți este să corectați linkurile din ele și să executați. Căutarea unei soluții se efectuează în același mod ca și în cazul unei funcții cu un singur parametru, dar deoarece dependența noastră este mai complexă, este necesar să deschidem subfereastra „Parametri” în dialogul „Căutare soluție”. caseta (Fig. 69) și setați următoarele opțiuni: abatere admisă – 1%; „Scalarea automată”; estimări – „Cuadratic”; diferențe – „Central”. Orez. 69 După aceea, faceți clic pe butonul Ok și în fereastra „Căutați o soluție” - „Run”. Dacă la prima încercare nu se obține o precizie satisfăcătoare, operația de căutare a soluției poate fi repetată. În cele din urmă, tot ce trebuie să facem este să construim suprafața. Pentru a face acest lucru, mai întâi construiți o matrice de date pe o foaie nouă (Fig. 70). Accesați o nouă foaie și introduceți titlul unui tabel. - 54 - Copyright JSC Central Design Bureau BIBKOM & LLC Agenția de servicii de carte Fig. 70 de valori X și Y sunt calculate folosind formule. Pentru a introduce prima valoare pentru presiune, introduceți „=”, apoi accesați foaia „Experiment_2” și faceți clic pe celula cu valoarea minimă a presiunii (în cazul nostru este 1) și apăsați tasta „Enter”. Trebuie urmați aceiași pași pentru a introduce valoarea minimă a temperaturii. Valorile ulterioare ale temperaturii și presiunii sunt calculate folosind formula: Yi = Yi −1 + Ymax − Ymin , l (7) unde Xi, Yi sunt valorile curente ale temperaturii și presiunii, respectiv, Xmin, Ymin sunt valorile minime ale temperaturii și respectiv ale presiunii, Xmax, Ymax – valoarea maximă a temperaturii și respectiv a presiunii, l este treapta de grilă (fie egală cu 10). Introduceți formule pentru calcularea celei de-a doua valori a presiunii și a temperaturii. Ele vor arăta cam așa: =B4+(Experiment_2!$G$11-Experiment_2!$G$3)/10. Pentru a intra pe al treilea etc. valorile de temperatură și presiune, utilizați marcatorul de umplere. Tot ce rămâne este să introduceți valorile funcției (5). Luați valorile coeficientului din foaia „Experiment_2”. Nu uitați că referințele la coeficienți trebuie să fie absolute, iar referințele la valorile de temperatură și presiune trebuie amestecate. Formula ar trebui să arate cam așa: =Experiment_2!$B$7+Experiment_2!$B$8*$A5+Experiment_2!$B$9*B$4 +Experiment_2!$B$10*$A5^2+Experiment_2!$B$11* $ A5*B$4+ Experiment_2!$B$12*B$4^2 Datele pentru construirea suprafeței sunt gata, tot ce rămâne este să o reprezentați pe diagramă. - 55 - Copyright OJSC „CDB „BIBKOM” & LLC „Kniga-Service Agency” Utilizați „Diagram Wizard” selectând tipul de diagramă „Surface” (vezi Laboratorul nr. 4). Diagrama finală va arăta ceva ca cea prezentată în Fig. 71. Fig. 71 - 56 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency 1. 2. 3. 4. 5. Bibliografie Fulton, D. Master Microsoft Excel 2000 pe cont propriu.10 minute per lecție. / D. Fulton. – M.: Editura Williams, 2001. – 224 p. Levin, A.Sh. Excel este foarte simplu! / A.Sh. Levin. – Sankt Petersburg: Peter, 2004. – 74 p. Bezruchko, V.T. Workshop la cursul „Informatică”. Lucrul cu Windows 2000, Word, Excel: manual. indemnizatie. / V.T. Fără mânere. – M.: Finanțe și Statistică, 2003. – 544 p. Lavrenov, S.M. Excel: Culegere de exemple și sarcini. / CM. Lavrenov – M.: Finanțe și Statistică, 2004. – 336 p. Vorobyov, E.S. Fundamentele informaticii. Tehnici de lucru în mediul MS Office. Manual indemnizatie / E.S. Vorobyov, E.V. Nikolaeva, Vorobyova F.I., Kazan. stat tehn. univ. Kazan, 2005. – 84 p. - 57 - Copyright JSC Biroul Central de Proiectare BIBKOM & LLC Agenția de servicii de carte Cuprins Lucrări de laborator Nr. 1. .................................................. ...................................................... 3 Exercițiul 1 . Concepte de bază legate de funcționarea foilor de calcul Excel........................................ ............ ... 3 Exercițiul 2. Aplicarea tehnicilor de bază ale foilor de calcul: introducerea datelor într-o celulă. Formatarea fontului. Modificarea lățimii coloanei. Completare automată, introducerea unei formule, încadrarea tabelului, alinierea textului la centrul selecției, un set de indice și indice .............. ........... 6 Lucrări de laborator nr. 2 ................................. ................................................... ................ 10 Exercițiul 1. Consolidarea abilităților de bază în lucrul cu foi de calcul, familiarizarea cu conceptele: sortarea datelor, tipuri de aliniere a textului într-o celulă, format numeric... ................... 10 Exercițiul 2. Introducerea conceptului „referință absolută”, stabilirea valorii exacte a lățimii coloanei folosind comenzile din meniul orizontal. Inserarea unei funcții utilizând vrăjitorul de funcții.................................. ................ ................................. 13 Exercițiul 3. Introducerea conceptului de „nume de celulă”....... .............................. .......... 16 Lucrări de laborator Nr 3. ................................. ....................... ................................. ................ 19 Exercițiul 1: Schimbarea orientării textului în celulă, familiarizarea cu capabilitățile bazelor de date Excel. Sortarea datelor după mai multe chei.................................................. .................... ........................... 19 Lucrări de laborator nr. 4 ................................. ........... ............................................. ..... ................ 27 Exercițiul 1. Crearea și editarea graficelor într-un document Excel......... 27 Exercițiul 2. Crearea și editarea suprafețelor în document Excel .. 33 Lucrări de laborator Nr. 5 ............................................ .. ................................................ ........ .. 35 Exercițiul 1. Expresii logice în Excel ................................ .............. .............. 35 Exercițiul 2. Total funcții în Excel.............. .......................................................... .... 37 Exercițiul 3. Formule de tabel în Excel .......... ............................ ................... 39 Exercițiul 4 Funcții de distribuție în Excel ........................ ............................ ... 41 Exercițiul 5. Operații cu matrice în Excel ........... .......................... ................. 43 Lucrări de laborator Nr 6 . ............................ . .................................................. ...... .............. 47 Exercițiul 1. Găsirea unei soluții.............. ............................................................. ................... 47 Lucrări de laborator Nr. 7 ......................... .......................................................... ............................ 51 Exercițiul 1. Găsirea unei soluții la o problemă cu doi parametri......... ............................. 51 Bibliografie................... .. .................................................. .... ........................ 57 - 58 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Editor: T .M. Licenta Petrova Nr 020404 din 6 martie 1997. Semnat pentru tipar.Hârtie de scris. ed. academic. l. 2005. Format de imprimare 60x84 1/16 conventional. cuptor l. Tiraj 100 de exemplare. Ordinul „C” 60 Editura Universității Tehnologice de Stat Kazan Laboratorul Offset al Universității Tehnologice de Stat Kazan 420015, Kazan, K. Marksa, 68

Arhiva conține 6 lucrări de laborator pe tema EXCEL, în cadrul cărora elevii își vor consolida cunoștințele despre editorul de foi de calcul și vor lucra cu acesta în practică, iar profesorul le va putea evalua.

„Laboratorul 1 Excel”

Lucrare de laborator nr 1

„Crearea și formatarea unui tabel”

Scop: Învățați să creați și să formatați un tabel în editorul de foi de calcul Excel.

Progres:

Creați un tabel ca următorul pe prima foaie de lucru.

Când creați un tabel, aplicați următoarele setări:

Textul principal al tabelului este în font Courier New, dimensiunea 12;

textul este centrat în raport cu limitele celulei;

Pentru a face textul să se întinde pe mai multe linii într-o celulă, utilizați modul Format – Celulă – Aliniere;

adăugați 1 rând în partea de sus a mesei;

introduceți rubrica „Minerale”

colorează titlul în verde

Încadrați tabelul în albastru, pentru aceasta folosiți modul Format – Celulă – Chenar.

Salvați tabelul finalizat într-un folder de rețea.

Vizualizați conținutul documentului
„Lab 4 Excel”

Lucrare de laborator nr 4

„Se lucrează cu o masă. Construirea unei diagrame"

Scop: consolidarea abilităților în lucrul cu Diagram Wizard.

Progres:

Creați un tabel ca următorul în Foaia de lucru 1.

Completați coloana „medie” calculând-o pentru fiecare hobby folosind funcția Medie.

Folosind butonul „Reduceți adâncimea de biți”, În bara de instrumente Formatare, reduceți valoarea locului din coloana „medie” la numere întregi.

Folosind Chart Wizard, construiți o versiune tridimensională a unei diagrame circulare folosind coloanele: „tip de interes” și „valoare medie”. Diagrama ar trebui să conțină:

Nume,

Semnături de date în acțiuni,

Legenda situată în partea de jos

În segmentul „croaziere pe mare”, schimbați culoarea în albastru strălucitor.

Vizualizați conținutul documentului
„Laboratorul 6 Adresarea absolută în Excel”

Lucrare de laborator nr 6

„Introduceți și lucrați cu formule. Adresarea absolută și relativă a celulelor.”

Obiective: Utilizați diferite tipuri de adresare în calcule folosind formule matematice. Dezvoltarea capacității de a generaliza cunoștințele dobândite și de a le aplica în mod consecvent în procesul de executare a muncii. Dezvoltarea capacității de a utiliza diferite tipuri de adresare la rezolvarea diferitelor tipuri de probleme. Insuflarea abilităților de calcul în ET Excel. Cultivarea acurateței și a preciziei la scrierea formulelor matematice.

Progres:

1) Pe foaia 1 Creați următorul tabel:

În coloana E, calculați costul fiecărui articol.

În celula E10, calculați costul total al articolului folosind AutoSum.

Completați coloana F calculând cota de cumpărare folosind formula: Cotă de achiziție = Cost / Cost total.

Notă:

2) Pe foaia 2, creați și completați următorul tabel:

Efectuați calcule pe toate rândurile tabelului.

Formule de calcul:

Product output = Numărul de produse produse * Prețul de vânzare al unui produs

Costul produselor fabricate = Numărul de produse produse * Costul unui produs

Profit din vânzări de produse = Product output - Costul produselor produse,

Rentabilitatea produsului = Profit din vânzările de produse/Costul produselor fabricate

Pe linia pentru calcularea profitabilității produsului, adăugațiDespre format de cent numere. Efectuați alte calcule înDe format blând .

Formule pe coloane "CU" copiați prin autocopiere la dreapta de-a lungul liniei din coloane « D » Și „E”.

Vizualizați conținutul documentului
„Lucrarea de laborator nr. 3 în Excel”

Lucrare de laborator nr 3

„Introducerea și lucrul cu formule.”

Scop: consolidarea abilităților în completarea, editarea, proiectarea foilor de calcul și utilizarea formulelor în ele.

Progres

Pe Foaia 1 creați tabelul prezentat în figură:

Copiați tabelul pe care l-ați creat Foaia 2.

În tabelul de pe foaia 1 din celula C14, utilizați autosum pentru a găsi valoarea maximă a ariei;

În celula D 14 în același mod valoarea minimă a populației.

Completați intervalul E3:E13 calculând densitatea populației folosind formula: populație / zonă.

În celula E14, tipăriți suprafața medie a populației.

Arată profesorului rezultatul muncii tale

Vizualizați conținutul documentului
„Lucrarea de laborator nr. 5 pe excel”

Lucrare de laborator nr 5

„Crearea graficelor de funcții matematice folosind expertul pentru diagrame”

Scop: Consolidarea abilităților în construirea de funcții matematice folosind Chart Wizard din editorul de foi de calcul Excel.

Progres:

Exercitiul 1

Folosind Chart Wizard, creați un grafic al funcției y = x 3 pe foaia 1.

, cu un pas de schimbare de x 0,5.

Sarcina 2

Folosind expertul pentru diagrame din foaia 2, creați un grafic al funcției

Graficul este construit pe interval
cu un pas de schimbare de x 0,2.

Explicație: valoare
poate fi înlocuit cu valoarea

Sarcina 3

Pe foaia 3, creați următorul tabel pentru a calcula costul pachetelor de călătorie în ruble la prețul indicat în dolari și la cursul de schimb al dolarului.

Arată-ți munca profesorului

Vizualizați conținutul documentului
„Lab 2 Excel”

Lucrare de laborator nr 2

„Introduceți și lucrați cu formule. Completarea automată a tabelelor"

Scop: Stăpânirea tehnologiei de introducere a textului și a datelor numerice, introducerea și calcularea formulelor.

Progres:

Creați următorul tabel:

Introduceți datele inițiale în tabel:

coloana 1 – numere de la 1 la 6;

Coloana a 2-a – orice șase nume de familie;

Coloana a 3-a – orice sumă de salariu în intervalul de la 1000 la 10000;

A patra coloană – orice sumă de bonus în intervalul de la 100 la 3000.

Coloana a 5-a – Salariu angajat + bonusuri angajat

Coloana a 6-a – total acumulat / 100 * 13

Coloana a 7-a - total acumulat - impozit pe venit.

Notă:

amintiți-vă că orice formulă începe cu semnul =;

Rețineți că puteți utiliza întotdeauna completarea automată.

Ministerul Educației și Științei

Federația Rusă

Instituție de învățământ autonomă de stat federală

studii profesionale superioare

Universitatea Națională de Cercetare Nucleară „MEPhI”

Institutul Tehnic și Inginerie Volgodonsk - filiala Universității Naționale de Cercetare Nucleară MEPhI

Crearea de tabele

INSTRUCȚIUNI METODOLOGICEla munca de laborator

în informatică în programmicrosoftexcela

Volgodonsk 2010

UDC 519.683(076.5)

Referent Ph.D. tehnologie. Științe Z.O. Kavrishvili

Compilat de V.A. Buzdugan

Crearea de tabele. Ghid pentru lucrul de laborator în programul Microsort Excel. 2010. 13 p.

Orientările conțin explicații și recomandări pentru efectuarea lucrărilor de laborator în cadrul cursului de informatică în programul Microsort Excel.

_____________________________________________________________________________

ã Institutul Volgodonsk NRNU MEPhI, 2010

ã Bulava V.A., 2010

Lucrări de laborator Crearea de tabele în Excel folosind automatizarea introducerii datelor.

Scopul lucrării. Consolidați cunoștințele acumulate în crearea, editarea și proiectarea tabelelor în Excel.

Formularea problemei.

Calculați valoarea funcției y = f(X)/ g(X) pentru toți X pe interval [ A, b] în trepte La. Înţeles functions f(X) , g(X) , valoarea capetelor intervalului AȘi bși valoarea pasului La este dat din tabelul 1în Anexă conform opţiunii pentru o anumită specialitate.

Soluția trebuie obținută sub forma tabelelor „Principal” și „Auxiliar”.

Valori calculate ale funcției la copiați în coloană LA fără formule .

Excel este lansat folosind comenzile Start → Programe →Microsortareexcela.

Când creați un tabel, în primul rând, combinați celulele A1:H1 și plasați textul „Tabele” în centru.

În a doua linie, combinați celulele A2:E2 și plasați textul „Principal” în centru. Îmbinați celulele G2:H2 și plasați textul „Auxiliar” în centru

În celula A3, introduceți textul „Numărul articolului”. În celulele B3:F3 plasați numele coloanelor în mod corespunzător: X ; f(X)=…( conform opțiunii dvs.); g(X)=…( conform opțiunii dvs.); y= f(X)/ g(X).

În celulele G3:H3 plasați numele coloanelor în consecință: A ; La.

Când completați automat datele din tabelul principal în formule, utilizați adresarea celulelor absolută, relativă și mixtă.

În tabelele „Principal” și „Auxiliar”, conținutul celulelor ar trebui să fie aliniat la centrul celulei și să aibă o dimensiune a fontului de 12 pt.

Culoarea fontului numelor tabelelor ar trebui să fie albastră.

Colorați marginile exterioare ale tabelelor în albastru, marginile interne în verde și culoarea de umplere a celulei cu galben.

Formular de raportare.

Furnizați rezultatele muncii de laborator sub forma unui raport în formă tipărită sau electronică.

Versiunea tipărită a raportului trebuie să conțină:

a) pagina de titlu;

b) scopul lucrării;

c) enunţarea problemei;

d) rezultatul îndeplinirii sarcinii.

2. Furnizați rezultatul lucrărilor de laborator în formă electronică pe o dischetă de 3,5 inchi sub forma unui fișier numit „Tabele”.

Întrebări de control.

Ce este adresarea absolută, relativă, mixtă?

Cum se completează automat celulele cu numere și formule?

Care sunt diferitele moduri de a alinia conținutul celulei?

Cum pot schimba culoarea și grosimea liniilor marginilor exterioare și interioare ale mesei?

Cum pot schimba culoarea de fundal a celulelor tabelului?

Exemplu tipic.

Calculați valoarea funcției y = x∙sin(x)/(x+1) pe segmentul cu pas de 0,1. Furnizați soluția sub forma unui tabel. Valori calculate ale funcției la copiați în coloană LA fără formule .

Soluţie.

În acest caz f(X) = X∙ păcat(X) , g(X) = X+1 , A =0 , b = 2 , k = 0.1

1. În primul rând al tabelului, selectați celulele A1:H1. Hai să executăm comanda Format → Celule, în fereastra care se deschide, extinde fila nivelareși selectați elementul fuzionarea celulelor. În centrul celulelor îmbinate, introduceți textul „Tabele”.

2. În mod similar, în a doua linie, îmbinați celulele A2:E2 și plasați textul „Principal” în centru și îmbinați celulele G2:H2 și plasați textul „Auxiliar” în centru.

3. În al treilea rând din celula A3, introduceți textul Nu. ( numele primei coloane a tabelului ) , în celula B3 – X(numele celei de-a doua coloane a tabelului ), celula C3 – f(X)= X∙ păcat(X) , în celula D3 – g(X)= X+1 , în celula E3 – y=f(X)/ g(X) , în celula G3 – A, în celula H3 – k.

4. În celula A4 intrăm 1 și umpleți celulele A5:A24 cu numere de la 2 la 21. Pentru a face acest lucru, selectați celula A4 (faceți-o actuală), aceasta va fi evidențiată într-un cadru negru. Mutați cursorul mouse-ului peste marcatorul de umplere (cruce neagră în colțul din dreapta jos al celulei) și apăsând butonul din dreapta al mouse-ului, trageți marcatorul de umplere de-a lungul coloanei A astfel încât cadrul negru să acopere celulele A5:A24. Prin eliberarea butonului drept al mouse-ului, selectați elementul din meniul care se deschide completati. Celulele A5: A24 vor fi completate cu numerele 2;3;4...

5. În celula G4, introduceți valoarea 0 (valoarea capătului din stânga intervalului).

6. În celula H4, introduceți valoarea 0,1 (dimensiunea treptei).

7. Completați coloana ÎN valorile X:

În celula B4 introducem formula =$ G$4 (valoarea inițială a lui x), semnul $ indică adresarea absolută. În celula B5 introducem formula =B4+$H$4. Aceasta înseamnă că valoarea inițială a lui x va fi mărită cu valoarea pasului;

Folosind metoda de completare automată, completați celulele B5:B24 cu această formulă. Selectați celula B5. Treceți mouse-ul peste marcatorul de umplere și faceți clic stânga butonul mouse-ului, trageți marcatorul de umplere astfel încât cadrul negru să acopere celulele B5:B24. Coloana B va fi completată cu numerele 0; 0,1; 0,2;…, iar formulele corespunzătoare vor fi în bara de formule.

8. Completați coloana C cu valorile funcției f(x)=x∙sin(x). În celula C4, introduceți formula =B4∙sin(B4). Să umplem celulele C5:C24 cu această formulă folosind metoda de completare automată.

9. Completați coloana D cu valorile funcției g(x)=x+1. În celula D4, introduceți formula =B4+1. Să umplem celulele D5:D24 cu această formulă folosind metoda de completare automată.

10. Completați coloana E cu valorile funcției y=f(x)/g(x). În celula E4, introduceți formula =C4/D4 și completați celulele E5:E24 cu această formulă folosind metoda de completare automată.

11. Să încadram tabelele:

12. Schimbați culoarea de fundal a celulelor tabelelor principale și auxiliare:

selectați masa principală;

introduceți comenzile din meniu Format → Celule → Vizualizare.În fereastra care se deschide, selectați culoarea galbenă. Faceți clic pe butonul OK.

Selectați tabelul auxiliar și, în mod similar, schimbați culoarea de fundal a celulelor.

13. În tabelul principal, valorile obținute în urma calculelor la copiați în coloană LA fara formule:

selectați celulele E4:E24;

mutați cursorul mouse-ului peste conturul cadrului negru, astfel încât acesta să ia forma unei săgeți;

apăsând butonul drept al mouse-ului și fără a-l elibera, mutați indicatorul mouse-ului în celula K4;

Prin eliberarea butonului drept al mouse-ului, în meniul contextual care se deschide, selectați elementul copiați numai valorile.

În urma lucrărilor, obținem următoarele tabele:

EDITOR DE FOI DE TABLARE MICROSOFT EXCEL

Scopul lucrării:

Explorați capacitățile editorului de foi de calcul Excel 2007.

Dobândiți abilități în lucrul cu tabele, formule și funcții.

Exercițiu:

Când vă pregătiți pentru munca de laborator, familiarizați-vă cu elementele ferestrei excela2007; studiul metodelor de introducere a informațiilor, selectare, copiere, mutare și adăugare a elementelor de tabel; învață cum să formatezi conținutul celulei; luați în considerare în detaliu regulile de sintaxă atunci când scrieți formule, utilizarea legăturilor în formule și, de asemenea, familiarizați-vă cu conceptul de „funcție” înexcela 2007.

Învață cum să faci treaba.

Instrucțiuni

Exel este un procesor de tabel, adică un program conceput pentru a automatiza lucrul cu matrice mari de numere prezentate sub formă de tabel. Programele din această clasă sunt, de asemenea, numite foi de calcul.

Există mai multe abordări diferite pentru utilizarea Excel. Ele diferă în ce mijloace sunt utilizate și ce rezultate sunt obținute. Scopul principal al programului este de a automatiza calculele în tabele numerice, atunci când modificarea unei valori într-o celulă duce automat la o modificare a datelor în alte celule asociate acesteia. Acest stil de lucru este tipic pentru economiști, contabili, lucrători bancar și manageri responsabili de dezvoltarea întreprinderilor. Se bazează pe faptul că celulele pot conține nu numai numere, ci și formule. Dacă o celulă conține o formulă, atunci rezultatul calculului folosind această formulă este afișat pe ecran ca valoare numerică a celulei. Când se modifică valorile din celulele incluse într-o formulă, se modifică și rezultatul calculului formulei.

Pe lângă cele mai simple formule aritmetice din celule, puteți folosi funcții matematice și chiar microprograme scrise în limbaj VBA (Vizual De bază pentru Aplicații – Vizual De bază pentru aplicații). Acest nivel de utilizare a Excel este tipic în mediul academic. Excel este un instrument ideal pentru efectuarea de calcule statistice și pentru prelucrarea rezultatelor experimentelor, pentru pregătirea graficelor și diagramelor.

EXCEL ELEMENTE DE FEREASTRĂ

Pentru a lansa Excell, ar trebui să mergeți la meniustart , în submeniu Toate programele, grup de programe deschisMicrosoft Birou , și apoi selectați elementulMicrosoft Birou excela 2007 .

După lansare, editorul Excel va deschide automat o carte goală cu titlul Cartea 1, care va fi afișat în Linia de titlu, situat în partea de sus a ferestrei.

Spre deosebire de versiunile anterioare de Excell2007 are o interfață ușor modificată (Fig. 1). La fel ca în editorCuvânt, aici avem:

buton Birou - servește la afișarea unei liste de acțiuni posibile cu un document (deschidere, salvare, imprimare etc.), precum și pentru setarea parametrilor Excel.

panglica - file, care înlocuiesc meniurile și barele de instrumente tradiționale. Unele comenzi devin disponibile numai la editarea anumitor obiecte. De exemplu, comenzile de editare a diagramelor vor apărea numai dacăutilizatorul va dori să îl schimbe și să îl activeze făcând clic pe el cu mouse-ul.

Bara de stare

Fig.1 interfata Excel

Elementele rămase ale interfeței Excel rămân aceleași. Să luăm în considerare pe scurt scopul lor.

Bara de formule folosit pentru a introduce și edita conținutul celulei active sau formula pe care o conține. Pentru a introduce date, trebuie să selectați o celulă plasând cursorul pe ea (activând-o) și să introduceți datele în bara de formule, apoi faceți clic pe butonul introduceîn bara de formule sau cheie introduce. Datele apar în bara de formule pe măsură ce le introduceți în celula selectată.

Puteți afișa sau ascunde bara de formule de pe ecran setând opțiunea cu același nume în filă Vedere. Pentru a face acest lucru, selectați fila indicată și faceți clic pe săgeata din dreapta comenzii Arată sau ascunde, apoi bifați sau debifați opțiunea Bara de formule. Aici puteți verifica și opțiunile adecvate pentru afișarea unei grile de celule (opțiunea Net)și titluri de rând și coloane (opțional Titluri).

Câmp de nume - Acest câmp este situat în partea stângă a barei de formule și afișează numele celulei active (de exemplu, A1) sau obiectul selectat (de exemplu, Diagrama 1). În acest câmp puteți, de asemenea, să atribuiți un nume unei celule sau unui interval de celule/

Fisa de lucru un registru de lucru conține celule în care pot fi plasate date. Foaia este împărțită prin linii de grilă în coloane și rânduri. Fiecare coloană are un titlu de literă corespunzător, care este afișat în partea de sus, iar fiecare rând are un număr ca titlu, care este afișat în partea stângă.

Bara de stare situat în partea de jos a ferestrei Excel. În partea stângă se afișează numele operației care se efectuează (deschiderea sau salvarea unui fișier, copierea celulelor sau înregistrarea unei macrocomenzi etc.). Un indiciu poate fi afișat și aici, de exemplu, când faceți clic pe marginea unui bloc de celule selectat, este afișat un indiciu despre cum să trageți acest bloc; când faceți clic pe marcatorul de umplere (este afișat un indiciu despre cum să umpleți celulele cu o serie de date etc.) Partea dreaptă a barei de stare conține comenzi rapide pentru comutarea modurilor de vizualizare a documentelor, un buton Scară, care deschide o casetă de dialog cu același nume pentru selectarea scalei de afișare a documentului și un panou de zoom, unde puteți mări și micșora manual folosind glisorul. Puteți folosi și butoanele Scădea sau Crește, când se face clic, scara scade sau crește în trepte de 10%.

Bare de defilare servesc pentru a muta zona vizibilă a foii de lucru pe ecranul monitorului. Folosind cursorul, vă puteți deplasa rapid la partea dorită a foii de lucru active.

Separatoare de foi - Acestea sunt butoanele care se află în dreapta barei de defilare orizontale - și deasupra celei verticale. Prin apucarea separatorului cu mouse-ul și deplasându-l spre stânga sau în jos, puteți împărți fereastra în mai multe zone pentru a vizualiza simultan mai multe secțiuni ale foii, ceea ce este convenabil atunci când lucrați cu documente mari.

Lucrul cu foi

Fiecare registru de lucru conține implicit trei foi cu titluri standard; Foaia 1,Foaia 2,Foaia 3. Selectarea unei anumite foi se efectuează folosind comenzile rapide pentru foi din colțul din stânga jos al zonei de lucru. În mod implicit, eticheta apare cu un fundal mai deschis pentru foaia curentă și cu un fundal mai închis pentru toate celelalte. Pentru a selecta o foaie, faceți clic pe fila acesteia.

Cu ajutor separator situat în partea de jos a ecranului (în centru), puteți modifica dimensiunea zonei dintre filele foii și orizontală. bara de defilare(Fig. 2) pentru a mări spațiul pentru foi noi.

butoane de defilare, cu ajutorul căruia se defilează filele din registrul de lucru, sunt situate în stânga filelor. Butoanele cele mai exterioare derulează la prima sau ultima filă a registrului de lucru. Butoanele interne derulează la fila anterioară sau următoare registrului de lucru.

Notă. Numărul de foi pe care le are o carte nouă este setat în mod implicit utilizând opțiunea Număr de foi situat pe pagină De bază fereastră Parametrii excel , care se apelează prin comanda cu același nume din butonul de meniu Birou .

Orez. 2 Comenzi rapide

Pentru a schimba numele unei foi, trebuie mai întâi să selectați comanda rapidă a acesteia făcând clic stânga pe mouse, apoi făcând clic dreapta pentru a deschide meniul contextual în care să selectați comanda Redenumiți. De asemenea, puteți face dublu clic pe scurtătură. Ca rezultat, numele foii va fi evidențiat cu un fundal negru: După aceasta, trebuie să introduceți un nou nume de foaie în loc de cel vechi.

Dacă trebuie să adăugați o foaie nouă la registrul de lucru, ar trebui să rulați comanda Introduce Inserați foaie, situat în grup Celulele pe filă Acasă.În acest caz, o nouă foaie va fi adăugată înaintea foii de lucru curente. Pentru a adăuga o foaie, puteți utiliza și meniul contextual, care este apelat făcând clic dreapta pe numele foii și selectați comanda din lista care apare Introduce.

CREAREA UNUI TABEL

Introducere a datelor

Puteți introduce două tipuri de date în celule: formule și constante (numere, text sau valori de dată și oră). Înainte de a introduce date, ar trebui să luați în considerare cum să le plasați cel mai bine pe foaie. Este convenabil să creați mai întâi un antet de tabel și să completați numele rândurilor și coloanelor acestuia. Apoi, la introducerea datelor, va fi ușor de navigat în tabel. Dacă utilizatorul uită să includă o coloană sau un rând în tabel, acestea pot fi inserate ulterior fără a pierde informațiile deja introduse.

Numele celulei (adresa celulei) în Excel este format prin analogie cu denumirea celulelor de pe o tablă de șah: după numele coloanei și rândului pe care se află celula. Deci, celula C3 este situată la intersecția coloanei C și al 3-lea rând.

Notă . Legăturile pot avea așa-numitul stil R 1C1, unde R 1 este rândul 1, iar C1 este coloana 1. Comutarea între stiluri se face folosind opțiunea Stil link-uri R 1С1, situat pe pagină Formule fereastră Parametrii Excel1, care este apelat prin comanda cu același nume din meniul de butoane Birou .

A

b

V

Orez. 3. Introducere text:

a – vizualizarea conținutului celulei B2;

b – trecerea la celula goală C2; c – introduceți text în celula C2

Puteți face referire fie la celule individuale, fie la intervale de blocuri dreptunghiulare de celule. Când un interval include celule adiacente, de exemplu A1, A2 și AZ sau A1, B1 și C1, un astfel de interval este indicat în formulă prin referire la prima și ultima sa celule, între care este plasat două puncte „:” (A1: A3 Și A1: respectiv C1). Dacă celulele intervalului nu sunt adiacente, adică au fost selectate folosind tasta Ctrl, apoi referințele la greutatea celulei interval sunt enumerate în formulă separate prin punct și virgulă „;” (A1;A3;C1).

Într-o formulă, folosind link-uri, puteți utiliza date atât din foaia de lucru curentă, cât și din alte foi din registrul de lucru, precum și din alte registre de lucru.

Când deschideți un document nou, celula A1 este setată automat la activ și este înconjurată de un cadru negru. Și dacă începeți imediat să introduceți text, acesta va apărea în această celulă. Pentru a introduce text într-o altă celulă, de exemplu A2, trebuie să îl activați, de exemplu. Faceți clic pe această celulă cu mouse-ul sau plasați cursorul în ea făcând dublu clic (un link către celula activă este afișat în câmpul de nume). În continuare ar trebui să introduceți datele și completați introducerea apăsând tasta Tab, în urma căreia cursorul se deplasează în celula adiacentă din dreapta - B2.

Ar trebui să acordați atenție acestei situații. După ce textul este introdus într-o celulă, acesta poate fi afișat în afara acesteia (Fig. 3a). Dar nu trebuie să credeți că celulele vecine C2 și B2 sunt ocupate. Puteți verifica cu ușurință acest lucru activând una dintre aceste celule. Dacă celula activă conține informații, acestea apar în bara de formule. În fig. Figura 3b arată că bara de formule este goală, adică celula C2 nu conține nicio informație. După ce textul este introdus în el, informațiile care au trecut anterior dincolo de celula B2 vor fi ascunse (Fig. 3c).

Notă . Direcția în care se deplasează cursorul la introducerea datelor într-o celulă este specificată folosind opțiunea Treceți la o altă celulă după ce apăsați ENTERși lista derulantă Direcţie, situat pe pagină În plus fereastră Parametrii Exce1, care se apelează prin comanda cu același nume din meniul de butoane Birou .

Dacă informațiile au fost deja introduse într-o celulă și trebuie doar să adăugați sau să corectați datele introduse anterior (de exemplu, dacă nu trebuie să introduceți nimic în celula B2) Nume, A Numele produsului), trebuie să faceți următoarele:

Faceți dublu clic pe această celulă sau apăsați o tastă F 2 când celula dorită este evidențiată. Aceasta va comuta în modul de editare.

Plasați cursorul în locul în care doriți să adăugați text, de ex. în sfârşit cuvintele Nume, apasa un buton Spaţiuși introduceți cuvântul bunuri.

Pentru a confirma modificările efectuate, apăsați tasta introduce sau Tab sau un buton introduceîn bara de formule.

Dacă în timpul procesului de editare este necesară restaurarea datelor originale în celulă (ieșirea din modul de editare), ar trebui să apăsați tasta Esc sau faceți clic pe butonul Anulare, situat în bara de formule.

Pentru a modifica orice parametri ai tabelului (lățimea coloanei, fontul celulei etc.), mai întâi trebuie să învățați cum să selectați elementele necesare.

EVIDENȚIA ELEMENTELE DE TABEL

Principiul de funcționare în Excel este că înainte de a aplica orice acțiune oricărui element al tabelului (un fragment de text, un interval de celule, un rând sau o coloană), acesta trebuie mai întâi selectat.

De exemplu, pentru a selecta lățimea coloanelor din tabelul creat folosind Selectare automată - un instrument care vă permite să setați automat lățimea sau înălțimea unei celule (rând sau coloană), astfel încât toate informațiile să fie vizibile. Pentru a face acest lucru, trebuie mai întâi să selectați coloanele tabelului.

Selectarea rândurilor și coloanelor

Pentru a selecta o coloană, faceți clic pe antetul literei acesteia. Pentru a selecta mai multe coloane adiacente (una lângă alta) (de exemplu, A, B, C, D și E), trageți cursorul mouse-ului peste titlurile lor în timp ce țineți apăsat butonul din stânga. Selectarea rândurilor de tabel se face într-un mod similar.

Acum că sunt selectate coloanele dorite, puteți accesa fila Panglică Acasă iar în grup Celulele executa comanda Coloană Selectarea automată a lățimii coloanei. Ca rezultat, Excel va selecta automat lățimea necesară pentru fiecare coloană de tabel selectată.

Dacă trebuie să selectați toate celulele foii, faceți clic pe butonul de la intersecția numelor rândurilor și coloanelor. Selectarea coloanelor sau rândurilor neadiacente (localizate separat) se efectuează, ca și celulele neadiacente (vezi subsecțiunea „Selectarea celulelor neadiacente”), folosind tasta Ctrl .

Selectarea celulelor adiacente

A

b

Orez. 4. Selectarea celulelor adiacente:

a – în două coloane; b – într-o coloană

Când lucrați cu tabele, este foarte obișnuit să selectați blocuri de celule adiacente pentru a le aplica unele ajustări. De exemplu, pentru a alinia valorile coloanei din tabelul nostru Pret inclusȘi Prețul de vânzareîn centru, trebuie mai întâi să selectați un bloc cu numere, ale cărui celule sunt adiacente (Fig. 4 a).

Acest lucru se poate face în felul următor: mutați indicatorul în celula de colț a intervalului selectat, de exemplu D 3, apăsați butonul stâng al mouse-ului și, ținând-l apăsat, trageți indicatorul către celula diagonal opusă a blocului E7.

Acum că blocul de celule este selectat, pentru a centra valorile, urmați grupul de comenzi Aliniere pe filă Acasă apasa butonul In centru.

Selectarea celulelor neadiacente

Pentru a selecta celulele neadiacente, trebuie mai întâi să selectați primul interval (în acest caz A3:A7, aici două puncte sunt operatorul intervalului), apoi apăsați tasta Ctrlși, ținând-o apăsat, selectați celulele rămase (adică intervalul D 3:E7). Odată ce toate celulele necesare au fost selectate, le puteți aplica operația de aliniere.

COPIEREA SI MUTAREA CELULELOR

În editorul Excel, copierea și mutarea datelor se realizează într-un mod standard Windows, care constă din următorii pași:

Selectați una sau mai multe celule sau o parte din conținutul unei celule, de ex. informațiile care trebuie copiate.

Copiați (mutați) blocul selectat în clipboard (de exemplu, folosind butonul Copie(A tăia), Clipboard pe filă Acasă.

Plasați cursorul în locul din document în care vor fi inserate informațiile transferate.

Lipiți informațiile în buffer la locația cursorului apăsând butonul Introduce, situat în grupul de comandă Clipboard.

Pentru a copia date care nu fac parte dintr-o serie (vom vorbi mai târziu despre seria de date), puteți folosi și mânerul de umplere - pătratul negru din colțul din dreapta jos al celulei selectate. Pentru a face acest lucru, trebuie mai întâi să selectați o celulă sau un interval de celule (Fig. 5a), apoi mutați indicatorul mouse-ului la marcatorul de umplere (în acest moment aspectul său se va transforma dintr-o cruce albă într-una neagră) și trageți-l prin celulele de completat, în timp ce țineți apăsat butonul stâng al mouse-ului (Fig. 5 B). În acest caz, vorbim despre răspândirea datelor într-un interval.

Marker de umplere

A

b

Orez. 5. Copierea conținutului celulei:

a – evidențierea textului sursă; b – rezultatul copierii

În plus, copierea și mutarea se pot face prin tragerea cu mouse-ul. Pentru a face acest lucru, urmați acești pași:

Selectați o celulă sau un bloc de celule.

Mutați indicatorul mouse-ului în punctul de la marginea unei celule sau a unui bloc de celule unde indicatorul mouse-ului se schimbă de la o cruce albă la o săgeată albă.

Pentru a copia: apăsați tasta Ctrlși butonul stâng al mouse-ului și, ținându-le apăsat, mutați celulele în locația dorită din tabel. Pentru a muta cheia Ctrl nu este nevoie să apăsați.

Eliberați butonul mouse-ului și apoi tasta Ctrl .

În acest caz, toate datele existente în zona de inserare vor fi înlocuite cu altele noi.

Folosind o inserție specială

Uneori este destul de convenabil să aplicați adunări, înmulțiri etc. datelor copiate în clipboard. În aceste cazuri se folosește comanda insert special, apelat din meniul contextual.

În figura prezentată. Tabelul 6 trebuie să adauge valorile coloanei Brigada a 3-a la valorile coloanei Recoltarea merelor etc. Pentru a face acest lucru, trebuie să faceți următoarele:

Copiați valorile intervalului D 2:D 5 în clipboard.

Plasați cursorul în celula B2 - prima celulă a zonei de inserare și, făcând clic dreapta pe această celulă, apelați meniul contextual în care să selectați comanda Inserție specială.

În caseta de dialog cu același nume care se deschide (Fig. 7), în câmp Operațiune selectați elementul pliază

apasa butonul BINE.

Orez. 6 Adăugarea datelor

Ca urmare a acţiunilor efectuate în coloană Achiziții Apple, or vor fi afișate valorile totale (Fig. 8).

După cum se poate observa în Fig. 7, fereastra Inserție specială vă permite să copiați diverse elemente complexe:

grup Introduce definește obiectul de copiere;

grup Operațiune atribuie opțional o operație matematică care poate fi aplicată datelor copiate;

opțiune sări peste celulele goale vă permite să nu înlocuiți valori în zona de lipire dacă zona copiată conține celule goale;

opțiune transpune servește pentru a afișa rândul specificat în zona de copiere într-o coloană și, în consecință, coloana zonei copiate într-un rând (zona de inserare nu trebuie să se suprapună cu zona de copiere).

De exemplu, pentru a transpune un rând care listează lunile perioadei de raportare (interval B1:E1) într-o coloană (interval A2:A5), procedați în felul următor:

Orez. 7. Fereastra de dialog Inserție specială

Orez. 8 Rezultat adaos

Copiați valorile intervalului original B1:E1 în clipboard.

Plasați cursorul în celula din stânga sus a zonei de inserare A2 și activați comanda din meniul contextual Inserție specială.

În caseta de dialog cu același nume care se deschide, activați opțiunea transpuneși apăsați BINE.

Ca rezultat, rândul copiat va apărea în coloană așa cum se arată în Fig. 9

Orez. 9. Transpune rezultatul

Introducerea datelor în mai multe celule simultan

Puteți introduce aceleași date în mai multe celule în același timp fără a utiliza procedura de copiere. În acest caz, celulele nu trebuie să fie adiacente. Pentru a face acest lucru, trebuie să urmați pașii de mai jos:

Selectați acele celule în care doriți să plasați aceleași date.

Introduceți informațiile necesare (Fig. 10a).

Apăsați comanda rapidă de la tastatură Ctrl + introduce .

Ca rezultat, toate celulele selectate vor conține aceeași valoare (Fig. 10b).

Orez. 10 Introducerea datelor în mai multe celule simultan:

a – introduceți informațiile necesare;

b – copiere simultană în toate celulele selectate

Umplerea celulelor prin copiere

Dacă trebuie să completați celulele cu aceleași date, puteți utiliza cheia Ctrl.Pentru a face acest lucru, trebuie să implementați următoarele acțiuni:

Introduceți o valoare într-o celulă.

Apăsați și mențineți apăsată tasta Ctrl .

Prindeți marcatorul de umplere cu butonul stâng al mouse-ului și trageți-l peste celulele de umplut.

Ca urmare, celulele vor fi umplute cu o copie a valorilor care au fost introduse în celula originală.

Adăugarea de rânduri și coloane

La introducerea datelor, utilizatorul se va confrunta probabil cu nevoia de a introduce un rând sau o coloană goală într-un tabel. În Excel, pentru a rezolva această problemă, ar trebui să faceți următoarele:

1. Selectați rândul (coloana) înaintea căruia doriți să o introduceți făcând clic pe antetul acestuia.

Apelați meniul contextual făcând clic pe butonul din dreapta al mouse-ului.

În lista de comenzi care apare, selectați comanda Introduce, după care va apărea un nou rând (coloană).

Notă. Dacă trebuie să inserați mai multe rânduri (coloane) deodată, ar trebui să selectați acele rânduri (coloane) înainte de care doriți să inserați altele noi. În acest caz, numărul de rânduri (coloane) introduse corespunde numărului de rânduri selectate.

Celula este introdusă în tabel în același mod, dar după executarea comenzii Introduce de asemenea, trebuie să specificați metoda de inserare în caseta de dialog Adăugarea de celule.

Puteți folosi o altă metodă pentru a insera o celulă, un rând sau o coloană - în filă Acasă in grup Celulă articol deschis Introduceși selectați una dintre comenzi: Inserați celule, inserați rânduri în foaie sau Introduceți coloane într-o foaie respectiv.

LUCRU CU FORMULELE ȘI FUNCȚIILE

Principalul avantaj al editorului de foi de calcul Excel este prezența unui aparat puternic de formule și funcții cu ajutorul căruia puteți efectua operații matematice financiare și statistice, procesați date text și date de dată/ora, lucrați cu elemente logice, link-uri și tablouri. În plus față de operațiunile de calcul cu numere individuale, este posibil să se proceseze rânduri sau coloane individuale ale unui tabel, precum și blocuri întregi de celule. În special, puteți găsi media aritmetică, valorile maxime și minime, puteți efectua operații pe text și puteți stabili condiții pentru calcularea datelor.

O formulă în Excel este o secvență de caractere care începe cu un semn egal (=) și conține elemente calculate (operanzi) și operatori.

Operanzii pot fi:

valori constante;

nume;

funcții.

Există patru tipuri de operatori:

aritmetic;

operatori de comparare;

operatorul text „&”, care este folosit pentru a desemna operația de combinare a mai multor secvențe de caractere într-una singură;

operatori de adrese.

Operatorii tuturor soiurilor enumerate sunt prezentați mai jos (Tabelul 1-3).

Tabelul 1 Operatori aritmetici

Operatori aritmetici

Operatori de comparare

Operator

Sens

Operator

Sens

Plus

Egal

Scădere

Mai mult

Multiplicare

Mai puțin

Divizia

Mai mult sau egal

La sută

Mai puțin sau egal

Exponentiație

Nu este egal

Tabelul 2 Operatori de comparare

Operator

Sens

Operator de interval, care se referă la toate celulele dintre limitele intervalului inclus

Operatorul de unire, care se referă la uniuni ale celulelor intervalului

(spaţiu)

Operator de intersecție care se referă la celulele comune ale intervalului

In Exce l formula este evaluată de la stânga la dreapta după o anumită ordine a operatorilor din formulă, cu alte cuvinte, există o prioritate a operatorilor. Astfel, dacă sunt folosiți mai mulți operatori într-o formulă, atunci Excel efectuează calcule în ordinea priorității operatorului prezentată în Tabelul 3.

Tabelul 3. Precedența operatorului

Operator

Descriere

Operator

Descriere

Obținerea unei game de celule

Exponentiație

(spaţiu)

Intersecția razei

* Și /

Înmulțirea și împărțirea

Combinarea gamelor

ȘI -

Adunare si scadere

Schimbarea semnului unei expresii

Concatenarea șirurilor de text

Calcul procentual

= < > <= <= <>

Compararea datelor

Orez. unsprezece Elemente ale formulei

Pentru a schimba ordinea operațiilor, includeți partea din formula care ar trebui evaluată mai întâi în paranteze.

De exemplu, pentru a scădea numărul 3 din numărul din celula A2 și a înmulți această diferență cu suma valorilor celulelor B3, B4 și B5, ar trebui să efectuați următoarele acțiuni:

Plasați cursorul în celula în care doriți să afișați rezultatul calculului.

Introduceți semnul egal (=) și adresele celulelor cu operatori aritmetici (Fig. 11).

apasa tasta introduce .

UTILIZAREA LINKURILOR

Legăturăîn editorul Excel identifică în mod unic o celulă de tabel sau un grup de celule din foaia de lucru. Referințele indică ce celule conțin valorile pe care doriți să le aplicați ca operanzi formulei. Într-o formulă, puteți utiliza link-uri pentru a utiliza date situate în locuri diferite pe foaia de lucru. De asemenea, puteți utiliza aceeași valoare a celulei în mai multe formule.

De asemenea, puteți utiliza link-uri pentru a face referire la celulele care se află pe alte foi dintr-un registru de lucru sau într-un alt registru de lucru sau chiar la date dintr-o altă aplicație.

Odată ce o formulă este introdusă într-o celulă, formula poate fi mutată, copiată sau extinsă la un bloc de celule. Copierea și mutarea celulelor de formulă este la fel ca și copierea și mutarea celulelor de date.

Când mutați formule dintr-o celulă în alta, referințele nu se modifică, în timp ce atunci când le copiați, se schimbă automat.

De exemplu, dacă formula =A1*A2 a fost scrisă în celula A3, atunci când copiați conținutul lui A3 în celulele B3 și C3, noile formule cu legături actualizate vor lua următoarea formă: = B1*B2, =C1*C2 (Fig. 12a).

Pe lângă linkurile relative, Excel folosește adesea legături absolute, unde pe lângă numele coloanei și numărul rândului, este folosit caracterul special „$”, care fixează o parte din link (coloană, rând) și o lasă neschimbată atunci când copiați formula cu o astfel de legătură într-o altă celulă. De obicei, referințele absolute indică celulele care conțin constante utilizate în calcule.

Orez. 12. Folosind link-uri

A – relativ; b – absolută

De exemplu, dacă este necesar să se fixeze în formula =A1*B1 valoarea celulei A1 (Fig. 12 b), care nu ar trebui să se schimbe dacă această formulă este copiată, atunci referința absolută la această celulă va avea următoarea formă : $A$1. Astfel, la copierea formulei din celula B2 în celula C2, formula va lua forma =$A$1*C1.

Selectați celula cu formula.

Prin apăsarea unei taste F 4 selectați tipul de legătură dorit.

Secvența de schimbare a tipurilor de legături pentru celula A1 când se utilizează cheia F 4 ca aceasta:

A$ 1 - coloană mutabilă și rând imuabil;

$A1 - coloană imuabilă și rând mutabil;

prin introducerea directă a legăturilor de la tastatură (introduse cu litere latine), care este adesea folosită la editarea formulelor;

făcând clic pe celulele ale căror valori iau parte la calcule.

A doua metodă include următorii pași:

În modul de editare, plasați cursorul în partea formulei în care doriți să inserați legătura, dar întotdeauna după operatorul matematic sau paranteza.

Pentru a crea un link către datele foii curente atunci când construiți o formulă, trebuie să faceți clic pe celula cu aceasta sau să selectați intervalul necesar de celule. Dacă datele se află pe o altă foaie de lucru, mergeți mai întâi la foaia dorită făcând clic pe eticheta acesteia din partea de jos a ecranului, apoi selectați celulele cu datele. În mod similar, vă puteți referi la datele conținute într-o altă carte.

apasa tasta introduce .

CONCEPTUL DE FUNCȚIE ÎN EXCEL

Funcțiiîn Excel sunt formule standard gata făcute și sunt folosite pentru a efectua anumite calcule în registrele de lucru. Sunt numite valorile care sunt utilizate pentru evaluarea funcțiilor argumente. Sunt denumite valorile returnate de funcții ca răspuns rezultate.

Pentru a utiliza o funcție în calcule, trebuie să o introduceți ca parte a unei formule într-o celulă a foii de lucru. Se numește secvența în care trebuie să fie localizate simbolurile utilizate în formulă sintaxa funcției.

Toate funcțiile folosesc aceleași reguli de sintaxă de bază. Dacă aceste reguli sunt încălcate, Excel va afișa un mesaj care afirmă că există o eroare în formulă.

Funcții matematice, financiare și alte funcții

Pentru confortul utilizatorului la construirea formulelor, funcțiile din Excel sunt împărțite în categorii: funcții de gestionare a bazelor de date și a listelor, funcții de dată și oră, financiare, statistice, text, matematice, logice (Fig. 13).

Orez. 13. Panou Biblioteca de funcții pe filă Formule

Funcții text folosit pentru prelucrarea textului și anume: căutarea caracterelor necesare, scrierea caracterelor într-un loc strict definit în text etc.

Prin utilizarea funcții de dată și oră puteți rezolva aproape orice problemă legată de luarea în considerare a datelor sau orelor calendaristice (de exemplu, calcularea numărului de zile lucrătoare pentru orice perioadă de timp).

Funcții logice folosit pentru a crea formule complexe care, in functie de indeplinirea anumitor conditii, vor implementa diverse tipuri de prelucrare a datelor.

In Exce Sunt larg reprezentat functii matematice,în special, pe lângă operațiile cu numere, puteți efectua și operațiuni de rotunjire.

Utilizatorul are la dispoziție și o bibliotecă functii statistice, cu care poți căuta valoarea medie, elementele maxime și minime etc.

Reguli de sintaxă pentru scrierea funcțiilor

Dacă o funcție apare chiar la începutul unei formule, ea trebuie să fie precedată de un semn egal, așa cum este cazul la începutul oricărei formule.

După aceasta, se introduce numele funcției, urmat imediat de o listă de argumente între paranteze. Argumentele sunt separate unul de celălalt prin punct și virgulă „;”. Parantezele permit Excel să determine unde începe și unde se termină lista de argumente (Figura 14).

Notă. Intrarea funcției trebuie să conțină paranteze de deschidere și de închidere, iar spații nu pot fi introduse între numele funcției și paranteze. În caz contrar, Excel va afișa un mesaj de eroare.

Orez. 14. Funcții de înregistrare

Argumentele pot fi numere, text, boolean, matrice, valori de eroare sau referințe. Parametrii inițiali specificați de utilizator trebuie să aibă valori valabile pentru argumentul dat.

De exemplu, în formula prezentată în fig. 15, celulele B2, V3, B4, B5 și E6 sunt însumate.

Fig. 15.Însumarea celulelor

Să luăm în considerare funcționarea funcției ROUND(arg1;arg2), care rotunjește un număr la un anumit număr de zecimale și are două argumente:

arg1 - adresa celulei cu numărul (sau numărul în sine) care trebuie rotunjit;

arg2 - numărul de cifre după virgula zecimală a numărului după rotunjire.

Pentru a rotunji numărul 2,71828 situat în celula A1 la una, două sau trei zecimale și pentru a scrie rezultatele calculului în celulele B1, C1 și, respectiv, D 1, trebuie să procedați după cum urmează:

Introduceți numărul 2.71828 în celula A1.

Introduceți următoarele formule în celulele B1, C1 și D 1 (Fig. 16): =ROUND(A1,1)

ROUND (A1,2)

ROUND (A1;3)

Orez. 16. Rezultatul utilizării funcției de rotunjire

Argumentele pot fi fie constante, fie funcții. Sunt apelate funcțiile care sunt argumente pentru o altă funcție cuibărit. De exemplu, să însumăm valorile celulelor A1 și A2, după ce au rotunjit mai întâi aceste valori la două zecimale:

SUM(ROUND(A1,2),ROUND(A2,2))

Aici funcția ROUND este imbricată. Excel vă permite să utilizați nu mai mult de șapte niveluri de imbricare a funcțiilor în formule.

In Exce l Există funcții care nu au argumente. Exemple de astfel de funcții sunt PI (returnează valoarea numărului , rotunjită la 15 cifre) sau TODAY (returnează data curentă). Când utilizați astfel de funcții, ar trebui să puneți paranteze în bara de formule imediat după numele funcției. Cu alte cuvinte, pentru a obține valoarea numărului  sau data curentă în celule, trebuie să introduceți formule ca aceasta:

PI()

ASTĂZI()

SARCINI PENTRU LUCRĂRI DE LABORATOR

Opțiunea 1

1. Pe prima foaie a unei cărți deschise, tastați următorul tabel

Numele I.

Algebră

Geometrie

General pok-l

Numărul de evaluări

Puncte

Scor mediu

Numărul de evaluări

Puncte

Scor mediu

Ivanov M.

Petrov D.

Sidorov V.

3,571428571

2. Introduceți titlul tabelului Evaluarea expresă a elevilor la disciplinele exacte.

3. Rezultatul coloanelor Scor mediu obtinut cu ajutorul formulei.

4. Într-o coloană Scor mediu rotunjiți numărul la două zecimale.

6. Redenumiți foaia 1în cearşaf Matematică.

7. Pe a doua foaie, creați un tabel similar cu un bloc de discipline umanitare (de exemplu, Literatură, istorie).

8. Desenați o legătură între tabele prin numele elevilor folosind o formulă introdusă într-o celulă.

Opțiunea 2

1. Pe prima foaie a unei cărți deschise, introduceți un tabel care conține date despre accidentele rutiere în care sunt implicați copii pentru luna ianuarie 2008/2009

Denumirea departamentului de afaceri interne

Total

Decedat

Rănită

Accident rutier

2008

2009

2008

2009

2008

2009

Direcția Municipală de Interne pentru Tambov

3

3

Departamentul raional al afacerilor interne Zherdevsky

Kirsanovsky GROVD

1

2

Kotovsky GOVD

Michurinskoe GUVD

Departamentul de Afaceri Interne al districtului Michurinsky

1

2

Departamentul de Afaceri Interne al orașului Morshansky

1

1

Departamentul de Afaceri Interne din districtul Morshansky

Rasskazovsky GROVD

1

1

Departamentul Afacerilor Interne din districtul Bondarsky

2. Pe a doua foaie, creați un tabel similar pentru luna februarie 2008/2009.

Denumirea departamentului de afaceri interne

Total

Decedat

Rănită

Accident rutier

2008

2009

2008

2009

2008

2009

Direcția Municipală de Interne pentru Tambov

4

4

Departamentul raional al afacerilor interne Zherdevsky

Kirsanovsky GROVD

Kotovsky GOVD

Michurinskoe GUVD

2

2

Departamentul de Afaceri Interne al districtului Michurinsky

1

1

Departamentul de Afaceri Interne al orașului Morshansky

1

1

Departamentul de Afaceri Interne din districtul Morshansky

Rasskazovsky GROVD

Departamentul Afacerilor Interne din districtul Bondarsky

Redenumiți prima foaie de registru de lucru Excel foaia 1 V 01 , A foaie 2-V 02 .

Pe a treia foaie, creați un tabel cu aceiași parametri, dar obțineți datele prin însumarea rezultatelor pentru două luni de un an.

Desemna foaia3 ca o foaie cu titlu +2 .

Opțiunea 3

1. Pe prima foaie a unei cărți deschise, tastați un tabel cu titlul Aviz de plată

Adresa: str. Proletarskaya, 11, ap. 067

Cont personal

2234567654

Perioadă

ian.08

Total de plătit

Bun frica de locuire:

23,35

Total cu asigurare:

Tip de plată (unitate)

Rată

Volum

Taxat conform tarifului

Gazon. si rem. Trăit (m2)

4,33

46,7

Incalzire (m2)

23,68

46,7

Gaz (sobe) (persoane)

Alimentare cu apă (persoane)

84,27

Evacuarea apei (persoane)

58,16

Gor. Rezerva de apa (persoane)

150,73

Îndepărtarea deșeurilor solide (persoane)

20,13

Interfon

Total plătit:

Dispozitive

Pentru o zi

Anterior

Cantitate (kW)

contabilitate

extracte

Lumină/1/

3200

3050

Spre tabelul principal într-o coloană Tipul de plată adăuga linie Electricitate (kW), cu o valoare tarifară de 2,05 ruble.

Scrieți numărul calculat de kilowați într-o linie Electricitate (kW), făcând conexiuni între aceste celule;

Valorile coloanelor Taxat conform tarifului obținut folosind o formulă (trebuie să înmulțiți valorile din coloane RatăȘi Volum).

Creați rânduri rezumative deasupra tabelelor Total de plătitȘi Total cu asigurare prin conectarea la tabelul principal printr-o formulă.

Opțiunea 4

1. Pe prima foaie a unui registru de lucru Excel deschis, tastați un tabel cu vânzările TV

Model

Preț

Vândut, buc.

Venituri, freacă.

Panasonic TX-R32LM70

25.848,00 RUB

2

51.696,00 RUB

Panasonic TX-R32LX70

33.084,00 RUB

3

99.252,00 RUB

Panasonic TX-R32LX700

44.604,00 RUB

1

44.604,00 RUB

Total:

195.552,00 RUB

3. Redenumiți foaia 1 V Panasonic .

Într-o celulă separată de tabel, introduceți valoarea cursului de schimb euro:

cursul de schimb euro

36,20 rub.

Adăugați o coloană Venituri, euro, adică calculați costul televizoarelor în euro raportat la cursul de schimb actual rublă/euro, folosind o formulă cu referință absolută a celulei.

Pe a doua foaie a registrului de lucru Excel, obțineți în mod similar următorul tabel de vânzări TV:

Model

Preț

Vândut, buc.

Sursa de venit

Philips 20PF4121

10.980,00 RUB

4

43.920,00 RUB

Philips 20PF5120

16.812,00 RUB

5

84.060,00 RUB

Philips 20PF5123

11.376,00 RUB

1

11.376,00 RUB

Total:

139.356,00 RUB

Opțiunea 5

Introduceți titlul tabelului Salarizare .

Completați următorul tabel introducând numele de familie și sumele salariale în ruble .

Introduceți numărul de copii în coloană Numărul de copii.

Nume de familie

Sumă de

număr

Suma pentru anul

Salariu

Impozit

extrădare

plăți

1

Moryzhenkov

15000

2

Vecini

14900

3

Semionov

13780

4

Korolenko

16200

5

Stenbock

17560

6

Muscatine

12870

7

Geraskin

18430

8

Koceev

15555

Introduceți valoarea într-o celulă separată de tabel Taxe pentru copii.

Acumulare pentru copii

153r

6. Pornit foaie 2 creați următorul tabel conectându-vă la tabelul situat pe prima foaie printr-o coloană Nume de familie.

Nume de familie,

Nume Nume patronimic

Sumă

7. Valorile coloanei Sumă obţinută folosind formula (înmulţirea numărului de copii cu valoarea Acumulare pentru copii), folosind o referință absolută la celula în care se află valoarea Acumulare pentru copii.

Întrebări de control

Care este numele documentului în Excel și în ce constă?

Cum să adăugați o foaie nouă la un registru de lucru? Cum se redenumește o foaie?

Ce este o celulă?

În ce constă o adresă de celulă?

Ce este o celulă activă?

Ce este adresarea absolută și relativă?

Cu ce ​​caracter începeți când introduceți o formulă într-o celulă?

Ce este Function Wizard și cum funcționează?

Cum să copiați sau să mutați o celulă?

Cum se editează conținutul unei celule?

Ce este completarea automată și cum se face?

Cum să ștergeți (inserați) un rând (coloană)?

Ce este asumarea automată?

Bibliografie

Gluşakov , S.V. Microsoft Office 2007. Cel mai bun tutorial / S.V. Glushakov, A.S. Serios. – ed. a 3-a adăugare. si prelucrare – M.: AST: AST MOSCOVA: Vladimir: VKT, 2008. -446. c. (Curs de pregatire).

Gluşakov , S.V. Microsoft Excel 2007. Cel mai bun tutorial / S.V. Glushakov, A.S. Serios. – ed. a 2-a adăugare. si prelucrare – M.: AST: AST Moscova:, 2008. -416 p. - (Curs de pregatire).

S. Simonovici, V. Murakhovsky. Manual de autoinstruire popular pentru lucrul la calculator - M.: „Techbook”, 2006. – 576 p.

Excel Labs

Lucrare de laborator nr 1

Crearea unei liste de clienți

Formatare Mese. Pentru celule I2-I14 setați stilul procentual (pentru a face acest lucru, selectați acest interval și faceți clic pe butonul Format procentual pe bara de instrumente Formatare).

Sortarea datelor. Trebuie selectat din meniu Date  Triere.În caseta de dialog, selectați primul criteriu de sortare Cod iar al doilea criteriu OrașȘi BINE. Filtrarea datelor. Selectați din meniu Date  Filtru/Atofiltru. După ce faceți clic pe numele acestei comenzi, va apărea un buton săgeată în primul rând lângă titlul fiecărei coloane. Poate fi folosit pentru a deschide o listă care conține toate valorile câmpului dintr-o coloană. Selectați numele unuia dintre orașele din Oraș. Pe lângă valorile câmpurilor, fiecare listă conține încă trei elemente: (Toate), (Primele 10...) și (Condiție...). Element (Toate) este conceput pentru a restabili afișarea tuturor intrărilor de pe ecran după aplicarea unui filtru. Element (Primele 10...) oferă afișarea automată a primelor zece intrări din listă. Dacă compilați tot felul de evaluări, a căror sarcină principală este de a determina primele zece, utilizați această funcție. Ultimul element este folosit pentru a forma un criteriu de selecție mai complex în care pot fi aplicați operatori condiționali ȘIȘi SAU. Plasați cursorul în orice celulă umplută și faceți următoarele: în meniu Format  Formatare automată  Lista 2 .

Crearea unei liste de produse

Lucrare de laborator nr 2

Comenzi de foi

Redenumiți foaia de lucru SheetZ adresat Comenzi.

Introduceți următoarele date în primul rând, care vor fi folosite ca nume de câmp în viitor:
A1  Luna comenzii , ÎN 1  Data comandă , CU 1  Număr de ordine , D1  Numărul de articol , E1  Numele produsului , F1  Cantitate , G1  pret pe unul ., H1  Codul companiei client ., eu1  Numele companiei clientului , J1  Pretul comenzii , K1  Reducere(%) , L1  Plata totala .

Pentru prima linie faceți alinierea datelor in centru Format Celulele Aliniere traduce după cuvinte .

Selectați coloanele una câte una B, C, D, E, F, G, H, I, J, K, L și introduceți camp Nume nume Data, Comanda, Numărul2, Produs2, Cantitate, Preț2, Cod2, Companie2, Sumă, Reducere2 Și Plată .

Selectați o coloană ÎNși executați comanda de meniu Format Celulele. În fila Număr Selectați
Format numeric Data, și în câmp Tip selectați formatul ca HH.MM.YY. La finalul dialogului
faceți clic pe butonul BINE.

Selectați coloaneleG, J, Lși executați comanda de meniu Format Celulele. În fila Număr
Selectați Format numeric Monetar , te rog sa indici Numărul de zecimale egal cu 0, iar în câmp
Selectați denumirea $ engleză (SUA). La sfârșitul dialogului, faceți clic pe butonul Bine.

Selectați coloana K și executați comanda de meniu Format Celulele. În fila Număr Selectați
Format numericProcent , te rog sa indici Numărul de zecimale egal cu 0. În cele din urmă
dialog faceți clic pe butonul Bine.

Într-o celulă A2 trebuie să introduceți următoarea formulă:

=IF(BLANC($B2),“ ”,SELECT(LUNA($B2), „Ianuarie”, „Februarie”, „Martie”, „Aprilie”,„mai”; „iunie”; „iulie”; „august”; „septembrie”; „octombrie”; „noiembrie”; „decembrie”)) (3.1)

Și umpleți celula galbenă.

Formula (3.1) funcționează după cum urmează: în primul rând, se verifică condiția pentru golirea celulei A2. Dacă celula este goală, atunci puneți un spațiu; în caz contrar, folosind funcția SELECT, selectați luna dorită din listă, al cărei număr este determinat de funcția LUNA.

Pentru a tasta formula (3.1) urmați acești pași:

face celula activă A2și apelați funcția DACĂ;

în fereastra funcției IF din câmp expresie_booleană tastați $ manual B2= "", V

camp valoare_dacă_adevărată formați " " , în câmp valoare_dacă_falsă apelați funcția SELECT;

în fereastra de funcții ALEGEREîn câmp valoare1 tip " Ianuarie",în câmp valoarea2 imprimare

în câmp Numarul indexuluiși apelați funcția LUNĂ;

în fereastra funcției LUNA din câmp Data_ca_număr formați adresa $ B2 ;

Faceți clic pe butonul Bine.

La celulă E2 introduceți următoarea formulă:

Regula de tastare a formulei:
Faceți clic pe celula E2. Plasați cursorul pe pictograma Standard Panel. Se va deschide o fereastră Funcție Master..., selectați funcția IF. Urmați pașii pe care îi vedeți în imagine
Acestea. pe poziție Expresie logică faceți clic pe o celulă D2 și apăsați tasta F4 de trei ori - obțineți $D2, tastați = " ", utilizați tasta Tab sau mouse-ul pentru a vă deplasa la poziția Valoare_dacă_adevăratși formați. " ", mergeți la poziție Valoare_dacă_fals– faceți clic pe butonul de lângă numele funcției și selectați comanda Alte funcții.. → Categorii → Legături și matrice, în fereastra Funcții → VEDERE→ OK → OK.

Se va deschide fereastra cu funcții VEDERE. Pe poziție Search_value faceți clic pe o celulă D2 și apăsați tasta F4 de trei ori - obțineți $D2, utilizați tasta Tab sau mouse-ul pentru a vă deplasa la poziție Vector_vizualși faceți clic pe scurtătura pentru foaie " Bunuri", selectați o serie de celule A2:A12, apăsați tasta F4, mergeți la poziție Vector_results– faceți din nou clic pe eticheta foii „ Bunuri", selectați o serie de celule B2:B12, apăsați tasta F4 și OK. Dacă ați făcut totul corect, va apărea în celulă # HD.

CU

10. La celulă G2 introduceți următoarea formulă:

=DACA($D2=“ ”;“ ”;VIZUALIZARE($D2;Numărul articolului; Preț)) (3,3)

Umpleți celula galben culoare.

11. La celulă eu2 introduceți următoarea formulă:
=IF($H2=“ ”;“ ”; VEZI($H2;Cod; Firmă)) (3,4)
Umpleți celula galben culoare.

12. La celulă J2 introduceți următoarea formulă:
=DACA(F2=“ ”;“ ”;F2* G2) (3.5)
Umpleți celula galben culoare..

13. La celulă K2 introduceți următoarea formulă:
=IF($H2=“ ”;“ ”; VEZI($H2;Cod; Reducere)) (3,6)
Umpleți celula galben culoare.

14. La celulă L2 introduceți următoarea formulă:
=DACA(J2=“ ”;“ ”;J2- J2* K2) (3.7)
Umpleți celula galben culoare.

15. Celulele B2, D2 și H2 – în care nu există formule, completați albastru culoare. Selectați un interval A2 – L 2 și un marker de umplere ( cruce neagră în colțul din dreapta jos al blocului ) umplere stretch și formule până la 31 linii inclusiv..

16. Faceți o celulă activă LA 2și trageți marcatorul de umplere în jos până la celulă VZ1 inclusiv.

17. La celulă C2 introduceți numărul 2008-01, care va fi numărul comenzii de pornire, și trageți marcatorul de umplere în jos până la celulăCZ1 inclusiv.

18. Acum trebuie să completați coloanele folosind tastatura B2:B31 , D2: D31 Și H2:H31. CU LA 2 De LA 11 Introducem datele din ianuarie (de exemplu, 01/2/08, 01/12/08). CU LA 12 De LA 21 introducem datele din februarie (de exemplu, 02/12/08, 21/02/08) și din B22 De B31 Introducem datele din martie (de exemplu, 03/5/08, 03/6/08). ÎN D2: D31 formați numerele de produs, de ex. 101, 102, 103, 104, 201, 202, 203, 204, 301, 302 și 303. Numerele pot fi repetate și în orice ordine, în mod similar în H2:H31 introduce Codurile companiile dvs. pe care le-ați tastat pe foaie Clienții. La coloană F introduce cifre duble numere.

19.

(SRSP) Lucrare de laborator nr 3

Formular de comandă

În celula H5, introduceți intrarea Cod, și în celulăeu5 pune formula
=DACA($E$3=“ ”; „ ”;VIZUALIZARE($E$3;Comandă; Cod 2)) La celulă C7 introduceți intrarea Numele produsului. Celulă E7 trebuie să conțină formula
=DACA($ E$3=“ ”; „ ”;VIZUALIZARE($ E$3;Comandă; Produs2)),
si celulele E7, F7, G7 atribuie sublinierea și centrarea. La celulă H7 introduceți caracterul № , și în celulăeu7 - formulă:
=DACA($ E$3=“ ”; „ ”;VIZUALIZARE($ E$3;Comandă; Numarul 2)) La celulă C9 introduceți intrarea Cantitate comandata. La celulă E9-formulă
=DACA($ E $3=“ ”; „ ”;VIZUALIZARE($ E$3;Comandă; Cantitate)) La celulă F9 -record unitati dupa pretși aliniați-l în raport cu centrul coloanelor FȘi G. Celulă H9 trebuie să conțină formula
=DACA($ E $3=“ ”; „ ”;VIZUALIZARE($ E$3;Comandă; Preț2)),
Această celulă ar trebui să aibă o subliniere și un stil de monedă. La celulă eu9 -record pe unitate Intră C11 text Costul total al comenzii, si in E11 pune formula
=DACA($ E $3=“ ”; „ ”;VIZUALIZARE($ E$3;Comandă; Sumă)),
La celulă F11 -record Reducere(%). A evidentia F11, G11, H11și faceți clic pe butonul Combinați și puneți în centru . La celulă eu11 pune formula
=DACA($ E$3=“ ”; „ ”;VIZUALIZARE($ E$3;Comandă; Reducere2)),
și setați opțiunile de formatare: stil subliniat și procent. La celulă C13-text A plăti.Și în celulăD13 plasează următoarea formulă
=DACA($ E$3=“ ”; „ ”;VIZUALIZARE($ E$3;Comandă; Plată)),
și setați opțiunile de formatare: sublinierea și stilul monedei. La celulă E13 introduceți intrarea Proiectat de:, a evidentia E13, F13 și setați centrarea textului. Apoi evidențiați G13, H13,eu13 și setați-le la centru și subliniere. În cele din urmă, setați lățimea coloanelorBȘi J egal cu 1,57, selectați B2- J14 și setați cadrul pentru întreaga gamă. Acum in E3 te rog sa indici Număr de ordineși înainte de a vă imprima formularul nume.

Ați finalizat cu succes lucrarea, predați-o profesorului!

Masă rotativă

A fost creată o listă de comenzi pentru utilizare practică, iar datele acesteia sunt supuse analizei. Expertul Pivot Table ne va ajuta să efectuăm analiza.

Tabelele pivot sunt create dintr-o listă sau bază de date.

8. Ați finalizat cu succes lucrarea, predați-o profesorului!

(SRSP) Laborator. Nr 4. Ramuri

Creați un registru de lucru și salvați-l în folderul dvs. sub numele Ramuri (numele tău de familie). Să începem exemplul prin crearea unui tabel și introducerea datelor despre fiecare ramură.

Etapa pregătitoare. Copiați în clipboard din foaie Bunuri cărți Comenzi date despre mărfuri, numărul și prețurile acestora, de ex. copiați o serie de celule A1-C12 foaie Bunuri.

Accesați prima pagină a cărții Ramuriși în celulă A3 lipiți fragmentul de tabel copiat. În formarea a 3-a în celuleD3, E3, F3 introduceți înregistrările în consecință Număr de comenzi, cantitate vândutăȘi Volumul vânzărilor. Setați centrarea textului în celule și permiteți textului să se încadreze în jurul cuvintelor.

La celulă F4 pune formula: =C4*E4și copiați-l în celule F5- F14 .

Tastați în celulă B15 cuvânt Total:, și în celulăF15 introduceți formula sumei sau faceți clic pe butonul din bara de instrumente Standard. excela va determina automat intervalul de celule al căror conținut trebuie însumat.

Ar trebui să existe atâtea astfel de foi câte orașe ați avut în foaie Clienții. Trebuie să copiem această foaie de 4 ori.

Pentru a face acest lucru, plasați cursorul mouse-ului pe comanda sa rapidă și apăsați butonul din dreapta al manipulatorului. În meniul contextual, selectați comanda Mutare/Copiere, în caseta de dialog care apare, specificați foaia în fața căreia trebuie inserată copia, activați opțiunea Creați o copieși apăsați Bine. Este mult mai ușor să copiați cu mouse-ul: poziționați indicatorul mouse-ului pe comanda rapidă a foii și mutați-l în poziția de inserare a copiei în timp ce țineți apăsată tasta [ Ctrl] .

Numele foilor de lucru se potrivesc cu titlurile orase din vedere Clienții, De exemplu, Almaty, Astana, Shymkent, Aktau, Karaganda sau alte nume. Introduceți numele ramurii corespunzător numelui foii și în celulă A1 a acestei foi.

Completează foaia Comenziîncă o coloană. La celulă M1 introduceți un cuvânt Oraș. La celulă M2 introduceți formula =DACA(EMPLANTARE($ H 2);“ ”;VIZUALIZARE($ H2;Cod; Oraș)) , extindeți această formulă la rândul 31 al acestei coloane.

Selectați din meniu Date  Filtru/Atofiltru. Selectați în coloană Oraș prima ramură. Date pe coloanăCantitate foaie Comenzi va fi introdus de dvs. în coloanăCantitate vândută foaie de carte Ramuri, în rânduri corespunzătoare numerelor de produs. Dacă mărfurile cu același număr sunt vândute în luni diferite, atunci cantitatea lor totală este luată. Și astfel se completează foile tuturor orașelor.

Consolidarea datelor. Copie de pe prima pagină a cărții Ramuri gamă A3-B14, accesați foaia de lucru 6 și lipiți în celulă A3.

Să începem consolidarea. Setați indicatorul de celulă laC3și selectați din meniu Date  Consolidare.

Pe listă Funcții trebuie selectat elementul Sumă. Introduceți în câmpul de introducere Legătură gama de celule ale căror date ar trebui supuse procesului de consolidare. Este convenabil să marcați o serie de celule folosind mouse-ul.

Plasați cursorul de intrare în câmp Legătură, faceți clic pe prima comandă rapidă a orașului, de exemplu – Almaty, selectați o serie de celuleD3- F14 și apăsați butonul Adăuga fereastră Consolidare. Ca urmare, intervalul specificat va fi rearanjat în câmp Lista intervalelor.

Apoi du-te la foaia celui de-al doilea oraș. Intervalul este indicat automat, apăsați butonul Adăuga si tot asa de 5 ori.

Dacă rândul de sus și/sau coloana din stânga conține titluri pe care doriți să le copiați în tabelul final, ar trebui să activați opțiunile corespunzătoare în grup Folosiți etichete. Deoarece în exemplul nostru rândul de sus conține antetele coloanei, trebuie să activăm opțiunea Pe linia de sus.

Dacă urmează să se stabilească o relație dinamică între datele sursă și datele consolidate din tabel, activați opțiunea Creați conexiuni cu datele sursă.

buton Revizuire ar trebui utilizat pentru a selecta fișierul care conține datele de consolidat.

Faceți clic pe butonul BINE.

La celulă A1 introduceți numele noului tabel Date rezumative.

Tastați în celulă B70 sens Total:, si in E70 - și apăsați tasta [ introduce]

Acum trecem la determinarea cotei din profitul total a sumei primite din vânzarea fiecărui produs. Intră F9 formulă = E9/$E$70 și copiați-l în restul celulelor coloanei F ( la celulă F70) .

Formatați conținutul coloaneiFîn stil procentual. Rezultatele obținute ne permit să tragem concluzii despre popularitatea unui anumit produs.

La consolidarea datelor, programul înregistrează fiecare element din tabelul final și creează automat o structură a documentului, care vă permite să afișați doar informațiile necesare pe ecran și să ascundeți detaliile inutile. Simbolurile structurii sunt afișate în partea stângă a tabelului. Numerele indică nivelurile structurii (în exemplul nostru - 1 Și 2). Butonul cu semnul plus vă permite să decriptați datele de nivel superior. Faceți clic, de exemplu, pe un buton pentru o celulă A9 pentru informații despre comenzile individuale.

Copiați formula dinF9 în celule F4- F8.

Numerele se transformă în diagrame

Munca pregatitoare. Deoarece fiecare diagramă are nevoie de propriul său tabel, să creăm un nou tabel pivot pe baza datelor din foaia de lucru Comenzi carte cu acelasi nume Comenzi. Deschideți un registru de lucru creat anterior Comenzi. Creați un nou registru de lucru și dați un nume primei sale foi Masa . Această fișă va conține materialul numeric pentru diagramă. Plasați indicatorul într-o celulă LA 3 și selectați meniul Date  Masă rotativă. Selectați prima metodă de aranjare a datelor - Într-o listă sau bază de date Microsoftexcela- apasa butonul Mai departe. În al doilea pas, plasarea cursorului de intrare în câmp Gamă urmează folosind meniul Fereastră accesați registrul de lucru Comenzi și în foaia de lucru Comenzi și evidențiați intervalulA 1- L 31 . Apoi faceți clic pe butonul Mai departe. Trebuie să definiți structura tabelului pivot. Așezați în zonă linii buton Numele produsului, și către zonă coloane – butonul Lună. Sumă va fi calculat pe câmp Pretul comenzii, acestea. mutați acest buton în zonă date . Faceți clic pe butonul Gata. Selectați un intervalB 4- F 14 . Dacă selectați un interval de celule cu mouse-ul, începeți selecția la orice celulă din interval, cu excepția celulei F 4 , care conține un buton de tabel pivot. Faceți clic pe butonul Chart Wizardîn bara de instrumente Standard. În primul pas, specificați tip de diagramă, faceți clic pe butonul Mai departe. În al doilea pas, confirmați interval =Tabel!$ B$4:$ F$15. În al treilea pas indicați parametrii diagramei (Titluri, Topoare, Legende etc.).Titlul diagramei introduce Volumul vânzărilor pe lună,Categoria (X)- Numele produsuluiȘi Sens( Y ) –Volumul vânzărilor (USD) . Modificările efectuate se vor reflecta imediat în imaginea din câmp Probă, faceți clic pe butonul Mai departe. Faceți clic pe butonul Gata.