So finden Sie in Excel Kombinationen, deren Summe einen bestimmten Wert hat

Dieser Artikel vermittelt Ihnen relevantes Wissen über Excel. Er stellt vor allem das Problem dar, wie man Kombinationen verwendet, die einen bestimmten Wert ergeben. Ich hoffe, dass es für alle hilfreich ist.

Verwandte Lernempfehlungen: Excel-Tutorial

Heute werde ich mit Ihnen teilen, wie Sie eine Funktion verwenden, um eine Kombination zu finden, deren Summe einen bestimmten Wert ergibt.

Lassen Sie mich Ihnen ein Beispiel geben.

Wie in der Abbildung unten gezeigt, sind die Spalten A~B Datenquellen, Spalte A ist die Rechnungsnummer und Spalte B ist der Rechnungsbetrag. Jetzt müssen Sie die Summe der in Zelle F1 angegebenen Rechnungskombinationen ermitteln, beispielsweise 20.089.

Zu diesem Problem habe ich Ihnen vor langer Zeit die Planungs- und Lösungsmethode mitgeteilt. Heute werde ich Ihnen die Funktionsmethode mitteilen.

Geben Sie die folgende Formel in Zelle C2 ein und kopieren Sie sie in den Bereich C2:C21.

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

Nach der Eingabe der Formel fordert das System einen Zirkelverweis an. Dies liegt daran, dass sich die Formel auf die Zelle, in der sich die Formel befindet, ist es wert, aber machen Sie sich keine Sorgen um ihn, dem es wichtig ist, sie zu lieben.

Klicken Sie auf den Befehl „Datei“ → „Optionen“ in der oberen linken Ecke von Excel, um das Dialogfeld „Dateioptionen“ zu öffnen, wechseln Sie zur Registerkarte „Formeln“, aktivieren Sie das Kontrollkästchen „Iterative Berechnung starten“ und legen Sie die „Maximalzahl“ fest Anzahl der Iterationen“ auf 30000 . Schließen Sie das Dialogfenster mit „OK“.

Zu diesem Zeitpunkt wird die Formel in Spalte C automatisch neu berechnet und der Artikel mit einem Neuberechnungsergebnis ungleich 0 ist die Rechnungskombination. Zur Überprüfung können Sie in Zelle F2 eine SUM-Funktionsformel eingeben.

Lass mich dir die Bedeutung der Formel erklären.

=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))

Die Formel bestimmt zunächst, ob die Summe der Fläche C2:C21 ist ist gleich der F1-Einheit. Der in der Zelle angegebene Zielwert gibt bei Gleichheit den Wert von C2 selbst zurück, andernfalls wird der Rechnungsbetrag in Zelle B2 multipliziert mit 0 oder 1 zurückgegeben. Bei Multiplikation mit 0 gibt das Ergebnis 0 zurück, was darauf hinweist, dass der Rechnungsbetrag nicht ausgewählt ist. Bei Multiplikation mit 1 gibt das Ergebnis den Rechnungsbetrag selbst zurück, was darauf hinweist, dass er ausgewählt ist.

Da sich die Formel auf den Wert der Zelle bezieht, in der sich die Formel befindet, z. B. die Formel SUM(C$2:C$21) in Zelle C2, wird ein Zirkelbezug ausgelöst. An diesem Punkt aktivieren wir die iterative Berechnung und das System berechnet die Formel wiederholt, bis die Bedingung zum Stoppen der Iteration erfüllt ist (die Summe von C2:C21 entspricht dem Zielwert) oder die Anzahl der Iterationen erschöpft ist.

Das ist es. 🎜🎜Hinterlassen Sie am Ende eine Übungsfrage. 🎜🎜🎜🎜As oben Wie in der Abbildung gezeigt, besteht die bekannte Summe 25 aus 10 Werten. Jeder Wert ist größer oder gleich 1 und kleiner oder gleich 5. Bitte geben Sie eine beliebige Kombination dieser 10 Werte an. 🎜🎜Referenzantwort: 🎜🎜A3:A12-Eingabeformel 🎜🎜=IF(SUM(A$3:A$12)=B$1,A3,RANDBETWEEN(1,5))🎜🎜Verwandte Lernempfehlungen : 🎜Excel-Tutorial🎜🎜

Das obige ist der detaillierte Inhalt vonSo finden Sie in Excel Kombinationen, deren Summe einen bestimmten Wert hat. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!