Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: Пред. 1 2
RSS
Макрос для отбора уникальных значений из ОГРОМНОГО массива, Нужны только уникальные значения
 
#31
27.09.2018 10:35:43
Понимаю, что нужен был макрос, но вдруг кому пригодится вариант на Power Query.
Андрей, если не сложно прогоните на вашем тестовом массиве мое решение? Ради спортивного интересу.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    ПосчитанныеCтроки = Table.RowCount(Источник),
    #"Несвернутые столбцы" = Table.UnpivotOtherColumns(Источник, {}, "Атрибут", "Значение")[Значение],
    #"Удаленные дубликаты" = List.Split(List.Distinct(#"Несвернутые столбцы"),ПосчитанныеCтроки),
    #"Преобразовано в таблицу" = Table.FromList(#"Удаленные дубликаты", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Преобразовано в таблицу", "Индекс", 1, 1),
    #"Развернутый элемент Column1" = Table.ExpandListColumn(#"Добавлен индекс", "Column1"),
    #"Добавлен индекс1" = Table.AddIndexColumn(#"Развернутый элемент Column1", "Индекс.1", 0, 1),
    #"Вычисленный остаток от деления" = Table.TransformColumns(#"Добавлен индекс1", {{"Индекс.1", each Number.Mod(_, ПосчитанныеCтроки), type number}}),
    #"Сведенный столбец" = Table.Pivot(Table.TransformColumnTypes(#"Вычисленный остаток от деления", {{"Индекс", type text}}, "ru-RU"), List.Distinct(Table.TransformColumnTypes(#"Вычисленный остаток от деления", {{"Индекс", type text}}, "ru-RU")[Индекс]), "Индекс", "Column1"),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Сведенный столбец",{"Индекс.1"})
in
    #"Удаленные столбцы"

Попробовал на 8 столбцах по миллиону строк, отработал за 92 секунды, но это на 8 ядерном ксеоне и 32 гига памяти.
Изменено: PooHkrd - 27.09.2018 10:49:59
Вот горшок пустой, он предмет простой...
 
 
 
#32
27.09.2018 11:36:50
Цитата
PooHkrd написал:
прогоните на вашем тестовом массиве мое решение
Ваш за 165 секунд. Можно чуть побыстрее за 36 секунд
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    rowCount = Table.RowCount(Source),
    uniqueList = List.Buffer(List.Distinct(Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value")[Value])),
    itemCount = List.Count(uniqueList),
    maker = List.Generate(
    () => [pos = 0, items = try List.Range(uniqueList, pos, rowCount) otherwise null],
    each [pos] < itemCount,
    each [pos = [pos] + rowCount, items = try List.Range(uniqueList, pos, rowCount) otherwise null],
    each [items]
    ),
    result = Table.FromColumns(maker, List.Transform({1..List.Count(maker)}, each Text.From(_)))
in
    result
 
 
 
#33
27.09.2018 11:55:55
Цитата
Андрей VG написал:
Можно чуть побыстрее за 36 секунд
Ого, это Pivot.Table с индексами так много времени забирает? Я тоже подумал, что генерация столбцов таблицы сразу из списка быстрее будет, но не думал, что на столько. Вот она - цена мышиного хардкора. :D
Вот горшок пустой, он предмет простой...
 
 
 
#34
27.09.2018 13:21:19
Цитата
Михаил С. написал:
четырьмя градусниками различных систем
Цельсий, Фаренгейт, Кельвин... Реомюр?
F1 творит чудеса
 
 
 
#35
27.09.2018 13:48:33
Off
Максим Зеленский, it's nice not rectal  :-)
По вопросам из тем форума, личку не читаю.
 
 
 
#36
27.09.2018 14:17:59
ОФФ-ТОП
Цитата
Максим Зеленский написал:
Цельсий, Фаренгейт, Кельвин... Реомюр?
нет, все по цельсию. Ртутный, Электрический, указатель с датчиком с трактора - не знаю, как он работает, но там от датчика к указателю трубка идет и еще один, не помню какой, скорее всего тоже ртутный все показали 91Ц.
На работе делал рацуху по проверке датчиков включения вентилятора на Икарус, температура срабатывания у них - 93 Ц, вода кипит - не один не срабатывает. Пришлось делать как на радиаторе автомобиля, чтоб давление повышать.

БМВ, я смотрел подобные таблицы раньше, ну не получается у них почему то... не сходится... Средняя высота Пятигоска над уронем моря - 660 м; атмосферное давление на данный момент - 770 мм; счас у меня нет подходящего термометра, проверить не могу...
 
 
 
#37
27.09.2018 21:24:03
Андрей VG, времени в обрез, набросал вариант через LPXLOPER12, что, думаю несколько медленнее, чем напрямую используя double array FP12, но не успеваю, доделать второй вариант.
Первый вариант, прилагаю - две библы (х32, х64), в них функция DellDuplicates. В файле Excel (тоже прилагаю) есть макрос test, который тестирует эту функцию.
Тест элементарный:
1.Открыть файл Excel
2.Открыть одну библу - разрядности вашего Excel
3.Запустить макрос

Заполняются все 8 миллионов, с выводом отсортированных данных, с удалением дубликатов (формула массива) - можно пересчитывать.
Итого у меня на хорошем ПК, расчет и заполнение ячеек происходит за 7 сек.
На больших объемах Excel x32  - у меня не отрабатывает.
Вообще, львиная часть времени уходит на ввод и вывод информации на лист.
Поэтому так медленно, на мой взгляд.
Если стоит задача просто получить массив, удалить дубликаты, выдать новый массив, это будет значительно быстрее, к примеру через declare.
или распарсить sheet1.xml, или сохранить в CSV/txt и очень быстро обработать exe.
Почему-то файлы более 100 Кбайт не проходят, было же 300? (пришлось резать часть кода в библе-другие функции, что бы влезла)
Изменено: bedvit - 27.09.2018 21:25:12
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#38
27.09.2018 21:24:48
Библа х32
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#39
27.09.2018 21:47:02
БМВ, кстати, вот пример задачи, который нормально решается в Excel64, и несколько сложнее в Excel32. Прогресс идет, объемы обрабатываемых данные растут. Думаю, все равно все на 64 bit перейдут. Вопрос времени :)
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#40
27.09.2018 22:06:21
Off
Цитата
bedvit написал:
все на 64 bit перейдут. Вопрос времени
всех в облака выгонят. Вопрос времени.  :-)

Ну а 32 vs 64 - отладили 64 уже давно и 32 имеет преимущество только для тех кто на линуксе его крутят, 64 там не запустить.

Да есть у меня на работе группа экономистов, модели свои считают x32 не справляется.  но убейте меня, эти объемы на пользовательском пк - это путь в никуда. Хотя геологические данные у нас считают и там террабайты сейсмических данных порой обрабатываются в модели и софт исключительно для рабочих станций.
По вопросам из тем форума, личку не читаю.
 
 
 
#41
27.09.2018 22:15:15
Цитата
БМВ написал:
всех в облака выгонят. Вопрос времени.  :-)
а вот, как не парадоксально, в облаках не все так безоблачно. Excel - это львиная доля финансовых расчетов - не все готовы их в облака выкладывать. Плюс подозреваю (но не утверждаю), что производительность в облаках явно проигрывает настольному варианту, и часть расчетов, таких как
Цитата
БМВ написал:
террабайты сейсмических данных порой обрабатываются в модели
будут на локалке. Если на сервак не перебросят функционал.
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#42
27.09.2018 22:42:03
Цитата
bedvit написал:
Итого у меня на хорошем ПК, расчет и заполнение ячеек происходит за 7 сек.
Виталий, большое спасибо за тест. Попробую поковырять. Хотя подозреваю, что если написать на VBA можно будет секунд в 20 уложиться.  Собственно основной алгоритм сортировка и отбор на С++ по идее должно менее секунды выполняться, а остальное действительно ввод/вывод.
 
 
 
#43
27.09.2018 23:12:13
Фига тут Функция Excel (Формула1, типа)  8-0
Вечер удивлений просто))) ну вы даёте, господа знатоки  :idea:  ;)
Изменено: Jack Famous - 27.09.2018 23:12:31
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#44
27.09.2018 23:22:21
Off
Цитата
bedvit написал:
это львиная доля финансовых расчетов - не все готовы их в облака выкладывать
Еще недавно все свои серверные держали, сегодня я сам предпочту сразу поставить все в ДЦ, сняв с себя проблемы каналов, энергетики, пожаротушения, да и физической безопасности и таких компаний уже очень много. От этого до приватного облака, которые уже дают многие провайдеры- пол шага. Так что , как и в случае с обычными процедурами безопасности, ( никому постороннему нельзя, но NDA подписан, значит можно) , так и с облаками будет, постепенно все поймут что безопасность не хуже.  
По вопросам из тем форума, личку не читаю.
 
 
 
#45
28.09.2018 01:43:24
Соглашусь с БМВ…
Не знаток/эксперт в области облачных сервисов, но на уровне юзера могу сказать, что подписки захватывают рынок. И не просто так. Вычислительные мощности компаний предоставляют существенный прирост скорости/качества обработки (дизайнерских, скажем) 3D-моделей и даже игр. Иметь современный мощный комп далеко не каждому по силам (финансово) — к тому же, с каждым годом "железо" устаревает и обесценивается. В таком случае, подписка — весьма конкурентный, на данный момент, способ "производить продукт" (вычислительный). А рынок на это и работает.
Изменено: Jack Famous - 28.09.2018 01:43:51
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#46
28.09.2018 11:59:05
Реализуя идею из #29 в сравнении с кодом Андрея на коллекции из #30 и тестовой методикой оттуда же.
Код
Sub testArr()
Dim x, n&
Dim t!: t = Timer
  ReDim v(0 To 8000000) As Boolean
  For Each x In ActiveSheet.Range("A2:H1000001").Value2
    If v(x) Then Else v(x) = True: n = n + 1
  Next
  Debug.Print "Arr: time " & Timer - t & " uniques "; n
End Sub
If в такой редакции работает чуть быстрее чем If Not v(x) Then v(x) = True: n = n + 1
Результаты (WinXP, Офис2010, проц 1,2ГГц)
Код
Arr: time 8,359375 uniques  5056549 
Col: time 244,7344 uniques 5056549
Полный текст тестовых процедур
Скрытый текст
 
 
 
#47
30.09.2018 00:49:01
Используя ту же функцию из сообщения #37 - DellDuplicates и не очень эффективно ее используя в VBA (накладные расходы на com, вывод хвоста - ненужного диапазона? предназначенного для диапазона ячеек), без вывода данных на лист - 2,2 секунды при сортировке 8 млн.значений и удалении дубликатов.
ПК -  Core i7-3770, win7, ОЗУ - 16Gb, excel 2016 x 64
Код
Sub Test2()
Dim i&, j&, t#, A(), xEnd, yEnd, A2()
xEnd = 1000000 'строк
yEnd = 8 'столбцов
Range("a2:q1000001").ClearContents
ReDim A(1 To xEnd, 1 To yEnd)
  For i = 1 To xEnd: For j = 1 To yEnd
    A(i, j) = Int(Rnd * 8000001)
  Next: Next
  
t = Timer 'start
A2 = Application.Run("DellDuplicates", A)
Debug.Print "Calculate = " & Timer - t
End Sub


Стало интересно, набросал .exe
Генерация массива из 8 млн. чисел, сортировка, удаление дубликатов - 0,99 сек.
Без генерации - только сортировка, удаление - 0,74 сек.
Итоговых значений около 6 млн. из 8 млн.
Код
#include "stdafx.h"
#include <vector>
#include <thread>
#include <algorithm>

   int main()
   {
      std::vector<long> myVector(8000000);
      unsigned int start_time = clock();
      for (long x = 0; x < 8000000; x++) 
      {
         myVector[x] = (rand() << 10) | rand(); 
      }
      std::sort(myVector.begin(), myVector.end());
      myVector.erase(unique(myVector.begin(), myVector.end()), myVector.end());
      unsigned int end_time = clock();
      unsigned int search_time = end_time - start_time;
      wprintf(L"Time, sec: %f (%lu) \n", search_time / 1000.0, myVector.size());
      system("pause");
      return 0;
   }
Изменено: bedvit - 30.09.2018 00:53:35
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#48
30.09.2018 13:39:23
Виталий, спасибо за очередное очень интересное исследование!
Владимир
 
 
 
Страницы: Пред. 1 2
Наверх