Maison > Les sujets > excel > Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

青灯夜游
Libérer: 2023-02-09 19:55:08
avant
1777 Les gens l'ont consulté

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"...

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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".

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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.

Tableau Excel dapprentissage de table dynamique éclectique OFFSET

É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.

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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.

Tableau Excel dapprentissage de table dynamique éclectique OFFSET公众号

É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".

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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.

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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 ».

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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é.

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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.

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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 :

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

É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.

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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))

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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.

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

É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 ».

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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.

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

Étape 3 : Actualiser la synchronisation

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

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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

Tableau Excel d'apprentissage de table dynamique éclectique OFFSET

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!

Étiquettes associées:
source:itblw.com
Déclaration de ce site Web
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Tutoriels populaires
Plus>
Derniers téléchargements
Plus>
effets Web
Code source du site Web
Matériel du site Web
Modèle frontal