mosestheg
Goto Top

Suchen von Werten von bis in externer Tabelle!

Hallo, bin ganz neu hier und hab bisher gedacht ich könnte etwas mit Excel umgehen, jetzt hab ich aber leider feststellen müssen dass ich wenns drauf an kommt garnix kann...
hoffe ihr könnt mir bissel weiterhelfen.

Hallo!
Ich brauch dringend eure Hilfe,

ich habe eine Arbeitsmappe mit 15 umbenannten Tabellen. In der Spalte J beginnen von 13 bis max 1000 meine Werte.
In Jeder Tabelle stehen diese Werte in der Spalte J.
Jetzt möchte ich die Werte zählen. Kleinster Wert ist 1 und größter Wert ist 1000.
Ich möchte die Gesamtanzahl zählen, die Anzahl in jeder Tabelle und die Anzahl derer die sich zwischen
0-20
21-50
51-80
81-120
121-150
151-200
201-250
251-300
301-500
501-800
801-1000
befinden. Ich hab keine Ahnung wie ich diese für mich sehr komplexe Formel hinbekommen kann.

Vielen Dank für euere Unterstützung,

Gruß Moses

Content-Key: 156763

Url: https://administrator.de/contentid/156763

Ausgedruckt am: 28.03.2024 um 17:03 Uhr

Mitglied: Biber
Biber 10.12.2010 um 17:59:56 Uhr
Goto Top
Moin MosesTheG,

willkommen im Forum.
[OT] Stimmt es eigentlich, dass jetzt vor Weihnachten noch schnell die Nickname-Ladenhüter verramscht werden, weil bald endlich das neue Sortiment kommt? [/OT]

Ein bisschen verwirrst du glaube alle Hilfswilligen, weil irgendwie die "externe Tabelle!" im Titel garnienicht wieder auftaucht später...
... dafür plötzlich von 15 umbenannten Tabellen die Rede ist, die aber auch ungenannt bleiben wollen... ?!?

Anyway, ich nehme an, du hast da "schon mal was vorbereitet", um mal ein beliebtes Zitat anzubringen.
Nämlich irgendwo eine optisch ansprechende Tabelle mit je einer Spalte für die Unter- und Obergrenze deiner Klassen und irgendwo daneben 15 Spalten, in denen die Ergebnisse aus 15 Arbeitsblättchen erscheinen sollen?
Oder wie ist der Plan ->wo willst du hin mit den ganzen Ergebnissen?

Du kannst diese Klassen-Auswertung mit mindestens 58 Strategien umsetzen, mit ZÄHLENWENN(), SUMMENPRODUKT(), SUMMEWENN(), Matrixformeln mit {=SUMME(..)}.
Dem Spieltrieb sind da wenig Grenzen gesetzt.

Empfehlen würde ich allerdings die Funktion HÄUFIGKEIT(), mit der die Redmonder PraktikantInnen das benannt haben, was du heftig gestikulierend als "die Anzahl derer die sich zwischen ... und ... befinden).

Beispiel:
Wenn eine deiner fuffzehn Tabellen "Tabelle12" heissen würde, dann würde:
=INDEX(HÄUFIGKEIT(Tabelle12!$J$13:$J$1000;{0;20});2)
... in die Zelle, in der du das hineinkopiert hast, die Anzahl der Werte zwischen 0 und 20 aus Tabelle12 schreiben.
=INDEX(HÄUFIGKEIT(Tabelle12!$J$13:$J$1000;{21;50});2)
... in die Zelle, in der du das hineinkopiert hast, die Anzahl der Werte zwischen 21 und 50 aus Tabelle12 schreiben.

Variante der ersten Formel:
=INDEX(HÄUFIGKEIT(Tabelle12!$J$13:$J$1000;{0;20});1)
... in die Zelle, in der du das hineinkopiert hast, die Anzahl der Werte Kleiner als (der Bereich zwischen 0 und 20) aus Tabelle12 schreiben.
Eine "3" statt einer "1" als letzter Parameter liefert entsprechend die Anzahl aller Werte, die größer sind.

Die beiden Werte in den geschweiften klammern {Untergrenze;Obergrenze} kannst du natürlich aus den beiden oben erwähnten Spalten holen.
Also z.B nach dem Proof-of-Concept in Richtung
=INDEX(HÄUFIGKEIT(Tabelle12!$J$13:$J$1000;{$A5;$B5});2)
verfeinern.
Das würde in die Zelle, in der du das hineinkopiert hast, die Anzahl der Werte zwischen Untergrenze (Zelle $A$5) und Obergrenze (Zelle $B$5) aus Tabelle12 schreiben.

P.S. Versuche nicht, Hinweise darauf in der Excel-Hilfe zu finden. face-smile

Grüße
Biber
Mitglied: 76109
76109 11.12.2010 um 11:36:34 Uhr
Goto Top
Hallo MosesTheG!

Wenn Du in jedem Tabellenblatt die Zwischensumme z.B. für die Biber-Formel
=INDEX(HÄUFIGKEIT($J$13:$J$1000;{0;20});2)
bildest und für die 15 Tabellenblätter eine fortlaufende Namensbezeichnung in der Art TA01, TA02..., TA15 verwendest, dann würde es in etwa so gehen:

Beispiel mit Zwischensumme für Werte zwischen 0-20 in Zelle X13. Gesamtsumme aller Tabellenblätter von TA01-TA15 wäre dann:
=SUMME(TA01:TA15!X13)

Gruß Dieter
Mitglied: MosesTheG
MosesTheG 14.12.2010 um 15:37:38 Uhr
Goto Top
Hallo Biber,
danke mal für deine schnelle und fast hilfreiche Antwort.
Die Tabelle zum auswerten habe ich vorbereitet gehabt, ähnlich deiner Beschreibung.
Mein Problem liegt darin, dass meine Arbeitsmappe "FMEA Analyse" heißt und ich darin die 15 Registerkarten habe die z.b. "Wareneingang Nr.1" heißen.
Ich weiß jetzt nicht wie ich auf die einzelnen Registerkarten zugreifen kann und in denen meine Suche zur Auswertung starten.

Das mit dem Nickname hat nichts mit der Jahreszeit zu tun face-smile
hab den Spitznamen schon ne Weile

Grüße Moses
Mitglied: MosesTheG
MosesTheG 14.12.2010 um 16:15:03 Uhr
Goto Top
So ich schon wieder,
hab das jetzt mehr oder weniger doch hinbekommen.
Das Problem ist aber, dass die Tabelle mit den Infos immer wieder verändert wird und ich aber in der Auswertung die aktuellen Daten benötige.
Wenn ich das mit der Index Formel mache muss ich immer im Explorer bei jeder Formel auf die Datei verweisen.
Kann ich dann einen Ordner machen in dem beide (Infos, Auswertung) Tabellen sich befinden und kann ich den dann auch Verschieben?
z.b. auf einen anderen Pc oder einen Stick.
Der direkte Verweis auf die Ober- und Untergrenze hat nicht funktioniert, da kam dann ein Fehler.

Danke für die Unterstützung,
MFG Moses
Mitglied: Biber
Biber 14.12.2010 um 16:23:42 Uhr
Goto Top
Moin MosesTheG,

na ja, und wo genau ist jetzt das Problem?

Wenn ich in meinem Beispiel sagen kann
=INDEX(HÄUFIGKEIT(Tabelle12!$J$13:$J$1000;{0;20});1)
... dann kannst du doch in deinem Fall schreiben
=INDEX(HÄUFIGKEIT([Wareneingang Nr.1]!$J$13:$J$1000;{0;20});1)
usw.
Ob nun Dieters 3-D-Formel in der Form
=SUMME([Wareneingang Nr.1]:[Wareneingang Nr.15]!X13) 
...klappt... -> ausprobieren.
Aber wenn du die Zwischensummen doch ohnehin in einer kleinen Übersichtstabelle hast,
dann geht es schneller mit den "normalen" Summen über die Teilergebnisse.

Was genau klappt denn nicht?

Grüße
Biber
Mitglied: Biber
Biber 14.12.2010 um 16:57:36 Uhr
Goto Top
Moin MosesTheG,

sorry, ich hatte deinen zweiten Kommentar nicht wahrgenommen vor meiner Antwort.

Zu deinem "Wenn ich das mit der Index Formel mache muss ich immer im Explorer bei jeder Formel auf die Datei verweisen. "..
Okay, dann musst du statt "direkt" (e.g. "[Wareneingang Nr.15]!!$J$13:$J$1000") eben mit INDIREKT()-Bezügen arbeiten.

Geht alles, allerdings mit zwei Wermutstropfen:
  • du kannst es so weit hinbasteln, dass der Name oder auch Ordnername der aktuellen Wareneingangstabellen genau ein einziges Mal irgendwo in der Auswertungstabelle steht. In allen Nachfolge-Formeln kannst du mit (Excel-) Namen arbeiten und diese Tabellen "logisch" ansprechen. Aber genau ein mal hast du den Speicherort halt irgendwo in der Auswertungstabelle und musst ihn anpassen/pflegen.
  • zweites Tröpfchen: die Tabelle mit den Quelldaten muss geöffnet sein (von dir und mit der gleichen Excelinstanz).

Beide Einschränkungen lassen sich natürlich mit einem Daumenbreit VBA umgehen/automatisieren.
Aber ich denke, wenn ihr eure Warenströme mit Excel analysiert, dann werden euch wahrscheinlich ein paar Zeilen VBA als viel zu große Kanonen für das bisschen Spatz vorkommen.

Inwieweit macht denn überhaupt eine "Automatisierung" bzw. die von dir angedachte Super-Flexibilität" mit "kann sich heute auf Laufwerk D: im Verzeichnis "unsortiertes" befinden, morgen unter anderem Namen auf dem USB-Stick sein" überhaupt Sinn?
Macht irgendein Praktikant diese Analyse jedes Jahr zum Jahresabschluss oder jeden Dienstag oder will sich der Cheffe täglich vor dem Morgenkaffee daran erfreuen?

Grüße
Biber
Mitglied: MosesTheG
MosesTheG 14.12.2010 um 17:55:14 Uhr
Goto Top
Ok, aber wie soll das genau aussehen? Ich hab ehrlich gesagt absolut keine Ahnung von VBA und Excel ist schon auch hartes Brot für mich.
Die beiden Tabellen bleiben schon immer zusammen an einem Speicherort.
Ich mache meine Technikerarbeit in einem Unternehmen und muss da jetzt die erstellten Tabellen auswerten und die Tabellen dann später dann noch bearbeiten und die bisherigen Ergebnisse überarbeiten dann verändern sich die Werte, brauch da jetzt ein Zwischenergebnis von den bisher bearbeiteten Tabellen.
Die Quelltabelle geöffnet zu haben ist absolut kein Problem.
Was meinst du mit direkt und indirekt? Sorry bin voll überfordert....
Gruß Moses
Mitglied: Biber
Biber 14.12.2010 um 19:27:20 Uhr
Goto Top
Moin MosesTheG,

langsam, langsam und vor allem nicht panisch werden...
Wenn wir hier den Arbeitsprozess und das Projektziel und die organisatorischen Zwänge und die USB-Sticks und die Makros und die Matrixformeln und die INDIREKT()-Funktion alle gleichzeitig in einem Satz problematisieren, dann wird das nix.

Nochmal zwangsstrukturiert:
a) Projektziel ist offensichtlich die mittelfristige Analyse der Entwicklung des Wareneingangs -> Verdichtung/Aggregation von Momentaufnahmen des "Wareneingangs 1" bis"..15" in zeitlichen Abständen.
a1) Wie oft gibt es aktuelle/gültige/nicht mehr nachträglich veränderbare Quelldaten? Täglich? Wöchentlich? Immer Ostern?
a2) Normalerweise ( wenn dieser Ausdruck im alltäglichen IT-Irrsinn überhaupt angebracht ist ) wäre ein Minimum an festen Punkten im Raum erforderlich:

  • organisatorisch ist gesichert, dass verbindliche Quelldaten bei dir landen, wenn sie fertig/gültig/freigegeben sind.
  • diese sind auch deiner und aus allen anderen Sichten "ReadOnly"-> Entweder die lassen sich as is komplett verwerten oder werden komplett abgelehnt.
  • nach der Überprüfung, ob die Quelldaten insgesamt gültig sind, saugst du aus den Quelldaten (die viel zu detailliert für eine Analyse/für Trend oder Kennzahlen sind) die Infos raus, die du brauchst, meinetwegen deine Monatssummen, Klassen und Häufigkeiten und dann kloppst du die Quelldaten in ein Zip, in ein Archiv oder in die Tonne
  • somit ist das ganze Gewusel doch nur eine Datenübernahme, ein Import und die ganzen Sorgen mit "mal sind die Quelldaten aufm Stick, mal gegenüber" sind doch unnötig

Jetzt endlich die nächsten Schritte:
Wie weit ist die Ziel-Vision des Projekts denn klar und abgestimmt?
Gibt es Vorgaben/Vorstellungen, ob es eine "Auswertungstabelle" pro Auswertungs-XLS-Datei gibt oder maximal 12 für 12 Monate?
Oder eine für jeden Monat in je einer XLS-Datei?
Oder beliebig viele?

Wieviel Aufwand lohnt sich wohl für diesen Prozess, solange das Ziel noch nicht klar ist?

Muss die Neu-Datenübernahme einmal im Monat gemacht werden?
So what -> was willst du dich dafür mit VBA auseinandersetzen und Dialogboxen basteln mit "Vielen Dank - Sie können den USB-Stick jetzt abziehen"?
Dann doch lieber (wissentlich und bewusst) eine Beschränkung auf Excel-Formeln. Wozu willst du eine halbe Stunde pro Monat "automatisieren"?

Skizzier doch bitte das Bild, das dir als Ergebnistabelle vorschwebt.
Eine Aussage wie "die Tabellen dann später dann noch bearbeiten und die bisherigen Ergebnisse überarbeiten dann verändern sich die Werte" oder allgemeiner "Alles dreht sich, alles bewegt sich" ist doch kein Plan.


P.S. Zum Thema "direkt"/Tabellenfunktion INDIREKT()" findest du reichlich in der Excel-Hilfe.

Grüße
Biber