Ветвления. Массивы. Циклы. Массивы констант
В Microsoft Excel можно создавать массивы, которые не хранятся в диапазонах ячеек. Их принято называть массивами констант . В этом уроке Вы узнаете, что же такое массивы констант и как с ними работать в Excel.
Коротко о массивах констант
Чтобы создать массив констант, введите его элементы и заключите их в фигурные скобки. Например, на рисунке ниже представлен массив, состоящий из 6 констант:
Такой массив можно использовать в формулах Excel. Например, следующая формула суммирует значения этого массива:
СУММ({1;2;3;4;5;6})
В формулах можно обрабатывать сразу несколько массивов. Например, следующая формула вернет максимальное значение, которое получится в результате сложения двух массивов констант:
МАКС({1;2;3;4;5;6}+{7,8,9,10,11,12})
Массивы констант могут содержать числа, текст, логические значения и значения ошибки #Н/Д :
={12;"Текст";ИСТИНА;ЛОЖЬ;#Н/Д}
У Вас может возникнуть резонный вопрос: Зачем нужен такой массив? Отвечу на него в виде примера.
Пример применения массива констант в Excel
На рисунке ниже приведен список студентов, которые получили определенные оценки:
Наша задача перевести оценку из числового вида в ее словесное описание и вывести соответствующие значения в диапазоне C2:C7. В данном случае создавать отдельную табличку для хранения текстового описания оценок не имеет смысла, поэтому выгоднее создать следующий массив констант:
={"";"Неудовл.";"Удовл.";"Хорошо";"Отлино"}
В данном случае первый элемент массива содержит пустую строку, поскольку предполагается, что оценки 1 быть не может.
Тогда формула, возвращающая нужный нам результат, будет выглядеть следующим образом:
В этом примере функция ИНДЕКС возвращает значение элемента из массива констант, положение которого задано порядковым номером (оценкой).
Данная формула не является формулой массива, хоть она и содержит массив. Поэтому при ее вводе достаточно нажать клавишу Enter .
Конечно же, мы в силах скопировать данную формулу в остальные ячейки и получить нужный нам результат:
Но грамотнее будет использовать многоячеечную формулу массива. Выглядеть она будет следующим образом:
Мы можем пойти еще дальше и присвоить массиву констант имя. Имя назначается точно так же, как и обычной константе , через диалоговое окно Создание имени :
Не забывайте указывать знак равенства в поле Диапазон , иначе Excel воспримет массив как текстовую строку.
Теперь формула выглядит менее пугающей:
Как видите, в некоторых случаях массивы констант бывают даже очень полезны.
Итак, в данном уроке Вы познакомились с массивами констант и их применением в Excel. Если желаете получить еще больше информации о массивах, читайте следующие статьи.
Константы типа массивов
Объявление константы типа массива определяет значения компонентов массива.
Тип компонентов массива может быть любым, кроме файлового .
Пример
type
Status = (Active, Passive, Waiting);
StatusMap = array of String;
const StatStr: StatusMap = ("Active", "Passive", "Waiting");
{ компоненты StatStr:
StatStr = "Active"
StatStr = "Passive"
StatStr = "Waiting" }
Символьные массивы
Упакованные константы со строковым типом (символьные массивы) могут быть определены и как одиночные символы, и как строки. Например, такое определение:
const Digits: array of Char = ("0", "1", "2", "3", "4", "5",
"6", "7", "8", "9");
может быть выражено более коротко:
const Digits: array of Char = "0123456789";
Нуль-основанные символьные массивы
Нуль-основанный символьный массив - это такой массив, в котором индекс первого элемента равен нулю, а последнего - положительному ненулевому целому числу. Например:
array of Char;
Если вы включаете расширенный синтаксис (с помощью директивы компилятора {$X+} ), то нуль-основанный символьный массив может быть инициализирован строкой, длина которой меньше, чем объявленная длина массива. Например:
const FileName = array of Char = "TEST.PAS";
Если строка короче, чем длина массива, то оставшиеся символы устанавливаются равными NULL (0), и массив будет содержать строку с нулевым окончанием .
Константы - многомерные массивы
Такие константы определяются заключением констант каждой размерности в отдельные наборы круглых скобок, разделенные запятыми.
Например, такое объявление:
type
Cube = array of Integer;
const Maze: Cube = (((0, 1), (2, 3)), ((4, 5), (6, 7)));
создаёт инициализированный массив Maze:
Maze = 0
Maze = 1
Maze = 2
Maze = 3
Maze = 4
Maze = 5
Maze = 6
Maze = 7
Ада
Агрегаты (так они называются в языке Ада) можно использовать не только при инициализации констант, но и вообще в любых выражениях. В ЛЮБЫХ!
procedure Arr_Agg is
Type Cube is array (0 .. 1, 0 .. 1, 0 .. 1) of Integer;
Maze: Cube:= (((0, 1), (2, 3)), ((4, 5), (6, 7)));
Begin
Maze:= (((7, 6), (5, 4)), ((3, 2), (1, 0)));
end Arr_Agg;
Подобные скобочные выражения могут быть восприняты компилятором двояко, и в этом случае нужно явно указывать тип с помощью конструкции тип"(значение). Например:
Cube"(((7, 6), (5, 4)), ((3, 2), (1, 0)))
будет всегда воспринято однозначно. В приведённом выше примере тип указывать не потребовалось, потому что сопоставление типов в языке Ада производится не только снизу вверх, но и сверху вниз. Переменной Maze можно присвоить только выражение типа Cube, поэтому указание типа было необязательно.
Одним из следствием двустороннего сопоставления типов является возможность перегружать функции не только по типу аргументов , но и по типу результата.
Также внутри агрегатов можно указывать индексы:
Демонстрация гибкости массивных агрегатов
Cube"(0 => (1 => (0 | 1 => 7), 0 => (1 => 5, others => 4)),
1 => ((0 .. 1 => 3), others => (others => <>)))
Индексированные элементы должны следовать за неиндексированными. На порядок элементов нет ограничений. Индексированный элемент может задавать значение сразу нескольким элементам массива. Для этого отдельные индексы и диапазоны индексов перечисляются через | (знак трубы). Также можно в конце списка использовать зарезервированное слово others, чтобы задать значение оставшимся элементам:
(1 | 3 .. 5 | 7 | 9 .. 11 | 13 .. 15 => True, others => False)
Лексема <> означает значение по умолчанию.
Как и в Borland Pascal, строковый литерал эквивалентен агрегату символов.
"Array of const" это массив переменных, декларированных как константы. Непосредственно они представлены структурой TVarRec. Скобки просто ограничивают массив. Массив констант дает вам возможность передавать процедуре переменное количество параметров type-safe (безопасным) способом. Вот пример:
type
TVarRec = record
Data: record case Integer of
0 : (L: LongInt ) ;
1 : (B: Boolean ) ;
2 : (C: Char ) ;
3 : (E: ^Extended) ;
4 : (S: ^string) ;
5 : (P: Pointer ) ;
6 : (X: PChar ) ;
7 : (O: TObject ) ;
end ;
Tag: Byte ;
Stuff: array [ 0 ..2 ] of Byte ;
end ;
function PtrToStr(P: Pointer ) : string ;
const
HexChar: array [ 0 ..15 ] of Char = "0123456789ABCDEF" ;
function HexByte(B: Byte ) : string ;
begin
Result:= HexChar[ B shr 4 ] + HexChar[ B and 15 ] ;
end ;
function HexWord(W: Word ) : string ;
begin
Result:= HexByte(Hi (W) ) + HexByte(Lo (W) ) ;
end ;
begin
Result:= HexWord(HiWord(LongInt (P) ) ) + ":" + HexWord(LoWord(LongInt (P) ) ) ;
end ;
procedure Display(X: array of const ) ;
I: Integer ;
begin
for I:= 0 to High (X) do
with TVarRec(X[ I] ) , Data do
begin
case Tag of
0 : ShowMessage("Integer: " + IntToStr (L) ) ;
1 : if B then
ShowMessage("Boolean: True" )
else
ShowMessage("Boolean: False" ) ;
2 : ShowMessage("Char: " + C) ;
3 : ShowMessage("Float: " + FloatToStr (E^) ) ;
4 : ShowMessage("String: " + S^) ;
5 : ShowMessage("Pointer: " + PtrToStr(P) ) ;
6 : ShowMessage("PChar: " + StrPas (X) ) ;
7 : ShowMessage("Object: " + O.ClassName ) ;
end ;
end ;
end ;
procedure TForm1.Button1Click (Sender: TObject ) ;
P: array [ 0 ..5 ] of Char ;
begin
P:= "Привет" #0 ;
Display([ -12345678 , True , "A" , 1.2345 , "ABC" , Ptr ($1234 , $5678 ) , P,
Form1] ) ;
end ;
Массив констант (array of const) фактически является открытым массивом TVarRec (описание предекларированных типов Delphi вы можете найти в электронной справке). Приведенный ниже "псевдокод" на языке Object Pascal может послужить скелетом для дальнейшего развития:
procedure AddStuff(const A: array of const ) ;
var i: Integer ;
begin
for i:= Low (A) to High (A) do
with A[ i] do
case VType of
begin
{ добавляем натуральное число, все real-форматы
Автоматически приводятся к extended }
end ;
begin
{ добавляем целое число, все integer-форматы
Автоматически приводятся к LongInt }
end ;
begin
if VObject is DArray then
with DArray(VObject) do
begin
{ добавляем массив double-типа }
else if VObject is IArray then
with IArray(VObject) do
begin
{ добавляем массив integer-типа }
end ;
end ;
end ; { Case }
end ; { AddStuff }
Для получения дополнительной информации загляните в главу "open arrays" электронной справки.
Взято из Советов по Delphi от Валентина Озерова
Сборник Kuliba
Массив констант во время выполнения приложения
Автор: Peter Below
...хорошо, непосредственно это синтаксис не поддерживает, поскольку массив констант Array of Const подобен открытым массивам, главным образом в части характеристик времени компиляции. Но вы можете обойти этот неприятный момент, обладая хотя бы начальными знаниями того, как реализован открытый массив. Что нам для этого необходимо: динамически размещенный массив array of TVarRec, который "принимает" ваши параметры, и "псевдоним" (alias) функции Format, позволяющий работать с таким массивом без "ругани" со стороны компилятора.
type
{ объявляем тип для динамического массива array of TVarRecs }
TVarArray = array [ 0 ..High (Word ) div Sizeof (TVarRec) - 1 ] of TVarRec;
PVarArray = ^TVarArray;
{ Объявляем alias-тип для функции Format. Передаваемые параметры будут иметь
В стеке тот же самый порядок вызова, что и при нормальном вызове Format }
FormatProxy = function (const aFormatStr: string ; var aVarRec: TVarRec;
HighIndex: Integer ) : string ;
{ AddVarRecs копирует параметры, передаваемые в массиве A в pRecs^, начиная
С pRecs^. highIndex - самый большой доступный индекс pRecs, число
Распределенных элементов - 1. }
procedure AddVarRecs(pRecs: PVarArray; atIndex, highIndex: Integer ; const A:
array of const ) ;
I: Integer ;
begin
if pRecs <> nil then
for i:= 0 to High (A) do
begin
if atIndex <= highIndex then
begin
PRecs^[ atIndex] := A[ i] ;
Inc (atIndex) ;
end { If }
else
Break ;
end ; { For }
end ; { AddVarRecs }
procedure TScratchMain.SpeedButton2Click (Sender: TObject ) ;
S: string ;
Proxy: FormatProxy;
begin
{ распределяем массив для четырех параметров, индексы - 0..3 }
GetMem (p, 4 * Sizeof (TVarRec) ) ;
{ добавляем параметры последующими вызовами AddVarRecs }
AddVarRecs(p, 0 , 3 , [ 12 , 0.5 , "Шаблон" ] ) ;
AddVarRecs(p, 3 , 3 , [ "Тест" ] ) ;
{ получаем полномочия Format }
@Proxy:= @SysUtils.Format ;
{ Вызов с динамически сгенерированным массивом параметров.
Естественно, строка формата может также быть сформирована
И во время выполнения программы. }
S:= Proxy("Целое: %d, Реальное: %4.2f, Строки: %s, %s" , p^[ 0 ] , 3 ) ;
{ выводим результат }
ShowMessage(S) ;
finally
FreeMem (p, 4 * Sizeof (TVarRec) ) ;
end ;
end ;
Я надеюсь вы поняли принцип. Естественно, имеются ограничения. Вы можете передавать в AddVarRecs числовые величины, строковые переменные и литералы, но не в коем случае не строковые выражения! В этом случае компилятор должен для хранения результата сформировать в стеке временную строку, передать ее в AddVarRecs (или лучше по адресу в TVarRec), и она может прекратить свое существование или может быть перезаписана в стеке другими данными, если в конечном счете вы передадите в Proxy целый массив!
Тестировалось только в Delphi 1.0!
Если ввести формулу массива , вы чаще всего использовать диапазон ячеек на листе, но вам не нужно. Вы также можете использовать константы массива , просто введите в строке формул фигурные скобки значения: {}. Затем вы можете имя константы чтобы облегчить для повторного использования.
Константы можно использовать как в формулах массива, так и отдельно от них.
Использование константы для ввода значений в столбец
Чтобы ввести значения в один столбец, например в три ячейки столбца C, сделайте следующее.
Использование константы для ввода значений в строку
Чтобы быстро ввести значения в одну строку, например в ячейки F1, G1 и H1, сделайте следующее.
Использование константы для ввода значений в несколько столбцов и строк
Использование константы в формуле
Теперь, когда вы уже познакомились с константами массива, рассмотрим практический пример.
Введите или скопируйте и вставьте в любую пустую ячейку следующую формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД:
=СУММ(A1:E1*{1,2,3,4,5})
В ячейке A3 появится значение 85 .
Что произошло? Вы умножили значение в ячейке A1 на 1, значение в ячейке B2 на 2 и т. д, а затем с помощью функции СУММ выполнили сложение этих результатов. Эту же формулу вы могли ввести в виде =СУММ(A1*1,B1*2,C1*3,D1*4,E1*5) .
А при желании можно ввести оба набора значений в виде констант массива:
=СУММ({3,4,5,6,7}*{1,2,3,4,5})
Для этого скопируйте формулу, выделите пустую ячейку, вставьте формулу в строку формул, а затем нажмите клавиши CTRL+SHIFT+ВВОД. Вы получите такой же результат.
Примечания: Если константы не работают
Тема 4. Формулы с массивами
Массивы являются удобным средством обработки групп однотипных данных. Массиы могут быть аргументами в некоторых функциях или формулах, возвращающих в результате вычислений либо единственное значение, либо массив новых значений. Формулы, возвращающие массив результатов, называются табличными формулами или формулами массива . Блок ячеек, в котором используется общая формула, называется интервалом массива . Массив представляет собой определенным способом организованный список каких-либо данных.
В формулах Excel можно использовать массивы констант. Массив констант строится как совокупность значений, заключенных в фигурные скобки и разделенных точкой с запятой. Массивы могут иметь различную размерность. Различают одномерные и двумерные массивы. Например, одномерный массив, размерностью 1×3, состоящий из одной строки и трех элементов в этой строке и содержащий элементы:
записывается как {5;7;9} , т.е. представляет собой совокупность значений массива, заключенных в фигурные скобки и разделенных точкой с запятой. Это горизонтальный одномерный массив (вектор-строка). Чтобы записать одномерный массив вертикально (вектор-столбец):
следует записать {5:7:9} , т.е. записать совокупность значений массива в фигурных скобках и разделить их двоеточием.
Двумерный массив состоит из нескольких строк и столбцов. Например, двумерный массив, размерностью 2×3, (состоящий из двух строк и трех столбцов), содержащий элементы:
записывается как {11;13;15:17;19;21} , т.е. представляет собой совокупность значений массива, заключенных в фигурные скобки, при этом значения, расположенные в одной строке отделяются друг от друга точкой с запятой, а строки двоеточием.
Массивы констант могут содержать числовые, текстовые или логические значения, причем текстовые константы должны быть заключены в кавычки. В один массив можно вводить значения разных типов, например, массив:
представляется как {«a»;«b»;«c»:1;2;3} .
Пусть имеются два двумерных массива:
Найти массив, каждый элемент которого представляет собой сумму соответствующих элементов исходных массивов:
Выполним следующие действия:
Выделим блок ячеек, в котором должны расположиться элементы результирующего массива: В2:С3.
Не сбрасывая выделения, в текущую ячейку (или в строку формул) введем выражение:
={1;2:3;4}+{10;20:30;40}.
Одновременно нажмем комбинацию клавиш Ctrl+Shift+Enter. Использование такой комбинации клавиш сообщает Excel, что вводится формула с массивами. Excel автоматически добавляет фигурные скобки при вводе табличной формулы. Эти скобки видны в строке формул, но если эти скобки ввести «вручную», формула будет воспринята как текст. Операндами этой формулы являются массивы констант.
В выделенном диапазоне получим элементы результирующего массива. Формулы с массивами выдали более одного результата: в режиме решения видим массив чисел изображенный на рисунке 224, в режиме показа формул – массив формул на рисунке 225.
Рисунок 224
Рисунок 225
Как видно из рисунка 225, в каждой ячейке выделенного диапазона записана формула, введенная в текущую ячейку. Эта «единичная» формула как бы существует одновременно в четырех ячейках в виде различных формул выделенного диапазона. Но внести изменения в них по отдельности нельзя. Это можно сделать, только выделив весь интервал В 2:С 3. Интервал В 2:С 3 представляет собой интервал массива, и табличная формула хранится в каждой ячейке.
Если в формуле используется массив констант, все остальные компоненты должны иметь ту же размерность, что и первый массив. В случае необходимости Excel расширяет нужные компоненты до требуемых размеров. Все компоненты должны иметь такое же количество строк и столбцов, как компоненты с наибольшим числом строк и столбцов.
Например, пусть одна из компонент – двумерный массив, состоящий из двух строк и трех столбцов. Для представления результатов вычисления по формуле ={1;2;3:4;5;6}*2 требуется выделить блок ячеек 2×3.
Одна из компонент исходного массива – массив 2×3, а другая – одиночное значение. При вычислении по этой формуле автоматически будет расширена вторая компонента до массива размерности 2×3 и произойдет вычисление по этой формулы как ={1;2;3:4;5;6}*{2;2;2:2;2;2}. Результат представлен на рисунке 226.
