Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

C'est le début de l'hiver et il fait froid ! Pas de chauffage dans la maison ? Bougez simplement, tapez du pied, frottez-vous les mains et vous vous sentirez au chaud. C’est le début de l’hiver et une autre année touche à sa fin ! Pas encore d'augmentation de salaire ? Laissez votre montre bouger différemment, se mettre à jour simultanément et s'afficher de manière dynamique. Non seulement cela vous évitera des ennuis, mais votre patron aimera aussi ouvrir un "salaire"...

Premier coup : Impressionnez, le graphique mobile va le faire. impressionnez votre patron

Patron Il a l'habitude de voir les tableaux simples (ci-dessous) que vous avez rapportés, et maintenant vous lui montrez soudainement un graphique (ci-dessous) qui peut être interrogé dynamiquement. Pensez-vous que cela peut toucher le cœur difficile du patron. ? Pouvez-vous surprendre votre patron avec une récompense ?

Votre rapport actuel                                                                                                                                                                    Votre rapport de demain

Comment réaliser ce changement ? C'est très simple, il suffit de créer des graphiques dynamiques.

Le graphique dynamique signifie que le patron sélectionne différentes zones et que le graphique affiche différentes données. Il y a trois étapes pour y parvenir : une étape consiste à créer un menu déroulant que le patron peut choisir, une étape consiste à créer une zone de données qui change dynamiquement en fonction de la sélection et une étape consiste à insérer un graphique basé sur la zone de données dynamiques.

Les zones de données dynamiques sont généralement implémentées avec la fonction RECHERCHEV, mais aujourd'hui, nous adoptons une approche différente. Nous utilisons la fonction OFFSET pour compléter la zone de données dynamiques.

Étape 1 : Faites une sélection déroulante

1. Sélectionnez la cellule J1 et cliquez sur "Vérification des données" sous l'onglet "Données".

2. Dans l'option "Paramètres" en bas de la fenêtre "Validation des données", sélectionnez "Série" pour "Autoriser", et sélectionnez les cellules où se trouvent les cinq zones de vente comme source "=$ 2$A : 6$A$". Cliquez sur OK.

Étape 2 : Créer une zone de données auxiliaires mise à jour dynamiquement

3. L'étape suivante consiste à mettre à jour dynamiquement le graphique en fonction de la valeur de la cellule J1. J1 sélectionne « Zone de Pékin ». Entrez "=OFFSET(B1,MATCH($J$1,$A$2:$A$6,0),0)" dans la cellule B7. Ensuite, la formule se remplit à droite jusqu'à la cellule G7. De cette manière, les cellules B7:G7 renvoient le volume des ventes dans la région de Pékin de janvier à juin.

Analyse :

Utilisez OFFSET pour prendre "B1" comme système de référence. Le nombre de lignes de décalage doit utiliser la fonction MATCH pour obtenir la position de $J$1 à $A$2 :$A$6. le numéro de colonne de décalage est 0. Pas de décalage. Comme le montre la figure J1, la valeur est "Zone de Pékin" et la position de $A$2:$A$6 est 1. La valeur renvoyée par OFFSET est un décalage de référence d'une ligne vers le bas avec "B1" comme système de référence. De cette façon, à mesure que la zone sélectionnée $J$1 continue de changer, les cellules B7:G7 obtiendront les données de ventes de la zone correspondante.

4. Définissez ensuite les données de ligne moyennes et entrez "=AVERAGE($B$7:$G$7)" dans la cellule B8 pour obtenir la moyenne de $B$7 :$G$7. Ensuite, la formule se remplit à droite jusqu'à la cellule G8. Si la zone sélectionnée $J$1 change, alors $B$7 :$G$7 change et la valeur moyenne changera également en conséquence.

Étape 3 : Créer un graphique

5. Nous créons un graphique basé sur les lignes auxiliaires définies. Sélectionnez l'en-tête B1: G1 et la zone des lignes auxiliaires B7: G8 et cliquez sur "Graphique à colonnes bidimensionnel" dans le groupe "Graphique" sous l'onglet "Insérer".

6. Entrez "=J1&"Sales Data"" dans la cellule K1.

Cliquez sur la zone de titre du graphique et saisissez "=Sheet2!$K$1" dans la barre d'édition, afin que le titre du graphique et la zone de vérification des données soient mis à jour simultanément.

7. Cliquez sur « Modifier le type de graphique » sous l'onglet « Conception » sous « Outils de graphique ».

8. Dans la fenêtre « Modifier le type de graphique », cliquez sur « Combinaison » sous l'option « Tous les graphiques » et modifiez la série où se trouve la valeur moyenne en « Graphique linéaire ».

9. Enfin, supprimez la légende du graphique, changez la police des cellules de données auxiliaires B7:G8 et K1 en blanche et invisible, et vous avez terminé.

Deuxième action : Liaison, pour que le tableau croisé dynamique et la source de données soient mis à jour simultanément

Si le tableau croisé dynamique souhaite être mis à jour de manière synchrone avec la source de données, je vous ai présenté que le super tableau peut être réalisé auparavant. De plus, nous pouvons également utiliser OFFSET. Comme le montre la figure, le tableau croisé dynamique de droite est inséré en fonction de la source de données de gauche.

Maintenant, nous avons besoin qu'une fois la source de données mise à jour, le tableau croisé dynamique puisse également être mis à jour simultanément, comme suit :

Étape 1 : Définir le nom

1 Cliquez sur "Défini" sous. l'onglet "Formule" "Définir le nom" dans le groupe d'options Nom.

2. Dans la fenêtre "Nouveau Nom", saisissez "Données" dans la colonne "Nom" et la formule suivante dans la "Position de référence"

=OFFSET(Sheet1!$A$1,,,COUNTA( Sheet1!$A: $A),COUNTA(Sheet1!$1:$1))

Analyse :

"Sheet1" est la feuille de calcul où se trouvent les données. La fonction prend "Sheet1!$A$1" comme système de référence, ne décale pas (les lignes et les colonnes de décalage sont vides) et renvoie dynamiquement l'intégralité des données de la table. COUNTA(Sheet1!$A:$A) est utilisé pour obtenir le nombre de lignes de données du tableau, et COUNTA(Sheet1!$1:$1) est utilisé pour obtenir le nombre de colonnes de données du tableau. Les résultats qu'ils obtiennent sont dynamiques et changent à mesure que le nombre de lignes et de colonnes du tableau augmente ou diminue.

Étape 2 : Modifiez la source de données

3. Cliquez sur n'importe quelle cellule du tableau croisé dynamique et l'outil "Tableau croisé dynamique" apparaîtra. Cliquez ensuite sur « Modifier la source de données » dans l'onglet « Analyse » sous « Outils de tableau croisé dynamique ».

4. Dans la fenêtre « Modifier la source de données du tableau croisé dynamique », remplacez « Tableau/Plage » par le nom que vous venez de définir « Données » et cliquez sur OK.

Étape 3 : Actualiser la synchronisation

5. Ensuite, ajoutez des données dans la dernière ligne de données.

6. Faites un clic droit sur le tableau croisé dynamique et sélectionnez la commande "Actualiser", et le tableau croisé dynamique sera mis à jour.

Grâce à la fonction Offset, nous avons mis en place deux "actions". La première action consiste à utiliser des graphiques dynamiques pour impressionner le patron et faciliter le "salaire". La deuxième action consiste à lier le tableau croisé dynamique avec la source de données. , ce qui nous facilite la tâche. Ok, dépêchez-vous et faites bouger votre formulaire ! L'hiver arrive, même si les mains et les pieds froids ne peuvent être évités, mais une augmentation de salaire vous réchauffera le cœur !

Recommandations d'apprentissage associées : Tutoriel Excel

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!