Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Динамика изменения количества во времени - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Динамика изменения количества во времени (Формулы/Formulas)
Динамика изменения количества во времени
book Дата: Вторник, 01.02.2022, 22:04 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 144
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
Добрый день!

Есть таблица, в которой ведется работа по вакансиям. Для каждой вакансии (уникальный id) записывается изменение ее статуса (Открыта, Приостановлена, Закрыта) во времени. Список и название статусов менять нельзя. Статусы могут повторяться. Вакансия может быть Приостановлена и Открыта несколько раз. При подсчете необходимо это учитывать.

Задача подсчитать количество вакансий в работе исторически на конец каждого месяца. Я начал решать эту задачу, но получил двойной счет. Если статусы повторяются, то я получаю неверное количество.

В решении использую PQ и сводные таблицы. Прошу подсказать, как обойти двойной счет.

Более подробно изложил в файле. Файл прилагаю. В файле вакансия одна, но реально их несколько сотен.

Заранее спасибо.

PS
Уважаемые администраторы, прошу прощение за, возможно, не самое удачное название темы.
К сообщению приложен файл: 0392197.xlsx(10.8 Kb)


--
С уважением,
Андрей.


Сообщение отредактировал book - Вторник, 01.02.2022, 22:06
 
Ответить
СообщениеДобрый день!

Есть таблица, в которой ведется работа по вакансиям. Для каждой вакансии (уникальный id) записывается изменение ее статуса (Открыта, Приостановлена, Закрыта) во времени. Список и название статусов менять нельзя. Статусы могут повторяться. Вакансия может быть Приостановлена и Открыта несколько раз. При подсчете необходимо это учитывать.

Задача подсчитать количество вакансий в работе исторически на конец каждого месяца. Я начал решать эту задачу, но получил двойной счет. Если статусы повторяются, то я получаю неверное количество.

В решении использую PQ и сводные таблицы. Прошу подсказать, как обойти двойной счет.

Более подробно изложил в файле. Файл прилагаю. В файле вакансия одна, но реально их несколько сотен.

Заранее спасибо.

PS
Уважаемые администраторы, прошу прощение за, возможно, не самое удачное название темы.

Автор - book
Дата добавления - 01.02.2022 в 22:04
bigor Дата: Среда, 02.02.2022, 09:20 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 681
Репутация: 139 ±
Замечаний: 0% ±

нет
Добрый
А вдруг так пойдет
Код
=MAX(SUM(G22:G24)+F25;0)
 
Ответить
СообщениеДобрый
А вдруг так пойдет
Код
=MAX(SUM(G22:G24)+F25;0)

Автор - bigor
Дата добавления - 02.02.2022 в 09:20
book Дата: Четверг, 03.02.2022, 10:19 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 144
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
bigor, спасибо за желание помочь.

Но если бы все было так просто... В реальной таблице много вакансий. Не стоит выбор между "0" и "-1". как в примере.
Задача - так организовать логику обработки исходной таблицы, чтобы не было повторного счета. Один из вариантов (может, есть и другие) - надо сравнивать текущий статус и предыдущий и на основе результата сравнения считать количество "0" или "-1".

Можно ли решить такую задачу с помощью PQ?
Выкладываю новый файл с уточнением вопроса.
К сообщению приложен файл: 1222567.xlsx(11.8 Kb)


--
С уважением,
Андрей.
 
Ответить
Сообщениеbigor, спасибо за желание помочь.

Но если бы все было так просто... В реальной таблице много вакансий. Не стоит выбор между "0" и "-1". как в примере.
Задача - так организовать логику обработки исходной таблицы, чтобы не было повторного счета. Один из вариантов (может, есть и другие) - надо сравнивать текущий статус и предыдущий и на основе результата сравнения считать количество "0" или "-1".

Можно ли решить такую задачу с помощью PQ?
Выкладываю новый файл с уточнением вопроса.

Автор - book
Дата добавления - 03.02.2022 в 10:19
Pelena Дата: Четверг, 03.02.2022, 10:44 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 18555
Репутация: 4202 ±
Замечаний: ±

Excel 2016 & Mac Excel
надо сравнивать текущий статус и предыдущий

тогда формулу лучше писать в выгруженной из PQ таблице в доп. столбце. Предвидя вопрос, обновление запроса формулу не поломает, если правильно написать
К сообщению приложен файл: 6116551.xlsx(13.1 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
надо сравнивать текущий статус и предыдущий

тогда формулу лучше писать в выгруженной из PQ таблице в доп. столбце. Предвидя вопрос, обновление запроса формулу не поломает, если правильно написать

Автор - Pelena
Дата добавления - 03.02.2022 в 10:44
book Дата: Четверг, 03.02.2022, 10:47 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 144
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
Pelena, но формулу придется каждый раз "тянуть" вниз руками. Верно?


--
С уважением,
Андрей.
 
Ответить
СообщениеPelena, но формулу придется каждый раз "тянуть" вниз руками. Верно?

Автор - book
Дата добавления - 03.02.2022 в 10:47
Pelena Дата: Четверг, 03.02.2022, 10:49 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 18555
Репутация: 4202 ±
Замечаний: ±

Excel 2016 & Mac Excel
Почему? Запрос PQ ведь выгружается в умную таблицу, а там формулы автоматически распространяются на весь столбец.

Или Вы не выгружаете сам запрос на лист, а сразу строите сводную?


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПочему? Запрос PQ ведь выгружается в умную таблицу, а там формулы автоматически распространяются на весь столбец.

Или Вы не выгружаете сам запрос на лист, а сразу строите сводную?

Автор - Pelena
Дата добавления - 03.02.2022 в 10:49
book Дата: Четверг, 03.02.2022, 11:06 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 144
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
Pelena, Вы правы: выгружаю запрос на лист, в файле Excel - два листа: с запросом и второй - со сводной.
А можно будет научить формулу работать для каждого id отдельно?
Должны ли быть отсортированы строки для правильной работы формулы?
PS Для простоты в файле примера показал только одну вакансию.


--
С уважением,
Андрей.
 
Ответить
СообщениеPelena, Вы правы: выгружаю запрос на лист, в файле Excel - два листа: с запросом и второй - со сводной.
А можно будет научить формулу работать для каждого id отдельно?
Должны ли быть отсортированы строки для правильной работы формулы?
PS Для простоты в файле примера показал только одну вакансию.

Автор - book
Дата добавления - 03.02.2022 в 11:06
Pelena Дата: Четверг, 03.02.2022, 11:51 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 18555
Репутация: 4202 ±
Замечаний: ±

Excel 2016 & Mac Excel
Должны ли быть отсортированы строки для правильной работы формулы?
Строки должны быть отсортированы по дате, так же как в примере

для каждого id отдельно
да, в моём файле это предусмотрено


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Должны ли быть отсортированы строки для правильной работы формулы?
Строки должны быть отсортированы по дате, так же как в примере

для каждого id отдельно
да, в моём файле это предусмотрено

Автор - Pelena
Дата добавления - 03.02.2022 в 11:51
book Дата: Четверг, 03.02.2022, 12:37 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 144
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
да, в моём файле это предусмотрено

Спасибо. Я попробую применить Вашу формулу на "живых" данных.


--
С уважением,
Андрей.
 
Ответить
Сообщение
да, в моём файле это предусмотрено

Спасибо. Я попробую применить Вашу формулу на "живых" данных.

Автор - book
Дата добавления - 03.02.2022 в 12:37
book Дата: Четверг, 03.02.2022, 15:56 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 144
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
Pelena, верно ли я понимаю, что эта формула (фрагмент Вашей формулы) "пробегает всю таблицу сверху вниз и ищет самый последний статус? т.е. возращает значение из самой "нижней" ячейки в столбце С ?
Учитывая только те строки, в которых id = b12 (в данном случае)
Код
=ПРОСМОТР(;-1/($B$7:B11=B12);$C$7:C11)


--
С уважением,
Андрей.


Сообщение отредактировал book - Четверг, 03.02.2022, 15:58
 
Ответить
СообщениеPelena, верно ли я понимаю, что эта формула (фрагмент Вашей формулы) "пробегает всю таблицу сверху вниз и ищет самый последний статус? т.е. возращает значение из самой "нижней" ячейки в столбце С ?
Учитывая только те строки, в которых id = b12 (в данном случае)
Код
=ПРОСМОТР(;-1/($B$7:B11=B12);$C$7:C11)

Автор - book
Дата добавления - 03.02.2022 в 15:56
Pelena Дата: Четверг, 03.02.2022, 18:27 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 18555
Репутация: 4202 ±
Замечаний: ±

Excel 2016 & Mac Excel
Да, всё верно. В умной таблице она будет немного по-другому выглядеть, т.к. там используются имена столбцов


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеДа, всё верно. В умной таблице она будет немного по-другому выглядеть, т.к. там используются имена столбцов

Автор - Pelena
Дата добавления - 03.02.2022 в 18:27
book Дата: Четверг, 03.02.2022, 18:40 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 144
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
Pelena, еще раз спасибо.
Мне потребуется некоторое время, чтобы внедрить Вашу идею, т.к. реально статусов больше, и условия обора сложнее, чем в примере.
Я обязательно отпишусь здесь в теме о результатах или задам новые вопросы. :)


--
С уважением,
Андрей.
 
Ответить
СообщениеPelena, еще раз спасибо.
Мне потребуется некоторое время, чтобы внедрить Вашу идею, т.к. реально статусов больше, и условия обора сложнее, чем в примере.
Я обязательно отпишусь здесь в теме о результатах или задам новые вопросы. :)

Автор - book
Дата добавления - 03.02.2022 в 18:40
book Дата: Четверг, 03.02.2022, 22:30 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 144
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
Pelena, на основе Вашей идеи составил новую формулу, которая учитывает большее количество статусов и разные их вариации.
Если будет возможность, посмотрите, пожалуйста, нет ли просчета в логике.
Заранее спасибо.
К сообщению приложен файл: 6116551-2.xlsx(74.9 Kb)


--
С уважением,
Андрей.
 
Ответить
СообщениеPelena, на основе Вашей идеи составил новую формулу, которая учитывает большее количество статусов и разные их вариации.
Если будет возможность, посмотрите, пожалуйста, нет ли просчета в логике.
Заранее спасибо.

Автор - book
Дата добавления - 03.02.2022 в 22:30
Pelena Дата: Четверг, 03.02.2022, 22:41 | Сообщение № 14
Группа: Админы
Ранг: Местный житель
Сообщений: 18555
Репутация: 4202 ±
Замечаний: ±

Excel 2016 & Mac Excel
Вот в этой части
Код
ЕСЛИ(И(СУММЕСЛИМН($D$5:D7;$B$5:B7;B7)<0;C6="Открыта");-1;

если id могут быть вперемешку, то вместо С6 надо использовать функцию ПРОСМОТР из предыдущих постов, чтобы найти последний статус, соответствующий id.


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВот в этой части
Код
ЕСЛИ(И(СУММЕСЛИМН($D$5:D7;$B$5:B7;B7)<0;C6="Открыта");-1;

если id могут быть вперемешку, то вместо С6 надо использовать функцию ПРОСМОТР из предыдущих постов, чтобы найти последний статус, соответствующий id.

Автор - Pelena
Дата добавления - 03.02.2022 в 22:41
book Дата: Четверг, 03.02.2022, 23:28 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 144
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
если id могут быть вперемешку

Это могу обойти в PQ: сделать сортировку там несложно.

Благодарю за помощь.


--
С уважением,
Андрей.
 
Ответить
Сообщение
если id могут быть вперемешку

Это могу обойти в PQ: сделать сортировку там несложно.

Благодарю за помощь.

Автор - book
Дата добавления - 03.02.2022 в 23:28
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Динамика изменения количества во времени (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2022 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!