Astuces Excel
  1. Astuces Excel
  3. Vous searchz une formule pour extraire des valeurs spécifiques d'une rangée contenant des nombres et des blancs
Intereting Posts
Exporter vers Excel pour l'ouvrir Excel VBA: message d'input de validation de données dynamics avec index match Lisez le file de file Excel FormCollection MVC Disable Home and other ribbons tabs in excel using VBA Relier la valeur de plusieurs cellules dans Excel Copier coller datatables séquentielles des blocs de données à l'aide de VBA Vlookup VBA ne présente pas toutes les informations Excel 2003 VBA PivotTable Afficher les éléments spécifiques Comment gérer l'erreur 1004 Erreur avec WorksheetFunction.VLookup? Extraire datatables en fonction de la valeur dans les lignes précédentes Obtenir une string à partir d'une cellule et se référer à une autre feuille à l'aide Python et Excel prennent la valeur de la cellule par son nom plutôt que par sa coordonnée Liste de search utilisant des wild cards Effacer le contenu de la cellule en double dans la colonne Si ensuite entre deux feuilles avec boîte de message

Vous searchz une formule pour extraire des valeurs spécifiques d'une rangée contenant des nombres et des blancs

J'ai une feuille avec des rangées de données, avec de nombreuses colonnes. Je cherche de l'aide sur une formule qui extrait la sum des 3 nombres les plus petits d'une ligne en fonction des 5 dernières valeurs saisies. Notez que toutes les lignes n'auront pas de valeurs pour chaque colonne, donc la première valeur trouvée sur chaque ligne sera trouvée dans une colonne différente.

Pour déterminer la sum des 3 plus petits, j'utilise la formule =SUM(SMALL(B3:R3,{1,2,3})) . Malheureusement, cette formule examine toute la gamme. Encore une fois, je cherche de l'aide avec une formule qui ne sélectionnera que les 5 dernières valeurs affichées.

Voici un exemple simple. Les résultats pour chaque ligne montrent les totaux qui devraient être déterminés. Encore une fois, il faut chercher la sum des plus petits 3 sur la base des 5 derniers postés (dans l'exemple ci-dessous, la gamme serait de 1 à 10, avec col 10 ayant les dernières publications).

Ex.
1 ….. 2 ….. 3 …… 4 …… 5 ….. 6 ….. 7 ….. 8 ….. .9 ….. 10 …… Résultat
31 ……… 44 …. 51 …. 36 ………. 44 … 34 …. 36 …. 38 …. … 106 (34 + 36 + 34)
35..31 … 44 … 40 ….. 38 … 52 ………. 42 …. 37 ………… … 115 (37 + 38 + 40)

J'espère que cela est compréhensible. Je search une solution de formule contre une solution macro VBA en raison de mes users. Merci pour toute aide!!

Maintenant que vous avez clarifié la question, j'ai une réponse à vous. C'est assez laid, mais ça fait le travail. Vous voudrez peut-être cacher les colonnes avec les résultats intermédiaires – ou vous pourriez avoir des aventures et "nicher" les expressions. Cela rend vraiment difficile à comprendre / debug. S'il y a un moyen plus intelligent de le faire, je suis toujours ouvert à l'apprentissage.

En supposant que vous disposez des données dans les colonnes A à J , en commençant à la rangée 2, mettez les éléments suivants dans la cellule L2:P2 : 

 =MATCH(9999, A2:J2,1) =MATCH(9999,OFFSET($A2,0,0, 1, L2-1)) ... copy this by dragging right to the next 2 columns =MATCH(9999,OFFSET($A2,0,0, 1, M2-1)) =MATCH(9999,OFFSET($A2,0,0, 1, N2-1)) =MATCH(9999,OFFSET($A2,0,0, 1, O2-1))

La première ligne trouve la dernière cellule avec des données; les prochains trouvent la dernière cellule "ne comprenant pas la dernière cellule", et ils travaillent à l'envers. Le résultat est un nombre correspondant aux colonnes avec datatables. Pour votre exemple, cela donne 

 10 9 8 7 5 9 8 6 5 4

Maintenant, nous voulons find la sum de la plus petite des trois suivantes: mettre l'équation suivante dans la cellule Q2 : 

 =SUM(SMALL(INDIRECT("RC["&P2-17&"]:RC["&L2-17&"]",FALSE),{1,2,3}))

Travailler de l'intérieur: 

 RC["&P2-17"] results in RC[-12], which is "the cell 12 to the left of this one". That is the first of the "last five cells with data", cell E2 RC["&L2-17"] results in RC[-7], the last cell with data in this row FALSE use "RC" rather than "A1" indexing INDIRECT turn ssortingng into an address (in this case a range) SMALL find the 3 smallest values in this range SUM and add them together.

Cette formule m'a effectivement donné 106, 115 pour l'exemple que vous avez fourni.

Je cacherais les colonnes L à P afin de ne voir que le résultat (et non le point intermédiaire).