Мне жаль, что у меня не было более точно описательного заголовка для этого вопроса, но я не очень хорошо осведомлен с электронными таблицами Excel или VBA (я - прежде всего, Java-разработчик и не играю очень в мире Microsoft).
Я пытаюсь использовать электронную таблицу Excel для выполнения чего-то. Я должен определить, могу ли я добраться там с PivotTable, PivotChart или некоторой другой встроенной функциональностью... или должен ли я спуститься по пути написания пользовательского кода VBA (или возможно использование другой платформы в целом).
Цель электронной таблицы состоит в том, чтобы помочь создать расписания для организации (клуб Toastmasters). Эта организация имеет список участников, она встречается еженедельно, и различные участники присвоены различным ролям в данной встрече.
Моя электронная таблица похожа на это:
Один столбец... список имен, представляющих список членства.
Каждая строка представляет прошлую дату встречи. Существуют столбцы для каждой роли, и ячейки должны быть заполнены с тем, кто выполнил ту роль в ту дату. Я использую Подтверждение правильности данных для имения списка первой вкладки в наличии в каждой ячейке как выпадающее.
Вот точка всего этого. Для каждой из возможных ролей встречи я хотел бы видеть, какие участники являются самыми "запоздалыми", чтобы быть присвоенными для той роли. В основном я хочу список всех членов клуба, отсортированных в порядке того, какой длины это было, так как они в последний раз выполнили ту роль. Люди, которые никогда не служили в той роли, были бы отсортированы наверху списка.
Те третьи данные вкладки - что-то, что может быть выполнено с PivotTable, и т.д...., или я неправильно понимаю цели и ограничения тех инструментов?
Исправьте меня, если я понимаю неправильно. Ваши данные похожи на это:
Первая вкладка (лист):
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
Раз так PivotTable не был бы применим, если Вы не преобразовываете его в форматы, которые записал Mike.
Вместо этого (РЕДАКТИРОВАНИЕ: после разделения каждого ролевого столбца на той же вкладке и сортировке их в алфавитном порядке), т.е. делают вторую вкладку как этот (вид вставки сокращения):
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
Затем можно создать вкладки для каждой роли (или разделить их на той же вкладке), и используйте функцию ПОИСКА для наблюдения в прошлый раз, когда участник сделал ту роль. Например, для 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
Если Вы сортируете от самых старых до новейших, Вы видите, кто никогда не делал роли наверху, и кто сделал в прошлый раз внизу.
Можно также добавить столбцы для того, сколько раз кто-то сделал определенную роль при помощи КОЛИЧЕСТВА, и при помощи скольких дни/недели передали с прошлого раза СЕГОДНЯ и получение различия.
Вставка копии те же функции для других ролей (принимающий они не многие) в различных вкладках.
Я лично пошел бы с VBA, но я обычно склоняюсь тот путь. Если Вы не парень VBA, может быть легче поддержать, если Вы делаете это с формулами и сводными таблицами. Необходимо будет принести некоторые жертвы, все же. Таким образом, стоит исследовать non-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, потому что это - формула массива. Это возвратит максимальную дату, которую это находит для того имени в столбце президента. Заливка, что формула вниз и вправо для удовлетворения данным.
Теперь с данными по листу списка, сделайте сводную таблицу для каждого "ролевого" столбца. Поставьте имя в области строки и роли (например, президент) в области данных. Это, вероятно, примет значение по умолчанию к количеству президента. Щелкните правой кнопкой по тому полю и выберите Field Settings. Изменение Подводит итог Max и установило числовой формат на дату.
Затем щелкните правой кнопкой по одному из имен и выберите полевые настройки. Нажмите кнопку Advanced. Измените Опции AutoSort на Возрастание на Max президента.
Результатом должен быть список отсортированных имен. Имена наверху пошли самое длинное, не будучи президентом.
Ничто лучше, чем пример. Загрузите тот здесь http://www.dailydoseofexcel.com/excel/Rotary.zip, Он содержит 1 рабочую книгу Excel 2003, которая показывает то, что я описал выше.
Да, сводная таблица должна быть достаточной, если Вы настраиваете свои данные соответственно на второй вкладке:
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 ...
Затем можно создать сводную таблицу следующим образом:
Фильтр отчета:ничего
Маркировки столбца:ничего
Маркировки строки: Роль, участник
Значения: МИН Недель с тех пор
Затем:
Ваша pivor таблица должна затем выглядеть подобной следующему:
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.