Функция SMALL()
nopoh
Может, это про танкистов. Но опрос показал, что коллеги (как и я) такой функции не знают.

В общем есть функция small(array;k) - возвращает k-й минимальный элемент в массиве.

Пример использования в файле. 
upd. Large() делает наоборот


Small Trick.xlsx



sumif, countif, medianif
nopoh
При подсчете среднего значения с условием обычно пользуются функциями sumif/countif или averageif, которая появилась в 2007 версии Excel и пока не так распространена.
К примеру, у нас есть предложения о продаже автомобилей.


На основе этой таблицы можно определить среднюю стоимость автомобиля для каждой марки. Обязательно необходимо делать это в отдельной сервисной таблице, т.к. при использовании функций sumif/countif для реестра в несколько тысяч позиций вычисляется всё крайне медленно (часто встречающаяся ошибка построения расчетного файла).
Так же в Excel нет функции medianif, однако её можно реализовать используя array formulas.




Шокирующий xlsb
nopoh
Excel Binary Book - бинарная книга Excel, которая появилась в 2007 версии. Создана для хранения больших объемов данных (таблиц с сотнями тысяч строк и сотнями колонок). Ага, это про нас. 
Реальное тестирование оказалось ошеломительным, ниже сравнительная таблица. Относительно минусов интернет молчит, в расчётах тоже пока замечено не было. Так что все на xlsb! Будем исследовать формат. 

Подтягиваем данные
nopoh

Про Excel.
 
Файл о котором я писал в первом посте - модель по оценке основных средств.
Каждый из 500 тысяч объектов отнесен к одной из 165 групп основных средств и принадлежит одной из 7 компаний. Используя lookup-функции в реестр подгружаются различные данные с других листов. 

Например, исходя из группы ОС опредялется полный срок службы объекта.



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





match такой match
nopoh
Про Excel. 

Узким местом при работе с большими файлами в 500 тысяч строк является необходимость подкачивания данных с одного листа на другой. Первое столкновение у меня (и коллег) возникло летом, когда на лист в полмиллиона строк нужно было загрузить данные с другого листа в 200 тысяч строк, связав позиции по ID. Lenovo t61 делал это около 3 часов. Для ускорения работы был куплен двухпроцессорный, восьмиядерный сервер с 16 гигабайтами оперативки, который управился за 12 минут. 


Для каждой из 500 тысяч позиций делался match( ID, Sheet2!A:A, 0). Вот мы и пришли к самому интересному. Что из себя представляет функция match? 

Если match_type = 0 (как у нас), то она возвращает номер по порядку в lookup_range равного нашему lookup_value элемента(ID). Соответственно, каждый раз она пробегает lookup_range от начала до искомого элемента. Время пропорционально количеству элементов и в случае больших массивов как у нас выливается в часы на обычных компьютерах.

Но вот если match_type равен 1, то функция возвращает номер по порядку равного нашему или ближайшего меньшего. Необходимо только, чтобы данные в lookup_range были отсортированы по возрастанию. Лист с информацией по числовому полю Excel сортирует мгновенно, причем это было сделано. Однако поиск в этом случае организован по-другому - используется алгоритм бинарного поиска элемента. То есть время пропорционально log2(n), где n - количество элементов в lookup_range, и не сильно возрастает на больших массивах. 

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

Проверяем ID на равенство найденному значению и получаем:
if (ID = index(Sheet2!A:A, match(ID, Sheet2!A:A, 1), 1), match(ID, Sheet2!A:A, 1), "n/a") 
На Lenovo t61 считается мгновенно. К сожалению, летом 2009 года я даже не подумал об этом.

(no subject)
nopoh
 Пока здесь ничего нет. Но это только пока.

You are viewing nopoh