Всем привет! Столкнулся с проблемой низкой производительности в Excel при большом количестве строк с использованием моего метода поиска последнего и предпоследнего значения в массиве данных.
Описание таблицы с данными (упрощенно): столбцы: A - наименование B - значение
Наименования периодически повторяются, но при этом они добавляются в конец текущих записей из внешнего источника без пересортировки. Было необходимо найти адреса последнего и предпоследнего значения одного наименования: ННН - 83043 ААА - 12345 БББ - 23456 ВВВ - 84548 ААА - 93030 В данном случае адрес последнего значения ААА - столбец А,строка 5, а предпоследнее - столбец А,строка 2. Решено было вот так: отдельный столбец для каждого наименования (заголовок - наименование из столбца А), потом делался СЧЁТЕСЛИ(A:A;$C$2) для наименования ННН, СЧЁТЕСЛИ(A:A;$D$2) для наименования ААА и т.д. Потом =ЕСЛИ(А2=$С$2;МИН($C$3:C4)-1;"") "протягивался" для всех строк в столбце С (для наименования ННН) и напоследок =ПОИСКПОЗ(1;C:C;0) и =ПОИСКПОЗ(2;C:C;0) - в таком случае находится последнее и предпоследнее значение для ННН, адрес узнаем при помощи =АДРЕС(). Все работает, но если мало строк - добавили (10-20 тыс строк) - excel зависает и приходится вручную добавлять столбцы для новых наименований, поэтому возникла необходимость оптимизировать для большого количества строк (сотни тысяч) и уникальных наименований - до 1000. В идеале было бы хорошо добавлять данные в БД (SQL), а в excel уже просто анализировать эти данные из БД, но пока будет храниться в excel для упрощения конструкции и ускорения внедрения. [moder]Нужен пример
Всем привет! Столкнулся с проблемой низкой производительности в Excel при большом количестве строк с использованием моего метода поиска последнего и предпоследнего значения в массиве данных.
Описание таблицы с данными (упрощенно): столбцы: A - наименование B - значение
Наименования периодически повторяются, но при этом они добавляются в конец текущих записей из внешнего источника без пересортировки. Было необходимо найти адреса последнего и предпоследнего значения одного наименования: ННН - 83043 ААА - 12345 БББ - 23456 ВВВ - 84548 ААА - 93030 В данном случае адрес последнего значения ААА - столбец А,строка 5, а предпоследнее - столбец А,строка 2. Решено было вот так: отдельный столбец для каждого наименования (заголовок - наименование из столбца А), потом делался СЧЁТЕСЛИ(A:A;$C$2) для наименования ННН, СЧЁТЕСЛИ(A:A;$D$2) для наименования ААА и т.д. Потом =ЕСЛИ(А2=$С$2;МИН($C$3:C4)-1;"") "протягивался" для всех строк в столбце С (для наименования ННН) и напоследок =ПОИСКПОЗ(1;C:C;0) и =ПОИСКПОЗ(2;C:C;0) - в таком случае находится последнее и предпоследнее значение для ННН, адрес узнаем при помощи =АДРЕС(). Все работает, но если мало строк - добавили (10-20 тыс строк) - excel зависает и приходится вручную добавлять столбцы для новых наименований, поэтому возникла необходимость оптимизировать для большого количества строк (сотни тысяч) и уникальных наименований - до 1000. В идеале было бы хорошо добавлять данные в БД (SQL), а в excel уже просто анализировать эти данные из БД, но пока будет храниться в excel для упрощения конструкции и ускорения внедрения. [moder]Нужен примерmrdc
Доброе утро! Прикрепил файл, сейчас посмотрю Ваше предложение. В моем примере "Последнее значение" и "Предыдущее значение" - просто номер строки, а не значение в этой ячейке.
Доброе утро! Прикрепил файл, сейчас посмотрю Ваше предложение. В моем примере "Последнее значение" и "Предыдущее значение" - просто номер строки, а не значение в этой ячейке.mrdc
Сообщение отредактировал mrdc - Понедельник, 20.10.2014, 10:41
Формула Владимира (gling) Вам подойдет. Сделал на Вашем файле без проверки на существование. На 25000 строк, вроде нормально работает. Проверяйте на полном объеме.
Формула Владимира (gling) Вам подойдет. Сделал на Вашем файле без проверки на существование. На 25000 строк, вроде нормально работает. Проверяйте на полном объеме.ShAM
gling, спасибо! Все работает. Подскажите, пожалуйста, как разместить данные формулы не в строках 1 и 2, т.к. если переношу их ниже - данные становятся неверны: видимо, массив "привязан" к текущей строке в качестве начальной точки отсчета.
По скорости: массив данных ~ 348 000 строк, 22 столбца, просматриваемый диапазон - 350 000 строк в одном столбце, уникальных наименований - 229, т.е. для каждого находится последнее и предыдущее значения = 458 значений. Ноутбук, процессор C2D 2,5Ггц, 2 ядра, RAM 3,5GB - добавление значений занимает около 1 минуты, вне зависимости добавляем одно значение или массив.
gling, спасибо! Все работает. Подскажите, пожалуйста, как разместить данные формулы не в строках 1 и 2, т.к. если переношу их ниже - данные становятся неверны: видимо, массив "привязан" к текущей строке в качестве начальной точки отсчета.
По скорости: массив данных ~ 348 000 строк, 22 столбца, просматриваемый диапазон - 350 000 строк в одном столбце, уникальных наименований - 229, т.е. для каждого находится последнее и предыдущее значения = 458 значений. Ноутбук, процессор C2D 2,5Ггц, 2 ядра, RAM 3,5GB - добавление значений занимает около 1 минуты, вне зависимости добавляем одно значение или массив.mrdc
Сообщение отредактировал mrdc - Понедельник, 20.10.2014, 19:07
добавление значений занимает около 1 минуты, вне зависимости добавляем одно значение или массив.
Автопересчет формул включен? Если да, то попробуйте добавлять данные при отключенном автопересчете, потом выполнить запустить пересчет клавишей F9. выделить диапазон с формулами, которые нужно пересчитать и выполнить макрос [vba]
Код
Sub пересчет() Selection.Calculate End Sub
[/vba]
mrdc, в формуле gling, для последнего значения замените СТРОКА() на 1, для предпоследнего - на 2
добавление значений занимает около 1 минуты, вне зависимости добавляем одно значение или массив.
Автопересчет формул включен? Если да, то попробуйте добавлять данные при отключенном автопересчете, потом выполнить запустить пересчет клавишей F9. выделить диапазон с формулами, которые нужно пересчитать и выполнить макрос [vba]
krosav4ig, это вряд ли ускорит процесс - сразу после добавления идет пересчет значений для всех наименований. Данные вставляю массивом, поэтому пересчет идет только 1 раз для всех значений, а если вставлять по 1 строке - тогда, конечно, автопересчет надо отключать и пересчитывать уже после внесения данных. Думаю все-таки на vba переписать - должно ускорить процесс.
krosav4ig, это вряд ли ускорит процесс - сразу после добавления идет пересчет значений для всех наименований. Данные вставляю массивом, поэтому пересчет идет только 1 раз для всех значений, а если вставлять по 1 строке - тогда, конечно, автопересчет надо отключать и пересчитывать уже после внесения данных. Думаю все-таки на vba переписать - должно ускорить процесс.mrdc
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
