Calculating 4 threads excel что это
По умолчанию Excel автоматически пересчитывает все формулы при открытии рабочего листа или при изменении значений, формул или имен в зависимых формулах. Процесс пересчета может длиться от нескольких секунд до нескольких минут в зависимости от количества и сложности формул в рабочей книге.
Пока формулы пересчитываются, указатель мыши меняется на песочные часы, и внести какие-либо изменения на рабочий лист невозможно. Для экономии времени можно временно отключить режим автоматических вычислений в Excel до завершения внесения данных. Сделать это можно без особого труда, и мы покажем Вам как.
Если Вы не хотите отключать режим автоматических вычислений, и Ваш компьютер имеет несколько процессоров, тогда можете применить режим многопоточных вычислений. Он поможет немного ускорить процесс пересчета в зависимости от количества процессоров компьютера. О том, как включить режим многопоточных вычислений в Excel, мы расскажем дальше.
Параметры вычислений
Следующий список поясняет опции, которые доступны в разделе Calculation options (Параметры вычислений):
- Automatic (Автоматически) – пересчитывает все зависимые формулы и обновляет все открытые или внедрённые диаграммы при любом изменении значения, формулы или имени. Данная настройка установлена по умолчанию для каждого нового рабочего листа Excel.
- Automatic except for data tables (Автоматически, кроме таблиц данных) – пересчитывает все зависимые формулы и обновляет все открытые или внедрённые диаграммы, за исключением таблиц данных. Для пересчета таблиц данных, когда данная опция выбрана, воспользуйтесь командой Calculate Now (Пересчет), расположенной на вкладке Formulas (Формулы) или клавишей F9.
- Manual (Вручную) – пересчитывает открытые рабочие листы и обновляет открытые или внедрённые диаграммы только при нажатии команды Calculate Now (Пересчет) или клавиши F9, а так же при использовании комбинации клавиши Ctrl+F9 (только для активного листа).
- Recalculate workbook before saving (Пересчитывать книгу перед сохранением) – пересчитывает открытые рабочие листы и обновляет открытые или внедрённые диаграммы при их сохранении даже при включенной опции Manual (Вручную). Если Вы не хотите, чтобы при каждом сохранении зависимые формулы и диаграммы пересчитывались, просто отключите данную опцию.
- Enable iterative calculation (Включить итеративные вычисления) – разрешает итеративные вычисления, т.е. позволяет задавать предельное количество итераций и относительную погрешность вычислений, когда формулы будут пересчитываться при подборе параметра или при использовании циклических ссылок. Более детальную информацию о подборе параметров и использовании циклических ссылок можно найти в справке Microsoft Excel.
- Maximum Iterations (Предельное число итераций) – определяет максимальное количество итераций (по умолчанию — 100).
- Maximum Change (Относительная погрешность) – устанавливает максимально допустимую разницу между результатами пересчета (по умолчанию — 0.001).
Вы также можете переключаться между тремя основными режимами вычислений, используя команду Calculation Options (Параметры вычислений) в разделе Calculation (Вычисление) на вкладке Formulas (Формулы). Однако, если необходимо настроить параметры вычислений, все же придется обратиться к вкладке Formulas (Формулы) диалогового окна Excel Options (Параметры Excel).
Многопоточные вычисления в Excel
Excel 2013 располагает режимом многопоточных вычислений, который позволяет сокращать время пересчета формул. Если Вы предпочитаете не отключать автоматическое вычисление, попробуйте использовать указанную опцию (при наличии у компьютера нескольких процессоров), чтобы сократить время пересчета.
- Чтобы включить режим многопоточных вычислений, нажмите на вкладку File (Файл) и выберите пункт Options (Параметры), чтобы открыть диалоговое окно Excel Options (Параметры Excel). Затем нажмите Advanced (Дополнительно).
- Опуститесь вниз до раздела Formulas (Формулы) и установите флажок возле пункта Enable multi-threaded calculation (Включить многопоточные вычисления). Вы можете вручную установить количество потоков вычисления, которое необходимо использовать, или указать Excel задействовать все процессоры компьютера, выбрав Use all processors on this computer (Использовать все процессоры данного компьютера).
Если на компьютере уже запущены другие программы, Вы можете ограничить число используемых процессоров для пересчета формул электронной таблицы Excel.
Испытайте разные настройки режимов автоматических и многопоточных вычислений, чтобы определить, какие из них лучше подходят для Вашего компьютера.
Многопотоковый пересчет листов впервые появился в Microsoft Office Excel 2007. Вы можете настроить до 1024 одновременных потоков, которые Excel будет использовать при пересчете, независимо от количества процессоров или ядер на компьютере.
С каждым потоком связаны затраты ресурсов операционной системы, поэтому не настраивайте больше потоков, чем нужно.
Если компьютер имеет несколько процессоров или ядер, за эффективное распределение потоков между процессорами отвечает операционная система.
Обзор многопотокового пересчета листов в Excel
Excel пытается определить части цепочки вычисления, которые можно пересчитывать одновременно в разных потоках. Ниже в качестве примера приведено очень простое дерево (где "x ← y" означает, что y зависит только от x).
Рисунок 1. Параллельные вычисления в разных потоках
Когда выполнено вычисление для ячейки A1, можно последовательно выполнить вычисление для ячеек A2 и A3 в одном потоке, в то время как в другом потоке последовательно выполняются вычисления для B1 и C1. Это возможно при условии, что все ячейки потокобезопасны.
Термин "Потокобезопасная ячейка" обозначает ячейку, содержащую только потокобезопасные функции. Список потоконебезопасных елементов см. в Какие элементы в Excel считаются потокобезопасными, а какие — нет.
Большинство используемых на практике книг содержат гораздо более сложные деревья зависимостей, чем этот пример. Кроме того, время пересчета ячейки не известно до завершения вычисления и может варьироваться в широких пределах в зависимости от аргументов функции. Чтобы получить наилучшие результаты, Excel пытается улучшить порядок вычисления после каждого вычисления, пока это возможно.
Excel использует один основной поток, чтобы запускать или выполнять:
функции интерфейса диспетчера надстроек XLL (функция xlAutoOpen и т. д.)
пользовательские команды Microsoft Visual Basic для приложений (Microsoft Visual Basic for Applications, VBA), часто именуемые макросами;
пользовательские функции VBA;
встроенные потоконебезопасные функции листа (см. список в следующем разделе);
пользовательские команды и функции листа макросов XLM;
функции и команды надстроек COM;
функции и операторов в выражениях условного форматирования;
функции и операторы в определениях определенных имен, используемых в формулах листа;
принудительную оценку выражения в поле редактирования формулы с помощью клавиши F9.
Вычисления по всем формулам листа, независимо от того, потокобезопасны функции или нет, выполняются в основном потоке, если не настроено использование нескольких потоков в Excel. Когда пользователь указывает, что следует использовать несколько потоков, дополнительные потоки используются для потокобезопасных ячеек. Обратите внимание, что основной поток также может использоваться для потокобезопасных ячеек, когда это целесообразно для балансировки нагрузки.
Стоит отметить, что Excel не выполняет более одной команды за раз, поэтому необязательно применять те же меры предосторожности, что и при написании потокобезопасных функций, например использовать локальную память потока и критические секции.
Какие элементы в Excel считаются потокобезопасными, а какие — нет
Excel считает покобезопасными только следующие элементы:
Все унарные и двоичные операторы в Excel.
Почти все встроенные функции листа, начиная с Excel 2007 (см. список исключений).
Потоконебезопасные встроенные функции листа:
PHONETIC
CELL, когда используется аргумент "format" или "address"
INDIRECT
GETPIVOTDATA
CUBEMEMBER
CUBEVALUE
CUBEMEMBERPROPERTY
CUBESET
CUBERANKEDMEMBER
CUBEKPIMEMBER
CUBESETCOUNT
ADDRESS, когда указывается пятый параметр (sheet_name)
Любая функция базы данных (DSUM, DAVERAGE, и т. д.), которая ссылается на сводную таблицу
ERROR.TYPE
HYPERLINK
Указанные ниже действия считаются небезопасными:
пользовательские функции VBA;
пользовательские функции надстроек COM;
пользовательские функции листа макросов XLM;
Вызов информационных функций XLM, например xlfGetCell (GET.CELL).
Вызов xlfSetName (SET.NAME) для определения или удаления внутренних имен XLL.
Вызов потоконебезопасных пользовательских функций с помощью xlUDF.
Вызов функции xlfEvaluate для выражений, содержащих потоконебезопасные функции или определенные имена, определения которых содержат потоконебезопасные функции.
Вызов функции xlAbort для сброса условия останова.
Вызов функции xlCoerce для получения значения невычисленной ссылки на ячейку.
Функции обратного вызова для C API — потокобезопасные:
xlCoerce (кроме случаев, когда не удается выполнить приведение невычисленных ссылок на ячейки)
xlFree
xlStack
xlSheetId
xlSheetNm
xlAbort (кроме случаев сброса состояния останова)
xlGetInst
xlGetHwnd
xlGetBinaryName
xlDefineBinaryName
Единственное исключение — функция xlSet, которая является эквивалентом команды и, поэтому, не может вызываться из функций листа.
Ниже приведены правила, которые необходимо соблюдать разработчикам при создании потокобезопасных функций.
Не вызывайте ресурсы в других библиотеках DLL, которые могут быть потоконебезопасными.
Не осуществляйте потоконебезопасные вызовы с помощью C API или COM.
Защищайте ресурсы, которые могут использоваться одновременно несколькими потоками, с помощью критических секций.
Используйте локальную память потока для хранения данных потока и заменяйте статические переменные в функциях локальными переменными потока.
В Excel действует дополнительное ограничение: потокобезопасные функции невозможно зарегистрировать как эквивалентные функциям листа макросов, поэтому они не могут вызывать информационные функции XLM и получать значения непересчитанных ячеек.
Состязание за память
Многопоковые системы должны решать две основные проблемы:
Как защитить память, с которой выполняется чтение или на которую выполняется запись, с помощью нескольких потоков.
Как создать память, связанную с выполняемым потоком и доступную только ему, и получить к ней доступ.
В операционной системе Windows и пакете средств разработки программного обеспечения (SDK) для Windows есть средства для их решения: критические секции и API для локального хранения данных потока (TLS) соответственно. Дополнительные сведения см. в статье Memory Management in Excel.
Первая проблема может возникнуть, например, когда двум функциям листа (или двум параллельно выполняемым экземплярам одной функции) нужен доступ к глобальной переменной в проекте DLL (например, для ее изменения). Помните, что эта переменная может быть скрыта в глобально доступном экземпляре объекта класса.
Вторая проблема может возникнуть, например, когда функция листа объявляет статическую переменную или объект в коде функции. Компилятор C/C++ создает только одну копию, которую используют все потоки. Это означает, что один экземпляр функции может изменить значение, а другой (в другом потоке) может использовать ранее заданное значение.
Примеры применения MTR
Любой XLL-модуль, который экспортирует функции листа, может использовать многопотоковый пересчет (MTR) в Excel, если эти функции не должны выполнять потоконебезопасные действия. Это позволяет Excel максимально быстро выполнять пересчет в книгах, в которых они используются, и поэтому MTR рекомендуется применять всегда.
В частности, MTR имеет большое влияние на время пересчета книг, которые вызывают пользовательские функции (UDF), которые в свою очередь вызывают внешние процессы для получения желаемых результатов. Рассмотрим функцию UDF, вызывающую удаленный сервер, который может обрабатывать большое количество запросов одновременно, и книгу, содержащую большое количество вызовов такой функции. Если пересчет книги однопотоковый, следующий вызов UDF и удаленного сервера не может начаться, пока не завершится предыдущий. В результате возможность сервера обрабатывать сразу несколько вызовов не используется. Если пересчет книги многопотоковый, Excel может совершать несколько вызовов одновременно или в быстрой последовательности.
Если в Excel и на сервере настроено использование одинакового количества потоков (N), при этом топология дерева зависимостей книги позволяет это, общее время пересчета можно сократить до значения, которое стремится к 1/N. Это возможно, даже если у клиентского компьютера (на котором обрабатывается книга) всего один процессор, особенно если время вызова сервера невелико по сравнению с временем обработки вызова сервером.
С каждым дополнительным потоком связаны затраты ресурсов операционной системы. Поэтому оптимальное количество потоков, которое должно использовать приложение Excel, для каждой книги, сервера и клиентского компьютера определяется опытным путем.
Рассмотрим компьютер с одним процессором, на котором запущено приложение Excel и обрабатывается книга, содержащая 1000 ячеек. Она вызывает функцию UDF, которая, в свою очередь, вызывает один или несколько удаленных серверов. Предположим, что 1000 ячеек не зависят друг от друга, поэтому Excel не нужно ожидать завершения одного вызова для совершения другого. (Это условие можно нарушить без последствий для этого примера.) Если серверы могут обрабатывать 100 запросов одновременно, а в Excel настроено использование 100 потоков, время выполнения можно сократить до 1/100 (сотой части от времени выполнения однопотокового пересчета). Чтобы приложение Excel могло распределять вызовы между потоками, а операционная система могла управлять 100 потоками, требуются значительные ресурсы. Это показывает, что на практике такого значительного сокращения времени не будет. Мы также предполагаем, что сервер характеризуется хорошей масштабируемостью, и что одновременная обработка 100 задач сильно не повлияет на время выполнения отдельных задач.
Пример практического применения, при котором этот способ дает отличные результаты, — использование методов Монте-Карло, а также выполнение других ресурсоемких задач, которые можно разделить на более мелкие подзадачи и обработать на серверах.
Рекомендации по использованию служб Excel
Службы Excel поддерживают загрузку, вычисление и обработку электронных таблиц Excel на сервере. Пользователи могут получить доступ к электронным таблицам и работать с ними, используя стандартные средства браузера.
Registered User Join Date 12-02-2021 Location Cardiff, Wales MS-Off Ver O365 Posts 6
"Calculating 4 threads" issue with tracker
Hope you're all well!
I am creating a "learning tracker" which pulls information from a "master sheet" and splits the data depending on a few variables to 6 separate sheets.
I am having issues with the "calculating 4 threads" message appearing at the bottom, where any change to the master sheet is met with a minute or two of loading.
I've seen some previous threads here that mention the "OFFSET" function, which could potentially be one of the issues, though I disabled the formulas with the OFFSET function, and i was still getting the calculating 4 threads message.
Here is an example of some of the formula in the sheets. I'm sure there's probably more efficient ways to work this, but my mind is at a blank:
This is the main bulk of the formula:
=IFERROR(INDEX('Master Sheet'!$B$2:$B$1500, SMALL(IF($A$1='Master Sheet'!$A$2:$A$1500, ROW('Master Sheet'!$A$2:$A$1500)-ROW($A$2)+1), ROW(1:1))),"" )
This is the formula looking at what date the learning started, and to produce the estimated stage 2 start date:
Any help would be greatly appreciated.
Thanks in advanced
Forum Guru Join Date 04-13-2005 Location North America MS-Off Ver 2002/XP and 2007 Posts 14,203
Difficult to give specific suggestions based on a couple of formulas without seeing those formulas in context.
In the first formula, I see a SMALL(. ) calculation involving an array of 1500 values. Every copy of this function will need to repeat this calculation, probably resulting in a lot of duplicated effort. One strategy I could see to improving calculation efficiency is to nail down exactly what this array function is doing and see if there is a more efficient way of accomplishing the calculation (perhaps involving helper cells/columns/rows).
The second formula is performing an exact match, linear lookup, which are notoriously slow. Improving this bottleneck usually involves coming up with a more efficient way of performing the lookup (if you can sort the lookup table by the first column, then you can use an approximate match binary search algorithm (4th argument is TRUE), which is much faster).
Can you modify your sheet and or formulas to reduce any duplicated effort or use more efficient lookup algorithms?
Originally Posted by shg
Registered User Join Date 12-02-2021 Location Cardiff, Wales MS-Off Ver O365 Posts 6
In the first formula, I see a SMALL(. ) calculation involving an array of 1500 values. Every copy of this function will need to repeat this calculation, probably resulting in a lot of duplicated effort. One strategy I could see to improving calculation efficiency is to nail down exactly what this array function is doing and see if there is a more efficient way of accomplishing the calculation (perhaps involving helper cells/columns/rows).
Thanks for responding.
So basically, the formula is looking for a business area in the master sheet, and then paste the required rows and columns. However, this formula is in each cell of the subsidiary tabs.
This equates to around 15,500 cells in each of the subsidiary 6 tabs - so around 93,000 calculations I assume? So I assume that is why it's taking so long to calculate?
Could you provide a little more information on helper cells/rows/columns please? This isn't something I've looked into before.
The second formula is only in the master sheet in a single column, but over 1500 rows.
What this is doing is seeing the date someone started their learning, then calculating the estimated date someone will join "stage 2".
However, stage 1 could start any day of the week, stage 2 has to start on a Monday.
This will be 9 weeks (and in some cases a few extra days apart.)
With Excel 2010+ you can use AGGREGATE instead of SMALL-IF. It's a non-array formula and is faster. If you included COUNTIF to retun a blank when the number of matching values is exceeded, that would help a lot, too.
Faster still with O365, but you don't use that one yet.
So, Please read the yellow banner (top) and post a small sample sheet.
I have ~340k rows of data across four columns--all static (not-formulas). In addition to those four columns, I have a CONCATENATE, two VLOOKUPS, and two ADD-SUBTRACT columns. It takes me about a minute to apply a filter/unfilter, or any sort of manipulation. Is there a way to speed up this process? This issue applies even if I have the data on one worksheet in one workbook, data value'd out.
Thanks in advance!
Some things I've used with large files, 1) if possible make it a binary worksheet, 2) I find index matches are quicker than lookups, 3) have the data on the left and calcs on the right. Also are you running 64bit Windows? If not I think it only uses 2 Gb max
this omg this above all else
I think I will revert back to Index/Match. I'm using 32-bit Excel, the file size is 108MB.
You start using Access.
You are right in many ways.
Disable calculations on the spreadsheet until you need updated values.
Also, in the VBA Immediate window type: Application.ScreenUpdating = False and hit enter.
Then set it to true when it's done. That will save you some time without requiring any formula changes on your part.
This is what OP needs. Any computer running those formulas over that span is going to be too slow under automatic calculation.
Keep the four column dataset in an external file and use Power Query in 2010/2013 or Get & Transform in 2016 to import the data and perform the lookups, concatenation and other calculations. Keep the new query as only a data connection and create a new pivot table using the newly created query connection as the data source. Updating the report with new data is as easy as refreshing the pivot table.
Yes, I currently do this, and do this for everything I possibly can. After Get & Transform (I hate that name) and Power Pivot, there are about 350K rows of data.
The quickest improvement is to turn off automatic calculation of formulas and then just hit F9 when you want to update, but that won't help you with filters.
Calculation time on very large spreadsheets is a technological limit that doesn't have a simple solution. If you're code savvy I recommend using VBA to do your calculations. Then the waiting time will only happen when you fire the macro, not when you're trying to filter.
If you're not experienced enough in VBA to write the code, you might try selecting all your formula rows, copying, and then pasting by value over top them. Just make sure to leave at least one row of formulas so you can paste them back into the cells later when you add new data.
I was wondering if you had an experience or solutions with Excel prompting "Calculating: (4 Processor(s)): %" at the bottom right of the workbook? I created a forecast using SUMIFS function and thousands of rows of data and as a result, its really slow to calculate. I have my calculation in Manual mode and I thought it would help if I put the large amount of data in Access then link it to excel but that didn't work either. See attached image for example.
Any suggestions would be great!
Attachments
David Evans
Active Member
Hunter Childress
Member
Thanks for the tip David. You are correct, I am using the Today() function which might be slowing it down. However, I don't know how to use VBA for todays date as shown in the link you provided. I am using Excel 2013 and my model starts with a row 1/1/2015 and goes to 12/31/2015. I need to use an alternative to the Today function since it seems to be slowing my model down but I dont know how. Can you help? Also, I have formulas stating =if(today() I need to make sure I can implement the alternative solution in my formulas.
I would really appreciate your help on this.
David Evans
Active Member
Hard code the value in a reference cell, and point any formulas that need to check the date to that one reference cell.
Can also use a macro to populate the current date as text into that reference cell when the workbook opens:
Private Sub Workbook_Open()
Range("SomeCell").Value = Date
End Sub
The above is cut from Charles Williams' file, worksheet "Volatile Functions" - =TODAY().
I'd be a little more thorough with my answer but I'm up against my own deadline here . The lads from down under will be along shortly, and they are always helpful, if its all new to you . I hope to be back later when I get home and settled .
Читайте также: