Das Clever-Excel-Forum.de - Treffen
... 14.-16. September 2018 im Allgäu ...

Differenz
#1
Lightbulb 
Hat jemand eine Idee, wie man folgenden Fall lösen kann?

Ich suche eine Zahl (1.100,00€), in einer Spalte, die sich aus verschiedenen Werten zusammensetzt.


Gesucht: 
1.100,00€ (Die Zahl ergibt sich aus den grünen Werten.) 


Spalte:
1.000,00€
10.000,00€
100,00€
10,00€


Angehängte Dateien Thumbnail(s)
   
to top
#2
Moin, gelöscht, weil offenbar Ziel weit verfehlt..
cu Jörg eine Rückmeldung wäre ganz reizend XL2003 bis XL2013
[-] Folgende(r) 1 Benutzer sagt Danke an Jockel für diesen Beitrag:
  • FC1911
to top
#3
Hi

Tabelle1

ABCDE
1EinzelbeträgeSuchbetragEinzelpostenAnzahl
21000216010002
3100001001
410106
510000
600
700
800
900
1000
Formeln der Tabelle
ZelleFormel
D2{=MAX((A$2:A$35<=B2)*A$2:A$35)}
E2=GANZZAHL(B$2/D2)
D3{=MAX((A$2:A$35<=RUNDEN(B$2-SUMME((D$2:D2)*(E$2:E2));5))*A$2:A$35)}
E3{=WENN(D3=0;0;GANZZAHL(RUNDEN((B$2-SUMME(($D$2:$D2)*(E$2:E2)));5)/D3))}
D4{=MAX((A$2:A$35<=RUNDEN(B$2-SUMME((D$2:D3)*(E$2:E3));5))*A$2:A$35)}
E4{=WENN(D4=0;0;GANZZAHL(RUNDEN((B$2-SUMME(($D$2:$D3)*(E$2:E3)));5)/D4))}
D5{=MAX((A$2:A$35<=RUNDEN(B$2-SUMME((D$2:D4)*(E$2:E4));5))*A$2:A$35)}
E5{=WENN(D5=0;0;GANZZAHL(RUNDEN((B$2-SUMME(($D$2:$D4)*(E$2:E4)));5)/D5))}
D6{=MAX((A$2:A$35<=RUNDEN(B$2-SUMME((D$2:D5)*(E$2:E5));5))*A$2:A$35)}
E6{=WENN(D6=0;0;GANZZAHL(RUNDEN((B$2-SUMME(($D$2:$D5)*(E$2:E5)));5)/D6))}
D7{=MAX((A$2:A$35<=RUNDEN(B$2-SUMME((D$2:D6)*(E$2:E6));5))*A$2:A$35)}
E7{=WENN(D7=0;0;GANZZAHL(RUNDEN((B$2-SUMME(($D$2:$D6)*(E$2:E6)));5)/D7))}
D8{=MAX((A$2:A$35<=RUNDEN(B$2-SUMME((D$2:D7)*(E$2:E7));5))*A$2:A$35)}
E8{=WENN(D8=0;0;GANZZAHL(RUNDEN((B$2-SUMME(($D$2:$D7)*(E$2:E7)));5)/D8))}
D9{=MAX((A$2:A$35<=RUNDEN(B$2-SUMME((D$2:D8)*(E$2:E8));5))*A$2:A$35)}
E9{=WENN(D9=0;0;GANZZAHL(RUNDEN((B$2-SUMME(($D$2:$D8)*(E$2:E8)));5)/D9))}
D10{=MAX((A$2:A$35<=RUNDEN(B$2-SUMME((D$2:D9)*(E$2:E9));5))*A$2:A$35)}
E10{=WENN(D10=0;0;GANZZAHL(RUNDEN((B$2-SUMME(($D$2:$D9)*(E$2:E9)));5)/D10))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Tabelle1

ABCDE
1EinzelbeträgeSuchbetragEinzelpostenAnzahl
21000216010002
3100001001
Formeln der Tabelle
ZelleFormel
D2{=MAX((A$2:A$35<=B2)*A$2:A$35)}
E2=GANZZAHL(B$2/D2)
D3{=MAX((A$2:A$35<=RUNDEN(B$2-SUMME((D$2:D2)*(E$2:E2));5))*A$2:A$35)}
E3{=WENN(D3=0;0;GANZZAHL(RUNDEN((B$2-SUMME(($D$2:$D2)*(E$2:E2)));5)/D3))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

abgekupfert bei: http://www.excelformeln.de/formeln.html?welcher=338

Im Übrigen lässt eine Anrede sowie ein Gruß ein Posting viel freundlicher erscheinen.
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#4
@WillWissen

Der Lösungsansatz ging komplett an meinem Anliegen vorbei.
to top
#5
Hola,

dann beschreib dein Anliegen mal etwas genauer bitte.

Gruß,
steve1da
to top
#6
Hi,

eine freundliche Anrede oder ein Hallo machen Deine Beiträge um einiges sympathischer und steigert auch die Lust der Helfer, sich Deiner Frage anzunehmen.

Was ist denn Dein Wunschergebnis, wenn Deine Tabelle so aussieht:

Arbeitsblatt mit dem Namen 'Tabelle1'
 AB
11.000,00 €! ! ! ! !
210.000,00 € 
3100,00 €! ! ! ! !
410,00 € 
5900,00 €?????
6200,00 €?????
to top
#7
Hallo,

das wird schwer bis nicht möglich.
Kommt auch entscheidend darauf an, wieviele Datensätze vorhanden sind.

Außerdem wird nicht immer eine Eindeutige Zuordnung möglich sein.

Unten ein Code, der bis zu 25 Summanden abarbeitet. Kannst aber einen Kaffe holen gehen, während er arbeitet.

Folgendes ist gegeben.
Deine Zahlen Max 25 (sonst bricht der Code ab) stehen in den Zellen A1:A25
In C1 steht die gesuchte Summe.

Ausgabe der Zahlen, die zu dieser Summe führen in Spalte B


Code:
Option Explicit

Sub summen_kombis()
    Dim lngAnzahl As Long, ar As Variant, i As Long, j As Long, dblVal As Double, dblSum As Double
    Dim k As Long
    lngAnzahl = Range("A1").End(xlDown).Row
    If lngAnzahl > 25 Then Exit Sub
    
    dblVal = Range("C1")
    ar = WorksheetFunction.Transpose(Range("A1").Resize(lngAnzahl))
    
    For i = 0 To 2 ^ lngAnzahl - 1
        dblSum = 0
        For j = 0 To lngAnzahl - 1
            If i And 2 ^ j Then dblSum = dblSum + ar(j + 1)
        Next
        If dblSum - dblVal = 0 Then
            For j = 0 To lngAnzahl - 1
                If i And 2 ^ j Then
                  If Application.CountIf(Range("B1:B" & k + 1), ar(j + 1)) = 0 Then
                    Cells(k + 1, 2) = ar(j + 1)
                    k = k + 1
                  End If
                End If
            Next
        End If
    Next
End Sub


Code von bst aus dem  MS-Office-Forum von mir erweitert

Um die Zahlen in Spalte A zu markieren, setzt Du folgende Formel ein:

=ZÄHLENWENN(B:B;A1)
Gruß Atilla
to top
#8
Hallo zusammen,

mir geht es darum einen feststehenden Betrag (z. B. 1100€) aus einer Spalte herauszufinden.
Der Haken an dieser Sache ist, dass sich der Betrag aus mind. 2 Beträgen der Spalte zusammensetzt.
Jetzt sind dort nicht viele Daten angegeben und ich wäre froh, wenn das möglich wäre.
Ich hätte am liebsten, dass er den Wert aus 100 oder 1000 Zeilen einer Spalte findet.

Gruß
FC1911
to top
#9
heje excelfreunde, FC1911,

FC1911, anhand welcher mermale unterscheiden sich deine zahlen.... bzw. anhand welcher merkmale ist zu erkennen, welche zahlen zusammengefasst werden sollen und welche nicht...
Vielen Dank
--Janosch
                                                     
Excel 2007 (SP3), Win 10 Pro                              
to top
#10
Hallo FC1911,

mal die Frage zur Zusammenstellung anders formuliert - was soll raus kommen, wenn es mehr als eine Möglichkeit zur Lösung gibt? Im Moment gehen die Antworten von dem Ansatz aus, dass Du eine zahl vorgibst und in Deiner Liste sollen passende Summanden gesucht werden.
Da geht der Ansatz von WillWissen auch nicht an Deiner Frage vorbei. Trage dort mal die 1100 ein und Du wirst sehen, es stimmt.

Eine andere Auffassung Deiner Frage wäre eine Addition nach Farben. Willst Du eventuell alle grünen Zahlen addieren? Dann brauchst Du einen anderen Lösungsansatz.
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste