Как вызвать мультитексную команду в Excel

Обновлено: 21.11.2024

Microsoft Office Excel 2007 был первой версией Excel, в которой использовался многопоточный пересчет (MTR) рабочих листов. Вы можете настроить Excel на использование до 1024 одновременных потоков при пересчете, независимо от количества процессоров или процессорных ядер на компьютере.

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

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

Обзор MTR Excel

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

Рисунок 1. Параллельное вычисление в разных потоках

После вычисления A1 можно вычислить A2, а затем A3 в одном потоке, а B1, а затем C1 можно вычислить в другом, предполагая, что все ячейки потокобезопасны.

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

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

Excel использует один основной поток для выполнения следующих действий:

Функции интерфейса диспетчера надстроек XLL (функция xlAutoOpen и т. д.)

Определяемые пользователем команды Microsoft Visual Basic для приложений (VBA) (часто называемые макросами)

Определяемые пользователем функции VBA

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

Определяемые пользователем команды и функции листа макросов XLM

Команды и функции надстройки COM

Функции и операторы в выражениях условного форматирования

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

Принудительное вычисление выражения в поле редактирования формулы с помощью клавиши F9

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

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

Что такое потокобезопасность в Excel, а что нет

Excel считает потокобезопасным только следующее:

Все унарные и бинарные операторы в Excel.

Почти все встроенные функции рабочего листа, начиная с Excel 2007 (см. список исключений)

Функции надстройки XLL, явно зарегистрированные как потокобезопасные.

ФОНЕТИЧЕСКИЙ

ЯЧЕЙКА, когда используется аргумент "формат" или "адрес"

НЕПРЯМОЙ

ПОЛУЧИТЬ ДАННЫЕ

КУБЭЛЕМЕНТ

КУБЗНАЧЕНИЕ

СОБСТВЕННОСТЬ КУБЭЛЕМЕНТА

НАБОР КУБОВ

CUBERRANKEDEMBER

КУБЭЛЕКТРЭЛЕКТРОННЫЙЭЛЕМЕНТ

КУБЧИСЛО

АДРЕС, где указан пятый параметр (имя_листа)

Любая функция базы данных (DSUM, DAVERAGE и т. д.), которая ссылается на сводную таблицу

ОШИБКА.ТИП

ГИПЕРССЫЛКА

Если быть точным, следующие элементы считаются небезопасными:

Определяемые пользователем функции VBA

Определяемые пользователем функции надстройки COM

Определяемые пользователем функции листа макросов XLM

Функции надстройки XLL, явно не зарегистрированные как потокобезопасные

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

Вызовы информационных функций XLM, например, xlfGetCell (GET.CELL).

Вызовы xlfSetName (SET.NAME) для определения или удаления внутренних имен XLL.

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

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

Вызовы функции xlAbort для сброса условия прерывания.

Вызовы функции xlCoerce для получения значения невычисленной ссылки на ячейку.

Функции рабочего листа XLL не могут вызывать команды API C, например xlcSave, независимо от того, зарегистрированы ли они как потокобезопасные или нет.

Учитывая, что функции XLL, объявленные как потокобезопасные, не могут вызывать информационные функции XLM или ссылаться на невычисленные ячейки, Excel не разрешает регистрировать функции XLL, которые зарегистрированы как эквиваленты листов макросов, также как потокобезопасные. Поэтому попытка получить значение невычисленной ссылки на ячейку с помощью xlCoerce завершается с ошибкой xlretUncalced. При вызове информационной функции XLM возникает ошибка xlretFailed. Другие пункты, перечисленные ранее, завершаются ошибкой с кодом ошибки, представленным в API Excel C: xlretNotThreadSafe.

Все функции обратного вызова только для C API являются потокобезопасными:

xlCoerce (кроме случаев, когда приведение невычисленных ссылок на ячейки не удается)

xlБесплатно

xlстек

кслшитид

кслшитнм

xlAbort (кроме случаев, когда используется для сброса условия прерывания)

кслжетинст

xlGetHwnd

xlGetBinaryName

кслдефинебинаринаме

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

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

Регистрация функций XLL как потокобезопасных

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

Не вызывайте ресурсы в других библиотеках DLL, которые могут быть небезопасными для потоков.

Не выполняйте небезопасные для потоков вызовы через C API или COM.

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

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

Excel накладывает дополнительное ограничение: потокобезопасные функции не могут быть зарегистрированы как эквиваленты листа макросов и, следовательно, не могут вызывать информационные функции XLM или получать значения непересчитанных ячеек.

Конфликт за память

Многопоточные системы должны решать две основные проблемы:

Как защитить память, которая должна быть прочитана или записана более чем одним потоком.

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

Операционная система Windows и Windows Software Development Kit (SDK) предоставляют инструменты для обеих этих задач: критических разделов и API локального хранилища потоков (TLS) соответственно. Дополнительные сведения см. в разделе Управление памятью в Excel.

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

Вторая проблема может возникнуть, например, когда функция рабочего листа объявляет статическую переменную или объект в коде тела функции. Компилятор C/C++ создает только одну копию, которую используют все потоки. Это означает, что один экземпляр функции может изменить значение, в то время как другой экземпляр в другом потоке может предположить, что значение является тем, которое было установлено ранее.

Примеры применения MTR

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

В частности, MTR оказывает огромное влияние на время пересчета книг, которые вызывают пользовательские функции (UDF), которые сами вызывают внешние процессы для получения желаемого результата. В частности, рассмотрим пользовательскую функцию, которая вызывает удаленный сервер, который может обрабатывать множество запросов одновременно, и рабочую книгу, содержащую множество вызовов этой функции. Если пересчет рабочей книги является однопоточным, каждый вызов пользовательской функции и, следовательно, удаленному серверу должен завершиться, прежде чем можно будет сделать следующий. Это лишает сервер способности обрабатывать множество вызовов одновременно. Если пересчет книги выполняется в несколько потоков, Excel может выполнять несколько вызовов одновременно или в быстрой последовательности.

Если Excel настроен на использование того же количества потоков, что и сервер (назовем его N), и это позволяет топология дерева зависимостей книги, общее время пересчета может быть сокращено примерно до 1/N время однопоточного расчета.Это может быть правдой, даже если клиентский компьютер (на котором работает книга) имеет только один процессор, особенно когда время, необходимое для выполнения вызова к серверу, мало по сравнению со временем, которое требуется серверу для обработки вызова.< /p>

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

Например, рассмотрим однопроцессорный компьютер, на котором работает Excel, и книгу, содержащую 1000 ячеек. Он вызывает UDF, который, в свою очередь, вызывает один или несколько удаленных серверов. Предположим, что 1000 ячеек не зависят друг от друга, поэтому Excel не нужно ждать завершения одного вызова перед вызовом следующего. (Некоторое ослабление этого ограничения возможно, не влияя на этот пример.) Если серверы могут обрабатывать 100 запросов одновременно, а Excel настроен на использование 100 потоков, время выполнения может быть сокращено до 1/100 того времени, когда только один используется нить. Накладные расходы, связанные с тем, что Excel распределяет вызовы по каждому потоку, а операционная система управляет 100 потоками, означают, что на практике сокращение не будет столь значительным. Здесь также неявно предполагается, что сервер хорошо масштабируется, и одновременная обработка 100 задач не окажет существенного влияния на время выполнения отдельных задач.

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

Примечания

Хотя Excel позволяет вкладывать до 64 различных функций ЕСЛИ, делать это совсем не рекомендуется. Почему?

Множественные операторы IF требуют тщательного обдумывания, чтобы правильно построить и убедиться, что их логика может правильно вычислять каждое условие до конца. Если вы не вложите свою формулу на 100 % точно, она может работать в 75 % случаев, но возвращать неожиданные результаты в 25 % случаев. К сожалению, шансы на то, что вы попадете в 25 %, невелики.

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

Если вы обнаружите, что оператор ЕСЛИ продолжает расти без конца, пора отложить мышь и пересмотреть свою стратегию.

Давайте посмотрим, как правильно создать сложный вложенный оператор ЕСЛИ, используя несколько операторов ЕСЛИ, и когда понять, что пришло время использовать другой инструмент в вашем арсенале Excel.

Примеры

Ниже приведен пример относительно стандартного вложенного оператора IF для преобразования результатов тестов учащихся в эквивалентные им буквенные оценки.

Этот сложный вложенный оператор IF следует простой логике:

Если результат теста (в ячейке D2) больше 89, учащийся получает пятерку

Если результат теста выше 79, учащийся получает B

Если результат теста выше 69, учащийся получает оценку C

Если результат теста выше 59, учащийся получает оценку D

В противном случае учащийся получает F

Этот конкретный пример относительно безопасен, потому что маловероятно, что корреляция между результатами тестов и буквенными оценками изменится, поэтому он не потребует особого обслуживания. Но вот мысль — что, если вам нужно сегментировать оценки между A+, A и A- (и так далее)? Теперь нужно переписать оператор IF с четырьмя условиями, чтобы он содержал 12 условий! Вот как теперь будет выглядеть ваша формула:

Он по-прежнему функционально точен и будет работать так, как ожидалось, но на написание и тестирование уходит много времени, чтобы убедиться, что он работает так, как вы хотите. Еще одна вопиющая проблема заключается в том, что вам приходилось вводить баллы и эквивалентные буквенные оценки вручную. Какова вероятность того, что вы случайно сделаете опечатку? А теперь представьте, что вы пытаетесь сделать это 64 раза с более сложными условиями! Конечно, это возможно, но вы действительно хотите подвергать себя таким усилиям и вероятным ошибкам, которые будет очень трудно обнаружить?

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

Дополнительные примеры

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

Эта формула гласит: ЕСЛИ(C9 больше 15 000, возвращается 20 %, ЕСЛИ(C9 больше 12 500, возвращается 17,5 % и т. д.

Несмотря на то, что эта формула очень похожа на предыдущий пример с оценками, она является отличным примером того, насколько сложно поддерживать большие операторы ЕСЛИ. Что вам нужно будет сделать, если ваша организация решит добавить новые уровни вознаграждения и, возможно, даже изменить существующие долларовые или процентные значения? У вас будет много работы!

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

Вот пример сценария комиссии с нарушенной логикой:

Вы видите, что не так? Сравните порядок сравнения Revenue с предыдущим примером. В какую сторону идет этот? Правильно, она идет снизу вверх (от 5000 до 15000 долларов), а не наоборот. Но почему это должно быть таким большим делом? Это большое дело, потому что формула не может пройти первую оценку для любого значения, превышающего 5000 долларов. Допустим, у вас есть доход в размере 12 500 долларов – оператор IF вернет 10 %, потому что он больше 5 000 долларов, и на этом он остановится. Это может быть невероятно проблематично, потому что во многих ситуациях эти типы ошибок остаются незамеченными, пока они не окажут негативного влияния. Итак, зная, что сложные вложенные операторы IF сопряжены с некоторыми серьезными ловушками, что вы можете сделать? В большинстве случаев можно использовать функцию ВПР вместо построения сложной формулы с помощью функции ЕСЛИ. Используя функцию ВПР, сначала необходимо создать справочную таблицу:

Эта формула говорит, что нужно искать значение в ячейке C2 в диапазоне C5:C17. Если значение найдено, вернуть соответствующее значение из той же строки в столбце D.

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

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

ВПР рассматривается здесь более подробно, но это намного проще, чем 12-уровневый сложный вложенный оператор ЕСЛИ! Есть и другие менее очевидные преимущества:

Справочные таблицы ВПР находятся в открытом доступе, и их легко увидеть.

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

Если вы не хотите, чтобы люди видели вашу справочную таблицу или мешали ей, просто поместите ее на другой рабочий лист.

Знаете ли вы?

Появилась функция IFS, которая может заменить несколько вложенных операторов IF одной функцией. Поэтому вместо нашего примера с начальными оценками, в котором есть 4 вложенные функции ЕСЛИ:

Это можно сделать намного проще с помощью одной функции IFS:

Функция IFS великолепна, потому что вам не нужно беспокоиться обо всех этих операторах IF и скобках.

Примечание. Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

Нужна дополнительная помощь?

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

Если вы хотите найти значение в таблице по одному критерию, это очень просто. Вы можете использовать простую формулу ВПР. Но если вы хотите использовать более одного критерия, что вы можете сделать? Существует множество способов использования нескольких функций Excel, таких как ВПР, ПРОСМОТР, ПОИСКПОЗ, ИНДЕКС и т. д. В этой записи блога я покажу вам несколько таких способов.

Майкрософт Эксель

Превратите данные в идеи.

Использование двух критериев для возврата значения из таблицы

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

Критериями являются "Имя" и "Продукт", и вы хотите, чтобы они возвращали значение "Кол-во" в ячейке C18. Поскольку значение, которое вы хотите вернуть, является числом, вы можете использовать простую формулу СУММПРОИЗВ(), чтобы найти Имя «Джеймс Аткинсон» и Продукт «Пакет молока», чтобы вернуть количество. Формула СУММПРОИЗВ в ячейке C18 выглядит следующим образом:

=СУММПРОИЗВ((B3:B13=C16)*(C3:C13=C17)*(D3:D13))

Он ищет в диапазоне B3:B13 значение в ячейке C16 и в диапазоне C3:C13 значение в ячейке C17. Когда он находит оба, он возвращает значение в столбце D из той же строки, где он соответствует обоим критериям. Вот как это будет выглядеть:

Он возвращает значение 1, которое соответствует значению в ячейке D4 ("Джеймс Аткинсон" в строке 4, а также "Молочный пакет" в той же строке), таким образом, возвращая значение в столбце D из этой строки. Давайте изменим значение в ячейке C5 с «Бутылка вина» на «Пакет молока», чтобы посмотреть, что произойдет с формулой в ячейке C18:

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

Этот метод нельзя использовать, если вы хотите найти два критерия и вернуть текстовый результат. Например, это не сработает:

=ИНДЕКС(C3:C13,СУММПРОИЗВ((B3:B13=C16)*(D3:D13=C18)*СТРОКА(C3:C13)),0)

Вы используете функцию СУММПРОИЗВ, чтобы найти строку, в которой выполняются оба критерия, и возвращаете соответствующий номер строки, используя функцию СТРОКА. Затем вы используете СУММПРОИЗВ в функции ИНДЕКС, чтобы вернуть значение в массиве C3:C13, которое находится в предоставленном номере строки. Результат будет таким:

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

=ПРОСМОТР(2,1/(B3:B13=C16)/(D3:D13=C18),(C3:C13))

Вы также можете использовать формулу массива с помощью функции ПОИСКПОЗ, например:

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

Использование нескольких критериев для возврата значения из таблицы

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

=СУММПРОИЗВ((B3:B13=C16)*(C3:C13=C17)*(E3:E13=C18)*(D3:D13))

=ИНДЕКС(C3:C13,СУММПРОИЗВ((B3:B13=C16)*(D3:D13=C18)*(E3:E13=C18)*СТРОКА(C3:C13)),0)

=ПРОСМОТР(2,1/(B3:B13=C16)/(D3:D13=C18)/(E3:E13=C18),(C3:C13))

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

Используйте большинство функций рабочего листа Microsoft Excel в своих операторах Visual Basic. Список функций рабочего листа, которые вы можете использовать, см. в разделе Список функций рабочего листа, доступных для Visual Basic.

Некоторые функции рабочего листа бесполезны в Visual Basic. Например, функция Concatenate не нужна, поскольку в Visual Basic вы можете использовать оператор & для объединения нескольких текстовых значений.

Вызов функции листа из Visual Basic

В Visual Basic функции рабочего листа Excel доступны через объект WorksheetFunction.

Следующая подпроцедура использует функцию рабочего листа Min для определения наименьшего значения в диапазоне ячеек. Сначала переменная myRange объявляется как объект Range, а затем ей присваивается диапазон A1:C10 на Sheet1.Другой переменной, answer, присваивается результат применения функции Min к myRange. Наконец, значение ответа отображается в окне сообщения.

Если вы используете функцию рабочего листа, для которой в качестве аргумента требуется ссылка на диапазон, вы должны указать объект Range. Например, вы можете использовать функцию листа Match для поиска диапазона ячеек. В ячейке рабочего листа вы должны ввести формулу, например =ПОИСКПОЗ(9,A1:A10,0). Однако в процедуре Visual Basic вы должны указать объект Range, чтобы получить тот же результат.

Функции Visual Basic не используют квалификатор WorksheetFunction. Функция может иметь то же имя, что и функция Microsoft Excel, но работать по-другому. Например, Application.WorksheetFunction.Log и Log вернут разные значения.

Вставка функции листа в ячейку

Чтобы вставить функцию рабочего листа в ячейку, укажите функцию как значение свойства Formula соответствующего объекта Range. В следующем примере функция рабочего листа RAND (генерирующая случайное число) назначается свойству Formula диапазона A1:B3 на листе Sheet1 в активной книге.

Пример

В этом примере используется функция рабочего листа Pmt для расчета платежа по ипотечному кредиту. Обратите внимание, что в этом примере используется метод InputBox вместо функции InputBox, чтобы метод мог выполнять проверку типов. Операторы Static заставляют Visual Basic сохранять значения трех переменных; они отображаются как значения по умолчанию при следующем запуске программы.

См. также

Поддержка и обратная связь

Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.

Читайте также: