Theo utilise une feuille de calcul Excel pour suivre les réservations dans son entreprise. Les données se composent de seulement trois colonnes. Le premier est le nom d’une personne, le second le numéro de la première semaine (1-52) de la réservation et le troisième le numéro de la dernière semaine de la réservation.

Les personnes peuvent être réservées pour plusieurs semaines (c’est-à-dire que la semaine de début est 15 et la semaine de fin est 19). Theo a besoin d’un moyen de saisir un numéro de semaine, puis de demander à une formule de déterminer quel nom (colonne A) est associé à ce numéro de semaine. Les données ne sont pas triées dans un ordre particulier et la société ne permettra pas à Theo d’utiliser une macro pour obtenir le résultat (il doit s’agir d’une formule).

La situation de Theo semble assez simple, mais elle est remplie d’embûches lors de l’élaboration d’une solution. L’examen des données potentielles (comme le montre la figure suivante) illustre rapidement pourquoi c’est le cas. (Voir la figure 1.)

image

Figure 1. Données potentielles pour le problème de Theo.

Notez que les données (comme l’a dit Théo) ne sont pas dans un ordre particulier.

Notez également qu’il y a des semaines où il n’y a pas de réservation (comme la semaine 5 ou 6), des semaines où il y a plusieurs personnes (comme la semaine 11 ou 16) et des semaines où quelqu’un est réservé, mais le nombre n’apparaît pas dans la colonne B ou C (comme la semaine 12 ou 17).

Avant de commencer à examiner les solutions potentielles, supposons que la semaine que vous souhaitez connaître est la cellule E1. Vous devez nommer cette plage en tant que requête. En outre, nommez la plage contenant les noms des personnes (dans cet exemple, les cellules A2: A10) comme ResNames, les semaines de début (B2: B10) comme StartWeeks et les semaines de fin (C2: C10) comme EndWeeks. Enfin, définissez un nom pour la table entière (A2: C10), tel que MyData. Cette dénomination, bien qu’elle ne soit pas strictement nécessaire, facilitera la compréhension des formules.

Une solution potentielle consiste à ajouter ce que l’on appelle communément une «colonne d’aide». Ajoutez ce qui suit à la cellule D2:

=IF(AND(Query>=B2,Query<=C2),"RESERVED","")

Copiez la formule vers le bas, pour autant de cellules qu’il y a de noms dans le tableau. (Par exemple, copiez-le dans la cellule D10.) Lorsque vous placez un numéro de semaine dans la cellule E1, le mot «RÉSERVÉ» apparaît à droite de toute réservation qui implique ce numéro de semaine. Il est également facile de voir s’il y a plusieurs personnes réservées pour cette semaine ou s’il n’y a pas de personnes réservées pour cette semaine. Vous pouvez même appliquer un filtre automatique et choisir d’afficher uniquement les enregistrements avec le mot «RÉSERVÉ» dans la colonne D.

Vous pouvez, si vous le souhaitez, renoncer à la colonne d’aide et envisager d’utiliser la mise en forme conditionnelle pour afficher qui est réservé pour une semaine souhaitée.

Sélectionnez simplement les noms dans la colonne A et ajoutez une règle de mise en forme conditionnelle qui utilise la formule suivante:

=AND(Query>=B2,Query<=C2)

(La façon dont vous entrez les règles de mise en forme conditionnelle a été décrite en détail dans d’autres numéros de ExcelTips.) Définissez la règle de sorte qu’elle modifie l’ombrage (motif) appliqué à la cellule, et vous pourrez facilement voir quelles réservations s’appliquent à la semaine qui vous intéresse.

Une autre approche consiste à utiliser une formule matricielle. Sélectionnez quelques cellules de plus que le nombre de réservations qui se chevauchent prévu, puis entrez ce qui suit dans ces cellules en appuyant sur Ctrl + Maj + Entrée:

=IFERROR(INDEX(ResNames,LARGE((StartWeeks<=Query)(EndWeeks>=Query)(ROW(ResNames)),ROW()-1)-1),"")

Lorsque vous choisissez le nombre de cellules, vous voulez que cette formule matricielle occupe, regardez, par exemple, le nombre de personnes qui peuvent être réservées au cours de la semaine 11. Dans l’exemple montré dans cette astuce, il s’agit de 2 personnes. Sélectionnez plus que ce nombre de cellules, puis placez la formule matricielle dans ces cellules.

Si vous pensez avoir 20 personnes potentiellement réservées pour la même semaine, vous voudrez choisir un plus grand nombre de cellules, comme 20 ou 30. Sélectionnez simplement les cellules, mettez la formule dans la barre de formule, puis appuyez sur Ctrl + Maj + Entrée.

Enfin, vous devriez vraiment envisager de revoir la présentation de vos données.

Vous pouvez créer une feuille de calcul contenant les numéros de semaine dans la colonne A (1 à 52 ou 53), puis les noms de lieu dans la colonne B.Si une personne était réservée pour deux semaines, son nom apparaîtrait dans la colonne B deux fois, une fois à côté de chacun des deux semaines qu’ils ont réservé.

Avec vos données dans ce format, vous pouvez facilement scanner les données pour voir quelles semaines sont disponibles, lesquelles sont prises et par qui elles sont prises.

Si vous souhaitez effectuer une sorte de recherche, il est facile d’utiliser la fonction RECHERCHEV en fonction du numéro de semaine, car il s’agit de la première colonne des données, dans l’ordre trié.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (11078) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Determining_a_Name_for_a_Week_Number [Détermination d’un nom pour un numéro de semaine].