Хотел бы с вами проконсультироваться, как правильней поступить при написании кода VBA, который преобразует данные в таблице с большим кол-вом строк (100К+). В данный момент таблица обрабатывается вручную путем замены, применения различных формул Excel и т.д. Сами данные выгружаются в .csv и многие поля некорректно отображаются. Главной проблемой является формула массива =МОДА.НСК с несколькими условиями, которая при протягивании по всей таблице отправляет комп в астрал на 60-90 минут. Из-за чего появилась необходимость более быстрой обработки таблицы в нужный вид для дальнейшего использования в сводной таблице.
Изменения с помощью формул в Excel: 1) Замена по столбцу даты с заменой символа "/" на "." (необходимо для дальнейшей фильтрации пользователем по дате) 2) Создание в новом столбце расчета дня недели от даты. Формула:
Код
=ТЕКСТ(B:B; "ДДДД")
3) Создание в новом столбце определения названия ряда из колонки места. Формула:
. и так далее перебор для более чем 20 наименований мест и определения для них более 15 названий рядов. 4) Определение наиболее часто встречающихся значений в поле "Время отправления" с критериями совпадения по "Номер Маршрута", "День Недели" и "Маршрут". Тут как раз и была формула массива {
Так как знания VBA довольно скудны, я начал реализовывать каждую задачу по отдельности: сначала нашел функцию замены символа путем выделения столбца и использования Replace. Далее по тому же принципу начал заменять по очереди названия ряда на основании поля "Место" и уже на данном этапе скрипт при проверке задумывается секунд на 10-20. Тут я и задумался, что так как мне все равно придется перебирать все строки таблицы для определения наиболее часто встречающихся значений, то может быть имеет смысл вносить все правки в процессе обработки каждой строки? С этой мыслью я и зашел в тупик, так как не знаю, что будет с точки зрения оптимизации корректней, с учетом того, что данные для наиболее часто встречающихся значений я получу лишь в конце проверки всей таблицы. Поэтому и прошу совета: лучше сначала подготовить таблицу по каждому столбцу, а после производить пересчет построчно, или же сразу заполнять таблицу по мере прохождения скрипта по каждой из строк?
P.S. Ребят, честно хотел объяснить как можно понятней. Сори, если перемудрил. Пример куска выгрузки прикрепляю.
Всем доброго времени суток!
Хотел бы с вами проконсультироваться, как правильней поступить при написании кода VBA, который преобразует данные в таблице с большим кол-вом строк (100К+). В данный момент таблица обрабатывается вручную путем замены, применения различных формул Excel и т.д. Сами данные выгружаются в .csv и многие поля некорректно отображаются. Главной проблемой является формула массива =МОДА.НСК с несколькими условиями, которая при протягивании по всей таблице отправляет комп в астрал на 60-90 минут. Из-за чего появилась необходимость более быстрой обработки таблицы в нужный вид для дальнейшего использования в сводной таблице.
Изменения с помощью формул в Excel: 1) Замена по столбцу даты с заменой символа "/" на "." (необходимо для дальнейшей фильтрации пользователем по дате) 2) Создание в новом столбце расчета дня недели от даты. Формула:
Код
=ТЕКСТ(B:B; "ДДДД")
3) Создание в новом столбце определения названия ряда из колонки места. Формула:
. и так далее перебор для более чем 20 наименований мест и определения для них более 15 названий рядов. 4) Определение наиболее часто встречающихся значений в поле "Время отправления" с критериями совпадения по "Номер Маршрута", "День Недели" и "Маршрут". Тут как раз и была формула массива {
Так как знания VBA довольно скудны, я начал реализовывать каждую задачу по отдельности: сначала нашел функцию замены символа путем выделения столбца и использования Replace. Далее по тому же принципу начал заменять по очереди названия ряда на основании поля "Место" и уже на данном этапе скрипт при проверке задумывается секунд на 10-20. Тут я и задумался, что так как мне все равно придется перебирать все строки таблицы для определения наиболее часто встречающихся значений, то может быть имеет смысл вносить все правки в процессе обработки каждой строки? С этой мыслью я и зашел в тупик, так как не знаю, что будет с точки зрения оптимизации корректней, с учетом того, что данные для наиболее часто встречающихся значений я получу лишь в конце проверки всей таблицы. Поэтому и прошу совета: лучше сначала подготовить таблицу по каждому столбцу, а после производить пересчет построчно, или же сразу заполнять таблицу по мере прохождения скрипта по каждой из строк?
P.S. Ребят, честно хотел объяснить как можно понятней. Сори, если перемудрил. Пример куска выгрузки прикрепляю.messir
Здравствуйте, messir, что бы не цитировать пишу по пунктам 1)импорт данных из csv лучше дать через OLEDBConnect для правильного отображения данных вам понадобится Schema.ini файл. это все можно загуглить. 2) здесь и далее не используйте ссылки на всю колонку, а лишь на строки с данными 3) я бы написал по другому формулу
4) здесь таже ошибка (п.2) не ссылайтесь на всю колонку! и если бы вы приложили пример с расчетами, то было бы понятней 5) можно сделать проще через Лукап
Код
=VLOOKUP(K4;$J$11:$K$18;2;1)
примеры во вложении
Здравствуйте, messir, что бы не цитировать пишу по пунктам 1)импорт данных из csv лучше дать через OLEDBConnect для правильного отображения данных вам понадобится Schema.ini файл. это все можно загуглить. 2) здесь и далее не используйте ссылки на всю колонку, а лишь на строки с данными 3) я бы написал по другому формулу
4) здесь таже ошибка (п.2) не ссылайтесь на всю колонку! и если бы вы приложили пример с расчетами, то было бы понятней 5) можно сделать проще через Лукап
Спасибо, что откликнулись. Как я и предполагал - слишком сильно намудрил с описанием задачи. 1) Бегло почитав не совсем понял, как преобразовывать уже загруженные данные, но почитаю позже внимательней. Спасибо за инфу. В принципе проблему конвертации данных удалось решить путем [vba]
[/vba] 2) Это была вынужденная мера, так как на момент создания формул была задача "унифицировать" их для любых ячеек определенного столбца вне зависимости от того, в какую строку формулу вставят. С этим пунктом я бился сегодня весь день. Все тщетно Не прибегая к построчному перебору, получить день недели (именно значение, а не дату в формате ДДДД!) мне не удалось, поэтому пришлось применить в VBA код установки формулы Excel и применить ее ко всем строкам: [vba]
[/vba] 3) С формулами все понятно, но ищу решение для VBA. Чтобы можно было задать соответствие, что место "A" или "B" будут в новой колонке "Ряд" обозначаться как "АА1". Предполагаю, что нужно создавать массив, и обрабатывать его при построчном пересчете? 4) Вот тут как раз и надо ссылаться на всю колонку. Дело в том, что в таблице отображается фактическое время отправления маршрутки. По расписанию оно, к примеру, в 2:55 (и чаще всего оно таким и будет), но бывает, что отправление состоялось с задержкой или же раньше срока. Причем сравнивать надо на уровне "номер маршрута" вкупе с "маршрут" и в купе с "День недели", так как номера маршрутов могут быть иногда одинаковыми на разных маршрутах, а время по расписанию может отличаться по дням недели. В итоге конечный результат должен получиться как часто упоминающееся значения для каждого номера маршрута & дня недели & маршрута. Вот тут я не знаю как решить задачу в рамках VBA: пока в голове пытаюсь придумать как реализовать создание переменных, имя которых будет равным содержанию ячеек "номер маршрута", "день недели", "маршрут" а так же "Время отправления". Значением переменной будет счетчик кол-ва полного совпадения условий. По окончании строк запускается новый построчный алгоритм, который будет выставлять наиболее популярное по критериям значения для каждого совпадения. Но мне кажется есть возможность намного проще реализовать этот процесс. 5) Опять же решение нужно в рамках VBA. По идее функцию ВПР и надо описать, только внутри кода VBA.
Приветствую, boa!
Спасибо, что откликнулись. Как я и предполагал - слишком сильно намудрил с описанием задачи. 1) Бегло почитав не совсем понял, как преобразовывать уже загруженные данные, но почитаю позже внимательней. Спасибо за инфу. В принципе проблему конвертации данных удалось решить путем [vba]
[/vba] 2) Это была вынужденная мера, так как на момент создания формул была задача "унифицировать" их для любых ячеек определенного столбца вне зависимости от того, в какую строку формулу вставят. С этим пунктом я бился сегодня весь день. Все тщетно Не прибегая к построчному перебору, получить день недели (именно значение, а не дату в формате ДДДД!) мне не удалось, поэтому пришлось применить в VBA код установки формулы Excel и применить ее ко всем строкам: [vba]
[/vba] 3) С формулами все понятно, но ищу решение для VBA. Чтобы можно было задать соответствие, что место "A" или "B" будут в новой колонке "Ряд" обозначаться как "АА1". Предполагаю, что нужно создавать массив, и обрабатывать его при построчном пересчете? 4) Вот тут как раз и надо ссылаться на всю колонку. Дело в том, что в таблице отображается фактическое время отправления маршрутки. По расписанию оно, к примеру, в 2:55 (и чаще всего оно таким и будет), но бывает, что отправление состоялось с задержкой или же раньше срока. Причем сравнивать надо на уровне "номер маршрута" вкупе с "маршрут" и в купе с "День недели", так как номера маршрутов могут быть иногда одинаковыми на разных маршрутах, а время по расписанию может отличаться по дням недели. В итоге конечный результат должен получиться как часто упоминающееся значения для каждого номера маршрута & дня недели & маршрута. Вот тут я не знаю как решить задачу в рамках VBA: пока в голове пытаюсь придумать как реализовать создание переменных, имя которых будет равным содержанию ячеек "номер маршрута", "день недели", "маршрут" а так же "Время отправления". Значением переменной будет счетчик кол-ва полного совпадения условий. По окончании строк запускается новый построчный алгоритм, который будет выставлять наиболее популярное по критериям значения для каждого совпадения. Но мне кажется есть возможность намного проще реализовать этот процесс. 5) Опять же решение нужно в рамках VBA. По идее функцию ВПР и надо описать, только внутри кода VBA.messir
messir, переходите на координаты листа R1C1 и формулы будут "унифицированные" например: что бы получить номер дня недели достаточно в любой строке написать формулу
Код
=WEEKDAY(DATEVALUE(RC2);1)
а для ряда, если желаете, можно пользовательскую функцию использовать [vba]
если бы вы приложили пример с расчетами, то было бы понятней
вариантов множество.
messir, переходите на координаты листа R1C1 и формулы будут "унифицированные" например: что бы получить номер дня недели достаточно в любой строке написать формулу
Код
=WEEKDAY(DATEVALUE(RC2);1)
а для ряда, если желаете, можно пользовательскую функцию использовать [vba]
boa, Да эта "унификация" не для меня) Пользователи, которые будут использовать этот макрос из серии "кто в лес, кто по дрова": у каждого может быть своя версия Excel со своими надстройками или их отсутствием. Да чего далеко ходить. Даже выгружаемая таблица с данными может быть у каждого по своему отображена в рамках расположения и кол-во столбцов. Именно поэтому написания макроса я начал с того, что он удаляет из файла все листы, кроме основного и листа, куда пользователь копирует данные из выгрузки. Далее создает новый лист и копирует туда только нужные столбцы и только после этого начинает эти данные преобразовывать.
За функцию ряда респект и уважуха! Скопипазжу себе на заметку)))
Теперь нужно придумать как реализовать расчет часто встречающихся значений времени) Сегодня я уже пробовал реализовать задумку с указанием в новом столбце по сути склееные воедино данные ячеек. Получилось что-то вроде "114685вторникМРЧ-ЛЛА" для каждой строки. Дальше я пришел в тупик, так как не знаю, как рассчитать сколько у меня в таблице разновидностей отклонений по времени отправления, чтобы потом выявить эталонное (чаще встречающееся). Далее еще подумалось, что можно было бы создавать многомерный массив, в который бы заносились данные по принципу добавления нового значения массива при не совпадении данных, но потом понял, что сравнивать при переборе вообще непонятно реализовать - отложил эту мысль.
Зато вспомнились слова учительницы по русскому: Плохо когда не знаешь, да еще и забудешь (С)
boa, Да эта "унификация" не для меня) Пользователи, которые будут использовать этот макрос из серии "кто в лес, кто по дрова": у каждого может быть своя версия Excel со своими надстройками или их отсутствием. Да чего далеко ходить. Даже выгружаемая таблица с данными может быть у каждого по своему отображена в рамках расположения и кол-во столбцов. Именно поэтому написания макроса я начал с того, что он удаляет из файла все листы, кроме основного и листа, куда пользователь копирует данные из выгрузки. Далее создает новый лист и копирует туда только нужные столбцы и только после этого начинает эти данные преобразовывать.
За функцию ряда респект и уважуха! Скопипазжу себе на заметку)))
Теперь нужно придумать как реализовать расчет часто встречающихся значений времени) Сегодня я уже пробовал реализовать задумку с указанием в новом столбце по сути склееные воедино данные ячеек. Получилось что-то вроде "114685вторникМРЧ-ЛЛА" для каждой строки. Дальше я пришел в тупик, так как не знаю, как рассчитать сколько у меня в таблице разновидностей отклонений по времени отправления, чтобы потом выявить эталонное (чаще встречающееся). Далее еще подумалось, что можно было бы создавать многомерный массив, в который бы заносились данные по принципу добавления нового значения массива при не совпадении данных, но потом понял, что сравнивать при переборе вообще непонятно реализовать - отложил эту мысль.
Зато вспомнились слова учительницы по русскому: Плохо когда не знаешь, да еще и забудешь (С)messir
messir, Даже в математике одну и туже теорему можно по разному доказывать а вы хотите, что бы ваши Идеи были поняты "на слух" покажите, что вы там считаете... на примере
messir, Даже в математике одну и туже теорему можно по разному доказывать а вы хотите, что бы ваши Идеи были поняты "на слух" покажите, что вы там считаете... на примереboa
boa, Собрал пример таблички с формулами Excel, которые применяются пользователями в данный момент. Оставил строки только по одному коду места, чтобы не раздувать данные в примере таблицы. И даже сделал сводную, для примера. Надеюсь так будет понятней
boa, Собрал пример таблички с формулами Excel, которые применяются пользователями в данный момент. Оставил строки только по одному коду места, чтобы не раздувать данные в примере таблицы. И даже сделал сводную, для примера. Надеюсь так будет понятней messir
Прибавьте 1 минуту к F2, т.е. F2+1/24/60, а потом умножьте на 8
тогда 23:59 становится 0:00 и определяется в 0:00 - 2:59 :))) Ну в целом я понял принцип. Интересная механика с перемножением)) я даже не подумал в эту сторону))
Кстати такой метод подстановки по индексу в VBA чем реализуется? А то гугл на запрос "VBA аналог =ИНДЕКС" ничего толкового не выдает
Прибавьте 1 минуту к F2, т.е. F2+1/24/60, а потом умножьте на 8
тогда 23:59 становится 0:00 и определяется в 0:00 - 2:59 :))) Ну в целом я понял принцип. Интересная механика с перемножением)) я даже не подумал в эту сторону))
Кстати такой метод подстановки по индексу в VBA чем реализуется? А то гугл на запрос "VBA аналог =ИНДЕКС" ничего толкового не выдает messir
все просто: 1 день = 24 часа = 1, 1 час =1/24, 1 мин =1/24/60 вы делите сутки на 8 равных частей(по 3 часа) соответственно если значение времени умножить на 8 и округлить в большую сторону до целого, мы получим номер части в которой данный промежуток находится. подобие функции Индекс на VBA я вам приводил в 4-м сообщении
все просто: 1 день = 24 часа = 1, 1 час =1/24, 1 мин =1/24/60 вы делите сутки на 8 равных частей(по 3 часа) соответственно если значение времени умножить на 8 и округлить в большую сторону до целого, мы получим номер части в которой данный промежуток находится. подобие функции Индекс на VBA я вам приводил в 4-м сообщенииboa
Сообщение отредактировал boa - Четверг, 17.10.2019, 16:23
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
Не прибегая к построчному перебору, получить день недели (именно значение, а не дату в формате ДДДД!) мне не удалось, поэтому пришлось применить в VBA код установки формулы Excel и применить ее ко всем строкам:
