Un long fichier d'accompagnement à un classeur Excel a de quoi étonner et rendre méfiant à première vue. Les tableurs
sont aujourd'hui courants et leur utilisation quasi instinctive. De longues explications cachent donc probablement
des complications inutiles, mais avant de trancher, je vous demande de lire ces quelques lignes et le mode d'emploi
de la page Formules puis d'essayer quelques calculs. Cinq minutes pour juger n'est pas trop demander, après quoi,
soit vous reviendrez à une utilisation classique, soit vous adopterez cette façon d'utiliser le formulaire, lirez
la suite et apprendrez à créer vos propres modèles.
Le classeur Omctrlw.xls contient 7 feuilles distinctes et une macro. Les feuilles sont repérées par les onglets :
Formules, Z ligne et ortho, Mesures, Divers, Tx, Rx, Formulaires.
L'onglet Formules contient les formules usuelles de radioélectricité qui s'utilisent de façon particulière. Tous les
développements ultérieurs se rapportent à cette feuille.
L'onglet Z Ligne et ortho contient toutes les formules ne pouvant pas profiter du backsolving et se rapportent
essentiellement aux formules de transformation d'impédance. Il contient également les formules d'orthodromie
traitées dans la page web sur les routes suivies par les ondes radio.
L'onglet Mesures effectue les calculs cités dans les pages traitant de mesures. Chaque expression en plus du titre,
comprend une indication qui correspond à la référence indiquée dans la page web.
Par exemple. Dans la page Grid-dip, dans la partie traitant de la précision vous trouvez l'indication suivante :
(Excel Onglet Mesures Référence gd3) .
L'expression numérique juste au dessus de cette indication figure dans l'onglet Mesures, avec pour titre
Grid-dip ref.gd3.
Cette formule est prête à l'emploi avec les mêmes notations.
L'onglet Divers se rapporte à des calculs sans rapport direct avec la radio ou l'électronique. Il contient
entre-autre, des formules de conversion, de la météo, un peu d'aéro, etc... Toutes les formules sont traitées
en backsolving.
Les onglets Tx et Rx sont liés à la page traitant des performances en réception HF d’Emetteurs Récepteurs et
de Récepteurs actuellement disponibles. Ces feuilles comportent des tableaux et des graphiques interactifs.
Pour ne pas dérouter l’utilisateur rétif au Backsolving, j’ai ajouté l’onglet Formulaires qui contient les formules
les plus courantes. Le calcul s’effectue de façon classique dans un tableur. Il contient aussi un formulaire écrit
et imprimable à conserver comme « pense bête ».
Le classeur est repéré par un numéro de version qui figure également dans la page de Téléchargement. Il est
susceptible d'évoluer en fonction de l'enrichissement du site.
Sommaire
Ce mode d'emploi est rappelé dans l'onglet Formules.
Modifiez les valeurs uniquement dans les cadres verts.
Pour calculer:
Actualisez les valeurs connues dans le cadre vert (les cellules en vert). La valeur dans le cadre orange
(la cellule orange) change. La cellule au dessus du cadre orange indique " !?!?! ".
Placez le curseur dans la cellule à calculer dans le cadre vert.
Initialisez avec une valeur plausible, 1 ou la dernière valeur affichée en première approximation.
Appuyez sur Ctrl w ou sur un bouton Go!.
Si la valeur au-dessus du cadre vert, la cellule orange, est égale à 0,ou très proche de 0,
la valeur de la cellule pointée par le curseur est le résultat recherché.
La cellule au dessus du cadre orange indique " OK ".
Un deuxième appui sur Ctrl w ou sur Go! peut être nécessaire pour totalement annuler la cellule orange.
C'est tout !
Un exemple avec la toute première formule, la loi d'ohm : dans le cadre vert nous posons I = 0,1 R = 100.
Placer le curseur en U et Appuyer sur Ctrl-w. La valeur en U s'est modifiée et indique maintenant 10 qui est
la réponse recherchée.
Noter aussi la valeur de la cellule orange qui doit être égale à 0. Tant que cette cellule n'est pas à zéro,
les valeurs contenues dans le cadre vert ne sont pas cohérentes avec la formule. Il y a « résultat » uniquement
lorsque le cadre orange est nul ou très proche de 0.
Poursuivons : le dernier résultat donne U = 10. Changeons I = 0,2. Curseur en R, Ctrl-w, R devient 50.
Et enfin : avec R = 50 changeons U = 12. Curseur en I, Ctrl-w, I devient 0,24.
Nous avons donc, à l'aide d'une seule formule, dans les mêmes cellules, calculé une première fois U = R*I,
puis R = U/I et enfin I = R/I.
C'est ça, le backsolving. La même procédure s'applique à toutes les expressions de l'onglet Formules.
Lorsque l'on calcule sur des expressions complexes et non linéaires, il peut arriver que le résultat indiqué soit
mathématiquement correct mais physiquement aberrant, telle qu'une capacité ou une résistance négative. Il faut alors
ré-initialiser la valeur de la cellule à calculer avec une valeur proche de la valeur recherchée.
Si vous n'êtes pas convaincus et que vous avez prochainement rendez-vous avec votre banquier au sujet d'un emprunt
immobilier, allez en J2. Mais ce n'est peut-être pas une bonne idée, vous y découvrirez vite les dures lois de la finance.
Sommaire
Tout simplement pour ne pas à avoir à écrire autant d'expressions qu'il y a de variables dans une formule.
Pour reprendre l'exemple de la loi d'Ohm, pour réaliser les 3 calculs cités plus haut, deux autres expressions auraient
été nécessaires, l'une exprimant R en fonction de U,I et l'autre exprimant I en fonction de U et de R.
De plus, le procédé évite d'avoir à recopier les résultats dans les autres cellules traitant du même problème.
Sommaire
Les curieux auront sans doute déjà jeté un coup d'oeil au contenu de la fameuse cellule orange. Sous forme littérale
cela donne : = P - R/I. Appelons le contenu de cette fonction O comme orange.
Un peu de maths.
Soit une fonction y= f(x,w,z). Dans le tableur il nous faut les cellules y, x, w, z. En utilisation classique, y contient
f(x,w,z) l'expression à calculer et x, w, z, les valeurs correspondantes.
On crée une nouvelle fonction O = y- f(x,w,z). Si y = f(x,w,z) alors et dans tous les cas O = 0, qui se lit O égale zéro.
Il suffit donc de créer cette nouvelle fonction O= g(y,x,w,z) dans la case orange et "s'arranger" pour que cette cellule
soit égale à zéro.
C'est une macro, écrite en VBA, commandée par Ctrl w qui "s'arrange" pour annuler la case orange. Cette macro contient
un algorithme itératif basé sur la méthode de Newton et utilise le calcul de la dérivée dg/dy ou dg/dx ou dg/dw ou dg/dz
suivant la cellule où le calcul a été déclenché. Il s'agit d'une évaluation numérique et non littérale. La valeur cible
de la case orange est unique et égale à 0, ce qui simplifie l'écriture de la procédure.
Si le calcul est déclenché, comme prévu, avec le curseur dans le cadre vert, la procédure VBA évalue la première
expression juste au dessus du cadre vert.
En général, le procédé converge très rapidement. Si une fonction admet plusieurs racines, c'est le cas de presque toutes
les fonctions non linéaires, l'algorithme converge fréquemment vers la valeur la plus proche de la valeur initiale.
C'est à dire, de la valeur qui se trouve dans la cellule à calculer avant l'appui sur ctrl w (Si le domaine de la
fonction est monotone et continu ).
Les formules courantes en radioélectricité répondent à ces critères.
Les connaisseurs savent sans doute que la possibilité de backsolving existe déjà dans Excel. Elle s'obtient par
Outils/Valeur cible et trois champs à renseigner. Une fréquente utilisation par cette voie deviendrait vite fastidieuse.
La macro commandée par Ctrl-w ou la touche Go réalise directement cette fonction en considérant systématiquement la
cellule à atteindre comme étant la cellule orange, la valeur cible 0 et la cellule à modifier celle pointée par le
curseur dans la cadre vert au moment du déclenchement de la commande.
Sommaire
Sur la ligne 100 nous rappelons le titre et la formule telle que nous la trouvons dans les bons ouvrages.
Ligne 101, rien pour aérer et faire joli.
J104 U
J105 R
J106 I
Nous initialisons K104 K105 K106 à 1 et nous colorons ces cellules en vert. Menu Format Cellules Motifs
En K103 nous écrivons =K104 - K105*K106. C'est à dire = U - R*I. Nous colorons K103 en orange.
Recopiez un bouton Go! en J102 et le contenu d'une des cellules au dessus du cadre orange en K102.
La méthode générale peut se décrire ainsi :
Pour une formule y = f(x,z,...)
De la gauche vers la droite.
Le modèle est prêt à l'emploi.