Science économie et gestion

Informatique de gestion

S4

Prof : EL Otmani Fouad

TabledesmatièresObjectifsdelaformation

Généralités

Fonctionnalitésd’Excel

InstalleretsedéplacerdanssonenvironnementExcel

L’interfaced’Excel

Sedéplacerdansleclasseur

Sélectiondescellules

OngletAffichage

Focussuruneoptiond’affichage:Figerlesvolets

Optionsd’affichageetenvironnement

Miseenformedutableau

Sélectionner,inséreretsupprimerdeslignes/colonnes

Sélection

Insertion/Suppression

Ajustementdelatailledescolonnesetdeslignes

Mettreenformelesdonnéesdansunecellule

Alignementverticalethorizontal

Formatdecellule

Formatsdemiseenforme

Miseenformeconditionnelle

Miseenformeconditionnelle,suite…

Filtrerettrierlesdonnées

Filtresautomatiques

Filtresélaborés

Outilspratiques

Optionsdecollage

Fenêtre«Collagespécial»

Rechercher

Remplacer

Maîtriserl’impressiond’untableau

Différentesméthodesd’impression

Aperçuavantimpression

Miseàl’Échelle

Zoned’impression

ModePage

Aperçudessautsdepage

En-têtesetpiedsdepage

Rapidement…

Plusendétail…

En-têtesetpiedsdepageprédéfinis

En-têtesetpiedsdepagepersonnalisés

Validerouquitterl’éditiondel’en-tête/pieddepage

Formulesetfonctions

Àquoiçasertetcommentçamarche?

Inséreruneformulesimplement

Recopiesetséries

Recopieverticaleouhorizontale

Explicationsynthétiquedesréférencesabsolues,relativesetmixtes

Opérateurs

Opérateursdecalcul

Opérateursdecomparaison

Opérateursderéférences

Opérateuralphanumérique

Fonctionsdebase

Fonctionssurdescellulescontenantdutexte

Fonctionsd’extraction

Fonctionsconditionnelles

Fonctionsderecherche

Fonctionsfinancières

Autresfonctions“utiles”

Auditdesformules

Examendesliaisons

Afficherlesformules

Évaluationdesformules

Référencesabsolues/relatives/mixtes

Référencerelative

Référenceabsolue

Référencesmixtes

Référenceàuneautrefeuille/àunautreclasseur

Attributiond’unnom

Créerunnom

Gestionnairedenoms

Lesséries

Suiteschronologiques

Suitesnumériques(linéairesougéométriques)

Sériesalphanumériques

Sériespersonnalisées

Tableauxcroisésdynamiques(TCD)

Créeruntableaucroisédynamique(TCD)

Rajouterunecolonnedecalcul

Protégerfichiers,classeursetfeuilles

Protégerlefichier*.xlsx

Protégerleclasseur

Protégerunclasseurcontrelamodificationglobale(structure)

Protégerunefeuille

Protégerunefeuilleouuneplagedecellules(ouunecelluleindividuelle)

Valeurcible

Créerdesmacros

Objectifsdelaformation

Découvrirouredécouvrirlesfonctionsessentiellesd’Excel.

Acquérir les connaissances suffisantes pour réaliser, en autonomie, lesprincipalesmanipulationssurlesfichiersauformatExcel.

GénéralitésFonctionnalitésd’Excel

Excel est un tableur, c’est-à-dire un logiciel qui permet de manipuler desdonnéessousformedetableaux.

Ilestdestinéàfairedestableauxintégrantsdescalculssousformedeformules(opérationscourantes,statistiques,simulationschiffrées).

Excelpeutégalementgérerdeslistessimples(ex:listedeclientsoudedossiersàtraiter…).

Il permet aussi de mettre en forme les résultats obtenus (sous forme degraphiques,tris…).

Etbiend’autreschosesencore…

Depuis la version d’Excel 2007, l’enregistrement des fichiers porte l’extension *.xlsx(pourlesfichiersExcelsansmacros)et*.xlsm(pourlesfichiersExcelavecmacros).

InstalleretsedéplacerdanssonenvironnementExcelL’interfaced’Excel

SedéplacerdansleclasseurDansExcel,oncréedesFEUILLESquel’onmetdansunCLASSEUR.

LeCLASSEURest leconteneur(c’estvotrefichier.xls)et lesFEUILLES sont lesdocumentsincorporés au classeur. Comme physiquement, on peut créer plusieurs feuilles dans unmêmeclasseur.

Ex:leclasseurInventaire.xlsxcomprendlesfeuillesdestockde2008,2009,2010,2011…

Si par défaut les onglets qui identifient lesfeuilles de calcul numérotent ces dernièressous la forme Feuil1, Feuil2, Feuil3, etc.,vous pouvez choisir des noms plusexplicites.

RENOMMERUNONGLET

Cliquezgauchesurl’ongletd’unefeuilledecalcul pour l’activer. D’un clic droit surl’onglet, ouvrez le menu contextuel et

cliquezsurRenommer.Saisissezlenomdevotrechoixetvalidez.

Résultat:

RÉORGANISERLESONGLETS

On peut souhaiter déplacer une feuille avant/après une autre (ex : mettre la feuille“Inventaire 2013” en premier, avant “Inventaire 2011”). Pour ce faire, avec le boutongauchedelasouris,restezcliquésurl’ongletàdéplaceretfaites-leglisseràl’endroitvoulu.

Vousremarquerezqu’uncurseurnoir(triangle)indiquelapositionfinale.

POURAGIRSURPLUSIEURSFEUILLESENMÊMETEMPS,ONUTILISELASÉLECTIONMULTIPLE

:

Poursélectionneruneouplusieursfeuilles,onprocèdeainsi:

- Une feuille : clic gauche sur l’onglet. L’onglet de la feuille sélectionnéedevientblanc.

-Desfeuillesadjacentes:clicgauchesurl’ongletdela1èrefeuille;Maj(Shift)+clicgauchesurl’ongletdeladernièrefeuille.

-Desfeuillesnonadjacentes:clicgauchesurl’ongletdela1èrefeuille;Ctrl+clicgauchesurchaqueautreongletdefeuillequel’onsouhaitesélectionner.

-Touteslesfeuillesduclasseur:clicdroitsurunonglet>Sélectionnertouteslesfeuilles.

SélectiondescellulesIlexisteplusieursfaçonsdesélectionnerdescellules.Nousneprésenteronsquelespluscourantes.

Jesouhaite Commentfaire?

Sélectionner1cellule

Si vous êtes en cours de saisie, validez pour sortir de cemode(parexemple,entapantsurEntrée),

pointezsurlacellulepuiscliquezgauche.

Vous pouvez aussi atteindre la cellule avec lesflèchesduclavier.

Modifier le contenud’une cellule quicontient déjà desinformations

Placez-vous sur la cellule (clic gauche dessus) puisappuyezsurF2.

Oudouble-cliquezgauchesurlacelluleàmodifier

ou cliquez dans la barre de formules puismodifiez votretexte.

Atteindre la celluleA1

Ctrl+↖[Home]

Atteindre la dernièrecellule de la zoneactive

Ctrl+Fin[End]

Atteindre la dernièrecellule de la ligneactive

↖[Home]

La prochaine celluleayantuncontenu

Un double-clic sur la bordure droite de la cellule A7sélectionneraautomatiquementlacelluleG7.

Un double-clic sur la bordure supérieure d’A5sélectionneraautomatiquementlacelluleA3.

La combinaison double-clic + Maj permettra desélectionnerlacellulesurlaquelleonacliqué+touteslescellulesavantlaprochainecelluleayantuncontenu.(Ex:enfaisantundouble-clic+Maj.surA7,jesélectionnerailaplageA7:G7)

Sélectionner uneplagedecellules

Cliquez-glissezdela1èreàladernièrecellule.

Oucliquezsurla1èrecellule; restezappuyésur la toucheMaj puis cliquez sur la dernière cellule de la plage;relâchezlatoucheMaj.

Sélectionner descellules nonadjacentes

Sélectionnezla1èrecellule(ouplage);restezappuyésurlatouche Ctrl puis sélectionnez les autres cellules (encliquantsur lescellulesuneàuneouenfaisantglissez lasourissurlaplagesouhaitée).

Sélectionner la zoneactive

Ctrl+*

Sélectionner unecolonne ou ligneentière

Colonne:cliquezsurl’en-têtedelacolonne

Ligne : cliquez sur l’en-têtedelaligne

Sélectionnerplusieurscolonnesoulignes

nonadjacentes

Ctrl+Clicgauchesurchaqueen-têtedecolonnes (oudelignesseloncequel’onsouhaitesélectionner)

Sélectionner toutesles cellules de lafeuille

Avecleclavier:(Ctrl+A)2fois

Avec la souris : cliquez sur (icône se trouvant àl’intersectiondelaligneetdelacolonned’en-tête).

Rempliruneplagedecellules d’une mêmevaleur

Sélectionnezlaplagedecellules,tapezlavaleurpuisCtrl+Entrée.

OngletAffichageCommedanstousleslogicielsdelasuiteOffice,onretrouvel’ongletAffichage.Ilpermetd’adapterl’affichageàl’écranpourunmeilleurconfortvisuel.

Options courantes : zoom,modenormal,modemise enpage, affichagedesbordures,ajustementdesfenêtres(volets,panneaux…).

Lechangementd’optionsdecetongletAffichagen’auraaucuneinfluencesurl’impressiondevosdocuments.

Vued’ensembledel’ongletAffichage

Fonctionsimportantesconcernantl’affichage.

GroupeAffichagesClasseur

NORMAL : affichage habituel, généralementpardéfaut.

MISEENPAGE:afficheledocumenttelqu’ilapparaîtra sur la page imprimée (sorted’aperçuavantédition)

APERÇUDESSAUTSDEPAGE:permetdevoircombien de pages vont être imprimées et àquelsendroitsletableauseracoupé.

GroupeAfficher

RÈGLE : la case est grisée, ce qui signifiequ’on ne peut pas la décocher [OptiondisponibleenmodePage].

QUADRILLAGE : affiche les traits entre leslignesetlescolonnespourfaciliterlalectureetl’éditiondutableau.

TITRES : en-têtes des colonnes et des lignes(à,B,C…1,2,3…)

GroupeZoom

100 % : permet de revenir à un zoom debaseà100%

ZOOMSURLASÉLECTION :permetdemettre

l’accentsurlescellulessélectionnées.

GroupeFenêtre

NOUVELLEFENÊTRE : permetdedupliquer àl’identique la fenêtre sur laquelle ontravaille…Peuintéressant!

RÉORGANISER TOUT : permet d’afficherautomatiquement les différentes fenêtresouvertesd’Excelsur1seulécran.

FIGERLESVOLETS:voirpageprécédente

FRACTIONNER:diviselafenêtreenplusieursvolets redimensionnables contenant les vuesde la feuille de calcul. Intérêts : avoir sur 1mêmeécran2partieséloignéesde la feuilledecalcul.

MASQUER : permet de masquertemporairement le classeur sur lequel ontravaille.Peuutile!

Focussuruneoptiond’affichage:FigerlesvoletsONGLETAFFICHAGE,GROUPEFENÊTRE,MENUFIGERLESVOLETS

Cet outil permet de garder des lignes et/ou des colonnes visibles lors de déplacementsdanslafeuille.

Ex:unclientnoustransmetsoninventaireparmail.Cetinventairecomprendplusde1000lignes.Grâceàcetoutil,onpeutdemanderàconserverlesen-têtes(ligneset/oucolonnes).

Decettefaçon,mêmeenbasdutableau,onsauratoujoursàquoicorrespondlacolonneoulalignesurlaquelleonsesitue.Voircopied’écranci-dessous.

Lignedetitre

Contenu

Optionsd’affichageetenvironnementLe choixde ces options influence le fonctionnement et l’affichagedu logiciel. Pour lesmodifier,rendez-vousdanslemenu:FICHIER/OPTIONS

Cemenupermet,entreautreschoses,parl’ongletOPTIONSAVANCÉESde:

-Afficherlesformulesàlaplacedeleurrésultatdansletableau

-Afficherounonlequadrillage

- Afficherounon lesvaleurszéro (dans la feuilleactiveetnondans tout leclasseur)

Onpeutaussiaffinerlesréglagesconcernantl’enregistrementdesdonnées,ledossierdepréférencespour l’enregistrement, lenombrede feuillesviergesà l’ouvertured’Excel…etc.

Àexplorer!

NB:cesoptionsn’aurontaucunimpactsurl’impressiondudocument!

MiseenformedutableauSélectionner,inséreretsupprimerdeslignes/colonnesÀ l’ouverture d’Excel, il a 1 048 576 lignes et 16 384 colonnes…de quoi faire !Celan’empêchepasmoinsqu’onaitbesoind’ajouterdeslignesetdescolonnespourintercalerdesinformations.

Sélection

Pour sélectionner une LIGNE ENTIÈRE,cliquezsurlen°delaligneenquestion.

PoursélectionneruneCOLONNEENTIÈRE,cliquezsurlalettredelacolonneenquestion.

Sélectiondelignesoudecolonnescontigües

1. Placerlasourissurla1èrecolonneàsélectionnerpuismaintenirleclicgauchedelasouris.

2. Faireglisserlasouris jusqu’àladernièrecolonneàincluredanslasélectionpuisrelâcher.

Insertion/Suppression

Ex:insérerunecolonneavantB

Faireunclicdroit sur l’en-têtede colonne (surB)puisclicgauchesurINSERTION.

Ex:supprimerlacolonneB

Faireunclicdroit sur l’en-têtede colonne (surB)puisclicgauchesurSUPPRIMER.

TOUTESCESOPÉRATIONSSONTAUSSIVALABLES

POURLESLIGNES!

AjustementdelatailledescolonnesetdeslignesPardéfaut,touteslescellulesd’unefeuilledecalculviergesousExcelaffichentlesmêmesproportions:80pixelsdelargesur20pixelsdehaut.Ilestrarequecettetaillepardéfautconvienne à toutes les situations. Vous devrez donc ajuster la taille des cellules à vosbesoinsréels.

Pourredimensionnerlescellules,vouspouvezprocéderdedifférentesfaçons.:

1)Lapremièreconsisteàplacerlecurseur de la souris sur lesbordures d’une étiquette de ligneou de colonne et d’effectuer unmouvement de glisser-déplaceravecvotresouris.

Lepointeurdelasourischangealorsd’apparence.Enétirantversladroite,vousélargissezlacellule;verslagauche,vouslaréduisez.

Procédezdelamêmefaçonpourajusterlahauteurdeslignes.

NB:danscecas,lamodificationdelataillenes’appliquequ’àlaligneouàlacolonnequevousaviezsélectionnéeetnonàl’ensembledelafeuilledecalcul.

Pourquelamodifications’appliqueàtoutesleslignesetcolonnes,

cliquezpréalablementsurpuisredimensionnezlescellulescommevuaupoint1).

2) Si la 1ère solution ne vous convient pas, il estpossibledeprocéderdifféremment:

Activerl’ongletACCUEIL–GroupeCELLULES.

Onpeutalorsdéfinirunehauteurdeligneouunelargeur de colonne fixe, on peut aussi ajuster lahauteuroulalargeurdelacelluleaucontenu.

ASTUCE :Endouble-cliquantsur labordured’une étiquette de cellule (entreC etD par

ex),lacolonnes’ajusteautomatiquement.

Idempourleslignes.

MettreenformelesdonnéesdansunecelluleVoici ce qui se passe lorsqu’on saisit desinformationsdansdescellulessansaucunemiseenforme:

Leslibellésnesontpascentrés(ni verticalement ni horizontalement), unretouràlaligneautomatique(voir3)aétéappliqué à la cellule D2 puisque le

contenuétaittroplargeparrapportàlatailledelacelluled’origine.

Alignementverticalethorizontal

Depuis la version 2007 d’Excel, on a accès à des outilsperformants pour l’alignement des données dans lescellules.

Cesoutilssesituentdansl’ongletACCUEIL–groupeALIGNEMENT.

1) Ajuste l’alignementvertical du textedans la cellule.L’icônesélectionnéesur lacopied’écranmontreparexempleque letexte est aligné en bas de la cellule. L’icône du milieu permet decentrerletexteaumilieuverticaldelacellule.

2) Ajustel’alignementhorizontaldutextedanslacellule.L’icônesélectionnéesurlacopied’écranmontreparexemplequeletexteestalignéaumilieudelacellule.Ilestcentré.

3) Renvoieautomatiquementletexted’unecelluleàlaligne(permetdevoirl’ensembleducontenudecettecelluledansuneseulecellule).Cf.celluleD2delacopied’écran1.

4) Fusionneretcentrer:permetderelier lescellulessélectionnéesenuneseuleetdecentrerlecontenuhorizontalement.Encliquantsurlemenudéroulant,ontrouveaussiunefonctionquipermetdefusionnerplusieurs lignes, ligneparligne.

5) Commesur tous lesgroupes,cettepetite icônepermetd’afficher laboîtededialoguedéveloppée,en lienaveccegroupe (en l’occurrence,pourcegroupe,ceseralaboîtededialogueFORMATDECELLULE/ALIGNEMENTquis’affichera).

FormatdecelluleSelonsoncontenu,unecellulen’aurapaslemêmeformat.

- Notezd’ailleursque2cellules contigüespeuventnepasavoir lemêmeformat.

-Ilestessentieldechoisirlebonformatpourplusieursraisons:

Lisibilité du tableau (18695785569 n’est pas lu de lamême façon s’il estécrit18695785569,00)Formules : certaines fonctions nécessitent des formules qui analysent leformat des données pour afficher le résultat (si on écrit une date (ex :31/12/2012)dansunecellulequiaun formatde typeStandard,onauraenretour 41274 [qui correspond au nombre de jours passés depuis le01/01/1900,date«zéro»d’Excel]).

Formatsdemiseenforme

STANDARD :aucunformatspécifique.C’est leformatpardéfaut pour toutecelluledanslaquelleonn’apasencoreécrit.

NOMBRE:formatadaptépourlesnombrespourlesquelsonn’apasbesoindesymbolesmonétaires(€,£,$…).Ceformatestparticulièrementpratiquepuisqu’ilpermetd’incluredesséparateursdemilliers.Onpeutaussichoisirlenombrededécimales(2pardéfaut).

MONÉTAIRE:mêmeformatqueNombremaisaveclapossibilitéd’ajouterunsymbolemonétaire(€,£,$…).Ceformatgèreaussilamiseenformeautomatiquedesnombresnégatifs.

COMPTABILITÉ:mêmeformatqueMonétairemaisaveclapossibilitéd’alignerlessymbolesmonétairesetlesdécimalessurunecolonne.

DATE:demultiplesformatssontdisponibles(JJ/MM/AAAA,jourmoisannéeentouteslettres,JJmoisentouteslettres.Lorsqu’ilyauneétoiledevantleformat,celasignifiequeleformatvarieraenfonctiondesparamètresrégionaux(ex:pourdesAnglais,JJ/MM/AAAAs’afficheraMM/JJ/AAAA).

HEURE:commepourladate,onpeutchoisirleformatadaptéànosbesoins.

POURCENTAGE:multiplielavaleurdelacellulepar100etaffichelerésultataveclesigne%.

FRACTIONS:afficheparexemple0,5en1/2.

TEXTE : si l’onappliquece format, le texte entré est traité commedu texteordinairemêmesic’estunnombre.Ceformatpeutêtreutilepourunnumérodetéléphoneparexpuisqu’avoirleformatpardéfaut,le0(zéro)estsupprimé.

SPÉCIAL : les formats proposés dans cette liste servent à des usages trèsparticuliersdetypen°desécuritésociale,n°detéléphone,codepostal.

PERSONNALISÉ : permetdecréerun formatparticulier qui n’existe pas pardéfaut(surtoututilepourlamiseenformedesdates).

Formatsdisponibles

Descriptionsuccincte

MiseenformeconditionnelleEnappliquantunemiseenformeconditionnelleàvosdonnées,vouspouvezd’unsimplecoupd’œilidentifierrapidementlesécartsdansuneplagedevaleurs.

CetoutilsetrouvedansACCUEIL/GroupeSTYLE/MISEENFORMECONDITIONNELLE

À votre convenance, vous pouvez suivre lesassistants préconfigurés en choisissant un des 5premierschoix.

Si vous souhaitez élaborer vous-même vousrègles de mise en forme, cliquez sur “Nouvellerègle…”

La fenêtre suivante s’ouvre et vous proposedifférentstypesderègles.

Prenonsl’exempledelarègle:

“Appliquer une mise en forme uniquement auxcellulesquicontiennent…”.

En

combinant les différentes options, on peut créer une mise en forme en cliquant sur leboutonFormat…

Miseenformeconditionnelle,suite…Poursélectionnerunelignecomplèteenfonctiond’uneformule,seplacersur:

- “Utiliserune formulepourdéterminerpourquelles cellules le format seraappliqué”

-Danslabarredeformules,entrer:

=$A1=“x”

-ChoisirunemiseenformeencliquantsurFormat…

-ValidezencliquantsurOK.

FiltrerettrierlesdonnéesLa fonction première d’Excel est le traitement de données. Cela inclut donc le tri, lefiltrage,lasélectiondecesdonnées.Sanstoutcela,l’exploitationdesinformationsseraitimpossible.

FiltresautomatiquesLes filtres permettent de sélectionner des données d’un tableau qui répondent à un ouplusieurscritères.Cescritèrespeuventêtrededifférentesnatures(texte,date,numérique,couleurdepolice…).

Pourmettreenplaceunfiltre,procédercommesuit:

1. Vérifier quechaquecolonne à

filtrerabienuntitre

2. Dans l’onglet ACCUEIL, groupe EDITION, cliquezsurTRIERETFILTRERpuissurFILTRER.

Suite à cette manipulation, tous les en-têtes decolonnes filtrables vont avoir un boutonsupplémentaire en forme de flèche permettant dechoisirlefiltreràappliquer:

3. IndiquerouchoisirlesfiltreràappliquerpuiscliquersurOK.

Ex:onnesouhaiteafficherqueleslignesoùquPoint=4etdontlacouleurdutexteestrouge.

NB1:pourannulerlefiltrer,ilsuffitderecliquersurcettemêmeflècheetcliquersur

EFFACERLEFILTREDEQUPOINT

NB2:ilestpossibledefiltrersurplusieurscolonnesenreproduisantlamêmeprocédure(àpartirdel’étape3évidemment!)

FiltresélaborésPour information, il existe aussi des filtres élaborés qui permettent l’extraction dedonnées. Ils présentent un inconvénientmajeur : si des données sontmodifiées dans letableauinitial,cesfiltresnelesprendrontpasencomptesil’extractionadéjàétéfaite.Cefiltres’assimileàuneimagedesdonnéesàuninstantt.

OutilspratiquesOptionsdecollageCommedans tous les logicielsde lasuiteOffice,ondisposed’unpresse-papiersdont lafonction est de garder enmémoire les informations copiées (par les boutonsCouper etCopier).Exceldisposeluiaussidel’accèsaupresse-papiersetdenombreusesoptionsdecollages.

Raccourcisclavieràconnaîtreabsolument!

Couper:Ctrl+XCopier:Ctrl+CColler:Ctrl+V

LegroupePRESSE-PAPIERSsesituedans l’ONGLETACCUEIL.

Icône Actiondecollage

Couper:ajoute lasélectionaupresse-papiersdansl’attente du collage. Les données sourcesdisparaitrontaumomentducollage.

Copier: ajoute la sélection au presse-papiers dansl’attente du collage. Les données sourcesdemeurent.

Coller la mise en forme : seuls les éléments demiseenformesontcollés(paslesdonnées).

Coller

Coller : on obtient une copieconforme (police, couleurs, taille, format decellules…,sauftailledelacellule)

Formules : collage des formules, sans la mise enforme.

Miseenformedesformulesetnombres:collagedes formules et des formats des nombres, sansélémentdemiseenforme.

Conserverlamiseenformesource:pasderéelledifférenceaveclebouton“Coller”

Aucune bordure : collage de tout, sauf desbordures.

Conserverles largeursdecolonnessources:nonseulement il y a collage des éléments de mise enforme, mais également collage des largeurs decolonnes.

Transposer:collageavecinversiondescolonnesetdeslignes.

Collerdesvaleurs

Valeurs:collagedesvaleurssansAUCUNélémentdemise en forme niAUCUNE formule (seul lerésultatdesformulesestcollé).

Mise en forme des valeurs et nombres : collagedes valeurs, seuls les formats de nombres sontconservés.

Miseenformedesvaleursetdelasource:collagedes valeurs, les formats sont conservés (pas lesautresélémentsdemiseen formecomme lesMEFconditionnellesparex).

Autresoptionsdecollage

Coller avec liaison : collage des références descellules copiées. Si on modifie le contenu d’unecelluleoriginale,lecontenudelacellulerésultantdesacopieserachangéenconséquence.

Lesautresoptionsneprésententquetrèspeud’intérêtetsontutilesdansdescasbienprécis

Fenêtre«Collagespécial»

Pour afficher la fenêtre “Collage spécial”, choisissez l’option COLLAGE SPÉCIAL dubouton dugroupePRESSE-PAPIERS,oubienfaitesunCLICDROITSURLA1ÈRE

CELLULEDELAPLAGEDEDESTINATION.

Cette fenêtre reprend pourpartie lesélémentsvusà lapageprécédente.

Rubrique«Coller»

Concernant la 1ère partie, leslibelléssonttoutàfaitexplicitesetne nécessitent pas davantage decommentaires.

Rubrique«Opération»

Il est possible d’effectuer desopérations entre les données à

coller et les données contenues dans les cellules de destination : addition, soustraction,multiplicationetdivision.

Opération Actiondecollage

Aucune Collageordinaire,sansopération!

Soustraction Ladonnéedechaquecellulecolléeestsoustraitedeladonnéedelacellulededestination:donnéedestination–donnéecollée.

Multiplication

Ladonnéedelacellulededestinationestmultipliéeparladonnéedelacellulecollée:donnéedestination*donnéecollée.

Fonctionutilequandonveutinverserlesensd’unnombre

(ex:touteslesvaleursdelacolonnecréditsontennégatif.Unedessolutions est de copier une cellule qui contient -1 et de faire uncollage spécial/Multiplication sur la colonne. Chaque cellule seramultipliéepar-1etdeviendradoncpositive!)

Blancsnoncompris

Pardéfaut,lescellulescollées,mêmevides,«écrasent»lesdonnéesdescellulesdedestination.Quandcettecaseestactivée,lescellulesàcollerqui sontvidesn’effacentpas lecontenudescellulesdedestination.

Cetableau

Mois CA Marge

2

Transposé

Janvier 825,54 565,11

Février9

998,771

999,75

Mars 5862,111

172,42

TOTAL18

686,413

737,28

devient

Mois Janvier Février Mars TOTAL

CA 2825,54

9998,77

5862,11

18686,41

Marge 565,111

999,751

172,42 3737,28

aprèsuncollagetransposé.

RechercherONGLETACCUEIL / GROUPE EDITION / RECHERCHER ET SÉLECTIONNER / RECHERCHER

[ouCtrl+F]

Cetoutilpermetderechercherdescaractèresdanslecontenudescelluleset/oudanslesformules.

Exemple:onveutrecherchertouslesmotsdanslesquelsonretrouve“Ab”.

-TapezAbdanslacase“Rechercher:“

- [facultatif] définissez éventuellement unemise en forme pourmettre enévidencelesrésultatsavecleboutonFormat…

- “Dans:”permetdechoisiroùvousvoulez rechercher (feuilleouclasseurcomplet)

-“Sens:”permetdechoisirlesensdebalayage(parligneouparcolonne).Ceparamètre est à ajuster selon la construction du tableau. Les résultats seront lesmêmes.C’estlarapiditéderecherchequidiffèrera.

-“Regarderdans:”permetdechoisiroùExcelvafairelarecherche(formules,valeurs,commentaires).Dansl’exemple,onchoisira“Valeur”.Onnechoisirapas“Formules”carsiunefonction (SOMME,MAX,MIN…) inclutdans sesparamètresuneplageA3:AB85parexemple,Excelmel’indiqueracommerésultat.

- “Respecter la casse :” permet de choisir de respecter ou non lesMAJUSCULES/minuscules.Dansl’exemple,lacasseauneimportancecarsionnecochepascettecase,Excelindiqueralesrésultatssuivants:ab;Ab;aB;AB.

-“Totalitéducontenudelacellule”permetdelimiterlarechercherauxseulscaractèressaisisdanslechampderecherche.

Cliquez sur Suivant ; Excel déplacera le curseur sur chaque occurrence à chaque foisqu’oncliquerasurSuivant.SivouscliquezsurRecherchertoutExcelétabliraunelistedesoccurrencestrouvées.

Opérateursderecherche

Ex:ondisposed’unjournald’achats.Onveuts’assurerqu’iln’yapasdecomptes706…danscejournal.

Pour éviter qu’Excel nous renvoie dans une cellule contenant par ex “607060”, on vaindiquer,grâceàdespointsd’interrogation,lenombredecaractèrestotalàrechercher:706???(soitunechaînecontenant6caractèrescommençantpar706).Danscecas,nousauronsbienuniquementlescomptesvoulus.

Unautrecaractèregénériquepeutêtreutilisé:l’étoile(*).Danscecas,Excelsauraqu’ilpeut la remplacer par tout caractère (y compris par “rien”). Ex : soit une suite denombres:12356/3125/125.

Siondemandeunerechercheavec12*5*,onobtiendra12356/3125/125.

Siondemandeunerechercheavec12?3?,onobtiendrauniquement12356.

Sionneveutrechercherquedansuneplagedonnée,ilsuffitdelasélectionnerpréalablementpuiscliquersurRechercher(dansleruban)ou

utiliserleraccourciclavierCtrl+F.

RemplacerONGLETACCUEIL/GROUPEEDITION/RECHERCHERETSÉLECTIONNER/REMPLACER[ouCtrl+H]

Concernant les options, elles sont parfaitement identiques à celles de la fonctionRechercher.

La seuledifférence est que l’onpeut voir apparaître une zone “Remplacerpar :” danslaquelleonvavenirinscrirelescaractèresderemplacement.

Sionnemetriendans“Remplacerpar”,Excelremplaceralestermesdelarechercheparunvide!

Ilestdoncaussiprimordialdebienchoisirlesoptions(“Regarderdans”parexemple).

LeboutonRemplacerpermetderemplacerlesoccurrencestrouvéesuneparune.

Le bouton Remplacer tout permet de remplacer, en une seule opération, toutes lesoccurrencestrouvées.

LeboutonRecherchertoutlistelesoccurrencestrouvéessansfaireaucunremplacement.

LeboutonSuivant déplace le curseur sur chaque nouvelle occurrence trouvée à chaquepressiondelasouris.

Onpeuttoutaussibienrechercherdescaractèresspéciauxdutype#~{espaceretouràlaligne.

Par contre, si certainsdeces caractères sontbien présents sur le clavier,d’autresne lesont pas. La toucheEntrée (pour le retour à la ligne) ou la tabulation par exemple ontd’autres usages. Le plus simple est donc de rentrer dans la cellule et de copier le

caractèreàremplacerpuislecollerdanslazone“Rechercher”.

Ilestcourantqueriennes’affichedanslazone.Normal!Unretouràlaligne,toutcommeunetabulationsontdescaractèresinvisibles!!

Maîtriserl’impressiond’untableauL’impressiond’unefeuilledetravailnécessitequelquescontrôles.Eneffet, iln’estpasrarequesil’onometdefaireunaperçuavantimpression,letableaus’éditesurplusieurspagesparex.Ilexistebienévidemmentdessolutionspourévitercesdésagréments:

Différentesméthodesd’impressionAperçuavantimpression

Depuislaversion2010duPackOffice,lebouton«Aperçuavantimpression»n’estplusprésentpardéfautdansl’espacedetravail(maisilestpossibledelerajouter).Désormais,il suffit de cliquer sur FICHIER puis IMPRIMER pour une génération automatique del’aperçu.

NombredepagesGestiondesmarges

Grâce à cet aperçu, on peut voir le nombre de pages et éventuellement les problèmesd’impression que l’on pourrait rencontrer (ex : la dernière colonne du tableau nes’imprimepassurlamêmefeuillequelereste…).

Ce panneau permet aussi de choisir l’imprimante sur laquelle on veut imprimer, lasélectiondepages(delapagexày),éventuellementlesmarges,lamiseàl’échelle…

PourIMPRIMER,oncliquedirectementsurleBOUTONIMPRIMER.

Pour SORTIR DE L’APERÇU (si on ne souhaite pas imprimer), il suffit de cliquer surACCUEIL.

Miseàl’Échelle

Uneautrefonctionpermetunréglageautomatique(OngletMISEENPAGE):

Si on souhaite avoir le tableau sur 1 page en largeur, il suffit de changer la valeur«Largeur»à«1page».Parcontre,onpeutlaisserlahauteurenautomatique,cequisignifiequ’Excel gèrera lui-même le nombredepages à imprimer en fonction de lalongueurdutableau.

Échelle : cette option est grisée lorsque l’on ne laisse pas tout en « Automatique ».Néanmoins, on peut voir ici que le document, pour qu’il tienne sur 1 page en largeur,devra être réduit à 50 %. Pour jouer sur cette valeur, il est conseillé de réduire aumaximumlalargeurdescolonnesàleurcontenu[voirfiche“Ajustementdelatailledescolonnesetdeslignes”.

Zoned’impression

TRÈSPRATIQUE, elle s’utiliseconjointement avec la fonctiondeMise à l’échelle ;elle permet de déterminer immédiatement la plage de cellules à imprimer. Pourl’utiliser,ilsuffitdesélectionnerlazoneàimprimerpuisOngletMISEENPAGE/GroupeMISEENPAGE/ZONEIMPRpuisDÉFINIR.

ModePage

Cemodepermetd’avoirsafeuilleExcelcommesil’onétaitenaperçuavantimpression.LeprincipeestlemêmequedansWordoùl’ontravaillesurledocumentcommeilseraàsa sortie de l’imprimante. Avec ce mode, on peut jouer sur marges, sur la largeur decolonnesdefaçonplusvisuelle.

Ce mode est à privilégier si le document sur lequel vous travaillez a pourvocationd’êtreimprimé.

Onremarquel’apparitiond’unerèglemaisaussidelamatérialisation«physique»desfeuilles.

Page1Page2

Aperçudessautsdepage

Cemodepermetdevoir et éventuellementdedéplacer par glisser-déplacer les sauts depage.

Les sauts de page sont matérialisés par les traits bleus (pointillés etcontinus).Excel

nousindiquelenumérodelapage(onvoitparexemplequecettefeuillequesionl’imprimaitmaintenant,nousaurionsaumoins23pages!!!)

Leszonesgriséesnesontpasimprimées.

En-têtesetpiedsdepageUnen-têteouunpieddepagepeuventparexemplecomprendreunnom,uneadresse,ladate,lenumérodepage,égalementunlogo,uneimage,unephoto.Pardéfaut,lemêmeen-têteetlemêmepieddepageapparaissentsurtouteslespagesdelafeuille.

Rapidement…

Enmode“Page”

Il suffitdecliquersur une

des3sectionsde l’en-têtepourquelabarred’outilss’ouvreetpourcommenceràsaisirsonen-têtepersonnalisé.

Plusendétail…

Pourafficherl’ONGLETCRÉATIONdes«Outilsdesen-têtesetpiedsdepage»,activezlebouton«EN-TÊTEETPIEDDEPAGE»duGROUPETEXTE, sous l’ONGLET INSERTION. OnutiliseraicilescommandesdecetongletCréation.Lafeuilles’afficheenmode«Miseenpage».

En-têtesetpiedsdepageprédéfinis

Excelproposedesen-têtesetdespiedsdepageprédéfinis.

LesdeuxpremiersboutonsEn-têteetPieddepagede l’ongletCRÉATION, affichent unelisted’en-têtesoudepiedsdepageprédéfinis.

En-têtesetpiedsdepagepersonnalisés

Vouspouvezégalementcréerunen-têteouunpieddepagepersonnalisé.En-têteetPieddepagecomportentchacuntroiszonesdesaisie.

VouspouvezinsérerdesélémentsàpartirdescommandesdugroupeELÉMENTSEN-TÊTEETPIEDDEPAGE.

L’insertiond’unélémentsetraduitparl’affichaged’uncodedecommandedelaforme:&[élément].

Exemple:sionsaisit:Le(espace) (boutonDateactuelle) (espace)à(espace) (boutonHeureactuelle),lecodeest:Le&[Date]à&[Heure].

L’en-têtedechaquepagepourraêtre:Le27/08/2016à19:48.

Pourmodifierlamiseenformedestextes,utilisezlescommandesdugroupePOLICEdel’ONGLETACCUEIL.

Validerouquitterl’éditiondel’en-tête/pieddepage

Pourvaliderouquitterl’en-têteoulepieddepage,ilsuffitdecliquersurlafeuilledecalcul

FormulesetfonctionsÀquoiçasertetcommentçamarche?L’objectifd’Excelestavanttoutderéaliserdescalculsautomatisés.Pourcefaire,ilestobligatoired’utiliserdesformulesdecalcul.

POURUTILISERUNEFORMULEDECALCUL,onseplacedansunecellulepuisoncommencepartaperunsigne«=»(égal).Excelvaalorsreconnaitrequ’unefonctionseradanscettecelluleetnondutexte.

Après avoir entré la formule, on tape sur la TOUCHE ENTRÉE pour sortir du modeModification.

En cas d’erreur, ou pour modifier la formule, il suffit de DOUBLE-CLIQUER DANS LACELLULEpourrentrerenmodeModification.

InséreruneformulesimplementExcelinclutdanssonruban,unonglet«Formules».Ilpermetd’insérersimplementuneformuleseloncequel’onsouhaitetraitercommedonnées(texte,date/heures,recherches,maths,financier…).

Si vous nesavez pasdansquellecatégoriechercher,vous

pouvez utiliser l’outil d’assistance en cliquant sur « INSÉRER UNE FONCTION », dansl’ongletFORMULES:

1:champderechercheetsélecteur

2:résultatdelarecherche

3:descriptifdelafonctionavecsasyntaxe

4:aidecomplémentaired’OfficeOnline

RecopiesetsériesVousavezparfoisbesoinde recopierune formulepourqu’elle s’appliqueaux lignesoucolonnessuivantes.

Vous pouvez aussi avoir à créer des séries avec un pas constant ou des séries de typeschronologiques(lundi,mardi…),alphanumériques(401frn1,401frn2,401frn3…).

Recopieverticaleouhorizontale

-Sélectionnezlapremièrecellule(oulesdeuxpremièrescellulessil’onveutcréerunesérie).

- Cliquez-glissez[1] sur la poignée (=petit carré noir en bas à droite de soncontour).Pendantlecliqué-glissé,lepointeurprendlaformed’unecroixnoire .

Recopiedanslecadred’unesérieRecopied’unélément

(pasdesérie)

-Laformuledelapremièrecelluleestrecopiée,onditaussi«étendue»,jusqu’àladernièrecelluleducliqué-glissé.

Laméthodederecopiefonctionnetoutaussibienverticalement(verslehaut,verslebas)qu’horizontalement(versladroite,verslagauche).

Explicationsynthétiquedesréférencesabsolues,relativesetmixtesUtilisationd’un%fixedansunecelluledéterminée(B5).

LaréférenceàB5doitêtresaisie manuellement pourque le calcul puisse sefaire.

La recopie sans erreur deC2versC4est impossible!

OnfaitréférenceàB5quel’onfigegrâceaux$(toucheF4)

Référencemixte:onnefigequelacolonneouquelaligneavecun$(dollar–F4).

Utile lorsqu’on veut utiliser larecopieautomatique.

Dans l’exemple, seule la formuledeC3aétésaisie.Leresteestle

résultatdelarecopiedeC3.

ExplicationdeD4:=D$2*$B4:

D$2:onafigélaligne2quicontientlemontantduCAHTquel’onveututiliserdanstouslescalculsmaisonn’apasfigélacolonneDpuisqu’enrecopiantversladroite,ExceldoitprendrelemontantHTdelacolonnesuivante(colE).

$B4: ona figé la colonneBqui contient le tauxdeTVAàutiliserdanschaquecolonne.Par contre, comme le tauxchange à chaque ligne (7%, 5,5%), on ne fige pas le 4 pour qu’en recopiant vers le bas, Excel prenne bien la lignesuivante.

OpérateursOpérateursdecalcul

Pourélaboreruneformuledecalcul,onsesertd’opérateurs.

Lesopérateursdecalculsont,parordredepriorité:

Priorité Opérateur Symboleclavier

1 Exposant ^ [Alt Gr + touche 9 dupavélettres]

2Multiplication *

Division /

3Addition +

Soustraction -

Exemple: la saisie=3^2*4+1 affiche comme résultat 37 (est calculé d’abord3^2, puis9*4,puis36+1).

On peut utiliser des parenthèses pour préciser la priorité des calculs. Le nombre deparenthèsesouvrantesdoitêtreégalaunombredeparenthèsesfermantes.

Laformuledel’exempleprécédentpeutégalementêtreécrite=((3^2)*4)+1.

OpérateursdecomparaisonOpérateur Comparaison

= Egal

> Supérieurà

< Inférieurà

>= Supérieur ou égalà

<= Inférieurouégalà

<> Différentde

Opérateursderéférences

Opérateur Fonction

: Permet de définir une plagedecellules.

; Permet d’unir plusieursréférences (plusieurscolonnes,parexemple).

Opérateuralphanumérique

L’esperluette&est l’opérateurpermettantdeconcaténer (c’est-à-dire lier) des chaînesdecaractères.

Exemple:OnmetlenuméroducompteenA1etl’intituléducompteenB1.

EnC1,onsaisitlaformulesuivante:=A1&”estlen°decomptepour“&B1.

LacelluleC1affiche:101000estlen°decomptepourCapital.

1. Nepasoublierlesigneégal,ils’agitd’uneformule!2. Desguillemetssuiventl’esperluetteetencadrentletexte!Attention:ne

pasoublierderajouterunespaceavantetaprèsletextesinononobtiendra:101000estlen°decomptepourCapital

Fonctionsdebase=A1+A2+A3+A10:calculelasommedescellulesA1,A2,A3etA10.

=SOMME(plage):calculelasommedesnombresd’uneplagedecellules.

plage:A1:A3;A10:faitlasommedeA1àA3(aveclesdeuxpoints)puisA10

=MOYENNE(plage):calculelamoyennedesnombresd’uneplagedecellules.

=MAX(plage):calculelemaximumdesnombresd’uneplagedecellules.

=MIN(plage):calculeleminimumdesnombresd’uneplagedecellules.

LasyntaxeestlamêmequepourlaformuleSOMME.

Notezqu’ilestconseilléd’entrerlaformuleenminuscule.SiExcelreconnaitlafonction,illatransformeraautomatiquementenMAJUSCULE.

Lorsqu’oncommenceàécrirelaformuledanslacellule,Excelproposeautomatiquementdesformulesquipeuventcorrespondre.

Fonctionssurdescellulescontenantdutexte=NBCAR(cellule):renvoielenombredecaractèrescontenusdansunechaînedetexte

Ex:=NBCAR(C1)oùC1contient :«ABCDE–FGHIJK» renverra14 (5 lettrespourABCDE+6lettrespourFGHIJK+1tiret+2espacesautourdutiret)

=NB(plage):renvoielenombredecellulescontenantdesnombres.

=NB.VIDE(plage):renvoielenombredecellulesvidesdansuneplagespécifique

Ex:=NB.VIDE(A1:Z10)

=NBVAL(plage):renvoielenombredecellulesnonvidesdansuneplacespécifique

C’estlafonctioninversedenb.vide.Ellefonctionnedefaçonanalogue.

=SUPPRESPACE(cellule):supprimelesespacessuperflusd’unechaînedecaractères(lafonctionn’effacepaslesespacessimplesentrelesmots!).

Ex:lacelluleB25contient«ABCDE-FGHIJK».Onsouhaiteavoir«ABCDE–FGHIJK».

=SUPPRESPACE(B25)Résultat:ABCDE-FGHIJK

=MAJUSCULE(texte):transformeunechaînedecaractèresenmajuscules

=MINUSCULE(texte):transformeunechaînedecaractèresenminuscules

=NOMPROPRE(texte) : transforme la 1ère lettre d’une chaîne de caractères enmajusculeetlesautresenminuscules.

Fonctionsd’extraction=GAUCHE(cellule;nombredecaractères):renvoiencaractèresàpartirdudébutdumot(oudelaphraseouduchampnumériqueoudecequel’onpeutavoirdanslacellule)

=DROITE(cellule ;nombredecaractères) : renvoiencaractèresàpartirde lafindumot(oudelaphraseouduchampnumériqueoudecequel’onpeutavoirdanslacellule).

=STXT(cellule ;position de départ ;nombre de caractères) : renvoie n caractères àpartird’unepositiondéterminée(ex:milieudumot).

Ex:contenudelacelluleB2:

Onneveutgarderquelenuméroducompte:onseplacedansB3(parex)etonsaisit

=GAUCHE(B2;6)Résultat:706000

B2correspondàlacellule«source»

6correspondaunombredecaractèresàextrairedepuislagauche

Ex:contenudelacelluleC5:

Problème:cettedaten’estpasinterprétéeparExcelentantquetelle,carleséparateurutilisén’estpasunslash(/)maisunpipeline(|).

Solution:extrairelejour,lemoisetl’annéeavecunefonctionSTXT:

EnC6onsaisit=STXT(C5;0;2)renvoie14

EnC7onsaisit=STXT(C5;4;2)renvoie07

EnC8onsaisit=STXT(C5;7;4)renvoie2012

Pourconcaténerletout,onutiliselafonction=DATE(année;mois;jour)

EnC9onsaisit=DATE(C8;C7;C6)renvoie14/07/2012

NB:pourextrairelejouretl’année,onauraittrèsbienpuutiliserlesfonctionsDROITEetGAUCHE.

=DROITE(C5;4)renvoie2012=GAUCHE(C5;2)renvoie14

=CONCATENER(cellule1;cellule2;celluleX) : permet d’assembler des chiffres, deslettresoun’importequelscaractèrescontenusdansdifférentescellulesenuneseule.

Danscetexemple,onassemblelen°ducompteavecsonlibellé:=CONCATENER(A5;”.

“;B5)

A5renvoie7011.

“.“permetd’ajouterunpointaprèslenuméropuisunespacepourséparerlepointdulibellé.

B5renvoie“Produitsfinis…“

Fonctionsconditionnelles=SI(test logique ; valeur_si_vrai ; valeur_si_faux) : renvoie une valeur déterminée àl’avanceenfonctiond’untestlogique.

Exn°1:=SI(1+1=2;«résultatcohérent»;«résultatincohérent»)

Exn°2:=SI(GAUCHE(A1;1)>=9;“N°decompte:Non”;“N°decompte:Oui”)

TestlogiqueRéponsesiVRAIRéponsesiFAUX

Enfrançais,onpourraitlire:sile1ercaractèredelacelluleA1estsupérieurouégalà9alorsonaffichera“N°decompte:Non”sinononaffichera“N°decompte:Oui”.

Onvoiticitoutl’intérêtd’Excel:l’automatisationdestâches!

=NB.SI(plage;critère):déterminelenombredecellulesnonvidesrépondantaucritèredéterminé.

Danscetexemple,laplageestA1:B9etonrecherchelenombred’occurrenceségalesà89.

=NB.SI(A1:B9;89)

On pourrait imaginer que la valeur 89 change. On aurait alorsremplacé 89 par l’adresse de la cellule qui contient la valeur ducritère.

Sil’onveutcompterlenombred’occurrencessupérieuresà25:

=NB.SI(A1:B9;“>25”)

Autresopérateurs:

“<25”:inférieurà25

“<>85”:différentde85

=SOMME.SI(plageàcontrôler;critère;sommeplage):permetdecalculerlasommedesvaleursd’uneplagequirépondaucritèrespécifié.

LafonctionestsemblableàNB.SIsaufqu’ellepermetdefairelasommedesrésultatsetnonlecompte.

Ex:aveclesdonnéesdeNB.SI.OnconservelaplageA1:B9commeplagederéférence.

Onsouhaitefairelasommedesvaleurscorrespondantà“a”(soit89+56+25):

=SOMME.SI(A1:A9;“a”;B1:B9)Résultat:170

A1:A9correspondàlaplagedanslaquelleonvaappliquerlecritère

acritèredesélection,àmettreentreguillemetscarc’estuncaractèrealphanumérique!!

B1:B9correspondàlaplagedanslaquelleonvafairelasommed’aprèslecritère.

OnpeututiliserlesmêmescritèresquepourNB.SI,àsavoir:

-“>25”:supérieurà25

-“<25”:inférieurà25

-“<>85”:différentde85

=SI(…imbriquée…)

Rappel:lafonction=SI(testlogique;valeur_si_vrai;valeur_si_faux)permetdefaireuntestsurunecelluleetendéduireunevaleurouunmessageàafficherenfonctiondurésultatdutestlogique.

Parfois,onabesoind’augmenterlenombredecritèressurletestainsiquelenombrederésultatsàafficher.C’estpourcetteraisonquel’onvautiliserunefonctionSIimbriquée.

LasyntaxeestlamêmequepourlafonctionSIsaufqu’onvaremplacer“valeur_si_vrai”et/ouvaleur_si_fauxparunenouvellefonctionSI.

Exemplepratique:

Imaginonsqueleplancomptabletransmisparleclientestlesuivant:

L’entreprise ne saisit sa comptabilité que sur descomptes à 4 chiffres. Les autres comptes ne sontque des regroupements. On veut donc lessupprimer.

Pourl’exemple,onnes’occuperaquedescomptesdeproduits.

FonctionSI“classique”:

=SI(GAUCHE(A1;1)=7;“Comptedeproduits”;“Erreur”)

Sile1ercaractèredunuméroestégalà7,Excelrenverra“Comptedeproduits”.Danslecascontraire,Excelrenverra“Erreur”.

Seulement,seulslescomptesà4chiffresenregistrentdesopérations.Ilvadoncfalloirrajouteruntestpourcontrôlerquelenombredecaractèresdunuméroestbiende4chiffres.

=SI(GAUCHE(A1;1)=“7”;SI(NBCAR(A1)=4;“OK”;“Moinsde4chiffres!”);“Cen’estpasuncompte7”).

=SI(GAUCHE(A1;1)=“7”;

Sivrai:SI(NBCAR(A1)=4

Sivrai:“OK”;

Sifaux:“Ilya+/-4chiffresmaispas4chiffresexactement!”)

Sifaux:“Cen’estpasuncompte7”

Onpourraitaussiimaginerremplacerle“SiFaux”paruneautrefonctionSI:

=SI(GAUCHE(A1;1)=“7”;

Sivrai:SI(NBCAR(A1)=4

Sivrai:“OK”;

Sifaux:”Ilya+/-4chiffresmaispas4chiffresexactement!”)

Sifaux:SI(NBCAR(A1)>4

Sivrai:“Ilyadescomptesdeplusde5chiffres”;

Sifaux:SI(NBCAR(A1)<4

Sivrai:“Ilyadescomptesdemoinsde5chiffres”;

Sifaux:””)

Fonctionsderecherche=RECHERCHEV(valeur_recherchée;table_matrice;n°_index_col;valeur_proche)

-valeur_recherchée:valeuràchercherdanslapremièrecolonnedutableauoudelaplage

-table_matrice:plagedecellulesquicontientlesdonnées

- N°indexcol : numérode la colonnede l’argument table_matrice dont lavaleurcorrespondantedoitêtrerenvoyée.

-valeur_proche:prendlavaleurVRAIouFAUX.

SiVRAI:silafonctionnetrouvepasderésultat,elleafficheralerésultatleplusprocheSiFAUX:silafonctionnetrouvepasderésultat,ellerenverrauneerreur

Exempleconcret:

Extraitdustockd’uneentreprise(lestockcompletcompteplusde1000lignes).

Réf. Désignation Quantité Prixunitaire Total Secteur

694839 LOGBOSNFD53 43,63 7,30 318,50 MAISON

570281 GRVAFVSSF53 24,02 13,30 319,47 ELECTROMENAGER

455704 BOSSARSFL53 29,08 11,00 319,88 INFORMATIQUE

335822 GVARKNSDO53 28,87 11,10 320,46 AUTO

212028 GVAZOVSFL53 17,06 18,80 320,73 ALIMENTAIRE

…

Un commissaire aux comptes souhaite faire un contrôle d’inventaire. Il a prélevé, auhasard, plusieurs références. Il souhaite savoir rapidement dans quel secteur il doitretrouverlesarticles.

On a préalablement nommé la plage qui contient l’ensemble des données :“plage_inventaire”

Réfprélevées:570281,335822,455704,etd’autres…

A B C

1 Référencesrecherchées

Secteuroùlesretrouver

Formulesquidonnent

lenomdusecteur

2 570281 ELECTROMENAGER =RECHERCHEV(A2;plage_inventaire;6;0)

3 335822 AUTO =RECHERCHEV(A3;plage_inventaire;6;0)

4 455704 INFORMATIQUE =RECHERCHEV(A4;plage_inventaire;6;0)

Explicationdelaformule=RECHERCHEV(A3;plage_inventaire;6;0)

=RECHERCHEV( Indique qu’Excel va devoir effectuer une recherche avec desdonnéesrangéesverticalement.

A3 Valeur que doit rechercher Excel dans la plage de données(inventaire)

plage_inventaire Plagededonnéesdanslaquellevarechercherlavaleurrenvoyéepar A3 (dans l’exemple, Excel va rechercher 335822 dansplage_inventaire.

plage_inventaire aurait tout aussi bien pu être remplacée parA2:F1000.

6 Correspond au numéro de la colonne dans la plage de données(plage_inventaire) dans laquelle Excel va trouver le résultat àafficher(ici,lenomdusecteur)

0 Correspond à la valeur FAUX pour dire à Excel que s’il netrouvepaslavaleurrecherchée,ilnefautpasqu’ilafficheautrechose(valeurimmédiatementinférieureousupérieure).

) ParenthèseferméepourclorelafonctionRECHERCHEV.

A

1 Réf.

2 694839

3 570281

4 455704

=RECHERCHEH(valeur_recherchée;table_matrice;n°_index_col;valeur_proche)

FonctionquieffectueuntravailsimilaireàRECHERCHEVmaisdefaçonhorizontale.

Réf. 694839 570281 455704 335822 212028

Désignation LOGBOSNFD53 GRVAFVSSF53 BOSSARSFL53 GVARKNSDO53 GVAZOVSFL53

Quantité 43,63 24,02 29,08 28,87 17,06

Prixunitaire 7,30 13,30 11,00 11,10 18,80

Total 318,50 319,47 319,88 320,46 320,73

Secteur MAISON ELECTROMENAGER INFORMATIQUE AUTO ALIMENTAIRE

B1:F6

DonnéesdelafonctionRECHERCHEH B9

Valeurrecherchée 570281

Plage B1:F6 Fonction: =RECHERCHEH(B9;B1:F6;6;0)

Lignerésultat 6 Résultat: ELECTROMENAGER

Valeurproche? 0

=EQUIV(valeur_recherchée;tableau_matrice;[type])

Renvoielapositionrelativedelavaleurrecherchéedanslamatrice

[type] est une option (d’où les crochets) qui permet de déterminer laréaction d’Excel si la valeur recherchée n’est pas trouvée. [Type] peutprendrelavaleur0,-1ou1avec:

0pourneprendrequelavaleurpourlaquelleilyaunecorrespondanceexacte

5 335822

6 212028

1pourprendrelavaleurimmédiatementinférieureàlavaleurrecherchée

-1pourprendrelavaleurimmédiatementsupérieureàvaleurrecherchée

Ex : on recherche sur quelle ligne on pourra retrouver les informations relatives à laréférence335822.

=EQUIV(335822;A2:A6;0)Résultatobtenu:4

Lavaleur4correspondàlapositiondelavaleur335822danslaplagesélectionnée(etnonlen°deligne!!!)

=INDEX(matrice;no_lig;no_col)

Permetlarecherched’unevaleurdansuntableauenfonctiondesescoordonnées.

Ex : ici, la valeur renvoyée par la fonction INDEXprovientdelaformulesuivante:

=INDEX(F10:H16;G18;G19)

F10:H16correspondàlaplagedecellulesquicontientlesdonnées(nepasinclurelesen-têtes)

G18correspondaunumérodeligne(ici:4)

G19correspondaunumérodecolonne(ici:1)

SionfaitvarierG18etG19,lerésultatchangera,bienévidemment!

=INDIRECT(réf_texte;[a1])

LafonctionINDIRECTpermetdeconcevoirdesliaisonsentrelesongletsd’unclasseur.L’intérêtdelafonctionvarésiderdansl’utilisationdesnomsdesongletscommevariables.

Réf_texteReprésenteuneréférenceàunecellulequicontientuneréférencedetypeA1

[a1]Optionfacultativeutilepourdesusagesspécifiques.

Exempleconcret:onutilisedesmatricesidentiquespourfairedesdéclarationsdeTVApourdifférentspayseuropéens(dansnotreexemple,laFrance,l’Allemagne,l’ItalieetleRoyaumeUni).On souhaite connaître les tauxdeTVA(tauxnormal et taux réduit)desdifférentspays.

On crée donc une feuille desynthèse.

Listedes5feuillesduclasseur:

FeuillenomméeINDIRECT:

Les différents taux de TVAsont obtenus grâce à uneformule qui utilise la fonctionINDIRECT.

Lorsqu’Excel indique #REF!,cela signifie qu’il y a uneerreur(ici,enl’occurrence,lesfeuilles Indir_PT et Indir_ESn’existentpas!)

20pourleRoyaumeUniestobtenugrâceà=INDIRECT(”’”&A5&”’!”&$E$9)

Explications : d’ordinaire, lorsqu’on fait référence à une cellule d’une autre feuille, laformuleutiliséeest la suivante :=‘Indir_RoyaumeUni’!B7 ou =Indir_FR!B7

Lenomde la feuille Indir_RoyaumeUniest encadrépar desapostrophes car le nomcomprendunespaceentreRoyaumeetUni.Pourdesnomssansespace(commeIndir_Fr),lesapostrophesnesontpasnécessaires,maisilestplusprudentdelesmettrequandmême!!

=INDIRECT( IntroductiondelafonctionINDIRECT =INDIRECT(

”‘“& L’apostrophe que l’on encadre par des guillemetsindiqueàExcelquec’estuncaractèredetypetexte.

L’esperluette indiqueàExcelquecequiva suivreestuneréférence.

=INDIRECT(’

A5 Renvoielenomdelafeuille =INDIRECT(‘Indir_RoyaumeUni

&”‘!” L’esperluette indique à Excel qu’il va devoirinterprétercequisuitcommedutexte.

Lesguillemetsencadrentletexte.

L’apostrophe et le point d’exclamationreprésententletexteàincluredanslafonction.

=INDIRECT(‘Indir_RoyaumeUni‘!

B7 Cellule dans laquelle on trouvera le taux de TVAdanslafeuilleIndir_RoyaumeUni.

=INDIRECT(‘Indir_Royaume

Uni’!B7

) Parenthèse fermée : les arguments de la fonctiononttousétépassés.Lafonctionestterminée. 20

Astuce:pouréviterlesproblèmesderéférences,ilestconseilléd’utiliserlemoinspossibled’espacesetdecaractèresaccentués.Onpréférerale

caractèreunderscore(tiretdu8)enremplacementdesespaces.

FonctionsfinancièresExcelpermetaussid’utiliserdesfonctionsfinancièrespourlecalculd’amortissements,demontantsàremboursersurunempruntdonné…

Parmilespluscourantes,voustrouverez:

-AMORDEGRCpourlecalculdesamortissementsdégressifs

- AMORLINpourlecalculdesamortissementslinéairesd’unbienpourunepériodedonnée.

UnelistedefonctionsconséquenteestàvotredispositiondansExcelpourvosbesoins.

Vouslesretrouverezdansl’ongletFORMULES/BoutonFINANCIER

Autresfonctions“utiles”=AUJOURDHUI():renvoieladatedujour.Cettedates’actualisetouslesjours.

Cettefonctionestutilepourcalculerl’intervalleentre2dates:

Ex:EnA1,onsaisit09/01/2011.

Pourcalculerladifférence,onentreenA2=AUJOURDHUI–A1

NepasoublierdemettrelacelluleA2enformatNombre,sinonlaréponseserasouslaformed’unedate.

=DATE(année;mois;jour):renvoieunedateàpartirde3élémentsséparés(l’année,lemoisetlejour).

Cettefonctionestutile lorsqu’onextraitunedatequiseraitsouslaforme14|07|2012(lepipelinen’étantpasreconnucommeunséparateurdedate,ilfautisoler14,07et2012).Ensuite,pourreconstituerladate,onutilise=DATE(2012;07;14).

=JOUR(date)

=MOIS(date)

=ANNEE(date)

Renvoie, selon la formule utilisée, le jour, le mois ou l’annéed’unedatepasséeenparamètre.

Ex:enA1,onaladatesuivante:14/07/2012

=JOUR(A1)Résultat:14

=MOIS(A1)Résultat:7

=ANNEE(A1)Résultat:2012

=ALEA():renvoieunevaleuraléatoireinférieureà1.

Cettefonctionestutilepourchoisiraléatoirementdesréférencesàtesterparex.

Ex:Unclientnous transmetson inventairesurExcel. Ilyaplusde10000références.Poureffectueruntestaléatoire,onattribueunnombrealéatoireàchaqueréférence.Enfin,grâceàunefonctiondetri,onsélectionneles10plusgrandesvaleursaléatoires.

ATTENTION!ÀchaquerecalculdelafeuilleExcel,lesvaleursaléatoireschangent!

AuditdesformulesSous l’onglet FORMULES, le groupe AUDIT DE FORMULES contient les commandesspécifiquesàl’auditdeformules,àlavérificationetàlarecherched’erreurs,ainsiqu’auxréférencescirculaires.

Pouraffichertouteslesformulesdelafeuille,activezlebouton«Afficherlesformules»(Ctrl+guillemets(tapezdenouveauCtrl+guillemetspourlesmasquer)).

ExamendesliaisonsIl peut être utile de visualiser les liaisons existantes avec d’autres cellules.Ces liaisonssont matérialisées par des flèches. Pour supprimer l’affichage des flèches, activez lebouton«Supprimerlesflèches».

-Antécédents:d’oùvientlerésultatdetelleoutellecellule?

- Dépendances : sionsupprime la formulepermettantd’afficher50,celaauraunimpactsurlacellulecontenant100.

AfficherlesformulesPermetdetransformer(momentanément)lesrésultatsdesformulesenleurécrituresource.

ÉvaluationdesformulesCetoutilpermetdevoirlesrésultatsintermédiairesdonnésparunefonction.Celapeutserévéler très pratique lorsque la formule est complexe (Fonctions SI imbriquées parexemple)

Référencesabsolues/relatives/mixtesDansuneformule,onpeutdésignerunecellule

-soitparsaréférenceColonneLigne(exemple:=6*J43),

-soitparsonnom(ex:=6*quantité,silacelluleJ43aéténomméequantité).

Quandon saisit sa référence, le contourde la cellule changede couleur et s’entouredequatrepoignées,cequipermetdebienladistinguer.CelluleJ43

Insertiond’uneréférencedansuneformule

Ilexistedeuxméthodespoursaisirlaréférenced’unecelluledansuneformule:

- soitonlasaisitavecleclavier :ontapelalettredecolonnesuividun°deligne(ex:J43),

- soitonlasélectionneaveclasouris,méthodeparticulièrementintéressantequandils’agitdesélectionnerdesplagesdecellules.

RéférencerelativeCommesonnoml’indique,elleestrelative.Uneformulefaisantappelàunecelluleavecsaréférencerelative,mémoriselapositiondecettecelluleparrapportàcelledanslaquellelaformuleestsaisie.

Exemple:danslacelluleC5,sionsaisit=A4,ExcelmémorisequeA4désignelavaleurdelacellulesituée2colonnesavant(colonneCàcolonneA)etuneligneau-dessus(ligne5àligne4).

Ainsi,sioncopie lacelluleC5enD8, lavaleur indiquéeenD8seracellede lacellulesituée2colonnesavant(colonneB)etuneligneau-dessus(ligne7),cequicorrespondàlacelluleB7(etnonlavaleurdeC5).

Lesréférencesrelativesposentproblèmelorsqu’onsouhaitefaireréférenceàunecellulebienprécise(ex:tauxdeTVA)etqu’ondoitrecopierlaformuleverslebas.Pourpallierceproblème,onutilisedesréférencesabsolues.

RéférenceabsolueQuand la référence à une cellule est absolue, il convient de le préciser sous la forme :$colonne$ligne(procédémnémotechnique:laréférenceaudollarestabsolue…).Danscecas, même si la cellule est recopiée, la valeur reste celle de la cellule d’origine. Lesymbole$figelacolonneoulalignequilesuit.

Pour ajouter les dollars à une référence relative, sélectionnez laréférencedanslaformuleettapezsurlatoucheF4.

RéférencesmixtesOn peut choisir de ne figer que la ligne ou que la colonne. Dans ce cas, on place lesymbole $uniquement devant la lettre (pour ne figer que la colonne (ex : $B9)) ouuniquementdevantlenombre(ex:B$9(pournefigerquelaligne).Celapeuts’avérertrèsutile lorsqu’onsouhaitefairedesrecopieshorizontalesparexempleoudanscertainscastrèsprécis.

Làencore,onpeututiliserlatoucheF4:

1erappuisurF4:$B$9|2èmeappui:B$9|3èmeappui:$B9|4èmeappui:B9

Référenceàuneautrefeuille/àunautreclasseurCertains tableauxnécessitentde faire appel àd’autres feuillesdu classeurou àd’autresclasseurs.C’estlecaslorsqu’onutiliseunefeuillededonnéesdanslaquelleonvastockertoutes les variables (ex : pour un bulletin de paie, on va stocker les taux de chaquecotisation, lemontantduSMIChoraire, lapériode…dansunefeuilleséparée.Dansuneautre, on créera le bulletin qui fera appel aux informations placées dans la 1ère feuille.L’avantage est qu’à chaque changement de taux, il n’est pas nécessaire demodifier lesformulesmaisjustelafeuillesource!)

DansExcel,ilyaplusieursétapesàsuivre:

1. Ouvrirl’ensembledesclasseursquivontêtreutilisés1. Classeur A (Bulletin Pierre | Bulletin Paul | Bulletin Jacques| Données

globales)

Le“ClasseurA”compte4feuilles,dont3bulletinsdepaiequiutilisentdesdonnéesdela4èmefeuille,lafeuille“Donnéesglobales”.

2. ClasseurB(Récapsalaires|Récapcharges).

Le “Classeur B” compte 2 feuilles ; la 1ère (Récap salaires) récupère le net à payer dePierre,PauletJacquesetla2èmefeuille(Récapcharges)calculelemontantdescotisationsàpayeràchaqueorganisme.

2. Seplacerdanslacelluleoùvaapparaîtrelerésultatdelaréférence(ex:sionveut faire référence au SMIC horaire qui se trouve dans la feuille “Donnéesglobales”,onseplacedansundes3bulletins).

3. Tapez = pour introduire une nouvelle formule puis cliquez sur l’onglet de lafeuille“Donnéesglobales”etenfin,cliquezsurlacellulecontenantlavariableSMIChoraire.

4. TapezsurlatoucheEntréepourvalider.5. Excelvousreplacedanslafeuilled’origine.Lafeuillesurlaquellevousvoussituez

(ex:BulletinPierre)amaintenantunlienverslafeuille“DonnéesGlobales”.

SionmodifielavaleurduSMIChorairedanslafeuille“Donnéesglobales”,cettevaleurseramodifiéedanstouteslesfeuillesayantunlienvers“Donnéesglobales”.

Concernantle“ClasseurB“,ilfonctionnecommes’ilétaitunefeuilleduclasseurA.Laprocédureestdoncanalogue:

1. OuvrirlesclasseursAetB2. Seplacer sur le “ClasseurB”, dans la cellule où va apparaître le résultat de la

référenceau“ClasseurA”(ex:montantnetdePierre)3. Tapez=pourintroduireunenouvelleformulepuiscliquezsur“ClasseurA”puis

sur l’onglet de la feuille “Bulletin Pierre” et enfin, cliquez dans la cellule

contenantlemontantnet.4. TapezsurlatoucheEntréepourvalider.

Danslacellule,aulieudevoirapparaîtreuneréférencedutypeB85,onaura=‘Donnéesglobales’!B85quiselit{celluleB85delafeuille“Donnéesglobales”}.

Silacellulefaitréférenceàunecelluled’autreclasseur,onlira=’[ClasseurB.xlsx]Récapsalaires’!$E$22quiselit{celluleE22delafeuille“Récapsalaires”duclasseur“ClasseurB.xslx”}.

Remarque:ilfautêtredansunemêmeinstanced’Excelpourpouvoirréalisercetteopération.Celasignifiequ’unefoisle1erclasseurouvert,ilfautouvrirle2èmeavecFichier/Ouvrir.

Àlaprochaineouverture du

classeurB, il est possible que vous ayez cet avertissement. Il est conseillé d’Activer lecontenu.CelapermettraàExceldecontrôlerqu’iln’yapaseudemodificationdanslescellulesliées.

Attributiond’unnomLes noms rendent les formules et les fonctions bien plus simples à utiliser et àcomprendre.Vousattribuezunnomàunecelluleouuneplagedecellules,àuntableauouunefonctionetvousvousservezensuitedecenomàlaplacedelaréférencedecellule.Par exemple, la fonction =SOMME(StatsTrimestre1) est plus claire que=SOMME(C2:D25).

-Unnomdéfinidanslafeuilledecalculn’estreconnuquedanscettefeuilleetnepeutpasêtreemployédansuneautrefeuilleduclasseur.

-Chaquenomdoitêtreuniquedansunclasseur.

-Lesnomsutilisentdesréférencesabsolues.

- En nommant la plage de données, vous copiez la formule sans crainte,puisque la zone nommée reste toujours la même (moins de problèmes deréférencesrelatives/absolues/$).

Créerunnom

-Sélectionnezlacellule,laplagedecellulesoulafonction[iciuneplageallantdeA1àA28]etsaisissezunnom[ici“plan_comptable”]danslazoneNomsituéeàgauchedelabarredeformule.Cenomserad’étendueglobale(toutleclasseur).

-AppuyezsurlatoucheEntréepourlevalider.

Désormais,vouspourrezfaireappelàplan_comptabledansuneformuledecalculplutôtqued’utiliserA1:A28quiestbeaucoupmoinsexplicite!

Ex:=NBVAL(plage)permetdecalculer lenombredecellulesnonvidesdans laplage.Ici,onvadéterminerlenombredecomptesqu’onadansnotreextraitdeplancomptable.

OnsaisitdoncenA29:=NBVAL(plan_comptable)Résultat:28

Lorsqu’onsélectionneuneplagenommée,Excelremplaceautomatiquementlesréférencesrelativesauxcellulesparlenomdela

plage.

Gestionnairedenoms

Ilpermetdesupprimer,renommer,modifieruneplagenomméedéjàexistante.

Onyaccèdeparl’ONGLETFORMULE/GESTIONNAIREDENOMS

NB: lorsde la créationd’unezoned’impression,Excel créede façonautomatiqueuneplagenommée“Zone_d_impression”.

LessériesExcelinclutdebasedessériesautomatiques(parexemplelalistedesjoursdelasemaine,lesmois…)maislelogicielestaussicapabledecréerdessériespersonnalisées.

SuiteschronologiquesPourlesmettreenœuvre,ilsuffitdesaisirla1èreoccurrencedelasérie(ex:janvier)etd’utiliser larecopie pourvoir la suite se construire automatiquement (février,mars…).Onpeutaussisaisirunedatedutype14/07/2012etfaireunerecopieverslebaspourvoirlasuitedelasérie(15/07/2012,16/07/2012…).

Onpeutaussiconstruiredessuiteschronologiquesaveclesjoursouvrésuniquement.Celapeutêtrepratiquepourbâtiruncalendrierparexemple!Voirlasection“Suitegéométrique“.

Suitesnumériques(linéairesougéométriques)Suitelinéaire

Une suite linéaire est une suite de nombres, chacun (à partir du 2ème) étant obtenu enajoutant un nombre fixe à l’élément précédent. Ce nombre est appelé “pas” ou“incrément”.

DansExcel,onsaisitle1erchiffredelasuite(ex:1enB10)puisle2ème(ex:3enB11).OnsélectionneB10etB11puisonutiliselapoignéederecopieverslebas.Onvapouvoirconstaterqu’Excelvaconstruireunesuiteenutilisantunpasde2(1,3,5,7,9…).

Suitegéométrique

Unesuitegéométriqueestunesuitedenombres,chacun(àpartirdu2ème)étantobtenuenmultipliantparunnombrefixel’élémentprécédent.

DansExcel,onsaisitle1erchiffredelasuite(ex:1enB10) puis on sélectionne une plage de cellules(ex : B10:B15). On clique ensuite sur le boutonSÉRIES… qui se situe dans l’onglet ACCUEIL /GROUPEEDITION/REMPLISSAGE/SÉRIES…

Puisondéterminelepas(ex:2)aprèsavoirchoisiuntypeGéométrique.OnvalideparOK.

<=Résultat

Onvoitalorsqu’onaaccèsàplusieursoptions:

>Sérieenlignesouencolonnes(selonlesensdel’incrémentation)

> Types de série : Linéaire (le pas s’ajoute), Géométrique (le pas multiplie),Chronologique,…

>Valeurdupas:àdéterminerselonlasuitequel’onsouhaitecréer.

>Dernièrevaleur:danslecasoùonn’auraitpassélectionnédeplage,Excelauraitcréé

unesuitejusqu’àcettedernièrevaleurpréalablementdéterminée.

SériesalphanumériquesLes valeurs d’une série alphanumérique sont constituées d’un textefixeetd’unnombrequipeutêtreincrémenté(ex:N+1|N+2|N+3…).

Dans Excel, on saisit la 1ère valeur en B10 : N+1. On utilise lapoignéederecopiepourincrémenterlasuiteautomatiquement.

SériespersonnaliséesSivousutilisezquotidiennementdesséries toujours identiquesdansvos tableauxetquevousvousattelezàlesrecopieràchaquefois,Excelpeutvousfaciliterlatâcheenincluantvospropresséries!

1èreétape:saisissezvotresériedansExcelpuissélectionnezlaplagelacontenant:

2èmeétape:

Allerenbasdelafenêtrepuiscliquezsur:

3èmeétape:cliquezsurImporter.

Onpeutensuiteconstaterquelesdonnéesdelalisteontétéajoutées.

ValidezparOK.

Pour réutiliser cette liste personnalisée, il suffira de saisir la 1ère entrée (Classe 1 –Capitaux)puisd’utiliserlapoignéepourdévelopperlasérie.

Tableauxcroisésdynamiques(TCD)Untableaucroisédynamiquepermetdecombineretdecomparerdesdonnées,pourmieuxlesanalyser.

Croisé:toutedonnéedépenddesétiquettesdesaligneetdesacolonne.

Dynamique :un tableaucroisédynamiqueest évolutif, facilementmodifiable. Ilpermetd’examinerlesdonnéessousdesanglesdifférents.

Il peut être complété par un graphique croisé dynamique représentant les données dutableau.

Prérequis:

-Disposerd’unesourcededonnéesencolonnes

-Cesdonnéessourcesdoiventêtredemêmenatureauseind’unemêmecolonne

-Lescolonnesnedoiventcontenirnifiltrenisous-totaux

-La1èrelignedutableaudoitcontenirdesen-têtes

Quepeut-onfaireconcrètement?

- Àpartirdejournauxd’écritures,onpeutparexemplecréerunebalanceenquelquesclics,fairedesregroupementsetsous-totauxparclasseavecdesdegrésdeprécisionsdéterminés

- Àpartird’unelistedupersonnel,onpeutressortirdesstatistiques(moyenned’âge,H/F,répartitiondeCA…

-Àpartird’unegestiondestemps,onpeutrecouperlesdonnéespourextraireparexempleletempstotalpasséparunsalariéenCACetenEC…

- Lespossibilitéssontnombreusesetdépendentdesdonnéesinitialesdontondispose. C’est pour cette raison qu’il est toujours conseillé d’en recueillir unmaximumquitteànepaslesafficherultérieurement.

Créeruntableaucroisédynamique(TCD)Danscetexemple,nousallonscréerunebalanceàpartird’un journald’écritures.Par lamêmeoccasion,nousallonscontrôlerquelapartiedoubleestbienrespectée.

- Sélectionnez la plage de données à analyser puis cliquez sur INSERTION /TABLEAUCROISÉDYNAMIQUE

Il est parfois nécessaire de retraiter les données de base selon le résultatattendu (rajouter une colonne Classe avec le 1er chiffre du compte parexemple).

=GAUCHE(C2;1)

-Choisissez“Nouvellefeuille”pourl’emplacementdurapportdeTCD

-Unenouvellefeuilleestgénéréeetunbandeauapparaîtàdroitedel’écran

Ce bandeau reprend les différentes colonnes dutableau source, colonnes sur lesquelles on vapouvoireffectuerdescalculs,desregroupements,destris…

Concernant les 4 zones, elles ont chacune leurutilité:

- Filtre du rapport : permet de filtrer lesdonnéesdutableausurunouplusieurschamps.

-Étiquettesdecolonnes:regroupelesvaleurscommunes ensemble et affiche le résultat dechaquevaleurdansunecolonne.

- Étiquettesdelignes:affichelesrésultatsdela synthèse de chaque valeur sur une lignedifférente.

- Valeurs:affichelesrésultatsdesfonctionsdemandées (somme,nombre,moyenne…) selon

lesvaleursaffichéessurleslignesetlescolonnes.

Ex : pour construire une balance à partir de journaux, on a besoin, au minimum, descolonnesCompte,Débit,Crédit.

Onvadoncfaire

glisser“Compte”dansÉtiquettededonnées (caron veut regrouper les données par Compte) puis “Débit” et “Crédit” dans Valeurs(puisqu’onvaeffectuerdesopérationssurcesvaleurs[sommes]).Voicicequ’onvaobtenir:

Excelacomptélenombred’occurrencespar compte (il y a par ex 2 débits et 2crédits pour le compte 106800). Celan’apporterienpournotrebalance.Ilfautdonc demander à Excel de faire unesomme sur les Débit et Crédit. Pourcela,cliquezsurNombredeCréditpuisPARAMÈTRES DES CHAMPS DE

VALEURS…

Onpeut voir qu’Excel nous propose plusieurs types d’opérations.Dans notre cas, nouschoisirons“Somme”.

OnpeutaussichoisirunformatpourlacolonneencliquantsurFormatdunombre.

On obtient donc le résultat escompté : une balance des comptes qui nous permet decontrôlerqueDébit=Crédit!

Sionsouhaiteavoiruntotalparclasse,ilsuffitde faire glisser “Classe” vers la zoneÉtiquettedelignes.

ATTENTION,sonemplacementdanslazoneaune importance. Si “Classe” est placée sous

“Compte”,onobtiendralerésultatsuivant:

Àl’inverse(“Classe”puis“Compte”),onaurabien

RajouterunecolonnedecalculOn souhaite rajouter une colonne pour calculer le solde. Pour ce faire, se placer dansl’ongletOPTIONSdesOUTILSDETABLEAUCROISÉDYNAMIQUE.

Cliquez sur “CALCULS“, “CHAMPS, ÉLÉMENTS ETJEUX”puisCHAMPCALCULÉ.

On obtient cette fenêtre danslaquelle on va paramétrer lechampSolde.

Laformuleutiliséeest=Débit–Crédit.

Il suffit de double-cliquer surles rubriques pour qu’elless’ajouteràlaformule.

Aprèsavoirvalidé,onobtient:

Protégerfichiers,classeursetfeuillesProtégerlefichier*.xlsxCommetoutfichier,onpeutprotégerunclasseur lorsdesonpremierenregistrement,enutilisant la fenêtreENREGISTRER SOUS. Dans cette fenêtre, activez le boutonOUTILS >OPTIONSGÉNÉRALES.

Vous pouvez définir unmot de passe pour la lecture, ainsi qu’unmot de passe pour samodification.

Unclasseuren«lectureseule»estmodifiable,maislesmodificationsnepourrontpasremplacerlefichierexistant.Ilestproposéd’enregistrerunenouvellecopiedufichier(aveclesmodifications).

S’ilestperdu,lemotdepassen’estpasrécupérable.

ProtégerleclasseurProtégerunclasseurcontrelamodificationglobale(structure)

Pour afficher la fenêtre PROTÉGER LE CLASSEUR, activez le bouton PROTÉGER LECLASSEURdugroupeMODIFICATIONS,sousl’ongletRÉVISION.

CocherlacaseStructureempêchedemodifierlastructure,pourempêcherparexemple d’ajouter ou de supprimer des feuilles, ainsi que d’afficher les feuillesmasquées.

CocherlacaseFenêtresempêchelechangementdestaillesoudespositionsdesfenêtres.

Onpeutsaisirunmotdepassepourprotégerl’accèsàcettefenêtre.

Suiteàlaprotectiondelafeuille,leboutonProtégerleclasseurdevientleboutonÔterlaprotection.

ProtégerunefeuilleProtégerunefeuilleouuneplagedecellules(ouunecelluleindividuelle)

Pardéfaut,àlacréationd’unnouveauclasseurExcel,touteslescellulessontprêtesàêtreverrouillées.

Pourverrouillerunefeuillecomplète

Dansl’ongletRÉVISION,groupeMODIFICATIONS,cliquezsurPROTÉGERLAFEUILLE.

Cettefenêtreapparaît.Pourunusagenormal,laissezlesdeuxpremièrescasescochées.

Vouspouvezmettreunmotdepassepour renforcerla protection. En laissant le champ vide, la feuillesera malgré tout protégée mais pour ôter laprotection,aucunmotdepasseneseranécessaire.

Danslecasoùvousn’auriezchangéaucuneoptiondansExcel, toutes lescellulesserontverrouillées.

Pourdéverrouiller

Ilsuffitdecliquersurlemêmeboutonquis’esttransforméenÔterlaprotection:

Pourneverrouillerqu’unepartiedelafeuilledecalcul

Pour empêcher le verrouillage decellules qui nécessitent une saisie parl’utilisateur, sélectionnez la plage à

déverrouiller puis faites un clic droit sur la plage et choisissez “Format de cellule“.Cliquezsurl’ongletProtectionetdécochez“Verrouillée“.

ValeurcibleCetoutilpermetderechercheunrésultatspécifiquepourunecelluleenajustantlavaleurd’uneautrecellule.

Pourutilisercetoutildulogiciel,rendez-vousdansl’ongletDONNÉES,groupeOUTILSDEDONNÉES,menuANALYSEDESCÉNARIOS,puisVALEURCIBLE.

Cettefenêtreapparaît:

Exemple:

UneentreprisedésireretirerunbénéficeaprèsISde50000€enfind’exercice.Maispourcela,elledoitsavoiràcombiendoits’éleversonCA.

Bienévidemment,decechiffred’affairesdécoulentdeschargesvariablesquireprésentent15% duCA, des charges fixes de 25 000 € et un IS à 33,33%. Toutes ces conditionsdoiventêtreentendues.

B2,B3,B5,B6etB7contiennentlesformulesnécessairesàcescalculs.

SeulB4estsaisipuisquec’estunevaleurfixe.

Pour déterminer le chiffre d’affaires, on ouvre l’outil Valeur cible et on indique lesparamètressuivants:

Celluleàdéfinir:ellereprésentelavaleurfinalequ’onsouhaiteobtenir.C’estdoncnotrerésultataprèsIS,soitB7.

Valeur à atteindre : c’est l’objectif fixé par l’entreprise, en €, qu’on voudraretrouverenB7:50000

Cellule àmodifier : pour atteindre ce résultat, on va faire varier le chiffred’affaires,soitB1.

Unefoisterminé,Excelvousaffichelerésultattrouvé.

Dans certains cas très complexes (et plutôt très rares), Excel n’est pas en mesure de

trouverdesolution.Danscecas,ilestpossibled’utiliserun autre outil qui n’est autre que le complémentSolveur(nonabordédanscesfiches).

CréerdesmacrosDesmacros(nomféminin)sontdesmini-programmesécritsdansunlangagepropriétairequiestleVBA(VisualBasicforApplications).Ellespermettentderéaliserdesopérationsrépétitives (routines) préalablement programmées (ex : réinitialiser les valeurs d’untableau). C’est un langage qui s’écrit en anglais et qui nécessite des connaissancesparticulièrespourl’utiliser.

Néanmoins, Excel possède un enregistreur de macros. Grâce à lui, sans aucuneconnaissance,l’utilisateurlambdapourraparamétrersespropresmacro-programmes!

1èreétape:rajouterl’ongletDéveloppeur[FICHIER/OPTIONS/PERSONNALISERLERUBAN/CocherDÉVELOPPEUR].

2èmeétape:dansl’ongletDÉVELOPPEUR,danslegroupeCODE,cliquezsurENREGISTRER

UNEMACRO.

3èmeétape:compléterlafenêtresuivante:

Lenomn’ad’importancequepourvousyretrouverparlasuite.

Latouchederaccourcipermetderappelerla fonction ultérieurement de façon plusaisée. Attention, choisissez des raccourcisquinesontpasdéjàutilisés!

L’enregistrementpeutsefairesoitdansleclasseuractuel(silamacron’estpasvouéeàresservirpourd’autresdocumentsExcel)soit dans un classeur de macrospersonnelles (on pourra réutiliser cettemacro dans tous les classeurs ouverts par

cetutilisateur,surceposte).

4èmeétape:aprèsavoircliquésurOK,réaliseznormalement lesétapesquevousvoulezfairereproduireautomatiquementlorsd’uneprochaineexécutiondelamacro(ex:effacerlecontenud’untableau).

Unefoisterminé,cliquezsurARRÊTERL’ENREGISTREMENT.

Pourrappelerlamacro,vouspouvez:

-Utiliserlatouchederaccourciparamétréeinitialement(voirétape3)

-Dansl’ongletDÉVELOPPEUR,cliquersurMacro,puissurleNOMDELAMACRO,puissurleboutonExécuter.

-Créerunboutonetluiaffecterlamacro.

[1]Cliquer-glissersignifiequ’ilfautcliqueravecleboutongauchedelasourissurlecarrénoirpuis,sansrelâcherlasouris,faireglisserlecurseurverslebas,lehaut,ladroite,lagaucheselonlesensdelarecopie.