Высший пилотаж: массивы в Excel
Друзья, в предыдущем посте о об округлении чисел я обещал рассказать о формулах массивов. Это мощный и сложный инструмент, о котором знают и пользуются не многие. Такие формулы позволяют выполнять расчеты, которые нельзя выполнить другими средствами Эксель, снизить громоздкость таблиц, исключив промежуточные расчеты. Давайте же не буду томить Вас ожиданием, переходим к изучению материала.
Что такое формулы массивов в Эксель
Как следует из самого названия, формулы массивов – это вычисления, связанные с обработкой массивов (матриц) данных. Некоторые операции с массивами можно выполнить с помощью функций, а некоторые, можно посчитать только с применений формул массивов.
Например, чтобы посчитать среднее значение чисел в диапазоне А1:А10 , можно использовать функцию СРЗНАЧ(Диапазон)
Среднее значение в Excel
Как известно, функция СРЗНАЧ игнорирует пустые ячейки, но учитывает нулевые. А что если их не нужно учитывать? Вот тогда поможет формула массива. И мы с Вами посчитаем правильное среднее значение прямо сейчас! Вам необходимо понимать один факт: в формулах массивов вместо одного значения для расчетов можно указать целый диапазон.
Давайте запишем формулу: =СРЗНАЧ(ЕСЛИ(A1:A10=0;»»;A1:A10)) и нажмем Ctrl+Shift+Enter . Такой комбинацией мы сигнализируем Excel, что применяется формула массива. Программа обернет формулу в фигурные скобки, обозначая применение именно формулы массива. Не дописывайте фигурные скобки сами, это не сработает, нужно именно нажать комбинацию клавиш. Как же работает эта формула:
- Обычно первым аргументом функции «ЕСЛИ» записывается условие. Например, «A1=0», и если значение в ячейке А1 равно нулю, выполняется второй аргумент, в противном случае – третий. Мы же передали функции ссылку не на одну ячейку, а на массив из десяти значений ( А1:А10 ). Функция «ЕСЛИ» проверит каждое из них и все нулевые заменит пустой строкой. Результатом будет массив из десяти чисел или пустых значений. Такой список функция СРЗНАЧ уже отработает правильно;
- Полученный массив передается в функцию СРЗНАЧ, и теперь она считает среднее значение чисел без учета нулей, т.к. их уже нет.
Как видите, результат вычисления получился другой. Несложно проверить, что именно это число мы ожидали получить.
Рассмотрим еще один пример, похожий на предыдущий.
У нас есть ежедневные продажи за 20 дней. Нужно посчитать сумму продаж в те дни, когда касса превысила 5000. В обычных условиях нам понадобится промежуточный расчет. Так, в колонке С мы проверяем, если продажи выше пяти тысяч – возвращаем эту сумму, если ниже – записываем ноль. Далее, в ячейке С23 суммируем все числа столбца С и получаем искомый результат.
Теперь посмотрите, как мы сделали все эти действия одновременно в одной лишь ячейке Е4 и получили такой же результат! Обратите внимание на простую формулу в строке формул:
Получается, промежуточный расчет делать и не нужно было, он лишь засоряет таблицу, усложняя структуру страницы. Очевидное преимущество за формулой массива, правда?
Сумма без учета ошибок
Отличный способ применения формул массивов – сложить числа в массиве, содержащем ошибки. Пусть в массиве В2:F5 у нас результаты вычислений, среди которых встречаются ошибки. Как сложить эти числа, игнорируя ошибки? Очень просто, вот вам формула: =СУММ(ЕСЛИОШИБКА(B2:F5;0)) . Здесь функция ЕСЛИОШИБКА проверит содержание каждой ячейки и заменит ( в памяти компьютера, не на листе ) ошибки на нули. Далее, функция СУММ просуммирует все обработанные значения и выдаст результат без учета ошибок.
Общий смысл применения формул таков, что Вы заменяете одно значение на массив, а Excel выполняет вычисления для каждого из элементов массива. Это нужно понимать и осознавать, тогда формулы массивов Вам будут по плечу. Очень практичными получаются формулы массивов с применением поименованных диапазонов вместо обычных ссылок.
Эксперты MS Excel выстраивают из подобных формул огромные комбинации, выполняя в одной ячейке столько операций, сколько обычные пользователи умещают на несколько листов. Так что, экспериментируйте, пробуйте разные варианты применения, и не забывайте: для завершения ввода нажимаем Ctr+Shift+Enter , вместо клавиши «Ввод».
Если что-то из темы статьи вам непонятно или не получается, задавайте вопросы в комментариях, будем разбираться вместе!
А в следующей публикации мы с вами будем рисовать диаграммы, а это слишком важная тема, чтобы ее пропустить, подписывайтесь на электронную рассылку и читайте свежие статьи пока горячи!
VBA Excel. Диапазон ячеек и массив (обмен значениями)
Копирование значений из диапазона ячеек в массив и обратно с помощью VBA Excel. Простейшие примеры обмена значениями между диапазоном и массивом.
Копирование значений из диапазона ячеек в массив
Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (As Variant) и присвоить ей значения диапазона ячеек с помощью оператора присваивания (=):
VBA Excel автоматически преобразует объявленную переменную в двумерный массив, соответствующий размерности диапазона ячеек, в нашем случае в массив — a(1 To 3, 1 To 3), и заполняет его значениями. Нумерация измерений массивов, созданных таким образом, начинается с единицы (1).
Можно, в этом случае, объявить сразу динамический массив, чтобы изначально указать, что эта переменная будет массивом. Так как свойством диапазона ячеек по-умолчанию в VBA Excel является значение (Value), его можно в коде явно не указывать, но, при желании, можно и указать. Получится такая конструкция, аналогичная первой:
Стоит отметить, что для копирования значений из диапазона ячеек в массив можно использовать только обычную переменную или динамический массив универсального типа (Variant). VBA Excel автоматически преобразовывает их в двумерный массив. Если объявить двумерный массив с указанной заранее размерностью, использовать его не получится, будет сгенерирована ошибка с сообщением: Can’t assign to array (Нельзя назначать массив).
Копирование значений из массива в диапазон ячеек
Значения в диапазон ячеек добавляются из массива с помощью оператора присваивания (=):
Обратите внимание, что вставить значения в диапазон ячеек можно только из двумерного массива. Размерность такого массива может начинаться с нуля (0). Количество элементов в измерениях массива должно совпадать с количеством строк и столбцов в диапазоне ячеек. Если вам нужно вставить значения в одну строку или в один столбец, укажите размерность единственной строки или единственного столбца как (0) или (1 To 1), если вы хотите использовать нумерацию измерений своего массива с единицы. Например, для записи десяти значений из массива в одну строку можно объявить такой массив — massiv(9, 0), или в один столбец — massiv(0, 9).
Для вставки значений в диапазон ячеек из массива идеально подойдет массив, созданный для копирования в него значений из диапазона. В этом случае, данные с рабочего листа Excel переносятся в массив, обрабатываются и, после обработки, вставляются обратно в ту же или другую таблицу на том же или другом рабочем листе.
Простейшие примеры обмена значениями
Эти примеры составлены так, чтобы вам не пришлось совершать лишних действий, просто скопируйте их в свой модуль любой книги Excel с поддержкой макросов и запустите по-очереди на выполнение.
Пример 1
Заполнение двумерного массива значениями и и их присвоение диапазону ячеек на рабочем листе Excel: