Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
892to896
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
892to896
892to896
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Indirekt, Bereich variabel

Indirekt, Bereich variabel
07.08.2007 11:15:00
Alwin
Hallo Leute,
ich habe folgende Formel...
=SUMMENPRODUKT((2,25=WOW!$J$6:$J$1000)*($E8=WOW!$I$6:$I$1000)*(WOW!BC$6:BC$1000))
Wie kann ich denn die 1000 in den Bereichen Variabel gestalten? Funktioniert irgendwie mit Indirekt.
Hab mal folgendes versucht... In der Zelle C1 steht "1000".
=SUMMENPRODUKT((2,25=INDIREKT("WOW!$J$6:$J$" & $C$1))*($E8=INDIREKT("WOW!$I$6:$I$" & $C$1)) *(INDIREKT("WOW!BC$6:BC$" & $C$1)))
Allerdings kann ich dann die Formel nicht ziehen. Das "BC" im letzten Teil muss sich ändern.
Hintergrund:
Die Summenkriterien stehen in der Spalte J und I. Und die Werte die ich addieren will in den Spalten BA, BB, BC, BD...
Jemand 'ne Idee?
Gruß
Alwin

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Indirekt, Bereich variabel
07.08.2007 11:28:49
Renee
Mazwara Alwin,
Klar ändern sich die Formeln beim Kopieren nicht mehr, da die "Begriffe zwischen den Gänsefüsschen" von Excel als unveränderbare Strings angesehen werden (was sie ja auch sind!").
Vorschlag:
1. Kopiere Deine ursrpüngliche Formel
2. Alle Ersetzen: WOW! durch INDIREKT("WOW!
3. Alle Ersetzen: $1000 durch " & $C$1
Greetz Renee

AW: Indirekt, Bereich variabel
07.08.2007 11:36:00
Adrian1977
Hallo Alwin,
u.U. hilft es Dir die Formel (Adresse(zeile();spalte(),Parameter Bezug absolut oder relativ) mit in die Indirekt-Funktion einzubauen.
Gruß
Adrian

Anzeige
AW: Indirekt, Bereich variabel
07.08.2007 12:04:00
ingUR
Hallo, Allwin,
Soe recht habe ich Deine Datenstruktur in der Tabelle nicht verstanden (vielleicht lädst Du eine Beispielbaltt hoch).
Generell besteht die Möglichkeit die Zellenfunkktion ADRESSE(ZEILE(),SPALTE()) statt eines festen Teilbezugs einzusetzen:
INDIREKT(Adresse(Zeile();Spalte(BC:BC)-8))) liefert Dir also den Wert der Zelle in der aktuellen Zeile, der acht Zellen vor der Spalte BC steht. Der Spaltenbezug BC wird beim Ziehen verändert.
Möglich, dass dieses Änderung klappt:
=SUMMENPRODUKT((2,25=INDIREKT("WOW!$J$6:$J$" & $C$1))*($E8=INDIREKT("WOW!$I$6:$I$" & $C$1)) *INDIREKT("WOW!"&ADRESSE(6;SPALTE(BC:BC))&":"&ADRESSE(ZEILE(C1);SPALTE(BC:BC))))
Gruß,
Uwe

Anzeige
AW: Indirekt, Bereich variabel
07.08.2007 13:18:51
Alwin
Hallo Uwe,
so einigermaßen hab ich's auch verstanden. So ähnlich dachte ich mir das auch. Allerdings klappt das noch nicht so ganz. Ich bekomme immer einen #Wert-Fehler.
=SUMMENPRODUKT((2,25=INDIREKT("WOW!$J$6:$J$" & $C$1))*($E7=INDIREKT("WOW!$I$6:$I$" & $C$1)) *(INDIREKT("WOW!" & ADRESSE(6;SPALTE(BC:BC)) & ":" & ADRESSE(C1;SPALTE(BC:BC)))))
Hier muss irgendwo der Fehler liegen:
INDIREKT("WOW!" & ADRESSE(6;SPALTE(BC:BC)) & ":" & ADRESSE(C1;SPALTE(BC:BC))))
Wobei eine Formelauswertung schon die richtige Adresse liefert...
Anbei mal ein Beispiel. Die Formel habe ich mal nur im der Zelle AB7 im Blatt "alloc" eingebaut.
https://www.herber.de/bbs/user/44817.xls
Gruß
Alwin

Anzeige
ZELLE("Adresse";A1)
07.08.2007 12:27:00
{Boris}
Hi Alwin,
genau dafür bietet sich die ansonsten wenig brauchbare Funktion ZELLE("Adresse";Bezug) an.
=ZELLE("Adresse";C10) liefert den für die INDIREKT-Funktion benötigten String C10 - und beim Kopieren verhält er sich relativ.
Alternativ kannst Du die INDIREKT-Funktion auch mit der Z1S1-Adressierung verwenden - dann muss der 2. Parameter auf FALSCH bzw. 0 gesetzt werden.
Oder aber - wie bereits gepostet - mit der Funktion ADRESSE.
Grüße Boris

AW: Indirekt, Bereich variabel
07.08.2007 14:57:20
ingUR
Hallo, Alwin,
in der Tat liefert ADRESSE in verbindung mit INDIREKT, angewendet, in der SUMMENPRODUKT-Formel für mich unerwartete Ergebnisse.
 
  
 AB
13 
14$BC$6
15$BC$1000
16$BC$6:$BC$1000
17WOW!$BC$6:$BC$1000
181053214,449
19 
Formeln der Tabelle
AB13 : =INDIREKT("WOW!" & ADRESSE(6;SPALTE(BC:BC)) & ":" & ADRESSE(C1;SPALTE(BC:BC)))

AB14 : =ADRESSE(6;SPALTE(BC:BC))

AB15 : =ADRESSE(C1;SPALTE(BC:BC))

AB16 : =ADRESSE(6;SPALTE(BC:BC))&":"&ADRESSE(C1;SPALTE(BC:BC))

AB17 : ="WOW!" & ADRESSE(6;SPALTE(BC:BC)) & ":" & ADRESSE(C1;SPALTE(BC:BC))

AB18 : =SUMME(INDIREKT(AB17))

AB19 : =SUMMENPRODUKT(INDIREKT("WOW!" & ADRESSE(6;SPALTE(BD:BD)) & ":" & ADRESSE(C1;SPALTE(BD:BD)));INDIREKT("WOW!" & ADRESSE(6;SPALTE(BC:BC)) & ":" & ADRESSE(C1;SPALTE(BC:BC))))


 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

Nun ist es gut, dass Du die Beispeildatei hochgeladen hast. Eigenlich solltes Du mir der BEREICH.VERSCHIEBEN-Zellenformel gut hinkommen, die ich Dir als "Schnellösung anbiete, die das Ergebins 99.018 in Zelle AB7 liefert:

=SUMMENPRODUKT((2,25=INDIREKT("WOW!$J$6:$J$"&$C$1))*($E7=INDIREKT("WOW!$I$6:$I$"&$C$1)) *(BEREICH.VERSCHIEBEN(WOW!BC6;0;0;C1-5;1))) 

Gruß,

Uwe




		

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            Nicht ADRESSE ist der Übeltäter, sondern...
                                            

                    

                                                    07.08.2007 15:37:02
                                            

                                

                                            {Boris}
                                    

            

        

        

                            
Hi Uwe,

...die Funktion SPALTE. Sie liefert nämlich als Rückgabewert eine MATRIX - und diese kann von der Funktion INDIREKT nicht verarbeitet werden.

Die Hilfe schreibt dazu:

SPALTE(Bezug)

Ist Verweis ein Zellbereich, und ist SPALTE als einzeilige Matrix eingegeben, liefert SPALTE die Spaltennummern von Verweis ebenfalls als einzeilige Matrix.

Wobei Verweis = Bezug ist (das ist ein klassischer Verschreiber).

Gib mal ein: =SPALTE(A1) und werte dies in der Bearbeitungsleiste mit F9 aus. Das Ergebnis ist nicht = 1 sondern:

={1}

Grüße Boris




		

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: Nicht ADRESSE ist der Übeltäter, sondern...
                                            

                    

                                                    07.08.2007 15:48:38
                                            

                                

                                            ingUR
                                    

            

        

        

                            
Danle, Boris,

für den Hinweis auf meinen Fehler und Deine Erläuterung. 

Der Tatsache, dass mit SPALTE(BC) eine Matrix angesprochen wurde habe ich bei der Lösung nicht für Bedeutung zugemessen, die sie bei einem Summenprodukt einzunehmen hat, wo es darauf ankommt, dass alle beteiligten Matrizen von gleicher Zeilenanzahl sind.

Gruß,

Uwe




		

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: Indirekt, Bereich variabel
                                            

                    

                                                    07.08.2007 15:01:54
                                            

                                

                                            ingUR
                                    

            

        

        

                            
Hallo, Alwin,

in der Tat liefert ADRESSE in verbindung mit INDIREKT, angewendet, in der SUMMENPRODUKT-Formel für mich unerwartete Ergebnisse.


 
  
 AB
13 
14$BC$6
15$BC$1000
16$BC$6:$BC$1000
17WOW!$BC$6:$BC$1000
181053214,449
19 
Formeln der Tabelle
AB13 : =INDIREKT("WOW!" & ADRESSE(6;SPALTE(BC:BC)) & ":" & ADRESSE(C1;SPALTE(BC:BC)))

AB14 : =ADRESSE(6;SPALTE(BC:BC))

AB15 : =ADRESSE(C1;SPALTE(BC:BC))

AB16 : =ADRESSE(6;SPALTE(BC:BC))&":"&ADRESSE(C1;SPALTE(BC:BC))

AB17 : ="WOW!" & ADRESSE(6;SPALTE(BC:BC)) & ":" & ADRESSE(C1;SPALTE(BC:BC))

AB18 : =SUMME(INDIREKT(AB17))

AB19 : =SUMMENPRODUKT(INDIREKT("WOW!" & ADRESSE(6;SPALTE(BD:BD)) & ":" & ADRESSE(C1;SPALTE(BD:BD)));INDIREKT("WOW!" & ADRESSE(6;SPALTE(BC:BC)) & ":" & ADRESSE(C1;SPALTE(BC:BC))))


 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

Nun ist es gut, dass Du die Beispeildatei hochgeladen hast. Eigenlich solltes Du mir der BEREICH.VERSCHIEBEN-Zellenformel gut hinkommen, die ich Dir als "Schnellösung anbiete, die das Ergebins 99.018 in Zelle AB7 liefert:

=SUMMENPRODUKT((2,25=INDIREKT("WOW!$J$6:$J$"&$C$1))*($E7=INDIREKT("WOW!$I$6:$I$"&$C$1)) *(BEREICH.VERSCHIEBEN(WOW!BC6;0;0;C1-5;1))) 

Gruß,

Uwe




		

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: Indirekt, Bereich variabel
                                            

                    

                                                    07.08.2007 15:34:00
                                            

                                

                                            Alwin
                                    

            

        

        

                            
Einwandfrei...

Danke.

                    


            


    
    


            
    
    
    
                    


    
    
    
    
    
    
            


    

        
        Beliebteste Forumthreads (12 Monate)
    

    
    

 

    
    
    


    
Anzeige

    

        
                    
                
    




    
    

    

        

            

                
                    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
                
            

            

                

                
            

        

    




    
    

    
    
            
 

            
            

                
                
                                    

                        


    

        
        Beliebteste Forumthreads (12 Monate)
    

    
    

 

                    

                                


    
Anzeige

    

        
                    
            
                
    



                


    
Anzeige

    

        
                    
            
                
    



                


    
Anzeige