Столкнулся с необходимостью выборки данных по условию из двух таблиц с разным размером массивов данных и дальнейшим сложением результатов вычислений. Как ни крутил ни вертел СУММПРОИЗВ() и СУММЕСЛИ() вместе и врозь, к сожалению, ничего не получилось.
Пример во вложенном файле.
Заранее благодарен за помощь!
Здравствуйте!
Столкнулся с необходимостью выборки данных по условию из двух таблиц с разным размером массивов данных и дальнейшим сложением результатов вычислений. Как ни крутил ни вертел СУММПРОИЗВ() и СУММЕСЛИ() вместе и врозь, к сожалению, ничего не получилось.
Lyova, Добрый день. Вы словами напишите что нужно считать. Кардинально - загнать все источники в модель, по ней и считать. Стал чуть вникать в ребус - в январе есть 500 Работа1 - вся посчитана на Должность1, а как же Дложность2? Она дважды ведь тоже эту работу работала!
Если понять логику то думаю проще UDF сочинить.
Lyova, Добрый день. Вы словами напишите что нужно считать. Кардинально - загнать все источники в модель, по ней и считать. Стал чуть вникать в ребус - в январе есть 500 Работа1 - вся посчитана на Должность1, а как же Дложность2? Она дважды ведь тоже эту работу работала!
Если понять логику то думаю проще UDF сочинить.Hugo
В UDF можно сперва запомнить сколько часов каждой работы записано на каждую должность. Или/и наоборот - для каждой должности сколько часов каждой работы, что тут наверное удобнее. Это чтоб затем не бегать в ту базу для каждого значения. Затем например для Должность1 просматриваем данные за Январь: видим сумму, видим Работа1 вспоминаем есть ли у этой Должности такая работа если есть - сколько часов, умножаем-запоминаем идём дальше снова сумма, Работа5 - ищем, умножаем, суммируем... В конце выводим собранное в ячейку M19
Это я больше для себя записал, если позже будет время продолжить. Если приемлима UDF.
В UDF можно сперва запомнить сколько часов каждой работы записано на каждую должность. Или/и наоборот - для каждой должности сколько часов каждой работы, что тут наверное удобнее. Это чтоб затем не бегать в ту базу для каждого значения. Затем например для Должность1 просматриваем данные за Январь: видим сумму, видим Работа1 вспоминаем есть ли у этой Должности такая работа если есть - сколько часов, умножаем-запоминаем идём дальше снова сумма, Работа5 - ищем, умножаем, суммируем... В конце выводим собранное в ячейку M19
Это я больше для себя записал, если позже будет время продолжить. Если приемлима UDF.Hugo
Честно говоря только от Вас узнал, что такое UDF. Интернет говорит, что это Universal Disk Format. Не очень понимаю в чем суть. Как результат мне нужно получить файл эксель, который буду посылать людям, которые максимум, что умеют - это сложить две клетки.
По сути. Для должности 2 и любой другой не стал писать, поскольку, как я понимаю, там будет аналогичная формула, что и для первой. Поэтому для должности 1 написал простейшими умножить/сложить, чтобы была понятна логика, что нужно получить. Вначале нужно посчитать трудозатраты конкретной должности по каждой работе из Таблицы 1 (сложив трудозатраты по подработам, составляющим работу). Количество подработ и участие конкретной должности в разных работах может быть разным.
Далее полученные значения по каждой конкретной работе надо умножить на количество работ конкретного типа из Таблицы 2, соответственно. Далее полученные результаты умножения сложить. Таким образом, в результате посчитаем общее количество трудозатрат в часах на выполнение всех работ по каждой конкретной должности.
Hugo,
Честно говоря только от Вас узнал, что такое UDF. Интернет говорит, что это Universal Disk Format. Не очень понимаю в чем суть. Как результат мне нужно получить файл эксель, который буду посылать людям, которые максимум, что умеют - это сложить две клетки.
По сути. Для должности 2 и любой другой не стал писать, поскольку, как я понимаю, там будет аналогичная формула, что и для первой. Поэтому для должности 1 написал простейшими умножить/сложить, чтобы была понятна логика, что нужно получить. Вначале нужно посчитать трудозатраты конкретной должности по каждой работе из Таблицы 1 (сложив трудозатраты по подработам, составляющим работу). Количество подработ и участие конкретной должности в разных работах может быть разным.
Далее полученные значения по каждой конкретной работе надо умножить на количество работ конкретного типа из Таблицы 2, соответственно. Далее полученные результаты умножения сложить. Таким образом, в результате посчитаем общее количество трудозатрат в часах на выполнение всех работ по каждой конкретной должности.Lyova
Сообщение отредактировал Lyova - Пятница, 25.10.2024, 18:36
Пробовал, но, наверное, как-то неправильно. Моя проблема в том что мне нужно сложить, но предварительно перемножив. И то и другое надо сделать по критерию.
cmivadwot,
Пробовал, но, наверное, как-то неправильно. Моя проблема в том что мне нужно сложить, но предварительно перемножив. И то и другое надо сделать по критерию.Lyova
Сообщение отредактировал Lyova - Пятница, 25.10.2024, 18:40
Lyova, нужно так искать: Если файл отдавать другим без замены формул на значения - тогда придётся отдавать с включенными макросами, что может быть проблемой.
Lyova, нужно так искать: Если файл отдавать другим без замены формул на значения - тогда придётся отдавать с включенными макросами, что может быть проблемой.Hugo
cmivadwot, cmivadwot, Нет, не так. Вы учитываете только конкретные ячейки М5 и М7 (т.е. только для конкретных работ 2 и 5), а надо весь диапазон М5:Mn. В оригинальном файле количество работ 200. Я поэтому и в примере диапазоны и обозначил последним элементом с индексом N. Просто в примере нет смысла указывать все 200 работ если логика формулы для каждой из них будет одинаковая. Т.е. конкретная должность может встретиться и в подработах для работы, например, 74, 96, 115 и т.д.
cmivadwot, cmivadwot, Нет, не так. Вы учитываете только конкретные ячейки М5 и М7 (т.е. только для конкретных работ 2 и 5), а надо весь диапазон М5:Mn. В оригинальном файле количество работ 200. Я поэтому и в примере диапазоны и обозначил последним элементом с индексом N. Просто в примере нет смысла указывать все 200 работ если логика формулы для каждой из них будет одинаковая. Т.е. конкретная должность может встретиться и в подработах для работы, например, 74, 96, 115 и т.д.Lyova
Hugo, Как вариант, конечно. Но мне кажется на крайний случай. Я просто уверен, что это можно реализовать функциями. Наверняка, можно как-то массивными функциями. Я ими пользуюсь, но чаще уже проверенными и не до конца понимаю логику работы той или иной функции, когда она массивная.
Hugo, Как вариант, конечно. Но мне кажется на крайний случай. Я просто уверен, что это можно реализовать функциями. Наверняка, можно как-то массивными функциями. Я ими пользуюсь, но чаще уже проверенными и не до конца понимаю логику работы той или иной функции, когда она массивная.Lyova
Lyova, вот готова UDF. Для Windows only пока. Месяц не ищется, это зависит как укажете диапазон. Жёлтую можно тянуть как обычно по таблице. Можно что-то в коде дописать, например вернуть нули ))
Lyova, вот готова UDF. Для Windows only пока. Месяц не ищется, это зависит как укажете диапазон. Жёлтую можно тянуть как обычно по таблице. Можно что-то в коде дописать, например вернуть нули ))Hugo
Hugo, Скопировал Вашу формулу в оригинальный файл, протестировал. Все супер, работает как надо. Спасибо огромное!
Единственный ньюанс. Только поймите меня правильно, я не придираюсь.
Ваше решение очень узкоспециализированное, для конкретного случая (С моей колокольни). Если бы это были стандартные формулы, то, если бы вводные данные немного поменялись, например, добавилась бы еще одна таблица с исходными данными, то я уверен, что разобрался бы как модернизировать формулу самостоятельно. Я пользуюсь макрорекордером, убирая весь мусор, который он пишет, могу писать простейшие циклы, но как работает Ваш макрос я не понимаю в принципе. Т.е. ваше решение для очень-очень продвинутых юзеров, коих здесь на сайте, думаю, не большая доля.
В любом случае, еще раз, огромное спасибо!
Hugo, Скопировал Вашу формулу в оригинальный файл, протестировал. Все супер, работает как надо. Спасибо огромное!
Единственный ньюанс. Только поймите меня правильно, я не придираюсь.
Ваше решение очень узкоспециализированное, для конкретного случая (С моей колокольни). Если бы это были стандартные формулы, то, если бы вводные данные немного поменялись, например, добавилась бы еще одна таблица с исходными данными, то я уверен, что разобрался бы как модернизировать формулу самостоятельно. Я пользуюсь макрорекордером, убирая весь мусор, который он пишет, могу писать простейшие циклы, но как работает Ваш макрос я не понимаю в принципе. Т.е. ваше решение для очень-очень продвинутых юзеров, коих здесь на сайте, думаю, не большая доля.
Я правильно понимаю, что в экселе на маке эта формула работать не будет?
да, так не будет. Но можно дописать класс и будет. Про "поменялась таблица" - специально придумывал названия аргументов, вроде понятно... Три первых берёте из первой таблицы Таблица 1. Трудозатраты, специально делал три отдельных диапазона (с допстолбцом!) чтоб можно было их в любом порядке и не рядом брать. Следующие два столбца из Таблица 2. План выполннения работ, шт. и последний аргумент - Должность к которой нужно всё это подтянуть. По нулям - сделайте коррекцию последней строки кода так: [vba]
Я правильно понимаю, что в экселе на маке эта формула работать не будет?
да, так не будет. Но можно дописать класс и будет. Про "поменялась таблица" - специально придумывал названия аргументов, вроде понятно... Три первых берёте из первой таблицы Таблица 1. Трудозатраты, специально делал три отдельных диапазона (с допстолбцом!) чтоб можно было их в любом порядке и не рядом брать. Следующие два столбца из Таблица 2. План выполннения работ, шт. и последний аргумент - Должность к которой нужно всё это подтянуть. По нулям - сделайте коррекцию последней строки кода так: [vba]
Код
'If itogo Then Rabota = itogo 'Else Rabota = ""
[/vba] Т.е. должно работать только Rabota = itogoHugo
Hugo, Вы не обратили внимание, я написал не "поменялась таблица", а поменялись данные и таблица "добавилась", т.е. стало не 2 исходные таблицы, а, например, 3. С массивами, которые нужно указывать в вашей формуле проблем нет, все понятно. Насколько я понимаю, если добавиться еще одна таблица, например, аналогичная таблице 1, то просто надо добавить в макрос еще один цикл For, аналогичный первому в макросе?
Hugo, Вы не обратили внимание, я написал не "поменялась таблица", а поменялись данные и таблица "добавилась", т.е. стало не 2 исходные таблицы, а, например, 3. С массивами, которые нужно указывать в вашей формуле проблем нет, все понятно. Насколько я понимаю, если добавиться еще одна таблица, например, аналогичная таблице 1, то просто надо добавить в макрос еще один цикл For, аналогичный первому в макросе?
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
