Microsoft Excel

Herbers Excel/VBA-Archiv

BEREICH.VERSCHIEBEN mit Text

Betrifft: BEREICH.VERSCHIEBEN mit Text von: Boris
Geschrieben am: 11.11.2014 17:34:35

Guten Abend liebe Excel-Profis!

Anbei ist die Beispieldatei:
https://www.herber.de/bbs/user/93688.xlsx

Darin sind 3 Bereiche zu sehen. Im ersten Bereich B3:C14 stehen die Waren und deren Gattungen: Birnen sind Obst, Zucchini sind Gemüse etc. Im zweiten Bereich E3:F38 stehen die einzelnen Waren und deren Anzahl. Dieselben Waren können mehrmals vorkommen. Im dritten Bereich H3:K4 werden die Waren gezählt und den Warengattungen zugeordnet. Für diese Zwecke habe ich folgende Matrix-Formel geschrieben:
{=SUMME(N(N(BEREICH.VERSCHIEBEN($C$2;VERGLEICH($E$3:$E$38;$B$3:$B$14;0);0))=H$3)*($F$3:$F$38))}

Mein Problem ist, dass die Funktion N() die Ergebnisse der Funktion BEREICH.VERSCHIEBEN($C$2;VERGLEICH($E$3:$E$38;$B$3:$B$14;0);0) in Zahlen konvertiert, wodurch keine Vergleichbarkeit mit dem Berech H3:K3 gegeben ist. Die Funktion BEREICH.VERSCHIEBEN() macht an sich alle Zuordnungen richtig. Gibt es eine Möglichkeit, die Ergebnisse der BEREICH.VERSCHIEBEN()-Funktion in eine Matrix so umzuwandeln, dass sie mit dem Bereich H3:K3 vergleichbar sind?

Für die möglichen Lösungsvorschläge wäre ich sehr dankbar!

Eine Anmerkung noch, es wäre wichtig, diese Aufgabe ohne die zusätzlichen Spalten zu lösen, weil sich die Warenlite monatlich ändert.

Beste Grüße!

Boris

  

Betrifft: sehe momentan keinen wirklichen Grund... von: neopa C (paneo)
Geschrieben am: 11.11.2014 18:34:04

Hallo Boris,

... hier auf eine Hilfsspalte zu verzichten, zumal man es dann mit zwei einfachen und schnellen Formeln lösen kann.

In dieser (z.B. n Spalte S) folgende Formel: =SUMMEWENN(E:E;B3;F:F) nach unten kopieren und in H4: dann einfach =SUMMEWENN($C:$C;H3;$S:$S) und nach rechts kopieren.

Gruß Werner
.. , - ...



  

Betrifft: Grund für die Lösung ohne die zusätzlichen Spalten von: Boris
Geschrieben am: 12.11.2014 09:49:43

Hallo Werner,

vielen Dank für deinen Kommentar! Die Lösung ohne die zusätzlichen Spalten ist erforderlich, weil der Arbeitsblatt, in dem sich die Liste mit den Waren und deren Anzahl befindet, jeden Monat gelöscht und neu erstellt wird. Es wäre zu umständlich, dort die zusätzlichen Spalten immer wieder neu einzupflegen.

Außerdem wäre es für meine Bildung interessant zu wissen, ob es in Excel von Haus aus einen Analog der Funktion N() gibt, der den Inhalt der Matrix BEREICH.VERSCHIEBEN() in eine Text-Matrix umwandelt, sodass ich damit die Summen-Funktion laufen lassen kann. Im Internet bin ich zu diesem Thema bisher leider nicht fündig geworden.

Beste Grüße!

Boris


  

Betrifft: nun wenn es so ist ... aber dann ... von: neopa C (paneo)
Geschrieben am: 12.11.2014 11:03:01

Hallo Boris,

... von mir nachfolgend eine Lösung ohne die volatile Funktion BEREICH:VERSCHIEBEN().

Mehr zu BEREICH:VERSCHIEBEN() sieh mal hier: http://www.online-excel.de/excel/singsel.php?f=41

Hier nun mein hilfsspaltenfreier Formelvorschlag (vorbereitet für momentan bis 96 Daten und 31 Definitionen aber leicht anpassbar; Formel nach rechts kopieren):

 HIJK
3ObstGemüseBeerenKräuter
4626437437471

Formeln der Tabelle
ZelleFormel
H4{=SUMME(WENN(ISTZAHL(VERGLEICH($E3:$E99;WENN(ISTZAHL(VERGLEICH($C3:$C33;H3;)); $B3:$B33); )); $F3:$F99))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...




  

Betrifft: Vielen Dank für die Lösung! von: Boris
Geschrieben am: 12.11.2014 12:26:45

Hallo Werner,

diese Lösung ist schlicht und ergreifend GENIAL!!! Es funktioniert einwandfrei!

Vielen liebend Dank für Deine Mühe!

Und Danke für den Link zur Funktion BEREICH.VERSCHIEBEN.

Beste Grüße!

Boris