Использование формул массива: рекомендации и примеры

Использование формул массива: рекомендации и примеры

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

Начиная с обновления за Сентябрь 2018 для Office 365любая формула, которая может возвращать несколько результатов, будет автоматически сбрасывать их вниз или в соседние ячейки. Это изменение в поведении также сопровождается несколькими новыми функциями динамических массивов. Формулы динамических массивов независимо от того, используют ли они существующие функции или функции динамических массивов, должны быть введены только в одну ячейку, а затем нажать клавишу Ввод. Ранее формулы массива прежних версий должны сначала выбрать весь диапазон вывода, а затем подтвердить формулу нажатием клавиш CTRL + SHIFT + ВВОД. Они обычно называются формулами CSE .

Формулы массива можно использовать для выполнения сложных задач, таких как:

  • Быстро создавайте образцы наборов данных.

  • Подсчитайте количество символов, содержащихся в диапазоне ячеек.

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

  • Суммирование всех n-х значений в диапазоне значений.

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

Скачивание примеров

Загрузите пример книги со всеми примерами формул массива в этой статье.

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

  • Формула массива с несколькими ячейками

    Функция массива с несколькими ячейками в ячейке H10 = F10: F19 * G10: G19 для вычисления количества автомобилей, проданных за единицу

  • Здесь мы определяем общие продажи типа купе и "седан" для каждого продавца, вводя = F10: F19 * G10: G19 в ячейке H10.

    После нажатия клавиши Вводрезультаты будут перенесены в ячейки H10: H19. Обратите внимание на то, что при выборе любой ячейки в диапазоне для сброса выделенный диапазон удаляется с границей. Кроме того, вы можете заметить, что формулы в ячейках H10: H19 неактивны. Они просто находятся в справочных материалах, поэтому если вы хотите изменить формулу, вам потребуется выбрать ячейку H10, где находится главная формула.

  • Формула массива с одной ячейкой

    Формула массива с одной ячейкой для вычисления общего итога с функцией = сумм (F10: F19 * G10: G19)

    В ячейке H20 из примера книги введите (или скопируйте и вставьте = Sum (F10: F19 * G10: G19), а затем нажмите клавишу Ввод.

    В этом случае Excel умножает значения в массиве (диапазон ячеек с F10 по G19), а затем использует функцию сумм для сложения итоговых значений. Общий итог продаж составит 1 590 000 рублей.

    В данном примере демонстрируется, какие широкие возможности предоставляет данный тип формул. Например, предположим, что у вас есть 1000 строк с данными. Вы можете частично или полностью сложить их значения, создав формулу массива в отдельной ячейке, а не перетаскивать формулу на 1000 строк ниже. Кроме того, обратите внимание на то, что формула одной ячейки в ячейке H20 полностью независима от формулы с несколькими ячейками (формула в ячейках H10 с помощью H19). Это указывает на еще одно преимущество использования формул массива — их гибкость. Вы можете изменить другие формулы в столбце H, не влияя на формулу в H20. Кроме того, можно использовать независимые итоги, так как это помогает проверить точность результатов.

  • Формулы динамических массивов также предлагают следующие преимущества:

    • Согласованность   . Если щелкнуть одну из ячеек в H10 вниз, появится та же формула. Такая согласованность помогает обеспечить более высокую точность результатов.

    • Безопасность    Нельзя переписать компонент формулы массива с несколькими ячейками. Например, щелкните ячейку H11 и нажмите клавишу DELETE. Приложение Excel не изменит вывод массива. Чтобы изменить его, необходимо выделить ячейку, расположенную в верхней левой части массива, или ячейку H10.

    • Меньший размер файла    Часто можно использовать единственную формулу массива вместо нескольких промежуточных формул. Например, в примере продажи за автомобиль используется одна формула массива для вычисления результатов в столбце E. Если вы использовали стандартные формулы, например = F10 * G10, F11 * G11, F12 * G12 и т. д., вы использовали 11 разных формул для вычисления одного и того же результата. Это не слишком большая проблема, но что делать, если вы хотите суммировать тысячи строк? Затем это может значительно увеличиться.

    • Эффективность   . Функции массивов могут быть эффективным способом для создания сложных формул. Формула массива = сумм (F10: F19 * G10: G19) такая же, как в данном случае: = сумм (F10 * G10, F11 * G11, F12 * G12, F13, G13, F14 * G14, *, *, F15 *, * G15).

    • Выходят    Формулы динамических массивов будут автоматически перенесены в выходной диапазон. Если исходные данные находятся в таблице Excel, то при добавлении или удалении данных формулы динамических массивов будут автоматически изменены.

    • #SPILL! ошибки    В динамических массивах появились ошибки #SPILL!, указывающий на то, что предполагаемый диапазон для переноса заблокирован по какой – либо причине. При разрешении блокировки формула будет автоматически перенесена.

Константы массива являются компонентами формул массива. Константы массива создаются путем ввода списка элементов с последующей вставкой фигурных скобок ({ }) вокруг списка, следующим образом:

= {1; 2; 3; 4; 5} или = {"Январь", "Февраль", "Март"}

Если элементы списка разделяются точками с запятой, будет создан горизонтальный массив (строка). Если элементы разделяются двоеточиями, будет создан вертикальный массив (столбец). Для создания двумерного массива следует разделять элементы в каждой строке запятыми и разделять каждую строку точкой с запятой.

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

  • Создание горизонтальной константы

    Воспользуйтесь книгой из предыдущих примеров или создайте новую книгу. Выделите пустую ячейку и введите = Sequence (1; 5). Функция SEQUENCE создает 1 строку на 5 массив столбцов так же, как = {1, 2, 3, 4, 5}. На экране появится следующий результат:

    Создание горизонтальной константы массива с = SEQUENCE (1; 5) или = {1, 2, 3, 4, 5}

  • Создание вертикальной константы

    Выделите пустую ячейку с комнатой, а затем введите = Sequence (5)или = {1; 2; 3; 4; 5}. На экране появится следующий результат:

    Создание вертикальной константы массива с помощью = SEQUENCE (5) или = {1; 2; 3; 4; 5}

  • Создание двумерной константы

    Выделите пустую ячейку, расположенную справа и под ней, и введите = Sequence (3, 4). Вы получите следующий результат:

    Создание 3 строки по 4 константе массива столбцов с помощью = SEQUENCE (3, 4)

    Вы также можете ввести: или = {1; 2; 3; 4; 5; 6; 7; 8; 9, 10, 11, 12}, но при этом вы захотите обратить внимание на то, где вы вводите точки с запятой и запятой.

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

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

В ячейке D9 введено = Sequence (1, 5, 3, 1), но вы также можете ввести 3, 4, 5, 6 и 7 в ячейки A9: H9. Нет ничего особенного для выбора определенного числа, мы просто выбрали для отличия нечто, отличное от 1-5.

В ячейке E11 введите = сумм (D9: H9 * Sequence (1; 5))или = сумм (D9: H9 * {1; 2; 3; 4; 5}). Формулы возвращают 85.

Использование констант массива в формулах. В этом примере мы использовали функцию = сумм (D9: H (* SEQUENCE (1; 5)).

Функция SEQUENCE создает эквивалент константы массива {1, 2, 3, 4, 5}. Поскольку Excel выполняет операции с выражениями, заключенными в круглые скобки, следующие два элемента, которые используются для воспроизведения, — это значения ячеек в D9: H9 и оператор умножения (*). На этом этапе формула выполнит умножение значений в сохраненном массиве на соответствующие значения в константе. Это эквивалент следующей формулы:

= Сумм (D9 * 1; E9 * 2; F9 * 3; G9 * 4; H9 * 5)или = сумм (3 * 1, 4 * 2, 5 * 3, 6 * 4 *).

Наконец, функция СУММ складывает значения и возвращает 85.

Чтобы не использовать сохраненный массив и полностью сохранить операцию в памяти, вы можете заменить ее другой константой массива:

= Сумм (последовательность (1; 5; 3; 1) * Sequence (1; 5))или = Sum ({3; 4; 5; 6; 7} * {1; 2; 3; 4})

Элементы, которые можно использовать в константах массива

  • Константы массива могут содержать числа, текст, логические значения (такие как истина и ложь), а также значения ошибок, например #N/А. Числа можно использовать в целых, десятичных и научных форматах. Если вы хотите добавить текст, нужно заключить его в кавычки ("текст").

  • Константы массива не могут содержать дополнительные массивы, формулы или функции. Другими словами, они могут содержать только текст или числа, разделенные запятыми или точками с запятой. При вводе такой формулы, как {1;2;A1:D4} или {1;2;СУММ(Q2:Z8)}, выводится предупреждение. Кроме того, числовые значения не могут содержать знаки процента, знаки валюты, запятые или кавычки.

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

Перейдите в раздел формулы _Гт_ определенные имена _гт_ define Name. В поле имя введите Квартал1. В поле Диапазон введите следующую константу (не забудьте ввести скобки вручную):

={"Январь";"Февраль";"Март"}

Диалоговое окно должно выглядеть следующим образом:

Добавление именованной константы массива из формул _Гт_ определенные имена _Гт_ _Гт_ New

Нажмите кнопку ОК, а затем выберите любую строку с тремя пустыми ячейками и введите = Квартал1.

На экране появится следующий результат:

Используйте именованную константу массива в формуле, например = Квартал1, где Квартал1 определен как = {"Январь", "Февраль", "Март"}

Если вы хотите, чтобы результаты выводятся по вертикали, а не по горизонтали, можно использовать =транспонировать(Квартал1).

Если вы хотите отобразить список из 12 месяцев, например, при создании финансового отчета, вы можете использовать функцию SEQUENCE в качестве основы для одного из текущих года. Это может быть похоже на то, что при отображении только месяца отображается допустимая дата, которую можно использовать в других вычислениях. Эти примеры содержатся в разделе именованная константа массива , а также на листах наборов данных экспресс-образцов в примере книги.

= ТЕКСТ (Дата (год (сегодня ()); последовательность (1; 12); 1); "МММ")

Использование сочетаний клавиш "текст", "Дата", "год", "сегодня" и "последовательность" для создания динамического списка из 12 месяцев

При использовании функции Дата для создания даты на основе текущего года, последовательность создает константу массива от 1 до 12 для января – ого, после чего функция текст преобразует формат отображения в "МММ" (Янв, фев, Мар и т. д.). Если вы хотите отобразить полное название месяца (например, Январь), нужно использовать "MMMM".

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

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

  • Несколько элементов массива

    Введите = Sequence (1; 12) * 2; 2; 3; 4; 5; 6; 7; 8; 9 ;

    Вы также можете разделить с помощью (/), добавить с помощью (+) и вычитание с помощью (-).

  • Возведение в квадрат элементов массива

    Ввод = последовательность (1; 12) ^ 2; 2; 3; 4; 5; 6; 7; 8; 9 ;

  • Поиск квадратного корня квадратных элементов в массиве

    Ввод =sqrt(последовательность (1; 12) ^ 2)или = Sqrt ({1; 2; 3; 4; 5; 6; 7; 8; 9; 11

  • Транспонирование одномерной строки

    Ввод = транспонировать (последовательность (1; 5))или = транспонировать ({1; 2; 3; 4; 5})

    Даже если была введена горизонтальная константа массива, функция ТРАНСП преобразует константу массива в столбец.

  • Транспонирование одномерного столбца

    Ввод = транспонировать (последовательность (5; 1))или = транспонировать ({1; 2; 3; 4; 5})

    Даже если была введена вертикальная константа массива, функция ТРАНСП преобразует константу массива в строку.

  • Транспонирование двумерного массива

    Ввод = транспонировать (последовательность (3; 4))или = транспонировать ({1; 2; 3; 4; 5; 6; 7; 8; 9

    Функция ТРАНСП преобразует каждую из строк в последовательность столбцов.

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

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

    В следующем примере показано, как использовать формулы массива для создания нового массива на основе существующего массива.

    Введите = Sequence (3, 6, 10, 10)или = {10, 20, 30, 40, 50, 60; 70, 80; 90100110120; 130140150160170180}

    Не забудьте ввести {(открывающая фигурная скобка) перед вводом 10 и} (закрывающая фигурная скобка) после ввода 180, так как вы создаете массив чисел.

    Затем введите = D9 #или = D9: I11 в пустую ячейку. Появится массив ячеек 3 x 6 с одинаковыми значениями, которые отображаются в D9: D11. Знак # называется оператором " диапазон", а также ексцел'с способ ссылки на весь диапазон массива, а не на его ввод.

    Использование перенесенного оператора Range (#) для ссылки на существующий массив

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

    Вы можете получить результаты формулы массива, а затем преобразовать ее в ее компоненты. Выберите ячейку D9, а затем нажмите клавишу F2 , чтобы перейти в режим редактирования. Затем нажмите клавишу F9 , чтобы преобразовать ссылки на ячейки в значения, которые Excel преобразует в константу массива. Когда вы нажмете клавишу Ввод, формула = D9 # должна иметь значение = {10, 20, 30; 40, 50, 60; 70; 80; 90}.

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

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

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

    = SUM (LEN (C9: C13))

    В этом случае функция ДЛСТР возвращает длину каждой текстовой строки в каждой ячейке в диапазоне. Функция сумм добавляет эти значения вместе и отображает результат (66). Если вы хотите получить среднее количество символов, вы можете использовать следующее:

    = AVERAGE (LEN (C9: C13))

  • Содержимое самой длинной ячейки в диапазоне C9: C13

    = ИНДЕКС (C9: C13; MATCH (MAX (LEN (C9: C13)); LEN (C9: C13)

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

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

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

    MAX (LEN (C9: C13)

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

    ДЛСТР (C9: C13)

    Аргумент типа Match в данном случае равен 0. Тип соответствия может быть равен 1, 0 или-1.

    • 1 — возвращает наибольшее значение, которое меньше или равно значению подстановки.

    • 0 — возвращает первое значение, точно равное значению подстановки.

    • -1 — возвращает наименьшее значение, которое больше или равно указанному значению подстановки.

    • Если тип соответствия не указан, предполагается, что он равен 1.

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

    Если вы хотите получить содержимое наименьшей текстовой строки, в приведенном выше примере следует заменить максимальное значение на min.

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

    В этом примере показано, как найти три наименьших значения в диапазоне ячеек, где массив примеров данных в ячейках B9: B18has создан с помощью: = int (RANDARRAY(10; 1); Обратите внимание, что RANDARRAY является временной функцией, поэтому вы получаете новый набор случайных чисел каждый раз при расчете в Excel.

    Формула массива Excel для поиска n-наименьшего значения: = Малый (B9 #, SEQUENCE (D9))

    Введите = мелкий (B9 #, Sequence (D9); = малый (B9: B18; {1; 2; 3})

    Эта формула использует константу массива для того, чтобы вычислить минимальную функцию в три значения, а затем вернуть наименьшие 3 члена массива, содержащегося в ячейках B9: B18, где 3 — это значение переменной в ячейке D9. Чтобы найти другие значения, можно увеличить значение функции SEQUENCE или добавить другие аргументы для константы. С этой формулой также используются дополнительные функции, например СУММ или СРЗНАЧ. Например:

    = СУММ (МЕЛКИЙ (B9 #, ПОСЛЕДОВАТЕЛЬНОСТЬ (D9))

    = AVERAGE (МЕЛКИЙ (B9 #, ПОСЛЕДОВАТЕЛЬНОСТЬ (D9))

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

    Для поиска наибольших значений в диапазоне можно заменить функцию SMALL на большую. Кроме того, в следующем примере используются функции СТРОКА и ДВССЫЛ.

    Введите = крупный (B9 #, строка (косвенно (1:3)))или = крупный (B9: B18, строка (косвенная запись ("1:3")))

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

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

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

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

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

    = SEQUENCE (10)

    Рассмотрим формулу, которую вы использовали раньше — = LARGE (B9 #, строка (КОСВЕНная обработка ("1:3"))), начиная с внутренних скобок и заканчивая наружной. Функция ДВССЫЛ возвращает набор текстовых значений, в этом случае значения от 1 до 3. Функция строка, в свою очередь, создает массив столбцов с тремя ячейками. Функция LARGE использует значения в диапазоне ячеек B9: B18 и обрабатывается три раза, один раз для каждой ссылки, возвращенной функцией строка. Если вы хотите найти другие значения, вы можете добавить в функцию ДВССЫЛ диапазон большего количества ячеек. Наконец, как и в небольших примерах, вы можете использовать эту формулу с другими функциями, например SUM и AVERAGE.

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

    Функция сумм в Excel не работает при попытке суммирования диапазона, содержащего значение ошибки, например #VALUE! или #N/А. В этом примере показано, как суммировать значения в диапазоне с именами данных, которые содержат ошибки.

    Использование массивов для обработки ошибок. Например, функция = сумм (если (ЕОШИБКА (Data); ""; Data) будет суммировать диапазон данных, даже если они содержат ошибки, например #VALUE! или #NA!.

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

    Формула создает новый массив, содержащий исходные значения минус любые значения ошибок. При работе с внутренними функциями и наружным внешнем функция ЕОШИБКА выполняет поиск ошибок в диапазоне ячеек (данных). Функция если возвращает определенное значение, если указанное условие имеет значение TRUE, и другое значение, если оно имеет значение FALSE. В этом случае возвращается пустая строка ("") для всех значений ошибок, так как они оцениваются как истина и возвращает оставшиеся значения из диапазона (данные), так как они имеют значение ложь, то есть они не содержат значений ошибок. Функция сумм затем вычисляет итог для отфильтрованного массива.

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

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

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

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

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

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

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

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

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

Вы можете использовать массивы для вычисления с учетом определенных условий. Функция = сумм (если (Sales>0; Sales)) будет суммировать все значения, превышающие 0, в диапазоне с именем Sales.

Например, эта формула массива суммирует только положительные целые значения в диапазоне с именем Sales, которое представляет ячейки E9: E24 в приведенном выше примере:

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

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

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

= SUM ((Sales>0) * (Sales<2500) * (Sales))

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

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

= СУММ (если ((Sales>0) + (Sales<2500); продажи))

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

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

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

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

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

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

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

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

= СУММ (1 * (Мидата_лт__гт_йоурдата))

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

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

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

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

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

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

В образце книги можно найти похожие примеры в различиях между листами DataSet.

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

  • Формула массива с несколькими ячейками

Скопируйте всю таблицу ниже и вставьте ее в ячейку a1 на пустом листе.

Sales Пользователь

Автомобиль Type (тип )

Число Продал

Единица измерения Price (Цена )

Общее количество Sales

Зуева

Седан

5

33000

Купе

4

37000

Егоров

Седан

6

24000

Купе

8

21000

Еременко

Седан

3

29000

Купе

1

31000

Климов

Седан

9

24000

Купе

5

37000

Шашков

Седан

6

33000

Купе

8

31000

Формула (общий итог)

Общий итог

'=СУММ(C2:C11*D2:D11)

=СУММ(C2:C11*D2:D11)

  1. Чтобы просмотреть итоги продаж типа купе и "седан" для каждого продавца, выделите ячейки E2: E11, введите формулу = C2: C11 * D2: D11, а затем нажмите клавиши CTRL + SHIFT + ВВОД.

  2. Чтобы просмотреть общие итоги всех продаж, выберите ячейку F11, введите формулу = сумм (C2: C11 * D2: D11), а затем нажмите клавиши CTRL + SHIFT + ВВОД.

При нажатии клавиш CTRL + SHIFT + ВВОДExcel окружает формулу фигурными скобками ({}) и вставляет экземпляр формулы в каждую ячейку выбранного диапазона. Это происходит очень быстро, так что вы видите в столбце E — общая сумма продаж для каждого типа автомобилей для каждого продавца. Если вы выбрали E2, выберите E3, E4 и т. д., вы увидите ту же формулу: {= C2: C11 * D2: D11}

Итоговые суммы в столбце E, вычисленные с помощью формулы массива

  • Создание формулы массива с одной ячейкой

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

=СУММ(C2:C11*D2:D11)

В этом случае Excel умножает значения в массиве (диапазон ячеек C2 – D11), а затем использует функцию суммдля сложения итоговых значений. Общий итог продаж составит 1 590 000 рублей. В данном примере демонстрируется, какие широкие возможности предоставляет данный тип формул. Например, предположим, что у вас есть 1000 строк с данными. Вы можете частично или полностью сложить их значения, создав формулу массива в отдельной ячейке, а не перетаскивать формулу на 1000 строк ниже.

Кроме того, обратите внимание на то, что формула одной ячейки в ячейке D13 полностью независима от формулы с несколькими ячейками (формула в ячейках E2 – E11). Это указывает на еще одно преимущество использования формул массива — их гибкость. Вы можете изменить формулы в столбце E или полностью удалить столбец, не влияя на формулу в D13.

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

  • Согласованность   . Если щелкнуть несколько ячеек ниже ячейки E2, вы увидите, что все они содержат одну и ту же формулу. Такая согласованность помогает обеспечить более высокую точность результатов.

  • Безопасность    Компонент формулы массива с несколькими ячейками нельзя переписать. Например, щелкните ячейку E3 и нажмите клавишу Delete. Вам придется выделить весь диапазон ячеек (E2–E11) и изменить формулу для всех ячеек либо оставить массив без изменений. Чтобы подтвердить изменение формулы, необходимо нажать клавиши CTRL + SHIFT + ВВОД .

  • Меньший размер файла    Часто можно использовать единственную формулу массива вместо нескольких промежуточных формул. Например, в книге используется одна формула массива для вычисления результатов в столбце E. Если вы использовали стандартные формулы (например, = C2 * D2, C3 * D3, C4 * D4...), вы использовали 11 разных формул для вычисления одного и того же результата.

Как правило, в формулах массивов используются стандартные синтаксисы формул. Все они начинаются со знака равенства (=), и вы можете использовать большинство встроенных функций Excel в формулах массива. Ключевое отличие заключается в том, что при использовании формулы массива нажмите клавиши CTRL + SHIFT + ВВОД , чтобы ввести формулу. После этого Excel окружает формулу массива фигурными скобками — при вводе фигурных скобок вручную формула будет преобразована в текстовую строку, и она не станет работать.

Функции массивов могут быть эффективным способом для создания сложных формул. Формула массива =СУММ(C2:C11*D2:D11) эквивалентна формуле =СУММ(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Важно: Нажмите клавиши CTRL + SHIFT + ВВОД каждый раз, когда нужно ввести формулу массива. Это правило действует для формул как с одной, так и с несколькими ячейками.

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

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

  • Содержимое отдельной ячейки в формуле массива изменить нельзя. Чтобы проверить это правило, выделите ячейку E3 в книге и нажмите клавишу DELETE. В приложении Excel отобразится сообщение о том, что нельзя изменить часть формулы массива.

  • Формулу массива можно переместить или удалить только целиком. Другими словами, чтобы уменьшить формулу массива, сначала нужно удалить существующую формулу, а затем создать новую формулу с самого начала.

  • Чтобы удалить формулу массива, выделите весь диапазон формул (например, E2: E11) и нажмите клавишу Delete.

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

Иногда возникает необходимость увеличить диапазон формулы массива. Выделите первую ячейку в диапазоне существующих массивов и продолжайте, пока не выберете весь диапазон, на который вы хотите добавить формулу. Нажмите клавишу F2 , чтобы изменить формулу, а затем нажмите клавиши CTRL + SHIFT + ВВОД , чтобы подтвердить формулу, когда вы настроили диапазон формул. Ключом является выделение всего диапазона, начиная с верхней левой ячейки массива. Верхняя левая ячейка является редактируемой.

Несмотря на все преимущества формул массива, у них имеются свои недостатки.

  • Иногда вы можете забыть нажать клавиши CTRL + SHIFT + ВВОД. Это может случиться даже с самыми опытными пользователями Excel. Не забывайте нажимать это сочетание клавиш каждый раз при вводе или изменении формулы массива.

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

  • Большие формулы массива могут замедлять вычисления на компьютерах с невысокой скоростью процессора или небольшим объемом памяти.

Константы массива являются компонентами формул массива. Константы массива создаются путем ввода списка элементов с последующей вставкой фигурных скобок ({ }) вокруг списка, следующим образом:

={1;2;3;4;5}

Теперь, когда вы создаете формулы массива, вам нужно нажать клавиши CTRL + SHIFT + ВВОД . Константы массива являются составляющей формул массива, поэтому скобки вокруг них необходимо ввести вручную. Затем вы можете использовать сочетание клавиш CTRL + SHIFT + ВВОД , чтобы ввести формулу целиком.

Если элементы списка разделяются точками с запятой, будет создан горизонтальный массив (строка). Если элементы разделяются двоеточиями, будет создан вертикальный массив (столбец). Чтобы создать двумерный массив, следует разделить элементы строк точками с запятой, а строки — двоеточиями.

Вот массив в одной строке: {1, 2, 3, 4}. Массив из одного столбца: {1:2:3:4}. А вот массив из двух строк и четырех столбцов: {1;2;3;4:5;6;7;8}. В двух массивах строк первая строка имеет значение 1, 2, 3 и 4, а вторая строка — 5, 6, 7 и 8. Строки разделены двоеточием между записями 4 и 5.

Как и формулы массива, константы массива можно использовать с большинством встроенных функций Excel. В следующих ниже разделах объясняется, как создавать те или иные типы констант и использовать эти константы с функциями Excel.

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

Создание горизонтальной константы

  1. На пустом листе выделите ячейки с a1 по E1.

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

    ={1;2;3;4;5}

    В этом случае необходимо ввести открывающую и закрывающую фигурные скобки ({}), а Excel добавит второй.

    Будет отображен следующий результат:

    Горизонтальная константа массива в формуле

Создание вертикальной константы

  1. Выделите в книге столбец из пяти ячеек.

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

    ={1:2:3:4:5}

    Будет отображен следующий результат:

    Вертикальная константа массива в формуле массива

Создание двумерной константы

  1. Выделите в книге блок ячеек из четырех столбцов и трех строк.

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

    ={1;2;3;4:5;6;7;8:9;10;11;12}

    Вы получите следующий результат:

    Двумерная константа массива в формуле массива

Использование констант в формулах

Рассмотрим простой пример с константами.

  1. Создайте новый лист в образце книги.

  2. В ячейку A1 введите значение 3, затем введите 4 в ячейку B1, 5 — в C1, 6 — в D1 и 7 — в E1.

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

    =СУММ(A1:E1*{1;2;3;4;5})

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

    Формула массива с константой массива

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

В следующем разделе поясняется работа этой формулы.

Использованная формула состоит из нескольких частей.

Синтаксис формулы массива с константой массива

1. Функция

2. Сохраненный массив

3. Оператор

4. Константа массива

Последний элемент внутри скобок — это константа массива: {1;2;3;4;5}. Помните, что Excel не заключает константы массива в фигурные скобки, их нужно вводить вручную. Также помните, что после добавления константы в формулу массива нажмите клавиши CTRL + SHIFT + ВВОД , чтобы ввести формулу.

Поскольку в первую очередь выполняются операции с элементами, заключенными в скобки, далее будут использоваться значения, хранящиеся в книге (диапазон A1:E1), и соответствующий оператор. На этом этапе формула выполнит умножение значений в сохраненном массиве на соответствующие значения в константе. Это эквивалент следующей формулы:

=СУММ(A1*1;B1*2;C1*3;D1*4;E1*5)

В последнюю очередь выполняется сложение значений при помощи функции СУММ, итоговое значение 85 отображается в ячейке A3.

Чтобы избежать использования сохраненного массива и выполнять операцию исключительно в памяти, замените сохраненный массив другой константой массива:

=СУММ({3;4;5;6;7}*{1;2;3;4;5})

Чтобы попробовать это, скопируйте функцию, выберите пустую ячейку в книге, вставьте формулу в строку формул, а затем нажмите клавиши CTRL + SHIFT + ВВОД. Получится тот же результат, что и в предыдущем упражнении, где использовалась формула массива:

=СУММ(A1:E1*{1;2;3;4;5})

Константы массива могут содержать числа, текст, логические значения (такие как ИСТИНА и ЛОЖЬ), а также значения ошибок (например, #Н/Д). Можно использовать числа в целочисленном, десятичном или экспоненциальном формате. При добавлении текста требуется заключить его в кавычки (").

Константы массива не могут содержать дополнительные массивы, формулы или функции. Другими словами, они могут содержать только текст или числа, разделенные запятыми или точками с запятой. При вводе такой формулы, как {1;2;A1:D4} или {1;2;СУММ(Q2:Z8)}, выводится предупреждение. Кроме того, числовые значения не могут содержать знаки процента, знаки валюты, запятые или кавычки.

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

  1. На вкладке Формулы в группе Определенные имена выберите команду Присвоить имя.
    Откроется диалоговое окно "присвоить имя ".

  2. В поле Имя введите Квартал1.

  3. В поле Диапазон введите следующую константу (не забудьте ввести скобки вручную):

    ={"Январь";"Февраль";"Март"}

    Содержимое этого диалогового окна должно иметь следующий вид:

    Диалоговое окно "Изменение имени" с формулой

  4. Нажмите кнопку ОК, а затем выделите строку из трех пустых ячеек.

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

    =Квартал1

    Будет отображен следующий результат:

    Именованный массив, введенный в виде формулы

При использовании именованной константы в качестве формулы массива не забудьте ввести знак равенства. Если не сделать этого, массив будет интерпретирован как строка текста и формула не будет работать должным образом. Кроме того, помните, что можно сочетать текст и числа.

Если константы массива не действуют, проверьте наличие перечисленных ниже причин.

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

  • Возможно, выделен диапазон ячеек, не соответствующий числу элементов в константе. Например, если выделен столбец из шести ячеек для использования в константе с пятью ячейками, в пустой ячейке будет выведено значение ошибки #Н/Д. Наоборот, если выделено слишком мало ячеек, значения, не имеющие соответствующей ячейки, будут пропущены.

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

Умножение каждого из элементов массива

  1. Создайте новый лист и выделите блок пустых ячеек из четырех столбцов и трех строк.

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

    ={1;2;3;4:5;6;7;8:9;10;11;12}*2

Возведение в квадрат элементов массива

  1. Выделите блок пустых ячеек из четырех столбцов и трех строк.

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

    ={1;2;3;4:5;6;7;8:9;10;11;12}*{1;2;3;4:5;6;7;8:9;10;11;12}

    Можно также ввести следующую формулу массива, где используется оператор возведения в степень (^):

    ={1;2;3;4:5;6;7;8:9;10;11;12}^2

Транспонирование одномерной строки

  1. Выделите столбец из пяти пустых ячеек.

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

    =ТРАНСП({1;2;3;4;5})

    Даже если была введена горизонтальная константа массива, функция ТРАНСП преобразует константу массива в столбец.

Транспонирование одномерного столбца

  1. Выделите строку из пяти пустых ячеек.

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

    =ТРАНСП({1:2:3:4:5})

Даже если была введена вертикальная константа массива, функция ТРАНСП преобразует константу массива в строку.

Транспонирование двумерного массива

  1. Выделите блок ячеек из трех столбцов и четырех строк.

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

    =ТРАНСП({1;2;3;4:5;6;7;8:9;10;11;12})

    Функция ТРАНСП преобразует каждую из строк в последовательность столбцов.

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

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

В следующем примере рассматривается использование формул массива для создания связей между диапазонами ячеек на разных листах. Здесь также объясняется, как создать константу массива на основе такого же набора значений.

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

  1. На листе Excel выберите диапазон ячеек C8:E10, а затем введите такую формулу:

    ={10;20;30:40;50;60:70;80;90}

    Не забудьте ввести { (открывающую фигурную скобку) перед числом 10 и} (закрывающую фигурную скобку) после числа 90, так как вы создаете массив чисел.

  2. Нажмите клавиши CTRL + SHIFT + ВВОД, чтобы ввести этот массив чисел в диапазон ячеек C8: E10 с помощью формулы массива. На листе ячейки с C8 по E10 должны выглядеть следующим образом:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Выделите диапазон ячеек C1–E3.

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

    =C8:E10

    В ячейках C1 – E3 отображаются массивы 3x3 с теми же значениями, которые вы видите в C8 через E10.

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

  1. В ячейках C1: выбран параметр C3, нажмите клавишу F2 , чтобы перейти в режим редактирования. 

  2. Нажмите клавишу F9 , чтобы преобразовать ссылки на ячейки в значения. Значения будут преобразованы в константы массива. Формула должна быть формулой = {10, 20, 30; 40, 50, 60; 70, 80, 90}.

  3. Нажмите клавиши CTRL + SHIFT + ВВОД , чтобы ввести константу массива в качестве формулы массива.

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

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

  1. Скопируйте всю таблицу и вставьте ее на лист, начиная с ячейки A1.

    Данные

    Это

    группа ячеек,

    собранных вместе,

    чтобы составить

    единое предложение.

    Общее количество символов в диапазоне ячеек A2:A6

    =СУММ(ДЛСТР(A2:A6))

    Содержимое ячейки с наибольшим количеством символов (A6)

    =ИНДЕКС(A2:A6;ПОИСКПОЗ(МАКС(ДЛСТР(A2:A6));ДЛСТР(A2:A6);0);1)

  2. Выберите ячейку A8 и нажмите клавиши CTRL + SHIFT + ВВОД , чтобы увидеть общее количество символов в ячейках A2: A6 (66).

  3. Выделите ячейку A10, а затем нажмите клавиши CTRL + SHIFT + ВВОД , чтобы увидеть содержимое наибольшего значения в ячейках A2: A6 (ячейка a3).

Следующая формула используется в ячейке A8 для подсчета общего количества символов (66) в ячейках A2 – A6.

=СУММ(ДЛСТР(A2:A6))

В данном случае функция ДЛСТР возвращает длину каждой текстовой строки в каждой из ячеек диапазона. Функция сумм добавляет эти значения вместе и отображает результат (66).

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

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

  1. Введите случайные числа в ячейках A1: A11.

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

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

    = МАЛЫЙ (A1: A11; {1; 2; 3})

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

= SUM (МАЛЫЙ (A1: A10; {1; 2; 3})

= AVERAGE (МАЛЫЙ (A1: A10; {1; 2; 3})

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

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

  1. Выделите ячейки с D1 по D3.

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

    = КРУПНЫЙ (A1: A10; СТРОКА (КОСВЕННАЯ ЯЧЕЙКА ("1:3")))

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

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

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

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

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

Рассмотрим формулу, которую вы использовали раньше — = крупный (A5: A14, Row ("1:3"))) , начиная с внутренних скобок и заканчивая наружой. функция ДВССЫЛ возвращает набор текстовых значений, в данном случае — значения от 1 до 3. Функция строка , в свою очередь, создает массив столбцов с тремя ячейками. Функция Large использует значения в диапазоне ячеек A5: A14 и обрабатывается три раза, по одному разу для каждой ссылки, возвращенной функцией строка . Значения 3200, 2700 и 2000 возвращаются в массив столбцов с тремя ячейками. Если вы хотите найти другие значения, вы можете добавить в функцию ДВССЫЛ диапазон большего количества ячеек.

Как и в предыдущих примерах, вы можете использовать эту формулу с другими функциями, например Sum и Average.

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

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

=ИНДЕКС(A2:A6;ПОИСКПОЗ(МАКС(ДЛСТР(A2:A6));ДЛСТР(A2:A6);0);1)

Текст "ряд ячеек, которые нужно".

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

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

(МАКС (LEN (A2: A6))

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

ДЛСТР (A2: A6)

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

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

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

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

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

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

Формула создает новый массив, содержащий исходные значения минус любые значения ошибок. При работе с внутренними функциями и наружным внешнем функция ЕОШИБКА выполняет поиск ошибок в диапазоне ячеек (данных). Функция Если возвращает определенное значение, если указанное условие имеет значение true, и другое значение, если оно имеет значение false. В этом случае возвращается пустая строка ("") для всех значений ошибок, так как они оцениваются как истина и возвращает оставшиеся значения из диапазона (данные), так как они имеют значение ложь, то есть они не содержат значений ошибок. Функция сумм затем вычисляет итог для отфильтрованного массива.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= СУММ (1 * (Мидата_лт__гт_йоурдата))

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

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

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

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

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

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

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

Синхронизации

Части этой статьи посвящены ряду столбцов в Excel Power Users, написанных с помощью Алексей орехов и адаптированных из глав 14 и 15 формул в Excel 2002, которые написаны компанией John Валкенбач и старыми ЭКСПЕРТами по Excel.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Динамические массивы и поведение рассеянного массива

Формулы динамических массивов и устаревшие формулы для массивов CSE

Функция ФИЛЬТР

Функция СЛУЧМАССИВ

Функция ПОСЛЕДОВ

Функция ОДНОЗНАЧ

Функция СОРТ

Функция СОРТПО

Функция УНИК

Ошибки #ПЕРЕНОС! в Excel

Обзор формул

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×