Tabelle mit vielen Spalten

[ECM25]

  Beispiel.xlsx (Größe: 206,61 KB / Downloads: 22)
  Beispiel.xlsx (Größe: 206,61 KB / Downloads: 22)
Hallo zusammen, 

ich habe momentan Schwierigkeiten mit meinen Excel-Fähigkeiten und hoffe, dass ich hier im Forum einen Lösungsvorschlag für mein Problem finden kann. Es geht um Folgendes: Ich habe eine Tabelle mit vielen Spalten, von denen einige den gleichen Namen als Überschrift haben. Jede dieser Spalten enthält bestimmte Werte. Nun möchte ich, dass automatisch nach Spalten mit demselben Namen gesucht wird. Anschließend soll eine neue Summenspalte erstellt werden, die die Summe der Werte der jeweiligen gleichnamigen Spalten enthält.

[Gast 123]

Hallo

mitz diesem kleinen Code sollte es ghen. In ein normaLes Modul kopieren.
Das Makro markiert mit Select die Einfügespalte, und fügt sie bei Yes ein.

mfg Piet


Sub Spalten_einfügen()
Dim AC As Range, LSp As Integer
Dim j As Long, n As Integer, ok
    'LastSpalte ermitteln
    LSp = Cells(1, Columns.Count).End(xlToLeft).Column
    'For next zum Spalte einfügen; Summe beachten
    For j = LSp To 3 Step -1
      If InStr(Cells(1, j), "Summe") Then
      ElseIf Cells(1, j) <> Cells(1, j - 1) And _
        InStr(Cells(1, j - 1), "Summe") = 0 Then
        Columns(j).Select
        Columns(j).Copy
        Txt = Cells(1, j - 1)
        ok = MsgBox(Txt & "  -  Summenspalte einfügen??", vbYesNoCancel)
        If ok = vbCancel Then Exit Sub
        If ok = vbYes Then
            Columns(j).Insert shift:=xlToRight
            Cells(1, j) = "Summe " & Cells(1, j - 1)
            Cells(1, j).Interior.ColorIndex = 44
            n = n + 1
        End If
      End If
    Next j
    MsgBox n
End Sub

[Oberschlumpf]

Hi,

vorneweg: Menschen und Datenerfassung/verwaltung sind nicht kompatibel miteinander
oder anders: du/ihr habt in der Überschriftenzeile (Raumnummern, -namen, -einträge) zu oft Tipp- und sogar richtige "ihr-habt-euch-total-verschrieben"-Fehler

Beispiele? = Gerne!

sind die Räume in den Spalten ALU:ALV...
VH BA 1
VH BA!
...wirklich unterschiedliche Räume, oder soll das ! im unteren Eintrag eigtl auch eine 1 sein?

oder die Räume in ALP:ALS...
"Vh"
"VH "
"VH "
"vh "
...sowohl in Groß- und Kleinschreibung, als auch mit und ohne Leerzeichen am Ende = soll das wirklich so?

der Eintrag in AIW...
Straßenseite
...scheint ok zu sein, aber...
...die Einträge in AIJ:AIU...
Straße
Zeit
...soll das wirklich so, oder soll "Zeit" vllt auch "seite" heißen und direkt an "Straße" folgen und nicht mit Entertaste getrennt?

Na ja, derlei und noch n paar andere Schreibfehler gibt es noch weitere

Aber wenn ich all das (und die anderen erforderlichen Bedingungen) richtig + vollständig berücksichtigt habe, dann bekommst du von mir die DELUXE-Lösung für deine Frage ❗ ??

Schau dir die Datei im Anhang mal an

 Nun zur Beschreibung meines Codes:

1. Du musst das Makro namens "sbSumCols" starten, um den Code auszuführen. Das Makro findest du in Modul1.

2. Im ersten Schritt werden erstmal alle existierenden Summen-Spalten in deiner Tabelle gelöscht. Denn es kann ja sein, dass im Lauf der Zeit noch weitere Spalten (neue Raumnummern) hinzukommen (oder auch gelöscht werden), und/oder bestehende Raumnummern wurden geändert/umbenannt. Es muss also eine Aktualisierung her = so wird also mit meinem Makro immer wieder auf's Neue "aufgeräumt"

3. So, jetzt enthält Zeile 1, beginnend in Spalte 3 nur noch Raumnummern und -namen.
Alle Einträge in Zeile 1, ab Spalte 3 werden in einer Array-Variablen gesammelt.
Das Besondere hier ist...
...es wird immer nur der erste Raum-Eintrag + Spalte und der letzte, selbe Raum-Eintrag + Spalte gesammelt
Bsp:
von den Raum-Einträgen...
1101 (Spalte 3)
1101 (Spalte 4)
1101 (Spalte 5)
...
1101 (Spalte 13)
1101 (Spalte 14)
...werden in der Arr-Var nur die Raum-Einträge + zugehörige Spalte...
1101 (Spalte 3)
1101 (Spalte 14)
...gesammelt - so weiß mein Code immer, in welcher Spalte ein Raum-Eintrag beginnt, und in welcher Spalte der gleichnamige Raum-Eintrag endet - mit den Infos erste+letzte Spalte wird dann die Summenformel gebildet

4. Es gibt sowohl viele Raum-Einträge, die öfter vorkommen, z Bsp 1101, 1102, usw, und es gibt aber auch viele Raum-Einträge, die nur einmal vorhanden sind, z Bsp "Treppenhaus vor/Hinten", "Treppenhaus HH", usw.
Mein Code erzeugt nur dann eine Summenspalte, wenn es von Raum-Eintrag mehr als nur einen gibt.

5. So sind wir beim nächsten Schritt
Mein Code beginnt mit der letzten, genutzten Spalte in Zeile 1 und arbeitet sich dann bis zu Spalte 3 vor (Spalte 3 = die 1. Spalte für Raum-Einträge)
Jedesmal, wenn mein Code das Ende eines Raum-Eintrags findet, von dem es mehr als einen Eintrag gibt...
...wird in der Tabelle direkt neben der Spalte mit letztem Eintrag eine Spalte hinzugefügt
...es wird in Zeile 1 die Überschrift "Summe " & Raum-Eintrag" eingetragen
...immer in Zeile 5 (= 1. Datenzeile) wird die Summenformel eingetragen, die alle Nachbar-Spalten nach links mit selbem Raum-Eintrag umfasst
...die Formel aus Zeile 5 wird kopiert und bis in die letzte, genutzte Zeile eingetragen 
in deiner Bsp-Datei ist das eigtl Zeile 72, aber es wird nur bis Zeile 71 eingetragen, weil...
...in deiner Bsp-Datei gibt es in den gelben Zeilen keinen Summen-Eintrag
...somit entfernt mein Code auch wieder alle Formel-Einträge in allen gelben Zellen

So, das wars❗

Ich hoffe, meine vielen Stunden "Rumprobieren" waren nicht umsonst

Konnte ich helfen?

Ciao
Thorsten

[Klaus-Dieter]

Hallo,

der Aufbau deiner Datei ist, sagen wir mal suboptimal. Wenn du die Inhalte zum Beispiel um 90° drehen würdest, könntest du deine Summen mit der Standardfunktion Teilergebnis automatisch erzeugen. Habe das in der angehängten Datei auf dem Blatt Tabelle 2 mal dargestellt. Wenn das für dich ein denkbarer Weg wäre, kannst du dich melden, dann schreibe ich dazu mehr.

[ECM25]

Ich möchte meine tiefe Dankbarkeit gegenüber allen zum Ausdruck bringen. Der von Thorsten bereitgestellte Code wurde von mir übernommen, da er optimal zu meinen Tabellen passte.

[snb]

Es wäre besser zu automatisieren.
Verwende eine dynamische Tabelle

Code:

Sub M_snb()
  sn = Tabelle3.Cells(1).CurrentRegion
  ReDim sp(10000, 3)
 
  sp(0, 0) = "raum"
  sp(0, 1) = "position"
  sp(0, 2) = "wert"
  sp(0, 3) = "code"
 
  For j = 2 To UBound(sn)
    For jj = 3 To UBound(sn, 2)
      If sn(j, jj) > 0 And Left(sn(1, jj), 3) <> "Sum" Then
        n = n + 1
        sp(n, 0) = sn(1, jj)
        sp(n, 1) = sn(j, 2)
        sp(n, 2) = sn(j, jj)
        sp(n, 3) = sn(j, 1)
      End If
    Next
  Next

  With Sheets.Add()
    .Cells(1).Resize(n, 4) = sp
    .ListObjects.Add 1, .Cells(1).CurrentRegion, , 1
  End With
End Sub

[ECM25]

Hi,
vorneweg: Menschen und Datenerfassung/verwaltung sind nicht kompatibel miteinander
oder anders: du/ihr habt in der Überschriftenzeile (Raumnummern, -namen, -einträge) zu oft Tipp- und sogar richtige "ihr-habt-euch-total-verschrieben"-Fehler
Beispiele? = Gerne!
sind die Räume in den Spalten ALU:ALV...
VH BA 1
VH BA!
...wirklich unterschiedliche Räume, oder soll das ! im unteren Eintrag eigtl auch eine 1 sein?
oder die Räume in ALP:ALS...
"Vh"
"VH "
"VH "
"vh "
...sowohl in Groß- und Kleinschreibung, als auch mit und ohne Leerzeichen am Ende = soll das wirklich so?
der Eintrag in AIW...
Straßenseite
...scheint ok zu sein, aber...
...die Einträge in AIJ:AIU...
Straße
Zeit
...soll das wirklich so, oder soll "Zeit" vllt auch "seite" heißen und direkt an "Straße" folgen und nicht mit Entertaste getrennt?
Na ja, derlei und noch n paar andere Schreibfehler gibt es noch weitere
Aber wenn ich all das (und die anderen erforderlichen Bedingungen) richtig + vollständig berücksichtigt habe, dann bekommst du von mir die DELUXE-Lösung für deine Frage ❗ ??
Schau dir die Datei im Anhang mal an
 Nun zur Beschreibung meines Codes:
1. Du musst das Makro namens "sbSumCols" starten, um den Code auszuführen. Das Makro findest du in Modul1.
2. Im ersten Schritt werden erstmal alle existierenden Summen-Spalten in deiner Tabelle gelöscht. Denn es kann ja sein, dass im Lauf der Zeit noch weitere Spalten (neue Raumnummern) hinzukommen (oder auch gelöscht werden), und/oder bestehende Raumnummern wurden geändert/umbenannt. Es muss also eine Aktualisierung her = so wird also mit meinem Makro immer wieder auf's Neue "aufgeräumt"
3. So, jetzt enthält Zeile 1, beginnend in Spalte 3 nur noch Raumnummern und -namen.
Alle Einträge in Zeile 1, ab Spalte 3 werden in einer Array-Variablen gesammelt.
Das Besondere hier ist...
...es wird immer nur der erste Raum-Eintrag + Spalte und der letzte, selbe Raum-Eintrag + Spalte gesammelt
Bsp:
von den Raum-Einträgen...
1101 (Spalte 3)
1101 (Spalte 4)
1101 (Spalte 5)
...
1101 (Spalte 13)
1101 (Spalte 14)
...werden in der Arr-Var nur die Raum-Einträge + zugehörige Spalte...
1101 (Spalte 3)
1101 (Spalte 14)
...gesammelt - so weiß mein Code immer, in welcher Spalte ein Raum-Eintrag beginnt, und in welcher Spalte der gleichnamige Raum-Eintrag endet - mit den Infos erste+letzte Spalte wird dann die Summenformel gebildet
4. Es gibt sowohl viele Raum-Einträge, die öfter vorkommen, z Bsp 1101, 1102, usw, und es gibt aber auch viele Raum-Einträge, die nur einmal vorhanden sind, z Bsp "Treppenhaus vor/Hinten", "Treppenhaus HH", usw.
Mein Code erzeugt nur dann eine Summenspalte, wenn es von Raum-Eintrag mehr als nur einen gibt.
5. So sind wir beim nächsten Schritt
Mein Code beginnt mit der letzten, genutzten Spalte in Zeile 1 und arbeitet sich dann bis zu Spalte 3 vor (Spalte 3 = die 1. Spalte für Raum-Einträge)
Jedesmal, wenn mein Code das Ende eines Raum-Eintrags findet, von dem es mehr als einen Eintrag gibt...
...wird in der Tabelle direkt neben der Spalte mit letztem Eintrag eine Spalte hinzugefügt
...es wird in Zeile 1 die Überschrift "Summe " & Raum-Eintrag" eingetragen
...immer in Zeile 5 (= 1. Datenzeile) wird die Summenformel eingetragen, die alle Nachbar-Spalten nach links mit selbem Raum-Eintrag umfasst
...die Formel aus Zeile 5 wird kopiert und bis in die letzte, genutzte Zeile eingetragen 
in deiner Bsp-Datei ist das eigtl Zeile 72, aber es wird nur bis Zeile 71 eingetragen, weil...
...in deiner Bsp-Datei gibt es in den gelben Zeilen keinen Summen-Eintrag
...somit entfernt mein Code auch wieder alle Formel-Einträge in allen gelben Zellen
So, das wars❗
Ich hoffe, meine vielen Stunden "Rumprobieren" waren nicht umsonst
Konnte ich helfen?
Ciao
Thorsten


Hallo Thorsten, ich möchte eine zusätzliche Spalte erstellen, in der die Gesamtsumme aller einzelnen Summenspalten berechnet wird. In dieser manuell erstellten Spalte auf der linken Seite sollen alle Summen der verschiedenen Summenspalten zusammengezählt werden.

[Klaus-Dieter]

Hallo ECM25,

es hätte völlig gereicht, Oberschlumpf direkt anzusprechen. Ein Vollzitat seines "Romans" ist überflüssig und bläht den Beitrag nur unnötig auf. Über den unsinnigen Aufbau deiner Liste hatte ich mich ja schon durch einen alternativen Vorschlag vertieft.

[Oberschlumpf]

Hi Klaus-Dieter,

und DU kannst doch gar nicht beurteilen, was ein unsinniger Aufbau ist oder nicht, nur weil es dir nicht gefällt^^ Vielleicht muss der gegebene Aufbau genau so verwendet werden, und der Fragende hat auf die Änderung desselbigen gar keinen Einfluss.

Aber was das Voll-Zitat "meines Romans" betrifft, da stimm ich dir zu ...da wäre Weniger bestimmt Mehr gewesen

Ciao
Thorsten

---

Hallo Thorsten, ich möchte eine zusätzliche Spalte erstellen, in der die Gesamtsumme aller einzelnen Summenspalten berechnet wird. In dieser manuell erstellten Spalte auf der linken Seite sollen alle Summen der verschiedenen Summenspalten zusammengezählt werden.

Hi,

ich hab erst vor wenigen Minuten deine neue Frage gesehen.
Auf meine Antwort müsstest du bis mindestens morgen warten, da ich heute dazu leider keine Zeit mehr habe.
Aber wer weiß, vllt findet sich ja auch noch ein anderer Antworter, der mein Makro entsprechend anpassen könnte.

ähh..WO GENAU soll deine neue Spalte denn sein?
Ich denke = ganz links, also links neben der jetzigen Spalte A, in der nur "F" drin steht, oder?

Ciao
Thorsten

[Ralf A]

und DU kannst doch gar nicht beurteilen, was ein unsinniger Aufbau ist oder nicht, nur weil es dir nicht gefällt^^ Vielleicht muss der gegebene Aufbau genau so verwendet werden, und der Fragende hat auf die Änderung desselbigen gar keinen Einfluss.

Auch wenn DIR das nicht gefallen mag, ich denke schon, dass Klaus Dieter den Unterschied zwischen einer unsinnigen und einer sinnvollen Datenstruktur kennt. Und nein, ein unsinniger Aufbau muss niemals so verwendet werden. Er wird nur deshalb immer wieder gern verwendet, weil sich sachunkundige und lernunwillige Chefs oder Anwender daran klammern. Die Datenstruktur ist die Basis aller Folgeschritte. Ist der Anfang erst mal vermasselt, wird es nach hinten raus nur noch schlimmer. Deshalb muss und darf man sich als fachkundiger Entwickler keinen unsinnigen Anweisungen fügen. Es ist vielmehr die Pflicht derer, die sich auskennen, auf kommende Fehler und Risiken sowie auf Vorteile einer anderen Herangehensweise aufmerksam zu machen! 
Wie ich diesen Satz á la "ich hab keinen Einfluss..." verabscheue....  Das ist eine Ausrede für eigene Ahnungslosigkeit, Angst, oder einfach nur Faulheit (die sich später meist rächen wird)
Wer diesen Satz in seinem Fachgebiet verwendet, der sollte sich einen anderen Job suchen. Einen, bei dem er sein Wissen und Können geltend machen kann.