Телевизор 

Массивы констант в Excel. Массивы констант в MS EXCEL. Именование массива констант

"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!

В 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, строковый литерал эквивалентен агрегату символов.

Если ввести формулу массива , вы чаще всего использовать диапазон ячеек на листе, но вам не нужно. Вы также можете использовать константы массива , просто введите в строке формул фигурные скобки значения: {}. Затем вы можете имя константы чтобы облегчить для повторного использования.

Константы можно использовать как в формулах массива, так и отдельно от них.

Использование константы для ввода значений в столбец

Чтобы ввести значения в один столбец, например в три ячейки столбца 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+ВВОД. Вы получите такой же результат.

Примечания: Если константы не работают

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Ранее в этой книге вам неоднократно встречались массивы констант. Действительно, каждый раз, когда вы проводили оценку работы элементов формул, используя клавишу F9, вы видели пример массива констант. Например, в первой формуле массива, созданного в (снова показан на рис. 7.1), вы можете выделить D2:D5-C2:C5, и нажав F9, увидеть результирующий массив {3;–8;7;6}. Однако, ранее, оценив массив, вы сразу же нажимали Ctrl+Z, чтобы оставить внутри формулы не жесткий код, а ссылку. Если бы вы не использовали Ctrl+Z, вы бы создали массив констант, который бы не изменялся при изменении исходных данных на листе. В этой главе вы узнаете о ситуациях, в которых использование массива констант дает преимущества.

Рис. 7.1. Выделите массив (слева), нажмите F9 (справа), и получите массив констант

Скачать заметку в формате или , примеры в формате

Рассмотрим синтаксис, используемый для создания массива констант, и примеры, иллюстрирующие три типа массива констант, с которыми вы можете столкнуться в Excel.

Вертикальный массива констант (рис. 7.2). В ячейке А6 создайте ссылку на массив =А2:А4, и нажмите F9. Вы увидите массив элементов, расположенный в фигурных скобках; текст всегда отображается в кавычках; двоеточие – разделитель, указывающий, что элементы расположены в столбце друг под другом.

Рис. 7.2. Вертикальный диапазон ячеек: выделите ссылку (слева) и нажмите F9 (справа)

Горизонтальный массив констант (рис. 7.3). В ячейке А6 создайте ссылку на массив =А2:В2, и нажмите F9. Вы увидите массив элементов, расположенный в фигурных скобках; текст всегда отображается в кавычках; цифры отображаются просто цифрами; точка с запятой, – разделитель, указывающий, что элементы расположены в одном ряду, в соседних столбцах.

Рис. 7.3. Горизонтальный диапазон ячеек: выделите ссылку (слева) и нажмите F9 (справа)

Прямоугольный массив констант (рис. 7.4). В ячейке А6 создайте ссылку на массив =А2:В4, и нажмите F9. Вы увидите массив элементов, расположенный в фигурных скобках; текст всегда отображается в кавычках; цифры отображаются просто цифрами; используются два разделителя: точка с запятой – для перехода к соседней колонке в той же строке, и двоеточие – для перехода в первую колонку новой строки.

Рис. 7.4. Прямоугольный диапазон ячеек: выделите ссылку (слева) и нажмите F9 (справа)

У вас может возникнуть вопрос, существуют ли ситуации, когда массив констант будет полезен? Не входит ли он в противоречие с золотым правилом Excel: если исходные данные могут измениться, разместите их на листе и сошлитесь на них внутри формулы; если исходные данные не изменятся, жестко закодируйте их в формуле. Польза от массива констант заключается в том, что формула с ним не требует нажатия Ctrl+Shift+Enter. Таким образом, если исходные данные не изменятся, используйте массив констант.

Рассмотрим пример из игры в гольф (рис. 7.5). Цель – найти сумму трех лучших (минимальных) результатов, при этом не учитывать 4-й и последующие результаты, если они равны третьему (видно, что в нашем примере как раз такой случай с числом 70). Для решения задачи отлично подойдет функция НАИМЕНЬШИЙ, аргумент k которой позволит выбрать требуемые значения: k = 1 даст минимальное значение, k = 2 – второе и т.д. Как видно на рис. 7.5, сумма трех наименьших при k = 1, 2, 3 дает правильный ответ. Отличная формула, имеющая, пожалуй, лишь тот недостаток, что потребует времени на написание, если вам нкжно отобрать, скажем, 10 минимальных значений.

Рис. 7.5. Аргумент k функции НАИМЕНЬШИЙ позволяет получить k-ое наименьшее значение

Чтобы создать более компактную формулу можно поместить массив в аргумент функции (рис. 7.6). И здесь вам пригодится массив констант {1,2,3}. Вы можете использовать жесткий код, поскольку число отбираемых значений меняться не будет. Поскольку аргумент k имеет три значения, функция НАИМЕНЬШИЙ также вернет три значения, которые и будут просуммированы. Чтобы убедиться в этом, выделите аргумент число1 функции СУММ и нажмите F9 (рис. 7.7). Видно, что, хотя третье наименьшее значение имеют два равных числа 70, функция НАИМЕНЬШИЙ(B3:B8;{1;2;3}) вернула только три числа.

Примечание: не важно, зададите ли вы массив констант горизонтальным {1;2;3} или вертикальным {1:2:3}.

Для ввода формулы не потребовались Ctrl+Shift+Enter. Однако, если вы вместо массива констант в аргументе k, используете ссылку на диапазон (рис. 7.8), ввод формулы потребует нажатия Ctrl+Shift+Enter (в противном случае вы получите ошибку #ЗНАЧ!). Заметим, что Ctrl+Shift+Enter можно всё же избежать, если воспользоваться формулой: =СУММПРОИЗВ(НАИМЕНЬШИЙ(B3:B8;D3:D5))

Примечание: HELP функции НАИМЕНЬШИЙ не содержит упоминания, что аргумент k может быть представлен массивом констант. Это является очередным свидетельством того, насколько плохо задокументированы возможности Excel по работе с формулами массива. Я узнал об этом на форуме MrExcel и многолетних изысканий методом проб и ошибок.

Если вам потребуется просуммировать все разделенные (ничейные) результаты, можно воспользоваться обычной функцией СУММЕСЛИ, а не формулой массива (рис. 7.9).

Рис. 7.9. Формула суммирует три минимальных балла, в том числе равные

Следующий пример демонстрирует использование функции НАИБОЛЬШИЙ (рис. 7.10). Цель – суммировать три самых длительных полета бумеранга у каждого участника.

А что, если вы хотите, чтобы формула суммировала переменное число максимальных или минимальных значений? На рис. 7.11 приведены две формулы, которые суммируют три наибольших значения. Формула исключает деленные третьи значения, формула включает таковые. Отличие этих формул от рассмотренных ранее заключается в том, что число максимальных значений не жестко зашито в формулу, а определяется значениями в ячейках D3 и D6. При изменении чисел в ячейках D3 и D6, сумма в ячейках Е3 и Е6 будет изменяться. На рис. 7.12 показаны суммы для двух максимальных значений.

Рис. 7.12. Формулы 1 и 2 дают одинаковые результаты для двух максимальных значений, так как среди вторых значений нет одинаковых

Формула , наверное, проста для понимания, а вот формула содержит элементы, с которыми вы ранее не встречались. Используя функции СТРОКА и ДВССЫЛ можно создать динамический массив переменной длины из последовательных чисел. Начните ввод формулы с функции ДВССЫЛ (рис. 7.13). ДВССЫЛ запрограммирована возвращать ссылку, заданную текстовой строкой. В нашем случае элемент аргумент ссылка_на_ячейку " 1: " &D3 представляет собой ссылку на строки 1:3. Чтобы убедиться в этом, выделите этот элемент и нажмите F9.

Рис. 7.13. Конкатенация " 1: " и D3 (слева) дает ссылку на строки " 1:3 " (справа)

Если вы захотите оценить, что возвращает формула =ДВССЫЛ(" 1: " &D3), выделите ее целиком и нажмете F9 (рис. 7.14), то получите ошибку «Формула слишком длинная. Ее длина не должна превышать 8192 знака».

Рис. 7.14. При оценке формулы =ДВССЫЛ(" 1: " &D3) возвращается ошибка

Ошибку вызвана тем, что ссылка на строки 1:3 содержит все ячейки в строках с 1 по 3. На листе Excel (начиная с версии 2007) 16 384 столбца, т.е. в трех строках содержится 49 152 ячейки. Сама по себе функция ДВССЫЛ не содержит ошибки, поэтому если поместить ее в качестве аргумента функции СТРОКА, вы получите требуемый результат (рис. 7.15). Заметим, что аргумент функции СТРОКА ожидает одно значение. Вы же «подсовываете» ему 49 152 ссылки на ячейки. Правда все эти ссылки относятся к строкам 1, 2 и 3. Так что функция СТРОКА вернет массив {1:2:3} (убедитесь в этом, выделив целиком функцию СТРОКА, и нажав F9). Обратите внимание, что поскольку речь идет о строках, в синтаксисе массива в качестве разделителя присутствует двоеточие.

Рис. 7.15. Функция СТРОКА возвращает номер строки аргумента ссылка; поскольку ДВССЫЛ возвращает ячейки, относящиеся к строкам 1, 2 и 3, функция СТРОКА возвращает массив {1:2:3}

Итак, с помощью конструкции СТРОКА(ДВССЫЛ(…)) вам удалось создать аргумент k функции НАИБОЛЬШИЙ (рис. 7.16). Этот элемент формулы является динамичным. Если в ячейке D3 ввести значение 2, массив для k вернет {1;2}, а если 5, то – {1;2;3;4;5}.

Рис. 7.16. Аргументом k функции НАИБОЛЬШИЙ получает массив, созданный конструкцией СТРОКА(ДВССЫЛ(…))

Вы можете закончить ввод формулы путем размещения функции НАИБОЛЬШИЙ в качестве аргумента массив1 функции СУММПРОИЗВ. Мне СУММПРОИЗВ нравится больше, чем просто СУММ, поскольку первая не требует нажатия Ctrl+Shift+Enter, а вторая – требует.

Рис. 7.17. Завершите ввод формулы путем размещения функции НАИБОЛЬШИЙ в качестве аргумента массив1 функции СУММПРОИЗВ

Мы только что разобрали трюк, который можно назвать «массив последовательных чисел переменной длины» (рис. 7.18).

Прежде чем перейти к следующей формуле массива, хочу познакомить вас с инструментом Excel –Таблица (рис. 7.19). Для создания таблицы встаньте на любую ячейку в диапазоне А1:В8 и пройдите по меню: Главная → Стили → Форматировать как таблицу (или нажмите Ctrl+T; Т – английское). Далее в меню Конструктор область Параметры стилей таблицы поставьте галочку напротив Строка итогов . Примените фильтр в колонке Объем продаж : Числовые фильтры → Первые 10 , задайте 3 наибольших элемента списка . К сожалению, отфильтрованный список будет включать дубли, так как третье наибольшее значение представлено двумя строками.

Рис. 7.19. Использование инструмента Таблице для отбора трех максимальных значений

Рассмотрим еще один пример того, как можно использовать массив констант. В мы вывели формулу для расчета стоимости товара с учетом скидок. При этом четыре значения скидок были размещены каждая в своей ячейке. Попробуем решить задачу, когда четыре значения скидок размещены в одной ячейке (рис. 7.20).

В одной ячейке нужно последовательно:

Чтобы одновременно извлечь четыре числа из текстовой строки, можно использовать массив констант в аргументе начальная_позиция функции ПСТР (рис. 7.21). Функция ПСТР возвращает заданное число знаков из строки текста, начиная с указанной позиции. Обычно аргумент начальная_позиция содержит один элемент. Например, если вы ввели формулу =ПСТР(C3;4;2), функция вернет два символа, начиная с 4-го из ячейки С3.

Рис. 7.21. Массив констант в аргументе начальная_позиция функции ПСТР

Однако, в нашем примере, вы вводите четыре значения в аргумент начальная_позиция . Поэтому функция ПСТР вернет четыре результата. Убедитесь в этом, выделив целиком функцию ПСТР и нажав F9 (рис. 7.22). Обратите внимание, что функция ПСТР возвращает текстовые элементы. Это не помешает нам, потому что на следующем шаге мы выполним с этим массивом математические операции, что автоматически приведет к преобразованию текста в числа.

На следующем шаге разделите массив на 100, а затем вычтете результат из единицы (рис. 7.23). Поскольку деление будет выполняться первым, вам не нужны дополнительные скобки. Оцените получившуюся формулу, выделив ее и нажав F9.

Завершите ввод формулы, умножая элементы массива, полученного на предыдущем шаге, с помощью функции ПРОИЗВЕД; далее умножьте на цену по прайс-листу, и наконец округлите результат до двух знаков после запятой (рис. 7.24). Поскольку вы использовали массив констант в аргументе функции ПСТР, ввод формулы не требует Ctrl+Shift+Enter.

Использование массива констант в функции ВПР для экономии места. На рис. 25 показано типичное применение функции ВПР (первая колонка в таблице F2:G5 отсортирована, поэтому ВПР ищет лишь приблизительное совпадение). Если вы предполагаете, что данные в таблице подстановки (F2:G5) меняться не будут и вам неудобно, чтобы таблица занимала место на листе, вы можете ввести код таблицы жестко в формулу. Проблема с жестким кодированием массива констант в формуле ВПР заключается в относительно большом объеме ввода (массив нужно поместить в фигурные скобки, текст – в кавычки, а также ввести «тучу» разделителей). Можно использовать небольшую хитрость: если таблица у вас уже есть, создайте обычную ВПР, а потом выделите аргумент таблица и нажмите F9. Вы преобразуете ссылку на диапазон в массив констант (рис. 7.26). Вы можете затем скопировать формулу ВПР вниз по колонке и удалить таблицу подстановки.

Рис. 7.25. Если данные в таблице подстановки могут меняться, обычная ВПР со ссылками на ячейки – отличный выбор

Рис. 7.26. Выделите аргумент таблица и нажмите F9; вы преобразуете ссылку на диапазон в массив констант

Еще одна возможность сэкономить пространство на листе – присвоить массиву констант имя, а затем использовать это имя в формулах. Чтобы поименовать массив констант, перейти на вкладку Формулы и щелкните Диспетчер имен (или нажмите Ctrl+F3), далее нажмите кнопку Создать . В открывшемся диалоговом окне Создание имени задайте имя, область применения имени и диапазон (в нашем случае, это не ссылка на ячейки, а массив констант). Можете воспользоваться хитростью, описанной выше, чтобы не набирать массив констант с клавиатуры (рис. 7.27).

Вы можете использовать это имя в качестве аргумента функции ВПР (рис. 7.28). Обратите внимание, что =ВПР(A2;ТоварЦена;2) не является формулой массива.

Рис. 7.28. Имя ТоварЦена содержит массив констант

Вы только что использовали два элемента (массив констант и определенное имя) в качестве аргумента Таблица функции ВПР. А как насчет других аргументов ВПР? Могут ли они содержать массив констант и/или операции с массивами? Как вы видели в , аргумент искомое_значение не может обрабатывать массивы. А аргумент номер_столбца ? Может ли он обрабатывать массив?

На рис. 7.29 (диапазон А1:Н4) показана таблица подстановки с названиями товаров в первом столбце (отсортированы по алфавиту) и компонентами затрат в столбцах 2–8. Цель формулы – сложить затраты по столбцам 2, 4, 5, 7 и 8. Вы можете использовать массив констант в качестве аргумента номер_столбца функции ВПР, чтобы получить на выходе функции сразу пять значений затрат. Вы можете увидеть это, если выделите функцию ВПР и нажмете F9 (рис. 7.30).

Рис. 7.29. Поместите в аргумент номер_столбца функции ВПР массив констант номеров столбцов

Рис. 7.30. Так как аргумент номер_столбца содержит пять элементов, функция ВПР вернет пять значений

Если вы введете формулу =СУММ(ВПР(A7;A2:H4;{2;4;5;7;8})) простым нажатием Enter, то получите неверный ответ (рис. 7.31). Видно, что функция СУММ обработала только первый элемент массива {1,35;2,15;3;2;4}, возвращенный функцией ВПР. Чтобы получить корректный результат введите формулу в ячейку используя Ctrl+Shift+Enter (рис. 7.32). Если вам хочется сэкономить на нажатии трех клавиш, поместите ВПР внутрь СУММПРОИЗВ (рис. 7.33).

Рис. 7.32. Независимо от того, содержит ли аргумент номер_столбца функции ВПР массив констант или ссылку на диапазон, ввод формулы требует нажатия Ctrl+Shift+Enter

Итак, получается, что некоторые аргументы функции, которые могут содержать массив констант, не требуют использования Ctrl+Shift+Enter (например, массив в НАИМЕНЬШИЙ и НАИБОЛЬШИЙ, текст в ДЛСТР, логическое_значение в ИЛИ), а некоторые – требуют (например, номер_столбца в ВПР).

До сих пор в этой главе вы использовали массивы констант в качестве аргументов функций. Массивы констант также могут использоваться в математических операциях и операциях сравнения массивов. При этом, если используется только массив констант, формула не требует нажатия Ctrl+Shift+Enter (рис. 7.34–7.36). Если также используется и массив в виде ссылок на диапазон, ввод формулы требует нажатия Ctrl+Shift+Enter (рис. 7.37).

Рис. 7.34. Формула массива (в ячейке С2) спрашивает: «равно ли содержимое ячейки В2 одному из значений: V.P., President или Admin?»; операция сравнения включает в себя операцию с массивом констант в аргументе логическое_значение1 функции ИЛИ и не требует нажатия Ctrl+Shift+Enter

Рис. 7.35. Формула массива рассчитывает ценовой коэффициент на основании значений отдельных скидок; выполнена математическая операция умножения с использованием массива констант в аргументе число1 , что не требует нажатия Ctrl+Shift+Enter

Рис. 7.36. Формула умножает два массива констант и затем суммирует результаты произведения (я не думаю, что вы будет использовать такую формулу, но я включил ее, чтобы проиллюстрировать работу массива констант); математическая операция с массивами констант помещена в аргумент число1 функции СУММ и не требует нажатия Ctrl+Shift+Enter

Рис. 7.37. Формула перемножает массивы ссылок и констант и затем суммирует результаты произведения (лучше использовать функцию СУММПРОИЗВ; я включил эту формулу, чтобы проиллюстрировать работу массива констант); математическая операция включает в себя не только массив констант, но и ссылку на диапазон ячеек, и она требует нажатия Ctrl+Shift+Enter

Резюме главы

  • Массивы константы можно использовать, как в обычных, так и в формулах массива
  • Синтаксис массивов констант: фигурные скобки в начале и в конце массива; точка с запятой разделяет элементы в строке, двоеточие – одну строку от другой; текстовые элементы помещаются в двойные кавычки, а числа, логические значения и значения ошибок – без кавычек
  • Используются три типа массивов констант: вертикальные, горизонтальные, прямоугольные
  • Массив констант не может содержать более 8192 символов
  • Для ввода формулы массива:
    • Если используется только массив констант, Ctrl+Shift+Enter не требуется
    • Некоторые аргументы функций при размещении в них массива констант не требуют Ctrl+Shift+Enter (например, массив в НАИМЕНЬШИЙ и НАИБОЛЬШИЙ, текст в ДЛСТР, логическое_значение в ИЛИ, начальная_позиция в ПСТР, число1 в ПРОИЗВЕД и СУММ)
    • Некоторые аргументы функций требуют нажатия Ctrl+Shift+Enter, если они содержат массив констант (например, аргумент номер_столбца в ВПР)
  • Золотое правило Excel в отношении формул массива можно дополнить следующим:
    • Если данные не будут меняться и вы экономите место на листе, используйте массив констант
    • Поименуйте массив констант и используйте его имя в формулах