Учебное пособие по Microsoft Excel. Может ли пользовательская функция VBA генерировать динамический массив?
Добро пожаловать на очередной выпуск «Советы и рекомендации по Excel VBA»! В этом видео мы рассмотрим два важных вопроса: можем ли мы создать N копий каждой строки на основе значений в ячейках? И что еще более важно, может ли VBA вернуть динамический массив? Эти вопросы были опубликованы исполнительным инженером на YouTube и вызвали бурное обсуждение некоторых замечательных решений в нашем сообществе.
К сожалению, исходный вопрос и решения были удалены, но, к счастью, все они были зафиксированы в теме на MrExcel.com. Я настоятельно рекомендую проверить это, так как было несколько фантастических ответов с использованием Power Query, LAMBDA и LET. Особая благодарность Smozgur и XLLambda за их вклад.
Но, как всегда, моей первой мыслью было использовать VBA. Итак, в этом видео я покажу вам, как написать пользовательскую функцию в VBA, которая действует как СОРТИРОВКА, ПОСЛЕДОВАТЕЛЬНОСТЬ и ФИЛЬТР. Мы будем использовать функцию РАСЧИНИТЬ для преобразования таблицы с такими данными, как «Яблоко, банан, вишня» и «Четыре яблока, три банана, две вишни» в динамический массив с выходными данными «четыре яблока, три банана, две вишни». .
Для начала откройте редактор VBA, нажав Alt+F11, и вставьте новый модуль. Затем просто скопируйте и вставьте код, который я покажу в этом видео. Затем мы вернемся в Excel и увидим код в действии. Я впервые пишу VBA для возврата динамического массива в сетку, так что для меня это довольно волнующий момент.
В коде мы будем использовать переменную с именем R для обозначения диапазона, который мы будем передавать функции. Первый столбец этого диапазона будет содержать элементы, которые мы хотим повторить, а второй столбец будет содержать количество повторений каждого элемента. Затем мы будем использовать эту информацию для построения нашего ответа в новой переменной с именем Answer. Мы также будем использовать функцию r.Rows.Count, чтобы определить количество строк в диапазоне, передаваемом в функцию.
Одна из наиболее интересных частей этого кода — использование функции Application.Transpose для преобразования горизонтального массива в вертикальный. Это необходимо, поскольку всем пользовательским функциям должна быть присвоена переменная с тем же именем, что и у функции, и в этом случае функция возвращает массив, который растягивается по всей странице. Но не волнуйтесь, я проведу вас через каждый шаг кода и подробно объясню его.
В итоге у нас будет полнофункциональная UDF, способная на лету генерировать динамический массив. Это отличное решение для тех, кто предпочитает использовать VBA в своих проектах Excel. И, как всегда, я рекомендую вам ознакомиться с замечательными решениями Power Query и формулами в теме на MrExcel.com. Спасибо за просмотр. Увидимся в следующем видео, где вы увидите больше советов и рекомендаций по Excel.
Купите последнюю книгу Билла Джелена по Excel: https://www.mrexcel.com/products/latest/
Вы можете помочь моему каналу, нажав «Нравится» или оставив комментарий ниже: https://www.mrexcel.com/like-mrexcel-...
Оглавление
(0:00) Два названия
(1:11) Использование VBA
(2:05) Сколько ячеек вернуть
(2:50) Построение массива
(3:22) Возврат массива в сетку
(4:00) Использование оператора хэш-массива
Это видео отвечает на следующие распространенные поисковые запросы:
Создание UDF VBA для создания динамических массивов в Excel
Демонстрация использования функции РАЗВЕДЕНИЕ в Excel
Разнесение таблицы в Excel с помощью VBA
Изучение использования VBA в Excel для динамических массивов.
Решения для создания динамических массивов с помощью Power Query и формул
Пошаговое руководство по написанию кода VBA для создания динамических массивов
Использование функций Power Query, LAMBDA и LET для динамических массивов
Присоединяйтесь к обсуждению этого видео на доске объявлений MrExcel по адресу https://www.mrexcel.com/board/threads....
Посмотрите потрясающие ответы от Smozgur и XLLambda здесь:
https://www.mrexcel.com/board/threads...
Информация по комментариям в разработке