Создание константы массива на основе существующих значений

1. На листе Массивы выделите ячейки C1—E3.
2. Нажмите клавишу F2, чтобы переключиться в режим правки.
3. Нажмите клавишу F9, чтобы преобразовать ссылки на ячейки в значения. Значения будут преобразованы в константы массива.
4. Нажмите клавиши CTRL+SHIFT+ВВОД, чтобы ввести константу массива в качестве формулы массива.

Формула массива =Данные!E1:G3 будет заменена следующей константой массива:

={1,2,3;5,6,7;9,10,11}

Связь между листами «Данные» и «Массивы» будет разорвана, а формула массива будет заменена константой массива.

Подсчет знаков в диапазоне ячеек

В примере ниже демонстрируется, как подсчитать число знаков, включая пробелы, в диапазоне ячеек.

• На листе Данные введите указанную ниже формулу в ячейке C7, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=СУММ(ДЛСТР(C1:C5))

В ячейке C7 появится значение 47.

В данном случае функция ДЛСТР возвращает длину каждой текстовой строки в каждой из ячеек диапазона. Затем функция СУММА складывает эти значения и выводит полученный результат в ячейке C7, которая содержит формулу.

Поиск n наименьших значений в диапазоне

В данном примере демонстрируется, как найти три наименьших значения в диапазоне ячеек.

1. На листе Данные выделите ячейки A12—A14.

Этот набор ячеек будет содержать результаты, возвращенные формулой массива.

1. В строке формул введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=НАИМЕНЬШИЙ(A1:A10,{1;2;3})

В ячейках A12—A14 появятся значения 400, 475 и 500 соответственно.

В этой формуле используется константа массива, при помощи которой три раза выполняется функция НАИМЕНЬШИЙ, в результате чего возвращаются три элемента массива, находящегося в ячейках A1:A10, которые имеют наименьшие значения (1, 2 и 3 по возрастанию). Чтобы найти дополнительные значения, следует добавить аргументы в константу и соответствующее число ячеек результата к диапазону A12:A14. С этой формулой также используются дополнительные функции, например СУММА или СРЗНАЧ. Например:

=СУММ(НАИМЕНЬШИЙ(A1:A10,{1;2;3}))

=СРЗНАЧ(НАИМЕНЬШИЙ(A1:A10,{1;2;3}))

Поиск n наибольших значений в диапазоне

Чтобы найти наибольшие значения в диапазоне, следует заменить функцию НАИМЕНЬШИЙ функцией НАИБОЛЬШИЙ. Кроме того, в следующем примере используются функции СТРОКА и ДВССЫЛ.

1. На листе Данные выделите ячейки A12—A14.
2. Нажмите клавишу DELETE, чтобы удалить существующую формулу, не отменяя выделение ячеек.
3. В строке формул введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=НАИБОЛЬШИЙ(A1:A10,СТРОКА(ДВССЫЛ("1:3")))

В ячейках A12—A14 появятся значения 3200, 2700 и 2000 соответственно.

На этом этапе может быть полезно ознакомиться с функциями СТРОКА и ДВССЫЛ. При помощи функции СТРОКА можно создать массив последовательных целых чисел. Например, выделите пустой столбец из 10 ячеек в книге примера, введите указанную ниже формулу массива в ячейках A1:A10, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

=СТРОКА(1:10)

В результате выполнения этой формулы будет создан столбец, содержащий 10 последовательных целочисленных значений. Чтобы понять, где может возникнуть проблема, вставьте строку над диапазоном, содержащим формулу массива (то есть над строкой 1). Ссылки на строки будут изменены и в результате выполнения формулы вы получите числа от 2 до 11. Чтобы избежать возникновения этой проблемы, следует добавить в формулу функцию ДВССЫЛ.

=СТРОКА(ДВССЫЛ("1:10"))

В функции ДВССЫЛ в качестве аргументов используются текстовые строки (именно поэтому диапазон 1:10 заключен в двойные кавычки). Текстовые значения не изменяются при вставке строк или перемещении формулы массива. В результате при выполнении функции СТРОКА всегда будет создаваться нужный массив целочисленных значений.

Разберем формулу, использованную ранее, функции ДВССЫЛ в качестве аргументов, можно использовать — =НАИБОЛЬШИЙ(A1:A10,СТРОКА(ДВССЫЛ("1:3"))) — начиная с внутренних скобок и переходя к внешним. Функция ДВССЫЛ возвращает набор текстовых значений, в данном случае значений с 1 по 3 включительно. В свою очередь, функция СТРОКА создает вертикальный массив из трех ячеек. Функция НАИБОЛЬШИЙ трижды выполняет оценку значений, находящихся в диапазоне ячеек A1:A10, по одному разу для каждой из ссылок, возвращенных функцией СТРОКА. В вертикальный массив из трех ячеек возвращаются значения 3200, 2700 и 2000. Если нужно найти дополнительные значения, расширьте диапазон ячеек в функции ДВССЫЛ.

Эту формулу можно использовать и с другими функциями, такими как СУММ и СРЗНАЧ.

Поиск наиболее длинной строки текста в диапазоне ячеек

В этом примере демонстрируется поиск наиболее длинной строки текста в диапазоне ячеек. Данная формула работает только в том случае, если диапазон данных содержит один столбец ячеек.

• На рабочем листе Данные очистите существующую формулу в ячейке C7, введите в этой ячейке указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=ИНДЕКС(C1:C5,ПОИСКПОЗ(МАКС(ДЛСТР(C1:C5)),ДЛСТР(C1:C5),0),1)

В ячейку C7 будет подставлено значение перепрыгнул.

Рассмотрим эту формулу, начиная с элементов в скобках. Функция ДЛСТР возвращает длину каждого из элементов в диапазоне ячеек C1:C5. Функция МАКС вычисляет наибольшее значение среди этих элементов, соответствующее строке текста максимальной длины, которая находится в ячейке C3.

Следующие функции немного сложнее. Функция ПОИСКПОЗ вычисляет смещение (относительную позицию) ячейки, которая содержит строку текста максимальной длины. Для этого требуется три аргумента: искомое значение, массив для поиска и тип соответствия. Функция ПОИСКПОЗ выполняет поиск указанного искомого значения в массиве поиска. В данном случае искомое значение представляет собой строку максимальной длины:

(МАКС(ДЛСТР(C1:C5))

из следующего массива:

ДЛСТР(C1:C5)

Аргумент типа соответствия равен 0. Тип соответствия может иметь значение 1, 0 или -1. Если для аргумента задано значение 1, функция ПОИСКПОЗ возвращает наибольшее значение, которое не превышает искомое значение. Если для аргумента задано значение 0, функция ПОИСКПОЗ возвращает первое значение, равное искомому. Если для аргумента задано значение -1, функция ПОИСКПОЗ ищет наименьшее из значений, которые превышают искомое значение или равны ему. Если тип соответствия не указан, предполагается, что он равен 1.

Наконец, функция ИНДЕКС имеет следующие документы: массив, номер строки и столбца в этом массиве. Массив образуется диапазоном ячеек C1:C5, функция ПОИСКПОЗ задает адрес ячейки, а последний аргумент (1) указывает, что значение должно быть найдено в первом столбце массива.

Использование более сложных формул массива

В данном разделе приводятся примеры использования более сложных формул массива.

Суммирование диапазона, который содержит значения ошибки

При попытке просуммировать диапазон, содержащий значения ошибки (например, #N/A) функция СУММА в Excel не работает. В следующем ниже примере демонстрируется, как просуммировать значения в диапазоне с именем «Данные», который содержит ошибки.

=СУММ(ЕСЛИ(ЕОШИБКА(Данные),"",Данные))

В результате выполнения этой формулы создается новый массив, содержащий исходные значения за вычетом значений ошибки. Разберем эту функцию, начиная с внутренних скобок и переходя к внешним. Функция ОШИБКА выполняет поиск ошибок в указанном диапазоне ячеек (Данные). Функция ЕСЛИ возвращает конкретное значение, если при проверке заданного условия получено значение ИСТИНА, и другое значение, если получено значение ЛОЖЬ. В данном случае для всех значений ошибки возвращаются пустые строки (""), поскольку условие выполнено (значение ИСТИНА), а для остальных ячеек указанного диапазона (Данные) возвращаются содержащиеся в них значения, так как условие не выполнено (значение ЛОЖЬ), то есть они не содержат значения ошибки. При помощи функции СУММА вычисляется итоговое значение по отфильтрованному массиву.

Подсчет количества значений ошибки в диапазоне

Формула в этом примере похожа на формулу предыдущего примера, однако она возвращает количество значений ошибки в диапазоне с именем «Данные» вместо исключения таких значений.

=СУММ(ЕСЛИ(ЕОШИБКА(Данные),1,0))

В результате выполнения этой формулы создается массив, в котором для ячеек со значениями ошибки указывается значение 1, а для других ячеек \endash значение 0. Для получения аналогичного результата указанную формулу можно упростить, удалив третий аргумент функции ЕСЛИ следующим образом:

=СУММ(ЕСЛИ(ЕОШИБКА(Данные),1))

Если этот аргумент не задан и ячейка не содержит значения ошибки, функция ЕСЛИ возвращает значение ЛОЖЬ. Формулу можно упростить еще больше:

=СУММ(ЕСЛИ(ЕОШИБКА(Данные)*1))

Такой вариант формулы допустим, поскольку ИСТИНА*1=1 и ЛОЖЬ*1=0.

Суммирование значений с учетом условий

Может возникнуть необходимость вложить значения при выполнении определенных условий. Например, в следующей формуле массива суммируются только положительные целые числа в диапазоне с именем «Продажи»:

=СУММ(ЕСЛИ(Продажи>0,Продажи))

В результате выполнения функции ЕСЛИ создается массив положительных значений и ложных значений. Функция СУММА будет игнорировать ложные значения, поскольку 0+0=0. Используемый в этой формуле диапазон ячеек может включать любое количество строк и столбцов.

Можно также выполнить суммирование значений с учетом нескольких условий. Например, в следующей формуле массива вычисления выполняются по значениям, которые больше 0 и не меньше 5:

=СУММ((Продажи>0)*(Продажи<=5)*(Продажи))

Следует помнить, что при наличии в указанном диапазоне одной или нескольких ячеек, не являющихся числовыми, эта формула возвратит ошибку.

Доступно создание формул массива с использованием условия типа ИЛИ. Например, можно выполнить сложение значений, которые меньше 5 и больше 15:

=СУММ(ЕСЛИ((Продажи<5)+(Продажи>15),Продажи))

Функция ЕСЛИ возвращает все значения меньше 5 и больше 15, которые затем передаются в функцию СУММ.

Важно. В формулах массива нельзя использовать непосредственно функции И или ИЛИ, поскольку эти функции возвращают отдельный результат, ИСТИНА или ЛОЖЬ, а для функций массива требуется массив результатов. Чтобы разрешить эту проблему, воспользуйтесь логикой, показанной в предыдущей формуле. Другими словами, математические операции, такие как сложение и умножение, выполняются в отношении значений, которые отвечают условию ИЛИ или И.

Подсчет среднего значения с исключением нулей

В этом примере демонстрируется, как удалить нулевые значения из диапазона, чтобы затем вычислить для него среднее значение. В формуле используется диапазон данных с именем «Продажи».

=СРЗНАЧ(ЕСЛИ(Продажи<>0,Продажи))

В результате выполнения функции ЕСЛИ создается массив значений, не равных 0, которые затем передаются в функцию СРЗНАЧ.

Подсчет количества различающихся ячеек в двух диапазонах

В данной формуле массива сравниваются значения в двух диапазонах ячеек с именами МоиДанные и ДругиеДанные, в результате чего возвращается количество различающихся ячеек между этими двумя диапазонами. Если содержимое двух диапазонов идентично, формула возвращает 0. Для использования данной формулы диапазоны ячеек должны иметь один и тот же размер и одинаковую размерность.

=СУММ(ЕСЛИ(МоиДанные=ДругиеДанные,0,1))

В результате выполнения формулы создается массив, имеющий такой же размер, что и сравниваемые диапазоны. Функция ЕСЛИ заполняет массив значениями 0 и 1 (0 для ячеек с различными значениями, 1 \endash для ячеек с одинаковыми значениями). Затем функция СУММА возвращает сумму значений в массиве.

Эту формулу можно упростить следующим образом:

=СУММ(1*(МоиДанные<>ДругиеДанные))

Подобно формуле, подсчитывающей количество значений ошибки в диапазоне, эта формула работает благодаря тому, что ИСТИНА*1=1, а ЛОЖЬ*1=0.

Поиск позиции максимального значения в диапазоне

Следующая формула массива возвращает номер строки максимального значения в диапазоне с именем «Данные», состоящем из одного столбца:

=МИН(ЕСЛИ(Данные=МАКС(Данные),СТРОКА(Данные),""))

В результате выполнения функции ЕСЛИ создается новый массив, соответствующий диапазону с именем "Данные". Если соответствующая ячейка содержит максимальное значение в диапазоне, массив будет содержать номер этой строки. В обратном случае массив содержит пустую строку (""). Функция МИН использует полученный массив в качестве своего второго аргумента и возвращает наименьшее значение, соответствующее номеру строки с максимальным значением в диапазоне с именем "Данные". Если диапазон "Данные" содержит одинаковые максимальные значения, формула возвращает строку первого значения.

Если требуется, чтобы формула возвращала фактический адрес ячейки с максимальным значением, воспользуйтесь следующей формулой:

=АДРЕС(МИН(ЕСЛИ(Данные=МАКС(Данные),СТРОКА(Данные),"")),СТОЛБЕЦ(Данные))

Правила для формул массива

1. Перед вводом формулы массива надо выделить ячейку или диапазон ячеек, который будет содержать результаты. Если формула возвращает несколько значений, необходимо выделить диапазон такого же размера и формы, как диапазон с исходными данными.
2. Нажмите клавиши Ctrl+Shift+Enter для фиксации ввода формулы массива. При этом Excel заключит формулу в фигурные скобки в строке формул. НЕ ВВОДИТЕ ФИГУРНЫЕ СКОБКИ ВРУЧНУЮ!
3. В диапазоне нельзя изменять, очищать или перемещать отдельные ячейки, а также вставлять или удалять ячейки. Все ячейки в диапазоне массива надо рассматривать как единое целое и редактировать все их сразу.
4. Для изменения или очистки массива надо выделить весь массив и активизировать строку формул. После изменения формулы надо нажать комбинацию клавиш Ctrl+Shift+Enter.
5. Чтобы переместить содержимое диапазона массива, надо выделить весь массив и в меню "Правка" выбрать команду "Вырезать". Затем выделите новый диапазон и в меню "Правка" выберите команду "Вставить".
6. Вырезать, очищать или редактировать часть массива не разрешается, но можно назначать разные форматы отдельным ячейкам в массиве.

Не нашли, что искали? Воспользуйтесь поиском: