Vba excel calculate
Calculate — пересчет листа
Тема пока не исследована.
Пока заметил лишь разницу в работе Calculate.
Calculate вызванный с функции прописанной на листе — лист пересчитывает.
Calculate, вызванный с функции какого-нибудь модуля (как например ThisWorkbook.Worksheets(Name).Calculate) — зачастую лист не пересчитывает.
Кто-то с таким сталкивался. Можете объяснить разницу?
Добавлено через 2 часа 41 минуту
Примечание:
не ThisWorkbook.Worksheets(Name).Calculate
а ThisWorkbook.Worksheets(dName).Calculate
потому как может показаться, что пересчет может не идти в связи с другим листов (не тем, который требуется, а тем, что активен).
и все-же такая аномальность есть . где и как — пока не понимаю (не вижу закономерности) . и возникает такая штука только при отработке события открытия книги (и то- не всегда и не везде) .
может кто сталкивался .
Событие листа Calculate
Такая проблема. После импорта лист запускает обработку данных событием calculate. При этом он.
Пересчет формул только для 1 листа
Подскажите, пож. Ситуация: программный пересчет объемных данных на листе 1; генерируются.
Единократный пересчет листа в книге xls
Здравствуйте, господа программисты. Подскажите — как с помощью VBScript — в определенной уже.
Пересчет тИЦ и пересчет позиций
Скажите пожалуйста, как по времени соотносятся между собой пересчет тИЦ и пересчет позиций. Это.
StepInLik, похоже, вы первый! На работе не до тонкостей.
Да стараюсь по теме. У нас в коммерческом листы пересчитывают по 10 раз на дню.
Просто хочу представить, как в рабочей обстановке разглядеть такие тонкости.
Задача: При загрузке файла необходимо пересчитать все формулы книги (в книге от 2 до 10 листов).
Ограничение: Перехватывать управление книгой при загрузке до момента полного пересчета — нельзя т.к. процесс отнимет много времени и пользователь останется недоволен .
P.S. Замечено, что пользователь зачастую переходит на какой-то лист и потом подолгу там сидит изучая данные этого листа.
Вывод: как только открылась книга — начать перманентно/прозрачно пересчитывать все формулы книги на всех ее листах — параллельно работе пользователя (DoEvents), а при переходе пользователя на какой-то лист — лочить книгу и принудительно пересчитывать именно этот лист (если он пока не был полностью пересчитан прежним переходом или функцией «параллельго» пересчета) . и после пересчета этого активного листа — продолжать работу по перманентному/прозрачному пересчету всех других листов .
Проблема: Если на событие активации листа вешать принудительный пересчет, то:
— если вызывается функция модуля, которой передается имя этого листа и пересчет вызывается как ThisWorkbook.Worksheets(dName$).Calculate — лист иногда! не пересчитывается (и формулы листа остаются нулями и НД)
— если вызывать ту же самую функцию, только не из модуля, а из листа (который пересчитывается), только в которой будет написано Calculate (как cacl текущего листа) — все всегда четко пересчитывается
пока не могу понять зависимости такого поведения. просто интересно почему так?! (если разберусь — отпишусь конечно)
Night Ranger, и вы туда же — забалтывать!
Я по сути сказать что-то ценное не могу, но просто любопытна сама организация труда и такая забота о работниках!
У нас так всё это на уровне самодеятельности.
Как ускорить и оптимизировать код VBA
- Если в коде есть много всяких Activate и Select , тем более в циклах — следует немедленно от них избавиться. Как это сделать я писал в статье: Select и Activate — зачем нужны и нужны ли?
- Обязательно на время выполнения кода отключить:
- автоматический пересчет формул . Чтобы формулы не пересчитывались при каждой манипуляции на листе во время выполнения кода — это может дико тормозить код, если формул много:
если печать производится внутри кода, то эту строку желательно вставить сразу после строки, выводящей лист на печать(при условии, что печать не происходит в цикле. В этом случае — по завершению цикла печати).
Я советую всегда отключать разбиение на страницы, т.к. это может тормозить весьма значительно, т.к. заставляет при любом изменении на листах обращаться к принтеру и переопределять кол-во и размер печатных страниц. А это порой очень не быстро.
На всякий случай можно отключить отображение информации в строке статуса Excel (в каких случаях там вообще отображается информация и зачем можно узнать в статье: Отобразить процесс выполнения). Хоть это и не сильно поедает ресурсы — иногда может все же ускорить работу кода:
Главное, что следует помнить — все эти свойства необходимо включить обратно после работы кода . Иначе могут быть проблемы с работой внутри Excel. Например, если забыть включить автопересчет формул — большинство формул будут пересчитывать исключительно принудительным методом — Shift+F9. А если забыть отключить обновление экрана — то есть шанс заблокировать себе возможность работы на листах и книгах. Хотя по умолчанию свойство ScreenUpdating и должно возвращаться в True, если было отключено внутри процедуры — лучше не надеяться на это и привыкать возвращать все свойства на свои места принудительно. По сути все это сведется к нескольким строкам:
‘Возвращаем обновление экрана Application.ScreenUpdating = True ‘Возвращаем автопересчет формул Application.Calculation = xlCalculationAutomatic ‘Включаем отслеживание событий Application.EnableEvents = True
Как такой код выглядит на практике. Предположим, надо записать в цикле в 10 000 строк значения:
Sub TestOptimize() ‘отключаем обновление экрана Application.ScreenUpdating = False ‘Отключаем автопересчет формул Application.Calculation = xlCalculationManual ‘Отключаем отслеживание событий Application.EnableEvents = False ‘Отключаем разбиение на печатные страницы ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False ‘Непосредственно код заполнения ячеек Dim lr As Long For lr = 1 To 10000 Cells(lr, 1).Value = lr ‘для примера просто пронумеруем строки Next ‘Возвращаем обновление экрана Application.ScreenUpdating = True ‘Возвращаем автопересчет формул Application.Calculation = xlCalculationAutomatic ‘Включаем отслеживание событий Application.EnableEvents = True End Sub
Разрывы печатных страниц можно не возвращать — они тормозят работу в любом случае.
Следует избегать циклов, вроде Do While для поиска последней ячейки . Часто такую ошибку совершают начинающие. Куда эффективнее и быстрее вычислять последнюю ячейку на всем листе или в конкретном столбце без этого тормозного цикла Do While. Я обычно использую
другие варианты определения последней ячейки я детально описывал в статье: Как определить последнюю ячейку на листе через VBA?
Для более опытных пользователей VBA я приведу несколько решений по оптимизации кодов в различных ситуациях:
- Самая хорошая оптимизация кода, если приходится работать с ячейками листа напрямую, обрабатывать их и, возможно, изменять значения, то быстрее все обработки делать в массиве и разом выгружать на листе. Например, код выше по заполнению ячеек номерами будет в этом случае выглядеть так:
Sub TestOptimize_Array() ‘Непосредственно код заполнения ячеек Dim arr, lr As Long ‘запоминаем в массив одним махом все значения 10000 строк первого столбца arr = Cells(1, 1).Resize(10000).Value ‘если нужно заполнение для двух и более столбцов ‘arr = Cells(1, 1).Resize(10000, 2).Value ‘или ‘arr = Range(Cells(1, 1),Cells(10000, 2)).Value ‘или автоматически вычисляем последнюю ячейку и заносим в массив данные, начиная с ячейки А3 ‘llastr = Cells(Rows.Count, 1).End(xlUp).Row ‘последняя ячейка столбца А ‘arr = Range(Cells(3, 1),Cells(llastr, 2)).Value For lr = 1 To 10000 arr(lr,1) = lr ‘заполняем массив порядковыми номерами Next ‘Выгружаем обработанный массив обратно на лист в те же ячейки Cells(1, 1).Resize(10000).Value = arr End Sub
Но здесь следует учитывать и тот момент, что большие массивы могут просто вызвать переполнение памяти. Наиболее актуально это для 32-битных систем, где на VBA и Excel выделяется памяти меньше, чем в 64-битных системах
If s <> s1 Then будет медленнее, чем
If StrComp(s, s1, vbBinaryCompare) = 0
и тем более, если при сравнении необходимо не учитывать регистр:
If LCase(s) <> LCase(s1) Then будет медленнее, чем
If StrComp(s, s1, vbTextCompare) = 0
Dim rRange as Object, wsSh as Object
будет медленнее работать, чем:
Dim rRange as Range, wsSh as Worksheet
Причина в том, что при объявлении As Object мы не даем VBA практически никакой информации о типе данных, кроме того, что это какой-то объект. И VBA приходится «на лету» внутри кода при каждом обращении к такой переменной определять её конкретный тип(Range, Worksheet, Workbook, Chart и т.д.). Что опять же занимает время.
Если работаете с массивами, то можно при объявлении указать это явно:
Такая инициализация происходит быстрее.
А еще лучше будет при этом еще и тип данных сразу присвоить:
Dim arr() as string, arr2() as long
но это только если есть уверенность в том, что в массив будут заноситься строго указанные типы данных
Конечно, это не все приемы и решения для оптимизации. Но на первых парах должно хватить. Плюс, всегда следует исходить из здравого смысла . Например, если код выполняется за 2 секунды, то вероятно нет смысла его дальше оптимизировать. Конечно, если этот код не из тех, которые просто изменяют значение одной-двух ячеек.
Статья помогла? Поделись ссылкой с друзьями!
DAX-Урок 2. Функция CALCULATE
Первая функция, с которой необходимо начинать изучение DAX является CALCULATE. Данная функция не имеет точного аналога в Excel, однако ее можно сравнить с функциями SUMIFS и COUNTIFS. Если коротко, то данная функция выполняет наложение фильтров на другие расчетные функции, тем самым мы можем отбирать и включать в расчеты только нужные нам данные.
Синтаксис функции CALCULATE()
=CALCULATE( , , , …)
Выражение, которое должно нам вернуть число как результат вычисления (значение, а не таблицу). По этой причине в этом параметре часто используются статистические функции типа SUM, MIN, MAX, COUNTROWS и т.д.
- SUM([ НазваниеСтолбца ])
- SUM([ НазваниеСтолбца1 ])/MAX([ НазваниеСтолбца2 ])
- Название другого расчетного поля (меры)
фильтр1 >, > .
Фильтр определяет диапазон данных над которыми нужно работать. Здесь указываются условия отбора значений. Как результат возвращает таблицу.
- [ НазваниеСтолбца ] = » Авто«
- [ НазваниеСтолбца]>= 6
- ALL( ‘НазваниеТаблицы ‘) или ALL( ‘НазваниеТаблицы ‘ [ НазваниеСтолбца])
- FILTER( ‘НазваниеТаблицы’; ‘НазваниеТаблицы ‘[ НазваниеСтолбца] = » Зеленый«)
Предположим мы имеем следующую таблицу, которая называется Demo и загружена в PowerPivot.
Давайте на ее основе создадим сводную таблицу и подсчитаем количество записей для каждого продукта.
Теперь давайте создадим новое расчетное поле (меру) Count_All_Product в нашей сводной таблице, которое будет отображать общее количество записей напротив всех строк.
Каким образом мы получили в расчетном поле Count_All_Product цифры 16? Объяснение в том, что мы сняли для этого поля все фильтры, которые были применены в сводной таблице (каждая строка в сводной таблице это отдельный фильтр), с помощью функции ALL(). То есть, другими словами, функция ALL( Demo ) дает команду снять все фильтры для расчетного поля Count_All_Product, которые применяются к таблице Demo .
Давайте добавим еще одно поле в строки, чтобы видеть какой была продажа каждого товара по городам.
Видим, что цифры в поле Count of Rows меняются, а в расчетном поле Count_All_Product — нет. Все правильно, поскольку для последнего мы сняли все фильтры в формуле. Теперь давайте внесем незначительные изменения в нашу формулу и посмотрим на результат.
=CALCULATE( COUNTROWS( Demo );ALL( Demo [ Product ]) )
Мы изменили аргумент для функции ALL(), заменив Demo на Demo [ Product ]. Теперь фильтры будут сняты не для всей таблицы Demo , а только для ее столбца Demo [ Product ].
Таким образом мы видим, что на поле Count of Rows действуют фильтры Product и City, а на расчетное поле Count_All_Product только фильтр City.
Analyst Cave
Excel VBA Calculate distance between two addresses or coordinates
Ever wanted to calculate the distance between two addresses in Excel? I recently had the following issue: from a list of over approx. 50 administration offices across my city I wanted to find the one that is closest to my workplace. Of course open Google Maps and type each location one by one… and then choose the shortest connection. By why bother when we have Google Maps Distance Matrix API.
To get the COORDINATES of any ADDRESS read this post
Using the Google Maps Distance Matrix API
Google has a lot of useful API out there and I encourage you to go sometime to the Google API Explorer and have a look at what other information you can easily utilize from Excel or your other applications.
Let’s however focus on getting the distance between two addresses. Google facilitates Google Maps Distance Matrix API for limited usage.
The API is configured using GET HTTP Params. A simple example below.
Say we want to get the distance between San Francisco and Victoria BC. We want to get there by bicycle. Copy this link into your browser:
Google Distance between Vancouver, BC, Canada and San Francisco, CA, USA
Google Distance Matrix Builder
Want to quickly test the Google API? Below a simple form for building a quick Distance URL. Simply type in the From an To addresses, select the transportation mode and hit Build URL! . Go here for more options for configuring the Google Maps Distance Matrix API.
| From: |
| To: |
| Mode: |
Google Maps Distance Matrix URL:
Calculate distance between two addresses using Google Maps in Excel
So I knocked up quickly this VBA Function in Excel which uses Google API distance matrix function to calculate the Google Maps distance.
Be sure to first replace YOUR_KEY with your personal API key obtained from here.
See the VBA code here:
Get Google Maps distance in meters
Get Google Maps duration (in seconds)
Calculate distance between two coordinates
You can calculate the distance between two coordinates (2 pairs of latitudes and longitudes) using either Google’s API or a simple VBA Function.
Calculate distance between coordinates using a VBA function
Taking into account the elliptic nature of Mother Earth you can easily calculate the distance between coordinates without using Google API .
The function returns the distance and using the unit variable you can state if you want the function to return the distance in Kilometres ( «K» ), Miles ( «M» ) or even nautical miles ( «N» ).
Calculate distance between coordinates using Google Maps in Excel
To get Google Maps distance between two coordinates simply use the same GetDistance function as above and replace the start and dest parameters with the coordinates in this format:
Final call example:
Same goes for the duration function:
How to set it up in Excel?
Important: Please remember that you need a direct Internet connection! Not via proxy etc.
Follow the steps:
Add new VBA Module
Go to the DEVELOPER ribbon and add select Visual Basic a and new Module to your VBA Project
Insert the VBA code
Insert the code from sections above (notice that the function is “Public” – therefore will be visible in your workbook as a so called UDF (User Defined Function)
Input the function in Excel
Go to the worksheet and input the function as shown below:
Make sure to replace ; with your default formula list separator (e.g. comma in the US)!
Calculate distance using Google Maps between any number of coordinates/addresses in Excel
Now that we know how to leverage our newly learned GetDistance and GetDuration functions we can use them to measure the distance/duration for any routes, with any coordinates/addresses in between our starting point and our destination. For this I created 2 simple procedures MultiGetDistance and MultiGetDuration:
Get distance between any number of locations
By using our GetDistance function we can get the distance between any number of locations using the Visual Basic procedure below:
Below example usage:
And here is the output: Get Google Maps distance between multiple addresses
Get duration between any number of locations
Similarly by using our GetDuration function we can get the duration between any number of locations using the Visual Basic procedure below:
Below example usage:
Parameters for calculating the Google Maps Distance
The following parameters are available in the API query:
| Param | Description |
|---|---|
| key | Your application’s API key. This key identifies your application for purposes of quota management. Learn how to get a key from the Google Developers Console. |
| mode | (defaults to driving) — Specifies the mode of transport to use when calculating distance. Valid values and other request details are specified in the Travel Modes section of this document. Other modes:
|
| language | The language in which to return results. See list here |
| avoid | Restrictions to the route. Available:
|
| units | Unit system to use when expressing distance as text. Available:
|
For other parameters see the original Google Distance Matrix API page:
The Google Distance Matrix API
Limits and common issues
Read more on the limitations of the Google Distance Matrix API here:
The Google Distance Matrix API
Google limits the amount of free queries to the following:
- 100 elements per query.
- 100 elements per 10 seconds.
- 2 500 elements per 24 hour period.
So beware of any mass recalculation of these functions as you may quickly find yourself exceeding these limits.
Common issues
The functions above are not full proof and don’t take into account:
- Be sure to obtain a Google Distance Matrix Key!
- In case of errors be sure to check if you are sitting behind a proxy server. If so check here how to configure a proxy
- That Google won’t find the exact addresses and will approximate with a similar address (see component filtering for more precision)
- That Google might return several matches. While the function takes the first one from the returned list
- HTTP or timeouts. See my Web Scraping Kit for how I dealt with such
- That distances/duration differ depending on which location is set as origin and which is set as destination (one way roads, detours etc.)
Having trouble with matching a large dataset with distances and durations? Reach out for a free quote.
Download an example
You can download an example of the above below and support AnalystCave.com:
Need help with calculating the distance between two addresses using the approach above? Feel free to comment below or ask a new Question via Questions page.
Next steps
Want to get the geolocation (coordinates) of any address?
Excel: Get geolocation (coordinates) of any address
Want to add maps and other cool Google Charts in Excel?
Excel: Google Charts Tool
Want to use Google translate in Excel?
Excel: Google Translate functionality
Загрузка...
detector