In einen gleich großen Bereich auf gleicher Höhe, also G8:K26, schreibst Du zunächst in G8 die Formel:

=SUMMENPRODUKT((A8>$A$8:$E$26)*1)+1

und füllst diesen Bereich damit. Die sich ergebenden Zahlen repräsentieren die alphabetische Reihenfolge.

Dann schreibst Du an beliebiger Stelle, zB A31 diese Formel:

=Indirekt(Adresse(Summenprodukt(($G$8:$K$26=Zeilen(A$31:A31))*Zeile($G$8:$K$26));Summenprodukt(($G$8:$K$26=Zeilen(A$31:A31))*Spalte($G$8:$K$26))-6))

diese Formel ziehst Du runter bis in A126 ein Fehler auftritt, den löscht Du dann wieder.

fertig ;-))

Wenn Du wissen willst, wie das funktioniert, musst Du die Formeln in ihre Bestandteile auseinandernehmen oder hier nochmals nachfragen!

(es ginge sicher auch mit Index, vllt sogar etwas kürzer, aber es ist 3:50 nachts und ich will morgen nicht zu spät aufstehn...)

...zur Antwort

falls das schon genannte Duplikate entfernen nicht anwendbar sein sollte, dann so:

Füge rechts daneben die Funktion

=Zählenwenn(C$2:C2;C2)

ein und ziehe sie runter soweit nötig. Die Fkt spannt einen Bereich bis zur jeweiligen Formelposition auf.

In C10 steht dann zB =Z.Wenn(S$2:C10) =>3

Setze diese Fkt zu Werten um(kopieren, WERTE einfügen), füge vorsichtshalber noch eine Spalte mit fortlaufend steigenden Zahlen ein (nicht als Formeln!), um ggf die richtige Ordnung später wiederherzustellen, falls was schief läuft

Und sortiere dann nach der Spalte mit den Zählenwenn-Ergebnissen.

Alle 2er, 3er und höhere wandern nach unten und du kannst sie als Block löschen.

...zur Antwort

ggf mit einem Ereignismakro, das

Range("A1").interior.Color.RGB(255, VAR, 0)

den GRÜN-wert VAR von 255 (=>GELB) nach 0 (=>reines ROT) entsprechend einer vorher definierten Funktion (%linear , logarithmisch, S-förmig oder sonstwas) und ihren Grenzwerten durchlaufen lässt, ähnlich wie man das mit einem Scroll-Balken machen kann.

Kann es mangels VBA-Hilfe in LO nicht ausführen, wäre aber vllt ganz interessant, wenn man sich mit Makro-Programmierung befassen will.

Bezweifle aber, dass das der Frager will.

...zur Antwort

in B1 stehe, ab welcher Zeile summiert werden soll,

in C1, bis zu welcher Zeile summiert werden soll:

=Summenprodukt(A1:A999*(Zeile(A1:A999)>=$B$1)*(Zeile(A1:A999<=$C$1)))

in A dürfen im Gesamtbereich keine Texte stehen, sonst ergibt sich ein Fehler.

(der lässt sich auch vermeiden, aber dann wirds deutlich komplizierter)

Die beiden geklammerten Faktoren sind Ausdrücke, die WAHR oder FALSCH ergeben, was bei der Multiplikation zu 1 bzw 0 umgewandelt wird ("Logik-Ausdrücke")

Obige Formel hat den Vorteil. dass die Spur zum Vorgänger angezeigt wird, was bei indirekt nicht der Fall ist (zumindest bis Office 2010, vermutlich weiterhin)

...zur Antwort

Du kannst die NebenBlätter doch einfach schützen (Blattschutz mit Passwort), dann kann keiner was eintragen ausser dem, der das aktuelle Blattschutzkennwort weiß. In .xlsx/.xlsm-Dateien sollte der auch nicht mehr so einfach zu knacken sein wie früher bei den .xls-Tabellen...

Ansonsten ein Ereignis-Makro, das beim Anklicken einer Zelle in diesen "kleinen" Blätter umgehend die Haupttabelle aktiviert. Nachteil dieses Vorschlags: deaktiviert der User die Makros beim Öffnen, kann er wieder schalten und walten, wie er will.

Kannst aber auch beides kombinieren. Und dem Makro eine Msgbox hinzufügen, dass gefälligst im Hauptblatt zu arbeiten ist (na ja, vllt etwas freundlicher formulieren)

...zur Antwort

Ich hab nur 1 angeschaut. Das ist ein Blatt, das zeigt, wie man eine Textauflösung macht, um zu rechenbaren Zahlen zu kommen. Mit dem Wert in D2 kannst Du nämlich nicht rechnen, [in der eckigen Klammer steht vermutlich die Quelle, also wo die Zahl veröffentlicht wurde].

Wenn Du zum Beispiel die Einwohner verschiedener Städte zusammenzählen willst (im einfachsten Fall), musst Du die Zahl isolieren, und das sollst Du lernen (vermute ich). Um dieses Lernen kommst Du nicht herum, auch wenn Du einen Kurs bezahlst.

Da xl meist viele Möglichkeiten hat, eine Aufgabe zu erledigen, sollst Du mehrere kennenlernen, denn oft gibt es Unterschiede in diesen Möglichkeiten. Das macht den xl-Kenner aus: dass er weiß, WELCHE Formel er bei einem Problem sinnvoll einsetzen kann. Nur zwei Beispiele:

1.Beispiel (Texte):

=WECHSELN("Maus";"M";"H") macht aus MAUS HAUS.

=ERSETZEN("Maus";1;1;"H") bewirkt in diesem Fall genau dasselbe.

Was ist also der Unterschied?

Wechseln macht aus ("Tanzmaus";"M";"H") TanzHaus, denn es wechselt an beliebiger Stelle.

=ERSETZEN("Tanzmaus";1;1;"H") liefert dagegen Hanzmaus, denn hier wird die erste Stelle ausgetauscht, was obendrein kein sinnvolles Wort ergibt. Es gibt noch weitere Unterschiede, die ich jetzt beiseite lasse.

2.Beispiel (Zahlen):

=GANZZAHL(2,345) liefert 2, wie erwartet. Aber

=GANZZAHL(-2,345) liefert -3, denn Ganzzahl zählt am Zahlenstrahl von links nach rechts. (und korrespondiert somit mit der Funktion REST())

Weiß man sowas nicht, hat man ganz schnell einen Fehler gemacht! Will man 2 und -2 erhalten, muss man

=Abrunden(2,345) und =Abrunden(-2,345) verwenden (ich weiß diese Feinheiten auch nicht alle auswendig, aber ich weiß, dass ich das erst prüfen muss, bevor ich die Funktion verwende: es gäbe nämlich auch noch die Funktion =Runden, =Aufrunden, =Obergrenze und =Untergrenze (zusätzlich auch noch =Vrunden, aber die lassen wir jetzt)

Du siehst, xl ist nichts, was man im Vorbeigehen lernt, das erfordert üben, üben und nochmals üben, auch, wenn's schwerfällt !!!

...zur Antwort

Und wenn Du Hyperlinks zu selbst erstellten (oder geklauten) Sound-Samples machst? Diese können ja kurz und gesummt oder gepfiffen sein, Hauptsache, Deine Idee geht für Dich daraus hervor.

Und du kannst zum Vorsortieren aller Röcke etc ja in xl je ein Blatt erstellen mit solchen Hyperlinks. Da hast Du je Blatt schon mal ca 30 Zeilen, bei zwei genutzten Spalten das Doppelte auf dem Bildschirm. Bleibt immer noch genug Zwischenraum für Kommentare (die Kommentarfunktion könnte man aber auch noch nutzen)

Und je nach Blattnamenslänge 20-30 Blattreiter auf einen Blick.

Um eine gewisse Einordnung kommst Du nie herum, und wie man es macht, ist es falsch. Damit muss man leben.

...zur Antwort

noch von Interesse?

in G1 stehe der individuelle Geburtstag, anstatt Heute() den Stichtag setzen oder einen Verweis auf diesen.

runde: =(MONAT(G1)=MONAT(HEUTE()))*(TAG(G1)=TAG(HEUTE()))*REST(JAHR(HEUTE())-JAHR(G1);10)=0

halbrunde: =(MONAT(G1)=MONAT(HEUTE()))*(TAG(G1)=TAG(HEUTE()))*REST(JAHR(HEUTE())-JAHR(G1);5)=0

Nimm aber für die bedingte Formatierung noch nicht das extremste Zell-layout, denn Du wirst bald entdecken, dass es wichtigere Bedingungen gibt als runde und halbrunde, zB

>80, vllt sogar >85 oder >90 und jedes Jahr ?

>=65 und ab da alle 5 Jahre,

ehemalige Vereinsmeister oder besonders verdiente Mitarbeiter vllt schon ab 50?

Nimm für die bedingte Formatierung unterschiedliche Elemente, die sich überlagern können: schwarzer, mitteldicker Rahmen links und rechts, roter dicker Rahmen unten, doppelt unterstrichen, Schriftfarbe, Schrift fett, Font Arial black, dann erst Hintergrund und auch noch Muster (letzteres gibts zB in LO für Zellen nicht, nur für Textfelder). Vllt gibts noch weitere unabhängige Elemente. Erst wenn alles ausgeht, Abstufungen, denn da macht man am leichtesten den Fehler, dass sich Bedingungen verkehrtrum verdecken.

...zur Antwort

Filtern weiß ich jetzt nicht, aber sortieren kannst Du nach zwei Kriterien, indem Du zum ersten Kriterium eine Ebene hinzufügen wählst und dort das zweite (ist nachgeordnet) eingibst.

Als Formel kannst Du eine HilfsSpalte(HS) kreieren, in der Du

=AF-AE*10^-10 berechnest und wieder das Minimum suchst

(10^-10 ist normalerweise klein genug, um nicht zu stören, hängt von der Größenordnung Deiner Grundzahlen ab. Und minus, weil Du das Maximum suchst und es zum Minimum machen musst, damit Du das Gesamtminimum findest.

Du kannst anschließend, anstatt zu filtern, mit dieser Formel den Namen(in B) finden:

=index(B:B;Vergleich(Min(HS:HS);HS:HS;0))

zu deutsch:

Nenne aus Spalte B den Eintrag(Namen), dessen Zellnummer derjenigen des Minimums aus Spalte HS in Spalte HS entspricht. Nimmt man die ganze Spalte entspricht die Zellnummer der Zeile.

Das geht nicht mit dem SVerweis, weil dort das Suchkriterium immer links stehen muss, hier ist es frei wählbar.

...zur Antwort

Ich nehme an, Du hast eine Materialienliste mit den Preisen.An diese Liste musst Du eine Spalte "vorhanden"[ml] anhängen. Von dieser Spalte ziehst Du den aktuellen "Verbrauch" ab. In der Ergebnisspalte musst Du dann je Material eine kritische Untergrenze(Erfahrungswert) definieren, nicht explizit, sondern in einer Formel, am kürzesten so:

=Vorrat-Verbrauch-Untergrenze

ZellZahlenFormat(Benutzerdefiniert, nicht bedingt): 0;[rot]-0\!!!;\°

Sinkt der Wert unter die Untergrenze, wird die Zahl rot-negativ als Warnsignal, dass du nachkaufen musst. Kannst natürlich die Untergrenze in einer separaten Spalte eintragen, könnte übersichtlicher sein.

Nach Zukauf musst Du natürlich den vorhanden-Wert manuell erhöhen.

Finanztechnisch könnten andere Anlagen sinnvoller sein, aber so ist es am kürzesten.

übrigens:kalkuliere Schwund/verschütten mit ein!

...zur Antwort

Mach das direkt neben Artikel&Nr: Füge also eine neue Spalte als Spalte C ein. In die Zeile mit den Tagen 1..31 (sie sei Zeile 1 -warum muss das immer ich erfinden???) schreibst Du den gewünschten Tag, also C1=zB 5 für den 5. des Monats. Ob du das per drop down oder Direkteintrag machst, ist egal.

In der ersten Artikelzeile(sie sei 2) dann die Formel für den ersten Artikel, also

C2: =index(D2:AH2;1;Vergleich(C$1;D$1:AH$1;0))

diese Formel kopierst Du nach unten - fertig

Wenn Du willst, kannst Du die Spalte natürlich auch nach ganz rechts verschieben, Ansichtssache.

Am Ende der Liste kannst Du in Sp.C noch die Summe bilden (C2:Cn) für den Gesamtwert aller Rückläufe des abgefragten Tages, auch als Basis für %-Anteile eines Artikels etc.

...zur Antwort

Doppelpunkte gibt es in den Unicode-Zeichen haufenweise, zB. ։ =Unizeichen(1417), ࿒(4050), ፡(4961), ∶(8758), gefolgt von denen, die ich in der Braille-Schrift gefunden hab ⡂⡄⠃⠅⠆⠰⢠ (zwischen 10300 und 10600, scheinbar gleiche unterscheiden sich im Spacing, ich staune schon, was für ein Feingefühl die Blinden anscheinend entwickeln!), weiter unten vermutlich noch viele weitere. Die Frage ist aber, ob Dir das was nützt, die Eingabe wäre unerfreulich langsam.

Was Du aber machen kannst: such Dir ein Zeichen auf der Tastatur aus, das Du nie brauchst (oder eine Kombi zweier Zeichen, meinetwegen #_ und trage das ins benutzerdefinierte Wörterbuch für die Autokorrektur ein, ersetzen durch eines der obigen Zeichen.

Lohnt nur, wenn wirklich sehr häufig gebraucht.

In Libre Office kann man sich eine Tabelle erstellen mit der Formel

=UNIZEICHEN((ZEILE()-1)*100+SPALTE()-1)

kopieren bis CV66000

und hat dann eine Darstellung aller Unizeichen.

Ob das in xl inzwischen auch geht, weiß ich nicht, früher gings nicht, als LO das schon lange konnte. (geht aber wahrscheinlich an Deinem aktuellen Interesse vorbei und ist obendrein eine Zeitsparkasse ohne Rückzahlung... Nur für Entdecker-Naturen geeignet))

...zur Antwort

Wenn die Namen in A stehen und die Kennzahl in B, dann füge oben eine neue Zeile ein und schreibe in B1 als Start die "Nullnummer" 0.0 (A1 kannst Du leer lassen oder Beliebiges reinschreiben, nur nicht den ersten Namen.

In B2 die Formel:

  • =WENN(A1=A2
  • ;LINKS(B1;FINDEN(".";B1;1)-1)&"."&TEIL(B1;FINDEN(".";B1;1)+1;999)+1  
  • ;LINKS(B1;FINDEN(".";B1;1)-1)+1&"."&0)

runterziehen.

Willst Du immer mit Unternummer 1 beginnen, dann ....&"."&1)

Bei mir tutets.

...zur Antwort

Du kannst im Nachhinein ein MusterFormat drüberlegen (Musterzelle, Doppelklick Formatpinsel und eine Zelle/Bereich nach der anderen anklicken), vorher gehts nicht

Es gab mal eine Tastenkombination(2-oder 3-stufig) für bearbeiten-Inhalte Einfügen-Werte, die funktioniert aber nicht mehr oder hat sich zumindest geändert. (ich glaube, war Alt+b, i, e) Mit standardmäßig / bekommst Du die Möglichkeiten aber in der Iconleiste als Buchstabe angezeigt, ob das noch drin ist, weiß ich nicht, hab inzwischen LO stratt xl.

...zur Antwort

Mach einfach =Rest(B2-A2;1)/24, das ist der Stundenrest, der über ganze Tage hinausgeht

(=Ganzzahl(B2-A2) sind die Tage)

Beide Werte entsprechend Benutzerdefiniert formatieren:

Tage: einfach T oder TT oder Zahl ohne Dezimalen

Stunden: [h] oder hh

Anmerkung: das Format schneidet allerdings nicht die überzähligen Minuten ab, nur in der Darstellung, beim Rechnen zählen die weiter mit.

Dafür müsstest Du anstatt B2-A2 überall diesen Ausdruck verwenden:

Ganzzahl((B2-A2)/24)*24

Wenn Du auch die Minuten beibehalten willst, nicht aber die Sekunden, dann:

Ganzzahl((B2-A2)/24/60)*24*60

...zur Antwort

Es geht nur mit einem Makro, zB einem Worksheet_Change -Makro. Formeln bleiben Variabel, wie KHSchindelar schon schreibt.

Makros müssen sehr spezifisch formuliert werden, und vllt bin ich etwas schwer von Begriff, aber ich sehe Deine Beschreibung nicht eindeutig.

Das Schema geht ungefähr so

  • Sub Worksheet_Change(ByVal target as range?)
  • if not intersect(Target, rows(1, Cells)) then exit sub 'Nur Einträge in Zeile 1 prozessieren
  • Target.copy Target.offset(0,1).end(xldown).offset(-1, 0) 'kopieren und versetzt einfügen
  • Target.clearvalues
  • end sub

Hab auf LO umgestellt und deshalb keine VBA-Hilfe zur Verfügung. Du siehst aber, das Makro wäre nicht allzu kompliziert. Vllt widmet sich dem ja noch wer.

...zur Antwort

Wenn es immer drei Zeilen sind (oder immer gleich viele), dann mach auf einem neuen Blatt eine Liste fortlaufender Zahlen in A (Das alte Blatt heisse der Kürze wegen SB (Stammblatt)

In B2:

  • =index(SB!B$1:B$9999;Vergleich($A2;SB!$A$1:$A$9999;0)+0)
  • &index(SB!B$1:B$9999;Vergleich($A2;SB!$A$1:$A$9999;0)+1)
  • &index(SB!B$1:B$9999;Vergleich($A2;SB!$A$1:$A$9999;0)+2)

(ist EINE Formel!) Die $-Zeichen sind genau zu beachten! (fehlen vor der Spalte in Index und vor der Zeile in Vergleich, damit es universell kopierbar wird)

diese Formel ziehst Du nach rechts und dann alle Formeln runter, soweit jeweils nötig.

sollten sich da bei den leeren Zellen unliebsame Nullen ergeben (ich habs in xl nicht ausprobieren können, in LO könnte es abweichen), dann die Gesamtformel einkleiden in =Wechseln(Formel;"0";""). Unterdrückt allerdings auch Nullen in Zelltexten (wenn da zB stünde Stufe 10 ==> Stufe1).

Hast Du unterschiedliche Blöcke, müsste man eine sehr viel komplexere Formel verwenden, vllt auch eine zusätzliche Hilfsspalte, diese Mühe erspare ich mir jetzt!

Um sowas zu vermeiden, müsstest Du im Stammblatt durch Suchen und Ersetzen (Strg+h) erst mal alle 0 durch zB °, o, ⓪ oder ⓿ etc ersetzen. Je nach Struktur Deiner Datei geht alles, wenn oft auch entsprechend kompliziert. Dabei lässt sich xl im Ggs zu unserem Hirn von so einer Komplexität nicht stören , es arbeitet stur alles ab.

...zur Antwort

Abkürzung für die Formel, mit der Du das Datum erzeugst: DFo

=Wenn(Rest(DFo;7)<2;DFo-Rest(DFo;7)-1;DFo)

Kopiere also Deine Formel (als String, ohne = in die Zwischenablage, sichere die Zwischenablage in einer leeren Zelle), überschreibe sie mit obiger Formel und ersetze dann DFo mit der Zwischenablage, 3x.

Kannst obige Fo nach Sichern der Deinigen ja in die ZwiAbl übernehmen zum Überschreiben der Deinigen und anschließend mit dem String aus der Sicherungszelle die dreifache Ersetzung vornehmen. Vor dem 3.x einfügen liefert die Fo natürlich einen Fehler, weil xl mit DFo ja nichts anfangen kann.

Mit dieser Methode kannst Du das in derselben Zelle machen, so dass Du keine Bezüge ändern musst.

Dass die Formel dabei lang wird, stört xl nicht, höchstens Deinen eigenen Durchblick (kann nämlich sehr unübersichtlich werden) Zeilenumbrüche in der Bearb-Zeile mit Alt+Enter können hilfreich sein, xl lässt das zu (im Ggs zu LO/OO).

...zur Antwort

Am besten bisher finde ich die Aw von gaterde. In dieser Formel ersetzt Du einfach den Leerschlag(nebenbei: danke für dieses Wort!) " " durch die jeweilige Einheit ggf noch mit ,:

...&" Hz, "&...&" Ohm, "&...&" kW, "

...zur Antwort

in Zeile 1 ganz normal =Summe(A1:A24) Formel stehe in Sp R wie Resultat

in R2: =SUMME(INDIREKT("A1:A"&ZEILE()*24))-SUMME(R$1:R1)

  • runterziehen soweit nötig.
  • Es darf kein Ergebnis oberhalb gelöscht oder verschoben werden!

Prinzip: es werden immer alle Werte zusammengezählt, aber davon immer die Summe bis von davor abgezogen.

Über etwas komplexeres indirekt ließe sich das auch nur für den betroffenen Bereich machen, wahrscheinlich auch über Bereich.verschieben, aber die Syntax kann ich grad nicht ausprobieren. (s.m.Kommentar zur AW Subopt)

Obiges sollte jedenfalls funktionieren!

...zur Antwort