Arrays von Konstanten in MS EXCEL. Konstante Arrays in Excel

Wenn Sie eine Matrixformel eingeben, verwenden Sie meistens einen Zellbereich auf einem Blatt, den Sie jedoch nicht benötigen. Sie können auch verwenden array-KonstantenGeben Sie einfach die folgenden Klammern in die Bearbeitungsleiste ein: (). Dann können Sie eine Konstante benennen, um die Wiederverwendung zu vereinfachen.

Konstanten können sowohl in Matrixformeln als auch separat von diesen verwendet werden.

Verwenden von Konstanten zum Eingeben von Werten in eine Spalte

Gehen Sie wie folgt vor, um Werte in eine Spalte, beispielsweise in drei Zellen von Spalte C, einzugeben.

Verwenden einer Konstante zum Eingeben von Werten in eine Zeichenfolge

Gehen Sie wie folgt vor, um schnell Werte in einer Zeile einzugeben, z. B. in die Zellen F1, G1 und H1.

Verwenden von Konstanten zum Eingeben von Werten in mehrere Spalten und Zeilen

Verwenden einer Konstante in der Formel

Nachdem Sie sich bereits mit Array-Konstanten vertraut gemacht haben, betrachten Sie ein praktisches Beispiel.

Geben oder kopieren Sie die folgende Formel und fügen Sie sie in eine leere Zelle ein. Drücken Sie dann STRG + UMSCHALT + EINGABETASTE:

= SUMME (A1: E1 * (1,2,3,4,5))

Ein Wert wird in Zelle A3 angezeigt 85 .

Was ist passiert? Sie haben den Wert in Zelle A1 mit 1, den Wert in Zelle B2 mit 2 usw. multipliziert und anschließend mit der Funktion SUMM die Addition dieser Ergebnisse durchgeführt. Sie können dieselbe Formel in das Formular eingeben = SUMME (A1 * 1, B1 * 2, C1 * 3, D1 * 4, E1 * 5).

Und wenn Sie möchten, können Sie beide Wertesätze in Form von Array-Konstanten eingeben:

= SUMME ((3,4,5,6,7) * (1,2,3,4,5))

Kopieren Sie dazu die Formel, markieren Sie die leere Zelle, fügen Sie die Formel in die Formelleiste ein und drücken Sie STRG + UMSCHALT + EINGABETASTE. Sie erhalten das gleiche Ergebnis.

Anmerkungen:  Wenn die Konstanten nicht funktionieren

Dies ist ein Kapitel aus dem Buch: Michael Girvin. STRG + UMSCHALT + EINGABETASTE. Mastering von Matrixformeln in Excel.

Weiter oben in diesem Buch sind Sie wiederholt auf Anordnungen von Konstanten gestoßen. Tatsächlich haben Sie jedes Mal, wenn Sie die Elemente einer Formel mit der Taste F9 ausgewertet haben, ein Beispiel für ein Array von Konstanten gesehen. In der ersten Formel des in erstellten Arrays (wieder in Abb. 7.1 gezeigt) können Sie beispielsweise D2: D5-C2: C5 auswählen und F9 drücken, um das resultierende Array (3; –8; 7; 6) anzuzeigen. Zuvor haben Sie jedoch nach der Auswertung des Arrays sofort Strg + Z gedrückt, um in der Formel keinen festen Code, sondern einen Link zu belassen. Wenn Sie nicht Strg + Z verwenden, erstellen Sie ein Array von Konstanten, die sich nicht ändern, wenn sich die Quelldaten auf dem Blatt ändern. In diesem Kapitel lernen Sie Situationen kennen, in denen die Verwendung einer Reihe von Konstanten Vorteile bringt.

Abb. 7.1. Markieren Sie das Array (links), drücken Sie F9 (rechts) und erhalten Sie ein Array mit Konstanten

Notiz in oder Format herunterladen, Beispiele in Format

Betrachten Sie die Syntax, die zum Erstellen eines Konstantenarrays verwendet wird, sowie Beispiele, die die drei Arten von Konstantenarrays veranschaulichen, die in Excel auftreten können.

Vertikale Anordnung von Konstanten(Abb. 7.2). Erstellen Sie in Zelle A6 eine Verknüpfung zu dem Array = A2: A4, und drücken Sie F9. Sie sehen eine Reihe von Elementen in geschweiften Klammern. Der Text wird immer in Anführungszeichen angezeigt. Ein Doppelpunkt ist ein Trennzeichen, das angibt, dass sich die Elemente in einer Spalte untereinander befinden.

Abb. 7.2. Vertikaler Zellbereich: Wählen Sie den Link (links) und drücken Sie F9 (rechts).

Horizontale Anordnung von Konstanten  (Abb. 7.3). Erstellen Sie in Zelle A6 eine Verknüpfung zu dem Array = A2: B2, und drücken Sie F9. Sie sehen eine Reihe von Elementen in geschweiften Klammern. Der Text wird immer in Anführungszeichen angezeigt. Zahlen werden einfach Zahlen angezeigt; Semikolon, - Trennzeichen, das angibt, dass sich die Elemente in derselben Zeile in benachbarten Spalten befinden.

Abb. 7.3. Horizontaler Zellenbereich: Wählen Sie den Link (links) und drücken Sie F9 (rechts).

Rechteckige Anordnung von Konstanten  (Abb. 7.4). Erstellen Sie in Zelle A6 eine Verknüpfung zu dem Array = A2: B4, und drücken Sie F9. Sie sehen eine Reihe von Elementen in geschweiften Klammern. Der Text wird immer in Anführungszeichen angezeigt. Zahlen werden einfach Zahlen angezeigt; Es werden zwei Trennzeichen verwendet: Ein Semikolon, um zur nächsten Spalte in derselben Zeile zu gelangen, und ein Doppelpunkt, um zur ersten Spalte in der neuen Zeile zu gelangen.

Abb. 7.4. Rechteckiger Zellbereich: Wählen Sie den Link (links) und drücken Sie F9 (rechts)

Sie fragen sich vielleicht, ob es Situationen gibt, in denen eine Reihe von Konstanten nützlich ist? Widerspricht es nicht der Excel-Goldregel? Wenn sich die Quelldaten ändern, platzieren Sie sie auf einem Blatt und verweisen Sie innerhalb der Formel auf sie. Wenn sich die Quelldaten nicht ändern, codieren Sie sie in der Formel fest. Der Vorteil des Konstantenarrays besteht darin, dass für die Formel kein Drücken von Strg + Umschalt + Eingabetaste erforderlich ist. Wenn sich die Quelldaten also nicht ändern, verwenden Sie ein Array von Konstanten.

Betrachten Sie ein Beispiel für ein Golfspiel (Abb. 7.5). Das Ziel ist es, die Summe der drei besten (minimalen) Ergebnisse zu finden, wobei das vierte und die nachfolgenden Ergebnisse unberücksichtigt bleiben, wenn sie gleich dem dritten sind (es ist klar, dass es in unserem Beispiel genau ein solcher Fall mit der Zahl 70 ist). Um das Problem zu lösen, ist die LEASTING-Funktion perfekt geeignet, mit deren Argument k Sie die erforderlichen Werte auswählen können: k = 1 gibt den Minimalwert an, k = 2 - die Sekunde usw. Wie in Abb. 7.5, die Summe der drei kleinsten mit k = 1, 2, 3 ergibt die richtige Antwort. Eine ausgezeichnete Formel, die möglicherweise nur den Nachteil hat, dass das Schreiben einige Zeit in Anspruch nimmt, wenn Sie beispielsweise 10 Mindestwerte auswählen müssen.

Abb. 7.5. Argument k  Die LEAST-Funktion liefert den k-ten kleinsten Wert.

Um eine kompaktere Formel zu erstellen, können Sie ein Array in das Funktionsargument einfügen (Abb. 7.6). Und hier können Sie das Array der Konstanten (1,2,3) verwenden. Sie können harten Code verwenden, da sich die Anzahl der auszuwählenden Werte nicht ändert. Da das Argument k drei Werte hat, gibt die LEASTEST-Funktion auch drei Werte zurück, die addiert werden. Um dies zu überprüfen, wählen Sie das Argument Nummer1 der SUMME-Funktion und drücken Sie F9 (Abb. 7.7). Es ist zu sehen, dass die LEAST (B3: B8; (1; 2; 3)) - Funktion, obwohl der drittkleinste Wert zwei gleiche Zahlen 70 aufweist, nur drei Zahlen zurückgab.

Hinweis: Es spielt keine Rolle, ob Sie ein Array von Konstanten horizontal (1; 2; 3) oder vertikal (1: 2: 3) angeben.

Um die Formel einzugeben, brauchten Sie nicht Strg + Umschalt + Eingabetaste. Wenn Sie jedoch anstelle eines Arrays von Konstanten im Argument k den Verweis auf den Bereich verwenden (Abb. 7.8), müssen Sie zur Eingabe der Formel die Tastenkombination Strg + Umschalt + Eingabetaste drücken (andernfalls wird der Fehler #WERT! Angezeigt). Beachten Sie, dass STRG + UMSCHALT + EINGABETASTE weiterhin vermieden werden kann, wenn Sie die Formel verwenden: = SUMPRODUCT (LEAST (B3: B8; D3: D5))

Anmerkung: Die HELP-Funktion von LEAST erwähnt nicht, dass das Argument k durch ein Array von Konstanten dargestellt werden kann. Dies ist ein weiterer Hinweis darauf, wie schlecht dokumentiert die Fähigkeit von Excel ist, mit Array-Formeln zu arbeiten. Ich habe im MrExcel-Forum und in langjähriger Forschung und Entwicklung durch Ausprobieren davon erfahren.

Wenn Sie alle geteilten (gezeichneten) Ergebnisse zusammenfassen müssen, können Sie die übliche SUMMESL-Funktion verwenden und nicht die Matrixformel (Abb. 7.9).

Abb. 7.9. Die Formel fasst drei Mindestpunkte zusammen, einschließlich Gleichheit

Das folgende Beispiel zeigt die Verwendung der Funktion BIGGEST (Abbildung 7.10). Ziel ist es, für jeden Teilnehmer die drei längsten Bumerangflüge zusammenzufassen.

Und was ist, wenn die Formel eine variable Anzahl von Maximal- oder Minimalwerten summieren soll? In Abb. 7.11 zeigt zwei Formeln, die die drei größten Werte zusammenfassen. Die Formel schließt geteilte dritte Werte aus, die Formel schließt diese ein. Der Unterschied dieser Formeln zu denen, die zuvor betrachtet wurden, besteht darin, dass die Anzahl der Maximalwerte nicht fest in die Formel eingenäht ist, sondern durch die Werte in den Zellen D3 und D6 bestimmt wird. Wenn sich die Zahlen in den Zellen D3 und D6 ändern, ändert sich auch der Betrag in den Zellen E3 und E6. In Abb. 7.12 zeigt die Beträge für die beiden Maximalwerte.

Abb. 7.12. Die Formeln 1 und 2 ergeben für die beiden Maximalwerte die gleichen Ergebnisse, da es unter den zweiten Werten keine identischen gibt

Die Formel ist wahrscheinlich leicht zu verstehen, enthält jedoch Elemente, die Sie noch nie zuvor kennengelernt haben. Mit den Funktionen STRING und DFSR können Sie ein dynamisches Array mit variabler Länge von fortlaufenden Zahlen erstellen. Beginnen Sie mit der Eingabe der Formel mit der DFSED-Funktion (Abbildung 7.13). FBSSL ist so programmiert, dass eine Referenz zurückgegeben wird, die durch eine Textzeichenfolge gegeben ist. In unserem Fall das Argumentelement cell_link  "1:" & D3 ist eine Verknüpfung zu 1: 3-Zeilen. Um dies zu überprüfen, wählen Sie dieses Element aus und drücken Sie F9.

Abb. 7.13. Verkettung "1:" und D3 (links) ergeben einen Link zu den Zeilen "1: 3" (rechts)

Wenn Sie auswerten möchten, was die Formel = FLAX ("1:" & D3) zurückgibt, wählen Sie sie vollständig aus und drücken Sie F9 (Abb. 7.14). Dann wird die Fehlermeldung "Die Formel ist zu lang. Ihre Länge sollte 8192 Zeichen nicht überschreiten. “

Abb. 7.14. Bei der Auswertung der Formel = FALSE ("1:" & D3) wird ein Fehler zurückgegeben

Der Fehler wird durch die Tatsache verursacht, dass die Verknüpfung zu den Zeilen 1: 3 alle Zellen in den Zeilen 1 bis 3 enthält. Auf der Excel-Tabelle (ab Version 2007) befinden sich 16.384 Spalten, d. H. Drei Zeilen enthalten 49.152 Zellen. Die DFSR-Funktion selbst enthält keinen Fehler. Wenn Sie sie also als Argument für die LINE-Funktion einfügen, erhalten Sie das gewünschte Ergebnis (Abb. 7.15). Beachten Sie, dass das Argument der STRING-Funktion einen Wert erwartet. Sie "rutschen" ihm 49.152 Zellreferenzen. Alle diese Verknüpfungen beziehen sich auf die Zeilen 1, 2 und 3. Die LINE-Funktion gibt also ein Array (1: 2: 3) zurück (stellen Sie dies sicher, indem Sie die gesamte LINE-Funktion markieren und F9 drücken). Bitte beachten Sie, dass die Array-Syntax einen Doppelpunkt als Trennzeichen enthält, da es sich um Strings handelt.

Abb. 7.15. Die String-Funktion gibt die Zeilennummer des Link-Arguments zurück. Da DFSB Zellen zurückgibt, die sich auf die Zeilen 1, 2 und 3 beziehen, gibt die STRING-Funktion ein Array zurück (1: 2: 3).

Mit Hilfe der STRING-Konstruktion (DSSE (...)) ist es Ihnen gelungen, ein Argument zu erstellen k  Funktionen sind die GRÖSSTEN (Abb. 7.16). Dieses Element der Formel ist dynamisch. Wenn Sie in Zelle D3 den Wert 2 eingeben, wird das Array für k  gibt (1; 2) zurück, und wenn 5, dann - (1; 2; 3; 4; 5).

Abb. 7.16. Ein Streit k  Funktionen erhält der BIGGEST ein Array, das vom STRING (DFSR (...))

Sie können die Eingabe der Formel beenden, indem Sie die Funktion BIGGEST als Argument einfügen array1  SUMPRODUCT. Ich mag SUMPRODUCT mehr als nur SUM, weil das erste Drücken von Strg + Umschalt + Eingabetaste nicht erforderlich ist und das zweite.

Abb. 7.17. Vervollständigen Sie die Formel, indem Sie die Funktion BIGGEST als Argument setzen array1  SUMPRODUCT-Funktionen

Wir haben soeben einen Trick zerlegt, der als „Array von fortlaufenden Zahlen variabler Länge“ bezeichnet werden kann (Abb. 7.18).

Bevor wir zur nächsten Matrixformel übergehen, möchte ich Ihnen das Excel-Tool vorstellen. Tisch  (Abb. 7.19). Stellen Sie sich zum Erstellen einer Tabelle in eine beliebige Zelle im Bereich A1: B8 und gehen Sie das Menü durch: Zuhause → Stile → Als Tabelle formatieren  (oder drücken Sie Strg + T; T - Englisch). Weiter im Menü Konstruktor  Bereich Optionen für den Tabellenstil  Aktivieren Sie das Kontrollkästchen Zeilensummen. Filter in Spalte anwenden Verkaufsvolumen: Numerische Filter → Erste 10setzen 3 größte Listenelemente. Leider enthält die gefilterte Liste Duplikate, da der dritthöchste Wert durch zwei Zeilen dargestellt wird.

Abb. 7.19. Werkzeuggebrauch Tisch  zur Auswahl der drei Maximalwerte

Betrachten Sie ein weiteres Beispiel für die Verwendung eines Arrays von Konstanten. In haben wir eine Formel zur Berechnung der Wareneinsatzkosten unter Berücksichtigung von Rabatten abgeleitet. In diesem Fall wurden jeweils vier Rabattwerte in eine eigene Zelle gestellt. Versuchen wir, das Problem zu lösen, wenn vier Rabattwerte in eine Zelle gestellt werden (Abb. 7.20).

In einer Zelle müssen Sie konsequent:

Um gleichzeitig vier Zahlen aus einer Textzeichenfolge zu extrahieren, können Sie im Argument ein Array von Konstanten verwenden startposition  PSTR-Funktionen (Abb. 7.21). Die PSTR-Funktion gibt die angegebene Anzahl von Zeichen aus einer Textzeile zurück, beginnend an der angegebenen Position. Normalerweise Argument startposition  enthält einen Artikel. Wenn Sie beispielsweise die Formel = PSTR (C3; 4; 2) eingegeben haben, gibt die Funktion ab dem vierten Zeichen der C3-Zelle zwei Zeichen zurück.

Abb. 7.21. Array von Konstanten im Argument startposition  PSTR-Funktionen

In unserem Beispiel geben Sie jedoch vier Werte in das Argument ein startposition. Daher gibt die PSTR-Funktion vier Ergebnisse zurück. Überprüfen Sie dies, indem Sie die gesamte PSTR-Funktion markieren und F9 drücken (Abbildung 7.22). Beachten Sie, dass die PSTR-Funktion Textelemente zurückgibt. Dies wird uns nicht hindern, denn im nächsten Schritt werden wir mit diesem Array mathematische Operationen durchführen, die automatisch zur Umwandlung von Text in Zahlen führen.

Teilen Sie im nächsten Schritt das Array durch 100 und subtrahieren Sie das Ergebnis von der Einheit (Abb. 7.23). Da die Unterteilung zuerst durchgeführt wird, benötigen Sie keine zusätzlichen Klammern. Bewerten Sie die resultierende Formel, indem Sie sie auswählen und F9 drücken.

Beenden Sie die Eingabe der Formel, indem Sie die im vorherigen Schritt erhaltenen Elemente des Arrays mit der Funktion PRODUCER multiplizieren. multiplizieren Sie dann mit dem Preis in der Preisliste und runden Sie das Ergebnis abschließend auf zwei Dezimalstellen (Abb. 7.24). Da Sie im Argument für die PSTR-Funktion ein Array von Konstanten verwendet haben, ist für die Eingabe einer Formel nicht die Tastenkombination STRG + UMSCHALT + EINGABETASTE erforderlich.

Verwenden Sie eine Reihe von Konstanten in der VLR-Funktion, um Platz zu sparen.In Abb. Abbildung 25 zeigt eine typische Anwendung der VLR-Funktion (die erste Spalte in Tabelle F2: G5 ist sortiert, sodass die VLR nur nach einer ungefähren Übereinstimmung sucht). Wenn Sie davon ausgehen, dass sich die Daten in der Nachschlagetabelle (F2: G5) nicht ändern und es für Sie unpraktisch ist, dass die Tabelle Platz auf dem Blatt einnimmt, können Sie die Tabelle in der Formel fest codieren. Das Problem beim harten Codieren eines Konstantenarrays in einer CDF-Formel ist die relativ große Eingabemenge (das Array muss in geschweifte Klammern gesetzt werden, der Text muss in Anführungszeichen gesetzt werden und eine „Wolke“ von Trennzeichen muss eingegeben werden). Sie können einen kleinen Trick anwenden: Wenn Sie bereits eine Tabelle haben, erstellen Sie eine reguläre CDF und wählen Sie dann das Argument aus der Tisch und drücken Sie F9. Sie wandeln eine Referenz auf einen Bereich in ein Array von Konstanten um (Abb. 7.26). Anschließend können Sie die QFD-Formel in die Spalte kopieren und die Nachschlagetabelle löschen.

Abb. 7.25. Wenn sich die Daten in der Nachschlagetabelle ändern können, ist eine reguläre CDF mit Zellreferenzen eine ausgezeichnete Wahl.

Abb. 7.26. Argument hervorheben der Tisch  und drücken Sie F9; Sie konvertieren eine Bereichsreferenz in ein Array von Konstanten

Eine andere Möglichkeit, Platz auf einem Blatt zu sparen, besteht darin, dem Konstantenarray einen Namen zuzuweisen und diesen Namen dann in Formeln zu verwenden. Um ein Array von Konstanten zu benennen, rufen Sie die Registerkarte auf Formeln  und klicken Sie auf Name Manager  (oder drücken Sie Strg + F3) und klicken Sie dann auf Erstellen. In dem sich öffnenden Dialogfeld Namenserstellung  Legen Sie den Namen, den Umfang des Namens und den Bereich fest (in unserem Fall handelt es sich nicht um eine Zellreferenz, sondern um ein Array von Konstanten). Mit dem oben beschriebenen Trick können Sie vermeiden, dass Sie eine Reihe von Konstanten über die Tastatur eingeben (Abb. 7.27).

Sie können diesen Namen als Argument für die CDF-Funktion verwenden (Abbildung 7.28). Bitte beachten Sie, dass = CDF (A2; Commodity Price; 2) keine Matrixformel ist.

Abb. 7.28. Vorname Produktpreis  enthält ein Array von Konstanten

Sie haben gerade zwei Elemente (ein Array von Konstanten und einen bestimmten Namen) als Argument verwendet Tisch  Funktionen von CDF. Was ist mit anderen CDP-Argumenten? Können sie ein Array von Konstanten und / oder Array-Operationen enthalten? Wie Sie gesehen haben, das Argument gesuchter_Wert  Arrays können nicht verarbeitet werden. Und das Argument column_number? Kann es mit einem Array umgehen?

In Abb. 7.29 (Bereich A1: H4) zeigt eine Nachschlagetabelle mit den Namen der Waren in der ersten Spalte (alphabetisch sortiert) und den Kostenelementen in den Spalten 2–8. Ziel der Formel ist es, die Kosten in den Spalten 2, 4, 5, 7 und 8 zu addieren. Sie können ein Array von Konstanten als Argument verwenden column_number  VPR-Funktionen zum Abrufen von fünf Kostenwerten am Ausgang der Funktion. Sie sehen dies, wenn Sie die CDF-Funktion markieren und F9 drücken (Abb. 7.30).

Abb. 7.29. Geben Sie das Argument ein column_number  CFC-Funktionen Array von Spaltenkonstanten

Abb. 7.30. Seit dem Streit column_numberenthält fünf Elemente, gibt die VLR-Funktion fünf Werte zurück

Wenn Sie durch einfaches Drücken der Eingabetaste die Formel = SUMME (BFR (A7; A2: H4; (2; 4; 5; 7; 8)) eingeben, erhalten Sie die falsche Antwort (Abb. 7.31). Es ist zu sehen, dass die SUM-Funktion nur das erste Element des Arrays (1,35; 2,15; 3; 2; 4) verarbeitet hat, das von der CDF-Funktion zurückgegeben wurde. Um das richtige Ergebnis zu erhalten, geben Sie die Formel mit Strg + Umschalt + Eingabetaste in die Zelle ein (Abb. 7.32). Wenn Sie beim Drücken von drei Tasten sparen möchten, legen Sie die CDF in das SUMPRODUCT (Abb. 7.33).

Abb. 7.32. Unabhängig davon, ob das Argument enthält column_numbervPR funktioniert mit einer Reihe von Konstanten oder einer Referenz auf einen Bereich, für die Eingabe einer Formel müssen Sie Strg + Umschalt + Eingabetaste drücken

Es stellt sich also heraus, dass einige Funktionsargumente, die ein Array von Konstanten enthalten können, nicht die Verwendung von Strg + Umschalt + Eingabetaste erfordern (z. B. array  im MINDESTEN und GRÖSSTEN, der text  im DLSTR, logischer_Wert  in OR), und einige erfordern (zum Beispiel, column_number  in CDF).

Bisher haben Sie in diesem Kapitel konstante Arrays als Funktionsargumente verwendet. Konstante Arrays können auch in mathematischen Operationen und Array-Vergleichsoperationen verwendet werden. Wenn nur ein Array von Konstanten verwendet wird, muss für die Formel nicht die Tastenkombination Strg + Umschalt + Eingabetaste gedrückt werden (Abb. 7.34–7.36). Wenn ein Array auch in Form von Verweisen auf einen Bereich verwendet wird, müssen Sie zur Eingabe einer Formel die Tastenkombination Strg + Umschalt + Eingabetaste drücken (Abb. 7.37).

Abb. 7.34. Die Matrixformel (in Zelle C2) fragt: "Entspricht der Inhalt von Zelle B2 einem der folgenden Werte: V. P., Präsident oder Administrator?"; Die Vergleichsoperation enthält eine Operation mit einem Array von Konstanten im Argument boolean1  ODER funktioniert und erfordert kein Drücken von Strg + Umschalt + Eingabetaste

Abb. 7.35. Die Matrixformel berechnet einen Preiskoeffizienten basierend auf den Werten der einzelnen Rabatte. eine mathematische Multiplikationsoperation unter Verwendung eines Arrays von Konstanten im Argument durchgeführt number1Dazu müssen Sie nicht die Tastenkombination Strg + Umschalt + Eingabetaste drücken

Abb. 7.36. Die Formel multipliziert zwei Arrays von Konstanten und fasst dann die Ergebnisse der Arbeit zusammen (ich glaube nicht, dass Sie eine solche Formel verwenden werden, aber ich habe sie aufgenommen, um die Arbeit eines Arrays von Konstanten zu veranschaulichen); Die mathematische Operation mit Arrays von Konstanten wird in das Argument eingefügt number1  SUMME funktioniert und erfordert kein Drücken von STRG + UMSCHALT + EINGABETASTE

Abb. 7.37. Die Formel multipliziert die Arrays von Referenzen und Konstanten und fasst dann die Ergebnisse der Arbeit zusammen (es ist besser, die SUMPRODUCT-Funktion zu verwenden; ich habe diese Formel eingefügt, um die Funktionsweise des Arrays von Konstanten zu veranschaulichen). Eine mathematische Operation enthält nicht nur ein Array von Konstanten, sondern auch einen Verweis auf einen Zellbereich. Dazu müssen Sie die Tastenkombination Strg + Umschalt + Eingabetaste drücken

Kapitelübersicht

• Konstante Arrays können sowohl in normalen als auch in Array-Formeln verwendet werden
• Syntax konstanter Arrays: geschweifte Klammern am Anfang und Ende eines Arrays; ein Semikolon trennt Elemente in einer Zeichenfolge, ein Doppelpunkt - eine Zeile von einer anderen; Textelemente werden in doppelte Anführungszeichen und Zahlen, logische Werte und Fehlerwerte gesetzt - ohne Anführungszeichen
• Es werden drei Arten von konstanten Arrays verwendet: vertikal, horizontal und rechteckig
• Das Konstantenarray darf nicht mehr als 8192 Zeichen enthalten.
• So geben Sie eine Matrixformel ein:
  • Wenn nur ein Array von Konstanten verwendet wird, ist Strg + Umschalt + Eingabetaste nicht erforderlich.
  • Für einige Argumente von Funktionen, in die ein Array von Konstanten eingefügt wird, ist Strg + Umschalt + Eingabetaste nicht erforderlich (z. B. array  im MINDESTEN und GRÖSSTEN, der text  im DLSTR, logischer_Wert  im OP startposition  in PSTR, number1  in PRODUCTION und SUM
  • Einige Funktionsargumente erfordern das Drücken von Strg + Umschalt + Eingabetaste, wenn sie ein Array von Konstanten enthalten (z. B. das Argument column_number  in CDF)
• Die goldene Excel-Regel für Matrixformeln kann wie folgt ergänzt werden:
  • Wenn sich die Daten nicht ändern und Sie Platz auf dem Blatt sparen, verwenden Sie eine Reihe von Konstanten
  • Benennen Sie ein Array von Konstanten und verwenden Sie dessen Namen in den Formeln

Thema 4. Array-Formeln

Arrays sind eine bequeme Möglichkeit, Gruppen des gleichen Typs zu verarbeiten. Massen können Argumente in einigen Funktionen oder Formeln sein, die als Ergebnis von Berechnungen entweder einen einzelnen Wert oder ein Array neuer Werte zurückgeben. Formeln, die ein Array von Ergebnissen zurückgeben, werden aufgerufen tabellarische Formeln  oder matrixformeln. Ein Zellenblock, der die allgemeine Formel verwendet, wird aufgerufen array-Abstand. Ein Array ist eine bestimmte Art und Weise organisierte Liste von Daten.

Excel-Formeln können konstante Arrays verwenden. Ein Array von Konstanten besteht aus einer Menge von Werten, die in geschweiften Klammern eingeschlossen und durch ein Semikolon getrennt sind. Arrays können unterschiedliche Dimensionen haben. Es gibt eindimensionale und zweidimensionale Arrays. Ein eindimensionales Array mit einer Dimension von 1 × 3, das aus einer Zeile und drei Elementen in dieser Zeile besteht und folgende Elemente enthält:

aufgenommen als {5;7;9} d.h. ist eine Gruppe von Array-Werten, die in geschweiften Klammern eingeschlossen und durch ein Semikolon getrennt sind. Dies ist ein horizontales eindimensionales Array (Zeilenvektor). So schreiben Sie ein eindimensionales Array vertikal (Spaltenvektor):

sollte schreiben {5:7:9} d.h. Schreiben Sie die Menge der Array-Werte in geschweifte Klammern und trennen Sie sie mit einem Doppelpunkt.

Ein zweidimensionales Array besteht aus mehreren Zeilen und Spalten. Ein zweidimensionales Array mit einer Größe von 2 × 3 (bestehend aus zwei Zeilen und drei Spalten), das folgende Elemente enthält:

aufgenommen als {11;13;15:17;19;21} d.h. ist ein Satz von Array-Werten in geschweiften Klammern, wobei die Werte in einer Zeile durch ein Semikolon und die Zeilen durch einen Doppelpunkt voneinander getrennt sind.

Array-Konstanten können numerische, textuelle oder logische Werte enthalten, und Textkonstanten müssen in Anführungszeichen gesetzt werden. In ein Array können Sie Werte unterschiedlichen Typs eingeben, z. B. ein Array:

es scheint so ("A"; "b"; "c": 1; 2; 3).

Angenommen, es gibt zwei zweidimensionale Arrays:

Suchen Sie ein Array, von dem jedes Element die Summe der entsprechenden Elemente der Quell-Arrays ist:

Führen Sie die folgenden Schritte aus:

Wählen Sie den Zellenblock aus, in dem sich die Elemente des resultierenden Arrays befinden sollen: B2: C3.

Ohne die Auswahl zurückzusetzen, geben wir in der aktuellen Zelle (oder in der Formelzeile) den Ausdruck ein:

={1;2:3;4}+{10;20:30;40}.

Drücken Sie gleichzeitig die Tastenkombination Strg + Umschalt + Eingabetaste. Die Verwendung einer solchen Tastenkombination teilt Excel mit, dass eine Formel mit Arrays eingegeben wird. Excel fügt beim Eingeben einer Tabellenformel automatisch geschweifte Klammern hinzu. Diese Klammern sind in der Formelleiste sichtbar. Wenn diese Klammern jedoch manuell eingegeben werden, wird die Formel als Text wahrgenommen. Die Operanden dieser Formel sind konstante Arrays.

Im ausgewählten Bereich erhalten wir die Elemente des resultierenden Arrays. Formeln mit Arrays ergaben mehr als ein Ergebnis: Im Lösungsmodus sehen wir ein in Abbildung 224 gezeigtes Array von Zahlen, im Formeldarstellungsmodus das in Abbildung 225 gezeigte Array von Formeln.

Abbildung 224

Abbildung 225

Wie aus Abbildung 225 ersichtlich, wird die in die aktuelle Zelle eingegebene Formel in jede Zelle des ausgewählten Bereichs geschrieben. Diese "einzelne" Formel scheint gleichzeitig in vier Zellen in Form verschiedener Formeln des ausgewählten Bereichs zu existieren. Es ist jedoch unmöglich, Änderungen separat vorzunehmen. Dies kann nur durch Hervorheben des gesamten Intervalls erfolgen. In2:Mit3. Intervall In2:Mit3 stellt den Array-Abstand dar und die tabellarische Formel ist in jeder Zelle gespeichert.

Wenn in der Formel ein Array von Konstanten verwendet wird, müssen alle anderen Komponenten dieselbe Dimension wie das erste Array haben. Bei Bedarf erweitert Excel die erforderlichen Komponenten auf die gewünschte Größe. Alle Komponenten müssen dieselbe Anzahl von Zeilen und Spalten aufweisen wie die Komponenten mit den meisten Zeilen und Spalten.

Beispielsweise sei eine der Komponenten ein zweidimensionales Array, das aus zwei Zeilen und drei Spalten besteht. Um die Ergebnisse der Berechnung mit der Formel = (1; 2; 3: 4; 5; 6) * 2 darzustellen, muss ein Block mit 2 × 3 Zellen ausgewählt werden.

Eine der Komponenten des ursprünglichen Arrays ist ein 2 × 3-Array und die andere ist ein einzelner Wert. Bei der Berechnung nach dieser Formel wird die zweite Komponente automatisch auf ein Array der Dimension 2 × 3 erweitert, und diese Formel wird berechnet als = (1; 2; 3: 4; 5; 6) * (2; 2; 2: 2; 2; 2 ). Das Ergebnis ist in Abbildung 226 dargestellt.

Ein Array von Werten (entweder eine Array-Konstante oder ein Array von Konstanten) ist eine Sammlung von Zahlen oder Textwerten, die Sie verwenden können. Array-Konstanten müssen in einem bestimmten Format eingegeben werden, z. B. für Zahlen (1: 2: 3: 4: 5) oder für Textwerte ("Nord": "SÜD": "Ost": "West").

In einer normalen Formel können Sie einen Verweis auf die Zelle eingeben, die den Wert enthält, oder auf den Wert selbst, der auch als Konstante bezeichnet wird. Ebenso können Sie entweder eine Array-Referenz oder eingeben array von Wertenin Zellen enthalten (in der EXCEL-Hilfe heißt es array-Konstante).  Array-Konstantenmuss in einem bestimmten Format eingegeben werden.

Erstellen eines eindimensionalen konstanten Arrays

Vor dem Erstellen Array von Konstantenschauen Sie sich den Zellenbereich mit den üblichen Werten genauer an (siehe Beispieldatei)

• in einer Reihe von Zellen A 1: A 5   Geben Sie beispielsweise 5 aufeinanderfolgende Zahlen von 1 bis 5 ein.

• in der Zelle B1   Geben Sie die Formel ein = A1: A5;

• in Schriftrolle A1: A5   und drücken Sie die Taste;
• holen Sie sich einen Datensatz (1: 2: 3: 4: 5), bei dem es sich um eine Reihe von Werten aus dem Bereich handelt A1: A5

Diese Wertemenge ist ebenfalls in geschweiften Klammern angegeben. Die Werte selbst sind durch Doppelpunkte getrennt. Wenn die Werte in einer Zeichenfolge (im Bereich) platziert wurden A1: E1 ) und nicht in einer Spalte werden die Werte durch ein Semikolon (1; 2; 3; 4; 5) getrennt.

Lass uns erschaffen array-Konstante  in der Zelle B2. Geben Sie dazu den Ausdruck = (1: 2: 3: 4: 5) in die Zelle ein und drücken Sie ENTER. Array von Werten  nicht automatisch wie eingeklammert matrixformeln  nach dem Klicken STRG + UMSCHALT + EINGABETASTE. Dies muss manuell erfolgen. Nur der erste Wert des Arrays wird in der Zelle wiedergegeben, d.h. 1.

Gewöhnlich array von Werten  nicht in eine Zelle eingeben, da In diesem Fall ist es nicht möglich, einzelne Werte herauszuholen. Alle unsere Werte anzeigen array von Werten  Sie müssen 5 Zellen in einer Spalte auswählen (z. B. B1: B5 ), geben Sie den Ausdruck = (1: 2: 3: 4: 5) ein und klicken Sie auf STRG + UMSCHALT + EINGABETASTE.

Jetzt werden wir versuchen, ein Element des Arrays zu entfernen, zum Beispiel aus der Zelle B 3 . Wir bekommen eine Warnung " Ein Teil des Arrays kann nicht geändert werden"Ist eine bestimmte Art von Array - Schutz.

So vermeiden Sie mühsame Eingaben für vertikalarray von Konstanten können Sie die Formel = STRING (1: 5) verwenden. Nachdem Sie es in eine leere Zelle geschrieben haben, wählen Sie es in der Formelleiste aus und klicken Sie auf und dann auf ENTER. Holen Sie sich eine Reihe von Konstanten (1: 2: 3: 4: 5). Kopieren Sie den Inhalt der Zelle in die Zwischenablage ( STRG + C), wählen Sie dann den vertikalen Zellenbereich aus, der der Dimension des Arrays entspricht, und fügen Sie den Inhalt in die aktive Zelle ein Zwischenablageund klicken Sie auf STRG + UMSCHALT + EINGABETASTE.

Verwenden Sie die Formel, um die mühsame Eingabe von fortlaufenden Zahlen für ein horizontales Array von Konstanten zu vermeiden = TRANSPORT (LINIE (1: 5))  oder = SPALTE (A: E). Holen Sie sich ein Array von Konstanten = (1; 2; 3; 4; 5). Die Array-Werte werden durch ein Semikolon getrennt.

Die Verwendung eines Arrays von Konstanten

A. Multiplikation von Vektoren (Spalte für Spalte, Zeile für Zeile)
  Mit der Hilfe matrixformeln  multiplizieren Sie die Spalte der Werte ( B 2: B 6 ) am array von Konstanten  (1: 2: 3: 4: 5) und fasse zusammen. Konstante Anordnung  vertikal mit einem Doppelpunkt geschrieben, d.h. Stellt auch eine Spalte dar. Die Abmessungen der Spalte und des Arrays müssen übereinstimmen.

SUMME (B2: B6 * (1: 2: 3: 4: 5))

Nach Eingabe der Formel müssen Sie auf klicken STRG + UMSCHALT +ENTER.

Matrixformel  Führt zuerst die elementweise Multiplikation der Werte aus der Spalte und der Konstanten aus dem Array aus. Das Äquivalent dieser Formel ist der folgende gebräuchliche Ausdruck:

SUMME (B2 * 1; B3 * 2; B4 * 3; B5 * 4; B6 * 5)

Die letzte Addition ist die Addition von Werten mit der Funktion SUM ().

B. Werte überprüfen
  Überprüfen Sie, ob der Wert in der Zelle gleich ist A1   einer der spezifischen Werte: 4, 6 oder 9.

ODER (A1 = (4; 6; 9))

Nach Eingabe der Formel muss nicht mehr gedrückt werden STRG + UMSCHALT + EINGABETASTE. Ein solcher Datensatz kann die Zeit zum Erstellen einer Formel im Vergleich zur Verwendung verschachtelter Funktionen IF () erheblich verkürzen.

Konstante Array-Benennung

Array von Konstanten  zugeordnet werden kann. Dies geschieht normalerweise mit Konstanten, die eine Gruppe ähnlicher Werte bilden, z. B. Sequenzen mit einer endlichen Anzahl von Elementen = (1: 2: 3).

Zuweisen array von Konstanten  Der Name muss folgendermaßen lauten:

• auf der Registerkarte Formeln  in einer Gruppe Spezifische Namen  Team auswählen Vergeben Sie einen Namen.
• Auf dem Feld Vorname  eintreten Array123.
• Auf dem Feld Reichweite  eintreten array von Konstanten  (vergessen Sie nicht, die Klammern manuell einzugeben), zum Beispiel (1: 2: 3);
• Klicken Sie auf OK.

Beispiel finden wir durch Schreiben der Formel = SUMPRODUCT (GROSS (A1: A10; Mass123)).

Es wird davon ausgegangen, dass im Bereich A1: A10   Es gibt eine Liste mit numerischen Werten.

Erstellen eines zweidimensionalen Konstantenarrays

Gehen Sie folgendermaßen vor, um ein zweidimensionales Array von Konstanten zu erstellen:

• wählen Sie im Buch einen Zellenbereich aus vier Spalten und drei Zeilen ( A1: D3 ).
• in aktiver Zelle ( A1), in Formelzeile  Ausdruck eingeben ={1;2;3;4: 5;6;7;8: 9;10;11;12}
• drücken Sie die Tastenkombination STRG + UMSCHALT +ENTER

"Array of const" ist ein Array von Variablen, die als Konstanten deklariert sind. Sie werden direkt durch die TVarRec-Struktur dargestellt. Die Klammern beschränken einfach das Array. Mit einer Reihe von Konstanten können Sie eine variable Anzahl typsicherer Parameter an die Prozedur übergeben. Hier ist ein Beispiel:

typ

TVarRec = aufzeichnen

Data: record case Ganzzahl von

0: (L: LongInt);

1: (B: Boolean);

2: (C: Char);

3: (E: ^ Extended);

4: (S: ^ string);

5: (P: Zeiger);

6: (X: PChar);

7: (O: TObject);

ende;

Tag: Byte;

Stuff: Array [0 ..2] von Byte;

ende;

funktion PtrToStr (P: Pointer): string;

const

HexChar: Array [0 ..15] von Char = "0123456789ABCDEF";

funktion HexByte (B: Byte): string;

beginne

Ergebnis: = HexChar [B shr 4] + HexChar [B und 15];

ende;

funktion HexWord (W: Word): string;

beginne

Ergebnis: = HexByte (Hi (W)) + HexByte (Lo (W));

ende;

beginne

Ergebnis: = HexWord (HiWord (LongInt (P))) + ":" + HexWord (LoWord (LongInt (P)));

ende;

procedure Display (X: Array von const);

I: Integer;

beginne

für I: = 0 bis High (X) tun

mit TVarRec (X [I]), Data do

beginne

case Tag von

0: ShowMessage ("Integer:" + IntToStr (L));

1: wenn B dann

ShowMessage ("Boolean: True")

sonst

ShowMessage ("Boolean: False");

2: ShowMessage ("Char:" + C);

3: ShowMessage ("Float:" + FloatToStr (E ^));

4: ShowMessage ("String:" + S ^);

5: ShowMessage ("Zeiger:" + PtrToStr (P));

6: ShowMessage ("PChar:" + StrPas (X));

7: ShowMessage ("Objekt:" + O.ClassName);

ende;

ende;

ende;

procedure TForm1.Button1Click (Sender: TObject);

P: Array [0,5] von Char;

beginne

P: = "Hallo" # 0 ;

Anzeige ([-12345678, True, "A", 1.2345, "ABC", Ptr ($ 1234 , $5678 ), P,

Form1]);

ende;

Ein Array von Konstanten (Array von const) ist eigentlich ein offenes TVarRec-Array (eine Beschreibung der vordeklarierten Delphi-Typen finden Sie in der Online-Hilfe). Der folgende "Pseudocode" in Object Pascal kann als Framework für die weitere Entwicklung dienen:

procedure AddStuff (const A: Array von const);

var i: Integer;

beginne

für i: = Low (A) bis High (A) tun

mit A [i] do

case VType von

beginne

(Fügen Sie eine positive ganze Zahl hinzu, alle Realformate

Automatisch in Extended umgewandelt)

ende;

beginne

(add integer, alle ganzzahligen Formate

Automatisch nach LongInt umwandeln

ende;

beginne

wenn VObject DArray ist, dann

mit DArray (VObject) machen

beginne

(Wir fügen ein Array vom Typ double hinzu.)

sonst, wenn VObject dann IArray ist

mit IArray (VObject) machen

beginne

(Fügen Sie ein Array vom Typ Integer hinzu.)

ende;

ende;

ende; (Fall)

ende; (AddStuff)

Weitere Informationen finden Sie im Kapitel "Offene Arrays" der Online-Hilfe.
  Entnommen den Delphi Councils von Valentina Ozerov
  Sammlung Kuliba

Array von Konstanten zur Laufzeit

Gepostet von: Peter Below
  ... nun, diese Syntax unterstützt nicht direkt, da das Array von Konstanten Array of Const offenen Arrays ähnlich ist, hauptsächlich in Bezug auf die Eigenschaften der Kompilierungszeit. Aber Sie können diesen unangenehmen Moment umgehen, wenn Sie zumindest Grundkenntnisse darüber haben, wie ein offenes Array implementiert wird. Dazu benötigen wir: ein dynamisch zugewiesenes Array von TVarRec, das Ihre Parameter "akzeptiert", und den "Alias" (Alias) der Format-Funktion, mit dem Sie mit einem solchen Array arbeiten können, ohne vom Compiler "beschimpft" zu werden.

typ

(Typ für dynamisches Array-Array von TVarRecs deklarieren)

TVarArray = array [0 ..High (Word) div Sizeof (TVarRec) - 1] von TVarRec;

PVarArray = ^ TVarArray;

(Wir deklarieren einen Alias-Typ für die Format-Funktion. Parameter, die übergeben werden, haben

Der Stapel entspricht der Reihenfolge des Aufrufs wie im normalen Aufrufformat.

FormatProxy = function (const aFormatStr: string; var aVarRec: TVarRec;

HighIndex: Integer): string;

(AddVarRecs kopiert die in Array A übergebenen Parameter beginnend nach pRecs ^

Mit pRecs ^. highIndex - die größte verfügbare Index-pRecs-Nummer

Verteilte Elemente - 1.)

prozedur AddVarRecs (pRecs: PVarArray; atIndex, highIndex: Integer; const A:

array von const);

I: Integer;

beginne

if pRecs<>  nichts dann

für i: = 0 bis High (A) tun

beginne

wenn atIndex<= highIndex then

beginne

PRecs ^ [atIndex]: = A [i];

Inc (atIndex);

ende (wenn)

sonst

Pause;

ende; (Für)

ende; (AddVarRecs)

procedure TScratchMain.SpeedButton2Click (Sender: TObject);

S: Schnur;

Proxy: FormatProxy;

beginne

(Wir verteilen ein Array für vier Parameter, Indizes - 0..3)

GetMem (p, 4 * Sizeof (TVarRec));

(Parameter mit nachfolgenden Aufrufen von AddVarRecs hinzufügen)

AddVarRecs (p, 0, 3, [12, 0,5, "Template"]);

AddVarRecs (p, 3, 3, ["Test"]);

(Wir bekommen die Kräfte des Formats)

   @Proxy: = @ SysUtils.Format;

(Aufruf mit einem dynamisch generierten Parameterarray.

Natürlich kann auch ein Formatstring gebildet werden.

Und während der Ausführung des Programms. )

S: = Proxy ( "Ganzzahl:% d, Real:% 4.2f, Zeilen:% s,% s"p ^ [0], 3);

(Ergebnis anzeigen)

ShowMessage (S);

endlich

FreeMem (p, 4 * Sizeof (TVarRec));

ende;

ende;

Ich hoffe du verstehst das Prinzip. Natürlich gibt es Einschränkungen. Sie können numerische Werte, Zeichenfolgenvariablen und Literale an AddVarRecs übergeben, jedoch keine Zeichenfolgenausdrücke! In diesem Fall muss der Compiler eine temporäre Zeichenfolge auf dem Stapel bilden, diese an AddVarRecs (oder besser an die Adresse in TVarRec) übergeben. Diese Zeichenfolge ist möglicherweise nicht mehr vorhanden oder wird möglicherweise auf dem Stapel mit anderen Daten überschrieben, wenn Sie letztendlich einen Proxy senden ganze Reihe!
  Es wurde nur in Delphi 1.0 getestet!