Всем привет! Помогите с правильной формулой. Суть такая, есть таблица с листами по месяцам, на каждом листе идут сотрудники со своими KPI по которым считает ранги, есть сумма рангов которую и надо посчитать на отдельном листе.
Всем привет! Помогите с правильной формулой. Суть такая, есть таблица с листами по месяцам, на каждом листе идут сотрудники со своими KPI по которым считает ранги, есть сумма рангов которую и надо посчитать на отдельном листе.
есть сумма рангов которую и надо посчитать на отдельном листе
Рангов или рейтингов? У Вас ранги в колонках U месячных листов, а считаете на листе "ИТОГОВЫЕ РАНГИ" суммы значений из колонок V месячных листов, которые называются "Рейтинг".
В формуле ниже я ориентировался на колонки V (при необходимости исправить на U несложно). Формула для ячейки C2 листа "ИТОГОВЫЕ РАНГИ" и может быть протянута ниже: [vba]
[/vba] Я вставил эти формулы в ваш файл в колонку K на листе "ИТОГОВЫЕ РАНГИ". С вашей колонкой C имеются существенные расхождения, которые можете проверить самостоятельно (у меня - правильно )
есть сумма рангов которую и надо посчитать на отдельном листе
Рангов или рейтингов? У Вас ранги в колонках U месячных листов, а считаете на листе "ИТОГОВЫЕ РАНГИ" суммы значений из колонок V месячных листов, которые называются "Рейтинг".
В формуле ниже я ориентировался на колонки V (при необходимости исправить на U несложно). Формула для ячейки C2 листа "ИТОГОВЫЕ РАНГИ" и может быть протянута ниже: [vba]
[/vba] Я вставил эти формулы в ваш файл в колонку K на листе "ИТОГОВЫЕ РАНГИ". С вашей колонкой C имеются существенные расхождения, которые можете проверить самостоятельно (у меня - правильно )Gustav
Gustav, огромное спасибо. А можешь объяснить оно считает по каждому сотруднику независимо от того в какой ячейки он будет находиться? И обязательно чтобы везде имя и фамилия была одинаково написана?
Gustav, огромное спасибо. А можешь объяснить оно считает по каждому сотруднику независимо от того в какой ячейки он будет находиться? И обязательно чтобы везде имя и фамилия была одинаково написана?nvkz60053134
оно считает по каждому сотруднику независимо от того в какой ячейки он будет находиться?
В смысле, независимо от того, в какой ячейке он будет находиться в конкретном месяце? Конечно, независимо. Главное, чтобы эта ячейка находилась в колонке A, а в какой именно строке - абсолютно не важно. Ведь формула внутри себя сначала готовит виртуальный массив пар значений "сотрудник - сумма за месяц", последовательно складывая в него колонки A и V со всех месячных листов (в SQL эта операция называется UNION). Пустые строки при этом исключаем с помощью FILTER, чтобы массив не приобретал гигантский размер по вертикали.
А дальше совместно используемые функции SUM(FILTER(...)) вычисляют по данным этого массива общую сумму для каждого конкретного сотрудника (см. формулу выше). Чтобы заглянуть внутрь этого массива "сотрудник - сумма за месяц", можно справочно визуализировать его на рабочем листе. Для этого нужно слегка изменить предыдущую формулу и ввести ее "где-то сбоку" в одну ячейку, следя за тем, чтобы в соседней колонке справа и в нужном количестве строк ниже при этом не было занятых ячеек - чтобы формула смогла "саморазвернуться": [vba]
[/vba] Я вставил эту формулу в ячейку W2 листа "ИТОГОВЫЕ РАНГИ" по ссылке выше.
P.S. Как вариант решения - можно использовать этот визуализированный массив на рабочем листе как промежуточный набор данных для применения по сотрудникам более простых формул с функцией SUMIF(СУММЕСЛИ). Тогда протягиваемая формула для ячейки C2 будет совсем простой:
оно считает по каждому сотруднику независимо от того в какой ячейки он будет находиться?
В смысле, независимо от того, в какой ячейке он будет находиться в конкретном месяце? Конечно, независимо. Главное, чтобы эта ячейка находилась в колонке A, а в какой именно строке - абсолютно не важно. Ведь формула внутри себя сначала готовит виртуальный массив пар значений "сотрудник - сумма за месяц", последовательно складывая в него колонки A и V со всех месячных листов (в SQL эта операция называется UNION). Пустые строки при этом исключаем с помощью FILTER, чтобы массив не приобретал гигантский размер по вертикали.
А дальше совместно используемые функции SUM(FILTER(...)) вычисляют по данным этого массива общую сумму для каждого конкретного сотрудника (см. формулу выше). Чтобы заглянуть внутрь этого массива "сотрудник - сумма за месяц", можно справочно визуализировать его на рабочем листе. Для этого нужно слегка изменить предыдущую формулу и ввести ее "где-то сбоку" в одну ячейку, следя за тем, чтобы в соседней колонке справа и в нужном количестве строк ниже при этом не было занятых ячеек - чтобы формула смогла "саморазвернуться": [vba]
[/vba] Я вставил эту формулу в ячейку W2 листа "ИТОГОВЫЕ РАНГИ" по ссылке выше.
P.S. Как вариант решения - можно использовать этот визуализированный массив на рабочем листе как промежуточный набор данных для применения по сотрудникам более простых формул с функцией SUMIF(СУММЕСЛИ). Тогда протягиваемая формула для ячейки C2 будет совсем простой:
И обязательно чтобы везде имя и фамилия была одинаково написана?
В общем, да. Чтобы не нагружать формулу элементами для неполного поиска - символами подстановки и т.п. А чтобы быть уверенным, что имена и фамилии всех сотрудников написаны одинаково на всех месячных листах, можно еще раз немного изменить окончание формулы для того, чтобы она для проверки выводила полный уникальный список всех сотрудников, хотя бы раз упомянутых на месячных листах. И тогда, если какой-либо сотрудник в силу опечатки встретится в списке более одного раза (возможно, с досадным отличием в одной букве), это всегда будет заметно визуально.
Новая редакция формулы - для вывода по алфавиту уникальных имен сотрудников с их суммами по рангам: [vba]
И обязательно чтобы везде имя и фамилия была одинаково написана?
В общем, да. Чтобы не нагружать формулу элементами для неполного поиска - символами подстановки и т.п. А чтобы быть уверенным, что имена и фамилии всех сотрудников написаны одинаково на всех месячных листах, можно еще раз немного изменить окончание формулы для того, чтобы она для проверки выводила полный уникальный список всех сотрудников, хотя бы раз упомянутых на месячных листах. И тогда, если какой-либо сотрудник в силу опечатки встретится в списке более одного раза (возможно, с досадным отличием в одной букве), это всегда будет заметно визуально.
Новая редакция формулы - для вывода по алфавиту уникальных имен сотрудников с их суммами по рангам: [vba]
Я вставил эти формулы в ваш файл в колонку K на листе "ИТОГОВЫЕ РАНГИ". С вашей колонкой C имеются существенные расхождения, которые можете проверить самостоятельно (у меня - правильно )
Знаю, т.к. использовать копирование ячеек через "="
P.S. Как вариант решения - можно использовать этот визуализированный массив на рабочем листе как промежуточный набор данных для применения по сотрудникам более простых формул с функцией SUMIF(СУММЕСЛИ). Тогда протягиваемая формула для ячейки C2 будет совсем простой:
=СУММЕСЛИ(W:W; A2; X:X)
Я правильно понимаю, что для работы по этой формуле мне сперва нужно объединить данные со всех листов в 1 и потом через СУММЕСЛИ будет работать?
И еще вопрос. Для дальнейшего использования твоей формулы мне нужно будет поменять только название месяцев и оставить тот же формат таблицы чтобы все нормально работало? Дальше листы будут называться Январь 2025 к примеру и в формуле я уже буду писать так же.
Я вставил эти формулы в ваш файл в колонку K на листе "ИТОГОВЫЕ РАНГИ". С вашей колонкой C имеются существенные расхождения, которые можете проверить самостоятельно (у меня - правильно )
Знаю, т.к. использовать копирование ячеек через "="
P.S. Как вариант решения - можно использовать этот визуализированный массив на рабочем листе как промежуточный набор данных для применения по сотрудникам более простых формул с функцией SUMIF(СУММЕСЛИ). Тогда протягиваемая формула для ячейки C2 будет совсем простой:
=СУММЕСЛИ(W:W; A2; X:X)
Я правильно понимаю, что для работы по этой формуле мне сперва нужно объединить данные со всех листов в 1 и потом через СУММЕСЛИ будет работать?
И еще вопрос. Для дальнейшего использования твоей формулы мне нужно будет поменять только название месяцев и оставить тот же формат таблицы чтобы все нормально работало? Дальше листы будут называться Январь 2025 к примеру и в формуле я уже буду писать так же.nvkz60053134
Сообщение отредактировал nvkz60053134 - Четверг, 12.12.2024, 07:45
Я правильно понимаю, что для работы по этой формуле мне сперва нужно объединить данные со всех листов в 1 и потом через СУММЕСЛИ будет работать?
Правильно. Сейчас таким "объединителем" является диапазон Q:R на листе "ИТОГОВЫЕ РАНГИ", у него еще сверху я добавил заголовок "Список персональных строк из месяцев для суммирования (по типу "СУММЕСЛИ")". В ячейку Q2 (ранее была W2) для формирования такого списка введена формула из моего сообщения №4 данной темы.
Стратегически подобный "объединитель" может располагаться как сейчас - на том же рабочем листе, где для сотрудников будут использоваться формулы с СУММЕСЛИ (при желании колонки списка можно скрыть). Либо можно вынести "объединитель" на отдельный рабочий лист (служебный, вспомогательный), который также можно скрыть при необходимости. Я, наверное, даже больше склоняюсь в пользу варианта с отдельным листом, потому что на нём можно разместить и другую вспомогательную информацию. Например, список листов-месяцев, с которых учитываются данные при суммировании.
АНОНС: чуть позже я добавлю в это сообщение формулу для построения объединителя, в котором список листов-месяцев будет браться из вспомогательного столбца. Элементы списка, размещенные в отдельных ячейках такого столбца, редактировать значительно проще, чем каждый раз "шуровать" внутри формулы, с риском ненароком повредить что-нибудь в ней.
P.S. Сделано. Допустим, список листов-месяцев будет располагаться на том же листе, что и объединитель, в колонке X, начиная с ячейки X2 (X1 оставим, например, для служебного заголовка). При этом ячейки не обязаны быть заполнены подряд, т.е. возможно нахождение пустых ячеек между элементами списка - функция FILTER уберет пустые значения, а UNIQUE исключит дубликаты.
Новая редакция формулы для "объединителя" может быть такой (после исключения внешней функции LET - она здесь лишняя): [vba]
[/vba] А дальше новые листы-месяцы, например, "Январь 2025" просто добавляем в колонку X, без необходимости лезть внутрь формулы. Старые же месяцы, ставшие ненужными, просто удаляются очисткой соответствующих ячеек столбца X. При этом оставшиеся непустые ячейки могут оставаться на своих текущих местах, без необходимости какой-либо перегруппировки внутри столбца X.
Я правильно понимаю, что для работы по этой формуле мне сперва нужно объединить данные со всех листов в 1 и потом через СУММЕСЛИ будет работать?
Правильно. Сейчас таким "объединителем" является диапазон Q:R на листе "ИТОГОВЫЕ РАНГИ", у него еще сверху я добавил заголовок "Список персональных строк из месяцев для суммирования (по типу "СУММЕСЛИ")". В ячейку Q2 (ранее была W2) для формирования такого списка введена формула из моего сообщения №4 данной темы.
Стратегически подобный "объединитель" может располагаться как сейчас - на том же рабочем листе, где для сотрудников будут использоваться формулы с СУММЕСЛИ (при желании колонки списка можно скрыть). Либо можно вынести "объединитель" на отдельный рабочий лист (служебный, вспомогательный), который также можно скрыть при необходимости. Я, наверное, даже больше склоняюсь в пользу варианта с отдельным листом, потому что на нём можно разместить и другую вспомогательную информацию. Например, список листов-месяцев, с которых учитываются данные при суммировании.
АНОНС: чуть позже я добавлю в это сообщение формулу для построения объединителя, в котором список листов-месяцев будет браться из вспомогательного столбца. Элементы списка, размещенные в отдельных ячейках такого столбца, редактировать значительно проще, чем каждый раз "шуровать" внутри формулы, с риском ненароком повредить что-нибудь в ней.
P.S. Сделано. Допустим, список листов-месяцев будет располагаться на том же листе, что и объединитель, в колонке X, начиная с ячейки X2 (X1 оставим, например, для служебного заголовка). При этом ячейки не обязаны быть заполнены подряд, т.е. возможно нахождение пустых ячеек между элементами списка - функция FILTER уберет пустые значения, а UNIQUE исключит дубликаты.
Новая редакция формулы для "объединителя" может быть такой (после исключения внешней функции LET - она здесь лишняя): [vba]
[/vba] А дальше новые листы-месяцы, например, "Январь 2025" просто добавляем в колонку X, без необходимости лезть внутрь формулы. Старые же месяцы, ставшие ненужными, просто удаляются очисткой соответствующих ячеек столбца X. При этом оставшиеся непустые ячейки могут оставаться на своих текущих местах, без необходимости какой-либо перегруппировки внутри столбца X.Gustav
Например, список листов-месяцев, с которых учитываются данные при суммировании.
Привет. Тут не могу сообразить. Т.е. для суммирования мне нужно переписать названия листов в отдельный список? Или как это должно выглядеть?nvkz60053134
Сообщение отредактировал nvkz60053134 - Среда, 18.12.2024, 10:26
Допустим, список листов-месяцев будет располагаться на том же листе, что и объединитель, в колонке X, начиная с ячейки X2 (X1 оставим, например, для служебного заголовка). При этом ячейки не обязаны быть заполнены подряд, т.е. возможно нахождение пустых ячеек между элементами списка
Содержимое ячеек колонки X, начиная с X1 и вниз по колонке: [vba]
Код
ЛИСТЫ-МЕСЯЦЫ (служебный заголовок в ячейке X1) Май Июнь
Допустим, список листов-месяцев будет располагаться на том же листе, что и объединитель, в колонке X, начиная с ячейки X2 (X1 оставим, например, для служебного заголовка). При этом ячейки не обязаны быть заполнены подряд, т.е. возможно нахождение пустых ячеек между элементами списка
Содержимое ячеек колонки X, начиная с X1 и вниз по колонке: [vba]
Код
ЛИСТЫ-МЕСЯЦЫ (служебный заголовок в ячейке X1) Май Июнь
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
)
