Comment créer un tableau de planning de suivi en utilisant des formules dans Google Sheets

GiGiKing·mars 6, 2024·0 commentaire

Le suivi des factures ou des rendez-vous peut parfois être une tâche chronophage et fastidieuse. Mais saviez-vous qu’il est possible de créer un tableau de planning de suivi dans Google Sheets grâce à quelques formules simples ? Cela vous permettra de suivre vos factures ou rendez-vous de manière systématique et en temps voulu.

Les données nécessaires

Pour créer un tableau de planning de suivi des factures ou rendez-vous dans Google Sheets, nous avons besoin des informations suivantes :

  1. La première date de suivi.
  2. Le nom de la personne à contacter.
  3. Le contact (numéro de téléphone ou adresse e-mail).
  4. Le nombre de fois à effectuer le suivi.
  5. La durée entre chaque suivi.

En utilisant ces informations sous forme de tableau, nous pouvons créer un tableau de planning de suivi dans Google Sheets. Voici un aperçu de ces informations sous forme de tableau :

Première date de suivi Nom Contact Nombre de jours de suivi Fréquence
13/06/2020 Eva xxxxxxx 2 5 jours

Dans ce tableau, nous souhaitons que les lignes 2 et 3 correspondent à la personne Eva.

Comment créer le tableau à partir des données

Maintenant que nous avons les données nécessaires, nous allons vous expliquer comment créer le tableau de planning de suivi en utilisant des formules dans Google Sheets.

  1. Tout d’abord, saisissez les données dans une feuille Google Sheets. Les données doivent être dans la plage A1:E5. Nous avons nommé l’onglet « Suivi ».
  2. Dans cette feuille « Suivi », nous avons défini le nombre total de lignes à 20, ce qui permet d’améliorer les performances. Vous pouvez utiliser plusieurs lignes (au moins 500 lignes) sans problème.
  3. Le nombre total de colonnes dans cette feuille (Suivi) est limité à 15. Cela signifie que la dernière colonne est la colonne O. Les 5 premières colonnes contiennent les données d’exemple et les 10 colonnes suivantes sont réservées aux 10 suivis.
  4. Suivez les étapes suivantes à partir de la cellule F2 pour créer le tableau de planning de suivi en utilisant des formules dans Google Sheets.

Générer une séquence de dates de durée (fréquence) (étape 1)

Tout d’abord, examinons la syntaxe de la fonction SEQUENCE (une fonction qui génère des nombres ou des dates séquentiels) et ensuite une formule générique basée sur celle-ci.

Syntaxe : SEQUENCE(lignes, [colonnes], [début], [pas])

Formule générique : 

=SIERREUR(SEQUENCE(1,Nombre_de_jours_de_suivi,Première_date_de_suivi+Fréquence,Fréquence))

Arguments dans la fonction et références correspondantes :

  • lignes : 1
  • colonnes : Nombre_de_jours_de_suivi
  • début : Première_date_de_suivi + Fréquence
  • pas : Fréquence

Selon cette formule générique, utilisez la formule Séquence ci-dessous dans la cellule F2 et faites glisser jusqu’à la cellule F20 :

=SIERREUR(SEQUENCE(1,D2,A2+E2,E2))

Maintenant, passons à la deuxième étape.

Combinez les données source et les dates de durée et aplatissez-les (étape 2)

Maintenant, nous allons commencer à créer le tableau de planning de suivi en utilisant une formule matricielle dans un nouvel onglet intitulé « Sch » dans le même fichier.

Pour vous aider à comprendre ma formule, je vais d’abord utiliser la plage A2:O5 (plage fermée) au lieu de A2:O (plage ouverte) de l’onglet « Suivi ».

Je vais combiner les colonnes de données source d’une manière spécifique en utilisant les opérateurs esperluette (&) et ensuite les aplatir en utilisant la fonction non documentée FLATTEN.

Voici la formule pour une plage fermée :

=ArrayFormula(APLATIR('Suivi'!A2:A5&"|"& 'Suivi'!B2:B5&"|"& 'Suivi'!C2:C5&"|"& 'Suivi'!F2:O5,"|"))

Avant de passer aux étapes finales, permettez-moi de vous expliquer comment utiliser une plage ouverte dans la formule ci-dessus.

Voici les références de tableau pour changer la formule de « fermée » à « ouverte » : ‘Suivi’!A2:A5, ‘Suivi’!B2:B5, ‘Suivi’!C2:C5 et ‘Suivi’!F2:O5.

Bien sûr, nous pouvons utiliser A2:A, B2:B, C2:C et F2:O comme plages ouvertes. Mais je ne le suis pas ici. Pourquoi ?

Parce que, plus tard, lors de la création du planning de suivi final, j’utiliserai les fonctions SPLIT et QUERY pour formater les données combinées et aplatis.

Dans ces deux fonctions, QUERY peut ne pas fonctionner correctement avec une plage ouverte. En effet, il y aurait une colonne (la première colonne) avec des données de types mixtes (dates et symboles de pipe) après le split. Cela pourrait causer des problèmes dans QUERY.

Voici donc la méthode appropriée pour utiliser une plage ouverte dans notre scénario spécifique :

J’utilise la fonction ARRAY_CONSTRAIN pour limiter la plage ouverte A2:A, B2:B, C2:C et F2:O à la dernière cellule non vide de la colonne A.

Vous pouvez remplacer A2:A5 dans la formule ci-dessus par la formule ARRAY_CONSTRAIN suivante :

ARRAY_CONSTRAIN('Suivi'!A2:A, MATCH(2,1/('Suivi'!A2:A<>""),1) ,1 )

Pour B2:B, utilisez la formule ARRAY_CONSTRAIN ci-dessus et remplacez A2:A par B2:B :

ARRAY_CONSTRAIN('Suivi'!B2:B, MATCH(2,1/('Suivi'!A2:A<>""),1) ,1 )

La formule pour C2:C est la suivante :

ARRAY_CONSTRAIN('Suivi'!C2:C, MATCH(2,1/('Suivi'!A2:A<>""),1) ,1 )

Et enfin, la formule pour F2:O5 est la suivante :

ARRAY_CONSTRAIN('Suivi'!F2:O, MATCH(2,1/('Suivi'!A2:A<>""),1) ,10 )

La formule après les changements mentionnés sera donc la suivante :

=ArrayFormula(APLATIR(ARRAY_CONSTRAIN('Suivi'!A2:A,MATCH(2,1/('Suivi'!A2:A<>""),1),1 )&amp;"|"&amp; 
                     ARRAY_CONSTRAIN('Suivi'!B2:B,MATCH(2,1/('Suivi'!A2:A<>""),1),1 )&amp;"|"&amp; 
                     ARRAY_CONSTRAIN('Suivi'!C2:C,MATCH(2,1/('Suivi'!A2:A<>""),1),1 )&amp;"|"&amp; 
                     ARRAY_CONSTRAIN('Suivi'!F2:O,MATCH(2,1/('Suivi'!A2:A<>""),1),10 ) ,"|")

)

Tableau de planning de suivi (étape 3)

Nous voici à l’étape finale de la création d’un tableau de planning de suivi dans Google Sheets. Nous allons modifier la formule ci-dessus dans la cellule A1 de l’onglet « Sch ».

Lorsque vous utilisez la fonction SPLIT pour séparer la sortie de la formule ci-dessus (en utilisant le pipe comme délimiteur dans la fonction SPLIT), vous obtiendrez le tableau de suivi des factures ou des rendez-vous dans une formule non formatée.

=Query(
    ArrayFormula(
        SPLIT(
            APLATIR(ARRAY_CONSTRAIN('Suivi'!A2:A,MATCH(2,1/('Suivi'!A2:A<>""),1),1 )&amp;"|"&amp; 
                            ARRAY_CONSTRAIN('Suivi'!B2:B,MATCH(2,1/('Suivi'!A2:A<>""),1),1 )&amp;"|"&amp; 
                            ARRAY_CONSTRAIN('Suivi'!C2:C,MATCH(2,1/('Suivi'!A2:A<>""),1),1 )&amp;"|"&amp; 
                            ARRAY_CONSTRAIN('Suivi'!F2:O,MATCH(2,1/('Suivi'!A2:A<>""),1),10 ) ,"|")
        ),
        "|"
    ),
    "Select * where Col4 is not null"
)

Cette formule peut générer de nombreuses cellules vides dans la 4e colonne. En utilisant la fonction QUERY, nous pouvons les supprimer. Voici la formule finale dans la cellule A1 de l’onglet « Sch » :

=Query(
    ArrayFormula(
        SPLIT(
            APLATIR(ARRAY_CONSTRAIN('Suivi'!A2:A,MATCH(2,1/('Suivi'!A2:A<>""),1),1 )&amp;"|"&amp; 
                            ARRAY_CONSTRAIN('Suivi'!B2:B,MATCH(2,1/('Suivi'!A2:A<>""),1),1 )&amp;"|"&amp; 
                            ARRAY_CONSTRAIN('Suivi'!C2:C,MATCH(2,1/('Suivi'!A2:A<>""),1),1 )&amp;"|"&amp; 
                            ARRAY_CONSTRAIN('Suivi'!F2:O,MATCH(2,1/('Suivi'!A2:A<>""),1),10 ) ,"|")
        ),
        "|"
    ),
    "Select * where Col4 is not null"
)

De cette façon, vous pouvez créer un tableau de planning de suivi des factures ou rendez-vous dans Google Sheets.

Remarque : Les dates dans les colonnes A et D peuvent apparaître sous forme de valeurs de date. Dans ce cas, sélectionnez ces colonnes et cliquez sur le menu Format > Nombre > Date.

Et voilà ! Vous avez maintenant toutes les clés en main pour créer un tableau de planning de suivi des factures ou rendez-vous dans Google Sheets. Profitez-en bien !

_Exemple_Sheet13620

Ressources :

GiGiKing

Articles en lien