Диапазон дат в сводной таблице Excel в виде столбцов

Question

У меня есть следующие данные, которые я хотел бы изменить:

Student    JoinDate    ReleaseDate
John       01/05/2011  05/05/2011
James      02/05/2011  04/05/2011

Я хотел бы создать сводную таблицу, в которой будет показано количество студентов в заданном диапазоне дат:

01/05/2011  02/05/2011  03/05/2011  04/05/2011  05/05/2011
1           2           2           2           1

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

Я могу добиться этого с помощью следующей структуры:

Student    JoinDate    ReleaseDate   PivotDate
John       01/05/2011  05/05/2011    01/05/2011
John       01/05/2011  05/05/2011    02/05/2011
John       01/05/2011  05/05/2011    03/05/2011
John       01/05/2011  05/05/2011    04/05/2011
John       01/05/2011  05/05/2011    05/05/2011
James      02/05/2011  04/05/2011    02/05/2011
James      02/05/2011  04/05/2011    03/05/2011
etc...

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

Есть ли у кого-нибудь какие-либо предложения относительно того, как я могу создать нужную сводную таблицу, не имея строки для каждой даты, когда ученик «активен»?

Answer 1

Если вы хотите попробовать решение, не включающее сводные таблицы:

Удалите столбец Дата сводки. Расположите диапазон дат в заголовках и используйте формулу AND(), чтобы определить, попадает ли эта дата в срок пребывания студента. Ваши данные будут выглядеть примерно так:

Вот как выглядит формула в D2 (обратите внимание на абсолютные ссылки). Просто перетащите или скопируйте формулу в остальные столбцы и строки, автозаполнение должно позаботиться об остальном:

=AND(D$1>=$B2,D$1<=$C2)+0

Числовой формат установлен на это: "+";;

Формула для общего количества студентов в диапазоне дат довольно проста. Вот формула для D26 выше.

=sum(D2:D25)

Чтобы добавить столбцы, просто скопируйте последний столбец и измените дату выше. Чтобы добавить учеников, вставьте пустую строку над последним учеником (в данном случае над Банши). Запишите макрос, чтобы вам было проще обновлять данные. Таким образом, вам не нужно вручную настраивать формулу SUM в нижней строке. Вы можете отсортировать данные по имени студента, дате вступления или дате выпуска; формулы справа должны быть в порядке.

Вот копия таблицы, показанной выше (требуется MS Excel 2007): http://ge.tt/46auqAN

Answer 2

Вы можете получить эти значения с помощью формулы массива. Пивот вообще не нужен.

Предполагая, что JoinDate находится в column B , ReleaseDate в column C , и только две строки данных для этого примера.
Даты суммирования указаны в columns E далее: формула в E2 . Введите как формулу массива (нажмите Ctrl-Shift-Enter, а не просто Enter, чтобы завершить формулу)

=SUM(((($B$2:$B$4<=E$1)*1+($C$2:$C$4>=E$1)*1)=2)*1)

Скопируйте через строку 2 для каждой даты, для которой вы хотите итого

Формула выглядит лучше и ее легче поддерживать, если вы используете Named Ranges для данных Date

=SUM((((JoinDate<=E$1)*1+(ReleaseDate>=E$1)*1)=2)*1)

Диапазоны дат могут выходить за пределы существующих данных, чтобы можно было добавить больше последних. например, $B$1:$B$100
Целый столбец ($B:$B) тоже работает, но замедлит пересчет

Кстати, если вы использовали Excel 2007 или более поздней версии, доступна новая функция (введите как обычную формулу, нет необходимости в массиве):

=COUNTIFS(JoinDate,"<="&E$1,ReleaseDate,">="&E$1)

Answer 3

Может не подходить для вашего приложения, но вы должны попробовать функцию Pivot в MS Access, которая намного превосходит Excel