model test 1
DESCRIPTION
ase managementTRANSCRIPT
1Nume PrenumeSucursalaDepartamentGenul (M=masculin F=feminin)Vrsta (ani)Data angajriiSpor vechime (%)Salariu ncadrarePOPESCU ViorelDoctor FelixCreditareM47 aniFri Jul 02 00:00:00 EDT 20100%1,560 leiMIHAI Carmentefan cel MareCreditareF24 aniSun Jun 20 00:00:00 EDT 20100%1,560 leiPOPA Florintefan cel MareCorporaiiM49 aniSun Dec 27 00:00:00 EST 198725%2,944 leiALBU ConstanaDoctor FelixCreditareF32 aniThu Feb 24 00:00:00 EST 20055%1,885 leiNEDELCU Mihaitefan cel MareOficiul juridicM31 aniSat Jun 12 00:00:00 EDT 20045%2,377 leiCHIRU BogdanPlevneiCreditareM23 aniFri Dec 12 00:00:00 EST 20080%2,005 leiALBITER OanaPlevneiCreditareF40 aniMon Oct 16 00:00:00 EDT 199515%2,018 leiVLAD Petrutefan cel MareOficiul juridicM50 aniWed Aug 08 00:00:00 EDT 199020%2,670 leiCORNEA AnaDoctor FelixCorporaiiF50 aniTue Apr 03 00:00:00 EDT 199020%2,944 leiMONDREA Lauratefan cel MareCreditareF49 aniSat Apr 09 00:00:00 EDT 199420%2,350 leiSAVU Ctlintefan cel MareAsigurriM40 aniWed Dec 03 00:00:00 EST 201410%2,080 leiPANAIT Corneltefan cel MareCreditareM51 aniWed Dec 10 00:00:00 EST 20140%1,560 leiCOMAN Auratefan cel MareAsigurriF28 aniTue Dec 12 00:00:00 EST 199515%2,080 leiBLAGA MdlinaPlevneiCorporaiiF23 aniSun Jan 17 00:00:00 EST 20100%1,900 leiMIHAI DinuDoctor FelixCreditareM24 aniWed Aug 06 00:00:00 EDT 20080%2,005 leiDEDU Nataliatefan cel MareCorporaiiF48 aniSun Feb 02 00:00:00 EST 198625%2,944 leiGANEA Paveltefan cel MareCreditareM27 aniSat Jun 20 00:00:00 EDT 20090%1,560 leiSORESCU LianaPlevneiRelaii clieniF31 aniSat Apr 21 00:00:00 EDT 200110%1,885 leiPETRE CristinaDoctor FelixRelaii clieniF41 aniMon Apr 18 00:00:00 EDT 199420%2,670 leiMNESCU Andatefan cel MareCorporaiiF28 aniTue Apr 04 00:00:00 EDT 20065%1,900 lei
2 pctS se formateze condiional (domeniul B2:B21 - fundal gri, font rosu) toii salariaii angajai in luna curenta la departamentul Creditare si Asigurari.
2Nume PrenumeSucursalaDepartamentVrsta (ani)Data angajriiVechimea (ani)Spor vechime (%)Salariu ncadrareNume PrenumeCod numeric personalPOPESCU ViorelDoctor FelixCreditare2/2/111,500 leiALBITER Oana2691107345633MIHAI Carmentefan cel MareCreditare5/3/092,500 leiVIAN Alexandra2721225557812POPA Florintefan cel MareCorporaii12/27/893,500 leiMNESCU Anda2811010510053ALBU ConstanaDoctor FelixCreditare2/24/054,500 leiCHIRU Bogdan1860817341871NEDELCU Mihaitefan cel MareOficiul juridic6/12/072,340 leiNEDELCU Mihai1790130333655CHIRU BogdanPlevneiCreditare12/12/083,400 leiCORNEA Ana2591207472590ALBITER OanaPlevneiCreditare10/16/931,240 leiSORESCU Liana2790312508405VLAD Petrutefan cel MareOficiul juridic8/8/905,400 leiPOPESCU Viorel1621212345345CORNEA AnaDoctor FelixCorporaii4/3/903,670 leiBLAGA Mdlina2861123546967MONDREA Lauratefan cel MareCreditare4/9/946,700 leiPETRE Cristina2690209310349SAVU Ctlintefan cel MareAsigurri3/20/003,200 leiMIHAI Dinu1850602422877PANAIT Corneltefan cel MareAsigurri10/10/821,670 leiMONDREA Laura2600615100121COMAN Auratefan cel MareAsigurri12/12/062,800 leiCOMAN Aura2820304227773BLAGA MdlinaPlevneiCorporaii1/17/113,900 leiPANAIT Cornel1590501673895MIHAI DinuDoctor FelixCreditare3/6/084,500 leiMIHAI Carmen2860227503812DEDU Nataliatefan cel MareCorporaii2/2/862,340 leiPOPA Florin1601001701902GANEA Paveltefan cel MareOficiul juridic5/3/093,400 leiGANEA Pavel1821101555889SORESCU LianaPlevneiRelaii clieni4/21/011,240 leiSAVU Ctlin1700404222888PETRE CristinaDoctor FelixRelaii clieni4/18/945,400 leiDEDU Natalia2610722114674MNESCU Andatefan cel MareCorporaii4/4/063,670 leiALBU Constana2770904444890VIAN AlexandraPlevneiOficiul juridic11/16/997,900 leiVLAD Petru1600205667445TOTAL SALARII DE NCADRARE?
4 pct1. S se calculeze "Vrsta" pentru fiecare salariat, n funcie de Codul Numeric Personal (CNP) din tabelul J1:K22 (numit "CNP")
2. S se calculeze numrul de salariai pe urmtoarele grupe de vrst:
< 30 ani?30 - 40 ani?41 - 50 ani?> 50 ani?
3Nume PrenumeSucursalaDepartamentGenul (M=masculin F=feminin)Vrsta (ani)Data angajriiVechimea (ani)Spor vechime (%)Salariu ncadrareNume PrenumeCod numeric personalPOPESCU ViorelDoctor FelixCreditare2/2/111,500 leiALBITER Oana2691107345633MIHAI Carmentefan cel MareCreditare5/3/092,500 leiVIAN Alexandra2721225557812POPA Florintefan cel MareCorporaii12/27/893,500 leiMNESCU Anda2811010510053ALBU ConstanaDoctor FelixCreditare2/24/054,500 leiCHIRU Bogdan1860817341871NEDELCU Mihaitefan cel MareOficiul juridic6/12/072,340 leiNEDELCU Mihai1790130333655CHIRU BogdanPlevneiCreditare12/12/083,400 leiCORNEA Ana2591207472590ALBITER OanaPlevneiCreditare10/16/931,240 leiSORESCU Liana2790312508405VLAD Petrutefan cel MareOficiul juridic8/8/905,400 leiPOPESCU Viorel1621212345345CORNEA AnaDoctor FelixCorporaii4/3/903,670 leiBLAGA Mdlina2861123546967MONDREA Lauratefan cel MareCreditare4/9/946,700 leiPETRE Cristina2690209310349SAVU Ctlintefan cel MareAsigurri3/20/003,200 leiMIHAI Dinu1850602422877PANAIT Corneltefan cel MareAsigurri10/10/821,670 leiMONDREA Laura2600615100121COMAN Auratefan cel MareAsigurri12/12/062,800 leiCOMAN Aura2820304227773BLAGA MdlinaPlevneiCorporaii1/17/113,900 leiPANAIT Cornel1590501673895MIHAI DinuDoctor FelixCreditare3/6/084,500 leiMIHAI Carmen2860227503812DEDU Nataliatefan cel MareCorporaii2/2/862,340 leiPOPA Florin1601001701902GANEA Paveltefan cel MareOficiul juridic5/3/093,400 leiGANEA Pavel1821101555889SORESCU LianaPlevneiRelaii clieni4/21/011,240 leiSAVU Ctlin1700404222888PETRE CristinaDoctor FelixRelaii clieni4/18/945,400 leiDEDU Natalia2610722114674MNESCU Andatefan cel MareCorporaii4/4/063,670 leiALBU Constana2770904444890VIAN AlexandraPlevneiOficiul juridic11/16/997,900 leiVLAD Petru1600205667445
3 pct
1. Sa se determine genul angajatilor in functie de CNP (Daca CNP incepe cu "1" sa se afiseze "M", altfel sa se afiseze "F")
2. Sa se determine salariul mediu de incadrare in functie de gen, cu restrictionarea interactiva a angajatilor in functie de departamentul de incadrare.