Ребят, подсобите пожалуйста, а то уже столько различных вариаций перебрал, и по-видимому я что-то делаю не так. В общем задача следующая: Определить минимальное время для каждого сотрудника по каждому дню недели и проставить это значение в каждую уникальную строчку. В таблице день недели рассчитывается формулой. Решение нужно на уровне формулы, так как использование шаблона невозможна по тех. причинам, а описывать рядовым пользованием функцию =ВПР и создание сводных таблиц крайне затруднительно, поэтому необходима универсальная формула, которую бы каждый вставил в ячейку и протянул на всю таблицу. Пробовал и =МИН и =ДМИН и другие функции. Через =ВПР работает, но, как писал выше, описать это в мануале для рядовых пользователей невозможно. =АГРЕГАТ работает, но для одной ячейки. Если протягивать на всю таблицу (~50-100 тыс строк), Excel уходит в астрал.
Так же прошу помочь с оптимизацией формулы соответствия в столбце "Подъезд". Она крайне примитивна - знаю, но там задача плясать от соседнего столбца слева от формулы.
Ребят, подсобите пожалуйста, а то уже столько различных вариаций перебрал, и по-видимому я что-то делаю не так. В общем задача следующая: Определить минимальное время для каждого сотрудника по каждому дню недели и проставить это значение в каждую уникальную строчку. В таблице день недели рассчитывается формулой. Решение нужно на уровне формулы, так как использование шаблона невозможна по тех. причинам, а описывать рядовым пользованием функцию =ВПР и создание сводных таблиц крайне затруднительно, поэтому необходима универсальная формула, которую бы каждый вставил в ячейку и протянул на всю таблицу. Пробовал и =МИН и =ДМИН и другие функции. Через =ВПР работает, но, как писал выше, описать это в мануале для рядовых пользователей невозможно. =АГРЕГАТ работает, но для одной ячейки. Если протягивать на всю таблицу (~50-100 тыс строк), Excel уходит в астрал.
Так же прошу помочь с оптимизацией формулы соответствия в столбце "Подъезд". Она крайне примитивна - знаю, но там задача плясать от соседнего столбца слева от формулы.messir
Заполните вручную в своем файле несколько строк, чтобы понять, как должен выглядеть верный ответ.
Заполнил колонку, которую надо рассчитать "Время МИН" вручную. Логика следующая: все строки Иванова по понедельника сравниваются и по всем понедельникам для Иванова проставляется это минимальное время. Потом так же по вторникам и т.д. И так для каждой строки каждого сотрудника. Файл в аттаче.
Я для примера указал цифры подъезда в критериях формулы, но сама деле там обозначения типа для турникета А и S = CE1, D F Q = CE2 ну и так далее. Причем в каждом подъезде разное кол-во соответствий. В примере просто получилось одинаковыми блоками по 4 пункта.
Заполните вручную в своем файле несколько строк, чтобы понять, как должен выглядеть верный ответ.
Заполнил колонку, которую надо рассчитать "Время МИН" вручную. Логика следующая: все строки Иванова по понедельника сравниваются и по всем понедельникам для Иванова проставляется это минимальное время. Потом так же по вторникам и т.д. И так для каждой строки каждого сотрудника. Файл в аттаче.
Я для примера указал цифры подъезда в критериях формулы, но сама деле там обозначения типа для турникета А и S = CE1, D F Q = CE2 ну и так далее. Причем в каждом подъезде разное кол-во соответствий. В примере просто получилось одинаковыми блоками по 4 пункта.messir
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter Но на 100000 в астрал все равно уйдет Может, Вам лучше сводную таблицу? см. файл - 2 варианта сводной
Так нужно?
Код
=МИН(ЕСЛИ((A$2:A$99=A2)*(C$2:C$99=C2);D$2:D$99))
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter Но на 100000 в астрал все равно уйдет Может, Вам лучше сводную таблицу? см. файл - 2 варианта сводной_Boroda_
Che79, _Boroda_, Пробовал я такой вариант еще до обращения сюда (гуглил же сначала разные варианты и решения). Не работает в реальной таблице и указывает время 0:00 Сижу теперь голову ломаю, почему в таблице с примером работает, а у меня нет
Che79, _Boroda_, Пробовал я такой вариант еще до обращения сюда (гуглил же сначала разные варианты и решения). Не работает в реальной таблице и указывает время 0:00 Сижу теперь голову ломаю, почему в таблице с примером работает, а у меня нет messir
Сообщение отредактировал messir - Пятница, 21.06.2019, 15:24
Или потому, что у какого-то Иванова понедельник просто не заполнен, пусто там в понедельнике. А пусто в данном слецчае интерпретируется как 0 Вам вообще зачем это нужно? И почему не хотите сводную? Пользователю вообще ничего делать не нужно будет, только обновить ее для новых данных
Или потому, что у какого-то Иванова понедельник просто не заполнен, пусто там в понедельнике. А пусто в данном слецчае интерпретируется как 0 Вам вообще зачем это нужно? И почему не хотите сводную? Пользователю вообще ничего делать не нужно будет, только обновить ее для новых данных_Boroda_
Nic70y, _Boroda_, Che79, Нашел причину. Дело в том, что файл экспортируется в .XML и если по ячейке с временем нажать F2 и Enter, то формат времени применяется и ваша и другие экспериментальные формулы, которые я пробовал срабатывают. Причина оказывается в этом Блин, мне и в голову не пришло, что формат не применился при экспортировании. Теперь сначала буду пробовать идеи сравнений))
И почему не хотите сводную? Пользователю вообще ничего делать не нужно будет, только обновить ее для новых данных
Дело в том, что конечный пользователь выгружает свои данные из общей базы данных. Столбцы там одни и те же, но вот их порядок у каждого свой может быть. Поэтому использовать какой-то единый шаблон невозможно. А описать процесс создания сводной таблицы и использования =ВПР для пользователей, которым надо писать пошагово вплоть до количества кликов и скриншоты где находится та или иная кнопка. Ну в общем вы поняли уровень) Поэтому и приходится искать решения в рамках "тупо скопируй и вставь")))
Ладно) в понедельник продолжу кумекать) Спасибо всем откликнувшимся и хороших выходных!
Nic70y, _Boroda_, Che79, Нашел причину. Дело в том, что файл экспортируется в .XML и если по ячейке с временем нажать F2 и Enter, то формат времени применяется и ваша и другие экспериментальные формулы, которые я пробовал срабатывают. Причина оказывается в этом Блин, мне и в голову не пришло, что формат не применился при экспортировании. Теперь сначала буду пробовать идеи сравнений))
И почему не хотите сводную? Пользователю вообще ничего делать не нужно будет, только обновить ее для новых данных
Дело в том, что конечный пользователь выгружает свои данные из общей базы данных. Столбцы там одни и те же, но вот их порядок у каждого свой может быть. Поэтому использовать какой-то единый шаблон невозможно. А описать процесс создания сводной таблицы и использования =ВПР для пользователей, которым надо писать пошагово вплоть до количества кликов и скриншоты где находится та или иная кнопка. Ну в общем вы поняли уровень) Поэтому и приходится искать решения в рамках "тупо скопируй и вставь")))
Ладно) в понедельник продолжу кумекать) Спасибо всем откликнувшимся и хороших выходных! messir
Сообщение отредактировал messir - Пятница, 21.06.2019, 15:43
Столбцы там одни и те же, но вот их порядок у каждого свой может быть
Открою Вам секрет - для сводной таблицы неважен порядок расположения столбцов. И каждый раз создавать ее тоже не нужно. Нужно просто скопировать новые данные, вставить их на место старых и обновить созданную заранее сводную
А преобразовать данные для нормальной работы формулы очень просто - выделяете нужный столбец - вкладка Данные - Текст по столбцам - ничего там больше не делая, жмем ОК
Для пользователя, который вообще ничего не понимает, можно написать макрос, который все за него делать будет и повесить его (макрос, не пользователя) на кнопку в файле. Пользователь открывает файл и жмет кнопку. Все. Но для написания макроса нужно больше данных про исходный файл
Столбцы там одни и те же, но вот их порядок у каждого свой может быть
Открою Вам секрет - для сводной таблицы неважен порядок расположения столбцов. И каждый раз создавать ее тоже не нужно. Нужно просто скопировать новые данные, вставить их на место старых и обновить созданную заранее сводную
А преобразовать данные для нормальной работы формулы очень просто - выделяете нужный столбец - вкладка Данные - Текст по столбцам - ничего там больше не делая, жмем ОК
Для пользователя, который вообще ничего не понимает, можно написать макрос, который все за него делать будет и повесить его (макрос, не пользователя) на кнопку в файле. Пользователь открывает файл и жмет кнопку. Все. Но для написания макроса нужно больше данных про исходный файл_Boroda_
_Boroda_, Вернулся я к этому вопросу сегодня. После редактирования столбца времени путем замены двоеточия на двоеточие формула массива заработала Правда при протягивании на 60К строк комп Excel ушел в астрал на полчаса) Но результат удался - все посчитано. Правда при проверке данных выяснилось, что функцию МИН использовать некорректно, а нужна =МОДА.НСК. Но в принципе в массиве и МОДА работает
Следующим шагом были попытки преобразовать отдельной колонкой получившееся в МОДЕ время, разделяя на интервалы по 3 часа с выводом результата в формате "00:00 - 02:39", но на этом все застопорилось. Во-первых из-за того, что после применения массива любые правки в таблице грузят ЦП на 100% и работать становится невозможно, а во-вторых у меня даже идея пока не появляется, кроме банального перебора ЕСЛИ с подстановкой абсолютных значений:
По-видимому придется вспоминать VBA коды скорее всего :-( Но чтобы там запустить скрипт на 60-100К строк, все равно получим астрал...
_Boroda_, Вернулся я к этому вопросу сегодня. После редактирования столбца времени путем замены двоеточия на двоеточие формула массива заработала Правда при протягивании на 60К строк комп Excel ушел в астрал на полчаса) Но результат удался - все посчитано. Правда при проверке данных выяснилось, что функцию МИН использовать некорректно, а нужна =МОДА.НСК. Но в принципе в массиве и МОДА работает
Следующим шагом были попытки преобразовать отдельной колонкой получившееся в МОДЕ время, разделяя на интервалы по 3 часа с выводом результата в формате "00:00 - 02:39", но на этом все застопорилось. Во-первых из-за того, что после применения массива любые правки в таблице грузят ЦП на 100% и работать становится невозможно, а во-вторых у меня даже идея пока не появляется, кроме банального перебора ЕСЛИ с подстановкой абсолютных значений:
о чтобы там запустить скрипт на 60-100К строк, все равно получим астрал
Вы так уверенно это утверждаете, что я даже почти поверил Ну да ладно, на ваш конкретный вопрос ответ дан, остальное к этой теме уже не относится. Тем более, что Вы ничего и не спрашиваете
- Прочитайте Правила форума - Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)
о чтобы там запустить скрипт на 60-100К строк, все равно получим астрал
Вы так уверенно это утверждаете, что я даже почти поверил Ну да ладно, на ваш конкретный вопрос ответ дан, остальное к этой теме уже не относится. Тем более, что Вы ничего и не спрашиваете_Boroda_
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
