Dis que j'ai des colonnes

/ 670 - Blanc | / 650 - noir | / 680 - Rouge | / 800 - Le plus blanc

Ceux-ci ont des données dans leurs lignes. Fondamentalement, je veux sommer leurs valeurs ensemble si leurs en-têtes contiennent la chaîne souhaitée. Par souci de modularité, je voulais simplement spécifier sum / 670, / 650 et / 680 sans avoir à mentionner le reste du texte d'en-tête.

Donc, quelque chose comme = SUMIF (a1: c1; "/ NUM & / NUM & / NUM"; a2: c2)

Cela ne fonctionne pas, et honnêtement, je ne sais pas ce que je devrais rechercher.

Trucs supplémentaires:

  • J'essaie de penser à la réponse moi-même, est-il possible de mentionner le texte d'en-tête comme condition pour les ifs? Comme: si A2 = "/ 650 - Noir", procédez à la somme de l'en-tête suivant. Est-ce possible?
  • Possibilité qu'il n'implique pas VBA, une formule déplaçable serait préférable!

  • À ce stade, je peux aussi bien demander une version qui gère le nom d'en-tête complet plutôt qu'une partie de celui-ci, car je pense que c'est difficile pour le code de formule seul.

Merci d'avoir regardé!

Faites-moi savoir si j'ai besoin d'élaborer.

EDIT: En ce qui concerne les échantillons de données, tout nombre positif fera l'affaire, dommage que le débordement de pile ne supporte pas le démarquage de table. Quoi qu'il en soit, par exemple alors ..:

    +-------------+-------------+-------------+-------------+-------------+
    |      A      |      B      |      C      |      D      |      E      |
+---+-------------+-------------+-------------+-------------+-------------+
| 1 |/650 - Black |/670 - White |/800 - White |/680 - Red   |/650 - Black |
+---+-------------+-------------+-------------+-------------+-------------+
| 2 |     250     |     400     |     100     |     300     |     125     |
+---+-------------+-------------+-------------+-------------+-------------+

J'aurais dû clarifier:

La plage de numéros pour ces en-têtes irait de / 100 à / 9999 et pas plus.

ÉDITER:

Progrès jusqu'à présent:

https://docs.google.com/spreadsheets/d/1GiJKFcPWzG5bDsNt93eG7WS_M5uuVk9cvkt2VGSbpxY/edit?usp=sharing

Formule:

=SUMPRODUCT((A2:D2*
(MID($A$1:$D$1,2,4)=IF(LEN($H$1)=4,$H$1&"",$H$1&" ")))+(A2:D2*
(MID($A$1:$D$1,2,4)=IF(LEN($I$1)=4,$I$1&"",$I$1&" ")))+(A2:D2*
(MID($A$1:$D$1,2,4)=IF(LEN($J$1)=4,$J$1&"",$J$1&" "))))

Apparemment, chaque fonction MID renvoie false à chaque calcul F9.

MODIFIER MODIFIER:

D'accord! J'ai trouvé mon problème, c'est le / en cours de lecture lorsque vous avez également mentionné qu'il n'était pas nécessaire. Mec, je devrais arrêter d'écrémer!

Édition finale:

    =SUMPRODUCT((RETURNSUM*
(MID(HEADER,2,4)=IF(LEN(Match5)=4,Match5&"",Match5&" ")))+(RETURNSUM*
(MID(HEADER,2,4)=IF(LEN(Match6)=4,Match6&"",Match6&" ")))+(RETURNSUM*
(MID(HEADER,2,4)=IF(LEN(Match7)=4,Match7&"",Match7&" ")))

L'idée est que Header et RETURNSUM deviendront des critères de correspondance comme les correspondances écrites ci-dessus, de cette façon, il serait plus facile d'insérer un nouveau critère dans la table de recherche. Pour le moment, il ne prend pas en charge plusieurs lignes / glisser.

1
Aroueterra 16 janv. 2017 à 12:31

2 réponses

Meilleure réponse

J'ai mis au point quelques formules qui permettront d'atteindre ce que vous recherchez. Pour plus de facilité, j'ai fait que l'entrée de recherche n'exige le numéro que car le fait d'appuyer sur / ne tape pas automatiquement dans la barre de formule. Je m'excuse pour la longueur de la réponse, je me suis un peu emporté avec l'explication.

Je l'ai configuré pour 3 critères situés dans J1, K1 et L1.

Voici le résultat que j'ai obtenu: entrez la description de l'image ici

Formule 1 - SUMPRODUCT (): =SUMPRODUCT((A4:G4*(MID($A$1:$G$1,2,4)=IF(LEN($J$1)=4,$J$1&"",$J$1&" ")))+(A4:G4*(MID($A$1:$G$1,2,4)=IF(LEN($K$1)=4,$K$1&"",$K$1&" ")))+(A4:G4*(MID($A$1:$G$1,2,4)=IF(LEN($L$1)=4,$L$1&"",$L$1&" "))))

Sumproduct(array1,[array2]) se comporte comme une formule matricielle sans qu'il soit nécessaire de la saisir comme telle. Les formules de tableau décomposent les plages et les calculent cellule par cellule (dans cet exemple, nous utilisons des lignes uniques afin que la formule évalue les colonnes séparément).

(A4:G4*(MID($A$1:$G$1,2,4)=IF(LEN($J$1)=4,$J$1&"",$J$1&" ")))

Essentiellement, j'ai brisé la formule Sumproduct() en 3 parties identiques - 1 pour chaque condition de recherche. (A4:G4*: Maintenant que la formule se comporte comme un tableau, nous multiplierons chaque cellule individuelle par 1 ou 0 et additionnerons les résultats ensemble.

1 est produit lorsque la partie suivante de la formule est vraie et 0 lorsqu'elle est fausse (valeurs numériques par défaut pour TRUE / FALSE).

(MID($A$1:$G$1,2,4)=IF(LEN($J$1)=4,$J$1&"",$J$1&" "))

MID(text,start_num,num_chars) est utilisé ici pour évaluer les 4 chiffres après le "/" et voir s'ils correspondent au nombre dans les 3 cellules que nous recherchons (dans ce cas, la première: {{X2} }). Encore une fois, comme SUMPRODUCT() fonctionne très comme une formule matricielle, chaque cellule de la plage sera évaluée individuellement.

J'ai ensuite utilisé le IF(logical_test,[value_if_true],[value_if_false]) pour vérifier la longueur du nombre que je recherche. Comme nous recherchons une chaîne de texte à 4 chiffres, si le nombre est à 4 chiffres, n'ajoutez rien ("") pour le forcer à une chaîne de texte et si ce n'est pas le cas (car il devra être composé de 3 chiffres), ajoutez 1 espace à la fin (" ") le forçant à nouveau à devenir une chaîne de texte.

La formule effectuera alors le calcul comme ceci: La formule MID() produit le tableau: {"650 ","670 ","800 ","680 ","977 ","9999","143 "}. Ceci combiné avec la première recherche produit {TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE} qui, multiplié par A4:G4 (rappelez-vous 0 pour faux et 1 pour vrai) produit ce tableau: {250,0,0,0,0,0,0} tirant essentiellement le résultat souhaité prêt à être additionné.

Formule 2: = SUM (IF (Array)): [Cette formule ne fonctionne pas pour les nombres à 3 chiffres car ils existeront dans les nombres à 4 chiffres! Je l'ai inclus à des fins éducatives uniquement] =SUM(IF(ISNUMBER(SEARCH($J$1,$A$1:$G$1)),A8:G8),IF(ISNUMBER(SEARCH($K$1,$A$1:$G$1)),A8:G8),IF(ISNUMBER(SEARCH($L$1,$A$1:$G$1)),A8:G8))

La formule devra être saisie sous forme de tableau (une fois copiée et collée, tout en restant dans la barre de formule, appuyez sur CTRL + SHIFT + ENTER )

Cette formule fonctionne de la même manière, SUM() additionnera les valeurs de tableau produites où les colonnes IF(ISNUMBER(SEARCH() correspondent à la colonne de résultat.

SEARCH() retournera un nombre quand il trouvera les caractères exacts dans une cellule qui représente sa position en nombre de caractères. En utilisant ISNUMBER(), j'évite d'avoir à faire tout MID() et IF(LEN()=4,""," ") que j'ai utilisé dans la formule précédente comme TRUE / FALSE sera produit lors d'une correspondance est trouvé indépendamment de sa position ou du formatage de la cellule.

Comme mentionné précédemment, cela pose un problème car 999 peut être trouvé dans 9999 etc.

Le tableau résultant pour la première partie est: {250,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE} (si vous souhaitez voir le tableau, vous pouvez mettre en évidence cette partie de la formule et calculer avec F9 mais assurez-vous de mettre en évidence les crochets exacts pour cette partie de la formule).

J'espère avoir bien expliqué cela, n'hésitez pas à poser des questions sur des choses que vous ne comprenez pas. Il est bon de voir des gens désireux d'apprendre et pas seulement de chercher une réponse rapide. Je serais plus qu'heureux de vous aider et de vous expliquer plus en profondeur.

2
Glitch_Doctor 17 janv. 2017 à 11:30

Je commence cette solution avec les noms dans un tableau, vous pouvez lire les noms d'en-tête dans un tableau sans trop de difficulté.

Sub test()
    Dim myArray(1 To 4) As String
    myArray(1) = "/670 - White"
    myArray(2) = "/650 - black"
    myArray(3) = "/680 - Red"
    myArray(4) = "/800 - Whitest"

    For Each ArrayValue In myArray
    'Find position of last character
        endposition = InStr(1, ArrayValue, " - ", vbTextCompare)
    'Grab the number section from the string, based on starting and ending positions
        stringvalue = Mid(ArrayValue, 2, endposition - 2)
    'Convert to number
        NumberValue = CLng(stringvalue)
    'Add to total
        Total = Total + NumberValue
    Next ArrayValue
    'Print total
    Debug.Print Total
End Sub

Cela imprimera la réponse dans la fenêtre de débogage.

1
User632716 16 janv. 2017 à 09:53