Formule =sierreur(index....) avec 2 conditions

Signaler
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020
-
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020
-
Bonjour,

Comment faire apparaitre dans le même tableau et avec 1 seule formule le résultat de ces 2 formules. parce que individuellement ca fonctionne.

Y a t il une solution pour avoir une seule formule qui ferait le tri ? parce que cela fait plusieurs heures que je teste des choses sans succès.

quand je saisis ces 2 formules ca marche de façon séparée :

=SIERREUR(INDEX(Feuil1!$D:$D;AGREGAT(15;6;(1/(Tableau1[BANQUE]="BANQUE1"))*LIGNE(Tableau1[BANQUE]);LIGNE(A1)));"") --------> ok ca marche

=SIERREUR(INDEX(Feuil1!$D:$D;AGREGAT(15;6;(1/(Tableau1[BANQUE]="BANQUE2"))*LIGNE(Tableau1[BANQUE]);LIGNE(A1)));"") -------------> OK ca marche aussi

merci d'avance ...et encore merci à danielc0 et à via55 qui m'ont respectivement beaucoup aidés ces derniers mois.

cordialement

glaieul29



Configuration: Windows / Internet Explorer 11.0

7 réponses

Messages postés
12625
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
14 septembre 2020
2 008
Bonjour glaieul

De rien !

Si tu veux enchainer les 2 recherches :
=SIERREUR(INDEX(Feuil1!$D:$D;AGREGAT(15;6;(1/(Tableau1[BANQUE]="BANQUE1"))*LIGNE(Tableau1[BANQUE]);LIGNE(A1)));SIERREUR(INDEX(Feuil1!$D:$D;AGREGAT(15;6;(1/(Tableau1[BANQUE]="BANQUE2"))*LIGNE(Tableau1[BANQUE]);LIGNE(A1)));"")

Cdlmnt
Via
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

saut via,
un plaisir de te relire....surtout que tu as encore tout bon !!!

en relisant ta formule, je pense que j'y étais presque :-) mais des messages d'arguments en + ou en -, .... j'aurais dû conserver tous mes essais. A la fin on ne sait plus ou on en est dans les modifs et c'est pourquoi je me suis résolus à demander de l'aide.

merci encore

et à plus

glaieul
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

re,
un petit truc que je n'arrive pas à résoudre.
tout est nickel sauf que j'ai chronologiquement tous les "banque1" et après tous les "banque2".

comment je pourrais faire pour que le résultat reste chronologique (du plus vieux au plus récent) quelque soit la banque. les lignes "banque1" et "banque2" seront donc mélangés. (même un tri forcé ne marche pas).

cdt

glaieul
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

re,

j'ai repris ta formule, mais après pointage, je m’aperçois qu'il y a vraiment un truc "bizaroïde" qui se passe.

1) pour chaque formule testée individuellement toutes les sommes de banque1 et de banque2 sont reprises.

2) en enchainant les 2 formules toutes les sommes de BANQUE1 sont reprises mais pas toutes celles de banque2.

3) si j'inverse l'ordre de banque1 et banque2 dans la formule (banque2 en 1er et banque1 en 2e), il n'y a que les lignes de banque2 qui sont reprises.

j'espère que je suis assez clair. Vois tu d’où cela peut venir ?
Je sais que le mieux serait que je t'envoie un fichier mais il faudrait que j'essaie de "l'anonymiser" un peu ou que je te l'envoie en message privé.

cdt

glaieul
Messages postés
12625
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
14 septembre 2020
2 008
Bonjour

Oui ma formule recherche d'abord dans banque1 et si pas trouvé dans banque2, c'est ce que je croyais que tu voulais mais non tu veux toutes les entrées concernant les deux apparemment
Oui prépare moi un fichier, poste le sur mon-partage.fr (tu peux mettre un mot de passe) et envoie moi le lien en mp avec éventuellement le mot de passe

J'ai noté aussi ta demande de sortie chronologique, je verrai comment c'est possible mais il faudra peut être une macro

Cdlmnt
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

bonjour via,
j'ai vainement essayé de me débrouiller tout seul.

Je te joints un fichier simplifié et anonymisé au maximum.

https://www.cjoint.com/c/IKpe3irkHIz

Je souhaiterais donc regrouper les lignes banque1 et af1 dans un mm onglet. (j'ai créer banque1+af1 à cet effet).

Je ne comprends vraiment pas pourquoi ça marche individuellement pour chaque onglet et pas pour l'onglet "banque1+af1).

Ce que j'ai remarqué c'est que l'onglet "banque1+af1" ne dépasse jamais le nombre de ligne de celui qui en a le plus. exemple :
af1 = 23 lignes
banque1 =7 lignes
"banque1+af1" = 23 lignes (au lieu des 30).

Concernant l'ordre chronologique, si cela est trop compliqué je pense que ce ne soit pas nécessaire de te casser la tête. Il n'y a aucun intérêt finalement. c'est très bien regroupé en 2.

Mais si jamais tu décidais de le faire aussi de façon chronologique (pour le fun, la réflexion que cela nécessitera.....) pourrais-tu le faire en supplément, dans un nouvel onglet, pour que je puisse comparer les 2 onglets et essayer de comprendre.

Merci encore

cdt

glaieuls
Messages postés
12625
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
14 septembre 2020
2 008
Bonjour

https://www.cjoint.com/c/IKpmwDBMZW2

Plusieurs possibilités :
- feuille banque1 + af1 : sans utiliser la matricielle AGREGAT puisqu'on déjà des listes triées mais en utilisant INDIRECT pour reconstituer l'adresse de l'info dans l'une ou l'autre feuille selon la ligne où on se trouve
- feuille banque1 + af1 + tri chrono : même chose mais en rajoutant une colonne de comptage pour obtenir à côté le tableau trié chronologiquement
_- feuille tri chrono banque1 af1 : en ayant rajouté une colonne de comptage en feuille 1; directement le tableau trié chronologiquement sans avoir besoin des feuilles Banque 1 et AF 1

Cdlmnt
Via
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

re,
je comprends pas tout je dois te l'avouer.
La formule agregat c'est notre ami danielc0 qui me l'avais sortie de son chapeau et j'ai essayé de la triturer dans tous les sens pour voir ce que ca donnait.

pour le reste fait au mieux et au plus facile.

merci d'avance

glaieul
Messages postés
12625
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
14 septembre 2020
2 008
C'est ce que j'ai fait !
ouvres le fichier que je t"ai posté et dont je t'ai mis le lien dans mon précédent message je t'ai mis les 3 possibilités, tu choisiras ce qui te convient le mieux

Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

excuse moi je n'avais pas vu que tu avais posté un fichier !!!
je regarde de suite.

cdt

glaieul
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

re,
c'est super !! c'est une autre approche apparemment, mais ça reste toujours du chinois pour moi.

Si je fais une copie de ta formule colonne G, pour la colonne D "travaux" ... ça ne marche pas !! (j'ai pourtant modifier le "G" en "d" dans ta formule !!).

J'avais pas voulu d’embêter avec les autres colonnes. je m’étais dit que je pourrais faire des copies de formules mais ça n'a pas l'air aussi simple.

Il est ou le pb avec mes copies de formules ?

cdt

glaieul
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

re,
complétement c.. le mec !! c'est moi qui ait effacé le contenu des colonnes !!! normal que ça n'affiche rien lol

merci
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

re,
dans le dernier onglet comment on peut faire pour ne pas voir apparaitre de 0 quand il n'y a rien de saisi dans une colonne le tableau de la feuil1. ?

si Feuil1 D18 je saisi : sous traitance ......> ça apparaitra dans le dernier onglet. Si D18 vide....> 0 apparait

merci encore
Messages postés
12625
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
14 septembre 2020
2 008
Pour le 0 le plus simple est d'éviter de toucher aux formules mais de masquer les 0 dans la colonne par une mise en forme conditionnelle écriture blanche pour les valeurs = 0

Sinon il faut modifier la formule ainsi :
=SI(SIERREUR(INDEX(Tableau1[TRAVAUX];EQUIV(LIGNES(G$9:G9);Tableau1[x];0));"")=0;"";SIERREUR(INDEX(Tableau1[TRAVAUX];EQUIV(LIGNES(G$9:G9);Tableau1[x];0));""))

Explication de la formule INDEX EQUIV :
INDEX va chercher une valeur dans la matrice Tableau1[TRAVAUX] à la ligne trouvée par EQUIV
EQUIV donne le rang dans la matrice Tableau1[x] du nombre déterminé par LIGNES(G$9:G9)
LIGNES(G$9:G9) donnant lui même le nombre de lignes dans la plage depuis G9
ainsi si on est en ligne11, LIGNES(G$9:G11) renvoie 3, dans la feuille 1 en colonne J 3 se trouve en ligne 21donc EQUIV renvoie 21et INDEX renvoie alors la valeur de D21

Cdlmnt
Via

Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

bonjour via,
tout marche nickel j'ai même testé la formule "0;-0;;@" pour éviter l'affichage des 0.

juste pour info mais rien de grave puisque tout marche nickel :
pour voir la différence, j'ai voulu tester ta formule index equiv... mais excel me renvoie un message d'erreur sur tableau1[x]... ? est-ce moi qui ait mal fait quelque chose ...

tu es un autodidacte d'excel ou tu as eu des cours ?
je maitrise plus les formules mathématique (basique), mais pour les tris et les recherches je suis vraiment une bille.
Tu m'as sorti une belle épine et me montrant les capacités d'excel en ce domaine.

Merci encore

à plus.

glaieul
Messages postés
12625
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
14 septembre 2020
2 008
Bonjour glaieul

La formule INDEX INQUIV existe déjà dans le dernier fichier que je t'avais envoyé dans la dernière feuille tri chrono ... et fonctionne bien

La formule de mon dernier post rajoute un SI .... = 0 pour traiter le cas du 0 en colonne D et fonctionne aussi, je viens de la retester sur mon fichier pour être sûr (une erreur de recopie est vite faite !)
Si tu as bien nommé x la colonne J de feuille 1 il ne devrait pas y avoir de problème sinon il suffit de remplacer dans ma formule le x par l'autre nom que tu aurais attribué à cette colonne J
Je ne peux guère en dire plus sans voir ton fichier

Enfin pour répondre à ta question je suis parfaitement autodidacte, au début comme pour toi INDEX EQUIV c'était de l'hébreu pour moi mais c'est une combinaison tellement utile à connaître car elle permet de retrouver une valeur dans un tableau d'après une autre quelque soit sa colonne ou sa ligne alors que les RECHERCHEV et RECHERCHEH exigent que la valeur permettant la recherche soit située obligatoirement en 1ere colonne ou en 1ere ligne

A plus
Via
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

Bonjour via

Merci pour tes explications de texte.

C'est normal que ça ne marchait pas chez moi car tu as bien deviné que je n'avais effectivement pas modifié "x" de ta formule.

le but de mon appel à l'aide concerne cette fois ci l'impression. (ft paysage)

après avoir effectuer les différentes modif pour que les tableaux tiennent sur une page ft paysage. (suppression de certaines colonnes, diminution de leur largeur...)

les différents onglets me proposent :13,10, voir 17 pages pour banque1+af1+tri chrono pages .

Il n'y a que feuil1 qui serait OK.

Comment faire pour éviter l'impression des pages blanches.

Peut on modifier le nombre de pages actives (menu impression) ?

encore merci

à plus

glaieul
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

re,

alors ce que j'ai fait :

j'ai été dans le petit picto en bas à droitre "aperçu des sauts de page", j'ai supprimé les pages vierges pour ne laisser que celles qui étaient écrites.

J'espère cependant que lorsque qu'il y aura suffisamment d'ecritures pour céer une nouvelle page AF1 AF2....., que celle-ci soit inclue dans l'impression.

A plus

glaieuls
Messages postés
12625
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
14 septembre 2020
2 008
Bonjour glaieul

Je ne sais pas de quel picto tu parles
Il faut avant tout pour chaque feuille définir des zones d'impression pour éviter l'impression de pages blanches (Dans le ruban Mise en page - ZoneImpr)

Si les tableaux peuvent varier en taille, il faut soit redéfinir chaque zone d'impression avant de lancer une impression soit le faire faire par une macro

A plus
Via
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

re,

merci pour l'info mais pas toujours évident de penser a modifier l'impression. je vais regarder comment ca fonctionne.

peut on faire en sorte que la derniere ligne de chaque tableau soit une ligne vierge ou vide ? comme ca la zone d'impression serait limitee au tableau sans autre intervention de notre part.

avec des tableaux réduits au minimum, j'ai remarqué que quand je laisse une ligne vierge aux tableaux, le dernier enregistrement est pris en compte.
autrement je suis obligé d'agrandir le tableau et de faire un copier/glisser de la date.

(j'espere avoir ete assez explicite)

a plus

glaieuls
Messages postés
12625
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
14 septembre 2020
2 008
re

il y a des formules qui sont étirées vers le bas dans la plupart des feuilles donc vaut mieux pas inserer de ligen vide
Voilà avec une macro
Le bouton en 1ere feuille lance la macro qui redéfinit les zones d d'impression de toutes les feuilles et ouvre l'aperçu avant impression pour le classeur entier
https://mon-partage.fr/f/7NuLgLAQ/

Comme la macro boucle sur toutes les feuilles si tu ajoutes de nouvelle feuilles elles seront prises en compte, le seul impératif est que tous les titres des tableau soient en ligne 8 et qu'il n'y ait rien d’autre après sur cette ligne
La macro définit les zones d'impressions depuis A8 jusqu'à la dernière ligne remplie de la dernière colonne
Si tu veux imprimer aussi les lignes au dessus tu peux modifier le début de la plage d'impression et le faire commencer en A5 par ex
De même j'ai défini les tailles des marges dans la macro (au minimum) mais tu peux modifier ces tailles dans la macro

A plus
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

ok merci je vais regarder ca demain....match oblige.

à plus
Messages postés
107
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
16 avril 2020

Bonjour via,
Juste un petit retour pour te remercier et te dire que ça fonctionne.

j'ai créé de nouveaux onglets, uniformisé les onglets.
Juste un peu galéré pour les nouveaux onglets car pour certains je n'arrivais pas à imprimer la page entière.

Mais maintenant ça marche depuis que j'ai compris ce que tu voulais m'expliquer dans ton dernier post, qu'il me fallait éventuellement écrire sur H8 ou J8 ...si je voulais que ces nouveaux onglets soient imprimés jusqu'à la colonne H ou J...
(Il comprend vite l'animal mais il faut lui expliquer longtemps).

Merci encore et sûrement à plus tard pour de nouveaux appels à l'aide.

glaieuls