Besoin d'une assistance, d'une application
MS Office
Consultez GEMCCAP

Un champ calculé

Un champ calculé est une information obtenue suite à un calcul basé sur 1 ou plusieurs champs du formulaire ou de la requête. Exemple :

• Vous avez un prix unitaire Hors taxe [PUHT], une quantité [Nb], un taux de TVA [txTva], vous pourrez obtenir ainsi le prix total hors taxe, le montant de la TVA et le prix total TTC.
• Vous désirez Afficher sur un formulaire une zone de texte comprenant le nom [nom] et le prénom [prenom]

Dans un formulaire :

• Ajoutez un contrôle indépendant, pour cela il suffit de cliquer sur le contrôle dans la barre d'outils Création d'un formulaire, puis de le dessiner sur le formulaire
• Dans la propriété Source contrôle, tapez = puis une formule opérant sur un ou plusieurs champs.
  • Dans notre 1er exemple: le contrôle pour le prix total hors taxe : =[PUHT]*[Nb]
    le contrôle pour le montant de la TVA : =[PUHT]*[Nb]*[txTva]
    le contrôle pour le prix total TTC : =([PUHT]*[Nb])+([PUHT]*[Nb]*[txTva])
  • Dans notre 2ème exemple : =[nom] &" "&[prenom]

Il est également possible d'utiliser le nom des contrôles indépendants dans les champs calculés. Reprenons notre 1er exemple :

• Nommons :
  • PHT le contrôle contenant le prix total hors taxe
  • TVA, celui contenant le montant de la TVA
  • TTC, celui contenant le prix total TTC
• la propriété Source contrôle pourra alors être écrite ainsi :
  • PHT : =[PUHT]*[Nb]
  • TVA : =[PHT]*[txTva]
  • TTC : =[PHT]+[TVA]

Dans une requête :

Le principe est le même quand à la construction des calculs, mais ce dernier se fait sur la 1ère ligne de la requête comme ci-dessous :

Fonction SOMME.SI

Cette fonction permet d'effectuer la somme d'une plage selon un critère. Ce critère peut s'appliquer sur la plage que vous voulez sommer ou sur une autre plage avec la seule condition que les 2 plages soient de même taille. Sa syntaxe est SOMME.SI(plage;critère;somme_plage) où

• plage représente la plage sur laquelle le critère sera appliquée, elle peut-être la même que somme_plage
• critère doit être sous la forme d'un nombre, d'un texte ou d'une expression de comparaison. Ce critère doit être mis entre guillemet et ne peut dépendre d'une cellule. Vous pouvez avoir :
  • "10" --> = 10
  • "un texte" --> = un texte
  • ">10"--> Supérieur à 10
  • ">=10"--> Supérieur ou égal à 10
  • "<>10"-->  Différent de 10 
  • "<10"--> Inférieur à 10
  • "<=10"--> Inférieur ou égal à 10
  • "<>un texte"--> Différent de 'un texte'
  • "au*" ou "*au" ou "*au*" --> tout les mots commençant par 'au', tout les mots finissant par 'au', tous les mots contenant au.
    Une étoile permet de remplacer n'importe quel caractère et ne précise pas le nombre de caractères remplacés. au* trouvera aux et autres
    Vous pouvez également utiliser un ?, celui-ci remplacera 1 seul caractère, au? trouvera aux mais pas autres.
• somme_plage est la plage sur laquelle la somme sera effectuée.

La fonction va parcourir la plage plage et pour chaque cellule X , elle va vérifier si la cellule Y, de même position dans la plage somme_plage, répond par vrai à la condition indiqué dans critère , si oui elle ajoute la valeur de la cellule X au résultat.

Exemple 1 : vous désirez sommer uniquement les valeurs supérieures à 100 dans une plage A1:A10. La formule sera donc :
=SOMME.SI(A1:A10;">100";A1:A10)

Exemple 2 : vous avez une plage B1:B10 qui indique le nombre d'enfants, une autre plage C1:C10 qui indique la prime. Vous désirez connaître la somme des primes pour 3 enfants, la formule est :
=SOMME.SI(B1:B10;"3";C1:C10)

Fonction NB.SI

Cette fonction compte le nombre de cellules correspondant à un critère dans une plage. Elle fonctionne à peu près comme la fonction précédente. Sa syntaxe est nb.si(plage;critère)

• plage représente la plage qui va être comptée.
• critère doit être sous la forme d'un nombre, d'un texte ou d'une expression de comparaison. Ce critère doit être mis entre guillemet et ne peut dépendre d'une cellule. Reportez-vous à la fonction SOMME.SI pour connaître toutes ses possibilités.

La fonction parcourt chaque cellule de la plage plage , si le contenu de cette cellule correspond à la condition indiqué dans critère , si oui elle ajoute 1 au résultat.

Exemple : Dans une plage D1:D10, vous avez une liste de Noms et vous désirez compter le nombre de Dupont :
=NB.SI(D1:D10;"Dupont")

Trouver la valeur la plus proche dans une plage non ordonnée

Soit une plage de valeurs non triées, si vous désirez à partir d'une valeur 

En C1 pour que la formule fonctionne, il faut impérativement appuyez sur Ctrl+Maj+Entrée et non simplement sur Entrée. Ceci permet de transformer votre formule en formule matricielle, elle sera entourée de {}. Une formule matricielle fait ses calculs sur chaque cellule de la plage. Dans notre exemple A2:A10-D2 renvoie une matrice (un tableau) de valeurs correspondants à chaque valeur de la plage A2:A10 moins la valeur de la cellule D2. Le tableau ci-dessous vous aidera à comprendre le fonctionnement de cette formule, le tableau effectue le même calcul  mais en utilisant plusieurs formules et plusieurs plages. La formule matricielle fait le même travail que les 3 plages B2:B10, C2:C10, D2:D10, ce qui retourne un tableau de valeur, ensuite on applique la fonction MIN pour avoir la valeur minimum de ce tableau.
 

	A	B	C	D
1				12,5
2	10	=ABS(A2-$D$1)	=ABS(A2-$D$1)	=SI(A2=$G$11;A2)
3	15	=ABS(A3-$D$1)	=ABS(A3-$D$1)	=SI(A3=$G$11;A2)
4	25	=ABS(A4-$D$1)	=ABS(A4-$D$1)	=SI(A4=$G$11;A2)
5	65	=ABS(A5-$D$1)	=ABS(A5-$D$1)	=SI(A5=$G$11;A2)
6	12	=ABS(A6-$D$1)	=ABS(A6-$D$1)	=SI(A6=$G$11;A2)
7	45	=ABS(A7-$D$1)	=ABS(A7-$D$1)	=SI(A7=$G$11;A2)
8	30	=ABS(A8-$D$1)	=ABS(A8-$D$1)	=SI(A8=$G$11;A2)
9	5	=ABS(A9-$D$1)	=ABS(A9-$D$1)	=SI(A9=$G$11;A2)
10	6	=ABS(A10-$D$1)	=ABS(A10-$D$1)	=SI(A10=$G$11;A2)
11			=MIN(C2:C10)	=MIN(D2:D10)

Dans le 1er tableau la cellule C4 utilise la fonction EQUIV pour connaître la position de la valeur de C3 dans la plage A2:A10, le dernier paramètre est à 0 pour rechercher la valeur exacte.

Ensuite en C5, la fonction LIGNE renvoie les N° de ligne de la plage A2:A10, soit une matrice (un tableau) contenant les valeurs {2,3,4,5,6,7,8,9,10}. La fonction INDEX renvoie toujours la valeur d'un tableau  selon une position, ici le tableau est la matrice renvoyée par la fonction LIGNE et la position est celle indiquée dans la cellule C4, soit pour la position 5, la valeur 6.
 

VBA

Compter les cellules colorées

Public Function NB_SI_COULEUR(PlageSomme As Range, PlageCouleur As Range) As Long
     Dim Cel As Range
     Dim lCpt As Long

     Application.Volatile 'permet à la fonction de se recalculer lorsque le classeur se recalcul.
     For Each Cel In PlageSomme 'pour chaque cellule de la plage
          ' si la couleur de fond de la cellule est la même que la couleur de cellule de référence alors le compteur lCpt est incrémenté de 1
          If Cel.Interior.ColorIndex = PlageCouleur.Interior.ColorIndex Then lCpt = lCpt + 1
     Next
     NB_SI_COULEUR= lCpt 
End Function

Cette fonction personnalisée permet de compter les valeurs de la plage ayant la couleur de la plage, attention cette dernière doit être une cellule unique.

Sommer les cellules colorées

Public Function SOMME_SI_COULEUR(PlageSomme As Range, PlageCouleur As Range) As Double
     Dim Cel As Range
     Dim Som As Double

     Application.Volatile
     For Each Cel In PlageSomme
          ' si la couleur de fond de la cellule est la même que la couleur de cellule de référence alors on ajoute son contenu à la variable Som
          If Cel.Interior.ColorIndex = PlageCouleur.Interior.ColorIndex Then Som = Som + Cel
     Next
     SOMME_SI_COULEUR = Som
End Function

Cette fonction personnalisée permet de sommer les valeurs de la plage ayant la couleur de la plage, attention cette dernière doit être une cellule unique.

Mettre le nom entier du classeur dans l'en-tête ou le pied de page ou une cellule

Public Sub Nom_Entier_PiedGauche()
     ' insère au pied gauche le nom complet du classeur
On Error Resume Next
     ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName 'LeftHeader pour l'en-tête
End Sub

Public Sub Nom_Entier_PiedCentre()
     ' insère au pied centre le nom complet du classeur
On Error Resume Next
     ActiveSheet.PageSetup.CenterFooter = ActiveWorkbook.FullName 'CenterHeader pour l'en-tête
End Sub

Public Sub Nom_Entier_PiedDroit()
     ' insère au pied droit le nom complet du classeur
On Error Resume Next
     ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName 'RightHeader pour l'en-tête
End Sub

Passez une formule de relative à absolu et inversement

'************************
' de relative à absolu
Sub RelativeAbsolu()
     Dim rCel As Range

     For Each rCel In Selection
          rCel.Formula = Application.ConvertFormula(rCel.Formula, xlA1, , xlAbsolute)
     Next
End Sub

'************************
' de absolu à relative
Sub AbsoluRelative()
     Dim rCel As Range

     For Each rCel In Selection
          rCel.Formula = Application.ConvertFormula(rCel.Formula, xlA1, , xlRelative)
     Next
End Sub

Le numéro de la semaine selon la norme Européenne

Public Function NoSem(UneDate As Date) As Integer
     ' donne le numéro de la semaine selon la norme Européenne (1ère semaine = 1ère semaine de 4 jours)
On Error Resume Next
     NoSem = CInt(Format(UneDate, "ww", vbMonday, vbFirstFourDays))
End Function

Synchroniser l'horloge de son PC sur un serveur de temps français

Si vous avez Windows XP (pour 2000 c'est à vérifier), votre horloge est mise à jour sur un serveur de temps, ce dernier est le serveur de Microsoft ou du gouvernement américain. Rien ne nous dit que Microsoft n'en profite pas pour savoir qui a Windows XP ou tout du moins compter le nombre de Windows XP se connectant. Et puis pourquoi se régler sur une horloge américaine ?

Donc pour profiter de cette synchronisation (bien pratique car faite sur une horloge atomique) :

• Double-cliquer sur l'horloge situé à droite de votre barre des Tâches, sinon démarrer/Panneau de configuration/Date et heure
• Sélectionnez l'onglet Temps Internet
• Cocher synchroniser avec un serveur de temps Internet
• Puis tapez dans la zone en-dessous : ntp.unice.fr
• Cliquez sur Mise à jour pour synchroniser immédiatement.

Modifier les lettres des disques

Version Windows 9x et Me

• Clic-droit sur l'icône du Poste de travail/Propriétés ou Démarrer/Paramètres/Panneau de configuration/Système.
• Onglet Gestionnaire de périphériques, dans la liste, cliquez sur le + de CD-ROM ou Lecteurs de disque, sélectionnez le lecteur concerné.
• Cliquez sur le bouton Propriétés, onglet Paramètres.
• Sélectionnez la lettre pour ce lecteur dans les listes Lettre de lecteur de début et Lettre de lecteur de fin.

Version XP (2000 à vérifier)

• démarrer/Panneau de configuration/Outils d'administration/Gestion de l'ordinateur
• dans la fenêtre qui s'affiche sélectionnez, dans le volet de gauche, Gestion des disques
• dans le volet de droite, Clic-droit sur le lecteur dont vous voulez modifiez la lettre
• sélectionnez Modifier la lettre du lecteur et le chemin d'accès...
• cliquez sur Modifier
• sélectionnez une lettre non encore utilisée par un lecteur
• Ok
• Ok

Ouvrir un document sur ses dernières modifications

Dès l'ouverture de votre document appuyez sur les touches Maj+F5, vous vous déplacerez alors dans le document à l'endroit de votre dernière modification avant la fermeture.