Graphique évolution automatique

%3CLINGO-SUB%20id%3D%22lingo-sub-2603285%22%20slang%3D%22fr-FR%22%3EAuto-evolution%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2603285%22%20slang%3D%22fr-FR%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20an%20excel%20page%2C%20I%20gathered%20HR%20data%20(FTEs%2C%20vacancies%2C%20recruitment%20etc.)%20that%20evolve%20and%20are%20updated%20every%20month.%20I%20would%20like%20to%20make%20an%20automatic%20chart%20that%20without%20me%20having%20to%20copy%20paste%20or%20create%20every%20month%20my%20cells%20take%20into%20account%20old%20data%20and%20updates%20to%20create%20curves%20for%20me.%20How%20do%20I%20proceed%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2603285%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2606241%22%20slang%3D%22en-US%22%3ERe%3A%20Graphique%20%C3%A9volution%20automatique%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2606241%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1119165%22%20target%3D%22_blank%22%3E%40zberriet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20question%20is%20not%20easily%20answered.%20Years%20ago%20(around%2030%20years%20ago)%20I%20had%20responsibility%20for%20the%20HR%20and%20Payroll%20database%20of%20a%20major%20corporation.%20We%20were%20charged%20with%20producing%20a%20monthly%20%22headcount%22%20report%2C%20by%20location%2C%20by%20employment%20classification%20(FTE%2C%20part-time%2C%20temp)%20showing%20changes%20from%20the%20prior%20month.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20did%20it%20by%20means%20of%20a%20fresh%20query%20each%20month%20to%20the%20mainframe%20database%2C%20created%20in%20the%20SQL%20query%20language%3B%20that%20exported%20a%20set%20of%20data%20in%20CSV%20format%2C%20as%20I%20recall%2C%20and%20I%20then%20created%20a%20number%20of%20Excel%20functions%20that%20produced%20the%20summary.%20It's%20so%20long%20ago%20now%20that%20I%20don't%20recall%20any%20of%20the%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20don't%20tell%20us%20enough%20about%20the%20way%20you%20get%20the%20data%20in%20the%20first%20place.%20So%20one%20can%20only%20make%20general%20suggestions.%20One%20way%20to%20approach%20it%20might%20be%3A%3C%2FP%3E%3CUL%3E%3CLI%3ECreate%20an%20Excel%20table%20consisting%20of%20a%20record%20for%20each%20active%20employee%20this%20month%2C%20showing%20status%2C%20location%20(whatever%20the%20%22slices%22%20are%20that%20you%20have%20to%20report)%3C%2FLI%3E%3CLI%3EInclude%20in%20that%20table%20the%20same%20data%20for%20last%20month.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3ECreate%20a%20summary%20report%20that%20uses%20such%20functions%20as%20%3CSTRONG%3ESUMIF%3C%2FSTRONG%3E%26nbsp%3Bor%20%3CSTRONG%3ESUMPRODUCT%3C%2FSTRONG%3E%20to%20produce%20the%20counts%20for%20employees%20in%20each%20%22slice%22%20or%20intersection%20of%20characteristics%20that%20you%20are%20expected%20to%20report.%3C%2FLI%3E%3CLI%3EAs%20an%20alternative%2C%20you%20might%20also%20be%20able%20to%20employ%20a%20%3CSTRONG%3EPivot%20Table%3C%2FSTRONG%3E%20(and%20associated%20charting%20capability)%20to%20summarize%20the%20data%20in%20the%20table.%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Bonjour à tous, 

 

Sur un page excel, j'ai rassemblé des données RH (ETP, poste vacant, recrutement etc) qui évoluent et sont mises à jour chaque mois. Je souhaiterai faire un graphique automatique qui sans que j'ai à copier coller ou créer chaque mois mes cellules prennent en compte les anciennes données et les mises à jours pour me créer des courbes. Comment puis-je procéder ? 

Merci ! 

1 Reply

@zberriet 

 

Your question is not easily answered. Years ago (around 30 years ago) I had responsibility for the HR and Payroll database of a major corporation. We were charged with producing a monthly "headcount" report, by location, by employment classification (FTE, part-time, temp) showing changes from the prior month. 

 

We did it by means of a fresh query each month to the mainframe database, created in the SQL query language; that exported a set of data in CSV format, as I recall, and I then created a number of Excel functions that produced the summary. It's so long ago now that I don't recall any of the details.

 

You don't tell us enough about the way you get the data in the first place. So one can only make general suggestions. One way to approach it might be:

  • Create an Excel table consisting of a record for each active employee this month, showing status, location (whatever the "slices" are that you have to report)
  • Include in that table the same data for last month.

 

  • Create a summary report that uses such functions as SUMIF or SUMPRODUCT to produce the counts for employees in each "slice" or intersection of characteristics that you are expected to report.
  • As an alternative, you might also be able to employ a Pivot Table (and associated charting capability) to summarize the data in the table.