Office: Nebeneinanderliegende Zellen zählen

Hallo zusammen,

ich habe eine Pivot mit tagweisen Datumsangaben in den Spalten erstellt. Nun möchte ich eine Möglichkeit finden, wie oft (je Zeile)
- eine einzelne Zelle (ohne Nachbarzelle) gefüllt ist
- zwei Zellen nebeneinander gefüllt sind
- drei Zellen nebeneinander gefüllt sind
- ...

Leider zerbreche ich mich den Kopf darüber und hoffe, mir kann hier geholfen werden.
Vorweg herzlichen Dank und viele Grüße

 

Hallo,

(D)eine Beispieldatei und zwar mit der Datenquelle Deiner Pivotauswertung wäre hilfreich(er)
Außerdem wird es von den wenigstens potentiellen Helfern gern gesehen, wenn Du Deine Fragestellung ohne entsprechende Verlinkung in mehreren Foren einstellst. Hole beides in den Foren nach und dann hoffe darauf, dass sich einer Deiner Fragestellung annimmt.

 

Hi,

danke erst einmal für deinen Input. Die Pivot habe ich bereits als einfaches Arbeitsblatt übertragen. Die Vereinfachung meiner Datei anbei.
Noch einmal zur Verdeutlichung: Die 1 bedeutet jeweils, dass Person A/B/C am jeweiligen Datum erschienen ist. Ich möchte auswerten, wie oft die jeweilige Person
- einen einzelnen Tag
- zwei Tage in Folge
- drei Tage in Folge
- ...
erschienen ist.

Vielen Dank vorab!

 

Hi,

also unter einer Pivot-Tabelle verstehe ich etwas anderes. Dies ist eine Kreuztabelle.

Wenn es VBA sein darf:

Code:

Function Nachbarn(Bereich As Range) As Variant
Dim i As Long
Dim k As Long
Dim t As Variant
ReDim t(1 To 1)
For i = 1 To Bereich.Cells.CountLarge
    If Not IsEmpty(Bereich.Cells(i)) Then
        k = k + 1
    Else
        If k > 0 Then
            If k > UBound(t) Then ReDim Preserve t(1 To k)
            t(k) = t(k) + 1
            k = 0
        End If
    End If
Next i
Nachbarn = t
End Function

Diese Funktion gibt die Anzahl zusammenhängender Werte als Matrix zurück. Der erste Wert ist die Anzahl der Einzelwerte, der zweite die Anzahl der 2er, der 3. die Anzahl der 3er usw.
Bei neuem Excel ist es eine Spill-Funktion, bei altem Excel muss man sie als Matrixfunktion mit entsprechendem erwartetem Ausgabebereich eingeben.

Der Aufruf mit =Nachbarn(B2:AF2) führt zur Ausgabe von 1 1 0 0 2
Soll heißen 1x 1 Tag, 1x 2 Tage, 0x 3 Tage, 0x 4 Tage, 2x 5 Tage

 

Hallo ebjo11,

bis jetzt hast Du meine Hinweise nur zT (Einstellung einer Datei) realisiert, wie ich mich gerade überzeugt habe. Verweise wenigsten jetzt in den anderen Foren auf dieses hier.

Es gibt übrigens zu Deiner Problemstellung auch Lösungen ohne VBA. Dann am einfachsten mit einer Hilfstabelle, in welcher nur Formeln eingesetzt werden müssen, die nur + und * bedürfen. Für die Ergebnisauswertung reicht dann auch nur eine einfache Formel mit lediglich ZÄHLENWENNS() aus.

 

Vielen Dank hierzu erst einmal! Da ich mich erst in die VBA-Thematik einarbeiten werde, kann ich deinen Hinweis auf die Schnelle noch nicht umsetzen. Dennoch vielen Dank!

Auf die Lösung ohne VBA komme ich gern zurück (dafür habe ich auch den anderen Thread geschlossen mit Verweis auf dieses Forum ;) ). Ich habe mir bereits Hilfstabellen erarbeitet, steuere hier allerdings in verschiedene Sackgassen. Für Unterstützung bin ich dankbar!

Viele Grüße

 

Hallo ebjo11,

nun dann sieh mal. Beachte lediglich, dass in nachstehender Tabelle A9:A11 nichts steht (also auch nicht ="")
Die Formel B9 und B11 nach rechts und unten ziehend kopieren:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1.1	2.1	3.1	4.1	5.1	6.1	7.1	8.1	9.1	10.1	11.1	12.1
2	Person A		1	1	1	1	1
3	Person B	1	1	1						1	1
4	Person C					1	1	1				1
5
6
7
8	Hilfstabelle
9		0	1	2	3	4	5	0	0	0	0	0	0
10		1	2	3	0	0	0	0	0	1	2	0	0
11		0	0	0	0	1	2	3	0	0	0	1	0
12
13	Person/ Anzahl	1	2	3	4	5	6	7	8	9	10	11	12
14	Person A	1	1			2
15	Person B	3	2	1
16	Person C	1		1

Zelle	Formel
B9	=(B2+A9)*B2
B14	=ZÄHLENWENNS($C9:$AG9;0;$B9:$AF9;B$13)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

 

Hi,

Das Umsetzen ist nicht schwer. Kopiere meinen Code in ein allgemeines Modul.

Dazu
- Alt-F11 drücken (öffnet den VBA-Editor).
- im Projekt-Explorer irgendwo innerhalb deiner Datei (=Projekt) ein Rechtsklick und Einfügen --> Modul auswählen

- es öffnet sich ein Fenster und dort kopierst du den Code hinein


Jetzt steht dir die Funktion Nachbarn() so wie jede normale Excel-Funktion zur Verfügung. Und immer daran denken, dass nun die Datei als *.xlsm gespeichert werden muss.

Code:

Tabellenblattname: Tabelle1
┌———┬—————————┬——————————┬——————————┬——————————┬——————————┬——————————┬——————————┬——————————┐
│   │    A    │    B     │    C     │    D     │    E     │    F     │    G     │    H     │ by Rₐⁿgₑ²ᵀₑˣT
├———┼—————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┤
│ 1 │         │01.01.2023│02.01.2023│03.01.2023│04.01.2023│05.01.2023│06.01.2023│07.01.2023│
├———┼—————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┤
│ 2 │Person A │          │         1│         1│         1│         1│         1│          │
├———┼—————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┤
│ 3 │Person B │         1│         1│         1│          │          │          │          │
├———┼—————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┤
│ 4 │Person C │          │          │          │          │         1│         1│         1│
├———┼—————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┤
│ 5 │         │          │          │          │          │          │          │          │
├———┼—————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┤
│ 6 │Tage     │         1│         2│         3│         4│         5│         6│          │
├———┼—————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┤
│ 7 │Person A │         1│         1│         0│         0│         2│          │          │
├———┼—————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┤
│ 8 │Person B │         3│         2│         1│          │          │          │          │
├———┼—————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┼——————————┤
│ 9 │Person C │         3│         0│         1│          │          │          │          │
└———┴—————————┴——————————┴——————————┴——————————┴——————————┴——————————┴——————————┴——————————┘

 

Hallo,

in Excel365 funktioniert das Ganze auch ohne VBA oder spezielle Hilfsformeln:
in Zelle AH1 die Nummerierung von 1 bis 6: =SEQUENZ(1;6;1)
in Zelle AH2: =LET(t;TEXTVERKETTEN("-";FALSCH;"";$B2:$AF2;""); su;"--"&WIEDERHOLEN("1-";AH$1)&"-"; ar;TEXTTEILEN(t;su); ANZAHL2(ar)-1)
und diese in den Bereich AH2:AM4 kopieren (durch Ziehen am Formelkopierpunkt).

 

Hallo,

ich muss leider meine Formel etwas korrigieren - war ein Fehler enthalten (in Variable ar):
Formel in Zelle AH2:
=LET(zl;$B2:$AF2; ct;AH$1; z;"1"; t;"-"; tt;t&t;
txt;WECHSELN(TEXTVERKETTEN(t;0;"";zl;"");tt;tt&tt); su;tt&WIEDERHOLEN(z&t;ct)&t; a;TEXTTEILEN(txt;su); ANZAHL2(a)-1)

 

Hallo Anton,

noch ist allerdings unklar, ob ebjo11 auch eine aktuelle Excelversion im Einsatz hast.

In diesem Zusammenhang eine Frage, die Du mir vielleicht beantworten kannst. Kann man aus einer Exceldatei erkennen, mit welcher XL-Version diese zuletzt abgespeichert wurde und wenn ja wie?

 

Hi Werner,

ich bin mir nicht sicher, aber wenn ich eine von dir eingestellte Datei in 7Zip öffne und dort dann docProps\app.xml anschaue, finde ich ganz am Ende folgenden Eintrag:

Code:

<AppVersion>14.0300</AppVersion>

Wenn ich diese Datei dann mit meinem Excel speichere und mir dieselbe Stelle anschaue, dann steht dort

Code:

<AppVersion>16.0300</AppVersion>

Das scheint mir dann doch ein Hinweis auf die Excel-Version zu sein. Ich verwende

 

Hallo Helmut,

zunächst erst einmal vielen Dank für diese Deine Info. Diese schien mir nach nur Lesen dessen als die Lösung für das von mir angefragte zu sein. Doch meine anschließend geführten Untersuchungen haben dies in Frage gestellt.

Da ich für Formellösungen meistens noch XL2010 einsetze und diese von MS als Version 14.0.7268.500 deklariert ist erstellt diese Dateien, die mit <AppVersion>14.0300</AppVersion> in der Datei gekennzeichnet sind. Soweit noch i.O.

Wenn ich PQ einsetze, nutze ich meine XL2016 er Version. Diese ist bei mir deklariert als Version 2301 (Build 16026.20146)
Die damit von mir erstellten Dateien werden mit <AppVersion>16.0300</AppVersion> gekennzeichnet. Also genau so wie Du es für Deine XL365 Version angegeben hast. Damit wäre aber über das von Dir aufgezeigte, leider noch kein eindeutige Zuordnung der erstellenden XL-Version möglich, da Du ja für Deine Dateien auch <AppVersion>16.0300</AppVersion> festgestellt hast.

Wie erklärt sich das?

 

Die Version 16.03 ist danach nicht weiter angestiegen. In einem Forum gab es eine Diskussion dazu. Schlimmer Murks! Denn wozu gibt es dann solche Informationen, wenn sie in die Irre führen ...

 

Hallo lupo1,

aber nicht nur da sondern auch bei den anderen Versionsangaben.
Meine XL2016:

Und Helmut gibt für seine XL365 an:

Wer kann da noch den Durchblick behalten?

[Nachtrag:] Findest Du noch den thread, wo bereits hierzu diskutiert wurde? Könntest Du dann bitte den Link zu diesem hier einstellen?