Определение необходимости VBA в Excel или достаточно ли сводной диаграммы / таблицы

Question

Хотелось бы, чтобы у меня был более точный описательный заголовок для этого вопроса, но я не очень хорошо разбираюсь в электронных таблицах Excel или VBA (я в основном разработчик на Java и мало играю в мире Microsoft).

Я пытаюсь использовать электронную таблицу Excel, чтобы чего-то добиться. Мне нужно определить, смогу ли я добраться туда с помощью сводной таблицы, сводной диаграммы или какой-либо другой встроенной функциональности ... или мне нужно пойти по пути написания собственного кода VBA (или, возможно, использовать другую платформу вообще).

Цель электронной таблицы - помочь в создании расписаний для организации (клуб Toastmasters). У этой организации есть список членов, она собирается еженедельно, и разные члены назначаются на разные роли на данном собрании.

Моя таблица выглядит следующим образом:

Первая вкладка

Один столбец ... список имен, представляющих список членов.

Вторая вкладка

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

Третья вкладка (может быть, несколько вкладок?)

Вот смысл всего этого. Для каждой из возможных ролей собрания я хотел бы увидеть, какие члены являются наиболее "просроченными" для этой роли. По сути, я хочу получить список всех членов клуба, отсортированный по порядку того, сколько времени прошло с тех пор, как они в последний раз исполняли эту роль. Люди, которые никогда не служили в этой роли, будут отсортированы вверху списка.

Являются ли эти данные третьей вкладки чем-то, что может быть достигнуто с помощью сводной таблицы и т.д. ... или я неправильно понимаю цели и ограничения этих инструментов?

score 0 · Answer 1 · 2011-03-09T19:38:42

Да, сводной таблицы должно быть достаточно, если вы правильно настроите свои данные на второй вкладке:

Meeting       Member      Role      Weeks since
1/12/2010     Member 1    Role 1    8 [ =(TODAY()-A2)/7 ]
1/12/2010     Member 2    Role 2    8
1/12/2010     Member 3    Role 3    8
1/12/2010     Member 4    Role 4    8
1/12/2010     Member 5    Role 5    8
8/12/2010     Member 1    Role 1    7
8/12/2010     Member 3    Role 2    7
8/12/2010     Member 7    Role 3    7
8/12/2010     Member 6    Role 4    7
8/12/2010     Member 4    Role 5    7
15/12/2010    Member 1    Role 1    6
15/12/2010    Member 3    Role 2    6
15/12/2010    Member 4    Role 3    6
15/12/2010    Member 2    Role 4    6
15/12/2010    Member 6    Role 5    6
22/12/2010    Member 1    Role 1    5
22/12/2010    Member 2    Role 2    5
22/12/2010    Member 5    Role 3    5
22/12/2010    Member 3    Role 4    5
22/12/2010    Member 4    Role 5    5
29/12/2010    Member 2    Role 1    4
29/12/2010    Member 3    Role 2    4
29/12/2010    Member 4    Role 3    4
29/12/2010    Member 5    Role 4    4
29/12/2010    Member 5    Role 5    4
...

Затем вы можете создать свою сводную таблицу следующим образом:

Фильтр отчетов: нет

Метки столбцов: нет

Метки строк: роль, участник

Значения: MIN недель с

Затем:

Удалить ненужные итоговые поля
Отредактируйте настройки поля Участник и выберите Макет и Печать / Показать элементы без данных. Это покажет участников, которые никогда не служили роли.
Измените порядок сортировки на Min of Weeks, начиная с Smallest и заканчивая Largest.

Ваша сводная таблица должна выглядеть примерно так:

Role        Member          Min of Weeks since
Role 1      Member 4        
            Member 7        
            Member 3        
            Member 5        
            Member 6        
            Member 2        4
            Member 1        5
Role 2      Member 4        
            Member 7        
            Member 6        
            Member 1        
            Member 5        
            Member 3        4
            Member 2        5
Role 3      Member 6        
            Member 1        
            Member 2        
            Member 4        4
            Member 5        5
            Member 7        7
            Member 3        8
Role 4      Member 7        
            Member 1        
            Member 5        4
            Member 3        5
            Member 2        6
            Member 6        7
            Member 4        8
Role 5      Member 7        
            Member 1        
            Member 3        
            Member 2        
            Member 5        4
            Member 4        5
            Member 6        6

Проверено в Excel 2010.

score 0 · Answer 2 · 2011-03-09T19:38:42

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

На листе добавьте столбцы для всех ролей. В моем примере есть две роли: президент и секретарь, поэтому полковник A - это имя, полковник B - президент, а полковник C - секретарь. В B2 положи эту формулу

{=MAX((OFFSET(Meetings!$A$1,1,MATCH(B$1,Meetings!$B$1:$C$1,FALSE),20,1)=$A2)*(OFFSET(Meetings!$A$2,0,0,20,1)))}

В этой формуле есть некоторые предположения, которые я бы определенно сделал более надежными, но это хорошее доказательство концепции. Вы вводите это с помощью Control+Shift+Enter, потому что это формула массива. Он вернет максимальную дату, которую он найдет для этого имени в колонке «Президент». Заполните эту формулу вниз и вправо в соответствии с вашими данными.

Теперь, имея данные в списке, создайте сводную таблицу для каждого столбца "Роль". Укажите имя в области строк и роль (например, "Президент") в области данных. Это, вероятно, по умолчанию для графа президента. Щелкните правой кнопкой мыши на этом поле и выберите "Настройки поля". Измените Summarize By на Max и установите числовой формат даты.

Далее щелкните правой кнопкой мыши по одному из имен и выберите настройки поля. Нажмите кнопку Дополнительно. Измените параметры автосортировки на Ascending on Max of President.

Результатом должен быть список отсортированных имен. Имена наверху стали самыми длинными, не будучи президентом.

Нет ничего лучше, чем пример. Загрузите его здесь http://www.dailydoseofexcel.com/excel/Rotary.zip. Он содержит 1 книгу Excel 2003, в которой показано то, что я описал выше.

score 0 · Accepted Answer · 2011-03-09T19:38:42

Поправь меня, если я неправильно понял. Ваши данные таковы:

Первая вкладка (лист):

Name
----
Alice
Bruce
Chuck
Derek
Emily

Вторая вкладка:

Date        Role1    Role2    Role3
--------    -----    -----    -----
13-01-11    Alice    Bruce    Chuck (Each selected from dropdown)
14-01-11    Derek    Emily    Bruce
15-01-11    Alice    Chuck    Derek
16-01-11    Emily    Bruce    Alice
17-01-11    Chuck    Derek    Bruce
18-01-11    Chuck    Bruce    Alice
19-01-11    Alice    Chuck    Bruce

Если это так, сводная таблица не будет применима, если вы не конвертируете ее в форматы, которые написал Майк.

Вместо этого (РЕДАКТИРОВАТЬ: после разделения каждого столбца роли на одной и той же вкладке и сортировки их по алфавиту), т.е. создайте вторую вкладку следующим образом (cut-paste-sort):

Date        Role1
--------    -----
13-01-11    Alice
15-01-11    Alice
19-01-11    Alice
17-01-11    Chuck
18-01-11    Chuck
14-01-11    Derek
16-01-11    Emily

Date        Role2
--------    -----
13-01-11    Bruce
16-01-11    Bruce
18-01-11    Bruce
15-01-11    Chuck
19-01-11    Chuck
17-01-11    Derek
14-01-11    Emily etc

Затем вы можете создать вкладки для каждой роли (или разделить их на одной вкладке) и использовать функцию LOOKUP, чтобы увидеть, когда участник в последний раз выполнял эту роль. Например, для Role1 вкладка 3 должна быть такой:

Name     Last Done (Date)
----     -------- 
Alice    19-01-11 [=LOOKUP(A2,Sheet2!$B$2:$B$8,Sheet2!$A$2:$A$8)]
Bruce    N/A
Chuck    18-01-11
Derek    14-01-11
Emily    16-01-11

Если вы сортируете от самых новых до самых старых, вы можете увидеть, кто никогда не выполнял роль сверху, а кто делал последний раз снизу.

Вы также можете добавить столбцы для того, сколько раз кто-то выполнял определенную роль, используя COUNT, и сколько дней / недель прошло с последнего раза, используя TODAY и получая разницу.

Скопируйте и вставьте те же функции для других ролей (если их не много) на разных вкладках.

score 0 · Answer 4 · 2011-03-09T19:38:42

Немного другой подход здесь, в трех частях:

Таблица ввода данных - каждый сеанс, кто-то будет вводить роли игроков и ролей. Таблица отсортирована по дате (по убыванию).
Таблица перекрестных ссылок - каждое имя имеет перекрестную ссылку на самую последнюю дату, которую они служили в каждой роли
Следующий список

Это очень низкие эксплуатационные расходы. Как только он настроен, единственной работой является ввод трех имен каждый день и расширение диапазона дат по мере необходимости.

Таблица ввода данных

Вот таблица (A1:D10). Примечание: здесь нет формул. Каждую неделю введите три имени на основе следующего списка ниже.

Date    Role1   Role2   Role3  
21-Jan            
20-Jan            
19-Jan  Alice   Chuck   Bruce  
18-Jan  Chuck   Bruce   Derek  
17-Jan  Chuck   Derek   Alice  
16-Jan  Emily   Bruce   Chuck  
15-Jan  Alice   Chuck   Emily  
14-Jan  Derek   Emily   Alice  
13-Jan  Alice   Bruce   Emily

Таблица перекрестных ссылок

В таблице перекрестных ссылок (A14:D19) ниже будет отображаться самый последний день, когда каждое имя выполняло каждую роль.

=IF(ISNA(MATCH($A15,B$2:B$10,0)),"",INDEX($A$2:$A$10,MATCH($A15,B$2:B$10,0)))

Name    Role1   Role2   Role3   Role4  
Alice   19-Jan          17-Jan  18-Jan  
Bruce           18-Jan  19-Jan  14-Jan  
Chuck   18-Jan  19-Jan  16-Jan  13-Jan  
Derek   14-Jan  17-Jan  18-Jan  19-Jan  
Emily   16-Jan  14-Jan  15-Jan  17-Jan

Если все имена выполнили роль хотя бы один раз, она предоставит самую последнюю соответствующую дату. Пустая ячейка, если имя никогда не выполняло роль

Следующий список

Раздел Next Up (A12:D12) ищет самую раннюю дату в столбце каждой роли и предоставляет соответствующее имя. Он работает, находя минимальное число в столбце и затем возвращая его строку. Пустые ячейки (никогда не выполняются) имеют приоритет.

=IF(ISNA(MATCH("",B$15:B$19,0)),INDEX($A$15:$A$19,MATCH(MIN(B$15:B$19),B$15:B$19,0)),INDEX($A$15:$A$19,MATCH("",B$15:B$19,0)))

Next-Up Role1   Role2   Role3  
Name    Bruce   Alice   Emily

^{Все формулы можно перетащить вниз и вправо. Если вы хотите расширить диапазон дат, вставьте дополнительные строки ниже строки 2, и формулы продолжат работать. Например, чтобы продлить до 31 января, выберите строки 3:12 и вставьте, затем в ячейки A13:A14 и перетащите маркер вверх до ячейки A2.}

Сейчас выбран русский

Определение необходимости VBA в Excel или достаточно ли сводной диаграммы / таблицы

Первая вкладка

Вторая вкладка

Третья вкладка (может быть, несколько вкладок?)

4 ответа4

Таблица ввода данных

Таблица перекрестных ссылок

Следующий список

Всё ещё ищете ответ? Посмотрите другие вопросы с метками microsoft-excel vba worksheet-function pivot-table.

Определение необходимости VBA в Excel или достаточно ли сводной диаграммы / таблицы

Первая вкладка

Вторая вкладка

Третья вкладка (может быть, несколько вкладок?)

4 ответа4

Таблица ввода данных

Таблица перекрестных ссылок

Следующий список

Всё ещё ищете ответ? Посмотрите другие вопросы с метками microsoft-excel vba worksheet-function pivot-table.

Похожие