Работа с массивами в эксель: Работа с массивами функций в Excel

основное для работы с массивами и базами данных — Stepik

Данный курс — аналог курса автора на платформе UDEMY. Он дает основные навыки работы с массивами и базами данных (функции, умные таблицы, именованные диапазоны и формулы) для бизнес-пользователей.

What you will learn

• Понятие массивов данных
• Функции \ формулы для работы с массивами и базами данных
• Именованные диапазоны и формулы
• Умные таблицы

About this course

• Этот курс — это способ быстро ознакомиться с возможностями MS Excel в части работы с массивами и базами данных. Владеющих такими знаниями сотрудников в офисной среде часто считают «гуру Excel» :).

• Курс не просто дает общее представление, а и обучает набору функционалов Excel для решения прикладных задач в части работы с массивами и умными таблицами на обычных листах (рабочей области) программы Excel

• В курсе основной упор не на демонстрации экрана и кнопок по ходу лекций, а на практическое освоение функционала.

  Поэтому курс насыщен практическими заданиями (40 ЗАДАНИЙ!).

• Курс от профессионала в анализе данных: владеющего навыками от обработки данных в MS Excel и статанализа с предиктивной аналитикой в спецпрограммах (SPSS, JASP, Statistica…) — и до методов Data Science применяемых в разработке систем «искусственного интеллекта».

• Создатель курса — автор популярной бизнес -литературы (доступна в крупнейших магазинах: Amazon, Ozon, ЛитРес, Ridero…). В частности, автор одного из русскоязычных бестселлеров  в категории «Анализ данных» — книги «Аналитика и Data Science для не-аналитиков и даже 100% гуманитариев», а также одной из первых отечественных книг авторов по работе с компонентом Power Query для Excel и Power BI   «Power Query: учебное руководство. От новичка до уверенного бизнес-пользователя

  «;

• Отобраны только самые необходимые знания Excel в части работы с массивами, достаточные для быстрого старта — т. е., Вам будут даны основные самые  часто используемые знания и навыки для работы с массивами и БД в Excel, а не «загрузка» всем что только можно.   

• Курс НЕ ОХВАТЫВАЕТ работу с массивами и БД с помощью надстроек семейства Power (Power Query, Power Pivot, Power View) — для этого есть отдельный курс.

• Никаких макросов (VBA), программных языков (DAX, M) и «километровой математики с трехэтажными формулами» — все изучаем постепенно и на простых примерах.

Whom this course is for

Бизнес-пользователи Excel, которые хотят расширить понимание этой программы и научиться работать с функциями массивов и БД Профессионалы и специалисты, работающие с массивами данных Любой специалист, желающий разобраться в работе с массивами данных с применением программы Excel

Initial requirements

Установленный на Вашем компьютере MS Excel. Не обязательно, но желательно чтобы Ваш MS Excel был совместимый с Power Pivot так как последние пару лекций и заданий курса будут связаны с Power Pivot  (если Вы не знаете совместима ли Ваша версия, то задайте в поисковике Google или Yandex «Где есть Power Pivot? — Excel — Microsoft Support» — и посмотрите в найденной статье) 

Владение программой Excel на уровне бизнес-пользователя (понимать как работает, форматы ячеек, фильтрация, понимать что такое абсолютные и относительные ссылки, фиксация F4, транспонирование, уметь применять основные функции\формулы (математические, логические, сверка ИСТИНА-ЛОЖЬ…), знать что такое обязательные и необязательные аргументы функций, строить сводные таблицы)

Meet the Instructors

How you will learn

Курс состоит из разделов (модулей), которые и снабдят Вас минимумом необходимых теоретических знаний, и покроют все основные практические аспекты работы с функциями массивов и БД, а также научат именовать диапазоны и работать с «умными» таблицами в Excel.  

Каждый из модулей будет состоять из лекций и практических заданий (иногда также будут тесты). 

Вы смотрите лекцию (при необходимости знакомитесь с приложенным допматериалом, если такой есть). Если не по ходу просмотра лекции не уловили что-то — возвращаемся и пересматриваем.

Если после лекции идет задание — то выполняете его. Не оставляете на потом, а сразу делаете, отвечаете на вопросы и сверяете с ответом преподавателя.

Когда доходите до теста (поочередный набор вопросов после лекции) — проходите его, так как тест поможет Вам сориентироваться что Вы усвоили хорошо, а что стоит повторить.  

И из всего этого самое главное — не ленимся делать задания! Только так, иначе Вы пришли на этот курс зря. 

Course content

Certificate

OpMod и Stepik

What you will get

Price

FAQ

How to pay from the company?

Share this course

https://stepik. org/course/173117/promo

Direct link:
https://stepik.org/173117

Как в VBA правильно создавать одномерный массив. ArrayList / Хабр

Когда я впервые встретил этот класс, подумал «Зачем? Ведь есть простые массивы». А потом попробовал и не представляю как жил без него раньше.

Начну сразу с примера

Предположим, на активном листе в столбце 1 находится список ФИО сотрудников.

Список ФИО

Наша задача собрать в массив только уникальные ФИО и отсортировать его по убыванию (ну такая вот немного странная задача). Сначала решим ее без использования

ArrayList, а в конце сравним результат.

Для получения уникальных значений, создаем функцию GetDistinctItems и в нее передаем столбец с ФИО. В самой функции пробегаем циклом For Each по всем ФИО и добавляем уникальные в объект Buffer (Dictionary). Далее методом Keys извлекаем элементы в дополнительную функцию DescendingSort (используем сортировку пузырьком) и получаем отсортированные значения в переменную Sorted, которую и возвращаем как результат функции.

Public Sub Main()
    Dim FullNameColumn As Range
    Set FullNameColumn = ActiveSheet.UsedRange.Columns(1) ' Получаем первый столбец.
    Dim DistinctList As Variant
    DistinctList = GetDistinctItems(FullNameColumn) ' Передаем диапазон в функцию.
    Debug.Print Join(DistinctList, vbCrLf) ' Выводим результат.
End Sub
Public Function GetDistinctItems(ByRef Range As Range) As Variant
    Dim Data As Variant: Data = Range.Value ' Преобразуем диапазон в массив.
    Dim Buffer As Object: Set Buffer = CreateObject("Scripting.Dictionary") ' Создаем объект Dictionary.
    Dim Item
    For Each Item In Data
        If Not Buffer.Exists(Item) Then Buffer.Add Item, Empty ' Проверяем наличие элемента и добавляем если отсутствует.
    Next
    
    Dim Sorted As Variant
    Sorted = DescendingSort(Buffer.Keys()) ' Сортируем функцией DescendingSort.
    GetDistinctItems = Sorted ' Возвращаем результат.
End Function
Public Function DescendingSort(ByRef Data As Variant) As Variant
    Dim i As Long
    For i = LBound(Data) To UBound(Data) - 1
        Dim j As Long
        For j = i + 1 To UBound(Data)
            If Data(i) < Data(j) Then
                Dim Temp As Variant
                Temp = Data(j)
                Data(j) = Data(i)
                Data(i) = Temp
            End If
        Next
    Next
    DescendingSort = Data
End Function

Результат

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

Что есть такое

Начнем с того, что ArrayList это класс из пространства имен System.Collections библиотеки mscorlib, который реализует интерфейс IList. Естественно, в VBA он несколько порезан в плане методов, иначе и быть не могло (например нет методов AddRange или BinarySearch). Но и тем не менее с ним можно (и нужно) работать.
По сути, это динамический массив. Его не нужно самостоятельно переопределять, чтобы изменить размерность, достаточно добавлять элементы с помощью метода Add. Где-то я читал, что на низком уровне (да простят меня знатоки, я не знаю правильно ли я применяю это словосочетание здесь) есть свои нюансы в плане производительности, но, откровенно говоря, за все время использования этого объекта каких-либо проблем я не замечал и время работы макроса из-за него если и растет вообще, то совсем не критично.

В чем же сила брат удобство?

Как минимум в том, что это динамический массив. Вы просто добавляете элементы через метод и не нужно заморачиваться на тему ReDim (и уж тем более Preserve) и вычислений размеров будущего массива.

А дальше начинаются вкусняхи

Во-первых, мы можем выгрузить все элементы одним методом ToArray. Как следует из названия, он преобразует все элементы объекта в обычный массив типа Variant.
Во-вторых, мы можем составлять список уникальных значений, проверяя их наличие методом Contains.

В-третьих, можно забыть про функцию UBound, ведь у этого класса есть свойство Count, которое, как не сложно догадаться, возвращает количество элементов помещенных в объект.
В-четвертых, есть возможность быстро отсортировать элементы как по возрастанию (метод Sort), так и по убыванию (сначала используем метод Sort, а после метод Reverse).

Ну и быстро пробегаем по оставшимся свойствам:

Item(Index)
Предоставляет доступ к элементу по его индексу.

и методам:

IndexOf(Item, StartFrom)
Возвращает индекс элемента. Обязательный аргумент StartFrom поможет найти каждый последующий индекс одинаковых элементов.

RemoveAt(Index)
Удаляет элемент по индексу.

Remove(Item)
Удаляет переданный элемент.

RemoveRange(StartPosition, Count)
Удаляет диапазон элементов. StartPosition указывает на индекс первого элемента, Count на количество элементов в удаляемом диапазоне.

Clear()
Удаляет все элементы.

Insert(Position, Item)
Добавляет элемент по заданной позиции.

Clone()
Создает копию объекта (по сути создает новый объект, а не возвращает ссылку на текущий).

Как создать это чудо

Создать объект класса ArrayList можно с помощью функции CreateObject:

Dim List As Object
Set List = CreateObject("System. Collections.ArrayList")

или через Tools -> Reference подключить библиотеку mscorlib.dll, а дальше создавать как обычный объект:

Dim List As New ArrayList

Минус и той и другой привязки в том, что интерфейс объекта вы не получите. Причину лично я не знаю, но почему-то VBA в Excel (больше нигде не проверял) не видит свойства и методы этого класса (в поздней привязке их и так нет ни у какого объекта, так как тип переменной

Object, а вот в ранней обычно есть).

Можно, конечно, получить часть интерфейса, объявив переменную с типом IList и уже после этого присвоить ей инстанс ArrayList, но тем самым мы потеряем бОльшую часть функционала, например методы Sort, ToArray, Reverse.

Вернемся в начало

Помните наш пример? Предлагаю решение с новыми знаниями.

Теперь мы добавляем уникальные значения в объект Buffer (ArrayList), перед этим проверяя методом Contains наличие ФИО в списке элементов. По окончанию цикла применяем метод

Sort и Reverse для получения списка по убыванию. Выгружаем результат методом ToArray. Согласитесь на этот раз все гораздо компактней.

Public Sub Main()
    Dim FullNameColumn As Range
    Set FullNameColumn = ActiveSheet.UsedRange.Columns(1) ' Получаем первый столбец.
    Dim DistinctList As Variant
    DistinctList = GetDistinctItems(FullNameColumn) ' Передаем диапазон в функцию.
    Debug.Print Join(DistinctList, vbCrLf) ' Выводим результат.
End Sub
Public Function GetDistinctItems(ByRef Range As Range) As Variant
    Dim Data As Variant: Data = Range.Value ' Преобразуем диапазон в массив.
    Dim Buffer As Object: Set Buffer = CreateObject("System.Collections.ArrayList") ' Создаем объект ArrayList.
    Dim Item
    For Each Item In Data
        If Not Buffer.Contains(Item) Then Buffer.Add Item ' Проверяем наличие элемента и добавляем если отсутствует.
    Next
    Buffer.Sort: Buffer.Reverse ' Сортируем по возрастанию, а потом переворачиваем (по убыванию).  
    GetDistinctItems = Buffer.ToArray() ' Выгружаем в виде массива.
End Function

Итоговый результат

Что в итоге

В итоге мы имеем преимущество перед классом Collection в том, что есть проверка на наличие элемента в списке (без танцев с бубном) и быстрая выгрузка в виде массива (без написания цикла).

Перед классом Dictionary, пожалуй, преимущество в отсутствии необходимости прописывать ключи (если они изначально не нужны).

Ну и оба вышеперечисленных проигрывают в плане сортировки, добавления элементов по индексу и т.д.

В общем и целом, достаточно удобный в применении класс для работы с одномерными массивами. Конечно, получать данные из объекта Range гораздо проще в обычный массив, но если нужно создавать новый (например в цикле), то, как по мне, ArrayList превосходный вариант.

Уже после написания статьи обратил внимание, что мой пример на чистом ПК не работает, появляется automation error -2146232576 при создании объекта ArrayList.

Судя по этому ответу, для работы mscorlib необходимо включить .NET Framework 3.5.

Сделать это можно через Панель управления -> Программы -> Включение или отключение компонентов Windows -> поставить галочку напротив .NET Framework 3.5 (включает .NET 2.0 и 3.0) после чего на ПК скачаются необходимые файлы для работы компонента.

Обязательно после проделанных действий перезагрузить Excel. У меня при установке выдал ошибку. Исправилось выключением Excel и повторным включением компонента.

К слову на моем рабочем ПК таких проблем не было, т.е. данный компонент уже был подключен организацией (или по умолчанию в ранних Windows, не знаю точно).

Спасибо, что прочитали до конца.

Как насчет применения этого класса? Пишите в комментариях!
А также, подписывайтесь на мой телеграмм.

Понимание того, как Excel обрабатывает функции массива

спросил 4 года 8 месяцев назад

Изменено 4 года, 8 месяцев назад

Просмотрено 1к раз

Этот вопрос основан на чужом вопросе, на который я ответил. При желании вы можете найти исходный вопрос здесь. (И, возможно, дать лучший ответ, чем у меня.) Однако все, что вам нужно, чтобы ответить на этот вопрос, прямо здесь.

Справочная информация

Учитывая следующие данные в A1:C5

 +-------------+--------+--------- -+
| Номер счета-фактуры | Тип | Статус |
+-------------+--------+----------+
| А0001 | Кредит | Платный |
| А0002 | Кредит | Не оплачено |
| Б0001 | Дебет | Платный |
| Б0002 | Дебет | Не оплачено |
+-------------+--------+----------+
 

Цель состояла в том, чтобы найти конкретное значение в таблице, используя формулу массива, оценивающую два столбца. То есть дайте мне первые Счет-фактура (столбец A), где Тип — Дебет и Статус — Не оплачено .

Мой первый ответ был попыткой исправить то, что я считал проблемой с условием OP. Я заключаю два условия в оболочку AND следующим образом:

 {=INDEX($A$2:$A$5,
        МАЛЕНЬКИЙ(ЕСЛИ(И($B$2:$B$5 = "Дебет",
                     $C$2:$C$5 = "Не оплачено"),
                 СТРОКА($A$2:$A$5)-СТРОКА($A$2)+1),
              1)
       )}
 

Однако это не сработало.

В итоге я предложил этот ответ, потому что он действительно работает:

 {=ИНДЕКС($A$2:$A$5,
        МАЛЕНЬКИЙ(ЕСЛИ($B$2:$B$5 & $C$2:$C$5 = "Дебет не оплачен",
                 СТРОКА($A$2:$A$5)-СТРОКА($A$2)+1),
              1)
       )}
 

Мой вопрос

Формулы массива в Excel иногда кажутся мне шаманством. кажется , как будто любой из них должен дать результат B0002 . На самом деле только вторая формула дает желаемый результат. Какому принципу или процессу оценки следует Excel (2013), что делает это возможным? Или, другими словами, что я не понимаю в том, как Excel управляет формулами массива?

• excel
• excel-формула

6

@StoneGiant прав. Есть функции, которые нельзя использовать внутри формул массива, потому что их единственная «причина существования» — превратить массив в скалярное значение. И иногда вы действительно хотите, чтобы эти функции возвращали одно значение внутри формулы массива, так как алгоритм выполнения формулы узнает, как вы этого хотите.

И() и OR() являются хорошими примерами этого. =И(массив значений) возвращает одно значение. MIN() — еще один пример. Возьмите формулу массива

=СУММ((A1:A10=»Боб»)*(Мин(B1:B10))

МИН() должен взять кучу значений и вернуть наименьшее. Как бы программа MIN() для возврата массива? Где вы решаете, что является наименьшим в каком контексте? Вы не можете. Вот почему это не работает в формулах массива.

Возьмите МАЛЕНЬКИЙ () следующий. Он может вернуть формулу массива, потому что он должен возвращать не только самое низкое значение, но и n-е самое низкое значение. Из-за этого n-го аргумента теперь вы можете вернуть массив

 =МАЛЕНЬКИЙ(A1:A10,{2,3})
 

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

Я сказал И() и OR() — хорошие примеры. MS могла бы запрограммировать их так, чтобы они возвращали массив, подобный комментарию @StoneGiant. Для меня это было бы странным дизайнерским решением, но они могли бы это сделать. Но у них уже есть И и ИЛИ в формулах массива. Умножьте на И и сложите на ИЛИ.

 =МАКС((B2:B5="Дебет)*(C2:C5="Не оплачен")*(СТРОКА(B2:B5))
 

Это вернет номер строки последней строки, которая является дебетовой и не оплаченной. (Ваш пример проблемы хотел первой, но MIN вводит проблемы, которые усложняют этот пример). Умножения между равенствами такие же, как И. Если вам нужна последняя строка, которая является дебетовой и где в столбце C указано «Не оплачено» или «Не уверен», вы можете сделать это с добавлением.

 =МАКС((B2:B5="Дебет)*((C2:C5="Не оплачен")+(C2:C5="Не уверен"))*(СТРОКА(B2:B5))
 

Знак «плюс» — это оператор ИЛИ между этими двумя наборами скобок. И результаты этого ИЛИ включаются в другие круглые скобки как И (умножаются).

Вы можете поместить их в ИНДЕКС

 =ИНДЕКС(A2:A5, MAX((B2:B5="Дебет)*(C2:C5="Не оплачен")*(СТРОКА(B2:B5)))
 

Итак, чтобы ответить на ваш вопрос, я думаю: как правило, функции, целью которых является преобразование набора значений в одно значение, не работают внутри формул массива. AND() и OR() несколько особенные, потому что сложение и вычитание уже делают это внутри формул массива.

Подробнее о массивах см. здесь http://dailydoseofexcel.com/archives/2004/04/05/anatomy-of-an-array-formula/

6

И и ИЛИ обычно не работают с формулами массива, потому что они возвращают одиночный результат (не массив).

Эта часть вашей формулы:

 И($B$2:$B$5 = "Дебет", $C$2:$C$5 = "Не оплачено")
 

Возвратит одно значение TRUE (не массив из значений TRUE / FALSE ), если все $B$2:$B$5 являются Дебет 9001 3 и , если $C$2:$ C$5 — это все Not Paid (в противном случае будет возвращено FALSE ).

Эта часть вашей формулы возвращает то, что вы хотите (массив из ИСТИНА / FALSE значения):

 $B$2:$B$5 & $C$2:$C$5 = "Дебет не оплачен"
 

Лично мне этот метод не очень нравится, вместо этого я бы сделал что-то вроде:

 ($B$2:$B$5="Дебет")*($C$2:$C$5="Не оплачен")
 

---

ПРИМЕЧАНИЕ

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

Простой пример:

 (A1="ab")*(B1="cd")
 

Возвращает «немного» отличный результат от:

 A1&B1 = "abcd"
 

Поскольку, например, если A1 равно abc , а B1 равно d , верхняя формула возвращает FALSE , а нижняя формула возвращает TRUE .

Но опять же… в зависимости от приложения, возможно, этот — это , что вам нужно. Просто будьте осторожны при объединении строк и сравнении результата по этой причине.

Итог… Как правило, вам следует избегать И и ИЛИ в формулах массива, потому что они не возвращают массивы.

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

Основная причина в том, что в Excel часто есть несколько способов выполнить одну и ту же задачу. Например, в случае вашего примера я бы просто вставил скрытый «вспомогательный столбец» для объединения Введите и Статус слева от столбца Счет № и выполните базовую ВПР в этом диапазоне.

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

Например,

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

Формула массива, включающая несколько ячеек, называется формулой с несколькими ячейками, а формула массива в одной ячейке называется формулой с одной ячейкой.
_{(Источник)}

---

Что касается вашего примерного вопроса, возможно, следующие шаги прольют свет.

Предполагается, что вы уже знакомы с основами использования функций Match / Index для выполнения поиска.

Формула без всех маркеров абсолютной ссылки $ :

 =ИНДЕКС(B3:B6,ПОИСКПОЗ(F3&G3,C3:C6&D3:D6,0))
 

разбивка:

• F3&G3 объединяет (объединяет) тип и статус , которые мы ищем, в одну строку, например ДебетОплачено
  • массив, который мы ищем, это C3:C6&D3:D6 , что-то вроде:
  • C3&D3 и C4&D4 и C5&D5 и C6&D6
  • 0 указывает, что нам нужно точное совпадение. (См. документы для MATCH .)

Часть формулы MATCH отдельно в h4 вернет 3 , поскольку совпадение находится в 3-м подмножестве массива (т. е. в 3-й строке сверху нашего массива). 3-е значение вниз» из столбца Счет № .

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

🖕 Помните, что для ввода формулы массива нужно использовать Ctrl + Shift + Введите .

См. мой другой ответ здесь для получения дополнительных объяснений и примеров ссылок Index/Match и Absolute Relative.

---

Дополнительная информация:

• Служба поддержки Office: Создание формулы массива
• Служба поддержки Office: Рекомендации и примеры формул массива
• Чип Пирсон: Введение в формулы массива
• Поддержка Office: Использовать константы массива в формулах массива
• Документы Office: Функция ПОИСКПОЗ (Excel)
• Документы Office: Функция ИНДЕКС (Excel)
• Переполнение стека: данные строки для определенных заголовков столбцов на основе идентификатора строки
• ExcelJet : ВПР с несколькими критериями
• Блог Microsoft 365: Использование нескольких критериев в формулах поиска Excel

2

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Обязательно, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

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

Функции массива — Руководство пользователя PyXLL

• Функции массива в Python
• Ctrl+Shift+Enter (CSE) Функции массива
• Функции автоматического изменения размера массива
• Функции динамического массива

Любая функция, которая возвращает массив (или диапазон) данных в Excel, называется функцией массива .

В зависимости от того, какую версию Excel вы используете, функции массива вводятся как формула Ctrl+Shift+Enter (CSE) или формула динамического массива. Преимущество формул динамического массива перед формулами CSE состоит в том, что они автоматически изменяют размер в соответствии с размер результата.

Чтобы помочь пользователям более старых версий Excel, результаты функции массива PyXLL могут быть автоматически изменить размер.

#РАЗЛИВ! Ошибка означает, что массив перезапишет другие данные.

Функции массива в Python

Любая функция, представленная в Excel с помощью декоратора xl_func , которая возвращает список значений является функцией массива.

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

 из pyxll импортировать xl_func

@xl_func
определение функция_массива():
    возвращаться [
        [1, 2, 3],
        [4, 5, 6],
        [7, 8, 9]
    ]
 

Необязательная сигнатура функции, переданная в xl_func , может использоваться для указания типа возвращаемого значения. Суффикс [] используется для одномерного массива (столбца), например. float[] и [][] используется для 2d-массив, например. с плавающей запятой[][] .

Например, следующая функция принимает массив 1d (список значений) и возвращает массив значений 2d (список списков):

 из pyxll импортировать xl_func

@xl_func("плавать[]: плавать[][]")
диагональ защиты (v):
    д = []
    для i, x в перечислении (v):
        d. append([x if j == i else 0.0 для j в диапазоне (len(v))])
    вернуть д
 

Массивы NumPy и типы Pandas (DataFrames, Series и т. д.) также могут быть возвращены в виде массивов в Excel, указав соответствующий тип в сигнатуре функции. Дополнительные сведения см. в разделе Типы массивов NumPy и типы Pandas.

При вводе формулы массива в Excel ее следует вводить как формулу Ctrl+Shift+Enter (CSE), или при использовании динамических массивов или функции автоматического изменения размера массива PyXLL они могут вводится так же, как и любая другая формула.

Ctrl+Shift+Enter (CSE) Функции массива

Ctrl+Shift+Enter или CSE формулы — это то, что Excel использовал для статического массива формулы в версиях Excel до появления динамических массивов. добавлен. PyXLL имеет функцию автоматического определения размера массива, которая может эмулировать динамические массивы в более ранних версиях Excel, которые не реализуют их.

Чтобы ввести формулу массива в Excel, вы должны сделать следующее:

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

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

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

Функции автоматического изменения размера массива

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

PyXLL может автоматически изменять размер функций массива в соответствии с результатом. Чтобы включить эту функцию, вы просто добавьте auto_resize=True к параметрам, переданным в xl_func . Например:

 из pyxll импортировать xl_func

@xl_func("float[]: float[][]", auto_resize=True)
диагональ защиты (v):
    д = []
    для i, x в перечислении (v):
        d.append([x if j == i else 0.0 для j в диапазоне (len(v))])
    вернуть д
 

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

 [PYXLL]
;
; Автоматическое изменение размера всех функций массива
;
auto_resize_arrays = 1
 

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

Предупреждение

Автоматическое изменение размера недоступно для функций RTD. Если вы возвращаете массив из функции RTD и вам нужно изменить его размер, вы можете использовать ref: Dynamic Arrays в Excel, начиная с Excel 2016 и далее.

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

Функции динамического массива

Динамические массивы были анонсированы как новая функция Excel в конце 2018 г. Эта функция будет развернута в Office 365 с начала 2019 года. Если вы не используете Office 365, ожидается, что динамические массивы будут доступны в Excel 2022.

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

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

Если вы используете собственную функцию автоматического изменения размера PyXLL, PyXLL обнаружит, доступны ли динамические массивы Excel и есть ли они будет использовать их вместо собственного изменения размера. Это означает, что вы можете напишите код для работы в старых версиях Excel, которые рассчитаны на будущее и будут «просто работать» при обновлении до более новой версии Office.

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

 [PYXLL]
;
; Используйте изменение размера вместо динамических массивов
;
allow_auto_resizing_with_dynamic_arrays = 1
 

Динамические массивы — отличная новая функция Excel, предлагающая некоторые преимущества по сравнению с функциями CSE и Функция автоматического изменения размера PyXLL:

Характеристика	Преимущество
Собственный для Excel	Динамические массивы глубоко интегрированы в Excel, поэтому массив изменение размера работает со всеми функциями массива, а не только с теми, которые написаны с помощью PyXLL. Добавить комментарий Отменить ответ Ваш адрес email не будет опубликован. Обязательные поля помечены * Комментарий * Имя * Email * Сайт