Microsoft Excel

Herbers Excel/VBA-Archiv

Wert in Spalte suchen

Betrifft: Wert in Spalte suchen von: Manfred
Geschrieben am: 23.10.2020 00:32:40

Hallo und guten Abend,

wie kann ich in die folgende Formel umbauen, damit in der Tabelle Arbeitszeiten die gesamte Spalte A durchsucht wird?

=WENN(TAG(Arbeitszeiten!$A218)=12;WENN(JAHR(Arbeitszeiten!$A218)+MONAT(Arbeitszeiten!$A218) /100=JAHR(blattname())+MONAT(blattname())/100;Arbeitszeiten!$C218;);"")

Ich habe schon einiges mit Index und Verweis versucht, aber meine wenigen Excel Kenntnisse reichen leider nicht aus.

Vielen Dank für hilfreiche Antworten
LG Manfred

Betrifft: AW: Wert in Spalte suchen
von: SF
Geschrieben am: 23.10.2020 00:36:51

Hola,

lade bloß keine Exceldatei hoch und erkläre bloß nicht was du genau vor hast!
Gruß,
steve1da

Betrifft: AW: Wert in Spalte suchen
von: Manfred
Geschrieben am: 23.10.2020 00:45:14

Sorry,

ich dachte, es wäre mit einem einfachen Eingriff möglich.

so ungefähr:

WENN(TAG(Index(Arbeitszeiten!A:A))=12;WENN(JAHR(Index(Arbeitszeiten!A:A)+MONAT(Index(Arbeitszeiten!A:A)/100=JAHR(blattname())+MONAT(blattname())/100;Index(Arbeitszeiten!A:A;);"")


Ich versuche meine Tabelle zu bearbeiten und sie einzustellen.

Gruß Manfred

Betrifft: AW: Wert in Spalte suchen
von: Manfred
Geschrieben am: 23.10.2020 02:25:33


Entschuldige,

ich frage nicht oft in Foren nach, ich google mir das meiste zurecht.
Habe die Tabelle hochgeladen, vielleicht schaust du nochmal drüber.

Vielen Dank und Gruß

Betrifft: AW: Wert in Spalte suchen
von: Luschi
Geschrieben am: 23.10.2020 07:50:23

Hallo Manfred,

hochladen allein reicht nicht, Du mußt die angezeigten Link-Adresse auch hier bekanntgeben.

Gruß von Luschi
aus klein-Paris

Betrifft: AW: Wert in Spalte suchen
von: Manfred
Geschrieben am: 23.10.2020 02:00:41


Hallo,

ich versuche mit dieser Formel aus der Tabelle "Arbeitszeiten" die Daten aus den Spalten C und E in die Tabelle "Okt 2020" in die Zeilen mit dem richtigen Kalendertag zu übernehmen.
Die nächste Tabelle sollte also "Nov 2020" benannt werden und die Formeln müssen dann von Hand oder VBA eintragen werden.

Ich hoffe, daß es einigermaßen verständlich ist.
Im Moment geht es nur um die Daten der beiden Spalten.

https://www.herber.de/bbs/user/141031.xlsm

Im voraus vielen Dank
Grüße Manfred

Betrifft: AW: Wert in Spalte suchen
von: Hajo_Zi
Geschrieben am: 23.10.2020 05:42:32

Hallo Manfreg,

es wäre einfacher mit Datum in Spalte A.

Okt 2020
 BCDEF
35'248:00'----- 12:304,50
36'259:0000:3017:007,50
37'267:3000:3016:008,00
38'2710:0000:3016:306,00
39'2812:00'----- 12:300,500000000000001
40'298:0000:3016:308,00
41'308:0000:3019:0010,50
42'317:3000:3014:306,50

verwendete Formeln
Zelle Formel Bereich N/A
C35:E42=WENNFEHLER(SVERWEIS(DATUM(2020;10;$B35);Arbeitszeiten!$A:$I;SPALTE();0);"")  
F35:F42=WENNFEHLER(SVERWEIS(DATUM(2020;10;$B35);Arbeitszeiten!$A:$I;8;0);"")  

Zahlenformate
Zelle Format Inhalt
B36 '@  25
B37 '@  26
B38 '@  27
B39 '@  28
B40 '@  29
B41 '@  30
B42 '@  31
C35, C40:C41 'h:mm;@  0,333333333333336
C36 'h:mm;@  0,375
C37, C42 'h:mm;@  0,3125
C38 'h:mm;@  0,416666666666667
C39 'h:mm;@  0,5
D35, D39 'h:mm;@  -----
D36: D38, D40: D42 'h:mm;@  2,08333333333333E-02
E35, E39 'h:mm;@  0,520833333333333
E36 'h:mm;@  0,708333333333332
E37 'h:mm;@  0,666666666666666
E38, E40 'h:mm;@  0,687499999999999
E41 'h:mm;@  0,791666666666664
E42 'h:mm;@  0,604166666666666
F35 '0,00  4,49999999999994
F36 '0,00  7,49999999999997
F37 '0,00  7,99999999999998
F38 '0,00  5,99999999999997
F39 '0,00  0,500000000000001
F40 '0,00  7,99999999999991
F41 '0,00  10,4999999999999
F42 '0,00  6,49999999999998
Zellen mit Format Standard werden nicht dargestellt
http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://Hajo-Excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 25.21 einschl. 64 Bit



GrußformelHomepage

Betrifft: AW: Wert in Spalte suchen
von: Manfred
Geschrieben am: 23.10.2020 11:28:31

Hallo und guten Tag,

mit Hajos Hinweis kann ich leider nichts anfangen.

Ich erkläre meinen Wunsch mal ausführlicher:

In der Tabelle "Arbeitszeiten" werden fortlaufend Zeiten eingetragen. Die Tabelle ist ziemlich umfangreich, mit Hilfsspalten und die Berechnung wird mit VBA durchgeführt. Das funktioniert alles ohne Probleme.

An jedem Monatsanfang soll die Tabelle vom vorherigen Monat kopiert und in den jetzigen Monat umbenannt werden. Also Okt 2020 wird kopiert und anschließend nach Nov 2020 umbenannt. Die Vorlage sollte nicht geändert werden. Formeln oder Zellformatierungen kann ich ändern.
Wenn ich die richtige Formel in allen Zeilen (12-42) in den Spalten C und E erarbeitet habe, muss die Tabelle nicht mehr geändert werden.

Mein Vorhaben ist das:
In der Tabelle Okt 2020, bzw. Nov 2020 usw. wird in den Zeilen 12-42 (die Kalendertage 1-31) mit dem Blattnamen (Monat und Jahr) verglichen (nur die Zeiten z.B. vom Nov (in der Tabelle Arbeitszeiten) werden in der Tabelle "Nov 2020" angezeigt).
WENN(JAHR(Arbeitszeiten!$A19)+MONAT(Arbeitszeiten!$A19) /100=JAHR(blattname())+MONAT(blattname())/100

Es soll in jeder Zeile verglichen werden (in den Kalendertagen 1-31 in Nov 2020) ob es sich um den richtigen Tag in "Arbeitszeiten" handelt.
WENN(TAG(Arbeitszeiten!$A19)=12 für den 12.Nov
WENN(TAG(Arbeitszeiten!$A19)=13 für den 13.Nov
WENN(TAG(Arbeitszeiten!$A19)=14 für den 14.Nov jeweils in der richtigen Zeile in "Nov 2020"

Mein Problem ist, daß dieser Teil der Formel (Arbeitszeiten!$A19) an allen Stellen geändert werden muss, so dass nicht nur die Zelle $A19 verglichen wird, sondern die ganze Spalte A.

Besser kann ich es leider nicht erklären, hoffe trotzdem auf eure Hilfe.

Die aktualisierte Tabelle ist hier zu finden:
https://www.herber.de/bbs/user/141038.xlsm

Vielen Dank schon mal im Voraus
Manfred

Betrifft: AW: Wert in Spalte suchen
von: Manfred
Geschrieben am: 24.10.2020 23:38:50

Guten Abend,

vielen Dank für die Antworten.
Meine Formel sieht nun so aus und funktioniert.

=WENNFEHLER(WENN(UND(JAHR(blattname())+MONAT(blattname())/100;ZÄHLENWENN(Arbeitszeiten!B:B; ($G23 + (MONAT(blattname())/100) & JAHR(blattname()))));INDIREKT($L23);"");"")


Nun habe ich das Problem, dass in der Tabelle vom Oktober, Sept. bzw. August die Arbeitszeiten nicht richtig berechnet werden.

Vielleicht kann mal jemand drüber schaun.
Danke, LG Manfred

https://www.herber.de/bbs/user/141061.xlsm

Betrifft: AW: Wert in Spalte suchen
von: Manfred
Geschrieben am: 25.10.2020 11:17:06


Problem in der Berechnung von Uhrzeiten gelöst

Danke

Beiträge aus dem Excel-Forum zum Thema "Wert in Spalte suchen"