Budget de personnel hopital

Question

En fait je travaille dans un hôpital, ou je dois créer un fichier mensuel et annuel entre le temps travaillé par service et les salaires perçus. Ou ça se complique c'est en fonction de 2 budgets, avec 30 services, des grades et statuts différents... Pour le faire j'ai 1 fichier chaque mois en base pour les salaires et un autre pour les ETP rémunérés. Mon problème est pour réunir les 2 car j'ai un gros mouvement de personnel contractuel qui change chaque mois, avec des changements de service, ou de grades. J'ai essayé de faire un fichier condensé pour vous montrer ce que j'ai, et vu les nombreux exemples que j'ai vu, vous êtes vraiment doués. Je pense que certaines personnes pourront m'aider ou m'expliquer comment arriver au résultat. Merci à vous. Marie claude...

Lomanic · Answer

La solution à ton pb réside probablement dans l'utilisation de formules matricielles ou l'équivalent avec SOMMEPROD(). Je n'ai pas compris dans quelle feuille se trouveront les totaux. Peux-tu faire un exemple de calcul sur une cellule ? A mon avis, les formules seront plus faciles à rédiger, à modifier et à comprendre si les codes UF 7303 EHPAD hermy - DEPENDAN sont ventilés en 4 colonnes (car ils vont servir de critère pour les additions sauf erreur de ma part). Question annexe : les fichier de données est-il alimenté par un export du logiciel de paye au format TXT ou y-a-t-il saisie manuelle des montants ? A+...

Lomanic · Answer

J'ai regardé d'un peu plus près ton fichier. Tu parles de budget mais tu indiques l'année 2009 dans la feuille ETPannée, est-il possible d'en conclure qu'il va falloir traiter 2009 pour le comparer mensuellement à 2010 ou s'agit-il d'un vrai budget prévisionnel pour 2010 ? Tu n'as pas répondu à ma question sur le mode d'alimentation de ton fichier. Je pense que le premier problème à résoudre n'est pas les formules d'Excel (Amadéus prête toujours son concours à partir du moment le demandeur n'est pas passif, ce que je trouve parfaitement normal), mais, compte tenu des volumes que tu indiques (350 salariés et jusqu'à 1000 lignes par mois dans les feuilles Salxx), je te propose de réfléchir au moyen de ne pas trop te fatiguer et de ne pas t'énerver à rechercher des erreurs de saisie inévitables avec de tels volumes en utilisant les possibiltés de créer des fichiers au format .TXT à partir du logiciel de paie, puis en les tripatouillant allègrèment sous Excel. Comme disait le maître de stage d'une collègue, "Avant de balayer la cour, il faut regarder où se trouvent les plus gros tas de feuilles et où le vent les pousse". Un autre avantage est que tu auras toujours la même structure de fichier, ce qui ne me semble pas le cas dans certaines cellules : dans les feuilles Salxx, colonne B, il n'y a aucun espace entre le chiffre et le nom du salarié en B8 et B9, alors qu'il y en a un dans les cellules suivantes B10 à B21. A quoi ce chiffre sert-il ? Comment est-il généré ? Est-il indispensable ? A titre professionnel, j'utilise un logiciel de planning qui me sert à importer environ 1700 variables (pour environ 220 salariés) dans le logiciel de paye ainsi qu'à générer un fichier pour l'importation des ventes dans le logiciel de comptabilité (après quelques traitements sous Excel), puis j'exporte quelques fichiers du logiciel de paye dans celui de comptabilité et dans celui de la banque (pour les virements), etc... Je suis un fervent adepte des l'exportation de fichiers TXT, technique que les comptables ignorent totalement par méconnaissance des logiciels qu'ils utilisent ainsi que des possibilités d'Excel à lire ces fichiers, et par une maîtrise insuffisante des formules de calcul un peu sophistiquées. Cette suggestion te déconcertera sans doute car ce n'était probablement pas ce que tu attendais dans un premier temps, mais je ne la proposerais pas s'il y avait une dizaine de salariés. A+...

FINDRH · Answer

Pour recoller les différents fichiers il faut avoir un dénominateur commun, en l'occurence le N° de matricule de l'agent; tout part de là. Quelle filière info est utilisée ? Comment les données sont elles extraites ( excel direct ou via BO ?), d'un fichier de paye après paye du mois, ou d'un module effectif à la fin d'un mois achevé ?

Le fichier doit être suffisamment détaillé pour que les données soient intéressantes et exploitables

Il faut que les temps agents soient bien affectés proportionnellement aux UF

les notions de statut ne sont pas neutres

il faut compléter les données par une colonne mois

La formule de recollement, à partir du moment où le matricule figure dans les deux fichiers est simple:

les données sont placées sur deux feuilles différentes d'un même classeur, une arrivée que l'on consolide, et une départ où l'on puise les infos
feuille départ:

on nomme les colonnes de manière unique, on sélectionne le tout et on trie par ordre croissant en fonction du matricule ( colonne intitulée Mat par exemple);

On veut récupérer les infos correspondantes de la colonne ETP par exemple

feuille arrivée:
on admet que la première ligne contient les entetes, le matricule étant en colonne 1, le premier sera donc en cellule A2
on se place dans la première cellule vide de la ligne 2 du tableau, on utilisera la fonction "recherche" associée à "esterreur(equiv)" pour éliminer les erreurs : exemple:

= si(esterreur(equiv(A2;MAT;0));0;recherche(a2;MAT;ETP))

on recopie vers le bas et le tour est joué, toute info de la feuille départ peut être consolidée sur la feuille arrivée

faire un copier collage special valeur pour figer les résultats et agréger les données mensuelles en un tableau annuel

Je fais comme cela pour plein de tableaux pour du controle de gestion hospitalier

Si tu es vraiement coincé, envoye moi les trois premières lignes de chaque tableau anonymisé ( évidemment) et je te fais ta formule !)

Bon courage

Tchibo

Lomanic · Answer

Je ne connais pas HEXAGONE D04 qui me semble spécifique au centres hospitaliers ni IMPROMPTU.

Je te conseille de te renseigner auprès de l'éditeur des logiciels si vous avez un contrat d'assistance (le niveau de connaissance des hot-lines est inégal suivant l'expérience du conseiller et il m'est arrivé de poser plusieurs fois la même question jusqu'à satisfaction), à défaut de consulter la documentation (c'est parfois assez succinct et certaines manip mal expliquées).

Il n'est pas nécessaire d'obtenir directement un fichier au format Excel car les fichiers au format .TXT ou CSV sont utilisables par Excel sans pb même si c'est moins pratique à ouvrir. Je ne suis pas sûr qu'il n'existe pas une manip pour obtenir un fichier comparable aux documents papier. Dans ce cas, il faudrait scanner le document avec un logiciel OCR mais cela peut-être assez pénible de le rectifier sous Excel car ce n'est pas toujours extraordinaire comme résultat. Cela serait la solution ultime en dernier recours, mais j'ai du mal à croire qu'il sagisse de la seule compte tenu de mon expérience chez différents clients.

Lors de l'ouverture d'un fichier TXT, Excel propose notamment de spécifier un format pour chaque colonne : je te conseille d'utiliser le format "Texte" pour le numéro matricule des salariés pour conserver les éventuels zéros non significatifs (comme dans 00021). Le numéro de Sécurité Sociale peut aussi être utilisé avec ce format sinon Excel l'écrit sous forme scientifique avec des puissances de 10, ce qui le rend illisible.

Qoiqu'il en soit, je pense qu'il existe une solution pour exporter les données sans trop de difficulté (certains logiciels permettent même à l'utilisateur de créer son fichier sur mesure comme semble le faire Impromptu et d'enregistrer un modèle des champs du fichier à créer pour obtenir la même structure de fichier à chaque extraction).

A+

FINDRH · Answer

Ne sachant pas si tu as reçu mes pièces jointes je te renvoies ta feuille de calcul à tout hasard. J'ai collatonné tes infos sur une seule feuille en supposant que tu n'utilise que le total paye. J'ai ensuite reventilé les montants sur les UF en tenant compte du nombre de fois où la somme est reprise. Cordialement...

Lomanic · Answer

A priori le fichier .csv me paraît tout-à-fait exploitable. Il faut seulement utiliser la commande Ctrl+h pour remplacer le point par la virgule des colonnes I à R, à moins qu'il s'agisse d'une spécificité de ma configuration, mais peu importe (certains logiciels demandent de spécifier le séparateur décimal au moment de l'exportation). Dans ton premier message, tu indiquais vouloir calculer le total des salaires bruts par service. A quelle colonne cela correpond-il sur le fichier joint ? J'ai l'impression que le logiciel a déjà additionné les salaires et les effectifs par fonction. Cela ne risque-t-il pas d'être incompatible avec l'objectif recherché d'un total par service et non par fonction ? Une question de commisssaire aux comptes : as-tu contrôlé la fiabilité de ton exportation avec les journal des salaires et les montants comptabilisés en compte 64 ? C'est un préalable indispensable car les exportations réservent parfois des surprises désagréables. Ci-joint le fichier csv au format xls A+...

Lomanic · Answer

1) Contrôle de l'exportation Je ne pense pas qu'il soit nécessaire de contrôler les feuilles de paye une par une, sauf peut-être la première fois et encore. Je préconisais de contrôler globalement le total des salaires bruts du fichier exporté avec le mondant du journal des salaires et le montant comptabilisé en charges. Le contrôle doit-être fait mensuellement et en cumul depuis le début de l'exercice pour être sûr qu'un petit malin (ou une petite maligne en cette journée de la femme) n'a pas modifié les écritures des mois antérieurs, sans doute à juste titre. Il est sage de te constituer un dossier avec ces documents comptables (sur papier ou au format pdf). 2) Caractéristiques du fichier exporté Je n'ai pas bien compris les pb auxquels tu es confrontée, mais de toute évidence, le fichier exporté n’est pas simple à exploiter et ce n’est absolument pas normal. Il y a probablement des options à utiliser lors de l'exportation par le logiciel Impromptu ou lors de l’ouverture par Excel. A l'ouverture du fichier CSV sous EXCEL, ce dernier l'ouvre immédiatement mais toutes les données sont dans une seule colonne (selon un test avec un fichier CSV téléchargé à partir du site de la banque), ce qu'il ne fait avec un fichier TXT. Il faut alors que j'utilise la commande Données, Convertir pour ventiler les différentes colonnes. Pour éviter cette étape, lors de l'exportation, j'impose l'extension TXT au fichier créé (il est possible de modifier après coup l'extension avec l'explorateur sans faire attention à l'avertissement de Windows). Lors de l'ouverture d'un fichier .TXT, Excel ouvre le fichier en plusieurs étapes : au cours de l'étape 3, il est peut être préférable d'indiquer des formats de colonnes spécifiques et de définir le séparateur décimal en cliquant dans le bouton "Avancé". Sinon, il est possible de modifier le point en virgule de toute une colonne avec la commande Ctrl+h. Indique-moi plus précisément les manœuvres qu’Excel t’impose pour que les formules d’Excel soient opérationnelles. Mon expérience des exportations-importations de fichiers avec différents logiciels en utilisant Excel comme passerelle de l’un à l’autre me rend optimiste quant à la résolution de tes difficultés. A+ N'hésite pas à m'appeler au numéro indiqué dans le courrier envoyé par la messagerie du site...

FINDRH · Answer

A l'expérience, le fichier doit pouvoir être passé sous un autre format ; n'y a t il pas un choix de format d'export, notamment txt séparateur ... au moment ou tu nomme le fichier d'exportation ? La méthode préconisée auparavant est la bonne et permet de scinder les zones très rapidement à l'ouverture Les transformations d'alpha en numérique sont automatisables à condition que les colonnes soient toujours ordonnées de la même manière. Avec un bout de fichier et les colonnes dans l'ordre, même quelques lignes issues d'impromptu( s'il y a des noms les remplacer par aa entre 2 séparateurs... il y aura surement moyen de régler cela. Cordialement FINDRH...