Excel 2
-
Upload
razvan-cristian -
Category
Documents
-
view
3 -
download
0
description
Transcript of Excel 2
-
Seminar Microsoft Excel partea 2
1. Recapitulare formule i funcii
Nr. Pas Mod de rezolvare OBS. 1. O firm importatoare de produse de birotic a
importat n primele ase luni ale anului trei categorii de produse: creioane, pixuri i markere. Firma a vndut n fiecare lun toate produsele importate. Cunoscndu-se cantitile importate i preurile de import, precum i ali indicatori ai activitii firmei, s se determine profitul net pe fiecare din cele ase luni
2. n fiierul Seminar2 Excel.xls, activai foaia de calcul Date.
3. Calculai valoarea n vam lunar pentru fiecare categorie de produse.
- Se calculeaz celulele de pe randurile 5, 8 i 11, astfel: Valoare = Cantitate* Pre
4. S se determine valoarea total n vam lunar Celulele de pe randul 13.
n acest seminar vei utiliza urmtoarele funcionaliti ale pachetului software: Recapitulare formule i funcii Grafice Scenarii Goal Seek
-
(pentru toate categoriile de produse). 5. S se determine Cheltuielile cu importul aferente
fiecrei luni. - Se calculeaz celulele de pe randul 13. - Formula de calcul: Cheltuieli cu importul = Valoarea
in vam*(1-Taxa vamala)
Atenie la folosirea adresrii absolute!
6. S se determine veniturile din vanzri, prin aplicarea adaosului comercial (abreviat A.C.)
- Se calculeaz celulele de pe randul 23, dup formula: Venituri din vanzari = Cheltuielile cu importul * (1+Adaos comercial)
Atenie la folosirea adresrii absolute!
7. S se culculeze cheltuielile cu salariile, tiind c fondul de salarii n primele dou luni a fost de 20000 RON, dup care, din luna martie a crescut cu 15% i a rmas constant pn la sfritul perioadei.
Celulele de pe randul 16.
8. Conducerea firmei a hotrt s acorde prime angajailor si, dac Veniturile din vnzri depesc un anumit plafon. Dac n luna respectiv Veniturile din vnzri au depit plafonul (Plafon :30000 RON), se acord o prima (Prima :10%) din diferena dintre Veniturile din vnzri iPlafon.
- Se calculeaz celulele de pe randul 17, prin folosirea funciei logice IF:
o Condiie: Venituri vanzari > Plafon o Ramura DA: (Venituri vanzari Plafon)* Prima o Ramura NU: 0
9. S se determin taxele i impozitele aferente salariilor.
- Se calculeaz celulele de pe randul 18, dup formula: Taxe si impozite = (Fond Salarii + Prime) * ( Imp. Sal + taxe (CAS))
10. Calculai Cheltuielile salariale totale ca sum dintre Fond salarii, Prime, Taxe si impozite.
Celulele de pe randul 19.
11. Se cunoate c, n fiecare lun, cheltuielile cu chiriile i regia au fost n valoare de 10000 RON.
Celulele de pe randul 20.
12. Stabilii cheltuielilel unare totale, sa sum dintre Cheltuielile cu importul, Cheltuielile Salariale totale i Cheltuielile fixe.
Celulele de pe randul 21.
-
12. Calculai profitul brut. - Se calculeaz celulele de pe randul 25, dup formula: Profit brut = Venituri din vanzari Total Cheltuieli
13. Calculai profitul net. - Se calculeaz celulele de pe randul 26, dup formula: - Profit net = Profit brut *(1- Impozit profit)
2. Grafice
Nr. Pas Mod de rezolvare OBS. 1. n fiierul Seminar2 Excel.xls, activai foaia de
calcul Date
2. Creai un grafic care s prezinte structura cantitilor importate n luna Ianuarie.
- Selectai celulele care conin cantitile importate n Ianuarie (C3, apoi innd apsat, C6 i C9)
- Insert->Chart (Chart Wizard) o Selectai tipul de grafic (Pie) o Series in: Columns o Completai corespunztor Titlul,
Legenda i Etichetele o Place Chart - As New Sheet: Structura
3. Modificai legenda, astfel nct datele s fie uor de neles
- Selectai graficul - Chart->Source Data, Series - Category Labels: selectai A3, apoi innd
apsat, A6 i A9 - OK
4. Adugai i alte informaii pe grafic (valoarea, procentul, numele pentru fiecare felie)
- Selectai graficul - Chart->Chart Options, Data Labels - Category Name, Value, Percentage - OK
5. Creai un grafic cu bare verticale pentru a evidenia evoluia preului celor trei articole pe
- Datele de selectat: C4:H4; C7:H7; C10:H10 (Cu ajutorul tastei !!)
-
parcursul celor 6 luni - Chart Type: Columns - Opiunis: X-Axis Luna, Y-Axis Preul - Titlu: "Evolutia Pretului - Place Chart - As New Sheet (EvolutiaPretului)
6. Modificai tipul de grafic - Selectai datele din grafic o Chart->Chart Type
7. Creai o legend cu sens - Selectai graficul o Chart->Source Data, Series o Series Name (un nume pentru fiecare
set de date: Creioane, Pixuri, Markere) o Category X Axis Labels (C2:H2)
8. Adaugai alte informaii pe graphic (Valoarea) - Selectai graficul o Chart->Chart Options, Data Labels o Value
9. Salvai fiierul
3. Scenarii
Nr. Pas Mod de rezolvare OBS. 1. In fiierul Seminar2 Excel.xls, activai foaia de
calcul Date
2. Se presupune c taxele vamale scad, de la 5% la 3%. Cum se modific profitul net?
- Tools->Scenarios, Add - Scenario Name: taxe imp3% - Changing Cells: K3, OK - New Value: 0,03, OK
3. Creai un raport pe baza scenariului - Selectai opiunea Summary - Result cells: C26:H26 (Profitul net)
5. Salvai fiierul
-
4. Goal Seek (Cutarea valorii int)
Nr. Pas Mod de rezolvare OBS. 1. n fiierul Seminar2 Excel.xls, activai foaia de
calcul Date
2. Deteminai ct ar fi trebuit s fie adaosul comercial, astfel nct firma s obin n luna iunie un profit de 32000 RON.
- Tools->Goal Seek: o Set cell: profit net iunie o To value: 32000 o By changing cell: Adaos comercial
- Apsai OK dac vrei s pstrai noile valori, sau Cancel, n caz contrar
3. S-a decis ca un capital de 10,000 s fie depus la banc sub forma unui depozit avnd dobnda anual de 3%. tiind c dobnda este compus, s-a calculat (prin dou variante) capitalul final care se obine dup o perioad de 5 ani.
Deschidei fiierul Seminar2 Excel.xls, foia de calcul Goal Seek i analizai modul de calcul al dobnzii compuse, prin cele dou variante.
4. Dorim ca dup perioada de cinci ani s avem n cont un capital final de 15.000 . Ct de mare trebuie s fie capitalul iniial, dac se acord acceai dobnd?
Pentru ambele variante aplicai paii urmtori: Tools->Goal Seek:
o Set cell: capitalul final dup 5 ani o To value: 15000
Prin Goal Seek se poate defini o singur celul care i modific valoarea!
Presupunem c celula D (celula rezultat) se calculeaz pe baza celulelor A, B i C (celule parametru), adic D = f(A, B, C). Facilitatea Goal Seek ne permite ca, stabilind o valoare pentru celula rezultat, i cunoscnd, de exemplu valorile parametrilor B i C, s se calculeze valoarea parametrului A. Prin Goal Seek putem spune c se realizeaz un drum invers fa de modul obinuit de calcul a valorii unei celule n MS Excel. De precizat c pot exista oricte celule parametru care s influeneze celula rezultat, dar c se poate determina numai valoarea unei singure celule parametru.
-
o By changing cell: capitalul iniial la nceputul celor 5 ani
-
Seminar Microsoft Excel partea a III-a
1. Solver
Nr. Pas Mod de rezolvare OBS. 1. In fiierul Seminar Excel 3.xls, activai foaia de
calcul Solver
2. Specificai parametrii pentru problema dat Tools->Solver Target Cell: D18, Max (Profitul total) Changing Cells: D9:F9 (Cantitatile de produs) Restrictions: Add C11:C15 = 0
3. Gsii soluia optim Solve 4. Salvai rezultatul gsit sau revenii la problema
iniial
5. Salvai fiierul
2.Liste (simularea lucrului cu baze de date n Excel) Nr. Pas Mod de rezolvare OBS. 1. In fiierul Seminar Excel 3 foaia de calcul
Vanzari
n acest seminar vei utiliza urmtoarele funcionaliti ale pachetului software: Solver Lucrul cu meniul Data: Form, Sort, Filter, Subtotals, validare de celule, Pivot Table
-
2. Adaugai o nou nregistrare Selectai domeniul de celule A1: H16 Data->Form; selectai butonul New Adugai o nou nregistrare valid
Cnd se lucreaz cu astfel de tabele, trebuie ca cel puin o celul din list s fie selectat
3. Utilizai diverse criterii de cutare pentru vanzari Data->Form; selectai butonul Criteria 1. Toate vanzarile care au client Real 2. Toate vanzarile din luna martie
Pentru a parcurge toate nregistrrile care satisfac criteriul se apas butonul Find Next
4. Validai datele din list astfel nct n cmpul Luna s poat fi introduse numai valori ntre1 i 12
Selectai domeniul de celule A2:A16 Data->Validation Completai n fereastra Data Validation informaiile:
a. Settings Allow: Whole Number, Between, Min:1, Max:12
b. Input Message Titlu: Luna; Message: Introducei un numar ntreg ntre 1 i 12!
c. Error Alert Style: Stop; Title: Luna; Message: Valoare greit!
Introducei valori corecte i greite n celulele pentru care s-a realizat validarea
5. Selectai din list numai vanzarile din luna februarie, ale furnizorului Stabilo
Selectai domeniul de celule A1: G16 Data->Filter->AutoFilter Executai un click pe sgeata aprut n dreptul lunii i selectati 2. Similar pentru furnizor.
6. Revenii la situaia iniial Data->Filter->AutoFilter
7. Sortai lista dup cmpurile Client i Articol Data->Sort (Client, Articol)
8. Calculai valoarea total a vnzrilor pentru fiecare client i per total
Data->Subtotals Lista trebuie mai nti sortat dup Client!
-
9. Revenii la situaia iniial Data->Subtotals: Remove All
10. Realizai un raport privind valoarea vnzrilor ctre fiecare client, evideniind ct a cumprat clientul de la fiecare furnizor, care s arate ca cel din figura de mai jos.
Data-> PivotTable and PivotChart-Report Pasul 1 - Selectai opiunile urmtoare: - Microsoft Office Excel list or database - Pivot Table Pasul 2 Selectai domeniul de celule pentru care vrei s realizai tabelul pivot, incluznd obligatoriu i capul de tabel al listei Pasul 3 Alegei locul n care vrei s plasai tabelul pivot (New Worksheet) Tragei cu Drag&Drop cmpurile din Pivot Table Field List pe suprafaa de lucru, astfel: - n Drop Raw Fields Here plasai cmpul care
reprezint criteriul de grupare pe orizontal - n Drop Column Fields Here plasai cmpul care
reprezint criteriul de grupare pe vertical - n Drop Data Fileds Here plasai cmpul care
reprezint informaiile de baz pentru care realizai raportul
Filtrai datele din raport astfel nct s cuprind numai comenzile nregistrate n luna februarie, plasnd cmpul dup care realizai filtrarea n Drop Page Fileds Here.
11. Salvai fiierul
Valoare
Furnizor Client
Seminar2 -Excel.pdfSeminar_3_Excel.pdf