Excel-Quickies (Vol 32) 6

Artikelbild-201
Ein paar schnelle Excel-Tipps für zwischendurch.
 

Weihnachten rückt immer näher und entgegen jeder Vernunft wird die Zeit immer hektischer. Daher gibt es heute wieder ein paar schnell umsetzbare Excel-Tipps, die dich nicht unnötig lange vom Geschenkekauf abhalten sollen.

In diesem Beitrag zeige ich dir Tipps zu folgenden Themen:

Da ist sicher auch für dich etwas dabei.


Excel-Quickie Nr. 106: Der erste Tag zu einer Kalenderwoche

Ich hatte vor längerer Zeit einen Artikel dazu geschrieben und dort eine relativ komplizierte Formel vorgestellt. Der heutige Tipp kommt von meinem Leser Armin Miedl, der eine deutlich einfachere Lösung für das Problem für uns hat.

Angenommen, das Jahr steht in B3 und die Kalenderwoche in B4. Dann liefert folgende Formel (bei mir in Zelle B7) ein Datum, das auf jeden Fall schon mal in der gewünschten Woche liegt:
=DATUM(B3;1;4)+7*(B4-1)
Der 4. Januar ist immer in KW1, deshalb ist der berechnete Tag immer in der gewünschten Woche.

Und nun lässt sich einfach der erste Tag in der betreffenden Woche ermitteln:
=B7-WOCHENTAG(B7;2)+1

Der erste Tag einer Kalenderwoche

Der erste Tag einer Kalenderwoche

Die übrigen Tage kann man mit +1 berechnen.

Vielen Dank an Armin für diese clevere Lösung!


Excel-Quickie Nr. 107: In welcher Kalenderwoche liegt der heutige Tag?

Du möchtest wissen, was wir gerade für eine Kalenderwoche haben, hast aber leider keinen Kalender zur Hand?
Zum Glück gibt’s ja Excel. Diese Frage lässt sich fast wortwörtlich in eine Formel packen:
=KALENDERWOCHE(HEUTE())

Die aktuelle Kalenderwoche

Die aktuelle Kalenderwoche

Kurz und schmerzlos. Ein echter Quickie eben!

Noch ein paar kleine Hinweise:
Der Funktion KALENDERWOCHE kann man als zusätzlichen Parameter einen Zahl-Typ übergeben. Damit wird gesteuert, mit welchem Wochentag die KW beginnen soll. Wer hier nach dem ISO-Standard rechnen möchte, nimmt den Zahltyp 21:
=KALENDERWOCHE(HEUTE();21)

Benutzern von Excel 2013 und 2016 steht darüberhinaus mit ISOKALENDERWOCHE eine weitere Funktion zur Verfügung, die ohne diesen Zusatzparameter das richtige Ergebnis liefert:
=ISOKALENDERWOCHE(HEUTE())

Damit wird die Kalenderwoche nach ISO-Norm bestimmt. Hier der Vergleich der beiden Funktionen und der Wirkung der Zahl-Typen:

Vergleich KALENDERWOCHE vs. ISOKALENDERWOCHE

Vergleich KALENDERWOCHE vs. ISOKALENDERWOCHE


Excel-Quickie Nr. 108: Unnötige Leerzeichen entfernen

Ein Ärgernis, das gerne im Umgang mit SVERWEIS & Co. auftritt, sind unnötige Leerzeichen in den abzugleichenden Spalten. Folgendes Szenario:

Die Werte aus zwei Tabellen sollen gegeneinander abgeglichen werden, z.B. um für eine Artikelnummer aus der einen Liste den Verkaufspreis aus einer anderen Liste zu finden. Die Daten dieser anderen Liste wurden jedoch nicht direkt in Excel erfasst, sondern stammen aus einem externen System und wurden in Excel importiert.

Ein Abgleich der Artikelnummern über einen SVERWEIS scheitert aber aus zunächst unerfindlichen Gründen, obwohl die gesuchte Artikelnummer definitiv in beiden Tabellen enthalten ist:

Wenn der SVERWEIS scheitert...

Wenn der SVERWEIS scheitert…

Wenn man für eine Artikelnummer oben in die Bearbeitungszeile klickt, sieht man auch die Wurzel des Übels. Am Ende der Artikelnummer hängen ein paar Leerzeichen:

...kann es an Leerzeichen liegen

…kann es an Leerzeichen liegen

Und damit sind für den SVERWEIS scheinbar identische Artikelnummern eben nicht mehr identisch.

Du kannst jetzt entweder alle Leerzeichen umständlich „zu Fuß“ löschen.

Oder du verwendest die Quickie-Lösung mit der GLÄTTEN-Funktion. Hiermit lassen sich kurz und schmerzlos sämtliche unnötigen Leerzeichen am Ende (und auch am Anfang!) entfernen. Jetzt muss der SVERWEIS nur noch auf die geglättete Artikelnummer angewendet werden, und schon funktioniert’s:

Leerzeichen entfernen mit der GLÄTTEN-Funktion

Leerzeichen entfernen mit der GLÄTTEN-Funktion

Und das war’s auch schon wieder für heute. Viel Spaß beim Ausprobieren und noch eine schöne Adventszeit!

 

Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

6 Gedanken zu “Excel-Quickies (Vol 32)

  • Avatar-Foto
    Andi

    Hallo Martin,
    bei solchen Aktionen haue ich nachher immer die Formeln raus, da sie in entsprechender Menge zur Bremse werden können – soll heißen: Datein importieren (wie auch immer) – Formel runterziehen – strg+c – rechtsklick – als Werte einfügen. Lässt man idealerweise in der ersten (Daten)-Zeile (wir haben natürlich eine eindeutige Spaltenüberschrift 😉 ) die Formel stehen und ersetzt nur Datenzeile 2-…), hat man sie später wieder zur Verfügung, denn eine Zeile selbst mit umfangreichen Sverweisen bemerkt man nicht, 100.000 dann schon.

    @Kalenderwoche: Jaja, Excel kann man echt für fast alles nutzen *gg* Da ist die Liste an Dingen, die man NICHT mit Excel bewältigen kann vermutlich deutlich kürzer.

    lg Andi

    • Avatar-Foto
      Martin Weiß

      Hallo Andi,

      ja, anschließend die Formeln durch Werte ersetzen ist eine gute Idee. Ist besser für die Performance und vermeidet Ärger, wenn man auf die Idee kommt, die Originalwerte zu löschen…

      Vielleicht wäre eine solche Liste mal eine lustige Idee: Dinge, die Excel NICHT kann 🙂

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Dieter Emonts

    Hallo Herr Weiß,

    danke für Ihre tollen und aufwendig gestalteten Beiträge.

    Zum Excel-Quickie 106 hier noch eine kürzere Version…

    =DATUM(B3;1;7* B4-3-WOCHENTAG(DATUM(B3;;);3))

    Viele Grüße
    de

    • Avatar-Foto
      Martin Weiß

      Hallo Herr Emonts,

      vielen Dank für das Lob und vor allem für diese knackige Lösung. Funktioniert einwandfrei, aber da muss ich selbst erst ein wenig daran knappern.

      Schöne Grüße,
      Martin

    • Avatar-Foto
      Stephen

      Diese Version ist echt knackig! Ich wäre interessiert an einer kurzen Erläuterung à la Martin Weiss, warum die funktioniert.

  • Avatar-Foto
    Maic W.

    Hallo,
    nur als kleine Ergänzung….
    Wenn man sich die Zwischenspalte mit „Glätten“ sparen möchte, funktioniert auch die folgende Matrix-Formel:

    ={SVERWEIS(B11;GLÄTTEN(A6:B8);2;0)}