☰

Excel: Eine Teilmenge von Zahlen finden, die zusammen eine bestimmte Summe ergeben?

tag-icon • tag-icon microsoft-excel microsoft-office microsoft-excel-2007
Excel: Eine Teilmenge von Zahlen finden, die zusammen eine bestimmte Summe ergeben?

Ich habe eine Spalte mit Zahlen (sagen wir, sie ist A1:A100) und muss eine Teilmenge davon finden, deren Summe eine bestimmte Summe ergibt.

Antwort1

Mit dem Solver-Add-In* ist das möglich. Die folgenden Schritte haben bei mir in Excel 2007 und 2010 funktioniert.

  1. Legen Sie eine Zelle fest, die das Ergebnis enthalten soll (in diesem Beispiel C1). Dies ist die Zielzelle und eine Spalte, die Excel für Skizzen verwenden kann (in diesem Beispiel B1:B100).
  2. Geben Sie in der Zielzelle die Formel "=SUMPRODUCT(A1:A100,B1:B100)" (ohne Anführungszeichen) ein. Dadurch wird die Summe von A1*B1+A2*B2+...usw. berechnet.
  3. Wählen Sie „Solver öffnen“ (Registerkarte „Daten“, Gruppe „Analyse“)
  4. Die Zielzelle sollte offensichtlich sein (in diesem Beispiel $C$1).
  5. Wählen Sie bei „Gleich:“ „Wert von:“ und geben Sie den gewünschten Wert ein
  6. Geben Sie im Feld „Durch Ändern der Zellen“ „$B$1:$B$100“ ein (ohne Anführungszeichen, und es kann erforderlich sein, diese Werte selbst auf 0 zu initialisieren).
  7. Fügen Sie den Zellen, die geändert werden können, eine Einschränkung hinzu. Wählen Sie im Pulldown-Menü „bin“ (Binär) aus. Dadurch werden die Werte dieser Zellen auf 0 (Entfernen der entsprechenden A-Zelle aus der Summe) oder 1 (Hinzufügen der entsprechenden A-Zelle zur Summe) beschränkt.
  8. Klicken Sie auf „Lösen“ und warten Sie. Die Zahlen, die Teil der gesuchten Teilmenge sind, haben eine 1 in der Spalte B.

Beispiel

Wenn der Löser sehr lange braucht, können Sie ihm helfen, indem Sie Zeilen entfernen, die offensichtlich nicht funktionieren (der Gesamtbetrag ist in Dollar angegeben und nur eine Zeile enthält Cent-Beträge ungleich null).

Bonus: Sie können Excel automatisch die Zellen hervorheben lassen, nach denen Sie suchen, indem Sie diesen Zellen eine bedingte Formatierung hinzufügen. Wählen Sie alle Zellen aus, die Sie formatieren möchten, und wählen Sie unter (Registerkarte Start)>>(Gruppe Formatvorlagen)>>Bedingte Formatierung>>Neue Regel die Option „Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen“ aus. Geben Sie in die Formel „=$B1=1“ (ohne Anführungszeichen) ein, was als „true“ ausgewertet wird, wenn die entsprechende Zeile in Spalte B 1 ist. Für das Format können Sie hinzufügen, was Sie möchten (fett, kursiv, grüne Füllung usw.).

Eine weitere einfache Möglichkeit, die wichtigen Zeilen zu finden, besteht darin, die Spalte B nach Z->A zu sortieren. Dann erscheinen alle Einsen oben.

*Das Solver-Add-In kann mit diesen Schritten installiert werden

  1. Klicken Sie auf die Microsoft Office-Schaltfläche und dann auf Excel-Optionen.
  2. Klicken Sie auf „Add-Ins“ und wählen Sie dann im Feld „Verwalten“ die Option „Excel-Add-Ins“ aus.
  3. Klicken Sie auf „Los“.
  4. Aktivieren Sie im Feld Verfügbare Add-Ins das Kontrollkästchen Solver-Add-In, und klicken Sie dann auf OK. (Wenn das Solver-Add-In nicht im Feld Verfügbare Add-Ins aufgeführt ist, klicken Sie auf Durchsuchen, um das Add-In zu suchen.)
  5. Wenn Sie eine Meldung erhalten, dass das Solver-Add-In derzeit nicht auf Ihrem Computer installiert ist, klicken Sie auf „Ja“, um es zu installieren.

Antwort2

Es gibt ein kostengünstiges Excel-Add-InSummeMatch, wodurch die Teilmenge der Zahlen hervorgehoben wird, die sich zu einer Zielsumme addieren.

Bildbeschreibung hier eingeben

verwandte Informationen