Comment trouver des combinaisons dont la somme est une certaine valeur dans Excel

Cet article vous apporte des connaissances pertinentes sur excel. Il présente principalement le problème de l'utilisation des fonctions pour trouver des combinaisons qui totalisent une certaine valeur. Examinons-le ensemble. J'espère que cela sera utile à tout le monde.

Recommandations d'apprentissage associées : Tutoriel Excel

Aujourd'hui, je vais partager avec vous comment utiliser une fonction pour trouver une combinaison qui totalise une certaine valeur.

Permettez-moi de vous donner un exemple.

Comme le montre la figure ci-dessous, les colonnes A ~ B sont des sources de données, la colonne A est le numéro de facture et la colonne B est le montant de la facture. Vous devez maintenant trouver la somme des combinaisons de factures spécifiées dans la cellule F1, par exemple 20 089.

Concernant ce problème, j'ai partagé avec vous la méthode de planification et de résolution il y a longtemps ; aujourd'hui je vais partager avec vous la méthode des fonctions ;

Entrez la formule suivante dans la cellule C2 et copiez-la dans la plage C2:C21.

=IF(SUM(C$2:C$21)=F$1,C2,B2*RANDBETWEEN(0,1))

Après avoir entré la formule, le système affichera une référence circulaire. En effet, la formule fait référence à. la cellule où se trouve la formule, ça vaut le coup, mais ne vous inquiétez pas pour lui, qui se soucie de l'aimer.

Cliquez sur la commande "Fichier" → "Options" dans le coin supérieur gauche d'Excel pour ouvrir la boîte de dialogue Options de fichier, passez à l'onglet "Formules", cochez la case "Démarrer le calcul itératif" et définissez le "Nombre maximum". d'itérations" à 30000 . Fermez la boîte de dialogue avec "OK".

À ce moment, la formule de la colonne C sera automatiquement recalculée et l'élément avec un résultat de recalcul non nul est la combinaison de facture. Vous pouvez saisir une formule de fonction SOMME dans la cellule F2 pour vérification.

Laissez-moi vous expliquer le sens de la formule.

=IF(SUM(C$2:C$21)=F$1,C2,B2*RANDBETWEEN(0,1))=IF(SUM(C:C)=F,C2,B2*RANDBETWEEN(0,1))

公式首先判断C2:C21区域的总和是否等于F1单元格指定的目标值，如果相等，则返回C2自身的值，否则，返回B2单元格发票金额乘以0或1。当乘以0时，结果返回0，表示该发票金额未被选中，当乘以1时，返回发票金额自身，表示被选中。

由于公式引用了公式所在单元格的值，比如C2单元格的公式SUM(C:C)，也就会触发循环引用。此时我们启用迭代计算，系统会反复计算该公式，直至停止迭代的条件成立(C2:C21的总和等于目标值)，或迭代次数用尽。

就这么回事。

最后留个练习题。

如上图所示，已知总和25，由10个数值构成，每个数值大于等于1，且小于等于5，请列出这10个数值的任一组合。

参考答案：

A3:A12输入公式 

=IF(SUM(A:A)=B,A3,RANDBETWEEN(1,5))

La formule détermine d'abord si la somme de l'aire C2:C21 est égal à l'unité F1. La valeur cible spécifiée dans la cellule, si elle est égale, renvoie la valeur de C2 elle-même, sinon, renvoie le montant de la facture dans la cellule B2 multiplié par 0 ou 1. Lorsqu'il est multiplié par 0, le résultat renvoie 0, indiquant que le montant de la facture n'est pas sélectionné. Lorsqu'il est multiplié par 1, le résultat renvoie le montant de la facture lui-même, indiquant qu'il est sélectionné.

Étant donné que la formule fait référence à la valeur de la cellule où se trouve la formule, comme la formule SUM(C$2:C$21) dans la cellule C2, une référence circulaire sera déclenchée. À ce stade, nous activons le calcul itératif et le système calculera la formule à plusieurs reprises jusqu'à ce que la condition pour arrêter l'itération soit établie (la somme de C2: C21 est égale à la valeur cible) ou que le nombre d'itérations soit épuisé.

C'est tout. 🎜🎜Laissez une question d'exercice à la fin. 🎜🎜🎜🎜Comme ci-dessus Comme le montre la figure, la somme connue 25 se compose de 10 valeurs. Chaque valeur est supérieure ou égale à 1 et inférieure ou égale à 5. Veuillez énumérer toute combinaison de ces 10 valeurs. 🎜🎜Réponse de référence : 🎜🎜Formule de saisie A3:A12 🎜🎜=IF(SUM(A$3:A$12)=B$1,A3,RANDBETWEEN(1,5))🎜🎜Recommandations d'apprentissage associées : 🎜tutoriel Excel🎜🎜

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!