Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1836to1840
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
Inhaltsverzeichnis

Bereichsnamen addieren

Bereichsnamen addieren
15.07.2021 16:14:56
Florian
Hallo!
In einer Tabelle habe ich mehrere Zellen denselben Bereichsnamen "Umsatz" vergeben. Die Zellen sind über die Tabelle verstreut.
Die Summe aller Zellen, die den Namen "Umsatz" tragen, erhalte ich mit "=SUMME(Umsatz)". So weit, so gut.
Gibt es eine Möglichkeit, nur bedingte Zellen, die den Namen "Umsatz" tragen, zu addieren?
Es befinden sich mehrere Zellen mit dem Namen "Umsatz" innerhalb des Bereichs A1:D20, andere Zellen mit dem Namen "Umsatz" aber außerhalb A1:D20.
Kann man hier Zellen addieren, die 1) den Namen "Umsatz" tragen und 2) sich innerhalb des Bereichs A1:D20 befinden?
Zweck: Durch Verschieben einzelner Zellen in den Bereich A1:D20 oder aus diesen Bereich heraus sollen sie mitaddiert werden - oder eben nicht.
Mit "=SUMMEWENN(A1:D20;Umsatz)" komme ich leider nicht weiter.
Vielen Dank!

27
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: (D)eine Beispieldatei könnte aufklären ...
15.07.2021 16:23:24
neopa
Hallo Florian,
... denn es kann nur einen Bereichsnamen "Umsatz" geben. Was also soll genau addiert werden? Zeig es an (D)einer Beispieldatei.
Gruß Werner
.. , - ...
AW: Bereichsnamen addieren
15.07.2021 18:33:50
Luschi
Hallo Florian,
ja das ist möglich; dafür ist der fast unbekannte Schnittstellen-Operator (Leerzeichen) verantwortlich!
Deine Formel lautet: =SUMME(A1:D20 Umsatz).
Mehr zu diesem Rechenkünstler findest Du hier:: https://www.tabellenexperte.de/excel-quickies-09-2014/
Gruß von Luschi
aus klein-Paris
Danke für diesen netten Gehirnkick!
15.07.2021 18:49:41
lupo1
Den Operator habe ich noch nie verwendet!

1
2
32 64 4 128 256
8
16
Die Vertikale sei als D, die Horizontale als BF benannt.
Mit =SUMME(D;BF;-D BF) ergibt sich 511,
mit =SUMME(D;BF;D BF) ergibt sich 519
Anzeige
AW: Danke für diesen netten Gehirnkick!
15.07.2021 19:53:11
Luschi
Hallo lupo1,
danke für die virtuellen Blumen.
Gruß von Luschi
aus klein-Paris
PS: auch ein alter Hase ist manchmal schneller als der junger Fuchs!
AW: prima, wieder was gelernt ...
15.07.2021 22:15:38
neopa
Hallo Luschi,
... dank Deines Beitrages hab ich nicht nur die Aufgabenstellung besser verstanden sondern eine mir bis heute unbekannte Operation neu kennengelernt. :-)
Mit diesem ist scheinbar noch mehr möglich, als Martin aufgezeigt hat. Man kann nämlich auch die Argumente negieren mit entsprechend anderen Auswertungsergebnissen. Allerdings müssen dann die Bereiche zumindest teilweise gleich groß sein. Das muss ich mir aber noch am WE mal genauer ansehen.
Gruß Werner
.. , - ...
Anzeige
AW: Schnittmengenoperator ist nachgeordnet ..
16.07.2021 18:57:34
neopa
Hallo,
... und zwar jeglicher mathematischer Operation, wie ich in meiner XL-2010er Version feststellen konnte.
D.h. während man die Werte der ersten Matrix normal mit - negieren kann, geht das für die Matrix nach dem gewünschten Schnittmengenoperator nur durch eine mathematische Operation (*-1). Eine Klammerung der zweiten Matrix hilft da nicht, weil diese eine mathematische Operation erzwingt (also bei mir in XL2010) Die Ergebnisse der gelb markierten Zellen ergeben sich durch etsprechende Auswertung der durch das - vor der 2. Matrix erzwungenen Zusammenfassung (Addition bzw. Subtraktion) aller Datenwerte beiden Matrizen zu einer Gesamtmatrix..
An nachfolgenden einfachen Beispiel ist das nachvollziehbar:
ABCDEFGH
M1    Summe:18 
181311  Summe:-18 
151710  Summe:-80-18
122722 Summe:-33618
145429 Min:2 
 242623 Max:7 
 212025 Min:-7 
   M2 Max:-2 
     Min:-24-7
     Max:16-2
     Mittelwert:4,5 
     Mittelwert:-4,5 
     Mittelwert:-284,5

NameBezug
_M1=temp!$A$2:$C$5
_M2=temp!$B$4:$D$7

ZelleFormel
G1=SUMME(_M1 _M2)
G2{=SUMME(-_M1 _M2)}
G3{=SUMME(_M1 -_M2)}
H3{=SUMME(_M1 _M2*-1)}
G4{=SUMME(-_M1 -_M2)}
H4{=SUMME(-_M1 _M2*-1)}
G5=MIN(_M1 _M2)
G6=MAX(_M1 _M2)
G7{=MIN(-_M1 _M2)}
G8{=MAX(-_M1 _M2)}
G9{=MIN(_M1 -_M2)}
H9{=MIN(_M1 _M2*-1)}
G10{=MAX(_M1 -_M2)}
H10{=MAX(_M1 _M2*-1)}
G11=MITTELWERT(_M1 _M2)
G12{=MITTELWERT(-_M1 _M2)}
G13{=MITTELWERT(-_M1 -_M2)}
H13{=MITTELWERT(-_M1 _M2*-1)}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
Deine Schlussfolgerung ist falsch, ...
17.07.2021 01:50:04
Luc:-?
…Werner;
er hat Priorität vor allen anderen Operatoren, genauso wie die anderen beiden Bereichsoperatoren. Die Leerzeichen, die Du in den verschiedenen Fmln verwendest, wdn nicht immer als SchnittmengenOperatoren interpretiert. Das wäre nur dann der Fall, wenn eines 2 Bereichsadressen trennt. Sobald andere Operatoren (Vorzeichen wirken hierbei auch als solche) zwischendurch gesetzt wdn, wird ein diesen voran- oder nachgestelltes Leerzeichen nur noch als FüllZeichen betrachtet und folglich ignoriert. Du könntest es auch weglassen und kämst zum selben Ergebnis. Das ist auch logisch, denn dadurch geht der ZellBezug verloren und Bereichsoperatoren sind auf Datenfelder nicht anwendbar. Man kann also stets nur das Ergebnis der SchnittmengenOperation negativieren, also wie zB in G2≡H3. Das hatte ich allerdings unten bereits festgestellt… ;-]
Lupo hat erkannt, dass es sich anderenfalls um kombinierte Datenfeld-Bereichsoperationen handelt, die ein Datenfeld liefern, mit dem dann die jeweilige Fkt arbeitet (vgl auch meine dortige AW).
Morhn, Luc :-?
Anzeige
AW: ja, meine getroffene Schlussfolgerung ...
17.07.2021 11:07:25
neopa
Hallo Luc,
... war insofern nicht korrekt, als dass ich diese nur aus (m)einem betrachteten Spezialfall verallgemeinert getroffen habe, nämlich den eines Operationszeichen (in meinem Beispiel dargestellt durch eine Negation der zweiten Matrix) zwischen zwei Bereichsangaben.
Meine Feststellung deckt sich mE jedoch mit Deiner Aussage, dass in so einem Fall (egal welches Operationszeichen da auch eingesetzt wird) jegliches Leerzeichen zwischen den beiden Bereichsangaben von Excel dann ignoriert wird und somit dieses von Excel nicht mehr als Schnittmengenoperator betrachtet wird. Dies und nichts anders wollte ich zum Ausdruck bringen. Nur war und ist durch die von mir getroffene Aussage suggerierte Verallgemeinerung inkorrekt. Ich bin und war halt kein Wissenschaftler.
Gruß Werner
.. , - ...
Anzeige
AW: Bereichsnamen addieren
15.07.2021 23:31:22
Florian
Hallo Luschi!
Schnittstellen-Operator: habe ich auch noch nie gehört.
Aber vielen Dank! Es ist genau das, was mir weiterhilft!
Beste Grüße,
Florian
Dass das weitgehend unbekannt ist, ...
16.07.2021 02:36:27
Luc:-?
…ist klar, Luschi,
allerdings konnte man schon immer darauf kommen, wenn man sich mal die möglichen Operatoren in der Xl-Hilfe angesehen hätte, denn dort wurde der schon immer unter Zell- bzw Bereichsoperatoren aufgeführt, neben : (IntervallBereich) und ; (Aufzählungsliste [ähnlich Vereinigung], engl ,). In VBA wird dafür die Methode Intersect verwendet, während Union nicht ganz dem ; entspricht, da die Liste damit nachbearbeitet (ggf sinnvoll zusammengezogen) wird.
Da sein Ergebnis ebenfalls ein Bereich ist, kann der nur in Gänze negativiert wdn (wie Lupo andeutete), weil dadurch der ZellBezug verloren geht und somit in Lupos 1.Bsp 2 Bereiche summiert wdn und deren Schnittmenge (1 Zelle!) dabei negativiert als reine Zahl zusätzlich einbezogen wird.
Morhn, Luc :-?
Anzeige
Interessante Fortsetzung
16.07.2021 15:53:13
lupo1
=INDEX(-D BF;;) ergibt -4
=INDEX(D -BF;;) ergibt
-31 -63 -3 -127 -255
-30 -62 -2 -126 -254
-28 -60 0 -124 -252
-24 -56 4 -120 -248
-16 -48 12 -112 -240
=INDEX(-D -BF;;) ergibt
-33 -65 -5 -129 -257
-34 -66 -6 -130 -258
-36 -68 -8 -132 -260
-40 -72 -12 -136 -264
-48 -80 -20 -144 -272
Interessante Fortsetzung 2
16.07.2021 16:01:56
lupo1
=KKLEINSTE((D;BF;D BF);ZEILE(1:12)) ergibt
1
2
4
4
4
8
16
32
64
128
256
#ZAHL!
Leider fehlt die echte Vereinigungsmenge, also die zusammengenommen Bereiche abz. der Schnittmenge (9 Werte).
immerhin aber:
16.07.2021 16:14:39
lupo1
=WENN(INDEX((D;BF);;)D BF;INDEX((D;BF);;))
1
2
FALSCH
8
16
... woraus man dann mit +ZEILE(...)%% oder +SPALTE(...)%% auch noch einen kombinierten Wert-Lage- statt Nur-Wertebezug herstellen könnte.
Anzeige
Du hast das iW richtig umgesetzt, ...
17.07.2021 01:20:57
Luc:-?
…Lupo,
und dabei wohl meine Ausführungen beachtet. Dabei kann man den SchnittmengenOperator auch auf mehr als 2 Bereiche* anwenden, ganz so wie den ListSeparator, der hierbei wie ein simpler VereinigungsmengenOperator fungiert. Auch der BereichsintervallOperator stellt im Grunde genommen einen letzteren dar, nur stets kontinuierlich, während mit dem ListSeparator auch diskontinuierliche Bereiche dargestellt wdn können. Für deren Form ist in Xl der Nutzer verantwortlich, während es in VBA mit Union ja auch noch eine rationelle Anordnungsmethode gibt, die aber nicht immer verarbeitungsgünstig ist (falls man EinzelZellen benötigt).
Im Ggsatz zu Dir hat Werner aus seinen Beobachtungen falsche Schlussfolgerungen gezogen, obwohl ich bereits geschrieben hatte, worauf sich eine Negation auswirkt. Die Bereichsoperatoren haben also immer die Priorität. Mit Deiner INDEX-Fml hast Du das ja angedeutet, aber diese Fkt wäre für eine plurale MatrixFml, die dieses Datenfeld liefert, gar nicht erforderlich (vgl oben - AW an Werner).
* Für Werners Bsp zB so: =_M1 _M2 4:4 C:C ⇒ 7
Morhn, Luc :-?
Anzeige
auf dem Weg zur Vereinigungsmenge?
17.07.2021 09:09:24
lupo1

A    1   B
2
32 64 4 128 256
C 8
16       3
D sei -- wie vorher schon so benannt - die Spalte mit den Einträgen 1..16
BF sei - wie vorher schon so benannt - die Zeile_ mit den Einträgen 32..256
Die hineingeschriebenen Zeichen A,B,C,3 seien unerwünschte Fremdkörper in der Matrix.
Die 16 von 25 unerwünschten Positionen der Matrix sollen entw. nicht oder als Fehler ausgewiesen werden, z.B. so

#DIV/0!	#DIV/0!	1	#DIV/0!	#DIV/0!
#DIV/0!	#DIV/0!	2	#DIV/0!	#DIV/0!
32      64      4       128      256
#DIV/0!	#DIV/0!	8	#DIV/0!	#DIV/0!
#DIV/0!	#DIV/0!	16	#DIV/0!	#DIV/0!
Warum hülfen Fehler schon mal? Weil man die über WENNFEHLER zu FALSCH drehen und damit ausschließen könnte.
Das ginge z.B. so (hier noch mit dem Makel der dafür konstruierten Ganzzahl-2er-LOG-Eigenschaft der enthaltenen Daten, nicht wie gewünscht über die Lage der Vereinigungsmenge):
=LET(x;INDEX(-(-D -BF)-(D BF);;);1/(1/(REST(LOG(x;2);1)=0)/x)) oder ohne LET
=1/(1/(REST(LOG(INDEX(-(-D -BF)-(D BF);;);2);1)=0)/INDEX(-(-D -BF)-(D BF);;))
____________
Das gilt es hinsichtlich der Darstellung der Vereinigungsmenge nun zu verallgemeinern. Mal sehen, wer es evtl. hinbekommt ...
Anm: Mit ZEILE und SPALTE ist es mir noch nicht gelungen.
Anzeige
xl365: Jetzt für Texte die OR-Menge
17.07.2021 12:13:27
lupo1
Folgende Formel zeigt jedes einzelne Element von D und BF, dabei auch deren Schnittmenge, genau einmal:
=LET(x;EINDEUTIG(RECHTS(ARRAY.COMBINE(
D&"-"&TEXT(SPALTE(D);"00000")&TEXT(ZEILE(D);"000000");MTRANS(
BF&"-"&TEXT(SPALTE(BF);"00000")&TEXT(ZEILE(BF);"000000")););11));
INDEX(A:XFC;RECHTS(x;6);LINKS(x;5)))

als
1
2
4
8
16
32
64
128
256
(LAMBDA-)ARRAY.COMBINE hier: https://www.office-hilfe.com/support/threads/36909#ARRAY_COMBINE
Hier nur Vektoren (also die Kreuzung von 1 Spalte und 1 Zeile).
Für die Kreuzung von Bereichen wird es entspr. länger. Kommt noch hinterher.
Da außerdem ARRAY.COMBINE verschachtelt werden kann, kann dann auch folgendes ausgewertet werden:

aa
aa  d
bbbxxbbxbbb
bbbxxbbxbbb
bbbxxbbxbbb
aa  d
xxccxc
xxccxc
aa
... also alle a-d und die x jeweils nur 1Mal statt doppelt.
Auch mehr als 2 an einer Schnittmenge beteiligte Mengen klappen damit.
Anmerkung: Die Schnittmenge (D BF) als Formelschreibung haben wir mittlerweile wieder verlassen.
AW: in XL2010 mit ZEILE(), SPALTE(), AGGREGAT() ..
17.07.2021 15:54:53
neopa
Hallo,
... zur sortierten Listung der Zahlenwerte.der Vereinigungsmenge.
Da ich XL365 nicht habe, nutze ich zur Kürzung meiner Auswertungsformel (hier in K8, die weit genug nach unten kopiert werden muss) mit: _VM eine benannte Formel.
Nachfolgende Formel gilt jedoch nur für Kreuzung von zwei Vektoren unter Nichtberücksichtigung von unzutreffende Zahlenwerten (gelb hinterlegt). Für 2 Bereiche ist es mir momntan noch nicht gelungen, unzutreffende Zahlenwerte auszuhalten. Wenn jedoch nur unzutreffende Textwerte wird die Zellformel einfacher und kann dann analog auch für die Vereinigungsmenge von zwei Bereichen eingesetzt werden.
Arbeitsblatt mit dem Namen 'Tab1'
 KLMNOP
2      
3 abc 1nn 
4  32  
5 32644128256
6  xyz8  
7   64 9
81     
92     
104     
118     
1232     
1364     
1464     
15128     
16256     
17      

NameBezug
_BF='Tab1'!$L$5:$P$5
_D='Tab1'!$N$3:$N$7
_VM=INDEX('Tab1'!$A:$ZZ;MIN(ZEILE(_D);ZEILE(_BF));MIN(SPALTE(_D);SPALTE(_BF))):INDEX('Tab1'!$A:$ZZ;MAX(ZEILE(_D);ZEILE(_BF));MAX(SPALTE(_D);SPALTE(_BF)))

ZelleFormel
K8=WENNFEHLER(AGGREGAT(15;6;1/(1/(((_VM>_BF)+(_VM>_D))&lt2))*_VM;ZEILE(L1));"")
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
AW: in XL2010 mit ZEILE(), SPALTE(), AGGREGAT() ..
17.07.2021 21:14:03
Luschi
Hallo Werner,
habe Dein Beispiel mal nachgebaut, die 3 Namen definiert, aber die Formel für Zelle 'K8' gibt kein Ergebnis zurück - kannst Du das mal nachprüfen?
https://www.herber.de/bbs/user/147176.xlsx
Gruß von Luschi
aus klein-Paris
Habe keine Namen, sondern Bereichsadressen ...
18.07.2021 05:00:22
Luc:-?
…verwendet, Luschi,
inkl _VM in Hilfszellen und die Fml auf plurale MatrixFml umgebaut (nur letzte ZeilenAngabe geändert). Dann kommt Lupos Ergebnis (mit Lupos Daten im Bereich A1:E5) mit folglich dieser FmlVersion heraus:
{=WENNFEHLER(AGGREGAT(15;6;1/(1/(((G1:K5<>A3:E3)+(G1:K5<>C1:C5))<2))*G1:K5;ZEILE(1:9)) ;"") }
Allerdings würde ich dafür eher diese plurale MatrixFml bevorzugen: ;-)
{=DataSet(C1:C5; A3:E3;;;1) }
Mit dieser UDF ist die Bildung von Vereinigungs-, Schnitt- und Differenzmenge von 2 (Ko-)Vektoren möglich - flfd oder ggübergestellt. Dopplungen in einem Operanden können ebenfalls entfernt und eine leere (Ergebnis-)Menge symbolisch angezeigt wdn.
Morhn, Luc :-?
AW: zumindest von mir aber so nicht gewollt, ...
18.07.2021 08:18:11
mir
Hallo Luc,
... denn wenn mit fixen Zelladressen gearbeitet werden kann, dann brauche ich auch die benannte Formel _VM. nicht. zu definieren.
Es ging mir darum, aus den definierten Vektoren den Bereich der Vereinigungsmenge zunächst in Abhängigkeit der definierten Vektoren den Bereich variabel zu für die meine Zellauswertungsformel zu bestimmen. Und für dies Auswertung würde ich in XL-Version vor 365 keine plurale Matrixformel einsetzen, weil der Ergebnisbereich dann ja auch variabel sein kann.
Gruß Werner
.. , - ...
Das war auch nur die Bestätigung ...
18.07.2021 13:00:41
Luc:-?
…meinerseits, Werner,
dass Deine Fml prinzipiell fktioniert. Dazu habe ich dann vollständigerweise angegeben, was ich getan hatte. Ich gehe davon aus, dass das mit definierten Namen und im Original genauso fktioniert. Ich bin ja auf solche Fmln nicht angewiesen… ;-]
Luc :-?
AW: ein Leerzeichen ist der Übeltäter ...
18.07.2021 08:25:23
neopa
Hallo Luschi,
...bei _VM hast Du im Namensmanager vor dem = ein Leerzeichen, dass musst Du lediglich löschen.
Gruß Werner
.. , - ...
Eine "Oder-Menge" gibt's nicht, ...
18.07.2021 05:25:15
Luc:-?
…Lupo,
was Du zeigst heißt Vereinigungsmenge. Was Du mit „Vereinigungsmenge“ firmiert hast, sollte wohl eher eine Art von Union auf einen Gesamt­Bereich wdn. Das ist aber nicht dasselbe. Wie man das macht, ist auch bekannt*, nur muss man die Daten anschließend noch bearbeiten, um Dein WunschErgebnis zu erhalten, wodurch die ZellBezüge verloren gehen, was Dir übrigens auch gelungen ist, indem Du ggf als Schnittmenge gedachte ZellBereiche durch zwischengeschriebene arithmetische Operatoren in Datenfelder gewandelt und das Ganze somit als Schnittmenge unwirksam gemacht hast. (Ich hatte das bereits 2× erwähnt!)
* Man kann die TeilBereiche eines diskontinuierlichen Bereichs ggf auch sinnvoll mit INDEX in einem kontinuierlichen Bereich zusammenführen (uU ergänzt).
Gruß, Luc :-?
Es gibt UND, ODER, XODER als Operator
18.07.2021 08:47:25
lupo1
... und daher habe ich mir erlaubt, die entsprechende Menge (Vereinigung) eine ODER-Menge zu nennen, um sie von den anderen beiden (Schnitt) und (Vereinigung ohne Schnitt) namensgleich abzugrenzen.
Eine entsprechende LAMBDA-Funktion für die Liste zum jeweiligen Operator würde ich "schamlos" entsprechend
LIST.AND
LIST.OR
LIST.XOR
nennen ... oder besser die entsprechenden Operatoren als Argument in
LIST
versorgen. Anstelle des Wortes LIST kann man natürlich auch was anderes nehmen.
Bevor ich so etwas schreibe, ...
18.07.2021 13:21:36
Luc:-?
…Lupo,
pflege ich meine Kenntnisse auf Wikipedia aufzufrischen… ;-]
Die Mengenlehre ist schon über 150 Jahre alt und deren Operationen fktionieren nicht genauso wie arithmetische oder logische, auch wenn ihre OperatorSymbole denen der letzteren ähneln.
Mengen von Objekten fktionieren im Prinzip genauso wie Mengen von Werten, man darf das aber gedanklich nicht durcheinanderwerfen. Übrigens, zuerst hatte ich vor Jahren mit Texten in ListenForm (quasi Aufzählungen) experimentiert und darauf Prinzipien der Mengenlehre angewendet, was auch später noch Niederschlag in Join-UDFs fand. DataSet (vgl AW an Luschi) arbeitet dagegen mit Bereichen (nur Zeile u/o Spalte) bzw analogen Datenfeldern und erstellt neue Datenfelder.
Luc :-?
Da bin ich wohl simpler gestrickt
19.07.2021 09:03:16
lupo1
... an meinem Beispiel:
Mit der 4 ist es OR (oder XOR + AND)
Mit der 4, aber aus beiden Teilarrays, ist es OR + AND (oder eben TEILARRAY + TEILARRAY)
Ohne die 4 ist es XOR
Nur die 4 ist AND
... und den Unterschied zwischen Werten oder Objekten habe ich da jetzt auch nicht so begriffen. Wie gesagt: Simpler gestrickt. Oder wie man so sagt: Dumm und glücklich.
Naja, wenn Du meinst.... ;-) owT
19.07.2021 18:00:32
Luc:-?
:-?

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige