Progress-servis55.ru

Новости из мира ПК
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel формулы массива условия

Формулы массива в EXCEL. Знакомство

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

Без формул массива (array formulas ) можно обойтись, т.к. это просто сокращенная запись группы однотипных формул. Однако, у формул массива есть серьезное преимущество: одна такая формула может заменить один или несколько столбцов с обычными формулами.

Например, можно найти сумму квадратов значений из диапазона А2:A12 , просто записав в ячейке B14 формулу =СУММ(A2:A12^2) . Для сравнения: чтобы найти сумму квадратов, используя обычные формулы, нам потребуется дополнительный столбец для вычисления квадратов значений и одна ячейка для их суммирования (см. файл примера или диапазон B 2 :B13 на рисунке ниже).

В отличие от ввода обычных формул, после ввода формулы массива нужно нажать вместо ENTER комбинацию клавиш CTRL+SHIFT+ENTER (поэтому, иногда, формулы массива также называются формулами CSE — это первые буквы от названия клавиш, используемых для ввода C trl, S hift, E nter). После этого формула будет обрамлена в фигурные скобки < >(их не вводят с клавиатуры, они автоматически появляются после нажатия CTRL+SHIFT+ENTER ). Это обрамление показано на рисунке выше (см. Строку формул ).

Если бы мы нажали просто ENTER , то получили бы сообщение об ошибке #ЗНАЧ!, возникающую при использовании неверного типа аргумента функции, т.к. функция СУММ() принимает в качестве аргумента только диапазон ячеек (или формулу, результатом вычисления которой является диапазон, или константы). В нашем случае мы в качестве аргумента ввели не диапазон, а некое выражение, которое еще нужно вычислить перед суммированием, поэтому и получили ошибку.

Чтобы глубже понять формулы массива проведем эксперимент:

  • выделим ячейку B13 , содержащую обычную формулу =СУММ($B$2:$B$12) ;
  • в C троке формул выделим аргумент функции СУММ() , т.е. $B$2:$B$12 ;
  • нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
  • получим <1:4:9:16:25:36:49:64:81:100:121>– массив квадратов значений из столбца В . Массив – это просто набор неких элементов (значений).

Т.е. обычная функция СУММ() в качестве аргумента получила некий массив (или точнее ссылку на него).Теперь проведем тот же эксперимент с формулой массива:

  • выделим ячейку, содержащую формулу массива =СУММ($A$2:$A$12^2) ;
  • в строке формул выделим аргумент функции СУММ() , т.е. $A$2:$A$12^2 ;
  • нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
  • получим <1:4:9:16:25:36:49:64:81:100:121>– тот же массив, что и в первом случае.

Т.е. нажатие CTRL+SHIFT+ENTER заставило EXCEL перед суммированием произвести промежуточные вычисления с диапазоном ячеек (с массивом содержащихся в нем значений). Для самой функции СУММ() ничего не изменилось – она получила тот же массив, только предварительно вычисленный, а не прямо из диапазона ячеек, как в случае с обычной формулой. Понятно, что вместо функции СУММ() в формуле массива может быть использована любая другая функция MS EXCEL: СРЗНАЧ() , МАКС() , НАИБОЛЬШИЙ() и т.п.

Вышеприведенный пример иллюстрирует использование функции массива возвращающей единственное значение, т.е. результат может быть выведен в одной ячейке. Это достигается использованием функций способных «свернуть» вычисленный массив до одного значения ( СУММ() , СРЗНАЧ() , МАКС() ). Примеры таких функций массива приведены в статье Формулы массива, возвращающие одно значение .

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

Преимущества и недостатки формул массива рассмотрены в одноименной статье Формулы массива. Преимущества и недостатки .

В файле примера также приведено решение данной задачи функцией СУММПРОИЗВ() , которая зачастую не требует введения ее как формулы массива: =СУММПРОИЗВ($A$2:$A$12^2)

Здесь, при вводе формулы СУММПРОИЗВ() нажимать CTRL+SHIFT+ENTER необязательно.

Ссылки на статьи о формулах массива на сайте Microsoft:

2. Основы формул массива (на английском) из книги авторов Colin Wilcox и John Walkenbach

ПРИМЕЧАНИЕ : При создании Именованных формул и правил Условного форматирования формулы массива нельзя ввести нажимая CTRL+SHIFT+ENTER . Эти формулы вводятся только в ячейки листа. Однако, если формуле массива присвоить Имя , то EXCEL «сообразит», что нужно с ней нужно делать. Например, если формуле =СУММ($A$2:$A$12^2) присвоить имя Сумма_квадратов, а затем в ячейке указать =Сумма_квадратов , то получим правильный результат.

Excel. Некоторые примеры использования формул массива

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

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

Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов – это наиболее сложное и непонятное средство Excel.

Если вы уже постигли азы, предлагаю вам продолжить знакомство с формулами массива вместе с Джоном Уокенбахом и его книгой MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.

Cкачать заметку в формате Word, примеры в формате Excel

Создание массивов на основе значений ячеек диапазона

На рис. 1 показан рабочий лист, содержащий данные в диапазоне A1:C4. Диапазон D8:F11 содержит массив, созданный на основе этих данных с помощью формулы

Рис. 1. Создание массива на основе значений ячеек диапазона

Массив в диапазоне D8:F11 связан со значениями диапазона A1:C4. Если изменить какое-либо значение в последнем диапазоне, то автоматически изменится соответствующее значение в массиве D8:F11.

Создание массива констант на основе значений диапазона ячеек

В предыдущем примере формула массива в ячейках D8:F11, по существу, являлась ссылкой на диапазон A1:C4. Чтобы «разорвать» эту связь и создать массив констант, выполните ряд действий.

  1. Выделите диапазон, содержащий формулу массива (в этом примере – D8:F11).
  2. Нажмите клавишу , чтобы перейти в режим редактирования формулы.
  3. Нажмите клавишу , чтобы преобразовать ссылки на ячейки в соответствующие значения.
  4. Нажмите комбинацию клавиш .

Теперь в диапазоне D8:F11 находится следующий массив: <1; " кот " ;3:4;5; " собака " :7;89;9,12: " обезьяна " ;11;44>. На рис. 2 показано, как этот массив выглядит в строке формул.

Рис. 2. После нажатия клавиши в строке формул отобразится массив констант

Выполнение операций над массивами

Следующая формула массива создает прямоугольный массив и умножает каждый его элемент на 2: <=<1;2;3;4:5;6;7;8:9;10;11;12>*2> (рис. 3)

Рис. 3. Результат умножения массива на 2

Следующая формула умножает каждый элемент массива на самого себя: <=<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:С4), то подобная формула возвратит квадрат каждого элемента этого массива: <=А1:С4^2>(рис. 4).

Рис. 4. Возведение значений массива в квадрат

Применение функций к массивам

В операциях над массивами можно использовать функции. Следующая формула массива, которую нужно ввести в вертикальный диапазон, состоящий из 10 ячеек, вычисляет квадратные корни каждого элемента массива: <=КОРЕНЬ(<1:2:3:4:5:6:7:8:9:10))>. Если массив хранится в диапазоне ячеек (например, А1:А10), подобная формула выполнит эти же вычисления для каждого элемента массива: <=КОРЕНЬ(А1:А10)>(рис. 5).

Рис. 5. Извлечение квадратного корня из элементов массива

Транспонирование массивов

При транспонировании массива его строки становятся столбцами, а столбцы — строками. Если массив одномерный, то при транспонировании горизонтальный массив становится вертикальным и наоборот. Для выполнения операции транспонирования используется функция Excel ТРАНСП. Допустим, имеется одномерный горизонтальный массив <1; 2; 3; 4; 5>, расположенный в диапазоне А1:Е1. С помощью функции ТРАНСП можно преобразовать его в вертикальный массив. Для этого выделите вертикальный диапазон, состоящий из пяти ячеек, введите формулу =ТРАНСП(А1:Е1) и нажмите комбинацию клавиш (рис. 6).

Рис. 6. Транспонирование одномерного массива

Транспонирование двухмерных массивов выполняется подобным образом. На рис. 7 показан двухмерный массив в диапазоне A1:D3. Формула <=TPAHCП(A1:D3)>создает в диапазоне F1:H4 транспонированный массив.

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

Генерирование последовательности натуральных чисел

С помощью формул массивов легко генерировать последовательности натуральных чисел. Для этого идеально подходит функция СТРОКА, возвращающая номер строки. Рассмотрим формулу массива, введенную в вертикальный диапазон, состоящий из 12 ячеек: <=СТРОКА(1:12)>. Эта формула возвращает массив из 12 элементов, содержащий числа от 1 до 12 (рис. 8). Отметим, что данная формула возвращает правильный результат, независимо от того, где расположен диапазон, в который вводится эта формула.

Рис. 8. Последовательности натуральных чисел на основе функции СТРОКА

Если вы поэкспериментируете с приведенной формулой, то обнаружите присущий ей недостаток: при добавлении новой строки выше диапазона, где расположена эта формула, Excel изменит номера строк, и формула автоматически преобразуется в формулу, возвращающую значения от 2 до 13: <=СТРОКА(2:13)>(рис. 9).

Рис. 9. Добавление строки над массивом изменяет ряд чисел

Приведем формулу массива, которая лишена этого недостатка: <=СТРОКА(ДВССЫЛ( " 1:12 " ))>. Эта формула использует функцию ДВССЫЛ, аргументом которой является текстовая строка. Excel не изменяет этот аргумент при вставке или удалении строк на рабочем листе, поэтому данная формула всегда возвращает правильный результат, состоящий из чисел от 1 до 12 (рис. 10).

Рис. 10. Последовательность натуральных чисел на основе функции ДВССЫЛ не меняется при добавлении строк над массивом

Функции, возвращающие массив. Результатом выполнения некоторых функций Excel является массив. Чтобы такие функции возвращали правильный результат, выделите диапазон, введите формулу, содержащую одну из этих функций, как формулу массива. Вот несколько функций, которые возвращают массив: ПРЕДСКАЗ, ЧАСТОТА, РОСТ, ЛИНЕЙН, ЛГРФПРИБЛ, МОБР, МУМНОЖ и ТЕНДЕНЦИЯ. Дополнительную информацию об этих функциях можно найти в справочной системе Excel.

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

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

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

Если необходимо подсчитать общее количество символов в определенном диапазоне, то традиционный метод выполнения этой задачи состоит в том, чтобы найти количество символов в каждой ячейке диапазона, например, с помощью формулы =ДЛСТР(А1), и затем суммировать эти значения (рис. 11). Формула массива <=СУММ(ДЛСТР(А1:А10))>выполняет эти вычисления без промежуточных формул. Эта формула с помощью функции ДЛСТР создает виртуальный массив в памяти компьютера, содержащий количество символов каждой ячейки диапазона, а затем суммирует элементы виртуального массива.

Рис. 11. Одна формула массива заменяет целый ряд отдельных формул

Суммирование трех наименьших значений диапазона

Предположим, что имеется диапазон чисел, названный Данные, и вы хотите узнать сумму трех наименьших чисел этого диапазона. Формула массива решает эту задачу: <=СУММ(НАИМЕНЬШИЙ(Данные;<1;2;3>))> (рис. 12). Здесь в качестве второго аргумента в функции НАИМЕНЬШИЙ используется массив констант. Это вынуждает Excel сгенерировать виртуальный массив, содержащий три наименьших значения из диапазона Данные. Затем к этому виртуальному массиву применяется функция СУММ, которая суммирует его элементы и возвращает искомый результат.

Рис. 12. Формула массива возвращает сумму трех наименьших значений поименованного диапазона Данные (A1:А10)

Подсчет количества ячеек, содержащих текст

В следующей формуле функция ЕСЛИ используется для проверки содержимого каждой ячейки диапазона. Затем создается виртуальный массив (того же размера, что и исходный), в котором содержатся числа 1 (если в ячейке находится текст) и 0 (в противном случае). Новый массив передается в функцию СУММ, которая возвращает сумму элементов массива. В итоге получаем количество ячеек, содержащих текст: <=СУММ(ЕСЛИ(ЕТЕКСТ(А1:D5);1;0))>. На рис. 13 показан пример использования этой формулы в ячейке С8. В результате выполнения функции ЕСЛИ создается виртуальным массив <0;1;1;1:1;0;0;0:1;0;0;0:1;0;0;0:1;0;0;0>. Обратите внимание на то, что виртуальный массив состоит из пяти строк, содержащих по четыре элемента (т.е. тот же размер, что и исходный массив). Функция СУММ суммирует значения этого виртуального массива.

Рис. 13. Формула массива возвращает число ячеек диапазона, содержащих текст

Следующая формула выполняет туже работу, но записана в более компактном виде <=СУММ(ЕТЕКСТ(A1:D5)*1)>. Здесь вместо функции ЕСЛИ используется тот факт, что ИСТИНА*1=1, а ЛОЖЬ*1=0

Исключение промежуточных формул

Одно из достоинств формул массивов заключается в том, что они позволяют исключать промежуточные формулы (и соответственно результаты промежуточных вычислений) с рабочих листов. Это делает таблицы на рабочих листах более компактными и понятными. На рис. 14 показан рабочий лист, содержащий баллы студентов до и после выполнения тестов. В столбце D содержатся формулы, вычисляющие разность между этими баллами, в ячейке D12 – формула, вычисляющая среднее значение этих разностей: =CPЗHAЧ(D2:D11).

Рис. 14. Вычисление среднего с помощью промежуточных формул и с помощью одной формулы массива

С помощью следующей формулы массива, записанной в ячейке С15, можно исключить промежуточные вычисления в столбце D: <=СРЗНАЧ(С2:С11-В2:В11)>. В этой формуле используются два массива, содержащиеся в диапазонах С2:С11 и В2:В11. Формула создает виртуальный массив, состоящий из разностей значений этих двух массивов. Этот виртуальный массив хранится в памяти компьютера, а не в рабочем листе Excel. Функция СРЗНАЧ использует этот виртуальный массив в качестве своего аргумента и возвращает среднее его элементов. Виртуальный массив имеет вид <27;18;18;10;3;8;0;17;8;-8>. Поэтому в данном случае предыдущая формула принимает вид =СРЗНАЧ(<27;18;18;10;3;8;0;17;8;-8>)

Аналогично создаются и работают формулы массивов, вычисляющие различные характеристики диапазонов данных. Например, следующая формула находит максимальное число среди разностей значений диапазонов С2:С11 и В2:В11: <=МАКС(C2:C11-B2:B11)>. Эта формула вернет число 27. А эта формула <=МИН(C2:C11-B2:B11)>возвращает наименьшее число разностей значений двух диапазонов. Формула вернет число -8.

Использование массивов вместо ссылок на диапазоны

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

На рис. 15 представлена таблица, в которой производится поиск названия заданного числа. Например, если 9 – искомое значение, то формула вернет слово Девять. В ячейку С1 введена следующая формула: =ВПР(В1;D1:Е10;2;ЛОЖЬ). Вместо ссылки на таблицу можно использовать двухмерный массив. Формула, приведенная ниже, работает аналогично предыдущей, но не использует таблицу, которая находится в диапазоне D1:E10.

Рис. 15. Таблицу, в которой производится поиск, можно заменить массивом констант

[1] К сожалению, в функциях, работающих с базами данных (например, БДСУММ), нельзя заменить ссылку на диапазон, содержащий критерии, массивом констант.

Формулы массива в Excel

Терминология

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

Пример 1. Классика жанра — товарный чек

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

  1. выделяем ячейку С7
  2. вводим с клавиатуры =СУММ(
  3. выделяем диапазон B2:B5
  4. вводим знак умножения (звездочка)
  5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:

  • чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter
  • Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

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

    Пример 2. Разрешите Вас. транспонировать?

    При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

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

    • Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
    • вводим функцию транспонирования =ТРАНСП(
    • в качестве аргумента функции выделяем наш массив ячеек A1:B8

    жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:

    Редактирование формулы массива

    Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

    Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

    Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

    Пример 3. Таблица умножения

    Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:

    При помощи формул массива она вся делается в одно движение:

    1. выделяем диапазон B2:K11
    2. вводим формулу =A2:A11*B1:K1
    3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

    и получаем результат:

    Пример 4. Выборочное суммирование

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

    В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

    Что такое формула массива

    Если Вы довольно много работаете в Excel то наверняка уже слышали выражение «формула массива«. Так же его часто можно встретить на форумах и сайтах, посвященных Excel. Но не все знают что это такое и тем более, как пользоваться. Главное это не путать формулы массива с функциями для работы с массивами и функциями для работы с базами данных. Итак, не буду затягивать.
    Большинство уже наверняка знакомо с функцией СУММ (SUMM) . Она суммирует значения в заданных диапазонах ячеек. Теперь рассмотрим поближе. Запишем в ячейки А1:А5 числа от одного до пяти. В ячейке В1 запишем функцию: =СУММ(A1:A5) . Получим сумму этих чисел — 15. Все просто и понятно. И вроде бы — функция уже работает с массивами — ведь группа ячеек А1:А5 по сути уже массив ячеек. Но это не формула массива. А теперь изменим функцию на такую: =СУММ(A1:A5+1) . Что я хочу получить от такой функции? Чтобы суммировались значения ячеек А1:А5, но с прибавлением к каждому аргументу 1. Ввожу функцию и. Результат будет 2. Явно что-то не так. Верно, ведь при такой записи Excel произведет сложение ячейки А1 и 1. Все дело в том, что напрямую Excel не понимает, что необходимо производить подобные операции с массивами. Ему надо явно указать, что мы хотим это сделать и результат вернуть в одну ячейку. Чтобы ему указать это, надо при вводе функции нажать не Enter, как мы привыкли, а целую комбинацию клавиш — Ctrl+Shift+Enter. Т.е. записали функцию в ячейку и не жмем Enter, чтобы завершить ввод, а жмем Ctrl+Shift+Enter. Функция при таком вводе будет заключена в фигурные скобки — < =СУММ(A1:A5+1) > . Если Вы после ввода их увидели — значит все сделано верно и формула массива введена правильно. И результат будет 20.

    Важно: не надо пытаться ввести фигурные скобки вручную с клавиатуры -результатом будет лишь текст в ячейке <=СУММ(A1:A5+1)>и ни о какой формуле и суммировании речи быть уже не может.

    Что же происходит внутри функции в этот момент? Все очень просто. Мы в ячейки А1:А5 ввели поочередно цифры от 1 до 5. Т.е. получили: 1, 2, 3, 4, 5. В сумме они дают 15. Я разложу на слагаемые: =СУММ(1;2;3;4;5) . Теперь мы изменили функцию и ввели её как формулу массива: <=СУММ(A1:A5+1)>. И внутри происходит сначала прибавление к каждому числу 1, а затем сложение уже измененных аргументов: =СУММ(1+1;2+1;3+1;4+1;5+1) ⇒ =СУММ(2;3;4;5;6)

    Рассмотрим еще один пример, когда формула массива может решить задачу непосильную стандартной формуле(да еще и в одной ячейке без доп.столбцов). Необходимо получить минимальное значение из массива чисел: 0;1;5;5;9;0;6;2;6;3
    Применив обычную формулу =МИН(A1:A10) мы получим нуль. Что будет верным. Но если нам как раз нуль учитывать не надо? Мы можем ввести такую формулу:
    =МИН(ЕСЛИ(A1:A10<>0;A1:A10)) Казалось бы условие задано верно и мы должны получить нужный результат, т.е. 1. Но! Т.к. это простая формула, она обрабатывает не массив значений, а только первое значение массива(A1:A10) из условия ЕСЛИ, т.е. только А1. Сама по себе функция ЕСЛИ не станет работать с массивом значений в данном случае. Это означает, что формула не просматривает весь заданный массив. Но если ввести её как формулу массива
    <=МИН(ЕСЛИ(A1:A10<>0;A1:A10))> то в таком случае формула последовательно просмотрит каждое значение из массива на предмет выполнения условия и выполнит необходимые вычисления, заданные в этой формуле, так как будто бы мы последовательно для каждой строки в отдельном столбце вывели результат выполнения заданного условия ЕСЛИ и уже по этим результатам определили минимальное значение. И результат формулы будет — 1.

    Так же формула массива может вернуть несколько значений. Очень наглядно это демонстрирует функция ТРАНСП (TRANSPOSE) . Функция преобразовывает вертикальный массив в горизонтальный и наоборот. Массив может быть многомерным. Как работает функция(на примере исходного диапазона A1:C10 ):

    • выделяете диапазон ячеек( D1:M3 ), равный по количеству ячеек исходному диапазону значений( A1:C10 ), которые необходимо транспонировать;
    • вписываете функцию ТРАНСП;
    • в качестве аргумента указываете ссылку на исходный диапазон значений: =ТРАНСП( A1:C10 ) ;
    • завершаете ввод функции сочетанием клавиш Ctrl+Shift+Enter.

    В диапазоне D1:M3 получите транспонированную таблицу. При написании функции следует учитывать, что число строк в диапазоне функции( D1:M3 ) должно быть равно числу столбцов в исходном диапазоне( A1:C10 ), а число столбцов — числу строк. Если указать меньше — не все значения будут транспонированы. Если больше — то все лишние ячейки будут заполнены значениями #Н/Д

    Какие особенности подобного применения функций массива:

    • во всех ячейках формула отображается совершенно одинаково, даже если ссылки на ячейки относительные. Это не должно вас пугать — так надо;
    • ячейки диапазона, в который подобным образом введена формула массива нельзя изменять по отдельности — только все вместе. В противном случае просто получите сообщение «Нельзя изменять часть массива!». Бывает очень удобно иногда в целях защиты формул от изменений.

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

    • ввод формулы завершается сочетанием клавиш Ctrl+Shift+Enter;
    • Если формулу массива записать сразу в несколько ячеек, то формула будет одна для всех ячеек и вернет для каждой ячейки свой результат

    Статья помогла? Поделись ссылкой с друзьями!

    Читать еще:  Диапазон ячеек в excel это
    Ссылка на основную публикацию
    Adblock
    detector