Prüfen, ob die Summe einer beliebigen Anzahl von Zellen in einer Spalte mit einer Zelle in einer anderen übereinstimmt

618
user152294

Das Ergebnis, das ich erreichen möchte, ist die Spalte 'C':

Prüfen, ob die Summe einer beliebigen Anzahl von Zellen in einer Spalte mit einer Zelle in einer anderen übereinstimmt

Zum leichteren Kopieren / Einfügen dieser Tabelle in Ihr eigenes können Sie die folgenden Daten zum Kopieren verwenden:

ID 1 2 3 4 5 6 7 8 9  Number 1000 300   800 300   200  ID 1 1 1 2 2 5 5 6 9 9 9 9  Number 100 300 700 200 100 600 300 300 900 100 100 300

Ich möchte es so machen, dass es prüfen kann, ob sich eine Kombination der Zahlen aus jedem Satz eindeutiger IDs mit der Nummer der eindeutigen ID in Spalte 'A' summiert. Für ID 1 ist dies z. B. der Fall, da '700' und '300' aus Spalte 'E' ID 1 eine mögliche Anzahl von Zahlen sind, die sich zu 1000 in Spalte B von ID 1 summieren. Aber zum Beispiel Für ID 5 gibt es keine Möglichkeit, die angegebenen Zahlen '600' und '300' zu summieren, um '800' zu erhalten.

Ich weiß, dass ich die COUNTFunktion verwenden kann, um zu zählen, wie viele IDs in Spalte 'D' vorhanden sind. Da dies jedoch eine beliebige natürliche Zahl sein kann, einschließlich 0, kann ich keine Möglichkeit finden, eine IF/ SUMSchleife zu erstellen, die endet, wenn die count erreicht entweder das Minimum oder das Maximum der COUNTProgrammierung. Muss ich irgendwie eine Submatrix der IDs mit ihren jeweiligen Nummern erstellen und die Schleife irgendwie starten?

Ich bin mir nicht sicher, ob ich meine Gedanken klar erklärt habe. Bitte erkundigen Sie sich nach der notwendigen Klarstellung. Vielen Dank.

0
Wie viele Wiederholungen einer bestimmten ID könnte es in Spalte D geben? Bandersnatch vor 6 Jahren 0
Mit einfachen Arbeitsblattformeln ist dies nicht möglich. Sie müssen VBA in einer Schleife durchlaufen, und selbst dann wird es sehr schnell auf viele Iterationen skaliert, wenn die Anzahl der Zahlen pro ID zunimmt. Bei ID '1' müssen Sie 7 Kombinationen berücksichtigen, aber das Hinzufügen einer zusätzlichen Zahl wie in '9' bedeutet, dass Sie 15 haben. Das ist ein exponentielles Wachstum. Dan vor 6 Jahren 0
@ Bandersnatch Ich denke, ich würde es auf 10 beschränken user152294 vor 6 Jahren 0
@ user152294, überprüfe meinen bearbeiteten Beitrag, jetzt mit der Summenmethode habe ich die Lösung gefunden. Rajesh S vor 6 Jahren 0
Nein, wieder falsch. Bandersnatch vor 6 Jahren 0

2 Antworten auf die Frage

1
Bandersnatch

Da Sie möglicherweise bis zu 10 ID-Nummern haben und die Summe aus einem der entsprechenden Werte in Spalte E (einschließlich nicht zusammenhängender Werte) zusammengesetzt werden kann, hat Dan Recht: Sie benötigen eine VBA-Lösung.

Diese benutzerdefinierte Funktion (UDF) verdankt dieser Antwort von @ Gary's Student viel :

Function CheckSums(ID, TargetSum) Dim NumBits As Long, NumSums As Long, RngStart As Integer Dim Mask As String, i As Integer, j As Integer Dim MaskArray() As Integer Dim SumArray() As Integer Dim TestSum As Long  NumBits = Application.WorksheetFunction.CountIf(Range("D:D"), ID) NumSums = 2 ^ NumBits - 1 RngStart = Application.WorksheetFunction.Match(ID, Range("D:D"), 0)  ReDim MaskArray(NumSums - 1) ReDim SumArray(NumSums - 1)  For i = 1 To NumSums Mask = Application.WorksheetFunction.Dec2Bin(i, NumBits) For j = 0 To NumBits - 1 MaskArray(j) = Mid(Mask, j + 1, 1) If MaskArray(j) = 0 Then SumArray(j) = 0 Else SumArray(j) = Range("E" & RngStart + j) End If Next j TestSum = Application.WorksheetFunction.Sum(SumArray()) If TestSum = TargetSum Then CheckSums = True Exit Function End If Next i  CheckSums = False End Function

Um diese Funktion zu verwenden, fügen Sie sie wie hier beschrieben in ein neues VBA-Modul ein .

Dann diese Formel aus C2:

=IF(B2<>"",IF(CheckSums(A2,B2),"Yes",""),"")

gibt die Ergebnisse aus dem Screenshot unten wieder.

Ich habe Antworten mit VBA-Code gesehen, bei denen die Schlüsselwörter und Kommentare farbig sind. Weiß jemand, wie das passiert? Der Excel-VBA-Editor zeigt Farben an, aber sobald Sie den Code einfügen, wird er an Schwarzweiß weitergeleitet. Bandersnatch vor 6 Jahren 0
@ Bandersnatch, Grund ist einfach, der VB-Editor hat auch ein Farbformat-Schema, das standardmäßig auf AUTO eingestellt ist. Wenn Sie sie ändern möchten. Klicken Sie im Editorfenster auf "Tool", "Option". Der Dialog "Registerseite" befindet sich nun im Editorformat. Sie finden die Listenfelder für Codefarbe, Schriftart, Größe und Farbeinstellung. Wählen Sie das Element aus der Codefarbe aus, z. B. "Keyword-Text", und wenden Sie die Farben für Vordergrund, Hintergrund und Indikator an. Rajesh S vor 6 Jahren 0
Im Excel VBA-Editor werden Farben korrekt angezeigt. Ich habe gefragt, wie man Farben in einer SU-Antwort hat. Bandersnatch vor 6 Jahren 0
@Bandersnatch Ich denke es ist eine Markdown-Implementierung hier. Siehe: https://stackoverflow.com/editing-help#syntax-highlighting So würde Ihre Antwort aussehen: https://i.imgur.com/35uIo2E.png user152294 vor 6 Jahren 0
Hallo, @ user152294. Dieser Link war interessant, und [diese verknüpfte Referenz] (https://meta.stackexchange.com/questions/72082/changes-to-syntax-highlighting) scheint darauf hinzudeuten, dass das System feststellen kann, welche Sprache verwendet wird Markieren Sie den Code entsprechend. Ich kopiere / füge einfach aus dem Excel VBA-Editor ein und bekomme normalerweise keine Farbe, wie diese Antwort, aber manchmal auch Farbe, wie in dieser [anderen Antwort] (https://superuser.com/questions/1307146/duplicating-excel-buttons -with-relative-reference / 1307838? noredirect = 1 # comment1943590_1307838). Es ist überhaupt nicht offensichtlich, was der Unterschied ist. Bandersnatch vor 6 Jahren 0
@Bandersnatch Ich glaube, das System kann die Sprache nur dann automatisch erkennen, wenn die entsprechenden Tags im Beitrag hinzugefügt werden. Wenn ich also VBA in meinem ursprünglichen Beitrag gepostet und meine Frage mit VBA markiert habe, würde dies die Farben zeigen. Aber Sie können das nicht in Antworten tun, denke ich, also müssen Sie diese Zeile manuell in Ihre Antwort einfügen user152294 vor 6 Jahren 0
@ Bandersnatch Ich habe meiner Frage gerade das "VBA" -Tag hinzugefügt. Ihre Antwort hat jetzt Farben user152294 vor 6 Jahren 1
-1
Rajesh S

BEARBEITET:

Dieses Problem kann möglicherweise mit einer kumulativen Summe gelöst werden. Also ich habe es SUMIF()wie unten gezeigt versucht . Beachten Sie auch, dass ich andere Daten als in der OP-Frage verwendet habe.

Diese Formel in C2 gibt die gezeigten Ergebnisse an.

=IF(B2<>"",IF(SUMIF($E$2:E2,E2,$B$2:B2)=B2,"Yes",""),"")

Beachten Sie jedoch, dass die Parameter für SUMIF()als angegeben sind SUMIF(range,criteria,sum_range).

Wie Sie sehen, summiert die obige Formel die Werte in Spalte B und nicht die in Spalte E angegebenen Werte. Diese Formel ermittelt und "summiert" den Zielwert in Spalte B und stellt ihn so dar, als hätte er eine legitime Antwort berechnet.

Wenn Sie eine gültige, intelligente Antwort auf Ihre Frage wünschen, müssen Sie sich anderswo umsehen.

@Bandersnatch, in Spalte B werden Werte zufällig geschrieben. Wenn jemand meine Formel mit den gleichen Werten verwendet, erscheint in C6 JA, was nicht darf! Ich habe SUMIF für die Helfer-Spalte in B verwendet und 900 für B6 erhalten, was logisch korrekt ist und in diesem Fall C6 leer ist. In den übrigen Zellen C2, C3, C7 und C10 führt meine Formel in beiden Fällen zu JA (als Screenshot). Teste es einfach. :-) Rajesh S vor 6 Jahren 0
@ Bandersnatch, jetzt habe ich das Problem mit der Summenmethode behoben und es funktioniert einwandfrei. Rajesh S vor 6 Jahren 0
Trotzdem ist es wichtig zu recherchieren, mit 300, 100, 700 funktioniert meine Formel auch. Ich habe es mit rund 15 Gesamtkombinationen getestet und nur mit 2, in C6 bekam ich Ja. Während Sie den Speicherort ändern, erzeugt Ihre UDF in C6 einen Fehler #Value oder Ja, selbst wenn ich den Bereich ("D: D") in den neuen Bereich geändert habe. Hoffe, bald knacken wir es, schönen Sonntag noch :-) Rajesh S vor 6 Jahren 0
@Bandersnatch, ich bin immer bestrebt zu lernen ,, Diese Daten wurden in der letzten Zeit mit einer anderen Frage vom selben Benutzer verwendet, und er hat mir geantwortet, dass es zufällig keine Logik gewählt hat, sodass ich die SUMIF angewendet habe und Sie wissen ,, Innovation hat immer viele Wege und ich muss vorschlagen, dass Sie "Ideen schätzen, die über den Tellerrand hinausschauen. Gute Zeit", bald kehre ich zurück Rajesh S vor 6 Jahren 0
Wenn ich 400 für ID 10 stelle, dann wird meine Formel in der nächsten Zelle auch Ja produzieren. Wenn ID 10 Werte von 200.500.500.200 hat, ist das Y. Ich sagte, Ops Idee war Zufallszahlen und so versuchte ich es mit SUMIF. ** Ja, VBA kann es besser machen, wie es Garys Studentencode tat, ** wie Ihre Antwort zeigt. Rajesh S vor 6 Jahren 0

Verwandte Probleme