Ich habe zuvor eine Frage gestellt, ohne ein Konto zu erstellen, und eine gute Antwort vom Benutzer Jeorje erhalten, aber ich habe nicht viele Details angegeben und konnte vorher nicht auf seinen Kommentar antworten.
Aus diesem Grund habe ich beschlossen, einen zweiten Versuch mit mehr Einzelheiten und Beispielen aus dem Arbeitsblatt, mit dem ich arbeite, zu wagen und der Möglichkeit, weiterhin auf Kommentare zu antworten.
Ich möchte ein System erstellen, das die Ausgabe der Häufigkeit ermöglicht, mit der ein Schlüsselwort oder eine Phrase in einem Textabsatz oder einem Dokumenttitel innerhalb eines änderbaren Datumsbereichs enthalten war.
Die Formel, die ich derzeit verwende, um in Zelle D1 eine Ausgabe zu erstellen, die die Anzahl der Einträge innerhalb des Datumsbereichs zählt, lautet:
=COUNTIFS('Main Sheet'!A:A,">="&Trends!C1,'Main Sheet'!A:A,"<="&Trends!A1)
Die Formel, die ich derzeit verwende, um in Zelle H2 eine Ausgabe zu erstellen, die die Anzahl der Einträge zählt, die das Schlüsselwort aus Zelle G2 enthalten, lautet:
=(SUM((LEN('Main Sheet'!H:H)-LEN(SUBSTITUTE((LOWER('Main Sheet'!H:H)),LOWER(G2),"")))/LEN(G2)))
Ich habe mich gefragt, ob es eine Möglichkeit gibt, diese beiden Formeln zu kombinieren, um den gewünschten Effekt zu erzielen, den ich suche, oder ob es eine dritte Formel gibt, mit der die Daten aus diesen beiden Formeln kombiniert werden können, um den gewünschten Effekt zu erzielen. Wenn ich völlig falsch liege, würde ich das auch gerne wissen.
Vielen Dank!
Bild des Hauptblattes
Bild des Trends Sheets
Antwort1
Ich glaube, dass Folgendes den Zweck erfüllen wird:
==SUM(IFERROR(IF( 'Main Sheet'!A:A >= Trends!C1, IF( 'Main Sheet'!A:A <= Trends!A1, FIND( G2, 'Main Sheet'!H:H ), NA() ), NA() ), " " ))
(Ich habe viele zusätzliche Leerzeichen eingefügt, damit die Elemente etwas besser hervorstechen. Jeder Satz besteht aus zwei Leerzeichen, Sie können also Find and Replacezwei Leerzeichen ohne Leerzeichen einfügen und alle von mir eingefügten Leerzeichen lassen sich problemlos entfernen.)
Die Version der Formel, die ich ausgearbeitet habe (mithilfe der Zellen in MEINER Testtabelle), hat in einer Reihe von Variationen gut funktioniert. Dann habe ich die Referenzen aus Ihren beiden Formeln dort eingefügt, wo sie hingehören, und meine ersetzt. Ich habe ein paar Tabellen usw. nicht benannt, um zu testen, ob ich das perfekt gemacht habe … wenn Sie also feststellen, dass es nicht funktioniert, nehmen Sie sich ein bisschen Zeit, um sicherzustellen, dass ich zuerst richtig kopiert und eingefügt habe, Sie wissen schon, ein Zeichen ausgelassen habe oder was auch immer.
Die Schwierigkeit, die ich hatte, war IF()anscheinend, dass die SPILL-Fähigkeit abgeschaltet wurde, was beispielsweise die Erstellung einer {FALSE;FALSE,TRUE,FALSE}Zeichenfolge in den internen Abläufen von Excel verhindert. Oder es sieht so aus, als wäre das der Mechanismus. Sie müssen eine einzelne Zelle VIEL testen. Wie Sie jedoch sehen, ist das nicht immer der Fall, und schließlich habe ich eine Kombination gefunden, die die Dinge nicht ruiniert hat.
Im Grunde genommen habe ich FIND()beim Arbeiten mit Excel immer eine Zeichenfolge {} erhalten. Es sucht nach dem gewünschten Wert, beispielsweise „Wasser“, und gibt Ihnen die Zeichennummer in der Zeichenfolge an, bei der dieser beginnt. Sie könnten also „5“ oder „2.184“ erhalten. In einer Zeichenfolge sieht das intern jedoch so aus: {„5“;„2184“} und COUNT()wird als zwei Elemente angesehen, NICHT als fünf Ziffern und daher als fünf Elemente.
In jedem Fall führen Fehler beim Finden des Datumsbereichs zu #NA-Fehlern, während Fehler beim Finden des Suchwerts in den Suchzeichenfolgen zu #VALUE-Fehlern führen. Erfolge führen zur Nummer des Zeichens, bei dem das erste Zeichen der Suchzeichenfolge gefunden wurde. Sie erhalten also eine {}-Zeichenfolge mit Fehlern und numerischen Werten, die Excel der IFERROR()Funktion präsentiert. Die Ausgabe sind natürlich die Werte für Nicht-Fehler und ein Leerzeichen für tatsächliche Fehler. Sie könnten stattdessen auch doppelte Anführungszeichen ("") verwenden, aber mir gefällt das Leerzeichen besser, da es das Ergebnis unter allen Umständen als Text betrachtet, sodass es nie einen Fehler machen kann, vielleicht von Excel als 0 betrachtet und mitgezählt wird. COUNT()mag nur Zahlen, also stellt die Erzwingung, dass sie immer Text sind, sicher, dass sie nie zum Ergebnis hinzugefügt werden können.
Dann COUNT()zählt es einfach diese Zeichenfolge und gibt die Anzahl der Orte zurück, die bei allen drei Elementen übereinstimmen, die Start- und Enddaten (den Datumsbereich) und das Vorkommen der Suchzeichenfolge in den Suchzellen.
Ich hoffe, ich habe hier die richtige Idee verstanden!