Fonction somme.si.ens dans excel

Cette fonction permet d'aller plus loin que la fonction somme.si.

Somme.si n'utilise qu'un seul critère.

Mais, si vous avez besoin d'aller rechercher des valeurs dans une base de données selon plusieurs critères, alors somme.si.ens vous facilitera la tache.

Dans l'exemple suivant, si vous voulez savoir quel est le montant des factures du mois de mai pour du "préventif" qui sont payées, la formule : =+SOMME.SI.ENS(D2:D17;A2:A17;"mai";C2:C17;"préventif";E2:E17;"oui") vous donnera la réponse.

Excel, allez plus vite avec les touches Ctrl-

Ctrl-s : sauvegarde

Ctrl-g : mise en gras

Ctrl-i : italiques

Ctrl- b : copie la cellule du dessus ou copie la 1° ligne de la sélection sur les autres

Ctrl-d : idem ctrl-b pour les lignes

Ctrl-c : copie

Ctrl-v : colle

Ctrl-x : coupe

Ctrl-a : sélectionne toutes les données contigües

Ctrl-z : annule la dernière action

Ctrl-y : rétablit l'action annulée

Ctrl-f : recherche une valeur

Ctrl-h : remplace une valeur

Ctrl-maj-% : convertit en %age

Ctrl-+ : ajout de cellules

Ctrl-- : suppression de cellules

Ctrl-flèche bas ou haut ou droite ou gauche : se déplace à la dernière cellule non vide contigüe

Un bon tableau de bord se génère automatiquement

Une des raisons pour laquelle, un tableau de bord part aux oubliettes et n'est pas utilisé est qu'il est long et fastidieux à préparer chaque mois.

Pour ma part, je ne conçois un tableau de bord que comme une exploitation automatique d'un outil de gestion. Ce que je veux dire, c'est qu'il doit se mettre à jour directement avec les informations de cet outil.

Par exemple, le tableau de suivi des ventes se génére tout seul à partir d'un export des factures. Cela permet d'être sûr de "tout avoir". Ensuite, les tableaux, automatiquement mis à jour, sont prêts pour l'analyse sur le champ.

Il y a un peu d'investissement à faire au départ. Mais après, vos statistiques de ventes tombent toutes seules en quelques instants dès que vous en avez besoin.

Alors, finies les resaisies inutiles. Vive l'intégration !

voir aussi : un TDB pour gagner du temps

reprise et tableau de bord                  tableau de bord et personnel

produire ou analyser son tableau de bord              tableau de bord des ventes par client

effets positifs du TDB sur la rentabilité                                  assistance de gestion

Affichage des heures au-delà de 24 dans un tableur

Les tableaux de suivi des heures du personnel, du dirigeant ou des machines nécessitent souvent d'afficher des nombres d'heures supérieurs à 24.


Dans ce cas, il faut choisir le format d'affichage avec des crochets autour du h pour heures comme ceci : [h]:mm.

Par exemple : 25 heures et 34 minutes s'afficheront 25:34 avec le format : [h]:mm et 01:34 avec le format : hh:mm (Les formats se trouvent dans le menu "format/cellule" ou par le click droit sur la cellule).

voir aussi : comprendre les dates et les heures dans les tableurs 

d'autres trucs et astuces sur les tableurs 

les temps perdus d'une machine

indicateurs pour le chef d'entreprise

Les tableaux croisés dynamiques des tableurs

Les tableaux croisés dynamiques sont des outils extrêmement puissants pour analyser des listes de données.

Supposons que vous disposiez d'une liste de mouvements de stocks et que vous souhaitiez savoir combien il vous reste de chacun des articles. Alors, il vous suffit de sélectionner la zone avec toutes ces données et d'en faire un tableau croisé dynamique dans lequel vous mettrez en ligne les articles et dans la colonne résultat la somme de tous les mouvements.

La mise à jour du tableau nécessite de sélectionner à nouveau les données puis d'actualiser le tableau.

Vous pourrez aussi facilement demander à n'avoir que les mouvements de ventes et à afficher en colonnes les dates regroupées par mois pour avoir vos statistiques de ventes en quantité.


Avec un peu de pratique, on arrive à des résultats impressionnants.

voir aussi : somme.si dans les tableurs

une limite des tableaux croisés dynamiques

facturer comme il faut avec un tableur

d'autres trucs, astuces et outils sur tableur

Les logiciels de tableau de bord

Personnellement, je n'y crois pas.

Le TDB est une vision très personnelle d'une entreprise unique dirigée par un patron qui a des objectifs très spécifiques.

Alors, tirer un TDB d'un logiciel fait pour toutes les entreprises n'apportera rien de bien concluant au dirigeant. 

Je pense que l'élaboration du TDB de l'entreprise, et c'est encore plus vrai pour la TPE, passe par un travail sur tableur. Le tableur permet d'être vraiment focalisé sur les besoins du dirigeant et de répondre aux critères de pertinence et de lisibilité d'un bon TDB.

Et comme il faut reporter des informations trouvées dans les outils de gestion, on est sûr de la manière sont elles sont calculées.

voir aussi : les logiciels pour la gestion
le tableau de bord de suivi des devis               le TDB dans la TPE
les factures sur un tableur                   trucs et astuces de tableur
le GPS du dirigeant

La comptabilité d'une TPE sur un tableur ?

A mon avis, il vaut mieux vous orienter vers un des logiciels de comptabilité du marché qui ne sont pas chers et faciles d'emploi.

Tenir une comptabilité sérieuse sur un tableur risque de vous faire passer beaucoup de temps pour des résultats qui ne vous satisferont pas et qui impliqueront probablement une ressaisie (donc des coûts) par un expert-comptable.

Par contre, le raisonnement de tenir vous même la comptabilité de votre TPE me parait excellent; la comptabilité est un outil de pilotage important et vous permettra de suivre presqu'en temps réel l'évolution économique de votre entreprise.

voir aussi : la saisie de la comptabilité
la comptabilité analytique              plus sur la comptabilité
plus sur les tableurs              audit du service comptable
tableau des dépenses et comptabilité                    intéressez vous à votre comptabilité !

Format pour les codes postaux dans les tableurs

La question : comment faire afficher le zéro qui débute les codes postaux des 9 premiers départements dans les tableurs ?


Ils afficheront en général "6110" alors que l'on voudrait "06110".

2 solutions :

1. taper une " ' " (touche 4) au début du code postal : " '06110 ". Mais alors, les tableurs considéreront cette saisie comme du texte, donc vous ne pourrez pas utiliser cette cellule pour faire des calculs. 
2. personnaliser le format d'affichage de la cellule (click droit puis format) en créant le format 00000, c'est-à-dire cinq "zéros" d'affilée. Et dans ce cas, la saisie reste un nombre utilisable dans des calculs.

voir aussi : mise en forme conditionnelle

afficher des heures au delà de 24                 tableaux croisés dynamiques

somme.si, fonction magique                 comprendre dates et heures

facturer avec un tableur

Somme.si dans les tableurs

Cette fonction des tableurs est particulièrement intéressante lorsqu'il s'agit de faire la somme de montants en fonction de critères.

Par exemple, vous avez une liste de toutes vos dépenses avec l'indication du mois de chacune, avec la fonction "somme.si", vous pourrez calculer facilement la somme des dépenses de chaque mois.



Somme.si(zone de recherche, valeur recherchée, zone des valeurs à sommer)


Dans l'exemple ci-dessus : 

• Zone de recherche : colonne A 
• Zone des valeurs à sommer : colonne C

en F3 la formule est donc : +SOMME.SI(A:A;E3;C:C)

voir aussi : dates et heures dans les tableurs

tenir sa comptabilité sur un tableur ?          d'autres trucs et astuces pour les tableurs 

format pour les codes postaux                      tableaux croisés dynamiques

les prestations de GPS

Comment comptabiliser les notes de frais ?

Si votre activité fait que les notes de frais sont nombreuses et représentent de grosses dépenses, alors, il peut être bien de préparer une petite application en tableur organisée de la manière suivante :

•  Onglet 1 : saisie par les personnes de leurs dépenses avec un numéro chronologique renvoyant au justificatif lui-même (les justificatifs sont scotchés sur des feuilles blanches) Date – libellé – nature – montant HT – montant TTC 
• Onglet 2 : récapitulatif des dépenses par jour et par nature (essence, parking, péage, hôtel, restauration, invitation, petits achats …) pour un 1° degré de contrôle 
• Onglet 3 : récapitulatif sur l’année, mois par mois et par nature 
• Onglet 4 :  mise en forme des écritures pour un export vers le logiciel de comptabilité 

Vous aurez ainsi un parfait outil de contrôle et de suivi de ces notes de frais, tout à fait dans l'esprit de GPS.


Les comptes comptables concernés sont en général les 625 pour les frais de déplacements, missions et réceptions : 625100 voyages déplacements  -  625600 mission  -  625700 réception....

voir aussi : comment contrôler les notes de frais

 frais généraux et marge                     plus sur les notes de frais

tableur                     tenir la comptabilité

les prestations de GPS

Tableau de bord des ventes par client

Voici un outil très opérationnel pour les entreprises qui travaillent mois après mois avec les mêmes clients :

Ce tableau présente :
+ en colonne les mois les uns à la suite des autres
+ en ligne les clients par ordre décroissant de chiffre d'affaires
+ à l'intersection des deux le CA du mois du client

L'analyse de ce tableau des ventes par client permet de savoir très rapidement comment orienter l'action commerciale immédiate en fonction du comportement de chaque client, par exemple :
+ Client 2 : client régulier - semble satisfait de nos prestations, que peut-on lui apporter de plus ?
+ Client 136 : client irrégulier en fréquence et en montant - comment le fidéliser ?
+ Client 1 : pourquoi ne commande-t-il plus depuis 7 mois ?
+ Client 119 : ce client a passé sa première commande il y 2 mois - est-il content ou pas ?

Ce tableau est relativement facile à mettre en œuvre pour peu que l'on maîtrise quelques outils décrits par ailleurs dans ce blog outils de gestion ou utiliser les tableurs

voir aussi : une politique commerciale gérable
des prévisions de ventes quotidiennes                analyser les remises
à propos de la facturation                     tableau de bord 
le tableau de bord, élément clef d'une reprise

Il n'y a pas de modèle de tableau de bord

Le tableau de bord est le reflet de l'entreprise, de son activité, de ses capacités, de son histoire et de ses objectifs.

Il ne peut donc y avoir de tableau de bord d'entreprise ou de TPE standard prêt à l'emploi qui soit opérationnel.


Ou bien, on tombe dans des modèles qui sont trop larges et n'apportent rien; le compte d'exploitation et le bilan annuels en sont un exemple : ils sont les mêmes pour toutes les entreprises, mais servent peu au pilotage. 

Par contre, vous en trouverez des exemples dans certains billets de ce blog sous le thème : "tableau de bord" ou ci-dessous.

C'est pour cela, que dans ma pratique je recrée tous les tableaux au démarrage d'une mission pour coller aux spécificités de chaque entreprise, puis nous les faisons évoluer au gré des besoins.

Et pour cela les tableurs sont des outils formidables.

voir aussi : indicateurs pour le suivi d'un commerce                     le tableau de suivi des devis

 le tableau de suivi d'un atelier                                       TDB pour un commerce             

mieux utiliser son tableur                                          TDB des ventes par client

plus sur le tableau de bord                                       tableau de bord du stock

le GPS du dirigeant                    tableau de bord de la paye
                         tableau de bord de la masse salariale

Tableaux croisés dynamiques – une limite

Les tableaux croisés dynamiques des tableurs sont des outils très puissants pour analyser une grosse base de données.

Une des limites se trouve dans le cas suivant.

Supposons que vous souhaitiez analyser une base des factures émises comportant les champs suivants : date, numéro facture, client, montant. Si un jour, vous n'avez pas facturé, alors le jour n'apparaitra pas dans le tableau croisé dynamique. Et si vous calculez une moyenne, elle sera fausse. Ou si vous dessinez un graphique, il n'y aura pas de chute de la courbe pour ce jour. 

Dans certains cas, il est possible de remédier à ce problème en demandant l'"affichage des éléments sans données".

voir aussi : suivi économique de l'activité par l'analyse des factures

somme.si            tableau de bord des ventes par client

facturer sur un tableur                    mise en forme conditionnelle

Base de données des contrats

Il est utile de se créer une petite base de données des contrats qui ont été signés par l'entreprise avec des fournisseurs ou des partenaires : loyer, téléphone, photocopieuse, prévoyance, assurance...

Un petit tableur fait en général l'affaire.

Spécifiez bien les conditions du contrat, et surtout les dates d'échéance et les délais de dénonciation.

Et puis, tous les trimestres, vous faites le point pour éviter de faire perdurer inutilement un contrat ou pour renégocier tant qu'il en est encore temps.

voir aussi : reconstruire une base de données

facturer sur un tableur                        mise en forme conditionnelle

dates et heures              imprimer les entêtes de colonnes

Facturer sur un tableur

Il arrive que certaines habitudes professionnelles rendent difficiles la mise en place d'un outil de réalisation des factures.

J'ai par exemple mis en place un tel outil dans une entreprise parce que les prix de vente sont calculés par une suite de remises successives : remise "nouveau client", remise "professionnel", remise "groupage", remise "quantitative", etc…

Le tableur est le moyen souple de résoudre une telle difficulté :
+ 1 onglet pour les clients
+ 1 onglet pour les articles, les tarifs et les taux des remises successives
+ 1 onglet pour les informations de facturations (N° et date facture, article retenu, choix des remises applicables, montants HT et TTC) et la méthode de calcul
+ 1 onglet pour mettre en forme (logo, couleurs, polices...) la facture à partir de son N°
+ des onglets de tableau de bord pour les analyses. (voir post : analyse de l'activité) 

Ainsi, tout est disponible, de la réalisation efficace des tâches de facturation à l'analyse de l'activité.

 En exploitation, il convient d'être vigilant car le degré de sécurité d'un tableur est inférieur à celui d’un outil de gestion comme CIEL Gestion Commerciale. Mais, c'est la contrepartie de la souplesse.

voir aussi : comptabilité sur un tableur
 base de données des contrats              remises de fin d'année
exemple de comptabilité analytique                 somme.si dans les tableurs
suivi du solde dû par les clients                assistance de gestion

Un seul dossier sur votre PC pour le Tableau de Bord

Je vois souvent les personnes en charge de la préparation du tableau de bord créer dans leur ordinateur des dossiers par nature de sujets traités : ventes, personnel, stocks, etc… Du coup, lorsqu'ils préparent les tableaux du mois, ils passent beaucoup de temps à naviguer d'un dossier à l'autre, voire à rechercher les fichiers dans l'arborescence gigantesque de leur disque dur. Pour ma part, je préfère créer un dossier : TABLEAU DE BORD et des sous-dossiers mensuels : 2009 01, 2009 02, etc… Dans chacun de ces dossiers, je stocke les fichiers de travail en intégrant dans le nom le mois concerné : VENTES 2009 02, PERSONNEL 2009 02, STOCKS 2009 02… Je retrouve ainsi beaucoup plus facilement ce que je recherche. Et de temps en temps, j'archive les mois anciens qui ne sont plus intéressants.

Mise en forme conditionnelle

La mise en forme conditionnelle est une fonction des tableurs qui permet d'avoir une présentation des cellules qui dépend de valeurs, de résultats de formules, de comparaisons.

Par exemple, c'est cette fonction permet de faire apparaitre automatiquement en rouge les soldes négatifs des comptes bancaires.

Je viens de l'utiliser pour mettre en place un planning d'ouverture de magasins et de présence des vendeurs. L'efficacité d'un tel outil dépend beaucoup de sa lisibilité. La mise en forme conditionnelle a donc été une alliée très précieuse.

voir aussi : lisibilité des tableaux 

afficher les heures au delà de 24                          tableaux croisés dynamiques

somme.si                           format pour les codes postaux       

modèle de tableau de bord ?

Comment calculer un coût de revient ?

C'est une opération délicate qui repose avant tout sur une connaissance très précise des dépenses de l'entreprise et des quantités traitées.

En effet, avec les tableurs il est facile de faire toutes les répartitions nécessaires, mais seulement si l'on sait quoi répartir.


Il convient ensuite de bien faire la distinction entre charges directes ou indirectes, variables ou invariables.

Enfin, ce coût de revient peut se calculer à différentes étapes de la réalisation du produit.

voir aussi : comptabilité analytique et prix de revient 

   la grille analytique                              coût de la tenue en stock           

tableau de suivi d'un atelier                     calcul du rendement d'un atelier

tableau de bord d'une boulangerie                                   gérer une voilerie

le GPS du dirigeant

Comprendre les dates et les heures dans les tableurs

Les tableurs gèrent très bien les heures et les dates. 

Le format de base est jj/mm/aaaa pour les dates et hh:mm:ss pour les heures. Indépendamment du format utilisé, les dates et heures sont converties en nombre nnnnnnn,dddddddd où les entiers sont les jours et les décimales les heures. C'est à partir de ces chiffres que se font les calculs. 

Les jours sont calculés à partir du 1/1/1900, puis chaque jour vaut une unité.

Ainsi : 

• le 01/01/1900 est converti en "1" 
• le 31/01/1900 est converti en 31 (30 jours plus tard que le 01/01/1900; 1+30) 
• le 31/12/1900 est converti en 366 (1900 a été une année bissextile) 
• le 01/01/2006 est converti en 38 718 
• le 31/01/2006 est converti en 38 748 

Les heures au sein de la journée sont exprimées en fraction de jours : 1 seconde s'écrit 00:00:01 et vaut 0,00001157 c'est-à-dire 1/(24 x 60 x 60) = 0,00001157

• 1 minute s'écrit 00:01:00 et vaut 0,00069444 c'est-à-dire 1/(24 x 60) = 0,00069444 
• 1 heure s'écrit 01:00:00 et vaut 0,04166667 c'est-à-dire 1/(24) = 0,04166667 
• 1 jour s'écrit 24:00:00 et vaut 1 c'est-à-dire 24 x 1/(24) = 1

Exemple : 8h 25 mn et 37 s vaut 0,351122685 c'est à dire :

(8/24) + 25/(24 x 60) + 37/(24 x 60 x 60)

0,333333333 + 0,017361111 + 0,000428241 = 0,351122685

Si nous considérons que nous sommes le 30/1/2006 à 8h 25 mn et 37 s, alors le tableur donnera 38 748,351122685 pour cet instant précis.

voir aussi : somme.si dans les tableurs 

affichage des heures au delà de 24                     les tableaux croisés dynamiques 

facturer grâce au tableur                        mise en forme conditionnelle 

copier coller de CIEL vers Excel                   imprimer les entêtes de colonnes savoir-faire de GPS

Politique commerciale gérable

On imagine parfois des politiques commerciales très … innovatrices ou sophistiquées.

Soyons vigilants à ce qu'elles soient gérables par l'outil de gestion commerciale de l'entreprise.

Car, il serait bien dommage de devoir faire une partie des calculs à la main ou bien de devoir faire des analyses compliquées dans un tableur pour verser une commission, une remise ou faire un cadeau.

voir aussi : des documents datés et signés
TDB des ventes            logiciel de gestion commerciale

TDB et ODG Outils de Gestion                  produire des factures