Office: (Office 2013) Spalten untereinander lückenlos

Hallo Zusammen,

folgende Beispieltabelle:



Code:

 

Ich baue keine Datei nach, die Zeit hat schon jemand investiert.
Ein Nachbau sieht bestimmt anders aus als das Original.
Ein Link zur Datei oder ein Tabellen Ausschnitt nicht als Bild wäre nicht schlecht.

Jul 2014

	C
29	Falsch
30	496

verwendete Formeln
Zelle	Formel	Bereich	N/A
C29	=C27=C28
C30	=SUMMEWENN([Bildschirm.xlsm]Statistik!$Q:$Q;"<" &HEUTE();[Bildschirm.xlsm]Statistik!$R)

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 18.07 einschl. 64 Bit

Benutze hier im Forum die Funktion zum hochladen. Falls Du die nicht benutzen möchtest beachte, von unsicheren Servern wie z.B. www.file-upload.net lade ich keine Datei runter. (lt. Einschätzung meines Virenprogramms)
Der Dateiname sollte was mit dem Problem zu tun haben.
Ich habe mir z.B. einen Ordner angelegt in dem ich alle Dateien aus dem Internet speichere. Bei Dateinamen wie Test..., Mappe…, Beispiel… wird eine vorhandene überschrieben.
Schaue hier, Dateiname im Beitrag
das ist nun das Word -Forum, das gilt hier aber auch.
Ein Bild in Excel geöffnet sieht anders aus als das Bild.
Ich habe kein Tool um ein Bild in Excel zu importieren.

warum steht Name1+ nicht dort.

 

Hi ?,
das ist nicht so ganz trivial...

Aber es gibt ja eine Suche, und es gibt Foren.
Bei office-loesung.de habe ich unter /ftopic297925_0_0_asc.php eine Formel von urs für das lückenlose Untereinanderschreiben vieler Spalten gefunden.
Die habe ich nur ein wenig angepasst:

	A	B	C	D	E	F	G
16		Name1	Name2	Name3	Name4	Name5	Name6
17
18		x			yyyy
19			x		M		x
20				x	x
21
22
23
24		Name1	x
25		Name2	x
26		Name3	x
27		Name4	yyyy
28		Name4	M
29		Name4	x
30		Name6	x
31

Zelle	Formel
B24	{=WENN(ZEILE(B1)>ANZAHL2($B:$G);"";INDEX($A:$G;16;GANZZAHL(KKLEINSTE(WENN($B:$G<>"";SPALTE($B:$G)+ZEILE($B:$G)%%);ZEILE(A1)))))}
C24	{=WENN(ZEILE(B1)>ANZAHL2($B:$G);""; INDEX($A:$G;10000*REST(KKLEINSTE(WENN($B:$G<>""; SPALTE($B:$G)+ZEILE($B:$G)%%);ZEILE(A1));1); GANZZAHL(KKLEINSTE(WENN($B:$G<>""; SPALTE($B:$G)+ZEILE($B:$G)%%);ZEILE(A1)))))}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

 

Hi,
die Formel in B24 geht ein wenig einfacher:

	B
23
24	Name1

Zelle	Formel
B24	{=WENN(ZEILE(B1)>ANZAHL2($B:$G);"";INDEX($A:$G;1;GANZZAHL(KKLEINSTE(WENN($B:$G<>"";SPALTE($B:$G)+ZEILE($B:$G)%%);ZEILE(B1)))))}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

 

Hallo Zusammen,

erstmal vielen Dank für die bisher getätigte Mühe. Ich habe mich wohl an vielen Stellen nicht klar genug ausgedrückt und mit meiner Beispieltabelle nicht genug auf das Ziel hingedeutet.

Deswegen zeige ich meine Originaldatei und entschuldige mich für bereits investierte Zeit.

Folgende Tabelle gibt es für jeden Monat. Der Kalender wird automatisch generiert (in E1 bis AI1 steht 01.01.2014, 02.01.2014 usw., nur anders formatiert), habe die Formeln jetzt nicht über jeannie anzeigen lassen:

Januar

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Januar				1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
2					Mi	Do	Fr	Sa	So	Mo	Di	Mi	Do	Fr	Sa	So	Mo	Di	Mi	Do	Fr	Sa	So	Mo	Di	Mi	Do	Fr	Sa	So	Mo	Di	Mi	Do
3					1	1	1	1	1	2	2	2	2	2	2	2	3	3	3	3	3	3	3	4	4	4	4	4	4	4	5	5	5	5
4	Personalnr.	Name	Vorname	Schiff
5	12						Test1	Test1				Test3	Test3
6	21					Test2

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Dabei möchte ich betonen, dass die Eintragungen im Kalender Tabellenblatt nicht immer gleich sind. Andere Tage können auch Eintragungen beinhalten. Auch wichtig, die lückenlose Aufzählung in einer Spalte untereinander (zwischen 04.01.2014 und 08.01.2014 ist keine leere Zelle!).
Würde ich jetzt im Tabellenblatt "Übersicht" in Zelle A2 "21" eintippen, sollte sich das Ergebnis entsprechend ändern.

Falls dies eine sehr umfangreiche Lösung erfordert, und auch in Anbetracht der bereits investierten Zeit, sagt es mir bitte, ich überlege mir sonst vielleicht nochmal einen "einfacheren" Aufbau, mit dem ich dann vielleicht auch alleine umgehen kann.

Erich, danke für deine Mühe. Die Zahlen 1, 5, 9 sollten als Beispiel für die hier aufgeführten Personalnummern stehen. Name1+ ist tatsächlich unwichtig und ich gebe dir recht, verwirrt nur.

Ich habe die Datei auch nochmal hochgeladen. Heißt genau wie der Thread.

MfG

 

Hallo

hier mal eine Makrolösung
Code:

MfG Tom

 

Hi,
alles angenommen! *mrcool

Schau dir mal die angehängte Mappe an.

Schönen Sonntag!

 

Danke euch beiden schon mal, dass ihr euch damit befasst. Kann es mir grad noch nicht angucken, gebe aber gleich morgen früh Rückmeldung.

Vielen Dank und schönen Abend noch (und Sonntag).

MfG

 

Sorry für Doppelpost, aber konnte irgendwie nicht mehr editieren.

@Erich:
Wow, das sieht wirklich kompliziert aus, aber so, wie ich dir die Tabelle geschildert hab, machen deine Formeln genau das, was sie sollen.
Vielleicht hörst du schon an der Formulierung, dass ich vergessen hab etwas zu erwähnen. Aber keine Sorge, konnte deine genialen Formeln anpassen.

Hab nämlich nicht deutlich gemacht, dass es mehr als drei Personalnummern geben kann. Aber konnte ja die Zellbereiche in deiner Formel ganz einfach dementsprechend anpassen. Wahrscheinlich schon mit diesem Hintergedanken von dir geplant*biggrin.gif*.

Auf jeden Fall hab ich das grade getestet:
- willkürlich Datensätze (Personalnummern) und Einträge im Kalender hinzugefügt
- Monat auf Februar umgeändert
= alles funktioniert so, wie es soll!*boah

Aber das mit den KWn verstehe ich nicht, nach DIN irgendwas/irgendwas gibt es doch auch 53 KWn (?)

Ich guck mir mal im Laufe der Zeit deine Lösungen genauer an, kann man bestimmt was draus lernen.
Also kann mich nur 1000mal bedanken, top!

@crazyTom

find ich richtig gut auch ne Makrolösung dafür zuhaben, vor allem, weil das auch bestimmt weniger Rechenleistung braucht bei hoher Datendichte.

Ich gehe davon aus, dass deine Lösung richtig ist, aber kriege sie irgendwie nicht zum laufen (kenne mich kaum mit VBA aus).
Wusste nicht zu 100%, wo das Makro hingehört. Hab mir aber gedacht, dass es ins entsprechende Tabellenblatt gehört.
Als das nicht geklappt hat, habe ich das Makro in "DieseArbeitsmappe" kopiert.
Als das nicht geklappt hat, habe ich das Makro in ein Klassenmodul kopiert.
Als das nicht geklappt hat, habe ich das Makro in ein Modul kopiert.

*grins du siehst, es klappt immernoch nicht, und ich denke, dass Problem bin ich
(Ich gehe davon aus, dass ich den Eintrag in Zelle A2 und B2 auf Tabellenblatt "Übersicht" ändern kann und mir dementsprechend das Ergebnis angezeigt wird, sofern das Makro aktiv ist.)

 

Hallo

das Makro gehört in ein Modul
auf dem Tabellenblatt "Übersicht" einer Schaltfläche das Makro zuweisen

MfG Tom

 

Hi,
das mit den mehr als drei Personalnummern erledigt sich doch ganz von selbst, ganz ohne Hintergedanken:

Wenn du Januar-Zeile 5 oder 6 duplizierst (z. B. mit der Maus kopierst), passt Excel die Formeln automatisch an.

Und die Geschichte mit den 52 oder 53 KWn meinte ich so:
Deine Formel (in meiner Mappe in Einstellungen!C3 vor dem "immer") rechnet IMMER 52 aus. Das ist wohl falsch.

In Einstellungen!F:H habe ich die KW-Anzahl/Jahr mal für eineige Jahre rechnen lassen.

Nochmal schönen Sonntag!

 

\@CrazyTom

Ah, danke.
Allerdings gibt es noch ein Problem.

Januar

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	Januar				1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
2					Mi	Do	Fr	Sa	So	Mo	Di	Mi	Do	Fr	Sa	So	Mo	Di	Mi	Do	Fr	Sa	So	Mo	Di	Mi	Do	Fr	Sa	So	Mo	Di	Mi	Do	Fr
3					1	1	1	1	1	2	2	2	2	2	2	2	3	3	3	3	3	3	3	4	4	4	4	4	4	4	5	5	5	5	5
4	Personalnr.	Name	Vorname	Schiff
5	12						Test1	Test1				Test3	Test3
6	21					Test2
7	7								f

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Hatte vorher das Ergebnis mit der Nummer 12 generiert (über deinen Button). Sofern die Einträge einer Nummer im Kalender < Einträge einer Nummer davor, zeigt er zwar das richtige Ergebnis an, aber lässt die alten Ergebnisse noch mit drin stehen.

@ Erich

Oh hatte da irgendwie Schwierigkeiten*eek.gif*
Die Formeln waren ja im gelben Kasten schon für weitere Eintragungen nach unten gezogen, aber sobald ich eine willkürliche neue Nummer hinzugefügt hatte, zeigte der mir nichts an, weil doch die Zellbezüge fixiert waren $$:

Januar

	A	B	C	D
1	Personalnummer	Monat
2	7	Januar
3
4	Ergebnis:
5	7	05.01.2014	f	richtig
6		04.01.2014	Test1	FALSCH
7		08.01.2014	Test3	FALSCH
8		09.01.2014	Test3	FALSCH

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

8

96

f

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen! Excel Jeanie HTML 4.8

$E$5:$AI$7

Die 7 hatte mich erst limitiert.

Und zur KW. Du hast recht, meine Formel spuckt immer 52 aus. Ist ja komisch, hab die Formel dazu von:
http://www.excelformeln.de/formeln.html?welcher=7
Ganz unten beide ausprobiert, beide zeigen immer 52 an?
Bin ich jetzt zu bescheuert um die Formeln zu kopieren und ist da ein Fehler auf der Seite?

Danke euch beiden!

 

Hallo

so werden erst die alten Einträge gelöscht
Code:

MfG Tom

 

Hey Tom,

ziemlich cool, danke*Smilie

 

Hi Maesthro(?),
"aber sobald ich eine willkürliche neue Nummer hinzugefügt hatte":
WO und wie hast du das getan?

In Januar!AK werden die Werte für PNR ja nun gerechnet, da stehen Fornmeln in den Zellen.
Eine neue PNR taucht automatisch auf, wenn in Spalte Januar!A eine neue PNR vorkommt.

Die 7 in den Formeln in Januar!AK:AM wird automatisch zur 8, wenn man zwischen den Zeilen 5 und 8 eine Zeile einfügt. Ob mit oder ohne "$", ist dabei egal.
Sicherheitshalber kopiert man dann die Formeln von AK:AM noch mal nach unten.*wink.gif*

Franz' Formel in excelformeln-Nr. 7 ist schon ok, und du hast sie auch richtig kopiert.*biggrin.gif*
Aber es ist die falsche Formel für deine Aufgabe. In Nr. 7 steht in A1 ein Datum, bei dir nur ein Jahr als vierstellige Zahl.
Was ist JAHR(2015)?
Wenn du schreibst
=52+(TAG(346-REST(DATUM(B1;1;2);7) +(DATUM(B1;1;1)))